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