Для чего нужна сводная таблица в excel

Для чего нужны сводные таблицы в excel

​Смотрите также​​ имеющихся полей строк​ в двух магазинах.​ флажок напротив названия​ продаж по месяцам.​ доступа.​ которая подходит для​ форматирование. Например, если​ ниже.​ 2 3 4​Второе поле​Group​ будущем измените структуру​ В оставшейся части​ заголовки из верхней​ примерно так:​

​ было.​Сводные таблицы​ и установить у​Порядок создания сводной таблицы​ товара.​ Поэкспериментируем и установим​На первом шаге выбираем​ Вашей версии Excel.​ для значений в​Убедитесь, что выбранный диапазон​ 5 6 7​Amount​(Группировать):​ сводной таблицы (а​ статьи мы разберём​ области в пустые​Возможно, Вас удивит, что​Сводные таблицы вместе с​

• ​— это один​
• ​ других полей. Сделаем​
• ​ из нескольких листов​
• ​Жмем ОК – сводная​
• ​ шаг – «Кварталы».​
• ​ источник данных для​
• ​Рекомендуется начать с 1-й​

Немного истории

​ столбцах​ охватывает именно те​ 8 9 10​назовите​Появится диалоговое окно группировки.​ это случится с​ некоторые способы создания​ области внизу. При​ из этого списка​ недооценкой успеха Windows​ из мощнейших инструментов​ отчет по наименованиям​ такой же.​ таблица меняется.​ Результат – сводная​ формирования сводной таблицы.​ части самоучителя и​B​ ячейки, которые должны​ 11​Averag​ Мы выбираем​ вероятностью 99%), то​ более сложных сводных​ этом автоматически формируется​ торговых операций с​

​ в целом, сыграли​ Excel. Они позволяют​

• ​ товаров, а не​Создадим отчет с помощью​Сводная таблица применяется для​ таблица вида:​ Нажимаем «Далее». Чтобы​ изучать пособие по​–​ быть использованы для​Date​e, операцию для него​Months​ форматирование будет потеряно.​ таблиц, а также​ сводная таблица, в​ помощью сводных таблиц​ похоронный марш для​ анализировать и подводить​ по отделам.​
• ​ мастера сводных таблиц:​ быстрого анализа большого​Если фамилия продавцов для​ собрать информацию в​ сводным таблицам Excel​G​ создания сводной таблицы.Здесь​Invoice Ref​ установите​(Месяцы) и жмём​ Нам же нужен​ узнаем, как их​ соответствии с нашими​ и всего за​ Lotus 1-2-3 и​ различные итоги для​

​А вот что получится,​Вызываем меню «Мастер сводных​ объема данных. Она​ анализа деятельности сети​ сводный отчет из​ последовательно.​настроить денежный формат,​

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

​ же можно выбрать,​Amount​Average​

​ОК​ способ сделать его​ настраивать.​ инструкциями. Если мы​ несколько секунд, мы​ положили начало успеху​ больших объёмов данных​ если мы уберем​ таблиц и диаграмм».​ позволяет объединять информацию​ магазинов не важна,​ нескольких листов, выбираем:​Часть 1: Что такое​ то прочесть сводную​ где должна быть​Sales Rep.​(Среднее) и формат​:​ (почти) постоянным.​Во-первых, мы можем создать​ допустили ошибку, то​ можем создать в​ Microsoft Excel.​ с помощью всего​ «дату» и добавим​ Для этого щелкаем​ из разных таблиц​ можем сформировать отчет​ «в списке или​

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

​ «отдел»:​ кнопку настройки панели​

​ и листов, подсчитать​ с поквартальной прибылью.​ базе данных Microsoft​ Excel?​ легче.​ таблица. Можно поместить​01/01/2016​ поле тоже измените​ намного больше пользы:​Sum of Amount​ Сделаем это, используя​ из нижней области​ о продажах, что​ охарактеризовать, что собой​ мышью. В этой​А вот такой отчет​ быстрого доступа и​ общий результат. Этот​Чтобы убрать результаты группировки,​ Excel».​Часть 2. Создаём простейшую​Фильтры в сводной таблице​ сводную таблицу​2016-0001​ на​Кстати, эта таблица практически​в области​ заголовок столбца​ либо перетащить другие​

​ мы разбирали выше.​ представляют сводные таблицы?​ статье мы познакомимся​ можно сделать, если​ нажимаем «Другие команды».​ универсальный аналитический инструмент​ необходимо щелкнуть по​На втором шаге определяем​ сводную таблицу в​ позволяют отобразить информацию​На существующий лист​$819​Accounting​ идентична той, которая​Values​Payment Method​

​ им на замену.​ Да, мы сможем​Говоря простым языком, сводные​ со сводными таблицами,​

​ перетащить поля между​ Здесь на вкладке​ существенно расширяет возможности​ ячейке с данными​ диапазон данных, на​ Excel?​ для одного значения​(Existing Worksheet) или​Barnes​(Финансовый) с числом​ была показана в​(Значения) и кликнем​(Способ оплаты). Просто​

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

​Область​ сделать это и​ таблицы — это​ разберёмся, что они​ разными областями:​ «Настройка» находим «Мастер​ программы Excel.​ правой кнопкой мыши​ основании которых будет​Часть 3: Группировка в​ или избирательно для​На новый лист​North​ десятичных знаков равным​

​ начале статьи, где​ по ней. В​

​ перетащите заголовок​Values​ еще многое другое!​ итоги каких-то данных,​ собой представляют, научимся​

​Чтобы название строки сделать​ сводных таблиц». Добавляем​

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

​(New Worksheet). Нажмите​​01/01/2016​​ нулю.​ итоги продаж были​ появившемся меню выберем​

• ​Payment Method​(Значения), вероятно, самая​Для начала убедитесь, что​
• ​ созданные для облегчения​

​ создавать их и​ названием столбца, выбираем​ инструмент в панель​ по исходным параметрам,​ Либо выбрать данный​ как у нас​Часть 4: Продвинутые сводные​ имеющихся полей данных.​ОК​2016-0002​Для третьего поля​ составлены вручную.​ пункт​в область​ важная из четырёх.​ у Вас есть​ анализа этих данных.​ настраивать.​ это название, щелкаем​ быстрого доступа. После​ поменяв строки и​ параметр в меню​ стоит курсор в​​ таблицы в Excel.​​ Например, в показанной​

​.​$456​Amount​Есть еще один очень​

​Value Field Settings​Column Labels​ То, какой заголовок​ какие-то исходные данные​ В отличие от​​При написании данной статьи​​ по всплывающему меню.​

​ добавления:​ столбцы местами. Можно​ «Структура».​ таблице, диапазон обозначится​Часть 5: Сортировка в​ выше сводной таблице​Появится пустая сводная таблица​Brown​установите название​ важный момент, который​(Параметры полей значений):​(Колонны):​ помещён в эту​ на листе Excel.​ созданных вручную итогов,​ был использован Excel​ Нажимаем «переместить в​Ставим курсор на первую​ произвести фильтрацию данных,​У нас есть сводный​ автоматически.​ сводной таблице.​ мы сможем просматривать​ и панель​South​Count​ необходимо знать! Вы​

​Появится диалоговое окно​​Получим результат:​​ область, определяет, по​ Перечень финансовых операций​ сводные таблицы Excel​ 2010. Концепция сводных​ название столбцов». Таким​ табличку и нажимаем​ показав разные элементы.​ отчет такого вида:​На третьем шаге Excel​Дальнейшее углублённое обучение работе​ данные только для​Поля сводной таблицы​01/01/2016​и операцию для​​ можете создать не​​Value Field Settings​Выглядит очень круто!​ каким данным будут​​ – самое типичное,​​ интерактивны. После создания,​

​ таблиц почти не​​ способом мы переместили​​ инструмент «Мастера». В​ А также наглядно​​Видны итоги по месяцам​​ предлагает выбрать, куда​​ со сводными таблицами​​ региона продаж​(Pivot Table Field​2016-0003​​ него –​​ один, а несколько​

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

​ открывшемся окне отмечаем,​ детализировать область.​ (сделано «Группировкой») и​ поместить сводную таблицу.​ можно найти на​North​ List), в которой​$538​

​Count​ уровней заголовков строк​Нажмите кнопку​ Как может выглядеть​ среднее, максимум, минимум​ самом деле, это​ изменять их, если​ но способ их​Поле «Отдел» мы проставили​ что создать таблицу​Для примера используем таблицу​ по наименованиям товаров.​ Жмем «Готово» и​ сайте Microsoft Office.​или только для​ уже содержатся несколько​Jones​(Количество)​ (или столбцов):​Number Format​​ такая таблица? Давайте​​ и т.д.) Это,​ может быть перечень​​ они не дали​​ создания немного различен​

​ перед наименованиями товаров.​ хотим в «нескольких​ реализации товара в​

​ Сделаем отчет более​ открывается макет.​Урок подготовлен для Вас​ региона​ полей данных. Обратите​

​South​В области​… а выглядеть это​(Числовой формат), откроется​ посмотрим…​ почти всегда, численные​ чего угодно: контактные​ той картины, которую​ в каждой новой​ Воспользовавшись разделом меню​ диапазонах консолидации». То​ разных торговых филиалах.​ удобным для изучения.​Нужно обозначить поля для​ командой сайта office-guru.ru​

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

​South​ внимание, что эти​01/01/2016​Column Labels​​ будет так…​​ диалоговое окно​Перетащите заголовок​​ значения. Отличный кандидат​​ данные сотрудников, коллекция​​ Вы надеялись получить.​​ версии Excel. Если​

​ «переместить в начало».​

​Из таблички видно, в​Как в сводной таблице​ отображения в отчете.​Источник: http://www.excelfunctions.net/Excel-Pivot-Table-Tutorial.html​

​.​​ поля – заголовки​​2016-0004​​(Колонны) автоматически создано​​То же самое можно​

​Package​ на место в​ компакт-дисков или данные​ Всего лишь парой​ у Вас версия​Покажем детали по конкретному​ объединить несколько мест​ каком отделе, что,​ сделать итоги сверху:​ Допустим, мы хотим​Перевел: Антон Андронов​Чтобы отобразить данные только​ из таблицы исходных​$1,009​ поле​​ проделать с заголовками​​(Формат ячеек):​

​(Комплекс) в область​ этой области –​ о расходе топлива​ щелчков мышью итоги​ Excel не 2010​

​ продукту. На примере​ с информацией. Вид​ когда и на​«Работа со сводными таблицами»​ узнать суммы продаж​Автор: Антон Андронов​ для региона продаж​ данных.Мы хотим, чтобы​​Barnes​​Σ Values​ столбцов (или даже​​Из списка​​Report Filter​ данные под заголовком​ Вашей компании.​ могут быть перевёрнуты​​ года, то будьте​​ второй сводной таблицы,​

​ отчета – «сводная​ какую сумму было​ — «Конструктор».​ по каждому продавцу.​​Работать со сводными таблицами​​North​

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

​ сводная таблица показывала​North​(Σ Значения) –​ с фильтрами).​Category​(Фильтры):​Amount​Итак, запускаем Excel… и​ таким образом, что​ готовы, что снимки​ где отображены остатки​ таблица». «Далее».​ продано. Чтобы найти​

​На вкладке «Макет» нажимаем​ Ставим галочки –​ Excel приходится в​, в панели​ итоги продаж помесячно​01/02/2016​ перетащите его в​Вернёмся к исходному виду​(Числовые форматы) выберите​Заметьте, где он оказался…​(Стоимость) нашей исходной​ загружаем такой список…​ заголовки столбцов становятся​ экранов в данной​ на складах. Выделяем​Следующий этап – «создать​ величину продаж по​ «Промежуточные итоги». Выбираем​ получаем:​ разных сферах. Можно​Поля сводной таблицы​

​ с разбиением по​​2016-0005​​ область​​ таблицы и посмотрим,​​Accounting​Это даёт нам возможность​ таблицы. Перетащим этот​После того, как мы​​ заголовками строк и​​ статье будут отличаться​

​ ячейку. Щелкаем правой​​ поля». «Далее».​​ каждому отделу, придется​

​ «Показывать все промежуточные​​Готовый отчет можно форматировать,​​ быстро обрабатывать большие​(Pivot Table Field​​ регионам и по​​$486​

​Row Labels​​ как вывести средние​​(Финансовый) и число​​ отфильтровать отчёт по​​ заголовок в область​ открыли этот список​ наоборот. Со сводными​ от того, что​​ кнопкой мыши –​​Прописываем диапазон данных, по​ посчитать вручную на​ итоги в заголовке​

​ изменять.​ объемы информации, сравнивать,​ List) перетащите поле​

​ продавцам. Для этого​Jones​(Строки)​ значения вместо сумм.​ десятичных знаков установите​ признаку «Какой комплекс​Values​

​ в Excel, можем​​ таблицами можно проделать​​ Вы увидите на​ «развернуть».​

​ которым будем формировать​ калькуляторе. Либо сделать​ группы».​​​ группировать данные. Это​​Region​ в панели​South​

​Вот что мы получим​Для начала кликните на​ равным нулю. Теперь​ отдыха был оплачен».​

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

​(Значения):​ приступить к созданию​ множество различных действий.​ своём экране.​В открывшемся меню выбираем​ сводный отчет. Выделяем​ еще одну таблицу​Получается следующий вид отчета:​Это можно сделать вручную​ значительно облегчает труд​в область​

​Поля сводной таблицы​​01/02/2016​​ в итоге:​​Sum of Amount​​ несколько раз нажмите​ Например, мы можем​Обратите внимание, что заголовок​ сводной таблицы.​ Вместо того, чтобы​​Немного истории​​ поле с данными,​

​ первый диапазон вместе​ Excel, где посредством​Уже нет той перегруженности,​ и автоматически.​ менеджеров, продавцов, руководителей,​Фильтры​(Pivot Table Field​2016-0006​Общая сумма, среднее значение​и из появившегося​

​ОК​ видеть разбивку по​Amount​Выделите любую ячейку из​ пытаться описать на​​Что такое сводные таблицы?​​ которые необходимо показать.​

​ с шапкой –​ формул показать итоги.​​ которая затрудняла восприятие​​Вручную:​​ маркетологов, социологов и​​(Report Filters).​

​ List) сделайте вот​$948​

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

​ словах все возможности​Как создать сводную таблицу?​Когда нажимаем на сводную​ «добавить». Второй диапазон​ Такими методами анализировать​ информации.​Ставим курсор в любом​

​ что:​Smith​ – всё в​Value Field Settings​

​ к нашей сводной​ способам оплаты для​ а в области​Затем на вкладке​

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

​ месте сводной таблице.​​Сводные таблицы позволяют быстро​​Region​Перетащите поле​​North​​ одной сводной таблице!​(Параметры полей значений):​​ таблице.​​ всех комплексов или​

​Values​Insert​​ продемонстрировать это на​​Форматирование сводных таблиц​​ закладка с параметрами​​ столбцов – снова​ и ошибиться.​ Просто на вкладке​ В результате становится​ сформировать различные отчеты​​появится вверху сводной​​Date​

​01/02/2016​​Сводные таблицы Microsoft Excel​​В списке​Как видите, числа оказались​ за пару щелчков​(Значения) появилась запись​(Вставка) выберите команду​ практике…​Прочие настройки сводных таблиц​ отчета. С ее​​ «добавить».​​Самое рациональное решение –​

​ макет выбираем «Не​ видна вкладка «Работа​ по одним и​ таблицы. Откройте выпадающий​в область​

​2016-0007​ содержат очень-очень много​Summarize value field by​ отформатированы как суммы​

1. ​ мышью изменить вид​​Sum of Amount​​PivotTable​Данные, которые Вы анализируете​​Заключение​​ помощью можно менять​
2. ​Теперь в списке выбираем​​ это создание сводной​​ показывать промежуточные суммы»:​​ со сводными таблицами».​​ тем же данным.​
3. ​ список в этом​​Строки​​$740​​ функций и настроек.​​(Операция) в диалоговом​ в долларах.​ сводной таблицы и​​(Сумма по полю​​(Сводная таблица):​ с помощью сводных​
4. ​На заре развития программ​​ заголовки, источники данных,​​ первый диапазон. Ставим​​ таблицы в Excel:​​Получим отчет без дополнительных​В меню «Данные» жмем​​ Кроме того, эти​​ поле и выберите​(Row Labels);​Barnes​ В такой небольшой​​ окне​​Раз уж мы занялись​ показать такую же​ Amount), указывающая на​
5. ​Появится диалоговое окно​​ таблиц, не могут​​ для создания электронных​​ группировать информацию.​​ птичку у единицы.​Выделяем ячейку А1, чтобы​​ сумм:​​ на кнопку «Обновить»​
6. ​ отчеты можно гибко​​ в нём регион​​Перетащите поле​North​​ статье их все​​Value Field Settings​ форматированием, давайте настроим​ разбивку только для​​ то, что столбец​​Create PivotTable​

​ быть какими попало.​ таблиц балом правил​

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

Заключение

​ (или комбинацию клавиш​ настраивать, изменять, обновлять​North​Amount​01/03/2016​ не охватить даже​(Параметры поля значений)​ формат для всей​ заказавших комплекс​Amount​(Создание сводной таблицы)​ Это должны быть​ Lotus 1-2-3. Его​ Excel легко проверить,​ сводного отчета. Даем​ какой информацией придется​ составляются на основании​ ALT+F5).​ и детализировать.​. Сводная таблица (как​в область​2016-0008​ близко. Чтобы полностью​​ выберите​​ сводной таблицы. Есть​​Sunseekers​​просуммирован.​ с двумя вопросами​ необработанные исходные данные,​ превосходство было настолько​ насколько правильно обслуживающие​ ему имя –​ работать.​ «чужих» таблиц, периодически​Если нужно обновить все​У нас есть тренировочная​

​ показано на картинке​Σ Значения​
​$543​
​ описать все возможности​

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

​ несколько способов сделать​.​Если мы посмотрим на​ для Вас:​ вроде какого-то списка.​ полным, что усилия​ организации начисляют квартплату.​ «Магазин 1». Выделяем​В меню «Вставка» выбираем​ нуждаются в детализации.​ отчеты в книге​ таблица с данными:​ ниже) покажет значения​

​(Σ Values);​Smith​ сводных таблиц, потребовалась​(Среднее):​ это. Используем тот,​Итак, если Вы правильно​ саму сводную таблицу,​

​Какие данные использовать для​ Например, это может​ Microsoft, направленные на​ Другой положительный момент​ второй диапазон данных​ «Сводная таблица».​

​ Мы не знаем,​	​ Excel, выбираем кнопку​	​Каждая строка дает нам​	​ только для региона​	​Перетащите поле​
​North​	​ бы небольшая книга​	​Заодно, пока мы здесь,​	​ что попроще…​	​ это понимаете, то​
​ то увидим сумму​	​ создания новой сводной​	​ быть список совершённых​	​ разработку собственного программного​	​ – экономия. Если​
​ – ставим птичку​	​Откроется меню «Создание сводной​	​ откуда взялась сумма​	​ «Обновить все» (или​	​ исчерпывающую информацию об​
​North​	​Region​	​01/03/2016​	​ или большой веб-сайт.​	​ давайте изменим​
​Откройте вкладку​	​ нашу сводную таблицу​	​ всех значений из​	​ таблицы?​	​ продаж в компании​
​ обеспечения (Excel), как​	​ мы будем ежемесячно​	​ у цифры «2».​	​ таблицы», где выбираем​	​ в конкретной ячейке​
​ комбинацию клавиш CTRL+ALT+F5).​	​ одной сделке:​	​.​	​в область​	​2016-0009​
​ Смелые и любознательные​	​Custom Name​	​PivotTable Tools: Design​	​ можно назвать трёхмерной.​	​ столбца​
​Куда поместить сводную таблицу?​	​ за последние шесть​	​ альтернативы Lotus, казались​	​ контролировать, сколько расходуется​	​ Название поля –​
​ диапазон и указываем​	​ Excel. Но можно​	​Настройка автоматического обновления при​

​в каком магазине были​Вы можете быстро переключиться​Колонны​$820​ читатели могут продолжить​(Пользовательское имя) с​(Работа со сводными​ Продолжим настраивать…​Amount​

1. ​Так как на предыдущем​ месяцев.​ пустой тратой времени.​ света, газа, то​ «Магазин 2». Нажимаем​​ место. Так как​​ это выяснить, если​ изменении данных:​ продажи;​ на просмотр данных​(Column Labels);​Brown​ исследование сводных таблиц.​Average of Amount​ таблицами: Конструктор):​Если вдруг выясняется, что​исходной таблицы.​
   • ​ шаге мы уже​Посмотрите на данные, показанные​ А теперь перенесёмся​
   • ​ сможем найти резерв​ «Далее».​ мы установили курсор​
2. ​ разбить сводную таблицу​​На вкладке «Работа со​​какого товара и на​ только для региона​​Перетащите поле​​South​​ Для этого достаточно​​(Количество по полю​Далее разверните меню нажатием​
3. ​ в сводной таблице​​Итак, наша первая сводная​​ выбрали одну из​ на рисунке ниже:​ в 2010 год!​​ для экономии средств​Выбираем, где расположить сводную​ в ячейку с​ на несколько листов.​ сводными таблицами» (необходимо​ какую сумму;​South​Sale​…​ щелкать правой кнопкой​​ Amount) на что-нибудь​​ на стрелочку в​​ должны выводится только​​ таблица создана! Удобно,​​ ячеек списка, то​​Обратите внимание, что это​
4. ​ Excel доминирует среди​ на оплату квартиры.​​ таблицу. На существующем​​ данными, поле диапазона​В марте продано двуспальных​ щелкнуть по отчету)​кто из продавцов постарался;​– для этого​s Rep.​…​ мыши практически на​ покороче. Введите в​ нижнем правом углу​ оплата чеком и​ но не особо​ для создания сводной​ не сырые исходные​​ электронных таблиц более,​​Для начала предлагаем составить​ листе или новом.​ заполнится автоматически. Если​
   • ​ кроватей на сумму​​ выбираем меню «Параметры».​​когда (число, месяц).​​ нужно в выпадающем​​в область​
   • ​…​​ любом элементе сводной​​ этом поле что-нибудь​​ раздела​​ кредитной картой (то​
   • ​ впечатляет. Вероятно, мы​​ таблицы будет выделен​​ данные, поскольку для​​ чем Lotus кода-либо​​ сводную таблицу тарифов​
   • ​ Лучше выбрать новый​​ курсор стоит в​​ 23 780 у.е.​​Открываем «Дополнительные параметры сводной​​Если это огромная сеть​​ списке в поле​

• ​ весь список автоматически.​ них уже подведены​ за всю свою​ по всем коммунальным​
• ​ лист, чтобы не​ пустой ячейке, необходимо​​ Откуда взялась эта​​ таблицы». Открывается мастер.​
• ​ магазинов и продажи​​Region​​(Column Labels).​ создадим сводную таблицу,​ какие открываются функции​Avg​​(Стили сводной таблицы),​​ то мы можем​​ информации о наших​​ Заметьте, что мы​ итоги. В ячейке​ историю, а число​ платежам. Для разных​ было наложений и​ прописать диапазон вручную.​ цифра. Выделяем ячейку​
• ​В разделе «Данные» устанавливаем​​ идут, то в​​выбрать​

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

​ можем выбрать другой​ B3 мы видим​ людей, которые до​ городов данные будут​ смещений. У нас​​ Сводную таблицу можно​​ с данной суммой​​ галочку напротив пункта​​ течение одного квартала​South​ будет заполнена ежедневными​ продаж помесячно за​

Фильтры в сводной таблице

​ Ленте Вы найдёте​Нажмите​ коллекцию встроенных стилей:​Cash​ сейчас.​ диапазон, другую таблицу​ $30000, что, вероятно,​ сих пор используют​ свои.​ получилось так:​ сделать на этом​​ и щелкаем правой​​ «Обновить при открытии​ размер таблицы станет​​.​​ значениями продаж для​

​ год с разбивкой​ две вкладки:​​ОК​​Выберите любой подходящий стиль​​(Наличными). Для этого​​Обратимся к исходным данным​ и даже какой-нибудь​​ является суммарным результатом,​​ Lotus, стремится к​​Для примера мы сделали​​Как видите всего в​

​ же листе или​​ кнопкой мыши и​​ файла».​ ужасающим. Проанализировать данные​Урок подготовлен для Вас​ каждого региона и​ по регионам и​​PivotTable Tools: Options​​и посмотрите, что​ и посмотрите на​ рядом с​ и попробуем определить​​ внешний источник данных,​​ который сделал James​

​ нулю. Как это​ сводную табличку тарифов​ несколько кликов можно​​ на другом. Если​​ выбираем опцию:​Теперь каждый раз при​ в сотне строк​​ командой сайта office-guru.ru​​ для каждого продавца,​​ по продавцам. Процесс​​(Анализ) и​

​ получилось. Обратите внимание,​ результат в своей​
​Column Labels​
​ один или несколько​

​ например, таблицу базы​

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

​ Cook в январе.​ могло произойти? Что​ для Москвы:​

​ создавать сложные отчеты​ мы хотим, чтобы​На новом листе откроется​ открытии файла с​​ будет очень сложно.​Источник: http://www.excelfunctions.net/advanced-pivot-table.html​ как показано ниже.​​ создания этой сводной​Design​ все значения изменились​ сводной таблице:​

​нажмите стрелку вниз​ столбцов, которые можно​ данных Access или​ Где же тогда​ послужило причиной для​Для учебных целей возьмем​ из нескольких листов​ сводные данные были​ таблица с данными​ измененными данными будет​ А на составление​Перевел: Антон Андронов​

​Чтобы сгруппировать данные помесячно:​ таблицы описан ниже.​(Конструктор). Не бойтесь​ с итоговых сумм​Иногда приходится фильтровать данные​ и в выпадающем​ использовать, чтобы раздробить​ MS-SQL. К тому​ исходные данные? Откуда​ такого драматического разворота​ семью из 4​

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

• ​Автор: Антон Андронов​Кликните правой кнопкой мыши​Выделите любую ячейку в​
• ​ допустить ошибку, всегда​ на средние значения,​ по датам. Например,​
• ​ меню снимите галочку​ эту сумму. Например,​
• ​ же нам необходимо​ взялась цифра $30000?​
• ​ событий?​ человек, которые проживают​

​ объема информации.​ не забывайте указывать​Мы можем переместить всю​ сводной таблицы.​

​ один день. В​Перед Вами пошаговое руководство​
​ по любой дате​
​ диапазоне или весь​

​ можно удалить сводную​

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

​ а заголовок таблицы​ в нашем списке​ с пункта​ мы можем сформировать​ выбрать, где разместить​ Где исходный список​Аналитики выделяют два основных​ в квартире 60​Начнем с простейшего: добавления​ для них место.​

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

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

​ торговых операций присутствует​Cash​

​ нашу сводную таблицу​ новую сводную таблицу:​ продаж, из которого​

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

​ таблица просто необходима.​ таблиц в Excel.​ столбце сводной таблицы;​ нужно использовать для​ все заново. У​ ячейке) поменялся на​ много-много дат. Excel​:​ таким образом, чтобы​ на новом листе​ этот итог за​Во-первых, компания Lotus решила,​ контролировать коммунальные платежи,​

​ Для примера рассмотрим​ следующая форма:​ на вкладке «Действия»​ данных, для выводов​Создам отчет с помощью​Мы начнём с того,​

1. ​В появившемся контекстном меню​
2. ​ построения сводной таблицы.​ Вас есть возможность,​Avg​ предоставляет инструмент для​
3. ​Давайте посмотрим, на что​ итоговая сумма продаж​ или на одном​ месяц был получен?​ что эта новомодная​ необходимо создать таблицы​ сводную табличку продаж​Сформируем табличку, которая покажет​ кнопку «Переместить».​ и принятия каких-то​

​ мастера сводных таблиц.​ что дадим ответ​ нажмите​ЗАМЕЧАНИЕ:​

1. ​ которой никогда не​:​ группировки данных по​ теперь похожа наша​ подсчитывалась для каждого​ из существующих. В​ Ясно, что кто-то​ GUI-платформа с названием​
2. ​ для расчета на​ по разным отделам​ сумму продаж по​По умолчанию в сводную​ решения может понадобиться​ В новых версиях​ на самый простой​Группировать​Если выделить одну​ было у давних​
3. ​Если захотеть, то можно​ дням, месяцам, годам​ сводная таблица. Как​ продавца по отдельности.​ данном примере мы​ совершил огромный труд​ Windows — это​ каждый месяц.​
4. ​ (см. выше).​ отделам. В списке​ таблицу помещается абсолютно​ группировка. Допустим, нам​ Excel он почему-то​
5. ​ вопрос: «​(Group);​ ячейку в диапазоне​ пользователей DOS и​ получить сразу сумму,​ и т.д. Давайте​ видите, столбец​

​ Т.е. в нашу​ выберем вариант –​

​ по упорядочиванию и​

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

​ всего лишь мимолётное​Первый столбец = первому​

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

1. ​ полей сводной таблицы​ вся информация из​ нужно увидеть итоги​ спрятано глубоко в​Что же это такое​
2. ​Появится диалоговое окно​ данных, то Excel​ Lotus 1-2-3.​ среднее и количество​
3. ​ посмотрим, как это​Cash​ сводную таблицу добавятся​New Worksheet​ сортировке всех данных​

​ увлечение, которое долго​ столбцу из сводной​

1. ​ у нас была​ выбираем названия столбцов,​ того столбца, который​ за месяц или​
2. ​ настройках:​ – сводные таблицы​
3. ​Группирование​ автоматически определит диапазон​Урок подготовлен для Вас​ (продаж), размещённые в​

​ делается.​исчез из нее.​ строки с именем​(На новый лист):​ о продажах за​

Некоторые секреты форматирования

​ не протянет. Они​ таблицы. Второй –​ область задач, где​ которые нас интересуют.​ мы добавляем в​ квартал.​Выберите «Файл»-«Параметры»-«Панель быстрого доступа».​ в Excel?​(Grouping) для дат​

​ для создания сводной​ командой сайта office-guru.ru​

1. ​ одной сводной таблице.​Для начала уберите запись​
2. ​Очевидно, что сводные таблицы​ каждого продавца компании​Excel создаст новый лист​ последние шесть месяцев​ отказались создавать версию​ формула для расчета​
3. ​ мы выбирали столбцы​ Получаем итоги по​ отчет.​
4. ​Группировка по дате в​В выпадающем списке левой​» – и далее​ (как показано на​ таблицы и расширит​Источник: http://www.howtogeek.com/howto/13336/working-with-pivottables-in-excel/​Вот пошаговая инструкция, как​Payment Method​ – это очень​ и его итоговой​

​ и разместит на​ и превратил их​ Lotus 1-2-3 для​ вида:​ в списке полей.​ каждому отделу.​В нашем примере –​

​ сводной таблице Excel:​ колонки: «Выбрать команду​ покажем, как в​ рисунке ниже). В​ выделение. Для того,​

​Перевел: Антон Андронов​ сделать это, начиная​из области​ мощный инструмент, но​ суммой продаж. Чтобы​ нем пустую сводную​ в таблицу итогов,​ Windows (впрочем, только​

Работа с итогами

​= тариф * количество​ Если она исчезла,​

​Просто, быстро и качественно.​ ВСЕ товары, ВСЕ​Источник информации – отчет​ из» укажите «Все​ Excel создать простейшую​

​ поле​ чтобы Excel выбрал​

1. ​Автор: Антон Андронов​ с пустой сводной​
2. ​Column Labels​ до сих пор​ достичь такого результата,​ таблицу:​ которую мы видим.​
3. ​ несколько лет), предсказывая,​

​ человек / показания​ просто щелкаем мышью​Важные нюансы:​

​ даты, ВСЕ суммы​ с данными.​ команды».​ сводную таблицу.​

​С шагом​ диапазон правильно, должны​

Детализация информации

​В этом разделе самоучителя​ таблицы:​(Колонны). Для этого​ результаты выглядят как-то​ достаточно перетащить заголовок​Как только мы кликнем​ Сколько, по-вашему, это​ что DOS-версия их​ счетчика / площадь​ по табличке.​Первая строка заданного для​

1. ​ и магазины. Возможно,​Так как нам нужна​В левой колонке найдите​Далее будет показано, как​(By) выберите​ быть выполнены следующие​ дана пошаговая инструкция,​Перетащите заголовок​ перетащите его обратно​
2. ​ незамысловато и скучно.​Salesperson​ по любой ячейке​

​ заняло времени? Час?​ программного обеспечения —​Для удобства рекомендуем сделать​Добавим в сводную таблицу​ сведения данных диапазона​

​ пользователю не нужны​ группировка по дате,​ по алфавитному порядку​ создать более продвинутую​Месяцы​ условия:​

​ как создать продвинутую​Salesperson​ к списку заголовков,​ Например, цифры, которые​(Торговый представитель) в​ в сводной таблице,​ Десять часов?​ это всё, что​ промежуточный столбец, в​ еще одно поле​

1. ​ должна быть заполнена.​ некоторые элементы. Они​ выделяем любую ячейку​ и выделите: «Мастер​
2. ​ двухмерную сводную таблицу​(Months). Кстати, сгруппировать​Каждый столбец в диапазоне​ сводную таблицу в​(Торговый представитель) в​ а на его​
3. ​ мы суммируем, не​ область​ появится ещё одно​Дело в том, что​ когда-либо будет нужно​ который будут заноситься​

​ для отчета. Для​В базовой табличке каждый​

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

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

​Row Labels​ диалоговое окно:​ таблица, приведённая выше,​ потребителям. Microsoft, естественно,​ показания по счетчикам​ этого установим галочку​ столбец должен иметь​ и мешают сосредоточиться​

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

​ Щелкаем правой кнопкой​ диаграмм». Нажмите на​ заключение мы расскажем,​

​ можно и по​ уникальный заголовок.​ (2007 и более​Column Labels​Date Booked​ в долларах –​(Строки):​PivotTable Field List​ это не сводная​ разработала Excel специально​ (переменная составляющая).​ напротив «Даты» (или​ свой заголовок –​ на главном. Уберем​ мыши.​

​ кнопку между колонками:​ как сортировать сводные​ другим временным периодам,​

1. ​В диапазоне данных не​ новых). Для тех,​(Колонны).​(Дата бронирования):​
2. ​ это просто какие-то​Становится интересней! Наша сводная​
3. ​(Поля сводной таблицы).​ таблица. Она была​ под Windows.​Наши формулы ссылаются на​ напротив «Товара»). Отчет​ проще настроить сводный​ ненужные элементы.​Из выпавшего меню выбираем​ «Добавить» чтобы инструмент​ таблицы по полям​ например, по кварталам,​ должно быть пустых​ кто работает в​Трижды перетащите заголовок​Как видите, это временно​ цифры. Давайте это​ таблица начинает обретать​Список полей в верхней​ создана вручную из​Во-вторых, Microsoft разработала в​ лист, где расположена​ сразу меняется –​
4. ​ отчет.​Нажимаем на стрелочку у​ «Группировку». Откроется инструмент​ переместился в правую​ данных, чтобы легко​ дням, часам и​ строк.​ более ранних версиях​

​ сделало нашу сводную​

• ​ исправим.​ форму…​ части диалогового окна​
• ​ исходных данных, сохранённых​ Excel такой инструмент,​ сводная таблица с​ появляется динамика продаж​В Excel в качестве​
• ​ названия столбца, где​ вида:​ колонку и нажмите​ извлекать нужную информацию.​ так далее;​

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

​ Excel, рекомендуем статью:​(Стоимость) в область​ таблицу бесполезной. Excel​Велик соблазн сделать привычные​Видите преимущества? За пару​ – это перечень​ где-то ещё, и​ как сводные таблицы,​ тарифами.​

​ по дням в​ источника информации можно​ будем корректировать количество​

​В полях «Начиная с»​ ОК.​

1. ​ Каждый раздел самоучителя​Нажмите​Сводная таблица​ Как создать продвинутую​Values​ cоздал отдельный столбец​ в такой ситуации​ кликов мы создали​ всех заголовков из​ их обработка заняла​ которого не было​Скачать все примеры сводной​
2. ​ каждом отделе.​ использовать таблицы Access,​ информации.​ и «По» Excel​Теперь инструмент находится в​ проиллюстрирован примерами сводных​ОК​(Pivot Table) в​ сводную таблицу в​(Значения).​ для каждой даты,​ действия и просто​
3. ​ таблицу, которую вручную​ исходного списка. Четыре​
4. ​ минимум пару часов.​ в Lotus 1-2-3.​ таблицы​Сгруппируем данные в отчете​ SQL Server и​Выбираем из выпадающего меню​ автоматически проставил начальную​ панели быстрого доступа,​ таблиц.​
5. ​.​ разделе​ Excel 2003?​Для первого поля​ в которую была​ выделить всю таблицу​ пришлось бы создавать​ пустые области в​ Именно такую таблицу​ Сводные таблицы, эксклюзивная​Если при расчете коммунальных​ по месяцам. Для​ др.​
6. ​ название поля. В​ и конечную даты​ а значит всегда​Поскольку интерфейс, используемый для​Как и требовалось, наша​Таблицы​В качестве исходных данных​Amount​

​ совершена торговая операция.​ (или весь лист)​ очень долго.​ нижней части экрана​ итогов можно создать,​ для Excel вещь,​

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

​ платежей применяются льготы,​ этого щелкаем правой​​ нашем примере –​ диапазона данных. Определяемся​ под рукой.​

​ создания сводных таблиц​ сводная таблица (смотрите​(Table) на вкладке​ для построения сводной​измените название на​ В итоге мы​ и использовать стандартные​Что ещё мы можем​

​ позволяют указать сводной​ используя сводные таблицы,​ оказалась так ошеломительно​ их тоже можно​ кнопкой мыши по​Часто требуется создавать сводные​ это название товара​ с шагом группировки.​Ставим курсор в любом​ в Excel 2003,​

​ картинку ниже) теперь​Вставка​ таблицы, мы используем​Total​ получили очень широкую​ кнопки форматирования чисел​ сделать? Ну, в​ таблице, как требуется​

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

​ показывает итоги продаж​(Insert) Ленты меню​ таблицу данных о​(Сумма), а формат​

​ таблицу!​ на панели инструментов,​ определённом смысле, наша​ обобщить данные. Пока​

​ всего лишь несколько​ были склонны осваивать​ Информацию по начислениям​ «Группировать». Выбираем «по​ таблиц. Есть пара​ остановимся на названии.​ – либо месяцы,​ данными. Вызываем мастер​

​ более поздних версий,​ по месяцам с​ Excel.​ продажах компании в​

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

​ месяцам». Получается сводная​ табличек с информацией.​Устанавливаем фильтр по значению.​

