如何最好地将百分比转换为Excel中的分数? [英] How to best convert percentage to score in Excel?

问题描述

我需要使用内置公式(首选)或在VBA中编写自定义函数，将百分比转换为Excel 2003中的评分系统.公式或函数必须接受一个允许我指定最高得分的参数.

I need to convert a percentage to a scoring system in Excel 2003, either using a built-in formula (preferable) or writing a custom function in VBA. The formula or function must accept a parameter allowing me to specify the maximum score.

例如，假设有一个名为 Pct2Score(Pct As String，MaxScore As Integer)的函数便可以执行此操作.

For example, let's say there's a function called Pct2Score(Pct As String, MaxScore As Integer) that does just this.

Pct2Score("65％"，4)将返回 3 ，因为它将使用以下分布:

Pct2Score("65%", 4) would return 3 because it would use this distribution:

0%-24.9% = 1
25-49.9% = 2
50-74.9% = 3
75-100% = 4

Pct2Score("65％"，5)将返回 4 ，因为它将使用以下分布:

Pct2Score("65%", 5) would return 4 because it would use this distribution:

0-19.9% = 1
20%-39.9% = 2
40-59.9% = 3
60-79.9% = 4
80-100% = 5

以此类推.我希望Excel中有一个内置的分布公式可以完全做到这一点，这样我就不必依赖VBA.甚至可以结合使用公式来实现此目的.唯一的限制是我需要将所有内容都包含在一个公式中.

And so on so forth. I'm hoping there is a built-in distribution formula in Excel that does exactly this so that I won't have to rely on VBA. It's even OK to use a combination of formulas to achieve this; the only limitation is that I need everything to be contained in one formula.

推荐答案

一个简单的公式(描述性而非Excel)就是:

A simple formula (descriptive, not Excel) is simply:

1 + INT((Pct AS DOUBLE) * MaxScore / 100).

使用VBA:

Public Function Pct2Score(pct As String, MaxScore As Integer) As Integer
    Dim pctD As Double
    If (Right(pct, 1) = "%") Then pct = Left(pct, Len(pct) - 1)
    pctD = CDbl(pct)
    Pct2Score = 1 + Int(pctD * MaxScore / 100)
End Function

---

只需一个公式，您就可以完成

---

With a single formula you can do

=1+INT(A1*B1)

其中A1是Pct，B1是MaxScore.

where A1 is Pct and B1 is MaxScore.

这篇关于如何最好地将百分比转换为Excel中的分数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！