在一些工作中,我们需要每日对某类别的库存定量进行记录,并且计算每日库存的增减。比如说:
日期 | 类别 | 库存 |
8月10日 | A | 9 |
8月10日 | B | 4 |
8月10日 | C | 5 |
8月9日 | A | 8 |
8月9日 | B | 9 |
8月9日 | C | 8 |
8月8日 | A | 10 |
8月8日 | B | 5 |
8月8日 | C | 5 |
8月5日 | A | 5 |
8月5日 | B | 1 |
8月5日 | C | 2 |
8月4日 | A | 2 |
8月4日 | B | 6 |
8月4日 | C | 2 |
8月3日 | A | 2 |
8月3日 | B | 7 |
8月3日 | C | 7 |
8月2日 | A | 2 |
8月2日 | B | 1 |
8月2日 | C | 9 |
这个是每日的定量记录表,它记录了每日的库存。我命名为库存记录表。但是,我们不仅需要对库存进行记录,而且还要计算当天与上一次记录相比的差值。
通常来说,可以直接排序并建立一个新表格,在同类中的当行库存减下一行库存,然后这个新表格就可以实现目的。
但是这种做法会依赖排序,而且这也意味着之后不能动原表格了。
这里提供一个做法,涉及两个excel函数
1.lookup函数,多条件查找值。
例如:
=LOOKUP(2,1/(库存记录表[日期]=[@日期])/(库存记录表[类别]=[@类别]),库存记录表[库存])
解释:lookup函数第一个参数是查找值,第二个参数是查找列&条件,第三个参数是输出的列,最终返回一个在输出列中符合条件的单元格。
日期 | 类别 | 当日库存 |
8月10日 | A | 9 |
8月9日 | A | 8 |
8月8日 | A | 10 |
8月7日 | A | - |
8月6日 | A | - |
8月5日 | A | 5 |
8月4日 | A | 2 |
8月3日 | A | 2 |
8月2日 | A | 2 |
8月1日 | A | - |
这个在网上也能搜到用法,我就不进一步解释原理了。
2.large函数
用法是:Large(array,k),返回一个array数组中第k大的数字
在这里,可以通过对日期的排名加重复数量获得k,从而获得在记录表中比当天小的最后一次记录的日期
例如:
=LARGE(库存记录表[日期],RANK.EQ([@日期],库存记录表[日期])+COUNTIF(库存记录表[日期],[@日期]))
解释:rank.eq是对日期进行排序,比如今天是1,那昨天的值比今天小,就会往后排。在这里,由于有三个类别,因此日期实际上会重复三次,所以昨天的日期在排名时会排成4,依次类推。因此需要加上一个countif的函数,将排名加上重复的个数,才能获得昨天的排名,从而在large函数中输出正确的日期。
将这个函数结果放到lookup多条件查找值的日期条件中,得到
=IFERROR(LOOKUP(2,1/(库存记录表[日期]=LARGE(库存记录表[日期],RANK.EQ([@日期],库存记录表[日期])+COUNTIF(库存记录表[日期],[@日期])))/(库存记录表[类别]=[@类别]),库存记录表[库存]),"-")
日期 | 类别 | 上次记录的库存 |
8月10日 | A | 8 |
8月9日 | A | 10 |
8月8日 | A | 5 |
8月7日 | A | - |
8月6日 | A | - |
8月5日 | A | 2 |
8月4日 | A | 2 |
8月3日 | A | 2 |
8月2日 | A | - |
8月1日 | A | - |
两个量相减即可获得差值。
=LOOKUP(2,1/(库存记录表[日期]=[@日期])/(库存记录表[类别]=[@类别]),库存记录表[库存])-IFERROR(LOOKUP(2,1/(库存记录表[日期]=LARGE(库存记录表[日期],RANK.EQ([@日期],库存记录表[日期])+COUNTIF(库存记录表[日期],[@日期])))/(库存记录表[类别]=[@类别]),库存记录表[库存]),"-")
日期 | 类别 | 差值 |
8月10日 | A | 1 |
8月9日 | A | -2 |
8月8日 | A | 5 |
8月7日 | A | - |
8月6日 | A | - |
8月5日 | A | 3 |
8月4日 | A | 0 |
8月3日 | A | 0 |
8月2日 | A | - |
8月1日 | A | - |