Ladda ner exempelarbetsboken
Denna handledning visar hur du använder Excel INDIRECT -funktion i Excel för att skapa en cellreferens från text.
INDIRECT Funktionsöversikt
INDIRECT -funktionen Skapar en cellreferens från en textsträng.
(Lägg märke till hur formelinmatningarna ser ut)
INDIRECT -funktion Syntax och ingångar:
| 1 | = INDIRECT (ref_text, C1) |
ref_text - En sträng som representerar en cellreferens eller intervallreferens. Strängen kan vara i R1C1- eller A1 -format, eller kan vara ett namngivet område.
a1 - VALFRITT: Anger om referensen är i R1C1- eller A1 -format. FALSE för R1C1 eller TRUE / Tillåtet för A1.
Vad är INDIRECT -funktionen?
Med INDIRECT -funktionen kan du ge en textsträng och låta datorn tolka strängen som en faktisk referens. Detta kan användas för att referera till ett intervall på samma blad, ett annat blad eller till och med en annan arbetsbok.
FÖRSIKTIGHET: INDIRECT -funktionen är en av de flyktiga funktionerna. För det mesta när du arbetar i ditt kalkylblad beräknar datorn bara en formel om ingångarna har ändrat deras värden. En flyktig funktion räknar emellertid om varje när du gör en ändring i en cell. Försiktighet bör iakttas för att säkerställa att du inte orsakar en stor omräkningstid på grund av överdriven användning av flyktig funktion eller att många celler är beroende av resultatet av en flyktig funktion.
Skapa en cellreferens
Säg att du vill hämta värdet från A2, men du vill se till att din formel stannar på A2 oavsett om nya rader infogas/tas bort. Du kan skriva en formel för
| 1 | = INDIRECT ("A2") |
Observera att argumentet i vår funktion är textsträngen "A2", och inte en cellreferens. Eftersom det här är en textsträng behöver du inte ange en absolut referens som $ A $ 2. Texten kommer aldrig att förändras, och därför kommer denna formel alltid att peka på A2, oavsett vart den flyttas till.
INDIREKT radnummer
Du kan sammanfoga textsträngar och värden från celler tillsammans. I stället för att skriva "A2" som vi gjorde tidigare kan vi ta ett numeriskt värde från cell B2 och använda det i vår formel. Vi skulle skriva ut formeln som
| 1 | = INDIRECT ("A" och B2) |
"&" -Symbolen används här för att sammanfoga textsträngen "A" med värdet från cell B2. Så om värdet på B2 för närvarande var 10, skulle vår formel läsa detta som
| 123 | = INDIRECT ("A" och 10)= INDIRECT ("A10")= A10 |
INDIREKT kolumnvärde
Du kan också sammanfoga i kolumnreferensen. Den här gången låt oss säga att vi vet att vi vill ta ett värde från rad 10, men vi vill kunna ändra vilken kolumn vi ska dra från. Vi lägger den kolumnbokstav vi vill ha i cell B2. Vår formel kan se ut
| 1 | = INDIRECT (B2 & "10") |
Om värdet på B2 är “G”, utvärderas vår formel så
| 123 | = INDIRECT ("G" & 10)= INDIRECT ("G10")= G10 |
INDIRECT r1c1 -stil
I vårt tidigare exempel var vi tvungna att använda en bokstav för att ange kolumnreferens. Detta beror på att vi använde det som kallas referens för A1 -stil. I A1 -stil ges kolumner med en bokstav och rader ges med siffror. Absoluta referenser anges med "$" före objektet vi vill förbli absoluta.
I r1c1 startas både rader och kolumner med nummer. Den absoluta hänvisningen till a1 skulle skrivas som
| 1 | = R1C1 |
Du kan läsa detta som "rad 1, kolumn 1". Relativa referenser ges med parenteser, men siffran anger position i förhållande till cell med formel. Så om vi skrev en formel i cell A10 och vi måste hänvisa till A1, skulle vi skriva formeln
| 1 | = R [-9] C |
Du kan läsa detta som "Cellen 9 rader upp, men i samma kolumn.
Anledningen till att detta kan vara till hjälp är att INDIRECT kan stödja användningen av r1c1 -notation. Tänk på det tidigare exemplet där vi hämtade ett värde från rad 10 men ville kunna ändra kolumnen. I stället för att ge en bokstav, låt oss säga att vi sätter ett tal i cell B2. Vår formel kan då se ut
| 1 | = INDIRECT ("R10C" & B2, FALSE) |
Vi har utelämnat 2: annd argument fram till nu. Om detta argument utelämnas eller True, kommer funktionen att utvärderas med hjälp av A1 -stil. Eftersom det är falskt kommer det att utvärderas i r1c1. Låt oss anta att värdet på B2 är 5. Vår formel kommer att utvärdera detta så
| 12 | = INDIRECT ("R10C5", FALSE)= $ E $ 10 |
INDIREKTA skillnader med A1 vs r1c1
Kom ihåg att vi tidigare visade att eftersom innehållet i denna formel var en textsträng, har den aldrig ändrats?
| 1 | = INDIRECT ("A2") |
Denna formel kommer alltid att titta på cell A2, oavsett var du flyttar formeln. I r1c1, eftersom du kan ange relativ position med parenteser, är denna regel inte konsekvent. Om du placerar denna formel i cell B2
| 1 | = INDIRECT ("RC [-1]") |
Det kommer att titta på cell A2 (eftersom kolumn A är en till vänster om kolumn B). Om du kopierar denna formel till cell B3 förblir texten inuti densamma, men INDIRECT kommer nu att titta på cell A3.
INDIRECT med bladnamn
Du kan också kombinera ett bladnamn i dina INDIRECT -referenser. En viktig regel att komma ihåg är att du ska placera enkla citattecken runt namnen, och du måste skilja bladnamnet från cellreferensen med ett utropstecken.
Låt oss säga att vi hade den här inställningen, där vi anger vårt bladnamn, rad och kolumn.
Vår formel för att kombinera alla dessa till en referens skulle se ut så här:
| 1 | = INDIRECT ("'" & A2 & "'!" & B2 & C2) |
Vår formel kommer sedan att utvärderas så här:
| 123 | = INDIRECT ("'" & "Sheet2" & "'!" & "B" & "5")= INDIRECT ("'" Sheet2'! B5 ")= 'Sheet2'! B5 |
Tekniskt sett, eftersom ordet "Sheet2" inte har några mellanslag i det, har vi inte det behöver de enda citattecknen. Det är helt giltigt att skriva något liknande
| 1 | = Sheet2! A2 |
Det skadar dock inte att sätta citattecken när du inte behöver dem. Det är bästa praxis att inkludera dem så att din formel kan hantera instans där de kan behövas.
INDIRECT till en annan arbetsbok
Vi kommer också att nämna att INDIRECT kan skapa en referens till en annan arbetsbok. Begränsningen är att INDIRECT inte kommer att hämta värden från en stängd arbetsbok, så den här användningen har begränsad praktik. Om arbetsboken som INDIRECT pekar på är oöppnad, kommer funktionen att kasta en "#REF!" fel.
Syntaxen när du skriver arbetsbokens namn är att den måste vara inom hakparenteser. Låt oss använda den här inställningen och försöka hämta ett värde från cell C7.
Vår formel skulle vara
| 1 | = INDIRECT ("'[" & A2 & "]" & B2 & "'! C7") |
Återigen, var uppmärksam på placeringen av de enda citattecken, parenteser och utropstecken. Vår formel kommer sedan att utvärderas så här:
| 123 | = INDIRECT ("'[" & "Sample.xlsx" & "]" & "Sammanfattning" & "'! C7")= INDIRECT ("'[Sample.xslx] Sammanfattning'! C7")= '[Sample.xlsx] Sammanfattning'! C7 |
INDIRECT för att bygga dynamiskt omfång
När du har en stor datamängd är det viktigt att försöka optimera formlerna så att de inte gör mer arbete än vad som behövs. Till exempel, i stället för att referera till hela kolumn A, kanske vi bara vill referera till det exakta antalet celler i vår lista. Tänk på följande layout:
I cell B2 har vi placerat formeln
| 1 | = COUNTA (A: A) |
COUNTA -funktionen är mycket lätt för datorn att beräkna, eftersom den helt enkelt kontrollerar hur många celler i kol A som har något värde, i motsats till att behöva göra några logiska kontroller eller matematiska operationer.
Låt oss nu bygga vår formel som summerar värdena i kolumn A, men vi vill se till att den bara ser på det exakta intervallet med värden (A2: A5). Vi kommer att skriva vår formel som
| 1 | = SUMMA (INDIRECT ("A2: A" & B2)) |
Vår INDIRECT kommer att ta nummer 5 från cell B2 och kommer att skapa en referens till intervallet A2: A5. SUMMEN kan sedan använda detta intervall för sin beräkning. Om vi lägger till ett annat värde i cell A6, kommer numret i B2 att uppdateras, och vår SUM-formel uppdateras också automatiskt för att inkludera detta nya värde.
FÖRSIKTIGHET: Med introduktionen av tabeller i Office 2007 är det mycket mer effektivt att lagra dina data i en tabell och använda en strukturreferens snarare än att bygga formeln som vi använde i detta exempel på grund av den flyktiga naturen hos INDIRECT. Det kan dock vara fall där du måste skapa en lista med objekt och inte kan använda en tabell.
Dynamisk kartläggning med INDIRECT
Låt oss ta det föregående exemplet och gå ett steg till. I stället för att skriva en formel för att ge oss summan av värdena skapar vi ett namngivet intervall. Vi kan kalla detta intervall för "MyData" och få det att hänvisa till
| 1 | = INDIRECT ("A2: A" & COUNTA ($ A: $ A)) |
Observera att eftersom vi placerar detta i ett namngivet område har vi bytt referens till B2 och istället lagt COUNTA -funktionen direkt där.
Nu när vi har det här namngivna intervallet kan vi använda det i ett diagram. Vi skapar ett tomt raddiagram och lägger sedan till en dataserie. För seriens värden kan du skriva något liknande
| 1 | = Sheet1! MyData |
Diagrammet kommer nu att använda denna referens till plottvärden. När fler värden läggs till i kolumn A kommer INDIRECT att hänvisa till ett större och större intervall, och vårt diagram kommer att fortsätta att vara uppdaterat med alla de nyligen tillagda värdena.
Dynamisk datavalidering med INDIRECT
När man samlar in input från användare finns det ibland ett behov av att välja ett val att välja mellan beroende på ett tidigare val. Tänk på denna layout, där vår första kolumn låter användaren välja mellan frukt, grönsaker och kött.
I 2nd kolumn vill vi inte ha en stor lista som visar alla möjliga val, eftersom vi redan har minskat lite. Så vi har skapat tre andra listor som ser ut så här:
Därefter tilldelar vi var och en av dem dessa listor till ett namngivet område. Dvs alla frukter kommer att vara i ett sortiment som kallas "frukt" och grönsaker i "grönsaker", etc.
Tillbaka i vår tabell är vi redo att konfigurera datavalidering i 2nd kolumn. Vi skapar en listtypvalidering med inmatning av:
| 1 | = INDIRECT (A2) |
INDIREKT kommer att läsa in valet i kol A och se namnet på en kategori. Vi har definierat intervall med dessa namn, så INDIRECT tar sedan det namnet och skapar en referens till det önskade intervallet.
Ytterligare anmärkningar
Använd INDIRECT -funktionen för att skapa en cellreferens från text.
Skapa först textsträngen som representerar en cellreferens. Strängen måste antingen vara i den vanliga A1-Style-kolumnbokstaven och radnumret (M37) eller i R1C1-stil (R37C13). Du kan skriva referensen direkt, men vanligtvis refererar du till celler som definierar raderna och kolumnerna. Ange sist vilket cellreferensformat du väljer. TRUE eller Utelämnad för A1-Style-referens eller FALSE för R1C1-style.
När du arbetar med INDIRECT Formulas kanske du vill använda RAD -funktion för att få radnumret för en referens eller COLUMN -funktion för att få kolumnnumret (inte bokstaven) för en referens.
Återgå till listan över alla funktioner i Excel
INDIREKT i Google Kalkylark
INDIRECT -funktionen fungerar exakt samma sak i Google Kalkylark som i Excel:
