Filter -> Teilergebnis -> Summenprodukt aus Teilergebnis

[sule1979]

Hallo zusammen,

ist stehe vor einer unlösbaren Aufgabe und vielleicht kann mir einer von euch weiterhelfen.
Suche über Google und Forum hat nichts passendes herausgespuckt.

Daten (Stundenabrechnung) werden über Filter (Daten -> Filter -> Autofilter) z. B. nach Monat x gefiltert.
Anzahl der gearbeiteten Tage (Frühschicht (F), Spätschicht (S) und Nachtschicht (N)) wird anhand der Formel "=TEILERGEBNIS(3;F14:F380)" zusammengezählt.
Soweit so gut.

Nun möchte ich auch dieser Zusammenfassung (Arbeitstage im Monat) weiter aufschlüsseln und zwar wie folgt:
Frühschicht: x Tage
Spätschicht: x Tage
Nachtschicht: x Tage

Folgende Formel versuche ich zu verwenden aber es gelingt mir einfach nicht.
"=SUMMENPRODUKT(TEILERGEBNIS(3;INDIREKT("F14:F380"))*(F14:F380="F"))" => falsches Ergebnis
"=SUMMENPRODUKT(TEILERGEBNIS(3;INDIREKT("F"&14&":F"&380))*(F14:F380="F"))" => falsches Ergebnis
"=SUMMENPRODUKT(TEILERGEBNIS(3;INDIREKT("F"&ZEILE(14:380)))*(F14:F380="F"))" => Err502

Ich weiß einfach nicht mehr weiter


Danke im Voraus
Gruß

Bsp:

Beispiel

bsp.jpg (123.56 KiB) 6911 mal betrachtet

[balu]

Hallo sule,

Ich weiß einfach nicht mehr weiter

Da wären wir dann schon mal zwei. Wobei bei mir das nix zu sagen hat, da ich ja auch nicht alles weiß.

"=SUMMENPRODUKT(TEILERGEBNIS(3;INDIREKT("F"&ZEILE(14:380)))*(F14:F380="F"))" => Err502

Dazu kann ich aber was sagen.
Der Fehler kommt daher, dass Du da eine Formel von Excel mit ihrer typischen Eigenschaft übernehmen willst, wass OOo jedoch sauer aufstößt. OOo mag z.B. bei ZEILE keine Zeilennummern, sondern ausschließlich exakte Zellbereichsangaben. Also müsste dort dann folgendes beispielsweise stehen.

Code: Alles auswählen

ZEILE(A14:A380)

Das hilft aber auch nicht viel weiter, da dann die Formel trotzdem nicht funktioniert, beziehungsweise ein "falsches" Ergebnis liefert.



Ich weiß nicht ob noch jemand anderer eine Wunschlösung für dich bieten kann/wird, ich könnt dir aber einen Holzhammer-Würkarount anbieten.

nach Monat x gefiltert

Wenn Du also in der gefilterten Spalte einen Monat stehen hast, dann könntest Du doch folgendes machen.

Nehm eine normale SUMMENPRODUKT für die Aufschlüsselung der Arbeitstage. Und damit auch nur der gefilterte Monat berücksichtigt wird, setzt Du wohl auch TEILERGEBNIS ein, aber mit einem ganz anderen Funktionsindex. Das könnte dann z.B. so aussehen

Code: Alles auswählen

=WENN(TEILERGEBNIS(4;B14:B380)=TEILERGEBNIS(5;B14:B380);SUMMENPRODUKT(F14:F380="F";B14:B380=TEILERGEBNIS(4;B14:B380));"---")

Die zwei TEILERGEBNIS am Anfang in der WENN überprüfen ob die Spalte B mit dem Monat gefiltert ist. Denn wenn sie nicht gefiltert ist, dann stehen ja dort mehrere Monatszahlen, 1,2...12. Und folglich ist das Teilergebnis für MAX(Funktionsindex=4) und MIN(Funktionsindex=5) unterschiedlich. Erst wenn beide das selbe Eregebnis liefern, wird die SUMMENPRODUKT ausgeführt.

In der SUMMENPRODUKT ist wiederum eine TEILERGEBNIS drin, die jetzt aber die Spalte B mit dem gefilterten Monat abgleicht. Eigentlich spielt es keine Rolle ob Du dort den Funktionsindex 4 oder 5 nimmst, da ja nur eine Zahl von TEILERGEBNIS zurückgegeben wird die SUMMENPRODUKT mit der Spalte B abgleichen kann. Wichtig ist nur das Du dich für eine von beiden, 4 oder 5, entscheidest.

Ich hoffe das Du mich verstanden hast, und das mein Vorschlag dir behilflich ist .


Gruß
balu

