要素ごとに複数の行を持つ 2 つのリストを比較するにはどうすればよいですか?

Question

私は、1 人あたり複数の行がある 2 つの「人」リストを、同じ行が含まれているかどうかわからない別のリストと比較する方法を見つけようとしています。

各人には、その人に固有の単一の ID があります。どちらのリストでも、各人に対して 1 行から 4 行の行があります。行の数が同じである必要はなく、順序も同じでない場合もあります。それ以外では、各人について、各行のほぼすべてのセルは冗長で同一になります (主に、変化しない個人情報)。可能な行 (「アイテム」) は常に同じです: ジャケット、帽子、靴、アクセサリー。各行の最後の 2 つのセルは、比較する必要がある実際の値です。

英語は私の第二言語ですが、言葉で表現するのにとても苦労しています。そこで、Google スプレッドシートで例を作成しました。

また、リンクしている例のように、特定の列と形式を持つこれらのテーブルの両方を取得していますが、それを変更したり並べ替えたりすることはできません。私にできるのは、「Went over?」列を追加することだけです。

私には、VLOOKUP のより洗練されたバージョンのようなものが必要なように思えます。つまり、一意の ID (列 B) を検索し、何らかの方法でアイテム (列 E) を検索して、その特定のアイテム (列 F) の金額値を返すことができるものです。その後は、IF A=B なら「OK!」などというだけです。しかし、いわば二重の VLOOKUP のように機能する関数は知りません。

SU の皆さん、スクリプトを必要とせずに N の「比較」列を作成する方法を教えてください。そうでなければ、各人の各要素と、他のリストの同じ人の同じ要素を比較するにはどうすればよいでしょうか。

例表1

名前	ベンダーID番号	セックス	年	予想されるアイテム	予想金額
ワッツ、トム	6505581	ま	21	ジャケット	44
ワッツ、トム	6505581	ま	21	帽子	20
ワッツ、トム	6505581	ま	21	靴	55
ワッツ、トム	6505581	ま	21	アクセサリー	18
スミス、ジェーン	702452	ふ	32	帽子	56
スミス、ジェーン	702452	ふ	32	靴	20
スミス、ジェーン	702452	ふ	32	アクセサリー	26
ドウ、ジョン	1235554	ま	54	ジャケット	80
ドウ、ジョン	1235554	ま	54	アクセサリー	20

例表2

名前	ベンダーID番号	セックス	年	販売商品	販売金額	渡った？
ワッツ、トム	6505581	ま	21	ジャケット	44	わかりました！
ワッツ、トム	6505581	ま	21	靴	65	+10
ワッツ、トム	6505581	ま	21	帽子	20	わかりました！
ワッツ、トム	6505581	ま	21	アクセサリー	18	わかりました！
スミス、ジェーン	702452	ふ	32	アクセサリー	26	わかりました！
スミス、ジェーン	702452	ふ	32	靴	10	-10
スミス、ジェーン	702452	ふ	32	帽子	56	わかりました！
ドウ、ジョン	1235554	ま	54	ジャケット	95	+15
ドウ、ジョン	1235554	ま	54	アクセサリー	22	2

Answer 1

テーブルなしのソリューションとLET

ここで、ヘルパー列を使用して、最初に予想金額を取得します (最終的な数式をよりシンプルに保つため)。
=FILTER($F$2:$F$10,($B$2:$B$10=I2)*($E$2:$E$10=L2))

主な計算式は、販売額と予想額を比較するだけです。
=IF(M2=N2,"OK!",M2-N2)

Answer 2

必要な関数は ですFILTER()。さらに、LET()関数を使用して数式を簡素化し、テーブル (挿入 - テーブル) を追加したので、構造化参照を使用できます。

=LET(expected, FILTER(Table1[AMOUNT EXPECTED],(Table1[VENDOR ID '#]=[@[VENDOR ID '#]])*(Table1[ITEMS EXPECTED]=[@[ITEMS SOLD]])),IF(expected=[@[AMOUNT SOLD]],"OK!",[@[AMOUNT SOLD]]-expected))