VSTACK関数が使えないExcelでもOK｜INDEX関数で複数の表を縦に結合する方法

VSTACK関数が使えなくて困っていませんか？

Excel 365やExcel 2021では便利なVSTACK関数が使えますが、
・Excel 2019以前を使っている
・会社のPCでExcelのバージョンを上げられない
といった理由で、VSTACK関数が使えない方も多いのではないでしょうか。

また、その場合はVBAを使用するのもおすすめですが、
・VBAやPower Queryを使うのは難易度が高い
と、気が進まない人も多いと思います。

でも安心してください。
INDEX関数を使えば、古いExcelでもVSTACK関数と同じような処理が可能になります。

この記事では、
・VSTACK関数の代わりにINDEX関数で縦結合する考え方
・実務で使える具体的な数式
・注意点と限界点
をできるだけ分かりやすく解説します。

特に、
「Excel 縦結合古い」
「VSTACK 使えない」
と検索している方に向けた内容です。

INDEX

VSTACK関数でやりたいことを整理
INDEX関数で縦結合する基本的な考え方
【基本】2つの表を縦に結合する方法
3つ以上の表を縦に結合したい場合
INDEX関数代替の注意点と限界
1. 注意点
2. 正直な結論
まとめ｜環境に応じて使い分けよう

VSTACK関数でやりたいことを整理

VSTACK関数は、複数の表を縦方向に連結して、1つの縦長の表として表示します。
VSTACK関数の説明と具体的な使い方については、以下記事で詳しく解説しています。
ぜひご覧ください。

要約すると、VSTACK関数では、
・複数の表を
・縦方向に連結して
・1つの表として表示する
という処理を行っています。

こちらの処理を、INDEX関数を代用して再現していくことになります。

INDEX関数で縦結合する基本的な考え方

まずINDEX関数とは？ですが、簡単に言うと
・表の中から、行番号と列番号を指定してその場所の値を取り出す
という関数になります。
つまり、行番号と列番号を取得することが今回のポイントになります。

では、この行番号・列番号をどうやって取得するか？ですが、
・行番号：ROW関数
・列番号：COLUMN関数
を使用します。
この二つの関数を組み合わせ、「今、何行目/何列目を表示しているか」を判定してきます。

ポイントはシンプルで、表示する行番号が、
・前半なら「表A」
・後半なら「表B」
というように、ROW関数で参照先を切替えて、データを縦結合していきます。

【基本】2つの表を縦に結合する方法

ここでは、シートが異なる2つの表を縦に結合する方法を紹介します。

前提条件

今回は、以下のような2つの人員名簿シートを結合していきます。

・シート1　→　A2:E4
・シート2　→　A2:E5

このシート1、シート2を結合したデータを、新たなシートに表示させます。

使用する数式

ではさっそく、数式を使って表を結合していきます。
別のシートに、以下の数式をセルA2に貼り、右方向・下方向にコピーします。

=IF(ROW(A1)<=ROWS(シート1!$A$2:$A$4),INDEX(シート1!$A$2:$E$4, ROW(A1), COLUMN(A1)),INDEX(シート2!$A$2:$E$5, ROW(A1)-ROWS(シート1!$A$2:$A$4), COLUMN(A1)))

この数式を反映した後の結果です。
見事にVSTACK関数のように、表が縦結合することができました。

数式の意味を分解

「え、でもこれってどういう意味の数式なの？」
と疑問を持たれた方もいると思います。
この数式が行っている処理を、分解して説明していきます。

【1】．ROW(A1)で行番号を取得
・これで表示している行番号を取得します。
・1行目では「1」、2行目では「2」と数が増えていきます。

【2】．ROWS(シート1!$A$2:$A$4)
・ROWS関数は、指定した範囲の行数を取得します。
・この数式では、シート1の2~4行目が範囲になっているため、「3」を指定することになります。
※ROWS関数では、空白行も含んでしまうので注意

【3】．IF関数で参照先を切り替え
・ROW関数で取得した行番号が、
　→【2】で指定した数以内であるとき、シート1を参照
　→【2】で指定した数を超える場合、シート2を参照
するという分岐を、IF関数にて表現しています。

【4】．シート2を参照する場合は、シート1分の行数を引いた行番号を取得
・　ROW(A4)-ROWS(シート1!$A$2:$A$4)とすることで、シート2の2行目からデータを取得することが可能になる。

という流れで、シート1とシート2のデータを縦結合しているわけです。

ですが、この方法では表にあった範囲を指定しないときれいに表結合ができません。
範囲を大きくすると、不要な空白行まで含んでしまいます。

例えば、シート1の範囲を、シート2と同じく5行目まで広げてみるとどうでしょう。

シート1には4行目までしかデータがないので、空白行が入ってしまいました。
こうなると、実務でもちょっと活用しづらいですよね。

表の増減がある場合、ある程度範囲を柔軟に設定できると、より実務でも活用しやすいと思います。

空白行を含めないようにする方法（実務向け）

ではどのようにしたら、範囲を広げても空白行が含まれないようにすることができるでしょうか。
それは、シート1の実データ行数を基準にすれば、空白行は表示されません。

=IF(ROW(A1)<=COUNTA(シート1!$A$2:$A$10),INDEX(シート1!$A$2:$E$10, ROW(A1), COLUMN(A1)),INDEX(シート2!$A$2:$E$15, ROW(A1)-(COUNTA(シート1!$A$2:$A$10)), COLUMN(A1)))

※ A列に必ず値が入る前提です。
※上記では、10行目までを範囲にしています。必要に応じて変更してください。

【ポイント】．COUNTA関数で実データ行をカウント
・COUNTA関数を使用することで、実データ行のみをカウント（＝空白行はカウントしない）

ROWS関数では、たとえ空白行であっても、対象範囲の行数が計算されます。
そのため、空白行を含めたくない場合、COUNTA関数を使うことで解決することができます。

3つ以上の表を縦に結合したい場合

先ほどのシート1、シート2に加え、以下のシート3を加えてみます。

表が3つ以上ある場合は、IF関数を入れ子にします。
つまり、
・まずはシート1の範囲
・次にシート1＋シート2の範囲
・それ以降はシート3の範囲
というように、段階的に判定します。

=IF(ROW(A1)<=COUNTA(シート1!$A$2:$A$10),INDEX(シート1!$A$2:$E$10, ROW(A1), COLUMN(A1)),IF(ROW(A1)<=COUNTA(シート1!$A$2:$A$10)+COUNTA(シート2!$A$2:$A$10),INDEX(シート2!$A$2:$E$10, ROW(A1)-(COUNTA(シート1!$A$2:$A$10)), COLUMN(A1)),INDEX(シート3!$A$2:$E$10, ROW(A1)-(COUNTA(シート1!$A$2:$A$10))-(COUNTA(シート2!$A$2:$A$10)), COLUMN(A1))))

※ 表が増えるほど数式は複雑になります。

これにより、3との表を縦結合することができます。
ロジックとしては2つの表を結合する場合と同様で、IF関数で分岐を一つ追加したイメージとなります。