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

[µo°Ý] ²Ä´X¤j­Èªº®æ¦¡¤Æ³]©w

[µo°Ý] ²Ä´X¤j­Èªº®æ¦¡¤Æ³]©w

²Ä´X¤j­Èªº®æ¦¡¤Æ³]©w.png
2018-4-27 19:03


EXCELªºª©¥»¬°MS2003

¤å¦r»¡©ú¡J
³Ì¤j¼ÆªºÀx¦s®æ¼Ð¥Ü¶À¦â
¦¸¤j¼ÆªºÀx¦s®æ¼Ð¥Üºñ¦â
²Ä¤T¤j¼ÆªºÀx¦s®æ¼Ð¥Ü¯»ÂŦâ

¦P¼Ë±Æ¦Wªº¦P¼ÆÀx¦s®æ¥i¥H­«½Æ¼Ð¥Ü¬Û¦PÃC¦â~§Y¥i¥H¦³1­Ó¥H¤Wªº³Ì¤j¼Æ©Î¦¸¤j¼Æ©Î²Ä¤T¤j¼Æ¡C

½Ð±Ð¡J
³]©w®æ¦¡¤Æªº±ø¥ó1~3¤½¦¡¡HÁÂÁ¡I

¦^´_ 1# papaya
°Ñ¦Ò¬Ý¬Ý
²Ä1¤j
  1. =SUMPRODUCT(($A$1:$A10>A1)/(COUNTIF($A$1:$A10,$A$1:$A10)+(COUNTIF($A$1:$A10,$A$1:$A10)=0)))+1=1
½Æ»s¥N½X
²Ä2¤j
  1. =SUMPRODUCT(($A$1:$A10>A1)/(COUNTIF($A$1:$A10,$A$1:$A10)+(COUNTIF($A$1:$A10,$A$1:$A10)=0)))+1=2
½Æ»s¥N½X
²Ä3¤j
  1. =SUMPRODUCT(($A$1:$A10>A1)/(COUNTIF($A$1:$A10,$A$1:$A10)+(COUNTIF($A$1:$A10,$A$1:$A10)=0)))+1=3
½Æ»s¥N½X
ª`·N¡G·|§â¤å¦r¤]ºâ¤J±Æ¦W

TOP

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

TOP

¦^´_ 2# naruto018
¦^´_ 3# hcm19522

ÁÂÁ¤G¦ì¤j¤jªº¦^ÂЩM¼ö¤ß«ü¾É¡C
´ú¸ÕOK¤F!·P®¦!

TOP

¢°¡D¸ê®Æµ§¼Æ¦h¤Ö¡H¡@¤Ó¦hªº¸Ü¡A¬Y¨Ç¨ç¼Æ·|¥dÀÉ
¢±¡D¼Æ¦r¬O¤â°Ê¿é¤J©Î¤½¦¡¡A¦³§_§t""©Î¨ä¥¦¤å¦r¡H
¡@
³Ì¦n¤WÀɮ׬ݹê»Ú¼Æ¾Úµ²ºc¡ã¡ã
¡@
¥ý¸Õ¡G
=LARGE(IF(FREQUENCY(A:A,--A$1:A$99),A$1:A$99),1)=A1¡@©Î¥Î¡@=MAX(A:A)=A1
=LARGE(IF(FREQUENCY(A:A,--A$1:A$99),A$1:A$99),2)=A1¡@©Î¥Î¡@=SMALL(A:A,FREQUENCY(A:A,MAX(A:A)-1))=A1
=LARGE(IF(FREQUENCY(A:A,--A$1:A$99),A$1:A$99),3)=A1

TOP

¥»©«³Ì«á¥Ñ papaya ©ó 2018-4-30 08:11 ½s¿è

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

¤£¦n·N«ä¡A¨t²Î¨S¦³"´£¿ô"¡A­è­è¤~¬Ý¨ì¡C

¦]±zªº¼f·V·r°u¸Ñµª·Ç½T©ÊªººA«×¡A¤p§Ì·Pı²zÀ³¸Ô²Ó¦^ÂЭI´º¸ê®Æ¡A¦³·Ðº¾¦h¨¥¤§³B¡A©|½Ð¨£½Ì¡C
¢°¡D¨C¤ëªº¸ê®Æ¦C¼Æ¬ù30-31¦C¡A20~30Ä楪¥k(¨C­Ó·~°È­û1Äæ¡A5~7¤H¬°¤@²Õ¡A¬Ý·~°È­ûªº¥X¶Ô²v)¡C
    ©Ò¥H¦~«×¤]¥u¦³365¦C¡A20Ä楪¥k¡F
    ¥u¬O¥t¶}¥ßªº¦~«×¾P°â¼Æ¶q²£«~±MÄݳøªíªºÁ`¦C¼Æ·|¹F5000¦C¥ª¥k

