Excel VBA 编程与应用

 

课程名称：SEC214 Excel VBA编程与应用Excel VBA Programming and Application

课程性质：本学院选修,实验课程

学分课时：2学分，32课时，其中实验课时16课时

主讲教师：冯建芬副教授

所属院系：金融学院金融工程系

          电话：64495048  E-mail:danxin_97@163.com    

教学对象：金融学院大学二年级学生

授课方式：讲授、实验（上机）、案例相结合

考核方式：小组作业（20%）小组编程任务

课堂实验（10%）个人编程任务，要求当堂完成，当堂提交

期中考试：上机考试（30%）在规定时间内完成编程任务，并提交

期末考试：论文考试（40%）分小组完成编程和论文撰写

其中小组作业和课堂实验属于平时成绩，占30%,期中考试占30%，期末考试占40%。

学术诚信：本课程对于学生的学术诚信的要求遵从《对外经济贸易大学学生违纪处分条例》、《对

外经济贸易大学学生学习违纪处分实施细则》、《对外经济贸易大学考场纪律》的规定。

教学方式：课堂讲授占比30%，上机实验占70%，教学中强调动手能力，采用实验教学、实践教学和研讨教学相结合的方式。教学全程在金融实验室完成，结合excel软件、Ocale实验教学平台、金融数据库和PPt进行教学。

出勤要求：遵从《对外经济贸易大学本科生课堂学习规范》，要求学生关闭一切电子设备；不能

无故缺席上课；上课专心听讲，积极参与课堂讨论；课后认真复习课堂上讲授内容，独立完成教师布置的任务；并预习新课。学生缺勤不得多于总课时的四分之一。教师可以根据考勤情况决定学生是否可以参加考试、是否扣分。

 

一、课程简介：

Exce VBA是金融机构在财务数据管理、金融数据处理中使用最广泛、成本最小的数据处理、展现与分析软件，大型金融数据库如彭博、万德、路透等都是通过excel与其他程序语言接口进行数据供给的，大部分金融实验软件也是通过exce VBA功能实现金融模型的量化。因此深入了解excel的VBA操作可以提高学生的动手能力、就业能力和获取更多帮助资源的能力。本课程作为金融学院专业选修课程，系统地讲解Excel VBA的基本语法及在数据处理中的应用，具体内容包括：excel表格的深入操作，excel数据分析工具应用，宏与VBA程序设计，窗体、菜单及数据管理程序，VBA与外部数据获取，VBA与其他软件接口管理、excel VBA的金融应用案例解析等。

二、教学目标

通过本课程的学习，希望学生能够熟练运用excel VBA进行更高效更快捷的数据处理和管理，能够轻松解读可获得的excel VBA程序，并结合自我需求进行程序调制，有效提高其动手能和协助教师进行学术研究的能力，提升专业的实验课程或专业课程的实验环节的实验效果，为进行大三的实习，大四就业打好基础。

三、课程学习资料

   1.教材：

《Excel与数据处理》（第5版），杜茂康 编著，电子工业出版社，2014

2.参考资料

《Excel 2010 VBA编程与实践》，罗刚君 章兰新 黄朝阳　编著，电子工业出版社，2010

《基于excel和VBA的高级金融建模》，朱世武，何剑波 译，中国人民大学出版社，2006

《excel VBA快速上手之宝典》，电子书，2006

Mastering.Financial.Mathematics.in.Microsoft.Excel

Excel +SQL server 数据库管理技术详解

四、学习效果及达成途径

   1.学习效果：

  通过本课程的学习，希望达成的学习效果如下：

1.能够灵活运用excel内置函数、excel公式、数组公式，进行单元格数据处理操作。

2. 掌握excel的数据处理工具的应用，包括模拟运算表、单变量求解、数据分析工具和规划求解工具。

3.掌握VBA语言的基本编程语法，能够解读VBA程序并进行改进。

4.掌握通过宏对单元格、单元格区域以及工作表甚至多个工作簿的操作

5.能够通过修改图表操作的宏，满足自己通过宏进行图表操作的需求

6.掌握通过VBA调用excel内置函数、加载宏工具的方法

7.能够运用VBA语言定义函数和宏扩展excel的数据处理和展现功能。

8.掌握如何通过VBA查询和筛选金融数据库的数据。

9.掌握如何通过VBA调用SQL查询语言进行数据查询。

10.掌握如何结合excel设计工具创建调用宏的工具按钮、菜单等，以此完成应用软件开发的初步操作。

2.达成学习效果的途径

 善于动手，课前完成例题程序的上机任务；上课跟着老师思路走，积极参与课堂讨论；充分利用实验教学平台的讨论区和教师指定的答疑时间及时答疑解惑；按时完成课堂实践任务和课下作业，认真准备期中上机考试，认真完成期末论文。

五、教学进度计划表 

本课程教学周为16 周，具体安排如下

周次	内容提要	参考资料	作业与考试
1	第一章：excel 公式与函数应用	教材第1,5,9,11章	课下小组作业
2	上机实验：公式应用与数学模型转换		课堂实践测验
3	第二章：excel 的数据分析工具应用	教材第8章	课下作业
4	上机实验：excel 数据分析工具应用		课堂实践测验
5	第三章：VBA 编程简介 3.1 宏与录制宏 3.2 VBA 程序设计基础	教材第12章；excel VBA快速上手之宝典	课堂实践测验；课下作业
6	3.3 VBA 的程序结构 3.4 VBA 程序的两种定义形式：子程序与函数		课下作业
7	上机实验：宏与函数的相互转换		课堂实践测验
8	第四章  自定义宏的操作 4.1  宏对单元格的引用、编辑和修改 4.2  宏对单元格区域的引用、编辑和修改 4.3  宏对多个工作表的操作	教材第12章，《Excel 2010 VBA编程与实践》
9	4.4  宏对多个工作簿的操作 4.5  正则表达式在查询中的使用		课堂实践测验
10	4.6  自动宏 4.7  宏对图表的操作		课下作业
11	上机实验:  编制宏练习		课堂实践测验
12	第五章自定义函数的操作 5.1  自定义函数举例 5.2  为自定义函数添加帮助信息 5.3  金融数据库的数据查询与下载		课下作业；
13	上机实验：自定义函数练习		课堂实践测验
14	第六章  excel 查询与外部数据交换 6.1 Excel 与数据库概述    6.2 Microsoft Query 与外部数据库访问    6.3 Excel 与其他文件类型的相互转换   6.4    SQL 查询工作表		分小组布置期末论文任务； 课下作业
15	上机实验：excel 数据查询与数据转换练习		课堂实践测验
16	第七章excel VBA应用综合实践		小组作业研讨
17-18	完成期末论文

 

六、教学内容

第一章excel公式与函数应用

【教学目的和要求】：

1. 使学生了解excel公式的书写规则、操作算符、操作对象；

2. 能够使用excel公式对单元格或者单元格区域进行绝对引用和相对引用、会进行数组公式计算；

3. 能够结合excel的各种内置函数灵活使用excel的公式，进行单元格数据处理操作；

4. 能够为单元格或者单元格区域命名，并在公式中灵活使用单元格名称进行数据处理操作；

5. 能够通过解读已有的excel单元格数据处理文件并还原数据处理过程；

【主要内容】

1.1 excel公式应用

• 公式的书写规则
• 公式的操作算符与操作对象
• 公式与单元格引用
• 数组公式与名字应用
• 公式的复制与自动重算
• 函数应用
• 函数简介
• 常用工作表函数分类
• 工作表函数应用举例
• 课堂练习：公式应用与数学模型转换

教学总时数：4，其中实验课时2课时

参考资料：

1.《Excel与数据处理》（第5版），杜茂康　等编著，电子工业出版社，2014,第一章、第五章、第九章、第十一章；

2.Mastering Financial Mathematics in Microsoft Excel，电子书

3. 《基于excel的高级金融建模》，杰克逊，斯汤顿 著，朱世武，何剑波 译，中国人民大学出版社，2006， 第二章

作业与练习：

课堂实验（2课时）

1.现有美国住宅抵押贷款利率历史数据,但日期不是日期格式的数值型数据,请将日期数据修正为数值型数据，然后画出美国30年贷款利率和1年期贷款利率的时序图，横轴为日期，纵轴为利率。                                                                                                                                                                                                                                                                                                                                                                                             

提示:

 步骤1. 首先将数据通过字符串函数运算改为日期格式的字符串表示,如”10-2-1992”先改为”1992-10-2”,  “12/11/2009”改为”2009/12/11”

 步骤2. 通过year（）、month（）、day()函数提取字符串中的年、月、日，然后通过date（）形成日期。

 

