- ?
EXCEL使用中必须要注意的常见问题
Santa
展开
EXCEL使用问题汇总
01、如何在已有的单元格中批量加入一段固定字符?
例如:在单位的人事资料,在excel中输入后,由于上级要求在原来的职称证书的号码全部再加两位,即要在每个人的证书号码前再添上两位数13,如果一个一个改的话实在太麻烦了,那么我们可以用下面的办法,省时又省力:
1)假设证书号在A列,在A列后点击鼠标右键,插入一列,为B列;
2)在B2单元格写入: ="13" & A2 后回车;
3)看到结果为 13xxxxxxxxxxxxx 了吗?鼠标放到B2位置,单元格的下方不是有一个小方点吗,按着鼠标左键往下拖动直到结束。当你放开鼠标左键时就全部都改好了。 若是在原证书号后面加13 则在B2单元格中写入:=A2 & “13” 后回车。
02、如何设置文件下拉窗口的最下面的最近运行的文件名个数?
打开“工具”,选“选项”,再选“常规”,在“最近使用的文件清单”下面的文件个数输入框中改变文件数目即可。若不在菜单中显示最近使用的文件名,则将“最近使用的文件清单”前的复选框去掉即可。
03、在EXCEL中输入如“1-1”、“1-2”之类的格式后它即变成1月1日,1月2日等日期形式,怎么办?
这是由于EXCEL自动识别为日期格式所造成,你只要点击主菜单的“格式”菜单,选“单元格”,再在“数字”菜单标签下把该单元格的格式设成文本格式就行了。
04、在EXCEL中如何使它象WORD一样的自动定时保存文件?
点击“工具”菜单“自动保存”项,设置自动保存文件夹的间隔时间。如果在“工具”菜单下没有“自动保存”菜单项,那么执行“工具”菜单下“加载宏...”选上“自动保存”,“确定”。然后进行设置即可。
05、用Excel做多页的表格时,怎样像Word的表格那样做一个标题,即每页的第一行(或几行)是一样的。但是不是用页眉来完成?
在EXCEL的文件菜单-页面设置-工作表-打印标题;可进行顶端或左端标题设置,通过按下折叠对话框按钮后,用鼠标划定范围即可。这样Excel就会自动在各页上加上你划定的部分作为表头。
06、在Excel中如何设置加权平均?
加权平均在财务核算和统计工作中经常用到,并不是一项很复杂的计算,关键是要理解加权平均值其实就是总量值(如金额)除以总数量得出的单位平均值,而不是简单的将各个单位值(如单价)平均后得到的那个单位值。在Excel中可设置公式解决(其实就是一个除法算式),分母是各个量值之和,分子是相应的各个数量之和,它的结果就是这些量值的加权平均值。
07、如果在一个Excel文件中含有多个工作表,如何将多个工作表一次设置成同样的页眉和页脚?如何才能一次打印多个工作表?
把鼠标移到工作表的名称处(若你没有特别设置的话,Excel自动设置的名称是“sheet1、sheet2、sheet3.......”),然后点右键,在弹出的菜单中选择“选择全部工作表”的菜单项,这时你的所有操作都是针对全部工作表了,不管是设置页眉和页脚还是打印你工作表。
08、EXCEL中有序号一栏,由于对表格进行调整,序号全乱了,可要是手动一个一个改序号实在太慢太麻烦,用什么方法可以快速解决?
如果序号是不应随着表格其他内容的调整而发生变化的话,那么在制作EXCEL表格时就应将序号这一字段与其他字段分开,如在“总分”与“排名”之间空开一列,为了不影响显示美观,可将这一空的列字段设为隐藏,这样在调整表格(数据清单)的内容时就不会影响序号了。
09、用Excel2000做成的工资表,只有第一个人有工资条的条头(如编号、姓名、岗位工资.......),想输出成工资条的形式。怎么做?
这个问题应该这样解决:先复制一张工资表,然后在页面设置中选中工作表选项,设置打印工作表行标题,选好工资条的条头,然后在每一个人之间插入行分页符,再把页长设置成工资条的高度即可。 使用自定义方式重装了一遍中文office,Excel的打印纸选项中只有A4一种,怎么办? 随便安装一个打印机驱动程序就可以了。
10、在Excel中小数点无法输入,按小数点,显示的却是逗号,无论怎样设置选项都无济于事,该怎么办?
这是一个比较特殊的问题,我曾为此花了十几个小时的时间,但说白了很简单。在Windows的控制面板中,点击“区域设置”图标,在弹出的“区域设置属性”对话面板上在“区域设置”里选择“中文(中国)”,在“区域设置属性”对话面板上在“数字”属性里把小数点改为“.”(未改前是“,”),按“确定”按钮结束。这样再打开Excel就一切都正常了。
11、如何快速选取特定区域?
使用F5键可以快速选取特定区域。例如,要选取A2:A1000,最简便的方法是按F5键,出现“定位”窗口,在“引用”栏内输入需选取的区域A2:A1000。
12、如何快速返回选中区域?
按Ctr+BacksPae(即退格键)。
13、如何快速定位到单元格?
方法一:按F5键,出现“定位”对话框,在引用栏中输入欲跳到的单元格地址,单市“确定”按钮即可。
方法二:单击编辑栏左侧单元格地址框,输入单元格地址即可。
14、“Ctrl+*”的特殊功用
一般来说,当处理一个工作表中有很多数据的表格时,通过选定表格中某个单元格,然后按下 Ctrl+* 键可选定整个表格。Ctfl+* 选定的区域是这样决定的:根据选定单元格向四周辐射所涉及到的有数据单元格的最大区域。
15、如何快速选取工作表中所有包含公式的单元格?
有时,需要对工作表中所有包含公式的单元格加以保护,或填入与其他单元格不同的颜色,以提醒用户注意不能在有此颜色的区域内输入数据。以下方法可以帮助快速选取所有包含公式的单元格:选择“编辑”“定位”,单击“定位条件”按钮,在“定位条件”对话框中选择“公式”项,按“确定”按钮即可。
16、如何在不同单元格中快速输入同一数内容?
选定单元格区域,输入值,然后按 Ctrl+ Ener键,即可实现在选定的单元格区域中一次性输入相同的值。
17、只记得函数的名称,但记不清函数的参数了,怎么办?
如果你知道所要使用函数的名字,但又记不清它的所有参数格式,那么可以用键盘快捷键把参数粘贴到编辑栏内。
具体方法是:在编辑栏中输入一个等号其后接函数名,然后按 Ctr+ A键,Excel则自动进入“函数指南——步骤 2之2”。当使用易于记忆的名字且具有很长一串参数的函数时,上述方法显得特别有用。
18、如何把选定的一个或多个单元格拖放至新的位置?
按住Shift键可以快速修改单元格内容的次序。
具体方法是: 选定单元格,按下Shift键,移动鼠标指针至单元格边缘,直至出现拖放指针箭头(空心箭头),然后按住鼠标左键进行拖放操作。上下拖拉时鼠标在单元格间边界处会变为一个水平“工”状标志,左右拖拉时会变为垂直“工”状标志,释放鼠标按钮完成操作后,选定的一个或多个单元格就被拖放至新的位置。
19、如何让屏幕上的工作空间变大?
可以将不用的工具栏隐藏,也可以极大化Excel窗口,或者在“视图”菜单中选择“全屏显示”命令。
20、如何使用快显菜单?
快显菜单中包括了一些操作中最常用的命令,利用它们可以大大提高操作效率。首先选定一个区域,然后单击鼠标右健即可调出快显菜单,根据操作需要选择不同命令。
21、如何使用快显菜单?
22、如何防止Excel自动打开太多文件?
当Excel启动时,它会自动打开Xlstart目录下的所有文件。当该目录下的文件过多时,Excel加载太多文件不但费时而且还有可能出错。解决方法是将不该位于Xlstart目录下的文件移走。另外,还要防止EXcel打开替补启动目录下的文件:选择“工具”“选项”“普通”,将“替补启动目录”一栏中的所有内容删除。
23、如何去掉网格线?
1)除去编辑窗口中的表格线
单击“工具”菜单中的“选项”,再选中“视图”,找到“网格线”,使之失效;
2)除去打印时的未定义表格线
有时会出现这样的情况:你在编辑时未定义的表格线(在编辑窗中看到的也是淡灰色表格线),一般情况下在打印时是不会打印出来的,可有时却偏偏不听使唤给打印出来了,特别是一些所谓的“电脑”VCD中编辑的Excel表格更是这样。要除去这些表格线,只要在单击“文件”、“页面设置”、“工作表”菜单,点击一下“网格线”左边的选择框,取消选择“网格线”就行了。
24、如何快速格式化报表?
为了制作出美观的报表,需要对报表进行格式化。有快捷方法,即自动套用Excel预设的表格样式。方法是: 选定操作区域,选取“格式”菜单中的“自动套用格式”命令,在格式列表框中选取一款你满意的格式样式,按“确定”按钮即可。要注意的是,格式列表框下面有包括“数字”、“边框线”、“字体”等6个“应用格式种类”选项,若某项前面的“x”不出现,则在套用表格样式时就不会用该项。
25、如何快速地复制单元格的格式?
要将某一格式化操作复制到另一部分数据上,可使用“格式刷”按钮。选择含有所需源格式的单元格,单击工具条上的“格式刷”按钮,此时鼠标变成了刷子形状,然后单击要格式化的单元格即可将格式拷贝过去。
26、如何为表格添加斜线?
一般我们习惯表格上有斜线,而工作表本身并没有提供该功能。其实,我们可以使用绘图工具来实现: 单击“绘图”按钮,选取“直线”,鼠标变成十字型.将其移至要添加斜线的开始位置,按住鼠标左键拖动至终止位置,释放鼠标,斜线就画出来了。另外,使用“文字框”按钮可以方便地在斜线上下方添加文字,但文字周围有边框,要想取消它,可选中文字框,调出快显菜单,选择“对象格式”“图案”,选择“无边框”项即可。
27、如何快速地将数字作为文本输入?
在输入数字前加一个单引号“’”,可以强制地将数字作为文本输入。
28、如何定义自己的函数?
用户在Excel中可以自定义函数。切换至 Visual Basic模块,或插入一页新的模块表(Module),在出现的空白程序窗口中键入自定义函数VBA程序,按Enter确认后完成编 写工作,Excel将自动检查其正确性。此后,在同一工作薄内,你就可以与使用Exed内部函数一样在工作表中使用自定义函数,如:
Function Zm(a)
If a< 60 Then im='不及格”
Else Zm=“及格”
End If
End Function
29、如何在一个与自定义函数驻留工作簿不同的工作簿内的工作表公式中调用自定义函数?
可在包含自定义函数的工作薄打开的前提下,采用链接的方法(也就是在调用函数时加上该函数所在的工作簿名)。假设上例中的自定义函数Zm所在工作薄为MYUDF.XLS,现要在另一不同工作簿中的工作表公式中调用Zm函数,应首先确保MYUDF.XLS被打开,然后使用下述链接的方法:=MYUDF.XLS! ZM(b2)
30、如何快速输入数据序列?
如果你需要输入诸如表格中的项目序号、日期序列等一些特殊的数据系列,千万别逐条输入,为何不让Excel自动填充呢?在第一个单元格内输入起始数据,在下一个单元格内输入第二个数据,选定这两个单元格,将光标指向单元格右下方的填充柄,沿着要填充的方向拖动填充柄,拖过的单元格中会自动按Excel内部规定的序列进行填充。如果能将自己经常要用到的某些有规律的数据(如办公室人员名单),定义成序列,以备日后自动填充,岂不一劳永逸!选择“工具”菜单中的“选项”命令,再选择“自定义序列”标签, 在输入框中输入新序列,注意在新序列各项2间要输入半角符号的逗号加以分隔(例如:张三,李四,王二……),单击“增加”按钮将输入的序列保存起来。
31、使用鼠标右键拖动单元格填充柄
上例中,介绍了使用鼠标左键拖动单元格填充柄自动填充数据序列的方法。其实,使用鼠标右键拖动单元格填充柄则更具灵活性。在某单元格内输入数据,按住鼠标右键沿着要填充序列的方向拖动填充柄,将会出现包含下列各项的菜单:复制单元格、以序列方式填充、以格式填充、以值填充;以天数填充、以工作日该充、以月该充、以年填充;序列……此时,你可以根据需要选择一种填充方式。
32、如果你的工作表中已有某个序列项,想把它定义成自动填充序列以备后用,是否需要按照上面介绍的自定义序列的方法重新输入这些序列项?
不需要。有快捷方法:选定包含序列项的单元格区域,选择“工具”“选项”“自定义序列”,单击“引入”按钮将选定区域的序列项添加至“自定义序列”对话框,按“确定”按钮返回工作表,下次就可以用这个序列项了。
33、上例中,如果你已拥育的序列项中含有许多重复项,应如何处理使其没有重复项,以便使用“引入”的方法快速创建所需的自定义序列?
选定单元格区域,选择“数据”“筛选”“高级筛选”,选定“不选重复的记录”选项,按“确定”按钮即可。
34、如何对工作簿进行安全保护?
如果你不想别人打开或修改你的工作簿,那么想法加个密码吧。打开工作薄,选择...
- ?
Excel中的各种时间差、日期差,该如何计算
牛鹤轩
展开
有些朋友被Excel中的时间差计算问题所困扰,所以今天整理了一下各种时间差、日期差的计算方法以及注意事项。
第一、计算“几小时几分钟几秒”的时间差
最简单的方法,就是用较大的时间减去减小的时间。所谓时间较大者指的是一天中更靠后的时间。
公式:=B2-B1或=TEXT(B2-B1,"h:mm:ss")
注意:原始的时间和计算结果单元格都必须设置成时间格式,如“h:mm:ss”。
如果你没有将结果单元格设置成时间格式,会得到一个数字(如0.0957);而如果你用较小的时间减去较大的时间,那就会得到一堆的“#”。
这么说,在计算时间之前,要用眼睛判断哪个大哪个小,然后再计算咯?
当然不是,我们可以把公式变成下面这两种样子:
公式1:=B2-B1+IF(B2
B2,TEXT(B1-B2,"-h:mm:ss"),TEXT(B2-B1,"+h:mm:ss")) 咦,这两个公式的计算结果有时候不一样呢?
但这两个公式都是正确的。当B2的时间数值上比B1小时,如果你用第二个公式,则表示这两个时间属于同一天,如果你用第一个公式,则表示B2的时间是第二天的时间,两者的计算结果相差24小时。
第二、计算小时差、分钟差和秒数差
在计算考勤时间时,我们不想得到“几天几小时几分钟几秒”的时间差,希望将时间差转换成小时、分钟或秒。这就可以使用上图的公式。
注意,原始的时间还是要设置成时间格式,时间差单元格设置成数值格式。用这种方法计算,会默认两个时间属于同一天。
公式中的1440表示“24小时*60分钟”,86400表示“24小时*60分钟*60秒”。
计算日期差
计算日期差,可以使用函数“DATEDIF(开始日期,结束日期、"Y/M/D")”,“Y”表示计算相差几年、“M”表示计算相差几月,“D”表示计算相差几天。
再次提醒一下,在计算时间差、日期差之前,一定要确保单元格的格式设置正确了。否则,将得到不正确的计算结果。
学习,为了更好的生活。欢迎点赞、评论、关注和点击头像。
- ?
Excel156|NETWORKDAYS.INTL函数,工作日天数花样算法
丹尼尔
展开
问题情景
甲说:我们单位周六、周日双休,从没有节假日;
乙说:我们单位周日单休,从没有节假日;
丙说:我们单位周六、周日双休,还有节假日;
丁说:我们单位周日单休,还有节假日;
但,我们都有同一个问题:怎么算两个日期之间有多少天是工作日?
公式实现
这四种情况的实现,一个函数就能解决,这个函数就是:NETWORKDAYS.INTL
如下图:
NETWORKDAYS.INTL函数
功能:
计算时间段内工作日的天数
语法:
NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
中文语法:
NETWORKDAYS.INTL(起始日期, 结束日期, [周末数字或字符串], [节假日])
NETWORKDAYS.INTL函数语法具有以下参数:
start_date和 end_date必需。 要计算其差值的日期。 start_date可以早于或晚于end_date,也可以与它相同。
weekend可选。 表示介于 start_date和 end_date之间但又不包括在所有工作日数中的周末日。Weekend是一个用于指定周末日的周末数字或字符串。
holidays 可选。 一组可选的日期,表示要从工作日日历中排除的一个或多个日期。 holidays 应是一个包含相关日期的单元格区域,或者是一个由表示这些日期的序列值构成的数组常量。 holidays 中的日期或序列值的顺序可以是任意的
weekend数值表示以下周末日:
(如果是星期一休息,weekend数值是12)
weekend也可以是周末字符串值,长度为七个字符,并且字符串中的每个字符表示一周中的一天(从星期一开始)。 1 表示非工作日,0 表示工作日。 在字符串中仅允许使用字符 1 和 0。 使用 1111111 将始终返回 0。
例如,0000011 结果为星期六和星期日是周末。
所以,本文开始的四种计算方式,可以写为:
但由于字符串写起来较麻烦,一般都用数值。
备注
如果周六周日双休且不考虑节日,也可直接用NETWORKDAYS函数:
往期相关推荐
- ?
三个小时,整理出Excel中的时间计算套路
风车
展开
小伙伴们好啊,今天是节后上班第一天,怎么样,做好撸起袖子加油干的准备了吗?
好时光总是过得太快,今天老祝就和大家说说Excel中,关于时间计算的套路。
开始之前,咱们先说说时间数据的输入。
要输入Excel能识别的时间,小时、分钟和秒必须以英文的冒号间隔,在实际输入时,如果以中文逗号间隔,Excel也会自动纠正:
如果你输入的是8点20或是8.20,Excel是不能识别为时间格式的,这里一定要注意哦。
如果是二B同事输入的时间数据,咱们也可以:
快速转换时间格式
复制内容粘贴到记事本中,按Ctrl+H键调出替换对话框,将小数点. 全部替换为英文冒号 :,再将数据复制回Excel。
计算时间间隔小时
要计算两个时间之间的间隔小时,可以使用公式:
=(结束时间-开始时间)*24
计算时间间隔分钟
要计算两个时间之间的间隔分钟,可以使用公式:
=(结束时间-开始时间)*1440
计算时间间隔秒
要计算两个时间之间的间隔秒数,可以使用公式:
=(结束时间-开始时间)*24*60*60
5秒男的同学请举手,咳咳……
计算跨天的间隔小时
跨天计算时间间隔时,直接相减会得出负数。如果不超过24小时,可以使用以下公式:
=MOD(结束时间-开始时间,1)
取整的间隔小时数
计算两个时间的间隔小时数,不足一小时部分舍去,计算加班时经常会用到,说多了都是泪……
=TEXT(B2-B1,"[h]")
取整的间隔分钟数
不足一分钟按一分钟计算,这个老祝就不说了,移动联通电信计算的都很好。
=CEILING(B2-B1,1/1440)
时间和日期的合并
万般皆是数,要合并日期和时间,只需要做个加法:
日期和时间的拆分
要从日期时间数据中提取出日期,可以使用以下公式:
=INT(A2)
要继续提取时间,只需要做个减法,就欧了:
好了,今天的分享就是这些吧,祝小伙伴们一周都顺利,天天好心情!
- ?
Excel公式函数教学-工龄计算与DAYS函数
杭萝
展开
今天我给大家介绍的是日期计算和一个日期计算函数。
浏览器版本过低,暂不支持视频播放在实际的工作中,我们经常需要计算员工的工作年限。
例如现在这张电子表格中,已经有了员工的姓名和入职日期。那么怎么计算他的工作年限呢?
如果使用之前介绍过的计算年龄的方法,获取现在是哪一年,然后减去入职日期中的年份,这样得到的数据是不精确的。工作年限经常需要精确到天。
所以我们使用这样一个公式来计算:
= today 减去 A2,然后结果除以365天。 这样的结果就可以把工作年限精确到天了。
除了直接相减之外,我们还可以用days函数来得到结果。
Days函数需要两个参数,第一个是结束日期,第二个是开始日期。
现在我们在单元格里输入公式 = days today a2, 再除以365。现在可以看到计算结果和A3单元格是一样的。
上面两种方法里,都可以使用now函数来替代today函数。但是如果用第一种相减的方法时,使用now函数的结果与today会有小数点后的差别,这是因为now函数还包括了时间。而如果使用Days函数,则无论使用now还是today,小数点后的小数位都会被截掉。
- ?
函数|当中国节假日遇上Excel的工作日姐妹花
彩色
展开
俗话说:常在江湖飘,哪有不挨刀;常在水边走,哪有不湿鞋。
在帝都的寒冬里,胖斯基为了和温暖的被窝缠缠绵绵,结果总是在公司点卯的时候,胖斯基才姗姗来迟,没事儿就上了"荣誉榜",为此常成为HRMM光顾对象,哈哈,原因你懂得……
某日,HRMM带着银铃般的笑容徐徐走来,看的胖斯基一脸懵逼,仿佛笑容背后暗藏杀机。简单扯淡后,直奔主题,原来是问:如何根据合同的请款日期(根据公司规定,在10个工作日内须付款(不涵盖请款日期当天)),自动算出最晚截止付款日期,便于管理。
就像这样
胖斯基的答复是:
结果换来的是:
于是胖斯基只好搬出【Excel工作日姐妹花】中的妹妹 WORKDAY
先解释下:
WORKDAY 计算指定日期之前或之后的若干个工作日日期,方式如下:
WORKDAY(开始日期,之后(正数)或之前(负数)天数,假日)
注意:结合天朝实际情况,需要考虑元旦、春节、清明、端午、中秋、国庆等节假日,需要提前将日期列出来,便于函数用来控制日期。
哈哈,掰手指数的工作瞬间得以完成,接下来,你以为完事了?NO,NO,NO……
HRMM说:有些区域公司周六也上班呢,咱们周六算工作日……
于是胖斯基只好请出【Excel工作日姐妹花】中的姐姐WORKDAY.INTL登场
先解释下:
WORKDAY.INTL 计算指定日期之前或之后的若干个工作日日期(可自定义周末),方式如下:
WORKDAY.INTL (开始日期,之后(正数)或之前(负数)天数,自定义周末,假日)
注意:第3个参数自定义周末要结合实际情况选择正确,不输入,则默认周六日是周末,非工作日。
哈哈,收工!
胖斯基带着得意的表情,看着HRMM,以为会赞赏一番。结果HRMM淡淡的一句,嘚瑟吧,天天迟到,真是
好吧,鉴于胖斯基此刻遭受的一万点伤害,我也要来波万箭齐发,请听题:
计算母亲节的日期(定义:每年5月的第2周的周日属于母亲节)
胖斯基说:
针对Excel中工作日姐妹花函数,无论你是HRMM(认识薪资核算),还是财务小姐姐(账龄账款回收),还是帅的一逼的营销大哥,都有大展宏图之地,所以不会的感觉学习吧!
- ?
你还在为计算多少个工作日苦恼吗?别怕,我来教你!
止于心
展开
在日常的工作、学习的过程中,我们经常看到一些文件上写“XX个工作日内完成XX事情”,为什么会有工作日的说法呢?
《中华人民共和国劳动法》第三十六条规定:“国家实行劳动者每日工作时间不超过八小时、平均每周工作时间不超过四十四小时的工时制度。”1995年3月25日,《国务院关于职工工作时间的规定》对工时制度作了修改,规定为:“职工每日工作八小时,每周工作四十小时。”因此,从1995年5月1日起,我国普遍实行每周工作5日(周一到周五)、每日工作8小时的工时制度。
原来,我国法律规定周一到周五为工作日,周末、法定节假日是不计入工作日的。那么有关文件上写的“多少个工作日”其实是不等于“多少天”的。那要工作日要怎么计算呢?别怕,EXCEL有办法!
语法
NETWORKDAYS(start_date, end_date, [holidays])
NETWORKDAYS 函数语法具有下列参数 (参数:为操作、事件、方法、属性、函数或过程提供信息的值。):
Start_date 必需。一个代表开始日期的日期。End_date 必需。一个代表终止日期的日期。Holidays 可选。不在工作日历中的一个或多个日期所构成的可选区域,例如:省/市/自治区和国家/地区的法定假日以及其他非法定假日。该列表可以是包含日期的单元格区域,或是表示日期的序列号的数组常量 (数组:用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。数组区域共用一个公式;数组常量是用作参数的一组常量。)。
要点应使用 DATE 函数输入日期,或者将函数作为其他公式或函数的结果输入。例如,使用函数 DATE(2008,5,23) 输入 2008 年 5 月 23 日。如果日期以文本形式输入,则会出现问题。
我们计算下9月23日到10月31日有多少个工作日。如上图,我们在A6输入=NETWORKDAYS(A3,A4,D4:D12),得出结果是9月23日到10月31日之间有22个工作日。
我们手工算了一下,实际9月23日到10月31日有24个工作日,原因是9月29日和9月30日这两个周末休息日因节假日调整为上班时间。所以我们需要对上述公式需要调整一下,在A6处输入=NETWORKDAYS(A3,A4,D4:D12)+COUNT(G4:G5)公式,即可得到正确的工作日时间。
上面介绍了NETWORKDAYS这个函数,下面我们再介绍个函数WORKDAY。
WORKDAY 函数语法具有以下参数 (参数:为操作、事件、方法、属性、函数或过程提供信息的值。):
Start_date 必需。一个代表开始日期的日期。Days 必需。start_date 之前或之后不含周末及节假日的天数。Days 为正值将生成未来日期;为负值生成过去日期。Holidays 可选。一个可选列表,其中包含需要从工作日历中排除的一个或多个日期,例如各种省\市\自治区和国家\地区的法定假日及非法定假日。该列表可以是包含日期的单元格区域,也可以是由代表日期的序列号所构成的数组常量 (数组:用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。数组区域共用一个公式;数组常量是用作参数的一组常量。)。
要点应使用 DATE 函数输入日期,或者作为其他公式或函数的结果输入。例如,使用函数 DATE(2008,5,23) 输入 2008 年 5 月 23 日。如果日期以文本形式输入,则会出现问题。
所以我们如果想知道6个工作日后是那一天可以输入函数=IF(WORKDAY(TODAY(),6,D4:D12)>MAX(G4:G5),WORKDAY(TODAY(),6-COUNT(G4:G5),D4:D12)+COUNT(G4:G5),WORKDAY(TODAY(),6,D4:D12))即可得到正确的日期。
---------------------------------------------------------------------------------------------------
各位看官,小编一直想在EXCEL应用方面给大家解决一些问题,如果你的生活工作当中经常涉及到EXCEL应用,可以关注我们,当然如果有任何问题也欢迎评论区留言。我们会第一时间给大家解答。
---------------------------------------------------------------------------------------------------
- ?
用Excel公式计算工资几分钟就搞定,是时候跟计算器说再见了
经秋翠
展开
有一个朋友,他有一个团队,做的是短期临时工作。每天的用工人数不是很固定,有时10来个人,有时20多人,高峰时期有30多人。他每天有一个工作,就是记录工人的考勤,一开始的时候,是记在本子上,每天都记,因为人员不固定,工作量特别大,特别是每2周核算工资的时候容易出错,当时按计算器,按了一会就不记得按到哪了。有时为了核对准确,要花好几个晚上,甚至熬夜到半夜。有一次他和我聊起他的烦恼后,我建议他立即使用Excel记录工资考勤,并利用Excel公式计算工人工资,让他从重复烦索的工作走出来,帮他节省了大量时间。那么用Excel公式计算工资是如何实现的呢?我现在就来演示整个过程。
说明:以下为了表格不会太宽,以便在手机上观看最佳效果,只演示了1号至5号的工资情况(即C列是1号,D列是2号,以此类推,G列是5号),如增到2周或1个月只要在此基础上,在G列后面插入相应的列即可。
第一步:左边红色线框中数据是考勤,根据实际出勤情况填写。1号时只要把C列(即1号)的出勤天数记录上,当天晚上收工回家花上几分钟记录一下。同理2号时把(D列)也是这样记录,当5天记录满时是以下左边红框内的数据。右边红框内的数据也是固定的,即每天的工资数。
第二步,开始利用Execl公式,计算5天内的出勤总天数。用到的公式是=SUM(C3:G3),记得有个等于号=,SUM是求和意思,C3就代表C列和第三行交叉的数据,以下这种表示方法以此类推。=SUM(C3:G3)的表达的含义就是上图中0.5+0.5+1+1+1=4的结果。
第三步:我们回车后就看到结果4出来了。
第四步:选中H3单元格,出现十字光标时,往下拉。
第五步:这样就得到H4的值是4.5,H5的值是5
第六步,计算总工资时,用到的公式是=H3*i3
第四步:回车时J3的值就出来了,是400。选中J3单元格,出现十字光标时,往下拉。
第五步:这样就得到J4的值是315,H5的值是350
第六步:计算汇总的公式是=SUM(J3:J6)
第七步:回车后,汇总的工资就是1065
至此,工资明细和工资汇总都出来,如果检查也很方便,检查一行一个人的,看看公式的范围有没有错,如果没有错基本就可以放心了。是不是Excel公式比用记在本子上,然后用记算器计算效率提升很多。您如果有需要也可以参考来做。
本头条号:时代新生分享工作、生活、技能方面的经验,愿和您一起成长。请多多关注哦!
- ?
工作中最常用的excel函数公式大全,帮你整理齐了,拿来即用
余无极
展开
1、取绝对值
=ABS(数字)
2、取整
=INT(数字)
3、四舍五入
=ROUND(数字,小数位数)
二、判断公式
1、把公式产生的错误值显示为空
公式:C2
=IFERROR(A2/B2,"")
说明:如果是错误值则显示为空,否则正常显示。
2、IF多条件判断返回值
公式:C2
=IF(AND(A2<500,B2="未到期"),"补款","")
说明:两个条件同时成立用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、按日期和产品求和
公式:F2
=SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25)
说明:SUMPRODUCT可以完成多条件求和
五、查找与引用公式
1、单条件查找公式
公式1:C11
=VLOOKUP(B11,B3:F7,4,FALSE)
说明:查找是VLOOKUP最擅长的,基本用法
2、双向查找公式
公式:
=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))
说明:利用MATCH函数查找位置,用INDEX函数取值
3、查找最后一条符合条件的记录。
公式:详见下图
说明:0/(条件)可以把不符合条件的变成错误值,而lookup可以忽略错误值
4、多条件查找
公式:详见下图
说明:公式原理同上一个公式
5、指定区域最后一个非空值查找
公式;详见下图
说明:略
6、按数字区域间取对应的值
公式:详见下图
公式说明:VLOOKUP和LOOKUP函数都可以按区间取值,一定要注意,销售量列的数字一定要升序排列。
六、字符串处理公式
1、多单元格字符串合并
公式:c2
=PHONETIC(A2:A7)
说明:Phonetic函数只能对字符型内容合并,数字不可以。
2、截取除后3位之外的部分
公式:
=LEFT(D1,LEN(D1)-3)
说明:LEN计算出总长度,LEFT从左边截总长度-3个
3、截取-前的部分
公式:B2
=Left(A1,FIND("-",A1)-1)
说明:用FIND函数查找位置,用LEFT截取。
4、截取字符串中任一段的公式
公式:B1
=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",20)),20,20))
说明:公式是利用强插N个空字符的方式进行截取
5、字符串查找
公式:B2
=IF(COUNT(FIND("河南",A2))=0,"否","是")
说明: FIND查找成功,返回字符的位置,否则返回错误值,而COUNT可以统计出数字的个数,这里可以用来判断查找是否成功。
6、字符串查找一对多
公式:B2
=IF(COUNT(FIND({"辽宁","黑龙江","吉林"},A2))=0,"其他","东北")
说明:设置FIND第一个参数为常量数组,用COUNT函数统计FIND查找结果
七、日期计算公式
1、两日期相隔的年、月、天数计算
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" 天数的差。忽略日期中的月和年。
"YM" 月数的差。忽略日期中的日和年。
"YD" 天数的差。忽略日期中的年。
2、扣除周末天数的工作日天数
公式:C2
=NETWORKDAYS.INTL(IF(B2
说明:返回两个日期之间的所有工作日数,使用参数指示哪些天是周末,以及有多少天是周末。周末和任何指定为假期的日期不被视为工作日
- ?
Excel计算工作日的函数,你会操作吗?
皮子骞
展开
计算日期,在我们的实际工作中非常的实用和常用。我们在签订装修合同的时候,约定工期的时候,或者我们需要开发一个网站,找IT公司来承接这个项目,我们在签订合同的时候。不知道我们有没有注意到:工期或者开发周期的空白处后边跟着是多少工作日,而不是多少天。
成都电脑培训短期班为什么我们的合同会这样写呢:一来执行这个项目的员工周末要休息,二来万一项目中间出现上面意外,我们有多余的时间来补救,不至于出现违约的情况。因为有的大型的项目,违约金也是很吓人的好不好。
说到日期函数,大家估计一下子就想起了datedif函数(计算两日期之差),但是这个函数是把日期的工作日和周末天数都进行累加了,我如何只计算工作日的天数呢?
下面就有请:networkdays函数闪亮登场。它是来进行计算工作日的。
networkdays函数参数(起始日期,终止日期,工作日的节假日日期)
举个例子给大家:
成都办公软件培训学校1、 由于这段时间没有其他的节假日,如果我们公司是双休的话,这里的工作天数的计算只需要键入公式即可:=NETWORKDAYS(A3,B3)。
成都办公软件培训机构2、 如果说在这段时间员工休假了,或者在工作日的区间有节假日的话,我们只需要把休假的日期或者节假日先罗列出来,如下图截图:
成都电脑培训机构现在如何计算他实际的工作日期呢?只需要这样编辑公式即可:=NETWORKDAYS(A2,B2,A10:A13)
3、 有些公司是单休的话应该怎么办呢?上边的这个函数是计算工作日的,也就是双休的情况。
这时候用到这个函数,=NETWORKDAYS.INTL(A3,B3,11)
成都电脑培训学校NETWORKDAYS.INTL函数参数:(起始日期,终止日期,休假的方式,工作日的节假日)
这里我们一般休假是单休的话,填写数字11即可。
成都电脑培训学院看完小编的解析,是不是觉得计算工作日的方法就是这么简单,希望我的分享能帮到大家。
excel工作日计算公式
-
1、只需3秒快速实现求和
-
2、如何快速填充序号
-
3、如何自动填充序号(公式法)
-
4、数据条的神奇应用
-
5、多文本快速合并
-
6、查找与替换的不同玩法
-
7、快速定位到指定区域
-
8、数据排序、工资条制作
-
9、快速筛选(模糊、精确筛选)
-
10、快速插入空行
-
11、快速删除空行
-
12.快速跳转到天涯海角
-
13、.同时查看两个Excel文件
-
14、用条件格式扮靓报表
-
15、一键插入Excel图表
-
16、批量处理行高、列宽
-
17、利用拆分功能查看数据
-
18、批量录入相同内容
-
19、工作表快速跳转
-
20、批量录入表格模板(精品课程)
-
21、Excel函数与公式的应用、公式循环引用的查找
-
22、IF函数单条件判断同比增长
-
23、用sum函数 格式相同,连续多表数据汇总
-
24、excel快捷键
-
25、VLOOKUP函数——根据销售员匹配销售额
-
26、统计各部门销售总额
-
27、统计指定条件个数
-
28、怎样输入当前日期和时间、星期数
-
29、销售业绩排名
-
30、Sumproduct函数-万能函数(销售额汇总求和)
-
31、根据销售员,地区,商品名称汇总
-
32、批量替换PPT字体
-
33、给销售额数据批量添加万元单位
-
34、一秒快速核对两列数据
-
35、快速定位到指定单元格或区域
-
36、快速制作双行标题工资条
-
37、给你的表格做个瘦身
-
38、快速打开常用的Excel文件
-
39、快速打开多个Excel文件
-
40、利用创建组—快速隐藏/展开多列数据
-
41、快速制作下拉菜单
-
42、复制粘贴表格,如何保留数据源列宽格式一致?
-
43、两列数据位置互换
-
44、1秒钟扮靓报表——如何实现表格隔行换色
-
45、快速删除重复记录——保留唯一值
-
46、快速向下填充、向右填充,文本或公式
-
47、给Excel文件添加密码
-
48、插入带图片的批注
-
49、输入公式后不计算?
-
50、如何设置单元格缩进
-
51、快速解决Excel表格总显示货币格式
-
52、批量添加万元单位
-
53、你会四舍五入么?
-
54、用RAND函数机选彩票
-
55、冻结首行你会么?
-
56、超链接的高级应用
-
57、IFERROR函数-屏蔽错误值
-
58、批量填充颜色
-
59、录入数据
-
60、快速输入工号
-
61、快速行列转置
-
62、自定义缩放界面
-
63、多个单元格同时输入
-
64、如何计算立方米?
-
65、快速制作双行标题工资条
-
66、输入带方框的√和×
-
67、快速将姓名对齐
-
68、快速输入性别
-
69、按单位职务排序
-
70、自动计算合同到期日期
-
71、计算时间间隔
-
72、日期和时间的拆分
-
73、快速处理不规范的日期格式
-
74、快速填充合并单元格
-
75、效率加倍的快捷键
-
76、快速复制表格和对象
-
77、快速创建工作表副本
-
78、快速复制序列号
-
79、快速显示公式
-
80、多个单元格同时输入
-
81、快速调整显示比例
-
82、快速自动填充
-
83、快速填充(Ctrl+E)
-
84、Ctrl与数字键结合
-
85、快速将多列数据整理为1列
-
86、快速将1列数据拆分为多列
-
87、快速定位公式
-
88、快速录入数据
-
89、快速累计求和
-
90、身份证号码显示为0怎么办?
-
91、快速制作斜线表头
-
92、文本竖向显示
-
93、神奇的监视窗口
-
94、不一样的格式刷
-
95、快速美化图表
-
96、快速生成当前日期
-
97、快速找出循环引用
-
98、快速提取信息
-
99、二维表快速转换为一维表
-
100、快速多表合并