作者別: it-yobi

２つのキーから値を検索して抽出する場合、今までは、以下のように検索用の列を用意する必要がありました。

しかし、２０２１以降のバージョンに新たに追加されたFILTER関数を活用することで、上記のような検索用の列を用意する必要がなくなりました。
今回は、FILTER関数を活用して、検索用の列を用意せずに２つのキーから値を検索して抽出する方法について解説していきます。

紹介する方法は、数ある方法の中の１つになります。
他の関数を活用しても実現することができるので、色んな方法を試されると良いです。

FILTER関数の使い方は、以下のとおりです。

=FILTER(配列, 含む, [空の場合])
// 配列：抽出対象の範囲
// 含む：抽出条件
// [空の場合]：抽出対象が１件もない場合に表示する値（省略可）

FILTER関数で複数条件で指定する場合は、AND「*」、もしくは、OR「+」を活用します。

実際に、FILTER関数を用いて以下の表からキー１とキー２が一致する値を抽出していきます。

この場合は、抽出対象の範囲がD7～D12になります。
次に抽出条件が以下の２つになります。
・キー１：B7～B12の中でB3と一致するデータ
・キー２：C7～C12の中でC3と一致するデータ

実際に、FILTER関数の引数に割り当てることによって、以下のように抽出することができます。

=FILTER(D7:D12,(B7:B12=B3)*(C7:C12=C3),"なし")
// キー1とキー2が一致する場合になるため、それぞれの条件をAND「*」で並べる
// 抽出対象が１件もない場合は「なし」と表示する

※黄色のセルに数式を入力しています。

一致するデータが複数件ある場合は、セルから溢れ出て、以下のように表示されます。

補足

複数の連続した項目を抽出する場合は、スピル（セルから溢れ出して表示する機能）を活用することで、以下のように１つの数式で表現することができます。

=FILTER(D7:E12,(B7:B12=B3)*(C7:C12=C3),"なし")

以下のように、項目ごとに入力を切り替える必要がある構成の表があります。

こちらの表の場合は、通常、以下のように入力モードの切り替えを行います。

日付：半角文字
名前：全角文字
フリガナ：全角文字 or 全角カタカナ

この入力モードの切り替えを自動で行う設定方法について解説していきます。

このような設定は[データの入力規則]にて行います。
以下の表の日付の項目に対し、半角文字の入力を自動設定する方法について解説していきます。

まずは、設定対象の範囲を全選択します。
こちらでは日付の項目のみを選択していますが、他にも同じ設定をしたいセルがあれば、一緒に選択することで、一括で設定できます。

選択できましたら、[データ]タブより[データの入力規則]を選択します。

表示された設定画面にて、[日本語入力]タブを選択し、IMEの設定を行います。

デフォルトですと、「コントロールなし」が選択されているかと思います。
こちらを「オフ（英語モード）」にして確定することで、設定完了です。

同様に、名前の項目には「ひらがな」、フリガナの項目には「全角カタカナ」を設定します。

以上の設定を行うことで、選択しているセルに応じて、入力モードを自動で切り替えることができます。

以下のように、設定した最低発注数と発注単位によって、発注数のドロップダウンリストに表示する値を変更する方法について解説していきます。

行ごとでリストに表示させる内容を変更する場合は、リストに表示させる値を行ごとで求める必要があります。
例えば、リストに１１件表示させる場合は、以下のような一覧が必要になります。

(例1)
最低発注数：100
発注単位：10
リストの元となる一覧：100,110,120,130,140,150,160,170,180,190,200

(例2)
最低発注数：200
発注単位：50
リストの元となる一覧：200,250,300,350,400,450,500,550,600,650,700

このようなリストの元となる一覧を、行ごとに数式を用いて求めていきます。

まずは開始の値です。
開始の値に関しては、最低発注数を参照するだけで求めることができます。

=C3

この数式を必要な行数分コピーすることで、簡単に行ごとの開始の値を求めることができます。

次に、発注単位の値を加えた等差の一覧を、隣の列に表示させます。
求め方は、先ほど求めた値に発注単位を加えるだけです。

=G3+$D$3
// 発注単位のセルの列は固定する必要があるため「$」を加える

この数式を必要な行数、列数分コピーすることで、行ごとのリストの元となる一覧を作成することができます。

一覧の用意ができましたら、[データの入力規則]にてリストを設定していきます。

まずはリストを設定する対象の範囲を選択し、[データ]タブから[データの入力規則]を選択します。

表示された設定画面にて、[入力値の種類]を「リスト」にし、[元の値]にアクティブになっているセルに反映したいリストの元となる一覧の範囲を指定します。

セルを選択して範囲指定すると、以下のように絶対参照の数式が入力されます。

