Содержание:
Сводные таблицы — один из самых мощных инструментов Excel. Они позволяют анализировать и суммировать различные сводки больших объемов данных всего несколькими щелчками мыши. В этой статье мы познакомимся со сводными таблицами, разберемся, что они собой представляют, научимся их создавать и настраивать.
Эта статья была написана с использованием Excel 2010. Концепция сводных таблиц не сильно изменилась за последние годы, но способ их создания немного отличается в каждой новой версии Excel. Если у вас версия Excel не 2010, то будьте готовы, что скриншоты в этой статье будут отличаться от того, что вы видите на своем экране.
Немного истории
На заре программного обеспечения для работы с электронными таблицами правила Lotus 1-2-3. Ее доминирование было настолько полным, что усилия Microsoft по разработке собственного программного обеспечения (Excel) в качестве альтернативы Lotus казались пустой тратой времени. Теперь перенесемся в 2010 год! Excel доминирует в электронных таблицах больше, чем код Lotus когда-либо в своей истории, а число людей, которые до сих пор используют Lotus, близко к нулю. Как такое могло произойти? В чем же была причина столь драматического поворота событий?
Аналитики выделяют два основных фактора:
- Во-первых, в Lotus решили, что эта новомодная платформа с графическим интерфейсом под названием Windows — всего лишь мимолетное увлечение, которое не продлится долго. Они отказались создавать версию Lotus 1-2-3 для Windows (но только на несколько лет), предсказывая, что версия их программного обеспечения для DOS будет всем, что когда-либо понадобится потребителям. Microsoft, естественно, разработала Excel специально для Windows.
- Во-вторых, Microsoft представила в Excel инструмент под названием PivotTables, которого не было в Lotus 1-2-3. Сводные таблицы, эксклюзивные для Excel, оказались настолько чрезвычайно полезными, что люди предпочли использовать новый программный пакет Excel, а не использовать Lotus 1-2-3, в котором их не было.
Сводные таблицы, наряду с недооценкой успеха Windows в целом, сыграли марш смерти для Lotus 1-2-3 и положили начало успеху Microsoft Excel.
Что такое сводные таблицы?
Итак, как лучше всего охарактеризовать сводные таблицы?
Проще говоря, сводные таблицы — это сводки некоторых данных, созданные для облегчения анализа этих данных. В отличие от итогов, создаваемых вручную, сводные таблицы Excel являются интерактивными. После создания вы можете легко изменить их, если они не дают той картины, на которую вы надеялись. Всего парой щелчков мыши можно перевернуть итоговые значения, чтобы заголовки столбцов стали заголовками строк, и наоборот. С помощью сводных таблиц можно делать многое. Вместо того, чтобы пытаться описать словами все возможности сводных таблиц, проще продемонстрировать это на практике…
Данные, которые вы анализируете с помощью сводных таблиц, не могут быть случайными. Это должны быть необработанные данные, например какой-то список. Например, это может быть список продаж, совершенных компанией за последние шесть месяцев.
Посмотрите на данные, представленные на рисунке ниже:
Обратите внимание, что это не необработанные необработанные данные, поскольку они уже были обобщены. В ячейке B3 мы видим 30000 30000 долларов, что, вероятно, и есть общий результат, который Джеймс Кук сделал в январе. Где тогда исходные данные? Откуда взялась цифра в XNUMX XNUMX долларов? Где находится первоначальный список продаж, из которого была получена эта месячная сумма? Видно, что кто-то проделал огромную работу по организации и сортировке всех данных о продажах за последние полгода и превращению их в итоговую таблицу, которую мы видим. Как вы думаете, сколько времени это заняло? Час? Десять часов?
Дело в том, что приведенная выше таблица не является сводной таблицей. Он был создан вручную на основе необработанных данных, хранящихся в другом месте, и его обработка заняла как минимум пару часов. Именно такую сводную таблицу можно создать с помощью сводных таблиц всего за несколько секунд. Давайте разберемся, как…
Если мы вернемся к исходному списку продаж, он будет выглядеть примерно так:
Вы можете быть удивлены, что из этого списка сделок с помощью сводных таблиц всего за несколько секунд мы можем создать ежемесячный отчет о продажах в Excel, который мы анализировали выше. Да, мы можем это и даже больше!
Как создать сводную таблицу?
Сначала убедитесь, что у вас есть исходные данные на листе Excel. Перечень финансовых операций является наиболее типичным, что встречается. Фактически, это может быть список чего угодно: контактные данные сотрудников, коллекция компакт-дисков или данные о расходе топлива вашей компании.
Итак, запускаем Excel… и загружаем такой список…
После того, как мы открыли этот список в Excel, мы можем приступить к созданию сводной таблицы.
Выберите любую ячейку из этого списка:
Затем на вкладке Вносимые (Вставить) выбрать команду PivotTable (Сводная таблица):
Появится диалоговое окно Создать сводную таблицу (Создание сводной таблицы) к вам два вопроса:
- Какие данные использовать для создания новой сводной таблицы?
- Где разместить сводную таблицу?
Поскольку на предыдущем шаге мы уже выбрали одну из ячеек списка, для создания сводной таблицы будет автоматически выбран весь список. Обратите внимание, что мы можем выбрать другой диапазон, другую таблицу и даже какой-либо внешний источник данных, например таблицу базы данных Access или MS-SQL. Кроме того, нам нужно выбрать, где разместить новую сводную таблицу: на новом листе или на одном из существующих. В этом примере мы выберем вариант – Новый рабочий лист (на новый лист):
Excel создаст новый лист и разместит на нем пустую сводную таблицу:
Как только мы щелкнем по любой ячейке сводной таблицы, появится еще одно диалоговое окно: Список полей сводной таблицы (Поля сводной таблицы).
Список полей в верхней части диалогового окна представляет собой список всех заголовков из исходного списка. Четыре пустые области в нижней части экрана позволяют указать сводной таблице, как вы хотите суммировать данные. Пока эти области пусты, в таблице тоже ничего нет. Все, что нам нужно сделать, это перетащить заголовки из верхней области в пустые области ниже. При этом автоматически формируется сводная таблица, в соответствии с нашей инструкцией. Если мы допустим ошибку, мы можем удалить заголовки из нижней области или перетащить другие, чтобы заменить их.
Площадь Наши ценности (Значения), вероятно, является самым важным из четырех. От того, какой заголовок размещен в этой области, зависит, какие данные будут суммироваться (сумма, среднее, максимальное, минимальное и т. д.) Это почти всегда числовые значения. Отличным кандидатом на место в этой области являются данные под заголовком Сумма (Стоимость) нашей исходной таблицы. Перетащите этот заголовок в область Наши ценности (Ценности):
Обратите внимание, что заголовок Сумма теперь отмечен галочкой, а в области Наши ценности (Значения) появилась запись Сумма суммы (поле Сумма Сумма), что указывает на то, что столбец Сумма подвела.
Если мы посмотрим на саму сводную таблицу, то увидим сумму всех значений из столбца Сумма оригинальный стол.
Итак, наша первая сводная таблица создана! Удобно, но не особо впечатляет. Вероятно, мы хотим получить больше информации о наших данных, чем имеем сейчас.
Давайте обратимся к исходным данным и попробуем определить один или несколько столбцов, которые можно использовать для разделения этой суммы. Например, мы можем сформировать нашу сводную таблицу таким образом, чтобы общая сумма продаж рассчитывалась для каждого продавца индивидуально. Те. В нашу сводную таблицу будут добавлены строки с именем каждого продавца в компании и их общей суммой продаж. Чтобы добиться этого результата, просто перетащите заголовок продавец (торговый представитель) в регион заголовки строк (Струны):
Становится интереснее! Наша сводная таблица начинает обретать форму…
Видите преимущества? За пару кликов мы создали таблицу, создание которой вручную заняло бы очень много времени.
Что еще мы можем сделать? Что ж, в некотором смысле наша сводная таблица готова. Мы создали полезную сводку исходных данных. Важная информация уже получена! В оставшейся части статьи мы рассмотрим некоторые способы создания более сложных сводных таблиц, а также научимся их настраивать.
Настройка сводной таблицы
Во-первых, мы можем создать двумерную сводную таблицу. Давайте сделаем это, используя заголовок столбца Оплата (Способ оплаты). Просто перетащите заголовок Оплата в район Ярлыки столбцов (Столбцы):
Получаем результат:
Выглядит очень круто!
Теперь сделаем трехмерную таблицу. Как будет выглядеть такая таблица? Давайте посмотрим…
Перетащите заголовок Упаковка (Комплекс) в район фильтры отчетов (Фильтры):
Обратите внимание, где он…
Это дает нам возможность фильтровать отчет по признаку «За какой комплекс отдыха было оплачено». Например, мы можем посмотреть разбивку по продавцам и по способам оплаты по всем комплексам, либо в пару кликов мышки изменить вид сводной таблицы и показывать такую же разбивку только для тех, кто заказал комплекс. Искатели Солнца.
Итак, если вы правильно это понимаете, то нашу сводную таблицу можно назвать трехмерной. Продолжим настройку…
Если вдруг окажется, что в сводной таблице должна отображаться только оплата чеком и кредитной картой (то есть безналичный расчет), то можно отключить отображение заголовка Наличный расчёт (Наличные). Для этого рядом с Ярлыки столбцов нажмите стрелку вниз и снимите флажок в раскрывающемся меню. Наличный расчёт:
Давайте посмотрим, как теперь выглядит наша сводная таблица. Как видите, столбец Наличный расчёт исчез от нее.
Форматирование сводных таблиц в Excel
Сводные таблицы, очевидно, являются очень мощным инструментом, но пока результаты выглядят немного простыми и скучными. Например, числа, которые мы складываем, не похожи на суммы в долларах — это просто цифры. Давайте это исправим.
Соблазнительно в такой ситуации сделать то, что вы привыкли: просто выделить всю таблицу (или весь лист) и с помощью стандартных кнопок форматирования чисел на панели инструментов установить нужный формат. Проблема такого подхода в том, что если вы когда-нибудь измените структуру сводной таблицы в будущем (что произойдет с вероятностью 99%), форматирование будет потеряно. Нам нужен способ сделать это (почти) постоянным.
Сначала найдем запись Сумма суммы in Наши ценности (Значения) и нажмите на него. В появившемся меню выберите пункт Настройки поля значений (Параметры поля значений):
Появится диалоговое окно Настройки поля значений (Параметры поля значений).
Нажмите кнопку Формат номера (Числовой формат), откроется диалоговое окно. Формат ячеек (формат ячейки):
Из списка Категории (Числовые форматы) выберите Бухгалтерский учет (Финансовые) и установите количество десятичных знаков на ноль. Теперь нажмите несколько раз OKчтобы вернуться к нашей сводной таблице.
Как видите, числа отформатированы как суммы в долларах.
Пока мы занимаемся форматированием, давайте настроим формат для всей сводной таблицы. Есть несколько способов сделать это. Используем тот, что попроще…
Нажмите Инструменты сводных таблиц: проектирование (Работа со сводными таблицами: Конструктор):
Далее разверните меню, нажав на стрелочку в правом нижнем углу раздела. Стили сводной таблицы (Стили сводной таблицы), чтобы просмотреть обширную коллекцию встроенных стилей:
Выберите любой подходящий стиль и посмотрите результат в сводной таблице:
Другие настройки сводной таблицы в Excel
Иногда вам нужно отфильтровать данные по датам. Например, в нашем списке сделок много-много дат. Excel предоставляет инструмент для группировки данных по дню, месяцу, году и т. д. Давайте посмотрим, как это делается.
Сначала удалите запись. Оплата из региона Ярлыки столбцов (Столбцы). Для этого перетащите его обратно в список заголовков, а на его место переместите заголовок Дата бронирования (дата бронирования):
Как видите, это временно сделало нашу сводную таблицу бесполезной. Excel создал отдельный столбец для каждой даты совершения сделки. В результате у нас получился очень широкий стол!
Чтобы это исправить, щелкните правой кнопкой мыши на любой дате и выберите в контекстном меню GROUP (Группа):
Появится диалоговое окно группировки. Мы выбираем Месяцев (Месяцы) и нажмите OK:
Вуаля! Эта таблица гораздо полезнее:
Кстати, эта таблица практически идентична той, что показана в начале статьи, где итоги продаж составлены вручную.
Есть еще один очень важный момент, который вам необходимо знать! Вы можете создать не один, а несколько уровней заголовков строк (или столбцов):
… и это будет выглядеть так…
То же самое можно сделать с заголовками столбцов (или даже с фильтрами).
Вернемся к исходному виду таблицы и посмотрим, как вместо сумм отображать средние значения.
Чтобы начать, нажмите на Сумма суммы и в появившемся меню выберите Настройки поля значений (Параметры поля значений):
Список Суммировать поле значения по (Операция) в диалоговом окне Настройки поля значений (Параметры поля значения) выберите Средняя (Средний):
В то же время, пока мы здесь, давайте поменяемся CustomName (Произвольное имя) с Среднее значение суммы (Сумма поля Сумма) на что-то более короткое. Введите в это поле что-то вроде в среднем:
Нажмите OK и посмотрим, что произойдет. Обратите внимание, что все значения изменились с итоговых на средние, а заголовок таблицы (в левой верхней ячейке) изменился на в среднем:
При желании вы можете сразу получить сумму, среднее значение и количество (продаж), помещённые в одну сводную таблицу.
Вот пошаговое руководство, как это сделать, начиная с пустой сводной таблицы:
- Перетащите заголовок продавец (торговый представитель) в регион Ярлыки столбцов (Столбцы).
- Перетащите заголовок три раза Сумма (Стоимость) в районе Наши ценности (Ценности).
- Для первого поля Сумма изменить заголовок на Всего (Сумма), а числовой формат в этом поле: Бухгалтерский учет (Финансовый). Количество десятичных знаков равно нулю.
- Второе поле Сумма имя Усредненныйе, установите для него операцию Средняя (Среднее) и числовой формат в этом поле также изменится на Бухгалтерский учет (Финансовый) с нулевым знаком после запятой.
- Для третьего поля Сумма установить заголовок Количество и операция ему – Количество (Количество)
- В Ярлыки столбцов Поле (Столбцы) создается автоматически Σ Значения (Σ Значения) – перетащите его в область заголовки строк (Линии)
Вот что у нас получится в итоге:
Общая сумма, средняя стоимость и количество продаж – все в одной сводной таблице!
Заключение
Сводные таблицы в Microsoft Excel содержат множество функций и настроек. В такой маленькой статье они даже близко не могут охватить их все. Чтобы полностью описать все возможности сводных таблиц, потребовалась бы небольшая книга или большой веб-сайт. Смелые и любознательные читатели могут продолжить изучение сводных таблиц. Для этого достаточно нажать правой кнопкой мыши практически на любой элемент сводной таблицы и посмотреть, какие функции и настройки открываются. На ленте вы найдете две вкладки: Инструменты сводных таблиц: параметры (анализ) и Проект (Конструктор). Не бойтесь совершить ошибку: вы всегда можете удалить сводную таблицу и начать все сначала. У вас есть возможность, которой никогда не было у давних пользователей DOS и Lotus 1-2-3.