Сводные таблицы в 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-х шагового Мастера из прошлых версий отобразится одно компактное окно с теми же настройками:

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

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

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

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

Источник: www.planetaexcel.ru

Обучение сводным таблицам Excel на примерах

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

Работа со сводными таблицами в Excel

Создайте таблицу исходных данных о клиентах фирмы, так как показано на рисунке:

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

Читайте также:  Vba excel самоучитель для чайников

Решение для построения сводного отчета в Excel:

  1. Перейдите на любую ячейку исходной клиентской базы и выберите инструмент: «Вставка»-«Таблицы»-«Сводная таблица».
  2. В появившемся диалоговом окне переключите на опцию «На существующий лист» и в поле «Диапазон» укажите значение H3:
  3. В окне настроек (справа) «Список полей сводной таблицы» переместите значение «Пол» из поля «Выберите поля для добавления в отчет» в поле «Фильтр отчета»:
  4. Таким же образом распределите остальные значения по полям так как указано выше на рисунке.
  5. Щелкните правой кнопкой мышки по любой ячейке внутри сводной таблицы. Из появившегося контекстного меню выберите опцию: «Итоги по»-«Среднее».
  6. Округлите все значения до десятых с помощью формата ячеек.

Обратите внимание! В ячейке I1 мы имеем возможность указать пол (мужской, женский или оба) для сегментирования отчета.

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

Пример настройки сводных отчетов

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

  1. Если вы случайно закрыли окно «Список полей сводной таблицы», тогда щелкните левой кнопкой мышки по сводной таблице, чтобы сделать ее активной. Затем включите опцию: «Работа со сводными таблицами»-«Параметры»-«Показать»-«Список полей».
  2. Переместите значение «Пол» в поле «Название строк» под значение «Образование».

Структура отчета кардинально изменена:

Появились группировки, которые управляются кнопками (+ и -). Эти вспомогательные кнопки и заголовки можно включить/отключить через меню: Работа со сводными таблицами»-«Параметры»-«Показать»-«Кнопки» или «Заголовки полей». Так же у нас больше нет фильтра для сегментирования отчета по полу в ячейке I1.

Источник: exceltable.com

Как использовать сводные таблицы Excel в КДП

О чем идет речь

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

Общее количество проданного товара увидим, если с помощью базы данных построить сводную таблицу в Excel.

Узнаем общую сумму продаж по каждой категории.

Проверим остатки по каждой категории товара и так далее.

Как использовать

Сводные таблицы в Excel для чайников представляются чем-то очень сложным и непонятным. На самом же деле не все так страшно. Перед тем как сделать сводную таблицу в Excel, необходимо «раздобыть» для нее исходные данные. Получают их как автоматически, выгрузив необходимую информацию из 1С или другой программы, например, системы ЭДО, так и в ручном режиме, создав документ со всеми необходимыми данными. Идеальный вариант, если сам учет деятельности ведется в Эксель, тогда никаких дополнительных действий совершать не придется. Главное — проверить, что исходный массив соответствует следующим требованиям:

  • в нем нет объединенных ячеек;
  • нет пустых строк и столбцов;
  • все столбцы имеют заголовки.

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

Создаем базу Excel с помощью функции «Вставка» — «Таблица» — «Сводная таблица».

Получим следующий результат:

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

А теперь добавим типы продаж.

Как сделать вычисления

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

Добавлять и убирать столбцы можно, перетаскивая поля в соответствующую область.

Если данные в исходном массиве изменились, базу необходимо обновить. Добавим менеджера Самуйлову в исходные данные, поставим курсор в любую ячейку базы и обновим результат сведений с помощью вкладки «Анализ» — «Обновить данные».

Чтобы настроить автоматическое обновление данных при открытии файла, необходимо установить галочку в соответствующем месте (вкладка «Анализ» — «Параметры» — «Данные»).

Удаляем базу, выделив ее и нажав клавишу Delete.

Где применять

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

Расчет KPI

Современные CRM-системы позволяют выгрузить все необходимые отчеты в готовом виде. Но что делать тем, кто специализированный софт не использует? Остается возможность как в Экселе сделать сводную таблицу, так и посчитать необходимые показатели в ручном режиме. Второй способ кажется проще, но он не всегда удобен. Если исходные данные представлены в виде списка подобного вида, использовать объединенные реестры вполне уместно, так как это значительно облегчает последующую работу.

Мы уже посчитали объем продаж для каждого менеджера с помощью сводной базы.

Теперь эти данные используем для дальнейшего расчета. Сравним плановый показатель с фактическим и вычислим отклонение.

Тем менеджерам, которые превысили плановое значение (отклонение положительно), положена премия, рассчитываемая в процентах от выручки, превышающей плановое значение. Рассчитаем премию.

Отчет по персоналу

Практически все данные о персонале получаем из 1С. Но если такой софт в организации не используется или необходим отчет в другой форме, не остается ничего, кроме как делать сводные таблицы в Еxcel. Даже если массив данных составляется в ручном режиме, базы помогут представить их в более «красивом» виде. Имея сведения об образовании, стаже, окладе сотрудников в виде подобного списка, есть возможность, допустим, выяснить, сколько сотрудников каждого из отделов имеют образование определенного уровня.

