Excel丨自动提取DOI与作者引用

萜妹是office的狂热粉丝，前阵子写了套自动提取DOI和作者年份的EXCEL公式，这周打算借着这个公式来介绍一些EXCEL函数吧。

那我们开始啦～

本期推送目标：学会自动提取DOI与作者

由于最开始设计公式是为了LME元分析项目，所以公式针对的是如下引用形式：

Goetz, K. H. (2017, Mar). political leadership in the European Union: a time-centred view [Article]. European Political Science, 16(1), 48-59. https://doi.org/10.1057/eps.2015.115 

后续的介绍，我会讲述我设计出两个自动提取公式的心路历程以及所用的每个函数的意义，小可爱们可以依据这些函数调整出自己需要的格式。

提取DOI

不难发现，DOI通常位于引用的最末端，所以我们首先要用到提取末端字符的函数——RIGHT。

RIGHT的作用是：根据所指定的字符数返回文本字符串中最后一个或多个字符。具体公式如下：

RIGHT(text,[num_chars])

• text：指要提取字符的文本字符串；

• num_chars：指定希望 RIGHT 提取的字符数。

我们可在【text】中填入引用所在单元格【A1】，但【num_chars】的数值尚不清楚。

再回顾引用可以发现，doi的前缀其实是有规律的，即【https://doi.org/】，因此我们可以通过识别固定的字符串而定位到具体的字符位置。这里需要用到FIND函数。

FIND(find_text, within_text, [start_num])

• find_text：要查找的文本；

• within_text：包含要查找文本的文本；

• start_num：指定开始进行查找的字符。

  注：within_text 中的首字符是编号为1的字符。如果省略 start_num，则假定其值为 1。

为了运算的简单，我们把需识别的固定字符缩短为【org/】，那么这里【find_text】为【“org/”】。注意要加“”号，这样才代表了文本，否则会报错。

此时，FIND("org/",A1)=238，这代表“org/”中的o是A1的238个字符。

考虑到RIGHT函数的字符数是从后数起的，所以我们还要用总体字符数减去固定位字符数才行。

【LEN(text)】

• text：指要提取字符的文本字符串。

LEN 返回文本字符串中的字符个数。

但是LEN(A1)-FIND("org/",A1)定位到的字符数是包含“rg/”的，所以我们最终还应该减去3个字符，才是最终的【num_chars】。

因此完整公式=RIGHT(A1,LEN(A1)-FIND("org/",A1)-3)

自动生成作者及年份

生成作者及年份的引用方式要复杂一些。因为根据作者的数量，引用的方式会有不同。

• 1人：Ronnle, 2017；

• 2人：Gedikli & Balaban, 2018；

• 3人及以上：Canete et al., 2018。

所以我们肯定要涉及IF函数。

IF(Logical_test, Value_if_ture, Value_if_false)

• Logical_test：逻辑检验；

• Value_if_turet：检验为真时的结果；

• Value_if_false：检验为假时的结果。

逻辑检验就看三者的作者引用有什么不同：

• 1人：Ronnle, E. (2017). Planners' analysis ……

• 2人：Gedikli, B., & Balaban, O. (2018). An ……

• 3人及以上：Canete, J. A., Navarro, F., & Cejudo, E. (2018). Territorially ……
  

观察得出，三种方式在作者部分均有【,】字符，且根据作者数量分别为1个、3个和更多个。

所以萜妹决定把上述条件作为逻辑检验，那这怎么计算文本中特定字符的次数呢？

萜妹原本也不会，但是度娘是万能的，于是让我找到了SUBSTITUTE公式。

SUBSTITUTE(text, old_text, new_text, [instance_num])

• text：需要替换其中字符的文本，或对含有文本（需要替换其中字符）的单元格的引用；

• old_text：需要替换的文本；

• new_text：用于替换 old_text 的文本；

