#Руководства
-
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 Таблицы» с нуля до PRO»
Узнать о курсе
Сводная таблица помогает быстро обработать большой объем данных и найти взаимосвязи. Например, в каком месяце удалось сэкономить больше всего денег или на какие категории расходов приходятся основные траты. Расскажем, как сводные таблицы помогут с личным финансовым учетом.
Вместо Excel мы пользуемся сервисом Google Sheets, потому что он бесплатный и работает в онлайне: можно заходить с компьютера и телефона. Если Excel привычнее — никаких проблем, в нем те же функции.
Что такое сводная таблица
Это стандартный инструмент Excel или Google Sheets для анализа данных. Он облегчает жизнь тем, кто давно ведет финансовый учет и накопил сотни, а то и тысячи строк с данными. Сводная таблица собирает все эти данные, группирует их, проводит нужные вычисления и готовит компактный отчет.
Как может выглядеть такая таблица:
В ней подсчитаны расходы по дням за целый год — в процентном соотношении и абсолютных суммах. И все это за несколько кликов.
Что нужно сделать перед созданием сводной таблицы
Чтобы сводная таблица работала, нужно подготовить данные:
- Добавить заголовки. У каждого столбика таблицы должен быть заголовок, например «День недели» и «Расходы».
- Привести форматы к единообразию. В каждом столбике могут быть данные лишь одного формата, например только названия или только суммы.
- Заполнить все. В таблице не может быть пустых ячеек или строк.
Вот пример таблицы, которая отвечает всем правилам:
Допустим, вы записываете расходы посуточно и хотите узнать, в какие дни тратите больше всего денег. В примере выше это несложно сделать на калькуляторе. Но что делать, если вы вели учет целый год и таблица состоит из 365 строк? Здесь и поможет сводная таблица, давайте создадим такую за два простых шага.
Шаг 1. Создаем таблицу
Нажмите Вставка → Создать сводную таблицу. Появится окошко, в котором нужно сначала выбрать диапазон данных. Это все ячейки, в которых находятся нужные нам данные, а именно дни недели и суммы расходов. Чтобы выбрать диапазон, нажмите на изображение таблицы:
Диапазон можно ввести вручную, но проще довериться рекомендациям Google Sheets и выбрать первый вариант в списке:
Нажмите ОК → Создать, таблица появится на новом листе. Перейдем к настройкам.
Шаг 2. Настраиваем таблицу
Добавим в таблицу нужные данные. Дни недели будут строками, а суммы расходов — значениями:
Вот так за несколько кликов видим, что в течение рабочей недели и в субботу расходы равномерны, а по воскресеньям тратится на 20–25% больше, чем в остальные дни:
В сводной таблице дни недели и другие текстовые значения отображаются в алфавитном порядке.
Частые вопросы
Как обновлять сводную таблицу?
Таблица не обновляется автоматически. Если вы продолжаете вести финансовый учет и вносите дополнительные расходы, они не будут отображаться в сводной таблице, потому что на первом шаге мы ограничили диапазон. Чтобы добавить новые строки в таблицу, нужно изменить диапазон:
Как учитывать доходы от инвестиций?
При создании сводной таблицы важно, чтобы в ней не было пустых ячеек и строк, иначе ничего не сработает. В инвестициях такая ситуация невозможна, потому что доход по некоторым финансовым инструментам не поступает каждый день: например, по депозитам или сдаче недвижимости. Поскольку строки нельзя оставлять пустыми, нужно каждый раз вводить ноль в графу доходов.
Что важно запомнить
- Сводная таблица помогает быстро проанализировать большой объем данных и собрать отчет в компактном виде.
- Таблицу нужно заполнять по правилам — иначе в результате будут ошибки.
- Обновлять сводную таблицу можно только вручную.
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, желающие освоить сводные таблицы
- бизнес-аналитики и специалисты, работающие с данными
- карьерно ориентированные люди, рассматривающие профессию аналитика данных
Вы когда-нибудь чувствовали себя потерянным перед огромной таблицей с данными, которые нужно срочно проанализировать? 📊 Или сидели часами, пытаясь вручную суммировать сотни строк в Excel? Сводные таблицы — это именно тот инструмент, который избавит вас от подобных мучений. Этот пошаговый самоучитель раскроет все секреты сводных таблиц даже для тех, кто только начинает свой путь в мире Excel. Превратите свои хаотичные данные в четкие инсайты за считанные минуты!
Устали от бесконечных формул и ручных расчетов? Курс «Excel для работы» с нуля от Skypro поможет вам овладеть не только сводными таблицами, но и десятками других мощных инструментов Excel! Наши студенты экономят до 3 часов рабочего времени ежедневно благодаря автоматизации рутинных задач. Присоединяйтесь к тысячам профессионалов, которые уже изменили свой подход к работе с данными!
Что такое сводные таблицы и почему они важны в Excel
Сводные таблицы — это мощный инструмент анализа данных в Excel, позволяющий быстро обрабатывать большие массивы информации и представлять их в структурированном виде. По сути, они выполняют роль универсального анализатора, который умеет группировать, фильтровать и вычислять данные без использования сложных формул.
Представьте, что у вас есть таблица с тысячами строк продаж, содержащая информацию о продавцах, товарах, регионах и суммах. Без сводной таблицы вам потребуется создавать отдельные формулы для подсчета продаж по каждому региону или продавцу. Со сводной таблицей вы получите эти результаты за пару кликов. 🚀
Иван Петров, руководитель аналитического отдела
Когда я пришел в компанию по производству электроники, отдел маркетинга тратил два полных дня в конце каждого месяца на составление отчетов по эффективности рекламных каналов. Файл содержал данные о 16 продуктах, 8 рекламных каналах и продажах в 12 регионах.
В первую неделю я создал простую сводную таблицу, которая автоматически обновлялась при добавлении новых данных. Она позволяла мгновенно переключаться между различными срезами информации — от общих продаж до детализации по конкретным продуктам и каналам.
Результат превзошел ожидания: время на составление отчетов сократилось с двух дней до одного часа. Более того, руководство получило возможность видеть не только «сухие цифры», но и тренды, которые раньше оставались незамеченными. Именно эти инсайты помогли перераспределить рекламный бюджет и увеличить продажи на 18% за квартал.
Почему сводные таблицы незаменимы в работе аналитика и бизнес-пользователя:
- Экономия времени — анализируйте тысячи строк данных за считанные минуты
- Гибкость настройки — меняйте параметры анализа одним перетаскиванием полей
- Визуализация тенденций — мгновенно выявляйте закономерности и аномалии
- Интерактивность — применяйте фильтры и срезы для углубленного анализа
- Автоматическое обновление — пересчет при изменении исходных данных
Согласно исследованию Forrester, специалисты, активно использующие сводные таблицы, экономят до 28 часов рабочего времени ежемесячно. Это почти целая рабочая неделя! 😮
Создание первой сводной таблицы из исходных данных
Создание сводной таблицы — процесс, требующий точного следования нескольким простым шагам. Важнейшее условие: исходные данные должны быть организованы в виде списка или таблицы без пустых строк и столбцов. Каждый столбец должен иметь заголовок.
Давайте разберем пошаговый алгоритм создания вашей первой сводной таблицы на примере отчета по продажам:
- Подготовьте данные — убедитесь, что ваша таблица имеет заголовки и не содержит пустых строк
- Выделите диапазон данных — кликните в любую ячейку таблицы и нажмите Ctrl+A для выделения всего диапазона
- Перейдите на вкладку «Вставка» в ленте Excel и выберите «Сводная таблица»
- Проверьте диапазон — Excel обычно сам определяет нужный диапазон, но лучше перепроверить
- Выберите расположение создаваемой сводной таблицы — новый лист или текущий лист
- Нажмите «ОК» — после этого появится пустая структура сводной таблицы и панель полей
После этих шагов вы увидите макет сводной таблицы и список полей справа. Именно здесь начинается самое интересное — формирование структуры анализа! 🧩
Мария Соколова, бизнес-аналитик
Недавно мне позвонила подруга, владелица небольшого интернет-магазина косметики. Она была в отчаянии: «У меня 6000 строк с продажами за два года, но я не понимаю, какие товары приносят больше прибыли, а какие нужно снимать с продажи.»
Мы встретились в кафе с ее ноутбуком. За чашкой кофе я показала ей, как создать первую сводную таблицу. Мы перетащили поле «Наименование товара» в область строк, «Дата» в фильтры, а «Прибыль» в область значений.
В считанные минуты она увидела полную картину своего бизнеса. Оказалось, что 20% товаров приносили 80% прибыли, а некоторые продукты фактически продавались в убыток из-за высоких затрат на рекламу. Это был настоящий момент прозрения!
Через три месяца она сообщила, что оптимизировала ассортимент, сократив количество наименований на 30%, но увеличив общую прибыль магазина на 22%. Всё благодаря одной сводной таблице и правильной интерпретации данных.
Вот какие элементы сводной таблицы доступны для настройки:
- Фильтры — позволяют отбирать данные по нужным критериям
- Строки — определяют, какие данные будут организованы по вертикали
- Столбцы — определяют, какие данные будут организованы по горизонтали
- Значения — числовые данные, которые нужно анализировать (суммы, количество, среднее и т.д.)
Для создания базовой сводной таблицы, отображающей продажи по категориям товаров:
- Перетащите поле «Категория» в область «Строки»
- Перетащите поле «Сумма продажи» в область «Значения»
- По умолчанию Excel суммирует числовые значения, но вы можете изменить это, щелкнув по полю правой кнопкой мыши
Вот и всё — ваша первая сводная таблица готова! 🎉 Теперь вы видите сумму продаж по каждой категории товаров.
Настройка полей и структуры сводных таблиц в Excel
Настройка полей — ключевой этап, определяющий аналитическую ценность создаваемой сводной таблицы. Excel предоставляет гибкие возможности для детальной настройки каждого элемента структуры. Рассмотрим базовые и продвинутые приемы настройки.
Прежде всего, необходимо определить, какую информацию вы хотите получить. Возможные сценарии анализа:
- Суммарные продажи по регионам
- Сравнение производительности сотрудников
- Динамика показателей по месяцам
- Соотношение доходов и расходов по категориям
В зависимости от задачи, вы будете по-разному настраивать поля сводной таблицы. Давайте разберем основные области настройки:
Форматирование и визуализация данных в сводных таблицах
Грамотное форматирование превращает сухие цифры в наглядную информацию, позволяющую принимать взвешенные решения. Excel предлагает разнообразные инструменты для визуального оформления сводных таблиц, делая их понятными даже для неподготовленного пользователя. 📈
Начать следует с базового форматирования:
- Выделите сводную таблицу (или её часть)
- На вкладке «Конструктор» выберите один из готовых стилей
- Для более точной настройки используйте вкладку «Формат» или контекстное меню
Ключевые элементы форматирования, на которые стоит обратить внимание:
- Числовые форматы — задайте корректное отображение валют, процентов или дат
- Условное форматирование — выделите важные значения цветом или значками
- Пользовательские форматы — создайте собственные шаблоны отображения данных
- Стили и темы — придайте таблице профессиональный вид в соответствии с корпоративными стандартами
Для настройки числового формата:
- Щелкните правой кнопкой мыши по полю значений
- Выберите «Параметры поля значений»
- Перейдите на вкладку «Формат ячеек» и выберите нужный формат
Условное форматирование позволяет визуально выделять важные тенденции:
- Выделите диапазон ячеек со значениями
- На вкладке «Главная» выберите «Условное форматирование»
- Выберите подходящий вариант (цветовые шкалы, гистограммы, наборы значков)
Для создания наглядных диаграмм на основе сводной таблицы:
- Выделите сводную таблицу или её часть
- Перейдите на вкладку «Вставка»
- Выберите подходящий тип диаграммы
- Используйте «Сводная диаграмма» для создания интерактивной визуализации
Сводные диаграммы наследуют интерактивность сводных таблиц — при изменении фильтров или структуры таблицы, диаграмма автоматически обновляется. Это делает их мощным инструментом для презентаций и отчетов. 📊
Для улучшения восприятия больших массивов данных используйте:
- Группировку данных — объединяйте даты по кварталам или товары по категориям
- Сортировку — располагайте данные в порядке убывания или возрастания для выявления лидеров и аутсайдеров
- Срезы — добавляйте интерактивные фильтры для быстрого переключения между различными представлениями данных
- Временные шкалы — используйте для фильтрации по временным периодам
Чтобы добавить срезы:
- Выделите любую ячейку в сводной таблице
- На вкладке «Анализ» (или «Параметры») выберите «Вставить срез»
- Выберите поля, по которым хотите фильтровать данные
Задумываетесь о смене профессии, но не знаете, подойдет ли вам карьера аналитика? Пройдите Тест на профориентацию от Skypro и узнайте, насколько ваши склонности соответствуют работе с данными и аналитическими инструментами вроде сводных таблиц. Тест разработан экспертами по карьерному развитию и поможет определить ваши сильные стороны. Получите персональные рекомендации по развитию навыков Excel уже через 5 минут!
Продвинутые приемы работы со сводными таблицами
Овладев базовыми навыками создания и настройки сводных таблиц, можно переходить к продвинутым техникам, которые превращают Excel в полноценный инструмент бизнес-аналитики. Эти приемы позволят вам извлекать максимум пользы из ваших данных. 🧠
Начнем с вычисляемых полей и элементов:
- Вычисляемые поля — создают новые метрики на основе существующих данных
- Вычисляемые элементы — позволяют комбинировать существующие элементы сводной таблицы
Для создания вычисляемого поля:
- Выделите любую ячейку в сводной таблице
- На вкладке «Анализ» выберите «Поля, элементы и наборы» → «Вычисляемое поле»
- Введите имя нового поля и формулу, например: =Доход-Расход
Пример практического применения: создание поля «Маржинальность» по формуле =(Доход-Расход)/Доход. Это позволит оценивать эффективность каждого направления бизнеса.
Мощной возможностью сводных таблиц являются функции группировки и детализации:
- Группировка дат — автоматическое объединение по дням, месяцам, кварталам, годам
- Группировка числовых значений — разбиение на диапазоны (возрастные группы, ценовые категории)
- Пользовательская группировка — создание собственных логических групп
Для группировки дат:
- Щелкните правой кнопкой мыши по полю с датами в сводной таблице
- Выберите «Группировать»
- Отметьте нужные периоды (месяцы, кварталы, годы)
Для группировки числовых данных:
- Выделите числовое поле в строках или столбцах
- Щелкните правой кнопкой мыши и выберите «Группировать»
- Задайте начальное значение, конечное значение и размер интервала
Рассмотрим также продвинутые методы анализа данных:
Особый интерес представляют показатели с накапливаемыми итогами и процентными отношениями. Для их настройки:
- Щелкните правой кнопкой мыши по полю значений
- Выберите «Показать значения как»
- Выберите нужный тип расчета: «% от суммы по столбцу», «нарастающий итог», «разница с предыдущим» и т.д.
Наиболее полезные варианты отображения значений:
- % от общей суммы — показывает долю каждого элемента
- % от родительской суммы — доля внутри вышестоящей группы
- Разница с — абсолютная разница с выбранным элементом
- % отношения к — процентное отношение к выбранному элементу
- Нарастающий итог — накопительная сумма по списку
Для обновления данных сводной таблицы при изменении исходного диапазона:
- Преобразуйте исходные данные в Таблицу Excel (Ctrl+T)
- Создайте сводную таблицу на основе этой Таблицы
- При добавлении новых строк в Таблицу, обновляйте сводную таблицу кнопкой «Обновить» или клавишей Alt+F5
Интеграция с внешними источниками данных открывает новые горизонты:
- Power Query — позволяет обрабатывать и очищать данные перед анализом
- Power Pivot — обеспечивает работу с миллионами строк и создание сложных моделей данных
- Внешние подключения — импорт данных из баз данных, веб-сервисов и других источников
Эти продвинутые техники превращают Excel из обычного табличного процессора в мощный инструмент бизнес-аналитики, способный конкурировать со специализированными BI-платформами. 🚀
Сводные таблицы в Excel — это не просто функция для обобщения данных, а мощный инструмент, способный превратить информационный хаос в упорядоченные знания. Начав с простых сумм по категориям, вы можете постепенно освоить навыки создания многомерных отчетов, интерактивных дашбордов и предиктивных моделей. Регулярная практика — ключ к мастерству. Применяйте полученные знания к реальным задачам, экспериментируйте с настройками и не бойтесь ошибок. Помните: профессионалом становится не тот, кто знает все функции наизусть, а тот, кто умеет находить оптимальные решения для конкретных задач.