2.由于将股票代码存储成数值型数据，导致沪市股票代码前面的0丢失。

 

1）请结合函数，利用公式补足前面的0，将股票代码改为正确的显示型式。

提示所有股票代码都是6位数字表示的，所以不足6位的要在前面添“0”.

如1改为000001.

2）有时候为了方便，需要将上述股票代码转换为数值格式的，请结合函数利用公式将其转换为数值格式，如000001，转换为1.

3.利用随机数函数进行随机分组。

给班里同学每三个人分成一组，剩余不足三人的最后形成一个组。

提示：

1）.首先在班级名册上利用rand()产生随机数

2）.对表格按照随机数进行升序排列

3）.产生一列作为每个同学的序号

4）.按照序号进行每三个人一组的分组，给出每个人的组号

4.利用countif()函数统计在“选择银行的原因”列表中，每一项（A,B,C,D,E,F,G,H,I,J,文字列为“其他原因”）选择的个数，如选择“ABCD”的，则在“A”的统计中应该计算，在“B”的统计中也应该计算

5. 概率统计函数应用：均值、方差、标准差、概率分布值、随机数

给出美国抵押贷款利率数据。

1）利用指定首行为名称，为利率数据单元格区域定义名称

2）利用该名称和统计函数每一列贷款利率的均值、方差、标准差、中位数、众位数

3）以给出的30年期贷款利率为样本，计算样本5%的分位数。

4）利用rand()函数，无重复地对30年期的贷款利率进行抽样，随机抽出100个样本

 

课下小组作业

以组为单位，每组三人，共计18个组，

每组选择三个作业的其中一个进行excel文件的公式解读，形成小组报告和视频报告。

要求：

1.通过查找文献给出该模型的介绍，不需要给出模型的推导或者深入的理解原理。

2.对应模型介绍，给出每个作业中模型实现的完整步骤，并给出在实现过程中每一步对应的excel公式。

不限字数。

 

第二章excel的数据分析工具应用

【教学目的和要求】

1.了解如何运用excel的模拟运算表、单变量求解功能进行重复运算和变量求解；

2.了解如何使用excel的加载宏功能加载数据分析工具和规划求解工具；

3.能够使用excel提供的上述工具进行数据计算，如模拟运算、非线性方程求解、回归分析、产生一定概率分布的随机数等

【主要内容】

2.1 excel的模拟运算表应用

• 建立单变量模拟运算表
• 建立双变量模拟运算表
• 应用练习：

    2.2. excel 的单变量求解与规划求解应用

• 单变量求解与规划求解的使用方法
• 单变量求解与规划求解的应用举例

    2.3. excel的数据分析工具应用

• 回归工具应用举例
• 随机数发生器与抽样工具应用举例
• 统计分析工具应用
• 数据分析工具应用练习

教学总时数：4课时，其中实验课时2课时

•  

1. 《Excel与数据处理》（第5版），杜茂康　等编著，电子工业出版社，2014,第八章

2. 《基于excel的高级金融建模》，杰克逊，斯汤顿 著，朱世武，何剑波 译，中国人民大学出版社，2006， 第二章，第六章

作业与练习：

课堂实验（2课时）：

1.分别利用单变量求解和规划求解估计期权价值的隐含波动率；

1)已知某产品的价值满足如下表达式：

 

 

 

其中

 

 

N(.)表示标准正态分布的累积分布函数；

这里函数的取值是已知的，即产品的价值，另外参数S0,X,T,r都是已知的，参见excel表格第二讲练习.xlsx, 练习2.1，

试通过单变量求解计算σ的取值。（注：σ取值在(0,1)之间是合理的，否则不合理）

2.利用规划求解估计非线性方程组的解；

已知公司权益价值满足如下表达式：

  （1）

同时，权益价值的波动率满足如下表达式：

  （2）

其他参数取值已知的情况下，通过规划求解，估计公式（1），（2）联立方程组中的V0 和，具体数值和求解变量初值见第二讲练习.xlsx, 练习2.2

约束条件：V0>E0, V0>D,0<σV<1

3.求解练习2.3中，price与Z1-Z10之间的回归系数，其中price为因变量，Z1-Z10为自变量，设常数项为0，分别使用数据分析工具和linest函数实现。

4.现有中信证券的历史交易记录，利用数据分析工具中的抽样工具随机抽出1000个复权收盘价；

5.设每一月的股票价格满足如下递推关系：

 

其中deltat=1/12，mu=0.05，σ=0.30, S0=10, ε为标准正态分布的随机变量，在递推过程中任何两步的ε是相互独立的。试通过随机数发生器，产生股票价格1年的价格路径100条，每条路径上有12个月的股票价格S0,S1,….,S12.并绘制出这100条路径的折线图（在同一幅图中）

 

第三章：VBA编程简介

【教学目的和要求】

1.能够通过录制宏功能了解VBA程序的基本构造；

2.了解VBA编程的原理和特点

3.掌握VBA的基本编程语法

4.能够解读VBA的程序，并在此基础上进行修改

【主要内容】

• 宏与录制宏

   3.1.1 excel中宏的概念

• 录制宏
• 编辑、查看宏代码
• 宏的安全性配置
• 宏的保存与执行

3.2 VBA程序设计基础

   3.2.1 VBA的变量定义与数据类型

• 运算符
• 数组定义与引用
• 对象、属性和方法的概念
• 如何查找和应用对象、属性和方法

3.3 VBA的程序结构

• 顺序结构
• 选择结构—条件语句
• 多分支结构—多分支语句
• 循环结构—循环语句

3.4 VBA程序的两种定义形式：子程序与函数

• 子程序（即宏）的定义与调用方式
• 函数的定义与调用方式
• 的程序调试与注释语句的使用
• 程序加密

教学总时数：6，其中实验课时2课时

参考资料：

1. 《Excel与数据处理》（第5版），杜茂康　等编著，电子工业出版社，2014,第12章

2. 《基于excel的高级金融建模》，杰克逊，斯汤顿 著，朱世武，何剑波 译，中国人民大学出版社，2006， 第三章，第四章

3.《Excel 2010 VBA编程与实践》，罗刚君 章兰新 黄朝阳　编著，电子工业出版社，2010

4. 《excel VBA快速上手之宝典》，电子书，2006

•  
• 2课时）：

练习要求：对于作业中的关键公式和语句在程序中通过注释说明作用，在程序开头通过注释说明此宏（sub）或者函数(function)的作用

1. 1）编写一个宏，通过消息框msgbox（）,显示工作表“练习1“中B5单元格中的值

2）将1到5的平方根写入工作表练习1的A1:A5中，使用for—next循环来实现。

2.编写宏，产生数组练习：

1) 编写两个宏，一个宏为thefifths(n)，产生包含n个元素的整型数据数组A，数组元素分别赋值为1,2,…,n的5倍数值，如thefifths（4）产生的数组元素取值为:5,10,15,20，在该宏中通过调用已经编写好的arrayprint程序将结果输出到“立即窗口“,调用方式为call arrayprint(A)

另一个宏“练习1_1“用于调用宏thefifths(n)。

2)编写一个宏multiplytwo（n），产生包含n个元素的数组A，A中第一个元素取值为1，第2个元素取值为2，如果n>2,从第3个元素开始，每个元素的取值为其前面两个元素的乘积。

如multiplytwo（7）的结果为：1,2,3,4,8,32,256.同样在该宏中通过调用已经编写好的arrayprint程序将结果输出到“立即窗口“,调用方式为call arrayprint(A)。

同时编写另外一个宏“练习1_2“用于调用宏multiplytwo(n)

3）.编写一个宏colnum(n,m)，产生包含n*m个元素的二维数组A,A中每个元素取值为其对应列下标值的2倍。

例如:colnum(3,5)产生的矩阵为：

2 4 6 8 10

2 4 6 8 10

2 4 6 8 10

同样在该宏中通过调用已经编写好的arrayprint程序将结果输出到“立即窗口“,调用方式为call arrayprint(A)。

同时编写另外一个宏“练习1_3“用于调用宏colnum (n)

3.已有某单位的职工工资表内容表格”练习3_1”,编写一个宏，计算表中的工资总额、税率、所得税、实发工资，其中，工资总额的计算方法如下：

工资总额=加班工资+房屋补贴+其他津贴+生计津贴+理智津贴-餐券

税率的计算方法如下：

税率	对应工资总额
0.05	800<=工资总额<1000
0.07	1000<=工资总额<1500
0.1	1500<=工资总额<2000
0.15	工资总额>=2000

