EXCEL数据分析与主要函数

1、EXCELEXCEL函数函数 与与数据分析数据分析o Excel函数与数据处理函数与数据处理EXCELEXCEL函数函数 与与数据分析数据分析3数据分析数据分析是业务发展的推动力是业务发展的推动力o随着公司的快速发展，对管理人员的数据分析能力提出了更高的随着公司的快速发展，对管理人员的数据分析能力提出了更高的要求，提高数据分析能力是提高管理能力和水平的重要内容。要求，提高数据分析能力是提高管理能力和水平的重要内容。o当前企业数据当中的大部分都属于非结构化数据，比如独立报表、当前企业数据当中的大部分都属于非结构化数据，比如独立报表、零散数据、自由文本等，致使企业不能充分利用。另一方面，企零散数据

2、、自由文本等，致使企业不能充分利用。另一方面，企业数据量非常大，而其中真正有价值的信息却很少，因此管理人业数据量非常大，而其中真正有价值的信息却很少，因此管理人员就要从大量的数据中经过深层分析，获得有利于企业运营的信员就要从大量的数据中经过深层分析，获得有利于企业运营的信息息 ，供领导决策。，供领导决策。o为贯彻落实为贯彻落实“科学发展观科学发展观”思想，深入开展思想，深入开展“管理提升月管理提升月”活动，活动，提高管理人员的数据挖掘和数据分析能力，特进行本次交流，共提高管理人员的数据挖掘和数据分析能力，特进行本次交流，共同探讨利用最常用的办公软件来管理和分析数据，提升管理水平同探讨利用最常用

3、的办公软件来管理和分析数据，提升管理水平 ，增强企业核心竞争力。增强企业核心竞争力。4总总 目目 录录o 公式与函数公式与函数o 常用函数用法常用函数用法o 应用举例应用举例5一、公式与函数一、公式与函数l 公式的特性公式的特性l 公式的输入公式的输入l 公式中的运算符公式中的运算符l 公式中的数据类型公式中的数据类型l 公式的复制和移动公式的复制和移动l 公式的调整公式的调整l 函数格式函数格式l 内置函数内置函数6公式的特性l 公式的基本特性：公式的基本特性： 公式的输入是以公式的输入是以“ “ = =”开始，公式的开始，公式的计计算结果算结果显示在单元格中，显示在单元格中，公式本身公式本

4、身显示显示在编辑栏中。在编辑栏中。(工具工具选项选项 菜单菜单) ) 如：如：=1+2+6=1+2+6 （数值计算）（数值计算） =A1+B2=A1+B2 （引用单元格地址）（引用单元格地址） 7公式的输入 = =IF(and(A1A2,B1B2),100IF(and(A1A2,B1B2),100* *A1/A2, 100A1/A2, 100* *B1/B2)B1/B2) （函数计算）（函数计算） = =“ABCABC”& &”XYZXYZ” ( (字符计算，结果字符计算，结果:ABCXYZ):ABCXYZ) =25+count(A1:C4)=25+count(A1:C4) ( (混和计算混和

5、计算) )8公式中的运算符l 算术运算符：算术运算符：+ - + - * * / % / %l 字符运算符：字符运算符：& &l 比较运算符：比较运算符：= = = = l 逻辑运算符：逻辑运算符：and or not and or not 以函数形式出现以函数形式出现l 优先级顺序：优先级顺序：算术运算符算术运算符字符运算符字符运算符比较运算符比较运算符逻辑函数符逻辑函数符 ( (使用括号可确定运算顺序使用括号可确定运算顺序) ) 9公式中的数据类型公式中的数据类型l 输入公式要注意输入公式要注意公式中可以包括：数值和字符、单元公式中可以包括：数值和字符、单元格地址、区域、区域名字、函数等格

6、地址、区域、区域名字、函数等不要随意包含空格不要随意包含空格公式中的字符要用公式中的字符要用半角引号半角引号括起来括起来公式中运算符两边的数据类型要相同公式中运算符两边的数据类型要相同 如：如：= =“abab”+25 +25 出错出错 #VALUE#VALUE10公式的复制和移动复制移动或公式时，公式会作相对调整复制移动或公式时，公式会作相对调整l 公式的复制：公式的复制：l 使用填充柄使用填充柄u菜单菜单 编辑编辑 复制复制/粘贴粘贴 或或 复制复制/选择性粘贴选择性粘贴 11公式的调整l 相对地址相对地址 在公式复制时将自动调整在公式复制时将自动调整l 绝对地址绝对地址 在公式复制时不变

7、。在公式复制时不变。 例如：例如：C3C3单元的公式单元的公式=$B$1+$B$2=$B$1+$B$2 复制到复制到D5D5中，中，D5D5单元的公式单元的公式=$B$1+$B$2=$B$1+$B$2l 混合地址混合地址 在公式复制时绝对地址不变，相对在公式复制时绝对地址不变，相对地址按规则调整。地址按规则调整。12函数格式l 函数是函数是ExcelExcel附带的预定义或内置公式附带的预定义或内置公式l 函数的格式：函数的格式： 函数名函数名( (参数参数1 1，参数，参数2,.)2,.) 函数中的参数可以是：数值、字符、逻辑函数中的参数可以是：数值、字符、逻辑值、表达式、单元格地址、区域、

8、区域名值、表达式、单元格地址、区域、区域名字等字等 没有参数的函数，括号不能省略没有参数的函数，括号不能省略 例如例如: PI( ): PI( )，RAND(), NOW( )RAND(), NOW( )13Excel内置函数内置函数o 数学和三角函数数学和三角函数o 统计函数统计函数o 文本函数文本函数o 日期与时间函数日期与时间函数o 逻辑函数逻辑函数o 财务函数财务函数o 数据库工作表函数数据库工作表函数o 工程函数工程函数o 信息函数信息函数o 查找与引用函数查找与引用函数14二、常用函数用法二、常用函数用法 重点介绍重点介绍5050个常用函数的功能、格式、参数和用法，包括：个常用函数

9、的功能、格式、参数和用法，包括：数学函数数学函数（ABSABS、MODMOD、INTINT、ROUNDROUND、ROUNDDOWNROUNDDOWN、 ROUDUPROUDUP、RANDRAND、SQRT SQRT 、 SUBTOTAL SUBTOTAL ）三角函数三角函数（SINSIN、COSCOS、PIPI）统计函数统计函数（AVERAGEAVERAGE、COUNTCOUNT、MAXMAX、MINMIN、SUMSUM、RANKRANK、 LARGE LARGE 、 FREQUENCYFREQUENCY）文本函数文本函数（TEXTTEXT、MIDMID、LEFTLEFT、RIGHTRIGH

10、T、TRIMTRIM、VALUEVALUE、LEN LEN 、 CONTAENATE CONTAENATE ）日期函数日期函数（NOWNOW、DATEDATE、DAYDAY、MONTHMONTH、TODAYTODAY、WEEKDAYWEEKDAY、DATEIFDATEIF）条件函数条件函数（IF IF、SUMIFSUMIF、COUNTIFCOUNTIF）逻辑函数逻辑函数（OROR、ANDAND）查找函数查找函数（COLUMNCOLUMN、 INDEXINDEX、MATCHMATCH、 VLOOKUP VLOOKUP ）财务函数财务函数（ PMTPMT、 PVPV、NPVNPV、IRRIRR）数

11、据库函数数据库函数(DCOUND )(DCOUND )其他函数其他函数（ ISBLANK ISBLANK 、ISERRORISERROR）15数学函数数学函数ABSo 主要功能主要功能：求出相应数字的绝对值。：求出相应数字的绝对值。 o 使用格式使用格式：ABS(number) ABS(number) o 参数说明参数说明：numbernumber代表需要求绝对值的数值或引代表需要求绝对值的数值或引用的单元格。用的单元格。 o 应用举例：如果在应用举例：如果在B2B2单元格中输入公式：单元格中输入公式：=ABS(A2)=ABS(A2)，则在，则在A2A2单元格中无论输入正数（如单元格中无论输入

12、正数（如100100）还是负数（如）还是负数（如-100-100），），B2B2中均显示出正数中均显示出正数（如（如100100）。）。 o 特别提醒特别提醒：如果：如果numbernumber参数不是数值，而是一些参数不是数值，而是一些字符（如字符（如A A等），则等），则B2B2中返回错误值中返回错误值“#VALUE#VALUE！”。 16数学函数数学函数MODo 主要功能主要功能：求出两数相除的余数。：求出两数相除的余数。 o 使用格式使用格式：MOD(number,divisor) MOD(number,divisor) o 参数说明参数说明：numbernumber代表被除数；代表被

13、除数；divisordivisor代表除数。代表除数。 o 应用举例应用举例：输入公式：输入公式：=MOD(13,4)=MOD(13,4)，确认后显示，确认后显示出结果出结果“1”1”。 o 特别提醒：特别提醒：如果如果divisordivisor参数为零，则显示错误值参数为零，则显示错误值“#DIV/0!”#DIV/0!”；MODMOD函数可以借用函数函数可以借用函数INTINT来表示：来表示：上述公式可以修改为：上述公式可以修改为：=13-4=13-4* *INT(13/4)INT(13/4)。 17数学函数数学函数INTo 主要功能主要功能：将数值向下取整为最接近的整数。：将数值向下取整

14、为最接近的整数。 o 使用格式使用格式：INT(number) INT(number) o 参数说明参数说明：numbernumber表示需要取整的数值或包含数表示需要取整的数值或包含数值的引用单元格。值的引用单元格。 o 应用举例应用举例：输入公式：输入公式：=INT(18.89)=INT(18.89)，确认后显示，确认后显示出出1818。 o 特别提醒特别提醒：在取整时，不进行四舍五入；如果输入：在取整时，不进行四舍五入；如果输入的公式为的公式为=INT(-18.89)=INT(-18.89)，则返回结果为，则返回结果为-19-19。18数学函数数学函数 ROUNDo 将数字将数字“12.

15、3456”12.3456”按照指定的位数进行四按照指定的位数进行四舍五入，可以在舍五入，可以在D3D3单元格中输入以下公式：单元格中输入以下公式：“=ROUND(B3,C3)=ROUND(B3,C3)“19数学函数数学函数 ROUNDDOWNo 向下舍入函数。向下舍入函数。o 例如：出租车的计费标准是：起步价为例如：出租车的计费标准是：起步价为5 5元，元，前前1010公里每一公里跳表一次，以后每半公里公里每一公里跳表一次，以后每半公里就跳表一次，每跳一次表要加收就跳表一次，每跳一次表要加收2 2元。输入元。输入不同的公里数，然后计算其费用。可以在不同的公里数，然后计算其费用。可以在C3C3单

16、元格中输入以下公式：单元格中输入以下公式：=IF(B3=10,5+ROUNDDOWN(B3,0)=IF(B3=18,=IF(C26=18,符合符合要求要求,不符合要求不符合要求) )，确信以后，如果，确信以后，如果C26C26单元格中的数值单元格中的数值大于或等于大于或等于1818，则，则C29C29单元格显示单元格显示“符合要求符合要求”字样，反之字样，反之显示显示“不符合要求不符合要求”字样。字样。 o 特别提醒特别提醒：本文中类似：本文中类似“在在C29C29单元格中输入公式单元格中输入公式”中指定中指定的单元格，在使用时，并不需要受其约束。的单元格，在使用时，并不需要受其约束。 49条

17、件函数条件函数SUMIFo主要功能主要功能：计算符合指定条件的单元格区域内的数值和。：计算符合指定条件的单元格区域内的数值和。 o使用格式使用格式：SUMIFSUMIF（Range,Criteria,Sum_RangeRange,Criteria,Sum_Range） o参数说明参数说明：RangeRange代表条件判断的单元格区域；代表条件判断的单元格区域；CriteriaCriteria为指定为指定条件表达式；条件表达式；Sum_RangeSum_Range代表需要计算的数值所在的单元格区代表需要计算的数值所在的单元格区域。域。 o应用举例应用举例：在：在c40c40单元格中输入公式：单元

18、格中输入公式：=SUMIF(D3:D22,=SUMIF(D3:D22,男男,F3:F22),F3:F22)，确认后即可求出，确认后即可求出“男男”生的语文成绩和。生的语文成绩和。d3d3性别计性别计算：算：=IF(LEN(C3)=18,IF(MOD(MID(C3,17,1),2)=0,女女,男男),IF(MOD(MID(C3,15,1),2)=0,女女,男男)o特别提醒特别提醒：如果把上述公式修改为：如果把上述公式修改为： =SUMIF(D3:D22,=SUMIF(D3:D22,”女女,F3:F22),F3:F22)，即可求出，即可求出“女女”生的语文成绩和；生的语文成绩和； “ “男男”和和

19、“女女” ” 是文本型。是文本型。 50条件函数条件函数COUNTIFo主要功能主要功能：统计某个单元格区域中符合指定条件的单元格数目。：统计某个单元格区域中符合指定条件的单元格数目。 o使用格式使用格式：COUNTIF(Range,Criteria) COUNTIF(Range,Criteria) o参数说明参数说明：RangeRange代表要统计的单元格区域；代表要统计的单元格区域；CriteriaCriteria表示指表示指定的条件表达式。定的条件表达式。 o应用举例应用举例：在：在C17C17单元格中输入公式：单元格中输入公式：=COUNTIF(f3:f22,”=80”)=COUNTI

20、F(f3:f22,”=80”)，确认后，即可统计出，确认后，即可统计出f3f3至至f22f22单单元格区域中，数值大于等于元格区域中，数值大于等于8080的单元格数目。的单元格数目。 假如假如d3:d22区域内存放着员工的性别，则公式区域内存放着员工的性别，则公式“=COUNTIF(d3:d22，”女女”)”统计其中的女职工数量统计其中的女职工数量 o特别提醒特别提醒：允许引用的单元格区域中有空白单元格出现：允许引用的单元格区域中有空白单元格出现 51逻辑函数逻辑函数ORo主要功能主要功能：返回逻辑值，仅当所有参数值均为逻辑：返回逻辑值，仅当所有参数值均为逻辑“假假（FALSEFALSE）”时

21、返回函数结果逻辑时返回函数结果逻辑“假（假（FALSEFALSE）”，否则都返，否则都返回逻辑回逻辑“真（真（TRUETRUE）”。 o使用格式使用格式：OR(logical1,logical2, .) OR(logical1,logical2, .) o参数说明参数说明：Logical1,Logical2,Logical3Logical1,Logical2,Logical3：表示待测试的：表示待测试的条件值或表达式，最多这条件值或表达式，最多这3030个。个。 o应用举例应用举例：在：在C40C40单元格输入公式：单元格输入公式：=OR(f3=60,f4=60)=OR(f3=60,f4=60

22、)，确，确认。如果认。如果C40C40中返回中返回TRUETRUE，说明，说明A62A62和和B62B62中的数值至少有一中的数值至少有一个大于或等于个大于或等于6060，如果返回，如果返回FALSEFALSE，说明，说明A62A62和和B62B62中的数值都中的数值都小于小于6060。 o特别提醒特别提醒：如果指定的逻辑条件参数中包含非逻辑值时，则函：如果指定的逻辑条件参数中包含非逻辑值时，则函数返回错误值数返回错误值“#VALUE!”#VALUE!”或或“#NAME”#NAME”。 52逻辑函数逻辑函数ANDo 主要功能主要功能：返回逻辑值：如果所有参数值均为逻辑：返回逻辑值：如果所有参数

23、值均为逻辑“真真（TRUETRUE）”，则返回逻辑，则返回逻辑“真（真（TRUETRUE）”，反之返回逻，反之返回逻辑辑“假（假（FALSEFALSE）”。 o 使用格式使用格式：AND(logical1,logical2, .) AND(logical1,logical2, .) o 参数说明参数说明：Logical1,Logical2,Logical3Logical1,Logical2,Logical3：表示待：表示待测试的条件值或表达式，最多这测试的条件值或表达式，最多这3030个。个。 o 应用举例应用举例：在：在C38C38单元格输入公式：单元格输入公式：=AND(f5=60,f6=

24、60)=AND(f5=60,f6=60)，确认。如果，确认。如果C38C38中返回中返回TRUETRUE，说明说明f5f5和和f6f6中的数值均大于等于中的数值均大于等于6060，如果返回，如果返回FALSEFALSE，说，说明明f5f5和和f6f6中的数值至少有一个小于中的数值至少有一个小于6060。 o 特别提醒特别提醒：如果指定的逻辑条件参数中包含非逻辑值时，：如果指定的逻辑条件参数中包含非逻辑值时，则函数返回错误值则函数返回错误值“#VALUE!”#VALUE!”或或“#NAME”#NAME”。 53财务函数财务函数PMTo 主要功能主要功能：求贷款分期偿还额求贷款分期偿还额 o 语法

25、形式：语法形式：PMT(rate,nper,pv,fv,type)PMT(rate,nper,pv,fv,type)其中，其中，raterate为各期为各期利率，是一固定值，利率，是一固定值，npernper为总投资（或贷款）期，即该项为总投资（或贷款）期，即该项投资（或贷款）的付款期总数，投资（或贷款）的付款期总数，pvpv为现值，或一系列未来为现值，或一系列未来付款当前值的累积和，也称为本金，付款当前值的累积和，也称为本金，fvfv为未来值，或在最为未来值，或在最后一次付款后希望得到的现金余额，如果省略后一次付款后希望得到的现金余额，如果省略fvfv，则假设，则假设其值为零（例如，一笔贷款

26、的未来值即为零），其值为零（例如，一笔贷款的未来值即为零），typetype为为0 0或或1 1，用以指定各期的付款时间是在期初还是期末。如果省，用以指定各期的付款时间是在期初还是期末。如果省略略typetype，则假设其值为零。，则假设其值为零。o 应用举例应用举例：假如你为购房贷款十万元，如果年利率为假如你为购房贷款十万元，如果年利率为7%7%，每月末还款。采用十年还清方式时，月还款，每月末还款。采用十年还清方式时，月还款额计算公式为额计算公式为“=PMT(7%/12,120,-100000)=PMT(7%/12,120,-100000)”。其。其结果为￥结果为￥-1,161.08-1,1

27、61.08，就是你每月须偿还贷款，就是你每月须偿还贷款1161.081161.08元。元。 54财务函数财务函数PV o 主要功能主要功能：零存整取收益函数零存整取收益函数o 语法形式语法形式：PVPV（raterate，npernper，pmtpmt，fvfv，typetype）。）。raterate为存款利率；为存款利率；npernper为总的存款时间，对于三为总的存款时间，对于三年期零存整取存款来说共有年期零存整取存款来说共有3 3* *12=3612=36个月；个月；pmtpmt为为每月存款金额，如果忽略每月存款金额，如果忽略pmtpmt则公式必须包含参数则公式必须包含参数fvfv；f

28、vfv为最后一次存款后希望得到的现金总额，如为最后一次存款后希望得到的现金总额，如果省略了果省略了fvfv则公式中必须包含则公式中必须包含pmtpmt参数；参数；typetype为数为数字字0 0或或1 1，它指定存款时间是月初还是月末。，它指定存款时间是月初还是月末。 o 应用举例应用举例：假如你每月初向银行存入现金假如你每月初向银行存入现金500500元，元，如果年利如果年利2.15%2.15%（按月计息，即月息（按月计息，即月息2.15%/122.15%/12）。）。如果你想知道如果你想知道5 5年后的存款总额是多少，可以使用年后的存款总额是多少，可以使用公式公式“=FV(2.15%/1

29、2,60,-500,0,1)”计算，其结果计算，其结果为￥为￥3131，698.67698.67。 55财务函数财务函数NPV o 主要功能主要功能：求投资的净现值：求投资的净现值：o 语法形式语法形式：NPV(rate,value1,value2,.)NPV(rate,value1,value2,.)其中，其中，raterate为各期贴现率，是一固定值；为各期贴现率，是一固定值；value1,value2,.value1,value2,.代表代表1 1到到2929笔支出及收入笔支出及收入的参数值，的参数值，value1,value2,.value1,value2,.所属各期间的所属各期间的长

30、度必须相等，而且支付及收入的时间都发长度必须相等，而且支付及收入的时间都发生在期末。生在期末。 o 应用举例应用举例： =NPV(8=NPV(8,A3:A8),A3:A8)56财务函数财务函数IRRo主要功能主要功能：返回内部收益率返回内部收益率o语法形式：语法形式： IRR(values,guess)IRR(values,guess)其中其中valuesvalues为数组或单元格的引用，包含为数组或单元格的引用，包含用来计算内部收益率的数字，用来计算内部收益率的数字，valuesvalues必须包含至少一个正值和一个负值，必须包含至少一个正值和一个负值，以计算内部收益率，函数以计算内部收益率

31、，函数IRRIRR根据数值的顺序来解释现金流的顺序，故应根据数值的顺序来解释现金流的顺序，故应确定按需要的顺序输入了支付和收入的数值，如果数组或引用包含文本、确定按需要的顺序输入了支付和收入的数值，如果数组或引用包含文本、逻辑值或空白单元格，这些数值将被忽略；逻辑值或空白单元格，这些数值将被忽略；guessguess为对函数为对函数IRRIRR计算结果计算结果的估计值，的估计值，excelexcel使用迭代法计算函数使用迭代法计算函数IRRIRR从从guessguess开始，函数开始，函数IRRIRR不断修不断修正收益率，直至结果的精度达到正收益率，直至结果的精度达到0.00001%0.000

32、01%，如果函数，如果函数IRRIRR经过经过2020次迭代，次迭代，仍未找到结果，则返回错误值仍未找到结果，则返回错误值#NUM#NUM！，在大多数情况下，并不需要为！，在大多数情况下，并不需要为函数函数IRRIRR的计算提供的计算提供guessguess值，如果省略值，如果省略guessguess，假设它为，假设它为0.10.1（10%10%）。）。如果函数如果函数IRRIRR返回错误值返回错误值#NUM#NUM！，或结果没有靠近期望值，可以给！，或结果没有靠近期望值，可以给guessguess换一个值再试一下。换一个值再试一下。o应用举例应用举例：如果要开办一家服装商店，预计投资为￥如果

33、要开办一家服装商店，预计投资为￥110,000110,000，并预期为，并预期为今后五年的净收益为：￥今后五年的净收益为：￥15,00015,000、￥、￥21,00021,000、￥、￥28,00028,000、￥、￥36,00036,000和和￥45,00045,000。分别求出投资两年、四年以及五年后的内部收益率。分别求出投资两年、四年以及五年后的内部收益率。57数据库函数数据库函数DCOUNTo 主要功能主要功能：返回数据库或列表的列中满足指定条件并：返回数据库或列表的列中满足指定条件并且包含数字的单元格数目。且包含数字的单元格数目。 o 使用格式使用格式：DCOUNT(databas

34、e,field,criteria) DCOUNT(database,field,criteria) o 参数说明参数说明：DatabaseDatabase表示需要统计的单元格区域；表示需要统计的单元格区域；FieldField表示函数所使用的数据列（在第一行必须要有表示函数所使用的数据列（在第一行必须要有标志项）；标志项）；CriteriaCriteria包含条件的单元格区域。包含条件的单元格区域。 o 应用举例应用举例：如图：如图1 1所示，在所示，在F4F4单元格中输入公式：单元格中输入公式：=DCOUNT(A1:D11,“=DCOUNT(A1:D11,“语文语文”,F1:G2),F1:G

35、2)，确认后即可，确认后即可求出求出“语文语文”列中，成绩大于等于列中，成绩大于等于7070，而小于，而小于8080的的数值单元格数目（相当于分数段人数）。数值单元格数目（相当于分数段人数）。 o 特别提醒特别提醒：如果将上述公式修改为：如果将上述公式修改为：=DCOUNT(A1:D11,F1:G2)=DCOUNT(A1:D11,F1:G2)，也可以达到相同目的。，也可以达到相同目的。 58数据库函数数据库函数 DCOUNT（续）（续）59查找函数查找函数COLUMNo 主要功能主要功能：显示所引用单元格的列标号值。：显示所引用单元格的列标号值。 o 使用格式使用格式：COLUMN(refer

36、ence) COLUMN(reference) o 参数说明参数说明：referencereference为引用的单元格。为引用的单元格。 o 应用举例应用举例：在：在C11C11单元格中输入公式：单元格中输入公式：=COLUMN(B11)=COLUMN(B11)，确认后显示为，确认后显示为2 2（即（即B B列）。列）。 o 特别提醒特别提醒：如果在：如果在B11B11单元格中输入公式：单元格中输入公式：=COLUMN()=COLUMN()，也显示出，也显示出2 2；与之相对应的还有一；与之相对应的还有一个返回行标号值的函数个返回行标号值的函数ROWROW(reference)(refere

37、nce)。 60查找函数查找函数INDEXo主要功能主要功能：返回列表或数组中的元素值，此元素由行序号和列：返回列表或数组中的元素值，此元素由行序号和列序号的索引值进行确定。序号的索引值进行确定。 o使用格式使用格式：INDEX(array,row_num,column_num) INDEX(array,row_num,column_num) o参数说明参数说明：ArrayArray代表单元格区域或数组常量；代表单元格区域或数组常量；Row_numRow_num表示表示指定的行序号（如果省略指定的行序号（如果省略row_numrow_num，则必须有，则必须有 column_numcolumn

38、_num）；）；Column_numColumn_num表示指定的列序号（如果省表示指定的列序号（如果省略略column_numcolumn_num，则必须有，则必须有 row_numrow_num）。）。 o应用举例应用举例：如图：如图3 3所示，在所示，在F8F8单元格中输入公式：单元格中输入公式：=INDEX(A1:D11,4,3)=INDEX(A1:D11,4,3)，确认后则显示出确认后则显示出A1A1至至D11D11单元格区域中，单元格区域中，第第4 4行和第行和第3 3列交叉处的单元格（即列交叉处的单元格（即C4C4）中的内容。）中的内容。 o特别提醒特别提醒：此处的行序号参数（：

39、此处的行序号参数（row_numrow_num）和列序号参数）和列序号参数（column_numcolumn_num）是相对于所引用的单元格区域而言的，不是）是相对于所引用的单元格区域而言的，不是ExcelExcel工作表中的行或列序号。工作表中的行或列序号。 61查找函数查找函数INDEX（续）（续）62查找函数查找函数MATCHo主要功能主要功能：返回在指定方式下与指定数值匹配的数组中元素的相：返回在指定方式下与指定数值匹配的数组中元素的相应位置。应位置。 o使用格式使用格式：MATCH(lookup_value,lookup_array,match_type) MATCH(lookup_

40、value,lookup_array,match_type) o参数说明参数说明：Lookup_valueLookup_value代表需要在数据表中查找的数值；代表需要在数据表中查找的数值；Lookup_arrayLookup_array表示可能包含所要查找的数值的连续单元格表示可能包含所要查找的数值的连续单元格区域；区域；Match_typeMatch_type表示查找方式的值（表示查找方式的值（-1-1、0 0或或1 1）。）。如果如果match_typematch_type为为-1-1，查找大于或等于，查找大于或等于 lookup_valuelookup_value的的最小数值，最小数值

41、，Lookup_array Lookup_array 必须按降序排列；必须按降序排列；如果如果match_typematch_type为为1 1，查找小于或等于，查找小于或等于 lookup_value lookup_value 的的最大数值，最大数值，Lookup_array Lookup_array 必须按升序排列；必须按升序排列；如果如果match_typematch_type为为0 0，查找等于，查找等于lookup_value lookup_value 的第一个的第一个数值，数值，Lookup_array Lookup_array 可以按任何顺序排列；如果省略可以按任何顺序排列；如果

42、省略match_typematch_type，则默认为，则默认为1 1。 o应用举例应用举例：如图：如图4 4所示，在所示，在F2F2单元格中输入公式：单元格中输入公式：=MATCH(E2,B1:B11,0)=MATCH(E2,B1:B11,0)，确认后则返回查找的结果，确认后则返回查找的结果“9”9”。 o特别提醒特别提醒：Lookup_arrayLookup_array只能为一列或一行。只能为一列或一行。 63查找函数查找函数MATCH264查找函数查找函数VLOOKUPo主要功能主要功能：在数据表的首列查找指定的数值，并由此返回数据：在数据表的首列查找指定的数值，并由此返回数据表当前行中

43、指定列处的数值。表当前行中指定列处的数值。 o使用格式使用格式：VLOOKUP(lookup_value,table_array,col_index_num,ranVLOOKUP(lookup_value,table_array,col_index_num,range_lookup) ge_lookup) o参数说明参数说明：Lookup_valueLookup_value代表需要查找的数值；代表需要查找的数值；Table_arrayTable_array代表需要在其中查找数据的单元格区域；代表需要在其中查找数据的单元格区域；Col_index_numCol_index_num为在为在tabl

44、e_arraytable_array区域中待返回的匹配值的列序号（当区域中待返回的匹配值的列序号（当Col_index_numCol_index_num为为2 2时时, ,返回返回table_arraytable_array第第2 2列中的数值，为列中的数值，为3 3时，返回第时，返回第3 3列的值列的值）；）；Range_lookupRange_lookup为一逻辑值，如为一逻辑值，如果为果为TRUETRUE或省略，则返回近似匹配值，也就是说，如果找不到或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于精确匹配值，则返回小于lookup_valuelookup_value的

45、最大数值；如果为的最大数值；如果为FALSEFALSE，则返回精确匹配值，如果找不到，则返回错误值，则返回精确匹配值，如果找不到，则返回错误值#N/A#N/A。 65o 应用举例应用举例：成绩表：成绩表2 2中，我们在中，我们在G27G27单元格单元格中输入公式：中输入公式：=VLOOKUP(孙丹孙丹,B2:C21,2,FALSE) )，确认后，单元格中，确认后，单元格中即刻显示出该学生的语文成绩。即刻显示出该学生的语文成绩。 o 特别提醒特别提醒：Lookup_valueLookup_value参见必须在参见必须在Table_arrayTable_array区域的首列中；如果忽略区域的首列中

46、；如果忽略Range_lookupRange_lookup参数，则参数，则Table_arrayTable_array的首的首列必须进行排序；在此函数的向导中，有关列必须进行排序；在此函数的向导中，有关Range_lookupRange_lookup参数的用法是错误的。参数的用法是错误的。 VLOOKUP （续）（续）66其他函数其他函数ISBLANKo 此函数可以判断单元格是否为空。例如判断员此函数可以判断单元格是否为空。例如判断员工是否到岗：工是否到岗：1）输入姓名和上班时间，如图）输入姓名和上班时间，如图75所示；所示；2）判断其是否到岗，在单元格）判断其是否到岗，在单元格E3中中输入以

47、下公式：输入以下公式：“=IF(ISBLANK(D3),请请假假,到岗到岗)”。 67其他函数其他函数 ISERRORo 主要功能主要功能：用于测试函数式返回的数值是否有错。如果：用于测试函数式返回的数值是否有错。如果有错，该函数返回有错，该函数返回TRUETRUE，反之返回，反之返回FALSEFALSE。 o 使用格式使用格式：ISERROR(value) ISERROR(value) o 参数说明参数说明：ValueValue表示需要测试的值或表达式。表示需要测试的值或表达式。 o 应用举例应用举例：输入公式：输入公式：=ISERROR(A35/B35)=ISERROR(A35/B35)，

48、确认以，确认以后，如果后，如果B35B35单元格为空或单元格为空或“0”0”，则，则A35/B35A35/B35出现错出现错误，此时前述函数返回误，此时前述函数返回TRUETRUE结果，反之返回结果，反之返回FALSEFALSE。 o 特别提醒特别提醒：此函数通常与：此函数通常与IF IF函数配套使用，如果将上述函数配套使用，如果将上述公式修改为：公式修改为：=IF(ISERROR(A35/B35),A35/B35)=IF(ISERROR(A35/B35),A35/B35)，如，如果果B35B35为空或为空或“0”0”，则相应的单元格显示为空，反之，则相应的单元格显示为空，反之显示显示A35/

49、B35A35/B35的结果。的结果。 68三、应用举例三、应用举例l 利用函数进行等级评定利用函数进行等级评定l 统计学生考试成绩统计学生考试成绩l 自动录入性别自动录入性别l 根据身份证号提取出生日期根据身份证号提取出生日期l 年龄统计年龄统计l 位次阈值统计位次阈值统计l 让让Excel按人打出工资条按人打出工资条l Word表格计算表格计算69利用函数进行等级评定利用函数进行等级评定o 在在F2F2单元格中输入：单元格中输入：=CONCATENATE(IF(C2=80,A,IF(C2=CONCATENATE(IF(C2=80,A,IF(C2=60,B,C),IF(D2=80,A,IF(D

50、2=60,B,60,B,C),IF(D2=80,A,IF(D2=60,B,C),IF(E2=80,A,IF(E2=60,B,C)C),IF(E2=80,A,IF(E2=60,B,C)，然后把鼠标指针指向然后把鼠标指针指向F2F2单元格的右下角，等单元格的右下角，等鼠标指针变成黑色十字加号时，按住左键向鼠标指针变成黑色十字加号时，按住左键向右拖动到这列单元格的最后放手。右拖动到这列单元格的最后放手。 7071利用函数进行等级评定利用函数进行等级评定(续续)o 也可以在也可以在F2F2单元格中输入：单元格中输入：=IF(C2=80,A,IF(C2=60,B,C)&IF(D2=IF(C2=80,A,

51、IF(C2=60,B,C)&IF(D2=80,A,IF(D2=60,B,C)&IF(E2=80,=80,A,IF(D2=60,B,C)&IF(E2=80,A,IF(E2=60,B,C)A,IF(E2=60,B,C)，然后把鼠标指针指，然后把鼠标指针指向向F2F2单元格的右下角，等鼠标指针变成黑色单元格的右下角，等鼠标指针变成黑色十字加号时，按住左键向右拖动到这列单元十字加号时，按住左键向右拖动到这列单元格的最后放手。格的最后放手。 7273统计学生考试成绩统计学生考试成绩 o 先点击先点击f23f23单元格，输入如下公式：单元格，输入如下公式：=AVERAGE(f3:f22)=AVERAGE(

52、f3:f22)，回车后即可得到语文平均分。，回车后即可得到语文平均分。o 点击点击f24f24单元格，输入公式：单元格，输入公式：=MAX(f$3:f$22)=MAX(f$3:f$22)，回车，回车即可得到语文成绩中的最高分。即可得到语文成绩中的最高分。o 优秀率是计算分数高于或等于优秀率是计算分数高于或等于8585分的学生的比率。点分的学生的比率。点击击f25f25单元格，输入公式：单元格，输入公式：=COUNTIF(C$2:C$95,=85)/COUNT(C$2:C$95)=COUNTIF(C$2:C$95,=85)/COUNT(C$2:C$95)，回车所得即为语文学科的优秀率。回车所得即

53、为语文学科的优秀率。o 点击点击f26f26单元格，输入公式：单元格，输入公式：=COUNTIF(C$2:C$95,=60)/COUNT(C$2:C$95)=COUNTIF(C$2:C$95,=60)/COUNT(C$2:C$95)，回车所得即为及格率。回车所得即为及格率。74o 选中选中f23:f26f23:f26单元格，拖动填充句柄向右填充公式至单元格，拖动填充句柄向右填充公式至h26h26单元格，松开鼠标，各学科的统计数据就出来单元格，松开鼠标，各学科的统计数据就出来了。了。o 至于各科分数段人数的统计，那得先选中至于各科分数段人数的统计，那得先选中f28:f35f28:f35单单元格，

54、在编辑栏中输入公式：元格，在编辑栏中输入公式：=FREQUENCY(F$3:F$22,$C$28:$C$35)=FREQUENCY(F$3:F$22,$C$28:$C$35)。然后按。然后按下下“Ctrl+Shift+Enter”Ctrl+Shift+Enter”快捷键，可以看到在公式快捷键，可以看到在公式的最外层加上了一对大括号。现在，我们就已经得的最外层加上了一对大括号。现在，我们就已经得到了语文学科各分数段人数了。在到了语文学科各分数段人数了。在K K列中的那些数列中的那些数字，就是我们统计各分数段时的分数分界点。字，就是我们统计各分数段时的分数分界点。o 现在再选中现在再选中f28:f

55、35f28:f35单元格，拖动其填充句柄向右至单元格，拖动其填充句柄向右至h h列，那么，其它学科的分数段人数也立即显示在列，那么，其它学科的分数段人数也立即显示在我们眼前了。我们眼前了。 75自动录入性别自动录入性别o 在在d3d3单元格中输入单元格中输入“IF(LEN(C3)=18,IF(MOD(MID(C3,17,1),2)IF(LEN(C3)=18,IF(MOD(MID(C3,17,1),2)=0,=0,女女,男男),IF(MOD(MID(C3,15,1),2)=0,),IF(MOD(MID(C3,15,1),2)=0,女女,男男)”。回车后即可在单元格获得该职。回车后即可在单元格获得

56、该职工的性别，而后只要把公式复制到工的性别，而后只要把公式复制到D3D3、D4D4等单元格，即可得到其他职工的性别。等单元格，即可得到其他职工的性别。 76根据身份证号提取出生日期根据身份证号提取出生日期o 在单元格中输入公式在单元格中输入公式“=IF(LEN(C3)=15,CONCATENATE(19,M=IF(LEN(C3)=15,CONCATENATE(19,MID(C3,7,2),ID(C3,7,2),年年,MID(C3,9,2),MID(C3,9,2),月月,MID(C3,11,2),MID(C3,11,2),日日),CONCATENATE(MID(C3,7,4),),CONCATE

57、NATE(MID(C3,7,4),年年,MID(C3,11,2),MID(C3,11,2),月月,MID(C3,13,2),MID(C3,13,2),日日)”。 77年龄统计年龄统计 o 工作表的工作表的E2:E600E2:E600单元格存放职工的工龄，我们要以单元格存放职工的工龄，我们要以5 5年年为一段分别统计年龄小于为一段分别统计年龄小于2020岁、岁、2020至至2525岁之间，一直到岁之间，一直到5555至至6060岁之间的年龄段人数，可以采用下面的操作方法。岁之间的年龄段人数，可以采用下面的操作方法。o 首先在工作表中找到空白的首先在工作表中找到空白的K K列列( (或其他列或其他

58、列) )，自，自K2K2单元格单元格开始依次输入开始依次输入2020、2525、3030、3535、40.6040.60，分别表示统计，分别表示统计年龄小于年龄小于2020、2020至至2525之间、之间、2525至至3030之间等的人数。然后之间等的人数。然后在该列旁边选中相同个数的单元格，例如在该列旁边选中相同个数的单元格，例如J2:J10J2:J10准备存准备存放各年龄段的统计结果。然后在编辑栏输入公式放各年龄段的统计结果。然后在编辑栏输入公式“=FREQUENCY(YEAR(TODAY()-=FREQUENCY(YEAR(TODAY()-YEAR(E2:E600),K2:K10)”YE

59、AR(E2:E600),K2:K10)”，按下，按下Ctrl+Shift+EnterCtrl+Shift+Enter组合组合键即可在选中单元格中看到计算结果。其中位于键即可在选中单元格中看到计算结果。其中位于J2J2单元单元格中的结果表示年龄小于格中的结果表示年龄小于2020岁的职工人数，岁的职工人数，J3J3单元格中单元格中的数值表示年龄在的数值表示年龄在2020至至2525之间的职工人数等。之间的职工人数等。78位次阈值统计位次阈值统计 o 假设假设C2:C21C2:C21区域存放着学生的考试成绩，区域存放着学生的考试成绩，首先在首先在D D列选取空白单元格列选取空白单元格D3D3，在其中

60、输入，在其中输入公式公式“=PERCENTILE(C2:C21,0.67=PERCENTILE(C2:C21,0.67) )”。其。其中中D2D2作为输入百分点变量的单元格，如果作为输入百分点变量的单元格，如果你在其中输入你在其中输入0.330.33，公式就可以返回名次达，公式就可以返回名次达到前到前1/31/3所需要的成绩。所需要的成绩。79让让Excel按人打出工资条按人打出工资条 o 新建一新建一ExcelExcel文件，在文件，在sheet1sheet1中存放工资表的原始中存放工资表的原始数据，假设有数据，假设有N N列。第一行是工资项目，从第二行列。第一行是工资项目，从第二行开始是每