Как " unpivot "или" reverse pivot " в Excel?

Если ваши данные не являются сводной таблицей Excel, а просто данными, вы можете "отменить" ее с помощью простого кода VBA. Код зависит от двух именованных диапазонов: исходного и целевого. Источник-это данные, которые вы хотите отменить (за исключением заголовков столбцов / строк, например, NY-RI в образце), а цель-первая ячейка, в которую вы хотите поместить свой результат.

Sub unPivot()
Dim oTarget As Range
Dim oSource As Range
Dim oCell As Range

Set oSource = Names("Source").RefersToRange
Set oTarget = Names("Target").RefersToRange

For Each oCell In oSource
    If oCell.Value <> "" Then
        oTarget.Activate
      ' get the column header
        oTarget.Value = oCell.Offset(-(oCell.Row - oSource.Row + 1), 0).Text 
      ' get the row header
         oTarget.Offset(0, 1).Value = oCell.Offset(0, _
           -(oCell.Column - oSource.Column + 1)).Text 
      ' get the value
        oTarget.Offset(0, 2).Value = oCell.Text 
      ' move the target pointer to the next row
        Set oTarget = oTarget.Offset(1, 0) 
    End If
Next
Beep
End Sub

Я построил надстройку, которая позволит вам сделать это, и что делает его легко адаптироваться к различным ситуациям. Проверьте это здесь: http://tduhameau.wordpress.com/2012/09/24/the-unpivot-add-in/

лучшее, что я придумал до сих пор это:

Id   LocNum  Loc
---------------------------------
1    1       =INDEX(Data,A6,B6)
1    2       =INDEX(Data,A7,B7)
1    3       =INDEX(Data,A8,B8)
1    4       =INDEX(Data,A9,B9)
2    1       =INDEX(Data,A10,B10)
2    2       =INDEX(Data,A11,B11)
2    3       =INDEX(Data,A12,B12)
2    4       =INDEX(Data,A13,B13)

это работает, но я должен генерировать идентификаторы и LocNum вручную. Если есть более автоматизированное решение (помимо написания макроса), сообщите мне об этом в отдельном ответе.

есть довольно хорошее решение в excel 2010, просто нужно немного поиграть с сводной таблицей.

создайте сводную таблицу из ваших данных с помощью следующих параметров:

• нет промежуточных итогов, нет общих итогов
• макет отчета: табличная форма, повторите все метки элементов
• добавьте все столбцы, которые вам нужны, держите столбцы, которые вы хотите преобразовать справа
• для каждого столбца, который вы хотите преобразовать: откройте настройки поля-на вкладке макет и печать: выберите "Показывать метки элементов в виде структуры" и установите оба флажка под ним
• скопируйте таблицу в отдельное место (только значения)
• если у вас есть пустые ячейки в исходных данных, то фильтр для пустых значений в крайнем правом столбце и удалить эти строки (не фильтровать в сводной таблице, как это не будет работать, как вам нужно!)

Если размеры ваших данных, как в образце, представленном в вашем вопросе, то следующий набор формул с использованием смещения должен дать вам необходимый результат:

предполагая, что

1 | НЬЮ-ЙОРК | ШТАТ КАЛИФОРНИЯ | КАЛИФОРНИЯ | ИЛ

2 / WA | OR | NH / RI

находятся в диапазоне A2: E3, затем введите

=смещение ($A$2, FLOOR ((ROW (A2) - строка ($A$2))/4,1),0)

в F2, скажем, и

=MOD (строка (A2) - строка ($A$2),4)+1

в G2, скажем, и

=OFFSET ($B$2, FLOOR (((ROW(B2)-строка ($B$2))/4,1),MOD (ROW (A2)-строка ($A$2),4))

в H2, скажем.

затем скопировать эту формулу вниз насколько это необходимо.

Это самое простое, чистое, встроенное решение формулы, о котором я могу думать.

вы, кажется, получили столбец" loc " (о чем свидетельствует ваш первый ответ), и теперь вам нужна помощь в получении двух других столбцов.

ваш первый вариант - просто ввести первые несколько (скажем, 12) строк в эти столбцы и перетащить вниз - я думаю, что Excel делает правильные вещи в этом случае (у меня нет excel на этом компьютере, чтобы проверить его наверняка).

Если это не работает, или если вы хотите что-то более программистское, попробуйте использовать функцию row (). Что-то вроде "=Пол(строка()/4)" для столбца ID и "=остат(строка(),4)+1" для столбца LocNum.

существует утилита параметрического преобразования VBA в unpivot или обратные сводные данные обратно в таблицу базы данных, см.

http://www.spreadsheet1.com/unpivot-data.html

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

Normalisieren von Pivot Tabellen

надеюсь, это поможет.

@DaveParillo ответ лучший ответ для одной таблицы вкладок. Я хотел бы добавить некоторые дополнительные здесь.

для нескольких столбцов до unpivot столбцов

этот метод не работает.

Youtube unpivot простые данные, такие как вопрос

Это видео на youtube, показывающее, как это сделать простым способом. Я пропустил часть о добавлении ярлыков и используется контекстное @devuxer в DaveParillo ответ.

https://www.youtube.com/watch?v=pUXJLzqlEPk