Как «отключить» или «обратный поворот» в Excel?

Вы можете сделать это с помощью сводной таблицы.

1. Создайте «Сводную таблицу множественных диапазонов консолидации». (Только в мастере сводных таблиц. Вызов с ALT+ D, Pв Excel 2007)
2. Выберите «Я создам свои собственные поля страницы».
3. Выберите ваши данные.
4. Двойной щелчок по общему итоговому значению - тот, что находится на пересечении Row Grand и Column Grand, полностью в правом нижнем углу сводной таблицы.

Вы должны увидеть новый лист, содержащий все данные в вашей сводной таблице, транспонированный так, как вы ищете.

Технологии 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/

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

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 вручную. Если есть более автоматизированное решение (помимо написания макроса), пожалуйста, дайте мне знать в отдельном ответе.

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

Создайте сводную таблицу из ваших данных с этими настройками:

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

If the dimensions of your data are as in the sample provided in your question, then the following set of formulae using OFFSET should give you your required result:

Assuming

1 | NY | CA | TX | IL

2 | WA | OR | NH | RI

are in the range A2:E3, then enter

=OFFSET($A$2,FLOOR((ROW(A2)-ROW($A$2))/4,1),0)

in F2, say, and

=MOD(ROW(A2)-ROW($A$2),4)+1

in G2, say, and

=OFFSET($B$2,FLOOR((ROW(B2)-ROW($B$2))/4,1),MOD(ROW(A2)-ROW($A$2),4))

in H2, say.

Then copy these formulae down as far as required.

This is the easiest, pure, built-in formula solution that I can think of.

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

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

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

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

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

Here is a nice tool to unpivot, normalize a Pivot Table.

Normalisieren von Pivot Tabellen

I hope it helps.

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

Для нескольких столбцов предварительно разверните столбцы

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

Youtube разворачивает простые данные как вопрос

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

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