Бесплатный фрагмент - Сводная таблица в Excel. Пошаговая инструкция

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

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

Все названия программных продуктов являются зарегистрированными торговыми марками соответствующих фирм.

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

Введение. Зачем нужна эта книга

Кому предназначена эта книга.

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

Как пользоваться книгой.

Читать книгу следует не как художественную литературу — укрывшись пледом или под одеялом. Эта книга будет максимально полезна, если перед вами будет компьютер и вы сможете отрабатывать все описанные алгоритмы сразу. Именно так у вас появится навык. К концу книги у вас точно отложится в голове алгоритм создания сводной таблицы, ведь он описан огромное количество раз. Материал в книге изложен от самых простых задач к наиболее сложным. Двигайтесь постепенно.

Файлы с примерами из этой книги

В конце каждой главы указаны названия файлов, которые необходимо использовать для отработки навыка. Обратите внимание, что файлы, как правило, существуют в двух версиях, например, «Многообразие возможностей_исходник» и «Многообразие возможностей». Файлы со словом «исходник» содержат исходные данные, которые необходимо обработать, на них нужно тренироваться выполнять все описанные в книге алгоритмы прямо по ходу изучения материала. Файлы без слова «исходник» — это итоговый файл, с которым вы сможете сравнить свой отработанный файл.

Где взять эти файлы: материалы можно скачать на моем сайте в разделе «Книги» https://nataliia-vnukovskaia.com/books.

Лучший способ усвоить материал и выработать устойчивый навык — это делать. Делайте все описанные в книге процедуры и у вас обязательно все получится. А еще лучше — объясните, как это сделать своему другу или коллеге.

Что такое сводная таблица и зачем она нужна

Сводная таблица — это таблица, которая в буквальном смысле слова сводит данные.

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

Например, у нас есть данные по выручке в разрезе городов за три месяца.

Имея только эти данные, мы можем увидеть:

1. общую сумму выручки за все месяца по городам

2. среднюю выручку за месяц в разрезе города

3. общую сумму выручки по всем городам в разрезе месяца

4. детально посмотреть выручку по городам в разрезе месяца

или так

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

Впечатляет? Не правда ли. И это, не используя ни одной формулы! Все реализовано только нажатием кнопок!

Давайте же учиться делать так же.

!Материалы к главе: файл «Одна таблица-несколько аналитик»

Разница между сводной таблицей и не сводной

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

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

Вот города расположены в строках, а месяца — в столбцах.

Буквально два движения в диалоговом окне сводных таблиц и города уже расположены в столбцах, а месяца — в строках.

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

Как понять, сводная перед вами таблица или нет?

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

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

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

!Материалы к главе: файл «Одна таблица-несколько аналитик»

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

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

Во-первых, при создании сводной таблицей вверху в правой части экрана появляются две дополнительные вкладки — Анализ сводной таблицы (в некоторых версиях она называется Параметры) и Конструктор. Запомните! Все, что можно сделать со сводными таблицами содержится в этих двух вкладках. Если вы забыли, где именно находится необходимый функционал, то посмотрите сначала в одной, потом — во второй. И поиски обязательно увенчаются успехом.

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

Что такое поля и зачем они нужны? По сути, поля сводной таблицы — это столбцы исходной таблицы. Как видим на примере, столбцы Дата, Город и Выручка в исходной таблице — это поля в сводной.

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

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

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

Но что делать если ее нужно вернуть обратно? Это можно сделать двумя способами. Путь первый. Нажать кнопку Список полей во вкладке Анализ сводной таблицы, и панель со списком полей и областей снова отобразится.

Путь второй. Щелкнуть правой кнопкой мыши на любой ячейке сводной таблицы и выбрать Показать список полей.

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

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

1). Снять галочку в списке полей. Я такой способ использую довольно редко. Сейчас объясню почему. Часто в работе со сводными одно и то же поле используется одновременно в разных областях или в одной области, но с использованием разных операций (суммирование, подсчитывание среднего, количества) или дополнительных вычислений. Предположим, поле Выручка лежит одновременно в области Фильтры и в области Значения. При снятии галочки в списке полей поле Выручка удалится и из Фильтров, и из Значений. А если надо было, чтобы удалилось только из Фильтров? Поэкспериментируйте.

2). Удалить строки непосредственно из области. Например, поле Выручка лежит в области Фильтры и области Значения. Необходимо удалить из области Значения. Нажимаем левой кнопкой мыши на поле Сумма по полю Выручка в области Значения => Удалить поле.

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

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

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

Еще один лайфхак при работе с большим количеством полей — всегда есть возможность ввести в строке Поиск искомое поле, а потом перетащить его в нужную область.

!Материалы к главе: файл «Интерфейс»

Умная таблица как основа сводной

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

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

Что это за преимущества:

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

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

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

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

4. При прокрутке таблицы вниз, заголовок всегда остается виден. И для этого не надо дополнительно закреплять его через вкладку Вид => Закрепить области. Это однозначно удобно.

5. При написании формулы в столбце умной таблицы, эта формула протягивается автоматически до конца таблицы. Не приходится самостоятельно продлевать формулу (тянуть черный крестик автозаполнения).

6. При добавлении новых строк, формулы копируются в них автоматически.

Это основные плюсы. Помимо них, есть и другие. Перечисленные выше — наиболее часто используемые.

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

Более подробно об умных таблицах можно узнать в приложении к книге «Умная таблица. Подробнее». Главный же вопрос этой главы — почему в качестве источника для сводной таблицы лучше использовать умную таблицу и когда лучше этого не делать.

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

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

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

После этого нажимаем Ctrl+T, в появившемся диалоговом окне проверяем галочку Таблица с заголовками, нажимаем ОК. В исходной таблице действительно есть заголовок, именно поэтому необходимо оставить галочку. Если заголовка нет, то галочку надо снимать (подробнее в приложении к книге «Оставлять или нет галочку ✔{️}Таблица с заголовками для умной таблицы?»).

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

Если вам не нужна раскраска таблицы, на вкладке Конструктор таблиц в блоке Стили таблиц выбираем тип форматирования Нет.

Вот такой результат должен получиться

Теперь создадим сводную таблицу. Выделяем любую ячейку умной таблицы. На вкладке Вставка нажимаем Сводная таблица.

В появившемся диалоговом окне отобразится название умной таблицы (ничего не меняем). А вот ниже, там, где написано Укажите, куда следует поместить отчет сводной таблицы выбираем вариант На существующий лист. После этого в поле Диапазон надо поставить курсор и выбрать ячейку, в которую планируем поместить отчет сводной таблицы. Я хочу разместить в ячейке G1. Обратите внимание, как отображает имя выбранной ячейки Excel. Не просто G1, а «Исходник_умная таблица»! $G$1. Кто работает с Excel хотя бы иногда, знают, что в данном случае «Исходник_умная таблица» — это название листа, а символ $ означает закрепление диапазона. Это не я закрепила диапазон вручную. Вместо меня это сделал Excel самостоятельно (Excel вообще достаточно часто старается помочь и некоторые действия делает автоматически, за пользователя. Вопрос только в том — всегда ли принимать эти автоматические изменения, это мы обсудим позже). Нажимаем ОК.

Вот такой промежуточный результат получается

Кладем поле Продано штук в область Значения. А поле Серия перетаскиваем в область Строки. Легко сказать «Кладем», а как это сделать на практике? Первый способ — отметить галочкой нужное на поле в списке полей. Я отметила поля Серия и Продано штук и Excel абсолютно корректно положил поля в нужные области.

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

Вот такую таблицу получаем:

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

При попытке переименовать заголовок из Сумма по полю Продано штук в Продано штук Excel выдаст сообщение, что такое поле сводной таблицы уже существует. Что это значит? А это значит, что в списке полей для сводной таблицы есть именно такое название поля (досимвольно, т.е. абсолютно идентичное название с точностью до символа). А я напомню, что поле сводной таблицы — это заголовок исходной таблицы (он же поле сводной таблицы). И его нельзя использовать в качестве заголовка сводной таблицы. Нельзя использовать досимвольно. Но если добавить невидимый глазу пробел до _Продано штук (здесь и далее вместо пробела я буду использовать нижнее подчеркивание, чтобы читателю было удобнее) или после Продано штук_, то сводная абсолютно спокойно даст переименовать заголовок.

Исправляем заголовок Сумма по полю Продано штук в Продано штук_ и вот такой результат получаем. Поздравляю! Ваша первая сводная создана!

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

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

Сок автоматически попадет в сводную.

А теперь сделаем все тоже самое. Но только пропустим этап преобразования простой таблицы в умную. Возвращаемся к исходнику и начинаем работать с ним. Выделяем любую ячейку исходной таблицы (ту самую не умную, а обычную простую таблицу). Нажимаем комбинацию клавиш Ctrl+A (выделится весь диапазон). На вкладке Вставка нажимаем Сводная таблица. В появившемся диалоговом окне отобразится выделенный диапазон «Исходник_простая таблица»! $A$1:$D$12 (мы ничего не меняем). Ниже, там где написано Укажите, куда следует поместить отчет сводной таблицы выбираем вариант На существующий лист. После этого в поле Диапазон надо поставить курсор и выбрать ячейку, в которую планируем поместить отчет сводной таблицы. Размещаем отчет сводной таблицы в ячейке G1. Нажимаем ОК.

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

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

