Tabellenüberschrift nach Rang auslesen

Bild

Betrifft: Tabellenüberschrift nach Rang auslesen
von: Joerschi
Geschrieben am: 14.07.2015 18:59:40

Hallo liebes Forum,
folgendes Problem möchte ich lösen (Skizze + Musterdatei unten):
Aus einer Tabelle sollen nach Rang absteigend (größte Werte links) jeweils in einer Zeile die zugehörige Überschrift auf der x-Achse (bzw. horizontal) sowie y-Achse (also vertikal) ausgelesen werden
Im Beispiel sind das die Zellen B10:Y11.
Zwei Beispiele sind bereits kursiv eingetragen.
Skizze:
Userbild
Musterdatei: https://www.herber.de/bbs/user/98822.xlsx
Hätte jemand einen Tip für eine Formellösung?
Danke vorab und liebe Grüße
Joerschi

Bild

Betrifft: Ergänzung
von: Joerschi
Geschrieben am: 14.07.2015 19:06:04
Die Formellösung sollte sich ebenfalls auf "Rang" beziehen, damit bei etwaigen doppelten Werten nicht die "falschen" Überschriften ausgelesen werden :-)

Bild

Betrifft: INDEX(), AGGREGAT() und ZÄHLENWENN() ...
von: der neopa C
Geschrieben am: 14.07.2015 19:30:15
Hallo Joerschi,
... in B10: =INDEX(1:1;AGGREGAT(14;6;SPALTE(1:1)/($A$2:$G$5=B9);ZÄHLENWENN($B9:B9;B9)))
und in B11: =INDEX($A:$A;AGGREGAT(14;6;ZEILE(A1:A5)/($A$1:$G$5=B9);ZÄHLENWENN($B9:B9;B9)))
Gruß Werner
.. , - ...

Bild

Betrifft: AW: INDEX(), AGGREGAT() und ZÄHLENWENN() ...
von: Joerschi
Geschrieben am: 14.07.2015 20:09:53
Dank Dir Werner.
Wie immer.
Funktioniert prima.
Wäre ich nicht drauf gekommen...

Bild

Betrifft: AW: INDEX(), AGGREGAT() und ZÄHLENWENN() ...
von: Joerschi
Geschrieben am: 14.07.2015 23:10:55
ich muss doch nochmal fragen...
Bei doppelten Werten scheint die Formel nicht richtig zu rechnen.
Angenommen in Zelle F3 wird ebenfalls der Wert "100" eingetragen.
Dann werden die falschen Koordinaten ausgelesen. Oder zumindest gemischt?
Userbild
Viele Grüße
Joerschi

Bild

Betrifft: schau ich mir später noch mal an ...
von: der neopa C
Geschrieben am: 15.07.2015 16:51:02
Hallo Joerschi,
... das habe ich gestern so offensichtlich nicht gecheckt. Sorry.
Ich melde mich dazu spätestens Morgen noch einmal.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: schau ich mir später noch mal an ...
von: Joerschi
Geschrieben am: 15.07.2015 16:58:16
Kein Problem Werner.
Ich habe zu danken :-)

Bild

Betrifft: Excelformeln.de
von: RPP63
Geschrieben am: 14.07.2015 19:11:07
Hi!
Schau mal hier (zweiter Teil):
http://www.excelformeln.de/formeln.html?welcher=39
Gruß Ralf

Bild

Betrifft: AW: Excelformeln.de
von: Joerschi
Geschrieben am: 14.07.2015 20:04:40
Dank Dir Ralf.
Der Ansatz ist gut, aber leider falsch herum (es wird von den Koordinaten der Tabellenwert ausgelesen statt anders herum). :-)

Bild

Betrifft: AW: Excelformeln.de
von: Joerschi
Geschrieben am: 14.07.2015 20:05:47
Korrektur: der 2. Teil passt besser. Hatte ich erst übersehen :-)

Bild

Betrifft: mehrfach geschachtelte Matrixfunktionalitätsformel
von: der neopa C
Geschrieben am: 16.07.2015 11:32:22
Hallo Joerschi,
... die Erschwernis Deiner gewünschten Auswertung in Zeile 10:11 sah ich nun darin, dass dieser zusätzlich zur Datenquelle (hier A1:G5) ein weiterer spezif. maßgebender Datenbereich in Zeile 9 vorgegeben ist, welcher seine Werte aus der Datenquelle B2:G5 holt.
Ohne die Wertangaben in Zeile 9 und dafür nur die verbale Aussage in Zeile 7 wäre die anschließende Auswertung des y-Wertes möglicherweise etwas weniger komplex geworden (dafür hätte sich die Formel für den x-Wert verlängert).
Nun, hab ich die Auswertung aber gleich so gestaltet, dass es dieser egal ist, wie die Daten in Zeile 9 sortiert gelistet sind (solange sie auch in B2:G5 wirklich vorhanden sind!).
Die Formel in B11 ist momentan nur für eine korrekte Ermittlung des y-Wertes aufgestellt. Auf eine möglicherweise Optimierung der Formel hab ich verzichtet. Die Formel in B10 hat sich geenüber vorher nur minimal geändert:

 ABCDEFGHIJKLMNOPQRSTUVWXYZ
1 ABCDEF                   
2M10531001100                   
3N4100631004540                   
4O8574321005232                   
5P4510035689100                   
6                          
7 Rang - von größten Wert (links) nach kleinsten Wert (rechts) 
8 123456789101112131415161718192021222324 
9Wert:10010010010010010010089857463565245454032321054331 
10X-Achse:BBDDDFFEABCDEAEFCFABACCE 
11y-Achse:NPMNOMPPOONPOPNNOOMMNMPM 
12                          

Formeln der Tabelle
ZelleFormel
B10=INDEX(1:1;AGGREGAT(15;6;SPALTE(1:1)/($A$2:$G$5=B9); ZÄHLENWENN($B9:B9;B9)))
B11=INDEX($A:$A;AGGREGAT(15;6;ZEILE(A1:A5)/(INDEX($A:$G;1;AGGREGAT(15;6;SPALTE(1:1)/($A$2:$G$5=B9); ZÄHLENWENN($B9:B9;B9))):INDEX($A:$G;5;AGGREGAT(15;6;SPALTE(1:1)/($A$2:$G$5=B9); ZÄHLENWENN($B9:B9;B9)))=B9); ZÄHLENWENN($B9:B9;B9)-SUMMENPRODUKT(($B2:$G5=B9)*($B1:$G1<B10))))


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

Bild

Betrifft: Irgendwo klemmts noch bei der Adaption :-(
von: Joerschi
Geschrieben am: 16.07.2015 21:01:15
Hallo Werner,
zuerst einmal recht herzlichen Dank für die Lösung. Der Wahnsinn...
Ich habe jetzt versucht, beide Lösungen für mich in einer Datei mit anderen Werten zu adaptieren.
Für die Formel in B10 (bei mir B153) ist das gelungen.
Bei der 2. Formel in B11 (bei mir B154) bekomme ich einfach die Fehlermeldung nicht weg. Sitze jetzt schon 2h dran und komme da einfach nicht dahinter, wo es hängt. :-((
Falls Du mal bei Gelegenheit mal ne Minute Zeit hast, könntest Du mal gegenprüfen?
Die Datei dazu anliegend.
https://www.herber.de/bbs/user/98876.xlsx
Falls es klappt, wäre das klasse. Vorab in jedem Fall Danke @Dich
Liebe Grüße
Joerschi

Bild

Betrifft: mit korrekter Formelanpassung und ...
von: der neopa C
Geschrieben am: 17.07.2015 11:03:03
Hallo Joerschi,
... im konkreten Fall mit zusätzlich der Berücksichtigung Deiner neuen x- /y-Wertbezeichnungen kommst Du schon zum Ziel.
Deine neue Achsbezeichnungen lassen sich nicht einfach mehr mit "kleiner" vergleichen (wie in meiner bisherigen Formel), weil ja z.B. "F6" größer als "F13" ist und nicht kleiner. Deshalb hab ich diesen Vergleich durch die Teilformel mit VERGLEICH() ersetzt. Dazu letzten Formelteil:

 ABCD
152 Wert 100,00%100,00%100,00%
153 x-Achse   F6 F6 F6
154 y-Achse   F13 F19 F29

Formeln der Tabelle
ZelleFormel
B153=INDEX($C$3:$EQ$3;AGGREGAT(15;6;SPALTE($A$3:$EQ$3)/($C$4:$EQ$147=B152); ZÄHLENWENN($B152:B152;B152)))
B154=INDEX($C1:$C149;AGGREGAT(15;6;ZEILE(C1:C149)/(INDEX($A1:$EQ149;1;AGGREGAT(15;6;SPALTE($A3:$EQ3)/($C$4:$EQ$149=B152); ZÄHLENWENN($B152:B152;B152))):INDEX($A1:$EQ149;149;AGGREGAT(15;6;SPALTE($A3:$EQ3)/($A$4:$EQ$149=B152); ZÄHLENWENN($B152:B152;B152)))=B152); ZÄHLENWENN($B152:B152;B152)-SUMMENPRODUKT(($D$4:$EQ$149=B152)*(SPALTE($D3:$EQ3)<VERGLEICH(B153;3:3;)))))


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

Bild

Betrifft: AW: Irgendwo klemmts noch bei der Adaption :-(
von: Josef B
Geschrieben am: 17.07.2015 11:04:24
Hallo Werner
Würde für die Bestimmung der Y Achse nicht auch folgende Formel reichen, oder habe ich da etwas übersehen.
=INDEX($A:$A;REST(AGGREGAT(15;6;ZEILE($A$1:$A$5)+SPALTE($A1:$G1)*99/($A$1:$G$5=B9);ZÄHLENWENN($B9:B9; B9));99))
Diese Variante könnte dann Joerschi leichter an seine Tabelle anzupassen.
Gruss Sepp

Bild

Betrifft: wieder eine super Verkürzung Deinerseits ...
von: der neopa C
Geschrieben am: 17.07.2015 11:40:28
Hallo Sepp,
... allerdings hatte ich gestern ja auch geschrieben: "Die Formel in B11 ist momentan nur für eine korrekte Ermittlung des y-Wertes aufgestellt. Auf eine möglicherweise Optimierung der Formel hab ich verzichtet"
Diese hast Du ja nun bereitgestellt. :-)
Damit vereinfacht sich natürlich auch letztendlich die Formel für Joerschi aktuelle Fragestellung zu:
=INDEX($C:$C;REST(AGGREGAT(15;6;ZEILE(C1:C149)+SPALTE($A1:$EQ1)*149/($A$1:$EQ$149=B152);ZÄHLENWENN($B152:B152;B152));149))
und verbessert damit auch gleichzeitig die Auswertungsgeschwindigkeit :-)
Gruß Werner
.. , - ...

Bild

Betrifft: AW: wieder eine super Verkürzung Deinerseits ...
von: Joerschi
Geschrieben am: 17.07.2015 17:20:27
Danke Euch @ Sepp und @ Werner
Das sind hervorragende Lösungen, auf die ich nicht gekommen wäre.
Ihr solltet hier so eine Art "Flattr"-Option einfügen (lassen), so dass sich Fragesteller für Lösungen mit einer Art microdonation bedanken können. Das macht zwar niemanden reich, aber ist immerhin eine kleine Anerkennung der Mühen und Zeitentschädigung.
Mal als Beispiel: siehe hier - rechts mittig: http://www.astronews.com/
Danke nochmal und vorab ein schönes WE
Liebe Grüße
joerschi

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Tabellenüberschrift nach Rang auslesen"