Toon posts:

Excel formule vastgestelde uitkomst variabele eenheden

Pagina: 1
Acties:

Onderwerpen

Vraag


Acties:
  • 0 Henk 'm!

Verwijderd

Topicstarter
Ik wil graag een formule in Excel invoeren die het volgende kan berekenen (versimpeld voorbeeld).

Er zijn dezelfde producten met verschillende aantallen qua inhoud/gewicht aanwezig.

Ik wil een formule maken waarin het uiteindelijke gewicht (het antwoord) leidend is voor de in te zetten losse eenheden (de variabelen).

Als er bijvoorbeeld een product is aanwezig met een gewicht/inhoud van 4,68 en 10,8.

Dan wil ik in Excel invoeren:
Artikel 1 = 4,68
Artikel 2 = 10,8
En dan verder eventuele meerdere artikelen.
Te leveren gewicht: 1.000 (dit zouden dan 6x artikel 1 en 90x artikel 2 kunnen zijn, maar ook 36 om 77 en 66 om 64 = allen 1.000,08)

Maar als ik de 1.000 zou veranderen in een andere waarde (bv 250), wil ik dat de formule uitrekent wat dan de verhoudingen tussen de artikels worden.

Excel moet dus aan de hand van het door mij gegeven eindgewicht berekenen wat de meest ideale verhouding is tussen de verschillende artikelen (1.000 is bv. niet precies mogelijk, 1.000,08 is de waarde het dichtst bij 1.000).

Wanneer iemand hier een formule voor weet zou dit erg gewaardeerd worden!

Alle reacties


Acties:
  • 0 Henk 'm!

  • MAX3400
  • Registratie: Mei 2003
  • Laatst online: 27-09 22:07

MAX3400

XBL: OctagonQontrol

Te leveren gewicht: 1.000 (dit zouden dan 6x artikel 1 en 90x artikel 2 kunnen zijn, maar ook 36 om 77 en 66 om 64 = allen 1.000,08)
Hier ga je dus nat op. Want wat is "de ideale verhouding"? Heb je daar een 1-op-meerdere relatie voor?

Mijn advertenties!!! | Mijn antwoorden zijn vaak niet snowflake-proof


Acties:
  • 0 Henk 'm!

  • naitsoezn
  • Registratie: December 2002
  • Niet online

naitsoezn

Nait Soez'n!

Met een formule waarmee je van een gegeven configuratie de 'waarde' uitrekent, een formule waarmee je het verschil tussen het ideale getal en de berekende waarde uitrekent, en de Solver zou je een eind kunnen komen. Maar dan loop je idd wel tegen de opmerking van MAX3400 hierboven aan, want met de Solver kun je één van de oplossingen bepalen als er meerdere zijn.

[ Voor 7% gewijzigd door naitsoezn op 27-11-2017 14:31 ]

't Het nog nooit, nog nooit zo donker west, of 't wer altied wel weer licht


Acties:
  • 0 Henk 'm!

Verwijderd

Topicstarter
OK, ik was inderdaad al bang dat de verschillende mogelijkheden een probleem zouden kunnen zijn.

Bij dit product is het zo dat 30 x artikel 1 qua gewicht gelijk is aan 13 x artikel 2.

Wat ons betreft is de beste verhouding in ieder geval zo veel mogelijk artikel 2.
Maar ik weet niet of Excel met deze subjectiviteit rekening kan houden, ik kom er in ieder geval niet uit.

Ik weet nog van mijn studie (te lang geleden om te kunnen onthouden:)) dat we een opdracht moesten maken van een boer die een bepaald aantal m2 aan grond had en verschillende producten met verschillende marges en variabele kosten (uiteraard met een parabool effect zodat de uitkomst niet 1 product met de beste marge was). Dan kon je met een formule Excel laten uitrekenen welke producten hoeveel m2 moesten krijgen voor de beste marge/benutting van de grond.

De formule ben ik dus lang vergeten, maar vond deze gewichten/aantallen er enigszins op lijken vandaar dat ik het probeerde op deze manier.

Acties:
  • +1 Henk 'm!

  • mifa
  • Registratie: Oktober 2004
  • Laatst online: 09-09-2024
Dit is toch linear programeren? en je hebt twee onbekenden, dus ook twee vergelijkingen nodig. met 30*X = 13*Y en ben je al een heel eind (Je weet dan wat y is uit gedrukt in X). Daarnaast is aX + bY = 1000. Die a en b geef je op. Aangezien je weet wat Y is, kun je de vergelijking schrijven afhankelijk van X. Oplossen en X invullen in eerste verglijking.
Succes!

Acties:
  • 0 Henk 'm!

  • breew
  • Registratie: April 2014
  • Laatst online: 20:55
Wat een leuk probleempje om wat vba-werk op los te laten.

Ik ben uitgegaan van de getallen uit je startpost, Art1 = 4.68, art2 = 10.8.
Mijn code vindt in totaal 7 optimale oplossingen voor de combinatie van beide artikelen (allen met een restwaarde van 0,08 tov 1000)...

Wat doet de code functioneel:
  1. hoog de hoeveelheid van artikel 1 steeds op met 1, net zo lang tot je het maximum van 1000 overschrijdt
  2. bepaal hoeveel van artikel 2 je nodig hebt om zo dicht mogelijk bij 1000 te komen, geven de hoeveelheid van artikel 1 (1.)
  3. bepaal hoe ver je van 1000 af zit met bovenstaande combinatie van artikel 1 en artikel 2 (1. en 2.)
    1. zit je dichter bij 1000 dan de tot nu toe beste oplossing, dan is deze combinatie van 1 en 2 het beste
    2. zit je net zo ver van 1000 als de tot nu toe beste oplossing, voeg de combinatie van 1 en 2 dan toe aan de verzameling met beste oplossingen
  4. ga terug naar 1.
  5. als de grens van 1. is bereikt, toon de verzameling met beste oplossingen
Uitkomst van onderstaande code:
optimel

Stel je het maximum in op 250, dan krijg je het volgende:
optimel250

Probeer eerst te begrijpen wat de code doet, voordat je hem gaat implementeren! Mijn code is slechts één aanvliegroute, die vrij 'dom' door mogelijke combinaties heen akkert..
Het kan allemaal slimmer, en sneller, maar de code is volgens mij op deze manier goed leesbaar.

Visual Basic:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
Option Explicit

Sub OptimaliseerDieHandel()

'uitgangspunten
'   - 2 artikelen (artikel 1 en artikel 2)
'   - eerste artikel (artikel 1) is het lichtst

'declaratie
Dim dblArt1 As Double         'de prijs/gewicht/etc.. van het eerste artikel
Dim dblArt2 As Double         'de prijs/gewicht/etc vna hte tweede artikel
Dim lonMaximum As Long        'het maximum wat je zo dicht mogelijk wilt benaderen
Dim lonAantalArt1 As Long     'aantal van artikel 1
Dim lonAantalArt2 As Long     'aantal van artikel 2
Dim dblSom As Double          'som van de waarde van het totaal van artikel 1 en het totaal van artikel 2
Dim dblRest As Double         'de restwaarde (lonMaximum - dblSom)
Dim dblRestOptimum As Double  'de kleinste tot nu toe bekende restwaarde (het optimum dus)
Dim i As Long                 'lusteller
Dim intOptimum As Integer     'hoeveel optimale combinaties van artikel 1 en 2 zijn bekend
Dim arrArt1() As Long         'array met optimale hoeveelheden van artikel 1
Dim arrArt2() As Long         'array met optimale hoeveelheden van artikel 2
Dim dblTotaal As Double       'waarde van de combinatie artikel 1 en artikel 2
Dim strOptimum As String      'string met het eindantwoord

'initialisatie
dblArt1 = 4.68
dblArt2 = 10.8
lonMaximum = 1000
dblRest = lonMaximum
dblRestOptimum = lonMaximum

'doorloop zo vaak als dat het lichtste artikel in het maximum past (verdeling = 100%-0%)
For i = 1 To Round((lonMaximum / dblArt1) + 1, 0)
  lonAantalArt1 = i
  'bepaal de hoeveelheid van artikel 2, zodat het resultaat zo dicht mogelijk bij lonMaximum ligt.
  lonAantalArt2 = Round((lonMaximum - lonAantalArt1 * dblArt1) / dblArt2, 0)
  'aangezien we ook iets boven het maximum mogen uitkomen, ga na of je het aantal van artikel2 met 1 op kunt hogen voor een beter resultaat
  If Abs(lonMaximum - (lonAantalArt1 * dblArt1 + lonAantalArt2 * dblArt2)) > Abs(lonMaximum - (lonAantalArt1 * dblArt1 + (lonAantalArt2 + 1) * dblArt2)) Then
    'levert dat ene beter resultaat op? dan het aantal van artikel2 met 1 ophogen
    lonAantalArt2 = lonAantalArt2 + 1
  End If
  'rest bepalen. afronden is nodig, omdat we anders tegen floating point errors aanlopen
  dblRest = Round(Abs(lonMaximum - (lonAantalArt1 * dblArt1 + lonAantalArt2 * dblArt2)), 3)
  'vergelijk dblRest nu met de optimale restwaarde (die zo dicht mogelijk bij 0 moet liggen)
  Select Case dblRest
    'als de nieuwe restwaarde kleiner is dat het tot nu toe bekende optimum
    Case Is < dblRestOptimum
      'arrays met optimale combinaties leegmaken
      ReDim arrArt1(0 To 0)
      ReDim arrArt2(0 To 0)
      'arrays vullen met de nieuwe optimale waarden
      arrArt1(0) = lonAantalArt1
      arrArt2(0) = lonAantalArt2
      'aantal optimalewaarden resetten naar 0 (dit is een foefje, om de arrays bij meerdere optima te kunnen vullen)
      intOptimum = 0
      'de huidige rest is de nieuwe optimale waarde
      dblRestOptimum = dblRest
    'als de rest gelijk is aan het tot nu toe bekende optimum
    Case Is = dblRestOptimum
      'er is een extra optimum gevonden, teller met 1 ophogen
      intOptimum = intOptimum + 1
      'arrays met optima uitbreiden naar het aantal optimale combinaties
      ReDim Preserve arrArt1(0 To intOptimum)
      ReDim Preserve arrArt2(0 To intOptimum)
      'laatste entry in de arrays vullen met de nieuwe combinatie optimale waarden
      arrArt1(intOptimum) = lonAantalArt1
      arrArt2(intOptimum) = lonAantalArt2
    Case Else
  End Select
Next i

'weergeven van een venster met de optimale combinaties
'iniktialiseren van de string
strOptimum = "Er zijn " & intOptimum + 1 & " optimale combinaties gevonden!" & vbCrLf & "De optimale restwaarde is: " & dblRestOptimum & vbCrLf & vbCrLf & _
"De optimale combinaties zijn (art1, art2, totaal):" & vbCrLf
'vullen van de string met de optimale waarden
For i = LBound(arrArt1) To UBound(arrArt1)
  dblTotaal = arrArt1(i) * dblArt1 + arrArt2(i) * dblArt2
  strOptimum = strOptimum & arrArt1(i) & ", " & arrArt2(i) & ", (" & dblTotaal & ")" & vbCrLf
Next i
'toon de string
MsgBox (strOptimum)

'KLAAR!!! KOFFIETIJD!!
End Sub

[ Voor 10% gewijzigd door breew op 28-11-2017 12:14 ]


Acties:
  • 0 Henk 'm!

  • breew
  • Registratie: April 2014
  • Laatst online: 20:55
mifa schreef op dinsdag 28 november 2017 @ 09:39:
Dit is toch linear programeren? en je hebt twee onbekenden, dus ook twee vergelijkingen nodig. met 30*X = 13*Y en ben je al een heel eind (Je weet dan wat y is uit gedrukt in X). Daarnaast is aX + bY = 1000. Die a en b geef je op. Aangezien je weet wat Y is, kun je de vergelijking schrijven afhankelijk van X. Oplossen en X invullen in eerste verglijking.
Succes!
Klopt, maar als je alle mogelijke oplossingen wilt hebben, dan wordt het ook met LP een wat lastiger verhaal denk ik. Je gaat moet dan al snel naar Simplex-toepassingen toe.. dat is wat lastiger te automatiseren, zeker als je meer dan 2 variabalen hebt :)
Daarnaast wil TS waarschijnlijk alleen hele artikelen inpakken, waardoor elke optimale oplossing die niet bestaat uit hele getallen voor X en Y, niet voldoet.

Stukje LP-achtergrondinfo voor wie zijn wiskunde weer wat op wil halen:
YouTube: How to Solve a Linear Programming Problem Using the Graphical Method

[ Voor 10% gewijzigd door breew op 28-11-2017 12:21 ]


Acties:
  • 0 Henk 'm!

  • breew
  • Registratie: April 2014
  • Laatst online: 20:55
Mocht je de oplossing uit willen breiden naar meerdere variabelen (artikelen).. dan raad ik je aan om je eens in R te verdiepen.

S:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
require(lpSolve)

# maximaliseer 4.68x1 + 10.8 x2
# 4.68x1 + 10.8x2 <= 1000.1
# 1 * x1 + 0 * x2 >= 0
# 0 * x1 + 1 * x2 >= 0

f.obj <- c(4.68, 10.8)
f.con <- matrix( c(4.68, 10.8, 1, 0, 0, 1), nrow = 3, byrow = TRUE)
f.dir <- c("<=", ">=", ">=")
f.rhs <- c(1000.1, 0, 0)
t <- lp ("max", f.obj, f.con, f.dir, f.rhs, all.int = TRUE)
print(t)
t$solution


levert
> print(t)
Success: the objective function is 1000.08 
> t$solution
[1] 96 51


Aangezien de verhouding tussen x1 en x2 gelijk is aan 468/1080 = 13/30, kun je elke 30x1 inwisselen voor 13x2. Mogelijke andere oplossingen zijn dus:
x1 = 96 - a*30
x2 = 51 + a*13

waarbij 
x1 >= 0 
x2 >= 0
-3 <=a <= 3

Acties:
  • 0 Henk 'm!

Verwijderd

Topicstarter
Bedankt voor jullie tips!
Ik ga er morgen mee aan de slag, hopend dit werkend te krijgen.
Pagina: 1