Excel函数使用之 VLOOKUP，MID，FIND，SUBSTITUTE，SUMPRODUCT，COUNTIFS_查找值在数据表中多次出现,导致有多个结果

VLOOKUP 垂直查询

功能：在表格的首列查找指定的数值，并返回表格当前行中指定列处的数值。

结构：=VLOOKUP(查找值,数据表,列序数,匹配条件)

• Lookup_value查找值。
  比如说根据【姓名】来查找【工资】，【姓名】就是查找值，且在数据表中要位于第一列（首列必须含有查找内容）；

• table_array数据表：查找的数据区域，即查找的范围。
  建议设置为绝对引用，在选定区域后按F4键就可以快速切换，就是在行和列的前面添加$符号，拖动公式时，区域就不会发生改变；

• col_index_num列序数：也就是返回的结果在数据表中位于第几列，包含隐藏的列。
  如“2” 表示参数2中工作表的第2列。（1开始计数，查询区域为文本能时不区分大小写）；
  col_index_num>table_array 列数返回#REF!
col_index_num<1 报错#VALUE!

• vange_Lookup匹配条件。
  若为0或FALSE代表精确匹配（找不到返回#N/A）；1或TRUE代表模糊匹配（第一列值必须升序排列）；

查找值在数据表中多次出现，导致有多个结果，函数仅仅会返回第一个找到的结果。

注意事项:
①第一个参数必须能够在第二个参数查询区域内的第一列找得到。
②第三个参数为要得到的值相对于查询依据所在的列数，而并非第二个参数查询区域的总列数。
③若非特殊或规定情况下，第四个参数为FALSE，即精确匹配。

MID 截取字符串

功能：从中间提取。返回文本字符串中从指定位置开始的特定数目的字符。

结构：=MID(字符串，开始位，截取长度)

• text字符串：将要被截取的对象。
• start_num开始位：截取的起始位置，即文本中要提取的第一个字符的位置。
• num_chars截取长度：截取的字符串的长度。

FIND 字符串中查找指定字符

功能：从文本字符串中查找特定文本，返回查找文本的起始位置。Find函数进行定位时，总是从指定位置开始，返回找到的第一个匹配字符串的位置，而不管其后是否还有相匹配的字符串。

结构：=FIND(特定字符，范围，查找第几个该特定字符)

• Find_text 特定字符：查找内容/文本字符串，不支持通配符/区分大小写/全角和半角符。
• Within_text 范围：查找区域，包含要查找关键字的单元格。就是说要在这个单元格内查找关键字。
• Start_num 指定开始进行查找的字符数。
  比如Start_num为1，则从单元格内第一个字符开始查找关键字。如果忽略start_num，则假设其为 1。

SUBSTITUTE 替换指定的文本

功能：在文本字符串中用 new_text 替代 old_text。如果需要在某一文本字符串中替换指定的文本，请使用函数 SUBSTITUTE；如果需要在某一文本字符串中替换指定位置处的任意文本，请使用函数 REPLACE。

结构：=SUBSTITUTE(要替换的文本，旧文本，新文本，替换第几个)

• Text 要替换的文本： 为需要替换其中字符的文本，或对含有文本的单元格的引用。 是不省略参数。
• Old_text 旧文本：为需要替换的旧文本。是不省略参数。
• New_text 新文本：用于替换 old_text 的文本。 是不省略参数，但有默认值空。
• Instance_num 替换第几个：为一数值，用来指定以new_text 替换第几次出现的 old_text。
  如果指定了 instance_num，则只有满足要求的 old_text被替换；如果缺省则将用 new_text 替换 TEXT 中出现的所有 old_text。

结合运用MID/FIND/SUBSTITUTE截取所需字符

=MID(B2,FIND(“CE”,B2,2)+1,10)

• B2 ：要提取字符串单元格
• FIND(“CE”,B2,1)+2 ：开始提取位置，在B2单元格中查找第1个字符串开始查找"CE"关键字，返回第一次出现的位置加2。
• 10 ：提取字符个数

先把第六次出现的\替换成#，再找#的位置。
=FIND(“#”,substitute(A1,“”,“#”,6))

=MID($A2,FIND(“#”,substitute($A2,"\","#",6))+1,FIND(“#”,substitute($A2,"\","#",7))-FIND(“#”,substitute($A2,"\","#",6))-1)

• $A2 ：要提取字符串单元格
• FIND(“#”,substitute($A2,"\","#",6))+1 ：开始提取位置
• FIND(“#”,substitute($A2,"\","#",7))-FIND(“#”,substitute($A2,"\","#",6))-1 ：提取字符个数

SUMPRODUCT返回乘积之和

功能：在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。
结构：=SUMPRODUCT(array1, [array2], [array3], …)

• Array1 必需。其相应元素需要进行相乘并求和的第一个数组参数。
• Array2, array3,… 可选。2 到 255个数组参数，其相应元素需要进行相乘并求和。

• 数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE!。
• 函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理。

COUNTIFS计算满足条件个数

功能：计算多个区域中满足给定条件的单元格的个数。
结构：=countifs(criteria_range1,criteria1,criteria_range2,criteria2,…)

• criteria_range1为第一个需要计算其中满足某个条件的单元格数目的单元格区域（简称条件区域），criteria1为第一个区域中将被计算在内的条件（简称条件），其形式可以为数字、表达式或文本。例如，条件可以表示为 48、“48”、“>48” 、 “广州” 或 A3；
• criteria_range2为第二个条件区域，criteria2为第二个条件，依次类推。最终结果为多个区域中满足所有条件的单元格个数。

Excel2007中新增函数，为countif函数的扩展。用法与countif类似，但countif针对单一条件，而countifs可以实现多个条件同时求结果。

实例运用SUMPRODUCT，COUNTIFS

求出数据范围大于等于20，小于等于50的个数

• 方法1：在D3单元格输入公式=SUMPRODUCT((A2:A25>=20)*(A2:A25<=50))，而后确定，获取符合条件的个数
  =SUMPRODUCT((A2:A25<>"")*(A2:A25>=B2)*(A2:A25<=C2)) 返回非空单元格(A2:A25<>"")中符合条件的个数

• 方法2：在D4单元格输入公式=COUNTIFS(A2:A25,">=20",A2:A25,"<=50")，这个是用的最常用的countif函数，获取符合条件区间的个数
  =COUNTIFS(A2:A25,"<>",A2:A25,">=20",A2:A25,"<=50") 返回非空单元格A2:A25,"<>"中符合条件区间的个数

• 方法3：在D5单元格输入公式=SUM(COUNTIF(A2:A25,{">=20",">=50"})*{1,-1})，这里是用的正负抵消的方法

• 方法4：在D6单元格输入公式=SUM((A2:A25>=20)*(A2:A25<=50))，这里使用的是数组公式，输入公式后，必须按三键结束，就是转化为数组