Преобразование строки со столбцами данных в столбец с несколькими строками в Excel 2007

Если у вас есть пять столбцов «заголовок», введите эти формулы

H1: =OFFSET($A$1,INT((ROW()-1)/5)+1,0)
I1: =OFFSET($A$1,0,IF(MOD(ROW(),5)=0,5,MOD(ROW(),5)))
J1: =INDEX($A$1:$F$9,MATCH(H1,$A$1:$A$9,FALSE),MATCH(I1,$A$1:$F$1,FALSE))

Копировать H1:J?? и вставьте специальные значения сверху. Сортируйте по столбцу J и удалите все, что равно нулю. Если у вас есть законные нули в данных, то вам сначала нужно заменить пустые ячейки какой-то уникальной строкой, которую вы можете удалить позже.

Если у вас больше столбцов, замените «5» во всех приведенных выше формулах на любое число, которое у вас есть.

Мне кажется, что часть того, что вы пытаетесь сделать, - это «отменить поворот» сводной таблицы. Мне очень помог этот совет, когда мне приходилось выполнять аналогичные задачи: http://spreadsheetpage.com/index.php/tip/creating_a_database_table_from_a_summary_table/

Обратите внимание, что в Excel 2007 вы можете получить доступ к старому мастеру сводных таблиц Excel 2003, используя сочетания клавиш Alt+D, P .

В Excel есть функция транспонирования, которая может удовлетворить ваши потребности. Это довольно скрыто и немного неуклюже, но, вероятно, проще, чем копаться в VBA. Вот выдержка из справки Excel 2007:

Цитата Переключить (транспонировать) столбцы и строки Показать всеСкрыть все Если данные вводятся в столбцах или строках, но вместо этого вы хотите переупорядочить эти данные в строки или столбцы, вы можете быстро перенести данные из одного в другой.

Например, данные о региональных продажах, организованные в виде столбцов, отображаются в строках после транспонирования данных, как показано на следующем рисунке.

1. На рабочем листе выполните следующие действия. Чтобы переупорядочить данные из столбцов в строки, выберите ячейки в столбцах, содержащих данные. Чтобы переупорядочить данные из строк в столбцы, выберите ячейки в строках, содержащих данные. 2. На вкладке «Главная» в группе «Буфер обмена» нажмите «Копировать».

Сочетание клавиш Чтобы скопировать выбранные данные, вы также можете нажать CTRL+C.

Примечание. Для изменения порядка данных можно использовать только команду «Копировать». Для успешного завершения этой процедуры не используйте команду «Вырезать».

3. На рабочем листе выберите первую ячейку целевых строк или столбцов, в которые вы хотите переупорядочить скопированные данные. Примечание. Области копирования (область копирования: ячейки, которые вы копируете, когда хотите вставить данные в другое место. После копирования ячеек вокруг них появляется движущаяся рамка, указывающая, что они были скопированы.) и области вставки (область вставки: Место назначения для данных, которые были вырезаны или скопированы с помощью буфера обмена Office.) не может перекрываться. Убедитесь, что вы выбрали ячейку в области вставки, которая находится за пределами области, из которой вы скопировали данные.

4. На вкладке «Главная» в группе «Буфер обмена» щелкните стрелку под надписью «Вставить» и выберите «Транспонировать». 5.После того, как данные успешно транспонированы, вы можете удалить данные в области копирования. Совет Если транспонированные ячейки содержат формулы, формулы транспонируются, а ссылки на данные в транспонированных ячейках корректируются автоматически. Чтобы убедиться, что формулы по-прежнему правильно ссылаются на данные в нетранспонированных ячейках, используйте абсолютные ссылки в формулах перед их транспонированием.

Дополнительные сведения см. в разделе Переключение между относительными, абсолютными и смешанными ссылками.

Цитата

Давайте посмотрим на возможное решение в VBA. Я думаю, это действительно поможет. Вот несколько вещей, которые вы должны знать о моем коде.

• Вам нужно будет поместить этот код в модуль кода в VBA (то же самое место, куда идут макросы)
• Посмотрите, как я назвал листы: Исходный и Нормализованный. Вы либо захотите изменить имена своих листов, либо код
• Я проверяю значения со строковым полем NULL. Если ячейка пуста, вместо этого вы захотите проверить If IsEmpty(rngCurrent.Value) Then.

'

Sub NormalizeSheet()
Dim wsOriginal As Worksheet
Dim wsNormalized As Worksheet
Dim strKey As String
Dim clnHeader As Collection
Dim lngColumnCounter As Long
Dim lngRowCounterOriginal As Long
Dim lngRowCounterNormalized As Long
Dim rngCurrent As Range
Dim varColumn As Variant

Set wsOriginal = ThisWorkbook.Worksheets("Original")     'This is the name of your original worksheet'
Set wsNormalized = ThisWorkbook.Worksheets("Normalized") 'This is the name of the new worksheet'
Set clnHeader = New Collection

wsNormalized.Cells.ClearContents        'This deletes the contents of the destination worksheet'

lngColumnCounter = 2
lngRowCounterOriginal = 1
Set rngCurrent = wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter)

' We'll loop through just the headers to get a collection of header names'
Do Until IsEmpty(rngCurrent.Value)
    clnHeader.Add rngCurrent.Value, CStr(lngColumnCounter)
    lngColumnCounter = lngColumnCounter + 1
    Set rngCurrent = wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter)
Loop

'Here we'll reset our Row Counter and loop through the entire data set'
lngRowCounterOriginal = 2
lngRowCounterNormalized = 1
lngColumnCounter = 1

Do While Not IsEmpty(wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter))

    Set rngCurrent = wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter)
    strKey = rngCurrent.Value ' Get the key value from the current cell'
    lngColumnCounter = 2

    'This next loop parses the denormalized values for each row'
    Do While Not IsEmpty(wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter))
        Set rngCurrent = wsOriginal.Cells(lngRowCounterOriginal, lngColumnCounter)

        'We're going to check to see if the current value'
        'is equal to NULL. If it is, we won't add it to'
        'the Normalized Table.'
        If rngCurrent.Value = "NULL" Then
            'Skip it'
        Else
            'Add this item to the normalized sheet'
            wsNormalized.Range("A" & lngRowCounterNormalized).Value = strKey
            wsNormalized.Range("B" & lngRowCounterNormalized).Value = clnHeader(CStr(lngColumnCounter))
            wsNormalized.Range("C" & lngRowCounterNormalized).Value = rngCurrent.Value
            lngRowCounterNormalized = lngRowCounterNormalized + 1
        End If

        lngColumnCounter = lngColumnCounter + 1
    Loop
    lngRowCounterOriginal = lngRowCounterOriginal + 1
    lngColumnCounter = 1    'We reset the column counter here because we're on a new row'
Loop



End Sub

Я бы создал макрос VBA, который перебирает каждую строку и выводит данные на другую страницу. Это позволит вам создать сводную таблицу на новой странице после вывода данных.

Не уверен, насколько вы знакомы с VBA, но это можно довольно легко сделать, загрузив данные в массив (или коллекцию объектов, если вы действительно хотите сделать это правильно) и записав их обратно.

Вот ссылка на хороший документ VBA.

http://social.msdn.microsoft.com/Forums/en/isvvba/thread/d712dbdd-c876-4fe2-86d2-7d6323b4262c

Изменить

Обратите внимание, что это не должно быть полностью рабочим решением, а действительно общей структурой, которая поможет вам в правильном направлении.

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

Sub RowsToColumns ()
  Application.ScreenUpdating = False
  Dim srcWrkSheet As Worksheet
  Dim destWrkSheet As Worksheet
  Dim excelData as pExcelData
  Dim srcRowNumber As Long
  Dim srcRolNumber As Long
  Dim destRowNumber As Long
  Dim destColNumber As Long

  SET srcWrkSheet = Sheets("YourSourceWorkSheetName")
  SET destWrkSheet = Sheets("YourDestinationWorkSheetName")

  srcRowNumber = 1
  srcColNumber = 1
  destRowNumber = 1
  destColNumber = 1

  'Loop until blank row is encountered in column 1
  Do
    destWrkSheet.Cells(destRowNumber ,1).Value = "Header 1 " & srcWrkSheet.Cells(srcRowNumber,srcColNumber )
    destWrkSheet.Cells(destRowNumber ,1).Value = "Header 2 " & srcWrkSheet.Cells(srcRowNumber ,srcColNumber)

    srcRowNumber = srcRowNumber + 1
    srcColNumber = srcColNumber + 1
    destRowNumber = destRowNumber  + 1
  Loop Until srcWrkSheet .Cells(rowNumber, 1).value = ""

End Sub