Wie verwende ich max if in excel?
In Excel können wir nicht einfach die Standard-MAX-Funktion mit einer Bedingung verwenden (es sei denn, Sie verwenden Microsoft Office 365).
Aber die Sache ist die: Wenn Sie den Maximalwert eines Bereichs unter Verwendung einer bestimmten Bedingung erhalten möchten, benötigen Sie eine MAX-IF-Formel.
Also, was ist der Punkt?
Sie können MAX und IF kombinieren, um eine Formel zu erstellen, die Ihnen dabei helfen kann, anhand bestimmter Kriterien den Maximalwert eines Bereichs zu ermitteln.
Kurz gesagt: MAXIF ist eine Matrixformel, mit der Sie anhand von Kriterien den Maximalwert eines Bereichs ermitteln können. Aber hier ist der Clou: In diesem Artikel werde ich Ihnen verschiedene Möglichkeiten vorstellen, die MAXIF-Formel zu verwenden.
Wenden Sie die MAX IF-Formel an
Nehmen wir ein Beispiel für die folgenden Daten, in denen Sie die Verkaufsmenge pro Monat, pro Woche und pro Datum haben.
Aus diesen Daten müssen Sie nun die maximale Verkaufsmenge für einen bestimmten Monat ermitteln. Zum Beispiel „Jan“ und dafür lautet die Formel:
=MAX(IF(A2:A367="Jan",D2:D367))Und Sie müssen es als Array-Formel mit Strg + Umschalt + Eingabetaste eingeben.
{=MAX(IF(A2:A367="Jan",D2:D367))}
Wenn Sie es eingeben, wird 200 zurückgegeben, was dem höchsten Verkaufswert des Monats Januar am 7. Januar 2016 entspricht.
Wie funktioniert das
Um zu verstehen, wie Max funktioniert, müssen Sie es in drei Teile aufteilen.
Bitte beachten Sie, dass Sie dies als Matrixformel eingegeben haben .
Zunächst haben Sie in der IF-Funktion einen Logiktest erstellt, um die gesamte Monatsspalte mit den Kriterien abzugleichen. Und es wurde ein Array zurückgegeben, in dem die Übereinstimmungswerte TRUE und alle anderen FALSE sind.
Zweitens haben Sie in der IF-Funktion die Verkaufsmengenspalte für TRUE-Werte angegeben und deshalb wurde die Verkaufsmenge nur für TRUE zurückgegeben.
Drittens gibt die MAX-Funktion einfach die höchste Zahl in diesem Array zurück, die der höchsten Verkaufsmenge für den Monat Januar entspricht. Genau wie diesen Formel-Hack hier habe ich einige der anderen aufgelistet, die Sie lesen und lernen müssen.
MAX IF mit mehreren Kriterien
Ich bin mir sicher, dass Sie an dieser Stelle darüber nachdenken, wie wir in max if mehr als eine Bedingung verwenden können. Und das ist kluges Denken.
In der realen Welt der Daten ist es sehr wahrscheinlich, dass wir mehrere Kriterien verwenden müssen, um den höchsten Wert zu erzielen. Fahren wir mit dem vorherigen Beispiel fort.
Nehmen wir an, Sie müssen statt des höchsten Umsatzwerts für Januar den höchsten Umsatz für die zweite Januarwoche erzielen.
Hier haben Sie also zwei verschiedene Bedingungen: Der Monat ist Januar und die Woche ist der 2.. Und die Formel wird sein:
=MAX(IF(A2:A367=F5,IF(B2:B367=G5,D2:D367),))Und wenn Sie es als Tabelle eingeben:
{=MAX(IF(A2:A367=F5,IF(B2:B367=G5,D2:D367),))}
Wie funktioniert das
Hier haben wir verschachteltes IF verwendet, um mehr als eine Logik zu testen. Einer ist für den Monat und der zweite für die Woche.
Es wird also eine Tabelle zurückgegeben, in der Sie die Verkaufsmenge nur dann haben, wenn der Monat Januar und die Woche der 2. ist.
Und am Ende gibt max den Maximalwert dieses Arrays zurück.
MAX IF ohne Tabelle
Tabellen sind leistungsstark, aber nicht jeder möchte sie verwenden. Und Sie haben die Möglichkeit, in max if kein Array zu verwenden.
Hierfür können Sie also die SUMPRODUCT-Funktion verwenden, da sie Ihnen dabei helfen kann, ein Array zu verwenden, ohne die eigentliche Array-Formel anzuwenden. Und die Formel wird sein:
=SUMPRODUCT(MAX((A2:A367="Jan")*D2:D367))Wenn Sie diese Formel einfügen, müssen Sie also nicht Strg + Umschalt + Eingabetaste verwenden.
Wie funktioniert das
Auch hier müssen wir diese Formel in drei verschiedene Teile unterteilen, um sie zu verstehen. Im ersten Teil haben Sie die Monatsspalte mit den Kriterien verglichen und es wurde ein Array mit TRUE zurückgegeben, wenn die Kriterien übereinstimmten, andernfalls FALSE.
Anschließend multiplizieren Sie diese Tabelle mit der Spalte „Verkaufsmenge“, was eine Tabelle zurückgibt, in der Sie die Verkaufsmenge anstelle von TRUE haben. Letztendlich hat die MAX-Funktion den höchsten Wert in diesem Array zurückgegeben, der Ihrer höchsten Verkaufsmenge entspricht.
Die Hauptaufgabe dieser gesamten Formel, sumproduct, besteht darin, Ihnen die Verwendung eines Arrays zu ermöglichen, ohne eine tatsächliche Array-Formel eingeben zu müssen.
Abschluss
Das, was wir oft tun müssen, ist es, anhand von Kriterien den größtmöglichen Wert zu erzielen. Und mit dieser Technik der Max-If-Formel können Sie dies mühelos tun.
Alle oben erlernten Methoden haben je nach Situation unterschiedliche Anwendungen. Ich hoffe, dieser Formeltipp hilft Ihnen, Ihre Excel-Kenntnisse zu verbessern. Aber jetzt sag mir eins.
Was gefällt Ihnen wirklich, ein Gemälde oder ohne Gemälde?
Bitte teilen Sie mir Ihre Meinung im Kommentarbereich mit. Ich würde gerne von Ihnen hören und vergessen Sie bitte nicht, diesen Beitrag mit Ihren Freunden zu teilen. Ich bin sicher, sie werden es zu schätzen wissen.