Excel 中常用电子表格公式的完整列表
1.Excel基本公式
1.查找重复内容公式：=IF((A:A,A2)>1,"重复","")。
2. 使用出生年月计算年龄公式：=TRUNC(((H6,"2009/8/30",FALSE))/360,0)。
3、根据输入的18位身份证号码的出生年月计算公式：=(MID(E2,7,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,"男","女")) 公式中的“C2”代表输入ID号的单元格。
1.求和： =SUM(K2:K56) - 对K2到K56的面积求和；
2、平均：=(K2:K56)——计算区域K2 K56的平均值；
3.排名：=RANK(K2, K$2:K$56) - 对55名学生的成绩进行排名；
4. 成绩：=IF(K2>=85,"优秀",IF(K2>=74,"良好",IF(K2>=60,"及格","不及格")))
5、学期总评价：=K2*0.3+M2*0.3+N2*0.4——假设K列、M列、N列分别存储学生的“平时总评价”、“期中”、“期末”三个成绩;
6.最高分：=MAX(K2:K56) - 求K2到K56区域的最高分（55名学生）；
7. 最低分数：=MIN(K2:K56) - 求K2到K56区域的最低分数（55名学生）；
8. 分数段人数：
(1) =(K2:K56,"100") - 求K2到K56区域内得分为100的人数； 假设结果存储在单元格 K57 中；
(2) =(K2:K56,">=95")-K57 - 求K2到K56区域范围内95到99.5的人数； 假设结果存储在单元格 K58 中；
(3)=(K2:K56,">=90")-SUM(K57:K58) - 求K2到K56区域内得分为90到94.5的人数； 假设结果存储在单元格 K59 中；
(4)=(K2:K56,">=85")-SUM(K57:K59) - 求K2到K56区域内得分为85到89.5的人数； 假设结果存储在单元格 K60 中；
(5)=(K2:K56,">=70")-SUM(K57:K60) - 求K2到K56区域内得分为70到84.5的人数； 假设结果存储在单元格 K61 中；
(6)=(K2:K56,">=60")-SUM(K57:K61) - 求K2到K56区域内得分为60到69.5的人数； 假设结果存储在单元格 K62 中；
(7) =(K2:K56,"
注：该函数还可以计算某个区域内的男孩和女孩的数量。
例如： =(C2:C351,"男") - 求C2到C351区域内男性的数量（共350人）；
9、优良率：=SUM(K57:K60)/55*100
10、通过率：=SUM(K57:K62)/55*100
11、标准差： =STDEV(K2:K56) - 求K2到K56区域（55名学生）的表现波动（值越小，说明班上学生之间的表现差异较小，反之，这意味着阶级分化有两个极点）；
12、条件求和： =SUMIF(B2:B56,"男",K2:K56) - 假设B列存储学生的性别，K列存储学生的分数，则该函数返回的结果意味着找到男孩的性别在课堂里。 成绩总和；
13. 多个条件求和：{=SUM(IF(C3:C322="Male",IF(G3:G322=1,1,0)))} - 假设C列（C3:C322区域）存储学生的性别，G列（G3:G322区域）存储的是学生的班级代码（1,2,3,4,5），那么该函数返回的结果表示一个班级的男生人数； 这是一个数组函数，输入后要按 Ctrl+Shift+Enter 组合键（产生“{...}”）。 “{}”不能手动输入，只能使用组合键生成。
14.根据出生日期自动计算年龄：=TRUNC(((D3,NOW( )))/360,0)
————假设D列存储学生的出生日期，E列输入生成学生年龄的函数。
15.Word中的三个技巧：
① 连续输入三个“~”，得到一条波浪线。
② 连续输入三个“-”，得到一条直线。
连续输入三个“=”即可得到双直线。
1、如何在Excel中的某个单元格满足一定条件时，在另一个单元格中显示特定的颜色。 例如：
当A1>1时，C1显示红色
A1
方法如下：
1. 单击单元格 C1，单击“格式”>“条件格式”，并将条件 1 设置为：
公式=A1=1
2、点击“格式”->“字体”->“颜色”，点击红色，点击“确定”。
条件2设置为：
公式 =AND(A1>0,A1
3.点击“格式”->“字体”->“颜色”，点击绿色，点击“确定”。
条件3设置为：
公式=A1
点击“格式”->“字体”->“颜色”，点击黄色，然后点击“确定”。
4、三个条件设置完毕后，点击“确定”退出。
2、EXCEL中如何控制每列数据的长度，避免重复录入
1. 使用数据有效性来定义数据长度。
用鼠标选择需要输入的数据范围，点击“数据”->“有效性”->“设置”，将“有效性条件”设置为“允许”，“文本长度”等于“5”（具体条件可根据您的需要进行更改）。
还可以定义一些提示信息、错误警告信息以及是否打开中文输入法等，定义完成后点击“确定”。
2. 使用条件格式以避免重复。
选中A列，点击“格式”->“条件格式”，设置条件为“公式=($A:$A,$A1)>1”，点击“格式”->“字体”->“颜色” ”，选择红色并单击“确定”两次。
这样设置后，如果输入的数据长度不正确，就会提示，如果数据重复，字体会变成红色。
3、如何识别EXCEL中B列和A列的差异？
(1) 如果要比较同一行A列和B列的数据：
假设第一行是标题，单击A2单元格，单击“格式”->“条件格式”，将条件设置为：
“单元格值”“不等于”=B2
单击“格式”->“字体”->“颜色”，选择红色，然后单击“确定”两次。
使用格式刷将单元格 A2 的条件格式向下复制。
B列可按此方法设置。
(2) 如果A列和B列作为整体进行比较（即相同数据不在同一行）：
假设第一行是标题，单击A2单元格，单击“格式”->“条件格式”，将条件设置为：
"公式"=($B:$B,$A2)=0
单击“格式”->“字体”->“颜色”，选择红色，然后单击“确定”两次。
使用格式刷向下复制单元格 A2 的条件格式。
B列可按此方法设置。
按照上述方法设置后，AB列的数据不会被着色，A列没有B列或B列没有A列的数据会被标记为红色字体。
4、EXCEL中如何批量按行排序
假设有大量数据（数值），每行需要从大到小排序。 这个怎么做？
由于按行排序和按列排序只能有一个主键，因此只有主键相同时才能按辅助键排序。 因此，这个问题无法通过排序来解决。 解决方法如下：
1. 假设您的数据位于 A 至 E 列，请在单元格 F1 中输入公式：
=大($A1:$E1,(A1))
使用填充柄将公式复制到右侧并向下到适当的范围内。
您的原始数据将显示在 F 至 J 列中，按行从最大到最小排序。 如有必要，使用“选择性粘贴/值”复制到其他地方。
注：步骤1中的公式可以根据自己的实际情况（数据范围）进行相应修改。 如果要从小到大排序，则将公式改为： =SMALL($A1:$E1,(A1))
5、熟练运用函数组合进行多条件计数统计
例：第一行是标题，A列是“姓名”，B列是“班级”，C列是“语文成绩”，D列是“录取成绩”，现在我们要把“班级”算作“二” ", "“语文成绩”大于等于104、“录取结果”为“再教育”的人数。统计结果存放在本工作表的其他栏目中。
公式如下：
=SUM(IF((B2:B9999="二")*(C2:C9999>=104)*(D2:D9999="重复"),1,0))
输入公式后，按Ctrl+Shift+Enter，则自动添加数组公式符号“{}”。
6. 如何判断单元格中是否包含指定文本？
假设判断A1单元格是否有“指定文本”，可以使用以下任一公式：
=IF((A1,"*"&"指定文字"&"*")=1,"是","否")
=IF((FIND("指定文本",A1,1)),"无","是")
查找某个区域中唯一数据的数量
例如，要查找范围 A1:A100 中唯一数据的数量，如果出现多次，则仅统计一个数字。 有两种计算方法：
一种是使用数组公式：
=SUM(1/(A1:A100,A1:A100))
输入公式后，按Ctrl+Shift+Enter，则自动添加数组公式符号“{}”。
第二种是使用乘积求和函数：
=(1/(A1:A100,A1:A100))
7. 一个工作簿中有很多工作表。 如何快速整理目录？
1. 使用宏3.0获取每个工作表的名称。 方法：
Ctrl+F3显示自定义名称对话框，命名为X，并在“参考位置”框中输入：
=MID(GET.(1),FIND("]",GET.(1))+1,100)
当然
2、使用函数批量插入连接。 方法：
在目录表（通常是第一张表）的单元格 A2 中输入公式：
=("#'"&INDEX(X,ROW())&"'!A1",INDEX(X,ROW()))
向下填写公式直至出现错误，并生成目录。
