Перейти к содержанию
Educora
RU
AzərbaycancaEnglishРусскийTürkçe
Моё обучениеEducora StudioНастройки
Продвинутый22 мин19 / 27

Сводные таблицы в деталях

Группировка дат и чисел, доли и рост через «Show Values As», вычисляемые поля и их ловушка, срезы и временные шкалы, сводные диаграммы и правильное обновление.

Проверь себя
В этом уроке ты узнаешь
  • Правильно готовить исходные данные для сводной таблицы
  • Группировать даты, числа и текстовые элементы
  • Считать доли, рост и маржу с «Show Values As» и вычисляемыми полями
  • Строить интерактивный отчёт со срезами, временной шкалой и сводной диаграммой

В журнале продаж за два года накопилось 2400 строк: дата, филиал, товар, выручка, себестоимость. Руководство хочет итоги по кварталам, долю каждого филиала, рост месяц к месяцу и маржу прибыли — да ещё в виде отчёта с фильтрами-кнопками. Основы сводных таблиц ты уже знаешь; теперь перейдём к профессиональным инструментам.

Подготовь источник

  • Одна строка заголовков, у каждого столбца уникальное имя; без пустых строк и столбцов, без объединённых ячеек.
  • В каждом столбце один тип данных: в столбце дат — только настоящие даты, в столбце сумм — только числа.
  • Преврати источник в таблицу Excel (Ctrl+T) и дай ей имя (Table Design › Table Name: Sales) — новые строки будут автоматически попадать в сводную при обновлении.
  • Для читаемого вида: Design › Layout › Report Layout › Show in Tabular Form и Repeat All Item Labels.

Группировка

  1. 1
    Даты по месяцам, кварталам и годам

    Перетащи Date в Rows — Microsoft 365 часто группирует даты сам. Вручную: правый щелчок по дате › Group… › выбери Months, Quarters, Years › OK.

  2. 2
    Числа по интервалам

    Помести Amount в Rows, правый щелчок › Group… › Starting at 0, Ending at 3000, By 500. Получится 0–499, 500–999… Так видно распределение заказов по размеру.

  3. 3
    Текстовые элементы в свою группу

    Выдели с Ctrl Baku и 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
Выручка по месяцам: Jan 2000, Feb 2690, Mar 3050 ₼. Путь: правый щелчок по значению › Show Values As. Перетащи одно и то же поле в Values дважды: одна копия покажет сумму, другая — долю.
  1. 1
    Создай вычисляемое поле

    PivotTable Analyze › Calculations › Fields, Items, & Sets › Calculated Field…. Name: Profit, Formula: =Revenue-Cost (поля добавляй из списка кнопкой Insert Field) › Add.

  2. 2
    Добавь маржу

    В том же окне второе поле: Margin = =Profit/Revenue. Задай ему процентный формат (Value Field Settings › Number Format › Percentage).

Интерактив
Загрузка симуляции…
Выручка и себестоимость по филиалам (в манатах): прибыль и маржа по логике сводной таблицы.
Ловушка вычисляемого поля

В источнике три строки по одному товару: цена 10, количество 5; цена 12, количество 3; цена 11, количество 4. Ты хочешь получить выручку в сводной через вычисляемое поле =Price*Quantity. Что получится?

Показать решение
Правильная выручка считается построчно: 10 · 5 + 12 · 3 + 11 · 4 = 50 + 36 + 44 = 130 ₼.
Вычисляемое поле сначала берёт суммы, а потом перемножает: (10 + 12 + 11) · (5 + 3 + 4) = 33 · 12 = 396 ₼ — ошибка более чем в три раза!
Правило: вычисляемое поле работает с итогами. Для отношений (Profit/Revenue) это верно, для построчного умножения — нет.
Решение: создай столбец Revenue в источнике (=[@Price]*[@Quantity]) и суммируй его в сводной как обычное поле.

Срезы, временные шкалы, сводные диаграммы и обновление

  1. 1
    Добавь срез

    PivotTable Analyze › Filter › Insert Slicer › отметь Branch и Product. Щелчок по кнопке фильтрует, Ctrl+щелчок выбирает несколько элементов, значок воронки справа вверху сбрасывает фильтр.

  2. 2
    Добавь временную шкалу

    PivotTable Analyze › Filter › Insert Timeline › Date. Протяни полосу, чтобы выбрать период; справа вверху переключай уровни YEARS, QUARTERS, MONTHS, DAYS.

  3. 3
    Один срез — несколько отчётов

    Выдели срез › Slicer › Slicer › Report Connections › отметь все сводные, построенные на том же источнике. Теперь один щелчок фильтрует весь дашборд.

  4. 4
    Построй сводную диаграмму

    PivotTable Analyze › Tools › PivotChart (или Alt+F1 внутри сводной). Диаграмма подчиняется тем же фильтрам; кнопки полей скрывает PivotChart Analyze › Show/Hide › Field Buttons.

Сгруппировать выделенные элементы своднойAlt+Shift+→
РазгруппироватьAlt+Shift+←
Обновить активную сводную таблицуAlt+F5
Обновить всё (Refresh All)Ctrl+Alt+F5
Мгновенная сводная диаграмма из сводной таблицыAlt+F1

Главное

  • Источник — таблица Excel с одной строкой заголовков и без пропусков; новые строки попадают в сводную при обновлении.
  • Даты группируют по месяцам/кварталам/годам, числа — по интервалам, текст — в свои группы.
  • Show Values As показывает доли, разницу, рост в %, нарастающий итог и ранг.
  • Вычисляемое поле работает с итогами: годится для отношений, но не для построчного умножения.
  • Срезы и временные шкалы фильтруют кнопками; Report Connections связывает их с несколькими сводными.

Проверь себя

Вопросов: 10. Каждый правильный ответ приносит XP.

1 / 10
Выручка: Jan 2000, Feb 2690. Что покажет % Difference From (previous) для Feb?