スプレッドシートで条件に応じて表示させる値を変更する方法

こんにちは、わくほこ(@wakuhoko)です。

今回はスプレッドシートの値が変わったときにそれに応じて表示する値を変化させる方法をご紹介します。

結論、QUERY関数とFILTER関数を使います。

あ、あと正直GAS(GoogleAppsScript)を使ったほうが楽です。

でもGASを使えない状況もあると思うので、そんなときは本記事を参考にしてみてくださいね〜！

あわせて読みたい

【コピペOK！】GASでお問い合わせメールをスプレッドシートに自動転記する方法 こんにちは、わくほこ(@wakuhoko)です。 最近Google Apps Script（GAS）の勉強をしています。 せっかくなのでブログに使えるものを作りたいと思い、お問い合わせ内容が…

今回作成する機能

今回は以下のサンプルシートを使って実装します。

サンプルシート

リンクをクリックするとダウンロードされるので、お使いになりたい方はどうぞ。

Excelだと数式やレイアウトが崩れます。一度ダウンロードした上でスプレッドシートにインポートしてお使いください。

想定しているイメージは、以下になります。

営業部が使用しているmasterデータを内勤部署にも共有&データの絞り込みを簡単に実施できるようにする

実装したい内容は以下の通りです。

スプレッドシートの構成

まずは今回使用するスプレッドシートの構成を確認しましょう。

構成は以下の通りです。

samplesheet_wakunkyo
├─master（営業部データ）　絞り込み対象となるデータ。営業部から共有されることを想定
├─運営会社　サービスの運営元の情報を表示するシート。masterシートから情報を取得
├─サービス名　サービスの詳細情報を表示するシート。masterシートから情報を取得
├─運営会社マスタ　運営会社のマスタデータ。定期的に情報が変わることを想定&内勤部署で使用している想定

スプレッドシートの中に4シートがあり、

の2シートはmasterシートの情報を参照しています。

条件を変更すると、masterシートから取得する情報が自動的に変わるようにします。

実際に機能を実装する方法

さて、それでは詳しく見ていきましょう。

以下の画像は、運営会社シートの内容です。

構成は以下のようになっています。

機関選択

C列はプルダウンになっており、機関名が選べるようになっています。

条件に一致するmasterシートのデータがA3行目〜に表示される仕様です。

日付選択欄

E列では日付が選択でき、この日付はmasterシートの「運営日」と比較しています。

「佐藤製作所」の「運営日」が2024/04/26日以降のデータがmasterシートに存在しないため、画像では「#N/A」が表示されています。

該当するデータがなかった場合、「#N/A」が表示されないようにする方法は後ほどご紹介します。

電話番号欄

電話番号欄は運営会社マスタから参照しています。

運営会社マスタの電話番号とmaster（営業部データ）が一致していることを確認します。

内勤部署で使っているデータと営業部で使っているデータが合っていることを確認するためです。

コードの内容

ここまで確認したので、いよいよコードを見ていきましょう！

今回、あえて

の2種類を用意しました。

電話番号欄がないほうは2通りの方法があるので、両方ご紹介できればと思います！

電話番号欄がないシート（サービス名シート）

電話番号欄がないシート（サービス名シート）は、

の2通りの方法で情報を取得できます。

FILTER関数のほうがシンプルですが、複雑な条件にも対応できるのはQUERY関数なので、状況によって使い分けていただけると良いかと。

QUERY関数

まずはQUERY関数の式についてです。

=QUERY('master（営業部データ）'!A:F,"SELECT * WHERE C = '"&C1&"' AND B >= DATE '" & TEXT(E1, "yyyy-mm-dd")&"' ",0)

サービス名シートのA3に上記を設定しています。

つまりこの式は、master（営業部データ）シートから

という条件に一致する行を抽出しています。

FILTER関数

サービス名シートはFILTER関数も使えます。

多分こちらのほうがシンプルです。

=FILTER('master（営業部データ）'!A:F,('master（営業部データ）'!C:C=C1) * ('master（営業部データ）'!B:B >= E1))

ここでのポイントは「*（アスタリスク）」を使っていること。

アスタリスクを使って式を繋げています。

こうすることで、すべての式の条件に合致する値だけを取得することができます。

もちろん結果はQUERY関数を使った時と同じです。

↓内容はQUERY関数のときとほぼ一緒ですが、一応解説。

つまりこの式は、master（営業部データ） シートから

という条件に一致する行を抽出しています。

電話番号欄があるシート（運営会社シート）

電話番号欄があるシート（運営会社シート）では、FILTER関数を使い、QUERY関数は使いません。

もしかしたらQUERY関数を方法もあるかもですが、わたしの検証ではFILTER関数しか使えませんでした。

FILTER関数

まず、運営会社シートのJ1に以下を設定しています。

=VLOOKUP(C1,'運営会社マスタ'!D:H,5,false)

C1（機関名）をキーにして運営会社マスタから電話番号を取得しています。

その後、以下の式を運営会社シートのA3に設定します。

=FILTER('master（営業部データ）'!A:F,('master（営業部データ）'!C:C=C1) * ('master（営業部データ）'!B:B >= E1) * match('master（営業部データ）'!F:F,J1))

つまりこの式は、master（営業部データ）シートから以下の条件に一致する行をフィルタリングします。

補足：電話番号を直接指定しない理由

ここで、

何でわざわざ式を設定するの？
運営会社マスタから直接値を指定すれば良いのでは？

と思った人もいるのではないでしょうか？

鋭いですね！

実はFILTER関数は行の長さを抽出列と条件列で揃える必要があるのです。

今回の例でいうと、運営会社シートは10行目までなのに対し、運営会社マスタは100行目まであります。

これだと行が一致していないため、FILTER関数ではエラーになるんですね。

行を揃えられるなら電話番号を直接指定すればOKです。

ただし、今回のように運営会社マスタのようにシートが定期的に更新される可能性がある場合はこの方法は使えません。

そこで、一旦運営会社マスタの電話番号を取得してその値を使う仕様にしています。

エラーメッセージを表示させたくないとき

さて、今の式だと該当する値がないときに「#N/A」が表示されてしまいます。

エラーメッセージを表示させたくない場合は、以下のようにIFERROR関数を使います。

（以下は例）

=IFERROR(FILTER('master（営業部データ）'!A:F,('master（営業部データ）'!C:C=C1)*('master（営業部データ）'!B:B >=E1)*match('master（営業部データ）'!F:F,J1)),"")

式全体をIFERROR関数で囲めばOKです。

最後の””に文字を入れれば、該当する値がないときにその文字が表示されます。

=IFERROR(FILTER('master（営業部データ）'!A:F,('master（営業部データ）'!C:C=C1)*('master（営業部データ）'!B:B >=E1)*match('master（営業部データ）'!F:F,J1)),"該当なし")

終わりに

今回はスプレッドシートで条件に応じて表示させる値を変更する方法をご紹介しました！

使っていて思いましたが、QUERY関数はめっちゃ便利ですね。

勉強用にQUERY関数のパターンを用意しましたが、今回の実装ではどちらもFILTER関数のみで実現可能です。

ただ、やっぱりGASを使うほうが手っ取り早いとは思います。

「GASを使ってみたい！」「GASを勉強してみたい！」という方はぜひ以下の記事も読んでみてください！

あわせて読みたい

【コピペOK！】GASでお問い合わせメールをスプレッドシートに自動転記する方法 こんにちは、わくほこ(@wakuhoko)です。 最近Google Apps Script（GAS）の勉強をしています。 せっかくなのでブログに使えるものを作りたいと思い、お問い合わせ内容が…

ではでは〜