论坛 › 宁夏 › Excel教程：智能考勤表，MM你再亦不消加班了！

1fy07h 发表于 2024-10-2 14:43:07

Excel教程：智能考勤表，MM你再亦不消加班了！

    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;">微X</span>扫码观看全套Excel、Word、PPT视频</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_png/b96CibCt70iaajvl7fD4ZCicMcjhXMp1v6UibM134tIsO1j5yqHyNhh9arj090oAL7zGhRJRq6cFqFOlDZMleLl4pw/640?tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;wx_co=1" style="width: 50%; margin-bottom: 20px;"><img src="https://mmbiz.qpic.cn/mmbiz_png/b96CibCt70iaajvl7fD4ZCicMcjhXMp1v6UibM134tIsO1j5yqHyNhh9arj090oAL7zGhRJRq6cFqFOlDZMleLl4pw/640?tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;wx_co=1" style="width: 50%; margin-bottom: 20px;"><img src="https://mmbiz.qpic.cn/mmbiz_png/b96CibCt70iaajvl7fD4ZCicMcjhXMp1v6UibM134tIsO1j5yqHyNhh9arj090oAL7zGhRJRq6cFqFOlDZMleLl4pw/640?tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;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;"><img src="https://mmbiz.qpic.cn/mmbiz_jpg/k6kCsib3eJMwB2CMISauYufg1DG3oNjj5RQKALIgnfgesEwicF85FgNBQOBnbfbotH9RicYgJEKpKEcubHxCiciayog/640?wx_fmt=jpeg&amp;tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;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;"><img src="https://mmbiz.qpic.cn/mmbiz_jpg/k6kCsib3eJMzttwMKtgQYUTSWVTMAqW6y6Izqico99HeontrFSWJk4Piaafpu0ICRTStFxpJpj8ordbTnIIVib7q3A/640?tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;wx_co=1" style="width: 50%; margin-bottom: 20px;"></span><span style="color: black;">文：老菜鸟|excel教程</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;"><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></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 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></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_gif/k6kCsib3eJMzttwMKtgQYUTSWVTMAqW6yItBX6YuLJIu8wGwUmrh2zicyiao5Hh5OGVzFxDydf0eyFzb4icmxyVw7A/640?tp=webp&amp;wxfrom=5&amp;wx_lazy=1" style="width: 50%; margin-bottom: 20px;"><span style="color: black;">QQ群：</span><span style="color: black;">885401280</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;"><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;">当月天数会自动变化，日期会<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></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;">下面就来教<span style="color: black;">大众</span>如何做出<span style="color: black;">这般</span>一个表格，会<span style="color: black;">触及</span>到<span style="color: black;">非常多</span>《Excel极速贯通班》里讲过的知识点。</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/k6kCsib3eJMzttwMKtgQYUTSWVTMAqW6ybyAUICViak41maF8U7Las8xcuBbOaZ9jq4SsDoLqEFn21z3wb6uiaG3w/640?tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;wx_co=1" style="width: 50%; margin-bottom: 20px;"><strong style="color: blue;">考勤主体的制作</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></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_png/k6kCsib3eJMzttwMKtgQYUTSWVTMAqW6ybcR7R2DQmcTGzvJwuaGqsCGXEsGm75DvvzHTw3rbGaicMSdfZl5rWlA/640?tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;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;">QQ群：316492581下载配套练习课件</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></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;">第1</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/k6kCsib3eJMzttwMKtgQYUTSWVTMAqW6yJ19lwvUFqKhjRNMg7Iq8zQDO8WFmcV51zs8GhtP3oEOdDV9p4icHFNg/640?tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;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 style="color: black;">第1</span>行的合并单元格里点击右键，<span style="color: black;">选取</span>“设置单元格格式”。在弹出的对话框中自定义格式为：鑫盛<span style="color: black;">机构</span>0月份考勤统计表。</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_png/k6kCsib3eJMzttwMKtgQYUTSWVTMAqW6ytjjYEpwibfibicDVAspQLBgibK2Cp5Qa9OKpf2aGsF3WQ4icucZOmnHpxqA/640?tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;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;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">重视</span>，<span style="color: black;">这儿</span>是数字0不是字母O</span></strong><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;">第1</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/k6kCsib3eJMzttwMKtgQYUTSWVTMAqW6yIcLRib64ibkcCtSfZsWGjHdibe7CYdE4hsVKdNyYmO5wArdZJmn9JOxug/640?tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;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 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></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">=DAY(EDATE(DATE(2018,A1,1),1)-1)</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_png/k6kCsib3eJMzttwMKtgQYUTSWVTMAqW6y7p7RHQyJfzlVib56h0g92c86Be173kCUszcBdU327rWVf5jyUPNwkuA/640?tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;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>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;">1.</span></strong><span style="color: black;">DATE函数的格式为：DATE(年、月、日)，<span style="color: black;">亦</span><span style="color: black;">便是</span><span style="color: black;">按照</span>指定的年月日得到一个日期，在本例中，年份为2018（<span style="color: black;">倘若</span>是下一年度的话，改为2019<span style="color: black;">就可</span>），月份取的是<span style="color: black;">第1</span>行输入的数字，日就取1，<span style="color: black;">这般</span>得到的日期<span style="color: black;">便是</span>考勤表当月的1号。</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;">2.</span></strong><span style="color: black;">EDATE函数的格式为：EDATE（<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>当月1日，相隔月数为1<span style="color: black;">暗示</span>次月1日这个日期。</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;">3.</span></strong><span style="color: black;">DAY函数的格式为：DAY（日期），得到指定日期是当月的第几天，在本例中指定日期是次月1日前1天（EDATE函数后面有个减1），换句话说，<span style="color: black;">便是</span>当月的最后一天。再<span style="color: black;">经过</span>DAY函数得到当月一共多少天。</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 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>星期了，在C4单元格填入公式：</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">=TEXT(DATE(2018,$A1,C3),"aaa")，右拉<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_png/k6kCsib3eJMzttwMKtgQYUTSWVTMAqW6y64dhFNickq0JSMeMhty0qibhw6XtLWB4T8RK87e4vjyYmt3v16oPxZpA/640?tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;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;">公式=TEXT(DATE(2018,$A$1,C$3),"aaa")中用到了两个函数TEXT和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;">DATE函数刚才<span style="color: black;">已然</span>讲过了，在这个公式中，年还是2018，月用的A1，<span style="color: black;">由于</span>公式要向右拉，为了防止右拉的时候A1<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;">TEXT函数的格式为：TEXT（要指定格式的数据，格式代码）。这个函数算是一个比较高级的函数了，虽然结构比较简单，<span style="color: black;">然则</span>格式代码非常多，<span style="color: black;">因此</span><span style="color: black;">亦</span>是一个多功能函数。本例中的格式代码为“aaa”，<span style="color: black;">便是</span>用一个字来<span style="color: black;">表示</span>星期，有兴趣的读者<span style="color: black;">能够</span>自己试试代码“aaaa”“ddd”和“dddd”分别是什么效果吧。</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>TEXT函数的时候，格式代码必须加引号（引号在英文状态输入）。</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 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;"><img src="https://mmbiz.qpic.cn/mmbiz_gif/k6kCsib3eJMzttwMKtgQYUTSWVTMAqW6y44licsjRLK4mCjSiay8uqP23JKq3D8rk0sr04kVfB13d4oFdjKQSDMCQ/640?tp=webp&amp;wxfrom=5&amp;wx_lazy=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 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></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_png/k6kCsib3eJMzttwMKtgQYUTSWVTMAqW6yXicXqtT0g8wibV4YMJL4EIBLK9XZt6vf2eiby5dmKk9S0nuL3EoSRnTWQ/640?tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;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;"><img src="https://mmbiz.qpic.cn/mmbiz_jpg/k6kCsib3eJMzttwMKtgQYUTSWVTMAqW6yGG0pg85cDcqsV1E6194sDNdkiapb49TgmOMWnVRdVDibibnxuzVbtBSYA/640?tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;wx_co=1" style="width: 50%; margin-bottom: 20px;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">运用</span><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;">接下来需要完成两项工作：</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">1.让表格中的日期<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;">2.考勤数据<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 style="color: black;">第1</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></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_png/k6kCsib3eJMzttwMKtgQYUTSWVTMAqW6yiaDUN5hO5f8kXSA6aPzqzlzsJMKBFClVlCia7jIOqlmVpcwEtSu4dfibA/640?tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;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 style="color: black;">选取</span>【使用公式确定要设置格式的单元格】，输入公式：<strong style="color: blue;">=C$3&gt;$AF$2</strong>，<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;"><img src="https://mmbiz.qpic.cn/mmbiz_png/k6kCsib3eJMzttwMKtgQYUTSWVTMAqW6y8nkwnj9xSzcgibfu6jZqzyibu0QPB4Ba7cBKN8yvEWaTp5DvlicDgTM9g/640?tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;wx_co=1" style="width: 50%; margin-bottom: 20px;"></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></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>【自定义】，在类型中输入三个分号：<strong style="color: blue;">;;;</strong>，<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_png/k6kCsib3eJMzttwMKtgQYUTSWVTMAqW6yqZzky3bGyxA4PsrHx2zGAXUlQbqW7lfL0PfAVq5gj0cnrUJtc7PqiaQ/640?tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;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 style="color: black;">繁杂</span>，记住<strong style="color: blue;">输入三个分号<span style="color: black;">表率</span>不<span style="color: black;">表示</span>单元格的内容。</strong></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></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/k6kCsib3eJMzttwMKtgQYUTSWVTMAqW6yBm94yR7IlMm7PA1iahpQG1IOw28YXZgkaOWPqpNxa0tsJXu7Bgp8oLg/640?tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;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 style="color: black;">选取</span>数据区域，新建规则，输入公式=OR(C$4="六",C$4="日")后设置格式：</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_png/k6kCsib3eJMzttwMKtgQYUTSWVTMAqW6y0SwpiaLFBAZmRAHD9RLIBnuGJ7yVlibM06zvb3sodBqJtoNSUwE7kXGg/640?tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;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 style="color: black;">亦</span>很好理解，OR为满足两个<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 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_png/k6kCsib3eJMzttwMKtgQYUTSWVTMAqW6yaHHiavAic4eV1DMpDlm9OKWRHEgvsP6M8efUia6IVog6iaYRbibmlnw8oNQ/640?tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;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 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_png/k6kCsib3eJMzttwMKtgQYUTSWVTMAqW6ySchaBD8KNsVUbf5X8vXI9NGV2F39A8Lhz3hjG9sZHZcNeZXe5kYIRg/640?tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;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 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 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;"><img src="https://mmbiz.qpic.cn/mmbiz_png/k6kCsib3eJMzttwMKtgQYUTSWVTMAqW6yXKrQaUyqeXOadJBInapbMVyfzagkHw3sVau7g75MVzJJ4rJOTwpsHA/640?tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;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 style="color: black;">倘若</span>为真则停止”，<span style="color: black;">而后</span>点击上移按钮</span><img src="https://mmbiz.qpic.cn/mmbiz_jpg/k6kCsib3eJMzttwMKtgQYUTSWVTMAqW6yr1NfufyGHJYuL33HCX5zaUibmemCJ8xsLf8m84aictqSxDIrZiasYAaMQ/640?tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;wx_co=1" style="width: 50%; margin-bottom: 20px;"><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_png/k6kCsib3eJMzttwMKtgQYUTSWVTMAqW6yiamIPia8HnKtahibwaSUibXRat0cZyTj7Zlc92SetRjfYmlTutibiaH9RlYA/640?tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;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>
    <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;"><img src="https://mmbiz.qpic.cn/mmbiz_jpg/k6kCsib3eJMzttwMKtgQYUTSWVTMAqW6yrWlT3ia9nVxibNkAKkwEUKO0jISQHZmcNQx8KwLK82KGxewdGugjIPOQ/640?tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;wx_co=1" style="width: 50%; margin-bottom: 20px;"><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>COUNTIF函数完成统计，公式为：</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">=COUNTIF($C5:$AG6,AI$4)/2：</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_png/k6kCsib3eJMzttwMKtgQYUTSWVTMAqW6yKXDJh33bglwpyQ9gpeFgPRDlHN5ovR36q8mq5vbnlqWJmeia2NAZM4Q/640?tp=webp&amp;wxfrom=5&amp;wx_lazy=1&amp;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;">COUNTIF函数的格式为：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>例子中<span style="color: black;">每日</span>的数据是两行，<span style="color: black;">因此</span>统计结果要除以2。</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 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="data:image/svg+xml,%3C%3Fxml version=1.0 encoding=UTF-8%3F%3E%3Csvg width=1px height=1px viewBox=0 0 1 1 version=1.1 xmlns=http://www.w3.org/2000/svg xmlns:xlink=http://www.w3.org/1999/xlink%3E%3Ctitle%3E%3C/title%3E%3Cg stroke=none stroke-width=1 fill=none fill-rule=evenodd fill-opacity=0%3E%3Cg transform=translate(-249.000000, -126.000000) fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E" 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 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></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><strong style="color: blue;">加号主<span style="color: black;">微X</span>，免费</strong></span><strong style="color: blue;"><span style="color: black;">领取Excel技巧视频学习</span></strong></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="data:image/svg+xml,%3C%3Fxml version=1.0 encoding=UTF-8%3F%3E%3Csvg width=1px height=1px viewBox=0 0 1 1 version=1.1 xmlns=http://www.w3.org/2000/svg xmlns:xlink=http://www.w3.org/1999/xlink%3E%3Ctitle%3E%3C/title%3E%3Cg stroke=none stroke-width=1 fill=none fill-rule=evenodd fill-opacity=0%3E%3Cg transform=translate(-249.000000, -126.000000) fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E" style="width: 50%; margin-bottom: 20px;"><img src="data:image/svg+xml,%3C%3Fxml version=1.0 encoding=UTF-8%3F%3E%3Csvg width=1px height=1px viewBox=0 0 1 1 version=1.1 xmlns=http://www.w3.org/2000/svg xmlns:xlink=http://www.w3.org/1999/xlink%3E%3Ctitle%3E%3C/title%3E%3Cg stroke=none stroke-width=1 fill=none fill-rule=evenodd fill-opacity=0%3E%3Cg transform=translate(-249.000000, -126.000000) fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E" style="width: 50%; margin-bottom: 20px;"><img src="data:image/svg+xml,%3C%3Fxml version=1.0 encoding=UTF-8%3F%3E%3Csvg width=1px height=1px viewBox=0 0 1 1 version=1.1 xmlns=http://www.w3.org/2000/svg xmlns:xlink=http://www.w3.org/1999/xlink%3E%3Ctitle%3E%3C/title%3E%3Cg stroke=none stroke-width=1 fill=none fill-rule=evenodd fill-opacity=0%3E%3Cg transform=translate(-249.000000, -126.000000) fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E" style="width: 50%; margin-bottom: 20px;"></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="data:image/svg+xml,%3C%3Fxml version=1.0 encoding=UTF-8%3F%3E%3Csvg width=1px height=1px viewBox=0 0 1 1 version=1.1 xmlns=http://www.w3.org/2000/svg xmlns:xlink=http://www.w3.org/1999/xlink%3E%3Ctitle%3E%3C/title%3E%3Cg stroke=none stroke-width=1 fill=none fill-rule=evenodd fill-opacity=0%3E%3Cg transform=translate(-249.000000, -126.000000) fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E" style="width: 50%; margin-bottom: 20px;"></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;">1星期</span>Excel直通车》视频课<span style="color: black;">或</span>《Excel极速贯通班》。</span></strong></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><strong style="color: blue;">《<span style="color: black;">1星期</span>Excel直通车》</strong></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>Excel技巧、函数公式、</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></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">最实用接地气的Excel视频课</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><strong style="color: blue;"><span style="color: black;">《<span style="color: black;">1星期</span>Excel直通车》</span></strong></span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><strong style="color: blue;"><span style="color: black;">风趣易懂，快速<span style="color: black;">有效</span>，</span></strong></span><span style="color: black;"><strong style="color: blue;"><span style="color: black;">带您7天学会Excel</span></strong></span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><strong style="color: blue;">38&nbsp;</strong></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;"><strong style="color: blue;"><span style="color: black;"><strong style="color: blue;"><span style="color: black;"><strong style="color: blue;"><span style="color: black;"><strong style="color: blue;"><span style="color: black;"><strong style="color: blue;"><span style="color: black;">（已更新完毕，可永久学习）</span></strong></span></strong></span></strong></span></strong></span></strong></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;"><strong style="color: blue;"><span style="color: black;">主讲老师：滴答</span></strong></span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="data:image/svg+xml,%3C%3Fxml version=1.0 encoding=UTF-8%3F%3E%3Csvg width=1px height=1px viewBox=0 0 1 1 version=1.1 xmlns=http://www.w3.org/2000/svg xmlns:xlink=http://www.w3.org/1999/xlink%3E%3Ctitle%3E%3C/title%3E%3Cg stroke=none stroke-width=1 fill=none fill-rule=evenodd fill-opacity=0%3E%3Cg transform=translate(-249.000000, -126.000000) fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E" 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;">Excel技术大神，资深培训师；</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">课程粉丝100万+；</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>有《Excel小白脱白系列课》</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"> &nbsp; &nbsp; &nbsp; &nbsp;《Excel极速贯通班》。</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">原价299元</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 style="color: black;"><strong style="color: blue;"><span style="color: black;">99</span></strong></span></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></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>的Excel职场技能！</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><strong style="color: blue;"><span style="color: black;">&nbsp; 长按下面二维码立即购买学习</span></strong></span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="data:image/svg+xml,%3C%3Fxml version=1.0 encoding=UTF-8%3F%3E%3Csvg width=1px height=1px viewBox=0 0 1 1 version=1.1 xmlns=http://www.w3.org/2000/svg xmlns:xlink=http://www.w3.org/1999/xlink%3E%3Ctitle%3E%3C/title%3E%3Cg stroke=none stroke-width=1 fill=none fill-rule=evenodd fill-opacity=0%3E%3Cg transform=translate(-249.000000, -126.000000) fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E" 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 style="color: black;">微X</span>：603830039领取练习课件</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;"><img src="data:image/svg+xml,%3C%3Fxml version=1.0 encoding=UTF-8%3F%3E%3Csvg width=1px height=1px viewBox=0 0 1 1 version=1.1 xmlns=http://www.w3.org/2000/svg xmlns:xlink=http://www.w3.org/1999/xlink%3E%3Ctitle%3E%3C/title%3E%3Cg stroke=none stroke-width=1 fill=none fill-rule=evenodd fill-opacity=0%3E%3Cg transform=translate(-249.000000, -126.000000) fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E" style="width: 50%; margin-bottom: 20px;"></span></strong></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">让工作提速百倍的「Excel极速贯通班」</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">↓&nbsp;点击<span style="color: black;"><strong style="color: blue;"><span style="color: black;">阅读原文</span></strong></span>，可直接购买。</span></p>

7wu1wm0 发表于 4 天前

感谢你的精彩评论，为我的思绪打开了新的窗口。

查看完整版本: Excel教程：智能考勤表，MM你再亦不消加班了！