NO.196# 用Excel查询先进先出法下发出商品批次

---

地 球 不 爆 炸    我 们 不 打 烊

            

>>>上课啦：

---

在供应链生产管理中，先进先出法可以有效的控制存货成本和避免任意浪费。本期小课堂我们分享如何查询发出商品的进货批次

---

▼本期案例截图

---

---

抛砖引玉：

---

小徐是某商贸企业的仓管员，每天需要根据周转商品的结余数量和进货数量查询发出商品的进货批次。例如：今天上午发货“曲奇饼干”2000件，由“进货批次”A01中的705件、D04中的677件和X07中的856件里的618件共同组成，而“进货批次”X07中的856件里的238件和R10中的691件成为库存。如何用Excel制作一个简易的查询实现呢？

---

庖丁解牛：

---

我们以发货“曲奇饼干”2000件为例，循序渐进推导最终的函数公式。请看黑板

---

<Ⅰ> 借助OFFSET函数的多维引用，构造一个多维内存数组。返回第1条数据是单元格区域B1:B1，第2条数据是单元格区域B1:B2，第3条数据是单元格区域B1:B3，……，第11条数据是单元格区域B1:B11

➽=OFFSET($B$1,,,ROW($1:$11))

---

<Ⅱ> 借助SUMIF函数的降维功能，按照“曲奇饼干”条件累加求和。返回数组{0;705;705;705;1382;1382;1382;2238;2238;2238;2929}

➽=SUMIF(OFFSET($B$1,,,ROW($1:$11)),E2,C1)

---

<Ⅲ> 借助LOOKUP函数的向量形式查询“进货批次”。如果LOOKUP函数查询不到第1参数Lookup_Value，则返回第2参数Lookup_Vector中小于或者等于第1参数Lookup_Value的最大值。返回F06

➽=LOOKUP(F2-0.0001,SUMIF(OFFSET($B$1,,,ROW($1:$11)),E2,C1),A1:A11)

---

<Ⅳ> 借助MATCH函数的定位功能锁定行号。返回8

➽=MATCH(LOOKUP(F2-0.0001,SUMIF(OFFSET($B$1,,,ROW($1:$11)),E2,C1),A1:A11),A1:A11,)+1

---

<Ⅴ> 借助INDIRECT函数的直接引用地址。返回数组{"A01";"B02";"C03";"D04";"E05";"F06";"X07"}

➽=INDIRECT("A2:A"&MATCH(LOOKUP(F2-0.0001,SUMIF(OFFSET($B$1,,,ROW($1:$11)),E2,C1),A1:A11),A1:A11,)+1)

---

<Ⅵ> 借助IF函数的重构数组。返回数组{"A01";#NAME?;#NAME?;"D04";#NAME?;#NAME?;"X07";#NAME?;#NAME?;#N/A}

➽=IF(B2:B11=E2,INDIRECT("A2:A"&MATCH(LOOKUP(F2-0.0001,SUMIF(OFFSET($B$1,,,ROW($1:$11)),E2,C1),A1:A11),A1:A11,)+1),\)

---

<Ⅶ> 借助TEXTJOIN函数的文本连接。返回A01，D04，X07

➽=TEXTJOIN("，",,IFERROR(IF(B2:B11=E2,INDIRECT("A2:A"&MATCH(LOOKUP(F2-0.0001,SUMIF(OFFSET($B$1,,,ROW($1:$11)),E2,C1),A1:A11),A1:A11,)+1),\),""))

---

下课啦>>>：

---

本期小课堂所分享的知识点源自于网名为“应用之家”的Excel大咖！

---

案例素材 链接: https://pan.baidu.com/s/1dcrY9LTDxZ5cgiSbx7eILQ