Funktion zum Zurückgeben von Zellreferenzen (Adressen) der N höchsten Werte

Question

Ich erstelle eine Kalkulationstabelle, um die Wahlergebnisse zu verfolgen und die gewählten Vertreter zu ermitteln. Wir verwenden ein Verhältniswahlsystem und wenden dabei diemodifizierte Sainte-Laguë-Methode. Die Wahlergebnisse werden in eine Kalkulationstabelle eingegeben und dann durch eine bestimmte Zahl (1,4, 3, 5 usw., bis zu 15 für insgesamt acht Quotienten, wie vom geltenden Gesetz vorgeschrieben) geteilt, um den Quotienten zu erhalten, anhand dessen die Sitze verteilt werden. Mithilfe der bedingten Formatierung hebt die Kalkulationstabelle nun die acht höchsten Zahlen hervor und zeigt, wer welche Position erhält. Dies funktioniert alles wie beabsichtigt.

Wir müssen a) eine Liste der acht erfolgreichen Kandidaten zurückgeben und b) diese Liste den Zellbezug (z. B. D7) und nicht die Zahl zurückgeben lassen. Ich habe mit der LARGEFunktion herumgespielt und kann die acht oberen Quotienten in C24:C31 erfolgreich zurückgeben, indem ich eine Formel verwende, die im Wesentlichen lautet: =LARGE($C$2:$J$21, ROW()-23). Ich kann sie jedoch nicht dazu bringen, die Gitterkoordinaten für diese Quotienten zurückzugeben.

Der Screenshot unten zeigt ein Modell dessen, was ich erreichen möchte. Die grün hervorgehobenen Zellen sind aufgrund der bedingten Formatierung hervorgehoben; es sind die acht größten Werte in C2:J21. Die gelb hervorgehobenen Zellen sind das, was ich erreichen möchte. Um es klarzustellen: Die Daten in Zellreferenz C2:J5 werden durch eine Formel generiert, ebenso sind die Werte in C24:C31 dieselben acht Werte, die oben grün hervorgehoben sind; sie werden durch eine Formel generiert. Die ausgeblendeten Zeilen 6 bis 21 sind im Wesentlichen Duplikate der Zeilen 1 bis 5. Ich habe die Tabelle hochgeladen aufGoogle Drive

Answer 1

Das ist schwierig. Wir können verwenden

=MIN(WENN($C$2:$J$21=Wert, REIHE($C$2:$J$21)))

um die Nummer der ersten Zeile zu finden, in dervalueerscheint. Ebenso

=MIN(WENN($C$2:$J$21=Wert, SPALTE($C$2:$J$21)))

findet die Nummer der ersten Spalte, in dervalueerscheint. Oben sind Arrayformeln.

Wenn die obersten acht Werte C2:J21eindeutig sind, Wir können das Obige verwenden, um einen Wert in diesem Raster zu finden. Dann

INDEX($A$1:$J$21,Zeilennummer,Spaltennummer)

wird diese Zelle indizieren, und wir können verwenden

ZELLE("Adresse", INDEX(obenstehendes))

um die Zeilen- und Spaltenadresse dieser Zelle zu erhalten.

Geben Sie also

=ZELLE("Adresse", INDEX($A$1:$J$21, MIN(WENN($C$2:$J$21=C24,ZEILE($C$2:$J$21))), MIN(WENN($C$2:$J$21=C24,SPALTE($C$2:$J$21)))))

in die Zelle B24, drücken Sie Ctrl+ Shift+ Enterund ziehen/füllen Sie nach unten bis B31.

Anmerkungen:

• CELL("address", …)gibt eine absolute Adresse (mit Dollarzeichen) zurück. Wenn Sie diese nicht möchten, können Sie verwenden

  =ERSATZ(ZELLE(bla bla bla), "$", "")

• Dies verarbeitet doppelte Werte nicht gut. Wenn beispielsweise E3auch 8 enthalten ist (zusätzlich zu D4), wird gemeldet, dass sie beide in D3(erster Zeile und erster Spalte) sind. Dies kann sehr schwer zu beheben sein, aber es wäre möglich,prüfen ob ein solcher Zufall aufgetreten ist, um eine manuelle Korrektur zu ermöglichen. 
• Obwohl dies alles in einer einzigen Zelle erledigt werden kann, kann es Ihnen das Leben auf lange Sicht möglicherweise erleichtern, wenn Sie Hilfsspalten für die Zeilen- und Spaltenwerte verwenden.

Diese Antwort basiert teilweise auf einem Trick vondiese Antwortvon Barry Houdini.