EXCEL: Meisterformel gesucht :-(

Computer: Software & Programmierung Office Software
1

Hi, hab leider noch eine Nuß zu knacken! Aber Ihr seid ja echt gut hier :smile:

Also, ich habe 3 Spalten (mit sehr viele Einträgen)

  1. Spalte = Datum
  2. Spalte = Uhrzeitbeginn
  3. Spalte = Uhrzeitende

Die 1. Spalte hat öfter gleiche Daten (plural Datum)

Beispiel

  1. Spalte______2. Spalte______3. Spalte

03.12.2002______8:00______13:00
03.05.2002______8:20______13:40
04.05.2002______8:10______14:00
02.05.2002______8:10______14:10
03.05.2002______8:20______13:30
04.05.2002______8:25______14:00
02.05.2002______8:30______13:00
… ____________…_________…

Ich schreibe jetzt mal aus, was die Formel können muss (falls es EINE dafür geben sollte)

Sortiere Spalte 1 nach Tagen. Errechne die Gesamtstunden (nicht 24 Stunden Rhythmus) für alle einzelnen Tage (Bsp.: 31.12.2002 = 5 Stunden; 03.05.2002 = 10 Stunden 30 Minuten oder 10,5 Std), bilde dann davon die Gesamtsumme und teile die durch die Anzahl der Tage (unterschiedliche Tage - ähnlich der Matrixformel von eben)

Hat da jemand einen Plan, wie man das anstellen könnte? Das Sortieren ist kein Problem, aber der Rest… :frowning:

2

mal ein Vorschlag:

  1. Sortieren:
    dies lös ich mittels Makro[keine Leerzellen sollen enthalten sein.
    Sub sortiere()
    Range(„A2“, Range(„A65536“).End(xlUp).Offset(0, 3)).Select
    Selection.Sort Key1:=Range(„A1“), Order1:=xlAscending, Key2:=Range(„B1“) _
    , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
    False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
    :=xlSortNormal
    Range(„A1“).Select
    End Sub
  2. Summenbildung
    du hast 3 Spalten[Datum-A,Start-B,Ende-C]. erzeug eine 4. Hilfspalte-D und verwende folgende Formel: =C3-B3 nun erhältst du die Dauer-D formatiere die Zellen mit: [hh]:mm
    und in die 5.Spalte schreibe folgende Formel:
    z.B in Spalte E Zeile 1:
    =Summewenn(A:A;A1;E:E)/Anzahl2(A:A)
    nun erhälts du als Wert die angefallenen Stunden des jeweiligen Datums dividierd durch die Anzahl der Datumseinträge.

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

3

etwas anders
Also

Hier noch mal das Beispiel

  1. Spalte______2. Spalte______3. Spalte

03.12.2002______8:00______13:00
03.05.2002______8:20______13:40
04.05.2002______8:10______14:00
02.05.2002______8:10______14:10
03.05.2002______8:20______13:30
04.05.2002______8:25______14:00
02.05.2002______8:30______13:00
… ____________…_________…

Bilde Summe der Zeiten für gleiches Datum und teile durch die Anzahl. Bilde Summe alles Durchschnittswerte und teile durch Gesamtanzahl Tage.

Das muss so sein, sonst gibt es eine statistische Ungenauigkeit.

Und jetzt muss ich das irgendwie zu einer Formel kleistern. Oder ich teile es auf.

Jemand noch eine Idee?

4

weis nicht was los ist,schickes schon 3 mal und läd nicht rein??

hier die richtige Lösung:

= summewenn(A:A;A1;E:E)/Zählenwenn(A:A;A1)

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

5

summe für Anzahl verschiedener Datumseingaben:
=SUMME(WENN(A1:A100="";0;1/ZÄHLENWENN(A1:A100;A1:A100)))

diese Formel als Matrixformel behandeln[Strg+Umschalt+Enter]

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

6

Hilfe
Hi, könntest Du das mal auf mein Beispiel anwenden? Bekomme es einfach nicht hin.

Noch einmal der Hinweis: Bilde Summe der Zeiten für gleiches Datum und teile durch die Anzahl der einzelnen Tage. Dadurch bekommt jeder Tag einen Durchscnittswert. Danach soll die Summe aller Durchschnittswerte ermittelt und durch Gesamtanzahl Tage geteilt werden.

Das muss so sein, sonst gibt es eine statistische Ungenauigkeit.

Ich hoffe, Du weißt es :frowning:

7

Zirkelbezug

weis nicht was los ist,schickes schon 3 mal und läd nicht
rein??

hier die richtige Lösung:

= summewenn(A:A;A1;E:E)/Zählenwenn(A:A;A1)

Hier sagt mir Excel, dass es ein Zirkelbezug wäre

8

Hi Sisko,
hast du nirgends Zugriff auf ein bisschen Webspace wo du eine kleine Beispieldatei hochstellen kannst?
Gruß
Reinhard

9

hab da mal ne Frage:
kopierst du die Formeln einfach von hier raus??

oder kontollierst du auch die spalten,die du zum berechnen verwendest mit denen der Formeln.
das Letzere wäre ein bischen besser.

aber macht nix, schick mir deine Datei und ich korrigier sie