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

½Ð°Ý³o12¸Uµ§¸ê®Æ¸Ì­± §Ú­n¦p¦ó²Î­p "TRUE"³sÄò¥X²{ªº¦¸¼Æ

¥»©«³Ì«á¥Ñ ML089 ©ó 2017-4-28 16:32 ½s¿è

¥Î¦WºÙ©w¸q¤½¦¡
xT =FREQUENCY(ROW($1:$120000),ROW($1:$120000)*($A$1:$A$120000=FALSE))

³Ì¦h¥X²{³sÄò´X­Ó
=MAX(xT)-1


³sÄò¥X²{
D3:D32 =FREQUENCY(xT,MOD(ROW(2:30),29))
½d³ò°}¦C¤½¦¡
PS: ½Æ»s¤W­z¤½¦¡¡A¿ïD3:D32«á±N¤½¦¡¶K¤W½s¿è¦C¡A¥ý«öCTRL+SHIFT¤£©ñ¡A¦A«öENTER¿é¤J¤½¦¡¡A­Y¦¨¥\¤½¦¡«e«á·|¦³ {.....}

"´X­Ó"  C3¤U©Ô30­Ó
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 3# eric7765

²Î­ptrueªº³sÄò¥X²{¦¸¼Æ.rar (372.03 KB)
¬Ý¬ÝÀɮ׸̪º¤½¦¡(¶À¦â°Ï)

¤u¨ã¦C¸Ì ¤½¦¡ - ¦WºÙºÞ²z­û¡A¬d¬Ý xT ¦WºÙ
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 7# eric7765

½d³ò°}¦C¤½¦¡
PS: ½Æ»s¤W­z¤½¦¡¡A¿ïD3:D32«á±N¤½¦¡¶K¤W½s¿è¦C¡A¥ý«öCTRL+SHIFT¤£©ñ¡A¦A«öENTER¿é¤J¤½¦¡¡A­Y¦¨¥\¤½¦¡«e«á·|¦³ {.....}

¤@¯ë¤½¦¡¥Î ENTER¿é¤J
°}¦C¤½¦¡¥Î CTRL+SHIFT+ENTER ¤TÁä»ô«ö¿é¤J
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 9# eric7765


²Î­ptrueªº³sÄò¥X²{¦¸¼Æ_TEST.rar (7.44 KB)
¤p¸ê®Æ½m²ß¡A¬Ý¬Ý¤½¦¡¤¤FREQUENCY¨ç¼Æ¨C­Ó°Ñ¼ÆÅܤơA¥ý¦Û¦æÅé·|¡A¤£À´¦A°Ý¡C

²Ä¤@­Ó(¤º)FREQUENCY²Î­pªº¼Æ­n´î1¤~¬O³sÄò¼Æ
¦]¬°°}¦C¦³120000­Ó¤£·Q¥ý´î1¡A³o·|¼W¥[­pºâ®É¶¡¡A©Ò¥H¥Î¨ú2¥N´À1¡A¨ú3¥N´À2¡A¨Ó²Î­p³sÄò¼Æ¼Æ¶q
FREQUENCY ²Ä¤Gªº°}¦C¼Æ­È²Ä¤@­ÓÀ³¸Ó¬°2¶}©l(2,3,4,5,......,0,1)¡A³Ì«áÀ³¸Ó¥Î1¦¬§À¡A©Ò¥H¨Ï¥ÎMOD¨ç¼Æ¨Ó³B²z
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2017-4-28 23:33 ½s¿è

²Î­p¨C­Ó³sÄò¼Æ+1
xT =FREQUENCY(ROW($1:$120000),ROW($1:$120000)*($A$1:$A$120000=FALSE))

²Î­p¬Û¦Pªº³sÄò¼Æ¼Æ¶q
=FREQUENCY(xT,MOD(ROW(2:30),29))
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 13# eric7765

AÄæ²Î­p
TRUE  =FREQUENCY(FREQUENCY(ROW(2:4835),ROW(2:4835)*(A2:A4835<>TRUE)),MOD(ROW(2:33),32))
FALSE =FREQUENCY(FREQUENCY(ROW(2:4835),ROW(2:4835)*(A2:A4835<>FALSE)),MOD(ROW(2:33),32))