所得税和实发工资的计算方法如下：

     所得税=工资总额* 税率

     实发工资=工资总额-餐券

工资表中的职工人数用while… wend循环或者Do…loop循环

 

4.设计一个excel表格，用于生成指定平均收益和波动率的股价走势。

要求：平均收益、波动率、初始股价、模拟时间（年）可以在单元格中更改。

编写一个宏”股价数据”，从单元格中读取平均收益、波动率、初始股价、模拟时间（年）的数据，结合vba的函数rnd()和excel的内置函数normsinv（）生成标准正态分布随机数，并利用以下递推公式，模拟一支股票规定时间内的价格路径100条。每条路径为一列数据，在第一行写明标题表明该列是第几条路径。

 

 

 

 

 

 

 

参考初值：S0=20, mu=0.05, sigma=0.32, T=1 （年），从单元格获得数据可参考”欧美式期权的二叉树”

5.编写两个函数(function) BS_cal和BS_put，分别用于对于给定的参数S,r,X,σ，T计算下面两个函数的取值.

 

 

 

 

 

设定参数的初值如下:S= 50, σ=0.2, r=0.1,T=1,X=60

1)在表练习5中要求的单元格中分别调用BS_call, BS_put，计算函数取值

2)在表练习5-1中，将对应的参数值更改列中的取值，其他数值和初值保持不变，计算BS_call, BS_put的取值。

提示：标准正态分布的累积分布函数可以在VBA中调用excel内置函数normsdist().

 

• 自定义宏的操作

【教学目的和要求】

1.掌握通过宏对单元格、单元格区域以及工作表甚至多个工作簿的操作

2.能够通过修改图表操作的宏，解决自己通过宏进行图表操作的需求

3.了解如何通过宏引用excel内置函数

4.了解如何通过宏引用其他数据分析工具如规划求解、回归等；

【主要内容】

• 宏对单元格的引用、编辑和修改
• 宏对单元格区域的引用、编辑和修改
• 宏对多个工作表的操作
• 宏对多个工作簿的操作
• 自动宏
• 宏对图表的操作
• 编制宏练习

教学总时数：6，其中实验课时2课时

参考资料：

1. 《Excel与数据处理》（第5版），杜茂康　等编著，电子工业出版社，2014,第12章

2. 《基于excel的高级金融建模》，杰克逊，斯汤顿 著，朱世武，何剑波 译，中国人民大学出版社，2006， 第三章，第四章

3.《Excel 2010 VBA编程与实践》，罗刚君 章兰新 黄朝阳　编著，电子工业出版社，2010

4. 《excel VBA快速上手之宝典》，电子书，2006

作业与练习：

     课堂实验（2课时）：

1.完成疑难24中的要求：将成绩在85分以上的学生和不及格的学生分别以不同的颜色表示，在新的表中。

2.在excel的单元格中，以51作为间隔，使用RGB函数设置不同颜色的单元格背景，依次查看RGB各种取值下的颜色，每个单元格的取值为RGB函数三个参数的取值，如：如果单元格A1中使用的背景颜色为RGB(255,0,0)，则A1的取值为”RGB(255,0,0)”。

注：以51为间隔目的是为了降低空间，如red参数的取值为0,51,102,….,255; 绿色和黄色也是类似。这样共需要5^3个单元格显示不同的颜色

3.更改分离男女教师.xlsm背后的宏文件。

1）不使用auto_open，改为自动搜索教师档案中已经使用的单元格的方式（usedrange），确定检索区域，将男女教师信息和讲师信息分别放在不同的工作表中。

2）在关闭工作簿时，同时删除男教师、女教师、讲师三个工作表

4.解读DG201 applications.xls中模块module中的自动宏的作用。

5. 为欧美式期权二叉树定价.xlsm文件的VBA程序加注释，解读数据处理过程。

6.将例4.29中生成的工作簿合并成一个工作簿，合并工作簿中每个表的名字为原工作簿的名字，合并后的工作簿的名字为“合并工作簿“

     小组作业

1.通过更改”获取股票历史数据.xls”中的代码，实现多个股票数据的查询。要求：

针对在选定的单元格区域中输入的股票代码，查询每个股票代码2000年1月1日以来的历史交易数据，并将每个股票的数据放在单独的工作表中，工作表的名字为股票的代码，如00001的数据存储的工作表名字为“stk000001”。

例：查询如下股票代码的"日期", "开盘价", "最高价", "成交价", "最低价", "成交量"

000001   000002      000004      000005      000006      000007      000008      000009      600459      600460      600461      600462       600463      600466      600467      600468      600469      600470      600472      600475      600476      600477      600478       600479      600480      600481      600482      600483      600485      600486      600487      600488      600489      600490       600491      600493      600495      600496      600497      600498      600499      600500      600501      600502      600503       600505

 

 

3.尝试将问题1中的工作簿拆分成多个工作簿，每个工作簿中存储一个股票的数据，工作簿的名字为相应工作表的名字，存放在同一文件夹中。

4. a) 对每只股票的收盘价计算ln(p(t))-ln(p(t-1)), 其中pt表示当前单元格中的收盘价，pt-1表示前一单元格中的收盘价,所有股票收益率的计算结果放在一个工作簿stock_return中，每个股票是一个工作表，包含两列数据，一列是日期，一列是收益率。

b)将stock_return中所有股票代码相同日期的收益率汇总在同一工作表中，每个股票的收益率起一个名字，如000001的收益率名字为return_000001，在该工作表中包含日期以及每个股票的收益率信息，每个变量为一列，通过VBA宏实现。（注意收益率日期要匹配）

5.实现各种排序算法，并检验各种算法的速度：冒泡排序、选择排序、插入排序、快速排序、希尔排序。给定一列数，使用各种算法对其进行排列，在表格中为各种算法增加运行按钮。

6. 接课件中的例4.30,查询“4.30 文件夹遍历“中所有的excel文件（包括文件夹中的excel文件）,将所有文件合并在同一工作簿中，每个文件的A1单元格的注释为该文件的文件路径，合并后的工作簿名字为”子文件夹遍历.xlsm“

 

第五章 自定义函数的操作

【教学目的和要求】

1.掌握如何定义和调用局部函数或者全局函数

2.掌握如何为函数添加帮助信息；

3.能够在excel中灵活使用excel内置函数和用户自定义函数

4.通过函数操作能够查询金融数据库中的数据；

【主要内容】

•   自定义函数举例
• 为自定义函数添加帮助信息

5.3金融数据库的数据查询与下载

5.2.1与Bloomberg的连接

   5.2.2 万德数据库的查询函数应用

   5.2.3 ROV期权估值软件的函数应用

   5.4 自定义函数练习

教学时数：4，其中实验课时2课时

参考资料：

1. 《Excel与数据处理》（第5版），杜茂康　等编著，电子工业出版社，2014,第12章

2. 《基于excel的高级金融建模》，杰克逊，斯汤顿 著，朱世武，何剑波 译，中国人民大学出版社，2006，第四章

3.《Excel 2010 VBA编程与实践》，罗刚君 章兰新 黄朝阳　编著，电子工业出版社，2010

4. 《excel VBA快速上手之宝典》，电子书，2006

作业与练习：

课堂实验（2课时）：

1.编写一个函数，用于产生泊松分布的随机数。要求不能使用excel的内置函数，但可以使用VBA的函数rnd()。

2.将欧美式二叉树模型.xlsm的程序改写成函数调用形式，删除二叉树的展现部分，只返回欧式期权和美式期权估值的结果，并为函数添加帮助信息，包括参数的帮助信息

函数帮助信息为:option_CRR(S0,X,r,sigma,T,N,q)此函数使用CRR二叉树模型计算欧式期权和美式期权的价值，包括不知红利和支付连续红利的情况。

参数帮助信息为：S0为标的资产价格；,X为执行价格；,r为无风险利率,1>r>0；sigma为波动率,1>sigma>0；T为到期期限，以年为单位；N为二叉树的步数,正整数；q为标的资产的红利率，0<q<1;

3.非线性方程求解问题，编写计算隐含波动率的VBA函数。设σ满足如下方程：

N(.)表示标准正态分布的累积分布函数

编写一个函数，其中C,S,X,r,T为输入参数，计算σ的取值，0<σ<=1。

 

第六章excel查询与外部数据交换

【教学目的和要求】

1.了解MsQuery的功能及用法

2.掌握MSQuery单表和多表查询方法

3.掌握通过excel的ADO功能使用SQL查询语言的方式

4.掌握基本的SQL查询语言

【主要内容】

6.1 Excel与数据库概述

6.2 Microsoft Query与外部数据库访问

6.2.1外部数据库与Microsoft Query概述

6.2.2建立或指定Microsoft Query的数据源

6.2.3操作Microsoft Query

6.2.4在Microsoft Query中进行多表查询

6.2.5条件查询

6.3 Excel与其他文件类型的相互转换

6.3.1 Excel和Web页之间的转换

6.3.2 Excel与Access数据交换

6.3.3 Excel与文本文件的数据转换

    6.4  SQL查询工作表

        6.4.1 Excel SQL查询基础

6.4.2 SQL条件查询和多表数据查询          

6.4.3使用SQL进行分组统计查询

6.4.4用SQL从重复数据中提取不重复数据

   6.5 excel数据查询与数据转换练习

教学总时数：4，其中实验课时2课时

参考资料：

1.《excel与数据处理》（第5版），杜茂康等，机械工业出版社，2014.（10章，11章）

2. 《Excel 2010 SQL完全应用》，电子工业出版社，2013

3. 《Excel+SQL server数据库管理技术详解》（3章，7章），电子版书籍，2008

4. 一个学习SQL的网址：http://www.w3school.com.cn/sql

作业与练习：

   课堂实验（2课时）：

    1.利用Microsoft Query的菜单操作完成对锐思数据库的数据库信息查询和数据表数据查询

2.利用Microsoft Query的SQL编程窗口修改SQL程序，完成进一步的数据处理

3. 利用VBA完成与锐思数据库的服务器连接

4. 利用VBA调用SQL语言完成对锐思数据库的数据查询

5. 利用VBA调用SQL语言完成对本工作簿、其他工作簿的数据查询和处理

小组作业

1.基本操作：

1）对锐思金融研究数据库中，yrret中所有股票分股票代码（stkcd）计算其年平均收益率，并按照年平均收益率进行升序排列，查询结果汇总在工作表中,sheet名为”股票年平均收益率”；

2).从锐思金融研究数据库的最新股票信息表lstkinfo中查询在2013年之前上市的所有沪深两市的股票信息，存储在工作表中，工作表的名字为“股票信息”；

3).利用锐思金融研究数据库中股票综合数据表格DRESSTK_2011_  中的前收盘价（prevclpr）和收盘价(clpr)信息（提示：如果收盘价）=round(0.1*前收盘价，2)，认为是涨停），查询所有以涨停收盘的股票，按照股票代码和日期进行排序,将结果输出到工作表中，保留股票代码，股票名称，日期，前收盘价，收盘价；

4).分股票代码汇总第3题中各股票涨停的天数。

2.

1）利用MS query，从“小组任务2数据.xlsx”中导入数据，利用两个sheet表a产品和b产品的数据两两组合，产生81中组合，即a产品的一条记录与b产品的一条记录相组合，组合后的数据有4列，分别命名为a 产品价格a产品成本b产品价格b产品成本，通过更改MS query的SQL查询程序实现，结果存储在另一个sheet中，命名为“MSquery 组合结果”。

2）利用VBA实现1）中相同的操作，结果存储在一个新的sheet中，sheet的名字为“VBA组合结果”

3.选择锐思数据库中lstkinfo表中的股票代码（stkcd），分别统计股票代码尾号为0,1,2,…,9的个数，结果存储在工作簿“股票代码尾号统计”中。

4.对锐思数据库中qttn_2011_中的数据，分股票代码（stkcd）和年份（需要利用日期date提取年份）统计每个股票每一年的交易天数（即每一年的交易记录），并选取每年交易天数不少于200天的股票，输出相应的股票代码和每一年的交易天数到一个工作簿中，保留四列：股票代码（stkcd），股票名称（lstkname）,年份（year），交易天数（tradedays），工作簿名字为“足够活跃度的股票”。可以分多步实现。

使用msquery或者VBA 都可以

 

第七章excel VBA应用综合实践

【教学目的和要求】

    通过报告和研讨，检查学生对课程内容的掌握程度和应用情况。

教学内容：

 为每组同学布置excel VBA数据实践任务，在最后两讲进行数据处理和编程报告，并通过excel的多种展现方式展现任务完成情况。任务包括两类：

任务1、解读已有的excel实验文件，完善其结果的人性化展示；

任务2、独立通过excel VBA完成一项数据处理相关的任务，并设计应用程序的人性化界面。

教学总时数：4，均为实验课时