如何对 Excel 中的列进行排序以便每行只显示一个值？

Question

假设我有一个包含以下数据的电子表格：

 A     B     C     D
---------------------
 2     1     0     2
 3     2     1     4
 5     3     3     6
 7     5     6     8
11     8    10    10
13    13    15    12
17    21    21    14
19    34    28    16
23    55    36    18
29    89    45    20

我想对列进行排序，以便每行只包含一个值，如下所示：

 A     B     C     D
---------------------
             0
       1     1
 2     2           2
 3     3     3
                   4
 5     5
             6     6
 7
       8           8
            10    10
11
                  12
13    13
                  14
            15
                  16
17
                  18
19
                  20
      21    21
23
            28
29
      34
            36
            45
      55
      89

在 Excel 中有没有快速完成此操作的方法？

Answer 1

您的问题要求使用工作表函数，我认为这是不可能的，所以我提供了一个宏（VBa），它可以满足您的要求！

Option Explicit
Sub DoThis()

Dim row As Integer
row = 1

Dim col As Integer
col = 65

    Do While (Range("A" & row).Value <> "")

        Do While (Range(Chr(col) & row).Value <> "")

        Dim currentValue As Integer
        currentValue = Range(Chr(col) & row).Value

        Range(Chr(col + 7) & currentValue + 1).Value = currentValue

        col = col + 1
        Loop

        row = row + 1
        col = 65

    Loop        

End Sub

我使用的 Excel 表（由于时间限制，我只使用了前 4 行或您的示例）

在我运行它之后

正如您所看到的，我选择将新表保存在不同的地方，因此它是非破坏性的。

Answer 2

可以使用 Power Query 插件解决此问题。需要几个步骤才能实现，还需要使用 Power Query 语言 (M) 编写一些代码来调用 Table.Pivot 函数（该函数未在 Power Query UI 中显示）。但这是 1 行代码（其余代码是通过在 Power Query UI 中单击来构建的），而 VB 代码大约有 20 行。

我已经构建了一个原型，您可以查看或下载它 - 我的 One Drive 中的“Power Query 演示 - 按值排序和展开行.xlsx”：

https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398

基本上，我的技巧是将列反转为行，然后复制值（代表每个单元格）。然后我使用 Table.Pivot 函数将数据返回到 A、B、C、D 列。然后我添加了分组依据并使用“值的复制”列进行排序以获得最终结果。

Table.Pivot 的文档在这里：

http://office.microsoft.com/en-au/excel-help/table-pivot-HA104111995.aspx?CTT=5&origin=HA104122363

使用 Table.Pivot 的另一个示例如下：

http://cwebbbi.wordpress.com/2013/11/25/pivoting-data-in-power-query/