Excel-函数——查找和引用函数

第5章查找和引用函数在Excel中，查找和引用函数的主要功能是查询各种信息。在数据量很大的工作表中，Excel的查找和引用函数能起到很大的作用。在实际应用中，查找和引用函数会和其他类型的函数一起综合应用，完成复杂的查找或者定位。在本章中，将根据函数的性质，分为查找和引用函数两个小节详细讲解。5.1查找函数查找函数的主要功能是快速的确定和定位所需要的信息。这类函数的主要功能是检索：根据实际需要，在工作表或者在多个工作薄中获取需要的信息或者数据。在本小节中，将详细讲解各个函数的功能。5.1.1ADDRESS函数：返回引用地址【功能说明】该函数根据给定的行号和列号，返回某一个具体的单元格的地址。【语法表达式】ADDRESS(row-num，column-num，abs-num，a1，sheet-text)【参数说明】zRow-num：代表行号，表示单元格在哪一行的数字。例如，单元格D2，就表示其在第2行。zColumn-num：代表列标号，表示单元格在哪一列的数字。例如，单元格D2，就表示其在D列。zAbs-num：代表引用类型。使用函数时，其值可以是1、2、3、4中的任意一个值。该参数也可以省略。如果省略该参数，系统将其默认为1。数字和其代表的引用类型的关系，如表5.1所示。表5.1数字和代表类型数字引用类型1代表绝对引用2代表绝对行号，相对列标3代表相对行号，绝对列标4代表相对引用zA1：代表引用样式的逻辑值。如果参数a1为TRUE或者省略，那么函数将返回a1样式的引用；如果a1为FALSE，那么函数将返回R1C1样式的引用。zSheet-text：代表一文本，指定作为外部引用时的工作表名称。如果省略该参数，那么表示不使用任何工作表名。【使用说明】如果参数a1是指定的四个数字之外的任意数字，函数都将返回错误值#value!。【实际应用】某公司组织员工进行某种游戏，现在有员工编号和游戏序号。根据昀后得奖序号来判断员工编号的单元格。在单元格E2中输入公式“=ADDRESS(3,1,1)”，得到得奖员工编号所在单元格，如图5.1所示。图5.1获取得奖单元格【应用说明】在Excel中，引用类型有绝对引用、相对引用、混合引用三种类型。引用样式有a1和R1C1两种。5.1.2AREAS函数：返回区域个数【功能说明】给定一个引用，利用AREAS函数计算该引用中包含的区域的个数。该引用区域可以是连续的单元格区域，也可以是某个具体的单元格。【语法表达式】AREAS(reference)【参数说明】Reference：代表对单元格或单元格区域的引用。使用函数是，其值可是是单个单元格，也可以是单元格区域。如果需要引用多个不连续的单元格区域，那么需要用括号把各引用区域引起来。【使用说明】在引用多个不连续的单元格区域时，一定要用大括号把引用区域括起来。【实际应用】某总公司将各个不同部门的销量统计在不同的单元格范围内，现在需要统计公司所有的区域总数。在单元格B2中输入“=AREAS((A4:B11,D4:E11,A15:B21,D14:E21,G14:H21))”，计算的区域总数结果如图5.2所示。图5.2返回区域个数【应用说明】单元格也是小区域，因此AREAS函数也可以统计单元格的个数。该函数还可以应用到经济销售的其他领域，用来统计销售项目个数或者销售区域总数。5.1.3CHOOSE函数：从列表中选择数值【功能说明】该函数利用给定的参数值，返回数值参数列表中的一个值。列表中的数值个数介于1-29这29个数值之间。【语法表达式】CHOOSE(index-num,value1,value2…)【参数说明】zIndex-num：用来指定所选定的数值参数，其中Index-num的参数必须是介于1-29之间的数字表达式或者单元格字段。这里包括了三种情况：（1）当Index-num的值为1时，CHOOSE函数会返回Value1的值；为2时，会返回Value2的值；依次类推。（2）当Index-num的值是一个小数，那么按四舍五入取整后，再返回（1）。（3）当Index-num的值小于1或者大于列表中昀后一个值的序列号，函数将返回一个错误值#VALUE！。zValue1，value2……为1到29个数值参数。函数根据Index-num提供的参数值，从中选择一个数值或者一项要执行的操作。这些数值参数可以是数值表达式、列表、公式、单元格引用、定义名称、函数或者文本。【使用说明】zIndex-num中的参数可以是数值表达式，也可以是字段，其运算结果是一个介于1和29之间数值。如果Index-num是一个数组，则在计算时，每一个值都会参与计算。z数值参数间需要用逗号隔开。【实际应用】某小公司中包含了多个员工的薪酬信息，根据不同的工资等级，公司需要为这些员工选取不同的奖金比例等级。在单元格C2中输入函数表达式“=CHOOSE(INT(B2/3000),4%,8%,12%)，计算单元格B2中工资额的奖金比例，然后利用自动填充功能，计算其他工资额的奖金比例，结果如图5.3所示。图5.3选取奖金比例【应用说明】CHOOSE函数的数值参数还可以为区域被引用。5.1.4COLUMN函数：返回列号【功能说明】该函数用来返回指定引用的列号。列号的范围是1~256之间的任意整数。【语法表达式】COLUMN(reference)【参数说明】表示单元格，或单元格区域。也就是，一个单元格，或单元格区域。【使用说明】z如果省略参数reference，那么返回函数COLUMN所在单元格的列号。z如果参数reference是单元格区域，且函数COLUMN以水平数组的方式输入，那么函数将返回位于单元格区域首列的单元格的列号。【实际应用】某公司统计了三个部门的员工编号，下面需要通过员工编号判断所在部门。在单元格E3中输入“=COLUMN(C6)”，判断员工编号314所在的部门，如图5.4示。图5.4确定员工所在的部门【应用说明】参数reference只能引用一个单元格区域，不能同时引用多个单元格区域。5.1.5COLUMNS函数：返回引用的列数【功能说明】计算引用的单元格区域包含的列数，返回的结果是1~256之间的数值。【语法表达式】COLUMNS(array)【参数说明】array：代表要计算其列数的数组或数组公式，可以是引用的单元格区域。【使用说明】该函数不能省略参数。【实际应用】某公司统计了各部门的员工编号，现在需要统计部门总数。在单元格E3中输入“=COLUMNS(A2:C10)”，结果如图5.5示。图5.5返回引用区域的数目【应用说明】如果参数是单元格、单元格区域、数组、数组公式以外的任何值，函数将返回错误值#Value！。5.1.6HLOOKUP函数：实现水平查找【功能说明】在数据表或数值数组的首行查找指定的数值，并在数据表中指定行的同一列中返回一个数值。【语法表达式】HLOOKUP(lookup-value，table-array，row-index-num，range-lookup)【参数说明】zLookup-value：表示需要在数据表第一行中查找的数值。该参数可以是数值、引用或文本字符串。zTable-array：表示需要在其中查找数据的数据表。该参数可以引用单元格区域或区域名称。第一行的数值可以为文本、数字或逻辑值，文本不区分大小写。zRow-index-num表示行号，也就是在table-array中待返回的表格中相匹配的值的行序号。zRange-lookup：表示逻辑值，用来指明函数HLOOKUP查找时是精确匹配，还是近似匹配。当range-lookup值为TRUE，或省略该参数时，函数将返回近似匹配值。也就是，如果函数找不到精确匹配值，那么，函数将返回小于lookup-value的昀大数值；当其值为FALSE时，那么函数会查找精确匹配值，否则，函数将返回错误值#N/A！。【使用说明】zHLOOKUP函数中H表示“行”。z如果range-lookup值为TRUE，那么table-array的第一行的数值必须按升序排列，如……-2、-1、0、1、2……A-Z、FALSE、TRUE；否则，该函数将找不到能匹配的数值；如果其值为FALSE，那么，table-array可以不用排序。z使用函数时，如果参数row-index-num值小于1，那么函数将返回错误值#VALUE！。z如果参数row-index-num的值大于table-array的行数时，函数将返回错误值#REF！。z在函数计算时，如果HLOOKUP找不到lookup-value，且range-lookup的值为TRUE，那么函数返回小于lookup-value的昀大值；如果HLOOKUP小于table-array第一行中的昀小数值，函数将返回错误值#N/A！。【实际应用】某厂商销售A、B和C三种商品，在销售单上，用户需要获取商品的单价。在单元格C5中输入函数“=HLOOKUP(A5,$A$1:$C$2,2,FALSE))”，选取商品A单价，然后利用自动填充功能，得到其他项目的单价，如图5.6所示。图5.6选取单价信息【应用说明】当比较值位于数据表的首行，并且要查找下面给定行中的数据时，可以使用此函数。当比较值位于要查找的数据左边的一列时，则要使用VLOOKUP函数。5.1.7HYPERLINK函数：创建跳转【功能说明】利用该函数可以创建一个快捷方式，也就是页面跳转。【语法表达式】HYPERLINK(link-location，friendly-name)【参数说明】zLink-location：表示文档的路径和文件名，可以把这个文档当作文本打开。该参数还可以为文档中某一更具体的位置指定路径，路径可以是存储在硬盘驱动器上的文件，也可以是服务器上的“通用命名规范”路径，还可以是“统一资源定位符”。zFriendly-name：表示单元格中显示出来的跳转文本值或数字值，单元格的内容呈蓝色并带有下划线；如果省略该参数，Link-location在单元格里将作为跳转文本显示。【使用说明】zLink-location可以是被双引号括起来的文本字符串，也可以是包含了文本字符串链接的单元格。zFriendly-name可以是数值、文本字符串、名称或包含跳转文本或数值的单元格。如果该参数返回错误值#VALUE！，那么单元格就是显示错误值以代替跳转文本。z如果需要选定一个包含超链接的单元格并且不跳往超链接的目标文件，可以单击单元格区域，同时按住鼠标按钮，直到光标变成一个十字，然后释放鼠标按钮即可。【实际应用】某公司统计了员工的信息，但是公司将其年龄设置为超连接。在单元格C2中输入公式“=HYPERLINK(30岁,年龄)”，设置第一名员工的年龄超连接。然后依次输入其他员工的超链接公式，结果如图5.7所示。图5.7查看超连接结果【应用说明】通过创建的快捷方式可以打开存储在网络服务器和Internet中的文件。单击HYPERLINK函数所在的单元格时，表格能打开存储在link-location中的文件。5.1.8LOOKUP函数：查找数据【功能说明】从单行或者是单列的单元格区域中，或者从一个数组中，返回需要查找的值。它有两种形式：向量形式和数组形式，都只包含一行或一列区域。【语法表达式】向量LOOKUP(lookup-value，lookup-vector，result-vector)数组LOOKUP(lookup-value，array)【参数说明】在向量LOOKUP中：zLookup-value：表示函数在第一个向量中搜索的值。该参数可以是数字、文本、逻辑值、名称，也可以引用值。zLookup-vector：只包含一行或一列的区域。该参数可以是文本、数字或者逻辑值。设置参数的值时，必须以升序顺序放置，否则函数可能无法提供正确值。zResult-vector：也只包含一行或一列区域，而且，大小必须与lookup-vector一致。在数组LOOKUP中：zlookup-value：表示函数在数组中搜获的值。使用函数时，其值可以是数字、