Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1404to1408
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

Summenprodukt mit dynamischem Bereich

Summenprodukt mit dynamischem Bereich
22.01.2015 08:56:23
Elias
Guten Morgen,
ich sitze seit gestern an einer Excel-Formel und brauche mal Eure kreativen Köpfe, um weiterzukommen.
Ausgangslage:
Tabelle "Abwesenheit" A-E
A: Kalenderwoche als Zahl 1-53
B: Datum vom 01.01. - 31.12.
C-E: Mitarbeiterspalten, die Abwesenheitsschlüssel (A, B, C, F, X) für Mitarbeiter enthalten
A | B | C | D | E
KW| Datum |Mayer|Schulze|Müller
1 | 01.01.2015 | B | X | X
1 | 02.01.2015 | A | A | F
1 | 03.01.2015 | A | B | C
1 | 04.01.2015 | A | B | C
2 | 05.01.2015 | B | F | C
2 | 06.01.2015 | B | F | X
2 | 07.01.2015 | X | X | X
2 | 08.01.2015 | A | X | X
2 | 09.01.2015 | A | X | X
2 | 10.01.2015 | X | X | C
2 | 11.01.2015 | X | X | C
3 | 12.01.2015 | X | C | A
3 | 13.01.2015 | B | X | B
3 | 14.01.2015 | F | B | X
...
Tabelle "Kappaplanung" mit Spalten A-D
A: Kalenderwoche als Zahl 1-53
B-D: Mitarbeitserspalten, die nun aggregiert je Kalenderwoche die Abwesenheitstage des Mitarbeiters enthalten soll. Hierbei ist zu beachten, dass nur die Kürzel "A", "B" und "F" als Abwesenheit gewertet werden sollen, die Kürzel "C" und "X" jedoch nicht.
A | B | C | D
1 | 4 | 3 | 1
2 | 4 | 2 | 0
...
Als Hilfstabelle (bzw. Vektor) habe ich noch "Abwesenheitskuerzel" in der in einer Zeile in Spalte A-C die zu wertenden Abwesenheitskürzel stehen:
A | B | F
Um auf die richtigen Ergebnisse für die Tabelle Kappaplanung zu kommen, habe ich folgende Formel erstellt:
Abwesenheit für Mitarbeiter Mayer in KW 1 =SUMMENPRODUKT(ZÄHLENWENN(Abwesenheit!C2:C5;Abwesenheitskuerzel!$A$1:$C$1))
Ich möchte nun aber nicht für jeden Mitarbeiter für jede Kalenderwoche den Bereich (im Beispiel Abwesenheit!C2:C5) manuell angeben, sondern gerne dynamisch ermitteln lassen.
Ich möchte also ausgehend von Tabelle "Kappaplanung" die Kalenderwoche auslesen und damit in der Tabelle "Abwesenheit" die in dieser Kalenderwoche relevanten Abwesenheitskürzel für jeden Mitarbeiter auslesen. Wie stelle ich das an?
Vorab besten Dank!

4
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Aetwas unübersichtlich ...
22.01.2015 09:17:29
neopa
Hallo,
... stell doch mal (D)eine Beispielarbeitsmappe ein und erkläre daran, was genau Du anstrebst.
Gruß Werner
.. , - ...

AW: Aetwas unübersichtlich ...
22.01.2015 11:41:06
Elias
Tut mir leid - nun als Beispielmappe unter https://www.herber.de/bbs/user/95164.xlsx zu finden.
Die Zellen, die berechnet werden sollen, sind gelb hinterlegt.
Die Berechnung an sich stimmt schon. Ich habe in der Formel jedoch je Kalenderwoche jeweils den Bereich manuell eingegeben, was ziemlich aufwändig ist und jedes Jahr angepasst werden müsste.
Was ich möchte ist, dass innerhalb der Formel der Bereich der jeweiligen KW automatisch selektiert wird.
So klarer?

Anzeige
AW: Summenprodukt mit dynamischem Bereich
22.01.2015 11:39:36
Elias
Tut mir leid - nun als Beispielmappe unter https://www.herber.de/bbs/user/95164.xlsx zu finden.
Die Zellen, die berechnet werden sollen, sind gelb hinterlegt.
Die Berechnung an sich stimmt schon. Ich habe in der Formel jedoch je Kalenderwoche jeweils den Bereich manuell eingegeben, was ziemlich aufwändig ist und jedes Jahr angepasst werden müsste.
Was ich möchte ist, dass innerhalb der Formel der Bereich der jeweiligen KW automatisch selektiert wird.
So klarer?

so ist es klarer ...
22.01.2015 11:47:45
neopa
Hallo Elias,
... folgende Formel in A27: =SUMMENPRODUKT((C$2:C$25=$J$2:$L$2)*($A$2:$A$25=$A27)) und nach rechts und unten kopieren.
Gruß Werner
.. , - ...
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige