[µo°Ý] Ãö©ó±Æ¦W(§«ô¤»­n¥Î¤F>"<)

kevenchin

¤£¦n·N«ä ½Ð±Ð¦U¦ì¹F¤H
  
¦b±Æ¦W¤W¦P®É²Å¦X¥H¤U¤TÂI­n¯À
¨ç¼Æ¸Ó¦p¦ó¼g
  
1.80¤À¥H¤W ¤~¦³¦W¦¸

2.¦pªG¦³4¤H°ÑÁÉ «h¨ú2­Ó¦W¦¸(2­Ó¦W¦¸¤£¬O2­Ó¤H)
5¤H°ÑÁɨú3­Ó¦W¦¸
9¤H°ÑÁɨú5­Ó¦W¦¸.....¥H¦¹Ãþ±À
¨Ò¦p 3­Ó¤H°ÑÁÉ «h¨ú2­Ó¦W¦¸ 1-2
6­Ó¤H°ÑÁÉ «h¨ú3­Ó¦W¦¸ 1-2-3
  
3.¹J¨ì¦P¤À®É ¨Ò¦p¦³¨â­Ó²Ä1¦W®É ¤£­n¸õ¹L¥h
¨Ò¦p 11­Ó¤H°ÑÁÉ ¦³¨â­Ó²Ä1¦W®É
¤£­n¬O 1-1-3-4-5-6 (2¤£¨£¤F)
¦Ó¬O­n 1-1-2-3-4-5-6 (2¦³¤F ¦Ó¥B­«½Æªº²Ä1¦W¨Ã¨S¦³­é¹Ü±¼²Ä6¦Wªº±o¼ú)

kevenchin

¹ï¤F
³o¬O§Ú¥Ø«eºô¸ô¤W¥|³B§ä§äªºµ²ªG
¦ý¬OµLªk§¹¥þ²Å¦X¤W­z ¤TÂI­n¯À
https://drive.google.com/file/d/0BzOjqeEr9rhvS09rSG5kOXpmX28/view?usp=sharing

Hsieh

¦^´_ 1# kevenchin

   ¨ú±o¦W¦¸ªº³W«h¬Ý¤£¥X¨Ó¡AA4¥H¿é¤J¤è¦¡

C2=IF(ISERROR(MATCH(B2,$C$1:C1,)),B2,"")
D2=IF(B2>79,IF(RANK(B2,$C$2:$C$28)>$A$4,"",RANK(B2,$C$2:$C$28)),"")
¦V¤U½Æ»s

¤U¸ü (11.04 KB)

2016-3-21 12:16

hcm19522

http://blog.xuite.net/hcm19522/twblog/393599677

ML089

i2 =IF(H2<80,"",TEXT(1+COUNT(0/FREQUENCY(IF(H$2:H$99>H2,H$2:H$99),H$2:H$99)),"[<="&LOOKUP(COUNT(H$2:H$99),{4,5,9},{2,3,5})&"];"))
°}¦C¤½¦¡¿é¤J¡A¥ý«öctrl+shift¤£©ñ¡A¦b«öenter ¥H¤TÁä»ô«ö¤è¦¡¿é¤J¤½¦¡

±ø¥ó¤@¡B80¤À¥H¤W ¤~¦³¦W¦¸
¤½¦¡: =IF(H2<80,"", ...)

±ø¥ó¤G¡B¦pªG¦³4¤H°ÑÁÉ «h¨ú2­Ó¦W¦¸(2­Ó¦W¦¸¤£¬O2­Ó¤H)
5¤H°ÑÁɨú3­Ó¦W¦¸
9¤H°ÑÁɨú5­Ó¦W¦¸.....¥H¦¹Ãþ±À
¨Ò¦p 3­Ó¤H°ÑÁÉ «h¨ú2­Ó¦W¦¸ 1-2
6­Ó¤H°ÑÁÉ «h¨ú3­Ó¦W¦¸ 1-2-3
¤½¦¡: LOOKUP(COUNT(H$2:H$99),{4,5,9},{2,3,5})

