求对应的最大的日期

inrever

1. =MAP(C2:C4,LAMBDA(n,TEXT(MAX(FILTER(A2:A20,--TEXT(A2:A20,"emm")=n)),"yyyy/m/d")))

复制代码

秃头发师

=MAX(IF(--TEXT($A$2:$A$20,"yyyymm")=C2,$A$2:$A$20,""))

浮生若梦~~~

=LET(a,A2:A20,b,TEXT(a,"emm"),c,UNIQUE(b),VSTACK({"年月","最大的日期"},HSTACK(c,MAP(c,LAMBDA(x,MAX(FILTER(a,b=x)))))))

一杯绿茶来了

=MAX((TEXT($A$2:$A$20,"emm")/1=C2)*$A$2:$A$20)

zhangcheng6688

学习一下字典，打扰了
Sub test()
arr = [a1].CurrentRegion
Set d = CreateObject("scripting.dictionary")
For i = 2 To UBound(arr)
   y = Year(arr(i, 1))
   m = Month(arr(i, 1))
   If Len(m) = 1 Then
      m = "0" & m
   End If
   rq = y & m
   If Not d.exists(rq) Then
      d(rq) = arr(i, 1)
   Else
      If d(rq) < arr(i, 1) Then
         d(rq) = arr(i, 1)
      End If
   End If
Next
brr = [c1].CurrentRegion
For j = 2 To UBound(brr)
   brr(j, 2) = d(CStr(brr(j, 1)))
Next
[c1].CurrentRegion = brr
End Sub

飞天篮球猪

pxy0409

新公式 老公式 透视表 pq ,方法甚多,但只有8楼的写法最简单,
LOOKUP(C2,--TEXT($A$2:$A$20,{"emm",0}))

flostst

1. =LOOKUP(9E+307,IF(--RIGHT(C2,2)=MONTH(A2:A20),A2:A20))

复制代码