Excel Trick egy változó tartományú cellák számának összegzéséhez

Az Excel programban összegezheti a sejtek számát egy változó tartományt használva az INDIRECT funkcióval. Az INDIRECT funkció automatikusan frissíti a hivatkozott cellák tartományát anélkül, hogy maga a képletet kézzel szerkesztené. Használhatja az INDIRECT funkciót számos Excel funkcióval, de a leggyakoribb (és hasznos) az SUM funkció használata.

jegyzet: A cikkben szereplő információk a 2019, 2016, 2013, 2010 és Excel for Mac verziókra vonatkoznak.

01/05

A cellák számának összegzése változó tartomány használatával

Beágyazhatja az INDIRECT függvényt a SUM függvényen belüli argumentumként, hogy létrehozhassa a SUM függvény funkciójának változó tartományát. Az INDIRECT funkció ezt úgy végzi el, hogy közvetett módon, közvetett cellahivatkozáson keresztül referenciálja a cellák tartományát.

Az együttesen használt funkciók formátuma így néz ki:

= SUM (közvetett ( "D1": D4)

Ez megakadályozza, hogy a tartomány a D1-ben kezdődjön, és lehetővé teszi a D4 megváltoztatását, ha behelyezi vagy törli a D oszlopban lévő cellákat.

A cellahivatkozások módosításához a táblázatban más cellákat is használhat. Ha például az E1-et használja a tartomány első cellájának és az E2-nek a tartomány utolsó cellájára történő hivatkozására, akkor a képlet így néz ki:

= SUM (INDIRECT ("D" & E1 & ": D" & E2))

Az E1 és E2 cellákban található számok megváltoztatásával módosíthatja a képlet tartományát a képlet manuális szerkesztése nélkül.

02. 05. sz

Próbálja ki a SUM és az INDIRECT funkciókat

Hozzon létre egy táblázatot a SUM és az INDIRECT függvények teszteléséhez. Kezdje el egy üres táblázat létrehozásával, és írja be a következő adatokat a D és E oszlopba:

Sejt Adat D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4

Ezután hozzon létre egy képletet az F1 cellában. Itt van, hogyan:

Cella kiválasztása F1. Itt jelenik meg a példa eredménye.

választ képletek.

Választ Math & Trig a funkció legördülő listájának megnyitásához.

választÖSSZEG a listában megnyithatja a SUM függvény argumentumokat párbeszédpanelt.

03. oldal, 05. o
Ürítse az INDIRECT funkciót a SUM funkcióba

Ezután írja be az INDIRECT funkciót a SUM funkcióba ezzel a párbeszédablakkal.

Ban,-ben szám1 mezőbe írja be az alábbi INDIRECT funkciót:
KÖZVETETT ( "D" és E1 & ": D" & E2)

választ rendben a funkció befejezéséhez és a párbeszédpanel bezárásához.

Az 50-es szám az F1 cellában jelenik meg. Ez a D1-D4 sejtek összege.

Amikor kiválasztja az F1 cellát, a képlet mező a következőket jeleníti meg:
= SUM (közvetett ( "D" és E1 & ": D" & E2))

Ha megértette, hogyan kell formázni a SUM és a beágyazott INDIRECT funkciókat, akkor a 4. lépésben bemutatott teljes funkciót közvetlenül beírhatja az eredménycellába (ebben a példában az F1 cellában).
04. 05. sz
Az indirekt függvény létrehozása

A fenti példában az E1 és E2 cellákra vonatkozó hivatkozások az idézőjeleken kívül vannak. Ez, a & összekapcsolja a szimbólumot, beágyazza az E1 és E2 sejtek belsejébe beágyazott tartalmat magába a képletbe.

Így működnek a képlet elemei:

& a szöveges adatok (ebben az esetben a D betű) összekapcsolására egy számértéket tartalmazó (E1 és E2) cellahivatkozással.

' ' a szöveges adatoknak a cellahivatkozásokon belül kell elhelyezkedniük (ebben a példában a D betű).

: az elválasztó az első cellától és az SUM tartomány utolsó cellájától.

A fenti példában a tartomány kezdőpontját a karakterek határozzák meg: "D" & E1.

A tartomány végpontját a karakterek határozzák meg: ": D" & E2

A kettőspontot be kell illeszteni az idézőjelek közé.

A középső harmadik és a középső szegmenst összekapcsolják a két szegmens egyetlen érvével:

"D" & E1 & ": D" & E2 05. 05
A SUM függvény változó tartománya

A SUM funkció belsejében elhelyezett INDIRECT funkció egyszerűvé teszi a SUM funkcióval megegyező tartomány kezdetét és végét, anélkül, hogy maga a funkciót szerkesztené.

A 3. lépésben alkalmazott összefűzés technikával az E1 és E2 cellákban tárolt számokat használhatja a táblázatban a SUM funkcióhoz tartozó cellák tartományának megváltoztatásához.

Tesztelje ezt a saját táblázatban az E1 és E2 cellák módosításával, és nézze meg, hogyan változik a SUM funkció eredményei.

Cella kiválasztása E1.

Írja be a számot 3.

nyomja meg Belép.

Cella kiválasztása E2.

Írja be a számot 6.

nyomja meg Belép.

Az F1 cellában a válasz 90-re változik. Ez a D3-D6 cellákban lévő számok összege.

Ahhoz, hogy az INDIRECT funkció működés közben megjelenjen, helyezzen be egy új cellát a D3 cellába. Ez lecsúszik az összes többi sejtet. Az új összeg a D3-D7 cellák összes száma. Az INDIRECT funkció dinamikusan megváltoztatja a teljes tartományt, hogy tartalmazza az eredetileg bevitt teljes listát, valamint a beillesztett új cellát is.

A bíró! Hibaérték

A bíró! hibaérték jelenik meg az F1 cellában, ha az INDIRECT funkció:

Érvénytelen cellahivatkozást tartalmaz.

Külső utalást tartalmaz egy másik munkalapra, amely nem létezik.

A munkalap határain kívül eső cellatartományra utal.

Egy üres cellára utal.