wake-up-neo.com

Ranking in Excel mit mehreren Kriterien

Zum Beispiel muss ich eine Verdienstliste mit wenigen Schülern erstellen, die auf der Gesamtpunktzahl (Spalte C) und dann auf höheren Noten in math (Spalte B) basiert. 

A        B  C   D
-------------------------
Student1 80 220 1
Student2 88 180 3
Student3 90 180 2

Die erwartete Verdienstposition ist in Spalte D angegeben. 

Ich kann die Funktion RANK verwenden, aber ich kann dies nur für eine Spalte (Gesamtzahl) tun. Wenn die Gesamtzahl von mehreren Schülern gleich ist, konnte ich keine Lösung dafür finden. 

5
user3717550

Sie können dies in D1 versuchen

=COUNTIF($C$1:$C$99,">"&C1)+1+SUMPRODUCT(--($C$1:$C$99=C1),--($B$1:$B$99>B1))

und dann kopieren/ausfüllen. Lassen Sie mich wissen, ob das hilft.

Erläuterung

Ihr erstes Kriterium befindet sich in Spalte C, und das zweite Kriterium befindet sich in Spalte B. Grundsätzlich zählt es zuerst die Anzahl der Einträge ($ C $ 1: $ C $ 99), die größer sind als der Eintrag selbst ($ C1). Für die erste in der Rangliste erhalten Sie Null, daher müssen Sie 1 zu jedem Ergebnis (+ 1) hinzufügen. Bis hierher erhalten Sie doppelte Platzierungen, wenn Sie den gleichen Wert zweimal haben.
Daher müssen Sie ein weiteres Argument hinzufügen, um einige zusätzliche Berechnungen basierend auf den zweiten Kriterien durchzuführen:
Um die Verbindungssituation zu lösen, müssen Sie sumproduct zwei Array-Formeln verwenden und das Ergebnis zum vorherigen Argument hinzufügen. Das Ziel besteht darin, die Anzahl der Einträge zu ermitteln Entspricht diesem Eintrag mit $ C $ 1: $ C $ 99 = C1 und hat einen größeren Wert in der zweiten Kriterienspalte $ B $ 1: $ B $ 99> B1:

Sie addieren -- um TRUE und FALSE zu s und 1 s, damit Sie sie multiplizieren können:
SUMPRODUCT(--($C$1:$C$99=C1),--($B$1:$B$99>B1))
Das erste Array ist zu sehen, wie viele Bindungen Sie in den ersten Kriterien haben. Und das zweite Array besteht darin, die Anzahl der größeren Werte als den Eintrag selbst zu ermitteln.

Anmerkung Sie können beliebig viele Einträge zu Ihren Spalten hinzufügen. Denken Sie jedoch daran, die Bereiche in der Formel zu aktualisieren. Derzeit ist die Einstellung auf 99 festgelegt. Sie können sie auf erweitern so viele Zeilen wie Sie möchten.

6
Marcel

Manchmal bietet eine helper -Spalte eine schnelle und berechnungseffiziente Lösung. Das Hinzufügen der mathematischen Noten zu den Gesamtmarken als Dezimalzahl sollte eine Zahl ergeben, die gemäß Ihren Kriterien eingestuft wird. Verwenden Sie in einer nicht verwendeten Spalte rechts diese Formel in Zeile 2,

=C2+B2/1000

Füllen Sie bei Bedarf nach. Sie können jetzt eine herkömmliche RANK-Funktion in dieser helper -Spalte wie =RANK(D2, D$2:D$9) für Ihre Ordnungszahlen verwenden.

1
user4039065

Sehr einfach (oder zumindest viel einfacher als die durch die beste Antwort ) "math" -Lösung: Eine lineare Kombination mit Gewichten ..__

weighted_marks = 10*colC + colB

sortieren Sie dann gewichtete Marken mit der einfachen Rangfunktion.

Es löst Ihr Problem, indem es das Ranking erstellt, das Sie benötigen.

1
Tomaso Neri

Wenn Sie die Anzahl der Zeilen oder die Anzahl der in den Kriterien verwendeten Zahlen nicht begrenzen möchten, kann die Vorgehensweise von Jeeped erweitert werden. Sie können die folgenden Formeln in den Zellen D2 bis L2 verwenden, vorausgesetzt, es gibt drei Kriterien, das erste in Spalte A, das zweite in Spalte B und das dritte in Spalte C:

=RANK($A2,$A:$A,1)
=RANK($B2,$B:$B,1)
=D2*2^27+E2
=RANK(F2,F:F,1)
=RANK($C2,$C:$C,1)
=G2*2^27+H2
=RANK(I2,I:I,1)
=J2*2^27-ROW()
=RANK(K2,K:K,0)

Die Formeln müssen abgeschrieben werden. Das Ergebnis befindet sich in Spalte L. Verbindungen werden anhand der Zeilennummer gebrochen.

Wenn Sie ein viertes Kriterium hinzufügen möchten, können Sie folgende Schritte ausführen, nachdem Sie die obigen Formeln verwendet haben:

  1. Fügen Sie das neue Kriterium zwischen den Spalten C und D hinzu.
  2. Fügen Sie drei neue Spalten zwischen den Spalten I und J ein.
  3. Kopieren Sie die Spalten G: I in die neuen Spalten J: L.
  4. Kopieren Sie Spalte G in Spalte M und überschreiben Sie deren Inhalt.
  5. Ändern Sie die Formel in Spalte L, um auf das neue Kriterium zu verweisen.

Der in den Formeln verwendete Faktor 2^27 gleicht die Genauigkeit von 53 Bits aus, die in doppeltgenauen Zahlen verfügbar sind. Dies reicht aus, um das Zeilenlimit der aktuellen Excel-Versionen zu decken.

0