快速生成工资条三种办法
<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>工资条有<strong style="color: blue;"><span style="color: black;">一行表头</span></strong>用<strong style="color: blue;"><span style="color: black;">排序法</span></strong>，<span style="color: black;">倘若</span>有<strong style="color: blue;"><span style="color: black;">两行表头就</span></strong>用<strong style="color: blue;"><span style="color: black;">定位法</span></strong>，第三种是<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;">第1</span>种：“排序法”</h1>从工资表里复制一份工资数据出来做工资条。<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/b5ea23be8fe44641b82d278f7a5f5bf1~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727986207&x-signature=Cc1HWgc7LLFTyETqONhvYyd3m1o%3D" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">1、复制工资表</p>
</div>在序号前面<span style="color: black;">插进</span>一列辅助列→有多少行表头就在下方复制多少<span style="color: black;">秩序</span>号和表头→表格里有一行表头就复制一次。<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/c5cbaebd388f4a7796932bc41e85099d~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727986207&x-signature=UsxwRHh34RjMqwjHpRcTEEheSqs%3D" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">2、<span style="color: black;">插进</span>辅助列</p>
</div>鼠标单击辅助列这一个单元格→再点工具栏上数据→再点击排序。<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p26-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/50ed846e4bc54d3b93ae549cbb3ee9d3~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727986207&x-signature=6ZJa69bFeukSua7wOpLsACNFKV8%3D" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">3、按辅助列排序</p>
</div>表格排序后，再删除A列辅助列，就能得到如下工资条，再把多余表头删除。<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p26-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/f1176722d7cc4c52bc3d87487cfc4a4c~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727986207&x-signature=W5ylihGhwd%2BZ9uKN0byDXjg2Eag%3D" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">4、删除A列辅助列和多余表头。</p>
</div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">第二种：“多表头定位法”</h1>复制工资表→<span style="color: black;">插进</span>辅助列→有两行表头就复制2<span style="color: black;">秩序</span>号（1-3）<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/abe73f4b3c014367bbde1401ef9b4ed9~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727986207&x-signature=c%2BuQ5uG95rphXShi00BCF3YQ2i4%3D" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">1、复制工资表和插入辅助列</p>
</div>选中黄色区域（不<span style="color: black;">包括</span>表头）→点击工具栏数据→排序→自定义排序→<span style="color: black;">重点</span>关键字选列A→确定。<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/e87da962c8fe458389888b405344d367~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727986207&x-signature=D%2B12RqQckOZM%2FIQ3aKdZofQpAhI%3D" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">2、按列A排序（<span style="color: black;">不可</span><span style="color: black;">包括</span>表头）</p>
</div>复制表头→选中黄色区域→再按CTRL+G定位→<span style="color: black;">选取</span>空值→粘贴→再删除辅助列→删除最后一列多余表头<span style="color: black;">就可</span>。<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/d112a69359a7426893902fd71aed031e~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727986207&x-signature=xMOMzZTHIIZGLRFFc0FWD01moho%3D" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">3、复制表头+定位+粘贴</p>
</div><span style="color: black;">倘若</span>想要工资条之间有一行空格方便裁剪，就相当于有三行表头就复制三次，后面<span style="color: black;">过程</span>跟前面<span style="color: black;">同样</span>。<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/64055cdb470c4111bac7e42ad314ff88~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727986207&x-signature=6jCo6ZeEr%2BC5jI7TUchJnfuuXhk%3D" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">4、三行表头只是多了在工资表上多<span style="color: black;">插进</span>一行这一<span style="color: black;">过程</span>。</p>
</div><span style="color: black;">能够</span>先<span style="color: black;">调节</span>好<span style="color: black;">第1</span>个人工资条的行高→再选中1-4行→再点格式刷→再按Ctrl+Shift+ ↓ 就<span style="color: black;">所有</span><span style="color: black;">同样</span>格式了。<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/475866a7f8604d3ab988eed24517c269~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727986207&x-signature=Khi%2F%2FgQWjtgbTc606CajRreQfCI%3D" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">5、工资条格式<span style="color: black;">调节</span></p>
</div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">第三种：“公式法”</h1>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/38ab4c79114148ccb1fa09c22936c58d~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727986207&x-signature=Wm9AWryA2l8NBLgMAauBo6267Ow%3D" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">1、工资表</p>
</div>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p26-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/9045666fa4634339ab76ae818eb10694~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727986207&x-signature=S%2BAhc6QlkjaV9%2Fs3Zv7jbdq8a0Q%3D" style="width: 50%; margin-bottom: 20px;">
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">2、工资条</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;">INDEX（<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;">ROW（<span style="color: black;">返回表格对应的行数</span>）</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">工资条里A3=INDEX(<span style="color: black;">月薪工资表!A:A</span>,<span style="color: black;">ROW()/4+4</span>,<span style="color: black;">1</span>)意思是：<span style="color: black;">查询</span>“工资表” 第4行 第1列的数据。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">工资条里<span style="color: black;">A3=ROW（）=3</span>，对应的是第3行，怎么转换成<span style="color: black;">查询</span>工资表里第4行?</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">用<span style="color: black;">A3=ROW（）/4</span>=3/4输出结果是0行。工资条有4行，A3=<span style="color: black;">ROW()/4+4</span><span style="color: black;">亦</span><span style="color: black;">便是</span>0+4=4返回第4行。</p>公式能大大<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>有误。
你的话深深触动了我，仿佛说出了我心里的声音。 你的见解独到，让我受益匪浅，非常感谢。 我赞同你的看法，你的智慧让人佩服，谢谢分享。 楼主发的这篇帖子，我觉得非常有道理。
页:
[1]