valószínűleg találkozni fog az összegek futtatásának szükségességével, ha bármilyen napi adattal foglalkozik.

képzelje el, hogy minden nap nyomon követi az értékesítést. Az adatok minden dátumhoz tartalmaznak egy sort a teljes értékesítési összeggel, de talán meg szeretné tudni a hónap összes értékesítését minden nap. Ez egy futó teljes összeg, ez az összes értékesítés összege, beleértve az aktuális napi értékesítéseket is.

ebben a bejegyzésben többféle módszert fogunk lefedni a napi adatok futó összegének kiszámításához. Megvizsgáljuk, hogyan használhatjuk a munkalap-képleteket, a pivot táblákat, a power pivot-ot a DAX és a power query segítségével.

azt is megvizsgáljuk, hogy mi történik a futó teljes számítással adatsorok beillesztésekor vagy törlésekor, valamint hogyan frissíthetjük az eredményeket.

Szerezd meg a fájlt az összes példával.

összesítések futtatása egyszerű képlettel

a + operátorral alapszintű teljes képletet lehet létrehozni.

a munka elvégzéséhez azonban két különböző képletet kell használnunk.

1. =C3 lesz az első képlet, és csak a futó összeg első sorában lesz.
2. =C4+D3 lesz a második sorban, és lehet másolni le a fennmaradó sorok a futó összesen.

az első sorunkban lévő képlet nem adhatja hozzá a fölötte lévő cellát az összeghez, mivel az oszlopfejléc szöveges értékét tartalmazza. Ez #értéket okozna! hiba jelenik meg a futó összegben, mivel a + nem tudja kezelni a szöveges értékeket. Ezt az első sorban egy másik képlettel kerüljük el, amely nem hivatkozik a fenti cellára.

mi történik a futó összeggel, amikor sorokat helyezünk be vagy törölünk az adatainkba?

új sor beszúrása hézagot eredményez a futó összegben. Ennek kijavításához le kell másolnunk a képletet az újonnan beillesztett sorok feletti első cellától egészen az utolsó sorig.

a sorok törlése #REF! hibák, mivel egy sor törlése azt jelenti, hogy törli az alatta lévő képlet által hivatkozott cellát. Ennek kijavításához le kell másolnunk a képletet az utolsó hibamentes celláról egészen az utolsó sorig.

összesítések futtatása ÖSSZEGKÉPLETTEL

elkerülhetjük a két különböző képlet használatának kínosságát a futó teljes oszlopban, ha a SUM függvényt használjuk a + operátor helyett. Amikor a SUM függvény találkozik egy szöveges cellával, ugyanúgy kezeli, mint a, bár tartalmazott egy 0-t.

így a következő képletet egységesen használhatjuk minden sorra, beleértve az első sort is.

= SUM (C3,D2)

Ez a képlet az első sor szövegét tartalmazó oszlopfejlécre hivatkozik, de ez rendben van, mivel 0-ként kezelik.

sorok beillesztésekor vagy törlésekor továbbra is ugyanazokkal a problémákkal találkozunk az üres cellákkal és a hibákkal. Ugyanúgy rögzíthetjük őket, mint az egyszerű képlet módszerrel futó összegek esetén.

részlegesen rögzített tartományú összegek futtatása

A SUM függvény egy másik opciója, hogy csak az értékesítési oszlopra hivatkozik, és részben rögzített tartomány hivatkozást használ.

Ha a következő képletet használjuk =SUM($C$3:C3), akkor ezt lemásolhatjuk és beilleszthetjük a tartományba. Nem fog hivatkozni egyetlen oszlopfejlécre sem, és a hivatkozott tartomány minden sorra növekedni fog.

sajnos ennek is ugyanazok a problémái (és megoldásai) lesznek a sorok beszúrásával vagy törlésével.

összesítések futtatása egy relatív nevű Tartománylal

elkerülhetjük a sorok beszúrásával és törlésével kapcsolatos problémákat az adatainkból, ha relatív nevű tartományt használunk. Ez a közvetlenül a fenti cellára utal, függetlenül attól, hogy hány sort szúrunk be vagy törölünk.

Ez egy trükk, amely magában foglalja az Excel referencia stílus ideiglenes váltását A1-ről R1C1-re. Ezután definiáljon egy megnevezett tartományt az R1C1 jelöléssel. Ezután kapcsolja vissza a referencia stílust az A1-re.

az R1C1 referenciastílusban a cellákra a hivatkozás segítségével hivatkozunk, hogy milyen messze vannak a cellától. Például az =RC a cellától jobbra lévő 2-es és 3-as cellára utal ezzel a képlettel.

ezzel a relatív hivatkozással létrehozhatunk egy megnevezett tartományt, amely mindig egy cellával van a hivatkozó cella felett az =RC képlettel.

a referencia stílus váltásához lépjen a Fájl fülre, majd válassza az Opciók lehetőséget. LÉPJEN az Excel beállítások menü képlet szakaszába, jelölje be az R1C1 referencia stílus négyzetet, majd nyomja meg az OK gombot.

most hozzáadhatjuk a megnevezett tartományunkat. LÉPJEN az Excel szalag képlet lapjára, és válassza a név megadása parancsot.

helyezzen be egy “fent” nevet a tartomány neveként. Adja hozzá a =RC képletet a refer to bemenethez, majd nyomja meg az OK gombot.

most már vissza tudjuk váltani az Excel-t az alapértelmezett hivatkozási stílusra. Lépjen a Fájl fülre > opciók a képlet szakasz > törölje az r1c1 referencia stílus négyzet jelölését > Ezután nyomja meg az OK gombot.

most már tudjuk használni a képletet =SUM(,fent) a futó teljes oszlopban.

a fent megnevezett tartomány mindig a közvetlenül a fenti cellára utal. Sorok beszúrásakor vagy törlésekor a relatív megnevezett tartomány ennek megfelelően módosul, és nincs szükség műveletre.

valójában, ha adatainkat egy Excel táblázatba helyezzük, akkor a képlet automatikusan kitölti az új sorokat, mivel a képlet az egész oszlopra egységes. A képletek lemásolásához nincs szükség műveletre.

összesítések futtatása egy Pivot táblával

a Pivot táblák rendkívül hasznosak bármilyen típusú adat összefoglalásához. Több van bennük, mint az átlagok összeadása, számlálása és megtalálása. Sok más típusú számítások beépített, és van valójában egy futó teljes számítás!

először be kell illesztenünk egy pivot táblát az adatok alapján. Jelöljön ki egy cellát az adatokon belül, lépjen a Beszúrás fülre, és válassza a kimutatás parancsot. Ezután menjen át a kimutatás létrehozása ablakban válassza ki, hogy hol szeretné a kimutatást, akár egy új munkalapon, akár valahol egy meglévőben.

adja hozzá a dátum mezőt a kimutatás tábla sorok területéhez, majd adja hozzá az értékesítés mezőt a kimutatás tábla értékek területéhez. Most adjon hozzá egy másik példányt az értékesítés mezőből a sorok területhez.

most két azonos értékesítési mezővel kell rendelkeznünk, amelyek közül az egyik az értékesítés összege címkével rendelkezik2. Bármikor átnevezhetjük ezt a címkét, egyszerűen beírva azt valami olyasmivel, mint a Total futtatása.

kattintson a jobb gombbal a Sales2 összege mező bármelyik értékére, majd válassza az érték megjelenítése mint lehetőséget, majd válassza a teljes futás lehetőséget.

a futó összeget dátum szerint szeretnénk megjeleníteni, ezért a következő ablakban ki kell választanunk a dátumot Alapmezőként.

Ez az, most már van egy új számítás, amely megjeleníti a futó teljes értékesítési belül pivot tábla.

mi történik, ha hozzáadunk vagy törölünk egy sort a forrásadatainkból, hogyan befolyásolja ez a futó összeget? A pivot tábla számításai dinamikusak, és minden új adatot figyelembe vesznek a futó teljes számítás során, csak frissítenünk kell a pivot táblát.

kattintson a jobb gombbal a pivot tábla bármely pontjára, majd válassza a Frissítés menüpontot.

összesítések futtatása Power Pivot és DAX mérésekkel

ennek első pár lépése pontosan ugyanaz, mint egy normál pivot tábla használata esetén.

Jelöljön ki egy cellát az adatokon belül, majd lépjen a Beszúrás fülre, és válassza a kimutatás parancsot.

amikor a kimutatás létrehozása menübe lép, jelölje be az Adatok hozzáadása az adatmodellhez négyzetet, hogy hozzáadja az adatokat az adatmodellhez, és engedélyezze a power pivot használatához.

helyezze a dátum mezőt a sorok területre, az értékesítés mezőt pedig a kimutatástábla értékek területére.

a power pivot segítségével bármilyen további számítást létre kell hoznunk a DAX nyelv használatával. Kattintson a jobb gombbal a táblázat nevére a Kimutatásmezők ablakban, majd válassza az intézkedés hozzáadása lehetőséget egy új számítás létrehozásához. Megjegyzés: Ez csak az adatmodellel érhető el.

