Сводные таблицы являются одной из самых мощных функций Microsoft Excel. Они позволяют анализировать и суммировать большие объемы данных всего несколькими щелчками мыши. В этой статье мы рассмотрим сводные таблицы, поймем, что они из себя представляют, а также узнаем, как их создавать и настраивать.
Примечание. Эта статья написана с использованием Excel 2010 (бета-версия). Концепция сводной таблицы с годами мало менялась, но метод ее создания менялся почти на каждой итерации Excel. Если вы используете версию Excel, отличную от 2010 года, ожидайте, что другие экраны будут отличаться от тех, которые вы видите в этой статье.
Маленькая история
В первые дни программ работы с электронными таблицами Lotus 1-2-3 управлял командой. Его доминирование было настолько полным, что люди думали, что Microsoft потратила время на разработку собственного программного обеспечения для работы с электронными таблицами (Excel), чтобы конкурировать с Lotus. Забегая вперед, к 2010 году, и доминирование Excel на рынке электронных таблиц больше, чем когда-либо было в Lotus, а число пользователей Lotus 1-2-3 все еще приближается к нулю. Как это случилось? Что вызвало такое драматическое изменение судьбы?
Отраслевые аналитики объясняют это двумя факторами: во-первых, Lotus решила, что эта модная новая платформа с графическим интерфейсом под названием «Windows» стала преходящим увлечением, которое никогда не взлетит. Они отказались создавать версию Lotus 1-2-3 для Windows (в любом случае, в течение нескольких лет), предсказывая, что их версия программного обеспечения для DOS – это все, что кому-либо когда-либо понадобится. Microsoft, естественно, разработала Excel исключительно для Windows. Во-вторых, Microsoft разработала для Excel функцию, которую Lotus не предоставляла в 1-2-3, а именно Сводные таблицы . Функция сводных таблиц, эксклюзивная для Excel, считалась настолько ошеломляюще полезной, что люди были готовы изучать весь новый пакет программного обеспечения (Excel), а не придерживаться программы (1-2-3), в которой ее не было. Эта особенность, наряду с ошибочным суждением об успехе Windows, стала концом смерти для Lotus 1-2-3 и началом успеха Microsoft Excel.
Понимание сводных таблиц
Так что же такое сводная таблица?
Проще говоря, сводная таблица представляет собой сводку некоторых данных, созданную для облегчения анализа этих данных. Но в отличие от созданного вручную резюме, сводные таблицы Excel являются интерактивными. Создав его, вы можете легко изменить его, если он не дает точного представления о ваших данных, на которые вы надеялись. За пару кликов сводка может быть «повернута» – повернута таким образом, что заголовки столбцов становятся заголовками строк, и наоборот. И еще многое можно сделать. Вместо того, чтобы пытаться описать все возможности сводных таблиц, мы просто продемонстрируем их …
Данные, которые вы анализируете с помощью сводной таблицы, не могут быть просто любыми данными – это должны быть данные raw , ранее необработанные (не суммированные) – обычно это какой-то список. Примером этого может служить список торговых операций в компании за последние шесть месяцев.
Изучите данные, показанные ниже:
Обратите внимание, что это не необработанные данные. На самом деле это уже какое-то резюме. В ячейке B3 мы видим 30 000 долларов, что, по-видимому, является суммой продаж Джеймса Кука за январь. Так где же необработанные данные? Как мы достигли цифры 30 000 долларов? Где находится исходный список транзакций продаж, из которых был создан этот показатель? Понятно, что где-то кто-то, должно быть, попытался сопоставить все транзакции продаж за последние шесть месяцев с итогом, который мы видим выше. Как вы думаете, сколько времени это заняло? Час? Десять?
Скорее всего, да. Видите ли, приведенная выше таблица на самом деле не является сводной таблицей. Он был создан вручную из необработанных данных, хранящихся в другом месте, и для его компиляции действительно потребовалось несколько часов. Тем не менее, это именно та сводка, которая может быть создана с помощью сводных таблиц, и в этом случае это заняло бы всего несколько секунд. Давайте узнаем, как …
Если бы мы отслеживали исходный список торговых транзакций, это могло бы выглядеть примерно так:
Вы можете быть удивлены, узнав, что, используя функцию сводной таблицы Excel, мы можем создать ежемесячную сводку продаж, аналогичную приведенной выше, за несколько секунд, всего за несколько щелчков мышью. Мы можем сделать это – и многое другое!
Как создать сводную таблицу
Во-первых, убедитесь, что у вас есть некоторые необработанные данные на листе в Excel. Список финансовых транзакций является типичным, но это может быть список всего, что угодно: контактные данные сотрудника, коллекция компакт-дисков или данные о расходе топлива для парка автомобилей вашей компании.
Итак, мы запускаем Excel … и загружаем такой список …
Как только мы откроем список в Excel, мы готовы приступить к созданию сводной таблицы.
Нажмите на любую ячейку в списке:
Затем на вкладке Вставка нажмите значок Сводная таблица :
Появится окно Создать сводную таблицу , в котором будут заданы два вопроса: на каких данных должна основываться ваша новая сводная таблица и где ее следует создавать? Поскольку мы уже нажали на ячейку в списке (на шаге выше), весь список, окружающий эту ячейку, уже выбран для нас ( $ A $ 1: $ G $ 88 в разделе Платежи , в этом примере). Обратите внимание, что мы можем выбрать список в любой другой области любой другой рабочей таблицы или даже некоторый внешний источник данных, такой как таблица базы данных Access или даже таблица базы данных MS-SQL Server. Нам также нужно выбрать, хотим ли мы, чтобы наша новая сводная таблица создавалась на новом листе или на существующем . В этом примере мы выберем новый :
Для нас создан новый лист, а на этом листе создана пустая сводная таблица:
Также появляется другое окно: Список полей сводной таблицы . Этот список полей будет отображаться всякий раз, когда мы нажимаем на любую ячейку в сводной таблице (выше):
Список полей в верхней части поля фактически представляет собой набор заголовков столбцов из исходного листа необработанных данных. Четыре пустых поля в нижней части экрана позволяют нам выбрать способ, которым мы хотели бы, чтобы наша сводная таблица суммировала необработанные данные. Пока в этих полях ничего нет, поэтому сводная таблица пуста. Все, что нам нужно сделать, это перетащить поля вниз из списка выше и поместить их в нижние поля. Сводная таблица автоматически создается в соответствии с нашими инструкциями. Если мы ошибаемся, нам нужно только перетащить поля туда, откуда они пришли и/или перетащить поля новые вниз, чтобы заменить их.
Поле Значения является, пожалуй, самым важным из четырех. Поле, которое перетаскивается в это поле, представляет данные, которые необходимо каким-либо образом суммировать (суммированием, усреднением, нахождением максимума, минимума и т. Д.). Это почти всегда числовые данные. Идеальным кандидатом на этот блок в наших примерах данных является поле/столбец «Сумма». Перетащите это поле в поле Значения :
Обратите внимание, что (a) поле «Сумма» в списке полей теперь помечено галочкой, а «Сумма суммы» добавлена в поле Значения , указывая, что столбец суммы суммирован.
Если мы рассмотрим саму сводную таблицу, мы действительно найдем сумму всех значений «Amount» из таблицы необработанных данных:
Мы создали нашу первую сводную таблицу! Удобно, но не особо впечатляет. Вполне вероятно, что нам нужно немного больше понимания наших данных, чем это.
Ссылаясь на наши примеры данных, нам нужно идентифицировать один или несколько заголовков столбцов, которые мы могли бы предположительно использовать для разделения этой суммы. Например, мы можем решить, что хотели бы увидеть сводку наших данных, где у нас есть заголовок строки для каждого из различных продавцов в нашей компании, и итоговую сумму для каждого. Чтобы достичь этого, все, что нам нужно сделать, это перетащить поле «Продавец» в поле Метки строк :
Теперь , наконец, все становится интересным! Наша сводная таблица начинает обретать форму …
С помощью пары щелчков мыши мы создали таблицу, которая заняла бы много времени вручную.
Так что еще мы можем сделать? Ну, в некотором смысле наша сводная таблица завершена. Мы создали полезную сводку наших исходных данных. Важные вещи уже изучены! В оставшейся части статьи мы рассмотрим некоторые способы создания более сложных сводных таблиц и способы настройки этих сводных таблиц.
Во-первых, мы можем создать двумерную таблицу. Давайте сделаем это, используя «Способ оплаты» в качестве заголовка столбца. Просто перетащите заголовок «Способ оплаты» в поле Метки столбца :
Который выглядит так:
Начинаю становиться очень крутым!
Давайте сделаем его трех -мерной таблицей. Как может выглядеть такая таблица? Ну, давай посмотрим …
Перетащите столбец/заголовок «Пакет» в поле Фильтр отчетов :
Обратите внимание, где это заканчивается …
Это позволяет нам фильтровать наш отчет по тому, какой «праздничный пакет» был приобретен. Например, мы можем увидеть разбивку продавца по сравнению с методом оплаты для пакетов всех , или, с помощью пары щелчков, изменить его, чтобы отобразить такую же разбивку для пакета «Sunseekers»:
Итак, если вы думаете об этом правильно, наша сводная таблица теперь трехмерная. Давайте продолжим настраивать …
Если получится, скажем, что мы хотим видеть только транзакции с чеком и кредитной картой (т.е. без транзакций с наличными), то мы можем отменить выбор пункта «Наличные» из заголовков столбцов. Нажмите стрелку раскрывающегося списка рядом с Ярлыки столбцов и снимите флажок «Наличные»:
Давайте посмотрим, как это выглядит … Как вы видите, «наличные деньги» исчезли.
Форматирование
Это, очевидно, очень мощная система, но пока результаты выглядят очень просто и скучно. Для начала, числа, которые мы суммируем, не похожи на суммы в долларах – просто старые цифры. Давайте исправим это.
Может возникнуть соблазн сделать то, что мы привыкли делать в таких обстоятельствах, и просто выбрать всю таблицу (или весь лист) и использовать стандартные кнопки форматирования чисел на панели инструментов для завершения форматирования. Проблема такого подхода заключается в том, что если вы когда-нибудь измените структуру сводной таблицы в будущем (что вероятнее всего на 99%), эти числовые форматы будут потеряны. Нам нужен способ, который сделает их (полу) постоянными.
Сначала мы находим запись «Сумма суммы» в поле Значения и нажимаем на нее. Появится меню. Мы выбираем Настройки поля значения… в меню:
Откроется окно Настройки поля значений .
Нажмите кнопку Числовой формат , и появится стандартное окно Формат ячеек :
В списке Категория выберите (скажем) Бухгалтерский учет и уменьшите число десятичных знаков до 0. Несколько раз нажмите ОК , чтобы вернуться в сводную таблицу …
Как видите, числа были правильно отформатированы как суммы в долларах.
Пока мы занимаемся форматированием, давайте отформатируем всю сводную таблицу. Есть несколько способов сделать это. Давайте использовать простой …
Перейдите на вкладку Инструменты/дизайн сводной таблицы .
Затем опустите стрелку в правом нижнем углу списка Стили сводной таблицы , чтобы увидеть обширную коллекцию встроенных стилей:
Выберите любой, который вам нравится, и посмотрите на результат в вашей сводной таблице:
Другие параметры
Мы также можем работать с датами. Сейчас обычно в списке транзакций много, много дат, например, с той, с которой мы начали. Но Excel предоставляет возможность группировать элементы данных по дням, неделям, месяцам, годам и т. Д. Давайте посмотрим, как это делается.
Во-первых, давайте удалим столбец «Способ оплаты» из поля Метки столбцов (просто перетащите его обратно в список полей) и замените его столбцом «Дата забронирована»:
Как видите, это делает нашу сводную таблицу мгновенно бесполезной, давая нам один столбец для каждой даты, когда произошла транзакция – очень широкая таблица!
Чтобы это исправить, нажмите правой кнопкой мыши на любую дату и выберите Группировать … в контекстном меню:
Появится окно группировки. Мы выбираем Месяцы и нажимаем ОК:
Вуаля! намного более полезная таблица:
(Между прочим, эта таблица практически идентична той, что показана в начале этой статьи – исходное резюме продаж, созданное вручную.)
Еще одна интересная вещь, о которой следует помнить, это то, что вы можете иметь более одного набора заголовков строк (или заголовков столбцов):
… который выглядит так …
Вы можете сделать то же самое с заголовками столбцов (или даже с фильтрами отчетов).
Еще раз упрощая, давайте посмотрим, как построить усредненные значения, а не суммированные значения.
Сначала нажмите «Сумма суммы» и выберите Настройки поля значения… в появившемся контекстном меню:
В списке Суммировать поле значений по в поле Настройки поля значений выберите Среднее :
Пока мы здесь, давайте изменим Пользовательское имя с «Среднее из суммы» на что-то более краткое. Введите что-то вроде «Avg»:
Нажмите ОК и посмотрите, как это выглядит. Обратите внимание, что все значения изменяются от суммированных итогов до средних значений, а название таблицы (верхняя левая ячейка) меняется на «Avg»:
Если нам нравится, мы можем даже иметь суммы, средние значения и подсчеты (количество = количество продаж) в одной сводной таблице!
Вот шаги, чтобы получить что-то подобное на месте (начиная с пустой сводной таблицы):
- Перетащите «Продавец» в Метки столбцов .
- Перетащите поле «Сумма» в поле Значения три раза.
- Для первого поля «Сумма» измените его настраиваемое имя на «Итого», а его числовой формат – на Бухгалтерский учет (0 десятичных знаков).
- Для второго поля «Сумма» измените его пользовательское имя на «Среднее», его функцию на Среднее и формат чисел на Бухгалтерия (0 десятичных знаков).
- Для третьего поля «Сумма» измените его имя на «Счет», а его функцию – на Счет .
-
Перетащите автоматически созданное поле
из Метки столбцов в Метки строк .
Вот что мы получаем в итоге:
Итого, в среднем и рассчитывать на одну и ту же сводную таблицу!
Заключение
Существует множество функций и параметров для сводных таблиц, созданных в Microsoft Excel, – слишком много, чтобы перечислять их в подобной статье. Чтобы полностью раскрыть потенциал сводных таблиц, потребуется небольшая книга (или большой веб-сайт). Смелые и/или увлеченные читатели могут довольно легко исследовать сводные таблицы: просто щелкните правой кнопкой мыши практически все и посмотрите, какие опции станут доступны для вас. Есть также две вкладки ленты: Инструменты/параметры сводной таблицы и Дизайн . Неважно, если вы допустите ошибку – легко удалить сводную таблицу и начать заново – такой возможности никогда не было у старых пользователей DOS Lotus 1-2-3.
Если вы работаете в Office 2007, возможно, вы захотите проверить нашу статью о том, как создать сводную таблицу в Excel 2007.
Мы включили книгу Excel, которую вы можете загрузить, чтобы попрактиковаться в своих навыках сводной таблицы. Он должен работать со всеми версиями Excel начиная с 97 года.
Скачать нашу Практикум Excel Workbook
