INDIRECT funkció az Excelben
Az indirekt függvény az excelben egy beépített függvény, amelyet a szövegértékek referenciájára és lekérdezésére használnak, ez a képlet hivatkozást vesz a hivatkozott celláról, két argumentuma van, az első argumentum nem választható, míg a második argumentum opcionális, amely az egyezés típusa, ez a függvény egy másik képlettel is használható.
A KÖZVETLEN funkció az Excel programban többféle felhasználással rendelkezik. Visszaadja a szöveglánc által megadott cellahivatkozást. Például, ha a B1 cella tartalmaz egy karakterláncot, akkor mondjuk, hogy a c4 és c4 tartomány értéke „Jessica”. Most, ha az INDIRECT függvényt fogjuk használni, és a referenciaszöveget B1-ként adjuk meg, akkor a c4 cellatartomány, azaz a „Jessica” értékét adja vissza.
Az Excel KÖZVETLEN funkciója lehetővé teszi a CÍM KÖZVETLEN megadását. Amint azt a fenti INDIRECT Excel példában megteheti, ha a B1 cella tartalmazza a C4 szöveget, ez a közvetett excel képlet adja vissza a C4 cella tartalmát ( Jessica ). Ez a funkció rendkívül hasznos funkció. Az INDIRECT funkciót bármikor használhatja, ha egy közvetett excel-képlet cellájára történő hivatkozást módosítani akarja, anélkül, hogy magát a képletet megváltoztatná.
INDIRECT Excel képlet
Magyarázat
A Ref_text hivatkozás egy cellára, amely tartalmaz A1 stílusú hivatkozást, R1C1 stílusú hivatkozást, hivatkozásként definiált nevet vagy hivatkozást egy cellára szöveges karakterláncként.
Az a1 argumentum nem kötelező
| a1 | Hamis vagy 0, ha a ref_text R1C1 stílusú |
| a1 | Elhagyva vagy Igaz (1), ha a ref_text A1 stílusú |
Hogyan használható az INDIRECT funkció az Excelben?
Vegyünk egy INDIRECT excel példát, mielőtt használnánk a cím függvényt az Excel munkafüzetben:
Tegyük fel, hogy egy www.xyz.com webhely esetében rendelkezünk a Google elemzési adataival a webhely különböző közönségcsatornákból érkező látogatói számára, öt különböző országból, az alábbi táblázat szerint:
Használhatjuk az INDIRECT funkciót az Excelben egy szöveges karakterlánc referenciaként való átalakításához, tehát utalva a H4 cellára, és ezt a nevet használva referenciaként. A INDIRECT függvény használata az excelben ebben az esetben a névtartományok hivatkozására szolgál egy cella értékeinek felhasználásával, így az I4 cellában egyszerűen használhatjuk az összeg függvényt egy országból érkező látogatók számának kiszámításához.
Most közvetlenül kiszámolhatjuk az egyes országok látogatóinak összegét az összeg függvény segítségével, de az INDIRECT függvény használatával az excelben kiszámíthatjuk a különböző országokból érkező látogatók összegét a névreferencia megváltoztatásával, nem pedig a közvetett excel képlet megváltoztatásával.
Minden országlátogató számára létrehoztuk a névtartományt; ezt könnyen megtehetjük, ha beírjuk a névtartományt a vágólap alatti területbe, az alábbiak szerint.
Egy másik módszer szerint elmehet a képletek-> névkezelő -> kiválasztja a megnevezni kívánt tartományt -> kattintson a névkezelőre -> írja be a névtartományt.
Ehhez hasonlóan létrehozott egy megnevezett tartományt az excelben más országokból érkező látogatók számára is.
Most, az I4-ben, kiszámítva a Kanadából érkező látogatók összegét az INDIRECT függvény használatával és a H4 névtartomány használatával (Kanada, fenti név az F3 esetében: F7), megkapjuk a látogatók teljes összegét.
Ha az országok adatellenőrzésével létrehozunk egy adatlistát, és ezt a listát alkalmazzuk a H4-re
Ha megváltoztatjuk a különböző országneveket Kanadától Indiáig, az Egyesült Államokig, Ausztráliáig vagy Szingapúrig, akkor az Excel INDIRECT funkciójával megkapjuk a látogató teljes összegét az I4-ben.
Tehát minden esetben nem változtatjuk meg a közvetett excel képletet, amely az I4 cellában található; megváltoztatjuk a név hivatkozást a H4-ben, és kiszámoljuk az egyes országok látogatóinak összegét.
Egy lépéssel tovább léphetünk, és hivatkozhatunk a munkalap szintű nevekre az INDIRECT funkció használatával is az excelben. Például most a INDIRECT funkciót fogjuk használni a munkafüzet különböző lapjaira hivatkozva.
Különböző lapokat hoztunk létre az országnevekkel az egyes keresési csatornákon végrehajtott eladások teljes számához a különböző országokban.
India,
Az USA számára,
Hasonlóképpen más országok esetében is.
Ismét kiszámítjuk az egyes csatornákon elért összes eladást az Excel INDIRECT funkciójának használatával, különböző lapokhoz, amelyekben országnevek szerepelnek referenciaként.
Ebben az esetben, mivel az értékesítési adatok különböző lapokban vannak, különböző elemeket kell összefűznünk, amelyek az adattartományt jelentik, így a ref_text a lap nevével kezdődik, amely ebben az esetben India a B4-ben, akkor használja az & amp; operátor nevű operátort, amelyet összefűzésre használnak.
Tehát megkapjuk az India eladásainak teljes összegét; most, ha megváltoztatjuk a B4 lapon szereplő országnevet Indiáról más országokra, például az USA-ra, Szingapúrra vagy Kanadára, akkor megkapjuk ezen országok teljes értékesítési értékét.
Megint ebben az esetben egyszerűen megváltoztattuk a ref_text értékeket a B4 cellában anélkül, hogy megváltoztattuk volna a fő közvetett excel képletet, amely C4-ben van, hogy megkapjuk az INDIRECT funkció használatával különböző országokba tartozó eladások összegét.
Most a INDIRECT funkciót fogjuk használni az R1C1 referencia segítségével, hogy megkapjuk a múlt havi értékesítési értéket. Különböző hónapok értékesítéseink vannak a B, C és D oszlopokban három, január, február és március hónapra, amelyek további hónapokra növekedhetnek, mint ápr, május, június stb. Tehát az eladási táblázat mindig növekszik, a következő hónapi eladások hozzáadásától függően. Tehát ebben a KÖZVETLEN Excel példában szeretnénk kiszámolni az elmúlt hónap eladását.
Tehát, itt az utolsó hónap március, és a INDIRECT funkció használatával ki akarjuk számolni a március havi teljes eladást, amely 249 344 USD.
Ebben a INDIRECT Excel példában megtaláljuk az INDIRECT függvény második argumentumának használatát
INDIRECT (ref_text, a1)
a1: amely opcionális argumentum, ez egy logikai érték (logikai érték), amely meghatározza, hogy a cellában milyen típusú hivatkozás (ref_text) található
ha az a1 igaz (1) vagy el van hagyva, a ref_text A1 stílusú hivatkozásnak számít
ha az a1 hamis (0), a ref_text R1C1 stílusként értelmezõdik
Miben különbözik az A1 stílus az R1C1 stílustól?
Normális esetben az INDIRECT funkció az Excelben A1 jelölést használ. Minden cellacím egy oszlopbetűből és egy sorszámból áll. Azonban az INDIRECT funkció az Excel-ben is támogatja az R1C1 jelölést. Ebben a rendszerben az A1 cellát R1C1 cellának, az A2 cellát R2C1-nek stb.
Az R1C1 jelöléshez való váltáshoz válassza a Fájl-> opciót az excel opciók párbeszédpanel megnyitásához, kattintson a képlet fülre, és jelölje be az R1C1 hivatkozási stílus opciót. Most észreveszi, hogy az oszlopbetűk számokra változnak. És a képleteiben szereplő összes cellahivatkozás is módosul.
Tehát, amikor az R1C1 stílust használjuk az INDIRECT függvényben az excel programban, akkor az a1 értéket hamisnak adjuk át, ha pedig A1 stílusú, akkor az igaz értéket adjuk át az a1 értékre.
Ebben a INDIRECT Excel példában az R1C1 stílust fogjuk használni a követelmény miatt, mivel a hónapos oszlopok növekednek.
Az R1C1 stílus használatával a teljes eladási érték a 10. sorban található, és az utolsó oszlop értékét, amely a D oszlopban található, a COUNTA függvény segítségével számoljuk ki , amely megadja az utolsó értéket tartalmazó oszlop számát. Tehát a count (10:10) ebben az esetben 4-et ad, amelyet az oszlop R1C1 stílusban ad meg. Tehát felvettük a kapcsolatot ezzel az értékkel, hogy megkapjuk az R10C4-et, amely tartalmazza a kívánt értéket, amely a múlt hónap teljes értékesítési értéke (249 344 USD).
És mivel itt az R1C1 stílust használjuk, hamis értéket adunk át az a1 argumentumnak.
Most adjunk hozzá még egy oszlopot az áprilisi eladás hónapjával.
Tehát szerezzen be új, a múlt havi értékesítési értékeket, amelyek megfelelnek április utolsó hónapjának. Hasonlóképpen, ha további hónapokat adunk hozzá, mindig új múlt havi eladási értéket kapunk, anélkül, hogy minden hónapra megváltoztatnánk a közvetett excel képletet. Ez a KÖZVETLEN funkció különlegessége az excelben.
Hogyan testreszabhatjuk a táblázat tömböt az INDIRECT Excel VLOOKUP függvényben?
Öt országunk van, és van egy keresési csatornánk, valamint az értékesítés, amelyet a weboldal ezen különböző csatornákon keresztül, különböző országokból hajtott végre. Most a B3 cellában azt az eladást szeretnénk elérni, amely az organikus keresések révén az Egyesült Államok országából származott.
Az egyes táblázatokhoz az egyes országok számára megneveztünk minden táblázatot az ország rövid megnevezésével, Ind Ind, Amerikai Egyesült Államok, Kanada Kan stb .
Ha közvetlenül az INDIRECT Excel vlookup függvényt használjuk az érték megszerzéséhez, az alábbiak szerint
Hibát kapunk, mert az INDIRECT Excel vlookup függvény nem képes felismerni a továbbított táblázat tömböt a B2 cella értékeként, ezért az INDIRECT funkciót az excelben fogjuk használni, hogy a vlookup a table_array-t érvényes argumentumként ismerje el.
Ha megváltoztatjuk a B1 és a B2 értékeit, akkor a B3-ban más eladóértékeket kapunk, szükség szerint.
Megjegyzés: A névtartományok nem tesznek különbséget a kis- és nagybetűk között. Ezért - az Ind, az IND és az InD megegyezik az Excelével.
A következő INDIRECT Excel példában az INDIRECT függvényt fogjuk használni az excelben egy függő lista létrehozásához. Van egy listánk a munkavállaló pozíciójáról egy vállalatnál; akkor minden beosztásnál megvan az alkalmazottak neve.
Most az I2 cellában létrehozunk egy listát a különböző alkalmazotti rangokhoz, mint pozícióik, az I3-ban pedig egy listát, amely az I2 értékétől függ. Ha I2 igazgató, akkor az I3-nak tükröznie kell az Andrew és Micheal rendezőinek nevét; ha I2 menedzser, akkor az I3-nak tükröznie kell a menedzser nevét, aki Camille, David, Davis és William, ugyanúgy a felügyelő számára.
Ebben az esetben ismét a INDIRECT funkciót fogjuk használni az excelben a függő lista létrehozásához. Az adatok ellenőrzése, az érvényesítési kritériumok listaként történő kiválasztásakor és a forrás mezőben az INDIRECT funkciót fogjuk használni az excelben az alábbiak szerint.
= KÖZVETLEN ($ I $ 2)
Amikor az igazgatói neveket választjuk az I3-ban, Andrew és Micheal nevekkel ellátott listaként tükröződik, amikor I2 menedzser, akkor az I3 tükrözi Camille, David, Davis és William menedzser nevét, és hasonlóan a felügyelőhöz, amint az az alábbiakban látható.
Dolgok, amikre emlékezni kell
- A ref_text argumentumnak érvényes cellahivatkozásnak kell lennie, különben az INDIRECT visszaadja a #REF! Hiba. A ref_text argumentum hivatkozhat egy másik munkafüzetre.
- Ha a ref_text egy másik munkafüzetre (külső hivatkozásra) utal, akkor a másik munkafüzetnek nyitva kell lennie. Ha a forrás munkafüzet nincs megnyitva, az INDIRECT visszaadja a #REF! Hibaérték.
- Ha a ref_text az 1 048 576 sorhatáron vagy a 16 384 (XFD) oszlophatáron kívül eső cellatartományra utal, az INDIRECT egy #REF! Hiba.
