ExcelのVSTACK関数/HSTACK関数とは？VBAを使わずに複数シートのデータを 1 シートで表示

Excel（エクセル）には400種類以上の関数があり、バージョンアップごとに新しい関数が登場していることをご存じですか？今回はMicrosoft365で利用可能なExcelのバージョンで、複数シートのデータを 1 シートで表示するための関数をご紹介します。

Excelで「範囲の結合」を行う場合、これまでにはVBAを使用するか、手作業で一つずつ行う必要がありました。しかし、今回ご紹介するVSTACK関数/HSTACK関数を使用することで、関数を利用して簡単に範囲を結合することができます。
これらの関数のメリットは以下の通りです。

• データそのものを結合せずに、結合した配列を作成できる
• 他の関数と組み合わせて使用することができる

ここから便利なVSTACK関数/HSTACK関数について、使用方法やポイントをご紹介します。

ExcelのVSTACK関数とは？

VSTACK関数とは、配列を垂直方向に順番に追加して、大きな配列を返す関数です。
VSTACK関数の基本構文は下記の通りです。

array1、array2は追加する配列（範囲）を表します。追加する配列の列数は同じである必要があります。VSTACK関数では、関数の計算結果がスピルされ複数行列にわたって範囲として返ってきます。

「スピル」については、こちらの記事をご覧ください。
スピルとは？Excelの常識が変わる革新的な機能！

VSTACK関数の使い方

ここから、VSTACK関数を使って下図のような顧客データを統合する基本的な方法をご紹介します。

まず、【G9】セルを起点に、セル範囲【A2:E7】と【G2:K7】のデータを縦方向に結合して表示してみましょう。

（1）【G9】セルに「=VSTACK」と入力

（2）セル範囲【A2:E7】を選択し、「,」を入力後セル範囲【G2:K7】を選択しデータを確定する
入力する数式は「=VSTACK(A2:E7,G2:K7)」となります。

VSTACK関数の戻り値はスピルを利用して、範囲が返されます。表示形式は反映されないため、入会日と会員期限にはシリアル値が表示されます。

ExcelのHSTACK関数とは？

配列を横直方向に順番に追加して、大きな配列を返す関数です。
HSTACK関数の基本構文は下記の通りです。

array1、array2は追加する配列（範囲）を表します。追加する配列の行数は同じである必要があります。HSTACK関数では、関数の計算結果がスピルされ複数行列にわたって範囲として返ってきます。
実際に入力をしてみましょう。

HSTACK関数の使い方

ここから、HSTACK関数関数を使って下図のような顧客データを統合する基本的な方法をご紹介します。

【G4】セルを起点にセル範囲【B1:D5】と【B7:D11】の顧客データを横方向に結合して表示します。
（1）【G4】セルに「=HSTACK」と入力

（2）セル範囲【B1:D5】を選択し、「,」を入力後、セル範囲【B7:D11】を選択しデータを確定
入力する数式は「=HSTACK(B1:D5,B7:D11)」になります。

先ほど同様戻り値はスピルを利用して、範囲が返されます。VSTACK関数同様、表示形式は反映されないため、入会日と会員期限にはシリアル値が表示されます。

↑　記事TOP

VSTACK関数の便利な使い方

ここではVSTACK関数の便利な使い方を5つ紹介します。

1.複数シートを串刺し参照する

VSTACK関数を利用すると、複数シートの表を結合することも可能です。
【A2】セルを起点に「VSTACK関数_応用」シートの右側の、A社・B社・C社の表を結合して表示します。
ここではVSTACK関数と串刺し参照と呼ばれる方法を使用します。

串刺し参照（3D参照）とは？

複数のシートにまたがる同じセル範囲に対して、関数を使って参照する方法です。
通常のセル参照では、同じシート内のセルを参照することができますが、串刺しを使うことで、複数のシートにまたがる同じセル範囲を参照することができます。

たとえば、3つのシートがあって、それぞれA1セルに値が入力されているとします。このとき、以下のようにSUM関数を使用して、3つのシートのA1セルの値を合計することができます。
=SUM(Sheet1:Sheet3!A1)

この式では、「:」を使って複数のシートを範囲指定し、該当のシートのA1セルの値を合計することができます。

串刺しを使用することで、複数のシートにまたがる同じセル範囲を効率的に参照することができます。
実際に入力してみましょう。

（1）【A2】セルに「=VSTACK」と入力

（2）A社のシートのセル範囲【A2:E25】を選択し、Shiftキーを押しながら、C社のシート見出しをクリックしてEnterキーで確定

すると、下図のように数式が「=VSTACK(A社:C社!A2:E25)」となります。
これは「A社～C社のシートのセル範囲A2からE25を結合して下さい」という意味になり、シートが何枚あっても最初と最後のシートを選択するだけで結合することができます。

しかしこのままでは、各シートのセルの範囲が違うため、データがない部分が「0」と表示されてしまいます。次に紹介するFILTER関数で「0」と表示されない方法を紹介します。

2.FILTER関数と組み合わせる

ここで使用するのがFILTER関数です。FILTER関数はその名の通り、フィルターした範囲を返すことができる関数です。
FILTER関数の基本構文は下記の通りです。

前述の「串刺し参照（3D参照）とは？」で入力した数式にFILTER関数を追加して、「0」の行は表示しないようにします。

（1）【A2】セルの数式の「=」の後に「FILTER」と入力
数式は「=FILTER(VSTACK(A社:C社!A2:E25)」となります。

（2）配列の引数は先ほど入力したVSTACK関数になるため、「 ) 」の後ろに「,」を入力

（3）「含む」にVSTACK関数「VSTACK(A社:C社!A2:A25)<>0」を入力し、数式を確定
これはA列が0でないという意味になるので、空白で「0」と表示されている行が非表示になります。

