☰

用于评估定性数据中的遗漏（添加）和增补（删除）的 Excel 公式

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2013 microsoft-excel-2016 visual-basic
用于评估定性数据中的遗漏（添加）和增补（删除）的 Excel 公式

我希望有人能帮我找出一些公式（或 VBA 脚本）来帮助我计算定性分析。具体来说，我有两列如下所示：

参与者 ID 和随时间变化的响应

在此处输入图片描述

需要说明的是，参与者 ID 列在此上下文中并不重要，我仅将其包括在内以说明每个 T1 和 T2 单元格都与我们正在比较的参与者相关联。这些列表示参与者在两个不同时间对问题的回答（代码）：立即（T1）和延迟后（T2）。因此，我需要将 T1 列与 T2 列进行比较，以评估参与者从 T1 到 T2 添加（提交）或删除（省略）的内容。我试图打印出一些公式，如下所示：

第 1 列：佣金列（检查从 T1 列添加到 T2 列的内容）

  • 67

  • 三十九

  • 39, 60

  • 47, 54

第 2 列：佣金数量（从佣金栏计算代码数量）

  • 1

  • 1

  • 2

  • 2

第 3 列：遗漏列（检查从 T1 列到 T2 列删除的内容）

  • 47, 52

  • 69

  • 40, 48

  • 42, 64

第四列：遗漏数（从遗漏列计算代码数量）

  • 2

  • 1

  • 2

  • 2

以下是我们尝试过并且（某种程度上）有效的一些公式的例子：

{=TEXTJOIN(",",TRUE,IF(ISNUMBER(SEARCH("," &TRIM(MID(SUBSTITUTE(N3,",",REPT(" ",999)),(ROW(INDIRECT("1:" & LEN(N3)-LEN(SUBSTITUTE(N3,",",""))+1))-1)*999+1,999))&",",","&B3&",")),"",TRIM(MID(SUBSTITUTE(N3,",",REPT(" ",999)),(ROW(INDIRECT("1:" & LEN(N3)-LEN(SUBSTITUTE(N3,",",""))+1))-1)*999+1,999))))}

上面的代码正确地计算了一些代码，但删除了中间的空格，例如：“39, 60”，并将其变为“39,60”，这是一个问题。此外，它没有正确地计算一些佣金，即使它们在 T1 中被提及，也会将一些数字计为佣金（这使其成为非佣金）。

{=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH(","&TRIM(FILTERXML("<z><y>" & SUBSTITUTE(C3,",","</y><y>")&"</y></z>","//y"))&",",","&SUBSTITUTE(B3," ","")&",")),TRIM(FILTERXML("<z><y>" & SUBSTITUTE(C3,",","</y><y>")&"</y></z>","//y")),""))}

上面的代码完全满足了我们对一列的要求，即跟踪 T1 和 T2 之间的一致性，也就是在两个时间报告的代码。不过，我们还没有弄清楚如何逆向工程此代码以使其适用于 Comissions 和 Omissions。

=LEN(TRIM(H3))-LEN(SUBSTITUTE(TRIM(H3),",",""))+1

上面的代码计算了代码，但对于空白单元格，它总是添加 1，这是有问题的，因为它实际上应该将这些单元格计为 0 或空白。

希望这是有意义的，并提前感谢大家的帮助！

答案1

我们精确地找出了计算佣金（增加）、遗漏（删除）和一致性所需的所有公式！我们将作为对这篇文章的回答与将来需要它们的人分享它们。

重要提示：下面的代码专门用于逗号分隔值（CSV）编码方案，我们不能保证它们也适用于其他情况。

要计算佣金（附加费），您需要插入 VBA 脚本/模块。为此，在 Excel 中按 Alt + F11，打开 VBA 窗口，然后单击插入 -> 模块并粘贴以下代码：

（来源：https://www.ozgrid.com/VBA/array-differences.htm）

Public Function Comissions(Cell1 As Range, Cell2 As Range) As String
Dim Array1, Array2, lLoop As Long
Dim strDiff As String, strDiffs As String
Dim lCheck As Long


Array1 = Split(Replace(Cell1, " ", ""), ",")
Array2 = Split(Replace(Cell2, " ", ""), ",")
On Error Resume Next
With WorksheetFunction
    For lLoop = 0 To UBound(Array2)
        strDiff = vbNullString
        strDiff = .Index(Array1, 1, .Match(Array2(lLoop), Array1, 0))
        If strDiff = vbNullString Then
            lCheck = 0
            lCheck = .Match(Array2(lLoop), Array1, 0)

            If lCheck = 0 Then
                strDiffs = strDiffs & ", " & Array2(lLoop)
            End If
        End If

    Next lLoop
End With

Comissions = Trim(Right(strDiffs, Len(strDiffs) - 1))
End Function

此函数的用法类似于：“Comissions(B3,C3)”，它将比较这些单元格并在公式所在的第三个单元格中打印佣金（附加费）。

对于遗漏（删除），您将需要插入另一个 VBA 脚本/模块，其中包含以下代码：

Public Function Omissions(Cell1 As Range, Cell2 As Range) As String
Dim Array1, Array2, lLoop As Long
Dim strDiff As String, strDiffs As String
Dim lCheck As Long


Array1 = Split(Replace(Cell1, " ", ""), ",")
Array2 = Split(Replace(Cell2, " ", ""), ",")
On Error Resume Next
With WorksheetFunction
    For lLoop = 0 To UBound(Array1)
        strDiff = vbNullString
        strDiff = .Index(Array2, 1, .Match(Array1(lLoop), Array2, 0))
        If strDiff = vbNullString Then
            lCheck = 0
            lCheck = .Match(Array1(lLoop), Array2, 0)

            If lCheck = 0 Then
                strDiffs = strDiffs & ", " & Array1(lLoop)
            End If
        End If

    Next lLoop
End With

Omissions = Trim(Right(strDiffs, Len(strDiffs) - 1))
End Function

与 Comissions 公式类似，输入“Omissions(B3,C3)”将比较这些单元格并在写有公式的第三个单元格中找到遗漏（删除）。

要找到一致性，即每个单元格中相同的代码/数字/值，请使用以下代码作为数组函数（粘贴此代码后按 Ctrl + Shift + Enter 使其成为数组函数，感谢@Scott Craner 提供代码）：

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH(","&TRIM(FILTERXML("<z><y>" & SUBSTITUTE(C3,",","</y><y>")&"</y></z>","//y"))&",",","&SUBSTITUTE(B3," ","")&",")),TRIM(FILTERXML("<z><y>" & SUBSTITUTE(C3,",","</y><y>")&"</y></z>","//y")),""))

此代码将打印两个单元格中存在的任何一致（相同）的值，并将它们打印在写入此公式的第三个单元格中。

然后，为了计算已提交、已遗漏和一致的比例/百分比，请使用此代码（感谢@cybernetic.nomad 提供代码）：

=LEN(TRIM(D3))-LEN(SUBSTITUTE(TRIM(D3),",",""))+NOT(--(D3=""))

您可以使用此代码来计算“遗漏/一致性”列中的代码，然后将其除以在（例如，本例中的 T1 列）中出现的值总数。例如，参与者 1 从 T1 到 T2 提交的比例/百分比将是 0.33（1/3），遗漏的比例/百分比将是 0.66（2/3），一致性的比例/百分比将是 0.33（1/3）。

希望这可以为其他使用 Excel 的定性研究人员提供帮助！

相关内容