Excel 2016 实现共词分析的方法

2023-11-14陈书敏

现代计算机 2023年17期

陈书敏

（江西医学高等专科学校基础医学院，上饶 334000）

0 引言

国内文献计量学研究的共词分析一般有三种方法来实现：第一，使用Bibexcel、Citespace、BICOMB、Histcite、UCINET 等文献计量学分析工具软件；第二，直接用VB、VC、VBA语言编写软件；第三，直接两两成组对检索来实现［1］。加之，不同的分析工具软件使用的方法不同、支持的数据来源不同、数据格式不同。如Histcite、Bibexcel 以Web of Science 数据源为基础，不能直接对知网、维普、万方等中文数据源进行文献分析；第二种方法难于理解和掌握，不利于学习使用；第三种方法过程比较复杂，不利于操作。

相对于上述方法，Excel 不需要太多学习成本、操作简单，是很好的文献计量研究的有效工具［2］。

Excel 在国内文献计量学研究中应用不多［3-5］。目前，从国内外数据库检索出的Excel在共词分析的理论研究文献只有两篇。这两篇文献都是2011 年发表的，均使用的是2010 及以前的版本。现在已经过去了十多年，Excel 版本在不断更新，功能也随之增加增强。使用新版本新方法（即Excel 2016）能让Excel 在共词分析中的应用更加高效。目前在国内外文献中还没有此类理论研究报告。

采用Excel 2016版更易于应用推广，具体原因如下：①Excel 2019、Excel 2021 与Excel 2016最大差别是增加了图标功能、新的函数和图表，其他的功能和操作没有太大的变化［3］；②Excel 2019 以上版本必须要安装在Window 10操作系统上，这对硬件要求比较高，从而造成一些老旧电脑无法安装；③高版本可以兼容低版本，且它们的操作相差不大；④本文使用的“逆透视列”和“多个工作表合并”均是Excel 2016版本中Power Query（查询增强版）插件的一个功能。Excel 2010版本没有此插件，国产的办公软件WPS也没有此插件。

本文以人工智能在医学领域应用的文献分析为例，从数据收集和数据分析处理的角度比较使用Excel 2016 与Excel 2010 版本实现共词分析的方法。希望此文能推动共词分析法在文献计量学的普及应用。

1 数据收集

1.1 原始数据导出

在“中国知网”中以“人工智能”并含“医学”为检索条件进行“同义词扩展”和“精确”匹配，检索截至时间为2018 年12 月31 日。点击“自定义导出电子表格”，直接导出数据。

1.2 数据预处理

将知网文献记录导出后需要对数据进行预处理，去除无效的记录。对比Excel 2016 与Excel 2010版本的操作，具体见表1。

表1 不同版本预处理效果比较

1.2.1 去除一稿多投

去除“一稿多投”无效记录的操作通过标识、筛选、甄别三步实现。在Excel2016 中，具体操作如下：第一步，打开前面导出电子表格文件，点击“Title-题名”所在列，选择“开始”选项卡→“条件格式”→“突出显示单元格规则”→“重复值”→设置重复值的颜色；再点“Author-作者”所在列，重复操作一次。这样就将题名相同、作者相同的单元格用颜色标识出来了。

第二步，选择文献记录所在任意一个单元格，点击“数据”选项卡→“筛选”按钮，再点击“Title-题名”所在列旁的下拉菜单→“按颜色筛选”→“按单元格颜色筛选”→选择刚刚条件格式设置的颜色；再点击“Author-作者”所在列，重复操作一次。这样就将题名和作者均相同的记录筛选出。最后一步，查看“文献来源”“摘要”等字段，甄别哪些记录是“一稿多投”。

1.2.2 去除广告类无效记录

常常遇到类似于“本期专论导读”“捐赠”“稿约”“圣爱中医馆简介”这类广告性质的文献记录，内容与研究主题无关，须要去除。故对题目字段进行不包含某些特定关键词筛选，实现去除无关记录的目的。下面以去除包括“稿约”关键词的记录为例，在Excel 2016 中的具体操作如下：第一步，选择“Title-题名”所在列，点击“数据”选项卡→“筛选”按钮，再点击题名列旁的下拉菜单→“文本筛选”→“不包含”；第二步，在弹出的对话框中，输入关键字“稿约”即可。

1.2.3 去除新闻类无效记录

常常遇到类似于“热烈庆祝《XX》又上新台阶”“某某系统通过技术鉴定”这种与研究主题相关的文献记录。其内容或是简单的新闻介绍，或是科普性说明，没有太多学术价值，也要去除。这类文献记录一般没有作者或是“本刊讯”。因此对作者字段进行文本筛选，去除关键字为空或“本刊讯”的记录，在Excel 2016中的具体操作如下：第一步，选择“Author-作者”所在列，点击“数据”选项卡→“筛选”按钮，再点击作者列旁的下拉菜单→去除“空白”复选框前钩；第二步，与前面所述不包括关键字文本筛选操作一样，且把关键字设置为“本刊讯”。

