В этой части руководства подробно описано, как создать сводную таблицу в Excel. Эта статья написана для Excel 2007 (а также для более поздних версий). Инструкции для более ранних версий Excel можно найти в отдельной статье: Как создать сводную таблицу в Excel 2003?
В качестве примера рассмотрим следующую таблицу, содержащую данные о продажах компании за первый квартал 2016 года:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Время | Номер счета-фактуры | Сумма | Торговый представитель. | Регион |
| 2 | 01/01/2016 | 2016 – 0001 | $ 819 | Барнс | север |
| 3 | 01/01/2016 | 2016 – 0002 | $ 456 | Brown | Южная |
| 4 | 01/01/2016 | 2016 – 0003 | $ 538 | Джонс | Южная |
| 5 | 01/01/2016 | 2016 – 0004 | $ 1,009 | Барнс | север |
| 6 | 01/02/2016 | 2016 – 0005 | $ 486 | Джонс | Южная |
| 7 | 01/02/2016 | 2016 – 0006 | $ 948 | Smith | север |
| 8 | 01/02/2016 | 2016 – 0007 | $ 740 | Барнс | север |
| 9 | 01/03/2016 | 2016 – 0008 | $ 543 | Smith | север |
| 10 | 01/03/2016 | 2016 – 0009 | $ 820 | Brown | Южная |
| 11 | ... | ... | ... | ... | ... |
Для начала создадим очень простую сводную таблицу, которая будет показывать общий объем продаж каждого из продавцов согласно таблице выше. Для этого сделайте следующее:
- Выберите любую ячейку из диапазона данных или весь диапазон, который будет использоваться в сводной таблице.ВНИМАНИЕ: Если вы выберете одну ячейку из диапазона данных, Excel автоматически обнаружит и выберет весь диапазон данных для сводной таблицы. Чтобы Excel правильно выбрал диапазон, должны быть выполнены следующие условия:
- Каждый столбец в диапазоне данных должен иметь свое уникальное имя;
- Данные не должны содержать пустых строк.
- Нажав на кнопку Таблица результатов (Сводная таблица) в разделе таблицы Вкладка (Таблицы) Вставить (Вставка) Ленты меню Excel.
- На экране появится диалоговое окно. Создать сводную таблицу (Создайте сводную таблицу), как показано на рисунке ниже.
Убедитесь, что выбранный диапазон соответствует диапазону ячеек, который следует использовать для создания сводной таблицы. Здесь также можно указать, куда следует вставить созданную сводную таблицу. Вы можете выбрать существующий лист, чтобы вставить на него сводную таблицу, или вариант – На новый лист (Новый рабочий лист). щелкнуть OK. - Появится пустая сводная таблица, а также панель Поля сводной таблицы (Список полей сводной таблицы) с несколькими полями данных. Обратите внимание, что это заголовки из исходной таблицы данных.
- В панелях Поля сводной таблицы (Список полей сводной таблицы):
- перетащить Торговый представитель. в район Ряды (Заголовки строк);
- перетащить Сумма в Ценности (Ценности);
- Проверяем: in Ценности (Значения) должно быть значением Поле суммы Сумма (Сумма суммы), а не Сумма по полю Сумма (Подсчет суммы).
В этом примере столбец Сумма содержит числовые значения, поэтому область Σ Значения (Значения Σ) будут выбраны по умолчанию. Поле суммы Сумма (Сумма суммы). Если в столбце Сумма будет содержать нечисловые или пустые значения, тогда можно выбрать сводную таблицу по умолчанию Сумма по полю Сумма (Подсчет суммы). Если такое произошло, то изменить количество на сумму можно следующим образом:
- В Σ Значения (Значения Σ) нажмите Сумма по полю Сумма (Количество суммы) и выберите опцию Параметры поля значения (Настройки поля значений);
- На вкладке Дополнительно Эксплуатация (Суммировать значения по) выбрать операцию Сумма (Сумма);
- Открыть OK.
Убедитесь, что выбранный диапазон соответствует диапазону ячеек, который следует использовать для создания сводной таблицы. Здесь также можно указать, куда следует вставить созданную сводную таблицу. Вы можете выбрать существующий лист, чтобы вставить на него сводную таблицу, или вариант – На новый лист (Новый рабочий лист). щелкнуть OK.
Сводная таблица будет заполнена общими показателями продаж для каждого продавца, как показано на изображении выше.
Если вы хотите отображать объемы продаж в денежных единицах, необходимо отформатировать ячейки, содержащие эти значения. Самый простой способ сделать это — выделить ячейки, формат которых вы хотите настроить, и выбрать формат. монетарный Раздел (Валюта) Номер регистрации Вкладка (Номер) Главная (Главная страница) Ленты меню Excel (как показано ниже).
В результате сводная таблица будет выглядеть так:
- сводная таблица перед настройкой числового формата
- сводная таблица после установки формата валюты
Обратите внимание, что формат валюты по умолчанию зависит от настроек системы.
Рекомендуемые сводные таблицы в последних версиях Excel
В последних версиях Excel (Excel 2013 или более поздней версии) на вкладке Вставить Кнопка (Вставить) присутствует Рекомендуемые сводные таблицы (Рекомендуемые сводные таблицы). На основе выбранных исходных данных этот инструмент предлагает возможные форматы сводных таблиц. Примеры можно посмотреть на сайте Microsoft Office.