スプレッドシート 条件付き書式 複数条件でのカスタム数式 データベース使用での設定方法

Question

前提・実現したいこと

3つのショップで仕入れた商品を管理している一覧表で、
各ショップで販売できない商品名に色を付けて警告したい。

データベースには各ショップでのNG商品コードが入っており、
それぞれのコードを”あNG”、”いNG”、”うNG”と名前付き範囲を設定している。
”あNG”は時期によって変わる場合があるのでチェックボックスが付いている。

セルA　商品名
セルB　商品コード
セルC　ショップ名

①　セルCがショップ「あ」で、更にセルBが名前付き範囲「あNG」のチェックされている商品と一致する場合セルAを赤にする
②　セルCがショップ「い」で、更にセルBが名前付き範囲「いNG」の場合セルAを青にする

以上の条件付き書式を設定したい。

=countif(indirect("いNG"),B2)

で、「いNG」リストにあるものに色をつけることができたが、更にセルCが「い」の場合という条件を追加する方法がわかりません。

また、以上の条件に「あNG」のチェックが入っているものだけという条件付けができるのかもわからない状態です。

試したこと

たくさん調べて、色々組み合わせてみましたがうまくいかず困っております。

補足情報

https://docs.google.com/spreadsheets/d/1tTdApA2UhN_P_9s0i3EGAzNmPHne0NJpAR4hebBWdaM/edit?usp=sharing

こちらにテスト用のシートを用意してあるので、お使いいただいて構いません。

Answer 1

条件付き書式で、COUNTIFやIFと掛け算(*)を組み合わせることで、ANDと同じ演算を行うことが可能です。

TRUE(1)*TRUE(1)=TRUE(1)

TRUE(1)*FALSE(0)=FALSE(0)

FALSE(0)*FALSE(0)=FALSE(0)

たとえば
=countif(indirect("いNG"),B2)*(C2="い")
とすると、
範囲"いNG"にB2セルと同じ値のセルが１個以上存在し、かつ
C2が「い」であれば、TRUEと評価されます。

具体的な設定手順は下記のようになります。

---

１．「一覧表」シートA列の条件付き書式のカスタム数式を

=countif(indirect("いNG"),B2)*(C2="い")

に変更し完了ボタンを押します。

２．「データベース」シートのタブをクリックし、
「データベース」シートのC列をクリックして列選択。
メニューの**「挿入」->「列」->「左に１列挿入」**を選択して、
C列の左隣に1行列を挿入します。

３．新たに作られたC列のC3セルに
=if(B3,A3,"")
という数式を入力し、4行目以降はその数式をコピーします。
（C4セルは =if(B4,A4,"")、C5セルは =if(B5,A5,"")...以下同様、となるように）

４メニュの「ツール」->「名前付き範囲」で、
「あNG」の範囲を
「'データベース'!C3:C7」に変更し完了ボタンを押します。

５．「データベース」シートのC列を右クリックし「列を非表示」を選択して、C列を非表示にします。

６．「一覧表」シートのタブをクリック。
「一覧表」シートのA2セルをクリックし、
メニューの「表示形式」->「条件付き書式」を選択します。

７．右側サイドバーに表示された「条件付き書式設定ルール」の「＋条件を追加」ボタンをクリックします。

８．「範囲に適用」に「A2:A19」と入力。
「書式ルール」の「セルの書式設定の条件...」ドロップダウンで「カスタム数式」を選択し

=countif(indirect("あNG"),B2)*(C2="あ")

という数式を入力します。

「書式設定のスタイル」で、赤色塗りつぶしを設定し、完了をクリックします。

---

上記を設定したスプレッドシートのリンク
https://docs.google.com/spreadsheets/d/1CKN1lYkF0JIfhzdAYVh96IpSGZpvW7TvrFCGVOe6NsM/edit?usp=sharing

Comments

[ETwork]

ありがとうございます（T-T)
やりたかったことが完璧にできました！！

単純な計算式程度しかやったことのない初心者ですが、色々調べながらやってもできなかった事が解決していただけて大変感謝しております。

自分でももっと勉強して、色々応用できるよう精進致します。

本当にありがとうございましたm(__)m