Svinkovod.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

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

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

Данная статья поможет вам разобраться, как самому сделать сводную таблицу в Microsoft Excel.

Создание сводной таблицы в Excel: пошаговая инструкция для чайников

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

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

Метод 1: Обычная сводная таблица

Для реализации процесса мы будем использовать рабочую среду Microsoft Excel 2010. Но можете не беспокоиться, ведь инструкция применима так же и к остальным версиям.

Как пример будем использовать таблицу выплат зарплаты сотрудника какой-либо корпорации. У нас будут присутствовать все необходимые данные по типу пола, возраста, категории, суммы выплаты и естественно имени работника.

Однако теперь нам нужно совместить все эти случайно разбросанные данные в одном едином месте, при этом по условию выплаты нужны только за 3-й квартал 2016 года. Давайте посмотрим, как это можно реализовать.

Для начала изменим тип данной нам таблицы на динамичный. Это потом упростит форматирование строк в сводной таблице. Теперь кликаем ЛКМ по случайно ячейке в таблице.

После переключаемся в меню «Стили» и там находим строчку «Форматировать как таблицу«. Выберете любой стиль и двигайтесь дальше по инструкции.

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

В появившемся окошке указываем адрес нашей таблицы. После нажимаем на «ОК«.

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

Теперь ваша таблица автоматически форматируется в зависимости от полученных ею данных. Так же при желании вы можете изменить её имя в появившемся разделе «Конструктор«.

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

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

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

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

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

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

После проделанной процедуры у вас слева отобразится поле настройки сводной таблицы.

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

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

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

Мы остановились на том, что перетащили «Дата» и «Пол» в поле «Фильтр отчета«. «Категория персонала» ушла в «Названия столбцов«, а «Имя» переместилось в «Название строк«. В «Значения» у нас попала «Сумма заработной платы«.

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

Действие 10:

Итог у нас вышел такой.

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

Метод 2: Мастер сводных таблиц

Есть один инструмент, называемый «Мастер сводных таблиц», который поможет автоматизировать вышеизложенный процесс. Однако перед тем, как это реализовывать, необходимо отобразить его в «Панель быстрого доступа«.

Переключитесь в раздел «Файл«, после чего найдите строчку «Параметры» и нажмите на неё.

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

Там ищем строчку «Панель быстрого доступа» и кликаем по ней. Справа в левом поле находим функцию «Мастер сводных таблиц и диаграмм» и нажатием на кнопку «Добавить >>» перетаскиваем её в правое поле. После этого нажимаем на «ОК«.

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

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

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

Откроется меню настроек в три шага.Тут есть несколько вариантов того, откуда можно браться данные для сводной таблицы, вам лишь надо указать, что «Вид создаваемого отчета» будет «Сводная таблица«. После этого переходим к следующему шагу с помощью «Далее«.

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

В поле вводим адрес нашей таблицы, откуда будут браться исходные данные. После нажимаем на «Далее«.

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

Теперь выбираем, каким типом у нас будет сохраняться сводная таблица. После выбора кликаем по «Готово«.

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

Мы указали «Новый лист«, поэтому сводная таблица приняла точно такой же вид, как и в первом варианте.

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

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

Настройка сводной таблицы

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

Читайте так же:
Как в документе ворд сделать таблицу

Возле строчки «Дата» находим стрелочку с выпадающим списком. Там указываем снизу галочку возле «Выделить несколько элементов«. И после этого убираем ненужные нам периоды. Теперь кликаем по «ОК«.

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

Точно так же можно настроить отображение и по полу, если вам это понадобится.

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

В итоге у нас получилась следующая сводная таблица.

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

Покажем вам то, что информация в данной таблице находится в динамическом состоянии, то есть она изменяется в зависимости от условий.

Для этого откройте раздел «Параметры» и найдите там строчку «Список полей«. Изменяем местоположение «Дата» на «Название строк«. Так же меняем местами параметры «Категория персонала» и «Пол«.

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

Готово. Мы только изменили внешний вид таблицы.

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

Если вместо параметра «Имя» поставить на первое место «Дата» в поле «Названия строк«, то именно даты выплат будут группироваться по именам работников.

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

Мы можем пойти дальше и наглядно отобразить все числовые данные из таблицы. Сделать это можно, если перейти в раздел «Главная» и там нажать на «Условное форматирование«. В появившемся списке кликаем по строчке «Гистограммы«, где уже можно выбраться внешний вид нашей гистограммы.

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

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

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

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

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

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

Мастер сводных таблиц в excel 2010

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

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

Итоговый консолидированный отчет сводной таблицы может содержать следующие поля в области Список полей сводной таблицы, добавляемой в сводную таблицу: «Строка», «Столбец» и «Значение». Кроме того, в отчет можно включить до четырех полей фильтра, которые называются «Страница1», «Страница2», «Страница3» и «Страница4».

Настройка исходных данных

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

Поля страницы при консолидации данных

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

Использование именованных диапазонов

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

Другие способы консолидации данных

В Excel также доступны другие способы консолидации данных, которые позволяют работать с данными в разных форматах и макетах. Например, вы можете создавать формулы с объемными ссылками или использовать команду Консолидация (доступную на вкладке Данные в группе Работа с данными).

Консолидация нескольких диапазонов

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

Читайте так же:
Два интервала в ворде как это

Консолидация данных без использования полей страницы

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

Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:

Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды.

В списке Выбрать команды из выберите пункт Все команды.

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

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

Щелкните значок мастера на панели быстрого доступа.

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

На странице Шаг 2а выберите параметр Создать поля страницы, а затем нажмите кнопку Далее.

На странице Шаг 2б сделайте следующее:

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

Совет: Если диапазон ячеек находится в другой книге, сначала откройте ее, чтобы упростить выбор данных.

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

На странице Шаг 3 укажите, следует ли добавлять сводную таблицу на новый или же на существующий лист, и нажмите кнопку Готово.

Консолидация данных с использованием одного поля страницы

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

Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:

Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды.

В списке Выбрать команды из выберите пункт Все команды.

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

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

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

На странице Шаг 2а выберите параметр Создать одно поле страницы, а затем нажмите кнопку Далее.

На странице Шаг 2б сделайте следующее:

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

Совет: Если диапазон ячеек находится в другой книге, сначала откройте ее, чтобы упростить выбор данных.

Нажмите кнопку Далее.

На странице Шаг 3 укажите, следует ли добавлять сводную таблицу на новый или же на существующий лист, и нажмите кнопку Готово.

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

Вы можете создать несколько полей страницы и назначить имена элементов каждому из исходных диапазонов. Это позволяет выполнять частичную или полную консолидацию. Например, в одном поле страницы могут консолидироваться данных отдела маркетинга и отдела продаж без данных производственного отдела, а в другом поле — данные всех трех отделов. Чтобы создать консолидацию с использованием нескольких полей страницы, сделайте следующее:

Добавьте мастер сводных таблиц и диаграмм на панель быстрого доступа. Для этого:

Щелкните стрелку рядом с панелью инструментов и выберите Дополнительные команды.

В списке Выбрать команды из выберите пункт Все команды.

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

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

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

На странице Шаг 2а выберите параметр Создать поля страницы, а затем нажмите кнопку Далее.

На странице Шаг 2б сделайте следующее:

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

Совет: Если диапазон ячеек находится в другой книге, сначала откройте ее, чтобы упростить выбор данных.

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

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

Если в разделе Во-первых, укажите количество полей страницы сводной таблицы задано число 1, выберите каждый из диапазонов, а затем введите уникальное имя в поле Первое поле. Если у вас четыре диапазона, каждый из которых соответствует кварталу финансового года, выберите первый диапазон, введите имя «Кв1», выберите второй диапазон, введите имя «Кв2» и повторите процедуру для диапазонов «Кв3» и «Кв4».

Если в разделе Во-первых, укажите количество полей страницы сводной таблицы задано число 2, выполните аналогичные действия в поле Первое поле. Затем выберите два диапазона и введите в поле Второе поле одинаковое имя, например «Пг1» и «Пг2». Выберите первый диапазон и введите имя «Пг1», выберите второй диапазон и введите имя «Пг1», выберите третий диапазон и введите имя «Пг2», выберите четвертый диапазон и введите имя «Пг2».

