Manchmal müssen Sie nicht nur die größte oder maximale Anzahl für alle Ihre Daten ermitteln. Sie müssen die größte Zahl in einer Teilmenge ermitteln, z. B. die größte positive oder negative Zahl.
Wenn die Datenmenge gering ist, kann die Aufgabe leicht durch manuelle Auswahl des korrekten Bereichs für die MAX-Funktion ausgeführt werden.
Unter anderen Umständen, z. B. bei einem großen unsortierten Datenmuster, kann sich die richtige Auswahl des Bereichs als schwierig oder gar unmöglich erweisen.
Durch die Kombination der ZF-Funktion mit dem MAX in einer Arrayformel können Bedingungen - wie nur positive oder negative Zahlen - einfach eingestellt werden, sodass nur die Daten, die diesen Parametern entsprechen, von der Formel getestet werden.
MAX IF Array-Aufschlüsselung
Die in diesem Lernprogramm verwendete Formel zum Ermitteln der größten positiven Zahl lautet:
= MAX (IF (A1: B5> 0, A1: B5))
Hinweis: Das optionale Argument value_if_false der IF-Funktion wird weggelassen, um die Formel zu verkürzen. Falls die Daten im ausgewählten Bereich das eingestellte Kriterium - Zahlen größer als Null - nicht erfüllen, gibt die Formel eine Null zurück ( 0 )
Die Aufgabe jedes Teils der Formel ist:
- Die IF-Funktion filtert die Daten so, dass nur die Zahlen, die das gewählte Kriterium erfüllen, an die MAX-Funktion übergeben werden
- Die MAX-Funktion ermittelt den höchsten Wert für die gefilterten Daten
- Die Matrixformel - angezeigt durch die geschweiften Klammern { } um die Formel herum - Ermöglicht es dem logischen Testargument der IF-Funktion, den gesamten Datenbereich nach Übereinstimmungen zu durchsuchen, z. B. Zahlen größer als Null, und nicht nur eine einzelne Datenzelle
CSE-Formeln
Array-Formeln werden durch Drücken von erstellt Ctrl, Verschiebung, und Eingeben Tasten auf der Tastatur gleichzeitig, wenn die Formel eingegeben wurde.
Das Ergebnis ist, dass die gesamte Formel - einschließlich des Gleichheitszeichens - von geschweiften Klammern umgeben ist. Ein Beispiel wäre:
{= MAX (IF (A1: B5> 0, A1: B5))}
Aufgrund der gedrückten Tasten zum Erstellen der Array-Formel werden sie manchmal als bezeichnet CSE Formeln.
Beispiel für das MAX-IF-Array von Excel
Wie in der Abbildung oben zu sehen, verwendet dieses Lernbeispiel die MAX-IF-Array-Formel, um die größten positiven und negativen Werte in einem Zahlenbereich zu ermitteln.
Die folgenden Schritte erstellen zuerst die Formel, um die größte positive Zahl zu finden, gefolgt von den Schritten, die zum Ermitteln der größten negativen Zahl erforderlich sind.
Übungsdaten eingeben
- Geben Sie die Zahlen aus dem Bild oben in die Zellen A1 bis B5 eines Arbeitsblatts ein
- In den Zellen A6 und A7 geben Sie die Beschriftungen ein Max positiv und Max Negativ
Eingabe der MAX IF Nested Formula
Da wir sowohl eine verschachtelte Formel als auch eine Array-Formel erstellen, müssen Sie die gesamte Formel in eine einzelne Arbeitsblattzelle eingeben.
Sobald Sie die Formel eingegeben haben UNTERLASSEN SIE drücken Sie die Eingeben Taste auf der Tastatur oder klicken Sie mit der Maus auf eine andere Zelle, um die Formel in eine Arrayformel umzuwandeln
- Klicken Sie auf Zelle B6 - den Ort, an dem die ersten Formelergebnisse angezeigt werden
- Geben Sie Folgendes ein:
= MAX (IF (A1: B5> 0, A1: B5))
Erstellen der Array-Formel
- Halten Sie die Taste gedrückt Ctrl und Verschiebung Tasten auf der Tastatur
- Drücken Sie die Eingeben Taste auf der Tastatur, um die Array-Formel zu erstellen
- Die Antwort 45 sollte in Zelle B6 angezeigt werden, da dies die größte positive Zahl in der Liste ist
- Wenn Sie auf Zelle B6 klicken, wird die vollständige Arrayformel angezeigt
{= MAX (IF (A1: B5> 0, A1: B5))}
ist in der Formelleiste über dem Arbeitsblatt zu sehen
Die größte negative Zahl finden
Die Formel zum Ermitteln der größten negativen Zahl unterscheidet sich von der ersten Formel nur im Vergleichsoperator, der im logischen Testargument der IF-Funktion verwendet wird.
Da das Ziel nun darin besteht, die größte negative Zahl zu finden, verwendet die zweite Formel den Operator "weniger als" ( < ) und nicht der Operator größer als ( > ), um nur die Daten zu testen, die kleiner als Null sind.
- Klicken Sie auf Zelle B7
- Geben Sie Folgendes ein:
= MAX (IF (A1: B5 <0, A1: B5))
- Führen Sie die obigen Schritte aus, um die Arrayformel zu erstellen
- Die Antwort -8 sollte in Zelle B7 angezeigt werden, da dies die größte negative Zahl in der Liste ist
#WERT bekommen! für eine Antwort
Wenn die Zellen B6 und B7 den Wert #WERT anzeigen! Fehlerwert und nicht die oben angegebenen Antworten. Dies liegt wahrscheinlich daran, dass die Array-Formel nicht korrekt erstellt wurde.
Um dieses Problem zu beheben, klicken Sie auf die Formel in der Formelleiste und drücken Sie die Ctrl, Verschiebung und Eingeben Tasten auf der Tastatur erneut.
