![1d5bb002590ecd1893b7d08f07821ea2.png](https://i-blog.csdnimg.cn/blog_migrate/b4d955df86d3151d6a4698de6ef7a7d1.jpeg)
今日目标:
认识Excel的强大功能
心血来潮在知乎里面搜索一下Excel,想学习一些高点赞文章的写作方法。
![c9da447d9cdd7807e66ce0831306adc5.png](https://i-blog.csdnimg.cn/blog_migrate/d89bffc4c9d7c28cc645dfa070371e43.jpeg)
看到这些标题,完了,一下子勾起下载和收藏的欲望啦!
怎么样把所有高点赞的文章全部都抓下来呢?
开始的时候我想的是用Python。后来想一想,用Power query好像也可以实现,于是就做出了下面这个效果。
![e1909c948afbcdb506dddf46289f2575.gif](https://i-blog.csdnimg.cn/blog_migrate/d20208391adc9280fa9d943f6ea37041.gif)
在表格里面输入搜索词,然后右键刷新,就可以得到搜索结果。
![75330bb06187429c562c43c2832ac5ed.png](https://i-blog.csdnimg.cn/blog_migrate/b293b8ba998730b735fd297698ed2010.jpeg)
你能明白我一定要抓到表格里吗?
因为Excel里可以根据「点赞数量」直接排序啊!
那种感觉就跟排队一样,到哪里排队,我都是第1个,挑最好的!
![428c96100606c42e3b2fad585fd9419a.gif](https://i-blog.csdnimg.cn/blog_migrate/c8882af3e441a40691c3169113f7055c.gif)
好了,闲话少说,我们来看一看这个表格是怎么做出来的。
大致可以分为4个步骤:
1- 获取JSON数据连接
2- Power query处理数据
3- 配置搜索地址
4- 添加超链接
1-操作步骤
1- 获取JSON数据连接
平常在浏览网页的时候,是一个单纯的网页地址。
![a13c0b0dccc36efc681cf5f47aec77b3.png](https://i-blog.csdnimg.cn/blog_migrate/4ac16e3fd5fb35110dce0f0f7976b70d.jpeg)
而网页里所看到的数据,实际上也有一个单独的数据链接,这个可以在浏览器里面查找到。
![0b57222cb0e2e7f90f31ef997786f199.png](https://i-blog.csdnimg.cn/blog_migrate/33be68bc4fd3e84b0767c08744cf3c74.jpeg)
我们需要的数据链接,对应的通常是JSON格式的数据,就像下面这样。
![3bf2a18c569cb006767d387b7d845792.png](https://i-blog.csdnimg.cn/blog_migrate/96438939199c8071ce6b9d76727ae88c.jpeg)
查找的方法,需要进入到开发者模式,然后查看数据的Network变化,找到xhr类型的链接,其中一个就是数据的传输连接。
![bdc6ee7026844810d12bcbf65d6a7ecd.gif](https://i-blog.csdnimg.cn/blog_migrate/9ef236780ef8b4ebfd682a42b9fa2aab.gif)
把这个链接复制下来,这就是Power query要抓取数据的链接。
2- Power query处理
你可能不知道,Power Query除了可以抓取Excel当中的数据之外,还可以抓取SQL、Access等多个类型的数据:
![4b9284bf7d3390540e7540233453e6d2.png](https://i-blog.csdnimg.cn/blog_migrate/874e093263d0df0cafa748cc8b043920.jpeg)
网站数据也是其中一个:
![c15c6afdeabb686ac151a1c934de3a41.png](https://i-blog.csdnimg.cn/blog_migrate/fa6111e22bc2895c35585c4804e65fc7.jpeg)
把前面我们获取的链接,粘贴到PQ里面,链接就可以抓取数据了。
![a5efc233a699b6f526c246d5f2fe8e54.gif](https://i-blog.csdnimg.cn/blog_migrate/0632d270f6f6ca1eeb96fe7662a447bb.gif)
然后得到的是网页的数据格式,怎么把具体的文章数据获取到呢?
Power Query强大的地方就在于,它可以自动识别json的数据格式,并解析提取具体的内容。
整个过程,我们不需要做任何的操作,只是鼠标点点就可以完成。
![f0cfa8d0ee0fd9cd5bf78409a79b94a6.gif](https://i-blog.csdnimg.cn/blog_migrate/b30e94ca62da8c021819c02b4906bf25.gif)
这个时候我们获得的数据,会有一些不需要的多余的数据.
比如说:thumbnail_info(缩略图信息),relationship,question,id.1等等。
![8aed70c2c52666d498de2fb9ffac3a77.png](https://i-blog.csdnimg.cn/blog_migrate/36b05e3c29b43dca21ba601a275879a6.jpeg)
把它们删掉,只保留可需要的文章的标题、作者、超链接等等就可以了。
![453ee97274f204a4a7d8926d67671c66.gif](https://i-blog.csdnimg.cn/blog_migrate/8be71fa03a5eb355150c4e1b7fe35be9.gif)
数据处理完成后,在开始选卡,点击「关闭并上载」,就完成了数据的抓取,非常的简单。
![38f552268c2f4d86e2e6a20091724f56.gif](https://i-blog.csdnimg.cn/blog_migrate/10189b416929d162d5fa989f312d8861.gif)
3- 配置搜索地址
不过这个时候,我们所抓取到的数据是固定的,没办法根据我们输入的关键词来更新。
这是因为数据超链接当中所包含的搜索词没有更新。
![8ee358a59216e126b192e18dc99e9155.png](https://i-blog.csdnimg.cn/blog_migrate/f25f39bc1c716268276b1ec538a34e0a.jpeg)
所以这一步呢,我们需要配置一下这个数据链接,实现根据搜索词动态更新。
在表格里面新创建一个数据,然后加载到Power query里面。
![c7614f2b38811f4e61d7dfe697130470.gif](https://i-blog.csdnimg.cn/blog_migrate/c07e5056e62904927d2a4ffabfc6dcec.gif)
再获取这个搜索词,以变量的形式放在搜索地址里面,就完成了搜索地址的配置。
![3f7213e536153ea6290d0839500d9621.png](https://i-blog.csdnimg.cn/blog_migrate/491d0870be25f7761265733fcd2562bf.jpeg)
修改后的地址代码如下:
getdata = (page)=>
let
keywords = 搜索词[ 搜索词]{0},
源 = Json.Document(Web.Contents("https://www.zhihu.com/api/v4/search_v3?t=general&q="& keywords & "&correction=1&offset="& Text.From(page*20) &"&limit=20&random=" & Text.From(Number.Random))),
data = 源[data],
jsondata = Table.FromList(data, Splitter.SplitByNothing, , , ExtraValues.Error)
in
jsondata,
转换为表 = Table.Combine(List.Transform({1..10}, getdata)),
4- 添加超链接
到这一步所有的数据都已经处理完成了,但是如果想要查看原始的知乎页面,需要复制这个超链接,然后在浏览器里打开。
![6300153ee8852764b00b71cde8ad0ece.gif](https://i-blog.csdnimg.cn/blog_migrate/487a2c7c09396a0a4aed48414d4f8527.gif)
每次要点击好几次鼠标比较麻烦,这里我们借助HYPERLINK这一个函数,生成一个可以点击的超链接,这样访问起来就会简单很多了。
![95b1d4a754b6fdc0854555dafb49c3d0.gif](https://i-blog.csdnimg.cn/blog_migrate/cdafa548767c85c621a8024b77188796.gif)
5- 最后效果
最后的效果就是:
1- 输入搜索词
2- 点击右键刷新
3- 找点赞最高的
4- 点击「点击查看」,享受插队的感觉!
![26f6157b498c611781b5f9953a3cf7f8.png](https://i-blog.csdnimg.cn/blog_migrate/5c2bd01f20d1d3d4bb7b16f532657b2d.jpeg)
2- 总结
知道在表格里面搜索的好处了吗?
1- 按照「点赞数」排序,「评论数」排序
2- 看过的文章,可以加一列写备注
3- 可以筛选自己喜欢的「作者」等等
明白为什么,精英都是Excel控了吧?
现在大部分表格的使用者,还是把Excel当做一个报表工具,画画表格、写写公式,而已。
请你记住下面几个Excel新功能,这些功能已经让Excel成长为了,一个强大的数据统计、数据分析软件,不再是你印象中的报表而已。
1- Power query:数据整理、清洗工具,搭载M强大的M语言,可以实现多表合并,也是本文的主要技术。
2- Power Pivot:数据统计工具,可以自定义统计方法,实现透视表多字段计算、自定义DAX数据计算方式。
3- Power BI:强大、易用的可视化工具,实现交互式数呈现,是企业商务数据报告优质解决方案。
3- 更多资源
最后,我要强调一下
我们是一个专业的Excel培训机构
秋叶Excel
想要一对一解答问题吗?
扫码添加「秋小E」,有机会参加「秋叶Excel免费专业坐诊」活动,帮你在线答疑。
![9e2fe1abfbb068269264e649217b1211.png](https://i-blog.csdnimg.cn/blog_migrate/506c59af0c502dc4e18512447b9f6c9c.jpeg)
我是拉小登,一个会设计表格的Excel老师
![0aa07f6a86619866a4fc58f44b51eb99.png](https://i-blog.csdnimg.cn/blog_migrate/b744079b6355b8c9ac36c93c7ee23229.jpeg)