カテゴリー別アーカイブ: 新ダイナミック関数

XLOOKUP関数を使用した複数ドロップダウンリストでの検索

複数ドロップダウンリストとの連動による検索

 今回は、新関数の特徴を活かして「複数のドロップダウンリストと連動した検索作業」をサクッと行う方法を解説します

 この検索方法が行えると、上のGIF画像にあるようなクロス表からの検索がグッと楽になります

「河合さんの数学の点数は?」「中島さんの国語の点数は?」

こんな検索もドロップダウンリストを2回操作するだけで行えます! 

従来であれば、複数の種類の関数を組み合わせなければこの検索は行えませんでした

 今回は、XLOOKUP関数を一度だけ組み合わせるだけで作成する方法を解説します

尚、XLOOKUP関数の基本的な使い方に関しては、過去の記事をご確認ください

今回解説に使用するデータ

今回、解説に使用するデータは、生徒毎の3科目の点数表です

 この表から、下の図の黄色に印を付けた箇所からリストの選択を行うだけで上の表から該当の点数を検索できるようにします

今回のポイント

今回は新関数の特徴を最大限に生かします

新関数の特徴は、1つのセルへの入力で複数の値を抽出できる点です

XLOOKUP関数も新関数ですので、複数の値を抽出できます

例として今回のデータを使用して、XLOOKUP関数を「複数の値」を抽出するように2つ設定してみます

①列方向に抽出

下の図をご覧下さい

 XLOOKUP関数の3番目の引数/戻り列を、1列だけでなく「C列~E列」指定しています

 戻り列を複数指定すると、下のGIF画像のように検索値の「伊藤さん」の全ての点数を抽出します

②行方向に抽出

 XLOOKUP関数のメリットの一つは、従来のVLOOKUP関数では検索できなかった方向にも検索が行えることです

①の列方向に抽出では、「伊藤さん」の点数を全ての科目について抽出しました

今回は、全ての生徒の数学の点数を抽出してみます

今度は、3番目の引数で「複数」を指定します

すると下の図のように、全ての生徒の数学の点数を抽出します

XLOOKUP関数の組み合わせ

では、前述の①と②を組み合わせて、検索を行う関数を完成させます

 列方向に抽出する①のXLOOKUP関数の3番目の引数に、②の行方向に抽出するXLOOKUP関数を組み合わせます

このように「行列」双方向のXLOOKUP関数を組み合わせるだけで、「 複数のドロップダウンリストと連動した検索作業 」を行う関数の入力が完成です

<まとめ>

 今回は、行列双方向に抽出するXLOOKUP関数を組み合わせて「複数のドロップダウンリストと連動した検索作業」を行う関数を解説しました

 今回解説した方法はXLOOKUP関数の2つの特徴、ⅰ)複数の値を出力、ⅱ)行列双方向で検索化、を組み合わせたものといえます

XLOOKUP関数にはまだまだ紹介していない魅力があります

また次回、XLOOKUP関数の魅力について解説します!

長文に最後までお付き合い頂き誠にありがとうございまいた

参考までに今回使用したファイルを添付します

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

RANDARRAY関数でテストデータ作成

 今回は、1つのセルへの関数入力でテストデータが作成できる「RANDARRAY関数」を紹介します

 旧関数でもRANDBETWEEN関数という関数があり、この関数でもテストデータは作成できました

今回紹介する「RANDARRAY関数」との違いは主に次の2つです

・整数以外も指定できる

・複数列でも指定できる

上に紹介した2つの違いを意識しながら、解説を行って行きます

RANDARRAY関数の基本

RANDARRAY関数の書き方(引数)は以下の通りになっています

=RANDARRAY(行,列,開始,終了,整数)

上に挙げた引数のすべてが省略可能となっているのが特徴です

〇省略した場合の設定

・行➡1

・列➡1

・開始➡0

・終了➡1

・整数➡FALSE(0と1の間)

例として、第一引数の行だけを「5」と指定してみます

そうすると、上のGIF画像のように5つ行方向に「0と1」の間の「数字」がランダムに出力されます

では、こちらを5つ目/最後の引数に「TRUE」を指定してみます

すると、0か1が5つ出力されます

では、0と1だけではなく「1から5」まで出力されるようにしてみましょう

3つ目と4つ目の引数にそれぞれ「1」と「5」を指定します

=RANDARRAY(5,,1,5,TRUE)

これで1から5の間で出力値を動かせるようになりました

ちなみに、F9キーで値は変動させることができます

複数列に値を出力

では、前述のRANDARRAY関数の2番目の引数に3を指定してみます

すると上の画像のように3列で出力されます

実践編

では、仮に生徒をつのクラスにランダムに割り当てるケースを考えて見ます

この場合には、第一の引数/行数にCOUNTA関数を設定して、人数分のランダムな数字が出力されるようにします(第三の引数/開始には3を設定)

=RANDARRAY(COUNTA(B:B),1,1,3,TRUE)

そうすれば、生徒数に応じて割当が行われます

なお、このケースは生徒数がある程度の数がいるケースを想定しています

生徒数が少ないと割り当てられないクラスが出る可能性があります

まとめ

今回は、RANDARRAY関数でテストデータを作成する方法を解説しました

今回は、COUNTA関数と組み合わせてサンプル数に合わせて出力するケースについても解説しました

実は他にも有効な関数の組み合わせ方法があるかもしれませんね

ぜひ研究して見たいと思います!

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

SEQUENCE関数で連続データ作成~組み合わせ活用~

 今回は、SEQUENCE関数という少しユニークな使い方をする新型関数について解説します

 このSEQUENCE関数は他の関数と組み合わせて使用すると、その便利さは無限に広がります

上のGIF画像では、1つのセル1つの関数を入力するだけでB2セルに入力されている「エクセル」の文字列を各セル別に分解しています!

では、基本的な使い方から解説をはじめます

基本的な使い方

SEQUENCE関数の書き方は次の通りです

 ちなみに[]の中は省略可という意味になっており、指定しなければ自動的に1が設定されます

=SEQUENCE(行数,[列数],[開始番号],[増分])

行数のみ指定

例えば、下のGIF画像のように行数を5とだけ指定すると、1~5の連続する数字が自動作成されます

行数に加えて列数等も指定

では次の様に、各引数を指定してみます

=SEQUENCE(2,3,4,5)

①行数:2

②列数:3

③開始:4

④増分:5 

 ①~④をSEQUENCE関数の引数に反映すると、次のような連続データができます

開始が4からはじまり、増分が5(例:4→9)になっています

そして行数は2、列数は3となっています

日付データ作成

では、今日(2021年5月27日)の日付を含めた10個の日付データを作成してみます

開始日には、=TODAY()を指定します

=SEQUENCE(10,1,TODAY())

上のGIF画像では、ただの連続データが作成されていますが、書式を日付に変更すれば下の画像のようになります

曜日データ作成

では、上の画像の日付データの横に曜日データを作成します

こちらは、WEEKDAY関数を各セルに入力するのではなく、下のGIF画像のように「#」を使って入力します

上のGIF画像では、=WEEKDAY(B2#)としてB2セルの次に#を付け足すことで、新型関数の抽出結果を再利用しています

上記の画像では、曜日が1~7の数字で表現されていますが、書式を「ユーザー定義」で下の画像のように「dddd」と設定すれば文字列の曜日で表示されます

上の画像のように連続した日付データを作成しておけば、下の画像のように「FILTER関数」の抽出条件式をOR条件で使用して、複数の好きな曜日の日付を抽出できます

上の画像では、抽出したい曜日をE2セルとF2セルに指定しています

ちなみに3は火曜日(日曜日が1)で5が木曜日です

*WEEKDAY関数の2番目の引数/省略可でどの曜日を1にするかは指定可

FILTER関数の抽出条件の設定(2番目の引数)についてはOR条件なので、2つの()の中に条件式を入れた上で「」でOR条件を指定します

=FILTER(B2#,(C2#=E2)+(C2#=F2))

文字列データの分解

記事の冒頭で紹介した、SEQUENCE関数を使用した「文字列の分解」について解説します

こちらはSEQUENCE関数をMID関数及びLEN関数と組み合わせて使用します

まず、話しを分かり易くするために「エクセル」の文字列が入力されたB2セルを操作対象として、MID関数を次の様に使用してみます

=MID(B2,1,1)

抽出結果は次の画像の通りです

上の画像では、MID関数の抽出開始位置を1としていますので「エ」が抽出されています

この2番目の引数の1を「エクセル」の文字列の長さまで変動させたらどうでしょう?

SEQUENCE関数を「=SEQUENCE(1,LEN(B2))」のように指定すると、次の画像のように1~4の文字列が行方向で作成されます

では、MID関数の2番目の引数に上のSEQUENCE関数を、次のように入れます

=MID(B2,SEQUENCE(1,LEN(B2)),1)

そうすると、次のGIF画像のように「エクセル」の文字列/4文字を5文字の「EXCEL」にかえても正確にセル毎に分割されます

<まとめ>

今回は、SEQUENCE関数という少しユニークな新型関数を紹介しました

連続データを作成するだけであれば、ホームタブに既に機能はあります

 このSEQUENCE関数は他の関数と組み合わせてみてはじめて、有効性を発揮します

 今回は、日付データと文字列データの分解の2つしか事例を紹介できませんでしたが、読者の皆さんの職場に色々とアイデアが落ちていそうですね

ぜひ、使用事例を連絡頂けると嬉しいです

長文を最後まで読んでくださり誠にありがとうございます

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

UNIQUE関数xFILTER関数から重複無リスト抽出~条件一致~

 FILTER関数UNIQUE関数などの新関数の醍醐味は「関数同士の組み合わせ」にあります!

 今回は FILTER関数UNIQUE関数 の組み合わせにより、ある表から条件に合う重複が無いリストを作成する方法を解説します

そして、最後はUNIQUE関数xFILTER関数の組み合わせを応用した内容も解説します

条件に合う重複無リスト作成

今回は、下の画像の左にある表から「70点未満の得点を一度でも取った人」リストを重複が無いように作成します

 抽出元の表には、田中さんや佐藤さんのように同じ名前の人のデータが複数含まれています

まずは設定条件/<70に沿ったFILTER関数を作成してみましょう

FILTER関数の書き方は次の通りです

=FILTER(抽出範囲,条件)

上の書き方を今回のやりたい事に当てはめると次の画像の通りになります

 上の画像では、まだFILTER関数しか入力していないので、リストに佐藤さんの名前が複数回登場しています

ですので、FILTER関数にUNIQUE関数を下の画像のように組み合わせます

これで、佐藤さんの登場は1回のみになりました

応用編

仮に、下の画像のように指定範囲を空欄にした場合には、下の画像のように「0」が関数の抽出結果に含まれてしまいます

この時には、FILTER関数の設定条件に「AND条件」を加えます

このANDで追加される条件は「>0」です

 この「AND条件」をFILTER関数に設定する場合には下の画像の黄色で囲まれた箇所のように「*」を使います

これで不必要な0が消えました

<まとめ>

 今回は、UNIQUE関数とFILTER関数を組み合わせて、条件に合った、重複が無いリストを作成しました

 この組み合わせを覚えると、今までピボットテーブル経由で取得していたデータが関数だけで取得できるのでとても便利です

ぜひ有効活用していきましょう!

 後、FILTER関数の設定条件に「*」を使用してAND条件で抽出する方法もぜひ覚えておきましょう

最後に、「+」を使用するとOR条件にて抽出できる旨も付け加えておきます

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

UNIQUE関数xSORT関数から集計表作成~#の魔法活用~

FILTER関数UNIQUE関数などの新関数の醍醐味は「関数同士の組み合わせ」にあります!

 今回は、UNIQUE関数とSUMIFS関数の組み合わせに加えて、「」を組み合わせて「集計表」を2つのセルへの関数入力だけで作成します

  本来であれば、「ピボットテーブル作成」を経由しなければならない「集計表の作成」の作業が格段に楽になります

 過去の記事でも#を有効活用した「検索付きリスト」の事例は紹介させて頂いていますが、今回は改めての使い方について解説させて頂きます

#とは?

 FILTER関数などの新関数は、1つのセルへの関数入力にて、複数のデータを抽出できるのが特徴です

「#」を使用すると、新関数で抽出されたデータを再利用できます

では、1つの事例で解説したいと思います

 下の画像のF2セルにはUNIQUE関数が入力されており、左にある表の「製品別」の列から重複しないデータを抽出しています

こちらのUNIQUE関数で抽出したデータを、を使って再利用してみます

 上の図ではI2セルに「=F2」と入力することでF2セルの内容を、I2セルに抽出しています

では、I2セルに入力した「=F2」の後に#を付けてみます

 上の画像のように「参照セル名」の後に#をつけると新関数のデータ抽出結果を再利用することができます

UNIQUE関数xSORT関数

 #について解説したところで、本題のUNIQUE関数を使用した集計表の作成に入りたいと思います

 まず、前述のようにUNIQUE関数にて製品別の列から重複しないデータを抽出します

更に、UNIQUE関数の抽出結果をSORT関数で並び変えることもできます

 上の画像のように、SORT関数の3つ目の引数を「-1」に変更して並び順を降順に変えることもできます

集計表の作成

 それでは、前述のUNIQUE関数で抽出したデータ毎に対して、左の表から「売上金額の集計金額」を紐づけます

 上の画像では、UNIQUE関数で抽出した「乗用車」に対して、SUMIFS関数を使用して左の表から「乗用車」の売上金額を集計しています

 では、上の画像のSUMIFS関数の3つ目の引数「乗用車」のテキストが入ったF2セルの指定に対して前述のを組み合わせます

これで、1つのSUMIFS関数の入力で集計表の1列が完成しました

<まとめ>

 今回は、UNIQUE関数xSORT関数の組み合わせに、更に#を組み合わせて集計表を完成させました

#を使えば、新関数の抽出結果を再度利用することができます

エクセル進化は本当に凄まじいですね

2つのセルへの関数入力だけで集計表が完成できるのは大きいです

この機会に新関数x#の組み合わせは有効活用できるようにしておきましょう!

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

UNIQUE関数~重複排除~

  今回は、1つのセルに関数を入力するだけで重複排除したリストが取得できる、「UNIIQUE関数」について解説します

尚、今回の解説で使用するデータは次の画像になります

上の画像のデータは、既にテーブル化してあります

ですので、関数の引数(中味)の指定については、過去の記事で解説した「テーブルとの連携」により指定する方法で解説します

詳細を知りたい方は過去の記事をご確認お願いします

1つの列での重複排除

このUNIQUE関数の書き方は至ってシンプルです

対象となるデータ範囲を指定するだけです

=UNIQUE(対象範囲)

今回はテーブルと連携するので、下のGIF画像のように対象範囲を指定します

上のGIF画像では、対象範囲を指定する際に、年度の列にてが出ていることにご注目ください

複数の列での重複排除

UNIQUE関数で複数の列を対象範囲に指定すると、指定された列の組み合わせの中から、重複しない組み合わせを出力します

行方向での重複排除

UQNIQUE関数の2つの引数を「TRUE」と指定すると、行方向での重複排除を行うことができます

1回登場する組み合わせを抽出

下の図のようにUNIQUE関数の3番目の引数を「TRUE」と指定した場合には、1回登場する組み合わせのみが抽出されます

=UNIQUE(Data[[年度]:[地域]],,TRUE)

下の図のように、画像左側の「TRUE」を指定していない場合と比較してみましょう

3番目の引数をTRUEと指定した右側では、1回登場している組み合わせのみが抽出されているので、左側より行数が少なくなっています

例えば、元のデータの黄色に印を付けた箇所は複数登場しているので、UNIQUE関数で抽出されていないのです

テーブルと連携しないケース

テーブルと連携しないケースでは、下の図のように対象範囲空欄が入る場合があります

この場合には、UNIQUE関数の出力に0が含まれます

この場合には、FILTER関数を使用して「空欄以外」の値を抽出するようにする方法があります

下の図のように、FILTER関数の抽出条件を「<>””」とします

こうすれば、0は出力されません

まとめ

今回は、UNIQUE関数により重複排除したリストを出力する方法を解説しました

省略可能ですが、2番目の引数をTRUEとすれば行方向での重複排除も行えます

最後にFILTER関数と連携して「0」を出力しないようにする方法も解説しました

 今回は他の新関数との組み合わせについては1パターンしか解説しませんでしたが、このUNIQUE関数の醍醐味は他の関数との組み合わせです

次回からは他関数との組み合わせについて解説を行って行きます!

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

SORT関数~関数1つでデータを並べ替え~

前回は、新ダイナミック関数の中でも一番代表的なFILTER関数を紹介しました

今回は、並べ替えを行う新ダイナミック関数である、「SORT関数」を紹介します

このSORT関数で行う並べ替えは、関数でなくても他の機能で行えます

それでも、このSORT関数を活用する大きなメリットが2点あります

SORT関数を使うメリット

組み合わせ活用

前述のように、並べ替えならば他の機能で行えます

SORT関数の醍醐味は他の関数との組み合わせです

・FILTER関数でデータ抽出 ➡ SORT関数で並べ替え

・SORT関数で並べ替え➡SUMIFS関数で並べ替えられたデータを集計

こちらの組み合わせ方法については、また別な記事で解説します

列の並べ替え

SORT関数は列方向でも並べ替えが行えます

今までのエクセルの並べ替え機能でも、列の並べ替えが行えます

今までの並べ替え機能との違いは、データ自体を並び変えるのではなく、他の箇所に出力されたデータを並び変える点です

SORT関数のメリットを2つ解説したところで、本格的な解説を始めます

通常の並び替え/基準1つ

今回使用するデータは次のデータです

まずは、上のデータの「年度」列を昇順で並び変えてみましょう

SORT関数の書き方

SORT関数の書き方は次の通りです

=SORT(①範囲 , ②基準, ③順序)

①範囲➡並び替え範囲(テーブルを範囲として使用可能)

②基準➡並び替えを行う列位置を1から始まる数字で指定

③順序➡1:昇順、-1:降順

並び替えの実際

並び替え範囲を「Data」というテーブル名にてテーブル化した上で、実際にSORT関数の並び替えを行います

左から1列目を基準に昇順で並び替え

この場合、SORT関数は次の通りに指定します

=SORT(Data,1,1)

すると、次のGIF画像の通りに並び替えが行われます

通常の並び替え/基準2つ

並び替えの基準が2つの場合には、前述のSORT関数の書き方が違ってきます

=SORT(①範囲 , ②基準, ③順序)

基準が2つの場合には、②基準及び③順序ともに書き方の工夫が必要です

波括弧を使用して、2つの基準と順を書けるようにします

・例1:②{1,1}、③{-1,1}

・例2:②{1,4}、③{1,-1}

ここで注意点ですが、2つの基準ともに昇順(降順)の場合でも、③は波括弧/{}で指定します

並び替えの実際

データは前のデータをそのまま使用します

画像に alt 属性が指定されていません。ファイル名: image-44.png

今回は、②の基準を「年度」と「売上金額」の2つにします

そして、年度は昇順にて並び替え、売上金額は降順で並び替えます

この場合の②と③の書き方は次の通りです

・②基準➡{1,4}

・③順序➡{1,-1}

では、実際に並び替えを行います

列の並び替え

列の並び替えを行う場合には、4つめの引数を指定します

=SORT(C2:P5,4,-1,TRUE)

4つ目の引数に「TRUE」を指定するとの並び替えとなります

今回使用するデータは次のデータです

こちらのデータにて、「売上金額」の行を降順で並び替えます

上のGIF画像における数式は次の通りです

=SORT(C2:P5,4,-1,TRUE)

まとめ

今回は、並び替えを行うSORT関数について解説しました

改めて書き方を解説すると、次の通りになります

=SORT(①範囲 , ②基準, ③順序)

特に問題となるのは、並び替え基準が2つになるケースです

この場合には、②と③の両方を波括弧/{を使用して指定します

 記事の冒頭でも前述したように、SORT関数の醍醐味は他の関数と組み合わせ易い点です

この組み合わせについては、後日紹介したいと思います

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

Filter関数~応用編~

 前回は、Filter関数の基本だけを解説しましたが、今回はFilter関数を応用して活用する方法を解説します

今回の内容としては主に次の2つです

Ⅰ.テーブルとの連携

とにかく表を見たらまずはCtrl+Tをしましょう

Ctrl+Tをして、表をテーブルにしておけば、関数の範囲もしくはをクリック一つで指定できるようになります

Ⅱ.抽出条件の複雑化(AND条件、OR条件)

AND(且つ)条件やOR条件(また)で抽出条件を指定できるのが、Filter関数の醍醐味です

今回も前回と同じデータを使用して解説します

まず、「テーブルとの連携」では前回と同じ様に、上のデータから製品別の列にて抽出条件を設定してデータを抽出します

次に製品別がⅰ)「乗用車」且つ売上金額が「50,000」超のデータとⅱ)「乗用車」または「50,000」超のデータを抽出します

テーブルとの連携

テーブル化

まずは表の上にカーソルを置き「Ctrl+T」を押します

すると、表はテーブルとして構造化されます

ちなみに、表がテーブルになると右下に次のような印が付きます

テーブルには、「Data」という名前をつけておきます

テーブル名は、テーブルデザインタブで変更が行えます

範囲及び列指定

では、まずはFilter関数の第一引数の範囲を指定します

テーブル全体を引数として指定するには、テーブルの左上で次のGIF画像のように指定します

上のGIF画像の左上に出てくる「右下向きの↘」に注目してください

上の画像の赤印の位置にカーソルを置くと、右下向きの↘が出てきますので左クリックします

そうすると、Filter関数にテーブル範囲が設定されます

これでFilter関数の第一引数である範囲が設定されました

次に第二引数である抽出条件の列を指定します

下のGIF画像のように、製品別の列の上で指定します

下の画像の赤印の箇所にカーソルを置くと、下向きの↓が出てきます

下向きの↓が出てきたら、左クリックをします

すると、第二引数が設定できます

もし、上の方法でうまくカーソルにて引数を設定できない場合には、下の表記の通り手動で入力を行ってください

範囲➡テーブル名

列➡テーブル名+[列名]

 *”[”角括弧になります

AND条件の設定

前述の通りⅰ)「乗用車」且つ売上金額が「50,000」超のデータを抽出します

AND条件を設定する場合には、抽出条件を次のような形で設定します

(条件1) * (条件2)

四則演算の掛け算と同じように「*」を使います

上の画像の数式をテキストにすると次のようになります

=FILTER(Data,(Data[製品別]=F2)*(Data[売上金額]>G2))

上記の数式を設定すれば下の画像のようにデータが出力されます

OR条件の設定

今度はⅱ)「乗用車」または売上金額が「50,000」超のデータを抽出します

AND条件を設定する場合には、抽出条件を次のような形で設定します

(条件1) + (条件2)

四則演算の足し算と同じように「+」を使います

上の画像の数式をテキストにすると次のようになります

=FILTER(Data,(Data[製品別]=F2)+(Data[売上金額]>G2))

上記の数式を設定すれば下の画像のようにデータが出力されます

<まとめ>

 今回は、Filter関数にて引数をテーブルと連携して設定する方法、及び、抽出条件をANDもしくはORで設定する方法について解説しました

いずれの方法も、これまでの関数の常識では考えられなかった処理です

何よりデータを大量に扱う人にとっては、とても実用的な処理だと思います

このブログでは、これから他の新ダイナミック関数の使い方も紹介していきます

 Filter関数は他の新ダイナミック関数と組み合わせて活用することができますので、そちらの方法についても順次解説を行って行きます

今回は以上です

最後まで記事を読んで下さり誠にありがとうございました

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

Filter関数~複数行を抽出~

 Office365のバージョンから新たなコンセプトの関数が使えるようになっているのをご存知ですか?

 新たなコンセプトの関数では、1つのセルへの関数の入力で、複数の値が抽出できます

 このブログではこの新たなコンセプトの関数を新ダイナミック関数と呼ぶことにします

 この新ダイナミック関数を使いこなせば、データの集計にかけていた時間を大幅に削減することができます

 この回では、新ダイナミック関数の中で一番代表的な「Filter関数」について紹介します

この回で解説するは「Filter関数」の基本的な使い方のみです

次の回では、テーブル機能と連携して引数を設定する方法、及び、抽出条件をAND条件やOR条件で抽出するを紹介します

ぜひ、そちらも楽しみにしていてください

Filter関数とは

Filter関数は次の様な文法で書きます

=Filter(①抽出範囲 , ②抽出条件 , ③該当するものが無い場合[省略可])

①の抽出範囲から、②の抽出条件に該当するものを集計します

これまでの関数とは全く違うイメージの関数なので、早速、実際の使用例を見てFilter関数の便利さを体感してみましょう!

Filter関数の使い方・実際

使用データ

Filter関数を使用するデータは次のデータになります

上のデータから「製品別」が「トラック」のデータだけを抽出します

集計

範囲指定

まず、抽出範囲を指定します

上の画像の段階では、数式は次のようになっています

=FILTER(A2:D31

3つ目の引数の「該当するものが無い場合」については、ここでは省略し、後ほど別途解説します

抽出条件

次に、抽出条件を指定します

この抽出条件は、次の画像の黄色の箇所のようにを丸ごと指定するのがポイントです

では、ここで”)”で上の画像の数式を閉じて、Filter関数を確定させます

Filter関数はF5セルに入力しただけですが、抽出範囲から「製品別」の列の内容が「トラック」に該当する「全ての行」が抽出されます

