INDIREKTE funksjon i Excel
Indirekte funksjon i Excel er en innebygd funksjon som brukes til å referere til og få celleverdier fra en tekststreng, denne formelen tar referanse fra den henviste cellen, den har to argumenter, det første argumentet er ikke valgfritt, mens det andre argumentet er valgfritt som refererer til typen match kan denne funksjonen også brukes med en annen formel.
INDIRECT-funksjonen i Excel har flere bruksområder. Den returnerer cellereferansen spesifisert av en tekststreng. For eksempel, hvis celle B1 inneholder en streng, la oss si at c4- og c4-området har verdien "Jessica." Nå, hvis vi bruker INDIREKTE-funksjonen og sender referanseteksten som B1, vil den returnere verdien som finnes i celleområdet c4 som er "Jessica."
Excels INDIRECT-funksjon lar deg spesifisere en celleadresse INDIRECTly. Som du kan i ovennevnte INDIREKTE Excel-eksempel, hvis celle B1 inneholder teksten C4, returnerer denne indirekte excel-formelen innholdet i cellen C4 ( Jessica ). Denne funksjonen er en ekstremt nyttig funksjon. Du kan bruke INDIRECT-funksjonen når du vil endre referansen til en celle i en indirekte Excel-formel uten å endre selve formelen.
INDIREKTE Excel-formel
Forklaring
Ref_text er en referanse til en celle som inneholder en referanse i A1-stil, en referanse til R1C1-stil, et navn definert som en referanse eller en referanse til en celle som en tekststreng.
a1 argument er valgfritt
| a1 | Usann eller 0, når ref_text er i R1C1-stil |
| a1 | Utelatt eller sann (1) når ref_text er i A1-stil |
Hvordan bruker jeg den INDIREKTE funksjonen i Excel?
La oss ta et INDIRECT excel-eksempel før vi bruker adressefunksjonen i Excel-arbeidsboken:
Anta at for et nettsted www.xyz.com har vi Google Analytics-data for besøkende til nettstedet fra forskjellige målgruppekanaler for fem forskjellige land som vist nedenfor i tabellen:
Vi kan bruke INDIRECT-funksjonen i Excel for å konvertere en tekststreng til en referanse, så det henvises til celle H4 og bruker det navnet som referanse. Bruk av INDIRECT-funksjonen i Excel, i dette tilfellet, er å referere til navnområder ved hjelp av verdier for en celle, så i celle I4 kan vi ganske enkelt bruke sumfunksjonen til å beregne det totale antall besøkende fra et land Canada.
Nå kan vi beregne summen av besøkende fra hvert land direkte ved hjelp av sumfunksjonen, men ved å bruke INDIRECT-funksjonen i excel, kan vi beregne summen av besøkende fra forskjellige land ved å endre navnereferansen i stedet for å endre selve den indirekte excelformelen.
Vi har opprettet navneområdet for hver landbesøkende; dette kan gjøres enkelt ved å skrive inn navnet i området rett under utklippstavlen, som vist nedenfor.
En annen metode er at du kan gå til formler-> navnebehandler -> velge området du vil gi navn -> klikke på navnebehandler -> skrive inn navnområdet.
På samme måte skapte det et navngitt utvalg i Excel for besøkende fra andre land også.
Nå, i I4, beregner vi summen av den besøkende fra landet Canada ved å bruke INDIRECT-funksjonen og bruker navnområdet i H4 (Canada, et navn ovenfor for F3: F7), får vi den totale summen av en besøkende.
Hvis vi oppretter en dataliste ved hjelp av datavalidering for landene og bruker den listen på H4
Og når vi endrer de forskjellige landnavnene fra Canada til India, USA, Australia eller Singapore, får vi den totale summen av den besøkende i I4 ved hjelp av INDIRECT-funksjonen i Excel.
Så for hvert tilfelle endrer vi ikke den indirekte excel-formelen, som er i celle I4; vi endrer navnereferansen i H4 og beregner summen av besøkende for hvert land.
Vi kan gå et skritt videre og kan referere til navn på regnearknivå også ved hjelp av INDIRECT-funksjonen i excel. For eksempel vil vi nå bruke INDIRECT-funksjonen med referanse til forskjellige ark i arbeidsboken.
Vi har laget forskjellige ark med landnavnene for totalt antall salg som er gjort for hver søkekanal for forskjellige land.
For India,
For USA,
Tilsvarende for andre land.
Igjen vil vi beregne det totale salget gjennom hver kanal ved hjelp av INDIRECT-funksjonen i Excel for forskjellige ark med landnavn som referanse.
I dette tilfellet, siden vi har salgsdata i forskjellige ark, må vi sammenkoble forskjellige elementer som er dataområdet, så ref_text begynner med navnet på arket, som er India i dette tilfellet i B4, så vil vi bruk & operatoren kalt ampersand som brukes til sammenkobling.
Så vi får den totale summen av salget for India; nå, hvis vi endrer arket landnavn i B4 fra India til andre land som USA, Singapore eller Canada, vil vi få den totale salgsverdien av disse landene.
Igjen, i dette tilfellet har vi ganske enkelt endret ref_text- verdiene i celle B4 uten å endre den viktigste indirekte excel-formelen, som er i C4, for å få summen av salg for forskjellige land som er i forskjellige ark ved hjelp av INDIRECT-funksjonen.
Nå skal vi bruke INDIRECT-funksjonen ved å bruke R1C1-referansen for å få den siste måneds salgsverdi. Vi har salg i forskjellige måneder i kolonnene B, C og D i tre måneder januar, februar og mars som kan vokse til ytterligere måneder som apr, mai, juni og så videre. Så salgetabellen vil alltid vokse avhengig av tillegg av det kommende månedssalget. Så i dette INDIREKTE Excel-eksemplet ønsker vi å beregne salget for den siste måneden.
Så her er den siste måneden mars, og vi vil beregne det totale salget av marsmåneden, som er $ 249 344, ved hjelp av INDIRECT-funksjonen.
I dette INDIRECT Excel-eksemplet finner vi bruken av det andre argumentet til INDIRECT-funksjonen
INDIREKTE (ref_text, a1)
a1: som er et valgfritt argument, det er en logisk verdi (en boolsk verdi) som spesifiserer hvilken type referanse (ref_text) som finnes i cellen
hvis a1 er sant (1) eller utelatt, betraktes ref_text som en referanse i A1-stil
hvis a1 er usant (0), blir ref_text tolket som en R1C1-stil
Hvordan er A1-stilen forskjellig fra R1C1-stilen?
Normalt bruker INDIRECT-funksjonen i Excel A1-notasjon. Hver celleadresse består av en kolonnebokstav og et radnummer. INDIRECT-funksjonen i Excel støtter imidlertid også R1C1-notasjon. I dette systemet blir celle A1 referert til som celle R1C1, celle A2 som R2C1, og så videre.
For å endre til R1C1-notasjon, velg Fil-> alternativet for å åpne dialogboksen excel-alternativer, klikk på formelfanen og et hake for R1C1 referansestilalternativ. Nå vil du merke at kolonnebokstavene alle endres til tall. Og alle cellereferansene i formlene dine justeres også.
Så når vi bruker R1C1-stilen i INDIRECT-funksjonen i excel, passerer vi a1-verdien som falsk, og hvis vi bruker A1-stil, så sender vi verdien true til a1.
Nå, i dette INDIRECT Excel-eksemplet, bruker vi R1C1-stilen på grunn av kravet ettersom kolonnene med måned ville vokse.
Ved bruk av R1C1-stilen er den totale salgsverdien i rad 10, og den siste kolonneverdien, som er i D-kolonnen, vil bli beregnet ved hjelp av COUNTA- funksjonen, som gir det siste kolonnetallet som inneholder en verdi. Så, tell (10:10) i dette tilfellet vil gi 4, som kolonnetallet i R1C1-stil. Så vi har kontaktet denne verdien for å få R10C4 som inneholder ønsket verdi som er den siste måneds totale salgsverdi ($ 249 344).
Og siden vi bruker R1C1-stilen her, vil vi gi en falsk verdi for a1-argumentet.
La oss nå legge til en kolonne til med april-salg.
Så få nye forrige måneds salgsverdier som tilsvarer den siste måneden i april. På samme måte, hvis vi legger til flere måneder, vil vi alltid få ny forrige måneds salgsverdi uten å endre den indirekte excelformelen for hver måned. Dette er spesialiteten til INDIRECT-funksjonen i excel.
Hvordan tilpasser du tabelloppsettet i INDIRECT Excel VLOOKUP-funksjonen?
Vi har fem land og en søkekanal og salget som nettstedet gjorde gjennom disse forskjellige kanalene fra forskjellige land. Nå, i celle B3, ønsker vi å få salget som var gjennom organiske søk fra landet USA.
For hver tabell for forskjellige land har vi navngitt hver tabell med et kort navn for landet, Ind for India, Stater for USA, Can for Canada, og så videre .
Hvis vi bruker INDIRECT Excel vlookup-funksjonen direkte for å få verdien, som gjort nedenfor
Vi får en feil fordi INDIRECT Excel vlookup-funksjonen ikke er i stand til å gjenkjenne tabellmatrisen som er sendt som verdien til B2-cellen, så vi vil bruke INDIRECT-funksjonen i excel for å få vlookup til å få table_array gjenkjent som et gyldig argument.
Hvis vi endrer verdiene til B1 og B2, får vi forskjellige salgsverdier i B3, etter behov.
Merk: Navneområder er ikke mellom store og små bokstaver. Derfor - Ind, IND og InD er alle de samme med Excel.
I det neste INDIRECT Excel-eksemplet vil vi bruke INDIRECT-funksjonen i excel for å lage en avhengig liste. Vi har en liste over stillingen til en ansatt i et selskap; for hver stilling har vi navnene på de ansatte.
Nå, i celle I2, skal vi lage en liste for forskjellige ansattes rangeringer som deres stillinger, og i I3 vil vi vise en liste som vil være avhengig av verdien i I2. Hvis I2 er regissør, bør I3 gjenspeile navnene på regissørene som Andrew og Micheal; hvis I2 er Manager, bør I3 gjenspeile navnene på manager som er Camille, David, Davis og William og på samme måte for veilederen.
I dette tilfellet vil vi igjen bruke INDIRECT-funksjonen i excel for å lage den avhengige listen. Datavalidering, i innstillinger som velger valideringskriterier som en liste og i kildefeltet, vil vi bruke INDIRECT-funksjonen i excel som følger.
= INDIREKTE ($ I $ 2)
Når vi velger regissørnavn i I3 reflekterer som en liste med navnene Andrew og Micheal, når I2 er Manager, reflekterer I3 navnene til manager Camille, David, Davis og William og tilsvarende for veilederen som vist nedenfor i figuren.
Ting å huske
- Ref_text-argumentet må være en gyldig cellereferanse, ellers vil INDIRECT returnere #REF! Feil. Ref_text-argumentet kan referere til en annen arbeidsbok.
- Hvis ref_text refererer til en annen arbeidsbok (en ekstern referanse), må den andre arbeidsboken være åpen. Hvis kildearbeidsboken ikke er åpen, returnerer INDIRECT #REF! Feilverdi.
- Hvis ref_text refererer til et celleområde utenfor radgrensen på 1.048.576 eller kolonnegrensen på 16.384 (XFD), returnerer INDIRECT et #REF! Feil.
