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

[µo°Ý] COUNTIFS²Î­p½Æ¼Æ±ø¥ó°ÝÃD

[µo°Ý] COUNTIFS²Î­p½Æ¼Æ±ø¥ó°ÝÃD

Â^¨ú.PNG
2016-6-3 23:47


·Q½Ð±Ð¤@¤U¬°¦óC7¤½¦¡²Î­pµ²ªG¦³°ÝÃD¡H¬O§_COUNTIFSµLªk²Î­p½Æ¼Æ±ø¥ó¡H
ÁöµM¤p©fª¾¹D¥t¤@­ÓSUMPRODUCT¤½¦¡¥i¸Ñ¨M½Æ¼Æ±ø¥ó²Î­p°ÝÃD¡A¦ý¤½¥q¸ê®ÆÀɬù¦³¢³¸Uµ§¸ê®Æ®É¡Aµo²{¨Ï¥ÎSUMPRODUCT¹Bºâ³t«×¸ûºC¤]¸û¤[
©Ò¥H¤~¥´ºâ§ï¥ÎCOUNTIFS¤½¦¡¡ã¡ã¡ã¡ã

¨D¸Ñ¡H¡H¡H

´ú¸Õ.rar (24.94 KB)
*¦v¤k¤@ªTµL»~*

¦^´_ 1# msmplay


    =SUM(COUNTIFS(A:A,{"´«³f";"*DOA*"},B:B,{"*µLªk¶}¾÷*","*½Ý½u*","*¾î½u*"},C:C,"ªù¥«"))

TOP

¥»©«³Ì«á¥Ñ hcm19522 ©ó 2016-6-4 17:56 ½s¿è

»²§U D2 ¤U©Ô=IF(((A2="´«³f")+COUNTIF(A2,"*DOA*"))*MMULT(ISNUMBER(FIND({"µLªk¶}¾÷","½Ý½u","¾î½u"},B2))*1,{1;1;1})*(C2="ªù¥«")>0,1,)
E7=SUM(D:D)
³]»²§U ¸ê®Æ¦h À³³t«×§Ö

TOP

¥»©«³Ì«á¥Ñ msmplay ©ó 2016-6-4 20:28 ½s¿è

¦^´_ 2# KCC

K¤j~~~~¶W¯Å·PÁ±z¡A´ú¸Õ¥i¦æ³á!!!¦ý¤p©f¥i¥H¦A½Ð±Ð¤@¤U2­Ó°ÝÃD¶Ü??
´N¬O{"´«³f";"*DOA*"}<<¤¤¶¡ªº , §ï ; ¬O¤°»ò­ì²z¤X!?¦]¬°§Ú¬d¨ç¼Æ»¡©ú¦n¹³¤]¨SÂ檺¥Îªk­C!!

µM«á¦pªG³sCÄæ¤]»Ý­n¦h±ø¥ó²Î­pªº¸Ü¡A¬O¤£¬O´N¤£¾A¥Î¥H¤W¥Îªk??

¨Ò¦p:CÄæ¨Ó·½¥¼¨Ó¤£¥u¦³"ªù¥«"»P"ºô¸ô"®É¡A¥i¯à¦³¦h­Ó¨Ó·½¡A¦ýCÄæ¥u»Ý²Î­p"ªù¥«"»P"ºô¸ô"¡A¤½¦¡­×§ï¦p¤U«oµLªk²Î­p¥¿½T¼Æ¦r

=SUM(COUNTIFS(A:A,{"´«³f";"*DOA*"},B:B,{"*µLªk¶}¾÷*","*½Ý½u*","*¾î½u*"},C:C,{"ªù¥«";"ºô¸ô"}))

Â^¨ú.PNG
2016-6-4 20:27

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

TOP

¦^´_ 3# hcm19522

h¤j~~~¤]«D±`·PÁ±zªº¼ö¤ßÀ°¦£­ò!!
*¦v¤k¤@ªTµL»~*

TOP

¦^´_ 5# msmplay
§Ú¤]µo²{ "," »P ";" ¤£¸Ñ¨ä·N ¦ý¤GÃä "," »P ";" ¹ï½Õ ¥ç¥i¦æ ÀR«Ý KCC¤j½ç±Ð

TOP

¥»©«³Ì«á¥Ñ KCC ©ó 2016-6-4 22:03 ½s¿è

¦^´_ 4# msmplay


    ³oºâ¬Oexcel°}¦C¹Bºâªº¯S©Ê§a¡A§Úªº²z¸Ñ¤j­P¦p¤U
   {1,1} ¬O¤ô¥­°}¦C {1;1}¬O««ª½°}¦C ¬Û­¼·|ÅÜ {1,1;1,1},SUM¤§«á¬O4
   ¦pªG³£¬O¤ô¥­°}¦C {1,1}*{1,1}={1,1}, sum¤§«á¬°2
   
   ¥H³oÆ[©ÀÃþ±À©p©Ò­nºâªº
   ¦pªG¬O³r¸¹¡A{"´«³f";"*DOA*"} {"*µLªk¶}¾÷*","*½Ý½u*","*¾î½u*"} ¨â°}¦C³£¬O¤ô¥­¡A·|Åܦ¨¨D  ("´«³f","*µLªk¶}¾÷*","ªù¥«"), ("*DOA*","*½Ý½u*","ªù¥«") ,("" ,"*¾î½u*","ªù¥«") ¤T²Õ±ø¥ó
   ´«¦¨ ¤À¸¹¡A´N·|Åܦ¨ ¤@­Ó 2*3ªº°}¦C(¤Ï¤§´NÅܦ¨3*2°}¦C) =>  ("´«³f","*µLªk¶}¾÷*","ªù¥«") ("*DOA*","*µLªk¶}¾÷*","ªù¥«), ....................¦@¤»²Õ±ø¥ó

   ¦Ü©ó©pªº²Ä¤G­Ó°ÝÃD¡A¦pªG¦A§â²Ä¤Tºû«×(ªù¥«¡Bºô¸ô...)ÂX¥R¡A¹ï¦hºû«×°Ñ·Ó¤§Ãþªº±Í¤H¤@ª½¨S·d±o«Ü²M·¡¡A¥Ø«eµLªkÀ°±o¤W¦£¡A©çÁ¡C

   ¥t¥~¡Ah¥S¥Îªºmmult¡A¥Îªº¦nªº¸Ü³t«×·|§Ö«Ü¦h¡A¤]¥i¦h°Ñ¦Ò¡C

TOP

©êºp¡A­èµo²{¼g¿ù¤F

¦pªG¬O³r¸¹¡A{"´«³f","*DOA*"} {"*µLªk¶}¾÷*","*½Ý½u*","*¾î½u*"} ¨â°}¦C³£¬O¤ô¥­
­ì¤å¤w¶W¹L­×§ï®É­­¡A¦b³o¸Ì§ó¥¿

TOP

¦^´_ 6# hcm19522

h¤j~~±z´£¨Ñªº¤èªk§Ú¨ì¤½¥q¦A´ú¸Õ¬Ý¬Ý¡A«D±`·PÁ³á!!
*¦v¤k¤@ªTµL»~*

TOP

¦^´_ 7# KCC

­ì¨Ó¦p¦¹¤X~~~~~¤p©f¤µ¤Ñ¤S¦h¤W¤F¤@½Ò¡A¤]«D±`·PÁ±z³áK¤j!!!!
*¦v¤k¤@ªTµL»~*

TOP

        ÀR«ä¦Û¦b : ¡i¬°µ½Ävª§¡j¤H¥Í­n¬°µ½Ävª§¡A¤À¬í¥²ª§¡C
ªð¦^¦Cªí ¤W¤@¥DÃD