Excel word ppt officeʹÓü¼ÇÉ´óÈ«(DOC¸ñʽ)-µÚ35²¿·Ö

¿ì½Ý²Ù×÷: °´¼üÅÌÉÏ·½Ïò¼ü ¡û »ò ¡ú ¿É¿ìËÙÉÏÏ·­Ò³ °´¼üÅÌÉ쵀 Enter ¼ü¿É»Øµ½±¾ÊéĿ¼ҳ °´¼üÅÌÉÏ·½Ïò¼ü ¡ü ¿É»Øµ½±¾Ò³¶¥²¿! Èç¹û±¾ÊéûÓÐÔĶÁÍ꣬ÏëÏ´μÌÐø½Ó×ÅÔĶÁ£¬¿ÉʹÓÃÉÏ·½ "Êղص½ÎÒµÄä¯ÀÀÆ÷" ¹¦ÄÜ ºÍ "¼ÓÈëÊéÇ©" ¹¦ÄÜ£¡


Application¡£ScreenUpdating¡¡=¡¡True¡¡¡¡¡¡¡¡

End¡¡Sub¡¡¡¡¡¡¡¡

042¡£Çó½ÌºÏ²¢µ¥Ôª¸ñÇøÓòµÄÁ¬Ðø¶ÁÈ¡·½·¨¡¡¡¡

Çó½Ì£º1¡¢ÈçºÎÑ¡¶¨Á¬ÐøµÄºÏ²¢µ¥Ôª¸ñÇøÓò£»2¡¢ÈçºÎÁ¬Ðø¶ÁÈ¡ºÏ²¢µ¥Ôª¸ñÖеÄÄÚÈÝ¡£¡¡¡¡

½â´ð£ºPublic¡¡Sub¡¡adre£¨£©¡¡¡¡¡¡¡¡

Dim¡¡cell¡¡As¡¡Range¡¡¡¡¡¡¡¡

Dim¡¡iRow_dn1¡¡As¡¡Integer¡¡¡¡¡¡¡¡

iRow_dn1¡¡=¡¡£§B65536£§¡£End£¨xlUp£©¡£Row¡¡¡¡¡¡¡¡

Set¡¡av1¡¡=¡¡Range£¨¡¨B3£ºB¡¨¡¡&¡¡iRow_dn1£©¡¡¡¡¡¡¡¡

For¡¡Each¡¡cell¡¡In¡¡av1¡¡¡¡¡¡¡¡

If¡¡cell¡¡¡¡¡¨¡¨¡¡Then¡¡¡¡¡¡¡¡

MsgBox¡¡cell¡£Address¡¡&¡¡¡¨¡¡¡¡µÈ춡¡¡¨¡¡&¡¡¡¨¡¡¡¡o¡¡¡¨¡¡&¡¡cell¡¡&¡¡¡¨¡¡¡¡¡ì¡¨¡¡¡¡¡¡¡¡

End¡¡If¡¡¡¡¡¡¡¡

Next¡¡¡¡¡¡¡¡

End¡¡Sub¡¡¡¡¡¡¡¡

043¡£ÇóÒ»¹«Ê½¡¡¡¡

sheet1¡¡sheet2¡¡¡¡¡¡¡¡

A¡¡B¡¡C¡¡A¡¡B¡¡C¡¡¡¡¡¡¡¡

1¡¡¡¡²úÆ·´úÂë²úÆ·Ãû¡¡Éú²ú»úÆ÷Ãû¡¡²úÆ·´úÂë¡¡²úÆ·Ãû¡¡Éú²ú»úÆ÷Ãû¡¡¡¡¡¡

2¡¡012354¡¡a203¡¡1m¯¡¡225894¡¡nj033¡¡¡¡£¿¡¡¡¡¡¡

3¡¡214345¡¡b4032¡¡¡¡·¢Åݯ¡¡056894¡¡kkl001¡¡¡¡£¿¡¡¡¡¡¡

4¡¡225894¡¡nj033¡¡1m¯¡¡214345¡¡b4032¡¡¡¡£¿¡¡¡¡¡¡

5¡¡056894¡¡kkl001¡¡¡¡·¢Åݯ¡¡¡¡

6¡¡124589¡¡lli002¡¡1m¯¡¡¡¡

SHEET1ÊÇÒ»ÕÅÔ´×ÊÁÏ±í£¬¶øSHEET2ÊÇÒ»¸öÉú²ú¼Æ»®±íµÄÒ»²¿·Ö¡£¡¡¡¡

ÇëÎÊ£º¡¡¡¡¡¡

ÎÒÇóSHEET2ÖеÄAÁÐÖвúÆ·´úÂëÏà¶ÔÓ¦µÄCÁеġ±Éú²ú»úÆ÷Ãû¡°¡¡¡£¡¡¡¡

Õâ¸ö¹«Ê½Ôõôд£¿¡¡¡¡¡¡

½â´ð£ºSheet2µÄC2¸ñ¹«Ê½Îª£º=VLOOKUP£¨A2£»SHEET1£¡A£ºC£»3£»0£©¡¡¡¡¡¡

044¡£ÌÖÂÛÒ»ÏÂÈ¡×îºóÒ»¸öµ¥´ÊµÄ·½·¨¡¡¡¡

