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

Читать в полной версии →

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

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

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

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

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

Пример формулы: =A2&" "&B2.

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

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

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

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

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

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

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

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

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

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

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

Самый простой способ – использовать формулу вида:

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

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

Пример 1:

Пример 2:

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

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

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

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

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

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

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

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

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

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

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

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

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

Навык работы с Excel и умение объединить данные поможет вам при обучении курсе

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