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