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

常用数学软件 之 Excel 张洪波 主讲,Excel 部分技巧介绍,讲授内容 引言 公式 函数 自定义函数简介 VBA介绍 自定义函数和宏编程,一、引言,Microsoft Excel是微软公司的办公自动化软件Microsoft office的组件之一，包含大量的公式函数，是一款试算表软件，它可以进行各种数据的计算处理和统计分析，广泛地应用于管理、统计财经、金融等众多领域。,Excel文件也称为工作簿，其扩展名为.xls(.xlsx)，一个工作簿可以有若干工作表（sheet）。 Excel工作表由行和列组成多个单元格。 Excel 2003单元格由65536行和256列共组成16777216个单元格。行使用数字165536表示；列使用A 、 、Z、AA 、 、AZ 、BA 、 、 IU、IV表示。 据资料表明：Excel 2007增加到了1048576行16384列,二、公式,公式以等号开头，使用运算符将各种数据、函数等等连接起来的表达式,1、公式创建,在工作表中选定的单元格中： 在编辑栏或单元格中输入“” 。 输入表达式（常量、运算符、单元格引用，函数等等）。 按【Enter】键或单击 显示结果。,=A2 & “:“ & “总分“ & SUM(B2:C2),2、单元格引用,每一个单元格使用行和列进行应用： 相对引用（E2）:在复制或填充公式时，相对引用会自动调整，保持相对位置不变。 绝对引用（ $E$2 ）:在复制或填充公式时， 绝对引用将保持不变，将与原公式引用相同的单元格。 混合引用:$E2 E$2 技巧：在输入或编辑公式时，输入引用单元格后，选择按【F4】键，将会循环改变应用形式。,不同引用在公式复制时的变化,例：九九乘法表,11部分： =B1 & “ & A2 & “=“ & B1*A2 28部分： =C1 & “ & A9 & “=“ & C1*A9 34部分： =D1 & “ & A5 & “=“ & D1*A5,B$1 & “ & $A2& “=“ & B$1*$A2,如何改进？ 有些单元格不应显示 需要有选择结构！,单元格其他引用,公式除引用同一sheet的单元格外，还可引用： 同一文件中的其他sheet的单元格， 引用语法格式例子：sheet2!A1:B4 其它Excel工作簿文件的sheet的单元格（外部引用） 文件打开时引用格式为： Budget.xlsx sheet2!A1:B4 文件未打开时引用格式为(含完整路径)： D:WORKBudget.xlsxAnnual!C10:C25,使用公式部分出错信息,三、函数,函数是一些预定义的公式，方便用户使用,函数可以应用到在公式的表达式中。 函数分为两类：一类是Excel自带的，一类是用户自己用VBA编写的。 要使用Excel函数，可以在单元格表达式中直接输入函数名，也可以选择编辑菜单上的“插入函数”按钮或快捷键 进行选择。,日期与时间函数(DATE、DATEVALUE、DAY等） 数学与三角函数(EXP、FLOOR、INT等） 逻辑函数(AND、IF、OR等） 查找与引用函数(LOOKUP 、ROW、ROWS等） 数据库函数(DAVERAGE 、DCOUNT、DCOUNTA等） 文本函数(LEFT、LEN、LOWER、MID等） 统计函数(COUNT、COUNTA、COUNTBLANK、COUNTIF等） 财务函数（ACCRINT等） 工程函数（BESSELJ 返回 Bessel 函数 Jn(x)） 信息函数（CELL单元格信息,INFO当前环境的信息） 用户自定函数,1.Excel函数分类,SUM(number1, number2, ) 功能：返回参数的总和。 SUMIF(range,criteria,sum_range) 功能：按给定条件对若干单元格求和 公式: =SUMIF(A2:A5,“160000“,B2:B5) AVERAGE(number1, number2, ) 功能：返回所有参数的算术平均值 COUNT ( valuel1, valuel2, ) 功能：求各数值参数和包含数值的单元格个数 Rank (number, ref,order) 功能：返回某数字在一列数字中的大小排位。 IF(logical_test,value_if_true,value_if_false) 功能：根据逻辑计算的真假值，返回不同结果 。,2.常用函数介绍,D2单元格平均分公式： =AVERAGE(B2:C2),E2单元格排名公式： =RANK(D2,$D$2:$D$20),进行排名,保持顺序不改变,改进后九九乘法表,再重行复制到各行列可得九九乘法表,例:如何根据成绩的判断等级： 优、良、及格、不及格,=IF(C2=90,“优“,IF(C2=80,“良“,IF(C2=60,“及格“,“不及格“),=IF(C2=90,“优“, ),IF(C2=80,“良“, ),IF(C2=60,“及格“,“不及格“),得出公式并复制到相关单元格：,函数的嵌套用法,例:如何统计各班的总成绩和平均成绩,A班人数计算公式 =COUNTIF($B$2:$B$25, “=A班“) A班总成绩计算公式 =SUMIF($B$2:$B$25,“=A班“,$C$2:$C$25) A班平均成绩计算公式 =ROUND(G7/F7,2) B班和C班进行修改（注意仅仅复制不行）,例：AND函数应用举例,在评价列D2中输入以下公式并复制： =IF(AND(B2=85,C2=85),“优等“,“一般“),要求：在评价栏目中显示：如果两科都大于85分显示优等，否者显示一般。,例：课程考试质量分析要求,通过对课程平时成绩、期末卷面分析得出： 每位同学的总评成绩； 卷面成绩：及格率、总平均分、各个分数段人数及所占比例 总评成绩：及格率、总平均分、各个分数段人数及所占比例 按题目类型得出该题最高分、最低分、题目难度系数（分值/该题平均得分）。,课程考试质量分析Excel表,为使不及格分数显示红色，设置单元格格式为： 红色=00;红色60#;#,四、自定义函数简介,在使用Excel公式编辑中，计算公式可能太复杂或者嵌套层次可能太多，这样会很麻烦。 用自定义函数来完成一些复杂的功能。 在Excel中完成自定义功能的模块有两种： function过程（自定义函数）和sub过程(宏)，都是使用VBA语言实现。 前者使用只要编辑好，就和其他函数一样使用。,自定义函数编辑,选择菜单【工具】/【宏】/【Visual Basic编辑器】(或按快捷键“Alt+F11”)打开进入Visual Basic编辑窗口 在Visual Basic编辑窗口中，选择菜单【插入】/【模块】命令，插入新模块 选择模块的右边“代码窗口”，输入代码或者选择【插入】/【过程】/【函数】 关闭窗口，自定义函数完成,输入自定义函数： 成绩(a),在保存自定义函数或宏时可能出现提示警告：,选择Excel菜单【工具】/【选项】/【安全性】,取消选择 （不打勾）,成绩转换函数例子,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 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内部函数没有的计算功能函数。 Excel中VBA“宏”相当于一个批处理文件，把我们的对Excel的操作过程换成命令文本来执行。我们可以用录制新宏来记录操作过程。,VBA的语法基本和Visual Basic一样 运算符 赋值运算符：= 数学运算符： +、-、*、/、 & (连接符) 逻辑运算符:Not、And、Or 关系运算符：= 、=、= 数据类型 如String、Integer、Double，对象类型如range等 变量声明、定义、引用： Dim x As Range Set x = Range(“B2:C3”) x.Cells(1, 1).Value = 12 Cells(1, 1).Value = 23 Cells(5, 5).Formula = “=A1+B2”,在B2写入数据 在A1写入数据 在E5写入公式,单行选择语句 If . Then . Else . 多行块选择语句 If 条件1 Then 语句组1 ElseIf条件2 Then 语句组2 Else 语句组3 End If 循环语句 For i=1 To 2 Step 1 . Next i,六、自定义函数和宏编程,自定义函数和宏建立和编辑过程是一样的，只是格式和用法不同。 自定义函数格式 Public Function 我的函数(r As Range) (包含有语句 我的函数=) End Function 宏定义形式 Public Sub 我的宏() End Sub 函数在单元格或宏中公式中使用；而宏不能直接使用，宏使用需要“运行”: 菜单【工具】/【宏】/【宏】选择运行的宏名。,自定义函数和宏VBA代码建立,选择菜单【工具】/【宏】/【Visual Basic编辑器】(或按快捷键“Alt+F11”)打开进入Visual Basic编辑窗口 在Visual Basic编辑窗口中，选择菜单【插入】/【模块】命令，插入新模块 选择模块的右边“代码窗口”，输入代码或者选择【插入】/【过程】/【函数】 关闭窗口，自定义函数完成,注：也可以直接通过文件导出和导入。,1、用宏实现九九乘法表,Public Sub 我的九九乘法表() For i = 1 To 9 For 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 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).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.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