Как сделать пивот таблицу в excel
Надстройка для Excel — Power Pivot, или жизнь после 1 048 576 строк
Как показывает практика, если в файле Excel больше 50 тысяч строк, да еще формулы типа ВПР, он падает и умирает. Потом восстает, как зомби, чтобы выпить нашу кровь и нервы. Ведет он себя тоже как зомби — еле двигается и «ни черта» не соображает.
Что же делать? Ответ простой: начать работать с надстройкой для Excel — Power Pivot. Этот инструмент создан для работы с данными. Он может легко обрабатывать миллионы строк!
Надстройка Power Pivot в Excel
Power Pivot – это надстройка Excel, с помощью которой можно работать с данными в несколько миллионов строк, объединять таблицы в модель данных и создавать аналитические вычисления.
В «обычном» Excel пользователи ограничены количеством строк в таблице – не более размера листа в 1 048 тысяч строк, но в Power Pivot такого ограничения нет. Надстройка может подключаться к данным из внешних источников и работать с большими объемами информации в миллионы строк.
Открыть надстройку Power Pivot можно, нажав на вкладке меню Power Pivot кнопку Управление. Эта вкладка выглядит одинаково во всех версиях Excel.
Хорошая новость: начиная c версий после 2019 года компания Microsoft анонсировала включение Power Pivot во все версии Excel.
Работа с данными в Power Pivot
Как правило, разработка отчетов в Power Pivot происходит в следующем порядке:
Подключения к источникам, связи и вычисления настраиваются в отчете один раз. При изменении исходных данных отчеты можно обновить в меню Данные → Обновить все. Давайте разберем подробнее, как это работает.
Добавление данных в Power Pivot
Чтобы начать работать с Power Pivot, перейдите на вкладку меню Power Pivot → нажмите Управление. Добавить данные в открывшейся надстройке можно несколькими способами:
Способ 1. Подключение к данным с помощью встроенных инструментов импорта.
В Power Pivot есть свои инструменты для импорта внешних данных, которые можно найти на вкладке Главная → кнопки Из базы данных, Из службы данных, Из других источников.
С помощью встроенных инструментов настраивается подключение к 15 видам источников данных.
Увидеть весь список можно в окне «Мастер импорта таблиц», которое открывается в меню Главная → Из других источников.
Настроим подключение к данным на примере файла Excel. Укажите путь к файлу, поставьте галочку «Использовать первую строку в качестве заголовков столбцов», выберите таблицы, жмем «Готово». У вас в окне включится счетчик импорта строк — работает довольно быстро. В результате импорта в окне Power Pivot появятся вкладки с таблицами.
Способ 2. Добавить данные из Power Query.
Загрузка данных с помощью инструментов Power Pivot делается легко, но Power Query лучше подходит для импорта и значительно расширяет возможности аналитики. В нем намного больше доступных источников и возможностей для обработки таблиц произвольного вида.
Чтобы настроить подключение с помощью Power Query, вам нужно создать запрос к источнику данных. Список ранее созданных запросов находится на вкладке «Запросы и подключения». Нажмите на запрос правой кнопкой мышки и выберите Загрузить в… В открывшемся окне доступных вариантов импорта поставьте галочку «Добавить эти данные в модель данных». Задать настройки импорта также можно в самом редакторе Power Query.
К сожалению, в Excel 2010 Power Pivot почти невозможно «подружить» с Power Query и этот новый функционал в старом Excel сильно ограничен.
Интерфейс Power Pivot
Разберем подробнее интерфейс Power Pivot.
В окне Power Pivot есть:
Модель данных и связи
Чтобы перейти к настройке связей между таблицами, выберите в меню Главная → Представление диаграммы (вернутся обратно к просмотру таблиц можно, нажав Представление данных).
Модель данных в Power Pivot – это набор таблиц, объединенных связями.
Графически связь таблиц обозначается линией между ними, как в примере на рисунке. Чтобы создать связь, выделите мышкой поле в одной таблице и «перетащите» его на соответствующее ему поле в области другой таблицы.
Power Pivot поддерживает типы связей «один к одному», «один ко многим».
Если выделить мышкой линию связи в модели данных, то можно увидеть, с помощью каких полей задана связь. Выделенные линии можно удалять. Или, щелкнув по ним дважды, менять связи в открывшемся окне. Также управление связями доступно в окне, которое открывается в меню Конструктор → Управление связями.
Вычисления в Power Pivot
Формулы Power Pivot пишут на языке DAX (Data Analysis Expressions, выражения для анализа данных). DAX-формулы позволяют, по аналогии с формулами Excel, выполнять вычисления и/или настраивать произвольную фильтрацию и представление данных в таблицах.
Язык DAX впервые появился в 2010 году вместе с надстройкой Power Pivot. В этом языке сотни функций, с помощью которых можно создавать аналитические расчеты. Кроме Power Pivot в Excel, DAX-формулы также доступны в Power BI и Analysis Services. То есть эти формулы вам точно пригодятся.
Вычисления с помощью DAX-формул создаются в виде:
Вычисляемые столбцы представляют собой столбцы в таблицах данных, созданные с помощью формул. Чтобы добавить такой столбец, щелкните мышкой дважды по столбцу слева «Добавление столбца», введите название вычисления, а затем знак «=» и формулу в строке формул.
Вычисляемый столбец похож на любой другой столбец в таблице. Также с помощью таких столбцов можно создавать связи в модели. Вычисляемые столбцы в Excel рассчитываются в момент загрузки (обновления) данных, а значения в самом столбце сохраняются, увеличивая размер файла. То есть столбцы – это статические вычисления.
Как и вычисляемые столбцы, меры тоже создаются с помощью DAX-формул. В отличие от столбцов, меры – это динамические вычисления, результат которых зависит от контекста – его можно увидеть в отчете, где мы задаем в разрезе каких полей, фильтров и др. вычислить меру. Меры записываются под таблицей в области вычислений.
Меры в Power Pivot можно превратить в KPI – ключевые показатели эффективности. Для этого выделите меру и нажмите на кнопку Создать KPI в меню Главная. Кроме мер, созданных пользователями, в Excel также есть неявные меры. Они создаются автоматически при формировании сводной таблицы, когда пользователь помещает данные в область значений. Чтобы посмотреть, есть ли у вас в Power Pivot неявные меры, выберите на вкладке Главная → Показать скрытые.
Excel Power Pivot — управление моделью данных
Основное использование Power Pivot — это его способность управлять таблицами данных и связями между ними, чтобы облегчить анализ данных из нескольких таблиц. Вы можете добавить таблицу Excel в модель данных во время создания сводной таблицы или непосредственно из ленты PowerPivot.
Вы можете анализировать данные из нескольких таблиц, только если между ними существуют отношения. С Power Pivot вы можете создавать отношения из представления данных или представления схемы. Более того, если вы решили добавить таблицу в Power Pivot, вам также необходимо добавить отношения.
Добавление таблиц Excel в модель данных с помощью сводной таблицы
При создании сводной таблицы в Excel она основана только на одной таблице / диапазоне. Если вы хотите добавить больше таблиц в сводную таблицу, вы можете сделать это с помощью модели данных.
Предположим, у вас есть две таблицы в вашей книге —
Один содержит данные о продавцах и регионах, которые они представляют, в таблице — продавец.
Другой, содержащий данные о продажах по регионам и месяцам, в таблице — Продажи.
Один содержит данные о продавцах и регионах, которые они представляют, в таблице — продавец.
Другой, содержащий данные о продажах по регионам и месяцам, в таблице — Продажи.
Вы можете суммировать продажи в зависимости от продавца, как указано ниже.
Нажмите на таблицу — Продажа.
Нажмите вкладку INSERT на ленте.
Выберите Сводная таблица в группе Таблицы.
Нажмите на таблицу — Продажа.
Нажмите вкладку INSERT на ленте.
Выберите Сводная таблица в группе Таблицы.
Нажмите на БОЛЬШЕ СТОЛОВ.
Нажмите на БОЛЬШЕ СТОЛОВ.
Откроется окно сообщения Создать новую сводную таблицу. Отображаемое сообщение: чтобы использовать несколько таблиц в анализе, необходимо создать новую сводную таблицу с помощью модели данных. Нажмите Да
Новая сводная таблица будет создана, как показано ниже —
Нажмите вкладку ВСЕ.
Две таблицы — Sales и Salesperson с соответствующими полями отображаются в списке полей сводной таблицы.
Щелкните поле «Продавец» в таблице «Продавец» и перетащите его в область ROWS.
Щелкните поле «Месяц» в таблице «Продажи» и перетащите его в область ROWS.
Щелкните поле «Сумма заказа» в таблице «Продажи» и перетащите его в область ∑ ЗНАЧЕНИЯ.
Нажмите вкладку ВСЕ.
Две таблицы — Sales и Salesperson с соответствующими полями отображаются в списке полей сводной таблицы.
Щелкните поле «Продавец» в таблице «Продавец» и перетащите его в область ROWS.
Щелкните поле «Месяц» в таблице «Продажи» и перетащите его в область ROWS.
Щелкните поле «Сумма заказа» в таблице «Продажи» и перетащите его в область ∑ ЗНАЧЕНИЯ.
Нажмите кнопку СОЗДАТЬ рядом с сообщением. Откроется диалоговое окно « Создать связь ».
Под таблицей выберите Продажи.
Под полем Column (Foreign) выберите Region.
Под связанной таблицей выберите продавца.
В поле « Связанный столбец (основной)» выберите «Регион».
Под таблицей выберите Продажи.
Под полем Column (Foreign) выберите Region.
Под связанной таблицей выберите продавца.
В поле « Связанный столбец (основной)» выберите «Регион».
Ваша сводная таблица из двух таблиц на двух листах готова.
Кроме того, как заявил Excel при добавлении второй таблицы в сводную таблицу, сводная таблица была создана с использованием модели данных. Чтобы проверить, сделайте следующее —
Нажмите вкладку POWERPIVOT на ленте.
Нажмите « Управление» в группе «Модель данных». Появится представление данных Power Pivot.
Нажмите вкладку POWERPIVOT на ленте.
Нажмите « Управление» в группе «Модель данных». Появится представление данных Power Pivot.
Вы можете заметить, что две таблицы Excel, которые вы использовали при создании сводной таблицы, преобразуются в таблицы данных в модели данных.
Добавление таблиц Excel из другой книги в модель данных
Предположим, что две таблицы — Salesperson и Sales находятся в двух разных рабочих книгах.
Вы можете добавить таблицу Excel из другой книги в модель данных следующим образом:
Нажмите на таблицу продаж.
Нажмите вкладку INSERT.
Нажмите Сводная таблица в группе Таблицы. Откроется диалоговое окно « Создание сводной таблицы ».
Нажмите на таблицу продаж.
Нажмите вкладку INSERT.
Нажмите Сводная таблица в группе Таблицы. Откроется диалоговое окно « Создание сводной таблицы ».
В поле Таблица / Диапазон введите Продажи.
Нажмите на новый лист.
Установите флажок Добавить эти данные в модель данных.
В поле Таблица / Диапазон введите Продажи.
Нажмите на новый лист.
Установите флажок Добавить эти данные в модель данных.
Вы получите пустую сводную таблицу на новом листе только с полями, соответствующими таблице продаж.
Вы добавили данные таблицы продаж в модель данных. Затем вы должны получить данные таблицы Salesperson также в Data Model следующим образом:
Нажмите на лист, содержащий таблицу продаж.
Нажмите вкладку ДАННЫЕ на ленте.
Нажмите Существующие подключения в группе Получить внешние данные. Откроется диалоговое окно «Существующие подключения».
Нажмите на вкладку Таблицы.
Нажмите на лист, содержащий таблицу продаж.
Нажмите вкладку ДАННЫЕ на ленте.
Нажмите Существующие подключения в группе Получить внешние данные. Откроется диалоговое окно «Существующие подключения».
Как сделать пивот таблицу в excel
Создание отчетов с помощью Сводных таблиц (Pivot Tables)
Представьте, что вам нужно с большой таблицы, которая содержит множество разнообразной информации сделать краткий аналитический отчет для принятия решений. Предположим, вы имеете данные, где отображается информация по кредитному портфелю Банка, где есть записанная подробная информация по каждому заемщику. Поэтому, для анализа кредитного портфеля, из базы данных в Excel выгружается примерно вот-такая таблица:
В ней каждая отдельная строка содержит полную информацию об одной кредитной сделке:
Естественно, если менеджеры по продажам знают свое дело и вкалывают всерьез, то каждый день этой таблице будет дописываться несколько десятков строк и до конца, например, года или хотя бы квартала размеры таблицы станут ужасными.
Однако еще больший ужас вызовет у Вас необходимость создания отчетов по этим данным. Сколько выдали кредитов в каждом месяце? Какова величина кредитного портфеля в разрезе направлений кредитования по количеству и сумме? Десять крупнейших заемщиков? и т.д.
Ответы на все вопросы можно получить легче, чем Вы думаете. Минуты так за три, примерно. С помощью одного из самых потрясающих инструментов Microsoft Excel — Сводных таблиц.
Выделяем всю нашу таблицу с данными и жмем в меню Вставка — Сводная таблица ( Insert — PivotTable ).
Появится вот такое окно, в котором мы можем выбрать куда поместить нашу сводную таблицу и скорректировать диапазон данных.
Работа с макетом
То, что Вы увидите, нажав кнопку OK называется макет (layout) сводной таблицы:
Работать с ним несложно — надо перетаскивать мышью названия столбцов (полей) из окна Список полей сводной таблицы (PivotTable Field List) в области строк (Row Labels), столбцов (Column Lables), фильтров (Report Filter) и данных макета (Values). Единственный нюанс — делайте это точнее, не промахнитесь! Поехали.
В процессе перетаскивания сводная таблица у Вас на глазах начнет менять облик, отражая те данные, которые Вам необходимы. Перебросив все четыре нужных нам поля из списка, Вы получите практически готовый отчет. Останется его только достойно отформатировать.
Выбираем форматирование сводной таблицы в меню PivotTables Tools — Design:
И получаем следующий вид:
Не так уж это все и сложно, не так ли?
Что такое сводная таблица Excel
Что такое сводная таблица (Pivot Table – англ.)? Pivot Table дословно переводится как «таблица, которую можно крутить, показывать в разных разворотах». Это инструмент, который позволяет представлять данные в виде, удобном для анализа. Вид сводной таблицы можно быстро менять с помощью одной только мышки, помещая данные в строки или столбцы, выбирать уровни группировки, фильтровать и «перетаскивать» мышкой столбцы с одного места на другое.
Также к сводным таблицам можно применять элементы управления и добавлять диаграммы, создавая наглядные отчеты. Примеры таких отчетов можно посмотреть здесь:
Исходные данные для сводной таблицы
Чтобы построить сводную таблицу, нужно обратиться к данным в Excel, организованным в виде плоской таблицы. Это значит, что все строки такой таблицы заполнены и в ней нет группировок.
Как построить сводную таблицу
Шаг 1. Выделить таблицу Excel
Выделите одну ячейку таблицы (тогда Excel автоматически определит границы таблицы на следующем шаге) или выделите всю таблицу вместе с заголовками.
Как быстро выделить таблицу:
Если выделить больше одной ячейки, но не всю таблицу, в качестве источника данных будет захвачена только выделенная область.
Шаг 2. Создать сводную таблицу
Создайте сводную таблицу: перейдите на вкладку Вставка и выберите «Сводная таблица».
В появившемся диалоговом окне укажите исходные данные (если вы уже выделили таблицу, источник данных заполнится автоматически) и желаемое место расположения сводной таблицы. Её можно поместить на новый или существующий лист.
Когда сводная таблица добавлена, на листе появляется область сводной таблицы. Если эта область не активна (вы не выделили ее мышкой), на ней будет подсказка: «Чтобы начать работу с отчетом сводной таблицей, щелкните в этой области». Щелкаем по ней мышкой и происходят две вещи:
Шаг 3. Добавить в сводную таблицу необходимые поля
Проставляем «галочки» в нужных полях сводной таблицы. При этом элементы «сами» встанут на свои места. Если просто поставить «галочки» в области выбора полей, Excel в зависимости от содержимого ячеек определит куда что ставить. Если в столбце содержатся только значения в числовом формате, то его содержимое попадет в область «Σ Значения».
Правило следующее: если поле содержит текст или числа хотя бы с одной пустой или текстовой ячейкой, то Excel автоматически поместит эти данные в область «Названия строк».
После заполнения областей сводной таблицы её вид изменится. В нашем примере в строках появились ФИО менеджеров и товары, а напротив них – суммы продаж. Далее данные можно детализировать и создать визуализации.
Как сделать пивот таблицу в excel?
Представьте себя в роли руководителя отдела продаж. У Вашей компании есть два склада, с которых вы отгружаете заказчикам, допустим, овощи-фрукты. Для учета проданного в Excel заполняется вот такая таблица:
В ней каждая отдельная строка содержит полную информацию об одной отгрузке (сделке, партии):
Естественно, если менеджеры по продажам знают свое дело и пашут всерьез, то каждый день к этой таблице будет дописываться несколько десятков строк и к концу, например, года или хотя бы квартала размеры таблицы станут ужасающими. Однако еще больший ужас вызовет у Вас необходимость создания отчетов по этим данным. Например:
Ответы на все вышеперечисленные и многие аналогичные вопросы можно получить легче, чем Вы думаете. Нам потребуется один из самых ошеломляющих инструментов Microsof Excel — сводные таблицы.
Если у вас Excel 2003 или старше
Ставим активную ячейку в таблицу с данными (в любое место списка) и жмем в меню Данные — Сводная таблица (Data — PivotTable and PivotChartReport). Запускается трехшаговый Мастер сводных таблиц (Pivot Table Wizard). Пройдем по его шагам с помощью кнопок Далее (Next) и Назад (Back) и в конце получим желаемое.
Шаг 1. Откуда данные и что надо на выходе?
На этом шаге необходимо выбрать откуда будут взяты данные для сводной таблицы. В нашем с Вами случае думать нечего — «в списке или базе данных Microsoft Excel». Но. В принципе, данные можно загружать из внешнего источника (например, корпоративной базы данных на SQL или Oracle). Причем Excel «понимает» практически все существующие типы баз данных, поэтому с совместимостью больших проблем скорее всего не будет. Вариант В нескольких диапазонах консолидации (Multiple consolidation ranges) применяется, когда список, по которому строится сводная таблица, разбит на несколько подтаблиц, и их надо сначала объединить (консолидировать) в одно целое. Четвертый вариант «в другой сводной таблице…» нужен только для того, чтобы строить несколько различных отчетов по одному списку и не загружать при этом список в оперативную память каждый раз.
Вид отчета — на Ваш вкус — только таблица или таблица сразу с диаграммой.
Шаг 2. Выделите исходные данные, если нужно
На втором шаге необходимо выделить диапазон с данными, но, скорее всего, даже этой простой операции делать не придется — как правило Excel делает это сам.
Шаг 3. Куда поместить сводную таблицу?
На третьем последнем шаге нужно только выбрать местоположение для будущей сводной таблицы. Лучше для этого выбирать отдельный лист — тогда нет риска что сводная таблица «перехлестнется» с исходным списком и мы получим кучу циклических ссылок. Жмем кнопку Готово (Finish) и переходим к самому интересному — этапу конструирования нашего отчета.
Работа с макетом
То, что Вы увидите далее, называется макетом (layout) сводной таблицы. Работать с ним несложно — надо перетаскивать мышью названия столбцов (полей) из окна Списка полей сводной таблицы (Pivot Table Field List) в области строк (Rows), столбцов (Columns), страниц (Pages) и данных (Data Items) макета. Единственный нюанс — делайте это поточнее, не промахнитесь! В процессе перетаскивания сводная таблица у Вас на глазах начнет менять вид, отображая те данные, которые Вам необходимы. Перебросив все пять нужных нам полей из списка, Вы должны получить практически готовый отчет.
Останется его только достойно отформатировать:
Если у вас Excel 2007 или новее
В последних версиях Microsoft Excel 2007-2010 процедура построения сводной таблицы заметно упростилась. Поставьте активную ячейку в таблицу с исходными данными и нажмите кнопку Сводная таблица (Pivot Table) на вкладке Вставка (Insert). Вместо 3-х шагового Мастера из прошлых версий отобразится одно компактное окно с теми же настройками:
В нем, также как и ранее, нужно выбрать источник данных и место вывода сводной таблицы, нажать ОК и перейти к редактированию макета. Теперь это делать значительно проще, т.к. можно переносить поля не на лист, а в нижнюю часть окна Список полей сводной таблицы, где представлены области:
Перетаскивать поля в эти области можно в любой последовательности, риск промахнуться (в отличие от прошлых версий) — минимален.
Единственный относительный недостаток сводных таблиц — отсутствие автоматического обновления (пересчета) при изменении данных в исходном списке. Для выполнения такого пересчета необходимо щелкнуть по сводной таблице правой кнопкой мыши и выбрать в контекстном меню команду Обновить (Refresh).
Ссылки по теме
В этой части самоучителя подробно описано, как создать сводную таблицу в Excel. Данная статья написана для версии Excel 2007 (а также для более поздних версий). Инструкции для более ранних версий Excel можно найти в отдельной статье: Как создать сводную таблицу в Excel 2003?
В качестве примера рассмотрим следующую таблицу, в которой содержатся данные по продажам компании за первый квартал 2016 года:
| Date | Invoice Ref | Amount | Sales Rep. | Region |
| 01/01/2016 | 2016-0001 | $819 | Barnes | North |
| 01/01/2016 | 2016-0002 | $456 | Brown | South |
| 01/01/2016 | 2016-0003 | $538 | Jones | South |
| 01/01/2016 | 2016-0004 | $1,009 | Barnes | North |
| 01/02/2016 | 2016-0005 | $486 | Jones | South |
| 01/02/2016 | 2016-0006 | $948 | Smith | North |
| 01/02/2016 | 2016-0007 | $740 | Barnes | North |
| 01/03/2016 | 2016-0008 | $543 | Smith | North |
| 01/03/2016 | 2016-0009 | $820 | Brown | South |
| … | … | … | … | … |
Для начала создадим очень простую сводную таблицу, которая покажет общий объем продаж каждого из продавцов по данным таблицы, приведённой выше. Для этого необходимо сделать следующее:
В данном примере в столбце Amount содержатся числовые значения, поэтому в области Σ Значения (Σ Values) будет по умолчанию выбрано Сумма по полю Amount (Sum of Amount). Если же в столбце Amount будут содержаться нечисловые или пустые значения, то в сводной таблице по умолчанию может быть выбрано Количество по полю Amount (Count of Amount). Если так случилось, то Вы можете изменить количество на сумму следующим образом:
Сводная таблица будет заполнена итогами продаж по каждому продавцу, как показано на рисунке выше.
Если необходимо отобразить объемы продаж в денежных единицах, следует настроить формат ячеек, которые содержат эти значения. Самый простой способ сделать это – выделить ячейки, формат которых нужно настроить, и выбрать формат Денежный (Currency) в разделе Число (Number) на вкладке Главная (Home) Ленты меню Excel (как показано ниже).
В результате сводная таблица примет вот такой вид:
Обратите внимание, что формат валюты, используемый по умолчанию, зависит от настроек системы.
Рекомендуемые сводные таблицы в последних версиях Excel
В последних версиях Excel (Excel 2013 или более поздних) на вкладке Вставка (Insert) присутствует кнопка Рекомендуемые сводные таблицы (Recommended Pivot Tables). Этот инструмент на основе выбранных исходных данных предлагает возможные форматы сводных таблиц. Примеры можно посмотреть на сайте Microsoft Office.
Урок подготовлен для Вас командой сайта office-guru.ru
Источник: /> Перевел: Антон Андронов
Правила перепечаткиЕще больше уроков по Microsoft Excel
Оцените качество статьи. Нам важно ваше мнение:
Обрабатывать большие объемы информации и составлять сложные многоуровневые отчеты достаточно непросто без использования средств автоматизации. Excel 2010 как раз и является инструментом, позволяющим упростить эти задачи, путем создания сводных (перекрестных) таблиц данных (Pivot table).
Сводная таблица в Excel 2010 используется для:
Создавать сводные таблицы можно двумя способами. Рассмотрим каждый из них.
Способ 1. Создание сводных таблиц, используя стандартный инструмент Excel 2010 «Сводная таблица»
Перед тем как создавать отчет сводной таблицы, определимся, что будет использоваться в качестве источника данных. Рассмотрим вариант с источником, находящимся в этом же документе.
1. Для начала создайте простую таблицу с перечислением элементов, которые вам нужно использовать в отчете. Верхняя строка обязательно должна содержать заголовки столбцов.
2. Откройте вкладку «Вставка» и выберите из раздела «Таблицы» инструмент «Сводная таблица».
Если вместе со сводной таблицей нужно создать и сводную диаграмму – нажмите на стрелку в нижнем правом углу значка «Сводная таблица» и выберите пункт «Сводная диаграмма».
3. В открывшемся диалоговом окне «Создание сводной таблицы» выберите только что созданную таблицу с данными или ее диапазон. Для этого выделите нужную область.
В качестве данных для анализа можно указать внешний источник: установите переключатель в соответствующее поле и выберите нужное подключение из списка доступных.
4. Далее нужно будет указать, где размещать отчет сводной таблицы. Удобнее всего это делать на новом листе.
5. После подтверждения действия нажатием кнопки «ОК», будет создан и открыт макет отчета. Рассмотрим его.
В правой половине окна создается панель основных инструментов управления — «Список полей сводной таблицы». Все поля (заголовки столбцов в таблице исходных данных) будут перечислены в области «Выберите поля для добавления в отчет». Отметьте необходимые пункты и отчет сводной таблицы с выбранными полями будет создан.
Расположением полей можно управлять – делать их названиями строк или столбцов, перетаскивая в соответствующие окна, а так же и сортировать в удобном порядке. Можно фильтровать отдельные пункты, перетащив соответствующее поле в окно «Фильтр». В окно «Значение» помещается то поле, по которому производятся расчеты и подводятся итоги.
Другие опции для редактирования отчетов доступны из меню «Работа со сводными таблицами» на вкладках «Параметры» и «Конструктор». Почти каждый из инструментов этих вкладок имеет массу настроек и дополнительных функций.
Способ 2. Создание сводной таблицы с использованием инструмента «Мастер сводных таблиц и диаграмм»
Чтобы применить этот способ, придется сделать доступным инструмент, который по умолчанию на ленте не отображается. Откройте вкладку «Файл» — «Параметры» — «Панель быстрого доступа». В списке «Выбрать команды из» отметьте пункт «Команды на ленте». А ниже, из перечня команд, выберите «Мастер сводных таблиц и диаграмм». Нажмите кнопку «Добавить». Иконка мастера появится вверху, на панели быстрого доступа.
Мастер сводных таблиц в Excel 2010 совсем не многим отличается от аналогичного инструмента в Excel 2007. Для создания сводных таблиц с его помощью выполните следующее.
1. Кликните по иконке мастера в панели быстрого допуска. В диалоговом окне поставьте переключатель на нужный вам пункт списка источников данных:
2. После этого выбирается вид создаваемого отчета – «сводная таблица» или «сводная диаграмма (с таблицей)».
При выборе «Создать поля страницы» прежде всего придется указать диапазоны источников данных: выделите первый диапазон, нажмите «Добавить», потом следующий и т.д.
Для удобства диапазонам можно присваивать имена. Для этого выделите один из них в списке и укажите число создаваемых для него полей страницы, потом задайте каждому полю имя (метку). После этого выделите следующий диапазон и т.д.
После завершения нажмите кнопку «Далее», выберите месторасположение будущей сводной таблицы – на текущем листе или на другом, нажмите «Готово» и ваш отчет, собранный из нескольких источников, будет создан.