Sehr komplizierte Aufgabe

Bild

Betrifft: Sehr komplizierte Aufgabe
von: Peter Weber
Geschrieben am: 02.10.2015 19:06:37

Hallo Zusammen,
ich habe eine sehr komplizierte Aufgabe die meine VBA-Kenntnisse deutlich überfordert.
Ich muss aus der Tabelle (Alle Daten) eine neue Tabelle (Berechnete Daten) erstellen.
Dabei werden zwei Zeitdaten als Suchkriterium verwendet. Die Originaldatei enthält mehrere Tausend Einträge. Ich habe keine Ahnung wie man so eine Aufgabe VBA-technisch am besten löst. Vielleicht ist das auch nicht so schwer? Hier ist meiner Ansicht nach Expertenwissen gefragt was ich leider nicht habe.
Die angehängte Datei soll als Beispiel zur Verdeutlich dienen.
https://www.herber.de/bbs/user/100559.xlsx
Über jede Hilfe bin ich dankbar.
LG,
Peter

Bild

Betrifft: AW: Sehr komplizierte Aufgabe
von: Sepp
Geschrieben am: 02.10.2015 19:30:23
Hallo Peter,
du solltest deine Dateien nicht mit Schreibschutz hochladen!
Warum VBA?
Berechnete Daten

 ABCDEFGHI
3 Datum Start01.01.2008Datum Ende15.09.2008    
4 Datum Start Vorjahr02.02.2007Datum Ende Vorjahr17.09.2007    
5         
6 RezeptZutatenMenge im Monat (Sept 2008)
Datum Ende
Summe Menge
(Jan 2008 bis Sept. 2008)
Menge im Monat (Feb. 2007) Summe Menge (Feb. 2007 bis Sept. 2007)Differenz
(Menge im Monat (Sept 2008)
Datum Ende - Menge im Monat (Feb. 2007) )
Differenz (Summe Menge
(Jan 2008 bis Sept. 2008) - Summe Menge (Feb. 2007 bis Sept. 2007))
7 ASalz000000
8 APfeffer000000
9 BSalz00650010000-6500-10000
10 BZucker043750201250-15750
11 BPfeffer 000000
12 BMehl018000131250-11325
13 CZucker35002100000350021000
14 CPfeffer096250009625
15 C Mehl000000
16 D---000000
17         

Formeln der Tabelle
ZelleFormel
D7=SUMMENPRODUKT(('Alle Daten'!$B$5:$B$1000=$B7)*('Alle Daten'!$D$5:$D$1000=$C7)*(JAHR('Alle Daten'!$C$5:$C$1000)=JAHR($E$3))*(MONAT('Alle Daten'!$C$5:$C$1000)=MONAT($E$3))*(TAG('Alle Daten'!$C$5:$C$1000)<=TAG($E$3))*'Alle Daten'!$E$5:$E$1000)
E7=SUMMENPRODUKT(('Alle Daten'!$B$5:$B$1000=$B7)*('Alle Daten'!$D$5:$D$1000=$C7)*('Alle Daten'!$C$5:$C$1000>=$C$3)*('Alle Daten'!$C$5:$C$1000<=$E$3)*'Alle Daten'!$E$5:$E$1000)
F7=SUMMENPRODUKT(('Alle Daten'!$B$5:$B$1000=$B7)*('Alle Daten'!$D$5:$D$1000=$C7)*(JAHR('Alle Daten'!$C$5:$C$1000)=JAHR($C$4))*(MONAT('Alle Daten'!$C$5:$C$1000)=MONAT($C$4))*'Alle Daten'!$E$5:$E$1000)
G7=SUMMENPRODUKT(('Alle Daten'!$B$5:$B$1000=$B7)*('Alle Daten'!$D$5:$D$1000=$C7)*('Alle Daten'!$C$5:$C$1000>=$C$4)*('Alle Daten'!$C$5:$C$1000<=$E$4)*'Alle Daten'!$E$5:$E$1000)
H7=D7-F7
I7=E7-G7


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß Sepp


Bild

