时间戳

首页 > 解决方案 > 如何比较两列的匹配值和非匹配值并以我想要的格式获取它？

excel - 如何比较两列的匹配值和非匹配值并以我想要的格式获取它？

问题描述

如何在两列之间找到匹配和不匹配的值并按以下方式对其进行格式化？

输入：

| 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但不在actual( SQL left outer join)

backward- 出现在actual但不在expected( SQL right outer join)

match- 出现在expected和actual( SQL inner join)中

expected是我从SQL查询中得到的。当我在RDBMS中没有actual列时，我有很多场景，所以我必须使用excel来比较。我通常可以比较它，VLOOKUP但它很耗时，而且它没有给出我想要的格式。我想要一个可以用上述格式很重要的解决方案。

我愿意接受建议。我个人使用 python/pandas 执行此操作，但我的同事不习惯 python，所以我更喜欢可以通过单击按钮完成的解决方案，或通过 VBA 自动完成，或通过 Excel 执行函数，基本上任何我可以与我的 Excel 同事分享，这可以使他们的流程更快。目前，他们VLOOKUP->Filter->Copy->Paste to another sheet对所有三列进行了冲洗重复。

提供了一种解决方案 - https://superuser.com/a/1417235/954024但它运行速度非常慢且效率低下，我的系统只是使用它挂起:(

我的蟒蛇解决方案：

import pandas as pd
import sys


def find_discrepancies(input_file):
    """
    input: df
    output: formatted df
    """
    df = pd.read_excel(input_file)
    df['match'] = df.loc[df['expected'].isin(df['actual'])].reset_index()[
        'expected']
    df['forward'] = df.loc[df['expected'].isin(
        df['actual']) == False].reset_index()['expected']
    df['backward'] = df.loc[df['actual'].isin(
        df['expected']) == False].reset_index()['actual']
    df = df[['expected', 'match', 'forward', 'backward', 'actual']]
    counts = df.count()
    df.columns = [df.columns[i].capitalize() + ' - ' + str(counts.values[i]) for i in range(5)]
    df.fillna('', inplace=True)

    return df


def main(inputFile, outputFile):
    df = find_discrepancies(inputFile)
    df.to_excel(outputFile, index=False)


if __name__ == '__main__':

    inputFile = sys.argv[1]
    outputFile = sys.argv[2]

    main(inputFile, outputFile)

标签： excelvba

解决方案

不是最干净的解决方案，但这应该就足够了。我需要更多地了解您的数据设置来配置它。

Option Explicit
Sub PopulateColumns()

    Dim i As Long, lastrow As Long
    Dim testitem As String

    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        testitem = Cells(i, 1).Value

        If Application.CountIf(Range("E:E"), testitem) = 0 Then
            lastrow = Cells(Rows.Count, 3).End(xlUp).Row
            Cells(lastrow + 1, 3).Value = testitem
        ElseIf Application.CountIf(Range("E:E"), testitem) > 0 Then
            lastrow = Cells(Rows.Count, 2).End(xlUp).Row
            Cells(lastrow + 1, 2).Value = testitem
        End If
    Next i

    For i = 2 To Cells(Rows.Count, 5).End(xlUp).Row
        testitem = Cells(i, 5).Value

        If Application.CountIf(Range("A:A"), testitem) = 0 Then
            lastrow = Cells(Rows.Count, 4).End(xlUp).Row
            Cells(lastrow + 1, 4).Value = testitem
        End If
    Next i

Cells(1, 1).Value = "Expected - " & Cells(Rows.Count, 1).End(xlUp).Row - 1
Cells(1, 2).Value = "Match - " & Cells(Rows.Count, 2).End(xlUp).Row - 1
Cells(1, 3).Value = "Forward - " & Cells(Rows.Count, 3).End(xlUp).Row - 1
Cells(1, 4).Value = "Backward - " & Cells(Rows.Count, 4).End(xlUp).Row - 1
Cells(1, 5).Value = "Actual - " & Cells(Rows.Count, 5).End(xlUp).Row - 1

Columns("A:E").AutoFit

For i = 1 To 5
    Cells(1, i).Interior.Color = RGB(168, 207, 141)
    Cells(1, i).Font.Bold = True
    Cells(1, i).HorizontalAlignment = xlCenter
    Cells(1, i).Borders.LineStyle = xlContinuous
Next i


End Sub

图像

推荐阅读