Excelで複数の列を素早く1列にまとめる方法は？

Excelでは、 テキストを列に分割 という機能は、1つの列のデータを指定した区切り文字で複数の列に分割するために設計されています。しかし、逆に複数の列の値を1つの列にまとめたい場合、例えば下記のようなケースではどうすればよいでしょうか？このようなケースは、データセットの統合や分析準備、またはレポートの形式変更作業などでよく発生します。残念ながら、Excelには直接列を縦にまとめる標準機能はありませんが、効率的にこの作業を行うための実用的な方法がいくつかあります。

数式で複数の列を1列にまとめる方法

VBAを使って複数の列を1列にまとめる方法

Transform Rangeで複数の列を1列にまとめる方法

Power Queryを使って複数の列を1列にまとめる方法

数式で複数の列を1列にまとめる方法

マクロやアドインを使用しない場合、INDEX関数を使った配列数式で複数の列を1つの列にまとめることができます。この方法は動的データや手動操作を避けたい時に最適です。利点は、元データが変更された際に数式が自動で結果を更新してくれる点ですが、範囲名を正確に設定し、参照範囲内でセルの挿入や削除を行わないよう注意が必要です。

1. スタックしたいデータ範囲（例：A1:C4）を選択し、 名前ボックス （数式バーの左側にあります）をクリックして、例えば MyDataなどと名前を入力し、 Enterキーを押します。これで範囲に名前がつき、参照が容易になります。

2.1列にまとめた結果を表示するための空白セル（通常はデータの下か別のシート）をクリックし、下記の数式を貼り付けます。

=INDEX(MyData,1+INT((ROW(A1)-1)/COLUMNS(MyData)),MOD(ROW(A1)-1+COLUMNS(MyData),COLUMNS(MyData))+1)

3. Enter キーを押して確定し、フィルハンドルを下方向にドラッグします。すると、 #REF! などのエラーが出るまでコピーされます。エラーセルは必要に応じて削除してください。

この数式では、MyDataは手順1で設定した範囲名を指します。COLUMNS(MyData)はデータの列数に自動で対応します。数式入力後にMyData内部で列の挿入や削除を行うと結果に影響するためご注意ください。データ内に空白セルがあった場合もその空白がまとめられますが、必要に応じて後でフィルター機能などで除外可能です。大きな範囲の場合、フィルハンドルのドラッグに時間がかかることがありますので、左隣の列にデータがある場合はフィルハンドルをダブルクリックすると素早くコピーできます。

Excel365やExcel2021以降のバージョンで動的配列に対応している場合は、下記の数式も使えます。

=TOCOL(MyData,1)

この数式は新しいExcelバージョン専用で、複数列をすぐに1列にまとめることができます。

VBAを使って複数列を1列にまとめる方法

頻繁に列をまとめたり、より高度な制御が必要な場合は、VBAマクロを利用してこの処理を数回のクリックで自動化できます。VBAは大規模データや繰り返しの作業を行う上級者に最適です。ただし、マクロの有効化はセキュリティリスクとなる場合があるため、マクロの出所に問題がないか確認し、実行前に必ずファイルを保存するなど慎重に操作しましょう。

1. Alt + F11を押して、Microsoft Visual Basic for Applicationsウィンドウを開きます。

2. VBAウィンドウで「挿入」＞「モジュール」をクリックします。新しいモジュールに下記のコードをコピー＆ペーストしてください。

VBA: 列をひとつにまとめる

