如何编写自定义函数按姓名首名分组统计对应得分匹配结果？

需求说明 #

从Name列提取逗号前的首名，按首名分组后，统计每组中Score列值为1的记录数量，最终输出格式为「[首名] team members」对应统计结果。

输入数据 #

期望输出 #

方案1：Excel实现（含自定义函数） #

快速公式法 #

1. 提取首名：在空白列（如C列）输入公式，下拉填充所有行：

   =LEFT(A2,FIND(",",A2)-1)
   

2. 分组统计：使用COUNTIFS函数统计指定首名的Score=1数量，或直接插入数据透视表：
   • 选中数据区域，插入数据透视表
   • 行区域选提取的首名列，值区域选Score，将值字段设置为「计数」，并添加筛选条件：Score等于1
   • 最后用公式拼接行标签：=C2&" team members"

VBA自定义函数 #

如果需要批量自动化处理，可以编写自定义函数：

Function CountScoreByFirstName(rngName As Range, rngScore As Range, targetScore As Integer) As Variant
    Dim resultDict As Object
    Dim i As Integer
    Dim firstName As String
    
    Set resultDict = CreateObject("Scripting.Dictionary")
    
    For i = 1 To rngName.Rows.Count
        ' 提取逗号前的首名
        firstName = Split(rngName.Cells(i, 1).Value, ",")(0)
        ' 统计匹配目标Score的记录
        If rngScore.Cells(i, 1).Value = targetScore Then
            If resultDict.Exists(firstName) Then
                resultDict(firstName) = resultDict(firstName) + 1
            Else
                resultDict(firstName) = 1
            End If
        End If
    Next i
    
    ' 转换为可输出的数组格式
    Dim outputArr() As Variant
    ReDim outputArr(1 To resultDict.Count, 1 To 2)
    Dim j As Integer
    j = 1
    For Each key In resultDict.Keys
        outputArr(j, 1) = key & " team members"
        outputArr(j, 2) = resultDict(key)
        j = j + 1
    Next key
    
    CountScoreByFirstName = outputArr
End Function

使用方法：选中两列多行的区域（如D1:E2），输入=CountScoreByFirstName(A2:A6,B2:B6,1)，按Ctrl+Shift+Enter以数组公式形式执行。

方案2：Python Pandas实现 #

import pandas as pd

# 构造输入数据（实际场景可替换为读取文件）
df = pd.DataFrame({
    'Name': ['Sam, Josh', 'Sam, Kay', 'Sam, Jane', 'Jay, Kane', 'Jay, Kris'],
    'Score': [0, 1, 0, 1, 0]
})

# 提取首名并去除多余空格
df['FirstName'] = df['Name'].str.split(',').str[0].str.strip()

# 筛选Score=1的记录，按首名分组统计数量
result = df[df['Score'] == 1].groupby('FirstName')['Score'].count().reset_index()

# 格式化输出的Name列
result['Name'] = result['FirstName'] + ' team members'

# 整理最终输出格式
final_output = result[['Name', 'Score']]
print(final_output)

运行后输出结果：

Name  Score
0  Jay team members      1
1  Sam team members      1

内容的提问来源于stack exchange，提问作者Aravindh Ks