엑셀 텍스트가 포함된 문자열 수식을 계산하는 방법(문자열에서 산출식 추출 및 계산)

엑셀에서 연산 작업은 가장 기본적으로 하는 작업입니다. 연산 작업을 쉽게 하기 위해 엑셀을 사용한다고 말해도 틀린 말이 아닐 정도입니다.
셀 값이 숫자 형식이면 다양한 방식으로 연산이 가능합니다.

만약 셀 값이 텍스트로 된 문자열인데 이것을 수식으로 계산하려면 어떻게 해야 할까요?

예를 들어 셀 값에 아래와 같이 텍스트 문자열로 된 것을 계산해야 한다면 무척 난감해질 것입니다.

  • “참석인원 6명 * 식사비 12,000원 * 5일”
  • “주차요원 1명 * 시간당 10,000원 * 4시간 * 5일”
  • “입장료: (학생2명*5000원)+(일반3명*8000)”
  • “기타비용 : (2*3000-500)*25+(9/3)*2”
  • “홍길동:1000원+김유신5000원+이순신8000원”

텍스트 내용만 봐서는 도저히 계산이 안 될 거 같습니다.
하지만 자료를 취합하다 보면 이런 말도 안되는 경우의 자료도 생길 수 있습니다.

이번 글에서는 엑셀 텍스트가 포함된 문자열 수식을 계산하는 방법(문자열에서 산출식 추출 및 계산)에 대해 알아보겠습니다.

텍스트 문자열에서 산출식(계산식)을 추출하는 방법

앞의 예제와 같은 텍스트 문자열을 계산에 사용하기 위해서는 계산에 적합한 산출식을 먼저 추출해야 합니다.

“참석인원 6명 * 식사비 12,000원 * 5일“에서 “6*12000*5“로 산출(계산)식만 뽑아 내면 연산을 쉽게 할 수 있습니다.

※ 텍스트 문자열에서 계산에 필요한 산출식을 추출하기 위해서는 VBA의 도움을 받아야 합니다.
※ 엑셀 VBA 매크로에 대해 기초 설명은 아래 포스팅 글을 참고하세요.

1. 아래 VBA 프로그램은 입력 받은 문자열에서 계산 식에 사용할 숫자와 연산 기호만 추출하는 사용자 정의 함수 입니다.

  • 프로그램에서 주의 깊게 볼 부분은 패턴(pattern) 부분입니다.
  • regex.pattern = “[0-9\+\-\*\/\%\.\(\)]+”
  • 숫자 0~9. 연산 기호인 +, -, *, /, %. 그리고 소수점[.]과 연산 우선 순위를 위한 괄호[(,)]만 패턴으로 정의해 줍니다.
  • 패턴에 의해서 나머지 문자는 모두 제외됩니다.
Public Function RegExpExtract(strValue As String)
    Dim i As Integer
    Dim textFormula As String
 
    On Error GoTo ErrHandl    ‘에러가 발생하면 지정한 레이블로 이동합니다.
        
    RegExpExtract = “”
        
    Set regex = CreateObject(“VBScript.RegExp”)
    regex.pattern = “[0-9\+\-\*\/\%\.\(\)]+”      ‘문자열과 일치하는데 사용하는 패턴입니다.
    regex.Global = True   ‘True인 경우 문자열의 패턴과 일치하는 모든 항목을 찾습니다.
                          ‘False인 경우 첫 번째 일치하는 항목만 찾습니다.
    ‘regex.MultiLine = True    ‘True인 경우 패턴 일치는 줄바꿈 전체에서 발생합니다.
                               ‘False인 경우 패턴 일치는 한 줄에서만 발생합니다.
    ‘regex.IgnoreCase = False  ‘True인 경우 대소문자 구분을 무시합니다.
                               ‘False인 경우 대소문자를 구분합니다.
        
    Set matches = regex.Execute(strValue)
  
    If 0 < matches.Count Then
        textFormula = “”
      
        For i = 0 To matches.Count – 1
            textFormula = textFormula & matches.Item(i)
        Next i
      
        RegExpExtract = textFormula
    End If
  
    Exit Function   ‘Function을 종료합니다.
                    ‘아래와 같이 레이블이 있으면 먼저 종료를 해 줍니다.
        
ErrHandl:
    RegExpExtract = CVErr(xlErrValue)
    
End Function

2. VBA에서 만든 사용자 함수를 엑셀에서 일반 함수처럼 사용하면 됩니다.

  • 함수 이름 RegExpExtract에 A2 셀 값을 입력하면 산출식만 반환해 줍니다.
엑셀 텍스트가 포함된 문자열 수식을 계산하는 방법(문자열에서 산출식 추출 및 계산) 1

텍스트가 포함된 문자열 수식을 계산하는 방법

1. 이번에 설명할 VBA 프로그램은 텍스트로 된 산출식을 바로 계산하는 사용자 정의 함수입니다.

  • 산출식을 추출하는 과정이 함수에 포함되어져 있습니다.
  • 프로그램에서 눈 여겨 볼 부분은 산출식을 추출하는 For문과 이렇게 추출된 산출식을 실제 계산하는 Evaluate 함수 부분입니다.
  • EVALUATE 함수는 문자열 수식을 계산해 주는 VBA 내장 함수입니다.
Public Function StrEvaluate(strValue As String)
    Dim i As Integer
    Dim strEvalText, strChar As String
 
    On Error Resume Next    ‘에러가 발생한 행을 무시하고 다음 행을 실행합니다.
    
    strEvalText = “”
    
    ‘문자열에서 숫자와 연산기호만 추출하는 과정입니다.
    For i = 1 To Len(strValue) + 1
        strChar = Mid(strValue, i, 1)
 
        If (strChar Like “[0-9]” Or strChar Like “[+-*/%.()]”) Then
            strEvalText = strEvalText & strChar
        End If
    Next i
   
    If strEvalText = “” Then
        StrEvaluate = 0
    Else
        StrEvaluate = Evaluate(strEvalText)   ‘텍스트로 되어 있는 수식을 계산해 주는 VBA 함수 입니다
    End If
    
End Function

2. VBA에서 만든 사용자 함수를 엑셀에서 일반 함수처럼 사용하면 됩니다.

  • 함수 이름 StrEvaluate에 A2 셀 값을 입력하면 자동으로 산출식을 추출하고 계산 결과를 반환합니다.
엑셀 텍스트가 포함된 문자열 수식을 계산하는 방법(문자열에서 산출식 추출 및 계산) 2

3. 앞 단락에서 만든 텍스트 문자열에서 계산식만 추출하는 사용자 함수를 만들어 놓은 상태라면 해당 함수를 활용해서 프로그램을 간단하게 만들 수도 있습니다.

Public Function StrEvaluate(strValue As String)
    Dim i As Integer
    Dim strEvalText, strChar As String
 
    On Error Resume Next    ‘에러가 발생한 행을 무시하고 다음 행을 실행합니다.
    
    strEvalText = “”
    
    ‘앞에서 만든 RegExpExtract  함수를 사용하여 수식 가져오기
    strEvalText = RegExpExtract(strValue)   
    
    If strEvalText = “” Then
        StrEvaluate = 0
    Else
        StrEvaluate = Evaluate(strEvalText)   ‘텍스트로 되어 있는 수식을 계산해 주는 VBA 함수 입니다
    End If
    
End Function

▼ 실제 VBA에서 작성한 RegExpExtract 함수와 StrEvaluate 함수 입니다.

엑셀 텍스트가 포함된 문자열 수식을 계산하는 방법(문자열에서 산출식 추출 및 계산) 3

EVALUATE 함수를 사용하여 문자로 된 수식을 계산하는 방법

