Office: Listenabfrage mit zwei Variablen --> "bester" W

Einen schönen, guten Morgen alle zusammen! :-)

Ich benötige mal wieder eure Hilfe:

Ich habe eine kleine Liste mit mehreren Spalten,

die erste Spalte (X) soll mit dem Inhalt eines Eingabefeldes verglichen werden, in der daraus resultierenden Zeile soll jetzt ein weiterer Wert mit dem Inhalt dieser Zeile verglichen werden und von dem Wert, der "am nächsten" dran ist soll der Index (A, B oder C) ausgegeben werden.

Das Problem für mich an dieser Sache: wenn in der jeweils vorausgehenden oder auch der folgenden Spalte ein Wert vorkommt der besser in Frage kommt (näher dran ist), so soll dessen Index ausgegeben werden.

Habe schon einiges mit INDEX VERGLEICH und KGRÖSSTE sowie der VERWEIS Funktion ausprobiert, aber so richtig hin bekommen habe ich es bis jetzt noch nicht.

Da sich das jetzt bestimmt kompliziert anhört, habe ich mal ein Beispiel angehängt!

Gruße,
Dennis

 

Hallo Dennis,

mit folgenden Formeln müsste es funktionieren:

	A	B	C	D	E	F	G	H
1	Eingabe:	0,7	und:	155
2
3	Ausgabe:	B
4
5	X	A	B	C		Zeile	Spalte	Wert
6	0,05	7,7	2,2	0,8		14	2	225,5
7	0,10	25,4	7,3	2,7
8	0,15	51,5	14,8	5,5
9	0,20	85,5	24,5	9,1
10	0,25	126,8	36,2	13,5
11	0,30	175,2	49,9	18,5
12	0,35	230,5	65,6	24,3
13	0,40	292,5	83,1	30,8
14	0,45		102,4	37,9
15	0,50		123,6	45,7
16	0,55		146,5	54,1
17	0,60		171,1	63,2
18	0,65		197,5	72,9
19	0,70		225,5	83,2
20	0,75			94,1
21	0,80			105,6
22	0,85			117,6
23	0,90			130,3
24	0,95			143,6
25	1,00			157,4
26

Zelle	Formel
B3	=ZEICHEN(G6+64)
F6	=VERGLEICH(B1-0,0001;A5:A25;1)
G6	{=VERGLEICH(MIN(ABS(BEREICH.VERSCHIEBEN($B$5:$D$5;$F$6;0;1;3)-$D$1));ABS(BEREICH.VERSCHIEBEN($B$5:$D$5;$F$6;0;1;3)-$D$1);0)}
H6	=INDEX(B6:D25;F6;G6)

<table><tr><td>Achtung, Matrixformel enthalten!</td></tr><tr><td><span>Die geschweiften Klammern{} werden </span><span>nicht</span><span> eingegeben.</span></td></tr><tr><td><span>Verlasse den Zelleneditor stattdessen mit </span><span>Strg+Shift + Enter</span><span>, anstatt mit Enter alleine.</span></td></tr></table>[/parsehtml]

Zu beachten ist jedoch noch:
dass in Zelle A5 eine 0 zu speichern ist. Damit aber wieder ein "X" angezeigt wird, muss als benutzerdefiniertes Zahlenformat ;;"X" eingegeben werden.

EDIT:
Statt der Formel in Zelle B3 kann man auch folgende Formel verwenden:
=INDEX(B5:D5;1;G6)
Diese hat den Vorteil, dass sie allgemeiner ist (und nicht so speziell auf nur 1 Buchstaben zugeschnitten).

 

Hallo Exl121150,

Danke, dass du dich meinem Problem annimmst!
Auf die Formel in "G6" wäre ich von alleine nicht gekommen.
Leider funktioniert sie nicht. Ich kann sie zwar nachvollziehen
aber den Fehler finde ich nicht. :-(

Matrix-Formel habe ich selbstverständlich beachtet! Die restlichen Formeln funktionieren soweit einwandfrei! Danke schon mal hierfür!

Gruß & gute Nacht!
Dennis

 

Hallo Dennis,

meinst Du das Problem mit den leeren Zellen innerhalb der Matrix B6:D25 und zwar in B14:B25 und C20:C25 ?
Sobald nämlich in D1 ein Wert eingegeben wird, der kleiner als der halbe Wert der 3. Spalte von B6:D25 ist, so wird eine 1 von der Formel in Zelle G6 errechnet, wenn in Spalte 1 der betreffenden Zeile von B6:D25 eine Leerzelle enthalten ist.

Es gibt eine Möglichkeit, ohne die Formeln zu ändern, dass die Formel in G6 im vorhin genannten Fall trotzdem die richtige Spalte liefert:
Indem man im Bereich B14:B25 und C20:C25 jeweils eine große Zahl (zB. 99999) speichert. Diese Zahl muss mindestens so groß sein, dass ihre Hälfte immer noch um einiges größer ist als die einzugebenden Zahlen in D1.
Damit nicht diese (unschönen) Zahlen in der Tabelle angezeigt werden, kann man sie mit dem benutzerdefinierten Zahlenformat ;;; wegblenden.
Ändern sich diese Problembereiche (häufig), so kann man im Bereich B6:D25 eine bedingte Formatierung durchführen, bei der das vorhin genannte benutzerdef. Format angewendet wird, sobald in der betreffenden Tabellenzelle eine solche große Platzhalterzahl (oder eine Zahl, die eine bestimmte Mindestgröße überschreitet) steht.

Darüberhinaus kann man die Formel in Zelle G6 um einiges übersichtlicher/nachvollziehbarer gestalten, indem man im Namensmanager einen neuen Namen festlegt:
zB. Name AbsDiffZeile
Bereich: Tabelle1
bezieht sich auf: =ABS(BEREICH.VERSCHIEBEN($B$5:$D$5;$F$6;0;1;3)-$D$1)
(Die $-Zeichen sind hier ganz wichtig, müssen unbedingt so eingegeben werden!!)
Dann kann man die Formel in Zelle G6 vereinfachen zu:
=VERGLEICH(MIN(AbsDiffZeile);AbsDiffZeile;0)
(die geschweiften Klammern können in diesem Fall sogar entfallen, da Excel im Bereich des Namens die Formel bereits als Matrix-Formel behandelt).

 

Hallo Exl,

Super! Danke! ;-)

Also das war der entscheidende Tip, dass die Leerzellen nicht leer sein dürfen.
Jetzt funktioniert alles so wie es sein soll! Auch deine weiteren Tips habe ich mir angenommen und diese funktionieren ebenfalls.
Ich kann zwar die Funktionsweise gut nachvollziehen, wäre allerdings nie auf diese Formeln, bzw. deren Kombination gekommen. Da fehlt mir dann doch ein bisschen die Erfahrung.

Werde die Tabelle zukünftig um einige Spalten erweitern, ich denke da müssten dann nur die Bezüge angepasst werden, damit das klappt.

Vielen Dank und schönen Tag noch,
Dennis.

 

Hallo Dennis,

nur der Sicherheit halber möchte ich noch erwähnen, da Du die Tabelle um weitere Spalten erweitern willst:
angenommen die Tabelle soll 1 Spalte dazubekommen (Bereich B6:E25 mit Überschrift in B5:E5):
im Namen AbsDiffZeile sind dann Anpassungen durchzuführen
=ABS(BEREICH.VERSCHIEBEN($B$5:$E$5;$F$6;0)-$D$1)
Die beiden letzten Parameter von BEREICH.VERSCHIEBEN($B$5:$E$5;$F$6;0;1;4) kann man in diesem Fall besser weglassen - dann behält der verschobene Bereich die Größe des 1. Parameters ($B$5:$E$5) bei.
Formel in H6: =INDEX(B6:E25;F6;G6)
Formel in B3: =INDEX(B5:E5;1;G6)

 

Hallo,

also ich habe die Tabelle auf 8 Spalten und 100 Zeilen erweitert, Bezüge angepasst - kein Problem. Bis jetzt konnte ich noch keinen Fehler feststellen: saubere Arbeit, Exl121150 !

Lieben Gruß,
Dennis