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

¤£¦P¦Cª÷ÃB¬Û­¼¥[Á`°ÝÃD

¤£¦P¦Cª÷ÃB¬Û­¼¥[Á`°ÝÃD



½Ð°Ý¦p¦ó¥H¤½¦¡²Î­pDÄæ»PFÄæª÷ÃB
»¡©ú:
CÄæ»PEÄæ²Î­p¼Æ¾Ú¡A¨Ó¦Û¡u¼Æ¾Ú¨Ó·½¡v¹ïÀ³²£«~Äæ¦C¼Æ¦r¥[Á`¡A¨Ò¦pC5=SUM(¼Æ¾Ú¨Ó·½!E5:G6)¡BD6=SUM(¼Æ¾Ú¨Ó·½!E7:G9)
DÄæ»PFÄæ²Î­pª÷ÃB¡A«h¨Ó¦Û¡u¼Æ¾Ú¨Ó·½¡v¹ïÀ³²£«~Äæ¦C¼Æ¦r¥[Á`¡A¦A¬Û­¼¡u¼Æ¾Ú¨Ó·½¡vDÄæª÷ÃB«á¥[Á`

¥Ø«e¹J¨ì°ÝÃD¦p¤U:
C²£«~©ó¡u¼Æ¾Ú¨Ó·½¡vD5:D6ª÷ÃB§¡¬°9999¡A¬GÁ`¼Æ¶q¥i¥H²Î¤@¬Û­¼9999
¦ýD²£«~©ó¡u¼Æ¾Ú¨Ó·½¡vD7¬°16732¡BD8¬°16999¡BD9¬°24499¡A©Ò¥H»Ý­n¤T¦C¼Æ¶q¬Û­¼¹ïÀ³DÄæª÷ÃB«á¦A¥[Á`¡A¤~¯à±o¥XÁ`ª÷ÃB

·Q½Ð°Ý¥H¤W¬O§_¥i¥H¤½¦¡¹F¦¨©O¡H


´ú¸ÕÀÉ.rar (16.73 KB)

Â^¨ú.PNG (45.21 KB)

Â^¨ú.PNG

*¦v¤k¤@ªTµL»~*

¦^´_ 1# msmplay
³oÀ³¸Ó¥Îsumproduct´N¥i¥H¤F§a¡Hex: D7=SUMPRODUCT(¼Æ¾Ú¨Ó·½!D7:D9,¼Æ¾Ú¨Ó·½!E7:E9)

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2016-8-27 20:26 ½s¿è

D3 =SUMPRODUCT(¼Æ¾Ú¨Ó·½!$D$3:$D$55*¼Æ¾Ú¨Ó·½!$E$3:$P$55*
(LOOKUP(ROW($3:$55),ROW($3:$55)/(¼Æ¾Ú¨Ó·½!$A$3:$A$55<>""),¼Æ¾Ú¨Ó·½!$A$3:$A$55)=LOOKUP("¼_",$A$3:$A3))*
(LOOKUP(ROW($3:$55),ROW($3:$55)/(¼Æ¾Ú¨Ó·½!$B$3:$B$55<>""),¼Æ¾Ú¨Ó·½!$B$3:$B$55)=$B3)*
(LOOKUP(COLUMN($E:$P),COLUMN($E:$P)/(¼Æ¾Ú¨Ó·½!$E$1:$P$1<>""),¼Æ¾Ú¨Ó·½!$E$1:$P$1)=C$1))

D3¤½¦¡¤U©Ô¡A¦A½Æ»s¦ÜFÄæ

´ú¸ÕÀÉ.rar (19.78 KB)

{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ hcm19522 ©ó 2016-8-27 11:21 ½s¿è

D3=SUMPRODUCT((LOOKUP(ROW($3:$55),ROW($3:$55)/(¼Æ¾Ú¨Ó·½!B$3:B$55<>""),¼Æ¾Ú¨Ó·½!B$3:B$55)=B3)*(LOOKUP(ROW($3:$55),ROW($3:$55)/(¼Æ¾Ú¨Ó·½!A$3:A$55<>""),¼Æ¾Ú¨Ó·½!A$3:A$55)=LOOKUP(1,0/(A$3:A3<>""),A$3:A3))*¼Æ¾Ú¨Ó·½!D$3:D$55*¼Æ¾Ú¨Ó·½!E$3:G$55)
F3=SUMPRODUCT((LOOKUP(ROW($3:$55),ROW($3:$55)/(¼Æ¾Ú¨Ó·½!B$3:B$55<>""),¼Æ¾Ú¨Ó·½!B$3:B$55)=B3)*(LOOKUP(ROW($3:$55),ROW($3:$55)/(¼Æ¾Ú¨Ó·½!A$3:A$55<>""),¼Æ¾Ú¨Ó·½!A$3:A$55)=LOOKUP(1,0/(A$3:A3<>""),A$3:A3))*¼Æ¾Ú¨Ó·½!D$3:D$55*¼Æ¾Ú¨Ó·½!H$3:P$55)
F3 :½Æ»sD3¤£§t "=" ¨ìF3 ,¥[ "=" ,¦A§ïE$3:G$55-->H$3:P$55

TOP

¦^´_ 4# hcm19522

h¤jÁÂÁ§A~~~~~
*¦v¤k¤@ªTµL»~*

TOP

¦^´_ 5# msmplay
½Ð±Ð¤G°ÝÃD
¤@ .À£ÁYÀɦp¦ó°µ §Ú¸gYAHOO FUNP ³B²z¤U¸ü«ÜºC   ¤G .SINGNATURE ©³¤U¤å¦r ¦p²£¥Í

TOP

        ÀR«ä¦Û¦b : ¨C¤ÑµL©Ò¨Æ¨Æ¡A¬O¤H¥Íªº®ø¶OªÌ¡A¿n·¥¡B¦³¥Î¤~¬O¤H¥Íªº³Ð³yªÌ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD