A Microsoft Excel-nek van néhány hűvös trükkje, és a SUM és az INDIRECT dinamikus tartományt alkalmazó formulák csak két módon tudják manipulálni az adatokat.
SUM - KÖZVETETT Formula áttekintése
Az INDIRECT funkció Excel formátumban történő használata megkönnyíti a képletben használt sejt-referenciák tartományának módosítását anélkül, hogy maga a képletet kellene módosítania.
Az INDIRECT számos olyan funkcióval használható, amelyek elfogadják a cellahivatkozást, mint az OFFSET és SUM funkciók.
Az utóbbi esetben az INDIRECT mint az SUM funkció argumentuma az SUM függvény függvényének dinamikus tartományát hozhatja létre.
Az INDIRECT ezt a cellák adatait közvetve közvetett helyen keresztül utalva érti.
Példa: SUM - INDIRECT A dinamikus értéktartományhoz tartozó képlet
Ez a példa a fenti képen látható adatokon alapul.
A SUM - INDIRECT formula , amelyet az alábbi bemutató lépésekkel hozhatunk létre:
= SUM (INDIRECT ("D" & E1 & ": D" & E2))
Ebben a képletben a beágyazott INDIRECT funkció argumentuma az E1 és E2 cellákra való hivatkozást tartalmazza. Az 1-es és 4-es cellában lévő számok, az INDIRECT érvelésének többi részével együtt, a D1 és D4 sejt-referenciákat alkotják.
Ennek eredményeképpen a SUM funkcióval számolt számok tartománya a D1-D4 cellák tartományában található - azaz 50-nél.
Az E1 és E2 cellákban található számok megváltoztatásával; azonban a teljes értéktartomány könnyen megváltoztatható.
Ez a példa először használja a fenti képletet, hogy összeadja a D1: D4 cellák adatait, majd változtassa meg az összegzett tartományt D3: D6-ra anélkül, hogy a képletet az F1 cellában szerkesztené.
01. 03. sz
Belépés a képletbe - Opciók
A képlet bevitelének lehetőségei:
- a fenti képletet közvetlenül az F1 cellába írja be, és megnyomja az Enter billentyűt a billentyűzeten
- az INDIRECT függvény argumentumként a SUM funkció párbeszédpaneljén keresztül
Az Excel legtöbb funkciójának van egy párbeszédablakja, amely lehetővé teszi, hogy a függvény minden argumentumát külön vonalba írja anélkül, hogy aggódnia kellene a szintaxis miatt .
Ebben az esetben a SUM függvény párbeszédpanelénél a képlet egy bizonyos mértékig egyszerűsíthető. Mivel az INDIRECT funkció beágyazódik a SUM belül, az INDIRECT funkciót és argumentumait manuálisan kell megadni.
Az alábbi lépések a SUM párbeszédablak használatával adják meg a képletet.
A bemutatóadatok beírása
Cell adat D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4- Adja meg a következő adatokat a D1-E2 cellákba
A SUM - INDIRECT formula elindítása - A SUM funkció párbeszédpanel megnyitása
- Kattintson az F1 cellára - itt jelenik meg a példa eredményei
- Kattintson a szalag menü Formulák lapjára
- Válassza a Math & Trig lehetőséget a szalagból a funkció legördülő listájának megnyitásához
- A függvény párbeszédablakának megnyitásához kattintson a SUM gombra a listában
02. 03. sz
Az INDIRECT funkció beírása - Kattintson a Nagyobb kép megtekintéséhez
Az INDIRECT képletet kell megadni az SUM függvény argumentumaként.
Beágyazott függvények esetén az Excel nem engedélyezi a második függvény párbeszédablakának megnyitását az argumentumok beadásához.
Ezért az INDIRECT funkciót manuálisan kell beírni az SUM függvény párbeszédpanelének 1. sorában.
- A párbeszédpanelen kattintson a Number1 sorra
- Adja meg az alábbi INDIRECT funkciót: INDIRECT ("D" & E1 & ": D" & E2)
- A funkció befejezéséhez és a párbeszédpanel bezárásához kattintson az OK gombra
- Az 50-es számnak meg kell jelennie az F1 cellában, mivel ez a D1-D4 cellákban található adatok összessége
- Amikor az F1 cellára kattint, a teljes képlet = SUM (INDIRECT ("D" & E1 & ": D" & E2)) jelenik meg a munkalapon található képletsávban
Az INDIRECT funkció lebontása
Annak érdekében, hogy a D oszlopban az INDIRECT segítségével dinamikus tartományt hozzunk létre, a D betűt az INDIRECT függvény argumentumában egyesíteni kell az E1 és E2 cellák számával.
Ez a következőképpen valósul meg:
- az ampersand ( & ) a szöveges adatok (ebben az esetben a D betű) cella hivatkozással (E1 és E2)
- Továbbá a sejtes hivatkozásokkal összefűzött szövegadatokat kettős idézőjelekkel ( "" ) kell körülvévezni,
- végül a tartomány végpontjait a kettőspont választja el (:)
Ezért a tartomány kezdőpontját a "D" és E1 karakterek határozzák meg.
A második karakterkészlet: ": D" és E2 kombinálja a kettőspontot a végponttal. Ez azért van így, mert a kettőspont szöveges karakter, ezért be kell illeszteni az idézőjelbe.
A harmadik amper és középen a két részt egy érvbe illesztik össze:
"D" & E1 & ": D" & E203. 03. sz
A SUM függvény tartományának dinamikus megváltoztatása
Ennek a képletnek az egész pontja az, hogy könnyebbé válik a SUM funkcióval megegyező tartomány megváltoztatása anélkül, hogy módosítaná a függvény argumentumait.
Az INDIRECT függvénynek a képletben történő bevitelével az E1 és E2 cellákban lévő számok megváltoztatása megváltoztatja a SUM funkcióval leolvasott cellák tartományát.
Amint az a fenti képen látható, ez azt is eredményezi, hogy az F1 cellában található formula választja, mivel az új adatkészletet tartalmazza.
- Kattintson az E1 cellára
- Írja be a 3. számot
- Nyomja meg az Enter gombot a billentyűzeten
- Kattintson az E2 cellára
- Írja be a 6. számot
- Nyomja meg az Enter gombot a billentyűzeten
- A válasz az F1 cellában 90-re változik - ami a D3-D6 cellákban található számok összértéke
- A képletet úgy is teszteljük, hogy a B1 és B2 cellák tartalmát 1 és 6 közötti számra változtatjuk
INDIRECT és a #REF! Hibaérték
A bíró! hibaérték jelenik meg az F1 cellában, ha az INDIRECT funkció argumentuma:
- nem érvényes cellahivatkozás
- tartalmaz egy külső hivatkozást egy másik munkafüzetre, és a munkafüzet nyitva van
- a munkalap határértékein kívül eső cellatartományra utal (1 048 576 sor vagy XFD oszlop)