绝！用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，互帮互助。

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

赞 (0)

相关推荐