Как объединить данные ячеек: полезные функции в Excel и Google

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

Как объединить данные в ячейках из нескольких столбцов

Выгрузки из Google Forms, 1С или CRM часто содержат одну категорию в нескольких ячейках. Например, имя и фамилия в разных столбцах. Или список заказанных товаров в отдельных колонках.

Если просто выделить ячейки и нажать «Объединить», в Excel и Google Таблицах останется только первое значение, остальные исчезнут. Чтобы собрать всё без потерь, используйте один из способов ниже.

В Excel

В Excel можно объединить данные через специальные значения и формулы.

Знак «&». Объединяет данные с разделителем. Выделите пустую ячейку, в которой появятся объединенные данные — например, D2.

Вставьте формулу и измените номера ячеек.

Пояснение:

• A1 — это номер ячейки.
• & — это оператор, который «склеивает» текст.
• » » — это пробел, заключенный в кавычки. Он нужен, чтобы между именем и фамилией был пробел. Если его не добавить, имя и фамилия будут написаны слитно.

Нажмите Enter и протяните вниз по строкам, если данных много.

Если нужно объединить данные через разделитель, то вместо пробела в формуле указываем «,», «;» или «—».

Формула «ОБЪЕДИНИТЬ». В англоязычном Excel функция называется TEXTJOIN. Соединяет текст из диапазона или отдельных ячеек с разделителем.

Выделите пустую ячейку и введите формулу:

• «, » — разделитель между элементами, можно заменить на «;», » — «, «».
• ИСТИНА (TRUE) — Excel будет игнорировать пустые ячейки. Если использовать ЛОЖЬ (FALSE) вместо ИСТИНА (TRUE), Excel оставит пустые ячейки в итоговой строке.
• A1:A11 — диапазон ячеек, из которых берем текст.

Формула поддерживает массивы и работает с целыми колонками:

В Google Таблицах

Вот как сделать то же самое в Google Таблицах.

Выделите пустую ячейку.

Введите формулу и заполните номера своих ячеек:

Нажмите Enter и протяните вниз по строкам.

В формуле JOIN вместо пробела тоже можно использовать другой разделитель.

Как объединить данные в нескольких таблицах

Иногда данные с одинаковыми колонками записаны в разных таблицах. Например, одна — это январская выгрузка, другая — февральская. Или один файл — это филиал в Москве, другой — в Екатеринбурге. Чтобы подсчитать общий объём или построить график, эти таблицы нужно объединить в одну.

Вот как это сделать в Excel и Google Таблицах.

Через формулу ВПР или VLOOKUP

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

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

Что нужно сделать. Создайте новую таблицу с тремя столбцами: сотрудники, премии за февраль и за январь.

Оба столбца с премией стоит ввести через функцию ВПР. Так, если изменятся данные в исходном столбце, они появятся и в новой таблице.

Наведите курсор на первую ячейку с премией. Перейдите в раздел «Формулы» → «Ссылки и массивы» → «ВПР». У вас появится такое окно.

В «Искомое_значение» введите ячейку с фамилией сотрудника. В «Таблице» — столбцы, откуда брать данные. «Номер_столбца» — это номер относительно указанного диапазона, а не всей таблицы. В нашем случае указываем «2». В «интервальном_просмотре» укажите «0», чтобы искать точные совпадения в столбце «ФИО».

Используйте формулу ВПР для переноса данных из второй таблицы. 

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

Если вы работаете в Google Таблицах, используйте формулу VLOOKUP. Она ищет совпадения в одном столбце и возвращает данные из другого — даже если порядок строк не одинаковый.

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

Пояснение:

• A2 — ячейка, где указано ключевое слово.
• Лист2!A:B — диапазон, где ищем это слово и берем данные (в этом случае столбец A — ключи, столбец B — частотность).
• 2 — номер столбца, из которого берём результат (относительно указанного диапазона, не всей таблицы).
• FALSE — ищет точное совпадение.

Через формулу ПРОСМОТРX или XLOOKUP

Функция XLOOKUP доступна в версии Excel 2021 года и Google Таблицах. Если ВПР ищет данные только сверху вниз и только в правую сторону, XLOOKUP работает и влево, и вверх, и по строкам. Например, по названию товара можно найти его поставщика, цену или категорию — даже если эти данные находятся в разных столбцах или строках.

Допустим, у нас есть две таблицы: первая — с категорией, списком товаров и поставщиками, а вторая — с товарами и ценой. Нам нужно подтянуть поставщиков и цены в таблицу с товарами.

Что нужно сделать. Выделите ячейку справа от товара — например, B2. Введите формулу.

В формуле несколько переменных:

• A2 — ячейка с товаром, который ищем.
• E2:E4 — столбец, где Excel ищет товар.
• G2:G4 — столбец, откуда подставляется значение, то есть с названием поставщика.

Протяните формулу вниз по строкам.

Затем то же самое сделайте для значения «Категория» и «Цена». В последнем случае вместо диапазона столбцов будут указаны строки — их формула тоже обрабатывает.

Через функции INDEX + MATCH

Если вы работаете в Excel 2016 или более ранней версии, функции XLOOKUP в ней ещё нет. Зато есть комбинация INDEX и MATCH, которая решает те же задачи — подставляет данные из одной таблицы в другую по совпадению значений.

Например, у нас есть список товаров и таблица с ценами. Нужно подставить цену для каждого товара в основную таблицу.

Что нужно сделать. Выделите ячейку, куда хотите подставить данные — например, ячейку C2.

Введите формулу:

Пояснение:

• A2 — это значение, по которому ищем (например, «Кофе»).
• $D$2:$D$6 — диапазон, где ищем совпадение (например, список товаров).
• $E$2:$E$6 — диапазон, откуда подставляем результат (например, цены).
• MATCH находит номер строки, где значение совпадает.
• INDEX берёт из этой строки значение из нужного столбца.

Формула ищет строгое совпадение — если товар не найден, Excel покажет ошибку #Н/Д.

Через команду «Консолидация»

Способ подойдет, если таблицы хранятся на разных листах и нужно просто объединить данные по одинаковым категориям. В Google Таблицах такой команды нет, только в Excel.

Что нужно сделать. Создайте пустой лист — это будет сводная таблица. Встаньте в ячейку А1.

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

Заполните данные в появившемся окне.

В поле «Функция» выберите, что нужно делать с данными — по умолчанию стоит «Сумма». В поле «Ссылки» поочерёдно добавьте все таблицы, которые хотите объединить. Для этого сначала перейдите на лист с первой таблицей → выделите диапазон, нажмите «Добавить» и повторите для остальных листов.

Поставьте две галочки:

• «Подписи в верхней строке». Так Excel будет знать, что первая строка — это названия столбцов.
• «Левый столбец». Так Excel будет сравнивать сотрудников по фамилии, а не просто по порядку.

Excel соберёт все значения в одну таблицу и сложит одинаковые фамилии. Если в исходных ячейках что-то поменяется — придётся снова нажать «Консолидация» → «ОК». Автоматически она не обновляется.

Через мастера сводных таблиц

В Google Таблицах такой команды нет, работает способ только в Excel. Этот способ подойдёт, если у вас больше трех листов с одинаковой структурой. Например, филиалы: «Москва», «Питер», «Казань», и везде одни и те же столбцы. 

Мастер сводных таблиц — это старая функция Excel. Сейчас она скрыта, поэтому нужно включить её вручную. Для этого щёлкните правой кнопкой мыши по пустому месту на панели инструментов. Выберите «Настройка ленты» → откройте вкладку «Панель быстрого доступа».

В выпадающем списке по центру выберите «Все команды». Найдите строку «Мастер сводных таблиц» (начинается со слова «Мастер»). Добавьте её в панель справа и нажмите ОК. Кнопка появится вверху Excel.

Что нужно сделать. Нажмите на кнопку и настройте сводную таблицу:

1. Выберите: «Несколько диапазонов» → «Создать поля страницы».
2. Далее → поочерёдно добавьте все листы с таблицами. 
3. Назовите каждый диапазон — например, «Москва», «Питер». 
4. Укажите, где разместить итоговую таблицу (например, A1 текущего листа).
5. Нажмите «Готово».

Excel создаст одну сводную таблицу по всем листам. В ней можно:

• выбрать, какие поля отображать;
• группировать по филиалам;
• убрать лишние строки (например, имена сотрудников);
• менять структуру без переделки формул.

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