ÀýÈçÏÖÔÚÔÚA1¡¡ÖÐÓÐÒ»¾ä¡°M¡£¡¡Henry¡¡Jackey¡±£¬ÈçºÎÓú¯Êý½«×îºóµÄÒ»¸öµ¥´ÊÈ¡³öÀ´ÄØ£¿¡¡µ±È»£¬¡¡

ÎÒÃÇÏÖÔÚÊÇÖªµÀ×îºóµÄµ¥´ÊÊÇ6¸ö×Ö·û£¬¿ÉÒÔÓÃRight£¨A1£»6£©À´¼ÆË㣬µ«Èç¹û×îºóÒ»¸öµ¥´ÊµÄ×Ö¡¡

·ûÊýÊDz»¶¨µÄÄØ£¬Èç¹û×öÄØ£¿¡¡Çë´ó¼ÒÊÔÏÂÓм¸ÖÖ·½·¨¡£¡¡¡¡¡¡

½â´ð£º·½·¨1¡¢ÓÃÒ»Áй«Ê½Ìî³ä¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡CCXL¡¡¡¡


¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­Page¡¡241¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­

¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡Excel_word_ppt_ʹÓü¼ÇÉ´óÈ«£¨ÍêÈ«°æ£©¡¡¡¡



=IF£¨LEFT£¨RIGHT£¨A1£»ROW£¨£©£©£»1£©=CHAR£¨32£©£»RIGHT£¨A1£»ROW£¨£©¡­1£©£»¡°¡±£©¡¡¡¡¡¡

·½¡¡·¨¡¡2¡¡¡¢¡¡=MID£¨A1£»FIND£¨¡¨¡¡¡¡¡¡*¡¨£»SUBSTITUTE£¨A1£»¡¨¡¡¡¡¡¡¡¨£»¡¨¡¡¡¡¡¡*¡¨£»LEN£¨A1£©¡­LEN£¨SUBSTITUTE£¨A1£»¡¨¡¡¡¡

¡¨£»¡¨¡¨£©£©£©£©£«1£»LEN£¨A1£©¡­FIND£¨¡¨¡¡¡¨£»A1£©£©¡¡¡¡¡¡¡¡

·½·¨3¡¢ÓÃ×Ô¶¨Ò庯Êýµ±È»·½±ã£¬¶øÇÒ¼òµ¥¡£¡¡¡¡

Function¡¡xx£¨n¡¡As¡¡String£©¡¡As¡¡String¡¡¡¡¡¡¡¡

n¡¡=¡¡Application¡£Trim£¨n£©¡¡¡¡¡¡¡¡

lastone¡¡=¡¡Right£¨n£»¡¡Len£¨n£©¡¡InStrRev£¨n£»¡¡¡¨¡¡¡¨£©£©¡¡¡¡¡¡¡¡

xx¡¡=¡¡lastone¡¡¡¡¡¡¡¡

End¡¡Function¡¡¡¡¡¡¡¡

·½·¨4¡¡¡¢=IF£¨ISERROR£¨SEARCH£¨¡¨¡¨£»TRIM£¨LEFT£¨B1£©£©£©£©£»RIGHT£¨A1£»ROW£¨£©£©£»¡¨¡¨£©ÍϳöÀ´µÄµÚÒ»¡¡

¸ö×Ö·û¾ÍÐС£¡¡¡¡

·½¡¡¡¡¡¡¡¡·¨¡¡¡¡¡¡¡¡5¡¡¡¡¡¢¡¡¡¡£û=RIGHT£¨A1£»LEN£¨A1£©¡­MAX£¨£¨MID£¨A1£»ROW£¨INDIRECT£¨¡¨1£º¡¨&LEN£¨A1£©£©£©£»1£©=¡¨¡¡¡¡

¡¨£©*ROW£¨INDIRECT£¨¡¨1£º¡¨&LEN£¨A1£©£©£©£©£©£ý¡¡¡¡¡¡¡¡

ÏÓ³¤¾Í£¨¼Ù¶¨×100×Ö·û£©¡¡¡¡¡¡

£û=RIGHT£¨A1£»LEN£¨A1£©¡­MAX£¨£¨MID£¨A1£»ROW£¨1£º100£©£»1£©=¡¨¡¡¡¨£©*ROW£¨1£º100£©£©£©£ý¡¡¡¡¡¡¡¡

045¡£ÈçºÎ»ñÈ¡¹¤×÷±íÖÐijһÁÐÓжàÉÙÌõ¼Ç¼£¿¡¡¡¡¡¡

ÒòΪÿһÁеĵļǼ¶¼²»Ò»Ñù¶à£¬ËùÒÔÎÒÏë»ñµÃÿһÁи÷ÓжàÉÙÌõ¼Ç¼£¬Ôõô×ö£¿¡¡¡¡¡¡

½â´ð£ºRecordNumbers=Application¡£COUNTA£¨A£ºA£©¡¡¡¡¡¡¡¡

»òÕߣºPrivate¡¡Sub¡¡UserForm_Activate£¨£©¡¡¡¡¡¡¡¡

x¡¡=¡¡Sheet1¡£UsedRange¡£Rows¡£Count¡¡¡¡¡¡¡¡

x1¡¡=¡¡Sheet1¡£CountA£¨c4£»¡¡cx£©¡¡¡¡¡¡¡¡

Ò²¿ÉÒÔ£ºSub¡¡aa£¨£©¡¡¡¡¡¡¡¡

