Hallo zusammen,
ich möchte eine Liste erstellen um die Mitarbeiter einer Abteilung in meiner Firma zu zählen.
Versucht habe ich es mit dem Summenprodukt, komme jedoch auf ein falsches Ergebnis.
Beispielhaft habe ich zwei Abteilungen erstellt(siehe Tabelle2).
Das Management umfässt lediglich Mitarbeiter die im Bereich "ITM" sind.
Der Vertrieb soll die Anzahl aller Mitarbeiter des Bereichs "Sales" und "CS" enthalten.
Ein Tag ist in Vormittag, Nachmittag und Abend gegliedert.
Die Mitarbeiter können sich aussuchen wann Sie arbeiten möchten - es kann zwischen einem und drei Abschnitten des Tages gearbeitet werden.
Aw, Ho und Ku stehen entsprechend in beliebiger Reihenfolge in den Zeilen. Die Formel soll den Status des Mitarbeiters nur ein Mal zählen und nicht etwa für jedes erfüllte Kriterium.
Viele Grüße und Danke im Voraus
Andi
Moin
Vor mir eine PQ-Pivot-Lösung.
Hallo Andreas.
Ich kann Dir eine etwas andere Lösung vorschlagen - ohne Pivot, aber mit Hilfsspalten F und G.
Schau mal in den Anhang. (Ich gehe davon aus, dass "Ur" immer in allen drei Spalten steht und nur diese Mitarbeiter nicht arbeiten, sonst ist mindestens eine Spalte der drei gefüllt.)
Das Ergebnis steht hier in F12:G13.
Wenn's noch nicht ganz passt oder ich Dich falsch verstanden habe, bessere ich gerne nach.,
LG Peter
Vielen Dank.
Grundsätzlich spricht nichts gegen die zwei extra Spalten. Gibt es aber auch eine Möglichkeit die Formeln in den Spalten F und G zu kürzen?
So wie es jetzt ist, werden die zu erfüllenden Bedingungen Aw, Ho und Ku für die drei Spalten C,D und E für jede dieser Spalten einzeln überprüft.
Beispielhaft habe ich 4 Werte gewählt 3 sollen zum Ergebnis "Arbeitet" und 1 zum Ergebnis "Arbeitet nicht" führen. Tatsächlich habe ich aber etwa 20 Werte, wovon 13 Kriterium für "Arbeitet" sind, entsprechend der Rest für "Arbeitet nicht".
Wäre es auch möglich, dass in mindestens zwei der drei Spalten ein Wert stehen muss damit die jeweilige Bedingung als erfüllt gilt?
Ein Beispiel:
In Zelle D2 und E2 steht jetzt "Kr" (Krank).
Somit müsste in F2 eine "0" und in G2 eine "1" stehen.
Vielen Dank nochmal (:
Andi
Hallo Andreas.
Ich habe Deine Ideen eingearbeitet.
Die Formel in F und G beginnt jetzt mit WENN(ANZAHL2(C2:E2)<2;0;..... Damit werden Zeilen mit nur einer gefüllten Spalte ignoriert.
Statt die Werte einzeln abzufragen, schlage ich Dir zwei Tabellen-Spalten mit allen möglichen Werten für arbeitet bzw. nicht vor. In meinem Beispiel C19:C21 und D19:D21, besser natürlich in einem separaten Blatt.
Statt der ODER-Abfrage gehe ich jetzt mit VERGLEICH auf diese Liste von Wörtern und setze 0 wenn kein Wert gefunden wird.
Das sieht dann für F2 so aus:
WENN(ANZAHL2(C2:E2)<2;0;MIN(1;WENN(ISTNV(VERGLEICH(C2;$C$19:$C$21;0));0;1)+WENN(ISTNV(VERGLEICH(D2;$C$19:$C$21;0));0;1)+WENN(ISTNV(VERGLEICH(E2;$C$19:$C$21;0));0;1)))
Mein kleines Beispiel findest Du im Anhang. Wenn Du Probleme bei der Implementierung der beiden Listen von Wörtern hast, darfst Du Dich gerne an mich wenden.
LG Peter
Nochmals Danke PAndersen,
die beiden Listen habe ich erfolgreich implementiert. Auch sind die Formeln einwandfrei und berechnen genau das was sie sollen.
Das Problem das ich jetzt aber habe, ist das kopieren der Formel nach rechts.
Zur besseren Übersicht habe ich drei Tabellen erstellt.
In den Zellen C18:C24 sind die Formeln noch in Ordnung, wenn man diese jedoch nach rechts zieht, bleiben die Zeilen gleich (was gut ist) aber die Spalten erhöhen sich um 1.
Beispiel:
Zelle C18 wird nach rechts kopiert: Aus C5:E5 wird D5:F5, richtig wäre aber aus C5:E5 wird F5:H5. Zur besseren Übersicht habe ich die ersten drei Tage farblich markiert, so sollte klar sein welche Bereiche abgebildet werden sollen.
Ich habe es mit "Bereich.verschieben", "Index" und "Indirekt" versucht - erfolgreich war ich leider nicht -
und da dies eine Jahresübersicht wird, erscheint es mir unwirklich jede Formel per Hand zu ändern :20:
Vermutlich werde ich erst am Montag daran weiterarbeiten, vielleicht findet sich aber noch jemand der davon Ahnung hat.
Danke an alle die sich hier beteiligten haben.
Viele Grüße
Andi
Hallo Andi.
Ich empfehle Dir, die Summenspalten nicht zusammenzuschieben, weil es dann mit der Referenz auf die Spaltenmatrix schwierig wird, sondern immer in der Spalte "vormitt" anzusiedeln.
Ich habe den Formeln dieses WENN vorangestellt : WENN(REST(SPALTE();3)>0;""; ... und erreiche damit, dass nur die Spalten C, F, I etc. gefüllt sind, in der Matrix auf die folgenden 3 Spalten oben verweisen und die dazwischen leer bleiben. Diese Formel kannst Du beliebig weit nach rechts ziehen.
Im Anhang ist das implementiert.
Wenn es für Dich unverzichtbar ist, beim letzten Summenüberblick immer einen ganzen Monat (ohne Leerspalten) sehen zu können, wird es echt kompliziert und ich weiß noch nicht. wie das gehen könnte.
Viele Grüße
Peter