excel函数说明 必看

数据库和列表管理函数 DAVERAGE 返回选定数据库项的平均值 DCOUNT 计算数据库中包含数字的单元格个数 DCOUNTA 计算数据库中非空单元格的个数 DGET 从数据库中提取满足指定条件的单个记录 DPRODUCT 将数据库中满足条件的记录的特定字段中的数值相乘 DSUM 对数据库中满足条件的记录的字段列中的数字求和 GETPIVOTDATA 返回存储于数据透视表中的数据 日期和时间函数 DATE 返回特定日期的序列号 DATEVALUE 将文本格式的日期转换为序列号 DAY 将序列号转换为月份中的日 EDATE 返回在开始日期之前或之后指定月数的日期的序列号 MONTH 将序列号转换为月 NOW 返回当前日期和时间的序列号 TIMEVALUE 将文本格式的时间转换为序列号 TODAY 返回今天日期的序列号 WEEKDAY 将序列号转换为星期几 WEEKNUM 将序列号转换为一年中相应的周数 逻辑函数 IF 指定要执行的逻辑检测 数学和三角函数 CEILING 将数字舍入为最接近的整数 或最接近的有效数字的倍数 ROUND 将数字舍入到指定位数 SUBTOTAL 返回数据库列表或数据库中的分类汇总 SUMIF 按给定条件将指定单元格求和 SUMPRODUCT 返回相对应的数组部分的乘积和 TRUNC 将数字截尾取整 信息函数 CELL 返回有关单元格格式 位置或内容的信息 ISBLANK 如果值为空 则返回 TRUE ISNA 如果值为 N A 错误值 则返回 TRUE 查找和引用函数 ADDRESS 以文本形式返回对工作表中某个单元格的引用 CHOOSE 从值的列表中选择一个值 HLOOKUP 在数组的首行查找并返回指定单元格的值 LOOKUP 在向量或数组中查找值 TRANSPOSE 返回数组的转置 VLOOKUP 在数组第一列中查找 然后在行之间移动以返回单元格的值 统计函数 CORREL 返回两个数据集之间的相关系数 COUNT 计算参数列表中数字的个数 COUNTA 计算参数列表中值的个数 COUNTBLANK 计算区间内的空白单元格个数 COUNTIF 计算满足给定标准的区间内的非空单元格的个数 MODE 返回数据集中出现最多的值 PERCENTRANK 返回数据集中值的百分比排位 文本和数据函数 ASC 将字符串内的全角 双字节 英文字母或片假名更改为半角 单字节 字符 CONCATENATE 将若干文本项合并到一个文本项中 EXACT 检查两个文本值是否完全相同 FIND 在一文本值内查找另一文本值 区分大小写 FIXED 将数字设置为具有固定小数位的文本格式 LEFT 返回文本值最左边的字符 LEN 返回文本字符串中的字符个数 LOWER 将文本转换为小写形式 MID 从文本字符串中的指定位置起返回特定个数的字符 REPLACE 替换文本内的字符 RIGHT 返回文本值最右边的字符 SEARCH 在一文本值中查找另一文本值 不区分大小写 SUBSTITUTE 在文本字符串中以新文本替换旧文本 TEXT 设置数字的格式并将数字转换为文本 TRIM 删除文本中的空格 UPPER 将文本转换为大写形式 A 1 数据 2 Ceramic Insulators 124 TD45 87 3 Copper Coils 12 671 6772 4 Variable Resistors 116010 公式 说明 结果 MID A2 1 FIND A2 1 1 在上面第一个字符串中 抽取从第一个字符到 的文本 Ceramic Insulators MID A3 1 FIND A3 1 1 在上面第二个字符串中 抽取从第一个字符到 的文本 Copper Coils MID A4 1 FIND A4 1 1 在上面第三个字符串中 抽取从第一个字符到 的文本 Variable Resistors A 1 数据 2 Statements 3 Profit Margin 4 margin 公式 说明 结果 SEARCH e A2 6 上面第一个字符串中的第一个 e 出现的位置 开始于第六 个位置 7 SEARCH A4 A3 margin 在 Profit Margin 中的位置 8 REPLACE A3 SEARCH A4 A3 6 Amount 用 Amount 替换 Margin 常用函数及技巧详解 1 求平均值 函数名称 AVERAGE 主要功能 求出所有参数的算术平均值 使用格式 AVERAGE number1 number2 参数说明 number1 number2 需要求平均值的数值或引用单元格 区域 参数不 能超过 30 个 应用举例 如在 B8 单元格中输入公式 AVERAGE B7 D7 即可求出 B7 至 D7 区域值 的平均值 也可求出指定区域的平均值 如 在 B8 单元格中输入公式 AVERAGE B7 D7 F7 H7 7 8 确认后 即可求出 B7 至 D7 区域 F7 至 H7 区域中的数 值和 7 8 的平均值 特别提醒 如果引用区域中包含 0 值单元格 则计算在内 如果引用区域中包含空白 或字符单元格 则不计算在内 2 求一组数据的和 函数名称 SUM 主要功能 计算所有参数数值的和 使用格式 SUM Number1 Number2 参数说明 Number1 Number2 代表需要计算的值 可以是具体的数值 引用的单元 格 区域 逻辑值等 应用举例 如在 D64 单元格中输入公式 SUM D2 D63 确认后即可求出 D 列中 2 63 格中的和 特别提醒 如果参数为数组或引用 只有其中的数字将被计算 数组或引用中的空白单元 格 逻辑值 文本或错误值将被忽略 如果将上述公式修改为 SUM LARGE D2 D63 1 2 3 4 5 则可以求出前 5 名成绩的和 3 求出部分指定数据的和 函数名称 SUMIF 主要功能 计算符合指定条件的单元格区域内的数值和 使用格式 SUMIF Range Criteria Sum Range 参数说明 Range 代表条件判断的单元格区域 Criteria 为指定条件表达式 Sum Range 代表需要计算的数值所在的单元格区域 应用举例 如 在 D64 单元格中输入公式 SUMIF B3 B500 七 1 E3 E500 即 可求 出在 B3 B500 中 为班名 E3 E500 某科目 的和 另外输入 SUMIF B3 B500 七 1 E3 E500 R17 R17 代表人数栏 确认后即可求出 七 1 班的某科成绩的平均值 班平均分 补充 如果把上述公式修改为 SUMIF C2 C63 女 D2 D63 即可求出 女 生的语文成 绩和 特别提醒 其中的 男 和 女 由于是文本型的 需要放在英文状态下的双引号 男 女 中 4 对一组数据进行自动排名 函数名称 RANK 主要功能 返回某一数值在一列数值中的相对于其他数值的排位 使用格式 RANK Number ref order 参数说明 Number 代表需要排序的数值 ref 代表排序数值所处的单元格区域 order 代 表排序方式参数 如果为 0 或者忽略 则按降序排名 即数值越大 排名结果数值越小 如果为非 0 值 则按升序排名 即数值越大 排名结果数值越大 应用举例 如在 C2 单元格中输入公式 RANK B2 B 2 B 31 0 确认后即可得出某 位同学的成绩在 B2 B31 中的排名结果 特别提醒 在上述公式中 我们让 Number 参数采取了相对引用形式 而让 ref 参数采取 了绝对引用形式 增加了一个绝对引用 符号 这样设置后 选中 C2 单元格 将鼠标 移至该单元格右下角 成细十字线状时 通常称之为 填充柄 按住左键向下拖拉 即 可将上述公式快速复制到 C 列下面的单元格中 完成其他同学的排名统计 5 求出一组数中的最大值和最小值 函数名称 MAX 求最大值 主要功能 求出一组数中的最大值 使用格式 MAX number1 number2 参数说明 number1 number2 代表需要求最大值的数值或引用单元格 区域 参数 不超过 30 个 应用举例 输入公式 MAX E4 E100 确认后即可显示出 E4 至 E100 单元中的最大值 特别提醒 如如果参数中有文本或逻辑值 则忽略 MIN 函数 求最小值 函数名称 MIN 主要功能 求出一组数中的最小值 使用格式 MIN number1 number2 参数说明 number1 number2 代表需要求最小值的数值或引用单元格 区域 参数 不超过 30 个 应用举例 输入公式 MAX E4 E100 确认后即可显示出 E4 至 E100 单元中的最小值 特别提醒 如果参数中有文本或逻辑值 则忽略 6 根据给定的条件 返回相对应的指定内容 函数名称 IF 主要功能 根据对指定条件的逻辑判断的真假结果 返回相对应的内容 使用格式 IF Logical Value if true Value if false 参数说明 Logical 代表逻辑判断表达式 Value if true 表示当判断条件为逻辑 真 TRUE 时的显示内容 如果忽略返回 TRUE Value if false 表示当判断条件为逻辑 假 FALSE 时的显示内容 如果忽略返回 FALSE 应用举例 在 D26 单元格中输入公式 IF C26 60 及格 不及格 确认以后 如果 C26 单元格中的数值大于或等于 60 则 D26 单元格显示 及格 字样 反之显示 不及格 字 样 也可根据实际可多设几档条件和内容 如 IF A3 60 不合格 IF A3 80 合格 IF A3 80 确认后 即可统计 出 B1 至 B13 单元格区域中 数值大于等于 80 的单元格数目 特别提醒 允许引用的单元格区域中有空白单元格出现 8 巧用函数组合进行多条件的计数统计 原创 举例说明 第一行为表头 A 列是 姓名 B 列是 班级 C 列是 语文成绩 现在要统计 班级 为 2 语文成绩 大于等于 100 的人数 统计结果存放在本工作表的其他列 公式如下 SUM IF B2 B500 2 C2 C500 100 1 0 输入完公式后按 Ctrl Shift Enter 键 让它自动加上数组公式符号 另 如果只求某一分数段的人数 假如 B 列是学生的语文成绩 则求从 B2 B56 这一范围 内分数在 80 90 之间的人数 则可以用这样一个公式 COUNTIF B2 B56 80 COUNTIF B2 B56 90 输入公式回车即可 注意 函数 COUNTIF 是条件统计函数 只能对一个条件进行限制 如果判断条件有两个以上 则不能用 另外 公式中的各种符 号都是在英文状态下输入的 还有一个办法 求出一个数目 然后采用递减的办法 COUNTIF B2 B56 80 A2 9 核对两列数据或文本是否一样 举例说明 比较 A 列和 B 列是否相同 方法一 在 c 列输入 IF a1 b1 一样 不一样 即 相同的显示 一样 不相同的显示 不一样 方法二 把 B 列与 A 列不同之处标识出来 1 如果是要求 A B 两列的同一行数据相比较 假定第一行为表头 单击 A2 单元格 点 格式 条件格式 将条件设为 单元格数值 不等于 B2 点 格式 字体 颜色 选中红色 点两次 确定 用格式刷将 A2 单元格的条件格式向下复制 B 列可参照此方法设置 不同的自动显示为红色 2 如果是 A 列与 B 列整体比较 即相同数据不在同一行 假定第一行为表头 单击 A2 单元格 点 格式 条件格式 将条件设为 公式 COUNTIF B B A2 0 点 格式 字体 颜色 选中红色 点两次 确定 用格式刷将 A2 单元格的条件格式向下复制 B 列可参照此方法设置 注 实际用时要将被用来比较的复制到相邻两列来 整体设条件 按第一格设 按以上方法设置后 AB 列均有的数据不着色 A 列有 B 列无或者 B 列有 A 列无的数据标 记为红色字体 提示 如果 AB 两列整体区别时 先要用鼠标点击成为整体选中再设置 10 跨表调取数据方法 原创 用 VLOOKUP 函数 主要功能 在表格或数值数组的首列查找指定的数值 并由此返回表格或数组中该数值所 在行中指定列处的数值 这里所说的 数组 可以理解为表格中的一个区域 数组的列序号 数组的 首列 就是这 个区域的第一纵列 此列右边依次为第 2 列 3 列 假定某数组区域为 B2 E10 那么 B2 B10 为第 1 列 C2 C10 为第 2 列 语法 VLOOKUP 查找值 区域 列序号 逻辑值 查找值 为需要在数组第一列中查找的数值 它可以是数值 引用或文字符串 区域 数组所在的区域 如 B2 E10 也可以使用对区域或区域名称的引用 例如数据 库或数据清单 列序号 即希望区域 数组 中待返回的匹配值的列序号 为 1 时 返回第一列中的数 值 为 2 时 返回第二列中的数值 以此类推 若列序号小于 1 函数 VLOOKUP 返回错 误值 VALUE 如果大于区域的列数 函数 VLOOKUP 返回错误值 REF 逻辑值 为 TRUE 或 FALSE 它指明函数 VLOOKUP 返回时是精确匹配还是近似匹配 如果为 TRUE 或省略 则返回近似匹配值 也就是说 如果找不到精确匹配值 则返回 小于 查找值 的最大数值 如果 逻辑值 为 FALSE 函数 VLOOKUP 将返回精确匹配值 如果找不到 则返回错误值 N A 如果 查找值 为文本时 逻辑值 一般应为 FALSE 另外 如果 查找值 小于 区域 第一列中的最小数值 函数 VLOOKUP 返回错误值 N A 如果函数 VLOOKUP 找不到 查找值 且 逻辑值 为 FALSE 函数 VLOOKUP 返回错误 值 N A 下面用示意图来说明 VLOOKUP 函数的使用方法 如 VLOOKUP A10 模拟 1 C 3 K 500 3 0 A10 表示要查找的对象 在表 2 中 模拟 1 表示被引用表的名称 C 3 K 500 表示被引用表的要引用的区域 C3 表示要引用的第一格 K500 表示要 引用的最后一格 表示绝对引用 是锁定的意思 没有为不锁定 复制时要变化 3 表示要引用区域的第 3 列数据 如例中的从 C 开始为 1 0 表示准确查找 如果是 1 表示近似查找 11 自动给出当时日期 函数名称 TODAY 主要功能 给出系统日期 使用格式 TODAY 参数说明 该函数不需要参数 应用举例 输入公式 TODAY 确认后即刻显示出系统日期和时间 如果系统日期和 时间发生了改变 只要按一下 F9 功能键 即可让其随之改变 特别提醒 显示出来的日期格式 可以通过单元格格式进行重新设置 12 其它一些常用的但又特殊的方法 一 如何在 EXCEL 中设置单元格编辑权限 保护部分单元格 1 先选定所有单元格 点 格式 单元格 保护 取消 锁定 前面的 2 再选定你要保护的单元格 点 格式 单元格 保护 在 锁定 前面打上 3 点 工具 保护 保护工作表 输入两次密码 点两次 确定 即可 二 在 EXCEL 中建立下拉列表按钮 选定你要设置下拉列表的单元格 点 数据 有效性 设置 在 允许 下面选择 序列 在 来源 框中输入你的下拉列表内容 各项之间用半角逗号隔开 如 A B C D 选中 提供下拉前头 点 确定 三 自动从学生的身份证号 提取 出生日期和性别 第一步 转换身份证号码格式 选中该列中的所有身份证号后 右击鼠标 选择 设置单元格格式 在弹出对话框中 数 字 标签内的 分类 设为 文本 然后点击确定 第二步 提取出 出生日期 将光标指针放到 出生日期 列的单元格内 这里以 C2 单元格为例 然后输入 MID B2 7 4 年 MID B2 11 2 月 MID B2 13 2 日 注意 外侧的双引号不用 输入 函数式中的引号和逗号等符号应在英文状态下输入 回车后 你会发现在 C2 单 元格内已经出现了该学生的出生日期 然后 选中该单元格后拖动填充柄 其它单元格内 就会出现相应的出生日期 还可以提取出 性别 判断性别 男女 选中 性别 列的单元格 如 D2 输入 IF MID B2 17 1 2 TRUNC MID B2 17 1 2 女 男 注意如上 后回车 该生 是男还是女 已经乖乖地判断出来了 拖动填充柄让 其他学生的性别也自动输入 四 解释 单元格的相对引用 绝对引用和混合引用 相对引用 绝对引用和混合引用是指在公式中使用单元格或单元格区域的地址时 当将公 式向旁边复制时 地址是如何变化的 具体情况举例说明 1 相对引用 复制公式时地址跟着发生变化 如 C1 单元格有公式 A1 B1 当将公式复制到 C2 单元格时变为 A2 B2 当将公式复制到 D1 单元格时变为 B1 C1 2 绝对引用 复制公式时地址不会跟着发生变化 如 C1 单元格有公式 A 1 B 1 当将公式复制到 C2 单元格时仍为 A 1 B 1 当将公式复制到 D1 单元格时仍为 A 1 B 1 3 混合引用 复制公式时地址的部分内容跟着发生变化 如 C1 单元格有公式 A1 B 1 当将公式复制到 C2 单元格时变为 A2 B 1 当将公式复制到 D1 单元格时变为 A1 C 1 规律 加上了绝对地址符 的列标和行号为绝对地址 在公式向旁边复制时不会 发生变化 没有加上绝对地址符号的列标和行号为相对地址 在公式向旁边复制时会跟着 发生变化 混合引用时部分地址发生变化 注意 工作薄和工作表都是绝对引用 没有相对引用 技巧 在输入单元格地址后可以按 F4 键切换 绝对引用 混合引用 和 相对引用 状态 五 出错自动提示设置方法 1 使用 数据有效性 选定我们需要检查的数据 如 B1 E20 单击 数据 有效性 出现 数据有效性 对话框 在 允许 列表框处选择 自定义 公式 文本框中输入如下公式 COUNTIF B 1 E 20 B1 1 单击 出错警告 标签 勾选 输入无效数据时显示出错警告 样式 列表处选择 停止 右侧为出错提示信息 在 标题 处输入 数据重复 错误信息 处输入 数据重复 请重新输入 当我们在 B1 E20 区域中的 D7 单元格中输入 张三 后 因为与同在该区域的 B4 单元格内容重复 所以立刻 会弹出一个警告对话框 阻止重复输入的数据 只有取消或更换输入的内容后 输入操作 才得到允许 方法 2 使用 条件格式 选定我们需要检查的数据区域 如 A1 E20 单击 格 式 条件格式 出现 条件格式 对话框 条件 1 列表框处选择 公式 在右侧的文本框中 输入如下公式 COUNTIF A 1 E 20 E20 1 单击 格式 按钮 在 单元格格式 对话框 中选择出现重复数据单元格的外观格式 如字体 边框和图案 本例中设置为浅绿色背景 当我们在 A1 E20 区域中的 C10 单元格中输入 张三 后 该区域中包含相同内容的 C10 和 A6 单元格底纹颜色立刻变为浅绿色 六 保护表格中的数据 如选取单元格 A1 F8 点击 格式 单元格 选单 选择 保护 选项 消除锁定复选框前的 对勾 单击确定 然后 再选取单元格 F4 F7 和 B8 F8 点击 格式 单元格 选单 选 择 保护 选项 使锁定复选框选中 单击确定 这样 就把这些单元格锁定了 接着 点 击 工具 保护 保护工作表 选单 这时 会要求你输入密码 输入两次相同的密码后 点击确定 工作表就被保护起来了 单元格的锁定也就生效了 今后 可以放心地输入数 据而不必担心破坏公式 如果要修改公式 则点击 工具 保护 撤消保护工作表 选单 这时 会要求你输入密码 输入正确的密码后 就可任意修改公式了