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

《常用数学软件之Excel部分.ppt》由会员分享，可在线阅读，更多相关《常用数学软件之Excel部分.ppt（42页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

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

2、Excel 2003单元格由65536行和256列共组成16777216个单元格。行使用数字165536表示；列使用A、Z、AA、AZ、BA、IU、IV表示。据资料表明：Excel 2007增加到了1048576行16384列 云南师范大学数学学院二、公式公式以等号开头，使用运算符将各种数据、公式以等号开头，使用运算符将各种数据、函数等等连接起来的表达式函数等等连接起来的表达式运算符运算符说明说明：，引用运算符%百分号百分号*/+-/+-乘乘 除除 加加 减减 乘方乘方&文本连接符号文本连接符号=比较运算符比较运算符云南师范大学数学学院1、公式创建在工作表中选定的单元格中：在编辑栏或单元格中输

3、入“”。输入表达式（常量、运算符、单元格引用，函数等等）。按【Enter】键或单击 显示结果。云南师范大学数学学院=A2&:&总分&SUM(B2:C2)云南师范大学数学学院2、单元格引用每一个单元格使用行和列进行应用：相对引用（E2）:在复制或填充公式时，相对引用会自动调整，保持相对位置不变。绝对引用（$E$2）:在复制或填充公式时，绝对引用将保持不变，将与原公式引用相同的单元格。混合引用:$E2 E$2技巧：在输入或编辑公式时，输入引用单元格后，选择按【F4】键，将会循环改变应用形式。云南师范大学数学学院公式单元格公式单元格(A1)(A1)复制到目的单元格复制到目的单元格(C10)(C10)

4、A1=B1+C1A1=B1+C1C10=D10+E10C10=D10+E10A1=$B$1+$C$1A1=$B$1+$C$1C10=$B$1+$C$1C10=$B$1+$C$1A1=$B1+C$1A1=$B1+C$1C10=$B10+E$1C10=$B10+E$1不同引用在公式复制时的变化云南师范大学数学学院例：九九乘法表11部分：=B1&A2&=&B1*A228部分：=C1&A9&=&C1*A934部分：=D1&A5&=&D1*A5B$1&$A2&=&B$1*$A2如何改进？有些单元格不应显示需要有选择结构！云南师范大学数学学院单元格其他引用公式除引用同一sheet的单元格外，还可引用：同一

5、文件中的其他sheet的单元格，引用语法格式例子：sheet2!A1:B4其它Excel工作簿文件的sheet的单元格（外部引用）文件打开时引用格式为：Budget.xlsx sheet2!A1:B4文件未打开时引用格式为(含完整路径)：D:WORKBudget.xlsxAnnual!C10:C25云南师范大学数学学院出错信息说明#DIV/0!公式中出现0作除数，可能是空单元格作除数引起的。#NAME?引用了Excel不能识别的文本#NULL!在不相交的区域中指定了一个交集#NUM!在公式或函数中使用了不适当的数字#REF!引用的单元格不存在,单元格无效#VALUE!错误的参数或运算对象#数值

6、长度超过了单元格列宽使用公式部分出错信息云南师范大学数学学院三、函数函数是一些预定义的公式，方便用户使用,函数可以应用到在公式的表达式中。函数分为两类：一类是Excel自带的，一类是用户自己用VBA编写的。要使用Excel函数，可以在单元格表达式中直接输入函数名，也可以选择编辑菜单上的“插入函数”按钮或快捷键 进行选择。云南师范大学数学学院1.日期与时间函数(DATE、DATEVALUE、DAY等）2.数学与三角函数(EXP、FLOOR、INT等）3.逻辑函数(AND、IF、OR等）4.查找与引用函数(LOOKUP、ROW、ROWS等）5.数据库函数(DAVERAGE、DCOUNT、DCOUN

7、TA等）6.文本函数(LEFT、LEN、LOWER、MID等）7.统计函数(COUNT、COUNTA、COUNTBLANK、COUNTIF等）8.财务函数（ACCRINT等）9.工程函数（BESSELJ 返回 Bessel 函数 Jn(x)）10.信息函数（CELL单元格信息,INFO当前环境的信息）11.用户自定函数1.Excel函数分类云南师范大学数学学院1.SUM(number1,number2,)功能：返回参数的总和。2.SUMIF(range,criteria,sum_range)功能：按给定条件对若干单元格求和公式:=SUMIF(A2:A5,160000,B2:B5)3.AVERA

8、GE(number1,number2,)功能：返回所有参数的算术平均值4.COUNT(valuel1,valuel2,)功能：求各数值参数和包含数值的单元格个数5.Rank(number,ref,order)功能：返回某数字在一列数字中的大小排位。6.IF(logical_test,value_if_true,value_if_false)功能：根据逻辑计算的真假值，返回不同结果。2.常用函数介绍云南师范大学数学学院D2单元格平均分公式：=AVERAGE(B2:C2)E2单元格排名公式：=RANK(D2,$D$2:$D$20)进行排名,保持顺序不改变云南师范大学数学学院改进后九九乘法表=B$1

9、&$A2&=&B$1*$A2单元格公式=IF(B$1=90,优,IF(C2=80,良,IF(C2=60,及格,不及格)=IF(C2=90,优,)IF(C2=80,良,)IF(C2=60,及格,不及格)得出公式并复制到相关单元格：函数的嵌套用法云南师范大学数学学院例:如何统计各班的总成绩和平均成绩1.A班人数计算公式=COUNTIF($B$2:$B$25,=A班)2.A班总成绩计算公式=SUMIF($B$2:$B$25,=A班,$C$2:$C$25)3.A班平均成绩计算公式=ROUND(G7/F7,2)4.B班和C班进行修改（注意仅仅复制不行）云南师范大学数学学院例：AND函数应用举例在评价列D

10、2中输入以下公式并复制：=IF(AND(B2=85,C2=85),优等,一般)要求：在评价栏目中显示：如果两科都大于85分显示优等，否者显示一般。云南师范大学数学学院例：课程考试质量分析要求通过对课程平时成绩、期末卷面分析得出：每位同学的总评成绩；卷面成绩：及格率、总平均分、各个分数段人数及所占比例总评成绩：及格率、总平均分、各个分数段人数及所占比例按题目类型得出该题最高分、最低分、题目难度系数（分值/该题平均得分）。云南师范大学数学学院课程考试质量分析Excel表为使不及格分数显示红色，设置单元格格式为：红色=00;红色 100 Then r=数据错误ElseIf x=90 Then r=优

11、秀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 IsNumeric(t)Then s=s+tNext jNext i求和=所求和=&sEnd Function单元格求和函数例子云南师范大学数学学院五、VBA介绍VBA的全称

12、是Visual Basic for Application，它是微软通用应用程序脚本编程语言.VBA可以看作是Visual Basic的一个子集，在微软的Office组件中都包含VBA，其VBA的原理是相通的。Excel中VBA“自定义函数”的目的是定义excel内部函数没有的计算功能函数。Excel中VBA“宏”相当于一个批处理文件，把我们的对Excel的操作过程换成命令文本来执行。我们可以用录制新宏录制新宏来记录操作过程。云南师范大学数学学院VBA的语法基本和Visual Basic一样1.运算符赋值运算符：=数学运算符：+、-、*、/、&(连接符)逻辑运算符:Not、And、Or关系运算

13、符：=、=、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编辑器中使用菜单【文件】/【导出文件】以及【文件】/【导入文件】来完成。也可以使用加载宏方式完成（查阅网络相应资料）云南师范大学数学学院七、练习（略）