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

1、工作中最常用的excel函数公式大全一、数字处理1、取肯定值 =abs 数字 2、取整 =int 数字 3、四舍五入 =round数字 , 小数位数 二、判定公式1、把公式产生的错误值显示为空公式： c2=iferrora2/b2,""说明：假如是错误值就显示为空，否就正常显示；2、if 多条件判定返回值公式：c2=ifanda2<500,b2="未到期 "," 补款 ",""说明：两个条件同时成立用and, 任一个成立用or 函数；三、统计公式1、统计两个表格重复的内容公式 :b2=countifsheet

2、15.a:a,a2说明：假如返回值大于0 说明在另一个表中存在，0 就不存在；2、统计不重复的总人数公式： c2=sumproduct1/countifa2:a8,a2:a8说明 :用 countif统计出每人的显现次数，用1 除的方式把显现次数变成分母，然后相加；四、求和公式1、隔列求和公式： h3=sumif$a$2:$g$2,h$2,a3:g3或=sumproductmodcolumnb3:g3,2=0*b3:g3说明：假如标题行没有规章用第2 个公式2、单条件求和公式： f2=sumifa:a,e2,c:c说明： sumif函数的基本用法3、单条件模糊求和公式：详见下图说明：假如需要进

3、行模糊求和，就需要把握通配符的使用，其中星号是表示任意多个字符，如"*a*" 就表示 a 前和后有任意多个字符，即包含 a；4、多条件模糊求和公式： c11=sumifsc2:c7,a2:a7,a11&"*",b2:b7,b11说明：在 sumifs中可以使用通配符*5、多表相同位置求和公式： b2=sumsheet1:sheet19.b2说明：在表中间删除或添加表后，公式结果会自动更新；6、按日期和产品求和公 式：f2=sumproductmonth$a$2:$a$25=f$1*$b$2:$b$25=$e2 *$c$2:$c$25说明： sum

4、product可以完成多条件求和五、查找与引用公式1、单条件查找公式公式1： c11=vlookupb11,b3:f7,4,false说明：查找是vlookup最善于的，基本用法2、双向查找公式公式=indexc3:h7,matchb10,b3:b7,0,matchc10,c2:h2,0说明：利用match函数查找位置，用index函数取值3、查找最终一条符合条件的记录；公式：详见下图说明： 0/ 条件 可以把不符合条件的变成错误值，而lookup可以忽略错误值4、多条件查找公式 :详见下图说明 :公式原理同上一个公式5、指定区域最终一个非空值查找公式 ;详见下图说明：略6、按数字区域间取对应

5、的值公式：详见下图公式说明： vlookup和 lookup函数都可以按区间取值，肯定要留意， 销售量列的数字一定要升序排列；六、字符串处理公式1、多单元格字符串合并公式： c2=phonetica2:a7说明： phonetic函数只能对字符型内容合并，数字不行以；2、截取除后3 位之外的部分公式：=leftd1,lend1-3说明： len运算出总长度 ,left从左边截总长度 -3 个3 、截取 -前的部分公式:b2=lefta1,find"-",a1-1说明：用find函数查找位置，用 left截取；4、截取字符串中任一段的公式公式:b1=trimmidsubsti

6、tute$a1," ",rept" ",20,20,20说明 :公式是利用强插n 个空字符的方式进行截取5、字符串查找公式： b2=ifcountfind"河南 ",a2=0," 否"," 是"说明 : find查找胜利，返回字符的位置， 否就返回错误值， 而 count可以统计出数字的个数，这里可以用来判定查找是否胜利；6、字符串查找一对多公式： b2=ifcountfind"辽宁","黑龙江 ","吉林",a2=0," 其

7、他","东北"说明： 设置 find 第一个参数为常量数组，用count函数统计 find 查找结果七、日期运算公式1、两日期相隔的年、月、天数运算a1 是开头日期（ 2021-12-1 ）， b1 是终止日期 2021-6-10；运算：相隔多少天？ =datedifa1,b1,"d"结果： 557相隔多少月. =datedifa1,b1,"m"结果： 18相隔多少年. =datedifa1,b1,"y"结果： 1不考虑年相隔多少月？=datedifa1,b1,"ym"结果： 6 不考

8、虑年相隔多少天？=datedifa1,b1,"yd"结果： 192不考虑年月相隔多少天？=datedifa1,b1,"md"结果： 9datedif函数第 3 个参数说明："y"时间段中的整年数；"m"时间段中的整月数；"d"时间段中的天数；"md"天数的差；忽视日期中的月和年；"ym"月数的差；忽视日期中的日和年；"yd"天数的差；忽视日期中的年；2、扣除周末天数的工作日天数公式： c2=networkdays.intlifb2<

