Источником данных для сводной таблицы служит список данных, где, как правило, каждый столбец выступает в роли поля сводной таблицы. Но что если к вам пришла, только с виду похожая на сводную, таблица (она отформатирована и внешне напоминает ее, но использовать инструменты работы со сводными таблицами невозможно). И вам необходимо превратить ее в список данных, т.е. выполнить обратную операцию. В этом посте вы узнаете, как преобразовать сводную таблицу с двумя переменными в список данных.
На рисунке показан принцип, который я описал. Т.е. в диапазоне A2:E5 находится исходная сводная таблица, которая преобразуется в список данных (диапазон H2:J14). Вторая таблица представляет тот же набор данных, только в другом ракурсе. Каждое значение исходной сводной таблицы выглядит в виде строки, состоящее из пункта поля строки, поля столбца и соответствующего им значения. Такое отображение данных бывает полезно, когда необходимо отсортировать и манипулировать данными другими способами.
Для того чтобы реализовать возможность создания такого списка, мы воспользуемся инструментами сводной таблицы. Добавим кнопку Мастер сводных таблиц в панель быстрого доступа, которая недоступна нам на ленте, но осталась как рудимент от более ранних версий Excel.
Перейдите по вкладке Файл -> Параметры. В появившемся диалоговом окне Параметры Excel, во вкладке Панель быстрого доступа в левом поле найдите пункт Мастер сводных таблиц и диаграмм и добавьте его в правый. Нажмите ОК.
Теперь на панели быстрого доступа у вас появился новый значок.
Щелкните по этой вкладке, чтобы запустить Мастер сводных таблиц.
На первом шаге мастера необходимо выбрать тип источника данных сводной таблицы. Устанавливаем переключатель В нескольких диапазонах консолидации и жмем Далее.
На шаге 2а укажите, как следует создавать поля страницы. Поместите переключатель Создать поля страницы -> Далее.
На шаге 2б, в поле Диапазон выберите диапазон, содержащий данные, и щелкните Добавить. В нашем случае это будет местоположение исходной сводной таблицы A1:E4.
На третьем шаге необходимо определиться, куда необходимо поместить сводную таблицу, и нажмите кнопку Готово.
Excel создаст сводную таблицу с данными. В левой части экрана вы увидите область Список полей сводной таблицы. Уберите все пункты с полей строк и столбцов. Более подробно о редактировании полей строк и столбцов в сводной таблице я писал в предыдущей статье.
У вас получится небольшая сводная таблица, состоящая из одной ячейки, которая содержит сумму всех значений исходной таблицы.
Дважды щелкните по этой ячейке. Excel создаст новый лист, который будет содержать таблицу со списком значений.
Заголовки этой таблицы представляют общую информацию, вероятно, вы захотите сделать их более информативными.
Спасибо большое за отличную идею преобразования «плоских» таблиц в нормальные
На форумах, обычно, для этого пишут макросы (предлагают использовать макросы)
Спасибо за идею! Очень полезная статья!
Добрый день!
возможно ли подобное преобразование в случае, если переменных не две, а три: столбец есть верхний и есть нижний заголовки (друг под другом), нужны оба потом в списке в поле напротив соответствующей строки. Спасибо заранее.
Громадное спасибо!
Вы мне очень помогли справиться со сложным и срочным заданием от руководства. Спасибо! Лучики хорошего настроения Вам!
Оригинально) Спасибо!
Спасибо, очень выручили. Очень нужно бывает данные как в сводной конвертировать в формат БД.
Идея топ, реально помогло.