L2,下拉=LET(x,UNIQUE(FILTER($C:$C,$D:$D=K2)),
优先级,INDEX(x,XMATCH(MAX(COUNTIF($C:$C,INDEX(F:F,IFERROR(XMATCH(x,F:F),-1)))),COUNTIF(C:C,INDEX(F:F,IFERROR(XMATCH(x,F:F),-1))))),
优先级2,INDEX(x,XMATCH(MAX(COUNTIF($C:$C,INDEX(G:G,IFERROR(XMATCH(x,G:G),-1)))),COUNTIF(C:C,INDEX(G:G,IFERROR(XMATCH(x,G:G),-1))))),
优先级3,INDEX(x,XMATCH(MAX(COUNTIF($C:$C,INDEX(H:H,IFERROR(XMATCH(x,H:H),-1)))),COUNTIF(C:C,INDEX(H:H,IFERROR(XMATCH(x,H:H),-1))))),
其它,INDEX(x,XMATCH(MAX(COUNTIF($C:$C,x)),COUNTIF(C:C,x))),
IFERROR(优先级1,IFERROR(优先级2,IFERROR(优先级3,其它)))
)
|