摘要：尝试把函数“叠Buff”，组合出你的专属工具库

数据核查是临床试验过程中的重要环节，它贯穿于EDC系统上线后到锁库前的各个阶段，用以确保数据的准确性、完整性和一致性。其中，人工核查是数据核查中的关键步骤，针对无法设置EDC在线及SAS程序的逻辑规则的数据，可通过Excel等工具进行数据核查。下面分享一些数据核查中可能会用到的Excel函数小技巧，供大家参考：

一、数据中指定文本提取

1、提取单元格中第n个指定符号之前的内容：可以通过该符号进行数据分列，但有可能会产生多列不需要的数据，此时就可以使用一个经典函数进行数据提取。

=LEFT(提取单元格, FIND("指定符号", 提取单元格) - 1) ，这是提取第一个指定符号后的内容，以此为基础嵌套FIND函数：=LEFT(提取单元格,FIND("指定符号",提取单元格,FIND("指定符号",提取单元格)+1)-1)，就变成提取第二个指定符号前的内容，以此类推。

例如：实验室检查、既往/合并用药、既往/合并非药物治疗表单，从不良事件编号的链接中仅提取AE编号，如图所示，可以在B2单元格中输入【=LEFT(A2, FIND("-", A2) - 1)】，A2为需要提取内容的单元格，“-”为指定的符号，如果A2单元格中没有“-”字符，将返回错误值“#VALUE!”，可以使用IFERROR函数处理，【=IFERROR(LEFT(A2, FIND("-", A2) - 1), "自定义")】公式将错误值输出为 “自定义”的内容，如“未找到”。这样就提取出单元格中第一个“-”符号前的AE编号了，向下填充即可提取A列所有的AE编号。后续可以通过受试者ID和AE编号得到一个唯一值，然后使用VLOOKUP函数查找AE结束日期等其他信息。

2、提取单元格中第n个指定符号之后的内容，也可以使用函数进行数据提取：

=MID(提取单元格,FIND("指定符号",提取单元格)+1,LEN(提取单元格))，这是提取第一个指定符号后的内容，以此为基础嵌套FIND函数：=MID(提取单元格,FIND("指定符号",提取单元格,FIND("指定符号",提取单元格)+1)+1,LEN(提取单元格))，就变成提取第二个指定符号后的内容，以此类推。

例如：从不良事件编号的连接中仅提取AE开始日期，如图所示，开始日期是从第3个“-”符号之后的内容，因此在B2单元格在输入【=MID(A2,FIND("-",A2,FIND("-",A2,FIND("-",A2)+1)+1)+1,LEN(A2))】，A2为需要提取内容的单元格，“-”为指定的符号，如果A2单元格中查找不到第3个“-”字符，将默认返回 错误值“#VALUE!”，也可以使用IFERROR函数处理。这样就提取出单元格中第3个“-”符号后的AE开始日期，向下填充即可提取A列所有的AE开始日期。

3、提取引号和括号符号内数据（如“”、（）、或[]等），除了通过“（”和“）”进行两次分列，也可以用函数进行提取：

=MID(提取单元格,FIND("左符号",提取单元格)+1,FIND("右符号",提取单元格)-FIND("左符号",提取单元格)-1)

例如：访视名称有时会设置link访视日期，如下图示，“筛选期 (2024-12-17)”，如需提取日期，输入【=MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1)】，A2为需要提取内容的单元格，就可以将访视对应的访视日期单独提取出来，方便与本表单内其他日期进行核查，而且这种情况下相比于
VLOOKUP函数会更快速一些。

二、VLOOKUP查询

1、VLOOKUP函数是数据清理过程中一个非常常用的查找和引用函数，通过一个共同值进行表单内或跨表单一对一匹配：

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

lookup_value为查找值（通过这个共同值进行查找，单元格格式需保持一致），table_array为包含数据的表格范围，查找值必须位于该范围的第一列，col_index_num为返回值所在的列号，从table_array的第一列开始计数，range_lookup：可选参数，用于指定查找方式：0/FALSE为精确匹配，1/TRUE/省略为近似匹配，数据清理一般使用精确匹配。

当查找值中包含特殊符号（如“筛选期D-3~D-1”中的“~”）时，会导致无法查询结果，这时需要将特殊符号替换掉。查询不到结果时默认显示错误值“#N/A”。

例如：通过样本编号查找随机表单中对应的随机号，如下图。在H2输入公式【=VLOOKUP(G2,B:D,3,0)】，“G2”为查找值，且包含在D列中，“B:D”为查找范围，且B列位于该范围的第一列，“3”即D列位于B列后第3列，“0”为精确匹配，通过该公式即可查找到G2在B列中对应的D列值。

2、VLOOKUP函数的局限在于查找值必须为查找范围的第一列，当我们需要逆向查询时，可以嵌套CHOOSE或IF函数调换查找区域中列的位置，帮助重新构建数据查找范围“table_array”：

=VLOOKUP(查找值,CHOOSE({数组序号},数组1,数组2),2,0)

“数组1”和“数组2”为查找的数据范围（不包含列标题），“数组序号”可以理解为数组1和数组2在CHOOSE重新构建的范围中的列序号，应与原来的位置相反。如下图所示：当“随机号”列在“样本编号”列之后，要通过“随机号”查找“样本编号”，输入公式【=VLOOKUP(M2,CHOOSE({1,2},$J$2:$J$1000,H2:H1000),2,0)】,M2为查找值，$J$2:$J$1000为数组1， J列未进行排序时，可以使用绝对引用；H2:H1000为数组2；数组{1,2}将J列放在H列之前，实现位置调换，反之亦可：CHOOSE({2,1},H2:H1000,$J$2:$J$1000)，将H列放在J列之后，然后VLOOKUP函数就可以在这个重建的范围中进行数据查询了。

3、由于当table_array第一列存在重复值时，查找结果只能返回第一个匹配到的结果，这可能导致数据不准确，因此需要通过CHOOSE函数构建一个多条件组合的数据范围进行查询：

=VLOOKUP(value1&value2,CHOOSE({数组序号},数组1&数组2,数组3),2,0)

“value1&value2”是查找值，将多个条件连接在一起，生成一个字符串；“数组1&数组2,数组3”为查找范围（不包含列标题），数组1&数组2将查找值对应的多个条件连接在一起，“数组3”是查找范围的第二个参数，表示要返回的结果列，“数组序号”可以理解为“数组1&数组2”和“数组3”在CHOOSE构建的范围中的列序号。

如下图所示：统一受试者有多个访视名称及访视日期，要想匹配访视日期，需要通过“受试者编号&访视编号”生成一个唯一值，才能进行匹配。输入【=VLOOKUP(F2&G2,CHOOSE({1,2},$A$2:$A$100&$B$2:$B$100,$D$2:$D$100),2,0)】，并使用Ctrl +Shift+Enter 确认。F2&G2为查找值“受试者编号”连接“访视编号”；$A$2:$A$100&$B$2:$B$100为在查找范围内从有数据的第1行至最后一行每一行的“受试者编号”连接“访视编号”，$D$2:$D$100为返回值所在的范围，F2&G2未排序，所以使用绝对引用；{1,2}为在CHOOSE构建的范围内，A&B为第1列，D为第2列，所以在这个数据范围中，VLOOKUP的col_index_num值即为2。

4、当table_array第一列存在重复值，且需要把所有的值都匹配出来，这时候需要先处理一下查找的数据范围，在查找范围后增加辅助列，并使用VLOOKUP函数嵌套IFERROR和连接符查找第一列的所有对应值，完成后可以正常使用VLOOKUP进行匹配：

=IFERROR(VLOOKUP(查找值,查找范围,列号,查找方式),"")&"自定义连接符号"&返回值

