Excelで横展開されたデータを縦に自動で変換する方法について解説します。特に、複数の資格情報を持つ社員のデータを簡単に縦にまとめる方法を紹介します。この方法を使えば、大量のデータを手間なく整形でき、作業効率を大幅に向上させることができます。
横展開データの問題点とその解決策
Excelで管理されるデータが横展開されている場合、例えば資格情報が1人分に対して複数列に渡る場合など、データの集計や分析が非常に難しくなります。特に、社員ごとに複数の資格があり、その情報を縦に並べる必要がある場合、手作業で変換するのは非常に時間がかかります。
こうした問題を解決するために、Excelの機能やマクロを活用して横展開されたデータを縦に自動で変換する方法を紹介します。
Excelの「Power Query」を使用して横展開を縦に変換する方法
Excelの「Power Query」機能を使用することで、複雑なデータを自動的に整形することができます。以下の手順で、横展開された資格データを縦に変換することができます。
1. 「データ」タブの「Power Query」を選択します。
2. 変換したいデータ範囲を選択し、「クエリの作成」をクリックします。
3. 「列の転置」オプションを選び、資格データを縦に並べます。
4. 必要に応じてデータを整形し、「閉じて読み込む」を選択します。
Excel関数を使って手動でデータを縦に変換する方法
Power Queryを使わず、関数を利用してデータを縦に変換する方法もあります。たとえば、`INDEX`関数や`IF`関数を活用することで、横展開データを縦に並べ替えることができます。
1. `INDEX`関数を使って、指定した行と列からデータを抽出します。
2. `IF`関数を使って、空のセルをスキップしながら資格情報を縦に並べます。
この方法は、データ量が少ない場合に有効ですが、大量のデータを扱う場合にはPower Queryを使用する方が効率的です。
VBAを使用した自動化によるデータ変換
ExcelのVBA（Visual Basic for Applications）を使って、横展開されたデータを縦に自動で変換するマクロを作成することもできます。以下は、簡単なVBAコードの例です。
“`vba
Sub ConvertDataToVertical()
Dim i As Long, j As Long
Dim lastRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastRow
For j = 3 To 12
If Cells(i, j).Value <> “” Then
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Cells(i, 1).Value
Cells(Rows.Count, 1).End(xlUp).Offset(0, 1).Value = Cells(i, 2).Value
Cells(Rows.Count, 1).End(xlUp).Offset(0, 2).Value = Cells(i, j).Value
End If
Next j
Next i
End Sub
“`
このVBAコードを実行すると、資格情報が縦に並べ替えられ、ID、名前、資格、部署ID、部署といった情報が正しく縦に並びます。
まとめ
横展開されたデータを縦に変換する方法には、ExcelのPower Query機能や関数、VBAを使った自動化があります。データ量や作業環境に応じて最適な方法を選択し、作業を効率化しましょう。特に、大量のデータを扱う場合は、Power QueryやVBAを活用することで、時間と手間を大幅に削減できます。
コメント