恭請 Excel 高手 (也許也必須要是數學高手...) 來挑戰

achen

想了很久還是決定不把這篇貼在電腦篇, 因為和軟硬體實在沒關係. 我已經弄了三個小時還是找不出方法求得我想要的答案, 我又花了半小時考慮應該怎麼把這個問題詳細的表達出來. 最後發現應該 "據實呈現" 的概念比較清楚. 所以請先聽我簡單敘述.

俾公司跟手機商的合約是 "共享分鐘數", 我們旗下有 350 條線, 每條線基本月費 $39.99, 每條線貢獻 450 分鐘到 "公用分鐘數" .所以總計是 350 x $39.99= $13996.50 的月費, 全公司可以有 350 x 450 = 157500 分鐘可以講. 基本上我已經把上面的敘述簡單化了, 另外還有一些額外的折扣和花費我就先不提.

他們最近更改了 plan 的內容, 多出了 A, D, E, F 四種選擇:

A plan: 0 分鐘: $19.99
B plan: 450 分鐘: $39.99
C plan: 900 分鐘: $59.99
D plan: 1350 分鐘: $79.99
E plan: 2000 分鐘: $99.99
F plan: 3000 分鐘: $149.99

我們不再被強迫每條線一定要買分鐘數了, 我們可以有部分門號選擇沒有分鐘數, 但是便宜一半的 (A) plan.  唯一的要求是, (A) plan 的數目不能超過總門號數的一半. 我了解這個規定的理由, 因為他們必須確保他們的最低利益. 因為在這個規定下, 有 350 條線的話, 我最低花費必須是:

一半放 A plan: 175 條 x $19.99 = $3498.25, 貢獻 0 分鐘
一半放 B plan:  175 條 x $39.99 = $6998.25, 貢獻 78750 分鐘
------------------------------------------------------------------------
總計: $10496.50,  公用分鐘數= 78750 分鐘

====================================================================================
這看起來毫無疑問的比原來的 $13996.50 便宜很多, 但是問題來了, 78750 分鐘不夠我們用啊!! 根據評估前半年的帳單, 我必須買 140000 分鐘才可以沒有擔心超時費的後顧之憂, 於是我做了這個 excel 表 (如附件):

B2= 門號數
B3= 所需通話分鐘數最低目標

Current Plan 表示我們現在的合約, New Plan 表示新合約
藍色表示分鐘數, 綠色表示 $$ 金額.

B 欄代表擺在每個不同 plan 下面的門號數量, 總合應該等於 350
C 欄是每個 plan 貢獻的通話分鐘數
D 欄是每個 plan 的價錢
E 欄是每月所購得總通話分鐘數
F 欄是每月月費統計

目標:
我欲求得 B17~B22 的最佳組合應該是多少, 以達成以下兩個條件:

1. E23 (每個月可購得的總通話分鐘數) 不小於 B3 (每個月我估計所需的通話分鐘數)
2. F23 值越小越好

遇到的問題
我連調整  B17~B22 的數字, 嘗試把 E23 弄到接近 140000 都有問題了, 更別說去找出最省錢的組合了....

[ 本文最後由 achen 於 2008-5-1 21:50 編輯 ]

leox

減化一下數學模型
A+B+C+D+E+F=350
450B+900C+1350D+2000E+3000F>=14000
求下式
(下式把.99進位)
20A+40B+60C+80D+100E+1500F為最小

暫時沒空想
先把式子列出來

jedij

1. 先弄個苦工用Excel檔。（如附件，我只輸入幾行。）
2. 每分鐘費用是E、F相同最低($0.05)，所以當然取E方案(滿足更多人數要求)
3. 以E方案算出貴公司最少費用滿足分鐘需求數。(滿足最低費用條件)
4. 如果人數不足再以移至D方案充人頭數。(滿足電話公司條件)
    苦工用檔這時候可以用來填填看。

以上

achen

jedij,

您的建議不錯, 感謝.

如果公式搞不定的話, 搞不好花時間把所有可能組合列出來, 將是最終的一個方法  (不知道會有多少可能性就是了..)
但是您的檔案裡面有一個小小的 miss..  $19.99 那一行最多只能放 175 個人頭.

zgenius

我用很簡單的邏輯跑了一個小程式，得到
a:175
b:129
c:12
d:12
e:11
f:11

總分鐘 ：140050
總價：      13086.5

我也不知道是不是最佳解，
但是我要下班了，所以，先走了，掰～

[ 本文最後由 zgenius 於 2008-5-2 09:31 編輯 ]

jedij

原文由 achen 於 2008-5-2 07:59 發表
jedij,
您的建議不錯, 感謝.
如果公式搞不定的話, 搞不好花時間把所有可能組合列出來, 將是最終的一個方法  (不知道會有多少可能性就是了..)
但是您的檔案裡面有一個小小的 miss..  $19.99 那一行最多只 ...

歹勢！沒有仔細看您的內文…

我的邏輯很簡單：
先找到可能的最低價設定，
再逐步更改設定加價至其他設定找到最佳解。
所以應該不用全面性的填空所有可能組合，
只要以「最低價設定」為中心作尋找最佳解。

我倒是很好奇zgenius 兄的計算方法，
看來不像我的這麼簡陋可是這麼快就有解。

[ 本文最後由 jedij 於 2008-5-2 08:35 編輯 ]

Donnie

是這樣嗎？

MIN = 19.99* x1  + 39.99* x2 + 59.99 *x3 + 79.99* x4 + 99.99 *x5 + 149.99 *x6 ;

450 *x2 + 900* x3 + 1350* x4 + 2000* x5 + 3000 *x6 >= 157500;
x1 <= 175;
x1 + x2 + x3 + x4 + x5 + x6 = 350;


解為  (175, 124, 0, 0, 51, 0)

157800 分鐘
$13,556.50    省 $440...

[ 本文最後由 Donnie 於 2008-5-2 08:47 編輯 ]

jedij

基於Donnie兄數字作展開：

175, 135, 0, 0, 40, 0
$12896.5, 140750 min

我前面說錯了，慚愧…

[ 本文最後由 jedij 於 2008-5-2 09:00 編輯 ]

Donnie

Sorry, 看錯分鐘數

MIN = 19.99* x1  + 39.99* x2 + 59.99 *x3 + 79.99* x4 + 99.99 *x5 + 149.99 *x6 ;
450 *x2 + 900* x3 + 1350* x4 + 2000* x5 + 3000 *x6 >= 140000;
x1 <= 175;
x1 + x2 + x3 + x4 + x5 + x6 = 350;

解為   (173, 138, 0, 0, 39, 0)
Total minutes = 140100
Price =  $ 12,876.50

achen

原文由 Donnie 於 2008-5-1 16:57 發表
MIN = 19.99* x1  + 39.99* x2 + 59.99 *x3 + 79.99* x4 + 99.99 *x5 + 149.99 *x6 ;
450 *x2 + 900* x3 + 1350* x4 + 2000* x5 + 3000 *x6 >= 140000;
x1 <= 175;
x1 + x2 + x3 + x4 + x5 + x6 = 350;

解為   (173, 138, 0, 0, 39, 0)
Total minutes = 140100
Price =  $ 12,876.50

太...強..了... 居然可以算出低於 $13000 的組合 !!

因為 350 和 140000 這兩個數字有可能每幾個月就會變更

敢問是否能提供詳細的運算過程?

krisher

用程式跑窮舉法, 可能比較容易解.
但是就是慢了一點.

mfhsieh

線性規劃

請參考：http://www.math.sinica.edu.tw/math_media/d171/17104.pdf

--

或者直接翻出 matlab/mathematica 求解。

sjackwu

原文由 mfhsieh 於 2008-5-2 13:53 發表
線性規劃

請參考：http://www.math.sinica.edu.tw/math_media/d171/17104.pdf

--

或者直接翻出 matlab/mathematica 求解。

從一看到問題就覺得「這個問題我以前學過」
看到「線性規劃」才覺得恍然大悟[洋蔥006]

線性規劃的話，excel  也可以喔

Excel 「規劃求解」範例一
http://yes.nctu.edu.tw/Lecture/P ... ple/Exam1/Index.htm

等回家再看看有沒有辦法幫 achen 把 excel 檔弄出來，看範例照做應該不會太難。。。吧？
太久沒碰連名字都忘了[洋蔥shy]

Donnie

原文由 achen 於 2008-5-2 12:41 發表


太...強..了... 居然可以算出低於 $13000 的組合 !!

因為 350 和 140000 這兩個數字有可能每幾個月就會變更

敢問是否能提供詳細的運算過程?

我是把下面的數學式輸入到 LINGO 裡面去解的，免費的線性規劃程式的話也可以用 lp_solve，
但是語法稍微不一樣。  用 Excel 應該也可以，但是感覺設定挺麻煩的。 ^^a

----
MIN = 19.99* x1  + 39.99* x2 + 59.99 *x3 + 79.99* x4 + 99.99 *x5 + 149.99 *x6 ;
450 *x2 + 900* x3 + 1350* x4 + 2000* x5 + 3000 *x6 >= 140000;
x1 <= 175;
x1 + x2 + x3 + x4 + x5 + x6 = 350;
@GIN(x1);
@GIN(x2);
@GIN(x3);
@GIN(x4);
@GIN(x5);
@GIN(x6);

sjackwu

原文由 Donnie 於 2008-5-2 08:57 發表
Sorry, 看錯分鐘數

MIN = 19.99* x1  + 39.99* x2 + 59.99 *x3 + 79.99* x4 + 99.99 *x5 + 149.99 *x6 ;
450 *x2 + 900* x3 + 1350* x4 + 2000* x5 + 3000 *x6 >= 140000;
x1  

參考 Donnie 的公式，跟 http://yes.nctu.edu.tw/Lecture/P ... ple/Exam1/Index.htm 的說明，也把（近似）最佳解弄出來了，不過算出來的值是 12896.5, 可能還要再微調計算方法才能得到 Donnie 的解。

附上 excel 檔，先照 nctu 上的範例做一次，再看這個檔案，應該就知道該怎麼把其他的條件弄進去。

BTW, 規劃求解的英文是 solver，竟然試到第三次才找到正確的 addon...[洋蔥victory]

sjackwu

原文由 Donnie 於 2008-5-2 18:29 發表


我是把下面的數學式輸入到 LINGO 裡面去解的，免費的線性規劃程式的話也可以用 lp_solve，
但是語法稍微不一樣。  用 Excel 應該也可以，但是感覺設定挺麻煩的。 ^^a

Lingo... 好熟悉的名字啊。。。當年也是靠這套軟體才能畢業的。。。
歲月催人啊～～～[洋蔥sad]

achen

原文由 Taiwanese 於 2008-5-2 11:01 發表
sorry, 問一下

無法用 family plan?

corp account 沒有所謂 "family plan" 這東西

但是我們的 shared pool minute 意思和 family plan 的共享通話數是一樣的

sjackwu

線性規劃可以用在一些複雜的情況，如：選擇手機費率的最佳方案上
只要你寫的出像前面的式子，就能用線性規劃求解
excel能計算的變數可能不會太多（？），不過如果像lingo或是其他專業的數學軟體，連二次式都可以搞定。。。[洋蔥sweat]

unicorn_khh

等等, 這個不是應該是accountant跟controller該作的規劃嗎? 什麼時候輪到MIS也來參一腳, 難道真的要包山包海包生小孩?

小酒蟲

依我的經驗（應該和 achen 差不多）而言，accounting 只負責把支票寄給電話公司。