excel×Ô¶¨Ò庯ÊýÔÚË®Öʹ¤×÷ÖеÄÓ¦ÓÃ(1)_¹¤³ÌÁ¦Ñ§×¨Òµ±ÏÒµÂÛÎÄ·¶ÎÄ
·¢²¼Ê±¼ä£º
2014-12-06
À´Ô´£º
ÈË´ó¾¼ÃÂÛ̳
excel×Ô¶¨Ò庯ÊýÔÚË®Öʹ¤×÷ÖеÄÓ¦ÓÃ(1)_¹¤³ÌÁ¦Ñ§×¨Òµ±ÏÒµÂÛÎÄ·¶ÎÄ
ÕªÒª£ºexcelÊÇ´ó¼Ò·Ç³£ÊìϤµÄÓ¦ÓÃÈí¼þ£¬ÆäʹÓ÷dz£·½±ã¡£ÓÈÆäÊÇÆäÇ¿´óµÄº¯Êý¹¦ÄÜ£¬ÎªÎÒÃÇ´øÀ´Á˼«´ó·½±ã£¬µ«ÓÐʱÎÒÃÇΪʵÏÖÒ»Ïî¹æ¶¨µÄ¹¦ÄÜ£¬¿ÉÄÜÓõ½¼¸¸öº¯Êý»òÕß²ÉÓÃǶÌ׺¯Êý£¬ÕâÑùÓ¦ÓÃÆðÀ´£¬Ò²²»Ì«·½±ã£»»òÕßʹÓÃ×Ô¶¨Ò幫ʽ½øÐмÆË㣬¾ÍûÓÐÏֳɵĹ«Ê½¿ÉÓ᣿ÉϲµÄÊÇ£¬ÎÒÃÇ¿ÉÒÔ´´½¨×Ô¶¨Ò庯Êýudf(user¡ªdefined function)£¬ËüµÄÔËÐÐÓëexcelÖÐ×Ô´øµÄº¯ÊýÍêÈ«Ïàͬ¡£½¨Á¢×Ô¶¨Ò庯ÊýÓÐÒÔϼ¸¸öÓŵ㣺½¨Á¢×Ô¼ºÌØÊâµÄ¹¦ÄܺÍÃû³ÆµÄº¯Êý£»Äܽ«¸´Ôӵġ¢Ç¶Ì׵ġ¢¶à¸öÔÓеĺ¯Êý×éºÏÔÚÒ»Æ𣬷¢»Ó¸ü´óµÄÍþÁ¦£¬Ê¹Óá¢Î¬»¤¸ü¼Ó·½±ã¡£
¹Ø¼ü´Ê£ºº¯Êý Ë®ÖÊ Ó¦ÓÃ
excelÊÇ´ó¼Ò·Ç³£ÊìϤµÄÓ¦ÓÃÈí¼þ£¬ÆäʹÓ÷dz£·½±ã¡£ÓÈÆäÊÇÆäÇ¿´óµÄº¯Êý¹¦ÄÜ£¬ÎªÎÒÃÇ´øÀ´Á˼«´ó·½±ã£¬µ«ÓÐʱÎÒÃÇΪʵÏÖÒ»Ïî¹æ¶¨µÄ¹¦ÄÜ£¬¿ÉÄÜÓõ½¼¸¸öº¯Êý»òÕß²ÉÓÃǶÌ׺¯Êý£¬ÕâÑùÓ¦ÓÃÆðÀ´£¬Ò²²»Ì«·½±ã£»»òÕßʹÓÃ×Ô¶¨Ò幫ʽ½øÐмÆË㣬¾ÍûÓÐÏֳɵĹ«Ê½¿ÉÓ᣿ÉϲµÄÊÇ£¬ÎÒÃÇ¿ÉÒÔ´´½¨×Ô¶¨Ò庯Êýudf(user¡ªdefined function)£¬ËüµÄÔËÐÐÓëexcelÖÐ×Ô´øµÄº¯ÊýÍêÈ«Ïàͬ¡£½¨Á¢×Ô¶¨Ò庯ÊýÓÐÒÔϼ¸¸öÓŵ㣺½¨Á¢×Ô¼ºÌØÊâµÄ¹¦ÄܺÍÃû³ÆµÄº¯Êý£»Äܽ«¸´Ôӵġ¢Ç¶Ì׵ġ¢¶à¸öÔÓеĺ¯Êý×éºÏÔÚÒ»Æ𣬷¢»Ó¸ü´óµÄÍþÁ¦£¬Ê¹Óá¢Î¬»¤¸ü¼Ó·½±ã¡£
±ÈÈçÔÚË®ÖʼÆËãÖУ¬¸ßÃÌËáÑÎÖ¸ÊýµÄ¼ÆË㹫ʽ·Ç³£·±Ëö£¬¶øÇÒº¬±ØÐ뽫½á¹ûÐÞÔ¼ÖÁ0.1£¬»¹µÃ¿¼ÂÇ·ê5Ææ½øżÉáµÄÎÊÌâ¡£ÀûÓüÆËãÆ÷Öð²½¼ÆËãºÜÊÇÂé·³£»ÔÚexcelÖÐÀûÓõ¥Ôª¸ñÒýÓüÆËãÉÔÓв»×¢Ò⣬¿ÉÄÜÔì³É¼ÆËãÉϲî´í£¬¶øÇÒÿ´Î¶¼µÃ½¨Á¢£¬ºÜ²»·½±ã¡£ÏÖÔÚÎÒÃÇÀûÓÃexcel×Ô´øµÄvba±à¼Æ÷½¨Á¢×Ô¼ºµÄº¯ÊýÀ´½â¾öÕâ¸öÎÊÌâ¡£
Ê×ÏȽøÈëexcel£¬ÔÚ[¹¤¾ß]¡ú[ºê]¡ú[visul basic±à¼Æ÷](Ò²¿É°´×éºÏ¼ü[alt+f11])£¬ÔÚ¡°visul basic±à¼Æ÷¡±ÖÐÑ¡Ôñ[²åÈë]¡ú[Ìí¼ÓÄ£¿é]£¬½«Ä£¿éÃû³Æ¸ÄΪ¡°¸ßÃÌËáÑÎÖ¸ÊýŨ¶È¼ÆË㹫ʽ¡±£¬ÔÚ´úÂë´°¿ÚÊäÈëÏÂÁк¯Êý£º
public function cimn(c as single, v0 as single, v1 as single, v2 as single, v as integer)
'¸ßÃÌËáÑÎÖ¸Êý¼ÆË㹫ʽ
dim dotlocation as integer '¶¨ÒåСÊýµãλÖñäÁ¿
'*************************************************************************
cimn = ((((10 + v1) * 10 / v2 - 10) - (((10 + v0) * 10 / v2 - 10) * (100 - v) / 100))) * c * 8000 / v
'*************************************************************************
'±£ÁôһλСÊý
'µ±cimnֵСÓÚ1ʱ£¬×Ô¶¯²¹Éϸöλ0
if cimn 1 then
cimn = 0 & cimn
end if
'È·¶¨Ð¡ÊýµãµÄλÖÃ
dotlocation = instr(cimn, ".")
'ÅжÏСÊýµãºóµÚ¶þλÊÇ·ñΪ5
if mid(cimn, dotlocation + 2, 1) = 5 then
if len(cimn) = dotlocation + 2 and mid(cimn, dotlocation + 1, 1) mod 2 = 0 then
cimn = left(cimn, dotlocation + 1) 'Ϊ5ÇÒ5ºóûÓÐÊýºÍ5ǰһλΪżÊý£¬Ö±½ÓÓÃround()º¯ÊýÐÞÔ¼
else
cimn = round(cimn, 1)
end if
else
cimn = round(cimn, 1) '²»Îª5£¬Ö±½ÓÓÃround()º¯ÊýÐÞÔ¼
end if
cimn = format(round(cimn, 1), "#0.0")
end function
ÎÒÃÇÖªµÀ£¬excelÖк¯Êý¶¼ÓÐÒ»¸ö˵Ã÷ÓÃÒÔ°ïÖúʹÓã¬ÎÒÃÇÒ²Òª¸øÕâ¸öº¯ÊýÌí¼ÓÒ»¸ö˵Ã÷¡£ÔÚ¹¤¾ßÀ¸ÖÐÑ¡Ôñ¡°¶ÔÏóä¯ÀÀÆ÷¡±(Èçͼ1)£¬Ñ¡ÔñÎÒÃÇËù×ö¡°¸ßÃÌËáÑÎÖ¸ÊýŨ¶È¼ÆË㹫ʽ¡±Ä£¿é£¬ÔÚÆä[ÓÒ¼ü]¡ú[ÊôÐÔ]ÖÐÌí¼Ó¹ØÓÚ¶ÔÕâ¸öº¯ÊýµÄÃèÊö£¬Õâ¸öÃèÊö½«³öÏÖÔÚexcelÖйØÓÚº¯ÊýµÄ˵Ã÷ÖУ¬Èç¹ûÄãÒª¶ÔÈí¼þ±£ÃܵĻ°£¬ÔÚ¡°Ä£¿é¡±ÉÏ°´ÓÒ¼ü£¬[vbaprojectÊôÐÔ]¡ú[±£»¤ÖÐÉèÖÃÃÜÂë]£¬±ðÈ˾Ϳ´²»µ½ÄãµÄÔ´³ÌÐòÁË¡£
ͼ1ÃèÊöº¯Êý
Õâʱ£¬Í˳ö£¬»Øµ½excel½çÃ棬½«Õâ¸öÎļþÁí´æΪ£ºÀàÐÍΪ¡°microsoft excel¼ÓÔغꡱ£¬ÔÚexcel 2003ÖУ¬Ëü»á×Ô¶¯¸ü¸Ä±£´æλÖÃΪ¡°c:documents and settingswzwemc£¨µçÄÔÓû§Ãû£©application datamicrosoftaddins¡± (ϵͳװÔÚc:£Üwindows)£¬µ±È»£¬ÄãÒ²¿ÉÒÔ°ÑÕâ¸öÎļþ¡°¸ßÃÌËáÑÎÖ¸ÊýŨ¶È¼ÆË㹫ʽ.xla¡±£¬Ö±½Ó¸´ÖƵ½office£Ülibrary(officeµÄ°²×°Â·¾¶ÏÂ)£¬¶øÔÚexcel 97ÖÐÖ»ÄÜ·ÅÔÚºóÒ»¸öλÖá£Ê¹Óú¯ÊýºÜ¼òµ¥£¬µã»÷[¹¤¾ß]¡ú[¼ÓÔغê]£¬ÔÚÄã´´½¨µÄ¡°¸ßÃÌËáÑÎÖ¸ÊýŨ¶È¼ÆË㹫ʽ¡±Ç°´ò¸ö¹´£¬Èçͼ2¡£ÔÚµ¥Ôª¸ñÖ±½ÓÊäÈë¡°=cimn()¡±£¬Ñ¡ÖÐÏàÓ¦µÄ²ÎÊý¼´¿É(Èçͼ3)¡£ÊDz»ÊÇÏñmicrosoft officeÌṩµÄº¯ÊýÒ»Ñù£¬ºÜÓеãרҵζµÀ¡£