¥­§¡­È

myleoyes

¦U¦ì«e½ú§A­Ì¦n!
         «e½ú!°ÝÃD¦pªþÀÉ®×»¡©ú
         A18=IF(A8=0,"",A8/COUNTIF(B1:AI1,"®tÃB"))
         ½Ðª¾¹Dªº«e½ú,¤£§[½ç±ÐÁÂÁ¦A¤T!!

kimbal

¦U¦ì«e½ú§A­Ì¦n!
         «e½ú!°ÝÃD¦pªþÀÉ®×»¡©ú
         A18=IF(A8=0,"",A8/COUNTIF(B1:AI1,"®tÃB"))
...
myleoyes µoªí©ó 2010-8-8 08:38

    ¥i¥H»¡¤@¤Ua4/a6/a8¬O¦b°µ¤°»ò¶Ü?
a11 ¬O¤£¬O§äb,e,h...ªº¥­§¡­È (®tÃB·í¦¨1?) ?
a14 ¬O¤£¬O§äc,f,i...ªº¥­§¡­È?
a18 ¬O¤£¬O§äd,g,j...ªº¥­§¡­È?

luhpro

¸gÆ[¹î§Aªº
A4 = SUMPRODUCT(0,8,0,0,6,0,0,0,0,...})
¥ç§Y±N C1,F1,I1,... ¥[Á`  (¦]¬° "®tÃB" ¥u·|¦bB1,E1,H1...²£¥Í,©Ò¥H¦bA4À³µL§@¥Î)
A6 = SUMPRODUCT({0,0,2,0,0,2,0,0,0,...})
¥ç§Y±N D1,G1,J1,... ¥[Á` (²z¥Ñ¦P¤W)

©Ò¥H§Ú²q§A­nªº¬O©³¤Uªº¤½¦¡
A11=A4/(COUNTA($B$1IV$1)/3)
A14=A6/(COUNTA($B$1IV$1)/3)
¥t¥~¦]¬° A8=A4-A6 ¥ç§Y¬°¨â­Ó¥[Á`¼Æ¦rªº®tÃB,
¦ü¥G»P "®tÃB" ªº­Ó¼ÆµLÃö,
³o¸Ì¤£¤Ó²M·¡§A¥Î COUNTIF(B1:AI1,"®tÃB") ·í¤À¥Àªº­ì¦].

myleoyes

¦^´_ 2# kimbal
kimbal«e½ú§A¦n!
       «e½ú!ÁÂÁÂ!
       A4¬Oc,f,i...ªºÁ`©M
       ©Ò¥HA11=A4/8¥­§¡­È¬O6.25
       A6¬O§äd,g,j...ªºÁ`©M
       ©Ò¥HA14=A6/8¥­§¡­È¬O3
       A8¬Ob,e,h...ªºÁ`©M
       §â"®tÃB"·í¦¨0­pºâ
       ©Ò¥HA17=A8/7¥­§¡­È¬O3.43
       ¥H½d¨ÒÀÉA17¨Ó»¡¤p§Ì¥Î¦¹¤½¦¡
       A17=IF(A8=0,"",A8/COUNTIF(B1:AI1,"®tÃB"))
       ­pºâ¥X¨Ó¬O4.8,¦ý¹ê»Ú¬O3.43
       ¦]¬°B1:AK1¤§¶¡¦³"®tÃB"ªº¦@¦³12­ÓÀx¦s®æ
       µM¦Ó¦³¼Æ¦rªº¦@¦³7­Ó©Ò¥H24/7=3.43
       ¤p§Ì¥Îªº¤½¦¡§ä¨ìªº¬O5­Ó°£¥X¨Ó·í¬O¿ù
       ½Ð¤£§[½ç±ÐÁÂÁ¦A¤T!!

myleoyes

¦^´_ 4# myleoyes
luhpro«e½ú§A¦n!
¡@¡@¡@¡@ «e½ú!ÁÂÁÂ!
         ³o¸Ì¤£¤Ó²M·¡§A¥Î COUNTIF(B1:AI1,"®tÃB") ·í¤À¥Àªº­ì¦]
         ¦]¬°B1:AK1¤§¶¡¦³"®tÃB"ªºÀx¦s®æ¦@¦³12­Ó
         ¦Ó¥BA8¬Ob,e,h...ªºÁ`©M,©Ò¥HA8/¦³7­Ó¨S¦³®tÃBªºÀx¦s®æ
         ´N¬O¥­§¡­È,¦pªG¤½¦¡§ï¦¨A8/COUNTIF(B1:AI1,"<>®tÃB")
         ¤]¤£¹ïªü!¦]¬°B1:AK1¤§¶¡¨S¦³®tÃBªºÀx¦s®æ¦³31­Ó
         ¦]¬°§ä¤£¨ì¤½¦¡©Ò¥H¤~¨D§U¦U«e½ú
         ½Ð¤£§[½ç±ÐÁÂÁ¦A¤T!!

asimov

¦^´_ 4# myleoyes


¸Õ¸Õ
A11
=IF(A4=0,"",A4/SUMPRODUCT((MOD(COLUMN($B$1:$AK$1),3)=0)*($B$1:$AK$1>0)))
A14
=IF(A6=0,"",A6/SUMPRODUCT((MOD(COLUMN($B$1:$AK$1)-1,3)=0)*($B$1:$AK$1>0)))
A17
=IF(A8=0,"",A8/SUMPRODUCT((MOD(COLUMN($B$1:$AK$1)+1,3)=0)*ISNUMBER($B$1:$AK$1)))

asimov

¥Î¦P¼Ëªº¤½¦¡¥i¥H²¤ÆA4,A6,A8
A4
=SUMPRODUCT((MOD(COLUMN($B$1:$AK$1),3)=0)*ISNUMBER($B$1:$AK$1),$B$1:$AK$1)
A6
=SUMPRODUCT((MOD(COLUMN($B$1:$AK$1)-1,3)=0)*ISNUMBER($B$1:$AK$1),$B$1:$AK$1)
A8
=SUMPRODUCT((MOD(COLUMN($B$1:$AK$1)+1,3)=0)*ISNUMBER($B$1:$AK$1),$B$1:$AK$1)

myleoyes

¦^´_ 7# asimov
asimov«e½ú§A¦n!
¡@¡@¡@¡@ «e½ú!ÁÂÁÂ!¥i¥HÅo!!ÁÂÁ¦A¤T!!

zz5151353

A4 ¤½¦¡
=SUMPRODUCT(N(OFFSET(C1,,(ROW(1:50)-1)*3,,)))
A6 ¤½¦¡
=SUMPRODUCT(N(OFFSET(D1,,(ROW(1:50)-1)*3,,)))
A11 ¤½¦¡
{=AVERAGE(IF(N(OFFSET(C1,,(ROW(1:50)-1)*3,,)),N(OFFSET(C1,,(ROW(1:50)-1)*3,,))))}
A14 ¤½¦¡
{=AVERAGE(IF(N(OFFSET(D1,,(ROW(1:50)-1)*3,,)),N(OFFSET(D1,,(ROW(1:50)-1)*3,,))))}
A17 ¤½¦¡
{=AVERAGE(IF((N(OFFSET(D1,,(ROW(1:50)-1)*3,,)))-(N(OFFSET(C1,,(ROW(1:50)-1)*3,,))),N(OFFSET(C1,,(ROW(1:50)-1)*3,,))-N(OFFSET(D1,,(ROW(1:50)-1)*3,,))))}

myleoyes

¦^´_ 9# zz5151353
zz5151353«e½ú§A¦n!
      «e½ú!!ÁÂÁÂ!¤ï¶Õ!¤@¦£§Ñ¤F¦^ÂÐ
      ©ì¨ì²{¦b¤½¦¡¥¿¬O»Ý¨DÁÂÁ¦A¤T!!