Excel(エクセル)で複数シートに作成したデータを１つにまとめるときに、１つ１つ切り貼りしていては時間がかかります。
このような場合、データを統合するときに便利なのが、あたらに登場したVSTACK関数(ブイ・スタック)です。
今までは、データを統合するためには、「統合」機能を利用するかVBAでマクロを作成する必要がありましたが、関数でも行えるようになりました。
今回の記事では、VSTACK関数の基本的な使い方と、VSTACK関数を使った７つの事例を紹介します。
ExcelのVSTACK関数とは？
VSTACK関数とは、「異なる範囲にある複数のデータを統合して１つにまとめる関数」です。
VSTACK関数の読み方は「ブイ・スタック」です。
Vが垂直を意味する英語「Vertical」の頭文字で、「STACK」は大量、桁の大きい数を意味しています。
VSTACK関数では、以下のイメージのように、異なるセル範囲のデータを合体し、そのデータをさらに加工するときによく利用されます。
現時点(25年2月)で利用できるユーザーは、Microsoft365(サブスク)を使用しているユーザーと限定的です。
また、VSTACK関数を使うメリットは以下のようなものがあります。
VSTACK関数のメリット
・データの切り貼りが不要
・関数を挿入するセルは１つだけ！
・データが膨大でも問題なし
・他の関数と組み合わせて、統合したデータを並べ替えしたり、抽出することができる
この関数を使うと、スピル機能が自動で働いて、隣接する複数のセルに結果が表示されるという便利な関数です。
VSTACK関数の書式と引数
VSTACK関数の書式(構文)と引数は、以下のとおりです。
=VSTACK(配列1,配列2‥‥)
配列(array)は、統合したいセル範囲をそれぞれ指定します。
統合する配列の列数は必ず同じである必要があります。また、見出しの並び順も一致させておきましょう。
VSTACK関数の基本的な使い方
VSTACK関数の基本的な使い方を解説します。
２つのデータを用意して、手順１～３に沿って、VSTACK関数を使用してみてください。
手順１：VSTACK関数の挿入・第１引数を指定
まずは、空いているセルに、VSTACK関数を挿入します。
挿入したら、第１引数「配列１」を指定するため、１つ目のデータをドラッグして選択します。
見出し行も忘れずに選択してください。選択したら、次のデータを選択するため、「,」(カンマ)を入力してください。
=VSTACK(B2:E6,
手順２：VSTACK関数の第２引数を指定
次に、第２引数「配列２」を指定するため、２つ目のデータをドラッグして選択します。
今度は見出しは含めずにセル範囲を選択してください。見出しを含めると、統合したデータに２つの見出し行が挿入されてしまいます。
=VSTACK(B2:E6,G3:J7)
手順３：VSTACK関数を確定
「Enter」キーでVSTACK関数を確定します。
そうすると、引数「配列1」「配列２」で指定したデータが統合されます。
VSTACK関数はG9セルしか挿入していませんが、スピルが働いて、隣接する「G9:J18」のセル範囲にも統合した結果が表示されます。
スピル機能について詳しく知りたい方は、以下の記事を参考にしてください。
VSTACK関数でエラー「#N/A」が発生する原因
VSTACK関数でエラー「#N/A」が発生する場合があります。
そのときは、統合しようとしているデータの列数が異なることが原因です。
例えば、下の図でG9セルに「=VSTACK(B2:E6,G3:K7)」を挿入していますが、エラーが発生しています。
原因は、１つ目のデータは４列で構成されていますが、２つ目のデータは５列目のため、複数のセルに「#N/A」が表示されます。
VSTACK関数を使う時は、データの列数と見出しの並び順が一致していることを確認するよう注意してください。
【基本編】VSTACK関数の事例を紹介
VSTACK関数の基本的な使い方を３つの事例で紹介します。
元データのセル範囲の行数が増減する場合の対処方法など、役立つ情報を載せていますので実務に活用してください。
VSTACK関数/事例１：複数シートのデータを統合
複数のワークシートの表を１つにまとめる方法を紹介します。
今回は、行数の異なる「表1」「表2」「表3」シートのデータを１つにまとめます。
まずは、VSTACK関数を挿入して(図①)、「表１」シートをクリックし(図２)、対象のデータをドラッグで選択します。(図３)
=VSTACK(表１!B2:E6,
次に、「,」(カンマ)を入力してから、「表２」シートをクリックし(図４)、見出しを除く対象データをドラッグで選択します。(図5)
続いて、「,」を入力して、「表３」シートをクリックし(図6)、見出しを除く対象のデータをドラッグで選択します。(図7)
=VSTACK(表１!B2:E6,表２!B3:E7,表3!B3:E8)
VSTACK関数を確定すると、以下のように３つのシートのデータを統合することができます。
統合されたデータは、選択したワークシートの順番で上から表示されます。
並び順を変更したい場合は、選択するシートの順番を変えるか、下の見出しで紹介しているSORT関数と組み合わせた方法を利用してください。
VSTACK関数/事例２：シートの串刺し演算
複数のワークシートの表を１つにまとめる方法は、もう１つあります。
「串刺し演算」と呼ばれる方法をVSTACK関数で利用して、データを統合します。
手順１．VSTACK関数を挿入
見出し行を作成して、その左下のセルにVSTACK関数を挿入します。
手順２．ワークシートを選択
統合したいシートのうち、一番左のシートをクリックし(図3)、「Shift」キーを押しながら一番右のシートをクリックします。(図4)
シートの選択が終わったら、見出しを除くデータをドラッグで選択してください。(図5)
手順３．VSTACK関数を確定
VSTACK関数を確定すると、数式は以下のようになり、「表1」「表2」「表3」シートを串刺しして統合することができます。
=VSTACK(表1:表3!B3:E8)
ただし、表の元データのセル範囲がそれぞれ異なって空白の行が選択されると、上の図の19行目、20行目のように、「0」が表示されます。
この串刺しによって発生する「0」を非表示に方法を次の見出しで紹介します。
VSTACK関数/事例３：FILTER関数で空白行による「0」を表示しない
VSTACK関数で統合する元データのセル範囲が変動する場合は、選択するセル範囲を大きめに選択しておくことをおすすめします。
ただ、選択したセル範囲に空白セルが存在すると、上の実例で紹介したように、VSTACK関数の結果は「0」で返されてしまいす。
このセルがブランクであることが原因で発生する「0」の行を表示しない方法を紹介します。
利用するのは、データを抽出することができるFILTER関数と、上の見出しで紹介した串刺し演算です。
FILTER関数の書式は以下のとおりです。
＝FILTER( 配列,含む,[空の場合]）
第１引数『配列』：データを絞りたいセル範囲を指定
第２引数『含む』：抽出する条件を指定
第３引数『空の場合』：省略可能
手順１．FILTER関数の後にVSTACK関数を挿入
FILTER関数を挿入し、FILTER関数の第１引数「配列(セル範囲)」にVSTACK関数を入力します。
=FILTER(VSTACK(表1:表2!B3:E9),
手順２．FILTER関数の第２引数を指定
FILTER関数の第２引数「含む(=抽出条件)」を指定します。
B列の値が「0」のセルを除きたいので、「0以外のセル」を抽出条件にするため「VSTACK(表1:表2!B3:B9)<>0」と指定します。
=FILTER(VSTACK(表1:表2!B3:E9),VSTACK(表1:表2!B3:B9)<>0)
手順３．FILTER関数を確定
FILTER関数を確定すると、空白の行は統合されずに、VSTACK関数の結果を表示することができます。
このように、表の大きさは変化することが多いので、VSTACK関数はFILTER関数とセットで利用することをお勧めします。
今回紹介したFILTER関数の使い方を詳しく知りたい方は、以下の記事を参考にしてください。
【応用編】VSTACK関数の事例を紹介
次に、VSTACK関数の応用テクニックを事例で紹介します。
VSTACK関数/事例４：XLOOKUP関数と組み合わせて検索する
XLOOKUP関数とは、VLOOKUP関数が進化した検索用の新関数です。
表が複数に分かれている場合、それぞれの表からまとめて特定の値を検索したい場合は、XLOOKUP関数にVSTACK関数を組み合わせると便利です。
XLOOKUP関数の書式は下記の通りです。(省略可能な引数は除く)
＝XLOOKUP(検索値,検索範囲,戻り範囲）
第１引数『検索値』：検索する文字列を指定
第２引数『検索範囲』：どのセル範囲から探したいかを指定
第３引数『戻り範囲』：どのセル範囲を返したいかを指定
手順１．XLOOKUP関数を挿入
まずは、XLOOKUP関数を挿入し、第１引数「検索値」を指定します。セル番号は絶対参照($)を付けてください。
=XLOOKUP($B$11,
手順２．XLOOKUP関数の第2引数を指定
次に、XLOOKUP関数の第２引数「検索範囲」を指定します。VSTACK関数を挿入して、表1のB列と表２のG列を統合します。VSTACK関数のセル範囲は絶対参照で固定してください。
=XLOOKUP($B$11,VSTACK($B$3:$B$6,$G$3:$G$7),
手順３．XLOOKUP関数の第３引数を指定
次に、XLOOKUP関数の第３引数「戻り範囲」を指定します。２つ目のVSTACK関数を挿入して、表1のC列と表２のH列を統合します。
=XLOOKUP($B$11,VSTACK($B$3:$B$6,$G$3:$G$7),VSTACK(C3:C6,H3:H7))
手順４．XLOOKUP関数を確定
XLOOKUP関数を確定すると、表2の「佐藤さん」の算数の点数を検索することができました。
手順５．XLOOKUP関数をコピペ
右のセルにもXLOOKUP関数をコピペすれば、「国語」と「社会」の点数も検索することができます。
XLOOKUP関数の使い方を詳しく知りたい方は、以下の記事を参考にしてください。
VSTACK関数/事例５：SORT関数と組み合わせて並べ替えする
SORT関数を利用すれば、VSTACK関数で統合したデータを昇順や降順に並べ替えすることができます。
SORT関数の書式は下記の通りです。
＝SORT(配列,[並べ替えインデックス],[並べ替え順序],[並べ替え基準])
第１引数『配列』：並べ替えするセル範囲を指定検索する文字列を指定
第２引数『並べ替えインデックス』：何列目を並べ替えするか指定
第３引数『並べ替え順序』：昇順は「1」、降順は 「-1」を指定
第４引数『並べ替え基準』：縦方向に並べ替えするときは省略可能
手順１．SORT関数の第１引数を指定
まずは、SORT関数を挿入し、第１引数「配列」にVSTACK関数を入力します。VSTACK関数は、並べ替えしたい元データを指定してください。
=SORT(VSTACK(B3:E6,G3:J6),
手順２．SORT関数の第２、３引数を指定
「算数」(２列目)を昇順(１)で並べ替えするために、第2引数「並べ替えインデックス」は「2」を指定、第3引数『並べ替え順序』は「1」を指定します。
=SORT(VSTACK(B3:E6,G3:J6),2,1)
手順３．SORT関数を確定
SORT関数を確定すると、VSTACK関数で統合されたデータの「算数」列が昇順に並べ替えされます。
SORT関数の使い方を詳しく知りたい方は、以下の記事を参考にしてください。
VSTACK関数/事例６：UNIQUE関数と組み合わせる
UNIQUE関数を利用すれば、VSTACK関数で統合したデータからユニークなデータを抽出することができます。
UNIQUE関数の書式は下記の通りです。(省略可能な引数は除く)
=UNIQUE(配列,[列の比較],[回数指定])
第１引数『配列』：ユニークなデータを返したい行または列を選択
第２引数『列の比較』：”列”の場合は「TRUE」(省略可能)、”行”は「FALSE」を指定
第３引数『回数指定』：省略可能
手順１．UNIQUE関数の第１引数を指定
まずは、UNIQUE関数を挿入し、第１引数「配列」にVSTACK関数を入力します。VSTACK関数は、ユニークな値を抽出したい列を指定します。
=UNIQUE(VSTACK(B3:B8,F3:F8))
手順２．UNIQUE関数を確定
UNIQUE関数を確定すると、セル範囲が離れたB列とF列の氏名からからユニークな氏名を抽出することができます。
UNIQUE関数の使い方を詳しく知りたい方は、以下の記事を参考にしてください。
VSTACK関数/事例７：テーブルを統合する
統合する元データに「テーブル」機能を設定しておくと、元データの大きさが変わっても自動で統合するデータを増減させることができます。
手順１．元データにテーブルを設定
統合する元データにテーブルを設定します。表を選択してから、「Ctrl＋T」キーのショートカットキーで簡単に設定できます。
手順２．VSTACK関数を挿入
VSTACK関数を挿入して、統合するデータをドラッグで選択すると、テーブル名が表示されます。
手順３．VSTACK関数を確定
VSTACK関数を確定すると、２つのテーブルを統合することができます。このテーブルを使った方法を利用すれば、FILTER関数を使って空白行を非表示にする必要もなくなります。
「テーブル」機能の使い方を詳しく知りたい方は、以下の記事を参考にしてください。
VSTACK関数が使えない場合の代用策
VSTACK関数はMicrosoft365(サブスク)を利用しているユーザーしか利用できません。
そのため、VSTACK関数が使えない場合は、「データ」タブの「統合」機能を利用してください。
以下の記事で統合機能について詳しく紹介しているので、興味のある方は覗いてみてください。
