수식에 한꺼번에 Round 씌우기

1. 문제

아래와 같이 B열의 숫자를 C열의 숫자로 나누면 값에 따라 소수점이하 자릿수가 다르게 표시됩니다.
이와 같은 경우 일정하게 소수점이하 둘째 자리로 통일하고자 할 경우 사용할 수 있는 매크로에 대해 설명하려고 합니다.

 

round씌우기.xlsx

0.01MB

 
 

2. 해법 찾기

가. 표시 형식 지정

반올림하는 것이라면 D열을 선택한 다음
셀 서식 - 표시형식에서
숫자 범주를 선택한 후 오른쪽의 소수 자릿수에서 0을 2로 수정하고 확인 버튼을 누르거나 엔터 키를 누르면 됩니다.

 
아래와 같이 소수점이하 자릿수가 2로 통일되면서 반올림됩니다.
4행의 경우 원래 값은 0.625인데, 반올림돼서 0.63으로 표시되고, 
3행의 경우 원래 값은 0.714286인데, 반올림돼서 0.71로 표시됩니다.

 
그러나, 이럴 경우 문제점은 더하기 등을 할 경우 소수점 셋째 자리 이하에 여전히 숫자가 있기 때문에 영향을 준다는 것입니다.
D열의 셀을 위, 아래로 2개씩 더해보면 대부분의 경우 눈으로 보는 값이 더한 값과 일치하는데,
5행의 경우에는 눈으로 보면 1.19가 나와야 하는데, 1.18로 표시됩니다.

 
이것은 실제 데이터는 0.625와 0.555556이기 때문에 이 둘을 더하면
1.180556이기 때문에 1.18로 표시되는 것입니다.
 
따라서, 이런 것을 방지하기 위해서는 소수점이하 자릿수를 제거할 필요가 있습니다.
 
그런데, 수식이 많을 경우 일일이 수식을 넣는다는 것은 어려우므로 VBA를 이용해야 합니다.
 

나. 매크로 작성

가능하면 Personal.XLSB에 저장하는 것이
엑셀 파일이 열리면서 같이 열리고, 여러 파일에 적용할 수 있기 때문에 좋습니다.

 
그러나, Personal.XLSB를 만드는 게 어려울 수 있으므로
XLSM 파일로 첨부합니다.
매크로 실행은 개발도구 - 매크로 - round씌우기를 누르면 되며,

PERSONAL.XLSB에도 round쒸우기 매크로가 있음

 
적용할 범위 선택,
반올림 함수 Round(반올림 1), RoundDown(절사 2), RoundUp(절상 3) 중에서 숫자로 선택,
소수점이하 자릿수 선택의
3단계로 이루어집니다.

매크로 실행 단계에서 떨어진 범위를 Ctrl 키를 눌러서 지정할 수 있는데,

 
순서를 바꿔서 적용할 범위를 먼저 선택하고 매크로를 실행해도 됩니다.
 
실행 결과는 아래와 같습니다.

 
매크로를 실행할 때마다 Round 함수가 앞에 씌워지므로,
예) =ROUND(ROUND(B1/C1,2),3)

 
Round 수식의 소수점 이하 자릿수만 수정할 때는
바꾸기(Ctrl+H)를 통해 찾을 내용에 2를 바꿀 내용에 3 등을 입력하고
모두 바꾸기를 해서 일괄적으로 바꾸는 것이 좋습니다.

 
그러면 2가 일관되게 3으로 바뀝니다. 

 
확인 버튼을 누르고 닫기 버튼을 누르고 보면. 여전히 소수점이하 자릿수가 2인데,
이것은 홈 탭, 표시 형식 그룹에서 '자릿수 늘림' 명령을 누르면 

 
아래와 같이 소수점이하 자릿수가 3으로 맞춰집니다.

 

Sub round씌우기()
    Dim rng서식범위 As Range, cell As Range
    Dim i As Long, j As Long

    If Selection.Count = 1 Then
        Set rng서식범위 = Application.InputBox("round함수를 추가할 범위를 선택하세요.", "범위 선택", Type:=8)
    Else
        Set rng서식범위 = Selection
    End If
    
    If rng서식범위 Is Nothing Then End
    
    i = Application.InputBox("반올림(1),절사(2),절상(3)함수에 해당하는 숫자를 입력하세요.", "함수 선택", Default:=1, Type:=1)
    
    If i >= 1 And i <= 3 Then
        Select Case i
            Case 1
                j = Application.InputBox("반올림할 자릿수를 입력하세요.", "자릿수 선택", Default:=0, Type:=1)
            Case 2
                j = Application.InputBox("절사할 자릿수를 입력하세요.", "자릿수 선택", Default:=0, Type:=1)
            Case 3
                j = Application.InputBox("절상할 자릿수를 입력하세요.", "자릿수 선택", Default:=0, Type:=1)
        End Select
        
        rng서식범위.Select
        
        Application.ScreenUpdating = False
        
        For Each cell In Selection
            If Len(cell) Then
                Select Case i
                    Case 1
                            cell.Value = "=round(" & IIf(Left(cell.Formula, 1) = "=", Mid(cell.Formula, 2), cell) & "," & j & ")"
                    Case 2
                            cell.Value = "=rounddown(" & IIf(Left(cell.Formula, 1) = "=", Mid(cell.Formula, 2), cell) & "," & j & ")"
                    Case 3
                            cell.Value = "=roundup(" & IIf(Left(cell.Formula, 1) = "=", Mid(cell.Formula, 2), cell) & "," & j & ")"
                End Select
            End If
        Next
    
        If j > 0 Then
            Selection.NumberFormatLocal = "#,##0." & Application.WorksheetFunction.Rept("0", j) & " "
        Else
            Selection.NumberFormat = "#,##0 "
        End If
        
        Application.ScreenUpdating = True
    End If

End Sub