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

[¤À¨É] ¤½¦¡¿é¤Jªº±`¥Î§Þ¥©(·s¤â¥²Åª)

[¤À¨É] ¤½¦¡¿é¤Jªº±`¥Î§Þ¥©(·s¤â¥²Åª)

¥»©«³Ì«á¥Ñ oobird ©ó 2010-10-31 09:47 ½s¿è

¦³¨Ç±`¦b¸ÑÃD¤¤¨Ï¥Îªº§Þ¥©¡A¦ý¤@¦A³Q­«½Æ°Ý¨ì¦¹Ãþ°ÝÃD¡A±N®e©ö³y¦¨¸ê·½®ö¶O
¬Gµo¦¹©«¡A§Æ±æ½×¾Â·s¤â¯à¥ý¹w²ß¡A­Ú§Q¤é«áª¦¤å¯à©ö©ó§l¦¬¤F¸Ñ¡C
¦¹©«§Æ±æ¤j®a¦³¦n§Þ¥©³£¯àµoªí¸ò©«¡A¦ý½Ð¤Åµoªí¨ä¥LµL·N¸q©Î·PÁ¤峹
¦³¦A·Q¨ìªº¶µ¥Ø±N³°Äò¼W¥[

1.°}¦C¤½¦¡¿é¤J¤èªk¬OCtrl+Shift+Enter¤TÁä¤@°_§¹¦¨¿é¤J
2.¤½¦¡¤¤--¥Nªí·N¸q:--´N¸ÑŪ¦¨­t­t±o¥¿´N¬O±N¼Æ¦r«¬ªº¦r¦êÂà´«¦¨¼Æ­È
¦p:M10234­n¨ú±o10234¦¨¬°¼Æ­È¨Ï¥Î¡A¥ý¥HMID©ÎRIGHTµ¥¦r¦ê¨ç¼Æ¨Ó¨ú±o
   =RIGHT("M10234",5)³o¼Ë±o¨ì10234¬O¦r¦ê«¬ºA
   =--RIGHT("M10234",5)³o¼Ë±o¨ì10234¬O¼Æ­È«¬ºA
3.¿é¤J¤½¦¡®Éµ½¥Îf4Áä¤Á´«¦ì§}ªº¬Û¹ï©Îµ´¹ï°Ñ·Ó¡C
¾Ç®üµL²P_¤£®¢¤U°Ý

¥»©«³Ì«á¥Ñ Hsieh ©ó 2010-12-21 10:52 ½s¿è

ª©¥D¤j¤j¡A½Ð°Ý¤U­±¼gªk¤ºªº -- ¬O»P±z©Ò»¡ªº¼Æ­È«¬ºA¤@¼Ë¶Ü¡HÁÙ¦³³o¼Ëªº¼gªk¦³§ó¦nªº¶Ü¡HÁÂÁ¦^ÂÐÅo!!

=IF(I14="","",IF(SUMPRODUCT(--(C14='C:\¥Í²£¤é³ø\[¼Ð·Ç¤u®É(·sª©).xls]¼Ð·Ç¤u®É'!$C$2:$C$3000),--(¥Í²£¤é³ø!I14='C:\¥Í²£¤é³ø\[¼Ð·Ç¤u®É(·sª©).xls]¼Ð·Ç¤u®É'!$E$2:$E$3000))=580,0,INDEX('C:\¥Í²£¤é³ø\[¼Ð·Ç¤u®É(·sª©).xls]¼Ð·Ç¤u®É'!$L$2:$L$3000,SUMPRODUCT(--(C14='C:\¥Í²£¤é³ø\[¼Ð·Ç¤u®É(·sª©).xls]¼Ð·Ç¤u®É'!$C$2:$C$3000),--(¥Í²£¤é³ø!I14='C:\¥Í²£¤é³ø\[¼Ð·Ç¤u®É(·sª©).xls]¼Ð·Ç¤u®É'!$E$2:$E$3000),ROW('C:\¥Í²£¤é³ø\[¼Ð·Ç¤u®É(·sª©).xls]¼Ð·Ç¤u®É'!$L$2:$L$3000))-1,1)))
marklu

TOP

¦^´_ 2# lumark1976


    --¬OÂର¼Æ­È¨S¿ù
¥u¬O¤£ª¾¹D©pªº¸ê®Æ«¬ºA
¤£¯àµ¹§A¥¿½T¦^µª
¦ý¥iªÖ©wªº¬O
§A³o¨Ç±ø¥ó¦¡À³¸Ó¬O­n¦P®É¦¨¥ßªº±¡ªp
¥[Á`¬YÄæ¦ì
À³¸Ó¥i¥H¥Î
sumproduct((±ø¥ó1)*(±ø¥ó2)*(±ø¥ó3),¥[Á`Äæ¦ì)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2010-12-21 21:44 ½s¿è

ª©¤j¡A§Úªº¸ê®Æ¬°¼Æ­È§ÎºA¡A¥B§Ú§ï§¹«á¼gªk¦p¤U¡G(¦ý³o¼Ë«o±a¤£¥X¼Æ­È¥X¨Ó¡A¬O¦]¬°­n3ºØ±ø¥ó¤~¯à¶Ü¡H)

=SUMPRODUCT((B14=[¼Ð·Ç¤u®É·sª©.xls]¼Ð·Ç¤u®É!$B$2:$B$3000)*(F14=[¼Ð·Ç¤u®É·sª©.xls]¼Ð·Ç¤u®É!$D$2:$D$3000),[¼Ð·Ç¤u®É·sª©.xls]¼Ð·Ç¤u®É!$I$2:$I$3000)
marklu

TOP

¦^´_ 4# lumark1976
¥[­Ó¨í©·¬Ý¬Ý

    =SUMPRODUCT(((B14=[¼Ð·Ç¤u®É·sª©.xls]¼Ð·Ç¤u®É!$B$2:$B$3000)*(F14=[¼Ð·Ç¤u®É·sª©.xls]¼Ð·Ç¤u®É!$D$2:$D$3000)),[¼Ð·Ç¤u®É·sª©.xls]¼Ð·Ç¤u®É!$I$2:$I$3000)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP



ª©¥D¤j¤j¡AÁÙ¬O¥X²{¤@¼Ëªº²{¶H¤]( ¸ÓÄæ¦ì¥X²{DIV/0! )¡A¤£ª¾±z¦³¸Ñ¶Ü¡H
marklu

TOP

¼Ð·Ç¤u®É·sª©¬O§_¶}±Ò?
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ lumark1976 ©ó 2010-12-21 23:54 ½s¿è

®¦®¦¡A¶}±Òª¬ºA!!¥i¬O­ì¨Ó¼gªº¤è¦¡¤´µM¨S°ÝÃD

¹Ï¤ù1.JPG (277.18 KB)

¹Ï¤ù1.JPG

marklu

TOP

³o¼Ë¬Ý¤£¥X§Aªº°ÝÃD¥X¦b­þ¸Ì?
¥i§_±NÀɮפW¶Ç¥H¤F¸Ñ¸ê®Æ
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

ª©¥D¤j¤j¡A¤w±Nªþ¥ó¤W¶Ç¡A³Â·Ð±z¤F¡AÁÂÁÂ!!
¥Í²£¤é³ø.rar (21.47 KB)
marklu

TOP

        ÀR«ä¦Û¦b : ¤H¥Í¨S¦³©Ò¦³Åv¡A¥u¦³¥Í©Rªº¨Ï¥ÎÅv¡C
ªð¦^¦Cªí ¤W¤@¥DÃD