☰

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

tag-icon • tag-icon microsoft-excel database
Как разделить одну строку в Excel на несколько строк для управления базой данных

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

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

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

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

решение1

В VBA предполагается, что данные находятся на Листе 1, начиная с А1, и что Лист 2 существует:

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

решение2

Для полноты картины я сейчас покажу, как я это делаю.безприбегая к 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    |         |
 … |                 …

Лист2

   |  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). Щелкните раскрывающийся список в столбце метода голосования и настройте сортировку, чтобы исключить результаты, состоящие из нуля.

решение3

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

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

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

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

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

Связанный контент