¢±¡D¤é³øªíªº¼Æ¦r¬O¤â°Ê¿é¤J¡A¶g³øªí¡B¤ë³øªí¡B¦~«×Á`³øªí«h¥H¤½¦¡¥N¥X¡F
    ¥u¦³¼ÐÃD¬°¤å¦r¡A¨ä¾l§¡¬°¼Æ¦r(²£«~§Ç¸¹©M¼Æ¶q)¡F ¦ý·|¦³ªÅ¥ÕªºÀx¦s®æ¡C
    7¤H²Õªº²£«~¾P°â¤é³øªíÀɮ׮榡¡A¤j·§(µo°Ý¥Î¡AµL¼Æ¶qÄæ¡A¨S¦³«Üºë·Ç)¦p
    http://forum.twbts.com/thread-20733-1-1.html

µo°Ý«e³£·|¥ý¤Wºô¬d¸ß¸ê®Æ¡A¦³·j´M¨ì
http://forum.twbts.com/viewthrea ... amp;highlight=LARGE
¤]¬O"LARGE+FREQUENCY"¡F¦ý¤å¤¤´£¨ì¡J¸ê®Æ3000¦C´N¤£¦n¥Î¡A¥²¶·¦A³]"©w¸q¦WºÙ"¡A
ı±o³Â·Ð¡A©Ò¥H¤W¨Óµo°Ý¡A§Æ±æ¯à§ä¨ì§ó¨Îªºµª®×¡C

±zªº¸Ñµª¤½¦¡°w¹ï¯S©Ê¦U¦³©Ò¥Î¡A¤p§Ì±Ä¥Î
=MAX(A:A)=A1
=SMALL(A:A,FREQUENCY(A:A,MAX(A:A)-1))=A1
=SMALL(A:A,FREQUENCY(A:A,MAX(A:A)-2))=A1
¨ú¨ä¯Â¼Æ­È©M¤½¦¡­È§¡¥i¥Î¡A¨Ã¥i²¤¹LªÅ¥ÕÀx¦s®æªºÀuÂI¡A
¤×¨ä¬O¥iÂI¿ï"¾ãÄæ"¡A¤£»Ý­n©Ô­Ó­n©R¥h¿ï¨úÀx¦s®æ½d³òªº¯S©Ê¡C

«D±`·PÁ±zªº¼ö¤ß¨ó§U©M­@¤ß«ü¾É!·P®¦!

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2018-4-30 12:37 ½s¿è

¦^´_ 6# papaya


=MAX(A:A)=A1
=SMALL(A:A,FREQUENCY(A:A,MAX(A:A)-1))=A1
=SMALL(A:A,FREQUENCY(A:A,MAX(A:A)-2))=A1  

²Ä¤T¤j¨º­Ó¤½¦¡¬O¿ùªº,
¦pªG³Ì¤j¼Æ»P²Ä¤T¤j®t¼Æ¤£¬O2, ¦p 99 95 90 88 84 ³o90´N§ì¤£¨ì

ÁÙ¦³, ³o¨Ç¤½¦¡¥u¹ï[¾ã¼Æ]ºÞ¥Î~~

TOP

¥»©«³Ì«á¥Ñ papaya ©ó 2018-4-30 14:39 ½s¿è

¦^´_ 7# ­ã´£³¡ªL
¹ï¾¾!¨S¦³ª`·N¨ì^^"
¨º³Ì¤j©M²Ä¤G¤jªº®t¼Æ<>1®É¡A²Ä¤G¤jªº¤½¦¡¤£¤]¬O¤£¯à¥Î¡C

¥HAÄæ¥u¦³²Ä1¦C¼ÐÃD¬°¤å¦r¡A¨ä¾l¬°§t¤½¦¡ªº¾ã¼Æ­È¡A¦C¼Æ¬°5000¦C¥ª¥k¬°­I´º¸ê®Æ¡A
¨ä³Ì¤j­È®æ¦¡¤Æ³]©w¤½¦¡=MAX(A:A)=A1
¨ä²Ä¤G¤j©M²Ä¤T¤jªº®æ¦¡¤Æ³]©w¤½¦¡?
ÁÂÁ±z!

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2018-4-30 17:28 ½s¿è

¦^´_ 8# papaya

²Ä¤T¤j:
=SMALL(A:A,FREQUENCY(A:A,INDEX(SMALL(A:A,FREQUENCY(A:A,MAX(A:A)-1))-1,1)))
  
²Ä1¤Î2·ÓÂÂ

TOP

¥»©«³Ì«á¥Ñ papaya ©ó 2018-4-30 19:39 ½s¿è

¦^´_ 9# ­ã´£³¡ªL
¤½¦¡º|¤F=A1¡A¦³¦Û¦æ¸É¤W¡C

´ú¸Õ«á¸Û¦p»Ý¨D!
·PÁ±z¤@¦Aªº¼ö¤ß¨ó§U©M­@¤ß«ü¾É¡C
·P®¦¦A·P®¦!

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¤Hªº§Ö¼Ö¡D¤£¬O¦]¬°¥L¾Ö¦³±o¦h¡A¦Ó¬O¦]¬°¥L­p¸û±o¤Ö¡C
ªð¦^¦Cªí ¤W¤@¥DÃD