Excel无VBA如何提取与列B标记行同符号的列A首尾数值

实现方案 #

前置说明 #

你的需求是：匹配B列值为1对应行的A列数值符号，提取A列中所有和该符号一致的第一个、最后一个数值，无需VBA，仅用Excel函数即可实现，以下是具体公式： #

前提约定 #

• A列为数值列，B列为标记列，数据从第2行开始
• B列有且仅有1个值为1的标记行

1. 计算目标匹配符号 #

在任意空白单元格（比如D2）输入以下公式，得到B=1对应行A列值的符号（返回1为正，-1为负）：
=SIGN(INDEX(A:A,MATCH(1,B:B,0))) #

2. 提取同符号的首个A列数值 #

Excel 365/2021及以上版本

=XLOOKUP(D2,SIGN(A:A),A:A,"无匹配数据",0)

Excel 2019及以下版本

选中单元格后按Ctrl+Shift+Enter输入数组公式：
=INDEX(A:A,MATCH(D2,SIGN(A:A),0)) #

3. 提取同符号的末尾A列数值 #

Excel 365/2021及以上版本

=XLOOKUP(D2,SIGN(A:A),A:A,"无匹配数据",0,-1)

Excel 2019及以下版本

选中单元格后按Ctrl+Shift+Enter输入数组公式：
=INDEX(A:A,MAX(IF(SIGN(A:A)=D2,ROW(A:A),0))) #

优化建议 #

如果你的数据是固定范围（比如数据仅到第100行），将公式中A:A、B:B的整列引用替换为A2:A100、B2:B100，可以提升运算效率。

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