Mittelwert bestimmter Zellen

[foenamike]

Guten Abend liebes Forum!

Habe folgendes Problem:

Ich habe eine Tabelle, wo in der ertsen Spalte die Zeitdifferenz zwischen 2 Reparaturen eingetragen ist. Die Reparaturen beziehen sich immer auf eine bestimmte Anlage, zwischen den Anlagen ist jeweils eine leere Zeile vorhanden. Nun möchte ich eine Auswertung erstellen, und zwar zuerst die Mittelwert der Reparaturen einer Anlage einzeln und in weiterre Folge einen Gesamtmittelwert über alle Anlagen hinweg berechnen!

Vielleicht ist es verständlicher, wenn ich mich auf die Excel-Datei beziehe:

Die Ausgangsdaten sind in Spalte A vorhanden. Die leeren Zeilen zwischen den Werten geben an, dass es sich um einen neue Anlage handelt [Anlage 1 von A3-A6, Anlage 2 von A8-A11,..].
Ich möchte nun in der Zelle B6 den Mittelwert der Zellen A3 bis A6 berechnen. Ebenso soll in der Zelle B11 der Mittelwert der Zellen A8-A11 ausgegeben werden und so weiter.

Wie man den Mittelwert berechnet, ist mir schon klar! Alleridngs suche ich eine Formel, welche mir den Mittelwert immer nur von dem Zahlenblock berechnet, welcher oberhalb steht (also Mittelwert von A3-A6 in Zelle B6 ausgeben). Ansonsten sollte die Spalte B leer bleiben! Und das ganze sollte mit einer schönen Formel funktionieren, den bei 92.908 Zeilen ist das händische Befüllen eine Lebensaufgabe... 

Ich hoffe die Erklärung war halbwegs verständlich!

Wäre sehr dankbar, wenn jemand einen Tipp für mich hätte!

Dankeschön,


Glg

[RPP63]

Hallo!
In der vorliegenden Form ist die Tabelle nicht auswertbar.

wo in der ertsen Spalte die Zeitdifferenz zwischen 2 Reparaturen eingetragen ist.

Die Differenz muss ja irgendwie errechnet worden sein!
Wo also ist Beginn und Ende der jeweiligen Reparatur dokumentiert?

zwischen den Anlagen ist jeweils eine leere Zeile vorhanden

Haben die Kinder (Maschinen) auch Namen?
Wo ist der?

Ach so: 92089 Zeilen sind für eine Beispieldatei etwas überdimensioniert und für ein Forum unnötiger Traffic.

Gruß Ralf

[RPP63]

... und wie es der Zufall so will, hat im Nachbarforum jemand anderes ein ganz ähnliches Problem.
Da hier die Datenbasis gut ist, hat die Lösung 10 Sekunden gedauert:

http://www.ms-office-forum.net/forum/sho...ost1700565

Gruß Ralf

[GMG-CC]

Es gibt auch für diese etwas abartige Datei eine Lösung, selbst wenn die Leerzeilen keine Leerzeilen sind. Das geht per VBA recht gut.

Schade nur, dass ich inzwischen keinerlei Lust mehr habe, Crossposter, die nicht verlinken, zu unterstützen. Dieses Thema steht ja nun deutlich genug an oberster Stelle.
Und noch blöder ist, dass ich das mit dem Crossposting erst (durch Ralf) erfahren habe, als ich die VBA-Lösung posten wollte.

[RPP63]

Moin Günther!
Nein, ich glaube nicht, dass es sich um Crossposting handelt.
Hier ist tatsächlich Kommissar Zufall im Spiel, das Problem ist wohl verblüffend ähnlich, aber nicht identisch.
Stelle doch mal Deine Lösung vor, die interessiert mich nämlich auch.

Gruß Ralf

[BoskoBiati]

Hallo Ralf,

warum soll das nicht auswertbar sein?


In B3, nach unten ziehen:

Code:

=WENN(INDEX(A:A;ZEILE()+1)="";MITTELWERT(INDEX(A:A;VERWEIS(9;1/($A$1:INDEX(A:A;ZEILE()-1)="");ZEILE($A$1:INDEX(A:A;ZEILE()-1)))):INDEX(A:A;ZEILE()));"")

In C3 ein einfacher Mittelwert über B:

Code:

=Mittelwert(B:B)

Abgesehen davon ist es sehr wohl Crosspost, es ist nämlich der gleiche Autor der Datei.

[shift-del]

Hallo Ralf

Nein, ich glaube nicht, dass es sich um Crossposting handelt.
Hier ist tatsächlich Kommissar Zufall im Spiel, das Problem ist wohl verblüffend ähnlich, aber nicht identisch.

Da ich in MOF nicht angemeldet bin, kann ich mir die Datei nicht runterlanden und mit dieser hier vergleichen.
Aber die Problembeschreibung, Name und Gruß weisen schon eine hohen Übereinstimmungsgrad auf.

@Bosko

warum soll das nicht auswertbar sein?

Mein Ansatz war in einer Hilfsspalte pro Gruppierung eine eindeutige Nummerierung zu erzeugen und dann mit MITTELWERTWENNS() auszuwerten.

[BoskoBiati]

Hallo Detlef,

die Datei mal in Anhang, sie unterscheidet sich wesentlich von der hier. Hilfsspalte wäre natürlich auch eine Option.


  Auswertung.xlsx (Größe: 9,78 KB / Downloads: 4)

[RPP63]

Hi!
[gleicher Autor]
Oops, da habe ich nicht drauf geachtet.

Allerdings frage ich mich ernsthaft, wieso hier eine Datei mit > 92K Zeilen reingehämmert wird, die relevanten Informationen aber vorher entfernt werden! 

Gruß Ralf

[GMG-CC]

Moin  Ralf,
Moin @ alle anderen Helfer und Mitleser,

erst einmal: Ich meide das andere Forum inzwischen und möchte dort auch nicht wegen eines Datei-Downloads wieder aktiv werden. Aber das ist ja nicht das eigentliche Thema. Ich vertraue also einfach einmal auf verschiedene Angaben hier im Forum und komme der Bitte nach dem Code einmal nach, auch wenn es offensichtlich auch eine reine Formel-Lösung gibt ... (Ich bin eben VBA-affin )

Da sind ja noch mehr Ungereimtheiten. Warum sind die Zellen, welche als Leerzellen erscheinen, keine "echten" Leerzellen? Ein Mal Strg+CursorDown bringt mich mit einem Schlag runter zu Zeile_92908. Und warum sind da mal eine, mal mehrere "inhaltslose" Zeilen?

Na ja, ich habe zwei Makros geschrieben, ein Mal Array/Tabellenfunktion gemischt und ein Mal rein Array. Die Zeitunterschiede sind merkbar, aber nicht unbedingt gravierend. Probiert es selber einfach einmal aus ...

Code:

Option Explicit
Option Base 1

Sub MittelwertX()
  Dim lRow As Long
  Dim rngData As Range
  lRow = Cells(Rows.Count, 1).End(xlUp).Row
  Dim aData(), ArrZe As Long
  Dim fRowInBlock As Range, lRowInBlock As Range
 
  With Application
     .Calculation = xlCalculationManual
     .ScreenUpdating = False
  End With

  On Error GoTo ErrorHandler
  'Leerzeilen sind nicht wirklich leer, darum mit "" ersetzen
  Set rngData = Range(Cells(2, 1), Cells(lRow, 1))
  aData = rngData
  For ArrZe = 1 To UBound(aData)
     If Not IsNumeric(aData(ArrZe, 1)) Then aData(ArrZe, 1) = ""
  Next ArrZe
  rngData = aData
 
  Set lRowInBlock = Cells(2, 1)
  Do
     Set fRowInBlock = lRowInBlock.End(xlDown)
     ' 1-Zeiler abfangen
     If fRowInBlock.Offset(1, 0) = "" Then
        Set lRowInBlock = fRowInBlock
     Else
        Set lRowInBlock = fRowInBlock.End(xlDown)
     End If
     If fRowInBlock.Row = 92903 Then Exit Do
     Cells(lRowInBlock.Row, 2) = _
      WorksheetFunction.Average(Range(fRowInBlock.Address & ":" & lRowInBlock.Address))
     If lRowInBlock.Row = lRow Then Exit Do
  Loop
  Cells(1, 4) = WorksheetFunction.Average(Range("B:B"))
 
ErrorHandler:
  With Application
     .Calculation = xlCalculationAutomatic
     .Calculate
     .ScreenUpdating = True
     MsgBox "Fertig!", vbInformation
     If Err.Number <> 0 Then
     
     MsgBox lRowInBlock.Row
     MsgBox "Fehler Nr.: " & Err.Number & vbCrLf _
        & Err.Description
     End If
  End With
End Sub

Sub MittelwertX2()
  'ErrorHandler fehlt noch!
  'Etwas schneller, aber andere Ergebnisse!
  Dim lRow As Long
  Dim rngData As Range
  lRow = Cells(Rows.Count, 1).End(xlUp).Row
  Dim aData(), ArrZe As Long
  Dim fRowInBlock As Range, lRowInBlock As Range
  Dim Summe As Single, AnzBlock As Integer
  Dim fNumeric As Long
 
  With Application
     .Calculation = xlCalculationManual
     .ScreenUpdating = False
  End With

  'Leerzeilen sind nicht wirklich leer, darum mit "" ersetzen
  Set rngData = Range(Cells(2, 1), Cells(lRow, 1))
  aData = rngData
  For ArrZe = 1 To UBound(aData)
     If Not IsNumeric(aData(ArrZe, 1)) Then aData(ArrZe, 1) = ""
  Next ArrZe
 
  'Ersten Numeric-Eintrag finden
  For ArrZe = 1 To UBound(aData)
     If aData(ArrZe, 1) > "" Then
        fNumeric = ArrZe
        Exit For
     End If
  Next ArrZe
 
  For ArrZe = fNumeric To UBound(aData)
     If aData(ArrZe, 1) > "" Then
        Summe = Summe + aData(ArrZe, 1)
        AnzBlock = AnzBlock + 1
     Else
        If aData(ArrZe - 1, 1) > "" Then
           Cells(ArrZe, 2) = Summe / AnzBlock
           Summe = 0
           AnzBlock = 0
        End If
     End If
  Next ArrZe
  Cells(ArrZe, 2) = Summe / AnzBlock
  Cells(1, 4) = WorksheetFunction.Average(Range("B:B"))
 
  With Application
     .Calculation = xlCalculationAutomatic
     .ScreenUpdating = True
  End With
End Sub