INDIRECTE functie in Excel
Indirecte functie in Excel is een ingebouwde functie die wordt gebruikt om te verwijzen naar en celwaarden uit een tekstreeks te halen, deze formule neemt verwijzing van de cel waarnaar wordt verwezen, het heeft twee argumenten, het eerste argument is niet optioneel, terwijl het tweede argument optioneel is dat verwijst naar het type overeenkomst, deze functie kan ook worden gebruikt met een andere formule.
INDIRECTE functie in Excel heeft meerdere toepassingen. Het retourneert de celverwijzing gespecificeerd door een tekstreeks. Als cel B1 bijvoorbeeld een tekenreeks bevat, hebben bijvoorbeeld het bereik c4 en c4 de waarde 'Jessica'. Als we nu de functie INDIRECT gebruiken en de referentietekst doorgeven als B1, wordt de waarde geretourneerd die is opgenomen in celbereik c4 dat "Jessica" is.
Met de INDIRECTE functie van Excel kunt u een celadres INDIRECT specificeren. Zoals u kunt in het bovenstaande INDIRECTE Excel-voorbeeld, als cel B1 de tekst C4 bevat, retourneert deze indirecte Excel-formule de inhoud van de cel C4 ( Jessica ). Deze functie is een buitengewoon nuttige functie. U kunt de functie INDIRECTE gebruiken wanneer u de verwijzing naar een cel binnen een indirecte Excel-formule wilt wijzigen zonder de formule zelf te wijzigen.
INDIRECTE Excel-formule
Uitleg
Ref_text is een verwijzing naar een cel die een verwijzing in A1-stijl, een verwijzing in R1C1-stijl, een naam die als verwijzing is gedefinieerd, of een verwijzing naar een cel als een tekstreeks bevat.
a1 argument is optioneel
| a1 | False of 0, wanneer ref_text in R1C1-stijl is |
| a1 | Weggelaten of True (1) als ref_text de stijl A1 heeft |
Hoe de INDIRECTE functie in Excel te gebruiken?
Laten we een INDIRECT Excel-voorbeeld nemen voordat we de adresfunctie in de Excel-werkmap gebruiken:
Stel dat we voor een website www.xyz.com de Google-analysegegevens hebben voor bezoekers van de website van verschillende doelgroepkanalen voor vijf verschillende landen, zoals hieronder in de tabel wordt weergegeven:
We kunnen de functie INDIRECT in Excel gebruiken om een tekstreeks om te zetten in een verwijzing, dus verwijzend naar cel H4 en die naam als referentie gebruiken. Het gebruik van de functie INDIRECT in Excel, in dit geval, is om te verwijzen naar naambereiken met behulp van de waarden van een cel, dus in cel I4 zouden we eenvoudig de functie som kunnen gebruiken om het totale aantal bezoekers uit een land Canada te berekenen.
Nu kunnen we direct de som van bezoekers uit elk land berekenen met behulp van de somfunctie, maar door de INDIRECTE functie in Excel te gebruiken, kunnen we de som van bezoekers uit verschillende landen berekenen door de naamreferentie te wijzigen in plaats van de indirecte Excel-formule zelf te wijzigen.
We hebben het naambereik gemaakt voor elke landbezoeker; dit kan eenvoudig worden gedaan door het naambereik in de ruimte net onder het klembord te typen, zoals hieronder wordt weergegeven.
Een andere methode is dat u naar formules-> naambeheer -> selecteer het bereik dat u een naam wilt geven -> klik op naambeheer -> typ het naambereik.
Evenzo creëerde het een benoemde reeks in Excel voor bezoekers uit andere landen.
Nu, in I4, de som van de bezoeker uit het land Canada berekenen met behulp van de INDIRECTE functie en het naambereik in H4 gebruiken (Canada, een naam hierboven voor F3: F7), krijgen we de totale som van een bezoeker.
Als we een datalijst maken met behulp van de datavalidatie voor de landen en die lijst toepassen op H4
En als we de verschillende landnamen wijzigen van Canada naar India, de VS, Australië of Singapore, krijgen we de totale som van de bezoeker in I4 met behulp van de INDIRECTE functie in Excel.
Dus voor elk geval wijzigen we niet de indirecte Excel-formule, die zich in cel I4 bevindt; we veranderen de naamreferentie in H4 en berekenen de som van de bezoekers voor elk land.
We kunnen nog een stap verder gaan en kunnen verwijzen naar namen op werkbladniveau, ook met de functie INDIRECT in Excel. We zullen nu bijvoorbeeld de functie INDIRECT gebruiken met verwijzing naar verschillende bladen in de werkmap.
We hebben verschillende bladen gemaakt met de landnamen voor het totale aantal verkopen dat met elk zoekkanaal voor verschillende landen is gedaan.
Voor India,
Voor de VS,
Evenzo voor andere landen.
Nogmaals, we zullen de totale verkoop via elk kanaal berekenen met behulp van de INDIRECTE functie in Excel voor verschillende bladen met landnamen als referentie.
In dit geval, aangezien we verkoopgegevens in verschillende bladen hebben, moeten we verschillende elementen samenvoegen die het gegevensbereik vormen, dus de ref_text begint met de naam van het blad, wat in dit geval India is in B4, en dan zullen we gebruik de operator & genaamd ampersand die wordt gebruikt voor aaneenschakeling.
Dus we krijgen de totale som van de verkopen voor India; Als we nu de landnaam van het blad in B4 wijzigen van India naar andere landen zoals de VS, Singapore of Canada, krijgen we de totale verkoopwaarde van die landen.
Nogmaals, in dit geval hebben we eenvoudigweg de ref_text- waarden in cel B4 gewijzigd zonder de belangrijkste indirecte Excel-formule, die in C4 staat, te wijzigen om de som van de verkopen te krijgen voor verschillende landen die zich in verschillende bladen bevinden met behulp van de INDIRECT-functie.
Nu gaan we de functie INDIRECT gebruiken met behulp van de R1C1-referentie om de verkoopwaarde van de afgelopen maand te krijgen. We hebben verkopen voor verschillende maanden in de kolommen B, C en D gedurende drie maanden Jan, Feb en Mar die kunnen oplopen tot maanden zoals april, mei, juni, enzovoort. De verkooptafel zal dus altijd groeien, afhankelijk van de toevoeging van de komende maandverkoop. Dus in dit INDIRECTE Excel-voorbeeld willen we de verkoop van de afgelopen maand berekenen.
Dus hier is de laatste maand maart, en we willen de totale verkoop van de maand maart berekenen, die $ 249.344 is, met behulp van de INDIRECTE functie.
In dit INDIRECTE Excel-voorbeeld vinden we het gebruik van het tweede argument van de INDIRECTE functie
INDIRECT (ref_tekst, a1)
a1: wat een optioneel argument is, het is een logische waarde (een Booleaanse waarde) die specificeert welk type verwijzing (ref_text) in de cel zit
als a1 waar (1) is of is weggelaten, wordt ref_text beschouwd als een referentie in A1-stijl
als a1 false (0) is, wordt ref_text geïnterpreteerd als een R1C1-stijl
Waarin verschilt de A1-stijl van de R1C1-stijl?
Normaal gesproken gebruikt de INDIRECTE functie in Excel de notatie A1. Elk celadres bestaat uit een kolomletter en een rijnummer. De functie INDIRECT in Excel ondersteunt echter ook R1C1-notatie. In dit systeem wordt cel A1 cel R1C1 genoemd, cel A2 R2C1, enzovoort.
Om naar R1C1-notatie te veranderen, kiest u Bestand-> optie om het Excel-opties dialoogvenster te openen, klikt u op het formule-tabblad en een vinkje naar de R1C1-referentiestijl. Nu zult u merken dat de kolomletters allemaal veranderen in cijfers. En alle celverwijzingen in uw formules worden ook aangepast.
Dus als we de R1C1-stijl gebruiken in de INDIRECT-functie in Excel, geven we de a1-waarde door als false, en als we de A1-stijl gebruiken, dan geven we de waarde true door aan a1.
Nu, in dit INDIRECTE Excel-voorbeeld, zullen we de R1C1-stijl gebruiken vanwege de vereiste, aangezien de kolommen met maand zouden groeien.
Met behulp van de R1C1-stijl bevindt de totale verkoopwaarde zich in rij 10 en wordt de laatste kolomwaarde, die in de D-kolom staat, berekend met de functie COUNTA , die het laatste kolomnummer met een waarde geeft. Dus tel (10:10) geeft in dit geval 4, het kolomnummer in R1C1-stijl. We hebben dus contact opgenomen met die waarde om R10C4 te krijgen die de gewenste waarde bevat, namelijk de totale verkoopwaarde van de afgelopen maand ($ 249.344).
En aangezien we hier de stijl R1C1 gebruiken, zullen we een valse waarde doorgeven voor het argument a1.
Laten we nu nog een kolom toevoegen met de maand apriluitverkoop.
Ontvang dus nieuwe verkoopwaarden van vorige maand die overeenkomen met die van de laatste maand van april. Evenzo, als we meer maanden toevoegen, krijgen we altijd de nieuwe verkoopwaarde van vorige maand zonder de indirecte Excel-formule voor elke maand te wijzigen. Dit is de specialiteit van de INDIRECTE functie in Excel.
Hoe de tabelmatrix aanpassen in de INDIRECTE Excel VERT.ZOEKEN-functie?
We hebben vijf landen en een zoekkanaal en de verkoop die de website via deze verschillende kanalen heeft gedaan uit verschillende landen. Nu willen we in cel B3 de verkoop krijgen via organische zoekopdrachten uit het land Verenigde Staten.
Voor elke tafel voor verschillende landen hebben we elke tafel een korte naam gegeven voor het land, Ind voor India, Staten voor de VS, Can voor Canada, enzovoort .
Als we de INDIRECT Excel-vlookup-functie rechtstreeks gebruiken om de waarde te krijgen, zoals hieronder wordt gedaan
We krijgen een foutmelding omdat de INDIRECT Excel-vlookup-functie de tabelmatrix niet kan herkennen die is doorgegeven als de waarde van de B2-cel, dus we zullen de INDIRECT-functie in Excel gebruiken om ervoor te zorgen dat de vlookup de table_array herkent als een geldig argument.
Als we de waarden van B1 en B2 wijzigen, krijgen we, zoals vereist, verschillende verkoopwaarden in B3.
Opmerking: naambereiken zijn niet hoofdlettergevoelig. Daarom - Ind, IND en InD zijn allemaal hetzelfde voor Excel.
In het volgende INDIRECTE Excel-voorbeeld zullen we de INDIRECTE functie in Excel gebruiken om een afhankelijke lijst te maken. We hebben een lijst met de functie van een werknemer in een bedrijf; vervolgens hebben we voor elke functie de namen van de medewerkers.
Nu zullen we in cel I2 een lijst maken voor verschillende werknemersrangen als hun posities, en in I3 zullen we een lijst tonen die afhankelijk is van de waarde in I2. Als I2 een regisseur is, dan moet I3 de namen van bestuurders weerspiegelen die Andrew en Micheal; als I2 Manager is, dan zou I3 de namen moeten weergeven van de manager die Camille, David, Davis en William is, en op dezelfde manier voor de supervisor.
In dit geval zullen we opnieuw de INDIRECTE functie in Excel gebruiken om de afhankelijke lijst te maken. Gegevensvalidatie, in instellingen die validatiecriteria selecteren als een lijst en in het bronveld, zullen we de INDIRECT-functie in Excel als volgt gebruiken.
= INDIRECT ($ I $ 2)
Wanneer we de namen van regisseurs selecteren in I3 weerspiegelt als een lijst met namen Andrew en Micheal, wanneer I2 Manager is, dan geeft I3 de namen weer van manager Camille, David, Davis en William en op dezelfde manier voor de supervisor, zoals hieronder in de afbeelding wordt weergegeven.
Dingen om te onthouden
- Het argument ref_text moet een geldige celverwijzing zijn, anders retourneert INDIRECT de #REF! Fout. Het argument ref_text kan verwijzen naar een andere werkmap.
- Als ref_text verwijst naar een andere werkmap (een externe referentie), moet de andere werkmap geopend zijn. Als de bronwerkmap niet is geopend, geeft INDIRECT de foutwaarde #REF! Foutwaarde.
- Als ref_text verwijst naar een celbereik buiten de rijlimiet van 1.048.576 of de kolomlimiet van 16.384 (XFD), geeft INDIRECT een #REF! Fout.
