엑셀 매크로로 자동번역기, 전문용어/약자변환기 만들기

지난번 글에서는 Vlookup을 통해서 전문용어 한글번역 사전을 만든 다음, 이것을 한글로 번역할 수 있게 하였다. 

2024.11.16 - [엑셀] - Vlookup을 사용해서 약자/약속어 변환하기

Vlookup을 사용해서 약자/약속어 변환하기

그런데 이 방법에는 한계점이 존재하는데, 단어 단위의 변환은 문제가 없으나, 단어가 2개이상 조립되는 경우에는

1. 각각의 단어를 각각 1개씩의 셀에 배치를 하고,

2. 그것을 Vlookup을 이용해서 변환을 한 다음

3.  Concatenate  함수를 통해 조립을 해줘야한다는 것이다.

 

사실 1,2,3의 과정은 귀찮긴 해도 엑셀 서식을 만들어 놓으면 해결할 수 있는 문제이다. 그러나 더 큰문제는 

0. 애초에 "두개 이상으로 조립된 문장"을 어떻게 단어 단위로 나눌것인가? 하는 문제이다. 실제로 내가 당시 해당업무를 맡고 있을 때는 본업이 바빠서 여기에 대한 제대로 된 해답을 찾지 못하고 2년정도를 허비했다. 

 

그러다가 불완전한 방법이나마 가능하게 되어 여러가지 해결법을 찾게 되었는데, 오늘은 그 중 가장 완성형에 가까운 엑셀 매크로를 사용한 전문용어 자동 변환기에 대해 소개하고자 한다. 

 

엑셀 매크로는 Alt + F11을 통해서 실행할 수 있다. 여기서 새 모듈을 추가한다. 

새 모듈에다가 첨부된 코드를 삽입하고 저장후 닫기를 누른다.

Public Function TranslateSentence(inputSentence As String, translationRange As Range) As String
    Dim words() As String
    Dim translatedWords() As String
    Dim i As Long
    Dim translatedWord As Variant
    
    words = Split(inputSentence, " ")
    ReDim translatedWords(LBound(words) To UBound(words))
    
    For i = LBound(words) To UBound(words)
        On Error Resume Next
        translatedWord = Application.WorksheetFunction.VLookup( _
            words(i), _
            translationRange, _
            2, _
            False)
        
        If Err.Number <> 0 Or IsEmpty(translatedWord) Then
            translatedWords(i) = words(i)
        Else
            translatedWords(i) = translatedWord
        End If
        
        On Error GoTo 0
        Err.Clear
    Next i
    
    TranslateSentence = Join(translatedWords, " ")
End Function

원활한 실행을 위해서 파일의 저장형식은 Xlsm (Excel 매크로 사용 통합 문서)로 하도록 하자.

 

다음으로 변환을 위한 사전을 제작하도록 하자. 내가 작성한 사전은 다음과 같다.

다음으로 TranslateSentence함수를 사용하여 다음과 같이 번역을 하도록 하자.

 

=TranslateSentence(번역대상 셀, 참조할 사전의 범위)

 

아래와 같이 번역이 완성된다.

하나씩 살펴보도록 하자.

 

1-3번의 경우는 사전에 등록된 단어를 이용하여 잘 번역이 된 것을 알 수 있다. 1번의 경우 함수를 뜯어보면, 번역대상셀은 E2이고, 참조할 사전의 범위는 아까전에 작성했던  A열과 B열로 했다. 여기서는 50행까지로 한정하였다. 사전에 등록된 단어들이 잘 번역된 것을 볼 수 있다.

 

다음으로 4번을 보면 prostectomy는 번역 사전에 기재가 안되어 있기 때문에 원문 그대로 기재가 되었음을 알 수 있다.

 

마지막으로  단어가 길어져서 문장처럼 제시되더라도 잘 번역이 되는 것을 5번에서 확인할 수 있다. 5번에서는 총 3개의 번역단어가 번역되었다. 이러한 시스템을 왜 그당시에는 구축하지 못했는지 아쉬움이 크지만 사회초년생을 지나 중년생이 된 지금에서라도 그럴싸하게 만들었음에 자기만족을 느끼며 필요로 하는 분들에게 공유하고 싶다.