tartalom
Gratulálunk! Eljutottál a maraton utolsó napjáig 30 Excel funkció 30 nap alatt. Hosszú és érdekes utazás volt, melynek során sok hasznos dolgot tanult meg az Excel funkcióiról.
A maraton 30. napján a funkció tanulmányozását szenteljük KÖZVETETT (INDIRECT), amely a szöveges karakterlánc által megadott hivatkozást adja vissza. Ezzel a funkcióval függő legördülő listákat hozhat létre. Például, amikor kiválaszt egy országot a legördülő listából, meghatározza, hogy mely lehetőségek jelenjenek meg a város legördülő listában.
Tehát nézzük meg közelebbről a függvény elméleti részét KÖZVETETT (KÖZVETETT) és keressen gyakorlati példákat az alkalmazására. Ha további információi vagy példái vannak, kérjük, ossza meg őket a megjegyzésekben.
30. funkció: KÖZVETETT
Funkció KÖZVETETT (INDIRECT) a szöveges karakterlánc által megadott hivatkozást adja vissza.
Hogyan használhatja az INDIRECT funkciót?
Mivel a funkció KÖZVETETT (INDIRECT) egy szöveges karakterlánc által adott hivatkozást ad vissza, használhatja:
- Hozzon létre egy nem eltolható kezdeti hivatkozást.
- Hozzon létre hivatkozást egy statikus nevű tartományra.
- Hozzon létre hivatkozást a lap-, sor- és oszlopinformációkkal.
- Hozzon létre egy nem eltolható számtömböt.
Szintaxis INDIRECT (INDIRECT)
Funkció KÖZVETETT (INDIRECT) a következő szintaxissal rendelkezik:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- ref_text (link_cellához) a hivatkozás szövege.
- a1 – ha egyenlő IGAZ (IGAZ) vagy nincs megadva, akkor a hivatkozás stílusa kerül felhasználásra A1; és ha FALSE (FALSE), akkor a stílus R1C1.
Csapdák KÖZVETETT (INDIRECT)
- Funkció KÖZVETETT (INDIRECT) újraszámításra kerül, amikor az Excel munkalap értékei megváltoznak. Ez nagymértékben lelassíthatja a munkafüzetet, ha a függvényt sok képletben használják.
- Ha a funkció KÖZVETETT (INDIRECT) hivatkozást hoz létre egy másik Excel-munkafüzetre, ennek a munkafüzetnek nyitva kell lennie, különben a képlet hibát jelez #REF! (#LINK!).
- Ha a funkció KÖZVETETT (INDIRECT) olyan tartományra hivatkozik, amely meghaladja a sor- és oszlopkorlátot, a képlet hibát jelez #REF! (#LINK!).
- Funkció KÖZVETETT (INDIRECT) nem hivatkozhat dinamikus nevű tartományra.
1. példa: Hozzon létre egy nem eltolható kezdeti hivatkozást
Az első példában a C és E oszlopok ugyanazokat a számokat tartalmazzák, ezek összegét a függvény segítségével számoltuk ki ÖSSZEG (SUM) is ugyanaz. A képletek azonban kissé eltérnek. A C8 cellában a képlet a következő:
=SUM(C2:C7)
=СУММ(C2:C7)
Az E8 cellában a függvény KÖZVETETT (INDIRECT) hivatkozást hoz létre a kezdő E2 cellára:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
Ha beszúr egy sort a lap tetejére, és hozzáadja a januári (januári) értéket, akkor a C oszlopban szereplő összeg nem változik. A képlet megváltozik egy sor hozzáadására reagálva:
=SUM(C3:C8)
=СУММ(C3:C8)
Azonban a funkció KÖZVETETT (INDIRECT) az E2-t rögzíti kezdőcellaként, így a január automatikusan bekerül az E oszlop összegeinek kiszámításába. A végcella megváltozott, de a kezdőcella nem változott.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
2. példa: Hivatkozás egy statikus nevű tartományra
Funkció KÖZVETETT (INDIRECT) hivatkozást hozhat létre egy elnevezett tartományra. Ebben a példában a kék cellák alkotják a tartományt NumList. Ezenkívül a B oszlopban szereplő értékekből dinamikus tartomány is létrejön NumListDyn, az ebben az oszlopban lévő számok számától függően.
Mindkét tartomány összege úgy számítható ki, hogy egyszerűen megadja a függvény nevét argumentumként ÖSSZEG (SUM), ahogy az E3 és E4 cellákban is látható.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
Ahelyett, hogy egy tartománynevet írna be egy függvénybe ÖSSZEG (SUM), A munkalap egyik cellájába írt névre hivatkozhat. Például, ha a név NumList a D7 cellába van írva, akkor az E7 cellában a képlet a következő lesz:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
Sajnos a funkció KÖZVETETT (KÖZVETETT) nem tud dinamikus tartomány hivatkozást létrehozni, ezért amikor lemásolja ezt a képletet az E8 cellába, hibaüzenetet fog kapni #REF! (#LINK!).
3. példa: Hozzon létre egy hivatkozást lap-, sor- és oszlopinformációkkal
Könnyen létrehozhat hivatkozást a sor- és oszlopszámok alapján, valamint a FALSE (FALSE) érték használatával a második függvény argumentumként. KÖZVETETT (KÖZVETETT). Így jön létre a stílushivatkozás R1C1. Ebben a példában a hivatkozáshoz hozzáadtuk a munkalap nevét – 'MyLinks'!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
4. példa: Hozzon létre egy nem eltolható számtömböt
Néha számtömböt kell használni az Excel képletekben. A következő példában a B oszlopban lévő 3 legnagyobb számot szeretnénk átlagolni. A számok beírhatók egy képletbe, ahogy az a D4 cellában történik:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
Ha nagyobb tömbre van szüksége, akkor valószínűleg nem akarja beírni az összes számot a képletbe. A második lehetőség a funkció használata SOR (ROW), ahogy a D5 cellába beírt tömbképletben történik:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
A harmadik lehetőség a függvény használata SOR (Bepaliz vkit vmivel KÖZVETETT (KÖZVETETT), amint azt a D6 cellában lévő tömbképlettel tesszük:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
Az eredmény mind a 3 képletre ugyanaz lesz:
Ha azonban sorokat szúr be a lap tetejére, a második képlet hibás eredményt ad vissza, mivel a képlet hivatkozásai a soreltolással együtt megváltoznak. Most a három legnagyobb szám átlaga helyett a képlet a 3., 4. és 5. legnagyobb szám átlagát adja vissza.
Funkciók használata KÖZVETETT (INDIRECT) esetén a harmadik képlet megtartja a helyes sorhivatkozásokat, és továbbra is a helyes eredményt mutatja.