常用数学软件之Excel部分.ppt

1、常用数学软件 之 Excel 张洪波 主讲,Excel 部分技巧介绍,讲授内容 引言 公式 函数 自定义函数简介 VBA介绍 自定义函数和宏编程,一、引言,Microsoft Excel是微软公司的办公自动化软件Microsoft office的组件之一，包含大量的公式函数，是一款试算表软件，它可以进行各种数据的计算处理和统计分析，广泛地应用于管理、统计财经、金融等众多领域。,Excel文件也称为工作簿，其扩展名为.xls(.xlsx)，一个工作簿可以有若干工作表（sheet）。 Excel工作表由行和列组成多个单元格。 Excel 2003单元格由65536行和256列共组成16777216

2、个单元格。行使用数字165536表示；列使用A 、 、Z、AA 、 、AZ 、BA 、 、 IU、IV表示。 据资料表明：Excel 2007增加到了1048576行16384列,二、公式,公式以等号开头，使用运算符将各种数据、函数等等连接起来的表达式,1、公式创建,在工作表中选定的单元格中： 在编辑栏或单元格中输入“” 。 输入表达式（常量、运算符、单元格引用，函数等等）。 按【Enter】键或单击 显示结果。,=A2 红色60#;#,四、自定义函数简介,在使用Excel公式编辑中，计算公式可能太复杂或者嵌套层次可能太多，这样会很麻烦。 用自定义函数来完成一些复杂的功能。 在Excel中完成

3、自定义功能的模块有两种： function过程（自定义函数）和sub过程(宏)，都是使用VBA语言实现。 前者使用只要编辑好，就和其他函数一样使用。,自定义函数编辑,选择菜单【工具】/【宏】/【Visual Basic编辑器】(或按快捷键“Alt+F11”)打开进入Visual Basic编辑窗口 在Visual Basic编辑窗口中，选择菜单【插入】/【模块】命令，插入新模块 选择模块的右边“代码窗口”，输入代码或者选择【插入】/【过程】/【函数】 关闭窗口，自定义函数完成,输入自定义函数： 成绩(a),在保存自定义函数或宏时可能出现提示警告：,选择Excel菜单【工具】/【选项】/【安全性

4、】,取消选择 （不打勾）,成绩转换函数例子,Function 成绩转换(x As Double) If x 100 Then r = 数据错误 ElseIf x = 90 Then r = 优秀 ElseIf x = 80 Then r = 良好 ElseIf x = 70 Then r = 中等 ElseIf x = 60 Then r = 及格 ElseIf x 0 Then r = 不及格 Else r = End If 成绩转换 = r End Function,Public Function 求和(x As Range) Dim s as Double s = 0 For i = 1

5、 To x.Rows.Count For j = 1 To x.Columns.Count t = x.Cells(i, j) If IsNumeric(t) Then s = s + t Next j Next i 求和 = 所求和= & s End Function,单元格求和函数例子,五、VBA介绍,VBA的全称是Visual Basic for Application，它是微软通用应用程序脚本编程语言. VBA可以看作是Visual Basic的一个子集，在微软的Office组件中都包含VBA，其VBA的原理是相通的。 Excel中VBA “自定义函数”的目的是定义excel内部函数没

6、有的计算功能函数。 Excel中VBA“宏”相当于一个批处理文件，把我们的对Excel的操作过程换成命令文本来执行。我们可以用录制新宏来记录操作过程。,VBA的语法基本和Visual Basic一样 运算符赋值运算符：=数学运算符： +、-、*、/、 & (连接符)逻辑运算符:Not、And、Or关系运算符：= 、=、= 数据类型如String、Integer、Double，对象类型如range等 变量声明、定义、引用：Dim x As RangeSet x = Range(“B2:C3”)x.Cells(1, 1).Value = 12 Cells(1, 1).Value = 23 Cell

7、s(5, 5).Formula = “=A1+B2”,在B2写入数据 在A1写入数据 在E5写入公式,单行选择语句If . Then . Else . 多行块选择语句If 条件1 Then 语句组1ElseIf条件2 Then语句组2Else语句组3End If 循环语句For i=1 To 2 Step 1 .Next i,六、自定义函数和宏编程,自定义函数和宏建立和编辑过程是一样的，只是格式和用法不同。 自定义函数格式Public Function 我的函数(r As Range) (包含有语句 我的函数=)End Function 宏定义形式Public Sub 我的宏() End Su

8、b 函数在单元格或宏中公式中使用；而宏不能直接使用，宏使用需要“运行”:菜单【工具】/【宏】/【宏】选择运行的宏名。,自定义函数和宏VBA代码建立,选择菜单【工具】/【宏】/【Visual Basic编辑器】(或按快捷键“Alt+F11”)打开进入Visual Basic编辑窗口 在Visual Basic编辑窗口中，选择菜单【插入】/【模块】命令，插入新模块 选择模块的右边“代码窗口”，输入代码或者选择【插入】/【过程】/【函数】 关闭窗口，自定义函数完成,注：也可以直接通过文件导出和导入。,1、用宏实现九九乘法表,Public Sub 我的九九乘法表() For i = 1 To 9 Fo

9、r j = 1 To i Cells(i, j) = i & & j & = & i * j Next j Next i End Sub,选择菜单【工具】/【宏】/【Visual Basic编辑器】建立一个宏（过程）：我的九九乘法表 编写以下代码,选择菜单【工具】/【宏】/【宏】运行宏：我的九九乘法表,2、用宏实现对所选单元格数据求和,Public Sub 所选单元格求和() Dim ocell ：Dim ss As Double ss = 0 For Each ocell In Selection t=ocell.Value If IsNumeric(t) Then ss = ss + t

10、Next ocell MsgBox ss End Sub,选择菜单【工具】/【宏】/【Visual Basic编辑器】建立一个宏，并编写以下代码：,3、用宏实现对所选单元格数据求和另解,Public Sub 选择单元格求和() Dim 总和 As Double 开始行=Selection.Rows.Row ：行数=Selection.Rows.Count 开始列=Selection.Columns.Column 列数=Selection.Columns.Count 总和 = 0 For i = 1 To 行数 For j = 1 To 列数 t = Cells(开始行 + i, 开始列 + j

11、).Value If IsNumeric(t) Then 总和 = 总和 + t Next j Next i MsgBox 总和 End Sub,4、用宏实现对所选单元格数据字母大小写转换,Public Sub 转换所选单元格为大写字母() Dim ocell For Each ocell In Selection ocell.Value = UCase(ocell.Value) Next ocell End Sub Public Sub 转换所选单元格为小写字母() Dim ocell For Each ocell In Selection ocell.Value = LCase(ocell

12、.Value) Next ocell End Sub,5、成绩转换函数,Function 成绩转换(x As Double) If x 100 Then r = 数据错误 ElseIf x = 90 Then r = 优秀 ElseIf x = 80 Then r = 良好 ElseIf x = 70 Then r = 中等 ElseIf x = 60 Then r = 及格 ElseIf x 0 Then r = 不及格 Else r = End If 成绩转换 = r End Function,6、录制宏,学习宏，可以查阅资料阅读别人的代码，也可以使用【录制新宏】方式来获得代码来学习。 选择菜单【工具】/【宏】/【录制新宏】（可查阅相应网络资料）,7、宏及自定义函数代码复制与恢