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

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

• 類型：辦公軟件
• 大小：2.2GB
• 語言：簡體中文
• 時間：2024-09-12

查看詳情

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

• 類型：辦公軟件
• 大小：260MB
• 語言：簡體中文
• 時間：2025-07-03

查看詳情

如下圖所示：

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

裝箱要求是：

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

下面顯示了每個箱子的明細：

• A型號：1箱39個
• B型號：1箱50個和1箱22個
• C型號：2箱各50個和1箱17個
• D型號：1箱21個

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

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

可以這樣寫公式：

=COLUMN(A:D)

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

繼續(xù)完善公式：

=COLUMN(A:D)*50

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

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

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

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

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

DDB參數(shù)解析：

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

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

當(dāng)life=1時，DDB直接返回 cost - salvage（如果cost > salvage），否則返回0（因為折舊不能為負）。

實際計算過程舉例：

型號A（salvage=39）：

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

型號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)

這部分計算每個箱子的實際數(shù)量。

運算過程舉例：

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

繼續(xù)完善公式：

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

用文本連接符 & 處理：

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

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

繼續(xù)完善公式：

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

• --：作用是將文本轉(zhuǎn)為數(shù)字
• "039"→39（有效）
• "0-11"→錯誤值（#VALUE!，因為不是合法數(shù)字）

目的：

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

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

我們輸入公式：

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

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

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

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

分隔符 ;; 用于后續(xù)TEXT函數(shù)拆分數(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)為單列，忽略無效項。將上述生成的4行×4列矩陣轉(zhuǎn)換為單列，并忽略空值和錯誤（第2參數(shù)設(shè)置為3）。

轉(zhuǎn)換過程：

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

結(jié)果按行掃描：先處理型號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 分割型號和數(shù)量，TEXT按分隔符;;分割為兩列。

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

比如 "A;;39"：

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

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

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

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

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

最新評論