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

[µo°Ý] COUNTIFS¤£¥]§t¦hÃöÁä¦r°ÝÃD

[µo°Ý] COUNTIFS¤£¥]§t¦hÃöÁä¦r°ÝÃD

¥»©«³Ì«á¥Ñ msmplay ©ó 2016-5-28 00:49 ½s¿è

¤£¦n·N«ä¡A¤p©f­n°Ý­Ó«D~~~~±`Äøªº°ÝÃD
´N¬O¥H¤U¤½¦¡¬O²Î­pAÄæ²Å¦X"´«³f"©Î"°h³f"ÃöÁä¦r+BÄæ²Å¦X"Ãö¾÷­«¶}"ÃöÁä¦rªº¼Æ¶q
=SUM(COUNTIFS($A:$A,{"*´«³f*","*°h³f*"},$B:$B,"*Ãö¾÷­«¶}*"))


¦ý­n¤½¦¡«ç»ò­×§ï¦¨¡u¤£¥]§t¡v©O¡H¨Ò¦p¥H¤U¿ù»~¼gªk(¤p©f¶Ã¼gªº¥Ü·N¼gªk°Õ)
=SUM(COUNTIFS($A:$A,{"<>*´«³f*","<>*°h³f*"},$B:$B,"*Ãö¾÷­«¶}*"))



¨D¸Ñ¤X~~~~~~~~~~
*¦v¤k¤@ªTµL»~*

¦^´_ 1# msmplay


¸É¤W´ú¸ÕÀÉhttp://www.FunP.Net/735107
*¦v¤k¤@ªTµL»~*

TOP

¦^´_ 2# msmplay

¤gªk·Ò¿û¤@¤U

=SUM(COUNTIFS(A:A,{"<>*´«³f*","<>*°h³f*","*´«³f*","*°h³f*"},B:B,"*Ãö¾÷­«¶}*")*{1,1,-1,-1})/2

¥t¥~ ¤£¥]§t"´«³f"©Î"°h³f"ÃöÁä¦r ·Pı¤W¼g¦¨ ¤£¥]§t"´«³f"¤Î"°h³f"ÃöÁä¦r ·|¤ñ¸ûºë½T¤@ÂI

TOP

=SUMPRODUCT(COUNTIFS($A:$A,{"*","*´«³f*","*°h³f*"},$B:$B,"*Ãö¾÷­«¶}*")*{1,-1,-1})
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 3# KCC
K¤j~~~~~~·PÁ¼ö¤ßÀ°¦£³á¡I


¦^´_ 4# ML089
M¤j~~~~~~¤]«D±`·PÁ§A£¬¡A¤p©f°ÝÃD¤w¸Ñ¡I
*¦v¤k¤@ªTµL»~*

TOP

        ÀR«ä¦Û¦b : ¡i¦æµ½­n¤Î®É¡j¦æµ½­n¤Î®É¡A¥\¼w­n«ùÄò¡C¦p¿N¶}¤ô¤@¯ë¡A¥¼¿N¶}¤§«e¤d¸U¤£­n°±º¶¤õ­Ô¡A§_«h­«¨Ó´N¤Ó¶O¨Æ¤F¡C
ªð¦^¦Cªí ¤W¤@¥DÃD