vba 如何将小数转换为分数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/706468/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I convert a decimal to a fraction?
提问by Daniel Brückner
How do I convert a indefinite decimal (i.e. .333333333...) to a string fraction representation (i.e. "1/3"). I am using VBA and the following is the code I used (i get an overflow error at the line "b = a Mod b":
如何将不定小数（即 .333333333...）转换为字符串分数表示（即“1/3”）。我正在使用 VBA，以下是我使用的代码（我在“b = a Mod b”行出现溢出错误：
Function GetFraction(ByVal Num As Double) As String
If Num = 0# Then
GetFraction = "None"
Else
Dim WholeNumber As Integer
Dim DecimalNumber As Double
Dim Numerator As Double
Dim Denomenator As Double
Dim a, b, t As Double
WholeNumber = Fix(Num)
DecimalNumber = Num - Fix(Num)
Numerator = DecimalNumber * 10 ^ (Len(CStr(DecimalNumber)) - 2)
Denomenator = 10 ^ (Len(CStr(DecimalNumber)) - 2)
If Numerator = 0 Then
GetFraction = WholeNumber
Else
a = Numerator
b = Denomenator
t = 0
While b <> 0
t = b
b = a Mod b
a = t
Wend
If WholeNumber = 0 Then
GetFraction = CStr(Numerator / a) & "/" & CStr(Denomenator / a)
Else
GetFraction = CStr(WholeNumber) & " " & CStr(Numerator / a) & "/" & CStr(Denomenator / a)
End If
End If
End If
End Function
回答by Daniel Brückner
As .333333333 is not 1/3 you will never get 1/3 but instead 333333333/1000000000 if you do not add some clever "un-rounding" logic.
由于 .333333333 不是 1/3，如果您不添加一些巧妙的“非四舍五入”逻辑，您将永远不会得到 1/3，而是 333333333/1000000000。
Here is a solution for handling numbers with periodic decimal representation I remember from school.
这是我在学校记得的处理具有周期性十进制表示的数字的解决方案。
A number 0.abcdabcd... equals abcd/9999. So 0.23572357... equals 2357/9999 exactly. Just take that many 9s as your pattern is long. 0.11111... equals 1/9, 0.121212... equals 12/99, and so on. So try just searching a pattern and setting the denominator to the corresponding number. Of course you have to stop after some digits because you will never know if the pattern is repeated for ever or just many times. And you will hit the rounding error in the last digit, so you still need some clever logic.
数字0。abcdabcd... 等于 abcd/9999。所以 0. 23572357... 正好等于 2357/9999。因为你的模式很长，所以只需要那么多的 9。0. 11111... 等于1/9，0. 121212... 等于 12/99，依此类推。所以试着只搜索一个模式并将分母设置为相应的数字。当然，您必须在一些数字后停止，因为您永远不会知道该模式是永远重复还是多次重复。而且你会在最后一位遇到舍入错误，所以你仍然需要一些聪明的逻辑。
回答by Oorang
This only works in Excel-VBA but since you had it tagged "VBA" I will suggest it. Excel has a custom "fraction" format that you can access via "Format Cells" (or ctrl-1 if you prefer). This particular number format is Excel-Specific and so does not work with the VBA.Format function. It doeshowever work with the Excel Formula TEXT(). (Which is the Excel equivalent of VBA.Format. This can be accessed like So:
这仅适用于 Excel-VBA，但由于您将其标记为“VBA”，因此我建议您这样做。Excel 具有自定义的“分数”格式，您可以通过“格式单元格”（或 ctrl-1，如果您愿意）访问该格式。此特定数字格式是 Excel 特定的，因此不适用于 VBA.Format 函数。然而，它确实适用于 Excel 公式 TEXT()。（这是 VBA.Format 的 Excel 等价物。这可以像这样访问：
Sub Example()
MsgBox Excel.WorksheetFunction.Text(.3333,"# ?/?")
End Sub
To show more than one digit (Example 5/12) just up the number of question marks.
要显示一个以上的数字（示例 5/12），只需增加问号的数量。
回答by Ken
Google for "decimal to fraction" and you'll get about a gazillion results.
谷歌搜索“小数到分数”，你会得到大约无数的结果。
I really like this one, because it's simple, has source code (in RPL, similar to Forth, ~25 lines), and is pretty fast (it's written to run on a 4-bit, 4MHz CPU). The docs say:
我真的很喜欢这个，因为它很简单，有源代码（在 RPL 中，类似于 Forth，约 25 行），而且速度非常快（它是为在 4 位、4MHz CPU 上运行而编写的）。文档说：
In a book called Textbook of Algebraby G. Chrystal, 1st edition in 1889, in Part II, Chapter 32, this improved continued fraction algorithm is presented and proven. Odd to tell, Chrystal speaks of it as if it were ancient knowledge.
在G. Chrystal所著的《代数教科书》（1889 年第 1 版）的第二部分第 32 章中，介绍并证明了这种改进的连分数算法。说来奇怪，克里斯塔尔把它说成是古老的知识。
回答by shoosh
回答by user86317
Python has a nice routine in its fractions module. Here is the working portion that converts a n/d into the closest approximation N/D where D <= some maximum value. e.g. if you want to find the closest fraction to 0.347, let n=347,d=1000 and max_denominator be 100 and you will obtain (17, 49) which is as close as you can get for denominators less than or equal to 100. The '//' operator is integer division so that 2//3 gives 0, i.e. a//b = int(a/b).
Python 在其分数模块中有一个很好的例程。这是将 an/d 转换为最接近的近似值 N/D 的工作部分，其中 D <= 某个最大值。例如，如果您想找到最接近 0.347 的分数，让 n=347,d=1000 和 max_denominator 为 100，您将获得 (17, 49)，这与分母小于或等于 100 时所能得到的最接近。 '//' 运算符是整数除法，因此 2//3 得到 0，即 a//b = int(a/b)。
def approxFrac(n,d,max_denominator):
#give a representation of n/d as N/D where D<=max_denominator
#from python 2.6 fractions.py
#
# reduce by gcd and only run algorithm if d>maxdenominator
g, b = n, d
while b:
g, b = b, g%b
n, d = n/g, d/g
if d <= max_denominator:
return (n,d)
nn, dd = n, d
p0, q0, p1, q1 = 0, 1, 1, 0
while True:
a = nn//dd
q2 = q0+a*q1
if q2 > max_denominator:
break
p0, q0, p1, q1 = p1, q1, p0+a*p1, q2
nn, dd = dd, nn-a*dd
k = (max_denominator-q0)//q1
bound1 = (p0+k*p1, q0+k*q1)
bound2 = (p1, q1)
if abs(bound2[0]*d - bound2[1]*n) <= abs(bound1[0]*d - bound1[1]*n):
return bound2
else:
return bound1
回答by Dan
Similar to CookieOfFortune's, but it's in VB and doesn't use as much brute force.
类似于 CookieOfFortune 的，但它是在 VB 中的，并且不使用那么多的蛮力。
Dim tolerance As Double = 0.1 'Fraction has to be at least this close'
Dim decimalValue As Double = 0.125 'Original value to convert'
Dim highestDenominator = 100 'Highest denominator you`re willing to accept'
For denominator As Integer = 2 To highestDenominator - 1
'Find the closest numerator'
Dim numerator As Integer = Math.Round(denominator * decimalValue)
'Check if the fraction`s close enough'
If Abs(numerator / denominator - decimalValue) <= tolerance Then
Return numerator & "/" & denominator
End If
Next
'Didn't find one. Use the highest possible denominator'
Return Math.Round(denominator * decimalValue) & "/" & highestDenominator
...Let me know if it needs to account for values greater than 1, and I can adjust it.
...让我知道它是否需要考虑大于 1 的值，我可以调整它。
EDIT:Sorry for the goofed up syntax highlighting. I can't figure out why it's all wrong. If someone knows how I can make it better, please let me know.
编辑：抱歉语法高亮显示错误。我不明白为什么这一切都是错的。如果有人知道我怎样才能使它变得更好，请告诉我。
回答by Brann
I would multiply by 10000000(or whatever you want depending on the precision), then simplify the resulting fraction (ie n*10000000/10000000)
我会乘以 10000000（或任何你想要的取决于精度），然后简化结果分数（即 n*10000000/10000000）
回答by CookieOfFortune
You can approximate it. Essentially cycle through all numerators and denominators until you reach a fraction that is close to what you want.
你可以近似它。基本上循环遍历所有分子和分母，直到得到一个接近你想要的分数。
int num = 1;
int den = 1;
double limit == 0.1;
double fraction = num / den;
while(den < 1000000 ) // some arbitrary large denominator
{
den = den + 1;
for(num = 0; num <= den; num++)
{
fraction = num / den;
if(fraction < n + limit && fraction > n - limit)
return (num + "/" + den);
}
}
This is slow and a brute force algorithm, but you should get the general idea.
这是一种缓慢且蛮力的算法，但您应该了解总体思路。
回答by dbrown0708
In general, it'll be easier if you find the repeating part of the rational number. If you can't find that, you'll have a tough time. Let's say the number if 8.45735735735...
一般来说，如果你找到有理数的重复部分会更容易。如果你找不到那个，你会很难过。让我们说这个数字，如果 8.45735735735...
The answer is 8 + 45/100 + 735/999/100 = 8 1523/3330.
答案是 8 + 45/100 + 735/999/100 = 8 1523/3330。
The whole number is 8. Add 45/100 - which is .45, the part before the repeating part.
整数是 8。加上 45/100 - 即 0.45，即重复部分之前的部分。
The repeating part is 735/999. In general, take the repeating part. Make it the numerator. The denominator is 10^(number of repeating digits) - 1.
重复部分是 735/999。一般来说，取重复部分。使它成为分子。分母是 10^(重复数字的个数) - 1。
Take the repeating part and shift it the appropriate number of digits. In this case, two, which means divide by 100, so 735/999/100.
取重复部分并将其移动适当的位数。在这种情况下，2，即除以 100，即 735/999/100。
Once you figure those parts out, you just need some code that adds and reduces fractions using greatest common fractions ...
一旦你弄清楚了这些部分，你只需要一些代码来使用最大的公共分数来增加和减少分数......
回答by Akash Kava
1/ .3333333333 = 3 because 1/3 = .3333333333333, so whatever number you get do this,
1/ .3333333333 = 3 因为 1/3 = .3333333333333，所以无论你得到什么数字，
double x = 1 / yourDecimal; int y = Math.Ceil(x);
双 x = 1 / yourDecimal; int y = Math.Ceil(x);
and now Display "1/" + y
现在显示“1/”+ y
