［Excel］ 2つの表のセル同士の突合方法について質問です。 添付の表で、 {=AND(EXACT

［Excel］

2つの表のセル同士の突合方法について質問です。

添付の表で、

{=AND(EXACT(C4:F4,INDIRECT("I"&MATCH(C4,$I$1:$I$6,0)&":L"&MATCH(C4,$I$1:$I$6,0))))}

上記の関数を実行した場合、「行単位」で完全一致しているかどうかで結果を返してくれると思いますが、

「セル単位」ですべてのセルに結果を求めたい時はどのような関数にすれば良いでしょうか。

よろしくお願いします。

No.4 回答者： 銀鱗 回答日時： 2023/09/08 04:37

その数式が何をやっているのか分かっているのであれば、

自力で工夫できるので質問することもないでしょうから、
分からないという事ですね。

この数式。本来何をやりたいかというと

＝AND( EXACT(C3,I3) , EXACT(D3,J3) , EXACT(E3,K3) , EXACT(F3,L3) )

という比較ですね。
（配列数式はC3:F3の範囲を C3、D3、E3、F3 と分解するために指定しています）

しかし EXACT の2つ目の範囲が不確定なので数式で与える必要がある。
それが MATCH関数です。
　＝MATCH(C3,I1:I6,0)
でC3セルと同じ値をI列から検索し、一致した値がどの行にあるのかを取得し、それを参照する行番号としています。
取得した数値を使った文字列を INDIRECT関数で範囲に置き換えですね。

見た目は分かりやすいけど、ぶっちゃけスゲー面倒。
OFFSET関数で指定すれば、同じパラメータを繰り返さず同じことができます。
　　{=AND(EXACT(C3:F3,OFFSET($I$1,MATCH(C3,$I$1:$I$6,0)-1,0,1,4)))}
とかね。
※ OFFSET関数の使い方は自身で調べてください。

・・・

と、いうことで、AND関数を使わない形にすればセルひとつずつの比較になります。
ただし今度はセルの数だけ数式を作る必要があります。

とりあえず、元の数式を修正するのであれば……

C列の比較は
　=EXACT(C3,INDIRECT("I"&MATCH($C3,$I$1:$I$6,0)))
D列の比較は
　=EXACT(D3,INDIRECT("J"&MATCH($C3,$I$1:$I$6,0)))
E列の比較は
　=EXACT(E3,INDIRECT("K"&MATCH($C3,$I$1:$I$6,0)))

こんな風になります。

なお、OFFSET関数を使った場合は、
これが
　{=AND(EXACT(C3:F3,OFFSET($I$1,MATCH(C3,$I$1:$I$6,0)-1,0,1,4)))}
こうなる
　=EXACT(C3,OFFSET($I$1,MATCH($C3,$I$1:$I$6,0)-1,COLUMN(A1)-1,1,1))
で、C3セルに対応するセルに入力後に、
　下2つへフィルコピー、さらに横3つへフィルコピー
するだけで良い。
配列数式にする必要はありません。
ポイントはCOLUMN関数で横方向に指定するセルをずらしているところ。


・・・余談・・・

たぶん、条件付き書式の条件として使いたいのだろうと推測して、エラー対策はしていません。
エラーは条件成立とみなされないだけなんで処理する必要なかったりします。

質問の例であれば、
　C3セルからF5セルを選択し、
　C3セルに入力できる状態で、
　OFFSET関数を使った数式を条件として入力、
　確定後、セルの修飾方法を選択する。
で、等しい時にセルを修飾することで同じであることを判別できます。
違うところだけ色を付けたい場合は、数式をNOT関数に入れて「等しくない」を条件にしてください。
　=NOT(EXACT(C3,OFFSET($I$1,MATCH($C3,$I$1:$I$6,0)-1,COLUMN(A1)-1,1,1)))


・・・独白・・・

ああ、暇なんで
「代わりに数式を作れ。オレ様の成果として使ってやるｗｗｗ」
って質問に数式まで作って説明しちゃったよ。

本来は「何が分からないのかを示してください」と問いただすところなんですけどね。
「作業依頼」はルール違反なんだよ。
前の質問で指摘されなかったのが悪かったのかな。
図に乗って同じように作業依頼を出しちゃってるんだ。
見捨てられたら、何もできない子になっちゃうから注意しましょう。

No.3 回答者： fujillin 回答日時： 2023/09/07 17:58

こんにちは

ご質問の文章から、一方向の照合で良いものと解釈していますが、前回は右の表から左の表を参照するのに対して、今回は左の表から右の表を参照するように変わっているようですね。

＞すべてのセルに結果を求めたい時は～～
個々のセルの「一致/不一致」を表示したいのだと思いますが、どのような結果にしたいのかが記述されていません。
勝手に解釈して、結果は1セルに表示するものと考えました。
項目（4項目）に対して、それぞれが合致していれば「〇」不一致なら「×」として、「○○○○」のように表示するものとしてみました。

ご提示のレイアウト（行番号が不明ですが・・）で、Ａ3セル（＝りんごのある行）に
=IF(C3="","",IFERROR(TEXTJOIN("",0,"〇",INDEX(MID("×〇",(D3:F3=OFFSET(J$1:L$1,MATCH(D3,J:J,0)-1,0))+1,1),)),"××××"))
の式を入力して、下方にフィルコピーではいかがでしょうか。

ご例示の場合であれば、結果は上から順に、
　○○○○
　○○○○
　○○××
となります。

「○○××」の意味は、第3、4項目（＝単価、合計）が不一致という意味です。
最初の項目が「×」の場合は、右側の表に該当する商品がないことを意味します。

※ スピル機能が使える環境であれば、式はもう少し簡単にできることでしょう。

No.2 回答者： goomania 回答日時： 2023/09/07 15:33

どこかで見たようなご質問だなと思ったら、同じ方のご質問でした。

https://oshiete.goo.ne.jp/qa/13586196.html

>2つの表のセル同士の突合方法
というご希望ですで、私の考えるところ、どちらかが正しくて、どちらかが誤りという話ではなく、単に違いを明らかにするということだと理解しました。

ということは、「①左表から右表を見る」と「➁右表から左表を見る」という2つの視点が必要だと思います。

①の左表の項目の対応位置の照合結果を左表の下に表示する
➁の右表の項目の対応位置の照合結果を右表の下に表示する

という2点を実現させるため、バージョン365の環境ですが、画像ではC8セルに

=IF(C3="","",IFERROR(IF(EXACT(C3:F3,INDEX($I$3:$L$6,MATCH(C3,$I$3:$I$6,0),0)),"OK","相違あり"),"項目なし"))･･･(a)

という数式を記述して、下方向へコピーしています。
さらに、画像ではI8セルに、

=IF(I3="","",IFERROR(IF(EXACT(I3:L3,INDEX($C$3:$F$5,
MATCH(I3,$C$3:$C$5,0),0)),"OK","相違あり"),"該当なし"))･･･(b)

という数式を記述して、下方向へコピーしています。

なお、スピル機能の使えないバージョンでは、数式(a)(b)とも「配列数式」とする必要があります。

No.1 回答者： d-q-t-p 回答日時： 2023/09/07 09:55

どのセルとどのセルの話ですか？

あと Excelのバージョンはいくつですか？