[엑셀] 수식을 값으로 바꾸는 사용자정의함수 만들기

수량산출 전문 프로그램이 아닌 엑셀에서 수량산출을 하는 경우가 많이 있습니다. 엑셀에서 수량산출을 해보신분은 좌측에는 수식을 입력하고 우측에는 좌측에 입력한 수식의 값이 나타나도록 했으면 좋겠는데 쉽지가 않습니다.

여러가지 방법이 있지만 가장 간단한 방법은 리본메뉴에서 [수식] - [이름정의]를 선택해서 EVALUATE함수를 사용하는 방법입니다. 그런데 [이름정의]를 통한 방법은 사용에 몇가지 불편한 점이 있습니다.

이런 불편한 점을 해소하기 위해 [SUM 함수]처럼 편하게 사용할 수 있는 [사용자정의 함수]를 만들어 사용해 보겠습니다.

[사용자정의 함수]는 사용자가 직접 만든 함수이며 좀더 깊게 알고 싶은 분은 카페나 블로그를 검색해보세요. 여기서는 단지 산식을 값으로 바꾸는 함수를 수량산출 파일에 적용하는 방법에 대해서만 설명 드리겠습니다.

VB Editor 창 열고 모듈만들기

코드 입력하기

Function TEVALUATE(ByVal strTemp As String) As Double
    Dim strMe As String
    Dim strF As String
    Dim strG As String
    Dim i As Integer, j As Integer
        i = Len(strTemp)
        For j = 1 To i
            strF = Mid(strTemp, j, 1)
            If IsNumeric(strF) Then
            Else
                Select Case strF
                    Case "*", "/", ".", "+", "-", "^", "(", ")", "%"
                    Case "X", "x"
                        strF = "*"
                    Case "÷"
                        strF = "/"
                    Case "＋"
                        strF = "+"
                    Case "－"
                        strF = "-"
                    Case Else
                        strF = ""
                End Select
            End If
            strMe = strMe & strF
        Next j
    
    If strTemp = "" Then strMe = 0
    If strMe = "" Then strMe = 0
    
    TEVALUATE = Evaluate(strMe)
End Function

상기 코드를 아래그림과 같이 생성된 모듈의 ①[코드 입력창]에 복사해서 붙여넣기 합니다. 코드는 제가 작성한것은 아님을 밝히며 엑셀책에서 부록으로 받은것 같은데 굉장히 천재적인 분인것 같습니다. 출처는 확인되는대로 알려드리겠습니다.

저장을 누르고 잘 입력되었는지 엑셀 창으로 돌아와 테스트를 해봅니다.

테스트방법

[Shift + F3] 하시고 함수 마법사 창에서 [범주선택] 에서 [사용자정의함수]를 선택하시면 TEVALUATE 라고 나타나면 정상입니다.
셀에서 [=TEVALUATE] 를 입력합니다. 아래 그림과 같이 ①[=TE]까지만 눌러도 TE가 포함된 함수가 나열되며 [=TEVALUATE] 함수가 있음을 확인 할 수 있습니다.

사용자정의 함수 사용

위와 같이 참조셀을 입력하여 수식을 끝까지 압력합니다.
입력한 수식을 드래그 하여 값을 입력할 셀 모두 함수를 입력합니다.

[사용자정의함수]를 사용시 장점

각 시트별로 이름정의를 해야하는 번거로움이 없음
값이 들어가는 위치를 일반 함수 사용하듯이 자유로이 사용
"개소", "높이", "폭" 등 수식에 문자를 같이 쓸수 있어 산출서의 검토가 편리합니다.

첨부파일

수식을값으로.xlsm