「FILTER関数」については、こちらの記事をご覧ください。
FILTER関数とは？複数条件の抽出に最適！作業効率に差をだす便利関数

3.テーブルを結合する

表がテーブルに変換されている場合は、VSTACK関数の引数にテーブル名を指定することで、FILTER関数を使用して0の行を非表示にする必要がありません。さらに、テーブルの範囲が増えれば、VSTACK関数の戻る範囲も自動で増やすことができます。
【A2】セルを起点にA社とB社のシートのテーブルを結合しましょう。

（1）【A2】セルに「=VSTACK」と入力し、A社の表のテーブル範囲を選択

（2）続けて「,」を入力し、B社の表のテーブルの範囲を選択し、Enterキーで数式を確定
数式は「=VSTACK(テーブル1,テーブル2)」となります。

A社のテーブルにデータを追加するとどうなるか確認してみましょう。
「A社」のシートの最終行に、適当なデータを入力します。

すると、入力したVSTACK関数の配列に追加したデータが反映されます。

↑　記事TOP

4.SORT関数と組み合わせる

VSTACK関数で結合した範囲はExcelの「並べ替え」の機能を使用することができません。
配列の範囲を並べ替えするには「SORT関数」を使用します。

SORT関数の基本構文は下記の通りです。

関数の引数には配列（並べ替えたい範囲）を指定し、引数が[ ]になっている場合は省略可能です。
SORT関数では、関数の計算結果がスピルされ複数行列にわたって範囲として返ってきます。

【A10】セルを起点にセル範囲【A2:E7】と【G2:K7】のデータを縦方向に結合し、会員期限の昇順で並べ替えたデータを表示しましょう。

（1）【A10】セルに「=VSTACK(」と入力

（2）セル範囲【A2:E7】を選択肢「,」を入力後、セル範囲【G2:K7】を選択し数式を一度確定

（3）会員期限の昇順に並べ替えるために、入力した数式の「=」の後ろに「SORT(」と入力

（4）SORT関数の引数の配列は、VSTACK関数で求めた範囲のため、「）」の後ろに「,」を入力し、並べ替えインデックスに「4」（4列目）、並べ替え順序に「1」（昇順）と入力し数式を確定

すると、縦方向に結合し、会員期限の昇順で並べ替えたデータが表示されます。

※C列、D列にはあらかじめ日付の表示形式を設定しています。
日付の表示形式については、こちらの記事をご覧ください。
表示形式のユーザー定義ってどう使うの？日付や金額、カンマをわかりやすく表示！データ活用の基本

「SORT関数」については、こちらの記事をご覧ください。
Excel（エクセル）のSORT関数でデータを並べ替え！使い方や注意点を解説

5.XLOOKUP関数と組合わせる

XLOOKUP関数は、指定された範囲または配列から検索して、最初に見つかった値に対応する項目を返す関数です。従来はVLOOKUP関数またはHLOOKUP関数を使用していましたが、ExcelのバージョンがMicrosoft365またはExcel2021であればXLOOKUP関数が利用できます。

XLOOKUP関数の数式は下記の通りです。

6つの引数のうち、検索値、検索範囲、戻り範囲の3つは必須、[見つからない場合]、[一致モード]、[検索モード]の3つは省略可能な引数です。

【A10】セルに入力された氏名を検索値として、付随する情報を表示します。
表の範囲が2つに分かれている場合、XLOOKUP関数の引数の検索範囲にVSTACK関数を使用することで、複数の範囲から検索値を探すことができます。

（1）【B10】セルに「=XLOOKUP」と入力

（2）【A10】セルをクリックし、コピーした際に検索値がA列から移動しないように、「$A10」とする
「$」の入力にはF4キーの利用が便利です。

（3）続けて検索範囲にVSTACK関数を利用して、「VSTACK(A2:A7,G2:G7)」と入力
検索範囲は「氏名」の範囲から移動しないので絶対参照「VSTACK($A$2:$A$7,$G$2:$G$7)」とする

（4）戻り範囲は性別の範囲のため、VSTACK関数を利用して、「VSTACK(B2:B7,H2:H7)」と入力し数式を確定

（5）オートフィルで数式をコピー
入会日と会員期限のセルには日付の表示形式があらかじめ設定されているため、オートフィルオプションから「書式なしコピー」を選択します。

（6）検索値の名前を左の表の名前に変更し、正しい値が表示されることを確認

「XLOOKUP関数」と「オートフィル」については、こちらの記事をご確認ください。
VLOOKUP関数はもうさようなら！「XLOOKUP関数」とは？おさえておきたい5つのポイントと使い方
Excel（エクセル）のオートフィルとは？連続データの自動入力やフラッシュフィルについても解説

最新関数は「Excel研修」で習得しよう

いかがでしょうか？これまでは手作業でコピーペーストしたりVBAを組んで実施していた、範囲を結合する作業が関数1つでできるようになりました。

今回はFILTER関数やXLOOKUP関数と組み合わせて使用しましたが、他のさまざまな関数と組み合わせて使用することができるので、汎用性も高く大変便利な関数です。コクーのEXCEL女子が開催する「Excel研修」では、Microsoft365で使用できる最新関数も取り扱っています。

実務経験者が講師のため、現場ですぐに使うことができる実践的なカリキュラムのご提供が可能です。
まずは概要だけ聞いてみたい、業務効率化したいけれど何をしたらいいのか分からなくて困っている、といったお悩みがありましたら、いつでもお気軽にご相談ください。

お役立ち情報

無料でサービス案内やノウハウ資料をダウンロードいただけます。

Excel研修のサービス案内資料

実際に現場で活躍するEXCEL女子が考えたカリキュラムをもとに、学習するExcel研修のご紹介です。

資料ダウンロード