前段時間到一家外企上EXCEL課，該企業(yè)很多學員都遇到了一個按他們話說“每個月都需要花半個月時間”去解決的一個問題——分配訂單。

我和EXCEL打交道這么些年，也遇到過很多類似這樣要花大量的時間才能解決的問題，但最后發(fā)現(xiàn)其中多半都是因為最初的解決思路和方式不對而造成的。

所以我們今天一起來看看這樣一個問題：利用EXCEL自動處理訂單分配。

【問題描述】

林老師，有個問題，我們目前需要花大量的手工時間，把下面這種發(fā)運需求計劃（1）按open訂單數(shù)量（2）的要求生成（3）這樣的分攤結(jié)果，請問excel是否有公式可以直接把需求分攤到open訂單里面呢？謝謝！

1.需求計劃

3.分攤出來的結(jié)果

【林屹老師回復】

其實這個問題看起來很簡單，似乎就是小學生算算術(shù)——1個訂單填滿了又繼續(xù)填充下一個訂單——但仔細看看，其實不是簡單粗暴地填充訂單那樣簡單，這填充訂單時還需要考慮訂單號（1）和日期（2）的邏輯關(guān)系。所以我想也正因為如此，這個兩個維度的分配難題才困擾了Isa Yi學員這么久。

其實我們換個思維，要讓表（1）和表（2）變得有關(guān)系，最好的辦法是建立一張“二維表”，把這兩個維度都包含進去，如下圖所示：

將數(shù)量放在二維表外側(cè)，時間和訂單號放在二維表內(nèi)側(cè)，這是方便稍后進行的帶表頭的數(shù)據(jù)透視操作。

接下來，我們要利用公式進行數(shù)據(jù)自動填充，為了避免循環(huán)引用，這里用3個公式進行填充——

【公式一】在表格的第一個單元格C3中錄入公式：

=MAX(MIN($A3,C$1),0)

如下圖藍色部分：

【公式二】在表格的第一行第二個單元格D3錄入公式并向右填充：

=MAX(MIN($A3-SUM($C3:C3),D$1),0)

如下圖紅色部分：

【公式三】在表格的第二行第一個單元格C4錄入公式并向下向右填充：

=IF(C$1>SUM(C$3:C3),MAX(MIN($A4-SUM(B4:$C4),C$1-SUM(C$3:C3)),0),0)

如下圖綠色部分：

這3個公式具體函數(shù)語法我就不贅述了，都是很簡單的函數(shù)語法，我給大家大致翻譯一下：將A列的訂單號數(shù)據(jù)按第1行日期數(shù)據(jù)要求填充至右側(cè)表格中，當填滿一個日期就繼續(xù)向右填充，若填不滿則用下一個訂單號數(shù)據(jù)繼續(xù)填充。

這里用三個部分的函數(shù)進行分別填充，是為了不循環(huán)引用數(shù)據(jù)。如果大家有更好的更為方便的公式，也請告訴我，謝謝！

另外說一句：這個二維表的數(shù)據(jù)區(qū)域如果要擴大，可以直接拖動紅色和綠色部分進行公式填充。

接下來，我們要把這張二維表變成表（3）那樣的一維表，可以利用數(shù)據(jù)透視表幫助我們實現(xiàn)，具體步驟如下：

首先，在此表中任一單元格依次按下Alt、D、P三鍵，彈出“數(shù)據(jù)透視表向?qū)А襟E1”窗口，在選擇“多重合并計算數(shù)據(jù)區(qū)域”，并繼續(xù)點擊“下一步”，如下圖所示：

在“數(shù)據(jù)透視表向?qū)А襟E2a”中選擇“創(chuàng)建單頁字段”，并繼續(xù)下一步，如下圖：

在“數(shù)據(jù)透視表向?qū)А襟E2b”中的選定區(qū)域引用二維表中B2:F5區(qū)域，注意不要選數(shù)字區(qū)域，繼續(xù)點擊下一步。

最后選擇數(shù)據(jù)透視表顯示位置在“新工作表”，點擊“完成”。

通過向?qū)陆ǖ臄?shù)據(jù)透視表如下圖所示：

雙擊F8單元格的最終總計的合計數(shù)：303

這時就自動生成了一個所有數(shù)據(jù)的一維明細表Sheet1，如下圖：

最后將此一維明細數(shù)據(jù)表進行優(yōu)化操作，刪除0值和多余列即可。

這個方法就是將兩個要求建立關(guān)聯(lián)的表格進行組合，然后在二維表下進行公式填充，最后通過數(shù)據(jù)透視表使之變成我們需要的二維表。

希望上述方法對美國普惠公司Isa Yi能有所幫助。如果大家有更好的方法和操作請告訴我，讓我學習，共同進步。

如果有需要的學員，可以通過下面地址或點擊【閱讀原文】進行本例文件下載：

https://yunpan.cn/cHvBwsxaN3GDC （提取碼：8724）

最后祝大家國慶愉快！