Для чего нужны сводные таблицы в excel
Смотрите также имеющихся полей строк в двух магазинах. флажок напротив названия продаж по месяцам. доступа. которая подходит для форматирование. Например, если ниже. 2 3 4Второе полеGroup будущем измените структуру В оставшейся части заголовки из верхней примерно так:
было.Сводные таблицы и установить уПорядок создания сводной таблицы товара. Поэкспериментируем и установимНа первом шаге выбираем Вашей версии Excel. для значений вУбедитесь, что выбранный диапазон 5 6 7Amount(Группировать): сводной таблицы (а статьи мы разберём области в пустыеВозможно, Вас удивит, чтоСводные таблицы вместе с
- — это один
- других полей. Сделаем
- из нескольких листов
- Жмем ОК – сводная
- шаг – «Кварталы».
- источник данных для
- Рекомендуется начать с 1-й
Немного истории
столбцах охватывает именно те 8 9 10назовитеПоявится диалоговое окно группировки. это случится с некоторые способы создания области внизу. При из этого списка недооценкой успеха Windows из мощнейших инструментов отчет по наименованиям такой же. таблица меняется. Результат – сводная формирования сводной таблицы. части самоучителя иB ячейки, которые должны 11Averag Мы выбираем вероятностью 99%), то более сложных сводных этом автоматически формируется торговых операций с
в целом, сыграли Excel. Они позволяют
- товаров, а неСоздадим отчет с помощьюСводная таблица применяется для таблица вида: Нажимаем «Далее». Чтобы изучать пособие по– быть использованы дляDatee, операцию для негоMonths форматирование будет потеряно. таблиц, а также сводная таблица, в помощью сводных таблиц похоронный марш для анализировать и подводить по отделам.
- мастера сводных таблиц: быстрого анализа большогоЕсли фамилия продавцов для собрать информацию в сводным таблицам ExcelG создания сводной таблицы.ЗдесьInvoice Ref установите(Месяцы) и жмём Нам же нужен узнаем, как их соответствии с нашими и всего за Lotus 1-2-3 и различные итоги для
А вот что получится,Вызываем меню «Мастер сводных объема данных. Она анализа деятельности сети сводный отчет из последовательно.настроить денежный формат,
Что такое сводные таблицы?
же можно выбрать,AmountAverage
ОК способ сделать его настраивать. инструкциями. Если мы несколько секунд, мы положили начало успеху больших объёмов данных если мы уберем таблиц и диаграмм». позволяет объединять информацию магазинов не важна, нескольких листов, выбираем:Часть 1: Что такое то прочесть сводную где должна бытьSales Rep.(Среднее) и формат: (почти) постоянным.Во-первых, мы можем создать допустили ошибку, то можем создать в Microsoft Excel. с помощью всего «дату» и добавим Для этого щелкаем из разных таблиц можем сформировать отчет «в списке или
сводная таблица в таблицу станет гораздо размещена создаваемая своднаяRegion чисел в этомВуаля! От такой таблицыВо-первых, найдём запись двумерную сводную таблицу. можно удалить заголовки Excel помесячный отчётИтак, как же лучше лишь нескольких щелчков
«отдел»: кнопку настройки панели

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

перетащить поля между Здесь на вкладке существенно расширяет возможности ячейке с данными диапазон данных, на Excel? для одного значения(Existing Worksheet) илиBarnes(Финансовый) с числом была показана в(Значения) и кликнем(Способ оплаты). Просто
Как создать сводную таблицу?
Область сделать это и таблицы — это разберёмся, что они разными областями: «Настройка» находим «Мастер программы Excel. правой кнопкой мыши основании которых будетЧасть 3: Группировка в или избирательно дляНа новый листNorth десятичных знаков равным
начале статьи, где по ней. В

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

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

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

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

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

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

таблиц почти не способом мы переместили инструмент «Мастера». В А также наглядноВидны итоги по месяцам предлагает выбрать, куда со сводными таблицами региона продаж(Pivot Table Field2016-0003 него – один, а несколько
(Параметры поля значений).Теперь сделаем трёхмерную таблицу. подводиться итоги (сумма, что встречается. На Вы можете легко изменялась долгие годы, дату в столбцы.

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

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

Сделаем отчет более открывается макет.Урок подготовлен для Вас региона полей данных. Обратите
SouthВ области… а выглядеть это(Числовой формат), откроется посмотрим… почти всегда, численные чего угодно: контактные той картины, которую в каждой новой Воспользовавшись разделом меню диапазонах консолидации». То разных торговых филиалах. удобным для изучения.Нужно обозначить поля для командой сайта office-guru.ru
Настройка сводной таблицы
South внимание, что эти01/01/2016Column 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 Field2016-0006Общая сумма, среднее значениеи из появившегося

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

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

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

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


что:Smith – всё вValue Field Settings
к нашей сводной способам оплаты для а в областиЗатем на вкладке
сводных таблиц, прощеНастройка сводной таблицы таблицу, становится доступной вместе с названием информацию непродуктивно. НедолгоКак удалить промежуточные итоги?

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

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

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

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

С помощью сводных таблиц Это первое поле Excel знал, сОгромные сводные таблицы, которые
Заключение
(или комбинацию клавиш настраивать, изменять, обновлятьNorthAmount01/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
- Так как на предыдущем месяцев. пустой тратой времени. света, газа, то «Магазин 2». Нажимаем место. Так как это выяснить, если изменении данных: продажи; на просмотр данных(Column Labels);Brown исследование сводных таблиц.Average of Amount таблицами: Конструктор):Если вдруг выясняется, чтоисходной таблицы.
- шаге мы ужеПосмотрите на данные, показанные А теперь перенесёмся
- сможем найти резерв «Далее». мы установили курсор
- разбить сводную таблицуНа вкладке «Работа сокакого товара и на только для регионаПеретащите полеSouth Для этого достаточно(Количество по полюДалее разверните меню нажатием

- в сводной таблицеИтак, наша первая сводная выбрали одну из на рисунке ниже: в 2010 год!
для экономии средствВыбираем, где расположить сводную в ячейку с на несколько листов. сводными таблицами» (необходимо какую сумму;SouthSale… щелкать правой кнопкой Amount) на что-нибудь на стрелочку в должны выводится только таблица создана! Удобно, ячеек списка, тоОбратите внимание, что это - 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 решила, контролировать коммунальные платежи,
Для примера рассмотрим следующая форма: на вкладке «Действия» данных, для выводовСоздам отчет с помощьюМы начнём с того,
- В появившемся контекстном меню
- построения сводной таблицы. Вас есть возможность,Avg предоставляет инструмент для
- Давайте посмотрим, на что итоговая сумма продаж или на одном месяц был получен? что эта новомодная необходимо создать таблицы сводную табличку продажСформируем табличку, которая покажет кнопку «Переместить». и принятия каких-то
мастера сводных таблиц. что дадим ответ нажмитеЗАМЕЧАНИЕ:
- которой никогда не: группировки данных по теперь похожа наша подсчитывалась для каждого из существующих. В Ясно, что кто-то GUI-платформа с названием
- для расчета на по разным отделам сумму продаж поПо умолчанию в сводную решения может понадобиться В новых версиях на самый простойГруппироватьЕсли выделить одну было у давних

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

- (см. выше). отделам. В списке таблицу помещается абсолютно группировка. Допустим, нам Excel он почему-то

- вопрос: «(Group); ячейку в диапазоне пользователей DOS и получить сразу сумму, и т.д. Давайте видите, столбец
Т.е. в нашу выберем вариант –
по упорядочиванию и
Как обновить данные в сводной таблице Excel?
всего лишь мимолётноеПервый столбец = первому
Справа от сводной таблицы
- полей сводной таблицы вся информация из нужно увидеть итоги спрятано глубоко вЧто же это такое
- Появится диалоговое окно данных, то Excel Lotus 1-2-3. среднее и количество

- посмотрим, как этоCash сводную таблицу добавятсяNew Worksheet сортировке всех данных
увлечение, которое долго столбцу из сводной
- у нас была выбираем названия столбцов, того столбца, который за месяц или
- настройках: – сводные таблицы
- Группирование автоматически определит диапазонУрок подготовлен для Вас (продаж), размещённые в
делается.исчез из нее. строки с именем(На новый лист): о продажах за
Некоторые секреты форматирования
не протянет. Они таблицы. Второй – область задач, где которые нас интересуют. мы добавляем в квартал.Выберите «Файл»-«Параметры»-«Панель быстрого доступа». в Excel?(Grouping) для дат
для создания сводной командой сайта office-guru.ru
- одной сводной таблице.Для начала уберите запись
- Очевидно, что сводные таблицы каждого продавца компанииExcel создаст новый лист последние шесть месяцев отказались создавать версию формула для расчета

- мы выбирали столбцы Получаем итоги по отчет.

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

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

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

Работа с итогами
= тариф * количество Если она исчезла,

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

- несколько лет), предсказывая,

человек / показания просто щелкаем мышьюВажные нюансы:
даты, ВСЕ суммы с данными. команды». сводную таблицу.

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

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

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

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

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

- двухмерную сводную таблицу(Months). Кстати, сгруппироватьКаждый столбец в диапазоне сводную таблицу в(Торговый представитель) в а на его

- мы суммируем, не область появится ещё одноДело в том, что когда-либо будет нужно который будут заноситься
для отчета. ДляВ базовой табличке каждый
Создание сводной таблицы Excel из нескольких листов
просто загромождают отчет с соответствующим значением. сводных таблиц и Excel. И в даты и время данных должен иметь современных версиях Excel область место переместите заголовок похожи на суммы
Row Labels диалоговое окно: таблица, приведённая выше, потребителям. Microsoft, естественно, показания по счетчикам этого установим галочку столбец должен иметь и мешают сосредоточиться
Сводная таблица в Excel
Щелкаем правой кнопкой диаграмм». Нажмите на заключение мы расскажем,

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

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


- отчет.Нажимаем на стрелочку у «Группировку». Откроется инструмент переместился в правую данных, чтобы легко дням, часам и строк. более ранних версиях

сделало нашу сводную
- исправим. форму… части диалогового окна
- исходных данных, сохранённых Excel такой инструмент, сводная таблица с появляется динамика продажВ Excel в качестве
- названия столбца, где вида: колонку и нажмите извлекать нужную информацию. так далее;
Как сделать сводную таблицу из нескольких таблиц
Excel, рекомендуем статью:(Стоимость) в область таблицу бесполезной. ExcelВелик соблазн сделать привычныеВидите преимущества? За пару – это перечень где-то ещё, и как сводные таблицы, тарифами.
по дням в источника информации можно будем корректировать количество
В полях «Начиная с» ОК.
- Каждый раздел самоучителяНажмитеСводная таблица Как создать продвинутуюValues cоздал отдельный столбец в такой ситуации кликов мы создали всех заголовков из их обработка заняла которого не былоСкачать все примеры сводной



- каждом отделе. использовать таблицы Access, информации. и «По» ExcelТеперь инструмент находится в проиллюстрирован примерами сводныхОК(Pivot Table) в сводную таблицу в(Значения). для каждой даты, действия и просто

- таблицу, которую вручную исходного списка. Четыре

- минимум пару часов. в Lotus 1-2-3. таблицыСгруппируем данные в отчете SQL Server иВыбираем из выпадающего меню автоматически проставил начальную панели быстрого доступа, таблиц.

- . разделе Excel 2003?Для первого поля в которую была выделить всю таблицу пришлось бы создавать пустые области в Именно такую таблицу Сводные таблицы, эксклюзивнаяЕсли при расчете коммунальных по месяцам. Для др.

- название поля. В и конечную даты а значит всегдаПоскольку интерфейс, используемый дляКак и требовалось, нашаТаблицыВ качестве исходных данных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 знаков равно нулю. контекстного меню пункт Вы когда-либо в получена важная информация! требуется, это перетащить он выглядел бы котором их не данные в ячейках. снять галочки напротив
Для чего нужны сводные таблицы в эксель
С помощью сводной таблицы можно создавать сводки, анализировать, изучать и представлять сводные данные. Сводные диаграммы дополняют сводные таблицы, позволяя визуализировать содержащиеся в них данные. Кроме того, с помощью сводных диаграмм можно легко отображать сравнения, шаблоны и тенденции. И сводные таблицы, и сводные диаграммы помогают принимать взвешенные решения на основании критически важных данных вашей организации. Вы также можете подключаться к внешним источникам данных, например к таблицам SQL Server, кубам служб SQL Server Analysis Services, Azure Marketplace, файлам подключения к данным Office (ODC-файлам), XML-файлам, базам данных Access и текстовым файлам, чтобы создавать сводные таблицы, или создавать на основе сводных таблиц другие таблицы.
Сводная таблица — это интерактивный способ быстрого обобщения больших объемов данных. Вы можете использовать ее для подробного анализа числовых данных и для получения ответов на разнообразные вопросы, связанные с данными. Сводные таблицы предназначены для выполнения следующих задач:
запрос больших объемов данных различными понятными способами;
подведение промежуточных итогов и вычисление числовых данных, обобщение данных по категориям и подкатегориям, создание пользовательских вычислений и формул;
развертывание и свертывание уровней представления данных для получения точных сведений о результатах, детализация итоговых данных по интересующим вопросам;
перемещение строк в столбцы или столбцов в строки («сведение») для просмотра различных сводок на основе исходных данных;
фильтрация, сортировка, группировка и условное форматирование наиболее важных и часто используемых подмножеств данных для привлечения внимания к нужным сведениям;
представление кратких, наглядных отчетов с примечаниями на веб-страницах или в напечатанном виде.
Например, слева показан простой список расходов семьи, а справа — созданная на его основе сводная таблица.
Данные о продажах
Соответствующая сводная таблица


После того как вы создадите сводную таблицу, выбрав для нее источник данных, упорядочив поля в списке полей сводной таблицы и выбрав исходный макет, вы сможете при работе с ней выполнять перечисленные ниже задачи.
Изучать данные. Вот какие действия доступны.
Развертывание и свертывание данных, а также отображение базовых сведений, которые относятся к значениям.
Сортировка, фильтрация и группировка полей и элементов.
Изменение функций сведения и добавление настраиваемых вычислений и формул.
Изменять макет формы и расположения полей. Вот какие действия доступны.
Изменение формы сводной таблицы: сжатая форма, структура или табличная форма.
Добавление, изменение порядка и удаление полей.
Изменение порядка полей или элементов.
Изменение макета столбцов, строк и промежуточных итогов Вот какие действия доступны.
Включение и отключение заголовков полей строк и столбцов, а также отображение или скрытие пустых строк.
Отображение промежуточных итогов выше или ниже их строк.
Настройка ширины колонок при обновлении.
Перемещение поля столбца в область строк или поля строки в область столбцов.
Объединение или отмена объединения ячеек для внешних элементов строки и столбца.
Изменять способы отображения пустых значений и ошибок. Вот какие действия доступны.
Изменение способа отображения ошибок и пустых ячеек.
Изменение способа отображения элементов и подписей без данных.
Отображение и скрытие пустых строк.
Изменять формат. Вот какие действия доступны.
Ручное и условное форматирование ячеек и диапазонов.
Изменение стиля общего формата сводной таблицы.
Изменение числового формата для полей.
Включение форматирования сервера OLAP.
Сводные диаграммы служат для графического представления данных, содержащихся в сопоставленных с ними сводных таблицах. Как и сводные таблицы, сводные диаграммы — интерактивные объекты. При создании сводной диаграммы на экране отображается область фильтра сводной диаграммы. С помощью этой области вы можете сортировать и фильтровать исходные данные, используемые в сводной диаграмме. Изменения макета и данных в связанной сводной таблице немедленно отражаются в макете и данных в сводной диаграмме и наоборот.
Как и в обычных диаграммах, в сводных диаграммах отображаются ряды данных, категории, маркеры данных и оси. Кроме того, вы можете изменить тип диаграммы и другие параметры, например названия, расположение условных обозначений, метки данных, расположение диаграммы и т. д.
Вот сводная диаграмма, основанная на приведенном выше примере сводной таблицы.

Дополнительные сведения см. в статье Создание сводной диаграммы.
Если вы знакомы со стандартными диаграммами, то работа со сводными диаграммами не будет для вас проблемой, так как большинство действий для этих двух типов диаграмм полностью совпадают. Тем не менее существуют и некоторые отличия.
Ориентация строк и столбцов В отличие от обычной диаграммы в сводной диаграмме вам не удастся изменить ориентацию строк и столбцов с помощью диалогового окна Выбор источника данных. Вместо этого вы можете изменить ориентацию меток строк и столбцов в связанной сводной таблице.
Типы диаграмм Для сводной диаграммы вы можете выбрать любой тип диаграммы, кроме точечной, биржевой и пузырьковой.
Исходные данные Обычные диаграммы связаны непосредственно с ячейками листа. Сводные диаграммы основаны на источнике данных сопоставленных с ними сводных таблиц. В отличие от обычной диаграммы в сводной диаграмме вам не удастся изменить диапазон данных диаграммы с помощью диалогового окна Выбор источника данных.
Форматирование Большинство параметров форматирования, включая добавленные вами элементы диаграммы, макет и стиль, сохраняются при обновлении сводной диаграммы. Тем не менее линии трендов, метки данных, пределы погрешностей и другие изменения, вносимые в наборы данных, не сохраняются. В обычных диаграммах после применения такого форматирования оно не исчезает.
Несмотря на то что вам не удастся напрямую изменить размер меток данных в сводной диаграмме, вы можете увеличить размер шрифта и таким образом изменить размер меток.
В качестве основы для сводной таблицы или сводной диаграммы вы можете использовать данные листа Excel. Данные должны представлять собой список, в первой строке которого содержатся метки столбцов. Приложение Excel использует эти метки в качестве имен полей. Каждая ячейка в последующих строках должна содержать данные, соответствующие заголовку столбца. Не следует использовать данные различных типов в одном и том же столбце. Например, в одном и том же столбце не следует использовать денежные значения и даты. Кроме того, в выбранном вами диапазоне данных не должно быть пустых строк или столбцов.
Таблицы Excel Таблиц Excel изначально представлены в виде списка и отлично подходят в качестве исходных данных для сводной таблицы. При обновлении сводной таблицы новые и обновленные данные из таблицы Excel автоматически включаются в операцию обновления.
Использование динамического именованного диапазона Чтобы было проще обновлять сводную таблицу, вы можете создать динамический именованный диапазон и использовать его имя в качестве источника данных для сводной таблицы. Если вы расширите именованный диапазон, чтобы включить в него дополнительные данные, просто обновите сводную таблицу, и в нее будут включены эти новые данные.
Включение итогов Excel автоматически создает промежуточные и общие итоги в сводной таблице. Если источник данных содержит автоматически вычисляемые промежуточные и общие итоги, которые вы создали с помощью команды Промежуточный итог в группе Структура на вкладке Данные, то с помощью этой же команды удалите промежуточные и общие итоги, прежде чем создавать сводную таблицу.
Вы можете получить данные из внешнего источника данных, например базы данных, куба OLAP или текстового файла. Например, у вас может быть база данных со сведениями о продажах, для которых вы хотите создать сводку и которые вам необходимо проанализировать.
Файлы подключения к данным Office Если вы используете файл подключения к данным Office (ODC-файл) для получения внешних данных для сводной таблицы, вы можете вводить данные непосредственно в нее. Рекомендуется получать внешние данные для отчетов с помощью ODC-файлов.
Источник данных OLAP Когда вы получаете исходные данные из базы данных OLAP или файла куба, данные возвращаются в Excel только в виде сводной таблицы либо сводной таблицы, преобразованной в функции листа. Дополнительные сведения см. в статье Преобразование ячеек сводной таблицы в формулы листа.
Источник данных, не являющийся источником данных OLAP Это базовые данные для сводной таблицы или сводной диаграммы, поступающие из источника, отличного от базы данных OLAP. Например, это могут быть данные из реляционных баз данных или текстовых файлов.
Кэш сводной таблицы Каждый раз при создании новой сводной таблицы или сводной диаграммы Excel сохраняет копию данных для отчета в памяти, а также сохраняет эту область хранения данных в файле книги — это называется кэш сводной таблицы. Для каждой новой сводной таблицы требуется дополнительный объем памяти и место на диске. Однако при использовании существующей сводной таблицы в качестве источника данных для новой таблицы в той же книге обе таблицы будут использовать один и тот же кэш. Благодаря повторному использованию кэша снижается размер книги и сокращается объем данных, хранящихся в памяти.
Требования к расположению Чтобы использовать одну сводную таблицу в качестве источника данных для другой сводной таблицы, обе они должны находиться в одной и той же рабочей книге. Если исходная сводная таблица находится в другой рабочей книге, скопируйте источник в рабочую книгу, в которой вы хотите создать другую сводную таблицу. Сводные таблицы и сводные диаграммы, находящиеся в разных рабочих книгах, — это отдельные объекты, для каждого из которых имеется собственная копия данных в памяти и в рабочих книгах.
Изменения влияют на обе сводные таблицы Когда вы обновляете данные в новой сводной таблице, Excel также обновляет данные в исходной сводной таблице и наоборот. Когда вы группируете элементы или отменяете их группировку либо создаете вычисляемые поля или вычисляемые элементы в одной таблице, изменения будут внесены в обе таблицы. Если вам необходимо, чтобы одна сводная таблица не зависела от другой, создайте сводную таблицу на основе исходного источника данных, а не копируйте исходную сводную таблицу. Просто имейте в виду, что если выполнять такие операции слишком часто, это может сказаться на объеме свободной памяти.
Сводные диаграммы Вы можете создать сводную таблицу или сводную диаграмму на основе другой сводной таблицы, но вам не удастся создать сводную диаграмму непосредственно на основе другой сводной диаграммы. Изменения, вносимые в сводную диаграмму, влияют на связанную сводную таблицу и наоборот.
Изменение исходных данных может привести к тому, что для анализа будут доступны другие данные. Например, вам может быть необходимо удобно переключаться из тестовой базы данных в рабочую базу данных. Переопределив исходные данные, вы можете обновить сводную таблицу или сводную диаграмму, используя данные, структура которых похожа на структуру информации, получаемой из исходного подключения к данным. Если эти данные значительно отличаются, и в них имеется большое количество новых или дополнительных полей, возможно, будет проще создать другую сводную таблицу или сводную диаграмму.
Отображение данных, полученных при обновлении При обновлении сводной таблицы могут быть изменены данные, доступные для отображения. Для сводных таблиц, основанных на данных листа, Excel получает новые поля, имеющиеся в указанном вами исходном диапазоне или именованном диапазоне. Для отчетов, основанных на внешних данных, Excel получает новые данные, которые соответствуют критериям для базового запроса или данных, которые становятся доступными в кубе OLAP. Вы можете просматривать все новые поля в списке полей и добавлять необходимые поля в отчет.
Изменение создаваемых кубов OLAP У отчетов, основанных на данных OLAP, всегда есть доступ ко всем данным в кубе. Если вы создали автономный куб, который содержит подмножество данных, имеющихся в кубе сервера, с помощью команды Автономный режим OLAP вы можете изменить файл куба так, чтобы он содержал другие данные с сервера.
Как использовать сводные таблицы Excel в КДП
Прежде чем разбираться в нюансах применения, выясним, что такое сводная таблица в Excel. В общем случае под этим термином понимают особый инструмент стандартной версии пакета MS Office — Excel, позволяющий выбрать из массива данных необходимые сведения и совершить с ними простые арифметические действия. Допустим, мы имеем массив следующего вида, иллюстрирующий работу фирмы по продаже оргтехники:

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

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

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

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

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

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

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

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

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


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


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

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

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

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

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

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


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

На основе таких отчетов удобно строить диаграммы для графического отображения информации.
Как построить сводные таблицы в Excel, LibreOffice, OpenOffice и таблицах Google
В предыдущих публикациях я поделился информацией о том, как импортировать данные из Google Analytics в различные процессоры электронных таблиц (мы рассматривали импорт в Google таблицы и Excel). Но импорт — это только первый шаг на пути к полноценному анализу данных. После загрузки всей необходимой вам информации в редактор электронных таблиц, ее необходимо визуализировать и только потом анализировать.
Самый удобный и быстрый инструмент для преобразования массива данных в информативный отчет — сводные таблицы. В основе сводных таблиц лежит технология OLAP, описанная в 1993 году известным исследователем баз данных и автором реляционной модели данных Эдгаром Коддом. По сути, сводная таблица — это инструмент обработки данных для их группировки и обобщения.
Любая сводная таблица строится на основе определенной базы данных. В виде базы данных выступает массив, состоящий из полей и записей. Каждая строка (запись) в базе данных представляет собой информацию об отдельном случае, объекте или состоянии изучаемого объекта, а каждый столбец (поле) является параметром, свойством или признаком всех исследуемых объектов. Например, параметром может быть источник трафика, описание информации об источнике. В поле «источник трафика» будет, например, Google. Все поля базы данных разделяются на два типа: измерения («параметры» в терминологии Google Analytics) и меры («показатели» в терминологии Google Analytics). Измерением — это название или свойство объекта, в разрезе которых мы можем анализировать различные количественные показатели.
Каждое измерение содержит ряд элементов. Например, элементами измерения «Источник трафика» будут: Google и любые другие возможные источники трафика. Мерой являются количественные показатели, которые мы сравниваем между элементами измерений. Например: количество кликов, количество транзакций, сумма дохода. С общим понятием, что такое сводная таблица, мы ознакомились. Далее рассмотрим, как строятся сводные таблицы в различных процессорах электронных таблиц.
Безусловный лидер в реализации всего возможного функционала сводных таблиц — Microsoft Excel. По функциональным возможностям с этим инструментом могут соревноваться только такие гиганты BI индустрии, как QlikView и Tableau, но в связи с тем, что данные платформы достаточно дорого стоят и в русскоязычном сегменте пока не успели получить особую популярность, в этой статье рассматриваться не будут.
Для построения сводных таблиц в описанных ниже мануалах используется одна и та же импровизированная база данных, состоящая из выгруженной из Google Analytics информации. Вы можете скачать эту таблицу по ссылке.
Сводные таблицы в Microsoft Excel 2013
Для построения сводной таблицы в Microsoft Excel 2013 вам необходимо открыть скачанный ранее csv файл. После чего, установив курсор на любой из ячеек таблицы, нажмите Ctrl+A — этим действием вы выделите всю базу данных. На её основе мы будем строить сводную таблицу. Далее перейдите на вкладку «Вставка» и в группе «Таблицы» нажмите «Сводная таблица».

В открывшимся диалоговом окне «Создание сводной таблицы» ничего не меняем и жмем ОК.
На этом процесс создания закончен и мы приступаем к работе со сводной таблицей. После создания таблицы в книге Excel будет создан новый лист. Он будет выглядеть так:

Как вы видите на скриншоте, этот лист состоит из самой сводной таблицы и конструктора сводной таблицы. Конструктор состоит, в свою очередь, из пяти частей:
- список полей;
- фильтры;
- колонны;
- строки;
- значения.
Для того, чтобы понять принцип работы сводной таблицы, давайте произведем следующие действия в конструкторе сводной таблицы:
- Поле Date с помощью правой кнопки мыши перетащим в область строк.
- Поле Device category перетащим в область колонн.
- Поле User type — в область фильтров.
- Поле Session — в область значений.
В итоге должно получиться так:

В результате четырех произведенных действий мы построили перекрестную таблицу, которую в Google Analytics построить нельзя. В строках мы видим даты, а по столбцам у нас располагается информация о количестве сеансов за определенную дату по каждому типу устройств. Это получилось, потому что мы перенесли поле Date в область строк. В отчете сводной таблицы в каждой строке появилась определенная дата.
После того, как мы перетащили поле Device category в область колонн, каждый столбец сводной таблицы получил название одного из существующих в базе типов устройств.
Перенеся поле Sessions в область значений, мы заполнили область значений сводной таблицы информацией о количестве сеансов за каждый день по каждому типу устройств. В полях, которые были добавлены в область значений конструктора сводной таблицы, можно изменять агрегирующую функцию. По умолчанию для числовых полей применяется суммирование, но вы можете выбрать любую другую из предложенных агрегирующих функций. Для этого достаточно в области значений сводной таблицы кликнуть правой кнопкой мыши и в выпадающем меню «Итоги по» выбрать нужную функцию.

Остается вопрос: а что же нам дал перенос поля User type в фильтры? Чтобы это понять, давайте применим фильтр сводной таблицы и выведем в отчет информацию только по новым пользователям.
- Откройте перечень элементов поля User type, нажав на ярлык с изображением воронки в области фильтров сводной таблицы.
- Выберите элемент New user.
- Нажмите ОК.

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

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

Такие иерархии можно строить и в области колонн. Вы можете смотреть информацию на уровне недель и при необходимости детализировать информацию по количеству сеансов до уровня дат простым нажатием на иконку +/−.

Очень полезная функция сводных таблиц в Microsoft Excel — возможность дополнительных вычислений над любыми выведенными в таблицу показателями.
Например, мы хотим посмотреть не количество сеансов за каждую дату в разбивке по типам устройств, а какую долю сеансов в каждой дате занимает каждый тип устройств. Для этого достаточно кликнуть правой кнопкой мыши в области значений сводной таблицы, и в контекстном меню выбрать пункт «Дополнительные вычисления» => «% от суммы по строке».

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

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

После этого появится диалоговое окно «Вставка вычисляемого поля», в котором необходимо дать название вашему вычисляемому полю. В данном случае наиболее подходящим будет «Показатель отказов». Заполнив название нужных полей сводной таблицы, напишите формулу, по которой будет рассчитываться наш показатель. При написании формул вы можете использовать все существующие формулы Microsoft Excel.
Единственное отличие от использования функций при создании вычисляемого поля и рабочего листа заключается в том, что при написании формулы в ячейке рабочего листа вы ссылаетесь на адреса нужных вам ячеек (например, A1), а при написании формулы вычисляемого поля сводной таблицы вы ссылаетесь на название полей сводной таблицы.
В нашем случае формула вычисляемого поля будет выглядеть как «=Bounces / Sessions». Дальше жмем OК.

В область значений отчета сводной таблицы будет добавлено поле «Показатель отказов»:

После добавления нового расчетного поля в таблице были автоматически созданы четыре новые колонны. И если сейчас посмотреть на конструктор сводной таблицы, мы увидим, что в область «Колонны» добавлено поле «Значения».

Это поле появляется автоматически, если в область «Значения» добавлено более одного поля. Вы можете перенести поле «Значения» в строки или колонны, в область фильтров и значений данное поля перенести нельзя.
По сути, это поле содержит название всех показателей (полей), выведенных в область значений. Для закрепления материала и преобразования сводной таблицы в более читабельный вид перетянем поле «Значения» в область строк под поле Date.

Теперь в таблице каждая дата разбита на две строки. В первой выводится доля сеансов по каждому типу устройства, во второй — показатель отказов для каждого типа устройств. Иногда в ходе анализа нам требуется каким-либо образом объединить некоторые элементы измерения в группы. Например, в данном случае нам может понадобиться объединить типы устройств mobile и tablet в одну группу и назвать ее Other.
Таким образом мы можем проанализировать различие между настольными устройствами и всеми остальными. Все, что нам необходимо сделать для группировки измерения, — это выделить нужные его элементы с помощью мыши, после чего вызвать контекстное меню правым кликом мыши и выбрать пункт «Группировать».

Элементы mobile и tablet будут объединены в группу, которой по умолчанию присваивается название «Группа 1». Чтобы переименовать группу, просто перейдите в ячейку с названием и введите новое Other.

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

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

Также существует возможность добавления срезов и временных шкал. Хочу заметить, что этот функционал не доступен в старых версиях Microsoft Excel, возможность добавления срезов появилась в 2010 версии, а временные шкалы добавили только в 2013 году. Срез выполняет ту же функцию что и фильтр, который мы построили, добавив поле User type при построении сводной таблицы в область фильтров.
Единственное отличие заключается в том, что срез имеет более удобную визуализацию. Давайте добавим срез по Source. Для этого выделите любую ячейку, относящуюся к области отчета сводной таблицы, перейдите на вкладку «Анализ» и в группе «Фильтры» нажмите иконку «Вставить срез».

Откроется диалоговое окно «Вставка срезов», в котором вам необходимо выбрать, по каким полям вы хотите создать срезы. В нашем случае это поле Source.

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

Зажав левый Ctrl, вы можете выбрать любое количество элементов среза, информацию по которым планируете вывести в отчет. Временная шкала работает по такому же принципу, как и срез, но строить ее можно только на основе полей, содержащих данные в формате даты. Создается она на вкладке «Анализ» с помощью кнопки «Вставить временную шкалу», находящейся в группе «Фильтры».
С помощью временной шкалы очень удобно выбирать период, за который мы хотим вывести данные в отчет сводной таблицы. Например, мы с помощью нескольких кликов можем вывести в отчет информацию только за август.

Функциональные возможности работы со сводными таблицами в Microsoft Excel 2013 выходят далеко за пределы описанного выше функционала, и в рамках одной статьи осветить все не получится.
Для тех, кто решил всерьез углубиться в изучение этого инструмента, советую ознакомиться с материалами, изложенными одним из ведущих специалистов по программному обеспечению электронных таблиц Биллом Джеленом в своей книге «Сводные таблицы в Microsoft Excel 2013» (если интересно, у меня есть электронная версия «Сводных таблиц в Microsoft Excel 2010»).
Сводные таблицы в Google Spreadsheets (Google таблицы)
Основное преимущество Google Spreadsheets — доступ к ним есть всегда и везде при условии наличия подключения к интернету. Именно это делает продукт наиболее удобным при совместной работе нескольких пользователей. Для построения сводной таблицы в Google Spreadsheets необходимо создать новую таблицу в своем Google Диске, перейдя по этой ссылке.

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

В открывшемся диалоговом окне переходим на вкладку «Загрузка» и жмем кнопку «Выберите файл на компьютере», после чего выбираем скачанный ранее файл pivotTableDB.csv.

В диалоговом окне «Импорт файла» устанавливаем переключатель «Действие после импорта» в положение «Заменить текущий лист», а переключатель «Разделитель» устанавливаем в положение «Другое». В качестве разделителя вводим точку с запятой.

После нажатия кнопки «Импортировать» данные будут загружены в вашу таблицу на «Лист1». Теперь можно приступить непосредственно к созданию сводной таблицы. Для этого надо выделить весь загруженный массив данных. Наиболее быстрым способом в этом случае будет перейти в ячейку A1 и последовательно использовать сочетание клавиш Ctrl + Shift + Стрелка вправо, затем Ctrl + Shift + Стрелка вниз.
Далее переходим в меню «Данные» и жмем на пункт «Сводная таблица».

Далее в Google таблице, так же как и в Microsoft Excel, будет создан новый лист с названием «Сводная таблица 1», областью сводной таблицы и редактором отчетов.

Редактор отчет так же состоит из четырех областей: «Строки», «Столбцы», «Значения», «Фильтры». Давайте пройдем путь построения сводной таблицы, описанный в примере выше. Для этого произведем те же четыре действия.
- В область «Строки» добавляем поле Date.
- В область «Столбцы» добавляем поле Device Category.
- В область «Значения» добавляем поле Sessions.
- В область «Фильтры» добавляем поле User type.

Сводная таблица приобрела знакомый из описания Microsoft Excel вид:

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

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

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

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

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

Далее необходимо ввести название расчетного поля и формулу. В нашем случае названием поля будет «Показатель отказов», а формулой для расчета «=bounces / sessions».
В отличие от Microsoft Excel, в данном случае названия полей в формуле расчета можно ввести только с клавиатуры. Это важно.

Теперь сводная таблица имеет следующий вид:

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

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

На этом функциональные возможности Google таблиц заканчиваются. В целом, этого достаточно для построения визуализации данных для проведения анализа.
Сводные таблицы в LibreOffice и OpenOffice
LibreOffice — бесплатный, десктопный процессор электронных таблиц. По функционалу возможности сводных таблиц LibreOffice и OpenOffice значительно уступают Microsoft Excel, но для решения большей части задач они вполне сгодятся. Процедуры построения сводных таблиц в LibreOffice и OpenOffice совершенно одинаковые, в связи с чем нет смысла описывать все этапы по отдельности. Поэтому в данном случае в качестве примера возьмем LibreOficce, но в OpenOffice с помощью совершенно идентичных действий вы можете создать такую же сводную таблицу.
Для создания сводной таблицы в меню «Файл» LibreOffice выберите пункт «Открыть», после чего укажите скачанный ранее файл pivotTableDB.csv. В диалоговом окне «Импорт текста» установите переключатель «Параметры разделителя» в положение «Разделитель», и в качестве разделителя установите «Точка с запятой».

После нажатия ОК необходимая таблица данных будет загружена в документ. Теперь, когда у вас есть база данных, необходимо выделить ее нажатием Ctrl+A, и в в группе «Сводная таблица» (меню «Данные») нажать кнопку «Создать».

В качестве источника в диалоговом окне «Выбрать источник» устанавливаем переключатель в положение «Выбранное выделение». Нажимаем ОК.

Далее появится диалоговое окно «Разметка сводной таблицы». Это своеобразный конструктор сводной табицы из приведенных выше примеров с Microsoft Excel и Google Spreadsheets. Чтобы построить сводную таблицу, аналогичную двум предыдущим примерам, сделайте следующие действия.
1. В область «Поля страниц» перетащите поле User type, так как в LibreOffice область «Поля страниц» — это область фильтров сводной таблицы.
2. В область столбцов перенесите поле Device category. По умолчанию область столбцов уже будет содержать поле с именем «Данные». С помощью этого поля вы можете изменять положение рассчитываемых метрик. Примерно так же, как мы делали это в Excel и Google Таблицах. Тогда мы располагали информацию так, чтобы каждая дата содержала две строки: одну с данными о сессиях, а вторую — о показателе отказов. Так же и в LibreOffice вы можете менять расположение вычисляемых данных, отображая их в строках или столбцах.
3. В область «Поля строк» перенесите сначала поле Week, после чего под этим полем расположите поле Date.
4. В область «Поля данных» перетащите поле Sessions. Для того, чтобы изменить агрегирующую функцию либо настроить дополнительное вычисление для рассчитываемого поля, достаточно дважды кликнуть на него левой кнопкой мыши и выбрать из списка нужную функцию либо дополнительное вычисление. Ранее мы в примере с Microsoft Excel устанавливали в качестве дополнительного вычисления «Процент от суммы по строке». Чтобы настроить подобное вычисление в LibreOffice после двойного клика по полю Sessions, расположенного в области данных, в открывшемся диалоговом окне «Поле данных» раскройте меню «Отображаемое значение», установите «Тип: % от строки» и нажмите ОК.

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

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

Заключение
Предлагаю сравнить функционал Microsoft Excel, Google Spreadsheets и OpenOffice по работе со сводными таблицами.

В данной таблице перечислен только тот функционал, который был описан в данной статье. Относительно Microsoft Excel, это даже не половина всех существующих возможностей по построению и использованию сводных таблиц.
Сводные таблицы — самый удобный инструмент для анализа больших массивов данных. Время, инвестируемое в изучение сводных таблиц, с лихвой окупится в будущем, когда вы за считанные минуты сможете из массива данных, насчитывающего десятки, а в некоторых случаях и сотни тысяч строк извлекать необходимую для анализа информацию.
Автор и преподаватель курса «Язык R для интернет — маркетинга».
Автор расширений (пакетов) для языка программирования R: rgoogleads, rfacebookstat, rytstat, rym и других.
Автор телеграмм канала R4marketing, который посвящён применению языка R в задачах интернет — маркетинга. Контент канала состоит из новостей, статей, докладов, вебинаров и релизов новых версий пакетов.