Реклама
Этот сайт использует Яндекс.Метрику для анализа посещаемости. Подробнее
Таблицы и сводные таблицы

Как показать истинные топ-5 клиентов в сводной таблице Excel с правильными итогами

forest_paw 28.10.2025 1037

Как показать истинные топ-5 клиентов в сводной таблице Excel с правильными итогами

В обычной сводной таблице фильтр «Первые 10» меняет набор элементов, включённых в общий итог. Это ожидаемое поведение, но оно не подходит, если нужна доля клиента от всей базы до отбора топ-5. В этой статье мы разберем два эффективных способа, как отобразить топ-5 клиентов, сохранив при этом корректные итоги от всей базы данных.

Руководство пригодится тем, кто хочет понять, как сделать сводную таблицу в Excel, настроить поля, расчеты и получить наглядный отчет по данным.

Представьте сводную таблицу с выручкой по клиентам. После применения фильтра "Топ-5" общая сумма пересчитывается только для этих пяти клиентов, из-за чего их процентная доля становится завышенной и не отражает реальной картины.

Сводная таблица с клиентами в столбце A, суммой выручки в B и процентом от общего итога в C.
Сводная таблица с клиентами в столбце A, суммой выручки в B и процентом от общего итога в C.

Метод 1: Автофильтр отдельной копии отчёта

Для разового отчёта сначала рассчитайте суммы и «% от общего итога» по всем клиентам, а затем фильтруйте статичную копию значений. Это позволяет сохранить исходный знаменатель без недокументированного автофильтра внутри сводной.

  1. Снимите фильтр топ-N, обновите сводную и сохраните общий итог всей базы отдельно. Скопируйте заголовки, строки всех клиентов, их суммы и проценты на новый лист через «Специальная вставка → Значения».
  2. Выделите в копии только заголовки и строки клиентов. Исключите строку общего итога из диапазона автофильтра и включите «Данные → Фильтр».
На исходной иллюстрации фильтр включён рядом со сводной. В этой инструкции используйте отдельную копию значений на новом листе.
На исходной иллюстрации фильтр включён рядом со сводной. В этой инструкции используйте отдельную копию значений на новом листе.

Для обычного диапазона копии в Excel для Windows автофильтр также включается сочетанием Ctrl + Shift + L. Проценты должны быть вставлены как значения, чтобы скрытие строк их не пересчитывало.

  1. Откройте выпадающий список в заголовке столбца с выручкой и выберите "Числовые фильтры" → "Топ-10...".
Открывается выпадающий список из заголовка 'Сумма выручки'. Выберите 'Числовые фильтры', затем 'Топ-10'.
Открывается выпадающий список из заголовка 'Сумма выручки'. Выберите 'Числовые фильтры', затем 'Топ-10'.
  1. В диалоговом окне установите: «Первые» 5 «Элементов». Общий итог в диапазон не входит, поэтому добавлять шестой элемент не нужно. При равенстве значений на границе фильтр может оставить больше пяти клиентов; для ровно пяти отсортируйте по выручке и задайте дополнительный порядок по имени.
На исходном скриншоте выбрано 6 элементов вместе с общим итогом. Для отдельной копии без итоговой строки выберите 5.
На исходном скриншоте выбрано 6 элементов вместе с общим итогом. Для отдельной копии без итоговой строки выберите 5.

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

Сводная таблица показывает исходный общий итог, а затем топ-5 клиентов с их исходным процентом от общего итога.
Сводная таблица показывает исходный общий итог, а затем топ-5 клиентов с их исходным процентом от общего итога.

Важно: копия значений статична. После изменения данных обновите исходную сводную, заново скопируйте результаты и повторите отбор. Она не синхронизируется со сводной автоматически.

Метод 2: Явные меры в модели данных

В настольном Excel для Windows с поддержкой модели данных и Power Pivot можно явно задать знаменатель. Для учебного примера назовите таблицу Sales, столбец клиента — Customer, выручки — Revenue. Пример ниже считает долю от всей таблицы Sales, снимая и фильтры периода; если нужен итог только выбранного периода, модель и формулу необходимо настроить отдельно.

  1. Создайте таблицу Sales, затем выберите «Вставка → Сводная таблица».
  2. Отметьте «Добавить эти данные в модель данных». Через «Power Pivot → Меры → Создать меру» создайте три меры: TotalRevenue с формулой =SUM(Sales[Revenue]), AllRevenue с формулой =CALCULATE([TotalRevenue],ALL(Sales)) и ShareAll с формулой =DIVIDE([TotalRevenue],[AllRevenue]). Имена функций DAX не переводятся; при региональном разделителе «;» замените запятые между аргументами.
  3. Добавьте Customer в строки, а TotalRevenue и ShareAll — в значения. Задайте ShareAll процентный формат. Сначала без фильтра убедитесь, что общий итог TotalRevenue совпадает с AllRevenue.
  4. В меню поля Customer выберите «Фильтры по значению → Первые 10…», укажите первые 5 элементов по мере TotalRevenue. Доля ShareAll продолжит использовать AllRevenue как знаменатель.
  5. Для отображения полной базы добавьте AllRevenue в отдельную сводную без фильтра клиентов. Итог TotalRevenue в отфильтрованном отчёте — сумма выбранных клиентов; AllRevenue — сумма всей базы. Подпишите эти показатели по-разному и не заменяйте ShareAll стандартным «% от общего итога».
На иллюстрации показана другая настройка: «Включать отфильтрованные элементы в итоги». Её доступность зависит от источника; явные меры в этом примере от неё не зависят.
На иллюстрации показана другая настройка: «Включать отфильтрованные элементы в итоги». Её доступность зависит от источника; явные меры в этом примере от неё не зависят.

Команда «Конструктор → Промежуточные итоги → Включать отфильтрованные элементы в итоги» — отдельная возможность для поддерживаемых источников. Она не гарантирует одинакового поведения всех моделей и фильтров. При использовании мер выше проверяйте сами значения: например, при общей выручке 1000 и выручке клиента 200 его доля должна остаться 20% после отбора.

Пример отчёта с итогом, включающим скрытые элементы. Звёздочка на иллюстрации не заменяет проверку знаменателя; в примере с мерами используйте AllRevenue и ShareAll.
Пример отчёта с итогом, включающим скрытые элементы. Звёздочка на иллюстрации не заменяет проверку знаменателя; в примере с мерами используйте AllRevenue и ShareAll.

Заключение

Для разового отчёта используйте статичную копию с сохранёнными процентами. Для обновляемого отчёта в модели данных задайте знаменатель мерой и явно различайте итог выбранных клиентов и итог всей базы. После обновления проверьте оба итога и состав топ-5; при нулевом знаменателе DIVIDE без третьего аргумента возвращает пустое значение.

Поделиться:

Мы используем cookies для улучшения работы сайта. Продолжая использовать сайт, вы соглашаетесь с политикой конфиденциальности.