Excel常用函数在工作中的应用
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
Leabharlann Baidu
公式2：条件升序
=IF(C4<=59,"不称职",IF(C4<=74,"基本称职",IF(C4<=84,"称职","优秀")))
公式3：条件乱序（报错）
=IF(C6<=59,"不称职",IF(C6<=84,"称职",IF(C6<=74,"基本称职","优秀")))
原因：
若“条件”属序列关系，则必须采用升序或降序设置IF函数公式，乱序则出错。 因为函数公式运行规则是：自左到右，即先执行IF(C6<=84,“称职”……），再
学习EXCEL函数公式解决问题的简要方法
1、掌握常用函数的命令、格式、用途、各参数要求（包括函数的优势与不 足）；
2、掌握各常用函数混编组合公式的格式与逻辑关系，尤其是各参数、分隔逗号、括号、绝 对引用、相对引用、混合引用、逻辑或、逻辑且等组合元素的位置与层次关系（技巧： 由最里逐级向外，分清每一函数公式的层次，在该层次中，必须符合该函数的格式要求， 括号必须对应成对，注：用鼠标在编辑栏选中全部公式后，凡对应成对的括号，均为同 一颜色）； 3、在构思函数公式前，必须先明确：要解决什么问题或存在什么问题，有什么已知条件， 再判断需要使用什么函数或函数组合。类似于平面几何证明题：可以从已知条件入手，
$右边的字母（即列）或数字（即行），复制时，其位置是不变的，反之就会改变。
7、关于循环引用：若确实需要循环引用同一单元格或单元区域，则必须 Excel选项中开启迭代运 算选项时，否则不能进行循环引用公式的计算，并自动提示报错。 8、在编辑栏中输入等号和函数英文字母时，电脑会自动在编辑栏下方逐字显示相应函数命令，
2、党费缴费比例及金额自动充填计算案例
说明：
1、如果采用方法1和方法2，则需依据当前人员类别，输入相应的函数 公式。不可将方法1或方法2复制到整个表的“比例”栏，否则报错： 显示为FALSE。 2、方法3可适用整个工作表，可将方法3复制到整个表的“比例”栏。 原因是： 在方法1和方法2的函数公式中，没有包涵所有人员类别。因IF函数在 指定单元格中找不到设定的人员类别参数而报错。而方法3函数公式 的参数设置涵盖了整个工作表，因此更适用。
逐步推进，寻找解决办法；也可从问题入手，逆向分解，寻找规律，解决问题。
特别说明：与平面几何证明题一样，用函数公式解决工作当中的实际问题的方法，也有2 种以上的方法，所不同的是：使用的函数不同、编写的公式长短不同、公式的繁简不同。
你的EXCEL函数水平有多高？
1、菜鸟级 会使用最常简的求和、求平均数函数 SUM 、AVERSE、MAX、MIN 2、新手级 比菜鸟会用更多的函数，会使用IF函数进行简单判断、会对数字进行四舍五入运算。 IF、COUNT、LARDE、ROUND、INT、TODAY 3、入门级 对函数已有初步了解，熟悉最常用函数的使用方法。 IFERROR、VLOOKUP、SUMIF、SUMPRODUCT 4、初级 已能熟练使用更多常用函数的公式设置，但对函数的深入用法不够了解。 MATCH、DATEDIF、SUMIFS、COUNTIFS 5、中级 这部分用户在单位里已属函数高手级别，已能解决复杂的函数问题，但公式优化还需学习。 OFFSET、INDIRECT、LOOKUP、TXTE 6、高级 对Excel函数已达到很高的境界，已能运用数组公式解决复杂的数据计算和处理。 FREQUENGY、MMULT、T/N、SUBSTOTAL 7、大师级 不用说了，这类是函数高手中的高手，没有他写不出的公式，解题思路已成系统。 二分法、三维引用、数组维数转换
IF函数应用实例三
1、1934年9月30日前出生增100元； 2、1934年10月1日—1939年9月30日期间出生增60元
第一列：=IF(D4="",0,IF(D4<=DATE(1934,9,30),100,0)) 第二列：
=IF(D4="",0,IF(AND(D4>DATE(1934,9,30),D4<=DATE(1939,9,30)),60,0))
执行IF(C6<=74,“基本称职“……），而C6<=84就包括了C6<=74，犯了逻辑错
误，因此不显示60～74成绩区间的“基本称职”。
设置IF函数公式注意事项：
1、要使IF函数公式适用于整个工作表，其采用的条件关键词，
必须涵盖整个工作表。 2、嵌套的层次关系要明晰，并符合IF函数公式的格式要求。
=IF(逻辑判断表达式1, "当判断条件成立时显示或执行的内容", IF(逻 辑判断表达式2, "当判断条件成立时显示或执行的内容……, "当判断条 件不成立时显示或执行的内容"))
IF函数应用实例一
进行校对：
通过函数公式对2人及2人以上编辑的相同电子表文档数据进行校 对，并对不正确的数据进行标注。
根据综合考核成绩所在区段，对应显示等级：
序号 1 2 3 4 成绩区间 0～59 60～74 75～84 84～100 等级 不称职 基本称职 称职 优秀 备注
4、综合考核统计案例
公式1：条件降序
=IF(C4>=85,"优秀",IF(C4>=74,"称职",IF(C4>=60,"基本称职","不称职")))
EXCEL常用函数在工作中的应用
闵华荣
2016年1月3日
EXCEL函数概述
Excel 是微软office办公软件的一个重要组成部分，具有强大的计算 统计功能，它可以进行各种数据的处理、统计分析和辅助决策操作， 广泛地应用于管理、统计、财经、金融等众多领域。
Excel中的函数功能，可以执行指定计算，分析信息，尤其是Excel 函数的组合使用，可以使十分复杂的计算、统计、查询、排版和管 理，简便化，起到事半功倍的效果。
供你选择。
EXCEL函数公式一般格式
不同函数公式，有不同的格式要求，必须按格式要求设置公式，尤其要注意函数公式中 的嵌套逻辑关系，否则报错： 1、=IF(条件， "条件成立时执行的内容", "条件不成立时执行的内容")； 2、=IF(AND(OR(G4= "条件1 ",G4= "条件2 "),I4>=30,H4= "条件3 "), "满足条件执行的 内容", "满足条件执行的内容") 3、=sum(需要进行汇总的数据区域)； 4、=COUNTIF(统计区域,“统计条件”)； 5、=LOOKUP(查找条件,查找条件所在区域,满足条件的对象所在区域)； 6、=SUMIFS(汇总数据区域,条件1所在区域,"条件1 ",条件2所在区域,"条件2 ",条件3所在 区域,"条件3 ",……条件n所在区域,"条件n") …………在此不一一例举。 以下介绍函数时，均会介绍函数的用途、格式等。
二列合一： =IF(D39="",0,IF(D39<=DATE(1934,9,30),100,IF(D39="",0,IF(AND( D39>DATE(1934,9,30),D39<=DATE(1939,9,30)),60,0))))
3、根据出生年月日自动判定增加离退休费案例
IF函数应用实例四
根据分数段，显示等级：
=IF(原文档G4=用于校对!G4,"正确","不正确")
即：在原文档相应单元格中输入公式： =IF(原文档中需要进行校对的数据单元格=另一文档中的该数据单元 格，“若相等显示：正确”, “若不相等显示：不正确") 适用于表间单元格内容校对
1、进行校对案例
IF函数应用实例二
根据指定类别，显示对应内容：
技巧是：
1、以第一个IF，按函数格式逻辑关系进行审核，然后审核第 二个IF，经此类推。 2、用鼠标在编辑状态栏内选中整个公式（同一级成对的括号为 同色），由内向外以左、右括号成对判定其位置是否正确。
5、IF函数嵌套逻辑关系图
二、函数名称：COUNT、COUNTA、COUNTIF
主要功能：COUNT统计某个单元格区域中数字单元格数据
根据党员类别和工资收入，自动选择并显示对应缴费比例或金额。 党费收缴标准如下： 党费收缴标准
党员类别 工资收入 缴费比例或金额
家属、下岗
无工资收入
0.2～1.00元
≤3000
0.50%
＞3000，≤5000 在岗 ＞5000，≤10000
1.00%
1.50%
＞10000
2.00%
≤5000 离退休 ＞5000
适用范围：
需要使用统计区域的数字单元格个数、非空单元格个数、或符合给定条件 的单元格个数参与计算的。 6、用COUNT、 COUNTA、 COUNTIF函数分别统计案例
COUNTIF函数应用实例一
根据给定条件，在指定单元格区域内统计符合条件的数量：
1、在统计全队职工中，女职工人数： =countif(表“性别”所在列,"女")
COUNTA统计某个单元格区域中非空单元格数据 COUNTIF统计某个单元格区域中符合指定条件的单元格数目。
使用格式：
= COUNT （单元格区域，或文本、或数字） = COUNT A（单元格区域，或文本、或数字） 注：以上2函数：若括号内有文本、或数字，则和单元格区域一并参与统计） = COUNTIF(要统计的单元格区域, "给定条件")
EXCEL函数公式编写规则
1、所有的公式必须在英文状态下输入，并以等号开头；
2、函数名，如IF，均写在等号的后面再加一个括号，如 =IF(A2=B2,”正确”,”不正确”)； 3、公式的函数名和单元格的列号，都不区分大小写； 4、在Excel的公式嵌套中，只使用小括号。嵌套时，新嵌套进去的公式，也要把函数名写在最前 面、后加括号； 5、当公式引用的单元格发生变化时，公式会自动重新计算结果，并显示在输入公式的单元格内； 6、注意绝对引用（$A$5）、相对引用（A5）及混合引用（$A5与A$5）的作用：凡有$符号的，
COUNTIF函数中的日期格式为：1960/1/1。 IF函数中的日期格式为：DATE(1960,1,1)
=IF(OR(G38=“家属”,G38=“下岗”),“”,IF(G38=“离退休”,IF(M38<=5000,“0.5%”,“1.0%”))) 对家属、下岗和离退休党员有效，对在册党员无效。
方法3、以多“人员类别”
=IF(G12="在册 ",IF(M12<=3000,"0.5%",IF(M12<=5000,"1.0%",IF(M12<=10000,"1.5%","2.0%"))),IF( OR(G12="家属",G12="下岗"),"",IF(G12="离退休",IF(M12<=5000,"0.5%","1.0%")))) 。 对在册党员、家属、下岗和离退休党员均有效。
一、函数名称：IF
主要功能：根据对指定条件的判断的是否成立，
返回条件成立后的执行内容。
使用格式：
=IF(条件表达式, “条件成立时显示或执行的 内容“, “条件不成立时显示或执行的内容")
说明：
1、所有Exce函数公式的等号、括号、逗号均为英文状态下输入。 2、office2003可嵌套7层； office2007嵌套64层。
0.50%
1.00%
在“比例”单元格内输入：
方法1、以单“人员类别”
=IF(G12="在册 ",IF(M12<=3000,"0.5%",IF(M12<=5000,"1.0%",IF(M12<=10000,"1.5%","2.0%")))) 对在册党员有效，对离退休和家属党员无效。
方法2、以双“人员类别”
2）1960年1月1日(含)以后参加工作的职工人数： =COUNTIF(AG$4:AG$373,">=1960/1/1")
注意：
1、单元格区域使用了混合引用（O$4:O$375），即行为绝对引用，列为相对引 用，目的是为了输入方便快捷，因行数较多，拖动选择很费事，通过复制粘贴， 再更改所需列标字母即可。 2、提示：
2、在统计全队职工中，少数民族职工人数：
=countif(表“民族”所在列,“*”)- countif(表“民族”所在列,“汉族 “)
说明：“*”是通配符，在此表示统计“民族”所在列的所有人数，减去 “汉族”人数后，即为少数民族人数，即“排他法”。
7、学历性别民族统计案例
3、以1960年1月1日为时间节点，统计全队职工： 1）1960年1月1日(不含)以前参加工作的职工人数： =COUNTIF(AG$4:AG$373,"<1960/1/1")
公式2：条件升序
=IF(C4<=59,"不称职",IF(C4<=74,"基本称职",IF(C4<=84,"称职","优秀")))
公式3：条件乱序（报错）
=IF(C6<=59,"不称职",IF(C6<=84,"称职",IF(C6<=74,"基本称职","优秀")))
原因：
若“条件”属序列关系，则必须采用升序或降序设置IF函数公式，乱序则出错。 因为函数公式运行规则是：自左到右，即先执行IF(C6<=84,“称职”……），再
学习EXCEL函数公式解决问题的简要方法
1、掌握常用函数的命令、格式、用途、各参数要求（包括函数的优势与不 足）；
2、掌握各常用函数混编组合公式的格式与逻辑关系，尤其是各参数、分隔逗号、括号、绝 对引用、相对引用、混合引用、逻辑或、逻辑且等组合元素的位置与层次关系（技巧： 由最里逐级向外，分清每一函数公式的层次，在该层次中，必须符合该函数的格式要求， 括号必须对应成对，注：用鼠标在编辑栏选中全部公式后，凡对应成对的括号，均为同 一颜色）； 3、在构思函数公式前，必须先明确：要解决什么问题或存在什么问题，有什么已知条件， 再判断需要使用什么函数或函数组合。类似于平面几何证明题：可以从已知条件入手，
$右边的字母（即列）或数字（即行），复制时，其位置是不变的，反之就会改变。
7、关于循环引用：若确实需要循环引用同一单元格或单元区域，则必须 Excel选项中开启迭代运 算选项时，否则不能进行循环引用公式的计算，并自动提示报错。 8、在编辑栏中输入等号和函数英文字母时，电脑会自动在编辑栏下方逐字显示相应函数命令，
2、党费缴费比例及金额自动充填计算案例
说明：
1、如果采用方法1和方法2，则需依据当前人员类别，输入相应的函数 公式。不可将方法1或方法2复制到整个表的“比例”栏，否则报错： 显示为FALSE。 2、方法3可适用整个工作表，可将方法3复制到整个表的“比例”栏。 原因是： 在方法1和方法2的函数公式中，没有包涵所有人员类别。因IF函数在 指定单元格中找不到设定的人员类别参数而报错。而方法3函数公式 的参数设置涵盖了整个工作表，因此更适用。
逐步推进，寻找解决办法；也可从问题入手，逆向分解，寻找规律，解决问题。
特别说明：与平面几何证明题一样，用函数公式解决工作当中的实际问题的方法，也有2 种以上的方法，所不同的是：使用的函数不同、编写的公式长短不同、公式的繁简不同。
你的EXCEL函数水平有多高？
1、菜鸟级 会使用最常简的求和、求平均数函数 SUM 、AVERSE、MAX、MIN 2、新手级 比菜鸟会用更多的函数，会使用IF函数进行简单判断、会对数字进行四舍五入运算。 IF、COUNT、LARDE、ROUND、INT、TODAY 3、入门级 对函数已有初步了解，熟悉最常用函数的使用方法。 IFERROR、VLOOKUP、SUMIF、SUMPRODUCT 4、初级 已能熟练使用更多常用函数的公式设置，但对函数的深入用法不够了解。 MATCH、DATEDIF、SUMIFS、COUNTIFS 5、中级 这部分用户在单位里已属函数高手级别，已能解决复杂的函数问题，但公式优化还需学习。 OFFSET、INDIRECT、LOOKUP、TXTE 6、高级 对Excel函数已达到很高的境界，已能运用数组公式解决复杂的数据计算和处理。 FREQUENGY、MMULT、T/N、SUBSTOTAL 7、大师级 不用说了，这类是函数高手中的高手，没有他写不出的公式，解题思路已成系统。 二分法、三维引用、数组维数转换
IF函数应用实例三
1、1934年9月30日前出生增100元； 2、1934年10月1日—1939年9月30日期间出生增60元
第一列：=IF(D4="",0,IF(D4<=DATE(1934,9,30),100,0)) 第二列：
=IF(D4="",0,IF(AND(D4>DATE(1934,9,30),D4<=DATE(1939,9,30)),60,0))
执行IF(C6<=74,“基本称职“……），而C6<=84就包括了C6<=74，犯了逻辑错
误，因此不显示60～74成绩区间的“基本称职”。
设置IF函数公式注意事项：
1、要使IF函数公式适用于整个工作表，其采用的条件关键词，
必须涵盖整个工作表。 2、嵌套的层次关系要明晰，并符合IF函数公式的格式要求。
=IF(逻辑判断表达式1, "当判断条件成立时显示或执行的内容", IF(逻 辑判断表达式2, "当判断条件成立时显示或执行的内容……, "当判断条 件不成立时显示或执行的内容"))
IF函数应用实例一
进行校对：
通过函数公式对2人及2人以上编辑的相同电子表文档数据进行校 对，并对不正确的数据进行标注。
根据综合考核成绩所在区段，对应显示等级：
序号 1 2 3 4 成绩区间 0～59 60～74 75～84 84～100 等级 不称职 基本称职 称职 优秀 备注
4、综合考核统计案例
公式1：条件降序
=IF(C4>=85,"优秀",IF(C4>=74,"称职",IF(C4>=60,"基本称职","不称职")))
EXCEL常用函数在工作中的应用
闵华荣
2016年1月3日
EXCEL函数概述
Excel 是微软office办公软件的一个重要组成部分，具有强大的计算 统计功能，它可以进行各种数据的处理、统计分析和辅助决策操作， 广泛地应用于管理、统计、财经、金融等众多领域。
Excel中的函数功能，可以执行指定计算，分析信息，尤其是Excel 函数的组合使用，可以使十分复杂的计算、统计、查询、排版和管 理，简便化，起到事半功倍的效果。
供你选择。
EXCEL函数公式一般格式
不同函数公式，有不同的格式要求，必须按格式要求设置公式，尤其要注意函数公式中 的嵌套逻辑关系，否则报错： 1、=IF(条件， "条件成立时执行的内容", "条件不成立时执行的内容")； 2、=IF(AND(OR(G4= "条件1 ",G4= "条件2 "),I4>=30,H4= "条件3 "), "满足条件执行的 内容", "满足条件执行的内容") 3、=sum(需要进行汇总的数据区域)； 4、=COUNTIF(统计区域,“统计条件”)； 5、=LOOKUP(查找条件,查找条件所在区域,满足条件的对象所在区域)； 6、=SUMIFS(汇总数据区域,条件1所在区域,"条件1 ",条件2所在区域,"条件2 ",条件3所在 区域,"条件3 ",……条件n所在区域,"条件n") …………在此不一一例举。 以下介绍函数时，均会介绍函数的用途、格式等。
二列合一： =IF(D39="",0,IF(D39<=DATE(1934,9,30),100,IF(D39="",0,IF(AND( D39>DATE(1934,9,30),D39<=DATE(1939,9,30)),60,0))))
3、根据出生年月日自动判定增加离退休费案例
IF函数应用实例四
根据分数段，显示等级：
=IF(原文档G4=用于校对!G4,"正确","不正确")
即：在原文档相应单元格中输入公式： =IF(原文档中需要进行校对的数据单元格=另一文档中的该数据单元 格，“若相等显示：正确”, “若不相等显示：不正确") 适用于表间单元格内容校对
1、进行校对案例
IF函数应用实例二
根据指定类别，显示对应内容：
技巧是：
1、以第一个IF，按函数格式逻辑关系进行审核，然后审核第 二个IF，经此类推。 2、用鼠标在编辑状态栏内选中整个公式（同一级成对的括号为 同色），由内向外以左、右括号成对判定其位置是否正确。
5、IF函数嵌套逻辑关系图
二、函数名称：COUNT、COUNTA、COUNTIF
主要功能：COUNT统计某个单元格区域中数字单元格数据
根据党员类别和工资收入，自动选择并显示对应缴费比例或金额。 党费收缴标准如下： 党费收缴标准
党员类别 工资收入 缴费比例或金额
家属、下岗
无工资收入
0.2～1.00元
≤3000
0.50%
＞3000，≤5000 在岗 ＞5000，≤10000
1.00%
1.50%
＞10000
2.00%
≤5000 离退休 ＞5000
适用范围：
需要使用统计区域的数字单元格个数、非空单元格个数、或符合给定条件 的单元格个数参与计算的。 6、用COUNT、 COUNTA、 COUNTIF函数分别统计案例
COUNTIF函数应用实例一
根据给定条件，在指定单元格区域内统计符合条件的数量：
1、在统计全队职工中，女职工人数： =countif(表“性别”所在列,"女")
COUNTA统计某个单元格区域中非空单元格数据 COUNTIF统计某个单元格区域中符合指定条件的单元格数目。
使用格式：
= COUNT （单元格区域，或文本、或数字） = COUNT A（单元格区域，或文本、或数字） 注：以上2函数：若括号内有文本、或数字，则和单元格区域一并参与统计） = COUNTIF(要统计的单元格区域, "给定条件")
EXCEL函数公式编写规则
1、所有的公式必须在英文状态下输入，并以等号开头；
2、函数名，如IF，均写在等号的后面再加一个括号，如 =IF(A2=B2,”正确”,”不正确”)； 3、公式的函数名和单元格的列号，都不区分大小写； 4、在Excel的公式嵌套中，只使用小括号。嵌套时，新嵌套进去的公式，也要把函数名写在最前 面、后加括号； 5、当公式引用的单元格发生变化时，公式会自动重新计算结果，并显示在输入公式的单元格内； 6、注意绝对引用（$A$5）、相对引用（A5）及混合引用（$A5与A$5）的作用：凡有$符号的，
COUNTIF函数中的日期格式为：1960/1/1。 IF函数中的日期格式为：DATE(1960,1,1)
=IF(OR(G38=“家属”,G38=“下岗”),“”,IF(G38=“离退休”,IF(M38<=5000,“0.5%”,“1.0%”))) 对家属、下岗和离退休党员有效，对在册党员无效。
方法3、以多“人员类别”
=IF(G12="在册 ",IF(M12<=3000,"0.5%",IF(M12<=5000,"1.0%",IF(M12<=10000,"1.5%","2.0%"))),IF( OR(G12="家属",G12="下岗"),"",IF(G12="离退休",IF(M12<=5000,"0.5%","1.0%")))) 。 对在册党员、家属、下岗和离退休党员均有效。
一、函数名称：IF
主要功能：根据对指定条件的判断的是否成立，
返回条件成立后的执行内容。
使用格式：
=IF(条件表达式, “条件成立时显示或执行的 内容“, “条件不成立时显示或执行的内容")
说明：
1、所有Exce函数公式的等号、括号、逗号均为英文状态下输入。 2、office2003可嵌套7层； office2007嵌套64层。
0.50%
1.00%
在“比例”单元格内输入：
方法1、以单“人员类别”
=IF(G12="在册 ",IF(M12<=3000,"0.5%",IF(M12<=5000,"1.0%",IF(M12<=10000,"1.5%","2.0%")))) 对在册党员有效，对离退休和家属党员无效。
方法2、以双“人员类别”
2）1960年1月1日(含)以后参加工作的职工人数： =COUNTIF(AG$4:AG$373,">=1960/1/1")
注意：
1、单元格区域使用了混合引用（O$4:O$375），即行为绝对引用，列为相对引 用，目的是为了输入方便快捷，因行数较多，拖动选择很费事，通过复制粘贴， 再更改所需列标字母即可。 2、提示：
2、在统计全队职工中，少数民族职工人数：
=countif(表“民族”所在列,“*”)- countif(表“民族”所在列,“汉族 “)
说明：“*”是通配符，在此表示统计“民族”所在列的所有人数，减去 “汉族”人数后，即为少数民族人数，即“排他法”。
7、学历性别民族统计案例
3、以1960年1月1日为时间节点，统计全队职工： 1）1960年1月1日(不含)以前参加工作的职工人数： =COUNTIF(AG$4:AG$373,"<1960/1/1")