Excel SUM és OFFSET képlet

Ha az Ön Excel munkalapja a cellák változó tartományán alapuló számításokat tartalmaz, akkor a SUM OFFSET formula SUM és OFFSET függvényekkel együtt egyszerűsíti a számítások naprakészségét.

Dinamikus tartomány létrehozása a SUM és OFFSET függvényekkel

Ha a folyamatosan változó időtartamú számításokat használja - például a hónap teljes összegének értékesítését -, akkor az Excel OFFSET funkciója lehetővé teszi egy olyan dinamikus tartomány beállítását, amely az egyes napi értékesítési adatokkal együtt változik.

Önmagában az SUM funkció általában beillesztheti az új adatcellák beillesztését az összegzett tartományba.

Az egyik kivétel akkor következik be, amikor az adatok be vannak helyezve a cellába, ahol a függvény jelenleg található.

A fenti példafájlban minden nap új értékesítési adatait a lista alján adjuk hozzá, így a teljes összeg folyamatosan lecsúszik egy cellát minden egyes alkalommal, amikor új adatokat adunk hozzá.

Ha az SUM függvényt önmagában használták az adatok összesítéséhez, akkor minden egyes új adat hozzáadásakor módosítani kell a függvényként használt cellák tartományát.

A SUM és az OFFSET funkciók együttes használatával azonban a teljes tartomány dinamikus lesz. Más szavakkal megváltozik az új adatkészletek befogadására. Az új adatcellák hozzáadása nem okoz problémákat, mivel a tartomány folyamatosan állítható, ahogy minden új cellát hozzáadnak.

Szintaxis és érvek

Lásd a fenti képen a következő útmutatót.

Ebben a képletben az SUM függvény használható az argumentumként megadott adatmennyiség teljesítéséhez. Ennek a tartománynak a kezdőpontja statikus, és a cella hivatkozása az első számra, amelyet a képletnek kell teljesítenie.

Az OFFSET függvény beágyazódik a SUM funkcióba, és dinamikus végpontot hoz létre a képlet által érintett adattartományhoz. Ezt úgy érjük el, hogy a tartomány végpontját a képlet helyéhez képest egy cellára állítjuk be.

A képlet szintaxisa:

= SUM (Range Start: OFFSET (Referencia, Sorok, Cols))

• Range Start: A SUM funkcióval megegyező cellák tartományának kiindulópontja szükséges. A példánkban ez a B2 cellát jelenti.
• Referencia: A tartomány végpontjának kiszámításához használt szükséges cellahivatkozás több sorból és oszlopból áll. A példaképben a Referencia argumentum maga a képlet referenciája, mivel mindig azt szeretné, hogy a tartomány véget vessen egy képlet fölötti cellának.
• sorok: Az eltolás kiszámításakor használt hivatkozási argumentum felett vagy alatt található sorok száma szükséges. Ez az érték lehet pozitív, negatív vagy nullára állítva. Ha az eltolás helye a Referencia argumentum, az érték negatív. Ha ez alatta marad, a sorok érve pozitív. Ha az eltolás ugyanabban a sorban található, az argumentum nulla. Ebben a példában az offset egy sorral kezdődik a Reference argumentum felett, ezért az argumentum értéke negatív (-1).
• cols: Az eltolás kiszámításához használt Referencia argumentum bal vagy jobb oldali oszlopainak száma szükséges. Ez az érték lehet pozitív, negatív vagy nullára állítva. Ha az eltolás helye a Referencia-argumentum bal oldalán található, akkor ez az érték negatív. Ha jobbra, a Cols érve pozitív. Ebben a példában az összesített adat ugyanabban az oszlopban van, mint a képlet, ezért az argumentum értéke nulla.

A SUM OFFSET képlet használata a teljes értékesítési adatokhoz

Ez a példa SUM OFFSET képletet alkalmaz a teljes munkalapon a B oszlopban felsorolt ​​napi értékesítési adatokért.

Kezdetben a képletet a B6 cellába helyeztük, és összesen négy napra értékeltük az értékesítési adatokat.

A következő lépés az SUM OFFSET képlet áthelyezése egy sorra, hogy helyet biztosítsunk az ötödik napi értékesítésnek. Ezt egy új 6 sor beillesztésével érheti el, amely a képletet a 7. sorra mozgatja.

Az áthelyezés eredményeként az Excel automatikusan frissíti a Referencia argumentumot a cellábaB7 és hozzáadja a sejtetB6 a képlet szerint összegezve.

A SUM OFFSET képlet beírása

1. Kattintson a B6 cellára, ahol a képlet eredményei kezdetben megjelennek.
2. Kattintson aképletek a szalag menü lapja.
3. VálasztMath & Triga szalagról a funkció legördülő listájának megnyitásához.
4. KattintsonÖSSZEG a listában a funkció párbeszédpaneljének megjelenítéséhez.
5. A párbeszédpanelen kattintson aszám1vonal.
6. Kattintson a cellára B2 a cella hivatkozásának beírása a párbeszédpanelbe. Ez a hely a képlet statikus végpontja.
7. A párbeszédpanelen kattintson a2. számúvonal.
8. Adja meg a következő OFFSET funkciót: OFFSET (B6, -1,0) hogy létrehozza a képlet dinamikus végpontját.
9. Kattints rendben a funkció befejezéséhez és a párbeszédpanel bezárásához.

A teljes megjelenik a B7 cellában.

A következő nap értékesítési adatainak hozzáadása

A következő napi értékesítési adatok hozzáadásához:

1. Kattintson a jobb egérgombbal a sor 6. fejlécére a helyi menü megnyitásához.
2. A menüben kattintson a gombraInsert új sor beillesztése a munkalapba.
3. Ennek eredményeként a SUM OFFSET formula lefelé halad a B7 cellához, és a 6. sor üres.
4. Kattintson a cellára A6.
5. Írja be a számot5 jelezve, hogy az ötödik nap értékesítési értéke be van jegyezve.
6. Kattintson a B6 cellára.
7. Írja be a számot$1458.25 és nyomja meg a gombotBelép gombot a billentyűzeten.

A B7 cellák frissítik az új$7137.40.

Amikor rákattint a B7 cellára, a frissített képletet= SUM (B2: OFFSET (B7, -1,0)) megjelenik a képlet sávban.

Az OFFSET funkció két opcionális argumentumot tartalmaz: magasság és szélesség , amelyeket ebben a példában nem használtak.

Ezek az érvek használhatók arra, hogy az OFFSET függvénynek a kimenet alakját mondják el oly sok sor magas és sok oszlop széles körében.

Ezeknek az érveknek a kihagyásával a függvény alapértelmezés szerint a Referencia argumentum magasságát és szélességét használja, amely ebben a példában egy sor magas és egy oszlop széles.