如何将非数字等级转换为对应分数并计算每位学生的平均成绩？

Hey there! Let's walk through solving this problem step by step—super straightforward once you break it down. You've got three sheets: raw student grades, a grading scale, and need to compute each student's average score. Here's how to do it in Excel or Google Sheets (the functions are nearly identical):

Step 1: Convert letter grades to numerical points in Sheet1 #

First, let's add a new column in Sheet1 to turn those letter grades into their corresponding points using the scale from Sheet2. Let's say we add column D right next to column C (where your letter grades live).

In cell D2 (assuming your data starts at row 2), paste this formula:
=VLOOKUP(C2, Sheet2!$A$2:$B$5, 2, FALSE)

Quick breakdown of what each part does:

• C2: The cell with the letter grade we want to convert
• Sheet2!$A$2:$B$5: The locked range in Sheet2 where your grade-to-point mapping lives (the $ signs make sure the range doesn't shift when you drag the formula down)
• 2: Tells the function to return the value from the second column of that range (the numerical points)
• FALSE: Ensures we get an exact match for the letter grade (no partial matches here!)

Drag this formula down all rows in Sheet1, and you'll have numerical points for every single grade entry.

Step 2: Calculate average score per student in Sheet3 #

Over in Sheet3, let's assume you've got a list of unique student names in column A (like Mark, Dave, Carol). In column B, we'll compute their average score.

In cell B2 (next to the first student name), use this formula:
=AVERAGEIF(Sheet1!$A$2:$A$7, A2, Sheet1!$D$2:$D$7)

Or if you prefer using SUM and COUNT (same end result):
=SUMIF(Sheet1!$A$2:$A$7, A2, Sheet1!$D$2:$D$7)/COUNTIF(Sheet1!$A$2:$A$7, A2)

For the AVERAGEIF version:

• Sheet1!$A$2:$A$7: The range in Sheet1 where student names are stored
• A2: The specific student name in Sheet3 we're calculating the average for
• Sheet1!$D$2:$D$7: The range of converted numerical points from Step 1

Drag this formula down for all students in Sheet3, and you'll get each student's average score based on the grading scale.

Bonus: Skip the extra column in Sheet1 (if you want) #

You can combine the VLOOKUP and AVERAGEIF into one formula in Sheet3, though it's a bit longer. Here's how:
=AVERAGEIF(Sheet1!$A$2:$A$7, A2, VLOOKUP(Sheet1!$C$2:$C$7, Sheet2!$A$2:$B$5, 2, FALSE))
Note: In Excel, you'll need to press Ctrl+Shift+Enter to run this as an array formula; in Google Sheets, it works as-is. The two-step method is usually easier to read and troubleshoot, though!

Testing this with your sample data gives exactly what you'd expect:

• Mark: (4 + 4)/2 = 4.0
• Dave: (2 + 3)/2 = 2.5
• Carol: (3 + 4)/2 = 3.5

备注：内容来源于stack exchange，提问作者Ferdinand Winstein