粉絲求助SOS：如何實(shí)現(xiàn)動(dòng)態(tài)[裝箱]計(jì)算？將不同型號(hào)的產(chǎn)品按50個(gè)一箱進(jìn)行分裝。

微軟Office LTSC 2021專業(yè)增強(qiáng)版 簡(jiǎn)體中文批量許可版 2024年09月更新

• 類型：辦公軟件
• 大?。?/span>2.2GB
• 語言：簡(jiǎn)體中文
• 時(shí)間：2024-09-12

查看詳情

WPS Office 2025 V12.1.0.21915 官方最新正式版

• 類型：辦公軟件
• 大小：260MB
• 語言：簡(jiǎn)體中文
• 時(shí)間：2025-07-03

查看詳情

如下圖所示：

• A列是型號(hào)：A、B、C、D
• B列是數(shù)量：39、72、117、21

裝箱要求是：

每個(gè)型號(hào)50個(gè)裝一箱，不能混裝。多出50個(gè)的部分需要裝在下一箱中，以此類推。最終結(jié)果顯示在在D1:E8區(qū)域。

下面顯示了每個(gè)箱子的明細(xì)：

• A型號(hào)：1箱39個(gè)
• B型號(hào)：1箱50個(gè)和1箱22個(gè)
• C型號(hào)：2箱各50個(gè)和1箱17個(gè)
• D型號(hào)：1箱21個(gè)

這個(gè)問題差點(diǎn)讓我崩潰：ddb+text函數(shù)組合，雙劍合璧，實(shí)現(xiàn)動(dòng)態(tài)[裝箱]計(jì)算。

第一步：生成可能箱子序列

可以這樣寫公式：

=COLUMN(A:D)

COLUMN(A:D) 返回列號(hào)數(shù)組 {1,2,3,4}（A列=1, D列=4）

繼續(xù)完善公式：

=COLUMN(A:D)*50

乘以50后得到：{50,100,150,200}。這代表可能的累積裝箱點(diǎn)（每50個(gè)一箱），最多4箱（200個(gè)），覆蓋了最大數(shù)量117的需求。

第二步：巧用DDB函數(shù)計(jì)算各箱數(shù)量

我們繼續(xù)完善公式：

=DDB(COLUMN(A:D)*50,B2:B5,1,1)

這里使用了DDB折舊函數(shù)，但被巧妙轉(zhuǎn)化為了裝箱計(jì)算。計(jì)算每個(gè)型號(hào)在每箱的“剩余量”。

DDB參數(shù)解析：

• cost: {50,100,150,200}（每個(gè)可能箱子的累積值）
• salvage: B2:B5，即 {39;72;117;21}（每個(gè)型號(hào)的總數(shù)量）
• life: 1（表示資產(chǎn)壽命只有1期）
• period: 1（計(jì)算第1期的折舊）
• factor: 省略（默認(rèn)為2）

DDB函數(shù)在life=1時(shí)的運(yùn)算原理：

當(dāng)life=1時(shí)，DDB直接返回 cost - salvage（如果cost > salvage），否則返回0（因?yàn)檎叟f不能為負(fù)）。

實(shí)際計(jì)算過程舉例：

型號(hào)A（salvage=39）：

• cost=50：DDB=50-39=11
• cost=100：DDB=100-39=61

型號(hào)B（salvage=72）：

• cost=50：50<72，DDB=0
• cost=100：100-72=28

繼續(xù)完善公式：

=50-DDB(COLUMN(A:D)*50,B2:B5,1,1)

這部分計(jì)算每個(gè)箱子的實(shí)際數(shù)量。

運(yùn)算過程舉例：

• 型號(hào)A，cost=50：50 - 11 = 39（第一個(gè)箱子數(shù)量）
• 型號(hào)A，cost=100：50 - 61 = -11（無效，后續(xù)會(huì)處理）
• 型號(hào)B，cost=50：50 - 0 = 50（第一個(gè)滿箱）
• 型號(hào)B，cost=100：50 - 28 = 22（第二個(gè)箱子剩余）

繼續(xù)完善公式：

=0&50-DDB(COLUMN(A:D)*50,B2:B5,1,1)

用文本連接符 & 處理：

例如：50-DDB=39→0&39="039"（文本）

負(fù)數(shù)如：-11→0&-11="0-11"（文本，后續(xù)會(huì)轉(zhuǎn)換為錯(cuò)誤值）

繼續(xù)完善公式：

=--(0&50-DDB(COLUMN(A:D)*50,B2:B5,1,1))

• --：作用是將文本轉(zhuǎn)為數(shù)字
• "039"→39（有效）
• "0-11"→錯(cuò)誤值（#VALUE!，因?yàn)椴皇呛戏〝?shù)字）

目的：

確保數(shù)字以三位形式出現(xiàn)（如39→039），但實(shí)際轉(zhuǎn)換后仍是數(shù)字39。負(fù)數(shù)產(chǎn)生的錯(cuò)誤將會(huì)在后續(xù)步驟中被過濾。

第三步：文本處理：構(gòu)建"型號(hào);;數(shù)量"字符串，確保數(shù)字格式。

我們輸入公式：

="\"&A2:A5&";;"&--(0&50-DDB(COLUMN(A:D)*50,B2:B5,1,1))

• 構(gòu)建型號(hào)字符串："\"&A2:A5&";;"

"\"表示雙引號(hào)字符，Excel中轉(zhuǎn)義寫法。

• 例如型號(hào)A："\"&"A"&";;" → "A;;"（字符串內(nèi)容為雙引號(hào)+A+兩個(gè)分號(hào)）。
• 與數(shù)量連接："A;;" & 39 → "A;;39"（表示型號(hào)A和數(shù)量39的組合）。

分隔符 ;; 用于后續(xù)TEXT函數(shù)拆分?jǐn)?shù)據(jù)。

第四步：降維過濾

我們外面嵌套TOCOL函數(shù)：

=TOCOL("\"&A2:A5&";;"&--(0&50-DDB(COLUMN(A:D)*50,B2:B5,1,1)),3)

使用TOCOL轉(zhuǎn)換和過濾，TOCOL將數(shù)組矩陣轉(zhuǎn)為單列，忽略無效項(xiàng)。將上述生成的4行×4列矩陣轉(zhuǎn)換為單列，并忽略空值和錯(cuò)誤（第2參數(shù)設(shè)置為3）。

轉(zhuǎn)換過程：

• 有效值（如A;;39、B;;50）保留。
• 錯(cuò)誤值（如負(fù)數(shù)轉(zhuǎn)換結(jié)果）被跳過。

結(jié)果按行掃描：先處理型號(hào)A所有箱子，再B、C、D。

第五步：拆分輸出

最外面嵌套TEXT函數(shù)：

=TEXT({1,0},TOCOL("\"&A2:A5&";;"&--(0&50-DDB(COLUMN(A:D)*50,B2:B5,1,1)),3))

用 TEXT 分割型號(hào)和數(shù)量，TEXT按分隔符;;分割為兩列。

{1,0}表示提取第1部分（型號(hào)）和第0部分（數(shù)量）。

比如 "A;;39"：

1. {1}提取 ;; 前的部分 → "A"
2. {0}提取 ;; 后的部分 → "39"

最終輸出兩列：D列為型號(hào)，E列為數(shù)量。

TEXT({1,0}, ...) 固定結(jié)構(gòu)：

不是真正的文本格式化函數(shù)，而是被用來按分隔符拆分字符串的巧妙技巧。就像用剪刀沿著縫線剪開布料，;; 是縫線，{1,0} 是指揮剪刀裁剪的位置指令。

推薦閱讀：lookup加座啥意思? wps中l(wèi)ookup+sumifs搞定有合并單元格的多條件求和

最新評(píng)論