import pandas as pdimport numpy as npfrom scipy import statsimport matplotlib.pyplot as pltimport seaborn as sns# 创建示例数据np.random.seed(286)departments = ['销售部', '研发部', '运营部']employees_per_dept = [12, 8, 10]data = []for dept, n in zip(departments, employees_per_dept):    if dept == '销售部':        scores = np.random.normal(90, 3, n)  # 销售部分数偏高    elif dept == '研发部':        scores = np.random.normal(78, 4, n)  # 研发部分数严格    else:        scores = np.random.normal(82, 3.5, n)  # 运营部分数中等    for i, score in enumerate(scores):        data.append({            '员工ID': f'{dept[:2]}{i+1:03d}',            '部门': dept,            '原始分数': max(60, min(100, round(score, 1)))  # 限制在60-100分之间        })df = pd.DataFrame(data)print("原始数据描述性统计：")print(df.groupby('部门')['原始分数'].describe())

def percentile_standardize(df):    """百分位数标准化"""    df_standardized = df.copy()    df_standardized['标准化分数'] = 0    for dept in df['部门'].unique():        dept_mask = df['部门'] == dept        dept_scores = df.loc[dept_mask, '原始分数']        # 计算每个分数的百分位数        percentiles = []        for score in dept_scores:            # 计算小于等于该分数的人数比例            percentile = (sum(dept_scores <= score) / len(dept_scores)) * 100            percentiles.append(percentile)        df_standardized.loc[dept_mask, '标准化分数'] = percentiles    return df_standardizeddf_percentile = percentile_standardize(df)

def zscore_standardize(df):    """Z-Score标准化"""    df_standardized = df.copy()    for dept in df['部门'].unique():        dept_mask = df['部门'] == dept        dept_scores = df.loc[dept_mask, '原始分数']        # 计算Z分数        z_scores = stats.zscore(dept_scores)        # 将Z分数转换为0-100分制        standardized = 50 + (z_scores * 10)  # 1个标准差对应10分        standardized = np.clip(standardized, 0, 100)  # 限制在0-100分之间        df_standardized.loc[dept_mask, '标准化分数'] = standardized    return df_standardizeddf_zscore = zscore_standardize(df)

def minmax_standardize(df, new_min=0, new_max=100):    """Min-Max归一化"""    df_standardized = df.copy()    for dept in df['部门'].unique():        dept_mask = df['部门'] == dept        dept_scores = df.loc[dept_mask, '原始分数']        old_min = dept_scores.min()        old_max = dept_scores.max()        # 线性转换公式        if old_max != old_min:  # 防止除零错误            standardized = ((dept_scores - old_min) / (old_max - old_min)) * (new_max - new_min) + new_min        else:            standardized = np.full_like(dept_scores, (new_max + new_min) / 2)        df_standardized.loc[dept_mask, '标准化分数'] = standardized    return df_standardizeddf_minmax = minmax_standardize(df)

def force_rank_distribution(df_standardized, ratios={'A': 0.2, 'B': 0.7, 'C': 0.1}):    """    强制排名分布    ratios: 各等级占比，如{'A': 0.2, 'B': 0.7, 'C': 0.1}    """    df_result = df_standardized.copy()    # 按标准化分数降序排列    df_result = df_result.sort_values('标准化分数', ascending=False).reset_index(drop=True)    total_employees = len(df_result)    # 计算各等级的分界点    cum_ratio = 0    performance_grade = []    for grade, ratio in ratios.items():        count = int(round(total_employees * ratio))        if grade == list(ratios.keys())[-1]:  # 最后一个等级            count = total_employees - len(performance_grade)        performance_grade.extend([grade] * count)    # 如果员工总数不等于等级分配总数，微调最后一个等级的数量    if len(performance_grade) != total_employees:        diff = total_employees - len(performance_grade)        performance_grade.extend([list(ratios.keys())[-1]] * diff)    df_result['绩效等级'] = performance_grade[:total_employees]    return df_result# 使用百分位数标准化结果进行强制排名df_final = force_rank_distribution(df_percentile)print("\n强制排名结果：")print(df_final[['员工ID', '部门', '原始分数', '标准化分数', '绩效等级']].head(10))

def department_wise_force_rank(df, ratios={'A': 0.2, 'B': 0.7, 'C': 0.1}):    """    分部门强制排名    """    result_dfs = []    for dept in df['部门'].unique():        dept_df = df[df['部门'] == dept].copy()        # 部门内标准化        dept_scores = dept_df['原始分数']        dept_df['部门内标准化分数'] = ((dept_scores.rank() - 1) / (len(dept_scores) - 1)) * 100        # 部门内按比例分配等级        dept_df = dept_df.sort_values('部门内标准化分数', ascending=False)        dept_total = len(dept_df)        # 计算各等级数量        grade_counts = {}        cumulative = 0        for i, (grade, ratio) in enumerate(ratios.items()):            if i < len(ratios) - 1:                count = int(round(dept_total * ratio))            else:  # 最后一个等级                count = dept_total - cumulative            grade_counts[grade] = count            cumulative += count        # 分配等级        grades = []        for grade, count in grade_counts.items():            grades.extend([grade] * count)        dept_df['部门内绩效等级'] = grades[:dept_total]        result_dfs.append(dept_df)    return pd.concat(result_dfs, ignore_index=True)# 分部门强制排名df_dept_rank = department_wise_force_rank(df)print("\n分部门强制排名结果：")print(df_dept_rank[['员工ID', '部门', '原始分数', '部门内标准化分数', '部门内绩效等级']].head(10))

def visualize_standardization(df_raw, df_standardized):    """可视化标准化效果"""    fig, axes = plt.subplots(2, 2, figsize=(15, 12))    # 1. 原始分数分布    ax1 = axes[0, 0]    for dept in df_raw['部门'].unique():        dept_scores = df_raw[df_raw['部门'] == dept]['原始分数']        ax1.hist(dept_scores, alpha=0.5, label=dept, bins=10)    ax1.set_xlabel('原始分数')    ax1.set_ylabel('频数')    ax1.set_title('各部门原始分数分布')    ax1.legend()    ax1.grid(True, alpha=0.3)    # 2. 标准化后分数分布    ax2 = axes[0, 1]    for dept in df_standardized['部门'].unique():        dept_scores = df_standardized[df_standardized['部门'] == dept]['标准化分数']        ax2.hist(dept_scores, alpha=0.5, label=dept, bins=10)    ax2.set_xlabel('标准化分数')    ax2.set_ylabel('频数')    ax2.set_title('各部门标准化后分数分布')    ax2.legend()    ax2.grid(True, alpha=0.3)    # 3. 箱线图对比    ax3 = axes[1, 0]    data_to_plot = [df_raw['原始分数'].values, df_standardized['标准化分数'].values]    ax3.boxplot(data_to_plot, labels=['原始分数', '标准化分数'])    ax3.set_ylabel('分数')    ax3.set_title('标准化前后分数整体分布对比')    ax3.grid(True, alpha=0.3)    # 4. 绩效等级分布    ax4 = axes[1, 1]    if '绩效等级' in df_standardized.columns:        grade_counts = df_standardized['绩效等级'].value_counts().sort_index()        colors = {'A': 'gold', 'B': 'silver', 'C': 'peru'}        bar_colors = [colors.get(grade, 'gray') for grade in grade_counts.index]        bars = ax4.bar(grade_counts.index, grade_counts.values, color=bar_colors, alpha=0.7)        # 在柱子上添加数量标签        for bar, count in zip(bars, grade_counts.values):            height = bar.get_height()            ax4.text(bar.get_x() + bar.get_width()/2., height + 0.1,                    f'{count}', ha='center', va='bottom', fontsize=10)        ax4.set_xlabel('绩效等级')        ax4.set_ylabel('人数')        ax4.set_title('绩效等级分布')        ax4.grid(True, alpha=0.3, axis='y')    plt.tight_layout()    plt.savefig('绩效标准化效果对比.png', dpi=300, bbox_inches='tight')    plt.show()# 生成可视化图表visualize_standardization(df, df_final)

Option Explicit' 主过程：执行绩效标准化与强制排名Sub PerformanceStandardization()    Dim ws As Worksheet    Dim lastRow As Long, i As Long    Dim deptRange As Range, cell As Range    Dim scores As Collection    Dim standardizedScore As Double    ' 设置工作表    Set ws = ThisWorkbook.Worksheets("绩效数据")    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row    ' 添加新列用于标准化结果    ws.Cells(1, 4).Value = "标准化分数"    ws.Cells(1, 5).Value = "部门排名"    ws.Cells(1, 6).Value = "部门百分位"    ws.Cells(1, 7).Value = "绩效等级"    ' 获取所有部门    Dim departments As Object    Set departments = CreateObject("Scripting.Dictionary")    For i = 2 To lastRow        Dim dept As String        dept = ws.Cells(i, 2).Value ' B列为部门        If Not departments.Exists(dept) Then            Set scores = New Collection            departments.Add dept, scores        Else            Set scores = departments(dept)        End If        ' 收集部门分数        scores.Add ws.Cells(i, 3).Value ' C列为原始分数    Next i    ' 计算标准化分数    Dim deptKey As Variant    For Each deptKey In departments.Keys        Set scores = departments(deptKey)        ' 转换为数组计算统计量        Dim scoreArr() As Double        ReDim scoreArr(1 To scores.Count)        For i = 1 To scores.Count            scoreArr(i) = scores(i)        Next i        ' 计算均值和标准差        Dim meanVal As Double, stdDev As Double        meanVal = Application.WorksheetFunction.Average(scoreArr)        stdDev = Application.WorksheetFunction.StDev_S(scoreArr)        ' 标准化计算        Dim j As Long: j = 1        For i = 2 To lastRow            If ws.Cells(i, 2).Value = deptKey Then                If stdDev > 0 Then                    standardizedScore = 50 + ((ws.Cells(i, 3).Value - meanVal) / stdDev) * 10                    ' 限制在0-100分之间                    standardizedScore = Application.WorksheetFunction.Max(0, _                        Application.WorksheetFunction.Min(100, standardizedScore))                Else                    standardizedScore = 50                End If                ws.Cells(i, 4).Value = Round(standardizedScore, 1)                j = j + 1            End If        Next i    Next deptKey    ' 计算部门内排名和百分位    For Each deptKey In departments.Keys        Dim deptScores As Collection        Set deptScores = New Collection        ' 收集该部门标准化分数        For i = 2 To lastRow            If ws.Cells(i, 2).Value = deptKey Then                deptScores.Add ws.Cells(i, 4).Value            End If        Next i        ' 计算排名        Dim rankDict As Object        Set rankDict = CreateObject("Scripting.Dictionary")        ' 计算百分位排名        For i = 2 To lastRow            If ws.Cells(i, 2).Value = deptKey Then                Dim currentScore As Double                currentScore = ws.Cells(i, 4).Value                ' 计算排名                Dim rank As Long                rank = 1                Dim k As Long                For k = 2 To lastRow                    If ws.Cells(k, 2).Value = deptKey Then                        If ws.Cells(k, 4).Value > currentScore Then                            rank = rank + 1                        End If                    End If                Next k                ws.Cells(i, 5).Value = rank                ' 计算百分位                ws.Cells(i, 6).Value = 1 - (rank - 1) / (deptScores.Count - 1)            End If        Next i    Next deptKey    ' 全公司强制排名    Call ForceRankCompanyWide(ws, lastRow)    MsgBox "绩效标准化与强制排名完成！", vbInformationEnd Sub' 全公司强制排名Sub ForceRankCompanyWide(ws As Worksheet, lastRow As Long)    Dim i As Long    Dim totalEmployees As Long    ' 计算总人数    totalEmployees = lastRow - 1    ' 按标准化分数排序    Dim scores() As Double    ReDim scores(1 To totalEmployees)    For i = 1 To totalEmployees        scores(i) = ws.Cells(i + 1, 4).Value    Next i    ' 计算排名    For i = 2 To lastRow        Dim rank As Long        rank = Application.WorksheetFunction.Rank(ws.Cells(i, 4).Value, _            ws.Range(ws.Cells(2, 4), ws.Cells(lastRow, 4)), 0)        ws.Cells(i, 7).Value = "待定"        ' 按比例分配绩效等级        Dim percentile As Double        percentile = rank / totalEmployees        If percentile <= 0.2 Then            ws.Cells(i, 7).Value = "A"        ElseIf percentile <= 0.9 Then            ws.Cells(i, 7).Value = "B"        Else            ws.Cells(i, 7).Value = "C"        End If    Next i    ' 格式化    With ws.Range("A1:G1")        .Font.Bold = True        .Interior.Color = RGB(217, 225, 242)    End With    With ws.Range("A1:G" & lastRow)        .Borders.LineStyle = xlContinuous        .HorizontalAlignment = xlCenter    End With    ' 条件格式：绩效等级    Dim rng As Range    Set rng = ws.Range("G2:G" & lastRow)    rng.FormatConditions.Delete    ' A级 - 金色    With rng.FormatConditions.Add(Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""A""")        .Interior.Color = RGB(255, 215, 0) ' 金色        .Font.Color = RGB(0, 0, 0)    End With    ' B级 - 银色    With rng.FormatConditions.Add(Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""B""")        .Interior.Color = RGB(192, 192, 192) ' 银色        .Font.Color = RGB(0, 0, 0)    End With    ' C级 - 铜色    With rng.FormatConditions.Add(Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""C""")        .Interior.Color = RGB(205, 127, 50) ' 铜色        .Font.Color = RGB(255, 255, 255)    End WithEnd Sub

' 动态调整绩效分布比例Sub AdjustPerformanceDistribution()    Dim ws As Worksheet    Set ws = ThisWorkbook.Worksheets("绩效数据")    ' 获取用户输入的分布比例    Dim ratioA As Double, ratioB As Double, ratioC As Double    ratioA = Application.InputBox("请输入A级比例（如0.2表示20%）", "绩效分布设置", 0.2, Type:=1)    ratioB = Application.InputBox("请输入B级比例（如0.7表示70%）", "绩效分布设置", 0.7, Type:=1)    ratioC = Application.InputBox("请输入C级比例（如0.1表示10%）", "绩效分布设置", 0.1, Type:=1)    ' 验证比例总和为1    If Abs(ratioA + ratioB + ratioC - 1) > 0.001 Then        MsgBox "比例总和必须为100%，请重新输入！", vbExclamation        Exit Sub    End If    ' 重新计算绩效等级    Dim lastRow As Long    lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row    ' 按标准化分数排序    Dim scores As Collection    Set scores = New Collection    Dim i As Long    For i = 2 To lastRow        scores.Add ws.Cells(i, 4).Value    Next i    ' 获取排序后的分数    Dim sortedScores() As Double    ReDim sortedScores(1 To scores.Count)    For i = 1 To scores.Count        sortedScores(i) = scores(i)    Next i    ' 快速排序    Call QuickSort(sortedScores, 1, scores.Count)    ' 确定分界点    Dim cutoffA As Long, cutoffB As Long    cutoffA = Int(scores.Count * ratioA)    cutoffB = Int(scores.Count * (ratioA + ratioB))    ' 重新分配绩效等级    For i = 2 To lastRow        Dim currentScore As Double        currentScore = ws.Cells(i, 4).Value        ' 查找当前分数的排名        Dim rank As Long        rank = 1        Dim j As Long        For j = 1 To scores.Count            If sortedScores(j) > currentScore Then                rank = rank + 1            End If        Next j        ' 分配等级        If rank <= cutoffA Then            ws.Cells(i, 7).Value = "A"        ElseIf rank <= cutoffB Then            ws.Cells(i, 7).Value = "B"        Else            ws.Cells(i, 7).Value = "C"        End If    Next i    MsgBox "绩效分布已调整！A级：" & ratioA * 100 & "%，B级：" & ratioB * 100 & "%，C级：" & ratioC * 100 & "%", vbInformationEnd Sub' 快速排序算法Sub QuickSort(arr() As Double, first As Long, last As Long)    Dim pivot As Double    Dim i As Long, j As Long    Dim temp As Double    If first < last Then        pivot = arr((first + last) \ 2)        i = first        j = last        Do            Do While arr(i) > pivot                i = i + 1            Loop            Do While arr(j) < pivot                j = j - 1            Loop            If i <= j Then                temp = arr(i)                arr(i) = arr(j)                arr(j) = temp                i = i + 1                j = j - 1            End If        Loop While i <= j        If first < j Then QuickSort arr, first, j        If i < last Then QuickSort arr, i, last    End IfEnd Sub