excel 通过定量的记录表自动计算差值

在一些工作中，我们需要每日对某类别的库存定量进行记录，并且计算每日库存的增减。比如说：

日期	类别	库存
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	-