Сводная таблица Excel - один из самых мощных инструментов, который поможет вам эффективно обобщать и анализировать большие наборы данных.
Сводная таблица - один из самых мощных и полезных инструментов суммирования данных в Excel, который позволяет быстро суммировать, сортировать, реорганизовывать, анализировать, группировать и подсчитывать большие наборы данных.
Сводная таблица позволяет вам вращать (или вращать) данные, хранящиеся в таблице, чтобы просматривать их с разных точек зрения и иметь четкое представление о больших наборах данных.
В этом руководстве вы найдете пошаговые инструкции по созданию и использованию сводных таблиц в Excel.
Организуйте свои данные
Чтобы создать сводную таблицу, ваши данные должны иметь структуру таблицы или базы данных. Итак, вам нужно организовать свои данные в строки и столбцы. Чтобы преобразовать диапазон данных в таблицу, выберите все данные, перейдите на вкладку «Вставить» и нажмите «Таблица». В диалоговом окне «Создание таблицы» нажмите «ОК», чтобы преобразовать набор данных в таблицу.
Использование таблицы Excel в качестве исходного набора данных для создания сводной таблицы делает вашу сводную таблицу динамической. Когда вы добавляете или удаляете записи в таблице Excel, данные в сводной таблице обновляются вместе с ними.
Предположим, у вас есть большой набор данных, как показано ниже, он состоит из более чем 500 записей и 7 полей. Дата, регион, тип продавца, компания, количество, доход и прибыль.
Вставить сводную таблицу
Сначала выберите все ячейки, содержащие данные, перейдите на вкладку «Вставка» и нажмите «Сводная диаграмма». Затем в раскрывающемся списке выберите «Сводная диаграмма и сводная таблица».
Откроется диалоговое окно «Создать сводную таблицу». Excel автоматически определит и заполнит правильный диапазон в поле «Таблица / диапазон», в противном случае выберет правильную таблицу или диапазон ячеек. Затем укажите целевое расположение для вашей сводной таблицы Excel, это может быть «Новый рабочий лист» или «Существующий рабочий лист», и нажмите «ОК».
Если вы выберете «Новый рабочий лист», новый лист с пустой сводной таблицей и сводной диаграммой будет создан на отдельном рабочем листе.
Создайте свою сводную таблицу
На новом листе вы увидите пустую сводную таблицу в левой части окна Excel и панель «Поля сводной таблицы» в правом углу окна Excel, где вы найдете все параметры для настройки вашей сводной таблицы.
Панель полей сводной таблицы разделена на два горизонтальных раздела: раздел полей (верхняя часть панели) и раздел макета (нижняя часть панели).
- В Полевая секция перечисляет все поля (столбцы), которые вы добавили в свою таблицу. Эти имена полей являются именами столбцов из исходной таблицы.
- В Раздел макета имеет 4 области, то есть фильтры, столбцы, строки и значения, с помощью которых вы можете упорядочивать и переупорядочивать поля.
Добавить поля в сводную таблицу
Чтобы создать сводную таблицу, перетащите поля из раздела «Поле» в области раздела «Макет». Вы также можете перетаскивать поля между областями.
Добавить строки
Начнем с добавления поля «Компания» в раздел «Строки». Обычно нечисловые поля добавляются в область строк макета. Просто перетащите поле «Comapny» в область «Row».
Все названия компаний из столбца «Компания» в исходной таблице будут добавлены в виде строк в сводную таблицу, и они будут отсортированы в порядке возрастания, но вы можете нажать кнопку раскрывающегося списка в ячейке «Ярлыки строк», чтобы изменить порядок.
Добавить значения
Вы добавили строку, теперь давайте добавим значение в эту таблицу, чтобы сделать ее одномерной. Вы можете создать одномерную сводную таблицу, добавив в области только названия строк или столбцов и их соответствующие значения. Область значений - это место, где хранятся вычисления / значения.
В приведенном выше примере снимка экрана у нас есть ряд компаний, но мы хотим узнать общую выручку каждой компании. Для этого просто перетащите поле «Доход» в поле «Значение».
Если вы хотите удалить какие-либо поля из раздела «Области», просто снимите флажок рядом с полем в разделе «Поля».
Теперь у нас есть одномерная таблица компаний (меток строк) вместе с суммой доходов.
Добавить столбец
Двумерный стол
Строки и столбцы вместе создадут двухмерную таблицу и заполнят ячейки третьим измерением значений. Предположим, вы хотите создать сводную таблицу, перечислив названия компаний в виде строк и используя столбцы для отображения дат и заполнив ячейки общим доходом.
Когда вы добавляете поле «Дата» в область «Столбец», Excel автоматически добавляет «Ежеквартально» и «Годы» в поля столбца для расчета и лучшего обобщения данных.
Теперь у нас есть двухмерная таблица с тремя измерениями значений.
Добавить фильтры
Если вы хотите отфильтровать данные по «Региону», вы можете перетащить поле «Регион» в область «Фильтр».
Это добавит раскрывающееся меню над сводной таблицей с выбранным «Полем фильтра». С его помощью вы можете отфильтровать доходы компаний за каждый год по регионам.
По умолчанию выбраны все регионы, снимите их и выберите только тот регион, по которому вы хотите фильтровать данные. Если вы хотите отфильтровать таблицу по нескольким записям, установите флажок рядом с «Выбрать несколько элементов» в нижней части раскрывающегося списка. И выберите несколько регионов.
Результат:
Сортировка
Если вы хотите отсортировать значения таблицы в порядке возрастания или убывания, щелкните правой кнопкой мыши любую ячейку в столбце «Сумма дохода», затем разверните «Сортировка» и выберите порядок.
Результат:
Группировка
Предположим, у вас есть данные, перечисленные в сводной таблице по месяцам, но вы не хотите видеть их ежемесячно, вместо этого вы хотите перераспределить данные по финансовым кварталам. Вы можете сделать это в своей сводной таблице.
Сначала выберите столбцы и щелкните их правой кнопкой мыши. Затем выберите вариант «Группа» из раскрывающегося списка.
В окне «Группировка» выберите «Кварталы» и «Годы», потому что мы хотим, чтобы они были организованы в финансовые кварталы каждый год. Затем нажмите «ОК».
Теперь ваши данные систематизированы по финансовым кварталам каждый год.
Настройки поля значений
По умолчанию сводная таблица суммирует числовые значения с помощью функции Sum. Но вы можете изменить тип расчета, который используется в области «Значения».
Чтобы изменить функцию сводки, щелкните правой кнопкой мыши любые данные в таблице, выберите «Суммировать значения по» и выберите свой вариант.
Кроме того, вы можете щелкнуть стрелку вниз рядом с «Сумма ..» в области значений в разделе поля и выбрать «Настройки поля значений».
В разделе «Параметры поля значений» выберите функцию для обобщения данных. Затем нажмите «ОК». В нашем примере мы выбираем «Подсчитать», чтобы подсчитать количество прибыли.
Результат:
Сводные таблицы Excel также позволяют отображать значения по-разному, например, отображать общие итоги в процентах или итоги по столбцам в процентах или итоги по строкам в процентах или упорядочивать значения от наименьшего к наибольшему и наоборот, и многое другое.
Чтобы отобразить значения в процентах, щелкните правой кнопкой мыши в любом месте таблицы, затем нажмите «Показать значения как» и выберите свой вариант.
Когда мы выбираем «% от общей суммы столбца», результат будет таким:
Обновите сводную таблицу
Хотя отчет сводной таблицы является динамическим, при внесении изменений в исходную таблицу Excel не обновляет данные в сводной таблице автоматически. Его необходимо «обновить» вручную, чтобы обновить данные.
Щелкните в любом месте сводной таблицы и перейдите на вкладку «Анализ», нажмите кнопку «Обновить» в группе «Данные». Чтобы обновить текущую сводную таблицу на листе, нажмите кнопку «Обновить». Если вы хотите обновить все сводные таблицы в книге, нажмите «Обновить все».
Кроме того, вы можете щелкнуть таблицу правой кнопкой мыши и выбрать опцию «Обновить».
Вот и все. Мы надеемся, что эта статья даст вам подробный обзор сводных таблиц Excel и поможет вам их создать.