Substitute 함수를 사용한 전문용어/약자 변환기

이전글에서는 매크로를 사용한 약자/전문용어 변환기 작성에 대해서 설명했다.

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

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

그런데, 액셀 매크로는 생각보다 진입장벽이 있어서 받아들이는 사람에 따라서는 사용하기가 어렵다는 단점이 있다. 오늘은 substitute함수를 사용해서 간단하게 약자/전문용어 변환기를 만드는 방법을 설명해 보도록 하겠다. 

함수의 구조

SUBSTITUTE 함수는 텍스트 내의 특정 문자 또는 문자열을 다른 문자열로 바꾸는 Excel 함수이다.

=SUBSTITUTE(text, old_text, new_text, [instance_num])

기본 예시

=SUBSTITUTE("IT Manager", "IT", "Information Technology") 결과: "Information Technology Manager"

 

그러면 substitute함수를 중첩시키게 되면 약자는 모두 해결가능한 것이 아닌가 하는생각을 하게 된다. 그러나 안타깝게도 엑셀함수의 중첩은 그 갯수에 한계가 있는 경우가 많다. substitute의 경우에도 7번 중첩이 한계이다. 다만 substitute 함수의 경우 장점은 여러가지 단어나 약자가 복합적으로 존재할 경우, 혹은 문장으로 제시된 경우에 일부의 약자만 등록되어 있으면 해당부분만 바꿀 수 있다는 점이다. 앞선 글에서 vlookup을 통한 약자변환시스템을 소개했지만, vlookup으로는 해결할 수 없는 기본적인 문제를 substitute함수는 해결할 수 있다.

	Vlookup 함수	Substitute 함수
장점	사전을 무제한으로 만들 수 있음.	2개 이상 연결된 복합단어, 문장도 가능 일부 내용만 변경가능
단점	단어가 2개이상 연결될 경우 해결불가   부분적인 단어만 변경할 수 없음.	한번에 중첩할 수 있는 제한은 7번. 즉, 한번에 약자를 7개까지 등록가능

일전에 Vlookup을 통한 약자/전문용어 변환에 대한 설명을 기술한 바 있으니 아래 글을 읽고 오면 더 이해가 쉬울 것이다.

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

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

Subsitutue 함수 중첩으로 갯수제한 해결하기

그렇다면 substitute함수의 장점만 취하고 단점을 해결할 수는 없을까? 정답은 가능하다. 조금 번거롭긴하지만 여러 열에 나눠서 substitute 를 중첩시키면 된다. 예를들어서 아래와 같은 21개의 약자를 full-term으로 변경하고 싶을때의 상황을 가정하겠다.

그림 1. 비즈니스 용어의 약자와 full-term

substitute함수의 중첩한계는 7번이기 때문에 한번에 처리할 수는 없다. 그렇다면 3번에 나눠서 중첩시키면 21개를 처리할 수 있다. 위 그림에서 노란색으로 표기된 3개의 약자를 복합어로 제시하고 이를 fu;l-term으로 변환하는 과정을 예시로 들어보겠다.

1단계. B1셀에 substitute함수를 7번 중첩시켜서 그림 1의 1번~7번까지의 약자를 등록한다. 결과값은 보시다시피 그림 1의 1번에 해당하는 CEO만 변환이 되었고 (부분 변환) 나머지는 그대로 약자로 남아있다.

2단계. B1을 대상으로 하여 다시 substitute함수를 7번 중첩시켜서 그림 1의 8번~14번까지 약자를 등록한다. 결과값을 보면 밑줄친 IT가 추가로 full-term으로 변경되었으며, VP는 여전히 약자로 남아있는 것을 볼 수 있다.

 

3단계. B2셀을 대상으로 하여 subsitutue함수를 7번 중첩시켜 15번-21번까지의 약자를 등록했다. 결과값을 보면 VP가 full-term인 Vice President로 변환되었음을 알 수 있다.

보통 전문용어라 하더라도 일상적으로 사용하는 단어가 100개를 넘어가기는 힘들기 때문에 매크로를 사용하지 않아도 substitute함수를 이용하여 효과적으로 약자나 전문용어를 변환할 수 있음을 확인하였다.  함수 수식이 복잡하기 때문에 3가지 수식을 아래에 코드로 제시하니 많은 도움이 되었으면 하는 바람이다.

 

// B1에서 첫 번째 그룹(1-7) 변환
=SUBSTITUTE(
    SUBSTITUTE(
        SUBSTITUTE(
            SUBSTITUTE(
                SUBSTITUTE(
                    SUBSTITUTE(
                        SUBSTITUTE(A1,
                            "CEO", "Chief Executive Officer"),
                        "CFO", "Chief Financial Officer"),
                    "COO", "Chief Operating Officer"),
                "CTO", "Chief Technology Officer"),
            "CMO", "Chief Marketing Officer"),
        "CIO", "Chief Information Officer"),
    "HR", "Human Resources")

// B2에서 두 번째 그룹(8-14) 변환
=SUBSTITUTE(
    SUBSTITUTE(
        SUBSTITUTE(
            SUBSTITUTE(
                SUBSTITUTE(
                    SUBSTITUTE(
                        SUBSTITUTE(B1,
                            "IT", "Information Technology"),
                        "PR", "Public Relations"),
                    "ROI", "Return On Investment"),
                "KPI", "Key Performance Indicator"),
            "B2B", "Business to Business"),
        "B2C", "Business to Consumer"),
    "RFP", "Request For Proposal")

// B3에서 마지막 그룹(15-21) 변환
=SUBSTITUTE(
    SUBSTITUTE(
        SUBSTITUTE(
            SUBSTITUTE(
                SUBSTITUTE(
                    SUBSTITUTE(
                        SUBSTITUTE(B2,
                            "ASAP", "As Soon As Possible"),
                        "PM", "Project Manager"),
                    "QA", "Quality Assurance"),
                "R&D", "Research and Development"),
            "VP", "Vice President"),
        "FAQ", "Frequently Asked Questions"),
    "CSR", "Corporate Social Responsibility")