PDA

Vollständige Version anzeigen : Summenprodukt - Formel verkürzen


Torsten1
05.04.2012, 03:58
Hi

Ich benötige eine Formel.
Ich habe einen Arbeitsplan. Sprich eine Arbeitsmappe in der die
Tabellenblätter von "Jan" (für Januar) bis "Dez" (für Dezember) gekennzeichnet sind.

Was möchte ich ?
Es sollen über alle 12 Arbeitsblätter bestimmte Zellen mit best. Bedingungen gezählt werden.

Bsp. Mitarbeiter Müller hat im Monat Januar 2 x am Montag Frei und einmal an
einem Montag Urlaub gehabt. Es soll also nur Montag mit Frei u. Urlaub gezählt werden.
Wenn das nur der Januar wäre, sollte somit das Ergebnis =3 sein.

Ich habe es bisher so gelöst:
=SUMMENPRODUKT((Jan!$C$3:$AG$3=C$16)*((Jan!$C5:$AG 5="F")+(Jan!$C5:$AG5="U")))
Das "C$16" kennzeichnet eine Zelle mit der Bez. "Mo". Hat nur etwas mit dem
Kopieren auf den Di, Mi, Do, Fr zutun.

Nun könnte ich den "Februar, März usw. mit einem "+" vorangestellt weiterführen,
was auch funktioniert:
=SUMMENPRODUKT((Jan!$C$3:$AG$3=C$16)*((Jan!$C5:$AG 5="F")+(Jan!$C5:$AG5="U")))+SUMMENPRODUKT((Feb!$C$ 3:$AG$3=C$16)*((Feb!$C5:$AG5="F")+(Feb!$C5:$AG5="U ")))

Das wird natürlich eine lange Formel - eigentlich kein Problem.
Ich habe aber gesehen (irgendwo i. I-Net), dass es auch kürzer geht. z.B. mit:
=SUMMENPRODUKT(ZÄHLENWENN(INDIREKT("'"&{"Jan"."Feb "."Mär"."Apr"}&"'!C3:ag3");"Mo"))

Nur ich bekomme es ums Verrecken nicht hin, meine (funktionierende) Formel (die Lange),
mit der "INDIREKT"-Funktion zu kombinieren sodass auch alle Bedingungen erfüllt sind.
Das "Zählenwenn" ist vermutl. auch schon falsch.
Es geht nur darum, das in den geschweiften Klammern "nutzen" zu können.
Also "meine" lange Formel zu verkürzen.

Kann jemand helfen ? (und ... hat überhaupt jemand was von dem verstanden, was ich erreichen will ?

Nette Grüße
Torsten

Hasso
05.04.2012, 11:05
Hallo Torsten,

klingt interessant. Wäre auch eine VBA-Lösung denkbar?

Allerdings würde das Hochladen einer Beispieldatei (ohne sensible Daten) dem Helfer die Arbeit wesentlich erleichtern.

Torsten1
05.04.2012, 11:30
Danke für Deine Antwort.

Leider kenne ich mich mit VBA üüüüberhaupt nicht aus.
Daher wäre eine Formellösung schon schön.

Die Formel, welche ich im I-Net gefunden habe (und mich "inspirierte) lautet:

=SUMMENPRODUKT((T(INDIREKT("'"&{"JensF"."WF"."Boris"}&"'!A"&ZEILE(1:100)))="x")*(T(INDIREKT("'"&{"JensF"."WF"."Boris"}&"'!B"&ZEILE(1:100)))="y")*(N(INDIREKT("'"&{"JensF"."WF"."Boris"}&"'!C"&ZEILE(1:100)))))

Quelle: http://www.excelformeln.de/formeln.html?welcher=342
Dort steht auch eine Beschreibung dazu.

Nur ich bekomme es einfach nicht auf meine Bedingungen angepasst.

Leider enthält die angegeben Datei eine fülle von sensiblen Daten
(es wird z.B. gleich der Lohn ausgerechnet, Rest Urlaub, Überstd. usw.).
Von daher ist es etwas schwierig auch nur einen Teil hochzuladen.

Nette Grüße
Torsten

mücke
05.04.2012, 12:58
Moin Torsten,

über 40 Hit´s und noch kein Lösungsansatz, da wirst Du um den Hinweis von Hasso Hochladen einer Beispieldatei (ohne sensible Daten) wohl nicht drum rum kommen ;)

Viel Erfolg ... schöne Ostern :)

