今回は、ExcelのVSTACK関数とHSTACK関数を使って、複数の表を1つに結合する方法を紹介します。
月ごとに分かれた売上表や、担当者ごとに作られた一覧表を1つにまとめたい場面は多くあります。コピーと貼り付けで集約する方法もありますが、元の表が更新されるたびに作業をやり直す必要があります。VSTACKとHSTACKを使えば、数式1つで複数の範囲をつなげた表を作れ、元データの変更も結果に反映されます。
対象環境は、Microsoft 365のExcel（Windows版・Mac版）とExcel for the webです。どちらの関数も、結果が複数のセルに広がる動的配列（スピル）に対応したExcelで使える関数です。古いバージョンの買い切り版Excelでは使えない場合があるため、関数を入力して「#NAME?」と表示されるときは、お使いのExcelが対応しているかを確認してください。
VSTACK関数とHSTACK関数の違い
2つの関数は、配列を「どの向きに積み重ねるか」が異なります。
- VSTACK：範囲を縦方向に順番につなげます。同じ列構成の表を上下に連結したいときに使います。
- HSTACK：範囲を横方向に順番につなげます。別々の列を左右に並べて1つの表にしたいときに使います。
名前の「V」はVertical（垂直）、「H」はHorizontal（水平）を表していると考えると覚えやすいでしょう。
基本の書き方
書式はどちらも同じ形です。
- =VSTACK(配列1, [配列2], …)
- =HSTACK(配列1, [配列2], …)
引数には、つなげたい範囲や配列をカンマで区切って順番に指定します。指定した順番がそのまま結果の並び順になります。
VSTACKで複数の表を縦に結合する
同じ列構成の表を上下につなげる
たとえば、4月の売上表がA2:C10、5月の売上表がE2:G8にあり、どちらも「日付・商品名・金額」の3列で作られているとします。空いているセルに次のように入力します。
=VSTACK(A2:C10, E2:G8)
Enterキーを押すと、4月のデータの下に5月のデータが続いた表が、入力したセルを起点に表示されます。結果の行数は各範囲の行数の合計、列数は各範囲の列数のうち最大のものになります。
見出し行を先頭に付ける
結合した表に見出しを付けたい場合は、最初の引数に見出し行の範囲を入れます。
=VSTACK(A1:C1, A2:C10, E2:G8)
見出しを1回だけ入れ、データ部分だけを続けて指定するのがポイントです。各表の見出しをそのまま含めてしまうと、結合結果の途中に見出し行が混ざってしまいます。
別のシートにある表もまとめられる
引数には別シートの範囲も指定できます。月ごとにシートを分けて管理している場合は、次のように書けます。
=VSTACK(‘4月’!A2:C10, ‘5月’!A2:C10, ‘6月’!A2:C10)
集計用のシートを1枚用意し、そこにVSTACKの数式を置いておくと、各月のシートを触らずに全体の一覧を確認できます。
HSTACKで表を横に結合する
HSTACKは、列を左右につなげたいときに使います。たとえば、A列に社員番号、D列に氏名、G列に所属があり、この3列を隣り合わせて並べた表を作りたいときは次のように入力します。
=HSTACK(A2:A20, D2:D20, G2:G20)
結果の列数は各範囲の列数の合計、行数は各範囲の行数のうち最大のものになります。元の表の列が離れていても、必要な列だけを好きな順番で並べ直せるのが便利な点です。
見出しと集計値を横に並べる使い方
HSTACKは、ほかの関数の結果と組み合わせて「項目名」と「値」を横に並べる用途にも向いています。たとえば、ある範囲の項目と、それぞれの計算結果を左右に並べるといった使い方です。表示用の小さな一覧を作るときに役立ちます。
サイズが合わないときの「#N/A」に対処する
VSTACKでは、列数が少ない範囲を結合すると、足りない部分が#N/Aで埋められます。HSTACKでも同様に、行数が少ない範囲の不足部分が#N/Aになります。
この表示を空白などに置き換えたい場合は、IFERROR関数で囲みます。
=IFERROR(VSTACK(A2:B10, D2:F10), “”)
こうすると、#N/Aの部分が空の文字列に置き換わります。ただし、IFERRORはほかのエラーもまとめて置き換えるため、元データに入っているエラーにも気付きにくくなります。元の表の確認が済んでから使うと安心です。
空白セルが「0」になることがある
元の範囲に空白セルが含まれていると、結合後の表ではそのセルが0として表示されることがあります。0を見えなくしたい場合は、表示形式を調整するか、元データの範囲を必要な行だけに絞るなどの方法で対応します。
ほかの関数と組み合わせるテクニック
VSTACKやHSTACKは、結果が配列として返されるため、ほかの動的配列関数と組み合わせると活用の幅が広がります。
- SORT：結合した結果を並べ替える。例：=SORT(VSTACK(A2:C10, E2:G8), 1)
- UNIQUE：結合した結果から重複を除く。例：=UNIQUE(VSTACK(A2:A10, C2:C10))
- FILTER：結合した結果から条件に合う行だけを抜き出す
- CHOOSECOLS：結合した表から必要な列だけを取り出す
たとえば、2つの部署の名簿から重複のない氏名一覧を作りたい場合は、VSTACKで縦につないでからUNIQUEで重複を取り除くと、1つの数式で一覧が完成します。
使うときの注意点とコツ
スピル範囲を空けておく
VSTACKとHSTACKの結果は、数式を入力したセルから下や右へ自動的に広がります。広がる先のセルに値が入っていると#SPILL!エラーになるため、結果を表示する場所の周囲は空けておきましょう。
テーブルを参照すると範囲の追加に強くなる
元データをテーブルに変換しておき、数式でテーブル名を参照すると、行を追加したときに参照範囲が自動で広がります。範囲を固定のセル番地で指定していると、データが増えるたびに数式を直す必要があるため、継続的に更新する表ではテーブルの利用が向いています。
結合結果は直接編集できない
スピルで表示された結果の一部を手で書き換えることはできません。結果を固定した表として使いたい場合は、結果をコピーして値として貼り付ける方法があります。ただし、値として貼り付けた表は元データの変更に連動しなくなる点に注意が必要です。
列の並びをそろえてから結合する
VSTACKは列の位置でデータをつなげるため、表ごとに列の順番が違うと、異なる項目が同じ列に並んでしまいます。結合する前に、各表の列の並びが同じになっているかを確認しておくと、あとの修正を減らせます。
まとめ
ExcelのVSTACK関数は範囲を縦に、HSTACK関数は範囲を横につなげて、1つの表として表示する関数です。コピーと貼り付けを繰り返さなくても、数式1つで複数の表を結合でき、元データの更新も結果に反映されます。
見出し行を最初の引数に入れる、サイズの違いで出る#N/AをIFERRORで処理する、SORTやUNIQUEと組み合わせて整えるといった工夫を取り入れると、集計用の表を手早く作れます。スピル範囲の確保やテーブルの活用も意識しながら、月別・担当者別に分かれたデータの集約に使ってみてください。