Opgelost Recept + Halffabricaten

Dit topic is als opgelost gemarkeerd
Georgyboy

Georgyboy

Terugkerende gebruiker
Lid geworden
6 jan 2007
Berichten
1.020
Besturingssysteem
Windows 11
Office versie
365
Hallo,

Heb een heel goed werkende code, echter wanneer er in Kolom F "ReceptuurNr" een N° staat > 0
Is dit de RecNummer uit kolom A, in Kolom F kunnen er meerdere Nummers staan (Halffabricaten).

Waarschijnlijk te moeilijk om op te lossen? of toch niet?


Bij filter in kolom A op Recnummer maakt dit wat duidelijker, daar zien we ook 2 halffbrikaten,
zijnde 1226 en 1283, waarvan 1283 nog eens een halfabricaat 114 bevat (om het gemakkelijk te maken).
Vele halffabricaten zijn op +/- 100 kg berekend welke we in de eindreceptuur een deel van nodig hebben.
Hier 54 kg van 1226 en 54 kg van 1283 die van halffabricaat 114 komt.

Recept met Halffabricaten (ReceptuurNr) zonder uitsplising ReceptNummer

RecNummer Regel GrondStof NaamRek HoeveelHeid ReceptuurNr
109 3 0 54 1226
109 4 0 44 1283
109 5 154000 PBCTRCZN M-6329 1 0
109 6 85507000 PRPRXKR VLZBXBRRR 1.3 L 1 0
109 7 85515000 FRJXTTRMXX 0,550000012 0
100,55
Klik om te vergroten...

Resultaat Code Jec

109
154000 PBCTRCZN M-6329 1,000 kg
85507000 PRPRXKR VLZBXBRRR 1.3 L 1,000 kg
85515000 FRJXTTRMXX 0,550 kg
14503000 VRRKBN RBBPJBS 31,030 kg
72042100 PRPRXKR GBVLRMD STRBPBN crt=4x2.5Kg 9,310 kg
72011100 RJUXNSCHXJVBN ct=10kg(4x2.5kg) 9,310 kg
85422000 GYRZSKRUXDBN MZ 0,620 kg
85034000 CFX MBRT PRZTBCTXZN TZTRL 0,310 kg
84514000 KZZLZRRDZLXB 3,100 kg
85019000 PRPRXKRPZBDBR 0,310 kg
0 44,000 kg

Hier 100,45 kg

Sub jec()
Dim ar, dict, k, i As Long
ar = Cells(1, 1).CurrentRegion
Set dict = CreateObject("scripting.dictionary")

With CreateObject("scripting.dictionary")
For i = 2 To UBound(ar)
k = ar(i, 1) & "|" & ar(i, 2)
If ar(i, 1) = Range("K1").Value Then 'dit kan ook variabel
dict(k) = Array(ar(i, 3), ar(i, 4), ar(i, 5))
If ar(i, 6) > 0 Then
.Item(ar(i, 5)) = ar(i, 5) & "|" & ar(i, 6)
dict.Remove k
End If
End If
Next

If .Count Then
For Each ky In .items
For i = 2 To UBound(ar)
If ar(i, 1) = Val(Split(ky, "|")(1)) Then
dict(ky & "|" & ar(i, 3)) = Array(ar(i, 3), ar(i, 4), Round(Split(ky, "|")(0) / Evaluate("sumif(A:A," & Split(ky, "|")(1) & ",E:E)") * ar(i, 5), 2))
End If
Next
Next
End If
End With

Range("J1").CurrentRegion.Offset(1).ClearContents
Range("J2").Resize(dict.Count, 3) = Application.Index(dict.items, 0, 0)
End Sub
Klik om te vergroten...

Hier met Halffabicaten

109
0 54,000 kg 12,96%
0 44,000 kg 10,56%
154000 PBCTRCZN M-6329 1,000 kg 0,24%
85507000 PRPRXKR VLZBXBRRR 1.3 L 1,000 kg 0,24%
85515000 FRJXTTRMXX 0,550 kg 0,13%
14503000 VRRKBN RBBPJBS 31,034 kg 7,45%
72042100 PRPRXKR GBVLRMD STRBPBN crt=4x2.5Kg 9,310 kg 2,24%
72011100 RJUXNSCHXJVBN ct=10kg(4x2.5kg) 9,310 kg 2,24%
85422000 GYRZSKRUXDBN MZ 0,621 kg 0,15%
85034000 CFX MBRT PRZTBCTXZN TZTRL 0,310 kg 0,07%
84514000 KZZLZRRDZLXB 3,103 kg 0,75%
85019000 PRPRXKRPZBDBR 0,310 kg 0,07%
0 44,000 kg 10,56%
0 44,000 kg 10,56%
314000 WRTBR 12,539 kg 3,01%
85109000 MBCZ VBRS CL-ZZ 1,486 kg 0,36%
85034000 CFX MBRT PRZTBCTXZN TZTRL 0,418 kg 0,10%
85100000 MBRT PRZTBCTXZN CL 0,557 kg 0,13%
314000 WRTBR 42,271 kg 10,15%
85318000 TZMRTBNCZNCBNTRRRT 28/30 20 KG 0,604 kg 0,14%
85305000 KBTCHUP HBXNZ 10.2 LXTBR 9,058 kg 2,17%
85317000 TZMRTBNPULP 36,232 kg 8,70%
85173000 KRLFSFZND 0,906 kg 0,22%
85006000 ZVBNKRUXDBN DBLXCRTBSSB 25 kg/zRk 0,604 kg 0,14%
85332000 GLUTRCLBRN 0,181 kg 0,04%
154000 PBCTRCZN M-6329 2,717 kg 0,65%
84516000 PRZVZB PLRNTRRRDXGB ZLXB 1,812 kg 0,43%
85351000 KRXSTRLSUXKBR 0,604 kg 0,14%
85507000 PRPRXKR VLZBXBRRR 1.3 L 0,181 kg 0,04%
77000 UXBNPZBDBR BGYPTB STRNDRRRD 0,302 kg 0,07%
85070000 RJUXN GBSTZZFDB 4,529 kg 1,09%
314000 WRTBR 31,591 kg 7,58%
85181000 TZMRRT NRTRXUMBBPBRKT KNZRR 3,510 kg 0,84%
85015000 WXTTB PBPBR GBMRLBN 0,070 kg 0,02%
85014000 LRURXBRBLRD 0,018 kg 0,00%
72039000 SJRLZT DV (10x250G)= 2.5KG/DZZs 1,053 kg 0,25%
85332000 GLUTRCLBRN 0,035 kg 0,01%
84516000 PRZVZB PLRNTRRRDXGB ZLXB 0,702 kg 0,17%
85317000 TZMRTBNPULP 7,020 kg 1,69%
314000 WRTBR 12,539 kg 3,01%
85109000 MBCZ VBRS CL-ZZ 1,486 kg 0,36%
85034000 CFX MBRT PRZTBCTXZN TZTRL 0,418 kg 0,10%
85100000 MBRT PRZTBCTXZN CL 0,557 kg 0,13%
416,550 kg 100,00%

Sub Recept1903()
Dim ar, dict, k, i, j As Long
Dim totaal As Double
Dim rngOutput As Range
Dim laatsteRij As Long
ar = Cells(1, 1).CurrentRegion
Set dict = CreateObject("scripting.dictionary")

' Eerste doorloop: hoofdrecepten en halffabricaten verzamelen
For i = 2 To UBound(ar)
k = ar(i, 1) & "|" & ar(i, 2)
If ar(i, 1) = Range("K1").Value Then
dict(k) = Array(ar(i, 3), ar(i, 4), ar(i, 5))
End If
Next

' Tweede doorloop: halffabricaten toevoegen aan hoofdrecepten
For i = 2 To UBound(ar)
If ar(i, 6) > 0 Then
Dim recNum As Long
recNum = ar(i, 6)
Dim factor As Double
factor = ar(i, 5) / Application.WorksheetFunction.SumIf(Columns(1), recNum, Columns(5))
For j = 2 To UBound(ar)
If ar(j, 1) = recNum Then
k = ar(i, 1) & "|" & ar(i, 2) & "|" & ar(j, 2)
If dict.exists(k) Then
dict(k)(2) = dict(k)(2) + ar(j, 5) * factor
Else
dict(k) = Array(ar(j, 3), ar(j, 4), ar(j, 5) * factor)
End If
End If
Next
End If
Next

' Output wissen en nieuwe data invoegen
Range("J1").CurrentRegion.Offset(1).ClearContents
Set rngOutput = Range("J2").Resize(dict.Count, 3)
rngOutput.Value = Application.Index(dict.items, 0, 0)

' Kolom L opmaken als "00.000 kg"
Range("L:L").NumberFormat = "0.000"" kg"""

' Laatste rij bepalen voor de som
laatsteRij = rngOutput.Row + rngOutput.Rows.Count

' Berekening totaal kolom L
Range("L" & laatsteRij).Font.Bold = True
totaal = Application.WorksheetFunction.Sum(Range("L2:L" & laatsteRij - 1))
Range("L" & laatsteRij).Value = totaal
Range("L" & laatsteRij).Font.Bold = True ' Optioneel: vetgedrukt

' Kolom M berekenen als percentage van het totaal
If totaal > 0 Then
Dim cell As Range
For Each cell In Range("M2:M" & laatsteRij - 1)
cell.Value = Round(cell.Offset(0, -1).Value / totaal, 4) ' Getal tussen 0 en 1
cell.NumberFormat = "0.00%" ' Celnotatie als percentage
Next
End If

' Som van kolom M moet 100% zijn
Range("M" & laatsteRij).Value = 1 ' 100% als numerieke waarde
Range("M" & laatsteRij).NumberFormat = "0.00%" ' Celnotatie als percentage
Range("M" & laatsteRij).Font.Bold = True ' Optioneel: vetgedrukt
Range("L2:L" & laatsteRij - 1).Font.Bold = False
Range("M2:M" & laatsteRij - 1).Font.Bold = False
End Sub
Klik om te vergroten...
 

Bijlagen

Goedemorgen,
is een uitbreiding van een oudere topic

Recept + Halffabricaat recept samenvoegen​

Klik om te vergroten...

Waarvoor dank aan Jec en Cow18
 
wat je beschrijft is een BOM structuur. meestal wordt dit in een database gedaan, maar dit filmpje toont een voorbeeld in Excel:
BOM
 
Dankjewel NoellaG,
Inderdaad!

Komt uit een ERP en via een Query kwamen dit dit resultaat, dit is een kleine selectie een een heel groot bestand. Den dat VBA of misschien Power Query het beste is?
 
moet het in Excel of mag het ook in access? in dat geval zou ik mijn vraag ook daar eens stellen. Indien liefst in Excel laat ik het over aan de echte Excel guru's.
 
Access lijkt me interessant voor recepten te beheren met verschillende tabellen.
Artikel, Leverancier, hoeveelheid, prijs, gewichtsverlies, werktijd, sub formulier met werkwijze, .......

Dit is voor later daar daarvoor een goede opbouw noodzakelijk is. Daar kan ik zeker hulp voor gebruiken.

Hier gaat het om gegevens die via een Qry uit een ERP komen en dit snel en makkelijk kunnen bewerken.
De basis is gemaakt geweest door Jec en Cow18 met daaraan wat uitbreiding.

Huidige vraag is complexer zoals een Halffabricaat in een halffabricaat met de juiste eindtotalen.

Dank om mee te denken :)
 
Ik vind het gek dat jullie een ERP-systeem hebben zonder BOM's (Bill Of Materials / recepturen)
Aan de basis van een ERP-systeem voor een productie afdeling staan, materialen lijst, bom's (wat heb ik nodig om het te maken, ingekocht of samengesteld), en routings (welke stappen om te maken, en hoe lang duren die).

Met een recursieve functie kan je de hele receptuurlijst doorlopen.
Er moet alleen niet het risico bestaan dat product A is gemaak van product B en product B is gemaakt van product A, want dan kom je in een oneindige lus.
Een goed ERP systeem met BOM's kan met deze situatie omgaan.
 
Laatst bewerkt:
Dag Alphamax,
Via de ERP kunnen we inderdaad de BOM in of uitklappen per receptuur.
Vraagt per receptuur redelijk veel tijd.
Echter om sneller gaan kunnen we via een QRY naar in dit geval Excel via 1 receptnummer
het recept zien met de halffabricaten uit de laatste kolom.
Die uitsplitsing via de QRY naar Excel is er nog wat verfijning nodig indien mogelijk.
 
Hallo Georges,
Je zal me misschien niet meteen herkennen (ander forum, andere gebruikersnaam;)), maar dus na hele lange tijd (remember 'sql') nog eens een leuke vraag van jou aangetroffen.
Los van voorgaande bedenkingen waar ik (in meerdere of mindere mate) achtersta, maar laten we daarover vooral niet in detail treden, heb ik in een methode voorzien waarvan ik denk dat je het zo bedoelt.
En jawel, het is in Excel en, vooral omdat de meesten in een kramp slaan enkel bij het woord 'recursie', hier met imitatie-recursie, en met geen overdreven lange code. Het aantal niveaus van halffabricaten zou ook geen rol mogen spelen.
 

Bijlagen

Dus jullie maken geen werkorders aan het ERP-systeem?
Als een werkorder is aangemaakt kan je een picklijst/werkbon printen, waar precies op staat welke ingredieenten je nodig hebt.
Wie gaat er voor zorgen dat de BOM in het ERP-systeem en de BOM in excel synchroon lopen.

Ik werk al jaren met SAP-ERP (geliefd en berucht) maar volgens mij gebruiken jullie niet alle basis functies van het ERP-systeem.
 
Goedemorgen,

@Enigmasmurf
Ja ik herken je meteen want gebruik nog steeds een super code (VRD (voor Voorraad)) waar
jij destijds heel veel moeite hebt gedaan voor mij!
Bedankt heb er nog steeds véél aan.
Wederom een super bruikbare oplossing van je (herkenbaar )😉

Weer super gedaan éérste testen gedaan op een zeer groot bestand en ziet er op eerste zicht goed uit.
Probeer dit verder uit gezien het grote bestand. Werkt alleszins sneller dan een knipoog :).
Zou er ook een totaal in Kolom L kunnen komen en een % van het totaal in kolom M?

@alphamax
Denk dat we mekaar waarschijnlijk verkeerd verstaan door mijn info, sorry als het zo is!
Onze ERP werkt naar behoren en maakt inderdaad werkorders aan.
Ieder werkorder is uniek en kan met de vaste receptuur, berekend van 1 grondstof of gewenste kg eindproduct.
Bij verwerking in productie worden de juiste gewichten en lotnummers ingegeven per regel en het eindproduct afgeboekt met een uniek lotnummer.
Zo kunnen we alles traceren.

De vraag in Excel is om snel buiten de ERP gegevens te kunnen gebruiken voor o.a. R&D waar je buiten de ERP aanpassingen of simulaties kan doen.
Zo kunnen we op een bestaande receptuur simuleren op klantenvraag, wijziging grondstoffen, zoutreductie, enz. Enz.
Verder kunnen zo ook Privé verder werken als het moet.
De voorbereiding via Excel is daarvoor handig om het later gestructureerd in de ERP te implementeren.

Verder is het leuk voor mij om met Excel, VBA, Power Query bezig te zijn.
Mijn dank is dan ook groot om deel te mogen zijn van Helpmij waar ik steeds terecht kan met mijn vragen en super toepasbare antwoorden van de experts onder jullie.
 
Hoi Georges,
Minstens de ganse voormiddag niet thuis maar zodra mogelijk zet ik dat er nog wel bij.
 
Ziezo, je extra wens (totaal & percentages) is ook vervuld. Omdat het nu eenmaal over gewichten gaat heb ik een extra cijfer achter de komma gezet (afronden op een gram is allicht logischer dan op 10).
Veel plezier ermee!
 

Bijlagen

Ja mijn wens is weeral vervult en zal er weer véél plezier aan hebben:)
Hoop dat het voor je ook leuk was om op te lossen.
Hartelijk dank!
 
Wel ja, de allergrootste uitdaging was het weer niet, maar qua leukheidsgehalte viel het best mee;)
Maar wat mij het meeste plezier doet is uiteraard dat je ermee geholpen bent.
Groetjes.
 
Voor mij al een hele uitdaging en zeer blij met de oplossing,
Gaat sneller dan een oog knip en correct op een zeer groot bestand!
Ga er lang van genieten en er plezier aan hebben.
Groetjes,
Georgyboy
 
Je moet ingelogd zijn om te kunnen reageren. Log in | Registreer
                    Steun ons                    

Nieuwste berichten

Terug
Bovenaan Onderaan