VBA: Range in Array laden, Berechnung, auslesen

Antwort erstellen

Smilies
:D :) :( :o :shock: :? 8-) :lol: :x :P :razz: :oops: :cry: :evil: :twisted: :roll: :wink: :!: :?: :idea: :arrow: :| :mrgreen: :badgrin: :doubt:
BBCode ist eingeschaltet
[img] ist eingeschaltet
[flash] ist ausgeschaltet
[url] ist eingeschaltet
Smilies sind eingeschaltet
Die letzten Beiträge des Themas
   

Ansicht erweitern Die letzten Beiträge des Themas: VBA: Range in Array laden, Berechnung, auslesen

Re: VBA: Range in Array laden, Berechnung, auslesen

Beitrag von Sludy » 07. Jun 2019, 07:31

Hallo Michael,

Variante 1 ist genau das was ich verfolge. Funktioniert wunderbar. Danke dir für deinen Input.

VG Sludy

Re: VBA: Range in Array laden, Berechnung, auslesen

Beitrag von Der Steuerfuzzi » 06. Jun 2019, 13:20

Variante 1:
Die Materialbezeichnungen stehen schon in der Tabelle "Auswertung" und es wird nur noch der Wert zu den darin stehenden Materialien eingefügt:
Code: Alles auswählen
Sub Summe1()

Dim dict As Object
Dim arr As Variant, arr2 As Variant
Dim L As Long
arr = Sheets("Test").Range("A7:B20").Value

Set dict = CreateObject("scripting.dictionary")
'Berechnen
For L = 1 To UBound(arr)
    dict(arr(L, 1)) = dict(arr(L, 1)) + arr(L, 2)
Next
arr2 = Sheets("Auswertung").Range("B3:B9").Value
For L = 1 To UBound(arr2)
    arr2(L, 1) = dict(arr2(L, 1))
Next

'Ausgabe

Sheets("Auswertung").Range("C3").Resize(UBound(arr2)).Value = arr2

End Sub


Variante 2:
Die Eintragungen auf dem Blatt "Auswertung" werden gelöscht und es wird komplett neu eingefügt:
Code: Alles auswählen
Sub Summe2()

Dim dict As Object
Dim arr As Variant, arr2 As Variant
Dim L As Long
arr = Sheets("Test").Range("A7:B20").Value

Set dict = CreateObject("scripting.dictionary")
'Berechnen
For L = 1 To UBound(arr)
    dict(arr(L, 1)) = dict(arr(L, 1)) + arr(L, 2)
Next

'Ausgabe bei nicht mehr als 65536 verschiedenen Materialbezeichnungen
'sollten es mehr sein oder werden, dann müsste man das Dictionary zuerst in
'ein Array "umspeichern"
With Sheets("Auswertung")
    .Range("B3", .Cells(.Rows.Count, 2).End(xlUp)).Resize(, 2).ClearContents
    .Range("B3").Resize(dict.Count).Value = Application.Transpose(dict.keys)
    .Range("C3").Resize(dict.Count).Value = Application.Transpose(dict.items)
    With .Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range( _
            "B2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        .SetRange Range("B2:C9")
        .Header = xlYes
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With

End Sub

Re: VBA: Range in Array laden, Berechnung, auslesen

Beitrag von Sludy » 06. Jun 2019, 12:07

Hallo zusammen,

vielen Dank an alle Beteiligten. Ich habe mich aktuell für den Code von "Steuerfuzzi" entschieden (siehe nachfolgend).

Code: Alles auswählen
Sub Summe()

Dim dict As Object
Dim arr As Variant, arr2 As Variant
Dim L As Long
arr = Sheets("Test").Range("A7:B20").Value

Set dict = CreateObject("scripting.dictionary")
'Berechnen
For L = 1 To UBound(arr)
    dict(arr(L, 1)) = dict(arr(L, 1)) + arr(L, 2)
Next
ReDim arr2(1 To UBound(arr), 1 To 1)

For L = 1 To UBound(arr)
    arr2(L, 1) = dict(arr(L, 1))
Next

'Ausgabe
Sheets("Test").Range("C7").Resize(UBound(arr2)).Value = arr2

End Sub

Wie kann man jedoch folgende Zeilen ins "deutsche" übersetzen?

Code: Alles auswählen
For L = 1 To UBound(arr)
    dict(arr(L, 1)) = dict(arr(L, 1)) + arr(L, 2)
Next
ReDim arr2(1 To UBound(arr), 1 To 1)

For L = 1 To UBound(arr)
    arr2(L, 1) = dict(arr(L, 1))
Next

Ich habe zudem die Beispieldatei mal noch modifiziert. Wie würde der Code aussehen, wenn man nun nicht mehr die Summen wie bisher im Reiter "Test" in der Spalte C ausgibt, sondern jeweils nur einmal im Reiter "Auswertung" in Spalte C gemäß der korrekten Materialnummer in Spalte B?

Beste Grüße
Sludy

Re: VBA: Range in Array laden, Berechnung, auslesen

Beitrag von DerHoepp » 05. Jun 2019, 10:13

Moin,

ich möchte noch mal auf die Pivot-Tabelle hinweisen. Ich persönlich erstelle regelmäßig für etwa 90.000 artikel mit etwa 620.000 Buchungen in 5 Ober- und je 5 Unterkategorien eine Pivotauswertung (natürlich mittlerweile per VBA, aber das ändert am Prinzip nix). Die Berechnung der Pivottabelle mit allen notwendigen Informationen benötigt dabei etwa 5 Minuten. Wenn ich die Ober- und Unterkategorien weglasse und einfach nur eine Buchungssumme je Artikel ziehe, komme ich ohne Einsatz von VBA im "Online-Betrieb" auf weniger als zwei Minuten Zusammenklicken der Pivot-Tabelle. Ich verstehe nicht, warum du dich auf eine eigene VBA-Lösung beschränken willst, wenn Excel dir doch ein mächtiges Werkzeug gratis liefert.

Viele Grüße
derHöpp

Re: VBA: Range in Array laden, Berechnung, auslesen

Beitrag von snb » 05. Jun 2019, 10:08

Transpose hört schon ab 6000+ auf.


Hast du schon die Pivottable angeschaut ? viewtopic.php?f=166&t=805740&p=3228191#p3228161

@slow

Es fehlen einige )

