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

Как создается сводная таблица в Excel

Перед составлением сводной таблички необходимо проверить соответствуют ли ее компоненты нескольким критериям

Если не обратить на это внимание, в дальнейшем могут возникнуть проблемы. Условия таковы:

• над каждым столбцом есть шапка с заголовком;
• каждая ячейка в таблице заполнена;
• установлены форматы ячеек (например, только формат «Дата» для дат);
• в одной ячейке указываются данные только одного формата;
• необходимо разделить объединенные ячейки.

Рассмотрим два метода создания табличного отчета.

Классический способ составления сводной таблицы

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

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

1. Нажимаем на одну из ячеек источника и открываем вкладку «Главная» в верхней части экрана. Нужно найти раздел «Стили», а в нем – функцию «Форматировать как таблицу». Выбираем понравившийся стиль.

1

1. На экране возникнет окошко, куда требуется добавить диапазон данных. Обычно строка уже заполнена, остается проверить координаты, поставить галочку в графе «Таблица с заголовками» и нажать «ОК».

2

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

1. Необходимо открыть вкладку «Вставка», кликнуть по пункту «Сводная таблица» в левой части экрана.

3

1. На экране появится окошко для составления сводной таблицы. Выбираем диапазон данных, из него будет создан отчет. Сначала в строке появится имя первой таблички – если есть необходимость, можно выбрать другие ячейки или указать имя другой таблицы из того же документа.
2. Выберем место, где будет размещена сводная таблица. Ее можно поместить на тот же лист или на новый в одном документе с начальными данными.
3. После заполнения всех полей нажимаем «ОК».

4

1. Откроется окно для формирования таблички. В нем находится список полей и области настроек. В верхней части окна выбираем нужные поля. После этого перетаскиваем их в нужные области.

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

5

Когда таблица сформирована, нужно применить выбранные фильтры. Разберем этот этап по шагам. Стоит вспомнить условие: нужно определить продажи женских теннисных кроссовок.

1. Открываем раздел «Пол» в таблице и выбираем «Женский», после этого кликаем «ОК».

6

1. Применяем фильтр к виду спорта – согласно условию, необходимо поставить галочку в графе «Теннис» и кликнуть «ОК».

Результат: на листе представлены только запрошенные данные. Информация о сумме в нескольких строчках с одинаковым наименованием суммируется.

7

Использование Мастера сводных таблиц

Составить отчет можно с помощью специального инструмента – Мастера сводных таблиц. Функция не находится в быстром доступе по умолчанию, поэтому сначала добавим ее туда.

1. Открываем вкладку «Файл», ее раздел «Параметры». Находим пункт «Панель быстрого доступа», в появившемся списке выбираем пункт «Мастер сводных таблиц и диаграмм». Кнопка «Добавить» станет активной, по ней нужно кликнуть, а после – нажать «ОК».

8

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

9

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

10

1. Определяем, где собираемся разместить сводную табличку. Если выбираете «Существующий лист», укажите конкретный лист из документа.

11

1. Заполняем форму сводной таблички по правилам классического метода и выставляем фильтры.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Консолидация данных из разных таблиц Excel

Одна из насущных задач, с которыми сталкиваются пользователи, – консолидация данных. Под консолидацией понимается объединение нескольких таблиц в одну. До появления Power Query это была довольно трудоемкая операция, особенно, если процесс требовал автоматизации. Хотя в эксель есть специальная команда Данные → Работа с данными → Консолидация, пользоваться ей не удобно. Мне, по крайней мере. Появление Power Query в корне изменило представление о том, как нужно объединять таблицы.

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

Процесс начинается с запуска пустого запроса: Данные → Получить и преобразовать данные → Создать запрос → Из других источников → Пустой запрос

Затем в строке формул вводим знакомую команду

= Excel.CurrentWorkbook()

Power Query показывает все таблицы в текущей книге.

Их нужно развернуть кнопкой с двумя стрелками в названии поля Content (на скриншоте ниже выделено красным кружком).

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

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

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

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

Удалим нижнее подчеркивание. Правой кнопкой мыши по названию столбца Name → Замена значений.

В следующем окне настроек указываем, что меняем _ на пусто, то есть в нижнем поле ничего не указываем.

Подчеркивание удаляется из названия.

Поиск и замена здесь работает так же, как и в обычном Excel.

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

Power Query распознает дату и меняет формат колонки. Мы также переименовываем столбец на Период.

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

Но что-то пошло не так. Во-первых, внизу таблицы пустая строка; во-вторых, при выгрузке произошла одна ошибка. Обновим запрос (справа от названия запроса значок обновления).

Что-то еще больше пошло не так. Даты исчезли, снизу таблицы добавились новые строки, а количество ошибок уже 19. Спокойствие, только спокойствие! Дело вот в чем.

Помните, на первом шаге мы получили все таблицы из файла? Так ведь и выгруженная таблица – это тоже таблица! Получается, Power Query взял 3 исходных таблицы, обработал, выгрузил на лист Excel и на следующем круге видит уже 4 таблицы!

