Excel使用-统计函数中动态引用单元格

---

前言

前两天，有个朋友提出了个Excel使用的问题，他的数据大致是这样的
他的需求是用什么方式能够快速根据B列信息计算A列的相关区域数据，并在在C列（最大最小值）相应区域的单元格显示。

举个例子
当B2单元格显示为“负数”时，C2单元格显示A2~A8之内的最小数值，即为 -1606
当B9单元格显示为“正数”时，C9单元格显示A9~A23之内的最大正数，即为 5290

这个问题，看似简单，我真正尝试时才发现要真想做到动态，需要应用多多个Excel函数的组合应用。

---

一、问题拆解

任何复杂的问题，都是由多个简单的问题组合而成的。对于Excel的问题而言，一般来说，复杂的问题，就是一堆Excel函数组合而成。这里我们先按照Excel的函数思路对问题做个拆解

1. 解决问题，首先需要做个条件判断。
   需要逐行对B列的数据做判断，条件判断有3个分支结果，分别对应值为“正数”、“负数”、空的情况。、
2. 获取A列对应区域的最大（最小）值
   当A列相应区域为“负数”时，获取最小值，当A列相应区域为“正数”时，则获取最大值
3. 获取要计算的A列区域的开始单元格位置
   通过分析，可以看出当B列有值时，其所在行所对应的A列单元格就是A列参与计算相应区域的开始单元格
4. 获取要计算要计算的A列区域的结束单元格位置
   很显然，结束单元格的位置与开始单元格的位置具有一定的关联关系，它们直接具有如下关系
   结束单元格的行号=开始单元格的行号+相关区域和记录数-1
5. 获取要计算的A列区域的记录数
   A列区域的记录数与B列的非空值具有一定关联关系，只要确定了B列两个上下相邻区域的行号，也就确定了对应A列区域的记录数

二、问题逐渐解决引入的关联函数

获取记录数

1. 要解决该问题首先需要确定当前行的行号。
   在Excel中具有函数 Rows()
   可确定当前单元格所在行的行号，这个函数与Rows(array)不同，这个函数可不需要参数直接返回当前所在行的值。
2. 其次要获取B列的相对地址
   这时候需要引入Address函数，该函数语法为

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
其主要参数为：
row_num    必需。 一个数值，指定要在单元格引用中使用的行号。
column_num    必需。 一个数值，指定要在单元格引用中使用的列号。

3. 然后引用相对地址并获取相关的值
   这里引入了Indirectly 函数，该函数相关说明为

基本语法：INDIRECT(ref_text, [a1])
必填参数：ref_text 为R1C1 样式引用
作用：返回由文本字符串指定的引用。

4. 最后获取相应的行号
   这里引入了Match函数，该函数相关说明为

语法：
MATCH(lookup_value, lookup_array, [match_type])
参数说明：
◾ lookup_value    必需。 要在 lookup_array 中匹配的值。 例如，如果要在电话簿中查找某人的电话号码，则应该将姓名作为查找值，但实际上需要的是电话号码。
lookup_value 参数可以为值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。
◾ lookup_array    必需。 要搜索的单元格区域。
◾ match_type    可选。 数字 -1、0 或 1。 
作用：
在 范围 单元格中搜索特定的项，然后返回该项在此区域中的相对位置。 

综合使用这些函数，可以获取相应的记录数，相关范例如下

对应的函数运算结果为

获取A列区域的开始单元格位置

采用与获取记
使用相同的函数，我们可以获取对应的A列区域的开始单元格位置
相关范例如下

相关计算结果如下

获取A列区域的结束单元格位置

根据之前的问题拆解分析，我们结合开始单元格位置与记录数的结果，获取到相应结束单元格位置
相关范例如下

相关计算结果如下

获取A列区域的极值

通过使用Excel的极值函数，我们可以计算出相关区域的极大值（或极小值），相关函数说明如下

函数：MAX(number1, [number2], ...)
作用：返回一组值中的最大值。

函数：MIN(number1, [number2], ...)
作用：返回一组值中的最小值。

相关范例如下

相关结果如下

条件判断并获取最终结果

上述几个步骤，我们已经可以计算出第一行的相关值，由于B列的值是有变化的，所以我们需要引入IF函数来做条件判断生成相关最终结果，IF是Excel相对较常用函数。
相关范例如下

=IF(B2="负数",MIN(INDIRECT(ADDRESS(ROW(),1)):INDIRECT(ADDRESS(ROW()+MATCH("正数",INDIRECT(ADDRESS(ROW(),2)):$B$1000,0)-2,1))),IF(B2="正数",MAX(INDIRECT(ADDRESS(ROW(),1)):INDIRECT(ADDRESS(ROW()+MATCH("正数",INDIRECT(ADDRESS(ROW(),2)):$B$1000,0)-2,1))),""))

其相关运算结果如下

总结

以上就是今天要讲的内容，本文以一个数据处理需求为例，讲解了多种不同的Excel函数组合使用的效果，完整的数据集合使用范例可见Excel统计中动态的动态引用