搜档网
当前位置:搜档网 › excel表格日期计算

excel表格日期计算

excel表格日期计算
excel表格日期计算

竭诚为您提供优质文档/双击可除

excel表格日期计算

篇一:excel中几个时间计算公式

假设b2为生日

=datedif(b2,today(),"y")

datediF函数,除excel2000中在帮助文档有描述外,其他版本的excel在帮助文档中都没有说明,并且在所有版本的函数向导中也都找不到此函数。但该函数在电子表格中确实存在,并且用来计算两个日期之间的天数、月数或年数很方便。微软称,提供此函数是为了与lotus1-2-3兼容。

该函数的用法为

“datediF(start_date,end_date,unit)”,其中start_date 为一个日期,它代表时间段内的第一个日期或起始日期。end_date为一个日期,它代表时间段内的最后一个日期或结束日期。unit为所需信息的返回类型。

“y”为时间段中的整年数,“m”为时间段中的整月数,“d”时间段中的天数。“md”为start_date与end_date日期中天数的差,可忽略日期中的月和年。“ym”为start_date 与end_date日期中月数的差,可忽略日期中的日和年。“yd”

为start_date与end_date日期中天数的差,可忽略日期中的年。比如,b2单元格中存放的是出生日期(输入年月日时,用斜线或短横线隔开),在c2单元格中输入

“=datedif(b2,today(),"y")”(c2单元格的格式为常规),按回车键后,c2单元格中的数值就是计算后的年龄。此函数在计算时,只有在两日期相差满12个月,才算为一年,假如生日是20xx年2月27日,今天是20xx年2月28日,用此函数计算的年龄则为0岁,这样算出的年龄其实是最公平的。

身份证号提取年龄

