text
Открыть свежий номер журнала>> / Подписка со скидкой 40% >> ✆ 8 (800) 550-07-98
Журнал

Как с помощью Excel объединить данные и разместить в одной ячейке отчетной таблицы

Если нужно разместить текст из нескольких столбцов в одном (например, перечислить все аналитические признаки), пригодится функция СЦЕПИТЬ(текст1;текст2;...). Она позволяет объединить несколько ячеек с текстовой или числовой информацией в одну, не потеряв и не исказив исходное выражение.

Например, отчет о стоимости готовой продукции представлен в виде таблицы 1 на рисунке 1, а для руководства нужно составить список в форме таблицы 2.

Чтобы заполнить таблицу 2, в ячейку E2 (см. рисунок 1) следует ввести формулу:
=СЦЕПИТЬ(A4;" ";B4;" - ";C4). Между каждым аргументом может стоять разделитель в виде пробела, знака «-» или запятой. Если пользователю отчета нужно показывать цену в рублях, то последний аргумент достаточно дополнить так: С4&" руб." Тогда в ячейке появится текст – «12234 Чай черный Малиновый – 70 руб.»

Иветта Новикова , финансовый менеджер ООО «ТЕТ-а-ТЕТ»

Если нужно объединить текстовую и числовую информацию отчета с помощью функции Excel СЦЕПИТЬ(), то отформатировать данные стандартным способом «Формат ячеек» не получится. Для корректного отображения чисел стоит использовать функцию ТЕКСТ(значение, строка_форматирования), где:

Если, к примеру, в ячейке А1 стоит сумма 2000, а в ячейку B1 она должна выводиться в рублях, то, введя в ячейку В2 формулу: = ТЕКСТ(А1, "# ###,0#р."), получим необходимый формат – 2 000р.

Это самый простой пример объединения текстов. На практике задачи могут быть сложнее, например, когда:

Как быстро объединить информацию из двух столбцов отчета в один с помощью Excel

Если необходимо объединить данные из двух и более столбцов одного или разных отчетов, обладающих каким-то общим признаком, то можно сделать это, не затрачивая много времени на ручную обработку исходной информации. Достаточно стандартных функций Excel.

Например, финансовой службе компании «Альфа» из исходного прайс-листа готовой продукции (см. рисунок 1. Прайс-лист готовой продукции) нужно выделить несколько позиций по их кодам и предоставить данные руководству в виде текста (см. рисунок 2. Поиск позиции по коду). Значит, условием поиска и объединения будет код каждой позиции.

На новом листе этой книги Excel необходимо создать новую таблицу или добавить столбцы к таблице на текущем листе (как это показано на рисунке 4). Столбец А можно заполнить вручную либо воспользоваться возможностями Excel для разделения текста (подробнее о том, как это сделать, см. Как быстро разделить список на части с помощью Excel). Теперь необходимо, чтобы в ячейках столбца В появились сводные данные из исходной таблицы, но без дублирования цифр кода.

В ячейке B4 следует записать выражение:
=ЕСЛИ(СОВПАД(ПРАВСИМВ(D4;5);A4);СЦЕПИТЬ(ПСТР(D4;1;ДЛСТР(D4)-6);" - ";F4&"руб./шт")).
Рассмотрим подробнее эту формулу.

Функция ЕСЛИ() позволит объединить информацию из исходной таблицы в ячейку B4 только в том случае, если код, указанный в ячейке А4, соответствует наименованию готовой продукции в ячейке D4. Ее синтаксис ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь).

Первый аргумент этой функции проверяет совпадение кодов из двух ячеек A4 и D4 и представляет формулу СОВПАД(), а точнее, выражение СОВПАД(ПРАВСИМВ(D4;5);A4), где:

Второй аргумент функции ЕСЛИ() – выражение СЦЕПИТЬ(ПСТР(D4;1;ДЛСТР(D4)-6);" - ";F4&"руб./шт") (подробнее о синтаксисе функции см. выше), где:

Результат работы этой формулы представлен на рисунке 5.

Как быстро найти и объединить информацию из двух столбцов отчета в один с помощью Excel

Если перед финансовой службой стоит задача найти и объединить в одном отчете данные из разных таблиц, удовлетворяющих определенному условию, можно вручную задать формулу в Excel, которая поможет сделать это быстро, не затрачивая время на упорядочивание информации.

Для этого нужно воспользоваться только двумя функциями Excel:

К примеру, номенклатура готовой продукции компании «Альфа» представлена таблицей из трех столбцов (см. рисунок 6. Ассортимент готовой продукции). Необходимо создать форму, которая позволит по введенному коду находить и отображать в новой ячейке соответствующую коду позицию и ее стоимость из исходной таблицы.

Сначала нужно дополнить таблицу двумя столбцами либо создать форму на новом листе или книге Excel (см. рисунок 7. Окно для поиска позиции по коду).

В ячейку А4 необходимо ввести нужный код готовой продукции, а в ячейку В4 – вписать следующее выражение:
=СЦЕПИТЬ(ВПР(A4;$D$4:$F$10;2;ЛОЖЬ);" - ";ВПР(A4;$D$4:$F$10;3;ЛОЖЬ)&"руб.").

Оно будет искать код в столбце D, а затем объединит данные из столбцов E и F соответствующей ему строки. В отличие от предыдущей формулы (см. выше), на поиск не будет влиять длина самого кода – в нем может быть произвольное количество символов. Также можно не упорядочивать столбец с наименованиями по кодам, однако для поиска по функции ВПР() числовую и текстовую информацию придется разделить.

