用excel怎么能实现微博找人的相似功能
发布网友
发布时间：2022-05-16 18:44
共3个回答
热心网友
时间：2023-11-22 15:27
如下图所示，A列为现有姓名，D1为查找内容，将查找结果显示在D2:D12中：
公式为=INDEX(A:A,SMALL(IF(1-ISERR(FIND(D$1,A$2:A$12)),ROW($2:$12),99),ROW(A1)))&""，数组公式，三键结束。
热心网友
时间：2023-11-22 15:28
1、在Sheet1的B1输入
=LOOKUP(CODE(A1),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(LEN(A1)>1,LOOKUP(CODE(MID(A1,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"}),"")&IF(LEN(A1)>2,LOOKUP(CODE(MID(A1,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"}),"")
回车并向下填充；
2、Sheet2的A1留给你输入姓名拼音开头（如LYH或LY）；
3、Sheet2的A2输入
=INDEX(Sheet1!A:A,SMALL(IF(ISNUMBER(FIND(A$1,Sheet1!B$1:B$1000)),ROW($A$1:$A$1000),4^8),ROW(1:1)))&""
数组公式，输入后先不要回车，按Ctrl+Shift+Enter结束计算，再向下填充。追问第三步的功能实现不了，他带出来的人名跟我的首字母对不上。再问一下，区分大小写么
追答你没有按三键结束计算？
你没有输入大写字母，我第1步的公式是返回大写字母，故要保持一致。
直接复制粘贴我的公式就行，不要重新输入，怕你弄错。
热心网友
时间：2023-11-22 15:28
