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

[µo°Ý] EXCEL²¤Æ¤½¦¡°ÝÃD

[µo°Ý] EXCEL²¤Æ¤½¦¡°ÝÃD

20109231wXyKNhXRSM.png
2020-8-26 09:11

½Ð°Ý¸Ó¦p¦ó²¤Æ¥H¤U¥|­Ó²Õ¦X°_¨Óªº¤½¦¡¡A2­ÓINDEX+MATCH¡B2­ÓSUMPRODUCT¡A¥|­Ó¤½¦¡¥u¦³$B3¸ò$D3³o¨â­Ó¦ì¸m¤£¦P¡A¨ä¥L§ìªº¦ì¸m³£¤@¼Ë¡C¥D­n§ì$B3¹ïÀ³ªº¼Æ¦r¸ò$D3¹ïÀ³ªºª÷ÃB¬Û¥[Á`¦b¤@°_¡C¨D¸Ñ~~~


TEST.rar (536.42 KB)
*¦v¤k¤@ªTµL»~*

ªþÀɥΪ©office¶}¤£¤F~~
«¬¸¹7,«¬¸¹151__
¬O«ü[«¬¸¹7 and «¬¸¹151]
[«¬¸¹7 and «¬¸¹151]
[«¬¸¹7 or «¬¸¹151]
ÁÙ¬O[«¬¸¹7 ~ «¬¸¹151],

TOP

¦^´_ 2# ­ã´£³¡ªL


   ­ã¤j~~~¬O«ü [«¬¸¹7 and «¬¸¹151] ³á!ÁÂÁÂ~~~   µM«á§Ú¥t¥~¦s¤F¤@­Ó2003ª©¥»¡A§A¬Ý¯à¤£¯à¶}³á~~


test.rar (760.21 KB)
*¦v¤k¤@ªTµL»~*

TOP

¦^´_ 3# msmplay


ª½±µ¥t¦s.XLS¬O¨S¥Îªº, ¦]®æ¦¡¤Ó¦h, ÁÙ¬OµLªk¶}±Ò,
§Ú¬OOFFICE2000

TOP

¦^´_ 4# ­ã´£³¡ªL


   ¨º¦pªG§Ú¶K¤½¦¡¡AµM«áºI¹Ïµ¹­ã¤j¬Ý¬Ý«¨~~¦pªG¯u¤£¦æ´N©ñ±ó ¼P
Àx¦s®æE3¤½¦¡
=IFERROR(INDEX(TEST!$D$3:$BZ$1000,MATCH($B3,TEST!$B$3:$B$1000,),MATCH($E1,TEST!$C$1:$BZ$1,)),0)+IFERROR(INDEX(TEST!$D$3:$BZ$1000,MATCH($D3,TEST!$B$3:$B$1000,),MATCH($E1,TEST!$C$1:$BZ$1,)),0)+SUMPRODUCT((1-ISNA(MATCH(¤â¤u³æ!$C$2:$FN$2,$B3,)))*(¤â¤u³æ!$A$3:$A$1000=$E1)*(¤â¤u³æ!$B$3:$B$1000=$E2)*(¤â¤u³æ!$C$3:$FN$1000))+SUMPRODUCT((1-ISNA(MATCH(¤â¤u³æ!$C$2:$FN$2,$D3,)))*(¤â¤u³æ!$A$3:$A$1000=$E1)*(¤â¤u³æ!$B$3:$B$1000=$E2)*(¤â¤u³æ!$C$3:$FN$1000))


TEST.PNG
2020-8-26 16:58
¤â¤u³æ.PNG
2020-8-26 16:58

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

TOP

¦^´_ 5# msmplay
¥ý¸Õ TEST ªºª÷ÃB¬O§_¥¿½T:
=SUMPRODUCT(SUMIF(TEST!B3:B1000,B3:D3,OFFSET(C3:C1000,,MATCH(E1,TEST!C1:BZ1,)))

TOP

¦^´_ 6# ­ã´£³¡ªL


   ­ã¤j~~~§A¤Ó±j¤F¡ATESTªºª÷ÃB¥¿½T³á¡A¦ý¤¤¶¡ªº¤½¦¡TEST!C3:C1000¤Ö¤FTEST!¡A§Ú¥[¤W¥h´N¥¿½T¤F
=SUMPRODUCT(SUMIF(TEST!B3:B1000,B3:D3,OFFSET(TEST!C3:C1000,,MATCH(E1,TEST!C1:BZ1,))))
*¦v¤k¤@ªTµL»~*

TOP

¦^´_ 7# msmplay

¤â¤u³æµ¥¤U¯Z¦^®a¦A¬Ý~~

TOP

¦^´_ 8# ­ã´£³¡ªL


   «¢~~~~~¨¯­W§A¤F£°
*¦v¤k¤@ªTµL»~*

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2020-8-26 19:27 ½s¿è

TESTªí§ï¦¨:
=SUMPRODUCT(SUMIF(TEST!B3:B1000,B3:D3,index(TEST!C3:BZ1000,,MATCH(E1,TEST!C1:BZ1,)+(E2="ª÷ÃB"))))
__OFFSET §ï¦¨ INDEX ¸û¤£¤zÂZÀÉ®×

¤â¤u³æ:§A­ì¨Ó¤½¦¡¬O¹ïªº
=SUMPRODUCT((¤â¤u³æ!A3:A1000=E1)*(¤â¤u³æ!B3:B1000=E2)*(1-ISNA(MATCH(¤â¤u³æ!C2:$FN2,B3:D3,)))*¤â¤u³æ!C3:FN1000)
©Î
=SUMPRODUCT((¤â¤u³æ!A3:A1000=E1)*(¤â¤u³æ!B3:B1000=E2)*(COUNTIF(B3:D3,¤â¤u³æ!C2:$FN2)>0)*¤â¤u³æ!C3:FN1000)
__COUNTIF¸ûºC, ¥ÎMATCH§Y¥i

__¦bE2¥i¿ï¾Ü[ª÷ÃB]©Î[¼Æ¶q]¦ÓÅÜ´«­pºâ????

TOP

        ÀR«ä¦Û¦b : ·O´d¨S¦³¼Ä¤H¡A´¼¼z¤£°_·Ð´o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD