運用Excel的規(guī)劃求解工具解決最佳人員分配問題

趙旭娟

摘要：Excel是用戶最熟悉和便捷的辦公應(yīng)用電子表格軟件，具備強(qiáng)大的數(shù)據(jù)處理、數(shù)據(jù)分析等功能。實際管理中遇到的問題紛繁復(fù)雜、各式各樣，既消耗管理人員的大量精力，也困擾管理人員做出正確決策。如何使用便捷、高效地工作方式解決管理中的優(yōu)化問題，考驗著管理人員的素質(zhì)和技能。該文以求解某企業(yè)特定崗位人員需求為例，提出了解決問題的基本思路、方法及步驟，能促使管理者有效解決實際工作中遇到的人員分配問題的優(yōu)化方案。

關(guān)鍵詞：Excel；規(guī)劃求解；數(shù)學(xué)模型；線性規(guī)劃

中圖分類號：TP317 文獻(xiàn)標(biāo)識碼：A 文章編號：1009-3044（2018）16-0270-03

1 背景

隨著計算機(jī)技術(shù)普及和信息化手段的運用，辦公軟件應(yīng)用成為現(xiàn)代人工作、學(xué)習(xí)、生活的必備工具之一。而Microsoft公司（微軟）開發(fā)的Excel可謂是當(dāng)前覆蓋面最廣的電子表格軟件，其在各大中小學(xué)校學(xué)習(xí)普及率和在各大類企事業(yè)單位使用率遠(yuǎn)遠(yuǎn)高于其他同類軟件。以浙江省為例，無論是省高校計算機(jī)等級考試中的一級Windows、二級辦公軟件高級應(yīng)用技術(shù)，還是計算機(jī)職稱考試，均是基于微軟公司辦公軟件而開發(fā)相應(yīng)考試平臺。

追其根本，原因在于該公司開發(fā)的Excel具備強(qiáng)大數(shù)據(jù)處理、數(shù)據(jù)分析功能，能方便、高效地為人們解決各類實際問題。同時，該公司開發(fā)的Excel能加載規(guī)劃求解工具，使用Frontline Systems公司的John Watson 和Dan Flastra 提供的有節(jié)變量單純性法和分值界定法的實現(xiàn)算法1。該工具具有易用、便捷、高效、準(zhǔn)確率高等特點，能有效解決線性規(guī)劃問題。

2 求解問題的基本思路

管理科學(xué)派（即數(shù)量學(xué)派）的學(xué)者們認(rèn)為，管理是制定和運用數(shù)學(xué)模型和程序的系統(tǒng)，應(yīng)用科學(xué)方法解決管理實際問題，通過各職能之間的互相關(guān)系分析，再用數(shù)學(xué)符號和公式來表達(dá)計劃、組織、控制、決策等管理過程中的合乎邏輯的程序，求出最優(yōu)的解答，以達(dá)到企業(yè)的管理目標(biāo)。其主張應(yīng)用計算機(jī)管理和技術(shù)，提高管理的經(jīng)濟(jì)效益。

該文以快速解決某企業(yè)特定同崗位所需人員問題為例。在求解過程當(dāng)中，以管理科學(xué)派思想為指引，首先基于實際問題本身開展認(rèn)真分析，再建立運籌學(xué)知識中所提及的線性規(guī)劃數(shù)學(xué)模型，并將該模型轉(zhuǎn)化到Excel中進(jìn)行表達(dá)，然后利用規(guī)劃求解工具，方便、快捷地求出最佳答案（也即最優(yōu)解）。如此，方可為企業(yè)管理人員最終決策提出依據(jù)。求解管理問題的基礎(chǔ)思路框架圖如下圖1所示。

3 案例與問題闡述

該文案例：解決某企業(yè)特定同崗位所需人員問題

具體為：某企業(yè)的某個特定崗位，其所需要的崗位上班人數(shù)具體如下表1所示。為確保在該企業(yè)工作的人員，符合“每周工作五天，連續(xù)休息兩天”的規(guī)定，現(xiàn)需要進(jìn)行合理安排，以滿足該崗位每天需求的最少人數(shù)。請問，企業(yè)管理人員應(yīng)如何決策更合理，最佳方案應(yīng)是什么？

4.1 概念界定

所謂線性規(guī)劃，指的是一種解決在一定約束條件下追求最大或最小的線性目標(biāo)函數(shù)的辦法2。同時，基本的線性規(guī)劃數(shù)學(xué)模型建立一般分為三步：第一步，假設(shè)決策變量；第二步，建立目標(biāo)函數(shù)；第三步，尋找約束條件。

4.2 問題分析

要符合工作人員每周連續(xù)休息兩天的規(guī)定，同時要滿足該崗位最少需求人數(shù)，對企業(yè)管理者來說，實際上就是要求得該崗位最少的總?cè)藬?shù)，以及求得每天都要開始上班的人數(shù)。并且，工作人員每周連續(xù)休息兩天，也就意味著每個人都是工作五天，連續(xù)休息兩天。因此，只要能求出連續(xù)休息兩天的崗位人數(shù)，也就可以計算出崗位總?cè)藬?shù)。

4.3 模型建立

結(jié)合線性規(guī)劃模型建立步驟和問題分析，進(jìn)而得出，該案例的數(shù)學(xué)模型建立過程，具體如下：

第一步，假設(shè)決策變量

