INDIREKTE funktion i Excel
Indirekte funktion i excel er en indbygget funktion, der bruges til at henvise til og hente celleværdier fra en tekststreng, denne formel tager henvisning fra den henviste celle, den har to argumenter, det første argument er ikke valgfrit, mens det andet argument er valgfrit, hvilket refererer til typen af match kan denne funktion også bruges med en anden formel.
INDIREKTE funktion i excel har flere anvendelser. Det returnerer den cellehenvisning, der er angivet af en tekststreng. For eksempel, hvis celle B1 indeholder en streng, lad os sige, at c4 og c4-området har værdien "Jessica." Hvis vi nu bruger INDIREKTE-funktionen og sender referenceteksten som B1, returnerer den værdien indeholdt i celleområde c4, der er "Jessica."
Excels INDIRECT-funktion giver dig mulighed for at angive en celleadresse INDIRECTly. Som du kan i ovenstående INDIREKTE Excel-eksempel, hvis celle B1 indeholder teksten C4, returnerer denne indirekte excelformel indholdet af cellen C4 ( Jessica ). Denne funktion er en yderst nyttig funktion. Du kan bruge den INDIREKTE funktion, når du vil ændre henvisningen til en celle i en indirekte excelformel uden at ændre selve formlen.
INDIREKTE Excel-formel
Forklaring
Ref_text er en reference til en celle, der indeholder en A1-stilreference, en R1C1-stilreference, et navn defineret som en reference eller en reference til en celle som en tekststreng.
a1-argumentet er valgfrit
| a1 | Falsk eller 0, når ref_text er i R1C1-stil |
| a1 | Udeladt eller sandt (1) når ref_text er i A1-stil |
Hvordan bruges INDIRECT-funktionen i Excel?
Lad os tage et INDIRECT excel-eksempel, inden vi bruger adressefunktionen i Excel-projektmappen:
Antag, for et websted www.xyz.com har vi Google Analytics-data for besøgende på webstedet fra forskellige målgruppekanaler i fem forskellige lande som vist nedenfor i tabellen:
Vi kan bruge INDIRECT-funktionen i Excel med det formål at konvertere en tekststreng til en reference, så vi henviser til celle H4 og bruger dette navn som reference. Brug af INDIRECT-funktionen i Excel, i dette tilfælde, er at henvise til navneområder ved hjælp af værdier for en celle, så i celle I4 kunne vi simpelthen bruge sumfunktionen til at beregne det samlede antal besøgende fra et land Canada.
Nu kan vi direkte beregne summen af besøgende fra hvert land ved hjælp af sumfunktionen, men ved hjælp af INDIRECT-funktionen i excel kan vi beregne summen af besøgende fra forskellige lande ved at ændre navnehenvisningen i stedet for at ændre selve den indirekte excelformel.
Vi har oprettet navneområdet for hver landsbesøgende; dette kunne gøres let ved at skrive navneområdet i rummet lige under udklipsholderen som vist nedenfor.
En anden metode er, at du kan gå til formler-> navnemanager -> vælge det område, du vil navngive -> klikke på navneadministrator -> skrive navnområdet.
På samme måde skabte det et navngivet interval i Excel for besøgende fra andre lande.
Nu, i I4, beregner summen af den besøgende fra landet Canada ved hjælp af INDIRECT-funktionen og ved hjælp af navneområdet i H4 (Canada, et navn ovenfor for F3: F7), får vi den samlede sum af en besøgende.
Hvis vi opretter en dataliste ved hjælp af datavalidering for landene og anvender denne liste på H4
Og når vi ændrer de forskellige landsnavne fra Canada til Indien, USA, Australien eller Singapore, får vi den samlede sum af besøgende i I4 ved hjælp af INDIRECT-funktionen i Excel.
Så for hvert tilfælde ændrer vi ikke den indirekte excelformel, som findes i celle I4; vi ændrer navnehenvisningen i H4 og beregner summen af besøgende for hvert land.
Vi kan bevæge os et skridt videre og kan henvise til navne på regnearkniveau også ved hjælp af INDIRECT-funktionen i Excel. For eksempel bruger vi nu INDIREKTE-funktionen med henvisning til forskellige ark i projektmappen.
Vi har oprettet forskellige ark med landene for det samlede antal salg, der foretages med hver søgekanal for forskellige lande.
For Indien,
For USA,
Tilsvarende for andre lande.
Igen vil vi beregne det samlede salg gennem hver kanal ved hjælp af INDIRECT-funktionen i Excel for forskellige ark med landsnavne som reference.
I dette tilfælde, da vi har salgsdata i forskellige ark, bliver vi nødt til at sammenkæde forskellige elementer, der er dataområdet, så ref_text starter med navnet på arket, hvilket er Indien i dette tilfælde i B4, så vil vi brug & operatoren kaldet ampersand, der bruges til sammenkædning.
Så vi får den samlede sum af salget til Indien; nu, hvis vi ændrer landets landnavn i B4 fra Indien til andre lande som USA, Singapore eller Canada, får vi den samlede salgsværdi for disse lande.
I dette tilfælde har vi simpelthen ændret ref_text- værdierne i celle B4 uden at ændre den primære indirekte excelformel , som er i C4, for at få summen af salget for forskellige lande, der er i forskellige ark ved hjælp af INDIRECT-funktionen.
Nu skal vi bruge INDIRECT-funktionen ved hjælp af R1C1-referencen til at få den sidste måneds salgsværdi. Vi har salg i forskellige måneder i kolonne B, C og D i tre måneder januar, februar og marts, der kan vokse til yderligere måneder som apr, maj, juni osv. Så salgstabellen vil altid vokse afhængigt af tilføjelsen af det kommende månedssalg. Så i dette INDIRECT Excel-eksempel vil vi beregne salget af den sidste måned.
Så her er den sidste måned marts, og vi vil beregne det samlede salg af martsmåneden, som er $ 249.344, ved hjælp af funktionen INDIRECT.
I dette INDIRECT Excel-eksempel finder vi brugen af det andet argument for INDIRECT-funktionen
INDIREKTE (ref_text, a1)
a1: hvilket er et valgfrit argument, det er en logisk værdi (en boolsk værdi), der specificerer hvilken type reference (ref_text) der er indeholdt i cellen
hvis a1 er sandt (1) eller udeladt, betragtes ref_text som en A1-stilreference
hvis a1 er falsk (0), fortolkes ref_text som en R1C1-stil
Hvordan adskiller A1-stilen sig fra R1C1-stilen?
Normalt bruger INDIRECT-funktionen i Excel A1-notation. Hver celleadresse består af et kolonnebogstav og et rækkenummer. Imidlertid understøtter INDIRECT-funktionen i Excel også R1C1-notation. I dette system betegnes celle A1 som celle R1C1, celle A2 som R2C1 og så videre.
For at skifte til R1C1 notation skal du vælge File-> option for at åbne dialogboksen excel options, klikke på fanen formel og et flueben til R1C1 reference stil option. Nu vil du bemærke, at kolonnebogstaverne alle skifter til tal. Og alle cellereferencer i dine formler justeres også.
Så når vi bruger R1C1-stilen i INDIREKTE-funktionen i excel, videregiver vi a1-værdien som falsk, og hvis vi bruger A1-stil, videregiver vi værdien sand til a1.
Nu, i dette INDIREKTE Excel-eksempel, bruger vi R1C1-stilen på grund af kravet, da kolonnerne med måned ville vokse.
Ved brug af R1C1-stilen er den samlede salgsværdi i række 10, og den sidste kolonneværdi, der er i D-kolonnen, beregnes ved hjælp af funktionen COUNTA , som giver det sidste kolonnetal, der indeholder en værdi. Så tæller (10:10) i dette tilfælde giver 4, hvilket kolonnetal i R1C1-stil. Så vi har kontaktet denne værdi for at få R10C4, der indeholder den ønskede værdi, der er den sidste måneds samlede salgsværdi ($ 249.344).
Og da vi bruger R1C1-stilen her, sender vi en falsk værdi til a1-argumentet.
Lad os nu tilføje endnu en kolonne med månedens salg af april.
Så få nye sidste måneds salgsværdier svarende til den sidste april måned. Tilsvarende, hvis vi tilføjer flere måneder, får vi altid ny sidste måneds salgsværdi uden at ændre den indirekte excelformel for hver måned. Dette er specialiteten i INDIRECT-funktionen i Excel.
Hvordan tilpasser du tabelarrayet i INDIRECT Excel VLOOKUP-funktionen?
Vi har fem lande og en søgekanal og det salg, som hjemmesiden foretog gennem disse forskellige kanaler fra forskellige lande. Nu, i celle B3, ønsker vi at få det salg, der var gennem organiske søgninger fra land USA.
For hver tabel for forskellige lande har vi navngivet hver tabel med et kort navn for landet, Ind for Indien, Stater for USA, Can for Canada osv .
Hvis vi direkte bruger INDIRECT Excel vlookup-funktionen til at få værdien, som gjort nedenfor
Vi får en fejl, fordi INDIRECT Excel vlookup-funktionen ikke er i stand til at genkende tabelarrayet, der er videregivet som B2-celleværdien, så vi vil bruge INDIRECT-funktionen i excel til at få vlookup til at få table_array anerkendt som et gyldigt argument.
Hvis vi ændrer værdierne for B1 og B2, får vi forskellige salgsværdier i B3 efter behov.
Bemærk: Navneområder er ikke store og små bogstaver. Derfor - Ind, IND og InD er alle de samme med Excel.
I det næste INDIRECT Excel-eksempel bruger vi INDIRECT-funktionen i excel til at oprette en afhængig liste. Vi har en liste over en medarbejders stilling i en virksomhed; så for hver stilling har vi medarbejdernes navne.
Nu, i celle I2, opretter vi en liste for forskellige medarbejderranger som deres positioner, og i I3 viser vi en liste, der afhænger af værdien i I2. Hvis I2 er instruktør, skal I3 afspejle navnene på instruktører, som Andrew og Micheal; hvis I2 er Manager, skal I3 afspejle navnene på den manager, der er Camille, David, Davis og William og på samme måde for supervisoren.
I dette tilfælde bruger vi igen funktionen INDIRECT i excel til at oprette den afhængige liste. Datavalidering, i indstillinger, der vælger valideringskriterier som en liste og i kildefeltet, bruger vi INDIRECT-funktionen i Excel som følger.
= INDIREKTE ($ I $ 2)
Når vi vælger instruktørnavne i I3 afspejler som en liste med navne Andrew og Micheal, når I2 er Manager, så reflekterer I3 navnene på manager Camille, David, Davis og William og tilsvarende for supervisoren som vist nedenfor i figuren.
Ting at huske
- Argumentet ref_text skal være en gyldig cellereference, ellers returnerer INDIRECT #REF! Fejl. Argumentet ref_text kan henvise til en anden projektmappe.
- Hvis ref_text henviser til en anden projektmappe (en ekstern reference), skal den anden projektmappe være åben. Hvis kildearbejdsmappen ikke er åben, returnerer INDIRECT #REF! Fejlværdi.
- Hvis ref_text henviser til et celleområde uden for rækkegrænsen på 1.048.576 eller kolonnegrænsen på 16.384 (XFD), returnerer INDIRECT et #REF! Fejl.
