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

[µo°Ý] Sumifs¥Îªk

[µo°Ý] Sumifs¥Îªk

¨Ï¥ÎSUMIFS¨ç¼Æ±ø¥ó¥[Á`®É¡AµLªk§P©w¤é´Á¡A½Ð±Ð¦U¦ì°ª¤â¡A¬O­þ¸Ì§Ë¿ù¤F¡HÁÂÁ¡I

SUMIFS°ÝÃD.zip (22 Bytes)
Adam

¦^´_ 8# adam2010


    ³o¤S¬O¦Ñ°ÝÃD  Á`­p©ñ¦b«á­±¡]S1¡^ ¦pªG§Aªº½d³ò¨S¦³³]©w¦n ´N¬O®e©ö¥X°ÝÃD
   §Aªº¤½¦¡¬O =SUMPRODUCT(($K$1:$V$1>$E$1)*(K6:V6))
    ­è¦n Á`­p>¤é´Á = true...©Ò¥H¥¦´N¤@°_­pºâ¤U¥h
   ¤£¬O§ïÁ`­pªº¦ì¸m ´N¬O§ï¤½¦¡   
   °ò¥»¤W§A¦Û¤v­×§ï¹L«áªº¤]¬Ook¡A¦]¬°¦hºâ¤@¦¸Á`­p¡A
   =SUMPRODUCT(($K$1:$V$1>$E$1)*(K6:V6))-SUM(K6:V6)/2
  ©ÎªÌ§â K:V §ï¦¨K:R
  =SUMPRODUCT(($K$1:$R$1>$E$1)*(K6:R6))

°Ñ¦Ò¬Ý¬Ý§a
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

·íÁ`­p¦b¥k°¼®É¨Ï¥Î=SUMIFS(K6:U6,$K$1:$U$1,">"&D$1)¤£·|±NÁ`­pºâ¶i¥h
¦ý¬O¨Ï¥Î=SUMPRODUCT(($K$1:$V$1>$E$1)*(K6:V6))«h·|±NÁ`­p¥[¶i¥h¡A©Ò¥H¤§«e§Ú¬O¦b¤½¦¡«á­±¦A´î¥hÁ`­p

SUMIFS°ÝÃD-2.zip (79.62 KB)
Adam

TOP

·PÁÂsoftsadwind¦^ÂСA´ú¸ÕOK
©Ò¥H¤é´Á±ø¥ó»Ý­n¥Î&³s±µ¡A¬O³o¼Ë¶Ü?
ªøª¾ÃѤF¡AÁÂÁ¡I
Adam

TOP

¦^´_ 3# adam2010


    §Ú­Ó¤H·|¤ñ¸û³ßÅw¥Îsumproduct¥hºâ....
    ¦pªG¤@©w­n¥Îsumifs
    D2=SUMIF($L$1:$V$1,">"&D$1,L2:V2)
   ©ÎªÌ=SUMIFS(L2:V2,$L$1:$V$1,">"&D$1)
   E2==SUMIFS(L2:V2,$L$1:$V$1,">"&E$1)
  
   °Ñ¦Ò¬Ý¬Ý
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

·PÁÂaerªºªº¨ó§U¡AÁÂÁ¡I
Adam

TOP

¦^´_ 3# adam2010

D2=SUMPRODUCT((L1:S1>$D$1)*(L2:S2))

E2=SUMPRODUCT((L1:S1>$E$1)*(L2:S2))

TOP

·PÁÂsoftsadwind¤j´£¿ô¡A­«·s¤W¶Ç¤@¦¸
SUMIFS°ÝÃD-1.zip (79.65 KB)
½Õ¾ãÁ`­p¦ì¸m¡AÁÙ¬OµLªk¥[Á`¡A¤£ª¾°ÝÃD¦b­þ¡H
Adam

TOP

¦^´_ 1# adam2010


    ªþ¥ó¤U¸ü«á µLªF¦è
   D1©MD2ªº¤½¦¡¬O±qKºâ¨ìY¡C§AªºÁ`­p¨º£¸¦æ ¦ü¥G¬OS(·Ó§A¹Ï¤ù¥hºâªº)¡A©Ò¥H·|³Q­pºâ¦b¸Ì­±¡C
  ¦pªG§Aªº¸ê®Æ¬O«ùÄò¾î¦¡¼W¥[¡A«Øij§A§âÁ`­p©ñ¦b12/23¤§«e¡Cª`·N¤½¦¡­n§ï¦¨L¨ìY ©ÎªÌ§ó»·...
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

        ÀR«ä¦Û¦b : ¤H­n¦Û·R¡A¤~¯à·R´¶¤Ñ¤Uªº¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD