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

[µo°Ý] ½Ð°Ý½Ñ¦ì¤j¤j, ¤U¦C»Ý¨D, ¦p¦óºë²?

[µo°Ý] ½Ð°Ý½Ñ¦ì¤j¤j, ¤U¦C»Ý¨D, ¦p¦óºë²?

½Ð°Ý½Ñ¦ì¤j¤j, ¤U¦C»Ý¨D, ¦p¦óºë²?

¦p¤U¹Ï¡G

¥X¯Ê¶Ô¬ö¿ýªí¤¤¡G
¡i¥[¯Z1¡j¥Nªí ¥[¯Z1¤p®É¡A¡i¨Æ¯f4¡j¥Nªí ¨Æ°²/¯f°²4¤p®É¡A¨Ì¦¹Ãþ±À¡C

¥Ø«e¤½¦¡¡G
1. J394:V425 ¡÷­pºâ¬Y¤ë¦U¶µ¥X¯Ê¶Ô²Î­p
J394¤½¦¡¡G=COUNTIF(J$41:J$68,$I394)

2. J391:V391¡÷­pºâ¬Y¤ë¤w¥ð¦~¥ð®É¼Æ
J394¤½¦¡¡G=SUMPRODUCT((J394:J401)*($H394:$H401))

3.J392:V392¡÷­pºâ¬Y¤ë¨Æ°²/¯f°²®É¼Æ
4.J393:V393¡÷­pºâ¬Y¤ë¥[¯Z®É¼Æ

§Æ±æ¡GJ391:V393  ªº·s¤½¦¡¡A¯à±N¶À¦â¦C 394:417 ¤½¦¡§R°£«á,
J391:V393 ¤´«O¦³­ì¥\¯à¡A½Ð°Ý ·s¤½¦¡ ¬O¤°»ò?

ªþ¥ó
http://www.mediafire.com/download/3vwt4sn0wi74bx4/%E5%87%BA%E7%BC%BA%E5%8B%A4%E7%B5%B1%E8%A8%88%E8%A1%A8.7z

¦^´_ 1# yen956
¸Ñ¨M¤F!!
¤½¦¡¹ê¦b¤ÓÃø¤F, ·Q¯}ÀY, ÁÙ¬O·Q¤£³q,
³Ì«áÁÙ¬O¥ÎVBA¸Ñ¨M.

TOP

J391  {=SUMPRODUCT(IFERROR((LEFT(J41:J68,2)="¦~¥ð")*RIGHT(J41:J68,1),0)) }
ctrl+shit+enter
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¥»©«³Ì«á¥Ñ yen956 ©ó 2014-5-19 12:27 ½s¿è

¦^´_ 3# softsadwind
ÁÂÁ«ü¾É!!
¥u¬O§Úªº¬O2003, ¹ï IFERROR ¥t¦³ªí¹F¤è¦¡,
¦pªG·Ó§Û, ·|¥X²{ #NAME? ¿ù»~,
§Ú¦³°Ñ¦Ò
http://forum.twbts.com/thread-11647-1-1.html
­×§ï, ¦ý¥\¤O¤Ó§C, §ï¤£¥X¨Ó, ¤£ª¾¤j¤j¬O§_ª¾¹D¦b 2003 ­n¦p¦ó§ï? ·PÁÂ!!

TOP

¦^´_ 4# yen956


    J391=SUMPRODUCT((LEFT(J41:J68,2)="¦~¥ð")*RIGHT(J41:J68,1))
   
     J41:J68 ¥Î0 ´À¥NªÅ®æ...
     ¦pªG©È0 ¤£¦n¬Ý ´N¥Î®æ¦¡¤Æ±ø¥ó¥h³]©w¦rÅéÃC¦â¬°¥Õ¦â
     °Ñ¦Ò¬Ý¬ÝÅo
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

§Ñ¤F¤W¤@½g§a..«¢«¢

{=SUMPRODUCT((LEFT(J41:J68,2)="¦~¥ð")*if(LEN(J41:J68)>2,RIGHT(J41:J68,1),0))}
ctrl+shift + enter
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¦^´_ 6# softsadwind
ÁÂÁ¤j¤j¤£¹½¨ä·Ð¦A¤T«ü±Ð, 6Fªº¤½¦¡, ¹B¦æµL»~, ÁÂÁÂ!!
¦]¤U©Ô¦¡²M³æ¤¤¦³ "¥¼¥´¥d", ¬G¤½¦¡²¤§ï¦p¤U(¥t¥[¸É§UÄæ):
=SUMPRODUCT((LEFT(J$41:J$68,2)=$H391)*IF(ISNUMBER(--RIGHT(J$41:J$68,1)),RIGHT(J$41:J$68,1),0))
(§Ú¥u¦³³Ì²³æªº IF ¤~·|°Õ, «¢«¢!!)

TOP

        ÀR«ä¦Û¦b : Ä@­n¤j¡B§Ó­n°í¡B®ð­n¬X¡B¤ß­n²Ó¡C
ªð¦^¦Cªí ¤W¤@¥DÃD