EXCEL 单元格中文提取拼音首字母的实现方法

在某些领域的工作中，经常会碰到中文名称需要提取每个字拼音首字母的情形，以便于用来进行检索。比如图书名称索引（当然真正的图书馆大量的图书并不会用EXCEL来实现），如下图右侧的字母缩写

图1

事实上，对于汉字转拼音首字母，网上有一套现成的方案。直接引用就可以实现。公式如下：

=LOOKUP(MID(A1,ROW($1:$6),1), {"","";"吖","A";"八","B";"攃","C";"咑","D";"妸","E";"发","F";"旮","G";"哈","H";"丌","J";"咔","K";"垃","L";"妈","M";"乸","N";"噢","O";"帊","P";"七","Q";"冄","R";"仨","S";"他","T";"屲","W";"夕","X";"丫","Y";"帀","Z"})

这其中 ROW($1:$6)的作用是形成一个数组，即｛1,2,3,4,5,6}。当然了，这个公式仅限于取6个汉字的拼音首字母。正常使用时，为了保证可扩展性，可以使用ROW(INDIRECT("$1:$"&LEN(A1)))这个公式来生成动态的数组。它会自动判断单元格有多少字符。如果用可扩展的公式的话，应该是这样的公式：
=LOOKUP(MID(A1,ROW(INDIRECT("$1:$"&LEN(A1))),1), {"","";"吖","A";"八","B";"攃","C";"咑","D";"妸","E";"发","F";"旮","G";"哈","H";"丌","J";"咔","K";"垃","L";"妈","M";"乸","N";"噢","O";"帊","P";"七","Q";"冄","R";"仨","S";"他","T";"屲","W";"夕","X";"丫","Y";"帀","Z"})

这个公式得出的结果只能是第一个汉字的拼音首字母，虽然公式已经将每个汉字的拼音首字母提取了，但是并没有通过一个文本连接函数将其连接起来，形成一个完整的文本。文本连接函数有和个，CONCATENATE这个函数并不支持数组公式，所以无法使用。从2019开始，包括OFFICE365，内置的concat 、textjoin这两个函数是支持数组公式的。可以使用这两个公式中的任意一个。区别在于concat只能简单的将数组中的文本连接成一个文本，而textjoin是可以在需要连接的文本中间添加连接符。

• 使用concat方法实现

 =CONCAT((LOOKUP(MID(A1,ROW(INDIRECT("$1:$"&LEN(A1))),1), {"","";"吖","A";"八","B";"攃","C";"咑","D";"妸","E";"发","F";"旮","G";"哈","H";"丌","J";"咔","K";"垃","L";"妈","M";"乸","N";"噢","O";"帊","P";"七","Q";"冄","R";"仨","S";"他","T";"屲","W";"夕","X";"丫","Y";"帀","Z"})))

图2

• 使用TEXTJOIN方法实现

=TEXTJOIN("-",TRUE,(LOOKUP(MID(A1,ROW(INDIRECT("$1:$"&LEN(A1))),1), {"","";"吖","A";"八","B";"攃","C";"咑","D";"妸","E";"发","F";"旮","G";"哈","H";"丌","J";"咔","K";"垃","L";"妈","M";"乸","N";"噢","O";"帊","P";"七","Q";"冄","R";"仨","S";"他","T";"屲","W";"夕","X";"丫","Y";"帀","Z"})))

图3

      这样就实现了汉字取拼音首字母的功能。但这个方法仍然有一个缺点，那就是无法区分多音字。