#Руководства
-
0
Как систематизировать тысячи строк и преобразовать их в наглядный отчёт за несколько минут? Разбираемся на примере с квартальными продажами автосалона
Иллюстрация: Meery Mary для Skillbox Media
Рассказывает просто о сложных вещах из мира бизнеса и управления. До редактуры — пять лет в банке и три — в оценке имущества. Разбирается в Excel, финансах и корпоративной жизни.
Сводная таблица — инструмент для анализа данных в Excel. Она собирает информацию из обычных таблиц, обрабатывает её, группирует в блоки, проводит необходимые вычисления и показывает итог в виде наглядного отчёта. При этом все параметры этого отчёта пользователь может настроить под себя и свои потребности.
Разберёмся, для чего нужны сводные таблицы. На конкретном примере покажем, как их создать, настроить и использовать. В конце расскажем, можно ли делать сводные таблицы в «Google Таблицах».
Подписывайтесь на наш телеграм-канал «Котлер любит котиков | Школа маркетинга и управления Skillbox». Здесь мы подкидываем идеи для нестандартной рекламы, дарим книги, обсуждаем новости и важные темы для диджитал-специалистов и руководителей.
А ещё постим мемчики и вакансии, которые могут вас заинтересовать. Например, недавно выкладывали реальную вакансию директора по расслабону с зарплатой 200 000 рублей в месяц ?
Сводные таблицы удобно применять, когда нужно сформировать отчёт на основе большого объёма информации. Они суммируют значения, расположенные не по порядку, группируют данные из разных участков исходной таблицы в одном месте и сами проводят дополнительные расчёты.
Вид сводной таблицы можно настраивать под себя самостоятельно парой кликов мыши — менять расположение строк и столбцов, фильтровать итоги и переносить блоки отчёта с одного места в другое для лучшей наглядности.
Разберём на примере. Представьте небольшой автосалон, в котором работают три менеджера по продажам. В течение квартала данные об их продажах собирались в обычную таблицу: модель автомобиля, его характеристики, цена, дата продажи и Ф. И. О. продавца.
Скриншот: Skillbox Media
В конце квартала планируется выдача премий. Нужно проанализировать, кто принёс больше прибыли салону. Для этого нужно сгруппировать все проданные автомобили под каждым менеджером, рассчитать суммы продаж и определить итоговый процент продаж за квартал.
Разберёмся пошагово, как это сделать с помощью сводной таблицы.
Создаём сводную таблицу
Чтобы сводная таблица сработала корректно, важно соблюсти несколько требований к исходной:
- у каждого столбца исходной таблицы есть заголовок;
- в каждом столбце применяется только один формат — текст, число, дата;
- нет пустых ячеек и строк.
Теперь переходим во вкладку «Вставка» и нажимаем на кнопку «Сводная таблица».
Скриншот: Skillbox Media
Появляется диалоговое окно. В нём нужно заполнить два значения:
- диапазон исходной таблицы, чтобы сводная могла забрать оттуда все данные;
- лист, куда она перенесёт эти данные для дальнейшей обработки.
В нашем случае выделяем весь диапазон таблицы продаж вместе с шапкой. И выбираем «Новый лист» для размещения сводной таблицы — так будет проще перемещаться между исходными данными и сводным отчётом. Жмём «Ок».
Скриншот: Skillbox Media
Excel создал новый лист. Для удобства можно сразу переименовать его.
Слева на листе расположена область, где появится сводная таблица после настроек. Справа — панель «Поля сводной таблицы», в которые мы будем эти настройки вносить. В следующем шаге разберёмся, как пользоваться этой панелью.
Скриншот: Skillbox Media
Настраиваем сводную таблицу и получаем результат
В верхней части панели настроек находится блок с перечнем возможных полей сводной таблицы. Поля взяты из заголовков столбцов исходной таблицы: в нашем случае это «Марка, модель», «Цвет», «Год выпуска», «Объём», «Цена», «Дата продажи», «Продавец».
Нижняя часть панели настроек состоит из четырёх областей — «Значения», «Строки», «Столбцы» и «Фильтры». У каждой области своя функция:
- «Значения» — проводит вычисления на основе выбранных данных из исходной таблицы и относит результаты в сводную таблицу. По умолчанию Excel суммирует выбранные данные, но можно выбрать другие действия. Например, рассчитать среднее, показать минимум или максимум, перемножить.
Если данные выбранного поля в числовом формате, программа просуммирует их значения (например, рассчитает общую стоимость проданных автомобилей). Если формат данных текстовый — программа покажет количество ячеек (например, определит количество проданных авто).
- «Строки» и «Столбцы» — отвечают за визуальное расположение полей в сводной таблице. Если выбрать строки, то поля разместятся построчно. Если выбрать столбцы — поля разместятся по столбцам.
- «Фильтры» — отвечают за фильтрацию итоговых данных в сводной таблице. После построения сводной таблицы панель фильтров появляется отдельно от неё. В ней можно выбрать, какие данные нужно показать в сводной таблице, а какие — скрыть. Например, можно показывать продажи только одного из менеджеров или только за выбранный период.
Настроить сводную таблицу можно двумя способами:
- Поставить галочку напротив нужного поля — тогда Excel сам решит, где нужно разместить это значение в сводной таблице, и сразу заберёт его туда.
- Выбрать необходимые для сводной таблицы поля из перечня и перетянуть их в нужную область вручную.
Первый вариант не самый удачный: Excel редко ставит данные так, чтобы с ними было удобно работать, поэтому сводная таблица получается неинформативной. Остановимся на втором варианте — он предполагает индивидуальные настройки для каждого отчёта.
В случае с нашим примером нужно, чтобы сводная таблица отразила Ф. И. О. менеджеров по продаже, проданные автомобили и их цены. Остальные поля — технические характеристики авто и дату продажи — можно будет использовать для фильтрации.
Таблица получится наглядной, если фамилии менеджеров мы расположим построчно. Находим в верхней части панели поле «Продавец», зажимаем его мышкой и перетягиваем в область «Строки».
После этого в левой части листа появится первый блок сводной таблицы: фамилии менеджеров по продажам.
Скриншот: Skillbox
Теперь добавим модели автомобилей, которые эти менеджеры продали. По такому же принципу перетянем поле «Марка, модель» в область «Строки».
В левую часть листа добавился второй блок. При этом сводная таблица сама сгруппировала все автомобили по менеджерам, которые их продали.
Скриншот: Skillbox Media
Определяем, какая ещё информация понадобится для отчётности. В нашем случае — цены проданных автомобилей и их количество.
Чтобы сводная таблица самостоятельно суммировала эти значения, перетащим поля «Марка, модель» и «Цена» в область «Значения».
Скриншот: Skillbox Media
Теперь мы видим, какие автомобили продал каждый менеджер, сколько и по какой цене, — сводная таблица самостоятельно сгруппировала всю эту информацию. Более того, напротив фамилий менеджеров можно посмотреть, сколько всего автомобилей они продали за квартал и сколько денег принесли автосалону.
По такому же принципу можно добавлять другие поля в необходимые области и удалять их оттуда — любой срез информации настроится автоматически. В нашем примере внесённых данных в сводной таблице будет достаточно. Ниже рассмотрим, как настроить фильтры для неё.
Настраиваем фильтры сводной таблицы
Чтобы можно было фильтровать информацию сводной таблицы, нужно перенести требуемые поля в область «Фильтры».
В нашем примере перетянем туда все поля, не вошедшие в основной состав сводной таблицы: объём, дату продажи, год выпуска и цвет.
Скриншот: Skillbox Media
Для примера отфильтруем данные по году выпуска: настроим фильтр так, чтобы сводная таблица показала только проданные авто 2017 года.
В блоке фильтров нажмём на стрелку справа от поля «Год выпуска»:
Скриншот: Skillbox Media
В появившемся окне уберём галочку напротив параметра «Выделить все» и поставим её напротив параметра «2017». Закроем окно.
Скриншот: Skillbox Media
Теперь сводная таблица показывает только автомобили 2017 года выпуска, которые менеджеры продали за квартал. Чтобы снова показать таблицу в полном объёме, нужно в том же блоке очистить установленный фильтр.
Скриншот: Skillbox Media
Фильтры можно выбирать и удалять как удобно — в зависимости от того, какую информацию вы хотите увидеть в сводной таблице.
Проводим дополнительные вычисления
Сейчас в нашей сводной таблице все продажи менеджеров отображаются в рублях. Предположим, нам нужно понять, каков процент продаж каждого продавца в общем объёме. Можно рассчитать это вручную, а можно воспользоваться дополнениями сводных таблиц.
Кликнем правой кнопкой на любое значение цены в таблице. Выберем параметр «Дополнительные вычисления», затем «% от общей суммы».
Скриншот: Skillbox
Теперь вместо цен автомобилей в рублях отображаются проценты: какой процент каждый проданный автомобиль составил от общей суммы продаж всего автосалона за квартал. Проценты напротив фамилий менеджеров — их общий процент продаж в этом квартале.
Скриншот: Skillbox Media
Можно свернуть подробности с перечнями автомобилей, кликнув на знак – слева от фамилии менеджера. Тогда таблица станет короче, а данные, за которыми мы шли, — кто из менеджеров поработал лучше в этом квартале, — будут сразу перед глазами.
Скриншот: Skillbox Media
Чтобы снова раскрыть данные об автомобилях — нажимаем +.
Чтобы значения снова выражались в рублях — через правый клик мыши возвращаемся в «Дополнительные вычисления» и выбираем «Без вычислений».
Обновляем данные сводной таблицы
Предположим, в исходную таблицу внесли ещё две продажи последнего дня квартала.
Скриншот: Skillbox
В сводную таблицу эти данные самостоятельно не добавятся — изменился диапазон исходной таблицы. Поэтому нужно поменять первоначальные параметры.
Переходим на лист сводной таблицы. Во вкладке «Анализ сводной таблицы» нажимаем кнопку «Изменить источник данных».
Скриншот: Skillbox Media
Кнопка переносит нас на лист исходной таблицы, где нужно выбрать новый диапазон. Добавляем в него две новые строки и жмём «ОК».
Скриншот: Skillbox Media
После этого данные в сводной таблице меняются автоматически: у менеджера Трегубова М. вместо восьми продаж становится десять.
Скриншот: Skillbox Media
Когда в исходной таблице нужно изменить информацию в рамках текущего диапазона, данные в сводной таблице автоматически не изменятся. Нужно будет обновить их вручную.
Например, поменяем цены двух автомобилей в таблице с продажами.
Скриншот: Skillbox Media
Чтобы данные сводной таблицы тоже обновились, переходим на её лист и во вкладке «Анализ сводной таблицы» нажимаем кнопку «Обновить».
Теперь у менеджера Соколова П. изменились данные в столбце «Цена, руб.».
Скриншот: Skillbox Media
Как использовать сводные таблицы в «Google Таблицах»? Нужно перейти во вкладку «Вставка» и выбрать параметр «Создать сводную таблицу». Дальнейший ход действий такой же, как и в Excel: выбрать диапазон таблицы и лист, на котором её нужно построить; затем перейти на этот лист и в окне «Редактор сводной таблицы» указать все требуемые настройки. Результат примет такой вид:
Скриншот: Skillbox Media
Бесплатный курс: «Excel и „Google Таблицы“ для всех»
Начать учиться
Сводные таблицы – один из самых эффективных инструментов в MS Excel. С их помощью можно в считанные секунды преобразовать миллион строк данных в краткий отчет. Помимо быстрого подведения итогов, сводные таблицы позволяют буквально «на лету» изменять способ анализа путем перетаскивания полей из одной области отчета в другую.
Cводная таблица в Эксель – это также один из самых недооцененных инструментов. Большинство пользователей не подозревает, какие возможности находятся в их руках. Представим, что сводные таблицы еще не придумали. Вы работаете в компании, которая продает свою продукцию различным клиентам. Для простоты в ассортименте только 4 позиции. Продукцию регулярно покупает пара десятков клиентов, которые находятся в разных регионах. Каждая сделка заносится в базу данных и представляет отдельную строку.
Ваш директор дает указание сделать краткий отчет о продажах всех товаров по регионам (областям). Решить задачу можно следующим образом.
Вначале создадим макет таблицы, то есть шапку, состоящую из уникальных значений товаров и регионов. Сделаем копию столбца с товарами и удалим дубликаты. Затем с помощью специальной вставки транспонируем столбец в строку. Аналогично поступаем с областями, только без транспонирования. Получим шапку отчета.
Данную табличку нужно заполнить, т.е. просуммировать выручку по соответствующим товарам и регионам. Это нетрудно сделать с помощью функции СУММЕСЛИМН. Также добавим итоги. Получится сводный отчет о продажах в разрезе область-продукция.
Вы справились с заданием и показываете отчет директору. Посмотрев на таблицу, он генерирует сразу несколько замечательных идей.
— Можно ли отчет сделать не по выручке, а по прибыли?
— Можно ли товары показать по строкам, а регионы по столбцам?
— Можно ли такие таблицы делать для каждого менеджера в отдельности?
Даже если вы опытный пользователь Excel, на создание новых отчетов потребуется немало времени. Это уже не говоря о возможных ошибках. Однако если вы знаете, как сделать сводную таблицу в Эксель, то ответите: да, мне нужно 5 минут, возможно, меньше.
Рассмотрим, как создать сводную таблицу в Excel.
Открываем исходные данные. Сводную таблицу можно строить по обычному диапазону, но правильнее будет преобразовать его в таблицу Excel. Это сразу решит вопрос с автоматическим захватом новых данных. Выделяем любую ячейку и переходим во вкладку Вставить. Слева на ленте находятся две кнопки: Сводная таблица и Рекомендуемые сводные таблицы.
Если Вы не знаете, каким образом организовать имеющиеся данные, то можно воспользоваться командой Рекомендуемые сводные таблицы. Эксель на основании ваших данных покажет миниатюры возможных макетов.
Кликаете на подходящий вариант и сводная таблица готова. Остается ее только довести до ума, так как вряд ли стандартная заготовка полностью совпадет с вашими желаниями. Если же нужно построить сводную таблицу с нуля, или у вас старая версия программы, то нажимаете кнопку Сводная таблица. Появится окно, где нужно указать исходный диапазон (если активировать любую ячейку Таблицы Excel, то он определится сам) и место расположения будущей сводной таблицы (по умолчанию будет выбран новый лист).
Обычно ничего менять здесь не нужно. После нажатия Ок будет создан новый лист Excel с пустым макетом сводной таблицы.
Макет таблицы настраивается в панели Поля сводной таблицы, которая находится в правой части листа.
В верхней части панели находится перечень всех доступных полей, то есть столбцов в исходных данных. Если в макет нужно добавить новое поле, то можно поставить галку напротив – эксель сам определит, где должно быть размещено это поле. Однако угадывает далеко не всегда, поэтому лучше перетащить мышью в нужное место макета. Удаляют поля также: снимают флажок или перетаскивают назад.
Сводная таблица состоит из 4-х областей, которые находятся в нижней части панели: значения, строки, столбцы, фильтры. Рассмотрим подробней их назначение.
Область значений – это центральная часть сводной таблицы со значениями, которые получаются путем агрегирования выбранным способом исходных данных.
В большинстве случае агрегация происходит путем Суммирования. Если все данные в выбранном поле имеют числовой формат, то Excel назначит суммирование по умолчанию. Если в исходных данных есть хотя бы одна текстовая или пустая ячейка, то вместо суммы будет подсчитываться Количество ячеек. В нашем примере каждая ячейка – это сумма всех соответствующих товаров в соответствующем регионе.
В ячейках сводной таблицы можно использовать и другие способы вычисления. Их около 20 видов (среднее, минимальное значение, доля и т.д.). Изменить способ расчета можно несколькими способами. Самый простой, это нажать правой кнопкой мыши по любой ячейке нужного поля в самой сводной таблице и выбрать другой способ агрегирования.
Область строк – названия строк, которые расположены в крайнем левом столбце. Это все уникальные значения выбранного поля (столбца). В области строк может быть несколько полей, тогда таблица получается многоуровневой. Здесь обычно размещают качественные переменные типа названий продуктов, месяцев, регионов и т.д.
Область столбцов – аналогично строкам показывает уникальные значения выбранного поля, только по столбцам. Названия столбцов – это также обычно качественный признак. Например, годы и месяцы, группы товаров.
Область фильтра – используется, как ясно из названия, для фильтрации. Например, в самом отчете показаны продукты по регионам. Нужно ограничить сводную таблицу какой-то отраслью, определенным периодом или менеджером. Тогда в область фильтров помещают поле фильтрации и там уже в раскрывающемся списке выбирают нужное значение.
С помощью добавления и удаления полей в указанные области вы за считанные секунды сможете настроить любой срез ваших данных, какой пожелаете.
Посмотрим, как это работает в действии. Создадим пока такую же таблицу, как уже была создана с помощью функции СУММЕСЛИМН. Для этого перетащим в область Значения поле «Выручка», в область Строки перетащим поле «Область» (регион продаж), в Столбцы – «Товар».
В результате мы получаем настоящую сводную таблицу.
На ее построение потребовалось буквально 5-10 секунд.
Работа со сводными таблицами в Excel
Изменить существующую сводную таблицу также легко. Посмотрим, как пожелания директора легко воплощаются в реальность.
Заменим выручку на прибыль.
Товары и области меняются местами также перетягиванием мыши.
Для фильтрации сводных таблиц есть несколько инструментов. В данном случае просто поместим поле «Менеджер» в область фильтров.
На все про все ушло несколько секунд. Вот, как работать со сводными таблицами. Конечно, не все задачи столь тривиальные. Бывают и такие, что необходимо использовать более замысловатый способ агрегации, добавлять вычисляемые поля, условное форматирование и т.д. Но об этом в другой раз.
Источник данных сводной таблицы Excel
Для успешной работы со сводными таблицами исходные данные должны отвечать ряду требований. Обязательным условием является наличие названий над каждым полем (столбцом), по которым эти поля будут идентифицироваться. Теперь полезные советы.
1. Лучший формат для данных – это Таблица Excel. Она хороша тем, что у каждого поля есть наименование и при добавлении новых строк они автоматически включаются в сводную таблицу.
2. Избегайте повторения групп в виде столбцов. Например, все даты должны находиться в одном поле, а не разбиты по месяцам в отдельных столбцах.
3. Уберите пропуски и пустые ячейки иначе данная строка может выпасть из анализа.
4. Применяйте правильное форматирование к полям. Числа должны быть в числовом формате, даты должны быть датой. Иначе возникнут проблемы при группировке и математической обработке. Но здесь эксель вам поможет, т.к. сам неплохо определяет формат данных.
В целом требований немного, но их следует знать.
Обновление данных в сводной таблице Excel
Если внести изменения в источник (например, добавить новые строки), сводная таблица не изменится, пока вы ее не обновите через правую кнопку мыши
или
через команду во вкладке Данные – Обновить все.
Так сделано специально из-за того, что сводная таблица занимает много места в оперативной памяти. Чтобы расходовать ресурсы компьютера более экономно, работа идет не напрямую с источником, а с кэшем, где находится моментальный снимок исходных данных.
Зная, как делать сводные таблицы в Excel даже на таком базовом уровне, вы сможете в разы увеличить скорость и качество обработки больших массивов данных.
Ниже находится видеоурок о том, как в Excel создать простую сводную таблицу.
Скачать файл с примером.
Поделиться в социальных сетях:
Применяется кExcel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2024 Excel 2024 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2016 Microsoft365.com «Мой Office» для iPhone
Сводная таблица — это эффективный инструмент для вычисления, сведения и анализа данных, который упрощает поиск сравнений, закономерностей и тенденций. Сводные таблицы работают немного по-разному в зависимости от платформы, используемой для запуска Excel.
Если у вас есть правильные требования к лицензии, вы можете попросить Copilot помочь вам создать сводную таблицу.
-
Выделите ячейки, на основе которых вы хотите создать сводную таблицу.
Примечание: Данные должны быть упорядочены по столбцам с одной строкой заголовка. Дополнительные сведения см. в разделе Советы и рекомендации по формату данных.
-
На вкладке Вставка нажмите кнопку Сводная таблица.
-
При этом создается сводная таблица на основе существующей таблицы или диапазона.
Примечание: Если выбрать Добавить эти данные в модель данных , таблица или диапазон, используемые для этой сводной таблицы, добавляются в модель данных книги. Дополнительные сведения.
-
Выберите место размещения отчета сводной таблицы. Выберите Новый лист, чтобы разместить сводную таблицу на новом листе или существующем листе, а затем выберите место, где будет отображаться новая сводная таблица.
-
Нажмите кнопку ОК.
Щелкнув стрелку вниз на кнопке, можно выбрать из других возможных источников для сводной таблицы. Помимо использования существующей таблицы или диапазона, для заполнения сводной таблицы можно выбрать еще три источника.
Примечание: В зависимости от ит-параметров вашей организации в списке может отображаться имя вашей организации. Например, «Из Power BI (Майкрософт)».
Получение из внешнего источника данных
Получение из модели данных
Используйте этот параметр, если книга содержит модель данных и вы хотите создать сводную таблицу из нескольких таблиц, улучшить сводную таблицу с помощью настраиваемых мер или работать с очень большими наборами данных.
Получение из Power BI
Используйте этот параметр, если ваша организация использует Power BI и вы хотите обнаружить и подключиться к рекомендуемых облачных наборах данных, к которым у вас есть доступ.
-
Чтобы добавить поле в сводную таблицу, установите флажок рядом с именем поля в области Поля сводной таблицы.
Примечание: Выбранные поля будут добавлены в области по умолчанию: нечисловые поля — в область строк, иерархии значений дат и времени — в область столбцов, а числовые поля — в область значений.
-
Чтобы переместить поле из одной области в другую, перетащите его в целевую область.
При добавлении новых данных в источник необходимо обновить все основанные на нем сводные таблицы. Чтобы обновить только одну сводную таблицу, щелкните правой кнопкой мыши в любом месте диапазона сводной таблицы, а затем выберите Обновить. Если у вас несколько сводных таблиц, сначала выберите любую ячейку в любой сводной таблице, а затем на ленте перейдите к разделу Анализ сводной таблицы > щелкните стрелку под кнопкой Обновить , а затем выберите Обновить все.
Операция
По умолчанию поля сводной таблицы, размещенные в области Значения , отображаются в виде СУММ. Если Excel интерпретирует данные как текст, данные отображаются как COUNT. Вот почему так важно убедиться, что вы не смешиваете типы данных для полей значений. Вы можете изменить вычисление по умолчанию, сначала щелкнув стрелку справа от имени поля, а затем выберите параметр Параметры поля значения .
Затем измените функцию в разделе Операция. Обратите внимание, что при изменении метода вычисления Excel автоматически добавляет его в раздел Пользовательское имя , например «Sum of FieldName», но вы можете изменить его. Если выбран параметр Числовой формат, можно изменить числовой формат для всего поля.
Совет: Так как при изменении вычисления в разделе Суммирование значений по изменяется имя поля сводной таблицы, лучше не переименовывать поля сводной таблицы до завершения настройки сводной таблицы. Один из способов заключается в использовании функции Поиска & Замены (CTRL+H) >Найти то, что > «Сумма«, а затем заменить на > оставить пустым, чтобы заменить все сразу, а не вручную повторно.
Дополнительные вычисления
Значения можно также выводить в процентах от значения поля. В приведенном ниже примере мы изменили сумму расходов на % от общей суммы.
Открыв диалоговое окно Параметр поля значений , вы можете сделать выбор на вкладке Показать значения как .
Отображение значения как результата вычисления и как процента
Просто перетащите элемент в раздел Значения дважды, а затем задайте параметры Суммировать значения по и Показать значения как для каждого из них.
Советы и рекомендации по форматированию данных
-
Используйте чистые табличные данные для достижения наилучших результатов.
-
Упорядочение данных по столбцам, а не по строкам.
-
Убедитесь, что все столбцы имеют заголовки с одной строкой уникальных, непустых меток для каждого столбца. Избегайте двойных строк заголовков или объединенных ячеек.
-
Отформатируйте данные как таблицу Excel (выберите в любом месте данных, а затем на ленте выберите Вставить > таблицу ).
-
Если у вас есть сложные или вложенные данные, используйте Power Query для их преобразования (например, для отмены сворачивания данных), чтобы они были упорядочены по столбцам с одной строкой заголовка.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Рекомендации сводной таблицы являются частью работы с подключением в Microsoft 365 и анализируют данные с помощью служб искусственного интеллекта. Если вы решите отказаться от работы с подключением в Microsoft 365, данные не будут отправляться в службу искусственного интеллекта, и вы не сможете использовать рекомендации сводной таблицы. Дополнительные сведения см. в заявлении о конфиденциальности Майкрософт .
Статьи по теме
Создание сводной диаграммы
Использование срезов для фильтрации данных сводной таблицы
Создание временной шкалы сводной таблицы для фильтрации дат
Создание сводной таблицы с моделью данных для анализа данных в нескольких таблицах
Создание сводной таблицы, подключенной к наборам данных Power BI
Упорядочение полей сводной таблицы с помощью списка полей
Изменение исходных данных сводной таблицы
Вычисление значений в сводной таблице
Удаление сводной таблицы
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
Наверняка в вакансиях вы видели похожее требование: «Знание Excel на уровне продвинутого пользователя (ВПР, сводные таблицы, формулы)». Звучит сложно, но на самом деле освоить эти функции довольно легко. Начнем со сводных таблиц: разберемся, что это такое, зачем они нужны и как их делать.
Что такое сводная таблица Excel и почему это удобно
Сводная таблица в Excel — это мощный инструмент для анализа и обработки больших объемов данных. Он помогает понять, как данные соотносятся друг с другом, выявить тенденции, закономерности, сгруппировать их по выбранным критериям и показать результат. Таблица позволяет быстро создать сводный отчет из сотен тысяч строк данных за несколько кликов.
Сводные таблицы создал разработчик Пито Салас в 1986 году. Разрабатывая программу для работы с электронными таблицами Lotus Improv, он начал замечать в данных закономерности, которые возникали при объединении разных таблиц. Он понял, что это может быть полезным инструментом для анализа данных, и создал сводные таблицы. Microsoft добавила функциональность сводных таблиц в Excel только в 1994 году. С тех пор на сводных таблицах держится многое в самых разных отраслях: финансах, маркетинге, продажах, закупках.
Например, маркетолог составляет маркетинговый план, в котором подробно описывает продвижение в разных каналах. Чтобы руководитель мог быстро и легко оценить эффективность этого плана, маркетолог создает сводную таблицу, которая объединяет информацию из всех задействованных документов. В этой таблице можно сравнить доходы, расходы и эффективность разных каналов в одном месте, без необходимости переключаться между документами.
Сводные таблицы можно использовать даже для анализа личного бюджета. Например, чтобы посмотреть, сколько денег потратили на каждую категорию товаров в течение года, вы можете создать сводную таблицу, где строки будут группироваться по категории товаров, а столбцы — по месяцам.
Как сделать простую сводную таблицу
Начинаем работу
Попробуем сделать простую сводную таблицу. В нашем примере будет небольшая IT-компания, которая занимается разработкой на заказ. Проектов много, поэтому без сводных таблиц не обойтись. В нашей таблице будут колонки «Название проекта», «Тип проекта», «Команда», которая занимается этим проектом, «Бюджет», который выделил заказчик, и «Срок (в месяцах)» — время, которое уйдет на выполнение проекта.
Чтобы создать сводную таблицу, перейдите в раздел «Вставка» и выберите «Сводная таблица».
Откроется окно создания сводной таблицы. По умолчанию оно выглядит так:
Выберем нужную нам таблицу. Чтобы это сделать, прописываем название листа (у нас «Проекты») и диапазон, который занимает таблица. Ячейки можно прописать вручную или выделить нужную область мышкой, тогда диапазон пропишется автоматически. Удобнее создать сводную таблицу на новом листе — так данные источника не будут вам мешать.
Нажимаем «Ок» и видим, что мы оказались на новом листе. Справа появился конструктор сводных таблиц. В основной области перечислены названия столбцов, которые были в нашей исходной таблице. Их можно выбирать — ставить галочки рядом или переносить мышкой. Фильтры помогут скрыть или отобразить нужные данные, столбцы и строки отвечают за внешний вид таблицы: данные будут разделены по столбцам или строкам соответственно, а значения нужны для вычисления данных.
Настраиваем сводную таблицу
Допустим, руководитель нашей компании хочет посмотреть, какие проекты ведут команды. Для этого он выбирает столбцы «Команда» и «Название проекта». По умолчанию они попадают в поле «Строки».
Теперь наша сводная таблица выглядит так:
Если выбрать сначала «Название проекта», а потом «Команда», таблица выглядит совсем по-другому. Порядок выбора столбцов важен.
Посмотрим на фильтры. Наша исходная таблица небольшая, команд немного, поэтому вывести всё и сразу довольно удобно. Если бы команд или проектов было больше, то такое отображение не подошло бы. Здесь выручат фильтры. Например, можно добавить «Команду» в поле «Фильтры».
Тогда все наши команды станут пунктами выпадающего меню, а проекты будут располагаться ниже и только для выбранной команды. Красиво, удобно, наглядно.
А что, если руководитель захочет посмотреть, сколько заработает каждая команда на проектах? Выбираем «Команда», выбираем «Бюджет». Обратите внимание, что «Команда», как обычно, оказалась в поле «Строки», а вот «Бюджет» попал в «Значения» автоматически, потому что в этом столбце были записаны числа.
Если мы перенесем «Команду» в «Столбцы», то увидим, что внешний вид таблицы изменился. Выбор внешнего вида зависит от личных предпочтений и от цели создания таблицы.
При работе с числовыми значениями часто применяются фильтры. Например, так наша IT-компания может узнать, какое из направлений ей приносит больше денег. Для этого нужно переместить «Тип проекта» в поле «Фильтры», а «Бюджет» сам попадет в «Значения». Теперь можно выбирать направление и смотреть, какое из них самое выгодное для компании.
Можно перенести «Тип проекта» в «Столбцы» и увидеть сразу все направления в одной таблице.
Так создаются простые сводные таблицы. Если необходимо выполнить более глубокий анализ данных, можно использовать более сложные варианты. Они позволяют работать с данными в различных измерениях, выполнять группировку, вычислять проценты, средние значения и многое другое.
Как обновить данные в сводной таблице
Что делать, если исходные данные изменились? Например, на какой-то проект назначили Team 2, а не Team 1? Перейдите в раздел «Анализ сводной таблицы» и нажмите «Обновить». Никаких подтверждающих окон не появится, но актуальные данные подтянутся в вашу сводную таблицу.
Если же ваша таблица изменилась существенно, например добавились новая строка или новый столбец, то есть изменился ее диапазон, нужно выбрать новый. Для этого перейдите в раздел «Анализ сводной таблицы» и нажмите «Источник данных». Вы окажетесь на листе с вашими исходными данными. Здесь появится окно, в котором вы сможете указать новый диапазон (или выделить нужную область мышкой). Нажмите «Ок» и продолжайте работу над сводной таблицей.
Как сделать сводную таблицу из нескольких листов
Сводные таблицы позволяют объединять данные из нескольких источников в одну таблицу для удобного анализа и сравнения. Например у вас есть информация, которая хранится на разных листах. В сводной таблице можно просматривать данные, не переключаясь между вкладками Excel.
Предположим, у нас есть еще одна таблица на другом листе — с тимлидами (руководителями команд). Компания у нас маленькая, поэтому руководители команд отвечают за проекты, которые ведет их команда.
Руководитель решает посмотреть, за сколько проектов сейчас отвечает каждый тимлид. Здесь пригодится сводная таблица. Но для начала нужно изменить наши таблицы — сделать их «умными». Для этого в разделе «Вставка» нужно выбрать «Таблица».
После этого — выделить нужный диапазон или прописать расположение данных вручную. Обязательно выбирайте опцию «Таблица с заголовками». Так вы сможете изменить название таблицы с системного «Таблица1» на свое.
Делаем это для обеих таблиц. Теперь они выглядят так:
После этого создаем сводную таблицу для наших исходных данных: таблицы с проектами. Обязательно ставим галочку в окошке «Добавить эти данные в модель данных». Мы видим уже привычный конструктор сводных таблиц. Но как же связать данные из наших двух таблиц? Обратимся к конструктору справа. Перейдем в раздел «Все».
Увидим обе наши таблицы! «Проекты» и «Тимлиды» — это названия, которые мы придумали.
Кликаем на каждый, раскрываем списки и видим названия столбцов. Чтобы посмотреть нагрузку на тимлидов, в списке «Проекты» мы выберем столбец «Название проекта», в списке «Тимлиды» — столбец «Тимлид». Разместим их в полях «Столбцы», чтобы придать таблице нужный вид, и в поле «Значение», чтобы автоматически посчитать количество проектов. У нас появляется желтое поле вверху.
Если выбрать «Автообнаружение», Excel сам поймет, что мы хотим сделать. Но при сложных процедурах это может не сработать. Выберем «Создать» и сделаем все вручную.
Открылось окошко создания отношений. В «Таблица» выберем основную таблицу (с проектами), в «Связанная таблица» — второй лист (с тимлидами). В «Столбец» и «Связанный столбец» должны быть одни и те же столбцы. Чтобы связать две таблицы, в них должен быть общий столбец, по которому и будет создано отношение. У нас это «Команда».
Вуаля! После нажатия «Ок» увидим желаемую таблицу и поймем, что тимлид Петров А. загружен меньше всех и мы можем дать ему еще проектов!
Полезные функции сводной таблицы
Расскажем про несколько функций, которые могут быть полезны при работе со сводными таблицами.
Вычисляемые поля
Эта функция нужна для дополнительных математических действий с данными сводной таблицы в Excel. Допустим, в компании принято, что 3% от всего бюджета команды идет на ее расходы: пятничные посиделки с пиццей, подарки на дни рождения и прочие маленькие радости. Это тоже можно посчитать в сводных таблицах. Сделаем распределение бюджетов по командам, так же как в прошлом примере.
В разделе «Анализ сводной таблицы» нужно найти выпадающее меню «Поля, элементы и наборы». Кликаем и выбираем «Вычисляемое поле».
Появляется окно, в котором можно написать имя нового столбца, у нас это «Расходы команды», и выбрать столбцы, которые понадобятся для расчета нового. Мы выбираем «Бюджет» (он сам подставится в поле «Формула») и дописываем умножение на 0,03 (3% от бюджета).
После нажатия «Ок» в нашей сводной таблице появился новый столбец. Все посчитано!
Рекомендуемые таблицы
Рядом с иконкой создания сводной таблицы есть и другая опция — «Рекомендуемые сводные таблицы». Эта функция предлагает шаблоны сводных таблиц для данных. Будет полезна тем, кто начинает работать со сводными таблицами. Можно не только сделать то, что нужно, но и посмотреть, в каких полях конструктора расположены разные столбцы, и глубже понять принцип работы сводных таблиц.
Подводя итог
В этой статье мы рассмотрели, как создать сводную таблицу в Excel. Если что-то не получилось сразу, не расстраивайтесь, попробуйте еще раз. И через некоторое время вы сможете сказать: «Я люблю Excel!».
Напоследок несколько советов, которые помогут вам использовать сводные таблицы более эффективно:
- Продумайте, какие данные вы хотите проанализировать. Что вы хотите узнать? Какая информация вам нужна для этого?
- Выберите правильные поля для вашей сводной таблицы. Они определяют, как будут группироваться ваши данные и какие вычисления будут выполняться.
- Используйте фильтры и сортировку, чтобы настроить свою сводную таблицу. Фильтры и сортировка могут помочь вам сосредоточиться на конкретных данных или увидеть данные в новом свете.
- Добавьте диаграммы и графики к своей сводке. Они помогут визуализировать данные и сделать их более понятными.
А если захотите пойти дальше в освоении Excel — можете познакомиться с курсом «Excel + Google-таблицы».
Сводная таблица помогает быстро обработать большой объем данных и найти взаимосвязи. Например, в каком месяце удалось сэкономить больше всего денег или на какие категории расходов приходятся основные траты. Расскажем, как сводные таблицы помогут с личным финансовым учетом.
Вместо Excel мы пользуемся сервисом Google Sheets, потому что он бесплатный и работает в онлайне: можно заходить с компьютера и телефона. Если Excel привычнее — никаких проблем, в нем те же функции.
Что такое сводная таблица
Это стандартный инструмент Excel или Google Sheets для анализа данных. Он облегчает жизнь тем, кто давно ведет финансовый учет и накопил сотни, а то и тысячи строк с данными. Сводная таблица собирает все эти данные, группирует их, проводит нужные вычисления и готовит компактный отчет.
Как может выглядеть такая таблица:
В ней подсчитаны расходы по дням за целый год — в процентном соотношении и абсолютных суммах. И все это за несколько кликов.
Что нужно сделать перед созданием сводной таблицы
Чтобы сводная таблица работала, нужно подготовить данные:
- Добавить заголовки. У каждого столбика таблицы должен быть заголовок, например «День недели» и «Расходы».
- Привести форматы к единообразию. В каждом столбике могут быть данные лишь одного формата, например только названия или только суммы.
- Заполнить все. В таблице не может быть пустых ячеек или строк.
Вот пример таблицы, которая отвечает всем правилам:
Допустим, вы записываете расходы посуточно и хотите узнать, в какие дни тратите больше всего денег. В примере выше это несложно сделать на калькуляторе. Но что делать, если вы вели учет целый год и таблица состоит из 365 строк? Здесь и поможет сводная таблица, давайте создадим такую за два простых шага.
Шаг 1. Создаем таблицу
Нажмите Вставка → Создать сводную таблицу. Появится окошко, в котором нужно сначала выбрать диапазон данных. Это все ячейки, в которых находятся нужные нам данные, а именно дни недели и суммы расходов. Чтобы выбрать диапазон, нажмите на изображение таблицы:
Диапазон можно ввести вручную, но проще довериться рекомендациям Google Sheets и выбрать первый вариант в списке:
Нажмите ОК → Создать, таблица появится на новом листе. Перейдем к настройкам.
Шаг 2. Настраиваем таблицу
Добавим в таблицу нужные данные. Дни недели будут строками, а суммы расходов — значениями:
Вот так за несколько кликов видим, что в течение рабочей недели и в субботу расходы равномерны, а по воскресеньям тратится на 20–25% больше, чем в остальные дни:
В сводной таблице дни недели и другие текстовые значения отображаются в алфавитном порядке.
Частые вопросы
Как обновлять сводную таблицу?
Таблица не обновляется автоматически. Если вы продолжаете вести финансовый учет и вносите дополнительные расходы, они не будут отображаться в сводной таблице, потому что на первом шаге мы ограничили диапазон. Чтобы добавить новые строки в таблицу, нужно изменить диапазон:
Как учитывать доходы от инвестиций?
При создании сводной таблицы важно, чтобы в ней не было пустых ячеек и строк, иначе ничего не сработает. В инвестициях такая ситуация невозможна, потому что доход по некоторым финансовым инструментам не поступает каждый день: например, по депозитам или сдаче недвижимости. Поскольку строки нельзя оставлять пустыми, нужно каждый раз вводить ноль в графу доходов.
Что важно запомнить
- Сводная таблица помогает быстро проанализировать большой объем данных и собрать отчет в компактном виде.
- Таблицу нужно заполнять по правилам — иначе в результате будут ошибки.
- Обновлять сводную таблицу можно только вручную.
Excel для личных финансов