1. módszer. Képletek
Kezdjük a bemelegítésnél a legegyszerűbb lehetőséggel – a képletekkel. Ha van egy kis táblázatunk dátum szerint rendezve bemenetként, akkor a futó végösszeg külön oszlopban történő kiszámításához szükségünk van egy elemi képletre:
A fő jellemző itt a tartomány trükkös rögzítése a SUM függvényen belül – a tartomány elejére való hivatkozás abszolút (dollárjelekkel), a végére pedig relatív (dollár nélkül) történik. Ennek megfelelően a képletet a teljes oszlopra lemásolva egy bővülő tartományt kapunk, aminek az összegét számítjuk ki.
Ennek a megközelítésnek a hátrányai nyilvánvalóak:
- A táblázatot dátum szerint kell rendezni.
- Új adatsorok hozzáadásakor a képletet manuálisan kell bővíteni.
2. módszer. Pivot tábla
Ez a módszer egy kicsit bonyolultabb, de sokkal kellemesebb. És hogy ezt tetézzük, vegyünk egy komolyabb problémát – egy 2000 soros adattáblázatot, ahol nincs dátum oszlop szerinti rendezés, viszont vannak ismétlések (azaz ugyanazon a napon többször is eladhatunk):
Eredeti táblázatunkat „intelligens” (dinamikus) billentyűkombinációvá alakítjuk Ctrl+T vagy csapat Kezdőlap – Táblázat formázása (Főoldal — Táblázat formázása), majd a paranccsal pivot táblát építünk rá Beszúrás – PivotTable (Beszúrás – Pivot Table). Az összesítésben a sorok mezőbe a dátumot, az értékek mezőbe az eladott áruk számát tesszük:
Kérjük, vegye figyelembe, hogy ha az Excel nem egészen régi verziója van, akkor a dátumok automatikusan évek, negyedévek és hónapok szerint vannak csoportosítva. Ha más csoportosításra van szüksége (vagy egyáltalán nincs rá szüksége), akkor a jobb gombbal bármelyik dátumra kattintva és a parancsok kiválasztásával javíthatja Csoportosítás / Csoportbontás megszüntetése (Csoport / Csoportbontás).
Ha külön oszlopban szeretné látni az eredményül kapott összegeket periódusonként és a futó összeget is, akkor érdemes a mezőt az értékterületbe dobni. Eladott ismét, hogy a mező másolatát kapjuk – ebben bekapcsoljuk a futó összegek megjelenítését. Ehhez kattintson a jobb gombbal a mezőre, és válassza ki a parancsot További számítások – Összesített összeg (Értékek megjelenítése mint – futó összesítés):
Ott kiválasztható az összegek százalékos növelése is, a következő ablakban pedig ki kell választani azt a mezőt, amelyre a felhalmozás megy – esetünkben ez a dátum mező:
Ennek a megközelítésnek az előnyei:
- Nagy mennyiségű adat gyorsan kiolvasható.
- Nem kell képleteket manuálisan megadni.
- A forrásadatok megváltoztatásakor elegendő az összefoglalót a jobb egérgombbal vagy az Adatok – Összes frissítése paranccsal frissíteni.
A hátrányok abból fakadnak, hogy ez egy összefoglaló, ami azt jelenti, hogy nem lehet azt csinálni benne, amit akarsz (sorokat beszúrni, képleteket írni, diagramokat építeni stb.), nem fog működni.
3. módszer: Power Query
A parancs segítségével töltsük be a forrásadatokat tartalmazó „okos” táblázatunkat a Power Query lekérdezésszerkesztőbe Adatok – Táblázatból/Tartományból (Adatok – táblázatból/tartományból). Az Excel legújabb verzióiban egyébként átnevezték – most úgy hívják Levélekkel (A lapról):
Ezután a következő lépéseket hajtjuk végre:
1. A paranccsal rendezze a táblázatot növekvő sorrendbe a dátum oszlop szerint Rendezés növekvő sorrendben a táblázat fejlécében található szűrő legördülő listában.
2. Kicsit később a futó összeg kiszámításához szükségünk van egy segédoszlopra a sorszámmal. Adjuk hozzá a paranccsal Oszlop hozzáadása – Index oszlop – 1-tól (Oszlop hozzáadása — Index oszlop — 1-tól).
3. Ezenkívül a futó összeg kiszámításához szükségünk van az oszlopra való hivatkozásra Eladott, ahol az összesített adataink rejlenek. A Power Queryben az oszlopokat listáknak (listának) is nevezik, és a rájuk mutató hivatkozás eléréséhez kattintson jobb gombbal az oszlop fejlécére, és válassza ki a parancsot. részletezve (Részletek mutatása). A szükséges kifejezés megjelenik a képletsorban, amely az előző lépés nevéből áll #"Index hozzáadva", ahonnan a táblázatot és az oszlop nevét vesszük [értékesítés] ebből a táblázatból szögletes zárójelben:
Másolja ezt a kifejezést a vágólapra további felhasználás céljából.
4. Törölje a felesleges további utolsó lépést Eladott és helyette adjunk hozzá egy számított oszlopot a futó összeg kiszámításához a paranccsal Oszlop hozzáadása – Egyéni oszlop (Oszlop hozzáadása – Egyéni oszlop). A szükséges képlet így fog kinézni:
Itt a funkció List.Tartomány átveszi az eredeti listát (oszlop [Értékesítés]), és kivonja belőle az elemeket, az elsőtől kezdve (a képletben ez 0, mivel a számozás a Power Queryben nulláról indul). A visszakeresendő elemek száma az oszlopból vett sorszám [Index]. Tehát ez az első sor függvénye csak az oszlop egy első celláját adja vissza Eladott. A második sorhoz – már az első két cella, a harmadikhoz – az első három stb.
Nos, akkor a függvény List.Sum összeadja a kinyert értékeket, és minden sorban megkapjuk az összes előző elem összegét, azaz az összesített összeget:
Marad az Index oszlop törlése, amelyre már nincs szükségünk, és a Home – Close & Load to paranccsal töltsük vissza az eredményeket az Excelbe.
A probléma megoldódott.
Gyors és dühös
Ezt elvileg meg lehetett volna állítani, de van egy kis légy a kenőcsben – teknős sebességgel működik az általunk készített kérés. Például az én nem a leggyengébb számítógépemen egy mindössze 2000 soros táblázatot 17 másodperc alatt dolgoznak fel. Mi van, ha több adat van?
A gyorsításhoz használhatjuk a speciális List.Buffer függvényt használó pufferelést, amely a neki argumentumként megadott listát (listát) betölti a RAM-ba, ami a jövőben nagyban felgyorsítja az elérést. Esetünkben célszerű pufferelni a #"Hozzáadott index"[Eladott] listát, amelyhez a Power Querynek hozzá kell férnie, amikor a 2000 soros táblázatunk minden sorában kiszámolja a futó végösszeget.
Ehhez a Fő lapon található Power Query szerkesztőben kattintson a Speciális szerkesztő gombra (Főoldal – Speciális szerkesztő), hogy megnyissa a lekérdezésünk forráskódját a Power Querybe épített M nyelven:
Aztán adjunk hozzá egy sort változóval A listám, melynek értékét a pufferelési függvény adja vissza, és a következő lépésben a listahívást ezzel a változóval helyettesítjük:
A változtatások elvégzése után a lekérdezésünk lényegesen gyorsabb lesz, és 2000 másodperc alatt megbirkózik egy 0.3 soros táblázattal!
Egy másik dolog, igaz? 🙂
- Pareto diagram (80/20) és hogyan lehet elkészíteni Excelben
- Kulcsszókeresés szövegben és lekérdezéspufferelés a Power Queryben