엑셀 중국어 병음 변환 공식

중국어 병음 변환 공식

=TEXTJOIN(" ",TRUE,IFERROR(VLOOKUP(MID(셀,ROW(INDIRECT("A1:A"&LEN(셀))),1),참조범위,열번호,0),""))

중국어 병음 변환 공식에 사용된 인수

중국어 병음 변환을 함수만으로 처리할 수 있는 이유

외국어 번역은 일반적으로 구글 번역기와 같은 외부 프로그램을 활용해야 합니다. 인터넷 연결 환경과 엑셀 외의 별도 도구가 필요한 경우가 대부분입니다.

반면 중국어 병음 변환처럼 '발음'을 구하는 작업은 각 단어의 발음기호만 확보되어 있다면 외부 프로그램이나 인터넷 연결 없이 엑셀 함수만으로도 충분히 처리할 수 있습니다.

중국어는 약 1,500자만 익히면 일상에서 사용되는 문장의 약 95%를 표현할 수 있다고 알려져 있습니다.

이 강의의 예제파일에는 일상에서 자주 사용되는 중국어 한자 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키를 눌러 매크로 편집기로 이동합니다. 화면에 개발도구 탭이 보이지 않을 경우, 아래 개발도구 활성화 방법 포스트를 참고합니다.
   
   
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 함수를 바로 사용할 수 있습니다.