第3章 MATLAB与Excel文件的数据交换

第3章MATLAB与Excel文件的数据交换 3 1案例背景 Excel是一款非常优秀的通用表格软件 在学习 工作与科研中大量的数据可能都是以Excel表格的方式存储的 Excel在矩阵计算 数据拟合与优化算法等方面的功能尚不足 Excel与MATLAB相结合是处理复杂数据问题的有效方法 如何利用MATLAB强大的数值计算功能处理Excel中的数据 首要解决的问题就是如何将Excel中的数据导入MATLAB中或将MATLAB数值计算的结果转存入Excel中 本章主要介绍以函数方式与Exlink宏的两种方法实现MATLAB与Excel的数据交互 3 2数据交互函数 3 2 1获取文件信息函数xlsfinfo 在读取Excel目标数据文件前 可以通过xlsfinfo函数获取该文件的相关信息 为MATLAB函数的后续操作获得有效信息 例如 文件类型 文件内部结构 相关的软件版本等 xlsfinfo函数语法 typ desc fmt xlsfinfo filename 测试函数M文件CaseXlsfinfo m如下 文件名称 excel xls typ desc fmt xlsfinfo funddata xls 文件在当前工作目录下 直接输入文件名称即可 也可以输入文件地址的形式调用xlsfinfo函数 typ desc fmt xlsfinfo C Users Administrator Desktop funddata xls 3 2 2读取数据函数xlsread MATLAB从Excel中读取数据的函数为xlsread xlsread函数是使用频率较高的函数之一 xlsread函数语法 data textdate xlsread filename data xlsread filename sheet rang 1 data textdate xlsread filename 函数测试M文件CaseXlsRead m如下 调用xlsread函数 data textdate xlsread funddata xls textdate的第一列为日期文本 第一行为列名称 Hs300Date textdate 2 14 1 2 data xlsread filename sheet range 3 2 2读取数据函数xlsread 测试函数m文件CaseXlsRead m如下 数据位置为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 2 3写入数据函数xlswrite MATLAB往Excel中写入数据的函数为xlswrite xlswrite函数语法 status message xlswrite filename M sheet range status 1时说明写入成果 0时写入失败 测试函数M文件CaseXlsWrite m如下 生产随机数据X randn 1 10 将X随机数据写入excel文件 表 sheet2 中 status message xlswrite excel xls X sheet2 3 3Excel Link宏 如果Excel文件数据量太大 1GB 以上 使用函数进行数据交互存在一定问题 例如Java内存会溢出等 数据量较大的时候可以使用ExcelLink宏进行数据交互 如图所示 MATLAB提供使其能与Excel互动操作的ExcelLink宏 ExcelLink使得数据在MATLAB与Excel之间随意交换 以及在Excel下调用MATLAB的函数 ExcelLink将MATLAB的强大的数值计算功能 数据可视化功能与Excel的数据sheet功能结合在一起 下面就简单介绍ExcelLink的基本操作 3 3 1加载Excel Link宏 Excel2003上的加载方法 在Excel工作窗口中选择 工具 加载宏 菜单项 在弹出的 加载宏 对话框中单击 浏览 按钮 弹出 浏览 对话框 根据MATLAB的安装路径查找 toolbox exlink excllink xla 双击对应文件 如图所示 exlink加载方法示意图 回到加载宏对话框 接着选择ExcelLink2 3forusewithMATLAB选项 单击 确定 按钮 如图1所示 若Excel的左上方出现startmatlab putmatrix getmatrix evalstring等选项 说明ExcelLink加载成功 如图2所示 xlink加载方法示意图 exlink加载方法示意图 3 3 1加载Excel Link宏 3 3 2使用Excel Link宏 各项功能如下 startmatlab 单击启动MATLAB putmatrix 将Excel的数据传输到MATLAB中 如图1所示 getmatrix 将MATLAB的数据传输到Excel中 exlink使用方法示意图 3 3 3Excel2007加载与使用宏 1 加载方法在Excel工作窗口单击office按钮 选择Excel选项 在弹出的 Excel选项 对话框中单击 加载项 再单击 转到 按钮 如下图所示 Excel2007加载Exlink 2 使用方法在Excel2007加载项下可以发现Exlink相关的按钮 如下图所示 具体使用方法与Exlink在Excel2003中的使用方法一样 3 3 3Excel2007加载与使用宏 excel2007使用exlink 3 4交互实例 3 4 1基金相关性的计算 例1funddata xls中存储着沪深300指数的价格与博时主题行业 嘉实沪深300 南方绩优成长的复权数据 如表所列 要求计算出每只基金与沪深300指数的相关性 3 4交互实例 3 4 1基金相关性的计算 M程序FundCorrelationCase m如下 typ desc fmt xlsfinfo funddata xls 读取数据 data textdate xlsread funddata xls 计算相关性R corrcoef data 写入到excel数据 status message xlswrite funddata xls R sheet2 B2 E5 行名称与列名称textdate textdate 2 2 5 status message xlswrite funddata xls textdate sheet2 B1 E1 status message xlswrite funddata xls textdate sheet2 A2 A5 textdate 表示转置即将行变为列 3 4 2多个文件的读取和写入 在实际的项目编程中 很多时候遇到从很多文件中读取数据 若逐个文件进行手工操作不仅身心疲惫 而且容易出错 例如 指数成分股与权重数据每天一个Excel文件 文件名为 000016weightnextday20100104 xls 000016weightnextday20110630 xls 000016weightnextday20120104 xls等 程序化读取的关键是将文件名自动化 集中调用文件TEXT2015 xls TEXT2016 xls TEXT2017 xls fileNum 2015 2017 cofilename TEST dataa zeros 10 3 datab zeros 10 3 datac zeros 10 3 fori 1 3filename cofilename num2str fileNum i dataa i xlsread filename sheet1 A2 A11 datab i xlsread filename sheet1 B2 B11 datac i xlsread filename sheet1 C2 C11 end 3 5数据的平滑处理 在对时间序列数据 如信号数据或股票价格数据 进行统计分析时 或许存在数据的缺失或奇异 例如ST股票反复的停牌 往往需要对数据进行平滑处理 本节介绍基于MATLAB的数据平滑方法 主要介绍smooth函数 smoothts函数和medfilt1函数的用法 3 5 1smooth函数 MATLAB曲线拟合工具箱中提供了smooth函数 用来对数据进行平滑处理 1 yy smooth y 利用移动平均滤波器对列向量y进行平滑处理 返回与y等长的列向量yy 移动平均滤波器的默认窗宽为5 yy中元素的计算方法如下 yy 1 y 1 yy 2 y 1 y 2 y 3 3yy 3 y 1 y 2 y 3 y 4 y 5 5yy 4 y 2 y 3 y 4 y 5 y 6 5yy 5 y 3 y 4 y 5 y 6 y 7 5 2 yy smooth y span 用span参数指定移动平均滤波器的窗宽 span为奇数 3 yy smooth y method 用method参数指定平滑数据的方法 method是字符串变量 可用的字符串如表所列 4 yy smooth y span method 对于由method参数指定的平滑方法 用span参数指定滤波器的窗宽 对于loess和lowess方法 span是一个小于或等于1的数 表示占全体数据点总数的比例 对于移动平均法和SavitzkyGolay法 span必须是一个正的奇数 只要用户输入的span是一个正数 smooth函数内部会自动把span转为正的奇数 数据平滑t linspace 0 2 pi 500 y 100 sin t plot t y noise normrnd 0 15 500 1 y y noise figureplot t y holdonyy1 smooth y 50 figureplot t y t yy1 r yy2 smooth y 0 15 lowess figureplot t y t yy2 b 3 5 2smoothts函数 smoothts函数的输入参数input是一个金融时间序列对象或行导向矩阵 其中金融时间序列对象是MATLAB中由ascii2fts或fints函数所创建的一种对象 行导向矩阵是指用行表示观测数据集的矩阵 若input是一个行导向矩阵 input的每一行都是一个单独的观测集 调用格式为 output smoothts input output smoothts input b wsize output smoothts input g wsize stdev output smoothts input e n 方法g b e分别表示高斯窗法 盒子法 指数法 wsize表示窗口 默认值为5 stdev表示高斯窗方法标准差 默认值为0 65 n是指数法的窗宽或指数因子 默认值为0 333 3 5 2smoothts函数 例2现有上海股市日开盘价 最高价 最低价 收盘价 收益率等数据 时间跨度为2005年1月4日至2007年4月3日 共510组数据 完整数据保存在文件StockPriceData xls中 其中部分数据如下图所示 试调用smoothts函数对日收盘价数据进行平滑处理 上海股市日开盘价 最高价 最低价 收盘价 收益率等部分数据 画价格走势图x xlsread StockPriceData xls price x 4 提取矩阵x的第4列数据 即收盘价数据figure 新建一个图形窗口 绘制日收盘价曲线图 黑色实线 线宽为2plot price k LineWidth 2 为X轴和Y轴加标签xlabel 观测序号 ylabel 上海股市日收盘价 盒子法 用盒子法平滑数据 窗宽为30output1 smoothts price b 30 用盒子法平滑数据 窗宽为100output2 smoothts price b 100 figure 新建一个图形窗口plot price 绘制日收盘价散点图holdon 绘制平滑后曲线图 黑色实线 线宽为2plot output1 k LineWidth 2 绘制平滑后曲线图 黑色点划线 线宽为2plot output2 k LineWidth 2 为X轴和Y轴加标签xlabel 观测序号 ylabel Boxmethod 为图形加标注框legend 原始散点 平滑曲线 窗宽30 平滑曲线 窗宽100 location northwest 用高斯窗方法平滑数据 output3 smoothts price g 30 窗宽为30 标准差为默认值0 65output4 smoothts price g 100 100 窗宽为100 标准差为100figure 新建一个图形窗口plot price 绘制日收盘价散点图holdon 绘制平滑后曲线图 黑色实线 线宽为2plot output3 k LineWidth 2 绘制平滑后曲线图 黑色点划线 线宽为2plot output4 k LineWidth 2 为X轴和Y轴加标签xlabel 观测序号 ylabel Gaussianwindowmethod legend 原始散点 平滑曲线 窗宽30 标准差0 65 平滑曲线 窗宽100 标准差100 location northwest 用指数法平滑数据 output5 smoothts price e 30 用指数法平滑数据 窗宽为30output6 smoothts price e 100 用指数法平滑数据 窗宽为100figure 新建一个图形窗口plot price 绘制日收盘价散点图holdon 绘制平滑后曲线图 黑色实线 线宽为2plot output5 k LineWidth 2 绘制平滑后曲线图 黑色点划线 线宽为2plot output6 k LineWidth 2 为X轴和Y轴加标签xlabel 观测序号 ylabel Exponentialmethod legend 原始散点 平滑曲线 窗宽30 平滑曲线 窗宽100 location northwest 3 5 medfilt1函数 1 y medfilt1 x n 对向量x进行一维中值滤波 返回与x等长的向量y 这里的n是窗宽参数 当n是奇数时 y的第k个元素等于x的第个元素至第个元素的中位数 当n是偶数时 y的第k个元素等于x的第个元素至第个元素的中位数 n的默认值为3 2 y medfilt1 x n blksz 用for循环 每次循环输出blksz个计算值 默认情况下 blksz length x 当x是一个矩阵时 通过循环对x的各列进行一维中值滤波 返回与x具有相同行数和列数的矩阵y 3 y medfilt1 x n blksz dim 用dim参数指定沿x的哪个维进行滤波 例 产生一列正弦波信号 加入噪声信号 然后调用medfilt1函数对加入噪声的正弦波进行滤波 平滑处理 产生一个从0到2 pi的向量 长度为500t linspace 0 2 pi 500 y 100 sin t 产生正弦波信号 产生500行1列的服从N 0 152 分布的随机数 作为噪声信号noise normrnd 0 15 500 1 y y noise 将正弦波信号加入噪声信号figure 新建一个图形窗口plot t y 绘制加噪波形图xlabel t 为X轴加标签ylabel y sin t 噪声 为Y轴加标签 中值滤波 调用medfilt1对加噪正弦波信号y进行中值滤波 并绘制波形图yy medfilt1 y 30 指定窗宽为30 对y进行中值滤波figure 新建一个图形窗口plot t y k 绘制加噪波形图holdon 绘制平滑后曲线图 黑色实线 线宽为3plot t yy k LineWidth 3 xlabel t 为X轴加标签ylabel 中值滤波 为Y轴加标签legend 加噪波形 平滑后波形 3 6数据的标准变换 对于多元数据 当各变量的量纲和数量级不一致时 往往需要对数据进行变换处理 以消除量