tartalom
A pivot táblák mindenkinek jól jönnek – gyorsan számolnak, rugalmasan konfigurálhatók, igény szerint elegánsan tekerhető beléjük a dizájn. De van néhány légy is, különösen az, hogy képtelenség összefoglalót készíteni, ahol az értékterület ne számokat, hanem szöveget tartalmazzon.
Próbáljuk meg kikerülni ezt a korlátot, és találjunk ki egy „pár mankót” hasonló helyzetben.
Tegyük fel, hogy cégünk konténerekben szállítja termékeit hazánk és Kazahsztán több városába. A konténereket havonta legfeljebb egyszer küldik ki. Minden tárolónak van egy alfanumerikus száma. Kiinduló adatként van egy szabványos kiszállítást listázó táblázat, amiből valamilyen összegzést kell készíteni, hogy jól látható legyen az egyes városokba és havonta kiküldött konténerek száma:
A kényelem kedvéért a paranccsal előre „okossá” tegyük a táblázatot a kiindulási adatokkal Kezdőlap – Táblázat formázása (Főoldal — Táblázat formázása) és adj neki nevet A szállítások lap építész (Tervezés). A jövőben ez leegyszerűsíti az életet, mert. a képletekben közvetlenül lehet majd használni a táblázat nevét és oszlopait.
1. módszer. A legegyszerűbb – használja a Power Queryt
A Power Query egy rendkívül hatékony eszköz az adatok Excelben való betöltésére és átalakítására. Ez a bővítmény 2016 óta alapértelmezés szerint be van építve az Excelbe. Ha Excel 2010-es vagy 2013-as verziója van, külön letöltheti és telepítheti (teljesen ingyenes).
Az egész folyamatot az egyértelműség kedvéért lépésről lépésre elemeztem a következő videóban:
Ha nem lehetséges a Power Query használata, akkor más módon is járhat – pivot táblán vagy képleteken keresztül.
2. módszer. Segédösszefoglaló
Adjunk hozzá még egy oszlopot az eredeti táblázatunkhoz, ahol egy egyszerű képlet segítségével kiszámítjuk a táblázat egyes sorainak számát:
Nyilvánvalóan a -1 kell, mert van egy egysoros fejléc a táblázatunkban. Ha a táblázat nem a lap elején található, akkor használhat egy kicsit összetettebb, de univerzális képletet, amely kiszámítja az aktuális sor és a táblázat fejlécének számainak különbségét:
Most szabványos módon elkészítjük adataink alapján a kívánt típusú pivot táblát, de az érték mezőben eldobjuk a mezőt Sorszám ahelyett, amit akarunk konténer:
Mivel ugyanabban a hónapban nincs több konténerünk ugyanabban a városban, így az összesítésünkben valójában nem a mennyiséget, hanem a szükséges konténerek sorszámát adjuk meg.
Ezenkívül kikapcsolhatja a vég- és részösszegeket a lapon Konstruktor – Általános összegek и részösszegek (Tervezés – végösszeg, részösszeg) és ugyanitt a gombbal kapcsolja át az összefoglalót egy kényelmesebb táblázatelrendezésre Jelentés makett (Jelentés elrendezése).
Így már félúton vagyunk az eredményhez: van egy táblánk, ahol a város és a hónap metszéspontjában a forrástáblázatban van egy sorszám, ahol a szükséges konténerkód található.
Most másoljuk át az összesítést (ugyanarra a lapra vagy másikra) és illesszük be értékekként, majd írjuk be a képletünket az értékterületre, amely az összesítésben található sorszámmal kivonja a konténer kódját:
Funkció IF (HA), ebben az esetben ellenőrzi, hogy az összegzés következő cellája nem üres-e. Ha üres, akkor adjon ki egy üres karakterláncot, azaz hagyja üresen a cellát. Ha nem üres, akkor vegye ki az oszlopból Konténer forrástábla A szállítások cellatartalom sorszám szerint függvény segítségével INDEX (INDEX).
Talán az egyetlen nem túl nyilvánvaló pont itt a kettős szó Konténer a képletben. Egy ilyen furcsa írásforma:
Kellékek[[Tartály]:[Tartály]]
… csak az oszlopra való hivatkozáshoz szükséges Konténer abszolút volt (mint egy referencia $ jelekkel a közönséges „nem intelligens” táblákhoz), és nem csúszott át a szomszédos oszlopokba, amikor képletünket jobbra másolta.
A jövőben a forrástábla adatainak módosításakor A szállítások, ne felejtsük el frissíteni a segédösszefoglalónkat sorszámokkal úgy, hogy jobb gombbal kattintunk rá, és kiválasztjuk a parancsot Frissítés és mentés (Frissítés).
3. módszer. Képletek
Ez a módszer nem igényel köztes pivot tábla létrehozását és manuális frissítését, hanem az Excel „nehézfegyverét” – a funkciót használja. SUMMESLIMN (SUMIFS). A sorszámok összegzésben történő keresése helyett a következő képlettel számíthatja ki őket:
Valójában némi külső terjedelműséggel ez a szelektív összegző funkció szokásos használati esete SUMMESLIMNA, amely az adott város és hónap sorszámait összegzi. Ismétlem, mivel ugyanabban a hónapban nincs több konténerünk ugyanabban a városban, a funkciónk valójában nem a mennyiséget, hanem magát a sorszámot adja ki. És akkor az előző módszerből már ismerős függvény INDEX A konténerkódokat is kivonhatja:
Természetesen ebben az esetben már nem az összesítés frissítésére kell gondolni, hanem nagy táblákon a függvény SUMMESLI észrevehetően lassú lehet. Ezután ki kell kapcsolnia a képletek automatikus frissítését, vagy használnia kell az első módszert - egy pivot táblát.
Ha az összefoglaló megjelenése nem igazán megfelelő a jelentéshez, akkor a sorszámokat nem közvetlenül, mint mi tettük, kinyerheti belőle a zárótáblába, hanem a függvény segítségével. GET.PIVOT.TABLE.DATA (GET.PIVOT.ADATOK). Ennek módja itt található.
- Hogyan készítsünk jelentést pivot tábla segítségével
- Számítások beállítása pivot táblákban
- Szelektív számlálás SUMIFS, COUNTIFS stb.