Ohne Objectvariablen mit TRANSPOSE:
Code: Alles auswählen
Sub M_snb()
   sn = Sheet1.Range("A7:B20")

   With CreateObject("Scripting.Dictionary")
      For j = 1 To UBound(sn)
        .Item(sn(j, 1)) = .Item(sn(j, 1)) + sn(j, 2)
      Next
     
      Sheet1.Cells(1, 10).Resize(.Count, 2) = Application.Transpose(Array(.keys, .items))
    End With
End Sub


Oder mit INDEX:
Code: Alles auswählen
Sub M_snb()
   sn = Sheet1.Range("A7:B20")

   With CreateObject("Scripting.Dictionary")
      For j = 1 To UBound(sn)
        .Item(sn(j, 1)) = .Item(sn(j, 1)) + sn(j, 2)
      Next
     
      Sheet1.Cells(1, 10).Resize(2, .Count) = Application.Index(Array(.keys, .items), 0)
    End With
End Sub

Re: VBA: Range in Array laden, Berechnung, auslesen

Beitrag von slowboarder » 05. Jun 2019, 10:01

ja, aber die Menge wird ja nicht erreicht.

wenn ja, kann man die Keys und und Items auch in normale eindimensionale Arrays übergeben und dann das Ergebnisarray per Schleife erstellen.
ist zwar etwas programmieraufwand, aber schneller als Transpose.

Gruß Daniel

Re: VBA: Range in Array laden, Berechnung, auslesen

Beitrag von Der Steuerfuzzi » 05. Jun 2019, 09:44

slowboarder hat geschrieben:
Code: Alles auswählen
Sheets("Test").Range("E7").Resize(Dic.Count, ) = Worksheetfunction.Transpose(dic.Keys)
Sheets("Test").Range("F7").Resize(Dic.Count, ) = Worksheetfunction.Transpose(dic.Items)

Application.Transpose habe ich bewusst vermieden. Gab es da nicht auch Probleme (wie bei Application.Index) bei mehr als ca. 65500 Werten?

Re: VBA: Range in Array laden, Berechnung, auslesen

Beitrag von slowboarder » 05. Jun 2019, 09:36

Hi

nur unwesentlich höher:

Code: Alles auswählen
Sub Summe()

Dim arr As Variant
dim dic as Object
Dim L As Long

set Dic = CreateObject("Scripting.Dictionary")
arr = Sheets("Test").Range("A7:B20").Resize(14, 3)

