【Excel】HSTACK・VSTACKで別シートの表を結合!Excelの複数範囲を動的にまとめる方法

Excelで複数の表を一つにまとめたい場面は多いです。特に、別々のシートにあるデータを結合したい場合、手作業でのコピー&ペーストは手間がかかります。

しかし、Microsoft 365の最新関数HSTACK関数とVSTACK関数を使えば、この作業を自動化し、動的にデータをまとめることが可能です。

この記事では、これらの関数を使った別シートの表の結合方法と、複数範囲を動的にまとめるテクニックを解説します。

HSTACK関数とVSTACK関数の概要

ExcelのHSTACK関数とVSTACK関数は、Microsoft 365の最新機能として追加されました。これらの関数は、配列(範囲)を横方向または縦方向に結合するための強力なツールです。

従来、このような結合にはPower QueryやVBAが必要でしたが、これらの関数を使えば数式だけで実現できます。これにより、データ集計やレポート作成の効率が大幅に向上します。

HSTACK関数とは

HSTACK関数は、指定した配列を横方向(列方向)に並べて結合します。例えば、2つの表の列を横に連結したい場合に利用します。

構文は「=HSTACK(配列1, [配列2], …)」です。配列1、配列2など、結合したい配列をカンマで区切って指定します。

VSTACK関数とは

VSTACK関数は、指定した配列を縦方向(行方向)に並べて結合します。例えば、複数の表の行を縦に積み重ねたい場合に利用します。

構文は「=VSTACK(配列1, [配列2], …)」です。配列1、配列2など、結合したい配列をカンマで区切って指定します。

動的な結合のメリット

これらの関数を使った結合は「動的」です。これは、元のデータが格納されているシートの表が更新されると、結合結果が自動的に最新の状態に反映されることを意味します。

手作業での更新作業が不要になるため、常に最新のデータに基づいた集計や分析が可能になります。特に、定期的に更新されるデータソースを扱う場合に非常に有効です。

別シートの表をHSTACK関数で横方向に結合する手順

ここでは、異なるシートにある2つの表をHSTACK関数を使って横方向に結合する具体的な手順を説明します。

1. 結合結果を表示するシートを選択する
   まず、結合された表を表示したいシートを開きます。新規シートでも既存のシートでも構いません。
2. HSTACK関数を入力するセルを選択する
   結合結果の左上に表示させたいセルを選択します。
3. HSTACK関数を入力する
   選択したセルに、以下の数式を入力します。
   =HSTACK(Sheet1!A1:B5, Sheet2!C1:D5)
   この例では、「Sheet1」のA1からB5の範囲と、「Sheet2」のC1からD5の範囲を横方向に結合しています。シート名や範囲は、実際のデータに合わせて変更してください。
4. Enterキーを押して結果を確認する
   数式を入力したらEnterキーを押します。指定した範囲のデータが横方向に結合されて表示されます。

別シートの表をVSTACK関数で縦方向に結合する手順

次に、異なるシートにある複数の表をVSTACK関数を使って縦方向に結合する手順を説明します。

1. 結合結果を表示するシートを選択する
   結合された表を表示したいシートを開きます。
2. VSTACK関数を入力するセルを選択する
   結合結果の左上に表示させたいセルを選択します。
3. VSTACK関数を入力する
   選択したセルに、以下の数式を入力します。
   =VSTACK(Sheet1!A1:B5, Sheet2!A1:B5, Sheet3!A1:B5)
   この例では、「Sheet1」、「Sheet2」、「Sheet3」のA1からB5の範囲を縦方向に結合しています。シート名や範囲は、実際のデータに合わせて変更してください。
4. Enterキーを押して結果を確認する
   数式を入力したらEnterキーを押します。指定した範囲のデータが縦方向に結合されて表示されます。

複数範囲を動的にまとめる応用テクニック

HSTACK関数とVSTACK関数は、単一の範囲だけでなく、複数の範囲を組み合わせて動的にデータをまとめる際にも非常に役立ちます。

組み合わせによる複雑な結合

HSTACK関数とVSTACK関数は、入れ子にして使用することも可能です。これにより、より複雑なデータ構造を動的に構築できます。

例えば、VSTACK関数で複数の表を縦に結合した後、その結果をHSTACK関数で別の表と横に結合するといった操作ができます。これにより、様々な形式のデータを柔軟に集計・整理することが可能になります。

Excelテーブルとの連携

HSTACK関数やVSTACK関数は、Excelテーブル(表形式のデータ)と組み合わせて使用すると、さらに強力になります。

Excelテーブルは、データの追加や削除に伴って範囲が自動的に拡張・縮小される特性があります。このテーブルをHSTACK関数やVSTACK関数の引数に指定することで、元のデータが更新された際に結合結果も自動的に追従する、真に動的なデータ集計が可能になります。

動的配列の活用

HSTACK関数とVSTACK関数は、動的配列関数です。これは、数式を1つのセルに入力するだけで、結果が複数のセルに自動的に展開されることを意味します。

この動的配列の性質を利用することで、数式の結果が格納される範囲を事前に確保する必要がなくなります。これにより、シートレイアウトの変更やデータ量の増減に柔軟に対応できます。

HSTACK関数・VSTACK関数でよくある失敗と対処法

これらの新しい関数を使用する際には、いくつかの注意点やよくある失敗パターンがあります。

#CALC!エラーが発生する場合

HSTACK関数やVSTACK関数で#CALC!エラーが発生した場合、原因は主に結合しようとしている配列のいずれかにエラーが含まれていることです。

対処法としては、まず各配列(参照しているセル範囲)にエラーがないか個別に確認します。例えば、結合したい範囲に#N/Aや#VALUE!などのエラー値が含まれている場合、それが原因で#CALC!エラーが発生します。エラーが発生している範囲を特定し、IFERROR関数などでエラー値を適切に処理してから再度結合を試みてください。

結合される範囲のサイズが一致しない場合

HSTACK関数やVSTACK関数は、結合する配列のサイズが一致しない場合でも、原則として結合を実行できます。

しかし、意図しない結果になることがあります。例えば、VSTACK関数で列数が異なる範囲を結合すると、Excelは不足している列を空白として扱います。HSTACK関数で列数が異なる範囲を結合した場合も同様に、行数が不足している部分は空白で埋められます。

期待通りの結果を得るためには、結合する範囲のサイズ(行数または列数)を事前に揃えるか、IF関数やREPT関数などを用いて、不足している部分を意図した値(例えば空白文字「””」)で埋めるように工夫する必要があります。

参照範囲が空の場合

結合する配列のいずれかが完全に空の場合、HSTACK関数やVSTACK関数はエラーを返さずに、空の配列として扱います。しかし、結合結果が期待通りにならないことがあります。

特に、VSTACK関数で空の範囲を結合した場合、その空の範囲は結果に含まれません。HSTACK関数で空の範囲を結合した場合も同様です。

もし、空の範囲も結果に含めたい場合は、IF関数などを用いて、範囲が空の場合は特定の文字(例えば「N/A」など)を返すように数式を調整することを検討してください。これにより、どの範囲が結合されたかを確認しやすくなります。

Microsoft 365のバージョンによる制限

HSTACK関数とVSTACK関数は、Microsoft 365の最新バージョンで利用可能な関数です。そのため、古いバージョンのExcel(Excel 2019、Excel 2016など)ではこれらの関数を使用できません。

これらの関数が使えない環境では、Power QueryやVBAを使用する必要があります。もし、これらの関数が使えない環境でファイル共有を行う場合は、代替手段を検討するか、数式を値に変換して共有するなどの対応が必要です。

HSTACK関数とVSTACK関数の比較

HSTACK関数とVSTACK関数は、どちらも配列を結合する関数ですが、結合の方向性が異なります。それぞれの特徴を比較します。

項目	HSTACK関数	VSTACK関数
結合方向	横方向(列方向)	縦方向(行方向)
主な用途	並列するデータをまとめる	積み重ねるデータをまとめる
引数	配列1, 配列2, …	配列1, 配列2, …
Excel 2019以前	利用不可	利用不可
Microsoft 365	利用可能	利用可能

まとめ

HSTACK関数とVSTACK関数は、Excelで複数範囲のデータを動的に結合するための画期的な機能です。

これらの関数を使いこなすことで、別シートの表を横方向または縦方向に簡単にまとめることができ、手作業による集計・更新作業の負担を大幅に軽減できます。

Excelテーブルと組み合わせることで、さらに強力な動的データ集計環境を構築することも可能です。Microsoft 365ユーザーは、ぜひこれらの関数を活用して、業務効率の向上を目指してください。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】