該当するものが無い場合

Filter関数で3番目の引数「該当するものが無い場合」を指定して見ましょう

ちなみに、既に作成したFilter関数をそのまましておき、抽出条件を該当するものが無い「EV」にすると次の画像のようになります

では、3番目の引数を次のように「該当なし」と設定してみます

=FILTER(A2:D31,B2:B31=F2,”該当なし”)

すると、次のように出力されます

使用上の注意点

新ダイナミック関数の全般に言えることですが、関数の出力範囲のセルに「なにがしかの入力」があると次の画像のようなエラーがでます

入力されたフォントの色が白になっていて、入力されているように見えなくてもこのエラーは出ますので注意が必要です

<まとめ>

Filter関数は前述の通り、これまでと全く違ったコンセプトの関数です

 発想を変えれば、今までならVBAなどを使って処理するものでも関数だけで処理が行えます

 このブログでは、Filter関数を他の関数と組み合わせて使用する事例も紹介しています

 例えば、シート名を抽出できるCELL関数と組み合わせて、1つのデータをシート別に振り分けたりすることもできます

 上のGIF画像では、Filter関数が既に入力されているシートをコピーし、シート名を「CCC」というシート名に変更しています

 そうすると、自動的に「カテゴリー」が「CCC」のデータが元のデータから自動抽出されています

 次回は、Filter関数の応用として「テーブル連動」や「AND条件やOR条件でのデータ抽出」を行います

記事を最後まで読んで下さり誠にありがとうございました

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村