Как сделать пивот таблицу в excel?

Microsoft Excel

трюки • приёмы • решения

Как использовать сводную таблицу Excel для анализа состояния проекта

Сводная таблица позволяет управлять крупным массивом данных, которые введены в ячейках рабочего листа. Благодаря сводным таблицам вы можете быстро анализировать данные под разными углами зрения и легко изменять их вид.

Это довольно компактная совокупность данных, но вообразите, как бы выглядел подобный список, если в нем представить пару десятков исполнителей проекта и несколько сотен рисков! Было бы весьма непросто даже прочитать такой список, не говоря уже о возможности проанализировать его и сделать на этом основании какие-то выводы.

Рис. 1. Реестр рисков проекта Grant St. Move

Попытаемся с помощью Excel преобразовать такой список в таблицу с обозначениями заголовков строк, которые мы могли бы фильтровать. (Не забывайте, что в вашей таблице не должно быть пустых строк или пустых столбцов, разделяющих данные.) Вы, наверное, помните из других наших материалов, что вам нужно просто выделить диапазон ячеек, которые требуется включить в таблицу, а затем выбрать стиль таблицы. Все остальное сделает за вас Excel.

Рис. 2. Результат преобразования в таблицу выделенного диапазона ячеек

Вместо того чтобы начинать с ввода данных в электронную таблицу (без применения табличного форматирования), вы можете сначала применить к диапазону ячеек требуемый табличный формат, а затем заняться вводом.

Теперь, когда данные представлены в табличном формате, вы можете создать сводную таблицу, не забудьте купить клавиатуру для компьютера. Для начала установите табличный курсор в любой ячейке этой таблицы, затем щелкните на значке PivotTable (Сводная таблица) вкладки Insert (Вставка) и в появившемся меню выберите команду PivotTable (Сводная таблица).

Рис. 3. Значок PivotTable расположен в крайней части вкладки Insert (Вставка)

На экране появится диалоговое окно Create Pivot Table (Создание сводной таблицы), показанное на рис. 4. В этом диалоговом окне следует указать программе, на основе каких данных будет построена сводная таблица — рабочего листа текущей рабочей книги или внешних данных (например, SQL Server). Обратите внимание, что в поле Table/Range (Таблица или диапазон) мы оставили заданное по умолчанию значение — Table1 (Таблица1).

Рис. 4. Диалоговое окно Create Pivot Table (Создание сводной таблицы)

Щелкните на кнопке ОК. Программа немедленно создаст макет сводной таблицы па новом рабочем листе (рис. 5), в правой части которого расположена панель Pivot Table Field List (Список полей сводной таблицы). Обратите внимание, что в верхней части этой панели перечислены названия всех полей созданной нами таблицы реестра рисков.

Рис. 5. Рабочий лист, на котором расположен макет сводной таблицы и панель Pivot Table Field List (Список полей сводной таблицы)

Названия полей, перечисленные на панели Pivot Table Field List, представляют собой названия заголовков столбцов, взятые из нашей таблицы. Области макета сводной таблицы предназначены для различного отображения данных. Их можно представлять как некую трехаспектную палитру. Допустим, нам требуется узнать количество рисков по каждой категории. Например, сколько внешних рисков у нашего проекта? Начните с перетаскивания поля Risk Category (Категория риска), как показано на рис. 6, в область Drop Row Fields Here (Перетащите сюда поля строк). (Местоположение этой области показано на рис. 5).

Рис. 6. Результат перетаскивания поля Risk Category (Категория риска) в область Drop Row Fields Here (Перетащите сюда поля строк)

Как видите, название поля Risk Category (Категория риска) появилось в области Row Labels (Названия строк), которая расположена в нижней части панели Pivot Table Field List (Список полей сводной таблицы). Теперь перетащите поле Risk Name (Название риска) в область макета сводной таблицы Drop Data Items Here (Перетащите сюда элементы данных), как показано на рис. 7.

Рис. 7. Результат перетаскивания поля Risk Name (Название риска) в область Drop Data /terns Here (Перетащите сюда элементы данных)

Обратите внимание, что в нижней части созданной нами сводной таблицы программа Excel автоматически добавила строку с заголовком Grand Total (Общий итог), в которой отображено общее количество названий рисков по отдельным категориям. Теперь нетрудно заметить, что, например, категория «Связанные с решением кадровых вопросов» (поле Organizational) содержит два риска, категория «Технические» (поле Technical) — четыре и т.д. В последней строке — Grand Total (Общий итог) — указано общее количество рисков (11) по всем категориям.

Как вы, должно быть, заметили, в области Values (Значения), расположенной в нижней части панели Pivot Table Field List (Список полей сводной таблицы), появился элемент Count of Risk Name (Количество по полю Risk Name), т.е. суммарное количество рисков (см. рис. 7). Существует множество способов отображения, представления и подсчета данных в сводных таблицах Excel. Если хотите увидеть результаты и подсчеты, которые сделает для вас Excel, поэкспериментируйте с перемещением полей из списка панели Pivot Table Field List (Список полей сводной таблицы) в разные области макета сводной таблицы.

Создание первой сводной таблицы

Исходные данные

Сейчас на простом примере мы научимся создавать сводные таблицы и познакомимся с некоторыми их возможностями.

Для примера в качестве исходных данных возьмем ведомость основных средств компании и определим стоимость позиций по каждому филиалу с помощью сводной таблицы.

Перед созданием сводной таблицы убедитесь, что в исходной таблице отсутствуют пустые заголовки. Это требуется потому что каждый столбец таблицы становиться полем сводной таблицы, по которому можно собрать данные.

Советую также преобразовывать исходный диапазон данных в таблицу (ГлавнаяФорматировать как таблицу). Тогда при добавлении или удалении строк и столбцов не придется менять ссылку на этот диапазон в сводном отчете.

Рекомендуемые сводные таблицы

Пользователи Excel 2013 могут выбрать сводную таблицу из макета на основе рекомендаций, которые предлагает Excel. Вот как это делается:

Выберите любую ячейку исходной таблицы.

Перейдите на вкладку ленты Вставка [Insert] и найдите группу Таблицы [Table].

Читать еще:  Как сделать секундомер в excel?

Выберите команду Рекомендуемые сводные таблицы [Recommended PivotTables].

Выберите подходящую таблицу в списке слева. После выборы в окне справа вы увидите макет будущей таблицы.

Самостоятельное создание сводной таблицы с помощью конструктора

Для тех, у кого нет Excel 2013, или же если нужной сводной таблицы нет в предложенных, можно создать ее с нуля с помощью конструктора. Для этого:

Выберите любую ячейку исходной таблицы.

Перейдите на вкладку ленты Вставка [Insert] и найдите группу Таблицы [Table].

Выберите команду Сводная таблица [PivotTable].

В диалоговом окне Создание сводной таблицы [Create PivotTable] убедитесь в правильности диапазона данных, на основе которого будет строиться отчет. Если диапазон некорректный, его нужно поменять в поле Таблица и диапазон [Table/Range].

Важно! При выделении диапазона убедитесь, что выбрана таблица с заголовками, а так же что выбранный диапазон не включает строку итогов.

Так же укажите, где необходимо разместить отчет сводной таблицы: на новом листе или на уже существующий.

Советую размещать каждый отчет сводной таблицы на отдельном листе. Это поможет избежать ошибок в случае если будут добавлять данные.

После формирования сводной таблицы в выбранном месте появиться область с ее названием. По умолчанию отчет называется СводнаяТаблица1 [PivotTable 1]. Для начала работы необходимо щелкнуть левой клавшей мыши по данной области. В результате в правой части листа откроется макет сводной таблицы.

Макет сводной таблицы состоит из списка полей сводной таблицы, в котором перечислены все заголовки исходной таблицы и четырех областей: ФИЛЬТРЫ, КОЛОННЫ, СТРОКИ и ЗНАЧЕНИЯ.

В зависимости от того в какую область мы перенесем то или иное поле будет зависеть вид будущей сводной таблицы.

В качестве примера давайте создадим сводный отчет, в котором посчитаем рыночную стоимость для каждой группы из исходной таблицы. Т.е. мы хотим, чтобы напротив группы стали строками сводного отчета. Значит нужно перетащить поле Группа в область строк.

Данную операцию можно сделать еще 2 способами:

  • отметить флажок напротив поля Группа;
  • щелкнуть по полу Группа правой кнопкой мыши и выбрать Добавить в названия строк [Add to Row Labels].

После добавления поля вы увидите список всех групп, которые есть в исходной таблице:

Теперь осталось добавить сумму по полю Рыночная стоимость. Для этого перенесем поле Рыночная стоимость в область значений. Желаемая таблица получена.

Создание сводной таблицы для анализа данных листа

В этом курсе:

Сводная таблица — это эффективный инструмент для вычисления, сведения и анализа данных, который упрощает поиск сравнений, закономерностей и тенденций.

Создание сводной таблицы

Выделите ячейки, на основе которых вы хотите создать сводную таблицу.

Примечание: Ваши данные не должны содержать пустых строк или столбцов. Они должны иметь только однострочный заголовок.

На вкладке Вставка нажмите кнопку Сводная таблица.

В разделе Выберите данные для анализа установите переключатель Выбрать таблицу или диапазон.

В поле Таблица или диапазон проверьте диапазон ячеек.

В разделе Укажите, куда следует поместить отчет сводной таблицы установите переключатель На новый лист, чтобы поместить сводную таблицу на новый лист. Можно также выбрать вариант На существующий лист, а затем указать место для отображения сводной таблицы.

Нажмите кнопку ОК.

Настройка сводной таблицы

Чтобы добавить поле в сводную таблицу, установите флажок рядом с именем поля в области Поля сводной таблицы.

Примечание: Выбранные поля будут добавлены в области по умолчанию: нечисловые поля — в область строк, иерархии значений дат и времени — в область столбцов, а числовые поля — в область значений.

Чтобы переместить поле из одной области в другую, перетащите его в целевую область.

Данные должны быть представлены в виде таблицы, в которой нет пустых строк или столбцов. Рекомендуется использовать таблицу Excel, как в примере выше.

Таблицы — это отличный источник данных для сводных таблиц, так как строки, добавляемые в таблицу, автоматически включаются в сводную таблицу при обновлении данных, а все новые столбцы добавляются в список Поля сводной таблицы. В противном случае необходимо либо изменить исходные данные для сводной таблицы, либо использовать динамический диапазон с именованной формулой.

Все данные в столбце должны иметь один и тот же тип. Например, не следует вводить даты и текст в одном столбце.

Сводные таблицы применяются к моментальному снимку данных, который называется кэшем, а фактические данные не изменяются.

Создание сводной таблицы

Если у вас недостаточно опыта работы со сводными таблицами или вы не знаете, с чего начать, лучше воспользоваться рекомендуемой сводной таблицей. При этом Excel определяет подходящий макет, сопоставляя данные с наиболее подходящими областями в сводной таблице. Это позволяет получить отправную точку для дальнейших экспериментов. После создания рекомендуемой сводной таблицы вы можете изучить различные ориентации и изменить порядок полей для получения нужных результатов.

Вы также можете скачать интерактивный учебник Создание первой сводной таблицы.

Рекомендуемые сводные таблицы

Создание сводной таблицы вручную

Щелкните ячейку в диапазоне исходных данных и таблицы.

На вкладке Вставка нажмите кнопку Рекомендуемые сводные таблицы.

«Рекомендуемые сводные таблицы» для автоматического создания сводной таблицы» />

Excel проанализирует данные и предоставит несколько вариантов, как в этом примере:

Выберите сводную таблицу, которая вам понравилась, и нажмите кнопку ОК. Excel создаст сводную таблицу на новом листе и выведет список Поля сводной таблицы.

Щелкните ячейку в диапазоне исходных данных и таблицы.

На вкладке Вставка нажмите кнопку Сводная таблица.

Если вы используете Excel для Mac 2011 или более ранней версии, кнопка «Сводная таблица» находится на вкладке Данные в группе Анализ.

Появится диалоговое окно Создание сводной таблицы, в котором указан ваш диапазон или имя таблицы. В этом случае мы используем таблицу «таблица_СемейныеРасходы».

В разделе Укажите, куда следует поместить отчет сводной таблицы установите переключатель На новый лист или На существующий лист. При выборе варианта На существующий лист вам потребуется указать ячейку для вставки сводной таблицы.

Нажмите кнопку ОК. Excel создаст пустую сводную таблицу и выведет список Поля сводной таблицы.

В верхней части области имя поля установите флажок для поля, которое вы хотите добавить в сводную таблицу. По умолчанию нечисловые поля добавляются в область строк , поля даты и времени добавляются в область столбцов , а числовые поля добавляются в область значений . Вы также можете вручную перетащить любой доступный элемент в любое поле сводной таблицы или если вы больше не хотите использовать элемент в сводной таблице, просто перетащите его из списка полей или снимите флажок. Возможность изменить порядок элементов полей — это одна из функций сводной таблицы, которая делает ее более удобной для быстрого изменения внешнего вида.

Читать еще:  Как сделать сетку в excel видимой на бумаге?

Список полей сводной таблицы

Соответствующие поля в сводной таблице

По умолчанию поля сводной таблицы, помещаемые в область значений , будут отображаться в виде суммы. Если приложение Excel интерпретирует данные как текст, оно будет отображаться как количество. Поэтому важно убедиться, что вы не намерены смешивать типы данных для полей значений. Чтобы изменить вычисление по умолчанию, сначала щелкните стрелку справа от имени поля и выберите параметр Параметры поля .

Затем измените функцию в разделе Суммировать по. Обратите внимание на то, что при изменении метода вычисления Excel автоматически добавляет его название в раздел Пользовательское имя (например, «Сумма по полю имя_поля»), но вы можете изменить это имя. Чтобы изменить числовой формат для всего поля, нажмите кнопку Число. .

Совет: Так как при изменении способа вычисления в разделе Суммировать по обновляется имя поля сводной таблицы, не рекомендуется переименовывать поля сводной таблицы до завершения ее настройки. Вместо того чтобы вручную изменять имена, можно выбрать пункт Найти (в меню «Изменить»), в поле Найти ввести Сумма по полю, а поле Заменить оставить пустым.

Значения также можно выводить в процентах от значения поля. В приведенном ниже примере мы изменили сумму расходов на % от общей суммы.

Вы можете настроить такие параметры в диалоговом окне Параметры поля на вкладке Дополнительные вычисления.

Отображение значения как результата вычисления и как процента

Просто перетащите элемент в раздел Значения дважды, щелкните значение правой кнопкой мыши и выберите команду Параметры поля, а затем настройте параметры Суммировать по и Дополнительные вычисления для каждой из копий.

При добавлении новых данных в источник необходимо обновить все основанные на нем сводные таблицы. Чтобы обновить одну сводную таблицу, можно щелкнуть правой кнопкой мыши в любом месте ее диапазона и выбрать команду Обновить. При наличии нескольких сводных таблиц сначала выберите любую ячейку в любой сводной таблице, а затем на ленте откройте вкладку Анализ сводной таблицы, щелкните стрелку под кнопкой Обновить и выберите команду Обновить все.

Если вы создали сводную таблицу и решили, что она больше не нужна, можно просто выделить весь диапазон сводной таблицы, а затем нажать клавишу Delete. Это не повлияет на другие данные, сводные таблицы и диаграммы вокруг него. Если сводная таблица находится на отдельном листе, где больше нет нужных данных, вы можете просто удалить этот лист. Так проще всего избавиться от сводной таблицы.

Теперь вы можете вставить сводную таблицу в электронную таблицу в Excel в Интернете.

Важно: Не рекомендуется создавать сводные таблицы и работать над ними в электронной таблице одновременно с другими пользователями.

Выделите таблицу или диапазон в электронной таблице.

На вкладке Вставка нажмите кнопку Сводная таблица.

В Excel появится диалоговое окно Создание сводной таблицы, в котором будет указан ваш диапазон или имя таблицы.

В разделе Укажите, куда следует поместить отчет сводной таблицы установите переключатель На новый лист или На существующий лист. При выборе варианта На существующий лист вам потребуется указать ячейку для вставки сводной таблицы.

Примечание: Указанная ячейка должна находиться за пределами таблицы или диапазона.

Нажмите кнопку ОК. Excel создаст пустую сводную таблицу и выведет список Поля сводной таблицы.

В верхней части области поля сводной таблицы установите флажок для поля, которое вы хотите добавить в сводную таблицу. По умолчанию нечисловые поля добавляются в область » строки «, поля даты и времени добавляются в область столбцов , а числовые поля — в область » значения «. Вы также можете вручную перетащить любой доступный элемент в любое поле сводной таблицы или если вы больше не хотите использовать элемент в сводной таблице, просто перетащите его из списка полей или снимите флажок. Возможность изменить порядок элементов полей — это одна из функций сводной таблицы, которая делает ее более удобной для быстрого изменения внешнего вида.

Список полей сводной таблицы

Соответствующие поля в сводной таблице

По умолчанию поля сводной таблицы, помещаемые в область значений , будут отображаться в виде суммы. Если приложение Excel интерпретирует данные как текст, оно будет отображаться как количество. Поэтому важно убедиться, что вы не намерены смешивать типы данных для полей значений. Вы можете изменить вычисление по умолчанию, щелкнув стрелку справа от имени поля, а затем выбрав параметр Параметры поля значения .

Затем измените функцию в разделе Операция. Обратите внимание на то, что при изменении метода вычисления Excel автоматически добавляет его название в раздел Пользовательское имя (например, «Сумма по полю имя_поля»), но вы можете изменить имя. Чтобы изменить числовой формат для всего поля, нажмите кнопку Числовой формат.

Совет: Так как при изменении способа вычисления в разделе Операция обновляется имя поля сводной таблицы, не рекомендуется переименовывать поля сводной таблицы до завершения ее настройки. Вместо того чтобы вручную изменять имена, можно нажать кнопку Найти и заменить (CTRL+H), в поле Найти ввести Сумма по полю, а поле Заменить оставить пустым.

Значения можно также выводить в процентах от значения поля. В приведенном ниже примере мы изменили сумму расходов на % от общей суммы.

Вы можете настроить такие параметры в диалоговом окне Параметры поля значений на вкладке Дополнительные вычисления.

диалоговое окно «Дополнительные вычисления»» />

Отображение значения как результата вычисления и как процента

Просто перетащите элемент в раздел Значения дважды, а затем настройте параметры Операция и Дополнительные вычисления для каждой из копий.

При добавлении новых данных в источник необходимо обновить все основанные на нем сводные таблицы. Чтобы обновить сводную таблицу, можно щелкнуть правой кнопкой мыши в любом месте ее диапазона и выбрать команду Обновить

Читать еще:  Как сделать таблицу табель учета рабочего времени в excel?

Если вы создали сводную таблицу и решили, что она больше не нужна, можно просто выделить весь диапазон сводной таблицы, а затем нажать клавишу DELETE. Это не повлияет на другие данные, сводные таблицы и диаграммы вокруг него. Если сводная таблица находится на отдельном листе, где больше нет нужных данных, вы можете просто удалить этот лист. Так проще всего избавиться от сводной таблицы.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Создание отчетов при помощи сводных таблиц

Лирическое вступление или мотивация

Представьте себя в роли руководителя отдела продаж. У Вашей компании есть два склада, с которых вы отгружаете заказчикам, допустим, овощи-фрукты. Для учета проданного в 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-х шагового Мастера из прошлых версий отобразится одно компактное окно с теми же настройками:

В нем, также как и ранее, нужно выбрать источник данных и место вывода сводной таблицы, нажать ОК и перейти к редактированию макета. Теперь это делать значительно проще, т.к. можно переносить поля не на лист, а в нижнюю часть окна Список полей сводной таблицы, где представлены области:

  • Названия строк (Row labels)
  • Названия столбцов (Column labels)
  • Значения (Values) — раньше это была область элементов данных — тут происходят вычисления.
  • Фильтр отчета (Report Filter) — раньше она называлась Страницы (Pages) , смысл тот же.

Перетаскивать поля в эти области можно в любой последовательности, риск промахнуться (в отличие от прошлых версий) — минимален.

Единственный относительный недостаток сводных таблиц — отсутствие автоматического обновления (пересчета) при изменении данных в исходном списке. Для выполнения такого пересчета необходимо щелкнуть по сводной таблице правой кнопкой мыши и выбрать в контекстном меню команду Обновить (Refresh) .

Ссылка на основную публикацию
Adblock
detector