excel全能应用(大全)知识分享.ppt

lnuyin Excel高级应用 辽宁大学计算中心殷慧文 目录 第一章Excel基本操作 第二章公式与函数 第三章数据透视表与数据透视图 第四章Excel统计分析功能 第五章Excel与数据库应用 第六章Excel综合应用案例 1 1Excel数据录入 1 2格式化工作表 1 3数据有效性设定与规则 第一章Excel基本操作 1 4Excel安全设置与数据保护 1 5冻结窗口 1 6隐藏行与列 第一章Excel基本操作 Excel2003是一个操作简单 使用方便 功能强大的电子表格软件 它主要用于对数据的处理 统计分析与计算 了解Excel2003的基本知识是学好该软件的前提 下面将详细介绍Excel2003的基础知识 一 启动Excel2003启动Excel2003有多种方法 下面简单介绍常用的3种方法 第一章Excel基本操作 1 在Windows的桌面上双击快捷图标 即可启动Excel2003 2 选择 开始 所有程序 MicrosoftOffice MicrosoftOfficeExcel2003 命令 即可启动Excel2003 3 选择 开始 运行 命令 即可弹出 运行 对话框 在 打开 文本框中输入 Excel exe 单击 确定 按钮 即可启动Excel2003应用程序 二 启动后的Excel窗口界面如下图 2003版 2010版 快速启动栏 功能区 工作薄 xlsx 第一章Excel基本操作 1 工作簿Excel2003工作簿是计算和存储数据的文件 每一个工作簿都由多张工作表组成 用户可以在单个文件中管理各种不同类型的信息 默认情况下 新建一个工作薄名称为Book1 一个工作簿包含3张工作表 分别为Sheet1 Sheet2和Sheet3 2 工作表用户利用工作表可以对数据进行组织和分析 也可以同时在多张工作表中输入或编辑数据 还可以对不同工作表中的数据进行汇总计算 工作表由单元格组成 横向为行 分别以数字命名 如1 2 3 4 65536 纵向为列 分别以字母命名 如A B C D IV 第一章Excel基本操作 3 单元格Excel2003工作簿最基本的核心就是单元格 它也是Excel工作簿的最小组成单位 单元格可以记录简单的字符或数据 单元格是由行号和列号标识的 如A1 B3 D8 F5等 4 工作表标签工作表标签用来标识工作簿中不同的工作表 单击工作表标签 即可迅速切换至相应的工作表中 第一章Excel基本操作 5 名称框名称框位于工具栏的下方 用于显示工作表中光标所在单元格的名称 6 编辑栏编辑栏用于显示活动单元格的数据和公式 7 活动单元格当前处理被选中的状态的单元格 第一章Excel基本操作 三 退出Excel2003退出Excel2003常用以下3种方法 1 选择菜单栏的 文件 退出 菜单命令 2 单击标题栏右侧的 关闭 按钮 3 双击 控制菜单 按钮 1 1Excel数据录入 一 新建工作簿当启动Excel2003时 系统会自动创建一个新的工作簿Book1 xls 并在新建的工作簿中创建3个空的工作表Sheet1 Sheet2和Sheet3 如果要创建新的工作簿 可以采用以下几种方法 1 选择菜单栏 文件 新建 菜单命令 弹出 新建工作簿 任务窗格 在任务窗格的 新建 选区中单击 空白工作簿 超链接 2 单击 常用 工具栏中的 新建 按钮 1 1Excel数据录入 2 一 新建工作簿 3 如果想利用模板创建一个工作簿 可以在 新建工作簿 任务窗格的 新建 选区中单击 本机上的模板 超链接 弹出如图所示的 模板 对话框 打开 电子方案表格 选项卡 在其列表框中选择需要的模板 然后单击 确定 按钮 1 1Excel数据录入 3 二 选中单元格对单元格进行各种编辑操作前 必须先将目标单元格选中 使其成为活动单元格 然后才能进行操作 在Excel中 用户可以使用以下8种方法选中目标单元格 1 用鼠标单击目标单元格 即可将其选中 2 单击某个单元格 按住鼠标左键拖动鼠标到另一个单元格后释放鼠标 即可选中以这两个单元格为对角线的矩形区域 1 1Excel数据录入 4 二 选中单元格 3 按住 Ctrl 键的同时依次单击多个不相邻的单元格或矩形区域 即可选中多个不相邻的单元格或矩形区域 4 单击某个单元格 按住 Shift 键的同时单击另一个单元格 即可选中以这两个单元格为对角线的矩形区域 5 单击工作表左侧的行号标签可以选中某一行 6 单击工作表上方的列号标签可以选中某一列 1 1Excel数据录入 5 二 选中单元格 7 选中某行或某列后按住鼠标左键并进行拖动 即可选中相邻的多行或多列 8 单击工作表左上角的 全选 按钮 或按 Ctrl A 可以选中整个工作表 默认情况下 文字左对齐 数字右对齐 若要把数字按照文字输入 输入时需要在数字前加 单引号 输入分数 在分数前加上前导0及空格 输入日期时 年月日用分隔符 或 输入时间时 要使用 作分隔符 输入当前日期 按 Ctrl 输入当前时间 按 Ctrl Shift 各种类型数据的输入方法 逻辑型数据的值为TURE和FALSE 默认情况下 文字左对齐 数字右对齐 快速输入数据方法在制作电子表格时 通常要在其中输入批量数据 如果一个一个地输入 这将十分麻烦且浪费时间 因此 用户可采取特定的方法来输入大批量的数据 以提高工作效率 各种类型数据的输入方法 2 1 利用填充命令复制数据方法 单击菜单栏 编辑 填充 菜单命令 3 自定义填充序列方法 单击菜单栏 工具 选项 菜单命令 在打开的对话框中选择 自定义序列 选项卡 4 使用序列功能输入数据方法 单击菜单栏 编辑 填充 序列 菜单命令 5 使用 自动更正 简化重复录入文本方法 单击菜单栏 工具 自动更正选项 菜单命令 各种类型数据的输入方法 3 2 利用填充句柄填充数据方法 单击活动单元格右下角的 填充句柄 向下或向右拖动 2010版 例1 1某公司为了进行精确的绩效考核 提高工作效率 需要每天统计A B C D E五个部门的销售业绩和成本情况 并且要明确标注是否完成任务 对于财务部门的会计而言 每天都需要录入五个部门的销售数据和成本数据 并标注出每个部门是否完成任务 这就意味着每天都要做大量重复性的工作 有没有什么办法可以解决这个重复录入的问题呢 1 1Excel数据录入 6 分析 文本数据 部门名称和部门编号数值型数据 销售量和成本日期型数据 考核日期逻辑型数据 是否完成任务 1 1Excel数据录入 7 销售额 成本 2200 从Access等数据库导入数据 从网络导入数据方法 数据导入技巧 设置单元格内容的对齐方式 数字的格式化 表格边框 底纹的设置 1 2格式化工作表 方法 格式 单元格格式 菜单 字符的格式化 例子 工作表编辑完后 用户可以利用Excel提供的各种格式工具栏和排版命令美化工作表 例如设置背景图案 工作表标签颜色 单元格和表格的边框线等内容 条件格式的设置 1 2格式化工作表 2 方法 格式 条件格式 菜单命令 例 工作薄文件 家庭支出明细 的 家庭年度支出 工作表记录了某家庭一年的经济支出情况 为了更清楚地显示该家庭本年度的支出情况 请对超过3000元的支出标注红色背景 1000 3000元之间的支出标注蓝色背景 低于1000元的支出标注绿色背景 例子 2010版 目的 为了减少错误 核查数据的正确性而提供的一种数据检验工具 该工具可以设置单元格数据输入的类型和范围 对错误的输入数据进行警告并且拒绝 可以有效防止出现输入错误 1 3数据有效性设定与规则 1 3数据有效性设定与规则 2 步骤 1 设置数据类型和有效范围为了避免错误类型数据的输入 可以限定单元格中输入数据的类型 取值范围或文本字节长度 如果输入的数据不符合限定条件 Excel会拒绝接受输入 2 设置输入提示信息为了方便使用者快捷准确地输入数据 可以设置一个提示信息 告诉使用者不同编号 应输入的数据类型 范围及注意事项等 可以大大提高数据录入的准确性 1 3数据有效性设定与规则 3 步骤 3 设置出错警告信息如果输入的数据不符合设置的数据类型或超出了有效性范围 可以让Excel显示出特定的警告信息 以提醒输入者 方法 选择 数据 有效性 菜单命令 1 3数据有效性设定与规则 3 例 某大学一学期期末考试之后需要统计学生的成绩 为了方便老师们输入时更加准确快捷 需要对成绩表中的数据输入类型及范围设置相应的有效性 具体要求如下 学号为9位字符 不能有缺失所有学科成绩为0 100之间的数字输入成绩时 显示输入提示信息输入错误时显示出错信息 数据表 输入时的提示信息 输入错误时的警告信息 2010版 1 3数据有效性设定与规则 4 说明 数据有效性的功能可以规定单元格中输入的数据类型和有效范围 防止输入时的错误 但对于已经输入了数据的工作表 可以通过设置单元格的有效性规则 利用数据审核工具 将错误的数据找出并标明 起到查错的作用 数据表 1 3数据有效性设定与规则 5 例 将下面数据表中高等数学 英语 计算机科目的成绩高于100分的数据圈释出来 数据表 1 3数据有效性设定与规则 6 步骤 1 选定要设定有效性范围的单元格区域C3 E12 2 用设定 数据有效性 的方法设置各门课成绩所在数据区域的数据有效性为0 100之间的整数 3 选择菜单命令 工具 公式审核 显示公式审核工具栏 4 在 公式审核 工具栏上单击 圈释无效数据 按钮 1 3数据有效性设定与规则 7 步骤 5 圈释后的效果如下图所示 2010版 1 4Excel安全设置与数据保护 Excel数据表完成之后 通常应根据需要对其进行安全设置 一方面可以保护数据安全 另一方面也便于管理 例如 我们可以为工作薄或工作表设置密码 使有权限的人才能查看 通过锁定单元格 规定哪些区域可以输入数据 哪些区域不能被修改等 Excel的安全设置包括对工作薄的保护 对工作表的数据保护及对工作表中单元格的保护 1 4Excel安全设置与数据保护 2 方法 单击菜单栏 工具 选项 菜单命令 在打开的 选项对话框 中选择 安全性 选项卡 然后设置安全密码 一 对工作薄的保护 2010版 方法一 2010版 方法二 1 4Excel安全设置与数据保护 3 通常打开一个Excel工作薄时 会出现名为 sheet1 sheet2 sheet3 三张工作表 这是Excel默认的设置 我们可以根据需要添加新的工作表或者删除无用工作表 并且可以更改工作表的名称 很多情况下 我们只希望对整个工作薄的某一两个工作表设定为只读或隐藏 这时 任何对于该工作表的操作均无效 二 对工作表的保护 1 4Excel安全设置与数据保护 4 方法 单击菜单栏 工具 保护 保护工作表 菜单命令 在弹出的对话框中直接输入密码 单击 确定 按钮后 会弹出 确认密码 对话框 再次输入密码 然后单击 确定 按钮 完成对工作表的保护 这样 以后每次打开该工作表都需要输入密码才能进入 二 对工作表的保护 若要撤销对工作表的保护 选择菜单命令 工具 保护 撤销工作表保护 方法一 2010版 2010版 方法二 1 4Excel安全设置与数据保护 5 只有在工作表被保护时 锁定单元格或隐藏公式才有效 在一张Excel表中 可以根据需要设置哪些单元格是只读的 哪些是可以修改的 三 对单元格的保护 1 4Excel安全设置与数据保护 6 三 对单元格的保护 例 将 安全设置与数据保护 xls 中 保护单元格 工作表的学号 姓名两列数据设为不可以修改 其余列数据可以修改 1 4Excel安全设置与数据保护 7 三 对单元格的保护 1 选定数据区域C3 E12 单击鼠标右键 在弹出的快捷菜单中 选择 设置单元格格式 2 在打开的 单元格格式 对话框中 选择 保护 选项卡 取消 锁定 复选项 然后单击 确定 按钮 1 4Excel安全设置与数据保护 8 三 对单元格的保护 3 选择 工具 保护 保护工作表 菜单命令 在打开的 保护工作表 对话框中 如图进行设置 设置工作表的保护密码 选定未锁定单元格 1 5冻结窗口与拆分 当工作表中数据量比较大时 需要拖动滚动条才能看到所有的数据 但此时用户也许就会不能看到数据所属的列标题 可以采用通过将数据的列标识冻结 使之保持不动 行冻结方法 单击选择要冻结行的下一行行号 然后单击菜单栏 窗口 冻结窗格 菜单命令 刚在选定行的上方插入一条冻结线 列冻结方法 单击选择要冻结列的下一列列标 然后单击菜单栏 窗口 冻结窗格 菜单命令 刚在选定列的左侧插入一条冻结线 2010版 1 6隐藏行与列 如果工作表中数据量比较大 不需要查看有些行或列 则可以通过隐藏行或列的方式减少对数据的浏览量 行隐藏与取消行隐藏方法 略 列隐藏方法 在要隐藏列的列标上单击鼠标右键 在弹出的快捷菜单中选择 隐藏 将该列隐藏 取消列隐藏方法 将鼠标移向隐藏列的下一列 当光标变为时 单击鼠标右键 在弹出的快捷菜单中选择 取消隐藏 1 7数据筛选 在实际工作中 经常需要从Excel工作表中找出符合一定条件的几行或几列数据 例如在超市中 要迅速在成百上千条的商品销售记录中查找缺货商品 并且及时补充货源 这就要用到Excel的数据筛选功能 Excel提供两种不同的筛选方式 自动筛选和高级筛选 在进行高级筛选前 必须明确数据需要满足的条件 即建立条件区域 1 7数据筛选 2 将公式作为筛选条件 例 一家电信公司的客户经理负责开展针对客户的优惠营销活动 在海量的数据集里需要对客户进行精确定位 如此才能找到最有营销价值的客户 请按照下列要求帮他迅速找出符合条件的营销对象 所在城市为北京 上海或杭州用户类别为神州行话费总额大于或等于用户数据表中的平均话费 1 7数据筛选 3 解析 从题目的三个要求来看 条件 为或条件 条件 为与条件 条件 需要应用公式进行计算 因此在建立数据区域的时候就要充分考虑到满足这三个条件 因为条件 和条件 的筛选在建立条件区域时需要有与数据表的标题行同样的字段 并且在相应的字段下方输入条件 而计算条件的列标题不能与数据表中的列标题相同 这与条件 和条件 建立条件区域的要求正好相反 所以 可以考虑分两步筛选的方法来实现目标 先筛选条件 再综合考虑条件 和条件 average D3 D9 D3 C 12 第一步设置筛选条件 第二步高级筛选 注意 用公式作为筛选条件 筛选条件的列标题不能与原数据区域列标题相同 设置筛选原数据区域 条件区域 筛选结果区域 第三步再次设置筛选条件 第四步再次高级筛选 这一次的筛选结果作为下一次筛选的原数据区域 最终的筛选结果 说明 要将最终筛选结果复制到sheet2工作表中 应在第四步操作时 首先选择sheet2工作表 然后再选择 数据 筛选 高级筛选 菜单命令 第二章公式与函数 在使用Excel处理数据的时候离不开公式和函数 公式的使用使得对工作表的处理更加简单 而函数则是公式使用过程中的一种内部工具 它可以被看做是比较复杂的公式 2 1公式 2 2函数 2 2 2函数的种类 第二章公式与函数 2 2 4日期与时间函数 2 2 6文本函数 2 2 7查找与引用函数 2 2 5逻辑函数 2 2 1函数的输入 2 2 3数学与三角函数 在Excel中 公式是对工作表中的数据进行计算和操作的等式 公式中可以包含运算符 单元格引用 区域名称 常量或者函数和括号等 使用公式可以对工作表中的数据进行各种运算 2 1公式 为D1定义名称 平均值 公式的组成 以等号 开头的式子 式子中可以包括以下元素 2 1公式 值或常量 通过键盘直接输入到单元格的数字或文本 单元格引用 通过使用一些固定的格式引用单元格中的数据 公式的组成 以等号 开头的式子 式子中可以包括以下元素 2 1公式 区域名称 直接引用为该区域定义的名称 如为单元格A1定义名称为 日工资 工作表函数 包括函数及它们的参数 公式的组成 以等号 开头的式子 式子中可以包括以下元素 2 1公式 运算符 连接公式中的基本元素并完成特定计算的符号 算术运算符该类运算符能够完成基本的数学运算 2 1公式 比较运算符该类运算符能够比较两个或者多个数字 文本串 单元格内容或者函数结果的大小关系 2 1公式 文本运算符该类运算符能够将两个文本连接起来合并成一个文本 2 1公式 引用运算符该类运算符能够将两个单元格或者区域结合起来生成一个联合引用 2 1公式 在使用公式和函数计算时 往往需要引用单元格中的数据 通过引用 可以在公式中使用工作表中不同部分的数据 还可以引用不同工作薄中的单元格数据 引用根据样式可以分为A1引用样式和R1C1引用样式 根据地址可以分为相对引用 绝对引用 混合引用及三维引用 2 1公式 A1引用样式此样式通过引用字母和数字标识 在工作表中查找其纵横相交的单元格 它是最常用的引用样式 2 1公式 R1C1引用样式此样式通过使用 R 加行数字和 C 加列数字来确定单元格的位置 2 1公式 R1C1引用样式在没有特殊说明的情况下 系统默认使用的是A1引用样式 如果要使用R1C1引用样式 则需要进行设置 具体步骤如下 2 1公式 选择菜单栏 工具 选项 菜单命令 打开 选项 对话框 在该对话框中选择 常规 选项卡 按下图所示进行设置 2010版 相对引用指公式所在的单元格与公式中引用的单元格之间的相对位置 也就是如果公式所在的单元格的位置发生了变化 那么引用的单元格的位置也相应地发生变化 所以当公式被复制或移动后 系统将自动地调整移动后函数的相对引用 使得能够引用相对于当前函数所在单元格位置的其它单元格 2 1公式 表现形式 直接用单元格所处位置的列标和行号引用单元格特点 如果单元格所处的位置改变 引用也随之改变 步骤1 在单元格E3输入公式 B3 C3 D3 然后按回车键 步骤2 计算其他同学的总分不需要再次输入公式 选定E3单元格 将鼠标放在该单元格右下角的填充句柄上 当鼠标变成十形状时按住鼠标左键拖动到E8单元格 这时公式中参数的引用为相对引用 即被引用的单元格区域会自动改变 E3 E4 B3 C3 D3 B C 4 4 4 D 绝对引用指被引用的单元格与公式所在的单元格的位置是绝对 即不管公式被复制到什么位置 公式中所引用的还是原来单元格区域的数据 2 1公式 表现形式 A1引用样式在单元格的行号和列标前分别加上 如 A 1 B 2 R1C1引用样式在 R 的后面加行号 在 C 的后面加列号 如R3C4特点 如果单元格所处的位置改变 引用不发生改变 例 已知各科成绩和各科成绩在综合评定中所占的比例 要求综合评定成绩 步骤1 在单元格F3输入公式 B3 B 11 C3 C 11 D3 D 11 然后按回车键 步骤2 计算其他同学的综合评定成绩不需要再次输入公式 选定F3单元格 将鼠标放在该单元格右下角的填充句柄上 当鼠标变成十形状时按住鼠标左键拖动到F8单元格 F3 B3 B 11 C3 C 11 D3 D 11 F4 B4 B 11 C4 C 11 D4 D 11 混合引用介于相对引用和绝对引用之间的引用 也就是说引用单元格的行和列之中一个是相对的 一个是绝对的 具有绝对列和相对行 或是绝对行和相对列两种形式 2 1公式 表现形式 绝对列相对行 A1相对列绝对行A 1特点 如果单元格所处的位置改变 引用可能发生改变 例 已知各科成绩 要求分别求出所有同学各门课平均成绩 步骤1 在单元格B10输入公式 B 3 B 4 B 5 B 6 B 7 B 8 6然后按回车键 步骤2 计算其他各门课的平均成绩不需要再次输入公式 选定B10单元格 将鼠标放在该单元格右下角的填充句柄上 当鼠标变成十形状时按住鼠标左键向右拖动到D10单元格 B10 B 3 B 4 B 5 B 6 B 7 B 8 6 C10 C 3 C 4 C 5 C 6 C 7 C 8 6 A3 A 1 B2 B3 1 B B 2 三维引用指引用非当前工作表中的单元格 即其他工作表中的单元格 这些工作表可以与当前工作表处于同一个工作薄中 也可以与当前工作表处于不同的工作薄 2 1公式 表现形式 工作薄名称 工作表名称 单元格名称如 Book1 sheet2 A3 工资管理 B5 若公式与其引用的单元格位于同一个工作薄中 可以省略工作薄的名称 函数实际上是Excel预先定义好的公式 每个函数都有相同的结构形式 函数名 参数1 参数2 参数可以是数字 文本 表达式 单元格或引用区域 数组 区域名称 逻辑值或者是其他的函数 2 2函数 使用函数可以大大地简化公式 并能实现很多复杂的计算 函数的功能主要如下 可以使一些复杂的公式更易于使用 如 sum A1 A100 使复杂数学表达式的输入简化 计算复杂的表达式 可以使人们在应用中获得一些其他方式无法获得的数据 如INFO 可以返回有关当前操作环境的信息 如内存 操作系统等 函数总是作为一个单元格公式的组成部分来使用 在使用函数时要注意以下几点 2 2 1函数的输入 函数名可以是小写也可以是大写 Excel总是会将函数名转换成大写 一定要把函数参数放在括号中 多个参数要用逗号分开 逗号的后面可加空格 有的函数不带参数 但函数名后必须带括号 每个函数都有一个语法行 在函数的后面跟上一个左括号时会弹出一个提示框 显示该函数的语法 要在工作表中使用函数必须先输入函数 函数的输入有两种常用的方法 手工输入和函数向导 2 2 1函数的输入 1 手工输入函数不用进行过多的操作 但需要用户对输入的函数非常熟悉 包括函数名称和各种对应的参数及类型 如在E3单元格中输入 sum B3 D3 然后按回车键确认输入 要在工作表中使用函数必须先输入函数 函数的输入有种常用的方法 手工输入和函数向导 2 2 1函数的输入 2 使用函数向导输入对于一些比较复杂的函数或者参数较多的函数 一般使用函数向导来输入 利用函数向导输入函数可以确保输入名称的正确性 同时还可以提供正确的参数次序及参数个数 有函数向导输入函数的具体步骤如下 2 2 1函数的输入 选要要输入函数的单元格E3 单击菜单栏 插入 函数 菜单命令或者单击编辑栏左侧的 插入函数 按钮 弹出 插入函数 对话框 有函数向导输入函数的具体步骤如下 2 2 1函数的输入 在 选择函数 列表框中选择所需要的函数 这里选择SUM后在 选择函数 列表框的下面会出现该函数的参数及对函数的简要说明 然后单击 确定 命令按钮 有函数向导输入函数的具体步骤如下 2 2 1函数的输入 在打开的 函数参数 对话框中输入该函数的参数 如果引用的是单元格或者单元格区域 也可以直接单击后面的 折叠 按钮 直接选择工作表中的区域 然后单击按钮返回到 函数参数 对话框 设置完成后单击 确定 按钮 即可在选择的单元格中显示出该函数的结果 Excel中的函数大致可以分为11类 分别是数学和三角函数 日期与时间函数 逻辑函数 查找与引用函数 文本函数 信息函数 数据库函数 工程函数 统计函数 财务函数以及用户自定义函数 2 2 2函数的种类 数学和三角函数可以处理简单的计算 如对数字取整的函数INT 计算单元区域中的数值总和的函数SUM或者解决一些复杂计算的函数 日期与时间函数可以在公式中分析和处理日期值和时间值 例如可以使用Now函数返回当天的日期和时间 2 2 2函数的种类 2 逻辑函数可以进行真假值判断或者进行复合检验 例如可以使用IF函数确定条件为真还是假 并由此近回不同的数值 文本函数可以在公式中处理字符串 例如改变大小写或者确定字符串的长度等操作 查找与引用函数当需要在数据清单或表格中查找特定的数值 或者需要查找某一个单元格的引用时 可以使用查询和引用工作表函数 例如需要在表格中查找与第一列中的值相匹配的数值 可以使用VLOOKUP工作表函数 2 2 2函数的种类 3 信息函数可以使用信息工作表函数确定存储在单元格中的数据的类型 信息函数包含一组称为IS的工作表函数 在单元格满足条件时返回TRUE 数据库函数当需要分析数据清单中的数值是否符合特定条件时 可以使用数据库工作表函数 例如 在一个包含销售信息的数据清单中 可以计算出所有销售数值大于1000且小于2500的行或记录的总数 工程函数主要用于工程分析 这类函数中的大多数可以分为4种类型 计算机塞尔值或修正后的贝赛尔值的函数 对复数进行处理的函数 在不同的数字系统之间进行数值转换的函数 2 2 2函数的种类 4 统计函数用于对数据区域进行统计分析 例如 统计工作函数可以提供由一组给定值绘制出的直线的相关信息 如直线的斜率和Y值截距 或构成直线的实际点的数值 财务函数进行一般的财务运算 如确定贷款的支付额 投资的未来值或净现值 以及债卷或息票的价值等 用户自定义函数如果要在公式或计算中使用特别复杂的计算 而工作表函数又无法满足时用户可以通过VBA创建自定义函数 2 2 3数学与三角函数 计算绝对值函数ABS格式 ABS number 含义 返回参数number的绝对值 number可以是数值 也可以是单元格引用 例 某公司测试一台机器分割木材的精确程度 3根木材用于试验切割并测量数据 在测量切割的尺寸时机器切割的实际长度可能与要求长度有误差 要求下表给出差异的大小 1 在单元格D3中输入函数 ABS C3 B3 然后按回车键 2 拖动D3单元格填充句柄到D5单元格 2 2 3数学与三角函数 向下取整函数INT格式 INT number 含义 将参数number向下舍入取整到最接近的整数 B2 INT B2 2 2 3数学与三角函数 例 假设某些商品的价格如下表所示 现在只有5000元 如果购买以下商品 请计算出能购买商品的最大数量 1 在单元格E3中输入函数 INT D3 C3 然后按回车键确认输入 2 拖动单元格E3的填充句柄到E6单元格 2 2 3数学与三角函数 取余 取模 函数MOD格式 MOD number divisor 含义 返回两数相除的余数 结果的正负号与除数相同 MOD 12 5 1 求MOD 12 5 的值为2 2 用除数 5 2 3 3 3的符号与除数相同 所以MOD 12 5 最终结果为了 3 说明 MOD函数常被用来判断一个数是否能被另一个数整除 2 2 3数学与三角函数 计算乘幂函数POWER格式 POWER number power 含义 返回底数number的power次幂 2 2 3数学与三角函数 计算所有参数的乘积函数PRODUCT格式 PRODUCT number1 number2 含义 返回所有以参数形式给出的数字相乘的乘积 例 假设某企业2009年度6月份的销售数量和销售单价如下表所示 用函数计算其销售额 2 2 3数学与三角函数 返回随机数 抽样调查函数RAND 自学 格式 RAND 含义 返回大于等于0及小于1的均匀分布随机数 每次计算都将返回一个新值 说明 若要生成a与b之间的随机实数 可以使用公式RAND b a a如果要使用函数RAND生成一个随机数 并且使之不随单元格的计算而改变 可以在编辑栏中输入 RAND 保持编辑状态 然后按F9键 将公式永久性地改为随机数 2 2 3数学与三角函数 例 假设在全班50名同学中以随机方式抽出20名进行计算机水平的普查 1 在单元格C1中输入函数开始号码为1 2 在单元格E1中输入结束号码为50 3 在单元格B2中输入公式 1 RAND 49 然后按回车键确认输入 4 利用自动填充功能将此公式填充式单元格F4中 2 2 3数学与三角函数 四舍五入函数ROUND格式 ROUND number num digits 含义 返回按num digits位数对number进行四舍五入后的值 2 2 3数学与三角函数 向下舍入函数ROUNDDOWN格式 ROUNDDDOWN number num digits 含义 返回按num digits位数对number进行向下舍入后的值 2 2 3数学与三角函数 例 假设出租车的计费标准是 每公里单价是3元 不足一公里按一公里计价 以后每公里跳一次表 按下表输入不同的公里数 然后计算其费用 2 2 3数学与三角函数 向上舍入函数ROUNDUP格式 ROUNDUP number num digits 含义 返回按num digits位数对number进行向上舍入后的值 例 网吧计费系统规定 不满一个单位按照一个单位计算 现假设每30分钟计价0 5元 请计算如下表所示的上网时间所花费的费用 计算上网天数 在单元格D3中输入如下公式 C3 B3 然后按回车键确认输入 2 拖动单元格D3的填充句柄到单元格D6 3 选中单元格区域D3 D6 设置单元格的格式为数值 保留两位小数 4 计算上网分钟数 在单元格E3中输入公式 D3 24 60 然后按回车键确认输入 5 拖动单元格E3的填充句柄到单元格E6 如图所示 6 计算上网费用 在单元格G3中输入如下公式 ROUNDUP E3 30 0 F3 7 拖动单元格G3的填充句柄到单元格G6 如图所示 2 2 3数学与三角函数 根据指定条件对单元格求和函数SUMIF格式 SUMIF range criteria sum range 含义 在range单元格区域内对符合criteria条件的sum range单元格区域求和 range 表示用于条件判断的单元格区域criteria 表示由数字和逻辑表达式等组成的判定条件sum range 表示要求和的单元格 区域或引用 2 2 3数学与三角函数 例 假设某企业生产的空调价格如下表所示 利用SUNIF函数计算销售单价超过3500元的所有空调的销售金额和 根据销售单价和销售数量计算出销售金额 在单元格E3中输入如下公式 C3 D3 然后按回车键确认输入 2 拖动单元格E3的填充句柄到单元格E8 3 在单元格B9中输入 销售单价超过3500元的销售金额之和 然后在元格E9中输入如下函数 SUMIF C3 C8 3500 E3 E8 按回车键确认输入 2 2 3数学与三角函数 SUMPRODUCT格式 SUMPRODUCT array1 array2 array3 含义 在给定的几组数组中将数组间对应的元素相乘 并返回乘积之和 2 2 3数学与三角函数 向上舍入 取最接近的数函数CEILING 自学 格式 CEILING number significance 含义 将参数number向上舍入 沿绝对值增大的方向 为最接近的指定基数significance的整倍数 说明 如果参数number为非数值型 该函数将返回错误值 VALUE 无论参数number为正数还是负数 该函数都按照远离0的方向向上舍入 如果参数number已经是significance整倍数 则该函数返回值为number的值如果参数number和significance的符号不同 该函数将返回错误值 NUM 2 2 3数学与三角函数 2 2 3数学与三角函数 例 假设某企业在2009年1 6月使用电表的度数如下表所示 利用CEILING函数计算实际交纳的电费数 并且将其舍入为整数 1 在单元格E3中输入公式 C3 D3 然后按回车键 2 拖动E3单元格填充句柄到E8单元格 3 在单元格F3中输入函数 CEILING E3 1 然后按回车键确认输入 4 拖动F3单元格填充句柄到F8单元格 2 2 3数学与三角函数 向上取整 取最接近的偶数函数EVEN 自学 格式 EVEN number 含义 可以返回沿绝对值增大方向取整后最接近的偶数 说明 如果参数number为非数值型 该函数将返回错误值 VALUE 无论参数number为正数还是负数 该函数都按照远离0的方向向上舍入 如果参数number是恰好是偶数 则函数返回值为number值 2 2 3数学与三角函数 向上取整 取最接近的奇数函数ODD 自学 格式 ODD number 含义 可以返回沿绝对值增大方向取整后最接近的奇数 说明 如果参数number为非数值型 该函数将返回错误值 VALUE 无论参数number为正数还是负数 该函数都按照远离0的方向向上舍入 如果参数number是恰好是奇数 则函数返回值为number值 2 2 3数学与三角函数 向下舍入 取最接近的数函数FLOOR 自学 格式 FLOOR number significance 含义 将参数number向下舍入 沿绝对值减小的方向 为最接近的指定基数significance的整倍数 说明 如果参数number为非数值型 该函数将返回错误值 VALUE 无论参数number为正数还是负数 该函数都按照接近0的方向向下舍入 如果参数number已经是significance的倍数 则该函数返回值为number的值如果参数number和significance的符号不同 该函数将返回错误值 NUM 2 2 3数学与三角函数 例 下表为某超市的购物小票 用函数计算购买商品的总金额及实付金额 其中实付金额为舍入到角 1 在单元格D7中输入函数 SUN D3 D6 然后按回车键 2 在单元格D8中输入函数 FLOOR D7 0 1 然后按回车键确认输入 使用Excel可以制作各种数据表格 如财务报表 销售报表以及员工时间表等 在制作这些表格的时候经常会包含日期和时间 而在有些包含日期和时间的表格中则需要对此做一些计算 如计算年龄时就要将现在的日期与出生日期相减 2 2 4日期和时间函数 DATE函数格式 DATE year month day 含义 返回代表特定日期格式 如果单元格的格式设为 常规 则返回日期的序列号 说明 在MicrosoftExcelforWindows系统中 1900年1月1日的序列号为1 2 2 4日期与时间函数 计算日期之差函数DATEDIF格式 DATEDIF date1 date2 interval 含义 计算结束日期date2与起始日期date1的差值 返回值类型为interval的值 说明 参数date1与date2可以是表示日期的系列数 带引用的日期 也可以是一个函数 date1一定要早于date2参数interval可以包含y m d ym yd md等6个值 其中y表示两个日期相差的整年数 m表示两个日期相差的整月数 d表示两个日期相差的天数 ym表示忽略年份的差别而只计算整月数的差 yd表示忽略年份的差别而计算差的天数 md表示忽略年份和月份的差别而只计算差的天数 下表为DATEDIF函数应用举例 下表为DATEDIF函数应用举例 2 2 4日期与时间函数 返回小时数函数HOUR格式 HOUR serial number 含义 返回时间值的小时数 其返回值是一个介于0 12 00A M 到23 11 00P M 之间的整数 例 假设某公司员工出入公司的时间如下表所示 用HOUR函数计算员工的上班小时数 2 2 4日期与时间函数 返回分钟数函数MINUTE格式 MINUTE serial number 含义 返回时间值的分钟数 它是一个介于0到59之间的整数 2 2 4日期与时间函数 返回秒数函数SECOND格式 SECOND serial number 含义 返回时间值的秒数 返回的值为0到59之间的整数 参数可以是带引号的文本字符串 十进制数以及其他公式或函数的结果 例 话吧计算系统统计每个电话的时长 输入打电话的起始时间和结束时间 如下表所示 在单元格D3中输入公式 HOUR C3 B3 然后按回车键确认输入 2 拖动单元格D3的填充句柄到单元格D6 3 在单元格E3中输入公式 MINUTE C3 B3 然后按回车键确认输入 4 拖动单元格E3的填充句柄到单元格E6 5 在单元格F3中输入公式 SECOND C3 B3 然后按回车键确认输入 6 拖动单元格F3的填充句柄到单元格F6 2 2 4日期与时间函数 返回年份函数YEAR格式 YEAR serial number 含义 返回日期中的年份 该值是1900年后的整数返回月份数函数MONTH格式 MONTH serial number 含义 返回日期中的月份 该值是介于1 12之间的整数返回日函数DAY格式 DAY serial number 含义 返回日期中的日 该值是介于1 31之间的整数 2 2 4日期与时间函数 返回当前日期时间函数NOW格式 NOW 含义 返回当前系统日期和时间 2 2 4日期与时间函数 返回当前日期函数TODAY格式 TODAY 含义 返回当前系统日期 返回时间函数TIME格式 TIME hour minute scond 含义 返回特定时间 在处理工作表中的数据时经常需要转换代码或者文本 或者计算字符串的长度 返回特定的字符等 这时就要用到文本函数 文本函数是针对文本串进行一系列相关操作的一类函数 此函数用于处理文本串 改变大小写以及连接文字串等 2 2 6文本函数 大小写转换函数格式 LOWER text UPPER text 含义 将文本字符串转换成小写或大写文本字符串 2 2 6文本函数 首字母大写函数PROPER格式 PROPER text 含义 可以将文本字符串的英文首字母以及任何非英文字母字符之后的首字母转换成大写 而将其余的字母转换成小写 2 2 6文本函数 合并字符串函数CONCATENATE格式 CONCATENATE text1 text2 含义 可以将若干个字符串合并为一个字符串 其功能与 运算符相同 2 2 6文本函数 判断字符串是否相同函数EXACT格式 EXACT text1 text2 含义 比较两个字符串是否相同 如果相同则返回TRUE 否则返回FALSE说明 比较时区分大小写 但忽略格式上的差异 2 2 6文本函数 查找字符串函数FINDFINDB格式 FIND find text within text start num FINDB find text within text start num 含义 查找字符串within text内的字符串find text 从within text的start num处开始返回find text的起始编号 二个函数的主要区别在于 FIND函数以字符数为单位返回起始位置编号 FINDB以字节数为单位返回起始位置编号 2 2 6文本函数 取左串函数LEFTLEFTB格式 LEFT text num chars LEFTB text num bytes 含义 从字符串左侧开始取一个或几个字符或字节数所组成的字符串 取右串函数RIGHTRIGHTB格式 RIGHT text num chars RIGHTB text num bytes 含义 从字符串右侧开始取一个或几个字符或字节数所组成的字符串 2 2 6文本函数 返回特定的字符函数MIDMIDB格式 MID text start num num chars MIDB text start num num bytes 含义 返回文本字符串中从指定位置start num开始的num chars num bytes 个字符 字节 2 2 6文本函数 返回字符串长度函数LENLENB格式 LEN text LENB text 含义 返回文本字符串中的字符数或字节数 2 2 6文本函数 替换文本函数REPLACEREPLACEB格式 REPLACE old text start num num chars new text REPLACEB old text start num num bytes new text 含义 将文本字符串old text从起始位置start num开始的num chars num bytes 个字符 字节 替换为new text 2 2 6文本函数 替换文本函数SUBSTITUTE格式 SUBSTITUTE text old text new text instance num 含义 将文本字符串text中第instance num次出现的字符old text用new text替换说明 如果需要在某一个文本字符串中替换指定的文本 则使用SUBSTITUTE函数 如果需要在某一个文本字符串中替换指定位置处的任意文本 则使用REPLACE函数如果指定了instance num 则只有满足要求的old text被替换 否则将用new text替换text中出现的所有的old text 2 2 6文本函数 复制文本函数REPT格式 REPT text number times 含义 将按照给定的次数number times重复显示文本text说明 使用该函数可以按照给定的次数重复显示文本 也可以通过该函数不断地重复显示一个文本字符串来地单元格进行填充 查找与引用函数在Excel表格中也是一类经常使用的函数 使用此类函数可以在数据清单或者数据表中查找特定的数值 或者查找某一个单元格引用的一类函数 这对于节省工作人员的工作时间 提高工作效率有着十分重要的作用 2 2 7查找与引用函数 2 2 7查找与引用函数 COLUMN函数格式 COLUMN reference 含义 用来返回给定引用的列标说明 若省略refence 则假定是对函数COLUMN所在单元格的引用 COLUMNS函数格式 COLUMNS array 含义 用来返回数组或引用的列数说明 参数array为需要得到其列数的数组 数组公式或对单元格区域的引用 2 2 7查找与引用函数 ROW函数格式 ROW reference 含义 用来返回给定引用的行号说明 若省略refence 则假定是对函数ROW所在单元格的引用 ROWS函数格式 ROWS array 含义 用来返回数组或引用的行数说明 参数array为需要得到其行数的数组 数组公式或对单元格区域的引用 2 2 7查找与引用函数 INDEX函数格式 INDEX reference row num column num area num 含义 用于返回指定的行与列交叉处的单元格引用的值说明 reference表示引用的区域 如果引用为不连续的区域 则必须用括号括起来 area num表示选择引用中的一个区域 对单列的引用 可使用INDEX reference row num 参数row num column num和area num必须指向reference中的单元格 2 2 7查找与引用函数 MATCH函数格式 MATCH lookup value lookup array match type 含义 用于返回在指定方式下与指定数值匹配的数组中元素的相应位置说明 参数lookup value表示需要在数据表中查找的数值 lookup array连续的单元格区域 必须是一行或一列 match type值为 1 0或者1 若match type值为1 该函数查找小于或者等于lookup value的最大数值 此时lookup array必须按升序排列 若match type值为0 该函数查找等于lookup value的第一个数值 此时lookup array可以按任意顺序排列 若match type值为 1 该函数查找大于或者等于lookup value的最小数值 此时lookup array必须按降序排列 例 已知各地之间相隔的距离如图所示 计算A