Excel工资条制作办法大全，3大类别7种区别形式
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">图文/赵志东(来自Excel精英培训<span style="color: black;">微X</span>平台)</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">小小工资条，牵动着无数工薪族的小神经。而制作工资条，<span style="color: black;">亦</span>是人资或财务的最常用excel操作技巧。今天兰色带<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;"><strong style="color: blue;"><span style="color: black;">1、</span>手工1次性制作</strong></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>下月还要用，还需要重复操作。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">1、排序法</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">在辅助列中输入1.2.3...再复制该序列到下面的行。在表格下粘贴相同行数的标题行，<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;"><img src="http://mmbiz.qpic.cn/mmbiz/4jUToJBtQvRZsBpfI0HcRZWhK90zXU0qq34ZJIZPKh2HdjvpP9B8tK6Cax1ug7jkrupRPjWyTmevIHgORsnLfQ/0?wx_fmt=gif&tp=webp&wxfrom=5&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;"><strong style="color: blue;">2、<span style="color: black;">插进</span>行法（<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>定位常量，<span style="color: black;">插进</span>整行。<span style="color: black;">而后</span>复制标题行，再定位第1列空值，粘贴标题行。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="http://mmbiz.qpic.cn/mmbiz/4jUToJBtQvRZsBpfI0HcRZWhK90zXU0qTwcUNMNlpuZssgewsQwd5G1cwcpviav3MlpPfxgSE9K7SqhIm6KicsHQ/0?wx_fmt=gif&tp=webp&wxfrom=5&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;"><strong style="color: blue;">3、<span style="color: black;">插进</span>行法（<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>是标题行含合并单元格的2行标题，则在拖入行后再次定位<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>2</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="http://mmbiz.qpic.cn/mmbiz/4jUToJBtQvRZsBpfI0HcRZWhK90zXU0qdjecupD3ibbVPqWrSroNGKgo056IWYVkMdwQf5unKUC7s9CuXGDcDgg/0?wx_fmt=gif&tp=webp&wxfrom=5&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;"><strong style="color: blue;"><span style="color: black;">2、</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;"><strong style="color: blue;">1、替换生成引用法。</strong></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>粘贴标题行。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="http://mmbiz.qpic.cn/mmbiz/4jUToJBtQvRZsBpfI0HcRZWhK90zXU0qbMGTyf8RDRoRQy1iaBFia7YjARia6ZGP3G3046hIe1vlv9Sa7G44PkYjQ/0?wx_fmt=gif&tp=webp&wxfrom=5&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;"><strong style="color: blue;">2、Vlookup函数法。</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">复制标题行到第8行 - 在第1列输入1，<span style="color: black;">而后</span>B列输入公式，并向右复制</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">=VLOOKUP($A10,$A$1:$G$6,COLUMN(B1),0)</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>，向下拖动复制。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="http://mmbiz.qpic.cn/mmbiz/4jUToJBtQvRZsBpfI0HcRZWhK90zXU0qGsMDXv5fNdwjIVSJ4ESRNXTCA2pxhrMprEMfflulxggfHwtD0Eu6xQ/0?wx_fmt=gif&tp=webp&wxfrom=5&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;"><strong style="color: blue;"><span style="color: black;">3、</span>函数公式法</strong></span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">1、单表生成</strong></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;">IF(MOD(ROW(A1),2),OFFSET(</span><span style="color: black;">$A$1</span><span style="color: black;">,(MOD(ROW(A1)-1,3)>0)*ROUND(ROW(A1)/3,),COLUMN(A2)-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;">办法</span></strong>：只需要修改上面公式的<span style="color: black;">$A$1</span> ，它是工资表的标题行的第1个单元格<span style="color: black;">位置</span>（<span style="color: black;">倘若</span>引用其他表，需要加上“表名!”，如<span style="color: black;">工资表!$A$1</span>）。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="http://mmbiz.qpic.cn/mmbiz/4jUToJBtQvRZsBpfI0HcRZWhK90zXU0qPy9DO7sALDBH7Q36uVwFbyfsM6LicTm2O05uCy5MxzHZT6e49H6ibj1g/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&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;"><strong style="color: blue;">2、多表智能生成</strong></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>直接拿去<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><span style="color: black;">经过</span><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;"><img src="http://mmbiz.qpic.cn/mmbiz/4jUToJBtQvTex8QnhTpCPYNjsIhP56covUqLPs4QpIIxyUoichXPc7N1Mp0GvM5eA2PcpgQ1e8sfrS6eFDswEcQ/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&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;"><img src="http://mmbiz.qpic.cn/mmbiz/4jUToJBtQvTex8QnhTpCPYNjsIhP56coc5KTksC9BfaAHgW8TAbylKkCqSnujH0nrAibXxsY5nUxIRaeLrj0GGQ/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&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><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;"><img src="http://mmbiz.qpic.cn/mmbiz/4jUToJBtQvTex8QnhTpCPYNjsIhP56coYW9WfD0C0ZB18Wyt3WC2RM3JEbHB7eu177aZw0z3gORdBX1aWicJ8Yw/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&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>指定标题行所在的行数（有的工资表标题行不<span style="color: black;">必定</span>在第1行）如下图所示。（<span style="color: black;">新增功能</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>：（粘到电脑浏览器<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;">http://www.excelpx.com/home/upload/2015_04/temp_15042418244223.zip</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 style="color: black;">朋友</span></span></strong><span style="color: black;">，<span style="color: black;">能够</span>扫下面的二维码快速关注（</span><span style="color: black;"><span style="color: black;">亦</span><span style="color: black;">能够</span><strong style="color: blue;">长按</strong>二维码<span style="color: black;">照片</span>，在弹出的菜单上点“<span style="color: black;">识别图中二维码</span>”<span style="color: black;">而后</span>点关注</span><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;"><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>
楼主节操掉了，还不快捡起来！
页:
[1]