Wie vergleiche ich zwei Spalten auf übereinstimmende und nicht übereinstimmende Werte und erhalte sie im gewünschten Format?

Question

Wie finde ich übereinstimmende und nicht übereinstimmende Werte zwischen zwei Spalten und formatiere sie auf die folgende Weise?

Eingang:

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

Ausgabe:

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

forward- vorhanden in, expectedaber nicht in actual( SQL left outer join)

backward- vorhanden in, actualaber nicht in expected( SQL right outer join)

expectedist das, was ich von einer SQLAbfrage bekomme. Ich habe viele Szenarien, in denen ich die actualSpalte nicht im RDBMS habe, also müsste ich Excel zum Vergleichen verwenden. Ich kann es normalerweise vergleichen, VLOOKUPaber es ist zeitaufwändig und gibt auch nicht das gewünschte Format. Ich hätte gerne eine Lösung, die umgesetzt werden kannwichtigmit dem Format wie oben.

Ich bin offen für Vorschläge.

Answer 1

Vorausgesetzt, Ihre Informationen sind wie oben angeordnet, können Sie in C3, D3 und E3 die folgenden drei Formeln verwenden:

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))),"")

Kopieren Sie die Formeln bis zur siebten Zeile. Das Ergebnis lautet dann:

Hinweis: AGGREGATEFührt arrayähnliche Berechnungen für Funktion 15 aus. Verwenden Sie daher innerhalb der AGGREGATEFunktion keine vollständigen Spaltenverweise. Beschränken Sie sie auf Bereiche in der Nähe Ihres Datensatzes. Andernfalls kann Ihr Computer durch eine enorme Menge an Berechnungen mit leeren Zellen überlastet werden.

Answer 2

Für Power Query wäre Ihre SchlüsseltransformationAbfragen zusammenführen. Es ähnelt einem SQL-Join und verwendet eine ähnliche Terminologie. Der erste Schritt wäre, separate Abfragen für dieerwartetUndtatsächlichSpalten. Für dieübereinstimmenListe würde ich eine Abfrage mit dem Join-Typ = Inner erstellen. Für dienach vorneListe würde ich eine weitere Abfrage mit Join Type = Left Anti erstellen, dann noch eine fürrückwärtsmit Right Anti.

Alles genau so zusammenzufassen, wie Sie es dargestellt haben, wäre etwas knifflig (falls das wirklich nötig ist), aber ich könnte mir vorstellen, dass Sie jeder Abfrage einen Index hinzufügen und Merge Joins (Join-Typ = Links außen) auf die Indexspalten anwenden, um aus jeder Abfrage in jeder Zeile ein einzelnes Ergebnis zu erhalten.