Филтърът за върхова таблица Top 10 дава общо видимите редове
Осевите таблици предлагат филтър Топ 10. Готино е. Той е гъвкав. Но го мразя и ще ви кажа защо.
Ето една обобщена таблица, показваща приходите по клиенти. Общите приходи са 6,7 милиона долара.
Ами ако моят мениджър има вниманието на златна рибка и иска да види само петте най-добри клиенти?
За да започнете, отворете падащото меню в A3 и изберете Филтри за стойности, Топ 10.
Супер гъвкавият диалогов прозорец Top 10 Filter позволява Top / Bottom. Може да направи 10, 5 или всяко друго число. Можете да поискате първите пет елемента, топ 80% или достатъчно клиенти, за да стигнете до 5 милиона долара.
Но тук е проблемът: Полученият отчет показва пет клиенти и общия брой от тези клиенти, вместо общите от всички.
Но първо, няколко важни думи за AutoFilter
Осъзнавам, че това изглежда като въпрос извън стената. Ако искате да включите падащите менюта Филтър на обикновен набор от данни, как да го направите? Ето три наистина често срещани начина:
- Изберете една клетка във вашите данни и щракнете върху иконата Филтър в раздела Данни.
- Изберете всичките си данни с Ctrl + * и щракнете върху иконата Филтър в раздела Данни.
- Натиснете Ctrl + T, за да форматирате данните като таблица.
Това са три наистина добри начина. Докато познавате някой от тях, няма абсолютно никаква нужда да знаете друг начин. Но ето невероятно неясен, но магически начин за включване на филтъра:
- Отидете до реда си със заглавки, отидете до най-дясната клетка на заглавието. Преместете една клетка надясно. По някаква неизвестна причина, когато сте в тази клетка и щракнете върху иконата Филтър, Excel филтрира зададените отляво данни. Нямам идея защо това работи. Наистина не си струва да се говори, защото вече има три наистина добри начина за включване на падащите менюта Филтър. Наричам тази клетка Магическа клетка.
И сега, Назад към обобщените таблици …
Така че, има правило, което казва, че не можете да използвате автофилтрите, когато сте в обобщена таблица. Виж отдолу? Иконата на филтъра е сива, защото съм избрал клетка в обобщената таблица.
Никога не съм се замислял защо Microsoft отслабва това. Трябва да е нещо вътрешно, което казва, че AutoFilter и Pivot Table не могат да съществуват едновременно. И така, има някой от екипа на Excel, който е натоварен със затъмняването на иконата Филтър. Този човек никога не е чувал за Вълшебната клетка. Изберете клетка в обобщената таблица и филтърът ще стане сив. Щракнете извън осевата таблица и филтърът е активиран отново.
Но почакай. Ами вълшебната клетка, за която току-що ви разказах? Ако щракнете в клетката вдясно от последното заглавие, Excel забравя да засиви иконата на филтъра!
Разбира се, Excel добавя падащи менюта AutoFilter към горния ред на вашата обобщена таблица. И AutoFilter работи по различен начин от филтрите на обобщената таблица. Отидете до падащото меню Приходи и изберете Филтри за числа, Топ 10 …
В диалоговия прозорец Top 10 AutoFilter изберете Top 6 Items. Това не е печатна грешка … Ако искате петима клиенти, изберете 6. Ако искате 10 клиенти, изберете 11.
За AutoFilter, общият ред е най-големият елемент в данните. Петте най-добри клиенти заемат позиции от 2 до 6 в данните.
Внимание
Ясно е, че разкъсвате дупка в тъканта на Excel с този трик. Ако по-късно промените основните данни и опресните своята обобщена таблица, Excel няма да опресни филтъра, тъй като, доколкото Microsoft знае, няма начин да приложите филтър към обобщена таблица!
Забележка
Нашата цел е да запазим това в тайна от Microsoft, защото това е доста готина функция. Той е „счупен“ от доста време, така че има много хора, които може да разчитат на него досега.
Напълно легално решение в Excel 2013+Ако искате обобщена таблица, показваща петте най-добри клиенти, но общата сума от всички клиенти, трябва да преместите данните си извън Excel. Ако имате Excel 2013 или 2016, има много удобен начин да направите това. За да ви покажа това, изтрих оригиналната обобщена таблица. Изберете Insert, Pivot Table. Преди да щракнете върху OK, поставете отметка в квадратчето с надпис Add This Data to the Data Model.
Изградете своята обобщена таблица както обикновено. Използвайте падащото меню в A3, за да изберете Филтри за стойност, Топ 10 и поискайте първите пет клиенти. С избрана една клетка в обобщената таблица, отидете в раздела Дизайн на лентата и отворете падащото меню „Междинни суми“. Крайният избор в падащото меню е Включване на филтрирани елементи в общо. Обикновено този избор е сив. Но тъй като данните се съхраняват в модела на данни, вместо в нормален кеш на ос, тази опция вече е налична.
Изберете опцията Включване на филтрирани елементи в общо и вашият общ сбор вече включва звездичка и общата сума на всички данни.
Този трик първоначално ми дойде от Дан на семинара ми във Филаделфия. Благодарим на Мигел Кабалеро, че предложи тази функция.
Гледам видео
- Филтърът за върхова таблица Top 10 дава общо видимите редове
- Включване на филтрирани елементи в общо е в сиво
- Странен начин за извикване на филтъра за данни от магическата клетка
- Филтрите за данни не са разрешени в обобщените таблици
- Excel не успява да изтрие филтъра за данни от магическата клетка
- Поискайте топ 6, за да получите топ 5 плюс общата сума
- Полезно за филтриране по конкретен опорен елемент
- Excel 2013 или по-нова версия: Различен начин да получите истинската сума
- Изпратете вашите данни чрез модела за данни
- Включване на филтрирани елементи в общо ще бъде налице
- Вземете Total със звездичка
- Научих този трик преди 10+ години от Дан във Филаделфия
Видео транскрипция
Научете Excel за подкаст, епизод 1999 - Pivot Table True Top Five
Подкастирам цялата тази книга. Има плейлист, щракнете върху I в горния десен ъгъл, за да следвате този плейлист. Добре дошли отново в netcast. Аз съм Бил Джелен.
Добре, така че ще създадем обобщена таблица и искаме да покажем не всички клиенти, а само първите пет клиенти. INSERT, Pivot Table. Добре, ще сложа Клиент отляво и Приходи. Добре, така че ето целият ни списък с клиенти, отбелязани като 6,7 милиона долара. Excel, улеснява получаването на първите пет. Влезте в Етикети на редове, Филтри за стойности, топ 10. Не е задължително да е отгоре. Тя може да бъде отгоре или отдолу. Не е задължително да са пет. Може да е двайсет, четиридесет, може и каквото и да е. Топ осемдесет процента, дайте ми достатъчно записи, за да стигна до три милиона долара или четири милиона долара, но ето го. Топ пет елемента. Сега запомнете 6,7 милиона долара, щракнете върху OK и големият ми проблем тук е, че този общ сбор не е 6,7 милиона. Когато дам това на вицепрезидента по продажбите, той ще се побърка, казвайки, изчакайте секунда,Знам, че съм направил повече от 3,3 милиона долара. Добре, така че ще отменим, отменим това и ще се върнем към първоначалните данни.
Сега този следващ трик, който научих по време на един от моите семинари на Power Excel във Филаделфия. Един човек на име Дан на втори ред ми показа това. Преди повече от десет години той ми показа този трик и първо трябва да поговорим за филтрите. Така че обикновено, ако ще използвате обикновения филтър, този филтър тук, вие избирате която и да е клетка във вашия набор от данни и щракнете върху иконата на филтъра, или някои хора избират целия набор от данни, CONTROL * и щракват върху иконата на филтъра, но има и трети начин. Начин, за който никой не се интересува. Ако отидете до последната заглавна клетка, в моя случай това е Cost in L1 и отидете с една клетка вдясно. Наричам това магическа клетка, нямам представа защо, но по някаква неизвестна причина от тази клетка мога да филтрирам съседния набор от данни. Добре, това е като странен начин и никой не се интересува от това.
Нали, тъй като има два други наистина добри начина за извикване на филтър, никой не трябва да знае за магическата клетка, но ето това, вижте вътре в обобщена таблица, то е сиво. Нямате право да използвате тези филтри. Това е против правилата. Сега, ако изляза тук, повече от добре дошъл да използвам филтъра, но вътре те се класират. Не знам кой е човекът, който отсича това, но никога не са чували малкия ми разговор за вълшебната клетка, защото ако отида до последната клетка на заглавието и отида с една клетка вдясно, вижте това те забравят да затъмнят филтъра и сега току-що добавих старите автоматични филтри към обобщената таблица. Така че аз идвам тук, отивам на Филтри за числа, това е различно от Филтри за стойност. Все още се нарича Top Ten. Малко по-различно, ще поискам първите пет, а не първите шест.Шестте най-добри, тъй като за този филтър общата сума е просто още един ред, а общата сума е най-големият елемент и след това, когато се искат елементи от 2 до 6, получавам първите пет елемента.
Добре, така сме. Готин филтър, който ни дава петте най-добри елемента и истинския общ брой на всички. Добре сега, няколко неща. Не забравяйте за вълшебната клетка. Добре, няма начин да изключите този филтър, освен ако не се върнете в магическата клетка. Добре, така че трябва да запомните вълшебната клетка. Освен това, ако промените основните данни и освежите обобщената таблица, те няма да опреснят филтъра, тъй като доколкото Microsoft знае, нямате право да имате филтър.
Това е полезно за други неща. Понякога имаме продукти, които преминават през горната част. Нека да отидем тук в таблична форма. Не е необходимо, просто обичам да получавам истински заглавия. Gizmo, Widget, Gadgets, Doodads. Добре и може би вие сте мениджър на Doodads и трябва да видите само клиентите, които са имали определена стойност и Doodads. Затова отивам до магическата клетка, включвам филтъра и след това под Doodads мога да поискам елементи, които са по-големи от нула. Щракнете върху OK. Добре, този тип филтриране не би било възможно на обикновена обобщена таблица, но е възможно използването на магическата клетка.
Добре сега да отменим списъка. Нека изключим този филтър и премахнем обобщената таблица и ако сте в Excel 2013 или нов, ще ви покажа напълно легален начин да получите точната сума в долната част. Вмъкнете обобщена таблица, долу в долната част, започвайки от Excel 2013, това много безобидно поле не звучи много вълнуващо, добавете тези данни към модела на данни. Това изпраща данните зад кулисите към Power Pivot Engine. Съставете точно същия отчет. Клиентите надолу отляво. Приходи в сърцето на Pivot Table. След това отидете на обикновените Филтри, Филтрите за стойност топ 10. Поискайте първите пет. Забележете отново, че имаме 6,7 милиона долара, след като направя това, 3,3 милиона долара, но ето разликата. Когато отида в раздела Дизайн, под Междинни суми, тази функция, наречена Включване на филтрирани елементи в Суми,вече не е в сиво. При обикновена обобщена таблица не се предлага. Там имаме малка звездичка и това е всичко от всичко. Добре, сега, разбира се, това работи само в Excel 2013 или по-нова версия.
Добре, ще ми отнеме шест седмици, докато изведа цялата тази книга тук в YouTube. Тук има толкова много добри съвети. Съвети, които могат да започнат да ви спестят време, веднага. Купете цялата книга точно сега и ще имате достъп до всичките 40, Това всъщност е много повече от 40 съвета. Клавишни комбинации на Excel. Всякакви страхотни неща в тази книга.
Добре, резюме. Така че, когато правим Pivot Table top 10 филтър, той ни дава общия брой, но само видимите редове, а не нещата, които е филтрирал. Да, ако отидем на втория раздел и потърсим Междинни суми, Филтрирани елементи и Общи суми, това е сиво, но има странен начин да извикате Стария филтър за данни от магическата клетка. Последната клетка на заглавието, отидете с една клетка надясно, не можете да използвате филтри и обобщени таблици, но ако отидете до магическата клетка, те забравят да я засипят. Сега в Числения филтър искаш първите шест, за да получиш първите пет, плюс общия сбор. Също така полезно за филтриране към определен Pivot елемент: Doodads, всичко, което е имало повече от 0 в Doodads или топ 5 Doodads. Excel 2013 или по-нова версия, има различен начин да получите True Total.Поставете отметка в квадратчето за Модел на данни и след това ще включва филтрирани елементи в Общо. Получавате сумата със звездичка. И благодарение на Дан във Филаделфия, който ме показа на един от моите семинари за Power Excel преди повече от десет години и ми даде този страхотен малък трик. Начин за филтъра да се промъкне през стената на Club Pivot Table. Те обикновено не позволяват този автоматичен филтър.
Хей, искам да ти благодаря, че се отби. Ще се видим следващия път, за поредното излъчване от MRExcel.
Свали файл
Изтеглете примерния файл тук: Podcast1999.xlsx
