Teljes futás (más néven halmozott összeg) nagyon gyakran használják számos helyzetben. Ez egy mutató, amely megmondja, hogy mennyi az eddigi értékek összege.
Például, ha rendelkezik a havi értékesítési adatokkal, akkor a futó összesítés megmutatja, hogy mennyi értékesítés történt a hónap első napjától számított adott napig.
Vannak más helyzetek is, amikor gyakran a futó összeget használják, például a készpénzmaradvány kiszámítása a bankszámlakivonaton/főkönyvben, a kalóriák számolása az étkezési tervben stb.
A Microsoft Excelben többféle módon lehet kiszámítani a futó összegeket.
A választott módszer az adatok felépítésének módjától is függ.
Például, ha egyszerű táblázatos adatokkal rendelkezik, akkor használhat egyszerű SUM képletet, de ha van Excel táblája, akkor a legjobb strukturált hivatkozásokat használni. Ehhez a Power Query -t is használhatja.
Ebben az oktatóanyagban ezeket a különböző módszereket fogom bemutatni számolja ki a futó összegeket Excelben.
Kezdjük hát el!
Összes futás kiszámítása táblázatos adatokkal
Ha táblázatos adatokkal rendelkezik (azaz egy Excel táblázat, amelyet nem alakít át Excel táblává), akkor néhány egyszerű képlettel kiszámíthatja a futó összesítéseket.
A Kiegészítő operátor használata
Tegyük fel, hogy dátum szerinti értékesítési adatokkal rendelkezik, és ki szeretné számítani a futó összeget a C oszlopban.
Az alábbiakban bemutatjuk a lépéseket.
1. lépés - Írja be a C2 cellába, amely az első cella, ahol a futó összeget szeretné megadni
= B2
Ez egyszerűen ugyanazokat az értékesítési értékeket kapja a B2 cellában.
2. lépés - A C3 cellába írja be az alábbi képletet:
= C2+B3
3. lépés - Alkalmazza a képletet az egész oszlopra. A kitöltő fogantyúval kiválaszthatja és húzhatja, vagy egyszerűen másolhatja be a C3 cellát az összes többi cellába (ez automatikusan beállítja a referenciát, és megfelelő eredményt ad).
Ez megadja az alább látható eredményt.
Ez egy nagyon egyszerű módszer, és a legtöbb esetben jól működik.
A logika egyszerű - minden cella felveszi a felette lévő értéket (ami az előző dátumhoz tartozó összesített összeg), és hozzáadja az értéket a mellette lévő cellához (ami az adott nap értékesítési értéke).
Egyetlen hátránya van - ha törli az adathalmaz bármelyik sorát, akkor az alábbi cellák, amelyek referencia hibát adnak vissza (#REF!)
Ha ez lehetséges az adathalmazával, használja a következő SUM képletet használó módszert
A SUM használata részben zárolt cellareferenciával
Tegyük fel, hogy dátum szerinti értékesítési adatokkal rendelkezik, és ki szeretné számítani a futó összeget a C oszlopban.
Az alábbiakban látható a SUM képlet, amely megadja a futóösszeget.
= SUM ($ B $ 2: B2)
Hadd magyarázzam meg, hogyan működik ez a képlet.
A fenti SUM képletben a hivatkozást használtam hozzá $ B $ 2: B2
- $ B $ 2 - ez abszolút hivatkozás, ami azt jelenti, hogy amikor ugyanazt a képletet másolom az alábbi cellákba, ez a hivatkozás nem fog változni. Tehát amikor a képletet az alábbi cellába másolja, a képlet SUM -ra változik ($ B $ 2: B3)
- B2 - ez a hivatkozás második része, amely relatív referencia, ami azt jelenti, hogy ez módosul, ahogy lefelé vagy jobbra másolom a képletet. Tehát amikor a képletet az alábbi cellába másolja, ez az érték B3 lesz
Olvassa el még: Abszolút, relatív és vegyes cella referenciák az Excelben
Ennek a módszernek az a nagyszerűsége, hogy ha törli az adathalmaz bármelyik sorát, akkor ez a képlet kiigazítja, és továbbra is megadja a megfelelő futási összegeket.
A futóösszeg kiszámítása Excel táblázatban
Ha Excel táblázatos adatokkal dolgozik, célszerű Excel táblává alakítani. Sokkal könnyebbé teszi az adatok kezelését, és lehetővé teszi az olyan eszközök használatát is, mint a Power Query és a Power Pivot.
Az Excel táblázatok kezelése olyan előnyökkel jár, mint a strukturált hivatkozások (ami nagyon megkönnyíti a táblázatban szereplő adatok hivatkozását és a képletekben való használatát), valamint a hivatkozások automatikus beállítása, ha adatokat ad hozzá vagy töröl a táblázatból.
Bár továbbra is használhatja a fenti képletet, amelyet Excel táblázatban mutattam be, hadd mutassak meg néhány jobb módszert erre.
Tegyük fel, hogy rendelkezik Excel -táblával, amint az alább látható, és ki szeretné számítani a futó összeget a C oszlopban.
Az alábbiakban ezt a képletet találja meg:
= SUM (Értékesítési adatok [[#Fejlécek], [Akció]]: [@eladó])
A fenti képlet kissé hosszúnak tűnhet, de nem kell saját maga írnia. amit az összegképletben lát, strukturált hivatkozásoknak nevezzük, ami az Excel hatékony módja az Excel táblázat bizonyos adatpontjaira való hivatkozáshoz.
Például a SalesData [[#Headers], [Sale]] a SalesData táblázat Sale fejlécére utal (a SalesData annak az Excel -táblázatnak a neve, amelyet a táblázat létrehozásakor adtam)
A [@Sale] pedig az Értékesítés oszlop ugyanazon sorában lévő cella értékére utal.
Ezt most itt magyaráztam, hogy megértsük, de még ha semmit sem tud a strukturált hivatkozásokról, akkor is könnyen létrehozhatja ezt a képletet.
Ennek lépései az alábbiakban találhatók:
- A C2 cellába írja be = SUM (
- Válassza ki a B1 cellát, amely az értékesítési értéket tartalmazó oszlop fejléce. Használhatja az egeret vagy a nyilakat. Észre fogja venni, hogy az Excel automatikusan megadja az adott cella strukturált hivatkozását
- Adjon hozzá egy: (kettőspont szimbólum)
- Válassza ki a B2 cellát. Az Excel ismét automatikusan beszúrja a cella strukturált hivatkozását
- Zárja be a konzolt, és nyomja meg az enter billentyűt
Azt is észre fogja venni, hogy nem kell a képletet a teljes oszlopba másolnia, egy Excel -táblázat automatikusan megteszi ezt Ön helyett.
Egy másik nagyszerű dolog ebben a módszerben az, hogy ha új rekordot ad hozzá ehhez az adatkészlethez, az Excel táblázat automatikusan kiszámítja az összes új rekord futási összegét.
Míg az oszlop fejlécét beépítettük képletünkbe, ne feledje, hogy a képlet figyelmen kívül hagyja a fejléc szövegét, és csak az oszlop adatait veszi figyelembe
A teljes futás kiszámítása a Power Query használatával
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 és átalakításához, mielőtt Excelbe helyezi őket.
Ha már dolgozik a Power Query -vel, hatékonyabb lenne hozzáadni a futó összesítéseket, miközben az adatokat átalakítja a Power Query szerkesztőben (ahelyett, hogy először megkapná az adatokat az Excelben, majd hozzáadná a futó összegeket a fentiek bármelyikével) fent ismertetett módszerek).
Bár a Power Query -ben nincs beépített szolgáltatás a futó összesítések hozzáadásához (bárcsak lenne), ezt mégis megteheti egy egyszerű képlet segítségével.
Tegyük fel, hogy rendelkezik Excel -táblával az alábbiak szerint, és hozzá szeretné adni a futó összesítéseket ezekhez az adatokhoz:
Ennek lépései az alábbiakban találhatók:
- Válassza ki az Excel táblázat bármelyik celláját
- Kattintson az Adatok elemre
- A Get & Transform lapon kattintson a Table/Range ikonra. Ez megnyitja a táblát a Power Query szerkesztőben
- [Nem kötelező] Ha a Dátum oszlop még nincs rendezve, kattintson a szűrő ikonra a Dátum oszlopban, majd kattintson a Rendezés növekvő sorrendre
- Kattintson az Oszlop hozzáadása fülre a Power Query szerkesztőben
- Az Általános csoportban kattintson az Indexoszlop legördülő menüre (ne kattintson az Indexoszlop ikonra, hanem a mellette lévő kis fekete döntött nyílra a további lehetőségek megjelenítéséhez)
- Kattintson az „1 -től” lehetőségre. Ezzel új indexoszlopot ad hozzá, amely egyből indul, és az egész oszlopban 1 -gyel növekvő számokat ír be
- Kattintson az „Egyéni oszlop” ikonra (amely szintén az Oszlop hozzáadása lapon található)
- A megnyíló egyéni oszlop párbeszédpanelen adja meg az új oszlop nevét. ebben a példában a „Running Total” nevet fogom használni
- Az Egyéni oszlop képlet mezőbe írja be az alábbi képletet: List.Sum (List.Range (#"Hozzáadott index" [Eladás], 0, [Index]))
- Győződjön meg arról, hogy a párbeszédpanel alján van egy jelölőnégyzet, amely azt mondja: „Nincs szintaktikai hiba”
- Kattintson az OK gombra. Ez hozzáad egy új futó összes oszlopot
- Távolítsa el az Index oszlopot
- Kattintson a Fájl fülre, majd a „Bezárás és betöltés” gombra
A fenti lépések egy új lapot illesztenének be a munkafüzetbe, egy táblával, amely tartalmazza a futó összegeket.
Ha most azt gondolja, hogy ez túl sok lépés az egyszerű képletek korábbi módszereihez képest, akkor igaza van.
Ha már rendelkezik adatkészlettel, és mindössze annyit kell tennie, hogy hozzáadja a futó összesítéseket, akkor jobb, ha nem használja a Power Query -t.
A Power Query használatával akkor van értelme, ha adatokat kell kinyernie egy adatbázisból, vagy több különböző munkafüzetből kell egyesítenie az adatokat, és közben hozzá kell adnia futó összegeket is.
Továbbá, ha ezt az automatizálást elvégzi a Power Query használatával, a következő alkalommal, amikor az adatkészlet megváltozik, nem kell újra megismételnie, egyszerűen frissítheti a lekérdezést, és az az új adatkészlet alapján megadja az eredményt.
Hogy működik ez?
Most hadd magyarázzam el gyorsan, mi történik ezzel a módszerrel.
Az első dolog, amit a Power Query szerkesztőben végezünk, hogy egy indexoszlopot szúrunk be eggyel kezdve, és eggyel növekszik, ahogy lemegy a cellákban.
Ezt azért tesszük, mert ezt az oszlopot kell használnunk, miközben egy másik oszlopban kiszámítjuk a futóösszeget, amelyet a következő lépésben szúrunk be.
Ezután beszúrunk egy egyéni oszlopot, és az alábbi képletet használjuk
List.Sum (List.Range (#"Hozzáadott index" [Akció], 0, [Index]))
Ez egy List.Sum képlet, amely megadja a benne megadott tartomány összegét.
És ezt a tartományt a List.Range függvénnyel határozzák meg.
A List.Range függvény az értékesítési oszlopban megadott tartományt adja meg kimenetként, és ez a tartomány az Index értéke alapján változik. Például az első rekord esetében a tartomány egyszerűen az első értékesítési érték lenne. És ahogy halad a cellákon, ez a tartomány kibővül.
Tehát az első cellához. List.Sum csak az első eladási érték összegét adja meg, a második cella pedig az első két értékesítési érték összegét, és így tovább.
Bár ez a módszer jól működik, nagy adathalmazokkal - több ezer sorral - nagyon lassú lesz. Ha nagy adathalmazzal van dolgában, és futó összegeket szeretne hozzáadni hozzá, tekintse meg ezt az oktatóanyagot, amely más gyorsabb módszereket mutat be.A futóösszeg kiszámítása kritériumok alapján
Eddig láttunk példákat, ahol egy oszlop összes értékére kiszámítottuk a futóösszeget.
Előfordulhatnak azonban olyan esetek, amikor ki kell számítani az egyes rekordok futási összegét.
Például az alábbiakban van egy adatkészletem, és két különböző oszlopban szeretném külön kiszámítani a nyomtatók és szkennerek futási összegét.
Ezt megteheti egy SUMIF képlet segítségével, amely kiszámítja a futóösszeget, miközben meggyőződik arról, hogy a megadott feltétel teljesül.
Az alábbi képlet ezt teszi a Nyomtató oszlopokhoz:
= SUMIF ($ C $ 2: C2, $ D $ 1, $ B $ 2: B2)
Hasonlóképpen, a szkennerek futóösszegének kiszámításához használja az alábbi képletet:
= SUMIF ($ C $ 2: C2, $ E $ 1, $ B $ 2: B2)
A fenti képletekben a SUMIF -ot használtam, amely megadja az összeg egy tartományát, ha a megadott feltételek teljesülnek.
A képlet három érvet tartalmaz:
- hatótávolság: ez az a kritériumtartomány, amelyet a megadott feltételeknek megfelelően kell ellenőrizni
- kritériumok: ez az a feltétel, amelyet csak akkor kell bejelölni, ha ez a feltétel teljesül, akkor a harmadik argumentum értékei, azaz az összegtartomány hozzáadódnak
- [összeg_tartomány]: ez az összegtartomány, ahonnan az értékek hozzáadódnak, ha a feltételek teljesülnek
Továbbá a hatótávolság és összeg_tartomány érvet, lezártam a hivatkozás második részét, hogy a cellákon lefelé haladva a tartomány folyamatosan bővüljön. Ez lehetővé teszi számunkra, hogy csak az adott tartományig vegyük figyelembe és adjuk hozzá az értékeket (tehát futó összegeket).
Ebben a képletben a fejléc oszlopot (Nyomtató és szkenner) használtam kritériumként. akkor is keményen kódolhatja a feltételeket, ha az oszlopfejlécek nem egyeznek meg a feltételek szövegével.
Ha több feltételt kell ellenőriznie, használhatja a SUMIFS képletet.
Összes futás kimutatástáblákban
Ha futó összegeket szeretne hozzáadni egy kimutatástábla eredményéhez, akkor ezt könnyen megteheti a Pivot táblák beépített funkciójával.
Tegyük fel, hogy rendelkezik egy pivot táblával, amint az alább látható, ahol az egyik oszlopban a dátum, a másik oszlopban pedig az értékesítési érték szerepel.
Az alábbi lépésekkel további oszlopot adhat hozzá, amely megjeleníti az eladások összesített dátum szerinti dátumát:
- Húzza az Eladás mezőt, és tegye az Érték területre.
- Ez egy újabb oszlopot ad hozzá az Értékesítési értékekkel
- Kattintson az Érték területen az Értékesítés összege2 lehetőségre
- Kattintson az „Értékmező beállításai” lehetőségre
- Az Értékmező beállításai párbeszédpanelen módosítsa az egyéni nevet „Összes futás” értékre
- Kattintson az „Érték megjelenítése másként” fülre
- Az Érték megjelenítése legördülő menüben válassza a „Összes futás” lehetőséget
- Az Alap mező beállításaiban győződjön meg arról, hogy a Dátum van kiválasztva
- Kattintson az OK gombra
A fenti lépések a második eladási oszlopot a Futó összes oszlopra változtatják.
Tehát ezek azok a módszerek, amelyekkel kiszámíthatja a futó összeget az Excelben. Ha táblázatos formátumú adatokkal rendelkezik, használhat egyszerű képleteket, és ha rendelkezik Excel -táblával, akkor strukturált hivatkozásokat használó képleteket.
A Power Query és a kimutatástáblák segítségével kitértem a futóösszeg kiszámításának módjára is.
Remélem, hasznosnak találta ezt az oktatóanyagot.
