19
Quasi statische Kreuztabelle, Spalten in Zeilen jedoch berechnet
Hallo Leute
bin neu hier im Forum, sehr informativ, habe bisher viele Anregungen gefunden. Nun hab ich ein Problem, bei dem ich einfach nicht weiterkomme.
Aus einer recht umfangreichen csv-Datei habe ich die relevanten Daten importiert und aufbereitet. Enthalten sind Artikel mit ihren verschiedenen Lieferdaten. Nun interessiert mich aber nicht die Stückzahl pro Datum, sondern die gesamte Menge pro Lieferwoche. Gut, KW aus Datum ist klar, auch die Begrenzung auf die interessierenden KW (heutige KW plus 8). Ich muss nun aber die Mengen aus den Spalten mit den korrelierenden KW in die erste Zeile des zugehörigen Artikels schreiben. Nicht mehr, einfach statisch. RS ist quasi auch eine KW, nur aufsummiert.
Vielleicht sieht man es besser hier:
Also im Moment fehlt mir ein venünftiger Plan, vielleicht bin ich einfach zu dumm. Bin über jeden Ansatz froh.
Grüsse @all
Sam
Vielleicht sieht man es besser hier:
Also im Moment fehlt mir ein venünftiger Plan, vielleicht bin ich einfach zu dumm. Bin über jeden Ansatz froh.
Grüsse @all
Sam
Share on Facebook
Share on Twitter
Share on Reddit
Share on LinkedinPlease also mark the comments that contributed to the solution of the article
Content-Key: 148598
Url: https://administrator.de/contentid/148598
Printed on: May 6, 2024 at 05:05 o'clock
19 Comments
Latest comment
Hallo samalson!
Also wenn ich dich richtig verstehe müssen alle Liefermengen von KW 31-38 in ein Feld kommen?
Was heisst es wenn keine KW dasteht? Einfach ignorieren?
Bei der Sache mit RS blick ich noch nicht ganz durch... Was käme in das Feld E5? Kommt etwas rein was und wieso? Wieso nicht?
Wäre über Erklärung dankbar (vielleicht versteh ich es dann gut genug um dir zu helfen
)
Mathe172
Also wenn ich dich richtig verstehe müssen alle Liefermengen von KW 31-38 in ein Feld kommen?
Was heisst es wenn keine KW dasteht? Einfach ignorieren?
Bei der Sache mit RS blick ich noch nicht ganz durch... Was käme in das Feld E5? Kommt etwas rein was und wieso? Wieso nicht?
Wäre über Erklärung dankbar (vielleicht versteh ich es dann gut genug um dir zu helfen
)Mathe172
Hallo samalson und willkommen im Forum!
Eigentlich ja eine typische Anwendung für "
Für die weiteren KW lässt sich die Formel kopiren, für die Artikel natürlich nicht ohne Anpassung der Bereiche ...
Grüße
bastla
Eigentlich ja eine typische Anwendung für "
SUMMEWENN()" - also etwa für F5:=SUMMEWENN($C$5:$C$19;F$1;$B$5:$B$19)
Grüße
bastla
Hallo Mathe172
die jeweiligen Artikel enthalten das Lieferdatum. Jetzt können aber mehrere Lieferungen trotz unterschiedlichem Datum trotzdem in eine Lieferwoche fallen. Mein macro rechnet das auch aus und weist dann eben 2 mal die KW 31 aus. Die Liefermengen müssen dann addiert und in die Spalte 31 in Höhe der ersten Zeile des jeweiligen Artikels eingetragen werden. Diese Vorgehensweise gilt für alle nichtleeren KW. Der Eintrag RS wird gesetzt, wenn das Lieferdatum vor dem heutigen liegt, daher können das also nur R.ückS.tände sein. Trotzdem werden sie ja mit den KW gleichbehandelt. Nur aus Platzgründen hab ich die KW32-37 ausgeblendet.
In das Feld E5 käme also der RS des Artikels 2126548, wenn es denn einen gäbe. Dieser Artikel ist aber nicht mit einem RS versehen.
Die Artikel-Datum-Kombinationen, die nicht in das KW-RS-Raster fallen, dienen im weiteren für längerfristige Planungen und werden in einem gesonderten AB verdichtet, verbleiben also als Zeile.
die jeweiligen Artikel enthalten das Lieferdatum. Jetzt können aber mehrere Lieferungen trotz unterschiedlichem Datum trotzdem in eine Lieferwoche fallen. Mein macro rechnet das auch aus und weist dann eben 2 mal die KW 31 aus. Die Liefermengen müssen dann addiert und in die Spalte 31 in Höhe der ersten Zeile des jeweiligen Artikels eingetragen werden. Diese Vorgehensweise gilt für alle nichtleeren KW. Der Eintrag RS wird gesetzt, wenn das Lieferdatum vor dem heutigen liegt, daher können das also nur R.ückS.tände sein. Trotzdem werden sie ja mit den KW gleichbehandelt. Nur aus Platzgründen hab ich die KW32-37 ausgeblendet.
In das Feld E5 käme also der RS des Artikels 2126548, wenn es denn einen gäbe. Dieser Artikel ist aber nicht mit einem RS versehen.
Die Artikel-Datum-Kombinationen, die nicht in das KW-RS-Raster fallen, dienen im weiteren für längerfristige Planungen und werden in einem gesonderten AB verdichtet, verbleiben also als Zeile.
Hallo bastla
Ersteinmal Danke euch beiden (mathe172) für die schnelle Reaktion.
Nun zu SUMMEWENN: Schön wäre es ja, aber keiner weiss genau, wieviele Lieferdaten in eine KW fallen, noch ob überhaupt in der nächsten oder doch erst in der übernächsten Woche relevante Daten anliegen.
Gruss
Samalson
Ersteinmal Danke euch beiden (mathe172) für die schnelle Reaktion.
Nun zu SUMMEWENN: Schön wäre es ja, aber keiner weiss genau, wieviele Lieferdaten in eine KW fallen, noch ob überhaupt in der nächsten oder doch erst in der übernächsten Woche relevante Daten anliegen.
Gruss
Samalson
Hllo samsalon!
Ich hatte schon befürchtet, dass "
- daher eine (eher schütter kommentierte ) Alternative in VBA:
[Edit] Eintrag für "RS" erfolgte in falscher Zeile und Summen des letzten Artikel wurden nicht eingetragen - ist korrigiert [/Edit]
Grüße
bastla
Ich hatte schon befürchtet, dass "
SUMMEWENN()" zuviel "Handarbeit" erfordern würde - daher eine (eher schütter kommentierte ) Alternative in VBA:Sub SummeWenn()
AbZeile = 2 'Daten ab Zeile 2
AbSpalte = 6 'Wochensummen ab Spalte F
Dim KWSumme()
ReDim KWSumme(53)
Z = AbZeile
ArtAkt = Cells(Z, "D").Value
ArtZuletzt = ArtAkt
ArtStart = AbZeile 'Beginn der Artikelbruppe (für Eintragung der Summen) merken
Do While ArtAkt <> ""
If ArtAkt <> ArtZuletzt Then 'neuer Artikelbereich
'Summen eintragen
Cells(ArtStart, "E") = KWSumme(0)
S = AbSpalte
i = Cells(1, S)
Do While i <> ""
Cells(ArtStart, S) = KWSumme(i)
S = S + 1
i = Cells(1, S)
Loop
ReDim KWSumme(53) 'Summen löschen
ArtStart = Z
End If
KW = Cells(Z, "C").Value
If KW = "" Then KW = -1
If KW = "RS" Then KW = 0
Menge = Cells(Z, "B").Value
If KW >= 0 And KW <= UBound(KWSumme) Then KWSumme(KW) = KWSumme(KW) + Menge
Z = Z + 1
ArtZuletzt = ArtAkt
ArtAkt = Cells(Z, "D").Value
Loop
'Summen eintragen
Cells(ArtStart, "E") = KWSumme(0)
S = AbSpalte
i = Cells(1, S)
Do While i <> ""
Cells(ArtStart, S) = KWSumme(i)
S = S + 1
i = Cells(1, S)
Loop
End SubGrüße
bastla
Hallo zusammen!
Wie wärs mit:
für F5? Liesse sich af alle Felder kopieren(ziehen), auch auf die wo nichts stehen darf.
Vielleicht weiss einer von euch wie man die "0" raus bekommt wenn das "wenn" nicht erfüllt wird.
Mathe172
//Edit: Sollte auch für das mit RS also Spalte E gehen
Wie wärs mit:
=WENN($C4<>$C5;SUMMEWENN($C:$C;"=" & F$1;$B:$B);) für F5? Liesse sich af alle Felder kopieren(ziehen), auch auf die wo nichts stehen darf.
Vielleicht weiss einer von euch wie man die "0" raus bekommt wenn das "wenn" nicht erfüllt wird.
Mathe172
//Edit: Sollte auch für das mit RS also Spalte E gehen
@mathe172
BTW: Da eine Abfrage auf "=" Default ist, kannst Du das Gleichheitszeichen auch weglassen:
Grüße
bastla
Vielleicht weiss einer von euch wie man die "0" raus bekommt wenn das "wenn" nicht erfüllt wird.
Wenn's nur das wäre:=WENN($C4<>$C5;SUMMEWENN($C:$C;"=" & F$1;$B:$B);"")
=WENN($C4<>$C5;SUMMEWENN($C:$C;F$1;$B:$B);"")
bastla
Hallo Bastla,
vielen Dank für die Hilfe, war schon bisschen geknickt. Jetzt ist Freude gross, wieder was gelernt.
Danke nochmals.
Gruss
samalson
vielen Dank für die Hilfe, war schon bisschen geknickt. Jetzt ist Freude gross, wieder was gelernt.
Danke nochmals.
Gruss
samalson
Hallo Mathe117
Dir auch vielen Dank, werde Deine Lösung ebenfalls probieren.
Gruss
samalson
Dir auch vielen Dank, werde Deine Lösung ebenfalls probieren.
Gruss
samalson
Hallo samalson!
Grüße
bastla
Jetzt ist Freude gross, wieder was gelernt.
So soll's ja auch sein ... Grüße
bastla
Hallo Bastla
Darf ich vielleicht noch mal nachfragen?
Habe die Kalenderwochen zuordnen können, das klappt auch alles. Jetzt müsste ich Deine Routine nur noch anpassen können, damit ich auch die Mengen bezüglich des Lieferdatums monatsweise ausgeben kann. Dazu habe ich das Lieferdatum auf den ersten eines Monats normiert und will nun, beginnend mit dem aktuellen Monat, die Liefermenge aufsummiert ausgeben. Also alles wie beim ersten Problem, nur sinds jetzt nicht errechnete Kalenderwochen, sondern eben Datumsangaben. Kann ich deine Routine überhaupt dazu verwenden?
Gruss Sam
Darf ich vielleicht noch mal nachfragen?
Habe die Kalenderwochen zuordnen können, das klappt auch alles. Jetzt müsste ich Deine Routine nur noch anpassen können, damit ich auch die Mengen bezüglich des Lieferdatums monatsweise ausgeben kann. Dazu habe ich das Lieferdatum auf den ersten eines Monats normiert und will nun, beginnend mit dem aktuellen Monat, die Liefermenge aufsummiert ausgeben. Also alles wie beim ersten Problem, nur sinds jetzt nicht errechnete Kalenderwochen, sondern eben Datumsangaben. Kann ich deine Routine überhaupt dazu verwenden?
Gruss Sam
Hallo samalson!
Wenn Du analog zur "KW" einfach "Monat" (nicht als Datum, sondern als Monatszahl) verwendest, sollte der Code im Prinzip unverändert weiter funktionieren ...
Grüße
bastla
Wenn Du analog zur "KW" einfach "Monat" (nicht als Datum, sondern als Monatszahl) verwendest, sollte der Code im Prinzip unverändert weiter funktionieren ...
Grüße
bastla
Hallo Bastla!
Meinst Du so?
Meinst Du so?
Sub SummeWennMonat()
'
'
Dim MO
Dim i
Dim fertigTeilSpalte As Integer
Dim rueckStandSpalte As Integer
Dim normLieferDatum As Integer
Dim liefMengeSpalte As Integer
Dim MoJahrSumme()
ReDim MoJahrSumme(1112)
'
fertigTeilSpalte = 8 '1 gleich "A", 2 gleich "B" usw.
rueckStandSpalte = 11
normLieferDatum = 6
liefMengeSpalte = 5
AbZeile = 2 'Daten ab Zeile 2, in der ersten steht ja die Überschrift
AbSpalte = 12 'Monatssummen ab Spalte Nr., also Spalte mit zb "Aug 2010"
'
Z = AbZeile
ArtAkt = Cells(Z, fertigTeilSpalte).Value ' der erste aktuelle Artikel zum unterscheiden also Spalt mit zB. "Fertigteile"
ArtZuletzt = ArtAkt
ArtStart = AbZeile 'Beginn der Artikelgruppe (für Eintragung der Summen) merken
'
' Artikel für Artikel dursuchen
'
Do While ArtAkt <> ""
If ArtAkt <> ArtZuletzt Then 'neuer Artikelbereich
'Summen eintragen
'
' Cells(ArtStart, rueckStandSpalte) = MoJahrSumme(0) 'Spalte steht für "Rückstand"
' ===== WIRD JA NICHT GEBRAUCHT
'
S = AbSpalte
i = Cells(1, S)
'
' solange die Tabelle reicht, also die mitlaufende Zelle (hier die erste) nicht leer ist,
'
Do While i <> ""
Cells(ArtStart, S) = MoJahrSumme(i)
S = S + 1
i = Cells(1, S)
Loop
'
' wirf die Monats-Daten in das Monats-Feld
'
ReDim MoJahrSumme(1112) 'Summen löschen
ArtStart = Z
End If
MO = Cells(Z, normLieferDatum).Value ' Einträge in Spalte "Monat/Jahr" werden geholt
If MO = "" Then MO = -1
'
' If MO = "RS" Then MO = 0 ===== WIRD JA NICHT GEBRAUCHT
'
Menge = Cells(Z, liefMengeSpalte).Value ' Spalte mit "Liefermenge"
If MO >= 0 And MO <= UBound(MoJahrSumme) Then MoJahrSumme(MO) = MoJahrSumme(MO) + Menge
Z = Z + 1
ArtZuletzt = ArtAkt
ArtAkt = Cells(Z, fertigTeilSpalte).Value ' Spalte mit der Artikelkennzeichnung - aktuellen Wert holen
Loop
'Summen eintragen
Cells(ArtStart, rueckStandSpalte) = MoJahrSumme(0) ' eintragen in Spalte "RS"
S = AbSpalte
i = Cells(1, S)
Do While i <> ""
Cells(ArtStart, S) = MoJahrSumme(i) ' Monatseinträge erledigen
S = S + 1
i = Cells(1, S)
Loop
End Sub
Hallo samalson!
Nicht ganz - als MO wird eine Zahl benötigt, genauso für die Spaltenüberschriften ab L12; allerdings könntest Du MO auch aus dem Datum bekommen, indem Du
verwendest ...
Das Array für die Monatssummen muss übrigens nicht ganz so groß werden -
würde es auch tun.
Meine Idee von oben wäre gewesen, einfach die Spalte C auf "Monat" (sowie die Spaltenüberschriften ab F1 auf 1 bis 12) zu ändern und ansonsten den Code völlilg unverändert zu lassen - Du willst ja aber eine Erweiterung der bestehenden Tabelle ...
Nicht ganz - als MO wird eine Zahl benötigt, genauso für die Spaltenüberschriften ab L12; allerdings könntest Du MO auch aus dem Datum bekommen, indem Du
MO = Month(Cells(Z, "A").Value)
Das Array für die Monatssummen muss übrigens nicht ganz so groß werden -
ReDim MoJahrSumme(12)
Meine Idee von oben wäre gewesen, einfach die Spalte C auf "Monat" (sowie die Spaltenüberschriften ab F1 auf 1 bis 12) zu ändern und ansonsten den Code völlilg unverändert zu lassen - Du willst ja aber eine Erweiterung der bestehenden Tabelle ...
Hallo Bastla!
nein, ich muss das in ein neues Tabellenblatt bringen, wäre also keine Erweiterung. Und 1 bis 12 geht nicht so ganz, wäre heute zum Beispiel August bis Juli. Ich könnte also durchaus statt KW die Monatsspalte nehmen, hab ich ja auch schon mit dem normierten Datum gefüllt...
nein, ich muss das in ein neues Tabellenblatt bringen, wäre also keine Erweiterung. Und 1 bis 12 geht nicht so ganz, wäre heute zum Beispiel August bis Juli. Ich könnte also durchaus statt KW die Monatsspalte nehmen, hab ich ja auch schon mit dem normierten Datum gefüllt...
Hallo samalson!
Versuch es einfach mit der ursprünglichen Datei (samt unverändertem Code) und der zu kopierenden Formel
in der Spalte C ...
Grüße
bastla
Und 1 bis 12 geht nicht so ganz, wäre heute zum Beispiel August bis Juli
Na dann eben 8 bis 12 und weiter mit 1 bis 7 - die Monatssumme wird anhand der Zahl in der Überschrift zugeordnet, wobei die Reihenfolge egal ist ...Versuch es einfach mit der ursprünglichen Datei (samt unverändertem Code) und der zu kopierenden Formel
=MONAT(A2)
Grüße
bastla
Hallo bastla!
Mach ich, sag Dir Bescheid.
Herzlichen Dank erstmal.
Gruss
Samalson
Mach ich, sag Dir Bescheid.
Herzlichen Dank erstmal.
Gruss
Samalson
Hallo Bastla!
Klappt soweit prima, ist auch logisch. Nur - habe leider manchmal 2x denselben Monat in untersch. Jahren, muss also das Jahr dazunehmen. Kann ich aus dem formatierten Datum (yymm) einen String machen? So ala Str(cells(...,...).format="yymm") ?
Grüsse
Samalson
PS. Entschuldigung, natürlich einen auswertbaren Integer-Wert, keinen String, dumm von mir ... Also zb. 1008 für August 2010, dann klappts auch mit dem Sortieren...
PS, das zweite: Gelöst, und zwar mit = Right(Year(Cells(zeilenNummerEDI, spalteLieferdatum)), 2) & Format(Cells(zeilenNummerEDI, spalteLieferdatum), "mm")
Nochmals herzlichen Dank an Dich, bastla
Samalson
Klappt soweit prima, ist auch logisch. Nur - habe leider manchmal 2x denselben Monat in untersch. Jahren, muss also das Jahr dazunehmen. Kann ich aus dem formatierten Datum (yymm) einen String machen? So ala Str(cells(...,...).format="yymm") ?
Grüsse
Samalson
PS. Entschuldigung, natürlich einen auswertbaren Integer-Wert, keinen String, dumm von mir ... Also zb. 1008 für August 2010, dann klappts auch mit dem Sortieren...
PS, das zweite: Gelöst, und zwar mit = Right(Year(Cells(zeilenNummerEDI, spalteLieferdatum)), 2) & Format(Cells(zeilenNummerEDI, spalteLieferdatum), "mm")
Nochmals herzlichen Dank an Dich, bastla
Samalson
