用公式将汉字转换为拼音的第一个字母作者：Excel123 文章来源： 点击数：525 更新时间：2011-1-3 14:51:44有时需要在 Excel 中将大量姓名或名称转换为拼音首字母的简写形式，例如将“周伯通”转换为“ZBT”用定义名称和公式的方法可以轻松实现转换，在 Windows XP 中，以 Excel 2003 为例方法如下：1.定义名称：单击菜单“插入→名称→定义”，在弹出的对话框中“在当前工作簿中的名称”下的文本框中输入一个名称，如“py”，在“引用位置”下输入下列代码后确定：={"","吖","八","嚓","咑","鵽","发","猤","铪","夻","咔","垃","嘸","旀","噢","妑","七","囕","仨","他","屲","夕","丫","帀";"","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 区域中，在 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.定义的名称为一个数组，以分号为界分为两行，第一行为汉字，第二行为与第一行汉字对应的拼音第一个大写字母如果是 Windows 7 或 Vista，则需要将定义的名称改为：={"","吖","八","攃","咑","鵽","发","旮","哈","丌","咔","垃","妈","乸","噢","帊","七","冄","仨","他","屲","夕","丫","帀";"","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 函数将每个汉字转换成拼音的第一个大写字母其原理是：Excel 中汉字（文本）之间也可以进行比较；汉字在 Excel 中的排列顺序与拼音顺序相同，排在前面的“值”小于排在其后的上述定义名称的数组中，每个汉字都是所有拼音首字母相同的汉字中的第一个，例如“八”在 Excel 中排在其他所有拼音首字母为“B”的前面，因而其“值”最小因此，LOOKUP 函数在查找某个不在数组中的汉字，如“伯”字时，会在数组的第一行中查找小于它的最大值“八”，然后返回最后一行同列的值“B”。
另外，由于在 Windows XP 与 Windows 7、Vista 中 Excel 对汉字的排列顺序有所不同，故定义的名称内容也不同如果在 Windows 7、Vista 中使用 XP中定义的数组，LOOKUP 函数第二个参数中的值未按升序排列，可能无法返回正确的结果 3.对于多音字只能返回一种结果4.如果有多于四个的汉字，继续在公式后面添加即可如有第五个汉字，则加上“&LOOKUP(MID(A2,5,1),py)”。