Как сделать срезы в Excel для сводных таблиц и диаграмм
Срез в Excel превращает фильтр сводной таблицы в набор видимых кнопок. На одном учебном наборе создадим срез по регионам, подключим его к двум сводным, проверим связанную диаграмму и разберём ситуацию, когда нужная сводная не появляется в подключениях. Если сводной пока нет, начните с руководства по сводным таблицам Excel. Повторяйте шаги в собственной книге: проверочный XLSX открылся после восстановления и не предлагается как чистый шаблон.
- Подготовьте источник, который можно обновлять
- Создайте первую сводную и срез
- Как читать и оформлять состояние среза
- Подключите один срез к двум сводным
- Добавьте сводную диаграмму
- Контрольный тест: выберите Москву
- Почему срез не работает со второй сводной
- Как обновлять отчёт без скрытого старого состояния
- Чек-лист готового среза
- Что осваивать после одного интерактивного отчёта
- Вопросы и ответы
Срез удобен там, где пользователь должен быстро увидеть текущее состояние фильтра и менять его без раскрытия списков. Но кнопки сами по себе ещё не делают отчёт согласованным: один срез может фильтровать только первую сводную, вторая останется на полном наборе, а обычная диаграмма продолжит показывать старые значения. Поэтому правильный результат проверяют по всей цепочке «источник → сводные → подключения среза → сводная диаграмма».
Ниже — воспроизводимый пример с таблицей из 20 учебных строк, двумя сводными, одним срезом «Регион» и диаграммой. В исходных данных используются только вымышленные имена и синтетические суммы.
Ограничение проверочного файла. При открытии Excel восстановил отдельные элементы созданной книги, а в заголовке окна виден маркер «[восстановлен]». Поэтому мы не выдаём этот XLSX за эталон внутренней целостности. Скриншоты доказывают только то, что непосредственно наблюдалось после восстановления: корректные исходные значения, две согласованные сводные, работу общего среза и изменение сводной диаграммы.
Подготовьте источник, который можно обновлять
Одна строка источника должна описывать одну запись. В учебном примере это продажа с пятью полями: дата, регион, менеджер, категория и выручка. Внутри диапазона нет пустых строк и промежуточных итогов, даты остаются датами, а выручка — числами.
- Выделите любую ячейку источника и создайте таблицу Excel через «Вставка → Таблица» или Ctrl + T.
- Подтвердите, что у таблицы есть заголовки.
- Дайте ей понятное имя, например
SalesData. - Проверьте типы данных: дата не должна быть текстом, сумма — строкой с вписанным знаком валюты.
- Уберите объединённые ячейки, ручные подытоги и пустые строки из тела источника.
Таблица удобнее фиксированного диапазона: после добавления строк они входят в источник при обновлении сводной. Microsoft также называет таблицы Excel подходящим источником для сводных и поясняет работу обновления в обзоре сводных таблиц и диаграмм.
Создайте первую сводную и срез
Если сводная ещё не создана, выделите ячейку таблицы SalesData, выберите «Вставка → Сводная таблица» и разместите отчёт на отдельном листе. Для первого контроля достаточно двух областей:
- поле «Регион» — в строки;
- поле «Выручка» — в значения с операцией «Сумма».
Сверьте общий итог с источником до добавления фильтров. В учебном наборе он равен 597 000: Екатеринбург — 97 000, Казань — 145 000, Москва — 195 000, Санкт-Петербург — 160 000.
Теперь создайте срез:
- Щёлкните внутри сводной таблицы.
- Откройте вкладку анализа сводной таблицы.
- В группе фильтров выберите вставку среза.
- Отметьте поле «Регион» и нажмите OK.
- Нажмите одну кнопку региона и убедитесь, что итог первой сводной изменился.
Названия команд зависят от языка и редакции Excel. В актуальной инструкции Microsoft по срезам путь описан как PivotTable Analyze → Slicer. Там же указаны множественный выбор и команда очистки фильтра.
Как читать и оформлять состояние среза
Выбранные кнопки показывают активный фильтр, а значок очистки возвращает полный набор. Для выбора нескольких несмежных пунктов можно удерживать Ctrl или включить режим множественного выбора, если он доступен в вашей версии.
- Заголовок. Оставьте название поля или замените его понятной подписью, если техническое имя непригодно для читателя.
- Количество столбцов. Увеличьте его для коротких названий, но не обрезайте текст кнопок.
- Размер. Оставьте место для новых значений после обновления источника.
- Стиль. Выбранное и доступное состояние должны различаться не только оттенком, но и оставаться читаемыми.
- Очистка. Проверяйте полный итог после снятия фильтра — это быстрый контроль, что источник и отчёт не потеряли строки.
Срез не меняет исходную таблицу: он управляет представлением связанной сводной. Поэтому сумма после очистки должна вернуться к контрольному итогу источника.
Подключите один срез к двум сводным
Вторая сводная учебного отчёта показывает категории: «Аудит», «Консультация» и «Обучение». Чтобы один срез управлял обеими таблицами, они должны использовать совместимый источник. Самый надёжный путь для обычной книги — скопировать уже созданную сводную в свободную область и заменить поле строк «Регион» на «Категория». Так отчёты сохраняют общий источник и кэш.
- Создайте или скопируйте вторую сводную и назовите обе понятно, например
PivotByRegionиPivotByCategory. - Выделите срез «Регион».
- Откройте вкладку среза и команду «Подключения отчётов» (Report Connections).
- Поставьте флажки у обеих сводных.
- Нажмите OK и по очереди выберите два разных региона.
- Снимите фильтр и сравните оба общих итога: они должны совпасть.
Microsoft прямо указывает, что срез можно подключить только к сводным с общим источником данных. В официальном примере динамической панели Excel каждое подключение задаётся явно: сам факт наличия нескольких сводных на листе их не синхронизирует.
Добавьте сводную диаграмму
Щёлкните внутри сводной по регионам, выберите команду создания сводной диаграммы и подходящий тип, например столбчатую. Важно создать именно сводную диаграмму, связанную с нужной сводной, а не обычный график по случайному диапазону ячеек.
Сводная диаграмма и её сводная таблица используют одно состояние: изменение фильтра в связанной таблице отражается на диаграмме. Это поведение описано Microsoft в обзоре PivotTables и PivotCharts. После подключения среза проверьте не только цифры в таблице, но и категории на оси диаграммы.
Контрольный тест: выберите Москву
В срезе нажимаем «Москва» и сверяем три независимых представления:
| Объект | Ожидаемый результат | Наблюдаемый результат |
|---|---|---|
| Сводная по регионам | Только Москва | Москва — 195 000; общий итог — 195 000 |
| Сводная по категориям | Только строки Москвы | Аудит — 72 000; Консультация — 48 000; Обучение — 75 000 |
| Проверка суммы категорий | Совпадение с итогом региона | 72 000 + 48 000 + 75 000 = 195 000 |
| Сводная диаграмма | Одна категория | Один столбец «Москва» — 195 000 |
Этот тест важнее визуального впечатления. Если одна из четырёх строк не сходится, панель ещё не готова: сначала найдите разрыв подключения или источник несовпадения.
Почему срез не работает со второй сводной
| Симптом | Вероятная причина | Что проверить |
|---|---|---|
| Вторая сводная не видна в «Подключениях отчётов» | Другой источник или несовместимый кэш | Сначала подтвердите точное совпадение источника; безопаснее скопировать уже совместимую сводную, чтобы сохранить общий кэш. Простое повторное создание из той же таблицы может не устранить несовместимость, поэтому снова проверьте Report Connections |
| Вторая сводная видна, но не меняется | Флажок подключения не установлен | Выделите срез и отметьте обе сводные в Report Connections |
| Диаграмма не реагирует | Это обычная диаграмма или она связана с другой сводной | Выберите нужную сводную и создайте PivotChart из неё; повторите тест одного региона |
| После добавления строк итог прежний | Фиксированный диапазон или данные не обновлены | Проверьте источник сводной, преобразуйте диапазон в таблицу и выполните «Обновить всё» |
| Кнопка серого цвета или значение исчезло | Для текущего сочетания фильтров нет данных либо скрыты элементы без данных | Очистите остальные фильтры, проверьте исходные строки и параметры отображения элементов без данных |
| Общие итоги двух сводных различаются | Разные фильтры, источники или операция агрегации | Очистите все фильтры, сравните поля значений и источники, затем обновите обе сводные |
Не лечите несовместимость созданием ещё одного внешне похожего среза. Два независимых фильтра легко оставляют отчёт в противоречивом состоянии. Сначала докажите общий источник, затем одно подключение и только после этого оформляйте панель.
Как обновлять отчёт без скрытого старого состояния
- Добавьте контрольную строку в конец таблицы-источника.
- Выполните «Данные → Обновить всё» или обновите нужную сводную.
- Очистите срез и проверьте новый полный итог.
- Выберите регион контрольной строки и убедитесь, что изменились обе сводные.
- Сверьте категории диаграммы с таблицей, на которой она основана.
Если книга содержит запросы, модель данных или внешние подключения, обновление может иметь дополнительные условия доступа и времени. Эта статья ограничена обычным учебным источником внутри книги и не переносит шаги на все корпоративные модели данных.
Чек-лист готового среза
- Источник имеет одну строку на запись, заголовки и корректные типы данных.
- Полный итог первой сводной совпадает с источником.
- Вторая сводная использует совместимый источник и появляется в подключениях.
- Один срез фильтрует обе сводные.
- После очистки фильтра общие итоги сводных совпадают.
- Сводная диаграмма связана с нужной сводной и меняет категории вместе с ней.
- Контроль одного региона сходится арифметически по всем представлениям.
- После добавления строки и обновления она попадает в отчёт.
Срез считается настроенным не тогда, когда на листе появились красивые кнопки, а когда один выбор даёт согласованный результат в каждой связанной сводной и диаграмме. Для проверенного примера полный итог равен 597 000, а фильтр «Москва» во всех трёх представлениях даёт 195 000.
Что осваивать после одного интерактивного отчёта
Один срез полностью решает локальную задачу фильтрации. Для регулярных отчётов дополнительно нужны навыки подготовки источника, формул и функций, очистки данных, сводных таблиц, Power Query и автоматизации. Эти темы связаны в курсе «Excel + „Google Таблицы“ с нуля до PRO».
От одного работающего среза — к системной подготовке данных и отчётов
Курс «Excel + „Google Таблицы“ с нуля до PRO» помогает последовательно осваивать интерфейс, формулы, очистку данных, сводные таблицы, срезы, визуализацию, Power Query и автоматизацию отчётов. Преподаватель курса — Ренат Шагабутдинов. По итогам обучения выдаётся Удостоверение о повышении квалификации.
Вопросы и ответы
- Как создать срез для сводной таблицы в Excel?
Щёлкните внутри сводной, откройте вкладку анализа сводной таблицы, выберите вставку среза, отметьте нужное поле и нажмите OK. Названия вкладок могут отличаться в зависимости от языка и версии Excel.
- Как подключить один срез к нескольким сводным таблицам?
Сводные должны использовать совместимый общий источник. Выделите срез, откройте «Подключения отчётов» и отметьте нужные сводные. Если второй отчёт не показан в списке, сначала проверьте источник и кэш; часто помогает копия первой сводной с последующей заменой полей.
- Почему срез не показывает вторую сводную в подключениях?
Обычно сводные построены на разных источниках или несовместимых кэшах. Сначала подтвердите точное совпадение источника. Безопаснее скопировать уже совместимую сводную, чтобы сохранить общий кэш. Простое повторное создание на той же таблице может не решить проблему, поэтому после изменения снова проверьте Report Connections.
- Будет ли срез фильтровать обычную диаграмму?
Срез напрямую управляет связанной таблицей или сводной. Для предсказуемого интерактивного отчёта создайте сводную диаграмму из нужной сводной таблицы и проверьте, что после выбора одного значения на диаграмме остаются соответствующие категории.
- Почему новые строки не появляются после обновления?
Проверьте, входит ли строка в источник. Если используется фиксированный диапазон, преобразуйте данные в таблицу Excel или расширьте источник. Затем обновите сводные и очистите фильтры среза для контрольной сверки общего итога.