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

[µo°Ý] ¤Ï±À²Å¦X±ø¥óªº³Ì¤j­È

[µo°Ý] ¤Ï±À²Å¦X±ø¥óªº³Ì¤j­È

¥»©«³Ì«á¥Ñ momo020608 ©ó 2017-6-3 12:56 ½s¿è

½Ð°Ý·Q¤Ï±À ¨D¸Ñ
http://isvincent.pixnet.net/blog/post/39119457-excel-%E7%AC%A6%E5%90%88%E6%A2%9D%E4%BB%B6%E7%9A%84%E6%9C%80%E5%A4%A7%E5%80%BC(max,weekday,%E9%99%A3%E5%88%97)
·Q¤Ï±À G2
986 ­È¡A¬O¥X¦Û©ó­þ­Ó¤H­û¡B¬P´Á¡B¤é´Á©O?

-
=MAX((«~¦W=CG2)*³æ»ù)
¦b «ö¤@¤U Ctrl+Shfit+F3 Áä¡A¤Ä¿ï¡u³»ºÝ¦C¡v¡A©w¸q¦WºÙ¡G «~¦W«á
«~¦Wµ¥©óCG2 ­È¡A¿ï¨ú±ø¥ó½d³ò¤º ³Ì°ª»ù~

¦ý¬O ·Q¨D¸Ñ ³Ì°ª»ù ¨D¥X«á¡A·Qª¾¹D ³Ì°ª»ù ¥X¦Û©ó ­þ¤@µ§¸ê®Æ?
¦pªG ¸ê®Æ³Ì¤j­È ¤£¥u¦³¤@µ§¸ê®Æ©O?

ÀÉ®× ¦pªþ¥ó ³Ì¤j±ø¥ó°ÝÃD01.rar (10.12 KB)

¥t¥~½Ð°Ý ¦³¤H·|ºâ ¥­§¡»ù¶Ü?
¤W¤¤¤U»ù

²Î­p¶µ¥Ø©w¸q¡G
¡]¤@¡^¤W»ù¡G¥H·í¤é¸Ó¹A²£«~Á`¥æ©ö¶q¤¤³Ì°ª»ù®æ¤§20%¡A¥[Åv¥­§¡­pºâ±o¤§¡C
¡]¤G¡^¤U»ù¡G¥H·í¤é¸Ó¹A²£«~Á`¥æ©ö¶q¤¤³Ì§C»ù®æ¤§20%¡A¥[Åv¥­§¡­pºâ±o¤§¡C
¡]¤T¡^¤¤»ù¡G¥H·í¤é¸Ó¹A²£«~Á`¥æ©ö¶q¤¤¦©°£³Ì°ª³Ì§C»ù®æ¦U20%³Ñ¾l¤§60%¡A¥[Åv¥­§¡­pºâ±o¤§¡C

³Ì¤j±ø¥ó°ÝÃD01.rar (10.12 KB)

³Ì¤j±ø¥ó°ÝÃD01.rar (10.12 KB)

³Ì¤j±ø¥ó°ÝÃD01.rar (10.12 KB)

¥»©«³Ì«á¥Ñ KCC ©ó 2017-6-3 15:00 ½s¿è

¦^´_ 1# momo020608
°w¹ï¤W¤U¤¤»ù

»ù°Ï
¶q°Ï

¤W = percentile(»ù°Ï,0.8)
¤U = percentile(»ù°Ï,0.2)

¤W¡B¤U¡B¤¤»ù
sumproduct((»ù°Ï>¤W)*»ù°Ï*¶q°Ï)/sumif(»ù°Ï,">"&¤W,¶q°Ï)
sumproduct((»ù°Ï<¤U)*»ù°Ï*¶q°Ï)/sumif(»ù°Ï,"<"&¤U,¶q°Ï)
sumproduct(((»ù°Ï<¤W)*(»ù°Ï>¤U))*»ù°Ï*¶q°Ï)/sumifs(¶q°Ï,»ù°Ï,"<"&¤W,»ù°Ï">"&¤U)

TOP

K2:L6{=INDEX($A:$A,SMALL(IF(($B$2:$B$44=$H2)*($F$2:$F$44=$I2),ROW(A$2:A$44),4^8),COLUMN(A1)))&""

À³¦³Ãþ¦üÃD http://blog.xuite.net/hcm19522/twblog
google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦^´_ 3# hcm19522


    ÁÂÁÂ~¤Ó¼F®`¤F~

TOP

¦^´_ 2# KCC


    ÁÂÁ ¬ã¨s¬Ý¬Ý

TOP

¦^´_  momo020608
°w¹ï¤W¤U¤¤»ù

»ù°Ï
¶q°Ï

¤W = percentile(»ù°Ï,0.8)
¤U = percentile(»ù°Ï,0.2 ...
KCC µoªí©ó 2017-6-3 14:58


½Ð°Ý ¸Ó¦p¨D¥X ¤W»ù        ¤¤»ù        ¤U»ù
´£¨ÑªþÀÉ¡AÁÂÁ¤j®v§A

°ªÄRµæ.rar (25.12 KB)

TOP

¦^´_ 6# momo020608

©êºp¡A§Ú­ì¨Óªº¦³¸ÑŪ¿ù»~¡A«á¨Ó·Q²Î­pªº¤À¦ì¼Æ·¥¤Ö³o¼Ë¹B¥Î
¥J²Ó¬Ý¤F­pºâ¤èªk¤~·dÀ´¬O¨Ì»ù¥H¶q¨Ó²Ö­p¨Ã¥[Åv
ªþ¤W¤@­Ó¦³¸Ô¦C¤W¤U»ù­pºâªíªºÀɵ¹§A°Ñ¦Ò¡A¤£¹Lºâ¥X¨Ó©M©x¤è¸ê®Æ¦³¤@¨Ç®t²§
°ÝÃD¦b¨º¸Ì§Ú´N¤£ª¾¹D¤F......

°ªÄRµæ2.zip (27.15 KB)

TOP

¦^´_  momo020608

©êºp¡A§Ú­ì¨Óªº¦³¸ÑŪ¿ù»~¡A«á¨Ó·Q²Î­pªº¤À¦ì¼Æ·¥¤Ö³o¼Ë¹B¥Î
¥J²Ó¬Ý¤F­pºâ¤èªk¤~·dÀ´ ...
KCC µoªí©ó 2017-6-4 21:01



    ÁÂÁ ¬ã¨s¬Ý¬Ý

TOP

½Ð°Ý ­Y¬O ¥[¤W ±ø¥ó©O
¤W = percentile(»ù°Ï,0.8)
¤W=PERCENTILE(IF(«~¦W°Ï=³æ¤@±ø¥ó«~¦W,³æ»ù°Ï),0.8)

½Ð°Ý ­Y¬O
¤W¡B¤U¡B¤¤»ù
¤W»ù=sumproduct((»ù°Ï>¤W)*»ù°Ï*¶q°Ï)/sumif(»ù°Ï,">"&¤W,¶q°Ï)

¥[¤W³æ¤@±ø¥ó¡A¸Ó¦p¦ó³]©w?

½d¨Ò ÀH«á¸É¤W ÁÂÁ¤j®a


¦^´_  momo020608
°w¹ï¤W¤U¤¤»ù

»ù°Ï
¶q°Ï

¤W = percentile(»ù°Ï,0.8)
¤U = percentile(»ù°Ï,0.2 ...
KCC µoªí©ó 2017-6-3 14:58

TOP

        ÀR«ä¦Û¦b : ÁÀ¨¥¹³¤@¦·²±¶}ªºÂAªá¡A¥~ªí¬üÄR¡A¥Í©Rµu¼È¡C
ªð¦^¦Cªí ¤W¤@¥DÃD