入力ミスチェックとそのチェック結果を表示させたい

質問

いつも、大変お世話になっております。
先日、タイトル名＝『データ入力のミスチェック後その結果を表示させたい』で、
ご教示を頂きましたが、もう一つの条件が必要である事が後からわかりました。説明が分かりにくくて誠に申し訳ありませんでした。

大変、恐縮ですが、下記の式に、もう1つの条件を入れ込んだ関数の式を、
ご教示頂けると大変助かります。まだまだ力不足で自分には作れそうにありません。

チェックしたい事は、参照条件は、[利用区分]が『大学』であった時、
規定値が表記された別シート2つとデータ入力シートの『利用者ID、』『利用者名』、『利用区分』、『課題ID』を
参照し、入力データの記載が、別シート2つに無かったら、無かった項目と入力データの値を表示させたい事です。

上記のタイトル名でご教示頂いた数式は、以下の通りになります。
重複はチェックしてくれますが、課題IDが規定値と違った場合等のチェック結果が出ません。
=IF(N3<>"大学","大学以外",IF(COUNTIFS($K$3:$K$3265,K3,$P$3:$P$3265,P3)>1,"重複",IF(COUNTA(J3,K3,P3,N3)<4,"未入力有",IF(SUMPRODUCT((('利用者-教授IDマスタ'!$D$2:$D$345=P3)*('利用者-教授IDマスタ'!$F$2:$F$345=J3)*('利用者-教授IDマスタ'!$G$2:$G$345=K3))+((利用者マスタ!$A$2:$A$345=J3)*(利用者マスタ!$B$2:$B$345=K3)))>0,"OK","リスト外"))))

Sheet名＝入力データ　　　
　・参照するセルはJ列[利用者ID]、K列[利用者名(教授と生徒が入り混じっている)]、N列[利用区分(大学のみ選択する)]、P列[課題ID]
　・データはA3～AS3265まで入っています。
　・J列、K列、N列、P列は共に、空白有り、値の重複もあります。
　・利用区分の種類は大学の他に複数有り、データ全体はそれらを含めた全てが入力されています。
　・データは課題ID毎で纏めています。課題ID1つに複数の利用者(教授と生徒)が関連します。
＜J列[利用者ID]、K列[利用者名(教授と生徒が入り混じっている)]、N列[利用区分(大学のみ選択する)]、P列[課題ID]は
以下のように記載されています。＞
　　　125　　　　　山田花子　　　　大学　　　AB78　　　　　　　　　　　　　　
　　　re1234　　　 今井太郎　　　　民間　　　AB01
空白の欄 田中幸太郎　　　大学　　　AB02
　　　126　　　　　空白の欄　　　　大学　　　AB02
　　　456　　　　　中山太郎　　　　大学　　　AB02
　　　re1235　　　 山田賢一　　　　民間　　　AB01　
空白の欄　　 空白の欄　　　　施設　　　AB100　
125　　　　　山田花子 空白　　　AB90　
　　　123　　　　 長井淳　　　　　大学　　　AB78
　　　129　　　　　鈴木護　　　　　大学　　　AB78

Sheet名＝利用者-教授IDマスタ(データは利用区分の大学のみ)　
　・参照するセルはD列[課題ID]、F列[利用者ID(教授のみ)]、G列[利用者名(教授のみ)]
　・データはA2～Q325まで入っています。
　・D列は空白や重複は無し。F列、G列共に重複有り。
＜D列[課題ID]、F列[利用者ID(教授のみ)]、G列[利用者名(教授のみ)]は
以下のように記載されています。＞
　　　AB78　　　　　125　　　山田花子
　　　AB90　　　　　125　　　山田花子
　　　AB02　　　　　456　　　中山太郎　

Sheet名＝利用者マスタ(データは利用区分の大学のみ)　　　
　・参照するセルはA列[教授と生徒全員の利用者ID]、B列[利用者名(教授と生徒全員の名前が記載されている)]
　・データはA2～B3345まで入っています。
　・A列、B列共に空白や重複は無し。
＜A列[教授と生徒全員の利用者ID]、B列[利用者名(教授と生徒全員の名前が記載されている)]は
以下のように記載されています。＞
　　　123　　　長井淳　　
　　　129　　　鈴木護
　　　126　　　高木昭

回答No.6

kkkkkm · ベストアンサー

> N列が『内部利用』の時を条件に追加したいのですが

左から順に評価されますから評価したい条件を必要なところに入れ込んでください。

=IF(P3="","",IF(N3<>"内部利用","",IF(COUNTIF('利用者-教授IDマスタ'!C:C,P3)>0,"課題ID一致","記載ミス")))

お礼

大変分かり易くご教示下さりありがとうございました。
課題ID記載ミスチェックに『内部利用』の条件を加える事が出来ました。

タイトル名＝『データ入力のミスチェック後その結果を表示させたい』で
ご教示頂いたデータチェックの条件式と今回の課題ID記載ミスチェックの条件式を
使う事で、チェックの見逃しが少なくなると思います。

Excelの条件式についても、今回、大変勉強になりました。
先ずは、条件式を考える時は、『何をどうチェックし、何を求めたいのか』を
理論的に考える必要がある事を実感致しました。

何時も支えて頂きまして、誠にありがとうございました。
今後ともよろしくお願いいたします。

回答No.5

kkkkkm

> 利用者-教授IDマスタのC列[課題ID]には存在しない、でたらめな値をデータ入力のP列[課題ID]の欄に入力しているのに『OK』と表示されてしまいました。

「リスト外」が出ると思います。

利用者マスタの場合だと利用者IDと利用者名が一致すれば「Ok」になります。利用者マスタに課題IDが無いので検査対象に入っていません。

既存の関数に追加するのはややこしいので、今の関数の横の列に

=IF(COUNTIFS('利用者-教授IDマスタ'!E:E,J3,'利用者-教授IDマスタ'!F:F,K3)>0,"",IF(COUNTIFS(利用者マスタ!A:A,J3,利用者マスタ!B:B,K3)<1,"リスト外",IF(COUNTA(J3,K3,N3)<3,"未入力有",IF(P3<>"","課題IDが不要","利用者"))))

として、利用者マスタでの検査結果を出します。

空白
元の関数の結果を参照(利用者-教授IDマスタのリストに存在するユーザー)

リスト外
利用者マスタのリストにありません。

未入力有
課題ID以外に未入力があります。

課題IDが不要(元の関数の結果は「OK」)
課題IDに無駄な入力があります。

利用者(元の関数の結果は「未入力有」)
利用者マスタに存在するので課題IDが未入力でもOKです。

補足

早速のご教示ありがとうございました。
説明が口足らずで誠に申し訳ありません。
入力データの課題IDの記載ミスチェックは、単純に利用者-教授IDマスタを参照して、一致したら『課題ID一致』、一致しなかったら『記載ミス』と判定出来れば良いだけでした。
因みに、下記の式で表示はなんとか出来ましたが、N列が『内部利用』の時を条件に追加したいのですが、式にどのように追加するのか分かりません。申し訳ありませんがご教示をお願いいたします。
=IF(P3="","",IF(COUNTIF('利用者-教授IDマスタ'!C:C,P3)>0,"課題ID一致","記載ミス"))

回答No.4

kkkkkm

もしくは
データの増減を気にしなくて済むように
行を指定せずに列だけ指定する方法で試してみてください。

=IF(N3<>"大学","大学以外",IF(COUNTIFS($K:$K,K3,$P:$P,P3)>1,"重複",IF(COUNTA(J3,K3,P3,N3)<4,"未入力有",IF(SUMPRODUCT((('利用者-教授IDマスタ'!$D:$D=P3)*('利用者-教授IDマスタ'!$F:$F=J3)*('利用者-教授IDマスタ'!$G:$G=K3))+((利用者マスタ!$A:$A=J3)*(利用者マスタ!$B:$B=K3)))>0,"OK","リスト外"))))

補足

早速のご確認とご教示ありがとうございます。
いつも、分かり易い説明をして下さり大変助かります。

下記については、記載ミスでした、申し訳ありません。
『利用者-教授IDマスタが「345」でも多いほうの「3345」に合わせてください。』

以下の式では、
データ入力のP列[課題ID]の欄を、空白にした場合、『未入力有』が表示されました。
しかし、利用者-教授IDマスタのC列[課題ID]には存在しない、でたらめな値をデータ入力のP列[課題ID]の欄に入力しているのに『OK』と表示されてしまいました。
『利用者-教授IDマスタ'!$C$2:$C$3346=P3』左記の条件と違った場合は『IDマスタに無し』と表示させたいのですが、難しいでしょうか？　大変お手数ですが、ご教示を頂けると大変助かります。
=IF(N3<>"大学","大学以外",IF(COUNTIFS($K$3:$K$3265,K3,$P$3:$P$3265,P3)>1,"重複",IF(COUNTA(J3,K3,P3,N3)<4,"未入力有",IF(SUMPRODUCT((('利用者-教授IDマスタ'!$C$2:$C$3346=P3)*('利用者-教授IDマスタ'!$E$2:$E$3346=J3)*('利用者-教授IDマスタ'!$F$2:$F$3346=K3))+((利用者マスタ!$A$2:$A$3346=J3)*(利用者マスタ!$B$2:$B$3346=K3)))>0,"OK","リスト外"))))

回答No.3

kkkkkm

あと、SUMPRODUCT関数の中の行指定が「345」になっていますが前回「3345」に合わせてくださいと回答しましたが「345」でいいのでしょうか。「345」のままでしたら利用者マスタは345行までしか検査対象に入っていません。

'利用者-教授IDマスタが「345」でも多いほうの「3345」に合わせてください。

また、指定行数以上は検査対象にならないので、今後増加した場合を考えて「4000」とかにしておいてもいいかもしれません。

回答No.2

kkkkkm

> ②データ入力の[利用者ID]と[利用者名]が、利用者-教授IDマスタ又は、利用者マスタ中の[利用者ID]と[利用者名]の組み合わせと一致しているか、

一致していない場合、No.1で回答したようにどちらが間違っているのか不明なので、現状「リスト外」が出ると思います。

> 　又は空欄だったら、記載ミスまたは記載抜けとなる。

「未入力」が出ると思います。

> 利用者-教授IDマスタ中にデータ入力の[課題ID]が見つからなかったら

現状「リスト外」が出ると思います。

> 空欄だったら

「未入力」が出ると思います。

現状で何が不足しているのかがわかりません。

回答No.1

kkkkkm

> 規定値が表記された別シート2つとデータ入力シートの『利用者ID、』『利用者名』、『利用区分』、『課題ID』を
> 参照し、入力データの記載が、別シート2つに無かったら、無かった項目と入力データの値を表示させたい事です。

現状では該当する組み合わせがない場合「リスト外」と表示されますが、個々に表示したいという事でしたらどの項目を基準(必ず正しい)として考えるのかが不明な場合は、判断できないのではないでしょうか。

たとえば

125,山田花子,大学,AB90
456,中山太郎,大学,AB02
587,田中一郎,大学,AB91

の組み合わせが正しいとして

456,山田花子,大学,AB91

上記の組み合わせが
IDの間違い
氏名の間違い
課題IDの間違い
何が間違いなのか分からないと思います。

組み合わせで考えなければ、個々はすべてリストに存在するので間違いなしになります。

補足

早速、ご教示下さり、大変光栄です。
以下①～③の全て条件がクリア出来た時は『OK』、条件がクリア出来なかった箇所は具体的に記載するとしたいのですが、
条件が複雑過ぎて、参照条件を理論的に説明するまでに至りませんでした、申し訳ありません。

①データ入力の[利用区分]が『大学』の時に、

②データ入力の[利用者ID]と[利用者名]が、利用者-教授IDマスタ又は、利用者マスタ中の[利用者ID]と[利用者名]の組み合わせと一致しているか、
　利用者-教授IDマスタ又は、利用者マスタにデータ入力の[利用者ID]と[利用者名]の組み合わせが見つからなかったら、
　又は空欄だったら、記載ミスまたは記載抜けとなる。
　※利用者IDは、利用者-教授IDマスタ、利用者マスタ共に利用者個別の番号です。

③また、データ入力の[課題ID]が、利用者-教授IDマスタの[課題ID]にあるIDであるか。
　利用者-教授IDマスタ中にデータ入力の[課題ID]が見つからなかったら、又は空欄だったら、課題IDの記載ミスまたは記載抜けとなる。　
　そして、データ入力の[課題ID]と[利用者名]と[利用者ID]の組み合わせが、利用者-教授IDマスタにあるか、
　無かったら、[利用者名]や[利用者ID]に記載ミス又は記載抜けがある。
　　※利用者-教授IDマスタの[課題ID]は重複無しですが、複数の[課題ID]に同じ利用者名と[利用者ID]が記載されています。
　　※一方、データ入力の[課題ID]は、[課題ID]1件に、[利用者ID]と[利用者名]の組み合わせが複数あります。

Sheet名＝データ入力
J列 [利用者ID]
K列 [利用者名]
P列 [課題ID]
N列 [利用区分]

Sheet名＝利用者-教授IDマスタ(データは利用区分の大学のみ)
※下記の通りが正しいです。質問文の記載に誤りがありました、申し訳ありません。
C列[課題ID]、
E列[利用者ID(教授のみ)]
F列[利用者名(教授のみ)]

Sheet名＝利用者マスタ(データは利用区分の大学のみ)
A列　[教授と生徒全員の利用者ID]
B列　[利用者名