Google Sheets自动扩展公式失效求助：计算股票区间最高价

背景说明 #

• 拥有两个表格：Watchlist（股票关注列表）和stockPrices（股价历史数据）
• Watchlist 表格详情：
  • A1:A列存储股票代码列表
  • AV列是辅助单元格：AV1存今日日期，AV2存5年前的今日日期；AV3、AV4分别对应stockPrices中今日、5年前数据所在的行号
  • AL列记录对应股票代码在stockPrices表格中的列索引
• stockPrices 表格详情：
  • A2:A是2000年1月1日至2024年12月31日的日期序列
  • B至LR列对应Watchlist中的股票历史股价，值分为三类：小数（有效股价）、NA（当日无数据）、空值（未来日期）
  • 第6267行（对应Watchlist!AV3）是今日股价数据，第4962行（对应Watchlist!AV4）是5年前的股价数据

Watchlist 表格结构 #

--+-------+----------+ ... +------+ ... +------------+
  |   A   |     B    | ... |  AL  | ... |     AV     |
--+-------+----------+ ... +------+ ... +------------+
1 | AAPL  |  150.12  | ... | 2    | ... | 05.01.2024 | 今日日期
2 | MSFT  |  344.01  | ... | 4    | ... | 05.01.2019 | 5年前日期
3 | KO    |   55.12  | ... | 3    | ... | 6267       | stockPrices今日行号
4 | PEP   |   70.54  | ... | 330  | ... | 4962       | stockPrices5年前行号
5 | TSLA  |          | ... |      | ... |            |
6 | SPCE  |          | ... |      | ... |            |
--+-------+----------+ ... +------+ ... +------------+

stockPrices 表格结构 #

-----+------------+----------+-------+--------+ ... +-------+
     |     A      |     B    |   C   |    D   |     |  LR   |
-----+------------+----------+-------+--------+ ... +-------+
1    | Date       |  AAPL    | KO    | MSFT   | ... | PEP   |
2    | 01.01.2000 |  0.92    | 6.21  | 23.32  | ... | 11.54 |
3    | 02.01.2000 |  1.11    | 6.54  | 23.12  | ... | 10.76 |
4    | 03.01.2001 |  NA      | 7.00  | 24.11  | ... | 10.53 |
.    | ...        |  ...     | ...   | ...    | ... | ...   |
4962 | 05.01.2019 |  30.51   | 18.21 | 100.21 | ... | 60.43 |
.    | ...        |  ...     | ...   | ...    | ... | ...   |
6267 | 05.01.2024 |  149.74  | 52.24 | 344.01 | ... | 56.00 |
.    | ...        |  ...     | ...   | ...    | ... | ...   |
6628 | 31.12.2024 |          | ...   |        | ... |       |
-----+------------+----------+-------+--------+ ... +-------+

需求 #

在Watchlist!B1中设置可自动向下扩展的公式，计算每只股票在AV1（今日）到AV2（5年前）区间内的最高股价，也可直接使用AV3、AV4的行号数据。

现有情况 #

• 手动拖拽可实现计算的公式：

=MAX(
  FILTER(
    INDEX(stockPrices!$B$2:$LR, , AL1), 
    stockPrices!$A$2:$A<>"", 
    stockPrices!$A$2:$A>=$AV$2, 
    stockPrices!$A$2:$A<=$AV$1, 
    stockPrices!$A$2:$A<>0, 
    stockPrices!$A$2:$A<>""
  )
)

• 尝试用MAP函数实现自动扩展，但报错：“错误：FILTER函数的范围大小不匹配。预期行数：6627，列数：1。实际行数：1，列数：1。”
  报错公式：

=MAP(AL1:AL,LAMBDA(idx,MAX(
  FILTER(
    INDEX(stockPrices!B2:LR, , idx), 
    stockPrices!A2:A<>"", 
    stockPrices!A2:A>=AV2, 
    stockPrices!A2:A<=AV1, 
    stockPrices!A2:A<>0, 
  )
)))

解决方法 #

报错原因是MAP遍历到空的idx值时，INDEX返回单个空单元格，导致FILTER的条件范围（6627行）和数据范围（1行）不匹配。以下是修复后的两种方案：

方案1：基于日期过滤（兼容空索引） #

=MAP(AL1:AL, LAMBDA(idx,
  IF(ISBLANK(idx),,
    MAX(
      FILTER(
        INDEX(stockPrices!$B$2:$LR, , idx),
        stockPrices!$A$2:$A >= Watchlist!$AV$2,
        stockPrices!$A$2:$A <= Watchlist!$AV$1,
        NOT(ISNA(INDEX(stockPrices!$B$2:$LR, , idx)))
      )
    )
  )
))

优化说明：

1. 用IF(ISBLANK(idx),, ...)跳过空的列索引，避免空值导致的范围不匹配
2. 移除重复的stockPrices!$A$2:$A<>""和stockPrices!$A$2:$A<>0条件，日期列本身无0值，且>=AV2已过滤空日期
3. 添加NOT(ISNA(...))过滤NA值，避免MAX计算被NA干扰

方案2：基于行号直接取值（效率更高） #

如果已经知道目标区间的行号（AV3和AV4），可以直接定位数据范围，无需FILTER，计算速度更快：

=MAP(AL1:AL, LAMBDA(idx,
  IF(ISBLANK(idx),,
    MAX(INDEX(stockPrices!$B$2:$LR, Watchlist!$AV$4:Watchlist!$AV$3, idx))
  )
))

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