大家好，我是學(xué) Excel 的小爽鴨~

產(chǎn)品從工廠生產(chǎn)出來之后，需要進(jìn)行裝箱操作。

不同的產(chǎn)品會(huì)有各自對(duì)應(yīng)的箱規(guī)(箱子規(guī)格)，每一個(gè)箱子需裝有規(guī)定數(shù)量的產(chǎn)品。

但是在實(shí)際生產(chǎn)過程中，不可能所有的產(chǎn)品剛好裝滿指定的箱子，大概率會(huì)有剩余。

所以，有時(shí)需要將數(shù)量按照箱規(guī)進(jìn)行一個(gè)個(gè)拆分，如下圖所示。

那么如何將左表拆分成右表的形式呢？

用函數(shù)？用 VBA？用 PQ?。?！

PowerQuery(簡(jiǎn)稱 PQ)是 Excel 自帶的利器，利用它，我們可以獲取數(shù)據(jù)，清洗數(shù)據(jù)，還有整理數(shù)據(jù)。

現(xiàn)在，我們嘗試用 PQ 去解決這個(gè)問題。

由于我們需要將數(shù)量按照指定規(guī)格數(shù)拆成一條條數(shù)據(jù)，最后不足規(guī)格數(shù)量的為一條。

所以只需要根據(jù)取整數(shù)，重復(fù)對(duì)應(yīng)的箱規(guī)數(shù)。

最后與剩余數(shù)進(jìn)行拼接即可。

思路已經(jīng)有了，接下來就開始操作啦 ~

整除數(shù)與取余數(shù)

具體操作 :

我們先將數(shù)據(jù)導(dǎo)入到 PQ 編輯器中。

選中數(shù)據(jù)表，在【數(shù)據(jù)】選項(xiàng)卡下，單擊【來自表格/區(qū)域】;

單擊【確定】按鈕，進(jìn)入 pq 編輯器中。

下面，為了方便大家理解，我采取新增列的方式，一步步帶大家完成這個(gè)問題。

首先我們來獲取數(shù)量與規(guī)格之間相除后的余數(shù)，使用的是 Number.Mod。

• =Number.Mod(被除數(shù) , 除數(shù))
  

在【添加列】選項(xiàng)卡，單擊【自定義列】；

新列名：取模。

自定義列公式為：

• =Number.Mod([數(shù)量],[箱規(guī)])
  

然后獲取數(shù)量與規(guī)格之間相除后的整數(shù)，使用的是 Number.IntergerDivide。

• =Number.IntergerDivide(被除數(shù)，除數(shù)

在【添加列】選項(xiàng)卡，單擊【自定義列】；

新列名：取整重復(fù)。

自定義列公式為：

• =Number.IntegerDivide([數(shù)量],[箱規(guī)])

整除數(shù)和余數(shù)都有了，接下來就是重復(fù)取整數(shù)后拼接啦。

重復(fù)拼接擴(kuò)展

我們要將數(shù)量按照指定規(guī)格數(shù)拆成一條條數(shù)據(jù)，也就是要重復(fù)箱規(guī)數(shù)的數(shù)據(jù)。

對(duì)于重復(fù)列表，我們使用的是 List.Repeat 函數(shù)。

• =List.Repeat({值}，重復(fù)次數(shù))

下面，我們?cè)谌≌蟮墓骄庉嬈?，加上重?fù)函數(shù)。

如下圖所示。

• = Table.AddColumn(已添加自定義,

• ? "取整重復(fù)",

• ? each ?List.Repeat(

• ? ?{[箱規(guī)]},

• ? ?Number.IntegerDivide([數(shù)量],[箱規(guī)])))

  
  

由于余數(shù)為 0 部分，不需要進(jìn)行拼接，所以我們可以用 if 語(yǔ)句進(jìn)行判斷。

在【添加列】選項(xiàng)卡，單擊【自定義列】；

新列名：拼接。

自定義列公式為：

• if [取模]=0

• then [取整重復(fù)]

• else [取整重復(fù)]&{[取模]}
  

這時(shí)可以看到，我們已經(jīng)將數(shù)據(jù)拼接好了。

現(xiàn)在，只需要把其他不需要的列刪除掉就可以。

刪除其他列，保留【產(chǎn)品名稱】和【拼接】列。

選擇【產(chǎn)品名稱】列，按住【Ctrl 鍵】，再選擇【拼接】列，鼠標(biāo)右鍵選擇【刪除其他列】。

選擇擴(kuò)展按鈕，選擇 「擴(kuò)展到行」。

到這里，我們就完成了。

是不是很簡(jiǎn)單~

延伸一下

如果大家掌握到一定程度，也可以不選擇通過自定義列的方式一步步完成效果。直接寫一個(gè) m 函數(shù)公式也可以。

• = Table.ExpandListColumn(

• ?Table.AddColumn(

• ? 源,"a",

• ? each

• ? let

• ? ? m= Number.Mod([數(shù)量],[箱規(guī)]),

• ? ? n=List.Repeat({[箱規(guī)]},Number.IntegerDivide([數(shù)量],[箱規(guī)]))

• ? in

• ? ? if m=0 then n else n&{m})

• ? ? [[產(chǎn)品名稱],[a]],

• ? "a")

  
  

當(dāng)然方法不是只有這種，比如我們可以用遞歸，List.Accumulate 等等方法。掌握最基礎(chǔ)的方法就行啦。

總結(jié)一下


本文講解的是，將數(shù)量根據(jù)指定的規(guī)格進(jìn)行擴(kuò)展的 PQ 做法。

涉及三個(gè)基礎(chǔ) m 函數(shù)：

? Number.Mod(取模函數(shù))，類似于 Excel 的 mod 函數(shù)。

? Number.IntergerDivide(取整函數(shù))，類似于 Excel 的 int 函數(shù)。

? List.Repeat 列表重復(fù)函數(shù)。

? if ……then……else 語(yǔ)句，跟 if 函數(shù)一樣是判斷作用，不過語(yǔ)句和函數(shù)概念不同。

今天講解就到這里就結(jié)束啦～

文中提到的三個(gè) m 函數(shù)大家掌握了嘛，是不是覺得 PQ 中的 m 函數(shù)其實(shí)也不是這么難？

到目前為止，讀到這里的你，腦子過一下，認(rèn)識(shí)了哪些 m 函數(shù)了呢，留言區(qū)一起聊聊吧~

如果你在工作中遇到問題想有老師指點(diǎn)，想學(xué)習(xí)更多 Excel 技巧，想擁有更多練手機(jī)會(huì)……

歡迎報(bào)名我們秋葉家的《3 天 Excel 集訓(xùn)營(yíng)》，這里有老師直播+錄播教學(xué)，有助教群內(nèi) 1 對(duì) 1 答疑，還有同學(xué)一起交流進(jìn)步！

3 天時(shí)間，每天 30 分鐘左右，你也有可能成為 Excel 高手！

3 天 Excel 集訓(xùn)營(yíng)

提升效率，助你準(zhǔn)時(shí)下班

數(shù)據(jù)可視化，讓領(lǐng)導(dǎo)刮目相看

秋葉 Excel 讀者專享

官網(wǎng)價(jià)?99?

現(xiàn)在僅需 1 元

點(diǎn)下方圖片掃碼即可報(bào)名

??????

↑↑↑

現(xiàn)在報(bào)名免費(fèi)獲取

307 個(gè)函數(shù)清單

35 個(gè)常用函數(shù)說明

……

*廣告