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”，如图所示。

第4步：创建网址中的两个参数的列表。新建一个空查询，直接使用公式生成以下的表。也可以提前在Excel表中准备好，直接导入即可使用，如图6-18所示。

第5步：在第4步创建好的表中直接调用自定义函数。首先单击“添加列”→“调用自定义函数”按钮，然后在弹出的“调用自定义函数”对话框的“新列名”文本框中输入“Sdata”，在“功能查询”下拉列表中选择自定义的函数“Sdata”，在“x”下拉列表中选择“赛季”选项，在“y”下拉列表中选择“赛事代码”选项，最后单击“确定”按钮，如图所示。

第6步：单击“Sdata”列中的“Table”元素可以预览获取的数据。将“Sdata”列展开后，将结果上载至Excel工作表和数据模型中，如图所示。

需要注意的是，虽然Excel中的Power Query可以获取一些常规的比较简单的网页数据，但是其能力毕竟有限，对于复杂的数据的获取就无能为力了。

第二部分：数据分析

抓取的数据是一个单表，再没有其他的表，此处再不用建立其他的维度表。

本期我们使用Excel Power Pivot进行分析，打造一个自定义表头的数据透视表，并且可以使用切片器进行切片。结果如下图所示。

具体的操作步骤如下。

第1步：在Excel工作表中建立一个标题行的数据表，并添加到数据模型中，表名为“标题”，该表与已经抓取的数据表不用建立任何关系。对“一级标题名称”执行"按列排序"操作，依据为"一级标题序号"列，对“二级标题名称”执行"按列排序"操作，依据为"二级标题序号"列。如图所示。

第2步：分别编写上述9个度量值。具体如下。（向左划动可查看完整代码）