#Руководства
- 13 май 2022
-
0
Как систематизировать тысячи строк и преобразовать их в наглядный отчёт за несколько минут? Разбираемся на примере с квартальными продажами автосалона
Иллюстрация: Meery Mary для Skillbox Media
Рассказывает просто о сложных вещах из мира бизнеса и управления. До редактуры — пять лет в банке и три — в оценке имущества. Разбирается в Excel, финансах и корпоративной жизни.
Сводная таблица — инструмент для анализа данных в Excel. Она собирает информацию из обычных таблиц, обрабатывает её, группирует в блоки, проводит необходимые вычисления и показывает итог в виде наглядного отчёта. При этом все параметры этого отчёта пользователь может настроить под себя и свои потребности.
Разберёмся, для чего нужны сводные таблицы. На конкретном примере покажем, как их создать, настроить и использовать. В конце расскажем, можно ли делать сводные таблицы в «Google Таблицах».
Сводные таблицы удобно применять, когда нужно сформировать отчёт на основе большого объёма информации. Они суммируют значения, расположенные не по порядку, группируют данные из разных участков исходной таблицы в одном месте и сами проводят дополнительные расчёты.
Вид сводной таблицы можно настраивать под себя самостоятельно парой кликов мыши — менять расположение строк и столбцов, фильтровать итоги и переносить блоки отчёта с одного места в другое для лучшей наглядности.
Разберём на примере. Представьте небольшой автосалон, в котором работают три менеджера по продажам. В течение квартала данные об их продажах собирались в обычную таблицу: модель автомобиля, его характеристики, цена, дата продажи и ФИО продавца.
Скриншот: 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 приходится в разных сферах. Можно быстро обрабатывать большие объемы информации, сравнивать, группировать данные. Это значительно облегчает труд менеджеров, продавцов, руководителей, маркетологов, социологов и т.д.
Сводные таблицы позволяют быстро сформировать различные отчеты по одним и тем же данным. Кроме того, эти отчеты можно гибко настраивать, изменять, обновлять и детализировать.
Создание отчета с помощью мастера сводных таблиц
У нас есть тренировочная таблица с данными:
Каждая строка дает нам исчерпывающую информацию об одной сделке:
- в каком магазине были продажи;
- какого товара и на какую сумму;
- кто из продавцов постарался;
- когда (число, месяц).
Если это огромная сеть магазинов и продажи идут, то в течение одного квартала размер таблицы станет ужасающим. Проанализировать данные в сотне строк будет очень сложно. А на составление отчета уйдет не один день. В такой ситуации сводная таблица просто необходима.
Создам отчет с помощью мастера сводных таблиц. В новых версиях Excel он почему-то спрятано глубоко в настройках:
- Выберите «Файл»-«Параметры»-«Панель быстрого доступа».
- В выпадающем списке левой колонки: «Выбрать команду из» укажите «Все команды».
- В левой колонке найдите по алфавитному порядку и выделите: «Мастер сводных таблиц и диаграмм». Нажмите на кнопку между колонками: «Добавить» чтобы инструмент переместился в правую колонку и нажмите ОК.
Теперь инструмент находится в панели быстрого доступа, а значит всегда под рукой.
- Ставим курсор в любом месте таблицы с данными. Вызываем мастер сводных таблиц, нажимая на соответствующий инструмент, который теперь уже расположенный напанели быстрого доступа.
- На первом шаге выбираем источник данных для формирования сводной таблицы. Нажимаем «Далее». Чтобы собрать информацию в сводный отчет из нескольких листов, выбираем: «в списке или базе данных Microsoft Excel».
- На втором шаге определяем диапазон данных, на основании которых будет строиться отчет. Так как у нас стоит курсор в таблице, диапазон обозначится автоматически.
- На третьем шаге Excel предлагает выбрать, куда поместить сводную таблицу. Жмем «Готово» и открывается макет.
- Нужно обозначить поля для отображения в отчете. Допустим, мы хотим узнать суммы продаж по каждому продавцу. Ставим галочки – получаем:
Готовый отчет можно форматировать, изменять.
Как обновить данные в сводной таблице Excel?
Это можно сделать вручную и автоматически.
Вручную:
- Ставим курсор в любом месте сводной таблице. В результате становится видна вкладка «Работа со сводными таблицами».
- В меню «Данные» жмем на кнопку «Обновить» (или комбинацию клавиш ALT+F5).
- Если нужно обновить все отчеты в книге Excel, выбираем кнопку «Обновить все» (или комбинацию клавиш CTRL+ALT+F5).
Настройка автоматического обновления при изменении данных:
- На вкладке «Работа со сводными таблицами» (необходимо щелкнуть по отчету) выбираем меню «Параметры».
- Открываем «Дополнительные параметры сводной таблицы». Открывается мастер.
- В разделе «Данные» устанавливаем галочку напротив пункта «Обновить при открытии файла».
Теперь каждый раз при открытии файла с измененными данными будет происходить автоматическое обновление сводной таблицы.
Некоторые секреты форматирования
Когда мы сводим в отчет большой объем данных, для выводов и принятия каких-то решения может понадобиться группировка. Допустим, нам нужно увидеть итоги за месяц или квартал.
Группировка по дате в сводной таблице Excel:
- Источник информации – отчет с данными.
- Так как нам нужна группировка по дате, выделяем любую ячейку с соответствующим значением. Щелкаем правой кнопкой мыши.
- Из выпавшего меню выбираем «Группировку». Откроется инструмент вида:
- В полях «Начиная с» и «По» Excel автоматически проставил начальную и конечную даты диапазона данных. Определяемся с шагом группировки. Для нашего примера – либо месяцы, либо кварталы. Остановимся на месяцах.
Получаем отчет, в котором четко видны суммы продаж по месяцам. Поэкспериментируем и установим шаг – «Кварталы». Результат – сводная таблица вида:
Если фамилия продавцов для анализа деятельности сети магазинов не важна, можем сформировать отчет с поквартальной прибылью.
Чтобы убрать результаты группировки, необходимо щелкнуть по ячейке с данными правой кнопкой мыши и нажать разгруппировать. Либо выбрать данный параметр в меню «Структура».
Работа с итогами
У нас есть сводный отчет такого вида:
Видны итоги по месяцам (сделано «Группировкой») и по наименованиям товаров. Сделаем отчет более удобным для изучения.
Как в сводной таблице сделать итоги сверху:
- «Работа со сводными таблицами» – «Конструктор».
- На вкладке «Макет» нажимаем «Промежуточные итоги». Выбираем «Показывать все промежуточные итоги в заголовке группы».
- Получается следующий вид отчета:
Уже нет той перегруженности, которая затрудняла восприятие информации.
Как удалить промежуточные итоги? Просто на вкладке макет выбираем «Не показывать промежуточные суммы»:
Получим отчет без дополнительных сумм:
Детализация информации
Огромные сводные таблицы, которые составляются на основании «чужих» таблиц, периодически нуждаются в детализации. Мы не знаем, откуда взялась сумма в конкретной ячейке Excel. Но можно это выяснить, если разбить сводную таблицу на несколько листов.
- В марте продано двуспальных кроватей на сумму 23 780 у.е. Откуда взялась эта цифра. Выделяем ячейку с данной суммой и щелкаем правой кнопкой мыши и выбираем опцию:
- На новом листе откроется таблица с данными о продажах товара.
Мы можем переместить всю сводную таблицу на новый лист, выбрав на вкладке «Действия» кнопку «Переместить».
По умолчанию в сводную таблицу помещается абсолютно вся информация из того столбца, который мы добавляем в отчет.
В нашем примере – ВСЕ товары, ВСЕ даты, ВСЕ суммы и магазины. Возможно, пользователю не нужны некоторые элементы. Они просто загромождают отчет и мешают сосредоточиться на главном. Уберем ненужные элементы.
- Нажимаем на стрелочку у названия столбца, где будем корректировать количество информации.
- Выбираем из выпадающего меню название поля. В нашем примере – это название товара или дата. Мы остановимся на названии.
- Устанавливаем фильтр по значению. Исключим из отчета информацию по односпальным кроватям – уберем флажок напротив названия товара.
Жмем ОК – сводная таблица меняется.
Для анализа больших и сложных таблиц обычно используют
Сводные таблицы
. С помощью формул также можно осуществить группировку и анализ имеющихся данных. Создадим несложные отчеты с помощью формул.
В качестве исходной будем использовать
таблицу в формате EXCEL 2007
(
), содержащую информацию о продажах партий продуктов. В строках таблицы приведены данные о поставке партии продукта и его сбыте. Аналогичная таблица использовалась в статье
Сводные таблицы
.
В таблице имеются столбцы:
Товар
– наименование партии товара, например, «
Апельсины
»;
Группа
– группа товара, например, «
Апельсины
» входят в группу «
Фрукты
»;
Дата поставки
– Дата поставки Товара Поставщиком;
Регион продажи
– Регион, в котором была реализована партия Товара;
Продажи
– Стоимость, по которой удалось реализовать партию Товара;
Сбыт
– срок фактической реализации Товара в Регионе (в днях);
Прибыль
– отметка о том, была ли получена прибыль от реализованной партии Товара.
Через
Диспетчер имен
откорректируем
имя
таблицы на «
Исходная_таблица
» (см.
файл примера
).
С помощью формул создадим 5 несложных отчетов, которые разместим на отдельных листах.
Отчет №1 Суммарные продажи Товаров
Найдем суммарные продажи каждого Товара. Задача решается достаточно просто с помощью функции
СУММЕСЛИ()
, однако само построение отчета требует определенных навыков работы с некоторыми средствами EXCEL.
Итак, приступим. Для начала нам необходимо сформировать перечень названий Товаров. Т.к. в столбце Товар исходной таблицы названия повторяются, то нам нужно из него выбрать только
уникальные
значения. Это можно сделать несколькими способами: формулами (см. статью
Отбор уникальных значений
), через меню
или с помощью
Расширенного фильтра
. Если воспользоваться первым способом, то при добавлении новых Товаров в исходную таблицу, новые названия будут включаться в список автоматически. Но, здесь для простоты воспользуемся вторым способом. Для этого:
- Перейдите на лист с исходной таблицей;
-
Вызовите
Расширенный фильтр
(
);
-
Заполните поля как показано на рисунке ниже: переключатель установите в позицию
Скопировать результат в другое место
; в поле Исходный диапазон введите $A$4:$A$530; Поставьте флажок
Только уникальные записи
.
- Скопируйте полученный список на лист, в котором будет размещен отчет;
-
Отсортируйте перечень товаров (
).
Должен получиться следующий список.
В ячейке
B6
введем нижеследующую формулу, затем скопируем ее
Маркером заполнения
вниз до конца списка:
=СУММЕСЛИ(Исходная_Таблица[Товар];A6;Исходная_Таблица[Продажи])
Для того, чтобы понять сруктурированные ссылки на поля в
таблицах в формате EXCEL 2007
можно почитать Справку EXCEL (клавиша
F1
) в разделе
Основные сведения о листах и таблицах Excel > Использование таблиц Excel
.
Также можно легко подсчитать количество партий каждого Товара:
=СЧЁТЕСЛИ(Исходная_Таблица[Товар];A6)
Отчет №2 Продажи Товаров по Регионам
Найдем суммарные продажи каждого Товара в Регионах. Воспользуемся перечнем Товаров, созданного для Отчета №1. Аналогичным образом получим перечень названий Регионов (в поле Исходный диапазон
Расширенного фильтра
введите $D$4:$D$530). Скопируйте полученный вертикальный диапазон в
Буфер обмена
и
транспонируйте
его в горизонтальный. Полученный диапазон, содержащий названия Регионов, разместите в заголовке отчета.
В ячейке
B
8
введем нижеследующую формулу:
=СУММЕСЛИМН(Исходная_Таблица[Продажи]; Исходная_Таблица[Товар];$A8; Исходная_Таблица[Регион продажи];B$7)
Формула вернет суммарные продажи Товара, название которого размещено в ячейке
А8
, в Регионе из ячейки
В7
. Обратите внимание на использование
смешанной адресации
(ссылки $A8 и B$7), она понадобится при копировании формулы для остальных незаполненных ячеек таблицы.
Скопировать вышеуказанную формулу в ячейки справа с помощью
Маркера заполнения
не получится (это было сделано для Отчета №1), т.к. в этом случае в ячейке
С8
формула будет выглядеть так:
=СУММЕСЛИМН(Исходная_Таблица[Сбыт, дней]; Исходная_Таблица[Группа];$A8; Исходная_Таблица[Продажи];C$7)
Ссылки, согласно правил
относительной адресации
, теперь стали указывать на другие столбцы исходной таблицы (на те, что правее), что, естественно, не правильно. Обойти это можно, скопировав формулу из ячейки
B8
, в
Буфер обмена
, затем вставить ее в диапазон
С8:
G
8
, нажав
CTRL
+
V
. В ячейки ниже формулу можно скопировать
Маркером заполнения
.
Отчет №3 Фильтрация Товаров по прибыльности
Вернемся к исходной таблице. Каждая партия Товара либо принесла прибыль, либо не принесла (см. столбец Прибыль в исходной таблице). Подсчитаем продажи по Группам Товаров в зависимости от прибыльности. Для этого будем фильтровать с помощью формул записи исходной таблицы по полю Прибыль.
Создадим
Выпадающий (раскрывающийся) список
на основе
Проверки данных
со следующими значениями:
(Все); Да; Нет
. Если будет выбрано значение фильтра
(Все)
, то при расчете продаж будут учтены все записи исходной таблицы. Если будет выбрано значение фильтра «
Да»
, то будут учтены только прибыльные партии Товаров, если будет выбрано «
Нет»
, то только убыточные.
Суммарные продажи подсчитаем следующей
формулой массива
:
=СУММПРОИЗВ((Исходная_Таблица[Группа]=A8)* ЕСЛИ($B$5=”(Все)”;1;(Исходная_Таблица[Прибыль]=$B$5))* Исходная_Таблица[Продажи])
После ввода формулы не забудьте вместо простого нажатия клавиши
ENTER
нажать
CTRL
+
SHIFT
+
ENTER
.
Количество партий по каждой группе Товара, в зависимости от прибыльности, можно подсчитать аналогичной формулой.
=СУММПРОИЗВ((Исходная_Таблица[Группа]=A8)* ЕСЛИ($B$5=”(Все)”;1;(Исходная_Таблица[Прибыль]=$B$5)))
Так будет выглядеть отчет о продажах по Группам Товаров, принесших прибыль.
Выбрав в фильтре значение
Нет
(в ячейке
B
5
), сразу же получим отчет о продажах по Группам Товаров, принесших убытки.
Отчет №4 Статистика сроков сбыта Товаров
Вернемся к исходной таблице. Каждая партия Товара сбывалась определенное количество дней (см. столбец Сбыт в исходной таблице). Необходимо подготовить отчет о количестве партий, которые удалось сбыть за за период от 1 до 10 дней, 11-20 дней; 21-30 и т.д.
Вышеуказанные диапазоны сформируем нехитрыми формулами в столбце
B
.
Количество партий, сбытые за определенный период времени, будем подсчитывать с помощью формулы
ЧАСТОТА()
, которую нужно ввести как
формулу массива
:
=ЧАСТОТА(Исходная_Таблица[Сбыт, дней];A7:A12)
Для ввода формулы выделите диапазон
С6:С12
, затем в
Строке формул
введите вышеуказанную формулу и нажмите
CTRL
+
SHIFT
+
ENTER
.
Этот же результат можно получить с помощью обычной функции
СУММПРОИЗВ()
:
=СУММПРОИЗВ((Исходная_Таблица[Сбыт, дней]>A6)* (Исходная_Таблица[Сбыт, дней]<=A7))
Отчет №5 Статистика поставок Товаров
Теперь подготовим отчет о поставках Товаров за месяц. Сначала создадим перечень месяцев по годам. В исходной таблице самая ранняя дата поставки 11.07.2009. Вычислить ее можно с помощью формулы:
=МИН(Исходная_Таблица[Дата поставки])
Создадим перечень дат –
первых дней месяцев
, начиная с самой ранней даты поставки. Для этого воспользуемся формулой:
=КОНМЕСЯЦА($C$5;-1)+1
В результате получим перечень дат – первых дней месяцев:
Применив соответствующий формат ячеек, изменим отображение дат:
Формула для подсчета количества поставленных партий Товаров за месяц:
=СУММПРОИЗВ((Исходная_Таблица[Дата поставки]>=B9)* (Исходная_Таблица[Дата поставки]
Теперь добавим строки для подсчета суммарного количества партий по каждому году. Для этого немного изменим таблицу, выделив в отдельный столбец год, в который осуществлялась поставка, с помощью функции
ГОД()
.
Теперь для вывода
промежуточных итогов
по годам создадим структуру через пункт меню
:
- Выделите любую ячейку модифицированной таблицы;
-
Вызовите окно
Промежуточные итоги
через пункт меню
;
- Заполните поля как показано на рисунке:
После нажатия ОК, таблица будет изменена следующим образом:
Будут созданы промежуточные итоги по годам. Нажатием маленьких кнопочек в левом верхнем углу листа можно управлять отображением данных в таблице.
Резюме
:
Отчеты, аналогичные созданным, можно сделать, естественно, с помощью
Сводных таблиц
или с применением
Фильтра
к исходной таблице или с помощью других функций
БДСУММ()
,
БИЗВЛЕЧЬ()
,
БСЧЁТ()
и др. Выбор подхода зависит конкретной ситуации.
Привет! Мы «Нескучные финансы», помогаем бизнесу навести порядок в учете и управлять бизнесом на основе цифр. Для этого мы или берем финучет на аутсорс, или предоставляем удобные бесплатные шаблоны. В этой статье как раз второе.
Мы подготовили барсетку. Это 16 шаблонов финансовых таблиц, в которых вы можете вести управленческий учет в своем бизнесе. Для каждой таблицы мы записали видеоинструкцию, ссылка на которую лежит на последней вкладке таблицы.
Внутри: отчет о движении денег, баланс, отчет о прибылях и убытках, учет основных средств, управление запасами, учет логистики, учет финансовой деятельности, учет сделок, финмодель, платежный календарь, зарплатная ведомость, отчет по маркетингу, калькулятор рентабельности, калькулятор финансового рычага, калькулятор скидок, отчет отдела продаж.
Чтобы воспользоваться таблицей, нужно сделать ее копию на свой Гугл-диск. Можно скачать в Экселе, но не рекомендуем: какие-то формулы могут не работать.
Можете просто перейти по ссылке и пользоваться таблицами на свое здоровье. А можете читать дальше: мы вкратце расскажем, зачем нужна каждая таблица из барсетки.
ДДС (отчет о движении денежных средств)
Это документ, с которого начинается внедрение финучета в компании. Из этого отчета видно, откуда пришли деньги и куда ушли. Так или иначе его ведут многие собственники ― в блокноте, Эксельке или голове, а вот анализируют не все. Однако нужно сравнивать суммы за разные периоды и по одинаковым статьям. Например, в этом месяце мы тратим на содержание офиса больше, чем в том.
Баланс
Самый важный отчет для собственника. Он показывает, сколько денег владельца компании работает в бизнесе. Собственный капитал увеличивается за счет получаемых прибылей, и уменьшается за счет полученных убытков, выплаты дивидендов.
Баланс состоит из активов и пассивов. В активах показывается, что есть у компании, а в пассивах ― кому это принадлежит.
Отчет о прибылях и убытках
Он помогает увидеть, сколько чистой прибыли заработала компания. Кроме того, отчет показывает детализацию по нескольким видам прибыли: маржинальной, валовой, операционной. Можно проанализировать и понять, как улучшить результат.
Учет основных средств
Нужен для того, чтобы систематизировать все данные, которые нужны для расчета амортизации и последующей продажи. В этом документе указывается стоимость и дата покупки, срок полезного использования и другие детали. Здесь может быть указано торговое, складское и производственное оборудование, оргтехника, недвижимость, мебель, транспорт ― все, что предназначено для основной деятельности организации и прослужит дольше года.
Управление запасами
Помогает навести порядок на складе и знать, сколько товара есть в наличии. Еще управлять запасами ― это держать ходовой товар в достаточном количестве. Чтобы определить спрос на товар, нужно анализировать фактические данные.ABC-анализ дает понять, какие товары занимали наибольшую долю в выручке компании, а XYZ-анализ помогает понять, как часто покупают конкретный товар. Можно анализировать не только конкретные продукты, но и категории товаров.
Чтобы не было товарного разрыва ― ситуации, когда клиенты приходят и хотят купить товар, а его нет ― нужно высчитать точку заказа. Так мы узнаем, сколько и когда нужно купить товара.
Учет логистики
С помощью этого инструмента можно анализировать работу с разными транспортными компаниями, учитывать расходы на доставку, если посылку не выкупили.
Учет финансовой деятельности
Позволяет собирать информацию по расчету с кредиторами и собственниками. В этом документе учитываются вклады и займы от владельца компании и информация о дивидендах. Также здесь систематизируются данные о сторонних кредитах, займах и лизинге оборудования.
Учет сделок
Позволяет не растерять информацию по проектам. В одном документе будут собраны сведения по контрагентам и проджект-менеджерам, сумма выплат и расходов, данные о расчетах и сроках. Еще в нем подсчитаны прибыль и рентабельность проекта.
Финансовая модель
В финмодели можно запланировать чистую прибыль и выручку, проанализировать ключевые показатели, которые влияют на финансовый результат бизнеса. Ее можно составить как для планируемых направлений бизнеса и компаний, так и для уже существующих. С помощью финмодели можно проверить, как то или иное управленческое решение повлияет на финансовый результат компании.
Платежный календарь
Один из инструментов планирования. Сначала планируют на месяц-два-три бюджет, а на его основе делают платежный календарь. В нем по датам видно, сколько и когда ожидается поступлений и затрат. Это помогает предвосхищать кассовые разрывы: в платежном календаре видны «дырки», которые можно залатать предоплатой от другого клиента, переносом сроков оплаты, заемными средствами и другими способами.
Зарплатная ведомость
Один из самых актуальных инструментов. Помогает вести учет выплат сотрудникам. Будет полезна компаниям, в которых сложная система мотивации, которая зависит от выполнения показателей эффективности. Помогает собрать информацию об отпускных, премиях, бонусах и отчислениях в пенсионный фонд, налоговую и соцстрах.
Маркетинговый отчет
Сводный документ, в котором учитываются рекламные активности на разных онлайн площадках и привлеченный с них трафик. С помощью такого инструмента можно собрать данные для анализа эффективности рекламы из разных источников: в отчете наглядно видно, что эффективней для продвижения вашего продукта ― контекстная реклама в Яндекс.Директе или таркетинговая во Вконтакте. Также здесь можно вести учет рекламных расходов и выручки, которая получена от клиентов из онлайна.
Калькулятор рентабельности проектов
Посчитает, выгодно заниматься проектом или нет. С его помощью можно определить маржинальность проекта и сравнить ее с плановой. Калькулятор рентабельности проектов даст оценить прибыль проекта с учетом разных систем налогообложения.
Калькулятор финансового рычага
Поможет рассчитать, нужен кредит или нет. Калькулятор финансового рычага сделает расчет эффективности кредита ― сможет компания взлететь на новые деньги или заемные средства потянут ее ко дну. Подробнее об эффекте финансового рычага мы рассказали в другой статье →
Калькулятор влияния скидки на прибыль
Рассчитает, как повлияет скидка на прибыль компании. Теперь вы сможете делать скидку не на глаз, а учитывая расчет по цене реализации, марже или наценке.
Отчет отдела продаж
Таблица, в которой удобно следить за работой всего отдела продаж и отдельных менеджеров — сразу увидите, если случится какое-то отклонение по выручке, сможете оперативно принять решение, как исправить ситуацию. А самим менеджерам отчет помогает понимать, сколько они заработают в этом месяце. Единственный отчет, к которому не записали видеоинструкцию.
Пользуйтесь на здоровье! Если есть вопросы по шаблонам, задавайте их в комментариях.
Сводные таблицы Excel – тема очень интересная и обширная.
В этой заметке я расскажу все, что вам нужно знать, для того чтобы начать применять сводные таблицы (в английском варианте – pivot table) в своей работе.
Сводные таблицы предоставляют очень широкие возможности для формирования нужных вам отчетов на основе каких-либо данных. При этом отчеты на базе сводных таблиц создаются буквально в несколько щелчков мыши и не требуют от пользователя создания сложнейших формул для группировки или суммирования необходимых данных.
Кроме этого, данные сводных таблиц очень просто визуализировать с помощью диаграмм или графиков, что с успехом применяется при создании так называемых дашбордов, которые сейчас очень широко применяются при анализе данных.
Постановка задачи
Итак, сводные таблицы могут быть применены к абсолютно любым данным, но чаще всего Excel применяют для анализа различных финансовых показателей компаний или проектов, поэтому давайте рассмотрим следующий пример (скачать файл).
Допустим вы трудитесь в компании, которая является поставщиком овощей и фруктов в сетевые супермаркеты, которые находятся в нескольких крупных городах страны.
По каждой поставке в базе есть информация, которую можно выгрузить в приблизительно вот такую таблицу:
Здесь в каждой строке мы видим дату заказа, название сети супермаркетов, город, в который осуществлялась поставка, категорию товара, его наименование, цену на дату поставки, количество заказов и итоговую сумму сделки.
Информации может быть намного больше. Для упрощения задачи я взял лишь минимальный набор данных. Тем не менее данных очень много и таблица состоит из нескольких тысяч строк.
Любую компанию в первую очередь интересует прибыль и поэтому может потребоваться найти ответы на ряд вопросов, например:
- Определить, в каком из городов за прошедшее время выручка была максимальной.
- Какая торговая сеть позволила получить компании наибольшую выручку.
- Определить категорию товаров и конкретный товар, принесшие наибольшую выручку.
В таблице представлены данные за два года, поэтому приблизительно такие же вопросы могут возникнуть у руководства компании по отношению к какому-то конкретному временному интервалу, например, какой товар был наиболее востребованным прошлым летом, или в прошлом году, какова была динамика продаж товаров разных категорий в течении года (по месяцам, кварталам) или какой из заказчиков в прошлом месяце был для компании наиболее значимым. Также часто возникает необходимость определить лидеров продаж, например составить ТОП5 товаров, на которые был наибольший спрос в определенное время.
Решение задачи формулами
На первый взгляд все эти задачи легко решаются несложными формулами и стандартными функциями.
Так, например, для определения города, принесшего максимальную выручку, нужно лишь сложить сумму всех поставок по каждому из городов. Сделать это можно с помощью функции СУММЕСЛИ.
То есть нам нужно будет создать отдельную таблицу, в которую с помощью функции СУММЕСЛИ свести данные по каждому из городов.
Для начала нужно создать список уникальных значений. Для этого скопируем значение столбца с названиями городов (столбец С) и вставим их на новый лист. Далее с помощью удаления дубликатов оставим лишь уникальные значения.
Ну а теперь применим функцию СУММЕСЛИ.
Сначала укажем диапазон значений, в котором будем искать условие (это столбец с городами в исходной таблице), а затем зададим само условие. Нам нужно, чтобы в этой строке суммировались итоги по конкретному городу, поэтому указываем ячейку с его названием в новой таблице. Ну а теперь задаем диапазон, значения которого нужно суммировать в случае выполнения условия – это столбец с итогами.
Получаем выручку, полученную в конкретном городе. Растягиваем формулу на весь диапазон новой таблицы и получаем результат.
В итоге по значениям суммарной выручки мы легко определим победителя и ответим на первый вопрос.
Для решения остальных задач нужно будет также создавать отдельные таблицы и с помощью формул, которые могут быть довольно сложными, рассчитывать значения.
Подобный подход к формированию нужных отчетов весьма трудоемкий и требует не только времени на создание отчета, но и внимательности со стороны пользователя, ведь допустить ошибку в формуле при обработке огромного массива данных довольно просто. Ну а об аппетитах начальства рассказывать и вовсе нет смысла. Как только на стол ляжет ответ на первый вопрос сразу же появятся дополнительные и придется вновь корпеть над формулами и стягивать нужные данные в компактную табличку…
Это не наш метод, тем более что сводные таблицы позволяют сделать ровно тоже самое, но в разы быстрее.
Ответим на первый вопрос с помощью сводной таблицы.
Создание сводной таблицы в Excel
Сводная таблица всегда строится на основе некоторого массива данных, который должен иметь строку заголовков.
В моем примере мы имеем простой диапазон значений, но первая строка диапазона содержит заголовки столбцов, а значит и такой диапазон подойдёт для создания сводной таблицы.
Установим табличный курсор в любую ячейку диапазона и на вкладке Вставка выберем Сводная таблица.
Excel автоматически выберет весь неразрывный диапазон значений и появится окно создания сводной таблицы, где будет указана абсолютная ссылка на этот диапазон.
Абсолютным и относительным ссылкам уже было посвящено отдельное подробное видео, поэтому не буду на этом останавливаться. Упомяну лишь, что в случае использования такого фиксированного диапазона в качестве источника данных сводной таблицы, мы в итоге не сможем добавлять в нее новую информацию. Точнее сказать, если в исходной таблице появятся новые строки, то для того, чтобы информация из них появилась в сводной таблице нам придется вручную корректировать в ее настройках этот диапазон.
Фактически данную проблему полностью решают умные таблицы. Поэтому перед тем, как создать новую сводную таблицу стоит преобразовать исходные данные в умную таблицу.
Делается это очень просто – также устанавливаем табличный курсор в любую ячейку диапазона и либо на вкладке Вставка выбираем Таблица, либо просто нажимаем сочетание клавиш Ctrl+T.
Так как строка с заголовками уже присутствует в диапазоне, то соответствующую галочку не убираем.
Ну и также, как и раньше создадим сводную таблицу, но уже на основе умной таблицы.
Теперь в источнике данных уже указан не фиксированный диапазон на листе, а отдельный объект Таблица1. Это нам гарантирует, что новые данные будут автоматически добавляться в сводную таблицу при ее обновлении.
Вставим сводную таблицу на новый лист.
На новом листе появится подсказка, подсказывающая нам, что это лист сводной таблицы, а также в правой части окна появится панель инструментов, позволяющая сконструировать нужный нам отчет.
Эту панель инструментов условно можно разделить на две части. В верхней находится перечень, так называемых полей. Можно легко убедиться, что названия полей соответствуют названиям заголовков исходной таблицы. То есть по названию поля мы можем легко понять, какой именно столбец с данными за ним стоит.
В нижней части расположены четыре области, которые относятся к четырём конструктивным элементам сводной таблицы. В зависимости от того, в какую область мы перетащим то или иное поле, его данные будут выводиться в той или иной части сводной таблицы.
Например, нам нужно ответить на вопрос – поставки товаров в какой город позволили получить максимальную выручку?
То есть в первую очередь нам нужно получить список городов. Для этого захватываю мышью поле Город и перетягиваем его в область Строки. Мы сразу получаем список названий всех городов из столбца Город исходной таблицы.
То есть область Строки позволят разместить данные в строках.
Если мы перетянем данное поле в область Столбцы, то получим тот же список, но уже в одной строке, то есть каждое название стало заголовком отдельного столбца.
Верну поле в Строки и завершу создание первой сводной таблицы. Нам ведь нужно узнать суммарную выручку по городам, поэтому просто перетягиваем поле Итого в Значения. Получаем точно такую же табличку, как и раньше, но буквально в несколько щелчков мыши.
Пока не будем обращать внимание на внешний вид сводной таблицы, а сосредоточимся на ее функциональности.
Обратите внимание на то, что в области Строки фигурирует имя поля, а в области Значения находится фраза «Сумма по полю Итого». Эта же фраза подставлена в заголовок соответствующего столбца сводной таблицы. Она указывает на то, что при формировании значений столбца сводной таблицы производилось суммирование значений столбца Итого умной таблицы.
Если щелкнуть мышью по маленькому черному треугольнику и в меню выбрать Параметры полей значений, то появится окно, в котором доступны все возможные операции. Чаще всего приходится применять суммирование или подсчет количества значений.
Так как в столбце Итого в исходной таблицы у нас находятся числовые значения, то Эксель автоматически выбрал суммирование при перетягивании поля в область. Если же в столбце находится текст, то по умолчанию будет выбрано количество.
Так в столбце Заказчик умной таблицы находятся наименования торговых сетей, то если я перетяну их в область Значения мы увидим количество по этому полю. Фактически это значение показывает, сколько было поставок в том или ином городе, то есть сколько сделок было совершено.
Ну а теперь давайте создадим еще одну сводную таблицу, которая ответит на второй вопрос – какая торговая сеть позволила получить компании наибольшую выручку?
Переключимся на лист с исходными данными и точно также создадим еще одну сводную таблицу на новом листе.
В первую очередь нам нужен список торговых сетей, поэтому перетянем поле Заказчик в область Строки. Ну а поле Итого в Значения. Все готово!
Ну и последняя задача – определить категорию товаров, принесшую наибольшую выручку.
Действием по аналогии.
Можно сделать отчет более информативным, если в область Строки перенести еще и Товар. Тогда мы сможем получить информацию не только по отдельным категориям товаров, но и по товарам внутри категории.
При этом важно соблюдать “вложенность” полей. То есть у нас товары принадлежат категориям, а не наоборот. Этот порядок задается последовательностью полей в области. Если сейчас изменить их очередность, то получим следующее – появится список всех товаров, а вложенной информацией станет их принадлежность к какой-либо товарной категории.
В данном случае это крайне не информативно, поэтому верну все как было.
Но не стоит забывать и про область Столбцы. Для примера перетянем в нее поле Заказчик. Мы получим подробный отчет по объемам заказов каждой категории товаров отдельными торговыми сетями. При этом каждую категорию можно раскрыть, чтобы увидеть детализацию по каждому товару и сети.
На первый взгляд формирование сводной таблицы с помощью полей может показаться довольно сложным и непредсказуемым, но небольшая практика быстро расставит все на свои места и позволит вам сходу ориентироваться в нужных областях при создании отчетов.
Итак, у нас есть отчеты, отвечающие на поставленные задачи. Осталось лишь немного отформатировать данные в таблицах, сделав их более приятными для восприятия.
Форматирования сводной таблицы
В первую очередь поговорим о заголовках. Именно их хочется сразу изменить, но тут есть один нюанс, который стоит учитывать.
Заголовок изменяется самым обычным образом – щелкаем по ячейке с ним и затем меняем текст.
Также можно выделить нужную ячейку с заголовком и нажать клавишу F2 для перехода в режим редактирования ее содержимого.
При этом важно учитывать, что в сводной таблице название заголовка не может быть таким же, как и название поля. То есть если я захочу переименовать «Сумма по полю Итого» в «Итого», то ничего не выйдет и появится ошибка.
Правда этот нюанс можно обойти. Если добавить в конце слова пробел, то для Эксель это будет уже другое значение, а пользователь разницы не увидит.
Я же просто переименую поля в «Сумма заказов» и «Количество заказов». Первый заголовок также можно изменить на «Город».
Осталось отформатировать сами значения. В первую очередь изменим числовой формат, сделав его денежным. При этом сразу же приходит на ум воспользоваться соответствующим инструментом со вкладки Главная.
Однако, если выделенным будет только одна ячейка столбца, то и форматирования затронет только ее. В данном случае правильнее будет изменить числовой формат для всего столбца и для этого достаточно из контекстного меню, вызванного щелчком правой кнопки мышки на любой из ячеек столбца, выбрать пункт Числовой формат.
Затем в появившемся окне указываем нужный формат и задаем его параметры.
Форматирование будет применено сразу ко всем столбцу.
Ну а также на контекстной вкладке Конструктор, которая появляется только при выделении сводной таблицы, можно задать стиль оформления таблицы целиком. Для этого нужно либо выбрать одну из готовых цветовых схем, либо можно создать свой вариант стилевого оформления, задав форматирования для каждого элемента сводной таблицы индивидуально.
Общие и промежуточные итоги
И уж если речь зашла о контекстной вкладке Конструктор, то стоит сразу сказать и о настройках сводной таблицы, связанных с ее макетом.
Макет определяет, в какой части сводной таблицы будет выводиться тот или иной ее элемент, то есть определяет ее структуру. Кроме данных, которые автоматически подтягиваются в сводную таблицу из исходной, сама сводная таблица формирует общие и промежуточные итоги по каждому столбцу и строке.
Расположением и видимостью общих и промежуточных итогов мы также можем управлять. Для этого есть соответствующие инструменты на контекстной вкладке Конструктор.
Промежуточные итоги в моем примере формируются суммами по каждой категории товаров и по умолчанию выводятся в строке с наименованием категории, то есть в заголовке группы.
То есть если просуммировать значения по каждому товару, то мы получим значение, указанное в промежуточных итогах.
Далеко не всегда это значение нужно выводить. Так при раскрытом списке оно скорее создает путаницу, если не знать, что именно оно означает. В таком случае можно отключить промежуточные итоги, выбрав соответствующую опцию.
Тогда промежуточные итоги будут выводиться только в случае свернутой категории, когда данные по отдельным товарам не отображаются.
Также можно выводить промежуточные итоги отдельной строкой в нижней части каждой категории товаров (второй пункт меню). Опять же, промежуточные итоги будут отображаться в свернутом виде в основной строке, а при развернутой категории смещаться отдельной строкой ниже.
Общие итоги также формируются автоматически по каждой строке и столбцу и далеко не всегда они необходимы. В соответствующем меню мы можем полностью отключить вывод общих итогов в сводной таблице, либо оставить итоги только по столбцу или только строке.
Макет сводной таблицы
Ну и выбор макета также влияет на внешний вид сводной таблицы. Есть три варианта.
Первый – сжатая форма. Этот вариант по умолчанию и мы его видим сразу после создания сводной таблицы.
При выборе второго варианта – форма структуры, в сводной таблице под каждое поле будет выделен отдельный столбец. То есть в первом столбце теперь выводится только категория товара, а сами товары отображаются во втором столбце.
Табличная форма аналогична форме структуры, но промежуточные итоги из строки с названием категории перемещаются вниз.
В этом же меню есть еще одна настройка, позволяющая повторять или не повторять подписи элементов.
Сейчас категория отображается только в одной строке и это вариант с не повторяющимися подписями. Если выбрать второй вариант, то название категории будет дублироваться в каждой строке.
Ну а теперь со знанием дела приведем отчет к нужному виду – вернем сводной таблице сжатую форму, а затем перенесем промежуточные итоги вниз каждой категории.
С помощью соответствующего инструмента вставим пустые строки после каждой категории, чтобы визуально их отделить друг от друга.
Ну а чтобы быстро свернуть или развернуть все категории можно воспользоваться контекстным меню, вызванным щелчком правой кнопки мыши на соответствующей ячейке. Здесь есть раздел, в котором выбираем нужный вариант.
Ну а если кнопки свертывания не нужны, то можно их скрыть. Для этого на контекстной вкладке Анализ отключим их отображение.
Подкорректируем заголовки, выберем подходящий стиль и наш отчет готов.
Сортировка и фильтрация
Скорее всего вы уже обратили внимание на то, что в сводной таблице есть две ячейки с кнопками.
По щелчку мыши на них появляется меню с возможностью фильтрации и сортировки данных. Эти инструменты относятся к заголовкам строк и, соответственно, столбцов.
Если нужно сформировать отчет только по какой-то одной товарной категории (например, “Зелень”), то с помощью фильтра отключаем все ненужные и получаем результат:
То же самое касается и заказчиков. То есть мы можем сократить отчет только до нужной категории товаров, заказанных определенной торговой сетью.
Если кроме категории нужно отфильтровать данные еще и по конкретным товарам, то в меню в выпадающем списке указываем соответствующее поле, а затем делаем фильтрацию по нему.
При применении сортировки или фильтрации значок на кнопке изменяется. По нему можно однозначно определить, что данные в столбце или строке отфильтрованы или отсортированы.
Чтобы удалить фильтры достаточно выбрать соответствующий пункт в меню, однако в случае с вложенными полями удаление фильтра касается только выбранного в выпадающем списке. То есть если фильтрация была произведена по нескольким полям, то для ее удаления нужно будет сначала переключиться на соответствующее поле.
Кроме стандартных возможностей фильтрации мы можем настроить фильтр по произвольному полю. Для этого есть отдельная область, которая так и называется Фильтры.
Сейчас мы построили отчет, дающий полное представление об объемах заказов со стороны торговых сетей, но вот как дела обстоят по отдельным городам?
Перетаскиваем соответствующее поле в область Фильтр и над сводной таблицей появляется соответствующий выпадающий список.
Мы можем выбрать отдельный город, чтобы получить информацию только по нему.
Что же касается сортировки, то в выпадающем меню есть стандартные инструменты, позволяющие отсортировать заголовки строк или столбцов в алфавитном порядке.
Однако намного удобнее пользоваться контекстным меню. Например одной из первых задач у нас было определить, в каком из городов за прошедшее время выручка была максимальной. Мы получили результат в виде данных по всем городам, но чтобы быстро определить нужное значение необходимо отсортировать значения по возрастанию или убыванию. Вызываем контекстное меню на любой ячейке столбца и выбираем нужный вариант.
Аналогично можно отсортировать данные по любому полю или итогам. Просто вызываем контекстное меню на соответствующей ячейке и выбираем нужное направление сортировки.
Ну и затронув тему фильтрации нельзя обойти стороной так называемые срезы.
Срезы в сводных таблицах
Срез – это тот же фильтр, но интерактивный.
При вставке среза мы также выбираем поле, по которому фильтр будет работать. Например, вставим два среза – по городам и товарам.
Если в ранее вставленном нами фильтре нужно выбирать нужные объекты из списка, то в срезе достаточно щелкнуть мышью по нужному пункту. При этом обратите внимание на то, что срез по городам и ранее вставленный вручную фильтр работают синхронно, то есть полностью дублируют друг друга.
Таким образом выбирая нужные значения в срезах в пару щелчков мыши мы можем изменять отчет, выводя в нем только нужную информацию.
Для выделения нескольких пунктов подряд достаточно выбирать их удерживая нажатой левую кнопку мыши. Если же нужно выбрать несколько несмежных значений, то в окне каждого среза есть соответствующая кнопка. Также как и для очистки фильтров.
Даты в сводных таблицах
Ну и последняя важная тема – это даты. Пока мы вообще не трогали поле Дата, но сводные таблицы позволяют очень гибко выводить информацию, связанную с датами и сейчас я это продемонстрирую.
Создадим еще одну сводную таблицу, в которой выведем выручку за все время.
В исходной таблице указывалась конкретная дата каждой сделки, а сводная таблица автоматически сгруппировала даты при этом не только по годам, но и по кварталам и месяцам. При этом в области Строки поле Дата было автоматически преобразовано в три – Годы, Кварталы и Дата.
Если такая группировка не нужна, то можно ее отменить через контекстное меню.
Также с помощью контекстного меню можно вернуть группировку (пункт Группировать), указав необходимые группы. Здесь можно выбрать сразу несколько, например, месяцы и года.
Сортировка и фильтрация по датам работает также, как и с другими данными. Например, можно отключить какой-то временной период.
Для дат существует свой формат срезов – временная шкала.
Она также в интерактивном режиме позволяет выбирать только интересующие вас временные интервалы.
Таким образом на базе сводной таблицы можно создать интерактивный отчет, в котором с помощью срезов и временной шкалы можно очень тонко фильтровать данные. Ну а преобразовав данные сводной таблицы в диаграммы или графики можно получить отличный дашборд, с помощью которого легко можно анализировать или демонстрировать информацию.
Ну а сводные таблицы – это очень обширная и увлекательная тема, которой я посвятил отдельный очень подробный видеокурс, который так и называется “Сводные таблицы“.
Нажмите на эту ссылку, чтобы перейти на страницу курса >>
________________________________________
Ссылки на мои ресурсы по Excel
★ YouTube-канал Excel Master
★ Серия видеокурсов “Microsoft Excel Шаг за Шагом”
★ Авторские книги и курсы
