请教在EXCEL中把人名的拼音首字母提取的方法
发布网友
发布时间：2022-04-26 22:55
共4个回答
热心网友
时间：2022-06-19 13:09
方法：
1、利用在线汉字转拼音应用将需要转换的汉字生成拼音；
2、将生成的拼音粘贴回excel，利用分列功能分成3列，每列都选文本格式；
3、按第三列（该列两字人名为空）排序，将空格排到下方；
4、在其后第一列第一行、第二行手动输入第一个、第二个人名首字母，选中两单元格，右键拖动拖动手柄至最下，松开鼠标，选“快速填充”；
5、此时，三字人名首字母提取完成，二字人名为空，在第一个二字名后手动输入首字母，下面首字母自动生成；
6、选中首字母列，开始菜单——“条件格式”——“突出显示单元格规则”——重复值，找出重复值，手动进行区分。
热心网友
时间：2022-06-19 13:09
楼上 用函数的方法， 很强大 自愧不如
我用一个笨办法 ，楼主有兴趣的话参考一下吧：
“选中-—数据/排序—字母排序 ”（如图）
再手动 选添一下人名&字母吧 方法很笨 但没有函数基础的可以参考一下！
热心网友
时间：2022-06-19 13:10
LOOKUP(CODE(LEFT(B1,1)),45217+{0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,56,6170,6229,7001,7481,7763,8472,92},{"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,56,6170,6229,7001,7481,7763,8472,92},{"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,56,6170,6229,7001,7481,7763,8472,92},{"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()
热心网友
时间：2022-06-19 13:10
好像公式里就有这样的函数