MsgBox¡¡£¨Application¡£CountA£¨Range£¨¡¨A£ºA¡¨£©£©£©¡¡¡¡¡¡¡¡

End¡¡Sub¡¡¡¡¡¡¡¡

»¹¿ÉÒÔ£ºSub¡¡aa£¨£©¡¡¡¡¡¡¡¡

x¡¡=¡¡Sheet1¡£UsedRange¡£Rows¡£Count¡¡¡¡¡¡¡¡

MsgBox¡¡£¨Application¡£CountA£¨Range£¨¡¨c3£ºcx¡¨£©£©£©¡¡¡¡¡¡¡¡

End¡¡Sub¡¡¡¡¡¡¡¡

ÕâÑùÒ²ÐУºÓÃÏÂÃæµÄ·½·¨¿É²â³öÈÎÒ»ÁÐʹÓõÄÐÐÊý¡¡¡¡

a=Sheet1¡£range£¨¡¨b1¡¨£©¡£End£¨xlDown£©¡£Row¡¡¡£¡¡¡¡

×ܽ᣺¡¡¡¡¡¡

1¡£Sub¡¡aa£¨£©¡¡¡¡¡¡

MsgBox¡¡£¨Application¡£CountA£¨Range£¨¡¨C£ºC¡¨£©£©£©¡¡¡¡¡¡¡¡

End¡¡Sub¡¡¡¡¡¡¡¡

½á¹ûÓÀÔ¶¶¼ÊÇ1»òÕß3£¬¿ÉÊÇʵ¼ÊÉϼǼÓÐ600¶àÌõ¡¡¡¡

2¡£¡¡¡¡¡¡¡¡

Sub¡¡aa£¨£©¡¡¡¡¡¡¡¡

Worksheets£¨¡¨sheet1¡¨£©¡£Activate¡¡¡¡¡¡¡¡

Range£¨¡¨c2¡¨£©¡£Select¡¡¡¡¡¡¡¡

x1¡¡=¡¡¡¨=COUNTA£¨sheet1£¡C£©¡¨¡¡¡¡¡¡¡¡

MsgBox¡¡x1¡¡¡¡¡¡¡¡

End¡¡Sub¡¡¡¡¡¡¡¡

Õâ¸öÊÇ¿´fhj¡¡¡¡Ê¾ÀýµÄÎļþ¼ÖƳɺê¸ÄµÄ£¬²»¹ýÔËÐнá¹ûÓÀÔ¶ÊÇ¡¡=counta£¨sheet1£¡c£©¡¡¡¡¡¡

3¡£¡¡¡¡¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡CCXLI¡¡¡¡


¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­Page¡¡242¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­

¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡Excel_word_ppt_ʹÓü¼ÇÉ´óÈ«£¨ÍêÈ«°æ£©¡¡¡¡



Sub¡¡aa£¨£©¡¡¡¡¡¡¡¡

x1¡¡=¡¡¡¨=COUNTA£¨sheet1£¡C£©¡¨¡¡¡¡¡¡¡¡

MsgBox¡¡x1¡¡¡¡¡¡¡¡

end¡¡sub¡¡¡¡¡¡¡¡

ÌáʾºÍÇ°ÃæµÄÒ»Ñù¡£¡¡¡¡

4¡£ÆäʵÒѾ­ÊÔÁ˼¸Ê®ÖÖ·½·¨ÁË¡£»¹ÊÇ´íµÄ¡£×÷Ϊ¹«Ê½Ê±£¬ÊÇ¿ÉÒÔʹÓᣵ«ÊÇÈ´¡¡¡¡

ÎÞ·¨°Ñ»ñµÃµÄÖµ¸³Öµ¸øÒ»¸ö±äÁ¿¡£³ý·ÇÊÇÏÈдµ½Ò»¸öµ¥Ôª¸ñÀÔÙÖØжÁ³öÀ´¡£¡¡¡¡¡¡

²»¹ýÎÒ¾õµÃÌ«Âé·³ÁË¡£¶øÇÒдµÄʱºò»áÐ޸Ť×÷±í¡£²»ÊǺÜÇ¡µ±¡£¡¡¡¡¡¡

½â´ð£ºApplication¡£CountA£¨Range£¨¡¨C£ºC¡¨£©£©·µ»Ø³ýÈ¥ÎÞÖµµ¥Ôª¸ñµÄËùÓе¥Ôª¸ñµÄÊýÁ¿¡£¡¡¡¡

Sheet1¡£range£¨¡¨C1¡¨£©¡£End£¨xlDown£©¡£Row·µ»ØµÚÒ»´ÎÓöµ½¿Õµ¥Ôª¸ñÇ°µÄµ¥Ôª¸ñµÄÊýÁ¿¡£¡¡¡¡

£¨×¢£ºµ±CÁÐÓпհ׵¥Ôª¸ñʱÓ㺡¡¡¡¡¡

myEndRow=sheets£¨¡¨sheet1¡¨£©¡£range£¨¡¨C65536¡¨£©¡£End£¨xlUp£©¡£row£©¡¡¡¡¡¡¡¡

½áÂÛ£ºSub¡¡aa£¨£©¡¡¡¡¡¡¡¡

x1¡¡=¡¡Sheet1¡£Range£¨¡¨C3¡¨£©¡£End£¨xlDown£©¡£Row¡¡¡¡¡¡¡¡

MsgBox¡¡x1¡¡¡¡¡¡¡¡

end¡¡sub¡¡¡¡¡¡¡¡

Õâ¾Í¶ÔÁË¡£Ð»Ð»¸÷λ£¡¡¡¡¡¡¡

»ØÓ¦£ºÍƼöÄãÓá¡¡¡¡¡

Columns£¨1£©¡£SpecialCells£¨xlCellTypeConstants£©¡£Count¡¡¡¡¡¡¡¡

045¡£ÈçºÎ½ûÖ¹ÊäÈë¿Õ¸ñ¡¡¡¡

ÔÚExcelÖÐÈçºÎͨ¹ý±à¼­¡°ÓÐЧÊý¾Ý¡±À´½ûֹ¼Èë¿Õ¸ñ£¿·³Çë´óÏÀÃÇ·ÑÐĽâ´ð¡£²»Ê¤¸Ð¼¤¡£¡¡¡¡

½â´ð£ºÓÐЧÐÔ¹«Ê½¡£=COUNTIF£¨A1£»¡¨*¡¡*¡¨£©=0¡¡¡¡¡¡

£¨×¢£ºCOUNTIF£¨A1£»¡¨*¡¡*¡¨£©¡¡¡¡ÔÚµ¥Ôª¸ñÓпոñʱ½á¹ûΪ1£»Ã»Óпոñʱ½á¹ûΪ0¡¡¡¡¡¡¡¡

ÈçÏ£ÍûµÚһλ²»ÄÜÊäÈë¿Õ¸ñ£ºcountif£¨a1£»¡¨¡¡*¡¨£©=0¡¡¡¡¡¡

ÈçÏ£Íû×îºóһλ²»ÄÜÊäÈë¿Õ¸ñ£ºcountif£¨a1£»¡¨*¡¡¡¨£©=0£©¡¡¡¡¡¡

046¡£ÈçºÎÅжϵ¥Ôª¸ñÖе¥´ÊµÄÊýÁ¿£¿¡¡¡¡¡¡

±ÈÈçÎÒÔÚA1¡¡ÖÐÊäÈë¡°you¡¡are¡¡a¡¡good¡¡boy¡±ÈçºÎÅжϵ¥´ÊΪ5¸ö£¿¡¡¡¡¡¡

½â´ð£º=LEN£¨E12£©¡­LEN£¨SUBSTITUTE£¨E12£»¡¨¡¡¡¨£»¡¨¡¨£©£©£«1¡¡¡¡¡¡¡¡

£¨×¢£º·½·¨ºÜÇÉÃîÓÃtrim°ÑÇ°ºóµÄ¿Õ¸ñÈ¥µô¡£Èç¹ûÓбêµã·ûºÅ»òÕßÁ½¸ö´ÊÖ®¼äµÄ¿Õ¸ñÊý´óÓÚ1¸ö¡¡

¾Í²»ºÃ°ìÁË£©¡¡¡¡¡¡¡¡

047¡£ÈçºÎÈ¡Êý¡¡¡¡

±íÒ»ÓÐÊý¾Ý£¬ÒªÇó±í¶þÖÐÊý¾ÝΪȡһÐбíÒ»Êý¾Ý£¬¿ÕÒ»ÐС£¡¡¡¡¡¡

½â´ð£º¡¡¡¡¡¡

Sub¡¡test£¨£©¡¡¡¡¡¡¡¡

On¡¡Error¡¡Resume¡¡Next¡¡¡¡¡¡¡¡

Application¡£ScreenUpdating¡¡=¡¡False¡¡¡¡¡¡¡¡

For¡¡i¡¡=¡¡1¡¡To¡¡Sheets£¨1£©¡£UsedRange¡£Rows¡£Count¡¡¡¡¡¡¡¡

Sheets£¨1£©¡£Rows£¨VBA¡£Trim£¨VBA¡£Str£¨i£©£©¡¡£«¡¡¡¨£º¡¨¡¡£«¡¡VBA¡£Trim£¨VBA¡£Str£¨i£©£©£©¡£Copy¡¡¡¡¡¡¡¡

Sheets£¨2£©¡£Activate¡¡¡¡¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡CCXLII¡¡¡¡


¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­Page¡¡243¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­

¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡Excel_word_ppt_ʹÓü¼ÇÉ´óÈ«£¨ÍêÈ«°æ£©¡¡¡¡



Sheets£¨2£©¡£Rows£¨VBA¡£Trim£¨VBA¡£Str£¨i¡¡*¡¡2¡¡¡¡1£©£©¡¡£«¡¡¡¨£º¡¨¡¡£«¡¡VBA¡£Trim£¨VBA¡£Str£¨i¡¡*¡¡2¡¡¡¡1£©£©£©¡£Select¡¡¡¡¡¡

ActiveSheet¡£Paste¡¡¡¡¡¡¡¡

Next¡¡i¡¡¡¡¡¡¡¡

Application¡£ScreenUpdating¡¡=¡¡True¡¡¡¡¡¡¡¡

End¡¡Sub¡¡¡¡¡¡¡¡

048¡£ÈçºÎͨ¹ýVBA±à³Ì½«·ûºÏÌõ¼þµÄÊý¾Ý¿â¼Ç¼ÊäÈëµ½EXCELÖС¡¡¡

ÏÖÔÚÓÐaccess¸ñʽµÄÊý¾Ý±í¡¡TEST¡¡¡¡¡¡¡¡

