Осевите таблици предлагат филтър Топ 10. Готино е. Той е гъвкав. Но го мразя и ще ви кажа защо.
Ето една обобщена таблица, която показва приходите по клиенти. Общите приходи са 6,7 милиона долара. Забележете, че най-големият клиент, Roto-Rooter, е 9% от общите приходи.
Ами ако моят мениджър има вниманието на златна рибка и иска да види само петте най-добри клиенти? За да започнете, отворете падащото меню в A3 и изберете Филтри за стойности, Топ 10.
Супер гъвкавият диалогов прозорец Top 10 Filter позволява Top / Bottom. Може да направи 10, 5 или всяко друго число. Можете да поискате първите пет елемента, топ 80% или достатъчно клиенти, за да стигнете до 5 милиона долара.
Но тук е проблемът: Полученият отчет показва пет клиенти и общия брой от тези клиенти, вместо общите от всички. Roto-Rooter, който преди това е бил 9% от общия брой, е 23% от новия общ.
Но първо, няколко важни думи за AutoFilter
Осъзнавам, че това изглежда като въпрос извън стената. Ако искате да включите падащите менюта Филтър на обикновен набор от данни, как да го направите? Ето три наистина често срещани начина:
- Изберете една клетка във вашите данни и щракнете върху иконата Филтър в раздела Данни или натиснете Ctrl + Shift + L.
- Изберете всичките си данни с Ctrl + * и щракнете върху иконата Филтър в раздела Данни.
- Натиснете Ctrl + T, за да форматирате данните като таблица.
Това са три наистина добри начина. Докато познавате някой от тях, няма абсолютно никаква нужда да знаете друг начин. Но ето невероятно неясен, но магически начин за включване на филтъра:
- Отидете до вашия ред заглавки и след това отидете до най-дясната клетка на заглавието. Преместете една клетка надясно. По някаква неизвестна причина, когато сте в тази клетка и щракнете върху иконата Филтър, Excel филтрира зададените отляво данни. Нямам идея защо това работи. Наистина не си струва да се говори, защото вече има три наистина добри начина за включване на падащите менюта Филтър. Наричам тази клетка магическа клетка.
И сега, Назад към обобщените таблици
Има правило, което казва, че не можете да използвате AutoFilter, когато сте в обобщена таблица. Виж отдолу? Иконата на филтъра е сива, защото съм избрал клетка в обобщената таблица.
Не знам защо Microsoft отслабва това. Трябва да е нещо вътрешно, което казва, че AutoFilter и обобщена таблица не могат да съществуват едновременно. И така, има някой от екипа на 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 или по-нова версия, работеща в Windows, има много удобен начин да направите това. За да ви покажа това, изтрих оригиналната обобщена таблица. Изберете Вмъкване, Осева таблица. Преди да щракнете върху OK, поставете отметка в квадратчето Добавяне на тези данни към модела на данни.
Изградете своята обобщена таблица както обикновено. Използвайте падащото меню в A3, за да изберете Филтри за стойност, Топ 10 и поискайте първите пет клиенти. След като изберете една клетка в обобщената таблица, отидете в раздела Дизайн в лентата и отворете падащото меню „Междинни суми“. Крайният избор в падащото меню е Включване на филтрирани елементи в общо. Обикновено този избор е сив. Но тъй като данните се съхраняват в модела на данни, вместо в нормален кеш на ос, тази опция вече е налична.
Изберете опцията Включване на филтрирани елементи в общо и вашият общ сбор вече включва звездичка и общата сума на всички данни, както е показано по-долу.
Този трик с вълшебни клетки първоначално ми дойде от Дан в семинара ми във Филаделфия и беше повторен 15 години по-късно от друг Дан от семинара ми в Синсинати. Благодарим на Мигел Кабалеро, че предложи тази функция.
