Excel Power Query抓取多个网页数据并配合Power Pivot进行分析
Excel Power Query抓取多个网页数据并配合Power Pivot进行分析
本节内容使用Excel的Power Query和Power Pivot组件，抓取多个网页数据，进行清洗、建模和分析。
第一部分：从网页动态抓取数据
使用Power Query不仅可以获取本地的Excel文件数据，还可以获取网页数据。
本节介绍如何使用Power Query获取新浪网新浪体育频道的新浪直播室网页中的足球排行榜数据，主要获取列表中的全部赛季的球队数据，赛事主要获取前5项数据（前5项赛事的数据结构是相同的），如图所示。
网址：
http://match.sports.sina.com.cn/football/opta_rank.php?year=2013&lid=1
默认打开网页时网址中有一部分为“year=2013&lid=1”；当“选择日期”为“2015赛季”、“选择赛事”为“德甲”时，网址中的这部分变为“year=2015&lid=3”。
对比网址中的这两部分不难发现，其中“year=2013”和“year=2015”部分表示赛事日期，而“lid=1”和“lid=3”部分分别表示赛事的名称，对应赛事列表中的赛事名称，而其他部分完全不变。
通过以上的发现，就可以将这两个参数构造为变量，创建自定义函数来依次获取网页中的数据。具体的操作步骤如下所述。
第1步：复制目标网页中的网址。首先新建一个Excel工作簿，将其打开后依次选择“数据”→“获取数据”→“来自其他源”→“自网站”选项，然后在弹出的“从Web”对话框中选中“高级”单选按钮，接着将网址按参数进行拆分，并分别填写至“URL部分”区域的各个对应的文本框中，最后单击“确定”按钮，如图6-15所示。
第2步：在弹出的“导航器”对话框的左侧选择“Table 0”选项，就可以在右侧看到当前网址对应的表格数据，然后单击“转换数据”按钮，如图6-16所示。
第3步：在Power Query中创建自定义函数。选择“Table 0”查询，打开“高级编辑器”窗口，将公式修改为自定义函数，自定义函数的名称为“Sdata”，如图所示。
(x as number, y as number) => let 源 = Web.Page( Web.Contents( "http://match.sports.sina.com.cn/football/opta_rank.php?year=" & Text.From(x) & "&lid=" & Text.From(y) ) ), Data0 = 源{0}[Data] in Data0
第4步：创建网址中的两个参数的列表。新建一个空查询，直接使用公式生成以下的表。也可以提前在Excel表中准备好，直接导入即可使用，如图6-18所示。
= Table.ExpandTableColumn( Table.AddColumn( Table.FromList({2011 .. 2021}, each {_}, {"赛季"}), "赛事代码和赛事名称", each #table({"赛事代码", "赛事名称"}, { {1, "英超"}, {2, "西甲"}, {3, "德甲"}, {4, "意甲"}, {5, "法甲"} }) ), "赛事代码和赛事名称", {"赛事代码", "赛事名称"} )
第5步：在第4步创建好的表中直接调用自定义函数。首先单击“添加列”→“调用自定义函数”按钮，然后在弹出的“调用自定义函数”对话框的“新列名”文本框中输入“Sdata”，在“功能查询”下拉列表中选择自定义的函数“Sdata”，在“x”下拉列表中选择“赛季”选项，在“y”下拉列表中选择“赛事代码”选项，最后单击“确定”按钮，如图所示。
第6步：单击“Sdata”列中的“Table”元素可以预览获取的数据。将“Sdata”列展开后，将结果上载至Excel工作表和数据模型中，如图所示。
需要注意的是，虽然Excel中的Power Query可以获取一些常规的比较简单的网页数据，但是其能力毕竟有限，对于复杂的数据的获取就无能为力了。
第二部分：数据分析
抓取的数据是一个单表，再没有其他的表，此处再不用建立其他的维度表。
本期我们使用Excel Power Pivot进行分析，打造一个自定义表头的数据透视表，并且可以使用切片器进行切片。结果如下图所示。
具体的操作步骤如下。
第1步：在Excel工作表中建立一个标题行的数据表，并添加到数据模型中，表名为“标题”，该表与已经抓取的数据表不用建立任何关系。对“一级标题名称”执行"按列排序"操作，依据为"一级标题序号"列，对“二级标题名称”执行"按列排序"操作，依据为"二级标题序号"列。如图所示。
第2步：分别编写上述9个度量值。具体如下。（向左划动可查看完整代码）
总胜场 := SUM ( '足球数据'[胜] )---------------------------------------总负场 := SUM ( '足球数据'[负] )---------------------------------------总进球 := SUM ( '足球数据'[进球] )---------------------------------------前3名的总净胜球 :=CALCULATE ( SUM ( '足球数据'[净胜球] ), '足球数据'[排名] <= 3 )---------------------------------------总积分 :=SUM ( '足球数据'[积分] )---------------------------------------场均胜率 :=AVERAGEX ( '足球数据', DIVIDE ( '足球数据'[胜], '足球数据'[场数] ) )---------------------------------------场均进球 :=AVERAGEX ( '足球数据', DIVIDE ( '足球数据'[进球], '足球数据'[场数] ) )---------------------------------------场均积分 :=AVERAGEX ( '足球数据', DIVIDE ( '足球数据'[积分], '足球数据'[场数] ) )---------------------------------------排名TOP3球队 :=IF ( HASONEVALUE ( '足球数据'[赛季] ), CALCULATE ( CONCATENATEX ( '足球数据', '足球数据'[球队], "，", '足球数据'[排名], ASC ), '足球数据'[排名] <= 3 ))第3步：此时我们还不能直接将上述的代码放入数据透视表。我们还需要一个度量值，以判断标题行的上下文，然后赋予不同的度量值。代码如下。
透视表值 :=VAR title1 = MAX ( '标题'[一级标题名称] )VAR title2 = MAX ( '标题'[二级标题名称] )VAR pivotvalue = SWITCH ( TRUE (), title1 = "总量分析", SWITCH ( TRUE (), title2 = "总胜场", [总胜场], title2 = "总负场", [总负场], title2 = "总进球", [总进球], title2 = "前3名的总净胜球", [前3名的总净胜球], [总积分] ), title1 = "场均分析", SWITCH ( TRUE (), title2 = "场均胜率", FORMAT ( [场均胜率], "0.00%" ), title2 = "场均进球", ROUND ( [场均进球], 2 ), ROUND ( [场均积分], 2 ) ), [排名TOP3球队] )RETURN pivotvalue第4步:分别将"一级标题名称"和"二级标题名称"放入列标签，将"赛季"放入行标签，将"透视表值"放入值标签，插入切片器。如图所示，完成透视表的自定义表头的设置和分析。
这个案例将数据获取、清洗、建模和分析的过程完整地展现了出来，体现了Power Query和Power Pivot在Excel中应用，大幅提升了Excel的智能化。
作者新书推荐
扫码了解本书详情
发布：刘恩惠
审核：陈歆懿
如果喜欢本文欢迎 在看丨留言丨分享至朋友圈 三连
热文推荐
用过那么多评估开发工作量的指标，还是它最好用！
书单 | 9月新书速递
流程管理软件的四种类型
P5~P9应该具备的核心能力是什么▼点击阅读原文，了解本书详情~
本文分享自 博文视点Broadview 微信公众号，前往查看
如有侵权，请联系 cloudcommunity@tencent.com 删除。
本文参与 腾讯云自媒体同步曝光计划 ，欢迎热爱写作的你一起参与！
腾讯云开发者
