工作中最常用地excel函数公式大全

1、工作中最常用的excel函数公式大全一、数字处理1、取绝对值=ABS数字）2、取整=INT（数字）3、四舍五入=ROUN二、判断公式1、把公式产生的错误值显示为空公式：C2=IFERROR（A2/B2,）说明：如果是错误值则显示为空，否则正常显示。21 / 212、IF多条件判断返回值公式:C2=IF（AND（A2500,B2=未到期）,补款,）说明：两个条件同时成立用 AND任一个成立用OR函数。二、统计公式1、统计两个表格重复的_C2_ T=IF(AND(A2500flABCD1是否到期提醒2800未到期3200已到期4300未到期补蔽5内容式:B2=COUNTIF(Sheet15!A:A

2、,A2)说明：如果返回值大于 0说明在另一个表中存在，0则不存在。ATTc 1D1姓名总人数2(A41SBA4C5D6A7ABB2、统计不重复的总人数公式：C2=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8) 说明:用COUNTIF统计出每人的出现次数，用 1除的方式把出现次数变成分母，然后相加。四、求和公式1、隔列求和公式H3=SUMIF($A$2:$G$2,H$2,A3:G3)或=SUMPRODUCT(MOD(COLUMN(B3:G3),2)=0)*B3:G3)7891011说明：如果标题行没有规则用第2个公式ABCDEFGH1言1=1.1口口1月2j冃3月合计2实际计

3、划实际计划实际计划实际计划3A16937417B976523十辽C6101076公式：=SUMIF($A$2：$G$2/H$2,A3：G3)122、单条件求和公式：F2=SUMIF(A A E2,C:C) 说明：SUMIF函数的基本用法ABCDEFG1 1产品单价金额产经合计2A324AB83E4124C6135A186E4127A216公式；SB4129E4126UM1I (A1A.E2CC)10C6ia-fl = 200例弓：根据提成表计算捕售提成提成表姓名销馬数星虽个提成提成额2510李玉吕300154500张千顺15575吴柳15091350孟良1009900D9公式：=XUP(C9,

4、B$4：C$6f2)ffllookup =LOOKUP(C9,B$4:B$6fC$4:C$6)用!F区间多时公式太复杂”不写了2、截取除后3位之外的部分公式：=LEFT(D1,LEN(D1)-3) 说明：LEN计算出总长度 丄EFT从左边截总长度-3个截取-前的部 分公式：B2=Left(A1,FIND(-,A1)-1)说明：用FIND函数查找位置，用LEFT截取。4、截取字符串中任一段的公式 公A1科目一级科目2银行存款建行银行存款3应收账款张三应收账款4其他应收款-赵志东其他应收款56n式：B仁TRIM(MID(SUBSTITUTE($A1, ,REPT( ,20),20,20)说明:公式

5、是利用强插N个空字符的方式进行截取ABCDE32 56 176 12 2232561761222;1.2 I5、字符串查找公式：B2=IF(C0UNT(FIND(河南,A2)=0, 否,是)说明:FIND查找成功，返回字符的位置，否则返回错误值，而COUNT可以统计出数字的个数，这里可以用来判断查找是否成功。AE1地址是否河南2囲洛阳市是3山东济南否4河南南旧是5丽匕石家庄否I 6 J76、字符串查找一对多公式：B2=IF(COUNT(FIND(辽宁,黑龙江,吉林,A2)=0,其他,东北)说明：设置FIND第一个参数为常量数组，用 COUNT!数统计FIND查找结果七、日期计算公式1、两日期相

6、隔的年、月、天数计算A1是开始日期(2011-12-1 ), B1是结束日期(2013-6-10)计算：相隔多少天？ =datedif(A1,B1,d)结果：557相隔多少月? =datedif(A1,B1,m)结果：18相隔多少年? =datedif(A1,B1,Y)结果：1不考虑年相隔多少月？ =datedif(A1,B1,Ym)结果：6不考虑年相隔多少天？ =datedif(A1,B1, YD) 结果：192 不考虑年月相隔多少天？ =datedif(A1,B1,MD) 结果：9 datedif函数第3个参数说明：Y时间段中的整年数。M时间段中的整月数。D时间段中的天数。MD天数的差。忽

7、略日期中的月和年。YM月数的差。忽略日期中的日和年。YD天数的差。忽略日期中的年。2、扣除周末天数的工作日天数公式：C2=NETW0RKDA YS.IN TL(IF(B21,重复,)。2、用出生年月来计算年龄公式：=TRUNC(DA YS360(H6,2009/8/30,FALSE)/360,0)。3、从输入的18位身份证号的出生年月计算公式：二CONCATENATE(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

8、, 男,女 ),IF(MOD(MID(C2,17,1),2)=1, 男,女)公式内的“ C?代表的 是输入身份证号码的单元格。5、 求和：=SUM(K2:K56) 对K2到K56这一区域进行求和；6、平均数：=AVERAGE(K2:K56) 对 K2 K56 这一区域求平均数；7、排名： =RANK(K2，K$2:K$56) 对 55 名学生的成绩进行排名；& 等级：=IF(K2=85,优,IF(K2=74, 良,IF(K2=60, 及格,不及格 )9、学期总评：=K2*0.3+M2*0.3+N2*0.4 假设 K 列、M列和 N列分别存放着学生的“平时总评” 、“期中”、“期末”三项成绩；1

9、0、最高分：=MAX(K2:K56) 求 K2 到 K56 区域( 55 名学生)的最高分；11 、最低分：=MIN(K2:K56) 求 K2 到 K56 区域( 55 名学生)的最低分；12、分数段人数统计：(1) =COUNTIF(K2:K56,100)求 K2 到 K56 区域 100 分的人数；假设把结果存放于 K57 单元格；(2) =COUNTIF(K2:K56,=95) - K57 求 K2 到 K56 区域 9599.5 分的人数；假设把结果存放于 K58 单元格；( 3) =COUNTIF(K2:K56,=90) - SUM(K57:K58) 求 K2 到 K56区域9094

10、.5分的人数；假设把结果存放于K59单元格；( 4) =COUNTIF(K2:K56,=85) - SUM(K57:K59) 求 K2 到 K56区域8589.5分的人数；假设把结果存放于K60单元格；( 5) =COUNTIF(K2:K56,=70) - SUM(K57:K60) 求 K2 到 K56区域7084.5分的人数；假设把结果存放于K61单元格；( 6) =COUNTIF(K2:K56,=60) - SUM(K57:K61) 求 K2 到 K56 区域6069.5分的人数；假设把结果存放于 K62单元格；(7) =COUNTIF(K2:K56,60)求 K2 到 K56 区域 60

11、 分以下的人数；假设把结果存放于 K63 单元格；说明：COUNTIF函数也可计算某一区域男、女生人数。如：=COUNTIF(C2:C351,男) 求 C2到 C351 区域(共 350 人) 男性人数；13、优秀率： =SUM(K57:K60)/55*10014、及格率： =SUM(K57:K62)/55*10015、 标准差：=STDEV(K2:K56) 求K2到K56区域(55人)的成绩波动情况(数值越小，说明该班学生间的成绩差异较小，反之，说 明该班存在两极分化) ；16、条件求和：=SUMIF(B2:B56,男，K2:K56) 假设 B列存放 学生的性别，K列存放学生的分数，则此函数

12、返回的结果表示求该班 男生的成绩之和；17、 多条件求和： =SUM(IF(C3:C322=男,IF(G3:G322=1,1,0)假设C列(C3:C322区域)存放学生的性别，G列(G3G322区域)存放学生所在班级代码( 1、 2、 3、 4、 5)，则此函数返回的结 果表示求一班的男生人数；这是一个数组函数，输完后要按 Ctrl Shift + Enter组合键(产生“ ”)。“ ”不能手工输入，只能 用组合键产生。18、根据出生日期自动计算周岁：=TRUNC(DAYS360(D3,NOW( )/360,0)假设D列存放学生的出生日期，E列输入该函数后则产生该生的周岁。19、在Word中三

13、个小窍门： 连续输入三个“ ”可得一条波浪线。 连续输入三个“ - ”可得一条直线。 连续输入三个“ =”可得一条双直线。一、excel 中当某一单元格符合特定条件， 如何在另一单元格显示特 定的颜色比如：A11 时， C1 显示红色0A11 时， C1 显示绿色A1“条件格式”，条件 1 设为：公 式 =A1=12、点“格式” -“字体” - “颜色”，点击红色后点“确定” 。条件2 设为：公式 =AND(A10,A1 “字体” -“颜色”，点击绿色后点“确定” 。 条件 3设为：公式 =A1 “字体” -“颜色”，点 击黄色后点“确定” 。4、三个条件设定好后，点“确定”即出。二、EXCE

14、L中如何控制每列数据的长度并避免重复录入 1 、用数据有效性定义数据长度。 用鼠标选定你要输入的数据范围，点 数据- 有效性 - 设置， 有效性条件 设成允许 文本长度 等于5 （具体条件可根据你 的需要改变）。 还可以定义一些提示信息、出错警告信息和是否打开中文输入法等， 定义好后点 确定 。2、用条件格式避免重复。选 定 A 列，点 格式- 条件 格式 ， 将条件设 成“公 式 =C0UNTIF（$A:$A,$A1）1”，点格式-字体-颜色，选定红色 后点两次 确定。这样设定好后你输入数据如果长度不对会有提示，如果数据重复字 体将会变成红色。三、在EXCEL中如何把B列与A列不同之处标识出

15、来？ （一）、如果是要求 A、 B 两列的同一行数据相比较： 假定第一行为表头，单击 A2 单元格，点“格式” -“条件格式”， 将条件设为 : “单元格数值” “不等于” =B2点“格式” -“字体” -“颜色”，选中红色，点两次“确定” 。用格式刷将 A2 单元格的条件格式向下复制。B 列可参照此方法设置。（二）、如果是A列与B列整体比较（即相同数据不在同一行）： 假定第一行为表头，单击 A2 单元格，点“格式” -“条件格式”， 将条件设为 : “公式” =C0UNTIF（$B:$B,$A2）=0 点“格式” -“字体” - “颜色”，选中红色，点两次“确定” 。用格式刷将A2单元格的条

16、件格式向下复制。B列可参照此方法设置。 按以上方法设置后，AB列均有的数据不着色，A列有B列无或者B 列有A列无的数据标记为红色字体。四、EXCEL中怎样批量地处理按行排序假定有大量的数据 (数值 ) ，需要将每一行按从大到小排序，如何操 作？由于按行排序与按列排序都是只能有一个主关键字 , 主关键字相同 时才能按次关键字排序。所以，这一问题不能用排序来解决。解决 方法如下：1、假定你的数据在 A至E列，请在F1单元格输入公式： =LARGE($A1:$E1,COLUMN(A1) 用填充柄将公式向右向下复制到相应范围。你原有数据将按行从大 到小排序出现在F至J列。如有需要可用“选择性粘贴/数值

17、”复制 到其他地方。注：第 1 步的公式可根据你的实际情况(数据范围)作相应的修改。 如果要从小到大排序 ,公式改为 :=SMALL($A1:$E1,COLUMN(A1)五、巧用函数组合进行多条件的计数统计例：第一行为表头，A列是“姓名”，B列是“班级”，C列是“语文 成绩”，D列是“录取结果”，现在要统计“班级”为“二”，“语文成 绩”大于等于 104，“录取结果”为“重本”的人数。统计结果存放 在本工作表的其他列。公式如下：=SUM(IF(B2:B9999= 二 )*(C2:C9999=104)*(D2:D9999= 重 本),1,0)输入完公式后按 Ctrl+Shift+Enter键 ,

18、 让它自动加上数组公式符号 。六、如何判断单元格里是否包含指定文本？假定对A1单元格进行判断有无指定文本,以下任一公式均可: =IF(COUNTIF(A1,*& 指定文本 &*)=1, 有, 无)=IF(ISERROR(FIND(指定文本,A1,1),无,有) 求某一区域内不重复的数据个数例如求 A1:A100 范围内不重复数据的个数，某个数重复多次出现只 算一个。有两种计算方法：一是利用数组公式： =SUM(1/COUNTIF(A1:A100,A1:A100) 输入完公式后按 Ctrl+Shift+Enter 键 , 让它自动加上数组公式符号 。二是利用乘积求和函数：=SUMPRODUCT(

19、1/COUNTIF(A1:A100,A1:A100)七、一个工作薄中有许多工作表如何快速整理出一个目录工作表1 、用宏 3.0 取出各工作表的名称，方法：Ctrl+F3出现自定义名称对话框，取名为X，在“引用位置”框中输入： =MID(GET.WORKBOOK(1),FIND(,GET.WORKBOOK(1)+1,100) 确定2、用HYPERLIN函数批量插入连接，方法：在目录工作表(一般为第一个 sheet )的A2单元格输入公式：=HYPERLINK(#&INDEX(X,ROW()&!A1,INDEX(X,ROW()将公式向下填充，直到出错为止，目录就生成了函数表达方式详细说明数据1数据2备注=tOday()2015-8-4=TODAY(),用来显示今天的时间2=YEAR(F3)1978=YEAR()提取日期里面的年份1978-9-28=NOW()2015-8-4 11:29=NO()，用来显示现在的时间9=DATEDIF(F9,G9,y)7=DATEDIF(A1,A2,y)这