关于Excel单元格区域，可能有99%的人都不知道的事

标签：Excel基础

在Excel工作表中执行操作，我们都要使用单元格区域，例如，我们在开始单元格和结束单元格之间使用冒号（:）来选择单元格区域，可能会附加美元符号（$）来固定单元格引用。然而，你真正了解使用单元格区域的所有基本方法吗？本文将为你逐一讲解。

单元格区域运算符

单元格区域运算符是冒号（：）符号，这是大多数Excel用户都知道的。例如：

B2:D5

表示从单元格B2到单元格D5的区域。

单元格区域运算符将区域内所有单元格合并为一个引用，如下图1所示。

图1

合并运算符

合并运算符是逗号（,）符号。如下图2所示，简单地使用逗号分隔一系列单元格，将这些单元格合并成单个单元格区域。

图2

将合并运算符与单元格区域运算符组合使用，会更加强大。如下图3所示，对两个单元格区域的值求和。

图3

然而，在“假设”类型的场景中使用合并运算符时，会发现更强大的功能。如下图4所示，使用AVERAGE函数来查找单元格区域B2:B7的平均值，同时包括单元格D4的值来回答：当下一个值是9时平均值是多少。

图4

交叉运算符

交叉运算符是空格字符。如下图5所示，将返回结果值6，因为单元格区域C2:C8与B5:E5交叉于单元格C5，所以在单元格F2中返回的值等于单元格C5中的值。

图5

在命名区域中应用交叉运算符时，可以创建简单的查找公式，而不需要任何函数。如下图6所示，根据第1行中的值命名对应列，根据列A中的值命名对应行。现在，可以使用交叉运算符创建基于月份和区域的查找。示例中单元格F2返回的值为7。

图6

更进一步，可以将交叉区域相加。如下图7所示，单元格F2中返回的值是15（7+8）。

图7

也可以将交叉运算符与单元格区域操作符一起使用。如下图8所示，单元格F2中返回的值是5（即2+3）。

图8

返回单元格区域的函数

处理单元格区域的一个挑战在于不知道要包含在区域中的单元格地址。幸运的是，可以使用许多函数来返回单元格的地址。

INDIRECT函数

INDIRECT接受文本字符串并将其转换为单元格区域，例如：

=INDIRECT(“A” & F3)

上面的公式接受单元格F3的值作为行号，如果F3中包含的值为6，则单元格引用是A6。

注意，INDIRECT函数是一个易失性函数，因此使用时要小心。

OFFSET函数

OFFSET函数最多可以有5个参数。参数Reference是要基于其偏移的单元格；参数Rows是希望引用单元格移动的行数；参数Cols是希望引用单元格移动的列数；参数Height（可选）是想要的区域的行数；参数Width（可选）是想要的区域的列数。

如下图9中单元格F2中的公式，OFFSET函数开始于单元格D5，向上移动3行到单元格D2，再向左移动2列到单元格B2；于是从单元格B2开始，高为2宽为4的区域（即以单元格B2为起点，2行4列的区域），即单元格区域B2:E3。单元格F2中返回的结果为23。

图9

注意，OFFSET函数是一个易失性函数，因此使用时要小心。

INDEX

INDEX函数的头3个参数与OFFSET函数相似，不同之外在于：（1）第一个参数必须是包含结果单元格的单元格区域；（2）INDEX(A1:C3,1,1)与OFFSET(A1,0,0)引用的单元格相同。

可以以独特的方式使用INDEX函数，如下图10所示。

图10

上图10中的公式实际上就是：

=SUM(B2:E5)

INDEX函数不是易失性函数，因此应尽可能优先于OFFSET函数和INDIRECT函数使用。

CHOOSE函数

CHOOSE函数用于基于索引号选择不同的单元格区域，例如：

=SUM(CHOOSE(2,B2:B7,C2:C7,D2:D7,E2:E7))

CHOOSE函数的第一个参数是索引号，之后的参数是可能结果列表。本例中，第一个参数索引号是2，因此将选择参数列表中的第2个区域即C2:C7。即上面的公式实际为：

=SUM(C2:C7)

SWITCH函数

SWITCH函数（Excel 2016以后版本引入）的操作方式与CHOOSE函数类似。但是，它不会基于索引选择项目，而是基于匹配值返回结果。

=SUM(SWITCH(F2,"北区",B2:B7,"南区",C2:C7,"东区",D2:D7,"西区",E2:E7))

如果单元格F2中的值是“东区”，则SWITCH函数返回单元格区域D2:D7，那么上面的公式变为：

=SUM(D2:D7)

注：本文学习整理自exceloffthegrid.com，供参考。