excel一月考勤表格怎么做？一个简美考勤表这般做！
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">excel制作<span style="color: black;">一月</span>的考勤表，其实<span style="color: black;">无</span>想象的<span style="color: black;">繁杂</span>，它<span style="color: black;">重点</span>应用了<span style="color: black;">有些</span><span style="color: black;">平常</span>的函数和表格内置功能。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">一个<span style="color: black;">完整的考勤表，它<span style="color: black;">包括</span>了<span style="color: black;">每一个</span>月份的明细考勤数据，以及考勤汇总表、日历视图、节假日表等。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_jpg/iaFJlPwvgGwIZibuiak98h3PXViaiaSdibUMBic57Wa4jCfSSM204YK0LcX0hXk0eco0CWc43rLDwqiaX5syXx9UI3pxrw/640?wx_fmt=jpeg&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">这节<span style="color: black;">咱们</span>的<span style="color: black;">重点</span>内容是快速制作<span style="color: black;">一月</span>份考勤表，如下图所示：</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_jpg/iaFJlPwvgGwIZibuiak98h3PXViaiaSdibUMBicn3Sou4CMQl4gtUbVu8scczl5rGyLnGjIrsJxhzq95LyicDW4pAF8WibQ/640?wx_fmt=jpeg&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">上图考勤表外观较为简洁，<span style="color: black;">无</span>太多的内容，但<span style="color: black;">已然</span>足够<span style="color: black;">平常</span><span style="color: black;">运用</span><span style="color: black;">需要</span>，<span style="color: black;"><span style="color: black;">经过</span>输入员工每日的出勤状态，能够<span style="color: black;">经过</span><span style="color: black;">区别</span>单元格颜色高亮<span style="color: black;">表示</span>和区分<span style="color: black;">她们</span>的出勤状态，并自动在表格中统计该员工或所有员工的<span style="color: black;">区别</span>出勤状态次数。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">下面进入正题，<span style="color: black;">根据</span><span style="color: black;">过程</span>进行快速讲解。</p>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">1、获取日期</span></h1>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">一月</span><span style="color: black;">包括</span>30或31天，<span style="color: black;">倘若</span>是2月份还<span style="color: black;">包括</span>28或29天，<span style="color: black;">因此呢</span><span style="color: black;">咱们</span><span style="color: black;">能够</span>输入date公式来自动获取当前月份的所有日期值。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">公式为：<strong style="color: blue;">=DATE($N$3,$S$3,COLUMN(A1))</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_jpg/iaFJlPwvgGwIZibuiak98h3PXViaiaSdibUMBicbMhhtAYayOMB32CPAiaY7Z4qh6D0rs8r8sYqNoLWFBkpUt8XOqglB4Q/640?wx_fmt=jpeg&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">date函数是返回指定年、月、日的日期值，<span style="color: black;">包括</span>三个参数分别为年份、月份和日数。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">这儿</span><span style="color: black;">重点</span>是公式中第3参数日数<span style="color: black;">运用</span>了column函数表达，能够使公式在向右填充时自动获取递增的日期值。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">另外</span>需要<span style="color: black;">重视</span>一点，<span style="color: black;">date函数的结果是一个日期，而<span style="color: black;">咱们</span>的<span style="color: black;">需要</span>是<span style="color: black;">表示</span>天数，<span style="color: black;">因此呢</span>必须将日期转换为天数，即只<span style="color: black;">表示</span>日期中的天数值。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">这儿</span>作者利用<span style="color: black;">单元格格式的自定义功能，</span><strong style="color: blue;"><span style="color: black;">在类型中输入“D”</span></strong><span style="color: black;">，即<span style="color: black;">暗示</span>仅<span style="color: black;">表示</span>日期天数！</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_jpg/iaFJlPwvgGwIZibuiak98h3PXViaiaSdibUMBic5mIiaESMoVm2km851icTdlsdeLHKFwqaRtfNbfiaNct7PoPKuu6mCxuCQ/640?wx_fmt=jpeg&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">经过</span>以上公式和格式设置，能得到本月的天数值。</p>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">2、获取星期值</span></h1>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">考勤表<span style="color: black;">一般</span>需要对应星期值，星期值的计算比较简单，<span style="color: black;">一般</span><span style="color: black;">运用</span>text函数就能得到。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">公式为：<strong style="color: blue;">=TEXT(H9,"aaa")</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_jpg/iaFJlPwvgGwIZibuiak98h3PXViaiaSdibUMBicYkerDbcKO2d9YmYNzNTDFPWz2ed5No2l4NMAshkJWj5Mg1JIeicg5yQ/640?wx_fmt=jpeg&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">这儿</span>重点是<span style="color: black;">text函数的第2参数，它是一个固定表达，<span style="color: black;">暗示</span>转换日期为特定格式的星期值，“aaa”<span style="color: black;">暗示</span>大写星期值！</span><span style="color: black;">咱们</span>记住这个表达<span style="color: black;">就可</span>。</p>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">3、设置出勤录入区域下拉菜单</span></h1>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">考勤表<span style="color: black;">包括</span>较多的出勤状态，<span style="color: black;">例如</span><span style="color: black;">平常</span>的正常、迟到、早退、事假、病假、加班等等，<span style="color: black;">咱们</span>需要设置一个下拉菜单来快速<span style="color: black;">选取</span>对应的出勤状态。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">关于下拉菜单的设置，<span style="color: black;">日前</span><span style="color: black;">重点</span>是利用数据验证功能，<span style="color: black;">详细</span>操作如下图所示，<span style="color: black;">首要</span><span style="color: black;">选中所有出勤录入区域，<span style="color: black;">而后</span>点击数据工具栏下方功能区“</span><strong style="color: blue;"><span style="color: black;">数据验证-序列-<span style="color: black;">源自</span></span></strong><span style="color: black;">”，在<span style="color: black;">源自</span>输入框中拉取表格上方的出勤状态文本区域。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_jpg/iaFJlPwvgGwIZibuiak98h3PXViaiaSdibUMBicticl7LKRciapUe9tJ8m9icbl8I3aNVX1EmJ75g5yaRl72xAClyE4toia9Q/640?wx_fmt=jpeg&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">得到的下拉菜单如下图所示：</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_jpg/iaFJlPwvgGwIZibuiak98h3PXViaiaSdibUMBickl6e1etPT8rzEicRcfic4DSS1cBib1iau09cILj1JOSf8lIgAzOo9D1EiaQ/640?wx_fmt=jpeg&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">此时<span style="color: black;">咱们</span>在录入出勤状态时，就<span style="color: black;">能够</span>直接在下拉列表中点击<span style="color: black;">选取</span>相应文本。</p>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">4、高亮<span style="color: black;">表示</span><span style="color: black;">反常</span>出勤状态</span></h1>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">当遇到非正常出勤，如迟到、早退等，则<span style="color: black;">能够</span>设置<span style="color: black;">区别</span>单元格颜色，来进行<span style="color: black;">明显</span><span style="color: black;">表示</span>。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">在<span style="color: black;">excel表格中对单元格的填充色进行自动和批量的设置，<span style="color: black;">一般</span>是利用<span style="color: black;">要求</span>格式</span>。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">这儿</span>作者<span style="color: black;">重点</span>是<span style="color: black;"><span style="color: black;">经过</span><span style="color: black;">要求</span>格式功能中“</span><strong style="color: blue;"><span style="color: black;">只为<span style="color: black;">包括</span>以下内容的单元格设置格式</span></strong><span style="color: black;">”选项，来进行颜色填充。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_jpg/iaFJlPwvgGwIZibuiak98h3PXViaiaSdibUMBicd3lw0wbk19VbicP0PWP0RHZ0CeSLcX0ricFC0ezF2WnAuu5T34pWzLNA/640?wx_fmt=jpeg&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">当然，<span style="color: black;">咱们</span><span style="color: black;">亦</span><span style="color: black;">能够</span><span style="color: black;">经过</span><span style="color: black;">要求</span>格式的公式表达来设置单元格格式。</p>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">5、统计员工<span style="color: black;">反常</span>出勤天数</span></h1>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">接下来是实时统计<span style="color: black;">每一个</span>员工本月的<span style="color: black;">反常</span>出勤次数。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">统计次数，<span style="color: black;">能够</span>利用countif函数，<span style="color: black;">亦</span><span style="color: black;">便是</span><span style="color: black;">要求</span>计数，输入公式为：</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">=COUNTIF($H10:$AL10,"迟到")+COUNTIF(H10:AL10,"早退")</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_jpg/iaFJlPwvgGwIZibuiak98h3PXViaiaSdibUMBicV25SyvhQnONsfaWNQiaEmeqFzvu8qRIFILQbTeoMNMNZYxoboiahw6uA/640?wx_fmt=jpeg&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">这儿</span>两个countif函数相加，是<span style="color: black;">由于</span>迟到和早退两种出勤状态被合并到<span style="color: black;">一块</span>进行统计，<span style="color: black;">因此呢</span><span style="color: black;">运用</span>了“或”的多<span style="color: black;">要求</span>关系计数表达。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">同理<span style="color: black;">统计请假，则<span style="color: black;">包括</span>病假、事假、婚假和产假，则无需<span style="color: black;">运用</span><span style="color: black;">以上</span>多<span style="color: black;">要求</span>相加表达，而是利用通配符来执行多关键字的单元格计数！<span style="color: black;">由于</span>它们有一个相同关键字，即“假”，<span style="color: black;">因此</span></span><strong style="color: blue;"><span style="color: black;">通配符表达为“*假”</span></strong><span style="color: black;">。</span></p>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">6、统计所有员工<span style="color: black;">反常</span>出勤次数</span></h1>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">最后是统计所有员工的<span style="color: black;">反常</span>出勤次数，<span style="color: black;">一样</span>是<span style="color: black;">运用</span>countif函数，只<span style="color: black;">不外</span>它的计数区域要相应的<span style="color: black;">调节</span>为<span style="color: black;">全部</span>出勤录入区域，则公式为：</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">=COUNTIF($H$10:$AL$18999,D5)</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_jpg/iaFJlPwvgGwIZibuiak98h3PXViaiaSdibUMBicdUDImuUTcIAXcChfGkq6oewTbibyb3hBRr0bPCknef9FCsfLU2Z7s4Q/640?wx_fmt=jpeg&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">它是对指定出勤状态在<span style="color: black;">全部</span>表格录入区域中的计数，得到所有员工的指定<span style="color: black;">反常</span>出勤天数。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">经过</span>以上几个<span style="color: black;">包括</span>公式和操作的<span style="color: black;">过程</span>，就能得到一个简洁美观的月份考勤表。关于它的表头，<span style="color: black;">能够</span>套用固定格式，<span style="color: black;">亦</span><span style="color: black;">能够</span><span style="color: black;">按照</span><span style="color: black;">需要</span>和<span style="color: black;">爱好</span>，来设置<span style="color: black;">区别</span>的文本描述或格式。</p>
“NB”（牛×的缩写，表示叹为观止）
页:
[1]