Читайте также:  Экспорт excel в pdf

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

На основе таких отчетов удобно строить диаграммы для графического отображения информации.

Источник: clubtk.ru

Excel для финансиста

Поиск на сайте

Глава 7. Сводные таблицы

Cводные таблицы – мощный и очень удобный инструмент Excel для анализа больших объёмов данных. С помощью таблиц можно легко получать сводные отчёты, видоизменяя и настраивая их несколькими щелчками мыши. Рассмотрим сразу на практическом примере.

Скачайте файл svodnie-tablici. На листе данные этого файла находятся двести записей о продажах товаров (на практике число анализируемых записей обычно на один-два порядка больше). Каждая запись представляет собой строчку в таблице и содержит информацию:

  • Дата совершения продажи;
  • Наименование товара;
  • Наименование покупателя товара;
  • Сумма сделки.

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

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

На все эти вопросы помогают ответить сводные таблицы.

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

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

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

Поля сводной таблицы

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

Напомним, сводная таблица должна давать ответы на поставленные вопросы. Например, ответим на три первых вопроса: о сумме продаж, о самом активном покупателе и самом популярном товаре. Для этого нужно отметить в окне справа поля Наименование товара, Покупатель, Сумма. Программа разместит поле Сумма в окошко Суммарные значения (в самом низу справа), а остальные два поля – в окошко Названия строк. Перетащите одно из полей в окошко Названия столбцов. Получится примерно так:

Всего несколько кликов мышкой, и первая сводная таблица в Excel готова! Программа уже посчитала суммы продаж в двух разрезах: по покупателям и товарам, и вывела общий итог. Таким образом программа берёт и структурирует данные. Можно немного доработать сводную таблицу. Выделите финансовые данные таблицы (диапазон B5:E9), задайте этим ячейкам финансовый формат, суммы стали нагляднее. Выделите ячейку Е5 (общий итог – покупатель Автоматика), нажмите меню Параметры, в разделе Сортировка – большую кнопку Сортировка, в открывшемся окне – Параметры сортировкиПо убыванию, ОК. Теперь и производители, и товары отсортированы по убыванию, ответы на первые три вопроса получены.

Как правило, данные анализируются за определённый период или несколько периодов. Структурируем данные по временным периодам. Например, можно узнать динамику продаж продуктов по годам. Для этого нужно отметить поле Год и перенести это поле в окошко Названия строк, убрав оттуда поле Покупатель обратно в окно списка полей. Получится наглядная таблица, данные которой удобно использовать для построения графика.

Одно окошко было пока обойдено вниманием: Фильтр отчёта. Перенесите туда поле Покупатель. В ячейках А1-А2 появился фильтр выбора значений этого поля, это полезно для более детального анализа. Добавив простую диаграмму-график на основе данных сводной таблицы, получаем хороший аналитический инструмент: выбирая покупателя, можно смотреть динамику продаж по каждому товару.

Скачать пример сводных таблиц Excel: svodnie-tablici

Источник: finexcel.ru

Сводные таблицы в excel примеры самоучитель

Проблемы с отображением видео:

Генератор примеров (массивы, таблицы, отчеты).

Когда я начинал читать тренинги по MS Excel то “стер” пальцы бесконечно создавая примеры для той или иной темы. Особенно это касалось темы “Сводные таблицы”. Решил я эту проблему просто – создал надстройку, которая мне эти примеры генерировала. Когда люди увидели это “чудо” на очередном тренинге они очень сильно “возбудились”. Оказалось, что это не только отличный инструмент для тренера, но и такой же отличный инструмент для “студента”.

Файлы для скачивания:

Файл Описание Размер файла: Скачивания
Генератор таблиц (v. 14.09) 38 Кб 1940

Как быстро построить сводную таблицу из отчета 1C или SAP?

Суть проблемы, я думаю, ясна всем, кто хоть раз строил Сводные таблицы на основе отчета, полученного из учетной системы. В одном столбце расположены разнотипные данные и Клиент, и Категория товара, и Наименование товара. Значения же, например, объем продаж разбит по нескольким столбцам, по месяцам: Январь в своем столбце, Февраль в своем и так далее.

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

Файлы для скачивания:

Файл Описание Размер файла: Скачивания
Пример 52 Кб 1429

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

Еще одна типичная проблема для обработки данных с помощью Сводных таблиц – это “нарисованные” руками “Мега-таблицы”, которые не поддаются анализу в силу специфики их структуры. В этой статье я расскажу, как быстро преобразовать такое “горе-творчество” в массив для построения сводной таблицы.

Файлы для скачивания:

Файл Описание Размер файла: Скачивания
Пример 69 Кб 1669

Как отфильтровать столбец сводной таблицы?

