Inhalt
- Hinzufügen des Analyse-ToolPak
- Verwenden des Rang- und Perzentil-Werkzeugs
- Verwenden der MATCH- und INDEX-Funktion zum Konvertieren der Ergebnisse in der Punktspalte
- Vervollständigung unserer Rangliste Unsere Schüler
- Fazit
- Und schlussendlich...
Robbie schreibt hauptsächlich über Skyrim, beleuchtet aber gelegentlich auch die Kuriositäten von Microsoft-Anwendungen wie Excel und Outlook.
In diesem Artikel werde ich das untersuchen Rang und Perzentil Werkzeug.
Rangfolge ermöglicht es Ihnen, alle Ihre Daten in der Reihenfolge vom höchsten zum niedrigsten und zu ordnen Perzentil Ermöglicht die einfache Kategorisierung Ihrer Daten in Perzentile wie die besten 25%, die besten 50% usw.
Um zu veranschaulichen, wie dieses Tool verwendet wird, werde ich ein Beispiel durcharbeiten. In meinem Beispiel habe ich eine Klasse von 30 Schülern. Ich habe gerade ihre neuesten Ergebnisse erhalten und möchte sie bewerten und ihnen Noten basierend auf ihrer Rangfolge innerhalb der Klasse zuweisen.Die besten 10% erhalten ein A, die nächsten 20% ein B, die nächsten 20% ein C und der Rest eine D-Note.
Mein oberstes Ziel ist es, mit möglichst wenig manueller Arbeit eine Tabelle wie die unten gezeigte zu erhalten.
In Verbindung mit Rang und Perzentil Werkzeug, ich werde das verwenden SPIEL und INDEX Funktionen zum Zuweisen der Schülernamen zu den vom Tool erstellten Rohdaten. Ich werde dann die verwenden WENN Funktion, um ihnen automatisch eine Note zuzuweisen, die auch auf ihren Ergebnissen basiert.
Hinzufügen des Analyse-ToolPak
Bevor Sie das Tool verwenden, müssen Sie sicherstellen, dass das Analysis ToolPak in Excel vorhanden ist. Das ToolPak finden Sie auf der Daten Registerkarte in der Analyse Gruppe und wird über die zugegriffen Datenanalyse Taste.
Verwenden des Rang- und Perzentil-Werkzeugs
Nachdem wir das Analysis ToolPak hinzugefügt haben, wählen Sie das aus Datenanalyse Schaltfläche und wählen Sie Rang und Perzentil Wie nachfolgend dargestellt.
Das Tool selbst ist recht unkompliziert und einfach zu bedienen.
- Wählen Sie zuerst die Eingabebereich
- Wenn sich Ihre Daten in Spalten befinden, gehen Sie Gruppiert nach wie Säulen (Andernfalls wählen Sie Reihen)
- Wenn Sie Beschriftungen für Ihre Spalten haben, wählen Sie Etiketten in der ersten Reihe
- Zuletzt wählen Sie Ihre Ausgabeoptionen, wählen Ausgabebereich um es an einer bestimmten Stelle in Ihrem Arbeitsblatt zu platzieren oder auszuwählen Neue Arbeitsblattlage oder Neue Arbeitsmappe abhängig von Ihrer Präferenz (wir haben die Ergebnisse des Tools in dem Beispiel, an dem wir heute arbeiten, neben den Originaldaten platziert)
- Klicken OK und Excel erstellt Ihre Rang- und Perzentiltabelle
Sie können die Ausgabe oben vom Tool sehen. Excel hat jeden Schüler eingestuft und ihm sein genaues Perzentil (die Spalte Prozent) gegeben. Anstatt jedoch die Namen der Schüler zu verwenden, wird eine Spalte mit dem Namen aufgerufen PunktDies ist die Zeilennummer jedes Schülers. Um das zu drehen Punkt Spalte in eine Spalte, die den Namen des Schülers enthält, muss ich mich an die wenden SPIEL und INDEX Funktion.
Verwenden der MATCH- und INDEX-Funktion zum Konvertieren der Ergebnisse in der Punktspalte
Diese beiden Funktionen arbeiten zusammen, um die Ergebnisse in der Spalte Punkt in den Namen des Schülers zu konvertieren.
Zuerst:
- Das SPIEL Die Funktion sucht nach der Nummer in der Spalte Punkt und findet dann ihre genaue Übereinstimmung in der Spalte mit der Bezeichnung Schülernummer.
- INDEX sucht dann nach der Zelle neben dem Ergebnis, dass SPIEL gefunden und gibt den Namen des Schülers zurück.
In der folgenden Abbildung sehen Sie die Formel, die für unseren Top-Schüler (Ed Bradley) in der Spalte Punkt 12 wird.
Die gesamte Formel lautet
= INDEX ($ B $ 3: $ B $ 32, MATCH (F5, $ A $ 3: $ A $ 32))
Es ist leichter zu verstehen, gebrochen, wenn es in seine Bestandteile zerfällt, SPIEL und INDEX
- Das SPIEL Funktion sucht nach dem Inhalt von F5 (12) im Bereich A3 bis A32
MATCH (F5, $ A $ 3: $ A $ 32))
- Das INDEX Die Funktion sucht dann im Bereich B3 bis B32 nach der Zelle neben der von MATCH gefundenen Zelle
So, SPIEL findet 12 in Zelle A14 und INDEX findet Ed Bradley in der Zelle neben A14
Hinweis:Das zu den Zellreferenzen hinzugefügte $ teilt Excel 2007 und Excel 2010 mit, dass sich diese Zellreferenzen auch dann nicht ändern, wenn die Formel in andere Zellen kopiert wird (sogenannte absolute Zellreferenzen). Dies verhindert, dass Excel bei jedem Kopieren zu jeder Zellreferenz eine hinzufügt (die Formel würde zu = INDEX (B4: B33, MATCH (F4, A4: A33)) und nicht zu = INDEX ($ B $ 3: $ B $ 32, MATCH) (F4, $ A $ 3: $ A $ 32)), die andernfalls ein möglicherweise falsches Ergebnis liefern könnten).
Ich habe diese Formel in die Spalte rechts neben der Punktspalte eingefügt, um es einfacher zu machen, unsere endgültige Tabelle mit der Spaltenüberschrift von Student zu vervollständigen.
Vervollständigung unserer Rangliste Unsere Schüler
Nachdem wir nun eine Spalte mit den Namen unserer Schüler haben, um die Tabelle zu vervollständigen:
- Verstecke das Punkt und Rang Spalten (löschen Sie sie nicht, da sonst die Formeln unterbrochen werden)
- Kopiere das Prozent Spalte und platzieren Sie es rechts von der ursprünglichen Spalte
- Ändern Sie den Namen des Originals Prozent Spalte zu Klasse
Denken Sie daran, dass wir dies so automatisch wie möglich tun wollten. Um unseren Schülern eine Note zuzuweisen, verwenden wir eine WENN Aussage wie unten gezeigt.
= IF (K350%, "D", IF (K3> 90%, "A", IF (K3> 70%, "B", IF (K3> 50%, "C")))
Diese Formel betrachtet die Spalte Prozent und den Prozentsatz und weist dann basierend auf dem gefundenen Ergebnis einen Buchstaben zu.
Hinweis:Die Reihenfolge ist von entscheidender Bedeutung. Wenn wir sie in der Reihenfolge C, B, A ausführen würden, würden wir nur die Noten D und C erhalten, da Excel alle Zahlen über 50% betrachten und ihnen ein C geben würde und die Formel enden würde.
- Kopieren Sie diese Formel in den Rest der Klasse Säule.
Jetzt haben wir unsere fertige Tabelle, die wie in der folgenden Abbildung aussieht:
Fazit
Das Rang und Perzentil Das Tool aus dem Analysis ToolPak, das in Excel 2007 und 2010 verfügbar ist, ermöglicht es uns, mit Daten zu arbeiten und diese zu ordnen und dann Excel das Perzentil berechnen zu lassen.
In meinem heutigen Beispiel habe ich meine (fiktive) Klasse von Schülern genommen und sie nach ihren Testergebnissen eingestuft. Ich gab ihnen dann eine Note, je nachdem, wo sie gegen ihre Kollegen saßen.
Um dies zu erreichen, habe ich nicht nur das Rang- und Perzentil-Werkzeug verwendet, sondern auch das SPIEL und INDEX Funktionen zum Konvertieren der Ergebnisse, sodass der Name des Schülers neben seiner Note angezeigt wird, und dann auch die WENN Befehl, ihnen automatisch ihre Note zuzuweisen.
Beispiel eines Histogramms, das mit dem Histogramm-Tool aus dem Analyse-Toolpaket in Excel 2007 und Excel 2010 erstellt wurde.
Und schlussendlich...
Dieser Artikel ist genau und nach bestem Wissen des Autors. Der Inhalt dient nur zu Informations- oder Unterhaltungszwecken und ersetzt nicht die persönliche Beratung oder professionelle Beratung in geschäftlichen, finanziellen, rechtlichen oder technischen Angelegenheiten.