앞 단락에서는 텍스트 문자열에서 수식 즉, 산출식 또는 계산식을 먼저 추출하고 그렇게 추출된 수식을 계산하는 과정을 설명하였습니다.

만약 이미 문자로 입력된 수식이 있는 경우 좀 더 간단하게 계산하는 방법이 있습니다. VBA 함수인 EVALUATE를 사용하면 쉽게 문자열 수식을 계산할 수 있습니다.

1. 아래 엑셀 화면을 보면 B열에 문자로 입력된 산출식(수식)이 있습니다. 이를 경우 굳이 VBA 프로그램을 만들지 않고 바로 계산 결과를 만들 수 있습니다.

2. 엑셀의 수식 탭을 선택하고 이름 관리자 또는 이름 정의를 선택합니다.

  • EVALUATE 함수는 엑셀 내장 함수가 아니라 VBA 함수이기 때문에 셀에서 바로 사용할 수 없습니다.
  • B2의 산술식이 C2에 계산 결과로 들어오므로 C2를 선택한 상태에서 이름 관리자를 선택해 줍니다.
EVALUATE 함수를 사용하여 문자로 된 수식을 계산하는 방법 1

3. 이름 관리자 화면에서 새로 만들기를 클릭해 줍니다.

EVALUATE 함수를 사용하여 문자로 된 수식을 계산하는 방법 2

4. 새 이름 만들기 화면에서 이름에 적당한 이름을 지정합니다. 엑셀 셀에서 사용할 이름입니다.

5. 참조 대상에 기존 입력된 내용은 모두 지우고, =EVALUATE(Sheet16!B2)를 입력하고 확인을 클릭합니다.

  • Sheet16은 현재 작업중인 워크시트 이름입니다.
  • B2 셀의 수식을 EVALUATE로 계산하겠다는 의미입니다.
  • 이때, 셀은 $가 빠진 상대 참조로 해야 합니다. 기본적으로 셀을 선택하면 Sheet16!$B$2와 같이 절대 참조로 지정되는데 F4 키를 3번 연속으로 눌러 상대 참조로 만들거나 직접 $ 기호를 지워서 상대 참조로 만들어 주면 됩니다.
  • 상대 참조, 절대 참조에 대한 설명은 엑셀에서 상대 참조와 절대 참조의 올바른 구분과 사용법 포스팅 글을 참고하세요.
EVALUATE 함수를 사용하여 문자로 된 수식을 계산하는 방법 3

6. 방금 만든 이름을 선택하고 닫기를 클릭해서 이름 관리자를 닫습니다.

EVALUATE 함수를 사용하여 문자로 된 수식을 계산하는 방법 4

7. C2 셀에 =Evaluate를 입력하고 엔터를 눌러 줍니다. 그럼 자동으로 B2 열의 문자로 된 산출식을 계산해서 결과를 반환해 줍니다.

EVALUATE 함수를 사용하여 문자로 된 수식을 계산하는 방법 5

8. C열의 나머지 행들도 빠른 채우기로 모두 =Evaluate를 지정하면 자동으로 계산 된 결과를 볼 수 있습니다.

EVALUATE 함수를 사용하여 문자로 된 수식을 계산하는 방법 6

마무리

VBA 매크로를 사용하거나 VBA 함수(EVALUATE)를 사용하기 위해서는 엑셀을 저장할 때 “Excel 매크로 사용 통합 문서 (*.xlsm)“으로 저장해야 합니다.

이 글에서 설명한 내용을 사용하더라도 모든 텍스트 문자열을 산술식으로 변환해서 계산할 수는 없습니다.
연산을 위해서는 추출되는 산출식이 연산에 적합한 형태여야 합니다.
연산 기호가 2번 이상 반복해서 나오거나 소수점 중복으로 표시되거나 괄호의 짝이 맞지 않는 경우는 당연히 계산이 되지 않습니다.





이 글이 도움이 되었기를 바랍니다. ^-^


답글 남기기