Sverweis, Intervall-Problem

[Knirschi]

Hallo Forum-Gemeinde,

ich habe leider ein Problem mit der Sverweis-Formel (sofern diese in meinem Beispiel überhaupt sinnvoll ist).

Ich habe eine Liste mit ca. 20.000 Intervallen und eine Liste mit Suchwerten die genau (oder gar nicht) in einem der Intervalle vorkommen.
Wenn der Wert vorkommt, soll die Nr. des Intervalls ausgegeben werden.
Leider steh ich hier etwas auf der Leitung und konnte bisher noch keine Lösung finden.

Im Anhang findet ihr ein Excel, wie ich mir das Ganze ca. vorstelle bzw. wie das Ganze aussieht.

Hoffe ihr könnt mir weiterhelfen.

LG
Knirschi

[BoskoBiati]

Hi,

das wird nicht realisierbar sein, da hier keine Zahlen sondern Text vorliegen! Außerdem ist kein einziger Suchwert in den Intervallen vorhanden!

Wären das Zahlen, dann ginge das so.

Code:

=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE($A$1:$A$1000)/($B$1:$B$1000<=F2)/($C$1:$C$1000>=F2);1));"nicht da")

[Wastl]

Moin,

es geht auch mit Text wie in dem Beispiel mit der Formel

PHP-Code:

=INDEX($A$2:$A$6;VERGLEICH($F$2:$F$6;$B$2:$B$6;1)) 


[BoskoBiati]

Hi Wastl,

ich habe mich in meinem ersten Post vertan, als ich schrieb, dass keine der Zahlen in einem Intervall liegt. Im Gegenteil, die liegen fast alle in mehreren Intervallen.

Arbeitsblatt mit dem Namen 'Tabelle1 (2)'
	A	B	C	D	E	F	G	H
1	Nr. Intervall	Anfangswert	Endwert			Suchwert	Soll-Ausgabe
2	1	101991	1019999	WAHR	WAHR	1019910	1	5
3	2	202991	2029999	WAHR	WAHR	1019910	2
4	3	303991	3039999	WAHR	WAHR	1019910	3
5	4	404991	4049999	WAHR	WAHR	1019910	4
6	5	505991	5059999	WAHR	WAHR	1019910	5
7
8	Nr. Intervall	Anfangswert	Endwert			Suchwert	Soll-Ausgabe
9	1	101991	1019999	WAHR	FALSCH	2029957	1	5
10	2	202991	2029999	WAHR	WAHR	2029957	2
11	3	303991	3039999	WAHR	WAHR	2029957	3
12	4	404991	4049999	WAHR	WAHR	2029957	4
13	5	505991	5059999	WAHR	WAHR	2029957	5
14
15	Nr. Intervall	Anfangswert	Endwert			Suchwert	Soll-Ausgabe
16	1	101991	1019999	WAHR	WAHR	303997	1	3
17	2	202991	2029999	WAHR	WAHR	303997	2
18	3	303991	3039999	WAHR	WAHR	303997	3
19	4	404991	4049999	WAHR	WAHR	303997	4
20	5	505991	5059999	WAHR	WAHR	303997	5
21
22	Nr. Intervall	Anfangswert	Endwert			Suchwert	Soll-Ausgabe
23	1	101991	1019999	WAHR	FALSCH	4049968	1	5
24	2	202991	2029999	WAHR	FALSCH	4049968	2
25	3	303991	3039999	WAHR	FALSCH	4049968	3
26	4	404991	4049999	WAHR	WAHR	4049968	4
27	5	505991	5059999	WAHR	WAHR	4049968	5
28
29	Nr. Intervall	Anfangswert	Endwert			Suchwert	Soll-Ausgabe
30	1	101991	1019999	WAHR	FALSCH	5059970	1	5
31	2	202991	2029999	WAHR	FALSCH	5059970	2
32	3	303991	3039999	WAHR	FALSCH	5059970	3
33	4	404991	4049999	WAHR	FALSCH	5059970	4
34	5	505991	5059999	WAHR	WAHR	5059970	5

Zelle	Formel
D2	=$F$2>=B2
E2	=$F2<=C2
H2	=INDEX($A2:$A6;VERGLEICH($F2:$F6;$B2:$B6;1))
H9	=INDEX($A9:$A13;VERGLEICH($F9:$F13;$B9:$B13;1))
H16	=INDEX($A16:$A20;VERGLEICH($F16:$F20;$B16:$B20;1))
H23	=INDEX($A23:$A27;VERGLEICH($F23:$F27;$B23:$B27;1))
H30	=INDEX($A30:$A34;VERGLEICH($F30:$F34;$B30:$B34;1))

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Du siehst aber auch, was Deine Formel anzeigt, wenn es sich um Zahlen handelt.

[Elex]

Hi

