excel学习库

Excel中查找与引用函数的使用 ▼表 3-1 查找数据 函数名称 功能 CHOOSE 根据指定的索引值，在参数中查找相应值或操作 HLOOKUP 在首行查找数据，并返回选定列中指定行处数值 INDEX 返回指定行列交叉处的单元格的值（数组形式） INDEX 返回指定行列交叉处的单元格引用（引用形式） LOOKUP 在单行或单列中查找数据（向量形式） LOOKUP 在数组中查找数据（数组形式） MATCH 返回指定内容在数组中的相对位置 VLOOKUP 在首列查找数据，并返回选定行中指定列出数值 ▼表 3-2 引用数据 函数名称 功能 ADDRESS 以文本的形式创建某一单元格的引用位置 AREAS 返回引用中包含的区域个数 COLUMN 返回某一引用的列号 COLUMNS 返回某一引用或数组的列数 03 查找与引用函数 Excel 2013提供了 19个查找与引用函数，它主要实现两大 功能：在工作表中查找符合一定条件的数据、引用工作表中的 数据。因此我们把 19个函数按功能分为查找数据函数和引用数 据函数两类。它可以与其他多个函数组合使用，灵活方便。下 面将对函数分类及其具体应用进行介绍。 68 （续表） FORMULATEXT 以字符串的形式返回公式 GETPIVOTDATA 提取存储在数据透视表中的数据 HYPERLINK 为指定内容创建一个快捷方式或者超链接 INDIRECT 返回由文本字符串指定的引用 OFFSET 以指定的引用为参照系，通过指定偏移量得到新 的引用 ROW 返回某一引用的行号 ROWS 返回某一引用或数组的行数 RTD 从支持 COM自动化程序中提取数据 TRANSPOSE 转置单元格区域 69 CHOOSE 根据指定的索引值，在参数中查找相应值或操作 函数格式： CHOOSE(index_num, value1, value2, ...) 参数说明： index_num：该参数为必需选项。用于指定所选定的数值参 数。 该参数必须是介于 1 到 254 之间的数字，或是包含 1 到 254 之间的数字的公式或单元格引用。 value1：该参数为必需选项，表示第一个数值参数，可以 是数字、文本、引用、名称、公式或函数。 value2 ...：该参数为可选选项，表示第 2~254个数值参数。 注意事项： 1.若参数 index_num 为 1，则 CHOOSE 函数返回参数 value1，若参数 index_num 为 2，则 CHOOSE 函数返回参数 value2，并以此类推。 2.若参数 index_num小于 1或大于 254或文本，则CHOOSE 函数返回错误值#VALUE!。 3.若参数 index_num为小数，则会在使用前将其截尾取整。 例如 参数 index_num是 2.1，CHOOSE函数将 2.1作为 2对待。 4. value可以是数字、文本、引用、名称、公式或函数。 例如公式形式的参数：=CHOOSE（A3*3，B2/2，B3/5， B4/7），此公式先计算 A3*3 的值，再根据结果返回 value 列表 中对应的值。 查找数据 70 使用 CHOOSE函数，用逗号分隔各数值，并返回参数值指定 位置的数据值。如果没有用于检索上网其他表，则会把检索处理 存储下来。此案例我们用数字 1,2,3…代表行政部、销售部等部门， 下面介绍使用 CHOOSE函数检索员工所在部门。 案例 检索员工所在部门 ①选中要输入函数的单 元格 ②单击【插入函数】按钮， 在【插入函数】对话框中 选择函数【CHOOSE】 ③设置参数，然后单 击【确定】按钮 ④单元格 D2 中显示 了与单元格 D2 中代 码一致的部门名称 71 ⑤选中 D2，将鼠标移 动到其右下角，然后 向下拖动鼠标到，将 公示填充到 D10 函数格式： HLOOKUP(lookup_value,table_array,row_index_num,range_l ookup) 参数说明： lookup_value：此参数为必需选项，表示要在表格的第一行 中查找的值。它可以是数值、引用或文本字符串。 table_array：此参数为必需选项，表示在其中查找数据的数 据表。 row_index_num：此参数为必需选项，表示 table_array 中待 返回的匹配值的行号。假如该参数为 2，table_array 将返回第 5 行的值；若该参数为 7，table_array将返回第 7行的值。 range_lookup：此参数为可选选项，它是一个逻辑值，表示 是精确查找或模糊查找。 注意事项： 1. range_lookup为 TRUE或省略时，表示模糊查找，返回小 于等于 lookup_value 的最大值。；若为 FALSE，表示精确查找， 如果找不到精确匹配值，则返回错误值 #N/A。 2.如果 range_lookup 为 TRUE，则 table_array 的第一行的 数值必须按升序排列；否则，HLOOKUP函数将不能给出正确 HLOOKUP 在首行查找数据，并返回选定列中指定行处数值 查找数据 72 下面以"根据奖金标准，统计销售人员奖金数量"为例，介 绍 HLOOKUP函数的具体应用。 的数值。如果 range_lookup 为 FALSE，则 table_array 不必进行 排序。 3.如果参数 row_index_num小于 1，HLOOKUP 函数将返回 错误值#VALUE!；如果参数 row_index_num 大于引用区域或数 组中的行数，HLOOKUP 函数将返回错误值#REF!。 4.当查找文本且 range_lookup 为 FALSE 时，则可以在 lookup_value 中使用通配符问号 (?) 和星号 (*)。问号匹配任意 单个字符；星号匹配任意一串字符。如果要查找实际的问号或 星号，请在字符前键入波形符 (~)。 案例 查找销售人员奖金所得 ②单击【插入函数】 按钮，在【插入函数】 对话框中选择函数 【HLOOKUP】 ①选中目标单元格 ③设置参数，然后单 击【确定】按钮 73 ④根据单元格 D7 的 值，返回与该值匹配 的当前行指定列处的 值 ⑤将公示填充到单元 格区域 E8:E10 INDEX（数组形式） 返回指定行列交叉处的单元格的值（数组形式） 查找数据 函数格式： INDEX(array, row_num, column_num) 参数说明： array：该参数为必选参数，表示要返回值得单元格区域或 数组。 row_num：该参数为必需参数，表示返回值所在的行号。 column_num：该参数为可选参数，表示返回值所在的列号。 注意事项： 1.如果数组只包含一行或一列，则相对应的参数 Row_num 74 下面以在销量统计表中查找各个商品在各个季度的销量为 例，介绍 INDEX函数的数组形式的使用方法。 案例 查找产品季度销售量 ②单击【插入函数】 按钮 ①选中需要输入函数 的单元格 或 Column_num 为可选参数；如果数组有多行和多列，但只使 用 Row_num 或 Column_num，函数 INDEX 返回数组中的整 行或整列，且返回值也为数组。 2.如果省略 Row_num，则必须有 Column_num。如果省略 Column_num，则必须有 Row_num。 3.若同时使用参数 Row_num 和 Column_num，函数 INDEX 返回这两个参数交叉处的单元格中的值。 4.若要使用以数组形式返回的值，请将 INDEX 函数以数组 公式形式输入，对于行以水平单元格区域的形式输入，对于列 以垂直单元格区域的形式输入。若要输入数组公式，请按 Ctrl+Shift+Enter。 75 INDEX（引用形式） 返回指定行列交叉处引用的单元格（引用形式） 查找数据 ⑥在 B13 中显示了以 B11为行号，B12为列 号的交叉位置的值 ⑤在【选定参数】对 话框中选择第一种参 数形式 ④在【选择函数】列 表框中选择【INDEX】 函数，然后单击【确 定】按钮 ③在【或选择类别】 下拉列表中选择【查 找与引用】选项 76 该案例中统计了两个销售小组的产品的销量，下面介绍使用 引用形式的 INDEX函数，查找商品的销量。 函数格式： INDEX(reference, row_num, column_num, area_num) 参数说明： reference：该参数为必选参数，表示查找范围，即对一个或 多个单元格区域的引用。 row_num：指定函数的返回值内容。从首行数组开始查找， 指定返回第几行的行号。 column_num：指定函数的返回值内容。从首行数组开始查 找，指定返回第几列的列号。 area_num：选择引用中的一个区域，并从区域中返回 row_num 和 column_num 的交叉区域。 注意事项： 1.参数 row_num、column_num 和 area_num 表示的引用必 需位于参数 reference 的范围内；否则，INDEX 返回 错误值 #REF!。如果省略参数 row_num 和 rolumn_num，函数 INDEX 返回由 Area_num 所指定的引用中的区域。 2.如果将参数 row_num 或 column_num 设置为 0，函数 INDEX 分别返回对整列或整行的引用。 3.若参数 reference引用的区域为（A1:C7,E1:G7,I1:K7），则 area_num1 表示区域 A1:C7，area_num2 表示区域 E1:G7。参数 area_num可以省略，如果省略，则函数使用区域 1。若指定值小 于 1，则返回错误值#VALUE! 案例 查找产品的销售量 77 在公式 =INDEX((A3:B6,D3:E6),B8,B9,1)中，" A3:B6"为 area_num1，"D3:E6"为 area_num2，"1"表示选择引用中的第一 个区域，所以此公式将返回第一个区域 A3:B6中，第 3行，第 2 ③在【选定参数】对 话框中选择第二中参 数格式 ④在【函数参数】对 话框中设置参数，然 后单击【确定】按钮 ⑤以 B8 为行号，以 B9为列号，显示了单 元格交叉位置的值 ②单击【插入函数】 按钮 ①选中需要输入公式 的单元格 78 列交叉位置的单元格的值。 LOOKUP（向量形式） 在单行或单列中查找数据（向量形式） 查找数据 函数格式： LOOKUP(lookup_value, lookup_vector, result_vector) 参数说明： lookup_value（必需）：表示要查找的值。若在查找区域中 找不到该值，则返回由参数 lookup_vector 指定区域或数组中小 于等于查找值得最大值。可以是数字、文本、逻辑值、名称或 对值的引用。 lookup_vector（必需）：表示要在其中查找的区域或数组。 如果该参数指定的是区域，则必须为单行或单列；如果是数组， 则必须为水平或垂直的一维数组。 result_vector（可选）：表示指定函数返回值的单元格区域， 该参数可以是区域或数组，但是其大小必须与参数 lookup_vector 一致。 注意事项： 1. 参数 lookup_vector表示的查找区域或数组中的数据必须 按升序排列，排序规则为：数字<字母<FALSE<TRUE。如果查 找前未排序，那么函数可能会返回错误结果。 2.如果要查找的值 (lookup_value)小于查找区域或数组 (lookup_vector)中最小的值，那么函数会返回错误值#N/A!。 3. 参数 lookup_vector和参数 result_vector必须为相同方向 的，即如果查找方向为行方向，那么返回结果的区域就不能是 列方向上的。 79 在单元格 E2 中输入公式：=LOOKUP(E1,A1:A8,B1:B8)，然 后按下【Enter】键即可显示出查找结果。 案例 根据编号查找员工姓名 在单元格 E2 中显示 结果 LOOKUP（数组形式） 在数组中查找数据（数组形式） 查找数据 函数格式： LOOKUP(lookup_value,array) 参数说明： lookup_value（必需）：表示要在区域或数组中查找的值， 可以是数字、文本、逻辑值、名称或对值的引用。 array（可选）：表示要在其中查找数据的区域或者数组。 注意事项： 1.参数 array表示的查找区域或数组中的数据必须按升序排 列，如果查找前未排序，那么函数可能会返回错误结果。 2.如果要查找的值 (lookup_value)小于查找区域或数组 (lookup_vector)中最小的值，那么函数会返回错误值#N/A!。 3.若单元格区域或数组中的列数大于行数，那么函数将在第 一行中查找 lookup_value的值；若列数小于等于行数，那么行数 将在第一列中进行查找。 80 在单元格 H2 中输入公式=LOOKUP(H1,A1:E10)，然后按下 【Enter】键即可显示出查找结果。此公式表示在单元格区域 A1:E10 中，查找员工"王宁"的学历。 在单元格 H2 中显示 查找结果 MATCH 返回指定内容在数组中的相对位置 查找数据 函数格式： MATCH(lookup_value, lookup_array, match_type) 参数说明： lookup_value（必需）：表示要在区域或数组中查找的值， 可以为值（数字、文本或逻辑值）或对数字、文本或逻辑值的 单元格引用。 lookup_array（必需）：表示要在其中查找数据的连续的单元 格区域。 match_type（可选）：表示查找方式。 下面我们用表格形式说明参数 match_type 取不同值时，函 数MATCH的返回值。 案例 根据员工姓名查找员工学历 81 该案例中我们要查找某销量在销量统计表中的相应位置。选 中单元格 F3，输入公式=MATCH(F2,C3:C11,1)，然后按下【Enter】 键，即可显示出查找到的位置。 案例 根据编号查找员工姓名 ▼表 3-3 参数 match_type 与 MATCH match_type MATCH返回值 1或省略 模糊查找，返回小于等于参数 lookup_value 的最大 值的位置，查找区域（lookup_array）需按升序排列 0 精确查找，翻译等于查找区域中第一个与参数 lookup_value相等的位置，查找区域（lookup_array） 无需排序 -1 模糊查找，返回大于等于参数 lookup_value 的最小 值的位置，查找区域（lookup_array）必按降序排列 注意事项： 1.如果参数为文本，MATCH函数将不区分大小字母。 2.当使用模糊查找方式时，若查找区域或数组未按顺序排 序，MATCH函数可能返回错误的结果。 3.如果 match_type 为 0且 lookup_value为文本字符串，可 在 lookup_value参数中使用通配符问号 (?) 和星号 (*) 。问号 匹配任意单个字符；星号匹配任意一串字符。如果要查找实际 的问号或星号，请在字符前键入波形符 (~)。 82 该公式表示在单元 格区域 C3:C11 查找 小于或等于660的最 大值 match_type 值为 1 时，查找区域按升序 排序 VLOOKUP 在首列查找数据，并返回选定行中指定列出数值 查找数据 函数格式： VLOOKUP(lookup_value,table_array,col_index_num, range_lookup) 参数说明： lookup_value（必需）：表示要在区域或数组中的首列查找 的值。它可以是数值或者单元格引用。 table_array（必需）：表示要在其中查找数值的区域或数组。 col_index_num（必需）：参数 table_array返回的匹配值的列 号。例如若该参数为 1，则参数 table_array返回第一列的值，若 该参数为 2，则参数 table_array返回第二列的值。 range_lookup（可选）：是一个逻辑值，指定函数 VLOOKUP 查找精确匹配值还是近似匹配值。若为 TRUE 或省略时，进行 模糊查找，返回小于等于参数 lookup_value的最大值，且查找区 域必需按升序排列；若为 FALSE，进行精确查找，返回等于查 找区域中与参数 lookup_value相等的值，查找区域无需排序。 83 下面介绍利用 VLOOKUP函数，根据员工姓名查找员工销量 的方法。 案例 根据员工姓名查找员工销量 ①选中需要输入内 容的单元格 E2 ②单击该按钮，然后 从【插入函数】对话 框中选择【VLOOKUP】 函数 注意事项： 1. 在 参数 table_array 第一列中搜索文本值时， table_array 第一列中的数据没有前导空格、尾部空格、直引号（' 或 "）与 弯引号（'或"）不一致或非打印字符。否则，VLOOKUP 可能 返回不正确或意外的值。 2. 如果参数 range_lookup为 FALSE 且 lookup_value为文 本，则可以在 lookup_value中使用通配符问号 (?) 、星号 (*)， 问号匹配任意单个字符；星号匹配任意一串字符。如果要查找 实际的问号或星号，请在该字符前键入波形符 (~)。 3. 若参数 col_index_num小于 1或者大于 table_array的列 数，则 函数 VLOOKUP返回错误值#REF!。 4.当使用模糊查找时，如果查找区域没有按照升序排列，函 数可能会返回错误值；当精确查找时，如果找到多个近似值， 函数只返回第一个找到的值。 84 ④单元格 E2 中即可 显示出查找结果 ADDRESS 以文本的形式创建某一单元格的引用位置 引用数据 函数格式： ADDRESS(row_num,column_num,abs_num,a1, sheet_text) 参数说明： row_num（必需）：表示单元格引用中使用的行号。 column_num（必需）：表示单元格引用中使用的列号。 abs_num（可选）：表示要返回的引用类型。 ③设置相应参数的值 85 案例 根据给定的行号和列号，建立单元格地址 ②单击该按钮，在【选择函 数】对话框中选择【ADDRESS】 函数 ①选中需要输入公式的单元 格 B7 ▼表 3-4 参数 abs_num 的取值及引用类型 abs_num参数值 返回的引用类型 举例 1或省略 绝对引用行和列 $A$1 2 绝对引用行号，相对引用列号 A$1 3 绝对引用列号，相对引用行号 $A1 4 相对引用行和列 A1 a1（可选）: 是一个逻辑值，表示返回的单元格地址是 A1 或 R1C1 引用样式。 sheet_text（可选）：表示用于指定座位外部引用的工作表的 名称。该参数省略时，表示不使用任何工作表名称。 注意事项： 1.如果参数 a1为 TRUE或被省略，则 ADDRESS函数返回 A1 引用样式；如果参数 a1为 FALSE，则 ADDRESS 函数返 回 R1C1引用样式。 2.单元格引用类型分为绝对引用、混合引用、相对引用；单 元格引用形式可以为 A1形式、R1C1形式。 86 ④显示了创建的单元 格的位置 AREAS 返回引用中包含的区域个数 引用数据 函数格式： AREAS(reference) 参数说明： reference（必需）：表示对某个单元格区域或单元格区域的 引用，也可以引用多个区域。 注意事项： 但是区域之间必必须用逗号分隔，且每个区域都必须用括 ③设置相应参数的值 87 本案例我们使用函数 AREAS 统计某公司销售部销售小组的 个数。在单元格 D8中输入公式：=AREAS((A1:B6,D1:E6,G1:H6))， 然后按下【Enter】键，即可在单元格 D8中显示出计算结果。 案例 统计某公司销售部销售小组的个数 因为公式中引用了多 个区域，因此需要将所 有区域用括号括起来 COLUMN 返回某一引用的列号 引用数据 函数格式： CLUMN(reference) 参数说明： reference（可选）：表示需要得到其列标的单元格或者单元 格区域。 注意事项： 当参数 reference 为区域时，返回位于区域首列的单元格的 列标。如果省略参数 reference，则返回函数 CLUMN 所在单元 格的列标。 号括起来。如果不用括号将对各引用区域括起来，在输入过程 中容易出现错误信息。如果指定单元格或单元格区域以外的参 数，也会返回错误值#NAME!。 88 在单元格 D8 中输入以下公式：=COLUMN(D5:E6)，然后按 下【ENTER】键，即可在单元格 D8中显示出列标。 案例 返回列标 在单元格 B16 中输入公 式=COLUMN(),此时省略 参数即返回当前列列标 在单元格 B15 中输入公 式 =COLUMN(D6),此时返 回 D6的列标 4 在单元格 B14 中输入公 式=COLUMN(A10:B12),此 时返回单元格区域首列 的列标 1 COLUMNS 返回某一引用或数组的列数 引用数据 函数格式： COLUMNS(array) 参数说明： Array（必需）：要计算列数的数组、数组公式或是对单元格 区域的引用。 注意事项： 如果指定单元格、单元格区域、数组、数组公式以外的参 数，将会返回错误值#VALUE!。 89 在单元格 D11 中输入公式=COLUMNS(B2:E2)，按【Enter】 键，即可显示出计算结果。 由于函数 COLUMNS 返回的是列数，所以输入公式 =COLUMNS(B2:E2)和公式=COLUMNS(B:E)的结果是一样的。 如果参数是数组，输入公式=COLUMNS({1,2,3;4,5,6})，则函 数 COLUMNS返回数值 3。 案例 返回数据区域的列数 单元格区域的列数 FORMULATEXT 以字符串的形式返回公式 引用数据 函数格式： FORMULATEXT(reference) 参数说明： reference（必需）：对公式的引用。 注意事项： 1.参数引用的单元格中不包含公式；单元格中的公式超过 8192个字符；无法在工作表中显示公式（例如工作表受保护）； 90 FORMULATEXT函数是 Excel 2013的新增函数，使用此函数 我们可以将单元格中的公式以字符串的形式将公式显示在单元格 中。 案例 查看公式 ①选中单元格 F3，即可 在编辑栏中看到 F3 中的 公式 ②在单元格 B11 中输入 公式=FORMULATEXT(F3)， 即以字符串的形式返回 单元格 F3中的公式 ③在单元格 B12 中输入 公式=FORMULATEXT(A1)， 因为 A1 中不包含公式， 所以返回错误值#N/A 包含此公式的外部工作薄未打开，在这 4 种情况下，此函数将 返回错误值#N/A。 2. 若参数 reference引用了整行或整列，或表示包含多个单 元格的区域或定义名称，则函数 FORMULATEXT 返回行、列 或区域中最左上角单元格中的值。 3.用作输入的无效数据类型将生成错误值#VALUE!。 91 GETPIVOTPDATA 提取存储在数据透视表中的数据 引用数据 函数格式： GETPIVOTDATA(data_field,pivot_table,field1,item1,field2, item2, ...) 参数说明： data_field（必需）：包含要检索数据的数据字段的名称，用 引号（""）引起来。 pivot_table（必需）：数据透视表中的任何单元格、单元格 区域或命名区域的引用。此信息用于确定包含要检索的数据的 数据透视表。 field1,item1,field2,item2, ...（可选）：描述要检索的数据的 1 到 126 对字段名称对和项目名称对。 注意事项： 1.如果参数未描述可见字段，或者参数包含其中未显示筛选 数据的报表筛选，则 GETPIVOTDATA 返回错误值 #REF!。 2.如果 pivot_table 并不代表找到了数据透视表的区域，则 函数 GETPIVOTDATA 将返回错误值 #REF!。 3.如果项目包含日期，则此值必须以序列号表示或使用 DATE 函数进行填充，以便在其他位置打开此工作表时将保留 此值。例如，引用日期 2014年 7月 16日以 DATE(2014,7,16) 的 形式输入。时间可按小数值的形式输入或使用 TIME函数输入。 4. 如果字段和项的参数描述的是单个单元格，则返回此单 元格的数值，无论是文本串、数字、错误值或其他的值。 5. 如果 pivot_table 为包含两个或更多个数据透视表的区 域，则将从区域中最新创建的报表中检索数据。 92 下面我们用函数 GETPIVOTDATA 在数据透视表中查找不同 商品在不同地区的销售额。 案例 查找不同地区各商品的销售额 ①选中要输入内容的 单元格 ②单击该按钮，从弹 出的【插入函数】对 话 框 中 选 择 函 数 【GETPIVOTDATA】 ③在【函数参数】对 话框中设置相应参数 ④单击【确定】按钮 ⑤此时单元格 B13 中 显示了洗衣机在上海 的销售额 93 当参数【field,item…】只指定【产品名称】和【洗衣机】一 组字段名和项名称的时候，在单元格 C11 中输入公式 =GETPIVOTDATA("销售额",A1,A11,B11)，则计算出洗衣机在各地 区销售总额。 当参数【field,item…】只指定【销售区域】和【上海】一组 字段名和项名称的时 候，在单元格 C12 中输入公式 =GETPIVOTDATA("销售额",A1,A12,B12)，即可计算出上海地区各 个商品的销售总额。 ⑥洗衣机在各地区 销售总额 ⑦上海地区各个商 品的销售总额 HYPERLINK 为指定内容创建一个快捷方式或者超链接 引用数据 94 下面我们利用 HYPERLINK函数查看业务员的详细信息。 案例 链接业务员信息 ①选中要输入公式的 单元格 ②单击该按钮，从弹出 的【插入函数】对话框 中选择【HYPERLINK】 函数格式： HYPERLINK(link_location, friendly_name) 参数说明： link_location（必需）：表示打开的文档的路径和文件名，是 加双引号（""）的文本。 friendly_name（可选）：表示单元格中显示的跳转文本或数 字值。如果省略 Friendly_name，单元格会将 link_location 显示 为跳转文本。 注意事项： 1. Friendly_name 可以为数值、文本字符串、名称或包含跳 转文本或数值的单元格。若参数 Friendly_name 返回错误值， 单元格将显示错误值以代替跳转文本。 2.若要选定一个包含超链接的单元格并且不跳转到目标文 件或者位置，则需要单击链接单元格并按住鼠标左键，知道光 标形状变为一个十字，然后释放鼠标按钮。 95 ③设置相应的参数，然 后单击【确定】按钮 ④此时单元格 E2 中显 示出如下内容："跳到 Sheet2 A7" ⑤将鼠标指针移动到 "跳到 Sheet2 A7" 上，当指针变为小手形 状时，单击鼠标左键 ⑥此时跳转到工作表 Sheet2中，即可查看业 务员的信息 96 如果将参数 ref_text设置为带双引号的单元格引用，那么将返 回双引号内的单元格内容。在单元格 A3 中输入公式 =INDIRECT("A1")，然后按【Enter】键。 INDIRECT 返回由文本字符串指定的引用 引用数据 函数格式： INDIRECT(ref_text, a1) 参数说明： ref_text（必需）：表示对单元格的引用，此单元格包含 A1 样 式的引用、R1C1 样式的引用、定义为引用的名称或对作为文本 字符串的单元格的引用。 a1（可选）：一个逻辑值，用于指定包含在单元格 ref_text 中的引用的类型。若 a1为 TRUE 或省略，ref_text 使用 A1样 式的引用。若 a1为 FALSE，则将 ref_text使用 R1C1样式的引 用。 注意事项： 若参数 ref_text 不是合法的单元格引用；若参数 ref_text 是 对另一个未打开的工作薄的引用；若参数 ref_tex 引用的单元格 区域行数大于 1048576，或者列数大于 16384(XFD)，则函数 INDIRECT 返回错误值#REF!。 案例 函数中引号的应用 此时单元格 A3 中显示 出单元格 A1中的内容， 即 C1 97 如果将参数 ref_text设置为不带双引号的单元格引用，那么将 返回引用中的引用指向的单元格内容。在单元格 A3 中输入公式 =INDIRECT(A1)，然后按【Enter】键。 此时单元格 A3 中显示 出单元格 C1中的内容 OFFSET 指定偏移量得到新的引用 引用数据 函数格式： OFFSET(reference, rows, cols, height, width) 参数说明： reference（必需）：要以其作为偏移量参照系的引用。引用 必须是对单元格或相邻的单元格区域的引用，否则 OFFSET 返 回 错误值 #VALUE!。 rows（必需）：表示相对于偏移量参照系的左上角单元格， 上（下）偏移的行数。参数 rows为正数，则向上移动；参数 rows 为负数，则向下移动。 cols（必需）：：表示相对于偏移量参照系的左上角单元格， 左（右）偏移的列数。参数 cols为正数，则向右移动；参数 cols 为负数，则向左移动。 height（可选）：表示需要返回的引用的行数。Height 必须 为正数。 width（可选）：表示需要返回的引用的列数。width 必须为 正数。 98 下面我们使用 OFFSET函数整体移动数据。 案例 1 整体移动数据 ②单击该按钮，然后 选择【OFFSET】函数 ①选中需要输入函数 的单元格 ③设置相应的参数，然 后单击【确定】按钮 ④此时即可看到已将 行政部移动到单元格 E2中，将公式填充到 单元格 E6中即可 注意事项： 1.如果行数和列数的偏移量超出了工作表的边界，函数 OFFSET将返回错误值#REF！。 2.如果省略了参数 height 或 width，则假设其高度或宽度 与 reference 相同。 99 下面我们使用 OFFSET函数，根据员工代码，查找员工信息。 ⑤按照同样的方法 移动员工人数列 案例 2 根据员工代码查找员工信息 ②单击该按钮，然后 选择【OFFSET】函数 ①选中需要输入公式 的单元格 ③设置相应的参数，然 后按住【 Ctrl】键和 【Shift】的同时单击 【确定】按钮 ④即可看到返回结果 参数 reference 引用 的是数组，所以数组公 式在一个中括号中 ⑤将公式向右填充到 I1即可 100 利用 ROW和 TEXT函数快速输入月份。 案例 快速输入月份 ①选中单元格区域 A1:A12 ②在编辑栏输入公式 =TEXT(ROW(),"0 月") 函数参数： ROW(reference) 参数说明： reference（可选）：表示要得到行号的单元格或单元格区域。 注意事项： 1.如果省略 reference，则假定是对函数 ROW 所在单元格 的引用。 2.如果 reference 为一个单元格区域，并且函数 ROW 作为 垂直数组输入，则 ROW 将以垂直数组的形式返回 reference 的行号。 3.Reference 不能引用多个区域。 ROW 返回某一引用的行号 引用数据 101 选中单元格 D11，输入公式=ROWS(A3:A9)，然后按下【Enter】 键，即可统计所有参加考核的员工的数量。 ROWS 返回某一引用或数组的行数 引用数据 函数格式： ROWS(array) 参数说明： Array（必需）：表示需要得到其行数的数组、数组公式或对 单元格区域的引用。 注意事项： 如果指定单元格、单元格区域、数组、数组公式以外的参 数，将会返回错误值#VALUE!。 案例 查看参加考核的员工数目 ③按【Ctrl+Shift+ Enter】键，即可显示 出输入的月份 102 RTD 从支持 COM自动化程序中提取数据 引用数据 函数格式： RTD(progID, server, topic1, topic2, ...) 参数说明： progID（必需）：已安装在本地计算机上且已注册 COM 自 动化加载项的 progID 的名称。将该名称用引号（""）括起来。 server（必需）：表示运行加载项的服务器的名称。 topic1（必需）：表示第一个参数，是一个唯一的实时数据。 topic2 ...（可选）：表示第 2~253个参数。 注意事项： 1.如果没有服务器，则在本地运行程序，将此参数 server 保留为空。否则，输入引号 ("") 将服务器名称括起来。 2.必须在本地计算机上创建和注册 RTD COM 自动化加载 项。如果未安装实时数据服务器，则在尝试使用 RTD 函数时， 单元格中将出现错误消息。 计算结果 103 下面我们用 RTD函数使用 COM地址快速表示时间。在单元 格 B2 中输入公式=RTD("excelrtd,rtdfunctions",,B1,D1)，然后按 【Enter】键。 案例 查看参加考核的员工数目 由于计算机上没有安装 并注册 COM 加载宏，所 以公式返回错误值 TRANSPOSE 转置单元格区域 引用数据 函数格式： TRANSPOSE(array) 参数说明： array （必需）：需要进行转置的数组或工作表上的单元格 区域。所谓数组的转置就是，将数组的第一行作为新数组的第 一列，数组的第二行作为新数组的第二列，以此类推。 注意事项： 使用 TRANSPOSE 函数时，必须以数组形式的形式输入到 单元格区域中。 案例 转置单元格区域 104 ①选中表示函数结果 的区域，原表格有 4 4列，选中区域也应该 有 4行 4列 ②单击该按钮，在打开 的对话框中选择函数 【TRANSPOSE】 ③设置参数 ④按住【Ctrl】键和 【Shift】键的同时单 击【确定】按钮 ⑤此时可看到转置后 的单元格区域