Создание сводных таблиц в excel

Настройка параметров и фильтров

«Поля сводной таблицы» состоят из различных составных частей будущей сводной таблицы. Они объединены в один блок и находятся наверху. Вы можете увидеть названия столбцов — «Квадратные метры», «Количество комнат», «Жилой комплекс», «Район», «Цена», «Дата продажи», «ФИО агента».

Чуть правее есть параметры в меню, обладающие важным функционалом:

Значения — для выбора необходимых полей и произведения вычислений в сводной таблице. По стандарту данные суммируются, но есть и другие варианты. Можно умножить, выбрать среднее, стандартное или несмещённое отклонение, максимум или минимум. Результат зависит и от формата данных в сводной таблице. Для расчёта общей стоимости подойдёт операция сложение, при работе с текстовым форматом можно просмотреть количество соответствующих заданному параметру ячеек.
Строки и столбцы — нужны для отражения расположения полей и областей. При выборе строк поля будут располагаться соответствующим образом, если выбрать столбец, то поля будут в виде него.
Фильтры — благодаря им происходит фильтрация данных в сводной таблице. Фильтры находятся отдельно от общей таблицы, поэтому можно выбрать, какие данные нужно отразить обязательно, а какие стоит убрать. Так легко увидеть, в каких жилых комплексах чаще всего продаются квартиры, или понять, на какую стоимость агенты продали недвижимость в апреле или мае.

Работать с настройками сводной таблицы можно несколькими способами. Быстрее всего поставить галочку возле поля. Программа автоматически «подтянет» данные из исходной таблицы. Однако этот вариант не самый надёжный. Excel может создать и расположить данные так, что их потом будет неудобно анализировать. Лучше сделать это вручную: нужно выбрать подходящие поля и добавить их в желаемую область

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

Погрузиться в тонкости работы с Microsoft Excel, узнать о лайфхаках и фишках, которые помогут оптимизировать выполнение даже самых сложных задач, вы можете на онлайн-курсах. Мы собрали проверенные варианты быстрого обучения Excel в небольшой список, курсы подойдут как новичкам, так и профессионалам своего дела:

Excel для анализа данных — Skypro
Excel. Базовый уровень — Бруноям
Excel для бухгалтера уровень PRO — Высшая школа Главбух

Фамилии агентов лучше расположить построчно. Для этого первым нужно зажать «ФИО агента» и перенести в «Строки», которые находятся ниже. Вы увидите, как слева отобразятся фамилии всех сотрудников, занимающихся продажами. Перенесём остальные параметры и данные в сводную таблицу — «Количество комнат» также надо перетащить в «Строки». В левой части появится ещё один блок. Сводная таблица автоматически распределит данные по квартирам между менеджерами, которые их продали. Нам ещё понадобится площадь и стоимость квартир. Для этого переносим поля с названиями столбца «Квадратные метры» и «Цена» в значения.

Перед нами появилась уже готовая сводная таблица с необходимыми категориями. Мы можем посмотреть, какие именно квартиры продал каждый сотрудник, какая площадь у них была и сколько они стоили. Для менеджера формируется итоговая заработанная за квартал сумма. Вы можете добавить в сводную таблицу те поля, которые нужны для отчётности. Например, посмотреть, в каких ЖК и местах больше всего новых продаж. Или оценить стоимость проданных жилых помещений по районам, где располагаются многоквартирные дома.

Сводные таблицы в Excel. Примеры

На примерах ниже мы разглядим, как при помощи сводных таблиц ответить на три вопросца:

Какой размер выручки у региона Север за 2021 год?;
ТОП 5 клиентов по выручке;
Какое пространство по выручке занимает клиент Лудников ИП в регионе Восток?

До этого чем рассматривать данные, принципиально решить каким образом должны смотреться данные таблицы (какие данные разметить в колонки, строчки, значения, фильтры). К примеру, если нам необходимо показать данные продаж клиентов по регионам, то следует поместить наименования регионов в строчки, месяцы в колонки, значения продаж в поле “Значения”. Как лишь вы представили каким образом вы видите итоговую таблицу – начинайте её создание.

В окне “Поля сводной таблицы” расположены области и поля со значениями для размещения:

Поля создаются на базе значений начального спектра данных. Раздел «Области» – это пространство, где вы располагаете элементы таблицы.

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

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

Пример 1. Какой размер выручки у региона Север?

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

сделать сводную таблицу и поле “Регион” перенести в область “Строчки”;
поле “Выручка” расположить в области “Значения”
задать денежный числовой формат ячейкам со значениями.

Получим ответ: реализации региона Север составляют 1 233 006 966 ₽:

Пример 2. ТОП 5 клиентов по продажам

Для того чтоб вычислить рейтинг ТОП 5 клиентов, нам необходимо:

переместить поле “Клиент” в область “Строчки”;
поле “Выручка” расположить в области “Значения”;
задать денежный числовой формат ячейкам со значениями.

У нас получится последующая таблица:

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

кликните правой клавишей на хоть какой из строчек с данными выручки;
перейдите в меню “Сортировка” => “Сортировка по убыванию”:

Как итог мы получим отсортированный перечень клиентов по размеру выручки.

Создание сводной таблицы в Excel

Открываем исходные данные. Сводную таблицу можно строить по обычному диапазону, но правильнее будет преобразовать его в таблицу Excel. Это сразу решит вопрос с автоматическим захватом новых данных. Выделяем любую ячейку и переходим во вкладку Вставить. Слева на ленте находятся две кнопки: Сводная таблица и Рекомендуемые сводные таблицы.

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

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

Обычно ничего менять здесь не нужно. После нажатия Ок будет создан новый лист Excel с пустым макетом сводной таблицы.

Макет таблицы настраивается в панели Поля сводной таблицы, которая находится в правой части листа.

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

Сводная таблица состоит из 4-х областей, которые находятся в нижней части панели: значения, строки, столбцы, фильтры. Рассмотрим подробней их назначение.

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

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

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

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

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

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

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

Посмотрим, как это работает в действии. Создадим пока такую же таблицу, как уже была создана с помощью функции СУММЕСЛИМН. Для этого перетащим в область Значения поле «Выручка», в область Строки перетащим поле «Область» (регион продаж), в Столбцы – «Товар».

В результате мы получаем настоящую сводную таблицу.

На ее построение потребовалось буквально 5-10 секунд.

Как просмотреть все формулы использованные в вычисляемом поле?

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

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

Как это сделать:

Выберите любую ячейку в сводной таблице;

Перейдите в раздел «Анализ» -> «Поля, элементы и наборы»;

Нажмите на кнопку «Вывести формулы».

Как только вы нажимаете на «Вывести формулы», Excel автоматически создает новый рабочий лист, в котором содержатся сведения обо всех вычисляемых полях/элементах, которые вы создали в этой сводной таблице.

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

Узнайте Excel как свои пять пальцев на курсе по таблицам от Skillbox

Как работать со сводными таблицами в Excel

Бывает недостаточно создать отчет в виде таблицы – требуются дополнительные действия. Рассмотрим несколько способов работы со сводной таблицей, ее структурой и данными.

Как в сводную таблицу Excel добавить столбец или таблицу

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

Открываем вкладку «Анализ» (в более ранних версиях – «Параметры») и кликаем по кнопке «Источник данных».

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

Обновление данных в сводной таблице в Excel

Если отчет не переведен в динамический формат, все еще есть способы обновить информацию в нем после внесения изменений в источник. Обновим сводную таблицу вручную. Для этого кликнем по любой ячейке отчета и по пункту «Обновить» в контекстном меню. Также можно воспользоваться кнопкой с тем же названием на вкладке «Параметры»/«Анализ».

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

Выбираем любую ячейку отчета и открываем вкладку «Параметры»/«Анализ». Находим пункт «Сводная таблица» – в нем нужно выбрать пункт «Параметры».

Находим в настройках пункт «Разметка и формат». Этот раздел состоит из нескольких пунктов. Требуется поставить галочки в графах, показанных на изображении ниже, и нажать кнопку «ОК».

Как создать таблицу в экселе пошагово: 4 шага и форма готова!

Простейшую табличку в экселе можно создать двумя способами: сначала ее нарисовать, а потом заполнить или наоборот. Ну, обо всем по порядку. Рассмотрим вопрос, как создать таблицу в экселе пошагово. А начнем, как говорится «от печки».

Шаг 1. Создаем базу

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

Шаг 2. Делаем скелет таблички

Сделать скелет таблички можно следующим образом:

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

У нас получится вот такой симпатичный «скелетик».

Шаг 3. Рисуем границы

Раскрывая список из вкладки «Границы», выбираем «Все границы». В результате обозначаться внутренние и внешние контуры.

Шаг 4. Редактируем форму

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

Все, табличка готова. Можно смело ее заполнять, перескакивая с ячейки на ячейку с помощью клавиши Enter. При желании можно изменять цвет ячеек, а также шрифт написанного.

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

А нажатие на значок суммы позволит подсчитать сумму столбца в автоматическом режиме.

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

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

Как удалить СТ

Самый простой случай – когда вы создали сводную таблицу, отослали результаты шефу, и она вам больше не нужна. Если вы в этом уверены, просто выбираем таблицу и жмём клавишу Delete. Просто и эффективно.

