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

[µo°Ý] ¤p°ÝÃD½Ð±Ð Âà¸m¬Û­¼

[µo°Ý] ¤p°ÝÃD½Ð±Ð Âà¸m¬Û­¼

Dear¤j¤j¡G

¤S¨Ó½Ð±Ð°ÝÃD¡A¦pªþÀÉ¡A¦b·Ð½Ð¦U¦ì¤j¤j«ü±Ð¡CÁÂÁÂ

¦³¤@µ§¸ê®Æ»Ý­¼¤W«Y¼Æ§@¬Û¥[(sumproduct)
¦ý¬O¦]¬°¸ê®Æ±Æ§ÇÃö«Y»ÝÂà¸m
´N¤£ª¾¦p¦ó¼g¤½¦¡¤F
¥t¥~¦³¥i¥H©¹¤U©ì¦²«o¼W¥[Ä檺¤½¦¡¶Ü¡H

¸Ô½Ð°Ñ¾\ªþÀÉ

¦A½Ð«ü±Ð¡AÁÂÁÂ

¤p°ÝÃD.rar (12.06 KB)

¦^´_ 1# jj369963

­pºâsheetªºB3Àx¦s®æ¨Ï¥Î°}¦C¤½¦¡(ctrl+shift+enter)
  1. =SUMPRODUCT(TRANSPOSE(¼Æ­È!$B2:$L2),«Y¼Æ!B$5:B$15)
½Æ»s¥N½X
¤U©Ô¥k©Ô

"©¹¤U©ì¦²«o¼W¥[Äæ"
¬Ý¤£À´

TOP

¦^´_ 2# Bodhidharma


    À³¸Ó¬O«ü­pºâªí®æ¼W¤j¡A¨Ò¦p­ì¥ý¬° 11*11 §ï¬° 25*25
  1. =SUMPRODUCT(TRANSPOSE(¼Æ­È!$B2:$Z2),«Y¼Æ!B$5:B$29)
½Æ»s¥N½X
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 1# jj369963


    ¤W¨â¦¡¿é¤J»Ýª`·N­n¥Î CTRL+SHIFT+ENTER¤TÁä»ô«ö¤è¦¡¿é¤J¤½¦¡¡A
    ¤U¦¡¥Î ENTER¿é¤J´N¥i¡A±N¬O¤@¯ë¿é¤J¤½¦¡¤è¦¡
  1. B3
  2. =MMULT(-TEXT(¼Æ­È!$B2:$Z2,"[<>];;0;!0"),-TEXT(«Y¼Æ!B$5:B$29,"[<>];;0;!0"))
½Æ»s¥N½X
MMULT¬°¯Â¼Æ­È¦æ¦C¬Û­¼¨ç¼Æ¡A¦]¬°¸ê®Æªí¤¤¦³¨Ç¬OªÅ®æ¡A´N»Ý­n¥ÎTEXT¨ç¼Æ±NªÅ®æÂର 0 ¨Ó³B²z
MMULT¨ç¼Æ¬Ý»¡©ú¥i¯à¤£©ö¬ÝÀ´¡A²³æ¨ÓÁ¿ ²Ä1°Ñ¼Æ¶·¬° ¤ô¥­¸ê®Æ ¡A²Ä2°Ñ¼Æ¶·¬° ««ª½¸ê®Æ¡A¥»¨Ò¬°1ºû*1ºû À³¸Ó¤ñ¸û®e©ö¤F¸Ñ¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 4# ML089

ÁÂÁ¤j¤j­Ì¡G

¨ü¯q³\¦h¡A­ì¨ÓÁÙ¦³MMULT¨ç¼Æ¡A¯u¬O·PÁ¡C¨ä¤¤textªºformat_text: "[<>];;0;!0" ¬O¥Nªí¤°»ò·N«ä§r,¬ã¨s¤¤

¦A¦¸·PÁÂ


MMULT(array1,array2)

Array1, array2     ¬°­n¨D­¼¿nªº¨â­Ó°}¦C¡C

µù¸Ñ

Array1 ªºÄæ¼Æ¥²¶·»P array2 ªº¦C¼Æ¬Û¦P¡A¥B¨â­Ó°}¦C¥²¶·¥u¥]§t¼Æ¦r¡C
array1 ©M array2 ¥i¥H¬OÀx¦s®æ½d³ò¡B°}¦C±`¼Æ©Î°Ñ·Ó¡C
MMULT ·|¦bµo¥Í¤U¦C±¡ªp®É¶Ç¦^¿ù»~ #VALUE!¡G
¥ô¦óÀx¦s®æ¬°ªÅ¥Õ©Î¥]§t¤å¦r¡C
array1 ªºÄæ¼Æ¤£µ¥©ó array2 ªº¦C¼Æ¡C
³Ì«á²£¥Íªº°}¦C¡A¨äÀx¦s®æÁ`¼Æµ¥©ó©Î¤j©ó 5,461¡C

TOP

¦^´_ 5# jj369963
>¨ä¤¤textªºformat_text: "[<>];;0;!0" ¬O¥Nªí¤°»ò·N«ä§r

TEXT(¼Æ¦r/¤å¦r¡A®æ¦¡)
®æ¦¡ ="®æ¦¡1=¥¿¼Æ;®æ¦¡2=­t¼Æ;®æ¦¡3=0;®æ¦¡4=¤å¦r"
®æ¦¡="[<>];;0;!0
¡@®æ¦¡1=[<>]¡Aªí¥Ü¿é¥X¥¿¼Æ»P­t¼Æ
¡@®æ¦¡2=µL(¦]¬°­t¼Æ¤w¸g©ó°Ñ¼Æ1³B²z¡A©Ò¥H¬Ù²¤)
¡@®æ¦¡1=0¡A¼Æ­È0®É¨Ï¥Î
¡@®æ¦¡1=!0¡A·í¤å¦r®É(¥]§t"")±j­¢Âର0
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

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