![用于评估定性数据中的遗漏(添加)和增补(删除)的 Excel 公式](https://linux22.com/image/1615888/%E7%94%A8%E4%BA%8E%E8%AF%84%E4%BC%B0%E5%AE%9A%E6%80%A7%E6%95%B0%E6%8D%AE%E4%B8%AD%E7%9A%84%E9%81%97%E6%BC%8F%EF%BC%88%E6%B7%BB%E5%8A%A0%EF%BC%89%E5%92%8C%E5%A2%9E%E8%A1%A5%EF%BC%88%E5%88%A0%E9%99%A4%EF%BC%89%E7%9A%84%20Excel%20%E5%85%AC%E5%BC%8F.png)
我希望有人能帮我找出一些公式(或 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 的定性研究人员提供帮助!