常用函数介绍精要.pptx

2017-3-30 行政例会 1 生产技术科常用函数介绍2016年2月 2017-3-30 2 一、匹配函数 二、统计函数 三、查找函数 四、日期函数 2017-3-30 3 一、匹配函数 1、lookup ①、定义：把数（或文本）与一行或一列的数据依次进行匹配，匹配成功后，然后把对应的数值查找出来。 ②、注意事项：重要注意点：（查找的范围）中的数值必须按升序排序：-2、-1、0、1、2、...、A-Z、FALSE、TRUE。否则，lookup函数可能会返回错误的结果。如果 lookup函数中lookup_value （查找的值）小于 lookup_vector （查找的范围）中的最小值，则 lookup函数会返回 #N/A 错误值。 ③、逆向查询 =lookup(1,0/(条件),目标区域或数组） 条件可以是多个逻辑判断相乘组成的多条件数组 =lookup(1,0/((条件1)*(条件2)*(条件N)),目标区域或数组) ④、根据简称查询全称 =iferror(lookup(1,0/find(A2,E2:E8),E2:E8),””) ⑤、多个区间的条件判断 =lookup(B2,{0,50,60,75,86,96}，{”很差”,”差”,”一般”,”较好”,”优秀”,”能手”}) 2017-3-30 4 一、匹配函数 2、vlookup ①、定义：当查找的值位于查找范围的首列，并且返回的值在查找范围的第几列，可以使用vlookup函数。 =vlookup（在数据表第一列中查找的值，查找的范围，返回的值在查找范围的第几列，模糊匹配/精确匹配） ②、注意事项：FALSE（0）省略为精确匹配。 TRUE（1）为近似匹配。 ③、常用举例 ④、多行查找时复制公式的问题 =vlookup(A1,B1:F18,COLUMNS(B1),0) ⑤、避免出现错误值 =if(iferror(vlookup(参数)),””,vlookup(参数)) ⑥、字符模糊查找 =vlookup(“*”A10”*”,A2:B6,2,0) 数字区域从小到大排序 找到比它小的数 2017-3-30 5 一、匹配函数 2、vlookup ⑦、反向查找 =vlookup(A9,if({1,0},B2:B5,A2:A5),2,0) ⑧、多条件查找 =vlookup(A9B,IF({1,0},A2:A5B2:B5,C2:C5),2,0) ⑨、换成文本查找 =vlookup(A9””,A1:D26,2,0) 2017-3-30 6 一、匹配函数 3、Hlookup ①、定义：在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定处的数值。当比较值位于数据表的首行，并且要查找下面给定行中的数据时，用HLOOKUP。 =hlookup（在数据表第一列中查找的值，查找的范围，返回的值在查找范围的第几行，模糊匹配/精确匹配） ②、注意事项：FALSE（0）省略为精确匹配。 TRUE（1）为近似匹配。 2017-3-30 7 一、匹配函数 4、match ①、定义：指返回指定数值在指定数组区域中的位置。 =match（查找值，区域，查询的指定方式） ②、注意事项： 查找值可以使用通配符、*、？等 查询指定方式：1时为查找小于或等于查找的最大值位置，数组区域必须按升序排列 0时为等于的第一个数值，任意排列 -1时为查找大于或等于查找的最小值位置，数组区域必须按降序排列 ③、与index合用 ④、MATCH函数用于在指定区域内按指定方式查询与指定内容所匹配的单元格位置； LOOKUP函数用于在指定区域内查询指定内容多对应的匹配区域内单元格的内容。 使用MATCH函数时的指定区域必须是单行多列或者单列多行； 查找的指定内容也必须在指定区域存在，否则会显示“#N/A”错误。 2017-3-30 8 一、匹配函数 5、index ①、定义：返回表或区域中的值或对值的引用。函数INDEX()有两种形式：数组形式和引用形式。数组形式通常返回数值或数值数组；引用形式通常返回引用。 =index（单元格区域或常数，行号，列号） ②、index与match结合的例子 6、choose 选择函数，函数从参数列表中选择并返回一个值。 =choose（选择的索引值，项目，项目，……） 2017-3-30 9 二、统计函数 1、count ①、定义：返回数字参数的个数，它可以统计数组或单元格区域中含有数字的单元格个数。 2、counta ①、定义：返回参数中非空值的数目。利用函数counta可以计算组成或者单元格区域中数据项的个数。 3、countblank ①、计算某个单元格区域中空白单元格的数目。 2017-3-30 10 二、统计函数 4、countif 定义：指定区域中符合指定条件