Работа с сводными таблицами в Microsoft Excel

Сводные таблицы являются одной из самых мощных функций 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, а именно PivotTables. Функция сводных таблиц, эксклюзивная для Excel, считалась настолько ошеломляюще полезной, что люди были готовы изучать весь новый пакет программ (Excel), а не придерживаться программы (1-2-3), в которой ее не было. Эта особенность, наряду с ошибочным суждением об успехе Windows, стала концом смерти для Lotus 1-2-3 и началом успеха Microsoft Excel..

Понимание сводных таблиц

Так что же такое сводная таблица,?

Проще говоря, сводная таблица представляет собой сводку некоторых данных, созданную для облегчения анализа этих данных. Но в отличие от сводки, созданной вручную, сводные таблицы Excel являются интерактивными. Создав его, вы можете легко изменить его, если он не дает точного представления о ваших данных, на которые вы надеялись. За пару кликов сводку можно «повернуть» - повернуть таким образом, чтобы заголовки столбцов стали заголовками строк, и наоборот. И еще многое можно сделать. Вместо того, чтобы пытаться описать все возможности сводных таблиц, мы просто продемонстрируем их…

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

Изучите данные, показанные ниже:

Обратите внимание, что это не необработанные данные. На самом деле, это уже какое-то резюме. В ячейке B3 мы видим 30 000 долларов, что, по-видимому, является суммой продаж Джеймса Кука за январь. Так где же необработанные данные? Как мы достигли цифры 30 000 долларов? Где находится исходный список транзакций продаж, из которых был создан этот показатель? Понятно, что где-то кто-то, должно быть, попытался сопоставить все транзакции продаж за последние шесть месяцев с итогом, который мы видим выше. Как вы думаете, сколько времени это заняло? Час? Десять?

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

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

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

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

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

Итак, мы запускаем Excel ... и загружаем такой список ...

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

Нажмите на любую ячейку в списке:

Затем из Вставить нажмите вкладку PivotTable значок:

Создать сводную таблицу появится окно с двумя вопросами: на каких данных должна основываться ваша новая сводная таблица и где ее следует создавать? Поскольку мы уже щелкнули ячейку в списке (на шаге выше), весь список, окружающий эту ячейку, уже выбран для нас ($ 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