Содержание:
Для кого эта статья?
Целевая аудитория данной статьи - граждане, которые плохо ли, хорошо ли, но работают в Excel, однако, боятся замахнуться на Вильяма нашего Шекспира сводные таблицы. Причин такой ситуации может быть несколько:
Кто-то вам сказал, что это нечто, сравнимое по сложности понимания с Общей теорией относительности А.Эйнштейна, и, что лучше, наверное, держаться от этого подальше, чтобы не подрывать свою "Великую веру в себя". Вам наплевать на Excel вообще и на сводные таблицы в частности, но завтра у вас собеседование на хорошую позицию в хорошем месте и надо произвести неизгладимое впечатление своей осведомленностью по поводу этой сакральной возможности, которая почему-то так люба работодателям. Вы лишь начинаете осваивать Excel и только-только созрели для этого нового чудного мира, который откроет перед вами знание сводных таблиц. Я так и знал, что это именно ваш пункт!
▲ вверх
Наша задача
Моя задача, как можно более кратко, просто, доходчиво и не скучно рассказать про сводные таблицы (pivot tables, пивотные таблицы). Я надеюсь, что по прочтении этого, вы, как минимум, перестанете их бояться и приступите к смелым экспериментам над своими данными, потому что сводные таблицы, это как раз то, что можно осваивать методом тыка. То, что функционал сводных таблиц сложен для понимания - это не более чем миф. Сводные таблицы - настолько же интуитивно-понятный инструмент, насколько и мощный. Всё, что необходимо - это знать чуть-чуть теории и разбираться в 2-3 окнах настройки. Вперёд!
▲ вверх
Учебная ситуация
Представьте, что вы владелец четырёх торговых точек по продаже цветов. У вас по точке на севере, юге, западе и востоке вашего городка. На вас работают 8 продавщиц. Вы продаёте 5 видов цветов: розы, гвоздики, тюльпаны, лилии и хризантемы. Весь 2014 год вы продавали цветы и вот 3 января 2015 года вы открыли журнал продаж, который вели весь год, и решили понять, кто у вас слабое звено кому выплатить годовую премию, да и вообще уяснить, как шёл бизнес.
▲ вверх
Журнал продаж цветов
▲ вверх
5 шагов
Внимательно вглядитесь в журнал учёта продаж цветов.
Имеем 5 столбцов 3 из них содержат текстовую информацию: точка, имя продавца, тип цветов 1 столбец содержит даты продаж 1 столбец содержит числа - стоимость проданных цветов
Обратите внимание, что один из столбцов содержит числовую информацию, которую можно суммировать. Теперь следите за руками:
Шаг 1. Скроем столбцы A:D, оставим только столбец E - Стоимость. В ячейку E1002 введём формулу суммы, а строки 2:1001 скроем. Получится вот это:
Таким образом мы узнали? что за весь год, всеми точками, всеми продавцами, всех видов цветов продано на 1063100 рублей. Мы пока не говорим ни о каких сводных таблицах, а просто пытаемся анализировать обычную таблицу.
Шаг 2. Будучи владельцем бизнеса, вы конечно же не остановитесь на шаге 1, узнав лишь общую сумму. Наверняка вам захочется увидать СЛАГАЕМЫЕ этой суммы, то есть её СТРУКТУРУ. OK! Давайте посмотрим, как продавали точки в сравнении друг с другом! Для этого мы опять скроем всё лишнее, оставив только столбцы Точка и Стоимость, и при помощи формул СУММЕСЛИ относительно легко получим такую статистику:
Посмотрите те ка, на южной стороне города мужчины чаще дарят женщинам цветы!
Шаг 3. А кто у нас передовик капиталистического труда? А вот кто:
Шаг 4. А какие цветы приносят больше денег? Посмотрим:
Шаг 5. А в каком месяце максимальные продажи? Странная картина, ну да ладно...
Утомительно, не правда ли? И заметьте, что действия ТИПОВЫЕ!
Так вот, для того, чтобы НИКОГДА БОЛЬШЕ не заниматься этими пятью позорными шагами, существуют сводные таблицы.
▲ вверх
Измерения
Я надеюсь, что вы заметили, что в 4-х из 5-ти шагов мы работали со столбцами, по которым ГРУППИРОВАЛИ текстовые данные (или даты) и суммировали стоимость цветов. Такие столбцы, по которым можно группировать строки давайте называть ИЗМЕРЕНИЯМИ. Наш журнал продаж имеет 4 измерения: торговая точка, имя продавца, вид цветов и дата продажи. Столбец Стоимость можно тоже считать измерением, но мы, чтобы не путаться будем называть его числовым полем.
▲ вверх
Создаём сводную таблицу
Давайте создадим сводную таблицу, благо дело это крайне простое:
Встаньте на любую ячейку нашей таблицы на листе Журнал_продаж На ленте ВСТАВКА нажмите крайнюю левую кнопку Сводная таблица Нажмите в возникшем диалоговом окне OK. В этом диалоге можно изменить диапазон нашей таблицы, который Excel отлично определяет самостоятельно, а также можно указать, куда вставлять сводную таблицу - рекомендую выбирать всегда отдельный новый лист, что и соответствует настройке по умолчанию.
▲ вверх
Момент истины
А теперь я при помощи сводной таблицы за 10 секунд повторю наши 5 шагов, на которые мы потратили минут 10.
Впечатляет, не так ли?
▲ вверх
Теперь по порядку...
Итак, что мы видим на главном экране управления сводной таблицой:
Секция Поля сводной таблицы - содержит не что иное, как заголовки столбцов нашей исходной таблицы. Поле или измерение - суть синонимы. Термин "поле" пришёл к нам из баз данных. В сводных таблицах операции с полями означают действия со всеми строками данного столбца, из чего, собственно, и проистекает вся мощь этого инструмента.
Раздел СТРОКИ - сюда перетаскивают поля, сгруппированные значения которых должны располагаться по вертикали. Пример:
Раздел КОЛОННЫ - сюда перетаскивают поля, значения которых должны располагаться горизонтально. Пример:
Раздел ЗНАЧЕНИЯ - сюда перетаскивают поля, которые можно суммировать. Суммировать можно только числовые поля, а вот подсчитать количество строк можно по любому полю, поэтому, если вы в данный раздел перетащите какое-либо поле измерения (например, Продавец), то Excel автоматически поймёт, что его надо подсчитывать (count), а не суммировать (sum).
Раздел ФИЛЬТРЫ - сюда перетаскиваем поля, по которым не надо группировать значения столбцов и суммировать, но надо отфильтровать строки, чтобы в дальнейшей группировке и суммировании участвовали только строки, удовлетворяющие условию на значение фильтра. Посмотрите на примере:
▲ вверх
Раз пошла такая пьянка...
А что это мы используем только одно измерение? Как насчёт двух?
или так...
А чего это у нас таблица такая блёклая? Добавим красок и ещё усложним:
- Двойная группировка по дате в колоннах(квартал + месяц)
- Тройная группировка по точке, продавцу и цветам в строках
Не хотите попробовать сделать такое формулами? А сводными таблицами это делается за 5 секунд!
А тут мы находим среднюю стоимость букета по продавщицам. Схема объясняет некоторые нюансы настройки.
▲ вверх
Выводы
Сводные таблицы - самый мощный и востребованный инструмент в Excel. Анализировать данные со сводными таблицами в десятки раз быстрее и проще, чем без оных. Миф о сложности освоения этого инструмента - всего лишь миф. Для первого раза и дальнейших самостоятельных экспериментов вы узнали достаточно! Не поленитесь - закрепите полученные знания на практике.
Дальнейшие нюансы работы со сводными таблицами будут обсуждаться в других статьях, которые будут опираться на эту. До встречи!
▲ вверх
Читайте также:
|
0
