如何用动态公式提取特定类别对应的最后5个价格（不足则返回全部）
2026-8-2
问题需求
给定如下样本数据（按日期升序排列）：
| Date | Category | Price | Quantity |
|---|---|---|---|
| 02-01-2019 | BASE_Y-20 | 279 | 1 |
| 02-01-2019 | BASE_Y-21 | 271.25 | 0 |
| 03-01-2019 | BASE_Y-20 | 276.5 | 2 |
| 03-01-2019 | BASE_Y-21 | 266.5 | 0 |
| 04-01-2019 | BASE_Y-20 | 272.88 | 14 |
| 04-01-2019 | BASE_Y-21 | 266.5 | 1 |
| 07-01-2019 | BASE_Y-20 | 270.48 | 29 |
| 07-01-2019 | BASE_Y-21 | 262.75 | 0 |
| 08-01-2019 | BASE_Y-20 | 270 | 4 |
| 08-01-2019 | BASE_Y-21 | 264 | 0 |
| 09-01-2019 | BASE_Y-20 | 270.06 | 31 |
| 09-01-2019 | BASE_Y-21 | 262.85 | 0 |
需要编写动态公式，提取BASE_Y-20类别对应的最后5个价格：
- 当该类别价格数量≥5时，返回最新的5个（按日期从新到旧排序）
- 当数量<5时，返回所有存在的价格
已尝试SUMPRODUCT和OFFSET组合，但未成功实现需求。
解决方案
方案1：Excel 365/2021 动态数组公式
直接使用FILTER+TAKE组合，简洁高效：
=TAKE(SORT(FILTER(C2:C13,B2:B13="BASE_Y-20",""),1,-1),5)
公式解析：
FILTER(C2:C13,B2:B13="BASE_Y-20","")：筛选出所有BASE_Y-20对应的价格SORT(...,1,-1)：将筛选结果按原数据行号倒序排序（原数据日期升序，倒序后最新数据在前）TAKE(...,5)：提取前5个结果，若筛选结果不足5个则返回全部
方案2：兼容旧版Excel的公式（非动态数组）
如果使用旧版Excel，可结合INDEX+SMALL+IF实现，需按Ctrl+Shift+Enter作为数组公式输入：
=INDEX(C:C,SMALL(IF(B$2:B$13="BASE_Y-20",ROW(B$2:B$13)),COUNTIF(B$2:B$13,"BASE_Y-20")-ROW(A1)+1))
使用说明：
- 在空白单元格输入公式，按
Ctrl+Shift+Enter确认 - 下拉公式，直到出现
#NUM!错误值为止，有效结果即为所需的最后5个价格 - 若BASE_Y-20的价格数量不足5个，下拉对应次数后就会出现错误值，前几个结果即为全部数据
公式解析：
IF(B$2:B$13="BASE_Y-20",ROW(B$2:B$13))：返回所有BASE_Y-20对应行的行号，其他行返回FALSECOUNTIF(B$2:B$13,"BASE_Y-20")：统计BASE_Y-20的价格总数SMALL(...,COUNTIF(...) - ROW(A1)+1)：从大到小提取行号（即最新的行在前）INDEX(C:C,...)：根据行号提取对应价格
内容的提问来源于stack exchange，提问作者Zam
相关产品推荐
相关产品推荐
