Сводная таблица в Excel – удобный инструмент для анализа и представления данных. Но что делать, если данные находятся в разных источниках? Разберем, как сделать сводную таблицу из нескольких листов в Excel.
Даже если есть ERP и BI-системы, без Excel финансовому директору не обойтись. Всевозможные расчеты, сводные таблицы, удобные графики - в Excel можно сделать практически все что угодно. Но нужно знать, как это сделать.
Сводная таблица в Excel средствами Power Query
Для начала необходимо отметить, что Excel может работать с исходными таблицами различного размера. Но заголовки и шапка этих таблиц должны быть одинаковы. Это нужно для того, чтобы программа правильно интерпретировала используемые данные. В противном случае может возникать ошибка.
Допустим, необходимо создать единую сводную таблицу, данные для которой надо взять из разных листов. Такая ситуация может возникать в случае, если в компании несколько распределенных точек сбыта, складских помещений, разные заказчики одинаковых групп товаров. В таком случае отчеты будут предоставлять разные филиаы. И чтобы их адекватно проанализировать, имеет смысл создать общую для всей компании таблицу данных, на основании которой в дальнейшем будет построена сводная таблица.
В этом случае необходимо создать чистый новый лист в программе Excel.
В этом листе перейти во вкладку «Данные».
Скачайте дополнительный материал к статье:
Далее нажать «Создать запрос», в выпавшем списке выбрать «Из файла» и затем – «Из книги». Создадим сводную таблицу из нескольких листов на примере – два магазина прислали отчеты о наличии у них ящиков различного цвета на стеллажах. Данные каждого магазина сохранены на одном листе. Из этих листов сформирована «Книга1», с которой мы работаем.
В появившемся окне надо указать книгу, откуда программа должна взять данные и нажать кнопку «Импорт».
Появится окно под названием «Навигатор». В нем надо выбрать лист, из которого будут взяты данные. Указать можно любой лист.
Полезный материал про работу в Excel!
Программа покажет данные в окне предпросмотра, которые предстоит взять из указанного листа.
В нашем примере видно, что указанный лист содержит множество ячеек с данными «null». Это неверно, так как программа будет обрабатывать и эти ячейки. Чтобы сократить область обрабатываемых значений и удалить такие нулевые ячейки, необходимо исправить исходный файл. Для этого нужно перейти в исходную таблицу и нажать «Ctrl + End». Будет выделена последняя активная ячейка таблицы. Надо удалить все ячейки правее и ниже таблицы, добиваясь того, чтобы при нажатии «Ctrl + End» становилась активной нижняя правая ячейка таблицы.
После этого источник данных не будет содержать лишней информации.
Дальше необходимо отредактировать данные в разделе «Параметры запроса».
Можно удалить строчки «Навигация» и «Измененный тип». И приступить к редактированию данных в разделе «Источник». В главном окне редактора будет отображаться перечень всех листов указанной книги. В нашем случае «Лист1» и «Лист2».
Далее надо выбрать только нужную информацию. В контекстном меню колонки «Data» выбрать «Удалить другие столбцы».
Затем в строке «Data» нажать иконку с двумя стрелками, как указано на рисунке.
В появившемся окне снять галочку с пункта «Использовать исходное имя столбца как префикс». И нажать «ОК». Появится таблица в которой собраны все данные.
Далее необходимо убрать лишние заголовки – «шапки». Для этого надо нажать «Использовать первую строку в качестве заголовков».
Таблица будет перестроена. Дублирующую строку с «шапкой» можно удалить. Для этого в фильтре столбца «Склад» снять галочку с пункта «Склад» и нажать «ОК». Затем в этом же фильтре нажать «Удалить пустые». Соответствующие строки будут удалены.
Далее необходимо сохранить полученную таблицу. Нажать кнопку «Закрыть и загрузить», далее в меню – «Закрыть и загрузить в...».
В появившемся окне «Загрузить в» поставить переключатель в позицию «Только создать подключение» и нажать кнопку «Загрузить». Появится запрос, на основании которого и будет строиться сводная таблица.
Далее, чтобы построить сводную таблицу, нужно во вкладке «Вставка» нажать кнопку «Сводная таблица».
В появившемся окне установить переключатель в положение «Использовать внешний источник данных» и нажать кнопку «Выбрать подключение».
В появившемся окне выбрать имя сформированного запроса, в нашем случае – «сводная» и нажать кнопку «Открыть».
Появится конструктор сводной таблицы. Данные можно добавлять и перемещать как в обычной сводной таблице.
Сводная таблица в версиях Excel до 2016 года
В программах Excel, изданных до 2003 года включительно, сводные таблицы из разных источников создавались через опцию «построить сводную по нескольким диапазонам консолидации». Однако подобное построение таблицы не дает возможности полноценно анализировать весь объем полученной информации. Таблица, построенная таким образом, может использоваться только для самого простого анализа данных.
В версиях с 2007 года надо было добавлять специальный «Мастер сводных таблиц», где новые диапазоны данных по одному указывались через диалоговые окна. Полученные сводные таблицы также обладали достаточно ограниченной функциональностью.