Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

MAx-Werte von Wertetabelle aus Überschr. auslesen

MAx-Werte von Wertetabelle aus Überschr. auslesen
21.10.2013 21:07:56
Wertetabelle
Hallo liebes Forum,
bei folgendem Problem komme ich leider nicht weiter.
Ich habe eine Wertetabelle, mit der ich folgendes auslesen möchte:
a) Die maximalen Tabellen-Überschriftenwerte (Referenz) einer Wertetabelle.
Dies vom größten Wert, vom 2.größten Wert, dem 3.größten Wert usw.
b) Das alles unter der Bedingung, dass ich von der Referenzzeile die "Spanne" vorgebe, z. B. zwischen 50-100%.
Ich schätze mal, es ist ein "wilde" Formelkombination aus INDEX + KKGroesste, aber ich finde die Formel leider nicht heraus. Ist mir zu tricky... :-(
Zum schnellen Verständnis eine Skizze sowie eine Beispieldatei mit kurzer Tabelle (wie im Screenshot - mit händisch eingetragenen Werten) sowie langer Tabelle, wie es dann in der "tatsächlichen" Tabelle ausschaut (bei GELB ist die Formel gesucht / bei GRÜN kann die Spanne gewählt werden, auf das sich die Formel bei GELB dann beziehen):
Userbild
Beispieldatei:

Die Datei https://www.herber.de/bbs/user/87728.xls wurde aus Datenschutzgründen gelöscht


Gesucht sind die Formeln in Tab "Tab1_kurz": Zellen L2:N3
Gesucht sind die Formeln in Tab "Tab2_lang": Zellen GV2:GX3
Hat jemand einen Tip oder Ansatz?
Liebe grüße
Joerschi

5
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: MAx-Werte von Wertetabelle aus Überschr. auslesen
22.10.2013 08:46:14
Wertetabelle
Kurze Ergänzung / Klarstellung zu Punkt b) oben
Die "Spanne" bezieht sich auf die Überschriften, welche wiederum aus Prozentzahlen bestehen.
Hier sollen bei fixer Vorgabe der "Spanne" dann nur die jeweiligen Tabellenwerte unter den Prozentzahlen ausgelesen werden, welche das Kriterium erfüllen.
(Bei der Beispielvorgabe oben zw. 50-100 % ist die zweite Karte der anhängten Beispieldatei gemeint, nicht der Screenshot - dort könnte man eine Spanne von 0,0%-4,0% festlegen, also die gesamte Tabelle)

KKLEINSTE und KGRÖSSTE
22.10.2013 08:46:38
Erich
Hi Joerschi,
vielleicht so?
 LMNOPQ
1Max Nr. 1Max Nr. 2Max Nr. 3 Referenz-Werte zwischen 
23,00%2,50%2,00% Min:0,00%
31,00%0,00%0,50% Max.3,00%

