wangyi041228 发表于 2023-2-24 01:08

Excel 查之前最近一项数据的6种公式

表里记录着各物品的返修日期和序列号,用简单的公式算出累计的返修次数和之前最近一次的返修日期。
样例如表:


返修次数
C2=COUNTIF(B$2:B2,B2)


上次返修日期
D5=IFNA(LOOKUP(1,0/(B$2:B4=B5),A$2:A4),"")
有 0/(B$2:B4=B5) 表达式加持,Lookup 经典而强大。

D5=IF(C5>1,INDEX(FILTER(A$2:A4,B$2:B4=B5),C5-1),"")
Office 365和2021带 Filter 函数,其他版本有插件。

D5=IF(C5>1,INDEX(A:A,SMALL(IF(B$2:B4=B5,ROW(A$2:A4)),C5-1)),"")
常见写法。

如果允许把返修日期倒序排列,新增记录放最上面,那么下面两种公式无需辅助列即可成立。搜索范围从下一行开始,如果要把公式拉到底,需要把范围多拉一行。
D2=IFNA(INDEX(A3:A$14,MATCH(B2,B3:B$14,0)),"")
D2=XLOOKUP(B2,B3:B$14,A3:A$14,"",0)

如果额外允许把SN前置:
D2=IFNA(VLOOKUP(A2,A3:B$14,2,0),"")

腾讯文档
目前没有 FILTER 和 XLOOKUP,且不支持IF里的列表,所以上面的三种公式没法用。另外目前没有IFNA,要用IFERROR替代。
支持 0/(B$2:B4=B5) 表达式,表扬一下。
https://docs.qq.com/sheet/DUnpaelFCckRtQUxz

谷歌文档
不支持 0/(B$2:B4=B5) 表达式,上面的 LOOKUP 简易公式不能用。难道是写法不一样?
https://docs.google.com/spreadsh ... u3CpYAFySMyUjJC8dKk


绕指流光 发表于 2023-2-24 08:19

本帖最后由 绕指流光 于 2023-2-24 08:25 编辑

谷歌可能是不支持
如果允许时间倒序,offset一下让vlookup查询范围从下一个单元格起就行了
倒序构建查询数组可以试试if({1,0},,)


另外这种典型的桌面数据库查询需求建议直接上access解决,还能搓个界面给人用,再套个管理模板局域网也能登陆。
非要强行公网在线多人编辑的话就没辙了,要不试试WPS的?
—— 来自 OPPO PEDM00, Android 13上的 S1Next-鹅版 v2.5.2-play

cxn 发表于 2023-2-24 10:30

页: [1]
查看完整版本: Excel 查之前最近一项数据的6种公式