Фильтр Top 10 сводной таблицы дает общее количество видимых строк
Сводные таблицы предлагают фильтр Top 10. Это круто. Он гибкий. Но я ненавижу это, и я скажу вам почему.
Вот сводная таблица, показывающая доход по клиентам. Общая выручка составляет 6,7 миллиона долларов.
Что, если мой менеджер внимателен как золотая рыбка и хочет видеть только пятерку лучших клиентов?
Для начала откройте раскрывающийся список в A3 и выберите Value Filters, Top 10.
Сверхгибкий диалог Top 10 Filter позволяет использовать Top / Bottom. Он может делать 10, 5 или любое другое число. Вы можете запросить пять лучших товаров, лучшие 80% или достаточное количество клиентов, чтобы получить 5 миллионов долларов.
Но вот в чем проблема: итоговый отчет показывает пять клиентов и общее количество от этих клиентов, а не общее количество от всех.
Но сначала несколько важных слов об автофильтре.
Я понимаю, что это кажется необычным вопросом. Если вы хотите включить раскрывающиеся списки Фильтр для обычного набора данных, как вы это сделаете? Вот три действительно распространенных способа:
- Выберите одну ячейку в данных и щелкните значок «Фильтр» на вкладке «Данные».
- Выделите все данные с помощью 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. Если у вас есть Excel 2013 или 2016, есть очень удобный способ сделать это. Чтобы показать вам это, я удалил исходную сводную таблицу. Выберите «Вставка», «Сводная таблица». Прежде чем нажать кнопку ОК, установите флажок «Добавить эти данные в модель данных».
Создайте сводную таблицу как обычно. Используйте раскрывающийся список в A3, чтобы выбрать Value Filters, Top 10 и спросить пять лучших клиентов. Выделив одну ячейку в сводной таблице, перейдите на вкладку «Дизайн» на ленте и откройте раскрывающийся список «Промежуточные итоги». Последний вариант в раскрывающемся списке - включить отфильтрованные элементы в итоги. Обычно этот выбор неактивен. Но поскольку данные хранятся в модели данных, а не в обычном сводном кэше, эта опция теперь доступна.
Выберите вариант «Включить отфильтрованные элементы в итоги», и ваша общая сумма теперь включает звездочку и сумму всех данных.
Этот трюк изначально пришел ко мне от Дэна на моем семинаре в Филадельфии. Спасибо Мигелю Кабальеро за предложение этой функции.
Смотреть видео
- Фильтр Top 10 сводной таблицы дает общее количество видимых строк
- Включить отфильтрованные элементы в итоги выделено серым цветом
- Странный способ вызвать фильтр данных из волшебной ячейки
- Фильтры данных нельзя использовать в сводных таблицах.
- Excel не может скрыть фильтр данных серым цветом из волшебной ячейки
- Спросите 6 лучших, чтобы получить 5 лучших плюс общий итог
- Полезно для фильтрации по определенному элементу сводки
- Excel 2013 или новее: другой способ получить истинную сумму
- Отправьте свои данные через модель данных
- Будет доступно включение отфильтрованных элементов в итоги
- Получить сумму со звездочкой
- Я научился этому трюку более 10 лет назад от Дэна в Филадельфии.
Стенограмма видео
Learn Excel for Podcast, Episode 1999 - Pivot Table True Top Five
Я подкастирую всю эту книгу. Есть плейлист, нажмите I в правом верхнем углу, чтобы следить за ним. Добро пожаловать в сетевую трансляцию. Я Билл Джелен.
Хорошо, поэтому мы собираемся создать сводную таблицу, и мы хотим показать не всех клиентов, а только пять лучших клиентов. INSERT, сводная таблица. Хорошо, я положу клиента в левую часть и выручку. Итак, вот весь наш список клиентов - 6,7 миллиона долларов. Excel позволяет легко составить пятерку лучших. Перейдите в раздел «Ярлыки строк», «Фильтры значений», верхний 10. Не обязательно быть верхним. Он может быть верхним или нижним. Не обязательно должно быть пять. Может быть двадцать, сорок, может быть что угодно. Лучшие восемьдесят процентов, дайте мне достаточно записей, чтобы получить три миллиона долларов или четыре миллиона долларов, но поехали. Пятерка лучших предметов. Теперь вспомните 6,7 миллиона долларов, нажмите ОК, и моя большая проблема в том, что общая сумма не равна 6,7 миллионам. Когда я передам это вице-президенту по продажам, он взбесится и скажет: «Подожди секунду,Я знаю, что заработал более 3,3 миллиона долларов. Итак, мы собираемся отменить, отменить это и вернуться к исходным данным.
А теперь следующий трюк, которому я научился во время одного из моих семинаров по Power Excel в Филадельфии. Парень по имени Дэн во втором ряду показал мне это. Он показал мне этот трюк более десяти лет назад, и сначала мы должны поговорить о фильтрах. Итак, обычно, если вы собираетесь использовать обычный фильтр, этот фильтр здесь, вы выбираете любую ячейку в своем наборе данных и щелкаете значок фильтра, или некоторые люди выбирают весь набор данных, КОНТРОЛЬ * и щелкают значок фильтра, но есть и третий способ. Путь, который никого не волнует. Если вы перейдете к самой последней ячейке заголовка, в моем случае это Стоимость в L1, и перейдете на одну ячейку вправо. Я называю это волшебной ячейкой, я понятия не имею, почему, но по какой-то неизвестной причине из этой ячейки я могу фильтровать соседний набор данных. Хорошо, это как-то странно, и никого это не волнует.
Да, поскольку есть два других действительно хороших способа вызвать фильтр, никому не нужно знать о волшебной ячейке, но вот что: посмотрите внутри сводной таблицы, она неактивна. Вам не разрешено использовать эти фильтры. Это против правил. Теперь, если я приду сюда, я буду более чем рад использовать фильтр, но внутри они выходят из строя. Я не знаю, кто это делает серым, но они никогда не слышали моего маленького разговора о волшебной ячейке, потому что если я перейду к самой последней ячейке заголовка и пройду на одну ячейку вправо, посмотрите на нее, они забывают затенять фильтр серым, и теперь я только что добавил старые автоматические фильтры в сводную таблицу. Итак, я прихожу сюда, перехожу к числовым фильтрам, которые отличаются от фильтров значений. Он по-прежнему называется Top Ten. Немного по-другому, я попрошу пятерку лучших, а не шестерку.Шесть лучших, потому что для этого фильтра общая сумма - это просто еще одна строка, а общая сумма - это самый большой элемент, а затем, когда меня просят указать элементы со 2 по 6, я получаю пять лучших элементов.
Хорошо, вот и мы. Классный хак с фильтром, который дает нам пять лучших пунктов и истинную сумму всех. Хорошо, теперь пара вещей. Не забывайте про волшебную клетку. Хорошо, нет возможности отключить этот фильтр, если вы не вернетесь в волшебную ячейку. Хорошо, тебе нужно запомнить волшебную ячейку. Кроме того, если вы измените базовые данные и обновите сводную таблицу, они не будут обновлять фильтр, потому что, насколько известно Microsoft, вам не разрешено иметь фильтр.
Это полезно для других вещей. Иногда продукты выходят на первое место. Давайте перейдем к табличной форме. Не обязательно, я просто хочу получать настоящие заголовки. Gizmo, Widget, Gadgets, Doodads. Хорошо, и, возможно, вы менеджер Doodads и вам нужно видеть только клиентов, которые имеют определенную ценность, и Doodads. Итак, я перехожу в волшебную ячейку, включаю фильтр, а затем в разделе «Doodads» я могу попросить предметы, которые больше нуля. Щелкните ОК. Хорошо, этот тип фильтрации был бы невозможен для обычной сводной таблицы, но это возможно с использованием волшебной ячейки.
Хорошо, теперь давайте отменим список. Давайте отключим этот фильтр и удалим сводную таблицу, и если вы используете Excel 2013 или новичок, я покажу вам полностью законный способ получить правильную сумму внизу. Вставьте сводную таблицу здесь внизу, начиная с этого безобидного окна в Excel 2013, звучит не очень интересно, добавьте эти данные в модель данных. Это негласно отправляет данные в Power Pivot Engine. Создайте точно такой же отчет. Клиенты внизу слева. Доход в центре сводной таблицы. Затем перейдите к обычным фильтрам, верхние 10 фильтров значений. Спросите пятерку лучших. Обратите внимание, что после того, как я это сделаю, у нас будет 6,7 миллиона долларов, 3,3 миллиона долларов, но вот разница. Когда я перехожу на вкладку «Дизайн» в разделе «Промежуточные итоги», эта функция называется «Включить отфильтрованные элементы в итоги»,больше не отображается серым цветом. У обычной сводной таблицы нет. Получаем там звездочку и все в сумме. Хорошо, теперь, конечно, это работает только в Excel 2013 или новее.
Хорошо, у меня уйдет шесть недель, чтобы выложить всю эту книгу на YouTube. Здесь так много хороших советов. Советы, которые помогут сразу же сэкономить ваше время. Купите всю книгу прямо сейчас, и у вас будет доступ ко всем 40, на самом деле это намного больше, чем 40 советов. Сочетания клавиш Excel. В этой книге есть множество замечательных вещей.
Хорошо, резюме. Поэтому, когда мы выполняем фильтр «Топ 10» сводной таблицы, он дает нам общее количество, но только видимые строки, а не то, что было отфильтровано. Да, если мы перейдем на вторую вкладку и поищем промежуточные итоги, отфильтрованные элементы и итоги, они будут выделены серым цветом, но есть странный способ вызвать фильтр старых данных из волшебной ячейки. Самая последняя ячейка заголовка, перейдите на одну ячейку вправо, вы не можете использовать фильтры и сводные таблицы, но если вы перейдете в волшебную ячейку, они забывают ее затенять. Теперь в числовом фильтре вы просите шесть лучших, чтобы они попали в пятерку, а также общую сумму. Также полезно для фильтрации по определенному элементу Pivot: Doodads, все, что имеет больше 0 в Doodads или 5 лучших Doodads. В Excel 2013 или новее есть другой способ получить Истинную сумму.Установите этот флажок для модели данных, а затем включите отфильтрованные элементы в итоги. Сумма помечена звездочкой. И спасибо Дэну из Филадельфии, который показал мне на одном из моих семинаров Power Excel более десяти лет назад и дал мне эту замечательную маленькую уловку. Способ проникновения фильтра через стену клубного сводного стола. Обычно они не позволяют использовать этот автоматический фильтр.
Эй, я хочу поблагодарить тебя за то, что заглянули. Увидимся в следующий раз, чтобы посмотреть еще одну трансляцию от MRExcel.
Скачать файл
Загрузите образец файла здесь: Podcast1999.xlsx