​ либо кварталы. Остановимся​ сводных таблиц, нажимая​ мы создали два​ разбивкой по регионам​Откроется диалоговое окно​ первом квартале 2016​ поле на​

Проверка правильности выставленных коммунальных счетов

​ правой кнопкой мыши​ формат. Проблема такого​ готова. Мы создали​ в таблице тоже​ как…​ Excel, а не​ своей обслуживающей организации.​ таблица такого вида:​ Нужно объединить их​ Исключим из отчета​ на месяцах.​ на соответствующий инструмент,​

​ варианта 2-й и​ и по продавцам.​Создание сводной таблицы​ года.​Accounting​ по любой дате​

​ подхода состоит в​ полезную сводку по​ ничего нет. Всё,​

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

​Чтобы улучшить вид сводной​(Create PivotTable), как​A B C​(Финансовый). Количество десятичных​ и выберите из​

​ том, что если​ исходным данным. Уже​ что от нас​

​ списку продаж, то​ Lotus 1-2-3, в​ – просто измените​ сводной таблице, достаточно​ Для науки придумаем​

​ кроватям – уберем​ четко видны суммы​ расположенный напанели быстрого​ учебника. Выберите ту,​

​ таблицы, следует настроить​ показано на рисунке​

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

Как сделать сводные таблицы в Excel: пошаговая инструкция со скриншотами

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

Разберёмся, для чего нужны сводные таблицы. На конкретном примере покажем, как их создать, настроить и использовать. В конце расскажем, можно ли делать сводные таблицы в «Google Таблицах».

Зачем нужны сводные таблицы и когда их используют

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

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

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

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

Разберёмся пошагово, как это сделать с помощью сводной таблицы.

Шаг 1

Создаём сводную таблицу

Чтобы сводная таблица сработала корректно, важно соблюсти несколько требований к исходной:

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

Теперь переходим во вкладку «Вставка» и нажимаем на кнопку «Сводная таблица».

Появляется диалоговое окно. В нём нужно заполнить два значения:

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

В нашем случае выделяем весь диапазон таблицы продаж вместе с шапкой. И выбираем «Новый лист» для размещения сводной таблицы — так будет проще перемещаться между исходными данными и сводным отчётом. Жмём «Ок».

Excel создал новый лист. Для удобства можно сразу переименовать его.

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

Шаг 2

Настраиваем сводную таблицу и получаем результат

В верхней части панели настроек находится блок с перечнем возможных полей сводной таблицы. Поля взяты из заголовков столбцов исходной таблицы: в нашем случае это «Марка, модель», «Цвет», «Год выпуска», «Объём», «Цена», «Дата продажи», «Продавец».

Нижняя часть панели настроек состоит из четырёх областей — «Значения», «Строки», «Столбцы» и «Фильтры». У каждой области своя функция:

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

Настроить сводную таблицу можно двумя способами:

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

Первый вариант не самый удачный: Excel редко ставит данные так, чтобы с ними было удобно работать, поэтому сводная таблица получается неинформативной. Остановимся на втором варианте — он предполагает индивидуальные настройки для каждого отчёта.

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

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

После этого в левой части листа появится первый блок сводной таблицы: фамилии менеджеров по продажам.

Теперь добавим модели автомобилей, которые эти менеджеры продали. По такому же принципу перетянем поле «Марка, модель» в область «Строки».

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

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

Чтобы сводная таблица самостоятельно суммировала эти значения, перетащим поля «Марка, модель» и «Цена» в область «Значения».

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

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

Шаг 3

Настраиваем фильтры сводной таблицы

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

В нашем примере перетянем туда все поля, не вошедшие в основной состав сводной таблицы: объём, дату продажи, год выпуска и цвет.

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

В блоке фильтров нажмём на стрелку справа от поля «Год выпуска»:

В появившемся окне уберём галочку напротив параметра «Выделить все» и поставим её напротив параметра «2017». Закроем окно.

Теперь сводная таблица показывает только автомобили 2017 года выпуска, которые менеджеры продали за квартал. Чтобы снова показать таблицу в полном объёме, нужно в том же блоке очистить установленный фильтр.

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

Шаг 4

Проводим дополнительные вычисления

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

Кликнем правой кнопкой на любое значение цены в таблице. Выберем параметр «Дополнительные вычисления», затем «% от общей суммы».

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

Можно свернуть подробности с перечнями автомобилей, кликнув на знак – слева от фамилии менеджера. Тогда таблица станет короче, а данные, за которыми мы шли, — кто из менеджеров поработал лучше в этом квартале, — будут сразу перед глазами.

Чтобы снова раскрыть данные об автомобилях — нажимаем +.

Чтобы значения снова выражались в рублях — через правый клик мыши возвращаемся в «Дополнительные вычисления» и выбираем «Без вычислений».

Шаг 5

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

Предположим, в исходную таблицу внесли ещё две продажи последнего дня квартала.

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

Переходим на лист сводной таблицы. Во вкладке «Анализ сводной таблицы» нажимаем кнопку «Изменить источник данных».

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

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

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

Например, поменяем цены двух автомобилей в таблице с продажами.

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

Теперь у менеджера Соколова П. изменились данные в столбце «Цена, руб.».

Как использовать сводные таблицы в «Google Таблицах»? Нужно перейти во вкладку «Вставка» и выбрать параметр «Создать сводную таблицу». Дальнейший ход действий такой же, как и в Excel: выбрать диапазон таблицы и лист, на котором её нужно построить; затем перейти на этот лист и в окне «Редактор сводной таблицы» указать все требуемые настройки. Результат примет такой вид:

Другие материалы Skillbox Media для менеджеров

• Руководство: как сделать ВПР в Excel и перенести данные из одной таблицы в другую
• Статья с разбором диаграммы Ганта — что должен знать каждый менеджер
• Подборка советов, как превратить хороший проект в великий, из книги Коллинза Good to Great
• Рассказ о модели VUCA и о том, как она помогает процветать в хаосе
• Подборка одиннадцати типичных ошибок при создании презентации

Исходная таблица — данные, которые сводная таблица собирает, группирует и формирует в отчёт.

Для чего нужна сводная таблица в excel

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

Зачем нужны сводные таблицы?

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

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

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

• Для примера мы будем использовать данные Минпросвещения России о количестве усыновленных за рубеж российских детей в 2019 году. Эту статистику мы использовали в своем исследовании о том, как введенные российскими властями ограничения для иностранцев повлияли на российских сирот. Этот набор данных для тренировки мы собрали из отчетов каждого региона. В них указан год, регион, из которого дети были усыновлены за рубеж, страна, в которую они отправились, количество усыновленных детей, количество усыновленных детей с инвалидностью и количество усыновленных по разным возрастным группам.

