☰

データベース管理のために Excel の 1 行を複数の行に分割する方法

tag-icon • tag-icon microsoft-excel database
データベース管理のために Excel の 1 行を複数の行に分割する方法

現在 39,000 行、27 列のデータ セットがあります。最初の列は ID 番号です。後続の列は選挙に対応しており、行 1 (セルB1- AA1) には選挙の日付が入っています。残りのセルには、投票方法に対応する文字 (投票しなかった場合は null) が入っています。このテーブルを再配置して、合計 3 つの列 (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

何か提案があれば、ぜひお願いします!

答え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

Sheet2の結果:

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。関数は 27 行が渡されるたびに 1 ずつ増加し、Sheet2 の行を Sheet1 の内容に正しくマッピングします。

このADDRESS関数は、数値入力とシート名からセルへの使用可能な参照を構築し、INDIRECT参照によって指されるコンテンツを取得します。

他の関数も同様の考え方に従います。つまり、補助関数を使用して、現在のセルの座標を Sheet1 の正しいセルにマッピングします。

セル名＃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"))

これは、前に使用した2つのシーケンスのミックスです。その理由は、IDに応じて内容が異なるためです（セル＃1と呼ばれる）と日付（セルからの部分）＃2入る）。

これらの関数を正しいセルに入力した後、下にドラッグするだけで結果が表示されますが、1 つ小さな問題があります。それは、無効な投票も表示されることです。

ただし、これらの結果をフィルター処理できます。ヘッダー (この場合は、ASheet2 の行) を選択し、[データ] > [フィルター] > [自動フィルター] (または使用している Excel バージョンで同等のもの) に移動します。投票方法の列のドロップダウンをクリックし、並べ替えをカスタマイズして、ゼロで構成される結果を除外します。

答え3

これには Power Query アドインを使用します。コードや複雑な関数は必要ありません。ゼロから始めても、このタスクを完了するのに 5 分もかからないでしょう。

既存の Excel テーブルからクエリを開始できます。その後、Unpivot コマンドを使用して、必要に応じてデータを変換します。

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

Unpivot の実装の優れた点は、追加された列 (新しい日付) をすべて受け入れ、クエリ定義を変更せずに処理できることです。

必要に応じて列の名前を変更し、結果を Excel テーブルに配信します。

関連情報