excel常用函数及实用技巧.ppt

EXCEL常用函数及实用技巧 主要内容 Excel函数用法 Excel实用小技巧 Excel基础常识 1 常用术语 工作簿 单元格 工作表 单元格区域 xls sheet A1 列号行号 A1 D13 对角上两个单元格 常用的数据类型 文本 数值 日期 货币等 注 各种操作均需区别对待每类对象 2 相对地址和绝对地址 单元格的列标和行号又称为地址 在列标和行号前加入 符号便构成了绝对地址A1 R1C1 A1 1 A A 1在地址输入时 相对地址和绝对地址F4键进行循环转换Excel中公式的相对地址 在该公式自身的位置发生变化 或在其引用对象的位置发生变化时 会自动进行相应的调整 3 单元格的选取 1 连续单元格的选取 选取相邻的行 列 shift 不相邻的单元格选取 选取不相邻的行 列 ctrl 4 工作表的重命名 复制 移动和隐藏 工作表的重命名 复制 移动和隐藏1 重命名工作表2 工作表在本工作薄复制或移动到新工作薄或其他工作薄3 工作表可以隐藏和显现 格式 工作表 隐藏or取消隐藏 位置 在选定工作表标签鼠标右击 5 保护工作表 薄 区域 位置 工具 保护 保护工作簿 1 不能移动删除工作页2 不能增加新工作页3 现有工作页可以修改 若此页没设保护 保护工作表 1 当前工作表只读 2 默认保护全部区域 3 每个工作表可设不同密码 密码可为空 二 Excel常用函数 函数及公式概念介绍 常用函数表达式 函数使用的关键点 事半功倍的好工具 函数 何为函数 Excel函数即是预先定义 执行计算 分析等处理数据任务的特殊公式 简单说 用机器帮我们逻辑 数值等方面的计算统计 如果您遇到了麻烦赶紧找它帮忙 函数格式说明 举例 SUM A1 B 公式开始 函数名 闻其名 见其意 参数 区域 常用函数介绍 1 求和函数 SUM SUMIF 2 查找函数 VLOOKUP HLOOKUP 3 其他函数 Average Max Min Countif Subtotal 1 常用的求和函数1 Sum 进行基本数值求和 语法 sum number1 number2 2 Sumif 根据条件求和 语法 sumif range criteria sum range SUM用途 返回某一单元格区域中所有数字之和 语法 SUM number1 number2 参数 Number1 number2 为1到N个需要求和的数值 只显示公式 无计算结果 SUMIF 条件求和 用途 根据指定条件对若干单元格 区域或引用求和 语法 SUMIF range criteria sum range 参数 Range为用于条件判断的单元格区域 Criteria是由数字 逻辑表达式等组成的判定条件 Sum range为需要求和的单元格 区域或引用 2 常用的查找函数 Vlookup Hlookup函数 VLOOKUP 用于在数据表的首列查找指定的数值 并返回数据表当前行中指定列处的值 Hlookup用于在表格或数值数组的首行查找指定的数值 并由此返回表格或数组当前行中指定列处的数值当比较值位于数据表的首行 并且要查找下面给定行中的数据时 请使用Hlookup当比较值位于数据表的最左列 并且要查找右面给定列中的数据时 请使用Vlookup 8 Vlookup Hlookup函数 使用方法 HLOOKUP lookup value table array row index num rang lookup VLOOKUP lookup value table array col index num rang lookup 所要查找的值 用于查找数据的区域 所要查找的数据必须与该区域首行 或最左列 的数据进行比较 返回对应该行序号的单元格 返回对应该列序号的单元格 FALSE 精确匹配 或TRUE 近似匹配 3 其他函数1 Average 求平均数 注意不包括空白和字符AVG AVERAGE Number1 Number2 Number3 2 Max 查找一组数的最大值MAX MAX Number1 Number2 Number3 3 Min 查找一组数的最小值MIN MIN Number1 Number2 Number3 常用函数快捷方式 在excel表格的右下角有一个常用函数快捷方式的区域 如同所示 可以右键点击该区域 使用六种常用函数这六种常用函数 分别是 平均值 AVERAGE 计数 COUNTA 计数值 COUNT 最大值 MAX 最小值 MIN 求和 SUM COUNTIF COUNTIF函数功能 统计符合条件要求的单元格的数量例 在B5单元格中输入 countif B2 B4 20 可以求出在B2到B4这三个单元格中大于20的数字有几个 SUBTOTAL函数 SUBTOTAL函数 为了分别统计各个专业的人数 可以在D1单元格中输入 SUBTOTAL 3 D3 D30 然后利用 数据 筛选 自动筛选 功能即可 SUBTOTAL函数 用途 对筛选列表中的可见单元格进行运算语法及功能SUBTOTAL function num ref1 ref2 参数 Function num为1到11之间的自然数 用来指定分类汇总计算使用的函数 所引用区域 如何取出单元格中的部分字符 1 Left 从左边开始截取指定数目的字符 语法 left text num chars 参数 Text是包含要提取字符的文本字符串 Num chars指定要由LEFT所提取的字符数 2 Right 从右边开始截取指定数目的字符 语法 right text num chars 用法举例 left ad246g 3 ad2 RIGHT ad246g 3 46g 3 Mid 从文本指定位置开始截取指定数目的字符语法 mid text start num num chars 参数 Text是包含要提取字符的文本字符串 Start num是文本中要提取的第一个字符的位置 文本中第一个字符的start num为1 以此类推 Num chars指定希望MID从文本中返回字符的个数 用法举例 mid 351429198205090303 7 8 19820509 函数使用关键点 明确达到的目标 明确各表之间的关联性使得表格自动化 将复杂的逻辑关系拆分为若干简单步骤 明确文本和数字的区别 公式中级联符号要求半角状态录入 自己编录公式 切记 开头 函数运用灵活性强 同时也使用一出现问题 必须核查结果是否正确 学会查看别人的使用方法 编辑栏 告诉你公式的使用细节 千万别忘了 函数的常见错误 EXCEL实用技巧 1 页面显示比例的放大或缩小 按住ctrl 鼠标滚动 在word powerpoint中也可使用 位置 格式 条件格式 2 条件格式 条件格式1 不是宏程序 却是可以智能判断条件的灵活格式 具有醒目提示的作用 方便快捷 2 不同条件执行不同单元格格式 最多3个条件 执行顺序为条件一 二 三 3 条件可以是本单元格数值大小条件 还可以是设置的公式函数 可将功能大大加强 3 数据有效性 位置 数据 有效性 数据有效性1 对输入格式进行限制 限制为日期 数值 序列等格式 2 序列下拉框提示选项 实现快捷输入 3 非限定值为无效数据 警告提醒 4 序列来源可手工输入或引用单元区域 4 如何在EXCEL中快速输入身份证号码 直接输入则显示科学计数法 应该设置成文本格式 或者先输入 即英文状态半角的小单引号 再输入身份证号也可以 5 输入m的立方 例如 m3 选中 3 右击 单元格格式 选中上标 6 如何在excel输入分数 例 如需在表中输入 1 3在单元格中输入 0空格1 3 7 禁止复制隐藏行或列中的数据 如果你复制了包含隐藏列 或行 的一个数据区域 然后把它粘贴到一个新的工作表 那么Excel把隐藏列也粘贴过来了 要想避免这种情况 选定要复制的数据区域 编辑 定位 定位条件 定位条件 可见单元格 或按F5 复制经筛选后的单元格的操作方法同上 8 表头和格式刷 表头斜线步骤1 划直线2 添加文本框3 去掉文本的边框 调整直线 格式刷1 格式刷就是将源单元格的格式属性应用到所选单元格或范围2 格式刷不改变数值 公式 批注等3 格式包括行宽 填充 边框 字体大小颜色等 9 在一个单元格内快速换行 Alt 回车键 即可输入第二行内容 再按 Alt 回车键 输入第三行以此类推 10 取消超级链接 1 如果正在输入E mail地址 在输入完毕后敲回车键 刚才输入的内容会变成蓝色 此时单击智能标记选择 撤消超链接 命令即可 2 如果在工作表中已存在超级链接 右键单击单元格 在快捷菜单中选择 取消超链接 命令即可 大家分享在平时工作中用到的经验 谢谢大家