Как разбить одну строку в Excel на несколько строк для управления базой данных

Question

У меня есть набор данных, который в настоящее время составляет 39 000 строк и 27 столбцов. Первый столбец - это идентификационный номер. Последующие столбцы соответствуют выборам с указанием даты выборов в строке 1 (ячейки B1 - AA1). Остальные ячейки заполняются буквой, соответствующей методу голосования (или нулю, если не голосовали). Мне нужно переставить эту таблицу так, чтобы было всего три столбца: ID, дата и метод голосования. Например:

Текущая таблица:

ID          05/2005        11/2005        03/2006 (etc., for 27 total columns)
2345           P              V
3789                          A              V
4321           V              A              V
7890                          I

И мне нужно, чтобы это выглядело так:

ID           Date            Voting Method
2345         05/2005               P
2345         11/2005               V
3789         11/2005               A
3789         03/2006               V
4321         05/2005               V
4321         11/2005               A
4321         03/2006               V
7890         11/2005               I

Я думаю, что для этого потребуется скрипт VBA, и я попытался собрать воедино кусочки скрипта, которые я нашел в Интернете (потому что я никогда не изучал VBA), но я не могу заставить его работать совершенно правильно. Возможно, эта функция уже существует в Excel?

Вот сценарий, с которым я работаю до сих пор:

Sub NewLayout()
For i = 2 To Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
  For j = 0 To 26
    If Cells(i, 3 + j) <> vbNullString Then
      intCount = intCount + 1
      Cells(i, 1).Copy Destination:=Cells(intCount, 10)
      Cells(i, 2).Copy Destination:=Cells(intCount, 11)
                [I think this one is wrong. It needs to copy the column name,
                not the cell value, if there is a cell value.]
      Cells(i, 3 + j).Copy Destination:=Cells(intCount, 12)
    End If
    Next j
  Next i
End Sub

Если у кого-то есть какие-либо предложения, я буду признателен за это!

Answer 1

В VBA предполагается, что данные находятся в Sheet1, начиная с A1, и что Sheet2 существует:

Sub normalize()
    Dim wks1 As Worksheet, wks2 As Worksheet
    Dim iColCount As Integer, iRowCount As Integer
    Dim i As Integer, j As Integer, k As Integer

    Set wks1 = ActiveWorkbook.Sheets("Sheet1")
    Set wks2 = ActiveWorkbook.Sheets("Sheet2")
    iColCount = Application.WorksheetFunction.CountA(wks1.Range("1:1"))
    iRowCount = Application.WorksheetFunction.CountA(wks1.Range("A:A"))

    k = 1
    For i = 2 To iRowCount
        For j = 2 To iColCount
            If wks1.Cells(i, j) <> vbNullString Then
                wks1.Cells(i, 1).Copy Destination:=wks2.Cells(k, 1)
                wks1.Cells(1, j).Copy Destination:=wks2.Cells(k, 2)
                wks1.Cells(i, j).Copy Destination:=wks2.Cells(k, 3)
                k = k + 1
            End If
        Next j
    Next i
End Sub

Результаты в Листе 2:

2345    5/2005  P
2345    11/2005 V
3789    11/2005 A
3789    3/2006  V
4321    5/2005  V
4321    11/2005 A
4321    3/2006  V
7890    11/2005 I

Answer 2

Я бы использовал Power Query Add-In для этого. Не требует никакого кода или сложных функций. Начиная с нуля, это, вероятно, займет менее 5 минут, чтобы завершить эту задачу.

Вы можете запустить запрос из существующей таблицы Excel. Затем я бы использовал команду Unpivot для преобразования данных, как вам нужно.

http://office.microsoft.com/en-au/excel-help/unpivot-columns-HA104053356.aspx

Прелесть применения Unpivot в том, что он будет принимать любые добавленные столбцы (новые даты) и обрабатывать их без каких-либо изменений в определении запроса.

Я бы переименовал столбцы по мере необходимости и доставил результат в таблицу Excel.

Answer 3

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

Давайте предположим следующее начальное условие:

Лист1

   |  A |    B    |    C    |    D    | …
---+----+---------+---------+---------+---
 1 | ID | 05/2005 | 11/2005 | 03/2006 |
 2 |2345|    P    |    V    |         |
 3 |3789|         |    A    |    V    | …
 4 |4321|    V    |    A    |    V    |
 5 |7890|         |    I    |         |
 … |                 …

Sheet2

   |  A |   B  |       C       |
---+----+------+---------------+
 1 | ID | Date | Voting method |
 2 | #1 |  #2  |       #3      |

Ячейка с именем # 1 имеет следующую формулу:

=INDIRECT(ADDRESS(FLOOR((ROW(A2)-2)/27 + 2,0),1,,,"Sheet1"))

Эта формула выполняет сопоставление текущей ячейки с правильными ячейками в Sheet1. Это делается с помощью функции FLOOR . Функция будет увеличиваться на 1 при каждом прохождении 27 строк, правильно отображая строки в Sheet2 с содержимым в Sheet1.

Функция ADDRESS создает пригодную для использования ссылку на ячейку из числового ввода и имен листов, в то время как функция INDIRECT извлекает содержимое, указанное ссылкой.

Остальные другие функции следуют той же причине: вы используете вспомогательную функцию для сопоставления координат текущей ячейки с правильной ячейкой на Листе 1.

Для ячейки с именем # 2:

=INDIRECT(ADDRESS(1,MOD(ROW(A2)-2,27)+2,,,"Sheet1"))

В этом случае функция MOD последовательно чередуется между 0 и 26, а затем преобразуется в последовательность от 2 до 28 (другими словами, где ячейки с датами).

Наконец, для ячейки с именем # 3:

=INDIRECT(ADDRESS(FLOOR((ROW(A2)-2)/27 + 2,0),MOD(ROW(A2)-2,27)+2,,,"Sheet1"))

Это смесь двух последовательностей, использованных ранее. Причина в том, что содержимое меняется в зависимости от идентификатора (как такового, называется часть из ячейки # 1 ) и даты (куда входит часть из ячейки № 2 ).

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

Вы можете фильтровать эти результаты, хотя. Выберите заголовок (в данном случае строку A на Листе 2) и перейдите в «Данные»> «Фильтр»> «Автоматический фильтр» (или эквивалент в используемой версии Excel). Нажмите на раскрывающийся список в столбце метода голосования и персонализируйте сортировку, чтобы исключить результаты, состоящие из нуля.