Объединение данных в Excel: методы и лучшие практики

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

• Как в Excel объединить данные на уровне ячейки
  • Использование символа «амперсанд» (&)
  • Использование функции СЦЕП
• Excel: объединить данные на уровне таблиц
  • Объединение данных с помощью функции суммирования
  • Объединение данных с помощью функции «консолидация»
  • Особенности функции «Консолидация»

Аналитик данных — с нуля до трудоустройства за 9 месяцев

• Постоянная поддержка от наставника и учебного центра
• Помощь с трудоустройством
• Готовое портфолио к концу обучения
• Практика с первого урока

Вы получите именно те инструменты и навыки, которые позволят вам найти работу

Узнать больше

Как в Excel объединить данные на уровне ячейки

Для объединения данных из двух и более ячеек в одну традиционно используют два метода:

Использование символа «амперсанд» (&)

1. Выберите ячейку для вывода объединенных данных.
2. Поставьте = (знак равенства) и укажите ячейку, данные из которой нужно показывать первыми.
3. Введите символ & и пробел в кавычках.
4. Укажите следующую ячейку с данными для объединения.
5. Нажмите клавишу ВВОД.

Пример: =A2&" "&B2.

Результат будет выглядеть следующим образом:

Использование функции СЦЕП

1. Укажите ячейку для вывода объединенных данных.
2. Введите функцию = СЦЕП(.

3. Укажите первую ячейку с данными, которые необходимо соединить.

   Используйте точку с запятой для разделения ячеек. Пробелы, знаки препинания и текст добавляйте в кавычках.

4. Нажмите клавишу ВВОД.

Пример: =СЦЕП("Не";" ";"слышны";" ";"в";" ";"саду";" ";"даже";" ";"шорохи.")

Результат: Не слышны в саду даже шорохи.

Excel: объединить данные на уровне таблиц

Объединение данных с помощью функции суммирования

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

Самый простой способ – использовать выражение вида:

=янв!B2+фев!B2+мар!B2+апр!В2+май!В2+июн!В2 или =СУММ(янв:июн!B2), если таблиц очень много.

Эти формулы суммируют содержимое ячеек В2 из нескольких листов.  Вы можете регулярно добавлять новые листы с данными между первым и последним листами,  они также будут учитываться при суммировании – для суммирования в этом случае используются трехмерные формулы.

Пример 1:

Пример 2:

Объединение данных с помощью функции «консолидация»

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

1. Откройте все таблицы, из которых вы хотите собрать данные.
2. Создайте новую незаполненную книгу.
3. Выберите ячейку и на вкладке меню зайдите в раздел Данные/Data – Консолидация/Consolidate. Откроется соответствующее окно:

1. В разделе Ссылка/Reference переключитесь на необходимый файл и выделите таблицу с данными вместе с шапкой. Нажмите кнопку Добавить/Add для добавления в список объединяемых диапазонов. Внесите таким образом все данные из файлов.

Если вы хотите учитывать заголовки столбцов и строк – поставьте все галочки в разделе «Использовать в качестве имен»/Use labels.

Опция «Создавать связи с исходными данными»/Create links to source data позволит  автоматически пересчитывать данные в консолидированной таблице при обновлении данных в отдельных таблицах.

1. Нажмите ОК.

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

Особенности функции «Консолидация»

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

Существуют три варианта консолидации данных:

• По расположению. В этом варианте финальные данные будут иметь одинаковое расположение и порядок с исходными. Чтобы получить такой консолидированный отчет, необходимо одинаково упорядочить позиции.
• По категории. В этом случае таблицы могут быть организованы по разным принципам. Например, в магазинах может быть разный ассортимент товаров. А в отчетах будут использоваться одинаковые названия столбиков и строк и представлены все товары. Для использования этого варианта необходимо проверить, чтобы в разделе «Использовать в качестве имен»/Use labels были проставлены галочки у опций «значения левого столбца» и «подписи верхней строки».
• По формуле. Вариант используется, когда данные для объединения (цифры, текст и т.д.) находятся на разных листах и в разных ячейках. В этом случае выражение со ссылками на диапазоны данных для объединения вводят в первую ячейку и копируют на весь список.

В консолидированном отчете вы всегда можете посмотреть, из каких данных сформированы итоговые величины. Для этого необходимо нажать «плюсик» в левом поле.

Умение работать с Excel поможет вам при обучении курсе

«Работа с Excel-файлами в Pandas», где вы не только узнаете про полезные функции Excel, но и узнаете, как их можно эффективно использовать в программировании.