虽然上述Excel 2016数据预处理的方法也可用于Excel 2010 版本，但从目前检索到的文献来看，之前研究者仅使用Excel“高级筛选”功能去除完全相同的记录来进行数据预处理，而这种方法在实际操作中作用不大。因为随着学术不端检测手段越来越先进，原文抄袭或大段文字抄袭变得越来越少［4］。

2 数据分析处理

2.1 年份分布

Excel 2010 采用“Countif”函数或数据透视表来实现年份分布的统计，其中数据透视表的操作更为便捷［5］。之前研究者直接对年份字段进行统计，然而在使用过程中会发现有些文献记录无法在“Year-年”字段显示相应年份，这样会造成某些有效记录没有被统计到。进一步研究发现，在“PubTime-发表时间”字段不会缺失，且字段的左侧前四个数字为发表的年份。因此先使用Excel2016 自动筛选功能筛选出年份字段为空白的文献记录，再在“年份”单元格中输入函数“=left（发表时间字段的单元格内容，4）”获得发表年份，最后使用数据透视表统计每一年的发表数量。

以人工智能在医学领域应用的文献分析为例，采用上述改进方法可得我国人工智能在医学领域的应用研究情况（1981—2018 年），总体上呈现出阶梯式增长趋势，并由此划分为尝试期、起步期、发展期三个发展阶段。

综上所述，对比Excel 2016 与Excel 2010 版本的操作得到表2，Excel 2016效果更好。

表2 不同版本年份分布处理效果比较

2.2 研究热点

2.2.1 词频统计

词频统计是热点研究分析的基本方法，通过关键词分列和关键词统计两步操作来实现［5］。

对比Excel 2016 与Excel 2010 版本的操作得到表3。由表3 可知，Excel 2010 因不能去除关键词之间的多个相同分隔符而产生许多无效的空白列，增加了要处理的数据量。若在分列前先将它们统一转换为同一个有效分隔符，则可以避免上述问题。Excel 2016 在分列前使用了“查找替换”实现了上面设想，改进了关键词分列操作。

表3 不同版本词频统计分析比较

由表3 可知，Excel 2010 版本进行关键词统计不仅会随着关键词列数变多而变得繁琐，而且会保留许多字段值为空的数据，例如将多列逐个复制首尾相连成一列的操作。假设关键词有n列，则需要执行n-1列复制步骤才能将所有关键词排列成一列。而使用Excel 2016的逆透视列功能可轻松实现相连成一列的效果。即选中所有的关键词，点击“数据”选项卡→点击“从表格”→进入“查询编辑器”→点击“转换”选项卡→点击“逆透视列”下拉列表中的“逆透视列”选项→选中“属性”所在列→点击“开始”选项卡→点击“删除列”按钮→点击“关闭并上载”按钮→得到最后结果。这个方法不仅快捷而且能自动将字段值为空的数据去除掉。

2.2.2 高频词

高频词能提供定量和定性的分析，突显出过去和现在研究领域的热点，是一种重要的研究数据［6］。高频词的确定分三步实现。第一步用高低频词界定公式，确定高频词的阀值；第二步按关键词统计计数结果降序排列；第三步确定高频词范围［5］。在实际操作过程中后两步可以简化改进为直接用高级筛选功能一次实现。

为了分析人工智能在医学领域应用不同阶段的研究热点变化，现将关键词按三个不同发展阶段分类并逐个分析。以起步期的关键词为例，经数据透视表对关键词频次统计可得关键词出现1 次数量为673。按照高低频词界定公式：÷ 2，其中I1指只出现过1 次的关键词数量，T为高频词中的最低阈值［7］。故I1为673，T值为36.19，四舍五入阀值为36。

下面用“高级筛选”获得高频词，具体操作如下：点击“数据”选项卡→点击“排序和筛选”组中的“高级”按钮→进入“高级筛选”详细设置，其中列表区域选择前面数据透视表数据，条件区域设置为“频次大于等于36”→“确定”按钮，得到两个高频词：人工智能和专家系统。

由于得到高频词数量太少难以分析研究热点，故选择占总词频20%以上的关键词为高频关键词，即选择频次前20%的关键词为高频词［8］。具体操作步骤如下：选择要操作的数据，再点击“数据”选项卡→点击“排序和筛选”组中的“筛选”按钮→点击“频次”选项卡→点击“数字筛选”→点击“前10 项”→设置筛选条件前20%→点击“确定”按钮，筛选出符合要求的高频词。

