Excel kleine Funktion, um die niedrigsten Werte zu finden und hervorzuheben

In diesem kurzen Tutorial werden wir über Excel Small Function, wie sie funktioniert und wie man sie verwendet, um die kleinste Nummer, Datum oder Uhrzeit zu finden.

Müssen Sie in einem Arbeitsblatt ein paar niedrigste Zahlen finden? Dies ist ziemlich einfach mit der Excel -Sortierfunktion. Möchten Sie keine Zeit damit verschwenden, Ihre Daten mit jeder Änderung neu zu sortieren? Die kleine Funktion hilft Ihnen dabei, schnell den niedrigsten Wert zu finden, den zweitkleinsten, dritt kleinsten und so weiter.

• Kleine Funktion in Excel
• Grundlegende kleine Formel
• So verwenden Sie kleine Funktionen - Formel Beispiele
  • Finden Sie unten 3, 5, 10 Werte
  • Sum niedrigste n Werte
  • Index und klein, um kleinste Spiele zu erhalten
  • Sortieren Sie Zahlen in aufsteigender Reihenfolge
  • Kleine Formel für Daten und Zeiten
  • Finden Sie ein früheres Datum, das heute am nächsten liegt oder das angegebene Datum
• Wie man die niedrigsten Werte in Excel hervorhebt
• Excel kleine Funktion funktioniert nicht

Excel kleine Funktion

Small ist eine statistische Funktion, die den kleinsten Wert in einem Datensatz zurückgibt.

Die Syntax der kleinen Funktion enthält zwei Argumente, die beide erforderlich sind.

Klein (Array, k)

Wo:

• Array - ein Array oder ein Zellbereich, aus dem der kleinste Wert extrahiert wird.
• K - Eine Ganzzahl, die die Position vom niedrigsten Wert zur Rückgabe angibt, dh K -te kleinste.

Die Funktion ist in allen Versionen von Excel für Office 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013, Excel 2010 und früher erhältlich.

Tipp. Verwenden Sie Excel Small Ifs-Formel, um den niedrigsten Wert von K-Th niedrigsten mit Kriterien zu finden.

Einfache kleine Formel in Excel

Eine kleine Formel in ihrer grundlegenden Form ist sehr einfach zu erstellen - Sie geben nur die Reichweite und die Position vom kleinsten Gegenstand an.

In der Liste der Zahlen in B2: B10 möchten Sie den kleinsten Wert von 3 ^RD extrahieren. Die Formel ist so einfach wie:

=SMALL(B2:B10, 3)

Um es einfacher zu machen, das Ergebnis zu überprüfen, wird Spalte B in aufsteigender Reihenfolge sortiert:

4 Dinge, die Sie über kleine Funktionen wissen sollten

Die folgenden Nutzungsnotizen helfen Ihnen dabei, das Verhalten der kleinen Funktion besser zu verstehen und Verwirrung beim Aufbau Ihrer eigenen Formeln zu vermeiden.

1. Alle leeren Zellen , Textwerte und logischen Werte, die im Array -Argument wahr und falsch sind, werden ignoriert.
2. Wenn Array einen oder mehrere Fehler enthält, wird ein Fehler zurückgegeben.
3. Falls in Array Duplikate vorhanden sind, kann Ihre Formel zu "Krawatten" führen. Wenn beispielsweise zwei Zellen die Zahl 1 enthalten und die kleine Funktion so konfiguriert ist, dass sie den kleinsten und den 2 ^-ND -Wert zurückgeben, erhalten Sie in beiden Fällen 1.
4. Angenommen, N ist die Anzahl der Werte in Array , klein (Array, 1) gibt den niedrigsten Wert zurück, und klein (Array, n) wählt den höchsten Wert aus.

So verwenden Sie die kleine Funktion in Excel -Formel -Beispielen

Und nun schauen wir uns einige weitere Beispiele für die excel kleine Funktion an, die über die grundlegende Verwendung hinausgehen.

Finden Sie unten 3, 5, 10 usw. Werte

Wie Sie bereits wissen, ist die kleine Funktion so ausgelegt, dass sie den niedrigsten Wert n-den niedrigsten Wert berechnet. Dieses Beispiel zeigt, wie dies am effektivsten geht.

Nehmen wir in der folgenden Tabelle an, Sie möchten die unteren 3 -Werte finden. Geben Sie dazu die Zahlen 1, 2 und 3 in getrennte Zellen (D3, D4 und D5 in unserem Fall) ein. Geben Sie dann die folgende Formel in E3 ein und ziehen Sie sie durch E5 nach unten:

=SMALL($B$2:$B$10, D3)

In E3 extrahiert die Formel den kleinsten Wert unter Verwendung der Zahl in D3 für das K -Argument. Die wichtigste Sache ist, ordnungsgemäße Zellreferenzen zu liefern, wodurch die Formel in anderen Zellen korrekt kopiert: absolut für Array und relativ für k .

Möchten Sie sich nicht die Mühe machen, die Reihen manuell zu tippen? Verwenden Sie die Zeilenfunktion mit einer expandierenden Reichweite , um den K -Wert bereitzustellen. Dafür machen wir eine absolute Referenz für die erste Zelle (oder nur die Zeilenkoordinate wie B $ 2) und relative Referenz für die letzte Zelle:

=SMALL($B$2:$B$10, ROWS(B$2:B2))

Als Ergebnis erweitert sich die Bereichsreferenz, wenn die Formel in der Spalte kopiert wird. In D2 erzeugt die Zeilen (B $ 2: B2) 1 für k und die Formel kehrt die niedrigsten Kosten zurück. In D3 liefert Zeilen (B $ 2: B3) 2, und wir erhalten die ^niedrigsten Kosten und so weiter.

Kopieren Sie die Formel einfach durch 5 Zellen, und Sie erhalten untere 5 Werte:

Summe untere N -Werte

Möchten Sie insgesamt die kleinsten N -Werte in einem Datensatz finden? Wenn Sie die Werte, die im vorherigen Beispiel gezeigt wurden, bereits extrahiert haben, wäre die einfachste Lösung eine Summenformel wie:

=SUM(E3:E5)

Oder Sie können eine unabhängige Formel erstellen, indem Sie die kleine Funktion zusammen mit Sumproduct verwenden:

Sumprodukt (klein ( Array , {1,…, n }))

Um die Summe der unteren 3 Werte in unseren Datensatzwerten zu erhalten, nimmt die Formel diese Form an:

=SUMPRODUCT(SMALL(B2:B10, {1,2,3}))

Die Summenfunktion erzeugt das gleiche Ergebnis:

=SUM(SMALL(B2:B10, {1,2,3}))

Notiz. Wenn Sie Zellreferenzen anstelle von Array Constant für K verwenden, müssen Sie die Strg -Eingabetaste drücken, um sie zu einer Array -Formel zu machen. In Excel 365, das dynamische Arrays unterstützt, arbeitet Sum Summe in beiden Fällen als reguläre Formel.

Wie diese Formel funktioniert:

In einer regelmäßigen Formel kehrt kleine k-te kleinste Wert in einem Bereich zurück. In diesem Fall liefern wir eine Array -Konstante wie {1,2,3} für das K -Argument und zwingen es, ein Array der kleinsten 3 Werte zurückzugeben:

{29240, 43610, 58860}

Die Summenprodukt- oder Summenfunktion addiert die Zahlen im Array und gibt die Gesamtsumme aus. Das war's!

Index Match kleine Formel, um kleinste Übereinstimmungen zu erhalten

Verwenden Sie in der Situation, wenn Sie einige Daten abrufen möchten, die dem kleinsten Wert zugeordnet sind.

Index ( return_array , match (small ( shookup_array , n ), Lookup_array , 0))

Wo:

• Return_array ist ein Bereich, aus dem zugehörige Daten extrahiert werden können.
• Lookup_array ist ein Bereich, in dem nach dem niedrigsten N-ten Wert gesucht werden soll.
• N ist die Position des kleinsten Wertes von Interesse.

Um beispielsweise den Namen des Projekts zu erhalten, der die niedrigsten Kosten hat, lautet die Formel in E3:

=INDEX($A$2:$A$10, MATCH(SMALL($B$2:$B$10, D3), $B$2:$B$10, 0))

Wo A2: A10 die Projektnamen sind, sind B2: B10 die Kosten und D3 ist der Rang vom kleinsten.

Kopieren Sie die Formel in die folgenden Zellen (E4 und E5), und Sie erhalten die Namen der 3 billigsten Projekte:

Anmerkungen:

• Diese Lösung funktioniert gut für einen Datensatz, der keine Duplikate hat. Zwei oder mehr doppelte Werte in einer numerischen Spalte können jedoch im Ranking "Krawatten" erzeugen, was zu falschen Ergebnissen führt. In diesem Fall verwenden Sie bitte etwas anspruchsvollere Formel, um Bindungen zu brechen.
• In Excel 365 kann diese Aufgabe mit Hilfe der neuen dynamischen Array -Funktionen erledigt werden. Dieser Ansatz löst automatisch das Problem der Bindungen. Ausführliche Informationen finden Sie unter dem Filtern der unteren N -Werte in Excel.

Sortieren Sie die Zahlen vom niedrigsten bis zum höchsten mit einer Formel

Ich glaube, jeder weiß, wie man Zahlen mit Excel -Sortierfunktion in Ordnung bringt. Aber wissen Sie, wie Sie Sortieren mit einer Formel durchführen? Die Benutzer von Excel 365 können dies mit der neuen Sortierfunktion auf einfache Weise tun. In Excel 2019, 2016 und früheren Versionen funktioniert Sort nicht, leider nicht. Aber ein wenig Vertrauen und klein werden zur Rettung kommen :)

Wie im ersten Beispiel verwenden wir die Zeilenfunktion mit einem expandierenden Bereich auf Increment K um 1 in jeder Zeile, in der die Formel kopiert wird:

=SMALL($A$2:$A$10, ROWS(A$2:A2))

Geben Sie die Formel in die erste Zelle ein und ziehen Sie sie dann in so viele Zellen hin, da es im ursprünglichen Datensatz Werte gibt (C2: C10 in diesem Beispiel):

Tipp. Verwenden Sie die große Funktion anstelle von klein, um absteigend zu sortieren.

Excel kleine Formel für Daten und Zeiten

Da Daten und Zeiten auch numerische Werte sind (im internen Excel -System werden Daten als sequentielle Zahlen und Zeiten als Dezimalfraktionen gespeichert), kann die kleine Funktion sie auch ohne zusätzliche Anstrengung auf Ihrer Seite verarbeiten.

Wie Sie in den folgenden Screenshots sehen können, funktioniert eine grundlegende Formel, die wir für Zahlen verwendet haben, auch für Daten und Zeiten wunderbar:

=SMALL($B$2:$B$10, D2)

Kleine Formel, um die frühesten 3 Daten zu finden:

Kleine Formel, um dreimal die kürzeste zu erhalten:

Das nächste Beispiel zeigt, wie die kleine Funktion Ihnen helfen kann, eine spezifischere Aufgabe in Bezug auf Daten zu erfüllen.

Finden Sie ein früheres Datum, das dem heutigen oder einem festgelegten Datum am nächsten liegt

Angenommen, Sie möchten das nächste Datum vor einem bestimmten Datum finden. Dies kann durch die Verwendung der kleinen Funktion in Kombination mit CountIF erfolgen.

Mit der Liste der Daten in B2: B10 und dem Zieldatum in E1 gibt die folgende Formel ein früheres Datum zurück, das dem Zieldatum am nächsten liegt:

=SMALL(B2:B10, COUNTIF(B2:B10, "

Um ein Datum zu extrahieren, das zwei Daten vor dem Datum in E1 entspricht, dh ein früheres Datum, ist die Formel:

=SMALL(B2:B10, COUNTIF(B2:B10, "

Verwenden Sie die heutige Funktion für die Kriterien von Countif, um ein vergangenes Datum zu finden: die heutige Funktion:

=SMALL(B2:B10, COUNTIF(B2:B10, "

Tipp. Um Fehler in der Situation zu verhindern, wenn ein Datum, das mit Ihren Kriterien entspricht, nicht gefunden wird, können Sie die ISTERROR -Funktion um Ihre Formel wie folgt wickeln:

=IFERROR(SMALL(B2:B10, COUNTIF(B2:B10, "<p> <b>Wie diese Formeln funktionieren:</b></p> <p> Die allgemeine Idee besteht darin, die Anzahl der Daten zu zählen, die kleiner als das Zieldatum mit Countif sind. Und diese Anzahl ist genau das, was die kleine Funktion für das <em>K</em> -Argument braucht.</p> <p> Um das Konzept besser zu verstehen, schauen wir es uns aus einem anderen Blickwinkel an:</p> <p> Wenn 1-Aug-2020 (das Zieldatum in E1) in unserem Datensatz erscheinen würde, wäre dies das <sup>7.</sup> größte Datum in der Liste. Folglich sind sechs Daten kleiner als es. Das heißt, das <sup>6.</sup> kleinste Datum ist das vorherige Datum am nächsten am Zieldatum.</p> <p> Zuerst berechnen wir, wie viele Daten kleiner sind als das Datum in E1 (das Ergebnis ist 6):</p> <p> <code>COUNTIF(B2:B10, "

Und dann die Anzahl in das ^2. Argument von Small: Stecker:

=SMALL(B2:B10, 6)

Um das vorherige, aber ein Datum (das das ^5. kleinste Datum in unserem Fall) zu erhalten, subtrahieren wir 1 vom Ergebnis von Countif.

Wie man die Bodenwerte in Excel hervorhebt

Um die kleinsten N-Werte in Ihrer Tabelle mit Excel-bedingter Formatierung hervorzuheben, können Sie entweder eine integrierte obere/untere Option verwenden oder Ihre eigene Regel basierend auf einer kleinen Formel einrichten. Die erste Methode ist schneller und einfacher angewendet, während die zweite mehr Kontrolle und Flexibilität bietet. Die folgenden Schritte führen Sie durch das Erstellen einer benutzerdefinierten Regel:

1. Wählen Sie den Bereich aus, in dem Sie die unteren Werte hervorheben möchten. In unserem Fall sind die Zahlen in B2: B10, also wählen wir sie aus. Wenn Sie ganze Zeilen hervorheben möchten, wählen Sie A2: B10.
2. Klicken Sie auf der Registerkarte "Start" in der Styles -Gruppe auf Bedingte Formatierung > Neue Regel .
3. Wählen Sie im Dialogfeld "Neue Formatierungsregel" Verwenden Sie eine Formel, um zu bestimmen, welche Zellen zu formatieren sind.
4. Geben Sie in den Formatwerten, bei denen diese Formel ein wahres Feld ist , eine Formel wie diese ein:

   =B2

   Wobei B2 die Zelle der linken Zelle des zu überprüfenden numerischen Reichweite ist, ist 2 $ B $: $ B $ 10 der gesamte Bereich und 3 sind die n unteren Werte, die hervorgehoben werden sollen.

   In Ihrer Formel kümmern Sie sich bitte an die Referenztypen: Die Zelle der linken The Links ist eine relative Referenz (B2), während der Bereich die absolute Referenz ist ($ B $ 2: $ B $ 10).

5. Klicken Sie auf die Schaltfläche Format und wählen Sie ein beliebiges Format aus.
6. Klicken Sie zweimal auf OK, um beide Dialogfenster zu schließen.

Erledigt! Die unteren 3 -Werte in Spalte B werden hervorgehoben:

Weitere Informationen finden Sie in der Excel -Bedingungsformatierung basierend auf der Formel.

Excel kleine Funktion funktioniert nicht

Wie Sie gerade aus unseren Beispielen gesehen haben, ist es ziemlich einfach, die kleine Funktion in Excel zu verwenden, und es ist unwahrscheinlich, dass Sie Schwierigkeiten haben. Wenn Ihre Formel nicht funktioniert, ist dies höchstwahrscheinlich ein #Num! Fehler, der aus den folgenden Gründen auftreten kann:

• Array ist leer oder enthält keinen einzigen numerischen Wert.
• Der K -Wert ist weniger als Null (ein dummer Tippfehler kann Ihre Stunden Fehlerbehebung kosten!) Oder die Anzahl der Werte im Array überschreitet.

So verwenden Sie eine kleine Formel in Excel, um die Bodnummern in einem Datensatz zu finden und hervorzuheben. Wenn Sie andere Szenarien kennen, in denen die Funktion nützlich ist, können Sie in Kommentaren am liebsten teilen. Ich danke Ihnen für das Lesen und hoffe, Sie nächste Woche in unserem Blog zu sehen!

Üben Sie die Arbeitsmappe zum Download

Excel -Beispiele für kleine Formel (.xlsx -Datei)

Das obige ist der detaillierte Inhalt vonExcel kleine Funktion, um die niedrigsten Werte zu finden und hervorzuheben. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!