텍스트와 섞여 있는 수식을 계산하는 방법

안녕하세요.

 

문서를 작성하다 보면 하나의 셀에 텍스트와 수식을 섞어 입력하고, 이것을 계산해야 하는 경우가 있습니다.

 

 

실습 파일 다운로드

세출예산.xlsx

0.01MB

CalStr.txt

0.00MB

 

G열에 계산할 숫자와 연산자, 그리고 단위를 나타내는 텍스트가 같이 한 셀에 들어 있습니다.

 

계산할 셀이 많지 않고, 산출식 내의 값을 한 번 작성한 뒤 바꾸지 않는다면 복잡하게 사용자 정의 함수를 이용하지 않고 수동으로 계산식을 만드는 것이 더 합리적인 선택입니다.

계산할 셀이 많고, 작성한 산출식의 값을 적당히 수정해서 최적의 값을 찾는 작업을 해야 한다면 값을 바꿀 때마다 바로 수정되도록 사용자 정의 함수를 만들어 써야 합니다.

 

셀 내용에서 계산에 필요한 숫자와 연산자만 걸러서 수식을 완성하고 결과를 반환하는 사용자 정의 함수 CalStr 입니다.

Function CalStr(strInput As String)
    Dim i As Integer
    Dim Temp As String, strText As String
    
    On Error Resume Next
    
    Temp = ""
    
    For i = 1 To Len(strInput) + 1
        strText = Mid(strInput, i, 1)
    
        If strText Like "[0-9+*/.%()-]" Then
            Temp = Temp & strText
        End If
    Next i
    
    If Temp = "" Then
        CalStr = 0
    Else
        CalStr = Application.Evaluate(Temp)
    End If
End Function

 

사용자 정의 함수를 엑셀 파일에 적용하려면 첨부된 파일을 다운로드해서 텍스트 편집기로 엽니다.

<Ctrl + A>를 눌러 전체 선택하고, <Ctrl + C>를 눌러 복사합니다.

 

엑셀 파일을 열거나 이미 열려 있다면 선택해서 활성화하고 <Alt + F11>을 눌러 VB 편집기(VBE)를 실행합니다.

[삽입]-[모듈]을 선택하거나 <Alt, I, M>을 눌러 모듈을 추가합니다.

<Ctrl + V>를 눌러 붙여넣기 합니다.

 

<Alt + Q>를 눌러 VBE를 닫고 엑셀 창으로 돌아가거나 <Alt + F11>을 눌러 엑셀 창으로 전환을 합니다.

 

[H3] 셀에 커서를 두고 =c를 입력하면 사용자 정의 함수 CalStr이 목록에 표시됩니다.

수식을 작성합니다.

적용할 다른 셀에 채우기해서 결과를 구합니다.

완성된 파일을 저장합니다.

 

파일 형식을 Excel 매크로 사용 통합 문서(*.xlsm)로 지정하지 않으면 경고 메시지가 표시됩니다.

사용자 정의 함수로 원하는 결과를 이미 구했기 때문에 굳이 사용자 정의 함수를 다시 쓸 수 있도록 매크로가 포함된 파일 형식으로 저장할 필요가 없습니다.

 

그냥 Excel 통합 문서(*.xlsx)로 저장합니다.

매크로를 제외한 파일로 저장해도 사용자 정의 함수로 구한 결과는 오류 없이 그대로 표시됩니다.

수식을 수정하거나 산출식 셀의 내용을 바꾸면 사용자 정의 함수 CalStr을 이해할 수 없기 때문에 #NAME? 오류 메시지가 표시됩니다.

이 때는 앞에서 소개한 방법대로 사용자 정의 함수를 복사해서 VBE에 붙여넣기 하면 다시 제대로 동작합니다.

 

필요하신 분께 도움이 되길 바랍니다.