如何在Excel中最好地将百分比转换为分数？

发布于 2024-12-11 11:42:46 字数 601 浏览 0 评论 0原文

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

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

Pct2Score("65%", 4) 将返回 3，因为它将使用此分布：

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

Pct2Score("65%", 5) 将返回 4 因为它将使用此分布：

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

等等。我希望 Excel 中有一个内置的分布公式可以完成此操作，这样我就不必依赖 VBA。甚至可以使用公式组合来实现此目的；唯一的限制是我需要将所有内容都包含在一个公式中。

原文

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.

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

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) 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

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦晓ヶ微光ヅ倾城 2024-12-18 11:42:46

一个简单的公式（描述性的，而不是 Excel）很简单：

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

使用一个公式，您可以完成

=1+INT(A1*B1)

A1 为 Pct、B1 为 MaxScore 的操作。

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

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

Using 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