基本の別シートの値と一致・不一致のチェック

質問

2つ目のシートのEセルとGセルの値が、1つ目のシートのAセルとQセルの値と一致しているかチェックをしたいのですが、
実際にどのような式を入れたら良いのかわかりません。　大変困ってしまっています。
何方か、ご教示頂けると大変幸いです。

１つ目のシートには基準となる値が入っています。2つ目のシートは入力されたデータが入っています。
比較するセルの値は1つ目のシートは空白は入っていませんが、2つ目のシートは、空白や全く違った値が入っています。
結果は、以下のように出力させたいです。

2つ目のシートのEセルの値と1つ目のシートのAセルの値が一致し、1つ目のQセルの値と2つ目のGセルの値が一致した時、2つ目のシートの最後の列へ『OK　Aセルの値、Qセルの値』、
2つ目のシートのEセルの値と1つ目のシートのAセルの値は一致したが、1つ目のQセルの値と2つ目のGセルの値が一致しなかった時、2つ目のシートの最後の列へ『NG　Aセルの値、Qセルの値』

1つ目のシートのフォーマットは、以下のようです。
Aセル　　　　　Qセル
果物　　　　　ランク
オレンジ　　　　B
レモン　　　　　C
バナナ　　　　　A
パイナップル　　D

2つめ目のシートのフォーマットは、以下のようです。
Eセル　　　　　Gセル
果物　　　　　ランク
バナナ　　　　　D
空白　　　　　　空白
スルメイカ　　　空白
鞄　　　　　　　　B
レモン　　　　　　C
メロン　　　　　　A
バナナ　　　　　　A

以上　ご教示宜しくお願いいたします。

回答No.4

kkkkkm · ベストアンサー

どちらのシートも項目(果物、ランク)が1行目から始まって2行目から果物名などが入力されているとして
1つ目のシートのデータはとりあえず10行目までの式です。実際の行に2か所合わせてください。
2つ目のシートの最後の列の2行目に以下の式を入れて下にコピーして試してみてください。

=IF(E2="","",IFERROR(IF((SUMPRODUCT(('1つ目のシート'!$A$2:$A$10=E2)*('1つ目のシート'!$Q$2:$Q$10=G2)))>0,"OK "&E2&"、"&G2,"NG " & E2 &"、"& INDEX('1つ目のシート'!A:Q,MATCH(E2,'1つ目のシート'!A:A,0),17)),""))

1つ目のシートに
バナナ　　　　　A
と
バナナ　　　　　C
が上記の順番であった場合

2つめ目のシートの
バナナ　　　　　D
の最終列には
NG バナナ、A
となります。

補足

何時も大変お世話になっております。
今回も助けて頂き大変感謝の気持ちで一杯です。

ご教示頂いたセルの式を試して見た所、
OKは問題無く表示されましたが、NGは表示されませんでした。
データは、記載ミスがありますので、NGは表示されるはずなのですが。

1つ目のシートでは、Aセル、Qセル、
2つ目のシートでは、Eセル、Gセルと記載しましたが、
実際は、
1つ目のシート(データ数＝325行)では、Dセル、Gセル、
2つ目のシート(データ数＝3265行)では、Pセル、Kセルになります。
1つ目のデータは2行目から、2つ目のデータは3行目からが実際のデータになります。

下記の式を2つ目のシートの最後の列の3行目に入力して最終列までコピーしました。
=IF(P3="","",IFERROR(IF((SUMPRODUCT(('rkad1160_20220428'!$D$2:$D$325=P3)*('test_20220428'!$G$2:$G$325=K3)))>0,"OK "&P3&"、"&K3,"NG "&P3&"、"&K3 INDEX('test_20220428'!D:G,MATCH(P3,'test_20220428'!D:D,0),17)),""))

私の貧弱な知識では、NGが出るように色々試して見ましたが、式を修正する事が出来ませんでした。
大変お手数でも、式をご確認頂き、NGが出るように式を直して頂けたら大変幸いです。

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

回答No.8

kkkkkm

> Kセルの値とGセルの値の間に『、』等を付ける事は不可能でしょうか？

付加したい文字列を「"」で囲んで&で繋いでやるとその個所に表示することが可能です。
INDEX(式)で取得したデータがG列のデータですので

K3&"、"& INDEX(

としてみてください。

お礼

早速のご教示ありがとうございました。
『、』を付ける事ができました、これでデータ入力ミスチェックが大分便利になりました。

何時もたどたどしい説明にも関わらす、
私がしたい事を的確にサラッとご教示下さります。
4月からExcelを主に使用する業務となり、全く身動きが取れていません。業務の期限も迫る中、パニックと戦い続ける毎日です。
頼れる場所があり、サラッと教えて下さる方がいる事は、業務を続ける上で欠かせない存在であります。
どうぞ、今後ともよろしくお願いいたします。

回答No.7

kkkkkm

回答No.6の蛇足ですが

もし、1つ目のシートで基準となる値の果物名が重複しないのでしたら
バナナ　　　　　A
バナナ　　　　　C
のようなことがない場合。

2つ目のシートのK3に以下の式を入れて必要なだけ下にコピーしておけば

=IF(P3="","",IFERROR(VLOOKUP(P3,test_20220428!D:G,4,FALSE),"該当なし"))

利用者が果物名を入力した時点でランクは勝手に表示されますので、ランクのミスは無くなります。
また、果物名をミスすると該当なしと表示されますので果物名の入力ミスもその時点でわかります。
ただし、リスト外の果物名入力も可能な場合、その時にも該当なしとなりますので、判断ミスはあるかもしれません。

補足

早速のご確認とご教示ありがとうございました。
ご質問のrkad1160_20220428は、test_20220428です。
補足に記載する時に記載ミスをしておりました、申し訳ありません。

下記の通りに入力して、無事NGの時も表示をする事が出来ました。
1行の式で、ここまで出来てしまうのですね、大変素晴らしいです。
=IF(P3="","",IFERROR(IF((SUMPRODUCT(('test_20220428'!$D$2:$D$326=P3)*('test_20220428'!$G$2:$G$326=K3)))>0,"OK "&P3&"、"&K3,"NG "&P3&"、"&K3& INDEX('test_20220428'!D:G,MATCH(P3,'test_20220428'!D:D,0),4)),""))

この表示でも十分なのですが、NGとなった時の表示でKセルの値とGセルの値の間に『、』等を付ける事は不可能でしょうか？

入力用ファイルについての入力ミス対策についてのご教示もありがとうございました。
各部署から提出されるExcelのファイルは、その部署毎に手入力されており、データ入力の統一化が進んでおりません。
統一にはまだ時間がかかりそうな状況です。
ご教示頂いた入力ミス対策の式は、別の業務で使わせて頂きます。

回答No.6

kkkkkm

NGが出ないのとは直接関係ないと思いますが
1つ目のシートがrkad1160_20220428とtest_20220428で違っていますが正しいでしょうか。

NGが出ないのは

"NG "&P3&"、"&K3 INDEX('
K3が付加されていますが、K3が必要だとしたら後ろに&が必要です。
"NG "&P3&"、"&K3& INDEX(

INDEX('test_20220428'!D:G,MATCH(P3,'test_20220428'!D:D,0),17)
の最後の数値「17」は範囲D:Gの横位置なので取り出したいG列の「4」になります。
元がA:Qだったので「17」でした。
INDEX(test_20220428!D:G,MATCH(P3,test_20220428!D:D,0),4)

上記で試してみてください。

回答No.5

chayamati

★補足有難うございます。
＞元は、Excelファイル2個でした。
　データ入力用のファイルへ規程値データをコピーしています。
　規程値のデータに沿って、各部署がデータ入力用に手入力をする為、
　入力ミスが発生してしまいます。
　そのミスを見抜きたいのです。
★添付のように、データ入力枠と規定値枠を設定
1.規定値枠の品名列は全ての値を入力できます
2.ランク列：データの入力規制のリストにA､B､C、Dを定義
3.品名＆ランク列は式=D3&" "&E3　で文字列結合
4.データ入力枠の品名＆ランク列に入力規則のリストに規定値の品名＆ランクを定義
　これで品名＆ランクは規定値以外の値は入りません
　チェックの必要はありません
　規定値表の管理
5.入力規則のツールはデータリボンの右よりにあいます。
　見つからなければまた補足ください
６.データ入力枠に日付を追加しました。
　入力規則で日付の期間指定も可能です

お礼

ご教示ありがとうございました。入力ミス対策の方法ありがとうございました。

今回のデータ入力については、各部署内で独自の入力データを作成しており、
独自の入力データからこちらの入力データへデータをコピペをしている部署があります。
各部署毎で都合があるらしく、全てのデータのフォーマットを統一させるのは難しい状況です。

ご教示頂いた文字列結合やデータ入力規則のやり方、大変参考になりました。
分かり易いご説明を頂きありがとうございます。今後ともよろしくお願いします。

回答No.3

chayamati

補足願います
＞2つ目のシートのEセルとGセルの値が、
　1つ目のシートのAセルとQセルの値と一致しているか
　チェックをしたいのですが、
1.Sheetを２つ使い分けておられているのは何故ですか
　何故ならセル名を特定するためにSheet名で修飾することになります
2.一つ目のSheetのA列の値は重複の無いユニークな値ですね
3.２つ目のSheetのE列にはバナナ重複しています、ランクがAとD
　『NG　Aセルの値、Qセルの値』
　これは式だけでは無理です。マクロの記録、またはVBAが必要です。

補足

説明が足りずでもうしわけありません。
元は、Excelファイル2個でした。データ入力用のファイルへ規程値データをコピーしています。
規程値のデータに沿って、各部署がデータ入力用に手入力をする為、
入力ミスが発生してしまいます。そのミスを見抜きたいのです。
データ入力用で、1行毎にセル式を入れて判定は難しいでしょうか？

回答No.2

imogasi

質問の表現が不十分で、尋ねている状況がよくわからない。
エクセルの経験が十分でないのだろうことと、他人に説明する、ということを甘く見ないでほしい。また他人は、他人の過去の経験から、それなりの思い込みというものもあって、それが邪魔をする、こともあるのだ。
また、セルの行を重視した説明表現を心がけること。
（例）Aセルーー＞A列の各セルというべき。
（１）２シートのデータ実例（模擬）を（10セル＝１０行分ぐらい）挙げて
（２）どういう比較をするのか、１、２例の場合を、文章で・エクセルの語句で説明するべきだ。こことここがこうだから、ここを探して、こことここを比べる、とかのように。同僚にこの作業を、手作業で、もし頼むときは、そういう説明を言葉で、するだろう。
これは、将来ぶつかると思う、プログラミングの作業そのものの思考過程だと思う。
＝＝
例えば
（１）２つ目のシートで出てきたバナナ（E列）を１つ目のシート（のA列）にあるかを探し、
（２）あれば、１つ目のシート同行Qセルのランクと、２つ目のシートのG列のランクが一致しているか、チェックする
などか（？）想像して書いているが。
両シートでバナナなどの出現順序は同じではないだろう。存在するとも限らないだろう？だったら単純なIF関数での比較＝＞式の複写、ででは、出来ないのだろう。
１つ目のシートに、２つ以上あればどうする？
（質問者には「当然という」会社の業務関連の知識は、読者にはないのだよ。）
すると、２つ目のシートのバナナを探すという処理が必要で、複数該当があり得るなど複雑なことになる。
探索は関数では難しい点がある。VLOOKUP関数、Match関数、なども１つ目しが探さない、とかの特徴（制約）がある。

補足

親切・丁寧なご指摘ありがとうございました。
ご指摘の、(1)、(2)の通りです。
下記のように、1つ目のシート＝規程値データ、2つ目のシート＝入力データとして、
再度データを追加しました。　規程値データの記載は重複無しです。

やりたいことは、
規程値データA列と入力データE列が一致した場合、入力データG列の記載が規程値データQ列と一致しているか、一致してないかの
判定を行いたい。

1つ目のシートのフォーマットは、以下のようです。(シート名＝規程値データ)
　　　A列　　　　　　Q列
【項目：果物】　【項目：ランク】
　　オレンジ　　　　　B
　　レモン　　　　　　C
　　バナナ　　　　　　A
　　パイナップル　　　D
　　キュウイ　　　　　E
　　マンゴ　　　　　　F
　　トマト　　　　　　H
　　スイカ　　　　　　G
　　ナス　　　　　　　J
　　トウモロコシ　　　I

2つめ目のシートのフォーマットは、以下のようです。(シート名＝入力データ)
　　　E列　　　　　　　G列
【項目：果物】　　　【項目：ランク】
　　果物　　　　　　　　D
　　バナナ　　　　　　空白
　　空白　　　　　　　空白
　　スルメイカ　　　　　B
　　鞄　　　　　　　　　C
　　レモン　　　　　　　A
　　メロン　　　　　　　A
　　バナナ　　　　　　　A
　　トウモロコシ　　　空白
　　空白　　　　　　　空白
　　キュウイ　　　　　　E
　　キュウイ　　　　　　B

回答No.1

kon555

if関数での一致確認で実現できます。
http://marus.info/excel-if-20/

　複数の条件が絡むので、バージョンによってはifs関数を使う方が分かりやすいと思います。
https://www.tipsfound.com/excel/04if2