> 馆藏中心

【原】Excel案例精解:TEXT函数巧算加班时间换来的约会

来源:用户 我爱极客 收藏 编辑:张晓华

小陆正在网上查一些关于精致生活的用品的时候,突然觉的左肩一沉,转头一看没人,正想继续时,右肩又一沉,正想发火说:谁啊,这么讨厌,打扰人家工作!可看到是谁后,瞬间笑脸相迎说:霉霉,什么事啊,你看看你有什么想要吗?我买给你啊!

霉霉:别说买东西了,哪有心情啊,领导又给我出难题啦!

小陆:开始都这样,走,去你哪吧,边走边说是什么问题吧?

霉霉:嗯,领导让我统计员工的总的加班时间?要求<30分,不算加班,>=30且<60的算30分钟,>=60且<90的算60,依次类推,这怎么计算啊?

小陆:我给你讲的日期的部分的函数,也没有系统的给你说日期的时间的知识,你不会很正常啊!打开你的发给你的表格,让我看看都是有什么内容?

霉霉打开了发来的表,内容如下:


公司员工的考勤信息表

小陆一边在E列创建的加班时间,接着输入公式:=ROUND(FLOOR(D2-(TEXT(D2,"yyyy/m/d")&" 17:29:59"),"0:30")/TEXT("1:00:00","0.000000"),3),然后拖拽完成公式填充。

霉霉:这个公式里我们就认识3,4个函数,其他的函数都是干什么的?之前你的写的吧,我还能看明白,今天的这个公式彻底蒙圈啦!你赶紧跟我讲讲吧。说着霉霉已经拿出小本本,准备好记笔记啦!

小陆:我先简单说一下思路:咱们正常的下班为17:30,我们用下班打卡的时间减去正常下班的时间,求出时间差,然后与30分比较,是30分钟整倍数的返回整倍数时间,然后再将分钟转化成小时数(方便日后求计算加班费)。

霉霉连连点头:嗯嗯,公式中我唯一能看懂就是D2使用引用的下班打卡的时间,其他就看不懂,你仔细讲解一下公式吧。

小陆:TEXT函数的功能是将字符串按设定的格式编码显示,我使用这个功能只截取了打卡的当天日期然后拼接下班时间,组成当天的下班日期时间串,注意在拼接时间的时候,一定要在时间字符串前加个空格,否则会导致无法识别为日期时间格式,也就无法计算,然后用打卡时间与之相减,得出加班时间。

TEXT文本函数使用语法结构图

霉霉疑惑的说:哪(TEXT(D2,"yyyy/m/d")&" 17:29:59")最终会得出什么结果呢?

小陆:执行后的结果:2019/6/1 17:29:59,时间日期字符串,这个结果可以直接与日期格式计算的。

霉霉:为什么是17:29:59啊?不应该是17:30:00吗?

小陆:这个跟公式中的floor函数有关,它的功能是向下取整,它有两个必填参数,第一个处理的数字,第二个参数为倍数,只保留最大整倍数的数字,比如floor(1,0.3)=0.9、语法结构图如下:

FLOOR函数语法结构图

正是利用floor这个特性,正好符合公司定的30分钟的规则,所以公式就可以这么写:=floor(D2-TEXT(D2,"yyyy/m/d")&" 17:30:00"),"0:30:00"),会出现18:00:00打卡时,公式计算为0,为了纠正需要将公式变为=floor(D2-TEXT(D2,"yyyy/m/d")&" 17:29:59"),"0:30:00"),这个问题可以调整也可以不调整,因为打卡的时间不太可能整分整秒。

霉霉:哪是为什么呢?怎么会出现这种情况呢?

小陆:由于时间30分是个无限循环的小数,因为Excel的精度问题,不过还好精度误差在1-2秒,这也是我为什么将公式提前1秒就能解决精度导致的误差问题,最后我们需要将结果转化成小时并保留2位小数即=round(floor(D2-TEXT(D2,"yyyy/m/d")&" 17:29:59"),"0:30:00")/text("1:00","0.000000"),2),最后加一个严谨性判断iferror就搞定啦!

霉霉:round函数也是小数保留函数吗?

小度:没有错,它是符合四舍五入的规则的,第二个参数就是保留几个小数位标志。

ROUND函数使用语法结构图

一个爱讲,一个爱听,不知什么两人背后多了两个人都不知道,不用说你也猜到了,正是小皮和小度。

小度接着说:陆儿啊,你的公式有问题,你怎么能舍近求远呢,计算时间还要带上年月日呢,者只需要计算小数部分就好啦,看我的:=ROUND(FLOOR(MOD(D4,1)-TEXT("17:30:00","0.000000"),"0:29:59")/TEXT("1:00","0.000000"),2),怎么样,这回我的写法没有问题吧!

小陆:嗯,是没有问题,我是因为怕将太多了,霉霉记不住,所以没有讲这种方法。然后转过头去,问小皮:你是不是也有什么要说的啊?

小皮:当然,我看见你俩吧,公式写的不错,就是操作太初级了,怎么还拖拽填充公式呢?我来教你们一手,鼠标停到单元格右下角,当变成实心的+时,双击就能完成整列有内容部分的填充!

小陆笑了笑,小声告诉霉霉:赶紧记下来吧,这个不错!

霉霉:第二个公式我不太明白呢?

小陆:你先记下来,回来我给你再讲,今天讲的东西挺多了,你回去消化消化吧!

小皮和小度都觉得很尴尬,小皮说:忘了忘了,我的文案还没有写完了!

