如何在 excel/vba 中进行特殊类型的舍入 [英] How to do a special type of rounding in excel/vba

问题描述

我必须在 excel 中为我的学校项目进行特殊类型的舍入.

I have to do a special type of rounding in excel for my school project.

如果数字大于 #.5，我必须将其四舍五入.如果数字等于#.5，我必须将其四舍五入到最接近的偶数.如果数字小于 #.5，我必须将其四舍五入.

If the number is bigger than #.5 I have to round it up. If the number is equal to #.5 I have to round it to the nearest even number. If the number is less the #.5 I have to round it down.

我希望有人能够帮助我.

I hope somebody is able to help me.

Dim getal As Decimal = Nothing
    Console.WriteLine("voer een nummer in")
    getal = Console.ReadLine()

    Dim dec As Integer = Nothing
    Console.WriteLine("voer het aantal deciale in")
    dec = Console.ReadLine()

    Dim factor As Integer = 0
    Dim floor As Decimal = 0
    Dim decimaal As Decimal = 0
    Dim antwoord As Decimal = 0
    Dim percentage As Decimal = 0
    Dim GetalTimesFactor As Decimal = 0
    Dim OnNoukeurigheid As Decimal = 0
    Dim VerFactor As Integer = 0
    Dim HasFactor As Boolean = False
    Dim notation As String = Nothing
    Dim RoundUp As Decimal = 0

    If getal > 1000 Then
        While getal > 10
            getal = getal / 10
            VerFactor = VerFactor + 1
            HasFactor = True
        End While
        Console.WriteLine("getal: " & getal)


    End If

    If getal < 0.0001 Then
        While getal < 1
            getal = getal * 10
            VerFactor = VerFactor - 1
            HasFactor = True
        End While
    End If

    Select Case dec
        Case 0
            factor = 1
            OnNoukeurigheid = 0.5
            RoundUp = 1
        Case 1
            factor = 10
            OnNoukeurigheid = 0.05
            RoundUp = 0.1
        Case 2
            factor = 100
            OnNoukeurigheid = 0.005
            RoundUp = 0.01
        Case 3
            factor = 1000
            OnNoukeurigheid = 0.0005
            RoundUp = 0.001
    End Select

    GetalTimesFactor = getal * factor
    floor = Decimal.Floor(GetalTimesFactor)
    floor = floor / factor
    decimaal = getal - floor
    Console.WriteLine("floor: " & floor)

    If decimaal > OnNoukeurigheid Then
        floor = floor * factor
        antwoord = floor + 1
        antwoord = antwoord / factor
    ElseIf decimaal = OnNoukeurigheid Then
        antwoord = Decimal.Round(getal, dec, MidpointRounding.ToEven)
    Else
        Console.WriteLine("decimaal: " & decimaal)
        Console.WriteLine("getal: " & getal)
        percentage = (decimaal / getal) * 100
        If percentage < 5 Then
            Console.WriteLine("percentage is: " & Decimal.Round(percentage, 1) & "%")
            antwoord = floor
        Else
            Console.WriteLine("percentage is: " & Decimal.Round(percentage, 1) & "%")
            antwoord = floor + RoundUp
        End If
    End If

    If HasFactor Then
        notation = "E" & Format(VerFactor, "00")
    End If

    Console.WriteLine(antwoord & notation)
    Console.ReadLine()

这是我在express中所做的，它确实有效，但在宏中无效

this is what i have made in express it does work but it doesn't work in macro

注意:对荷兰变量表示抱歉

note: sorry for the dutch varables

推荐答案

在 VBA 中很简单，因为 VBA 的 Round 函数会做那种四舍五入:

In VBA it is quite simple, since the VBA Round function does that kind of Rounding:

Function VBARound(N As Double, Optional NumPlaces As Long = 0) As Double
    VBARound = Round(N, NumPlaces)
End Function

NUMPLACES 将允许您选择四舍五入到非整数.

The NUMPLACES will allow you to optionally round to other than a whole number.

这篇关于如何在 excel/vba 中进行特殊类型的舍入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！