excel常用函数
一.逻辑函数
1.AND函数
AND函数用于检查一组条件是否全部为真。如果所有条件都为真,则返回TRUE;否则返回FALSE。
例子:假设你正在评估应聘者的简历,你的筛选条件是:申请人必须是本科以上学历,且有2年以上工作经验。你可以使用以下公式来筛选符合条件的候选人:
=AND(B2=“本科”, C2>=2)
2.OR函数
OR函数与AND函数类似,但是它只要求一组条件中有至少一个条件为真。如果其中任何一个条件为真,则返回TRUE;否则返回FALSE。
例子:假设你正在筛选销售额达到50万以上或客户超过100个的业务员。你可以使用以下公式来筛选符合条件的业务员:
=OR(B2>=500000, C2>100)
3.IF函数
IF函数用于根据条件的真假返回不同的结果。如果条件为真,则返回一个结果;如果条件为假,则返回另一个结果。
例子:假设你要查看每个员工的奖金。如果销售额高于100万,则奖金为5%;否则奖金为2%。你可以使用以下公式来计算奖金:
=IF(B2>1000000, B20.05, B20.02)
4.IFS函数
IFS函数允许你根据多个条件返回不同的结果。IFS函数将逐一检查每个条件,如果条件为真,则返回相应的结果。如果没有条件为真,则返回一个默认结果。
例子:假设你的电子商务网站设定了不同的邮费标准,取决于订单金额的不同。如果订单金额小于等于200元,则收取20元的固定邮费。如果订单金额大于200元且小于等于500元,则收取15元的邮费。如果订单金额大于500元,则免费送货。你可以使用以下公式来计算邮费:
=IFS(B2<=200, 20, B2<=500, 15, B2>500, 0)
5.IFERROR函数
IFERROR函数用于处理可能导致错误的公式。当公式的结果是错误值(如#DIV/0!或#N/A)时,该函数将取代它们并返回你指定的值。
例子:假设你要除以一个可能为零的值。你可以使用以下公式来处理:
=IFERROR(B2/C2, “除数不能为零”)
二.统计函数
sum–count函数例子部分数据
1.SUM函数
SUM函数返回指定范围内数值的总和。
例子:计算所有手机的总销量:
=SUM(N:N)
2.SUMIF(S)函数
SUMIF(S)函数用于对指定条件下符合条件的数值进行求和。
例子:求2019年的总销量。
=SUMIF(B:B,2019,O:O)
求2019年小米手机的销量
=SUMIFS(O:O,B:B,2019,G:G,“小米”)
3.SUMPRODUCT函数
SUMPRODUCT函数用于计算多个数组中对应元素相乘的和。
例子:上面的数据没有销售额这一列 统计对应的销售额
=SUMPRODUCT(M2:M30,N2:N30)
4.COUNT(A)函数
COUNT(A)函数用于计算指定范围内数值的数量,不论值为何种类型。
例子:求数据中所有的订单数
=COUNTA(G:G)-1
5.COUNTIF(S)函数
COUNTIF(S)函数用于对指定条件下符合条件的数值进行计数。
例子:求数据中浙江省的订单数
=COUNTIF(E:E,E2)
6.MAX(IFS)函数
MAX(IFS)函数用于返回一组数值中的最大值。
例子:假设你需要找到销售团队中最高的业绩,并列出该员工的姓名和业绩金额。你可以使用以下公式来查找最高的业绩:
=MAXIFS(C2:C20,B2:B20,“<>N/A”)
7.MIN(IFS)函数
MIN(IFS)函数用于返回一组数值中的最小值。
例子:假设你需要找到销售团队中最低的业绩,并列出该员工的姓名和业绩金额。你可以使用以下公式来查找最低的业绩:
=MINIFS(C2:C20,B2:B20,“<>N/A”)
8.AVERAGE(A)函数
AVERAGE(A)函数用于计算指定范围内数值的平均值。
例子:假设你需要计算销售团队的平均月销售额。你可以使用以下公式来计算平均月销售额:
=AVERAGE(C2:C20)
9.AVERAGEIF(S)函数
AVERAGEIF(S)函数用于对指定条件下符合条件的数值进行求平均数。
例子:假设你要计算某一销售年度中特定销售团队的平均月销售额。你可以使用以下公式来计算平均月销售额:
=AVERAGEIF(B2:B20,“Sales Team 1”,C2:C20)
10.RANK函数
RANK函数用于返回一组数据中某个数值的排名。
例子:假设你需要找到某个销售人员的排名。你可以使用以下公式来查找该销售人员的排名:
=RANK(C2,C2:C20)
11.RAND函数
RAND函数用于生成一个介于0和1之间的随机数。
例子:假设你需要在电子表格中插入一个随机数,你可以使用以下公式来生成随机数:
=RAND()
12.RANDBETWEEN函数用于生成指定范围内的随机整数。
例子:假设你需要生成一个介于1和10之间的随机整数。你可以使用以下公式来生成随机整数:
=RANDBETWEEN(1,10)
13.ROUND函数
ROUND函数用于将数值四舍五入到指定的位数。
例子:假设你需要将某个数值四舍五入到小数点后2位。你可以使用以下公式来实现:
=ROUND(A2,2)
14.FLOOR函数
FLOOR函数用于将数值四舍五入到指定的倍数,向下取整。
例子:假设你需要将某个数值向下取整到最接近的50的倍数。你可以使用以下公式来实现:
=FLOOR(A2,50)
15.INT函数
INT函数用于将数值向下取整到最接近的整数。
例子:假设你需要将某个数值向下取整到最接近的整数。你可以使用以下公式来实现:
=INT(A2)
三.文本函数
1.LEFT函数
LEFT函数用于返回文本字符串最左侧的若干个字符。
例子:假设你需要提取一个电子邮件地址中的用户名。电子邮件地址为“example123@example.com”,你可以使用以下公式来提取用户名:
=LEFT(A2,FIND(“@”,A2)-1)
2.RIGHT函数
RIGHT函数用于返回文本字符串最右侧的若干个字符。
例子:假设你需要提取一个字符串中的最后一个单词。字符串为“hello world,how are you”,你可以使用以下公式来提取最后一个单词:
=RIGHT(A2,LEN(A2)-FIND(“@”,A2))
3.MID函数
MID函数用于返回文本字符串中指定位置和长度的子串。
例子:假设你需要提取一个字符串中的第10至15个字符。字符串为“abcdefghijklmnopqr”,你可以使用以下公式来提取指定的子串:
=MID(A2,10,6)
4.FIND函数
FIND函数用于检索字符串中某个子串的位置。
例子:假设你需要查找一个字符串中的“@”符号的位置。字符串为“example123@example.com”,你可以使用以下公式来查找“@”符号的位置:
=FIND(“@”,A2)
5.LEN函数
LEN函数用于返回一个字符串的长度。
例子:假设你需要确定一个字符串的长度。字符串为“hello world”,你可以使用以下公式来返回字符串的长度:
=LEN(A2)
6.LENB函数
LENB函数用于返回一个字符串的长度,以字节为单位。
例子:假设你需要确定一个字符串的字节数。字符串为“你好”,你可以使用以下公式来返回字符串的字节数:
=LENB(A2)
7.REPLACE函数
REPLACE函数用于替换字符串中的一部分文本。
例子:假设你需要将一个字符串的第3个字符替换为另一个字符。字符串为“hello”,你可以使用以下公式进行替换:
=REPLACE(A2,3,1,“a”)
8.SUBSTITUTE函数
SUBSTITUTE函数用于替换文本字符串中的全部实例。
例子:假设你需要将一个字符串中的所有逗号替换为句号。字符串为“1,2,3,4”,你可以使用以下公式进行替换:
=SUBSTITUTE(A2,“,”,“.”)
四.日期函数
1.TODAY函数
TODAY函数用于返回当前日期。
例子:假设你需要记录文档的最后修改日期。你可以使用以下公式来自动提取当天的日期:
=TODAY()
2.DATE函数
DATE函数用于创建指定年、月、日的日期。
例子:假设你需要计算某个事件距离期限还有多少天。期限是2022年6月30日,你可以使用以下公式来计算距离期限的天数:
=DATE(2022,6,30)-TODAY()
3.DATEDIF函数
DATEDIF函数用于计算指定两个日期之间的时间间隔,可以精确到年、月、日等。
例子:假设你需要计算任务开始日期和结束日期之间的天数。任务开始日期是2022年1月1日,结束日期是2023年1月1日,你可以使用以下公式来计算天数:
=DATEDIF(DATE(2022,1,1),DATE(2023,1,1),“d”)
4.YEAR函数
YEAR函数用于返回特定日期的年份。
例子:假设你需要提取一个日期的年份。日期是2022年6月16日,你可以使用以下公式来提取年份:
=YEAR(DATE(2022,6,16))
5.MONTH函数
MONTH函数用于返回特定日期的月份。
例子:假设你需要提取一个日期的月份。日期是2022年6月16日,你可以使用以下公式来提取月份:
=MONTH(DATE(2022,6,16))
6.DAY函数
DAY函数用于返回特定日期的日份。
例子:假设你需要提取一个日期的日份。日期是2022年6月16日,你可以使用以下公式来提取日份:
=DAY(DATE(2022,6,16))
7.WEEKDAY函数
WEEKDAY函数用于返回特定日期的星期几,其中1表示星期日,7表示星期六。
例子:假设你需要知道某一日期是星期几。日期是2022年6月16日,你可以使用以下公式来查找星期几:
=WEEKDAY(DATE(2022,6,16))
8.WEEKNUM函数
WEEKNUM函数用于返回特定日期所在的周数。
例子:假设你需要知道某一日期是属于哪一周。日期是2022年6月16日,你可以使用以下公式来查找所在周数:
日期的日份。日期是2022年6月16日,你可以使用以下公式来提取日份:
=DAY(DATE(2022,6,16))
7.WEEKDAY函数
WEEKDAY函数用于返回特定日期的星期几,其中1表示星期日,7表示星期六。
例子:假设你需要知道某一日期是星期几。日期是2022年6月16日,你可以使用以下公式来查找星期几:
=WEEKDAY(DATE(2022,6,16))
8.WEEKNUM函数
WEEKNUM函数用于返回特定日期所在的周数。
例子:假设你需要知道某一日期是属于哪一周。日期是2022年6月16日,你可以使用以下公式来查找所在周数:
=WEEKNUM(DATE(2022,6,16))