论坛 › 宁夏 › 跟我学EXCEL-57（PQ案例03个税申报表）

wloe2gf 发表于 2024-10-2 14:13:02

跟我学EXCEL-57（PQ案例03个税申报表）

    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/9384fd5d0a5a48bd9be5c68e04b86f96~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727976068&amp;x-signature=x5EftPxy1CSmV0oz9EYf8R%2FgE1c%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;"><span style="color: black;">亲爱的小伙伴们，跟我学EXCEL系列福利来了，从初级<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></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;">文案</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>是Power Query）。</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;">期盼</span><span style="color: black;">大众</span><span style="color: black;">爱好</span>，欢迎提出宝贵意见和<span style="color: black;">意见</span>！</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;"><span style="color: black;">大众</span>好，<span style="color: black;">咱们</span>继续学习Power Query。</span></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;">4、</span>EXCEL高级篇-Power Query07</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">07、PQ案例03个税申报表</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">（1）、案例03<span style="color: black;">基本</span>表及<span style="color: black;">需要</span></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;">基本</span>表一为某月的“工资表”，格式如下。截图一。</span></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/d22d582ca55a499998f8a1227c2fdb9d~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727976068&amp;x-signature=%2FMp20wwT9h26XlXr%2BpK4HtyHVcA%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;"><span style="color: black;"><span style="color: black;">基本</span>表二为“扣社保明细表”，格式如下。截图二。</span></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/130e90e41d5341248624b785e6a2b8c2~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727976068&amp;x-signature=SwAYswa5LcZMhUR6koWL%2B%2FeO9tA%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;"><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-qvj2lq49k0/326507c41cfb404aaa3498da17746ca6~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727976068&amp;x-signature=3pijg6ovo0k3fgcexP1eEIhbn10%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;"><span style="color: black;">下月的工资表和扣社保明细表和前面的格式<span style="color: black;">同样</span>，就不截图了。</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;">需要</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></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">（2）、PQ操作过程</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;">第1</span>步、将“工资表”表导入PQ</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;">安置</span>在“工资表”表内，点击“数据”-&gt; “来自表格/区域”，<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>要勾选。<span style="color: black;">倘若</span>这张表是超级表就直接进入<span style="color: black;">查找</span>编辑器了。</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">将“表1”<span style="color: black;">查找</span>修改为“工资表”。</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;">亦</span>删除掉。“关闭并上载至”-&gt;“关闭并上载至”-&gt;“仅创建连接”，动图一。</span></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p26-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/d601d8038440460da488e10c9b7ab070~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727976068&amp;x-signature=GFh0LWTSj2vug792a8FOL4E821Y%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;"><span style="color: black;">第二步、将“社保明细1”导入PQ</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;">同样</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></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/b650985f40fe40c9b419fd542b5ddf63~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727976068&amp;x-signature=ErmhnaAcKJuqS6kptuuv%2FFIxpTQ%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;"><span style="color: black;">第三步、将“身份证”导入PQ</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;">同样</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></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/a80eda426121438d891e3367986ee5b8~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727976068&amp;x-signature=mw8SlO4wR7h1u1NN7JHT%2BJPHyzg%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;"><span style="color: black;">第四步、合并<span style="color: black;">查找</span></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;">查找</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><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>后<span style="color: black;">咱们</span>会进入<span style="color: black;">查找</span>编辑器的操作界面了，<span style="color: black;">咱们</span>点击“社保”标题后面的符号，将table展开，将“<span style="color: black;">运用</span>原始列名<span style="color: black;">做为</span>前缀”勾选去除，将“姓名”勾选去掉，<span style="color: black;">保存</span>“养老”“医疗”“失业”前面的勾选，确定。<span style="color: black;">而后</span>将“扣社保”列删除。将“合并1”修改为“个税申报”，动图四。</span></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/ca6c57c3cb544bc6b8579c2bedcb155d~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727976068&amp;x-signature=R3XM0tKcQvB%2BAzSDOFHN051s2G4%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;"><span style="color: black;">第五步、点击“主页”选项卡下的“合并<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>”，确定后展开身份证table，只<span style="color: black;">保存</span>“身份证”前面的勾选，确定，动图五。</span></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/1f8db97d1d964397a2eff5050e913b81~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727976068&amp;x-signature=rAvmPfPB8voy0Iy6oG743X4oWIw%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;"><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 style="color: black;">要求</span>列”，弹出的对话框，“新列名”录入“证照类型”，列名<span style="color: black;">选取</span>“身份证.1”列，运算符<span style="color: black;">选取</span>“不等于”，值输入“null”，输出录入“居民身份证”，确定，动图六。</span></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/65fdad2ee8384effa8234386c48c0848~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727976068&amp;x-signature=kKvdLt%2FKvDEvAD4qA3htYaGxip8%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;"><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 style="color: black;">要求</span>列”，弹出的对话框，“新列名”录入“本期免税收入”，列名<span style="color: black;">选取</span>“身份证.1”列，运算符<span style="color: black;">选取</span>“不等于”，值输入“null”，<span style="color: black;">通常</span><span style="color: black;">咱们</span>都<span style="color: black;">无</span>免税收入，输出空白<span style="color: black;">就可</span>，确定，动图七。</span></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/cc9c822e38424e00bd8cb9d1bcfbaf73~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727976068&amp;x-signature=itgEdqeiZJsODIoG8DMQS9AXKhY%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;"><span style="color: black;">第八步、<span style="color: black;">调节</span>列的<span style="color: black;">次序</span></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;">次序</span>，关闭<span style="color: black;">保存</span>后，系统自动就将生成的个税申报表加载到了一个新的工作表了，<span style="color: black;">倘若</span>觉得列名和模板表不一致，<span style="color: black;">亦</span><span style="color: black;">能够</span>再次进入PQ对列名进行修改，关闭<span style="color: black;">保存</span>上载的表就更新了。上载好的表只需要将数据部分复制，<span style="color: black;">选取</span>性粘贴数值到个税导入模板表就<span style="color: black;">能够</span>导入个税系统进行申报了。这个<span style="color: black;">过程</span>比较简单就不演示了。动图八。</span></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/8bd76040dfcb4088afca5a1b11c59f8a~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727976068&amp;x-signature=3nEyxnfKLlnmfM91Gc8%2BRutxQvc%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;"><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 style="color: black;">咱们</span>只需要将新的工资表和扣社保明细更新了，一刷新，新的个税申报表就随之更新了，动图九。</span></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/569c65d4c042464a9bbce63db5873cc6~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1727976068&amp;x-signature=GZa3L%2FEybz7l%2F24kWQfFV6xDmc8%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;"><span style="color: black;">附言：演示数据已发至公共邮箱，再次提醒用OFFICE2016及以上版本才<span style="color: black;">能够</span>看演示数据和操作PQ。公共邮箱：</span>excel147@163.com<span style="color: black;">，公共邮箱<span style="color: black;">秘码</span>：Excel258。</span></p>

1fy07h 发表于 2024-10-5 16:52:21

论坛是一个舞台，让我们在这里尽情的释放自己。

4lqedz 发表于 2024-10-15 11:02:52

你的话语如春风拂面，让我感到无比温暖。

b1gc8v 发表于 2024-10-17 02:20:53

你的努力一定会被看见，相信自己，加油。

查看完整版本: 跟我学EXCEL-57（PQ案例03个税申报表）