Download eksemplet på projektmappe
Denne vejledning viser, hvordan du bruger Excel INDIRECT -funktion i Excel for at oprette en cellereference fra tekst.
INDIREKT Funktionsoversigt
INDIRECT -funktionen Opretter en cellereference fra en tekststreng.
(Bemærk hvordan formelindgangene vises)
INDIRECT -funktion Syntaks og input:
| 1 | = INDIRECT (ref_text, C1) |
ref_tekst - En streng, der repræsenterer en cellereference eller områdereference. Strengen kan være i R1C1- eller A1 -format eller kan være et navngivet område.
a1 - VALGFRIT: Angiver, om referencen er i R1C1- eller A1 -format. FALSK for R1C1 eller SAND / Tilladt for A1.
Hvad er INDIRECT -funktionen?
INDIRECT -funktionen giver dig mulighed for at give en tekststreng og få computeren til at fortolke denne streng som en egentlig reference. Dette kan bruges til at referere til et område på det samme ark, et andet ark eller endda en anden projektmappe.
FORSIGTIG: INDIRECT -funktionen er en af de flygtige funktioner. Det meste af tiden, når du arbejder i dit regneark, genberegner computeren kun en formel, hvis inputene har ændret deres værdier. En flygtig funktion genberegnes imidlertid hver gang du foretager en ændring af en hvilken som helst celle. Forsigtighed bør udvises for at sikre, at du ikke forårsager en stor genberegningstid på grund af overdreven brug af flygtig funktion eller at have mange celler afhængige af resultatet af en flygtig funktion.
Opret en cellereference
Sig, at du vil hente værdien fra A2, men du vil sikre dig, at din formel bliver på A2 uanset om nye rækker indsættes/fjernes. Du kan skrive en formel for
| 1 | = INDIRECT ("A2") |
Bemærk, at argumentet inde i vores funktion er tekststrengen "A2", og ikke en cellereference. Da dette også er en tekststreng, er det ikke nødvendigt at angive en absolut reference som $ A $ 2. Teksten vil aldrig ændre sig, og derfor vil denne formel altid pege på A2, uanset hvor den bliver flyttet til.
INDIREKT rækkenummer
Du kan sammenkoble tekststrenge og værdier fra celler sammen. I stedet for at skrive "A2" som vi gjorde tidligere, kan vi få en numerisk værdi fra celle B2 og bruge den i vores formel. Vi ville skrive formel ud som
| 1 | = INDIRECT ("A" og B2) |
"&" -Symbolet bruges her til at sammenkoble tekststrengen "A" med værdien fra celle B2. Så hvis værdien af B2 i øjeblikket var 10, så ville vores formel læse dette som
| 123 | = INDIREKT ("A" og 10)= INDIREKT ("A10")= A10 |
INDIREKT kolonneværdi
Du kan også sammenkoble i kolonnehenvisningen. Lad os denne gang sige, at vi ved, at vi vil have en værdi fra række 10, men vi vil være i stand til at ændre, hvilken kolonne vi skal trække fra. Vi lægger det kolonnebogstav, vi ønsker, i celle B2. Vores formel kunne se ud
| 1 | = INDIRECT (B2 & "10") |
Hvis værdien af B2 er “G”, vurderer vores formel sådan
| 123 | = INDIREKT ("G" og 10)= INDIREKT ("G10")= G10 |
INDIREKT r1c1 -stil
I vores tidligere eksempel måtte vi bruge et bogstav til at angive kolonnehenvisning. Dette skyldes, at vi brugte det, der kaldes referencer til A1 -stil. I A1 -stil angives kolonner med et bogstav, og rækker er angivet med tal. Absolutte referencer angives ved hjælp af "$" før den vare, vi ønsker at forblive absolut.
I r1c1 startes både rækker og kolonner med tal. Den absolutte henvisning til a1 ville blive skrevet som
| 1 | = R1C1 |
Du kan læse dette som "Række 1, kolonne 1". Relative referencer angives ved hjælp af parenteser, men tallet angiver position i forhold til celle med formel. Så hvis vi skrev en formel i celle A10, og vi skal henvise til A1, ville vi skrive formlen
| 1 | = R [-9] C |
Du kan læse dette som “Cellen 9 rækker op, men i samme kolonne.
Grunden til at dette kan være nyttigt er, at INDIRECT kan understøtte brugen af r1c1 -notation. Overvej det foregående eksempel, hvor vi hentede en værdi fra række 10, men ønskede at kunne ændre kolonnen. I stedet for at give et bogstav, lad os sige, at vi sætter et tal i celle B2. Vores formel kan da se ud
| 1 | = INDIREKT ("R10C" & B2, FALSK) |
Vi har udeladt 2nd argument indtil nu. Hvis dette argument udelades eller True, evalueres funktionen ved hjælp af A1 -stil. Da det er Falsk, vil det blive evalueret i r1c1. Lad os antage, at værdien af B2 er 5. Vores formel vil evaluere dette sådan
| 12 | = INDIREKT ("R10C5", FALSK)= $ E $ 10 |
INDIREKTE forskelle med A1 vs r1c1
Husk, at vi tidligere viste, at da indholdet af denne formel var en tekststreng, ændrede den sig aldrig?
| 1 | = INDIREKT ("A2") |
Denne formel vil altid se på celle A2, uanset hvor du flytter formlen. Da du i r1c1 kan angive relativ position ved hjælp af parenteser, forbliver denne regel ikke konsekvent. Hvis du placerer denne formel i celle B2
| 1 | = INDIRECT ("RC [-1]") |
Det vil se på celle A2 (da kolonne A er en til venstre for kolonne B). Hvis du kopierer denne formel til celle B3, forbliver teksten indeni den samme, men INDIRECT vil nu se på celle A3.
INDIREKT med arknavn
Du kan også kombinere et arknavn i dine INDIREKTE referencer. En vigtig regel at huske er, at du skal placere enkelte anførselstegn omkring navnene, og du skal adskille arknavnet fra cellereferencen med et udråbstegn.
Lad os sige, at vi havde denne opsætning, hvor vi angiver vores arknavn, række og kolonne.
Vores formel til at kombinere alle disse til en reference ville se sådan ud:
| 1 | = INDIRECT ("'" & A2 & "'!" & B2 & C2) |
Vores formel vil derefter blive vurderet således:
| 123 | = INDIRECT ("'" & "Sheet2" & "'!" & "B" & "5")= INDIRECT ("'" Sheet2'! B5 ")= 'Ark2'! B5 |
Teknisk set, da ordet "Sheet2" ikke har mellemrum i det, har vi det ikke brug for de enkelte anførselstegn. Det er fuldstændig gyldigt at skrive sådan noget
| 1 | = Ark2! A2 |
Det skader dog ikke at placere anførselstegnene, når du ikke har brug for dem. Det er bedste praksis at inkludere dem, så din formel kan håndtere forekomst, hvor de kan være nødvendige.
INDIREKTE til en anden projektmappe
Vi vil også nævne, at INDIRECT kan oprette en reference til en anden projektmappe. Begrænsningen er, at INDIRECT ikke vil hente værdier fra en lukket projektmappe, så denne særlige anvendelse har begrænset anvendelighed. Hvis den projektmappe, som INDIRECT peger på, ikke er åbnet, vil funktionen kaste et “#REF!” fejl.
Syntaksen ved skrivning af projektmappens navn er, at den skal være i parentes. Lad os bruge denne opsætning og prøve at hente en værdi fra celle C7.
Vores formel ville være
| 1 | = INDIRECT ("'[" & A2 & "]" & B2 & "'! C7") |
Vær igen opmærksom på placeringen af de enkelte anførselstegn, parenteser og udråbstegn. Vores formel vil derefter blive vurderet sådan:
| 123 | = INDIRECT ("'[" & "Sample.xlsx" & "]" & "Resumé" & "'! C7")= INDIRECT ("'[Sample.xslx] Resumé'! C7")= '[Sample.xlsx] Oversigt'! C7 |
INDIREKT til at opbygge dynamisk område
Når du har et stort datasæt, er det vigtigt at prøve at optimere formlerne, så de ikke udfører mere arbejde end nødvendigt. For eksempel, i stedet for at henvise til hele kolonne A, vil vi måske bare henvise til det nøjagtige antal celler på vores liste. Overvej følgende layout:
I celle B2 har vi placeret formlen
| 1 | = COUNTA (A: A) |
COUNTA -funktionen er meget let for computeren at beregne, da den simpelthen kontrollerer, hvor mange celler i kolonne A, der har en vis værdi, i modsætning til at skulle foretage logiske kontroller eller matematiske operationer.
Lad os nu bygge vores formel, der summerer værdierne i kolonne A, men vi vil sikre os, at den kun ser på det nøjagtige område med værdier (A2: A5). Vi vil skrive vores formel som
| 1 | = SUM (INDIREKT ("A2: A" og B2)) |
Vores INDIRECT kommer til at få nummeret 5 fra celle B2 og vil oprette en reference til intervallet A2: A5. SUMMEN kan derefter bruge dette område til sin beregning. Hvis vi tilføjer en anden værdi til celle A6, opdateres tallet i B2, og vores SUM-formel opdateres også automatisk for at inkludere denne nye værdi.
FORSIGTIG: Med indførelsen af tabeller i Office 2007 er det meget mere effektivt at gemme dine data i en tabel og bruge en strukturel reference frem for at bygge den formel, vi brugte i dette eksempel på grund af INDIRECTs flygtige karakter. Det kan dog være tilfælde, hvor du skal oprette en liste med varer og ikke kan bruge en tabel.
Dynamisk kortlægning med INDIRECT
Lad os tage det foregående eksempel og gå et skridt mere. I stedet for at skrive en formel for at give os summen af værdierne, opretter vi et navngivet område. Vi kunne kalde dette område "MyData" og få det til at referere til
| 1 | = INDIRECT ("A2: A" & COUNTA ($ A: $ A)) |
Bemærk, at da vi placerede dette i et navngivet område, har vi byttet referencen til B2 og i stedet lagt COUNTA -funktionen der direkte.
Nu hvor vi har dette navngivne område, kunne vi bruge det i et diagram. Vi opretter et tomt stregdiagram og tilføjer derefter en dataserie. For serieværdierne kan du skrive noget lignende
| 1 | = Sheet1! MyData |
Diagrammet vil nu bruge denne reference til plotværdier. Efterhånden som der tilføjes flere værdier til kolonne A, vil INDIRECT referere til et større og større område, og vores diagram vil fortsat være opdateret med alle de nyligt tilføjede værdier.
Dynamisk datavalidering med INDIRECT
Når der indsamles input fra brugere, er der nogle gange behov for at foretage ét valgs muligheder at vælge imellem afhængigt af et tidligere valg. Overvej dette layout, hvor vores første kolonne giver brugeren mulighed for at vælge mellem frugt, grøntsager og kød.
I 2nd kolonne, ønsker vi ikke at have en stor liste, der viser alle mulige valg, da vi allerede har indsnævret tingene lidt. Så vi har oprettet 3 andre lister, der ser sådan ud:
Dernæst tildeler vi hver af dem Disse lister til et navngivet område. Det vil sige, at alle frugterne vil være i et område kaldet "Frugt" og grøntsager i "Grøntsager" osv.
Tilbage i vores tabel er vi klar til at konfigurere datavalidering i 2nd kolonne. Vi opretter en listetypevalidering med input af:
| 1 | = INDIREKT (A2) |
INDIREKT kommer til at læse i valget i kolonne A og se navnet på en kategori. Vi har defineret områder med disse navne, så INDIREKT vil derefter tage det navn og oprette en reference til det ønskede område.
Ekstra Noter
Brug INDIRECT -funktionen til at oprette en cellereference fra tekst.
Opret først den tekststreng, der repræsenterer en cellereference. Strengen skal enten være i det sædvanlige A1-Style-kolonnebogstav og rækkenummer (M37) eller i R1C1-stil (R37C13). Du kan skrive referencen direkte, men normalt refererer du til celler, der definerer rækker og kolonner. Indtast til sidst hvilket cellereferenceformat du vælger. SAND eller Udeladt for A1-Style reference eller FALSE for R1C1-style.
Mens du arbejder med INDIREKTE formler, kan du bruge ROW -funktion for at få rækkenummeret på en reference eller COLUMN -funktion for at få kolonnenummeret (ikke bogstavet) for en reference.
Tilbage til listen over alle funktioner i Excel
INDIREKT i Google Sheets
INDIRECT -funktionen fungerer nøjagtig det samme i Google Sheets som i Excel:
