Excelformel - welcher Mitarbeiter hat wie viele Aufträge pro Monat/Woche bearbeitet?
Hallo Gutefragenutzer,
ich bräuchte eure HIlfe. Und zwar benötige ich eine Excelformel für Excel 2010. Ich hab drei Spalten in Excel in einer steht der Name des Mitarbeiters der dass Projekt bearbeitet hat in der nächsten in Welchem Monat er das Projekt bearbeitet hat und in der dritten Spalte in welcher Kalenderwoche er das Projekt bearbeitet hat.
Nun benötige ich eine Formel mit der ich festellen kann welcher MItarbeiter in welcher Woche / Monat wie viele Projekte bearbeitet hat?
Über eine Formel oder weitere Fragen würde ich mich freuen.
Danke im Vorraus
4 Antworten
Für einen bestimmten Monat geht es in einer Zelle /Mitarbeiter.
Aber die DARSTELLUNG sollte natürlich eine 2D-Tabelle sein. Ich erfinde mal Deine Orte (Du schreibst ja selbst nichts dazu (offenbar versteht kaum einer der Frager genug von xl, um zu wissen, dass man Zelladressen braucht!). Wo steht denn das Projekt? Variabler Text? Und Info nur fürs lfd Jahr bzw 11 Monate zurück? und steht der Monat in jeder Zelle oder nur, wenn wechselt? Und unscharfe Grenzen, wenn man nach Monaten oder nach Wochen listet? Steht der Monat als MMM oder als MMMM formatiertes Datum da oder als Zahl (1..12) oder als Text und ausgeschrieben oder Jan, Feb. ...
Soo viele Fragen...
Es sei Zeile1 frei oder Überschriften (ggf eine neue Zeile einfügen)
Spalte A das bearbeitete Projekt(nur je einmal)
Spalte B der Mitarbeiter
Spalte C der Monat (jede Zelle gefüllt) als formatiertes Datum (!)
Spalte D die Woche (hier irrelevant)
(also Was/Wer/WannM/wannW)
Du schreibst in N1: 1.1.15, in O1 1.2.15 und kopierst die Liste nach rechts bis Z1 = 1.1.16
in Sp.M(wie Mitarb) untereinander die Mitarbeiter
in N2 die Formel
=Summenprodukt(($B2:$B999=$M2)($C2:$C999>=N$1)($C2:$C999<O$1)*Nicht(istleer($A2:$A999)))
Wichtig: Die Adress-Bereiche müssen gleiche Zellenzahl aufweisen!
Diese Formel nach rechts bis Sp.Y und nach unten, soviele MA-Zeilen kopieren.
>=N$1 heisst "am oder nach dem 1.1. und <O$1 heißt vor dem nächsten Ersten, es wird also mit jeder Zelle genau EIN Monat abgedeckt ($-Zeichen beachten.
Anschließend kannst Du den Ergebnisblock verschieben oder in ein anderes Blatt versetzen (nicht nur kopieren:
ausschneiden und einfügen, damit die externen Adressen kreiert werden! Den Block aus dem anderen Blatt kannst Du dann an den gleichen Ort wie dort kopieren (an einem anderen Ort würden sich die Relativ-Adressen verschieben)
Neue Antwort, da man bei Kommentaren keine Bilder anhängen kann.
Erstelle zunächst zwei Hilfstabellenblätter:
- Kalenderwochen
- Monate
Diese sind wie im angehängten Bild angegeben aufgebaut.
In Zelle B2 muss nun die Formel
=ZÄHLENWENNS(Tabelle1!$A:$A;$A2;Tabelle1!$D:$D;B$1)
eingetragen werden.
Diese Formel ist gültig für die Annahme, dass in
- Tabelle1 in Spalte A die Mitarbeiter stehen
- Tabelle1 in Spalte D der Monat angegeben ist
Aus meiner Sicht war bei der Formel nur die Spalte mit dem Monat falsch hinterlegt, weil es bei Dir ja in B steht:
Stell aber sicher, dass die Annahmen auch zutreffend sind:
Deine Werte stehen in einem Tabellenblatt namens Tabelle1
Spalte A (Name), B (Monat) und C (KW) hast Du ja genannt.
Dann schreibt Du bspw. auf Tabellenblatt 2
ab A2 untereinander die Namen der Mitarbeiter (wie im Screenshot gezeigt) und von B1-B12 die Monate (als Zahl oder Wort -> genauso wie sie eben in der Liste stehen).
Dann gehört in die Zelle B2
=ZÄHLENWENNS(Tabelle1!$A:$A;$A2;Tabelle1!$B:$B;B$1)
Wenn Du zusätzlich noch die Gesamtzahl je Monat wissen willst:
In eine neue Zeile:
=ZÄHLENWENNS(Tabelle1!$B:$B;B$1)
Das gleiche kann man mit dem KW machen, nur dass in B1-B51 eben die KW stehen statt der Monate.
Die Formel lautet ZÄHLENWENNS
Du benötigst für die Berechnung jedoch 2 Hilfstabellen
=ZÄHLENWENNS(Tabelle1!$A:$A;Wochen!$A2;Tabelle1!$D:$D;Wochen!B$1)
In Tabelle1 stehen die Daten drin, in Tabelle Wochen in Spalte A die Mitarbeiter (beginnend in Zeile 2) und in Zeile 1 (beginnend ab Spalte B) die Kalenderwochen.
Die Forme oben muss dann in Feld B2
Können sie mir das nochmal genauer erklären? Verstehe noch nicht ganz was sie meinen.
Das geht mit einer Pivot-Tabelle - hier eine kleine Anleitung zu Pivot-Tabellen:
Sobald Du das Prinzip verstanden hast, müsstest Du in der Lage sein es auf dein Problem anzuwenden.
ja ich besitze dazu schon eine Pivottabelle nur ist diese mit 60 Spalten und nicht mit 3. Jedoch nutzt mir diese pivottabelle nichts, da ich aus diesen 3 Spalten ein Säulendiagramm erstellen muss in dem ich in den Säulen sehen kann welcher MItarbeiter in welchem monat wie viele Projekte bearbeitet hat. Können sie mir da weiterhelfen?
Wenn Du mit Pivot-Tabellen nicht zurecht kommst, dann gibt es noch eine weitere Möglichkeit - ich gehe mal davon aus, dass jedes Projekt in einer neuen Zeile steht - diese Methode ist jedoch extrem umständlich.
Du benötigst für jeden Mitarbeiter 12 neue Spalten und entsprechend viele WENN-Funktionen die in die darunter liegenden Zellen eine 1 schreiben wenn das Projekt in dem jeweiligen Monat von dem betreffenden Mitarbeiter bearbeitet wurde. Dann kannst Du unten jeweils eine SUMME bilden und auswerten.
Ich persönlich hätte eine Datenbank daraus gemacht und die Zahlen mit einfachen Abfragen ermittelt.
Vielen Dank für Ihre Antwort. Leider funktioniert es bei mir nicht. Ich habe volgendes Problem in Spalte A stehen verschiedenen Namen (Mitarbeiter) alle Namen kommen mehrmals vor. In Spalte B stehen dann die Monate also z.b. Hr. Xy / Januar / Kalenderwoche 3
Fr. D / März / Kalenderwoche 10
Hr. Xy / August / Kalenderwoche 30
Und so weiter. Jetzt bräuchte ich eine Formel die mir sagt im August wurden so viele Projekte bearbeitet z.b 60 Projekte und davon hat herr Xy 9 stück bearbeitet. Und so weiter. Verstehen sie was ich meine. Würde mich rießig um eine Antwort freuen.