Сводная таблица из нескольких листов
- Excel 2010-2022 — Файл (File) — Параметры (Options) — Панель быстрого доступа (Quick Access Toolbar)
- Excel 2007 — Кнопка офис— Параметры Excel (Excel options) — Панель быстрого доступа (Quick Access Toolbar)
Конечно, это совсем не та сводная, что на основе одного листа: нет толковых и понятных заголовков столбцов; набор функций ограничен; да и вообще функционал скуден по сравнению с обычной сводной(например, нет группировки дат и значений). Может быть поэтому в Microsoft посчитали правильным убрать данный пункт с глаз, чтобы не вводить в заблуждение.
Но тем не менее, несмотря на все недочеты, для кого-то возможность создания подобных таблиц может оказаться очень даже полезной. Например, если необходимо проанализировать данные по продажам за несколько лет и каждый год на отдельном листе или в отдельном диапазоне, как на рисунке ниже:
Жмем Далее (Next) , выбираем место создания сводной таблицы(существующий или новый лист), Готово (Finish) .
Получаем более-менее привычный вид сводной таблицы:
Я бы выделил две небольшие проблемы:
- Например на рисунке выше столбец Менеджер расположен в области значений и нет возможности перенести его в область строк или столбцов. Можно лишь убрать его из отображения, выключив в фильтре поля Столбец. Все дело в том, что сводные таблицы, созданные на основе нескольких диапазонов консолидации, содержат только три базовых поля: Строка, Столбец и Значение. Поле Строка всегда создается на основе первого столбца указанного диапазона и может содержать только одну область. Поле Столбец содержит все, что расположено правее первого столбца и комбинирует столбцы всех диапазонов в одно поле заголовков.
- Тот же столбец Менеджер содержит значения по количеству, но не суммы, т.к. это текстовые поля. Именно из-за этого значения для остальных столбцов тоже отражены не суммами, а количеством. Это легко изменить, поменяв функцию поля на Сумма. В этом случае столбец Менеджер будет содержать нули. Но главная особенность в том, что этот столбец трактуется сводной изначально как значения и не может содержать текст. А это значит, что нет никакой возможности узнать значения из таблицы исходных данных.
Эти моменты следует учитывать при подготовке данных для построения сводной таблицы на основе нескольких диапазонов консолидации.
СОЗДАНИЕ ПОЛНОЦЕННОЙ СВОДНОЙ ТАБЛИЦЫ ИЗ НЕСКОЛЬКИХ ЛИСТОВ
Но есть и более экзотический метод создания сводной из нескольких листов — через подключения. Создается подключение на языке запросов SQL, запрос подсовываем в кэш сводной — и готово, у нас есть сводная из нескольких листов со всем её функционалом.
Пара важных замечаний(ложек дегтя, если угодно):
Количество строк в таблицах значения не имеет — оно может быть разным.
В прикрепленном файле я добавил процедуру обновления кэша без построения сводной заново. Может пригодиться, если подключение по каким-то причинам сбилось.
Скачать пример:
Так же на данный момент есть еще одна возможность сбора данных с нескольких листов и файлов — при помощи бесплатной надстройки PowerQuery. Как это сделать можно почитать и посмотреть в статье: Собрать и просуммировать данные из разных файлов при помощи PowerQuery
Как сделать сводную таблицу, сгруппировать временной ряд? | Что важно знать о | Статьи
Сводные таблицы в Excel позволяют проанализировать данные, которые находятся в одном большом диапазоне. Причем исходная таблица, которая будет использоваться для анализа, может быть создана как в Excel, так и в другой базе данных, или другом документе.
Как создать
Теперь рассмотрим пример создания сводной таблицы. Предположим, у нас есть данные магазина одежды по продажам: какой продавец, какого числа, что из товаров продал и на какую сумму.
Чтобы сделать сводную таблицу, выделяем любую ячейку из исходной, затем переходим на вкладку «Вставка» и нажимаем на кнопочку «Сводная таблица» .
Появляется следующее диалоговое окно. В нем нужно указать диапазон или название таблицы с исходными данными, причем здесь есть возможность выбрать ее с другого источника. Затем отметьте маркером, где поместить создаваемый отчет. Сделаем его на новом листе. Нажмите «ОК» .
В открытой книге Excel создастся новый лист, на котором будет размещена, пока еще пустая сводная таблица.
С правой стороны отобразится список полей и областей. Поля – это все заголовки столбцов, которые были в исходном диапазоне. С помощью мышки мы будем перетаскивать их в одну из четырех областей, расположенных ниже. Таким образом, формируя сводную таблицу.
Добавленные поля будут отмечены галочкой. В областях таблицы, Вы сможете менять их местами, чтобы добиться такого вида, который больше всего подойдет для анализа Ваших исходных данных.
Как делать сводную таблицу в Excel
Фильтровать данные по выбранному диапазону мы будем по продавцам. То есть — выберем продавца, а в таблице отобразятся проданные им товары. Зажимаем левой кнопкой мыши поле «Продавец» и перетягиваем его в область «Фильтр отчета» . Таблица изменилась, а добавленное поле теперь отмечено галочкой.
Параметры таблицы
В начале статьи упоминалось, что вычисления должны быть сделаны исключительно за третий квартал, а в итоговой таблице отображается весь год. Чтобы это исправить, проделайте такие действия:
- Нажмите на кнопку, которая находится возле пункта «Дата».
- В отобразившемся окне поставьте флажок возле значения «Выделить несколько…».
- Уберите флажки, которые расположены напротив всех дат, не относящихся к третьему кварталу, и кликните на клавишу «Ок».
Сводные таблицы в Excel: Создание, Фильтрация, Форматирование
- Перейдите во вкладку «Файл» и в появившемся списке выберите строку «Параметры».
- Перед вами появится новая вкладка, в которой слева необходимо отметить пункт «Панель быстрого доступа».
- Из левого столбца в перечне выберите «Мастер сводных таблиц и диаграмм», и нажмите на пункт «Добавить». Для окончания переноса кликните на клавишу «Ок».
Важно, чтобы новые добавленные столбцы были внутри диапазона уже существующей таблицы с данными, тогда нам не надо будет переделывать сводную, чтобы добавить новые поля, достаточно её будет обновить.