2.2.3 共词矩阵

采用Excel 2010 构建共词矩阵分三步实现。第一步清除低频词；第二步形成高频词两两组队；第三步构建矩阵［5］。若使用Excel 2016来实现，则操作更为简便，效率更高。但这个新办法需要改变上面的操作次序，两两组队即高频词两两配对，形成两列高频词队；第二步清除低频词；第三步构建矩阵。

（1）形成高频词两两组队。对比Excel 2016与Excel 2010版本的操作得到表4。由表4可知，Excel 2010高频词两两组队分两步实现。第一步先以第二关键词为主关键词进行排序，将第二个关键词中为空白的条目删除。这样可以将空白条目和单一关键词删除；第二步使用复制粘贴的办法，将所有关键词列两两组合并首尾相连成两列关键词［5］。假设有n列关键词，任取2列，其组合共有种，需进行次首尾相连。以起步期的关键词为例，总共有18 列，共有种组合，需进行153 次两两配对，152 次首尾相连复制粘贴。上面整个操作过程繁琐，且容易出错，而使用Excel 2016“逆透视列”和“多个工作表合并”的操作则不同。具体操作如下：第一步“逆透视列形成两两数列”。单击关键词列表区域任意单元格，再依次点击“数据”选项卡→点击“从表格”→进入“查询编辑器”→选中“关键词2”，再按住shift键不放，点击最后列（这样选择了除“关键词1”以外所有列）→点击“转换”选项卡→点击“逆透视列”下拉列表中的“逆透视列”选项→选中“属性”所在列→点击“开始”选项卡→点击“删除列”按钮→点击“关闭并上载”按钮→得到最后结果，并导出到新工作表中。这个操作结果能在直接清除空白条目和单一关键词条目的同时，得到第一列关键词与其他列关键词配对成的两两数列。若想得到第二列关键词与其他列关键词配对成的两两数列，只需将第一列关键词删除掉，重复上面操作即可。由于最后两列关键词已经配对，不需要使用“逆透视列”操作，故n列关键词实际只需进行n-2 次“逆透视列”操作，产生n-2个新工作表。加上最后两列关键词配对的工作表，共有n-1个工作表，需n-2次首尾相连复制粘贴才能合并在一起（以起步期的关键词为例，总共有18 列，需进行16 次“逆透视列”操作，产生16 个新工作表，将17 个工作表合并在一起，需16次首尾相连复制粘贴）。这样将n-1 个工作表合并在一起，也是一个繁琐操作。而使用Excel 2016的第二步“多个工作表合并”操作可解决上面问题。具体操作如下：点击“数据”选项卡→点击“新建查询”→“从工作簿”→选择工作簿→启动工作表合并导航器，点选“选择多项”→在要合并的工作表前打钩→点击“编辑”按钮→“开始”选项卡→点击“追加查询”→点选“三个或更多表”→再次选择要追加查询的工作表→点击“确定”按钮→点击“关闭并上载”按钮→得到多工作表合并最终结果。Excel 2016“多个工作表合并”优点在于不受工作表数量限制，直接首尾相连合并成两列关键词。

表4 不同版本构建共词矩阵分析比较

（2）清除低频词。Excel 2010 筛选出高频词的方法是先在每一列之后新建一空白列，再使用VLOOKUP函数找出与之匹配的高频词。其中低频词后面对应着“#N/A”，高频词后面对应着内容与高频词相同的词。再任取一列利用“排序”将值为“#N/A”的行全部排在一起，最后删除这些行，得到这一列的高频词。其它列的高频词也是这样操作得到的［5］。如前所述，第一步是清除低频词，而关键词列数越多则处理次数也越多。假设有n列关键词，每一列需在其后新建一空白列一次，使用VLOOKUP 函数一次，排序并删除低频词一次，则总共需要4×n步才能完成。以起步期的关键词为例，需要处理18 次，共72 步才能清除低频词。由于应用Excel 2016新办法的操作次序发生变化，使得实际处理的关键词列数只有2列，此时再使用“高级筛选”就可得到高频词列。具体操作如下：点击“数据”选项卡→点击“排序和筛选”组中的“高级”按钮→进入“高级筛选”详细设置，其中列表区域为原始数据，条件区域为两列高频词→“确定”按钮。

（3）构建矩阵。将得到的高频词队列交叉复制到列下，形成新的高频词队列；再对其使用“数据透视表”分析可得共词矩阵。具体操作：将高频词队列和高频词交叉后的队列分别存放在两个工作表中，再使用“多个工作表合并”将他们合并成一个队列，最后使用“数据透视表”分析得到共词矩阵。