解析excel中的单元格地址引用.docVIP

解析excel中的单元格地址引用 摘 要：Excel中有300多个函数，具有强大的数据处理和计算功能，其中，对单元格地址的引用是很多使用者头疼的一件事，也是老师在上课时比较难讲的内容之一，本文通过具体的使用实例，详细的解析了Excel中的单元格地址引用的变化规律。 关键词：单元格地址引用；绝对引用；相对引用；混合引用 中图分类号：TP317.3 Excel是一款非常重要的办公软件，通常用来制作报表和对数据进行统计分析的，比如，成绩处理、单位工资/资金管理、生产资料管理等。用Excel公式函数处理起来非常便捷，公式（函数）复制时，其中的单元格地址会跟着变化，这使得使用者一头雾水，其实只要抓住地址变化规律，一切都会明了。下面就以公式复制时，公式中单元格地址的变化来解析其变化规律，函数中单元格地址引用变化是完全与此相同的。 1 Excel中的地址引用种类 在Excel中单元格地址引用分为三种。 （1）相对引用：地址是由列号加行号组成，如：A1、C67等，这是excel中对单元格最为常用的引用。 （2）绝对引用：在地址的行号和列号前均加上绝对引用符“$”，如：$A$1、$C$67等，绝对引用在公式函数中也比较常见。 （3）混合引用：只在单元格地址的行号（或列号）前加上绝对引用符“$”，如：$A1，A$1、$C67、C$67等。 2 进行公式复制时，其中参数的单元格地址的变化规律 2.1 相对引用地址的变化规律 当公式从一个单元格复制到另外一个单元格时，公式中对单元格地址的相对引用会发生相同的变化。即：相对引用的行号与公式复制时的行号变化相同；相对引用的列号与公式复制时的列号变化相同。如：在实例中，单元格F18中的公式是C9+D7+E8，将单元格F18中的公式复制到H13中去，公式就变成成了E4+F2+G3，变化如下表。 通过表格对比可以看出，相对引用的地址变化与公式复制时的目标地址行列发生的变化值是相同的。 表1 公式复制后对应单元格地址变化表 2.2 绝对引用地址的变化规律 当公式复制时，其中对单元格地址的绝对引用在公式复制后不发生变化，也就是说在公式复制时，如果其中的某些地址不变，那么对该地址使用绝对引用。如：在成绩表中根据总成绩对每个学生排名次时，RANK（number，ref，order）函数的第二个参数是不能随着公式的复制而变化时，此时第二个参数地址使用绝对引用。 2.3 混合引用地址的变化规律 当公式复制时，有时也会使用到单元格地址的混合引用，混合引用是只在单元格地址的行（或列）号前加绝对引用符“$”，而列（或行）号前没有加绝对引用符。此时公式复制时，地址的混合引用变化规律是：前面加绝对引用符“$”的那部分地址不发生变化，如果列号前加“$”，则列号复制后不发生变化，如果行号前加“$”，则行号复制后不发生变化；而没有加绝对引用符“$”的那部分地址的变化与相对引用的变化相同。 例如，在F18单元格中输入公式：$C9+$D7+$E8时，将该公式复制到H13时，由于公式中的列号前均加绝对引用符“$”，所以公式复制后，对应列号不发生变化，而由于公式是从18行复制到13行，行号变化了-5，则公式复制后对应地址中的行号就会变化-5，复制后H13单元格中的公式是：$C4+$D2+$E3。 再如，在F18单元格中输入公式：C$9+D$7+E$8时，将该公式复制到H13时，由于公式中的行号前均加绝对引用符“$”，所以公式复制后，对应行号不发生变化，而由于公式是从F行复制到H行，列号变化了+2，则公式复制后，对应地址中的列号就会变化+2，复制后H13单元格中的公式是：E$9+F$7+G$8。 3 单元格地址引用变化实例 现在使用单元格地址的引用快速制作九九乘法表。 3.1 先把行数和列数作好 在A2～A10和B2～B10单元格中分别填充1～9。 3.2 九九乘法表只有下半部分填充乘法值，则可以使用if函数先判断单元格所在行首单元格值是否大于等于列首单元值，如果大于成立，则该项单元格值为其所在行首单元格值乘以列首单元值；否则该单元格值为空，即：B2 IF（A2 B1，B1*A2，）。 3.3 将B2单元格的函数填充复制到其他单元格时，函数中的地址是相对引用，复制后，行号列号均会发生变化。如：函数复制到I10单元格时为：IF（H10 I9，I9*H10，），显然是错误的，函数中的A列和第1行是不应变化的，则在列号A和行号1前加绝对引用符“$”，即：B2 IF（$A2 B$1，$A2*B$1，），然后将该函数下拉填充，接着左拉填充，很快一个九九乘法表就制作好了。 图1 下拉B2单元格填充至B10单元格，再填充至J2：J10单元格区域 4 结束语 通过上面的例子，可以看出：公式（函数）复制时，希望某些地址（或地址范围）不发生变化，则相应的地址使用