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

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

  •  2
  • katz2row  · 技术社区  · 7 年前

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

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

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

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

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

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

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

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

    Excel Data Example

    3 回复  |  直到 7 年前
        1
  •  2
  •   barry houdini    7 年前

    =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")

    数据的顺序无关紧要

        2
  •  2
  •   ImaginaryHuman072889    7 年前

    我已经为@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键 + 转移 + 进来 输入此公式后 进来 .

    工作示例见下文。

    enter image description here

        3
  •  1
  •   dwirony Greg Viers    7 年前

    可能是一个草率的解决方案,但它仍然有效-只需使用一些布尔值并循环通过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
    

    Before After