ExcelのVSTACK関数の使い方をお探しですね。

広告

Excel 最新！VSTACK関数とHSTACK関数で複数シートのデータをまとめる方法

Excelで複数シートの売上表や部署別の名簿、月別の実績データを1つの表にまとめたいとき、これまではコピー＆貼り付けやVBA、Power Queryを使うのが一般的でした。

でも、Microsoft 365などの新しいExcelでは、**VSTACK関数とHSTACK関数**を使うことで、複数の範囲やシートのデータを関数だけで簡単に合体・連結できるようになったんです。

この記事では、VSTACK関数とHSTACK関数の基本的な使い方から、実務での使い分け、注意点まで、わかりやすく解説していきます。

1. VSTACK関数とHSTACK関数って何？縦・横に表を連結できる便利な関数

**VSTACK関数**は、複数の表やセル範囲を**縦方向に積み重ねて**、1つの大きな表として表示してくれる関数です。

たとえば、1月シート、2月シート、3月シートに同じ形式の売上データがある場合、それらを1つの一覧表にまとめたいときにとても便利です。

使い方はシンプルで、「**=VSTACK(配列1, 配列2, …)**」と書くだけ。

指定した範囲が上から順番に連結されます。

従来のように「下の空行を探して貼り付けて…」という手間がなくなり、しかも元データが変われば連結結果も自動で更新されるのが大きなメリットです。

一方、**HSTACK関数**は、複数の表やセル範囲を**横方向に連結**する関数です。

基本の書き方は「**=HSTACK(配列1, 配列2, …)**」で、指定した範囲が左から右へ並びます。

たとえば、社員番号と氏名の表に、別の場所にある部署情報や評価データを横に並べたい場合に使えます。

VSTACKが「行を増やして縦長の一覧を作る関数」だとすれば、HSTACKは「列を増やして横に情報を追加する関数」と考えるとわかりやすいですね。

スピルって何？

どちらの関数も、結果は「**スピル**」という仕組みで複数セルに自動展開されます。

スピルとは、1つのセルに入力した数式の結果が、隣接するセル範囲へ自動的に広がって表示されるExcelの機能です。

そのため、数式を入力するのは左上の1セルだけでOK。

ただし、展開先に既存データがあると「**#SPILL!**」というエラーが出てしまうので、結果を表示する範囲には何も入力されていない状態にしておく必要があります。

2. VSTACK関数で複数シート・複数範囲を1つの縦長表にまとめる方法

VSTACK関数の基本的な使い方は本当にシンプルです。

たとえば、同じシート内に「A2:E10」と「G2:K10」という2つの表があって、それらを縦に連結したい場合は、結果を表示したいセルに「**=VSTACK(A2:E10,G2:K10)**」と入力するだけです。

これだけで、A2:E10の下にG2:K10のデータが続く形で表示されます。

3つ以上の範囲を結合したい場合も「**=VSTACK(A2:E10,G2:K10,M2:Q10)**」のように、カンマ区切りで範囲を追加していけばOKです。

複数シートのデータをまとめる

複数シートのデータをまとめる場合も考え方は同じです。

たとえば、各シートに同じ形式の表があって、1月、2月、3月のデータを集計用シートにまとめるなら「**=VSTACK(‘1月’!A2:E100,’2月’!A2:E100,’3月’!A2:E100)**」のように指定します。

シート名に日本語や記号が含まれる場合は、Excelが自動でシート名をシングルクォーテーション（’）で囲んでくれることがあります。

手入力でも作れますが、実務では数式入力中に対象シートへ移動して範囲選択すると、参照ミスを減らせますよ。

同じ形式のシートが連続して並んでいる場合は、串刺し参照の考え方が使える場面もあります。

たとえば、A社、B社、C社という連続したシートの同じ範囲をまとめる場合、「**=VSTACK(A社:C社!A2:E100)**」のように指定できるケースがあります。

ただし、シートの並び順や対象範囲の形がそろっていないと意図しない結果になることがあるので、重要な集計では各シートを個別に指定したほうが安全です。

特に、途中に集計対象外のシートを挟んでいる場合は注意してください。

テーブルと組み合わせるとさらに便利

VSTACK関数を実務で使うなら、元データをExcelの「**テーブル**」に変換しておく方法もおすすめです。

通常のセル範囲で「A2:E100」のように指定すると、101行目以降にデータが増えた場合に範囲を修正する必要があります。

一方、テーブル名を使って「**=VSTACK(売上_1月,売上_2月,売上_3月)**」のように指定すれば、各テーブルに行が追加されたときも連結結果に自動反映されます。

毎月データが増える売上表や、部署別に更新される名簿では、テーブル化してからVSTACKでまとめると管理がかなり楽になりますよ。

3. HSTACK関数で横方向にデータを合体する方法と使いどころ

HSTACK関数は、複数の範囲を横に並べて1つの表にしたいときに使います。

たとえば、A2:C10に社員番号・氏名・部署があって、E2:G10に評価点・等級・備考がある場合、結果を表示したいセルに「**=HSTACK(A2:C10,E2:G10)**」と入力すれば、2つの範囲が横方向に連結されます。

VSTACKが「同じ列構成のデータを下に追加する」のに向いているのに対し、HSTACKは「同じ行数のデータに列情報を追加する」のに向いています。

元データを壊さず確認用の表を作れる

HSTACK関数が便利なのは、元データを直接加工せずに、必要な列だけを一時的に組み合わせられる点です。

たとえば、別々の場所にある顧客基本情報と購入履歴の一部を横に並べて確認したい場合、元表をコピーして壊すことなく、確認用の表を作れます。

また、CHOOSECOLS関数やTAKE関数などと組み合わせると、必要な列だけを選んで横に結合することもできます。

単に表を横に貼り付けるだけでなく、「分析用に見たい形を関数で作る」という使い方ができるのがHSTACKの強みです。

行の対応関係に注意

ただし、HSTACK関数は**行の対応関係がずれていると、誤った意味の表になる**点に注意が必要です。

たとえば、左側の表は社員番号順、右側の表は部署順で並んでいる場合、そのままHSTACKで横に結合すると、別人の評価データが同じ行に並んでしまう可能性があります。

HSTACKは行番号ベースで横に連結するため、キー項目を照合して結合する関数ではありません。

社員番号や商品コードを基準に正しく情報を取り出したい場合は、XLOOKUP関数などで照合してから結合するほうが安全です。

VSTACKとHSTACKを組み合わせる

VSTACKとHSTACKは組み合わせて使うこともできます。

たとえば、4つの小さな表を上下左右に並べて1つの表にしたい場合、「**=VSTACK(HSTACK(A2:B5,D2:E5),HSTACK(A8:B11,D8:E11))**」のように、先に横方向へ結合したものを縦方向に積み重ねます。

逆に、縦にまとめた結果同士をHSTACKで横に並べることも可能です。

複数の範囲をどういう形の表にしたいのかを先に決めてから、VSTACKとHSTACKの順番を組み立てると、複雑な表も整理しやすくなります。

4. エラー対策・便利な組み合わせ・Power Queryとの使い分け

VSTACK関数とHSTACK関数を使うときに最もよく出る疑問が、**列数や行数がそろっていない場合の扱い**です。

VSTACKでは、指定した範囲の列数が異なると、足りない列の部分に「**#N/A**」が表示されることがあります。

HSTACKでも、指定した範囲の行数が異なると、足りない行の部分に「#N/A」が表示されます。

これは関数が壊れているのではなく、Excelが「ここに対応する値がない」と示している状態です。

見た目を整えたい場合は、IFERROR関数やIFNA関数を組み合わせて空白にできます。

#N/Aエラーを空白にする方法

たとえば、VSTACKの不足部分に表示される「#N/A」を空白にしたい場合は「**=IFNA(VSTACK(A2:E10,G2:I10),””)**」のように書きます。

HSTACKでも同じ考え方で「**=IFNA(HSTACK(A2:C10,E2:F8),””)**」とすれば、対応する値がない部分を空白表示にできます。

ただし、元データの中に本来確認すべき「#N/A」エラーが含まれている場合、それも空白になってしまいます。

エラーを消すことだけを目的にせず、元データの不備を隠していないかを確認することが大切です。

他の関数と組み合わせてさらに便利に

連結した表をさらに使いやすくするには、他の動的配列関数と組み合わせるのが効果的です。

たとえば、空白行を除外したい場合はFILTER関数、連結後に並べ替えたい場合はSORT関数、重複を取り除きたい場合はUNIQUE関数が役立ちます。

代表的な組み合わせは次のとおりです。

– **空白行を除外する**：`=FILTER(VSTACK(範囲1,範囲2),VSTACK(キー列1,キー列2)<>“”)`
– **連結後に並べ替える**：`=SORT(VSTACK(範囲1,範囲2),並べ替え列番号,1)`
– **重複を除く**：`=UNIQUE(VSTACK(範囲1,範囲2))`

また、複数範囲を1つにまとめたうえで検索したい場合は、XLOOKUP関数との組み合わせも便利です。

たとえば、商品マスターが2つの表に分かれているとき、検索範囲と戻り範囲の両方にVSTACKを使えば、分かれた表をまたいで検索できます。

「**=XLOOKUP(検索値,VSTACK(商品コード範囲1,商品コード範囲2),VSTACK(商品名範囲1,商品名範囲2),”該当なし”)**」のような形です。

これにより、表を物理的に1つへ統合しなくても、検索用には1つのマスターのように扱えます。

Power Queryとの使い分け

一方で、VSTACK関数やHSTACK関数が常に最適とは限りません。

単純に同じブック内の表を結合して表示したいだけなら関数で十分ですが、次のような場合はPower Queryのほうが向いています。

– 複数ファイルやCSVを取り込みたい
– 列名を基準に結合したい
– 不要列の削除やデータ型の変換まで自動化したい

VSTACKとHSTACKは「軽く、すぐに、関数で表示を作る」用途に強く、Power Queryは「取り込みから加工、更新までを仕組み化する」用途に強いと考えると選びやすくなります。

対応環境の確認も忘れずに

最後に、VSTACK関数とHSTACK関数を使う際は、**対応しているExcel環境かどうか**も確認しておきましょう。

これらは比較的新しい動的配列関数のため、古い買い切り版Excelでは利用できない場合があります。

関数が認識されないときは、Microsoft 365やExcel for the webなど対応環境で開くか、Power Queryや従来のコピー結合で代替する必要があります。

対応環境であれば、複数シートや複数範囲のデータを1つの表に合体・連結する作業は、VBAを使わなくてもかなり効率化できますよ。

ぜひこの記事を参考に、VSTACK関数とHSTACK関数を使いこなしてみてください！

広告