±ø¥ó¤T¡B¹J¨ì¦P¤À®É ¨Ò¦p¦³¨â­Ó²Ä1¦W®É ¤£­n¸õ¹L¥h
¨Ò¦p 11­Ó¤H°ÑÁÉ ¦³¨â­Ó²Ä1¦W®É
¤£­n¬O 1-1-3-4-5-6 (2¤£¨£¤F)
¦Ó¬O­n 1-1-2-3-4-5-6 (2¦³¤F ¦Ó¥B­«½Æªº²Ä1¦W¨Ã¨S¦³­é¹Ü±¼²Ä6¦Wªº±o¼ú)
¤½¦¡: TEXT(1+COUNT(0/FREQUENCY(IF(H$2:H$99>H2,H$2:H$99),H$2:H$99)),"[<="& ¨ú´X¦W &"];"))

kevenchin

·PÁ¦U¦ì¤j¤jªºÀ°¦£
  
¤£¹L ML089 ¤j¤j
½Ð°Ý ¬°¤°»ò ¦pªG ¬Y¤@²Õ ¥u¦³ 1­Ó 2­Ó ©Î¬O 3­Ó¤H
¤]´N¬O 4­Ó¥H¤W ¤~¥i¥H¥¿±`¹B§@©O

kevenchin

=IF(H2<80,"",TEXT(1+COUNT(0/FREQUENCY(IF(H$2:H$99>H2,H$2:H$99),H$2:H$99)),"[<="&LOOKUP(COUNT(H$2:H$99),{1,2,3,4,5,6,7,8,9,10},{1,1,2,2,3,3,4,4,5,5})&"];"))
  
¥J²Ó¬Ý¤F ML089 ¤j¤j ¯S§O¦b¤U¤è¨C¤@­Ó§Ú»Ý­nªº±ø¥ó¸Ô²Óªºµù¸Ñ
  
¨þ¨þ ¾Ç¨ìªF¦è¤F ¾Ç¨ìªF¦è¤F ^^
  
·P®¦~·P®¦~·P®¦.....orz

kevenchin

=IF(I2=1,"²Ä¤@¦W",IF(I2=2,"²Ä¤G¦W",IF(I2=3,"²Ä¤T¦W",IF(I2=4,"²Ä¥|¦W",IF(I2=5,"²Ä¤­¦W","")))))
¥»¨Ó³o­Ó ¦b
I2¬O =IF(H2>=80,IF(RANK(H2,$H$2:$H$11)<=5,RANK(H2,$H$2:$H$11)),"")
ªº®É­Ô ¬O¥¿±`ªº
²{¦b«o¥¢®Ä¤F
  
¦]¬°¦³®É­Ô§Q¥ÎWord¦X¨Ö¦C¦L ¦L¼úª¬ ªº®É­Ô
»Ý­n¥Î¨ì¤¤¤å¦W¦¸²Ä¤@¦W ²Ä¤G¦W ....... ²Ä¤­¦W

ML089

¦^´_ 8# kevenchin


    Text¨ç¼Æ¦^¶Çªº­È¬O¤å¦r¡A"1","2","3",
±N§A­ì¨Óªº¤½¦¡¤¤ªº1,2,3¼Æ¦r§ï¬°¤å¦r"1","2","3"§Y¥i¡C

§Ú¤½¦¡¤¤ªºText¨ç¼Æ¤]¥iª½±µ±N1,2,3§ï¬°¿é¥X¬°"²Ä¤@¦W","²Ä¤G¦W","²Ä¤T¦W"
§Ú¨ì¤½¥q¬d¤@¤U¦A¸Éµ¹§A¡C

½d¨Ò
=TEXT(ROW(),"[DBnum1]²Ä0¦W;")

±N¤½¦¡¤¤TEXT¨ç¼Æ¥~¥[¥t¤@­ÓTEXT¨ç¼Æ¦p¤U
=IF(H2<80,"",TEXT(TEXT(..., ...),"[DBnum1]²Ä0¦W"))

ML089

=LOOKUP(COUNT(H$2:H$99),{1,2,3,4,5,6,7,8,9,10},{1,1,2,2,3,3,4,4,5,5})       
¤]¥i¥H²¤Æ¬°
=LOOKUP(COUNT(H$2:H$99),{1,3,5,7,9},{1,2,3,4,5})