Excel VSTACK関数で複数のシートからデータを集める方法

ExcelでVSTACK関数を使うと、複数のシートからデータを集めることができます。

※VSTACK関数　by　Sony α7SⅢ+70-200mmF2.8Ⅱ

VSTACK関数の基礎

VSTACK（ブイスタック）関数は、記事執筆現在（2022年10月16日）、
・Microsoft 365（Windows、Mac）
で使えるものです。

ブイ＝垂直
スタック＝積み重ねる
という意味で、こういった赤枠で示した複数の範囲のデータをくっつけることができます。

VSTACK関数は次のように使います。
=VSTACK（
の後に、マウスで、くっつけたい範囲を選択しましょう。

この場合、セルA1からB7です。

  

カンマを入れ、
=VSTACK（A1:B7,
さらにくっつけたい範囲をマウスでドラッグして選びます。
ここでは、セルD1からE5です。

これで、指定した範囲（秋葉原、目黒）の売上をくっつけることができました。

 

指定した範囲だけくっつけることができます。
このVSTACK関数は、1つのセル（セルA10）に入れるだけで、それ以降のものも表示できるのです。
（スピルといいます）

データの下のほうはこうなっています。

このVSTACK関数、使いどころとして考えられるのは、複数のシートのデータを1つにする場合です。

 

シートを1つにできるVSTRAC関数

このように秋葉原支店から五反田支店までのデータが、シートごとにあるとします。
すべての支店のデータを集計するのは大変です。

こういった場合にVSTACK関数を使うことができます。

VSTACK関数の基本は、
=VSTACK（範囲、範囲……）
というもの。
※「範囲」は、「配列」という表現をします。

この場合には、秋葉原支店から五反田支店までの範囲を指定すればいいのです。
ただ、1つずつ指定するのは大変。
まとめて指定する方法があります。

ただ、VSTACK関数の基本的な使い方もおさえておきましょう。
まずは、
=VSTACK（
と入れ、まずは秋葉原支店のシートを指定します。
範囲は、セルA1からB85です。
（列AからBというように指定できればいいのですが、エラーになります）

次に目黒支店、その次に……と指定していけば、

  

複数のシートのデータをまとめることができます。

まとめて設定するには、
=VSTACK（
と入れ、
・秋葉原のシート見出しをクリック
・Shiftキーを押しながら、指定する範囲の最終シートである五反田のシート見出しをクリック
とし、

範囲を入力します。
’（入力したほうがうまくいきます）
ここでは、セルA1からB100を指定しました。
各シートで、データの数が違うのですが、それを1つずつ設定するのは大変なので、ざっくりと多めに指定しましょう。

 

データがないところは、このように0となりますが、後で削除もできますので。

これを解決する方法もあります。
FILTER関数を使って、「B列が0より大きいときのみ、データを持ってくる」ということができるのです。

このFILTER関数は、
=FILTER（範囲、条件）
というルールで、
・範囲→VSTACKで指定したもの（各シートのセルA2からB100）
・条件→各シートのセルB2からB100で0より大きいもの
と設定しています。

実際は、もっと多めに10000行くらいで設定しておくといいでしょう。

VSTACK関数は、シートだけでなく複数のファイル（ブック）からも集めることができます。

このVSTACK関数は、おもしろいのですが、じゃあ、使うかどうか。
手放しでおすすめできるものではありません。

VSTRAC関数を使うかどうか

今回の記事の事例で取り上げた、このようなデータや

シートごとに分かれたデータ。
こういったケースがあるのは好ましくありません。

 

データを集計することもできませんし、扱いにくくなりますので。
Excelのデータを整えておけば、VSTACK関数を使う必要もないのです。
もし、こういったデータを受け取った、つくってしまったというなら致し方ありませんが。

VSTACK関数を覚えておいて損はありません。
上記のように、FILTER関数との組み合わせが必要な場合もありますし、複数のファイルの場合だと一工夫必要ですが。

Excelのアップデートの方向性は、「マクロを使わずにExcelで処理できるように」というもので、今後もそうなると思われます。
こういうのもそうでしょうし。
フォルダ内の複数のExcelファイルを結合する「取得と変換」（Power Query）

これらはこれらで覚えておいて、目の前の仕事を効率化することが先決です。

ただ、同様のケースだと、Excelマクロ（VBA）で解決する方法もあります。
こういったプログラムです。
シート「merge」以外のすべてのシートのデータを集める
という処理をしています。

Sub macro()


    Dim Ws
    For Each Ws In Worksheets
    
        If Ws.Name <> "merge" Then
        
            Dim Last_row
            Last_row = Ws.Range("a" & Rows.Count).End(xlUp).Row
            
            Dim Merge_Last_row
            Merge_Last_row = Worksheets("merge").Range("a" & Ws.Rows.Count).End(xlUp).Row
            
            Ws.Rows("2:" & Last_row).Copy Worksheets("merge").Ws.Range("a" & Merge_Last_row + 1)
        
        
        End If
    
        
    Next
    

End Sub

シートがいくら増えてもデータがいくら増えても一瞬で処理できるのです。
2行目からデータがある行までをコピーするので、どんな形式のファイルにも対応できます。
値のみコピーをするならもう少し手を加える必要がありますが。

応用すれば複数のファイルのデータを集めることもできます。
複数のファイル（ブック）を1枚のシートに効率よくまとめるマクロ

Excelマクロの敷居は低くありませんが、汎用性、拡張性があるのはこちらです。
また、Excel、ITを使いこなすためにも、Excelマクロ＝プログラミングは、欠かせません。
（考え方が古いのかもしれませんが……。何事にも基礎スキルは大事だと思いますし、ITはやはりプログラミングすることが基礎です）

Excelの新機能をおさえつつ、Excelマクロも使えるようにしておくのが理想です。

 

 

 

---

■編集後記
昨日は、オフ。
妻が美容院だったので娘といろいろと遊びました。
その他は、Switchのスパロボ30を。

夜はプール。
疲れずに泳ぐ方法を研究。
いい感じになりました。

■1日1新→Kindle『1日1新』
豊洲　マクドナルド
豊洲　おにやんま　カウンター

■娘(5歳）日記→Kindle『娘日記』・ Kindle『娘日記Ⅱ』
ハロウィンで使う魔女のほうきを探す旅へ。
100均だといいものがなく、ネットでも同様でした。
自転車で隣町へ。
ひとまず、ハッピーセット狙いでマクドナルト。
リカちゃんを無事入手し、ホームセンターへ行き、いい感じのほうきが見つかりました。
その後、うどんを食べて帰宅。