设为首页收藏本站

嘻皮客娱乐学习网

 找回密码
 中文注册

QQ登录

只需一步,快速开始

扫一扫,访问微社区

搜索
开启左侧

用公式将汉字转换为拼音的第一个字母

[复制链接]
发表于 2013-5-12 20:35:00 | 显示全部楼层 |阅读模式
有时需要在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"}
2011010315530454.jpg
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)
然后拖动填充柄向下填充公式即可。
2011010316015668.jpg
说明:
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)”。
回复

使用道具 举报

精彩推荐

小黑屋|手机版|嘻皮客网 ( 京ICP备10218169号|京公网安备11010802013797  

GMT+8, 2019-3-23 00:18 , Processed in 0.304906 second(s), 37 queries , Gzip On.

Powered by Discuz! X3.3

© 2001-2017 Comsenz Inc.

快速回复 返回顶部 返回列表