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

点击蓝字【秋叶 Excel】👆

发送【计划】

免费领计划表模板！

本文作者：小爽

本文编辑：雅梨子、竺兰

大家好，我是学着起标题的小爽~

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

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

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

比如说，秋叶 Excel。

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

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

标题列表：

词库表：

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

函数法

👉 具体操作：

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

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

公式如下：

=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 函数。

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 的加载项，去制作词云图。

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

利用加载项制作词云图

E2D3 是一个可视化加载项，利用它，我们就可以制作 Excel 中没有的各种炫酷图表~

比如，按照时间变化的气泡图：

做词频分析的词云图：

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

还有很多其他炫酷图表，大家就自行挖掘啦～

(后面有机会，我们也可以继续聊聊)

👉 具体操作：

❶ 添加 E2D2 加载项。

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

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

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

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

❹ 进行分析。

① 一共有 577 个标题，感叹号就有 521 个，说明几乎每个标题都有含有感叹号(！)，问号(？)也出现了 200 次。

② 标题中出现最多的对象居然不是小白，而是同事，可见，同事的「表」的问题，苦恼着各位表哥表妹们。

③ 出现了高达 191 次的你，可见第二人称在标题中特别常见。

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

总结

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

▋函数方法（适用于所有版本）

思路：通过将文本的词语替换为空，统计文本的长度 减去 替换为空的文本长度就是该词语的总字数。

最后总字数 除以 词语的本身的长度，就是总次数。

▋M 函数方法（需要 Excel 中存在 powerquery 的版本）

❶ Text. Positionof 函数可以统计词语中出现的所有位置。

❷ List. Count 可以进行计数。

▋词云图

它是词频统计中，经常使用到的图表。

在 Excel 中，虽然没有词云图，但是我们可以借助加载项 E2D3 制作词云图。

美中不足的是，这个加载项不是特别的灵活，而且需要 Office 2013 及以上版本可用，但它够我们在日常工作中使用了。

问问大家，在工作中，有没有用到词频统计?

一般用在哪些场合呢？

你们会使用哪些神奇的小工具呢？

欢迎在留言区与我讨论哦~

欢迎加入秋叶Excel专属读者群~和群友一起互相交流学习 Excel，互帮互助。

现在进群，还会掉落各种学习资源，助力大家提升办公效率

↓↓↓

遇到有价值的文章

不放过 !

👇👇👇

动动小手

分享给朋友~

👇👇👇