In diesem Artikel erfahren Sie, wie Sie Werte mithilfe der Hilfsspalte in Excel indizieren und sortieren.
Szenario:
Manchmal arbeiten Sie mit unsittlichen Daten. Wir müssen die kürzere Version als erforderliche Daten aus den Tabellendaten basierend auf der Hilfsspalte abrufen. Hier werden wir zuerst verstehen, wie man die Hilfsspalte erhält und dann die sortierten kleinen erforderlichen Daten basierend auf den Tabellendaten.
Wie kann man Werte im Bereich indizieren?
Für diesen Artikel müssen wir die ZÄHLENWENN-Funktion verwenden. Jetzt machen wir eine Formel aus der Funktion. Hier gegeben Text und Zahl gemischte Werte in einem Bereich. Wir müssen sie in aufsteigender Reihenfolge zuerst numerisch und dann alphabetisch indizieren.
Generische Formel:-
| = COUNTIF ( list , "<=" & f_value ) + ( COUNT (list) * ISTEXT (f_value) ) |
list : Liste mit Zahlen und Text
f_value : erster Wert des Bereichs
Erläuterung:
- Die ZÄHLENWENN-Funktion zählt die Anzahl der Werte in der zurückgegebenen Liste
- Die COUNT-Funktion ruft die Anzahl der Zahlen im Bereich ab.
- Die Funktion ISTEXT überprüft, ob der Wert in der ausgewählten Zelle Text ist oder nicht. Gibt TRUE für Text und FALSE für Zahlen zurück.
Beispiel:
All dies kann verwirrend sein zu verstehen. Lassen Sie uns verstehen, wie Sie die Hilfsspalte mithilfe der erklärten Formel abrufen. Hier soll die Hilfsspalte anhand der Sortierwerte der Reihe nach (zuerst Zahlen und dann alphabetisch) geordnet werden. Dazu verwenden wir die Formel, um die Index- oder Rangspalte als Hilfsspalte für die unten gezeigten Ausgabendaten zu erhalten.
Hier finden Sie die Werte im Bereich. Für Berechnungszwecke verwenden wir den genannten Bereich für das feste Array D5:D12 als Aufwand.
Verwenden Sie die Formel:
| = ZÄHLENWENN ( Aufwand , "<=" & D5 ) + ( ZÄHLEN (Aufwand) * ISTEXT (D5) ) |
Die Formel sieht wie in der obigen Momentaufnahme gezeigt aus. Der Wert und das Array werden als benannter Bereich und Zellbezug in der Formel angegeben.
Wie Sie sehen können, ist der Index der ersten Zelle im Bereich 5, was bedeutet, dass wir die Ausgaben in Sortierreihenfolge platzieren, Elektrizität wird auf Platz 5 platziert. Kopieren Sie nun die Formel in andere Zellen, indem Sie die Drag-Down-Option verwenden oder die Tastenkombination Strg + D verwenden, wie unten gezeigt, um den Index oder den Rang für die restlichen Werte zu erhalten.
Wie Sie sehen können, haben wir jetzt eine Index- oder Rangliste, die die Ausgaben sortiert. Jetzt verwenden wir diese Rangspalte als Hilfsspalte, um die kürzere Version der Tabelle mit derselben Reihenfolge in der neuen Liste oder Tabelle zu erhalten.
Wie sortiere ich Werte mithilfe der Hilfsspalte in Excel?
Die Sortiernummer in Excel mithilfe der Hilfsspalte muss Indexnummern und den erforderlichen Bereich zum Sortieren haben. Die Hilfsspalte kann jede erforderliche Reihenfolge haben. Wenn Sie beispielsweise die Liste in der erforderlichen zufälligen Reihenfolge abrufen müssen, platzieren Sie einfach den dem Wert entsprechenden Rang in der Index- oder Rangspalte, die als Hilfsspalte dient.
- INDEX-Funktion
- MATCH-Funktion
- ROWS-Funktion
Jetzt erstellen wir eine Formel mit den obigen Funktionen. Die MATCH-Funktion gibt den Index der niedrigsten Übereinstimmung aus dem Bereich zurück. Die INDEX-Funktion verwendet den Zeilenindex als Argument und gibt die entsprechenden erforderlichen Ergebnisse zurück. Diese Funktion findet die
Generische Formel:
| = INDEX ( return_array , MATCH ( ROWS (relative_reference) , Index , 0 ) ) |
return_array : Array, von dem der Wert zurückgegeben wird
Relative Referenz : erzeugt eine aufsteigende Reihenfolge in der Formel. Kann mit jedem Array verwendet werden
Index : Index-Array der Tabelle
0 : genaue Übereinstimmung
Erläuterung:
- ROWS (relative_reference) gibt einen Wert gemäß der Länge der erweiterten Formel zurück. Wenn es in der ersten Zelle angewendet wird, ist es eine, dann die zweite und weiter, bis sie sich ausdehnt.
- Die MATCH-Funktion gleicht den Index mit dem Zeilenwert ab, um sie mit der ROWS-Funktion in aufsteigender Reihenfolge abzurufen.
- Die INDEX-Funktion gibt den übereinstimmenden Index mit dem entsprechenden Wert zurück.
Beispiel:
All dies kann verwirrend sein zu verstehen. Lassen Sie uns diese Formel verstehen, indem Sie sie im unten gezeigten Beispiel ausführen. Hier haben wir die Daten in zufälliger Reihenfolge geordnet, um die ersten drei Werte basierend auf der Indexspalte zu erhalten. Verwenden Sie die Formel, um den ersten Wert der erforderlichen kurzen Tabelle zu erhalten.
Verwenden Sie die Formel:
| = INDEX ( B5:B13 , MATCH ( REIHEN (D5:D5),D5:D13,0)) |
Die Formel sieht wie in der obigen Momentaufnahme gezeigt aus. Das Wertearray wird als benannter Bereich und Zellbezug angegeben.
Wie Sie sehen, ist der erste Wert die Zahl aus dem Bereich, die als "Milch" herauskommt. Der entsprechende Index ist 1. Kopieren Sie nun die Formel in andere Zellen mit der Drag-Down-Option oder mit der Tastenkombination Strg + D wie unten gezeigt, um zu erhalten die restlichen Werte.
Wie Sie sehen, werden die Werte in aufsteigender Reihenfolge indiziert. Wir haben alle Werte erhalten, da dies auch beweist, dass die Formel gut funktioniert. Wir können die Zahlenwerte mit der Formel derselben Formel extrahieren.
Wie Sie sehen, haben wir die kürzeren und sortierten Werte aus der Tabelle mit der Hilfsspalte, die entsprechend indiziert ist.
Hier sind einige Beobachtungsnotizen, die unten gezeigt werden.
Anmerkungen:
- Die Formel funktioniert nur mit Zahlen und Text.
- Die Formel ignoriert Textwerte beim Vergleichen von Zahlen und ignoriert Zahlen, wenn Textwerte übereinstimmen.
- Die Hilfsspalte kann jede Art von erforderlicher Spaltenreihenfolge sein.
Ich hoffe, dieser Artikel zum Indizieren und Sortieren von Werten mit der Hilfsspalte in Excel ist erklärend. Weitere Artikel zu Nachschlageformeln in Excel finden Sie hier. Wenn Ihnen unsere Blogs gefallen haben, teilen Sie sie mit Ihren Freunden auf Facebook. Und Sie können uns auch auf Twitter und Facebook folgen. Wir würden uns freuen, von Ihnen zu hören, lassen Sie uns wissen, wie wir unsere Arbeit verbessern, ergänzen oder erneuern und für Sie verbessern können. Schreiben Sie uns auf der E-Mail-Site
So verwenden Sie die SUMMENPRODUKT-Funktion in Excel: Gibt die SUMME nach der Multiplikation von Werten in mehreren Arrays in Excel zurück.
SUM, wenn das Datum dazwischen liegt : Gibt die SUMME der Werte zwischen den angegebenen Daten oder dem Zeitraum in Excel zurück.
Summe, wenn das Datum größer als das angegebene Datum ist: Gibt die SUMME der Werte nach dem angegebenen Datum oder Zeitraum in Excel zurück.
2 Möglichkeiten, in Excel nach Monat zu summieren: Gibt die SUMME der Werte innerhalb eines bestimmten Monats in Excel zurück.
So summieren Sie mehrere Spalten mit Bedingung: Gibt die SUMME von Werten über mehrere Spalten mit Bedingung in Excel zurück
So verwenden Sie Platzhalter in Excel : Zählen Sie Zellen, die mit Phrasen übereinstimmen, indem Sie die Platzhalter in Excel verwenden
populäre Artikel
50 Excel-Shortcut zur Steigerung Ihrer Produktivität : Erledigen Sie Ihre Aufgabe schneller. Mit diesen 50 Tastenkombinationen arbeiten Sie noch schneller mit Excel.
Wie benutzt manDie 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.
So verwenden Sie die ZÄHLENWENN-Funktion in Excel : Zählen Sie Werte mit Bedingungen mit dieser erstaunlichen Funktion. Sie müssen Ihre Daten nicht filtern, um bestimmte Werte 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.