Пивот табеле нуде Топ 10 филтера. Страва је. Флексибилан је. Али мрзим то, и рећи ћу вам зашто.
Ево пивот табеле која приказује приход по купцу. Укупан приход је 6,7 милиона долара. Приметите да највећи купац, Рото-Роотер, чини 9% укупног прихода.
Шта ако мој менаџер има пажњу златне рибице и жели да види само пет најбољих купаца? За почетак отворите падајући мени у А3 и изаберите Филтери вредности, Топ 10.
Супер флексибилни дијалог Топ 10 Филтер омогућава горњи / доњи део. Може 10, 5 или било који други број. Можете да затражите пет најбољих ставки, 80% или довољно купаца да бисте дошли до 5 милиона долара.
Али овде је проблем: резултујући извештај приказује пет купаца и укупан број тих купаца уместо укупних износа за све. Рото-Роотер, који је раније био 9% од укупног броја, представља 23% новог укупног броја.
Али прво, неколико важних речи о аутоматском филтрирању
Схватам да ово изгледа као питање ван зидова. Ако желите да укључите падајуће падајуће филтере на редовном скупу података, како се то ради? Ево три стварно уобичајена начина:
- Изаберите једну ћелију у подацима и кликните икону Филтер на картици Дата или притисните Цтрл + Схифт + Л.
- Изаберите све податке помоћу Цтрл + * и кликните икону Филтер на картици Дата.
- Притисните Цтрл + Т да бисте податке форматирали као табелу.
Ово су три заиста добра начина. Све док познајете било кога од њих, апсолутно нема потребе да знате други начин. Али ево невероватно нејасног, али магичног начина да укључите филтер:
- Идите до свог реда заглавља, а затим идите до крајње десне ћелије заглавља. Помери једну ћелију удесно. Из непознатог разлога, када се налазите у овој ћелији и кликнете на икону Филтер, Екцел филтрира податке постављене са ваше леве стране. Немам појма зашто ово делује. Заиста не вреди причати о томе јер већ постоје три заиста добра начина за укључивање падајућих падајућих менија Филтер. Ову ћелију називам магичном ћелијом.
И сада, Повратак на изведене табеле
Постоји правило које каже да не можете користити АутоФилтер када сте у пивот табели. Види доле? Икона филтера је сива јер сам изабрао ћелију у пивот табели.
Не знам зашто је Мицрософт ово затамнио. То мора да је нешто унутрашње што каже да АутоФилтер и пивот табела не могу коегзистирати. Дакле, у Екцел тиму постоји неко ко је задужен за сиву икону Филтер. Та особа никада није чула за магичну ћелију. Изаберите ћелију у пивот табели и филтер ће постати сив. Кликните изван пивот табеле и филтер је поново омогућен.
Али чекај. Шта је са магичном ћелијом о којој сам ти малопре говорио? Ако кликнете на ћелију с десне стране последњег заглавља, Екцел ће заборавити да засиви икону Филтер!
Свакако, Екцел додаје падајући мени АутоФилтер у горњи ред ваше изведене табеле. А АутоФилтер функционише другачије од филтера пивот табеле. Идите на падајући мени Приход и одаберите Филтери бројева, Топ 10 ….
У дијалогу Топ 10 АутоФилтер одаберите Топ 6 Итемс. То није грешка у куцању … ако желите пет муштерија, одаберите 6. Ако желите 10 муштерија, одаберите 11.
За АутоФилтер, збирни ред је највећа ставка у подацима. Пет најбољих купаца заузимају позиције од 2 до 6 у подацима.
Опрез
Јасно је да овим триком рушите рупу у Екцел-овом тексту. Ако касније промените основне податке и освежите пивот табелу, Екцел неће освежити филтер, јер, колико Мицрософт зна, не постоји начин да се филтер примени на пивот табелу!
Белешка
Циљ нам је да од Мицрософта ово остане тајна, јер је то прилично добра карактеристика. Већ је неко време „сломљен“, тако да постоји много људи који би се до сада могли на њега поуздати.
Потпуно легално решење у програму Екцел 2013+
Ако желите пивот табелу која приказује пет најбољих купаца, али укупан број свих купаца, податке морате да преместите изван програма Екцел. Ако имате Екцел 2013 или новији који раде у оперативном систему Виндовс, постоји врло згодан начин да то урадите. Да бих вам ово показао, избрисао сам оригиналну пивот табелу. Изаберите Убаци, заокретна табела. Пре него што кликнете на ОК, означите поље за потврду Додај ове податке у модел података.
Изградите своју стожерну табелу као и обично. Помоћу падајућег менија у А3 изаберите Филтери вредности, Топ 10, и тражите пет најбољих купаца. Са изабраном једном ћелијом у пивот табели, идите на картицу Дизајн на траци и отворите падајући мени Међузбир. Коначни избор у падајућем менију је Укључи филтриране ставке у збире. Обично је овај избор сив. Али с обзиром на то да се подаци чувају у моделу података уместо у уобичајеној кеш меморији, ова опција је сада доступна.
Изаберите опцију Укључи филтриране ставке у збире, а ваш збирни збир сада укључује звездицу и укупан број свих података, као што је приказано у наставку.
Овај трик са магичним ћелијама првобитно ми је дошао од Дана на мом семинару у Филаделфији, а поновио га је 15 година касније други Дан са мог семинара у Цинциннатију. Хвала Мигуелу Цабаллеро-у што је предложио ову функцију.