'Berechnen
For L = 1 To UBound(arr)
    dic(arr(L, 1) = dic(arr(L, 1)) + arr(L, 2)
Next

'Ausgebe 1
for L = 1 to ubound(arr)
    arr(L, 3) = dic(arr(L, 1))
Next
Sheets("Test").Range("A7:C20") = arr

'ausgabe 2
Sheets("Test").Range("E7").Resize(Dic.Count, ) = Worksheetfunction.Transpose(dic.Keys)
Sheets("Test").Range("F7").Resize(Dic.Count, ) = Worksheetfunction.Transpose(dic.Items)


End Sub

Gruß Daniel

Re: VBA: Range in Array laden, Berechnung, auslesen

Beitrag von Der Steuerfuzzi » 05. Jun 2019, 09:33

Hier mal ein (schnelles) Beispiel mit einem Dictionary:
Code: Alles auswählen
Sub Summe()

Dim dict As Object
Dim arr As Variant, arr2 As Variant
Dim L As Long
arr = Sheets("Test").Range("A7:B20").Value

Set dict = CreateObject("scripting.dictionary")
'Berechnen
For L = 1 To UBound(arr)
    dict(arr(L, 1)) = dict(arr(L, 1)) + arr(L, 2)
Next
ReDim arr2(1 To UBound(arr), 1 To 1)

For L = 1 To UBound(arr)
    arr2(L, 1) = dict(arr(L, 1))
Next

'Ausgabe
Sheets("Test").Range("C7").Resize(UBound(arr2)).Value = arr2

End Sub

Der Vorteil ist, dass die Berechnung der Werte nur einmal erfolgen muss und nicht für jede Zeile immer wieder. Allerdings ist VBA idR langsamer als Tabellenblattfunktionen.

Eine weitere Möglichkeit als Alternative wäre die Auswertung per ADO (mit einer SQL-Abfrage).

Re: VBA: Range in Array laden, Berechnung, auslesen

Beitrag von Sludy » 05. Jun 2019, 09:28

Hallo Daniel und Michael,

vielen Dank für euren Input. Ich möchte gern bei VBA bleiben. Ich würde mir dann mal in den nächsten Tagen das Dictionary Objekt zu Gemüte ziehen. Besteht im vorliegendem Beispiel ein hoher Programmieraufwand? Wenn man eine "Vorlage" hat, dann fällt mir persönlich die Anwendung bzw. der Verständnisaufbau immer leichter.

Schade dass es doch nicht so einfach geht, wie anfangs vermutet ;(.

LG Sludy

Re: VBA: Range in Array laden, Berechnung, auslesen

Beitrag von Der Steuerfuzzi » 05. Jun 2019, 09:12

Zum Thema Dictionary gibt es eine ganz gute Übersichten und Anleitungen:
https://excelmacromastery.com/vba-dictionary/
http://www.snb-vba.eu/VBA_Dictionary_en.html

Noch eine Ergänzung: Man kann sich schon Teile (Zeilen oder Spalten) eines Array herauspicken. Das ginge z.B. mit Application.Index, hat aber Nachteile und bringt Dir hier wahrscheinlich keinen wirklichen Geschwindigkeitsvorteil. Daher wäre hier evtl. ein Dictionary oder eine Pivot oder PowerQuery die bessere Wahl!

Re: VBA: Range in Array laden, Berechnung, auslesen

Beitrag von slowboarder » 05. Jun 2019, 08:55

HI

die ganzen -WENNS-Funktionen arbeiten nur mit Zellbereichen zusammen, aber nicht mit Arrays.
Warum? - Weil MS das so programmiert hat.

auch wenn du mit Funktionen verarbeiten würdest, du kannst ein großes Array nicht auf diese Weise in ein kleiners Array umwandeln.
das geht so nur mit Zellbereichen.
Du müsstest hier also mehrere Arrays in verschiedenen Größen mit den unterschiedlichen Zellbereichen als Quelle anlegen.

das Arbeiten mit Arrays hat nur dann einen Geschwindigkeitsvorteil, wenn du in einer Schleife Einzelwerte bearbeiten musst.
das liegt daran, dass jede Änderung eines Zellwertes für Excel einen bestimmten "Verwaltungsaufand" anfällt.
änderst du jetzt 1000 Zellen einzeln, muss diese Arbeit 1000x getan werden.
liest du die Werte in ein Array ein, bearbeitest sie dort und schreibst dann das Array als ganzes zurück, dann fällt dieser Arbeitsaufwand nur 1x an, weil Excel das für alle 1000 Zellen gleichzeitig tun kann.
Im Prinzip so wie wenn du 10 Brötchen kaufen willst, es ist schneller, einmal in die Bäckerei zu gehen und dort 10 Brötchen zu kaufen, als 10x in die Bäckerei zu gehen und immer nur ein Brötchen zu kaufen.

in deinem Fall ist es aber so, dass SummeWenn in Verbindung mit großen Datenmengen einfach viel rechenzeit benötigt es dann egal ist, ob du mit Arrays verwendest oder nicht.

wie gesagt, du kannst diese Art der Auswertung auch mit ein paar Mausklicks mit Hilfe der Pivottabelle erstellen.

wenn du bei VBA-Programmierung bleiben willst, solltet du dir mal das Dictionary-Objetk genauer anschauen.
damit lassen sich solche Auswertungen in VBA recht schnell erstellen.
Dictionarys sind im Prinzip eindimensionale Arrays, bei denen der Index nicht durch eine aufsteigende Zahlenfolge (1-x) gebildet werden muss, sondern jeder Beliebige Freitext einen Index bilden kann (z.B. deine Artikelnummern oder auch -Bezeichnungen)

ansonsten habe ich hier beschrieben, wie man in großen Datenmengen solche Auswertungen auch mit Formeln mit geringer Laufzeit erstellen kann.

viewtopic.php?f=166&t=805473

Gruß Daniel

Re: VBA: Range in Array laden, Berechnung, auslesen

Beitrag von Sludy » 05. Jun 2019, 08:33

Es soll immer die gleiche Summe dort stehen =).

LG Sludy

Re: VBA: Range in Array laden, Berechnung, auslesen

Beitrag von snb » 05. Jun 2019, 08:31

Verwende ein Pivottable.

Re: VBA: Range in Array laden, Berechnung, auslesen

Beitrag von Der Steuerfuzzi » 05. Jun 2019, 08:22

Hallo,

dazu müsste man mal wissen, was Du genau berechnen willst. Gesucht ist anscheinend die Summe. Soll da bei jedem Material die gleiche Summe stehen oder fortlaufend?

Nach oben