Betrifft: AW: Sehr komplizierte Aufgabe
von: fcs
Geschrieben am: 02.10.2015 20:06:50
Hallo Peter,
ich würde an deiner Stelle hier mit Formeln arbeiten. Das kann man gut mit der Funktion SUMMENPRODUKT lösen.
Zur besseren Übersicht solltest du die Zellbereiche im Blatt "Alle Daten" mit Namen versehen.
DIe Null-Werte kannst du mit einer benutzerdefinierten Zahlen-Formatierung als "---" dartsellen: 0;-0;"---"
Gruß
Franz
Berechnete Daten

 BCDE
3Datum Start01.01.2008Datum Ende15.09.2008
4Datum Start Vorjahr02.02.2007Datum Ende Vorjahr17.09.2007
5    
6RezeptZutatenMenge im Monat (Sept 2008)
Datum Ende
Summe Menge
(Jan 2008 bis Sept. 2008)
7ASalz--- 

Formeln der Tabelle
ZelleFormel
D7=SUMMENPRODUKT((B7=Daten_Rezept)*(C7=Daten_Zutaten)*($C$3<=Daten_Datum)*($E$3>=Daten_Datum)*Daten_Menge)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Bild

Betrifft: AW: Sehr komplizierte Aufgabe
von: Peter Weber
Geschrieben am: 02.10.2015 22:22:10
Hallo Zusammen,
vielen Dank für Eure Hilfe aber ich muss die Aufgabe unbedingt mit VBA lösen.
LG,
Peter

Bild

Betrifft: AW: Sehr komplizierte Aufgabe
von: Daniel
Geschrieben am: 02.10.2015 23:45:41
Hi
per VBA am besten mit Hilfe des Dictionary-Objektes.
dann kannst du die benötigten Summen in einer Schleife erstellen.
schau dir mal das Beispiel dann.
die Berechnung in den letzten beiden Spalten wird zwar per Formel berechnet, aber wenn man die Formeln mit VBA einträgt, ist das ja auch VBA.
https://www.herber.de/bbs/user/100562.xlsm
Gruß Daniel

Bild

