新しい関数とスピルで作る抽出と並べ替えの仕組み

今回次のような物を作ってみました。

抽出条件に入力したものを一覧表に表示して、並べ替える項目を指定して、昇順降順か指定するだけでその一覧表が出来上がってしまう仕組みです。

従来のExcelでも計算式だけでこのような仕組みを作ることはできましたが、新関数の一覧表から抽出する関数あるFILTER関数と、並べ替えることができるSORT関数を、スピル機能で組み合わせることによって、今までの1/3程度の作成時間でこのような仕組みを作ることができるようになりました。

その作り方を解説します。今回は次の状態を入力してから作ります。

テーブル設定

元のデータとなるA列からD列のデータは、ホームタブのテーブルとして書式設定を使ってテーブルにしておきます。テーブル名もわかりやすい名前をつけておくと良いと思います。今回は「予定一覧」にします。

関数

今回は関数を組み合わせて作っていきます。

FILTER関数

一覧表からデータを抽出する関数で、書式は次の通りです。

=FILTER(抽出したい範囲,検索条件範囲=抽出条件)

「検索条件範囲=抽出条件」の「=」は日付や数値データであれば不等号を使うことができます。

今回は、セルF2に入力された日付と同じ予定日のデータの予定CDを出力します。その計算式は次のようになります。

=FILTER(予定一覧,予定一覧[予定日]=F2,””)

この計算式を一旦セルK1に入力します。

MATCH関数

その値がセル範囲の何番目にあるかを調べる関数です。

書式は次の通りです。

=MATCH(検索値,検索範囲,0)

最後の0は、その値が範囲の中で完全一致するものを探す場合です。もし近似値で検索する場合は1か-1を指定します。今回は完全一致なので0です。

セルG2の並べ替え基準が全体の何列目を表しているかを求めるのに使います。

これで求めた値は実際に目で見て確認しなくてもいいものなので、今回は、ちょっと離れたセルP1にその値を求めます。

=MATCH(G2,予定一覧[#見出し],0)

検索範囲としては一覧表の項目名を使いました。

この計算式をセルP1に入力します。

IF関数

もしもどうだったらどうするかという関数です。

書式は次のとおりです。

=IF(条件のセル=値,条件のセルが値と同じ場合に出力する値,条件のセルが値と同じ場合に出力する値)

条件のセル=値の部分は、その条件が成立しているかというものを入力するところです。=が不等号の場合もありますし、条件のセル1つ目と条件のセル2つ目の比較と指定する場合もあります。使用するケースで一番多いのは今回の例の条件のセル=値のパターンです。

このパターンはよく値の変換に使います。

今回は、理由は後述しますが、セルH1が「昇順」なら1、「降順」なら-1としたいのです。

「昇順」か「降順」かの2つに1つの場合、IF関数は1つ目の条件と同じか、そうでなければ

という指定をしますので、今回は次のような計算式になります。

=IF(H2=”昇順”,1,-1)

比較するものが文字列であれば””で囲みます。

では、この値も直接人間が確認するものではないので、この計算式をセルQ1に入力します。

SORT関数

並べ替える関数です。書式は次のとおりです。

=SORT(並べ替える表,並べ替えに使う列,昇順か降順か)

並べ替えに使う列は何列目かというのを数字で指定します。

MATCH関数で求めた数値はここで使います。

昇順か降順かは、昇順であれば1、降順であれば-1になります。

IF関数で求めたものはここで使うことになります。

今回並べ替える表は、上記のFILTER関数で求めたセルK1から始まる一覧表です。

従来はK1:N10のように範囲指定しなければなりませんでしたが、新たに搭載されたスピル機能によって、範囲に結果を求める計算式は、その計算式が入っているセル参照に#をつける形で指定すればその一覧表全てを選択することができるようになりました。

今回、セルF5に入力する計算式は次のとおりになります。

=SORT(K1#,P1,Q1)

この計算式をセルF5に入力すれば完成です。

まとめ

たったこれだけの手順で操作ができる見やすい表を作ることができます。

Excelの機能でも並べ替えやフィルターをすることはできますが、それぞれ操作をしなければ結果が変わりません。Power Queryを使えばそのようなことも手軽にできるようになりましたが、それでも更新という作業が必要なので更新し忘れするとデータが昔のままになってしまうというミスが起きる可能性があります。

今回の仕組みは全て計算式なので、元のデータが入力された時点で結果が変わってくれますし、それぞれの条件も変わった時点で再計算してくれますので操作し忘れによるミスは最低でも防ぐことができています。

新しい関数やスピル機能は、Office365またはOffice2019という最新のバージョンにしか搭載されていないのでまだまだ使えない環境の方も多いのですが、新しいExcelにすればこのような便利な計算式がこのくらい手軽に作成できるようになっています。

サトウヨシヒロ

Excelを中心としたMicrosoft Officeの魔法使い。
仙台市在住のフリーランスのパソコンインストラクターです。
1969年1月生まれ。
Excelを黎明期から30年近く使っています。Wordも使えます。
出版社様と一緒にExcelの情報発信もしています。
最近は羊に夢中です。
2020年は令和時代のExcelの使い方と教え方を勉強しています。

Twitterでも業務で使える便利なOffice技をめっちゃつぶやくツイッタラーです。@yosatonet

コメントを残す

メールアドレスが公開されることはありません。