Video ansehen - Verwenden des Intersect-Operators in Excel
Schnittoperator in Excel kann verwendet werden, um den (die) Schnittwert(e) von zwei Listen/Bereichen zu finden. Dies ist ein ungewöhnlicher Operator, da er durch ein Leerzeichen dargestellt wird (ja, das ist richtig).
Wenn Sie ein Leerzeichen zwischen zwei Bereichen verwenden, wird es in Excel zum Intersect-Operator.
Schnittoperator in Excel
Sie können den Schnittoperator in Excel verwenden, um Folgendes zu finden:
- Der Schnittpunkt einer einzelnen Zeile und Spalte.
- Der Schnittpunkt mehrerer Zeilen und Spalten.
- Der Schnittpunkt der benannten Bereiche.
Schnittmenge einer einzelnen Zeile und Spalte
Angenommen, es gibt einen Datensatz wie unten gezeigt:
Wenn Sie nun =C2:C13 B5:D5 [Beachten Sie, dass sich zwischen den Bereichen ein einzelnes Leerzeichen befindet, das auch unser Schnittoperator in Excel ist], wird 523 (der Wert in Zelle C5) zurückgegeben, was der Schnittpunkt dieser beiden Bereiche ist.
Schnittmenge mehrerer Zeilen und Spalten
Sie können das gleiche Verfahren auch verwenden, um die Schnittmenge von Bereichen zu finden, die sich über mehr als eine Zeile oder Spalte erstrecken. Beispielsweise können Sie mit demselben Datensatz wie oben gezeigt die Schnittmenge von Produkt 1 und Produkt 2 im April ermitteln.
Hier ist die Formel, die das tun kann: =B2:C13 B5:D5
Beachten Sie, dass das Ergebnis dieser Formel einen Wertfehler anzeigen würde. Wenn Sie jedoch die Formel auswählen und F9 drücken, wird das Ergebnis als {649,523} angezeigt. Diese Formel gibt ein Array der Schnittmengenwerte zurück. Sie können dies in Formeln verwenden, z. B. SUM (um die Summe der Schnittwerte zu erhalten) oder MAX (um das Maximum der Schnittwerte zu erhalten).
Schnittmenge benannter Bereiche
Sie können auch benannte Bereiche verwenden, um den Schnittpunkt mithilfe des Schnittpunktoperators in Excel zu finden.
Hier ist ein Beispiel, in dem ich die Werte von Produkt 1 als Prdt1, Werte von Produkt 2 als Prdt2 und April-Werte als Apr bezeichnet habe.
Jetzt können Sie die Formel =Prdt1 Apr verwenden, um den Schnittpunkt dieser 2 Bereiche zu erhalten. Ebenso können Sie =Prdt1:Prdt2 Apr verwenden, um die Schnittmenge von Produkt 1, Produkt 2 und April zu erhalten.
Ein praktisches Beispiel für die Verwendung des Intersect-Operators in Excel
Hier ist eine Situation, in der dieser Trick nützlich sein könnte. Ich habe einen Datensatz mit Vertriebsmitarbeitern und den Verkäufen, die sie in jedem Monat im Jahr 2012 gemacht haben.
Ich habe auch eine Dropdown-Liste erstellt mit Vertriebsmitarbeiter Name in einer Zelle und Monat Namen in einem anderen, und ich möchte die Verkäufe extrahieren, die der Vertreter in diesem Monat gemacht hat.
Etwas wie unten gezeigt:
So erstellen Sie dies:
- Wählen Sie den gesamten Datensatz (B3:N13) aus und drücken Sie Strg + Umschalt + F3, um benannte Bereiche zu erstellen (dies kann auch über Formel -> Definierte Namen -> Aus Auswahl erstellen erfolgen). Dadurch wird ein Dialogfeld "Namen aus Auswahl erstellen" geöffnet.
- Wählen Sie die Optionen „Obere Reihe“ und „Linke Spalte“ und klicken Sie auf OK.
- Dadurch werden benannte Bereiche für alle Vertriebsmitarbeiter und den ganzen Monat erstellt.
- Gehen Sie nun zu Zelle B16 und erstellen Sie eine Dropdown-Liste für alle Vertriebsmitarbeiter.
- Gehen Sie in ähnlicher Weise zu Zelle C15 und erstellen Sie eine Dropdown-Liste für alle Monate.
- Verwenden Sie nun in Zelle C16 die folgende Formel = INDIREKT (B16) INDIREKT (C15)
Wie funktioniert es?
Beachten Sie, dass sich zwischen den beiden INDIREKTEN Formeln ein Leerzeichen befindet.
Die Funktion INDIREKT gibt den Bereich für die benannten Bereiche zurück – Vertriebsmitarbeiter und Monat, und der Abstand zwischen ihnen fungiert als Schnittpunktoperator und gibt den Schnittpunktwert zurück.
Hinweis: Dieser unsichtbare Schnittpunktoperator hat Vorrang vor anderen Operatoren. Wenn Sie also in diesem Fall =INDIRECT(B16) INDIRECT(C15)>5000 verwenden, wird basierend auf dem Schnittwert WAHR oder FALSCH zurückgegeben.
