Excelの条件付き書式で、複数セルの条件が揃った時のみ色をつけるルールの作成

Question

前提・実現したいこと

Excelにて実現したいことがあり、質問させていただきます。

sheet1のようにグループごとに番号と名前があり、更にグループ内で個別に番号が振られている時、
sheet1のC列のグループNo.が同じすべての行のF列が「〇」になった場合に
sheet2の該当グループNo.の行に色をつける

ということを実現したい為、方法をご教授いただけないでしょうか。

例えば、下記のような場合このように色がつくことを目指しています。

試したこと

おそらく、条件付き書式でルールを追加することによって実現できるのかと思うのですが、
このように複数のセルの条件が揃った場合のやり方は、調べてもヒットしませんでした。

また、実際に使用したい管理表の情報量がかなり多いため
グループごとにルールを作成するのではなく、表全体にルールを作成したいと考えています。

よろしくお願いいたします。

補足情報（FW/ツールのバージョンなど）

Excel2016

Answer 1

こんな感じでどうでしょうか。

=COUNTIFS(Sheet1!$B$3:$B$14,Sheet2!$C3,Sheet1!$C$3:$C$14,Sheet2!$D3)-COUNTIFS(Sheet1!$B$3:$B$14,Sheet2!$C3,Sheet1!$C$3:$C$14,Sheet2!$D3,Sheet1!$F$3:$F$14,"○")=0

Comments

[sazi]

〇ではなく×を判断しないと駄目だと思います。
↑失礼。差し引きしていますね。

[jinoji]

はい、差し引いています。
「全てが○」と「×が0個」が同じと考えてよいか分からなかったので、多少回りくどいけれど上記のようにしました。

[sazi]

比較の方が若干シンプルになりますね。

[jinoji]

ああ確かに。
あと、グループ名はみなくてもグループNo.だけでよいのかも。

[jinoji]

=COUNTIF(Sheet1!$B$3:$B$14,Sheet2!$C3)=COUNTIFS(Sheet1!$B$3:$B$14,Sheet2!$C3,Sheet1!$E$3:$E$14,"○")

[bulponm_00]

こちら「Sheet1!$B$3:$B$14」の部分は『Sheet1!$C$3:$C$14』、「Sheet1!$$3:$E$14」の部分は『Sheet1!$F$3:$F$14』でよろしかったでしょうか

[jinoji]

あ、そうです。
手元で確かめたときにずれてしまってました。

[bulponm_00]

sheet2の表全体に条件付き書式の新しいルールとして「=COUNTIF(Sheet1!$C$3:$C$14,Sheet2!$C3)=COUNTIFS(Sheet1!$C$3:$C$14,Sheet2!$C3,Sheet1!$F$3:$F$14,"○")」で背景に色が付くようにしてみたのですが、うまくいきませんでした。
間違っている点があればご教授いただければ幸いです。

[sazi]

COUNTIF()の条件は範囲に対応していないので、Sheet2のグループごとに条件付き書式を指定しないと駄目だと思います。

[jinoji]

表全体というのはC3:H7ですか？

[bulponm_00]

はい。C3:H7です。

[jinoji]

〇と○の違いかも？
まあ、解決されたようなのでよしとしましょうか。

[sazi]

=(COUNTIFS(Sheet1!$C$3:$C$14,Sheet2!$C3)=COUNTIFS(Sheet1!$C$3:$C$14,Sheet2!$C3,Sheet1!$F$3:$F$14,"○"))
範囲は「=$C$3:$H$7」
で試しましたが、正しい結果にはなりませんので、よし（＝正しい）とはしないで下さい。

[bulponm_00]

こちらでも上手くいきました。その違いのようでした、、！
ご回答ありがとうございました！

[sazi]

マジですか。こちらでは駄目なんですけど。。。

[sazi]

> bulponm_00さん
設定した式を教えて下さい。
↑〇の違いでした。orz
> jinoji さん
失礼しました。

Answer 2

複数のセルの条件が揃った場合のやり方

SUMPRODUCT()は結構便利です。
Excel2010-2016：SUMPRODUCT関数の使い方【応用編】
以下は「×」の個数が0の場合にTrueになる条件式です。

=(SUMPRODUCT((Sheet1!$C$3:$C$999=Sheet2!$C3)*(Sheet1!$D$3:$D$999=Sheet2!$D3)*(Sheet1!$F$3:$F$999="×")=0)

すべての行のF列が「〇」になった場合に

以下は、全体の個数と〇の数が一致した場合にTrueとなる式です。
※〇以外は関係が無いので、×ではなくて空白でも大丈夫です。

=(SUMPRODUCT((Sheet1!$C$3:$C$999=Sheet2!$C3)*(Sheet1!$D$3:$D$999=Sheet2!$D3))= SUMPRODUCT((Sheet1!$C$3:$C$999=Sheet2!$C3)*(Sheet1!$D$3:$D$999=Sheet2!$D3)*(Sheet1!$F$3:$F$999="〇")))

グループごとにルールを作成するのではなく、表全体にルールを作成したいと考えています。

上記条件付き書式は、sheet2全体に対して１つで大丈夫です。

Comments

[bulponm_00]

ご回答ありがとうございます。
sheet２の表全体に「=(SUMPRODUCT((Sheet1!$C$2:$C$999=Sheet2!$C2)*(Sheet1!$D$2:$D$999=Sheet2!$D2))= SUMPRODUCT((Sheet1!$C$2:$C$999=Sheet2!$C2)*(Sheet1!$D$2:$D$999=Sheet2!$D2)*(Sheet1!$F$2:$F$999="〇")))」の条件付き書式を作成したのですが、sheet２の「62（メロン）」「13いちご」の行に色がついてしまいました。

[sazi]

こちらではそのようにはなりません。
適用先の内容はどのようにしましたか？こちらで指定した範囲は「=$C$3:$H$7」です。
※範囲は特に影響しなそうですが。
若しくは、質問以外のデータが存在していたりしませんか？
特にありそうなのが、〇とかグループ名とかが微妙に違うとかです。

[sazi]

グループNoと「〇」の条件だけで良いのなら、グループ名を条件から外してみてください。

[bulponm_00]

同じく範囲は「=$C$3:$H$7」で、他の条件のクリア、「〇」のコピペなど再度確認しましたが、うまくいきませんでした。グループ名を外した場合の「=(SUMPRODUCT(Sheet1!$C$2:$C$999=$C2)= SUMPRODUCT((Sheet1!$C$2:$C$999=$C2)*(Sheet1!$F$2:$F$999="〇")))」も試しましたが、こちらはsheet２の「100（リンゴ）」の行に色がついてしまいました。

[sazi]

ヘッダー迄範囲に含めるとおかしいのかもしれませんね。
=(SUMPRODUCT((Sheet1!$C$3:$C$999=Sheet2!$C3)*(Sheet1!$C$3:$C$999=Sheet2!$C3))= SUMPRODUCT((Sheet1!$C$3:$C$999=Sheet2!$C3)*(Sheet1!$F$3:$F$999="〇")))
としてみて下さい。

[bulponm_00]

上手くいきました！何度もお聞きしてすみませんでした。
色々と検討して頂きありがとうございました。

Answer 3

別シートを条件付き書式として制御するのはできないと思います。

上図のようにピボットテーブルを設定して、xが無い行に条件付き書式を設定するなど一手間追加する必要があるかと思います。

Comments

[sazi]

> 別シートを条件付き書式として制御するのはできないと思います。
別シートを条件付き書式の条件に含める事についてですよね？
出来ましたよ？

[t_obara]

INDIRECTを使う方法でしょうかね。

[sazi]

いえ、私が回答している内容はそのまま条件付き書式に設定しています。

[t_obara]

なるほど、参考になります。

[jinoji]

Excelのバージョンによります。