一招鲜,吃遍天之三:巧妙搭配的 INDEX 和 MATCH 函数
INDEX 函数和 MATCH 函数结合, 能够实现任意方向的数据查询, 使数据查询更加灵活简便。 INDEX 函数能够在一个区域引用或数组范围中 , 根据指定的行号或 ( 和 ) 列号来返回值或引 用 。 INDEX 函数的语法有引用和数组两种形式 。 数组形式语法如下 。 第 1 参数为检索的单元格区域或数组常量 。 如果数组只包含一行或一列 , 则相应的 row_num 或 column_num 参数是可选的 。 如果数组具有多行和多列 , 并且仅使用 row_num 或 column_num , 则 INDEX 返回数组中整个行或列的数组 。 2 参数 row_num 代表数组中的指定行, 函数从该行返回数值。 如果省略 row_num , 则需要有第 3 参数 column_num 。 第 3 参数 column_num 为可选参数 , 代表数组中的指定列 , 函数从该列返回数值 。 如果省略该 参数 , 则需要有第 2 参数 row_num 。 引用形式语法如下 。 第 1 参数 reference 是必需参数 , 为一个或多个单元格区域的引用 , 如果需要输入多个不连续 的区域 , 则必须将其用小括号括起来 。 第 2 参数 row_num 是必需参 数 , 为要返回引用的行号 。 第 3 参数 [ column_num ] 是可选参数 , 为要返回引用的列号 。 第 4 参数 [ area_num ] 是可选参数 , 为要选择 返回用引用的区域 。 如下图所示, A1 : D4 单元格区域中是需要检索的数据。 以下公式为返回 A1 : D4 单元格区域中第 3 行和第 4 列交叉处的 单元格 , 即 D3 单元格的值 12 。 以下公式为返回 A1 : D4 单元格区域中第 3 行单元格的和 , 即 A3 : D3 单元格区域的和 42 。 以下公式为返回 A1 : D4 单元格区域中第 4 列单元格的和 , 即 D1 : D4 单元格区域的和 40 。 以下公式返回 (A1 : B4,C1 : D4) 两个单元格区域中的第二个区域第 3 行第 1 列的单元格 , 即 C3 单元格 。 由于 INDEX 函数的第 1 参数是多个区域 , 因此用小括号括起来 。 根据公式需要 , INDEX 函数的返回值可以为引用或是数值 。 例如 , 如下第一个公式等价于第 二个公式 , CELL 函数将 INDEX 函数的返回值作为 B1 单元格的引用 。 而在以下公式中 , 则将 INDEX 函数的返回值解释为 B1 单元格中的数字 。 ROW 函数可以生成垂直方向连续递增的自然数序列, COLUMN 函数可以在水平方向上生成连续递增的自然数序列。 ROW 函数和 COLUMN 函数组合可以生成指定规则的序列, 结合 INDEX 函数, 可以实现将单列或单行数据转换为多行多列。 如下图 所示 , A2 : A13 单元格区域为 零件库存基本信息 , 从 A2 单元格起 , 每 3 个 单元格为一组 。 要求将 A2 : A13 单元格区域的 单列数据转换为 C2 : E5 单元格区域的形式 , 每 个零件占 1 行 3 列 。 在 C2 单元格输入以下公式, 将公式复制到 C2 : E5 单元格区域。 公式中的 “ 3 * ROW(A1) - 3+COLUMN(A1) ” 部分 , 计算结果为 1 , 公式向下复制时 , ROW(A1) 依次变为 ROW(A2) 、 ROW(A3) …… 公式计算结果分别为 4 , 7 , 10 …… 即生成步长为 3 的递增数列 。 公式向右复制时 , COLUMN(A1) 依次变为 COLUMN(B1) 、 COLUMN(C1) …… 计算结果为 2 , 3 …… 即生成步长为 1 的递增数列 。“ 3 * ROW(A1) - 3+COLUMN(A1) ” 部分生成 的结果如下图 所示 。 最后用 INDEX 函数, 根据以上公式中生成的数列提取 A 列中对应单元格的内容, 实现将单列数据转换为多行多列数据的目的。 MATCH 函数用于根据指定的查询值,返回该查询值在一行(一列)的单元格区域或数组中的相对位置。若有多个符合条件的结果,MATCH 函数仅返回第一次出现的位置。其函数语法如下。第 1 参数 lookup_value 为指定的查找对象 。 第 2 参数 lookup_array 为可能包含查找对象的单元格区域或数组 , 这个单元格区域或数组只可 以是一行或一列 , 如果是多行多列则返回错误值 #N/A 。 第 3 参数 [ match_type ] 是可选参数 , 为查找的匹配方式 。 当第 3 参数为 0 、 1 或省略 、- 1 时 , 分别表示精确匹配 、 升序模式下的近似匹配和降序模式下的近似匹配 。 如果简写第 3 参数的 值 , 仅以逗号占位 , 表示使用 0 , 也就是精确匹配方式 , 如 “ MATCH('ABC',A1 : A10,0) ” 等价于 “ MATCH('ABC',A1 : A10,) ”。 在精确匹配模式下 , MATCH 函数的第 1 参数支持使用通配符 。 例 1 : 当第 3 参数为 0 时 , 第 2 参数不需要排序 。 以下公式返回值为 3 。 其含义为在第 2 参数 的数组中 , 字母 “ A ” 第一次出现的位置为 3 。 例 2 : 当第 3 参数为 1 或省略第 3 参数值时 , 第 2 参数要求按升序排列 , 如果第 2 参数中没有 具体的查找值 , 将返回小于第 1 参数的最大值所在位置 。 以下两个公式返回值都为 2 , 由于第 2 参 数没有查询值 4 , 因此以小于 4 的最大值也就是 3 进行匹配 。 3 在第 2 参数数组中是第 2 个 , 因此 结果返回 2 。 例 3 :当第 3 参数为 -1 时,第 2 参数要求按降序排列,如果第 2 参数中没有具体的查找值,将返回大于第 1 参数的最小值所在位置。以下公式返回值为 3,由于第 2 参数中没有查询值 5,因此以大于 5 的最小值也就是 6 进行匹配。6 在第 2 参数数组中是第 3 个,因此结果返回 3。 第 3 参数为 0 的精确匹配时 , 第 1 参数的查找值中可以使用通配符 。
本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请
点击举报 。