小度说:嗯嗯,我也有点还没有弄,我得走了!

这两人就这样灰溜溜的走了,小陆说:等过这段时间,我好好给补补Excel啊!

霉霉:好啊好啊,哪是去我家,还是你家?此处省略1万字。

加班时间效果图

(我是喜欢用故事聊excel的我爱极客的小胖,欢迎你在下方留言,把你遇到问题告诉我们,我们看到后,会第一时间回复。在工作的路上你并不孤单,我们一起同行!)

赞赏 共11人赞赏

这本书对没有基础的童鞋,学习obejctive-C,有多大意义?

本书主要特色: (1)使用Alice引入面向象编程本书配套网站YouTube视频均Alice载说明简单编程示例本书作者Gary Bennett仅具发经验位色培训师所创办xcelMe.com培训优秀iPh...

ACCESS 2007 BXCEL 2007 WORD 2007是教程文件

是 Microsoft Office 2007 的成员之一。 Access能够存取 Access/Jet、Microsoft SQL Server、Oracle,或者任何 ODBC 兼容数据库内的资料。熟练的软件设计师和资料分析师利...

如何在原有Excel2010图表中添加系列

答:一、首先,打开Excel程序,进入Excel程序主界面。 二、然后,然后将要添加系列的图表插入表格中。 三、然后,在窗口中表格下一行添加数据。 四、然后,选中该行,在右键菜单中选择“复制”,点击打开。 五、然后,选择键盘上的“ctrl”键并点击图表...

在EXCEL中如何把求和得到的数据把原公式去掉而保留...

答:复制公式所在单元格 右点击之-选择性粘贴-数值 然后删除2,3所在单元格

Excel 排序后如何恢复至原有顺序

答:Excel 排序后恢复至原有顺序的具体操作步骤如下: 1、排序之后只要没有进行保存,可以使用快捷键ctrl+Z来进行操作撤回,回到原来的排列顺序。 2、在排序前加一列序号辅助列,如图,在右边按顺序进行排列。这就是表格排序的最初始的状态。 3、不...

在Excel表格中,在复制后,如何保持原有行距、列宽?

答:1、首先,选中原始表格的A1:D5区域,粘贴到新表格中,这时行高列宽都变化了。 2、用鼠标选中原始表格的所有行,点击一下【格式刷】。 3、到新表格中用格式刷刷一下粘贴数据的所有行,行高就复制过来了。 4、同样的方法,选择原始表格的整列,然...

Excel文件被同名的Excel文件覆盖后如何找回原Excel...

答:被同名的Excel文件覆盖之后是没法进行恢复原来的Excel文件。 避免文件被覆盖的方法: 将重要的文件进行备份(如:存放于硬盘上面的其他文件夹、U盘、网盘等),这样一旦被覆盖或丢失,那么还是可以找回; 在文件被覆盖之前都会有提示的,选择“取...

EXCEL表格中怎么去掉原有的虚线

答:材料/工具:Excel2010 1、点击左上方的开始,出现了软件的设置对话框 2、在设置对话框中,点击“Excel选项”,选择高级的选项 3、在新出现的“Excel选项”的对话框中,点击高级,在右侧就出现了“编辑项目”、“复制和黏贴”、“打颖等很多有关excel基本...

怎样将Word中的表格复制到Excel中还保持原有内容和...

答:准备工具:office2010 1.在桌面中找到需要进行修改的word的表格,鼠标左键双击打开该表格。 2.在打开的word文档中找到要复制的表格,鼠标左键选中该表格。 3.选中完毕后右键调出功能框,在功能框点击复制选项留作备用,如下图所示。 4.在桌面中...

Excel工作表会变成了快捷方式,要怎么才能恢复原有...

答:1、在下图中可以看到该excel表格是快捷方式。 2、选中该excel表格,点击鼠标右键,在弹出的选项中点击“属性”。 3、在弹出的“属性”对话框中可以看到“目标”后面就是该工作表的原有文件所在位置。 4、在对话框的下方,点击“打开文件位置”。 5、系统...

如何把Word转化成Excel并保留原格式

答:1、打开word,点击【文件】 2、点击【另存为】——【浏览】 3、将【保存类型】修改成【网页】 4、保存好该文件 5、打开Excel,点击左侧顶部的【文件】 6、【打开】——【浏览】选项 7、从弹出的窗口找到刚刚保存好的html文件,点击【打开】 8、word...

Excel EXCEL内复制后粘贴,如何保留原有的格式

答:准备工具/材料:装有windows 10的电脑一台,Microsoft Office 家庭和学生版 2016 excel软件。 1、首先,在第一个excel表格的单元格中输入数值或其他字符,并格式化单元格。 2、选择单元格后,按“Ctrl+C”执行复制操作。 3、然后打开新的excel表格...

本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。本文《【原】Excel案例精解:TEXT函数巧算加班时间换来的约会》由网友我爱极客 转载收藏,版权归原作者所有,若侵权请联系:E-MAIL:513175919@qq.com
违法违规信息请立即联系本网可获得现金奖励,TEL:1-8-2-1-0-2-3-3-3-8-1(电话仅供违法违规信息举报,侵权类信息请EMAIL。)

www.wosoni.com false 互联网 http://www.wosoni.com/view/socangkudk/mkdm/dm/czlszgjjWSNlcmdckcsk.html report 22737

热门图片

经济金融企业管理法律法规社会民生科学教育健康生活体育运动文化艺术电子数码电脑网络娱乐休闲行政地区心理分析医疗卫生