Anzeige
Archiv - Navigation
1456to1460
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Reiter und kleinste Werte

Reiter und kleinste Werte
05.11.2015 12:52:05
PatCo
Hallo,
ich hoffe, dass mir jemand bei meinem Vorhaben helfen kann und dass ich im Vorfeld alles eindeutig beschrieben habe. Diesbgzl. habe ich eine Tabelle zur Veranschaulichung hochgeladen. Zum besseren Verständnis habe ich noch drei weitere Reiter hinzugefügt. Die Tabelle findet man hier: https://www.herber.de/bbs/user/101268.xlsx
Ausgangssituation:
Office 2007
bedingte Excel Kenntnis (Basis, learning by doing)
Vorhaben:
10 Spieler (A - J) spielen an 24 Spieltagen, wovon aber nur die 12 Spieltage mit den niedrigsten Punkten in die Wertung kommen. In meiner Vorlage wären das im Beispiel Zeile 5 die Werte in den Zellen C5 bis M5 + Zelle P5 (14 Punkte). Während dem Spielbetrieb müssen sich der Zwischenstand und die Platzierung automatisch bei neuer Eingabe in den Spieltagen aktualisieren.
Hinweis:
Die Spalten AB, AC und AE sind ausgeblendete Spalten (gelb markiert). Um es anschaulich zu machen, habe ich sie eingeblendet.
Meine Formeln (als Beispiel dient die Spalte 5):
AB5: =RANG(AE5;AE:AE;1)
AC5: keine Formel
AD5: =SUMME(A5:X5)
AE5: =SUMME(KKLEINSTE(C5:Z5;{1;2;3;4;5;6;7;8;9;10;11;12}))
AG5: =WENNFEHLER(INDEX(AB:AB;VERGLEICH(AH5;AC:AC;0)); "")
AH5: =WENNFEHLER(INDEX(AC:AC;VERGLEICH(KKLEINSTE(AE:AE;ZEILE(AC1));AE:AE;0));"")
AI5: =KKLEINSTE(AE:AE;ZEILE()-4)
AG6 - AG14: =AG6=AG5 (Bedingte Formatierung für das "Ausblenden" bei doppelter Platzierung)
Problem 1 (Beispiel Spieler A):
In der Zelle AE18 liegt die Formel für die Ermittlung der 12 niedrigsten Werte aus den Zellen C5 bis Z5, sowie dessen gleichzeitiger Summierung. Diese Formel funktioniert zwar, allerdings nur dann, wenn in den Zellen C5 bis einschließlich N5 ein Wert in der jeweiligen Zelle steht, also den ersten 12 Spieltagen. Steht in einer dieser Zellen kein Wert drin, verlangen die Zellen AB5, AE5 und AI5 nach einer Zahl (#Zahl!) und die Zellen AG5 + AH5 bleiben leer (siehe Reiter „Beispiel 1“ und „Beispiel 2“). Somit kann ich leider erst ab dem 12. Spieltag rechnen. Die Formel muss also anders aussehen, allerdings habe ich keine Ahnung wie.
Problem 2:
Spieler G und Spieler J, sowie Spieler F und Spieler H haben jeweils die gleiche Punktzahl erreicht und sind anhand der fortlaufenden Formel in der Spalte AG richtig platziert, allerdings werden die Spieler J und H nicht angezeigt (rot markiert). Dies kann man mit der Funktion "AGGREGAT" beheben, allerdings geht das in Office 2007 nicht. Daher habe ich eine Formel eingegeben, die nur bedingt funktioniert. Die Formel muss also anders aussehen, allerdings habe ich keine Ahnung wie.
Vielen Dank im Vorfeld für eure Hilfe!
Gruß PatCo

4
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: nutze WENNFEHLER() ...
05.11.2015 13:22:53
...
Hallo PatCo,
... wenn es kein anderes Ausschlusskriterium gibt (z.B. zwingend mind. 12 Spiele notwendig) für beide Beispiele in AE5 z.B. folgende Formel:
=WENNFEHLER(SUMME(KKLEINSTE(C5:Z5;{1;2;3;4;5;6;7;8;9;10;11;12}));SUMME(C5:Z5))
und diese nach unten kopieren.
Gruß Werner
.. , - ...

AW: nutze WENNFEHLER() ...
05.11.2015 13:43:57
PatCo
Hallo Werner,
vielen Dank für die schnelle Rückmeldung und der ersten richtigen Formel! :-)
Problem Nr. 1 ist somit gelöst, d.h. ich kann mit Spieltag 1 beginnen und es werden bei der Zwischensumme und der Platzierung der Wert übernommen, ohne das ein Fehler auftritt. Dies war ja vorher der Fall ((#Zahl!)Nichts desto trotz müssen 12 Spiele gespielt werden, um am Ende in die Wertung zu kommen. Insofern benötige ich mindestens 12 Einträge, egal in welchen der 24 Zellen. Aber wie gesagt - es funktioniert ja jetzt. :-)
Problem Nr. 2 ist damit allerdings noch nicht gelöst! Gemäß der erspielten Punkte werden die Spieler zwar richtig platziert, aber bei Doppelplatzierung die Namen nicht angepasst.
Hier die angepasste Liste mit der neuen Formel in AG5 und die darunter liegenden Zellen: https://www.herber.de/bbs/user/101271.xlsx
Viele Grüße
PatCo
btw: der Betreff sollte "Rangliste und kleinste Werte" heißen und nicht "Reiter und....."

Anzeige
AW: da Du kein Excel 2010 hast ...
05.11.2015 13:58:26
...
Hallo Pat,
... lässt sich das nicht mit AGGREGAT() lösen, wie Du bereits erkannt hast, aber mit einer (echten) Matrixformel. Formel AH5 einfach ziehend nach unten kopieren.
 ACADAEAFAGAHAI
5A2815 1G14
6B3220 1J14
7C2818 3A15
8D3017 4D17
9E3434 5C18
10F3519 6F19
11G1914 6H19
12H3319 8B20
13I3823 9I23
14J2614 10E34

Formeln der Tabelle
ZelleFormel
AH5{=WENNFEHLER(INDEX(AC:AC;KKLEINSTE(WENN(AE$1:AE$15=AI5;ZEILE(A$1:A$15)); ZÄHLENWENN(AI$5:AI5;AI5))); "")}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
AW: Probleme gelöst!!! :-)
05.11.2015 14:55:10
PatCo
Hallo Werner,
damit wäre auch Problem Nr. 2 gelöst! Hat alles geklappt, insofern sage ich tausend Dank für die super schnelle und kompetente Hilfe!!! :-)
Viele Grüße
PatCo

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige