Vízszintes oszlopszűrés Excelben

Ha nem vagy teljesen kezdő felhasználó, akkor már biztosan észrevette, hogy az Excelben mindennek 99%-a függőleges táblázatokkal működik, ahol a paraméterek vagy attribútumok (mezők) átmennek az oszlopokon, és az objektumokkal vagy eseményekkel kapcsolatos információk találhatók. a sorokban. Pivot táblázatok, részösszegek, képletek másolása dupla kattintással – minden kifejezetten erre az adatformátumra van szabva.

Azonban nincsenek kivételek nélküli szabályok, és meglehetősen rendszeres gyakorisággal kérdezik tőlem, hogy mit tegyek, ha egy vízszintes szemantikai orientációjú táblázat, vagy olyan táblázat, ahol a sorok és az oszlopok jelentésükben azonos súlyúak, találkoztak a műben:

És ha az Excel továbbra is tudja, hogyan kell vízszintesen rendezni (a paranccsal Adatok – Rendezés – Beállítások – Oszlopok rendezése), akkor a szűréssel rosszabb a helyzet – egyszerűen nincsenek beépített eszközök az oszlopok, nem pedig sorok szűrésére az Excelben. Tehát, ha ilyen feladattal szembesül, különböző bonyolultságú megoldásokat kell kidolgoznia.

1. módszer. Új SZŰRŐ funkció

Ha az Excel 2021 új verzióját vagy Excel 365-előfizetést használ, akkor kihasználhatja az újonnan bevezetett funkció előnyeit. FILTER (SZŰRŐ), amely nem csak sorok, hanem oszlopok szerint is képes szűrni a forrásadatokat. A funkció működéséhez szükség van egy segédvízszintes egydimenziós tömbsorra, ahol minden érték (IGAZ vagy HAMIS) meghatározza, hogy megjelenítsük-e, vagy fordítva, elrejtse a táblázat következő oszlopát.

Adjuk hozzá a következő sort a táblázatunk fölé, és írjuk bele az egyes oszlopok állapotát:

• Tegyük fel, hogy mindig az első és az utolsó oszlopot (fejléceket és összegeket) szeretnénk megjeleníteni, ezért ezekre a tömb első és utolsó cellájában az = TRUE értéket állítjuk be.
• A többi oszlop esetében a megfelelő cellák tartalma egy képlet lesz, amely függvények segítségével ellenőrzi a számunkra szükséges feltételt И (ÉS) or OR (OR). Például, hogy a végösszeg 300 és 500 között van.

Ezt követően már csak a funkció használata marad FILTER olyan oszlopok kiválasztásához, amelyek felett a segédtömbünk IGAZ értéket tartalmaz:

Hasonlóképpen szűrheti az oszlopokat egy adott lista alapján. Ebben az esetben a funkció segít COUNTIF (COUNTIF), amely ellenőrzi a következő oszlopnév előfordulások számát a tábla fejlécéből az engedélyezett listában:

2. módszer. Pivot tábla a szokásos helyett

Az Excelben jelenleg csak a pivot táblákban van beépített vízszintes oszloponkénti szűrés, így ha sikerül az eredeti táblánkat pivot táblává alakítanunk, akkor ezt a beépített funkciót használhatjuk. Ehhez a forrástáblázatunknak meg kell felelnie a következő feltételeknek:

• legyen „helyes” egysoros fejlécsor üres és egyesített cellák nélkül – különben nem fog működni a pivot tábla felépítése;
• ne tartalmazzon ismétlődéseket a sorok és oszlopok címkéiben – ezek „összecsukódnak” az összefoglalóban, és csak egyedi értékeket tartalmaznak;
• csak számokat tartalmaznak az értéktartományban (a sorok és oszlopok metszéspontjában), mert a pivot tábla minden bizonnyal alkalmaz rájuk valamilyen összesítő függvényt (összeg, átlag stb.), és ez nem fog működni a szöveggel

Ha mindezek a feltételek teljesülnek, akkor ahhoz, hogy az eredeti táblánkhoz hasonló pivot táblát készítsünk, azt (az eredetit) a kereszttáblából lapossá kell bővíteni (normalizálni). Ennek legegyszerűbb módja a Power Query bővítmény, egy 2016 óta az Excelbe beépített hatékony adatátalakító eszköz. 

Ezek a következők:

1. Alakítsuk át a táblázatot „intelligens” dinamikus paranccsá Kezdőlap – Táblázat formázása (Főoldal — Táblázat formázása).
2. Betöltés a Power Querybe a paranccsal Adatok – Táblázatból/Tartományból (Adatok – Táblázatból/Tartományból).
3. A sort szűrjük a végösszegekkel (az összesítésnek saját összege lesz).
4. Kattintson a jobb gombbal az első oszlop fejlécére, és válassza ki A többi oszlop összecsukásának feloldása (Más oszlopok forgatásának megszüntetése). Minden ki nem jelölt oszlopot kettővé alakítunk át – a munkavállaló nevére és mutatójának értékére.
5. Az oszlop szűrése az oszlopba került összegekkel Attribútum.
6. A kapott lapos (normalizált) tábla szerint pivot táblát építünk a paranccsal Kezdőlap — Bezárás és betöltés — Bezárás és betöltés… (Főoldal – Bezárás és betöltés – Bezárás és betöltés…).

Mostantól használhatja a pivot táblákban elérhető oszlopok szűrésének lehetőségét – ez a szokásos pipa a nevek és elemek előtt Aláírási szűrők (Címkeszűrők) or Érték szerint szűr (Értékszűrők):

És természetesen az adatok megváltoztatásakor frissítenie kell a lekérdezést és az összefoglalót egy billentyűkóddal Ctrl+más+F5 vagy csapat Adatok – Összes frissítése (Adatok – Összes frissítése).

3. módszer. Makró VBA-ban

Az összes korábbi metódus, mint jól látható, nem éppen szűrés – nem az eredeti listában rejtjük el az oszlopokat, hanem az eredetiből egy adott oszlopkészlettel új táblázatot alkotunk. Ha a forrásadatok oszlopait szűrni (elrejteni) kell, akkor alapvetően más megközelítésre van szükség, nevezetesen egy makróra.

Tegyük fel, hogy menet közben szeretnénk szűrni azokat az oszlopokat, ahol a tábla fejlécében szereplő menedzser neve megfelel az A4 sárga cellában megadott maszknak, például „A” betűvel kezdődik (vagyis kapjuk az „Anna” és „Arthur” kifejezést. ” ennek eredményeként). 

Az első módszerhez hasonlóan először egy segéd tartománysort valósítunk meg, ahol minden cellában egy képlet ellenőrzi a kritériumunkat, és a látható és a rejtett oszlopoknál a TRUE vagy FALSE logikai értékek jelennek meg:

Ezután adjunk hozzá egy egyszerű makrót. Kattintson a jobb gombbal a lapfülre, és válassza a parancsot forrás (Forráskód). Másolja ki és illessze be a következő VBA-kódot a megnyíló ablakba:

Private Sub Worksheet_Change(ByVal Target As Range) Ha Target.Address = "$A$4" Akkor minden egyes cellához a tartományban("D2:O2") Ha cella = igaz Akkor cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End If Next cell End If End Sub  

Logikája a következő:

• Általában ez egy eseménykezelő Worksheet_Change, azaz ez a makró automatikusan lefut az aktuális munkalap bármely cellájának módosítása esetén.
• A megváltozott cellára való hivatkozás mindig a változóban lesz cél.
• Először ellenőrizzük, hogy a felhasználó pontosan az (A4) feltétellel rendelkező cellát változtatta-e meg – ezt az operátor végzi if.
• Ezután kezdődik a ciklus Az egyes… a szürke cellákon (D2:O2) való iterációhoz IGAZ / HAMIS jelzőértékekkel minden oszlophoz.
• Ha a következő szürke cella értéke TRUE (igaz), akkor az oszlop nincs elrejtve, ellenkező esetben elrejtjük (tulajdonság Rejtett).

•  Dinamikus tömbfunkciók az Office 365-ből: SZŰRÉS, RENDEZÉS és UNIC
• Kimutatástábla többsoros fejléccel a Power Query használatával
• Mik azok a makrók, hogyan kell létrehozni és használni őket