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

Сводные таблицы необходимы для суммирования, анализа и представления данных, находящихся в «больших» исходных таблицах, в различных разрезах . Рассмотрим процесс создания несложных Сводных таблиц.

Сводные таблицы (Вставка/ Таблицы/ Сводная таблица ) могут пригодиться, если одновременно выполняются следующие условия:

имеется исходная таблица с множеством строк (записей), речь идет о нескольких десятках и сотнях строк;
необходимо провести анализ данных, который требует выборки (фильтрации) данных, их группировки (суммирования, подсчета) и представления данных в различных разрезах (подготовки отчетов);
этот анализ затруднительно провести на основе исходной таблицы с использованием других стредств: (CTRL+SHIFT+L ), ;
исходная таблица удовлетворяет определенным требованиям (см. ниже).

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

Подготовка исходной таблицы

Начнем с требований к исходной таблице.

каждый столбец должен иметь заголовок;
в каждый столбец должны вводиться значения только в одном формате (например, столбец «Дата поставки» должен содержать все значения только в формате Дата ; столбец «Поставщик» - названия компаний только в текстовом формате или можно вводить Код поставщика в числовом формате);
в таблице должны отсутствовать полностью незаполненные строки и столбцы;
в ячейки должны вводиться «атомарные» значения, т.е. только те, которые нельзя разнести в разные столбцы. Например, нельзя в одну ячейку вводить адрес в формате: «Город, Название улицы, дом №». Нужно создать 3 одноименных столбца, иначе Сводная таблица будет работать неэффективно (в случае, если Вам нужна информация, например, в разрезе города);
избегайте таблиц с «неправильной» структурой (см. рисунок ниже).

Вместо того, чтобы плодить повторяющиеся столбцы (регион 1, регион 2, … ), в которых будут в изобилии незаполненные ячейки, переосмыслите структуру таблицы, как показано на рисунке выше (Все значения объемов продаж должны быть в одном столбце, а не размазаны по нескольким столбцам. Для того, чтобы это реализовать, возможно, потребуется вести более подробные записи (см. рисунок выше), а не указывать для каждого региона суммарные продажи).

Более детальные советы по построению таблиц изложены в одноименной статье .

Несколько облегчит процесс построения Сводной таблицы , тот факт, если исходная (Вставка/ Таблицы/ Таблица ). Для этого сначала приведите исходную таблицу в соответствие с вышеуказанными требованиями, затем выделите любую ячейку таблицы и вызовите окно меню Вставка/ Таблицы/ Таблица . Все поля окна будут автоматически заполнены, нажмите ОК.

Теперь поставим галочку в Списке полей у поля Продажи.

Т.к. ячейки столбца Продажи имеют числовой формат, то они автоматически попадут в раздел Списка полей Значения.

Несколькими кликами мыши (точнее шестью) мы создали отчет о Продажах по каждому Товару. Того же результата можно было достичь с использованием формул (см. статью ).
Если требуется, например, определить объемы продаж по каждому Поставщику, то для этого снимем галочку в Списке полей у поля Товар и поставим галочку у поля Поставщик.

Детализация данных Сводной таблицы

Если возникли вопросы о том, какие же данные из исходной таблицы были использованы для подсчета тех или иных значений Сводной таблицы , то достаточно двойного клика мышкой на конкретном значении в Сводной таблице , чтобы был создан отдельный лист с отобранными из исходной таблицей строками. Например, посмотрим какие записи были использованы для суммирования продаж Товара «Апельсины». Для этого дважды кликнем на значении 646720. Будет создан отдельный лист только со строками исходной таблицы относящихся к Товару «Апельсины».

Обновление Сводной таблицы

Если после создания Сводной таблицы в исходную таблицу добавлялись новые записи (строки), то эти данные не будут автоматически учтены в Сводной таблице . Чтобы обновить Сводную таблицу выделите любую ее ячейку и выберите пункт меню: меню Работа со сводными таблицами/ Параметры/ Данные/ Обновить . Того же результата можно добиться через контекстное меню: выделите любую ячейку Сводной таблицы Обновить .

Удаление Сводной таблицы

Удалить Сводную таблицу можно несколькими способами. Первый – просто удалить лист со Сводной таблицей (если на нем нет других полезных данных, например исходной таблицы). Второй способ - удалить только саму Сводную таблицу : выделите любую ячейку Сводной таблицы , нажмите CTRL + A (будет выделена вся Сводная таблица ), нажмите клавишу Delete .

Изменение функции итогов

При создании Сводной таблицы сгруппированные значения по умолчанию суммируются. Действительно, при решении задачи нахождения объемов продаж по каждому Товару, мы не заботились о функции итогов – все Продажи, относящиеся к одному Товару были просуммированы.
Если требуется, например, подсчитать количество проданных партий каждого Товара, то нужно изменить функцию итогов. Для этого в Сводной таблице Итоги по/ Количество .

Изменение порядка сортировки

Теперь немного модифицируем наш Сводный отчет . Сначала изменим порядок сортировки названий Товаров: отсортируем их в обратном порядке от Я до А. Для этого через выпадающий список у заголовка столбца, содержащего наименования Товаров, войдем в меню и выберем Сортировка от Я до А .

Теперь предположим, что Товар Баранки – наиболее важный товар, поэтому его нужно выводить в первой строке. Для этого выделите ячейку со значением Баранки и установите курсор на границу ячейки (курсор должен принять вид креста со стрелками).

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

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

Изменение формата числовых значений

Теперь добавим разделитель групп разрядов у числовых значений (поле Продажи). Для этого выделите любое значение в поле Продажи, вызовите правой клавишей мыши контекстное меню и выберите пункт меню Числовой формат …

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

Добавление новых полей

Предположим, что необходимо подготовить отчет о продажах Товаров, но с разбивкой по Регионам продажи. Для этого добавим поле Регион продажи, поставив соответствующую галочку в Списке полей. Поле Регион продажи будет добавлено в область Названия строк Списка полей (к полю Товар). Поменяв в области Названия строк Списка полей порядок следования полей Товар и Регион продажи, получим следующий результат.

Выделив любое название Товара и нажав пункт меню Работа со сводными таблицами/ Параметры/ Активное поле/ Свернуть все поле , можно свернуть Сводную таблицу , чтобы отобразить только продажи по Регионам.

Добавление столбцов

Добавление поля Регион продажи в область строк привело к тому, что Сводная таблица развернулась на 144 строки. Это не всегда удобно. Т.к. продажи осуществлялись только в 6 регионах, то поле Регион продажи имеет смысл разместить в области столбцов.

Сводная таблица примет следующий вид.

Меняем столбцы местами

Чтобы изменить порядок следования столбцов нужно взявшись за заголовок столбца в Сводной таблице перетащить его в нужное место.

Удаление полей

Любое поле можно удалить из Сводной таблицы. Для этого нужно навести на него курсор мыши в Списке полей (в областях Фильтр отчета, Названия отчета, Названия столбцов, Значения), нажать левую клавишу мыши и перетащить удаляемое поле за границу Списка полей.

Другой способ – снять галочку напротив удаляемого поля в верхней части Списка полей. Но, в этом случае поле будет удалено сразу из всех областей Списка полей (если оно использовалось в нескольких областях).

Добавление фильтра

Предположим, что необходимо подготовить отчет о продажах Групп Товаров, причем его нужно сделать в 2-х вариантах: один для партий Товаров принесших прибыль, другой – для убыточных. Для этого:

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

Вид получившейся Сводной таблицы должен быть таким:

Теперь воспользовавшись Выпадающим (раскрывающимся) списком в ячейке B1 (поле Прибыль) можно, например, построить отчет о продажах Групп Товаров, принесших прибыль.

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

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

Очистить фильтр можно через меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить фильтры .

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

Несколько итогов для одного поля

Очистим ранее созданный отчет: выделите любое значение Сводной таблицы , нажмите пункт меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить все ;
Поставьте галочки напротив полей Товар и Продажи в верхней части Списка полей. Поле Продажи будет автоматически помещено в область Значения;
Перетащите мышкой еще одну копию поля Продажи в ту же область Значения. В Сводной таблице появится 2 столбца подсчитывающими суммы продаж;

в Сводной таблице выделите любое значение поля Продажи, вызовите правой клавишей мыши контекстное меню и выберите пункт Итоги по/ Количество . Задача решена.

Отключаем строки итогов

Строку итогов можно отключить через меню: Работа со сводными таблицами/ Конструктор/ Макет/ Общие итоги . Не забудьте предварительно выделить любую ячейку Сводной таблицы .

Группируем числа и Даты

Предположим, что требуется подготовить отчет о сроках сбыта. В результате нужно получить следующую информацию: сколько партий Товара сбывалось в период от 1 до 10 дней, в период 11-20 дней и т.д. Для этого:

Очистим ранее созданный отчет: выделите любое значение Сводной таблицы , нажмите пункт меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить все ;
Поставьте галочку напротив поля Сбыт (срок фактической реализации Товара) в верхней части Списка полей. Поле Сбыт будет автоматически помещено в область Значения;
выделите единственное значение поля Сбыт в Сводной таблице , вызовите правой клавишей мыши контекстное меню и выберите пункт Итоги по/ Количество .
Перетащите мышкой еще одну копию поля Сбыт в область Названия строк;

Теперь Сводная таблица показывает сколько партий Товара сбывалось за 5, 6, 7, … дней. Всего 66 строк. Сгруппируем значения с шагом 10. Для этого:

Выделите одно значение Сводной таблицы в столбце Названия строк;
В меню выберите пункт Группировка по полю ;
Появившееся окно заполните, как показано на рисунке ниже;

Нажмите ОК.

Теперь Сводная таблица показывает сколько партий Товара сбывалось в период от 1 до 10 дней, в период 11-20 дней и т.д.

Чтобы разгруппировать значения выберите пункт Разгруппировать в меню Работа со сводными таблицами/ Параметры/ Группировать .

Аналогичную группировку можно провести по полю Дата поставки. В этом случае окно Группировка по полю будет выглядеть так:

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

Условное форматирование ячеек Сводной таблицы

К ячейкам Сводной таблицы можно применить правила как и к ячейкам обычного диапазона.
Выделим, например, ячейки с 10 наибольшими объемами продаж. Для этого:

Выделите все ячейки содержащие значения продаж;
Выберите пункт меню Главная/ Стили/ Условное форматирование/ Правила отбора первых и последних значений/ 10 первых элементов ;
Нажмите ОК.

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

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

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

Узнайте о стандартные сценарии преобразования сводные таблицы в формулы листа

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

Изменение расположения и удаление ячеек

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

Вставка строк или столбцов

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

Использование нескольких источников данных

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

Использование ссылок на ячейки для изменения введенных пользователем данных

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

Создание неоднородного макета строк или столбцов (асимметричное создание отчета)

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

Создание собственных формул куба и многомерных выражений

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

Преобразование ячейки в формулы, использующие функцию куба

Примечание: Только с помощью этой процедуры можно преобразовать сводную таблицу интерактивной аналитической обработки (OLAP).

Преобразование ячейки в формулы, использующие функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

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

Убедитесь, что доступна команда Создать GetPivotData в группе Сводная таблица на вкладке Параметры .

Примечание: Команда Создать GetPivotData управляет параметром Использовать функции GetPivotData для ссылок в сводной таблице в категории Формулы раздела Работа с формулами в диалоговом окне Параметры Excel .

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

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

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

Завершите ввод формулы и нажмите клавишу ВВОД.

Примечание: В случае удаления из отчета любой из ячеек, на которые ссылается формула ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ формула возвращает ошибку #ССЫЛКА!.

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

Лист, показанный на рис. 167.1, отображает тот тип преобразования, о котором я говорю. Диапазон А1:Е4 содержит исходную сводную таблицу: 48 точек данных. Столбцы G:I показывают часть 48-строковой таблицы, полученную из сводной таблицы. Другими словами, каждое значение в исходной сводной таблице преобразуется в строку, которая также содержит соответствующие значению название продукта и месяц. Этот тип списка полезен, поскольку его можно отсортировать и манипулировать им другими способами.

Хитрость создания такого списка заключается в использовании сводной таблицы. Но прежде чем вы сможете применить этот метод, вы должны добавить команду Мастер сводных таблиц на панель быстрого доступа. Excel 2007, Excel 2010 и Excel 2013 все еще поддерживают Мастера сводной таблицы , но он недоступен на ленте. Чтобы получить доступ к мастеру, выполните следующие действия.

Щелкните правой кнопкой мыши на панели быстрого доступа и выберите в контекстном меню пункт Настройка панели быстрого доступа .
В разделе Панель быстрого доступа диалогового окна Параметры Excel выберите Команды на ленте из раскрывающегося списка слева.
Прокрутите список и выберите пункт .
Нажмите кнопку Добавить .
Нажмите 0К , чтобы закрыть диалоговое окно Параметры Excel .

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

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

Активизируйте любую ячейку в сводной таблице.
Щелкните на значке Мастер сводных таблиц и диаграмм , который вы добавили на панель быстрого доступа.
В диалоговом окне Мастер сводных таблиц и диаграмм установите первый переключатель в положение в нескольких диапазонах консолидации и нажмите кнопку Далее .
В шаге 2а установите переключатель в положите Создать поля страницы и нажмите кнопку Далее .
В шаге 2b в поле Диапазон укажите диапазон сводной таблицы (А1:Е4 для выборки из примера) и нажмите кнопку Добавить ; затем нажмите кнопку Далее , чтобы перейти к шагу 3.
В шаге 3 выберите место для сводной таблицы и нажмите кнопку Готово . Excel создаст сводную таблицу с данными и покажет область Список полей сводной таблицы .
В области Список полей сводной таблицы снимите флажки Строка и Столбец .

Так, сводная таблица остается только с полем данных Сумма по полю Значение . На данный момент небольшая сводная таблица показывает только сумму всех значений (рис. 167.2).

Чтобы завершить, щелкните два раза на ячейке, содержащей итоговое значение (2851 в этом примере). Excel создаст новый лист, который будет содержать исходные данные в виде таблицы (рис. 167.3). Заголовки столбцов представляют общее описание (Строка , Столбец и Значение ), так что вы, вероятно, захотите изменить их и сделать более информативными.

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

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

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

1. Нажмите на любую ячейку в сводной таблице и перейдите к дизайн вкладку и нажмите подытоги > Не показывать субтитры .

2. Нажмите Grand Totals > Выкл. Для строк и столбцов под дизайн Вкладка. Смотрите скриншот:

3. Нажмите Макет отчета > Повторить все метки элементов под дизайн Вкладка. См. Снимок экрана:

4. Нажмите Макет отчета снова и нажмите Показать в табличной форме , Смотрите скриншот:

Теперь сводная таблица показана ниже:

5. Нажмите Опционы вкладку (или Анализировать вкладка) и снимите флажок Кнопки и Заголовки полей , который относится к Показать группа.

Теперь сводная таблица, показанная ниже:

5. Теперь я покажу пустые ячейки как ноль. Щелкните любую ячейку сводной таблицы и щелкните правой кнопкой мыши, чтобы выбрать Параметры сводной таблицы , затем в раскрывающемся диалоговом окне, под Макет и формат вкладка, снимите флажок Для пустых ячеек И нажмите OK закрыть Параметры сводной таблицы Диалог. Смотрите скриншот:

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

6. Выберите сводную таблицу и нажмите Ctrl + C в то время, чтобы скопировать его, затем поместите курсор на ячейку, в которую вы хотите вставить сводную таблицу в виде списка, и щелкните правой кнопкой мыши, чтобы выбрать Специальная вставка > Значение (V) , Смотрите скриншот:

Внимание : В Excel 2007 вам нужно щелкнуть Главная > макаронные изделия > Вставить значения для вставки сводной таблицы в виде списка.

Теперь вы можете увидеть список, показанный ниже:

Office Tab

Принесите удобные вкладки в Excel и другое программное обеспечение Office, как Chrome, Firefox и новый Internet Explorer.

Сводные таблицы также называют двумерными (2D) таблицами или таблицами в «пользовательском» представлении. Они преподносят информацию в сжатой и наглядной матрице с заголовками столбцов и строк. Но такое представление данных не подходят для построения сводных таблиц PivotTable, графиков, фильтрации, экспорта данных в сторонние системы, т.д. Поэтому перед анализом данных так важно аккуратно преобразовать сводные таблицы в «плоский» список.

Надстройка «Редизайн таблицы» точно преобразует сводные таблицы в плоский список без написания макросов:

Редизайн сводной таблицы в список в секунды
Преобразование сложных таблиц с многоуровневыми заголовками
Корректный редизайн таблиц с объединёнными или пустыми ячейками
Сохранение заголовков столбцов
Сохранение форматирования ячеек

Язык видео: английский. Субтитры: русский, английский. (Внимание: видео может не отражать последние обновления. Используйте инструкцию ниже.)

Добавить «Редизайн таблицы» в Excel 2019, 2016, 2013, 2010, 2007

Подходит для: Microsoft Excel 2019 - 2007, desktop Office 365 (32-бит и 64-бит).

Как работать с надстройкой:

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

Нажмите кнопку «Редизайн таблицы» на вкладке XLTools > Откроется диалоговое окно.
Совет
Укажите размер заголовков:
В простой таблице: Заглавных строк = 1, Заглавных столбцов = 1
Чтобы вставить плоский список на существующий лист, укажите начальную ячейку (верхняя левая).
Нажмите ОК >

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

Некоторые сводные таблицы могут иметь сложную структуру и многоуровневые заголовки. Их также можно сделать плоскими с помощью XLTools:

Нажмите кнопку «Редизайн таблицы» на вкладке XLTools > Откроется диалоговое окно.
Выделите сводную таблицу, включая заголовки.
Совет : нажмите на любую ячейку таблицы, и вся таблица будет выделена автоматически.
Укажите размер заголовков:
- Заглавных строк: число строк, которые составляют заголовок таблицы сверху.
- Заглавных столбцов: число столбцов, которые составляют заголовок таблицы слева.
Укажите, следует ли поместить результат на новый или на существующий лист.
Нажмите ОК > Готово. Надстройка автоматически подберёт ширину столбцов для плоского списка.

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

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

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

Выделите сводную таблицу, включая заголовки.
Укажите размер заголовков.
Отметьте флажком «Пропустить пустые значения».
Нажмите ОК > Готово.

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

Нажмите кнопку «Редизайн таблицы» на вкладке XLTools.
Выделите сводную таблицу, включая заголовки.
Укажите размер заголовков.
Отметьте флажком «Дублировать значение в объединённых ячейках»:
- Если объединённые ячейки находятся в заголовке: содержимое объединённых ячеек заголовка будет продублировано в каждой соответствующей строке плоского списка.
- Если в объединённые ячейки находятся в теле таблицы: значения в объединённых ячейках будут продублированы в каждой соответсвующей ячейке плоского списка.
Укажите, куда поместить результат.
Нажмите ОК > Готово.

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

Нажмите кнопку «Редизайн таблицы» на вкладке XLTools.
Выделите сводную таблицу, включая заголовки.
Укажите размер заголовков.
Отметьте флажком «Сохранить заголовки»:
- Где это возможно, надстройка продублирует заголовки из сводной таблицы.
- Категориям таблицы будет автоматически присвоен заголовок «Категория».
- Переменным значениям таблицы будет автоматически присвоен заголовок «Значение».
Укажите, куда поместить результат.
Нажмите ОК > Готово.

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

Нажмите кнопку «Редизайн таблицы» на вкладке XLTools.
Выделите сводную таблицу, включая заголовки.
Укажите размер заголовков.
Отметьте флажком «Сохранить формат ячеек»:
Каждая ячейка сохранит своё форматирование в результирующем плоском списке, в т.ч. цвет заливки, границы, цвет шрифта, цвета условного форматирования, дата/общий/числовой/денежный/ формат, т.д.
Укажите, куда поместить результат.
Нажмите ОК > Готово.

Внимание : обработка больших таблиц с множеством форматов займёт больше времени.

Какие таблицы обрабатывает надстройка «Редизайн таблицы»

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

Термином «Таблица» в Excel часто обозначают разные понятия:

«Настоящая» таблица — именованный диапазон с применением стиля таблицы (операция «Форматировать как таблицу»). Может быть преобразована в простой диапазон.
Диапазон — простой диапазон внешне похожий на таблицу, с применением (или без) форматирования цвета фона, границ, т.д. Может быть преобразован в «настоящую» таблицу.
Сводная таблица (PivotTable) — динамическая таблица, сгенерированная с помощью операции Excel «Сводная таблица» (PivotTable). Ячейки не могут быть отредактированы.

Надстройка XLTools «Редизайн таблицы» позволяет преобразовать в плоский список «настоящие» таблицы и диапазоны. Чтобы произвести редизайн PivotTable, сначала скопируйте диапазон такой таблицы и вставьте значения — это создаст простой диапазон, который далее можно преобразовать.

Появились вопросы или предложения? Оставьте комментарий ниже.