データの記載が正しい入力か判定したい

質問

大変お世話になっております。
頻繁にご質問をしてしまい、大変恐縮です。

今回は、下記に記載した【利用者の売上げ】のデータに記載されている、『ユーザーID』と『利用者』について、基準となる【職員マスタ】の記載通りになっているかの判定を行う事と、
データ全体から利用区分が『外部利用』、『社内利用』の時の、
『ユーザーID』と『利用者』記載が誤っているかもと思われる箇所の判定を行いたい事です。
※この『誤っているかも』の判定に、基準となるデータがありません。なので、データ全体をなめて、『外部利用』、『社内利用』の時の、『ユーザーID』と『利用者』の記載が同じでなかった記載の時は『誤りかも』と判定するのが良いかなと思っています。

下記、【利用者の売上げ】のフォーマットの通り、データは手入力の為、ユーザーIDを間違えていたり、苗字と名前の間に全角スペースが入っていたり、苗字と名前の間に半角スペースが無かったりしています。

『ユーザーID』は数字入力です、その数字が合っていれば正しい記載と判定します。
『利用者は苗字と名前が一致していれば、苗字と名前にスペースが有っても無くても、正しい記載と判定します。
『ユーザーID』の記載が基準となる【職員マスタ】の『職員ID』と一致しているか？
又、『利用者』の記載が基準となる【職員マスタ】の『氏名』と一致しているか？
【利用者の売上げ】と【職員マスタ】は別々のExcelデータですが、
判定の式を作る為には、同じExcelのファイル内に入れた方がやり易いかなと思っています。
自分の能力では、以前ご教示頂いた式を少し変えた下記の式ぐらいしか思いつきませんでした。
上記のような複雑な判定がExcelの数式で可能でしたら、どうかご教示を頂きたいです。
=IF(N2<>"内部利用","内部利用以外",IF(COUNTA(J2,K2,P2,N2)<4,"未入力有",IF(SUMPRODUCT((職員マスタ!$A$2:$A$3346=J2)*(職員マスタ!$B$2:$B$3346=K2))>0,"OK","職員マスタに登録無し")))

・下記は【利用者の売上げ】のフォーマットです。データはA列～AS列まで入っています。
　AT列は職員マスタとデータのユーザーID,利用者が一致しているかの判定の為の数式を入れる列になります。
A列　　　　　　　J列　　　 K列　　　N列　　　　　　　　　　　　　　　　　　AT列
装置ID,,,,,,,,,ユーザーID,利用者,,,利用区分,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,数式入力
AB23,,,,,,,,,234,岩田 　太郎,,,内部利用,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
AB254,,,,,,,,,234,岩田 太郎,,,内部利用,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
AB543,,,,,,,,,334,岩田太郎,,,内部利用,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
AB334,,,,,,,,,2354,菊川　五郎,,,外部利用,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
AB334,,,,,,,,,3254,菊川 五郎,,,外部利用,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
AB23,,,,,,,,,2354,菊川五郎,,,外部利用,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
AB23,,,,,,,,,823456,田中雄一郎,,,社内利用,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
AB912,,,,,,,,,823456,田中　雄一郎,,,社内利用,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
AB912,,,,,,,,,456,田中 雄一郎,,,社内利用,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
AB254,,,,,,,,,678,加藤 一郎,,,内部利用,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
AB543,,,,,,,,,978,加藤 一郎,,,内部利用,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
AB912,,,,,,,,,678,加藤一郎,,,内部利用,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

・下記は、【職員マスタ】のフォーマットです。
A列　　　　　B列
ユーザID　　氏名
234,　　　岩田 太郎
678,　　　加藤 一郎

　A列＝職員ID、B列＝氏名
　氏名は漢字以外に片仮名で書かれた名前の記載もあります。
　苗字と名前の間は必ず半角スペースが入っています。
　『ジョンソン 花子』、『サルルン アニワシール マザール』のように半角スペースが入っています。

回答No.1

SI299792 · ベストアンサー

このような感じでいいですか。
あなたが上げたデータには、IDがあるが、名前が不一致の例が無かったので追加しました。
AT2 =IFERROR(IF(K2="","",IF(SUBSTITUTE(SUBSTITUTE(K2,"　","")," ","")=SUBSTITUTE(VLOOKUP(J2,職員マスタ!A:B,2,0)," ",""),"一致","不一致")),"ID無し")
下へコピペ。

補足

大変お世話になっております。
SI299792さん、早速のご教示ありがとうございました。
SUBSTITUTE関数を使うと、文字列置換が出来ちゃうんですね！そして、このような使い方が出来るんですね！

先ずは、利用区分の『内部利用』についての判定をおこなうのに、ご教示頂いた式を、下記のようにしてみました。
=IF(O2<>"内部利用","内部利用以外",IFERROR(IF(L2="","",IF(SUBSTITUTE(SUBSTITUTE(L2,"　","")," ","")=SUBSTITUTE(VLOOKUP(K2,職員マスタ!A:B,2,0)," ",""),"ID名前共に一致","職員マスタにID有り、名前記載誤り")),"職員マスタに名前有り、ID記載誤り"))
Excelの超初心者でこの式の意味を良く理解出来てないのですが、自分の勝手な解釈で判定をして見た処、
№14,15では、職員マスタにはIDも氏名も登録が無い場合でも、『職員マスタに名前有り、ID記載誤り』と判定されました。
№,装置ID,,,,,,,,,ユーザーID,利用者,,,利用区分,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,数式入力1,数式入力2,　　　　　　　　　　　　　　　　　コメント
1,AB23,,,,,,,,,234,岩田 　太郎,,,内部利用,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,ID名前共に一致,ID名前共に一致,
2,AB254,,,,,,,,,234,岩田 太郎,,,内部利用,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,ID名前共に一致,ID名前共に一致,
3,AB543,,,,,,,,,334,岩田太郎,,,内部利用,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,職員マスタに登録ないID,職員マスタに名前有り、ID記載誤り,この判定の時は、職員マスタに登録されているユーザID＝234、氏名＝岩田 太郎を表示させたい
4,AB334,,,,,,,,,2354,菊川　五郎,,,外部利用,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,職員マスタに登録ないID,内部利用以外,
5,AB334,,,,,,,,,3254,菊川 五郎,,,外部利用,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,職員マスタに登録ないID,内部利用以外,
6,AB23,,,,,,,,,2354,菊川五郎,,,外部利用,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,職員マスタに登録ないID,内部利用以外,
7,AB23,,,,,,,,,823456,田中雄一郎,,,社内利用,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,職員マスタに登録ないID,内部利用以外,
8,AB912,,,,,,,,,823456,田中　雄一郎,,,社内利用,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,職員マスタに登録ないID,内部利用以外,
9,AB912,,,,,,,,,456,田中 雄一郎,,,社内利用,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,職員マスタに登録ないID,内部利用以外,
10,AB254,,,,,,,,,678,加藤 一郎,,,内部利用,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,ID名前共に一致,ID名前共に一致,
11,AB543,,,,,,,,,978,加藤 一郎,,,内部利用,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,職員マスタに登録ないID,職員マスタに名前有り、ID記載誤り,この判定の時は、職員マスタに登録されているユーザID＝234、氏名＝岩田 太郎を表示させたい
12,AB912,,,,,,,,,678,加藤 一郎,,,内部利用,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,ID名前共に一致,ID名前共に一致,
13,AB912,,,,,,,,,234,菊川　五郎,,,内部利用,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,ID一致したが名前不一致,職員マスタにID有り、名前記載誤り,この判定の時は、職員マスタに登録されているユーザID＝234、氏名＝岩田 太郎を表示させたい
14,AB912,,,,,,,,,34,菊川 三郎,,,内部利用,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,職員マスタに登録ないID,職員マスタに名前有り、ID記載誤り,職員マスタにこのIDも氏名も無いが？
15,AB912,,,,,,,,,4,市毛 三郎,,,内部利用,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,職員マスタに登録ないID,職員マスタに名前有り、ID記載誤り,職員マスタにこのIDも氏名も無いが？

可能でしたら、職員マスタを基準(こちらが正しい)として、データのユーザIDと利用者について、それぞれに判定が出来たら、
記載誤りが明確になって良いかなと思っております。
数式の書き方をご存じでしたら、ご教示頂けると大変幸いです。

回答No.4

SI299792

貴方の上げた関数は、難しすぎるので無視します。どうしてもやりたいなら、有償でお願いすることをお勧めします。
Excel2019 ならifs が使えますね。
私が上げた方法ですが。

職員マスター
C2: =SUBSTITUTE(B2," ","")
下へコピペ。

利用者の売上
AU2 =IFERROR(ifs(K2="","",K2=INDEX(職員マスタ!A:A,MATCH(AX2,職員マスタ!C:C,0)),"一致",TRUE,INDEX(職員マスタ!A:A,MATCH(AX2,職員マスタ!C:C,0))),"無し")
AV2 =IFERROR(ifs(K2="","",O2<>"内部利用","内部利用外",AX2=VLOOKUP(K2,職員マスタ!A:C,3,0),"一致",TRUE,VLOOKUP(K2,職員マスタ!A:C,3,0)),"無し")
AX2 =SUBSTITUTE(SUBSTITUTE(L2," ",""),"　","")
下へコピペ。

Ｃ列、AX列がワークエリアです。目障りなら非表示にして下さい。

お礼

ご返答ありがとうございました。
今回は、文字の間の空白を取り去る為に使うSUBSTITUTE(文字置換)関数や、VLOOKUP関数の使い方など、
ご教示頂きましてありがとうございました、大変参考になりました。