=$G$3:$Q$3

ただ、この数式に関しては、行ごとに参照する位置を変更する必要があるので、行番号の手前の「$」を外し、列のみを固定とした参照に修正します。

=$G3:$Q3
// 範囲選択後にF4キーを2回押すことでも入力可能

この設定内容で確定することで、行ごとに異なるリストを設定することができます。

申し込みフォームから取得したメールアドレスなどで、「重複を除いた件数」と「重複した件数」を求める方法について解説していきます。

・重複を除いた件数

まずは、以下の表の申し込みされたメールアドレスから重複を除いた件数を求めていきます。

重複を除いた件数は、UNIQUE関数とCOUNTA関数を組み合わせることで求めることができます。
それぞれの関数の使い方は、以下のようになります。

=UNIQUE(配列)
// 指定した表から行単位で重複を除外した表を返す
// 配列：対象の表
※実際には他の引数も存在し、他の引数を指定することで、列単位で重複を除外することも可能

=COUNTA(値1, [値2], [値3], …)
// 範囲内の空白でないセルの数を返す
// 値：対象の値、もしくは、対象の範囲
※[値2]以降は省略可能

まずは、UNIQUE関数を用いて、申し込みされたメールアドレスの重複を除いた一覧を取得します。

=UNIQUE(B:B)
// 今後追加されることを考慮し列全体を指定

次に、この取得した一覧の件数を、COUNTA関数を用いて求めます。

=COUNTA(UNIQUE(B:B))

ただ、この件数には、空白セルから取得された「0」と見出しの「申し込み」という値の件数まで含まれます。
そのため、COUNTA関数で求めた件数から「0」と「申し込み」を取り除いた件数、要するに「-2」した件数が重複を除いた件数になります。

実際に求めてみると、以下のようになります。

=COUNTA(UNIQUE(B:B))-2

・重複した件数

では次に、重複した件数を求めていきます。

重複した件数は、申し込みされたメールアドレスの件数から、実際の申し込み件数を引いた件数になります。
申し込みされたメールアドレスの件数は、COUNTA関数で求めることができます。
そのため、COUNTA関数の取得値と先ほど求めた重複を除いた件数を用いて、以下のように求めることができます。

=COUNTA(B:B)-1-D3
// 今後追加されることを考慮し列全体を指定
// 先ほどとは異なり「0」が含まれないため、見出し分の「-1」した件数から、重複を除いた件数を引く

以上のように、UNIQUE関数とCOUNTA関数を用いることによって「重複を除いた件数」と「重複した件数」を求めることができました。

以下のように複数のシートが存在するファイルの場合、先頭もしくは末尾のシートへ移動するのが大変のように感じます。

今回は、瞬時に先頭もしくは末尾のシートへ移動する方法について解説していきます。

その方法とは、Ctrlキーを押しながら「<」や「>」のアイコンをクリックするだけです。
それだけで先頭もしくは末尾のシートまでスクロールすることができるので、後は対象のシートをクリックすることで、そのシートを表示できます。

もしくは、「<」や「>」のアイコンを右クリックすることで、シートの一覧を表示することができるので、その一覧から対象のシートを選択することでも対象のシートを表示できます。

・補足

「<」や「>」の上にカーソルを移動させて数秒待つと、これらの方法が表示されます。

以下のように、灰色の見出しの３つの表を縦方向に結合し、重複を除いた表（緑の見出しの表）に抽出する数式の作成方法について解説していきます。

・複数の表を縦方向に結合

まずは、複数の表を縦方向に結合する方法から解説していきます。

複数の表（範囲）を縦方向に結合する場合は、VSTACK関数を活用します。
この関数の使い方は、以下になります。

=VSTACK(配列1, [配列2, 配列3, …])
// 指定した配列を縦方向に結合して返す
// 配列：結合対象の表（配列2以降は省略可能）

実際に活用して３つの表を結合すると、以下のようになります。

=VSTACK(B3:C6,E3:F7,H3:I6)

このように、指定した順番で表を結合して抽出することができます。

・表から重複を除外

次に、表から重複したデータを除外する方法について解説していきます。

先ほど結合した表には重複したデータが存在します。
以下の画像では、同じ色のデータが重複しています。

これらの重複を一括で除外する場合は、UNIQUE関数を活用します。
この関数の使い方は、以下になります。

=UNIQUE(配列)
// 指定した表から行単位で重複を除外した表を返す
// 配列：対象の表
※実際には他の引数も存在し、他の引数を指定することで、列単位で重複を除外することも可能

実際に先ほどのVSTACK関数の数式に組み合わせると、以下のように重複を除外した表を抽出することができます。

=UNIQUE(VSTACK(B3:C6,E3:F7,H3:I6))