=CALCULATE ( SUM ( Sales ), FILTER ( ALL (Sales ), Sales 

most létrehozhatjuk az új futó teljes mértéket.

1. az intézkedés ablakban hozzá kell adnunk egy intézkedés nevét. Ebben az esetben megnevezhetjük az új mértéket futó összesen.
2. a fenti képletet hozzá kell adnunk a Képlet mezőbe.
3. a power pivot nagyszerű tulajdonsága, hogy számformátumot rendelhet egy méréshez. Kiválaszthatjuk az intézkedés pénznemének formátumát. Amikor ezt az intézkedést egy pivot táblában használjuk, a formátum automatikusan alkalmazásra kerül.

nyomja meg az OK gombot, és az új mérés létrejön.

a Kimutatásmezők ablakban megjelenik egy új mező. A bal oldalon egy kis fx ikon jelzi, hogy ez egy mérték, nem pedig egy szabályos Mező az adatokban.

ezt az új mezőt ugyanúgy használhatjuk, mint bármely más mezőt, és az értékek területre húzva hozzáadhatjuk a futó teljes számításunkat a pivot táblához.

mi történik a futó összeggel, amikor adatokat adunk hozzá vagy távolítunk el a forrástáblából? Csakúgy, mint egy szokásos pivot tábla, egyszerűen csak jobb egérgombbal kell kattintanunk a pivot táblára, és a Frissítés lehetőséget választjuk a számítás frissítéséhez.

futó összegek Power lekérdezéssel

a power query használatával futó összegeket is hozzáadhatunk adatainkhoz.

először importálnunk kell a táblázatot a power query-be. Válassza ki az adattáblát, LÉPJEN az adatok fülre, és válassza a táblázat/tartomány lehetőséget. Ez megnyitja a power query szerkesztőt.

ezután az adatokat dátum szerint rendezhetjük. Ez egy opcionális lépés, amelyet hozzáadhatunk, így ha megváltoztatjuk a forrásadatok sorrendjét, a futó összeg továbbra is dátum szerint jelenik meg.

kattintson a szűrő kapcsolóra a dátum oszlop fejlécében, majd válassza a rendezés növekvő lehetőséget a lehetőségek közül.

hozzá kell adnunk egy index oszlopot. Ezt később a futó teljes számítás során használják. LÉPJEN az Oszlop hozzáadása fülre, majd kattintson az Index oszlop melletti kis nyílra az első sor 1-től kezdődő index beszúrásához.

új oszlopot kell hozzáadnunk a lekérdezéshez a futó összeg kiszámításához. LÉPJEN az Oszlop hozzáadása fülre, és válassza az Egyéni oszlop parancsot.

megnevezhetjük az oszlopot futó összegként, és hozzáadhatjuk a következő képletet.

a lista.Range függvény létrehoz egy listát az értékek az értékesítés oszlop kezdve az 1.sor (0. tétel), amely átfogja a Sorok száma értéke alapján az index oszlopban.

a lista.Összeg függvény ezután összeadja ezt az értékek listáját, amely a futó teljes.

már nincs szükségünk az index oszlopra, elérte a célját, és eltávolíthatjuk. Kattintson a jobb gombbal az oszlop fejlécére, majd válassza az Eltávolítás lehetőséget a lehetőségek közül.

megvan a futó teljes, és kész a lekérdezés szerkesztő. Bezárhatjuk a lekérdezést, és betölthetjük az eredményeket egy új munkalapra. Lépjen a Lekérdezésszerkesztő Kezdőlap lapjára, majd nyomja meg a Bezárás & Betöltés gombot.

mi történik a futó összeggel, amikor sorokat adunk hozzá vagy távolítunk el a forrásadatokból? Frissítenünk kell a power query kimeneti táblázatot, hogy frissítsük a futó összeget a változásokkal. Kattintson a jobb gombbal az asztal bármely pontjára, és válassza a Frissítés lehetőséget a táblázat frissítéséhez.

a fenti opcionális rendezési lépéssel, ha a dátumokat nem rendezzük a forrásadatokhoz, a power query dátum szerint rendezi, és a futó összeghez a helyes sorrendet adja vissza.

következtetések

számos különböző lehetőség van a futó összegek kiszámítására az Excelben.

megvizsgáltuk a lehetőségeket, beleértve a munkalap képleteit, a pivot táblákat, a power pivot DAX képleteket és a power query-t. Egyesek robusztusabb megoldást kínálnak sorok hozzáadásakor vagy eltávolításakor az adatokból, más módszerek könnyebb megvalósítást kínálnak.

A munkalap egyszerű képletei könnyen beállíthatók, de nem kezelik könnyen az új adatsorok beillesztését vagy törlését. Más megoldások, mint a pivot táblák, a DAX és a power query robusztusabbak, és könnyen kezelik az adatsorok beszúrását vagy törlését, de nehezebb beállítani.

jó, ha tisztában vagyunk az egyes módszerek előnyeivel és hátrányaival, és kiválasztjuk a legmegfelelőbbet. Ha nem fog új adatokat beszúrni vagy törölni, akkor a munkalap-képletek lehetnek az út.