Excel INDIRECT -toiminto (selitetty esimerkeillä + video)

Excel INDIRECT -toiminto - yleiskatsaus

Excelin INDIRECT -toimintoa voidaan käyttää, kun sinulla on solun tai alueen viite tekstimerkkijonona ja haluat saada arvot näistä viitteistä.

Lyhyesti sanottuna - voit käyttää epäsuoraa kaavaa palauta tekstimerkkijonon määrittämä viite.

Tässä Excel -opetusohjelmassa näytän sinulle kuinka käyttää epäsuoraa toimintoa Excelissä käyttämällä joitain käytännön esimerkkejä.

Mutta ennen kuin käsittelen esimerkkejä, katsotaan ensin sen syntaksia.

Epäsuora toiminto Syntaksi

= Epäsuora (ref_text, [a1])

Syöttöargumentit

• ref_text - Tekstimerkkijono, joka sisältää viittauksen soluun tai nimettyyn alueeseen. Tämän on oltava kelvollinen soluviittaus, tai muuten funktio palauttaa #REF! virhe
• [a1] - Looginen arvo, joka määrittää, minkä tyyppistä viitettä käytetään ref teksti. Tämä voi olla joko TOSI (osoittaa A1-tyyliviittaus) tai FALSE (osoittaa R1C1-tyylisen viittauksen). Jos se jätetään pois, se on oletusarvoisesti TOSI.

Lisämerkinnät

• INDIRECT on haihtuva toiminto. Tämä tarkoittaa, että se laskee uudelleen aina, kun Excel -työkirja on auki tai kun laskenta käynnistyy laskentataulukossa. Tämä pidentää käsittelyaikaa ja hidastaa työkirjaasi. Vaikka voit käyttää epäsuoraa kaavaa pienillä tietojoukoilla, joilla ei ole juurikaan vaikutusta nopeuteen, se saattaa hidastaa työkirjaasi käytettäessä sitä suurten tietojoukkojen kanssa
• Viiteteksti (ref_text) voi olla:
  • Viittaus soluun, joka puolestaan ​​sisältää viitteen A1- tai R1C1-tyylisessä viitemuodossa.
  • Viittaus soluun lainausmerkeissä.
  • Nimetty alue, joka palauttaa viittauksen

Esimerkkejä epäsuoran toiminnon käyttämisestä Excelissä

Nyt sukellamme ja katsomme muutamia esimerkkejä siitä, miten voit käyttää epäsuoraa toimintoa Excelissä.

Esimerkki 1: Käytä soluviittausta arvon hakemiseen

Se ottaa soluviittauksen tekstimerkkijonoksi syötteeksi ja palauttaa kyseisen viitteen arvon (kuten alla olevassa esimerkissä):

Kaava solussa C1 on:

= Epäsuora ("A1")

Yllä oleva kaava ottaa soluviittauksen A1 syöttöargumentiksi (lainausmerkeissä tekstimerkkijonona) ja palauttaa tämän solun arvon, joka on 123.

Jos ajattelet, miksi en käytä yksinkertaisesti = A1: tä INDIRECT -toiminnon käyttämisen sijaan, sinulla on pätevä kysymys.

Tässä on syy…

Kun käytät = A1 tai = $ A $ 1, se antaa sinulle saman tuloksen. Mutta kun lisäät rivin ensimmäisen rivin yläpuolelle, huomaat, että soluviittaukset muuttuvat automaattisesti ottamaan huomioon uuden rivin.

Voit myös käyttää INDIRECT -toimintoa, kun haluat lukita soluviittaukset siten, että se ei muutu, kun lisäät rivejä/sarakkeita laskentataulukkoon.

Esimerkki 2: Käytä soluviittausta solussa arvon hakemiseksi

Tämän toiminnon avulla voit myös noutaa arvon solusta, jonka viite on tallennettu itse soluun.

Yllä olevassa esimerkissä solun A1 arvo on 123.

Solussa C1 on viittaus soluun A1 (tekstimerkkijonona).

Nyt kun käytät INDIRECT -funktiota ja käytät argumenttia C1 (jossa puolestaan ​​on solun osoite tekstimerkkijonona), se muuntaa solun A1 arvon kelvolliseksi soluviittaukseksi.

Tämä puolestaan ​​tarkoittaa, että funktio viittaisi soluun A1 ja palauttaisi sen arvon.

Huomaa, että sinun ei tarvitse käyttää lainausmerkkejä täällä, koska C1: n soluviittaus on tallennettu vain tekstimerkkimuotoon.

Lisäksi jos solun C1 tekstimerkkijono ei ole kelvollinen soluviittaus, epäsuora funktio palauttaa #REF! virhe.

Esimerkki 3: Viitteen luominen solun arvon avulla

Voit myös luoda soluviittauksen käyttämällä sarakkeen aakkosten ja rivinumeron yhdistelmää.

Jos esimerkiksi solu C1 sisältää numeron 2 ja käytät kaavaa = Epäsuora (“A” ja C1) silloin se viittaisi soluun A2.

Käytännön sovellus tähän voi olla, kun haluat luoda dynaamisen viittauksen soluihin jonkin toisen solun arvon perusteella.

Jos kaavassa käyttämäsi tekstimerkkijono antaa viitteen, jota Excel ei ymmärrä, se palauttaa viittausvirheen (#REF!).

Esimerkki 4: Laske solualueen SUM

Voit myös viitata solualueeseen samalla tavalla kuin yksittäiseen soluun käyttämällä epäsuoraa Excel -toimintoa.

Esimerkiksi, = Epäsuora (“A1: A5”) viittaisi alueeseen A1: A5.

SUMMA -funktion avulla voit etsiä summan tai LARGE/SMALL/MIN/MAX -funktion muiden laskelmien tekemiseen.

Aivan kuten SUM -toiminto, voit käyttää myös toimintoja, kuten LARGE, MAX/MIN, COUNT jne.

Esimerkki 5: Viittauksen luominen taulukkoon käyttämällä INDIRECT -toimintoa

Yllä olevat esimerkit kattoivat kuinka viitata saman laskentataulukon soluun. Voit myös käyttää epäsuoraa kaavaa viittaamaan soluun jossakin muussa laskentataulukossa tai toisessa työkirjassa.

Tässä on jotain, mitä sinun on tiedettävä viittaamisesta muihin arkeihin:

• Oletetaan, että sinulla on laskentataulukko, jonka nimi on Sheet1, ja solun A1 taulukossa on arvo 123. Jos siirryt toiselle arkille (sanotaan arkki 2) ja viitataan taulukon 1 soluun A1, kaava olisi: = Taulukko1! A1

Mutta…

• Jos sinulla on laskentataulukko, joka sisältää kaksi tai enemmän kuin kaksi sanaa (välilyönti niiden välissä) ja viitat tämän taulukon soluun A1 toiselta taulukolta, kaava olisi seuraava: = 'Tietojoukko'! A1

Jos Excel sisältää useita sanoja, se lisää automaattisesti lainausmerkit taulukon nimen alkuun ja loppuun.

Katsotaanpa nyt, kuinka luoda Epäsuora funktio viittaamaan toisen laskentataulukon soluun.

Oletetaan, että sinulla on taulukko nimeltä Dataset ja solun A1 arvo 123.

Jos haluat viitata tähän soluun toisesta laskentataulukosta, käytä seuraavaa kaavaa:

= Epäsuora ("'Tietojoukko'! A1")

Kuten näette, viittaus soluun sisältää myös laskentataulukon nimen.

Jos laskentataulukon nimi on solussa (esimerkiksi A1), voit käyttää seuraavaa kaavaa:

= Epäsuora ("" "& A1 &" '! A1 ")

Jos laskentataulukon nimi on solussa A1 ja solun osoite solussa A2, kaava olisi seuraava:

= Epäsuora ("" "& A1 &" '! "& A2)

Voit myös muokata kaavaa viittaamaan toisen työkirjan soluun.

Tästä voi olla hyötyä, kun yrität luoda yhteenvetotaulukon, joka kerää tiedot useilta eri taulukoilta.

Muista myös, että kun käytät tätä kaavaa viittaamaan toiseen työkirjaan, kyseisen työkirjan on oltava auki.

Esimerkki 6: Viittaaminen nimettyyn alueeseen INDIRECT -kaavan avulla

Jos olet luonut nimetyn alueen Excelissä, voit viitata siihen nimetyllä alueella käyttämällä Epäsuoraa funktiota.

Oletetaan esimerkiksi, että sinulla on viiden oppilaan arvosanat kolmesta aiheesta, kuten alla on esitetty:

Nimeä tässä esimerkissä solut:

• B2: B6: Matematiikka
• C2: C6: Fysiikka
• D2: D6: kemia

Jos haluat nimetä solualueen, valitse solut ja siirry nimikenttään, kirjoita nimi ja paina enter.

Nyt voit viitata näihin nimettyihin alueisiin käyttämällä kaavaa:

= Epäsuora ("Nimetty alue")

Jos esimerkiksi haluat tietää matematiikan arvosanojen keskiarvon, käytä kaavaa:

= KESKIMÄÄRÄINEN (Epäsuora ("matematiikka"))

Jos solussa on nimetty alueen nimi (F2 alla olevassa esimerkissä on nimi Math), voit käyttää tätä suoraan kaavassa.

Alla oleva esimerkki näyttää kuinka lasketaan keskiarvo käyttämällä nimettyjä alueita.

Esimerkki 7: Riippuvan avattavan luettelon luominen Excel INDIRECT -toiminnolla

Tämä on yksi tämän toiminnon erinomaisista käyttötarkoituksista. Voit helposti luoda riippuvan avattavan luettelon käyttämällä sitä (kutsutaan myös ehdolliseksi avattavaksi luetteloksi).

Oletetaan esimerkiksi, että sinulla on luettelo maista peräkkäin ja kaupunkien nimet kullekin maalle alla esitetyllä tavalla:

Jos haluat luoda riippuvan avattavan luettelon, sinun on luotava kaksi nimettyä aluetta, A2: A5 nimellä US ja B2: B5 nimellä Intia.

Valitse nyt solu D2 ja luo avattava luettelo Intiaa ja Yhdysvaltoja varten. Tämä olisi ensimmäinen pudotusvalikko, jossa käyttäjä voi valita maan.

Luo nyt riippuvainen pudotusvalikko:

• Valitse solu E2 (solu, josta haluat saada riippuvan avattavan luettelon).
• Napsauta Data -välilehteä
• Napsauta Tietojen vahvistus.
• Valitse Luettelo vahvistuskriteereiksi ja käytä lähdekentässä seuraavaa kaavaa: = Epäsuora ($ D $ 2)
• Napsauta OK.

Kun tulet Yhdysvaltoihin solussa D2, solun E2 avattavassa valikossa näytetään Yhdysvaltojen tilat.

Ja kun kirjoitat Intiaan solussa D2, solun E2 pudotusvalikko näyttää Intian osavaltiot.

Joten tässä on muutamia esimerkkejä epäsuoran toiminnon käyttämisestä Excelissä. Nämä esimerkit toimivat kaikissa Excel-versioissa (Office 365, Excel2021-2022/2016/2013/2013)

Toivottavasti pidit tätä opetusohjelmaa hyödyllisenä.

• Excel VLOOKUP -toiminto.
• Excel HLOOKUP -toiminto.
• Excel INDEX -toiminto.
• Excel MATCH -toiminto.
• Excel OFFSET -toiminto.