Как создать сводную таблицу в Excel для нарезки и нарезки данных
Microsoft Excel 2016, представленный в пакете Office, является новейшим и лучшим приложением для работы с электронными таблицами от Redmond, и есть веская причина, по которой многие перехватчики чисел используют его для удовлетворения всех своих потребностей. В то время как Excel подходит для простых таблиц, позволяющих отслеживать расходы или создавать календари, он работает самостоятельно, когда вам нужно нарезать кубиками, а затем представлять сложные данные. Здесь мы покажем вам, как создать сводную таблицу в Excel, чтобы воспользоваться одним из самых мощных инструментов приложения.
Прежде чем мы начнем, для чего именно нужны сводные таблицы? Проще говоря, сводные таблицы позволяют вам смотреть на одни и те же данные по-разному и под разными углами, чтобы упростить проведение углубленного анализа и выявить важные тенденции. Например, когда вы оцениваете результаты продаж, вам может потребоваться посмотреть на отдельного человека, конкретный продукт или конкретный период времени. С помощью сводной таблицы вы можете создать один пул информации, а затем легко сменить фокус с одного на другое — анализ, который было бы утомительно выполнять вручную.
Обратите внимание, что вам понадобится полная версия Excel 2016 для использования сводных таблиц. Универсальная платформа Windows (UWP) Версия Excel для Windows 10 не включает эту и многие другие расширенные функции Excel. Наконец, если вы хотите опробовать новые функции Excel, присоединитесь к программе Office Insider и, возможно, станьте первым, кто испытает новую функцию сводной таблицы.
Шаг 1: подготовьте ваши данные
Возможно, наиболее важным шагом в использовании сводных таблиц Excel является тщательная организация ваших данных. Самый простой способ сделать это — использовать таблицы Excel, которые позволяют добавлять строки, которые будут включаться в сводную таблицу при каждом нажатии кнопки «Обновить». Но, по крайней мере, вы хотите, чтобы ваши данные были в табличной форме с информативными заголовками столбцов и с согласованными данными в столбцах.
Например, предположим, что вы хотите проанализировать информацию о продажах для вашей компании. У вас есть шесть продавцов, которые продают три продукта в нескольких регионах. Ваши продукты адаптированы для каждого клиента, поэтому цены варьируются. Вот пример таблицы с вымышленной информацией, чтобы дать вам представление о том, как данные могут быть организованы с учетом сводной таблицы.
Таблица была создана путем простого ввода данных, выбора всего диапазона и перехода в меню «Вставка»> «Таблица». Опять же, вам не нужно предпринимать этот шаг, но рекомендуется, если вы хотите добавить больше строк данных позже и упростить обновление сводной таблицы.
Марк Коппок / Digital Trends
Шаг 2: Попробуйте рекомендацию
Excel полон изящных приемов, облегчающих работу с данными, и, когда это возможно, он будет пытаться угадать, чего вы хотите достичь, а затем автоматически выполнит несколько шагов. Этот полезный характер демонстрируется здесь инструментом Recommended PivotTables, который просматривает ваши данные и предлагает некоторые логические варианты того, как анализировать и представлять вещи.
Чтобы использовать рекомендуемую сводную таблицу, просто перейдите в «Вставка»> «Рекомендуемые сводные таблицы». Excel представит вам несколько вариантов для рассмотрения. В нашем примере Excel предлагает создать 10 различных сводных таблиц, которые учитывают различные аспекты наших данных о продажах. Обратите внимание, что то, как вы маркируете столбцы, имеет значение Excel читает эти заголовки и предлагает рекомендации, которые имеют смысл. Например, если вы хотите посмотреть продажные цены, не используйте термин «стоимость», потому что Excel будет соответствующим образом основывать свои рекомендации.
Одной из рекомендаций является «Сумма общих продаж клиентом». Если мы выберем эту опцию, Excel продолжит создание сводной таблицы.
Марк Коппок / Digital Trends
Обратите внимание, что в сводной таблице отображаются только те данные, которые имеют отношение к нашему нынешнему анализу. Справа вы найдете критерии, которые Excel использовал для его создания, в диалоговом окне Поля сводной таблицы. Мы рассмотрим, что означает каждое из этих полей, в следующем разделе о настройке.
Шаг 3: Настройте сводную таблицу
Каждый из элементов в этом диалоговом окне важен для определения того, как будет работать ваша сводная таблица. Нажмите на значок конфигурации, чтобы изменить внешний вид этого диалогового окна так, чтобы оно подходило именно вам.
Выбор полей
Здесь вы выбираете, какие столбцы включить в сводную таблицу. То, как эти данные будут заполняться в сводной таблице, определяется типом данных, которые они представляют. Excel определит, следует ли добавить столбец в сводную таблицу или добавить данные поля в таблицу. Например, если вы выбираете «Товары», Excel предполагает, что вы хотите добавить количество товаров для каждого клиента.
Марк Коппок / Digital Trends
С другой стороны, если вы выберете «Дата», Excel поместит данные в таблицу и упорядочит продажи по времени их возникновения.
Марк Коппок / Digital Trends
Работая над своими собственными сводными таблицами, вы можете поэкспериментировать, чтобы увидеть, как добавление и удаление полей влияет на отображаемые данные. Вы обнаружите, что Excel отлично справляется с выбором, который имеет смысл, но вы также можете изменить положение вещей, если выбор Excel не имеет смысла.
фильтры
Перетащите элементы из списка выбора полей в раздел «Фильтры», если хотите ограничить отображаемые данные. Например, если вы перетащите «Клиент» в раздел «Фильтры», вы сможете легко отобразить только данные одного или нескольких клиентов.
Марк Коппок / Digital Trends
Колонны
Перетаскивая поля в раздел «Столбцы», вы можете расширить способ представления своих данных. Опять же, когда вы перетаскиваете поле в этот раздел, Excel попытается выяснить, как вы хотите представить данные. Например, если вы перетащите «Дата» в раздел «Столбцы», то Excel отобразит объем продаж в кратком виде для наиболее логичного периода, который в данном случае составляет месяц. Это было бы полезно, если бы вашей главной заботой было то, сколько было продано ежемесячно с прицелом на покупательскую структуру.
Марк Коппок / Digital Trends
Ряды
Аналогично, вы можете перетащить поля в раздел «Строки», чтобы встроить разные данные в строки сводной таблицы. Опять же, если мы перетащим «Дата» в раздел «Строки», Excel будет разбивать продажи по покупателям за месяц, но данные будут суммироваться по покупателям, а не по месяцам, как в предыдущем примере. В этом случае вы больше всего беспокоитесь о том, сколько вы продали каждому покупателю, но вы также хотите определить тенденции, основанные на времени.
Марк Коппок / Digital Trends
Ценности
Наконец, раздел «Значения» определяет, как вы анализируете свои данные. До сих пор во всех наших примерах мы рассматривали общий объем продаж. Если вы нажмете клавишу со стрелкой вниз в этом разделе, вы можете настроить параметры поля значений для просмотра другого численного расчета.
Марк Коппок / Digital Trends
Например, предположим, что вы хотите посмотреть на средние продажи вместо общих продаж. Вы бы просто выбрали «Среднее» в диалоговом окне настроек поля значений. Вы также можете установить числовой формат так, чтобы результаты были наиболее подходящими.
Марк Коппок / Digital Trends
Теперь, вместо того, чтобы рассматривать общий объем продаж по клиентам, а затем вычислять общую сумму, мы смотрим на средние продажи по компаниям, а затем средние продажи по компании. Это было бы полезно при оценке, например, кто выше или ниже среднего по продажам и, следовательно, кто заслуживает наибольшего (или наименьшего) внимания. В нашем примере, возможно, Acme Engineering и Jones Roofing не заслуживают такого же внимания со стороны продаж, как другие.
Очевидно, что сводные таблицы предлагают множество вариантов, чтобы упростить нарезку и нарезку данных. Хитрость в эффективном использовании сводных таблиц состоит в том, чтобы решить, что именно вы хотите видеть, прежде чем начать применять параметры. Вы также хотите убедиться, что ваши данные соответствуют тому, как вы выложили свою таблицу и как вы назвали свои заголовки. Чем тщательнее вы настраиваете, тем эффективнее будут ваши сводные таблицы.
Шаг 4: Создайте свои собственные сводные таблицы с нуля
Как только вы поигрались с функцией сводной таблицы и получили некоторое представление о том, как различные параметры влияют на ваши данные, вы можете начать создавать сводную таблицу с нуля. Процесс аналогичен использованию рекомендации, только перейдите в меню «Вставка»> «Сводная таблица», а затем вручную выберите данные в качестве первого шага.
В нашем случае это означает выбор таблицы 1, но мы также можем выбрать диапазон данных или извлечь данные из внешнего источника данных. Мы также можем решить, хотим ли мы создать новый рабочий лист или разместить сводную таблицу рядом с нашими данными в определенном месте на существующем рабочем листе.
Марк Коппок / Digital Trends
После того, как мы сделали свой выбор, нам представили пустую сводную таблицу и наш диалог Поля сводной таблицы.
Марк Коппок / Digital Trends
Создание нашей собственной сводной таблицы — это просто вопрос выбора полей и определения способа расчета и отображения данных. Допустим, мы хотим посмотреть, как продавцы выступали в месяц, а общий объем продаж за год. Мы должны выбрать поля «Продавец», «Дата» и «Общий объем продаж», перетащить поле «Продавец» в раздел «Фильтры» и настроить значения для отображения в виде валюты. Excel автоматически добавляет соответствующие элементы даты в раздел «Строки» и предполагает, что мы хотим видеть суммы.
Марк Коппок / Digital Trends
По умолчанию Excel покажет все данные для всех продавцов. Мы также можем выбрать одного продавца, чтобы увидеть только его или ее данные. В этом случае мы видим, что у Джуди Френч были продажи всего за три месяца, хотя ее общие продажи были значительными. Это может означать, что ее процесс продаж был более длительным, потому что она охотилась на китов, а не на рыб — ценный вывод, если он точный. Возможно, было бы неплохо инвестировать в помощника, чтобы помочь Джуди быстрее завершить продажи.
Марк Коппок / Digital Trends
Шаг 5: инвестировать в обучение
Если вы хотите по-настоящему хорошо использовать сводные таблицы Excel, потратьте немного времени на изучение того, как используются различные типы данных. Microsoft предлагает свои собственные учебные ресурсы, и есть множество сторонних инструкторов.
Как правило, это означает копание в Excel способом, выходящим за рамки данного руководства. Тем не менее, надеюсь, вы теперь видите, как сводные таблицы могут быть мощным инструментом для анализа ваших данных, и относительно легко начать работу, если ваши данные сконфигурированы в нужной таблице. И мы не можем не подчеркнуть, насколько важно знать, чего вы хотите достичь с помощью сводной таблицы, прежде чем начать.
