需要帮助将一系列数字转换成excel中的文本

I'm using MS Excel 2010; I'm a total newbie to using functions, so be kind when answering.

我用的是MS Excel 2010;我是一个使用函数的新手，所以回答的时候要友善。

I'm creating a sports grading sheet that will help instructors calculate the test scores for their students. So far I've set up excel to add a students individual points in a column and then give a percentage value out of 100%

我正在制作一个运动评分表，帮助老师计算学生的考试成绩。到目前为止，我已经建立了excel，在一个列中添加一个学生的个人分数，然后给出100%的百分比值

What I want to do now is have the percentage value be converted to a selection of text result. For example, if a student gets 76.76% on their test; then their mark is considered a 'conditional pass' as it falls between the ranges of 75%-85%

我现在要做的是将百分比值转换为文本结果的选择。例如，如果一个学生在考试中得到76.76%的分数;然后他们的分数被认为是“有条件的通过”，因为它在75%到85%之间

I need excel to determine which range their test results fall under and then automatically enter the text result in the cell underneath, e.g.:

我需要excel确定他们的测试结果属于哪个范围，然后在下面的单元格中自动输入文本结果，例如:

Name: Student Percentage total: 76.76% Result: Conditional Pass

姓名:学生百分比合计:76.76%结果:有条件通过

I'm going to have to assign every possible number (including decimal points) to one of three text results.

我将不得不将所有可能的数字(包括小数点)分配给三个文本结果中的一个。

Hope this makes some sense. Thanks!

希望这是有道理的。谢谢!

1 个解决方案

#1

Jeepeds answer is a great way to do this, so I'll help you a bit more.

Jeepeds的回答是一个很好的方法，所以我将帮助你更多。

Jeeped specified a table of values in his answer using {0,0.5,0.75,0.85,0.95} which will work, but it means the values are in every formula of every cell. The way below allows you to maintain a separate list of values that define the result text associated with a range of percentages

Jeeped使用{0,0.5,0.75,0.85,0.95}在他的答案中指定了一个值表，这将会起作用，但是它意味着值在每个单元格的每个公式中。下面的方法允许您维护一个单独的值列表，该列表定义与百分比范围相关联的结果文本

I would set up a a range with a table of values as follows.

我将设置一个具有以下值表的范围。

LowerMark       UpperMark          ResultText
0.0             0.50             Fail
0.50            0.60             Ok Pass
0.60            0.75             Contional Pass
0.75            0.85             Good Pass
0.85            0.90             Excellent
0.90            1                Brilliant
1               9999             This should never happen

Lets say the above table is in sheet2 in cells A1 to C7. Note that the upper mark column is not really needed, but it helps "read the table". (It could be set using a formula that gets it's value form the next rows LowerMark)

假设上面的表位于A1到C7单元的sheet2中。注意，上面的标记列不是真正需要的，但它有助于“读取表”。(它可以使用一个公式来设置，它的值从下一行的小写标记中获取)

In you main list that has the pupil marks, the cell that you want to display the "Result Text" will need a formula like the following. This formula refers to cell D6 which is the cell that has the numeric percentage they obtained.

在有学生标记的主列表中，想要显示“结果文本”的单元格需要如下的公式。这个公式指的是单元格D6，它是具有它们获得的数值百分比的单元格。

 =VLOOKUP('Sheet2'!$A$1:$C$7, D6, 3, TRUE)

Note that the table of values assumes that you are calculating and storing the percentage for each child as a number that ranges from 0 to 1 (not 0 to 100)

注意，值表假定您正在计算并存储每个子节点的百分比，其数值范围为0到1(而不是0到100)

#1