Excel技巧，绝！用Excel做词云图，真的惊艳到我了！

 

转自：秋叶Excel

 

​

对于新媒体行业的打工人来说，他们经常需要给文章起标题。

 

​但是从业初期，作为一名小白，每次起标题就感觉头大，灵感接近枯竭。

 

​好的标题是文章成功的一半，要学会起标题，我们可以先研究一下别的公众号是怎么做的。

 

​比如说，秋叶 Excel。

 

​所以，我爬取了 2018 到 2021 年秋叶 Excel 公众号的所有标题，进行词频统计分析。

 

​前期，我们需要准备好标题和词库表。

 

标题列表：

​

 

 

词库表：

 

 

 

话不多说，我们先来看看函数方法。

 

图片

 

01 函数法

 

👉 具体操作：

 

​❶ 先将标题进行合并，同时将内容中的字母全部转为大写。

 

​（由于标题中的函数名称既有大写，又有小写，所以我把所有的内容都转换为大写，方便后续统计。）

 

 

 

公式如下：

​

=UPPER(CONCAT(A2:A578))

 

CONCAT 函数是用来合并数据的；UPPER 函数是用来将文本全部转换为大写的。

 

​❷ 统计次数。

 

标题内容合并完了，接下来，就是进行词频统计了，我们先来看看函数公式。

 

 

 

公式如下：

=(LEN($G$2)-LEN(SUBSTITUTE(G$2,UPPER(D2),"")))/LEN(D2)

 

​SUBSTITUTE 函数是一个替换函数，它能够将文本中旧字符串替换为新字符串。

 

​这里使用这个函数的目的是，将文本中包含词语的内容全部替换为空。

 

=substitute(文本,旧字符,新字符串)=SUBSTITUTE($G$2,UPPER(D2),"")

 

其中，G2 单元格是前面合并后的文本内容，由于公式后面需要填充，所以这里需要绝对引用。

​

UPPER 函数就是将词语的字母全部转为大写字母，主要为了跟前面文本内容全部转换为大写字母进行统一。

​

LEN 函数是用来计算文本长度的：

​

LEN(文本)-LEN(文本中将词语替换为空后)=该词语的总个数总次数=总字数/LEN(词语)

​

将「文本的总长度 」减去 「词语替换为空的文本长度」，就是「该词语的总字数」。

​

最后，「总字数」 除以 「词语的长度」，也就是「该词语所出现的总次数」，即我们想要的结果。

 

​整个函数公式的思路虽然比较绕，但是大体的逻辑还是比较清晰的。

 

 

 

接下来，我们就来看看 POWERQUERY 的做法。

在 M 函数中，就有一个函数可以直接查找出文本中所有位置的函数，跟着我一起看看吧。

 

​PS.POWERQUERY 中使用的函数叫 M 函数。

​

02 PQ 函数法

 

👉 具体操作：

 

​❶ 将数据导入到 PQ 编辑器中。

 

​① 我们先选中标题区域，按住快捷键【Ctrl+T】，将表格转换为智能表格，表的名称改为「标题」。

 

 

 

 

② 同理，我们选中词库表，按住快捷键【Ctrl＋T】，将词库表转换为智能表格，名称改为「词语」。

 

 

 

③ 选中标题表格区域，在【数据】选项卡中，单击【来自工作表】（版本不同，名称也有区别），调出 PQ 编辑器。

 

 

 

④ 复制一个表格，将公式中的标题，改成词语，最后将复制出来的工作表重命名为词语。

 

 

 

❷ 对标题表进行预处理。

 

​① 选中标题表-右键，选择深化：

 

 

 

PS. 深化的目的是将表转换为列表。

 

​添加公式：

 

 

 

公式如下：

= Text.Upper(Text.Combine(源[标题]))

​

​解析：

 

​① Text. Combine 是将列表合并为一个文本，跟前面 concat 函数的目的一样。

 

​② Text. Upper 是将合并后的字母转化为大写字母，跟前面函数法中的 upper 函数一样。

在这里，大家已经学会 pq 中的两个 m 函数了！棒(๑·̀ㅂ·́)و✧

 

​❸ 在词语表中，自定义添加公式。

 

​在【添加列】选项卡下，单击【自定义列】，新列名为【次数】。

 

​输入如下公式：

List.Count(Text.PositionOf(标题,Text.Upper([词语]),2))

 

 

 

 

Text.PositionOf(标题,Text.Upper([词语]),2)

​

​简单解释一下：

 

​Text.Upper([词语])是将字母都改成大写字母的函数。

​

Text. Positionof 函数是用来查找指定文本中，词语出现的位置的。

 

=Text. Positionof(文本，需要在文本中查找的内容，0/1/2) =Text.PositionOf(标题,Text.Upper([词语]),2)

​

​其中第三参数：

 

​① 0，表示查找第一次出现的位置；

​​② 1，表示查找最后一次出现的位置；

​③ 2，表示查找所有出现的位置。

 

​Text.PositionOf 查找所有位置后，形成的是一个列表，所以之后我们只需要使用 List.Count 函数进行计数就可以。

 

 

 

删除分类列和词语列。

 

 

❹ 加载到表。

​

① 在【主页】选项卡下，单击【关闭并上载至】，仅创建链接，单击【确定】按钮；

​

② 在查询列表中，右键词语表，选择【加载到】，导入数据中，选择【表】，位置选择 E1 单元格，单击【确定】按钮。

​

此时数据就已经导入了。

 

 

到这里，两种函数方法就都介绍完啦~

​

但是现在我们只有表格数据，如果直接进行分析，还显得不是特别直观。

​

一般词频分析，我们会选用词云图，但在 Excel 中，并没有这个图表。

​

不过也不用担心，我们可以借助 Excel 的加载项，去制作词云图。

​

操作特别简单，点点鼠标就能完成，我们继续往下看吧。

 

03 利用加载项制作词云图

E2D3 是一个可视化加载项，利用它，我们就可以制作 Excel 中没有的各种炫酷图表~
比如，按照时间变化的气泡图：

图片

做词频分析的词云图：

图片

展示数据流动利器的桑基图：

图片

还有很多其他炫酷图表，大家就自行挖掘啦～
​(后面有机会，我们也可以继续聊聊)
​👉 具体操作：

​
❶ 添加 E2D2 加载项。

​
① 在【插入】选项卡下，单击【获取加载项】。

图片

② 搜索 E2D3 插件，点击【添加】。

图片

❷ 新建一个空白工作表，单击 A1 单元格，找到词语图，单击【Visualize】。

图片

图片

❸ 替换数据源，全选替换后的数据源，单击【Reset data area】，即可生成对应的词云图。

图片

❹ 进行分析。
​① 一共有 577 个标题，感叹号就有 521 个，说明几乎每个标题都有含有感叹号(！)，问号(？)也出现了 200 次。
​② 标题中出现最多的对象居然不是小白，而是同事，可见，同事的「表」的问题，苦恼着各位表哥表妹们。
​③ 出现了高达 191 次的你，可见第二人称在标题中特别常见。

图片

在词云图中，我们也可以清楚的看到，感叹号，Excel，函数，你，出现的次数比较多～

图片

 

​05 总结

本文介绍词频统计的两种方法：

​
▋函数方法（适用于所有版本）
​思路：通过将文本的词语替换为空，统计文本的长度 减去 替换为空的文本长度就是该词语的总字数。
​最后总字数 除以 词语的本身的长度，就是总次数。
​▋M 函数方法（需要 Excel 中存在 powerquery 的版本）
​❶ Text. Positionof 函数可以统计词语中出现的所有位置。
​❷ List. Count 可以进行计数。
​▋词云图
​它是词频统计中，经常使用到的图表。

在 Excel 中，虽然没有词云图，但是我们可以借助加载项 E2D3 制作词云图。
​美中不足的是，这个加载项不是特别的灵活，而且需要 Office 2013 及以上版本可用，但它够我们在日常工作中使用了。

 

​以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

学习资料见知识星球。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利​​​​！

更多技巧， www.excelbook.cn

欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；

1、价值上万元的专业的PPT报告模板。

2、专业案例分析和解读笔记。

3、实用的Excel、Word、PPT技巧。

4、VIP讨论群，共享资源。

5、优惠的会员商品。

6、一次付费只需129元，即可下载本站文章涉及的文件和软件。