BÄæ²Î­p
TRUE  =FREQUENCY(FREQUENCY(ROW(2:4835),ROW(2:4835)*(B2:B4835<>TRUE)),MOD(ROW(2:33),32))
FALSE =FREQUENCY(FREQUENCY(ROW(2:4835),ROW(2:4835)*(B2:B4835<>FALSE)),MOD(ROW(2:33),32))

CÄæ²Î­p
TRUE  =FREQUENCY(FREQUENCY(ROW(2:4835),ROW(2:4835)*(C2:C4835<>TRUE)),MOD(ROW(2:33),32))
FALSE =FREQUENCY(FREQUENCY(ROW(2:4835),ROW(2:4835)*(C2:C4835<>FALSE)),MOD(ROW(2:33),32))
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 18# eric7765

¥H¤U¸ê®Æ¦ì©ó 2:4835¦C¡A­Y¦³ 10000µ§ªí¥Ü¸ê®Æ¦ì©ó 2:10001¡A±N
ROW(2:4835)§ï¬°ROW(2:1001)
A2:4835§ï¬°A2:A1001

AÄæ²Î­p
TRUE  =FREQUENCY(FREQUENCY(ROW(2:4835),ROW(2:4835)*(A2:A4835<>TRUE)),MOD(ROW(2:33),32))
FALSE =FREQUENCY(FREQUENCY(ROW(2:4835),ROW(2:4835)*(A2:A4835<>FALSE)),MOD(ROW(2:33),32))

BÄæ²Î­p
TRUE  =FREQUENCY(FREQUENCY(ROW(2:4835),ROW(2:4835)*(B2:B4835<>TRUE)),MOD(ROW(2:33),32))
FALSE =FREQUENCY(FREQUENCY(ROW(2:4835),ROW(2:4835)*(B2:B4835<>FALSE)),MOD(ROW(2:33),32))

CÄæ²Î­p
TRUE  =FREQUENCY(FREQUENCY(ROW(2:4835),ROW(2:4835)*(C2:C4835<>TRUE)),MOD(ROW(2:33),32))
FALSE =FREQUENCY(FREQUENCY(ROW(2:4835),ROW(2:4835)*(C2:C4835<>FALSE)),MOD(ROW(2:33),32))
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 16# eric7765

28 29­Ó¨ºÃä·|¥X²{¼Æ¦r?

³o¬O¤£­nªº¼Æ¦r(¬°1¤Î0ªº²Î­p¼Æ)¡A¥u¬O¨q¥X¨Ó¤F¸Ñ
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 15# eric7765

AÄæ²Î­p
TRUE  =FREQUENCY(FREQUENCY(ROW(2:4835),ROW(2:4835)*(A2:A4835<>TRUE)),MOD(ROW(2:33),32))
FALSE =FREQUENCY(FREQUENCY(ROW(2:4835),ROW(2:4835)*(A2:A4835<>FALSE)),MOD(ROW(2:33),32))

°Ñ¦Ò¤W¨â¦¡TRUE»PFALSE¨Ï¥Î®t§O¡A¦]¬°¦³ªÅ¥Õ®æ®É¡A¥Î = ®e©ö¿ù»~¡A§ï¥Î <> ¤ñ¸û¦n¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 24# eric7765

¥i¥H°Ñ¦Ò19¼Ó¤è¦¡¤ñ¸û²³æ
B1 {=IF(AND(D$2:D$17=A1:A16),1,0)}
¤U©Ô
I6 =COUNTIF(B:B,1)


OR


I6 =COUNT(0/(MMULT(N(N(OFFSET(A1,ROW(1:120000)+COLUMN(A:P)-2,))=N(OFFSET(D2,COLUMN(A:P)-1,))),ROW(1:16)^0)=16))
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¤H¨ÆªºÁ}Ãø»PµZ¿i¡A´N¬O¤@ºØ¦ÒÅç¡C
ªð¦^¦Cªí ¤W¤@¥DÃD