excel可利用函数

1、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 Wo

2、rksheetFunction '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 Works

3、heetFunction '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 Worksheet

4、Function '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 Wo

5、rksheetFunction '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 WorksheetFunct

6、ion '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 WorksheetFuncti

7、on '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-invok

8、e 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 Works

9、heetFunction '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.866025

10、40378443864676372317075294)=>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)=&

11、gt;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)一年中的周数，一周开始于星

12、期日，返回“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 

13、9;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-in

14、voke 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 Works

15、heetFunction (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

16、 (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&q

17、uot; "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 "

18、信息" "题头" "默认值") (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-pri

19、n1-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 *sc

20、r* (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 tit

21、le) ")" ) ) (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) (a

22、pply '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

23、.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 &

24、quot;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 (expr

25、ess 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;返回向上舍入（远离零）到最接近的

26、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* 'WorksheetFunc

27、tion) (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* 

28、9;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-

29、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 &q

30、uot;abcde" "X")=>"abcdeabcdeX"(Text1 "abcde" "!")=>"abcde"(Text1 "2010-1-1 9:8:5" "mmm")=>"Jan"m：月份数(当用于时间时，也可以表时为分钟);mm：当小于10时带前导0的月数(当用于时间时，也可以表示为两位数的分钟数）;mmm：三个英文字母表示的月份数;mmmm：英文表示的月份数;其它功能请核查VBA format

31、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 Fac

32、t (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 / WorksheetFun

33、ction) (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 fr

34、eedom2 / 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" "

35、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 'CON

36、VERT 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 Works

37、heetFunction (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-invo

38、ke (cons WorksheetFunction (cons 'CountIf (list number condition); WorksheetFunction: nil; Property values:; Application (RO) = #<VLA-OBJECT _Application 0eb937b4> Creator (RO) = 1480803660; Parent (RO) = #<VLA-OBJECT _Application 0eb937b4> Methods supported:; AccrInt (7); AccrIntM (

39、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); B

40、eta_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

41、); 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); CritBin

42、om (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); DStDe

43、vP (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);

44、 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 (3

45、0); 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

46、 (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);

47、 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