点击关注【秋叶 Excel】
发送【6】
领取秋叶 Excel 6 年精选文章合集
作者：小爽
编辑：卫星酱
PS.文末下载配套案例文件，记得看到最后领取哦~
大家好，我是在看奥运会的小爽~
今年奥运会在巴黎举行，根据奖牌榜的最新数据 （更新截止时间：北京时间 2024 年 08 月 03 日 10 时） ，中国代表团以 13 金 9 银 9 铜的成绩，共获得 31 枚奖牌，位列奖牌榜首位。
下面是咪咕视频上，每天更新的巴黎奥运会总奖牌榜。
作为一个 Exceller，我就想 :网站上的数据，能不能弄到 Excel 中呢？
那就试试？
先复制网站的表格到 Excel 中。
我们全选数据表，按【Ctrl+C】进行复制，打开 Excel，【Ctrl+V】粘贴。
咦，为啥出来的数据只有一列 ?
不过没事，将单列整理成表，还难不倒我。
一列转多行函数
Office2024/Office365 一个函数就搞定。
这个函数 WPS 也有。
wrapcols 函数可以将一维数组按列转化为二维数组； wraprows 函数可以将一维数组按行转化为二维数组。
=WRAPROWS(向量,列数,[异常返回值])
如果没有这些函数，你还可以用 PowerQuery 搞定！
PowerQuery
首先将数据导入到 PQ 编辑器中。
选中区域数据，在【数据】选项卡下，单击【来自表格/区域】。
单击【确定】，进入 PQ 编辑器中。
选择整列，鼠标右键，选择【深化】，将一列的表格转化为列表。
按 6 进行拆分。
拆分后进行转表。
在【主页】选项卡下，单击【将第一行用作标题】，数据整理完成！
但是，奖牌数据一直在更新，总不能每天都重复操作一次吧？
接下来，我来教你用 PQ 爬取数据，自动更新表格 ！
当然啦，以上所有技巧，在《秋叶 Excel 3 天集训营》你都能学到，秋叶金牌讲师、500 强企业培训师带你系统学习！
如果你想提高办公效率、升职加薪，那么我强烈建议你学点 Excel 技能，增强职场竞争力~
秋叶 Excel 3 天集训营
名额有限，还等什么
赶紧点击下方小程序报名吧
报名后，自动弹出班主任微信
扫码添加，还能领取超值福利！
PQ 爬取数据
在浏览器中，利用网页请求，找到对应的 JSON 文件 URL，利用 PQ 将 JSON 数据获取出来。
打开咪咕视频对应的奖牌网页 （网址文末获取） 。
按住快捷键【F12】 (部分电脑按【Fn+F12】) 。
PS : 本文用的是微软浏览器，其余浏览器界面可能不一样，不过步骤是一样的。
单击【网络】-刷新，将网页进行刷新。
获取刷新后的请求信息。
进行搜索 : 找到对应的 JSON 数据文件。
比如搜索中国。
点击【标头】，找到对应的请求 URL 进行复制。
这里可以看到请求方式是 GET。
URL 我们有了，后面就是利用 PQ 获取 URL 下的 JSON 文件数据。
在【数据】选项卡下，单击【自网页】，把刚刚复制的网址贴进去，单击【确定】按钮。
进入 PQ 编辑器中，如下图，可以看到数据自动转化为 JSON 文件数据信息了。
其中 :
Web.Contents : 将 URL 网址识别成二进制文件 (默认是 GET 请求) 。
Json.document : 将二进制文件转化为 JSON 文件数据。
如下图，观察数据，可以看到，我们要的奖牌数据 data 中。
接下来提取数据。
筛选出需要的字段信息。这里我需要的是国家名称，排名，以及金银铜的奖牌数，以及图片网址。
新建一个步骤，利用 Table.FromRecords 进行转表。
由于国家图片在 list 中，所以需要 Table.TransformColumns 对这一列进行提取。
如下图 :
选择奖牌区域，将数据类型设置为整数。
由于排名中没有参与排名的均为 0，所以将 rank 列中的 0 筛选掉。
最后将数据导出就可以啦！
利用 image 函数，将网址中的国家图片显示出来。
数据弄好之后，就能基于表格信息进行数据分析了~
比如利用数据透视表分析出金牌数前十名的国家。
之后，我们只要更新源数据，以上表格就可以自动更新啦。
在蹲点巴黎奥运会时，作为 Exceller 的我特别留意奖牌榜单。
对于「网页表格数据直接粘贴到 Excel 中，就变成单列数据这个问题」，我第一反应是：对单列数据清洗。
有两种方法可以实现：
❶ wraprows 函数能够将一列数据按照指定数按行拆分为表格数据 （Office2024/Office365/WPS） 。
❷ List.Split 拆分，Table.FromRows 转表，最后将第一行用作标题 （PowerQuery） 。
但是，由于直接复制出来的数据，不能随着网页动态更新，所以又尝试了 PQ 爬虫
❶ 通过获取网页请求得到 JSON 数据文件对应的 URL 网址；
❷ 通过 PQ 中获取网页 (Web.Contents 和 JSON.document) ，得到网页中的数据；
❸ 通过简单清洗得到数据文件；
❹ image 函数获取网址中的图片，最后进行数据分析。
本文到这里就结束，大家看奥运会了嘛？可以留言区聊聊~
如果你也想动手练习一下，那就：
在公众号【秋叶 Excel】后台
留言回复关键词【奥运】
即可免费下载案例文件！
其实以上这些，不过是 Excel 技巧中的冰山一角！
还想系统学习更多高效办公技巧，早点下班？
我推荐你来《秋叶 Excel 3 天集训营》，跟着专业讲师学习，稳步提升 Excel 技能！
课后还有练习文件，遇到问题也有助教随时答疑，不用担心看不懂、学不会~
秋叶 Excel 3 天集训营
今天只需 1 元！
还等什么
赶紧点击下方小程序加入吧
报名后，自动弹出班主任微信
扫码添加，还能领取超值福利！
点击下方卡片关注【秋叶 Excel】
发送【6】
领取秋叶 Excel 6 年精选文章合集