Formeln der Tabelle
ZelleFormel
L2{=INDEX($B$1:$J$1;VERGLEICH(KKLEINSTE(WENN(($Q$2<=$B$1:$J$1)*($B$1:$J$1<=$Q$3); $B2:$J2); SPALTE(A2)); $B2:$J2;0))}
M2{=INDEX($B$1:$J$1;VERGLEICH(KKLEINSTE(WENN(($Q$2<=$B$1:$J$1)*($B$1:$J$1<=$Q$3); $B2:$J2); SPALTE(B2)); $B2:$J2;0))}
N2{=INDEX($B$1:$J$1;VERGLEICH(KKLEINSTE(WENN(($Q$2<=$B$1:$J$1)*($B$1:$J$1<=$Q$3); $B2:$J2); SPALTE(C2)); $B2:$J2;0))}
L3{=INDEX($B$1:$J$1;VERGLEICH(KGRÖSSTE(WENN(($Q$2<=$B$1:$J$1)*($B$1:$J$1<=$Q$3); $B3:$J3); SPALTE(A3)); $B3:$J3;0))}
M3{=INDEX($B$1:$J$1;VERGLEICH(KGRÖSSTE(WENN(($Q$2<=$B$1:$J$1)*($B$1:$J$1<=$Q$3); $B3:$J3); SPALTE(B3)); $B3:$J3;0))}
N3{=INDEX($B$1:$J$1;VERGLEICH(KGRÖSSTE(WENN(($Q$2<=$B$1:$J$1)*($B$1:$J$1<=$Q$3); $B3:$J3); SPALTE(C3)); $B3:$J3;0))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

In Zeile 2 steht nicht KGRÖSSTE, sondern KKLEINSTE, denn in deinem Beispiel werden hier
die Spalten mit den drei kleinsten Werten ausgegeben. -4 ist kleiner als -3.
Oder sollen in den Zeilen 2 und 3 die Absolutbeträge vergleichen werden?
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
AW: KKLEINSTE und KGRÖSSTE
22.10.2013 09:30:39
Joerschi
Hallo Erich,
Es funktioniert. vielen lieben Dank.
Auf dieses Formelmonster wäre ich nicht gekommen.
(Du hattest natürlich Recht, in Zeile 2 waren die kleinsten Werte angegeben.)
Trotzdem 3 Fragen zum Verständnis:
1. An welcher Stelle definiert sich nun, dass man den 2.-, 3.- oder x.-größten Wert ausgelesen haben möchte?
Im Optimalfall würde ich gern die Überschriften bei den Formeln mit 1, 2, 3 usw. benennen, um dann in der Formel untendrunter darauf zu verweisen, welcher Rangwert (also 1., 2. 3. usw.) ausgelesen werden soll.
2. Welche Bewandtnis hat es mit mit dem Formelteil Spalte(). Dieser reicht ja direkt in die Wertetabelle hinein.
3. In der Beispieltabelle habe ich die Formel auf die "große" Tabelle der zweiten Kartei angepasst.
Funktioniert ebenfalls, aber: Bei dem 2.höchsten Wert (Zelle GV2) - welcher genauso hoch ist wie Wert 1, nur eben etwas weiter rechts steht - müsste eigentlich 98% ausgegeben werden (Ich habe dort angenommen, dass mit umstellen von Spalte() der jeweils nächsthöhere Wert ausgegeben wird). Stattdessen erfolgt die gleiche Ausgabe wie bei Wert 1.
Überarbeitete Beispieltabelle: https://www.herber.de/bbs/user/87735.xls
Irgendwo habe ich noch einen Denkfehler...
Viele Grüße
Joerschi

Anzeige
AW: KKLEINSTE und KGRÖSSTE
22.10.2013 10:08:29
Erich
Hi Joerschi,
zuerst zu 3.:
Wenn in Zeile 2 Werte mehrfach auftreten, reicht zur Bestimmung der richtigen Spalte der Wert natürlich nicht aus,
die jeweilige Position spielt dann auch eine Rolle.
Das kann man berücksichtigen z. B. mit der Änderung der Werte um einen kleinen,
von der Position (Spalte) abhängigen Betrag, etwa -SPALTE($B2:$GT2)%%%)
So sieht die Formel dann aus:
 GVGWGX
1123
297,50%98,00%98,50%

Formeln der Tabelle
ZelleFormel
GV2{=INDEX($B$1:$GT$1;VERGLEICH(KGRÖSSTE(WENN(($HA$2<=$B$1:$GT$1)*($B$1:$GT$1<=$HA$3); $B2:$GT2-SPALTE($B2:$GT2)%%); SPALTE(A2)); $B2:$GT2-SPALTE($B2:$GT2)%%;0))}
GW2{=INDEX($B$1:$GT$1;VERGLEICH(KGRÖSSTE(WENN(($HA$2<=$B$1:$GT$1)*($B$1:$GT$1<=$HA$3); $B2:$GT2-SPALTE($B2:$GT2)%%); SPALTE(B2)); $B2:$GT2-SPALTE($B2:$GT2)%%;0))}
GX2{=INDEX($B$1:$GT$1;VERGLEICH(KGRÖSSTE(WENN(($HA$2<=$B$1:$GT$1)*($B$1:$GT$1<=$HA$3); $B2:$GT2-SPALTE($B2:$GT2)%%%); SPALTE(C2)); $B2:$GT2-SPALTE($B2:$GT2)%%%;0))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Deine Frage 2 beantwortet die Frage 1 schon. Wenn du in Excel die Formel GV2 bearbeitest, siehst du
beim Tipp-Text, dass im Ausdruck KGRÖSSTE(Matrix; k) an der Stelle des k der Ausdruck SPALTE(A2) steht.
SPALTE(A2) ist gleich 1 - die Nummer der Spalte A.
SPALTE(B2) ist gleich 2 - die Nummer der Spalte B. Das stehtz in Zelle GW2.
Das k beim KGRÖSSTE wird also via SPALTE(...) erzeugt. Sonst müsste man in GV2 eine 1, in GW2 eine 2 usw. schreiben
und hätte damit viele verschiedene und manuell zu bearbeitende Formeln.
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
AW: KKLEINSTE und KGRÖSSTE
22.10.2013 11:42:27
Joerschi
Hallo Erich,
vielen Dank für die Lösung.
Die Rangordnung habe ich jetzt so gelöst, dass Spalte() statt einer harten Zahl (die tatsächlich manuell aufwändig zu ändern wäre) mit Zeilenüberschrift 1,2,3 ansteuere.
wenn GV1=1: =INDEX($B$1:$GT$1;VERGLEICH(KGRÖSSTE(WENN(($HA$2
Danke Dir übrigens für Deine bisherigen, geduldigen Ausführungen.
Beste Grüße
Joerschi

347 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige