Excel

Zelle ist gleich eines von vielen Dingen

Cell Equals One Many Things

Excel-Formel: Zelle entspricht einem von vielen DingenAllgemeine Formel |_+_| Zusammenfassung

Wenn Sie eine Zelle testen möchten, um festzustellen, ob sie einem von mehreren Dingen entspricht, können Sie dies mit einer Formel tun, die die SUMMENPRODUKT-Funktion verwendet.





wie man Spiegel in Excel macht

Kontext

Angenommen, Sie haben eine Liste von Textzeichenfolgen im Bereich B5:B11 und möchten jede Zelle mit einer anderen Liste von Dingen im Bereich E5:E9 testen. Mit anderen Worten, für jede Zelle in B5:B11 möchten Sie wissen: Entspricht diese Zelle einem der Dinge in E5:E9?

Sie könnten beginnen, eine große Formel zu erstellen, die auf basiert verschachtelte IF-Anweisungen , aber eine auf SUMPRODUCT basierende Array-Formel ist ein einfacherer und saubererer Ansatz.





Lösung

Die Lösung besteht darin, eine Formel zu erstellen, die auf mehrere Werte testet und eine Liste mit TRUE / FALSE-Werten zurückgibt. Sobald wir das haben, können wir diese Liste (eigentlich ein Array) mit SUMPRODUCT verarbeiten.

Die von uns verwendete Formel sieht so aus:



wie man Box und Whisker Plot in Excel macht
= SUMPRODUCT (--(A1=things))>0
Erläuterung

Der Schlüssel ist dieser Ausschnitt:

 
= SUMPRODUCT (--(B5=things))>0

die einfach den Wert in B5 mit jedem Wert im benannten Bereich 'Dinge' vergleicht. Da wir B5 mit einem Array vergleichen (d. h. dem benannten Bereich 'Dinge', E5:E11), ist das Ergebnis ein Array mit TRUE / FALSE-Werten wie folgt:

{WAHRFALSCHFALSCHFALSCH}

Wenn wir nur ein WAHR im Array haben, wissen wir, dass B5 mindestens einer Sache in der Liste entspricht einstellig). Nach diesem Zwang haben wir folgendes:

{10000}

wie man Pivot-Tabellen schnell lernt

Jetzt verarbeiten wir das Ergebnis mit SUMPRODUCT, wodurch die Elemente im Array addiert werden. Wenn wir ein Ergebnis ungleich Null erhalten, haben wir mindestens eine Übereinstimmung, also verwenden wir >1, um ein Endergebnis von entweder WAHR oder FALSCH zu erzwingen.

Mit einer hartcodierten Liste

Es ist nicht erforderlich, dass Sie einen Bereich für Ihre Liste der Dinge verwenden. Wenn Sie nur nach wenigen Dingen suchen, können Sie eine Liste im Array-Format verwenden, die als Array-Konstante bezeichnet wird. Wenn Sie beispielsweise nur nach den Farben Rot, Blau und Grün suchen, können Sie {'red','blue','green'} wie folgt verwenden:

 
--(B5=things)

Umgang mit zusätzlichen Räumen

Wenn die Zellen, die Sie testen, zusätzlichen Leerraum enthalten, stimmen sie nicht richtig überein. Um den zusätzlichen Platz zu entfernen, können Sie die Formel ändern, um die TRIM-Funktion wie folgt zu verwenden:

 
--(B5={'red','blue','green'})
Autor Dave Bruns


^