Inhoud
Gefeliciteerd! Je hebt de laatste dag van de marathon gehaald 30 Excel-functies in 30 dagen. Het is een lange en interessante reis geweest waarin je veel nuttige dingen hebt geleerd over Excel-functies.
Op de 30e dag van de marathon zullen we de studie van de functie wijden INDIRECTE (INDIRECT), die de link retourneert die is opgegeven door de tekenreeks. Met deze functie kunt u afhankelijke vervolgkeuzelijsten maken. Als u bijvoorbeeld een land selecteert in een vervolgkeuzelijst, wordt bepaald welke opties in de stadsvervolgkeuzelijst verschijnen.
Laten we het theoretische deel van de functie eens nader bekijken INDIRECTE (INDIRECT) en verken praktische voorbeelden van de toepassing ervan. Als u aanvullende informatie of voorbeelden heeft, deel deze dan in de opmerkingen.
Functie 30: INDIRECTE
Functie INDIRECTE (INDIRECT) retourneert de link die is opgegeven door de tekenreeks.
Hoe kunt u de INDIRECT-functie gebruiken?
Omdat de functie INDIRECTE (INDIRECT) retourneert een link gegeven door een tekenreeks, u kunt deze gebruiken om:
- Maak een niet-verschuivende eerste link.
- Maak een verwijzing naar een statisch benoemd bereik.
- Maak een koppeling met blad-, rij- en kolomgegevens.
- Maak een niet-verschuivende reeks getallen.
Syntaxis INDIRECT (INDIRECT)
Functie INDIRECTE (INDIRECT) heeft de volgende syntaxis:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- ref_tekst (link_to_cell) is de tekst van de link.
- a1 – indien gelijk aan TRUE (TRUE) of niet gespecificeerd, dan wordt de stijl van de link gebruikt A1; en indien ONWAAR (FALSE), dan is de stijl R1C1.
Vallen INDIRECTE (INDIRECT)
- Functie INDIRECTE (INDIRECT) wordt herberekend wanneer de waarden in het Excel-werkblad veranderen. Dit kan uw werkmap aanzienlijk vertragen als de functie in veel formules wordt gebruikt.
- Als de functie INDIRECTE (INDIRECT) maakt een koppeling naar een andere Excel-werkmap, die werkmap moet open zijn of de formule geeft een fout weer #REF! (#KOPPELING!).
- Als de functie INDIRECTE (INDIRECT) verwijst naar een bereik dat de rij- en kolomlimiet overschrijdt, de formule rapporteert een fout #REF! (#KOPPELING!).
- Functie INDIRECTE (INDIRECT) kan niet verwijzen naar een dynamisch benoemd bereik.
Voorbeeld 1: Maak een niet-verschuivende initiële link
In het eerste voorbeeld bevatten kolommen C en E dezelfde getallen, hun sommen berekend met de functie SOM (SUM) zijn ook hetzelfde. De formules zijn echter iets anders. In cel C8 is de formule:
=SUM(C2:C7)
=СУММ(C2:C7)
In cel E8 is de functie INDIRECTE (INDIRECT) maakt een link naar de startcel E2:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
Als u een rij bovenaan het blad invoegt en de waarde voor januari (jan) toevoegt, verandert het bedrag in kolom C niet. De formule zal veranderen als reactie op de toevoeging van een regel:
=SUM(C3:C8)
=СУММ(C3:C8)
Echter, de functie INDIRECTE (INDIRECT) stelt E2 vast als de startcel, dus januari wordt automatisch meegenomen in de berekening van kolom E-totalen. De eindcel is gewijzigd, maar de startcel is niet gewijzigd.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
Voorbeeld 2: Link naar een statisch benoemd bereik
Functie INDIRECTE (INDIRECT) kan een verwijzing naar een benoemd bereik maken. In dit voorbeeld vormen de blauwe cellen het bereik NumLijst. Daarnaast wordt er ook een dynamisch bereik gemaakt van de waarden in kolom B NumLijstDyn, afhankelijk van het aantal cijfers in deze kolom.
De som voor beide bereiken kan worden berekend door simpelweg de naam als argument aan de functie te geven SOM (SOM), zoals u kunt zien in de cellen E3 en E4.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
In plaats van een bereiknaam in een functie te typen SOM (SOM), U kunt verwijzen naar de naam die in een van de cellen van het werkblad is geschreven. Als bijvoorbeeld de naam NumLijst is geschreven in cel D7, dan ziet de formule in cel E7 er als volgt uit:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
Helaas de functie INDIRECTE (INDIRECT) kan geen verwijzing naar dynamisch bereik maken, dus wanneer u deze formule naar cel E8 kopieert, krijgt u een foutmelding #REF! (#KOPPELING!).
Voorbeeld 3: Maak een koppeling met blad-, rij- en kolominformatie
U kunt eenvoudig een koppeling maken op basis van de rij- en kolomnummers, evenals de waarde FALSE (FALSE) gebruiken voor het tweede functieargument INDIRECTE (INDIRECT). Zo wordt de stijllink gemaakt R1C1. In dit voorbeeld hebben we bovendien de bladnaam toegevoegd aan de link – 'MyLinks'!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
Voorbeeld 4: Maak een niet-verschuivende reeks getallen
Soms moet u een reeks getallen gebruiken in Excel-formules. In het volgende voorbeeld willen we het gemiddelde nemen van de 3 grootste getallen in kolom B. De getallen kunnen in een formule worden ingevoerd, zoals in cel D4 wordt gedaan:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
Als u een grotere matrix nodig heeft, is het onwaarschijnlijk dat u alle getallen in de formule wilt invoeren. De tweede optie is om de functie te gebruiken: RIJ (RIJ), zoals gedaan in de matrixformule die is ingevoerd in cel D5:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
De derde optie is om de functie te gebruiken: RIJ (STRING) samen met INDIRECTE (INDIRECT), zoals gedaan met de matrixformule in cel D6:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
Het resultaat voor alle 3 formules zal hetzelfde zijn:
Als rijen echter bovenaan het blad worden ingevoegd, geeft de tweede formule een onjuist resultaat omdat de verwijzingen in de formule samen met de rijverschuiving veranderen. Nu, in plaats van het gemiddelde van de drie grootste getallen, retourneert de formule het gemiddelde van de 3e, 4e en 5e grootste getallen.
Functies gebruiken INDIRECTE (INDIRECT), de derde formule behoudt de juiste rijverwijzingen en blijft het juiste resultaat tonen.