excel可利用函数.doc_第1页
excel可利用函数.doc_第2页
excel可利用函数.doc_第3页
excel可利用函数.doc_第4页
excel可利用函数.doc_第5页
已阅读5页,还剩11页未读 继续免费阅读

下载本文档

版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领

文档简介

lisp可直接利用的VBA函数 ;BY黄明儒;将十进制数 9 转换为 4 个字符的二进制数 (1001);(Dec2Bin 9 4)=1001 (defun Dec2Bin (Dec Bin / WorksheetFunction) (or *excel* (setq *excel* (vlax-get-or-create-object excel.application) ) (setq WorksheetFunction (vlax-get *excel* WorksheetFunction) (vlax-invoke WorksheetFunction Dec2Bin Dec Bin);10进制转8进制的公式;(Dec2Oct 75 4)=0113(defun Dec2Oct (Dec Oct / WorksheetFunction) (or *excel* (setq *excel* (vlax-get-or-create-object excel.application) ) (setq WorksheetFunction (vlax-get *excel* WorksheetFunction) (vlax-invoke WorksheetFunction Dec2Oct Dec Oct);10进制转16进制的公式;(Dec2Oct 75 4)=4B(defun Dec2Hex (Dec Hex / WorksheetFunction) (or *excel* (setq *excel* (vlax-get-or-create-object excel.application) ) (setq WorksheetFunction (vlax-get *excel* WorksheetFunction) (vlax-invoke WorksheetFunction Dec2Hex Dec Hex);十六进制转换为二进制编码;(HEX2BIN 4B)=1001011(defun HEX2BIN (HEX / WorksheetFunction) (or *excel* (setq *excel* (vlax-get-or-create-object excel.application) ) (setq WorksheetFunction (vlax-get *excel* WorksheetFunction) (vlax-invoke WorksheetFunction HEX2BIN HEX);十六进制转换为十进制编码;(Hex2Dec 4B)=75.0(defun Hex2Dec (HEX / WorksheetFunction) (or *excel* (setq *excel* (vlax-get-or-create-object excel.application) ) (setq WorksheetFunction (vlax-get *excel* WorksheetFunction) (vlax-invoke WorksheetFunction Hex2Dec HEX);十六进制转换为八进制编码;(Hex2Oct 4B)=113(defun Hex2Oct (HEX / WorksheetFunction) (or *excel* (setq *excel* (vlax-get-or-create-object excel.application) ) (setq WorksheetFunction (vlax-get *excel* WorksheetFunction) (vlax-invoke WorksheetFunction Hex2Oct HEX);八进制转换为二进制编码;(Oct2Bin 113)=(Oct2Bin 113)=1001011(defun Oct2Bin (HEX / WorksheetFunction) (or *excel* (setq *excel* (vlax-get-or-create-object excel.application) ) (setq WorksheetFunction (vlax-get *excel* WorksheetFunction) (vlax-invoke WorksheetFunction Oct2Bin HEX);Odd - 返回比参数大的最接近的奇数。Even - 返回比参数大的最接近;(Odd 32)=33.0(defun Odd (num / WorksheetFunction) (or *excel* (setq *excel* (vlax-get-or-create-object excel.application) ) (setq WorksheetFunction (vlax-get *excel* WorksheetFunction) (vlax-invoke WorksheetFunction Odd num);(Even 32)=32.0(defun Even (num / WorksheetFunction) (or *excel* (setq *excel* (vlax-get-or-create-object excel.application) ) (setq WorksheetFunction (vlax-get *excel* WorksheetFunction) (vlax-invoke WorksheetFunction Even num);反cos;(Acos 0.86602540378443864676372317075294)=0.523599弧度(30度)(defun Acos (Num / WorksheetFunction) (or *excel* (setq *excel* (vlax-get-or-create-object excel.application) ) (setq WorksheetFunction (vlax-get *excel* WorksheetFunction) (vlax-invoke WorksheetFunction Acos Num);反sin;(Asin 0.5)=0.523599弧度(30度)(defun Asin (Num / WorksheetFunction) (or *excel* (setq *excel* (vlax-get-or-create-object excel.application) ) (setq WorksheetFunction (vlax-get *excel* WorksheetFunction) (vlax-invoke WorksheetFunction Asin Num);(WeekNum 2008年3月9日 1)一年中的周数,一周开始于星期日,返回“11”;(WeekNum 2008年3月9日 2)一年中的周数,一周开始于星期一,返回“10”(defun WeekNum (express Num / WorksheetFunction) (or *excel* (setq *excel* (vlax-get-or-create-object excel.application) ) (setq WorksheetFunction (vlax-get *excel* WorksheetFunction) (vlax-invoke WorksheetFunction WeekNum express Num);(Weekday February 12, 1969)=值为 4,因为February 12, 1969是星期四(defun Weekday (express / WorksheetFunction) (or *excel* (setq *excel* (vlax-get-or-create-object excel.application) ) (setq WorksheetFunction (vlax-get *excel* WorksheetFunction) (vlax-invoke WorksheetFunction Weekday express);求和;(Sum (6 7 8)=21(defun Sum (express / WorksheetFunction) (or *excel* (setq *excel* (vlax-get-or-create-object excel.application) ) (setq WorksheetFunction (vlax-get *excel* WorksheetFunction) (apply vlax-invoke (cons WorksheetFunction (cons Sum express);第几个最大数;(Large (6 7 8) 1)=8第一个最大数(defun Large (express Num / WorksheetFunction) (or *excel* (setq *excel* (vlax-get-or-create-object excel.application) ) (setq WorksheetFunction (vlax-get *excel* WorksheetFunction) (apply vlax-invoke (cons WorksheetFunction (cons Large (list express Num);查找替换;(Replace ABcDCF C 阿)=ABcD阿F(defun Replace (express find rep) (or *scr* (setq *scr* (vlax-create-object MSScriptControl.ScriptControl.1) ) (vlax-put *scr* language vbs) (vlax-invoke *scr* ExecuteStatement (strcat x = Replace( (VL-PRIN1-TO-STRING express) , (VL-PRIN1-TO-STRING find) , (VL-PRIN1-TO-STRING rep) ) ) (vlax-invoke *scr* eval x);输入对话框 By 819534890;(inputbox 信息 题头 默认值) (defun inputbox(info title default) (or *scr* (setq *scr* (vlax-create-object MSScriptControl.ScriptControl.1) (vlax-put *scr* language vbs) (vlax-invoke *scr* ExecuteStatement (strcat str=InputBox( (vl-prin1-to-string info) , (vl-prin1-to-string title) , (vl-prin1-to-string default) ) ) ) (vlax-invoke *scr* eval str);提示信息框 By 819534890;(msgbox 信息 2 题头),不同的button值自己试试(defun msgbox (info button title / SCR) (or *scr* (setq *scr* (vlax-create-object MSScriptControl.ScriptControl.1) (vlax-put *scr* language vbs) (vlax-invoke *scr* ExecuteStatement (strcat str=MsgBox( (vl-prin1-to-string info) , (vl-prin1-to-string button) , (vl-prin1-to-string title) ) ) ) (vlax-invoke *scr* eval str);平均值;(Average (6 7 8)=7.0(defun Average (express / WorksheetFunction) (or *excel* (setq *excel* (vlax-get-or-create-object excel.application) ) (setq WorksheetFunction (vlax-get *excel* WorksheetFunction) (apply vlax-invoke (cons WorksheetFunction (cons Average express) );list中有Round,而 RoundDown RoundUp Ceiling Ceiling_Precise Floor Floor_Precise是没有的;(RoundDown 3.1256 2)=3.12(defun RoundDown (express num / WorksheetFunction) (or *excel* (setq *excel* (vlax-get-or-create-object excel.application) ) (setq WorksheetFunction (vlax-get *excel* WorksheetFunction) (apply vlax-invoke (cons WorksheetFunction (list RoundDown express num);(RoundUp 3.1246 2)=3.13(defun RoundUp (express num / WorksheetFunction) (or *excel* (setq *excel* (vlax-get-or-create-object excel.application) ) (setq WorksheetFunction (vlax-get *excel* WorksheetFunction) (apply vlax-invoke (cons WorksheetFunction (list RoundUp express num);(CEILING 0.234 0.01) 将 0.234 向上舍入到最接近的 0.01 的倍数 (0.24);(CEILING -2.5 -2) 将 -2.5 向上舍入到最接近的 -2 的倍数 (-4) (defun Ceiling (express num / WorksheetFunction) (or *excel* (setq *excel* (vlax-get-or-create-object excel.application) ) (setq WorksheetFunction (vlax-get *excel* WorksheetFunction) (apply vlax-invoke (cons WorksheetFunction (list Ceiling express num);Office 2010 and later;返回向上舍入(远离零)到最接近的 significance 的倍数的 number;(Ceiling_Precise 0.234 0.01)=0.24;(Ceiling_Precise -2.5 -2)=-2.0(defun Ceiling_Precise (express num / WorksheetFunction) (or *excel* (setq *excel* (vlax-get-or-create-object excel.application) ) (setq WorksheetFunction (vlax-get *excel* WorksheetFunction) (apply vlax-invoke (cons WorksheetFunction (list Ceiling_Precise express num);最小公倍数;(Lcm (32 16 4)=32.0(defun Lcm (express / WorksheetFunction) (or *excel* (setq *excel* (vlax-get-or-create-object excel.application) ) (setq WorksheetFunction (vlax-get *excel* WorksheetFunction) (apply vlax-invoke (cons WorksheetFunction (cons Lcm express);(Text1 123 正;负;零)=正;(Text1 123 0000)=0123格式化字串,不足前面补0;(Text1 2010-5-1 9:8:5 YYYY年MM月DD日)=2010年05月01日;y:一年中的第几天(1-366);yy:两位数的年份(00-99);yyy:上面的 yy 与 y 结合在一起;yyyy:四位数的年份(0100-9999);d:一个月中的第几天(1-31);dd:与 d 相同,但不足两位时补足 0;ddd:三个英文字母表示的星期几;dddd:英文表示的星期几;ddddd:显示标准日期;dddddd:长日期;(Text1 2010-5-1 9:8:5 ddd)=Sat;(Text1 abcde X)=abcdeX;(Text1 abcde X)=abcdeabcdeX;(Text1 abcde !)=abcde;(Text1 2010-1-1 9:8:5 mmm)=Jan;m:月份数(当用于时间时,也可以表时为分钟);mm:当小于10时带前导0的月数(当用于时间时,也可以表示为两位数的分钟数);mmm:三个英文字母表示的月份数;mmmm:英文表示的月份数;其它功能请核查VBA format Text(defun Text1 (express form / WorksheetFunction) (setq *excel* (vlax-get-or-create-object excel.application) (setq WorksheetFunction (vlax-get *excel* WorksheetFunction) (apply vlax-invoke (cons WorksheetFunction (list Text express form) );阶乘;(Fact 4)=24.0(defun Fact (num / WorksheetFunction) (setq *excel* (vlax-get-or-create-object excel.application) (setq WorksheetFunction (vlax-get *excel* WorksheetFunction) (vlax-invoke WorksheetFunction Fact num);半数阶乘,意思就是偶数的只计算偶数阶乘,奇数的只奇数奇数阶乘;(FactDouble 4)=8(defun FactDouble (num / WorksheetFunction) (setq *excel* (vlax-get-or-create-object excel.application) (setq WorksheetFunction (vlax-get *excel* WorksheetFunction) (vlax-invoke WorksheetFunction FactDouble num);X是用来计算概率分布的区间点,freedom1 是分子自由度,freedom2是分母自由度;(FDist 1 90 89)=0.500157(defun FDist (num freedom1 freedom2 / WorksheetFunction) (setq *excel* (vlax-get-or-create-object excel.application) (setq WorksheetFunction (vlax-get *excel* WorksheetFunction) (apply vlax-invoke (cons WorksheetFunction (list FDist num freedom1 freedom2);转换;这个功能太强;(CONVERT1 1.0 lbm kg)= 将 1 磅转换为千克 (0.453592) (defun CONVERT1 (number from_unit to_unit / WorksheetFunction) (setq *excel* (vlax-get-or-create-object excel.application) (setq WorksheetFunction (vlax-get *excel* WorksheetFunction) (apply vlax-invoke (cons WorksheetFunction (list CONVERT number from_unit to_unit) );样本平均值的偏差的平方和;(DevSq (90 86 65 54 36)=2020.8(defun DevSq (number / WorksheetFunction) (setq *excel* (vlax-get-or-create-object excel.application) (setq WorksheetFunction (vlax-get *excel* WorksheetFunction) (apply vlax-invoke (cons WorksheetFunction (cons DevSq number)(CountIf (90 86 65 54 36) 65)(defun CountIf (number condition / WorksheetFunction) (setq *excel* (vlax-get-or-create-object excel.application) (setq WorksheetFunction (vlax-get *excel* WorksheetFunction) (apply vlax-invoke (cons WorksheetFunction (cons CountIf (list number condition); WorksheetFunction: nil; Property values:; Application (RO) = #; Creator (RO) = 1480803660; Parent (RO) = #; Methods supported:; AccrInt (7); AccrIntM (5); Acos (1); Acosh (1); Aggregate (30); AmorDegrc (7); AmorLinc (7); And (30); Asc (1); Asin (1); Asinh (1); Atan2 (2); Atanh (1); AveDev (30); Average (30); AverageIf (3); AverageIfs (29); BahtText (1); BesselI (2); BesselJ (2); BesselK (2); BesselY (2); BetaDist (5); BetaInv (5); Beta_Dist (6); Beta_Inv (5); Bin2Dec (1); Bin2Hex (2); Bin2Oct (2); BinomDist (4); Binom_Dist (4); Binom_Inv (3); Ceiling (2); Ceiling_Precise (2); ChiDist (2); ChiInv (2); ChiSq_Dist (3); ChiSq_Dist_RT (2); ChiSq_Inv (2); ChiSq_Inv_RT (2); ChiSq_Test (2); ChiTest (2); Choose (30); Clean (1); Combin (2); Complex (3); Confidence (3); Confidence_Norm (3); Confidence_T (3); Convert (3); Correl (2); Cosh (1); Count (30); CountA (30); CountBlank (1); CountIf (2); CountIfs (30); CoupDayBs (4); CoupDays (4); CoupDaysNc (4); CoupNcd (4); CoupNum (4); CoupPcd (4); Covar (2); Covariance_P (2); Covariance_S (2); CritBinom (3); CumIPmt (6); CumPrinc (6); DAverage (3); Days360 (3); Db (5); Dbcs (1); DCount (3); DCountA (3); Ddb (5); Dec2Bin (2); Dec2Hex (2); Dec2Oct (2); Degrees (1); Delta (2); DevSq (30); DGet (3); Disc (5); DMax (3); DMin (3); Dollar (2); DollarDe (2); DollarFr (2); DProduct (3); DStDev (3); DStDevP (3); DSum (3); Duration (6); DVar (3); DVarP (3); EDate (2); Effect (2); EoMonth (2); Erf (2); ErfC (1); ErfC_Precise (1); Erf_Precise (1); Even (1); ExponDist (3); Expon_Dist (3); Fact (1); FactDouble (1); FDist (3); Find (3); FindB (3); FInv (3); Fisher (1); FisherInv (1); Fixed (3); Floor (2); Floor_Precise (2); Forecast (3); Frequency (2); FTest (2); Fv (5); FVSchedule (2); F_Dist (4); F_Dist_RT (3); F_Inv (3); F_Inv_RT (3); F_Test (2); GammaDist (4); GammaInv (3); GammaLn (1); GammaLn_Precise (1); Gamma_Dist (4); Gamma_Inv (3); Gcd (30); GeoMean (30); GeStep (2); Growth (4); HarMean (30); Hex2Bin (2); Hex2Dec (1); Hex2Oct (2); HLookup (4); HypGeomDist (4); HypGeom_Dist (5); IfError (2); ImAbs (1); Imaginary (1); ImArgument (1); ImConjugate (1); ImCos (1); ImDiv (2); ImExp (1); ImLn (1); ImLog10 (1); ImLog2 (1); ImPower (2); ImProduct (30); ImReal (1); ImSin (1); ImSqrt (1); ImSub (2); ImSum (30); Index (4); Intercept (2); IntRate (5); Ipmt (6); Irr (2); IsErr (1); IsError (1); IsEven (1); IsLogical (1); IsNA (1); IsNonText (1); IsNumber (1); IsOdd (1); ISO_Ceiling (2); Ispmt (4); IsText (1); Kurt (30); Large (2); Lcm (30); LinEst (4); Ln (1); Log (2); Log10 (1); LogEst (4); LogInv (3); LogNormDist (3); LogNorm_Dist (4); LogNorm_Inv (3); Lookup (3); Match (3); Max (30); MDeterm (1); MDuration (6); Median (30); Min (30); MInverse (1); MIrr (3); MMult (2); Mode (30); Mode_Mult (30); Mode_Sngl (30); MRound (2); MultiNomial (30); NegBinomDist (3); NegBinom_Dist (4); NetworkDays (3); NetworkDays_Intl (4); Nominal (2); NormDist (4); NormInv (3); NormSDist (1); NormSInv (1); Norm_Dist (4); Norm_Inv (3); Norm_S_Dist (2); Norm_S_Inv (1); NPer (5); Npv (30); Oct2Bin (2); Oct2Dec (1); Oct2Hex (2); Odd (1); OddFPrice (9); OddFYield (9); OddLPrice (8); Od

温馨提示

  • 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
  • 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
  • 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
  • 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
  • 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
  • 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
  • 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

评论

0/150

提交评论