Inhoud
Op het eerste gezicht (vooral bij het lezen van de help), is de functie INDIRECTE (INDIRECT) ziet er eenvoudig en zelfs onnodig uit. De essentie is om tekst die op een link lijkt om te zetten in een volwaardige link. Die. als we naar cel A1 moeten verwijzen, kunnen we gewoonlijk een directe link maken (voer een gelijkteken in D1, klik op A1 en druk op Enter), of we kunnen INDIRECTE voor hetzelfde doel:
Houd er rekening mee dat het functieargument – een verwijzing naar A1 – tussen aanhalingstekens staat, zodat hier in feite tekst is.
“Nou, oké”, zeg je. “En wat is het voordeel?”
Maar oordeel niet op de eerste indruk - het is bedrieglijk. Deze functie kan u in veel situaties helpen.
Voorbeeld 1. Transponeren
Een klassieker van het genre: je moet de verticale dia
groef naar horizontaal (transponeren). Je kunt natuurlijk een speciale inzet of functie gebruiken TRANSP (TRANSPONEREN) in een matrixformule, maar je kunt rondkomen met onze INDIRECTE:
De logica is eenvoudig: om het adres van de volgende cel te krijgen, lijmen we de letter "A" met het speciale teken "&" en het kolomnummer van de huidige cel, die de functie ons geeft COLUMN (KOLOM).
De omgekeerde procedure kan beter iets anders worden gedaan. Sinds deze tijd moeten we een link vormen naar cellen B2, C2, D2, enz., Het is handiger om de R1C1-linkmodus te gebruiken in plaats van de klassieke "zeeslag". In deze modus zullen onze cellen alleen verschillen in het kolomnummer: B2=R1C2, C2=R1C3, D2=R1C4 enz.
Dit is waar het tweede optionele functieargument binnenkomt. INDIRECTE. Als het gelijk is LIEGEN (ONWAAR), dan kunt u het linkadres instellen in de R1C1-modus. Dus we kunnen het horizontale bereik gemakkelijk terug naar verticaal transponeren:
Voorbeeld 2. Som per interval
We hebben al een manier geanalyseerd om over een venster (bereik) van een bepaalde grootte op een blad op te tellen met behulp van de functie VERWIJDEREN (VERSCHUIVING). Een soortgelijk probleem kan ook worden opgelost met INDIRECTE. Als we alleen gegevens uit een bepaalde bereikperiode moeten samenvatten, dan kunnen we deze uit stukjes lijmen en er dan een volwaardige link van maken, die we in de functie kunnen invoegen SOM (SOM):
Voorbeeld 3. Vervolgkeuzelijst Slimme tabel
Soms behandelt Microsoft Excel namen en kolommen van slimme tabellen niet als volledige koppelingen. Dus, bijvoorbeeld, wanneer u probeert een vervolgkeuzelijst te maken (tab Gegevens – Gegevensvalidatie) op basis van kolom Werknemers van slimme tafel Mensen we krijgen een foutmelding:
Als we de link "inpakken" met onze functie INDIRECTE, dan accepteert Excel het gemakkelijk en wordt onze vervolgkeuzelijst dynamisch bijgewerkt wanneer nieuwe medewerkers aan het einde van de slimme tabel worden toegevoegd:
Voorbeeld 4. Onbreekbare links
Zoals u weet, corrigeert Excel automatisch referentieadressen in formules bij het invoegen of verwijderen van rijkolommen op een blad. In de meeste gevallen is dit correct en handig, maar niet altijd. Laten we zeggen dat we de namen van de werknemersdirectory naar het rapport moeten overbrengen:
Als u reguliere links plaatst (voer =B2 in de eerste groene cel in en kopieer deze naar beneden), dan krijgen we de #LINK! fout in de groene cel die met haar overeenkomt. (#REF!). In het geval van het gebruik van de functie om koppelingen te maken INDIRECTE zo'n probleem zal er niet zijn.
Voorbeeld 5: Gegevens verzamelen van meerdere bladen
Stel dat we 5 bladen hebben met rapporten van hetzelfde type van verschillende medewerkers (Mikhail, Elena, Ivan, Sergey, Dmitry):
Laten we aannemen dat de vorm, grootte, positie en volgorde van goederen en maanden in alle tabellen hetzelfde zijn - alleen de aantallen verschillen.
Met één formule kun je gegevens van alle bladen verzamelen (niet optellen, maar onder elkaar leggen in een “stapeltje”):
Zoals je kunt zien, is het idee hetzelfde: we lijmen de link aan de gewenste cel van het gegeven blad, en INDIRECTE maakt er een "live" van. Voor het gemak heb ik boven de tabel de letters van de kolommen (B,C,D) toegevoegd en aan de rechterkant de regelnummers die van elk blad moeten worden genomen.
Valkuilen
Als u gebruik maakt van INDIRECTE (INDIRECT) je moet onthouden over de zwakke punten:
- Als je naar een ander bestand linkt (door de bestandsnaam tussen vierkante haken te plakken, de bladnaam en het celadres), dan werkt het alleen als het originele bestand open is. Als we het sluiten, krijgen we de foutmelding #LINK!
- INDIRECT kan niet verwijzen naar een dynamisch benoemd bereik. Op statisch - geen probleem.
- INDIRECT is een vluchtige of "vluchtige" functie, dwz het wordt herberekend voor elke verandering in een cel van het blad, en niet alleen voor het beïnvloeden van cellen, zoals bij normale functies. Dit heeft een slechte invloed op de prestaties en je kunt je beter niet laten meeslepen door grote INDIRECTE tafels.
- Een dynamisch bereik maken met automatische grootte
- Optellen over een bereikvenster op een blad met de OFFSET-functie