Számítsa ki a futó összegeket (halmozott összegeket) az Excelben – 5 egyszerű módszer

futóösszeg (más névenhalmozott összeg) nagyon gyakran használják sok esetben.Ez egy mérőszám, amely megmondja, hogy mennyi az eddigi értékek összege.

Például, ha havi értékesítési adatai vannak, a futó végösszeg megmutatja, hogy hány értékesítés történt a hónap első napjától egy adott dátumig.

Vannak más helyzetek is, ahol gyakran használnak végösszegeket, például a banki kivonatokon/főkönyvekben szereplő készpénzegyenlegek kiszámítása, az étkezési tervekben szereplő kalóriaszámítás stb.

A Microsoft Excel programban számos különböző módszer létezik a futó összegek kiszámítására.

A választott módszer az adatok szerkezetétől is függ.

Például, ha egyszerű táblázatos adatokkal rendelkezik, használhat egy egyszerű SUM képletet, de ha van Excel táblája, akkor jobb, ha strukturált hivatkozásokat használ.Ehhez a Power Queryt is használhatja.

Ebben az oktatóanyagban mindegyikre kitérekSzámítsa ki a futó végösszeget Excelbenkülönböző módszerek.

Tehát kezdjük!

Számítsa ki a futó összegeket táblázatos adatok segítségével

Ha vannak táblázatos adatai (vagyis olyan Excel-táblázatok, amelyeket nem konvertált Excel-táblázatokká), akkor néhány egyszerű képlet segítségével kiszámíthatja a futó összegeket.

Használja az összeadás operátort

Tegyük fel, hogy dátum szerinti értékesítési adatai vannak, és szeretné在 a C oszlopbanszámításFutás összesen.

Itt vannak ennek lépései.

1. lépés – Írja be a C2 cellába azt a cellát, amelyre az összesítést le szeretné futtatni

= B2

Ez ugyanazt az értékesítési értéket kapja a B2 cellában.

2. lépés– A C3 cellába írja be a következő képletet:

= C2 + B3

3. lépés– Alkalmazza a képletet a teljes oszlopra.A kitöltő fogantyúval kiválaszthatja és húzhatja, vagy egyszerűen másolja és illessze be a C3 cellát az összes többi cellába (ez automatikusan módosítja a hivatkozást, és a megfelelő eredményt adja).

Ezzel az alábbiakban látható eredményt kapja.

Ez egy nagyon egyszerű módszer, amely a legtöbb esetben jól működik.

A logika egyszerű – minden cella felveszi a rajta lévő értéket (ami az előző nap összesített összege), és hozzáadja az értéket a szomszédos cellában (ami az aktuális napi eladási érték).

Csak egy hátránya van: ha törli bármelyik sort ebben az adatkészletben, az alábbi cellák mindegyike hivatkozási hibát fog visszaadni (#REF!)

Ha az adatkészlet rendelkezik ezzel a lehetőséggel, használja a következő módszert a SUM képlet használatával

Használja a SUM összeget részlegesen zárolt cellahivatkozásokkal

Tegyük fel, hogy dátum szerinti értékesítési adataival rendelkezik, és a C oszlopban futó összeget szeretne kiszámítani.

Az alábbiakban látható a SUM képlet, amely egy futó összeget ad.

= SUM ($ B $ 2: B2)

Hadd magyarázzam el, hogyan működik ez a képlet.

A fenti SUM képletben a hivatkozást használtam a $B$2:B2 hozzáadáshoz

• $B$2 – Ez egy abszolút hivatkozás, ami azt jelenti, hogy ez a hivatkozás nem változik, amikor bemásolom ugyanazt a képletet az alábbi cellába.Tehát amikor az alábbi cellába másolja a képletet, a képlet a következőre változik: SUM($B$2:B3)
• B2 - Ez a hivatkozás második része, ez egy relatív hivatkozás, ami azt jelenti, hogy amikor lefelé vagy jobbra másolom a képletet, ez beállítja.Tehát az alábbi cellában lévő képlet másolásakor az érték B3 lesz

Az a szép ebben a megközelítésben, hogy ha törli bármelyik sort az adatkészletben, ez a képlet korrigálja, és továbbra is a megfelelő futó összeget adja meg.

Számítsa ki a futó teljes összeget Excel munkalapon

Ha táblázatos adatokkal dolgozik Excelben, a legjobb, ha azokat Excel-táblázattá konvertálja.Könnyebbé teszi az adatok kezelését, és az olyan eszközök használatát is, mint a Power Query és a Power Pivot.

Az Excel-tábla használata számos előnnyel jár, mint például a strukturált hivatkozások (amelyek nagyon egyszerűvé teszik a táblázatban szereplő adatok hivatkozását és a képletekben való felhasználását), valamint a hivatkozások automatikus beállítását, amikor adatokat vesz fel vagy távolít el a táblázatból.

Bár továbbra is használhatja a fenti képletet, amelyet egy Excel-lapon mutattam meg, hadd mutassak meg néhány jobb módszert erre.

Tegyük fel, hogy van egy Excel-táblázata, mint az alábbi, és a C oszlopban futó összeget szeretne kiszámítani.

Íme a képlet ennek elvégzéséhez:

=SZUM(Eladási adatok[[#Fejlécek],[Akció]]:[@Akció])

A fenti képlet kissé hosszúnak tűnhet, de nem kell magadnak megírnod.Amit az összegképletben lát, azt strukturált hivatkozásnak nevezik, és ez egy hatékony módja annak, hogy az Excel egy Excel-táblázat adott adatpontjaira hivatkozzon.

Például a SalesData[[#Headers],[Sale]] az Értékesítés fejlécére utal a SalesData táblában (A SalesData annak az Excel-táblázatnak a neve, amelyet a táblázat létrehozásakor adtam meg)

Míg a [@Sale] az Eladás oszlop ugyanazon sorában lévő cellában lévő értékre utal.

Ezt csak a megértésed érdekében magyarázom el, de még ha nem is tudsz semmit a strukturált hivatkozásokról, akkor is könnyen elkészítheted ezt a képletet.

Íme a lépések ehhez:

1. A C2 cellába írja be: =SUM(
2. Válassza ki a B1 cellát, amely az eladási értékkel rendelkező oszlop fejléce.Használhatja az egeret vagy a nyílbillentyűket.Észreveheti, hogy az Excel automatikusan beír egy strukturált hivatkozást a cellára
3. Hozzáadás: (kettőspont)
4. Válassza ki a B2 cellát.Az Excel automatikusan újra beszúrja a strukturált hivatkozásokat a cellákba
5. Zárja be a zárójelet, és nyomja meg az Enter billentyűt

Észre fogja venni azt is, hogy nem kell a képletet a teljes oszlopba másolni, az Excel munkalap ezt automatikusan elvégzi.

Ennek a megközelítésnek egy másik előnye, hogy ha új rekordot ad hozzá ehhez az adatkészlethez, az Excel-munkalap automatikusan kiszámítja az összes új rekord futó összegét.

Bár az oszlopfejléceket belefoglaltuk a képletbe, ne feledje, hogy a képlet figyelmen kívül hagyja a fejléc szövegét, és csak az oszlopban lévő adatokat veszi figyelembe.

Számítsa ki a futó összegeket a Power Query segítségével

A Power Query egy csodálatos eszköz az adatbázisokhoz való kapcsolódáshoz, az adatok több forrásból történő kinyeréséhez, valamint az Excelbe való helyezés előtti átalakításhoz.

Ha már használja a Power Queryt, hatékonyabb, ha futó összegeket vesz fel, amikor magában a Power Query-szerkesztőben alakítja át az adatokat (ahelyett, hogy először lekérné az adatokat az Excelben, majd a fenti módszerek bármelyikével hozzáadja a futó összegeket).

Bár a Power Queryben nincs beépített funkció a futó összegek hozzáadására (bárcsak lenne), ezt egy egyszerű képlettel is megteheti.

Tegyük fel, hogy van egy Excel-táblázata, amely így néz ki, és futó összegeket szeretne hozzáadni ezekhez az adatokhoz:

Íme a lépések ehhez:

1. Jelölje ki az Excel táblázat bármelyik celláját
2. kattintson az adatokra
3. A Get & Transform lapon kattintson a Táblából/Tartományból ikonra.Ezzel megnyílik a táblázat a Power Query Editorban
4. [Opcionális] Ha a dátumoszlop még nincs rendezve, kattintson a szűrő ikonra a dátum oszlopban, majd kattintson a Növekvő rendezés lehetőségre.
5. Kattintson az Oszlop hozzáadása fülre a Power Query-szerkesztőben
6. Az Általános csoportban kattintson az Index oszlop legördülő menüre (ne az Index oszlop ikonra, hanem a mellette lévő kis fekete ferde nyílra kattintson a további lehetőségek megjelenítéséhez)
7. Kattintson az "1-től" opcióra.Ezzel hozzáad egy új indexoszlopot, amely 1-gyel kezdődik, és az oszlopban 1-gyel növelt számokat ír be.
8. Kattintson az Egyéni oszlopok ikonra (szintén az Oszlopok hozzáadása lapon)
9. A megnyíló Egyéni oszlopok párbeszédpanelen adja meg az új oszlop nevét.Ebben a példában a "Running Total" nevet fogom használni.
10. Az Egyéni oszlopképlet mezőbe írja be a következő képletet:List.Sum(Lista.Tartomány(#"Hozzáadott index"[Akció],0,[Index]))
11. Győződjön meg arról, hogy van egy jelölőnégyzet a párbeszédpanel alján, amely azt írja ki, hogy „Nem észleltünk szintaktikai hibákat”
12. Kattintson az OK gombra.Ezzel hozzáad egy új futó összeg oszlopot
13. csepp index oszlop
14. Kattintson a Fájl fülre, majd a Bezárás és Betöltés gombra

A fenti lépések egy új munkalapot szúrnak be a munkafüzetbe a futó összegeket tartalmazó táblázattal.

Ha úgy gondolja, hogy ez túl sok lépés az előző, egyszerű képletekkel végzett megközelítéshez képest, akkor igaza van.

Ha már rendelkezik adatkészlettel, és csak futó összegeket kell hozzáadnia, akkor a legjobb, ha nem használja a Power Queryt.

A Power Query használata olyan helyzetekben van értelme, amikor adatokat kell kinyernie egy adatbázisból, vagy több különböző munkafüzetből kell adatokat kombinálnia, és a folyamat során futó összegeket kell hozzáadnia hozzájuk.

Ezenkívül, ha egyszer automatizálja ezt a Power Query segítségével, az adatkészlet következő változásakor nem kell újra megtennie, csak frissítheti a lekérdezést, és az új adatkészlet alapján fog megjelenni.

Hogy működik ez?

Most pedig hadd magyarázzam el gyorsan, mi történik ezzel a megközelítéssel.

Az első dolog, amit a Power Query szerkesztőben teszünk, az, hogy beszúrunk egy indexoszlopot, amely 1-gyel kezdődik, és a cellán lefelé haladva növekszik.

Ezt azért tesszük, mert ezt az oszlopot kell használnunk, amikor a következő lépésben beszúrt másik oszlopban lévő futó összeget számítjuk ki.

Ezután beszúrunk egy egyéni oszlopot, és a következő képletet használjuk

List.Sum(Lista.Tartomány(#"Hozzáadott index"[Akció],0,[Index]))

Ez egy List.Sum képlet, amely megadja a benne megadott tartomány összegét.

A tartomány a List.Range funkcióval van megadva.

A List.Range függvény az értékesítés oszlopban megadott tartományt veszi kimenetként, és a tartomány az Index értékétől függően változik.Például az első rekordnál a tartomány az első eladási érték.Ez a tartomány a cellában lefelé haladva bővül.

Tehát az első cellához.A List.Sum csak az első eladási érték összegét adja meg, a második cellában pedig az első két eladási érték összegét, és így tovább.

Bár ez a megközelítés jól működik, nagy adathalmazok (több ezer sor) esetén nagyon lelassulhat.

Számítsa ki a futó összegeket kritériumok alapján

Eddig láthattunk példákat egy oszlopban lévő összes érték futó összegének kiszámítására.

Bizonyos esetekben azonban érdemes kiszámítani egy adott rekord futó végösszegét.

Például az alábbiakban van egy adatkészletem, ahol két különböző oszlopban szeretném megszámolni a nyomtatók és szkennerek teljes számát.

Ezt a SUMIF képlet segítségével lehet megtenni, amely kiszámítja a futó összeget, miközben biztosítja a meghatározott feltételek teljesülését.

Íme a képlet a nyomtató oszlophoz:

=SUMIF($C$2:C2,$D$1,$B$2:B2)

Hasonlóképpen, a szkenner teljes összegének kiszámításához használja a következő képletet:

=SUMIF($C$2:C2,$E$1,$B$2:B2)

A fenti képletben a SUMIF-et használtam, amely összeget ad egy tartományban, ha a megadott feltételek teljesülnek.

A képlet három paramétert vesz fel:

1. hatótávolság: Ez a feltételek azon tartománya, amelyet a rendszer a megadott feltételhez képest ellenőrzi
2. kritériumok: ez az a feltétel, amely csak akkor kerül ellenőrzésre, ha ez a feltétel teljesül, akkor a harmadik paraméterben lévő érték, az összegtartomány hozzáadódik
3. [összeg_tartomány]: ez a feltétel teljesülése esetén hozzáadandó értékek összegének tartománya

Ezen túlmenőenhatótávolság和összeg_tartományparamétert, zároltam a hivatkozás második részét, így ahogy haladunk lefelé a cellában, a tartomány folyamatosan bővül.Ez lehetővé teszi, hogy csak az adott tartományig (tehát a futó összegig) vegyünk figyelembe és adjunk hozzá értékeket.

Ebben a képletben a fejléc oszlopokat (Nyomtatók és Szkennerek) használom kritériumként.A szabványt akkor is kódolhatja, ha az oszlopfejlécek nem egyeznek meg pontosan a szabványos szöveggel.

Ha több feltételt kell ellenőriznie, használhatja a SUMIFS képletet.

Összesítések futtatása egy kimutatástáblázatban

Ha futó összeget szeretne hozzáadni a kimutatás eredményeihez, ezt egyszerűen megteheti a kimutatás beépített funkciójával.

Tegyük fel, hogy van egy pivot táblája, mint az alábbi, amelynek egyik oszlopában a dátumok, a másikban pedig értékesítési értékek szerepelnek.

A következő lépésekkel adhat hozzá egy további oszlopot, amely az összesített eladásokat mutatja dátum szerint:

1. Húzza az Eladás mezőt, és dobja az Érték területre.
2. Ezzel hozzáad egy újabb oszlopot az értékesítési értékkel
3. Kattintson a Teljes értékesítés 2 lehetőségre az Érték területen
4. Kattintson az Értékmező beállításai lehetőségre
5. Az Értékmező beállításai párbeszédpanelen módosítsa az Egyéni nevet Futóösszeg értékre
6. Kattintson az "Érték megjelenítése másként" fülre
7. A Megjelenítési érték legördülő menüben válassza a „Futó összesen” lehetőséget
8. Az Alapvető mezők beállításainál győződjön meg arról, hogy a Dátum van kiválasztva
9. Kattintson az OK gombra

A fenti lépések a második értékesítési oszlopot „futó összeg” oszlopra módosítják.

Tehát ez néhány módszer, amellyel kiszámíthatja a futó összegeket az Excelben.Ha táblázatos adatai vannak, akkor egyszerű képleteket, ha pedig Excel-táblázatokat, akkor strukturált hivatkozásokat használó képleteket használhat.

Kitértem arra is, hogyan számítható ki a futó összegeket Power Query és PivotTables segítségével.

Remélem hasznosnak találtad ezt az oktatóanyagot.