文章最后有彩蛋！好礼相送！

Excel秘籍大全，正文开始

一、trunc函数

功能：将数字的小数部分截去（不进行舍入或四舍五入）。

结构：=TRUNC(数值,小数位数)

二、int函数

功能：将数字向下舍入到最接近的整数。

结构：=INT(数值)

三、fixed函数

功能：将数字按照指定的小数位数进行四舍五入。

结构：=FIXED(数值,小数位数,有无逗号分隔)

说明：1、结果以文本形式返回，所以不能直接用sum函数求和；

2、第三个参数若是TRUE或省略，返回结果不包含逗号，如11100.36

3、第三个参数若是FALSE，返回结果包含逗号，如11,100.4

四、round函数

功能：将数字按照指定的小数位数进行四舍五入。

结构：=ROUND(数值,小数位数)

说明：1、若“小数位数”大于0，则四舍五入到指定的小数位；

2、若“小数位数”等于0，则四舍五入到最接近的整数，如=ROUND(10.56,0)返回结果11；

3、若“小数位数”小于0，则在小数点左侧进行四舍五入，如=ROUND(15.56,-1)返回结果20。

五、exact函数

功能：比较两字符串是否相同(区分大小写)，相同则返回ture，否则返回false。

结构：=EXACT(字符串,字符串)

目的：核对库存数跟盘点数

六、datedif函数

功能：计算两个日期间的年数、月数、天数。

结构：=DATEDIF（开始日期，结束日期，返回类型）

“返回类型”分以下6种情况：

1、"Y":计算两个日期间隔的年数；

2、"M":计算两个日期间隔的月份数；

3、"D":计算两个日期间隔的天数；

4、"MD":忽略年和月，计算间隔天数；

5、"YM":忽略年和日，计算间隔月份数；

6、"YD":忽略年，按照月、日，计算天数。

需要注意的是，在“返回类型”若是手动输入，则输入英文字符双引号，否则会出现#NAME?错误值。

目的：计算员工的工作年限

七、if函数

功能：判断一个条件是否满足，如果满足返回一个值，不满足则返回另一个值。

结构：=IF（测试条件，真值，假值）

说明：1、返回的真值和假值若是文字，需要在英文状态下的双引号中，例如=IF(C2>=80,"合格","不合格")；

2、测试条件若是文字，需要在英文状态下的双引号中，例如=IF(C1="一班",200,0)；

目的：求员工的全勤奖（满22天，奖励200元）

八、countifs函数

功能：计算区域中满足多个指定条件的单元格个数。

结构：=COUNTIFS（区域1，条件1，区域2，条件2...）

目的：统计各部门人数

九、sumif函数

功能：对区域中满足单个条件的单元格求和。

结构：=SUMIF（区域，条件，求和区域）

说明：1、求和区域中的数字若为文本格式，将产生错误；

2、若条件区域或条件单元格含有空字符串，将产生错误；

3、如果忽略了 求和区域，则对区域中的单元格求和。

目的：对入库的数量进行汇总

十、sum函数

功能：返回某一单元格区域中所有数字之和。

结构：=SUM（数值1，数值2...）

目的：累计求和

十一、vlookup函数

功能：在表格的首列查找指定的数值，并返回表格当前行中指定列处的数值

结构：=VLOOKUP(查找值,数据表,列序数,匹配条件)

说明：1、当查找值在数据表中不位于首列，计算结果返回#N/A；

2、当查找值为数字，且为文本格式，计算结果返回#N/A；

3、当查找值含有空字符串，计算结果返回#N/A；

4、当数据表中含有隐藏的列，可能导致列序数出错；

5、若要复制公式，需对数据表进行绝对引用，计算结果部分返回#N/A；

6、当查找值不存在，计算结果返回#N/A

目的：根据工号返回底薪

十二、dollar函数

功能：将数字按照指定的小数位数进行四舍五入，转换成货币格式。如$10.00

结构：=DOLLAR(数值,小数位数)

说明：1、第二个参数若是省略，则假设为2；

2、返回的结果是以文本格式形式存在，不能直接用sum函数等计算，若转换，需加两短线，如=SUM(--C93:C94)，按ctrl+shift+回车键结束。

十三、rmb函数

功能：将数字按照指定的小数位数进行四舍五入，转换成货币格式。如￥10.00

结构：=RMB(数值，小数位数)

说明：1、第二个参数若是省略，则假设为2；

1、返回的结果也是以文本格式形式存在，不能直接用sum函数求和。

十四、sumproduct函数

功能：将数组间对应的元素相乘，并返回乘积之和。

结构：=SUMPRODUCT（数组1，数组2）

目的：根据数量、单价，求总金额(记得设置格式，单击右键，选中设置单元格格式，点击数字中的货币)

十五、edate函数

功能：返回指定日期 之前或之后指示的月份数的日期。

结构：=EDATE（开始日期，月数N）

说明：1、"开始日期"若是手动输入，需要添加双引号，如"2022/1/8"；

2、若月数N＞0则返回未来的日期，比如返回2个月后的日期，则输入2；

3、若月数N＜0则返回过去的日期，比如返回3个月前的日期，则输入－3；

十六、iferror函数

功能：如果公式计算结果错误, 则返回您指定的值;否则, 它将返回公式的结果。

结构：=IFERROR(值,自定义的值)

当运用vlookup函数来查找数值时，会出现错误值#N/A，这时候可以用iferror函数将其返回空值，如=IFERROR(VLOOKUP(F3,$A$1:$D$5,4,FALSE),"")

十七、eomonth函数

功能：返回某日期指定月数之前或之后某个月最后一天的日期。

结构：=EOMONTH（开始日期，指定开始日期前后的月份）

说明：1、"开始日期"若是手动输入，需要在英文状态下添加双引号，如"2022/1/7"；

2、第二个参数若为正数返回未来的日期，比如返回当月的最后一天，则输入0，返回n个月后的最后一天，则输入n；

3、第二个参数若为负数返回过去的日期，如果想返回上一个月的最后一天，则输入-1，返回n个月前的最后一天，则输入－n。

十八、mod函数

功能：返回两数相除的余数，结果的正负号与除数相同

结构：=MOD(数值,除数)

经常与IF函数组合起来使用，比如说求性别 。身份证号第17位数若为偶数，代表性别为女，否则为男，如图

十九、text函数

功能：把数值格式转变成想要的文本。

结构：=TEXT（值，格式）

二十、small函数

功能：返回列表区域中第K个最小值。

结构：=SMALL（参数区域，K）

说明：1、忽略逻辑值和文本字符串；

2、如果“参考区域”为空，则返回错误值 #NUM!；

3、如果 第二个参数k ≤ 0 或 k 超过了数据点个数，则 返回错误值 #NUM!；

4、如果某一组数据A1:A6中有n个数，SMALL(A1:A6,n)则求的是最大值;

5、参数区域中的数字不能是文本格式，否则被忽略掉，会导致计算结果出错。

二十一、large函数

功能：返回数据集中第K个最大值函数。

结构：=LARGE（数组，K）

二十二、year函数

功能：返回日期中的年份。

二十三、month函数

功能：返回日期中的月份。

二十四、day函数

功能：返回日期中的天数。

二十五、now函数

功能：返回日期时间格式的当前日期和时间。

二十六、today函数

功能：返回日期格式的当前日期。

二十七、row函数

功能：返回指定单元格的行号（位于第几行）。

结构：=ROW（参照区域）

说明：1、 如果省略 参照区域，则求的是当前单元格所在的行号

2、参照区域是一个单元格区域，不能引用多个单元格区域，否则弹出阻拦窗口；

二十八、column函数

功能：返回指定单元格的列号（位于第几列）。

结构：=COLUMN（参照区域）

说明：1、 如果省略 参照区域，则求的是当前单元格所在的列号；

2、参照区域是一个单元格区域，不能是多个单元格区域；

3、此函数要与columns函数进行区分，后者返回某区域的列数（共有几列）

常与vlookup函数组合使用，例如制作工资条

二十九、mid函数

功能：从指定的位置开始，返回指定字符个数的字符串。

结构：=MID(字符串，开始位置，字符个数)

说明：1、一个空格代表一个字符，所以字符串不能含有空格，否则将产生错误；

2、开始位置是文本中要提取的第一个字符的位置，由左往右开始算起，第1个字符位置为1，第2个字符位置为2，以此类推；

3、字符个数指的是想要返回的字符串个数，若要提取8个字符，就输入8；

4、提取出的数字为文本格式，不能用于直接用于sum函数等，需在前面加-- ，例如=--MID(C2,7,8)

2024-04-21 16:53·Excel秘籍大全

文章最后有彩蛋！好礼相送！

熟悉Excel的小伙伴们都知道，IF函数是Excel中最基础的条件判断函数，其用法也很简单：=IF(判断条件，真值，[假值])，第一参数判断条件可以是数值或表达式，真值TRUE为满足判断条件时返回的值，假值FALSE为不满足判断条件时返回的值，第三参数可省略。IF函数除了应用于基础的条件判断外，还有很多高级用法，很多小伙伴觉得IF函数很简单，看了以下用法，你还这样觉得吗？

1.IF函数基础用法

先来让大家回顾一下IF函数的基础用法，如图1所示，工龄大于等于3年，可享受薪资的5%作为提成，现在要求取满足条件的每名员工的提成金额，在G2单元格输入公式：=IF(D2>=3,F2*$I$2,"无提成")，并向下填充。

图1

2.逆向查询

VLOOKUP函数是目前职场应用频率最高的查询函数，但它仍然有不少不足之处，比如说，它本身并不支持逆向查询，需要搭配IF函数才可以。如图2所示，根据薪资查询对应的员工姓名，其公式为：=VLOOKUP(H2,IF({1,0},F1:F8,A1:A8),2,0)。

IF函数的第一个参数为数值{1,0}，当第一个参数为1时，它表示真值，返回IF函数的第二个参数F1:F8构建的数据区域，当第一个参数为0时，它表示假值，返回IF函数的第三个参数A1:A8构建的数据区域，将0和1返回的结果结合起来，IF函数会构建成F1:F8&A1:A8联合起来的两列数据区域，A1:A8为姓名所在的第二列数据区域，对应VLOOKUP函数的第三参数2。IF函数的作用就是将原本逆向的数据区域让其顺向排列，进而搭配VLOOKUP函数进行数据查询。

图2

3.多条件判断——并关系

如图3所示，当工龄大于等于3年，年龄大于等于30，两个条件都满足时可获得其薪资的5%作为提成，要计算满足两个条件的员工的提成金额，在G2单元格输入公式：=IF(AND($B$2:$B$8>=30,$D$2:$D$8>=3),F2*$J$2,"无提成")，并向下填充，也可以写成：=IF(($B$2:$B$8>=30)*($D$2:$D$8>=3),F2*$J$2,"无提成")。

AND是Excel中的逻辑值函数，当每个参数判断条件都成立时AND函数返回TRUE时，AND($B$2:$B$8>=30,$D$2:$D$8>=3)和($B$2:$B$8>=30)*($D$2:$D$8>=3)意义一样，表示“并”的关系，即要求两个条件都满足。$B$2:$B$8>=30返回逻辑值组成的数组{FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE}，$D$2:$D$8>=3返回数组{FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE}，TRUE为满足条件返回的值，FALSE为不满足条件返回的值，在Excel四则运算中，逻辑值TRUE可以视为1，FALSE视为0，当两个数组相乘时，返回数组{0;1;1;0;0;0;1}，1表示TRUE*TRUE即1*1，即两个条件都满足，当数组{0;1;1;0;0;0;1}为1时，返回IF函数的第二参数，为0返回IF第三参数。

图3

4.多条件判断——或关系

如图4所示，当满足工龄大于等于3或年龄大于等于30任意一个条件时，获得提成金额，要求满足条件的员工的提成金额，在G2单元格输入公式：=IF(($B$2:$B$8>=30)+($D$2:$D$8>=3),F2*$J$2,"无提成")，也可写成=IF(OR($B$2:$B$8>=30,$D$2:$D$8>=3),F2*$J$2,"无提成")。

逻辑值函数OR表示当其参数其中有一个返回TRUE，OR函数返回TRUE，($B$2:$B$8>=30)+($D$2:$D$8>=3)和OR($B$2:$B$8>=30,$D$2:$D$8>=3)意义一样，都表示“或”的关系。当逻辑值相加时，只有其中有一个是TRUE，那么结果就为TRUE。($B$2:$B$8>=30)+($D$2:$D$8>=3)返回数组{0;1;1;0;1;0;2}，当为1或2时表示真值返回IF第二参数，为0表示假值返回IF函数第三参数。

图4

5.判断数据是否存在重复

如图5所示，判断姓名是否重复，其公式为：=IF(COUNTIF($A$2:$A$10,A8)>1,"是","否")。

用COUNTIF函数计数，如果有重复就会返回大于1的数值，IF函数的第一个参数如果是大于0的数值，那么它表示真值就会返回第二参数，如果为0即为假值会返回第三参数。

图5

6.忽略错误值和文本求和

如图6所示，忽略文本和错误值统计总销量，其公式为：=SUM(IF(ISNUMBER(C2:C10),C2:C10,0))，并同时按下Ctrl+Shift+Enter。

图6

先用逻辑值函数ISNUMBER对数据区域C2:C10进行判断，当为数值时返回TRUE，否则返回FALSE，ISNUMBER(C2:C10)返回的结果为数组{TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE}，IF函数第二参数返回数组{121;"未统计";132;132;#VALUE!;211;166;#NAME?;138}，第一参数数组中对应的元素TRUE会返回第二参数的数组对应的元素，IF函数最终的值为{121;0;132;132;0;211;166;0;138}，再用SUM函数对这个数组进行求和。

7.多条件求和

如图7所示，统计薪资7500以上的男性员工的总工资，其公式为：=SUM(IF((C2:C8=H2)*(F2:F8>7500),F2:F8,0))，并同时按下Ctrl+Shift+Enter。

图7

(C2:C8=H2)*(F2:F8>7500)为逻辑值组成的数组相乘，结果为{0;0;1;0;0;0;1}，为1时返回IF函数的第二参数，为0时返回IF的第三参数，最终IF函数返回的结果为数组{0;0;8000;0;0;0;8500}，再用SUM函数对这个数组进行求和。

8.对不规则的重复值排序

如图8所示，对部门进行排序，其公式为：=IF(B2<>B1,1,A1+1)，对相邻的两个上下单元格进行判断，等不等于的时候，返回第二参数1，当等于时，返回第三参数，第三参数值为上个序号+1。

图8

9.IF+ISERROR处理错误值

IF+ISERROR处理错误值处理错误值比较常见 如图9所示，用VLOOKUP函数查找“小包”对应的线上销量，“小包”并不存在于数据区域中，也就是查找值不存在，当查找值不存在时VLOOKUP会返回#N/A错误值，这个时候就需要借助IF+ISERROR来将错误值转化为空值或者自行设定的文本，公式为：=IF(ISERROR(VLOOKUP(E2,A1:C10,3,FALSE)),"查找到此人",VLOOKUP(E2,A1:C10,3,FALSE))或者写：=IF(ISERROR(VLOOKUP(E2,A1:C10,3,FALSE)),"查找到此人",VLOOKUP(E2,A1:C10,3,FALSE))。

图9

10.IF多判断嵌套

IF多判断进行嵌套也是比较常见的情况，新版的EXCEL或者WPS支持IFS函数来代替IF函数的多条件嵌套，这里还是来给大家介绍原始的IF函数嵌套。如图10所示，判断成绩等级，其公式为：=IF(D2>=90,"优秀",IF(D2>=80,"良好",IF(D2>=60,"及格","不及格")))，也可以用IFS函数，其公式为：=IFS(D2>=90,"优秀",D2>=80,"良好",D2>=60,"及格",D2<60,"不及格")。

图10

以下《900套高逼格工作模板.xls 》免费下载，不收一分钱！

常用Excel

用Excel玩好报表

是必不可缺的技能

要知道一张好的图表

可以做到一图胜千言！

今天推荐的超实用干货是

《900套高逼格工作模板.xls 》

3.2G高逼格Excel可视化模板

制作精美 可直接套用

适合自用和内部培训使用

领取方式

关注我们

私信发送关键字：900

即可免费领取

资料来源于网络，公益分享，如有侵权，联系删除