假定[Xi]為周[i]開始休息的人數(shù)，[i=1，2，3，4，5，6，7]。把連續(xù)休息兩天的人員按照開始休息時間分成為7類，各類的人數(shù)分別為[X1]，[X2]，[X3]，[X4]，[X5]，[X6]，[X7]。

第二步，建立目標(biāo)函數(shù)

總崗位人數(shù)[Z]最少值MIN，就是[X1]，[X2]，[X3]，[X4]，[X5]，[X6]，[X7]之和的最小值。

目標(biāo)函數(shù)即為：[MINZ]=[X1]+[X2]+[X3]+[X4]+[X5]+[X6]+[X7]

第三步，尋找約束條件（即模型建立）

根據(jù)崗位需求人數(shù)統(tǒng)計表，可以得到如下的約束條件表達(dá)式，也即數(shù)學(xué)模型：

[Xi≥0，Xi是整數(shù)，i= 1，2，3，4，5，6，7]

從上述建立的模型可知，該案例的問題實際上就是純整數(shù)規(guī)劃問題，也屬線性規(guī)劃問題之一。實際當(dāng)中，數(shù)學(xué)模型的線性規(guī)劃求解方法可以有圖解法、Excel求解法、Python算法等?？紤]到Excel的普及程度高、適用性強(qiáng)、易用性好、準(zhǔn)確率高等特點，該文將采用Excel求解法，就是運用Excel電子表格軟件對上述模型進(jìn)行轉(zhuǎn)化，并用規(guī)劃求解工具，得出最優(yōu)解。

5 模型轉(zhuǎn)化與Excel求解

5.1 創(chuàng)建文件及表信息

即Excel中轉(zhuǎn)化數(shù)學(xué)模型信息。根據(jù)問題分析及模型建立步驟，啟動Excel程序，新建文件，在表中按下圖2輸入相應(yīng)信息。所輸入的信息，要對目標(biāo)函數(shù)、決策變量、約束條件等進(jìn)行合理布局，便于后期通過公式計算，利用規(guī)劃求解工具得出結(jié)果，同時便于對相應(yīng)原始數(shù)據(jù)進(jìn)行修改。

打開Excel程序后，在“文件|選項|加載項”對話框中，選擇下方的“管理-Excel加載項”右側(cè)的“轉(zhuǎn)到……”按鈕，單擊，選擇“規(guī)劃求解加載項”，確定。若Excel程序中曾加載過規(guī)劃求解工具，則此步驟可忽略。

5.3 設(shè)置公式和函數(shù)

為求崗位總?cè)藬?shù)，B14單元格需要用到的函數(shù)為SUM；各約束條件對應(yīng)單元格（I8～I(xiàn)14）應(yīng)按照約束條件要求設(shè)置相應(yīng)的公式。具體公式和函數(shù)設(shè)置參照下表2進(jìn)行。

5.4 運用規(guī)劃求解工具解題

將光標(biāo)放任意位置，點擊“數(shù)據(jù)”工具欄上的“規(guī)劃求解”按鈕。并對照數(shù)學(xué)模型和前期單元格設(shè)置信息，將目標(biāo)函數(shù)、決策變量、約束條件進(jìn)行依次設(shè)置。①目標(biāo)值選擇B2、最小值；②可變單元格（即為決策變量）選擇B5：H5；③添加約束條件7個，對應(yīng)模型中約束條件的不等式，選擇對應(yīng)單元格；④設(shè)置選擇求解方法為“單純線性規(guī)劃”。具體參數(shù)設(shè)置結(jié)果參照下圖3所示。⑤完成后，單擊“求解”按鈕。在跳出對話框中選擇“保留規(guī)劃求解的解”，如下圖4所示，再單擊確定。表中立即顯示了最佳求解結(jié)果，如下圖5所示。

由圖5可知，該案例問題的最優(yōu)方案已經(jīng)得出。作為該企業(yè)管理人員，選擇最佳的解決方案就是：應(yīng)最少安排33人在該崗位，其中周一至周日開始上班人數(shù)依次為10、4、7、3、0、2、7，每周一至周日在崗人數(shù)依次為24、16、19、22、23、30、31。本例中，若對崗位最少人數(shù)進(jìn)行修改，只需要重復(fù)“5.4運用規(guī)劃求解工具解題”步驟，即可重新獲得最新結(jié)果，簡單、方便、快捷。

6 總結(jié)

企業(yè)管理人員在管理運籌過程中，最優(yōu)方案的決策問題，體現(xiàn)了管理者的水平和能力。與該案例相近的同類管理問題，可借鑒該文提出的求解最佳人員分配問題的基本思路和方法，以建立數(shù)學(xué)模型為基礎(chǔ)，通過Excel合理布局模型中的各要素信息，并通過設(shè)置相應(yīng)的公式和函數(shù)，再運用Excel的規(guī)劃求解工具，求出最優(yōu)解。如此便可為企業(yè)管理人員解決實際需求，提高管理效率，促進(jìn)企業(yè)目標(biāo)達(dá)成。

參考文獻(xiàn)：

[1] 楊秋輝， 游志勝， 攀鴻， 等. 用線性規(guī)劃求解工程混合料最優(yōu)配料問題[J]. 四川大學(xué)學(xué)報： 自然科學(xué)版， 2005（1）：198-201.

[2] 陳芳. 基于Excel的線性規(guī)劃求解模型設(shè)計初探[J]. 科技展望， 2017（9）： 176-177.