excel 公式大全

excel 公式大全

Excel常用电子表格公式大全
查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,"重复","")
计算年龄公式：=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)
从身份证号计算出生年月：=CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))
自动提取性别：=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))
求和：=SUM(K2:K56)
平均数：=AVERAGE(K2:K56)
排名：=RANK(K2，K$2:K$56)
等级：=IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格")))
学期总评：=K2*0.3+M2*0.3+N2*0.4
最高分：=MAX(K2:K56)
最低分：=MIN(K2:K56)
分数段人数统计：
=COUNTIF(K2:K56,"100")=COUNTIF(K2:K56,">=95")－K57=COUNTIF(K2:K56,">=90")－SUM(K57:K58)=COUNTIF(K2:K56,">=85")－SUM(K57:K59)=COUNTIF(K2:K56,">=70")－SUM(K57:K60)=COUNTIF(K2:K56,">=60")－SUM(K57:K61)=COUNTIF(K2:K56,"<60")优秀率：=SUM(K57:K60)/55*100
及格率：=SUM(K57:K62)/55*100
标准差：=STDEV(K2:K56)
条件求和：=SUMIF(B2:B56,"男"，K2:K56)
多条件求和：｛=SUM(IF(C3:C322="男",IF(G3:G322=1,1,0)))｝
自动计算周岁：=TRUNC((DAYS360(D3,NOW( )))/360,0)
单元格条件格式设置：1. 单元击C1单元格，点“格式”>“条件格式”，条件1设为：公式 =A1=12. 点“格式”>“字体”>“颜色”，点击红色后点“确定”。3. 条件2设为：公式 =AND(A1>0,A1<1)4. 点“格式”>“字体”>“颜色”，点击绿色后点“确定”。5. 条件3设为：公式 =A1<0 点“格式”>“字体”>“颜色”，点击黄色后点“确定”。6. 三个条件设定好后，点“确定”即出。
控制数据长度：1. 选定你要输入的数据范围，点"数据"->"有效性"->"设置"，"有效性条件"设成"允许""文本长度""等于""5"。2. 还可以定义一些提示信息、出错警告信息和是否打开中文输入法等，定义好后点"确定"。3. 选定A列，点"格式"->"条件格式"，将条件设成“公式=COUNTIF($A:$A,$A1)>1”，点"格式"->"字体"->"颜色"，选定红色后点两次"确定"。
标识不同之处：1. A2单元格，点“格式”>“条件格式”，将条件设为: “单元格数值” “不等于”=B2 点“格式”>“字体”>“颜色”，选中红色，点两次“确定”。2. 用格式刷将A2单元格的条件格式向下复制。 B列可参照此方法设置。
按行排序：1. 假定你的数据在A至E列，请在F1单元格输入公式： =LARGE($A1:$E1,COLUMN(A1)) 用填充柄将公式向右向下复制到相应范围。2. 你原有数据将按行从大到小排序出现在F至J列。
多条件计数统计：1. 公式如下： =SUM(IF((B2:B9999="二")*(C2:C9999>=104)*(D2:D9999="重本"),1,0)) 输入完公式后按Ctrl+Shift+Enter键,让它自动加上数组公式符号"{}"。
判断文本：1. 任一公式均可: =IF(COUNTIF(A1,"*"&"指定文本"&"*")=1,"有","无") =IF(ISERROR(FIND("指定文本",A1,1)),"无","有")
求不重复数据个数：1. 一是利用数组公式： =SUM(1/COUNTIF(A1:A100,A1:A100)) 输入完公式后按Ctrl+Shift+Enter键,让它自动加上数组公式符号"{}"。2. 二是利用乘积求和函数： =SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100))
快速整理目录：1. 用宏3.0取出各工作表的名称，方法：Ctrl+F3出现自定义名称对话框，取名为X，在“引用位置”框中输入： =MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,100) 确定。2. 用HYPERLINK函数批量插入连接，方法：在目录工作表（一般为第一个sheet）的A2单元格输入公式： =HYPERLINK("#'"&INDEX(X,ROW())&"'!A1",INDEX(X,ROW())) 将公式向下填充，直到出错为止，目录就生成了。
2024-12-07