Excel 计算给定引用列标:COLUMN函数

COLUMN函数用于返回给定引用的列标。其语法如下:


COLUMN(reference)

其中,reference参数为需要得到其列标的单元格或单元格区域。如果省略reference,则假定为是对函数COLUMN所在单元格的引用。如果reference参数为一个单元格区域,并且函数COLUMN作为水平数组输入,则函数COLUMN将reference参数中的列标以水平数组的形式返回。reference参数不能引用多个区域。下面通过实例详细讲解该函数的使用方法与技巧。

打开“COLUMN函数.xlsx”工作簿,切换至“Sheet1”工作表,本例中的原始数据如图16-40所示。要求根据工作表中的数据内容,返回给定引用的列标。具体操作步骤如下。

STEP01:选中A2单元格,在编辑栏中输入公式“=COLUMN()”,用于返回公式所在列的列标,输入完成后按“Enter”键返回计算结果,如图16-41所示。

图16-40 原始数据

STEP02:选中A3单元格,在编辑栏中输入公式“=COLUMN(B15)”,用于返回B15的列标,输入完成后按“Enter”键返回计算结果,如图16-42所示。

图16-41 计算公式所在列

图16-42 计算引用的列

Excel 计算引用区域个数:AREAS函数

AREAS函数用于返回引用中包含的区域个数,区域表示连续的单元格区域或某个单元格。其语法如下:


AREAS(reference)

其中,reference参数表示对某个单元格或单元格区域的引用,也可以引用多个区域。如果需要将几个引用指定为一个参数,则必须用括号括起来,以免Excel将逗号作为参数间的分隔符。下面通过实例详细讲解该函数的使用方法与技巧。

打开“AREAS函数.xlsx”工作簿,切换至“Sheet1”工作表,本例中的原始数据如图16-36所示。要求根据工作表中的数据内容,返回引用中包含的区域个数。具体操作步骤如下。

STEP01:选中A2单元格,在编辑栏中输入公式“=AREAS(B1:D5)”,用来计算引用中包含的区域个数,输入完成后按“Enter”键返回,即可得出计算结果为1,如图16-37所示。

图16-36 原始数据

图16-37 A2单元格计算结果

STEP02:选中A3单元格,在编辑栏中输入公式“=AREAS((B1:D5,E5,F6:I9,G6))”,用来计算引用中包含的区域个数,输入完成后按“Enter”键返回,即可得出计算结果为4,如图16-38所示。

STEP03:选中A4单元格,在编辑栏中输入公式“=AREAS(B1:D5 B2)”,用来计算引用中包含的区域个数,输入完成后按“Enter”键返回,即可得出计算结果为1,如图16-39所示。

图16-38 A3单元格返回结果

图16-39 A4单元格返回结果

Excel 调整引用:OFFSET函数实例图解

OFFSET函数的功能是以指定的引用为参照系,通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域,并可以指定返回的行数或列数。其语法如下:


OFFSET(reference,rows,cols,height,width)

其中,reference参数作为偏移量参照系的引用区域。rows参数为相对于偏移量参照系的左上角单元格,上(下)偏移的行数。cols参数为相对于偏移量参照系的左上角单元格,左(右)偏移的列数。height参数为高度,即所要返回的引用区域的行数,必须为正数。width参数为宽度,即所要返回的引用区域的列数,必须为正数。下面通过实例详细讲解该函数的使用方法与技巧。

打开“OFFSET函数.xlsx”工作簿,切换至“Sheet1”工作表,本例中的原始数据如图16-32所示。要求根据工作表中的数据内容,以指定的引用为参照系,通过给定偏移量得到新的引用。具体操作步骤如下。

STEP01:选中A2单元格,在编辑栏中输入公式“=OFFSET(B2,2,3,1,1)”,用于显示E4单元格中的值,输入完成后按“Enter”键返回计算结果,如图16-33所示。

图16-32 原始数据

图16-33 A2单元格返回结果

STEP02:选中A3单元格,在编辑栏中输入公式“=SUM(OFFSET(C3:E5,-2,0,3,3))”,用于对C1:E3数据区域进行求和,输入完成后按“Enter”键返回计算结果,如图16-34所示。

STEP03:选中A4单元格,在编辑栏中输入公式“=OFFSET(C3:E5,0,-3,3,3)”,输入完成后按“Enter”键返回计算结果,因为引用区域不在工作表中,工作表中会显示计算结果为错误值“#REF!”,如图16-35所示。

图16-34 A3单元格返回结果

图16-35 A4单元格返回结果

Excel 返回单元格地址引用值:ADDRESS函数

ADDRESS函数用于按照给定的行号和列标,建立文本类型的单元格地址。其语法如下:


ADDRESS(row_num,column_num,abs_num,a1,sheet_text)

其中,row_num参数表示在单元格引用中使用的行号,column_num参数表示在单元格引用中使用的列标,abs_num参数用于指定返回的引用类型,其返回的引用类型如表16-1所示。

a1参数为用于指定A1或R1C1引用样式的逻辑值。如果a1为TRUE或省略,函数ADDRESS返回A1样式的引用;如果a1为FALSE,函数ADDRESS返回R1C1样式的引用。

表16-1 abs_num参数返回的引用类型

abs_num参数返回的引用类型

sheet_text参数为一文本,用于指定作为外部引用的工作表的名称,如果省略sheet_text,则不使用任何工作表名。下面通过实例详细讲解该函数的使用方法与技巧。

打开“ADDRESS函数.xlsx”工作簿,切换至“Sheet1”工作表,本例中的原始数据如图16-26所示。要求根据工作表中的数据内容,以文本形式返回单元格地址引用值。具体操作步骤如下。

STEP01:选中A2单元格,在编辑栏中输入公式“=ADDRESS(6,8)”,用于返回绝对引用($H$6),输入完成后按“Enter”键返回计算结果,如图16-27所示。

图16-26 原始数据

图16-27 A2单元格返回结果

STEP02:选中A3单元格,在编辑栏中输入公式“=ADDRESS(6,8,2)”,用于返回绝对行号,相对列标(H$6),输入完成后按“Enter”键返回计算结果,如图16-28所示。

STEP03:选中A4单元格,在编辑栏中输入公式“=ADDRESS(6,8,2,FALSE)”,用于返回在R1C1引用样式中的绝对行号,相对列标(R6C[8]),输入完成后按“Enter”键返回计算结果,如图16-29所示。

图16-28 A3单元格返回结果

A5单元格返回结果

图16-29 A4单元格返回结果

STEP04:选中A5单元格,在编辑栏中输入公式“=ADDRESS(6,8,1,FALSE,”[Book1]Sheet1″)”,用于返回对其他工作簿或工作表的绝对引用([Book1]Sheet1!R6C8),输入完成后按“Enter”键返回计算结果,如图16-30所示。

STEP05:选中A6单元格,在编辑栏中输入公式“=ADDRESS(6,8,1,FALSE,”销售报表”)”,用于返回对其他工作表的绝对引用(’销售报表’!R6C8)),输入完成后按“Enter”键返回计算结果,如图16-31所示。

图16-30 A5单元格返回结果

图16-31 A6单元格返回结果

Excel 删除与允许循环引用

单元格公式中如果使用了循环引用,在状态栏中的“循环引用”后面显示是循环引用中的某个单元格的引用。如果在状态栏没有“循环引用”一词,则说明活动工作表中不含循环引用。

删除循环引用的具体操作步骤如下。

STEP01:打开“循环引用.xlsx”工作簿,切换至“公式”选项卡,在“公式审核”组中单击“错误检查”下三角按钮,在展开的下拉列表中选中“循环引用”选项,在展开的级联列表中选中一个循环引用单元格,这里选中A1单元格,如图10-47所示。

STEP02:随后会返回工作表,光标已经将刚才选中的单元格定位。这时,在公式编辑栏中的公式中将其循环引用的单元格删除即可,如图10-48所示。

选中循环引用的单元格

图10-47 选中循环引用的单元格

删除循环引用的单元格

图10-48 删除循环引用的单元格

允许使用循环引用的具体操作步骤如下。

在工作表中切换至“文件”选项卡,在左侧导航栏中单击“选项”标签,打开如图10-49所示的“Excel选项”对话框。单击“公式”标签,在对应的右侧窗格中向下滑动滑块至“计算选项”列表框下,勾选“启用迭代计算”复选框,并在复选框下方设置“最多迭代次数”为100,设置“最大误差”的值为0.001,然后单击“确定”按钮完成设置,如图10-49所示。

勾选“启用迭代计算”复选框

图10-49 勾选“启用迭代计算”复选框

Excel 切换引用

在Excel中进行公式编辑时,常常会根据需要在公式中使用不同的单元格引用方式。通常情况下用户会按老套的方法进行输入,这种方法不仅浪费时间,工作效率降低,同时准确度也会随之下降。这时可以用如下方法来快速切换单元格引用方式。

选中包含公式的单元格,在编辑栏中选择要更改的引用单元格,按“F4”键就可以在相对引用、绝对引用和混合引用间快速切换。

例如,选择“A2”引用,按一次“F4”键时,就会变成$A$2;连续按两下“F4”键时,就会变成A$2;连续按3次“F4”键,就会变成$A2;连续按4次“F4”键,就会变成A2。

只要使用“F4”键即可轻松地在$A$2、A$2、$A2、A2之间进行快速切换。

Excel 更改其他单元格引用

在单元格被引用后通常情况下也会有变动。如何将单元格引用更改为其他单元格引用?其具体操作步骤如下。

双击包含希望更改公式的单元格,使单元格公式处于可编辑状态。然后执行下列操作之一:

如果要将单元格或区域引用更改为其他单元格或区域,则将单元格或单元格区域的彩色标记边框拖动到新的单元格或单元格区域上即可。

如果要在引用中包括更多或更少的单元格,则拖动边框的一角,增大或减小单元格区域的选择即可。

