Если вы читаете это руководство, есть большая вероятность, что вы слышали (или даже использовали) сводную таблицу Excel. Это одна из самых мощных функций Excel (без шуток).
Лучшее в использовании сводной таблицы заключается в том, что даже если вы ничего не знаете в Excel, вы все равно можете делать с ней довольно интересные вещи, имея очень базовое представление о ней.
Давайте начнем.
кликните сюда чтобы загрузить образцы данных и следовать их указаниям.
Что такое сводная таблица и почему вам это нужно?
Сводная таблица - это инструмент в Microsoft Excel, который позволяет быстро суммировать огромные наборы данных (с помощью нескольких щелчков мышью).
Даже если вы новичок в мире Excel, вы можете легко использовать сводную таблицу. Для создания отчетов достаточно просто перетащить заголовки строк / столбцов.
Предположим, у вас есть набор данных, как показано ниже:
Это данные о продажах, состоящие из ~ 1000 строк.
В нем есть данные о продажах по регионам, типам розничных продавцов и покупателям.
Теперь ваш босс может захотеть узнать несколько вещей из этих данных:
- Каковы были общие продажи в Южном регионе в 2016 году?
- Какие розничные сети входят в первую пятерку по объемам продаж?
- Каковы результаты деятельности The Home Depot по сравнению с другими розничными торговцами на Юге?
Вы можете продолжить и использовать функции Excel, чтобы дать вам ответы на эти вопросы, но что, если вдруг ваш босс предложит список еще из пяти вопросов.
Вам придется возвращаться к данным и создавать новые формулы при каждом изменении.
Вот где действительно пригодятся сводные таблицы Excel.
В течение нескольких секунд сводная таблица ответит на все эти вопросы (как вы узнаете ниже).
Но реальное преимущество заключается в том, что он может помочь вашему привередливому руководителю, работающему с данными, сразу же ответить на его вопросы.
Это настолько просто, что вы можете потратить несколько минут и показать своему боссу, как это сделать самому.
Надеюсь, теперь вы понимаете, почему сводные таблицы такие классные. Давайте продолжим и создадим сводную таблицу, используя набор данных (показанный выше).
Вставка сводной таблицы в Excel
Вот шаги для создания сводной таблицы с использованием данных, показанных выше:
- Щелкните в любом месте набора данных.
- Перейдите в Вставка -> Таблицы -> Сводная таблица.
- В диалоговом окне «Создание сводной таблицы» параметры по умолчанию в большинстве случаев работают нормально. Вот несколько вещей, которые стоит проверить:
- Таблица / Диапазон: Он заполняется по умолчанию на основе вашего набора данных. Если в ваших данных нет пустых строк / столбцов, Excel автоматически определит правильный диапазон. При необходимости вы можете изменить это вручную.
- Если вы хотите создать сводную таблицу в определенном месте, в разделе «Выберите, где вы хотите разместить отчет сводной таблицы» укажите Местоположение. В противном случае создается новый рабочий лист со сводной таблицей.
- Щелкните ОК.
Как только вы нажмете ОК, будет создан новый рабочий лист со сводной таблицей.
Пока сводная таблица создана, вы не увидите в ней никаких данных. Все, что вы увидите, - это имя сводной таблицы и однострочная инструкция слева, а поля сводной таблицы - справа.
Теперь, прежде чем мы перейдем к анализу данных с помощью этой сводной таблицы, давайте разберемся, из чего состоит сводная таблица Excel.
Основы сводной таблицы Excel
Чтобы эффективно использовать сводную таблицу, важно знать компоненты, которые создают сводную таблицу.
В этом разделе вы узнаете о:
- Сводный кеш
- Область ценностей
- Площадь строк
- Столбцы Площадь
- Область фильтров
Сводный кеш
Как только вы создаете сводную таблицу с использованием данных, что-то происходит в серверной части. Excel делает снимок данных и сохраняет их в своей памяти. Этот снимок называется сводным кешем.
Когда вы создаете разные представления с помощью сводной таблицы, Excel не возвращается к источнику данных, а использует сводный кеш для быстрого анализа данных и предоставления сводки / результатов.
Причина создания сводного кеша заключается в оптимизации работы сводной таблицы. Даже когда у вас есть тысячи строк данных, сводная таблица очень быстро суммирует данные. Вы можете перетаскивать элементы в поля строк / столбцов / значений / фильтров, и это мгновенно обновит результаты.
Примечание. Одним из недостатков сводного кеша является то, что он увеличивает размер вашей книги. Поскольку это копия исходных данных, при создании сводной таблицы копия этих данных сохраняется в сводном кэше.
Прочитайте больше: Что такое Pivot Cache и как его лучше всего использовать.
Область ценностей
Область значений - это то, что содержит вычисления / значения.
На основе набора данных, показанного в начале руководства, если вы хотите быстро рассчитать общий объем продаж по регионам за каждый месяц, вы можете получить сводную таблицу, как показано ниже (мы увидим, как ее создать позже в руководстве) .
Область, выделенная оранжевым цветом, - это область значений.
В этом примере это общий объем продаж за каждый месяц для четырех регионов.
Площадь строк
Заголовки слева от области значений составляют область строк.
В приведенном ниже примере область Rows содержит регионы (выделенные красным):
Столбцы Площадь
Заголовки вверху области значений составляют область столбцов.
В приведенном ниже примере область столбцов содержит месяцы (выделены красным):
Область фильтров
Область фильтров - это дополнительный фильтр, который можно использовать для дальнейшей детализации набора данных.
Например, если вы хотите видеть продажи только для многострочных розничных продавцов, вы можете выбрать этот вариант из раскрывающегося списка (выделено на изображении ниже), и сводная таблица будет обновляться данными только для многострочных розничных продавцов.
Анализ данных с помощью сводной таблицы
Теперь давайте попробуем ответить на вопросы, используя созданную нами сводную таблицу.
кликните сюда чтобы загрузить образцы данных и следовать их указаниям.
Чтобы анализировать данные с помощью сводной таблицы, вам необходимо решить, как вы хотите, чтобы сводка данных выглядела в конечном результате. Например, вы можете захотеть, чтобы все регионы были слева, а общий объем продаж - рядом с ними. Помня об этом, вы можете просто перетащить соответствующие поля в сводную таблицу.
В разделе Pivot Tabe Fields у вас есть поля и области (как выделено ниже):
Поля создаются на основе внутренних данных, используемых для сводной таблицы. Раздел «Области» - это место, где вы размещаете поля, и в зависимости от того, где находится поле, ваши данные обновляются в сводной таблице.
Это простой механизм перетаскивания, при котором вы можете просто перетащить поле и поместить его в одну из четырех областей. Как только вы это сделаете, он появится в сводной таблице на рабочем листе.
Теперь давайте попробуем ответить на вопросы вашего менеджера, используя эту сводную таблицу.
Q1: Каков был общий объем продаж в Южном регионе?
Перетащите поле «Регион» в область «Строки» и поле «Доход» в область «Значения». Это автоматически обновит сводную таблицу на листе.
Обратите внимание, что как только вы опускаете поле «Доход» в области «Значения», оно становится «Сумма дохода». По умолчанию Excel суммирует все значения для данного региона и показывает общую сумму. При желании вы можете изменить его на «Количество», «Среднее» или другие статистические показатели. В данном случае сумма - это то, что нам было нужно.
Ответ на этот вопрос будет 21225800.
Q2 Какие розничные сети входят в первую пятерку по объемам продаж?
Перетащите поле «Клиент» в область «Строка» и поле «Доход» в область значений. В случае, если в разделе области есть какие-либо другие поля, и вы хотите удалить их, просто выберите их и перетащите из него.
Вы получите сводную таблицу, как показано ниже:
Обратите внимание, что по умолчанию товары (в данном случае клиенты) отсортированы в алфавитном порядке.
Чтобы получить пятерку лучших розничных продавцов, вы можете просто отсортировать этот список и использовать имена пяти ведущих клиентов. Сделать это:
- Щелкните правой кнопкой мыши любую ячейку в области значений.
- Перейдите в раздел «Сортировка» -> «Сортировать от большего к меньшему».
Это даст вам отсортированный список на основе общего объема продаж.
Q3: Какова эффективность The Home Depot по сравнению с другими розничными торговцами на Юге?
Вы можете провести много анализа по этому вопросу, но давайте просто попробуем сравнить продажи.
Перетащите поле региона в область строк. Теперь перетащите поле «Клиент» в область «Строки» под полем «Регион». Когда вы это сделаете, Excel поймет, что вы хотите классифицировать свои данные сначала по регионам, а затем по клиентам в регионах. У вас будет что-то, как показано ниже:
Теперь перетащите поле «Доход» в область «Значения», и у вас будут продажи для каждого клиента (а также для всего региона).
Вы можете отсортировать розничных продавцов на основе показателей продаж, выполнив следующие действия:
- Щелкните правой кнопкой мыши ячейку со значением продаж для любого продавца.
- Перейдите в раздел «Сортировка» -> «Сортировать от большего к меньшему».
Это мгновенно отсортирует всех розничных продавцов по стоимости продажи.
Теперь вы можете быстро просмотреть Южный регион и определить, что продажи The Home Depot составили 3004600, и это лучше, чем у четырех розничных торговцев в Южном регионе.
Теперь есть несколько способов снять шкуру с кошки. Вы также можете поместить регион в область фильтра, а затем выбрать только южный регион.
кликните сюда чтобы загрузить образцы данных.
Я надеюсь, что это руководство даст вам базовый обзор сводных таблиц Excel и поможет вам начать работу с ними.
Вот еще несколько руководств по сводным таблицам, которые могут вам понравиться:
- Подготовка исходных данных для сводной таблицы.
- Как применить условное форматирование к сводной таблице в Excel.
- Как группировать даты в сводных таблицах в Excel.
- Как сгруппировать числа в сводной таблице в Excel.
- Как фильтровать данные в сводной таблице в Excel.
- Использование срезов в сводной таблице Excel.
- Как заменить пустые ячейки нулями в сводных таблицах Excel.
- Как добавить и использовать вычисляемые поля сводной таблицы Excel.
- Как обновить сводную таблицу в Excel.