Sub ConvertRangeToColumn()
'UpdatebyExtendoffice
Dim Range1 As Range, Range2 As Range, Rng As Range
Dim rowIndex As Integer
xTitleId = "KutoolsforExcel"
Set Range1 = Application.Selection
Set Range1 = Application.InputBox("Source Ranges:", xTitleId, Range1.Address, Type:=8)
Set Range2 = Application.InputBox("Convert to (single cell):", xTitleId, Type:=8)
rowIndex = 0
Application.ScreenUpdating = False
For Each Rng In Range1.Rows
    Rng.Copy
    Range2.Offset(rowIndex, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
    rowIndex = rowIndex + Rng.Columns.Count
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

3. マクロを実行するには、F5を押すか「実行」ボタンをクリックします。実行後にプロンプトが表示されるので、まとめたいデータ範囲を選択し、「OK」をクリックします。

4. 次に、結果を表示したいセルを選択するよう求められます。クリックして OK を押すと、選択したセルから1列にまとめられます。

エラーが発生した場合は、選択範囲を再度確認し、保護されているシートや結合セルが含まれていないかチェックしてください。空白をスキップしたり一部の列のみをまとめたい場合もコードをカスタマイズ可能です。データ損失を防ぐため、VBA実行の前に作業内容を必ず保存しましょう。

Transform Rangeで複数の列を1列にまとめる方法

数式やコードを書かずに、素早く誰でも扱える方法を望む場合は、Kutools for ExcelのTransform Range機能が便利です。このユーティリティは直感的なインターフェースでデータを素早く処理したい事務職や多次元データを定期的に扱う方に特におすすめです。Kutoolsを使えば数式のミスリスクも減り、大幅に時間を短縮できます。ただしアドインのインストールが必要です。

無料インストール 後、次の手順で進めてください。

1.1列にまとめたい列または範囲を選択します。次に、 Kutools > 範囲 > Transform Range をクリックしてダイアログを開きます。

2. Transform Range ダイアログボックスで、 範囲を単一列に変換 オプションを選択し、「 OK」をクリックします。次に、結果を表示したいセルを選択してください。

3. 最後に、 OKをクリックすると、選択した列がすぐに指定した場所に1列に変換されます。

この方法はシンプルでエラーが出にくいです。もし結果が期待通りでない場合も、Ctrl + Zで簡単に元に戻せますし、異なるデータセットには再度Transform Rangeを実行できます。データに結合セルがある場合は、予期せぬ結果を避けるために事前に結合を解除するのがおすすめです。

Power Queryで複数の列を1列にまとめる方法

Power Query（Excelの一部のバージョンでは「データの取得と変換」とも呼ばれます）は、特に大量データや定期的なデータ変換の自動化が必要な方におすすめの方法です。Power QueryはExcel2016以降に標準搭載されており、Excel2010/2013では無料アドインとして提供されています。高度なデータ整形にも対応し、シンプルなUIで複雑な処理も簡単に行えます。ただし、初めての方は多少の習得が必要です。

ソースとなる表をPower Queryに読み込みます。Excelで対象データを選択し、「データ」＞「テーブル／範囲から」でPower Queryエディタを開きます。

2. クエリプレビューで全ての列を選択します。Transformタブで「参照表」グループを見つけ、「転置」をクリックします。これで行と列が入れ替わります。

3. 再度、転置後の全ての列を選択します。Transformタブの「Any Column」グループにある「Unpivot Columns（列のピボット解除）」をクリックします。

4. 不要なら「Attribute」列を削除します。「Attribute」列のヘッダーを右クリックして「削除」を選びます。

5. 「閉じて読み込む」をクリックし、結果をExcelに新しいワークシートまたはテーブルとして出力します。元データが変化しても、いつでも更新や再適用が可能です。

Power Queryでは、この集約処理をクエリとして保存し、ソースデータの変化時にも迅速に再集計できます。

注意：この方法では、元の列名情報が保持されない場合があります。

まとめ：各方法にはそれぞれ強みがあります。数式は動的なデータに最適で、VBAは繰り返し作業の自動化に便利です。Kutools for Excelは直感的な操作で専門知識不要、Power Queryは高度で定形的な変換に適しています。処理後は、空白セルや結合セル、書式エラーが残っていないかを必ず確認しましょう。もしエラーや意図しない結果が出た場合は、範囲や手順を再チェックしてください。これらの方法を組み合わせることで、報告や分析のためにExcelデータを効率良く管理・変換できます。