【原】Excel134 | 纵队变横队：主单号站头排，子单号一号一列往后站

每天清晨六点，准时与您相约

问题来源

今天一朋友问这样一个问题：

左侧表格是纵向排列的主单号，能不能用公式生成右侧的结果，即：主单号与子单号成一行排列？

此题的难点在于：

1、主单号一样，若用查询函数，往往只能查询出第一个主单号对应的子单号；

2、纵列分布变横列。

韩老师给两个方法：

第一个，用COUNTIF做一辅助列，再用VLOOKUP查询；

第二个，直接用VLOOKUP函数套用一动态区域完成。

方法一：辅助列+VLOOKUP

第一步：加辅助列

在A列后插入一列，在B2单元格输入公式：

=A2&COUNTIF($A$2:A2,A2)

其中：$A$2:A2是一个随着公式向下填充而范围逐渐扩展的动态区域。

此辅助列的作用是在每个主单号后面加上一位数，该位数是主单号出现的次数，从而使每个主单号完全不一致。

如下图：

第二步：VLOOKUP实现

在F2单元格输入公式：

=IFERROR(VLOOKUP($E3&COLUMN(A$1),$B$2:$C$13,2,0),"")，

其中：

$E3&COLUMN(A$1)，是在E3单元格的主单号后加一个动态编号，此动态编号随着公式向右填充，变为：COLUMN(A$1)、COLUMN(B$1)、COLUMN(C$1)，即1、2、3……；

VLOOKUP($E3&COLUMN(A$1),$B$2:$C$13,2,0)

主单号+动态编号作为LOOKUP的查找值，在$B$2:$C$13中精确查找第2列的数值；

IFERROR(VLOOKUP($E3&COLUMN(A$1),$B$2:$C$13,2,0),"")

如果查找出现错误，返回空值。

结果如下：

方法二：VLOOKUP套用动态区域

公式

在E2单元格输入公式：

=IFERROR(VLOOKUP($D3&COLUMN(A$1),IF({1,0},$A$2:$A$13&COUNTIF(INDIRECT("A2:A"&ROW($2:$13)),$D3),$B$2:$B$13),2,0),"")

以<CTRL+SHIFT+ENTER>结束。

结果如下：

公式详解

其中：

IF({1,0},$A$2:$A$13&COUNTIF(INDIRECT("A2:A"&ROW($2:$13)),$D3),$B$2:$B$13)是实现一个新的查找区域：

INDIRECT("A2:A"&ROW($2:$13))是分别指向a2:a2,a2:a3,a2:a4,a2:a5,a2:a6,a2:a7,a2:a8,a2:a9,a2:a910,a2:a11,a2:a12,a2:a13的十二个数组：

因为要用到数组计算，所以公式结束的时候要用“CTRL+SHIFT+ENTER”。

素材下载

链接：http://pan.baidu.com/s/1ge5apX5

密码：vxbs

---