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
Hallo!
In der vorliegenden Form ist die Tabelle
nicht auswertbar.
Zitat: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?
Zitat: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
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.
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
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:
Abgesehen davon ist es sehr wohl Crosspost, es ist nämlich der gleiche Autor der Datei.
Hallo Ralf
Zitat: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
Zitat:warum soll das nicht auswertbar sein?
Mein Ansatz war in einer Hilfsspalte pro Gruppierung eine eindeutige Nummerierung zu erzeugen und dann mit MITTELWERTWENNS() auszuwerten.
Hallo Detlef,
die Datei mal in Anhang, sie unterscheidet sich wesentlich von der hier. Hilfsspalte wäre natürlich auch eine Option.
[
attachment=3054]
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
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