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

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

Если у Вас версия Excel не года, то будьте готовы, что снимки экранов в данной статье будут отличаться от того, что Вы увидите на своём экране. На заре развития программ для создания электронных таблиц балом правил Lotus Его превосходство было настолько полным, что усилия Microsoft, направленные на разработку собственного программного обеспечения Excelкак альтернативы Lotus, казались пустой тратой времени.

А теперь перенесёмся в год! Excel доминирует среди электронных таблиц более, чем Lotus кода-либо за всю свою историю, а число людей, которые до сих пор используют Lotus, стремится к нулю. Как это могло произойти? Что послужило причиной для такого драматического разворота событий? Сводные таблицы вместе с недооценкой успеха Windows в целом, сыграли похоронный марш для Lotus и положили начало успеху Microsoft Excel.

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

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

Сводные таблицы в MS Excel

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

Это должны быть необработанные исходные данные, вроде какого-то списка. Например, это может быть список совершённых продаж в компании за последние шесть месяцев. Обратите внимание, что это не сырые исходные данные, поскольку для них уже подведены итоги. Где же тогда исходные данные? Где исходный список продаж, из которого этот итог за месяц был получен?

Работа со сводными таблицами в Microsoft Excel - zdorovofrolovo.ru

Ясно, что кто-то совершил огромный труд по упорядочиванию и сортировке всех данных о продажах за последние шесть месяцев и превратил их в таблицу итогов, которую мы видим. Сколько, по-вашему, это заняло времени? Дело в том, что таблица, приведённая выше, это не сводная таблица. Она была создана вручную из исходных данных, сохранённых где-то ещё, их обработка заняла минимум пару часов. Именно такую таблицу итогов можно создать, используя сводные таблицы, потратив на это всего лишь несколько секунд.

Возможно, Вас удивит, что из этого списка торговых операций с помощью сводных таблиц и всего за несколько секунд, мы можем создать в Excel помесячный отчёт о продажах, что мы разбирали выше. Да, мы сможем сделать это и еще многое другое! Для начала убедитесь, что у Вас есть какие-то исходные данные на листе Excel. Перечень финансовых операций — самое типичное, что встречается. На самом деле, это может быть перечень чего угодно: Затем на вкладке Insert Вставка выберите команду PivotTable Сводная таблица:.

Появится диалоговое окно Create PivotTable Создание сводной таблицы с двумя вопросами для Вас:. Так как на предыдущем шаге мы уже выбрали одну из ячеек списка, то для создания сводной таблицы будет выделен весь список автоматически. Заметьте, что мы можем выбрать другой диапазон, другую таблицу и даже какой-нибудь внешний источник данных, например, таблицу базы данных Access или MS-SQL. К тому же нам необходимо выбрать, где разместить новую сводную таблицу: В данном примере мы выберем вариант — New Worksheet На новый лист:.

Как только мы кликнем по любой ячейке в сводной таблице, появится ещё одно диалоговое окно: PivotTable Field List Поля сводной таблицы. Список полей в верхней части диалогового окна — это перечень всех заголовков из исходного списка. Четыре пустые области в нижней части экрана позволяют указать сводной таблице, как требуется обобщить данные. Пока эти области пусты, в таблице тоже ничего.

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

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

Это, почти всегда, численные значения. Отличный кандидат на место в этой области — данные под заголовком Amount Стоимость нашей исходной таблицы. Перетащим этот заголовок в область Values Значения:. Обратите внимание, что заголовок Amount теперь отмечен галочкой, а в области Values Значения появилась запись Sum of Amount Сумма по полю Amountуказывающая на то, что столбец Amount просуммирован. Если мы посмотрим на саму сводную таблицу, то увидим сумму всех значений из столбца Amount исходной таблицы.

Итак, наша первая сводная таблица создана! Удобно, но не особо впечатляет. Вероятно, мы хотим получить больше информации о наших данных, чем есть. Обратимся к исходным данным и попробуем определить один или несколько столбцов, которые можно использовать, чтобы раздробить эту сумму.

Например, мы можем сформировать нашу сводную таблицу таким образом, чтобы итоговая сумма продаж подсчитывалась для каждого продавца по отдельности. Чтобы достичь такого результата, достаточно перетащить заголовок Salesperson Торговый представитель в область Row Labels Строки:. За пару кликов мы создали таблицу, которую вручную пришлось бы создавать очень долго. Что ещё мы можем сделать? Ну, в определённом смысле, наша сводная таблица уже готова. Мы создали полезную сводку по исходным данным.

Уже получена важная информация! В оставшейся части статьи мы разберём некоторые способы создания более сложных сводных таблиц, а также узнаем, как их настраивать. Во-первых, мы можем создать двумерную сводную таблицу. Сделаем это, используя заголовок столбца Payment Method Способ оплаты. Просто перетащите заголовок Payment Method в область Column Labels Колонны:. Перетащите заголовок Package Комплекс в область Report Filter Фильтры:. Это даёт нам возможность отфильтровать отчёт по признаку "Какой комплекс отдыха был оплачен".

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

Для этого рядом с Column Labels нажмите стрелку вниз и в выпадающем меню снимите галочку с пункта Cash:. Давайте посмотрим, на что теперь похожа наша сводная таблица. Как видите, столбец Cash исчез из. Очевидно, что сводные таблицы — это очень мощный инструмент, но до сих пор результаты выглядят как-то незамысловато и скучно.

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

Во-первых, найдём запись Sum of Amount в области Values Значения и кликнем по. В появившемся меню выберем пункт Value Field Settings Параметры полей значений:. Нажмите кнопку Number Format Числовой форматоткроется диалоговое окно Format Cells Формат ячеек:. Из списка Category Числовые форматы выберите Accounting Финансовый и число десятичных знаков установите равным нулю.

Теперь несколько раз нажмите ОКчтобы вернуться назад к нашей сводной таблице. Раз уж мы занялись форматированием, давайте настроим формат для всей сводной таблицы. Есть несколько способов сделать. Используем тот, что попроще…. Откройте вкладку PivotTable Tools: Design Работа со сводными таблицами: Далее разверните меню нажатием на стрелочку в нижнем правом углу раздела PivotTable Styles Стили сводной таблицычтобы увидеть обширную коллекцию встроенных стилей:.

Иногда приходится фильтровать данные по датам. Например, в нашем списке торговых операций присутствует много-много дат. Excel предоставляет инструмент для группировки данных по дням, месяцам, годам и т.

Давайте посмотрим, как это делается. Для начала уберите запись Payment Method из области Column Labels Колонны.


Коментарии:

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





2016-2017 zdorovofrolovo.ru