Verweisfunktion mit mehreren Suchkriterien

[ovdiyyel]

Hallo zusammen,

bitte um Hilfe bei folgendem Beispiel (die Excel-datei ist anbei)

Ich habe eine Matrix mit Spalten:

A-Kunde, B-bestellter Artikel, C-Einzepreis, D-Anzahl der bestellungen im Januar, E-Anzahl der bestellungen im Februar, F-Anzahl der bestellungen im März... usw.


Welche Formel soll ich nutzen, wenn ich wissen will, was und wie viel der Kunde Müller GmbH (1. Verweis) im Monat Januar (2. Verweis) bestellt hat?

Es sollen alle Aktikel und die Anzahl angezeigt werden, die der Kunde bestellt hat, bezogen auf den Kundenname und den gewählten Zeitraum.

Vielen Danke für die Untestützung.  

[steve1da]

Hola,

damit niemand unnötig arbeiten muss..

http://ms-office-forum.net/forum/showthr...p?t=323480

Gruß,
steve1da

[WillWissen]

Hi,

benutze doch eine Pivottabelle. Da brauchst du gar keine Formeln.

   

[OBI-Fan]

Hallo,

in Deine Excel-Tabelle habe ich drei Funktionen eingetragen, die Dir die Stückzahlen pro Artikel und Kunde anzeigen, den Umsatz pro Kunde und Artikel sowie den Gesamtumsatz pro Kunde. Ich hoffe Du kannst damit etwas anfangen. Wenn Du mehr zu dem Thema "Matrix-Operationen" benötigst, schau mal hier:

 http://www.online-excel.de/excel/singsel.php?f=26

ist aus meiner Sicht sehr verständlich aufgebaut und bei Station 5 erkennst Du die Formeln wieder, die ich in Deiner Tabelle verwendet habe.

Viele Grüße
Franjo

[OBI-Fan]

  Copy of Mappe2.xlsx (Größe: 12,52 KB / Downloads: 5) Hallo,

jetzt mit Anhang

in Deine Excel-Tabelle habe ich drei Funktionen eingetragen, die Dir die Stückzahlen pro Artikel und Kunde anzeigen, den Umsatz pro Kunde und Artikel sowie den Gesamtumsatz pro Kunde. Ich hoffe Du kannst damit etwas anfangen. Die {} werden nicht mit eingegeben sondern werden durch Drücken der Tastenkombination Strg - Shift - Enter erzeugt; durch Betätigen nur von Enter erhältst Du kein Ergebnis!!! Wenn Du mehr zu dem Thema "Matrix-Operationen" benötigst, schau mal hier:

 http://www.online-excel.de/excel/singsel.php?f=26

ist aus meiner Sicht sehr verständlich aufgebaut und bei Station 5 erkennst Du die Formeln wieder, die ich in Deiner Tabelle verwendet habe.

Viele Grüße
Franjo

[WillWissen]

Hi Franjo,

deine Berechnung stimmt aber nicht (Ich selbst habe aber auch noch keine Lösung).
Hier mal ein Tab.-Ausschn. nur für Obst und Gemüse im April.

Tabelle4

	A	B	C	D	E	F
12	Kunde	Artikel	Zeitraum	Anzahl der bestellten Artikel
13	Müller GmbH	Obst	April 15	1
14		Gemüse		0

Formeln der Tabelle

Zelle Formel D13 {=SUMME(($A$3:$A$9=$A$13)*($B$3:$B$9=B13)*($D$3:$D$9))} D14 {=SUMME(($A$3:$A$9=$A$13)*($B$3:$B$9=B14)*($D$3:$D$9))}

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

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Das bekommst du heraus und das

Tabelle4

	A	B	C	G
2	Kunde	bestellte Artikel	Einzelpreis	Apr 15
3	Müller GmbH	Obst	10	6
4	Meyer GmbH	Gemüse	12	8

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

ist die Bestellliste.

Übrigens, auch die Formeln in Spalte J würde ich einfacher gestalten.

Code:

=SUMMENPRODUKT(D3:I3)*C3

Diese Formel in J3 und runterziehen.

[schauan]

Hallöchen,

den variablen Monat bekommt man so:

Arbeitsblatt mit dem Namen 'Tabelle4'
	A	B	C	D	E	F
12	Kunde	Artikel	Zeitraum	Anzahl der bestellten Artikel
13	Müller GmbH	Obst	April 15	6

Zelle	Formel
D13	{=SUMME(($A$3:$A$9=$A$13)*($B$3:$B$9=B13)*(INDEX(A3:I9;;VERGLEICH(C13;A2:I2;0))))}

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

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

[BoskoBiati]

Hallo,

die Summenformel in J ist undurchdacht. Hier mal zwei Varianten (einmal in D13, dann in den Zeilen darunter):

Arbeitsblatt mit dem Namen 'Tabelle4'
	A	B	C	D	E	F	G	H	I	J
2	Kunde	bestellte Artikel	Einzelpreis	Jan 15	Feb 15	Mrz 15	Apr 15	Mai 15	Jun 15	Gesamt
3	Müller GmbH	Obst	10	1	2	4	6	2	3	180
4	Meyer GmbH	Gemüse	12	5	3	6	8	11	3	432
5	Schmidt AG	Kräuter	5	4	2	4	6	2	3	105
6	Müller GmbH	Getränke	2,5	7	3	5	8	10	3	90
7	Meyer GmbH	Obst	10	2	1	1	1	1	1	70
8	Schmidt AG	Gemüse	12	1	3	4	4	4	5	252
9	Müller GmbH	Kräuter	5	9	6	2	2	3	1	115
10
11
12	Kunde	Artikel	Zeitraum	Anzahl der bestellten Artikel	Preis
13	Müller GmbH	Obst	Februar 15	2
14		Obst		2	20
15		Getränke		3	7,5
16		Kräuter		6	30

Zelle	Formel
J3	=SUMME(D3:I3)*C3
D13	=SUMMENPRODUKT((A3:A9=A13)*(B3:B9=B13)*(D2:I2=C13)*D3:I9)
B14	{=WENN(ZEILE(A1)>ZÄHLENWENN($A$3:$A$9;$A$13);"";INDEX(B:B;KKLEINSTE(WENN($A$3:$A$9=$A$13;ZEILE($A$3:$A$9));ZEILE(A1))))}
D14	{=WENN(ZEILE(C1)>ZÄHLENWENN($A$3:$A$9;$A$13);"";INDEX($D:$I;KKLEINSTE(WENN($A$3:$A$9=$A$13;ZEILE($A$3:$A$9));ZEILE(C1));VERGLEICH($C$13;$D$2:$I$2)))}
E14	{=WENN(ZEILE(D1)>ZÄHLENWENN($A$3:$A$9;$A$13);"";INDEX($D:$I;KKLEINSTE(WENN($A$3:$A$9=$A$13;ZEILE($A$3:$A$9));ZEILE(D1));VERGLEICH($C$13;$D$2:$I$2))*INDEX($C:$C;KKLEINSTE(WENN($A$3:$A$9=$A$13;ZEILE($A$3:$A$9));ZEILE(D1))))}

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

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

[WillWissen]

Hi Edgar,

die Summenformel in J ist undurchdacht.

Zelle Formel
J3 =SUMME(D3:I3)*C3

guckst du:

#6
Übrigens, auch die Formeln in Spalte J würde ich einfacher gestalten.



Code:

Code:

=SUMMENPRODUKT(D3:I3)*C3

Diese Formel in J3 und runterziehen.

[BoskoBiati]

Hallo Günter,

Summenprodukt ist aber auch etwas übertrieben :19: :39: :15: