利用公式联想数据有效性录入

f8b1987

缺点：不完美，输入一部分文字后，需要手动点击三角下拉选择存在关联文字的选项，不能像VBA一样可以自动显示联想内容。

优点：可以去掉无关的数据有效性下拉选项，简化下拉框内容，避免手工输入名称不完整；操作简易，不像VBA代码修改代码需要了解代码结构。
补充：xlsx格式下容易卡，大概是引用整列的原因，建议另存为xls格式使用，或者自行优化公式引用范围。
利用公式进行数据有效性联想录入.zip (9.36 KB, 下载次数: 19)

2021-4-26 11:36 上传

点击文件名下载附件

第一步：
制作好所有名称列表，存放在某个位置


第二步：
设定B1单元格为录入内容所在位置，C列用于提醒是否录入了完整名称，D列为辅助列用于提取模糊联想的所有结果


第三步：
设置B1单元格数据有效性

公式为以下，注意如果辅助列变化就修改D:D

1. =OFFSET(D1,,,SUM(IF(LEN($D:$D)>1,1,0)))

复制代码

选择B1单元格时的操作提醒设置

录入简称时，允许预先录入


第四步：
C1单元格公式

1. =IF(ISERROR(VLOOKUP(B1,G:G,1,)),"尚未录入正确名称","")

复制代码

第五步：
D列数组公式

1. =IF(OR(B$1="",INDEX(G:G,LARGE(IF(IFERROR(FIND($B$1,G:G),0),ROW(G:G),0),ROW(A1)),1)="公司名称"),"",INDEX(G:G,LARGE(IF(IFERROR(FIND($B$1,G:G),0),ROW(G:G),0),ROW(A1)),1))

复制代码

公式中的G:G是公司全称所在列，如果位置不一样，可以自行修改;引用的是$B$1单元格，位置变化也可以修改。"公司名称"是标题名称，如果标题变化，也可以修改。


以上完成了所有操作。
手工录入“深圳”，点击小三角，显示如下

因为没有完成录入，所以C1提醒“尚未录入正确名称”
我们选择一个全名，结果如下