eric7765

¦^´_ 29# ML089
ÁÂÁª©¥D±Ð¾É
³Ìªñµo²{¤@­Ó¤p°ÝÃD ·Q½Ð±Ð«e½ú­Ì¦³¨S¦³¤èªk¥i¥H­×¥¿
´N¬O¦pªG¦³¼W¥[©Î¬O§R°£¦Cªº¸Ü ´N·|¥X²{¿ù»~ ¦³¿ìªk§â¤½¦¡©T©w¦í¶Ü?
¦p¹Ï §Ú§âA1-A12 §R°£ µM«á¿ï¨ú ¤U¤èÀx¦s®æ¤W²¾  ¤½¦¡´N·|¶]±¼¤F  ¦p¦óÅý¤½¦¡³£¬O­pºâ³Ì·sªº¸ê®Æ
[attach]27144[/attach]


[attach]27145[/attach]

ML089

¦^´_ 28# eric7765

A1:A12000 ¸Ìµ¥©ó D2:D17 ¼Æ¶q
=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))

B1:B5000 ¸Ìµ¥©ó F2:F11 ¼Æ¶q
=COUNT(0/(MMULT(N(N(OFFSET(B1,ROW(1:5000)+COLUMN(A:J)-2,))=N(OFFSET(F2,COLUMN(A:J)-1,))),ROW(1:10)^0)=10))

A1:A120000§ï¬°B:B5000­×§ï
    A1§ï¬°B1
    12000§ï¬°5000

D2:D17§ï¬°F2:F11­×§ï
    D2§ï¬°F2
    D2:D17¬°16­Ó¡AF2:F11¬°10­Ó¡A±N16§ï¬°10¡AP(=16)§ï¬°J(=10)

eric7765

¦^´_ 27# ML089

²Ä¤@­Ó¤èªk¤w¸g¤F¸Ñ

·Q½Ð°Ý ¦pªG¤µ¤Ñ¤@¼Ë¬O12¸Uµ§¸ê®Æ  ­n·j¯Á"B"Äæ
=COUNT(0/(MMULT(N(N(OFFSET(A1,ROW(1:120000)+COLUMN(A:R)-2,))=N(OFFSET(F2,COLUMN(A:R)-1,))),ROW(1:16)^0)=16))

¦pªG¬O­n·j¯Á"B"Äæ ¬O§_µ¥©ó F2:F11ªº¸Ü¥H¤W¤½¦¡À³¸Ó§ó§ï¨º¨Ç¦a¤è??
            ­n·j¯Á"B"Äæ ¬O§_µ¥©ó E2:E8 ªº¸Ü??
            °²¦p¤µ¤Ñ¥u­n·j´M5¸Uµ§¸ê®Æ¬O§_¥u­n±N  =COUNT(0/(MMULT(N(N(OFFSET(A1,ROW(1:120000)+COLUMN(A:R)-2,))=N(OFFSET(F2,COLUMN(A:R)-1,))),ROW(1:16)^0)=16))
            ¸Ì­±ªº120000§ï¦¨50000§Y¥i


¦A³Â·Ð¤j¯«ª©¥D¤F  ³o´X¤Ñªº°ÝÃD °÷§Ú½m²ß¦n¤@°}¤l¤F   ·Q¤F¸Ñ¥H¤W³o¤½¦¡ªº¥Îªk ¤§«á¦pªG¦P®É«Ü¦hÄæ¦ìªº¸Ü ¨Ï¥Î¤W·|¤ñ¸û¤è«K

ML089

¬O§_¦³¤@¼Ëªº²Õ¦X.rar (1002.85 KB)

¤U¸ü¦¸¼Æ: 7

2017-4-29 17:43

eric7765

¦^´_ 25# ML089
©êºp §Ú¸ÕµÛ¬ã¨s¦n¤[ ¦ýÁÙ¬O±o¤£¥Xµ²ªG ¤£ª¾¹D¬O§Ú­þ¸Ì²z¸Ñ¿ù»~¤F

¤U¸ü (62.85 KB)

2017-4-29 17:35

½Ð°Ý¬O§_¦³¸òD2:D7¤@¼Ëªº²Õ¦X À³¸Ó­n¿é¤J­þ­Ó¤½¦¡?
¸òD2:D7¤@¼Ëªº²Õ¦X Á`¦@¦³´X²Õ À³¸Ó­n¥Î­þºØ¤½¦¡?

ML089

¦^´_ 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))

eric7765

¦^´_ 22# ML089
¤j¯«ª©¥D ¯uªº«Ü·PÁ§Aªº±Ð¾É ¥]¬A¤§«e³£²ßºD¥Î=¸¹ªº²ßºD ²{¦b¤S¦³¤F·s¤èªk
«á½ú¤]¬ã¨s¥X¤ß±o¨Ó¤F
¤]¤S·Q¨ì¤@­Ó°ÝÃD·Qµo°Ý ¤£ª¾¹D³o°ÝÃD¬O§_¥ÎEXCEL ¯à¸Ñªº¥X¨Ó

¤U¸ü (68.54 KB)

2017-4-29 08:23

¬O§_¦³¤@¼Ëªº²Õ¦X.rar (369.92 KB)

¤U¸ü¦¸¼Æ: 6

2017-4-29 08:24

¦A³Â·Ð«e½ú­Ì«ü¾É

eric7765

¦^´_ 19# handmuch
ÁÂÁ«e½úªº¤èªk ¾A¦X§Ú³o­Ó·s¤â

ML089

¦^´_ 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

ML089

¦^´_ 16# eric7765

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

³o¬O¤£­nªº¼Æ¦r(¬°1¤Î0ªº²Î­p¼Æ)¡A¥u¬O¨q¥X¨Ó¤F¸Ñ