EXCEL我爱学习网

EXCEL学习及培训平台,包括Excel表格,基本操作,技巧,函数,常用公式,VBA等,excel模板及教程免费下载。

加入收藏 | 设为首页 | 会员中心 | 我要投稿
您当前的位置:首页 > excel教程 > excel技巧

excel爬虫

时间:2019-03-10 01:01:30  来源:excel我爱学习网  作者:杨超

现在很多数据都是被大公司所掌握,他们掌握了这些数据就可以提前规划、抢占先机,而我们这些普通人只有被动的接受他们有意传导的一些信息。我们能不能自已掌握一些大数据呢,当然可以。毫无疑问在数据爬取方面Python语言功能非常的强大,我们这些EXCEL爱好者大部分人都没有学过Python,专门去学习又非常耗时。从EXCEL2013版开始,我们就可以进行excel爬虫
声明一下我们要获取地是网络上公开的数据,不涉及商业机密。现在中国股市非常活跃,我们就来获取A股各上市公司的员工构成(按学历划分),看看哪些上市公司属于知识密集型,让我们好好地认识一下A股的上市公司。

 

一、WEBSERVICE函数

excel爬虫主要用到WEBSERVICE这个函数。

语法

WEBSERVICE(url)

WEBSERVICE 函数语法具有下列参数。

  • Url    必需。 Web 服务的 URL。

说明

返回 Intranet 或 Internet 上的 Web 服务数据。

备注

  • 如果参数无法返回数据,则 WEBSERVICE 返回错误值 #VALUE!。

  • 如果参数导致字符串无效或含有的字符超过允许的单元格限制(32767 个字符),则 WEBSERVICE 返回错误值 #VALUE!。

  • 如果 url 字符串所含字符超过 GET 请求允许的 2048 个字符,则 WEBSERVICE 返回错误值 #VALUE!。

  • 对于不支持的协议,例如 ftp :// 或 file://,WEBSERVICE 返回 #VALUE! 错误值。
     

二、excel爬虫-数据从哪里来

网络上有很多服务商提供了A股相关数据,我们今天从中商产业研究院数据库来获取数据,网址http://s.askci.com/stock/summary/000001/employee/,
网址中000001为平安银行的股票代码,我们可以通过改变这个代码来提取不同上市公司的信息。我们把这些数据放在A列,并根据需要下拉拖动填充。这时000001是无法直接有效输入EXCEL中的,我们在输入之前加个英文状态的单引号',或是改变单元格格式为文本。

 

三、excel爬虫-使用WEBSERVICE获取数据

在第二列,我们输入公式=WEBSERVICE("http://s.askci.com/stock/summary/"&A1&"/employee/"),现在A1单元格为000001,通过字符串连接符&,我们访问的网页就是http://s.askci.com/stock/summary/000001/employee/。获得的数据就像这样"<!doctype html><html><head>......"。这是HTML格式的数据,我们在浏览器看到的是这样(下图)。HTML格式数据我们普通人无法有效使用,需要处理一下,获得关键数据。




 

四、excel爬虫-HTML格式数据处理

 

我们在chrome浏览器中查看网页源代码,看一下,我们要获取的数据格式。





 

通过查看源代码我们发现我们要的数据在<td></td>中间,并且前面和后面都有很多我们不需要的数据。我们如何来取得我们所要的数据呢,EXCEL中有个FILTERXML函数,他是和WEBSERVICE配套使用的,可惜他只支持XML格式数据,我们的数据是HTML,他就没法实现我们的功能。既然EXCEL没有完善这一功能,我们只能用一些笨办法了,主要用SUBSTITUTE文本替换、 LEFT截取字符串、FIND查找字符串(若您不清楚这些知识点,请自行补充学习),综合起来使用取得我们要的数据。

 

五、excel爬虫-先正其名

000001表示平安银行,可能有些老股民清楚,但是我们很多人不知道,我们先把他的名字取出来。