9、;date2021,1,1, date 2021,1,1,b2,date2021,1,1,b2,date2021,1,31,11说明：返回两个日期之间的全部工作日数，使用参数指示哪些天是周末，以及有多少天是周末；周末和任何指定为假期的日期不被视为工作日excel常用电子表格公式大全1、查找重复内容公式：=ifcountifa:a,a2>1,"重复 ","" ；2、用诞生年月来运算年龄公式：=truncdays360h6,"2021/8/30",false/360,0；3、从输入的18 位身份证号的诞生年月运算公式：=concat

10、enatemide2,7,4,"/",mide2,11,2,"/",mide2,13,2；4、从输入的身份证号码内让系统自动提取性别，可以输入以下公式：=if lenc2=15,ifmodmidc2,15,1,2=1,"男"," 女",ifmodmidc2,17,1,2=1,"男"," 女"公式内的“c2 ”代表的是输入身份证号码的单元格；5、求和：=sumk2:k56对 k2 到 k56 这一区域进行求和；6、平均数：=averagek2:k56对 k2 k56这一区域求平

11、均数；7、排名： =rankk2，k$2:k$56对 55 名同学的成果进行排名；8 、等级：=ifk2>=85,"优",ifk2>=74,"良",ifk2>=60,"及格 "," 不及格 "9、学期总评：=k2*0.3+m2*0.3+n2*0.4假设 k 列、m 列和 n列分别存放着同学的“平常总评”、“期中、”“期末”三项成果；10 、最高分：=maxk2:k56求 k2 到 k56区域（ 55 名同学）的最高分；11 、最低分：=mink2:k56求 k2 到 k56 区域（ 55 名同学）

12、的最低分；12 、分数段人数统计：（ 1） =countifk2:k56,"100" 求 k2 到 k56 区域100 分的人数；假设把结果存放于k57 单元格；（ 2） =countifk2:k56,">=95" k57求k2到 k56区域95 99.5分的人数；假设把结果存放于k58 单元格；（ 3） =countifk2:k56,">=90" sumk57:k58求k2到k56区域 90 94.5分的人数；假设把结果存放于k59 单元格；（ 4） =countifk2:k56,">=85"

13、sumk57:k59求k2到k56区域 85 89.5分的人数；假设把结果存放于k60 单元格；（ 5） =countifk2:k56,">=70" sumk57:k60求k2到k56区域 70 84.5分的人数；假设把结果存放于k61 单元格；（ 6） =countifk2:k56,">=60" sumk57:k61求k2到k56区域 60 69.5分的人数；假设把结果存放于k62 单元格；（ 7） =countifk2:k56,"<60"求 k2 到 k56 区域 60 分以下的人数；假设把结果存放于k63单元格

14、；说明： countif函数也可运算某一区域男、女生人数；如： =countifc2:c351,"男" 求 c2 到 c351区域（共 350人）男性人数；13 、优秀率：=sumk57:k60/55*10014 、及格率：=sumk57:k62/55*10015 、标准差：=stdevk2:k56求k2到 k56 区域 55人的成果波动情形（数值越小，说明该班同学间的成果差异较小，反之，说明该班存在两极分化） ；16 、条件求和：=sumifb2:b56,"男"， k2:k56假设 b 列存放同学的性别， k 列存放同学的分数，就此函数返回的结果表示求

15、该班男生的成果之和；17 、多条件求和： =sumifc3:c322="男",ifg3:g322=1,1,0假设 c 列（ c3:c322区域）存放同学的性别，g 列（ g3:g322区域）存放同学所在班级代码（1、2、3、4、5），就此函数返回的结果表示求一班的男生人数；这是一个数组函数，输完后要按ctrl shift enter组合键 产生“”；“”不能手工输入，只能用组合键产生；18 、依据诞生日期自动运算周岁：=truncdays360d3,now /360,0假设d 列存放同学的诞生日期，e 列输入该函数后就产生该生的周岁；19 、在 word中三个小窍门：连续输

16、入三个“ ”可得一条波浪线；连续输入三个“-”可得一条直线；连续输入三个“= ”可得一条双直线；一、excel中当某一单元格符合特定条件，如何在另一单元格显示特定的颜色比如：a1 1 时， c1 显示红色0<a1<1时， c1 显示绿色a1<0时， c1 显示黄色方法如下：1、单元击 c1 单元格，点“格式”>“条件格式，”条件1 设为：公式=a1=12、点“格式”->“字体-”>“颜色，”点击红色后点“确定”；条件 2 设为：公式 =anda1>0,a1<13、点“格式”->“字体-”>“颜色，”点击绿色后点“确定”；条件3 设为：

