Excel:通过计算其他列的数据和第三列的匹配数据来编辑列数据。

Question

我们有来自我们系统的数据，其中包含用于保险选择的人口统计数据。雇员可以为自己选择保险，为他们和子女选择保险，为他们和配偶选择保险，也可以为他们的整个家庭(他们、他们的配偶和他们的子女)选择保险。传统上，我们会按总人数(雇员、Employee+One或家庭)对他们进行分组，我们不关心这种组合是否包括子女或配偶，但运营商想要的不一样。

• 员工
• 雇员+配偶
• 雇员+子女
• 雇员+子女
• 家庭

我们需要保留行数，因为对于不同的人，每一行都有，但是成员if是在员工和任何受抚养人之间共享的。

我正试图找到一种使用公式的方法，最好是因为这样更多的人可以使用它来查看所有相同的成员is (可能是1到无穷大--到目前为止我看到的最高值是7)，然后是另一篇专栏文章，上面提到了self、S偶或，并根据结果添加了上述名称之一。

因此，本质上，我将有一个额外的专栏(假设列H)，它将说雇员、雇员+配偶、雇员+子女、雇员+子女或家庭，这取决于在B列中找到匹配的A列in的自我、配偶和子女的组合。

下面是数据的一个例子。我删除了不相关的列(电子邮件地址、家庭地址、社交网站等)。这包括在实际数据中，我已经更改了所有的名称和成员I，但是我们正在处理的数据非常相似。

我看到的大多数解决方案都会产生一行连接数据，这些数据仍然需要被解析为适当的指定，但我需要对所有行进行重复。例如，在以下示例中：

• 第2行和第3行都有雇员+配偶
• 第四行会有员工
• 第5-7行将有雇员+子女
• 第8行会有雇员
• 第9-15行将有家庭
• 第16行和第17行将有雇员+子女

​

​

Answer 1

这个通常输入的公式适用于我。

=CHOOSE(MATCH(SUM(COUNTIFS(A:A,A2,B:B,{"Spouse","Child"})*{100,1}),{0,1,2,100,101}),"Employee","Employee + Child","Employee + Children","Employee + Spouse","Family")

我假设每个ID都有一个“自我”条目，上面没有检查-公式给配偶分配了一个值100，给一个孩子分配了一个值1，一个ID的总数会告诉你它是一个家庭还是其他一个组合。

如果您需要，可以在计算中包括"Self“，如果没有任何ID，则返回"Error”。

=CHOOSE(MATCH(SUM(COUNTIFS(A:A,A2,B:B,{"Self","Spouse","Child"})*{1000,100,1}),{0,1000,1001,1002,1100,1101}),"Error","Employee","Employee + Child","Employee + Children","Employee + Spouse","Family")

数据的顺序并不重要

Answer 2

可能是一种草率的解决方案，但它仍然有效--只是使用一些布尔值，并遍历匹配值的memberID和relationship列。

Sub GetBenefitOption()
Application.ScreenUpdating = False

Dim sht As Worksheet, lastrow As Long, i As Long, j As Long
Set sht = ThisWorkbook.Worksheets("Sheet1")
lastrow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

Dim memberID As String
Dim spouse As Boolean, child As Boolean, children As Boolean, family As Boolean

For i = 2 To lastrow
    memberID = Range("A" & i).Value

    For j = 2 To lastrow
        If Not Range("A" & j).Value = memberID Then
            If j = lastrow Then
                Range("G" & i).Value = "Employee"
            End If
        Else
            If Range("B" & j).Value = "Spouse" Then
                spouse = True
            ElseIf Range("B" & j).Value = "Child" And child = True Then
                children = True
            ElseIf Range("B" & j).Value = "Child" Then
                child = True
            End If
        End If

        If spouse = True And child = True And children = True Then
            family = True
        End If
    Next j

    If family = True Then
        Range("G" & i).Value = "Family"
    Else
        If children = True Then
            Range("G" & i).Value = "Employee + Children"
            child = False
        Else
            If child = True Then
                Range("G" & i).Value = "Employee + Child"
            Else
                If spouse = True Then
                    Range("G" & i).Value = "Employee + Spouse"
                End If
            End If
        End If
    End If

spouse = False
child = False
children = False
family = False

Next i

Application.ScreenUpdating = True
End Sub

​

​