У меня есть данные, которые выглядят так:
Id | Loc1 | Loc2 | Loc3 | Loc4
---+------+------+------+-----
1 | NY | CA | TX | IL
2 | WA | OR | NH | RI
И я хочу преобразовать это в это:
Id | LocNum | Loc
---+--------+----
1 | 1 | NY
1 | 2 | CA
1 | 3 | TX
1 | 4 | IL
2 | 1 | WA
2 | 2 | OR
2 | 3 | NH
2 | 4 | RI
Какой самый простой способ сделать это в Excel 2007?
Вы можете сделать это с помощью сводной таблицы.
Вы должны увидеть новый лист, содержащий все данные в вашей сводной таблице, транспонированный так, как вы ищете.
Технологии Datapig предоставляют пошаговые инструкции , которые на самом деле являются более сложными, чем вам нужно - его пример транспонирует только часть набора данных и использует технику поворота в сочетании с TextToColumns. Но там много картинок.
Обратите внимание, что сводная таблица будет группировать данные. Если вы хотите, чтобы он был разгруппирован, единственный способ сделать это - скопировать сводную таблицу и "вставить специальные" в качестве значений. Затем вы можете заполнить пробелы с помощью такой техники: http://www.contextures.com/xlDataEntry02.html
Если ваши данные не сводная таблица Excel, а просто данные, вы можете захотеть «разворачивать» их с помощью некоторого простого кода VBA. Код зависит от двух именованных диапазонов, Source и Target. Источник - это данные, которые вы хотите отменить (исключая заголовки столбцов / строк, например, NY-RI в образце), а Target - первая ячейка, в которую вы хотите поместить свой результат.
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/
В Excel 2010 есть довольно хорошее решение, просто нужно немного поиграть с сводной таблицей.
Создайте сводную таблицу из ваших данных с этими настройками:
Лучшее, что я придумал, это:
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)
Это работает, но я должен генерировать Id и LocNum's вручную. Если есть более автоматизированное решение (помимо написания макроса), пожалуйста, дайте мне знать в отдельном ответе.
Если размеры ваших данных такие же, как в примере, приведенном в вашем вопросе, то следующий набор формул с использованием OFFSET должен дать вам требуемый результат:
Если предположить,
1 | Нью-Йорк | CA | TX | Иллинойс
2 | WA | ИЛИ | NH | Род-Айленд
находятся в диапазоне A2:E3, затем введите
= OFFSET($ A $ 2, этаж ((СТРОКА (А2)-строка ($ A $ 2))/4,1), 0)
в F2, скажем, и
= MOD(СТРОКА (А2)-строка ($ A $ 2), 4)+1
в G2, скажем, и
= OFFSET($ B $ 2, ПОЛ ((СТРОКА (В2)-строка ($ B $ 2))/4,1), MOD (СТРОКА (А2)-строка ($ A $ 2), 4))
в H2, скажем.
Затем скопируйте эти формулы, насколько это необходимо.
Это самое простое, чистое, встроенное решение для формул, которое я могу придумать.
Похоже, вы получили столбец "loc" (о чем свидетельствует ваш первый ответ), и теперь вам нужна помощь в получении двух других столбцов.
Ваш первый вариант - просто набрать первые несколько (скажем, 12) строк в эти столбцы и перетащить вниз - я думаю, что в этом случае Excel делает правильные вещи (у меня нет компьютера на этом компьютере, чтобы проверить это наверняка).
Если это не сработает, или если вы хотите что-то более программистское, попробуйте использовать функцию row(). Что-то вроде «= Floor(row()/4)» для столбца идентификатора и «= mod(row(), 4)+1» для столбца LocNum.
Вот хороший инструмент для разворота, нормализации сводной таблицы.
Normalisieren von Pivot Tabellen
Я надеюсь, что это помогает.
Существует утилита параметрического преобразования VBA для отмены или обратного поворота данных обратно в таблицу базы данных, см.
Ответ @DaveParillo - лучший ответ для таблицы с одной вкладкой. Я хотел бы добавить некоторые дополнения здесь.
Для нескольких столбцов предварительно разверните столбцы
Этот метод не работает.
Youtube разворачивает простые данные как вопрос
Это видео на YouTube, показывающее, как это сделать простым способом. Я пропустил часть о добавлении ярлыка и использовал ярлык @devuxer, который есть в ответе DaveParillo.
Это намного проще, чем это. Просто выберите опцию "Транспонировать" в «Специальная вставка ...», чтобы получить запрашиваемую транспозицию.