Читайте так же:
Можно ли варить сваркой в дождь

Нажмите кнопку Далее.

На странице Шаг 3 укажите, следует ли добавлять сводную таблицу на новый или же на существующий лист, и нажмите кнопку Готово.

Мастер сводных таблиц в excel 2010

На этом шаге мы рассмотрим этапы создания сводной таблицы.

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

Если выбрать эту команду, то появится первое из трех диалоговых окон (рис. 1). На этом этапе определяется источник данных.

Рис. 1. Диалоговое окно Мастер сводных таблиц и диаграмм — шаг 1 из 3

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

Данные в списке Excel

Чаще всего анализируемые данные хранятся в базе данных рабочего листа, который называется списком . База данных, хранящаяся в рабочем листе, не может иметь больше 65 535 записей и 256 полей. В первой строке базы данных должны содержаться названия полей. Данные могут состоять из чисел, текста или формул.

Данные во внешнем источнике данных

Если для создания сводной таблицы используется внешняя база данных, то данные вводятся в рабочий лист с помощью отдельного приложения Microsoft Query , входящего в поставку Excel или Office . Можно использовать файлы dBASE , данные SQL -сервера или другие источники. Выбор источника данных осуществляется на втором шаге работы Мастера сводных таблиц и диаграмм .

Данные в диапазонах консолидации

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

Данные в другой сводной таблице

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

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

Чтобы перейти к следующему этапу работы Мастера сводных таблиц и диаграмм , щелкните на кнопке Далее . На втором шаге этот мастер выдает запрос об источнике исходных данных. Вид диалогового окна будет зависеть от сделанного выбора в первом окне. На рисунке 2 показано диалоговое окно, появляющееся в случае, если на первом этапе в качестве источника данных был выбран список на рабочем листе Excel .

Рис. 2. Диалоговое окно Мастер сводных таблиц и диаграмм — шаг 2 из 3

Если при выполнении команды Данные | Сводная таблица табличный курсор находился на одном из элементов списка, то Excel автоматически определит диапазон ячеек базы данных на втором этапе работы Мастера сводных таблиц и диаграмм . Чтобы открыть другой рабочий лист и выбрать диапазон ячеек, надо щелкнуть на кнопке Обзор . Чтобы перейти к третьему этапу, надо щелкнуть на кнопке Далее .

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

Рис. 3. Диалоговое окно Мастер сводных таблиц и диаграмм — шаг 3 из 3

Если вы выбрали переключатель Новый лист , то Excel вставит новый рабочий лист для сводной таблицы в текущую рабочую книгу. Если выбран переключатель Существующий лист , то сводная таблица появится на текущем рабочем листе (можно указать ее положение на листе).

Если в этом окне мастера щелкнуть на кнопке Параметры , то откроется диалоговое окно Параметры сводной таблицы , в котором можно определить параметры отображения данных в сводной таблице. Щелкните на кнопке ОК для возврата в диалоговое окно Мастера сводных таблиц и диаграмм .

Рис. 4. Диалоговое окно Параметры сводной таблицы

