Last ned eksemplet på arbeidsbok
Denne opplæringen viser hvordan du bruker Excel INDIRECT -funksjon i Excel for å lage en cellereferanse fra tekst.
INDIREKT Funksjonsoversikt
INDIRECT -funksjonen Oppretter en cellereferanse fra en tekststreng.
(Legg merke til hvordan formelinngangene vises)
INDIRECT -funksjon Syntaks og innganger:
| 1 | = INDIRECT (ref_text, C1) |
ref_text - En streng som representerer en cellereferanse eller områdereferanse. Strengen kan være i R1C1- eller A1 -format, eller kan være et navngitt område.
a1 - VALGFRITT: Angir om referansen er i R1C1- eller A1 -format. FALSK for R1C1 eller SANN / Uttatt for A1.
Hva er INDIRECT -funksjonen?
INDIRECT -funksjonen lar deg gi en tekststreng og få datamaskinen til å tolke strengen som en faktisk referanse. Dette kan brukes til å referere til et område på samme ark, et annet ark eller til og med en annen arbeidsbok.
FORSIKTIG: INDIRECT -funksjonen er en av de flyktige funksjonene. Mesteparten av tiden når du jobber i regnearket, vil datamaskinen bare beregne en formel på nytt hvis inngangene har endret verdiene. En flyktig funksjon regner imidlertid ut på nytt hver gang du gjør en endring i en hvilken som helst celle. Forsiktighet bør utvises for å sikre at du ikke forårsaker en stor omberegningstid på grunn av overdreven bruk av flyktig funksjon eller at mange celler er avhengige av resultatet av en flyktig funksjon.
Lag en cellereferanse
Si at du vil hente verdien fra A2, men du vil sørge for at formelen din blir på A2 uavhengig av at nye rader blir satt inn/fjernet. Du kan skrive en formel for
| 1 | = INDIRECT ("A2") |
Vær oppmerksom på at argumentet i funksjonen vår er tekststrengen "A2", og ikke en cellereferanse. Siden dette også er en tekststreng, er det ikke nødvendig å angi en absolutt referanse som $ A $ 2. Teksten vil aldri endre seg, og derfor vil denne formelen alltid peke på A2, uansett hvor den flyttes til.
INDIREKT radnummer
Du kan sammenkoble tekststrenger og verdier fra celler sammen. I stedet for å skrive "A2" som vi gjorde tidligere, kan vi hente en numerisk verdi fra celle B2 og bruke den i formelen vår. Vi ville skrive ut formel som
| 1 | = INDIRECT ("A" og B2) |
"&" -Symbolet brukes her for å sammenkoble tekststrengen "A" med verdien fra celle B2. Så hvis verdien av B2 for øyeblikket var 10, så ville formelen vår lese dette som
| 123 | = INDIRECT ("A" og 10)= INDIRECT ("A10")= A10 |
INDIREKT kolonneverdi
Du kan også sammenkoble i kolonnehenvisningen. Denne gangen la oss si at vi vet at vi vil hente en verdi fra rad 10, men vi vil kunne endre hvilken kolonne vi skal trekke fra. Vi legger kolonnen bokstaven vi ønsker i celle B2. Formelen vår kan se ut
| 1 | = INDIRECT (B2 & "10") |
Hvis verdien av B2 er “G”, vurderer formelen vår slik
| 123 | = INDIRECT ("G" og 10)= INDIRECT ("G10")= G10 |
INDIREKT r1c1 -stil
I vårt forrige eksempel måtte vi bruke en bokstav for å indikere kolonnehenvisning. Dette er fordi vi brukte det som kalles referanse for A1 -stil. I A1 -stil er kolonner gitt med en bokstav, og rader er gitt med tall. Absolutte referanser indikeres med "$" før elementet vi ønsker å forbli absolutt.
I r1c1 startes både rader og kolonner med tall. Den absolutte referansen til a1 vil bli skrevet som
| 1 | = R1C1 |
Du kan lese dette som "Rad 1, kolonne 1". Relative referanser gis ved å bruke parenteser, men tallet angir posisjon i forhold til celle med formel. Så hvis vi skrev en formel i celle A10 og vi må referere til A1, ville vi skrive formelen
| 1 | = R [-9] C |
Du kan lese dette som "Cellen 9 rader opp, men i samme kolonne.
Grunnen til at dette kan være nyttig er at INDIRECT kan støtte bruk av r1c1 -notasjon. Tenk på det forrige eksemplet der vi hentet en verdi fra rad 10, men ønsket å kunne endre kolonnen. La oss si at vi setter et tall i celle B2 i stedet for å gi et brev. Vår formel kan da se ut
| 1 | = INDIREKT ("R10C" og B2, FALSK) |
Vi har utelatt 2nd krangel frem til nå. Hvis dette argumentet er utelatt eller True, vil funksjonen evaluere ved hjelp av A1 -stil. Siden det er usant, skal det evalueres i r1c1. La oss anta at verdien til B2 er 5. Formelen vår vil vurdere dette slik
| 12 | = INDIRECT ("R10C5", FALSE)= $ E $ 10 |
INDIREKTE forskjeller med A1 vs r1c1
Husk at vi tidligere viste at siden innholdet i denne formelen var en tekststreng, har den aldri endret seg?
| 1 | = INDIRECT ("A2") |
Denne formelen vil alltid se på celle A2, uansett hvor du flytter formelen. I r1c1, siden du kan angi relativ posisjon ved hjelp av parenteser, forblir denne regelen ikke konsekvent. Hvis du plasserer denne formelen i celle B2
| 1 | = INDIRECT ("RC [-1]") |
Det vil se på celle A2 (siden kolonne A er en til venstre for kolonne B). Hvis du kopierer denne formelen til celle B3, vil teksten inne forbli den samme, men INDIRECT vil nå se på celle A3.
INDIREKT med arknavn
Du kan også kombinere et arknavn i dine INDIREKTE referanser. En viktig regel å huske er at du bør sette enkelt anførselstegn rundt navnene, og du må skille arknavnet fra cellereferansen med et utropstegn.
La oss si at vi hadde dette oppsettet, der vi oppgir bladnavnet, raden og kolonnen.
Vår formel for å kombinere alle disse til en referanse vil se slik ut:
| 1 | = INDIRECT ("'" & A2 & "'!" & B2 & C2) |
Formelen vår vil da bli evaluert slik:
| 123 | = INDIRECT ("'" & "Sheet2" & "'!" & "B" & "5")= INDIRECT ("'" Sheet2'! B5 ")= 'Ark2'! B5 |
Teknisk sett, siden ordet "Sheet2" ikke har mellomrom i det, har vi ikke det trenge enkelt anførselstegn. Det er helt gyldig å skrive noe slikt
| 1 | = Ark2! A2 |
Det skader imidlertid ikke å sette anførselstegn når du ikke trenger dem. Det er best å inkludere dem slik at formelen din kan håndtere forekomster der de kan være nødvendige.
INDIREKTE til en annen arbeidsbok
Vi vil også nevne at INDIRECT kan lage en referanse til en annen arbeidsbok. Begrensningen er at INDIRECT ikke vil hente verdier fra en lukket arbeidsbok, så denne bruken har begrenset praktisk. Hvis arbeidsboken som INDIRECT peker på er uåpnet, vil funksjonen kaste en "#REF!" feil.
Syntaksen når du skriver arbeidsboknavnet er at den må stå i parentes. La oss bruke dette oppsettet og prøve å hente en verdi fra celle C7.
Vår formel ville være
| 1 | = INDIRECT ("'[" & A2 & "]" & B2 & "'! C7") |
Vær igjen oppmerksom på plasseringen av enkelt anførselstegn, parenteser og utropstegn. Formelen vår vil da bli evaluert slik:
| 123 | = INDIRECT ("'[" & "Sample.xlsx" & "]" & "Sammendrag" & "'! C7")= INDIRECT ("'[Sample.xslx] Sammendrag'! C7")= '[Eksempel.xlsx] Sammendrag'! C7 |
INDIREKT for å bygge dynamisk område
Når du har et stort datasett, er det viktig å prøve å optimalisere formlene slik at de ikke gjør mer arbeid enn nødvendig. For eksempel, i stedet for å referere til hele kolonne A, vil vi kanskje bare referere til det eksakte antallet celler i listen vår. Vurder følgende oppsett:
I celle B2 har vi plassert formelen
| 1 | = COUNTA (A: A) |
COUNTA -funksjonen er veldig enkel for datamaskinen å beregne, da den ganske enkelt sjekker hvor mange celler i kol A som har en viss verdi, i motsetning til å måtte utføre logiske kontroller eller matematiske operasjoner.
La oss nå bygge vår formel som vil summere verdiene i kolonne A, men vi vil sikre at den bare ser på det eksakte området med verdier (A2: A5). Vi vil skrive formelen vår som
| 1 | = SUMME (INDIREKT ("A2: A" og B2)) |
Vår INDIRECT kommer til å hente tallet 5 fra celle B2 og vil opprette en referanse til området A2: A5. SUMMEN kan deretter bruke dette området til beregningen. Hvis vi legger til en annen verdi i celle A6, oppdateres tallet i B2, og vår SUM-formel oppdateres også automatisk for å inkludere denne nye verdien.
FORSIKTIG: Med introduksjonen av tabeller i Office 2007 er det mye mer effektivt å lagre dataene dine i en tabell og bruke en strukturell referanse i stedet for å bygge formelen vi brukte i dette eksemplet på grunn av den flyktige naturen til INDIRECT. Imidlertid kan det være tilfeller der du må opprette en liste over elementer og ikke kan bruke en tabell.
Dynamisk kartlegging med INDIRECT
La oss ta det forrige eksemplet og gå et skritt til. I stedet for å skrive en formel for å gi oss summen av verdiene, lager vi et navngitt område. Vi kan kalle dette området "MyData" og få det til å referere til
| 1 | = INDIRECT ("A2: A" & COUNTA ($ A: $ A)) |
Vær oppmerksom på at siden vi setter dette i et navngitt område, har vi byttet referansen til B2 og i stedet satt COUNTA -funksjonen der.
Nå som vi har dette navngitte området, kan vi bruke det i et diagram. Vi lager et tomt linjediagram, og legger deretter til en dataserie. For serieverdiene kan du skrive noe slikt
| 1 | = Sheet1! MyData |
Diagrammet skal nå bruke denne referansen til plottverdier. Etter hvert som flere verdier legges til i kolonne A, vil INDIRECT referere til et større og større område, og diagrammet vårt vil fortsette å bli oppdatert med alle de nylig tilføyde verdiene.
Dynamisk datavalidering med INDIRECT
Når man samler innspill fra brukere, er det noen ganger behov for å velge ett valg å velge mellom, avhengig av et tidligere valg. Tenk på dette oppsettet, der vår første kolonne lar brukeren velge mellom frukt, grønnsaker og kjøtt.
I 2nd kolonne, ønsker vi ikke å ha en stor liste som viser alle mulige valg, ettersom vi allerede har redusert ting litt. Så vi har laget 3 andre lister som ser slik ut:
Deretter tildeler vi hver av dem disse lister til et navngitt område. Det vil si at alle fruktene vil være i et utvalg som kalles "frukt" og grønnsaker i "grønnsaker", etc.
Tilbake i tabellen vår er vi klare til å sette opp datavalidering i 2nd kolonne. Vi oppretter en lisstypevalidering, med en inngang på:
| 1 | = INDIREKT (A2) |
INDIRECT kommer til å lese i valget gjort i kolonne A og se navnet på en kategori. Vi har definert områder med disse navnene, så INDIRECT tar deretter det navnet og oppretter en referanse til ønsket område.
Ytterligere merknader
Bruk INDIRECT -funksjonen til å lage en cellereferanse fra tekst.
Lag først tekststrengen som representerer en cellereferanse. Strengen må enten være i den vanlige A1-stilkolonnen og radnummeret (M37) eller i R1C1-stilen (R37C13). Du kan skrive referansen direkte, men vanligvis vil du referere til celler som definerer radene og kolonnene. Til slutt skriver du inn hvilket cellereferanseformat du velger. SANN eller utelatt for referanse i A1-stil eller FALSK for R1C1-stil.
Mens du arbeider med INDIREKTE formler, kan det være lurt å bruke ROW -funksjon for å få radnummeret til en referanse eller COLUMN -funksjon for å få kolonnenummeret (ikke bokstaven) til en referanse.
Gå tilbake til listen over alle funksjoner i Excel
INDIREKT i Google Regneark
INDIRECT -funksjonen fungerer nøyaktig det samme i Google Sheets som i Excel:
