Töltse le a példa munkafüzetet
Ez a bemutató bemutatja, hogyan kell használni Excel INDIRECT függvény Excelben cellahivatkozás létrehozásához a szövegből.
KÖZVETLEN Funkció áttekintés
A KÖZVETLEN függvény Cellahivatkozást hoz létre szöveges karakterláncból.
(Figyelje meg, hogyan jelennek meg a képletbemenetek)
INDIRECT funkció Szintaxis és bemenetek:
| 1 | = KÖZVETLEN (ref_text, C1) |
ref_text - Cellahivatkozást vagy tartományhivatkozást képviselő karakterlánc. A karakterlánc lehet R1C1 vagy A1 formátumú, vagy nevezett tartomány is lehet.
a1 - Választható: Azt jelzi, hogy a hivatkozás R1C1 vagy A1 formátumban van -e. HAMIS az R1C1 esetében vagy IGAZ / elhagyva az A1 esetében.
Mi az INDIRECT funkció?
Az INDIRECT funkció lehetővé teszi, hogy szöveges karakterláncot adjon, és a számítógép tényleges hivatkozásként értelmezze ezt a karakterláncot. Ezzel hivatkozhat egy tartományra ugyanazon a lapon, egy másik lapon vagy akár egy másik munkafüzeten.
FIGYELEM: A KÖZVETLEN funkció az egyik illékony funkció. A táblázatkezelés legtöbbször a számítógép csak akkor számítja újra a képletet, ha a bemenetek megváltoztatták értékeiket. Az illékony függvény azonban újraszámítja minden amikor megváltoztatja bármelyik cellát. Óvatosan kell eljárni annak biztosítása érdekében, hogy ne okozzon hosszú újraszámítási időt az illékony funkció túlzott használata vagy az illékony funkció eredményétől függő sok cella miatt.
Cellahivatkozás létrehozása
Tegyük fel, hogy szeretné lekérni az értéket az A2 -ből, de győződjön meg arról, hogy a képlet marad az A2 -n, függetlenül az új sorok beillesztésétől/eltávolításától. Írhat egy képletet
| 1 | = KÖZVETLEN ("A2") |
Vegye figyelembe, hogy a függvényünkben szereplő argumentum az „A2” szöveges karakterlánc, és nem cellahivatkozás. Továbbá, mivel ez szöveges karakterlánc, nincs szükség abszolút hivatkozásra, például $ A $ 2. A szöveg soha nem fog változni, és ez a képlet mindig az A2 -re mutat, függetlenül attól, hogy hova kerül.
KÖZVETLEN sorszám
A szöveges karakterláncokat és értékeket a cellákból összefűzheti. Ahelyett, hogy „A2 -t” írnánk, mint korábban, kiolvashatunk egy számértéket a B2 cellából, és ezt használhatjuk a képletünkben. Ilyen formulát írnánk ki
| 1 | = KÖZVETLEN ("A" és B2) |
Itt az „&” szimbólumot használjuk az „A” szöveges karakterlánc összekapcsolására a B2 cella értékével. Tehát, ha a B2 értéke jelenleg 10, akkor a képletünk így nézne ki
| 123 | = KÖZVETLEN ("A" és 10)= KÖZVETLEN ("A10")= A10 |
KÖZVETLEN oszlopérték
Az oszlophivatkozásban is összefűzheti. Ezúttal tegyük fel, hogy tudjuk, hogy a 10. sorból szeretnénk megragadni egy értéket, de szeretnénk megváltoztatni, hogy melyik oszlopból kell kihúzni. A kívánt oszlopbetűt a B2 cellába tesszük. A képletünk így nézhet ki
| 1 | = KÖZVETLEN (B2 és "10") |
Ha a B2 értéke „G”, akkor a képletünk így értékel
| 123 | = KÖZVETLEN ("G" és 10)= KÖZVETLEN ("G10")= G10 |
KÖZVETLEN r1c1 stílus
Korábbi példánkban egy betűt kellett használnunk az oszlophivatkozás jelzésére. Ennek oka az, hogy az úgynevezett A1 stílusú hivatkozást használtuk. A1 stílusban az oszlopokat betű, a sorokat számok adják meg. Az abszolút hivatkozásokat a „$” jelzi, mielőtt abszolútnak szeretnénk maradni.
Az r1c1 -ben a sorok és az oszlopok is a szám használatával kezdődnek. Az ab1 abszolút hivatkozását így írnánk
| 1 | = R1C1 |
Ezt úgy olvashatja, mint „1. sor, 1. oszlop”. A relatív hivatkozásokat zárójelben adjuk meg, de a szám a pozíciót jelzi képletű sejthez viszonyítva. Tehát, ha képletet írnánk az A10 cellába, és hivatkoznunk kell az A1 -re, akkor a képletet írnánk
| 1 | = R [-9] C |
Ezt a következőképpen olvashatja: „A 9 -es cella felfelé halad, de ugyanabban az oszlopban.
Ez azért lehet hasznos, mert az INDIRECT támogatja az r1c1 jelölés használatát. Tekintsük az előző példát, amikor egy értéket szereztünk be a 10. sorból, de meg akartuk változtatni az oszlopot. Ahelyett, hogy levelet adnánk, tegyük fel, hogy számot teszünk a B2 cellába. A képletünk így nézhet ki
| 1 | = KÖZVETLEN ("R10C" és B2, HAMIS) |
Kihagytuk a 2nd vita eddig. Ha ezt az argumentumot kihagyja vagy igaz, akkor a függvény A1 stílust használ. Mivel hamis, az r1c1 -ben fogja értékelni. Tegyük fel, hogy a B2 értéke 5. A képletünk ezt így fogja értékelni
| 12 | = KÖZVETLEN ("R10C5", HAMIS)= $ 10 E $ |
KÖZVETLEN különbségek A1 és r1c1 között
Ne feledje, hogy korábban megmutattuk, hogy mivel a képlet tartalma szöveges karakterlánc volt, soha nem változott?
| 1 | = KÖZVETLEN ("A2") |
Ez a képlet mindig az A2 cellát fogja nézni, függetlenül attól, hogy hová helyezi a képletet. Az r1c1 -ben, mivel zárójelek segítségével jelezheti a relatív pozíciót, ez a szabály nem marad következetes. Ha ezt a képletet a B2 cellába helyezi
| 1 | = KÖZVETLEN ("RC [-1]") |
Az A2 cellát fogja nézni (mivel az A oszlop a B oszlop bal oldalán található). Ha ezt a képletet a B3 cellába másolja, a benne lévő szöveg ugyanaz marad, de a KÖZVETLEN most az A3 cellát nézi.
KÖZVETLEN a lap nevével
A lap nevét is kombinálhatja a KÖZVETLEN hivatkozásokba. Fontos megjegyezni, hogy a nevek körül egyetlen idézőjelet kell elhelyezni, és a lap nevét a cellahivatkozástól felkiáltójellel kell elválasztani.
Tegyük fel, hogy megvolt ez a beállítás, ahol megadjuk a lap nevét, sorát és oszlopát.
Mindezeket referenciává egyesítő képletünk így nézne ki:
| 1 | = KÖZVETLEN ("" "& A2 &" '! "& B2 & C2) |
A képletünket a következőképpen értékeljük:
| 123 | = KÖZVETLEN ("" "&" Sheet2 "&" '! "&" B "&" 5 ")= KÖZVETLEN ("" "Sheet2 '! B5")= 'Lap2'! B5 |
Technikailag, mivel a „Sheet2” szónak nincsenek szóközei, nekünk nincs szükség az egyetlen idézőjelet. Teljesen jogos ilyesmit írni
| 1 | = 2. lap! A2 |
Azonban nem árt az idézőjelek elhelyezése, amikor nincs rájuk szükség. A legjobb gyakorlat az, ha ezeket felveszi, így a képlet képes kezelni a példányokat ott, ahol szükség lehet rájuk.
KÖZVETLEN egy másik munkafüzethez
Azt is megemlítjük, hogy az INDIRECT hivatkozást hozhat létre egy másik munkafüzetre. A korlátozás az, hogy az INDIRECT nem fogja lekérni az értékeket egy zárt munkafüzetből, ezért ennek a konkrét felhasználásnak korlátozott a praktikussága. Ha az INDIRECT munkafüzet nincs megnyitva, akkor a függvény „#REF!” Jelzést dob hiba.
A szintaxis a munkafüzet nevének írásakor az, hogy szögletes zárójelben kell lennie. Használjuk ezt a beállítást, és próbáljunk meg lekérni egy értéket a C7 cellából.
A képletünk az lenne
| 1 | = KÖZVETLEN ("'[" & A2 & "]" & B2 & "'! C7") |
Ismét figyeljen az idézőjelek, zárójelek és felkiáltójel elhelyezésére. A képletünket a következőképpen értékeljük:
| 123 | = KÖZVETLEN ("'[" & "Sample.xlsx" & "]" & "Összefoglaló" & "'! C7")= KÖZVETLEN ("'[[Minta.xslx] Összefoglaló'! C7")= '[Minta.xlsx] Összefoglalás'! C7 |
KÖZVETLEN dinamikus tartomány létrehozásához
Ha nagy adathalmaza van, fontos, hogy megpróbálja optimalizálni a képleteket, hogy ne végezzenek több munkát a szükségesnél. Például ahelyett, hogy az A oszlop egészére hivatkoznánk, érdemes csak hivatkoznunk a listánkban lévő cellák pontos számára. Vegye figyelembe a következő elrendezést:
A B2 cellában elhelyeztük a képletet
| 1 | = COUNTA (A: A) |
A COUNTA függvényt a számítógép nagyon könnyen kiszámítja, mivel egyszerűen ellenőrzi, hogy az A oszlop hány cellája rendelkezik valamilyen értékkel, szemben a logikai ellenőrzések vagy matematikai műveletek elvégzésével.
Most építsük fel a képletünket, amely összegzi az A oszlop értékeit, de szeretnénk biztosítani, hogy csak a pontos tartományt nézze meg az értékekkel (A2: A5). A képletet így írjuk le
| 1 | = SUM (KÖZVETLEN ("A2: A" és B2)) |
INDIRECT -ünk az 5 -ös számot fogja ki a B2 cellából, és hivatkozást hoz létre az A2: A5 tartományra. A SUM ezt a tartományt használhatja a számításhoz. Ha hozzáadunk egy másik értéket az A6 cellához, akkor a B2-ben lévő szám frissül, és a SUM képletünk is automatikusan frissül, hogy tartalmazza ezt az új értéket.
FIGYELMEZTETÉS: Az Office 2007 -ben a Táblázatok bevezetésével sokkal hatékonyabb, ha adatait táblázatban tárolja, és strukturális hivatkozást használ, ahelyett, hogy az INDIRECT ingatag jellege miatt a példát használnánk. Előfordulhatnak azonban olyan esetek, amikor létre kell hoznia az elemek listáját, és nem használhat táblázatot.
Dinamikus ábrázolás INDIRECT segítségével
Vegyük az előző példát, és lépjünk még egy lépést. Ahelyett, hogy olyan képletet írnánk, amely megadja az értékek összegét, létrehozunk egy elnevezett tartományt. Ezt a tartományt nevezhetjük „MyData” -nak, és hivatkozhatunk rá
| 1 | = KÖZVETLEN ("A2: A" & COUNTA ($ A: $ A)) |
Ne feledje, hogy mivel ezt egy elnevezett tartományba tesszük, a hivatkozást B2 -re cseréltük, és helyette a COUNTA függvényt helyeztük el.
Most, hogy megvan ez a megnevezett tartomány, használhatjuk egy diagramban. Létrehozunk egy üres vonaldiagramot, majd adatsort adunk hozzá. A sorozatértékekhez írhat valami hasonlót
| 1 | = 1. lap! MyData |
A diagram most ezt a hivatkozást fogja használni a diagramértékekhez. Ahogy az A oszlopba további értékek kerülnek, az INDIRECT egyre nagyobb tartományra fog utalni, és a diagramunk folyamatosan frissül az összes újonnan hozzáadott értékkel.
Dinamikus adatellenőrzés INDIRECT segítségével
Amikor összegyűjti a felhasználók véleményét, néha szükség van arra, hogy egy választás közül választhasson a korábbi választástól függően. Tekintsük ezt az elrendezést, ahol az első oszlop lehetővé teszi a felhasználók számára, hogy gyümölcsök, zöldségek és húsok közül válasszanak.
A 2 -bennd oszlopban, nem szeretnénk, ha minden lehetséges választást bemutató nagy lista lenne, mivel már kicsit szűkítettük a dolgokat. Így létrehoztunk még 3 listát, amelyek így néznek ki:
Ezután mindegyiket hozzárendeljük ezek névsorba sorolja. Azaz minden gyümölcs a „Gyümölcsök”, a zöldségek pedig a „Zöldség” stb.
A táblázatunkban készen állunk az adatellenőrzés beállítására a 2 -bennd oszlop. Létrehozunk egy listatípust, a következő bemenettel:
| 1 | = KÖZVETLEN (A2) |
A KÖZVETLEN olvassa be az A oszlopban tett választást, és nézze meg egy kategória nevét. Ezekkel a nevekkel határoztunk meg tartományokat, ezért az INDIRECT ezt a nevet veszi fel, és létrehoz egy hivatkozást a kívánt tartományra.
További megjegyzések
Az INDIRECT függvény használatával cellahivatkozást hozhat létre a szövegből.
Először hozza létre a szöveges karakterláncot, amely cellahivatkozást képvisel. A karakterláncnak vagy a szokásos A1-stílusú oszlop betűjében és sorszámában (M37), vagy R1C1 stílusban (R37C13) kell szerepelnie. A hivatkozást közvetlenül is beírhatja, de általában hivatkozik a sorokat és oszlopokat meghatározó cellákra. Végül adja meg, hogy melyik cellahivatkozási formátumot választja. IGAZ vagy elhagyva az A1-stílusú referenciához, vagy HAMIS az R1C1-stílushoz.
A KÖZVETLEN képletekkel való munka során érdemes használni a Sor funkció hogy megkapja a hivatkozás sorszámát vagy a Oszlop funkció hogy megkapja a hivatkozás oszlopszámát (nem betűje).
Térjen vissza az Excel összes funkciójának listájához
KÖZVETETT a Google Táblázatokban
Az INDIRECT funkció pontosan ugyanúgy működik a Google Táblázatokban, mint az Excelben:
