excel可利用函数.doc

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