excel动态考勤表制作教程
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">Excel制作“考勤表”，能更新日期，自动统计考勤表制作</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"> 手把手教你用Excel制作“考勤表”，能更新日期，自动统计考勤!</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>用Excel来制作一份强大的考勤表，不仅能记录考勤，还能够自动统计考勤。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"> <span style="color: black;">最后</span>演示</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"> 1.修改月份，日期会跟着自动变化。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"> 2.下拉菜单可<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;"> 3.自动统计出勤、迟到、早退、病假等天数<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;"><span style="color: black;">Excel<span style="color: black;">起始</span>制作考勤表</span></strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">1、先在单元格A1中输入考勤的年月份，<span style="color: black;">而后</span>添加姓名、星期、日期等基本信息。在C3单元格中输入公式：=A1。在D3单元格输入公式：=C3+1。输入完成后，选中D3单元格，向右填充到AG单元格。</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;">2、在C2单元格输入公式：=C3。<span style="color: black;">而后</span>继续向右填充。</span></span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">3、光标定位到C3单元格，<span style="color: black;">而后</span><span style="color: black;">运用</span>快捷键「Ctrl + Shift + 向右箭头」选中所有单元格，「右键」-「设置单元格格式」-「数字」-「自定义」，类型中输入「d」。</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>的再选中“星期”所有的单元格，「右键」-「设置单元格格式」-「数字」-「自定义」，类型中输入「aaa」。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;">美化考勤表</span></strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"> 4、给表格添加一下颜色和边框，<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;">5、自适应一下数字区域单元格<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;"><strong style="color: blue;"><span style="color: black;">自动更新日期设置</span></strong></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></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">6、选中“日期”和“星期”最后三个单元格，「<span style="color: black;">起始</span>」-「样式」-「</span><span style="color: black;"><span style="color: black;">要求</span>格式</span>」-「新建规则」-「<span style="color: black;">运用</span>公式确定要设置格式的单元格」，输入公式：=MONTH(AE2)>MONTH(AB2) 。</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></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"> 说明：month可获取日期月份，<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 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></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">7、在后面合并单元格添加考勤信息出勤、迟到、早退、病假等。</span></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;"><span style="color: black;"><span style="color: black;">8、添加考勤符号，√;●;※;△;Ο;×。</span></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 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></span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><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;">9、依次在出勤、迟到、早退、病假等下面单元格中输入公式：</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><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;">countif</span>(C4:AG4,"√")+AI4+AJ4</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><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;"> =COUNTIF(C4:AG4,"●")</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><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;"> =COUNTIF(C4:AG4,"※")</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><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;"> =COUNTIF(C4:AG4,"△")</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><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;"> =COUNTIF(C4:AG4,"Ο")</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><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;">=COUNTIF(C4:AG4,"×")</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 style="color: black;">咱们</span>来<span style="color: black;">瞧瞧</span>统计效果。</span></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 style="color: black;">咱们</span>的考勤表<span style="color: black;">已然</span><span style="color: black;">所有</span>制作完成，最后打开来<span style="color: black;">瞧瞧</span>效果。</span></span></p>
你的言辞如同繁星闪烁，点亮了我心中的夜空。 我深受你的启发，你的话语是我前进的动力。 我赞同你的看法，你的智慧让人佩服，谢谢分享。
页:
[1]