Как в excel работать со сводными таблицами

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

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

Исходный материал – таблица с несколькими десятками и сотнями строк, несколько таблиц в одной книге, несколько файлов. Напомним порядок создания: «Вставка» – «Таблицы» – «Сводная таблица».

А в данной статье мы рассмотрим, как работать со сводными таблицами в Excel.

Как сделать сводную таблицу из нескольких файлов

Первый этап – выгрузить информацию в программу Excel и привести ее в соответствие с таблицами Excel. Если наши данные находятся в Worde, мы переносим их в Excel и делаем таблицу по всем правилам Excel (даем заголовки столбцам, убираем пустые строки и т.п.).

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

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

Гораздо сложнее сделать сводную таблицу на основе разных по структуре исходных таблиц. Например, таких:

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

Мастер сводных таблиц при таких исходных параметрах выдаст ошибку. Так как нарушено одно из главных условий консолидации – одинаковые названия столбцов.

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

  1. В ячейке-мишени (там, куда будет переноситься таблица) ставим курсор. Пишем = – переходим на лист с переносимыми данными – выделяем первую ячейку столбца, который копируем. Ввод. «Размножаем» формулу, протягивая вниз за правый нижний угол ячейки.
  2. По такому же принципу переносим другие данные. В результате из двух таблиц получаем одну общую.
  3. Теперь создадим сводный отчет. Вставка – сводная таблица – указываем диапазон и место – ОК.

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

Покажем, к примеру, количество проданного товара.

Можно выводить для анализа разные параметры, перемещать поля. Но на этом работа со сводными таблицами в Excel не заканчивается: возможности инструмента многообразны.

Детализация информации в сводных таблицах

Из отчета (см.выше) мы видим, что продано ВСЕГО 30 видеокарт. Чтобы узнать, какие данные были использованы для получения этого значения, щелкаем два раза мышкой по цифре «30». Получаем детальный отчет:

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

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

Курсор должен стоять в любой ячейке сводного отчета.

Правая кнопка мыши – обновить.

Чтобы настроить автоматическое обновление сводной таблицы при изменении данных, делаем по инструкции:

  1. Курсор стоит в любом месте отчета. Работа со сводными таблицами – Параметры – Сводная таблица.
  2. Параметры.
  3. В открывшемся диалоге – Данные – Обновить при открытии файла – ОК.

Изменение структуры отчета

Добавим в сводную таблицу новые поля:

  1. На листе с исходными данными вставляем столбец «Продажи». Здесь мы отразим, какую выручку получит магазин от реализации товара. Воспользуемся формулой – цена за 1 * количество проданных единиц.
  2. Переходим на лист с отчетом. Работа со сводными таблицами – параметры – изменить источник данных. Расширяем диапазон информации, которая должна войти в сводную таблицу.

Если бы мы добавили столбцы внутри исходной таблицы, достаточно было обновить сводную таблицу.

После изменения диапазона в сводке появилось поле «Продажи».

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

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

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

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

  1. Определяемся, какие функции будет выполнять виртуальный столбец. На какие данные сводной таблицы вычисляемое поле должно ссылаться. Допустим, нам нужны остатки по группам товаров.
  2. Работа со сводными таблицами – Параметры – Формулы – Вычисляемое поле.
  3. В открывшемся меню вводим название поля. Ставим курсор в строку «Формула». Инструмент «Вычисляемое поле» не реагирует на диапазоны. Поэтому выделять ячейки в сводной таблице не имеет смысла. Из предполагаемого списка выбираем категории, которые нужны в расчете. Выбрали – «Добавить поле». Дописываем формулу нужными арифметическими действиями.
  4. Жмем ОК. Появились Остатки.

Группировка данных в сводном отчете

Для примера посчитаем расходы на товар в разные годы. Сколько было затрачено средств в 2012, 2013, 2014 и 2015. Группировка по дате в сводной таблице Excel выполняется следующим образом. Для примера сделаем простую сводную по дате поставки и сумме.

Щелкаем правой кнопкой мыши по любой дате. Выбираем команду «Группировать».