=datediF(--text((len(a1)=15)*19”即可获得当时的日期时间;

2、使用公式:用=now()而非=date(),=date()只有日期,然后进行菜单“工具->选项”,选择“重新计算”页,选中“人工重算”,勾不勾选“保存前自动重算”看自己的需要和想法了,如果勾选了,那日期时间那总是最后一次保存的日期时间,不勾选的话,如果你的表格中有公式记得准备存前按F9

篇二:excel中如何计算两个日期之间的月数

excel中如何计算两个日期之间的月数

EXCLE日期时间计算公式

(Excel)常用函数公式及操作技巧之三: 时间和日期应用 ——通过知识共享树立个人品牌。 自动显示当前日期公式 =YEAR(NOW()) 当前年 =MONTH(NOW()) 当前月 =DAY((NOW())) 当前日 如何在单元格中自动填入当前日期 Ctrl+; 如何判断某日是否星期天 =WEEKDAY(A2,2) =TEXT(A1,"aaaa") =MOD(A1,7)<2 某个日期是星期几 比如2007年2月9日,在一单元格内显示星期几。 =TEXT(A1,"aaa") (五) =TEXT(A1,"aaaa") (星期五) =TEXT(A1,"ddd") (Fri) =TEXT(A1,"dddd") (Friday) 什么函数可以显示当前星期 如:星期二10:41:56 =TEXT(NOW(),"aaaa hh:mm:ss") 求本月天数 设A1为2006-8-4 求本月天数 A1=DAY(DATE(YEAR(A1),MONTH(A1)+1,0)) 也有更簡便的公式:=DAY(EOMONTH(NOW(),0)) 需加載分析工具箱。

当前月天 数:=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-DATE(YEAR(TO DAY()),MONTH(TODAY()),1) 用公式算出除去当月星期六、星期日以外的天数 =SUMPRODUCT(--(MOD(ROW(INDIRECT(DATE(YEAR(NOW() ),MONTH(NOW()),1)&":"&DATE(YEAR(NOW()),MONTH(NOW ())+1,0))),7)>1)) 显示昨天的日期 每天需要单元格内显示昨天的日期,但双休日除外。 例如,今天是7月3号的话,就显示7月2号,如果是7月9号,就显示7 月6号。 =IF(TEXT(TODAY(),"AAA")="一 ",TODAY()-3,IF(TEXT(TODAY(),"AAA")="日 ",TODAY()-2,TODAY()-1)) =IF(TEXT(TODAY(),"AAA")="一",TODAY()-3,TODAY()-1) 关于取日期 怎么设个公式使A1在年月日向后推5年,变成2011-7-15 =DATE(YEAR(A1)+5,MONTH(A1),DAY(A1)) =EDATE(A1,12*5) 如何对日期进行上、中、下旬区分 =LOOKUP(DAY(A1),{0,11,21,31},{"上旬","中旬","下旬","下旬"}) 如何获取一个月的最大天数 "=DAY(DATE(2002,3,1)-1)"或"=DAY(B1-1)",B1为"2001-03-01日期格式转换公式 将“01/12/2005”转换成“20050112”格式 =RIGHT(A1,4)&MID(A1,4,2)&LEFT(A1,2) = YEAR($A2)&TEXT(MONTH($A2),"00")&TEXT(DAY($A2),"00" ) 该公式不用设置数据有效性,但要设置储存格格式。 也可以用下列两方法: 1、先转换成文本, 然后再用字符处理函数。 2、[数据]-[分列] [日期]-[MDY] 将“2005年9月”转换成“200509”格式

Excel函数怎么计算已知出生日期计算员工年龄

Excel函数怎么计算已知出生日期计算员 工年龄 如果老板给你一份关于所有员工的基本资料,里面有他们的出生日期,但是没有年龄,想让你计算出每一位员工的实际年龄,你会怎么做呢?借助Excel函数,可以很好的做到。以下是为您带来的关于Excel函数计算已知出生日期计算员工年龄,希望对您有所帮助。 Excel函数计算已知出生日期计算员工年龄 1、选中C2单元格,切换到“公式”选项卡,在“函数库”组中找

到日期和时间函数TODAY。 2、此时会弹出“函数参数”对话框,上面有对TODAY函数的介绍,说它是返回日期格式的当前日期,且不需要参数,直接确定即可。 3、现在C2单元格中会返回TODAY函数的值,我们双击C2单元格就可以进入编辑状态,然后在现有的公式后加上“-B2”即可。这是

我们一般理解的求年龄的方法,用现在的日期减去出生日期。 4、但是你会发现Excel算出来的结果是一个日期,这是比较容易理解的,因为一个日期减去另外一个日期嘛,结果当然也就是一个日期了,没关系,我们把这个日期换算成一个年份值就好了。Excel中正好有对应的函数,它的名字叫做YEAR。那现在只好委屈一下,让我们之前的那个公式成为YEAR函数的参数咯。

YEAR函数。

6、在弹出的YEAR函数参数对话框中,将之前剪切的内容粘贴到它的参数对应的文本框内,并确定。 7、当我们把日期值换算成一个年份值之后,你发现C2单元格显示的还是一个日期格式的值,那现在我们就要调整它的数字格式了。选

中C2单元格,将其数字格式设置为“常规”。 8、好了,现在C2单元格中显示的是一个数值了,但还是不对,用脑子计算一下,你发现它多了1900年,这是因为Excel函数使用了1900年时间系统,YEAR函数返回的是一个1900至9999之间的值,所以我们自己在现有的公式后减去多出来的1900就好了。

Excel电子表格计算公式使用方法技巧总结

Excel电子表格计算公式使用方法25条公式技巧总结 对于Excel表格计算公式的方法实在太多,今天就整理了一个公式大全需要对有需要的朋友有些帮助。 1、两列数据查找相同值对应的位置 =MATCH(B1,A:A,0) 2、已知公式得结果 定义名称=EVALUATE(Sheet1!C1) 已知结果得公式 定义名称=GET.CELL(6,Sheet1!C1) 3、强制换行 用Alt+Enter 4、超过15位数字输入 这个问题问的人太多了,也收起来吧。一、单元格设置为文本;二、在输入数字前先输入' 5、如果隐藏了B列,如果让它显示出来? 选中A到C列,点击右键,取消隐藏 选中A到C列,双击选中任一列宽线或改变任一列宽 将鼠标移到到AC列之间,等鼠标变为双竖线时拖动之。 6、EXCEL中行列互换 复制,选择性粘贴,选中转置,确定即可 7、Excel是怎么加密的 (1)、保存时可以的另存为>>右上角的"工具">>常规>>设置 (2)、工具>>选项>>安全性

8、关于COUNTIF COUNTIF函数只能有一个条件,如大于90,为=COUNTIF(A1:A10,">=90") 介于80与90之间需用减,为=COUNTIF(A1:A10,">80")-COUNTIF(A1:A10,">90") 9、根据身份证号提取出生日期 (1)、=IF(LEN(A1)=18,DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),IF (LEN(A1)=15,DATE(MID(A1,7,2),MID(A1,9,2),MID(A1,11,2)),"错误身份证号")) (2)、=TEXT(MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")*1 10、想在SHEET2中完全引用SHEET1输入的数据 工作组,按住Shift或Ctrl键,同时选定Sheet1、Sheet2 11、一列中不输入重复数字 [数据]--[有效性]--[自定义]--[公式] 输入=COUNTIF(A:A,A1)=1 如果要查找重复输入的数字 条件格式》公式》=COUNTIF(A:A,A5)>1》格式选红色 12、直接打开一个电子表格文件的时候打不开 “文件夹选项”-“文件类型”中找到.XLS文件,并在“高级”中确认是否有参数1%,如果没有,请手工加上 13、excel下拉菜单的实现 [数据]-[有效性]-[序列] 14、10列数据合计成一列 =SUM(OFFSET($A$1,(ROW()-2)*10+1,,10,1)) 15、查找数据公式两个(基本查找函数为VLOOKUP,MATCH) (1)、根据符合行列两个条件查找对应结果

Excel中关于日期的计算公式的运用

Excel中关于日期的计算公式的运用 方法1:在A1单元格输入前面的日期,比如“2004-10-10”,在A2单元格输入后面的日期,如“2005-6-7”。接着单击A3单元格,输入公式 “=DATEDIF(A1,A2,"d")”。然后按下回车键,那么立刻就会得到两者的天数差“240”。 提示: 公式中的A1和A2分别代表前后两个日期,顺序是不可以颠倒的。此外,DATEDIF函数是Excel中一个隐藏函数,在函数向导中看不到它,但这并不影响我们的使用。 方法2:任意选择一个单元格,输入公式“="2004-10-10"-"2005-6-7"”,然后按下回车键,我们可以立即计算出结果。 计算工作时间——工龄—— 假如日期数据在D2单元格。 =DATEDIF(D2,TODAY(),"y")+1 注意: 工龄两头算,所以加“1”。 如果精确到“天”—— =DATEDIF(D2,TODAY(),"y")&"年"&DATEDIF(D2,TODAY(),"ym")&"月 "&DATEDIF(D2,TODAY(),"md")&"日" 二、计算2003-7-617:05到2006-7-713:50分之间相差了多少天、多少个小时多少分钟 假定原数据分别在A1和B1单元格,将计算结果分别放在C 1、"D1和E1单元格。

C1单元格公式如下: =ROUND(B1-A1,0) D1单元格公式如下: =(B1-A1)*24 E1单元格公式如下: =(B1-A1)*24*60 注意: A1和B1单元格格式要设为日期,C 1、"D1和E1单元格格式要设为常规. 三、计算生日,假设b2为生日 =datedif(B2,today(),"y") DATEDIF函数,除Excel2000中在帮助文档有描述外,其他版本的Excel在帮助文档中都没有说明,并且在所有版本的函数向导中也都找不到此函数。但该函数在电子表格中确实存在,并且用来计算两个日期之间的天数、月数或年数很方便。微软称,提供此函数是为了与Lotus1-2-3兼容。 该函数的用法为“DATEDIF(Start_date,End_date,Unit)”,其中Start_date为一个日期,它代表时间段内的第一个日期或起始日期。End_date为一个日期,它代表时间段内的最后一个日期或结束日期。Unit为所需信息的返回类型。 “Y”为时间段中的整年数,“M”为时间段中的整月数,“D”时间段中的天数。“MD”为Start_date与End_date日期中天数的差,可忽略日期中的月和年。“YM”为Start_date与End_date日期中月数的差,可忽略日期中的日和年。“YD”为Start_date与End_date日期中天数的差,可忽略日期中的年。比如,B2单元格中存放的是出生日期(输入____年__月__日时,用斜线或短横线隔开),在C2单元格中输入“=datedif(B2,today(),"y")”(C2单元格的格式为常规),按回车键

Excel表格公式使用基本操作及excel表格计算公式大全、使用技巧

Excel 部分函数列表. AND “与”运算,返回逻辑值,仅当有参数的结果均为逻辑“真(TRUE)”时返回逻辑“真(TRUE)”,反之返回逻辑“假(FALSE)”。条件判断AVERAGE 求出所有参数的算术平均值。数据计算 COLUMN 显示所引用单元格的列标号值。显示位置 CONCATENATE 将多个字符文本或单元格中的数据连接在一起,显示在一个单元格中。字符合并 COUNTIF 统计某个单元格区域中符合指定条件的单元格数目。条件统计 DATE 给出指定数值的日期。显示日期 DATEDIF 计算返回两个日期参数的差值。计算天数 DAY 计算参数中指定日期或引用单元格中的日期天数。计算天数 DCOUNT 返回数据库或列表的列中满足指定条件并且包含数字的单元格数目。条件统计 FREQUENCY 以一列垂直数组返回某个区域中数据的频率分布。概率计算 IF 根据对指定条件的逻辑判断的真假结果,返回相对应条件触发的计算结果。条件计算 INDEX 返回列表或数组中的元素值,此元素由行序号和列序号的索引值进行确定。数据定位 INT 将数值向下取整为最接近的整数。数据计算 ISERROR 用于测试函数式返回的数值是否有错。如果有错,该函数返回TRUE,反之返回FALSE。逻辑判断 LEFT 从一个文本字符串的第一个字符开始,截取指定数目的字符。截取数据LEN 统计文本字符串中字符数目。字符统计 MATCH 返回在指定方式下与指定数值匹配的数组中元素的相应位置。匹配位置MAX 求出一组数中的最大值。数据计算 MID 从一个文本字符串的指定位置开始,截取指定数目的字符。字符截取 MIN 求出一组数中的最小值。数据计算 MOD 求出两数相除的余数。数据计算 MONTH 求出指定日期或引用单元格中的日期的月份。日期计算 NOW 给出当前系统日期和时间。显示日期时间 OR 仅当所有参数值均为逻辑“假(FALSE)”时返回结果逻辑“假(FALSE)”,否则都返回逻辑“真(TRUE)”。逻辑判断 RANK 返回某一数值在一列数值中的相对于其他数值的排位。数据排序 RIGHT 从一个文本字符串的最后一个字符开始,截取指定数目的字符。字符截取 SUBTOTAL 返回列表或数据库中的分类汇总。分类汇总 SUM 求出一组数值的和。数据计算 SUMIF 计算符合指定条件的单元格区域内的数值和。条件数据计算 TEXT 根据指定的数值格式将相应的数字转换为文本形式数值文本转换 TODAY 给出系统日期显示日期 VALUE 将一个代表数值的文本型字符串转换为数值型。文本数值转换VLOOKUP 在数据表的首列查找指定的数值,并由此返回数据表当前行中指定列处

电子表格日期公式

竭诚为您提供优质文档/双击可除 电子表格日期公式 篇一:excel中几个时间计算公式 假设b2为生日 =datedif(b2,today(),"y") datediF函数,除excel2000中在帮助文档有描述外,其他版本的excel在帮助文档中都没有说明,并且在所有版本的函数向导中也都找不到此函数。但该函数在电子表格中确实存在,并且用来计算两个日期之间的天数、月数或年数很方便。微软称,提供此函数是为了与lotus1-2-3兼容。 该函数的用法为 “datediF(start_date,end_date,unit)”,其中start_date 为一个日期,它代表时间段内的第一个日期或起始日期。end_date为一个日期,它代表时间段内的最后一个日期或结束日期。unit为所需信息的返回类型。 “y”为时间段中的整年数,“m”为时间段中的整月数,“d”时间段中的天数。“md”为start_date与end_date日期中天数的差,可忽略日期中的月和年。“ym”为start_date 与end_date日期中月数的差,可忽略日期中的日和年。“yd”

为start_date与end_date日期中天数的差,可忽略日期中的年。比如,b2单元格中存放的是出生日期(输入年月日时,用斜线或短横线隔开),在c2单元格中输入 “=datedif(b2,today(),"y")”(c2单元格的格式为常规),按回车键后,c2单元格中的数值就是计算后的年龄。此函数在计算时,只有在两日期相差满12个月,才算为一年,假如生日是20xx年2月27日,今天是20xx年2月28日,用此函数计算的年龄则为0岁,这样算出的年龄其实是最公平的。 身份证号提取年龄 =datediF(--text((len(a1)=15)*19”即可获得当时的日期时间; 2、使用公式:用=now()而非=date(),=date()只有日期,然后进行菜单“工具->选项”,选择“重新计算”页,选中“人工重算”,勾不勾选“保存前自动重算”看自己的需要和想法了,如果勾选了,那日期时间那总是最后一次保存的日期时间,不勾选的话,如果你的表格中有公式记得准备存前按F9 篇二:excel函数--时间和日期函数 时间和日期函数 1.date 用途:返回代表特定日期的序列号。

超牛Excel表格公式 excel公式计算

excel公式计算第 1 页共 1 页 Excel表格公式大全, 太强大了 1、查找重复内容公式:=IF(COUNTIF(A:A,A2)>1,"重复","")。2、用出生年月来计算年龄公式:=TRUNC((DAYS360(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,"男","女代表的是输入身份证号码的单元格。"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”1、求和: =SUM(K2:K56) ----对K2到K56这一区域进行求和; 2、平均数: =AVERAGE(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) =COUNTIF(K2:K56,"100") ----求K2到K56区域100分的人数;假设把结果存放于K57单元格;(2) =COUNTIF(K2:K56,">=95")-K57 ----求K2到K56区域95~99.5分的人数;假设把结果存放于K58单元格;(3)=COUNTIF(K2:K56,">=90")-SUM(K57:K58) ----求K2到K56区域90~94.5分的人数;假设把结果存放于K59单元格;(4)=COUNTIF(K2:K56,">=85")-SUM(K57:K59) ----求K2到K56区域85~89.5分的人数;假设把结果存放于K60单元格;(5)=COUNTIF(K2:K56,">=70")-SUM(K57:K60) ----求K2到K56区域70~84.5分的人数;假设把结果存放于K61单元格;(6)=COUNTIF(K2:K56,">=60")-SUM(K57:K61) ----求K2到K56区域60~69.5分的人数;假设把结果存放于K62单元格;(7) =COUNTIF(K2:K56," 第 2 页共 2 页 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="男",IF(G3:G322=1,1,0)))} ----假设C列(C3:C322区域)存放学生的性别,G列(G3:G322区域)存放学生所在班级代码(1、2、3、4、5),则此函数返回的结果表示求一班的男生人数;这是一个数组函数,输完后要按Ctrl+Shift+Enter组合键(产生“{……}”)。“{}”不能手工输入,只能用组合键产生。 14、根据出生日期自动计算周岁:=TRUNC((DAYS360(D3,NOW( )))/360,0) ------假设D列存放学生的出生日期,E列输入该函数后则产生该生的周岁。 15、在Word中三个小窍门:①连续输入三个“~”可得一条波浪线。②连续输入三个“-”可得一条直线。连续输入三个“=”可得一条双直线。一、excel 中当某一单元格符合特定条件,如何在另一单元格显示特定的颜色比如: A1〉1时,C1显示红色 0“条件格式”,条件1设为:公式 =A1=1 2、点“格式”->“字体”->“颜色”,点击红色后点“确定”。条件2设为:公式 =AND(A1>0,A1“字体”->“颜色”,点击绿色后点“确定”。条件3设为:公式 =A1“字体”->“颜色”,点击黄色后点“确定”。 4、三个条件设定好后,点“确定”即出。二、EXCEL中如何控制每列数据的长度并避免重复录入 1、用数据有效性定义数据长度。用鼠标选定你要输入的数据范围,点"数据"->"有效性"->"设置","有效性条件"设成"允许""文本长度""等于""5"(具体条件可根据你的需要改变)。还可以定义一些提示信息、出错警告信息和是否打开中文输入法等,定义好后点"确定"。 第 3 页共 3 页 2、用条件格式避免重复。选定A列,点"格式"->"条件格式",将条件设成“公式=COUNTIF($A:$A,$A1)>1”,点"格式"->"字体"->"颜色",选定红色后点两次"确定"。

Excel中如何计算日期差

Excel中如何计算日期差: ----Excel中最便利的工作表函数之一——Datedif名不见经传,但却十分好用。Datedif能返回任意两个日期之间相差的时间,并能以年、月或天数的形式表示。您可以用它来计算发货单到期的时间,还可以用它来进行2000年的倒计时。 ----Excel中的Datedif函数带有3个参数,其格式如下: ----=Datedif(start_date,end_date,units) ----start_date和end_date参数可以是日期或者是代表日期的变量,而units则是1到2个字符长度的字符串,用以说明返回日期差的形式(见表1)。图1是使用Datedif函数的一个例子,第2行的值就表明这两个日期之间相差1年又14天。units的参数类型对应的Datedif返回值 “y”日期之差的年数(非四舍五入) “m”日期之差的月数(非四舍五入) “d”日期之差的天数(非四舍五入) “md”两个日期相减后,其差不足一个月的部分的天数 “ym”两个日期相减后,其差不足一年的部分的月数 “yd”两个日期相减后,其差不足一年的部分的天数

表1units参数的类型及其含义 图1可以通过键入3个带有不同参数的Datedif公式来计算日期的差。units的参数类型 ----图中:单元格Ex为公式“=Datedif(Cx,Dx,“y”)”得到的结果(x=2,3,4......下同) ----Fx为公式“=Datedif(Cx,Dx,“ym”)”得到的结果 ----Gx为公式“=Datedif(Cx,Dx,“md”)”得到的结果 现在要求两个日期之间相差多少分钟,units参数是什么呢? 晕,分钟你不能用天数乘小时再乘分钟吗? units的参数类型对应的Datedif返回值 “y”日期之差的年数(非四舍五入) “m”日期之差的月数(非四舍五入) “d”日期之差的天数(非四舍五入) “md”两个日期相减后,其差不足一个月的部分的天数 “ym”两个日期相减后,其差不足一年的部分的月数 “yd”两个日期相减后,其差不足一年的部分的天数 假设你的数据从A2和B2开始,在C2里输入下面公式,然后拖拉复制。 =IF(TEXT(A2,"h:mm:ss")

Excel2020时间计算方法详解

Excel2020时间计算方法详解 一、Excel时间计算的基础 和日期一样,Excel处理时间的方式并不像我们看到的那样—— 时间是由小时、分钟和秒钟组成的,有特殊格式的数据。Excel在 内部把每24小时的时间周期作为一个从0至1的小数存储 说明:把A列中的时间数据复制到C列,然后通过“单元格格式”对话框,将C列的格式设置为“常规”即可看到效果。 从中可以看出,每24小时的周期都从午夜零点(0:00:00)开始,存储为0。正午(12:00:00)则存储为0.5,因为从午夜零点至正午恰 好是一个整天的一半。到下一个午夜零点时(0:00:00),Excel把它 存储为1。从上一个午夜零点开始至下一个午夜零点开始,正好经 历了24小时,这就是我们所说的,Excel处理时间的一个24小时 周期。 将时间作为小数存储使得Excel可以对时间进行运算。例如,要 计算两个时间之间的间隔长度,执行结束时间对应的小数减去开始 时间对应的小数的操作即可,具体应用下面会有实例介绍。 二、使用公式快速输入时间 如果需要在工作表中输入很多没有规律的时间系列。假如只用输入A列的数字,然后用Excel公式自动转换成需要得到的C列的时 间格式,就可以大大提高输入的效率。 这个问题所需的公式,和处理快速输入日期的问题相似,下面先来看一下。选中C2单元格,输入公式: =TIME(LEFT(A2,2),MID(A2,3,2),RIGHT(A2,2)) 回车后,再选中C2单元格,用公式填充柄复制公式到恰当单元格,如C10单元格。

这个公式的其余部分,可以参照上一节的“快速输入无规律的日期系列”部分理解。后面的操作也完全类似,可以仿照进行。 三、计算出工作的小时数 在一些类似于考勤表的工作表中,常常需要计算工作的小时数,这也就是前面所提到的,两个时间间隔长度的问题。我们在下面的工作表中进行操作。 选中B5单元格,输入公式:=(B4-B3)*24 回车后,显示计算结果8.25,即张路路7月1日的工作时间为8.25小时。为什么要在(B4-B3)之后乘以24呢?前面讲过,Excel在内部将每24小时的时间周期作为一个从0至1的小数存储,所以(B4-B3)得到的只是这两个时间对应的小数差值,这个差值再乘以24就可以让计算结果的单位转换为小时。由此可见,理解了Excel 处理时间的规律,有助于理解公式的含义。 然后选中B5单元格,用公式填充柄复制公式到G5单元格即可。 四、计算出考试的分钟数 上面一个实例,计算两个时间的差值,我们希望计算结果的单位为小时。那么,假如我们希望计算结果的单位为分钟,该用什么公式呢?下面用计算考试的分钟数来说明。 工作表中,我们要计算出每科考试的时间,放到D4:D11单元格中,单位为分钟。 处理的方法有两种,包括不使用函数的方法和使用函数的方法。 1、不使用函数的方法 选中D2单元格,输入公式:=(C4-B4)*24*60 2、使用函数的方法 选中D2单元格,输入公式:=CONVERT((C4-B4)*24,"hr","mn") 回车后可以看到得到的计算结果仍为90。

Excel计算公式 excel公式计算

excel公式计算 Excel计算公式 1. Excel2003最大值,最小值,求和,平均值等 A1写“最大值”,A2公式=MAX(E3:E100) B1写“最小值”,B2公式=MIN(E3:E100) C1写“求和”,C2公式=SUM(E3:E100) D1写“平均值”,D2公式=AVERAGE(E3:E100) 2.在excel表格中不同日期显示不同颜色例如,想再每个月日期为奇数时显示红色,偶数时显示绿色,就是隔天一个颜色,格式----条件格式----公式:=MOD(A1,2)=1,设置单元格格式背景为红色----确定; 3. excel 下拉填充序号 01 02 03 04 05 =TEXT(ROW(A1),"00) 4. EXCEL隔行求和(如从O7单元格开始,到05000,后面每隔16行开始求和)=IF(MOD(ROW()-6,16),"",SUM(OFFSET($O$7,ROW()-22,,16,))) 5. ExcelIf函数有sheet1和sheet2两个页,其中有A,B,C列.A列是人名,B2是语文成绩,B3为数学成绩C为分数. 第一页sheet1已经是填好的表格。现在需要做的是:假如sheet2的A列的人名与sheet1的A列的人名相同,再把“语文成绩”或“数学成绩”输入,则把对应B列的成绩引入到sheet2的C列中来。 =VLOOKUP(A1,SHEET1!A1:C100,2,0) 6. EXCEL里设置时间相加(还是时间)例如在A单元格中输入10:30,在B单元格中输入0:30 - 1 - c输入公式=SUM(A1:B1)C列设置单元格格式为的时间格式或自定义格式h:mm 时间相加,不超过24小时,如果是25就会变成1:00 7. excel函数例如:A1是2011-02-02,在B1显示2012-02-01,如果A1是2011-02-01,就必须显示2012-01-31. B1中输=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)-1) 单元格A1是1*150*250,怎样在B1单元格中显示为1,B2为150,B3为250 数据----分列----分隔符号:*----确定! 9.在EXCEL里一个格子里面输入DK123+000~002,拖动时000~002,两个数字可以分别以定值增加!="DK123+"&680+(row(a1)-1)*3&"~"&680+row(a1)*3 10. excel在一组数字前全部加一个符号比如一列数字12,23,54,54,65,,,,,我要在全部加个符号A,变成 A12,A23,A54,A54,A65,,,, 假设A列是12,23,34.....,需要在B列完成你所要的结果,那么在B列的第一行的单元格内键入:=CONCATENATE("A",A1) 中,一个百分数,在大于等于100%的情况下,得满分4分,小于50%得0分,但大于等于50%小于100%情况下=IF(A1>1,4,MAX(0,MIN((A1-0.5)*150,20))) 12. excel用函数提取某个符号前的字符串?比如A1=复合PVC-85/1.5,在B1用什么函数提取“/”之前的值,就是提取出“复合PVC-85”?=LEFT(A1,SEARCH("/",A1)-1) 13. EXCEL自动输出某一范围的随机数值,比如A1到E99,全部自己输出1.10-1.99的随机数 =TEXT(RAND()*(1.99-1.10)+1.10,"0.00")*1 14. EXCEL排序例如 326 我想要结果是236 ;353我想要结果是335 - 2 - =MIN(MID(TEXT(B5,"000"),{1,2,3},1)*1)&LARGE(MID(TEXT(B5,"000"),{1,2,3},1)*1,2)&M AX(MID(TEXT(B5,"000"),{1,2,3},1)*1) 15.用excel求和后等于11.5,总是12 一:把单元格拉大点看看是否有变化?二:看看是否设置了小数点位数为0,工具----选项----编辑----自动设置小数点数为0的勾取掉?三:是否你的公式使用了ROUND或者ROUNDUP函数。 16. Excel表格问题:假设表1有1000个数据,表2有5000个数据,怎样才能知道表1中的数据在表2中有没有出现,有哪些数据出现过?假设在A列,B列输入:=IF(COUNTIF(SHEET2!A:A,A1)>0,"有","") 然后对B列进行非空筛选! 17. EXCEL设置在 A单元格输入30,在B单元格要变成0:30 =INT(A1/60)&":"&MOD(A1,60) 18. Excel判断比如一个表中,A为各分店商品名称,B为库存量,然后想做一个表,由各分店填好后,复制在一个计表中,一个公式就能统计出各种商品的库存总和? =SUMIF($A:$A,"a*",$B:$B) 19. excel中有3列数字分别表示时分秒,合并到一列里? =TEXT(A1&":"&B1&":"&C1,"H:MM:SS") 20. EXCEL里,比如:2个,26箱,50根,136台,去掉后面的字,只要数字?=LEFT(A1,LENB(A1)-LEN(A1) 21. excel 三列穿插变一列在EXCEL表中,如 1 2 3 4 5 6 7

Excel函数计算公式大全(完整)

Excel函数计算公式大全(完整) Excel函数计算公式大全(完整) 篇一 一、数字处理 1、取绝对值=ABS(数字) 2、取整=INT(数字) 3、四舍五入=ROUND(数字,小数位数) 二、判断公式 1、把公式产生的错误值显示为空 公式:C2=IFERROR(A2/B2,"") 说明:如果是错误值则显示为空,否则正常显示。 2、IF多条件判断返回值 公式:C2=IF(AND(A2 说明:两个条件同时成立用AND,任一个成立用OR函数。 三、统计公式 1、统计两个表格重复的内容 公式:B2=COUNTIF(Sheet15!A:A,A2) 说明:如果返回值大于0说明在另一个表中存在,0则不存在。 2、统计不重复的总人数 公式: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) 说明:如果标题行没有规则用第2个公式 2、单条件求和 公式:F2=SUMIF(A:A,E2,C:C) 说明:SUMIF函数的基本用法 3、单条件模糊求和 公式:详见下图 说明:如果需要进行模糊求和,就需要掌握通配符的使用,其中星号是表示任意多个字符,如"*A*"就表示a前和后有任意多个字符,即包含A。 4、多条件模糊求和 公式:C11=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11) 说明:在sumifs中可以使用通配符* 5、多表相同位置求和 公式:b2=SUM(Sheet1:Sheet19!B2) 说明:在表中间删除或添加表后,公式结果会自动更新。 6、按日期和产品求和 公式:

Excel电子表格计算公式使用方法25条公式技巧

常用电子表格公式 一、利用函数COUNTA统计本班的应考人数(总人数) 因为函数COUNTA可以计算出非空单元格的个数,所以我们在利用此函数时,选取本班学生名字所在单元格区域(B3~B12)作为统计对象,就可计算出本班的应考人数(总人数)。 二、利用COUNT、COUNTBLANK和COUNTIF函数分别统计各科参加考试的人数、统计各科缺考人数、统计各科各分数段的人数 我们在输入成绩时,一般情况下,缺考的人相应的科目的单元格为空就可以了,是0分的都输入0。 1、查找重复内容公式:=IF(COUNTIF(A:A,A2)>1,"重复","")。 2、用出生年月来计算年龄公式:=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。 3从输入的18位身份证号的出生年月计算公式: 格式为2012/4/21的=CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。格式为20120421的=CONCATENATE(MID(E4,7,4),MID(E4,11,2),MID(E4,13,2)) 4、从输入的身份证号码内让系统自动提取性别,可以输入以下公式:

=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表的是输入身份证号码的单元格。 1、求和: =SUM(K2:K56) ——对K2到K56这一区域进行求和; 2、平均数: =AVERAGE(K2:K56) ——对K2 K56这一区域求平均数; 3、排名: =RANK(K2,K$2:K$56) ——对55名学生的成绩进行排名;或 =RANK(E2,$E$2:$E$5),但遇到同分数时排名不连续。 用下面的几个公式都可以获得中国式排名,还是以上图为例,在单元格中输入公式并向下拖: =SUMPRODUCT((D$2:D$19>D2)*(1/(COUNTIF(D$2:D$19,D$2:D $19))))+1 或下面的几个数组公式,公式输入完毕后按Ctrl+Shift+Enter结束: =SUM(IF(D$2:D$19>D2,1/COUNTIF(D$2:D$19,D$2:D$19)))+1 =SUM(--(FREQUENCY(D$2:D$19,IF(D$2:D$19>=D2,D$2:D$19))>0)) =SUM(--IF(D$2:D$19>=D2,MATCH(D$2:D$19,D$2:D$19,)=ROW( $2:$19)-1)) 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名学生)的最低分;

excel中计算日期差工龄生日等方法

excel中计算日期差工龄生日等方法 方法1:在A1单元格输入前面的日期,比如“2004-10-10”,在A2单元格输入后面的日期,如“2005-6-7”。接着单击A3单元格,输入公式“=DATEDIF(A1,A2,"d")”。然后按下回车键,那么立刻就会得到两者的天数差“240”。 提示:公式中的A1和A2分别代表前后两个日期,顺序是不可以颠倒的。此外,DATEDIF 函数是Excel中一个隐藏函数,在函数向导中看不到它,但这并不影响我们的使用。 方法2:任意选择一个单元格,输入公式“="2004-10-10"-"2005-6-7"”,然后按下回车键,我们可以立即计算出结果。 计算工作时间——工龄—— 假如日期数据在D2单元格。 =DA TEDIF(D2,TODAY(),"y")+1 注意:工龄两头算,所以加“1”。 如果精确到“天”—— =DA TEDIF(D2,TODAY(),"y")&"年"&DATEDIF(D2,TODAY(),"ym")&"月"&DATEDIF(D2,TODAY(),"md")&"日" 二、计算2003-7-617:05到2006-7-713:50分之间相差了多少天、多少个小时多少分钟 假定原数据分别在A1和B1单元格,将计算结果分别放在C1、D1和E1单元格。 C1单元格公式如下: =ROUND(B1-A1,0) D1单元格公式如下: =(B1-A1)*24 E1单元格公式如下: =(B1-A1)*24*60 注意:A1和B1单元格格式要设为日期,C1、D1和E1单元格格式要设为常规. 三、计算生日,假设b2为生日

=datedif(B2,today(),"y") DA TEDIF函数,除Excel2000中在帮助文档有描述外,其他版本的Excel在帮助文档中都没有说明,并且在所有版本的函数向导中也都找不到此函数。但该函数在电子表格中确实存在,并且用来计算两个日期之间的天数、月数或年数很方便。微软称,提供此函数是为了与Lotus1-2-3兼容。 该函数的用法为“DA TEDIF(Start_date,End_date,Unit)”,其中Start_date为一个日期,它代表时间段内的第一个日期或起始日期。End_date为一个日期,它代表时间段内的最后一个日期或结束日期。Unit为所需信息的返回类型。 “Y”为时间段中的整年数,“M”为时间段中的整月数,“D”时间段中的天数。“MD”为Start_date与End_date日期中天数的差,可忽略日期中的月和年。“YM”为Start_date与End_date日期中月数的差,可忽略日期中的日和年。“YD”为Start_date与End_date日期中天数的差,可忽略日期中的年。比如,B2单元格中存放的是出生日期(输入年月日时,用斜线或短横线隔开),在C2单元格中输入“=datedif(B2,today(),"y")”(C2单元格的格式为常规),按回车键后,C2单元格中的数值就是计算后的年龄。此函数在计算时,只有在两日期相差满12个月,才算为一年,假如生日是2004年2月27日,今天是2005年2月28日,用此函数计算的年龄则为0岁,这样算出的年龄其实是最公平的。 本篇文章来源于:实例教程网(https://www.sodocs.net/doc/88882484.html,) 原文链接:https://www.sodocs.net/doc/88882484.html,/bgruanjian/excel/631.html

Excel的SUM函数9种公式设置范例(完整资料).doc

【最新整理,下载后即可编辑】 Excel的SUM函数9种公式设置范例 1、数组求和:{=SUM((G12:G21>100)*G12:G21)} [公式说明]:本公式为数组公式,可以对G12:G21区域中大于100的数据进行求和,而排除小于等于100的数据。输入公式时必须按【Ctrl+Shift+Enter】组合键结束,否则无法得到正确结果。 [使用注意]:1、公式中"G12:G21>100"部分表示求和条件,后跟实际求和区域"G12:G21"。如果有多个条件,可一并罗列出来。例如求大于100且小于115的数据之和,公式如下:=SUM(G12:G21>100)*(G12:G21<115)*G12:G21)。2、此数组公式只适用于单个区域求和,如果有多个区域,只能用多个SUM 求和,然后相加。例如对G12:G21和H12:H21区域中大于100的数汇总,公式如下:=SUM(SUM((G12:G21>100)*G12:G21),SUM((H12:H21>100)*H1 2:H21))。3、对于SUM函数的数组公式,可以用SUMPRODUCT 函数来代替,从而将数组公式转换成普通公式。例如本案例的公式用SUMPRODUCT函数后,普通公式如下:=SUMPRODUCT((G12:G21>100)*G12:G21)。 2、数据类型转换求和:=SUM(VALUE(H5),H6:H10,J5:J10,L5:L10);=SUM(--(H5),H6:H10,J5:J10,L5:L10);=SUM((H5)*1,H6:H10,J5:J10,L5:L10);=SUM((H5)/1,H6:H10,J5:J10,L5:L10) [公式说明]:SUM函数用于对单元格区域的数据或者逻辑值、表达式进行求和,它有1-255个参数。鉴于本题的特殊性,公式也可以改为"=SUM(区域1)",函数会忽略区域中的文本。 [使用注意]:1、SUM函数有1-255个参数。参数可以是区域,也

相关主题