Сводные таблицы предлагают фильтр Top 10. Это круто. Он гибкий. Но я ненавижу это, и я скажу вам почему.
Вот сводная таблица, показывающая доход по клиентам. Общая выручка составляет 6,7 миллиона долларов. Обратите внимание, что самый крупный клиент, Roto-Rooter, составляет 9% от общего дохода.
Что, если мой менеджер внимателен как золотая рыбка и хочет видеть только пятерку лучших клиентов? Для начала откройте раскрывающийся список в A3 и выберите Value Filters, Top 10.
Сверхгибкий диалог Top 10 Filter позволяет использовать Top / Bottom. Он может делать 10, 5 или любое другое число. Вы можете запросить пять лучших товаров, лучшие 80% или достаточное количество клиентов, чтобы получить 5 миллионов долларов.
Но вот в чем проблема: итоговый отчет показывает пять клиентов и общее количество от этих клиентов, а не общее количество от всех. Roto-Rooter, который ранее составлял 9% от общего числа, составляет 23% от нового числа.
Но сначала несколько важных слов об автофильтре.
Я понимаю, что это кажется необычным вопросом. Если вы хотите включить раскрывающиеся списки Фильтр для обычного набора данных, как вы это сделаете? Вот три действительно распространенных способа:
- Выделите одну ячейку в данных и щелкните значок «Фильтр» на вкладке «Данные» или нажмите Ctrl + Shift + L.
- Выделите все данные с помощью Ctrl + * и щелкните значок «Фильтр» на вкладке «Данные».
- Нажмите Ctrl + T, чтобы отформатировать данные в виде таблицы.
Это три действительно хороших способа. Пока вы знаете какой-либо из них, нет никакой необходимости знать другой способ. Но вот невероятно непонятный, но волшебный способ включить фильтр:
- Перейдите к строке заголовков, а затем перейдите к самой правой ячейке заголовка. Переместите одну ячейку вправо. По неизвестной причине, когда вы находитесь в этой ячейке и щелкаете значок «Фильтр», Excel фильтрует набор данных слева от вас. Понятия не имею, почему это работает. Об этом действительно не стоит говорить, потому что уже есть три действительно хороших способа включить раскрывающиеся списки фильтров. Я называю эту ячейку волшебной ячейкой.
А теперь вернемся к сводным таблицам
Существует правило, согласно которому вы не можете использовать автофильтр, когда находитесь в сводной таблице. См. ниже? Значок фильтра неактивен, потому что я выбрал ячейку в сводной таблице.
Я не знаю, почему Microsoft скрывает это. Это должно быть что-то внутреннее, что говорит о том, что автофильтр и сводная таблица не могут сосуществовать. Итак, в команде Excel есть кто-то, кто отвечает за выделение серого значка фильтра. Этот человек никогда не слышал о волшебной клетке. Выберите ячейку в сводной таблице, и фильтр станет серым. Щелкните за пределами сводной таблицы, и фильтр снова будет включен.
Но ждать. А как насчет волшебной клетки, о которой я вам только что рассказал? Если щелкнуть ячейку справа от последнего заголовка, Excel забудет выделить серый значок фильтра!
Разумеется, Excel добавляет раскрывающиеся списки Автофильтра в верхнюю строку вашей сводной таблицы. Автофильтр работает иначе, чем фильтр сводной таблицы. Перейдите в раскрывающийся список "Доход" и выберите "Числовые фильтры", "Топ 10 …".
В диалоговом окне Top 10 AutoFilter выберите Top 6 Items. Это не опечатка… если вам нужно пять клиентов, выберите 6. Если вам нужно 10 клиентов, выберите 11.
Для автофильтра строка общего итога является самым большим элементом данных. Первые пять клиентов занимают позиции со 2 по 6 в данных.
Осторожно
Ясно, что этим трюком вы продираете дыру в ткани Excel. Если позже вы измените базовые данные и обновите сводную таблицу, Excel не обновит фильтр, потому что, насколько известно Microsoft, нет способа применить фильтр к сводной таблице!
Заметка
Наша цель - сохранить это в секрете от Microsoft, потому что это довольно крутая функция. Он был «сломан» в течение некоторого времени, так что уже есть много людей, которые могут полагаться на него.
Полностью легальное решение в Excel 2013+
Если вы хотите, чтобы сводная таблица показывала вам пять основных клиентов, но общее количество клиентов, вам нужно вынести свои данные за пределы Excel. Если у вас в Windows работает Excel 2013 или новее, есть очень удобный способ сделать это. Чтобы показать вам это, я удалил исходную сводную таблицу. Выберите «Вставка», «Сводная таблица». Перед тем, как нажать ОК, установите флажок Добавить эти данные в модель данных.
Создайте сводную таблицу как обычно. Используйте раскрывающийся список в A3, чтобы выбрать Value Filters, Top 10 и спросить пять лучших клиентов. Выделив одну ячейку в сводной таблице, перейдите на вкладку «Дизайн» на ленте и откройте раскрывающийся список «Промежуточные итоги». Последний вариант в раскрывающемся списке - включить отфильтрованные элементы в итоги. Обычно этот выбор неактивен. Но поскольку данные хранятся в модели данных, а не в обычном сводном кэше, эта опция теперь доступна.
Выберите параметр «Включить отфильтрованные элементы в итоги», и ваша общая сумма теперь будет включать звездочку и сумму всех данных, как показано ниже.
Этот трюк с волшебной клеткой первоначально пришел ко мне от Дэна на моем семинаре в Филадельфии и был повторен 15 лет спустя другим Дэном из моего семинара в Цинциннати. Спасибо Мигелю Кабальеро за предложение этой функции.