• Это руководство подходит для работы в Google Spreadsheets.

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

Перед созданием сводной таблицы важно убедиться, что у каждого столбца есть заголовок – это понадобится при построении сводной таблицы. Чтобы создать сводную таблицу, надо выделить все данные, нажать «Данные» – «Создать сводную таблицу» – «Создать».

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

Например, давайте посчитаем, сколько детей было усыновлено за границу и в какие страны. В поле «Cтроки» мы выбираем «Страна». В поле «Значения» – «Всего усыновлено». В появившемся окне «Суммировать по» – параметр SUM, который просуммирует количество усыновленных.

Появится новая таблица со списком стран и количеством усыновленных в них детей. Внизу таблицы автоматически подсчитывается сумма. Так мы узнаем, что всего за 2019 год иностранцами было усыновлено 240 детей.

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

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

Фильтры в сводной таблице

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

Процентное отображение значений

Данные в сводной таблице могут быть отображены в разных форматах. Сейчас количество усыновленных отображено в абсолютных числах, но мы можем показать их и в процентах. Для этого надо в поле «Значения» в параметре «Всего усыновлено» выбрать «Показывать как» – «% от итоговой суммы». Тогда мы увидим, например, что на Италию пришлось 68% детей из усыновленных за рубеж в 2019 году.

Добавление нескольких значений

В сводной таблице можно добавлять сразу несколько значений. Например, рядом с общим количеством усыновленных в страны показать, сколько из них – детей с инвалидностью. Для этого добавляем еще одного «Значение» – «Усыновлено детей с инвалидностью», а в окне «Суммировать по» – параметр SUM. Можно добавить еще одно значение с возрастом. Например, узнать, сколько в эти страны было усыновлено детей в возрасте до 1 года. Добавляем еще одно «Значение» – «до 1 года», а в окне «Суммировать по» – параметр SUM. Тогда мы увидим, что иностранные усыновители не брали в свои семьи младенцев – по крайней мере в 2019 году.

Перемена строк

Давайте теперь поработаем с регионами: например, узнаем, из каких регионов иностранцы чаще всего усыновляли детей в 2019 году. Для этого надо удалить из конструктора сводных таблиц «Строку» – «Страна» и добавить «Строку» – «Регион», а в «Значениях» оставить только параметр «Всего усыновлено». Выполним сортировку: в поле «Регион» выбираем «Сортировать» – «По убыванию», «Сортировать по» – «SUM параметра Всего усыновлено». В этом случае мы увидим, что больше всего детей, усыновленных иностранцами в 2019 году, жили в Кемеровской области.

Если нас интересует, сколько детей уехало из какого-то конкретного региона, мы снова можем воспользоваться «Фильтрами». В поле «Фильтр» нажимаем «Добавить» – параметр «Регион». Если нажать на выпадающее меню под надписью «Статус», мы увидим, что сейчас там выделены все регионы. Нажмем «Очистить» и введем в поле только один регион, например, «Москва». В появившейся сводной таблице мы увидим, что в 2019 году иностранцы усыновили всего двух детей из Москвы.

Суммирование значений разными способами

До этого момента все значения мы суммировали с помощью функции SUM, но в выпадающем меню есть и другие варианты, позволяющие производить расчеты с данными: среднее, минимум, максимум и другие. Давайте воспользуемся функцией COUNTUNIQUE, которая подсчитывает количество уникальных значений или диапазонов в наборе данных. Например, мы хотим узнать, сколько всего регионов участвуют в международном усыновлении. Для этого очистим таблицу и создадим новую. В «Строки» добавим «Страна». В «Значения» – «Регион» с параметром COUNTUNIQUE. В строке «Итого» сводной таблицы мы увидим, что всего 30 регионов участвовали в усыновлении.

Эта же таблица показывает нам, из какого количества регионов каждая страна усыновляла детей (функция COUNTUNIQUE посчитала количество уникальных пар «Страна» – «Регион»). Если мы добавим рядом еще одно «Значение» – «Всего усыновлено», мы увидим, что, например, все усыновленные Аргентиной дети – а их было 23 – родом из одного региона.

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

Использование столбцов

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

Сохранение результатов

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

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

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

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

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

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

Данную табличку нужно заполнить, т.е. просуммировать выручку по соответствующим товарам и регионам. Это нетрудно сделать с помощью функции СУММЕСЛИМН. Также добавим итоги. Получится сводный отчет о продажах в разрезе область-продукция.

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

— Можно ли отчет сделать не по выручке, а по прибыли?

— Можно ли товары показать по строкам, а регионы по столбцам?

— Можно ли такие таблицы делать для каждого менеджера в отдельности?

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

Рассмотрим, как создать сводную таблицу в Excel.

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

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

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

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

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

Макет таблицы настраивается в панели Поля сводной таблицы, которая находится в правой части листа.

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

Сводная таблица состоит из 4-х областей, которые находятся в нижней части панели: значения, строки, столбцы, фильтры. Рассмотрим подробней их назначение.

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

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

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

Область строк – названия строк, которые расположены в крайнем левом столбце. Это все уникальные значения выбранного поля (столбца). В области строк может быть несколько полей, тогда таблица получается многоуровневой. Здесь обычно размещают качественные переменные типа названий продуктов, месяцев, регионов и т.д.

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

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

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

Посмотрим, как это работает в действии. Создадим пока такую же таблицу, как уже была создана с помощью функции СУММЕСЛИМН. Для этого перетащим в область Значения поле «Выручка», в область Строки перетащим поле «Область» (регион продаж), в Столбцы – «Товар».

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

На ее построение потребовалось буквально 5-10 секунд.

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

Изменить существующую сводную таблицу также легко. Посмотрим, как пожелания директора легко воплощаются в реальность.

Заменим выручку на прибыль.

Товары и области меняются местами также перетягиванием мыши.

Для фильтрации сводных таблиц есть несколько инструментов. В данном случае просто поместим поле «Менеджер» в область фильтров.

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

Источник данных сводной таблицы Excel

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

1. Лучший формат для данных – это Таблица Excel. Она хороша тем, что у каждого поля есть наименование и при добавлении новых строк они автоматически включаются в сводную таблицу.

2. Избегайте повторения групп в виде столбцов. Например, все даты должны находиться в одном поле, а не разбиты по месяцам в отдельных столбцах.

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

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

В целом требований немного, но их следует знать.

Обновление данных в сводной таблице Excel

Если внести изменения в источник (например, добавить новые строки), сводная таблица не изменится, пока вы ее не обновите через правую кнопку мыши

или
через команду во вкладке Данные – Обновить все.

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

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

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