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

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

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

TOP

¦^´_ 3# msmplay


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

TOP

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

TOP

¦^´_ 7# msmplay

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

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 : °ß¨ä´L­«¦Û¤vªº¤H¡A¤~§ó«i©óÁY¤p¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD