В журнале продаж за два года накопилось 2400 строк: дата, филиал, товар, выручка, себестоимость. Руководство хочет итоги по кварталам, долю каждого филиала, рост месяц к месяцу и маржу прибыли — да ещё в виде отчёта с фильтрами-кнопками. Основы сводных таблиц ты уже знаешь; теперь перейдём к профессиональным инструментам.
Подготовь источник
- Одна строка заголовков, у каждого столбца уникальное имя; без пустых строк и столбцов, без объединённых ячеек.
- В каждом столбце один тип данных: в столбце дат — только настоящие даты, в столбце сумм — только числа.
- Преврати источник в таблицу Excel (
Ctrl+T) и дай ей имя (Table Design › Table Name:Sales) — новые строки будут автоматически попадать в сводную при обновлении. - Для читаемого вида:
Design › Layout › Report Layout › Show in Tabular FormиRepeat All Item Labels.
Чтобы читать дальше, зарегистрируйся бесплатно
Остальные разделы урока, тест и заметки открываются с аккаунтом Educora. Регистрация занимает минуту, а прогресс сохраняется на всех твоих устройствах.
Группировка
Даты по месяцам, кварталам и годам
Перетащи
DateвRows— Microsoft 365 часто группирует даты сам. Вручную: правый щелчок по дате ›Group…› выбериMonths,Quarters,Years›OK.Числа по интервалам
Помести
AmountвRows, правый щелчок ›Group…›Starting at0,Ending at3000,By500. Получится 0–499, 500–999… Так видно распределение заказов по размеру.Текстовые элементы в свою группу
Выдели с
CtrlBaku и Sumgait, правый щелчок ›Group— появится «Group1»; переименуй её в «Absheron». Отменить —Ungroup.
Show Values As и вычисляемые поля
Вариант Show Values As | Что показывает | Пример (1-й квартал) |
|---|---|---|
% of Grand Total | доля каждого элемента в общем итоге | Баку 52,3%, Гянджа 31,9%, Сумгайыт 15,8% |
Difference From (Base item: (previous)) | изменение к предыдущему месяцу | Feb +690, Mar +360 |
% Difference From | рост месяц к месяцу в % | Feb +34,5%, Mar +13,4% |
Running Total In | нарастающий итог | 2000 → 4690 → 7740 |
Rank Largest to Smallest | место (1 = наибольший) | Баку 1, Гянджа 2, Сумгайыт 3 |
Show Values As. Перетащи одно и то же поле в Values дважды: одна копия покажет сумму, другая — долю.Создай вычисляемое поле
PivotTable Analyze › Calculations › Fields, Items, & Sets › Calculated Field….Name:Profit,Formula:=Revenue-Cost(поля добавляй из списка кнопкойInsert Field) ›Add.Добавь маржу
В том же окне второе поле:
Margin==Profit/Revenue. Задай ему процентный формат (Value Field Settings › Number Format › Percentage).
Интерактив
Ловушка вычисляемого поля
Задача
В источнике три строки по одному товару: цена 10, количество 5; цена 12, количество 3; цена 11, количество 4. Ты хочешь получить выручку в сводной через вычисляемое поле =Price*Quantity. Что получится?
Показать решениеСкрыть решение
Решение
Вычисляемое поле сначала берёт суммы, а потом перемножает: (10 + 12 + 11) · (5 + 3 + 4) = 33 · 12 = 396 ₼ — ошибка более чем в три раза!
Правило: вычисляемое поле работает с итогами. Для отношений (
Profit/Revenue) это верно, для построчного умножения — нет.Решение: создай столбец
Revenue в источнике (=[@Price]*[@Quantity]) и суммируй его в сводной как обычное поле.Срезы, временные шкалы, сводные диаграммы и обновление
Добавь срез
PivotTable Analyze › Filter › Insert Slicer› отметьBranchиProduct. Щелчок по кнопке фильтрует,Ctrl+щелчок выбирает несколько элементов, значок воронки справа вверху сбрасывает фильтр.Добавь временную шкалу
PivotTable Analyze › Filter › Insert Timeline›Date. Протяни полосу, чтобы выбрать период; справа вверху переключай уровниYEARS,QUARTERS,MONTHS,DAYS.Один срез — несколько отчётов
Выдели срез ›
Slicer › Slicer › Report Connections› отметь все сводные, построенные на том же источнике. Теперь один щелчок фильтрует весь дашборд.Построй сводную диаграмму
PivotTable Analyze › Tools › PivotChart(илиAlt+F1внутри сводной). Диаграмма подчиняется тем же фильтрам; кнопки полей скрываетPivotChart Analyze › Show/Hide › Field Buttons.
Refresh All)Ctrl+Alt+F5Запомни
- Источник — таблица Excel с одной строкой заголовков и без пропусков; новые строки попадают в сводную при обновлении.
- Даты группируют по месяцам/кварталам/годам, числа — по интервалам, текст — в свои группы.
Show Values Asпоказывает доли, разницу, рост в %, нарастающий итог и ранг.- Вычисляемое поле работает с итогами: годится для отношений, но не для построчного умножения.
- Срезы и временные шкалы фильтруют кнопками;
Report Connectionsсвязывает их с несколькими сводными.