Это диалоговое окно содержит следующие опции:

  • Имя . Это поле позволяет определить название сводной таблицы. Excel по умолчанию назьшает их СводнаяТаблица1 , СводнаяТаблица2 и т.д.
  • Общая сумма по столбцам . Установите флажок этой опции, если необходимо подвести общие итоги по столбцам.
  • Общая сумма по строкам . Установите флажок этой опции, если необходимо подвести общие итоги по строкам.
  • Автоформат . Если необходимо применить средство автоматического форматирования Excel , то установите этот флажок. Excel будет использовать автоматическое форматирование, даже если вы измените макет таблицы.
  • Включать скрытые значения . Установите флажок этой опции, если необходимо включать скрытые элементы поля страницы в промежуточные итоги.
  • Объединить ячейки заголовков . Установите флажок этой опции, если необходимо объединить ячейки для внешних заголовков строк и столбцов. В результате таблицу будет легче читать.
  • Сохранять форматирование . Установите флажок этой опции, если необходимо сохранять любое ваше форматирование при изменении сводной таблицы.
  • Повторять подписи на каждой странице печати . Установите флажок этой опции, если необходимо, чтобы заголовки строк печатались на каждой странице отчета сводной таблицы.
  • Макет страницы . С помощью этой опции можно указать порядок следования полей страниц.
  • Число полей в столбце . С помощью этой опции можно указать количество выводимых полей страницы в строке или столбце перед выводом следующей строки или столбца.
  • Для ошибок отображать . Можно указать значение, которое будет выводиться в ячейках сводной таблицы при возникновении ошибки.
  • Для пустых ячеек отображать . Можно указать значение, которое будет выводиться в пустых ячейках.
  • Печать заголовков . Установите флажок этой опции, если необходимо на каждой странице отчета сводной таблицы распечатывать заголовки столбцов.
  • Сохранить данные вместе с таблицей . Если эта опция активизирована, то Excel сохраняет дополнительную копию данных, которая называется "кэш сводной таблицы", вместе с рабочей книгой. Это позволяет гораздо быстрее пересчитывать сводную таблицу при ее изменении. Если на вашем компьютере не хватает оперативной памяти, снимите флажок этой опции, тогда обновление будет происходить немного медленнее.
  • Развертывание разрешено . Если данная опция активизирована, то после двойного щелчка на ячейке сводной таблицы выводятся дополнительные сведения для этой ячейки.
  • Обновить при открытии . Если эта опция активизирована, то после открытия книги сводная таблица будет обновлена автоматически.
  • Сохранить пароль . При использовании внешней базы данных, которая требует пароля, эта опция позволяет сохранить пароль как часть запроса, после чего не требуется его ввод.
  • Фоновый запрос . Если эта опция активизирована, то запрос к внешней базе данных будет выполняться в фоновом режиме, и вы сможете продолжить работу в Excel .
  • Оптимизировать память . С помощью этой опции можно уменьшить общее количество памяти, которая используется при обновлении запроса к внешней базе данных.

Для создания макета сводной таблицы Excel предоставляет два способа:

  • с помощью мастера сводных таблиц и диаграмм;
  • самостоятельно на рабочем листе с использованием инструментов панели Сводные таблицы (рис. 5).

Рис. 5. Панель инструментов Сводные таблицы

Создание макета сводной таблицы с помощью мастера

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

Рис. 6. Диалоговое окно Мастер сводных таблиц и диаграмм — макет

Макет сводной таблицы имеет четыре области:

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

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

При перетаскивании кнопки поля в область данных Мастер сводных таблиц по умолчанию применит функцию СУММ , если поле содержит числовые значения, и функцию СЧЁТ — если поле содержит нечисловые значения.

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

Если случайно перетащили поле не в ту область, то необходимо выведите его за пределы макета, чтобы удалить из области. На рисунке 7 показано диалоговое окно после перетаскивания некоторых кнопок полей в сводную таблицу. Эта сводная таблица будет отображать сумму по полю Счет в зависимости от типа счета (поле строки Тип ) и клиента (поле столбца Клиент ). А поле Отделение будет использоваться в качестве поля страницы. Чтобы вернуться к диалоговому окну Мастера сводных таблиц и диаграмм , нужно щелкнуть на кнопке ОК .

Рис. 7. Готовый макет сводной таблицы

Создание макета сводной таблицы с помощью панели инструментов

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

Выполните два первых шага Мастера сводных таблиц и диаграмм . Укажите местоположение сводной таблицы во втором диалоговом окне Мастера сводных таблиц и диаграмм и щелкните на кнопке Готово в этом окне. Excel отобразит в рабочем листе шаблон сводной таблицы, как показано на рисунке 8.

Рис. 8. Готовый макет сводной таблицы

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

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

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

Сводная таблица из нескольких листов «Excel».

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

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

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

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

Шаг первый.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Второй способ.

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

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

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

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

Шаг первый.

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

Шаг два.

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

Power Query из таблицы

Power Query из таблицы

Шаг три.

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

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

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

Шаг четыре.

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

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

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

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector