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

½Ð°Ýsumproduct¦p¦ó¤£¥]§tÁôÂÃ

½Ð°Ýsumproduct¦p¦ó¤£¥]§tÁôÂÃ

½Ð°Ý§Ú¦³«Ü¦h¤Ñ°Ó«~ªº¸ê®Æ
AÄæ¤é´Á BÄæ°Ó«~ CÄæ¼Æ¶q DÄæ»ù®æ
§Ú¥ý¨Ï¥Î¦Û°Ê¿z¿ï¿ï¨ú¬Y¤@¤é
·|¥X²{¬Y¤é©Ò¦³ªº°Ó«~¡B¼Æ¶q¡B»ù®æ
§Ú¨Ï¥Îsumproduct¿ï¨ú¼Æ¶q©M»ù®æ
¦ý¬O¥L·|±NÁôÂ꺼Ʀr¤]¦C¤J­pºâ
­n¦p¦ó¥uºâÅã¥Üªí®æªº­¼¿n¥[Á`??
³Â·Ð¤j¤j«ü¾É
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

¥»©«³Ì«á¥Ñ kimbal ©ó 2010-8-14 22:02 ½s¿è

¦^´_ 1# amu1129

sumproduct+subtotal
F11¤½¦¡: °²³]±ø¥ó¦b F8©MF9
=SUMPRODUCT(--(B2:B6=F8),--(A2:A6=F9),SUBTOTAL(109,OFFSET(C2:C6,ROW(C2:C6)-ROW(C2),0,1)),(D2:D6))

t20100814sumproductsubtotal.zip (4.71 KB)

¦ý¬O...ÁÙ¬O«Øij¦h¥Î¼Ï¯Ãªí
À´±oµo°Ý,µª®×´N·|¦b¨ä¤¤

¤µ¤éの¤@¬íは  ©ú¤éにない
http://kimbalko-chi.blogspot.com
http://kimbalko.blogspot.com

TOP

ÁÂÁª©¥D~ª©¥D¦n¼F®`
¤£¹L§Ú¹ï³o¨ç¼ÆÁÙ¤£¬O«ÜÀ´
subtotal(109)¬O©¿²¤ÁôÂ꺥[Á`
¦Ósumproductªº­¼¿nÀ³¸Ó¬O¥H³Ì«á¨â­Ó½d³òºâªº§a
³o¼Ë¤£´NÅܦ¨CÄæÁ`©M x DÄæ?
ÁÙ¦³¤p§Ì¹ïoffsetªº¨ç¼Æ¤£¬O«ÜÀ´
¥i¥H½Ðª©¥Dµy·LÁ¿¸Ñ¶Ü

¥t¥~½Ð°Ý³o­Ó¤½¦¡¤@©w»Ý­n°²³]±ø¥ó¶Ü
¤£¯àª½±µ¹³sumproduct¥Î·Æ¹«¿ï¨ú½d³ò§Y¥i¶Ü
·Ð½Ð¬°¤p§Ì¤£¤~«üÂI¤@¤U¡A·PÁÂ
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¥»©«³Ì«á¥Ñ kimbal ©ó 2010-8-15 11:13 ½s¿è

¦^´_ 3# amu1129

·Q¤À§OÄæ¦ìªº¸Ü,³o¼Ë´N¥i¥H¤F
c:
    =SUMPRODUCT(--(B2:B6=F8),--(A2:A6=F9),SUBTOTAL(109,OFFSET(C2:C6,ROW(C2:C6)-ROW(C2),0,1)))
d:
=SUMPRODUCT(--(B2:B6=F8),--(A2:A6=F9),SUBTOTAL(109,OFFSET(D2:D6,ROW(D2:D6)-ROW(D2),0,1)))

³o­Ó­n¥Î¤WOFFSET¤F, ª½±µ¥ÎSUBTOTAL(109,D2:D6) ·|Åܦ¨¹ê¼Æ,¤£¬O°}¦C
À´±oµo°Ý,µª®×´N·|¦b¨ä¤¤

¤µ¤éの¤@¬íは  ©ú¤éにない
http://kimbalko-chi.blogspot.com
http://kimbalko.blogspot.com

TOP

ÁÂÁÂ~§Ú¤j­P¤W¤F¸Ñ¥Îªk¤F
¥u¬OÁÙ¦³¤@­Ó¦a¤è¤£¤ÓÀ´
ROW(C2:C6)-ROW(C2)
¬°¤°»ò­n´îROW(2)°Ú?
¦ý¬O§Ú±N´îROW(2)®³±¼ªº¸Ü
¼Æ­È¤S¤£¹ï
¥i¥H³Â·Ðª©¥D§i¶D§Ú³o­Ó°Ê§@¬O·F¹À¥Îªº¶Ü?
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¦^´_ 5# amu1129

¤½¦¡¥i¥H¼g¦¨³o­Ó¼Ë¤l:
=SUMPRODUCT(--(B2:B6=F8),--(A2:A6=F9),SUBTOTAL(109,OFFSET(C2,ROW(C2:C6)-2,0)))

ÁÙ­ì°ò¥»¨B, «e¥b¨Bªº²z¸Ñ¶Ü?
=SUMPRODUCT(--(B2:B6=F8),--(A2:A6=F9))

³o¥y¥i¥H¸ÑÄÀ¬°
   --(B2=F8) * --(A2=F9)
+ --(B3=F8) * --(A3=F9)
+ ....
+ --(B6=F8) * --(A6=F9)

-- ¥Î¨Ó§â§PÂ_±ø¥ó´«¦¨1 / 0, 1´N¬OB2=F8 ªº·N«ä


¥[¤F " ,SUBTOTAL(109,OFFSET(C2,ROW(C2:C6)-2,0)) " «á

   --(B2=F8) * --(A2=F9) * SUBTOTAL(109,C2)
+--(B3=F8) * --(A3=F9) * SUBTOTAL(109,C3)
...
+--(B6=F8) * --(A6=F9) * SUBTOTAL(109,C6)

¦^¤W¤@¨B
   --(B2=F8) * --(A2=F9) * SUBTOTAL(109,OFFSET(C2,0,0))
+--(B3=F8) * --(A3=F9) * SUBTOTAL(109,OFFSET(C2,1,0))
...
+--(B6=F8) * --(A6=F9) * SUBTOTAL(109,OFFSET(C2,4,0))

¦A¦^¤W¤@¨B
   --(B2=F8) * --(A2=F9) * SUBTOTAL(109,OFFSET(C2,ROW(C2)-2,0))
+--(B3=F8) * --(A3=F9) * SUBTOTAL(109,OFFSET(C2,ROW(C3)-2,0))
...
+--(B6=F8) * --(A6=F9) * SUBTOTAL(109,OFFSET(C2,ROW(C6)-2,0))
À´±oµo°Ý,µª®×´N·|¦b¨ä¤¤

¤µ¤éの¤@¬íは  ©ú¤éにない
http://kimbalko-chi.blogspot.com
http://kimbalko.blogspot.com

TOP

¦^´_ 6# kimbal

ÁÂÁª©¥D~
¤p§Ì¤j­P¤W³£À´¤F
¦ý¤p§Ì¸ê½è·M¶w
¹ïROW(C2:C6)-2ÁÙ¬O¤£À´­C
¥i¥H°w¹ïROW(C2:C6)-2Á¿¸Ñ¶Ü
§Úª¾¹DROW(C2:C6)¬O°}¦C
¦ý-2³o¨B¹ê¦b¤£À´
³Â·Ð½Ð¬°¤p§Ì¦A«üÂI¤@¤U¡AÁÂÁÂ
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¦^´_ 7# amu1129

ROW(C2:C6)-2
¦^¶Ç¥X¨Óªº¬O 2-2,3-2,4-2,5-2,6-2 §Y 0,1,2,3,4
°t¦Xoffset¨Ó¥Î,
OFFSET(C2,0,0) ¦^¶Ç C2 ¦Û¤v
OFFSET(C2,1,0) ¦^¶Ç C3
OFFSET(C2,2,0) ¦^¶Ç C4
OFFSET(C2,3,0) ¦^¶Ç C5
OFFSET(C2,4,0) ¦^¶Ç C6
À´±oµo°Ý,µª®×´N·|¦b¨ä¤¤

¤µ¤éの¤@¬íは  ©ú¤éにない
http://kimbalko-chi.blogspot.com
http://kimbalko.blogspot.com

TOP

¦^´_ 8# kimbal

¤S¾Ç·|¤@¼Ë¤F ÁÂÁª©¥D!
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¦n²`¶ø®@!!§Úªº¥\¤O¤Ó²L¡A§Ú­n¦b¥[±j¸ò¦U¦ì¥ý¶i¾Ç²ß¡A¬Ý¤F¤@ª¾¥b¸Ñ¡A«¢«¢
marklu

TOP

        ÀR«ä¦Û¦b : §g¤l¬°¥Ø¼Ð¡A¤p¤H¬°¥Øªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD