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

[µo°Ý] ½Ð°Ýsum¨ç¼Æ¦p¦ó¥[Á`¤£½T©wªº½d³ò?(¥Ø«e¥ÎmatchµLªk¸Ñ¨M)

[µo°Ý] ½Ð°Ýsum¨ç¼Æ¦p¦ó¥[Á`¤£½T©wªº½d³ò?(¥Ø«e¥ÎmatchµLªk¸Ñ¨M)

¥»©«³Ì«á¥Ñ summergod ©ó 2015-5-11 21:41 ½s¿è

½Ð°Ý¦U¦ì«e½ú ¦pªG§Ú·Q¦b¤@¦ê¶Ã¼Æ¸ê®Æ¤¤ ²Î­p "©M©P³ò¼Æ¦r¥[°_¨Óµ¥©ó20" ªº¸ê®Æ¦³´X­Ó ¸Ó¦p¦ó°µ©O?

ex :
            ¸ê®Æ--1  18  20   5  16  17  11   8  13 15   5   2  18   7   1   1   10   6   2
²Å¦Xªº±¡ªp--             X                                            X       X                              X
=> XÁ`¦@¦³4­Ó

¥Ø«e¦]¬°¤£ª¾¹Dsumªº½d³ò­n¥[´X­Ó
©Ò¥H¥u¦n¥Î³oºØ¼gªk¦b¸ê®Æ®ÇÃä¤@­Ó­Ó¥[----if( sum(a1:a2)=20, "X", if( sum(a1:a3)=20, "X", if( sum(a1:a4)=20, "X".............)
(´N¬O¥Îsum¤@®æ¨â®æ¤T®æ....¨ìN®æ³o¼Ë¥hÀˬd¡A³Ì«á¦A²Î­p"X"ªº­Ó¼Æ)

½Ð°Ý¦³¤°»ò¨ç¼Æªº²Õ¦X¤è¦¡¥i¥HÂ\²æ³oºØ¦aº» ? QAQ

«Øij¨Ï¥ÎVBA¨Ó¸Ñ¨M¡A³æ¯Â¥Î¤½¦¡Ãø¥H¸Ñ¨M¡A¦]¬°³o¬O¶¥­¼¼Æ¾Ç¡A¨Ò¦p°²³]¦³10­ÓÅܼơA¨º´N¦³
10+9+8+...+2+1=55ºØ²Õ¦X¡A¤]´N¬O­n¼g55­Óif¡A³o¬O¤£¥i¯àªº¡A¦]¬°ExcelªºÀx¦s®æ¤º¦n¹³¥u¯à¼g10­Óif¡A©Ò¥H¥²»Ý¨Ï¥ÎVBAªº¼gªk¡A¤]´N¬O°j°é¼gªk

TOP

¦^´_ 2# tyrone123456


¦hÁ«e½ú«üÂI
¤§«e¦³¥Î¹LVBA
¤£¹L¦]¬°¨C¦¸³£­n¦A­«¶]¤@¦¸¦Ó¥B¶]«Ü¤[(¥¨¶°¼g±o¤ÓÁcº¾¤Forz
©Ò¥H¤~·Q¥Î¨ç¼Æ¤@¦¸¸Ñ¨M

³o­Ó°ÝÃDªº­ì«¬¤j·§¦p¤U--
°²³]¨º¨ÇÅܼƬ°"¨C¦¸­°«B¤é©Ò¶¡¹jªº¤Ñ¼Æ" ( ²Ä1¦¸­°«B«á¹L¤FX¤Ñ«á¤S­°«B...³o¼Ëªºªí²{ ),¨º­n¦p¦ó²Î­p¥X"¨ì¤F20¤Ñ¤@©w·|­°«B"ªº±¡ªp?

¥Ø«e¦Û¤vªº¸Ñ¨M¤è¦¡ ´N¦^¨ì­ì©lªº¸ê®Æ¨ºùØ ¥Îcountifs ¨Ó²Î­p¨â­Ó½d³ò(¤µ¤Ñ&20¤Ñ«e)³£¦³">=1"ªº±¡ªp
±o¨ì²Î­pµ²ªG
ºâ¬O¸Ñ²æ¤F¨ç¼Æ¦aº»  
¤£¹L¦pªG¥H«á¦A¹J¨ìÃþ¦üªº±¡ªp¥i¯à·|GG

©Ò¥H¦pªG¦U¦ì«e½ú¦³¤°»òÂI¤lÁٽФ£§[½ç±Ð ·PÁ¤j®a

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2015-5-14 15:59 ½s¿è

¦^´_ 3# summergod


   

B2 =IF(ISNA(MATCH(20,SUBTOTAL(9,OFFSET(B1,,,,{1,2,3,4,5,6,7,8,9})),)),"","X")

¤½¦¡¥k©Ô½Æ»s


20¤Ñ­°«B»Ý­n±N {1,2,3,4,5,6,7,8,9} §ï¬° {1,2,3,4,5,6,7,8,9 .... 20}
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¦³Ä@©ñ¦b¤ß¸Ì¡A¨S¦³¨­Åé¤O¦æ¡A¥¿¦p¯Ñ¥Ð¤£¼½ºØ¡A¬Ò¬OªÅ¹L¦]½t¡C
ªð¦^¦Cªí ¤W¤@¥DÃD