提取单元格内引用单元格的位置_Excel｜深入理解公式和单元格引用

Excel的精髓在于公式，公式的精髓在于可以使用单元格引用，其强大之处在于公式可以复制(通过单元格的相对引用、绝对引用、混合引用实现)。

1 公式的组成

1.1 以等于号“=”开头，也可以以加号“+”开头；

1.2 运算符：有算术、比较、文本、引用、逻辑运算符等，运算符使用需要考虑优先级。

1.3 单元格引用：包括命名的单元格和范围，既可以是当前工作表，也可以是当前工作簿的其他工作表的单元格，或是其他工作薄中的单元格。

1.4 值或字符串。

1.5 工作表函数和参数：分为文本处理函数、查找与引用函数、数学函数、信息提取和逻辑判断函数、日期和时间计算函数、统计和求和函数、财务金融函数、工程函数等类别。

函数可以是Excel内置函数，也可以是自定义函数。

1.6 括号：控制着公式中表达式的计算顺序。

1.7 可以使用数组公式。

需要注意的是，公式中可以引用多个函数、或函数相互嵌套(也就是用函数作为另一个函数的参数)。

2 单元格的引用方式

如果按编程语言的数据结构去理解Excel，一个工作表就是一个二维数组，行Row用1、2、3……1048576，列Column用A、B、C……XFD表示，其单元格地址有两种表示方式：

2.1 A1引用方式

A1即表示第一列(A列)每一行，C3即表示第三列(C列)第3行。

2.2 R1C1引用方式

R是Row的首字母，C是Column的首字符。

除了可以引用单元单元格，还可以使用冒号“:”引用运算符来引用单元格区域，使用空格来交叉引用，使用逗号“,”进行联合引用。

3 相对引用、绝对引用、混合引用

想像一下，我们怎样确定一个位置，通过经度、纬度多少即可以GPS定位到，这是绝对定位的方式，如果有一个参照物，便可以以参照物为基准进行偏移量的计量也可以确定准确位置。

Excel也是如此，公式中的引用可以使用绝对引用，就是单元格的行列交错，也可以是相对引用，就是以公式所在单元格为基准，引用单元格相对于公式所在单元格的偏移来确定引用的是什么单元格，这就是相对引用，两者混合使用就是混合引用。

看下面的查找引用VLOOKUP函数：

=VLOOKUP(D:D,基础资料!A:C,3,0)

以上使用的是相对引用的A1引用方式，表示如下：

如果使用R1C1引用方式：

=VLOOKUP(C[-2],基础资料!C[-5]:C[-3],3,0)

从上面可以看到，公式所在单元格相对于引用的单元格的偏移都是列的偏移，所以公式在往下复制时，列的偏移是不变的，在A1引用方式下往下复制公式，因为是相对引用，公式完全一样，但计算的值不一样，这就是相对引用的妙处。

在A1引用方式下，往右复制公式，则列会发生变化(因为列的偏移不一样了)：

=VLOOKUP(D:D,基础资料!A:C,3,0)

变成了：

=VLOOKUP(E:E,基础资料!B:C,2,0)

如果你不想让列的复制发生偏移，只要把不想偏移的列设置为绝对引用即可：

=VLOOKUP($D:$D,基础资料!$A:C,3,0)

(相对引用与绝对引用可以使用F4功能键进行快速切换(在公式编辑状态下))

需要注意的是，在R1C1引用方式下，往下或往右复制公式，不管是绝对引用还是相对引用，公式都是不会发生改变的。

4 跨表引用

[工作簿名称]工作表名称!单元格引用

工作簿名称需要包含路径及扩展名，如果是相同路径下的工作簿，路径可以省略。

5 公式引用错误类型

当被引用的单元格区域或被引用的工作表被删除时，返回#REF!错误；

#NULL!表示交叉引用为空时的错误；

6 查看公式的部分运算结果

在公式编辑状态下，选择全部公式或其中的某一部分，按F9键可以单独计算并显示该部分公式的运算结果；

也可以使用“公式求值”查看分步计算结果(公式选项卡下)。

－End－