Excelで縦に並んだ複数行のデータを一行にまとめたい場面は、名簿の整形、アンケート回答の集約、売上明細の報告書作成などでよくあります。
同じ担当者や商品名が繰り返される表を横並びに変換できれば、一覧表が読みやすくなり、印刷や別システムへの貼り付けも進めやすくなります。
Microsoft 365またはExcel 2021以降では、TOCOL、TOROW、FILTER、TRANSPOSEなどの動的配列関数を組み合わせる方法が便利です。
古いExcelを使っている場合も、INDEX関数やTEXTJOIN関数、コピーと形式を選択して貼り付けを使えば、目的に近い形へ整えられます。
縦方向の複数行を一行にする主な方法は、数式で自動変換する方法、値を転置して配置を変える方法、同じ条件のデータを抽出して横並びにする方法です。
この記事では、1行目に見出しがある表を例に、複数行を一行へまとめる関数と横並び変換の考え方を詳しく確認していきましょう。
エクセルで複数行を一行にまとめる関数
| A列 | B列 | C列 | D列 |
|---|---|---|---|
| 担当者 | 商品 | 数量 | 変換後の横並び |
| 田中 | りんご | 3 | りんご 3 みかん 5 |
| 田中 | みかん | 5 |
それではまず、縦方向に入力されたセル範囲を一行へ並べ替える基本的な関数について解説していきます。
TOROW関数による横一列への展開
Microsoft 365で最もわかりやすい方法は、TOROW関数を使って対象範囲を横方向へ展開する方法です。
たとえばA2からA6に商品名が縦に入力されている場合、空いているC2セルへ次の数式を入力します。
=TOROW(A2:A6,1)
第1引数のA2:A6は横並びにしたい元データの範囲です。
第2引数の1は空白セルを無視する指定であり、途中に空欄があっても詰めて表示したい場合に役立ちます。
数式を確定すると、C2を先頭としてC列、D列、E列のように結果が自動で広がります。
動的配列の結果が広がる先に値が入力されているとスピルエラーになるため、出力先の右側は空けておくことが重要です。
範囲内の値をそのまま一行に並べるだけなら、コピーや手作業でセルを移動する必要はありません。
元データを変更すると結果も自動更新されるため、日々追加される一覧表にも向いています。
【操作のポイント】TOROW関数の出力先は、右側に十分な空き列があるセルを選びます。
TOCOL関数とTRANSPOSE関数の使い分け
元データがすでに横方向にある場合や、途中の空欄を除外して縦横を入れ替えたい場合には、TOCOL関数とTRANSPOSE関数も候補になります。
TRANSPOSE関数は、縦横の位置関係を保ったまま表全体を転置する関数です。
たとえばA2:C4の3行3列の表を横と縦で入れ替える場合は、次の数式を使います。
=TRANSPOSE(A2:C4)
この数式ではA列の内容が1行目に移り、B列の内容が2行目に移るように、表の向きそのものが90度変わります。
一方、TOCOL関数は範囲の値を一列に並べる関数であり、TOROW関数とは出力方向が異なります。
横に並んだB2:F2の値を一列に戻してから加工したいときは、=TOCOL(B2:F2,1)が使えます。
表全体の行列を交換するならTRANSPOSE、複数セルを順番に一列または一行へ連結するならTOCOLまたはTOROWが適しています。
作業前には、最終的に必要なのが表の向きの変更なのか、値だけを一列に並べる処理なのかを分けて考えましょう。
【操作のポイント】TRANSPOSEは表の構造を維持し、TOROWは値を一続きの横並びへ整える関数です。
TEXTJOIN関数による一つのセルへの集約
複数行を横の複数セルへ展開するのではなく、一つのセル内に文章としてまとめたい場合はTEXTJOIN関数が便利です。
たとえばA2からA6の商品名を読点区切りでD2セルに表示する数式は次のとおりです。
=TEXTJOIN(“、”,TRUE,A2:A6)
最初の引数は項目間に入れる区切り文字であり、ここでは日本語の読点を指定しています。
2番目のTRUEは空白セルを無視する設定です。
3番目のA2:A6は連結するデータ範囲となります。
TEXTJOIN関数の結果は一つのセルに収まるため、商品一覧を備考欄へ表示したい場合や、メール本文用の文字列を作る場合に有効です。
セル内で改行したいときは、区切り文字をCHAR(10)にして、対象セルの折り返して全体を表示する設定を有効にします。
ただし、計算後に個別の項目を集計したい場合は、一つのセルへ文字列化する前の元表を残しておくと安心です。
【操作のポイント】一覧を一セルへ表示する用途ではTEXTJOIN、後から列ごとに使う用途ではTOROWを選びます。
条件別データを横並びに変換する数式
| A列 | B列 | C列 | D列以降 |
|---|---|---|---|
| 担当者 | 商品 | 数量 | 田中の商品一覧 |
| 田中 | りんご | 3 | りんご みかん |
| 佐藤 | ぶどう | 2 | |
| 田中 | みかん | 5 |
続いては、同じ担当者や同じ分類に属する複数行を抽出し、一行にまとめる数式を確認していきます。
FILTER関数とTOROW関数の組み合わせ
複数行の中から特定の条件に一致するデータだけを横並びにするなら、FILTER関数とTOROW関数を組み合わせます。
サンプル表でA列が担当者、B列が商品名であり、F2セルに担当者名の田中が入力されているとします。
G2セルへ次の数式を入力すると、田中の商品名だけが横方向へ並びます。
=TOROW(FILTER($B$2:$B$100,$A$2:$A$100=$F2),1)
FILTER関数は、B2:B100からA2:A100がF2と一致する行だけを取り出します。
その抽出結果は通常は縦方向に表示されますが、外側をTOROW関数で囲むことで横並びの結果になります。
抽出条件のセルであるF2を変更するだけで、担当者ごとの商品一覧を切り替えられる点が大きな利点です。
データ件数が増える予定なら、範囲を100行程度まで余裕を持って指定するか、元データをテーブル化すると管理しやすくなります。
該当するデータがない場合のエラーを避けたいときは、FILTER関数の3番目の引数に「該当なし」などの文字列を指定する方法があります。
【操作のポイント】FILTERで条件に合う行を抽出してから、TOROWで表示方向を横へ変えます。
UNIQUE関数による重複を除いた横並び
同じ商品名や同じ分類が何度も出現する表では、UNIQUE関数を加えることで重複しない一覧を作成できます。
田中が購入した商品を重複なしで横並びに表示する場合は、次のように記述します。
=TOROW(UNIQUE(FILTER($B$2:$B$100,$A$2:$A$100=$F2)),1)
最初にFILTER関数が田中の商品を抽出し、UNIQUE関数が同じ商品名を一つにまとめ、最後にTOROW関数が横方向へ展開します。
表示順を昇順に整えたいときは、UNIQUE関数の外側にSORT関数を追加します。
=TOROW(SORT(UNIQUE(FILTER($B$2:$B$100,$A$2:$A$100=$F2))),1)
抽出、重複削除、並べ替え、横並び表示を一つの数式で行えるため、報告用の集計表を自動化しやすくなります。
ただし、商品名の末尾に空白がある場合は別の値として扱われることがあります。
入力ゆれが多い表では、あらかじめTRIM関数で余分な空白を整えることも検討しましょう。
【操作のポイント】重複を取り除きたいときは、FILTERの結果にUNIQUE関数を重ねます。
複数列を一行へまとめる考え方
商品名だけでなく数量や単価も一行にまとめたい場合は、対象範囲を複数列にしてTOROW関数へ渡します。
たとえば、田中の商品のB列からC列を横並びにする場合、FILTER関数の配列にB2:C100を指定します。
=TOROW(FILTER($B$2:$C$100,$A$2:$A$100=$F2),1)
この結果は、りんご、3、みかん、5のように、抽出された行を上から順に一行へ並べる形になります。
商品名をすべて先に並べ、その後に数量を並べたい表では、列ごとに別のFILTER関数を用意する方法が理解しやすいでしょう。
見出しも自動表示したいときは、VSTACK関数やHSTACK関数を併用する方法があります。
必要な表示順を先に紙へ書き出してから、どの列をどの順番で抽出するか決めると、複雑な数式の作り直しを減らせます。
【操作のポイント】複数列を対象にすると、各行の値は左から右の順で一行へ展開されます。
データを横並びに変換する貼り付け操作
| 変換前 | A列 | 変換後 | B列 | C列 |
|---|---|---|---|---|
| 商品 | りんご | 商品 | りんご | みかん |
| 商品 | みかん |
続いては、数式を残さずに複数行のデータを横並びへ変換したい場合の貼り付け操作を確認していきます。
形式を選択して貼り付けの行列を入れ替える操作
一度だけ表の向きを変えたい場合は、コピー後に形式を選択して貼り付けを使う方法が手早く確実です。
まず、横並びにしたい元のセル範囲をドラッグで選択し、CtrlキーとCキーを同時に押してコピーします。
次に、変換結果を表示したい先頭セルを選択します。
右クリックメニューから形式を選択して貼り付けを開き、行列を入れ替えるにチェックを入れて実行します。
この操作は数式ではなく値や書式を配置し直すため、元データの更新を変換先へ自動反映したい用途には向きません。
【操作のポイント】更新が不要な完成表には、行列を入れ替える貼り付け操作が適しています。
コピー元と貼り付け先の空き領域
転置貼り付けでは、コピー元の行数が貼り付け先の列数になり、コピー元の列数が貼り付け先の行数になります。
たとえば縦5行横2列の範囲をコピーすると、貼り付け先には縦2行横5列の空き領域が必要です。
貼り付け先に既存データがあると、Excelは上書きに関する警告を出すことがあります。
作業前に出力範囲を確認し、必要なら新しいシートや表の右側に貼り付け先を用意しましょう。
結合セルを含む範囲は転置時にエラーや意図しない配置になる可能性があります。
結合を解除してから操作すると、セル単位で整った結果を得やすくなります。
【操作のポイント】貼り付け先は、コピー元の縦横を反転した大きさより広く確保します。
値だけを残す貼り付け後の整形
関数で作成した横並びの結果を、他の人へ渡す固定データにしたい場合は、値として貼り付けを行います。
まず、TOROW関数やFILTER関数で表示された範囲を選択してコピーします。
同じ場所または別の場所で右クリックし、貼り付けオプションの値を選択します。
これにより数式は消え、画面に表示されていた文字や数値だけが残ります。
数式を値に変換した後は、元データを修正しても変換結果は更新されません。
更新用の数式シートと、提出用に値だけを残したシートを分けると、誤操作を防ぎやすくなります。
集計の仕組みを残す必要があるブックでは、数式を消す前に別名保存や複製を行うことが大切です。
【操作のポイント】提出用には値貼り付け、継続更新用には数式を残すという使い分けが便利です。
古いエクセルで複数行を一行にまとめる方法
| A列 | B列 | C列 | D列 |
|---|---|---|---|
| 商品 | 変換後1 | 変換後2 | 変換後3 |
| りんご | りんご | みかん | ぶどう |
| みかん | |||
| ぶどう |
続いては、TOROW関数やFILTER関数が使えないExcelで、複数行を一行にまとめる方法を確認していきます。
INDEX関数とCOLUMN関数による横展開
Excel 2019以前でも、単純な縦一列を横一列にするならINDEX関数とCOLUMN関数の組み合わせで対応できます。
A2からA10に元データがあり、C2から右へ横並びにしたい場合は、C2セルに次の数式を入力します。
=INDEX($A$2:$A$10,COLUMN(A1))
COLUMN(A1)は1を返すため、最初はINDEX関数がA2:A10の1番目、つまりA2の値を表示します。
数式を右方向へコピーすると、COLUMN(B1)は2、COLUMN(C1)は3となり、A3、A4の値が順番に表示されます。
開始位置を固定するためのドル記号をA2:A10へ付けることが、オートフィルで範囲がずれるのを防ぐポイントです。
元データの件数より右へ数式をコピーすると、範囲外の番号になってエラーが出ることがあります。
必要に応じてIFERROR関数で囲み、エラーの代わりに空白を表示させると見た目が整います。
【操作のポイント】INDEXの範囲は絶対参照にし、COLUMN関数で右コピー時の連番を作ります。
INDEX関数で条件に一致する行を取り出す仕組み
古いExcelで担当者別のデータを横並びにする場合は、INDEX、SMALL、IF、COLUMN関数を組み合わせる方法があります。
ただし数式が長くなり、Excelのバージョンによっては配列数式として確定する必要があります。
実務では、補助列に連番を作ってからINDEX関数で参照する方法のほうが、修正や引き継ぎをしやすいでしょう。
たとえばD列に、A列の担当者がF2の名前と一致したときだけ連番を付ける補助列を設けます。
その連番を利用すれば、横並び先ではINDEX関数で1番目、2番目、3番目の一致データを順に取得できます。
複雑な配列数式を一つに詰め込むより、補助列で処理の途中経過を見えるようにすると、エラーの原因を確認しやすくなります。
【操作のポイント】旧版Excelでは、補助列を使って条件一致の順番を管理すると安全です。
手動転置との判断基準
元データが今後も更新されるなら、少し手間がかかっても数式を使う価値があります。
一方で、一度だけ提出する資料や数件の項目を整えるだけなら、転置貼り付けのほうが速い場合もあります。
関数を使う方法では、元データの内容が変わると横並び結果も変化します。
貼り付け操作では、変換した時点の値が固定されます。
定期更新する管理表は関数、単発の資料作成は転置貼り付けという判断が基本です。
ファイルを受け取る相手が古いExcelを使っている可能性がある場合は、最終版を値として保存しておくと表示崩れを避けられます。
【操作のポイント】更新頻度、利用者のExcelバージョン、提出形式を基準に方法を選びます。
横並び変換で発生しやすいエラーと注意点
| 表示 | 主な原因 | 確認する内容 |
|---|---|---|
| スピル | 出力先にデータがある | 右側のセルを空欄にする |
| 該当なし | 条件に一致する行がない | 検索条件と空白を確認する |
| 重複表示 | 元データに同じ項目がある | UNIQUE関数を検討する |
続いては、複数行を一行にまとめる際に表示されやすいエラーと、表を安全に扱うための注意点を確認していきます。
スピルエラーの確認
TOROW関数やFILTER関数を入力したときにスピルと表示される場合は、結果を展開する範囲に障害物があります。
数式を入れたセルを選択すると、Excelが本来広がる予定の範囲を枠線で示します。
その範囲内に文字、数値、空白ではない数式、結合セルなどがないか確認します。
不要な値を削除する前に、そのセルが別の計算や帳票で使われていないかを確認しましょう。
出力先をより右側の空いた列へ移すだけで解決することも少なくありません。
テーブルの内部では動的配列が広がりにくい場合があるため、表の外に数式を置く方法も有効です。
【操作のポイント】スピルエラーは、数式の誤りより出力先の占有セルを先に確認します。
空白セルと不要なゼロの扱い
元データに空白セルが含まれると、横並びの途中に空欄が現れることがあります。
TOROW関数の第2引数を1にすれば空白を無視できますが、空欄自体に意味がある表では注意が必要です。
たとえば商品名が空欄で数量だけある行を詰めてしまうと、商品と数量の対応関係が崩れる可能性があります。
複数列のデータを扱う場合は、行単位でFILTER関数を使って抽出し、行のまとまりを保つ設計が安心です。
空白を削る処理は便利ですが、項目同士の対応を維持できるかを必ず確認する必要があります。
【操作のポイント】単一列なら空白無視、複数列なら行の対応関係を優先して数式を設計します。
参照範囲とテーブル化の管理
元データに行が追加されると、A2:A100のように固定した参照範囲では新しい行を拾えないことがあります。
余裕のある範囲を指定する方法もありますが、表をExcelテーブルとして登録する方法はさらに管理しやすい選択肢です。
テーブル化すると、新しいデータを最終行の下へ入力した際に、数式で参照する範囲も自動的に広がります。
見出しを含む表を選択してCtrlキーとTキーを押すと、テーブルの作成画面を開けます。
データベースのように継続追加する一覧は、最初からテーブルとして管理すると数式の保守が楽になります。
ただし、テーブル名や列見出しを変更すると数式の参照表記も変わるため、共有ブックでは名称の変更ルールを決めておくとよいでしょう。
【操作のポイント】追加されるデータが多い表は、固定範囲よりテーブル参照が向いています。
まとめ エクセルでデータを横並びに変換し複数行を一行にまとめる関数
Excelで複数行を一行にまとめるなら、Microsoft 365やExcel 2021以降ではTOROW関数が基本になります。
単純に縦一列を横一列へ変換する場合は、=TOROW(A2:A6,1)のように入力すると、空白を除きながら横並びにできます。
条件に一致する複数行を一行へまとめる場合は、FILTER関数で対象を抽出し、TOROW関数で横方向へ展開します。
重複を除いた一覧が必要ならUNIQUE関数を、並び順も整えたいならSORT関数を追加する方法が効果的です。
一つのセルへ文字列としてまとめたい用途ではTEXTJOIN関数を使い、提出用に固定した表が必要なら行列を入れ替える貼り付け操作や値貼り付けを選びましょう。
古いExcelではINDEX関数とCOLUMN関数の組み合わせでも、縦のデータを右方向へ展開できます。
数式を使う場合はスピルエラー、出力先の空き領域、空白セルによるデータのずれを確認することが大切です。
更新される一覧表には関数を残し、完成した資料には値を残すという使い分けを行えば、横並び変換を効率よく活用できます。