G2    =WENNFEHLER(VERWEIS(2;1/((F2>=$B$2:$B$6)*(F2<=$C$2:$C$6));$A$2:$A$6);"")

[Jockel]

Moin,

... in dem Beispiel mit der Formel

PHP-Code:

=INDEX($A$2:$A$6;VERGLEICH($F$2:$F$6;$B$2:$B$6;1)) 


Hallo, dass das Suchkriterium hier ein Bereich ist, ist das ein besonderer Trick..? Was bewirkt das genau..? Warum muss das so...? Was ist der signifikante Unterschied zu ..:

Code:

=INDEX($A$2:$A$6;VERGLEICH(F2;$B$2:$B$6;1))

... ohne beide Formeln auf Richtigkeit geprüft zu haben...

[Wastl]

Ups @ Jockel,

das hab ich mir irgendwann mal angewöhnt.
Damit kann sie überall stehen.
Deine Formel tut genauso, es sei denn, du ziehst die zuweit nach unten, dann bringt sie #NV
und außerdem ist sie kürzer.

Ich bin neulich über noch was gestoßen was Formeln verlängert und du weniger überlegen musst:
wenn die Formel in der selben Reihe liegt wie das was gezählt werden soll.
Excel weiß dann was gemeint ist.
=Zählenwenn(A:A;A:A)

@ Boskobati

Schöne Darstellung, die aber nicht den Vorgaben des TE entspricht.

Sverweis - habe ich 1998 gelernt - funktioniert nicht mit Zahlen. Punkt
Wenn Serweis mit Zahlen funktionieren soll, mach aus den Zahlen Texte )
Hier is Sverweis nicht anwendbar weil Suchspalte links ist
Meine Variante ist nur Sverweis nach Links mit den oben gültigen Bedingungen.

[Jockel]

Hallo, also kein sittlicher Nährwert..?!? ... außer, das n andere Fehlermeldung kommt... ... und ein Blick in die Formelauswertung zeigt..:

   

[Wastl]

Hallo, also kein sittlicher Nährwert..?!?

Nein, Nährwert nicht, nur muss ich weniger nachdenken bzw. mache weniger Fehler.

Excel ist bei mir wie das Meer. Mal gibt es Wellenberge, so wie eben, mal Wellentäler, wo ich Excel nicht mal aufmache.
Das erste Mal mit ungleichen Bereichen, die dann nicht tun, bin ich bei Summenprodukt gestoßen, was man heute mit Zählenwenns ersetzt.
Es gibt halt so Kniffe die gut sind.
Einen dieser guten Kniffe hab ich mal vor Jahren WF abgeguggt:
=DATEDIF(MIN(C14;A14);MAX(C14;A14);"d")
ich kann mir nie merken, ob zu erst der kleinere Wert kommt oder der größere. Min in Max ändere ich leichter als die Zelladressen.

[BoskoBiati]

Hi Wastl,

Sverweis - habe ich 1998 gelernt - funktioniert nicht mit Zahlen. Punkt

völliger Quatsch!

Das erste Mal mit ungleichen Bereichen, die dann nicht tun, bin ich bei Summenprodukt gestoßen, was man heute mit Zählenwenns ersetzt.

Auch ZÄHLENWENNS braucht gleiche Bereiche, ist längst nicht so flexibel wie SUMMENPRODUKT und versagt an manchen Stellen ganz.


Die Vorgaben des TE sind auch sehr dubios, was ich mit meinem Beitrag nur zeigen wollte. Sind die Werte Zahlen, dann überlappen die Bereiche. Sind es keine Zahlen, dann funktionieren keine Vergleiche mit den Operanden >, = oder
Deine Formel funktioniert nur, weil Texte anders behandelt werden als Zahlen. Außerdem bringt sie falsche Ergebnisse, wenn eine Zahl nicht im Intervall ist:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G
1	Nr. Intervall	Anfangswert	Endwert			Suchwert	Soll-Ausgabe
2	1	0101991	01019999			01119910	1
3	2	0202991	02029999			02029957	2
4	3	0303991	03039999			0303997	3
5	4	0404991	04049999			04049968	4
6	5	0505991	05059999			05059970	5

Zelle	Formel
G2	=VERGLEICH(F2;$B$2:$B$6;1)

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Die erste Zahl ist definitiv nicht im ersten Intervall!

So sollte es funktionieren:

Arbeitsblatt mit dem Namen 'Tabelle1'
	F	G
1	Suchwert	Soll-Ausgabe
2	01119910	nicht da
3	02029957	2
4	0303997	3
5	04049968	4
6	05059970	5

Zelle	Format	Wert
G2	[=0]"nicht da";Standard	0

Zelle	Formel
G2	=VERGLEICH(F2;$B$2:$B$6;1)*(WENNFEHLER(VERGLEICH(F2;$C$2:$C$6;1);0)<>VERGLEICH(F2;$B$2:$B$6;1))

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg