Googleスプレッドシートの条件付き書式で、複数の条件に合致する時にセルに色を塗りたい。

Question

前提・実現したいこと

Googleスプレッドシートの条件付き書式で、複数の条件に合致する時にそれぞれセルに色を塗りたい。

【現状】

購入者管理のシートで、

A列に名前
B列にメアド
C列に購入金額

と３列あります。（行は過去の決済が全て記載されています）
購入情報が入るたびに、行の内容が追加されていく仕様です。

【やりたいこと】

１）購入者情報が追加された時、「名前」と「メアド」が両方とも重複した場合は、名前のセルを青色に
（分割決済で、同じ人が決済したことがわかるように）

２）購入者情報が追加された時、「名前」と「メアド」と「金額」が全て重複した場合は、名前のセルを赤色に
（同じ決済が２回なされてしまったことに対するアラート）

【試したこと】

まず１）を満たすために条件付き書式で、

・範囲をA:Aに
・書式ルールとして
・「カスタム数式」を選択
・式は以下
=AND(COUNTIF($A:$A,A1)>1,COUNTIF($B:$B,B1)>1)

これで、名前とメアドが両方重複した時にのみ、A列を青色に塗ることはできました。

しかし２）を実行する際、その上（優先順位を上にして）にもう１つカスタム数式（以下）を追加したところ、うまくいきませんでした。

=AND(COUNTIF($A:$A,A1)>1,COUNTIF($B:$B,B1)>1,COUNTIF($C:$C,$C1)>1)

【結果】

AとBとCが全て重複した時にのみ、赤色にしたかったのですが、
Cの金額だけが過去と重複したセルについても、赤色になってしまいます。

【考えられる原因】

AND条件が上手く働いておらず、
A and B and Cではなく、Cだけ重複していても赤色になります。
どなたかわかる方、教えて頂けましたら幸甚です。

よろしくお願いします。

Answer 1

=COUNTIF(ARRAYFORMULA(CONCAT(CONCAT($A$1:$A$100,$B$1:$B$100),$C$1:$C$100)),CONCATENATE(A1:C1))>1

結合させてから重複カウントがよさそうなんで、本当は処理列1個作ると楽に出きるのですが、
処理列無しだと ARRAYFORULA使う為にCONCATを入れ子にして、こんな感じで実現できるかなと。

Comments

[hiraosa]

ありがとうございます。
ARRAYFORMULAを使用するんですね。
COUNTIFSでやや、無理くり実施していました。
参考にさせていただきます。

Answer 2

なるほど、詳しいご解説、誠にありがとうございます。
やはりやろうとしていたことが、
and条件になっていなかったことが具体的によくわかりました。

また、query関数とcountを使用するとのアドバイスありがとうございます。
参考にさせていただきます。

Answer 3

=AND(COUNTIF($A:$A,A1)>1,COUNTIF($B:$B,B1)>1,COUNTIF($C:$C,$C1)>1)

では、以下のような条件を意味します。
（xは1,2,3などの数値）

A列でAxに一致する行が1より大きい
かつ
B列でBxに一致する行が1より大きい
かつ
C列でCxに一致する行が1より大きい

つまりは各列を単独で調べていることに過ぎません。

A	B	C
Hoge	[e-mail]	100
Fuga	[e-mail]	200
Foo	[e-mail]	300

こういうデータが有って、

A	B	C
Hoge	[e-mail]	300

こういうデータが追加された場合、
A列に既に「Hoge」はあるし、
B列に既に「[e-mail]」はあるし、
C列に既に「300」はあるので、
現状通り、質問者様が意図していない動作になります。

ついでに言えば名前とメールアドレスのみの条件も同じ理由でおかしいはずです。

---

ちょっと無理矢理感はありますが、count関数とquery関数を使用すれば意図した動作になると思います。

query関数で「A列がAxかつB列がBxかつC列がCx」のものを探し、
count関数で2個以上（1個より多く）見つかれば重複ということになります。