試行錯誤した結果、下記の式でチェックをする事にしました。
内部利用のみのチェックになりますが、文字の間に空白が3つ入っている利用者に対応させる為です。
=IF(N2<>"内部利用","内部利用以外",IFERROR(IF(L2="","",IF(TRIM(SUBSTITUTE(SUBSTITUTE(L2,"　","")," ",""))=TRIM(SUBSTITUTE(SUBSTITUTE(VLOOKUP(K2,職員マスタ!A:B,2,0),"　","")," ","")),"OK ","NG " & K2 &"、"& INDEX(職員マスタ!A:B,MATCH(K2,職員マスタ!A:A,0),2))),"NG 職員マスタに【" & K2 &"】無し"))

私のようなわがまま者には有料でお願いするべきなのでしょうが、何分にもお金も時間も余裕が無いのが現状です、お察し下さい。
兎に角、先へと進むしか無いのが現状です。
これからも一生懸命勉強して行きますので、皆様のお力添えをよろしくお願い致します。
今回のご質問は、これて完了とさせて頂きます。

回答No.3

SI299792

Excel のバージョンは何ですか。（最初に聞くべきでした）

　補足に画像はつけれません。画像を付けたい場合、新しい質問を立てます。どうせただだし。

　あなたが上げた表は複雑すぎ、混乱します。それにそれでは、対応できない場合があります。
17列の様なデータがあった場合、どっちが正しいか人間が判断するしかありません。２列使うのであれば、画像の様にしてはどうでしょうか。両方出ているという事は、どっちかが違っている。それは人間が判断する。

　ワークエリア…空いている列を使い、途中まで計算する。その領域を指します。画像のAX、Ｃ列です。余計な列を使うので嫌がる人が多いですが、数式が簡単になり、計算速度が上がります。今回プルダウンは関係ありません。（データ件数が多いなら、使った方がいいです）

補足

大変お世話になっております。
早速のご教示ありがとうございました。

・『Excel のバージョンは何ですか。』
　2019を使用しています。
　4月からExcelを用いた仕事に変わった為、2010から2019へ更新しました。

・『17列の様なデータがあった場合、どっちが正しいか人間が判断するしかありません。』
　昨夜から教示頂いた数式を用いて、下記のような数式を考えました。
=IF(O2<>"内部利用","内部利用以外",IFERROR(IF(L2="","",IF(SUBSTITUTE(SUBSTITUTE(L2,"　","")," ","")=SUBSTITUTE(VLOOKUP(K2,職員マスタ!A:B,2,0)," ",""),"OK "&K2&"、"& INDEX(職員マスタ!A:B,MATCH(K2,職員マスタ!A:A,0),2),"NG " & K2 &"、"& INDEX(職員マスタ!A:B,MATCH(K2,職員マスタ!A:A,0),2))),"NG 職員マスタに【" & K2 &"】無し"))

　結果は以下のようになり、取り敢えずこれで使えるかなと思っています。
　OK 234、岩田 太郎
　OK 234、岩田 太郎
　NG 職員マスタに【334】無し
　内部利用以外
　内部利用以外
　内部利用以外
　内部利用以外
　内部利用以外
　内部利用以外
　OK 678、加藤 一郎
　NG 職員マスタに【978】無し
　OK 678、加藤 一郎
　NG 234、岩田 太郎
　NG 職員マスタに【34】無し
　NG 職員マスタに【4】無し

・１つ、可能でしたらご教示頂きたい事があります。
　下記のように利用者の文字の間に3個以上の空白があるとNGとなるようです。
　この対処の仕方をご存じでしたら、教えて頂けると大変幸いです。
『アールッド アユマン　マッサン　ザキ』
『ラウト クルスュナ　アマリ』
『ビン ドゥーバン エレム アマー アブトゥラ』

回答No.2

SI299792

随分ややこしいですが、
=IF(O2<>"内部利用","内部利用以
利用区分はＮ列ではないのですか。コロコロ変わると作れません。
ここが変わると他に影響しますが他の列はどう変わるのですか。

＞この式の意味を良く理解出来てないのですが
ワークエリアを使えば、簡単な数式にする事が可能です。その方がいいですか。

補足

大変お世話になっております。早速のご教示ありがとうございました。

・大変申し訳ありません、昨日、左端に1列[連番]追加したのを、ご連絡しておりませんでした。
　ご説明するのに、補足へ画面イメージを貼り付けたかったのですがやり方が分からなかった為、
　見やすいように連番を付けたデータで数式を実施していました。なので、『利用区分』はＮ列で間違いないです。
　『利用区分はＮ列ではないのですか。コロコロ変わると作れません。』

・ワークエリアをネットで調べると、
　ワークエリアでプルダウンにてシート名を選択したら、
　選択したシートが表示されるように出来る例が載っておりましたが、そのような事でしょうか？
　私の扱っている【職員マスタ】は4000件程データが入っており、【利用者の売上げ】も1年間分で6万件程になります。可能でしたら、数式での判定が効率が良いと考えています。

・ご紹介頂いた、下記式について、動作を簡単でよいので、解説を頂けたら大変幸いです。
=IFERROR(IF(K2="","",IF(SUBSTITUTE(SUBSTITUTE(K2,"　","")," ","")=SUBSTITUTE(VLOOKUP(J2,職員マスタ!A:B,2,0)," ",""),"一致","不一致")),"ID無し")