Как показать истинные топ-5 клиентов в сводной таблице Excel с правильными итогами
В обычной сводной таблице фильтр «Первые 10» меняет набор элементов, включённых в общий итог. Это ожидаемое поведение, но оно не подходит, если нужна доля клиента от всей базы до отбора топ-5. В этой статье мы разберем два эффективных способа, как отобразить топ-5 клиентов, сохранив при этом корректные итоги от всей базы данных.
Руководство пригодится тем, кто хочет понять, как сделать сводную таблицу в Excel, настроить поля, расчеты и получить наглядный отчет по данным.
Представьте сводную таблицу с выручкой по клиентам. После применения фильтра "Топ-5" общая сумма пересчитывается только для этих пяти клиентов, из-за чего их процентная доля становится завышенной и не отражает реальной картины.
Метод 1: Автофильтр отдельной копии отчёта
Для разового отчёта сначала рассчитайте суммы и «% от общего итога» по всем клиентам, а затем фильтруйте статичную копию значений. Это позволяет сохранить исходный знаменатель без недокументированного автофильтра внутри сводной.
- Снимите фильтр топ-N, обновите сводную и сохраните общий итог всей базы отдельно. Скопируйте заголовки, строки всех клиентов, их суммы и проценты на новый лист через «Специальная вставка → Значения».
- Выделите в копии только заголовки и строки клиентов. Исключите строку общего итога из диапазона автофильтра и включите «Данные → Фильтр».
Для обычного диапазона копии в Excel для Windows автофильтр также включается сочетанием Ctrl + Shift + L. Проценты должны быть вставлены как значения, чтобы скрытие строк их не пересчитывало.
- Откройте выпадающий список в заголовке столбца с выручкой и выберите "Числовые фильтры" → "Топ-10...".
- В диалоговом окне установите: «Первые» 5 «Элементов». Общий итог в диапазон не входит, поэтому добавлять шестой элемент не нужно. При равенстве значений на границе фильтр может оставить больше пяти клиентов; для ровно пяти отсортируйте по выручке и задайте дополнительный порядок по имени.
В копии останутся строки крупнейших клиентов, а сохранённые проценты будут относиться к общей сумме до отбора. Отдельный итог всей базы держите за пределами фильтруемого диапазона и подпишите его.
Важно: копия значений статична. После изменения данных обновите исходную сводную, заново скопируйте результаты и повторите отбор. Она не синхронизируется со сводной автоматически.
Метод 2: Явные меры в модели данных
В настольном Excel для Windows с поддержкой модели данных и Power Pivot можно явно задать знаменатель. Для учебного примера назовите таблицу Sales, столбец клиента — Customer, выручки — Revenue. Пример ниже считает долю от всей таблицы Sales, снимая и фильтры периода; если нужен итог только выбранного периода, модель и формулу необходимо настроить отдельно.
- Создайте таблицу Sales, затем выберите «Вставка → Сводная таблица».
- Отметьте «Добавить эти данные в модель данных». Через «Power Pivot → Меры → Создать меру» создайте три меры: TotalRevenue с формулой
=SUM(Sales[Revenue]), AllRevenue с формулой=CALCULATE([TotalRevenue],ALL(Sales))и ShareAll с формулой=DIVIDE([TotalRevenue],[AllRevenue]). Имена функций DAX не переводятся; при региональном разделителе «;» замените запятые между аргументами. - Добавьте Customer в строки, а TotalRevenue и ShareAll — в значения. Задайте ShareAll процентный формат. Сначала без фильтра убедитесь, что общий итог TotalRevenue совпадает с AllRevenue.
- В меню поля Customer выберите «Фильтры по значению → Первые 10…», укажите первые 5 элементов по мере TotalRevenue. Доля ShareAll продолжит использовать AllRevenue как знаменатель.
- Для отображения полной базы добавьте AllRevenue в отдельную сводную без фильтра клиентов. Итог TotalRevenue в отфильтрованном отчёте — сумма выбранных клиентов; AllRevenue — сумма всей базы. Подпишите эти показатели по-разному и не заменяйте ShareAll стандартным «% от общего итога».
Команда «Конструктор → Промежуточные итоги → Включать отфильтрованные элементы в итоги» — отдельная возможность для поддерживаемых источников. Она не гарантирует одинакового поведения всех моделей и фильтров. При использовании мер выше проверяйте сами значения: например, при общей выручке 1000 и выручке клиента 200 его доля должна остаться 20% после отбора.
Заключение
Для разового отчёта используйте статичную копию с сохранёнными процентами. Для обновляемого отчёта в модели данных задайте знаменатель мерой и явно различайте итог выбранных клиентов и итог всей базы. После обновления проверьте оба итога и состав топ-5; при нулевом знаменателе DIVIDE без третьего аргумента возвращает пустое значение.