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

Сводная по таблице с многострочной шапкой

Предположим, что в качестве источника данных вам досталась вот такая «красота»:
Приятная на вид таблица, с которой, тем не менее, страшно неудобно работать. Представьте, например, что вам нужно по этой таблице:

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

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

Поэтому (капитан Очевидность!) это не нужно делать формулами. Гораздо проще и быстрее реализовать подобное будет с помощью сводных таблиц — гибкого, мощного и намного более подходящего инструмента для такой задачи.

Одна проблема — в Microsoft Excel нельзя построить сводную таблицу, если у неё многострочная шапка, а именно это мы и имеем в нашем примере.

Нас выручит Power Query — мощная надстройка, встроенная в Excel, начиная с 2016-й версии (а для Excel 2010 и 2013 её можно бесплатно скачать с сайта Microsoft).

Шаг 1. Грузим данные в Power Query

Самый простой путь в нашем случае — это выделить весь диапазон с данными (начиная с ячейки B2 и до конца таблицы) и дать ему имя на вкладке Формулы — Задать имя (Formulas — Create name) . Давайте оригинально назовём его Данные, например:

Затем, проверив, что выделена вся таблица, выберем на вкладке Данные (или на вкладке Power Query, если вы установили её как отдельную надстройку на Excel 2010-2013), команду Из таблицы / Диапазона (From Table/Range) . Наша «красивая» таблица загрузится в редактор запросов Power Query:

Обратите внимание, что объединенные ячейки разъединились, а образовавшиеся в результате пустые ячейки теперь заполнены null — специальным словом, обозначающим в Power Query «пустоту» или отсутствие чего-либо.

Также Power Query попытался автоматически распознать типы данных в каждом столбце, добавив в правой панели Примененные шаги (Applied Steps) шаг Измененный тип (Changed Type) . Делать такое пока рановато, так что этот шаг можно смело удалить, щёлкнув по крестику слева от его названия:

Шаг 2. Преобразуем таблицу в плоскую

Дальнейшие преобразования будут состоять из нескольких операций:

Во-первых, давайте избавимся от лишних пустых строк (см. строки 9 или 26, например) с помощью команды Главная — Удалить строки — Удалить пустые строки (Home — Remove rows — Remove empty rows) :

На этом приведение исходных данных в порядок успешно завершено и можно переходить к построение сводной таблицы.

Шаг 3. Строим сводную

На Главной вкладке Power Query выберем команду Закрыть и загрузить — Закрыть и загрузить в. (Home — Close & Load — Close & Load to. ) :

В следующем окне можно пойти двумя путями:

Если у вас есть в этом окне опция Отчет сводной таблицы (Pivot Table Report) — смело выбирайте его и жмите на ОК. На новом листе будет создана сводная таблица по нашим данным из Power Query.

Если у вас старая версия Excel, то такой опции в этом окне может не быть. Тогда придется «идти в обход»:

1. Выбираем вариант Только создать подключение (Create connection only) ,
2. После возвращения в Excel на вкладке Вставка жмём кнопку Сводная таблица (Insert — Pivot Table)
3. В окне создания сводной выбираем опцию Использовать внешний источник данных (Use external data source) .
4. В открывшемся окне выбираем наше подключение из Power Query:

И в том, и в другом случае результатом будет сводная, построенная по нашей исходной красивой, но неудобной таблице:

Теперь наши данные можно крутить-вертеть-анализировать любым нужным образом легко и красиво 🙂

Ссылки по теме

• Редизайн кросс-таблицы в плоскую
• Сводная таблица по нескольким диапазонам данных
• Настройка вычислений в сводной таблице

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

Объединить ячейки в сводной таблице

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

Одним из вариантов оптимизации для удобства чтения является объединение единым заголовком группы столбцов, входящих, например, в один определенный период.

В обычных таблицах мы пользуемся кнопкой « Объединить и поместить в центре ».

В сводной таблице данная кнопка работать не будет .

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

Кликнуть правой клавишей на любом месте сводной таблицы.

В меню выбрать «Параметры сводной таблицы».

В открывшемся окне в группе «Разметка и формат» поставить галку «Объединить и выровнять по центру ячейки с подписями».

Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее «перевести»)

Группировка и отмена группировки данных в сводной таблице

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

Группировка данных

1. Щелкните значение в сводной таблице правой кнопкой мыши и выберите команду Группировать.
2. В окне Группировка установите флажки Начиная с и Заканчивая и при необходимости измените значения.
3. В разделе С шагом выберите период времени. Для числовых полей введите число, которое указывает интервал для каждой группы.
4. Нажмите кнопку ОК.

Группировка выделенных элементов

1. Удерживая нажатой клавишу CTRL, выделите несколько значений.
2. Щелкните правой кнопкой мыши и выберите команду Группировать.

Группировка по дате и времени

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

Присвоение имени группе

1. Выберите группу.
2. На вкладке Анализ нажмите кнопку Параметры поля. На вкладке Сводная таблица вАктивном поле щелкните Параметры поля.
3. Измените пользовательское имя, как вам нравится, а затем нажмите кнопку ОК.

Разгруппировка сгруппированных данных

Как правильно склеить текст из нескольких ячеек таблицы Excel

В категории Текстовые есть функция СЦЕПИТЬ (CONCATENATE), которая соединяет содержимое нескольких ячеек (до 255) в одно целое, позволяя комбинировать их с произвольным текстом. Например, вот так.

Способ 1. Функция СЦЕПИТЬ

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

Способ 2. Символ для склеивания текста (&)

Для суммирования содержимого нескольких ячеек используют знак +, а для склеивания содержимого ячеек используют знак & (расположен на большинстве клавиатур на цифре 7).

Способ 2. Символ для склеивания текста

При его использовании необходимо помнить, что:

 Этот символ надо ставить в каждой точке соединения, т.е. на всех «стыках» текстовых строк, так же как вы ставите несколько плюсов при сложении нескольких чисел (2+8+6+4+8).
 Если нужно приклеить произвольный текст (даже если это всего лишь точка или пробел, не говоря уж о целом слове), то этот текст надо заключать в кавычки, как и в предыдущем способе.

Способ 3. Макрос для объединения ячеек без потери текста

Начинающие пользователи часто пытаются использовать для объединения ячеек с текстом кнопку Объединить и поместить в центре (Merge and Center) с вкладки Главная (Home).

Проблема в одном – объединять ячейки эта функция умеет

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

Microsoft Excel честно предупреждает при попытке выполнения операции

Сделать свой вариант этой функции с сохранением (точнее – слиянием) текста из всех объединяемых ячеек можно с помощью небольшого макроса. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте в вашу книгу новый программный модуль (меню Insert – Module) и введите туда текст такого простого макроса.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

Sub MergeToOneCell() Const sDELIM As String = " " 'символ-разделитель Dim rCell As Range Dim sMergeStr As String If TypeName(Selection) <> "Range" Then Exit Sub 'если выделены не ячейки – выходим With Selection For Each rCell In .Cells sMergeStr = sMergeStr & sDELIM & rCell.Text 'собираем текст из ячеек Next rCell Application.DisplayAlerts = False 'отключаем стандартное предупреждение .Merge Across:=False 'объединяем ячейки Application.DisplayAlerts = True .Item(1).Value = Mid(sMergeStr, 1 + Len(sDELIM)) 'добавляем к объединенной ячейке текст End With End Sub

Sub MergeToOneCell() Const sDELIM As String = » » ‘символ-разделитель Dim rCell As Range Dim sMergeStr As String If TypeName(Selection) <> «Range» Then Exit Sub ‘если выделены не ячейки – выходим With Selection For Each rCell In .Cells sMergeStr = sMergeStr & sDELIM & rCell.Text ‘собираем текст из ячеек Next rCell Application.DisplayAlerts = False ‘отключаем стандартное предупреждение .Merge Across:=False ‘объединяем ячейки Application.DisplayAlerts = True .Item(1).Value = Mid(sMergeStr, 1 + Len(sDELIM)) ‘добавляем к объединенной ячейке текст End With End Sub

Теперь, если выделить несколько ячеек и запустить этот макрос с помощью сочетания клавиш Alt+F8, то Excel объединит выделенные ячейки в одну, слив туда же и текст из всех ячеек через пробелы. Вместо пробела, конечно же, можно использовать другой разделитель – введите его во второй строке макроса в переменную sDELIM.