用公式將漢字轉(zhuǎn)換為拼音的第一個(gè)字母

 有時(shí)需要在Excel中將大量姓名或名稱轉(zhuǎn)換為拼音首字母的簡寫形式，例如將“周伯通”轉(zhuǎn)換為“ZBT”。用定義名稱和公式的方法可以輕松實(shí)現(xiàn)轉(zhuǎn)換，在Windows XP中，以Excel 2003為例方法如下：

    1.定義名稱：

    單擊菜單“插入→名稱→定義”，在彈出的對話框中“在當(dāng)前工作簿中的名稱”下的文本框中輸入一個(gè)名稱，如“py”，在“引用位置”下輸入下列代碼后確定：

    ={"","吖","八","嚓","咑","鵽","發(fā)","猤","鉿","夻","咔","垃","嘸","旀",

"噢","妑","七","囕","仨","他","屲","夕","丫","帀";"","A","B","C","D","E",

"F","G","H","J","K","L","M","N","O","P","Q","R","S","T","W","X","Y","Z"}

 2.假如名單在A2:A100區(qū)域中，在B2單元格中輸入公式：

    =LOOKUP(LEFT(A2,1),py)&LOOKUP(MID(A2,2,1),py)&LOOKUP(MID(A2,3,1),py)&LOOKUP(MID(A2,4,1),py)

 說明：

    1.定義的名稱為一個(gè)數(shù)組，以分號(hào)為界分為兩行，第一行為漢字，第二行為與第一行漢字對應(yīng)的拼音第一個(gè)大寫字母。如果是Windows 7或Vista，則需要將定義的名稱改為：

    ={"","吖","八","攃","咑","鵽","發(fā)","旮","哈","丌","咔","垃","媽","乸",

"噢","帊","七","冄","仨","他","屲","夕","丫","帀";"","A","B","C","D","E",

"F","G","H","J","K","L","M","N","O","P","Q","R","S","T","W","X","Y","Z"}

    2.公式用LOOKUP函數(shù)將每個(gè)漢字轉(zhuǎn)換成拼音的第一個(gè)大寫字母。其原理是：Excel中漢字（文本）之間也可以進(jìn)行比較；漢字在Excel中的排列順序與拼音順序相同，排在前面的“值”小于排在其后的。上述定義名稱的數(shù)組中，每個(gè)漢字都是所有拼音首字母相同的漢字中的第一個(gè)，例如“八”在Excel中排在其他所有拼音首字母為“B”的前面，因而其“值”最小。因此，LOOKUP函數(shù)在查找某個(gè)不在數(shù)組中的漢字，如“伯”字時(shí)，會(huì)在數(shù)組的第一行中查找小于它的最大值“八”，然后返回最后一行同列的值“B”。

    另外，由于在Windows XP與Windows 7、Vista中Excel對漢字的排列順序有所不同，故定義的名稱內(nèi)容也不同。如果在Windows 7、Vista中使用XP中定義的數(shù)組，LOOKUP函數(shù)第二個(gè)參數(shù)中的值未按升序排列，可能無法返回正確的結(jié)果。

    3.對于多音字只能返回一種結(jié)果。

    4.如果有多于四個(gè)的漢字，繼續(xù)在公式后面添加即可。如有第五個(gè)漢字，則加上“&LOOKUP(MID(A2,5,1),py)”。