用Excel Link实现Excel与Matlab混合编程
MATLAB与Excel的链接及其在频谱监测数据处理中的应用
队园地Military World中国无线电 2007年第8期481 概述 MATLAB是Mathworks公司于20世纪80年代推出的软件，经过不断完善和发展，目前已成为涉及多学科、多领域的软件平台。
M A T L A B拥有四大特点：一是强大的数值运算能力和符号运算能力；二是强大的数据可视化功能；三是丰富的工具箱；四是可延拓性和开放的架构。
Excel是Microsoft公司推出的一个非常出色的电子表格软件，它以图、文、表三者完美结合的形式被广泛应用于财务、统计和分析等领域。
目前，无线电频谱监测系统中的数据有一大部分是以Excel格式存储的，如果能够将Excel与Matlab进行链接并完成数据交换，将会大大有利于无线电频谱监测数据的后期处理。
2 MATLAB与Excel的链接方法2.1 利用动态数据交换(D D E )和ActiveX技术 A c t i v e X技术和与之相关的C O M（c o m p o n e n t object mode1）是由Microsoft开发的提供开发人员使用的编程技术和软件接口。
ActiveX技术能够大幅度提高工作效率，因此，在Matlab中利用动态数据交换（DDE）和ActiveX技术编程可自动处理Excel数据。
利用动态数据交换和ActiveX技术处理Excel数据共分七个步骤。
第一步：利用uigetfile函数（如[FileName,Path Name]=uigetfile('*.xls')）产生一个标准Windows 查找文件对话框，查找相应的文件后，所选择的文件名和文件所在的路径名传递给参数FileName和PathName。
第二步：使用fullfile函数（如file=fullfile(PathNa me,FileName)）将文件的全路径传递给参数file。
第三步：利用函数actxserver（如he=actxserver('Excel.Application')）启动Excel软件。
如何将Excel中的数据导入MATLAB并在MATLAB中画出图像
首先Excel必须是office的（WPS的不可以，MATLAB不能识别），在MATLAB中使用命令xlsread读取Excel中的数据到MATLAB里，如下所示：[number,txt,raw]=xlsread('')，此时读取的文件须在MATLAB默认路径下，命令具体用法及参数含义可直接用help命令在MATLAB中查找。
读取的数据界面如下：
1.如何将Excel中的数据导入MATLAB并在MATLAB中画出图像？
首先Excel必须是office的（WPS的不可以，MATLAB不能识别），file-Import Data-在工作路径下找到需要导入的文件（图3）-点击打开（图4）-点击next-点击finish-导入成功-在workspace中看到图5（导入的数据）。
至此数据导入成功。
下面是如何利用该数据绘图：
法一：双击workspace中的data打开如图6，选中要画图的数据如图7（数据范围可以根据需要随便选择），点击上方的plot(data(1:874,1:2)，画出图像如图8，并且在工作空间中自动显示画图命令。
法二：直接在工作空间中写画图命令。
图3
图4
图5图6图7图8。
matlab与excel数据交换的有利工具
matlab与excel数据交换的有利工具——excel link(1) What Is Excel Link?Excel Link is a software add-in that integrates Microsoft Excel andMATLAB in a Microsoft Windows-based computing environment. By connecting Exceland MA TLAB, you can access the numerical, computational, and graphical powerof MATLAB from Excel worksheet and macro programming tools. Excel Link letsyou exchange and synchronize data between the two environments.1. System Requirements2. Installing Excel Link3. Configuring Excel to Work with Excel Link4. Starting Excel Link5. Connecting to an Existing MATLAB Session6. Stopping Excel LinkFollow these instructions to install Excel Link and then configure Excel.System RequirementsExcel Link requires approximately 202 kilobytes of disk space. Operating system requirements are:Microsoft Windows XP ，Microsoft Windows 2000Excel Link also requires one of the following versions of Excel:Excel 2000Excel ，2002Excel ，2003Excel（Excel Link has also been qualified against Excel 2007 Beta.）Excel Link also requires MA TLAB for Windows.For best results with MATLAB figures and graphics, set the color palette of your display to a value greater than 256 colors. Click Start > Settings > Control Panel > Display, and then click the Settings tab.Choose an appropriate entry from the Color Palette menu.Installing Excel LinkInstallWindows and Excel before you install MATLAB and Excel Link.To install Excel Link, follow the instructions in the MA TLAB installation documentation. Select the Excel Link check box when you select MATLAB components to install.Configuring Excel to Work with Excel LinkAfter you have installed Excel Link, you are ready to configure Excel. You need do these steps only once:1. Start Microsoft Excel.2. Select Tools > Add-Ins and click Browse.3. Find and select the Excel Link add-in excllink.xla under matlabroot/toolbox/exlink. Click OK.（Throughout this document the notation matlabroot representsthe MATLAB root directory, the directory where MATLAB is installed on yoursystem.）4. Back in the Add-Ins dialog box, make sure that the check box is selected and click OK.The Excel Link add-in loads now and with each subsequent invocation of Excel.5 .Watch for the appearance of the MATLAB Command Window button on the Windows taskbar.6 .Watch for the appearance of the Excel Link toolbar on your Excel worksheet.Excel Link is now ready for your use.Starting Excel LinkAutomatic StartWhen installed and configured according to the preceding instructions,Excel Link and MA TLAB automatically start when you start Excel.If you do not want Excel Link and MATLAB to start automatically when you start Excel, enter =MLAutoStart("no") in a worksheet cell. This function changes the initialization file so that Excel Link and MATLAB no longer start automatically when you start Excel. See MLAutoStart in Functions — Alphabetical List.Manual StartTo start Excel Link and MATLAB manually from Excel, select Tools > Macro. In the Macro Name/Reference box enter matlabinit and click Run. Watch for the MA TLAB Command Window button to appear on the taskbar. See matlabinit in Functions — Alphabetical List.Connecting to an Existing MATLAB SessionTo connect a new Excel session to an existing MATLAB process, you must start MATLAB with the /automation command-line option. The /automation option starts MA TLAB as an automation server. The Command Window is minimized, and the MATLAB desktop is not running.To add the /automation option to the command line:1. Right-click your shortcut to MA TLAB.2 .Select Properties.3. Click the Shortcut tab.4. Add the string /automation in the Target field. Remember to leave a space between matlab.exe and /automation.Stopping Excel LinkTo stop both Excel Link and MATLAB, stop Excel as you normally would.Excel Link and MATLAB both stop when you stop Excel.To stop MA TLAB and Excel Link and leave Excel running, enter =MLClose() inan Excel worksheet cell. You can restart Excel Link and MATLAB manually with MLOpen or matlabinit.If you stop MATLAB directly in the MATLAB Command Window and leave Excel running, enter =MLClose() in an Excel worksheet cell. (MLClose tells Excel that MATLAB is no longer running.)You can restart Excel Link and MA TLAB manually with MLOpen or matlabinit. Understanding the EnvironmentExcel Link communicates between the Excel workspace and the MATLAB workspace.It positions Excel as a front end to MA TLAB. You use Excel Link functionsfrom an Excel worksheet or macro without leaving the Excel environment. Witha small number of functions to manage the link and manipulate data, ExcelLink is powerful in its simplicity.Installing and Operating Excel LinkThis section covers the following topics:matlab与excel数据交换的有利工具——excel link(2) What the Functions DoThis section covers the following topics:Link Management FunctionsData Management FunctionsWith Excel Link, Microsoft Excel becomes an easy-to-use data-storage and application-development front end for MA TLAB, which is a powerful computational and graphical processor. Excel Link provides functions to manage the link and to manipulate data. You never have to leave the Excel environment. You can invoke functions as worksheet cell formulas or in macros.For details on each function, see Functions — Alphabetical List.Link Management FunctionsExcel Link provides four link management functions to initialize, start,and stop Excel Link and MATLAB.You can invoke any link management function except matlabinit as a worksheet cell formula or in a macro. You invoke matlabinit fromthe Excel Tools Macro menu or in a macro subroutine.Use MLAutoStart to toggle automatic startup. If youinstall and configure Excel Link according to the default instructions, Excel Link and MATLAB automatically start every time you start Excel. If you choose manual startup, use matlabinit to initialize Excel Link and start MATLAB.Use MLClose to stop MA TLAB .without stopping Excel,and use MLOpen or matlabinit to restart MATLAB in the same Excel session.Data Management FunctionsExcel Link provides the following data management functions to copy data between Excel and MATLAB and to execute MATLAB commands from Excel.Function Purposematlabfcn Evaluate MA TLAB command given Excel data.matlabsub Evaluate MATLAB command given Excel data and designate output location. MLAppendMatrix Create or append MA TLAB matrix with data from Excel worksheet. MLDeleteMatrix Delete MA TLAB matrix.MLEvalString Evaluate command in MATLAB.MLGetFigure Import current MATLAB figure into Excel spreadsheet.MLGetMatrix Write contents of MA TLAB matrix in Excel worksheet.MLGetVar Write contents of MATLAB matrix in Excel VBA variable.MLPutMatrix Create or overwrite MATLAB matrix with data from Excel worksheet.MLPutVar Create or overwrite MATLAB matrix with data from Excel VBA variable. MLShowMatlabErrors Used by MLEvalString to return standard Excel Link errors or full MATLAB errors.MLStartDir Specify current working directory of MATLAB after startup.MLUseFullDesktop Specify whether to use full MA TLAB desktop or only Command window. You can invoke any data management function except MLPutVar as a worksheet cell formula or in a macro. You can invoke MLPutVar only in a macro.Use MLAppendMatrix, MLPutMatrix, and MLPutVar to copy data from Excel to MA TLAB.Use MLEvalString to execute MA TLAB commands from Excel.Use MLDeleteMatrix to delete a MATLAB variable.Use matlabfcn, matlabsub, MLGetMatrix,and MLGetVar to copy data from MATLAB to Excel.matlab与excel数据交换的有利工具——excel link(3) Tips and RemindersThis section covers the following topics:Syntax 语法Worksheets 工作表Macros 宏Data Types 数据类型Dates 日期Saved Worksheets 保存工作表Information for International Users 国际用户信息These tips and reminders help you use Excel Link efficiently.Excel Link functions perform an action, while Microsoft Excel functions return a value. Keep this distinction in mind as you use Excel Link. Excel operations and function keys may behave differently with Excel Link functions.Syntax 语法Function NamesExcel Link function names are not case sensitive; that is, MLPutMatrix and mlputmatrix are the same.MATLAB function names and variable names are case sensitive; that is, BONDS, Bonds, and bonds are three different MA TLAB variables. Standard MATLAB function names are always lower case; for example, plot(f).Worksheet Formulas1. Begin worksheet formulas with + or =. For example:=mlputmatrix("a", C10)2 .In worksheet formulas, enclose function arguments in parentheses. In macros, leave a space between the function name and the first argument; do not use parentheses.Variable Names1. You can directly or indirectly specify a variable-name argument in most Excel Link functions.(1.) To specify a variable name directly, enclose it in double quotes; for example, MLDeleteMatrix("Bonds").(2.).A variable-name argument without quotes is an indirect reference.The function evaluates the contents of the argument to get the variable name.The argument must be a worksheet cell address or range name.2. A data-location argument must be a worksheet cell address or range name. Do not enclose a data-location argument in quotes (except in MLGetMatrix, which has unique argument conventions).3.A data-location argument can include a worksheet number; for example, Sheet3!B1:C7 orSheet2!OUTPUT.(注意You can use virtually any special character as part of a worksheet name if you embed the sheet name within single quotes ('') when referencing it in MLGetMatrix or MLPutMatrix.) Worksheets 工作表1. After an Excel Link function successfully executes as a worksheet formula, the cell contains the value 0. While a functionis executing, the cell may continue to show the entered formula.2.We suggest selecting Move Selection after Enter on the Excel Tools Options > Edit tab. The active cell changes when an operation is complete, providing a useful confirmation for lengthy operations.3.We recommend using Excel Link functions in automatic calculation mode. If you use MLGetMatrix in manual calculation mode,enter the function in a cell, then press F9 to execute it. However, pressing F9 in this situation may also reexecute other worksheet functions and generate unpredictable results.4.To recalculate Excel Link functions in a worksheet, reexecute each function by pressing F2, then Enter.5.Pressing F9 to recalculate a worksheet affects only Excel functions (which return a value). F9 does not operate on Excel Link functions, which perform an action.6.To "automate" the recalculation of an Excel Link function, add to it some cell whose value changes. For example:=MLPutMatrix("bonds", D1:G26) + C1When the value in cell C1 changes, Excel reexecutes the MLPutMatrix function. Be careful, however, not to create endless recalculation loops.7.Excel Link functions expect A1-style worksheet cell references (columns designated with letters and rows with numbers). This is the default reference style. If your worksheet shows columns designated with numbers instead of letters, select Tools > Options andclick the General tab. Under Settings, clear the R1C1 reference style check box.8.If you use explicit cell addresses in MLGetMatrix and later insert or delete rows or columns, or move or copy the function to another cell, edit the argument to correct the addresses. Excel Link does not automatically adjust cell addresses in MLGetMatrix.9.Enter (type) Excel Link functions directly in worksheet cells.Do not use the Excel Function Wizard; it generates unpredictable results.Macros 宏1.To create macros that use Excel Link functions, you must first configure Excel to reference the functions from the Excel Link add-in. From the Visual Basic environment, select Insert > Module. When the Module page opens, select Tools > References.In the References dialog box, select the box for excllink.xla and click OK. You may have to use Browse to find the excllink.xla file.2.If you use MLGetMatrix in a macro subroutine, enter MatlabRequest on the line after MLGetMatrix. MatlabRequest initializesinternal Excel Link variables and enables MLGetMatrix to function in a subroutine. For example: Sub Get_RangeA()MLGetMatrix "A", "RangeA"MatlabRequestEnd SubDo not include MatlabRequest in a macro function unless the macro function is called from a subroutine.Data Types 数据类型Excel Link handles only MATLAB two-dimensional numeric arrays,one-dimensional character arrays (strings), and two-dimensional cell arrays.It does not work with MATLAB multidimensional arrays and structures.Dates 日期Default Excel date numbers start from January 1, 1900, while MATLAB date numbers start from January 1, 0000. Thus May 15, 1996 is 35200 in Excel and 729160 in MATLAB, a difference of 693960. If you use date numbers in MA TLAB calculations, apply the 693960 constant: add it to Excel date numbers going into MATLAB, or subtract it from MATLAB date numbers coming into Excel. If you use the optional Excel 1904 date system, the constant is 695422.Saved Worksheets Saved Worksheets 保存工作表1.When you open an Excel worksheet that contains Excel Link functions, Excel tries to execute the functions from the bottom up and right to left, thus possibly generating cell error messages (#COMMAND!, #NONEXIST!, etc.). Such behavior is usual for Excel. Simply ignore the messages, close any MATLAB figure windows, and reexecute the cell functions one at a time in the correct order by pressing F2, and then Enter.2.If you save an Excel worksheet containing Excel Link functions and later open it under a different computer environment where the excllink.xla add-in is in a different location, Excel may display a message box. Click No. Then select Edit > Links. In the Links dialogbox, click Change Source. In the Change Links dialog box, find and select excllink.xla under matlabroot/toolbox/exlink and click OK. Excel executes each function as it changes its link. You may see MATLAB figure windows and hear error beeps as the links change and functions execute; ignore them. Back in the Links dialog box, click OK. The worksheet now correctly connects to the Excel Link add-in.Or, instead of using the Edit Links menu, you can manually edit the link location in each affected worksheet cell to show the correct location of excllink.xla.Information for International Users 国际用户信息This document uses Excel with an English?(United States) Windows regional setting for illustrative purposes. If you use Excel Link with a non-English?(United?States) Windows desktop environment, certain syntactical elements may not work as illustrated. For example, you may have to replace the comma (,) delimiter within the Excel Link commands with a semicolon (;) or other operator. Please consult your Windows documentation to determine which regional setting differences exist among various international versions.matlab与excel数据交换的有利工具——excellink(4)Example 1: Regression and Curve FittingRegression techniques and curve fitting attempt to find functions that describe the relationshipamong variables. In effect, they attempt to build mathematical models of a data set. MATLAB provides many powerful yet easy-to-use matrix operators and functions to simplify the task.This example does both data regression and curve fitting. It also executes the same example in a worksheet version and a macro version. The example uses Excel worksheets to organize and display the data. Excel Link functions copy the data to MATLAB and execute MATLAB computational and graphic functions. The macro version also returns outputdata to an Excel worksheet.Worksheet VersionTo try the worksheet-only version of this example, click the Sheet1 tab on the ExliSamp.xls window.The worksheet contains one named range: A4:C28 is named DATA and contains the sample data set:1.Make E5 the active cell. Press F2, then Enter to execute the Excel Link function that copies the sample data set to MATLAB.The data set contains 25 observations of three variables. There is a strong linear dependence among the observations; in fact, they are close to being scalar multiples of each other.2.Move to cell E8 and press F2, then Enter. Repeat with cells E9 and E10. These Excel Link functions tell MATLAB to regress the third column of data on the other two columns. They createa single vector y containing the third-column data, and a new three-column matrix A consisting ofa column of ones followed by the rest of the data.3.Execute the function in cell E13. This function computes the regression coefficients by using the MATLAB backslash operation to solve the (overdetermined) system of linear equations, A*beta = y.4.Execute the function in cell E16. MATLAB matrix-vector multiplication produces the regressed result (fit).5.Execute the functions in cells E19, E20, and E21. These functions compare the original data with fit; sort the data in increasing order and apply the same permutation to fit; and create a scalar for the number of observations.6.Execute the functions in cells E24 and E25. Often it is useful to fit a polynomial equation to data. To do so, you would ordinarily have to set up a system of simultaneous linear equations and solve for the coefficients. The MA TLAB polyfit function automatesthis procedure, in this case for a fifth-degree polynomial. The polyval function then evaluates the resulting polynomial at each data point to check the goodness of fit (newfit).7.Execute the function in cell E28. The MA TLAB plot function graphs the original data (blue circles), the regressed result fit (dashed red line), and the polynomial result (solid green line); and adds a legend. Data plots.Since the data is closely correlated but not exactly linearly dependent, the fit curve (dashed line)shows a close, but not an exact,fit. The fifth-degree polynomial curve, newfit, represents a more accurate mathematical model for the data.When you have finished this version of the example, close the figure window.Macro VersionTo try the macro-and-worksheet version of this example, click the Sheet2 tab on ExliSamp.xls.Make cell A4 the active cell, but do not execute it yet.Cell A4 calls the macro CurveFit, which you can examine from the Visual Basic environment.While this module is open, select Tools > References. In the References dialog box, make sure that the excllink.xla check box is selected. If not, select the check box and click OK. You may have to use Browse to find the excllink.xla file.Back in cell A4 of Sheet2, press F2, then Enter to execute the CurveFit macro. The macro executes the same functions as in Step 1 through Step 7 of the worksheet version (in a slightly different order), including plotting the graph. Plus, it copies the original data y (sorted), the corresponding regressed data fit, and the polynomial data newfit, to the worksheet. (The last three MLGetMatrix unctions in the CurveFit macro copy data to the Excel worksheet.)When you have finished the example, close the figure window.matlab与excel数据交换的有利工具——excellink(5)插值Interpolation is a process for estimating values that lie between known data points. It is important for applications such as signal and image processing and data visualization. MATLAB provides a number of interpolation functions that let you balance the smoothness of data fit with execution speed and efficient memory use.This example uses a two-dimensional data-gridding interpolation function on thermodynamic data, where volume has been measured for time and temperature values. It finds the volume values underlying the two-dimensional time-temperature function for a new set of time and temperature coordinates.The example uses an Excel worksheet to organize and display the original data and the interpolated output data. Excel Link functions copy the data to and from MATLAB, execute the MATLAB interpolation function, and invoke MATLAB graphics to display the interpolated data in a three-dimensional color surface.To try this example, click the Sheet3 tab on ExliSamp.xls.The worksheet contains the measured thermodynamic data in cells A5:A29, B5:B29, and C5:C29. The time and temperature values for interpolation are in cells E7:E30 and F6:T6 respectively:1.Make A33 the active cell. Press F2, then Enter to execute the Excel Link function that passes the Time, Temp, and Volume labels to MATLAB.2.Make A34 the active cell. Press F2, then Enter to execute the Excel Link function that copies the original time data to MATLAB.Move to cell A35 and execute the function to copy the original temperature data. Execute the function in cell A36 to copy the original volume data.3.Move to cell A39 and press F2, then Enter to copy the interpolation time values to MATLAB. Execute the function in cell A40 tocopy the interpolation temperature values.4.Execute the function in cell A43. griddata is the MATLAB two-dimensional interpolation function that generates the interpolatedvolume data using the inverse distance method.5.Execute the functions in cells A46 and A47 to transpose the interpolated volume data and copy it to the Excel worksheet.The data fills cells F7:T30, which are enclosed in a border.6.Execute the function in cell A50. MA TLAB plots and labels the interpolated data on a three-dimensional colorsurface, with the color proportional to the interpolated volume data.When you have finished with the example, close the figure window.matlab解经典符号方程solve（）solveSymbolic solution of algebraic equationsSyntaxsolve(eq)solve(eq,var)solve(eq1,eq2,...,eqn)g = solve(eq1,eq2,...,eqn,var1,var2,...,varn)DescriptionSingle Equation/Expression2The input to solve can be either symbolic expressions or strings. If eq is a symbolic expression(x^2-2*x+1) or a string that does not contain an equal sign ('x^2-2*x+1'), then solve(eq)solves the equation eq=0 for its default variable (as determined by findsym). solve(eq,var) solves the equation eq (or eq=0 in the two cases cited above) for the variablevar.System of EquationsThe inputs are either symbolic expressions or strings specifying equations. solve(eq1,eq2,...,eqn) or solves the system of equations implied by eq1,eq2,...,eqn inthe n variables determined by applying findsym to the system.g = solve(eq1,eq2,...,eqn,var1,var2,...,varn) finds the zeros for the system ofequations for the variables specified as inputs.Three different types of output are possible. For one equation and one output, the resultingsolution is returned with multiple solutions for a nonlinear equation. For a system of equations andan equal number of outputs, the results are sorted alphabetically and assigned to the outputs. Fora system of equations and a single output, a structure containing the solutions is returned.For both a single equation and a system of equations, numeric solutions are returned if symbolicsolutions cannot be determined.Examplessolve('a*x^2 + b*x + c') returns[ 1/2/a*(-b+(b^2-4*a*c)^(1/2)),1/2/a*(-b-(b^2-4*a*c)^(1/2))]solve('a*x^2 + b*x + c','b') returns-(a*x^2+c)/xS = solve('x + y = 1','x - 11*y = 5') returns a structure S withS.y = -1/3, S.x = 4/3solve :: Functions (Symbolic Math Toolbox) text://2 of 2 2012-7-26 9:A = solve('a*u^2 + v^2', 'u - v = 1', 'a^2 - 5*a + 6')returnsA =a: [4x1 sym]u: [4x1 sym]v: [4x1 sym]whereA.a =[ 2][ 2][ 3][ 3]A.u =[ 1/3+1/3*i*2^(1/2)][ 1/3-1/3*i*2^(1/2)][ 1/4+1/4*i*3^(1/2)][ 1/4-1/4*i*3^(1/2)]A.v =[ -2/3+1/3*i*2^(1/2)][ -2/3-1/3*i*2^(1/2)][ -3/4+1/4*i*3^(1/2)][ -3/4-1/4*i*3^(1/2)]关于使用solve（）时产生x: [1x1 sym]的解决亲爱的同学们，你有没有在使用matlab过这样类似的情况呢？>> solve('x + y = 1','x - 11*y = 5')ans =x: [1x1 sym]y: [1x1 sym]为什么会出现这样的现象呢？这是因为你的默认输出格式是结构体，你想看的是单个的元素。
如何将Excel中的数据导入MATLAB并在MATLAB中画出图象
1.如何将Excel中的数据在MATLAB界面下显示出来：
首先Excel必须是office的（WPS的不可以，MATLAB不能识别），在MATLAB中使用命令xlsread读取Excel中的数据到MATLAB里，如下所示：[number,txt,raw]=xlsread('noise.xlsx')，此
时读取的文件须在MATLAB默认路径下，命令具体用法及参数含
义可直接用help命令在MATLAB中查找。
读取的数据界面如下：
2.如何将Excel中的数据导入MATLAB并在MATLAB中画出图像？
首先Excel必须是office的（WPS的不可以，MATLAB不能识
别），file-Import Data-在工作路径下找到需要导入的文件（图3）-点击打开（图4）-点击next-点击finish-导入成功-在workspace中看到图5（导入的数据）。
至此数据导入成功。
下面是如何利用该数据绘图：
法一：双击workspace中的data打开如图6，选中要画图的数据如图7（数据范围可以根据需要随便选择），点击上方的
plot(data(1:874,1:2)，画出图像如图8，并且在工作空间中自动显示画图命令。
法二：直接在工作空间中写画图命令。
图3
图4
图5
图6
图7
图8。
如何将Excel中的数据导入MATLAB并在MATLAB中画出图像
1. 如何将Excel中的数据在MATLA界面下显示出来：首先Excel必须是office 的（WPS勺不可以，MATLA不能识别），在MATLA中使用命令xlsread读取Excel中的数据到MATLA里，如下所示：[number,txt,raw]=xlsread（'noise.xlsx'），此时读取的文件须在MATLA默认路径下，命令具体用法及参数含义可直接用help 命令在MATLA中查找。
读取的数据界面如下：[0.2i60][0.24SO][0.2500][0.2520][0.2540][0.2560][0.2580][0.25Q0][0.2620][0. 2640][0. 2660][0.2680][0.2700][0.2720][0.2740][0. 2760][23.6000][ 28][30.4000]125-2000][ 18][19.2000][2L 2000][17. 20Q0][16.SOOO][17.6000][13.6000][11.2000][13.6000][13.2000][ 10][12.4000]2. 如何将Excel中的数据导入MATLA并在MATLA中画出图像？首先Excel必须是office 的（WPS勺不可以，MATLA不能识别），file-Import Data- 在工作路径下找到需要导入的文件（图3）-点击打开（图4）-点击next-点击finish- 导入成功-在workspace中看到图5 （导入的数据）。
至此数据导入成功。
下面是如何利用该数据绘图：法一：双击workspace中的data打开如图6,选中要画图的数据如图7 （数据范围可以根据需要随便选择），点击上方的plot（data（1:874,1:2），画出图像如图8,并且在工作空间中自动显示画图命令。
法二：直接在工作空间中写画图命令。
>> [number, txt raw!=zlsr*ad C noise.xlsz J)riunber 二0.246023. 60000.24SC 2& 00000.2500 30. 40000. 2520 25.20000. 2E40 18. 00000.2560 2000Di 2580 21. 20000.2600 17.20000. 2620 16.30000. 2640 17. 60000. 13. 60000* 26SA 1],20000. 2700 13. SQ00Import Data査找范圉（X）：库计算机网貉名称'悸改日期,,noise.rriat2014/3/1S 19:181^gnoisenKlsx2014/3/1& 19：01I HKTIAB ▼> ®文件名曲）:iriOL XISX文件类型⑴；Recognized Filww取消 |JJWorkspace圄■鱼1鲂I^Lr Select data ...〒Name *田［甌Value<874x2 double* rrrCommand Window■+* □ ? X>> plol (datadia?^ I. = 2), J DisplayMafte J p J dataCl;874? 1;2)a ? J YDataSource 1/ data(i:B74? 1:2^ ) .f igurt (ecf)九—K —图81 f 4New to MATLAB? Watch thi$ Sdg see 口旳口轧 or read Getting 5t^rt 亡cLNew Parallel D”ktq 因❼Ctirrent F file Edit 0ew Ins-ert lo&ls Desktop Window Help□ U 矗B |輿耳U 物穏摇TO □囲I ■口IXCommaHd Window。
matlab与word,excel无缝连接
问题：如何将图片自动导入Excel或Word文件中，换句话说，就是怎样用matlab生成带有图片的Excel和Word文档，对此，我分别写了下面两个小程序，并在matlab2008a中经过了测试。
1. 用matlab生成带有图片的Excel文档function ceshi_Excel%设定测试Excel文件名和路径filespec_user=[pwd '\测试.xls'];%判断Excel是否已经打开，若已打开，就在打开的Excel中进行操作，%否则就打开ExceltryExcel=actxGetRunningServer('Excel.Application');catchExcel = actxserver('Excel.Application');end;%设置Excel属性为可见set(Excel, 'Visible', 1);%返回Excel工作簿句柄Workbooks = Excel.Workbooks;%若测试文件存在，打开该测试文件，否则，新建一个工作簿，并保存，文件名为测试.Excel if exist(filespec_user,'file');Workbook = invoke(Workbooks,'Open',filespec_user);elseWorkbook = invoke(Workbooks, 'Add');Workbook.SaveAs(filespec_user);end%返回工作表句柄Sheets = Excel.ActiveWorkBook.Sheets;%返回第一个表格句柄sheet1 = get(Sheets, 'Item', 1);%激活第一个表格invoke(sheet1, 'Activate');%如果当前工作表中有图形存在，通过循环将图形全部删除Shapes=Excel.ActiveSheet.Shapes;if Shapes.Count~=0;for i=1:Shapes.Count;Shapes.Item(1).Delete;end;end;%随机产生标准正态分布随机数，画直方图，并设置图形属性zft=figure('units','normalized','position',...[0.280469 0.553385 0.428906 0.251302],'visible','off');set(gca,'position',[0.1 0.2 0.85 0.75]);data=normrnd(0,1,1000,1);hist(data);grid on;xlabel('考试成绩');ylabel('人数');%将图形复制到粘贴板hgexport(zft, '-clipboard');%将图形粘贴到当前表格的A5:B5栏里Excel.ActiveSheet.Range('A5:B5').Select;Excel.ActiveSheet.Paste;%删除图形句柄delete(zft);2. 用matlab生成带有图片的Word文档function ceshi_Word%设定测试Word文件名和路径filespec_user=[pwd '\测试.doc'];%判断Word是否已经打开，若已打开，就在打开的Word中进行操作，%否则就打开WordtryWord = actxGetRunningServer('Word.Application');catchWord = actxserver('Word.Application');end;%设置Word属性为可见set(Word, 'Visible', 1);%返回Word文件句柄documents = Word.Documents;%若测试文件存在，打开该测试文件，否则，新建一个文件，并保存，文件名为测试.doc if exist(filespec_user,'file');document = invoke(documents,'Open',filespec_user);elsedocument = invoke(documents, 'Add');document.SaveAs(filespec_user);endcontent = document.Content;selection = Word.Selection;paragraphformat = selection.ParagraphFormat;%页面设置document.PageSetup.TopMargin = 60;document.PageSetup.BottomMargin = 45;document.PageSetup.LeftMargin = 45;document.PageSetup.RightMargin = 45;%设定内容起始位置和标题set(content, 'Start',0);title='测试文件';set(content, 'Text',title);set(paragraphformat, 'Alignment','wdAlignParagraphCenter');%设定标题字体格式rr=document.Range(0,10);rr.Font.Size=16;rr.Font.Bold=4;%设定下面内容的起始位置end_of_doc = get(content,'end');set(selection,'Start',end_of_doc);%另起一段selection.TypeParagraph;%如果当前工作文档中有图形存在，通过循环将图形全部删除shape=document.Shapes;shape_count=shape.Count;if shape_count~=0;for i=1:shape_count;shape.Item(1).Delete;end;end;%随机产生标准正态分布随机数，画直方图，并设置图形属性zft=figure('units','normalized','position',...[0.280469 0.553385 0.428906 0.251302],'visible','off');set(gca,'position',[0.1 0.2 0.85 0.75]);data=normrnd(0,1,1000,1);hist(data);grid on;xlabel('考试成绩');ylabel('人数');%将图形复制到粘贴板hgexport(zft, '-clipboard');%将图形粘贴到当前文档里，并设置图形属性为浮于文字上方selection.Range.PasteSpecial;shape.Item(1).WrapFormat.Type=3;shape.Item(1).ZOrder('msoBringInFrontOfText');%删除图形句柄delete(zft);。
excel和matlab链接
Matlab具有强大的数据计算和处理、图形显示能力；Excel则具有强大的表格处理同时也有强大的数据统计和显示能力。
在科研过程中常会同时使用这两款软件，如果实现Matlab和Excel的链接，可以满足实际要求。
实现Matlab和Excel的混合编程主要有以下几种方法：1、利用Excel Link（最常用方法）Excel Link是一个在Windows环境下实现Excel与Matlab进行链接的插件。
通过连接Excel和Matlab，实现Excel和Matlab的数据共享。
使用Excel Link 时，不脱离Excel环境直接在Excel中调用Matlab函数。
具体可参见Matlab帮助或Excel Link手册。
2、利用Excel生成器(Matlab作COM服务器)COM(通用对象模型)是一组面向对象的技术和工具的集合。
利用Matlab提供的COM生成器，把Matlab的算法程序生成组件，这些组件可作为独立的COM对象被Excel的Visual Basic使用。
具体可参见Matlab COM Builder或者Matlab Builder for Excel工具。
3、利用Active X控制(Matlab作COM客户端)ActiveX是 Microsoft对于一系列策略性面向对象程序技术和工具的称呼，其中主要的技术是组件对象模型。
主要利用Matlab中的actxserver函数：actxserver('progid')；progid为程序的ActiveX部件的标识符，针对不同的程序有不同的progid。
如actxserver('Excel.Application')，然后可利用invoke函数进行一系列操作。
4、文件导入方法利用xlsread和xlswrite函数读写Excel文件(也可用fopen、fread和fwrite函数)，然后进行数据处理操作。
另外，还可以通过菜单File->Import data 方法。
第3章 MATLAB与Excel文件的数据交换
3.2.3
写入数据函数xlswrite
MATLAB往Excel中写入数据的函数为xlswrite。 xlswrite函数语法： ［status, message］ = xlswrite (filename, M, sheet, range) %status=1时说明写入成果，0时写入失败。
测试函数M文件CaseXlsWrite.m如下：
《金融数量分析——基于MATLAB编程 》
3.1 案例背景
Excel是一款非常优秀的通用表格软件，在学习、工作与科研中 大量的数据可能都是以Excel表格的方式存储的。Excel在矩阵计算、 数据拟合与优化算法等方面的功能尚不足，Excel与MATLAB相结合 是处理复杂数据问题的有效方法。 如何利用MATLAB强大的数值计算功能处理Excel中的数据，首 要解决的问题就是如何将Excel中的数据导入MATLAB中或将 MATLAB数值计算的结果转存入Excel中。 本章主要介绍以函数方式与Exlink宏的两种方法实现MATLAB与 Excel的数据交互。
%数据位置为excel.xls文件 表1 位置为B3:B14的列数据 Hs300Price=xlsread(‘funddata.xls', 1, 'B3:B14') %或 Hs300Price=xlsread(‘funddata.xls’, ‘Sheet1', 'B3:B14') Hs300Vol=xlsread(‘funddata.xls', 1, 'C3:C14') %数据位置为excel.xls文件 表1 位置为C3:C14的列数据
3. 4.2
多个文件的读取和写入
在实际的项目编程中，很多时候遇到从很多文件中读取数据，若逐个文件 进行手工操作不仅身心疲惫，而且容易出错。例如，指数成分股与权重数据每 天一个Excel文件，文件名为： 000016weightnextday20100104.xls,000016weightnextday20110630.xls，…， 000016weightnextday20120104.xls等，程序化读取的关键是将文件名自动化。
MATLAB和其他软件数据连接
MATLAB和Excel数据连接
Excel Link 的使用
直接把数据输入到Mຫໍສະໝຸດ TLAB中3. 单击“putmatrix”按钮弹出一个菜单，根 据菜单提示将Excel中数据用鼠标选定， 传输到MATLAB中变量名为ytg_000088, 然后单击“确定”按钮
3. 4. 5.
6.
25
7.
MATLAB和Excel数据连接
Excel Link 的使用
MATLAB中自带Excel文件介绍
用二叉树方法计算欧式看跌期权价格
1. 打开”sheet4”，数据B4：B10名称是bindata ，分别存放股票价格、行权价、 无风险利率、期权存续期（5/12）、时间离散步长（1/12）、股票标准差 以及是否是欧式（美式）期权，B15开始存放二叉树各节点价格 asset_tree,B23开始存放现金流value_tree 2. 激活D5单元，将Excel中bindata传到MATLAB变量b中，激活D12单元，调 用MATLAB中计算二叉树函数binprice,其二叉树数据结果保存在变量p中， 价格保存在变量o中。激活D11、D12，分别保存在MATLAB变量 asset_tree与value_tree中，注意B15与B23分别是asset_tree与 value_tree的起点。
MATLAB和Excel数据连接
Excel Link 的使用
简易方法将Excel数据导入MATLAB
1. 在Excel中建立一个数据文件
Excel Link 的使用
数据管理函数
函数 MATLABfcn MATLABsub 作用 对于给定的Excel数据运 行MATLAB命令 对于给定的Excel数据运 行MATLAB命令，并制 定输出位置 函数 MLAppendMatrix MLPutMatrix 作用 向MATLAB 工作区添加 Excel数据表 用Excel数据表创建或覆 盖MATLAB数据 用Excel数据表VBA创建 或覆盖MATLAB数据 向Excel数据表VBA写 MATLAB数据内容
第章MATLAB与excel数据连结及绘图概要
(1)在excel电子表格中输入命令=MLOpen()
(2) 利用excel的宏工具，在宏对话框中输入Matlabinit， 然后单击执行按钮。
(3)按左上按钮"startmatlab"。
1、ExcelLink连接管理函数
(1) Matlabinit 该函数只能在宏子例程中使用。初始化 ExcelLink和启动Matlab进程。只有在 MLAutoStart函数中使用“no”参数，才需要 手动使用Matlabinit来初始化ExcelLink和启 动Matlab进程， 如果使用参数“yes”，则Matlabinit是自动执 行的。 使用语法：Matlabinit
(3) MLAppendMatrix
将Excel工作表中的数据追加到Matlab中指 定的矩阵中如果该矩阵不存在，则创建矩阵。 在工作表中使用的语法： MLAppendMatrix(var_name，mdat)
注意要追加的数据维数要和原矩阵中的维数 相匹配，否则出错
假设matlab中，矩阵a是个2行3 列的矩阵，如下图:
4. xlswrite与xlsread
➢ MATLAB提供的函数xlswrite，具有将 MATLAB中的数据写入EXCEL的功能。
➢ MATLAB提供的函数xlsread，具有将EXCEL 中的数据读入MATLAB的功能。
xlswrite的调用方式
xlswrite('', M) xlswrite('', M, sheet) xlswrite('', M, 'range') xlswrite('', M, sheet, 'range')
基于EXCEL与MATLAB混合编程的学生成绩与试卷分析
万方数据
基于EXCEL与MATLAB混合编程的学生成绩与试卷分析
作者： 作者单位： 刊名：
英文刊名： 年，卷(期)：
赵玉丹 长春理工大学理学院,吉林,长春,130022
空中英语教室（社会科学版） STUDIO CLASSROOM 2010(11)
参考文献(4条)
1.张志涌 精通MATLAB6 2003 2.包研科;李娜 数理统计与MATLAB数据处理 2008
依据，而且有助于充分地获得通过考试提供的教学反馈信息，
为改进教学提供依据。学生考试成绩大多数都是以电子表格 的形式存储的，它使用起来方便灵活，而且具有一定的计算功
能，因而得到了广泛的应用。ＭＡＴＬＡＢ具有许多ＥＸＣＥＬ不具
备的函数，编程灵活，尤其是在根据已有数据进行图形处理方
面的功能非常强大。ＥＸＣＥＬ Ｌｉｎｋ是在Ｗｉｎｄｏｗｓ环境下，将
＝ｍｌｅｖａｌｓｔｒｉｎｇ（”ｋ＝ｃｅｉｌ（１．８７｝（１ｅｎｇｔｈ（ｍａｔｈ（：，６））一１） 叼．４））”）
＝ｒｎｌｅｖａｌｓｔｒｉｎｇ（”［ｎｉ，ａｋ］＝ｈｉｓｔ（ｍａｔｈ（：，６），ｋ）”）
＝ｍｌｅｖａｌｓｔｒｉｎｇ（”ｆｉ＝ｎｉ／ｌｅｎｇｔｈ（ｍａｔｈ（：，６））”） ＝ｍｌｅｖａｌｓｔｒｉｎｇ（”ｍｆｉ＝ｃｕｍｓｕｍ（ｆｉ）”） ＝ｍｌｅｖａｌｓｔｒｉｎｇ（”ｓｔａｔｓ＝［［１：ｋ］ｊａｋ．ｎｉ＇，ｆｉ＇，ｍｆｉ＇ｌ”）”） ＝ｍｌｅｖａｌｓｔｒｉｎｇ（”ｈｉｓｔ（ｍａｔｈ（：，６））”） ＝ｍｌｅｖａｌｓｔｒｉｎｇ（”ｈ＝ｆｉｎｄｏｂｊ（ｇｃａ，ｌｙｐｅ：ｉＤａｔｃｈ＇）”） ＝ｍｌｅｖａｌｓｔｒｉｎｇ（”ｓｅｔ（ｈ，ＦａｃｅＣｏｌｏｒ．，ｊ ＥｄｇｅＣｏｌｏｒ．ｂ’”） ＝ｍｌｅｖａｌｓｌｒｉｎｇ（”ｈ＝ｈｉｓｆｆｉｔ（ｍａｔｈ（：，６），１３）”）
MATLAB与excel混合使用
本章主要介绍如何在excel平台下使用MA TLAB资源。
MA TLAB提供的Spreadsheet Link 将Microsoft Excel和MA TLAB完美结合，即在Excel表单中利用MA TLAB资源，包括科学计算和绘图功能。
其工作原理是：首先在Excel表单中创建命令，然后传递给MA TLAB后台处理，最后将后台处理结果回传到Excel表单中。
1、Spreadsheet Link的安装启动Microsoft Excel，选择工具菜单中的“加载宏”选项点击浏览按钮，选择MA TLAB路径下\toolbox\exlink子目录中的excllink.xla文件，然后点击“确定”按钮即可加载MA TLAB，可以看到Excel运行窗口增加了Spreadsheet Link的工具条，如图所示：2、Spreadsheet Link的启动和退出按照上述步骤安装Spreadsheet Link后，将在每次启动Excel时自动启动Spreadsheet Link和MA TLAB。
如果希望改变此种启动方式，可以在Excel表单单元中输入“=MLAutoStart("no")”语句，执行后则改变了设置。
当然如果希望恢复原设置，可以采用“=MLAutoStart("yes")”语句。
对于Spreadsheet Link和MA TLAB没有自动启动的情况，可以在Excel平台中手动启动。
首先在工具菜单中选择“宏”选项，打开“宏”对话框并输入“MA TLABinit”，单击“执行”按钮后即可启动Spreadsheet Link和MA TLAB。
当退出Excel时自动退出Spreadsheet Link和MA TLAB。
如果希望在Excel平台中退出Spreadsheet Link和MA TLAB，只需在表单单元中输入“=MLClose()”语句即可。
3、Spreadsheet Link的实际应用在Spreadsheet Link应用时，主要是实现Excel数据的读入、MA TLAB对数据的处理和显示以及将处理结果显示在Excel中。
MATLAB与Excel混合编程.
MATLBA 与 Excel 混合编程
Matlab 具有强大的数据计算能力, 但是对于对一些常见的统计图形的显示, 例如棒图,饼图,折线图等, Excel 的显示能力质量很高,且易于控制。
Excel link是一个软件插件,它可以将 Excel 和 Matlab 进行集成。
使用 Excel Link 时,不必脱离 Excel 环境, 而直接在 Excel 的工作区域, 或者宏操作中调用 Matlab 的函数。
Excel 提供了11个函数来实现链接和数据操作。
因此,通过 Excel link这个中介, Excel 成为Matlab 的一个易于使用的数据存储和应用开发前端,它是一个功能强大的计算和图形处理器。
(一 Excel Link的安装
平台:matlab 2009b, office 2007,Excel Link无需安装, 在 matlab2009b 中已有图示步骤:
点击确定后,找到此宏的目录在 matlab 安装文件目录\toolbox\exlink\excllink2007.xlam 最后出现:
(二 Excel Link的函数及其应用
表一:Excel Link的连接管理函数
表二:Excel Link 数据管理函数
具体应用可以看看 MATLAB 的 help 文件,里面都有 (三应用举例1.
并且会动态更新的 2.。
在Excel中应用MATLAB
收稿日期:2000205222 作者简介:李杰(1972-),男,硕士研究生,主要研究方向:遥控遥测、CDM A 扩频通信仿真、卫星通信.文章编号:1001-9081(2000)11-0066-02在Excel 中应用M AT LAB李 杰(北京航空航天大学电子工程系,北京100083)摘 要:详细介绍了连接Excel 和M AT LAB 的步骤及在Excel 中应用M AT LAB 的方法,极大地增强了Excel 处理数据的能力和图形输出的能力。
关键词:Excel ;Matlab ;函数中图分类号:TP311 文献标识码:A1 MAT LAB 和Excel 简介M AT LAB 是MathW orks 公司推出的面向工程和计算的交互式计算软件,它已成为国际公认的最优秀的科技应用软件。
自1980年以来,它已被广泛应用于控制系统、信号处理、数据分析、通信系统、金融系统、生物医学等领域。
M AT LAB 拥有三大特点:一是强大的矩阵处理和数值运算能力;二是强大的符号运算能力;三是强大的图形输出能力。
M AT LAB 的三大特点使它不仅能够进行复杂的数据计算、符号运算,而且能够将计算的结果以二维、三维、乃至四维的图形表现出来,并且可以通过对图形线性、立面、色彩、渲染、光线、视角等特性的处理,把计算数据的特征表现得淋漓尽致。
Excel 是一个非常出色的电子表格软件,它以图、文、表三者完美结合的形式被广泛应用于财务、统计和分析等领域。
Excel 内包含一个巨大的内置函数集,供数据处理使用,如能将M AT LAB 强大的数据计算和处理能力、灵活的可编程能力、完美的图形输出能力赋予Excel ,那么Excel 的功能将会变得更加强大。
本文就介绍如何将Excel 和M AT LAB 结合起来,使它们交互协同工作。
2 连接E xcel 和M A T LA B 的步骤与函数1)运行Micros oft Excel ;2)打开工具菜单(T ools ),选择加载宏(Add -Ins ),单击Browse 按钮;3)在C :\M AT LAB \EX LI NK 下找到EXC LI NK.X LA ,单击OK 按钮;4)返回到加载宏(Add -Ins )窗口,单击OK 按钮,Excel Link 则被加载到Excel 中;5)观察Excel 会发现M AT LAB 命令窗口(包括putmatrix ,getmatrix ,evalstring 三个按钮)出现在Excel的任务栏内(如图1)。
excel_link_Matlab
Computation Visualization ProgrammingFor Use with M ATLAB®User’s GuideVersion 1.0.8Excel LinkPreface Setting Your Expectations (iv)Documentation Conventions (v)1Using Excel LinkWhat Is Excel Link?...................................1-2Understanding the Environment........................1-2Installing and Operating Excel Link....................1-3System Requirements.................................1-3Installing Excel Link..................................1-3Configuring Excel to Work with Excel Link...............1-3Starting Excel Link Automatically.......................1-4Starting Excel Link Manually..........................1-4Stopping Excel Link..................................1-4What the Functions Do................................1-5Link Management Functions...........................1-5Data Management Functions...........................1-6Tips and Reminders...................................1-7Syntax.............................................1-7Worksheets.........................................1-8Macros.............................................1-9Data Types..........................................1-9Dates..............................................1-9Saved Worksheets...................................1-10i2Solving Sample Problems with Excel Link Example1:Regression and Curve Fitting...............2-3 Worksheet Version....................................2-3 Macro Version.......................................2-6 Example2:Interpolating Data.........................2-9 Example3:Pricing a Stock Option with theBinomial Model......................................2-13 Example4:Calculating and Plotting the Efficient Frontierof Financial Portfolios................................2-173ReferenceAError Messages and Troubleshooting Excel Cell Error Messages..............................A-2 Excel Error Message Boxes.............................A-4 Audible Error Signals.................................A-4 Data Errors..........................................A-5BInstalled Files Installed Files........................................B-2ii ContentsPrefaceSetting Your Expectations (iv)Documentation Conventions (v)Prefaceiv Setting Your ExpectationsIn designing Excel Link and this manual,we assume you are a knowledgeableand experienced user of Microsoft Windows,Microsoft Excel,and MATLAB®.If you are unfamiliar with any of this software,please consult the appropriateproduct documentation before using Excel Link.After reading this manual,you will understand Excel Link concepts,content,functions,and uses.You will be able to use the functions of choice to developintegrated applications.Documentation ConventionsvDocumentation ConventionsWe use some or all of these conventions in our manuals.To IndicateThis Guide Uses ExampleExample codeMonospace type(Use Code tag.)To assign the value 5to A ,enterA = 5Functionnames/syntaxMonospace type(Use Code tag.)For syntax lines,use paragraph tag Syntax .The cos function finds the cosine of each array element.Syntax line example isMLGetVar ML_var_nameKeys Boldface with an initial capital letter(Use Menu-Bodytext tag.)Press the Return key .Mathematical expressionsVariables in italics .Functions,operators,and constants in standard type.(UseEquationVariables tag.)This vector represents the polynomial p =x 2+2x +3MATLAB outputMonospace type(Use Code tag.)MATLAB responds withA =5Menu names,menu items,and controls Boldface with an initial capital letter(Use Menu-Bodytext tag.)Choose the File menu.New termsNCS italics(Use Body text-ital tag.)An array is an ordered collection of information.Preface vi1 Using Excel LinkWhat Is Excel Link?.................1-2 Understanding the Environment............1-2Installing and Operating Excel Link.........1-3System Requirements.................1-3Installing Excel Link.................1-3Configuring Excel to Work with Excel Link........1-3Starting Excel Link Automatically...........1-4Starting Excel Link Manually.............1-4Stopping Excel Link.................1-4What the Functions Do...............1-5Link Management Functions..............1-5Data Management Functions..............1-6Tips and Reminders................1-7 Syntax.......................1-7 Worksheets.....................1-8 Macros.......................1-9Data Types.....................1-9 Dates.......................1-9Saved Worksheets..................1-101 Using Excel Link1-2What Is Excel Link?Excel Link is a software add-in that integrates Microsoft Excel and MATLAB®in a Microsoft Windows-based computing environment.By connecting Exceland MATLAB,you can access the numerical,computational,and graphicalpower of MATLAB from Excel worksheet and macro programming tools.ExcelLink lets you exchange and synchronize data between the two environments.Understanding the EnvironmentExcel Link communicates between the Excel workspace and the MATLABworkspace.It positions Excel as a front end to MATLAB.You use Excel Linkfunctions from an Excel worksheet or macro,and you never have to leave theExcel environment.With only11functions to manage the link and manipulatedata,Excel Link is powerful in its simplicity.Installing and Operating Excel Link 1-3Installing and Operating Excel LinkFollow these instructions to install Excel Link and then configure Excel.System RequirementsExcel Link requires approximately 202kilobytes of disk space.It requires Microsoft Windows 95or Microsoft Windows NT 4.0;Microsoft Excel 97;and MATLAB for Windows version 5.1or later.For best results with MATLAB figures and graphics,set the color palette of your display to a value greater than 256colors.Click Start then Settings and Control Panel .Open Display ,and on the Settings tab choose an appropriate entry from the Color Palette menu.Installing Excel LinkInstall Windows and Excel before you install MATLAB and Excel Link.To install Excel Link,follow the instructions in the MATLAB Installation Guide .Click in the box for Excel Link when you select MATLAB components to install.Configuring Excel to Work with Excel LinkOnce you have installed Excel Link,you are ready to configure Excel.You need do these steps only once.1Start Microsoft Excel.2Pull down the Tools menu,select Add-Ins and click Browse .3Find and select the Excel Link add-in EXCLLINK.XLA underC:\MATLAB\EXLINK (or your path).Click OK .4Back in the Add-Ins window,make sure there’s a check in the box for Excel Link for use with MATLAB and click OK .The Excel Link add-in loads now and with each subsequent invocation of Excel.5Watch for the MATLAB Command Window button to appear on the taskbar.Excel Link is now ready to use.1 Using Excel Link1-4Starting Excel Link AutomaticallyWhen installed and configured according to the preceding instructions,Excel Link and MATLAB automatically start when you start Excel.If you do not want Excel Link and MATLAB to start automatically when you start Excel,enter=MLAutoStart("no")in a worksheet cell.This function changes the initialization file so that Excel Link and MATLAB no longer start automatically when you start Excel.See MLAutoStart in the Reference chapter. Starting Excel Link ManuallyTo start Excel Link and MATLAB manually from Excel,pull down the Tools menu and select Macro.In the Macro Name/Reference box enter matlabinit and click Run.Watch for the MATLAB Command Window button to appear on the taskbar.See matlabinit in the Reference chapter.Stopping Excel LinkTo stop both Excel Link and MATLAB,stop Excel as you normally would.Excel Link and MATLAB both stop when you stop Excel.You cannot connect a new Excel session to an existing MATLAB process unless you have started MATLAB with the/automation command line option.To stop MATLAB and Excel Link and leave Excel running,enter =MLClose() in an Excel worksheet cell.You can restart Excel Link and MATLAB manually with MLOpen or matlabinit.If you stop MATLAB directly in the MATLAB Command Window and leave Excel running,enter =MLClose()in an Excel worksheet cell.(MLClose tells Excel that MATLAB is no longer running.)You can restart Excel Link and MATLAB manually with MLOpen or matlabinit.What the Functions DoWhat the Functions DoWith Excel Link,Microsoft Excel becomes an easy-to-use data-storage andapplication-development front end for MATLAB,which is a powerfulcomputational and graphical processor.Excel Link provides functions to manage the link and to manipulate data.Younever have to leave the Excel environment.You can invoke functions asworksheet cell formulas or in macros.See the Reference chapter for details on each function.Link Management FunctionsExcel Link provides four link management functions to initialize,start,andstop Excel Link and MATLAB.matlabinit Initialize Excel Link and start MATLAB process.MLAutoStart Automatically start MATLAB process.MLClose Terminate MATLAB process.MLOpen Start MATLAB process.You can invoke any link management function except matlabinit as aworksheet cell formula or in a macro.You invoke matlabinit from the ExcelTools Macro menu or in a macro subroutine.Use MLAutoStart to toggle automatic startup.If you install and configure ExcelLink according to the default instructions,Excel Link and MATLABautomatically start every time you start Excel.If you choose manual startup,use matlabinit to initialize Excel Link and start MATLAB.Use MLClose to stop MATLAB without stopping Excel,and use MLOpen ormatlabinit to restart MATLAB in the same Excel session.1-51 Using Excel Link1-6Data Management FunctionsExcel Link provides seven data management functions to copy data between Excel and MATLAB and to execute MATLAB commands from Excel:You can invoke any data management function except MLGetVar and MLPutVar as a worksheet cell formula or in a macro.You can invoke MLGetVar and MLPutVar only in a macro.Use MLAppendMatrix,MLPutMatrix,and MLPutVar to copy data from Excel to MATLAB.Use MLEvalString to execute MATLAB commands from Excel.Use MLDeleteMatrix to delete a MATLAB variable.Use MLGetMatrix and MLGetVar to copy data from MATLAB to Excel.Note:MLGetMatrix argument syntax differs from the other data management functions.Please see the Reference chapter for details.MLAppendMatrix Create or append MATLAB matrix with datafrom Excel worksheet.MLDeleteMatrix Delete MATLAB matrix.MLEvalString Evaluate command in MATLAB. MLGetMatrix Write contents of MATLAB matrix in Excelworksheet.MLGetVar Write contents of MATLAB matrix in ExcelVBA(Visual Basic for Applications)variable. MLPutMatrix Create or overwrite MATLAB matrix withdata from Excel worksheet.MLPutVar Create or overwrite MATLAB matrix withdata from Excel VBA variable.Tips and RemindersTips and RemindersThese tips and reminders help you use Excel Link efficiently.Excel Link functions perform an action,while Microsoft Excel functions returna value.Keep this distinction in mind as you use Excel Link.Excel operationsand function keys may behave differently with Excel Link functions.Syntax•Excel Link function names are not case sensitive;that is,MLPutMatrix andmlputmatrix are the same.•MATLAB function names and variable names are case sensitive;that is,BONDS,Bonds,and bonds are three different MATLAB variables.StandardMATLAB function names are always lower case,for example plot(f).•Begin worksheet formulas with+or=.For example,=mlputmatrix("a", C10)•In worksheet formulas,enclose function arguments in parentheses.Inmacros,leave a space between the function name and the first argument;donot use parentheses.•You can directly or indirectly specify a variable-name argument in mostExcel Link functions.-To specify a variable name directly,enclose it in double quotes;forexample,MLDeleteMatrix("Bonds").-A variable-name argument without quotes is an indirect reference.Thefunction evaluates the contents of the argument to get the variable name.The argument must be a worksheet cell address or range name.•A data-location argument must be a worksheet cell address or range name.Do not enclose a data-location argument in quotes(except in MLGetMatrix,which has unique argument conventions).•A data-location argument can include a worksheet number;for example,Sheet3!B1:C7or Sheet2!OUTPUT.1-71 Using Excel Link1-8Worksheets•After an Excel Link function successfully executes as a worksheet formula, the cell contains the value0.While a function is executing,the cell may continue to show the entered formula.•We suggest checking Move Selection after Enter on the Excel Tools Options...Edit tab.The active cell changes when an operation is complete, providing a useful confirmation for lengthy operations.•We recommend using Excel Link functions in automatic calculation mode.If you use MLGetMatrix in manual calculation mode,enter the function in a cell,then press F9to execute it.However,pressing F9in this situation may also re-execute other worksheet functions and generate unpredictable results.•To recalculate Excel Link functions in a worksheet,re-execute each function by pressing F2,then Enter.•Pressing F9to recalculate a worksheet affects only Excel functions(which return a value).F9does not operate on Excel Link functions,which perform an action.•To“automate”the recalculation of an Excel Link function,add to it some cell whose value changes.For example,=MLPutMatrix("bonds", D1:G26) + C1When the value in cell C1changes,Excel re-executes the MLPutMatrix function.Be careful,however,not to create endless recalculation loops.•Excel Link functions expect A1-style worksheet cell references.Select A1cell Reference Style on the Excel Tools Options...General tab.•If you use explicit cell addresses in MLGetMatrix and later insert or delete rows or columns,or move or copy the function to another cell,edit the argument to correct the addresses.Excel Link does not automatically adjust cell addresses in MLGetMatrix.•Enter(type)Excel Link functions directly in worksheet cells.Do not use the Excel Function Wizard;it generates unpredictable results.Tips and RemindersMacros•To create macros that use Excel Link functions,you must first configureExcel to reference the functions from the Excel Link add-in.From the VisualBasic environment pull down the Insert menu and select Module.When the Module page opens,pull down the Tools menu and select References....Inthe References window,check the box for EXCLLINK.XLA and click OK.Youmay have to use Browse to find the EXCLLINK.XLA file.•If you use MLGetMatrix in a macro subroutine,enter MatlabRequest on theline after MLGetMatrix.MatlabRequest initializes internal Excel Linkvariables and enables MLGetMatrix to function in a subroutine.For example,Sub Get_RangeA()MLGetMatrix "A", "RangeA"MatlabRequestEnd SubDo not include MatlabRequest in a macro function unless the function iscalled from a subroutine.Data Types•Excel Link and Excel handle only two-dimensional numeric MATLABarrays.They do not work with MATLAB multidimensional arrays,cellarrays,character arrays,or structures.•Worksheet cells or VBA variables that pass to MATLAB must contain only numeric data,not strings.Dates•Default Excel date numbers start from January1,1900,while MATLAB date numbers start from January1,0000.Thus May15,1996is35200in Exceland729160in MATLAB,a difference of693960.If you use date numbers in MATLAB calculations,apply the693960constant:add it to Excel datenumbers going into MATLAB,or subtract it from MATLAB date numbers coming into Excel.If you use the optional Excel1904date system,theconstant is695422.1-91 Using Excel Link1-10Saved Worksheets•When you open an Excel worksheet that contains Excel Link functions,Excel tries to execute the functions from the bottom up and right to left,thus possibly generating cell error messages(#COMMAND!,#NONEXIST!,etc.).Such behavior is“normal”for Excel.Simply ignore the messages,close any MATLAB figure windows,and re-execute the cell functions one at a time in the correct order by pressing F2,then Enter.•If you save an Excel worksheet containing Excel Link functions and later open it under a different computer environment where the EXCLLINK.XLA add-in is in a different location,Excel may display a message box:Click No.Then pull down the Edit menu and select Links.In the Links window,click Change Source.In the Change Links window,find and select EXCLLINK.XLA under C:\MATLAB\EXLINK(or your path)and click OK.Excel executes each function as it changes its link.You may see MATLAB figure windows and hear error beeps as the links change and functions execute; ignore them.Back in the Links window,click OK.The worksheet now correctly connects to the Excel Link add-in.Or,instead of using the Edit Links...menu,you can manually edit the link location in each affected worksheet cell to show the correct location of EXCLLINK.XLA.2 Solving Sample Problemswith Excel LinkExample1:Regression and Curve Fitting.......2-3Worksheet Version..................2-3Macro Version....................2-6Example2:Interpolating Data............2-9Example3:Pricing a Stock Option with theBinomial Model................2-13Example4:Calculating and Plotting theEfficient Frontier of Financial Portfolios....2-172 Solving Sample Problems with Excel Link2-2This section shows how Microsoft Excel,Excel Link,and MATLAB work together to solve real-world problems.These examples ship with Excel Link in the file EXLISAMP.XLS,which is installed in the subdirectory EXLINK under your MATLAB directory(for example C:\MATLAB\EXLINK).Start Excel,Excel Link,and MATLAB.Open and try executing the examples.Note: Examples1and2use only basic MATLAB functions.Examples3and 4use functions in the optional MATLAB Financial Toolbox.Example 1: Regression and Curve FittingExample 1: Regression and Curve FittingRegression techniques and curve fitting attempt to find functions that describethe relationship among variables.In effect,they attempt to build mathematicalmodels of a data set.MATLAB provides many powerful yet easy-to-use matrixoperators and functions to simplify the task.This example does both data regression and curve fitting.It also executes thesame example in a worksheet version and a macro version.The example usesExcel worksheets to organize and display the data.Excel Link functions copythe data to MATLAB and execute MATLAB computational and graphicfunctions.The macro version also returns output data to an Excel worksheet.Worksheet VersionTo try the worksheet-only version of this example,click the Sheet1tab onEXLISAMP.XLS.2 Solving Sample Problems with Excel LinkThe worksheet contains one named range: A4:C28is named DATA and containsthe sample data set.1Make E5the active cell.Press F2,then Enter to execute the Excel Linkfunction that copies the sample data set to MATLAB.The data set contains25observations of three variables.There is a strong linear dependenceamong the observations;in fact,they are close to being scalar multiples ofeach other.2Move to cell E8and press F2,then Enter.Repeat with cells E9and E10.These Excel Link functions tell MATLABto regress the third column of dataon the other two columns.They create a single vector y containing thethird-column data,and a new three-column matrix A consisting of a columnof ones followed by the rest of the data.3Execute the function in cell E13.This function computes the regressioncoefficients by using the MATLAB backslash operation to solve the(overdetermined)system of linear equations,A*beta = y.4Execute the function in cell E16.MATLAB matrix-vector multiplicationproduces the regressed result(fit).5Execute the functions in cells E19,E20,and E21.These functions comparethe original data with fit;sort the data in increasing order and apply thesame permutation to fit;and create a scalar for the number of observations.6Execute the functions in cells E24and E25.Often it is useful to fit apolynomial equation to data.To do so,you would ordinarily have to set up asystem of simultaneous linear equations and solve for the coefficients.TheMATLAB polyfit function automates this procedure,in this case for aExample 1: Regression and Curve Fitting fifth-degree polynomial.The polyval function then evaluates the resulting polynomial at each data point to check the goodness of fit(newfit).7Finally,execute the function in cell E28.The MATLAB plot function graphs the original data(blue circles),the regressed result fit(dashed red line),and the polynomial result(solid green line);and adds a legend.Since the data is closely correlated but not exactly linearly dependent,the fit curve(dashed line)shows a close,but not an exact,fit.The fifth-degree polynomial curve,newfit,represents a more accurate mathematical model forthe data.When you have finished with this version of the example,close the Figure window.2 Solving Sample Problems with Excel LinkMacro VersionTo try the macro-and-worksheet version of this example,click the Sheet2tabon EXLISAMP.XLS.Make cell A4the active cell,but do not execute it yet.Cell A4calls the macro CurveFit,which you can examine from the Visual Basicenvironment.Example 1: Regression and Curve FittingWhile this module is open,pull down the Tools menu and select References.In the References window,make sure there is a check in the box for EXCLLINK.XLA.If not,check the box and click OK.You may have to use Browse...to find the EXCLLINK.XLA file.Back in cell A4of Sheet2,press F2,then Enter to execute the CurveFit macro.The macro executes the same functions as in Step1through Step7of the worksheet version(in a slightly different order),including plotting the graph. Plus,it copies the original data y(sorted),the corresponding regressed datafit,and the polynomial data newfit,to the worksheet.(The last three MLGetMatrix functions in the CurveFit macro copy data to the Excel worksheet.)2 Solving Sample Problems with Excel LinkWhen you have finished with the example,close the Figure window.Example 2: Interpolating DataExample 2: Interpolating DataInterpolation is a process for estimating values that lie between known datapoints.It is important for applications such as signal and image processing anddata visualization.MATLAB provides a number of interpolation functions thatlet you balance the smoothness of data fit with execution speed and efficientmemory use.This example uses a two-dimensional data-gridding interpolation function onthermodynamic data,where volume has been measured for time andtemperature values.It finds the volume values underlying thetwo-dimensional time-temperature function for a new set of time andtemperature coordinates.The example uses an Excel worksheet to organize and display the original dataand the interpolated output data.Excel Link functions copy the data to andfrom MATLAB,execute the MATLAB interpolation function,and invokeMATLAB’s graphics to display the interpolated data in a three-dimensionalcolor surface.2 Solving Sample Problems with Excel LinkTo try this example,click the Sheet3tab on EXLISAMP.XLS.The worksheet contains the measured thermodynamic data in cells A5:A29,B5:B29,and C5:C29.The time and temperature values for interpolation are incells E7:E30and F6:T6respectively.1Make A33the active cell.Press F2,then Enter to execute the Excel Linkfunction that copies the original time data to MATLAB.Move to cell A34andExample 2: Interpolating Data execute the function to copy the original temperature data.Execute thefunction in cell A35to copy the original volume data.22Move to cell A38and press F2,then Enter to copy the interpolation time values to MATLAB.Execute the function in cell A39to copy the interpolation temperature values.3Execute the function in cell A42.griddata is the MATLAB two-dimensional interpolation function that generates the interpolated volume data using the inverse distance method.4Execute the functions in cells A45and A46to transpose the interpolated volume data and copy it to the Excel worksheet.The data fills cells F7:T30, which are enclosed in a border.2 Solving Sample Problems with Excel Link5Execute the function in cell A49.MATLAB plots and labels the interpolateddata on a three-dimensional color surface,with the color proportional to theinterpolated volume data.When you have finished with the example,close the Figure window.Example 3: Pricing a Stock Option with the Binomial ModelExample 3: Pricing a Stock Option with the Binomial ModelThe MATLAB Financial Toolbox provides several functions that computeprices,sensitivities,and profits for portfolios of options or other equityderivatives.This example uses the binomial model to price an option.Thebinomial model assumes that the probability of each possible price over timefollows a binomial distribution;that is,that prices can move to only two values,one up and one down,over any short time period.Plotting the two values,andthen the subsequent two values each,and then the subsequent two valueseach,and so on,over time,is known as“building a binomial tree.”This example uses the Excel worksheet to organize and display input andoutput data.Excel Link functions copy data to a MATLAB matrix,calculate theprices,and return data to the worksheet.Note:This example requires the optional MATLAB Financial Toolbox.2-132 Solving Sample Problems with Excel Link2-14Clickthe Sheet4tab on EXLISAMP.XLS to try this example.The worksheet contains three named ranges:Also,two cells in bindata actually contain formulas:B4:B10named bindataB15named asset_treeB23named value_treeB7contains=5/12B8contains=1/12Example 3: Pricing a Stock Option with the Binomial Model2-15Make D5the active cell.Press F2,then Enter to execute the Excel Link functionthat copies the asset data to MATLAB.Move to D8and execute the functionthat computes the binomial prices,then execute the functions in D11and D12to copy the price data to Excel.The worksheet looks like:Read the asset price tree this way:Period 1shows the up and down prices,Period 2shows the up-up,up-down,and down-down prices,Period 3shows theup-up-up,up-up,down-down,and down-down-down prices,and so on.Ignorethe zeros.The option value tree gives the associated option value for each nodein the price tree.Because this is a put,the option value is zero for pricessignificantly above the exercise price.Ignore the zeros that correspond to a zeroin the price tree.2 Solving Sample Problems with Excel Link2-16Try changing the data in B4:B10and re-executing the Excel Link functions. Note,however,that if you increase the time to maturity(B7)or change the time increment(B8),you may need to enlarge the output tree areas.。
matlab与excel混合
【原创】MATLAB与Excel的混合编程MATLAB与Excel的混合编程有两种方式: 利用MATLAB提供的Excel Link插件,实现Excel与 MATLAB的数据共享利用MATLAB提供的Excel生成器,生成dll组件和VBA代码,dll组件可以进行COM 生成器组件相似的操作,VBA代码则可以在Excel的Visual Basic编译器中直接使用,可以保存为插件.下面分别第一种方法:通过Excel Link实现 Excel和MATLAB的数据共享1. Excel Link是一个软件插件,它可将Excel与MATLAB进行集成,通过MATLAB与Excel的链接,用户可以在Excel的工作空间中,利用Excel的宏编程功能,使用MATLAB的数据处理和图形处理功能进行相关操作,同时Excel保证MATLAB和Excel工作空间数据的交换和同步更新.使用Excel Link时,不必脱离Excel环境,而直接在Excel的工作区或者宏操作中调用MATLAB函数,Excel提供了11个函数来实现链接和数据操作.2. Excel Link的安装Excel Link对运行环境没有要求,只要能够同时运行MATLAB和Excel即可,大概要200KB的空间,在Windows平台下要求先安装Excel,再安装MATLAB.i. Excel的设置,启动Excel,如下图:首先要打开"开发工具"选项卡,"文件"-->"选项"-->"自定义功能区",如下图所示,然后在右侧选中"开发工具".现在"开发工具"-->"加载项",打开"加载宏"窗口,如下图所示:单击"浏览"按钮,在弹出的"路径选择"对话框中,选择MATLAB安装目录下的toolbox\exlink\excellink.xla(在这儿要注意了,由于我使用的是Excel 2010,所以要选择excllink2007.xlam文件,通过将鼠标放在文件文件上,显示的tips可以看到,如下图)文件,然后单击确定后,返回到"加载宏"对话框:此时,单击"确定"按钮,此时Excel Link 将加载了MATLAB,Excel的窗口变化如下图所示:ii. 设置Excel Link的自启动通过"MATLAB"-->"选项(Preferences)"可以打开选项配置窗口.如下图所示,如果不想MATLAB每次在Excel启动时都自动启动,请取消第一个选项,当然也可以通过在数据单元格中输入命令的方式来实现关闭MATLAB的自启动,方法是在Excel的任意一个单元格中输入下面命令:=MLAutoStart("no"),然后回车就行了,要注意不要忘了前面有个等号!!!这该单元格显示的结果是0,也就是说在下次启动时,MATLAB不会随Excel的启动而启动了.该函数将会改变Excel初始化文件中对自启动Excel Link和MATLAB的设置.3. Excel Link的链接管理函数在上面我们已经用到了函数MLAutoStart(),现在讲解下几个函数的用法:MLAutoStart() 设置是否自动启动MATLAB,参数可以是"no"或者"yes"MATLABinit() 初始化Excel Link,并且启动MATLABMLClose() 关闭MATLAB进程MLOpen() 启动MATLAB进程在上面的命令中,MATLABinit只能以宏命令的方式运行,而其它的3个命令可以作为数据单元函数或者宏命令来执行.宏命令的运行方式是"开发工具"-->"宏"-->输入宏名-->"执行".如果在设置中没有设置MATLAB随Excel的自启动,而是手动启动,那么在Excel中启动MATLAB,就需要用MATLABinit来初始化Excel Link并且启动MATLAB.MLClose()在保持Excel继续运行的状态下,用来终止MATLAB进程的运行.4. Excel Link的数据管理函数Excel提供了9个数据管理函数,实现MATLAB与Excel之间的数据复制,并可在Excel中执行MATLAB命令.MATLABfcn() 对于给定的Excel数据,运行MATLAB命令MATLABsub() 对于给定的Excel数据,运行MATLAB命令并指定输出位置MLDeleteMatrix() 删除MATLAB矩阵MLEvalString() 执行MATLAB命令MLGetMatrix() 向Excel数据表中写入MATLAB矩阵的数据内容MLGetVar() 向Excel数据表VBA写入MATLAB矩阵的数据内容MLAppendMatrix() 向MATLAB空间添加Excel数据表的数据MLPutMatrix() 向Excel数据表创建或覆盖MATLAB矩阵MLPutVar() 向Excel数据表VBA创建或覆盖MATLAB矩阵值得注意的是,我们能以数据单元函数的形式或者宏命令的形式调用除MLGetVar()和MLPutVar()以外所有的数据处理函数,而MLGetVar()和MLPutVar()函数只能以宏命令的形式被调用.5. Excel Link 使用的注意事项本帖隐藏的内容*大小写区分,Excel Link函数名对大小写不区分,而在MATLAB中大小写是区分的;*Excel的工作表等通常以"+"或者"="作为起始标记,如=MLPutMatrix("Labels",A4:C4)*大多数Excel Link的函数中有两种定义变量的方式,直接定义和间接定义,将变量用双引号标记即是直接定义,如MLDeleteMatrix("Bonds")即是将MATLAB中的矩阵变量bonds删除,函数中不加双引号的工作区单元地址或者行列名称为间接变量,函数对其指引内容进行操作.工作区单元地址可以包含页表序号,如Sheet3!B1:C11,表示Sheet3中的B1:C11单元格中的数据.*日期,默认的Excel日期是从1990年1月1日开始,而MATLAB的日期是从0000年1月1日开始,因此,在MATLAB中计算使用日期数字,将Excel日期转换为MATLAB日期需要加上常量693960.例如2010年12月13日在Excel中是40525,而在MATLAB中是734485.*建议使用Excel Link的自动计算模式,如果在手动计算模式下使用MLGetMatrix()函数,当在单元格中输入完函数等式时,需要按<F9>键执行,而按下<F9>键将有可能引起其它工作表函数的重复执行,产生无法预料的后果.设置方法如下:在Excel中"文件"-->"选项"-->"公式"-->计算选项前天 13:31 上传下载附件(124.9 KB)*在打开一个包含Excel Link 函数的Excel数据表时,Excel会自动从上住下,从左到右执行这些函数,所有可能出现如"#command!"或者"#nonexist"等Excel错误提示,只老需要关闭所有的MATLAB图形窗口,然后按<F2>键和<Enter>键重新单步执行单元格里的函数就可以了.。
MATLAB与Excel在模糊控制中的应用
MAT LAB与Excel在模糊控制中的应用付家才,滕吉荣(黑龙江科技学院电气与信息工程学院,哈尔滨150027)摘要:详细介绍了在Excel中链接MAT LAB的过程及Excel Link函数,并用实例介绍如何将这2个软件有机地运用在模糊控制系统中,从而很好地解决了模糊理论中有关语言变量隶属函数的问题。
关键词:MAT LAB;Excel;模糊控制;隶属函数中图分类号:TP31;TP273 文献标志码:A 文章编号:100320794(2009)0120170203 Application of M AT LAB and Excel in Fuzzy ControlFU Jia-cai,TENG Ji-rong(C ollege of E lectric and In formation Engineering,Heilongjiang Institute of Science and T echnology,Harbin150027,China) Abstract:Introduces Excel link functions and the process of linking MAT LAB up to Excel in detail.Besides, the im plementation of a real exam ple explains how to apply tw o s oftwares to fuzzy control system,s o that it per2 fectly s olves the problem of the language varies membership functions.K ey w ords:MAT LAB;Excel;Fuzzy control;membership function0 引言MT LAB是国际公认的优秀数学应用软件之一。
MATLAB与Word和Excel的组合使用
2 在Word中使用Notebook
• 输入单元 • 输出单元 • 自初始化单元
3 Excel Link简介
• 配置与安装
• Spreadsheet Link EX工具栏
选项
功能
startmatlab 在Excel中启动MATLAB
putmatrix 向MATLAB中输入数据
getmatrix 从MATLAB中获得数据
MATLAB与Word和Excel 的组合使用
1 Notebook简介
• Notebook的配置 • Notebook的启动
2 在Word中使用Notebook
下拉菜单命令 Define Input Cell Define AutoInit Cell Define Calc Zone Underfine Cells Purge Selected Output Cells Group Cells Ungroup Cells Hide Cell Markers Toggle Graph Output for Cell Evaluate Cell Evaluate Calc Zone Evaluate M-book Evaluate Loop Bring MATLAB to Front Notebook Options
MLPutVar
将Excel中的数据创建成或覆盖MATLAB中的矩阵
– MLautoStart – MATLABinit – MLOpen – MLClose
4 Excel Link函数
• 数据管理函数
函数名
功能
matlabfcn
对指定的Excel数据执行MATLAB命令
matlabsub
对指定的Excel数据执行MATLAB命令，并指定输出位置
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
用Excel Link实现Excel与Matlab混合编程来源: 神经网络作者：AI俱乐部网友评论0 条浏览次数48Excel Link是一个在Windows环境下实现Excel与Matlab进行链接的插件。
通过连接Excel 和Matlab，用户可以在Excel工作表空间和宏编程工具中使用Matlab的数值计算，图形处理等功能，不需要脱离Excel环境。
同时由Excel Link来保证两个工作环境中的数据交换和同步更新。
1. Excel Link的安装和和设置首先，在系统中安装Excel软件。
然后安装Matlab和Excel Link，用Matlab安装盘开始安装，选择自定义安装中，在选中组件ExcelLink，如下图所示：安装完Excel Link后还需要在Excel中进行一些设置后才能使用。
启动Excel，选择菜单“工具”项下的“加载宏”项，弹出如下对话框：选中Excel Link项。
如果该项不存在，则通过浏览目录，在目录％MATLAB％toolboxexlink下找到excllink.xla文件，如下图示，并确定。
选中ExcelLink项并确定后，在Excel中多了一个Excel Link工具条，如下图示: 经过以上的设置后就可以开始使用Excel Link了。
2. ExcelLink连接管理函数(1) Matlabinit该函数只能在宏子例程中使用。
初始化ExcelLink和启动Matlab进程。
只有在MLAutoStart 函数中使用“no”参数，才需要手动使用Matlabinit来初始化ExcelLink和启动Matlab进程，如果使用参数“yes”，则Matlabinit是自动执行的。
使用语法：Matlabinit(2) MLAutoStart设置自动启动Matlab和ExcelLink。
在工作表中的使用语法：MLAutoStart("yes")MLAutoStart("no")在宏中的使用语法：MLAutoStart "yes"MLAutoStart "no"使用“yes”参数，则当Excel启动时，自动启动Matlab和ExcelLink；如果使用参数“no”，则当Excel启动时，不启动Matlab和ExcelLink。
如果在此之前它们已经启动，则无任何影响。
(3) MLClose终止Matlab进程并删除Matlab工作空间的所有变量。
并通知Excel，Matlab不再运行。
在工作表中的使用语法：MLClose（）在宏中的使用语法：MLClose(4) MLOpen启动Matlab进程。
如果Matlab进程已经启动，则MLOpen函数不进行任何操作。
在使用MLClose关闭Matlab进程后使用MLOpen来重新启动Matlab。
在工作表中的使用语法：MLOpen（）在宏中的使用语法：MLOpen3. ExcelLink数据管理函数(1) Matlabfcn根据给定的Excel数据执行Matlab命令。
在工作表中使用时的语法：matlabfcn(command，inputs)参数command，Matlab将执行的命令，命令需要写成“command”（使用双引号引起来）的形式。
参数inputs 传给Matlab命令的变长输入参数列表。
列表是包含数据的工作表单元格范围。
函数返回单一数值或者是字符串，结果返回到调用函数的单元格中。
例如matlabfcn（”sum”，B1:B10）；把从B1到B10的单元格中数据相加，如下图示：并将结果返回到当前的活动单元格。
即A11，结果如下图。
(2) Matlabsub根据给定的Excel数据执行Matlab命令，并将结果返回到指定的单元格中。
在工作表中的使用语法：matlabsub（command，edat，inputs）command和inputs参数的与matlabfcn相同。
参数edat，指定返回值写入在工作表中的位置。
如果edat用双引号引起来，则edat必须是单元格地址或范围的名字。
如果参数不用引号引起edat的形式，则通过计算获得矩阵名。
例如：matlabsub（”sum”，”A1”，B1:B10）；把工作表中从单元格B1到B10的数据相加，并将结果返回到单元格A1中。
注意：edat指定的位置不能包含matlabsub所在的位置。
(3) MLAppendMatrix将Excel工作表中的数据追加到Matlab中指定的矩阵中如果该矩阵不存在，则创建矩阵。
在工作表中使用的语法：MLAppendMatrix(var_name，mdat)在宏中使用的语法：MLAppendMatrix var_name，mdat注意要追加的数据维数要和原矩阵中的维数相匹配，否则出错。
例如：MLAppendMatrix（”a”，A1:A2）；假设矩阵a是个2行3列的矩阵，如下图:将A1：A2中的数据追加到矩阵后，如下图示成为矩阵的第四列，如下图示。
单元格B1中是字符a，函数MLAppendMatrix（B1，A1:A2）的作用于MLAppendMatrix （”a”，A1:A2）相同。
(4) MLDeleteMatrix删除Matlab空间中指定的矩阵在工作表中使用的语法：MLDeleteMatrix（var_name）；在宏中使用的语法：MLDeleteMatrix var_nameVar_name，是要删除的矩阵名，如果矩阵名在引号内“var_name”的形式，则直接指定变量名，如果var_name不用引号引起来，则通过计算获得实际矩阵的名字。
例如，单元格B1中的内容为a，MLDeleteMatrix（B1），则表示删除Matlab工作空间的矩阵a，它等价于MLDeleteMatrix（”a”）(5) MLEvalString将命令（写成字符串的形式）传到Matlab中执行。
在工作表中使用的语法：MLEvalString（command）；在宏中使用的语法：MLEvalString command参数command如果是用引号引起来”command”的形式，则是直接指定命令；如果不用引号引起来command的形式，则command必须是包含了命令字符串的工作表的单元格地址或者是范围。
例如：MLEvalString（”b=magic(4)”）；表示在Matlab中执行命令b=magic(4)；(6) MLGetMatrix将指定的Matlab矩阵写入到Excel工作表中的指定位置。
在工作表中使用的语法：MLGetMatrix（var_name，edat）在宏中使用的语法：MLGetMatrix var_name，edat参数Var_name，是要写入工作表的矩阵名，如果矩阵名在引号内“var_name”的形式，则直接指定变量名，如果var_name不用引号引起来，则通过计算获得实际矩阵的名字。
参数edat指定了矩阵写入工组表的位置。
如果参数是用引号引起”edat”的形式，则是直接指定矩阵名，如果参数不用引号引起edat的形式，则通过计算获得矩阵名。
例如：MLGetMatrix（”a”，”sheet1!B1”）；将矩阵a写入工作表sheet1以单元格B1起始的位置，如果a是一个2行3列的矩阵，则矩阵占据sheet1的B1到D2的空间。
如果在A1中有字符串a，则MLGetMatrix（A1，” sheet1!C1”）的作用与MLGetMatrix（”a”，”sheet1!C1”）相同。
函数运行结果如图示。
(7) MLGetVar将MatLab矩阵传送给Excel VBA变量，只能在宏子例程中使用。
使用语法：MLGetVar ML_var_name，VBA_var_name参数ML_var_name是将获取的矩阵名。
如果矩阵名在引号内“ML_var_name”的形式，则直接指定变量名，如果ML_var_name不用引号引起来，则通过计算获得实际矩阵的名字。
参数VBA_var_name，将其Matlab矩阵数据传给的VBA变量名，不用引号将其引起来。
例如：Sub Fetch()MLGetVar "J"，DataJEnd Sub表示将Matlab矩阵J的数据写入到VBA变量DataJ中。
(8) MLPutMatrix用指定位置的Excel工作表中的数据，创建或者覆盖Matlab矩阵。
在工作表中使用的语法：MLPutMatrix（var_name，mdat）在宏中使用的语法：MLPutString var_name，mdat参数var_name是将有被创建或者被覆盖的矩阵名。
如果指定的矩阵不存在，则创建该矩阵，如果矩阵已经存在，则覆盖该矩阵。
参数mdat，指定工作表中的位置。
例如：工作表中有A1到C2的数据，使用函数MLPutMatrix（”c”，A1:C2），则可以将这些数据写到Matlab矩阵c中，如下图示。
注意：如果修改工作表中的数据，则Matlab中的矩阵相应发生变化。
如果把数据剪切到别的地方，如D1到F2，则函数MLPutMatrix（”c”，A1:C2）会自动更改为MLPutMatrix（”c”，D1:F2）(9) MLPutVar使用VBA变量的数据创建或者覆盖Matlab 矩阵。
只能在宏子例程中使用。
使用语法：MLPutVar ML_var_name，VBA_var_name参数ML_var_name是将被创建或覆盖的矩阵名。
如果矩阵名在引号内“ML_var_name”的形式，则直接指定变量名，如果ML_var_name不用引号引起来，则通过计算获得实际矩阵的名字。
参数VBA_var_name，将其数据传给Matlab矩阵的VBA变量名，不用引号将其引起来。
如果VBA_var_name变量包含字符串的数据，则输出到Matlab为元胞数组格式。
例如：Sub Put()MLPutVar "K"，DataKEnd Sub用VBA变量DataK中的数据创建或着覆盖Matlab矩阵K。
4. 补充使用Excel Link的注意事项（1）Excel工作表通常以“＝”作为起始标记，例如＝matlabfcn（”sum”，B1:B10）；（2）大多数的Excel Link函数中有两种定义变量的方式：直接定义，即将变量用双引号标记则是直接定义变量，例如MLGetMatrix（”bonds”，”sheet1!C1”），其中bonds是直接定义的变量；间接定义，函数中的不用双引号的工作表单元地址或行列名称被看作是间接变量，函数对其指引的内容进行操作。
工作表单元地址可以包含页表序号，例如MLDeleteMatrix（B1）；单元格B1中的内容为a，则相当于执行MLDeleteMatrix（”a”）；（3）建议使用Excel Link的自动计算模式。