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

½Ð±Ð¦U¦ì«e½ú²Î­pªº°ÝÃD

½Ð±Ð¦U¦ì«e½ú²Î­pªº°ÝÃD

¦pªG­n³]­p¤@­Ó²Î­pªºªí®æ¡A·Q­n¦b«ü©wªºÀx¦s®æ¤º¿é¤J¤é´Á´N·|ª½±µ±a¥X¬ÛÃö¸ê®Æ
³o¼Ë¸Ó¦p¦ó»s§@¡H
®æ¦¡¦p¤Uªí¡G


1.zip (3.62 KB)

¦^´_ 1# ryan36241


J4 =SUMPRODUCT(($D$3:$D$14=RIGHT($H4))*($B$3:$B$14=$H$2))
L4 =SUMPRODUCT(($D$3:$D$14=RIGHT($H4))*($B$3:$B$14=$H$2),$E$3:$E$14)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_  ryan36241


J4 =SUMPRODUCT(($D$3D$14=RIGHT($H4))*($B$3B$14=$H$2))
L4 =SUMPRODUCT(($D$ ...
ML089 µoªí©ó 2014-10-15 18:12



ÁÂÁ«e½ú«ü±Ð¡A³o¼Ë¥i¥H¥¿½T¾É¥X§Ú­nªº­È
¦ý·Q¶¶«K½Ð±Ð¤@¤U¡A¦pªGÃþ§Oªºµ§¼Æ³¡¥÷·Q­n¥H§Ç¸¹°Ï¤À¸Ó«ç»ò§@¡H
¨Ò¦p¡G¢ÏªºÃþ§O¦b15¸¹¦³¤Tµ§¡@¦ý¨ä¤¤¨âµ§§Ç¸¹¬Û¦P¡@¤@µ§¤£¦P¬G¨t²Î·|§P§O¥u¦³¨âµ§

TOP

¦^´_ 3# ryan36241

½Ð¦A¼ÒÀÀÀɮ׶ǤW¨Ó¬Ý¬Ý
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_  ryan36241

½Ð¦A¼ÒÀÀÀɮ׶ǤW¨Ó¬Ý¬Ý
ML089 µoªí©ó 2014-10-15 22:54



Àɮצp¤Uªþ¥ó¡A¦³±N»Ý¨D¼g¦bªí®æ¤º
¦A³Â·Ð«e½ú«ü±Ð¤F¡ã¡@ÁÂÁÂ

²Î­p.zip (3.29 KB)

TOP

¦^´_ 5# ryan36241

L3=SUMPRODUCT(($D$2:$D$7=$K3)*($B$2:$B$7=$K$1))
M3=SUMPRODUCT(($D$2:$D$7=$K3)*($B$2:$B$7=$K$1),$E$2:$E$7)

TOP

¦^´_ 5# ryan36241

L3 =SUMPRODUCT((B$2:B$9=K$1)*(D$2:D$9=K3)*(MATCH(C$2:C$9&D$2:D$9,C$1:C$9&D$1:D$9,)=ROW($2:$9)))
M3 =SUMPRODUCT((B$2:B$9=K$1)*(D$2:D$9=K3),E$2:E$9)

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

TOP

¦^´_  ryan36241

L3 =SUMPRODUCT((B$2:B$9=K$1)*(D$2$9=K3)*(MATCH(C$2:C$9&D$2$9,C$1:C$9&D$1$9 ...
ML089 µoªí©ó 2014-10-16 16:03



·PÁ«e½úÀ°¦£~~~ ²×©ó§â ³øªí»s§@¦n¤F
ÁÂÁÂËç~~
¶¶«K½Ð°Ý¤@¤U¡A¦pªG­n«ü©w¤@ª½¦CªºÀx¦s®æ
B$2:B$9­n«ü©w¬°¢Ðª½¦Cªº¸Ü¸Ó¦p¦ó¼g¡H

TOP

·PÁ«e½úÀ°¦£~~~ ²×©ó§â ³øªí»s§@¦n¤F
ÁÂÁÂËç~~
¶¶«K½Ð°Ý¤@¤U¡A¦pªG­n«ü©w¤@ª½¦CªºÀx¦s®æ
B$2:B$9 ...
ryan36241 µoªí©ó 2014-10-16 19:03



ÁÙ¦³¦pªG­n³]­p¾ã­Ó¤ë¥÷ªº²Î­p­n¦p¦ó¼g?
¦A³Â·Ð«e½ú«ü±Ð~
ÁÂÁÂ

TOP

¦^´_ 8# ryan36241


L3 =SUMPRODUCT((B$2:B$9=K$1)*(D$2:D$9=K3)*(MATCH(C$2:C$9&D$2:D$9,C$1:C$9&D$1:D$9,)=ROW($2:$9)))
M3 =SUMPRODUCT((B$2:B$9=K$1)*(D$2:D$9=K3),E$2:E$9)

­×§ï¦¹§PÂ_¦¡ (B$2:B$9=K$1)
§ï¬° (MONTH(B$2:B$9)=K$1)
K$1¿é¤J 1~12 (¤ë¥÷)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¡i®É¶¡¦¨´N¤@¤Á¡j®É¶¡¥i¥H³y´N¤H®æ¡A¥i¥H¦¨´N¨Æ·~¡A¤]¥i¥HÀx¿n¥\¼w¡C
ªð¦^¦Cªí ¤W¤@¥DÃD