Excel2010 飛び地セルの重複チェック

質問

教えてください。
今、A1,A3,A5に入力するセルがありますが、すでにどれかに入力されていると、その他のセルに入力すると、明示的に警告するように「条件付き書式」で、セルの背景を赤に「塗りつぶし」指定したいのですが、飛び地セルの設定がわかりません。
記述は、=CountIf($A$1,$A$5,$A$5,A1)>1とし、適用先を$A$1,$A$5,$A$5としていますが、エラーでうまくいきません。よい方法を教えて戴けると嬉しいです。
ちなみに、上記セルには他の重複チェックの「条件付き書式」2件と、入力方法は「データの入力規則」で、入力値の種類を「リスト」を選択しています。

回答No.4

tsubuyuki · ベストアンサー

> 例えば、A3セルに入力されている時にA1または、
> A5のいづれかに入力しようとすると、入力しようとするセルを赤くしたい訳です。

なるほど・・・
エクセルはご存知のように「セル選択→入力開始→入力完了→確定」と言う流れです。
ここで確定された「セルに入力された結果を見て」内容を判断しますので、
“入力しようとする”セルに対して判断する機能は基本的には困難です。

なので、先般の回答のように
「（Ａ３に）入力した結果、他のセル（Ａ１ｏｒＡ５）にもデータが入っているので、
　警告・確認の意味でセルを塗り潰す」
と言う条件付き書式を使う選択肢が一つ出来るわけです。

基本機能でご希望の処理に一番近い可能性がある機能は「入力規則」です。
ご存知とは思いますが、
「規則に合わない値を確定しようとすると警告ダイアログを出し、確定させない」
機能ですから、ご希望の処理の内“入力させない”はクリアできます。
これが二つ目の選択肢ですが、すでに入力値をリストに設定しておられるようなので、
この機能は残念ながら使えません。
（ついでなので紹介しておくと、入力規則の種類をユーザー設定、
　数式を「=COUNTA($A$1,$A$3,$A$5)<=1」として使います。）

なので基本機能で頑張ろうと思うと、やはり条件付書式が妥当です。
先般の私からの提示は「Ａ３に入力されたからＡ１・Ａ５を赤くしよう」と言うもので
完全に私が誤解してしまっていた結果です。失礼しました。

「Ａ３に入力されているのに、Ａ１にも入力されちゃったから赤くしよう」
の処理であれば、条件付書式の数式に
　　=COUNTA($A$1,$A$3,$A$5)>1
が妥当と思います。
「Ａ３に入力されているのに、Ａ１にも入力されちゃったから、この２セルは赤くしよう
　でも、Ａ５は空白のままだから、白のままでいいや」
とするのであれば、ＡＮＤ関数を併用して
　　=AND(A1<>"",COUNTA($A$1,$A$3,$A$5)>1)　※
Ａ１セルはこれでうまくいきます。
Ａ３・Ａ５がうまくいかなかったら　A1<>"" を A3・A5に書き換えてください。

とりあえず、現在の頭の中身と条件付書式のルールを一旦すべて削除し、
条件付書式のルールに、最後の式（※）をコピー貼り付けして試してみてください。

どうしても確定前にチェックしたいというのであれば、
マクロを使うことになりますが、個人的にはそこまでしなくても・・です。

お礼

いつもお世話になります。只今作成している表はまだ完成しませんのでこれからも分からないところが出ましたら教えてください。よろしくお願いします。

補足

回答ありがとうございます。

＞どうしても確定前にチェックしたい

と誤解される文面で大変申し訳ございません。確定した後で重複しているとわかればいいのです。それで、そのセルを赤くすれば入力を取り消すようにしたい訳です。

＞=COUNTA($A$1,$A$3,$A$5)>1
を確認すると2個目のセルを確定すると既に入力されている1個目のセルが赤くなります。

＞=AND(A1<>"",COUNTA($A$1,$A$3,$A$5)>1)・・・A1セル
＞=AND(A3<>"",COUNTA($A$1,$A$3,$A$5)>1)・・・A3セル
＞=AND(A5<>"",COUNTA($A$1,$A$3,$A$5)>1)・・・A5セル

として式を入力すると、２番目のセルに入力すると、１番目に入力されているセルと２番目のセルが赤くなります。
２番目のセルを赤くしたい希望でしたが、これでも特に支障がないので、と言うかこれの方が注意喚起には都合がよいようです。
何回もご教示いただきましてありがとうございました。

何かコメントがあるといけないので当面「ベストアンサー」とせず保留しておきます。時間を見計らって閉じます。ご了解ください。

回答No.3

tsubuyuki

失礼しました。
私も読み違えていたようです。

> 同じ文字の重複チェックではなく、
> A1,A3,A5のいづれかに文字は”一回しか入力”できず、
> 重複した入力を防ぎたい訳です。

つまり、
・入力するセルは「Ａ１・Ａ３・Ａ５」セルのうち「一箇所だけ」
・例えばＡ３セルに値が入っている場合、Ａ１・Ａ５セルには入力させたくない
・そのために、例えばＡ３セルに入力されている時はＡ１・Ａ５セルに色を塗る
と言うことでしょうか。

上記でＯＫとすると、
Ａ１セルの条件付き書式
・=OR(A3<>"",A5<>"")　　塗り潰し：赤　←Ａ３が空白以外かＡ５が空白以外なら塗り潰し

Ａ３セルの条件付き書式
・=OR(A1<>"",A5<>"")　　塗り潰し：赤　←Ａ１が空白以外かＡ５が空白以外なら塗り潰し

Ａ５セルの条件付き書式
・=OR(A1<>"",A3<>"")　　塗り潰し：赤　←Ａ１が空白以外かＡ３が空白以外なら塗り潰し

どこにも入力されていないときは塗り潰しなし、
いずれかに入力されると、他のセルが赤く塗り潰されます。

補足

tsubuyuki様

回答ありがとうございます。

>例えばＡ３セルに入力されている時はＡ１・Ａ５セルに色を塗る
と言うことでしょうか。

例えば、A3セルに入力されている時にA1または、A5のいづれかに入力しようとすると、入力しようとするセルを赤くしたい訳です。

回答を参考にトライしましたがたどり着きません。頭が悪くて済みません。よい回答をお願いします。

回答No.2

keithin

＞すでにどれかに入力されていると、その他のセルに入力すると、
＞明示的に警告するように「条件付き書式」で、セルの背景を赤に「塗りつぶし」指定したい

条件付き書式の数式で
=COUNTA($A$1,$A$3,$A$5)>1
と設定し、赤く塗るようにしておきます。

「２か所以上記入した」と「重複入力があった」のどっちを優先表示したいのか考えて、ルールの管理で上下関係を調整します。

補足

keithin様

回答ありがとうございます。

>条件付き書式の数式で
=COUNTA($A$1,$A$3,$A$5)>1
と設定し、赤く塗るようにしておきます。

上記をトライすると入力したセルが赤くなります。その目的の回答と思いますが、

例えば、A3セルに入力されている時にA1または、A5のいづれかに入力しようとすると、入力しようとするセルを赤くしたい訳です。

>「２か所以上記入した」と「重複入力があった」のどっちを優先表示したいのか考えて、ルールの管理で上下関係を調整します。

この意味がよくわかりません。頭が悪くて済みません。よい回答をお願いします。

回答No.1

tsubuyuki

例えば、Ａ１セルの条件付き書式のルールを式にして、
　　=OR(A1=A3,A1=A5)

例えば、Ａ２，Ａ４が条件に影響無いとして
　　=COUNTIF($A$1:$A$5,A1)>1

こんな感じでしょうか。

補足

早速回答ありがとうございます。
質問に誤記がありました。範囲は誤A1,A5,A5正A1,A3,A5でした。

>=COUNTIF($A$1:$A$5,A1)>1
はすでにこれと同じ条件式が入力され、”同じ文字”が入力されるセルを赤く「塗りつぶし」処理しています。

飛び地セルで処理したいのは、同じ文字の重複チェックではなく、A1,A3,A5のいづれかに文字は”一回しか入力”できず、重複した入力を防ぎたい訳です。
ちなみに、ご教示戴いた=OR(A1=A3,A1=A5)を
=Countif(or(A1=A3,A1=A5),A1)>1のように入力するとエラーが出ました。
=OR(A1=A3,A1=A5)だけでは、入力前から「塗りつぶし」された状態になります。
このような処理は無理でしょうか。