17、公式=a1<0点“格式-”> “字体-”> “颜色，”点击黄色后点“确定；”4、三个条件设定好后，点“确定”即出；二、 excel中如何掌握每列数据的长度并防止重复录入1、用数据有效性定义数据长度；用鼠标选定你要输入的数据范畴，点"数据 "->" 有效性 "->" 设置 "，"有效性条件 "设成 "答应 ""文本长度 ""等于 ""5"（ 具体条件可依据你的需要转变）；仍可以定义一些提示信息、出错警告信息和

18、是否打开中文输入法等，定义好后点"确定 "；2、用条件格式防止重复；选 定a列 ， 点 " 格 式 "->" 条 件 格 式 " ， 将 条 件 设 成 “公 式=countif$a:$a,$a1>1”，点"格式 "->" 字体 "->" 颜色 "，选定红色后点两次 "确定 "；这样设定好后你输入数据假如长度不对会有提示，假如数据重复字体将会变成红色；三、在 excel中如何把 b 列与a 列不同之处标识出来？（一）、假如是要求a、

19、b 两列的同一行数据相比较：假定第一行为表头，单击a2 单元格，点“格式”-> “条件格式，”将条件设为 :“单元格数值”“不等于”=b2点“格式”-> “字体-”>“颜色，”选中红色，点两次“确定”；用格式刷将a2单元格的条件格式向下复制；b 列可参照此方法设置；（二）、假如是 a 列与 b 列整体比较（即相同数据不在同一行）： 假定第一行为表头，单击a2 单元格，点“格式”-> “条件格式，”将条件设为 :“公式=”countif$b:$b,$a2=0点“格式”->“字体-”>“颜色，”选中红色，点两次“确定”；用格式刷将 a2 单元格的条件格式向下复制

20、； b 列可参照此方法设置；按以上方法设置后，ab 列均有的数据不着色，a 列有b 列无或者 b列有a 列无的数据标记为红色字体；四、 excel中怎样批量地处理按行排序假定有大量的数据数值 ，需要将每一行按从大到小排序，如何操作？由于按行排序与按列排序都是只能有一个主关键字,主关键字相同时才能按次关键字排序；所以，这一问题不能用排序来解决；解决方法如下： 1、假定你的数据在a 至 e 列，请在 f1 单元格输入公式：=large$a1:$e1,columna1用填充柄将公式向右向下复制到相应范畴；你原有数据将按行从大到小排序显现在f至 j 列；如有需要可用“挑选性粘贴 /数值”复制到其他地方

21、；注：第1 步的公式可依据你的实际情形（数据范畴）作相应的修改；假如要从小到大排序,公式改为 :=small$a1:$e1,columna1五、巧用函数组合进行多条件的计数统计例：第一行为表头，a 列是“姓名，”b 列是“班级，”c 列是“语文成果”，d 列是“录用结果，”现在要统计“班级”为“，二“”语文成果”大于等于104 ， “录用结果”为“重本”的人数；统计结果存放在本工作表的其他列； 公式如下：=sumifb2:b9999="二 "*c2:c9999>=104*d2:d9999="重 本",1,0输入完公式后按ctrl+shift+ent

22、er键, 让它自动加上数组公式符号"" ；六、如何判定单元格里是否包含指定文本？假定对 a1 单元格进行判定有无"指定文本 ",以下任一公式均可:=ifcountifa1,"*"&"指定文本 "&"*"=1," 有"," 无"=ifiserrorfind"指定文本 ",a1,1," 无"," 有"求某一区域内不重复的数据个数例如求 a1:a100范畴内不重复数据的个数，某个数重复多

23、次显现只算一个；有两种运算方法：一是利用数组公式：=sum1/countifa1:a100,a1:a100输入完公式后按ctrl+shift+enter键, 让它自动加上数组公式符号 "" ；二是利用乘积求和函数：=sumproduct1/countifa1:a100,a1:a100七、一个工作薄中有很多工作表如何快速整理出一个目录工作表 1、用宏 3.0 取出各工作表的名称，方法： ctrl+f3显现自定义名称对话框，取名为 x，在“引用位置”框中输入：=midget.workbook1,find"",get.workbook1+1,10 0确定2、用hyperlink函数批量插入连接，方法：在目录工作表（一般为第一个sheet ）的 a2 单元格输入公式：=hyperlink"#'"&indexx,row&"'.a1",indexx,row将公式向下填充，直到出错为止，目录就生成了；函数表达方式具体说明数据 1数据 2备注=t0day2021-8-4=today ，用来显示今日的时间2=yearf31978=year 提取日期里面的年份1978-9-28