При повторном обновлении запрос захватывает их все, а т.к. таблица выхода имеет другую структуру, то возникают ошибки.

Короче, из запроса нужно исключить таблицу, которая получается на выходе (Запрос1). Есть разные подходы, самый простой – это добавить шаг фильтрации. Выделяем в правой панели первый шаг Источник, открываем фильтр в колонке с названиями, снимаем галку с таблицы Запрос1 → Ok.

Снова выгружаем таблицу в Excel и на этот раз все в порядке.

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

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

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

А вот, как это выглядит при использовании Power Query.

Достаточно два раза нажать кнопку Обновить все (первый раз – для обновления запроса, второй – для сводной таблицы).

На добавление в отчет новых данных вместе с их обработкой потребовалось несколько секунд.

Вот за это мы так любим Power Query.

Ограничения Таблиц Excel

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

1. Не работают представления. Это команда, которая запоминает некоторые настройки листа (фильтр, свернутые строки/столбцы и некоторые другие).

2. Текущую книгу нельзя выложить для совместного использования.

3. Невозможно вставить промежуточные итоги.

4. Не работают формулы массивов.

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

Однако на фоне свойств и возможностей Таблиц, эти недостатки практически не заметны.

Множество других секретов Excel вы найдете в онлайн курсе.

Свойства таблиц

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

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

10

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

11

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

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

Вот маленькая демонстрация того, как новая строка автоматически добавляется к таблице.

12

Как мы видим, новые ячейки автоматически форматируются так, чтобы соответствовать таблице, а также ее ячейки заполняются нужными формулами и ссылками. Удобно, не так ли?

То же касается и новых столбцов.

13

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

14

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

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

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

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

12

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

13

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

14

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

Сводная таблица в Excel средствами Power Query

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

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

В этом случае необходимо создать чистый новый лист в программе Excel.

В этом листе перейти во вкладку «Данные».

Скачайте дополнительный материал к статье:

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

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

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

Полезный материал про работу в Excel!

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

В нашем примере видно, что указанный лист содержит множество ячеек с данными «null». Это неверно, так как программа будет обрабатывать и эти ячейки. Чтобы сократить область обрабатываемых значений и удалить такие нулевые ячейки, необходимо исправить исходный файл. Для этого нужно перейти в исходную таблицу и нажать «Ctrl + End». Будет выделена последняя активная ячейка таблицы. Надо удалить все ячейки правее и ниже таблицы, добиваясь того, чтобы при нажатии «Ctrl + End» становилась активной нижняя правая ячейка таблицы.

После этого источник данных не будет содержать лишней информации.

Дальше необходимо отредактировать данные в разделе «Параметры запроса».

Можно удалить строчки «Навигация» и «Измененный тип». И приступить к редактированию данных в разделе «Источник». В главном окне редактора будет отображаться перечень всех листов указанной книги. В нашем случае «Лист1» и «Лист2».

Далее надо выбрать только нужную информацию. В контекстном меню колонки «Data» выбрать «Удалить другие столбцы».

Затем в строке «Data» нажать иконку с двумя стрелками, как указано на рисунке.

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

Далее необходимо убрать лишние заголовки – «шапки». Для этого надо нажать «Использовать первую строку в качестве заголовков».

Таблица будет перестроена. Дублирующую строку с «шапкой» можно удалить. Для этого в фильтре столбца «Склад» снять галочку с пункта «Склад» и нажать «ОК». Затем в этом же фильтре нажать «Удалить пустые». Соответствующие строки будут удалены.

Далее необходимо сохранить полученную таблицу. Нажать кнопку «Закрыть и загрузить», далее в меню – «Закрыть и загрузить в. ».

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

Далее, чтобы построить сводную таблицу, нужно во вкладке «Вставка» нажать кнопку «Сводная таблица».

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

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

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

Построение отчета сводной таблицы

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

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

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

В итоге мы получим следующий пример сводной таблицы:

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

В динамике добавление полей выглядит так:

Теперь несколько видоизменим постановку задачи и сделаем анализ уже не в разрезе товаров, а в разрезе доходов магазинов по фирмам производителей.

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

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

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

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

Создать сводную таблицу в Excel

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

После двух месяцев работы вам будет любопытно, продали ли вы больше товаров в первый или второй месяц. Вы также хотели бы знать, продали ли вы больше продуктов Apple или продуктов Samsung. И наконец, вы хотели бы узнать общий объем продаж за каждый месяц.

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

Вот пошаговые инструкции по созданию сводной таблицы.

ШАГ 1 – Создайте сводную таблицу, щелкнув любую из ячеек в таблице данных, затем перейдите на верхнюю вкладку в Excel и выберите Вставить -> Сводная таблица.

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

ШАГ 3 – Нажмите на пустую сводную таблицу, созданную на новом листе. Вы увидите, что в правой части таблицы появятся поля сводной таблицы. Здесь вы можете перетащить и создать быстрое резюме.

ШАГ 4 – Чтобы узнать количество мобильных телефонов, продаваемых каждый месяц, перетащите «Проданные месяцы» в область «СТРОКИ» и «Марка в область ЦЕННОСТИ».

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

Если вы перетащите «Модель» или «Расположение склада» в ЗНАЧЕНИЯ вместо «Марка», он будет выдавать одинаковые числа для каждого месяца, поскольку он просто ссылается на общее количество строк в каждом проданном месяце. Похоже, мы продали больше телефонов в январе, чем в ФЕВ.

ШАГ 5. Чтобы узнать, было ли продано больше товаров Apple или Samsung в вашем магазине, вы можете повторно использовать одну и ту же сводную таблицу, не создавая новую.

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

Затем замените его на Бренд в строке ROWS.

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

ШАГ 5. И наконец, чтобы узнать, сколько вы получили в продажах за каждый из месяцев, мы будем повторно использовать одну и ту же сводную таблицу.

Очистите поле Марка и перетащите месяц Продано обратно в область строк. Поскольку мы специально хотим узнать общий объем продаж, очистите область VALUES и перетащите в Продажную цену, как показано ниже.

Поскольку столбец «Цена продажи» в исходном наборе данных представлен в числовом формате, сводная таблица будет автоматически суммировать цену продажи вместо подсчета количества строк цены продажи. Вуаля, вы получили 7550 долларов в январе и 7 100 долларов в феврале.

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

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

Советы: Если панель полей сводной таблицы справа от электронной таблицы пропадает, попробуйте навести указатель мыши на сводную таблицу, щелкните правой кнопкой мыши и выберите «Показать список полей». Это должно вернуть это. Наслаждайтесь!

Вычисляемые поля сводной таблицы

Если предоставленных операций и вычислений недостаточно, то эксель позволяет создать свое вычисляемое поле в сводной таблице. Для этого выделите ячейку из области таблицы, перейдите на вкладку «Параметры» («Анализ» для Excel 2013) появившейся ленты. Далее в разделе «Сервис» кликните по пиктограмме «Формулы», из раскрывающегося меню (в версии 2010 и выше путь отличается: Раздел «Вычисления» -> Раскрывающийся список «Поля, элементы и наборы») выберите пункт «Вычисляемое поле…». Должно появиться окно:

Задайте понятное имя, и запишите формулу, используя любые функции (имейте в виду, что вычисляемые поля не работают с текстом). В качестве примера умножим курс на 1000 и вычтем 13 процентов (=Курс*1000*0,87). Назовем поле «ЗП», добавим в область значений и в качестве операции применим максимум. Посмотрите новый вид отчета:

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

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

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

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

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

Если источник данных сводной таблицы представляет собой внешнюю базу данных, созданную в другой программе, такой как Access, установите переключатель Использовать внешний источник данных. Потом щелкните на кнопке Выбрать подключение, а затем в открывшемся диалоговом окне выберите требуемое подключение. Кроме того, Excel поддерживает анализ данных для нескольких связанных таблиц листа (так называемая “модель данных”). Если данные новой сводной таблицы будут анализироваться наряду с данными существующей сводной таблицы, то установите флажок Добавить эти данные в модель данных.

После того как будет определен источник данных и указано место расположения сводной таблицы, щелкните на кнопке ОК, и программа добавит пустую сетку для новой таблицы, а также откроет в правой части области рабочего листа панель Список полей сводной таблицы. Эта панель разделена на две части. Вверху находится список полей источника данных, которые можно добавить в сводную таблицу, а внизу — область, разделенная на четыре зоны: ФИЛЬТРЫ, СТРОКИ, СТОЛБЦЫ и ЗНАЧЕНИЯ.

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

• ФИЛЬТРЫ. Здесь содержатся поля, позволяющие фильтровать данные таблицы. Так, например, если перетащить сюда поле года, то можно будет отображать в таблице сводные данные для каждого конкретного года, представленного в списке данных.
• СТОЛБЦЫ. Здесь содержатся поля, определяющие данные, которые отображаются в столбцах сводной таблицы.
• СТРОКИ. Здесь находятся поля, определяющие данные, которые отображаются в строках сводной таблицы.
• ЗНАЧЕНИЯ. Здесь содержатся поля, определяющие, какие данные будут отображаться в ячейках таблицы, т.е. значения, консолидируемые в последнем столбце (по умолчанию суммируемые).

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

В этой сводной таблице в качестве фильтра из списка данных было выбрано поле Дата. Поле Категория я назначил меткам строк. В качестве значений были выбраны поля Расход и Доход.

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

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

• Список полей. Служит для сокрытия и отображения списка полей на панели задач в правой части области рабочего листа.
• +/- Кнопки. Используется для сокрытия и отображения кнопок сворачивания (-) и разворачивания (+) конкретных строк и столбцов, позволяющих временно удалять и отображать в сводной таблице конкретные значения.
• Заголовки полей. Служит для сокрытия и отображения полей, назначаемых меткам строк и столбцов сводной таблицы.