Torsten1
06.04.2012, 09:15
Na super !
Jetzt habe ich die Mappe entsprechend modifiziert und kann sie nicht hochladen,
da ich bis jetzt noch keine e-Mail-Bestätigung meiner Anmeldung bekommen habe (nein - auch nicht im Spam-Ordner).

O.K. ich belasse es bei meiner langen Formel.

Eine Lösung bzw. Verkürzung der Formel wäre zwar schön gewesen (auch in Bezug auf andere Berechnungen),
aber diese Lösung beschäftigt mich nun doch zu lange.

Nette Grüße
Torsten

Hajo_Zi
06.04.2012, 09:29
Hallo Torsten,

Mailbenachrichtigung schaue hier
http://www.ms-office-forum.net/forum/showpost.php?p=819829&postcount=5

<a href="http://Hajo-Excel.de/index.htm" target="_blank" title="Hajo's Excelseiten">Gruß Hajo</a>

Torsten1
06.04.2012, 10:58
Hallo Torsten,

Mailbenachrichtigung schaue hier
http://www.ms-office-forum.net/forum/showpost.php?p=819829&postcount=5

<a href="http://Hajo-Excel.de/index.htm" target="_blank" title="Hajo's Excelseiten">Gruß Hajo</a>

Jo - vielen Dank für den Tipp ! So hat's geklappt und anbei auch gleich die Datei.
Sieht zwar ziemlich zerstückelt aus, aber ich denke man kann verstehen,
was gemeint ist.
Die "F" und "U" mit (hier) "Mo" aus Jan - Dez (hier nur Jan u. Feb) sollen in der Jahresübersich aufsummiert werden.
Funktioniert ja auch - nur die Formel ist zu lang.

Das Makro, welches enthalten ist, ist nicht von mir und dient nur der
Feiertagsberechnung und dem autom. Wechsel der Anzeige von Samstag u. Sonntag.
Wechselt man den Monat, so passt sich die Anzeige (blauer Balken) entsprechend an.

So - nun harre ich der Dinge, die da kommen mögen ...:grins:

Nette Grüße
Torsten

Torsten1
07.04.2012, 09:15
129 Hits - und noch keiner 'ne Idee ?

Hasso
08.04.2012, 13:18
Hallo Torsten,

129 Hits - und noch keiner 'ne Idee ?

Kann ja sein, dass man über Ostern auch was anderes vorhat als Excel-Probleme zu lösen :D .

Ich habe mir dein Problem aber trotzdem weiter zu Gemüte geführt und glaube, mit einer benutzerdefinierten Funktion eine Lösung gefunden zu haben:
Option Explicit

Public Function freieTage(zeile As Long, wochentag As String)
Dim monatsblatt As Worksheet
Dim zelle As Range
Dim monate As String

monate = ("JanFebMrzAprMaiJunJulAugSepOktNovDez") 'Die Namen der Monatsblätter
For Each monatsblatt In Worksheets
If InStr(monate, monatsblatt.Name) Then
With Worksheets(monatsblatt.Name)
For Each zelle In .Range(.Cells(zeile + 2, 3), .Cells(zeile + 2, 34))
If (zelle = "U" Or zelle = "F") And .Cells(3, zelle.Column) = wochentag Then
freieTage = freieTage + 1
End If
Next
End With
End If
Next
End Function
Diese Funktion im Blatt "Jahresübersicht" verwenden.

Das Ganze muss eventuell noch an deine Bedürfnisse angepasst werden.

Beispielmappe anbei.