Процедуры из статьи доступны в !SEMTools Lite и Pro.
Объединение данных — одна из базовых и при этом самых многогранных задач в Excel. Под этим понятием могут скрываться совершенно разные операции: от склейки текста в соседних ячейках до сборки данных из десятков файлов, листов или таблиц в единый итоговый набор. В этой статье разберем основные объекты, которые можно объединять в Excel, стандартные формульные возможности, их ограничения, а также более продвинутые сценарии.
Какие объекты можно объединять в Excel
В Excel объединение применяется к разным уровням структуры данных. В зависимости от задачи, это могут быть:
- отдельные ячейки с текстом или числами;
- диапазоны ячеек;
- столбцы таблиц;
- листы внутри одной книги;
- разные книги Excel;
- CSV-файлы как внешний источник данных.
Каждый из этих уровней требует своих инструментов: формул, встроенных процедур Excel или специализированных инструментов.
Формулы для объединения текста ячеек
Склеить текст из нескольких ячеек в одну формулой можно четырьмя способами: оператором & и функциями СЦЕПИТЬ, СЦЕП и ОБЪЕДИНИТЬ. Они появлялись в Excel в разное время, поэтому до сих пор работают все, но по-разному обращаются с диапазонами, разделителями и пустыми ячейками. Подробно каждый способ, их сравнение и ограничения по версиям разобраны в статье как объединить ячейки в Excel без потери данных:
- оператор &: работает в любой версии, каждая ячейка и разделитель перечисляются вручную;
- СЦЕПИТЬ (CONCATENATE): самая старая функция, оставлена ради совместимости, отдельная страница о ней в справочнике функций;
- СЦЕП (CONCAT): принимает диапазоны, но склеивает без разделителей;
- ОБЪЕДИНИТЬ (TEXTJOIN): один разделитель на все значения и пропуск пустых ячеек, основной выбор в Excel 2019 и новее.
Как все четыре способа ведут себя на одних и тех же данных, видно на примере с ФИО в статье объединить данные в нескольких столбцах Excel без потерь.
Процедура «Объединить ячейки» и ее недостатки
Excel также предлагает визуальную процедуру Объединить ячейки на вкладке «Главная». Важно понимать, что это не объединение данных, а объединение ячеек как объектов интерфейса.
Основные недостатки такого подхода:
- сохраняется только значение из верхней левой ячейки, остальные данные теряются;
- невозможно корректно сортировать и фильтровать данные;
- возникают проблемы при копировании, вставке и работе с таблицами;
- формулы и автоматизация становятся менее предсказуемыми.
По этой причине объединение ячеек как процедура оформления не рекомендуется для аналитических и рабочих таблиц. В большинстве случаев следует объединять именно данные, а не сами ячейки. Горячие клавиши, отмена объединения, поиск объединённых ячеек и причины, по которым кнопка бывает неактивна, собраны в статье как объединить ячейки в Excel.
Объединение выделенных ячеек с помощью !SEMTools
Для задач, где требуется быстро и безопасно объединить содержимое выделенных ячеек, существует группа бесплатных инструментов в надстройке !SEMTools для Excel. Он позволяет разными способами объединять значения без потери данных и без использования формул.
Как работает процедура «Выделенные ячейки» и чем она отличается от формул, рассказано в разделе объединить выделенные ячейки без формул. Для построчной склейки столбцов есть две команды «По горизонтали», они описаны в статье объединить данные в нескольких столбцах.
Объединение диапазонов, столбцов, листов и книг
Когда объем данных растет, задача объединения выходит за рамки отдельных ячеек. Excel и надстройки позволяют собирать данные:
- из нескольких диапазонов по горизонтали;
- из всех столбцов листа в один;
- со всех листов книги в единый лист;
- из нескольких открытых книг Excel;
- из множества CSV-файлов в один итоговый файл.
Для каждого из этих сценариев существуют отдельные инструкции и инструменты:
- Объединить файлы CSV
- Объединить все открытые книги в одну
- Собрать данные с нескольких листов на один
- Объединить столбцы по горизонтали (построчно, без потери данных)
- Собрать все столбцы в один
- Объединить выделенные ячейки
Создание гиперссылок как частный случай объединения данных
Отдельного внимания заслуживает создание гиперссылок. По своей сути гиперссылка в Excel — это результат объединения трех элементов:
- адреса ссылки;
- отображаемого текста;
- текста подсказки (overtip).
Вручную в Excel можно создать только одну гиперссылку за раз. Для этого используется стандартная функция или диалоговое окно, что подробно описано в статье: 10 способов сделать ссылку в Excel.
Однако при работе с большими массивами данных такой подход не масштабируется. Но есть решение! !SEMTools позволяет массово создавать гиперссылки, объединяя тексты, адреса и подсказки в полноценные ссылки автоматически.
Подробное описание этого подхода доступно в отдельной статье: Массовое создание гиперссылок в Excel.
Итоги: какой способ объединения выбрать
Объединение данных в Excel — это целый набор подходов, зависящих от уровня данных и поставленной задачи. Формулы подходят для точечной работы с текстом, процедуры оформления — лишь для визуальных целей, а для системной и массовой обработки оптимально использовать специализированные инструменты, такие как !SEMTools для Excel. Обратные операции — разделение ячеек, строк и листов — собраны в статьях как разделить ячейку, как разделить ячейку на строки и как разбить лист по значению столбца; формулой текст делит ТЕКСТРАЗД.