Jeg vet at Excel-eksperter hater denne funksjonen, men det er fortsatt mitt favoritt «hemmelige våpen»
Nevn INDIRECT-funksjonen i et Excel-forum, og du vil starte en krangel. Den er volatil, noe som betyr at den alltid er aktiv og regner på nytt, noe som kan gjøre et raskt regneark til en treg affære. Men brukt riktig, er det et kraftbrukers hemmelige våpen for å bygge dynamiske, reaktive dashbord.
Selv om ryktet for å forårsake treghet er velfortjent, er den virkelige faren ikke funksjonen i seg selv - det er å bruke den til feil oppgave når bedre alternativer finnes. For å mestre INDIRECT må du vite når du skal omfavne fleksibiliteten og når du skal bytte til et mer stabilt alternativ.
Hvordan INDIRECT-funksjonen fungerer
De fleste Excel-funksjoner er direkte, men INDIRECT er et kart over et kart. Tenk på det som en lapp på en dør: i stedet for å gå inn i et hus (en standard formel), forteller lappen deg å gå til et helt annet hus for å finne dataene dine.
Syntaksen for Excels INDIRECT-funksjon er enkel:
=INDIRECT(ref_text,[a1])hvor :
- ref_text er en vanlig cellereferanse, en R1C1-stilreferanse, en navngitt celle eller en referanse til en celle som en tekststreng.
- a1 (valgfritt) er en logisk verdi hvor TRUE (eller utelatt) tolker ref_text som en A1-stil referanse, og FALSE tolker ref_text som en R1C1-stil referanse.
Som standard bruker Excel A1-stil for referanser (kolonner er bokstaver, rader er tall). R1C1-stilen bruker tall for både kolonner og rader (for eksempel er R2C1 rad 2, kolonne 1), så i de fleste tilfeller kan du la a1-argumentet stå tomt og gå videre.
INDIRECT er ideell for å lage avhengige rullegardinlister
Anta at du har to navngitte tabeller i Excel-arket ditt: T_France og T_USA. Du vil at alternativene i bycellen din (F2) skal endre seg automatisk når du velger i landcellen (E2).
Først opprett en rullegardinliste over land i celle E2. Velg den cellen, og i Data-fanen klikker du på «Datavalidering».
Velg deretter «Liste» i Tillat-feltet, skriv Frankrike,USA i Kilde-feltet, og klikk «OK.»
Til slutt, velg celle F2 (hvor byene dine skal legges inn), og velg «Liste» igjen i dialogboksen for datavalidering. Denne gangen skal du imidlertid bruke funksjonen INDIRECT i Kilde-feltet:
=INDIRECT("T_"&E2)
Nå, når du velger "USA" i celle E2, viser rullegardinlisten i celle F2 en liste over alle byene i T_USA-tabellen din. På den annen side, hvis du velger "Frankrike" i celle E2, får du en liste over franske byer i celle F2.
Dette fungerer på grunn av ampersand (&) i formelen din, som tar den statiske teksten "T_" og limer den sammen med hvilket som helst ord som for øyeblikket er i celle E2. INDIRECT tar deretter den endelige sammensatte strengen og forteller Excel å finne tabellen med dette navnet.
Hvorfor eksperter hater det | Hvorfor jeg fortsatt bruker det |
|---|---|
I et enormt regneark med tusenvis av rader med disse rullegardinlistene, kan INDIRECTs "alltid-på"-adferd gjøre Excel treigt og kaotisk. | INDIRECT er den beste måten å lage app-lignende kaskaderende rullegardinlister på uten å bruke VBA. I en fokusert oppsett som dette er ytelsestapet mikroskopisk. |
INDIRECT er den beste funksjonen for å sette sammen et dashbord med flere tabeller
Tenk deg at du har separate salgstabeller for forskjellige år: T_2024 (venstre) og T_2025 (høyre). Du ønsker at en oppsummeringscelle (J2) automatisk skal summere salg basert på året som er valgt fra en nedtrekksmeny (I2).
Først opprett en tredje tabell kalt T_Years, og i den eneste kolonnen, list opp alle årene du ønsker skal vises i rullegardinlisten i celle I2.
Neste, dobbeltsjekk at begge datatabellene er navngitt konsekvent (T_[År]) og at kolonnen du skal hente data fra har nøyaktig samme etikett (Salg).
Velg celle I2, og på Data-fanen klikker du på "Datavalidering."
I Tillat-feltet, velg «Liste», og i Kilde-feltet, sett inn direkte cellehenvisninger til årene i T_Years-tabellen ved enten å skrive dem manuelt som absolutte referanser eller velge dem med musen. Klikk deretter «OK».
Selv om du har brukt direkte cellehenvisninger, vil området utvides automatisk når du legger til flere år i T_Years-tabellen din—en av de mange fordelene med å bruke strukturerte Excel-tabeller.
Nå i celle J2, skriv inn følgende formel:
=SUM(INDIRECT("T_"&I2&"[Sales]"))
Denne formelen forteller Excel å konstruere en tabellreferanse ved å bruke ampersand for å kombinere prefikset "T_", året som er valgt i celle I2, og den spesifikke [Sales]-kolonnen. INDIRECT evaluerer deretter den tekststrengen som et aktivt område som SUM-funksjonen kan beregne.
Hvorfor eksperter hater det | Hvorfor jeg fortsatt bruker det |
|---|---|
Denne oppsettet er sårbart fordi du bygger en tekstbasert referanse i stedet for et direkte valg. Hvis noen endrer T_2024 til Table2024, eller ved et uhell legger til et mellomrom på slutten av et element i årlisten din, vil tekststrengen ikke samsvare med noen kjent objekt, og formelen vil bryte. | Til tross for at den er skjør, bruker jeg den fortsatt fordi den gjør dashbordene skalerbare. Når 2026 kommer, trenger jeg ikke å omskrive noen formler — jeg lager ganske enkelt en ny tabell kalt T_2026, legger til 2026 i årlisten min, og dashbordet oppdaterer seg selv. |
INDIRECT er ikke alltid den beste løsningen: Det rullende vinduet
Mens INDIRECT er flott for å bytte mellom separate tabeller, som i eksemplene ovenfor, har det en grense. Når du begynner å bruke det for å utføre tung matematikk på et enkelt voksende datasett, blir volatilitetsskatten en for stor pris å betale.
Anta at du har en tabell kalt T_Stocks som vokser daglig. Du vil beregne gjennomsnittsprisen på de siste X oppføringene, der X er et variabelt tall du skriver inn i celle D3.
Her kunne du bruke INDIRECT for å manuelt bygge en tekststreng som T_Stocks[Price] og beregne radforskyvninger for å finne startpunktet ditt. For denne typen beregning bruker jeg imidlertid den ikke-volatilen INDEX, som bare beregner på nytt når dataene den faktisk ser på endres.
Her er formelen for celle E3:
=AVERAGE(INDEX(T_Stocks[Price],ROWS(T_Stocks)-(D3-1)):INDEX(T_Stocks[Price],ROWS(T_Stocks)))I dette tilfellet skaper kolon (:) mellom de to INDEX-funksjonene det dynamiske området, så det er ikke nødvendig med INDIRECT. Den første INDEX teller tilbake fra bunnen av tabellen, og den andre INDEX bruker ROWS for å finne den aller siste oppføringen i tabellen.
Så, hvis du endrer 5 i celle D3 til 10, utvides vinduet umiddelbart.
Hvorfor eksperter hater det | Hvorfor jeg ikke bruker det her |
|---|---|
Å bruke INDIRECT for matematiske områder er den raskeste måten å få Excel til å kollapse på. Fordi den er volatil, må Excel bygge hele områdereferansen fra en tekststreng på nytt hver gang du legger til en ny rad. | Å gå over til INDEX gir den samme dynamiske fleksibiliteten uten ytelsestapet. |
INDIRECT er fortsatt en av Excels mest omtalte funksjoner fordi den kan være treg og ustabil. Men når du trenger å gjøre tekst om til et funksjonelt kart for rullegardinlister eller tabellbytting, er det det beste verktøyet for jobben. Å ta hensyn til ustabilitet er en av de mange viktige måtene å gjøre Excel-regnearkene dine raskere på og opprettholde en responsiv arbeidsbok.