Live-Forum - Die aktuellen Beiträge
Datum
Titel
27.04.2024 20:04:34
Excel in Formen z.b Rechteck, eine Tabelle einfügen?
27.04.2024 19:23:05
Passwortabfrage für verschiedene tabellenblätter
27.04.2024 16:48:06
Formel gibt Falsch aus und ich weiß nicht warum - VBA
27.04.2024 14:45:54
Verknüpfung Tabelleneinträge mit Datumsspalte
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1924to1928
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1924to1928
1924to1928
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

VBA-Programm: Mittelwert/Max/Min/...

VBA-Programm: Mittelwert/Max/Min/...
21.03.2023 12:59:33
Andre

Hallo zusammen,

ich bräuchte mal eure Hilfe. Diesmal geht es um ein VBA-Programm bzw. -Funktion.
Folgender Sachverhalt:
Ich habe Rohdaten in einer Textdatei, die ich mir in Excel importiere. Diese Rohdaten bestehen aus einer Vielzahl an Zahlen, aufgegliedert nach verschiedenen "Szenarien". Die Szenarien sind jeweils unter einander gelistet und bestehen aus 80 Zeilen und 260 Spalten.
Zur besseren Verständlichkeit lade ich eine Beispieldatei hoch. Hierin ist allerdings nur ein Ausschnitt enthalten, da die Anzahl an Szenarien später vmtl. bei 1000+ liegen wird.

Das Ziel besteht nun darin, aus diesen Rohdaten in jeweils einem neuen Tabellenblatt folgendes zu berechnen:
Mittelwert
Maximum
Minimum
Standardabweichung
Zur Berechnung sollen jeweils alle Zahlen herangezogen werden, die in den verschiedenen Szenarien an derselben Stelle stehen. Als Beispiel: Für den ersten Mittelwert der neuen Tabelle (A2) sollen die Zellen A2, A84, A166, A248 , etc. herangezogen werden. Hierbei ist sicher hilfreich zu wissen, dass die jeweiligen Zahlen immer an derselben Stelle stehen - sie sind immer 82 Zeilen auseinander. Die Berechnung soll dann für jede Zelle durchgeführt werden. Am Ende ist das Ziel eine einzige Tabelle (bzw. für Mittelwert, max, min, Sigma jeweils eine) aus der Vielzahl an Szenarien zu erhalten. Die Tabelle ist dann wiederum 80x260 groß.
Ich hoffe das ist soweit verständlich.
Ich habe bei der Beispieldatei im Tabellenblatt Mittelwert über eine Formel eingetragen, wie die Berechnung stattfinden soll:
https://www.herber.de/bbs/user/158344.xlsb

Ich habe das ganze auch bereits über eine Formel berechnet, allerdings ist das sehr rechenlastig (zudem muss die Berechnung nur einmal durchgeführt werden. Die Rohdaten ändern sich nicht bzw. für neue Rohdaten wird das Makro dann einfach nochmals durchgeführt. Es braucht also keine permanenten Verlinkungen).
Formel in Zelle A2:
=MITTELWERT(Rohdaten!A2;WENN(REST(ZEILE(INDIREKT("Rohdaten!"&ADRESSE(1;SPALTE(A2))&":"&ADRESSE(VERGLEICH(0;Rohdaten!A:A;-1);SPALTE(A2))))-ZEILE(A2);82)=0;INDIREKT("Rohdaten!"&ADRESSE(1;SPALTE(A2))&":"&ADRESSE(VERGLEICH(0;Rohdaten!A:A;-1);SPALTE(A2)))))​

Ich wäre über eure Hilfe wirklich sehr dankbar!

Viele Grüße
Andre

PS: Falls es auch über eine simple Formel funktioniert, hätte ich auch nichts dagegen :)

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA-Programm: Mittelwert/Max/Min/...
21.03.2023 13:14:06
Rudi Maintaire
Hallo,
in Rohdaten eine Hilfsspalte (JA): =Rest(Zeile(a1);82)
Mittelwert: =MITTELWERTWENNS(Rohdaten!A:A;Rohdaten!$JA:$JA;ZEILE(A1))
Min/ Max analog.

Gruß
Rudi


