总表变分表亦变，透视表+Filter才是最牛的Excel表格拆分工具！
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><strong style="color: blue;">表格拆分</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>透视表+filter拆分表格则<span style="color: black;">能够</span>批量拆分<span style="color: black;">况且</span><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;">每一个</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;"><img src="//q0.itc.cn/q_70/images03/20240806/bdf85b3fb8914fa78eab2326f90c3c0f.jpeg" 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;"><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></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">=FILTER(总!A2:D150,总!B2:B150=E2)</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="//q1.itc.cn/q_70/images03/20240806/425878c6c7b54343b317e17e0fac6119.jpeg" 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 style="color: black;">选择</span>总表B列制作透视表，<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;"><img src="https://q2.itc.cn/q_70/images03/20240806/ab4b95a68ba44eb6bd23a0a9baca2356.gif" 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、制作分表</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>所有分表，粘到D列<span style="color: black;">起始</span>的区域，E2输入公式=B1，数据引入完成。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://q6.itc.cn/q_70/images03/20240806/da2cc805d497408badccc81f223bb52d.gif" 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 style="color: black;">隐匿</span>所有表AB列，取消全选，完工！</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>用切片器+filter，效果更炸裂。<span style="color: black;">倘若</span>想学点页面下大拇指，超过200明天就分享。</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;">150集</span></span><span style="color: black;">函数+189集图表+50集透视表+119个操作技巧</span><span style="color: black;">），从入门到进阶和高级应用，<span style="color: black;">仔细</span><span style="color: black;">能够</span>点击下面链接</span><a style="color: black;"><span style="color: black;">返回<span style="color: black;">外链论坛： http://www.fok120.com</span>，查看<span style="color: black;">更加多</span></span></a></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">责任编辑：网友投稿</span></p>
论坛的成功是建立在我们诚恳、务实、高效、创新和团结合作基础上，我们要把这种精神传递下去。
页:
[1]