前段時(shí)間到一家外企上EXCEL課，該企業(yè)很多學(xué)員都遇到了一個(gè)按他們?cè)捳f(shuō)“每個(gè)月都需要花半個(gè)月時(shí)間”去解決的一個(gè)問(wèn)題——分配訂單。

我和EXCEL打交道這么些年，也遇到過(guò)很多類(lèi)似這樣要花大量的時(shí)間才能解決的問(wèn)題，但最后發(fā)現(xiàn)其中多半都是因?yàn)樽畛醯慕鉀Q思路和方式不對(duì)而造成的。

所以我們今天一起來(lái)看看這樣一個(gè)問(wèn)題：利用EXCEL自動(dòng)處理訂單分配。

【問(wèn)題描述】

林老師，有個(gè)問(wèn)題，我們目前需要花大量的手工時(shí)間，把下面這種發(fā)運(yùn)需求計(jì)劃（1）按open訂單數(shù)量（2）的要求生成（3）這樣的分?jǐn)偨Y(jié)果，請(qǐng)問(wèn)excel是否有公式可以直接把需求分?jǐn)偟?/span>open訂單里面呢？謝謝！

1.需求計(jì)劃

3.分?jǐn)偝鰜?lái)的結(jié)果

【林屹老師回復(fù)】

其實(shí)這個(gè)問(wèn)題看起來(lái)很簡(jiǎn)單，似乎就是小學(xué)生算算術(shù)——1個(gè)訂單填滿(mǎn)了又繼續(xù)填充下一個(gè)訂單——但仔細(xì)看看，其實(shí)不是簡(jiǎn)單粗暴地填充訂單那樣簡(jiǎn)單，這填充訂單時(shí)還需要考慮訂單號(hào)（1）和日期（2）的邏輯關(guān)系。所以我想也正因?yàn)槿绱?，這個(gè)兩個(gè)維度的分配難題才困擾了Isa Yi學(xué)員這么久。

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

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

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

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

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

如下圖藍(lán)色部分：

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

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

如下圖紅色部分：

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

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

如下圖綠色部分：

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

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

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

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

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

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

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

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

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

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

這時(shí)就自動(dòng)生成了一個(gè)所有數(shù)據(jù)的一維明細(xì)表Sheet1，如下圖：

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

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

希望上述方法對(duì)美國(guó)普惠公司Isa Yi能有所幫助。如果大家有更好的方法和操作請(qǐng)告訴我，讓我學(xué)習(xí)，共同進(jìn)步。

如果有需要的學(xué)員，可以通過(guò)下面地址或點(diǎn)擊【閱讀原文】進(jìn)行本例文件下載：

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

最后祝大家國(guó)慶愉快！