多值报表与排序：如何自动生成员工月度绩效榜首统计？

Absolutely! You can ditch the tedious monthly manual work and fully automate the creation of PAGE 2 (your top performer summary) from your PAGE 1 target completion data. Below are step-by-step solutions for the two most common tools used for this kind of task:

Option 1: Excel/Google Sheets (No Coding Required) #

This is perfect if you’re working directly in spreadsheets. First, make sure your PAGE 1 data is structured as a table (in Excel, select your data and press Ctrl+T; in Google Sheets, go to Data > Create a filter).

Step 1: Set up PAGE 2 structure #

Create a clean layout in your PAGE 2 sheet like this:

Step 2: Add formulas to pull top data #

Assuming your PAGE 1 data has:

• Names in column B
• TAR 1 % in column C
• TAR 2 % in column D
• TAR 3 % in column E
• TAR 4 % in column F

Use these formulas in PAGE 2:

• For Top % values (e.g., cell C2 for TAR 1):

  =MAX(PAGE1!C:C)
  

• For corresponding names (e.g., cell B2 for TAR 1):
  • If only one top performer is expected:

    =INDEX(PAGE1!B:B, MATCH(MAX(PAGE1!C:C), PAGE1!C:C, 0))
    

  • If you need to handle ties (show all top performers):

    =TEXTJOIN(", ", TRUE, IF(PAGE1!C:C=MAX(PAGE1!C:C), PAGE1!B:B, ""))
    

    Note: In Excel, enter this as an array formula by pressing Ctrl+Shift+Enter; in Google Sheets, just press Enter.

Step 3: Auto-refresh #

Every month, update your PAGE 1 data, and PAGE 2 will automatically refresh with the latest top performers—no manual copy-pasting needed.

Option 2: Python (For Larger Datasets or Advanced Automation) #

If you’re comfortable with code, using Python and the pandas library lets you automate the entire process (including updating the Excel file directly).

Step 1: Install dependencies #

First, install the required libraries if you haven’t already:

pip install pandas openpyxl

Step 2: Run the script #

Use this code to read PAGE 1, calculate top performers, and write to PAGE 2:

import pandas as pd

# Load your report file (replace with your actual file path)
file_path = "monthly_target_report.xlsx"

# Read PAGE 1 data
df = pd.read_excel(file_path, sheet_name="PAGE 1")

# Clean up column names (remove any extra spaces)
df.columns = df.columns.str.strip()

# Initialize a dictionary to store top performers for each target
top_performers = {}

# Iterate through each target column
target_columns = ["TAR 1 %", "TAR 2 %", "TAR 3 %", "TAR 4 %"]
for col in target_columns:
    # Get the maximum percentage for the target
    max_percent = df[col].max()
    # Get all names that achieved this max percentage
    top_names = df[df[col] == max_percent]["NAME"].tolist()
    # Store in the dictionary
    top_performers[col] = {
        "Name of top %": ", ".join(top_names),
        "Top %": max_percent
    }

# Convert the dictionary to a DataFrame for PAGE 2
page2_df = pd.DataFrame.from_dict(top_performers, orient="index").reset_index()
page2_df.columns = ["Target", "Name of top %", "Top %"]

# Write PAGE 2 back to the Excel file (replace existing PAGE 2 if it exists)
with pd.ExcelWriter(file_path, mode="a", if_sheet_exists="replace", engine="openpyxl") as writer:
    page2_df.to_excel(writer, sheet_name="PAGE 2", index=False)

print("PAGE 2 successfully updated with top performers!")

This script will automatically update your Excel file with the latest top performers every time you run it—you can even set it up to run on a schedule if you want to go fully hands-off.

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