タグ別アーカイブ: ダイナミック関数

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へ


にほんブログ村



SORT関数によるエクセルデータの並び替え~列の並び替えも可

前回は1つの関数に対して複数の出力がある関数(ダイナミック関数)として、FILTER関数を紹介しました

今回は、同じようにダイナミック関数のひとつである、SORT関数を紹介します

エクセルで大量のデータを扱う時に、注意しなくてはいけないことの一つが並べ替えです

間違って並び替えると、列間で食い違いが生じて大規模なミスにつながります。SORT関数では並べ替えのキーが複数あっても、正確に且つ、簡単に並べ替えの処理が行えます

加えて、SORT関数では列の並べ替えが行えるのが大きな利点です!

以前、列フィルター機能を紹介しましたが、従来のエクセルでは列の扱いが困難だったのです

では、今回は、SORT関数の概要だけ紹介します!

1.複数の条件による並び替え

=SORT(並び替え範囲,{列位置,列位置・・・},{並び替え方法,並び替え方法・・・})

並び替え範囲/引数1を指定した後、並び替えの条件になる列の位置/引数2(例:{2,3})を指定し、更に並べ替え方法/引数3を指定します(例:{1,-1})

並べ替え方法/引数3で指定する1、-1とは、<1が昇順>、<-1が降順>の意味にになります

ちなみみに、引数2と引数3は指定の仕方がセットになっています

前述の例で言えば、引数2を{2,3}と指定し、引数3を{1,-1}と指定したので

・引数2の1番目(2)は引数3の1番目(1)とセット

・引数3の2番目(3)は引数3の2番目(-1)とセット

となります

実際の例として、以下の表/テーブル2を並べ替え範囲として、地域(列:2、昇順:1)、売上金額(列:3、降順-1)を並び変えてみます

元データ-テーブル2

数式は以下の通りとなります

SORT関数による並べ替え

SORT関数事例
SORT関数

2.列の並び替え

エクセルは行フィルター機能があるのが便利なのですが、業務によっては在庫表のようなクロス表形式で作業することがあります

以前から、列の並び替え機能もあると便利だとは思っていました

列の並び替えを指定する場合には、 前述の「1.複数の条件による並び替え」 で指定した3つの引数に加えて、4つ目の引数【列方向で並び替えを行う】をTRUEで指定する必要があります

ちなみに、「1.複数の条件による並び替え」にて、列位置で指定した 引数2は行位置で指定します

実際の例として、以下の表の3行目を並べ替え条件に設定し、降順にて列の並べ替えを行います

以下が数式です。4番目の引数をTRUEで指定しています

列並び替え
SORT関数ー列並び替え

但し、列の並び替えができるといっても元データ自体を並び替えできるわけではありません

利用機会を増やすには、別シートで並び変えた内容を編集して表示するなどの工夫が必要になります

ちなみに、FILTER関数と同じく、関数を入力したセル位置に「#」をつけることにより、出力データの再利用は行えます

下のGIFでは、SORT関数が入力された位置(F12)に#をつけることにより、表の右横にSORT関数の出力データを再出力しています

SPILL再利用
#によるデータ再利用

<まとめ>

今回はSORT関数により、データの並べ替えを行う方法を解説しました

SORT関数では、{}の文字を組み合わせることにより、複数の並べ替え条件に1(昇順)、-1(降順)をセットで指定することができます

後、SORT関数では列の並び替えを指定できるという特徴があります

こちらは使い方を研究して見て、ぜひ日常業務の中で活用してみてください

尚、実際のデータでSORT関数の動きを確認したい方は以下のファイルをダウンロードしてください!

アイコン

SORT関数 18.85 KB 4 downloads

...

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

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

にほんブログ村