(Excel) 庫存量計算數種產品產出量所有排列組合

神奇珍妮特

神奇珍妮特
個人積分：0分
文章編號：66270164

0分

樓主

2017-10-31 14:12

請教高手:

如下面照片所示

三個產品: A/B/C
物料: 991 / 992 / 993
如何用物料現有庫存來計算A/B/C 三個產品,可產出數量的所有組合?
感謝!!

(Excel) 庫存量計算數種產品產出量所有排列組合

2017-10-31 14:12 發佈

文章關鍵字 excel 產品產出量庫存量排列組合

slash410

slash410
個人積分：5269分
文章編號：66272043

5269分

2樓

2017-10-31 16:53

這題很有趣

BOM表硬要靠Excel函數解的話應該很硬吧? (通常應該是VBA)

我只想到靠樞紐展出所有組合,
然後靠sumproduct算出所有組合的零件數
再用判斷式去呈現..不過這樣子做應該太笨了.. 應該有更好的方法, 標記等解答.

snare

snare
個人積分：3041分
文章編號：66562552

3041分

3樓

2017-11-23 22:57

神奇珍妮特 wrote:
如何用物料現有庫存來計算A/B/C 三個產品,可產出數量的所有組合?...(恕刪)

這屬於管理數學中的線性規劃
為了回答，我還翻出以前的課本，稍微複習一下 sorry

基本上呢，這種問題是沒有在求“所有組合”的
您會不會搞錯了??
都是求可生產最大數量、最佳解
如果有加上售價就是求最大利潤
更複雜一點的還會加上折舊、運輸、機器時間…等等
再加上人因工程(人的反應、手拿零件的時間、身體轉動的角度…之類的)
看起來就像天書一樣，當初我可是學的很痛苦 orz

只求最大產量(利潤)的話，excel 內建功能就可以辦到

一、先建立公式
e2=SUMPRODUCT(B2:D2,B$5:D$5)
e3=SUMPRODUCT(B3:D3,B$5:D$5)
e4=SUMPRODUCT(B4:D4,B$5:D$5)
b7=sum(B5:D5)

二、開啟“規劃求解”(怎麼開請google)
照圖片中的規則輸入=>按求解(這種簡單的問題，求解方法隨便選一個都可以解)
產量那行維持空白就可以，求解後會自動填入

三、如果要限制產品數量，例如：c產品一定要產出3個或以上
新增一個規則就可以了

另外，要注意的是，最佳解不是唯一的答案，只是所有可能解之一

snare

snare
個人積分：3041分
文章編號：66562563

3041分

4樓

2017-11-23 22:59

slash410 wrote:
然後靠sumproduct算出所有組合的零件數
再用判斷式去呈現.....(恕刪)

您說對了一半

snare

snare
個人積分：3041分
文章編號：66583697

3041分

5樓

2017-11-25 23:38

如果真的要看所有組合，這裡補上列出所有排列組合的vba方式

為了方便說明，使用公式+vba，當然您要把公式寫入vba也可以

一、公式部份，先算出每項產品的最大產量
只要把每項庫存原料/使用原料，再取最小值(需為整數，因為產品是沒有1.1 or 1.6個)
就是單項產品最大產量，(圖片中的 a6:d10)

二、程式部份，使用每項產品的最大產量當作要跑的迴圈次數
當3個條件都成立時，就是可能的產量組合方式，在f、g、h欄列出
(3個條件就是SUMPRODUCT那3個公式)

'============================================

Sub test()

Columns("F:H").ClearContents
Range("f1:h1") = Array("a", "b", "c")

max_a = Range("b10")
max_b = Range("c10")
max_c = Range("d10")
r = 1

For i = 0 To max_a
For j = 0 To max_b
For k = 0 To max_c

p1 = i * Range("b2") + j * Range("c2") + k * Range("d2")
p2 = i * Range("b3") + j * Range("c3") + k * Range("d3")
p3 = i * Range("b4") + j * Range("c4") + k * Range("d4")

If p1 <= Range("a2") And p2 <= Range("a3") And p3 <= Range("a4") Then
r = r + 1
Cells(r, 6) = i
Cells(r, 7) = j
Cells(r, 8) = k
End If

Next k
Next j
Next i

MsgBox "所有可能組合" & r - 1 & "種", vbOKOnly, "report"

End Sub

'============================================