Question

请教在EXCEL中把人名的拼音首字母提取的方法

想把下列人名的拼音首字母提取出来，然后在另一页的A栏至Z栏排列出符合条件的人名，请教如何操作？

左娜
张瑶
唐悦丽
刘月霞
李婉仪
林翠平
卢高波
李久全
李晓
张熙
何志荣
梁青锋
夏文武
陆振球

Answer 1

方法：
1、利用在线汉字转拼音应用将需要转换的汉字生成拼音；
2、将生成的拼音粘贴回excel，利用分列功能分成3列，每列都选文本格式；
3、按第三列（该列两字人名为空）排序，将空格排到下方；
4、在其后第一列第一行、第二行手动输入第一个、第二个人名首字母，选中两单元格，右键拖动拖动手柄至最下，松开鼠标，选“快速填充”；
5、此时，三字人名首字母提取完成，二字人名为空，在第一个二字名后手动输入首字母，下面首字母自动生成；
6、选中首字母列，开始菜单——“条件格式”——“突出显示单元格规则”——重复值，找出重复值，手动进行区分。

Answer 2

楼上 用函数的方法， 很强大 自愧不如

我用一个笨办法 ，楼主有兴趣的话参考一下吧：

“选中-—数据/排序—字母排序 ”（如图）

再手动 选添一下人名&字母吧            方法很笨 但没有函数基础的可以参考一下！

Answer 3

LOOKUP(CODE(LEFT(B1,1)),45217+{0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264},{"A","B","C","D","E","F","G","H","J","K","L","M","N","O","P","Q","R","S","T","W","X","Y","Z"})&LOOKUP(CODE(MID(B1,2,1)),45217+{0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264},{"A","B","C","D","E","F","G","H","J","K","L","M","N","O","P","Q","R","S","T","W","X","Y","Z"})&LOOKUP(CODE(MID(B1,3,1)),45217+{0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264},{"A","B","C","D","E","F","G","H","J","K","L","M","N","O","P","Q","R","S","T","W","X","Y","Z"})
公式太长，不写if了，名字为2个字的删除最后一个&lookup()

Answer 4

好像公式里就有这样的函数