工作中，我们常常会将已经形成最优的表格形成一个模板，即：数据的组织结构相同，仅仅是行数不同。但我们又有需要依据实际情况对数据进行加工的需求。这时，我们该怎么去设计一个能随行数变化而变化的数组公式呢？

下面小编就以自己工作中所设计的数据模板为例，来讲解我当时如何解决这个问题的。

模板样式

小编以公司某两款产品的bom成本表模板为例：

产品1的bom表：

产品2的bom表：

大家可以看到，上述两款产品的bom不同之处就只是行数的区别（实事上，有些产品的行数可能有几百行）。其他数据的组织结构完全一致。

当时，我所遇到的问题是：

1、如何才能让未税金额这一例自动根据数据行数来进行计算？

2、如何才能让最终的bom成本，按照行数进行汇总？

即是上述两个地方，我只需要将模板直接应用于不同的产品，而不需要另外再对指定单元格的公式进行修改？

思路

以下是我当时解决问题的思路：

1、分析模板的数据组织结构

2、分析需求中核心要解决的问题

3、依据实际需要设计公式

模板的数据组织结构的分析

这个bom的成本模板的数据组织结构主要是体现在第一行、第二行以及数据明细行。第一行是用来汇总物料总成本；第二行是物料的明细的标题行；数据明细行是从第三行开始，依次往下方填充。第I列的未税金额为F列的数量 乘以 H列的未税单价得来的。

经过分析，发现模板中A列和C列的数据在有效bom之内，任何时候都不会出现中间空白单元格的情况。经过取舍，我将要以A列数据来对数据行数进行统计，并依据这个数据对计算区域的大小进行选定；再根据行数，对F列的数量和H列的未税单价有效单元格进行选择。

分析需求中核心要解决的问题

经过上面对数据的组织结构的分析，我发现要满足我的需求，我只要能解决明细数据的行数就可以了，并且要根据这实际行数来确定最后一行明细数据所在行的行号即可。那么核心问题如下：

1、明细数据的行数。

2、最后一条明细数据的行号。

3、选择F列和H列的有效单元格区域。

公式设计

1、明细数据的行数

根据对模板的分析得知，A列的数据在明细数据完成之前，A列不会出现意外空格。因此，我们可以统计A列的非空单元格个数再减1(减的1，即是减的第二行的标题行的非空单元格数)就能得到明细数据的行数。

那么，哪个函数可以用来统计非空单元格的个数呢？COUNTA函数就呼之欲出了。

计算明细数据的行数的公式就是COUNTA(A:A) - 1

2、那么最后一条明细数据的行号也很容易就能得到了：即是在上述行数的基础之上加2，加的这个2指的就是第一行和第二行。

最后一条明细数据的行号就是：COUNTA(A:A) - 1 + 2 = COUNTA(A:A) + 1

3、选择F列和H列的有效单元格区域范围。

这个范围的选择也是本公式的重点和难点。

已知行号、列号，如何引用单元格的区域范围呢？

通常我们的单元格的引用有两种样式，一种是A1；另一种R1C1（这种引用格式的意思是第1行第1列相交汇的单元格：这里第一行和第一列交汇的单元格其实就是A1）。

R1C1：R表示行，C表示列；R后面的数字表示第几行，列后面的数字则表示第几列。R1C1表示的是第1行和第1列相交的单元范围。同理R2C3，则表示是第二行和第3列交汇的单元格范围，如下图中的红色矩形区域能很好的解释R2C3单元格。

很显然，当前我们只能采用R1C1这种形式来引用单元格区域。

多数情况下，如果你没有在Excel和wps的设置里将单元格引用设置成R1C1的样式，我们是没有办法让表格直接识别R1C1这种引用单元格的方式的。

那么，当未进行上述引用样式设置的前提下，如何让公式能识别R1C1这样的引用形式呢？

INDIRECT()函数的出现完美解决了这一难题。

下图是INDIRECT的基本用法，本合集不做讲解，大家看看即可

如果我要引用F2:F5这个单元格区域的内容，用R1C1样式进行，就是：
INDIRECT("R2C6:R5C6",false)，大家可以动手试一下，这样是否能引用到F2:F5这个区域的单元格。一定要注意第一个参数要用半角双引号引用单元格区域，以表示该参数是一个文本。

这样，我们结合第2点的结论可以将F列和H列的公式设计出来了

因此F列明细数据的引用范围的公式是：
INDIRECT("R3C6"&":R"&(1+COUNTA(A:A))&"C6",FALSE)
同理H列明细数据的引用范围的公式是：
INDIRECT("R3C8"&":R"&(1+COUNTA(A:A))&"C8",FALSE)
此时我们只需要在I3单元格中写入公式：
”=ROUND(INDIRECT(INDIRECT("R3C6"&":R"&(1+COUNTA(A:A))&"C6",FALSE)*INDIRECT("R3C8"&":R"&(1+COUNTA(A:A))&"C8",FALSE),2)“
如此就能实现依据有效数据的行数进行自动计算未税金额。大家可以看看效果：

I1单元格的自动求和公式也可以写出来了：

=SUM(INDIRECT("R3C"&COLUMN()&":R"&(1+COUNTA(A:A))&"C"&COLUMN(),FALSE))

上述公式中有个Column()函数，这个函数的作用是获取当前单元格所在的列号。
本意是这个公式设置在哪个单元格，就计算该单元格所在列第二行起的有效数的和。在这里就是获取第I列，从第三行开始，到明细数最后一条数据所在行止的数据的和。

总结

经过上述的设置，我只需要在模板中设置好，I1和I3两个单元格中的公式，无论明细数所有多少行，我都不用再去修改公式，设置求和及求积的范围。做到了以不变应万变。

***大家也可以将你们工作中的需求告诉我，我会每周花一期专们写一篇文章来实现大家的需求。