财务——Excel设计工资条（公式篇）
<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-axegupay5k/301efc9cbcab479d85d89d8cf37f3569~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727984890&x-signature=nj%2Bc1v99vCScZbeMmvxP%2F8%2BGjQE%3D" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">原始格式</p>
</div>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/23340e14a92241c3bb81c698dc674326~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727984890&x-signature=IV%2BqGEos3e0ljZFdI50%2BUcrDSN8%3D" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">工资条</p>
</div>
<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;">今天分享一个利用公式批量生成工资条办法，按图1内容录入好数据后，在J1单元格录入公式：=IF(MOD(ROW(),3)=1,A$1,IF(MOD(ROW(),3)=2,OFFSET(A$1,ROW()/3+1,0),""))。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/a3c894a978ad489fb630c650808ad824~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727984890&x-signature=UBvD%2Bh9FPBz4rP39GmLooDquGjs%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/c99460a1652146cf8fcea8f28da81799~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727984890&x-signature=KYHCjxA73aau0HYJoqleq6WaRXU%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/7844dddeec614987ba147ddaa6efe7a5~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727984890&x-signature=FjL1g0fqQNd37FUrwKQmRmP%2Ffl0%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>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">此公式的<span style="color: black;">重点</span>原理为利用当前行号除以3求余，得到一个1,2,0,1,2,0....1,2,0<span style="color: black;">持续</span>重复的序列，再用if判断与offset进行当前行号隔三行偏移一行原始数据行的原理实现，下篇分享另一种利用隔行排序办法制作工资条。</p>
i免费外链发布平台 http://www.fok120.com/ “NB”（牛×的缩写，表示叹为观止）
页:
[1]