职业素养Excel函数教程

《职业素养Excel函数教程》由会员分享，可在线阅读，更多相关《职业素养Excel函数教程（46页珍藏版）》请在金锄头文库上搜索。

1、Page 0生管管理科生管管理科ExcelExcel函数教程函数教程作成：院长Page 1生产管理科生产管理科一，数组介绍一，数组介绍一，数组介绍一，数组介绍二，二，二，二，SUMIFSUMIF函数函数函数函数三，三，三，三，VLOOKUPVLOOKUP函数函数函数函数四，四，四，四，COUNTIFCOUNTIF函数函数函数函数五，五，五，五，MATCHMATCH函数函数函数函数六，六，六，六，SUMPRODUCTSUMPRODUCT函数函数函数函数七，七，七，七，LOOKUPLOOKUP函数函数函数函数八，日期与时间函数八，日期与时间函数八，日期与时间函数八，日期与时间函数九，数学函数九，数

2、学函数九，数学函数九，数学函数十，统计函数十，统计函数十，统计函数十，统计函数十一，查找与引用函数十一，查找与引用函数十一，查找与引用函数十一，查找与引用函数十二，文本函数十二，文本函数十二，文本函数十二，文本函数十三，逻辑、信息函数十三，逻辑、信息函数十三，逻辑、信息函数十三，逻辑、信息函数01050914182127313235373943Page 2生产管理科生产管理科复杂函数的运算原理复杂函数的运算原理数组运算数组运算运算类型：运算类型：1）四则运算：、）四则运算：、2）逻辑运算：、）逻辑运算：、Page 3生产管理科生产管理科 以数组的角度理解函数应用以数组的角度理解函数应用理解最基

3、本原理，学会举一反三理解最基本原理，学会举一反三授人于鱼，不如授人于渔授人于鱼，不如授人于渔Page 4数组的运算原理数组的运算原理1，1，0，0，1，0，0，0，1，0，10，1，1，0，1，1，0，0，1，1，1=? 0 , 1 , 0 , 0 , 1 , 0 , 0 , 0 , 1 , 0 , 1 1，1，0，0，1，0，0，0，1，0，10，1，1，0，1，1，0，0，1，1，10，1，0，0，1，0，0，0，1，0，1|1）相运算的数组里数字的个数必须相等，如例子，数组）相运算的数组里数字的个数必须相等，如例子，数组1与数组与数组2均为均为11个。个。2）运算时，数组中的数字，按顺序

4、分别进行计算，不会交叉或错乱。）运算时，数组中的数字，按顺序分别进行计算，不会交叉或错乱。3）数组进行求和，）数组进行求和，SUM(0,1,0,0,1,0,0,0,1,0,1)=0+1+0+0+1+0+0+0+1+0+1=4相当于：相当于：SUM(0,1,0,0,1,0,0,0,1,0,1) 运算方法如下运算方法如下运算方法如下运算方法如下Page 5SumIF单条件求和函数单条件求和函数H7:H19=E,E,D,E,E,A,C,B,C,A,D,A,BM7=A，在函数中，自动转换成数组形，在函数中，自动转换成数组形式，即式，即A,A,A,A,A,A,A,A,A,A,A,A,AE,E,D,E,E

5、,A,C,B,C,A,D,A,BA,A,A,A,A,A,A,A,A,A,A,A,A| | | | | | | | | | | | |0,0,0,0,0,1,0,0,0,1,0,1,01）按次序逐对进行比较。）按次序逐对进行比较。2）相符（）相符（TRUE）为）为1，不相符（，不相符（FALSE）为为0。第第第第步得出的步得出的步得出的步得出的结果，再进行结果，再进行结果，再进行结果，再进行下一步运算下一步运算下一步运算下一步运算需要求需要求需要求需要求A A组组组组的段取时的段取时的段取时的段取时间累计，间累计，间累计，间累计，怎么做呢怎么做呢怎么做呢怎么做呢？Page 6SumIF单条件求和

6、函数单条件求和函数0,0,0,0,0,1,0,0,0,1,0,1,0第第步比较运算得出的结果：步比较运算得出的结果：再与再与再与再与K7K7：K19K19（如下）数据按顺序相乘（如下）数据按顺序相乘（如下）数据按顺序相乘（如下）数据按顺序相乘0.50,0.83,1.170.50,0.50,0.33,0.0.50,0.83,1.170.50,0.50,0.33,0.6767结果0,0,0,0,0,0.50,0,0,0,0.50,0,0.33,0最后求和得：最后求和得：1.3311=1 01=0Page 7SumIF单条件求和函数单条件求和函数SumIf（Range，Criteria，Sum_Ra

