Bekijk video - Intersect-operator gebruiken in Excel
Intersect-operator in Excel kan worden gebruikt om de kruisende waarde(n) van twee lijsten/bereiken te vinden. Dit is een ongebruikelijke operator omdat het wordt weergegeven door een spatie (ja dat klopt).
Als u een spatie tussen twee bereiken gebruikt, wordt dit de Intersect-operator in Excel.
Intersect-operator in Excel
U kunt Intersect Operator in Excel gebruiken om het volgende te vinden:
- Het snijpunt van een enkele rij en kolom.
- Het snijpunt van meerdere rijen en kolommen.
- De kruising van Named Ranges.
Snijpunt van een enkele rij en kolom
Stel dat er een dataset is zoals hieronder weergegeven:
Als u nu =C2:C13 B5:D5 [Merk op dat er een enkele spatie tussen de bereiken is, wat ook onze intersect-operator is in Excel], retourneert het 523 (de waarde in cel C5), wat het snijpunt is van deze 2 bereiken.
Snijpunt van meerdere rijen en kolommen
U kunt dezelfde techniek ook gebruiken om het snijpunt te vinden van bereiken die meer dan één rij of kolom beslaan. Met dezelfde gegevensset als hierboven kunt u bijvoorbeeld de kruising van Product 1 en Product 2 in april krijgen.
Hier is de formule die dat kan doen: =B2:C13 B5:D5
Merk op dat het resultaat van deze formule een waardefout zou weergeven, maar wanneer u de formule selecteert en op F9 drukt, wordt het resultaat weergegeven als {649.523}. Deze formule retourneert een matrix van de snijpuntwaarden. U kunt dit gebruiken in formules, zoals SOM (om het totaal van de snijpuntwaarden te krijgen) of MAX (om het maximum van de snijpuntwaarden te krijgen).
Snijpunt van benoemde bereiken
U kunt ook benoemde bereiken gebruiken om de kruising te vinden met behulp van de Intersect-operator in Excel.
Hier is een voorbeeld waarbij ik de product 1-waarden heb genoemd als Prdt1, Product 2-waarden als Prdt2 en april-waarden als april.
Nu kunt u de formule =Prdt1 Apr gebruiken om het snijpunt van deze 2 bereiken te krijgen. Op dezelfde manier kunt u =Prdt1:Prdt2 Apr gebruiken om de kruising van Product 1, Product 2 en april te krijgen.
Een praktisch voorbeeld van het gebruik van Intersect Operator in Excel
Hier is een situatie waarin deze truc van pas kan komen. Ik heb een dataset van Sales Rep en de verkopen die ze in 2012 elke maand hebben gemaakt.
Ik heb ook een vervolgkeuzelijst gemaakt met Vertegenwoordiger Naam in één cel en Maand naam in een andere, en ik wil de verkopen extraheren die de vertegenwoordiger in die maand deed.
Iets zoals hieronder weergegeven:
Hoe maak je dit aan:
- Selecteer de volledige gegevensset (B3:N13) en druk op Control + Shift + F3 om benoemde bereiken te maken (dit kan ook worden gedaan via Formule -> Gedefinieerde namen -> Maken op basis van selectie). Dit opent een dialoogvenster 'Namen maken uit selectie'.
- Selecteer de opties 'Bovenste rij' en 'Linkerkolom' en klik op OK.
- Hiermee worden benoemde bereiken gemaakt voor alle verkoopvertegenwoordigers en de hele maand.
- Ga nu naar cel B16 en maak een vervolgkeuzelijst voor alle verkopers.
- Ga op dezelfde manier naar cel C15 en maak een vervolgkeuzelijst voor alle maanden.
- Gebruik nu in cel C16 de volgende formule =INDIRECT(B16)INDIRECT(C15)
Hoe werkt het?
Merk op dat er een spatie is tussen de twee INDIRECTE formules.
De functie INDIRECT retourneert het bereik voor de benoemde bereiken - Verkoper en de maand, en de spatie ertussen werkt als een intersect-operator en retourneert de snijdende waarde.
Opmerking: deze operator voor onzichtbare snijpunten krijgt voorrang op andere operatoren. Dus als u in dit geval =INDIRECT(B16) INDIRECT(C15)>5000 gebruikt, wordt WAAR of ONWAAR geretourneerd op basis van de kruisende waarde.