与前面所述的单纯使用VLOOKUP进行一对一匹配稍有不同的是，这里查找范围要包括查找值所在列、返回值所在列和辅助列中查找值的下一行至所有数据后第一行空行，辅助列的范围要固定绝对引用；列号为辅助列在查找范围中的第几列而不是返回值所在列号。

如下图所示，A列有重复值，我们需要查找E列每个“样本编号”对应的所有B列中的“病理医师编号”，首先在B列后增加辅助列C，在C2使用函数【=IFERROR(VLOOKUP(A2,A3:$C$1052,3,0),"")&" "&B2】，VLOOKUP(A2,A3:$C$1052,3,0)在A3:$C$1052范围中查找A2值，查找范围：查找值A列至辅助列C列、从查找值A2的下一行至所有数据后第一空行；IFERROR(VLOOKUP(...),"")将未找到的值返回空字符串；&" "&B2将查找结果与B2用空格（或其他自定义符号）连接，向下填充所有单元格后会完成自下而上的数据累积，也就是在A列中每个样本编号第一行对应C列中查询到所有B列数据，后续就可以在F列中使用VLOOKUP查询E列对应的所有B列值了。

三、通过一个共同值进行表单内或跨表单的一对多匹配，除了用上述VLOOKUP嵌套IFERROR和&，还可以使用一个较为复杂的嵌套函数实现一步操作：

=MID(SUBSTITUTE(PHONETIC(OFFSET(基准单元格,MATCH(查找值,查找范围,查找方式),0,COUNTIF(条件区域,查找值),区域宽度)),查找值,","),2,最大字符长度)

仍用上述举例，A列有重复值，要查找E列每个“样本编号”对应的所有B列中的“病理医师编号”，首先对A、B列区域和E列进行排序，然后在F2输入函数【=MID(SUBSTITUTE(PHONETIC(OFFSET(A1,MATCH(E2,A2:A99,),0,COUNTIF(A:A,E2),2)),E2,","),2,99)】，就可以直接在F2中提取所有匹配的B列值了。在这个函数中，MATCH(E2, A2:A99, 0) 表示在A2:A99范围中查找E2的值，并定位到第一个匹配项；OFFSET(A1,MATCH(E2,A2:A99,),0,COUNTIF(A:A,E2),2)表示从A1单元格开始偏移（偏移行数为MATCH查找到的第一个匹配项，偏移列数为0即不偏移列），并提取一个包含E2相关数据的区域（区域高度为COUNTIF结果，即E2在A列中出现的次数，区域宽度为2列）；PHONETIC将OFFSET提取的区域中的所有文本连接成一个字符串；SUBSTITUTE(PHONETIC(...), E2, ",")将PHONETIC生成的字符串中所有的E2文本替换为逗号“，”（或其他自定义符号）；最后MID(SUBSTITUTE(...), 2, 99) 在SUBSTITUTE替换后的字符串中，从第2个字符开始提取最多99个字符（提取的最大字符长度可以根据实际的数据情况自定义）。

四、当遇到有条件的求和，可以使用SUMIF和SUMIFS函数。

=SUMIF(range,criteria, sum_range)用于单条件求和；

=SUMIFS(range,criteria, sum_range sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)用于多条件求和；

range为条件区域，用于条件判断的单元格区域，criteria为求和条件，sum_range为求和区域。

 例如：计算各受试者所有访视给药的总剂量：用函数【=SUMIF(A:A,H2, D:D)】,如下图所示：

如果要计算受试者每个访视的给药总剂量，需要用函数【=SUMIFS(A:A,H2, D:D)】,如下图所示：

以上给大家拆解了一些DM数据核查的Excel函数技巧，尝试把函数“叠Buff”，组合出你的专属工具库，混乱难清的数据死角，也能被精准“照亮”。干净、合规的数据，也是整个临床试验的基础。