Excelで縦に並んだデータを、横並びに整理したいと思ったことはありませんか？
たとえば、
・都道府県と地番が縦にずらっと並んでいる
・同じ都道府県が何度も出てくる
・地域ごとに横並びで一覧にしたい
といったケースです。
このようなとき、
・ピボットテーブルを使おうとして挫折したり
・VBAは難しそうで手を出せなかったり
ということがあると思います。
実は、Excelの関数だけで縦データを横並びに変換することができます。
しかも、データが増えても自動で反映される方法です。
この記事では、関数だけを使って住所データを横並びに変換する方法を、
・旧Excelでも使える方法
・Excel365で使える最新の方法
の両方で解説します。
- 縦に並んだデータを横並びにする方法（旧Excel版）
- 縦に並んだデータを横並びにする方法（365版）
今回使うサンプルデータ（Excelで縦に並んだ住所データ）
まずは元データです。
今回は以下のようなデータを用意しました。
このデータを、次のように都道府県ごとに横並びにしていきます。
方法①【旧Excel対応】COUNTIF×VLOOKUP関数で縦データを横並びにする方法
まずは、Excel2010などの旧Excelでも使える定番の方法です。
COUNTIFとVLOOKUP関数で、横並びにしていきます。
１．COUNTIF関数で同じ都道府県ごとに連番をつける
地番の出現順を管理するために、補助列で連番を振ります。
=COUNTIF($C$2:C2,C2)COUNTIFの範囲先頭を固定することで、都道府県ごとに1,2,3…と番号を振ることができます。
この方法であれば、同じ都道府県が再登場しても、正しく連番を振ることができます。
２．都道府県&連番のキー列を作成する
VLOOKUP関数は検索値を一つしか受けとれないので、「都道府県&連番」の検索キーを作成する必要があります。
以下のように、都道府県と先ほど作成した連番を&で結合しましょう。
あまり列を増やしたくない、という場合は、以下のようにCOUNTIF関数に組み込んでもOKです。
３．VLOOKUP関数で連番ごとに地番を横方法に取得する
横並び用の表を用意し、先ほど先ほど作成した検索キーを使用して地番を取得します。
横並びに番号を振っておくと、VLOOKUPの検索キーが作りやすくなります。
=VLOOKUP($F2&G$1,$A:$D,4,0)
エラー値が気になる場合は、IFERROR関数で非表示にしましょう。
=IFERROR(VLOOKUP($F2&G$1,$A:$D,4,0),"")
この方法のメリットは、
・古いExcelでも使える
・ロジックが分かりやすい
という一方で、
・補助列が必要
・数式がやや長くなる
といった点には注意が必要です。
そこで、次は新しい関数でもっと簡単にできる方法を解説します。
方法②【Excel 365向け】FILTER関数で縦データを一瞬で横並びにする
次は、Excel365を使っている方向けの最短ルートです。
ここでは、主に
・UNIQUE関数
・FILTER関数
・TRANSPOSE関数
の3つを使用します。
１．UNIQUE関数で都道府県一覧を自動抽出する
UNIQUE関数は、指定した範囲から簡単に重複を除外してくれる便利な関数です。
この関数を使うことで、都道府県の一覧を自動的に作成することができます。
=UNIQUE(A1:A10)
UNIQUE関数については、以下の記事で詳しく解説しています。
こちらもぜひご覧ください。
２．FILTER関数 × TRANSPOSE関数で地番を横方向に並べる
先ほどと同じように、横並び用の表を用意し、以下計算式を入力すると…
あっという間に横並び表示にすることができました。
=TRANSPOSE(FILTER($B$1:$B$10,$A$1:$A$10=D2))
この数式がどういう仕組みかを解説すると、
FILTER関数で、D列とA列が合致する場合、合致した行のB列データを取得し、
（東京都で合致した場合、東京都に紐づいている地番をすべて取得する）
TRANSPOSE関数で行と列を入れ替える
ということをやっているため、該当する都道府県の地番を横並びにすることができるのです。
ちなみに以下の記事でFILTER関数を詳しく解説しています。
こちらもぜひご覧ください。
Excelのバージョン別｜どの横並び方法を選ぶべきか
基本的には新しい関数（FILTER関数など）が使える環境であれば、そちらをおすすめします。
ただお使いのExcelバージョンによっては、FILTER関数が使えないケースがあります。
その場合は、VLOOKUP×COUNTIF関数の組み合わせでも実行可能です。
| Excel環境 | おすすめ方法 |
|---|---|
| Excel 2010〜2019 | COUNTIF × VLOOKUP の組み合わせ |
| Excel 365 | FILTER × TRANSPOSE の組み合わせ |
お使いの環境に応じて、使い分けしてみてください。
Excelで縦→横変換するときの注意点
関数でデータを縦→横にすることに注意したいポイントを解説します。
注意点１：FILTER関数で表示される範囲にデータがないか確認する
FILTER関数は対象の配列を表示するため、配列を表示する範囲に何かしら別の値があると「#スピル!」エラーが発生します。
使用する場合は、表示範囲に何もデータがないことを確認しましょう。
注意点２：元データの並び順がそのまま反映される
FILTER関数では、元データの並び順で表示されます。
並び順を変えたい場合、元データの並び替えを行うか、SORT関数を組み合わせて変更しないといけません。
簡単にやりたい場合は、事前に元データの並び替えを行っておきましょう。
まとめ｜Excelの縦データは関数で横並びにできる
縦に並んだ住所データや一覧表でも、Excelの関数を使えばピボットテーブルやVBAに頼らず、横並びに整理することが可能です。
旧Excel環境では、COUNTIF関数で同じ値ごとに連番を振り、その連番をキーとしてVLOOKUP関数でデータを取得する方法が王道です。
少し手順は増えますが、仕組みを理解すれば多くの場面に応用できます。
一方、Excel 365を利用できる場合は、FILTER関数を使うことで補助列や複雑なキーを作らずに、縦データを一瞬で横並びにできます。
データの追加や変更にも自動で対応できるため、作業効率は大きく向上します。
重要なのは、自分のExcel環境に合った方法を選ぶことです。
VLOOKUPで工夫する方法を知っておけば旧環境でも対応できますし、365環境ではよりシンプルな関数を選ぶことで無駄な作業を減らせます。
本記事の内容を参考に、ぜひ日々のデータ整理や業務効率化に活用してみてください。
最後まで読んでいただき、ありがとうございました。
コメント