В открывшемся диалоге задаем параметры группировки. Начальная и конечная дата диапазона выводятся автоматически. Выбираем шаг – «Годы».

Получаем суммы заказов по годам.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также:  В эксель работа с массивами

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

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

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

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

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

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

Расчет KPI

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

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

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

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

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

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

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

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

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

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

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

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

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

В появившемся окне необходимо выбрать источник данных, который представляет:

  1. Диапазон (может находиться в другой книге);
  2. Таблица данных (указывается ее имя);
  3. Данные из внешнего источника, полученные по SQL-запросу из базы данных и т.п.

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

Управление списком полей таблицы

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

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

  • Названия строк – отображает значения поля в виде названия строк. По сути данная область ничего не меняет, если назначить ей все имеющиеся поля, то получим исходную таблицу;
  • Названия столбцов – значения полей отображаются в виде названий столбцов. Действие напоминает обычное транспонирование;
  • Значения – со значениями столбцов, помещенными в эту область, производятся вычисления. При этом одно поле может быть назначено ей несколько раз;
  • Фильтр отчета – сюда помещаются поля, значения которых служат для фильтрации строк исходных данных, которые сводная таблица должна проигнорировать.

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

Месяц Дата Кол-во Курс Изменение
Январь 10.01.2013 1 30,4215 0,0488
Январь 11.01.2013 1 30,3650 -0,0565
Январь 12.01.2013 1 30,2537 -0,1113
. . . . .
Сентябрь 28.09.2013 1 32,3451 0,1715

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

В область «Названия строк» перетащим поле «Месяц». Области «Значения» назначим «Курс», после чего изменим параметры для поля так, чтобы по нему высчитывалось среднее арифметическое. Для этого кликаем по требуемому пункту в области, в раскрывшемся меню жмем на «Параметры полей значений…». В появившемся окне имеется вкладка «Операция». В ней необходимо выбрать из списка «Среднее». Готово.

Также в параметрах поля можно изменить заголовок и применить дополнительные вычисления на одноименной вкладке рассмотренного меню.

Вычисляемые поля сводной таблицы

Если предоставленных операций и вычислений недостаточно, то эксель позволяет создать свое вычисляемое поле в сводной таблице. Для этого выделите ячейку из области таблицы, перейдите на вкладку «Параметры» («Анализ» для Excel 2013) появившейся ленты. Далее в разделе «Сервис» кликните по пиктограмме «Формулы», из раскрывающегося меню (в версии 2010 и выше путь отличается: Раздел «Вычисления» -> Раскрывающийся список «Поля, элементы и наборы») выберите пункт «Вычисляемое поле…». Должно появиться окно:

Задайте понятное имя, и запишите формулу, используя любые функции (имейте в виду, что вычисляемые поля не работают с текстом). В качестве примера умножим курс на 1000 и вычтем 13 процентов (=Курс*1000*0,87). Назовем поле «ЗП», добавим в область значений и в качестве операции применим максимум. Посмотрите новый вид отчета:

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

Параметры сводной таблицы в Excel

Для дальнейшего изучения темы построим более сложную таблицу (принцип построения не отличается от рассмотренного ранее).
Исходные данные представляют список из 100 строк, где каждая запись отражает заработную плату сотрудников различных отраслей в определенных регионах:

Из примера видно, что сводная таблица представляет древовидную структуру, если используется более 1 поля. Корнем являются значения столбца, который в списке области «Названия строк» идет первым. Все последующие поля вкладываются в него и в друг друга, согласно своей очередности в списке, изменить которую можно простым перетаскиванием мыши. Каждую отдельную ветвь подобного дерева можно сворачивать и раскрывать. Данное свойство так же применимо к области названий столбцов.
По умолчанию эксель задает сводным таблицам макет в сжатом виде. Его можно изменить через параметры (клик правой кнопкой мыши по области таблицы -> параметры сводной таблицы -> Вывод -> Классический макет) либо через конструктор:

Читайте также:  Excel vba преобразовать число в текст

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

Так как сводная таблица представляет древовидную структуру, то название строки отображается только один раз. В Microsoft Excel, начиная с версии 2010, можно дополнительно применить к макету повторение подписей элементов.

Теперь законченная сводная таблица выглядит так на листе Excel:

Помимо рассмотренных свойств через параметры таблицы можно установить:

  1. Имя сводной таблицы;
  2. Объединение и выравнивание подписей;
  3. Вывод значений для пустых ячеек;
  4. Автоматическое изменение ширины столбцов;
  5. Отображение общих итогов по строкам и столбцам;
  6. Сортировку;
  7. Печать;
  8. Обновление и др.

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

Источник: office-menu.ru

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

Видео

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Общие сведения о сводных таблицах

БД.xlsx (30,9 KiB, 1 433 скачиваний)

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

Для чего же нужны сводные? В Excel работу можно разделить на две категории: анализ(вычисление) и форматирование данных. Под вычислениями и анализом я понимаю получение неких показателей на основании имеющихся данных. А форматирование – не закраска ячеек цветом, а вид представления таблиц данных. Все это можно сделать и формулами. Предположим, есть исходные ежедневные данные по продажам всех филиалов за полгода. Из этих данных необходимо построить отчет в разрезе каждого филиала и для филиала в разрезе месяца. Плюс сводные отчеты по каждому филиалу за все полгода. А теперь представим как это будет выглядеть формулами:
-сначала надо получить список месяцев;
-затем список уникальных наименований филиалов;
-далее создать листы с рыбой таблиц, в которые надо будет собрать данные из исходной таблицы при помощи СУММЕСЛИ, СУММПРОИЗВ и им подобным.
При должном опыте можно уложиться минут в 15-20. С использованием сводных это можно сделать за две минуты.

Сводная таблица (Pivot Table) – инструмент Excel, используемый для создания уникального представления данных и последующего анализа. Сводная таблица может быть построена на основе правильно сформированной исходной таблицы данных:

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

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

Выделить любую ячейку сводной таблицы→Правая кнопка мыши→Обновить (Refresh) или вкладка Данные (Data) →Обновить все (Refresh all) →Обновить (Refresh) .

СОЗДАНИЕ СВОДНОЙ ТАБЛИЦЫ

  1. Выделить любую ячейку исходной таблицы
  2. Вкладка Вставка (Insert) →группа Таблица (Table) →Сводная таблица (PivotTable)
  3. В диалоговом окне Создание сводной таблицы (Create PivotTable) проверить правильность выделения диапазона данных (или установить новый источник данных), определить место размещения Сводной таблицы:
    • На новый лист (New Worksheet)
    • На существующий лист (Existing Worksheet)
  4. нажать OK

СВОДНАЯ ТАБЛИЦА СОСТОИТ ИЗ ЧЕТЫРЕХ ОБЛАСТЕЙ:
Область данных – основная область сводной таблицы, в которой производятся расчеты. Содержит основные итоговые данные по числовым полям. В область данных можно поместить одно и тоже поле, но с разными вычислениями (например одно Сумма по полю, другое Количество по полю).
Основные вычислительные функции области данных:

 Сумма (Sum)
 Количество (Count)
 Среднее (Average)
 Максимум (Max)
 Минимум (Min)
 Произведение (Product)

Область строк – состоит из заголовков в левой части сводной таблицы и отображает уникальные значения вставленного поля.
Область столбцов – состоит из заголовков столбцов сводной таблицы.
Область страниц и фильтров – необязательная область. Расположена в верхней части сводной таблицы и выполняет функции фильтрации по вставленным в неё полям.

При использовании сводной таблицы следует учитывать, что она не обновляет свои значения автоматически при изменении значений в исходных данных. Для выполнения обновления необходимо выделить любую ячейку сводной таблицы→Правая кнопка мыши→Обновить (Refresh) или вкладка Данные (Data) →Обновить (Refresh) . Это связано с тем, что сводная не содержит прямой ссылки на исходные данные, а хранит их в кэше, что позволяет сводной достаточно быстро обрабатывать данные.

Статья помогла? Поделись ссылкой с друзьями!

Источник: www.excel-vba.ru