エクセルで条件に基づきエラーメッセージを表示させた

質問

下記のように設定しています
・A2、B2、C2、D2の各セル内にチェックボックスを配置
・A2のリンクセルはAE2に、B2はAF2に、C2はAG2に、D2はAH2に配置
・AY2に「=COUNTIF(AE2:AH2,TRUE)」を配置して、B2、C2 、D2の中で選択した数を表示

B2、C2 、D2の全てに該当しない場合にはA1をチェックするのですが、
A1を選んだ場合で、間違ってB2、C2 、D2の中でどれか1つ以上にもチェックしてしまった場合に注意喚起するメッセージを表示させたいのです

データの入力規則の「無効なデータが入力されたらエラーメッセージを表示する」を使って
A2を選択したあとに、かつB2 and/or C2 and/or D2を選択したとして
例えば「重複してませんか？」のように表示させようとしたのですが、どうもうまくできませんでした

どなたかよい解決法をご存知でしたら、どうぞ教えて下さい
可能であればマクロは使わない方法がありがたいです
（わかりやすくするためセルの表記を簡素化しています、実際には横に20行近い質問項目のセルを用意し、
縦に300近く個別のIDを設定していますので、マクロの微修正が出来るほどの技術は持ち合わせて無いからなのです）

使用環境はWindows10、excel2019相当の自宅とWindows7、excel2010相当の職場でデータをやりとりしています

どうぞよろしくおねがいいたします

回答No.12

HohoPapa · ベストアンサー

VBAを持ち込むことができるのであれば、
添付画像を例に
'----------------------------------------------
Sub 警告()
　Dim c As Range
　For Each c In Range("AY2:AY4")
　　If c.Value <> 1 Then
　　　MsgBox "重複してませんか？"
　　End If
　Next
End Sub
'----------------------------------------------
といったコードでいかがでしょうか？

あるいは、
#9のコメントの延長で考えるのであれば、
'----------------------------------------------
Sub 警告()
If Range("BA2") = "警告！" Then MsgBox "入力内容に矛盾があります。"
End Sub
'----------------------------------------------
を
'----------------------------------------------
Sub 警告()
　Dim c As Range
　For Each c In Range("BA2:BA300")
　　If c.Value = "警告！" Then
　　　MsgBox "入力内容に矛盾があります。"
　　End If
　Next
End Sub
'----------------------------------------------
とすることで、複数範囲の行を1行ごとにチェックする動作になります。

お礼

誠にありがとうございます
願っていた通りの挙動を実装出来ました
まさに望外の幸せです
今後ともご指導の程どうぞよろしくおねがい申し上げます

回答No.11

kon555

最後になると思います。１つだけ、提示させて頂いたマクロについて補足します。

If Range("XX1") = "警告！" Then MsgBox "入力内容に矛盾があります。"

↑の "警告！"を変更すれば、探す文字は自由に変えられます。 "矛盾"でも "ルール違反"でもなんでもありです。

　そして書き方を変えると、数値判定も可能です。
If Range("XX1") = 1 Then MsgBox "入力内容に矛盾があります。"
　これで「1なら」です。

If Range("XX1") >1 Then MsgBox "入力内容に矛盾があります。"
　これで「1より大きいなら」です。

If Range("XX1") <> 1 Then MsgBox "入力内容に矛盾があります。"
　これで「1以外なら」です。

　この辺りはセル関数としてのif関数と考え方は全く同じです。文字列なら""で囲みます。数字ならそのままです。

お礼

どうもありがとうございました

回答No.10

kon555

>>そもそも論として、メッセージボックス用のマクロ内のRange関数では単一のセルしか指定出来ないということでしょうか？
>>Range("BA2:BA300")などのように複数セルを指定することが出来ないと解釈していますが合っていますか？
　そうです。複数セルを指定したい場合、色々とやり方はありますが、こんな書き方になります。

Sub 警告()
dim i , f
f = 0
for i = 1 to 100
　If celles(1,i) = "警告！" Then f = f+1
next i
if f <>0 then MsgBox "入力内容に矛盾があります。"
End Sub

　動作確認をしていないというか、本当に私の脳内だけで書いていますので変なミスタイプなどがあるかもしれないのですが、まあこんな感じ、という例文だと考えてください。
　これで1行目を横向きに、A1、B1、C1・・・・と100個までのセルを端からチェックしていく、という動作になります。こうした形でマクロのみで処理するのは可能ですし、そうするのが最もスマートな手法ではあります。
　ただ貴方が書かれたような「実際のシートでは300近くの列が存在し、その数は日々増えて行きます」というような場合、遠からずマクロが想定している範囲から逸脱して、マクロが破綻してエラーを起こしたり、予期せぬ挙動を招きます。
　そうなると、多分今の貴方に修正することは不可能でしょうし、こうした質問サイトなどに頼っても、使い物にはならないと思います。
　それならいっそ、マクロはギリギリまで削り落としてしまい、まだしも修正が簡単なセル関数を用いてしまおう、と割り切るのが私の案です。

>>実際の運用では～～
　if関数やcountifあたりの組み合わせです。何とでもなると思います。
　ただこの『なんとでもなると思います』の辺りも「私にとってはスキルオーバーだし、自分でその仕組みを考えて、修正する事はできない」というなら、申し訳ないですが、貴方が実現しようとしている希望自体が、貴方にとって高望み過ぎる、と私としては言わざるを得ません。

お礼

どうもありがとうございました

回答No.9

kon555

>> 具体的にどのような手順を指しますか？
　ごめんなさい、これは私の書き方が悪かったですね。申し訳ないです。
　ちょっと長くなりましたが、最後まで読んで下さい。

『シート関数を使う』というのは、『マクロではなく、ワークシートの関数を使う』という意味です。
　おそらく貴方が今普通にやっているような、SUMやIFやcount ifやvlookupその他諸々の、セルに入力して使う関数です。それを使って、どこかの1つのセルに条件判定の結果を集約するのです。

　単に『関数』というとワークシートの関数かマクロの関数か分かりにくいので、以下の内容ではワークシートの関数はセル関数、マクロの関数はマクロ関数、と書きます。

　究極的に言えば、貴方が今困っているのは、下記の2点です。
1.『とあるセルの値が◯◯の時にはメッセージボックスを出す』という、いうなれば「MSGセル関数」とか「MSG機能」が普通に使用可能なExcelの機能としては用意されていない。
2.メッセージボックス機能はマクロ関数には存在するが、それを自在に操るだけのスキルがない。

　なので、まず私は極めて限定的な、貴方でもコントロール可能なレベルの、メッセージボックスマクロを作成しました。
　これを使えば、貴方は任意のタイミングで、好きな文言のメッセージボックスが作成できます。
　要は「判定できるセル範囲は1つだけ」という若干不便なメッセージボックス機能が、アップデートで新規追加された、と考えてみて下さい。いかにもマイクロソフトが作りそうな、概ね便利だけど痒いところに手が届かない新規機能です。
　でもこの機能と、複数のセル関数を組み合わせれば、貴方は希望の機能を実現できるハズです。
　セル関数で可能な事はセル関数でやれば良いのです。「メッセージボックスを出す」という、セル関数では無理なところだけマクロ関数に任せれば良いんです。

お礼

どうもありがとうございました。

補足

なんとなくイメージは出来たのですが
実際にどの関数を用いるべきなのかは、私の知識では難しいですね
そもそも論として、メッセージボックス用のマクロ内のRange関数では単一のセルしか指定出来ないということでしょうか？
それゆえに、Range("BA2:BA300")などのように複数セルを指定することが出来ないと解釈していますが合っていますか？

例えば
A2　B2　C2　D2　BA2
A3　B3　C3　D3　BA3
A4　B4　C4　D4　BA4
A5　B5　C5　D5　BA5
A6　B6　C6　D6　BA6
とあったとして、

1）A2とB2にチェックされていたら、BA2には"重複！"と表示されます
それをもとにRange("BA2")としたマクロが作動してメッセージボックスが表示される
ここまでは実現できました

2）実際の運用ではA2とB2、A4とC4、A6とD6などのように複数の列が対象となります
このとき、BA2、BA4、BA6にそれぞれ"重複！"と表示させる可能性があるわけですが
もっと別の判定用に用意した例えばXX300というセルにその"重複！"を飛ばすということでしょうか？
そうすると、BA2、BA4、BA6もすべて同時にXX300に飛んでしまいそうです

一般的なセル関数について知識を深めるために下記も見ながら
https://excel-ubara.com/EXCEL/EXCEL902.html
https://excel-ubara.com/EXCEL/EXCEL918.html
一つ一つ探してみましたが
1つのセルで判定させるのに必要な関数とはどれを指しますでしょうか？
ご教示頂けますと幸いです

回答No.8

kon555

>> 他の列へと複数の範囲を指定しようとするとうまく行きません。実際のシートでは300近くの列が存在し、その数は日々増えて行きます。
　その必要はありません。
　より詳しく言うなら『マクロで複数セルを対象にしなくていい』です。
　発想を変えましょう。おそらく貴方のスキルなら、シート関数を組み合わせたりして、『判定セルの対象範囲を広げる』事と、『マクロの対象セルを変更する』事は可能だと思います。
　つまり『シート関数により最終的に1つのセルで判定を行う』プラス『単一セルを判定対象とした警告マクロ』を組み合わせればよいのです。
　この方法なら、他のワークシートで警告メッセージが必要になった際も柔軟に対応できると思いますし、学習負荷を最小にできると思いますが、いかがでしょうか。

お礼

どうもありがとうございました。

補足

ご教示頂きありがとうございます

総論的には同意致しますが、あまりに遠大な目標のため実現できるか不透明です
取り組むべき方向性は理解致しました

>> シート関数を組み合わせたりして、『判定セルの対象範囲を広げる』事と、『マクロの対象セルを変更する』事
とは具体的にどのような手順を指しますか？
「SHEET関数」というのも見つけましたが、これはシート番号を返すもののようなのでおそらく意図されているものとは違いそうですね
取り組みたい意欲はあるのですが、方法がわかりません
是非教えて下さい

>> 『単一セルを判定対象とした警告マクロ』
というのは、No.6で教えて頂いたメッセージボックス用の1行マクロでよいでしょうか
この中のRange関数のセルの指定部分を
上のシート関数によって変化させるということですか？

それとも、Range関数のセルは固定したままなのであれば
例えば判定用に別のシートを用意して、A2をチェックしたという情報を毎回その判定用シートに飛ばすという意味合いでしょうか？
Range関数の方はシートを分ければセルを固定することができそうですが
その場合はメッセージボックスはその新たなシートに表示されませんか？

とんちんかんな質問となっているとおもいますが
何卒ご教示ください

回答No.7

lalique0304

回答No.5、No.6の妥協案です。

チェックボックスのON/OFFとそれを使った計算式の結果だけでマクロを使わずにメッセージボックスを表示・消去する方法は考えつきませんでした。

妥協案ですが、メッセージは文字ではなく色だけではだめでしょうか？
条件付き書式を使って、エラー時にチェックボックスが配置されたA2からD2のセルを赤くするという方法です。

AY2は前回のまま　=IF(AE2*COUNTIF(AF2:AH2,TRUE)=0,COUNTIF(AF2:AH2,TRUE),"重複！")
・チェックが正しければ当初のとおりチェックの数をカウント
・AY2を使いたくなければ未使用の列を使い非表示にする

A2:D2のセルに条件付き書式で新しいルールを以下のように設定します。
　ルールの種類：数式を使用して、書式設定するセルを決定
　数式：=$AY2="重複！"
　書式：赤色
以下、A2:D2の書式のみをコピーする
・セルいっぱいにチェックボックスが埋め込まれていると範囲選択できないので、一時的にA2とD2のセル幅を広げておく

これで、A2をチェックしB2からD2のいずれかをチェックした瞬間、もしくはB2からD2のいずれかをチェックしたあとA2をチェックした瞬間、チェックボックスがあるA2:D2のセルが赤になって警告・注意喚起し、修正すれば元の色に戻ります。

お礼

どうもありがとうございました。

補足

ご教示頂きありがとうございます
さっそく作成してみました
実は、チェックボックスを配置しているA2:D2相当のセルには
すでにチェックボックスを選択したらセルの色を塗りつぶす条件付き書式を組んでおりました
複数の条件付き書式を組むことも可能なようでしたが
いろんな色が重なり合うとエラーメッセージとして表示させても気がつかない恐れがありそうです

別のセル（例えばAY2）に色を塗りつぶすように指定した場合には、セルが遠すぎて色が変わったことに気がつかないかもしれません

どうぞよろしくおねがいいたします

回答No.6

kon555

すでにNo5で関数による判定用セルの作成は回答済ですので、警告メッセージの出し方を。出し方というか、アイデアのレベルですね。

　まずマクロを使用しないパターン。これは『警告メッセージっぽいもの』です。
　やり方は簡単で、どこか自由に使えるセル、データ範囲外のセルに対して条件付き書式を設定します。別シートでも構いません。
　書式も自由です。入力内容がOKなら青塗りで「内容OK」、NGなら赤塗りで「内容矛盾」、とかですかね。
　そしてそのセルを元にした『リンクされた図』を適当な位置に挿入します。
https://hamachan.info/win7/Excel/zu.html
　これでセル構造に対して影響を与える事無く、警告メッセージを出せます。メッセージと違うのは、どう頑張っても『リンクされた図』はその場に存在し続ける事です。OKを押して消す、みたいな事はできません。
　ただ入力する際の注意喚起目的なら、実務では充分に使えると思います。

　次にマクロを用いるパターン。個人的なオススメはコチラ。
　既に判定用セルを設定する事は出来るわけですから、それを対象としたメッセージボックス用の1行マクロです。
Sub 警告()
If Range("XX1") = "警告！" Then MsgBox "入力内容に矛盾があります。"
End Sub
　このコードの中では仮にXX1セルを対象としていますが、A1に変えたければ「Range("XX1") 」を「Range("A1")」に帰るだけです。
　マクロの実行はオブジェクトに設定したボタン式がオススメです。
http://www4.synapse.ne.jp/yone/excel2010/excel2010_macro_form.html
　マクロを編集するスキルがないなら、編集しなくてもいいほどに簡略なマクロを作り、シート側で合わせようという発想です。

　多分どちらでもお望みの業務フローは実現できると思います。

お礼

どうもありがとうございました。

補足

素晴らしいアイデアありがとうございます
マクロの方でチャレンジしてみました

No.5で教えていただいた関数を仕込んだBA2を用意しました
そこでいただいたマクロを配置したボタンを押すと
思い描いていたエラーメッセージが表示出来ました

ただ・・
私のスキルが足りないからなのですが
XX1のところをBA2に変更して、そこが"重複！"となれば、見事にエラーメッセージが表示されるものの
他の列へと複数の範囲を指定しようとするとうまく行きません
実際のシートでは300近くの列が存在し、その数は日々増えて行きます

Range("BA2")のところを、列全体を指定したRange("BA")としたり
範囲を指定したRange("BA2:BA300")としてみたり
複数選択して名前の定義というものでもよいという情報もみつけたので、BA2:BA300に"内容矛盾"と設定し Range("内容矛盾")ともしましたが
どれも実行時エラーとなってしまいます

IF関数もRange関数も初めてなので、とても初歩的な疑問となり恐縮ですが
BA列全体か、あるいは広範囲にBA2:BA300というような形で、このマクロを適用させる方法がありましたらあわせてご教示ください

どうぞよろしくお願いいたします

回答No.5

lalique0304

質問のA1がA2の間違いであると思って回答します。

A2のコントロール値とB2:D2のコントロール値はいずれかが0であればOKなので、その2つを掛け合わせても0ならOK、0以外であればNGとしました。（どちらも0の場合は未入力なのでそのまま0表示です）

AY2に次の式を入れてみてください。
=IF(AE2*COUNTIF(AF2:AH2,TRUE)=0,COUNTIF(AF2:AH2,TRUE),"重複！")

お礼

どうもありがとうございました。

補足

大変素晴らしいアイデアをご教示頂き、誠にありがとうございます
ただ、AY2にはすでに頭書に記載の関数を配置しているため別のセルを用意しなくてはならないのですが
横にも縦にも画面いっぱいにセルが埋まっており
せっかくの"重複！"が遠いところに表示されてしまうことになってしまいました
出来れば入力規則のエラーメッセージに表示出来ればベストなのですが・・
如何でしょうか？

回答No.4

kon555

>> 紙ベースで集められた様式を目視し、エクセルに手入力していくというのが根幹です
　なるほど。健康診断の問診票やアンケートのイメージですね。
　私が考えていた自動化は「他の項目にチェックがなければ『あてはまらない』チェックオン、そうでなければ『あてはまらない』オフ」というイメージでした。つまり入力ミスを発生させない目的です。
　元々が手書き用紙なら、原紙の誤りがあり得るため『矛盾できない』という仕組みも不適切である、と理解しました。

　警告メッセージの表示は十分可能です。複数の方法がありますので、詳細をまとめてみます。

お礼

どうもありがとうございました。

回答No.3

kon555

>> 実際のシートを簡略化したsampleを作成しました
　申し訳ないのですが、私の環境的に実ファイルをダウンロードして確認する事は困難です。画像閲覧なら大丈夫なのですが。

　またこの件について、貴方に許される作業範囲について確認したいのですが、下記の事項はいかがでしょうか。
　正直なところ、やり方次第で自動チェックは十分に可能です。ただ複数のやり方がありそうなので、貴方にとっての最適解のあたりを付けたいのです。
（以下質問）
1.そもそものチェックの自動化は、『可能ならやりたい』か『出来れば人がチェックするのが望ましい』か。

2.既存のシート構造とは無関係な位置にデータ用のセルを追加したり、新規シートを作成したりは可能か。

3.マクロについて『マクロの微修正が出来るほどの技術は持ち合わせて無い』との事だが、具体的にはどの程度のスキルはあるか。

4.マクロの使用について、貴方のスキルで対応可能なレベルという前提の場合、使用したくない理由はあるか。
（例.他の人も使う、会社としては推奨していない、長期的な安定性に懸念、など）

お礼

どうもありがとうございました。

補足

御連絡誠にありがとうございます
わたくしの言葉足らずであったことをご容赦下さい
今回の作業は紙ベースで集められた様式を目視し、エクセルに手入力していくというのが根幹です
その用紙には20個ほどの選択肢が存在し、どれを選択しているのかを転記する訳です
一切選択されていなかったらA2をチェックし、他の項目を選択していたらそれぞれB2、C2、D2・・・と選んでゆきます
転記が必須な時点で、どこかの段階でミスは発生し得るわけですが、そこはそことして・・
A2にチェックしたうえで、誤ってB2以降にチェックも入れてしまわないような対策を考えていたところです

1）チェックの自動化
最初に御指摘頂いたURLを参照しましたが、チェックを入れたことをデータ化して（TRUE or FALSE）、以降の動作に関数を織り込むと解釈したのですが、異なっていたらご容赦下さい
チェックを入れること自体は手入力なので自動化は出来ません

2）セルを追加、新規シートの作成
両者ともに可能ではありますが、セルの場合は1画面内におさまる範囲に新たなスペースはありません
かなり離れた場所には用意できます
シートについては新たに増やすことに制約はありません

3）マクロのスキル
今回のエクセルファイルを作成するにあたり、チェックボックスを配置して、カウントしたりする都合が出たので
はじめてマクロに触れました
といってもネットに出ている情報を頼りに、例えば指定範囲のチェックボックスを消すマクロを組んだりしました
ほぼコピーペーストによるものであり、マクロの意図を理解して一文ずつ作成していったわけはありません
ですので、マクロのスキルはほぼ無いといえます

4）使用したくない理由
職場の環境としてはマクロ入りのファイルを用いること自体には制約はありません
このファイルの管理はわたし自身に任されているので、他の方がいじることは考えにくいです

ご質問の意図に沿っているでしょうか
どうぞよろしくご検討下さい

なお、簡略化したエクセルのエッセンス部をキャプチャーした画像を用意したのですが
それをどのように提示するべきか模索中です
OKWAVEの管理の方に問い合わせておりますので、ご提示できるようになればと願っております

回答No.2

kon555

業務フロー全体が分からないので的外れかもしれませんが、そもそも「B2、C2 、D2の全てに該当しない場合にはA1をチェックする」という部分について、チェックボックスではなく関数で自動判定させれば解決するように思います。
https://office-hack.com/excel/checkbox-function/
　B2、C2 、D2の全てのチェックボックスがoffならA1セルに何らかのマークを表示させるなり、「レ」という文字を表示させるなり、if関数で簡単にできるハズです。
　そもそも人間の判定に委ねない事でミスへの注意喚起そのものが不要になります。

　どうしてもチェックボックスでなければいけない理由があるのであれば、セルの値と連動せる事でチェックボックスの動作をコントロールできますので、これを組み合わせれば自動チェックのon-offも可能です。
https://tonari-it.com/excel-form-checkbox/#toc5

お礼

わたくしのミスなのですが「A1」ではなく「A2」でした
うまくニュアンスを伝えるべく、実際のシートを簡略化したsampleを作成しました
ギガファイル便経由でおとして頂けると幸いです
下記よりご確認ください
https://xgf.nu/1WDK
ダウンロード期限は1週間となっておりますのでご容赦下さい

補足

御指摘ありがとうございます
当初はIF関数やAND関数を用いて、条件を作り出そうとしていましたがわたくしの知識ではうまく作成できませんでした
例えば、AND(AE=TRUE,AY>1)として
データの入力規則の条件（ユーザー設定）で数式の部分に入力しても
エラーメッセージが表示されませんでした
適切な関数？引数？ありましたら是非ご教示ください

またもう一つのチェックボックスとの連動についてですが
すでにチェックボックスをクリックしたことによる動作はリンクするセルに設定してありますので
重複して動作を設定することは可能なのでしょうか？

実際のエクセルシートあるいはそのpng画像を添付したいのですが
この画面からはうまく添付できませんでした
ご容赦下さい