В итоге получится таблица, представленная на рисунке 8.

Иветта Новикова , финансовый менеджер ООО «ТЕТ-а-ТЕТ»

Группа компаний состоит из нескольких торговых и производственных предприятий. Финансовой службе необходимо подготовить аналитическую записку для руководства, где отобразить в простой форме план-факт отклонения по компаниям «Альфа» и «Бета», а также общий итог фактических расходов по ним в разрезе каждой статьи. Фрагмент отчета о расходах представлен на рисунке 9.

В аналитической записке должны быть только итоги в общих чертах (у какой из компаний отклонение выше, по какой конкретно статье) так, как это показано на рисунке 10, чтобы далее вывести эти данные в другой документ.

Введем дополнительный столбец с критериями, по которым будем собирать исходные данные, – это могут быть коды или наименования статей – для простоты выберем коды. Теперь необходимо, чтобы по этим кодам в ячейках В4 и B16 (см. рисунки  11 и 12) выводилась одна строка текста.

Ячейке В4 следует задать выражение функции СЦЕПИТЬ():
=СЦЕПИТЬ("Отклонение по статье ";ВПР(A4;'Исходные данные 2'!A3:J19;2;ЛОЖЬ); ": ";ЕСЛИ(ВПР(A4;'Исходные данные 2'!A3:J19;5;ЛОЖЬ)>ВПР(A4;'Исходные данные 2'!A3:J19;9;ЛОЖЬ); "Альфа > Бета";"Альфа < Бета")), где:

В ячейку В16 необходимо ввести следующее выражение:
=СЦЕПИТЬ("Итого по статье ";ВПР(A16;'Исходные данные 2'!A3:J19;2;ЛОЖЬ); ": ";ТЕКСТ((ВПР(A16;'Исходные данные 2'!A3:J19;4;ЛОЖЬ)+ВПР(A16;'Исходные данные 2'!A3:J19;8;ЛОЖЬ)); "# ##0,00р.")), где:

В результате отчет будет выглядеть так, как показано на рисунке 12.

Иветта Новикова , финансовый менеджер ООО «ТЕТ-а-ТЕТ»

Чтобы объединить информацию из нескольких столбцов одного отчета или из разных отчетов в одну ячейку, воспользуйтесь специальной функцией надстройки «Финансист» (см. рисунок 13. Панель работы с текстом надстройки «Финансист»).

К примеру, нужно сформировать из сводного отчета о расходах холдинга сравнительную таблицу, где выделить только затраты на оплату труда и содержание персонала.

На отдельном листе той же книги или в новом файле создайте форму сравнительной таблицы. Например, как на рисунке 14.

Выделите свободную ячейку С3, откройте вкладку главного меню «Финансист», раздел «Работа с текстом» и выберите пункт «Сцепить текст в ячейках». Далее в диалоговом окне (см. рисунок 15. Заполнение параметров сцепления ячеек в надстройке «Финансист») введите:

Рекомендации по теме

Школа

Проверь свои знания и приобрети новые

Записаться

Самое выгодное предложение

Самое выгодное предложение

Воспользуйтесь самым выгодным предложением на подписку и станьте читателем уже сейчас

Мы в соцсетях
А еще:
×
Чтобы скачать документ, зарегистрируйтесь на сайте!

Это бесплатно и займет меньше минуты!

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться

Зарегистрируйтесь или войдите на сайт:

Вы получите доступ к уникальному контенту, который закрыт для незарегистрированных пользователей!

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться

Здравствуйте, меня зовут Александр
Давайте знакомиться!

Чтобы я открыл вам доступ, пожалуйста, пройдите короткую регистрацию

Вам будут доступны:

  • - 15 000 рекомендаций по управлению финансами;
  • - 1000 образцов положений и форм отчетов
  • - 300 финансовых Excel-моделей.

После регистрации пришлю Вам подарок
КНИГА "Cash is king. Управление денежными потоками"

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
...и получить подарки!
Зарегистрироваться

Здравствуйте, меня зовут Александр
Давайте знакомиться!

Чтобы я открыл вам доступ, пожалуйста, пройдите короткую регистрацию

Вам будут доступны:

  • - 15 000 рекомендаций по управлению финансами;
  • - 1000 образцов положений и форм отчетов
  • - 300 финансовых Excel-моделей.

После регистрации пришлю Вам подарок
КНИГА "Cash is king. Управление денежными потоками"

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
...и получить подарки!
Зарегистрироваться
×
Пожалуйста, войдите на сайт

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться

Здравствуйте, меня зовут Александр
Давайте знакомиться!

Чтобы я открыл вам доступ, пожалуйста, пройдите короткую регистрацию

Вам будут доступны:

  • - 15 000 рекомендаций по управлению финансами;
  • - 1000 образцов положений и форм отчетов
  • - 300 финансовых Excel-моделей.

После регистрации пришлю Вам подарок
КНИГА "Cash is king. Управление денежными потоками"

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
...и получить подарки!
Зарегистрироваться
Пожалуйста, войдите на сайт

Зарегистрируйтесь или войдите на сайт:

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться
×

Сайт использует файлы cookie. Они позволяют узнавать вас и получать информацию о вашем пользовательском опыте. Это нужно, чтобы улучшать сайт. Посещая страницы сайта и предоставляя свои данные, вы позволяете нам предоставлять их сторонним партнерам. Если согласны, продолжайте пользоваться сайтом. Если нет – установите специальные настройки в браузере или обратитесь в техподдержку.