绝！⽤Excel做词云图，真的惊艳到我了！发送【计划】
本⽂作者：⼩爽
本⽂编辑：雅梨⼦、竺兰
⼤家好，我是学着起标题的⼩爽~
对于新媒体⾏业的打⼯⼈来说，他们经常需要给⽂章起标题。
但是从业初期，作为⼀名⼩⽩，每次起标题就感觉头⼤，灵感接近枯竭。
好的标题是⽂章成功的⼀半，要学会起标题，我们可以先研究⼀下别的是怎么做的。
⽐如说，秋叶 Excel。
所以，我爬取了 2018 到 2021 年秋叶 Excel 的所有标题，进⾏词频统计分析。
前期，我们需要准备好标题和词库表。
标题列表：
词库表：
话不多说，我们先来看看函数⽅法。
函数法
具体操作：
❶先将标题进⾏合并，同时将内容中的字母全部转为⼤写。
（由于标题中的函数名称既有⼤写，⼜有⼩写，所以我把所有的内容都转换为⼤写，⽅便后续
统计。）
公式如下：
=UPPER(CONCAT(A2:A578))
CONCAT 函数是⽤来合并数据的；UPPER 函数是⽤来将⽂本全部转换为⼤写的。
❷统计次数。
标题内容合并完了，接下来，就是进⾏词频统计了，我们先来看看函数公式。
公式如下：
=(LEN($G$2)-LEN(SUBSTITUTE(G$2,UPPER(D2),'')))/LEN(D2)
=(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，互帮互助。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。