Что может сводная таблица (часть 2)

1. Введение, что же такое «СВОДНАЯ ТАБЛИЦА».

В ЧАСТИ 1 мы познакомились с тем, что такое «Плоская таблица», или более точное определение «плоская многомерная таблица». Именно к «плоской многомерной таблице» мы можем применить инструмент «сводная таблица» в Excel.

Что же может «сводная таблица»? Это навык, где практика в 100 раз важнее теории, поэтому к статье приложен файл в Excel, в котором можно будет попробовать все, что здесь описано.

Для начала нам нужна «плоская таблица». (см. Рисунок 1)

Рисунок 1

Эта таблица продаж. Она показывает:

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

Посмотрите на таблицу… Представьте, что в ней 100 тысяч записей (строк). И вам нужно найти какие-то конкретные продажи. Если тратить на каждую строку в течении 5 секунд, то понадобится 17 рабочих дней, чтобы просмотреть все строки. Конечно, так никто не делает, и все пользуются… правильно, «фильтром» в Excel.

Выделяем область, начиная с названия колонок, нажимаем Данные/Фильтр. (Рисунок 2).

Рисунок 2

После этого сверху таблицы появляются значки "треугольник вниз". Если нажать на них, то система предложить отметить те значения, которые мы хотим отобрать в таблице. См. (Рисунок 3),

Допустим, нам нужно хочется увидеть продажи только Москвы. Нажимаем на "треугольник вниз" в колонке ФИЛИАЛ убираем все галочки (снимаем «галочку» «(Выделить все)») и ставим галочку только Москва. Нажимаем и смотрим, что получилось.

Рисунок 3

Смотрим и видим, что, Excel отобрал только те записи, где есть филиал Москва. (Рисунок 4). На эту таблицу мы можем продолжать накладывать фильтры еще и еще.

Рисунок 4

Например, отберем дополнительно Регион продаж: Москва, Якутия, Юг России, и Производителей, все, кроме Испании. Оба раза нажимаем OK. (см. Рисунок 5)

Рисунок 5

Смотрим, все что получилось (Рисунок 6). Произнесем словами: «мы выбрали (отобрали) только те записи (те продажи), которые произвел филиал Москва, при этом только продажи в регионах Москва, Якутия, Юг России и исключили производителя товара из Испании. Ну, или другими словами: «Мы отобрали продажи филиала Москва в регионы Москва, Якутия, Юг России, товара всех производителей коме кроме Испании». Теперь, можем посчитать, сумму этого товара (100+20+450=570). И мы впрямую подошли к тому, что делает сводная таблица.

Рисунок 6

2. Четыре вещи, которые делают СВОДНЫЕ ТАБЛИЦЫ

Первое и самое основное свойство, «сводная таблица» может отбирать и складывать однотипные записи (то, что мы сделали вручную) посчитав (100+20+450=570). (Рисунок 6).

Это можно представить себе в виде сужающейся трубы. Диаметр всей трубы – это вся таблица. С каждым следующим отбором мы уменьшаем ее диаметр: филиал только Москва, регионы только Дальний Восток, Москва, Якутия, производители – все, кроме Испании, и в конце мы считаем значения: сумму, произведение, среднее, максимум и минимум, и прочее. (см. Рисунок 7). Но сила «сводной таблицы» не в том, что она может сосчитать какие-то отдельные комбинации полей, а в том, что … она показывает всю картину в целом… Она считает много вариантов таких «труб-запросов» к данным.

Рисунок 7

Итак, смотрим картину в целом... (Рисунок 8). Посмотрите, насколько наглядной для восприятия получилась таблица! Кто, кому, сколько, в каком периоде отгрузил продукции. Поля, и их расположение вы выбираете сами, вы можете поиграться данными и получить наиболее информативный вид, и при этом перед вами (так бывает в большинстве случаев) вы увидите какую-то удивительную закономерность, либо какое-то несоответствие … Так бывает в 95% случаев (из моей практики).


Если прямо здесь вы начнете тренироваться, то 1. Выделяйте «Плоскую таблицу» 2. Нажимайте Вставка/Сводная таблица/ОК.

Рисунок 8

Тривиально, что разные задачи потребуют разных наборов и расположений полей. Давайте посмотрим некоторые варианты Сводных таблиц. (Рисунок 9). Посмотрите в каждую таблицу и попробуйте понять, что она говорит?

Рисунок 9

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

3. Как настроить «Сводную таблицу»?


Если прямо здесь вы начнете тренироваться, то 1. Выделяйте «Плоскую таблицу» 2. Нажимайте Вставка/Сводная таблица/ОК.


Что есть у таблицы? Правильно! У таблицы есть КОЛОНКИ (1), СТРОКИ (2) и ЗНАЧЕНИЯ (3. У нее есть только КОЛОНКИ(1), СТРОКИ(2) и ЗНАЧЕНИЯ (3). (Рисунок 10). Просто перетягиваем поля (все, что мы называем «аналитическими разрезами, аналитиками, характеристиками, измерениями») в СТРОКИ и КОЛОНКИ. И наши «данные» (все, что мы называем «данные-сами данные-цифры-измеряемые величины-ресурсы-цифры-показатели») в ЗНАЧЕНИЯ.

Еще раз: … Хватаем поля сверху …= > … и тянем в СТРОКИ, КОЛОНКИ, ЗНАЧЕНИЯ.


Кстати, можно отметить, что Сводная таблица относится к кросс-таблицам, так как мы пересекаем в строках и в колонках различные «аналитические разрезы» (cross –пересечение), а на пересечении выводим значения данных. Помните в ЧАСТИ 1 – мы говорили о том, что кросс-таблицы человек воспринимает очень хорошо.


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

Рисунок 10

Как получить наиболее информативную таблицу? Здесь важна и последовательность полей, и их комбинация, и распределение между СТРОКАМИ и КОЛОНКАМИ. Какие-то таблицы получатся очень информативными, какие-то нет, и точной методики, как получить самую информативную для вашей задачи таблицу, мы не знаем, хотя и есть некоторые хитрости, которые мы сейчас опишем.

4. Хитрости при настройки сводной таблицы

Когда у вас есть какая-то задача, и вы ее можете произнести в слух, то вы услышите, как сами произнесете какие-то аналитические разрезы. Например, вы говорите: «В какие регионы страны продают наши филиалы? Нет ли ошибок, когда товар везется сначала из Моссквы на филиал в Ижевск, а потом из Ижевска клиенту обратно в Москву?». Произнесите еще раз: «В какие РЕГИОНЫ страны продают наши ФИЛИАЛЫ»? Какие аналитические разрезы участвуют в предложении? Регионы(1), ФИЛИАЛЫ(2). Поместите ФИЛИАЛЫ в СТРОКИ. Смотрим, что получилось (Рисунок 11).

В КОЛОНКИ мы поместили Регионы продаж, в СТРОКИ поместили Филиалы, а в ЗНАЧЕНИЯ – Кол-во. Все используемые поля, вы можете увидеть в верхней части, там, где написано «Выберете поля для добавления в отчет». Все поля, которые мы используем в таблице – всегда выделены «галочкой».

Рисунок 11

А дальше, если нужно, то начинаем усложнять эту таблицу. СОВЕТЫ:

  • Человек воспринимает легче, аналитические разрезы, когда они в СТРОКАХ. Поэтому если разрезов больше, чем 2, то сначала пробуйте добавлять их в СТРОКИ. Смотрите что получилось. (Рисунок 12) СОВЕТ: ДОБАВЛЯЙТЕ БОЛЬШЕ АНАЛИТИК В СТРОКИ, ЧЕМ В КОЛОНКИ.
  • Рисунок 12
  • Мы расшифровали продажи филиала еще одним аналитическим разрезом, Товаром. (смотрите в Настройки «Сводной таблицы» в СТРОКИ, ФИЛИАЛ, под ним Товар. Если поменять местами, то уже Товар будет расшифровываться аналитикой ФИЛИАЛ (Рисунок 13). В первом случае мы скажем: Продажи ФИЛИАЛА расшифровываются номенклатурой Товара, а во втором случае скажем, что продажи Товара расшифровываются по ФИЛИАЛАМ.) СОВЕТ: ПОРЯДОК АНАЛИТИЧЕСКИХ РАЗРЕЗОВ В СТРОКАХ ВАЖЕН, АНАЛИТИКА КОТОРАЯ ВЫШЕ РАСШИФРОВЫВАЕТ АНАЛИТИКУ, КОТОРАЯ НИЖЕ.
  • Рисунок 13
  • Посмотрите на (Рисунок 12, Рисунок 13), аналитики Товар и ФИЛИАЛ находятся в одной колонке, кому то это не очень удобно. Когда в СТРОКАХ много «аналитик» (вспомните Рисунок 8, где их шесть), то удобно, чтобы для каждой аналитики была своя колонка. Делается это так: КОНСТРУКТОР/Макет отчета/Показать в табличной форме (Рисунок 14).

Рисунок 14

Таблица после этого будет выглядеть так (Рисунок 15). Товар в 1-ой колонке, ФИЛИАЛ – во 2-ой.

Рисунок 15

СОВЕТ: ПРИМЕНЯЙТЕ «ПОКАЗАТЬ В ТАБЛИЧНОЙ ФОРМЕ».

Если у вас много «аналитик» в строках, то лучше убрать подсчет ИТОГОВ. Смотрите на (Рисунок 15). Товар 1 Итог, Товар 2 Итог.. Например, добавим еще аналитику Менеджер. (Рисунок 16)

Рисунок 16

Если убрать промежуточные итоги Товар Итог, Филиал Итог, то таблица будет выглядеть так (Рисунок 17).

Рисунок 17

Убрать промежуточные итоги можно, если нажать правой клавишей мыши на поле в СТРОКАХ.

Рисунок 18

А теперь вернемся к вопросу, что же умеет «сводная таблица».

5. Четыре вещи, которые делают СВОДНЫЕ ТАБЛИЦЫ

Вещь первая: считают все «трубы».

Вещь вторая: могут вывести уникальные поля, то есть ответить на вопрос: сколько и кто наши менеджеры, например. (Рисунок 19) Или, а какая вообще номенклатура Товара участвовала в продажах?

Рисунок 19

Вещь третья: cоотношения. Сводная таблица – не лучший для этого инструмент, но все-таки большой помощник… Например, как сочетается Филиал и Менеджер? (Рисунок 20) Менеджер работает в одном филиале, но в одном филиале может быть много менеджеров. Это легко можно увидеть из «сводной таблицы».

Настройка «Сначала менеджер – потом Филиал», говорит нам, в каком филиале работает менеджер.

А настройка «Сначала филиал – а потом менеджер», говорит нам, какие менеджеры работают в каком филиале.

Видите пустое пространство под «Москва»? Это Excel подсказывает, что Москве соответствуют несколько строк «менеджер», то есть в переводе на человеческий, в Москве работает несколько менеджеров. Это часто очень важно, понимать, тип связи: Их бывает 3. Один к одному (1:1), как муж-жена, у жены всегда один муж, у мужа одна жена. Один ко многим (1:N) у ребенка всегда один папа, а у папы может быть много детей., и многие к многим (N: N), это как друзья, я дружу с многими людьми, и каждый из них тоже не со мной одним. Запомните, вам когда-то это пригодится. А «сводная таблица» в Excel это показывает пропусками под строками в «сводной таблице». Один ко многим мы уже посмотрели. (Рисунок 20)

Рисунок 20

Многие ко многим (N: N), например, товар-производитель. (Рисунок 21). Каждая страна производит несколько видов Товара, и есть товары не производятся одной страной. (Хотя мы и видим, что товар 2,3,4,5 – производятся одной страной, но нам важен прецедент, чтобы сказать, что ТОВАР:ПРОИЗВОДИТЕЛЬ (N: N).

Рисунок 21

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

Вещь четвертая: Количество строк в таблице, сколько раз в таблице встретился Иванов И., например. Сколько раз встретился менеджер в каждой нашей таблице? (Рисунок 22). Иванов И. 3 раза, а Петров Н., Сидоров А. и Сидоров Е. по одному. В нашем примере это говорит, что Петров Н., Сидоров А. и Сидоров Е. осуществили всего одну продажу за этот период. Но кстати, не говорит, что Иванов И. сделал три продажи, так как он мог просто одному клиенту продать три вида Товара. (в нашей таблице – это было бы три записи). Кстати, можете проверить (кто хочет с линейкой и карандашом, кто хочет «Фильтром», в Excel, а кто хочет и с помощью «Сводной таблицы».

Рисунок 22