Microsoft Excel

Herbers Excel/VBA-Archiv

Summenprodukt, aber wesentlich schwieriger | Herbers Excel-Forum


Betrifft: Summenprodukt, aber wesentlich schwieriger von: Markus
Geschrieben am: 06.09.2008 18:11:00

Hallo zusammen,

ich stehe vor einem großen Problem. Ich habe eine Tabelle, die wie folgt aufgebaut ist.

In Spalte B (ab Zeile 8) befinden sich Datumsangaben. In Spalte F (ab Zeile 8) befinden sich die dazugehörigen Ausgaben. Ich möchte nun in Zelle B3 immer die Summe bilden, die die letzte belegte Zelle in Spalte F hat, bezogen aber nur auf das bestimmte Datum in Zeile B.

01.09.2008 = 35,00
03.09.2008 = 12,00
05.09.2009 = 20,00
05.09.2008 = 15,00 = in Zelle B3 also 35,00 (es soll ja nur der 05.09.2008 gezählt werden)

Es wird aber noch komplizierter. Wird nun ein weiterer Wert eingegeben, beispielsweise mit Datum
03.09.2008 = 10,00 so soll nicht 22,00 rauskommen, sondern nur die 10,00.

Es soll also immer die Summe der Ausgaben bezogen auf das Datum der letzten belegten Zelle ermittelt werden.

Kann mir hier jemand helfen?

Viele Grüße
Markus

  

Betrifft: AW: Summenprodukt, aber wesentlich schwieriger von: WalterK
Geschrieben am: 06.09.2008 18:41:54

Hallo Markus,

so vielleicht:
Tabelle1

 BCDEF
322    
4     
5     
6     
7     
801.09.2008   35
902.09.2008   12
1005.09.2008   20
1105.09.2008   15
1202.09.2008   10

Formeln der Tabelle
ZelleFormel
B3{=SUMMENPRODUKT((B8:B1000=INDIREKT("B"&MAX((B8:B1000<>"")*ZEILE(8:1000))))*F8:F1000)}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

siehe auch hier:
http://www.excelformeln.de/formeln.html?welcher=48

Servus, Walter


  

Betrifft: AW: Summenprodukt, aber wesentlich schwieriger von: Beate Schmitz
Geschrieben am: 06.09.2008 23:24:45

Hallo Markus,

hier eine Lösung mit Hilfsspalte D, die Formel aus D8 kann runterkopiert werden:

 ABCDEF
1      
2      
3      
4 Summe des letzten Datums am Stück nach oben:34
5      
6      
7 DatumWertHilfsspalte  
8 01.09.200835FALSCH  
9 03.09.200812FALSCH  
10 05.09.200820FALSCH  
11 05.09.200815FALSCH  
12 01.09.200835FALSCH  
13 03.09.200812FALSCH  
14 05.09.200820WAHR  
15 05.09.200814WAHR  

Formeln der Tabelle
ZelleFormel
F4=SUMMEWENN(D$8:D1000;WAHR;C$8:C1000)
D8=ZÄHLENWENN(B8:INDIREKT("B"&VERGLEICH(0;B:B;-1)); B8)=ANZAHL(B8:INDIREKT("B"&VERGLEICH(0;B:B;-1)))


Gruß,
Beate


  

Betrifft: mit SUMMENPRODUKT() kombiniert mit VERWEIS() ... von: neopa
Geschrieben am: 07.09.2008 07:59:26

Guten Morgen Markus, Beate und Walter,

... ist die Aufgabenstellung auch ohne Hilfspalte lösbar.

Walters Formel (SUMMENPRODUKT() bedarf keiner {}-Klammerung) wäre kürzer so lösbar: =SUMMENPRODUKT((B8:B19=VERWEIS(9^9;B8:B19))*F8:F19), doch das entspricht noch nicht Markus Aufgabenstellung. Die hat Beate mit Hilfsspalte mE richtig umgesetzt. Angeregt dadurch habe ich nun eine Lösung ohne Hilfsspalte anzubieten:

 ABCDEFG
3spez. Summe:39     
4       
5       
6       
7 Datum   Wert 
8 01.09.2008   35 
9 03.09.2008   12 
10 05.09.2008   20 
11 05.09.2008   15 
12 03.09.2008   35 
13 03.09.2008   12 
14 05.09.2008   20 
15 05.09.2008   14 
16 05.09.2008   5 
17       

Formeln der Tabelle
ZelleFormel
B3=SUMMENPRODUKT((B8:B99=VERWEIS(9^9;B8:B99))*(ZEILE(B8:B99)>VERWEIS(9;1/((B8:B99<>VERWEIS(9^9;B8:B99))*(B8:B99<>"")); ZEILE(8:99)))*F8:F99)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Gruß Werner
.. , - ...


  

Betrifft: AW: mit SUMMENPRODUKT() kombiniert mit VERWEIS() ... von: Markus
Geschrieben am: 07.09.2008 19:32:18

Hallo Ihr drei,

danke für Eure Mühe. Ich finde das echt klasse hier.

Die Lösung von neopa ist die beste. Ich hatte mir parallel zum Einbau von Walters Formel auch etwas per VBA gestrickt, allerdings benötigte ich dafür zwei Hilfszellen zum zwischenparken (um die Werte später zu subtrahieren). Die Lösung per Formel ist natürlich viel einfacher (naja wenn man weiß wie es geht) und unkomplizierter. Habe ich auch schon eingebaut und es klappt prima.

Also besten Dank an Euch drei und einen schönen Abend noch.

Viele Grüße
Markus