EXCEL实用技巧培训.ppt

EXCEL实用技巧培训 系列一 轻松运用Excel日期计算 上海交通大学安泰经济与管理学院人力资源部 主要内容 主要内容 1、显示当前日期 1、显示当前日期 使用函数TODAY 插入→函数→日期与时间→TODAY 1、显示当前日期 设置日期显示的格式 格式→单元格格式 2、使用公式快速输入日期 2、使用公式快速输入日期 快速输入有规律的日期 先在工作表的A2单元格中输入日期“2004-1-1” 然后在A3单元格输入公式： =DATE(YEAR(A2),MONTH(A2)+1,DAY(A2)) ， 并回车确认 2、使用公式快速输入日期 快速输入无规律的日期 2007-4-2或 2007/4/2 2、使用公式快速输入日期 在A列输入日期数字，不要输入连接符号， 在C列进行转换——选中C2单元格，输入公式：=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))； 回车后再选中C2单元格，然后用公式填充柄复制公式到恰当单元格； 2、使用公式快速输入日期 把C2:C5中的数据复制后，直接选中A2单元格，执行“选择性粘贴”命令 ,选择“数值”，确定； 调整A2：A5的显示格式为日期； 删除C列数据 3、计算日期对应的星期数 在日期一列输入日期，如“2007-4-21”,并将格式设置为日期格式； 在B2单元格中输入公式： =CHOOSE(WEEKDAY(A2,2),“星期一”,“星期二”,“星期三”,“星期四”,“星期五”,“星期六”,“星期日”) 4、计算两个日期之间的年数 在B5单元格中输入公式： =YEAR(B3)-YEAR(B2) 5、计算两个日期之间的天数 回车后得到计算答案： 5、计算两个日期之间的天数 将C2的单元格格式设置为“常规”即可 6、计算两个日期之间工作日的天数 NETWORKDAYS函数 例，假如项目经理2006年7月7日接到一个新项目，要求2007年4月22日完成，他需要计算一下这两个日期之间有多少个工作日，以便安排工作的进度。 6、计算两个日期之间工作日的天数 安装分析工具库 选择菜单“工具→加载宏”命令，打开“加载宏”对话框，勾选“可用加载宏”下方列表框内的“分析工具库”复选框，单击“确定”按钮完成安装。 6、计算两个日期之间工作日的天数 NETWORKDAYS (start_date, end_date, holidays) 将单元格格式设置为“常规”即可： 6、计算两个日期之间工作日的天数 将期间的非周末的节假日排除，输入公式： =NETWOEKDAYS（A3，B3，A6：A12） 7、计算若干年、月、日以后的日期 DATE函数 例，计划用1年2个月零15天来完成一本图书的编写工作，开工日期为2004年7月28日，那么什么时候能完成图书的编写呢？ 7、计算若干年、月、日以后的日期 在B8单元格中输入公式： =DATE(YEAR(B2)+B4,MONTH(B2)+B5,DAY(B2)+B6) 8、计算年龄 方法一：在B5中输入公式： =INT（（B3-B2）/365） 方法二：在B5中输入公式： =INT(YEARFRAC(B2,B3,3)) YEARFRAC (start_date, end_date, basis) basis为日计数基准类型，如果取3则日计数基准为实际天数/365 9、常用日期函数总结 取出当前系统/日期 主要使用函数NOW、TODAY 取得日期/时间的部分字段值 主要使用函数HOUR、DAY、MONTH、YEAR 例，需要返回2006-5-30 12：30PM的年份、月份、日期及小时数，可以分别采用以下函数实现。 YEAR（B2）=2006 MONTH（B2）=5 DAY（B2）=30 HOUR（B2）=12 10、从身份证号码中提取出生年月 身份证号码的含义： 15位的身份证：1——6位为地区代码，7——8位为出生年份（2位），9——10位为出生月份，11——12位为出生日期，13——15位为顺序号，并能够判断性别，奇数为男，偶数为女。例，330101781206002 18位的身份证：1——6位为地区代码，7——10位为出生年份（4位），11——12位为出生月份，13——14位为出生日期，15——17位为顺序号，并能够判断性别，奇数为男，偶数为女，18位为校验位。例，330101197812060