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

³]©w®æ¦¡¤Æ±ø¥ó-¦h­È

³]©w®æ¦¡¤Æ±ø¥ó-¦h­È

¥»©«³Ì«á¥Ñ LITTLEFAT7048 ©ó 2015-11-18 20:16 ½s¿è

¦U¦ì«e½ú¦n¡A¤p§Ì¤S¨Ó½Ð±Ð°ÝÃD¤F¡I

³o¦¸·Q½Ð°Ýªº¬O³]©w®æ¦¡¤Æ±ø¥ó¦³¿ìªk³]©w¦h­È¶Ü¡H
¡]°£¤F¤@­Ó¤@­Ó³]¤§¥~¡A¤@­Ó¤@­Ó³]©wªº¤p§Ì¦³¦¨¥\¡^
¦]¬°¬Ý»¡©ú³]©w®æ¦¡¤Æªº¦n¹³­nOFFICE2010ªº¤~¥i¥H¥Î¨ì¤T­Ó¥H¤W¡C

¤p§Ì¦³©w¸qRNG1ªºÀx¦s®æ½d³ò¡A

ÁÙ¦³¤@­Ó½d³ò¬O¨S¦³³]¾ãÀx¦s®æ½d³òªº¡A
¦ý¬O»Ý­n¥Î¨ì¸Ì­±ªº­È¡C

Á|¨Ò¨Ó»¡¡G
A1:E3¬°RNG1

A4:E6¬°»Ý­n¹ï·Óªº­È

¦ÓA4:E6¬°ÅÜ­È¡A´N¬O±q¨ä¥LSHEETS¸Ì°Ñ·Ó¹L¨Óªº­È¡A¥i¦Û¦æ¿é¤Jªº­È¡C
¡]¤£¹L³£¬O¾ã¼Æ¡A¤£·|¦³¤å¦r©Î¬O¤p¼Æ¤§Ãþªº¡^

¤p§Ì»Ý­nªº¥\¯à¬O¡G

RNG1¸Ì­±¦³¤@­Óªº­È¡uµ¥©ó¡vA4:E6¸Ì­±­Èªº®É­Ô¡A
¨äRNG1Àx¦s®æ¸Ìªº¨º®æ´N·|Åܦ¨¬õ¦r¶À©³¤§Ãþªº¡A¨ä¥LRNG1½d³ò¤ºªº®æ¦¡¤£·|ÅÜ

µMA4:E6¥i¯à¥þ³¡³£¿é¤J¡A©ÎªÌ¥u¿é¤J¤@­Ó¡A³o­Ó¤£¤@©w¡A
ºÝ¬Ý­n¿é¤JªÌ·Q­n¿é¤J¦h¤Ö­Ó³£¦æ¡A¦ý¬O´N¬O¤£¯à¶W¹LA4:E6³o¨ÇÀx¦s®æ¥H¥~¡C

¤p§Ì¥Ø«eªº·Qªk¥u¦³¤@®æµ¹¤@­Ó¥N½X¡G
DIM I as Integer, J as Integer...........¤@ª½¼g¤U¥h
I = RANGE("A4") J=RANGE("A5") ...........
IF RNG1=I Then .....
    RNG1=J Then .....

¤§Ãþªº¤@ª½¼g¤U¥hªº¡C

½Ð°Ý¦³§ó¦nªº¤è¦¡¥i¥H´£¨Ñªº¶Ü¡H

¥ÎCOUNTIF,MATCH, ¯Â¼Æ­È¤]¥i¥ÎRANK,
¦ô­p¥Î¤@­Ó®æ¦¡¤Æ¤½¦¡§Y¥i,
³Ì¦n¯à¤W¶Çªþ¥ó¬Ý¬Ý~~

TOP

¥»©«³Ì«á¥Ñ LITTLEFAT7048 ©ó 2015-11-19 01:40 ½s¿è

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

­ã¤j±z¦n¡G
¤p§Ì»Ý­nªº¥\¯à¡A
Àɮ׸̭±¦³½d³ò¦WºÙRNG1,RNG2ªº¦b¸Ì­±¤F¡A

¦Ó¤p§Ì§Æ±æ
RNG1¡]¤]´N¬O©Ò¦³¡u¤H1¡v¡^¨ºROWªº­È¡A
¦pªG¦bB52:F54¸Ì­±ªº­È¦³©M¤H1¨º¦æ¦³¤@¼Ëªº¡A´N©M9ªºÃC¦â©M©³¦â¤@¼Ë
¡]ÃC¦â©M©³¦âªº­×§ï¤ñ¸û²³æ¡A¨º­Ó¤p§Ì¥i¥H¦Û¦æ³B²zXD¡^

RNG2¡]¤]´N¬O©Ò¦³¡u¤H2¡v¡^¨ºROWªº­È¡A
¦pªG¦bI52:N54¸Ì­±ªº­È¦³©M¤H2¨º¦æ¦³¤@¼Ëªº¡A´N©M9ªºÃC¦â©M©³¦â¤@¼Ë

³Ì«á´N¬O¬P´Á¨º¦æ¡]RNG3¡^¡A¦pªG¬O¤»¡B¤é¡A§ïÅÜÃC¦â¦Ó¤w¡C
¥Ø«e»Ý­nªº¬O³o¤T­Óªº³]©w®æ¦¡¤Æ±ø¥ó¡C

¦]¬°¹³B52:F54Á`¦@¦³15®æ¡A©Ò¥H¤£ª¾¹D«ç¼Ë¤@¦¸¼g¶i¥h¡A
¤À¦¸¼g­Ó15¦¸³oºØ²³æªº¤p§Ì¬O·|³B²z¡A¦ý¬O§Æ±æ¥i¥H¦h¾Ç¤@ÂI¡I
ÁÂÁ«ü±Ð¡I

©³¤U¬Oªþ¥ó¡G
½ü¥ðªí-´¼¼zª©(§¹¦¨ª©)-Âù¤Hª©-1.zip (35.21 KB)

TOP

®æ¦¡¤Æ±ø¥ó¤½¦¡¡G
¢Ð¢²¡G=RANK(B3,$B$52:$F$54)¡@¡Ö¡@¦V¥k®æ¦¡½Æ»s
¢Ð¢³¡G=RANK(B4,$J$52:$N$54)¡@¡Ö¡@¡¨¡¨
¢Ð¢´¡G=OR(B5="¤»",B5="¤é")¡@¡@¡Ö¡@¡¨¡¨

¢²¡D¢³¡D¢´¦C§¹¦¨¡A¦A®æ¦¡½Æ»s¦Ü¨ä¥¦¦ì¸m

TOP

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

ÁÂÁ­ã¤j¡A¦¨¥\¤F¡C

¤£¹L¤p§Ì¬d¤F¤@¤Urank¨ç¼Æªº´y­z¡A¥Ø«e·Q¤£¥X¨Ó¬°¦ó­n¥Îrank¨ç¼Æ¥h±a¡H(Orz....)

¦pªG¬O¦h¤å¦rªº±ø¥óªº¸Ü¡A¥i¯à´N¤£¯à¥Îrank¨ç¼Æ¤F¡H­n¥Îor¬O¶Ü¡H
¦]¬°¤p§Ì¬Ýrank»¡©ú¥L»¡«D¼Æ­Èªº·|³Q©¿²¤¡C

RANK ¨ç¼Æ
¥»¤å±N»¡©ú Microsoft Excel ¤¤ RANK ¨ç¼Æªº¤½¦¡»yªk¤Î¨Ï¥Î¤è¦¡¡C

´y­z
¶Ç¦^¼Æ¦r¦b¤@¼Æ¦C¤¤ªº±Æ¦W¡C ¼Æ¦r±Æ¦W¬O¼Æ¦r¬Û¹ï©ó¼Æ¦C¤¤¨ä¥L¼Æ­Èªº¤j¤p (¦pªG±z¬°¼Æ¦C±Æ§Ç¡A¼Æ¦rªº±Æ¦W´N¬O¨ä¦ì¸m)¡C
­«­n  ¦¹¨ç¼Æ¤w¸g¥Ñ¤@­Ó©Î¦h­Ó·s¨ç¼Æ¨ú¥N¡A·s¨ç¼Æ¥i¯à´£¨Ñ§ó¨Îªº·Ç½T©Ê¡A¦Ó¥B¨ä¦WºÙ§ó¯à¤Ï¬M¨ä¥Î³~¡C ÁöµM°ò©ó¦^·¹¬Û®e©ÊÁÙ¬O¦³´£¨Ñ³o¨Ç¨ç¼Æ¡A¦ý¬O±zÀ³¸Ó¦Ò¼{±q²{¦b¶}©l¨Ï¥Î·s¨ç¼Æ¡A¦]¬°³o¨Ç¨ç¼Æ¦b±N¨Óªº Excel ª©¥»¤¤¥i¯à¤£·|´£¨Ñ¡C
¦p»Ý·s¨ç¼Æªº¸Ô²Ó¸ê°T¡A½Ð°Ñ¾\ RANK.AVG ¨ç¼Æ¤Î RANK.EQ ¨ç¼Æ¡C

»yªk

RANK(number,ref,[order])

RANK ¨ç¼Æ»yªk¨ã¦³¤U¦C¤Þ¼Æ¡G

Number     ¥²­n¡C ³o¬O­n§ä¥X¨ä±Æ¦Wªº¼Æ¦r¡C
Ref     ¥²­n¡C ³o¬O¼Æ¦Cªº°}¦C©Î°Ñ·Ó¡C ·|©¿²¤ ref ¤¤ªº«D¼Æ­È¡C
Order     ¿ï¥Î¡C ³o¬O«ü©w±Æ¦C¼Æ­È¤è¦¡ªº¼Æ¦r¡C
¦pªG order ¬° 0 (¹s) ©Î³Q¬Ù²¤¡A«h Microsoft Excel §â ref ·í¦¨¥H»¼´î¶¶§Ç±Æ§Çªº¼Æ¦C¨Ó¬° number ±Æ¦W¡C
¦pªG order ¤£¬O 0¡A«h Microsoft Excel ·|±N ref ·í¦¨¥H»¼¼W¶¶§Ç±Æ§Çªº¼Æ¦C¨Ó¨Ó¬° number ±Æ¦W¡C

µù¸Ñ

RANK ·|µ¹­«½Æªº¼Æ¦r¬Û¦Pªº±Æ¦W¡C µM¦Ó¡A­«½Æªº¼Æ¦r·|¼vÅT«áÄò¼Æ¦rªº±Æ¦W¡C ¨Ò¦p¡A¦b¤@­Ó¥H»¼¼W¶¶§Ç±Æ§Çªº¾ã¼Æ¼Æ¦C¤¤¡A­Y¼Æ¦r 10 ¥X²{¨â¦¸¡A¨Ã¥B±Æ¦W¬° 5¡A«h 11 ªº±Æ¦W±N¬O 7 (¦p¦¹´N¨S¦³±Æ¦W 6 ªº¼Æ¦r)¡C
°ò©ó¬Y¨Ç¥Øªº¡AÀ³¸Ó±Ä¥Î±Nµ¥¼Æ¦C¤J¦Ò¶qªº±Æ¦W©w¸q¡C ¦b¤W¨Ò¤¤¡AÀ³¸Ó§â¼Æ¦r 10 ªº±Æ¦W­×¥¿¬° 5.5¡C ±N¤U¦C­×¥¿¦]¯À¥[¤J RANK ©Ò¶Ç¦^ªº­È¡A§Y¥i¹F¦¨¦¹¥Øªº¡C ¦¹­×¥¿¦]¯À¹ï©ó¥H»¼´î¶¶§Ç­pºâ (order = 0 ©Î¬Ù²¤) ©Î¥H»¼¼W¶¶§Ç­pºâ (order = «D¹sªº­È) ¨âºØ®×¨Ò³£¾A¥Î¡C
¬Û¦Pªº­×¥¿¦]¯À ranks=[COUNT(ref) + 1 ¡V RANK(number, ref, 0) ¡V RANK(number, ref, 1)]/2¡C
¦b¤U¦C½d¨Ò¤¤¡ARANK(A2,A1:A5,1) µ¥©ó 3¡C ­×¥¿¦]¯À¬° (5 + 1 ¡V 2 ¡V 3)/2 = 0.5¡A¦Ó±Nµ¥¼Æ¦C¤J¦Ò¶qªº­×¥¿±Æ¦W¬° 3 + 0.5 = 3.5¡C ¦pªG number ¦b ref ¤¤¥u¥X²{¤@¦¸¡A«h­×¥¿¦]¯À·|¬O 0¡A¦]¬° RANK ¤£»Ý­n°w¹ïµ¥¼Æ°µ½Õ¾ã¡C

TOP

¦^´_ 5# LITTLEFAT7048


=OR(B3=$B$52:$F$54)¡@¤]¬O¥i¥Î¡A¦ý³o¬O¡e°}¦C¡f¤½¦¡¡F¡@©Î¡G=COUNTIF($B$52:$F$54,B3)
³o¨âªÌ¡e¤å¦r¡D¼Æ­È¡f³£¥i¥HÀË´ú¡A¦ý­Y°Ñ·Ó½d³ò¤j¡A¤½¦¡®æ¤]¦h¡A¹Bºâ®É´N¸û¯Ó¸ê·½¡I

¯Â¼Æ­È®É¡A§ï¥Î RANK¡A¦b¤j½d³ò®É¡A³t«×§Ö«Ü¦h¡I

¾Ç¤½¦¡´N¬O­n¦Û¤v¥ý¥h§ä¨ç¼Æ´ú¸Õ¡I

TOP

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

¨C¦¸¬Ý¨ì¦³¤£ª¾¹Dªº¨ç¼Æ³£·|¥h¬d¤@¤U¡A
¤£¹L¥i¯à²z¸Ñ¤O¤£¬O«Ü¦n¡A©Ò¥H¦³¨Ç¬Ý¤£À´¡A
ÁٽШ£½Ì¡I

¦A½Ð±Ð¤@­Ó°ÝÃD¡A
¦P¤@­ÓÀɮסA
¦pªG¹³¤H1©M¤H2¦]¬°½ü¥ðªºµf¸¹¤£¤@¼Ë¡A
¥i¥H­pºâ¥X2­Ó¤H¨C­Ó¤ë¦@¦P©ñ°²ªº¤Ñ¼Æ¶Ü¡H

¥Ø«e¦³¼g¤@­Ó¤½¦¡¡G

=COUNTIFS($B$3:$AF$3,RANK(M3,$B$52:$F$54),$B$4:$AF$4,RANK(M4,$J$52:$N$54))

³o¬O¦]¬°³]©wªºµf¸¹­è¦nM3¡A©MM4³£­è¦n©ñ°²¡A
©Ò¥H¿ï³o®æ°µ°ò·Ç¡A¤W­z¨º¦æµª®×·|¥X²{1¡C

¦ý¬OM3¨S¦³¿ìªk¥Î½d³òªº¤è¦¡¡]¨Ò¡GB3:AF3¡A¥u¯à¥Î¤@®æ¤@®æ¿é¤J¡^
©Ò¥HÅܦ¨­n©Ô¤@¾ã­Órow¤~·|¥X²{¦³¦@¦P©ñ°²ªº"1"¡AµM«á¦A¥h¥[Á`"1"¡A
µ²ªGÁöµM¬O­nªº¡A¤£¹L½Ð°Ý¬O§_¦³§Oªº¤è¦¡¡H
ÁÂÁ­ã¤j¼·¤¾«ü±Ð¡I

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2015-11-22 10:34 ½s¿è

¦^´_ 7# LITTLEFAT7048


¨C¤ë¨â­Ó¤H¡e¦P¤@¤Ñ¦P®É©ñ°²¡f¤Ñ¼Æ
°}¦C¤½¦¡¡G
=SUM(COUNTIF(B52:F54,B3:AF3)*COUNTIF(J52:N54,B4:AF4))
=COUNT(RANK(B3:AF3,B52:F54)*RANK(B4:AF4,J52:N54))

¤@¯ë¤½¦¡¡G
=SUMPRODUCT(COUNTIF(B52:F54,B3:AF3)*COUNTIF(J52:N54,B4:AF4))
=SUMPRODUCT(1-ISNA(RANK(B3:AF3,B52:F54)*RANK(B4:AF4,J52:N54)))


¡Õ°}¦C¤½¦¡¡Ö¿é¤Jªk
¿é¤J©Î­×§ï¤½¦¡§¹¦¨«á¡A´å¼Ð¯d¦b¡e½s¿è¦C¡f¡A¥ý¤£­n«öEnter¡A
¦P®É«ö¦í¡eShift + Ctrl¡f¤£©ñ¡A¦A«ö¡eEnter¡f¡C

­Y¹ïRANK¥ÎªkÁÙ¤£¼ô±x¡A«Øij¥ý°µ°Ñ¦Ò¡ACOUNTIF¸û¦n²z¸Ñ¡ã¡ã

¥t ¡G
¢°¡DB52 ¤½¦¡¶·§ï¬°¡G=IF(¿é¤J°Ï!B4="","",¿é¤J°Ï!B4)¡A¨ä¥¦®æ¥çµM¡A§_«h¡eªÅ®æ¤Î¢¯­È¡f·|»~§P¬°¬Û¦P¡I
¢±¡D¿é¤J°Ï¸¹½X¤£¥i­«ÂÐ

TOP

¥»©«³Ì«á¥Ñ LITTLEFAT7048 ©ó 2015-11-22 22:23 ½s¿è

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

ÁÂÁ­ã¤j¡A¥Ø«e¬O¦¨¥\¤F¡A

³o­Ó¤½¦¡¬Ý±oÀ´¡G¡]¤ñ¸û²³æ!!¡A³o¨â­Ó³£¦¨¥\¡^
=SUMPRODUCT(COUNTIF($B$56:$F$58,B7:AF7)*COUNTIF($J$56:$N$58,B8:AF8))
{=SUM(COUNTIF($B$56:$F$58,B3:AF3)*COUNTIF($J$56:$N$58,B4:AF4))}

¤£¹LÁÙ¦³¤@­Ó¤p°ÝÃD¡A
¤p§Ì¤w¸g§âB56:F58ªº¿é¤J¤FIFªº¨º­Ó¤½¦¡¡A¡]¥»¨ÓB52ªº©¹¤U²¾¡A¦]¬°ªÅ¥X¨Óªº­n¯dµ¹­pºâªºµª®×¡^
³o­Ó³¡¥÷¨S°ÝÃD¡A
¦ý¬O¤G¤ë´N¦³°ÝÃD¤F¡A
¦]¬°¤G¤ë¥u¦³28¤Ñ¡A©ÎªÌ¬O»¡¥u­n¬O¤£¬O31¤Ñªº­pºâ³£·|¦³ÂI°ÝÃD¡A
­n§â¨S¦³31¤ÑªºÀx¦s®æ¤º®e¥þ³¡§R°£´N·|¦¨¥\¤F¡C

¤W­z¤G¤ëªº­pºâ¡Aª½±µ±q¤@¤ë©¹¤U©Ôªº¡A
³o­Óµª®×ºâ¥X¨Ó·|¬O401¡A¦pªG§R±¼29¤éÀx¦s®æ¤º®e¡A·|Åܦ¨269¡A
¦A§R30¤é±o137¡A¦A§R31¤é±o5¡A¡]µ¥©ó§R¤@¤é´N·|´î132¡^
5¬O¥¿¸Ñ¡C¦]¬°³o­Ó¤ë­è¦n­pºâªº¬O5¤Ñ¡C

±µµÛ¤p§Ì´N§â©³¤UªºIFªº¥h±¼¡Aª½±µ¬O°Ñ·Óªº¡]=¿é¤J°Ï!B4¡^
µM«á´N¦¨¥\¤F¡A°ÝÃD¥X¦b­þ¸Ì¥Ø«e¤£½T©w¡A¤£¹Lµª®×¬O¥¿½Tªº¡C
¦ý¬ORANK¨ç¼Æªº¨º2­Ó¦n¹³¦³ÂI¿ù»~¡A
¨SÃö«Y¡A¨º­Ó¦³ÂIÃø¡AºCÂI¦A¬ã¨s¦n¤F¡A¥ý¬ã¨s·|ªº...
³ø§i§¹²¦¡I¥H¤W¬O¤p§Ì´ú¸Õªºµ²ªG¡I

ÁÂÁ­ã¤j«ü¾É¡I

TOP

        ÀR«ä¦Û¦b : ¶¢¤HµL¼Ö½ì¡A¦£¤HµL¬O«D¡C
ªð¦^¦Cªí ¤W¤@¥DÃD