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

[µo°Ý] ²Î­p¦C¼Æ½d³òªº°ÝÃD¡C

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2018-5-7 09:59 ½s¿è

¦^´_ 4# ML089



5¬P.rar (582.89 KB)

±z»¡¡J
excel 2007 MMULTªº®e¶q¦³ 4^10 = 1048576

¨º¦pªG¥H2007ª©¨Ó¸Ñ
49¨ú5¦³1906884²Õ
°²³]¤À¦¨¤G¬q
²Ä¤@¬q  L2¡JP1000000
R2
=SUM(N(MMULT(COUNTIF(L2:P2,INDIRECT("B2:G"&COUNTA(A:A))),{1;1;1;1;1;1})=5))
¤U©Ô¶ñº¡

½Ð°Ý¡J
°£¤FCOUNTIF¨ç¼Æ(®Ä²v¤£¨Î)¡A¦³¨ä¥¦ªº¨ç¼Æ¥i´À´«¶Ü?
ÁÂÁ±z^^"

TOP

¥»©«³Ì«á¥Ñ hcm19522 ©ó 2018-5-7 11:06 ½s¿è

H2(»²§U)=SUMPRODUCT((MMULT(COUNTIF(B2:G2,L$2:Q$16),{1;1;1;1;1;1})=6)*K$2:K$16)


R2=SUMPRODUCT(N(H:H=K2))
google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦^´_  ML089

±z»¡¡J
excel 2007 MMULTªº®e¶q¦³ 4^10 = 1048576

¨º¦pªG¥H2007ª©¨Ó¸Ñ
49¨ú ...
ziv976688 µoªí©ó 2018-5-7 09:53


¤À¬q¤]¬O¤£¿ùªº·Qªk¡AÆg
¥Ø«e¨S¦³·Q¨ì¨ä¥L¤èªk¡AÁöµMCOUNTIFºC¤]¥u¯à¨Ï¥Î

excel 2007 MMULTªº®e¶q¦³ 4^10 = 1048576 ¥u¬O·§¦ô¡A¦]¬°2007ª©ªº³Ì¤j¦C¼Æ¬° 4^10 = 1048576 ¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2018-5-7 19:12 ½s¿è

¦^´_ 12# hcm19522
H2(»²§U)=SUMPRODUCT((MMULT(COUNTIF(B2:G2,L$2:Q$16),{1;1;1;1;1;1})=6)*K$2:K$16)  =>OK

R2=SUMPRODUCT(N(H:H=K2))  =>NG (Åã¥Ü#NUM!)

R2­n¦p¦ó¦A­×¥¿?
·q½Ð¦A½ç±Ð!
ÁÂÁ±z^^

TOP

¦^´_ 13# ML089

ÁA¸Ñ¤F!¦pªG¦³§ó¨Îªº¸ÑÃD¨ç¼Æ¡A·q½Ð¦A½ç±Ð!
ÁÂÁ±z^^

TOP

Äò14#
R2ªºH:H§ï¦¨
H$2:H$10648
©Î
INDIRECT("H2:H"&COUNTA(A:A))
´NOK¤F!
ÁÂÁ±zªº¼ö¤ß«ü¾É!·P®¦^^

TOP

        ÀR«ä¦Û¦b : §g¤l¦p¤ô¡AÀH¤è´N¶ê¡AµL³B¤£¦Û¦b¡C
ªð¦^¦Cªí ¤W¤@¥DÃD