Összegző táblázat az értékek szövegével
Mindenki boldog a pivot táblákkal - gyorsnak tekinthetők, rugalmasan állíthatók és a formatervezés elegáns, ha szükséges. De van néhány kanál kátrány, különösen, hogy képtelenek létrehozni egy összetett, ahol az értéktartományban nem lehet szöveg, hanem számokat.
Próbáljuk megkerülni ezt a korlátozást és hasonló helyzetben "pár mancsokkal" jönni.
Tegyük fel, hogy cégünk többféle városba szállítja termékeit Oroszországban és Kazahsztánban. A konténereket legfeljebb havonta egyszer küldik meg. Minden tárolónak alfanumerikus száma van. Forrásadatokként van egy szabványos tábla, amely tartalmazza a szállítmányok listáját, amelyből valamilyen összegzést kell készíteni ahhoz, hogy vizuálisan láthassa az egyes városhoz és minden hónaphoz küldött konténerek számát:
A kényelem érdekében készítsünk előzetesen "intelligens" adatokat tartalmazó táblázatot az "Otthon - formátum mint tábla" parancs segítségével, és adja meg a megjelenítés nevét a Tervezés lapon. A jövőben ez egyszerűsíti az életet, mert A táblázat neve és oszlopai közvetlenül használhatók a képletekben.
1. módszer: A legegyszerűbb - a Power Query-t használjuk
Ha nem használhatja az Energiagazdálkodási lekérdezést, akkor más módokon is meg lehet menni - összefoglaló táblázaton vagy képleteken keresztül.
2. módszer: Segéd összefoglaló
Adja hozzá a forrás táblázatunkhoz egy oszlopot, ahol egyszerű képlet használatával számolja ki a táblázatban szereplő sorok számát:
Nyilvánvaló, hogy -1-re van szükség, mert egy soros fejléc van a táblázatban. Ha a táblázata nem a munkalap elején fekszik, használhat egy kissé összetettebb, de univerzális képletet, amely kiszámítja az aktuális vonal és az asztalfejlécek számának különbségét:
Most a kívánt formanyomtatványt összefoglaló táblázatot állítjuk össze az adataik alapján, de az értékek mezőjében a kívánt konténer helyett a mező sorszámát töltjük ki.
Mivel ugyanabban a hónapban ugyanabban a városban nincs több konténerünk, összevontan, valójában nem a konténerek összegét, hanem sorszámát szeretnénk.
Ezen felül, akkor letilthatja a megosztott és részösszegek a Tervezés lap - Általános összegek és részösszegek (Design - Grand összesítések, Részösszegek), és ott váltani egy sokkal kényelmesebb összefoglaló táblázat elrendezés gombra Report Layout (Elrendezés Report).
Így már félúton vagyunk az eredményhez: van egy asztalunk, ahol a város és a hónap kereszteződésében van egy sorszám a forrás táblában, ahol a tartály kódja van.
Most másolja Összegzés (ugyanazon a lapon, vagy egy másik), és illessze be, mint értékek, és azután vezetik be az a képlet az értékek, amelyek kivonat a konténer kódot a sor számát talált összefoglaló:
IF funkciót. ebben az esetben ellenőrzi, hogy az összefoglaló következő cellája nem üres. Ha üres, akkor üres szöveges karaktert adunk ki, azaz " hagyja üresen a cellát. Ha nem üres, akkor az INDEX oszlop sorszámával kivonjuk a cella tartalmát az Eredeti kiszállítási táblázat Container (Tartalma) segítségével.
Talán az egyetlen nem túl nyilvánvaló pillanat itt a tartályban szereplő konténer. Az ilyen furcsa írásmód:
A jövőben, amikor az adatokat az eredeti Táblázat táblázatban módosítja. ne felejtse el frissíteni segéd összefoglaló vonalszámokkal a jobb egérgombbal rákattintva, és válassza a Frissítés lehetőséget.
3. módszer: A képletek
Ez a módszer nem igényel közbenső összefoglaló táblát és manuális frissítést, hanem a "nehéz Excel fegyvert" használja - a SUMIFS funkciót. Ahelyett, hogy az összefoglalóban sorszámokat keresne, kiszámíthatja ezeket a képlet segítségével:
Valójában külső konzervativitással ez a standard változat a SUMMARY szelektív összegzésének funkcióját használja. amely összefoglalja az adott város és hónap sorszámát. Ismét, mivel ugyanabban a hónapban ugyanabban a városban nincs több tárolóhelyünk, a funkciónk ténylegesen nem adja ki az összeget, hanem a vonal számát. És akkor az előző módszerrel már ismerős INDEX funkció kitörölheti és tárolhatja a kódokat:
Önmagában ebben az esetben már nem kell gondolni az összefoglaló frissítésére, de a nagy táblákon a SUMMER funkció jelentősen lassulhat. Ezután le kell tiltania a képletek automatikus frissítését vagy az első módszert - összefoglaló táblát.
Ha a megjelenése az összefoglaló nem nagyon alkalmas a rekordot, akkor lehet, hogy húzza ki belőle a sorszámokat a döntő asztalra nem közvetlenül, mint tettük, és a GETPIVOTDATA funkció (GET.PIVOT.DATA). Hogyan kell ezt csinálni?