»õºÅ¡¡»õÃû¡¡¹æ¸ñ¡¡µ¥¼Û¡£¡£¡£¡£¡¡¡¡¡¡¡¡

1¡­01¡¡¡¡»õÆ·1¡¡1M¡¡250¡£00¡¡¡¡¡¡

1¡­02¡¡¡¡»õÆ·2¡¡4Kg¡¡100¡£00¡¡¡¡¡¡¡¡

¡£¡£¡£¡£¡£¡£¡£¡£¡£¡£¡£¡£¡£¡£¡£¡£¡¡¡¡¡¡¡¡

N¡­99¡¡¡¡»õÆ·N¡¡999¡¡999¡£99¡¡¡¡¡¡¡¡

ÏÖÔÚÎÒÏëÔÚEXCELµÄµ¥Ôª¸ñÖÐÊäÈë»õºÅ£¬Í¨¹ýVBA´úÂë×Ô¶¯´ÓÊý¾Ý±íÖвéÕÒ³öÏàÓ¦µÄ¼Ç¼£¬²¢¡¡

ÔÚÏàÁÚµÄÁзֱð×Ô¶¯Â¼Èë»õÆ·¡¢¹æ¸ñ¡¢µ¥¼ÛµÈÄÚÈÝ£¬´Ó¶øʵÏÖEXCEL¡¡×Ô¶¯Êý¾Ý¼Èë¡£ÇëÎÊÕâVBA¡¡

´úÂëÓ¦ÈçºÎд£¿Ð»Ð»£¡¡¡¡¡¡¡

½â´ð£ºPrivate¡¡Sub¡¡Worksheet_Change£¨ByVal¡¡Target¡¡As¡¡Range£©¡¡¡¡¡¡¡¡

Dim¡¡Rs¡¡As¡¡New¡¡ADODB¡£Recordset¡¡¡¡¡¡¡¡

Dim¡¡Query¡¡As¡¡String¡¡¡¡¡¡¡¡

Dim¡¡Cnn¡¡As¡¡String¡¡¡¡¡¡¡¡

With¡¡Application¡¡¡¡¡¡¡¡

¡£ScreenUpdating¡¡=¡¡False¡¡¡¡¡¡

¡£EnableEvents¡¡=¡¡False¡¡¡¡¡¡

End¡¡With¡¡¡¡¡¡¡¡

Cnn¡¡=¡¡¡¨Driver=Microsoft¡¡Access¡¡Driver¡¡£¨*¡£mdb£©£»DBQ=C£º*¡£mdb¡¨¡¡¡¡¡¡¡¡

Query¡¡=¡¡¡¨SELECT¡¡*¡¡FROM¡¡TEXT¡¡WHERE¡¡¡¡»õºÅ='¡¨¡¡&¡¡Target¡¡&¡¡¡¨'¡¨¡¡¡¡¡¡¡¡

With¡¡Rs¡¡¡¡¡¡¡¡

¡£Open¡¡Query£»¡¡Cnn£»¡¡adOpenStatic£»¡¡adLockReadOnly¡¡¡¡¡¡

If¡¡¡£RecordCount¡¡=¡¡0¡¡Then¡¡¡¡¡¡¡¡

MsgBox¡¡¡¨Ã»Óд˻õºÅ£¡¡¨¡¡¡¡¡¡¡¡

Target¡£ClearContents¡¡¡¡¡¡¡¡

Else¡¡¡¡¡¡¡¡

Target¡£CopyFromRecordset¡¡Rs¡¡¡¡¡¡¡¡

End¡¡If¡¡¡¡¡¡¡¡

¡£Close¡¡¡¡¡¡

End¡¡With¡¡¡¡¡¡¡¡

With¡¡Application¡¡¡¡¡¡¡¡

¡£ScreenUpdating¡¡=¡¡True¡¡¡¡¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡CCXLIII¡¡¡¡


¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­Page¡¡244¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­

¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡Excel_word_ppt_ʹÓü¼ÇÉ´óÈ«£¨ÍêÈ«°æ£©¡¡¡¡



¡£EnableEvents¡¡=¡¡True¡¡¡¡¡¡¡¡

End¡¡With¡¡¡¡¡¡¡¡

End¡¡Sub¡¡¡¡¡¡¡¡

049¡£ÈçºÎͳ¼ÆÒ»¸öµ¥Ôª¸ñÖеÄÊý×ÖÓм¸Î»Êý£¿¡¡¡¡¡¡

½â´ð1£º=LEN¡¡¡¡£¨µ¥Ôª¸ñµØÖ·£©¡¡¡¡¡¡

ÓÖÎÊ£ºÈç¹ûÖмäÓкº×Ö»òÕßÊÇ×Öĸ¡¢·ûºÅÄØ£¬»òÊÇÊýÕ߶¼ÓÐÄØ£¿¡¡¡¡¡¡

½â´ð2£º=SUM£¨LEN£¨A1£©¡­LEN£¨SUBSTITUTE£¨A1£»£û¡¨0¡¨£»¡¨1¡¨£»¡¨2¡¨£»¡¨3¡¨£»¡¨4¡¨£»¡¨5¡¨£»¡¨6¡¨£»¡¨7¡¨£»¡¨8¡¨£»¡¨9¡¨£ý£»¡¨¡¨£©£©£©¡¡¡¡¡¡¡¡

