Excel SUM és OFFSET képlet

Használja a SUM és OFFSET értékeket az adatok dinamikus tartományainak eléréséhez

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 forgalmát -, akkor az OFFSET funkció lehetővé teszi, hogy dinamikus tartományt állítson be, amely folyamatosan változik, ahogy az egyes napi értékesítési adatok hozzáadásra kerülnek.

Önmagában az SUM funkció általában új adatcellákat tud befogadni, amelyek beillesztésre kerülnek 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 cikkhez mellékelt példaképen az egyes napi új értékesítési adatok kerülnek a lista aljára, ami arra készteti az összeget, hogy minden egyes új adat hozzáadásakor folyamatosan váltson le egy cellát.

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

Tekintse meg a jelen cikkhez tartozó képet, hogy kövesse ezt a bemutató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 - (szükséges) a SUM funkcióval megegyező cellák tartományának kezdőpontja. A példánkban ez a B2 cellát jelenti.

Referencia - (szükséges) a tartomány végpontjának kiszámításához használt referenciaérték, amely 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énk, hogy a tartomány véget vessen a képlet feletti cellának.

Sorok - (szükség van) az Offset számításakor használt Referencia argumentum felett vagy alatt található sorok számát. Ez az érték lehet pozitív, negatív vagy nullára állítva.

Ha az eltolás helye a Referencia- argumentum felett van, akkor ez az érték negatív. Ha ez alatta marad, a sorok érve pozitív. Ha az eltolás azonos sorban található, akkor ez 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 - (szükséges) a referencia argumentum bal vagy jobb oldalán található oszlopok számát az offset kiszámításához. 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 B7 cellához, és hozzáadja a B6 cellát a képlet által összegyűjtött tartományhoz.

A SUM OFFSET képlet beírása

1. Kattintson a B6 cellára, ahol a képlet eredményei kezdetben megjelennek.
2. Kattintson a szalag menü Formulák lapjára.
3. Válassza a Math & Trig lehetőséget a szalagból a funkció legördülő listájának megnyitásához.
4. A függvény párbeszédablakának megjelenítéséhez kattintson a SUM gombra a listában.
5. A párbeszédpanelen kattintson a Number1 sorra.
6. Kattintson a B2 cellára, hogy beírja ezt a cella hivatkozást a párbeszédpanelbe. Ez a hely a képlet statikus végpontja;
7. A párbeszédpanelen kattintson a Number2 sorra.
8. Adja meg a következő OFFSET funkciót: OFFSET (B6, -1,0), hogy a képlet dinamikus végpontját hozzon létre.
9. A funkció befejezéséhez és a párbeszédpanel bezárásához kattintson az OK gombra .

A teljes $ 5679.15 megjelenik a B7 cellában.

Amikor a B3 cellára kattint, a teljes függvény = SUM (B2: OFFSET (B6, -1,0)) megjelenik a munkalap feletti képletsávban.

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 Beszúrás gombra egy új sor beszúrása a munkalapba.
3. Ennek eredményeként a SUM OFFSET formula lefelé halad a B7 cellához, és a 6. sor üres.
4. Kattintson az A6 cellára.
5. Adja meg az 5-ös számot, 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 1458,25 dollár értéket, és nyomja meg az Enter gombot a billentyűzeten.

A Cell B7 frissíti az új, összesen 7137,40 dollárt.

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

Megjegyzés : Az OFFSET funkció két opcionális argumentumot tartalmaz: Magasság és Szélesség, amelyeket ebben a példában elhagytunk.

Ezeket az érveket használhatjuk arra, hogy az OFFSET függvényt a kimenet alakjára mondjuk, mivel annyi sor magas és sok oszlop széles.

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.