엑셀 중국어 병음 변환, 함수로 쉽게 해결하는 방법

매크로 없이 함수만 사용하여 중국어 발음을 손쉽게 구하는 방법과 공식의 동작원리를 알아봅니다.

홈페이지 » 엑셀 중국어 병음 변환, 함수로 쉽게 해결하는 방법

엑셀 중국어 병음 변환, 함수 공식 하나로 손쉽게 구하는 방법!

엑셀 중국어 병음 목차 바로가기
중국어 병음 변환 공식
=TEXTJOIN(" ",TRUE,IFERROR(VLOOKUP(MID(셀,ROW(INDIRECT("A1:A"&LEN(셀))),1),참조범위,열번호,0),""))
중국어 병음 변환 공식에 사용된 인수
엑셀 중국어 병음 변환 공식 인수
중국어 병음 변환 공식에 사용된 인수입니다.
인수설명
중국어 문장 또는 중국어 문장이 입력된 셀 입니다.
참조범위중국어와 병음이 입력된 표 범위 입니다. 중국어는 반드시 맨 좌측열에 있어야 합니다.
열번호참조범위에서 병음이 입력된 열의 열번호입니다.
영상 강의

예제파일 다운로드

강의에 사용된 기초 함수 목록
세로로 입력된 자료에서 원하는 값을 찾아 옆에 위치한 다른 값을 참조합니다.
문자열의 특정 위치부터 원하는 문자수만큼의 텍스트를 반환합니다.

중국어 병음 변환을 손쉽게 할 수 있는 이유

외국어 번역을 하려면 일반적으로 구글 번역기와 같은 외부 프로그램을 사용해야만 합니다. 따라서 인터넷이 접속된 환경, 엑셀 외적인 다른 기술을 필요로 했는데요.

반면에 중국어 병음 변환과 같은 '발음'을 구하는 기능은 각 단어의 발음기호만 알 수 있다면 외부 프로그램이나 인터넷 접속 없이 엑셀 함수만으로도 쉽게 처리할 수가 있습니다.

중국어의 경우 약 1,500자만 알면 일상생활에서 사용되는 문장의 약 95%를 번역할 수 있다고 합니다.

중국어 병음 변환 1500자

따라서 본 강의 예제파일에는 일상생활에서 주로 사용되는 중국어 한자 1,500자와(첫번째 시트), 전체 중국어 한자인 81,390자의 병음(두번째 시트)을 각각의 시트에 나누어 준비해드렸으니 필요에 맞춰 사용합니다.

영상강의에서는 다루지 못했지만, 아래 적어드린 PinYin 사용자 지정함수를 사용하면 중국어 병음 변환을 좀 더 손쉽게 할 수 있습니다.

중국어 병음 변환 공식의 동작 원리

  1. LEN 함수로 셀에 입력된 중국어 문장의 단어 개수를 셉니다.
    =LEN("你吃好了吗")
    =5  '5글자이므로 5를 반환합니다.
  2. INDIRECT 함수로 "A1"셀부터 "A글자수"까지 범위를 반환합니다.
    =INDIRECT("A1:A"&LEN("你吃好了吗"))
    =INDIRECT("A1:A"&5)
    =INDIRECT("A1:A5")
  3. ROW 함수로 1부터 글자수까지의 행번호(숫자)를 배열로 반환합니다.
    =ROW(INDIRECT("A1:A"&LEN("你吃好了吗")))
    =ROW(INDIRECT("A1:A5"))
    ={1,2,3,4,5}  '범위의 행번호를 배열로 반환합니다.
  4. MID 함수로 각 중국어 문장의 각 단어를 한글자씩 변환합니다.
    =MID("你吃好了吗",ROW(INDIRECT("A1:A"&LEN("你吃好了吗"))),1)
    =MID("你吃好了吗",{1,2,3,4,5},1)
    ={"你","吃","好","了","吗"}  '중국어 문장의 문자를 하나씩 반환합니다.
  5. VLOOKUP 함수로 각 단어의 병음을 찾습니다.
    =VLOOKUP(MID(셀,ROW(INDIRECT("A1:A"&LEN(셀))),1),참조범위,열번호,0)
    =VLOOKUP({"你","吃","好","了","吗"},A:B,2,0)
    ={"nǐ","chī","hǎo","le","ma"}  '각 한자의 병음을 구합니다.
  6. IFERROR 함수를 사용하여 VLOOKUP 함수에서 발생가능한 오류(#N/A)를 빈칸으로 변환합니다.
    =IFERROR(VLOOKUP(MID(셀,ROW(INDIRECT("A1:A"&LEN(셀))),1),참조범위,열번호,0),"")
    =IFERROR({"nǐ","chī","hǎo","le","ma"},"")
    ={"nǐ","chī","hǎo","le","ma"}
  7. TEXTJOIN 함수를 사용하여 각각의 병음을 다시 한 문장으로 병합합니다.
    =TEXTJOIN(" ",TRUE,IFERROR(VLOOKUP(MID(셀,ROW(INDIRECT("A1:A"&LEN(셀))),1),참조범위,열번호,0),""))
    =TEXTJOIN(" ",TRUE,{"nǐ","chī","hǎo","le","ma"})
    ="nǐ chī hǎo le ma"  '병음을 한 문장으로 다시 병합합니다.

엑셀 매크로를 사용하여 나만의 함수 만들기 :: PinYin 함수

아래 명령문을 복사하여 VBA 편집기에 붙여넣기 하면 PinYin 사용자 지정함수를 사용할 수 있습니다.

  1. 예제파일을 실행한 뒤, 개발도구 - Visual Basic 버튼을 클릭하거나 Alt + F11를 눌러 매크로 편집기로 이동합니다. 화면에 개발도구가 안보이실 경우, 아래 개발도구 활성화 방법 포스트를 참고하세요.

    엑셀 개발도구 활성화
    개발도구 - Visual Basic 버튼을 클릭하여 매크로 편집기를 실행합니다.
  2. 삽입 - 모듈을 클릭하여 새로운 모듈을 추가합니다.

    엑셀 모듈 추가
    삽입 - 모듈을 클릭하여 새로운 모듈을 추가합니다.
  3. 아래 명령문을 복사하여 모듈 안에 붙여넣기 합니다.
    Function PinYin(ParamArray Range() As Variant)
     
    '###############################################################
    '오빠두엑셀 VBA 사용자지정함수 (https://www.oppadu.com)
    '▶ PinYin 함수
    '▶ 중국어 문장의 병음(발음)을 반환합니다. 중국어와 병음이 입력된 표를 참조하여 동작합니다.
    '▶ 인수 설명
    '_____________Range     : 중국어 또는 중국어가 입력된 셀, 범위입니다.
    '###############################################################
     
    Dim vRngs As Variant
    Dim vRng As Variant
    Dim sStr As String
    Dim sChar As String: Dim sPinyin As String
    Dim sResult As String
    Dim WS As Worksheet
     
    On Error GoTo EH:
     
    '시트이름을 넣어주세요.
    Set WS = ThisWorkbook.Worksheets("1500자")  '<-- 시트 이름을 변경합니다.
     
    For Each vRngs In Range
        For Each vRng In vRngs
            sStr = vRng.Value
            For i = 1 To Len(sStr)
                sChar = Mid(sStr, i, 1)
                sPinyin = Application.WorksheetFunction.VLookup(sChar, WS.Range("A:B"), 2, False)
                sResult = sResult & sPinyin & " "
            Next
        Next
    Next
     
    PinYin = sResult
     
    Exit Function
     
    EH:
                sPinyin = sChar
                Resume Next
    End Function
  4. 시트로 돌아오면 PinYin 함수를 사용할 수 있습니다.

    엑셀 중국어 병음 변환 PinYin 함수
    PinYin 함수를 사용하여 중국어 병음을 변환합니다.

 

4.5 4 votes
게시글평점
현재 페이지 댓글알림 신청
알림 설정
guest
5 Comments
Inline Feedbacks
View all comments
hm****
hm****
2020년 7월 23일 11:45 오후
게시글평점 :
     

언제나 좋은 강의 감사드립니다. 항상 좋은 정보를 많이 얻고 있고, 강의해주신 내용중 필요한 부분이 많아 업무에 많은 도움이 되고 있습니다. 이번강의도 그렇지만, 이전 강의에서도 그림의 떡 같은 함수들이 있습니다. 대부분… 더보기 »

TPE 델포르트
TPE 델포르트
2020년 7월 24일 11:46 오전
게시글평점 :
     

좋은 강의 감사합니다.

thwlsdldml
thwlsdldml
2020년 7월 28일 10:19 오전
게시글평점 :
     

감사합니다. 덕분에 많이 배웁니다

스마일
스마일
2020년 8월 23일 10:32 오전
게시글평점 :
     

엑셀에서 안되는 게 없네요. 잘만 배워두면 활용도가 많을 것 같아요.
정말 감사합니다.

미성
미성
2020년 10월 20일 5:36 오후
게시글평점 :
     

대단하다는 말밖에 안 나오네요
언제면 다 배우게 될지 욕심만 앞서게 되네요

5
0
여러분의 생각을 댓글로 남겨주세요.x