在公式编辑栏中,以公式形式选择引用,然后输入一个新的引用,按“Enter”键返回即可。对于数组公式,则按“Ctrl+Shift+Enter”组合键返回结果。

Excel 引用其他单元格

在进行公式运算时,很多情况下都需要使用其他工作表中的数据来参与计算。在引用其他工作表单元格中的数据时,通常的格式引用是:‘工作表名’!数据源地址。

STEP01:打开“总销售量统计.xlsx”工作簿,切换至“上半年总销售量”工作表,选中要引用其他工作表的单元格,这里选择B2单元格。在该单元格中输入函数“=SUM(”,如图10-43所示。

STEP02:单击“1-3月份销售量”工作表标签,选中要参与计算的单元格或单元格区域,这里选择“B2:D2”单元格区域,如图10-44所示。

STEP03:完善公式,在公式文本框中输入“)”,然后按“Enter”键返回即可得出计算结果,此时的工作表如图10-45所示。

STEP04:选中B2单元格,利用填充柄工具向下拖动鼠标,复制公式至B6单元格,结果如图10-46所示。

图10-43 输入部分公式

选择被引用的单元格

图10-44 选择被引用的单元格

完善公式

图10-45 完善公式

图10-46 复制公式

Excel 相对与绝对引用图解

单元格的引用有两种,一种是相对引用方式,一种是绝对引用方式。

相对引用

相对引用单元格的方法非常简单,接下来通过一个实例说明相对引用方式的方法。

STEP01:打开“单元格引用数据.xlsx”工作簿,切换至“相对引用.xlsx”工作表,在D2单元格中输入公式“=B2+C2”,按“Enter”键返回即可得到如图10-37所示的计算结果。

计算总分

图10-37 计算总分

图10-38 复制公式

STEP02:选中D2单元格,利用填充柄工具向下拖动鼠标,复制公式至D6单元格,结果如图10-38所示。

STEP03:选择复制得到公式的单元格,例如D5单元格,此时发现D5单元格中的公式为“=B5+C5”,数据源自动改变了,如图10-39所示。

绝对引用

单元格的绝对引用指的是把公式复制或移动到其他位置时,公式中的固定单元格地址保持不变。如果要对单元格采用绝对引用的方式,则需要使用“$”符号为标识。接下来还是通过一个案例说明如何对单元格进行绝对引用。

STEP01:切换至“绝对引用”工作表,选中D2单元格,在D2单元格中输入公式“=$B$2*C2”,按“Enter”键返回,即可得到如图10-40所示的计算结果。

STEP02:选中D2单元格,利用填充柄工具向下拖动鼠标,复制公式至D5单元格,结果如图10-41所示。

STEP03:选择复制得到公式的单元格,例如D5单元格,此时发现D5单元格中的公式为“=$B$2*C5”,说明绝对引用时地址不变,如图10-42所示。

图10-39 数据源自动改变

绝对引用计算结果

图10-40 绝对引用计算结果

图10-41 复制公式

绝对引用时地址不变

图10-42 绝对引用时地址不变

Excel 单元格引用样式

A1引用样式

默认情况下,Excel使用A1引用样式该样式使用数字1~65536表示行号,用字母A~IV表示列标。例如,第C列和第5行交叉处的单元格的引用形式为“C5”,如果引用整行或者整列,可以省去列标或者行号,比如1:1表示第1行。

R1C1引用样式

在工作表中切换至“文件”选项卡,在左侧导航栏中单击“选项”标签打开“Excel选项”对话框,单击“公式”标签,在对应的右侧窗格中向下滑动滑块至“使用公式”列表框下,勾选“R1C1引用样式”复选框,单击“确定”按钮即可完成设置,如图10-35所示。用R1C1引用样式,可以使用“R”与数字的组合来表示行号,“C”与数字的组合则表示列标。R1C1样式可以更加直观地体现单元格的“坐标”概念。

三维引用计算结果

图10-36 三维引用计算结果

三维引用

引用单元格区域时,冒号表示以冒号两边所引用的单元格为左上角和右下角之间的所有单元格组成的矩形区域。

当右下角单元格与左上角单元格处在同一行或者同一列时,这种引用称为一维引用,如A1:D1,或者A1:A5。而类似A1:C5,则表示以A1单元格为左上角,C5单元格为右下角的5行3列的矩形区域,这就形成了一个二维的面,所以该引用称为二维引用。

当引用区域不只在构成二维平面的方向出现时,其引用就是多维的,是一个由不同层次上多个面组成的空间模型。

打开“三维引用数据.xlsx”工作簿,在“Sheet1”工作表的E8单元格中输入公式“=SUM(Sheet1:Sheet3!A1:C5)”,表示对从工作表Sheet1到Sheet3的A1:C5单元格区域求和,按“Enter”键即可返回计算结果,如图10-36所示。在此公式的引用范围中,每个工作表的A1:C5都是一个二维平面,多个二维平面在行、列和表3个方向上构成了三维引用。