Как в сводной таблице сделать столбцы рядом
Перейти к содержимому

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

  • автор:

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

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел»
ИНН 7735603520
ОГРН 1147746834949
ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРНИП 310633031600071

Как сделать сводные таблицы в Excel: пошаговая инструкция со скриншотами

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

Сводная таблица — инструмент для анализа данных в Excel. Она собирает информацию из обычных таблиц, обрабатывает её, группирует в блоки, проводит необходимые вычисления и показывает итог в виде наглядного отчёта. При этом все параметры этого отчёта пользователь может настроить под себя и свои потребности.

Разберёмся, для чего нужны сводные таблицы. На конкретном примере покажем, как их создать, настроить и использовать. В конце расскажем, можно ли делать сводные таблицы в «Google Таблицах».

Зачем нужны сводные таблицы и когда их используют

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

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

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

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

Разберёмся пошагово, как это сделать с помощью сводной таблицы.

Шаг 1

Создаём сводную таблицу

Чтобы сводная таблица сработала корректно, важно соблюсти несколько требований к исходной:

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

Теперь переходим во вкладку «Вставка» и нажимаем на кнопку «Сводная таблица».

Появляется диалоговое окно. В нём нужно заполнить два значения:

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

В нашем случае выделяем весь диапазон таблицы продаж вместе с шапкой. И выбираем «Новый лист» для размещения сводной таблицы — так будет проще перемещаться между исходными данными и сводным отчётом. Жмём «Ок».

Excel создал новый лист. Для удобства можно сразу переименовать его.

Слева на листе расположена область, где появится сводная таблица после настроек. Справа — панель «Поля сводной таблицы», в которые мы будем эти настройки вносить. В следующем шаге разберёмся, как пользоваться этой панелью.

Шаг 2

Настраиваем сводную таблицу и получаем результат

В верхней части панели настроек находится блок с перечнем возможных полей сводной таблицы. Поля взяты из заголовков столбцов исходной таблицы: в нашем случае это «Марка, модель», «Цвет», «Год выпуска», «Объём», «Цена», «Дата продажи», «Продавец».

Нижняя часть панели настроек состоит из четырёх областей — «Значения», «Строки», «Столбцы» и «Фильтры». У каждой области своя функция:

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

Настроить сводную таблицу можно двумя способами:

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

Первый вариант не самый удачный: Excel редко ставит данные так, чтобы с ними было удобно работать, поэтому сводная таблица получается неинформативной. Остановимся на втором варианте — он предполагает индивидуальные настройки для каждого отчёта.

В случае с нашим примером нужно, чтобы сводная таблица отразила ФИО менеджеров по продаже, проданные автомобили и их цены. Остальные поля — технические характеристики авто и дату продажи — можно будет использовать для фильтрации.

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

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

Теперь добавим модели автомобилей, которые эти менеджеры продали. По такому же принципу перетянем поле «Марка, модель» в область «Строки».

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

Определяем, какая ещё информация понадобится для отчётности. В нашем случае — цены проданных автомобилей и их количество.

Чтобы сводная таблица самостоятельно суммировала эти значения, перетащим поля «Марка, модель» и «Цена» в область «Значения».

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

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

Шаг 3

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

Чтобы можно было фильтровать информацию сводной таблицы, нужно перенести требуемые поля в область «Фильтры».

В нашем примере перетянем туда все поля, не вошедшие в основной состав сводной таблицы: объём, дату продажи, год выпуска и цвет.

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

В блоке фильтров нажмём на стрелку справа от поля «Год выпуска»:

В появившемся окне уберём галочку напротив параметра «Выделить все» и поставим её напротив параметра «2017». Закроем окно.

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

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

Шаг 4

Проводим дополнительные вычисления

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

Кликнем правой кнопкой на любое значение цены в таблице. Выберем параметр «Дополнительные вычисления», затем «% от общей суммы».

Теперь вместо цен автомобилей в рублях отображаются проценты: какой процент каждый проданный автомобиль составил от общей суммы продаж всего автосалона за квартал. Проценты напротив фамилий менеджеров — их общий процент продаж в этом квартале.

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

Чтобы снова раскрыть данные об автомобилях — нажимаем +.

Чтобы значения снова выражались в рублях — через правый клик мыши возвращаемся в «Дополнительные вычисления» и выбираем «Без вычислений».

Шаг 5

Обновляем данные сводной таблицы

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

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

Переходим на лист сводной таблицы. Во вкладке «Анализ сводной таблицы» нажимаем кнопку «Изменить источник данных».

Кнопка переносит нас на лист исходной таблицы, где нужно выбрать новый диапазон. Добавляем в него две новые строки и жмём «ОК».

После этого данные в сводной таблице меняются автоматически: у менеджера Трегубова М. вместо восьми продаж становится десять.

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

Например, поменяем цены двух автомобилей в таблице с продажами.

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

Теперь у менеджера Соколова П. изменились данные в столбце «Цена, руб.».

Как использовать сводные таблицы в «Google Таблицах»? Нужно перейти во вкладку «Вставка» и выбрать параметр «Создать сводную таблицу». Дальнейший ход действий такой же, как и в Excel: выбрать диапазон таблицы и лист, на котором её нужно построить; затем перейти на этот лист и в окне «Редактор сводной таблицы» указать все требуемые настройки. Результат примет такой вид:

Другие материалы Skillbox Media для менеджеров

  • Руководство: как сделать ВПР в Excel и перенести данные из одной таблицы в другую
  • Статья с разбором диаграммы Ганта — что должен знать каждый менеджер
  • Подборка советов, как превратить хороший проект в великий, из книги Коллинза Good to Great
  • Рассказ о модели VUCA и о том, как она помогает процветать в хаосе
  • Подборка одиннадцати типичных ошибок при создании презентации

Исходная таблица — данные, которые сводная таблица собирает, группирует и формирует в отчёт.

Сводная по таблице с многострочной шапкой

Исходные данные

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

  • Вычислить отличие факта от плана по каждому товару за каждый год.
  • Сравнить между собой одинаковые кварталы за разные годы.
  • Посчитать суммарный или средний факт по каждому товару.
  • Сравнить продажи апельсинового сока разных наименований.

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

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

Одна проблема — в Microsoft Excel нельзя построить сводную таблицу, если у неё многострочная шапка, а именно это мы и имеем в нашем примере.

Нас выручит Power Query — мощная надстройка, встроенная в Excel, начиная с 2016-й версии (а для Excel 2010 и 2013 её можно бесплатно скачать с сайта Microsoft).

Шаг 1. Грузим данные в Power Query

Самый простой путь в нашем случае — это выделить весь диапазон с данными (начиная с ячейки B2 и до конца таблицы) и дать ему имя на вкладке Формулы — Задать имя (Formulas — Create name) . Давайте оригинально назовём его Данные, например:

Создаем именованный диапазон

Затем, проверив, что выделена вся таблица, выберем на вкладке Данные (или на вкладке Power Query, если вы установили её как отдельную надстройку на Excel 2010-2013), команду Из таблицы / Диапазона (From Table/Range) . Наша «красивая» таблица загрузится в редактор запросов Power Query:

Данные загружены в Power Query

Обратите внимание, что объединенные ячейки разъединились, а образовавшиеся в результате пустые ячейки теперь заполнены null — специальным словом, обозначающим в Power Query «пустоту» или отсутствие чего-либо.

Также Power Query попытался автоматически распознать типы данных в каждом столбце, добавив в правой панели Примененные шаги (Applied Steps) шаг Измененный тип (Changed Type) . Делать такое пока рановато, так что этот шаг можно смело удалить, щёлкнув по крестику слева от его названия:

Удаляем лишний шаг

Шаг 2. Преобразуем таблицу в плоскую

Дальнейшие преобразования будут состоять из нескольких операций:

    Во-первых, давайте избавимся от лишних пустых строк (см. строки 9 или 26, например) с помощью команды Главная — Удалить строки — Удалить пустые строки (Home — Remove rows — Remove empty rows) :

Удаляем пустые строки

Заполняем вниз

Склеиваем столбцы

Транспонируем

Поднимаем заголовки

Отмена свертывания

Разделяем слипшийся столбец

Переименовываем столбцы

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

Шаг 3. Строим сводную

На Главной вкладке Power Query выберем команду Закрыть и загрузить — Закрыть и загрузить в. (Home — Close & Load — Close & Load to. ) :

Выгружаем данные в Excel

В следующем окне можно пойти двумя путями:

Выбор места для выгрузки

Если у вас есть в этом окне опция Отчет сводной таблицы (Pivot Table Report) — смело выбирайте его и жмите на ОК. На новом листе будет создана сводная таблица по нашим данным из Power Query.

Если у вас старая версия Excel, то такой опции в этом окне может не быть. Тогда придется «идти в обход»:

  1. Выбираем вариант Только создать подключение (Create connection only) ,
  2. После возвращения в Excel на вкладке Вставка жмём кнопку Сводная таблица (Insert — Pivot Table)
  3. В окне создания сводной выбираем опцию Использовать внешний источник данных (Use external data source) .
  4. В открывшемся окне выбираем наше подключение из Power Query:

Строим сводную таблицу по подключению из Power Query

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

Готовая сводная таблица

Теперь наши данные можно крутить-вертеть-анализировать любым нужным образом легко и красиво 🙂

Ссылки по теме

  • Редизайн кросс-таблицы в плоскую
  • Сводная таблица по нескольким диапазонам данных
  • Настройка вычислений в сводной таблице

Добавление и расстановка данных сводной таблицы в Numbers на Mac

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

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

Добавление полей в сводную таблицу

  1. Выберите сводную таблицу.
  2. Во вкладке «Параметры сводки» боковой панели «Упорядочение» выполните любое из указанных ниже действий.
    • Автоматическое добавление полей к строкам, столбцам или значениям. Установите флажок напротив поля, которое нужно добавить. Поле отобразится в разделе «Столбцы», «Строки» или «Значения» в зависимости от типа данных в этом поле.
    • Добавление полей к строкам, столбцам или значениям вручную. Перетяните поле из списка в один из разделов.

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

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

Изменение порядка полей и их перемещение

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

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

Удаление полей

Можно удалить информацию из сводной таблицы, удалив отдельные поля из раздела (на вкладке «Параметры сводки») или удалив все поля в разделе. Удаление полей не влияет на исходные данные.

Меню параметров поля, отображающее элементы управления для группировки и сортировки данных, а также для удаления поля.

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

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *