Excel公式求助：如何批量判断数值是否落入指定的任意数值区间

第一步：预处理区间数据 #

首先要把第二组的区间文本拆为两列独立的数值列：

• 假设区间原始数据存放在Sheet2的A列，从A2行开始
• 在Sheet2的B2单元格输入提取下限的公式：=LEFT(A2,FIND("to",A2)-1)*1
• 在Sheet2的C2单元格输入提取上限的公式：=RIGHT(A2,LEN(A2)-FIND("to",A2)-1)*1
• 下拉填充完所有100万+行区间数据后，选中B、C两列，复制粘贴为纯数值，避免公式反复计算导致卡顿
• 对B列（下限列）做升序排序，排序时要扩展选中C列，保证上下限的对应关系不变，这是后续快速匹配的核心前提

第二步：单值匹配判断 #

假设第一组单值存放在Sheet1的A列，从A2行开始，在B2单元格输入对应公式后下拉即可：
如果你的Excel版本支持LET函数，优先用以下高性能公式：

=LET(
x,A2,
lower,Sheet2!B:B,
upper,Sheet2!C:C,
max_row,COUNTA(lower),
pos,MATCH(x,INDEX(lower,1):INDEX(lower,max_row),1),
IF(AND(pos>=1,x<=INDEX(upper,pos)),"存在","不存在")
)

如果Excel版本不支持LET函数，用嵌套版公式即可：
=IF(AND(MATCH(A2,Sheet2!B:B,1)>=1,A2<=INDEX(Sheet2!C:C,MATCH(A2,Sheet2!B:B,1))),"存在","不存在")

方案原理说明 #

• 普通COUNTIFS是逐行遍历匹配，10万条单值匹配100万条区间需要做千亿次计算，必然卡顿甚至报错，本方案利用MATCH参数为1时的二分查找特性，单次匹配仅需要最多20次计算，总计算量仅200万次，性能完全满足你的数据量要求
• 即使区间存在重叠、上下限相等的单个值区间，公式也可正常识别，无需额外处理

注意事项 #

• 所有数值要确保是数字格式，不能是文本格式，否则匹配会出错，可选中所有数值列，设置单元格格式为数值，小数位设为0即可

内容的提问来源于stack exchange，提问作者GoodSPORT