Excel函数教程

Page0生管管理科Excel函数教程作成：院长Page1生产管理科一，数组介绍二，SUMIF函数三，VLOOKUP函数四，COUNTIF函数五，MATCH函数六，SUMPRODUCT函数七，LOOKUP函数八，日期与时间函数九，数学函数十，统计函数十一，查找与引用函数十二，文本函数十三，逻辑、信息函数01050914182127313235373943Page2生产管理科复杂函数的运算原理——数组运算运算类型：1）四则运算：＋、－、×、÷2）逻辑运算：＞、＜、≥、≤、＝、﹤﹥Page3生产管理科○以数组的角度理解函数应用○理解最基本原理，学会举一反三○授人于鱼，不如授人于渔Page4数组的运算原理{1，1，0，0，1，0，0，0，1，0，1}{0，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，1}{0，1，1，0，1，1，0，0，1，1，1}×××××××××××{0，1，0，0，1，0，0，0，1，0，1}||||||||||||||||||||||1）相运算的数组里数字的个数必须相等，如例子，数组1与数组2均为11个。2）运算时，数组中的数字，按顺序分别进行计算，不会交叉或错乱。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)☆运算方法如下☆Page5SumIF——单条件求和函数H7:H19={E,E,D,E,E,A,C,B,C,A,D,A,B}M7=A，在函数中，自动转换成数组形式，即{A,A,A,A,A,A,A,A,A,A,A,A,A}{E,E,D,E,E,A,C,B,C,A,D,A,B}{A,A,A,A,A,A,A,A,A,A,A,A,A}||||||||||||||||||||||||||{0,0,0,0,0,1,0,0,0,1,0,1,0}1）按次序逐对进行比较。2）相符（TRUE）为1，不相符（FALSE）为0。第①步得出的结果，再进行下一步运算需要求A组的段取时间累计，怎么做呢？Page6SumIF——单条件求和函数{0,0,0,0,0,1,0,0,0,1,0,1,0}第①步比较运算得出的结果：再与K7：K19（如下）数据按顺序相乘{0.50,0.83,1.17……0.50,0.50,0.33,0.67}结果{0,0,0,0,0,0.50,0,0,0,0.50,0,0.33,0}最后求和得：1.331×1=10×1=0Page7SumIF——单条件求和函数SumIf（Range，Criteria，Sum_Range）Range：判断区域，一维数组Criteria：条件，可以是固定值Sum_Range：求和区域，一维数组运算步骤及注意点：1）Range与Criteria进行逻辑运算，得出结果①2）结果①与Sum_Range进行相乘运算3）Range与Sum_Range的范围大小必须一致Page8SumIF——单条件求和函数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）汇总等于D2单元格的所有值2）汇总等于”A”的所有值3）汇总大于3的所有值Page9Vlookup——单条件引用函数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}结果①Page10Vlookup——单条件引用函数A1,A2,A3,A4,A5,A6,A7B1,B2,B3,B4,B5,B6,B7二维数组第①步结果{0，0，0，0，1，0}与第2列比较{B1,B2,B3,B4,B5,B6,B7}返回1对应的数据B6=Z-0Page11Vlookup——单条件引用函数Vlookup（lookup_value,table_array,col_index_num,range_lookup）Lookup_value：条件值，是值，非区域，比如A1，而非A1:A100，即使A1:A100，也只选择左上角值A1Table_array：搜索的区域/范围，是一个多维数组，如A1:D100之类Col_index_num：需要返回的值所在列，从搜索范围的起始列开始算Range_lookup：匹配方式，一般使用精确匹配，即0或FALSEPage12Vlookup——单条件引用函数Vlookup（lookup_value,table_array,col_index_num,range_lookup）例子：1）Vlookup（D2，A1:C100，2，0）2）Vlookup（”848K15293”，A1:C100，2，0）3）Vlookup（D2&E2，A1:C100，2，0）解释：1）根据D2单元格的值，在A1:C100中搜索，并返回从A列算起，第2列（即B列）的值。2）根据”848K15293”，在A1:C100中搜索，并返回从A列算起，第2列（即B列）的值。3）根据D2与E2单元格合并后的值，在A1:C100中搜索，并返回从A列算起，第2列（即B列）的值。Page13Vlookup——单条件引用函数Vlookup（lookup_value,table_array,col_index_num,range_lookup）注意点:1）如果在查找区域table_array含有多个与lookup_value相等的值，即含有重复项，只能返回首个lookup_value值所对应的结果2）如果table_array中没有与lookup_value相等的值，函数将返回错误值#N/APage14CountIf——统计符合条件的个数，单条件统计区域条件函数解释：根据条件H2在统计区域E2：E11中，进行逐一比对（逻辑运算），最终汇总符合条件的个数条件：{A，A，A，A，A，A，A，A，A，A}统计区域：{E，B，E，C，E，A，B，A，D，A}运算方式按条件{0，0，0，0，0，1，0，1，0，1}最后对结果进行求和，如下：SUM({0,0,0,0,0,1,0,1,0,1})=3ClickPage15CountIf——统计符合条件的个数，单条件CountIf（Range，Criteria）Range：统计区域，是一维数组，即A1:A100之类，而不能是A1:C100这样多列/行Criteria：条件值，是一个值，而不是区域/数组。可以是单元格，如D2，或者固定值，如”0”例子：1）CountIf（A1:A100,D2）在A1:A100之中，统计等于D2单元格里数值的个数2）CountIf（A1:A100,”2”）在A1:A100之中，统计大于2的个数Page16CountIf——统计符合条件的个数，单条件CountIf（Range，Criteria）利用两个CountIf函数相结合使用，可以处理一些简单的双条件求个数例子：求在A1:A100之中，大于2且小于10的个数1）COUNTIF（A1:A100，”2”）统计大于2的个数2）COUNTIF（A1:A100，”10”）统计大于10的个数3）COUNTIF(A1:A100,”2”)-COUNTIF(A1:A100,”10”)即结果：大于2的个数减去大于10的个数210/////////////////Page17CountIf——统计符合条件的个数，单条件CountIf（Range，Criteria）函数解释：在单行/列的范围内（Range）统计符合条件（Criteria）的个数。主要用途：1）按条件统计个数。2）筛选有重复项的记录。用它本身A2在A2:A8中进行比对，即得出A2在A2:A8中的个数，故只要是大于1的结果，都表示含有重复项。Page18Match——查找符合条件的值所在的行号/列号例如，需查C列中的型番在A列中是否全部都包含有。{C2，C2，C2，C2，C2，C2，C2，C2，C2，C2，C2}{A1，A2，A3，A4，A5，A6，A7，A8，A9，A10，A11}比对{0，0，0，0，1，0，0，0，0，0，0}◇返回第一个1的位置，即5◇Page19Match——查找符合条件的值所在的行号/列号Match（lookup_value，lookup_array，match_type）Lookup_value：是值，非区域，例如A1，而非A1:A8。根据这个值，进行逐一比对。Lookup_array：是一个区域，一维数组，只能是一行或一列，不能多行多列。例如，A1:A100，或A1:G1，而不能A1:D10Match_type：匹配方式，精确匹配、模糊匹配，一般使用精确匹配，用数值0表示。函数解释：在lookup_array中，查找与lookup_value相符的值，并返回它所在的行/列数。1）如果lookup_array存在两个或以上的lookup_value值，函数只会返回首个lookup_value的所在行/列数。Page20Match——查找符合条件的值所在的行号/列号Match（lookup_value，lookup_array，match_type）Lookup_value：是值，非区域，例如A1，而非A1:A8。根据这个值，进行逐一比对。Lookup_array：是一个区域，一维数组，只能是一行或一列，不能多行多列。例如，A1:A100，或A1:G1，而不能A1:D10Match_type：匹配方式，精确匹配、模糊匹配，一般使用精确匹配，用数值0表示。函数解释：在lookup_array中，查找与lookup_value相符的值，并返回它所在的行/列数。2）这里所指的行/列数，与EXCEL本身的行列不同，而是看lookup_array中的行列，比如A2:A10，虽然A5是处于EXCEL的第5行，但函数会返回4，因为它是从A2开始算起Page21Sumproduct——多条件求个数、多条件求和一、多条件求和Sumproduct（(条件1)＊(条件2)＊(求和区域)）条件1条件2求和区域此时，如果用单条件求和函数SumIf则结果将会是1200+800+800=2800条件一：A2:A8=E2E2与A2:A8进行逐个比较，符合条件的返回1，不符合的返回0，条件二亦同样原理。条件一返回结果（等于“台湾”的）：{0，0，1，0，1，0，1}条件二返回结果（等于“57623”的）：{1，1，0，0，0，0，1}Page22Sumproduct——多条件求个数、多条件求和一、多条件求和Sumproduct（(条件1)＊(条件2)＊(求和区域)）条件1条件2求和区域条件一返回结果：{0，0，1，0，1，0，1}条件二返回结果：{1，1，0，0，0，0，1}相乘相乘的结果为：{0，0，0，0，0，0，1}求和区域：{1200,800,1500,250,520,400,800}{0，0，0，0，0，0，800}×||Page23Sumproduct——多条件求个数、多条件求和一、多条件求和Sumproduct（(条件1)＊(条件2)＊(求和区域)）条件1条件2求和区域{0，0，0，0，0，0，800}条件1、条件2、求和区域相乘后的结果最后求和：SUM（{0，0，0，0，0，0，800}）=800Page24Sumproduct——多条件求个数、多条件求和=Sumproduct((A2:A8=E2)＊(B2:B8=F2)＊(C2:C8))注意点：1）条件、求和区域，都是一维数组，必须是单行/列，而不能是多行/列，比如A2:A8，而不能是A2:C82）条件、求和区域，必须同时是行或同时是列