《常用数学软件之Excel部分.ppt》由会员分享，可在线阅读，更多相关《常用数学软件之Excel部分.ppt（42页珍藏版）》请在三一办公上搜索。
1、常用数学软件之Excel张洪波 主讲,Excel部分技巧介绍,讲授内容引言公式函数自定义函数简介VBA介绍自定义函数和宏编程,一、引言,Microsoft Excel是微软公司的办公自动化软件Microsoft office的组件之一，包含大量的公式函数，是一款试算表软件，它可以进行各种数据的计算处理和统计分析，广泛地应用于管理、统计财经、金融等众多领域。,Excel文件也称为工作簿，其扩展名为.xls(.xlsx)，一个工作簿可以有若干工作表（sheet）。Excel工作表由行和列组成多个单元格。Excel 2003单元格由65536行和256列共组成16777216个单元格。行使用数字16
2、5536表示；列使用A、Z、AA、AZ、BA、IU、IV表示。据资料表明：Excel 2007增加到了1048576行16384列,二、公式,公式以等号开头，使用运算符将各种数据、函数等等连接起来的表达式,1、公式创建,在工作表中选定的单元格中：在编辑栏或单元格中输入“”。输入表达式（常量、运算符、单元格引用，函数等等）。按【Enter】键或单击 显示结果。,=A2&:&总分&SUM(B2:C2),2、单元格引用,每一个单元格使用行和列进行应用：相对引用（E2）:在复制或填充公式时，相对引用会自动调整，保持相对位置不变。绝对引用（$E$2）:在复制或填充公式时，绝对引用将保持不变，将与原公式引
3、用相同的单元格。混合引用:$E2 E$2技巧：在输入或编辑公式时，输入引用单元格后，选择按【F4】键，将会循环改变应用形式。,不同引用在公式复制时的变化,例：九九乘法表,11部分：=B1&A2&=&B1*A228部分：=C1&A9&=&C1*A934部分：=D1&A5&=&D1*A5,B$1&$A2&=&B$1*$A2,如何改进？有些单元格不应显示需要有选择结构！,单元格其他引用,公式除引用同一sheet的单元格外，还可引用：同一文件中的其他sheet的单元格，引用语法格式例子：sheet2!A1:B4其它Excel工作簿文件的sheet的单元格（外部引用）文件打开时引用格式为：Budget.
4、xlsx sheet2!A1:B4文件未打开时引用格式为(含完整路径)：D:WORKBudget.xlsxAnnual!C10:C25,使用公式部分出错信息,三、函数,函数是一些预定义的公式，方便用户使用,函数可以应用到在公式的表达式中。函数分为两类：一类是Excel自带的，一类是用户自己用VBA编写的。要使用Excel函数，可以在单元格表达式中直接输入函数名，也可以选择编辑菜单上的“插入函数”按钮或快捷键 进行选择。,日期与时间函数(DATE、DATEVALUE、DAY等）数学与三角函数(EXP、FLOOR、INT等）逻辑函数(AND、IF、OR等）查找与引用函数(LOOKUP、ROW、RO
5、WS等）数据库函数(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)AV
6、ERAGE(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),进行排名,保持顺序不改变,改进后九九乘法表,再重行复制到各行列可得九九乘法表,例:如何
7、根据成绩的判断等级：优、良、及格、不及格,=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(AN
8、D(B2=85,C2=85),优等,一般),要求：在评价栏目中显示：如果两科都大于85分显示优等，否者显示一般。,例：课程考试质量分析要求,通过对课程平时成绩、期末卷面分析得出：每位同学的总评成绩；卷面成绩：及格率、总平均分、各个分数段人数及所占比例总评成绩：及格率、总平均分、各个分数段人数及所占比例按题目类型得出该题最高分、最低分、题目难度系数（分值/该题平均得分）。,课程考试质量分析Excel表,为使不及格分数显示红色，设置单元格格式为：红色=00;红色60#;#,四、自定义函数简介,在使用Excel公式编辑中，计算公式可能太复杂或者嵌套层次可能太多，这样会很麻烦。用自定义函数来完成一些复
9、杂的功能。在Excel中完成自定义功能的模块有两种：function过程（自定义函数）和sub过程(宏)，都是使用VBA语言实现。前者使用只要编辑好，就和其他函数一样使用。,自定义函数编辑,选择菜单【工具】/【宏】/【Visual Basic编辑器】(或按快捷键“Alt+F11”)打开进入Visual Basic编辑窗口在Visual Basic编辑窗口中，选择菜单【插入】/【模块】命令，插入新模块选择模块的右边“代码窗口”，输入代码或者选择【插入】/【过程】/【函数】关闭窗口，自定义函数完成,输入自定义函数：成绩(a),在保存自定义函数或宏时可能出现提示警告：,选择Excel菜单【工具】/【
10、选项】/【安全性】,取消选择（不打勾）,成绩转换函数例子,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成绩转换=rEnd Function,Public Function 求和(x As Range)Dim s as Doubles=0For i=1 To For j=1 To t=x.Cells(i,j)If I
11、sNumeric(t)Then s=s+tNext jNext i求和=所求和=&sEnd Function,单元格求和函数例子,五、VBA介绍,VBA的全称是Visual Basic for Application，它是微软通用应用程序脚本编程语言.VBA可以看作是Visual Basic的一个子集，在微软的Office组件中都包含VBA，其VBA的原理是相通的。Excel中VBA“自定义函数”的目的是定义excel内部函数没有的计算功能函数。Excel中VBA“宏”相当于一个批处理文件，把我们的对Excel的操作过程换成命令文本来执行。我们可以用录制新宏来记录操作过程。,VBA的语法基本和
12、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 Cells(5,5).Formula=“=A1+B2”,在B2写入数据在A1写入数据在E5写入公式,单行选择语句If.Then.Else.多行块选择语句If 条件1 Then 语句组1ElseIf条件2 Th
13、en语句组2Else语句组3End 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+F1
14、1”)打开进入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 jNext iEnd Sub,选择菜单【工具】/【宏】/【Visual Basic编辑器】建立一个宏（过程）：我的九九乘法表编写以下代码,选择菜单【工具】/【宏】/【宏】运
15、行宏：我的九九乘法表,2、用宏实现对所选单元格数据求和,Public Sub 所选单元格求和()Dim ocell：Dim ss As Doubless=0For Each ocell In Selection t=ocell.Value If IsNumeric(t)Then ss=ss+tNext ocellMsgBox ssEnd Sub,选择菜单【工具】/【宏】/【Visual Basic编辑器】建立一个宏，并编写以下代码：,3、用宏实现对所选单元格数据求和另解,Public Sub 选择单元格求和()Dim 总和 As Double开始行=：行数=开始列=列数=总和=0For i=1
16、 To 行数For j=1 To 列数t=Cells(开始行+i,开始列+j).ValueIf IsNumeric(t)Then 总和=总和+tNext jNext iMsgBox 总和End Sub,4、用宏实现对所选单元格数据字母大小写转换,Public Sub 转换所选单元格为大写字母()Dim ocellFor Each ocell In Selection ocell.Value=UCase(ocell.Value)Next ocellEnd SubPublic Sub 转换所选单元格为小写字母()Dim ocellFor Each ocell In Selection ocell.
17、Value=LCase(ocell.Value)Next ocellEnd 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成绩转换=rEnd Function,6、录制宏,学习宏，可以查阅资料阅读别人的代码，也可以使用【录制新宏】方式来获得代码来学习。选择菜单【工具】/【宏】/【录制新宏】（可查阅相应网络资料）,7、宏及自定义函数代码复制与恢复,如果在一个Excel文件中编辑好VBA代码，保存时要注意在菜单【工具】/【选项】下的【安全性】标签里取消“保存时从文件属性中删除个人信息”。一般编辑的VBA代码只能在本文件备份恢复VBA代码可以在Excel自带的Visual Basic编辑器中使用菜单【文件】/【导出文件】以及【文件】/【导入文件】来完成。也可以使用加载宏方式完成（查阅网络相应资料）,七、练习（略）,
