Microsoft Excel

Herbers Excel/VBA-Archiv

Flexiblere Formel gesucht | Herbers Excel-Forum


Betrifft: Flexiblere Formel gesucht von: Sibylle Weber
Geschrieben am: 25.01.2012 20:00:51

Guten Abend,

ich suche für unten angezeigte Datei flexiblere Formeln.
Kann man die absoluten Bezüge "loswerden" und durch relative ersetzen?
Pro Monat sollte nur ein Ergebnis angezeigt werden.
Vielleicht gibt es weitere Verbesserungen.
Einen schönen Abend.

Gruß
Sibylle

Tabelle1

 ABCDEFG
1DatumWerte  MonateSummenAnzahlDurchschnitt
201.12.201150,00 €  01.10.201100 
303.12.201160,00 €  01.11.201100 
407.12.201165,00 €  01.12.2011175358,3333333
501.01.201270,00 €  01.01.2012142271
605.01.201272,00 €  01.02.201280180
701.02.201280,00 €  01.03.201200 
803.05.201288,00 €  01.04.201200 
907.05.201296,00 €  01.05.2012184292
1006.06.2012104,00 €  01.06.20121041104
1101.07.2012112,00 €  01.07.20123603120
1202.07.2012120,00 €  usw.     
1308.07.2012128,00 €         
14usw.usw.         

verwendete Formeln
Zelle Formel Bereich
E2 =SUMMENPRODUKT((JAHR($A$2:$A$13)=JAHR(D2))*(MONAT($A$2:$A$13)=MONAT(D2));$B$2:$B$13) 
F2 {=SUMMENPRODUKT((JAHR($A$2:$A$13)=JAHR(D2))*(MONAT($A$2:$A$13)=MONAT(D2))*1)} $F$2
G2 =WENN(E2+F2=0;"";E2/F2) 
E3 =SUMMENPRODUKT((JAHR($A$2:$A$13)=JAHR(D3))*(MONAT($A$2:$A$13)=MONAT(D3));$B$2:$B$13) 
F3 {=SUMMENPRODUKT((JAHR($A$2:$A$13)=JAHR(D3))*(MONAT($A$2:$A$13)=MONAT(D3))*1)} $F$3
G3 =WENN(E3+F3=0;"";E3/F3) 
E4 =SUMMENPRODUKT((JAHR($A$2:$A$13)=JAHR(D4))*(MONAT($A$2:$A$13)=MONAT(D4));$B$2:$B$13) 
F4 {=SUMMENPRODUKT((JAHR($A$2:$A$13)=JAHR(D4))*(MONAT($A$2:$A$13)=MONAT(D4))*1)} $F$4
G4 =WENN(E4+F4=0;"";E4/F4) 
E5 =SUMMENPRODUKT((JAHR($A$2:$A$13)=JAHR(D5))*(MONAT($A$2:$A$13)=MONAT(D5));$B$2:$B$13) 
F5 {=SUMMENPRODUKT((JAHR($A$2:$A$13)=JAHR(D5))*(MONAT($A$2:$A$13)=MONAT(D5))*1)} $F$5
G5 =WENN(E5+F5=0;"";E5/F5) 
E6 =SUMMENPRODUKT((JAHR($A$2:$A$13)=JAHR(D6))*(MONAT($A$2:$A$13)=MONAT(D6));$B$2:$B$13) 
F6 {=SUMMENPRODUKT((JAHR($A$2:$A$13)=JAHR(D6))*(MONAT($A$2:$A$13)=MONAT(D6))*1)} $F$6
G6 =WENN(E6+F6=0;"";E6/F6) 
E7 =SUMMENPRODUKT((JAHR($A$2:$A$13)=JAHR(D7))*(MONAT($A$2:$A$13)=MONAT(D7));$B$2:$B$13) 
F7 {=SUMMENPRODUKT((JAHR($A$2:$A$13)=JAHR(D7))*(MONAT($A$2:$A$13)=MONAT(D7))*1)} $F$7
G7 =WENN(E7+F7=0;"";E7/F7) 
E8 =SUMMENPRODUKT((JAHR($A$2:$A$13)=JAHR(D8))*(MONAT($A$2:$A$13)=MONAT(D8));$B$2:$B$13) 
F8 {=SUMMENPRODUKT((JAHR($A$2:$A$13)=JAHR(D8))*(MONAT($A$2:$A$13)=MONAT(D8))*1)} $F$8
G8 =WENN(E8+F8=0;"";E8/F8) 
E9 =SUMMENPRODUKT((JAHR($A$2:$A$13)=JAHR(D9))*(MONAT($A$2:$A$13)=MONAT(D9));$B$2:$B$13) 
F9 {=SUMMENPRODUKT((JAHR($A$2:$A$13)=JAHR(D9))*(MONAT($A$2:$A$13)=MONAT(D9))*1)} $F$9
G9 =WENN(E9+F9=0;"";E9/F9) 
E10 =SUMMENPRODUKT((JAHR($A$2:$A$13)=JAHR(D10))*(MONAT($A$2:$A$13)=MONAT(D10));$B$2:$B$13) 
F10 {=SUMMENPRODUKT((JAHR($A$2:$A$13)=JAHR(D10))*(MONAT($A$2:$A$13)=MONAT(D10))*1)} $F$10
G10 =WENN(E10+F10=0;"";E10/F10) 
E11 =SUMMENPRODUKT((JAHR($A$2:$A$13)=JAHR(D11))*(MONAT($A$2:$A$13)=MONAT(D11));$B$2:$B$13) 
F11 {=SUMMENPRODUKT((JAHR($A$2:$A$13)=JAHR(D11))*(MONAT($A$2:$A$13)=MONAT(D11))*1)} $F$11
G11 =WENN(E11+F11=0;"";E11/F11) 
{} Matrixformel mit Strg+Umschalt+Enter abschließen
Matrixformeln sind durch geschweifte Klammern {} eingeschlossen
Diese Klammern nicht eingeben!!


