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

[µo°Ý] §ä²Å¦X±ø¥óªº³Ì¤j­È_¦¸¤j­È

[µo°Ý] §ä²Å¦X±ø¥óªº³Ì¤j­È_¦¸¤j­È

¤j¤j¦n,

BÄæ©T©w¤GºØ½¦±aÃC¦â ºñ & ³z
¥B¥H½¦±aÃC¦â,±a¥XDÄ檺­È(¤é´Á¨Ó·½¬O"·R¤ß"¤u§@ªíªºDÄæ)
¤é´Á»Ý¨D¤À§O¬O
1) ºñ...³Ì¤j¤é´Á (D3)
2) ºñ...¦¸¤j¤é´Á (D4)
3) ³z...³Ì¤j¤é´Á (D5)
4) ³z...¦¸¤j¤é´Á (D6)

°ÝÃD¡G
1)  D3 / D5¤w¦³¤½¦¡,¦ýD4 & D6ªº¦¸¤j­È­n¦p¦ó¤U¤½¦¡?
2) ­n¦bDÄæÄ~Äò²ÄN¤jªº­È,¤£ª¾¥i§_¹F¦¨?
§ä³Ì¤j­È_¦¸¤j­È.rar (19.32 KB)

¦^´_ 1# PJChen


±N©p­ì¨Ó¤½¦¡¤¤Max§ï¨Ï¥Îlarge
LARGE(array, k)LARGE ¨ç¼Æ»yªk¨ã¦³¤U¦C¤Þ¼Æ (¤Þ¼Æ¡G±N¸ê°T´£¨Ñµ¹°Ê§@¡B¨Æ¥ó¡B¤èªk¡BÄÝ©Ê¡B¨ç¼Æ©Îµ{§Çªº­È¡C)¡G

Array  ¥²­n¡C³o¬O±z­n§ä¥X²Ä k ­Ó³Ì¤j­È¤§¼Æ­È¸ê®Æªº°}¦C©Î½d³ò¡C
K  ¥²­n¡C³o¬O­n¶Ç¦^¤§¸ê®Æ°}¦C©ÎÀx¦s®æ½d³ò¤¤ªº¦ì¸m (¥Ñ³Ì¤j­Èºâ°_)¡C

D3=IF(LARGE(IF((·R¤ß!$B:$B=$B3),·R¤ß!$D:$D),1)=0,"",LARGE(IF((·R¤ß!$B:$B=$B3),·R¤ß!$D:$D),1))
D4=IF(LARGE(IF((·R¤ß!$B:$B=$B4),·R¤ß!$D:$D),2)=0,"",LARGE(IF((·R¤ß!$B:$B=$B4),·R¤ß!$D:$D),2))
Ctrl+Alt+Enter

TOP

¥»©«³Ì«á¥Ñ hcm19522 ©ó 2020-9-7 10:17 ½s¿è

D3 ¤U©Ô {=LARGE(IF(B3=·R¤ß!B$4:B$99,·R¤ß!D$4:D$99),COUNTIF(B$3:B3,B3))

https://blog.xuite.net/hcm19522/twblog/589332967
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¥»©«³Ì«á¥Ñ PJChen ©ó 2020-9-7 18:25 ½s¿è

¦^´_ 2# jcchiang
¦^´_ 3# hcm19522
¨â¦ì¤j¤j¦n,
"·R¤ß"¤u§@ªíªºB¡BC¡BDÄ檺¸ê®Æ±q¶i³f¶}©l,·|¤@ª½¨ì¥X³f¼Æ¶q¬°0®É,¤~ºâµ²§ô,
©Ò¥HB:Dªº¸ê®Æ¥i¯à·|­«½Æ¥X²{,ª½¨ì¼Æ¶q¬°0,´ú¸Õ¨ì²Ä2~3¤jªº"¤é´Á"³£·|¥¿½T,²Ä3~4¤j¶}©lµª®×´N¤£¥¿½T¤F
¤q¨ä¤é´Á­«½Æ¦¸¼Æ¦h®É....
§ä³Ì¤j­È_¦¸¤j­È.rar (23.52 KB)

TOP

¬Ýªþ¥ó, ¤½¦¡¤£°£¿ù, ¦³¥²­n¦Û¦æ¦A¥[iferror:
Xl0000249.rar (17.77 KB)

TOP

¦^´_ 5# ­ã´£³¡ªL

­ã¤j,
¤£¦n·N«ä,§Ú¥Î±z¦^ÂЪºªþ¥ó,¥Î³o¼Ëªº¤è¦¡©Ò±o¥Xªº²ÄN¤j,¤é´Á·|­«½Æ,¦ý§Ú¤£­n­«½Æªº¤é´Á,¤@­Ó¤é´Á¥u­n¥X²{¤@¦¸´N¦n,§Ú­n¦p¦ó°£¿ùÅý¥¦¥u¥X²{¤@¦¸?
20200908.153059.jpg
2020-9-8 15:33

TOP

¦^´_ 6# PJChen


¥ý¤â°Ê°µ¥X¦UÄæ¦ìªº»Ý¨Dµ²ªG¤ÎÅÞ¿è,
¤£µMÁÙ¬O²q¤£§¹~~

TOP

¦^´_ 6# PJChen


D3/°}¦C:
=MAX((·R¤ß!B$4:B$99=B3)*(·R¤ß!D$4:D$99<IF(B3=B2,D2))*·R¤ß!D$4:D$99)

TOP

¦^´_ 8# ­ã´£³¡ªL

¯u°ª¿³¬Ý¨ì³o­Óµª®×,§¹¥þ¥¿½T...«D±`ÁÂÁ­ã¤j

TOP

        ÀR«ä¦Û¦b : ¤Ñ¤W³Ì¬ü¬O¬P¬P¡A¤H¥Í³Ì¬ü¬O·Å±¡¡C
ªð¦^¦Cªí ¤W¤@¥DÃD