Office: "SVERWEIS" und Datumsbereich

Guten Morgen.

Ich habe ein Problem mit der SVERWEIS-Funktion und Datumsbereichen. SVERWEIS funktioniert mit ungefährer Übereinstimmung bei einem Datumsbereich meines Erachtens nicht. Ich habe schon auf diversen Seiten nachgelesen und vermute, dass sich mein Problem mit der SUMMEPRODUKT-Funktion beheben lässt, aber leider habe ich es nicht geschafft das umzusetzen.

Ich habe zwei Bilder beigefügt. Ich würde gern Folgendes erreichen:

In 'Daten und Werte'!K116 (im Bild: 24.10.2014) soll ein Wert errechnet werden. Dazu soll er das Datum aus der Zelle G116 nehmen und im Bereich 'Datumsbereiche'!$A$110:$C$119 suchen und den Wert der 3. Spalte zurückgeben (im Bild: zwischen dem 01.01.2014 und dem 01.01.2015, sodass der Wert 60% aus der Zeile 01.01.2014 zurückgegeben werden soll).

Vielen Dank vorab!

OlliSt

:)

 

Du müsstest für den SVerweis die DatenQuelle aufsteigend sortieren, bei dir ist das Datum absteigend sortiert.

.mcs{margin-top:5pt;}.mcs table{border-collapse:collapse;}.mcs table td{border:1px solid #999999;color:#000000;background-color:#FFFFFF;font:11pt Calibri, sans-serif;padding:1pt 2pt;text-align:left;vertical-align:middle;}.mcs .foot td{border:0;font-size:9pt;color:#888888;padding:0;}.mcs .foot{clear: both;}.mcs .foot td{background-color:transparent;}.mcs .sheet td:first-child{background-color:#ddddee;text-align:center;}.mcs .sheet tr:first-child td{background-color:#ddddee;text-align:center;}.mcs .ffarial{font-family:arial;}.mcs .r{text-align:right;}.mcs .formulas tr:first-child td{background-color:#eeaaaa;}.mcs .cond tr:first-child td{background-color:#aaaaee;}.formats, .names, .formulas, .cond, .fonts, .valid, .notice{float: none;margin: 10pt 10pt 0 0;}

	B	C	D	E	F
2	01.01.2009	1
3	01.01.2010	2
4	01.01.2011	3
5	01.01.2012	4
6	01.01.2013	5
7	01.01.2014	6		01.01.2014	6
8	01.01.2015	7		24.10.2014	6
9	01.01.2016	8		01.01.2015	7
10	01.01.2017	9
11	01.01.2018	10

Zelle	Formel
F7:F9	=SVERWEIS(E7;$B:$C;2;WAHR)

 

Hi,

oder mit dem gezeigten Aufbau so:

Arbeitsblatt mit dem Namen 'Tabelle1'
*	A	B	C	D	E	F
1	01.01.2018	100%	*	*	*	*
2	01.01.2017	90%	*	*	*	*
3	01.01.2016	80%	*	*	25.04.2014	0,6
4	01.01.2015	70%	*	*	*	0,6
5	01.01.2014	60%	*	*	*	*
6	01.01.2013	50%	*	*	*	*
7	01.01.2012	40%	*	*	*	*
8	01.01.2011	30%	*	*	*	*

Zelle	Formel
F3	=INDEX(B:B;VERGLEICH(E3;A:A;-1)+1)
F4	=INDEX(B:B;VERGLEICH(E3-365;A:A;-1))

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

 

Hallo.

Super. Vielen Dank für die schnellen Antworten!


OlliSt

 

Hallo Edgar, ich denke das funktioniert am Jahreswechsel nicht richtig:
.mcs{margin-top:5pt;}.mcs table{border-collapse:collapse;}.mcs table td{border:1px solid #999999;color:#000000;background-color:#FFFFFF;font:11pt Calibri, sans-serif;padding:1pt 2pt;text-align:left;vertical-align:middle;}.mcs .foot td{border:0;font-size:9pt;color:#888888;padding:0;}.mcs .foot{clear: both;}.mcs .foot td{background-color:transparent;}.mcs .sheet td:first-child{background-color:#ddddee;text-align:center;}.mcs .sheet tr:first-child td{background-color:#ddddee;text-align:center;}.mcs .ffarial{font-family:arial;}.mcs .bcFF0000{background-color:#FF0000;}.mcs .r{text-align:right;}.mcs .formulas tr:first-child td{background-color:#eeaaaa;}.mcs .cond tr:first-child td{background-color:#aaaaee;}.formats, .names, .formulas, .cond, .fonts, .valid, .notice{float: none;margin: 10pt 10pt 0 0;}

	B	C	D	E	F	G
15	01.01.2018	10
16	01.01.2017	9
17	01.01.2016	8		31.12.2013	5	5
18	01.01.2015	7		01.01.2014	5	5
19	01.01.2014	6		02.01.2014	6	6
20	01.01.2013	5		24.10.2014	6	6
21	01.01.2012	4		01.01.2015	6	6
22	01.01.2011	3		02.01.2015	7	7
23	01.01.2010	2
24	01.01.2009	1

Zelle	Formel
F17:F22	=INDEX($C:$C;VERGLEICH(E17;$B:$B;-1)+1)
G17:G22	=INDEX($C:$C;VERGLEICH(E17-365;$B:$B;-1))

 

Hi,

liegt aber möglicherweise an der Funktion VERGLEICH:

Arbeitsblatt mit dem Namen 'Tabelle1'
*	A	B	C	D	E
1	43101	100%	*	*	*
2	42736	90%	42735	0,8	0,8
3	42370	80%	42736	0,8	0,8
4	42005	70%	42737	0,9	0,9

Zelle	Formel
D2	=INDEX(B:B;VERGLEICH(C2;A:A;-1)+1)
E2	=INDEX(B:B;VERGLEICH(EDATUM(C2;-12);A:A;-1))

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

 

Mit Parameter -1 sucht Vergleich() nach dem kleinsten Wert, der größer oder gleich dem angegebenen Suchbegriff ist, solange die Matrix absteigend sortiert ist.

Dadurch funktioniert es aber im Beispiel nicht, den eigentlich wollen wir nach dem größten Wert suchen, der kleiner oder gleich dem gesuchten ist, und das geht (auch bei der Vergleich()-Funktion) nur bei einer aufsteigend sortierten Matrix.

 

Hi,

das erklärt aber nicht den Fehler bei 42736!

 

Doch, denn du addierst ja ganz am Ende +1
=INDEX(B:B;VERGLEICH(C2;A:A;-1)+1)

 

Hallo, ich würde hier so tun..:

Arbeitsblatt mit dem Namen 'Blatt1'
	A	B	C	D	E	F
1	01.01.2018	100,00%
2	01.01.2017	90,00%
3	01.01.2016	80,00%
4	01.01.2015	70,00%			01.01.2014	60,00%
5	01.01.2014	60,00%			24.10.2014	60,00%
6	01.01.2013	50,00%			01.01.2015	70,00%
7	01.01.2012	40,00%
8	01.01.2011	30,00%

Zelle	Formel
F4	=MAX(INDEX(B:B*(A:A<=E4);))

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

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