Tabellendarstellung in Foren Version 5.46


  

Betrifft: AW: Flexiblere Formel gesucht von: CitizenX
Geschrieben am: 25.01.2012 21:11:33

Hi,

eine Möglichkeit:

Arbeitsblatt mit dem Namen 'Tabelle1'
 ABCDEFGH
1DatumWerte MonateSummenAnzahlDurchschnittletze Zeile
201.12.201150,00 € Okt.201100 11
303.12.201160,00 € Nov.201100  
407.12.201165,00 € Dez.2011175358 
501.01.201270,00 € Jan.2012142271 
605.01.201272,00 € Feb.201280180 
701.02.201280,00 € Mrz.201200  
803.05.201288,00 € Apr.201200  
907.05.201296,00 € Mai.2012184292 
1006.06.2012104,00 € Jun.20121041104 
1101.07.2012112,00 € Jul.20121121112 

ZelleFormel
E2=SUMMENPRODUKT((($A$2:INDEX(A:A;$H$2)-TAG($A$2:INDEX(A:A;$H$2))=D2-TAG(D2))*$B$2:INDEX(B:B;$H$2)))
F2=SUMMENPRODUKT((($A$2:INDEX(A:A;$H$2)-TAG($A$2:INDEX(A:A;$H$2))=D2-TAG(D2))*1))
G2=WENN(E2+F2;E2/F2;"")
H2{=MAX((A:A<>"")*ZEILE(A:A))}
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.0) erstellt. ©Gerd alias Bamberg


Wobei man die letzte ermittelte Zeile auch als Namen vergeben könnte ,doch dann geht die Performance in die Knie,so kannst du sie ggf ausblenden ...

Grüße
Steffen


  

Betrifft: AW: Formelteil unklar von: Sibylle Weber
Geschrieben am: 26.01.2012 14:24:57

Hallo Steffen,

vielen Dank für Deine Formel. Auffallend wie dabei die Auswertung nach unten begrenzt wird.

