219mze 发表于 2024-10-2 15:15:34

利用Excel函数快速巧妙制作工资条


    <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>非易事。网上有不少利用EXCEL制作工资条的<span style="color: black;">办法</span>，今天鉴水鱼老师跟<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>能让刚入职场的小白进一步领略Excel函数强大的功能。</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-axegupay5k/7a20b922b0334a49991161bb906a55b1~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727984904&amp;x-signature=U7TtLH2OynwckycuMGMrAnrPV%2BI%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>如下图2所示的一张<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/d475c3dd909f469dbed8767d23989c40~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727984904&amp;x-signature=rlOcve8d4NY5Ddh4Xq9oCrkbjGA%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;"><span style="color: black;">此刻</span><span style="color: black;">咱们</span>要制作如下图3所示的带裁剪虚线的工资条。</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/2f7438ba2af246e1a3a793c9efec88ef~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727984904&amp;x-signature=lUDN5RE1YY4zrKfz6SNClVSe2U8%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>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">1、</span><span style="color: black;">运用</span>VLOOKUP函数制作工资条</h1>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">1、如下图4所示，在A2单元格中输入员工编号YG001，<span style="color: black;">而后</span>在B2单元格中输入公式：</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">【=VLOOKUP($A2,Sheet1!$A$1:$I$11,2,0)】</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">按【Enter】键即得计算公式结果，如下图4所示。</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/7417fc8e214e4c829335512479e263da~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727984904&amp;x-signature=y9ta3o3hBtzHFDxRdyF1x%2FM5Y8Y%3D" style="width: 50%; margin-bottom: 20px;">
      <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">图4</p>
    </div>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">2、<span style="color: black;">而后</span>，向右复制公式到I2单元格，<span style="color: black;">因为</span>公式中VLOOKUP函数的第3个参数相同，<span style="color: black;">因此</span>复制公式后，得到的结果相同。<span style="color: black;">此时</span>需要对第3个参数进行修改。<span style="color: black;">咱们</span>将B2单元格的VLOOKUP函数中的第3个参数【2】更改为一个函数【COLUMN()】，即将公式改为：</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">=VLOOKUP($A2,Sheet1!$A$1:$I$11,COLUMN(),0)</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">按回车后等到计算结果，<span style="color: black;">而后</span>向右以复制公式方式填充，即得正确结果，如下图5示。</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/debc49474ade4084bbd2d9cd4bb2d382~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727984904&amp;x-signature=trzthTGf0D4YZLcanoeL6mYomhc%3D" style="width: 50%; margin-bottom: 20px;">
      <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">图5</p>
    </div>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">这儿</span>用到的column函数，取得单元格所在的列号，即工资单元表中的列相对应。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">3、设置单元格区域A3:I3的框为：下框线（虚线）；<span style="color: black;">而后</span>选中A1:I4单元格区域，如下图6所示：</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/cf07386ecd4546a195ff01e93e0d7203~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727984904&amp;x-signature=2znnsbbQKVF%2FMMmd0AhaMYbM9AQ%3D" style="width: 50%; margin-bottom: 20px;">
      <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">图6</p>
    </div>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">4、拖动右下角的填充柄向下填充到I38单元格，<span style="color: black;">就可</span>得到要制作的工资单，如下动图7所示。</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/f851d5b3178a4336909a3f3ed187ff34~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727984904&amp;x-signature=zqfbzIavKtHG2QQb5tQxifswzUY%3D" style="width: 50%; margin-bottom: 20px;">
      <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">图7</p>
    </div>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">2、</span><span style="color: black;">运用</span>OFFSET函数<span style="color: black;">按照</span>给定的偏移量返回新的引用区域</h1>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">OFFSET函数以指定的引用为参照系，<span style="color: black;">经过</span>给定的偏移量得到新的引用，还<span style="color: black;">能够</span>指定返回的行数或列数。返回的引用<span style="color: black;">能够</span>为一个单元格或单元格区域。OFFSET函数的语法如下：</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">OFFSET(reference,rows,cols,,))</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;"><strong style="color: blue;">◎reference(必选)</strong>，<span style="color: black;">暗示</span>偏移量参照系的引用区域。reference必须为对单元格或相连单元格区域的引1用；否则， OFFSET 函数返回错误值【#VALUE1】。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">◎rows(必选)</strong>：<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>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">◎cols(必选)</strong>：<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;"><strong style="color: blue;">◎height(可选)</strong>：<span style="color: black;">暗示</span>高度，即所要返回引用区域的行数。height 必须为正数。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">◎width(可选)：</strong><span style="color: black;">暗示</span>宽度，即所要返回引用区域的列数。width 必须为正数。</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;">运用</span>OFFSET 函数制作工资条，<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;"><span style="color: black;">【<span style="color: black;">过程</span>01】</span></strong>在“工资条”工作表中的A2单元格中输入公式1【=OFFSET(Sheet1!$A$1,ROW()/4+1,COLUMN()-1)】，按【Enter】键，得到<span style="color: black;">第1</span>位员工的编号，如下图8所示。</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/aa76495eff5b4731af637269cd4b047d~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727984904&amp;x-signature=AcHjkFDxFsb9Rnl9NhMkvrUNBvs%3D" style="width: 50%; margin-bottom: 20px;">
      <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">图8</p>
    </div>
    <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;"><span style="color: black;"><span style="color: black;">公式【=OFFSET(Sheet1!$A$1,ROW()/3+1,COLUMN()-1)】<span style="color: black;">暗示</span>以“Sheet1”工作表中的A1单元格为参照系，向下偏移1(是ROW()/3这部分取整得到的)行，向右不偏移，<span style="color: black;">因此</span>得到的结果是“Sheet1”工作表 A2单元格中的值。</span></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>2】</span></strong>向右复制公式至l2单元格，<span style="color: black;">就可</span>生成<span style="color: black;">第1</span>区域 员工的工资条。效果如下图9所示。</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p26-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/cf36a7d2a888456290133dd3150f4e76~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727984904&amp;x-signature=hBkb%2Be1s1VvHJCiOXsumU2bEnY8%3D" style="width: 50%; margin-bottom: 20px;">
      <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">图9</p>
    </div>
    <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>3】</span></strong>设置单元格区域A3:I3的框为：下框线（虚线）；<span style="color: black;">而后</span>选中A1:I4单元格区域，如下图10所示。</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/5b3239a34d06400688b691290912575b~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727984904&amp;x-signature=MY8jzml8U2d4yGX%2BVBtq%2BsvTBI8%3D" style="width: 50%; margin-bottom: 20px;">
      <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">图10</p>
    </div>
    <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>4】</span></strong>拖动右下角的填充柄向下填充到I38单元格，<span style="color: black;">就可</span>得到要制作的工资单，如下动图11所示。</p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/c6483cff05794b2a9f1f79a73052e1d3~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727984904&amp;x-signature=oPSyPUSVZZgVcDp7lbOxR2eX1fI%3D" style="width: 50%; margin-bottom: 20px;">
      <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">图11</p>
    </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;"><strong style="color: blue;"><span style="color: black;">我是鉴水鱼老师，关注我，<span style="color: black;">连续</span>分享<span style="color: black;">更加多</span>的Excel操作技巧。</span></strong></p>




m5k1umn 发表于 2024-11-1 02:55:14

楼主的文章深得我心，表示由衷的感谢！

m5k1umn 发表于 2024-11-8 22:11:02

这夸赞甜到心里，让我感觉温暖无比。

nqkk58 发表于 2024-11-11 06:33:13

我完全赞同你的观点，思考很有深度。
页: [1]
查看完整版本: 利用Excel函数快速巧妙制作工资条