Фильтрация полей отчета
Пожалуй, наиболее важной кнопкой сводной таблицы является кнопка фильтрации полей отчета. Выбрав конкретные пункты из раскрывающегося списка, прикрепленного к кнопке фильтрации, вы увидите в таблице только сводные данные для этого подмножества
Например, в нашей сводной таблице, в качестве поля фильтрации используется Дата. В результате можно отображать данные о расходах и доходах за конкретный период. Для этого сделайте следующее.
Щелкните на кнопке фильтрации по Дате, в раскрывающемся списке выберите «1 января», а затем щелкните на кнопке ОК. В результате отобразятся расходы и доходы только за 1 января.
Аналогичным образом можно выбирать несколько дат. Для этого установите галочку напротив пункта Выделить несколько элементов.
Если позже вы решите отображать расходы и доходы за весь период, снова установите флажок в поле (Все) раскрывающегося списка фильтрации и щелкните на кнопке ОК.
Как изменить внешний вид (формат) сводной таблицы
Про настройку форматирования сводной таблицы есть видеоролик:
В статье кратко остановимся на возможностях.
Изменить внешний вид сводной таблицы можно на вкладке Конструктор.
Выпадающий список Промежуточные итоги позволяет управлять выводом промежуточных итогов по строкам. Промежуточные итоги можно отключить совсем, или выводить в верхней или нижней части группы.
Общий итоги — позволяет управлять общими итогами для строк или столбцов, выводить их или отключать.
Макет отчета — позволяет изменять структуру таблицы. По умолчанию сводная таблица создается в сжатой форме. Но можно перестроить ее в форме структуры или в табличной форме.
Пустые строки — этот выпадающий список редко используется, позволяет добавлять пустую строку между группами, чтобы визуально отделить их.
Такую таблицу можно выполнить двумя разными способами.
- Создание сводной таблицы с разных листов при помощи стандартных возможностей и инструментов.
- Создание таблицы берущей данные с нескольких листов при помощи запроса сформированного в надстройке Power Query .
Рассмотрим первый способ.
Шаг первый.
Необходимо добавить в ленту инструментов кнопку мастера создания сводных таблиц и диаграмм.
Для этого следует кликнуть правой кнопкой мыши по ленте (панели инструментов) и выбрать из выпадающего меню пункт «Настройка ленты»
Настройка ленты
либо войти во вкладку
«Файл» => «Параметры» => «Настройка ленты».
Далее в настройках ленты из выпадающего списка под надписью «Выбрать команды» выбрать пункт «Все команды». В окне ниже отобразятся все возможные команды, которые можно разместить на панели, в алфавитном порядке.
Из списка выбираем «Мастер сводных таблиц и диаграмм»
Добавление мастера сводных таблиц
В правом окне при помощи кнопки «Создать группу» создаем новую группу инструментов. Для группы можно выбрать удобное для Вас наименование. Например, «Своя группа». Можно выбрать на какой вкладке будет создана группа. В своем примере я выбрал вкладку «Главная».
Когда группа создана, выделите ее курсором, выделите курсором «Мастер сводных таблиц и диаграмм» в левом окне и нажмите кнопку «Добавить >>».
После нажмите «Ок».
Теперь на главной вкладке панели инструментов находится инструмент «Мастер сводных таблиц и диаграмм».
Мастер сводных на панели
Шаг второй. Построение сводной таблицы из нескольких источников данных.
- Кликнуть по кнопке мастера построения сводных таблиц.
- На первом окне поставить флажок, напротив «в нескольких диапазонах консолидации» и флажок напротив «сводная таблица»
Консолидация диапазонов
Во втором окне выбрать «Создать одно поле страницы»
Поле сводной
В третьем окне добавить все диапазоны, которые Вы хотите консолидировать(соединить в сводной таблице).
Несколько диапазонов
В четвертом окне выбрать лист, на котором будет размещена сводная таблица.
На существующий лист
Нажать кнопку «Готово».
Данный способ заключается в использовании запроса надстройки Power Query.
О данной надстройке рассказывалось в статье: «Power Query» в «Excel» — что это?
Создание запроса Power Query для сведения нескольких страниц книги в одну таблицу.
Шаг первый.
Нужно создать два запроса, каждый из которых будет брать информацию из отдельной таблицы.
Шаг два.
Для этого во вкладке Power Query нужно нажать кнопку «Из таблицы» и указать в появившемся окне диапазон – источник данных. После чего нажать «Ок».
Power Query из таблицы
Шаг три.
Когда создан второй запрос, нужно во вкладке Power Query кликнуть по кнопке «Слияние запросов» и настроит в появившемся окне вид получившейся общей таблицы.
Слияние запросов
Шаг четыре.
Когда вид настроен, нужно нажать кнопку «Закрыть и загрузить.»
Закрыть и загрузить
Надстройка Power Query соберет данные с двух листов и соединит их в одной таблице.
Плюсы сводных таблиц
Отчеты в формате сводных таблиц обладают серьезными преимуществами перед другими типами отчетов в Excel. Рассмотрим каждое из них:
- Таблица составляется по практически любому объему данных.
- Редактировать вид отчета можно через меню форматов – встроенная библиотека содержит множество цветовых тем таблиц.
- Возможно объединение данных в более широкие группы, например несколько дат объединяются в кварталы.
- По результатам отчета можно провести расчеты с помощью инструментов Excel, это не повлияет на источники данных.
- Информация в сводной таблице может стать основой для составления графика или другого визуального отчета.
Как работать со сводными таблицами в Excel
Начнем с простейшего: добавления и удаления столбцов. Для примера рассмотрим сводную табличку продаж по разным отделам (см. выше).
Справа от сводной таблицы у нас была область задач, где мы выбирали столбцы в списке полей. Если она исчезла, просто щелкаем мышью по табличке.
Добавим в сводную таблицу еще одно поле для отчета. Для этого установим галочку напротив «Даты» (или напротив «Товара»). Отчет сразу меняется – появляется динамика продаж по дням в каждом отделе.
Сгруппируем данные в отчете по месяцам. Для этого щелкаем правой кнопкой мыши по полю «Дата». Нажимаем «Группировать». Выбираем «по месяцам». Получается сводная таблица такого вида:
Чтобы изменить параметры в сводной таблице, достаточно снять галочки напротив имеющихся полей строк и установить у других полей. Сделаем отчет по наименованиям товаров, а не по отделам.
А вот что получится, если мы уберем «дату» и добавим «отдел»:
А вот такой отчет можно сделать, если перетащить поля между разными областями:
Чтобы название строки сделать названием столбца, выбираем это название, щелкаем по всплывающему меню. Нажимаем «переместить в название столбцов». Таким способом мы переместили дату в столбцы.
Поле «Отдел» мы проставили перед наименованиями товаров. Воспользовавшись разделом меню «переместить в начало».
Покажем детали по конкретному продукту. На примере второй сводной таблицы, где отображены остатки на складах. Выделяем ячейку. Щелкаем правой кнопкой мыши – «развернуть».
В открывшемся меню выбираем поле с данными, которые необходимо показать.
Когда нажимаем на сводную таблицу, становится доступной закладка с параметрами отчета. С ее помощью можно менять заголовки, источники данных, группировать информацию.
Сводные диаграммы в Excel на основе простой и сводной таблицы
Сводная диаграмма в Excel похожа на сводную таблицу. Несколько отличаются режимы отображения. В сводную таблицу программа помещает подробные сведения. В диаграмму – итоги или сводки. Вместо строк и столбцов – ряды и категории. Каждый ряд имеет определенный цвет и представляет собой связанные точки данных. Категория – это одна точка с каждого ряда (на оси Х).
Как создать сводную диаграмму в Excel из нескольких таблиц
На разных листах одной книги находятся отчеты по продажам в нескольких магазинах:
Они имеют одинаковую структуру и одинаковые способы представления данных.
Прежде чем делать сводную диаграмму в Экселе, добавим на панель быстрого доступа кнопку «Мастера сводных таблиц и диаграмм». Через «Офис» заходим в «Параметры Excel» — «Настройка». Выбираем «Все команды». Находим инструмент «Мастер сводных таблиц и диаграмм». Жмем «Добавить».
На панели появится такой значок:
Переходим на новый лист. Вызываем «Мастера»:
- Первый шаг. Указываем, что будем создавать «сводную диаграмму со сводной таблицей» на основе данных, которые находятся «в нескольких диапазонах консолидации».
- Второй шаг. Меняем настройку по умолчанию – «создать поля страницы». Если оставить параметр «создать одно поле страницы», данные отобразятся путано.
- Третий шаг. Формируем список диапазонов, на основании которых будет построена сводная диаграмма. Сначала переходим на лист с первой таблицей – выделяем ее – жмем добавить. Переходим на лист со второй таблицей – повторяем все те же процедуры. Указываем количество полей – 1. Становится активным окошко «Первое поле». Даем ему название – «2013». Повторяем эту же операцию для каждого диапазона – названия «2014», «2015».
- Четвертый шаг. Переходим на лист, где будет находиться сводная диаграмма. Ставим курсор в первую ячейку диапазона для сводной таблицы (весь диапазон Excel сформирует самостоятельно).
После нажатия кнопки «Готово» получаем сводную диаграмму и таблицу следующего вида:
Пока на этом остановимся и рассмотрим другой метод создания сводной диаграммы.
Сводная диаграмма в Экселе на основе готовой сводной таблицы
Выбираем сводную таблицу, щелкнув в любом месте мышкой. После чего переходим на вкладку «Вставка-Гистограмма».
Выбираем простую гистограмму. В результате появляется диаграммы с данными из сводной таблицы и область фильтра. Зададим фильтр для значений в столбцах – отобразить продажи за 1 квартал 2013, 2014, 2015 гг.
Окно «Область фильтра сводной таблицы» не предназначено для изменения условий построения диаграммы. Гистограмма формируется только на основе данных в сводной таблице. Внести сюда значения из основных таблиц невозможно. Но добавленные в сводную таблицу новые значения отражаются в графике.
С помощью окна «Область фильтра» можно управлять сводной таблицей и диаграммой.
Сравнение продаж по кварталам за анализируемый период:
Анализ продаж в магазине «Серебрянка»:
Если для удобства необходимо переместить диаграмму на новый лист, щелкаем правой кнопкой по области построения и нажимаем «Переместить диаграмму».
В первом примере мы объединяли в одну диаграмму сразу несколько диапазонов. Но можно построить график и на основе одной базовой таблицы. Выделить ее – перейти на вкладку «Вставка».
Программа создаст одновременно сводную таблицу и диаграмму. Настраивайте поля – и анализируйте.
Сводная диаграмма в программе «Excel».
В программе «Excel» существует такое понятие, как сводная диаграмма.
Этот вид диаграмм строится на основании данных полученных из сводных таблиц.
Основным преимуществом таких диаграмм является их динамичность — возможность менять вид диаграммы в зависимости от отображенных полей сводной таблицы.
Например, установив фильтр в сводной таблице, мы можем выбирать (фильтровать) интересующие нас данные по заданным критериям и получать индивидуальную диаграмму (график) для каждого результата фильтрации.
Рассмотрим пример построения динамичной сводной диаграммы (графика).
Для начала следует создать сводную таблицу. Порядок создания сводных таблиц в статье:
Далее для построения сводной диаграммы нужно выполнить следующую последовательность действий:
- Поставить курсор на поле сводной таблицы.
- Войти во вкладку панели инструментов «Вставка».
- На вкладке «Вставка» выбрать тип диаграммы(Гистограмма, График, Круговая, Линейчатая, Точечная и т.д.)
После создания диаграммы в конструкторе (на вкладке «Конструктор») можно настроить ее внешний вид.
Теперь, когда у нас построена сводная диаграмма, мы можем менять параметры сводной таблицы. При изменении вида графика (диаграммы) будет изменяться сводная таблица. И наоборот при фильтрации данных в сводной таблице будет меняться диаграмма.
Использование таких диагармм удобно тем, что Вам не нужно строить множество графиков при подготовке отчета или презентации. Достаточно построить одну диаграмму, привязанную к сводной таблице, которую можно будет менять в процессе презентации использованием встроенных в «Excel» фильтров.
Сводная диаграмма в Экселе на основе готовой сводной таблицы
Выбираем сводную таблицу, щелкнув в любом месте мышкой. После чего переходим на вкладку «Вставка-Гистограмма».
Выбираем простую гистограмму. В результате появляется диаграммы с данными из сводной таблицы и область фильтра. Зададим фильтр для значений в столбцах – отобразить продажи за 1 квартал 2013, 2014, 2015 гг.
Окно «Область фильтра сводной таблицы» не предназначено для изменения условий построения диаграммы. Гистограмма формируется только на основе данных в сводной таблице. Внести сюда значения из основных таблиц невозможно. Но добавленные в сводную таблицу новые значения отражаются в графике.
С помощью окна «Область фильтра» можно управлять сводной таблицей и диаграммой.
Сравнение продаж по кварталам за анализируемый период:
Анализ продаж в магазине «Серебрянка»:
Если для удобства необходимо переместить диаграмму на новый лист, щелкаем правой кнопкой по области построения и нажимаем «Переместить диаграмму».
В первом примере мы объединяли в одну диаграмму сразу несколько диапазонов. Но можно построить график и на основе одной базовой таблицы. Выделить ее – перейти на вкладку «Вставка».
Программа создаст одновременно сводную таблицу и диаграмму. Настраивайте поля – и анализируйте.
Проблема: Имеются данные по нескольким тысячам жертвователей и их ежегодным пожертвованиям. Сводная таблица, построенная по этим данным, не сможет дать ясной картины, кто из жертвователей вносит больше всех или сколько жертвователей вносит пожертвования в той или иной категории.
Решение: Нужно построить сводную диаграмму. Графическое представление информации, которая собрана в сводной таблице, может пригодиться для презентации в PowerPoint, использования на собрании, в отчёте или для быстрого анализа. Сводная диаграмма даёт снимок интересующих данных (как и обычная диаграмма), но вдобавок она снабжена интерактивными фильтрами непосредственно из сводной таблицы, которые позволяют быстро анализировать различные срезы данных.
Сводная диаграмма в excel как сделать
На этом шаге мы рассмотрим использование сводных таблиц на различных примерах.
Пример 1. Использование сводных таблиц для консолидации данных
На рисунке 1 показаны три рабочих листа, каждый из которых содержит данные за месяц о продажах товаров в одном из магазинов крупной торговой сети. Наша цель — консолидировать эту информацию в одну сводную таблицу. В этом примере исходные данные содержатся в одной рабочей книге. Однако так бывает далеко не всегда. Данные, которые необходимо консолидировать, могут находиться в разных рабочих книгах.
Рис. 1. Рабочие листы, содержащие данные за месяц о продажах товаров
Для создания сводной таблицы выполните следующие действия.
- Добавьте новый лист, назовите его Итоги.
- Выберите команду Данные | Сводная таблица, чтобы запустить средство Мастер сводных таблиц и диаграмм.
- В первом диалоговом окне мастера выберите переключатель В нескольких диапазонах консолидации и щелкните на кнопке Далее.
- В следующем диалоговом окне Мастер сводных таблиц и диаграмм — шаг 2а из 3 выберите переключатель Создать одно поле страницы. Щелкните на кнопке Далее.
- Теперь определите диапазоны для консолидации. Первый диапазон — Магазин1!А$1:$D12 (его адрес можно ввести непосредственно или указать на рабочем листе). Щелкните на кнопке Добавить для добавления диапазона к списку Список диапазонов.
- Повторите пункт 5 для двух других диапазонов. Щелкните на кнопке Далее, чтобы снова перейти к диалоговому окну шага 3.
- В третьем диалоговом окне Мастер сводных таблиц и диаграмм щелкните на кнопке Готово.
В результате сводная таблица будет иметь вид:
Рис. 2. Сводная таблица
На четвертом шаге описанной процедуры в диалоговом окне Мастер сводных таблиц и диаграмм — шаг 2а из 3 можно выбрать переключатель Создать поля страницы. Это позволит назначить имя каждому элементу в поле страницы.
Файл с данным примером можно взять здесь.
Пример 2. Создание диаграмм по сводной таблице
Сводная диаграмма — это диаграмма, связанная со сводной таблицей. При использовании Мастера сводных таблиц и диаграмм можно одновременно создавать как сводную таблицу, так и сводную диаграмму. Для этого в первом диалоговом окне Мастер сводных таблиц и диаграмм установите переключатель Сводная диаграмма (со сводной таблицей).
Если Вы в этом окне щелкните на кнопке Готово, то Excel создаст новый рабочий лист для сводной таблицы и лист диаграмм для сводной диаграммы. На этих листах будут представлены шаблоны сводной таблицы и сводной диаграммы соответственно. Путем перетаскивания кнопок полей из панели инструментов Список полей сводной таблицы в шаблоны Вы создадите структуру сводной таблицы и сводной диаграммы.
Например, надо создать сводную диаграмму для следующей сводной таблицы (рис. 3).
Рис. 3. Пример сводной таблицы
Применяя вышеописанные действия, сводная диаграмма может быть следующей:
Рис. 4. Пример сводной диаграммы
Диаграмму можно создавать на основе уже существующей сводной таблицы. Для этого надо щелкнуть на кнопке Мастер диаграмм на панели инструментов Сводные таблицы. Excel создаст сводную диаграмму на отдельном листе диарамм. При изменении сводной таблицы сводная диаграмма обновляется автоматически.
Сводные диаграммы всегда строятся на отдельном листе диаграмм. Чтобы преобразовать сводную диаграмму во внедренную диаграмму рабочего листа, перейдите на лист сводной диаграммы и выполните команду Диаграмма | Размещение. В открывшемся диалоговом окне Размещение диаграммы выберите переключатель имеющимся и рабочий лист, на который нужно поместить диаграмму. Щелкните на кнопке ОК.
Пример 3. Анализ данных, полученных в результате опроса общественного мнения
На рисунке 5 показана часть необработанных данных в форме, которая обычно используется для исследования опроса общественного мнения. В каждой записи представлен ответ одного опрашиваемого.
Рис. 5. Пример таблицы, содержащей данные опроса общественного мнения
На рисунке 6 показана сводная таблица, в которой вычислены средние баллы для каждого из 12 вопросов исследования в зависимости от пола опрашиваемого. Дополнительные поля строк позволяют легко просмотреть результаты по возрастным группам и по конкретным городам. Для более сложных сводных таблиц можно перетащить одно или оба поля страницы в область строки или столбца.
Рис. 6. Пример сводной таблицы
На рисунке 7 показан другой рабочий лист. Этот лист содержит 12 отдельных сводных таблиц — по одной для каждого из вопросов. В каждой сводной таблице отображена частота ответов и их процентное соотношение.
Рис. 7. Пример одной из 12 сводных таблиц
Файл с данным примером можно взять здесь.
На следующем шаге мы рассмотрим анализ данных с помощью сценария «что-если».
Создаём сводную диаграмму
В Excel 2013 можно создать сводную диаграмму двумя способами. В первом случае мы используем достоинства инструмента «Рекомендуемые диаграммы» в Excel. Работая с этим инструментом, нам не потребуется сначала создавать сводную таблицу, чтобы далее из неё построить сводную диаграмму.
Второй способ – создать сводную диаграмму из существующей сводной таблицы, используя для этого уже созданные фильтры и поля.
Вариант 1: Создаём сводную диаграмму при помощи инструмента «Рекомендуемые диаграммы»
- Выделите данные, которые нужно показать на диаграмме.
- На вкладке Вставка (Insert) в разделе Диаграммы (Charts) нажмите кнопку Рекомендуемые диаграммы (Recommended Charts), чтобы открыть диалоговое окно Вставка диаграммы (Insert Chart).
- Диалоговое окно откроется на вкладке Рекомендуемые диаграммы (Recommended Charts), где в меню слева приведён список подходящих шаблонов диаграмм. В правом верхнем углу миниатюры каждого шаблона есть иконка сводной диаграммы:
- Кликните по любой диаграмме из списка рекомендованных, чтобы увидеть результат в области предварительного просмотра.
- Выберите, подходящий (или почти подходящий) тип диаграммы и нажмите ОК.
Слева от листа с данными будет вставлен новый лист, на котором будет создана сводная диаграмма (и сопутствующая сводная таблица).
Если ни одна из рекомендованных диаграмм не подходит, закройте диалоговое окно Вставка диаграммы (Insert Chart) и выполните шаги из Варианта 2, чтобы создать сводную диаграмму с чистого листа.
Вариант 2: Создаём сводную диаграмму из существующей сводной таблицы
- Кликните в любом месте сводной таблицы, чтобы на Ленте меню появилась группа вкладок Работа со сводными таблицами (PivotTable Tools).
- На вкладке Анализ (Analyze) нажмите кнопку Сводная диаграмма (Pivot Chart), при этом откроется диалоговое окно Вставка диаграммы (Insert Chart).
- В левой части диалогового окна выберите подходящий тип диаграммы. Далее выберите подтип диаграммы в верхней части окна. Будущая сводная диаграмма будет показана в области предварительного просмотра.
- Нажмите ОК, чтобы вставить сводную диаграмму на тот же лист, где находится исходная сводная таблица.
- После того, как сводная диаграмма создана, можно заняться настройкой её элементов и цветов при помощи списка полей на Ленте меню или иконок Элементы диаграммы (Chart Elements) и Стили диаграмм (Chart Styles).
- Посмотрите на получившуюся сводную диаграмму. Вы можете управлять фильтрами прямо на диаграмме, чтобы увидеть различные срезы данных. Здорово, правда!
Правила перепечаткиЕще больше уроков по Microsoft Excel
Оцените качество статьи
Нам важно ваше мнение:
Для чего нужно поле Фильтр в сводной таблице
Итак, мы разобрались со строками, столбцами и значениями в сводной таблице, научились их добавлять и удалять. Но зачем же нужно поле Фильтр в сводной таблице?
Перестроим таблицу таким образом: в строки добавим Наименование товаров, в столбцы — месяцы.
Предположим, нужно видеть не все названия товаров, а только по категории Инструмент.
Для этого нужно перетащить поле Категория товара в блок ФИЛЬТРЫ.
После этого над сводной таблицей появляется дополнительная строка с фильтрами.
По умолчанию выбраны все значения. Отфильтруем нужную нам категорию Инструмент. Для этого щелкнем на выпадающий список в фильтре и выделим нужную категорию. Сводная таблица уменьшилась, отобразив данные только по категории Инструмент.
Фильтровать значения в сводной таблице можно не только по одному параметру, но и по нескольким. Для этого нужно активировать “галочку” Выделить несколько элементов и установить “галочки” возле нужных.
К томе же, полей, по которым фильтруем данные, может быть несколько. Чтобы отфильтровать данные в сводной таблице не только по категории товара, но и по другим полям, их также нужно перетянуть в блок ФИЛЬТРЫ.
Как создать сводную таблицу?
Для начала убедитесь, что у Вас есть какие-то исходные данные на листе Excel. Перечень финансовых операций – самое типичное, что встречается. На самом деле, это может быть перечень чего угодно: контактные данные сотрудников, коллекция компакт-дисков или данные о расходе топлива Вашей компании.
Итак, запускаем Excel… и загружаем такой список…
После того, как мы открыли этот список в Excel, можем приступить к созданию сводной таблицы.
Выделите любую ячейку из этого списка:
Затем на вкладке Insert (Вставка) выберите команду PivotTable (Сводная таблица):
Появится диалоговое окно Create PivotTable (Создание сводной таблицы) с двумя вопросами для Вас:
- Какие данные использовать для создания новой сводной таблицы?
- Куда поместить сводную таблицу?
Так как на предыдущем шаге мы уже выбрали одну из ячеек списка, то для создания сводной таблицы будет выделен весь список автоматически. Заметьте, что мы можем выбрать другой диапазон, другую таблицу и даже какой-нибудь внешний источник данных, например, таблицу базы данных Access или MS-SQL. К тому же нам необходимо выбрать, где разместить новую сводную таблицу: на новом листе или на одном из существующих. В данном примере мы выберем вариант – New Worksheet (На новый лист):
Excel создаст новый лист и разместит на нем пустую сводную таблицу:
Как только мы кликнем по любой ячейке в сводной таблице, появится ещё одно диалоговое окно: PivotTable Field List (Поля сводной таблицы).
Список полей в верхней части диалогового окна – это перечень всех заголовков из исходного списка. Четыре пустые области в нижней части экрана позволяют указать сводной таблице, как требуется обобщить данные. Пока эти области пусты, в таблице тоже ничего нет. Всё, что от нас требуется, это перетащить заголовки из верхней области в пустые области внизу. При этом автоматически формируется сводная таблица, в соответствии с нашими инструкциями. Если мы допустили ошибку, то можно удалить заголовки из нижней области либо перетащить другие им на замену.
Область Values (Значения), вероятно, самая важная из четырёх. То, какой заголовок помещён в эту область, определяет, по каким данным будут подводиться итоги (сумма, среднее, максимум, минимум и т.д.) Это, почти всегда, численные значения. Отличный кандидат на место в этой области – данные под заголовком Amount (Стоимость) нашей исходной таблицы. Перетащим этот заголовок в область Values (Значения):
Обратите внимание, что заголовок Amount теперь отмечен галочкой, а в области Values (Значения) появилась запись Sum of Amount (Сумма по полю Amount), указывающая на то, что столбец Amount просуммирован. Если мы посмотрим на саму сводную таблицу, то увидим сумму всех значений из столбца Amount исходной таблицы
Если мы посмотрим на саму сводную таблицу, то увидим сумму всех значений из столбца Amount исходной таблицы.
Итак, наша первая сводная таблица создана! Удобно, но не особо впечатляет. Вероятно, мы хотим получить больше информации о наших данных, чем есть сейчас.
Обратимся к исходным данным и попробуем определить один или несколько столбцов, которые можно использовать, чтобы раздробить эту сумму. Например, мы можем сформировать нашу сводную таблицу таким образом, чтобы итоговая сумма продаж подсчитывалась для каждого продавца по отдельности. Т.е. в нашу сводную таблицу добавятся строки с именем каждого продавца компании и его итоговой суммой продаж. Чтобы достичь такого результата, достаточно перетащить заголовок Salesperson (Торговый представитель) в область Row Labels (Строки):
Становится интересней! Наша сводная таблица начинает обретать форму…
Видите преимущества? За пару кликов мы создали таблицу, которую вручную пришлось бы создавать очень долго.
Что ещё мы можем сделать? Ну, в определённом смысле, наша сводная таблица уже готова. Мы создали полезную сводку по исходным данным. Уже получена важная информация! В оставшейся части статьи мы разберём некоторые способы создания более сложных сводных таблиц, а также узнаем, как их настраивать.
Как выделить столбец на диаграмме?
По одному критерию
Давайте начнем с выделения столбцов.
Рассмотрим простую таблицу с одним показателем в виде продаж для каждого магазина:
Выделим полученный диапазон с данными $A$1:$F$2 и добавим на лист диаграмму, для этого в панели вкладок выбираем Вставка -> Диаграммы -> Гистограмма с группировкой.
Также давайте сразу ее немножко модифицируем и добавим на нее подпись данных (нажимаем правой кнопкой мыши по ряду и выбираем Добавить подпись данных) и уберем лишние детали (уберем сетку и вертикальную ось), чтобы они нам сейчас не мешали:
В результате получаем классическую диаграмму, вот теперь давайте подумаем как можно выделить столбец на диаграмме и в качестве критерия будем искать столбец с максимальным значением.
В данном примере это Магазин 4, т.е. 4 столбец. Какие у нас есть варианты?
Вариант изменять заливку подходящего столбца (вручную самостоятельно перекрашивать или делать с помощью макроса). Этот вариант не выглядит достаточно удобным так как это не даст нам возможности автоматического обновления при изменении данных.
Также есть вариант с добавлением дополнительного ряда на диаграмму, который мы с помощью формул можем прописать, и вот этот вариант нам уже вполне подойдет, так как даст нам возможность автоматизировать выделение столбца.
Поэтому давайте дополним нашу исходную таблицу вспомогательным рядом, в котором пропишем условие на выделение: если значение в исходном столбце удовлетворяет условию, то во вспомогательном ряду ставим значение из исходного ряда, в остальных же случаях ставим ноль. Другими словами, если значение максимально, то дублируем его во вспомогательный ряд, в остальных случаях пишем ноль.
Само условие прописываем с помощью функции ЕСЛИ (в английской версии IF), а поиск максимального значения функцией МАКС (английский вариант MAX):
Затем давайте расширим диапазон для построенной диаграммы и добавим в него новый вспомогательный ряд. Мы это можно сделать либо нажав правой кнопкой мыши по диаграмме, затем щелкнув Выбрать данные и задав новый диапазон ($A$1:$F$3), либо же активировав диаграмму и расширив диапазон с помощью появившейся рамки с диапазоном прямо на листе Excel.
Как мы видим новый ряд отобразился рядом с самым высоким столбцом, мы этого как раз и добивались когда прописывали вспомогательный ряд.
Теперь наша задача состоит в том, чтобы ряды оказались не рядом (как сейчас на картинке), а друг за другом. Для этого щелкаем правой кнопкой мыши по дополнительному ряду с данными и выбираем Формат ряда данных -> Параметры ряда и устанавливаем значение в параметре Перекрытие рядов равным 100%, в результате получаем:
Что произошло?
Наш дополнительный ряд оказался на переднем плане и тем самым как бы заслоняет собой основной ряд (оранжевый столбец находится к нам ближе чем синий), за счет чего и возникает представление, что мы поменяли раскраску столбца, хотя на самом деле просто добавили еще один ряд на график.
Теперь настроим внешний вид графика под свои предпочтения, я, к примеру, поменял стандартную раскраску столбцов и несколько увеличил их ширину с помощью настройки бокового зазора:
Диаграмма готова, и теперь при изменении максимального значения в таблице оно также поменяется и на графике в виде выделенного цветом столбца, вот так это выглядит при изменении значений в исходной таблице:
При этом если в таблице будет сразу несколько максимальных значений, то все они на графике также будут выделяться.
Перейдем к следующему примеру.
По нескольким критериям
Усложним задачу и теперь помимо выделения максимального значения на графике, также выделим и минимальное, таким образом, чтобы мы видели не только лучший результат, но и худший.
Алгоритм действий точно такой же, как и в предыдущем примере — добавляем в таблицу еще один дополнительный ряд, где прописываем условие на соответствие минимальному значению, поиск минимума можно сделать с помощью функции МИН (в английской версии MIN):
Расширяем диапазон (до $A$1:$F$4) для построения диаграммы и получаем еще один «отличительный» столбец на диаграмме, дополнительно также меняем заливку столбца по своим предпочтениям:
В принципе таких критериев выделения столбцов можно прописать сколь угодно много, однако если с этим переборщить, то обычная диаграмма может превратиться в слишком пестрый и непонятный график. Поэтому с критериями лучше не злоупотреблять и ограничиться 1-2 условиями.
Excel: как построить график функции или диаграмму в Эксель
После создания сводной таблицы в Excel 2016 вы можете создать сводную диаграмму, чтобы графически отобразить ее итоговые значения, выполнив два простых шага:
Нажмите кнопку командной строки PivotChart в группы инструментов на вкладке «Анализ» в контекстной вкладке «Сводная таблица инструментов», чтобы открыть диалоговое окно «Вставить диаграмму».
Помните, что контекстная вкладка PivotTable Tools с двумя вкладками — Analyze and Design — автоматически появляется всякий раз, когда вы нажимаете любую ячейку в существующей сводной таблице.
Нажмите миниатюру типа диаграммы, которую вы хотите создать, в диалоговом окне «Вставить диаграмму», а затем нажмите «ОК».
Как только вы нажмете OK после выбора типа диаграммы, Excel отобразит две вещи на том же рабочем листе, что и сводная таблица:
Сводная диаграмма , используя тип выбранной диаграммы, который вы можете перемещать и изменять размер по мере необходимости (официально известный как встроенная диаграмма )
Контекстная вкладка PivotChart Tools разделена на три вкладки — Анализ, Дизайн и Формат — каждый со своим набором кнопок для настройки и уточнения сводной диаграммы
Вы также можете создать сводную диаграмму с нуля, построив ее аналогично ручному созданию сводной таблицы. Просто выберите ячейку в таблице данных или списке, который нужно наметить, а затем выберите параметр PivotChart в раскрывающемся меню кнопки PivotChart на вкладке «Вставка» ленты (вместо этого выберите опцию PivotChart & PivotTable в этом выпадающем меню, если вы хотите создать сводную таблицу, а также сводную диаграмму). Затем Excel отображает диалоговое окно Create PivotChart с теми же параметрами, что и диалоговое окно Create PivotTable.
Диалоговое окно Создать сводную таблицу.
После выбора параметров и закрытия этого диалогового окна Excel отображает пустую сетку диаграмм и область задач PivotChart Fields вместе со контекстной вкладкой PivotChart Tools на ленте. Затем вы можете создать новую сводную диаграмму, перетащив нужные поля в соответствующие зоны.
В чем полезность сводных таблиц
Многие специалисты используют Microsoft Excel для вышеперечисленных целей, потому что программа сильно экономит время профессионала, забирая всю трудоемкую работу с данными на себя.
Еще отличительной чертой, из-за которой Excel стал популярным — создание достаточно нетривиальных средств анализа данных (Сводные таблицы входят в их число) не требует особых знаний по программированию.
Но перед тем, как начать пользоваться всеми «профитами» от Сводных таблиц Excel, нужно приложить немало усилий, чтобы хорошо освоить довольно сложную технику создания таблиц и работы с ними. Но в итоге, эффект от приобретения нового навыка, вот увидите, окупит потраченное время, энергию и силы.
В этой статье мы решили рассказать об основных технологиях создания Сводных таблиц в Excel и отметить некоторые особенности методов, которые могут Вам пригодиться в будущей работе.
Сначала предлагаем разобраться в том, что же такое Сводная таблица и «с чем ее едят».
Сводные таблицы — это перечень, содержащий выборочные данные из каких-либо источников (Базы Данных, таблицы Excel и другие), который нужен специалисту для дальнейшего анализа данных, изменения структуры таблицы и так далее.
Сводные таблицы позволяют менять визуальное представление данных, представляя их с новой точки зрения, не меняя сам источник.