7、nge）Range：判断区域，一维数组：判断区域，一维数组Criteria：条件，可以是固定值：条件，可以是固定值Sum_Range：求和区域，一维数组：求和区域，一维数组运算步骤及注意点：运算步骤及注意点：运算步骤及注意点：运算步骤及注意点：1 1）RangeRange与与与与CriteriaCriteria进行逻辑运算，得出结果进行逻辑运算，得出结果进行逻辑运算，得出结果进行逻辑运算，得出结果2 2）结果）结果）结果）结果与与与与Sum_RangeSum_Range进行相乘运算进行相乘运算进行相乘运算进行相乘运算3 3）RangeRange与与与与Sum_RangeSum_Range的范围

8、大小必须一致的范围大小必须一致的范围大小必须一致的范围大小必须一致Page 8SumIF单条件求和函数单条件求和函数SumIf（Range，Criteria，Sum_Range）例子：例子：1）SUMIF（A1:A100，D2，B1:B100）2）SUMIF（A1:A100，”A”，B1:B100）3）SUMIF（A1:A100，”3”，B1:B100）解释：解释：解释：解释：1 1）汇总等于）汇总等于）汇总等于）汇总等于D2D2单元格的所有值单元格的所有值单元格的所有值单元格的所有值2 2）汇总等于）汇总等于）汇总等于）汇总等于” ”A”A”的所有值的所有值的所有值的所有值3 3）汇总大于）

9、汇总大于）汇总大于）汇总大于3 3的所有值的所有值的所有值的所有值Page 9Vlookup单条件引用函数单条件引用函数A1,A2,A3,A4,A5,A6,A7B1,B2,B3,B4,B5,B6,B7二维数组二维数组二维数组二维数组D2，D2，D2，D2，D2，D2，D2 A1，A2，A3，A4，A5，A6，A7首先首先D2与首列进行逐个比较运算与首列进行逐个比较运算逐一按逐一按逐一按逐一按顺序进顺序进顺序进顺序进行比较行比较行比较行比较|0，0，0，0，1，0结果结果Page 10Vlookup单条件引用函数单条件引用函数A1,A2,A3,A4,A5,A6,A7B1,B2,B3,B4,B5,

10、B6,B7二维数组二维数组二维数组二维数组第第 步结果步结果0，0，0，0，1，0与第与第2列比较列比较B1,B2,B3,B4,B5,B6,B7返回1对应的数据B6=Z-0Page 11Vlookup单条件引用函数单条件引用函数Vlookup（lookup_value,table_array,col_index_num,range_lookup）Lookup_valueLookup_value：条件值，是值，非区域，比如条件值，是值，非区域，比如条件值，是值，非区域，比如条件值，是值，非区域，比如A1A1，而，而，而，而 非非非非A1:A100A1:A100，即使，即使，即使，即使A1:A10

11、0A1:A100，也只选择左上角值，也只选择左上角值，也只选择左上角值，也只选择左上角值A1A1Table_arrayTable_array：搜索的区域搜索的区域搜索的区域搜索的区域/ /范围，是一个多维数组，如范围，是一个多维数组，如范围，是一个多维数组，如范围，是一个多维数组，如A1:D100A1:D100之类之类之类之类Col_index_numCol_index_num：需要返回的值所在列，从搜索范围需要返回的值所在列，从搜索范围需要返回的值所在列，从搜索范围需要返回的值所在列，从搜索范围的起始列开始算的起始列开始算的起始列开始算的起始列开始算Range_lookupRange_loo

12、kup：匹配方式，一般使用精确匹配，即匹配方式，一般使用精确匹配，即匹配方式，一般使用精确匹配，即匹配方式，一般使用精确匹配，即0 0或或或或FALSEFALSEPage 12Vlookup单条件引用函数单条件引用函数Vlookup（lookup_value,table_array,col_index_num,range_lookup）例子：例子：例子：例子：1 1）VlookupVlookup（D2D2，A1:C100A1:C100，2 2，0 0）2 2）VlookupVlookup（” ”848K 15293”848K 15293”，A1:C100A1:C100，2 2，0 0）3 3）

13、VlookupVlookup（D2&E2D2&E2，A1:C100A1:C100，2 2，0 0）解释：解释：解释：解释：1 1）根据）根据）根据）根据D2D2单元格的值，在单元格的值，在单元格的值，在单元格的值，在A1:C100A1:C100中搜索，并返回从中搜索，并返回从中搜索，并返回从中搜索，并返回从A A列算起，列算起，列算起，列算起，第第第第2 2列（即列（即列（即列（即B B列）的值。列）的值。列）的值。列）的值。2 2）根据）根据）根据）根据” ”848K 15293”848K 15293”，在，在，在，在A1:C100A1:C100中搜索，并返回从中搜索，并返回从中搜索，并返回

14、从中搜索，并返回从A A列算起，列算起，列算起，列算起，第第第第2 2列（即列（即列（即列（即B B列）的值。列）的值。列）的值。列）的值。3 3）根据）根据）根据）根据D2D2与与与与E2E2单元格合并后的值，在单元格合并后的值，在单元格合并后的值，在单元格合并后的值，在A1:C100A1:C100中搜索，并返中搜索，并返中搜索，并返中搜索，并返回从回从回从回从A A列算起，第列算起，第列算起，第列算起，第2 2列（即列（即列（即列（即B B列）的值。列）的值。列）的值。列）的值。Page 13Vlookup单条件引用函数单条件引用函数Vlookup（lookup_value,table_a

15、rray,col_index_num,range_lookup）注意点注意点:1）如果在查找区域）如果在查找区域table_array含有多个与含有多个与lookup_value相等的值，即含有重复项，只能相等的值，即含有重复项，只能返回首个返回首个lookup_value值所对应的结果值所对应的结果2）如果）如果table_array中没有与中没有与lookup_value相相等的值，函数将返回错误值等的值，函数将返回错误值#N/APage 14CountIf统计符合条件的个数，单条件统计符合条件的个数，单条件统计区域统计区域条件条件函数解释：函数解释：函数解释：函数解释：根据条件根据条件根

16、据条件根据条件H2H2在统计区域在统计区域在统计区域在统计区域E2E2：E11E11中，中，中，中，进行逐一比对（逻辑运算），最终进行逐一比对（逻辑运算），最终进行逐一比对（逻辑运算），最终进行逐一比对（逻辑运算），最终汇总符合条件的个数汇总符合条件的个数汇总符合条件的个数汇总符合条件的个数条件：条件：条件：条件： AA，A A，A A，A A，A A，A A，A A，A A，A A，AA统计区域：统计区域：统计区域：统计区域：EE，B B，E E，C C，E E，A A，B B，A A，D D，AA运算运算方式方式按条按条件件0，0，0，0，0，1，0，1，0，1最后对结果进行最后对结果进行

17、求和，如下：求和，如下：SUM(0,0,0,0,0,1,0,1,0,1) SUM(0,0,0,0,0,1,0,1,0,1) =3=3ClickClickPage 15CountIf统计符合条件的个数，单条件统计符合条件的个数，单条件CountIf（Range，Criteria）Range：统计区域，是一维数组，即A1:A100之类，而不能是A1:C100这样多列/行Criteria：条件值，是一个值，而不是区域/数组。可以是单元格，如D2，或者固定值，如”0”例子：例子：例子：例子：1 1）CountIfCountIf（A1:A100,D2A1:A100,D2）在在在在A1:A100A1:A1

18、00之中，统计等于之中，统计等于之中，统计等于之中，统计等于D2D2单元格里数值的个数单元格里数值的个数单元格里数值的个数单元格里数值的个数2 2）CountIfCountIf（A1:A100,”2”A1:A100,”2”）在在在在A1:A100A1:A100之中，统计大于之中，统计大于之中，统计大于之中，统计大于2 2的个数的个数的个数的个数Page 16CountIf统计符合条件的个数，单条件统计符合条件的个数，单条件CountIf（Range，Criteria）利用两个利用两个利用两个利用两个CountIfCountIf函数相结合使用，可以处理一些函数相结合使用，可以处理一些函数相结合使

19、用，可以处理一些函数相结合使用，可以处理一些简单的双条件求个数简单的双条件求个数简单的双条件求个数简单的双条件求个数例子：例子：例子：例子：求在求在求在求在A1:A100A1:A100之中，大于之中，大于之中，大于之中，大于2 2且小于且小于且小于且小于1010的个数的个数的个数的个数1 1）COUNTIFCOUNTIF（A1:A100A1:A100，” ”2”2”）统计大于）统计大于）统计大于）统计大于2 2的个数的个数的个数的个数2 2）COUNTIFCOUNTIF（A1:A100A1:A100，” ”10”10”）统计大于）统计大于）统计大于）统计大于1010的个数的个数的个数的个数3

20、3）COUNTIF(A1:A100,”2”)-COUNTIF(A1:A100,”10”)COUNTIF(A1:A100,”2”)-COUNTIF(A1:A100,”10”)即结果：大于即结果：大于即结果：大于即结果：大于2 2的个数减去大于的个数减去大于的个数减去大于的个数减去大于1010的个数的个数的个数的个数210/Page 17CountIf统计符合条件的个数，单条件统计符合条件的个数，单条件CountIf（Range，Criteria）函数解释：函数解释：函数解释：函数解释：在单行在单行在单行在单行/ /列的范围内（列的范围内（列的范围内（列的范围内（RangeRange）统计符合条件

21、（）统计符合条件（）统计符合条件（）统计符合条件（CriteriaCriteria）的个数。的个数。的个数。的个数。主要用途：主要用途：主要用途：主要用途：1 1）按条件统计个数。）按条件统计个数。）按条件统计个数。）按条件统计个数。2 2）筛选有重复项的记录。）筛选有重复项的记录。）筛选有重复项的记录。）筛选有重复项的记录。用它本身用它本身A2在在A2:A8中进行比中进行比对，即得出对，即得出A2在在A2:A8中的个中的个数，故只要是大于数，故只要是大于1的结果，都的结果，都表示含有重复项。表示含有重复项。Page 18Match查找符合条件的值所在的行号查找符合条件的值所在的行号/列号列号

22、例如，需查例如，需查例如，需查例如，需查C C列中的型列中的型列中的型列中的型番在番在番在番在A A列中是否全部都列中是否全部都列中是否全部都列中是否全部都包含有。包含有。包含有。包含有。C2，C2，C2，C2，C2，C2，C2，C2，C2，C2，C2A1，A2，A3，A4，A5，A6，A7，A8，A9，A10，A11比对比对00，0 0，0 0，0 0，1 1，0 0，0 0，0 0，0 0，0 0，00 返回第一个返回第一个1的位置，即的位置，即5 Page 19Match查找符合条件的值所在的行号查找符合条件的值所在的行号/列号列号MatchMatch（lookup_valuelooku

23、p_value，lookup_arraylookup_array，match_typematch_type）Lookup_value：是值，非区域，例如是值，非区域，例如A1，而非，而非A1:A8。根据。根据这个值，进行逐一比对。这个值，进行逐一比对。Lookup_array：是一个区域，一维数组，只能是一行或一列，是一个区域，一维数组，只能是一行或一列，不能多行多列。例如，不能多行多列。例如，A1:A100，或，或A1:G1，而不能，而不能A1:D10Match_type：匹配方式，精确匹配、模糊匹配，一般使用精匹配方式，精确匹配、模糊匹配，一般使用精确匹配，用数值确匹配，用数值0表示。表示

24、。函数解释：函数解释：在在lookup_array中，查找与中，查找与lookup_value相符的值，并相符的值，并返回它所在的行返回它所在的行/列数。列数。1）如果）如果lookup_array存在两个或以上的存在两个或以上的lookup_value值，函数只会返回首个值，函数只会返回首个lookup_value的所在行的所在行/列数。列数。Page 20Match查找符合条件的值所在的行号查找符合条件的值所在的行号/列号列号MatchMatch（lookup_valuelookup_value，lookup_arraylookup_array，match_typematch_type）L

25、ookup_value：是值，非区域，例如是值，非区域，例如A1，而非，而非A1:A8。根据。根据这个值，进行逐一比对。这个值，进行逐一比对。Lookup_array：是一个区域，一维数组，只能是一行或一列，是一个区域，一维数组，只能是一行或一列，不能多行多列。例如，不能多行多列。例如，A1:A100，或，或A1:G1，而不能，而不能A1:D10Match_type：匹配方式，精确匹配、模糊匹配，一般使用精匹配方式，精确匹配、模糊匹配，一般使用精确匹配，用数值确匹配，用数值0表示。表示。函数解释：函数解释：在在lookup_array中，查找与中，查找与lookup_value相符的值，并相符

26、的值，并返回它所在的行返回它所在的行/列数。列数。2）这里所指的行/列数，与EXCEL本身的行列不同，而是看lookup_array中的行列，比如A2:A10，虽然A5是处于EXCEL的第5行，但函数会返回4，因为它是从A2开始算起Page 21Sumproduct多条件求个数、多条件求和多条件求个数、多条件求和一、多条件求和一、多条件求和Sumproduct（(条件条件1)(条件条件2)(求和区域求和区域)）条件1条件2求和区域此时，如果用单条件求和函数此时，如果用单条件求和函数SumIf则结则结果将会是果将会是1200+800+800=2800条件一：A2:A8=E2 E2与A2:A8进行

27、逐个比较，符合条件的返回1，不符合的返回0，条件二亦同样原理。条件一返回结果（等于条件一返回结果（等于“台湾台湾”的的 ）：）：0，0，1，0，1，0，1条件二返回结果（等于条件二返回结果（等于“57623”的）的） ：1，1，0，0，0，0，1Page 22Sumproduct多条件求个数、多条件求和多条件求个数、多条件求和一、多条件求和一、多条件求和Sumproduct（(条件条件1)(条件条件2)(求和区域求和区域)）条件1条件2求和区域条件一返回结果：条件一返回结果：0，0，1，0，1，0，1条件二返回结果：条件二返回结果：1，1，0，0，0，0，1相乘相乘相乘的结果为：相乘的结果为：

28、0，0，0，0，0，0，1求和区域：求和区域：1200,800,1500,250,520,400,8000，0，0，0，0，0，800|Page 23Sumproduct多条件求个数、多条件求和多条件求个数、多条件求和一、多条件求和一、多条件求和Sumproduct（(条件条件1)(条件条件2)(求和区域求和区域)）条件1条件2求和区域0，0，0，0，0，0，800条件1、条件2、求和区域相乘后的结果最后求和：最后求和：最后求和：最后求和：SUMSUM（00，0 0，0 0，0 0，0 0，0 0，800800）=800=800Page 24Sumproduct多条件求个数、多条件求和多条件求

29、个数、多条件求和=Sumproduct(A2:A8=E2)(B2:B8=F2)(C2:C8)注意点：1）条件、求和区域，都是一维数组，必须是单行/列，而不能是多行/列，比如A2:A8，而不能是A2:C82）条件、求和区域，必须同时是行或同时是列，不能一个是行，一个是列，比如A2:A8是行，A2:H2是列，则错误3）条件、求和区域的范围大小必须一样，比如条件一是A2:A8条件二不能是B1:B7，求和区域也一样道理，必须同时都是从第2行到第8行范围4）如果求和区域中包含有非数值型字符，则结果会返回#value5）任意个条件都可以，只需保证最后一个是求和区域就OK6）条件的书写位置不限制，既可以A2

30、:A8=E2也可E2=A2:A8Page 25Sumproduct多条件求个数、多条件求和多条件求个数、多条件求和=Sumproduct(A2:A8=E2)(B2:B8=F2)(C2:C8)7）条件不只是等于，还可以是大于、小于，等等）条件不只是等于，还可以是大于、小于，等等8）条件间的相隔符（星号）可以理解为）条件间的相隔符（星号）可以理解为“并且并且”，既，既N个个条件都符合的情况下，对求和区域进行累加。如上面例子，可条件都符合的情况下，对求和区域进行累加。如上面例子，可解释为：统计出货地为解释为：统计出货地为”台湾台湾”，并且部番是，并且部番是”802K 57623”的数量的数量9）按此

31、推理，也可以求）按此推理，也可以求“或者或者”的情况，即的情况，即“统计统计802K 57623”出货出货“台湾台湾”和和”泰国泰国”的总和，则函数式可变通为的总和，则函数式可变通为这样：这样：Sumproduct(A2:A8=E2)+(B2:8=F2)*(C2:C8)=1600Sumproduct(条条 件件 一一)+(条条 件件 二二)*(求和区域求和区域)从以上可以看出，函数中用从以上可以看出，函数中用“”表示表示“或者或者”10）”或者或者”的逻辑运算次序要低于并且，请留意此点！的逻辑运算次序要低于并且，请留意此点！Page 26Sumproduct多条件求个数、多条件求和多条件求个数

32、、多条件求和二、多条件求个数二、多条件求个数Sumproduct（条件（条件1）（条件）（条件2）少了“求和区域”条件1结果：0,1,0,1,1,0,1,1,0,1条件2结果：0,1,0,0,1,1,1,0,1,0相乘后结果：0,1,0,0,1,0,1,0,0,0并且：同时为1时才是1，其它都是0或者：同时为0时才是0，其它都是1最后求和：最后求和：最后求和：最后求和：Sum(0,1,0,0,1,0,1,0,0,0)=3Sum(0,1,0,0,1,0,1,0,0,0)=3Page 27Lookup另一种查找函数另一种查找函数LOOKUP(lookup_value,lookup_vector,r

33、esult_vector)最常用途：最常用途：对评分结果进行多级别的分类。在供应商对评分结果进行多级别的分类。在供应商评价、员工评价工作中经常使用，较之一般的评价、员工评价工作中经常使用，较之一般的IF函数函数简洁明了，且效率高。简洁明了，且效率高。条件范围 函数运算时，检验目标值函数运算时，检验目标值A2是否处在是否处在X的各个范围，是返的各个范围，是返回回1，否返回，否返回0，形成一个数组，形成一个数组Page 28Lookup另一种查找函数另一种查找函数LOOKUP(lookup_value,lookup_vector,result_vector)条件范围待返回的结果待返回的结果1，0，

34、0，0，0，0F，E，D，C，B，A45分对应分对应的等级是的等级是FPage 29Lookup另一种查找函数另一种查找函数LOOKUP(lookup_value,lookup_vector,result_vector)条件范围待返回的结果待返回的结果1）”条件范围条件范围”必须是从小到大必须是从小到大2）”条件范围条件范围”与与”待返回的结果待返回的结果”必须个数相等必须个数相等3）”条件范围条件范围”最左端为范围下限，上限不限制，即如上例，最左端为范围下限，上限不限制，即如上例，最小值等于最小值等于0，最大值无限制，可以是无限大，最大值无限制，可以是无限大Page 30Lookup另一种查

35、找函数另一种查找函数LOOKUP(lookup_value,lookup_vector,result_vector)条件范围待返回的结果待返回的结果4）”条件范围条件范围”从左至右，其逻辑关系如上图示，即只包含左从左至右，其逻辑关系如上图示，即只包含左端数值，如端数值，如60x70，即，即6070只包含只包含60，而不包含，而不包含70。5）”待返回的结果待返回的结果”是文本型，必须用双引号括起来。是文本型，必须用双引号括起来。6）”条件范围条件范围”与与”待返回的结果待返回的结果”也可以是变量，即用单元格代也可以是变量，即用单元格代替，如替，如Lookup(A2,B1:B6,C1:C6)，范

36、围要相等，单行或单列，范围要相等，单行或单列Page 31日期与时间函数日期与时间函数 Date（年，月，日）主要用途：将数值转化成日期，如主要用途：将数值转化成日期，如Date(2011,5,10)转换成日期转换成日期2011/5/10，当日期需要作为变量处理时经常用到。单独使用较，当日期需要作为变量处理时经常用到。单独使用较少，一般与其它函数嵌套使用，或利用此原理，作其它变通。少，一般与其它函数嵌套使用，或利用此原理，作其它变通。 Day(日期)、Month(日期)、Year(日期)、Hour(时间)、Minute(时间)、Second(时间)主要用途：将数值转化成日期时间中的日、月、年、

37、小时、分主要用途：将数值转化成日期时间中的日、月、年、小时、分钟、秒，以作筛选汇总使用。比如，需要对出货记录进行按月钟、秒，以作筛选汇总使用。比如，需要对出货记录进行按月汇总，则可以先将日期用汇总，则可以先将日期用Month函数提取出月数，再按月数进函数提取出月数，再按月数进行分类汇总或数据透视。行分类汇总或数据透视。 Today()、Now()主要用途：取得系统日期、日期时间，主要用途：取得系统日期、日期时间，today只获取日期，只获取日期，now除了获取日期外，还包含有时间。除了获取日期外，还包含有时间。Page 32数学函数数学函数 Subtotal(系数，区域)1）主要用途：对有筛选

38、的数据区域进行求和、求个数、求最大）主要用途：对有筛选的数据区域进行求和、求个数、求最大值、求最小值、求平均值等。值、求最小值、求平均值等。筛选后，求和函数的差别筛选后，求和函数的差别Sum函数在求和时，永远只能求整个区域的值，遇到有筛选时，函数在求和时，永远只能求整个区域的值，遇到有筛选时，缺陷就出现，此时，需要用缺陷就出现，此时，需要用Subtotal函数替代，才可避免求和错函数替代，才可避免求和错误。其它求个数、最大值、最小值等，同样道理。误。其它求个数、最大值、最小值等，同样道理。Page 33数学函数数学函数 Subtotal(系数，区域)2）常用的系数如下：）常用的系数如下：一般使

39、用该类一般使用该类一般使用该类一般使用该类 Int(数值)、Mod(数值，被除数)1）INT用于对数值进行不取舍的取整，如用于对数值进行不取舍的取整，如INT(1.25)=12）MOD用于求余数，如用于求余数，如MOD(100,3)=1Page 34数学函数数学函数 Round(数值，保留小数位数)1）该函数用于四舍五入取值，效果优于下图设置：）该函数用于四舍五入取值，效果优于下图设置：右图的保留小数设置不能真正实现四舍五入，只是显示为整数，但参与运算时，仍然是原数值，故当数据量比较大时，误差也就随之增大。 Roundup(数值，保留小数位数)该函数用于向上舍入取整，比如，该函数用于向上舍入取

40、整，比如，2.1取整时为取整时为3，即凡有小，即凡有小数，都向上约进，一般用于包材核算较多，不足数，都向上约进，一般用于包材核算较多，不足1箱的，约箱的，约进进1箱，箱，Roundup（2.31，0）表示向上取整）表示向上取整Page 35统计函数统计函数 Counta（区域）、CountBlank（区域）1）COUNTA用于计算区域内非空单元格的个数;COUNTBLAN用于计算区域内空值单元格的个数2）空值单元格不等于含空格或零值的单元格，它是指单元格内字符长度为零的一个空值，函数中常用两个双引号表示，如”，另外可用LEN函数检测其与空格单元格的差别 Max(区域)、Min(区域)求区域内的

41、最大值（MAX）和最小值（MIN），文本型的数值将不参与运算Page 36统计函数统计函数 Large（区域，排位）-第几大1）此函数用于计算所属区域中，从大到小排行第几位的数值，比如，Large（A1:A100,5）意思是，在A1:A100范围内，求得第5大的数值，利用其它函数（如ROW和COLUMN等函数或单元格值），将”排位”替换成变量，可以快速地从一组数据中提取从大到小的排列顺序2）不允许存在并列第几的状态，如果有相同的数值，将顺位排序，如100,90,90,85,82取第二大数值时是90，第三大是90，第四大是85. Small（区域，排位）-第几小原理与LARGE相同，参照如上。P

42、age 37其它查找与引用函数其它查找与引用函数 ROW、COLUMN1）直接=ROW()用于取得当前单元格的行数，进行拖拉时，可以取得与EXCEL行数相对应的递增或递减的数值。2）添加参数，如=ROW(单元格)，如=ROW(A1)可返回该单元格的行号，如=ROW(B2)返回2第2行，作用同上。3）将一些函数中固定系数、逻辑值按需要替换成变量，可利用这两个函数，以方便拖拉，而不须逐个更改函数中的固定系数值，提高效率。参数值可变量代替，可以实现一次性拖拉得出，节省时间参数值可变量代替，可以实现一次性拖拉得出，节省时间Page 38其它查找与引用函数其它查找与引用函数 INDIRECT(用字符表示

43、的引用区域)1）在函数中引用位置可以变成变量形式，INDIRECT（”A1”）即等于单元格A1的值注意两者形式比较B2相当于”Sheet2” 在统计年、月度报表（日报表分散成一个一个工作表时）时在统计年、月度报表（日报表分散成一个一个工作表时）时用处较大，可巧妙地运用此函数，快速地汇总所需数据。用处较大，可巧妙地运用此函数，快速地汇总所需数据。2）此函数属易失函数，即在引用另一个工作薄数据时，必须在两个EXCEL同时打开的情况下，才有效。3）我们在工作表中直接引用，即=A1之类，在删除单元格时，引用会出错（#REF），如果改用=INDIRECT(A1)则不管如何删除，它都能保证引用返回A1的值

44、。Page 39文本函数文本函数 FIND(单个字符，字符串，起始位置)函数说明：=FIND(“A”,”BDFCAFDAD”,2)在字符串中，从第2位开始，查找”A”首次出现的位置，即返回5，如不包含，则返回错误值#VALUE!，如果省略”起始位置”，则默认为从第1位开始查找。 LEN(单元格或字符)函数说明：=LEN(A1)，计算A1单元格中字符串的长度，不能用区域，如LEN(A1:A10)，也只返回最左上角A1单元格的长度值 VALUE(单元格或字符)函数说明：=VALUE(A1)，将A1单元格中文本型的数字转换成数值型，以便参与函数公式的运算。相当于=-A1，即在A1前面添加两个减号，含

45、“负负得正”的意思。另一种方法是用数值性粘贴方法，将A1单元格乘于1，即转换成数值型。Page 40文本函数文本函数 LOWER、UPPER、TRIM、REPT1）LOWER、UPPER用于将小/大写字母转换成大/小写字母，如下图:2）TRIM函数用于删除字符前后的空格，字符中间的空格无法删除，此函数常用于整理手工输入时误输空格符的情况3）REPT(字符，重复个数)函数用于产生N个重复的字符，当函数中需要用到多个重复字符时，用这个函数减少函数体积4）TEXT(字符，格式类型)它相当于单元格格式设置中的自定义设置，一般在嵌套在其它函数中使用，如下图：使资料规范统一两种方式的效果对比Page 41

46、文本函数文本函数 SUBSTITUTE 字符替换函数1）Substitute(需要替换掉的字符，字符串，需要替换成的字符)如Substitute(“a”,”AFGERHAaFe”,”中”)意思是将字符串中A字符全部替换成”中”，返回结果是“中FGERH中中Fe”，故它是不区分大小写的。2）经常用途，需要去掉部番中间的空格时（848K 15293）如果单纯用查找替换方式，容易将一些059E开头的部番变成科学记数法，而成为乱码，影响数据准确性，此时，可先用本函数去除空格，再数值性粘贴到文本区域。3）替换空格时，可以简写成这样：SUBSTITUTE（“ ”，A1，）即将A1中的空格清除空格格字符空值

47、值字符，可以不填，或用”表示Page 42文本函数文本函数 LEFT、RIGHT、MID1）Left(字符串，位数N) 截取字符串左边的N个字符，用法示例：Left(“中华人民共和国”,3)=“中华人”2）Right(字符串，位数N) 截取字符串右边的N个字符，用法示例：Right(“中华人民共和国”,3)=“共和国”3）Mid(字符串，起始位置Y，位数N) 从字符串的第Y位开始，截取N位字符，用法示例：MID(“中华人民共和国”，2，3)=“人民共”Page 43逻辑、信息函数逻辑、信息函数 IF函数IF（条件，条件成立时返回结果，条件不成立时返回结果）（条件，条件成立时返回结果，条件不成立

48、时返回结果）1）IF（A160，“合格”，“不合格”）即判断A1单元格的值，如果从于60，则返回“合格”，反之，则返回“不合格”2）嵌套使用，如IF(A190,”优秀”,”合格”)意思为如果A1小于60则返回“不合格”，大于60但小于90，返回“合格”，大于90则返回“优秀”，依此类推，共可以嵌套七层，也就是说只可以区分七个等级，超过则会出错，同时，如果函数判断层级达到4-5个以上，函数式会变得很长，极不利于检查核对，故此时需要考虑改用LOOKUP函数来替代3）使用此函数时，逻辑关系务必100%严密，否则将返回不到正确的结果Page 44逻辑、信息函数逻辑、信息函数 ANDOR函数AND（条件（条件1，条件，条件2，条件，条件3）1）AND表示多个条件并列，如AND(A12,B160,A12,B12或B13时条件成立，返回TRUE，用OR时，所有条件只要有一个符合，就返回TRUE， IF(OR(A1=2,A1=3),”良好”,”其它等级”) 即A1在等于2或3时都返回“良好”OR （条件（条件1，条件，条件2，条件，条件3）Page 45逻辑、信息函数逻辑、信息函数 Iserror、Isna函数用于判断错误值，ISNA只判断#NA型的错误值，ISERROR可判断所有错误值，一般结果其它函数使用，如下：无法计算合计数添加ISERROR函数后，避免无法合计现象