爱魔域

excel表格功能非常强大，久久魔域怎么玩我们可以利用它进行各种各样的数据统计。今天教大家用三种方法提取姓名的所有拼音首字母。

工具/原料

联想天逸510

windows10

WPS11.1.0.14309

方法1：辅助列提取（初级用法）

1

首先建立姓名列，将需要获取所有首字母的姓名列入其中

2

建立辅助列“姓名第一个字”、“姓名第二个字”、“姓名第三个字”，然后使用mid公式拆分出姓名里的每个字分别放进对应的辅助列，公式下拉填充

3

再建立辅助列“姓名第一个字字母”、“姓名第二个字字母”、“姓名第三个字字母”，这里要用到一个特殊的公式：=VLOOKUP(B2,

{"","";"吖","A";"八","B";"攃","C";"咑","D";"鵽","E";"发","F";"旮","G";"哈","H";

"丌","J";"咔","K";"垃","L";"妈","M";"乸","N";"噢","O";"帊","P";"七","Q";

"冄","R";"仨","S";"他","T";"屲","W";"夕","X";"丫","Y";"帀","Z"},2,TRUE)

这个公式用到了常量数组和VLOOKUP函数的区间匹配，原理是利用汉字的机器编码组成常量数组，用VLOOKUP函数的区间匹配返回对应的字母，不理解这个原理的小伙伴也不要紧，只需要复制上面的公式到对应的单元格，然后将参数的“B2”改成对应汉字的单元格位置就好

添加好后同样下拉填充，这样我们通过辅助列已经基本就已经完成了姓名的所有首字母提取

4

最后我们只需要将所有字母用“&”串联起来，就可以得到姓名的所有首字母。

END

方法2：公式合并提取（进阶用法）

1

有函数基础经验的小伙伴，可以直接用

=VLOOKUP(MID(A2,1,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,TRUE)

&VLOOKUP(MID(A2,2,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,TRUE)

&VLOOKUP(MID(A2,3,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,TRUE)

把所有公式串连起来，这样就省去了辅助列的步骤，不过公式因为过长，比较容易写错

END

方法3：名称管理器+公式（高级用法）

1

已经非常熟练WPS各个功能的小伙伴，还可以把常量数组

{"","";"吖","A";"八","B";"攃","C";"咑","D";"鵽","E";"发","F";"旮","G";"哈","H";

"丌","J";"咔","K";"垃","L";"妈","M";"乸","N";"噢","O";"帊","P";"七","Q";

"冄","R";"仨","S";"他","T";"屲","W";"夕","X";"丫","Y";"帀","Z"}

放进名称管理器中，命名为“汉字机器编码”（名称可改）

再用=VLOOKUP(MID(A2,1,1),汉字机器编码,2,TRUE)&VLOOKUP(MID(A2,2,1),汉字机器编码,2,TRUE)&VLOOKUP(MID(A2,3,1),汉字机器编码,2,TRUE)

就可以非常帅气地只用极少字符就完成了这个提取过程

END

注意事项

公式要多练习、多用才能熟练