Was ich nicht verstehe ist folgender Formelteil:
=SUMMENPRODUKT((($A$2:INDEX(A:A;$H$2)-TAG($A$2:INDEX(A:A;$H$2))=D2-TAG(D2)) ...

Weshalb werden da - Tag(... abgezogen?

Kannst Du mir bitte den Sich erklären?

Gruß
Sibylle


  

Betrifft: AW: Flexiblere Formel gesucht von: Erich G.
Geschrieben am: 25.01.2012 21:15:25

Hi Sibylle,
ein wenig lässt sich da schon vereinfachen. Auch in Spalte F braucht die Formel nicht als Matrix eingegeben zu werden.
Das kann SUMMENPRODUKT - wie ja schon in Spalte E - auch so.

Das "*1" ist - außer unten in F5 - überflüssig, da die Wahrheitswerte ja schon mit etwas multipliziert
und dabei in Zahlen umgewandelt werden.

Schau dir diese Formeln mal an - sie werden nach unten immer einfacher:

 ABCDEF
203.03.20110,01 01.01.201100
307.04.20115 01.02.20111111
405.02.2011111 01.03.20110,011
512.04.20113 01.04.201182

Formeln der Tabelle
ZelleFormel
E2=SUMMENPRODUKT((JAHR($A$2:$A$13)=JAHR(D2))*(MONAT($A$2:$A$13)=MONAT(D2)); $B$2:$B$13)
F2{=SUMMENPRODUKT((JAHR($A$2:$A$13)=JAHR(D2))*(MONAT($A$2:$A$13)=MONAT(D2))*1)}
E3=SUMMENPRODUKT((JAHR($A$2:$A$13)=JAHR(D3))*(MONAT($A$2:$A$13)=MONAT(D3))*$B$2:$B$13)
F3=SUMMENPRODUKT((JAHR($A$2:$A$13)=JAHR(D3))*(MONAT($A$2:$A$13)=MONAT(D3))*1)
E4=SUMMENPRODUKT((TEXT($A$2:$A$13;"JMM")=TEXT(D4;"JMM"))*$B$2:$B$13)
F4=SUMMENPRODUKT((JAHR($A$2:$A$13)=JAHR(D4))*(MONAT($A$2:$A$13)=MONAT(D4)))
E5=SUMMENPRODUKT((TEXT($A$2:$A$13;"JMM")=TEXT(D5;"JMM"))*$B$2:$B$13)
F5=SUMMENPRODUKT((TEXT($A$2:$A$13;"JMM")=TEXT(D5;"JMM"))*1)
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Nicht verstanden habe ich deinen Wunsch, von absoluten Bezügen auf relative überzugehen.
Über welche Bereiche sollen denn dann die Summen gebildet werden? Ich sehe da keine Möglichkeit für relative Bezüge.

"Pro Monat sollte nur ein Ergebnis angezeigt werden." habe ich auch nicht verstanden.
Oder meintest du hier nur das Selbstverständliche?

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


  

Betrifft: AW:Danke Zusatzfrage von: Sibylle Weber
Geschrieben am: 26.01.2012 14:30:20

Hallo Erich,

vielen Dank für Deine Formeln.
Wenn man auf die Summenproduktfunktion verzichtet, statt dessen Summe(wenn als Matrixformel verwendet, kann man dann in Excel 2010 die Spalte in der Weise A:A ansprechen?

Gruß
Sibylle


  

Betrifft: Ja aber... von: Erich G.
Geschrieben am: 26.01.2012 16:56:49

Hi Sibylle,
da frage ich mich doch zu allererst: Hast du denn das nicht einfach mal ausprobiert?
Das geht doch einfacher als hier eine Frage zu stellen...

Ja, sicher. Aber zum einen musst du deshalb nicht von SUMMENPRODUKT zu SUMME wechseln (SUMMENPRODUKT kanns auch),
zum anderen solltest du bedenken, dass es in Spalte A die nicht geringe Anzahl von 1048576 Zeilen gibt.
Ich kann dir nur dann dazu raten, A:A zu schreiben, wenn's wirklich (nicht nur etwas) länger dauern soll.

Aber wie gesagt: Du kannst das ja einfach mal ausprobieren.

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


  

Betrifft: AW: OK, Danke von: Sibylle Weber
Geschrieben am: 26.01.2012 17:11:23

Hallo Erich,

ich werde testen und darüber nachdenken.
Vielen Dank für Deine Denkhilfe.

Gruß
Sibylle


Beiträge aus den Excel-Beispielen zum Thema "Flexiblere Formel gesucht"