EXCEL常用公式函数实战技巧与应用入门

EXCEL常用公式函数实战技巧与应用入门2026/5/162目录常用操作技巧引用切换公式与成果切换手动重算和F9设置计算精度设置底纹隐藏与保护序列数据填充数据有效性常用函数实践数学函数IF与奖金核算多条件求和与业绩记录舍入取整记录单元格数量查找和排名日期函数与在库天数、员工工龄计算查找反复值2026/5/163学习Excel的必要性、心态及措施普及性效率性通用性积极心态循序渐进善用资源学以致用愿你从一种新手成长成为一种高手。2026/5/164EXCEL技能的层次新手：刚刚开始接触初级：基本掌握常用功能：录入、排序、筛选等中级：纯熟使用常用功能+20个以上函数+数据透视表+简朴的宏等高级：……+数组公式+VBA编程专家：EXCEL技能+专业知识+行业经验从新手到专家2026/5/165公式中的算术运算符2026/5/166公式中的比较运算符2026/5/167公式中的文本运算符连字符＆=CONCATENATE（参数1，参数2）2026/5/168公式中运算符优先级次序2026/5/169常用操作技巧单元格引用相对引用（默认状态）绝对引用混合引用切换技巧：F42026/5/1610常用操作技巧单元格公式与成果切换：菜单方式：工具--选项--视图--公式（勾选）快捷键：Ctrl+~2026/5/1611常用操作技巧自动重算和手动重算默认自动，但数据量大时运行速度慢切换方式：工具—选项—重新计算—（勾选）手动计算重算活动工作表：Shift+F9编辑栏内选中公式切换成果：F9所有工作薄：Shift+Ctrl+F92026/5/1612常用操作技巧：设置计算精度：菜单方式：工具—选项—重新计算—（勾选）以显示精度为准函数方式：RONUD(数据，位数）2026/5/1613常用操作技巧设置不一样底纹格式—条件格式—公式—（进行设置）奇偶行不一样底纹：=MOD(ROW(),2)=1奇偶列不一样底纹：=MOD(COLUMN(),2)=1根据部门名称设置不一样间隔底纹：=MOD(SUM(--($D$2:$D2<>$D$1:$D1)),2)=1=MOD(SUM(--($D$2:$D2<>$D$1:$D1)),2)=0=MOD(COUNTA($A$2:$A2),2)=12026/5/1614常用操作技巧隐藏数据：1、单元格格式-数字-分类-自定义-类型-输入三个英文半角状态下的分号2、切换到保护，勾选隐藏和锁定，退出3、工具-保护-保护工具表，设定密码2026/5/1615常用操作技巧隐藏公式：1、单元格格式，“保护”清除“锁定”，返回2、“编辑”-定位-定位条件-选择—公式，返回3、单元格格式，保护—（勾选）隐藏和锁定4、工具—保护工作表，设置密码2026/5/1616常用操作技巧数据保护权限：查看和编辑、部分和所有、工作表和簿与否可见、可改：单元格和工作表保护2026/5/1617常用操作技巧数据填充左键（范本,智能标识）右键（菜单）录入范本，左键拖动填充录入基准，1，左键复制，Ctrl，放开左键录入基准1，左键填充，显示智能标识选中区域-编辑-填充-序列设定基准，1，右键-自动填充选项-序列2026/5/1618常用操作技巧复写纸功能：同步在多张工作表上编辑数据Ctrl,Shift编辑—填充—至同组工作表2026/5/1619常用操作技巧隔一行插一空行：插入辅助列—先用内容加行号123……--之后1.1,2.1,3.1,---排序升序—删除辅助列2026/5/1620常用操作技巧数据有效性与二级下拉菜单：1、数据有效性与下拉菜单2、序列引用名称实现跨工作表3、建立名称区域，序列=indirect(单元格）实现二级下拉菜单单元格：上级菜单中的一种，相对引用2026/5/1621常用操作技巧----常用快捷键选中活动单元格周围的目前区域：Ctrl+SHIFT+8选定整个工作表：CTRL+A单元格内换行：Alt+回车键每次回车在原单元格：Ctrl+回车键迅速录入相似文本:Ctrl+DCtrl+R同步多选单元格或工作表：Ctrl,Shift2026/5/1622常用操作技巧----常用快捷键显示“单元格格式”对话框：Ctrl+1隐藏行：Ctrl+9取消隐藏行：Ctrl+Shift+9隐藏列：Ctrl+0（零）取消隐藏列：Ctrl+Shift+0插入新工作表：Shift+F112026/5/1623常用操作技巧----常用快捷键插入新工作表：Shift+F11定义名称：Ctrl+F3插入时间ctrl+shift+:

输入日期ctrl+;2026/5/1624单元格函数错误提醒：#####:列不够宽或包括一种无效的时间#VALUE:参数类型错误#REF!:单元格引用无效#NAME?:不能识别公式中的文本，名称拼写错误#NUM!:公式中使用了无效数值#N/A:数值对公式或函数不可用。2026/5/1625什么是函数？Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。以常用的求和函数SUM为例，它的语法是“SUM(number1,number2,)”。其中“SUM”称为函数名称，一种函数只有唯一的一种名称，它决定了函数的功能和用途。函数名称后紧跟左括号，接着是用逗号分隔的称为参数的内容，最终用一种右括号表达函数结束。2026/5/1626函数的参数常量逻辑值数组错误值单元格引用嵌套函数名称和标识2026/5/1627函数的参数常量是直接输入到单元格或公式中的数字或文本，或由名称所代表的数字或文本值，例如数字“2890.56”、日期“-8-19”和文本“黎明”都是常量。不过公式或由公式计算出的成果都不是常量，由于只要公式的参数发生了变化，它自身或计算出来的成果就会发生变化。常量2026/5/1628函数的参数逻辑值是比较特殊的一类参数，它只有TRUE(真)或FALSE(假)两种类型。例如在公式“=IF(A3=0,"",A2/A3)”中，“A3=0”就是一种可以返回TRUE(真)或FALSE(假)两种成果的参数。当“A3=0”为TRUE(真)时在公式所在单元格中填入“0”，否则在单元格中填入“A2/A3”的计算成果。逻辑值2026/5/1629函数的参数数组用于可产生多种成果，或可以对寄存在行和列中的一组参数进行计算的公式。Excel中有常量和区域两类数组。前者放在“{}”(按下Ctrl+Shift+Enter组合键自动生成)内部，并且内部各列的数值要用逗号“，”隔开，各行的数值要用分号“；”隔开。假如你要表达第1行中的56、78、89和第2行中的90、76、80，就应当建立一种2行3列的常量数组“{56，78，89；90，76，80}。数组2026/5/1630函数的参数区域数组是一种矩形的单元格区域，该区域中的单元格共用一种公式。例如公式“=TREND(B1:B3,A1:A3)”作为数组公式使用时，它所引用的矩形单元格区域“B1:B3,A1:A3”就是一种区域数组。区域数组2026/5/1631函数的参数使用错误值作为参数的重要是信息函数，例如“ERROR.TYPE”函数就是以错误值作为参数。它的语法为“ERROR.TYPE(error_val)”，假如其中的参数是#NUM!，则返回数值“6”。错误值2026/5/1632函数的参数单元格引用是函数中最常见的参数，引用的目的在于标识工作表单元格或单元格区域，并指明公式或函数所使用的数据的位置，便于它们使用工作表各处的数据，或者在多种函数中使用同一种单元格的数据。还可以引用同一工作簿不一样工作表的单元格，甚至引用其他工作簿中的数据。单元格引用2026/5/1633函数的参数函数也可以是嵌套的，即一种函数是另一种函数的参数，例如“=IF(OR(RIGHTB(E2,1)="1"，RIGHTB(E2,1)="3"，RIGHTB(E2,1)="5"，RIGHTB(E2,1)="7"，RIGHTB(E2,1)="9"),"男","女")”。其中公式中的IF函数使用了嵌套的RIGHTB函数，并将后者返回的成果作为IF的逻辑判断根据。嵌套函数2026/5/1634函数的参数为了愈加直观地标识单元格或单元格区域，我们可以给它们赋予一种名称，从而在公式或函数中直接引用。例如“B2:B46”区域寄存着学生的物理成绩，求解平均分的公式一般是“=AVERAGE(B2:B46)”。在给B2:B46区域命名为“物理分数”后来，该公式就可以变为“=AVERAGE(物理分数)”，从而使公式变得愈加直观。名称和标识2026/5/1635常用函数解读与应用----数学函数求和（差）：SUM（参数1，参数30）返回某一单元格区域中数字、逻辑值及数字的文本体现式之和。

2026/5/1636常用函数解读与应用----数学函数求积：=PRODUCT（参数1，……参数30）2026/5/1637常用函数解读与应用----数学函数求商：返回商的整数部分=QUOTIENT（被除数，除数）返回余数=MOD(被除数,除数)2026/5/1638常用函数解读与应用----数学函数舍入与取整函数四舍五入:ROUND按位舍入：ROUNDUP\ROUNDDOWN按倍舍入:CEILING\FLOOR截断舍入：TRUNC截断取整：INT奇偶取整：ODD\EVEN

2026/5/1639常用函数解读与应用----数学函数舍入与取整函数按指定位数四舍五入某个数字=ROUND（数据，保留的位数）按绝对值减小的方向舍入某一数字=ROUNDDOWN(数据，位数）=ROUNDDOWN(3.14159，3)返回3.141按绝对值增大的方向舍入一种数字=ROUNDUP(-3.14159，1)返回-3.22026/5/1640常用函数解读与应用----数学函数舍入与取整函数将数字的小数部分按指定小数截去，返回整数=TRUNC（数据，保留的位数）数据舍入取整=INT（数据）两函数在处理负数时有所不一样：TRUNC(-4.3)返回-4，而INT(-4.3)返回-5。2026/5/1641常用函数解读与应用----数学函数舍入与取整函数按照货币格式将小数四舍五入到指定的位数并转换成文字=DOLLAR(number，decimals)=RMB(数据，位数)（省略参数decimals，则以2计算）指定位数四舍五入后文本形式显示FIXED(数据，位数，逻辑值)2026/5/1642常用函数解读与应用----数学函数2026/5/1643常用函数解读与应用----记录函数求平均值：计算所有参数的算术平均值=AVERAGE(参数1，…，参数30)计算参数清单中数值的平均值（包括文本等）=AVERAGEA（参数1，参数30）掐头去尾求平均值=TRIMMEAN(array，percent)=TRIMMEAN(区域，去掉的数据比例)=TRIMMEAN($B3:$I3,2/8)2026/5/1644常用函数解读与应用----记录函数求最大值=MAX（参数1，参数30）=MAXA（参数1，参数30）求中位值=MEDIAN(number1，number30)求最小值=MIN（参数1，参数30）=MINA（参数1，参数30）2026/5/1645常用函数解读与应用----记录函数MODE求众数用途：返回在某一数组或数据区域中的众数。语法：MODE(number1，number2，...)。33参数：Number1，number2，...是用于众数计算的1到30个参数。实例：假如A1=71、A2=83、A3=71、A4=49、A5=92、A6=88，则公式“=MODE(A1:A6)”返回71。2026/5/1646常用函数解读与应用----记录函数计算单元格个数记录单元格区域中具有数字的单元格个数=COUNT（参数1，参数30）返回参数组中非空值的数目=COUNTA（参数1，参数30）计算某个单元格区域中空白单元格的数目=COUNTBLANK(区域)2026/5/1647常用函数解读与应用----记录函数计算单元格个数计算区域中满足给定条件的单元格的个数=COUNTIF(range，criteria)=COUNTIF(区域，条件)2026/5/1648常用函数解读与应用----记录函数返回某一数据集中的某个最大值=LARGE(array，k)=LARGE(区域，名次)返回数据集中第k个最小值=SMALL(array，k)=SMALL(数据集，名次)2026/5/1649常用函数解读与应用----记录函数返回一种数值在一组数值中的排位=RANK(数值，区域，0或省略)降序=RANK(数值，区域，1或不小于1)升序RANK(要判断的单元格,查找的区域,1为最小数排第一0为最大数排第一)=COUNTIF(A$3:A$12,">"&A3)+1{=SUM(--(A$3:A$12>A3))+1}业绩排名2026/5/1650常用函数解读与应用----文本函数从一种文本字符串的指定位置开始，截取指定数目的字符=MID（文本字符串，起始位置，截取数目）从一种文本字符串的最终一种字符开始,截取指定数目的字符RIGHT（文本字符串，截取数目）提取文本的一部分2026/5/1651常用函数解读与应用----文本函数从一种文本字符串的第一种字符开始,截取指定数目的字符=LEFTB(目的单元格,从内容的左边开始截取指定的字的个数)=LEFT（目的单元格，截取数目）提取文本的一部分：2026/5/1652常用函数解读与应用----文本函数记录文本字符串中字符数目=LEN(目的单元格)检索字符位置(辨别或不辨别大小写)=FIND(要查找的内容加引号或单元格,查找区域(单元格),从几种字开始查找)=SEARCH(要查找的内容加引号或单元格,查找区域(单元格),从几种字开始查找)计算文本的长度：2026/5/1653常用函数解读与应用----文本函数EXACT：检查两文本与否完全相似=EXACT(目的单元格或文本1,目的单元格或文本2)REPT：根据指定次数反复文本REPT(要显示的内容或单元格,规定反复的次数)2026/5/1654常用函数解读与应用----文本函数TEXT(要变化数值或单元格,要显示的方式代码)神奇的TEXT函数2026/5/1655常用函数解读与应用----实战案例性别提取：=IF(A2<>"",IF(MOD(RIGHT(LEFT(A2,17)),2),"男","女"),)年龄计算：=IF(A2<>"",DATEDIF(TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00"),TODAY(),"y"),)身份证解析要用到的函数2026/5/1656常用函数解读与应用----实战案例出生日期提取：IF(A2<>"",TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")+0,)有效性验证{=OR(LEN(A2)=15,IF(LEN(A2)=18,MID("10X98765432",MOD(SUM(MID(A2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(A2)))}身份证解析要用到的函数2026/5/1657常用函数解读与应用----实战案例提取所属省市IF(A5<>"",VLOOKUP(LEFT(A5,2),data,2,)&VLOOKUP(LEFT(A5,4),data,2,),)15位升级18位：=IF(LEN(A3)=15,REPLACE(A3,7,,19)&MID("10X98765432",MOD(SUM(MID(REPLACE(A3,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),A3)身份证解析要用到的函数2026/5/1658常用函数解读与应用----逻辑函数IF:根据条件满足与否返回不一样的值AND:检测所有的条件与否为真OR:检测任意一项条件与否为真NOT:对示条件的参数的逻辑值求反逻辑值：TRUE:表达总是为真FALSE:表达总是为假逻辑函数2026/5/1659常用函数解读与应用----逻辑函数IF逻辑函数的运用:假如……就……否则……=IF(条件体现式，TRUE成果，FALSE的成果）=IF($J$3/$L$3>1.2,500,IF($J$3/$L$3>=1.15,400,IF($J$3/$L$3>=1.1,300,IF($J$3/$L$3>=1.05,200,0))))=CHOOSE(IF($J$3/$L$3>1.2,4,IF($J$3/$L$3>=1.15,3,IF($J$3/$L$3>=1.1,2,IF($J$3/$L$3>=1.05,1,5)))),200,300,400,500,0)2026/5/1660常用函数解读与应用----数学函数条件求和根据指定条件对若干单元格、区域或引用求和=SUMIF(条件区域，条件，求和区域）=SUMIF(B1:B1000，"中级"，F1:F1000)2026/5/1661常用函数解读与应用----数学函数SUBTOTAL(计算方式,要计算的区域)2026/5/1662常用函数解读与应用----实战案例多条件求和:

数组公式①：{=SUM(IF(($Q$91:$Q$120="店销")*($G$91:$G$120=C8),1,0)*($J$91:$J$120))}{=SUM(IF((条件区域=条件）*（条件区域2=条件2）,1,0)*(求和区域))}2026/5/1663常用函数解读与应用----实战案例多条件求和:数组公式②{=SUM(($R$91:$R$120=“分期")*($G$91:$G$120=$C$5)*($J$91:$J$120))}{=SUM(（条件1）*（条件2）*（求和区域）)}2026/5/1664常用函数解读与应用----实战案例多条件求和:函数①：=SUMPRODUCT(($R$91:$R$120="分期")*($G$91:$G$120=$C$5),($J$91:$J$120))=SUMPROCUCT(（条件区域=条件）*（条件区域2=条件2）*(求和区域）)2026/5/1665常用函数解读与应用----实战案例多条件求和:函数②：（）=SUMIFS（求和区域，条件区域1，条件1，条件区域2，条件2）2026/5/1666常用函数解读与应用----实战案例多条件计数:

{=SUM(（条件1）*（条件2）*（条件n）)}=SUMPROCUCT(（条件区域1=条件1）*（条件区域2=条件2）*(条件区域n=条件n）)2026/5/1667常用函数解读与应用----日期与时间函数今天和目前=TODAY()=NOW()怎样快捷输入当日日期？2026/5/1668常用函数解读与应用----日期与时间函数从日期中提取出年或月或日或星期几=YEAR(日期）=MONTH(日期）=DAY(日期）=WEEKDAY(要查的单元格,2)提取时分秒与此雷同2026/5/1669常用函数解读与应用----日期与时间函数将文本方式转换成原则日期：=DATE(年，月，日）从指定的年、月、日来计算日期序列号值.=DATEVALUE(要计算的单元格)从表达日期的文本来计算序列号值=TIME(hour,minute,secod)从时、分、秒来计算出时间的序列号值=TIMEVALUE(时间)从表达时间的文本来计算序列号值2026/5/1670常用函数解读与应用----日期与时间函数计算年龄，库龄，工龄=IF($K$2>DATE(YEAR($K$2),MONTH(D5),DAY(D5)),YEAR($K$2)-YEAR(D5),YEAR($K$2)-YEAR(D5)-1)=DATEDIF(D5,C5,"y")=IF(($K$2-D6)/365>1,INT(($K$2-D6)/365),0)2026/5/1671常用函数解读与应用----日期与时间函数隐秘函数DATEDIF诸多版本里都没有，不能从“插入函数”对话框中输入.用来计算两个日期的年、月、天数的差=DATEDIF(起始日期,结束日期,"y")=DATEDIF(起始日期,结束日期,“m")=DATEDIF(起始日期,结束日期,“d")2026/5/1672常用函数解读与应用----查找与引用函数VLOOKUP（查找条件，总区域，成果列数，0）在表格或数值数组的首列查找指定的数值,并由此返回表格或数组目前行中指定列处的数值VLOOKUP(要查找的内容,搜索的区域,从查找区域首列开始到要找的内容的列数,指定是近似匹配还是精确匹配查找方式)精确查找是vlookup最基本也是最常用的功能,对于数据量大的查找,其速度比菜单中的查找还快.设置vlookup第四个参数为false或0,即为精确查找.2026/5/1673常用函数解读与应用----查找与引用函数=VLOOKUP（查找条件，总区域，成果列数，0）=VLOOKUP（查找值，所在区域，成果所在列数，0）=VLOOKUP（查找条件，搜索区域，列序号，查找方式）=VLOOKUP（指定条件，查找区域，右移几行，0）指定搜查条件，设定搜查区域，向右设定到成果所在的列，设定搜查方式与否精确查找。2026/5/1674常用函数解读与应用----查找与引用函数MATCH(查找条件,查找区域,0)返回在指定方式下与指定数值匹配的数组中元素的对应位置2026/5/1675常用函数解读与应用----查找与引用函数CHOOSE(指定区域的第几种,指定区域1,指定区域2,……)第一种参数是用来指明待选参数序号的值，它必须是1到29之间的数字、或者是包括数字1到29的公式或单元格引用；其他29个参数可以是数字、单元格，已定义的名称、公式、函数或文本。2026/5/1676常用函数解读与应用----查找与引用函数COLUMN（）返回列号ROW()返回行号ADDRESS(第几行,第几列,引用类型,工作表名称)2026/5/1677常用函数解读与应用查找反复值1、防止录入反复数据数据有效性=COUNTIF(A:A,A1)=12、查找区域内反复值①条件格式=COUNTIF(A:A,A1)>1②=IF(COUNTIF(A:A,A1)>=2,"反复","")2026/5/1678常用函数解读与应用查找反复值3、不一样列中找反复数据=ISNUMBER(MATCH($B2,$A$2:$A$18,0))=IF(ISNUMBER(MATCH($B8,$A$2:$A$18,0)),"反复","")2026/5/1679常用函数解读与应用查找反复值4、VLOOKUP函数与否可以查找反复值？请大家自己学习。2026/5/1680常用函数解读与应用5、高级筛选处理反复值A、提取单个表中的不反复值全选-数据-高级筛选-列表区域-选择不反复记录B、提取两个表中的相似数据一种条件区域，一种列表区域2026/5/1681常用函数解读与应用查找不反复值{=SUM(N(MATCH(数据区域，数据区域，0）=ROW(数据行区间）-1））}{=SUM(N(MATCH(B$2：B$10，B$2:B$10，0）=ROW($2：$10）-1））}2026/5/1682常用函数解读与应用VLOOKUP函数向左查询=VLOOKUP(G3,CHOOSE({1,2},$D$3:$D$12,$C$3:$C$12),2,)=VLOOKUP(G3,IF({1,0},$D$3:$D$12,$C$3:$D$12),2,)2026/5/1683常用函数解读与应用计算不反复单元格的个数=SUM(1/COUNTIF($A$1:$A$6,$A$1:$A$6))数组公式{=SUM(1/COUNTIF(区域,区域))}2026/5/1684常用函数解读与应用----注意事项名称的规范不能数字和？开头或以纯数字命名不能以字母R,C;r,c命名不能用空格，可如下划线和点号字母不辨别大小写2026/5/1685常用函数解读与应用----注意事项通配符*表达任何字符？表达任何单个字符~用于解释字符的通配性2026/5/1686常用函数解读与应用文本型数字转换为数值的六个公式：=A1*1=A1/1=A1+0=A1-0=--A1=VALUE(A1)2026/5/1687常用函数解读与应用----实战案例=IF(ABS(A2)<0.005,"",IF(A2<0,"负",)&IF(INT(ABS(A2)),TEXT(INT(ABS(A2)),"[dbnum2]")&"元",)&IF(INT(ABS(A2)*10)-IN