Lataa esimerkkityökirja
Tämä opetusohjelma osoittaa, miten Excel Epäsuora toiminto Excelissä soluviittauksen luomiseksi tekstistä.
Epäsuora toimintojen yleiskatsaus
INDIRECT -toiminto Luo soluviittauksen tekstimerkkijonosta.
(Huomaa, miten kaavasyötteet näkyvät)
INDIRECT -toiminto Syntaksi ja tulot:
| 1 | = Epäsuora (ref_text, C1) |
ref_text - Merkkijono, joka edustaa soluviittausta tai alueviittausta. Merkkijono voi olla R1C1- tai A1 -muodossa tai nimetty alue.
a1 - VALINNAINEN: Ilmaisee, onko viite R1C1- tai A1 -muodossa. EPÄTOSI R1C1: lle tai TOSI / Poistettu A1: lle.
Mikä on epäsuora toiminto?
INDIRECT -toiminnon avulla voit antaa tekstimerkkijonon ja pyytää tietokonetta tulkitsemaan merkkijonon varsinaisena viitteenä. Tätä voidaan käyttää viittaamaan samaan arkkiin, eri taulukkoon tai jopa eri työkirjaan.
VAROITUS: Epäsuora toiminto on yksi haihtuvista toiminnoista. Suurimman osan ajasta, kun työskentelet laskentataulukossasi, tietokone laskee kaavan uudelleen vain, jos syötteet ovat muuttaneet arvojaan. Haihtuva funktio laskee kuitenkin uudelleen joka kun teet muutoksen mihin tahansa soluun. Varovaisuutta on noudatettava sen varmistamiseksi, että et aiheuta suurta uudelleenlaskenta -aikaa haihtuvien toimintojen liiallisen käytön tai monien solujen vuoksi, jotka ovat riippuvaisia haihtuvan toiminnon tuloksesta.
Luo soluviittaus
Oletetaan, että haluat noutaa arvon A2: sta, mutta haluat varmistaa, että kaava pysyy A2: ssa riippumatta uusien rivien lisäämisestä/poistamisesta. Voisit kirjoittaa kaavan
| 1 | = Epäsuora ("A2") |
Huomaa, että funktion sisällä oleva argumentti on tekstimerkkijono “A2” eikä soluviittaus. Lisäksi koska tämä on tekstimerkkijono, sinun ei tarvitse ilmoittaa absoluuttista viitettä, kuten $ A $ 2. Teksti ei koskaan muutu, joten tämä kaava osoittaa aina kohtaan A2 riippumatta siitä, minne se siirretään.
Epäsuora rivinumero
Voit yhdistää tekstimerkkijonoja ja arvoja soluista yhdessä. Sen sijaan, että kirjoittaisimme ”A2” kuten aiemmin, voimme napata numeerisen arvon solusta B2 ja käyttää sitä kaavassamme. Kirjoittaisimme kaavan, kuten
| 1 | = Epäsuora ("A" ja B2) |
"&" -Symbolia käytetään tässä tekstiketjun "A" yhdistämiseen solun B2 arvoon. Joten jos B2: n arvo oli tällä hetkellä 10, kaavamme lukisi tämän muodossa
| 123 | = Epäsuora ("A" & 10)= Epäsuora ("A10")= A10 |
Epäsuora sarakkeen arvo
Voit myös ketjuttaa sarakeviitteessä. Sanotaan tällä kertaa, että tiedämme haluavamme napata arvon riviltä 10, mutta haluamme pystyä muuttamaan, mistä sarakkeesta vedetään. Laitamme haluamasi sarakekirjaimen soluun B2. Kaavamme voisi näyttää
| 1 | = Epäsuora (B2 & "10") |
Jos B2: n arvo on "G", kaavamme arvioi näin
| 123 | = Epäsuora ("G" & 10)= Epäsuora ("G10")= G10 |
Epäsuora r1c1 -tyyli
Edellisessä esimerkissämme piti käyttää kirjainta sarakeviittauksen osoittamiseen. Tämä johtuu siitä, että käytimme A1 -tyylisiä viittauksia. A1 -tyylissä sarakkeet annetaan kirjaimella ja rivit numeroilla. Absoluuttiset viittaukset on merkitty käyttämällä "$" ennen kohdetta, jonka haluamme pysyä absoluuttisena.
R1c1: ssä sekä rivit että sarakkeet aloitetaan numerolla. Ehdoton viittaus a1: een kirjoitettaisiin muodossa
| 1 | = R1C1 |
Voit lukea tämän rivinä 1, sarake 1. Suhteelliset viitteet annetaan suluissa, mutta numero osoittaa sijainnin suhteessa soluun, jolla on kaava. Joten jos kirjoitimme kaavan soluun A10 ja meidän on viitattava kohtaan A1, kirjoitamme kaavan
| 1 | = R [-9] C |
Voit lukea tämän seuraavasti: "Solu 9 riviä ylöspäin, mutta samassa sarakkeessa.
Tästä saattaa olla apua, koska INDIRECT voi tukea r1c1 -merkintätapaa. Harkitse edellistä esimerkkiä, jossa haimme arvon riviltä 10, mutta halusimme muuttaa saraketta. Kirjeen antamisen sijaan sanotaan, että laitamme numeron soluun B2. Silloin kaavamme voisi näyttää tältä
| 1 | = Epäsuora ("R10C" & B2, EPÄTOSI) |
Olemme jättäneet pois 2toinen väittely tähän asti. Jos tämä argumentti jätetään pois tai tosi, funktio arvioi A1 -tyyliin. Koska se on epätosi, se arvioidaan r1c1: ssä. Oletetaan, että B2: n arvo on 5. Kaavamme arvioi tämän näin
| 12 | = Epäsuora ("R10C5", EPÄTOSI)= 10 dollaria |
Epäsuorat erot A1 vs r1c1
Muista, että aiemmin osoitimme, että koska tämän kaavan sisältö oli tekstimerkkijono, se ei koskaan muuttunut?
| 1 | = Epäsuora ("A2") |
Tämä kaava katsoo aina solua A2 riippumatta siitä, minne kaavan siirrät. Koska r1c1: ssä voit ilmaista suhteellisen sijainnin hakasulkeilla, tämä sääntö ei pysy johdonmukaisena. Jos sijoitat tämän kaavan soluun B2
| 1 | = Epäsuora ("RC [-1]") |
Se tarkastelee solua A2 (koska sarake A on yksi sarakkeen B vasemmalla puolella). Jos kopioit tämän kaavan soluun B3, teksti pysyy samana, mutta Epäsuora katselee nyt solua A3.
Epäsuora arkin nimen kanssa
Voit myös yhdistää taulukon nimen epäsuoraan viittaukseesi. Muista tärkeä sääntö: laita lainausmerkit nimien ympärille, ja arkin nimi on erotettava soluviittauksesta huutomerkillä.
Oletetaan, että meillä oli tämä asetus, jossa ilmoitamme arkin nimen, rivin ja sarakkeen.
Kaavamme yhdistää nämä kaikki viitteeksi näyttäisi tältä:
| 1 | = Epäsuora ("" "& A2 &" '! "& B2 & C2) |
Kaavamme arvioidaan sitten seuraavasti:
| 123 | = Epäsuora ("" "&" Sheet2 "&" '! "&" B "&" 5 ")= Epäsuora ("" "Sheet2 '! B5")= 'Taulukko2'! B5 |
Teknisesti, koska sanassa "Sheet2" ei ole välilyöntejä, meillä ei ole tarve yksittäiset lainausmerkit. On täysin pätevää kirjoittaa jotain sellaista
| 1 | = Taulukko2! A2 |
Lainausmerkkien asettaminen ei kuitenkaan haittaa, kun et tarvitse niitä. Paras käytäntö on sisällyttää ne, jotta kaava pystyy käsittelemään esimerkiksi siellä, missä niitä voidaan tarvita.
Epäsuora toiseen työkirjaan
Mainitsemme myös, että INDIRECT voi luoda viittauksen toiseen työkirjaan. Rajoitus on, että INDIRECT ei hae arvoja suljetusta työkirjasta, joten tämän nimenomaisen käytön käytännöllisyys on rajallinen. Jos työkirja, johon INDIRECT osoittaa, on avaamaton, toiminto heittää "#REF!" virhe.
Työkirjan nimen syntaksi on, että sen on oltava hakasulkeissa. Käytä tätä asetusta ja yritä hakea arvo solusta C7.
Meidän kaava olisi
| 1 | = Epäsuora ("'[" & A2 & "]" & B2 & "'! C7") |
Kiinnitä jälleen huomiota lainausmerkkien, hakasulkeiden ja huutomerkkiin. Kaavamme arvioidaan sitten seuraavasti:
| 123 | = Epäsuora ("'[" & "Sample.xlsx" & "]" & "Yhteenveto" & "'! C7")= Epäsuora ("'[[Näyte.xslx] Yhteenveto'! C7")= '[Sample.xlsx] Yhteenveto'! C7 |
Epäsuora dynaamisen alueen rakentamiseen
Kun sinulla on suuri tietojoukko, on tärkeää yrittää optimoida kaavat niin, että ne eivät tee enemmän työtä kuin tarvitaan. Esimerkiksi sen sijaan, että viittaisimme koko sarakkeeseen A, saatamme haluta viitata vain luettelomme solujen tarkkaan määrään. Harkitse seuraavaa asettelua:
Olemme sijoittaneet kaavan soluun B2
| 1 | = COUNTA (A: A) |
COUNTA -funktio on tietokoneen erittäin helppo laskea, koska se yksinkertaisesti tarkistaa, kuinka monella solun A solulla on jokin arvo, eikä sen tarvitse suorittaa mitään loogisia tarkistuksia tai matemaattisia toimintoja.
Rakennetaan nyt kaava, joka laskee yhteen sarakkeen A arvot, mutta haluamme varmistaa, että se näyttää vain tarkan alueen arvojen kanssa (A2: A5). Kirjoitamme kaavamme muodossa
| 1 | = SUMMA (Epäsuora ("A2: A" & B2)) |
INDIRECT -järjestelmämme hakee numeron 5 solusta B2 ja luo viittauksen alueeseen A2: A5. SUM voi sitten käyttää tätä aluetta laskemiseen. Jos lisäämme toisen arvon soluun A6, B2: n luku päivittyy ja SUM-kaavamme päivittyy automaattisesti sisältämään tämän uuden arvon.
VAROITUS: Kun otamme käyttöön taulukot Office 2007: ssä, on paljon tehokkaampaa tallentaa tiedot taulukkoon ja käyttää rakenteellista viitettä kuin rakentaa tässä esimerkissä käytetty kaava INDIRECTin epävakaan luonteen vuoksi. Ne voivat kuitenkin olla tilanteita, joissa sinun on luotava luettelo kohteista etkä voi käyttää taulukkoa.
Dynaaminen kartoitus INDIRECT -toiminnolla
Otetaan edellinen esimerkki ja otetaan vielä yksi askel. Sen sijaan, että kirjoittaisimme kaavan, joka antaa meille arvojen summan, luomme nimitetyn alueen. Voisimme kutsua tätä aluetta "MyData" ja saada sen viitata siihen
| 1 | = Epäsuora ("A2: A" & COUNTA ($ A: $ A)) |
Huomaa, että koska laitamme tämän nimettyyn alueeseen, olemme vaihtaneet viittauksen B2: ksi ja asettaneet sen sijaan COUNTA -funktion suoraan.
Nyt kun meillä on tämä nimetty alue, voisimme käyttää sitä kaaviossa. Luomme tyhjän viivakaavion ja lisäämme sitten datasarjan. Sarjan arvoille voit kirjoittaa jotain sellaista
| 1 | = Taulukko1! MyData |
Kaavio käyttää nyt tätä viittausta kuvaajan arvoihin. Kun sarakkeeseen A lisätään lisää arvoja, INDIRECT viittaa yhä suurempiin alueisiin, ja kaaviosi pysyy jatkuvasti ajan tasalla kaikkien uusien lisäarvojen kanssa.
Dynaaminen tietojen validointi INDIRECT -toiminnolla
Kun kerätään tietoja käyttäjiltä, joskus on tehtävä yhden vaihtoehdon valinta, joka riippuu aiemmasta valinnasta. Harkitse tätä asettelua, jossa ensimmäisessä sarakkeessamme käyttäjä voi valita hedelmien, vihannesten ja lihan välillä.
2: ssatoinen sarakkeessa, emme halua, että meillä on laaja luettelo kaikista mahdollisista vaihtoehdoista, koska olemme jo kaventaneet asioita hieman. Olemme siis luoneet kolme muuta luetteloa, jotka näyttävät tältä:
Seuraavaksi annamme jokaiselle nämä luettelot nimettyyn alueeseen. Eli kaikki hedelmät ovat sarjassa nimeltä "Hedelmät" ja vihannekset "Vihannekset" jne.
Taulukossamme olemme valmiit määrittämään tietojen validoinnin 2: ssatoinen sarake. Luomme luettelotyypin validoinnin, ja syötteenä on:
| 1 | = Epäsuora (A2) |
Epäsuora lukee sarakkeen A valinnan ja näkee luokan nimen. Olemme määrittäneet alueet näillä nimillä, joten INDIRECT ottaa tämän nimen ja luo viittauksen haluttuun alueeseen.
Lisämerkinnät
Käytä INDIRECT -funktiota luodaksesi soluviittauksen tekstistä.
Luo ensin soluviittausta edustava tekstijono. Merkkijonon on oltava joko tavallisessa A1-tyylisessä sarakkeen kirjaimessa ja rivinumerossa (M37) tai R1C1-tyylisessä (R37C13). Voit kirjoittaa viittauksen suoraan, mutta yleensä viitataan soluihin, jotka määrittävät rivit ja sarakkeet. Kirjoita lopuksi valitsemasi soluviittausmuoto. TOSI tai puuttuu A1-tyylin viitteelle tai EPÄTOSI R1C1-tyyliin.
INDIRECT -kaavojen kanssa työskennellessäsi saatat haluta käyttää ROW -toiminto saadaksesi viitteen rivinumeron tai COLUMN -toiminto saadaksesi viitteen sarakkeen numeron (ei kirjaimen).
Palaa Excelin kaikkien toimintojen luetteloon
Epäsuora Google Sheetsissä
INDIRECT -toiminto toimii täsmälleen samalla tavalla Google Sheetsissä kuin Excelissä:
