Wir wissen, wie Sie die am häufigsten vorkommende Nummer in Excel ermitteln. Wir verwenden die MODE-Funktion. Wenn wir jedoch die am häufigsten vorkommenden Zahlen auflisten möchten, funktioniert diese Formel nicht. Für diese Aufgabe benötigen wir eine Kombination von Excel-Funktionen.
Hier ist die generische Formel, um die am häufigsten vorkommenden Zahlen aufzulisten:
| =MODE(IF(1-ISNUMBER(MATCH(Zahlen,erweiternder Bereich,0)),Zahlen)) |
Zahlen:Dies ist der Bereich oder das Array, aus dem wir die am häufigsten vorkommenden Zahlen erhalten müssen.
expand_range:Es handelt sich um einen sich erweiternden Bereich, der von einer Zelle darüber beginnt, in der Sie diese Formel schreiben. Wenn zum Beispiel die Excel-Formel in der Zelle B4 richtig ist, wird der Erweiterungsrang $B$3:B3 sein.
Sehen wir uns nun diese Formel in Aktion an.
Beispiel: Erhalten Sie 3 häufig erscheinende Bewertungen.
Das Szenario ist also dieses. Uns liegen einige Daten zu einer Umfrage vor, in der Nutzer unsere Dienstleistungen bewertet haben. Wir müssen die 3 am häufigsten erscheinenden Bewertungen erhalten.
Schreiben Sie diese Formel in G3:
| =MODE(IF(1-ISNUMBER(MATCH($D$3:$D$11,$G$2:G2,0)),$D$3:$D$11)) |
Dies ist eine Array-Formel. Verwenden Sie STRG+UMSCHALT+EINGABETASTE. Ziehen Sie diese Formel in drei Zellen nach unten. Und jetzt haben Sie Ihre 3 am häufigsten vorkommenden Zahlen.
Nun, da wir es getan haben, wollen wir sehen, wie es funktioniert.
Wie funktioniert es?
Hier besteht die Idee darin, bereits extrahierte am häufigsten vorkommende Zahlen beim nächsten Lauf der MODE-Funktion auszuschließen. Dazu verwenden wir zusätzliche Hilfe von anderen logischen Excel-Funktionen.
Um es zu verstehen, schauen wir uns die Formel in Zelle G4 an. Welches ist:
| =MODE(IF(1-ISNUMBER(MATCH($D$3:$D$11,$G$2:G3,0)),$D$3:$D$11)) |
Die Formel beginnt mit der innersten Funktion MATCH.
Die MATCH-Funktion hat den Nachschlagebereich D3:D11 (die Zahlen). Der Nachschlagewert ist ebenfalls ein Bereich G2:G3 (erweitert beim Herunterkopieren). Nun enthält G2 natürlich einen Text, der nicht im Bereich D3:D11 liegt. Als nächstes enthält G3 6. MATCH sucht nach 6 im Bereich D3:D11 und gibt ein Array von #N/A error und Positionen von 6 im Bereich zurück, was ist {#N/A;2;#N/A;2;2;#N/A;#N/A;#N/A;#N/A}.
Nun wird dieses Array der ISNUMBER-Funktion zugeführt. ISNUMBER gibt TRUE für Zahlen im Array zurück. Daher erhalten wir ein weiteres Array als {FALSCH;WAHR;FALSCH;WAHR;WAHR;FALSCH;FALSCH;FALSCH;FALSCH}.
Als nächstes wird dieses Array von 1 subtrahiert. Wie wir wissen, wird FALSE als 0 und WAHR als 1 behandelt. Wenn wir dieses Array von 1 subtrahieren, erhalten wir ein invertiertes Array als{1;0;1;0;0;1;1;1;1}. Dies ist das genaue Spiegelbild des von der ISNUMBER-Funktion zurückgegebenen Arrays.
Nun wird für jede 1 (TRUE) MODE-Funktion ausgeführt. Die MODE-Funktion erhält ein Array{2;FALSCH;2;FALSCH;FALSCH;5;4;4;5}.Und schließlich erhalten wir die am häufigsten vorkommende Zahl im Array als 2. (Beachten Sie, dass 5 und 4 beide zweimal vorkommen. Aber da 2 zuerst aufgetreten ist, erhalten wir 2.
In G3 gibt die ISNUMBER-Funktion nur ein Array von FALSE zurück. Daher wird der gesamte Bereich der MODE-Funktion zugeführt. Dann gibt die Modusfunktion die am häufigsten vorkommende Zahl im Bereich als 6 zurück.
Eine andere Formel
Eine andere Möglichkeit besteht darin, die NOT-Funktion zu verwenden, um die Array-Rückgabe durch die ISNUMBER-Funktion zu invertieren.
| {=MODE(IF(NOT(ISNUMBER(MATCH($D$3:$D$11,$G$2:G3,0))),$D$3:$D$11))} |
Also ja, Leute, so können Sie die am häufigsten vorkommenden Zahlen in Excel abrufen. Ich hoffe, ich war erklärend genug. Wenn Sie Zweifel bezüglich einer Funktion haben, die in dieser Formel verwendet wird, klicken Sie einfach darauf. Sie gelangen zur Erklärung dieser Funktion. Wenn Sie Fragen zu diesem Artikel oder einer anderen Excel- oder VBA-Funktion haben, fragen Sie mich im Kommentarbereich unten.
So finden Sie häufig vorkommende Zahlen | Die am häufigsten vorkommenden Zahlen können mit der MODE-Funktion von Excel berechnet werden.
Holen Sie sich den am häufigsten vorkommenden Text in Excel | Erfahren Sie, wie Sie häufig erscheinenden Text in Excel abrufen.
Nachschlagen von häufig erscheinendem Text mit Kriterien in Excel | Um den am häufigsten vorkommenden Text in Excel mit Kriterien zu erhalten, müssen Sie nur eine if-Bedingung vor der Match-Funktion setzen.
Abrufen des ersten Werts in einer Liste, der größer/kleiner als ein angegebener Wert ist | Erfahren Sie, wie Sie den ersten Wert suchen, der größer als ein bestimmter Wert ist. Die allgemeine Formel zum Nachschlagen ist …
So rufen Sie die gesamte Zeile eines übereinstimmenden Werts ab| Es ist einfach, die gesamte Zeile einer Tabelle nachzuschlagen und abzurufen. Mal sehen, wie wir diese Suche durchführen können.
So rufen Sie den aktuellen Preis in Excel ab | Um den neuesten Preis aus einem Bereich zu ermitteln, müssen Sie Ihre Daten sortieren lassen. Verwenden Sie nach der Sortierung diese Formel …
Populäre Artikel:
50 Excel-Kurzbefehle zur Steigerung Ihrer Produktivität | Werden Sie schneller bei Ihrer Aufgabe. Mit diesen 50 Tastenkombinationen arbeiten Sie noch schneller mit Excel.
Die SVERWEIS-Funktion in Excel | Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die verwendet wird, um Werte aus verschiedenen Bereichen und Blättern zu suchen.
ZÄHLENWENN in Excel 2016 | Zählen Sie Werte mit Bedingungen mit dieser erstaunlichen Funktion. Sie müssen Ihre Daten nicht filtern, um einen bestimmten Wert zu zählen. Die Countif-Funktion ist unerlässlich, um Ihr Dashboard vorzubereiten.
So verwenden Sie die SUMIF-Funktion in Excel | Dies ist eine weitere wesentliche Funktion des Dashboards. Dies hilft Ihnen, Werte unter bestimmten Bedingungen zusammenzufassen.