Ha az Excel munkalap változó cellatartományon alapuló számításokat tartalmaz, használja a SUM és OFFSET függvényeket együtt egy SUM OFFSET képletben, hogy leegyszerűsítse a számítások naprakészen tartását.
A cikkben található utasítások az Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013 és Excel 2010 verziókra vonatkoznak.
Dinamikus tartomány létrehozása a SUM és OFFSET függvényekkel
Ha folyamatosan változó időszakra vonatkozó számításokat használ – például a havi eladások meghatározása –, használja az OFFSET funkciót az Excelben, hogy beállítson egy dinamikus tartományt, amely a napi eladási adatok hozzáadásával változik.
A SUM függvény önmagában általában képes új adatcellák beszúrására az összegzett tartományba. Egy kivétel akkor fordul elő, ha az adatokat abba a cellába illesztik be, ahol a függvény jelenleg található.
Az alábbi példában az egyes napokra vonatkozó új eladási adatok a lista aljára kerülnek, így az összesítés folyamatosan egy cellával lejjebb tolódik az új adatok hozzáadásakor.
Az oktatóanyag követéséhez nyisson meg egy üres Excel-munkalapot, és adja meg a mintaadatokat. A munkalapot nem kell a példához hasonlóan formázni, de ügyeljen arra, hogy az adatokat ugyanabban a cellában adja meg.
Ha csak a SUM függvényt használjuk az adatok összesítésére, a függvény argumentumként használt cellák tartományát minden új adat hozzáadásakor módosítani kell.
A SUM és OFFSET függvények együttes használatával az összesített tartomány dinamikussá válik, és az új adatcellákhoz igazodik. Az új adatcellák hozzáadása nem okoz problémát, mert a tartomány folyamatosan változik minden új cella hozzáadásával.
Szintaxis és argumentumok
Ebben a képletben a SUM függvény az argumentumként megadott adatok tartományának összegzésére szolgál. Ennek a tartománynak a kezdőpontja statikus, és a képlet által összesítendő első szám cellahivatkozásaként azonosítható.
Az OFFSET függvény a SUM függvénybe van beágyazva, és dinamikus végpontot hoz létre a képlet által összesített adattartományhoz. Ez úgy érhető el, hogy a tartomány végpontját a képlet helye feletti egy cellára állítja.
A képlet szintaxisa:
=SZUM(Tartomány kezdete:ELTOLÁS(Referencia, sorok, oszlopok))
Az érvek a következők:
- Tartomány kezdete: A SUM függvény által összesített cellatartomány kezdőpontja. Ebben a példában a kiindulási pont a B2 cella.
- Referencia: A tartomány végpontjának kiszámításához használt szükséges cellahivatkozás. A példában a Referencia argumentum a képlet cellahivatkozása, mert a tartomány egy cellával a képlet felett végződik.
- Sorok: Az eltolás kiszámításához használt Referencia argumentum feletti vagy alatti sorok száma kötelező. Ez az érték lehet pozitív, negatív vagy nullára állítható. Ha az eltolás helye a Referencia argumentum felett van, az érték negatív. Ha az eltolás kisebb, a Sorok argumentum pozitív. Ha az eltolás ugyanabban a sorban található, akkor az argumentum nulla. Ebben a példában az eltolás egy sorral a Referencia argumentum felett kezdődik, így az argumentum értéke negatív egy (-1).
- Cols: Az eltolás kiszámításához használt Referencia argumentumtól balra vagy jobbra eső oszlopok száma. Ez az érték lehet pozitív, negatív vagy nullára állítható. Ha az eltolás helye a Referencia argumentumtól balra található, akkor ez az érték negatív. Ha az eltolás jobb, a Cols argumentum pozitív. Ebben a példában az összesítendő adatok ugyanabban az oszlopban vannak, mint a képlet, így ennek az argumentumnak az értéke nulla.
Használja a SUM OFFSET képletet a teljes értékesítési adatokhoz
Ez a példa egy SUM OFFSET képletet használ a munkalap B oszlopában felsorolt napi értékesítési adatok végösszegének visszaadásához. Kezdetben a képlet a B6 cellába került, és négy napra összesítette az értékesítési adatokat.
A következő lépés az SUM OFFSET képlet egy sorral lejjebb mozgatása, hogy helyet adjon az ötödik napi eladási összegnek. Ez egy új 6. sor beszúrásával érhető el, amely áthelyezi a képletet a 7. sorba.
Az áthelyezés eredményeként az Excel automatikusan frissíti a Referencia argumentumot a B7 cellára, és hozzáadja a B6 cellát a képlettel összegzett tartományhoz.
- Válassza ki a B6 cellát, amely az a hely, ahol a képleteredmények kezdetben megjelennek.
-
Válassza ki a szalag Képletek lapját.
Image -
Válasszon Matek és trig.
Image -
Válassza ki a következőt: SUM.
Image - A Funkció argumentumai párbeszédpanelen helyezze a kurzort a Number1 szövegmezőbe.
-
A munkalapon válassza ki a B2 cellát a cellahivatkozás megadásához a párbeszédpanelen. Ez a hely a képlet statikus végpontja.
Image - A Funkció argumentumai párbeszédpanelen helyezze a kurzort a Number2 szövegmezőbe.
-
Írja be: OFFSET(B6, -1, 0). Ez az OFFSET függvény képezi a képlet dinamikus végpontját.
Image -
A funkció befejezéséhez és a párbeszédpanel bezárásához válassza az OK lehetőséget. Az összeg a B6 cellában jelenik meg.
Image
Adja hozzá a következő napi értékesítési adatokat
A következő napi értékesítési adatok hozzáadása:
- Kattintson jobb gombbal a 6. sor fejlécére.
-
Válassza ki a Beszúrás lehetőséget, ha új sort szeretne beszúrni a munkalapba. A SUM OFFSET képlet egy sorral lejjebb lép a B7 cellába, és a 6. sor most üres.
Image - Válassza ki a A6 cellát, és írja be a 5 számot, jelezve, hogy az ötödik nap eladási végösszegét írják be.
-
Válassza ki a B6 cellát, írja be: $1458.25, majd nyomja meg az Enter.
Image - A B7 cella frissítése az új végösszegre: 7137,40 USD.
A B7 cella kiválasztásakor a frissített képlet megjelenik a képletsorban.
=SZUM(B2:ELTOLÁS(B7, -1, 0))
Az OFFSET függvénynek két opcionális argumentuma van: Height és Width, amelyeket ebben a példában nem használtunk. Ezek az argumentumok adják meg az OFFSET függvénynek a kimenet alakját a sorok és oszlopok számában.
Ezen argumentumok kihagyásával a függvény a Referencia argumentum magasságát és szélességét használja helyette, amely ebben a példában egy sor magas és egy oszlop széles.