AW: VBA-Programm: Mittelwert/Max/Min/...
21.03.2023 14:01:30
Andre
Hey, danke dafür. Ich hatte zwar anfangs versuchen wollen das ganze ohne Hilfsspalte zu lösen, aber der Weg funktioniert so auch.
Ich müsste allerdings noch die Standardabweichung ermitteln. Ich konnte eine entsprechende Formel hierfür nicht finden. Wäre das auch irgendwie möglich?


AW: VBA-Programm: Mittelwert/Max/Min/...
21.03.2023 14:21:49
Andre
Edit:
Ich habe mich gerade selbst dran versucht und folgende Formel aufgestellt. Ich glaube die sollte passen:

=STABWN(WENN(Rohdaten!$JA:$JA=ZEILE(Rohdaten!A1);WENN(Rohdaten!A:A>"";Rohdaten!A:A)))


Anzeige
AW: VBA-Programm: Mittelwert/Max/Min/...
21.03.2023 14:27:51
Andre
EDIT 2:
Die Formel ist allerdings seeeehr rechen-/leistungsintensiv. Die Berechnung von 7 Szenarien benötigt auf meinem Rechner mehrere Minuten. Gibt es hierfür vielleicht eine Alternative?


AW: VBA-Programm: Mittelwert/Max/Min/...
21.03.2023 15:30:16
GerdL
Hallo Andre,

weis nicht. Musst du die Dinger unbedingt über ganze Spalten - alle Zeilen runter - rechnen lassen?

Gruß Gerd


AW: VBA-Programm: Mittelwert/Max/Min/...
21.03.2023 15:59:15
Andre
Hey Gerd,
leider ja. Ein Szenario ist jeweils ein Messergebnis mit unterschiedlichen Distanzen. D.h. je mehr "Szenarios" bzw. Messergebnisse ich in die Auswertung mit aufnehme, desto besser ist die Güte des Produkts zu beurteilen :/

VG Andre


Anzeige
AW: VBA-Programm: Mittelwert/Max/Min/...
21.03.2023 16:10:08
GerdL
Hallo Andre,

ich dachte halt, du benötigst nicht alle der 1.048.576 Zeilen.

Gruß Gerd


AW: VBA-Programm: Mittelwert/Max/Min/...
21.03.2023 16:23:07
Andre
Hey, nein, das nicht, allerdings variiert die Anzahl der benötigten Zeilen. Aber da hast du Recht. Ich könnte versuchen das so zu begrenzen, dass es sicher ausreicht.

EDIT:
Habe es gerade versucht und auf 150.000 Zeilen begrenzt. Es geht zwar nicht viel schneller, aber zumindest ein wenig - danke für den Tipp!


AW: VBA-Programm: Mittelwert/Max/Min/...
21.03.2023 16:43:17
Rudi Maintaire
Hallo,
für deine Version geht auch
=STABWN(FILTER(Rohdaten!A$1:A$100000;Rohdaten!$JA$1:$JA$100000=Zeile($A1)))
Wenn du jetzt deine Daten noch in eine formatierte Tabelle bringst, wird alles noch eine Ecke schneller.

Schreibt Rohdaten als Tabelle in Blatt Daten (musst du vorher anlegen)
Sub CreateListObject()
  Dim vntARR, vntOUT(), i As Long, j As Long, k As Long, n As Integer, strS As String
  
  With Sheets("Rohdaten")
    vntARR = .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp)).Resize(, 262)
  End With
  ReDim vntOUT(1 To UBound(vntARR) + 1, 1 To UBound(vntARR, 2))
  
  k = 1
  For j = 1 To 260
    vntOUT(k, j) = "SP_" & Format(j, "000")
  Next
  vntOUT(k, 261) = "Sz"
  vntOUT(1, 262) = "lfd"
  
  For i = 1 To UBound(vntARR)
    If vntARR(i, 1) Like "*Szenario*" Then
      strS = vntARR(i, 1)
      n = 0
    Else
      If vntARR(i, 1) > "" Then
        k = k + 1
        n = n + 1
        For j = 1 To 260
          vntOUT(k, j) = vntARR(i, j)
        Next j
        vntOUT(k, 261) = strS
        vntOUT(k, 262) = n
      End If
    End If
  Next i
  
  With Sheets("Daten")
    .ListObjects(1).Delete
    .Cells.ClearContents
    .Cells(1, 1).Resize(k, 262) = vntOUT
    .ListObjects.Add(xlSrcRange, .Range(.Cells(1, 1), .Cells(k, 262)), , xlYes).Name = "Daten"
  End With
  
End Sub
Die StAbw sieht dann so aus:
=STABW(FILTER(Daten[SP_001];Daten[[lfd]:[lfd]]=ZEILE($A1)))

Gruß
Rudi


Anzeige
=MIN(INDEX(Rohdaten!A:A;ZEILE()+82*{0;1;2;3;4}))
21.03.2023 18:08:08
lupo1

303 Forumthreads zu ähnlichen Themen

maximum finden in VBA
Lars

Hallo zusammen,
ich habe in Excel eine Tabelle, in der in der ersten Zeile Überschriften stehen (B1, B2, B3, ...). Darunter befinden sich die dazugehörigen Werte. Über eine Abfrage suche ich mir nun bestimmte Überschriften (z.B. B1,B3,B4,B7) heraus und lasse mir deren Spalte jeweils in eine...
13.11.2008 07:22:00
Thread-ID: 1024016
6 Beiträge
Anzeige
min und max wert in listbox per vba
kirsche

hallo leute,
dank beverly und tino bin ich ein ganzes stück schlauer und weiter gekommen, ich finde dieses forum einfach klasse und möchte ein großes lob euch allen aussprechen.
nun hab ich aber doch noch eine frage: in zwei weiteren textboxen möchte ich die kleinste und die größte span...
20.11.2008 12:47:01
Thread-ID: 1026383
8 Beiträge
| HERBERS Excel Forum
kai

hallo zusammen,
ich würde gerne per makro die letzten beiden beschriebenen zellen der spalte h löschen?
würde mich freuenn, wenn mir das jemand kurz aufschreiben könnte.
danke und viel grüße kai
26.04.2009 19:51:39
Thread-ID: 1071220
6 Beiträge
Anzeige
In Spalte B einen Wert mittels VBA setzen
Josef

Hallo!
Ich habe nur in der Zelle B1 den Wert "ü" stehen.
Ab der 2.Zeile habe ich ca. 15000 Datensätze stehen.
Mit dem Autofilter suche ich jetzt nach dementsprechenden Werten in anderen Spalten.
Wenn jetzt z. B. 10 Werte gefunden werden, habe ich nun 10 Datensätze und möchte in...
21.01.2010 14:36:58
Thread-ID: 1131799
7 Beiträge
Suche unterschiedliche Max-Wert in einer Spalte
Volker

Hallo zusammen,
in Spalte T stehen Hunderte von siebenstelligen Artikelnummern. Die haben je nach Sparte eigene Nummerkreise:
Sparte A (1.xxx.xxx), Sparte B (2.xxx.xxx) und Sparte C (3.xxx.xxx).
Es kommen innerhalb der Sparten regelmaessig weitere Artikel hinzu.
Suche jeweils di...
14.03.2011 11:00:24
Thread-ID: 1205567
6 Beiträge
mit VBA Prüfen ob ein Wert in Spalte A existiert
marspoki

Hallo,
gibt es in VBA die Möglichkeit in VBA zu prüfen, ob in Spalte A10:A100 der Wert "x" exsistiert?
Vielleicht gib es auch eine Möglichkeit, rauszubekommen wie oft der Wert vorhanden ist?
Vielen Dank im Vorraus
Mars
18.03.2011 10:54:48
Thread-ID: 1206302
4 Beiträge
Max in VBA
Dieter

Hallo zusammen,
die u.g. Berechnung des Max-Wertes funktioniert so nicht.
Sum3 = Evaluate("max((Daten!Kalwo=Aktuell)*(Daten!Ressource=Maschine)*(Daten!Schicht=1)*(Daten!Stillid=Still1))") , habe ich in meinem Mokro eingebaut.....
Wie muss sie korrekt aussehen ?
In der Tabellenfo...
23.05.2011 17:01:54
Thread-ID: 1215644
6 Beiträge
Achsenbeschriftung, VBA durch min max aus Zelle
Peter

Hallo liebe Leute,
ich habe wieder ein Problem.
Ich habe ein simples Leistung/Drehzal Diagramm in Abhängigkeit der Drehzahl.
Die Leistung ist links, als vertikale Primärachse, das Drehmoment rechts als vertikale Sekundärachse und die Drehzahl als vertikale Primärachse angeordnet.
25.06.2011 08:28:33
Thread-ID: 1219617
13 Beiträge

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
5
18
7
5
4
6
5
3
5
12
2
2
3
3
2
7
5
7
8
6
2
2
8
3
5