Как в сводной таблице заполнить пустые строки
Перейти к содержимому

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

  • автор:

Повторение подписей элементов в сводной таблице

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

Повторение подписей элементов

  1. Щелкните правой кнопкой мыши строку или метку столбца, которую требуется повторить, и выберите пункт Параметры поля.
  2. Откройте вкладку Макет & Печать и проверка поле Повторять метки элементов.
  3. Убедитесь, что выбран параметр Показывать метки элементов в табличной форме .
  • При изменении любой из повторяемых меток внесенные изменения применяются ко всем остальным ячейкам с той же меткой. Вы не можете изменять отдельные повторяющиеся ячейки.
  • При изменении формата всех повторяющихся меток путем выбора метки элемента в строке сводки или всех повторяющихся меток формат автоматически применяется ко всем остальным ячейкам с той же меткой. Однако вы также можете изменить формат отдельных повторяющихся меток, не применяя то же форматирование к другим ячейкам с той же меткой.
  • Повторяющиеся метки отображаются только в том случае, если сводная таблица находится в табличной форме. Они не отображаются при применении компактной формы или формы контура. При необходимости можно изменить формат в макете отчета.

Включение или отключение повторяющиеся метки элементов для всех полей

Макет отчета

  1. Щелкните в любом месте сводной таблицы.
  2. На вкладке Конструктор для средства сводных таблиц щелкните Макет отчета.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

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

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел»
ИНН 7735603520
ОГРН 1147746834949
ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРНИП 310633031600071

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

Исходные данные

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

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

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

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

Одна проблема — в 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:

Данные загружены в 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. ) :

Выгружаем данные в Excel

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

Выбор места для выгрузки

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

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

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

Строим сводную таблицу по подключению из Power Query

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

Готовая сводная таблица

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

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

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

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

Автозаполнение пустых строк в сводной таблице

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

Дмитрий Щербаков(The_Prist):
Добрый день.
А Вы и не сможете использовать данную команду в сводной. Вы даже вручную не сможете заполнить эти пустые ячейки. Получите сообщение: «Нельзя изменить эту часть отчета сводной таблицы».
Это ограничение самого Excel и его никак не обойти. Чтобы заполнить эти ячейки Вам необходимо таблицу скопировать, вставить на новое место как значения и уже затем обрабатывать как угодно.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *