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

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

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

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2018-5-4 16:52 ½s¿è

¦C¼Æ½d³òªº°ÝÃD.rar (96.87 KB)
¦C¼Æ½d³òªº°ÝÃD.png
2018-5-4 16:48

R2=
²Î­p¦bB2¡JGn½d³ò¤ºªº¦U·í¦C¡A¦³¥X²{ L2¡JQ16¦U·í¦C¬Û¦P6­Ó­Èªº¦¸¼Æ¡C

R2¤½¦¡
=SUM(N(MMULT(COUNTIF(L2:Q2,INDIRECT("B2:G"&COUNTA(A:A))),{1;1;1;1;1;1})=6))
©Î
=SUM(N(MMULT(COUNTIF(L2:Q2,B$2:G$10648),{1;1;1;1;1;1})=6))
©Î
=SUM(N(MMULT(COUNTIF(L2:Q2,B$2:G$5463),{1;1;1;1;1;1})=6))
©Î
=SUM(N(MMULT(COUNTIF(L2:Q2,B$2:G$5462),{1;1;1;1;1;1})=6))

°ÝÃD¡J
·í¤ñ¹ï½d³ò¦C¨ì
COUNTA(A:A) ¦C(¦]¬°A¡JGµ¥7Ä檺¸ê®Æ·|¤£©w®ÉÄò¼W)¡A16048¦C¡A5463¦C®É¡A¤½¦¡³£µLªk­pºâ¥X¥¿½T­È¡F

¥u¦³¤ñ¹ï½d³ò¦C¨ì <= 5462 (Ex¡JS2¤½¦¡)®É¡A«h¤½¦¡¤~¥i¥H­pºâ¥X¥¿½T­È¡C

½Ð°Ý¡J
R2ªº¤½¦¡­n¦p¦ó­×§ï ?
ÁÂÁÂ !

¦^´_ 1# ziv976688
°Ñ¦Ò¬Ý¬Ý
R2¶ñ¤J
  1. =SUMPRODUCT((L2=B:B)*(M2=C:C)*(N2=D:D)*(O2=E:E)*(P2=F:F)*(Q2=G:G))
½Æ»s¥N½X

TOP

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

¦^´_ 2# naruto018

n¤j:
³o¤½¦¡¤£¦æ¡F¦ýÁÙ¬OÁÂÁ±zªº¼ö¤ß½ç±Ð^^

TOP

¦^´_ 1# ziv976688

MMULT ¨ç¼Æ¦³°}¦C®e¶q­­¨î¡A¤j­P¬°
excel 2007 MMULTªº®e¶q¦³ 4^10 = 1048576
excel 2003 MMULTªº®e¶q¦³ 5461

§Ú¥Î EXCEL2007ª©°õ¦æ¡AR2´N¯à°õ¦æ
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 4# ML089

ÁA¸Ñ¤F!ÁÂÁ±z!
½Ð°Ý:
2003ª©¡A¦³¨ä¥¦¨ç¼Æ¥i¨ÑR2ªº¤½¦¡»Ý¨D¶Ü?
ÁÂÁ±z^^

TOP

¦^´_ 5# ziv976688

EXCEL2003ª©¸Ñªk
R2 =SUMPRODUCT(N((B$2:B$16048^7+C$2:C$16048^7+D$2:D$16048^7+E$2:E$16048^7+F$2:F$16048^7+G$2:G$16048^7)=(L2^7+M2^7+N2^7+O2^7+P2^7+Q2^7)))
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 6# ML089

´ú¸ÕOK¤F!
ÁÂÁ±zªº¤£§[½ç±Ð!·P®¦^^

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2018-5-5 13:47 ½s¿è

¦^´_ 6# ML089
¥»¥H¬°^7¬O7¦¸¤èªº·N«ä¡F¦]¦¹´ú¸Õ:
^7¥þ³¡§ï¬°^6   =>µª®×OK
^7¥þ³¡§ï¬°^3   =>µª®×NG
©Ò¥H^7´N¤£¬O7¦¸¤è

½Ð°Ý:
¥HL2:Q2ªº¼Æ¦r¤ñ¹ïB2:G10648¦³6­ÓÀx¦s®æ(¤£­­Äæ¦ì)ªº¼Æ¦r¬Û¦Pªº¦C¼Æ¦³´X¦C~
^7¬O¥Nªí¤°»ò·N«ä?
ÁÂÁ±z^^"

TOP

¦^´_  ML089
¥»¥H¬°^7¬O7¦¸¤èªº·N«ä¡F¦]¦¹´ú¸Õ:
^7¥þ³¡§ï¬°^6   =>µª®×OK
^7¥þ³¡§ï¬°^3   =>µª®×NG
©Ò ...
ziv976688 µoªí©ó 2018-5-5 13:32


^7´N¬O7¦¸¤èªº·N«ä¡F¥Øªº´N¬O©ñ¤j¨C¦ì¼Æ¦rªº¶¡¶ZÂ÷¡A«OÃÒ¦U¼Æ©ñ¤j«á¬Û¥[¤§¦X¨ã¦³°ß¤@©Ê¡C

^3©ñ¤j¶¡¶Z¹L¤p¡A¤@¯ë^7¥H¤W¤j­POK

«e¤½¦¡¤w¸g¥i¥H­pºâ´X­Ó¬Û¦P
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 9# ML089
ÁÂÁª©¥Dªº­@¤ß¸Ñ»¡¡A
ÁA¸Ñ¤F!·P®¦^^

TOP

        ÀR«ä¦Û¦b : ¨Ã«D¦³¿ú¾{¬O§Ö¼Ö¡A°Ý¤ßµL·\¤ß³Ì¦w¡C
ªð¦^¦Cªí ¤W¤@¥DÃD