在Excel中获取商店每日账单的最终余额
每天会有多条记账店对应的过账时间记录,我们想取每天最后一条记录:
这个问题看起来很简单,但是通过一个公式实现起来就没那么容易了。我们来分析一下这个问题的关键点。我们要找出同一天同一家店的账单,然后找出最后一次对应的金额。关键是最后一次,怎么确定?
另外,就是查询的方式。对于数值查询,可以使用Excel中的查询函数或数值计算函数。我们将针对这两种方法给出解决方案:
方案一:SUM函数SUM函数是一个非常强大的函数。如果结合CTRL SHIFT ENTER三键数组公式,甚至比SUMIF和SUMIFS函数更强大,接近SUMPRODECT函数。
SUM函数中括号内的连续乘法等价于多条件和同时成立:
=SUM((表1过账时间=MAX((表1存储=f3) * (-text(表1过账时间,' yyyy/mm/dd')=G3)*表1账户余额(元))最外面的账户余额是SUM函数计算的最终数据列,前面括号里的部分是如果是SUMIF或SUMIFS函数,参数只能是实表中的列,如果不能是计算生成的数据列,两个SUM函数都可以。
我们来拆解一下条件列表:
(表1过账时间=MAX((表1店铺=f3) * (-text(表1过账时间,' yyyy/mm/dd')=G3)*表1过账时间前面的过账时间是表中的实列。我们应该从这个列中找到哪个值?
MAX((表1 stores=F3) * (-text(表1过账时间,' yyyy/mm/dd')=G3)*表1过账时间我们要找的是一个过账时间的最大值,也是有条件的:
(表1店铺=F3) * (-TEXT(表1过账时间,' yyyy/mm/dd')=G3)条件是店铺名称等于F3,过账时间日期等于G3。这里过账时间的日期是text函数生成的计算列,实际上并不存在。文本的前两个减号用于将文本转换为数值。
这样我们就能明白整个函数是怎么计算的了。
最后,当然,CTRL+SHIFT+ENTER三键是需要的。
方案二:索引函数索引函数几乎是一个万能的查询函数。举棋不定的情况下,想想指数函数。只要你能给出行列的计算方法,就能找到你想要的值。
公式的长度相似:
=INDEX(表1账户余额(人民币),MAX((表1过账时间=MAX((表1店铺=F3) * (-text(表1过账时间,' yyyy/mm/dd')=G3))*表1过账时间)* row(13360 $ 44))
MAX((表1过账时间=MAX((表1店铺=f3) * (-text(表1过账时间,' yyyy/mm/dd')=G3) *表1过账时间)* Row ($13360 $44))同样,我们把它一层一层剥开,最外层最好理解为
(表1预订时间=MAX((表1店铺=F3) * (-Text(表1预订时间,' yyyy/mm/dd')=G3) *表1预订时间)看到这里你就明白了,这个条件不就是和SUM函数里的条件一样吗?也就是过账时间要等于同店同天的最后一次。
以上是两个Excel公式的解法。下面给出一个电量查询的查询方法:
方案三:电量查询查询:先给电量查询添加数据源和查询条件:
表1是数据源,表2是查询条件。
其实我们不用想的太复杂。我们直接复制一个数据源,然后对该数据源进行完整的查询操作:
额宝怎么查看一年内资金明细,余额宝怎么转出余额5.jpg">提取出日期列,然后开始筛选店铺与日期:
然后筛选时间:
然后我们通过这个查询创建函数:
参数就是店铺名称与日期
然后我们来到查询条件表2,中添加自定义列引用这个查询函数:
取得fx查询结果表格的第一行,账户余额列的值,就可以了。
看起来挺复杂,其实都是鼠标操作,就需要添加两个参数,相对Excel中的公式还是比较简单的。
使用的方法,把表2加载到工作表中:
我们修改上面的查询条件,然后右键刷新,就能直接取得下面的查询结果表格。
以上就是我们获取店铺当日账单最后余额的解决办法。