Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1244to1248
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Flexiblere Formel gesucht

Flexiblere Formel gesucht
Sibylle
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


6
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
AW: Flexiblere Formel gesucht
25.01.2012 21:11:33
CitizenX
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
Anzeige
AW: Formelteil unklar
26.01.2012 14:24:57
Sibylle
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
AW: Flexiblere Formel gesucht
25.01.2012 21:15:25
Erich
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
Anzeige
AW:Danke Zusatzfrage
26.01.2012 14:30:20
Sibylle
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
Ja aber...
26.01.2012 16:56:49
Erich
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
Anzeige
AW: OK, Danke
26.01.2012 17:11:23
Sibylle
Hallo Erich,
ich werde testen und darüber nachdenken.
Vielen Dank für Deine Denkhilfe.
Gruß
Sibylle

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige