Anwendung von Verketten in SVerweis

[paljass]

Hi,

und erhalte immer Fehler

Interessant wäre mal gewesen, was für ein Fehler unter welchen Bedingungen auftaucht und was du dagegen gemacht hast - immer diese halbseidenen Angaben.
Aber Ok, ich versuchs mal.
Mit =VERKETTEN("KW";$A$2-1;".$A$2:$Z$5000") bekommst du einen Textstring, mit den SVerweis nichts anfangen kann. Also musst du das wieder noch in die Indirekt-Funktion einbauen, also

Code: Alles auswählen

=Indirekt(VERKETTEN("KW";$A$2-1;".$A$2:$Z$5000"))

damit sollte es eigentlich funktionieren.

Gruß
paljass

[lorbass]

Jetzt möchte gern die fett dargestellten ausdrücke mit Hilfe der Funktion "Verketten" erstellen und erhalte immer Fehler.

Welchen Fehler denn?

Ich habe den Gegenstand deiner Schlaflosen Arbeitstage mal zerpflückt und in genießbare Portionen zerteilt. Das liest sich dann erst mal so:

Code: Alles auswählen

Sequenz ①
==========
SVERWEIS(
	$D65;
	INDIREKT(
		VERKETTEN(
			"KW";
			$A$2-1;
			".";
			<adresse1>
			":";
			<adresse2>
		)
	);
	VERGLEICH(
		G$5;
		$D$5:$H$5;
		0
	);
	0
)

mit

Code: Alles auswählen

Sequenz ②a
==========
<adresse1> :=
	ADRESSE(
		VERGLEICH(
			SVERWEIS(
				$A$64;
				[b]KW28.$A$2:$Z$5000[/b];
				VERGLEICH(
					$A$5;
					$A$5:$Z$5;
					0
				);
				0
			);
			[b]KW28.$A$1:$A$5000[/b];
			0
		)+1;
		4
	);

und

Code: Alles auswählen

Sequenz ②b
==========
<adresse2> :=
	ADRESSE(
		VERGLEICH(
			SVERWEIS(
				$A$64;
				[b]KW28.$A$2:$Z$5000[/b];
				VERGLEICH(
					$A$5;
					$A$5:$Z$5;
					0
				);
				0
			);
			[b]KW28.$A$1:$A$5000[/b];
			0
		)+$A$65;
		8
	)

Darin fallen mir in <adresse1> und <adresse2> die folgenden gleichlautenden Sequenzen auf:

Code: Alles auswählen

Sequenz ③
==========
VERGLEICH(                  |
	$A$5;                    |
	$A$5:$Z$5;               |> ≡ 1 oder #NV
	0                        |
);                          |

Der Wert ist immer identisch mit 1, denn wenn ich einen Zellbreich mit dem Inhalt der ersten Zelle vergleiche, kann nix anderes rauskommen.

Wenn dem nun so ist, dann greift das darüber liegende SVERWEIS immer nur auf die erste Spalte der Matrix KW28.$A$2:$Z$5000 zu:

Code: Alles auswählen

Sequenz ④a
==========
SVERWEIS(
	$A$64;
	[b]KW28.$A$2:$Z$5000[/b];
	1;
	0
);

Das Ergebnis davon kann dann nur der gesuchte Wert selbst (hier: $A$64) oder der Fehlerwert #NV (für »nicht vorhanden«) sein. Da du nun in deinen SVERWEIS-Funktionen grundsätzlich keine Fehlerbehandlung für diesen Fall eingebaut hast, gehe ich davon aus, dass deine Daten so gestaltet sind, dass es gar nicht zum Fehler #NV kommen kann. Damit kann ich diese komplette SVERWEIS-Sequenz durch $A$64 ersetzen:

Code: Alles auswählen

Sequenz ④b
==========
SVERWEIS(                     |
	$A$64;                     |
	[b]KW28.$A$2:$Z$5000[/b];  |> ≡ $A$64 oder #NV
	1;                         |
	0                          |
);

Der äußere VERGLEICH ist dann schon ziemlich einfach:

Code: Alles auswählen

Sequenz ⑤a
==========
VERGLEICH(
	$A$64;
	[b]KW28.$A$1:$A$5000[/b];
	0
)+X;

wobei X in <adresse1> gleich 1, in <adresse2> gleich $A$65 ist.

Mit denselben Argumenten wie in der Sequenz ③ ist auch der VERGLEICH identisch mit 1 bzw. #NV und die Sequenz ⑤a wird reduziert zu

Code: Alles auswählen

Sequenz ⑤b
==========
1+X

Deine Ausgangsfunktion ist dann schön übersichtlich:

Code: Alles auswählen

SVERWEIS(
	$D65;
	INDIREKT(
		VERKETTEN(
			"KW";
			$A$2-1;
			".";
			ADRESSE(
				2;
				4
			);
			":";
			ADRESSE(
				1+$A$65;
				4
			);
		)
	);
	VERGLEICH(
		G$5;
		$D$5:$H$5;
		0
	);
	0
)

Diese Form lebt von meiner Überlegung, dass es aufgrund der realen Daten zu keinem #NV-Fehler kommen kann.

Wenn diese Annahme falsch ist, können die Vereinfachungen (Sequenzen ③, ④a, ④b und ⑤a) nicht so einfach ersetzt werden. Aber warum kümmerst du, @Steffen, dich dann nicht um die Fehlerbehandlung?

So, das ist mir aber nach Bauchgefühl alles irgendwie zu einfach! Daher bitte ich darum, dass die Mitstreiter dieses Forums meine Argumentationen unter die Lupe nehmen und nach meinen Fehlern suchen. – Aber nicht hauen

Gruß
lorbass