Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1540to1544
1540to1544
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Mittelwert mit vba bilden, für best. Bereiche

Forumthread: Mittelwert mit vba bilden, für best. Bereiche

Mittelwert mit vba bilden, für best. Bereiche
09.02.2017 17:57:35
Antonia
Hallihallo,
ich habe hier mal wieder ein kleines Problem, bei dem ich nicht weiter komme.
Im Anhang habe ich hier eine Liste mit unterschiedlichen Produkten (Ziffern 1bis 9), die zu unterschiedlichen Zeitpunkten zu unterschiedlichen Preisen verkauft wurden. In die Spalte danach würde ich nun gerne mittels vba für die jeweils gleichen Produkttypen, die auch zum gleichen Zeitpunkt gekauft wurden, den Mittelwert des Preises bilden. Wenn nur ein Produkttyp zu einem Zeitpunkt verkauft wurde, soll einfach der Preis kopiert werden. Ich habe die zusammengehörigen Gruppen auch nochmals farbig markiert. So ist es vielleicht verständlicher.
https://www.herber.de/bbs/user/111349.xlsm
Ich würde mich sehr freuen, wenn mir jemand weiterhelfen könnte.
Viele Grüße,
Antonia
Anzeige

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Mittelwert mit vba bilden, für best. Bereiche
09.02.2017 18:59:46
Antonia
Hab das Problem nochmals in einem anderen Forum gepostet. Falls jemand dort eine andere Lösung hat;) Wer interessiert ist, kann dort auch noch mal nachschauen:
http://www.office-loesung.de/p/posting.php?mode=reply&f=166&t=732472
Anzeige
AW: Mittelwert mit vba bilden, für best. Bereiche
10.02.2017 01:28:55
Piet
Hallo Antonia,
probiere bitte einmal den folgenden Code aus, er müsste funktionieren.
mfg Piet

Dim AC As Object, PRNr As String
Dim Preis As Currency, lz As Long
Dim Zeit As Date, Zeit2 As Date
Sub Mittelwert_bilden()
Dim n As Integer, ze As Long
lz = Range("A1").End(xlDown).Row
n = 1: ze = 2 '1.Zeile Mittelwert
Range("D2:D" & lz).ClearContents
PRNr = Range("A2").Value
Zeit = Range("C2").Value
Preis = Range("B2").Value
On Error Resume Next
For Each AC In Range("A2:A" & lz)
Zeit2 = CDate(AC.Cells(1, 3))
If PRNr = AC.Value And Zeit = Zeit2 Then
Preis = Preis + AC.Cells(1, 2)
n = n + 1  'Teiler addieren
Else
'Mittelwert eintragen
Cells(ze, 4) = Round(Preis / n, 2)
ze = AC.Row:  n = 1
'Variable neu laden
PRNr = AC.Value
Zeit = AC.Cells(1, 3)
Preis = AC.Cells(1, 2)
'Einzelwerte eintragen
If PRNr  AC.Cells(2, 1) Then
AC.Cells(1, 4) = AC.Cells(1, 2)
End If
End If
Next AC
End Sub

Anzeige
AW: Mittelwert mit vba bilden, für best. Bereiche
10.02.2017 01:38:24
Piet
Nachtrag
der Bereich für Mittelwert ist als Datum Formatiert!! Bitte auf Normal aendern!!
AW: Mittelwert mit vba bilden, für best. Bereiche
10.02.2017 08:31:25
Antonia
Hi Piet,
hab's grade ausprobiert, es funktioniert!!! Danke!!!
Viele Grüße,
Antonia
AW: Mittelwert mit vba bilden, für best. Bereiche
10.02.2017 12:05:10
Antonia
Hi Piet,
jetzt hab ich doch noch eine Frage: Habe deinen Code in meine Originaldatei übertragen und nun wird mir zwar an den richtigen Stellen aber nicht der Mittelwert oder der tatächliche Preis eingetragen, sondern eine 0. Dies liegt daran, dass das Programm für Preis/Sowohl auch für Zeit immer den Wert null nimmt.
Weißt du woran das liegen kann?
Viele Grüße,
Antonia
Anzeige
AW: Mittelwert mit vba bilden, für best. Bereiche
10.02.2017 17:10:22
Piet
Hallo Antonia,
Frage: stimmen die Spalten und Zeilen so wie in der Beispieldatei? Wenn ja kannst du mir aus deiner Origianldatei das Blatt mit einigen Daten als Beispiel hochladen. Im Augenblick weiss ich noch nicht wo der Fehler sein kann?
Alternativ einen Tipp von mir zur Fehlersuche, nenne ich Praxis. Setze hinter die Variable Zeit2 zwei MsgBoxen mit Exit Sub und schau dir an welche Werte in den Variablen stehen? Das kannst du auch in den If Then Auswertungen machen. Dann sieht man ob dort Werte drin stehen oder sie Leer sind. Oder ob Werte nicht stimmen, wenn z.B. das Zellen Format nicht übereinstimmt.
mfg Piet
  Zeit2 = CDate(AC.Cells(1, 3))
MsgBox Zeit & Chr(10) & Zeit2
MsgBox PRNr & "  " & Preis
Exit Sub

Anzeige
AW: Mittelwert mit vba bilden, für best. Bereiche
12.02.2017 14:39:14
Antonia
Hi Piet,
vielen Dank nochmal für deine Hilfe. Ich habe es mit deiner Methode versucht, komme aber trotzdem nicht weiter. Hier nochmal ein Auszug der Originaldatei. Die drei Spalten, mit welchen wir gerechnet haben, habe ich grau markiert. Ich glaube aber dass, das Marko für Preis und Uhrzeit den Wert Null annimmt und deshalb auch das Ergebnis immer null ist. An der Formatierung liegt es meiner Meinung nach aber auch nicht, da die betreffenden Spalten genauso formatiert sind wie im ursprünglichen Beispiel.

Die Datei https://www.herber.de/bbs/user/111402.xlsm wurde aus Datenschutzgründen gelöscht


Vielen Dank für deine Hilfe!!
Antonia
Anzeige
mal reingeschaut
12.02.2017 17:56:02
Michael
Hi Antonia,
in Deiner neuen Datei steht in E61 eine 0 - das sollte nicht sein. In diesem Falle (weil zwei Zeilen für diese Ware vorhanden sind) macht das nichs, bei nur 1 Zeile käme eine Division durch 0 raus.
Das könnte man im Makro abfangen, aber das mag ich jetzt nicht machen.
Versuch mal das: 
Sub test()
Dim a, b  ' ohne Angabe = as Variant, Verwendung als Array
Dim i&, zmax&, za& ' & = as long
Dim GP#, St#       ' # = as double
zmax = Range("A" & Rows.Count).End(xlUp).Row + 1
a = Range("A1:H" & zmax)
ReDim b(1 To zmax, 0)
b(1, 0) = "Mittelwert"
za = 2 ' d.h. beim Beginn der Schleife wird zunächst
' Zeile 2 mit Zeile 2 verglichen.
For i = 2 To zmax
If a(i, 8) = a(za, 8) And a(i, 6) = a(za, 6) Then
GP = GP + a(i, 5)
' geht davon aus, daß in E der GesamtPreis steht,
' nicht der Einzelpreis.
' Ansonsten müßte man hier noch multiplizieren.
St = St + a(i, 4)
Else
b(za, 0) = GP / St
GP = a(i, 5): St = a(i, 4)
za = i
End If
Next
Range("I:I").ClearContents
Range("I1").Resize(zmax, 1) = b
End Sub
Die Datei: https://www.herber.de/bbs/user/111412.xlsm
Schöne Grüße,
Michael
Anzeige
Anzeige
Anzeige

Infobox / Tutorial

Mittelwert mit VBA für bestimmte Bereiche berechnen

Schritt-für-Schritt-Anleitung

Um den Mittelwert mit VBA für bestimmte Bereiche in Excel zu berechnen, folge diesen Schritten:

  1. Öffne Excel und lade deine Daten. Stelle sicher, dass die Spalten für Produkttypen, Preise und Zeit korrekt formatiert sind.

  2. Aktiviere die Entwicklertools in Excel, falls diese noch nicht aktiviert sind. Du kannst dies unter Datei > Optionen > Menüband anpassen tun.

  3. Öffne den VBA-Editor durch Drücken von ALT + F11.

  4. Füge ein neues Modul hinzu: Rechtsklick auf "VBAProject (deineDatei.xlsx)" > Einfügen > Modul.

  5. Kopiere den folgenden Code in das Modul:

    Sub Mittelwert_bilden()
   Dim AC As Range, PRNr As String
   Dim Preis As Currency, lz As Long
   Dim Zeit As Date, Zeit2 As Date
   Dim n As Integer, ze As Long

   lz = Range("A1").End(xlDown).Row
   n = 1: ze = 2 ' Startzeile für Mittelwert
   Range("D2:D" & lz).ClearContents
   PRNr = Range("A2").Value
   Zeit = Range("C2").Value
   Preis = Range("B2").Value

   On Error Resume Next
   For Each AC In Range("A2:A" & lz)
       Zeit2 = CDate(AC.Cells(1, 3))
       If PRNr = AC.Value And Zeit = Zeit2 Then
           Preis = Preis + AC.Cells(1, 2)
           n = n + 1  ' Teiler addieren
       Else
           ' Mittelwert eintragen
           Cells(ze, 4) = Round(Preis / n, 2)
           ze = AC.Row:  n = 1
           ' Variable neu laden
           PRNr = AC.Value
           Zeit = AC.Cells(1, 3)
           Preis = AC.Cells(1, 2)
       End If
   Next AC
End Sub

  6. Führe das Makro aus: Drücke F5 oder gehe auf Run > Run Sub/UserForm.

Häufige Fehler und Lösungen

  1. Null-Werte in den Ergebnissen:

    • Überprüfe, ob die Daten in den verwendeten Zellen korrekt und richtig formatiert sind. Die Spalte für den Preis sollte als Währung formatiert sein.

  2. Falsche Ergebnisse:

    • Stelle sicher, dass die Spalten und Zeilen der Originaldaten mit denen im Beispiel übereinstimmen. Überprüfe auch, ob die Daten in der richtigen Reihenfolge stehen.

  3. Formatierung als Datum:

    • Wenn der Bereich für den Mittelwert als Datum formatiert ist, könnte das zu Problemen führen. Ändere die Formatierung auf "Normal".

Alternative Methoden

Wenn du den Mittelwert nicht mit VBA berechnen möchtest, kannst du auch Excel-Funktionen verwenden:

  • Mittelwert-Funktion: 
    =MITTELWERTWENN(A:A, "Produkt1", B:B)

    Diese Funktion berechnet den Mittelwert für alle Einträge eines bestimmten Produkts.

Praktische Beispiele

  • Beispiel 1: Wenn du die Preise für Produkt 1 in den Zellen B2:B10 hast und die Zeit in C2:C10, kannst du die Funktion wie folgt verwenden:

    =MITTELWERTWENN(A2:A10, "Produkt1", B2:B10)

  • Beispiel 2: In einem komplexeren Szenario, wo mehrere Bedingungen gelten, könntest du die SUMMEWENN und ANZAHLWENN Kombination verwenden, um den Mittelwert zu berechnen.

Tipps für Profis

  • Nutze Debugging: Füge MsgBox-Befehle in deinen Code ein, um Werte in Variablen während der Ausführung anzuzeigen. Dies hilft, Probleme schnell zu identifizieren.

  • Optimierung: Wenn du mit großen Datenmengen arbeitest, überlege, wie du die Schleifen optimieren kannst, um die Ausführung zu beschleunigen.

  • Dokumentation: Kommentiere deinen VBA-Code gut, damit du und andere Benutzer später schneller verstehen, was der Code macht.

FAQ: Häufige Fragen

1. Frage
Wie kann ich sicherstellen, dass der VBA-Code in meiner Datei funktioniert?
Antwort: Stelle sicher, dass die Daten in der richtigen Struktur und Formatierung vorliegen. Teste den Code in einer Kopie deiner Datei, um sicherzustellen, dass keine Daten verloren gehen.

2. Frage
Kann ich den Mittelwert für mehrere Produkte gleichzeitig berechnen?
Antwort: Ja, du kannst den Code anpassen, um Schleifen für verschiedene Produkte zu integrieren oder die Excel-Funktion MITTELWERTWENN für mehrere Bedingungen zu verwenden.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige