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

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

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

작성자 :
오빠두엑셀
최종 수정일 : 2021. 11. 10. 03:23
URL 복사
메모 남기기 : (8)

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

엑셀 중국어 병음 목차 바로가기
중국어 병음 변환 공식
=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.2 6 투표
게시글평점
8 댓글
Inline Feedbacks
모든 댓글 보기
8
0
여러분의 생각을 댓글로 남겨주세요.x