☰

如何比較兩列的匹配值和不匹配值並以我想要的格式獲取它？

tag-icon • tag-icon microsoft-excel worksheet-function vba power-query
如何比較兩列的匹配值和不匹配值並以我想要的格式獲取它？

如何找到兩列之間的匹配和不匹配值並按以下方式對其進行格式化？

輸入：

| 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來比較。我可以通常使用它進行比較，VLOOKUP但這很耗時，而且它沒有給出我想要的格式。我想要一個可以完成的解決方案重要的格式如上。

我願意接受建議。

答案1

佈局

假設您的資訊如上所示，您可以在C3、D3和E3中分別使用以下三個公式：

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 行，您將得到以下結果：

POC

注意： AGGREGATE對函數 15 執行類似數組的計算AGGREGATE。將其限制為接近您的資料集。如果沒有，您的計算機可能會因為對空白單元格進行大量計算而陷入困境。

答案2

對於 Power Query，您的關鍵轉換是合併查詢。它類似於 SQL 連接並使用類似的術語。第一步是為以下內容建立單獨的查詢預期的和實際的列。為了匹配我將使用 Join Type = Inner 建立一個查詢。為了向前清單 我將使用 Join Type = Left Anti 建立另一個查詢，然後再建立一個查詢落後使用右抗。

將它們完全按照您呈現的方式整合在一起會有點繁瑣（如果確實有必要），但我想您可以向每個查詢添加一個索引，並在索引列上使用合併聯接（聯接類型=左外部）來取得每行的每個查詢的單一結果。

相關內容