Excel-多像元计算 [英] Excel - Multiple Cell Calculation

问题描述

我是一名老师，正在尝试创建分数表，我遇到的问题是，作业使用不同的方案进行了标记(例如，％[1-100]，Level [1-4-]，Letter Grade [F-A] ).我的问题是，我只想使用一种标记方案进行报告，而需要根据已标记的方案来转换或计算其他标记方案.

I'm a teacher and trying to create a mark sheet the problem I have is that assignments are marked using different schemes (i.e. % [1 - 100], Level [1 -4], Letter Grade [F - A]). The problem I have is that I only want to report using one marking scheme and need to convert or calculate the other marking schemes based on the scheme there is it is marked.

我制作了一个excel工作表，其中包含学生的姓名和作业.该工作表还具有其他3列，每个列代表每个方案字母"，级别"，每个方案的百分比列.现在，在任何给定时间，方案"列之一中都会有一个值-我需要excel能够自动或通过宏将适当的值计入其他单元格中.

I've made an excel worksheet which contains a student names and assignments. The sheet then has 3 other columns, one representing each scheme "Letter", "Level", Percentage a column for each scheme. Now, at any given time there is going to be a value in one of the 'scheme' columns - I need excel to automatically or by macro calculate the appropriate value into the other cells.

例如，在同一张纸上有三个作业，每个作业都用不同的方案标记.

For example, there are three assignments each marked with a different scheme on the same sheet.

Assignment 1 - %
Assignment 2 - Level 
Assignment 3 - Letter Grade

如何让excel检查存在的标记值，然后填充/计算其他两个相应的值.

How can I get excel to check which mark value exists and then populate/calculate the other two the corresponding values.

迈克在作业1上获得80％(％)，但我希望excel同时在水平栏和字母列中填充相应的值作业2的等级3 excel计算相同百分比和字母并在同一时间作业3中字母A的时间，应计算相应的百分比和水平.

Mike got 80% (%) on Assignment 1, but I want excel to populate the level column and letter column with the corresponding values at the same time Level 3 on Assignment 2 excel to calculate corresponding percentage and letter and at the same time for a Letter A on Assignment 3 the corresponding % and level should be calculated.

不知道这是否可行或有意义，但是我尝试了VLOOKUP，但未成功，因为我必须手动完成所有操作-我的目标是自动化流程.

Don't know if this is possible or makes sense, but I tried VLOOKUP and was not successful because I have to do it all manually - I'm aiming for an automated process.

很抱歉，如果不清楚或令人困惑...但是我已经呆了两天，没有运气，甚至不确定我在做什么是否可能.

Sorry if it is not clear or confusing...but I've been at this for two days with no luck and not even sure if what I'm doing is possible.

推荐答案

像这样创建表(如果需要定义+/-分数(例如"B +"等)，则可以添加更多行).此处的关键是按％升序对表格进行排序，每一行都是相应的级别/字母等级"所需的最低得分.

Create a table like so (you can add more rows if you need to define +/- scores like "B+", etc.). The key here is sort the table ascending by %, with each row being the minimum score needed for the corresponding Level/Letter Grade.

然后，您可以针对此表使用VLOOKUP公式，就像这样获得级别:

Then, you can use VLOOKUP formula against this table, like so to get the Level:

=VLOOKUP(F2,$A$1:$C$7,2,TRUE)

像这样，获得字母等级:

And like so, to get the Letter Grade:

=VLOOKUP(F2,$A$1:$C$7,3,TRUE)

这利用了VLOOKUP函数中的True参数，该参数将返回近似匹配(并假设数据按升序排序).因此，当您输入"81％"之类的值时，它将返回最接近的行，该行不大于 81％，因此它将返回第5行的数据.同样，有12％将从第5行返回数据第2行，第4行的75％，依此类推.

This takes advantage of the True parameter in the VLOOKUP function, which will return an approximate match (and assumes data is sorted ascending). So, when you enter a value like "81%", it returns the nearest row which is not greater than 81%, so it will return data from row 5. Likewise, 12% will return data from row 2, 75% from row 4, etc.

您的结果:

如果您无法将级别/字母等级" 1:1映射到百分比"行，则可能需要使用两个表，但是想法是相同的.

You may need to use two tables if you can't 1:1 map the Level/Letter Grade to a Percent row, but the idea would be the same.

这篇关于Excel-多像元计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！