Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.04.2024 12:46:38
Nutzereingaben Speichern und Laden
26.04.2024 11:49:48
VAB und Bedingte Formatierung nimmt die Farbe im Makro nicht
26.04.2024 11:38:46
Acrobat PDF Formularfelder mit Excel ausfüllen
26.04.2024 11:23:04
zählen wenn - aber nur die selektierten Zeilen
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1332to1336
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1332to1336
1332to1336
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
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

331 Forumthreads zu ähnlichen Themen

Daten aus vielen xls-Datein auslesen
Daniel

Hallo Profis,
ich suche einen VBA-Befehl für folgendes:
Ich habe einen Ordner mit vielen xls-Dateien. Diese sollen nun alle in einer Datei zusammengefahren werden, alle Zeilen, wenn der Wert der 2. Spalte links mit "12345" anfängt.
Geht dies?
Vielen Dank vorab.
Daniel
...
18.08.2008 15:07:01
Thread-ID: 1002621
2 Beiträge
Anzeige
Daten sortiert aus anderem Tabellenblatt auslesen
Robert

Hallo,
bräuchte eure Hilfe bitte.
Aus einem Tabellenblatt ("FEHLZEITEN") sollen Daten ausgelesen und in einem anderen Tabellenblatt ("EINSATZPLANUNG") ab Zeile 100 eingefügt und bei Änderungen in "FEHLZEITEN" aktualisiert werden.
Habe da mal eine Beispieldatei zur besseren Veransch...
28.08.2008 11:32:00
Thread-ID: 1005356
3 Beiträge
Datensätze aus Blatt auslesen
bully

Hallo Leute,
ich möchte mit VBA aus dem Blatt2 ganze Datenzeilen auslesen, wenn diese in der Spalte3 den Wert enthalten, den ich in Blatt3 in Zelle F6 vorgebe. Die Datenzeilen sollen dann in Blatt3 ab Zeile14 eingefügt werden.
Nach etlichen Selbstversuchen gebe ich jetzt auf.
Gruss...
02.09.2008 15:55:27
Thread-ID: 1006842
6 Beiträge
Anzeige
Werte aus Matrix auslesen j. mit richtiger Bsp.
trebor-robert

Hallo,
wie kann ich aus einer Matrix, die in einer separaten Arbeitsmappe (im Beispiel der Bereich Arbeitsmappe "B") liegt, den entsprechenden EUR Betrag auslesen, wenn in der Spalte "C" untereinander in den einzelnen Zeilen die Bemessungsgrösse 1 steht (in diesem Fall qm) und in den Spalt...
05.09.2008 13:33:00
Thread-ID: 1007667
5 Beiträge
von Datum bis Datum suchen und Werte in die Spalte
Datum

Hallo liebe Excel-Könner,
ich habe folgendes Problem und finde keine Lösung.
Ich habe in Spalte A von 2006 bis heute Datum stehen, über eine UserForm möche ich in zwei TextBoxen von bis Datum eintragen und in Spalte B in diesem Bereich den WertX.
Ich hoffe Ihr könnt mir helfen!
...
07.09.2008 09:31:20
Thread-ID: 1007931
8 Beiträge
Werte aus anderem Blatt für ComboBox auslesen
mtremer

Hallo Excel-Freunde,
ich glaub ich werde verrückt.
Meine ComboBox füllt sich nicht mehr. Ich versuche den Code so zu ändern, dass die Quelle für die ComboBox wo anders stehen kann. Im folgenden Code müssen die Werte in der Spalte A der Tabelle1 stehen.
Private Sub Worksheet_Change...
17.09.2008 13:17:34
Thread-ID: 1010349
11 Beiträge
Wert innerhalb Array suchen
David

Hallo VBAler,
wenn ich ein Array in der Form
treffer_n(n)
habe, gibt es da eine schnelle und einfache Möglichkeit zu prüfen, ob ein bestimmter Wert in diesem Array vorhanden ist, oder muss ich alle Werte per Schleife einzeln prüfen lassen?
Gruß
David
19.09.2008 08:48:14
Thread-ID: 1010894
3 Beiträge
Bestimmten Wert in einer Zelle finden
Olli

Hallo,
ich lauf gerade wieder mal mit einem Brett vorm Kopf rum und hoffe, mir kann einer hier helfen.
Ich habe in einer Zelle mehrere Werte stehen, die jeweils durch Semikolon getrennt sind (bis zu 15).
Wie kann ich per VBA eine Schleife erstellen, dass solange nach einem Wert gesu...
19.09.2008 08:54:00
Thread-ID: 1010897
5 Beiträge
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
1
8
2
5
3
4
7
10
2
4
2
4
4
3
6
1
3
3
6
6
1
4
2
2
4