Сводные таблицы в excel 2016

Продвинутые сводные таблицы в Excel

В этом разделе самоучителя дана пошаговая инструкция, как создать продвинутую сводную таблицу в современных версиях Excel (2007 и более новых). Для тех, кто работает в более ранних версиях Excel, рекомендуем статью: Как создать продвинутую сводную таблицу в Excel 2003?

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

A B C D E
1 Date Invoice Ref Amount Sales Rep. Region
2 01/01/2016 2016-0001 $819 Barnes North
3 01/01/2016 2016-0002 $456 Brown South
4 01/01/2016 2016-0003 $538 Jones South
5 01/01/2016 2016-0004 $1,009 Barnes North
6 01/02/2016 2016-0005 $486 Jones South
7 01/02/2016 2016-0006 $948 Smith North
8 01/02/2016 2016-0007 $740 Barnes North
9 01/03/2016 2016-0008 $543 Smith North
10 01/03/2016 2016-0009 $820 Brown South
11

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

  1. Выделите любую ячейку в диапазоне или весь диапазон данных, который нужно использовать для построения сводной таблицы.ЗАМЕЧАНИЕ: Если выделить одну ячейку в диапазоне данных, то Excel автоматически определит диапазон для создания сводной таблицы и расширит выделение. Для того, чтобы Excel выбрал диапазон правильно, должны быть выполнены следующие условия:
    • Каждый столбец в диапазоне данных должен иметь уникальный заголовок.
    • В диапазоне данных не должно быть пустых строк.
  2. Кликните по кнопке Сводная таблица (Pivot Table) в разделе Таблицы (Table) на вкладке Вставка (Insert) Ленты меню Excel.
  3. Откроется диалоговое окно Создание сводной таблицы (Create PivotTable), как показано на рисунке ниже.Убедитесь, что выбранный диапазон охватывает именно те ячейки, которые должны быть использованы для создания сводной таблицы.Здесь же можно выбрать, где должна быть размещена создаваемая сводная таблица. Можно поместить сводную таблицу На существующий лист (Existing Worksheet) или На новый лист (New Worksheet). Нажмите ОК.
  4. Появится пустая сводная таблица и панель Поля сводной таблицы (Pivot Table Field List), в которой уже содержатся несколько полей данных. Обратите внимание, что эти поля – заголовки из таблицы исходных данных.Мы хотим, чтобы сводная таблица показывала итоги продаж помесячно с разбиением по регионам и по продавцам. Для этого в панели Поля сводной таблицы (Pivot Table Field List) сделайте вот что:
    • Перетащите поле Date в область Строки (Row Labels);
    • Перетащите поле Amount в область Σ Значения (Σ Values);
    • Перетащите поле Region в область Колонны (Column Labels);
    • Перетащите поле Sales Rep. в область Колонны (Column Labels).

  • В итоге сводная таблица будет заполнена ежедневными значениями продаж для каждого региона и для каждого продавца, как показано ниже.Чтобы сгруппировать данные помесячно:
    • Кликните правой кнопкой мыши по любой дате в крайнем левом столбце сводной таблицы;
    • В появившемся контекстном меню нажмите Группировать (Group);
    • Появится диалоговое окно Группирование (Grouping) для дат (как показано на рисунке ниже). В поле С шагом (By) выберите Месяцы (Months). Кстати, сгруппировать даты и время можно и по другим временным периодам, например, по кварталам, дням, часам и так далее;
    • Нажмите ОК.
  • Как и требовалось, наша сводная таблица (смотрите картинку ниже) теперь показывает итоги продаж по месяцам с разбивкой по регионам и по продавцам.

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

    Фильтры в сводной таблице

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

    Чтобы отобразить данные только для региона продаж North, в панели Поля сводной таблицы (Pivot Table Field List) перетащите поле Region в область Фильтры (Report Filters).

    Поле Region появится вверху сводной таблицы. Откройте выпадающий список в этом поле и выберите в нём регион North. Сводная таблица (как показано на картинке ниже) покажет значения только для региона North.

    Вы можете быстро переключиться на просмотр данных только для региона South – для этого нужно в выпадающем списке в поле Region выбрать South.

    Источник: office-guru.ru

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

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

    Исходный материал – таблица с несколькими десятками и сотнями строк, несколько таблиц в одной книге, несколько файлов. Напомним порядок создания: «Вставка» – «Таблицы» – «Сводная таблица».

    А в данной статье мы рассмотрим, как работать со сводными таблицами в Excel.

    Как сделать сводную таблицу из нескольких файлов

    Первый этап – выгрузить информацию в программу Excel и привести ее в соответствие с таблицами Excel. Если наши данные находятся в Worde, мы переносим их в Excel и делаем таблицу по всем правилам Excel (даем заголовки столбцам, убираем пустые строки и т.п.).

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

    Мы просто создаем сводный отчет на основе данных в нескольких диапазонах консолидации.

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

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

    Мастер сводных таблиц при таких исходных параметрах выдаст ошибку. Так как нарушено одно из главных условий консолидации – одинаковые названия столбцов.

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

    1. В ячейке-мишени (там, куда будет переноситься таблица) ставим курсор. Пишем = – переходим на лист с переносимыми данными – выделяем первую ячейку столбца, который копируем. Ввод. «Размножаем» формулу, протягивая вниз за правый нижний угол ячейки.
    2. По такому же принципу переносим другие данные. В результате из двух таблиц получаем одну общую.
    3. Теперь создадим сводный отчет. Вставка – сводная таблица – указываем диапазон и место – ОК.

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

    Покажем, к примеру, количество проданного товара.

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

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

    Из отчета (см.выше) мы видим, что продано ВСЕГО 30 видеокарт. Чтобы узнать, какие данные были использованы для получения этого значения, щелкаем два раза мышкой по цифре «30». Получаем детальный отчет:

    Как обновить данные в сводной таблице Excel?

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

    Курсор должен стоять в любой ячейке сводного отчета.

    Правая кнопка мыши – обновить.

    Чтобы настроить автоматическое обновление сводной таблицы при изменении данных, делаем по инструкции:

    1. Курсор стоит в любом месте отчета. Работа со сводными таблицами – Параметры – Сводная таблица.
    2. Параметры.
    3. В открывшемся диалоге – Данные – Обновить при открытии файла – ОК.

    Изменение структуры отчета

    Добавим в сводную таблицу новые поля:

    1. На листе с исходными данными вставляем столбец «Продажи». Здесь мы отразим, какую выручку получит магазин от реализации товара. Воспользуемся формулой – цена за 1 * количество проданных единиц.
    2. Переходим на лист с отчетом. Работа со сводными таблицами – параметры – изменить источник данных. Расширяем диапазон информации, которая должна войти в сводную таблицу.

    Если бы мы добавили столбцы внутри исходной таблицы, достаточно было обновить сводную таблицу.

    После изменения диапазона в сводке появилось поле «Продажи».

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

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

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

    Инструкция по добавлению пользовательского поля:

    1. Определяемся, какие функции будет выполнять виртуальный столбец. На какие данные сводной таблицы вычисляемое поле должно ссылаться. Допустим, нам нужны остатки по группам товаров.
    2. Работа со сводными таблицами – Параметры – Формулы – Вычисляемое поле.
    3. В открывшемся меню вводим название поля. Ставим курсор в строку «Формула». Инструмент «Вычисляемое поле» не реагирует на диапазоны. Поэтому выделять ячейки в сводной таблице не имеет смысла. Из предполагаемого списка выбираем категории, которые нужны в расчете. Выбрали – «Добавить поле». Дописываем формулу нужными арифметическими действиями.
    4. Жмем ОК. Появились Остатки.

    Группировка данных в сводном отчете

    Для примера посчитаем расходы на товар в разные годы. Сколько было затрачено средств в 2012, 2013, 2014 и 2015. Группировка по дате в сводной таблице Excel выполняется следующим образом. Для примера сделаем простую сводную по дате поставки и сумме.

    Щелкаем правой кнопкой мыши по любой дате. Выбираем команду «Группировать».

    В открывшемся диалоге задаем параметры группировки. Начальная и конечная дата диапазона выводятся автоматически. Выбираем шаг – «Годы».

    Получаем суммы заказов по годам.

    По такой же схеме можно группировать данные в сводной таблице по другим параметрам.

    Источник: exceltable.com

    Сводная таблица по нескольким диапазонам данных

    Постановка задачи

    Сводные таблицы – один из самых замечательных инструментов в Excel. Но до сих пор, к сожалению, ни одна из версий Excel не умеет “на лету” делать такой простой и нужной вещи как построение сводной по нескольким исходным диапазонам данных, находящимся, например, на разных листах или в разных таблицах:

    Прежде, чем начать давайте уточним пару моментов. Априори я полагаю, что в наших данных выполняются следующие условия:

    • Таблицы могут иметь любое количество строк с любыми данными, но обязательно – одинаковую шапку.
    • На листах с исходными таблицами не должно быть лишних данных. Один лист – одна таблица. Для контроля советую использовать сочетание клавиш Ctrl + End , которое перемещает вас на последнюю использованную ячейку листа. В идеале – это должна быть последняя ячейка таблицы с данными. Если при нажатии на Ctrl + End выделяется какая-либо пустая ячейка правее или ниже таблицы – удалите после таблицы эти пустые столбцы справа или строки снизу и сохраните файл.

    Способ 1. Сборка таблиц для сводной с помощью Power Query

    Начиная с 2010 версии для Excel существует бесплатная надстройка Power Query, которая умеет собирать и трансформировать любые данные и отдавать их потом как источник для построения сводной таблицы. Решить нашу задачу с помощью этой надстройки совсем несложно.

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

    Затем на вкладке Данные (если у вас Excel 2016 или новее) или на вкладке Power Query (если у вас Excel 2010-2013) выберем команду Создать запрос – Из файла – Excel (Get Data – From file – Excel) и укажем исходный файл с таблицами, которые надо собрать:

    В появившемся окне выберем любой лист (не принципиально какой именно) и внизу жмем кнопку Изменить (Edit) :

    Поверх Excel должно открыться окно редактора запросов Power Query. В правой части окна на панели Параметры запроса удалим все автоматически созданные шаги кроме первого – Источник (Source) :

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

    Удалим все столбцы, кроме колонки Data, щелкнув по заголовку столбца правой кнопкой мыши и выбрав команду Удалить другие столбцы (Remove other columns) :

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

    Если вы всё сделали правильно, то на этом моменте должны увидеть содержимое всех таблиц, собранных друг под другом:

    Осталось поднять первую строку в шапку таблицы кнопкой Использовать первую строку в качестве заголовков (Use first row as headers) на вкладке Главная (Home) и удалить попавшие в данные повторяющиеся шапки таблиц с помощью фильтра:

    Сохраним всё проделанное с помощью команды Закрыть и загрузить – Закрыть и загрузить в. (Close & Load – Close & Load to. ) на вкладке Главная (Home) , а в открывшемся окне выберем опцию Только подключение (Connection Only) :

    Всё. Осталось только построить сводную. Для этого идём на вкладку Вставка – Сводная таблица (Insert – Pivot Table) , выбирыем опцию Использовать внешний источник данных (Use external data source) , а затем, нажав кнопку Выбрать подключение, наш запрос. Дальнейшее создание и настройка сводной происходит совершенно стандартным образом путем перетаскивания нужных нам полей в области строк, столбцов и значений:

    Если в будущем изменятся исходные данные или добавится еще несколько листов-магазинов, то достаточно будет обновить запрос и нашу сводную с помощью команды Обновить все на вкладке Данные (Data – Refresh All) .

    Способ 2. Объединяем таблицы SQL-командой UNION в макросе

    Еще одно решение нашей задачи представлено вот таким макросом, который создает набор данных (cache) для сводной таблицы, используя команду UNION языка запросов SQL. Эта команда объединяет таблицы со всех указанных в массиве SheetNames листов книги в единую таблицу данных. То есть вместо физического копирования-вставки диапазонов с разных листов на один мы делаем то же самое в оперативной памяти компьютера. Потом макрос добавляет новый лист с заданным именем (переменная ResultSheetName) и создает на нем полноценную(!) сводную на основе собранного кэша.

    Чтобы воспользоваться макросом используйте кнопку Visual Basic на вкладке Разработчик (Developer) или сочетание клавиш Alt + F11 . Затем вставляем новый пустой модуль через меню Insert – Module и копируем туда следующий код:

    Готовый макрос потом можно запустить сочетанием клавиш Alt + F8 или кнопкой Макросы на вкладке Разработчик (Developer – Macros) .

    Минусы такого подхода:

    • Данные не обновляются, т.к. кэш не имеет связи с исходными таблицами. При изменении исходных данных надо запустить макрос еще раз и построить сводную заново.
    • При изменении количества листов необходимо правки в код макроса (массив SheetNames).

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

    Техническое замечание: если при запуске макроса вы получаете сообщение об ошибке вида “Provider not registered”, то скорее всего у вас 64-битная версия Excel или установлена не полная версия Office (нет Access). Чтобы исправить ситуацию замените в коде макроса фрагмент:

    И скачайте и установите бесплатный движок обработки данных из Access с сайта Microsoft – Microsoft Access Database Engine 2010 Redistributable

    Способ 3. Мастер консолидации сводных таблиц из старых версий Excel

    Этот способ немного устарел, но тоже стоит упоминания. Формально говоря, во всех версиях до 2003 включительно в мастере сводных таблиц была опция “построить сводную по нескольким диапазонам консолидации”. Однако, отчет, построенный таким образом, к сожалению, будет лишь жалким подобием настоящей полноценной сводной и не поддерживает многие “фишки” обычных сводных таблиц:

    В такой сводной нет заголовков столбцов в списке полей, нет гибкой настройки структуры, ограничен набор используемых функций и, в общем и целом, все это слабо похоже на сводную таблицу. Возможно именно поэтому начиная с 2007 года Microsoft эту функцию убрали из стандартного диалога при создании отчетов сводных таблиц. Теперь эта возможность доступна только через настраиваемую кнопку Мастер сводных таблиц (Pivot Table Wizard) , которую при желании можно добавить на панель быстрого доступа через Файл – Параметры – Настройка панели быстрого доступа – Все команды (File – Options – Customize Quick Access Toolbar – All Commands) :

    После нажатия на добавленную кнопку нужно выбрать на первом шаге мастера соответствующую опцию:

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

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

    Источник: www.planetaexcel.ru

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

    Очень часто при формировании отчётов (аналитики) основанных на больших объемах данных приходиться сводить в одну таблицу информацию с нескольких листов книги «Excel» .
    В идеале для сбора и аналитики информации с разных листов «Эксель» желательно сделать сводную таблицу, которая будет брать данные из нескольких источников (листов книги).

    Такую таблицу можно выполнить двумя разными способами.

    1. Создание сводной таблицы с разных листов при помощи стандартных возможностей и инструментов.
    2. Создание таблицы берущей данные с нескольких листов при помощи запроса сформированного в надстройке Power Query .
    Рассмотрим первый способ.

    Создание сводной таблицы с разных листов при помощи стандартных возможностей и инструментов.

    Шаг первый.

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

    Для этого следует кликнуть правой кнопкой мыши по ленте (панели инструментов) и выбрать из выпадающего меню пункт «Настройка ленты»

    Настройка ленты

    либо войти во вкладку

    «Файл» => «Параметры» => «Настройка ленты».

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

    Из списка выбираем «Мастер сводных таблиц и диаграмм»

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

    В правом окне при помощи кнопки «Создать группу» создаем новую группу инструментов. Для группы можно выбрать удобное для Вас наименование. Например, «Своя группа». Можно выбрать на какой вкладке будет создана группа. В своем примере я выбрал вкладку «Главная».

    Когда группа создана, выделите ее курсором, выделите курсором «Мастер сводных таблиц и диаграмм» в левом окне и нажмите кнопку «Добавить >>».

    После нажмите «Ок».

    Теперь на главной вкладке панели инструментов находится инструмент «Мастер сводных таблиц и диаграмм».

    Мастер сводных на панели

    Шаг второй. Построение сводной таблицы из нескольких источников данных.

    • Кликнуть по кнопке мастера построения сводных таблиц.
    • На первом окне поставить флажок, напротив «в нескольких диапазонах консолидации» и флажок напротив «сводная таблица»

    Консолидация диапазонов
    Во втором окне выбрать «Создать одно поле страницы»

    Поле сводной
    В третьем окне добавить все диапазоны, которые Вы хотите консолидировать(соединить в сводной таблице).

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

    На существующий лист

  • Нажать кнопку «Готово».
  • Второй способ.

    Создание таблицы берущей данные с нескольких листов при помощи запроса сформированного в надстройке Power Query .

    Данный способ заключается в использовании запроса надстройки Power Query.

    О данной надстройке рассказывалось в статье: «Power Query» в «Excel» — что это?

    Создание запроса Power Query для сведения нескольких страниц книги в одну таблицу.

    Шаг первый.

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

    Шаг два.

    Для этого во вкладке Power Query нужно нажать кнопку «Из таблицы» и указать в появившемся окне диапазон – источник данных. После чего нажать «Ок».

    Power Query из таблицы

    Шаг три.

    Когда создан второй запрос, нужно во вкладке Power Query кликнуть по кнопке «Слияние запросов» и настроит в появившемся окне вид получившейся общей таблицы.

    Слияние запросов

    Шаг четыре.

    Когда вид настроен, нужно нажать кнопку «Закрыть и загрузить.»

    Закрыть и загрузить

    Надстройка Power Query соберет данные с двух листов и соединит их в одной таблице.

    Источник: ruexcel.ru

    Сводная таблица по нескольким диапазонам данных

    Постановка задачи

    Сводные таблицы – один из самых замечательных инструментов в Excel. Но до сих пор, к сожалению, ни одна из версий Excel не умеет “на лету” делать такой простой и нужной вещи как построение сводной по нескольким исходным диапазонам данных, находящимся, например, на разных листах или в разных таблицах:

    Прежде, чем начать давайте уточним пару моментов. Априори я полагаю, что в наших данных выполняются следующие условия:

    • Таблицы могут иметь любое количество строк с любыми данными, но обязательно – одинаковую шапку.
    • На листах с исходными таблицами не должно быть лишних данных. Один лист – одна таблица. Для контроля советую использовать сочетание клавиш Ctrl + End , которое перемещает вас на последнюю использованную ячейку листа. В идеале – это должна быть последняя ячейка таблицы с данными. Если при нажатии на Ctrl + End выделяется какая-либо пустая ячейка правее или ниже таблицы – удалите после таблицы эти пустые столбцы справа или строки снизу и сохраните файл.

    Способ 1. Сборка таблиц для сводной с помощью Power Query

    Начиная с 2010 версии для Excel существует бесплатная надстройка Power Query, которая умеет собирать и трансформировать любые данные и отдавать их потом как источник для построения сводной таблицы. Решить нашу задачу с помощью этой надстройки совсем несложно.

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

    Затем на вкладке Данные (если у вас Excel 2016 или новее) или на вкладке Power Query (если у вас Excel 2010-2013) выберем команду Создать запрос – Из файла – Excel (Get Data – From file – Excel) и укажем исходный файл с таблицами, которые надо собрать:

    В появившемся окне выберем любой лист (не принципиально какой именно) и внизу жмем кнопку Изменить (Edit) :

    Поверх Excel должно открыться окно редактора запросов Power Query. В правой части окна на панели Параметры запроса удалим все автоматически созданные шаги кроме первого – Источник (Source) :

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

    Удалим все столбцы, кроме колонки Data, щелкнув по заголовку столбца правой кнопкой мыши и выбрав команду Удалить другие столбцы (Remove other columns) :

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

    Если вы всё сделали правильно, то на этом моменте должны увидеть содержимое всех таблиц, собранных друг под другом:

    Осталось поднять первую строку в шапку таблицы кнопкой Использовать первую строку в качестве заголовков (Use first row as headers) на вкладке Главная (Home) и удалить попавшие в данные повторяющиеся шапки таблиц с помощью фильтра:

    Сохраним всё проделанное с помощью команды Закрыть и загрузить – Закрыть и загрузить в. (Close & Load – Close & Load to. ) на вкладке Главная (Home) , а в открывшемся окне выберем опцию Только подключение (Connection Only) :

    Всё. Осталось только построить сводную. Для этого идём на вкладку Вставка – Сводная таблица (Insert – Pivot Table) , выбирыем опцию Использовать внешний источник данных (Use external data source) , а затем, нажав кнопку Выбрать подключение, наш запрос. Дальнейшее создание и настройка сводной происходит совершенно стандартным образом путем перетаскивания нужных нам полей в области строк, столбцов и значений:

    Если в будущем изменятся исходные данные или добавится еще несколько листов-магазинов, то достаточно будет обновить запрос и нашу сводную с помощью команды Обновить все на вкладке Данные (Data – Refresh All) .

    Способ 2. Объединяем таблицы SQL-командой UNION в макросе

    Еще одно решение нашей задачи представлено вот таким макросом, который создает набор данных (cache) для сводной таблицы, используя команду UNION языка запросов SQL. Эта команда объединяет таблицы со всех указанных в массиве SheetNames листов книги в единую таблицу данных. То есть вместо физического копирования-вставки диапазонов с разных листов на один мы делаем то же самое в оперативной памяти компьютера. Потом макрос добавляет новый лист с заданным именем (переменная ResultSheetName) и создает на нем полноценную(!) сводную на основе собранного кэша.

    Чтобы воспользоваться макросом используйте кнопку Visual Basic на вкладке Разработчик (Developer) или сочетание клавиш Alt + F11 . Затем вставляем новый пустой модуль через меню Insert – Module и копируем туда следующий код:

    Готовый макрос потом можно запустить сочетанием клавиш Alt + F8 или кнопкой Макросы на вкладке Разработчик (Developer – Macros) .

    Минусы такого подхода:

    • Данные не обновляются, т.к. кэш не имеет связи с исходными таблицами. При изменении исходных данных надо запустить макрос еще раз и построить сводную заново.
    • При изменении количества листов необходимо правки в код макроса (массив SheetNames).

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

    Техническое замечание: если при запуске макроса вы получаете сообщение об ошибке вида “Provider not registered”, то скорее всего у вас 64-битная версия Excel или установлена не полная версия Office (нет Access). Чтобы исправить ситуацию замените в коде макроса фрагмент:

    И скачайте и установите бесплатный движок обработки данных из Access с сайта Microsoft – Microsoft Access Database Engine 2010 Redistributable

    Способ 3. Мастер консолидации сводных таблиц из старых версий Excel

    Этот способ немного устарел, но тоже стоит упоминания. Формально говоря, во всех версиях до 2003 включительно в мастере сводных таблиц была опция “построить сводную по нескольким диапазонам консолидации”. Однако, отчет, построенный таким образом, к сожалению, будет лишь жалким подобием настоящей полноценной сводной и не поддерживает многие “фишки” обычных сводных таблиц:

    В такой сводной нет заголовков столбцов в списке полей, нет гибкой настройки структуры, ограничен набор используемых функций и, в общем и целом, все это слабо похоже на сводную таблицу. Возможно именно поэтому начиная с 2007 года Microsoft эту функцию убрали из стандартного диалога при создании отчетов сводных таблиц. Теперь эта возможность доступна только через настраиваемую кнопку Мастер сводных таблиц (Pivot Table Wizard) , которую при желании можно добавить на панель быстрого доступа через Файл – Параметры – Настройка панели быстрого доступа – Все команды (File – Options – Customize Quick Access Toolbar – All Commands) :

    После нажатия на добавленную кнопку нужно выбрать на первом шаге мастера соответствующую опцию:

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

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

    Источник: www.planetaexcel.ru

    Excel для финансиста

    Поиск на сайте

    Глава 7. Сводные таблицы

    Cводные таблицы – мощный и очень удобный инструмент Excel для анализа больших объёмов данных. С помощью таблиц можно легко получать сводные отчёты, видоизменяя и настраивая их несколькими щелчками мыши. Рассмотрим сразу на практическом примере.

    Скачайте файл svodnie-tablici. На листе данные этого файла находятся двести записей о продажах товаров (на практике число анализируемых записей обычно на один-два порядка больше). Каждая запись представляет собой строчку в таблице и содержит информацию:

    • Дата совершения продажи;
    • Наименование товара;
    • Наименование покупателя товара;
    • Сумма сделки.

    Относительно этих данных может возникнуть множество вопросов:

    • Какая общая сумма продаж?
    • Кто самый активный покупатель?
    • Какой самый популярный товар по общей сумме сделки?
    • Как распределены продажи в течение года, есть ли сезонность у товаров?
    • Растут или падают продажи в течение нескольких лет?

    На все эти вопросы помогают ответить сводные таблицы.

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

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

    Перед тем, как сделать сводную таблицу, нужно задать данные, которые будут в ней отражены. В нашем случае – вся таблица. Проще всего выделить таблицу, выбрав любую ячейку в ней и нажав Ctrl-A. Теперь в меню Вставка нажмите кнопку Сводная таблица, в открывшемся окне проверьте выбранный диапазон данных, выберите, что создание сводной таблицы произойдёт на новом листе, ОК.

    Поля сводной таблицы

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

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

    Всего несколько кликов мышкой, и первая сводная таблица в Excel готова! Программа уже посчитала суммы продаж в двух разрезах: по покупателям и товарам, и вывела общий итог. Таким образом программа берёт и структурирует данные. Можно немного доработать сводную таблицу. Выделите финансовые данные таблицы (диапазон B5:E9), задайте этим ячейкам финансовый формат, суммы стали нагляднее. Выделите ячейку Е5 (общий итог – покупатель Автоматика), нажмите меню Параметры, в разделе Сортировка – большую кнопку Сортировка, в открывшемся окне – Параметры сортировкиПо убыванию, ОК. Теперь и производители, и товары отсортированы по убыванию, ответы на первые три вопроса получены.

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

    Одно окошко было пока обойдено вниманием: Фильтр отчёта. Перенесите туда поле Покупатель. В ячейках А1-А2 появился фильтр выбора значений этого поля, это полезно для более детального анализа. Добавив простую диаграмму-график на основе данных сводной таблицы, получаем хороший аналитический инструмент: выбирая покупателя, можно смотреть динамику продаж по каждому товару.

    Скачать пример сводных таблиц Excel: svodnie-tablici

    Источник: finexcel.ru