[sule1979]

Hallo balu,

tausend Dank für deine Hilfe !!!!!!

Deine Formel funktioniert einwandfrei.
Um zu meinem Ergebnis zu kommen, d. h. falls der Filter auf "alle" gesetzt ist, wurde die Formel bei "Sonst" um Zählenwenn erweitert.

=WENN(TEILERGEBNIS(4;B14:B380)=TEILERGEBNIS(5;B14:B380);SUMMENPRODUKT(F14:F380="F";B14:B380=TEILERGEBNIS(4;B14:B380));ZÄHLENWENN(F14:F380;"F"))

Nun werde ich noch versuchen die Formel um den Filter "Jahr" zu erweitern. Das kann ja noch lustig werden

Nochmals danke

Gruß

[sule1979]

Hallo balu,

ist es überhaupt möglich die Formel mit UND zu verknüpfen, damit man Monat abhängig vom Jahr berechnen kann, oder ist mein Denkansatz hier flasch?

Bsp.:
=WENN(UND(TEILERGEBNIS(4;B11:B380)=TEILERGEBNIS(5;B11:B380);TEILERGEBNIS(4;A11:A380)=TEILERGEBNIS(5;A11:A380));
SUMMENPRODUKT(F11:F380="F";UND(B11:B380=TEILERGEBNIS(4;B11:B380);A11:A380=TEILERGEBNIS(4;A11:A380)));ZÄHLENWENN(F11:F380;"F"))

Gruß

[balu]

Hallo sule,

mit der Kombination von Autofilter + TEILERGEBNIS + SUMMENPRODUKT hatte ich bis einschließlich gestern noch nie was zu tun gehabt. Und von daher kann ich zu deiner Frage:

ist es überhaupt möglich die Formel mit UND zu verknüpfen

erstmal noch nichts verlässliches sagen. Ich bin in dieser hinsicht genau so Schlau wie Du.

damit man Monat abhängig vom Jahr berechnen kann

Gehts dir jetzt lediglich um das berechnen, oder um das filtern + berechnen? Das ist keine Haarspalterei, sondern zwei verschiedene Wege um etwas per Formel zu berechnen.
Und ferner, wo willst Du das Ergebnis sehen, im gleichem Blatt wie dein Screenshot, oder wo anders?



Gruß
balu

[sule1979]

Also, mir geht es um filtern und berechnen.

In der Spalte A steht das Jahr (2008, 2009, 2010, 2011, usw.) welches aus dem Datum herausgelöst wird und in der Spalte B die Monate.

Das Filtern nach Monat und die Berechnung wie weiter oben beschrieben funktioniert einwandfrei.
Nun habe ich das Problem, dass alle Einträge aus dem gewählten Monat zusammengerechnet werden unabhängig davon zu welchen Jahr diese Einträge gehören.

Ich möchte jetzt einen Filter auf das Jahr und einen auf den Monat anwenden und die darin enthaltenen Einträge mit dem Kriterium "F" berechnen.

[balu]

Hallo sule,

ich weiß nicht ob ich es jetzt richtig verstanden habe (bei dem trüben Wetter macht Denken kein Spaß), aber ich versuchs mal. Vielleicht klappts ja.

Um die Formeln etwas übersichtlicher zu halten, würde ich zwei Hilfszellen nehmen. Beispielsweise neben deiner =TEILERGEBNIS(3;F14:F380). Angenommen die steht in G370, dann wären die Hilfzellen H370 und I370.
In H370 für das Jahr:

Code: Alles auswählen

=WENN(TEILERGEBNIS(4;A11:A380)=TEILERGEBNIS(5;A11:A380);1;"")

In I370für den Monat:

Code: Alles auswählen

=WENN(TEILERGEBNIS(4;B11:B380)=TEILERGEBNIS(5;B11:B380);1;"")

Durch die beiden zusätzlichen Formeln wird nun seperat überprüft ob gefiltert wurde.
Un nun wird folgende Formel eingesetzt.

Code: Alles auswählen

=WENN((H370=1)*(I370=1);SUMMENPRODUKT(F11:F380="F";B11:B380=TEILERGEBNIS(4;B11:B380));ZÄHLENWENN(F11:F380;"F"))

Ist es das was Du haben wolltest, und funktioniert es bei dir?



Gruß
balu

[balu]

Korrektur!
Ich glaub jetzt müsste es passen. Hoffentlich.

Code: Alles auswählen

=WENN((H370=1)*(I370=1);SUMMENPRODUKT(F11:F380="F";B11:B380=TEILERGEBNIS(4;B11:B380);A11:A380=TEILERGEBNIS(4;A11:A380));ZÄHLENWENN(F11:F380;"F"))

Gruß
balu