Excel の関数「LET・FILTER・HSTACK／COLUMN」などを組み合わせたデータ抽出

最近、以下の Excel に関する記事を書いたのですが、この記事も Excel ネタです。

●Excel で複数の年・月が別セルに書かれたものを統合して和暦にする（スピルを利用したり表示で元年の表記を使ったりも） - Qiita
　htps://qiita.com/youtoy/items/2720377a88c0a35b5197

とあるデータ処理で Excel の関数を使ったのですが、それに関するメモ的な記事を書いておこうと思います。

そのデータ処理で最終的に行った内容は、今回の内容だけではないのですが、ひとまず区切りの良いところを記事化してみます。

今回やること

今回やることは「隣り合っていない複数の列を取り出す」というものです。今回は、以下の仮のデータを使って進めていきます。

取り出す列を、以下として進めていきます。

さらに、上記の列抽出を 1段階目の処理として、さらに追加の 2段階目の抽出処理もやってみようと思います。

今回使う関数

上記を実現する方法は複数ありますが、今回は以下を使った方法を試します。

• LET関数
• FILTER関数
• HSTACK／COLUMN関数

実際の内容

それでは、実際の内容に入っていきます。

パターン1

まず、HSTACK・FILTER の 2つの関数を抽出に使った方法です。
それに関して、LET関数で名前をつけつつ、列の抽出を行います。

まずは、上の画像で赤枠で囲んでいた部分を取り出します。

=LET(列を抽出した結果, HSTACK(A1:A5, C1:C5, E1:E5),
  列を抽出した結果
)

処理の内容は、複数の列を取り出したものを「HSTACK」関数で結合するというものです。

以下は、上記を「A7」に入力した結果です。
出力としては、スピルにより右と下にも結果が展開された形になります。

さらに E列の内容を条件にした、追加の抽出処理もやってみます。
抽出前後で一番右にある列の各セルの値が、特定の数値より大きいものを取り出してみます。

=LET(列を抽出した結果, HSTACK(A1:A5, C1:C5, E1:E5),
  条件, E1:E5 >2,
  特定の行, FILTER(列を抽出した結果, 条件),
  特定の行
)

上記の処理を適用した結果は、以下のとおりです。

下3行分の内容が取り出せているのが分かります。

パターン2

今度は、FILTER・COLUMN の 2つの関数を抽出に使った方法です。以下のようにしてみます。

=LET(データ全体,A1:E5,
  条件,(COLUMN(データ全体)=1)+(COLUMN(データ全体)=3)+(COLUMN(データ全体)=5),
  特定列のみの表,FILTER(データ全体,条件),
  特定列のみの表
)

処理結果は、以下のとおりです。

ここで先ほどと同様、追加の処理を加えてみます。そして、先ほどと同様の結果が得られるのを確認できました。

=LET(データ全体,A1:E5,
  条件1,(COLUMN(データ全体)=1)+(COLUMN(データ全体)=3)+(COLUMN(データ全体)=5),
  特定列のみの表,FILTER(データ全体,条件1),
  条件2, E1:E5 >2,
  特定の行, FILTER(特定列のみの表, 条件2),
  特定の行
)

参考

●LET 関数 - Microsoft サポート
　https://support.microsoft.com/ja-jp/office/let-%E9%96%A2%E6%95%B0-34842dd8-b92b-4d3f-b325-b8b8f9908999

●FILTER 関数 - Microsoft サポート
　https://support.microsoft.com/ja-jp/office/filter-%E9%96%A2%E6%95%B0-f4f7cb66-82eb-4767-8f7c-4877ad80c759

●HSTACK 関数 - Microsoft サポート
　https://support.microsoft.com/ja-jp/office/hstack-%E9%96%A2%E6%95%B0-98c4ab76-10fe-4b4f-8d5f-af1c125fe8c2

●COLUMN 関数 - Microsoft サポート
　https://support.microsoft.com/ja-jp/office/column-%E9%96%A2%E6%95%B0-44e8c754-711c-4df3-9da4-47a55042554b