今回はExcelワークシート関数の新関数の一つであるVSTACK関数及びHSTACK関数を紹介します。VSTACKを中心に使い方を説明し、最終的には他の関数との組み合わせで簡易的なシステムを構築する方法を紹介します。
VSTACK関数とHSTACK関数とは何か
VSTACK関数とHSTACK関数はサブスクリプションのOfficeや、購入型のOffice2020以降で使用できます。VSTACK関数はセルの範囲を垂直方向に結合、HSTACK関数は水平方向に結合します。Pythonなどプログラミング経験者であれば、concatinateという名前のほうがしっくりくるかもしれません。
これらの関数は、データ分析において、複数のデータテーブルを組み合わせる必要がある場合や、異なるフォーマットのデータを並べ替える必要がある場合に有用です。
VSTACK関数とHSTACK関数の構文は以下の通りです。
=VSTACK(配列1,配列2…)
=HSTACK(配列1,配列2…)
以下では、VSTACK関数を例に、使い方を説明します。
関数の基本的な使い方
VSTACK関数の最もわかりやすい使い方は、複数の同じ内容のシートを統合するというものです。例えば以下のように、販売実績データが県ごとにシートが分かれている場合、VSTACK関数を使って1つのシートに統合することができます。
具体的には以下の通り、1行目のラベルだけコピーしておいて、各シートの2行目以降をVSTACK関数で結合しています。
ただし、この方法では各シートのデータが変わった時にその内容が変更されません。VSTACK関数とHSTACK関数はスピル機能を持っているので、せっかくであれば動的な結合リスト、つまり各シートの内容が変更されたときにその内容を反映できるようなリストにした方が、有用です。以下では動的な結合リストの作り方を2つ紹介します。
動的な結合リストの作り方
テーブルを設定する
1つ目の方法は、テーブルを設定する方法です。統合元の各シートがテーブルとなっていれば、内容に変更があっても、統合先シートにも反映することができます。
VSTACK関数の指定方法は以下の通りです。
他の関数と組み合わせる
もしテーブルを設定できない場合は、数式が非常に複雑になってしまいますが、以下のようにINDIRECT関数・ADDRESS関数・COUNTA関数を組み合わせることでも動的な結合リストを作ることができます。
INDIRECT関数・ADDRESS関数・COUNTA関数を組み合わせる使い方については以下の記事を参照ください。
VSTACK関数を使って簡易システムを構築する
続いて、VSTACK関数を使って簡易的なシステムを構築できることを紹介します。
今回は納期管理のシステムを構築します。事前に以下の通り、”年始在庫”データと”在庫変化”データを用意します。年始在庫データは、在庫を計算する起点のタイミングで各商品の在庫がいくつあったかというデータです。棚卸を実施した後のデータのようなイメージです。
在庫変化データは、在庫の増減に関するデータで、例えば売上により在庫が減るとか、入荷予定で増えるといったデータです。
続いて、新たに希望する商品・希望納期・希望数量を投入します。
これら3つのデータをVSTACK関数を使って統合し、それらを日付順に並べることで、在庫不足が発生するかどうか確認するシステムを構築することができます。
具体的には以下の画像の通り、まず大枠としては赤線で示した通りVSTACKで3つの配列を統合した後に、SORT関数で日付順に並び替えています。青色で示した通りVSTACKで統合している内容は、XLOOKUPで引用している年始在庫データ、左側の希望商品データ、そしてFILTER関数で引用している在庫変化データです。
SORT関数、XLOOKUP関数、FILTER関数については以下を参照ください。
数式の詳細は以下の通りです。
=SORT(VSTACK(XLOOKUP($B$3,年始在庫!$A:$A,年始在庫!B:C),C3:D3,FILTER(在庫変化!B2:C140,在庫変化!A2:A140=納期管理システム!$B$3)),1,1)
続いて、在庫変化を積算して、それぞれのタイミングで在庫が合計いくつになるのかを計算させます。
あとは、在庫数量がマイナス＝在庫不足なので、条件付き書式でマイナス値をハイライトすれば、どのタイミングで在庫が足りなくなるのか、すぐに理解できます。
更にこれをグラフ化することで、よりわかりやすくなります。
この簡易システムは、年始在庫・在庫変化・希望商品の情報を変えても、自動で更新されます。自動更新されるのは、Excel新機能のスピル機能によるものです。スピル機能については以下を参照ください。
コメント