【スプレッドシート】プルダウンで複数条件を指定すると、条件に合致するデータが表示されるようにしてみた
¡Hola! マイコです。
同僚の依頼で「複数条件に合致するデータを表示する。条件はプルダウンでぽちぽち指定可能」なスプレッドシートを作ってみたところ、好評だったので記事にします。
今回は、裏側の関数としては超便利と巷で噂の（？）INDEX関数とMATCH関数の組み合わせを利用しました。
※ここからのデータや各名称はすべてダミーです
作ったもの
自社データベースにはユーザ情報が登録されており、各ユーザには業種、職種、企業規模、役職の4つの属性情報が付与されています。
この4つの属性についてプルダウンで要素を選択し、その掛け合わせを満たすユーザ数を表示できるようにしました。
このようにして4つの属性情報を選択すると、その組み合わせを満たすユーザ数が同じシート内に表示されるようにしました。図ではA6とB6セルに該当します。
食品業界、マーケティング職、大規模、マネージャーを満たすユーザが100人いる図です。
プルダウンを開くと、各属性の要素名がリストになっています。
作り方
1️⃣データを準備する
自社データベースから、業種、職種、企業規模、役職の全ての組み合わせについて、ユーザ数、アクティブユーザ数を計算し、抽出します。
Metabaseで抽出したところ、だいぶ計算に時間がかかっていました。
2️⃣データシートを作成する
1️⃣で抽出したデータを、スプレッドシートの「データ」シートにコピーしておきます。
このシートが崩れると全てが機能しなくなるので、あとで非表示にしておきます。
3️⃣計算シートでプルダウンメニューを作成する
プルダウンで4つの属性情報を選択できるようにします。
ここではAからDの2行目に属性名、3行目に各要素が含まれたプルダウンを作っています。
挿入＞プルダウンを選択肢、一つ一つデータベースで使用しているのと同じ名称を入力していきます。このあとの関数内で用いるため、表記揺れなく作るのが重要です。
色はお好みでつけてあげます。
これでDONEをクリックすると、プルダウンができます。
UIができました。ここから関数を作ります！
4️⃣INDEX関数とMATCH関数の組み合わせで検索する
INDEX関数でデータを取得する範囲を指定し、MATCH関数でその範囲内の特定の条件に一致する行または列の位置を見つけることができます。
急がば回れということで、基本の確認。
・INDEX関数
基本の使い方：INDEX(範囲, 行, [列])
・MATCH関数
基本の使い方：MATCH(検索値, 範囲, [検索タイプ])
例えば範囲 A1:C10 内で "Apple" が見つかった行の2列目の値を返すためには次のように書けば良い。
=INDEX(A1:C10, MATCH("Apple", A1:A10, 0), 2)
さて本題に戻り、A6セルにはユーザ数、A7セルにはアクティブユーザ数を返します。
ユーザ数は2️⃣で作った「データ」シート内のE列、アクティブユーザ数はF列にあります。
A6セルに「データ」シート内の特定の列（A列、B列、C列、D列）から条件に一致する行を見つけ、その行のE列（ユーザ数）の値を表示します。
少し長いですが、次の関数を書きます。
=INDEX('データ'!E:E, MATCH(A3&B3&C3&D3,'データ'!A:A&'データ'!B:B&'データ'!C:C&'データ'!D:D, 0))MATCH関数で、現在のシートのA3、B3、C3、D3セルの内容を結合し（A3&B3&C3&D3）、「データ」シートのA、B、C、D列を同様に結合した範囲内で完全一致する位置を探す。
MATCH関数は、一致する位置（行番号）を返します。
その行番号をINDEX関数に渡し、「データ」シートのE列から対応するデータを取得します。
同じ考え方でアクティブユーザ数も検索できるようにしました。
問題なく動作しているのを確認したら、完成です！