了解大数据分析？试试看从VBA爬虫＋SQL查询开始

回头想想，自己跟数据打交道也不少时日了。所谓大数据，其实是服务于战略决策、解决实际问题的数据集合的推演过程。数据采集、清洗（规整化、处理无效数据、GBK转码）、存储、分析、输出&可视化等都是“大数据”概念的组件。那天开了个会，主题好像是“智能财务”，感觉深受启发，尤其是“数据湖”“数据集市”“智能分录”“模型工厂”等一众崭新理念令我等耳目一新，获益良多（只要你敢想，估值百倍不是梦~）。

虽然（大量）数据积累是个吃力不讨好的活儿。枯燥、易错、缓慢且不被重视。但如果积累了一定量的高质量数据，却能成为有力的战略武器，为许多决策提供极高的参考价值。

其实很多大型网站积累的数据能为我们的分析、建模、决策提供了很好的素材。而“爬虫”程序，是我们批量采集，搭建基础库的一种有效工具，具有较高的技术价值。（“爬虫工程师”你们了解一下~）。

上回，我们通过“批量爬取小黄图”一文，接触到了爬虫程序常用的VBA函数。批量图片爬取，主要是识别图片地址常用的“src”起头&.jpg,.png后缀等固定字符串结尾，截取指定的图片地址信息，再用VBA-downloadtofile函数完成批量下载（最新研究发现，VBA可以直接调用迅雷，完成高速批量下载）

这次，我们以【携程】为例，了解爬虫工具中各种VBA字符串函数的使用方法&循环爬取策略。目的是将携程网上深圳全部的酒店信息批量摘下来。建立一个数据库。

还是一样，爬虫思路的构建是从分析目标网站的HTML代码开始的。

列表中第一个酒店是“南山citiGO”，我们直接在HTML码页面搜索（Ctrl+F）去定位（这样可以跳过大段的无关HTML代码）。于是，我们在514行发现了citogo的实际列表信息（酒店名称），以及“地铁站”“地址”“评价分数”“最低价格”等关键信息。我们的目的就是快速、批量获取&整理成数据库。

同时，网页分出了291个页面，也需要我们通过VBA-循环语句进行批量访问，提取每一页的酒店信息。

这个分页器的HTML代码其实蕴含着解决批量访问的关键！它可以为我们使用VBA-循环找到很好的路径。我们发现，该页面地址是根据shenzhen30/PXX(XX为页码)的规律排列的，尾页是291。哈哈哈，这个好办，VBA-循环就可以解决了！

通过分析，我们发现，页面虽大，HTML代码虽多，但酒店清单其实开始于514行，而开头的标志是："hotel_list"，分页器意味着结尾。也就是说，返回每页的这一段，就可以作为提取关键信息（字段）的文本依据了。

VBA代码：【建立msxml2.xmlhttp对象——发行请求——返回HTML文本——截取酒店清单列表——放入A1单元格】

结果返回

根据一开始的思路，我们要截取514行开始的酒店清单，也就是识别头部“hotel_list”以及尾部“”（位于543行）。这里我们用VBA-split函数，先把文本从hotel_list分开取第二段，然后再从分开取第一段。VBA代码如下：

单元格A1得到酒店清单信息，已经开始可以在头几行看到CITIGO酒店的名称，说明HTML 514行之前的内容被截掉了。

现在，我们要做的是：提取清单中每个酒店的名称，地址，评价分数，房价等信息。他要求我们，使用VBA进行字符串的操作，截取例如“深圳南山citiGO酒店”这样的文字，放入excel单元格。从页面上看，每张页面25个酒店。截取25个“酒店名称”相当于提取一个含25个元素的数组。每个数组元素表示一个酒店名称。

这个可以通过酒店名称的"hotel_num"作为前置标识符，分成25个数组，再通过""作为后置标识符截取。

vba代码如下：

我们可以看到结果很接近最后的目的了：

最后用vba-replace函数把这些替换掉，调整截取的初识位置，返回干净的酒店名称即可

返回结果

用类似方法，VBA函数的分列split，截取mid+instr，替换replace，调整初始位置等字符串操作，我们还能提取酒店地址，评价，价格等具体信息。代码如下：

携程深圳第一页的酒店清单相关信息，我们就全部清理出来了

接下来就是循环所有网页~通过shenzhen30/p &页码的格式进行循环。另外要注意，每循环一次是25个酒店信息，所以循环一页是25×页码（k=1 to 291）+i行(i=1 to 25)

于是，在2018-6-22晚上九点，我们爬取了携程在深圳的5261家酒店，耗时大概180秒

我们再来总价一下爬虫程序批量获取信息的方法论：

①分析网页HTML代码，找到关键信息的位置。观察关键信息前后的字符串特征。

②建立VBA object对象，请求返回HTML代码，同时观察HTML地址的特征，建立批量访问的循环语句

使用VBA函数，包括split, mid,instr,replace 等处理关键信息字符串，循环填充到excel单元格

接下来。。。。既然，有了这么多酒店信息，我们是否可以为将来开房做些有参考价值的数据挖掘呢？哈哈哈，当然是可以的。这里，我们着重介绍在excel中，将excel worksheet作为一个数据库，使用sql的分析操作。

sql是standard query language(标准查询语言)的缩写，它主要用于查询、分析数据库中的数据，例如一句比较标准的例句：select * from table_1 where price>500, 意思是，从table_1这张数据表中返回所有字段的信息，其中满足price字段下的所有数值＞500。虽然基于excel的数据分析中，数据透视&取数公式是主流，但在大量数据分析中，sql的优势是快速，标准化，查询结果的可移植。

在excel调用sql，主要的方法是：使用VBA建立一个数据库联结对象，将爬虫返回的excel worksheet作为一个数据库，再建立一个数据集对象，将worksheet数据库的表定义成数据集，使用vba-execute函数，用sql查询想要获得的信息，最后将查询结果复制黏贴到一个新的worksheet.

代码如下：

【查询目的：返回深圳酒店的价格分布，即分段返回价格在0-300，400-600，700-1000&1000以上的酒店家数】

查询结果如下：

然后画个图：

我们发现深圳的酒店价格大部分集中在300以下，而300-700的中等价位仍有一定占比。700-1000&1000以上价格偏高酒店总体数量不多。

当然，这只是个简易的分析实例，我们可以用sql或者其他方法对数据进一步挖掘，获得更有价值的信息，为战略决策服务。

就这样，我们通过简单但深入本质的组合工具“爬虫+HTML+VBA+SQL”，对所谓“大数据”这个概念有了一次初步但具体认识。（以后也不会这么简单就被忽悠了有没有。。。）。谢谢各位的耐心，我们下次再聊~