ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] Âà¸m¤½¦¡

[µo°Ý] Âà¸m¤½¦¡

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2021-1-14 11:20 ½s¿è


´ú¸ÕÀÉ : Âà¸m¤½¦¡.rar (58.59 KB)
¤£¦n·N«ä¡A·Q±NB25©MV25ªº¤½¦¡¡A¦A¥[¤WÂà¸mªº»Ý¨D~
½Ð°Ý¡J¤U¦Cªº2­Ó¤½¦¡­n¦p¦ó½s¼g¡H
ÁÂÁÂ !

2021-0105!B2
=±NA!$B$2:$S$51¦UÄ檺³Ì¤j­È¤§A!¦P¦CAÄæ­È½Æ»sÂà¸m¶K¤WB2:S19

2021-0105!V2
=±NA!$V$2:$AM$51¦UÄ檺³Ì¤p­È¤§A!¦P¦CAÄæ­È½Æ»sÂà¸m¶K¤WV2:AM19

PS¡JA!·j´M½d³òªº¦U³æÄæ¾ãÄ欰""®É¡A«h¨S¦³³Ì¤j­È©Î³Ì¤p­È(§YªÅ¥Õ®æ¤£µø¬°0)
EX :2021-0105!
B8=""©MV18=""

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2021-1-14 16:52 ½s¿è

¦³¸Õ®M¥Îª©¥D½ç±Ðªº¤½¦¡~
A!B2
=IF(COLUMN(A$1)>SUM(N(--(0&INDEX(A!$B$2:$S$51,,ROW($A1)))=MAX(A!B$2:B$51))),"",SMALL(IF(--(0&INDEX(A!$B$2:$S$51,,ROW($A1)))=MAX(A!B$2:B$51),A!$A$2:$A$51),COLUMN(A$1)))
A!V2
=IF(COLUMN(A$1)>SUM(N(INDEX(A!$V$2:$AM$51,,ROW($A1))=MIN(A!V$2:AM$51))),"",SMALL(IF(INDEX(A!$V$2:$AM$51,,ROW($A1))=MIN(A!V$2:AM$51),A!$A$2:$A$51),COLUMN(A$1)))
¦ý2­Óµª®×³£¤£¹ï>*<


¬ã¨sª©¥Dªº¤½¦¡ªñ¤G¤Ñ¡AÁÙ¬O¤£ª¾­n¦p¦ó­×§ï¤½¦¡¤~¬O¥¿½T?
·Ð½Ðª©¥D©M¦U¦ì¤j¤j¤£§[½ç±Ð!ÁÂÁÂ!

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2021-1-16 06:10 ½s¿è

¦^´_ 2# ziv976688
¨S¦³·Q¨ì±N"©w¼Æ(>10)§ï¬°¤£©w¼Æ(¦U³æÄæ³Ì¤j­È)·|³o»òÃø>*<

´«¥t¤@­Ó´£°Ý¤è¦¡~
2021-0105
B2=IF(ISERROR(SMALL(IF(A!$B$2:$B$51=MAX(A!$B$2:$B$51),A!$A$2:$A$51),COLUMN(A$1))),"",SMALL(IF(A!$B$2:$B$51=MAX(A!$B$2:$B$51),A!$A$2:$A$51),COLUMN(A$1)))
B3=IF(ISERROR(SMALL(IF(A!$C$2:$C$51=MAX(A!$C$2:$C$51),A!$A$2:$A$51),COLUMN(A$1))),"",SMALL(IF(A!$C$2:$C$51=MAX(A!$C$2:$C$51),A!$A$2:$A$51),COLUMN(A$1)))
:
:
B18=IF(ISERROR(SMALL(IF(A!$R$2:$R$51=MAX(A!$R$2:$R$51),A!$A$2:$A$51),COLUMN(A$1))),"",SMALL(IF(A!$R$2:$R$51=MAX(A!$R$2:$R$51),A!$A$2:$A$51),COLUMN(A$1)))
B19=IF(ISERROR(SMALL(IF(A!$S$2:$S$51=MAX(A!$S$2:$S$51),A!$A$2:$A$51),COLUMN(A$1))),"",SMALL(IF(A!$S$2:$S$51=MAX(A!$S$2:$S$51),A!$A$2:$A$51),COLUMN(A$1)))   
½Ð°Ý : ¦p¦ó±N¤W­z18­Ó¤½¦¡Â²¤Æ¬°¤@¦¡¤U©Ô¦¨®×ªº¤½¦¡?
ÁÂÁÂ!

TOP

³Ì¤j­È:
=LOOKUP("z",IF({1,0},"",SMALL(IF(INDEX(A!$B$2:$S$51,,ROW($A1))=LARGE(INDEX(A!$B$2:$S$51,,ROW($A1)),1),A!$A$2:$A$51),COLUMN(A$1))&""))

³Ì¤p­È:
=LOOKUP("z",IF({1,0},"",SMALL(IF(INDEX(A!$V$2:$AM$51,,ROW($A1))=SMALL(INDEX(A!$V$2:$AM$51,,ROW($A1)),1),A!$A$2:$A$51),COLUMN(A$1))&""))

¤½¦¡¥Îvba¶K¦¨­È, ¼Æ¦r·|¦Û°ÊÂন¼Æ­È®æ¦¡, ""ªÅ¦r²Å·|Åܦ¨¯uªÅ®æ

TOP

¦^´_ 4# ­ã´£³¡ªL
ª©¥D¤j¤j : ±z¦n
±z¤Ó¼F®`¤F
ÁÂÁ±zªº½ç±Ð~·P®¦........(Áù°`)

TOP

        ÀR«ä¦Û¦b : ¦³®É·í«äµL®É­W¡A¦n¤Ñ­n¿n«B¨Ó³¡C
ªð¦^¦Cªí ¤W¤@¥DÃD