今天跟大家一起分享一个某学员遇到的关于库存台账结余汇总统计的案例。
简单介绍一下这位学员的问题和需求。
这位学员的工作主要是统计管理各种型号产品的库存台账。现在需要将大量型号产品库存信息表中的结余数量汇总到一个工作表中。
如下图：
如GHE、AA、600分别是三种不同型号产品，E列中的最后一行为该产品的最新结余情况。（注:各产品型号表中的格式一致）
现在需要在汇总表中以产品型号为行，统计每个产品最后结余情况。
如下图所示：
我们要达成这样的需求其实有2个问题点。
-
1、将各个产品结余明细工作表中的数据引用到汇总表中。
-
2、如何才能返回产品型号表中的最后结余情况。
下面我们就带着这两个问题一起来解决这位同学的需求。
1、既然用到引用那么我们就必须要会用indirect函数，其主要作用为返回文本字符串所指定的引用。
举例:
A列为工作表的名称，通过将A2单元格中的文本字符与!B2合并构建一个引用。相信大家在平时工作中使用跨表匹配的时候一定很熟悉，目标单元格引用均由工作表名称+感叹号+单元格名称组成，比如：GHE!B2。
这里我们就可以通过INDIRECT(A2&"!B2")函数公式直接返回GHE工作表中B2单元格的内容。
我们再看看GHE工作表中B2单元格的内容是不是GHE。
我们看到GHE工作表中B2单元格的内容的确是GHE。
2、第二个问题是如何返回最后结余情况。
以GHE工作表为例我们最终目的是要返回表中的E7单元格内容，而且需要随着行数变化而变化。
看到这里相信很多人都会想到用offset函数来完成
Offset函数功能为以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。Reference 作为偏移量参照系的引用区域。Reference 必须为对单元格或相连单元格区域的引用；否则，函数 OFFSET 返回错误值#VALUE!。
如下图：
函数公式：=OFFSET(B2,5,3,1,1)
含义：以GHE工作表所在的B2作为参照单元格，向右偏移3列，向下偏移5行即可返回E7单元格最终的结余数。函数公式中的5表示第五行，3表示第三列，最后两个参数为1表示只返回一个单元格内容。
下面我们我只要将函数公式与第一步中的indirect函数公式完成嵌套：
=OFFSET(INDIRECT(A2&"!B2"),5,3,1,1)
静态的数据返回做好了，那么如何做到随着行数的变化而随时变化呢？
因为表中A列的日期与E列结余是对应的，这里我们巧妙的将行数用count函数来代替，通过count函数统计A列数值单元格数量来作为OFFSET的第二个参数。这样我们就能做到随着行数变化随时统计对应的最终结余数据。
函数公式为：COUNT(INDIRECT(A2&"!A:A"))+1，加1的原因是由于GHE工作表 A列中只有4个单元格为数值，而在上个案例中我们需要向下偏移5行，所以我们需要这基础上加1来补充到。
最终函数公式：
=OFFSET(INDIRECT(A2&"!B2"),COUNT(INDIRECT(A2&"!A:A"))+1,3,1,1)
我们再来简单总结梳理一下：
本案例中主要的难点在如何引用指定列的最后一行数值，这里我们使用了offset、indirect函数完成了指定数据的引用，同时通过count函数完成了对指定单元格动态更新查找。最终实现了更具产品名称快速统计返回对应库存台账的最终结余情况。
