Innhold
Ved første øyekast (spesielt når du leser hjelpen), funksjonen INDIREKTE (INDIREKTE) ser enkelt ut og til og med unødvendig. Essensen er å gjøre tekst som ser ut som en lenke til en fullverdig lenke. De. hvis vi trenger å referere til celle A1, kan vi enten lage en direkte lenke (skriv inn et likhetstegn i D1, klikk på A1 og trykk Enter), eller vi kan bruke INDIREKTE til samme formål:
Vær oppmerksom på at funksjonsargumentet – en referanse til A1 – er lagt inn i anførselstegn, slik at det faktisk er tekst her.
"Vel, ok," sier du. "Og hva er fordelen?"
Men ikke døm etter førsteinntrykket – det er villedende. Denne funksjonen kan hjelpe deg i mange situasjoner.
Eksempel 1. Transponer
En klassiker av sjangeren: du må snu den vertikale dia
rille til horisontal (transponere). Selvfølgelig kan du bruke en spesiell innsats eller funksjon TRANSP (TRANSPOSER) i en matriseformel, men du kan klare deg med vår INDIREKTE:
Logikken er enkel: for å få adressen til neste celle limer vi bokstaven "A" med spesialtegnet "&" og kolonnenummeret til den gjeldende cellen, som funksjonen gir oss KOLONNE (KOLONNE).
Den omvendte prosedyren gjøres bedre litt annerledes. Siden denne gangen må vi danne en kobling til cellene B2, C2, D2, etc., er det mer praktisk å bruke R1C1-koblingsmodusen i stedet for den klassiske "sjøkampen". I denne modusen vil cellene våre bare avvike i kolonnenummeret: B2=R1C2, C2=R1C3, D2=R1C4 og så videre
Det er her det andre valgfrie funksjonsargumentet kommer inn. INDIREKTE. Hvis det er likt LØVER (FALSK), så kan du angi koblingsadressen i R1C1-modus. Så vi kan enkelt transponere det horisontale området tilbake til vertikalt:
Eksempel 2. Sum etter intervall
Vi har allerede analysert en måte å summere over et vindu (område) av en gitt størrelse på et ark ved å bruke funksjonen FJERNING (OFFSET). Et lignende problem kan også løses ved hjelp av INDIREKTE. Hvis vi bare trenger å oppsummere data fra en bestemt rekkeviddeperiode, kan vi lime dem fra stykker og deretter gjøre dem om til en fullverdig lenke, som vi kan sette inn i funksjonen SUM (SUM):
Eksempel 3. Nedtrekksliste for smart bord
Noen ganger behandler ikke Microsoft Excel smarte tabellnavn og kolonner som fullstendige lenker. Så for eksempel når du prøver å lage en rullegardinliste (tab Data – Datavalidering) basert på kolonne Ansatte fra smart bord porsjoner vi får en feilmelding:
Hvis vi "pakker inn" lenken med funksjonen vår INDIREKTE, da vil Excel enkelt godta det og rullegardinlisten vår oppdateres dynamisk når du legger til nye ansatte på slutten av smarttabellen:
Eksempel 4. Unbreakable Links
Som du vet, retter Excel automatisk referanseadresser i formler når du setter inn eller sletter radkolonner på et ark. I de fleste tilfeller er dette riktig og praktisk, men ikke alltid. La oss si at vi må overføre navnene fra ansattkatalogen til rapporten:
Hvis du legger inn vanlige lenker (skriv inn =B2 i den første grønne cellen og kopier den ned), så når du sletter for eksempel Dasha, får vi #LINK! feil i den grønne cellen som tilsvarer henne. (#REF!). Ved bruk av funksjonen til å lage lenker INDIREKTE det vil ikke være noe slikt problem.
Eksempel 5: Samle data fra flere ark
Anta at vi har 5 ark med rapporter av samme type fra forskjellige ansatte (Mikhail, Elena, Ivan, Sergey, Dmitry):
La oss anta at formen, størrelsen, posisjonen og rekkefølgen av varer og måneder i alle tabeller er like – bare tallene er forskjellige.
Du kan samle inn data fra alle ark (ikke oppsummere, men legg dem under hverandre i en "bunke") med bare én formel:
Som du kan se, er ideen den samme: vi limer lenken til ønsket celle i det gitte arket, og INDIREKTE gjør det til et "live". For enkelhets skyld, over tabellen, la jeg til bokstavene i kolonnene (B, C, D), og til høyre - linjenumrene som må tas fra hvert ark.
Fallgruver
Hvis du bruker INDIREKTE (INDIREKTE) du må huske på svakhetene:
- Hvis du lenker til en annen fil (ved å lime filnavnet i hakeparenteser, arknavnet og celleadressen), så fungerer det bare mens originalfilen er åpen. Hvis vi lukker den, får vi feilmeldingen #LINK!
- INDIREKTE kan ikke referere til et dynamisk navngitt område. På statisk - ikke noe problem.
- INDIREKTE er en flyktig eller "flyktig" funksjon, dvs. den beregnes på nytt for enhver endring i en hvilken som helst celle i arket, og ikke bare påvirkende celler, som i vanlige funksjoner. Dette har en dårlig effekt på ytelsen og det er bedre å ikke la seg rive med av store INDIREKTE bord.
- Hvordan lage et dynamisk område med automatisk størrelse
- Summering over et områdevindu på et ark med OFFSET-funksjonen