Excel公式大全：常用函数与操作技巧指南

Excel表格公式大全

掌握核心的Excel公式能有效提升数据处理效率。本文汇总了常用公式，涵盖查找、统计、计算等多种实用场景。

常用Excel公式汇总

1. 查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,"重复","")。excel函数公式大全sum求和公式

2. 用出生年月计算年龄公式：=TRUNC((DAYS360(H6excel使用技巧大全 电子书,"2009/8/30",FALSE))/360,0)。

3. 从18位身excel数据统计份证号提取出生年月：=CONCATENATE(MID(E2,7,excel数据统计汇总4),"/",MID(E2,11,2),"/",MID(E2,13,2))。

4. 从身份证号码自动提取性别：=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))Excel使用技巧PPT课件。公excel条件格式公式设置规则式中的“C2”代表输入身份证号码的单元格。

5. 求和： =SUM(K2:K56) ——对K2到K56区域进行求和。

6. 求平均数： =AVERAGE(K2:K56) ——对K2到K56区域求平均数。

7. 排名： =RANK(K2,K$2:K$56) ——对55名学生的成绩进行排名。

8. 等级评定： =IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格")))

9. 计算excel数据统计与分析学期总评： =K2*0.3+M2*0.3+N2*0.4 ——假设K、M、N列分别存放“平时总评”、“期中”、“期末”成绩。

10.excel数据统计函数 求最高分： =MAX(K2:K5excel函数固定符号$的用法6excel使用步骤) ——求K2到K56区域的最高分。

11. 求最低分： =MIN(K2:K56) ——求K2到K56区域的最低分。

12. 分数段人数统计：

• =COUNTIF(K2:K56,"100") ——求100分的人数，结果可存放excel使用技巧大全视频教程于K57单元格。
• =COUNTexcel数据统计汇总IF(K2:K56Excel使用技巧PPT课件,">=95")-K57 ——求95～99.5分的人数，结果可存放于excel函数vlookup的使用方法K58单元格。
• =COUNTIF(K2:K56,">=90")-SUM(K57:K58) ——求90～94.5分的人数，结果可存放于excel条件格式怎么用公式设置颜色K59单元格。
• =COUNTIF(K2:K56,">=85")-SUM(K57:excel数据统计K59) ——求85～8excel数据统计与分析9.5分的人数，结果可存放于K60单元格。
• =COUNTIF(K2:K56,">=常用excel函数公式大全70")-SUM(K57:K60) ——求70～84.5分的人数，结果可存放于K61单元格。
• =COUNTIF(K2excel函数公式大全及图解:K56,">=60")-SUM(K57:K61) ——求60～69.5分的人数，结果可存放于K62单excel使用步骤元格。
• =COUNTIF(K2:K56,"<60") ——求60分以下的人数，结果可存放于K63单元格。

COUNTIF函数也可用于统计，excel使用技巧大全例如=COUNTIF(C2:C351,"男")可计算C2:C351区域的男性人数。

13. 计算优秀率： =SUM(K57:K60)/55*100

14. 计算及格率： =SUM(K57:excel函数固定符号$的用法K62)/55*100

15. 计算标准差： =STDEV(K2:K56) ——分析K2到K56区域成绩excel数据统计分析的波动情况，数值越小表示差异越小。

16. 单条件求和excel使用技巧大全 电子书： =Sexcel数据统计怎么用UMIF(B2:B56excel函数怎么用,"男",K2:K56) ——假设B列为性别，K列为分数，此公式可求男生成绩总和。

17. 多条件求和： {=SUM(IF(C3:C322="男",IF(G3:G322=1,1,0)))} ——假设C列为性别，G列为班级代码，此数组公式可求一班的男生人数。输入后需按Ctrl+Shift+Enter组合键。

18. 根据出生excel使用教程详解日期计算周岁：=TRUNC((DAYS360(D3,NOW()))/360,0)。假设D列存放出生日期，E列使用此公式即可得到excel函数公式大全加减乘除周岁。

Excel实用技巧与进阶公式

掌握以下Excel使用技巧与实用公式，能帮助您更高效地处理复杂数据。

一、条件格式设置：根据数值变化自动改变单元格颜excel数据统计怎么用色

例如，希望实现：当A1>1时，C1显示红色；当0

二、数据有效性控制：限制数据长度并避免重复录入

1. 使用数据有效excel数据统计函数性定义数据长度excel数据统计：
选中目标区域excel条件格式怎么弄，点击“数据”->“有效性”->“设置”，在“允许”中选择“文本长度”，在“数据”中选择“等于”，并输入指定长度（如5）。可进一步设置输入信息和出错警告。

2. 使用条件格式避免重复：
选中A列，点击“格式”->“条件格式”，将条件设为“公式=COUNTIF($A:$A,$excel函数公式大全A1)>1excel函数怎么用”，点击“格式”->“字体”->“颜色”，选定excel函数公式大全加减乘除红色后确定。设定后，输入长度不符的数据会收到提示，重复数据则会显示为红色字excel函数公式大全及图解体。

三、数据比对标识：快速找出两列数据的差异

1. 同行数据比对：
假定第一行为表头，单击A2单元格，点击“格式”->“条件格式”，将条件设为：“单元格数值” “不等于” =B2。设置字体颜色为红色，并用格式刷将格式向下复制。B列可参照此方法设置。

2. 整列数据比对（不考虑行对应）：
单击A2单元格，点击“格式”->“条件格式”，将条件设为：excel条件格式设置填充颜色“公式”=COUNTIF($B:$Bexcel函数公式大全表,$A2)=0。设置字体颜色为红色，并用格式刷向下复制。B列设置方法类似（公式为=COUNTIF($A:$A,$B2)=0）。设置后，仅存在于单列的数据会以红色字体标识。

四excel函数公式大全表、按行排序：对每一行数据独立进行排序

若需将大量数据的每一行独立按从大到小排序，由于排序功能限制，可借助公excel使用技巧大全 电子书式实现。假设数据在A至E列，在F1单元格输入公式：

=LARGE($A1:$E1,COLUMN(A1))

使用填充柄将公式向右向下复制到目标范围，原数据将按行从大到小排excel条件格式怎么弄列在F至J列。如excel条件格式怎么设置大于或小于需从小到excel条件格式三个三角形大排序，公式改为：

=SMALL($A1:$E1,COLUMN(A1))

五、多条件计数统计：使用函数组合实现复杂统计

例：统计A列“姓名”、B列“班级”为“二”、C列“语文成绩”excel使用教程详解>=104、D列“录取结果”为“重本”的人数。可使用以下数组公式：

=SUM(IF((B2:B9999="二")*(C2:C9999>=104)*(D2:D9999="重本"),1,0))

输入后按Ctrl+Shift+Enter组合键完成数组公式输入。

六、文本内容判断：检测单元格是否包含特定文本

判断A1单元excel数据统计与分析格是否包含“指定文本”，可使用以下任一公式：

=IF(COUNTIF(A1,"*"&"指定文本"&"*")=1,"有","无")

或

=IF(ISERROR(FIND("指定文本",A1,1)),"无","有")

此外，计算某一区域（如A1:A100）内不重复数据的个数，也有两种方法：
数组公式：=SUM(1/COUNTexcel条件格式怎么设置大于或小于Iexcel函数公式大全及图解F(A1:A100,A1:A100))（按Ctrl+Shift+Enter输入）。
或使用乘积求和函数：=SUMexcel使用教程详解PRODUCT(1/COUNTIF(A1:A100,A1:A100))。

七、快速生成工作表目录

1. 使用宏函数获取所有工作表名称：
按Ctrl+F3打开定义名称对话框，名称输入“X”，引用位置输入：

=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,100)

点击确定。

2. 使用HYPERLINK函数批量创建超链接目录：
在目录工作表的A2单元格输入公式：

=HYPERLINK("#'"&INDEX(X,ROW())&"'!A1",INDEX(X,ROW()))

将公式向下填充直至出现错误，即可生成带有超链接的工作表目录。

希望这份Excel公式与技巧大全能成excel使用方法入门为您日常工作的得力助手。如需查看更多进阶教程或获取配套工具，可前往互知网获取相关资源。

更多优质资源与干货，可关excel数据统计图表注公众号：拾暮笔记