Как отсортировать столбцы в 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

решит эту проблему с помощью Power Query Add-In. Для этого требуется несколько шагов и немного кода на языке Power Query (M) для вызова таблицы.Функция Pivot (она не отображается в пользовательском интерфейсе Power Query). Но это 1 строка кода (остальная часть была построена с помощью щелчка мышью в интерфейсе Power Query) против примерно 20 строк кода VB.

Я создал прототип, который вы можете просмотреть или скачать - его демонстрация Power Query - сортировка и распределение строк по значению.xlsx »на моем One Drive:

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

По сути, моя техника заключалась в том, чтобы разворачивать столбцы в строки, а затем копировать значение (представляющее каждую ячейку). Затем я использовал таблицу.Сводная функция для возврата данных в столбцы A, B, C, D. Затем я добавил Group By и Sort, используя столбец "Copy of Value" для достижения окончательного результата.

Документация для табл.Пивот здесь:

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

Еще один пример использования таблицы.Пивот здесь:

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

Answer 2

Ваши вопросы требуют функции листа, я не думаю, что это возможно, поэтому я предлагаю макрос (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 строки или ваш пример из-за временных ограничений)

И после того, как я бегу это

Как видите, я решил сохранить новую таблицу в другом месте, чтобы она не была разрушительной.