Quelle: https://www.herber.de/cgi-bin/eachthread.pl?idx=1989543
Stand: 29.08.2024 19:45
Bitte beachten Sie die hier beschriebene Besonderheit in Sachen des Herber-Forums.
Die Fragestellung von Manfred282
Hallo,
ich habe für mein Team eine Excel Datei erstellt, in der ich die tägliche Inbound Mailbearbeitung eintrage.
Hierfür habe ich in Spalte A das Datum und in Spalte B bis I die einzelnen Mitarbeiter.
Die Daten starten ab Zeile 6, die ersten Zeilen sind nur für Namen, Durchschnittswerte gesamt, usw.
So, nun zum Problem. Aktuell sieht die Formel bei mir so aus (Durchschnitt letzte 10 Tage):
=MITTELWERT(BEREICH.VERSCHIEBEN($B$6;ANZAHL2($B$6:$B$999)-10;0;10;1))
Das klappt auch soweit ganz gut, bis auf ein kleines Problem.
In den Spalten B bis I sind nicht nur die Zahlen mit der Anzahl der Mails, sondern auch Buchstaben mit „F”,„K” und „U”, damit ich auch gleich weiß, wann der Mitarbeiter Krank oder Urlaub hatte.
Wenn ich jetzt jemanden in der Liste habe, der 10 aufeinanderfolgende Tage oder mehr einen Buchstaben in den Zellen hat, dann gibt Excel mit als Ergebnis „#DIV/0!” als Ergebnis an, was auch logisch ist, da er dann den Mittelwert daraus errechnen will und das funktioniert nun mal nicht.
Bei der normalen Mittelwertberechnung mit =MITTELWERT(B6:B999) ignoriert er ja leere Zellen und Zellen mit Buchstaben.
Kann ich meine Formel oben so ändern, dass er nur Zahlen in die Berechnung aufnimmt, dies müsste ja mit =WENN(ISTZAHL oder so ähnlich funktionieren, oder?
Leider bin ich kein Excel Profi und bräuchte deswegen mal ein wenig Hilfe, wie ich die Formel umstellen kann 😉
Schon mal danke…
Ein, zwei Anmerkungen vorab: Manfred ist einer der wenigen Fragesteller, die sich nicht nur zum Nulltarif „bedienen lassen“ wollen, er hat sich wirklich Mühe gegeben und all seine aus erster Betrachtung sinnvollen Versuche aufgeführt. Und die Weisung (Funktion) BEREICH.VERSCHIEBEN() ist absolut nicht der Hit, weil volatil und damit ein Ressourcenfresser.
Da leider vom Hilfe suchenden bis jetzt keine Musterdatei eingestellt worden ist, habe ich eine durch einen anderen Helfer erstellte Beispieldatei verwendet und noch etwas manipuliert. Diese biete ich Ihnen hier zum Download an.
Im Endeffekt soll dieses Ergebnis herauskommen:
Da für mich die Nachvollziehbarkeit und Transparenz im Vordergrund steht, habe ich mehrere Abfragen erstellt. Und die dem PQ eigene Dynamik sorgt dafür, dass jeder neue Tag automatisch korrekt erfasst wird.
Der Import-Code
let
Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
#"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Datum", type date}, {"Anna", type any}, {"Bert", type any}, {"Charly", type any}, {"Dolly", type any}}),
#"Entpivotierte andere Spalten" = Table.UnpivotOtherColumns(#"Geänderter Typ", {"Datum"}, "Attribut", "Wert"),
#"Umbenannte Spalten" = Table.RenameColumns(#"Entpivotierte andere Spalten",{{"Attribut", "Name"}}),
#"Geänderter Typ1" = Table.TransformColumnTypes(#"Umbenannte Spalten",{{"Wert", Int64.Type}}),
#"Entfernte Fehler" = Table.RemoveRowsWithErrors(#"Geänderter Typ1", {"Wert"})
in
#"Entfernte Fehler"
Die Query für den ersten Namen… (Anna) Hinweis: Verweis, nicht Duplizieren!
let
Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
#"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Datum", type date}, {"Anna", type any}, {"Bert", type any}, {"Charly", type any}, {"Dolly", type any}}),
#"Entpivotierte andere Spalten" = Table.UnpivotOtherColumns(#"Geänderter Typ", {"Datum"}, "Attribut", "Wert"),
#"Umbenannte Spalten" = Table.RenameColumns(#"Entpivotierte andere Spalten",{{"Attribut", "Name"}}),
#"Geänderter Typ1" = Table.TransformColumnTypes(#"Umbenannte Spalten",{{"Wert", Int64.Type}}),
#"Entfernte Fehler" = Table.RemoveRowsWithErrors(#"Geänderter Typ1", {"Wert"})
in
#"Entfernte Fehler"
Die 3 weiteren Abfragen sind identisch, nur der Filter-Name (in Zeile_3) wurde angepasst. Die letzte Query fasst nun die einzelnen Filter-Queries zum Ergebnis zusammen:
let
Quelle = Table.Combine({Anna, Bert, Charly, Dolly})
in
Quelle
Viel Erfolg beim Erstellen Ihrer Lösung! …Und natürlich ist es auch möglich, in der Datei mit den Rohdaten (RawData) ein Feld zu erstellen, wo die Zahl der auszuwertenden letzten Arbeitstage je Mitarbeiter eingetragen und durch PQ ausgewertet wird.
Und hier, beim Stichwort last but not least finden Sie noch einen Hinweis, wo und wie Sie Antworten auf eventuelle Fragen oder auch die ausgearbeitete Datei bekommen können. Grundlegendes zum Thema M-Code lesen Sie hier im Blog nach.