Теперь обновляем сводную таблицу. На любой ячейке сводной таблицы щелкаем правой кнопкой мыши. В появившемся меню выбираем Обновить. И что мы видим?

Ничего не поменялось. Почему? Давайте вернемся к источнику данных. Чтобы увидеть источник данных, на основе которых построена сводная таблица, необходимо на вкладке Анализ сводной таблицы выбрать Источник данных => Источник данных…

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

Как только в источнике данных «Исходник_простая таблица»! $A$1:$D$12 исправить на «Исходник_простая таблица»! $A$1:$D$13, то в сводной таблице отобразятся все данные.

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

!Материалы к главе: файлы «Умная таблица-основа сводной_исходник» и «Умная таблица-основа сводной»

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

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

Начинаем с того, что из простой таблицы делаем умную. Для этого ставим курсор в любую ячейку исходной таблицы. Выделяем весь диапазон таблицы при помощи комбинации клавиш на клавиатуре 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).

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

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

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

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

Или так. Добавляем поле Торговая сеть в область Строки.

А можно еще и так. Перетаскиваем в области Столбцы ∑ Значения выше поля Вендор и построение отчета меняется.

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

!Материалы к главе: файлы «Многообразие возможностей_исходник» и «Многообразие возможностей»

Делаем сводную таблицу на основе кривой выгрузки из 1С. Сумма по полю

Давайте потренируемся и рассмотрим часто встречающийся пример — обработку отчета или выгрузки из какой-нибудь учетной системы (1С Бухгалтерия, ЗУП, УНФ и т.п.). У них много общего, т.к. при сохранении из учетной системы в формат xls часто имеют ряд схожих моментов, которые затрудняют их обработку.

Итак, есть вот такая выгрузка из 1С:

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

Понять, сколько продано по каждому складу, можно отфильтровав нужный склад. Например, по складу г. Котельники, пр.1-Мега (салон) продано 9 единиц товара на общую сумму 8 991 руб.

Если попытаться создать сводную таблицу на основе неотредактированной выгрузки из 1С (поставить курсор в любую ячейку таблицы, нажать Ctrl+A, а потом во вкладке Вставка выбрать Сводная таблица => ОК), Excel выдаст следующее сообщение

Так что придется немного предварительно немного отредактировать таблицу.

Что тут важно? Во-первых, есть сгруппированные строки (1—2), которые нам вообще не нужны. Во-вторых, есть объединенные столбцы. Например, колонка Склад фактически занимает столбцы A-D. Ну и в-третьих, имеется двухуровневый заголовок: заголовки из колонок Склад, Документ продажи и Итого расположены фактически в 4-й строке, а заголовки Количество товаров и Сумма продаж со скидкой расположены в 5-й строке. Все эти группировки, объединение столбцов и строк, пустые столбцы и тому подобное — типичное явление для выгрузок из большинства учетных систем.

Давайте исправлять.

Первое, что необходимо сделать — отменить объединение на всем листе. Для этого ставим курсор в левом верхнем углу таблицы между столбцом А и строкой 1 как показана на рисунке

Нажимаем левую кнопку мыши, и весь лист становится выделенным. Далее нажимаем на вкладке Главная на стрелочку справа от кнопки Объединение столбцов => Отменить объединение ячеек.

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

То же самое делаем с ненужными пустыми столбцами. Выделяем столбцы B, C и D при помощи зажатой левой кнопки мыши => нажимаем правую кнопку мыши на любом из выделенных столбцов => Удалить.

Осталось разобраться с двухуровневым заголовком.

Что будет, если мы оставим заголовок двухуровневым?

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

Так что преобразование двухуровневого заголовка в одноуровневый — обязательный этап.

В нашем случае сделать это довольно легко. Выделяем при помощи зажатой левой кнопки мыши значения Склад и Документ продажи и перетаскиваем их на строку ниже

Остается только удалить теперь ненужную нам верхнюю строку и можем приступать к формированию сводной таблицы.

Напомню, удаляем строку следующим образом: выделяем всю строку, нажав левой кнопкой мыши на номер строки 1, потом нажимаем правую кнопку мыши на том же самом месте => удалить.

Готово! У нас есть исходная таблица для формирования сводной.

Как всегда (почти всегда), начинаем с того, что из простой плоской таблицы делаем умную таблицу. Для этого мы ставим курсор в любую ячейку таблицы (например, в В6) и нажимаем на клавиатуре комбинацию клавиш Ctrl+A (выделится вся таблица).

После этого нажимаем комбинацию клавиш Ctrl+Т для преобразования в умную таблицу

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