取出名字比较简单,我们在E列, E1单元格=MID(B1,FIND("<title>",B1)+7,4)。先查找 "<title>"所在位置,再通过MID截取4个字符。上市公司大部分为4个字简称,有些3个字,这种处理有些瑕疵,会得到“万科A(”这种数据,无关大雅大家可以自行完善一下,我们继续往下。

 

六、excel爬虫-数据整理1

接第四条,在这里我们通过公式  C1单元格=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(B1,SEARCH("<td>本科或本科以上人数</td>*<td>",B1),1500),"<td>",""),"</td>",","),"<tr>",""),"</tr>","")," ",""),CHAR(10),""),CHAR(13),""),把数据截取一下。

在这里我们先把结果给大家看一下。通过公式,整理得到的数据为“本科或本科以上人数,2010,17.46,博士以上人数,7,0.06,大专人数,2288,19.87,研究生人数,170,1.48,中专及以下人数,7040,61.14,</tbody></table></div><divclass="clear"></div></div><divclass="footermg_ttwo"><divclass="footer_hrefl"><ahref="http://www.askci.com/reports/about/introduction.shtml"target="_blank">公司简介</a><i>|</i><ah“
第3张图片的源码格式,初步变成了我们想要的格式。
在这里我们一层一层地把这个公式的皮扒开,首先通过SEARCH("<td>本科或本科以上人数</td>*<td>",B1),我们得到关键数据所在的位置(000001,位置从14323字符)。
再通过MID((B1,SEARCH("<td>本科或本科以上人数</td>*<td>",B1),1500),表示为MID((B1,14323,1500),从14323字符开始取1500个字符。
接下来就是多次用SUBSTITUTE把<td>、  <tr> 、    </tr>、 空 格" "  、 CHAR(10)换行符、 CHAR(13)回车符替换成“”,即是把这些字符删除。  </td>特别处理,替换成“,”,以方便后期数据再次整理(单元格分列)。
通过多次SUBSTITUTE,得到了我们初步的数据了。但还是有些尾巴,像“</tbody></table></div><divclass="clear">.....",不是我们要的数据,为了性能及可阅读性,我们增加一列再处理这些数据。

 

七、excel爬虫-数据整理2

D1单元格=LEFT(C1,FIND("</tbody>",C1)-1),我们通过“</tbody>”所在的位置,再用LEFT截取字符串,在这里我们把后面无用的尾巴删除了。得到的数据为“本科或本科以上人数,29884,81.02,博士以上人数,——,——,大专人数,6514,17.66,研究生人数,——,——,中专及以下人数,487,1.32,”

 

八、excel爬虫-公式填充

我们先把000001填充一下,把A列填充为000001,000002,000003,......,根据个人需要自行填充。再把B到E列的单元格向下填充。至此,我们的动态数据已经获得,还缺少一步,就是把数据分开。

 

九、excel爬虫-数据分列

我们把D列的数据,像“本科或本科以上人数,29884,81.02,博士以上人数,——,——,大专人数,6514,17.66,研究生人数,——,——,中专及以下人数,487,1.32,”,整列地复制到F列,在这里要强调的是选择性粘贴操作。
接下来选择F列,”数据“菜单-"分列",按照步骤,步骤1选择分隔符号,步骤2选择逗号分隔,步骤3选择常规即可,数据就出来了。








要获取的数据,部分结果如下

 

在这里 还要再说明一下,像000003,可能目前还没有这支股票,会显示#VALUE!,大家可以手动删除错误信息,或是再加上IF(ISERROR(......)...)函数处理,在这里为了公式可阅读性,就不再增加代码了。

 

好了,我们EXCEL爬取数据是不是也很简单。在这里声明一下请勿将此用于非法用途。若您还有不清楚的可以联系QQ1876513803。

WWW.EXCEL520.CN原创作品,未经允许,请勿转载!

来顶一下
返回首页
返回首页
上一篇:VLOOKUP基本知识 下一篇:2019年工资及个税计算EXCEL表

excel常用函数
IF函数 SUM COUNT ROUND VLOOKUP SUMIF SUBSTITUTE ROUNDUP SUMIFS
发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表
推荐资讯
if函数多个条件怎么用
if函数多个条件怎么用
2019年工资及个税计算EXCEL表
2019年工资及个税计算
excel爬虫
excel爬虫
VLOOKUP基本知识
VLOOKUP基本知识
相关文章
    无相关信息
栏目更新
栏目热门