考勤常用函数Word格式.docx
《考勤常用函数Word格式.docx》由会员分享，可在线阅读，更多相关《考勤常用函数Word格式.docx（12页珍藏版）》请在冰豆网上搜索。
aH33区域，按上面工具栏中的“数据-有效性”，弹出有效性设置对话框，选“设置”选项卡，在“允许”中选“序列”，在“来源”中点击右端的折叠按钮，然后用鼠标在表格中选取aR1:
aR8区域，再次点击折叠按钮，回到有效性设置窗口，按“确定”退出。
完成后，在考勤表的d4:
aH33区域中任意一个单元格选中时，都会出现一个下拉框按钮，点击按钮就弹出下拉框，可用鼠标方便的选取要输入的考勤符号。
四、考勤统计
考勤已经可以输入了，怎样统计每个人的出勤情况呢？
还是通过公式自动来统计。
先划出一块区域来做考勤统计，如下图红圈范围：
这个区域中要设置多个合并单元格，aK4:
aK5是合并的，aL4:
aL5是合并的......aP4:
aP5是合并的。
也就是每一个姓名对应的上下两行需要合并，这样方便把上午与下午统计在一个格中。
做完aL4:
aP5区域的合并操作后，选中区域右下角填充柄，按住鼠标左键向下拉动，一直到aP33单元格再松开鼠标左键，就能快速的把下面单元格分别也变成合并状态。
（其实就是复制了aL4:
aP5的样式）
由于第一个人的考勤记录区域是d4:
aH5区域，所以需要将此区域中的考勤符号出现次数统计出来，就能知道这个人的考勤情况了。
先在aK3:
aP3中输入考勤符号，然后在aK4单元格中输入公式
=coUnTiF（$d4:
$aH5,
aK$3）怎么还在做考勤表，用随办帮助你吧
使用coUnTiF函数统计d4:
aH5区域内出现了几次aK3格中的符号。
用拖动复制功能把这个公式复制到aK4:
aP4区域中。
再选中aK4:
aP4区域，按住aP4右下角的填充柄向下拖动复制，一直复制到aP33单元格。
现在统计区域中的每个格都有公式了，由于公式中某些部份使用了绝对引用符号“$”，所以在拖动复制中，每个单元格的公式都是不太一样的。
在这个考勤表中，多次使用了“拖动复制”方法，可以极大的简化输入公式与设置格式的操作，而且在公式中灵活使用绝对引用符号“$”，也能把有规律变化的公式快速输入到区域中，避免了逐个输入的麻烦。
现在我们看一下有统计公式的效果
在统计结果中，会有许多0值出现，表示对应的考勤符号未在考勤区域中出现，0值太多时会感觉很“乱”，我们通过设置来“隐藏”这些0值。
按工具栏中的“工具-选项”，出现选项窗口，按下图设置，去掉“零值”前的勾，就可使这些0值不显示出来。
篇二：
Excel常用函数功能列表
Excel常用函数功能、用法及实例剖析
我们在使用Excel制作表格整理数据的时候，常常要用到它的函数功能来自动统计处理表格中的数据。
本专题整理了Excel中使用频率最高的函数的功能、使用方法，以及这些函数在实际应用中的实例剖析，并配有详细的介
绍和图示，同时提供.xls文件供大家下载参考。
Excel常用函数实例剖析
实例·
奖金计算表
功能
只要将员工的出勤情况记录在表中，该员工的奖金将自动计算出来，兼有考勤和计算奖金两种功能。
自动统计表做好以后还可以保存成模板，以便以后使用。
原文件下载.xls文件下载
·
制作万年历这个万年历可以显示当月的月历，还可以随意查阅任何日期所属的月历，非常方便。
如果你愿意，还可以让它在特殊的日子里显示不同的提醒文字。
.xls文件下载
自动评分计算表参加比赛的选手为20人，评委9人，去掉1个最高分和1个最低分后，求出平均分，然后根据平均分的高低排定选手的名次。
自动统计学生成绩自动统计最高分、最低分、总分、平均分、名次等数据信息，还可以根据自定条件以不同的颜色显示分数。
各分数段学生数统计·
自动生成员工简历表
统计各学科相应分数段或各等级的学生人数。
自动提取“员工基本情况登记表”中的信息，生成并打印员工简历表。
.xls文件下载.xls文件下载
>
更多内容⊙Excel常用函数功能及用法介绍
函数名aBSand
求出参数的绝对值。
用途示例数据计算
“与”运算，返回逻辑值，仅当有参数的结果均为逻辑“真（TRUE）”条件判断时返回逻辑“真（TRUE）”，反之返回逻辑“假（FaLSE）”。
aVERaGE
求出所有参数的算术平均值。
数据计算
coLUmn显示所引用单元格的列标号值。
显示位置
显示在一个单元字符合并concaTEnaTE将多个字符文本或单元格中的数据连接在一起，
格中。
coUnTiF
统计某个单元格区域中符合指定条件的单元格数目。
条件统计
daTEdaTEdiFdaYdcoUnT
给出指定数值的日期。
计算返回两个日期参数的差值。
计算参数中指定日期或引用单元格中的日期天数。
返回数据库或列表的列中满足指定条件并且包含数字的单元格数目。
显示日期计算天数计算天数条件统计
FREQUEncY
以一列垂直数组返回某个区域中数据的频率分布。
概率计算
iF根据对指定条件的逻辑判断的真假结果，返回相对应条件触发的条件计算计算结果。
indEX返回列表或数组中的元素值，此元素由行序号和列序号的索引值数据定位进行确定。
inTiSERRoR
将数值向下取整为最接近的整数。
用于测试函数式返回的数值是否有错。
如果有错，该函数返回TRUE，反之返回FaLSE。
数据计算逻辑判断
LEFTLEn
从一个文本字符串的第一个字符开始，截取指定数目的字符。
统计文本字符串中字符数目。
截取数据字符统计
maTcHmaXmidmin
返回在指定方式下与指定数值匹配的数组中元素的相应位置。
求出一组数中的最大值。
从一个文本字符串的指定位置开始，截取指定数目的字符。
求出一组数中的最小值。
匹配位置数据计算字符截取数据计算
modmonTH
求出两数相除的余数。
求出指定日期或引用单元格中的日期的月份。
数据计算日期计算
now
给出当前系统日期和时间。
显示日期时间
oR仅当所有参数值均为逻辑“假（FaLSE）”时返回结果逻辑“假（FaLSE）”，否则都返回逻辑“真（TRUE）”。
逻辑判断
RanKRiGHT
返回某一数值在一列数值中的相对于其他数值的排位。
数据排序
从一个文本字符串的最后一个字符开始，截取指定数目的字符。
字符截取
SUBToTaLSUmSUmiF
返回列表或数据库中的分类汇总。
求出一组数值的和。
计算符合指定条件的单元格区域内的数值和。
分类汇总数据计算条件数据计算
TEXTTodaY
根据指定的数值格式将相应的数字转换为文本形式给出系统日期
数值文本转换显示日期
VaLUEVLooKUP
将一个代表数值的文本型字符串转换为数值型。
文本数值转换
在数据表的首列查找指定的数值，并由此返回数据表当前行中指条件定位定列处的数值
wEEKdaY
给出指定日期的对应的星期数。
星期计算
篇三：
excel常用函数公式及技巧搜集（常用的）
EXcEL提供直观快捷的数据输入、强大灵活的数据处理、丰富多样的图表制作、完善快捷的报表设计、快速完备的统计分析功能，办公人员几乎没有不用到EXcEL的。
如果我们只满足手工操作这些功能，那我们处理一些重复性的工作时就只能象机器人一样进行机械的操写代码，再运行程序，oK，不到一分钟完成了！
下次老板再让做，没关系，调出代码再运行一遍嘛，EaSY!
如果你是刚毕业的大学生：
面对激烈的竞争，面试的时候，别人要花1小时才能完成的数据处理问题，你10分钟搞定，这时候主管一定会着急起来：
“你什么时候能够上班?
?
”。
如果你想学VBa
VBa这么好，学起来会很难吗？
不！
其实VBa是程序语言中最易学，上手极快的一个，并不强调你必须要有专门的程序开发基础，因为EXcEL提供了VBa录制功能。
即使你不会写代码也可以录制一段VBa代码去处理重复工作，如果忘记一些代码的语法了，录制一段代码就能看到了，所以学起来最容易了。
EXcELVBa也
【身份证信息？
提取】
从身份证号码中提取出生年月日
=TEXT（mid（a1,7,6+（LEn（a1）=18）*2）,”#-00-00”）+0
=TEXT（mid（a1,7,6+（LEn（a1）=18）*2）,”#-00-00”）*1
=iF（a2”“,TEXT（（LEn（a2）=15）*19&
mid（a2,7,6+（LEn（a2）=18）*2）,”#-00-00”）+0,）显示格式均为yyyy-m-d。
（最简单的公式，把单元格设置为日期格式）
=iF（LEn（a2）=15,”19”&
mid（a2,7,2）&
”-”&
mid（a2,9,2）&
mid（a2,11,2）,mid（a2,7,4）&
mid（a2,11,2）&
mid（a2,13,2））
显示格式为yyyy-mm-dd。
（如果要求为“1995/03/29”格式的话，将”-”换成”/”即可）=iF（d4=““,”“,iF（LEn（d4）=15,TEXT（（“19”&
mid（d4,7,6））,”0000年00月00日”）,iF（LEn（d4）=18,TEXT（mid（d4,7,8）,”0000年00月00日”））））
显示格式为yyyy年mm月dd日。
（如果将公式中“0000年00月00日”改成“0000-00-00”,则显示格式为yyyy-mm-dd）
=iF（LEn（a1:
a2）=18,mid（a1:
a2,7,8）,”19”&
mid（a1:
a2,7,6））
显示格式为yyyymmdd。
=TEXT（（LEn（a1）=15）*19&
mid（a1,7,6+（LEn（a1）=18）*2）,”#-00-00”）+0
=iF（LEn（a2）=18,mid（a2,7,4）&
-mid（a2,11,2）,19&
-mid（a2,9,2））
=mid（a1,7,4）&
”年”&
mid（a1,11,2）&
”月”&
mid（a1,13,2）&
”日”
=iF（a1”“,TEXT（（LEn（a1）=15）*19&
mid（a1,7,6+（LEn（a1）=18）*2）,”#-00-00”））从身份证号码中提取出性别
=iF（mod（mid（a1,15,3）,2）,”男”,”女”）（最简单公式）
=iF（mod（RiGHT（LEFT（a1,17））,2）,”男”,”女”）
=iF（a2””,iF（mod（RiGHT（LEFT（a2,17））,2）,”男”,”女”）,）
=iF（VaLUE（LEn（RoUnd（RiGHT（a1,1）/2,2）））=1,”男”,”女”）
从身份证号码中进行年龄判断
=iF（a3””,daTEdiF（TEXT（（LEn（a3）=15*19&
mid（a3,7,6+（LEn（a3）=18*2）,”#-00-00”）,TodaY（）,”Y”）,）
=daTEdiF（a1，TodaY（），“Y”）
（以上公式会判断是否已过生日而自动增减一岁）
=YEaR（now（））-mid（E2,iF（LEn（E2）=18,9,7）,2）-1900
=YEaR（TodaY（））-iF（LEn（a1）=15,”19”&
mid（a1,7,2）,mid（a1,7,4））
=YEaR（TodaY（））-VaLUE（mid（B1,7,4））&
”岁”
=YEaR（TodaY（））-iF（mid（B1,18,1）=““,concaTEnaTE（“19”,mid（B1,7,2））,mid（B1,7,4））按身份证号号码计算至今天年龄
=daTEdiF（TEXT（（LEn（a1）=15）*19&
mid（a1,7,6+（LEn（a1）=18）*2）,”#-00-00”）,TodaY（）,”y”）
以20XX年10月31日为基准日,按按身份证计算年龄（周岁）的公式
=daTEdiF（TEXT（mid（a1,7,6+（LEn（a1）=18）*2）,”#-00-00”）*1,”20XX-10-31”,”y”）按身份证号分男女年龄段
按身份证号分男女年龄段，身份证号在K列，年龄段在J列（身份证号为18位）男性16周岁以下为1
男性16周岁（含16周岁）以上至50周岁为2
男性50周岁（含50周岁）以上至60周岁为3
男性60周岁（含60周岁）以上为4
女性16周岁以下为1
女性16周岁（含16周岁）以上至45周岁为2
女性45周岁（含45周岁）以上至55周岁为3
女性55周岁（含55周岁）以上为4
=maTcH（daTEdiF（daTE（mid（K1,7,4）,mid（K1,11,2）,mid（K1,13,2））,TodaY（）,”y”）,{0,16,50,60}-{0,0,5,5}*iSEVEn（mid（K1,17,1）））
=SUm（--（daTEdiF（mid（K1,7,4）&
”/”&
mid（K1,11,2）&
mid（K1,13,2）,TodaY（）,”y”）>
={0,16,45,55}+{0,0,5,5}*mod（mid（K1,17,1）,2）））
㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜
【年龄和工龄计算】
根据出生年月计算年龄
＝daTEdiF（a1,TodaY（）,”y”）
＝daTEdiF（a1,TodaY（）,”y”）&
”周岁”
＝daTEdiF（a1,now（）,”y”）
根据出生年月推算生肖
中国人有12生肖，属什么可以推算出来。
即用诞生年份除以12，再用除不尽的余数对照如下：
0→猴，1→鸡，2→狗，3→猪，4→鼠，5→牛，6→虎，7→兔，8→龙，9→蛇，10→马，11→羊例如：
XXX出生于1921年，即用1921年除以12，商得数为160，余数为1，对照上面得知余数1对应生肖是鸡，XXX就属鸡。
=mid（“猴鸡狗猪鼠牛虎兔龙蛇马羊”,mod（YEaR（a2）,12）+1,1）（20XX）如何求出一个人到某指定日期的周岁？
=daTEdiF（起始日期,结束日期,”Y”）
计算距离退休年龄的公式
=iF（E2=““,”“,iF（E2>
=V2,”已经退休”,”距离退休还有”&
daTEdiF（TodaY（）,daTE（YEaR（U2）+（V2）,monTH（U2）,daY（U2））,”Y”）&
daTEdiF（TodaY（）,daTE（YEaR（U2）+（V2）,monTH（U2）,daY（U2））,”Ym”）&
”个月”&
daTEdiF（TodaY（）,daTE（YEaR（U2）+（V2）,monTH（U2）,daY（U2））,”md”）&
”天”））其中E2为年龄（可用身份证号码的公式生成）；
V2为法定退休年龄（男60，女50）公式为：
=iF（d2=““,”“,iF（d2=“男”,60,50））
d2为男或女（可用身份证号码的公式生成）；
U2为出生年月日（可用身份证号码的公式生成）。
求工齡
=daTEdiF（B2,TodaY（）,”y”）
=daTEdiF（B2,TodaY（）,”ym”）
=daTEdiF（B2,TodaY（）,”md”）
=daTEdiF（B2,TodaY（）,”y”）&
daTEdiF（B2,TodaY（）,”ym”）&
daTEdiF（B2,TodaY（）,”md”）&
计算工龄
=daTEdiF（c6,c8,”y”）求两日期间的年数
=daTEdiF（c6,c8,”ym”）求两日期间除去整年数剩余的月数
=daTEdiF（c6,c8,”m”）求两日期间的总月数
如果只需要算出周年的话，可以用=datedif（“1978-8”,”20XX-5”,”Y”）
年龄及工龄计算
有出生年月如何求年龄？
有工作时间如何求工龄？
（求出的结果为多少年另几个月，如：
0303的形式，即3年零3个月）。
a1是出生年月或工作时间：
=datedif（a1,today（）,”y”）
=text（datedif（a1,today（）,”y”）,”00”）&
text（datedif（a1,today（）,”m”）,”00”）
如[B2]=1964-9-1则:
=TEXT（daTEdiF（B2,TodaY（）,”y”）,”00”）&
TEXT（mod（daTEdiF（B2,TodaY（）,”m”）,12）,”00”）&
#39;
显示4009
=TEXT（daTEdiF（B2,TodaY（）,”y”）,”00年”）&
TEXT（mod（daTEdiF（B2,TodaY（）,”m”）,12）,”00月”）&
显示40年09月如果你找不到daTEdiF函数，也可以不用daTEdiF函数,
=TEXT（RiGHT（YEaR（now（）-B2）,2）,”00”）&
TEXT（mod（monTH（now（）-B2）-1,12）,”00”）&
=TEXT（RiGHT（YEaR（now（）-B2）,2）&
mod（monTH（now（）-B2）-1,12）&
”个月”,”“）&
显示40年09个月
自动算出工龄日期格式为（yyyy.mm.dd）
能否用：
（yyyy.mm.dd）这种格式来计算出工龄有多长呢~？
以前用这样一段（=TEXT（RiGHT（YEaR（now（）-a1）,2）&
mod（monTH（now（）-a1）-1,12）&
”个月”,”“））。
但这种方法只能用：
（yyyy-mm-dd）这样的日期格式才能实现！
你不妨把“.”替换成“-”，不就行了吗，再说后者是日期的一种标准格式，
=TEXT（RiGHT（YEaR（now（）-SUBSTiTUTE（a1,”.”,”-”））,2）&
mod（monTH（now（）-SUBSTiTUTE（a1,”.”,”-”））-1,12）&
”个月”,”“）
【时间和日期应用】
自动显示当前日期公式
=YEaR（now（））当前年
=monTH（now（））当前月
=daY（（now（）））当前日
如何在单元格中自动填入当前日期
ctrl+;
如何判断某日是否星期天
=wEEKdaY（a2,2）
=TEXT（a1,”aaaa”）
=mod（a1,7）某个日期是星期几
比如20XX年2月9日，在一单元格内显示星期几。
=TEXT（a1,”aaa”）（五）
=TEXT（a1,”aaaa”）（星期五）
=TEXT（a1,”ddd”）（Fri）
=TEXT（a1,”dddd”）（Friday）
什么函数可以显示当前星期
如：
星期二10:
41:
56
=TEXT（now（）,”aaaahh:
mm:
ss”）
求本月天数
设a1为20XX-8-4求本月天数
a1=daY（daTE（YEaR（a1）,monTH（a1）+1,0））
也有更簡便的公式：
=daY（EomonTH（now（）,0））需加載分析工具箱。
当前月天数：
=daTE（YEaR（TodaY（））,monTH（TodaY（））+1,1）-daTE（YEaR（TodaY（））,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年,变成20XX-7-15
=daTE（YEaR（a1）+5,monTH（a1）,daY（a1））
=EdaTE（a1,12*5）
如何对日期进行上、中、下旬区分
=LooKUP（daY（a1）,{0,11,21,31},{“上旬”,”中旬”,”下旬”,”下旬”}）
如何获取一个月的最大天数
“=daY（daTE（20XX,3,1）-1）”或”=daY（B1-1）”,B1为”20XX-03-01
日期格式转换公式
将“01/12/20XX”转换成“20XX0112”格式
＝RiGHT（a1,4）&
mid（a1,4,2）&
LEFT（a1,2）
＝YEaR（$a2）&
TEXT（monTH（$a2）,”00”）&
TEXT（daY（$a2）,”00”）该公式不用设置数据有效性,但要设置储存格格式。
也可以用下列两方法：
1、先转换成文本,然后再用字符处理函数。
2、[数据]-[分列][日期]-[mdY]
将“20XX年9月”转换成“20XX09”格式
先用公式：
=text（a1,”yyyymm”）+0然后将单元格格式为常规。
将“20X
升级会员 