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

[µo°Ý] ½Ð°ÝÃö©ó²Î­p¤£­«½Æ¶µ¥Øªº­Ó¼Æ"«ü©w±ø¥ó"

¦^´_ 30# starry1314

¬O§_¬O³o¼Ë¡A¨ú²Ä2½X»P²Ä5½X¤ñ¸û
D3 =SUMPRODUCT(1/COUNTIF(rng,rng)*(MMULT((MID(rng,{2,5},1)=MID(D$2,{1,2},1))*1,{1;1})=2))       
¥k©Ô       
       
J3 =SUM(D3:I3)       
K3 =SUMIF(D2:I2,"?V",D3:I3)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 29# ML089

ª©¥D~¤£¦n·N«ä ¦b´£¿ô¤º¦³¬Ý¨ì©p¦^´_~¦ý¤å³¹¨S¦³¥X²{....¨S¦³¬Ý¨ì¤º®e

TOP

¦^´_ 32# starry1314

¨ú²Ä2½X»P²Ä5½X¤ñ¸û
D3 =SUMPRODUCT(1/COUNTIF(rng,rng)*(MMULT((MID(rng,{2,5},1)=MID(D$2,{1,2},1))*1,{1;1})=2))        
¥k©Ô        
        
J3 =SUM(D3:I3)        
K3 =SUMIF(D2:I2,"?V",D3:I3)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 33# ML089

·PÁª©¤j¨¯­W¸Ñ¨M,­èµo²{¦pªG¼Æ¶q°µ¨ì10¦ì¼Æªº¸Ü,²Ä5¦ì¤¸´N³Q²¾¨ì²Ä6¤F,

§ÚÁÙ¬O¦V¥DºÞ«Øij±N¥N¸¹´«±¼¦n¤F, ·PÁ¼ö¤ßÀ°¦£

TOP

¦^´_ 33# ML089


    ªO¤j~¤£¦n·N«ä ¹J¨ì­Ó°ÝÃD

rag=©w¸q¦WºÙ
=SUMPRODUCT(1/COUNTIF(rng,rng))

¦pªGrag½d³ò¤º¦³ªÅ¥Õªº¸Ü¥L¤]·|ºâ¬°¤@­Ó¼Æ¶q,½Ð°Ý¦³¿ìªk¸Ñ¨M¶Ü?

¦p¦A3~300¦C¤§¶¡  
10~15¦C¬OªÅ¥Õªº¸Ü~ <<·|·í¬°¤@­Ó¼Æ¶q

TOP

¦^´_ 35# starry1314


    =SUMPRODUCT(1/COUNTIF(rng,rng)) - COUNTIF(rng," ")
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 36# ML089


    ·PÁÂÀ°¤F¤j¦£~´î¥hªÅ¥Õ¡I!!

TOP

¦^´_ 36# ML089


   ¤j¤j~
=SUMPRODUCT(1/COUNTIF(rng,rng)) - COUNTIF(rng," ")  
¦pªG¬O¨Ï¥Î³sµ²¥X²{ªº­È©O?   ªÅ¥Õ¥¦Åܦ¨0
=SUMPRODUCT(1/COUNTIF(rng,rng)) - COUNTIF(rng,"0") ¦ý¬O¥L·|­pºâ¨ì­«½Æ¼Æ¶q  ¦p¦³¤Q­ÓªÅ¥Õ®æ ¥L´N·|¦©°£Á`¼Æ10 ¨ä¹ê¥u»Ý­n¦©°£1

§ï¥Î
=SUMPRODUCT(1/COUNTIF(rng,rng)) - SUMPRODUCT(1/COUNTIF(rng,"0")) ·|µLªk­pºâ...
¥Ø«e¨Ï¥Î
¦]¥Ø«e10­Ó¤H¤º¥u­n¦³¤@­Ó¤H¬O¤£­n­pºâªº¸Ü´N·|©T©w¥X²{¤Q­ÓªÅ¥Õ­È
=SUMPRODUCT(1/COUNTIF(rng,rng)) - COUNTIF(rng," ")  /10  §Ú¥Ø«e¬O³o¼Ë¨Ï¥Î¥i¥¿½T­pºâ,¦ý·Q½Ð°Ý¦pªG¹J¨ì¤£©T©wªÅ¥Õ®æ©O?

·Qªk¬O¥ÎSUMPRODUCT(1/COUNTIF(rng,rng))  ¨ú¥X¤£­«½Æ­Èªº¼Æ¶q¤è¦¡ ¨Ï¥Î¦bªÅ¥Õ³sµ²Àx¦s®æ¤º,
¤]´N¬O SUMPRODUCT(1/COUNTIF(rng,"0"))
¦ý¦n¹³¤£¯à³o¼Ë¼g?¦]¨S¦³­pºâ­È¥X¨Ó

TOP

¥»©«³Ì«á¥Ñ starry1314 ©ó 2015-6-21 14:17 ½s¿è

¦^´_ 36# ML089

§ï¥Î
    =SUMPRODUCT(1/COUNTIF(rng¦­,rng¦­)) -SUMPRODUCT(1/COUNTIF(rng¦­,"0"))
·|¤ñ¹ê»Ú¼Æ¶q¦h0.85
¥[¤W
=SUMPRODUCT(1/COUNTIF(rng¦­,rng¦­)) -SUMPRODUCT(1/COUNTIF(rng¦­,"0")) -0.85

¦A§â®æ¦¡½Õ¾ã¤£Åã¥Ü³Ñ¾l¦ì¼Æ ¤]¬O¥i¹F¨ì¥Ø¼Ð

TOP

¦^´_ 36# ML089


    ¹J¨ì·s°ÝÃD...
-SUMPRODUCT(1/COUNTIF(rng¦­,"0")) ¦pªG¨S¦³ªÅ¥Õªº³sµ²Àx¦s®æªº¸Ü ,´N·|µLªk­pºâ...
·|¥X²{
#DIV/0

TOP

        ÀR«ä¦Û¦b : ª¾ÃÑ­n¥Î¤ßÅé·|¡A¤~¯àÅܦ¨¦Û¤vªº´¼¼z¡C
ªð¦^¦Cªí ¤W¤@¥DÃD