excel数据分析与处理-数据透视表连接多个数据源
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/51ed51ba4e5a45ad86a44f28b9311ee4~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727978309&x-signature=K2MR%2Fu8yX64Z0XlRf3sSn6e%2FTxw%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><a style="color: black;">视频讲解通道</a></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>数据透视表时，又只能<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>连接到多个数据源。</p>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">数据模型</h1>
<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>excel里的一个关系型数据库，这个数据库里存储着你放进去的<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>有一张表记录着的学生的个人信息，里面<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/c0b7fda699a64e14bb0eb340e6a80836~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727978309&x-signature=RUOa7jfPjyISHPwmrA%2FAZ6SLHUo%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">另一张表是学生成绩表，上面记录着学号，成绩；</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/1176897bcab24dec879860c08a5fddfb~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727978309&x-signature=Qae7Tb%2FiJ%2BfR9cmF59GdStMJiFc%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>，班<span style="color: black;">专家</span>名<span style="color: black;">叫作</span>，班<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/8125ba3cc96d400291ff350d9ba9e2be~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727978309&x-signature=pvv20pokM0kErvZWeMPlcpfTKqk%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>表之间<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>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/e0e9bd9e3b924620b4670c9eb931869a~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727978309&x-signature=WlyzSMfWqrLdvEPWE7KK7CeLSBU%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>，这些关系必须是一一对应<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>学生成绩和学生信息表之间<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>1对多的关系，一个班级<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>要<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>外来项去匹配班级信息表的班号。</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>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">怎样</span>创建数据模型</h1>
<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>。</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>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/c8cf0e5a80264ebdb59db85a29bb699a~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727978309&x-signature=XrZjfToWYIFz6yqfuB2QQfuLnpw%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>依次设置数据源的关系。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p26-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/95db81bc87494644b2f84f90d9c82f8c~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727978309&x-signature=XIfww9Pc0g%2FTCX9agbsHYTXrY%2Fk%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;">第1</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>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/df1b27f501744edaa755ea2c3b4e0616~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727978309&x-signature=mAG%2BLMWeVElGK9Yvc%2B8ocegSXnw%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>表<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>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/9bea57211cca4071a3b6189b1d609741~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727978309&x-signature=iYo7dt2PzDB5h%2BSere8VJ7QAeRc%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>表<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>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/68eaa58ec75b4e92b0396ce847ece684~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727978309&x-signature=502P7M8kBs6sxv0%2BTzfQXc5uVyE%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>学生对应一个各科分数，<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>学号。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/1bbeab74a3b94e59a7974f586015297a~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727978309&x-signature=hY76tOQfssvxpSygTnO7qED90uA%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>该<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/c423be6d7628416a898ad23e6ab267f1~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727978309&x-signature=rFbKfHwoRHlhStSw4ZGYXqjQhnc%3D" style="width: 50%; margin-bottom: 20px;"></div>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/a86faa78354144c790edfa3c53b8a02c~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727978309&x-signature=asXFBZwLwh1AIjGHCyrj9x66zZY%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><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>数据工具中的管理数据模型创建关系。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/31c5cc671cd444748770f9e147b156ef~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727978309&x-signature=4pOeqx2ARsXlfCKF1WI24h%2FWBRg%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>的获取<span style="color: black;">外边</span>数据选项中就<span style="color: black;">能够</span>快速<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/f4e9aaf17f2443b59a84f48a3d73471e~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727978309&x-signature=Rtk86BTWEgyfxVnc6aPvFPSd94A%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><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>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/cc8e5bd7eb334c158138e431b9cf269b~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727978309&x-signature=rnOMPfT0GVQoD4o3%2FKuNIE8Ivdw%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><span style="color: black;">咱们</span>就<span style="color: black;">能够</span>一目了然各个数据之间的关系是<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/8a756813343f42da8ab70c7511201bcd~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727978309&x-signature=3E0hSC1MB%2FWyhMyT4JpCW%2BZjv0A%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>就<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>就创建好了一个各学院各专业各科成绩的分数统计报表。</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>
一看到楼主的气势，我就觉得楼主同在社区里灌水。
页:
[1]