Создание отчетов при помощи сводных таблиц
Видео
Лирическое вступление или мотивация
Представьте себя в роли руководителя отдела продаж. У Вашей компании есть два склада, с которых вы отгружаете заказчикам, допустим, овощи-фрукты. Для учета проданного в 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), смысл тот же.
Перетаскивать поля в эти области можно в любой последовательности, риск промахнуться (в отличие от прошлых версий) - минимален.
P.S.
Единственный относительный недостаток сводных таблиц - отсутствие автоматического обновления (пересчета) при изменении данных в исходном списке. Для выполнения такого пересчета необходимо щелкнуть по сводной таблице правой кнопкой мыши и выбрать в контекстном меню команду Обновить (Refresh).
Ссылки по теме
- Настройка вычислений в сводных таблицах
- Группировка дат и чисел с нужным шагом в сводных таблицах
- Сводная таблица по нескольким диапазонам с разных листов
подскажите, пожалуйста, если необходимо сделать ссылку на итог сводной таблицы, каким образом это сделать? т.к. если изменить структуру, например, свернув под категорию, ссылка остается на пустое поле
=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Продажа";$A$3;"Регион";"Центр")которая (для примера) ссылается на итог по полю Продажа для региона Центр.
Такая ссылка не слетает при реструктуризации сводной и всегда будет выдавать правильное значение.
Заранее благодарен !
Сделал отчет с кучей сводных таблиц, во всех приходится менять фильтр по дате, число одно и тоже.
Можно вставить туда ссылку или имя переменной?
Можно как-то обойти эту проблему?
Нам очень нужно работать в нем одновременно со сводной таблицей...
Подскажите, пожалуйста, можно ли в сводной таблице вместо суммы вычислить разницу между двумя столбцами?
Буду благодарна за помощь.
Такая сводная не является полноценной и нормально с ней работать не получится
Подскажите, пожалуйста, мне в поле значения необходимо указать ID номер, т.е. это номер, который присвоен определенному товару, можно сделать так, чтобы в категории, которая выше не отображалась сумма этих ID?
Часто видел такую таблицу в прайс-листах. Теперь и сам буду делать
Можно ли сделать так, чтобы при размещении нескольких полей в одной области они отражались не иерархично, а параллельно? Допустим, чтобы в области строк параллельно шла следующая информация: клиент, регион, товар и т.п.
Подскажите, пож-та.
У меня есть нумерованный список данных. Как сформировать сводную таблицу, чтобы этот список был в нужном порядке? Имеется ввиду список 2.1., 2.1.1. и т.д.
У меня Excel 2003. Я создала сводную таблицу с 3 полями Фирма, Модель (2 модели) и Количество продаж в разрезе моделей.
Подскажите, как сделать так, чтобы Фирмы с одинаковыми наименованиями проссумировались? Спасибо.
Допустим я руководитель отдела продаж.
Собрал данные по продажам - основные данные "Клиент" "Номенклатура" " Месяц" Сумма происходит по полю количество.
И когда сморю полную табличку то все хорошо - продажи идут каждый месяц(столбики) по всей номенклатуре(строки).
А вот когда смотрю отдельного клиента то получаю данные только по тому что он купил и только в те месяца в которые он покупал.
А хотелось бы видеть картинку по всем месяцам и по всей номенклатуре. Чтобы увидеть дырки.
Если делать столбик в две группы то все позиции второго столбика попадают в каждую группу первого.
возможность установки галочки на Отображать пустые элементы отсутствует.
Если речь про обычную таблицу, то лучше использовать .
пользуюсь таблицами. возникла проблема с оформлением.
На вашем примере столбик итогов по месяцам выделен цветом.
на моем компьютере, встав на заголовок итогового столбца (или строки), мышь меняет форму и я могу выделить одновременно все подобные столбцы (или строки). например, чтобы тоже их покрасить.
на компьютере шефа (у нас обоих EXCEL 2010) такой возможности нет. где-то сбились настройки? что проверить?
так происходит и при построении новых таблиц, и при использовании одного и того же файла (по очереди) на этих двух компьютерах.
надежда только на Вас. очень долго искала возможность перенастроить...
Есть сводная таблица (строки=список артикулов, столбцы=даты, на пересечение сумма продаж). и обычная таблица которая содержит (строки=расширенный список артикулов, столбцы=даты), а на пересечение нужно подтянуть суммы продаж из сводной.
Можно ли это сделать функцией "получить.данные.сводной.таблицы", если да то как? Я думал что функция работает по принципу ВПР, но она подтягивает данные по порядку, а если я указываю в "поле данных" артикул из простой таблицы, то формула выдает #ССЫЛКА!
Помогите пожалуйста решить задачу.
Есть три столбца
/
Как сделать сводную таблицу такого формата
Не как не могу найти с помощью какого инструмента это можно решить.
Здесь скорее что-то похожее на нужно пробовать.
Ситуация: У меня две разные исходные таблицы. Они совпадают лишь по дате события и ФИО сотрудника. Поэтому я создал 2 pivot таблицы и с помощью Getpivotdata объединил их на новом листе в одну, куда подгружаются нужные мне из этих таблиц данные. Но мне нужно двойным щелчком в итоговой таблице просматривать результаты ссылающиеся на источник.
Подскажите, есть ли возможность получать значение ячейки, взятого из исходного Pivot с помощью GETPIVOTDATA, с сохранением возмости нажатия на этом значении двойного щелчка для открытия на отдельном листе, чтобы посмотреть что исходное значения?
Спасибо.
С помощью макроса - возможно.
Как вариант: в Excel 2013 можно сделать сводную сразу по обеим вашим таблицам (при построении ставим флажок про Data Model). Тогда и двойной щелчок работать будет и GETPIVOTDATA не нужна.
Ситуация следующая: есть сводная таблица по планам и фактам продаж. Отдельно идут факты по отделам, отдельно планы. Необходимо посчитать процент выполнения плана отдельно по сотрудникам и по отделам в целом. То есть разделить факт на план. Можно ли это сделать при помощи сводной таблицы, или все-таки просто написать формулу?
Я только начал разбираться с таблицами и сразу возникли проблемы.
У меня есть большая исходная таблица с данными с которой нужно вывести на печать данные. Пр. данные по учету животных: столбцы - № учета, дата, район, область, вид, численность, биотоп. Как сделать, что бы при фильтрации в сводной таблице высвечивались др. показатели.
Заранее огромное СПАСИБО
Спасибо!
Как и все Ваши уроки очень полезны, за что огромная благодарность. Пользуюсь надстройкой Plex начиная с версии 2.0. Очень полезная и точная надстройка. E-book тоже приобрел сразу же как появилась на сайте
Есть одна задача которую не могу решит. Задача: Ест две базы из которых создал 3 сводные таблицы (с одной базы одну, а с другой две). Каждые из них по дате фильтруются. Две сводные таблицы из разных баз всегда должны быт в одинаковой дате. А другая на один рабочий день раньше. Хочу что если поменял дату на одной сводной (в том которая не связанна с другой базой) то следующие сводные таблицы (которые связаны с одинаковой базой) автоматически настраивались сами (один на ту же дату, а другой на рабочий день назад).
Попробовал Timeline, по отдельности получается. Если сказанная задача будет управляться с Timeline, то вообще было бы супер. В Timeline есть месяц, квартал и т. д.
Заранее за все СПАСИБО.
Если выделить Timeline, то на вкладке Параметры будет кнопка Подключения к отчетам - она позволит выбрать какие сводные таблицы должны фильтроваться и, таким образом, можно будет выбрать и фильтровать обе сразу.
Вопрос по Вашему примеру.
Если на листе "заказы" добавить нового Заказчика или новый месяц, то в сводной таблице эта информация не обновляется и на экране не появляется, почему?
Во-вторых, надо обновить сводную (правой кнопкой мыши по сводной - Обновить).