Betrifft: AW: Sehr komplizierte Aufgabe
von: Peter Weber
Geschrieben am: 03.10.2015 01:40:18
Hallo Daniel,
vielen Dank für deine Antwort. Leider habe ich vergessen zu schreiben, dass die Tabelle "Berechnete Daten" ein Beispiel für eine Tabelle ist, die per Makro aus "Alle Daten" erzeugt werden soll.
Es werden nur die Datumswerte geändert und dann soll per Makro die Tabelle erzeugt werden.
(Es ist so, dass die Tabelle "Alle Daten" immer weiter wächst. Zurzeit beinhaltet "Alle Werte" einige tausend Werte und es kommen pro Tag bis 5 Werte dazu. Dabei habe ich bereits ca. 150 Rezeptwerte, und 30 Zutaten.
Hast du dafür auch eine VBA-Lösung?
PS. Die Zeile 7 und 8 in deiner Datei finde ich sehr gut. Sie sollte auf jeden Fall beibehalten werden
LG,
Peter

Bild

Betrifft: AW: Sehr komplizierte Aufgabe
von: Peter Weber
Geschrieben am: 03.10.2015 03:43:16
Hallo Daniel,
ich habe noch etwas vergessen.
Es sollen nur die Zutaten für ein Rezept in der Tabelle "Berechnete Werte" erscheinen die in dem Zeitraum verwendet worden.
Also:
Rezept A, kein Salz in dem Zeitraum verwendet - die Zeile soll nicht angezeigt werden
Rezept A, kein Pfeffer in dem Zeitraum verwendet - die Zeile soll nicht angezeigt werden
Stattdessen soll nur wie in Zeile 18 das Rezept angezeigt werden. Damit soll angezeigt werden, dass das Rezept besteht aber nichts verbraucht wurde in diesem Zeitraum.
https://www.herber.de/bbs/user/100564.xlsm
Schaft du das auch zu lösen?
Ich möchte mich bei dir sehr bedanken!
LG,
Peter
.

Bild

Betrifft: AW: Sehr komplizierte Aufgabe
von: Daniel
Geschrieben am: 03.10.2015 13:11:09
Hi
probiers mal so.
damit der Code lauffähig wird, musst du alle Zeilenumbrüche im Code, die die Forensoftware hier automatisch und oft an unsinnigen Stellen einfügt, entfernen.
Ich habe den Code ganz ohne Zeilenumbruch erstellt.
(Zeilenumbruch im VBA-Code ist ein Unterstrich am ende einer Programmzeile, die Programmzeile geht dann in der nächsten Zeile logisch weiter, kann man machen, damit auch lange einzelne Programmzeilen ohne seitlich zu scrollen auf dem Bildschirm sichtbar sind)

Sub berechnen()
Dim dicErg As Object
Dim dicRez As Object
Dim arrErg
Dim arrDatum
Dim arrRezept
Dim arrAlle
Dim Z As Long
Dim D As Long
Dim ID As String
Dim K
'--- Rahmendaten ( Datum) lesen, alte Daten löschen
With Sheets("Berechnete Daten")
    arrDatum = .Range("D7:G8").Value
    .Range(.Cells(9, 2), .Cells.SpecialCells(xlCellTypeLastCell)).ClearContents
End With
'--- Alle Daten lesen
With Sheets("Alle Daten")
    arrAlle = .Range("B4").CurrentRegion.Value
End With
'--- Alle Daten gruppiert aufsummieren
Set dicErg = CreateObject("Scripting.Dictionary")
Set dicRez = CreateObject("Scripting.Dictionary")
For Z = 2 To UBound(arrAlle, 1)
    ID = arrAlle(Z, 1) & "-" & arrAlle(Z, 3) & "-"
    For D = 1 To UBound(arrDatum, 2)
        If arrAlle(Z, 2) >= arrDatum(1, D) Then
            If arrAlle(Z, 2) <= arrDatum(2, D) Then
                dicRez(Left(ID, Len(ID) - 1)) = 1
                dicErg(ID & D) = dicErg(ID & D) + arrAlle(Z, 4)
            End If
        End If
    Next
Next
'--- Ergebnis-Rezeptliste erstellen
ReDim arrRezept(1 To dicRez.Count, 1 To 2)
ReDim arrErg(1 To dicRez.Count, 1 To UBound(arrDatum, 2))
Z = 0
For Each K In dicRez.keys
    Z = Z + 1
    arrRezept(Z, 1) = Split(K, "-")(0)
    arrRezept(Z, 2) = Split(K, "-")(1)
Next
With Sheets("Berechnete Daten").Range("B9").Resize(UBound(arrRezept, 1), UBound(arrRezept, 2))
    .Value = arrRezept
    .Sort key1:=.Cells(1, 1), order1:=xlAscending, key2:=.Cells(1, 2), order2:=xlAscending,  _
Header:=xlNo
End With
'--- Summen in Ergebnisliste zuruückschreiben
For Z = 1 To UBound(arrRezept, 1)
    For D = 1 To UBound(arrDatum, 2)
        ID = arrRezept(Z, 1) & "-" & arrRezept(Z, 2) & "-" & D
        If dicErg.exists(ID) Then
            arrErg(Z, D) = dicErg(ID)
        Else
            arrErg(Z, D) = "'---"
        End If
    Next
Next
With Sheets("Berechnete Daten")
    With .Range("D9").Resize(UBound(arrErg, 1), UBound(arrErg, 2))
        .Value = arrErg
        With .Offset(0, .Columns.Count).Resize(, 2)
            .FormulaR1C1 = "=IF(AND(RC[-4]=""---"",RC[-2]=""---""),""---"",IF(RC[-4]=""---"",0, _
RC[-4])-IF(RC[-2]=""---"",0,RC[-2]))"
            .Formula = .Value
        End With
    End With
End With
'--- nicht verwendete Rezepte listen
Set dicErg = CreateObject("Scripting.Dictionary")
Set dicRez = CreateObject("Scripting.Dictionary")
For Z = 1 To UBound(arrRezept, 1)
    dicErg(arrRezept(Z, 1)) = 1
Next
For Z = 2 To UBound(arrAlle, 1)
    If Not dicErg.exists(arrAlle(Z, 1)) Then dicRez(arrAlle(Z, 1)) = 1
Next
With Sheets("Berechnete Daten").Range("B9").Offset(UBound(arrRezept, 1), 0).Resize(dicRez.Count, _
 1)
    .Value = WorksheetFunction.Transpose(dicRez.keys)
    .Sort key1:=.Cells(1, 1), order1:=xlAscending, Header:=xlNo
End With
End Sub
Gruss Daniel

Bild

Betrifft: AW: Sehr komplizierte Aufgabe
von: Peter Weber
Geschrieben am: 04.10.2015 13:26:43
Hallo Daniel,
vielen vielen Dank! Das Makro läuft fast bis zum Ende durch.
Hier bleibt das Makro leider stehen. (Lauzeitfehler 1004, Anwendungs- oder Objektdefinierer Fehler)
With Sheets("Berechnete Daten").Range("B9").Offset(UBound(arrRezept, 1), 0).Resize(dicRez.Count, 1)
Ich habe die Datei noch einmal hochgeladen:
https://www.herber.de/bbs/user/100576.xlsm
Du weißt bestimmt woran das liegt.
Ich habe gestern versucht dein Makro zu verstehen. Heute Abend werde ich mich weiter damit beschäftigen. Die Sache mit den scripting.dictionary schein sehr interessant zu sein.
Ich habe nämlich noch eine sehr komplexe Aufgabe und ich glaube dass dieser Art ein Makro aufzubauen genau das richtige ist.
Ich werde die neue Herausforderung noch heute ins Forum reinstellen.
Nochmal, vielen vielen Dank für deine Hilfe
LG,
Peter

Bild

Betrifft: AW: Sehr komplizierte Aufgabe
von: Daniel
Geschrieben am: 05.10.2015 12:35:23
Hi
ganz einfach:
wenn alle Rezepte auch im angefragten Zeitraum vorkommen, gibt es keine nicht verwendeten Rezepte, die noch gelistest werden müssten.
Dann enthält das Dictionary dicRez keine Werte und dicRez.Count ergibt 0.
einen Zellebereich, der 0 Zellen hoch ist, kann Excel aber nicht ansprechen, daher der Fehler.
du müsstest also vor der WITH-Klammer noch eine Abrfage einfügen, ob nicht vorhandene Rezepte überhaupt vorhanden sind und wenn nein, diesen Teil des Codes überspringen:


if dicRez.Count > 0 then
    With Sheets(.....
        ...
    End with
End if
gruß Daniel

Bild

Betrifft: AW: Sehr komplizierte Aufgabe
von: Peter Weber
Geschrieben am: 07.10.2015 21:27:28
Hallo Daniel,
ich hätte noch eine Frage zum letzten Teil des Makros.
Ich musste meine Datei durch eine Rezepturnummer erweitern.
https://www.herber.de/bbs/user/100650.xlsm
Ich habe daruf hin den Code schon geändert. Dank Deiner, sehr geschätzen Hilfe, läuft das Makro bis hier durch. Leider komme ich hier nicht weiter.
Ich möchte Gern das bei diesem Teil die Rezeptnummer, Rezeptname und die Zutaten aufgelistet werden.
Wie müsste der letzte Teil angepasst werden?

'--- nicht verwendete Rezepte listen
Set dicErg = CreateObject("Scripting.Dictionary")
Set dicRez = CreateObject("Scripting.Dictionary")
For Z = 1 To UBound(arrRezept, 1)
    dicErg(arrRezept(Z, 1)) = 1
Next
For Z = 2 To UBound(arrAlle, 2)
    If Not dicErg.exists(arrAlle(Z, 1)) Then dicRez(arrAlle(Z, 1), arrAlle(Z, 2), arrAlle(Z, 3)) _
 = 1
Next
With Sheets("Umsatz Berechnete Daten").Range("B12").Offset(UBound(arrRezept, 1), 0).Resize( _
dicRez.Count, 1)
    .Value = WorksheetFunction.Transpose(dicRez.keys)
    .Sort key1:=.Cells(1, 1), order1:=xlAscending, Header:=xlNo
End With
End Sub
Für deine Hilfe bedanke ich mich herzlich.
LG,
Peter

Bild

Betrifft: AW: Sehr komplizierte Aufgabe
von: Daniel
Geschrieben am: 07.10.2015 21:45:39
Hi
wenn du zusätzliche Spalten hinzufügst, stimmen u.U die Spaltennummern nicht mehr, weil sich die Spaltennummern im Array immer relativ auf den tatsächlich genutzen Zellbereich beziehen.
ursprünglich stand in der ersten Spalte der Rezeptname und in der zweiten Spalte das Datum.
dadurch, dass du am Anfang jetzt noch die R-Nummer voran gestellt hast, steht der Rezeptname jetzt in der zweiten und das Datum in der dritten Spalten.
Das musst du im Code noch anpassen, bevor du weiter machst
Gruß Daniel

Bild

Betrifft: AW: Sehr komplizierte Aufgabe
von: Peter Weber
Geschrieben am: 07.10.2015 22:02:20
Hallo Daniel,
das habe ich alles getan. Das Makro läuft gut. Es werden alle Daten incl. der R.Nummer erstellt. Die Tabelle "Berechnete Daten" sieht super aus. Nur mit dem letzten Teil komme ich nicht weiter.
LG, Peter

Bild

Betrifft: AW: Sehr komplizierte Aufgabe
von: Daniel
Geschrieben am: 07.10.2015 22:15:07
also die Datei die du hochgeladen hast, stoppte wegen diesem Fehler.
die zuätzlichen Infos kannst du doch mit dem SVerweis aus der Liste dann zuspielen.
wie man eine Formel per VBA in die Zellen bekommt, kannst du ja an der Summenformel ableiten
Gruss Daniel

Bild

Betrifft: AW: Sehr komplizierte Aufgabe
von: Peter Weber
Geschrieben am: 07.10.2015 22:26:39
Hallo Daniel,
läßt sich dein Code, den ich genial finde, am ende nicht anpassen?
LG,
Peter

Bild

Betrifft: AW: Sehr komplizierte Aufgabe
von: Daniel
Geschrieben am: 07.10.2015 22:43:07
Hi
natürlich lässt sich der Code anpassen.
auch das hinzunehmen der SVerweisfunktion ist ja eine Anpassung.
du kannst aber auch den Key für die Summen auch aus Rezeptnummer, Rezeptname und Zutatt zusammensetzen.
das passiert in dieser Zeile:

ID = arrAlle(Z, 1) & "-" & arrAlle(Z, 3) & "-"
das ausgabearray von Rezept musst du dann von 2 auf 3 Spalten erweitern und entsprechend befüllen.
das passiert alles hier:
ReDim arrRezept(1 To dicRez.Count, 1 To 2)
       ReDim arrErg(1 To dicRez.Count, 1 To UBound(arrDatum, 2))
       Z = 0
       For Each K In dicRez.keys
           Z = Z + 1
           arrRezept(Z, 1) = Split(K, "-")(0)
           arrRezept(Z, 2) = Split(K, "-")(1)
       Next
ich hab die Ergeänzungen jetzt noch nicht eingebaut, diese Anpassungen überlasse ich dir, schliesslich willst du das ganze ja auch mit gutem Gewissen als deinen Code "verkaufen".
Gruss Daniel

Bild

Betrifft: AW: Sehr komplizierte Aufgabe
von: Peter Weber
Geschrieben am: 07.10.2015 22:54:21
Hallo Daniel,
danke für den Hinweis. Ich mach mich jetzt dran, schließlich möchte ich auch dazulernen und die Herausforderung lösen.
Sollte ich dann nich weiterkommen, melde ich mich wieder und nein, es ist und bleibt dein Code.
Jedes mal, wenn ich eine neue Aufgabe zulösen versuche, werde ich immer diesen Code zurate ziehen und an den Programmiere denken.
LG,
Peter

Bild

Betrifft: AW: Sehr komplizierte Aufgabe
von: Daniel
Geschrieben am: 07.10.2015 23:03:36
Hi
nett von dir, aber letztendlich bis du derjenige, der dafür verantwortlich ist, dass dieser Code funktioniert und richtige Ergebnisse liefert und daher ist es dein Code (auch wenn er nicht von dir ist)
Gruss Daniel

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Sehr komplizierte Aufgabe"