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

常用数学软件之Excel张洪波主讲 Excel部分技巧介绍 讲授内容引言公式函数自定义函数简介VBA介绍自定义函数和宏编程 一 引言 MicrosoftExcel是微软公司的办公自动化软件Microsoftoffice的组件之一 包含大量的公式函数 是一款试算表软件 它可以进行各种数据的计算处理和统计分析 广泛地应用于管理 统计财经 金融等众多领域 Excel文件也称为工作簿 其扩展名为 xls xlsx 一个工作簿可以有若干工作表 sheet Excel工作表由行和列组成多个单元格 Excel2003单元格由65536行和256列共组成16777216个单元格 行使用数字1 65536表示 列使用A Z AA AZ BA IU IV表示 据资料表明 Excel2007增加到了1048576行16384列 二 公式 公式以等号开头 使用运算符将各种数据 函数等等连接起来的表达式 1 公式创建 在工作表中选定的单元格中 在编辑栏或单元格中输入 输入表达式 常量 运算符 单元格引用 函数等等 按 Enter 键或单击显示结果 A2 总分 SUM B2 C2 2 单元格引用 每一个单元格使用行和列进行应用 相对引用 E2 在复制或填充公式时 相对引用会自动调整 保持相对位置不变 绝对引用 E 2 在复制或填充公式时 绝对引用将保持不变 将与原公式引用相同的单元格 混合引用 E2E 2技巧 在输入或编辑公式时 输入引用单元格后 选择按 F4 键 将会循环改变应用形式 不同引用在公式复制时的变化 例 九九乘法表 1 1部分 B1 A2 B1 A22 8部分 C1 A9 C1 A93 4部分 D1 A5 D1 A5 B 1 A2 B 1 A2 如何改进 有些单元格不应显示 需要有选择结构 单元格其他引用 公式除引用同一sheet的单元格外 还可引用 同一文件中的其他sheet的单元格 引用语法格式例子 sheet2 A1 B4其它Excel工作簿文件的sheet的单元格 外部引用 文件打开时引用格式为 Budget xlsx sheet2 A1 B4 文件未打开时引用格式为 含完整路径 D WORK Budget xlsx Annual 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表 为使不及格分数显示红色 设置单元格格式为 红色 0 0 红色 60 四 自定义函数简介 在使用Excel公式编辑中 计算公式可能太复杂或者嵌套层次可能太多 这样会很麻烦 用自定义函数来完成一些复杂的功能 在Excel中完成自定义功能的模块有两种 function过程 自定义函数 和sub过程 宏 都是使用VBA语言实现 前者使用只要编辑好 就和其他函数一样使用 自定义函数编辑 选择菜单 工具 宏 VisualBasic编辑器 或按快捷键 Alt F11 打开进入VisualBasic编辑窗口在VisualBasic编辑窗口中 选择菜单 插入 模块 命令 插入新模块选择模块的右边 代码窗口 输入代码或者选择 插入 过程 函数 关闭窗口 自定义函数完成 输入自定义函数 成绩 a 在保存自定义函数或宏时可能出现提示警告 选择Excel菜单 工具 选项 安全性 取消选择 不打勾 成绩转换函数例子 Function成绩转换 xAsDouble Ifx 100Thenr 数据错误 ElseIfx 90Thenr 优秀 ElseIfx 80Thenr 良好 ElseIfx 70Thenr 中等 ElseIfx 60Thenr 及格 ElseIfx 0Thenr 不及格 Elser EndIf成绩转换 rEndFunction PublicFunction求和 xAsRange DimsasDoubles 0Fori 1Tox Rows CountForj 1Tox Columns Countt x Cells i j IfIsNumeric t Thens s tNextjNexti求和 所求和 sEndFunction 单元格求和函数例子 五 VBA介绍 VBA的全称是VisualBasicforApplication 它是微软通用应用程序脚本编程语言 VBA可以看作是VisualBasic的一个子集 在微软的Office组件中都包含VBA 其VBA的原理是相通的 Excel中VBA 自定义函数 的目的是定义excel内部函数没有的计算功能函数 Excel中VBA 宏 相当于一个批处理文件 把我们的对Excel的操作过程换成命令文本来执行 我们可以用录制新宏来记录操作过程 VBA的语法基本和VisualBasic一样运算符赋值运算符 数学运算符 连接符 逻辑运算符 Not And Or关系运算符 数据类型如String Integer Double 对象类型如range等变量声明 定义 引用 DimxAsRangeSetx Range B2 C3 x Cells 1 1 Value 12Cells 1 1 Value 23Cells 5 5 Formula A1 B2 在B2写入数据 在A1写入数据 在E5写入公式 单行选择语句If Then Else 多行块选择语句If条件1Then语句组1ElseIf条件2Then语句组2Else语句组3EndIf循环语句Fori 1To2 Step1 Nexti 六 自定义函数和宏编程 自定义函数和宏建立和编辑过程是一样的 只是格式和用法不同 自定义函数格式PublicFunction我的函数 rAsRange 包含有语句我的函数 EndFunction宏定义形式PublicSub我的宏 EndSub函数在单元格或宏中公式中使用 而宏不能直接使用 宏使用需要 运行 菜单 工具 宏 宏 选择运行的宏名 自定义函数和宏VBA代码建立 选择菜单 工具 宏 VisualBasic编辑器 或按快捷键 Alt F11 打开进入VisualBasic编辑窗口在VisualBasic编辑窗口中 选择菜单 插入 模块 命令 插入新模块选择模块的右边 代码窗口 输入代码或者选择 插入 过程 函数 关闭窗口 自定义函数完成 注 也可以直接通过文件导出和导入 1 用宏实现九九乘法表 PublicSub我的九九乘法表 Fori 1To9Forj 1ToiCells i j i j i jNextjNextiEndSub 选择菜单 工具 宏 VisualBasic编辑器 建立一个宏 过程 我的九九乘法表编写以下代码 选择菜单 工具 宏 宏 运行宏 我的九九乘法表 2 用宏实现对所选单元格数据求和 PublicSub所选单元格求和 Dimocell DimssAsDoubless 0ForEachocellInSelectiont ocell ValueIfIsNumeric t Thenss ss tNextocellMsgBoxssEndSub 选择菜单 工具 宏 VisualBasic编辑器 建立一个宏 并编写以下代码 3 用宏实现对所选单元格数据求和 另解 PublicSub选择单元格求和 Dim总和AsDouble开始行 Selection Rows Row 行数 Selection Rows Count开始列 Selection Columns Column列数 Selection Columns Count总和 0Fori 1To行数Forj 1To列数t Cells 开始行 i 开始列 j ValueIfIsNumeric t Then总和 总和 tNextjNextiMsgBox总和EndSub 4 用宏实现对所选单元格数据字母大小写转换 PublicSub转换所选单元格为大写字母 DimocellForEachocellInSelectionocell Value UCase ocell Value NextocellEndSubPublicSub转换所选单元格为小写字母 DimocellForEachocellInSelectionocell Value LCase ocell Value NextocellEndSub 5 成绩转换函数 Function成绩转换 xAsDouble Ifx 100Thenr 数据错误 ElseIfx 90Thenr 优秀 ElseIfx 80Thenr 良好 ElseIfx 70Thenr 中等 ElseIfx 60Thenr 及格 ElseIfx 0Thenr 不及格 Elser EndIf成绩转换 rEndFunction 6 录制宏 学习宏 可以查阅资料阅读别人的代码 也可以使用 录制新宏 方式来获得代码来学习 选择菜单 工具 宏 录制新宏 可查阅相应网络资料 7 宏及自定义函数代码复制与恢复 如果在一个Excel文件中编辑好VBA代码 保存时要注意在菜单 工具 选项 下的 安全性 标签里取消 保存时从文件属性中删除个人信息 一般编辑的VBA代码只能在本文件备份恢复VBA代码可以在Excel自带的VisualBasic编辑器中使用菜单 文件 导出文件 以及 文件 导入文件 来完成 也可以使用加载宏方式完成 查阅网络相应资料 七 练习 略