Excel(エクセル)で複数のデータを横方向に統合する時に便利なのが、新たに登場したHSTACK関数(エイチ・スタック)です。
HSTACK関数は、異なるセル範囲のデータを合体し、そのデータをさらに並べ替えしたり、特定のデータのみを抽出するなどして利用されます。
さらに、HSTACK関数はXLOOKUP関数と組み合わせて利用すると、異なる列から複数の検索値を一括で抽出する、ということもできます。
今回の記事では、HSTACK関数の基本的な使い方と、HSTACK関数を使った５つの事例を紹介します。
エクセルのHSTACK関数とは？
HSTACK関数の読み方は「エイチ・スタック関数」です。
HSTACK関数とは、「異なる範囲にある複数のデータを横方向に統合して１つにまとめる関数」です。
Hが水平を意味する英語「Horizonal」の頭文字で、「STACK」は大量、桁の大きい数を意味しています。
ちなみに、VSTACK関数という似た関数がありますが、VSTACK関数は縦方向に統合する関数です。
HSTACK関数では、以下のイメージのように、異なるセル範囲のデータを合体し、そのデータをさらに加工するときによく利用されます。
現時点(25年3月)で利用できるユーザーは、Microsoft365(サブスク)を使用しているユーザーと限定的です。
また、HSTACK関数を使うメリットは以下のようなものがあります。
HSTACK関数のメリット
・データの切り貼りで結合する必要がない
・関数を挿入するセルは１つだけで完結！
・データが膨大でも問題なし
・他の関数と組み合わせて、統合したデータを並べ替えしたり、抽出することができる
この関数を１つのセルに使うと、スピル機能が自動で働いて、隣接する複数のセルに結果が表示されるという便利な関数です。
HSTACK関数とVSTACK関数の違い
以下の記事で、VSTACK関数という関数の使い方を紹介しました。
VSTACK関数は、HSTACK関数と機能はほぼ同じですが、データを統合する方向が異なります。
HSTACK関数は横方向にデータを結合するのに対して、VSTACK関数は縦方向にデータを結合します。(下の図を参考)
HSTACK関数の書式と引数
HSTACK関数の書式(構文)と引数は、以下のとおりです。
=HSTACK(配列1,配列2‥‥)
配列(=array)は、統合したいセル範囲をそれぞれ指定します。
統合する配列の行数は必ず同じである必要があります。また、見出しの並び順も一致させておきましょう。
HSTACK関数の基本的な使い方
HSTACK関数の基本的な使い方を解説します。
２つのデータを用意して、手順１～３に沿って、HSTACK関数を使用してみてください。
手順１．HSTACK関数の挿入・第１引数を指定
まずは、空いているセルに、HSTACK関数を挿入します。
挿入したら、第１引数「配列１」を指定するため、１つ目のデータを見出しも含んでドラッグで選択します。
選択したら、次のデータを選択するため、「,」(カンマ)を入力してください。
=HSTACK(B2:E6,
手順２：HSTACK関数の第２引数を指定
次に、第２引数「配列２」を指定するため、２つ目のデータをドラッグして選択します。
今度は見出し列は含めずにセル範囲を選択してください。見出しを含めると、統合したデータに２つの見出し列が挿入されてしまいます。
=HSTACK(B2:E6,H2:I6)
手順３：HSTACK関数を確定
「Enter」キーを押して、HSTACK関数を確定します。
そうすると、引数「配列1」「配列２」で指定したデータが統合されます。
HSTACK関数を挿入したセルはB9セルですが、スピルが働いて、隣接する「B9:G13」のセル範囲にも統合した結果が表示されます。
スピル機能について詳しく知りたい方は、以下の記事を参考にしてください。
HSTACK関数でエラー「#N/A」が発生する原因
HSTACK関数でエラー「#N/A」が発生する場合があります。
そのときは、統合しようとしているデータの行数が異なることが原因です。
例えば、下の図でB9セルに「=HSTACK(B2:E6,H2:I7)」を挿入していますが、エラーが発生しています。
原因は、１つ目のデータは5行で構成されていますが、２つ目のデータは６行のため、複数のセルに「#N/A」が表示されます。
HSTACK関数を使う時は、データの行数と見出しの並びが一致していることを確認するよう注意してください。
【基本編】HSTACK関数の事例を紹介
HSTACK関数の基本的な使い方を３つの事例で紹介します。
元データのセル範囲の行数が増減する場合の対処方法など、役立つ情報を載せていますので実務に活用してください。
HSTACK関数/事例１：複数シートのデータを統合
複数のワークシートの表を１つにまとめる方法を紹介します。
今回は、列数の異なる「表1」「表2」シートのデータを１つにまとめます。
まずは、HSTACK関数を挿入して(図１)、「表１」シートをクリックし(図２)、対象のデータをドラッグで選択します。(図３)
=HSTACK(表１!B2:E6,
次に、「,」(カンマ)を入力してから、「表２」シートをクリックし(図４)、見出しを除く対象データをドラッグで選択します。(図5)
=HSTACK(表１!B2:E6,表２!C2:D6)
HSTACK関数を確定すると、以下のように２つのシートのデータを統合することができます。
統合されたデータは、選択したワークシートの順番で左から表示されます。
フィルター機能のように並び順を変更したい場合は、下の見出しで紹介しているSORT関数と組み合わせた方法を利用してください。
HSTACK関数/事例２：シートの串刺し演算
複数のワークシートの表を１つにまとめる方法は、もう１つあります。
「串刺し演算」と呼ばれる方法をHSTACK関数で利用して、データを統合します。
手順１．HSTACK関数を挿入
見出し行を作成して、その右上のセルにHSTACK関数を挿入します。
手順２．ワークシートを選択
統合したいシートのうち、一番左のシートをクリックし(図3)、「Shift」キーを押しながら一番右のシートをクリックします。(図4)
シートの選択が終わったら、見出しを除くデータをドラッグで選択してください。(図5)
手順３．HSTACK関数を確定
HSTACK関数を確定すると、数式は以下のようになり、「表1」「表2」「表3」シートを串刺しして統合することができます。
=HSTACK(表１:表３!C2:D6)
ただし、表の元データの列数がそれぞれ異なっていると、空白の列に対しては「0」が表示されます。
この列数が異なることで発生する「0」を非表示に方法を次の見出しで紹介します。
HSTACK関数/事例３：FILTER関数で空白列の「0」を表示しない
HSTACK関数で統合する元データのセル範囲が変動する場合は、選択するセル範囲を大きめに選択しておくことをおすすめします。
ただ、選択したセル範囲に空白列が存在すると、下の図のように、HSTACK関数の結果は「0」で返されてしまいす。
この空白列が原因で発生する「0」の列を表示しない方法を紹介します。
利用するのは、データを抽出することができるFILTER関数と、上の見出しで紹介した串刺し演算です。
FILTER関数の書式は以下のとおりです。
＝FILTER( 配列,含む,[空の場合]）
第１引数『配列』：データを絞りたいセル範囲を指定
第２引数『含む』：抽出する条件を指定
第３引数『空の場合』：省略可能
手順１．FILTER関数の後にHSTACK関数を挿入
FILTER関数を挿入し、FILTER関数の第１引数「配列(セル範囲)」にHSTACK関数を入力します。
=FILTER(HSTACK(表１:表３!C2:E6),
手順2 ．FILTER関数の第２引数を指定
FILTER関数の第２引数「含む(=抽出条件)」を指定します。
見出し行の2行目の値が「0」のセルを除きたいので、「0以外のセル」を抽出条件にするため「HSTACK(表1:表3!C2:E2)<>0」と指定します。
=FILTER(HSTACK(表１:表３!C2:E6),HSTACK(表１:表３!C2:E2)<>0)
手順３．FILTER関数を確定
FILTER関数を確定すると、空白の列は表示されず、HSTACK関数の結果を取得することができます。
このように、表の大きさは変化することが多いので、HSTACK関数はFILTER関数とセットで利用することをお勧めします。
今回紹介したFILTER関数の使い方を詳しく知りたい方は、以下の記事を参考にしてください。
【応用編】HSTACK関数の事例を紹介
次に、HSTACK関数の応用テクニックを事例で紹介します。
HSTACK関数/事例４：XLOOKUP関数と組み合わせて検索する
XLOOKUP関数とは、VLOOKUP関数が進化した検索用の新関数です。
表の中から異なる列の値を一括で抽出する時は、XLOOKUP関数にHSTACK関数を組み合わせると便利です。
XLOOKUP関数の書式は下記の通りです。(省略可能な引数は除く)
＝XLOOKUP(検索値,検索範囲,戻り範囲）
第１引数『検索値』：検索する文字列を指定
第２引数『検索範囲』：どのセル範囲から探したいかを指定
第３引数『戻り範囲』：どのセル範囲を返したいかを指定
今回は上の表から、「小関さん」の「算数」「社会」「国語」の点数を検索する手順を解説します。
手順１．XLOOKUP関数を挿入
C12セルにXLOOKUP関数を挿入して、第１引数「検索値」と第２引数「検索範囲」を指定します。
=XLOOKUP(B12,B3:B8,
手順２．XLOOKUP関数の第３引数を指定
XLOOKUP関数の第３引数「戻り範囲」にHSTACK関数を挿入します。
=XLOOKUP(B12,B3:B8,HSTACK(
手順３．HSTACK関数の引数を指定
HSTACK関数の引数を指定します。検索値を抽出したい列を「Ctrl」キーを押しながら、ドラッグして選択します。「算数」「社会」「国語」の点数を抽出したいので、この３列を指定します。
=XLOOKUP(B12,B3:B8,HSTACK(C3:C8,E3:E8,G3:G8))
手順４．数式を確定
XLOOKUP関数を確定すると、「小関さん」の「算数」「社会」「国語」の点数を一括で抽出することができます。
XLOOKUP関数の使い方を詳しく知りたい方は、以下の記事を参考にしてください。
HSTACK関数/事例５：SORT関数と組み合わせて並べ替えする
SORT関数を利用すれば、HSTACK関数で統合したデータを昇順や降順に並べ替えすることができます。
SORT関数の書式は下記の通りです。
＝SORT(配列,[並べ替えインデックス],[並べ替え順序],[並べ替え基準])
第１引数『配列』：並べ替えするセル範囲を指定検索する文字列を指定
第２引数『並べ替えインデックス』：何列目を並べ替えするか指定
第３引数『並べ替え順序』：昇順は「1」、降順は 「-1」を指定
第４引数『並べ替え基準』：縦方向に並べ替えするときは省略可能
手順１．HSTACK関数でデータを統合
まずは、HSTACK関数を入力して、２つの表のデータを統合します。
＝HSTACK(B3:E6,H3:I6)
手順２．SORT関数を利用
手順１で作成したHSTACK関数を囲うように、SORT関数を挿入します。(下の数式の緑下線部)
「国語」(３列目)を昇順(１)で並べ替えするために、第2引数「並べ替えインデックス」は「3」を指定、第3引数『並べ替え順序』は「1」を指定します。
=SORT(HSTACK(B3:E6,H3:I6),3,1)
SORT関数を確定すると、HSTACK関数で統合されたデータの「国語」列が昇順に並べ替えされます。
SORT関数の使い方を詳しく知りたい方は、以下の記事を参考にしてください。
HSTACK関数が使えない場合
HSTACK関数はMicrosoft365(サブスク)を利用しているユーザーしか利用できません。
そのため、HSTACK関数が使えない場合は、「統合」機能を利用して、データを合体します。
まず、結合する先のセルを選択し、「データ」タブの「統合」ボタンをクリックします。
「統合の設定」画面が表示されます。「統合元範囲」で統合したい表１のセル範囲を指定して、「追加」ボタンを押します。
つづいて、「統合元範囲」で統合したい２つ目のデータ(表２)のセル範囲を指定して、「追加」ボタンを押します。
左下の「統合の基準」の「上端行」にチェックを入れて、「OK」を押します。
以上の操作により、データの結合が完了します。
統合機能の詳しい使い方は、以下の記事を参考にしてください。