На тренингах я часто задаю вопрос участникам: Кто знает, как отфильтровать отдельный (не итоговый) столбец сводной таблицы по значениям? И что я слышу в ответ: Это невозможно! В сводных таблицах нет такой возможности! Вопрос настолько древний, что люди уже перепробовали всякие варианты и не найдя решения сделали вывод – невозможно. Хотя решение существует.

Файлы для скачивания:

Файл Описание Размер файла: Скачивания
Пример 290 Кб 1486

Как построить сводную таблицу по нескольким массивам (листам)?

Типичная задача при обработке информации полученной из разных источников. Типовое решение – взять и свести все таблицы в одну. Но что делать, когда таблиц много (например, 20), или свести их в одну нет возможности, на листе просто не хватает строк (все таблицы в сумме дают больше 1 100 000 строк)?

Однако решение существует! И оно не очень сложное.

Файлы для скачивания:

Файл Описание Размер файла: Скачивания
Пример 27 Кб 1145

Как построить сводную таблицу?

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

Как проводить вычисления в сводной таблице?

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

Файлы для скачивания:

Файл Описание Размер файла: Скачивания
Пример 109 Кб 2498

Что такое сводные таблицы?

Приходилось ли вам когда-нибудь попадать в такую ситуацию?:

Вы очень долго “рисовали” сложную таблицу с множеством строк и столбцов и большим содержанием данных, например, таблицу – отчет по продажам в разрезе Подразделений, Городов, Менеджеров, Типов клиентов, с группировкой по кварталам, по количеству и суммам, с вычислением процентов и долей. И когда уже все готово и “раскрашено”, вы показываете эту таблицу, например, руководителю, а он говорит: “Все круто, вот только бы добавить сюда еще разрез по Товарным категориям, вообще было бы замечательно”.

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

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

Источник: e-xcel.ru

Властелин таблиц: 10 малоизвестных фишек для бизнеса в Excel

H&F разобрался, как предпринимателю перестать бояться формул Excel и начать считать всё, что хочется.

Excel — не самая дружелюбная программа на свете. Обычный пользователь использует лишь 5% её возможностей и плохо представляет, какие сокровища скрывают её недра. H&F почитал советы Excel-гуру и научился сравнивать прайс-листы, прятать секретную информацию от чужих глаз и составлять аналитические отчёты в пару кликов. (О’кей, иногда этих кликов 15.)

Импорт курса валют

В Excel можно настроить постоянно обновляющийся курс валют.

— Выберите в меню вкладку «Данные».

— Нажмите на кнопку «Из веба».

— В появившемся окне в строку «Адрес» введите http://www.cbr.ru и нажмите Enter.

— Когда страница загрузится, то на таблицах, которые Excel может импортировать, появятся чёрно-жёлтые стрелки. Щелчок по такой стрелке помечает таблицу для импорта (картинка 1).

— Пометьте таблицу с курсом валют и нажмите кнопку «Импорт».

— Курс появится в ячейках на вашем листе.

— Кликните на любую из этих ячеек правой кнопкой мыши и выберите в меню команду «Свойства диапазона» (картинка 2).

— В появившемся окне выберите частоту обновления курса и нажмите «ОК».

Супертайный лист

Допустим, вы хотите скрыть часть листов в Excel от других пользователей, работающих над книгой. Если сделать это классическим способом — кликнуть правой кнопкой по ярлычку листа и нажать на «Скрыть» (картинка 1), то имя скрытого листа всё равно будет видно другому человеку. Чтобы сделать его абсолютно невидимым, нужно действовать так:

— Слева у вас появится вытянутое окно (картинка 2).

— В верхней части окна выберите номер листа, который хотите скрыть.

В нижней части в самом конце списка найдите свойство «Visible» и сделайте его «xlSheetVeryHidden» (картинка 3). Теперь об этом листе никто, кроме вас, не узнает.

Запрет на изменения задним числом

Перед нами таблица (картинка 1) с незаполненными полями «Дата» и «Кол-во». Менеджер Вася сегодня укажет, сколько морковки за день он продал. Как сделать так, чтобы в будущем он не смог внести изменения в эту таблицу задним числом?

— Поставьте курсор на ячейку с датой и выберите в меню пункт «Данные».

— Нажмите на кнопку «Проверка данных». Появится таблица.

— В выпадающем списке «Тип данных» выбираем «Другой».

— В графе «Формула» пишем =А2=СЕГОДНЯ().

— Убираем галочку с «Игнорировать пустые ячейки» (картинка 2).

— Нажимаем кнопку «ОК». Теперь, если человек захочет ввести другую дату, появится предупреждающая надпись (картинка 3).

— Также можно запретить изменять цифры в столбце «Кол-во». Ставим курсор на ячейку с количеством и повторяем алгоритм действий.

Запрет на ввод дублей

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

— Выделяем ячейки А1:А10, на которые будет распространяться запрет.

— Во вкладке «Данные» нажимаем кнопку «Проверка данных».

— Во вкладке «Параметры» из выпадающего списка «Тип данных» выбираем вариант «Другой» (картинка 1).

— В графе «Формула» вбиваем =СЧЁТЕСЛИ($A$1:$A$10;A1)

Источник: www.the-village.ru