½â´ð3£º=2*LEN£¨A1£©¡­LENB£¨A1£©¡¡¡¡¡¡¡¡

£¨±¸×¢£ºŒ¦ÖÐÎÄ¡¡£«¡¡¡¡”µ×ÖÊǺܺõÄ×ö·¨£¬µ«ÓÐÓ¢ÎÄ×Ö¡¡»ò¡¡ÖÐÎÄ¡¡£«¡¡¡¡”µ×Ö¡¡£«¡¡¡¡Ó¢Îľͳöåe£©¡¡¡¡¡¡¡¡

050¡£ÈçºÎßxÈ¡ÁЃȲ»ÖØÑ£ýÙYÁÏ¡¡¡¡

½â´ð1£ºSub¡¡ff£¨£©¡¡¡¡¡¡¡¡

Dim¡¡aa¡¡As¡¡New¡¡Collection¡¡¡¡¡¡¡¡

cc¡¡=¡¡2¡¡¡¡¡¡¡¡

On¡¡Error¡¡Resume¡¡Next¡¡¡¡¡¡¡¡

Do¡¡Until¡¡Cells£¨3£»¡¡cc£©¡¡=¡¡¡¨¡¨¡¡¡¡¡¡¡¡

aa¡£Add¡¡Cells£¨3£»¡¡cc£©¡£value£»¡¡Cells£¨3£»¡¡cc£©¡£Text¡¡¡¡¡¡¡¡

cc¡¡=¡¡cc¡¡£«¡¡1¡¡¡¡¡¡¡¡

Loop¡¡¡¡¡¡¡¡

On¡¡Error¡¡GoTo¡¡0¡¡¡¡¡¡¡¡

cc¡¡=¡¡2¡¡¡¡¡¡¡¡

For¡¡Each¡¡itm¡¡In¡¡aa¡¡¡¡¡¡¡¡

Cells£¨5£»¡¡cc£©¡¡=¡¡itm¡¡¡¡¡¡¡¡

cc¡¡=¡¡cc¡¡£«¡¡1¡¡¡¡¡¡¡¡

Next¡¡¡¡¡¡¡¡

End¡¡Sub¡¡¡¡¡¡¡¡

½â´ð2£ºÌṩһ‚€ÒÔê‡Áз½·¨žé˼¿¼Ä£Ê½µÄ·½·¨¡¡¡¡

Sub¡¡ff£¨£©¡¡¡¡¡¡¡¡

On¡¡Error¡¡Resume¡¡Next¡¡¡¡¡¡¡¡

cc¡¡=¡¡2¡¡¡¡¡¡¡¡

Set¡¡NoDupes¡¡=¡¡CreateObject£¨¡¨Scripting¡£Dictionary¡¨£©¡¡¡¡¡¡¡¡

Do¡¡Until¡¡Cells£¨3£»¡¡cc£©¡¡=¡¡¡¨¡¨¡¡¡¡¡¡¡¡

NoDupes¡£Add¡¡Cells£¨3£»¡¡cc£©¡£value£»¡¡Cells£¨3£»¡¡cc£©¡£value¡¡¡¡¡¡¡¡

cc¡¡=¡¡cc¡¡£«¡¡1¡¡¡¡¡¡¡¡

Loop¡¡¡¡¡¡¡¡

Range£¨¡¨B6¡¨£©¡£Resize£¨1£»¡¡UBound£¨NoDupes¡£keys£©¡¡£«¡¡1£©¡¡=¡¡NoDupes¡£keys¡¡'ê‡ÁÐÓÉ0é_ʼ£»ËùÒÔ£«1¡¡¡¡¡¡¡¡

End¡¡Sub¡¡¡¡

¡¡¡¡

¡¡¡¡

¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡Excel¡¡±í¸ñµÄ¡¡35¡¡ÕбØѧÃؼ¼¡¡¡¡



¡¡¡¡¡¡¡¡¡¡Ò²ÐíÄãÒѾ­ÔÚ¡¡Excel¡¡ÖÐÍê³É¹ýÉÏ°ÙÕŲÆÎñ±¨±í£¬Ò²ÐíÄãÒÑÀûÓÃExcel¡¡º¯ÊýʵÏÖ¹ýÉÏǧ´ÎµÄ¡¡



¸´ÔÓÔËË㣬ҲÐíÄãÈÏΪ¡¡Excel¡¡Ò²²»¹ýÈç´Ë£¬ÉõÖÁÁËÎÞÐÂÒâ¡£µ«ÎÒÃÇƽÈÕÀïÎÞÊý´ÎÖظ´µÄµÃÐÄÓ¦¡¡



ÊÖµÄʹÓ÷½·¨Ö»²»¹ýÊÇ¡¡Excel¡¡È«²¿¼¼ÇɵİٷÖÖ®Ò»¡£±¾×¨Ìâ´Ó¡¡Excel¡¡ÖеÄһЩÏÊΪÈËÖªµÄ¼¼ÇÉ¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡CCXLIV¡¡¡¡


¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­Page¡¡245¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­

¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡Excel_word_ppt_ʹÓü¼ÇÉ´óÈ«£¨ÍêÈ«°æ£©¡¡¡¡



ÈëÊÖ£¬ÁìÂÔһϹØÓÚ¡¡Excel¡¡µÄ±ðÑù·çÇé¡£¡¡¡¡¡¡¡¡



Ò»¡¢Èò»Í¬ÀàÐÍÊý¾ÝÓò»Í¬ÑÕÉ«ÏÔʾ¡¡¡¡¡¡¡¡



¡¡¡¡¡¡¡¡ÔÚ¹¤×ʱíÖУ¬Èç¹ûÏëÈôóÓÚµÈÓÚ¡¡2000¡¡ÔªµÄ¹¤×Ê×ܶîÒÔ¡°ºìÉ«¡±ÏÔʾ£¬´óÓÚµÈÓÚ¡¡1500¡¡ÔªµÄ¡¡



¹¤×Ê×ܶîÒÔ¡°À¶É«¡±ÏÔʾ£¬µÍÓÚ¡¡1000¡¡ÔªµÄ¹¤×Ê×ܶîÒÔ¡°×ØÉ«¡±ÏÔʾ£¬ÆäËüÒÔ¡°ºÚÉ«¡±ÏÔʾ£¬¡¡



ÎÒÃÇ¿ÉÒÔÕâÑùÉèÖᣡ¡¡¡¡¡¡¡



¡¡¡¡¡¡¡¡1¡£´ò¿ª¡°¹¤×Ê±í¡±¹¤×÷²¾£¬Ñ¡ÖС°¹¤×Ê×ܶËùÔÚÁУ¬Ö´ÐС°¸ñʽ¡úÌõ¼þ¸ñʽ¡±ÃüÁ´ò¡¡



¿ª¡°Ìõ¼þ¸ñʽ¡±¶Ô»°¿ò¡£µ¥»÷µÚ¶þ¸ö·½¿òÓÒ²àµÄÏÂÀ­°´Å¥£¬Ñ¡ÖС°´óÓÚ»òµÈÓÚ¡±Ñ¡ÏÔÚºóÃæ¡¡



µÄ·½¿òÖÐÊäÈëÊýÖµ¡°2000¡¡¡±¡£µ¥»÷¡°¸ñʽ¡±°´Å¥£¬´ò¿ª¡°µ¥Ôª¸ñ¸ñʽ¡±¶Ô»°¿ò£¬½«¡°×ÖÌ塱µÄ¡¡



¡¡¡°ÑÕÉ«¡±ÉèÖÃΪ¡°ºìÉ«¡±¡£¡¡¡¡¡¡¡¡



¡¡¡¡¡¡¡¡2¡£°´¡°Ìí¼Ó¡±°´Å¥£¬²¢·ÂÕÕÉÏÃæµÄ²Ù×÷ÉèÖúÃÆäËüÌõ¼þ£¨´óÓÚµÈÓÚ¡¡1500£¬×ÖÌåÉèÖÃΪ¡°À¶¡¡



É«¡±£»Ð¡ÓÚ¡¡1000£¬×ÖÌåÉèÖÃΪ¡°×ØÉ«¡±£©¡¡¡£¡¡¡¡¡¡¡¡



¡¡¡¡¡¡¡¡3¡£ÉèÖÃÍê³Éºó£¬°´Ï¡°È·¶¨¡±°´Å¥¡£¡¡¡¡¡¡¡¡



¡¡¡¡¡¡¡¡¿´¿´¹¤×ʱí°É£¬¹¤×Ê×ܶîµÄÊý¾ÝÊDz»ÊÇ°´ÄãµÄÒªÇóÒÔ²»Í¬ÑÕÉ«ÏÔʾ³öÀ´ÁË¡£¡¡¡¡¡¡¡¡



¡¡¡¡



¶þ¡¢½¨Á¢·ÖÀàÏÂÀ­ÁбíÌî³äÏî¡¡¡¡¡¡¡¡



¡¡¡¡¡¡¡¡ÎÒÃdz£³£Òª½«ÆóÒµµÄÃû³ÆÊäÈëµ½±í¸ñÖУ¬ÎªÁ˱£³ÖÃû³ÆµÄÒ»ÖÂÐÔ£¬ÀûÓá°Êý¾ÝÓÐЧÐÔ¡±¹¦¡¡



Äܽ¨ÁËÒ»¸ö·ÖÀàÏÂÀ­ÁбíÌî³äÏî¡£¡¡¡¡¡¡¡¡



¡¡¡¡¡¡¡¡1¡£ÔÚ¡¡Sheet2¡¡ÖУ¬½«ÆóÒµÃû³Æ°´Àà±ð£¨Èç¡°¹¤ÒµÆóÒµ¡±¡¢¡°ÉÌÒµÆóÒµ¡±¡¢¡°¸öÌåÆóÒµ¡±µÈ£©·Ö±ðÊä¡¡



È벻ͬÁÐÖУ¬½¨Á¢Ò»¸öÆóÒµÃû³ÆÊý¾Ý¿â¡£¡¡¡¡¡¡¡¡



¡¡¡¡¡¡¡¡2¡£Ñ¡ÖС¡A¡¡ÁУ¨¡¡¡¡¡°¹¤ÒµÆóÒµ¡±Ãû³ÆËùÔÚÁУ©¡¡£¬ÔÚ¡°Ãû³Æ¡±À¸ÄÚ£¬ÊäÈë¡°¹¤ÒµÆóÒµ¡±×Ö·ûºó£¬°´¡¡



¡¡¡°»Ø³µ¡±¼ü½øÐÐÈ·ÈÏ¡£¡¡¡¡¡¡¡¡



¡¡¡¡¡¡¡¡·ÂÕÕÉÏÃæµÄ²Ù×÷£¬½«¡¡B¡¡¡¢C¡¨¡¨ÁзֱðÃüÃûΪ¡°ÉÌÒµÆóÒµ¡±¡¢¡°¸öÌåÆóÒµ¡±¡¨¡¨¡¡¡¡¡¡¡¡



¡¡¡¡¡¡¡¡3¡£Çл»µ½¡¡Sheet1¡¡ÖУ¬Ñ¡ÖÐÐèÒªÊäÈë¡°ÆóÒµÀà±ð¡±µÄÁУ¨Èç¡¡C¡¡ÁУ©¡¡£¬Ö´ÐС°Êý¾Ý¡úÓÐЧÐÔ¡±Ãü¡¡



Á´ò¿ª¡°Êý¾ÝÓÐЧÐÔ¡±¶Ô»°¿ò¡£ÔÚ¡°ÉèÖá±±êÇ©ÖУ¬µ¥»÷¡°ÔÊÐí¡±ÓÒ²àµÄÏÂÀ­°´Å¥£¬Ñ¡ÖС°Ðò¡¡



ÁС±Ñ¡ÏÔÚÏÂÃæµÄ¡°À´Ô´¡±·½¿òÖУ¬ÊäÈë¡°¹¤ÒµÆóÒµ¡±£¬¡°ÉÌÒµÆóÒµ¡±£¬¡°¸öÌåÆóÒµ¡±¡¨¡¨ÐòÁС¡



£¨¸÷ÔªËØÖ®¼äÓÃÓ¢ÎĶººÅ¸ô¿ª£©¡¡£¬È·¶¨Í˳ö¡£¡¡¡¡¡¡¡¡



¡¡¡¡¡¡¡¡ÔÙÑ¡ÖÐÐèÒªÊäÈëÆóÒµÃû³ÆµÄÁУ¨Èç¡¡D¡¡ÁУ©¡¡£¬ÔÙ´ò¿ª¡°Êý¾ÝÓÐЧÐÔ¡±¶Ô»°¿ò£¬Ñ¡ÖС°ÐòÁС±Ñ¡¡¡



Ïîºó£¬ÔÚ¡°À´Ô´¡±·½¿òÖÐÊäÈ빫ʽ£º=INDIRECT£¨C1£©¡¡£¬È·¶¨Í˳ö¡£¡¡¡¡¡¡¡¡



¡¡¡¡¡¡¡¡4¡£Ñ¡ÖС¡C¡¡ÁÐÈÎÒâµ¥Ôª¸ñ£¨Èç¡¡C4£©£¬µ¥»÷ÓÒ²àÏÂÀ­°´Å¥£¬Ñ¡ÔñÏàÓ¦µÄ¡°ÆóÒµÀà±ð¡±ÌîÈëµ¥Ôª¸ñ¡¡



¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡CCXLV¡¡¡¡


¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­Page¡¡246¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­¡­

¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡Excel_word_ppt_ʹÓü¼ÇÉ´óÈ«£¨ÍêÈ«°æ£©¡¡¡¡



ÖС£È»ºóÑ¡Öиõ¥Ôª¸ñ¶ÔÓ¦µÄ¡¡D¡¡Áе¥Ôª¸ñ£¨Èç¡¡D4£©¡¡£¬µ¥»÷ÏÂÀ­°´Å¥£¬¼´¿É´ÓÏàÓ¦Àà±ðµÄÆóÒµÃû¡¡



³ÆÁбíÖÐÑ¡ÔñÐèÒªµÄÆóÒµÃû³ÆÌîÈë¸Ãµ¥Ôª¸ñÖС£¡¡¡¡¡¡¡¡



¡¡¡¡¡¡¡¡Ìáʾ£ºÔÚÒÔºó´òÓ¡±¨±íʱ£¬Èç¹û²»ÐèÒª´òÓ¡¡°ÆóÒµÀà±ð¡±ÁУ¬¿ÉÒÔÑ¡ÖиÃÁУ¬ÓÒ»÷Êó±ê£¬¡¡



Ñ¡¡°Òþ²Ø¡±Ñ¡Ï½«¸ÃÁÐÒþ²ØÆðÀ´¼´¿É¡£¡¡¡¡¡¡¡¡



¡¡¡¡¡¡¡¡Èý¡¢½¨Á¢¡°³£ÓÃÎĵµ¡±Ð²˵¥¡¡¡¡¡¡¡¡



¡¡¡¡¡¡¡¡Ôڲ˵¥À¸ÉÏн¨Ò»¸ö¡°³£ÓÃÎĵµ¡±²Ëµ¥£¬½«³£ÓõŤ×÷²¾ÎĵµÌí¼Óµ½ÆäÖУ¬·½±ãËæʱµ÷Óᣡ¡¡¡¡¡¡¡



¡¡¡¡¡¡¡¡1¡£ÔÚ¹¤¾ßÀ¸¿Õ°×´¦ÓÒ»÷Êó±ê£¬Ñ¡¡°×Ô¶¨Ò塱ѡÏ´ò¿ª¡°×Ô¶¨Ò塱¶Ô»°¿ò¡£ÔÚ¡°ÃüÁ±ê¡¡



Ç©ÖУ¬Ñ¡ÖС°Àà±ð¡±Ïµġ°Ð²˵¥¡±Ï