[µ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¼ú)

ML089

¦^´_ 11# kevenchin


J2 =IF(I2="",LOOKUP(H2,{0,60,70,80},{"","¥Òµ¥","Àuµ¥","¯SÀu"}),TEXT(I2,"[DBnum1]²Ä0¦W"))

handmuch

¥Î«Ü¦h»²§UÄæªk!!
¤T±i¹Ï¤ù¬O¦b¤£¦P¤H¼Æª¬ªp¤UªººI¹Ï

¤U¸ü (113.06 KB)

2016-3-24 17:27

¤U¸ü (115.02 KB)

2016-3-24 17:27

¤U¸ü (115.43 KB)

2016-3-24 17:27

kevenchin

·PÁÂ ML089 ¤j¤j
°ò¥»¤W¬P´Á¤»ªº¤ñÁɬOOK°Õ....orz ·P®¦

¦ý¬O ¦³¥t¤@­Ó°ÝÃD·Q½Ð±Ð (¥t¤@­Ó¤ñÁÉ)
°²³]§Ú­n³]©w I ¬O¼Æ¦r¦W¦¸(·íµM¬O¥Î§Aªº´NOK¤F)
¦ý¬O J ¬O­n¤¤¤åµ¥²Ä
¦³¦W¦¸ªº ²Ä¤@¦W.²Ä¤G¦W......
¨S¦³¦W¦¸ªº
80¤À¥H¤W ¯SÀu
70¤À¥H¤W Àuµ¥
60¤À¥H¤W ¥Òµ¥
¨º J À³¸Ó­n«ç»ò¼g©O

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})

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"))

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

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

·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

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 &"];"))