|
★ 编按 ★ 公式中有单元格的引用才能使公式具有更强的可变性，如果引用的单元格可以随着某些条件的变化而变化，就会使公式的功能更加强大，这就需要使用引用类函数来实现。下面一起来学习下几种常用的引用类函数。 1.CHOOSE()函数—根据情况选择值或单元格区域 在某些公式中，由于条件不同，需要使用不同的值，或者引用不同的单元格区域，这时候就可以使用CHOOSE()函数来实现选择。 其语法格式为CHOOSE（index_num,value1,[value2],…）。 从语法结构中可以看出，该函数包含两个必选参数，各参数意义为： index_num：指定要选择的参数的序号，必须为1-254之间的整数。 value1、value2…：返回值列表，可以是单元格引用或名称、公式等，CHOOSE()函数将根据其排序，选择第index_num个参数作为函数返回值。 应用案例 客户消费评级 某公司要根据客户的消费金额将客户划分为5个等级。要按小于1000、1000-3000、3000-10000、10000-50000和大于50000的区间，分别划分为铁牌、铜牌、银牌、金牌、钻石5个等级。 【分析】可以使用CHOOSE()函数根据一个索引号在参数列表中选取一个值，这个参数列表就可以定义这5个等级的文本。 然后使用MATCH()函数进行模糊匹配，得到代表这5个区间的序号。再用CHOOSE()函数返回对应的等级文本。  扫码入群，下载Excel练习文件 【具体步骤】 在D2单元格中输入公式“=CHOOSE(MATCH(C2,{0,1000,3000,10000,50000},1),"铁牌","铜牌","银牌","金牌","钻石")”，按“Enter”键完成公式输入，然后向下填充，判定等级。  本例公式，是由MATCH()函数和CHOOSE()函数嵌套组成。首先通过MATCH()函数将D列单元格中具体数值转换为每个区间代表的序号。然后将这个序号作为CHOOSE()函数的index_num参数，用于从后面的参数列表中选择输出符合条件的文本。 2.HYPERLINK()函数—利用超链接快速跳转到其他位置 超链接在网页中是非常常见的，它在Excel表格中的用途也非常广泛。尤其是在数据非常多的表格中，用超链接导航可以省去很多数据查找的时间。这就需要HYPERLINK函数来实现。 该函数的语法结构为：HYPERLINK（LINK_LOCATION，[FRIENDLY_NAME]）。 该函数一共就两个参数： LINK_LOCATION：代表链接的地址；FRIENDLY_NAME：代表显示的标题。 应用案例 链接到当前工作表的指定区域 要求点击A3单元格能够跳转到D3单元格。 在A3单元格输入“=HYPERLINK("#D3","D3单元格")”。 其中“D3”代表链接的地址，“D3单元格”代表显示的标题。第一个参数加“#”这是固定用法，朋友们记住就行。最后摁“Enter”键完成输入。单击A3单元格，就会跳转到D3单元格。  其实，HYPERLINK函数还有链接到文件夹、链接到文件、链接到当前工作薄中其他工作表的指定区域、链接到网页等功能，非常简单，朋友们可以自己练习。 3.INDIRECT()函数——文本表示的引用也能返回正确的值 在某些公式中需要引用的单元格并不确定，或者这些单元格较为特殊，无法直接引用其地址。 此时也可使用文本来“描述”单元格，并最终将以文本表示的单元格地址转换为公式可引用的单元格地址。这种情况可以使用INDIRECT()函数来完成。 其语法结构：INDIRECT(ref_text,[a1])。 ref_text：以文本形式表示的对单元格的引用。 a1：指定包含在单元格ref_text中引用样式的逻辑值。当参数取值TURE或省略时，默认采用A1引用样式，当参数取值为FALSE时，采用R1C1引用样式。 应用案例 数据汇总 某公司每月都会制作一张月度销售表，年终需要将这些表格中数据汇总到一张表中，并从12张表中引用数据。 观察表格的结构可以发现，每张工作表的名称都在汇总的表格中列举了出来。此时，我们可以使用INDIRECT()函数和ROW()函数完成数据引用。  【具体步骤】 在“汇总”工作表中的C4单元格输入公式“=INDIRECT(C$3&"!C"&ROW()-1,TRUE)”，摁“Enter”键完成输入。 向右拖动单元格右下角的自动填充柄至N4单元格，分别引用了12张工作表中C3单元格的值。 保持选中状态，向下填充公式至11行，引用所有数据。 【分析】 公式先获取C3单元格的值“1月”，这个1月和工作表的名称“1月”相同，即要引用的工作表名称。 然后通过连接符连接“！C”文本，引用工作表通常使用感叹号连接，并且引用的单元格需要加上双引号,表示引用C3单元格所在的工作表中C列内容。 接着通过“ROW()-1”取得行号，即目标工作表中当前行号上一行的内容，最后通过INDIRECT()函数将连接起来的文本转换为A1引用样式引用的单元格。  4.OFFSET()函数——根据指定偏移量选择单元格区域 如果要以某个单元格为基准，引用与之距离指定行列数以后的单元格或单元格区域，则可以利用OFFSET()函数来完成。 其语法格式：OFFSET(reference,rows,cols,[height],[width])。 各参数意义： reference：作为偏移量参照系的引用。也称为基准单元格，必须为对单元格或相连单元格区域的引用。 rows：相对于基准单元格向上（Rows取负值）或向下（Rows取正值）偏移的行数。 cols：相对于基准单元格向左（cols取负值）或向右（cols取正值）偏移的列数。 height：要返回的引用区域的行数，必须是正整数。 width：要返回的引用区域的列数，必须是正整数。 OFFSET()函数并不会改变任何单元格或更改选定区域，它只是返回一个引用。函数参数中，除第一个参数外，其它4个参数都必须是整数。 OFFSET()函数是比较难理解的函数，下面就详细介绍这个函数。 ①将A1单元格内容克隆到D1单元格，在D1单元格输入=OFFSET(A1,0,0)，D1单元格显示“A”。  ②将OFFSET(A1,0,0)第二个参数0改成1，即改成=OFFSET(A1,1,0)，写入D1单元格中，D1单元格显示“C”。  ③将OFFSET(A1,0,0)第二个参数0改成2，即改成=OFFSET(A1,2,0)，写入D1单元格中，D1单元格显示“E”。  相信大家已经看明白了，OFFSET(A1,2,0)的第二个参数，是以A1为基准，向下移动几个单元格。而第一个参数就是基准单元格。另外，第三个参数写几，以基准单元格向右移动几个单元格。 ④以A1为基准，在D1单元格中克隆B3的内容，应该怎么写？答案是在D1单元格输入“=OFFSET(A1,2,1)”。  但是，OFFSET一共有5个参数，我们刚才只用了前3个参数，下面来说说剩下两个的用法。 ⑤要求使用OFFSET函数一次性克隆A1和B1到D1和E1。  选择D3和E3单元格，在公式栏写入“=OFFSET(A1,0,0,1,2)”，按下快捷键<Ctrl+Shift+Enter>变成数组形式，这就得到所需要的结果。  到这里，大家应该领悟到第五个参数的真谛了吧。第五个参数是2，就是返回以第一个参数A2单元格为基准，横向两个单元格的内容，输出单元格也要同时选择横向两个单元格。不然，输出只选一个单元格会放不下，它就会报错。 ⑥如何利用OFFSET一次性克隆A1:B3区域到D1:E3区域？选中D1:E3区域，在公式栏输入“=OFFSET(A1,0,0,3,2)”, 按下快捷键<Ctrl+Shift+Enter>变成数组形式，这就得到所需要的结果。 OFFSET（以A1单元格为基准，0，0，返回横向2个单元格区域，返回纵向3个单元格区域）。第四个参数是克隆显示纵向的单元格数量。这就是OFFSET()函数中5个参数的原理。 OK，今天我们学习了很多引用函数，包括：CHOOSE()函数、HYPERLINK()函数、INDIRECT()函数、OFFSET()函数，这些函数相对来说较难理解，但在EXCEL工作中都会用到，小伙伴们，请一定要掌握。还有别的什么想法，欢迎留言。 今日互动 在评论区留下你的足迹叭~ 你是什么行业，工作中应用最多的函数是什么？ |
|
|
