辅助列法实现EXCEL一对多数据提取

本文转载自公众号:呼叫中心数据分析，作者:王厚东

一对多数据的提取已经陆续介绍了index small if法、textjoin函数法、DAX函数法等多种方法，但无奈第一种太难，第二种需要较高的EXCEL版本，第三种需要略懂Power Pivot的DAX语法。今天介绍一种可能相对要容易一些的辅助列法。

还是上一篇文章中的示例数据，每个人有多个成绩，需要一对多提取：

在A2单元格输入如下的公式：=B2&countif($B$2:B2,B2)，后面的COUNTIF函数会依次输出每一个姓名出现的次数，并于B列的姓名相连接，生成可以唯一识别的辅助姓名列：

回车填充到底后，我们就得到了如下所示的辅助列：

在需要放置每个人的多次成绩的地方设定如下所示的数据布局。由于从辅助列中发现，每个人最多有三次成绩，因此我们为每个人准备了1-3个成绩序号。如果情况稍微复杂一些，每个人成绩的次数并不一致时，我们就需要取最大次数且附加iferror函数排错：

然后我们输入如下经典的vlookup提取函数。在这个函数中，我们把左列的姓名与上方的成绩序次相组合作为查询目标，以对应A列辅助列中的数据，然后查找C列的成绩。尤其需要注意的是左列姓名与上方成绩序次的锁定规则。对于姓名列我们需要锁定列确保往右填充不串列，而不锁行确保向下填充可换行；而对于上方的成绩次序则正相反，我们需要锁行而不锁列：

回车确认，并拖拽填充，我们就得到了每个人的三次考核成绩：

其实，面对如此有规律的数据，我们还有一种更简单的方法：替换法！

---