怎么样快速学习数据透视表，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>是Excel/WPS中最实用的功能，常用来完成数据的<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>就从<strong style="color: blue;">透视表的<span style="color: black;">创立</span>、实用功能、动态更新数据源、<span style="color: black;">平常</span>错误总结和冷门实用技能</strong>等五个维度，带领新手小伙伴们从零到<span style="color: black;">1、</span>从入门至精通，快速学习数据透视表。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_png/7ib9uhYMjcjg4liapic11g7ibkDCjL9gjN4OO9v1JicLehLpDEAM21N8emAhsvMZVJnsTe5wW86Eosz4icib7ETvLViamA/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">1、</span><span style="color: black;">创立</span>数据透视表</span></strong></span></h1>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;"><strong style="color: blue;">1、<span style="color: black;">插进</span>数据透视表</strong></span></h1>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">数据透视表<span style="color: black;">位置于</span>【<strong style="color: blue;"><span style="color: black;">插进</span></strong>】功能区，<span style="color: black;">便是</span>下面这个按钮，它旁边的“<span style="color: black;">举荐</span>的数据透视表”是透视表的弟弟，<span style="color: black;">能够</span>先忽略。</h1>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_png/7ib9uhYMjcjg4liapic11g7ibkDCjL9gjN4O4iakE2fEcph87c7PGYgoQ7eTPEqibE85RQcgQI42qxJf46pmokkqcoPw/640?wx_fmt=png&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 style="color: black;">创立</span>透视表之前，先准备一份<strong style="color: blue;">数据源</strong>，你需要<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;"><strong style="color: blue;">1、表头<span style="color: black;">不可</span>存在合并单元格；2、表头<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>说的<strong style="color: blue;">表头<span style="color: black;">通常</span><span style="color: black;">指的是</span>数据源的<span style="color: black;">第1</span>行</strong>。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_png/7ib9uhYMjcjg4liapic11g7ibkDCjL9gjN4OSDZj5V6TFG8RE4oQjHC9AyTdNVUSyf1g1tHIgkqR5rquIQSV92yOCA/640?wx_fmt=png&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 style="color: black;">第1</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;">鼠标选中数据源<strong style="color: blue;">任意一单元格</strong>、<span style="color: black;">或</span><strong style="color: blue;">Ctrl+A</strong>全选数据源，之后依次点击【<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://mmbiz.qpic.cn/mmbiz_jpg/7ib9uhYMjcjg4liapic11g7ibkDCjL9gjN4OHUQAlo2iaibib7QeyOUkarkssnK497lOcic6Dd4MmHzSklu5BrnwmG6XzA/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 style="color: black;">第1</span>个框是透视表的区域，经过上面的操作，<span style="color: black;">这儿</span>会<strong style="color: blue;">自动填入数据源区域</strong>，<span style="color: black;">因此</span><strong style="color: blue;"><span style="color: black;">不消</span>管</strong>，第二个选项<span style="color: black;">通常</span>默认新建一个工作表用来展示透视表，<span style="color: black;">因此</span><strong style="color: blue;"><span style="color: black;">亦</span><span style="color: black;">不消</span>管</strong>，直接点击【<strong style="color: blue;">确定</strong>】，进入透视表界面。</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;">2、字段区域说明</strong></span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">点击确定之后，会在数据源前面生成一个新的Sheet，用于展示透视表。设置界面，一共由两部分<span style="color: black;">构成</span>，左边的<strong style="color: blue;">报表生成区</strong>和右边的<strong style="color: blue;">字段展示区</strong>。将字段展示区中的字段拖动到<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://mmbiz.qpic.cn/mmbiz_png/7ib9uhYMjcjg4liapic11g7ibkDCjL9gjN4OhoEJKltblarUibJUTedCaZPDOibgrPOoV3XSS3bMrWoS3lCtJ6QTzyMA/640?wx_fmt=png&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;"><strong style="color: blue;">筛选区域</strong>：字段拖入到筛选区域中后，对应字段会<span style="color: black;">显现</span>在透视表的<strong style="color: blue;">最上方</strong>，<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;"><strong style="color: blue;">列区域</strong>：将字段拖动到此区域中，会以<strong style="color: blue;">非重复值列</strong>的形式展示，出<span style="color: black;">此刻</span>透视表的上方，<span style="color: black;">做为</span>新的列标签；<span style="color: black;">通常</span>拖入0-2个字段，太多的列数据会看<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;"><strong style="color: blue;">行区域</strong>：将字段拖动到此区域中，会以<strong style="color: blue;">非重复值行</strong>的形式展示，出<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;"><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></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="https://mmbiz.qpic.cn/mmbiz_png/7ib9uhYMjcjg4liapic11g7ibkDCjL9gjN4ONXeNf7ctN73Mr6f2nSD8NlDWSF0VeCicCoKhdSfRhzCZ9af61ELc83w/640?wx_fmt=png&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;"><strong style="color: blue;">3、经典布局</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>的观看形式，将多个字段拖动到行区域中，它们会以压缩的形式分布在一列中（下图中A列分布了省份、城市、邮寄方式3个字段），<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="https://mmbiz.qpic.cn/mmbiz_png/7ib9uhYMjcjg4liapic11g7ibkDCjL9gjN4OLowDC9aAe0n0ptrYdTCvh2T6MtgxBnodgcEDYanl5PZURgfwIONibGg/640?wx_fmt=png&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;">点击最上方的【<strong style="color: blue;">设计</strong>】，报表布局依次<span style="color: black;">选取</span>【<strong style="color: blue;">以表格形式展示</strong>】、【<strong style="color: blue;">重复所有项<span style="color: black;">目的</span>签</strong>】，最后取消<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="https://mmbiz.qpic.cn/mmbiz_gif/7ib9uhYMjcjg4liapic11g7ibkDCjL9gjN4OLLEm8ovZqb5jMZcwOykMiaudILdnHiaToKEW2Yh7LdAezTh7Bfff2zpQ/640?wx_fmt=gif&tp=webp&wxfrom=5&wx_lazy=1" style="width: 50%; margin-bottom: 20px;"></p>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;"><strong style="color: blue;"><span style="color: black;">2、</span>实用功能演示</strong></span></h1>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;"><strong style="color: blue;">1、值汇总依据</strong></span></h1>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">上面说到文本字段拖动到值区域默认计数、数值字段拖动到值区域默认求和，除此之外，透视表还<span style="color: black;">供给</span>多种汇总依据。鼠标右键，<span style="color: black;">选取</span>【值汇总依据】，<span style="color: black;">能够</span>看到多种方式。</h1>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_png/7ib9uhYMjcjg4liapic11g7ibkDCjL9gjN4OwP2zicibRMQicsqvbzhic9Mzt4rpBsfAleFoAykM06CNS90N7NT03jicMDA/640?wx_fmt=png&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 style="color: black;">例如</span>在<span style="color: black;">这儿</span>查看<span style="color: black;">每一个</span>省份最大一单的<span style="color: black;">营销</span>数量，在汇总依据中<span style="color: black;">选取</span>【<strong style="color: blue;">最大值</strong>】<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://mmbiz.qpic.cn/mmbiz_gif/7ib9uhYMjcjg4liapic11g7ibkDCjL9gjN4OJCF93F89Yl5YBR5rKYHkSY0UQnZLr2HGd2fBQv6B1bI7bWPMsQPaGA/640?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;">2、值<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>方式，常用的有<strong style="color: blue;">列汇总的百分比</strong>、<strong style="color: blue;">行汇总的百分比、差异百分比</strong>（同环比）。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_png/7ib9uhYMjcjg4liapic11g7ibkDCjL9gjN4OSSFK6qib5ic7yC7gIXkAwxODRGiaxkmLArVG7x0NLbH9JEBOdsv431Gzw/640?wx_fmt=png&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 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;"><img src="https://mmbiz.qpic.cn/mmbiz_gif/7ib9uhYMjcjg4liapic11g7ibkDCjL9gjN4OyhnIvZaBHicR94fD6wIvRn4FAWHLHRriaQQrCkGJx5jT8CtrpK9jXf1g/640?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;">还<span style="color: black;">能够</span>用差异百分比来计算同环比，下图中计算近三年同比数据（无2019年数据，<span style="color: black;">因此</span>2020年同比为空）</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_gif/7ib9uhYMjcjg4liapic11g7ibkDCjL9gjN4OMSJzuUuXZuYLV1GiaBq2M8jOxwaia3SvQicicja6lWboibGls1ictv2jz8zg/640?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;">3、新建字段</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></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>
<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="https://mmbiz.qpic.cn/mmbiz_gif/7ib9uhYMjcjg4liapic11g7ibkDCjL9gjN4OUicxaiaNKQ3VHq1f28EAqbImnhm2NCppCdmKnxHDKmzOqqzehAWI7NaQ/640?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;">4、创建组合</strong></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;">行区域</strong>内容，透视表<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;">下图中A列为明细日期，<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>【组合】，步长<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://mmbiz.qpic.cn/mmbiz_gif/7ib9uhYMjcjg4liapic11g7ibkDCjL9gjN4OoCk5lHDhjvooibxyMgGhiaIj18GCB59YEzv7O5MbmjvIJGaqcLhoSknA/640?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;">组合功能<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 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://mmbiz.qpic.cn/mmbiz_gif/7ib9uhYMjcjg4liapic11g7ibkDCjL9gjN4OMeicx1licLRPL5X7UddlHBzu2MFAuAcjExWBFu89vNXrwpJicxYbzkliaQ/640?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;">5、透视表<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></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>的手段，鼠标选中数据透视表<strong style="color: blue;">表头旁边的空白单元格</strong>，<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="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;"><strong style="color: blue;">6、切片器</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></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></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></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 style="color: black;">能够</span>联动多个<strong style="color: blue;">同源数据透视表</strong>（<span style="color: black;">重视</span>，是同一个数据源<span style="color: black;">创立</span>的透视表，<span style="color: black;">区别</span>源需要在Power Pivot中<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><span style="color: black;">创立</span>报表连接，用一个切片器<span style="color: black;">掌控</span>了2个透视表。</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>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">3、</span>动态更改数据源</span></strong></span></h1>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><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>“A1:J8236”区域<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>，<strong style="color: blue;">刷新透视表，内容是不会变化的</strong>。</h1>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">那样</span><span style="color: black;">怎样</span>动态更新数据源，数据源变化时，透视表自动变更数据源？</h1>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;"><strong style="color: blue;"><span style="color: black;">第1</span>种<span style="color: black;">办法</span>是将数据源转换为超级表</strong></span>，选中数据源，<span style="color: black;">同期</span>按下<strong style="color: blue;">Ctrl+T</strong>，点击【确定】，<span style="color: black;">这般</span>，数据增减，透视表的数据源会跟着变动。</h1>
<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;"><strong style="color: blue;">第二种方式是定义名<span style="color: black;">叫作</span>结合OFFSET函数。</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 style="color: black;">咱们</span>输入"data"，引用位置输入公式：</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;">=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$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 style="color: black;">插进</span>数据透视表，表/区域输入刚才的名<span style="color: black;">叫作</span>“<strong style="color: blue;">data”</strong>，<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="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>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">4、</span><span style="color: black;">平常</span>错误总结</span></strong></span></h1>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;"><strong style="color: blue;">1、透视表<span style="color: black;">没法</span>创建</strong></span></h1>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">透视表创建不成功<span style="color: black;">重点</span><span style="color: black;">原由</span>在于<strong style="color: blue;">表头字段存在空白</strong><span style="color: black;">导致</span>，数据源的<span style="color: black;">第1</span>行不要存在空白单元格。</h1>
<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 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;"><strong style="color: blue;"><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;"></strong></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;">2、数据源修改内容后，透视表刷新无变化</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 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>点击【数据透视表分析】—【<strong style="color: blue;">更改数据源</strong>】，重新框选下数据源就好了。</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;"><strong style="color: blue;">3、透视结果<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;">透视表中<strong style="color: blue;">某字段<span style="color: black;">显现</span>空白，<span style="color: black;">然则</span>值区域却有数据</strong>，这种<span style="color: black;">状况</span>是<span style="color: black;">由于</span>这个字段数据源中<strong style="color: blue;"><span style="color: black;">包括</span>合并单元格</strong>，取消合并单元格，填充<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="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;"><strong style="color: blue;">4、日期/数字<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>【<strong style="color: blue;">选定区域<span style="color: black;">不可</span>分组</strong>】，这种<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></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>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;"><strong style="color: blue;"><span style="color: black;"><span style="color: black;">5、</span>透视表冷门技能</span></strong></span></h1>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;"><strong style="color: blue;">1、批量合并单元格</strong></span></h1>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">右键<span style="color: black;">选取</span>【数据透视表选项】，勾选【<strong style="color: blue;">合并且居中<span style="color: black;">摆列</span>带标签的单元格</strong>】，接着用<strong style="color: blue;">格式刷</strong>去刷原始数据源<span style="color: black;">能够</span>完成单元格的批量合并。</h1>
<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;"><strong style="color: blue;">2、按字段拆分工作表</strong></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;">待拆分字段拖动到透视表的筛选区域</strong><span style="color: black;">中，接着下拉透视表选项，点击【</span><strong style="color: blue;"><span style="color: black;">表示</span>报表筛选页</strong><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;"><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;"><strong style="color: blue;">3、批量合并工作表</strong></span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">合并多个工作表的内容，点击Alt+D+P三键（依次点击），进入透视表向导界面，勾选【<strong style="color: blue;">多重合并计算数据区域</strong>】，点击下一步，添加所有待合并表格数据之后，点击【确定】，生成一张透视表，将筛选项“页1”拖动到行区域，<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="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 style="color: black;">懊恼</span>，以上<span style="color: black;">便是</span>关于数据透视表的技能介绍，<span style="color: black;">期盼</span>对你有所<span style="color: black;">帮忙</span>~</span></p>
祝福你、祝你幸福、早日实现等。 我深感你的理解与共鸣，愿对话长流。 同意、说得对、没错、我也是这么想的等。
页:
[1]