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

Сводная таблица в Excel. Пошаговая инструкция

Как быстро и качественно анализировать любые данные

Наталия Внуковская

© Наталия Внуковская, 2026

ISBN 978-5-0069-2355-3

Создано в интеллектуальной издательской системе Ridero

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

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

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

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

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

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

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

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

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

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

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

Где взять эти файлы: материалы можно скачать на моем сайте в разделе «Книги» 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 выдаст сообщение, что такое поле сводной таблицы уже существует. Что это значит? А это значит, что в списке полей для сводной таблицы есть именно такое название поля (досимвольно, т.е. абсолютно идентичное название с точностью до символа). А я напомню, что поле сводной таблицы – это заголовок исходной таблицы (он же поле сводной таблицы). И его нельзя использовать в качестве заголовка сводной таблицы. Нельзя использовать досимвольно. Но если добавить невидимый глазу пробел до _Продано штук (здесь и далее вместо пробела я буду использовать нижнее подчеркивание, чтобы читателю было удобнее) или после Продано штук_, то сводная абсолютно спокойно даст переименовать заголовок.

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

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