Excelの新しい関数、FILTER関数があります。
Googleスプレッドシートでは実装されていたのですが、それがExcelにも実装されるようになりました。
この関数は、一覧表の中の条件に合うリストを出してくれる関数で・・・え？リストを出してくれる関数？という疑問があります。
普通、関数は1つのセルにしか答えを出しませんよね。
Office Insiderという誰でもなれる先行機能のお試し版にすれば、FILTER関数を含む、リストで出してくれる新しい関数を使うことができるようになります。
リストで出してくれる関数ってめっちゃ便利なんですよ。
FILTER関数の書式
FILTER関数の書式は次の通りです。
=FILTER(元データの範囲,条件)
次のような一覧表があります。
商品が「牛乳」のものだけを抽出したリストをセルF5からの範囲に出したいときは、セルF5に次の計算式を入力します。
=FILTER(A2:D16,C2:C16=”牛乳”)
結果がセルF5からの範囲に出てきます。G列は日付シリアル値の数値になっていますが、表示形式を変更すればOKです。
日付からある期間を取り出す
日付からある期間のデータを抜き出すこともできます。
その場合、指定の始まり日以降で、指定の終了日以前のデータを選ぶという２つの条件になります。
しかし、上記の書式では１つの条件しか指定できないのですね。
そこで、論理演算式を使います。といってもAND関数やOR関数を使うのではなく、どこがどうだという式を足し算または乗算します。逆にAND、OR関数は使えません。
なおかつ条件が乗算、または条件が足し算です。
販売日はB列なので、B2からB16の範囲で始まり日付以降で、なおかつ終わり日以前の条件にします。
=B2:B16>=始まり日
=B2:B16<=終わり日
を乗算で結びますが、それぞれの式を（）でくくります。等号、不等号より足し算、乗算の方が先に計算されてしまうようです。
１月４日から１月７日まででFILTER関数を使う場合は、
=FILTER(A2:D16,(B2:B16>=DATE(2019,1,4))*(B2:B16<=DATE(2019,1,7)))
結果が求まります。日付の指定はその日付シリアルかDATE関数でないと受け付けません。”2019/4/1″形式では、文字として認識するようです。
特定月を求める
特定の月だけ求めたいという場合は、他のセルに年と月を入力するようにするといいでしょう。
セルG1に年、セルG2に月を入力します。
開始年月日は、指定した年月の1日なので次の計算式で求まります。
=DATE(G1,G2,1)
終了年月日は開始年月日の翌月1日の前日ですので次の計算式で求まります。
=DATE(G1,G2+1,1)-1
この計算式をFILTER関数に埋め込みます。
=FILTER(A2:D16,(B2:B16>=DATE(G1,G2,1))*(B2:B16<=DATE(G1,G2+1,1)-1))
求めるデータ個数を規定する
上記のデータをチーズで検索すると8つのデータが出てきます。
でも実際には5つしか表示したくない場合があります。
Excelの10行目には別の何かのデータや文を入れたいという場合です。
そのような時に答えの出た範囲になにかを書き込もうとすると#SPILL!エラーが出ます。
FILTER関数は、データが検索された結果を際限なく、データがある限り出します。
しかし、それでは下のセルに何も書けないので、何か方法はないのでしょうか。
この場合、INDEX関数と、これも新たな関数なのですが、SEQENCE関数という連番を出す関数を使うとよいでしょう。
検索の元データは1列ずつにします。これはFILTER関数の便利なところなのですが、元のデータ範囲以外でも検索できます。
まず、A列に対する検索結果は次のINDEX関数とFILTER関数の組み合わせで行います。
=INDEX(FILTER(A2:A16,C2:C16=”チーズ”),SEQUENCE(5))
A列に対し、C列がチーズだったものを探し、そのリストの上から連番で5つ求めます。
あとは横にコピーすればOKなのですが、C列は固定にするので、今入れた計算式のC列を絶対参照に変更します。
=INDEX(FILTER(A2:A16,$C$2:$C$16=”チーズ”),SEQUENCE(5))
コピーすれば横に全部、縦に5つになりますね。
INDEX関数は1セルに求める関数で、それを5つにSEQUENCE関数で限定しています。
まとめ
いままで私も様々なExcelツールを紹介してきました。
そのためには大変複雑な計算式を組み合わせて使う場合も多いです。
しかし、新しいExcel関数を使えるようになると難しい仕組みがいらなくなります。
新しい関数にはFILTER関数の他に重複データを削除できるUNIQUE関数、並べ替えるSORT関数、SORTBY関数のように従来機能でしか実現しなったことも関数でできるようになります。
それによって、VBAが実用だった作業も関数でできるようになるので、全くVBAが不要にはなりませんが、ポイントを絞ったVBAの使いかたをすることができるようになるでしょう。
コメント