• Instance_num：指定要发生替换的出现位置。 

  注：如果指定了 instance_num，则只有满足要求的 old_text 被替换。否则，文本中出现的所有 old_text 都会更改为 new_text。例如，指定3则替换第三个old_text。
  

所以【SUBSTITUTE(LEFT(A2,FIND("(",A2)),",",""】意味着将所有的【,】替换为了【】（空值）。

那么此时计算原文本长度与新文本长度的差值，便是【,】的数量。

考虑到标题和其他部分也有可能出现【,】符号，因此我们将替换计数的部分局限于年份之前，这样就不会有纰漏，LEFT函数可以帮助我们实现这个功能。

LEFT(text,[num_chars])

• text：指要提取字符的文本字符串；

• num_chars：指定希望 LEFT 提取的字符数。

再结合FIND函数可以提取年份前的文本，即【LEFT(A1,FIND("(",A1)))】

最后结合LEN函数，计算年份之前的【,】数量的公式为【LEN(LEFT(A1,FIND("(",A1)))-LEN(SUBSTITUTE(LEFT(A1,FIND("(",A1)),",",""))】

解决完逻辑判断，我们还要形成各类结果。

不难发现，它们都是由作者与年份两个部分组成，那我们先来提取简单的年份。

年份由于在文本的中央，所以要用MID函数。

MID(text, start_num, num_chars)

• text：指要提取字符的文本字符串；

• start_num：

  文本中要提取的第一个字符的位置；

• num_chars：指定 MID 从文本中返回字符的个数。

结合FIND函数，我们对年份的提取公式为：【MID(A1,FIND("(",A1)+1,4)】

而对作者的提取稍微有些复杂，首先三种情况都需要提取第一位作者的名，我们借助LEFT函数进行：【LEFT(A1,FIND(",",A1)-1)】

此时，1人与3人作者时的引用表达已经出来了，利用连字符【&】，我们将公式写成：

• 1人：【LEFT(A1,FIND(",",A1)-1)&", "&MID(A1,FIND("(",A1)+1,4)】；

• 3人：【LEFT(A1,FIND(",",A1)-1)&" et al., "&MID(A1,FIND("(",A1)+1,4)】。

2人作者的复杂点在于还需提取第二个作者的名，这里就要灵活运用上述的多个公式。

最终第二个作者名字的公式为：

【LEFT(MID(A3,FIND("&",A3)+2,LEN(A3)),FIND(",",MID(A3,FIND("&",A3)+2,LEN(A3)))-1)】

有兴趣的小可爱可以自己推推，萜妹就不展开了。

因此，完整公式为【=IF(LEN(LEFT(A1,FIND("(",A1)))-LEN(SUBSTITUTE(LEFT(A1,FIND("(",A1)),",",""))=1,LEFT(A1,FIND(",",A1)-1)&", "&MID(A1,FIND("(",A1)+1,4),IF(LEN(LEFT(A1,FIND("(",A1)))-LEN(SUBSTITUTE(LEFT(A1,FIND("(",A1)),",",""))=3,LEFT(A1,FIND(",",A1)-1)&" & "&LEFT(MID(A1,FIND("&",A1)+2,LEN(A1)),FIND(",",MID(A1,FIND("&",A1)+2,LEN(A1)))-1)&", "&MID(A1,FIND("(",A1)+1,4),LEFT(A1,FIND(",",A1)-1)&" et al., "&MID(A1,FIND("(",A1)+1,4)))】

啦啦啦，这篇推送就到这里啦。想要完成这些步骤并非一定要用上述的组合方式，小可爱们也可以自行探索其他的组合呀。

另外，可能最后的完整公式对绝大部分的小可爱没用，但是前面介绍的那些小函数，融会贯通还是能在不经意的时候起到大用处的。萜妹有时间会再来介绍其他EXCEL函数哒~

最后，萜妹最近不太顺。推送我还是尽量不鸽，如果开启冬眠会提前告诉大家的。那不出意外，我们下周见~

往期推送