Office: Mittelwert über Mindestwert in Abhängigkeit von Uhrzeit/Stunde

Guten Abend,

ich beschäftige mich derzeit mit einer Tabelle, in der Geschwindigkeitsmessung durchgeführt wurden.

Erfasst wurden neben Datum, Wochentag und Uhrzeit eben auch die Geschwindigkeiten mit Toleranzabzug.

Fragestellung ist, zu welcher Uhrzeit (hier Stunde) oder Wochentag schneller als 30km/h gefahren wurde und wie der Mittelwert aus diesen Überschreitungen ist, also z.B. 35,6km/h

Mittels Pivot bin ich hier noch nicht weitergekommen bzw. schaffe es hier nur den Mittelwert aller Daten und Uhrzeitenauszugeben, also auch inkl. derer unter 30km/h
Und auch möchte auch nicht die fragliche Werte in ein anderes Tabellenblatt übernehmen, sondern möglichst direkt aus den Originalwerten. Gibt es hier einen Weg, den ich noch übersehe?

Würde mich über Input sehr freuen.
VG Kai

 

Hallo Kai,

die Hilfsspalte G bedarf es nicht.
Die Zeitdaten kannst Du wie folgt ermittel:

In eine freie Spalte: =WENNFEHLER(AGGREGAT(15;6;(A$2:A99+C$2:C99)/(D$2:D$99-3>30);ZEILE(A1));"")
das Zellformat: TT.MM.JJ hh:mm und danach die Formel diese ziehend nach unten kopieren.

Der reale Mittelwert von v welche über dem Grenzwert + 3km/h liegen, wie folgt:
=MITTELWERTWENN(D:D;">33";D:D)

 

A1:G19: für Pivot markieren
In Pivot:
"Uhrzeit" in "Zeilen" ziehen. Dort tauchen "Stunden" und "Uhrzeit" auf, zu sehen sind nur "Stunden", also genau, wie Du es willst. Natürlich kann man noch das Datum drüber ziehen
"Bereinigte Messung ..." in "Werte" ziehen. Als Wertfeldeinstellung "Mittelwert" nehmen.

EDIT: neopa hat es richtig gelesen ;) Ich habe ALLES gemittelt.

Also weiter.
"Bereinigte Messung ..." in "Spalten" ziehen und dort wie folgt gruppieren: "Starten" 31 bis "Beenden" 100 Intervall "Nach" 69.

Man sieht als Nebeneffekt auch die mittlere Geschwindigkeit der braven Bürger.

 

Bin anscheinend ein Exceldummie...

@neopa C Vielen Dank für die Ermittlung des Mittelwertes. Die genannte Formel kann ich nur bis Zeile 7 ziehen, danach wird nichts mehr übernommen und auch die Ausgabewerte sagen mir nichts, außer dass Datum und Uhrzeit nun zusammen in einer Zelle stehen, inkorrekte Zeitwerte haben, siehe Anhang.

@lupo1 verstehe ich nicht was du mit Starten, 31, beenden und 00, Intervall und 69 meinst. Die Funktion Gruppieren kann ich aufrufen, dort wird nach Zeilen oder Spalten gefragt und dann keine weitere Auswahlmöglichkeit mehr. Auch "die mittlere Geschwindigkeit der braven Bürger" sehe ich hier nicht, wobei Bürger zwar Bürger sind, aber eher Kunden auf Werksgelände ;-) Soll der Lokalisation von Bereichen dienen, in denen die Geschwindigkeit gesenkt werden soll.

 

Ich hasse es, für jeden Pups 'ne Datei hochzuladen. Aber hier spart es mir jetzt im Nachgang Zeit. - Die Gruppenbildung geschieht in festen Intervallen. Da theoretisch jmd. auch 100 fahren könne, definiere ich eine Gruppe 31-100 als "Speeding" und die darunter als "braver Bürger". Die Breite ergibt sich zu 69. Und das Ende des "braven Bürgers" mithin zu 30 (eine Geschwindigkeit zwischen 30 und 31 gibt es in Deinen Daten nicht). - Da Du Deine Excelversion nicht verrätst, tue ich es auch nicht. Pivot ist aber unterschiedlich im Handling.

 

Schon mal Deinen Avatar betrachtet?

 

RPP63: Ich bin gutmütiger, als ich mich manchmal gebe ... :)

 

Hallo Geoka,

Die mit der Formel ab J2 ermittelten Zeitdaten sind die, wo die Geschwindigkeit den Grenzwert überschritten hat und das sind nun mal nur 6.

Mit Pivotauswertung würde das dann bei mir wie folgt aussehen:

 

Sorry, war nicht beabsichtigt von mir. Ich nutze derzeit die MS Office Professionel Plus 2019 Version.

@neopa C
@lupo1
Vielen Dank für Eure erläuternden Worte und natürlich Dateien. Werde mir diese heute Abend näher zu Gemüte führen. Vielen Dank an dieser Stelle für eure Zeit und Mühe :-)

 

@lupo1

Vielen Dank für Deinen Input, der mir zum gewünschtem Ergebnis verholfen hat.

 

Die Aussage des Mittelwerts 41-50 ist völlig konstruiert. Interessieren kann doch nur das Mittel der Überschreiter an sich, vielleicht aber auch nicht mal das, sondern nur das Mittel aller, also auch der "braven". Denn nur dann lässt sich eine Aussage treffen (z.B. in einem Wohngebiet), zu welcher Geschwindigkeit man verleitet wird.