Excel常用公式合集

这是一篇关于Excel常用公式合集的思维导图， Excel是大家每天都会接触到的东西，但对于Excel里面的基本概念和操作，又有多少人能够全部了解呢？看看下图。

常用公式合集

统计函数

vlookup

VLOOKUP(A1,B:C,2,0)

满足多条件计数countifs

COUNTIFS(C:C,A1,D:D,B1)

说明：求满足c列中含有A1，且满足D列中含有B1的个数

eg：A1=1，B1=2 则，同时满足c列中含1，且D列中含2的个数并求和

满足多条件求和sumifs

sumifs(A:A,C:C,B1,D:D,F1)

说明： 多个条件并返回基于这些条件的值 求(同时满足C列中含有B1,且D列中含有F1)的A列的和

eg：A列为1-10，C列为姓名（张三李四王五），D列为日期（1号，2号，3号） 则，满足张三1号对应A列数字的求和

转换类函数

日期函数

某日期提取 2021/12/23

提取日

day(A1)

eg:23

提取周

WEEKNUM(A1,2)

eg:52

提取月

MONTH(A1)

eg:12

提取年

year(A1)

eg:2021

组合年月日

DATE(B2,B3,B1)

eg:2021/12/23

计算某日期天数、月数、年数

计算日

DATEDIF(开始日期,结束日期,"D")

eg:=DATEDIF(A1,A5,"D")

说明：2021/12/24到2021/12/31,间隔7天（不包括24日）

计算月

DATEDIF(开始日期,结束日期,"M")

示例如日

计算年

DATEDIF(开始日期,结束日期,"Y")

示例如日

计算某两个日期间的天数（刨除周六日）

NETWORKDAYS(开始日期,结束日期,1)

eg：=NETWORKDAYS(A1,A5,1)

说明：2021/12/24到2021/12/31,间隔6天（包括24日，但不包括周六日）； 其中1代表周六日，2代表周日周一，可按个人需求更改；

now展示当前时间

now()

today展示当天日期

today()

符号变换

横杠变斜杠

SUBSTITUTE(L144,"-","/")

eg："2021-12-08 19:28"转换"2021/12/08 19:28"

SUBSTITUTE(LEFT(L144,10),"-","/")

eg："2021-12-08 19:28"转换"2021/12/08 "

IF函数

单条件判断返回值

=IF(A1>20,"完成任务","未完成")

说明，如果A1大于20为完成任务，否则为未完成

多重条件判断

=IF(A1="是","现金",IF(A1="否","应收票据"，IF(A1=123,"原材料")))

说明：当A1为是则记为现金，当A1为否则记为应收票据，当A1为123则记为原材料

多区间判断

=IF(A1<60,"不及格",IF(A1<80,"良好","优秀"))

=IF(A1>=80,"优秀",IF(A1>=60,"良好","不及格"))

说明： A1<60，不及格 60<=A1<80，良好 A1>=80，优秀

多条件并列判断

=IF(AND(A1>60,B1<100),"合格","不合格")

=IF(OR(A1>60,B1<100),"合格","不合格")

注： and()表示括号内的多个条件要同时成立--且 or()表示括号内的多个条件任一个成立--或

复杂的多条件判断

=IF(OR(AND(A1>60,B1<100),C1="是"),"合格","不合格")

说明：满足（A1>60且B1<100）或满足C1=是，则合格，否则为不合格

=IF(AND(OR(A1>60,B1<100),C1="是"),"合格","不合格")

说明：满足（A1>60或B1<100）且满足C1=是，则合格，否则为不合格

判断后返回区域

=VLOOKUP(A1,IF(B1=1,C:D,F:G),2,0)

说明：如果B1=1则v值选择范围是C:D，否则v值选择范围为F:G

if、vlookup组合

IF(VLOOKUP(A144,'!B:B,1,0)<>"",1,0)

说明：如果满足（v某个值且该值不为空）则记为1，否则记为0

if、countifs组合

IF(COUNTIFS(mini!AP:AP,AP3)>=5,"点亮","未点亮")

说明：如果满足（特定条件下计数且该数值>=5）则记为点亮，否则记为未点亮

好长示例

=IF(AND(AD9="含餐饮",AG9="是",AH9="含热食",AI9="含预包装",AJ9="含自制饮品"),"A",IF(AND(AD9="含餐饮",AG9="是",AH9="含热食"),"B",IF(AND(AD9="含餐饮",AG9="否"),"C",IF(AND(AD9="不含餐饮",AG9="否"),"D","待补"))))

文本类函数

清除某单元格内所有非打印字符

clean()

判断该单元格内的字符长度

=len(A1)

eg：A1单元格内容为：今天天气真好！！ 说明：包括叹号在内，A1单元格内共有8个字符，即=len(A1)返回值为8

单元格内-字符截取

从左向右截取

=left（A1,3)

说明：从左数取前三个字符 eg：A1=123456，则=left（A1,3)返回值为123

从右向左截取

=right(A1,4)

说明：从右数取前四个字符 eg：A1=123456，则=right（A1,4)返回值为3456

从中间截取

=MID(A1,2,3)

说明:从中间截取，从第二个字符开始从左到右取三个字符 eg：A1=123456，则=MID(A1,2,3)返回值为234

排行

单一排行

RANK(G1,$G$1:$G$20,0)

说明：G1单元格在G1-G20中的排行

满足某条件下的排行

INDEX($D$1:$D$20,MATCH(LARGE($G$1:$G$20,1),$G$1:$G$20,0))

说明：G1-G20单元格中第一大的数字，显示对应D1-D20的值

示例： D1-D20单元格为张三、李四、王五； G1-G20单元格为12、16、8 则：最大数字为16，对应展示的姓名为李四

INDEX($D$1:$D$20,MATCH(LARGE($G$1:$G$20,2),$G$1:$G$20,0))

说明：G1-G20单元格中第二大的数字，显示对应D1-D20的值

同上述示例，第二大数字为12，对应展示的姓名为张三