☰

2 つの列の一致する値と一致しない値を比較し、必要な形式で取得するにはどうすればよいですか?

tag-icon • tag-icon microsoft-excel worksheet-function vba power-query
2 つの列の一致する値と一致しない値を比較し、必要な形式で取得するにはどうすればよいですか?

2 つの列間で一致する値と一致しない値を見つけて、以下のようにフォーマットするにはどうすればよいですか?

入力：

| expected | match | forward | backward | actual |
|----------|-------|---------|----------|--------|
| a        |       |         |          | b      |
| b        |       |         |          | c      |
| c        |       |         |          | r      |
| d        |       |         |          | s      |
| e        |       |         |          |        |

出力：

| expected | match | forward | backward | actual |
|----------|-------|---------|----------|--------|
| a        | b     | a       | r        | b      |
| b        | c     | d       | s        | c      |
| c        |       | e       |          | r      |
| d        |       |         |          | s      |
| e        |       |         |          |        |

forward- には存在するが、 （）expectedには存在しないactualSQL left outer join

backward- には存在するが、 （）actualには存在しないexpectedSQL right outer join

expectedクエリから取得したものですSQL。RDBMSに列がない場合がたくさんあるのでactual、比較するにはExcelを使用する必要があります。通常はExcelを使用して比較できますVLOOKUPが、時間がかかり、必要な形式が得られません。実行できるソリューションが必要です。重要なのは上記の形式になります。

ご提案があれば歓迎します。

答え1

レイアウト

情報が上記のようにレイアウトされていると仮定すると、C3、D3、E3 にそれぞれ次の 3 つの数式を使用できます。

C3
=IFERROR(INDEX(F:F,AGGREGATE(15,6,ROW($B$3:$B$7)/(COUNTIF($B$3:$B$7,$F$3:$F$7)>0),ROW(A1))),"")

D3
=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$3:$B$7)/(COUNTIF($C$3:$C$7,$B$3:$B$7)=0),ROW(A1))),"")

E3
=IFERROR(INDEX(F:F,AGGREGATE(15,6,ROW($F$3:$F$7)/((COUNTIF($C$3:$C$7,$F$3:$F$7)=0)*($F$3:$F$7<>"")),ROW(A1))),"")

数式を 7 行目までコピーすると、次のようになります。

有価証券

注: AGGREGATE関数 15 では配列のような計算を実行します。そのため、AGGREGATE関数内では完全な列参照を使用しないでください。データ セットに近いものに制限してください。そうしないと、空白セルに対する膨大な計算によってコンピューターが停止する可能性があります。

答え2

Power Queryの場合、キー変換は次のようになります。マージクエリこれはSQLの結合に似ており、同様の用語を使用します。最初のステップは、期待されるそして実際の列。マッチリストの場合は、結合タイプ = 内部を使用してクエリを作成します。フォワードリストでは、結合タイプ = 左反を使用して別のクエリを作成し、次に後ろ向きRight Anti を使用します。

すべてをあなたが提示したとおりにまとめるのは少し面倒ですが (それが本当に必要な場合)、各クエリにインデックスを追加し、インデックス列でマージ結合 (結合タイプ = 左外部) を使用して、各行の各クエリから 1 つの結果を取得できると思います。

関連情報