如何在Excel表格中查找数据.pdf

本文分步介绍了如何使用 microsoft excel 中的各种内置函数在表中（或单元格区域中）查找数 据。您可以使用不同的公式来获取相同的结果。 创建示例工作表 本文使用一个示例工作表来阐释 excel 的内置函数，例如引用列 a 中的姓名并从列 c 中返回 该用户的年龄。要创建此工作表，请在空白 excel 工作表中输入下面的数据。 您需要在单元格 e2 中键入要查找的值。您可以在同一工作表中的任一空白单元格内键入该公 式。 a b c d e 1 name dept age find value 2 henry 501 28 mary 3 stan 201 19 4 mary 101 22 5 larry 301 29 术语定义 本文使用下列术语来说明 excel 内置函数： 术语术语 定义定义 示例示例 table_array 整个查找表。 a2:c5 lookup_value 要在 table_array 的第一列中查找的值。 e2 lookup_array - 或 - lookup_vector 包含可能的查找值的单元格区域。 a2:a5 col_index_num table_array 中的应为其返回匹配值的列号。 3（table_array 中的第三列） result_array - 或 - result_vector 一个只包含一行或一列的区域。 它的大小必须 与 lookup_array 或 lookup_vector 相同。 c2:c5 range_lookup 逻辑值（true 或 false）。如果该值为 true 或被忽略，则返回近似的匹配项。如果该值为 false，则将查找完全匹配的项。 false top_cell 这是要作为偏移量基准位置的参考位置。 top_cell 必须是一个单元格或一个由相邻单 元格组成的区域。否则，offset 会返回 #value! 错误值。 offset_col 这是您希望结果区域的左上角单元格偏离 （向 左或向右）所参考的单元格的列数。例如，如 果 offset_col 参数为“5”，则会指定结果 区域中的左上角单元格在参考位置右侧第五 列处。offset_col 可以是正数（表示位于起 始参考位置的右侧），也可以是负数（表示位 于起始参考位置的左侧）。 函数 lookup() lookup 函数可在单个行或列中查找值，并将该值与另一行或列中同一位置的值进行匹配。 下面是 lookup 公式语法的示例： =lookup(lookup_value,lookup_vector,result_vector) 下面的公式在示例工作表中查找 mary 的年龄： =lookup(e2,a2:a5,c2:c5) 该公式使用单元格 e2 中的值“mary”并在查找矢量（列 a）中查找“mary”。然后，该公式会将 该值与结果矢量（列 c）中同一行内的值进行匹配。因为“mary”在第 4 行中，所以 lookup 会 返回列 c 的第 4 行中的值 (22)。 注意注意：lookup 函数要求对表进行排序。 有关 lookup 函数的更多信息， 请单击下面的文章编号， 以查看 microsoft 知识库中相应的文 章： 324986 如何在 excel 中使用 lookup 函数 vlookup() 当数据以列的形式列出时，使用 vlookup（即“纵向查找”）函数。该函数在最左边的列中搜索 某个值，然后将该值与同一行中指定列内的数据进行匹配。您可以使用 vlookup 在已排序的 或未排序的表中查找数据。下面的示例使用含有未排序的数据的表。 下面是 vlookup 公式语法的一个示例： =vlookup(lookup_value,table_array,col_index_num,range_lookup) 下面的公式在示例工作表中查找 mary 的年龄： =vlookup(e2,a2:c5,3,false) 该公式使用单元格 e2 中的值“mary”并在最左边的列（列 a）中查找“mary”。然后，该公式会 将该值与 column_index 中的同一行中的值进行匹配。该示例使用“3”作为 column_index（列 c）。因为“mary”在第 4 行中，所以 vlookup 会返回列 c 中的第 4 行中的值 (22)。 有关 vlookup 函数的更多信息，请单击下面的文章编号，以查看 microsoft 知识库中相应的 文章： 181213 如何使用 vlookup 或 hlookup 查找完全匹配项 index() 和和 match() 您可以将 index 函数和 match 函数一起使用，以获取与使用 lookup 或 vlookup 相同的 结果。 下面是一个语法示例，它组合使用 index 和 match，以便与前面示例中 的 lookup 和 vlookup 产生相同的结果： =index(table_array,match(lookup_value,lookup_array,0),col_index_num) 下面的公式在示例工作表中查找 mary 的年龄： =index(a2:c5,match(e2,a2:a5,0),3) 该公式使用单元格 e2 中的值“mary”并在列 a 中查找“mary”。然后，它将该值与列 c 中同一 行内的值进行匹配。 因为“mary”在第 4 行中， 所以该公式会返回列 c 中的第 4 行中的值 (22)。 注意注意：如果 lookup_array 中的任何单元格都不与 lookup_value（“mary”）匹配，则该公式将返 回 #n/a。 有关 index 函数的更多信息， 请单击下面的文章编号， 以查看 microsoft 知识库中相应的文章： 324988 xl2002：如何使用 index 函数在表中查找数据 offset() 和和 match() 您可以组合使用 offset 和 match 函数，以便与前面示例中的函数产生相同的结果。 下面是一个语法示例，它组合使用 offset 和 match，以便与 lookup 和 vlookup 产生相 同的结果： =offset(top_cell,match(lookup_value,lookup_array,0),offset_col) 该公式在示例工作表中查找 mary 的年龄： =offset(a1,match(e2,a2:a5,0),2) 该公式使用单元格 e2 中的值“mary”，并在列 a 中查找“mary”。然后，该公式将该值与同一行 中向右两列处 （列 c） 的值进行匹配。 因为“mary”在列 a 中， 所以该公式会返回列 c 中的第 4 行中的值 (22)。 有关 offset 函数的更多信息，请单击下面的文章编号，以查看 microsoft 知识库中相应的文 章： 324991 xl2002：如何使用 offset 函数 excel 数据查找数据查找 excel 有强大的数据处理功能，可以用它对数据进行组织和管理，当在工作中采集了大量 数据后，出于某种需要，我们希望将符合某种条件的数据提取出来，有时是一个汇总结果， 有时是所有符合条件的数据本身。很多刚接触 excel 的朋友不知道从哪个方面入手，下面 我们通过几个实例，快速掌握这些方法。 一、通过一定条件查找，返回汇总结果一、通过一定条件查找，返回汇总结果 在这个销量表中，如果我们想统计出姓名为“张三”的全部销量，可以用一个函数来做公式： =sumif(销量表!b:b,“张三“,销量表!c:c) 这个 sumif 函数是一个条件汇总函数，它有三个参数，第一个参数是要查找的区域，在本 例中是 b 列，第二个参数是要查找的条件，在本例中是“张三”这个姓名，第三个参数是需要 汇总的区域。 这个公式的含义是：在“销量表”的 b 列查找“张三”这个内容，在 b 列找到后，把找到的同一 行中的 c 列值相加。 我们还可以用下面这个公式达到同样的效果： =sum(if(销量表!b1:b100=“张三“,销量表!c1:c100) 这是一个数组公式，在输入完成后要按 ctrl+shift+enter 组合键确认，此时会自动在 公式的两端加上一对花括号（手工输入花括号无效）。 在这个公式中用了两个函数 sum 与 if，先由 if 对它右边的条件“销量表!b1:b100=“张三“” 做判断，这个条件同样是在销量表的 b 列查找“张三”，当条件成立时，就执行“销量 表!c1:c100”这个部份，即返回 c 列同一行中的数值，最后由 sum 函数将返回的所有相符 的数值相加，得到正确结果。 第二个公式看似比第一个公式复杂，但它有一个“特殊”的作用，就是可以“多条件”查找。 再看一个例子，还是在“销量表”中，把六七月份的“张三”的销量统计出来（不要五月份的） =sum(if(month(销量表!a2:a100)5)*(销量表!b2:b100=“张三“),销量表!c2:c100) 这个数组公式用了两个条件，一个是 a 列的月份要大于 5（即只要 6、7 月份的），另一个 条件是 b 列等于“张三”，只有这两条件都成立，才执行同一行中的 c 列汇总求和。 在这个公式中，由于要对“月份”进行计算，所以用一了个 month 函数，它的作用是提取日 期中的“月份”。 要注意一点，在 sumif 函数中可以用“整列”（即：销量表!b:b），而在 sum 与 if 组合的 数组公式中，不能用“整列”这个方式，只能用一个具体的区域（如：销量表!a2:a100）。 提示提示 1：在上面几个公式中，为了方便使用，可以把“条件”放在一个固定的单元格中，然后 在公式中只引用这个单元格。比如把要查找的“张三”输入到 k1 格中，然后把公式改成： =sumif(销量表!b:b,k1,销量表!c:c) 提示提示 2： 如果公式不在“销量表”中， 那么公式中必须加上表格名字用以指定数据位置 （如： “销 量表!b1:b100”前面的“销量表！”），如果公式就在“销量表”工作表中，则公式中不必加工作 表的名字。 提示 3：想达到上面的结果，还有很多其他方法，大家可根据自己的喜好选择。 二、通过条件查找，返回符合条件的内容。二、通过条件查找，返回符合条件的内容。 在上图的表格中，每个姓名是唯一的，希望通过一个姓名查找到所需的电话号码 比如查找李四的电话号码 在 f1 单元格中输入一个要查找的姓名“李四”，然后在 f2 单元格中用公式 =vlookup(f1,a:b,2,0) 只要按需要改变 f1 格中的姓名，就可在 f2 单元格中得到对应的号码。 这个公式使用了 vlookup 函数，它有四个参数，第一个参数是要查找的值，第二个参数 是要查找的范围，第三个参数表示返回范围中的第几列内容，第四个参数如果为 0，就表示 “精确查找”，如果为 1 就表示“模糊查找”，一般用 0 值居多。 公式的含意是： 在 a： b 两列范围的第一列 a 列中， 查找 f1 单元格中的值， 执行精确查找， 找到后返回 a：b 两列中第二列 b 列的内容。 还可以用另一个公式来达到这个效果： =index(b:b,match(f1,a:a,0) 这个公式用了两个函数，match 函数返回 f1 单元格的值在 a 列的位置，第三个参数 0 表 示只返回查找到的第一个值；index 函数通过前一个函数返回的位置，在 b 列中取出对应 的内容。 这两个公式各有特点，当查找区域是连续时，用 vlookup 比较省事；当区域不在同一个 位置时，就只能用第二个公式了。 三、通过指定条件，把所有符合的内容都显示出来三、通过指定条件，把所有符合的内容都显示出来 还是以最上面的“销量表”为例，通过一个公式把所有姓名等于“张三”的内容都显示出来。 先在 f1 格输入一个姓名“张三”，然后在 h、i、j 列使用公式： h2 单元格输入公式 =index($a$1:$a$10,small(if($b$1:$b$10=$f$1,row($b$1:$b$10),1000),row(a1) ) i2 单元格输入公式 =index($b$1:$b$10,small(if($b$1:$b$10=$f$1,row($b$1:$b$10),1000),row(a1) ) j2 单元格输入公式 =index($c$1:$c$10,small(if($b$1:$b$10=$f$1,row($b$1:$b$10),1000),row(a1) ) 这三个数组公式中，只有 index 的第一个参数不同，其他都是一样的，公式完成后，把这 三个单元格同时选中，用鼠标向下拖动复制到下面的其他格中。 这个公式对初学者来说，就比较难理解了，其实不论多复杂的公式，你可以把它分成多个部 份，一部份一部份的来分析理解，就能知道它的作用了。 以 i2 格公式为例进行分析： 1、先看“if($b$1:$b$10=$f$1,row($b$1:$b$10),1000)” 它的意思是在 b1：b10 区域内查找 f1 单元格的值（一个要查找的姓名），如果区域内有 这个值，就返回它的行号（row 是返回行号的函数），如果没有就返回一个比较大的数值 （任意，只要大于数据的个数就行）。如果有多个符合的内容，就返回多个行号。 用本例数据查找“张三”来说明，通过这部份运算，会得到一串数值 1000;2;1000;4;5;1000;1000;1000;9;1000 它表示在区域的第 2、4、5、9 行中找到了与“张三”相符的内容。为了方便说明，我给这串 数值取个名字叫“行” 2、再看“small(if($b$1:$b$10=$f$1,row($b$1:$b$10),1000),row(a1)”这部份，它 等价于“small(行,row(a1)” 这部份用 small 函数返回一个“第几小值”，由于 i2 单元格最后用的是 row(a1)，它是 a1 格的行号，即 1，表示返回第一个最小的值，所以从上面“行”数值串中返回“2”。为了说明方 便，我给它取名为“位置”。 提示：之所以在上面部份用 row(a1)来代表 1，是为了向下拖动复制公式时，它会自动递 增，每复制一行就自动增加 1，例如把 i2 公式复制到 i3 格时，在 i3 格会变成 row(a2)。 这时在 small 函数的第二个参数变成 2，表示返回第二小值，即“行”字串中的“4”