A szűrés az Excel 365 új funkcióival sokkal könnyebbé válik

2018 ősze óta jelentéseket követek az Excel új dinamikus tömbfüggvényeiről. Eddig azonban ezeket csak az „Insider” és a „Monthly target” frissítési csatornával rendelkező felhasználók használhatták. Az olyan funkciók, mint az UNIQUE, a FILTER, a SORT és a SORT AFTER, már elérhetőek a „havi” frissítési csatornán is.
Mivel a listák rendezése és szűrése az Excel egyik gyakori feladata, először a SZŰRŐ és RENDEZÉS funkciókat próbáltam ki. Egy dolgot előre tudok mondani: csodálkozom, hogy milyen könnyű működtetni, és izgatott vagyok a sok lehetőség miatt. Itt van egy első példa.

A kész eredmény előnézete a FILTER segítségével

A jövőben egy gombnyomásra lehetséges lesz: a szűrt adatok megjelenítése és nyomtatása külön lapra

Akiknek gyakran szűrniük kell adataikat, tudják a problémát:

Először a szűrőt állítják be.
Ezután a szűrő kritériuma be van állítva.
A szűrt adatbázist ezután egy külön lapra másolja.
Végül egy címsort adunk hozzá, és kinyomtatjuk az eredményt.

Az új funkcióval SZŰRŐ ez sokkal könnyebb és gyorsabb:

Csak válassza ki a kívánt szűrőkritériumot egy cellában, például egy terméket, egy vevőt vagy egy régiót.
A SZŰRŐ funkció azonnal elkészíti az alkalmazandó adatrekordok listáját. BEFEJEZETT.

Így működik: A SZŰRŐ segítségével csak bizonyos adatrekordokat sorolhat fel

A következő ábra az adatkészletet mutatja: Dátum, tételcsoport, régió, vevő, értékesítési képviselő (ADM) és nettó árbevétel szerint rögzített értékesítések.

Változtatom a listát az értékesítési adatokkal is Ctrl + T "intelligens" asztalba. A fülön keresztül Asztali eszközök Megadom neki a nevet tbl_ eladások nak nek. Ennek a megközelítésnek két előnye van:

A képletek megírása könnyebbé válik, mert nem kell rejtjeles cellahivatkozásokkal dolgoznom.
Ezenkívül a táblázatomat automatikusan módosítják az adatrekordok beírásakor és törlésénél, és az értékelésem megbízhatóan hozzáfér az aktuális adatbázishoz.

Kivonat a szűrendő adatbázisból

Csak a TerraMeda vevő rekordjait szeretném megjeleníteni. Így csinálom:

Átállok a munkalapra értékelés és írja be az ügyfél nevét az E3 cellába, tehát ide TerraMeda.
Az adatbázis 4. sorában a B oszlop oszlopfejlécét másolom G-be.
Ezután a kiválasztott ügyfél adatrekordjait fel kell venni az Értékelő lapra a 6. sorból. Tehát megjelölöm a B6 cellát, és ezzel kezdem a képletemet = SZŰRŐ (
Ennek a függvénynek három argumentuma van mátrix, bezárni és if_blank.
Az első érvre mátrix vigye az egeret a táblázat bal felső sarkába az alábbiak szerint. Az egér ferde fekete nyíllá változik. Egy kattintás elegendő, és minden adatrekord meg van jelölve.

Egyszerűen jelölje meg az adatbázist a ferde nyíllal

A pontosvessző beírása után következik a második érv: Az Excelnek ellenőriznie kell, hogy az ügyfél neve megjelenik-e az ügyfelek oszlopának E3 cellájában. A keresendő ügyfél oszlopot úgy jelölöm meg, hogy egyszerűen az adatbázisban a szó fölé kattintok vevő kattintson (az egér most függőleges fekete nyíl). Ezután beírok egy egyenlőségjelet, és rákattintok az E3 összehasonlító cellára.
A képlet eddig = SZŰRŐ (tbl_Umsatz; tbl_Umsatz [ügyfél] = értékelés! E3
Nincs szükségem a harmadik érvre itt. Tehát zárójelben bezárom a képletet, és belépek.
Az eredmény most majdnem úgy néz ki, mint az 1. ábrán. Csak az oszlop számformátumai dátum és háló még ki kell igazítani. Ezt a Szám csoport Kezdőlap fülén végezem, az előre definiált formátumok listájával. Ott választom Dátum, rövid mint például valuta.

A kész SZŰRŐ képlet

Így válik világosabbá: Rendezze a szűrt eredményeket

Gyakran nagyon hasznos, ha a szűrt eredményeket bizonyos sorrendben jeleníti meg, például tétel, régió vagy értékesítés szerint rendezve.
Az új tömbfunkcióknak köszönhetően ezt csak néhány kattintással lehet elvégezni. Ehhez az imént létrehozott SZŰRŐ funkciót beépítem a SORT funkcióba az alábbiak szerint.

A kurzort a képletsávba a SZŰRŐ szó elé tettem.
azt hiszem Sajnálom a. Az Excel a SORT funkciót javasolja. Megnyomásával elfogadom ezt a javaslatot fülre-gomb.
Helyezem a kurzort a képlet végére, és ott pontosvesszőt írok be.
Most meg kell érvelnem Rendezés index adja meg annak az oszlopnak a számát, amely szerint a szűrt listát rendezni kell. Értékesítés szerint szeretnék rendezni, ezért 6-at tettem, mert a mátrix 6. oszlopa az értékesítés.
Újabb pontosvessző után eldől, hogy növekvő vagy csökkenő sorrendben rendezi-e. Szeretném, ha a legmagasabb eladások lennének a tetején, ezért válassza a -1 lehetőséget.

Csökkenő sorrendben -1 választ

Fogjon le egy lehetséges hibaüzenetet az IFERROR segítségével

A szűrt és rendezett adatelemzésemnek még mindig van egy kis hibája. Ha az értékelő lap E3 cellája üres, azaz ha nincs megadva szűrési feltétel, akkor az új hibaüzenet a B6 cellában jelenik meg #MÉSZ!.

Az új #KALK! Hibaüzenet a B6 cellában

Ezt elkerülöm azzal, hogy ...

helyezze a kurzort a SORT szó elé a képletsávban,
WENNF és írja be a függvényt az Excel javaslatainak listájából IFROROR a gomb megnyomásával fülre-Alkalmaz gombra,
helyezze a kurzort a képlet végére, és írjon be pontosvesszőt és két idézőjelet,
majd zárja be a képletet zárójelben és írja be.

A kész formula így néz ki:

Elfogja a hibaüzeneteket kifejezetten az IFERROR funkcióval

kilátások

Elegánsabb lenne, ha az E3 cellában a legördülő listán keresztül kényelmesen kiválaszthatnám a szűrőkritériumot. A következő bejegyzésben leírom, hogyan tudom ezt megtenni az adatok ellenőrzésével és az új UNIQUE funkció használatával.

És ha az új funkciók (még) nem állnak rendelkezésre?

Az új dinamikus tömb funkciók csak az Excel 365-ben érhetők el.
Jelenleg csak akkor érhetők el, ha a frissítési csatorna beállítása „Insider”, „Havonta célzott” vagy „Havonta”.
Mindenkinek, akinek frissítési csatornája van »évente kétszer«, türelmesnek kell lennie.
A beállított frissítési csatornát a parancssorozattal lehet meghatározni fájl >számla kitalál.

Az alábbi ábra a »Havi« frissítési csatornához tartozó információkat mutatja. Itt a verziószám a meghatározó 1911 (azaz 2019. november).

A frissítési csatornán és a sárgával jelölt verzión szereplő információk meghatározóak