代码之家 › 专栏 › 技术社区 › katz2row

Excel:通过使用第三列中的匹配数据评估其他列中的数据来编辑列数据

concatenation excel

katz2row · 技术社区 · 8 年前

我们有来自系统的数据,其中包含用于保险选择的人口统计数据。员工可以为自己选择保险、为他们和他们的孩子选择保险、为他们和他们的配偶选择保险或为他们的整个家庭(他们、他们的配偶和他们的孩子)选择保险。传统上,我们会按总数(员工、员工+一人或家庭)对其进行分组,我们不在乎组合是否包括子女或配偶,但运营商的要求不同。

受雇者
员工+配偶
员工+子女
员工+子女
家庭

我们需要保留行数,因为每一行都适用于不同的人,但成员ID在员工和任何家属之间共享。

我正试图找到一种使用公式的方法,最好是因为它可以被更多的人使用 成员ID (可能是1到无穷大-到目前为止我看到的最高值是7)然后另一列说 本人、配偶或子女

因此,本质上我会有一个额外的列(比如H列),这将表示员工、员工+配偶、员工+子女、员工+子女或家庭,这取决于在B列中找到的匹配A列ID的自我、配偶和子女的组合。

以下是数据示例。我删除了实际数据中不相关的列(电子邮件地址、家庭地址、社交等),我更改了所有名称和成员ID,但我们使用的数据非常相似。

我看到的大多数解决方案都是一行串联数据,仍然需要将其解析为适当的名称,但我需要对所有行重复。例如,在下面的示例中:

第2行和第3行都有员工+配偶
第4行将有员工
第8行将有员工
第9-15行将有家庭
第16行和第17行将有员工+子女

3 回复 | 直到 8 年前

barry houdini 8 年前

=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,为孩子分配一个值,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")

数据的顺序无关紧要

ImaginaryHuman072889 8 年前

我已经为@dwirony找到了相同的解决方案,除了没有VBA,以防VBA不适合您。

将以下数组公式粘贴到单元格中 G2 并根据需要向下拖动(添加换行符以提高可读性):

= CHOOSE(MIN(IF(SUMPRODUCT(($B$2:$B$20="Spouse")+0,($A2=$A$2:$A$20)+0)=0,5,4),
  MMULT(MMULT(TRANSPOSE((ROW($A$2:$A$20)>0)+0),(IF($A2=$A$2:$A$20,$B$2:$B$20)=
  {"self","Spouse","Child"})+0),{1;1;2})),
  "Employee","Employee + Spouse","Employee + Child","Family","Employee + Children")

注意,这是一个数组公式,因此必须按 Ctrl键 + 转移 + 进来 输入此公式后 进来 .

工作示例见下文。

dwirony Greg Viers 8 年前

可能是一个草率的解决方案,但它仍然有效-只需使用一些布尔值并循环通过memberID和关系列匹配值。

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