Но вдруг структура таблицы может вам понадобиться в будущем? В Excel имеется возможность удалить только результаты, или данные ячеек. Рассмотрим, как это делается.

Для удаления результатов вычислений выполняем следующие шаги:

помечаем мышкой любую ячейку СТ;
выбираем на верхней панели вкладку «Анализ»;
из перечня доступных действий выбираем «Очистить»;
кликаем на пункте «Очистить всё».

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

снова выбираем любую ячейку, кликаем на вкладке «Анализ»;
выбираем пункт меню «Действия», кликаем на «Выбрать», отмечаем мышкой всю сводную таблицу;
щёлкаем ПКМ внутри выделенной области;
из контекстного меню выбираем пункт «Скопировать»;
переходим к вкладке «Главная», снова щёлкаем ПКМ и выбираем «Вставить»;
выбираем вкладку «Вставить значение», в ней отмечаем параметр «Вставить как значение».

В итоге сводная таблица будет стёрта с сохранением результатов.

СОВЕТ. Ускорить процедуру можно посредством использования комбинации клавиш. Для выделения таблицы применяйте Ctrl + A, для копирования – Ctrl + C. Затем жмём ALT + E, ALT + S, ALT + V и завершаем процедуру нажатием Enter.

Для удаления сводных таблиц в Excel 2007/2010 нужно использовать другой алгоритм:

выбираем СТ, предназначенную для удаления;
жмём вкладку «Параметры»;
в «Группе действий» выбираем пункт «Очистить», а затем – «Очистить всё».

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

В старых версиях программы для этого нужно выделить диаграмму, щёлкнуть на вкладке «Анализ», выбрать группу данных и нажать последовательно «Очистить» и «Очистить всё».

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

Для версий старше Excel 2010 нужно выбрать диаграмму, на вкладке «Анализ» выбрать пункт «Действия» и нажать «Очистить» и «Очистить всё». Результат будет аналогичным.

Создание сводной таблицы вручную

Иногда ни одна из сводных таблиц, предлагаемых инструментами быстрого анализа или кнопкой Рекомендуемые таблицы, не подходит. В подобных случаях можно либо выбрать готовый образец сводной таблицы, макет которого наиболее близок к требуемой вам сводной таблице, либо создать нужную таблицу “с нуля”. Создать сводную таблицу вручную не так уж и трудно, как может показаться на первый взгляд.

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

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

Если источник данных сводной таблицы представляет собой внешнюю базу данных, созданную в другой программе, такой как Access, установите переключатель Использовать внешний источник данных. Потом щелкните на кнопке Выбрать подключение, а затем в открывшемся диалоговом окне выберите требуемое подключение. Кроме того, Excel поддерживает анализ данных для нескольких связанных таблиц листа (так называемая “модель данных”). Если данные новой сводной таблицы будут анализироваться наряду с данными существующей сводной таблицы, то установите флажок Добавить эти данные в модель данных.

После того как будет определен источник данных и указано место расположения сводной таблицы, щелкните на кнопке ОК, и программа добавит пустую сетку для новой таблицы, а также откроет в правой части области рабочего листа панель Список полей сводной таблицы. Эта панель разделена на две части. Вверху находится список полей источника данных, которые можно добавить в сводную таблицу, а внизу — область, разделенная на четыре зоны: ФИЛЬТРЫ, СТРОКИ, СТОЛБЦЫ и ЗНАЧЕНИЯ.

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

ФИЛЬТРЫ. Здесь содержатся поля, позволяющие фильтровать данные таблицы. Так, например, если перетащить сюда поле года, то можно будет отображать в таблице сводные данные для каждого конкретного года, представленного в списке данных.
СТОЛБЦЫ. Здесь содержатся поля, определяющие данные, которые отображаются в столбцах сводной таблицы.
СТРОКИ. Здесь находятся поля, определяющие данные, которые отображаются в строках сводной таблицы.
ЗНАЧЕНИЯ. Здесь содержатся поля, определяющие, какие данные будут отображаться в ячейках таблицы, т.е. значения, консолидируемые в последнем столбце (по умолчанию суммируемые).

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

В этой сводной таблице в качестве фильтра из списка данных было выбрано поле Дата. Поле Категория я назначил меткам строк. В качестве значений были выбраны поля Расход и Доход.

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

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

Список полей. Служит для сокрытия и отображения списка полей на панели задач в правой части области рабочего листа.
+/- Кнопки. Используется для сокрытия и отображения кнопок сворачивания (-) и разворачивания (+) конкретных строк и столбцов, позволяющих временно удалять и отображать в сводной таблице конкретные значения.
Заголовки полей. Служит для сокрытия и отображения полей, назначаемых меткам строк и столбцов сводной таблицы.