制作动态考勤表
<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>设计一个动态的表格</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-axegupay5k/0bfe232c93b6490f803bb74eb159beaf~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727979960&x-signature=Icld4qweLpFJXc94zPqZouWC9s0%3D" style="width: 50%; margin-bottom: 20px;"></div>
<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>在B3列输入 =DATE(B2,E2,"1")，并设置其单元格格式为m/d（月/天），在B4列输入<span style="color: black;">一样</span>的公式，<span style="color: black;">或</span>直接用 =B3 ，设置其单元格格式为 aaa（周*），<span style="color: black;">而后</span>向右拉动，填充31列，<span style="color: black;">每一个</span>月最多有31天<span style="color: black;">吗</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p26-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/89e0fe2a43a544fc9974e6b0054e1267~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727979960&x-signature=oo7gb05Hex%2Fus0rv%2BP%2FrTty%2F6pk%3D" style="width: 50%; margin-bottom: 20px;"></div>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/d421eba1b38d4a1a99a3672a617a4ccd~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727979960&x-signature=mAP5m72O4ew6jo0nCvaDHrR5BrE%3D" style="width: 50%; margin-bottom: 20px;"></div>
<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>到了最右侧就出bug了，<span style="color: black;">因为</span>有些月份少于31天，它就会填充下个月的日期</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/5b44a1950f47434d982784ed1bd6f78f~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727979960&x-signature=4smyAfjnkwuY78D3%2FUJjWf%2FC6sA%3D" style="width: 50%; margin-bottom: 20px;"></div>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p26-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/6b482db67903417682a4b9fe78d0175f~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727979960&x-signature=YHVQoRwgkST24PlekxLsyNb3DeQ%3D" style="width: 50%; margin-bottom: 20px;"></div>
<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>判断27号之后的日期<span style="color: black;">是不是</span>是当月的（<span style="color: black;">由于</span><span style="color: black;">每一个</span>月都会有27天，<span style="color: black;">那样</span><span style="color: black;">为何</span><span style="color: black;">不消</span>28日去判断呢，演示的时候忘了<span style="color: black;">吗</span>），<span style="color: black;">咱们</span><span style="color: black;">能够</span><span style="color: black;">经过</span>公式 =IF(AB3<>"",IF(MONTH(AB3+1)<=$E$2,AB3+1,""),"") 来实现，其中AB3+1即<span style="color: black;">暗示</span>27日之后的一天，加上month函数<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>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/f0ba919da35c4b4b9e6736ba47a75449~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727979960&x-signature=qWdvm4%2BnPuhDSWK9LBnDtGaQzuI%3D" style="width: 50%; margin-bottom: 20px;"></div>
<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>看起来会比较low，还是<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></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/c2dd5f4911f84ee0a754f03163165999~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727979960&x-signature=z7yZHl1VdLQJ4SLqR3OjwKK%2FY0k%3D" style="width: 50%; margin-bottom: 20px;"></div>
<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>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/255accee399e4f98ad6441c9f2a9fe69~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727979960&x-signature=Pszd6PyD4f4vEmAaoajJB3QqE7o%3D" style="width: 50%; margin-bottom: 20px;"></div>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/6fc96bf67f8348029d995e0da91a001e~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727979960&x-signature=6MzZzojrUq%2FvX8nzcqNHaj3Idao%3D" style="width: 50%; margin-bottom: 20px;"></div>
<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>自己的excel导航菜单里<span style="color: black;">无</span><span style="color: black;">研发</span>工具，其实是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>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/b3092cb22abd42fcb89cfdd1ce1c8563~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727979960&x-signature=VJarZxg7ePZuZpawm1cte0aV%2Fz8%3D" style="width: 50%; margin-bottom: 20px;"></div>
<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;"><span style="color: black;">咱们</span>选中要着色的区域，点<span style="color: black;">起始</span>—><span style="color: black;">要求</span>格式—>新建规则，<span style="color: black;">要求</span>为=WEEKDAY(A$3,2)>5，weekday函数即为判断某个日期是星期几，第二个参数是说星期一是对应数字几，<span style="color: black;">倘若</span>第二个参数为1则星期一对应数字0，<span style="color: black;">咱们</span>对符合这个<span style="color: black;">要求</span>格式的填充颜色，确定OK了</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/0200e1a50a8146bc80e009e8e8e56d6a~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727979960&x-signature=wq6V1EY4hF4MbEWsImk8YxbwM9k%3D" style="width: 50%; margin-bottom: 20px;"></div>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/2b4c758e2af14bfaba6ba165bd19dede~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727979960&x-signature=yY54q6eFnDW%2FBTLclex9WA7eYOg%3D" style="width: 50%; margin-bottom: 20px;"></div>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/66fa2ce3661d450186bfa1a2163389dc~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727979960&x-signature=Ff%2FAlOq7ruc%2FSa7vaub0BXKsWd4%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">瞧瞧</span>效果</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/05850780edfc4d378a0a87b47c3fe1cf~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727979960&x-signature=cbd9ksGONioyhqVbOzaB%2BiAec7o%3D" style="width: 50%; margin-bottom: 20px;"></div>
可以发布外链的网站 http://www.fok120.com/ 谢谢、感谢、感恩、辛苦了、有你真好等。
页:
[1]