excel公式大全讲解|excel表格函数公式大全

在本篇介绍，主编罗列了咱们常见的Excel常用电子表格公式，并且附带有相应的案例和Excel源文件提供给大家学习。

第一，查找重复内容
 
　　你们在C2单元格输入公式：=iF(CounTiF(A:A,A2)>1,"重复","")，可以将A列重复的姓名寻找出来。
 

第二，Excel中用出生年月来计算年龄
 
　　在C2单元格输入公式：=TRunC((dAYs360(B2,TodAY(),FALsE))/360,0)
 

第三，Excel提取出生年月
 
　　从输入的18位身份证号中提取出生年月计算公式，你们在C2单元格输入公式：=ConCATEnATE(Mid(B2,7,4),"/",Mid(B2,11,2),"/",Mid(B2,13,2))

第四，Excel提取性别
 
　　从输入的身份证号码内让Excel自动提取性别，你们在C2单元格输入公式：=iF(LEn(B2)=15,iF(Mod(Mid(B2,15,1),2)=1,"男","女"),iF(Mod(Mid(B2,17,1),2)=1,"男","女"))

 
第五，Excel求和、求平均、等级、排名、最高分、最低分
 
　　d2单元格求和公式：=suM(B2:B12)
　　E2单元格求平均值公式：=AVERAGE(B2:B12)
　　F2单元格求最高分公式：=MAx(B2:B12)
　　G2单元格求最低分公式：=Min(B2:B12)
　　H列的排名公式：=RAnK(B2,$B$2:$B$12)
　　i列的等级算法公式：=iF(B2>=85,"优",iF(B2>=74,"良",iF(B2>=60,"及格","不及格")))

 
第六，条件格式的使用
 
　　excel中当某一单元格符合特定条件，怎么在另一单元格显示特定的颜色比如：A1〉1时，C1显示红色；0<A1<1时，C1显示官方；A1<0时，C1显示黄色。
　　办法如下：单击C1单元格，点“格式”>“条件格式”：
　　1、 条件1设为：公式 =A1=1，点“格式”->“字体”->“颜色”，点击红色后点“确定”。
　　2、条件2设为：公式 =And(A1>0,A1<1)，点“格式”->“字体”->“颜色”，点击官方后点“确定”。
　　3、条件3设为：公式 =A1<0，点“格式”->“字体”->“颜色”，点击黄色后点“确定”。
　　4、三个条件设定好后，点“确定”直接。
 
第七，数据有效性的使用
 
　　ExCEL中怎么控制每列数据的长度并避免重复录入？

　　1、用数据有效性定义数据长度。
　　用鼠标选定您要输入的数据范围，点"数据"->"有效性"->"配置"，"有效性条件"设成"允许""文本长度""等于""5"（全部条件可根据您的需要改变）。
　　还可以定义一些提示信息、出错警告信息和是否打开免费输入法等，定义好后点"确定"。
　　2、用条件格式避免重复。
　　选定A列，点"格式"->"条件格式"，将条件设成“公式=CounTiF($A:$A,$A1)>1”，点"格式"->"字体"->"颜色"，选定红色后点两次"确定"。
这样设定好后您输入数据如果长度不对会有提示，如果数据重复字体将会变成红色。
 
第八，巧用函数组合进行多条件的计数统计
 
　　统计“班级”为“二班”，“语文成绩”大于等于104，“录取后果”为“重本”的人数。
　　公式：=suM(iF((B2:B9999="二班")*(C2:C9999>=104)*(d2:d9999="重本"),1,0))
　　输入完公式后按Ctrl+shift+Enter键,让它自动加上数组公式符号"{}"。

 
第九，求某一区域内不重复的数据个数
 
　　例如求A2:A12范围内不重复姓名的个数，某个姓名重复多次出现只算一个。有两种计算办法：
　　一是利用数组公式：=suM(1/CounTiF(A2:A12,A2:A12))，输入完公式后按Ctrl+shift+Enter键,让它自动加上数组公式符号"{}"。
　　二是利用乘积求和函数：=suMpRoduCT(1/CounTiF(A2:A12,A2:A12))

 
第十，做Excel目录工作表
 
　　一个Excel工作薄中有很多工作表怎么快速整理出一个目录工作表呢？

　　第一步，Ctrl+F3出现自定义名称对话框，取名为x，在“引用位置”框中输入：
=Mid(GET.woRKBooK(1),Find("]",GET.woRKBooK(1))+1,100)，确定。

 
 
　　第二步，用HYpERLinK函数批量插入连接，办法：在目录工作表（一般为第一个sheet）的A2单元格输入公式：=HYpERLinK("#'"&indEx(x,Row())&"'!A1",indEx(x,Row()))，将公式向下填充，直到出错为止，目录就生成了。
 

　　最后，轻易一下，主编再给大家共享一个word小技巧。在word中连续输入三个“~”，按下回车键可得到一条波浪线。连续输入三个“-”，按下回车键可得到一条直线。连续输入三个“=”，按下回车键可得到一条双直线。