excel - 将许多列转换为一两列

Question

我有一个巨大的数据表，需要转换为以下示例：

选项1：

选项 2：

有没有我可以使用的公式？注意：没有查询/VBA，这里是菜鸟！

标签： excelexcel-formula

Answer 1

这是一个很好的脑筋急转弯，所以我试了一下：

• 首先，我们需要分解行和列，我们需要知道您首先使用了多少列并获取它们的列号。我选择了=AGGREGATE，它想：

=AGGREGATE(15,3,(($A$2:$C$5<>"")/($A$2:$C$5<>""))*COLUMN($A$2:$C$5),ROWS($A$2:A2))

• 假设我们现在已经将这些数字放在列L中。其次，我们需要使用 `=COUNTIFS() 函数知道每个项目的行号。一个公式看起来像：

=COUNTIFS(INDIRECT("L2:L"&ROW()),L2)+1

到目前为止，我们的结果如下图所示：

• 我们现在的第三步是使用获取行和列索引号的地址

=ADDRESS(K2,L2,4,1)

• 我们将在第四步中使用它来返回一个值，=INDIRECT()如下所示：

=INDIRECT(ADDRESS(K2,L2,4,1)

• 最后一步返回了我们的名字，但我们还需要月份值。我们可以=INDEX()在下面的公式中使用我们的列索引号的组合来获得它：

=INDEX($A$1:$C$5;1;L2)

• 现在我们将所有内容组合起来以列中的结果E:F（这可能看起来很困难）但是

塞尔E2： =INDEX($A$1:$C$5,1;AGGREGATE(15,3,(($A$2:$C$5<>"")/($A$2:$C$5<>""))*COLUMN($A$2:$C$5),ROWS($A$2:A2)))

塞尔F2： =INDIRECT(ADDRESS(COUNTIFS(INDIRECT("E2:E"&ROW()),E2)+1,AGGREGATE(15,3,(($A$2:$C$5<>"")/($A$2:$C$5<>""))*COLUMN($A$2:$C$5),ROWS($A$2:A2)),4,1))

• 这很好，但是如果您的一列最后有空单元格怎么办？好吧，它会给列表末尾的那个单元格一个错误。我们可以通过这两种方式来处理；一个简单的=IFERROR()函数来捕获错误。但是更有效的方法是通过查看我们是否需要进行计算来防止计算如此长的函数。我们可以使用一个小技巧来查看与值的数量相比正在使用的行数。如果小于或等于，我们需要进行计算。公式如下所示：

=IF(ROWS($D$2:D2)<=COUNTA($A$2:$C$5),...Do our calculation.....,"")

让我们将其包含在我们的最终公式中：

E2： =IF(ROWS($D$2:D2)<=COUNTA($A$2:$C$5),INDEX($A$1:$C$5,1;AGGREGATE(15,3,(($A$2:$C$5<>"")/($A$2:$C$5<>""))*COLUMN($A$2:$C$5),ROWS($A$2:A2))),"")

F2： =IF(ROWS($D$2:D2)<=COUNTA($A$2:$C$5),INDIRECT(ADDRESS(COUNTIFS(INDIRECT("E2:E"&ROW()),E2)+1,AGGREGATE(15,3,(($A$2:$C$5<>"")/($A$2:$C$5<>""))*COLUMN($A$2:$C$5),ROWS($A$2:A2)),4,1)),"")

• 向下拖动公式，我们的最终结果如下所示：