Сводная таблица в Excel. Пошаговая инструкция. Как быстро и качественно анализировать любые данные

Многообразие возможностей сводной таблицы

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

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

Далее нажимаем Ctrl+Т для непосредственного преобразования простой таблицы в умную. В появившемся диалоговом окне проверяем, что стоит галочка Таблица с заголовками ведь у исходной таблицы действительно есть заголовок. Нажимаем ОК.

Меняем раскраску таблицы (при необходимости) и даем понятное имя умной таблице. Все эти действия делаются во вкладке Конструктор таблиц.

Здесь же на вкладке Конструктор таблиц нажимаем Сводная таблица. Да-да! Именно во вкладке Конструктор таблиц. Здесь тоже есть возможность создания сводной таблицы. В предыдущих примерах сводную таблицу создавали через вкладку Вставка => Сводная таблица.

В диалоговом окне выбираем На новый лист.

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

Задаем сразу нормальное имя столбцам и форматы. Для этого на поле Сумма по полю Цена в области Значения нажимаем левой кнопкой мыши, выбираем Параметры полей значений…. В диалоговом окне в поле Пользовательское имя вносим Продажи, руб. Далее в левом нижнем углу нажимаем Числовой формат

В открывшемся окне выбираем Числовой, ставим галочку Разделитель групп разрядов () и указываем 0 число десятичных знаков => ОК => ОК.

Осталось переименовать столбец с наименованием стран. Выделяем ячейку с заголовком (в нашем случае, это ячейка А3) и пишем новый заголовок.

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

Теперь очевидно, что самые большие продажи в Китае, самые маленькие – во Вьетнаме.

А как понять какие вендоры продаются в этих странах? А тут есть два варианта, в зависимости от того, как нам удобнее. Первый вариант – добавить поле Вендор в область Строки ниже поля Страна. Добавляем перетаскиванием – зажимаем левой кнопкой мыши поле Вендор в списке полей и перетаскиваем в область Строки.

Вот так преобразовывается наша сводная таблица. Видно, что в Китае продается Tecno и Xiaomi. При этом есть возможность «схлопнуть» данные до Страны. Для этого надо нажать на значок минус «-», расположенный рядом с нужной страной (например, «-» со страной Китай).

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

Я не люблю, когда данные из разных полей (по сути, параметров) содержатся в одном столбце. Сейчас, например, в столбце А расположены и страны, и вендоры. Предпочитаю делать так, чтобы один параметр был в одном столбце, второй – во втором. И это можно сделать при помощи инструмента Макет отчета. Переходим во вкладку Конструктор => выбираем Макет отчета => Показать в табличной форме.

После этого Страна остается в столбце А, а вендор переходит в столбец В.

Excel, как обычно, старается помочь и делает автоматически итоги по каждой стране. Мне они не нужны, предпочитаю их удалять. Для этого на вкладке Конструктор выбираем Промежуточные итоги => Не показывать промежуточные итоги.

Отлично! Промежуточные итоги удалены. При этом остается возможность «схлопывать» данные до стран. Хочу обратить ваше внимание на интересный момент: в странах, в которых представлены два вендора наименование страны отображается только в одной (верхней) ячейке. Например, в Китае есть Tecno и Xiaomi, при этом наименование Китай отображается только в ячейке А4, а ячейка А5 пустая.

Если есть необходимость, чтобы наименование стран отображалось напротив каждого вендора, то необходимо во вкладке Конструктор нажать Макет отчета => Повторять все подписи элементов.

Зададим сводной таблице понятное название. Во вкладке Анализ сводной таблицы выберем Сводная таблица и в поле Имя введем Продажи по странам и вендорам.

Вот такой итог имеем:

Хорошо. Мы видим рейтинг продаж по странам, да еще дополнительно можем посмотреть и по вендорам внутри страны. Это был первый способ увидеть продажи по странам и вендорам. Давайте рассмотрим второй способ. Скопируем сводную таблицу Продажи по странам и вендорам и вставим ее копию справа от первой (например, в ячейку Е3). Для того, чтобы сводная таблица скопировать корректно и не потеряла своих свойств (не превратилась в обычную таблицу), необходимо копировать весь диапазон ее расположения. В нашем примере нужно выделить диапазон ячеек А3:С12, скопировать при помощи комбинации клавиш Ctrl+C и вставить в ячейку Е3 при помощи комбинации клавиш Ctrl+V.

Продолжаем работать уже в новой скопированной сводной таблицы. Перетащим поле Вендор из области Строки в область Столбцы.

Вот такой результат получим. Зададим понятное имя сводной через Анализ сводной таблицы => Сводная таблица => в поле имя внесем Продажи по странам и вендорам (2).

Теперь посмотрим рейтинг продаж по вендорам. Чтобы не создавать сводную таблицу с нуля, скопируем ранее созданную и вставим ее ниже. Выделяем весь диапазон, в которой расположена сводная (диапазон А3:С12), копируем при помощи комбинации клавиш Ctrl+С, ставим курсор в ячейку А15 и нажимаем комбинацию клавиш Ctrl+V. Сейчас достаточно поставить курсор в новую сводную таблицу, и в области строк удалить поле Страна. Это можно сделать, как показано на рисунке ниже, либо просто сняв галочку в перечне полей с поля Страна.

После этого имеет смысл заново отсортировать продажи по убыванию. Ставим курсор в любую ячейку с продажами (кроме самого заголовка и общего итога), нажимаем правую кнопку мыши => Сортировка => Сортировка по убыванию. Tecno продается лучше всех, Sony – хуже всех.

То есть на данный момент, мы уже понимаем, что топ по продажам из стран – это Китай, из вендоров Tecno. Антитоп: из стран – Вьетнам, из вендоров – Sony. Интересный момент – при почти равных суммарных продажах в Японии и Вьетнаме (в Японии продажи составляют 76 872 руб., во Вьетнаме 76 734 руб.), вендор Sony продается только в Японии. Кажется, Sony претендент на исключение из товарной матрицы.

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

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

Прекрасно. Теперь видны продажи не только в рублях, но и доля продаж.

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

Выбираем Числовой, ставим галочку Разделитель групп разрядов () и указываем 0 число десятичных знаков => ОК => ОК.

Вот такой результат получили. Зададим понятное имя сводной таблицы через Анализ сводной таблицы => Сводная таблица => в поле имя внесем Продажи по вендорам.

Движемся дальше. Необходимо увидеть аналитику в разрезе торговых сетей и номенклатур. Давайте сначала посмотрим по номенклатурам. Опять же чтобы не создавать сводную таблицу с нуля, скопируем сводную Продажи по вендорам и вставим ее ниже. Выделяем весь диапазон таблицы, в которой расположена сводная (диапазон А15:D22), копируем при помощи комбинации клавиш Ctrl+С, ставим курсор в ячейку А25 и нажимаем комбинацию клавиш Ctrl+V. Ставим курсор в новую сводную, и в области Строки выбираем Удалить поле Вендор.

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

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

Очевиден лидер продаж в разрезе номенклатур:

И вот теперь мы можем немного исправить данные в столбце Отклонение продаж от Tecno и превратить его в Отклонение продаж от Цифр видеокамер. Нажимаем левой кнопкой мыши на поле Отклонение продаж от Tecno в области Значения => выбираем Параметры полей значений…. В отрывшемся диалоговом окне меняем пользовательское имя столбца. В поле выбираем Номенклатура, элемент выбираем Цифровая видеокамера на флеш-карте.

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

И мы же помним, если нам не нужно это отклонение достаточно удалить соответствующее поле из области Значения. Не забываем нормально именовать сводные таблицы. Даем последней созданной таблице имя Продажи по номенклатурам (все также через Анализ сводной таблицы => Сводная таблица => вносим название в поле имя).

Хочется понять в каких странах, торговых сетях, по каким вендорам они продаются. И тут тоже есть несколько вариантов аналитик.

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

Выделили сводную => скопировали (Ctrl+C) => вставили ниже (Ctrl+V).

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

Один из вариантов может быть таким. Перенесем поле Вендор в область Строки ниже Номенклатуры. При таком варианте столбец Отклонение продаж от Цифр видеокамер становится, как будто, неуместным.

Давайте удалим его. В области Значения на поле Отклонение продаж от Цифр видеокамер нажимаем левой кнопкой мыши => Удалить поле. После этого поле Страна кладем в область Фильтры и теперь мы можем фильтровать по интересующей нас стране.

Или может быть так. Перетаскиваем поле Вендор из области Строки в область Столбцы выше ∑ Значения. Таким образом, мы видим продажи по стране Китай в разрезе номенклатуры и вендора.