komplizierte Summewenns oder Sverweis ??

Forumthread: komplizierte Summewenns oder Sverweis ?

komplizierte Summewenns oder Sverweis ?

03.01.2013 17:37:33

Peter

Guten Abend
Ich sollte aus einer Tabelle (J1:L20) einen Wert abfragen, der mehreren Bedingungen entspricht:
In Spalte J entspricht das Kriterium demjenigen, das in Zelle A1 eingetragen ist
Aus Spalte L wird Wert übernommen, bei dem das Datum in Spalte K demjenigen von Zelle E1 entspricht, oder falls kein solches vorhanden, das nächste ältere Datum.
Die Tabelle ist jeweils zuerst nach Datum und dann nach Buchstabe in Spalte J sortiert.
Leider bin ich mit meinen Versuchen gescheitert.
Gruss, Peter
https://www.herber.de/bbs/user/83239.xlsm

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: komplizierte Summewenns oder Sverweis ?

03.01.2013 21:49:40

silex1
INDEX mit Matrixformel

04.01.2013 00:08:17

Erich
AW: INDEX mit Matrixformel

04.01.2013 23:25:02

Peter
noch ein WENN

05.01.2013 00:32:14

Erich
AW: noch ein WENN

05.01.2013 22:43:47

Peter

AW: komplizierte Summewenns oder Sverweis ?

03.01.2013 21:49:40

silex1

Hallo,
nutze SUMMENPRODUKT() oder nimm die Pivottabelle!
VG, Rene

INDEX mit Matrixformel

04.01.2013 00:08:17

Erich

Hi Peter,
so ganz sicher bin ich nicht, wie du das meinst. Vielleicht so?

	A	B	C	D	E
1	G	13		Monat	01.06.2012

Formeln der Tabelle

Zelle	Formel
B1	{=INDEX(mSatz;MAX(ZEILE(1:20)(mKZE=A1)(mDat<=cANF)))}

Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

Matrix verstehen

Namen in Formeln

Zelle	Name	Bezieht sich auf
B1	cANF	=Abfrage!$E$1
B1	mDat	=Abfrage!$K$1:$K$20
B1	mKZE	=Abfrage!$J$1:$J$20
B1	mSatz	=Abfrage!$L$1:$L$20

	J	K	L
1	A	01.01.2010	19
2	B	01.01.2010	82
3	C	01.01.2010	449
4	D	01.01.2010	15
5	E	01.01.2010	105
6	FS	01.01.2010	141
7	G	08.03.2011	114
8	A	01.01.2012	225
9	A	01.01.2012	110
10	B	01.01.2012	94
11	B	01.01.2012	124
12	C	01.01.2012	122
13	D	01.01.2012	393
14	G	01.01.2012	13
15	E	01.03.2012	122
16	G	01.07.2012	393
17	F	01.08.2012	37
18	H	01.09.2012	72
19	G	01.11.2012	111
20

SUMMENPRODUKT und Pivot sehe ich hier weniger... :-)
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

AW: INDEX mit Matrixformel

04.01.2013 23:25:02

Peter

Hallo Erich
Die Formel liefert genau das Ergebnis, das ich suche.
Nur etwas hat mich irritiert. Wenn ich in Spalte J alle "G" Einträge durch einen anderen Eintrag ersetze, so dass es kein "G" mehr hat, wird mir regelmässig der erste Eintrag in Spalte L, also 19 berechnet. Dabei ist ja eigentlich die Bedingung ).. (mKZE=A1)... nicht gegeben, da in A1 "G" steht und dieser Buchstabe im Bereich mKZE nicht zu finden ist.
Eigentlich würde ich hier ein #NV erwarten. Oder wie siehst du das?
Vielen Dank für die Unterstützung - jemandem mit "gefährlichem Halbwissen" (vgl. Eintrag von Silex - nehme ich zumindest an, dass dies von ihm kommt ...)
Gruss, Peter

noch ein WENN

05.01.2013 00:32:14

Erich

Hi Peter,
die 19 kommt raus mit INDEX(L1:L20;0), wenn also keine Zeile die Bedingungen erfüllt.
Es kommt dabei natürlich nicht nur auf das Kennz. an. Für ein sinnvolles Ergebnis
muss es eine Zeile geben, für die das Kennzeichen=A1 und das Datum<=cANF ist.
So kannst du das wohl abfangen:

	A	B
1	G	#NV

Formeln der Tabelle

Zelle	Formel
B1	{=WENN(MAX((mKZE=A1)(mDat<=cANF))=0;NV(); INDEX(mSatz;MAX(ZEILE(1:20)(mKZE=A1)*(mDat<=cANF))))}

Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

Matrix verstehen

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

AW: noch ein WENN

05.01.2013 22:43:47

Peter

Hallo Erich
So ist die Formel perfekt, vielen Dank.
Schöner Sonntag und freundlicher Gruss, Peter

Beliebteste Forumthreads (12 Monate)

komplizierte Summewenns oder Sverweis ??

Forumthread: komplizierte Summewenns oder Sverweis ?

5 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread