Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.04.2024 23:47:55
Datum ausweisen, wenn definierte Summe erreicht ist
25.04.2024 21:40:53
Excel Datenquelle aus dem Web mit Anmeldung?
25.04.2024 21:21:16
Zellkommentar in UserForm anzeigen und bearbeiten
25.04.2024 21:13:13
Kalenderwochen addieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1448to1452
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
1448to1452
1448to1452
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Sehr komplizierte Aufgabe

Sehr komplizierte Aufgabe
02.10.2015 19:06:37
Peter
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

17
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Sehr komplizierte Aufgabe
02.10.2015 19:30:23
Sepp
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

Anzeige
AW: Sehr komplizierte Aufgabe
02.10.2015 20:06:50
fcs
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

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

AW: Sehr komplizierte Aufgabe
02.10.2015 23:45:41
Daniel
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

AW: Sehr komplizierte Aufgabe
03.10.2015 01:40:18
Peter
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

Anzeige
AW: Sehr komplizierte Aufgabe
03.10.2015 03:43:16
Peter
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
.

Anzeige
AW: Sehr komplizierte Aufgabe
03.10.2015 13:11:09
Daniel
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)

Anzeige
AW: Sehr komplizierte Aufgabe
04.10.2015 13:26:43
Peter
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

Anzeige
AW: Sehr komplizierte Aufgabe
05.10.2015 12:35:23
Daniel
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

Anzeige
AW: Sehr komplizierte Aufgabe
07.10.2015 21:27:28
Peter
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

Anzeige
AW: Sehr komplizierte Aufgabe
07.10.2015 21:45:39
Daniel
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

AW: Sehr komplizierte Aufgabe
07.10.2015 22:02:20
Peter
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

Anzeige
AW: Sehr komplizierte Aufgabe
07.10.2015 22:15:07
Daniel
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

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

AW: Sehr komplizierte Aufgabe
07.10.2015 22:43:07
Daniel
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

AW: Sehr komplizierte Aufgabe
07.10.2015 22:54:21
Peter
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

AW: Sehr komplizierte Aufgabe
07.10.2015 23:03:36
Daniel
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

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige