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

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

На рисунке показан принцип, который я описал. Т.е. в диапазоне A2:E5 находится исходная сводная таблица, которая преобразуется в список данных (диапазон H2:J14). Вторая таблица представляет тот же набор данных, только в другом ракурсе. Каждое значение исходной сводной таблицы выглядит в виде строки, состоящее из пункта поля строки, поля столбца и соответствующего им значения. Такое отображение данных бывает полезно, когда необходимо отсортировать и манипулировать данными другими способами.

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

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

Перейдите по вкладке Файл -> Параметры. В появившемся диалоговом окне Параметры Excel, во вкладке Панель быстрого доступа в левом поле найдите пункт Мастер сводных таблиц и диаграмм и добавьте его в правый. Нажмите ОК.

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

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

ярлик мастера сводних таблиц

Щелкните по этой вкладке, чтобы запустить Мастер сводных таблиц.

На первом шаге мастера необходимо выбрать тип источника данных сводной таблицы. Устанавливаем переключатель В нескольких диапазонах консолидации и жмем Далее.

На шаге 2а укажите, как следует создавать поля страницы. Поместите переключатель Создать поля страницы -> Далее.

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

На шаге 2б, в поле Диапазон выберите диапазон, содержащий данные, и щелкните Добавить. В нашем случае это будет местоположение исходной сводной таблицы A1:E4.

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

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

Excel создаст сводную таблицу с данными. В левой части экрана вы увидите область Список полей сводной таблицы. Уберите все пункты с полей строк и столбцов. Более подробно о редактировании полей строк и столбцов в сводной таблице я писал в предыдущей статье.

сумма по полю значений

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

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

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

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


7 комментариев

  1. Спасибо большое за отличную идею преобразования «плоских» таблиц в нормальные
    На форумах, обычно, для этого пишут макросы (предлагают использовать макросы)

    Спасибо за идею! Очень полезная статья!

  2. Добрый день!
    возможно ли подобное преобразование в случае, если переменных не две, а три: столбец есть верхний и есть нижний заголовки (друг под другом), нужны оба потом в списке в поле напротив соответствующей строки. Спасибо заранее.

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

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