データ入力のミスチェック後その結果を表示させたい

質問

大変お世話になっております。　
複雑なチェックの為、チェックしたい事を上手くお伝え出来るか不安ではありますが、
下記のような複雑なチェックがセルの式で可能でしたらご教示頂けると大変助かります。
頼ってばかりで、大変申し訳ありませんが宜しくお願いします。

チェックしたい事は、入力データのN列[利用区分が『大学』の時、J列[利用者ID]、K列[利用者名(教授と生徒が入り混じっている)]、P列[課題ID]、
N列[利用区分]、P列[課題ID]で入力ミスがないか、空白の欄があるかを判定して、判定結果を入力データの最後の列へ表示させることです。

・利用区分が『大学』のときは、J列[利用者ID]、K列[利用者名]、P列[課題ID]、N列[利用区分]、P列[課題ID]に空白の欄があるのでは記載抜けのミスになります。
・利用区分が同じ『大学』で、利用者名が重複している場合、課題IDが違っていれば問題ないです。

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.3

kkkkkm · ベストアンサー

No.2の補足です。

SUMPRODUCTの範囲指定が両方のマスタとも2行目から345行目指定になっていますが、SUMPRODUCTの中で範囲の数を合わさないと駄目なので多いほうに合わせています。

で、今確認したら3345行目なので3345に合わせてください。

それぞれの行数で指定したい場合は、

SUMPRODUCT((('利用者-教授IDマスタ'!$D$2:$D$3345=P3)*('利用者-教授IDマスタ'!$F$2:$F$3345=J3)*('利用者-教授IDマスタ'!$G$2:$G$3345=K3))+((利用者マスタ!$A$2:$A$3345=J3)*(利用者マスタ!$B$2:$B$3345=K3)))>0

の部分を OR(SUMPRODUCT(),SUMPRODUCT())のパターンにして以下のようにしてください。

OR(SUMPRODUCT(('利用者-教授IDマスタ'!$D$2:$D$325=P3)*('利用者-教授IDマスタ'!$F$2:$F$325=J3)*('利用者-教授IDマスタ'!$G$2:$G$325=K3))>0,SUMPRODUCT((利用者マスタ!$A$2:$A$3345=J3)*(利用者マスタ!$B$2:$B$3345=K3))>0)

回答No.6

kkkkkm

あと、山田花子の利用区分が 空白の所がありますが、この場合「大学以外」と表示されます。

No.2の式の後ろに以下を継ぎ足せば

& IF(N3="",IF(IFERROR(IFERROR(MATCH(K3,'利用者-教授IDマスタ'!$G$2:$G$325,0),(MATCH(K3,利用者マスタ!$B$2:$B$3345,0))),"")<>""," 大学かも",""),"")

リストに名前があれば

「大学以外 大学かも」

と表示されます。

回答No.5

kkkkkm

No.2でいけた場合ですが

重複の場合どこと重複しているのか分からないので

元の式がQ列にあるとして
その右の列に(たとえばR列)
=IF(Q8="重複",K8 & P8,"")
上記がR列にあるとして
その右の列(たとえばS列)
=IF(R3="","",HYPERLINK(IFERROR("#R" & MATCH(R3,R4:$R$3265,0)+ROW(R3),"#R" & MATCH(R3,$R$2:R2,0)+1)))

として必要なだけ下にコピーしておくと、S列に重複する相手のR列のアドレスが#付きで表示されます。
そこをクリックすると相手先のセルにジャンプします。

同じ種類の重複が複数ある場合は、下方向にあるアドレスを表示して最下行には最上部の重複アドレスが表示されます。

お礼

早速のご教示ありがとうございました。
私の拙い説明から連想されて、ここまで洗練した式をサラッと作ってしまう事に大変感動しました。
更に、痒い所に手が届くような式までご紹介下さり,
更にまた感謝です。
関数式1行でここまで出来てしまうのですね。
ご教示頂いた式は、1発ですべて正常に動き、ご説明通りのチェック結果を表示させる事が出来ました。

1週間後に早速使わせて頂きます。データ入力ミスのチェックは、これ以上はチェックのしようがないと考えます。
チェックで新たに困った事が発生した時は、再度仕切り直して質問を出させて頂きます。
ご教示頂いた式はこれから時間をかけて解読して行きます。
素晴らしい方々とこのように繋がって、助けて頂ける事に幸せと安心感を感じます。
また、質問でご教授をお願いする事と思います、どうぞ今後ともご指導宜しくお願い致します。

回答No.4

imogasi

（１）もう少し、チェック項目を分類し、整理して、箇条書きで記述すること。
　　読者は、質問者の専属の教師でないので、一読して、読む気も起らないと思う。
（２）VBAなどの語句が全く出ていないが、VBAの経験があるのか、書くべきだろう。
　　＞セルの式で可能でしたら
　　が「エクセルの関数式を用いて」、ならば、チェック項目が、データベースの課題によく出るもののようなので、関数では不適で、手間がかかって、回答する、やる気が起こらない予想。
　　Excelの関数をかじったぐらいで、仕事の問題を解決しようというのは、認識が甘すぎる。コンピュター処理データは、プログラムの習得が必須で、初心者にエクセルは何でもできる、かのような幻想を抱かせる罪もある。
現状の力では、むしろ操作で、例えば、「利用区分が『大学』のときは」なら、独立したシートに、フィルタ機能などで、「大学」のデータを抜出して，抜き出したシートで、またソートなどして、同じ分類のデータは近くの行に集め、そこでチェック（多分変数（中間的に使えるデータの一部を分離して考えられるVBAで）を考える、とか。
労力がかかるが、現状の力では、やむを得ないのではないか。
入力ミスなども、VLOOKUP関数で、入力可能項目の表を作り、存在するかをチェックする以外は方法がないのでは？データベース（例アクセスVBA）なら、テーブルが自在に作れるので、それを使ってチェックするだろう。
（１）単体（１セルの中身）チェック
（２）相対チェック　1セルのあるデータの時に多分同行で、他セルのデータが許容されるかどうか
（３）他行や他シートデータとの関連（組み合わせ）チェック
こういう分類で、質問の説（チェック項目）を書き直してみたらどうか。

お礼

大変お世話になっております。この度も質問文や文言についてのご指摘ありがとうございました。
情けない事に、Excelは超初心者で、何処から手を付けて良いやら分からない状態です。しかしながら、こうしてご親切な皆様のご教示を頂く毎に、少しずつではありますが、Excelの式の表現を学ばせて頂ける環境がある事に大変感謝致します。
VBAも学ぶできなのですが、仕事を覚える事で精一杯でまだまだそこまでの余裕が生まれていません。
今後ともご指導宜しくお願いします。

回答No.2

kkkkkm

凄く長くなりましたが、以下のような事でしょうか。

=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","リスト外"))))

回答No.1

hiro_1116

データ形式の説明が長々書かれていますが、要は下記の２点をチェックすれば良いのですか？
＞・利用区分が『大学』のときは、J列[利用者ID]、K列[利用者名]、P列[課題ID]、N列[利用区分]、P列[課題ID]に空白の欄があるのでは記載抜けのミスになります。
＞・利用区分が同じ『大学』で、利用者名が重複している場合、課題IDが違っていれば問題ないです。

3263件のデータのうち、利用区分「大学」がどのくらいの数を占めるのか、利用者名の重複がどれだけあるのかが分かりませんが、利用区分「大学」のデータを抽出した後で、名前でソート、更に課題IDでソートすれば手作業でもやれそうですね。

お礼

早速のご教示ありがとうございました。
データ入力ミスチェックの条件が複雑で、自分でも整理が出来なてない状態で質問をしてしまいました。
Excelの超初心者ですので、これからも少しずつでもExcelの関数式に慣れていこうと思います。
今後ともご教示宜しくお願いいたします。