电子表格函数公式使用集锦

1 电子表格函数公式使用集锦电子表格函数公式使用集锦 怎么让怎么让 excelexcel 表格公式只能看不能被改 表格公式只能看不能被改 选定不需要保护的的单元格 单击右键 选定不需要保护的的单元格 单击右键 设置单元格格式设置单元格格式 取消锁定取消锁定 选定需要保护的的单元格 单击右键 设置单元格格式选定需要保护的的单元格 单击右键 设置单元格格式 锁定锁定 工具工具 保护保护 保护工作表保护工作表 输入密码输入密码 确定 确定 一 电子表格中由身份证号自动导出年月日的公式一 电子表格中由身份证号自动导出年月日的公式 IF LEN E1 15 19 MID E1 7 2 MID E1 9 2 IF LEN E1 15 19 MID E1 7 2 MID E1 9 2 MID E1 11 2 MID E1 7 4 MID E1 11 2 MID E1 13 2 MID E1 11 2 MID E1 7 4 MID E1 11 2 MID E1 13 2 转换出生年月如转换出生年月如 1986 05 231986 05 23 说明 说明 E E 列数 列数 E1890E1890 第 第 E E 列第列第 18901890 行 输入身份证号码行 输入身份证号码 LENLEN texttext 返回本字符串的个数 返回本字符串的个数 LENLEN 123 123 3 3 LEN E1 15LEN E1 15 表示如果身份证号码为表示如果身份证号码为 1515 个数字个数字 表示相加表示相加 MIDMID 字符串 字符串 M M N N 从该字符串第 从该字符串第 M M 位开始 取位开始 取 N N 位字符 位字符 MIDMID A1A1 3 3 4 4 3456 3456 从 从 A1 A1 单元格中的第单元格中的第 3 3 位起截取位起截取 4 4 个数个数 IFIF 条件表达式 语句 条件表达式 语句 1 1 语句 语句 2 2 如果条件成立 那么就执行语句 如果条件成立 那么就执行语句 1 1 否则执行 否则执行 语句语句 2 2 LEFTLEFT A1 14A1 14 截取 截取 A1A1 单元格前单元格前 1414 位数位数 RIGHTRIGHT A1 14A1 14 截取 截取 A1A1 单元格后单元格后 1414 位数位数 身份证号码有两种 如身份证号码有两种 如 352124860213541 352124860213541 或或352124198602135411 如果如果 E1E1 是是 1515 个个 为为 1919 加上从第加上从第 7 7 个开始取个开始取 2 2 个个 86 86 加上加上 加上从第加上从第 9 9 个开始取个开始取 2 2 个个 02 02 加加 上上 加上从第加上从第 1111 个开始取个开始取 2 2 个个 13 13 合起来为合起来为 1986 02 13 1986 02 13 否则为从第 否则为从第 7 7 个开始取个开始取 4 4 个个 1986 1986 加上加上 加上从第加上从第 1111 个开始取个开始取 2 2 个个 02 02 加上加上 加上从第加上从第 1313 个开始取个开始取 2 2 个个 13 13 合起来为合起来为 1986 02 13 1986 02 13 19 19 为直接写入的数 为直接写入的数 IF LEN A1 15 19 MID A1 7 4 MID A1 7 6 IF LEN A1 15 19 MID A1 7 4 MID A1 7 6 转换出生年月取如转换出生年月取如 198606 198606 IF LEN A1 15 CONCATENATE 19 MID A1 7 2 MID A1 9 2 IF LEN A1 1 IF LEN A1 15 CONCATENATE 19 MID A1 7 2 MID A1 9 2 IF LEN A1 1 8 CONCATENATE MID A1 7 4 MID A1 11 2 8 CONCATENATE MID A1 7 4 MID A1 11 2 身份证错身份证错 转换出生年月取如转换出生年月取如 1986 051986 05 2 2010 MID B1 1 4 IF MID B1 5 2 0 8 1 0 2010 MID B1 1 4 IF MID B1 5 2 0 8 1 0 计算年龄计算年龄 IF LEN A1 15 YEAR NOW 1900 IF LEN A1 15 YEAR NOW 1900 VALUE MID A1 7 2 IF LEN A1 18 YEAR NOW VALUE MID A1 7 4 VALUE MID A1 7 2 IF LEN A1 18 YEAR NOW VALUE MID A1 7 4 身份证错身份证错 计算年龄 月数全部不算如计算年龄 月数全部不算如 2424 岁岁 2 2 个月和个月和 2424 岁岁 1111 个月都是个月都是 2424 岁岁 IF LEN A1 15 IF MOD VALUE RIGHT A1 3 2 0 IF LEN A1 15 IF MOD VALUE RIGHT A1 3 2 0 女女 男男 IF LEN A1 IF LEN A1 18 IF MOD VALUE MID A1 15 3 2 0 18 IF MOD VALUE MID A1 15 3 2 0 女女 男男 身份证错身份证错 转换性别转换性别 二 成绩在年级里的排名菜二 成绩在年级里的排名菜 RANKRANK 函数的使用 函数的使用 RANK N2 N 2 N 1501 0 RANK N2 N 2 N 1501 0 N2N2 为所要排名的单元格 为所要排名的单元格 N 2 N 1501 N 2 N 1501 为从为从 N2N2 列到列到 N1501N1501 列 列 0 0 表示表示为按照降序为按照降序 排列的列表 不为零为按照升序排列的列表排列的列表 不为零为按照升序排列的列表 RANK C1 C 1 C 10 RANK C1 C 1 C 10 为为 1010 个学生中的第一个的排名个学生中的第一个的排名 三 利用函数统计考试成绩三 利用函数统计考试成绩 COUNTA A1 A25 COUNTA A1 A25 算有数值的单元格个数应考人数算有数值的单元格个数应考人数 COUNT B1 B25 COUNT B1 B25 和上面的一样用处算出考试人数和上面的一样用处算出考试人数 COUNTBLANK B1 B25 COUNTBLANK B1 B25 算出缺考人数算出缺考人数 COUNTIF B1 B25 90 COUNTIF B1 B25 90 算算 9090 分以上人数分以上人数 COUNTIF B1 B25 80 COUNTIF B1 B25 80 COUNTIF B1 B25 90 COUNTIF B1 B25 90 算算 8080 到到 9090 分人数分人数 MAX C1 C25 MAX C1 C25 算最高分算最高分 MIN C1 C25 MIN C1 C25 算最低分算最低分 AVERAGE C1 C25 AVERAGE C1 C25 算平均分算平均分 COUNTIF C1 C25 90 COUNT C1 C25 90 COUNTIF C1 C25 90 COUNT C1 C25 90 分以上占百分比分以上占百分比 MEDIAN B1 B25 MEDIAN B1 B25 算中位数算中位数 MODE B1 B25 MODE B1 B25 算众数算众数 STDEVP B1 B25 STDEVP B1 B25 算标准差算标准差 四 四 文本格式转换成数值格式文本格式转换成数值格式 在原单元格上转换 在任一空白单元格输入在原单元格上转换 在任一空白单元格输入 1 1 复制复制 1 1 选定所有需要改变的单元格选定所有需要改变的单元格 右键右键 选择性粘贴选择性粘贴 选选 乘乘 用 用 0 0 加计算也行加计算也行 3 在新单元格上转换 选定新单元格 在新单元格上转换 选定新单元格 所要转换的文本单元格所要转换的文本单元格 0 0 在新单元格上转换 复制在新单元格上转换 复制 选择性粘贴选择性粘贴 数值数值 点击下拉点击下拉 转换成数值转换成数值 五 五 的应用的应用 B2 C2 B2 C2 表示把两个单元格的数值合为一个如表示把两个单元格的数值合为一个如 45 45 67 67 合为合为 4567 4567 F 17 C1 F 17 C1 表示在一个数值前加一个数 在任意单元格输入表示在一个数值前加一个数 在任意单元格输入 123123 设为绝对值 再加设为绝对值 再加 所要添加的单元格所要添加的单元格 六 六 IFIF 的应用 满足其中一个条件的判断 的应用 满足其中一个条件的判断 IF A2 89 IF A2 89 优优 IF A2 79 IF A2 79 优优 IF A2 69 IF A2 69 良良 IF A2 59 IF A2 59 及格及格 不及格不及格 IF A2 B2 IF A2 B2 超预算超预算 预算内预算内 是对预算执行结果的判断是对预算执行结果的判断 IF A2 100 SUM B5 B15 IF A2 100 SUM B5 B15 如果如果 A2A2 数字为数字为 100100 则计算单元格区域 则计算单元格区域 B5 B15B5 B15 否 否 则返回空文本则返回空文本 IF AND A1 0 A110 A1 100 A10 A110 A1 100 A160 IF AND C1 90 IF C1 60 IF AND C1 90 优秀优秀 合格合格 不合格不合格 如果单元格如果单元格 C1C1 的值大于的值大于 6060 则执行第二个参数 在这里为嵌套函数 继续判断单元格 则执行第二个参数 在这里为嵌套函数 继续判断单元格 C1C1 的值是否大于的值是否大于 9090 为 为 了让大家体会一下了让大家体会一下 ANDAND 函数的应用 写成函数的应用 写成 AND C1 90 AND C1 90 实际上可以仅写 实际上可以仅写 C1 90C1 90 如果满 如果满 足在单元格足在单元格 C2C2 中显示优秀字样 不满足显示合格字样 如果中显示优秀字样 不满足显示合格字样 如果 C1C1 的值以上条件都不满足 的值以上条件都不满足 则执行第三个参数即在单元格则执行第三个参数即在单元格 C2C2 中显示不合格字样 中显示不合格字样 IF A1 1 A IF A1 2 B IF A1 3 C IF A1 4 D IF A1 5 E IF A1 6 F IF A1 1 A IF A1 2 B IF A1 3 C IF A1 4 D IF A1 5 E IF A1 6 F IF A1 7 G IF A1 8 G IF A1 9 Q IF A1 10 l IF A1 11 k IF AIF A1 7 G IF A1 8 G IF A1 9 Q IF A1 10 l IF A1 11 k IF A 1 12 y IF A1 13 x IF A1 14 n IF A1 15 m IF A1 16 o 1 12 y IF A1 13 x IF A1 14 n IF A1 15 m IF A1 16 o 为多层嵌套为多层嵌套 七 七 ANDAND 函数 同时满足条件判断 函数 同时满足条件判断 ANDAND 条件一 条件二 条件一 条件二 条件三条件三 条件一 条件一 二二 三三 都成立时返回都成立时返回 TURETURE 否则返 否则返 回回 FALSEFALSE 4 IF AND A1 60 B1 60 C1 60 IF AND A1 60 B1 60 C1 60 及格及格 不及格不及格 当 当 ANDAND A1 60A1 60 B1 60B1 60 C1 60C1 60 为为 TURETURE 时返回时返回 及格及格 为 为 FALSEFALSE 时返回时返回 不及格不及格 IF A1 60 IF A160 A160 A170 A170 A185 IF A1 85 优秀优秀 可以和可以和 IFIF 函数合起来使用函数合起来使用 八 八 CONCATENATECONCATENATE 函数 函数 将将几几个个文文本本字字符符串串合合并并为为一一个个文文本本字字符符串串 CONCATENATE A1 B1 C1 D1 CONCATENATE A1 B1 C1 D1 也也可可以以用用 和和号号 运运算算符符代代替替函函数数 C CO ON NC CA AT TE EN NA AT TE E 实实现现文文本本项项的的合合并并 九 名称和标志九 名称和标志 为了更加直观地标识单元格或单元格区域 我们可以给它们赋予一个名称 从而在为了更加直观地标识单元格或单元格区域 我们可以给它们赋予一个名称 从而在 公式或函数中直接引用 例如公式或函数中直接引用 例如 B2 B46 B2 B46 区域存放着学生的物理成绩 求解平均分的公区域存放着学生的物理成绩 求解平均分的公 式一般是式一般是 AVERAGE B2 B46 AVERAGE B2 B46 在给 在给 B2 B46B2 B46 区域命名为区域命名为 物理分数物理分数 以后 该公式就以后 该公式就 可以变为可以变为 AVERAGE AVERAGE 物理分数物理分数 从而使公式变得更加直观 从而使公式变得更加直观 给一个单元格或区域命名的方法是给一个单元格或区域命名的方法是 选中要命名的单元格或单元格区域 鼠标单击编选中要命名的单元格或单元格区域 鼠标单击编 辑栏顶端的辑栏顶端的 名称框名称框 在其中输入名称后回车 也可以选中要命名的单元格或单元格区 在其中输入名称后回车 也可以选中要命名的单元格或单元格区 域 单击域 单击 插入插入 名称名称 定义定义 菜单命令 在打开的菜单命令 在打开的 定义名称定义名称 对话框中输入名称后对话框中输入名称后 确定即可 如果你要删除已经命名的区域 可以按相同方法打开确定即可 如果你要删除已经命名的区域 可以按相同方法打开 定义名称定义名称 对话框 对话框 选中你要删除的名称删除即可 选中你要删除的名称删除即可 由于由于 ExcelExcel 工作表多数带有工作表多数带有 列标志列标志 例如一张成绩统计表的首行通常带有 例如一张成绩统计表的首行通常带有 序号序号 姓名姓名 数学数学 物理物理 等等 列标志列标志 也可以称为字段也可以称为字段 如果单击 如果单击 工具工具 选项选项 菜单命令 在打开的对话框中单击菜单命令 在打开的对话框中单击 重新计算重新计算 选项卡 选中选项卡 选中 工作簿选项工作簿选项 选项组中选项组中 的的 接受公式标志接受公式标志 选项 公式就可以直接引用选项 公式就可以直接引用 列标志列标志 了 例如了 例如 B2 B46 B2 B46 区域存区域存 放着学生的物理成绩 而放着学生的物理成绩 而 B1B1 单元格已经输入了单元格已经输入了 物理物理 字样 则求物理平均分的公式可字样 则求物理平均分的公式可 以写成以写成 AVERAGE AVERAGE 物理物理 十 几个常用函数十 几个常用函数 ABS A2 ABS A2 则在 则在 A2A2 单元格中无论输入正数 如单元格中无论输入正数 如 100100 还是负数 如 还是负数 如 100 100 B2B2 中均中均 显示出正数 如显示出正数 如 100100 5 AND A5 60 B5 60 AND A5 60 B5 60 确认 如果 确认 如果 C5C5 中返回中返回 TRUETRUE 说明 说明 A5A5 和和 B5B5 中的数值均大于中的数值均大于 等于等于 6060 如果返回 如果返回 FALSEFALSE 说明 说明 A5A5 和和 B5B5 中的数值至少有一个小于中的数值至少有一个小于 6060 COLUMN B11 COLUMN B11 确认后显示为 确认后显示为 2 2 即 即 B B 列 列 十一 与求和有关的函数十一 与求和有关的函数 1 1 SUM H3 H12 SUM H3 H12 求求 H3H3 至至 H12H12 的和的和 2 2 SUBTOTAL function num ref1 ref2 SUBTOTAL function num ref1 ref2 分类汇总分类汇总 Function numFunction num 为为 1 1 到到 1111 之间的数字 指定使用何种函数在数据清单中进行分之间的数字 指定使用何种函数在数据清单中进行分 类汇总计算 类汇总计算 Function NumFunction Num 函数函数 1 1 AVERAGEAVERAGE 求算术平均数求算术平均数 2 2 COUNTCOUNT 计计算算参参数数列列表表中中的的数数字字项项的的个个数数 3 3 COUNTACOUNTA 计算单元格区域或数组中包含数据的单元格个数 计算单元格区域或数组中包含数据的单元格个数 4 4 MAXMAX 求最大值求最大值 5 5 MINMIN 求最小值求最小值 6 6 PRODUCTPRODUCT 单元格内的乘积单元格内的乘积 7 7 STDEVSTDEV 估算样本的标准偏差 反映相对于平均值的离散程度估算样本的标准偏差 反映相对于平均值的离散程度 8 8 STDEVPSTDEVP 整整个个样样本本总总体体的的标标准准偏偏差差 9 9 SUMSUM 求和求和 1010 VARVAR 计计算算基基于于给给定定样样本本的的方方差差 1111 VARPVARP 计算基于整个样本总体的方差计算基于整个样本总体的方差 例 例 SUBTOTAL 9 A2 A5 SUBTOTAL 9 A2 A5 对对 A2A2 至至 A5A5 列使用列使用 SUMSUM 函数计算出的分类汇总函数计算出的分类汇总 303 303 SUBTOTAL 1 A2 A5 SUBTOTAL 1 A2 A5 对对 A2A2 至至 A5A5 列使用列使用 AVERAGEAVERAGE 函数计算出的分类汇总函数计算出的分类汇总 75 75 75 75 3 3 SUMIFSUMIF 根根据据指指定定条条件件对对若若干干单单元元格格求求和和 例 例 SUMIF C 3 C 12 SUMIF C 3 C 12 销售部销售部 F 3 F 12 F 3 F 12 C 3 C 12 C 3 C 12 指部门名称单元指部门名称单元 格 格 销售部销售部 指计算其中的指计算其中的 销售部销售部 部门 部门 F 3 F 12 F 3 F 12 指部门名称相应的数值单指部门名称相应的数值单 元格 元格 6 4 4 S SU UM MP PR RO OD DU UC CT T 在在给给定定的的几几组组数数组组中中 将将数数组组间间对对应应的的元元素素相相乘乘 并并返返回回乘乘 积积之之和和 S SU UM MP PR RO OD DU UC CT T A A1 15 5 A A1 16 6 B B1 15 5 B B1 16 6 表表示示 A A1 15 5 B B1 15 5 A A1 16 6 B B1 16 6 SUMPRODUCT B2 C4 D2 E4 SUMPRODUCT B2 C4 D2 E4 表示表示 B2 D2 C2 E2 B2 D2 C2 E2 B4 D4 C4 E4 B4 D4 C4 E4 S SU UM MP PR RO OD DU UC CT T B B 2 2 B B 1 11 1 E E2 2 C C 2 2 C C 1 11 1 F F 1 1 计计算算符符合合 2 2 个个及及以以上上条条 件件的的数数据据个个数数 4 4 人人的的单单元元格格输输入入公公式式 姓名姓名性别性别职称职称性别性别中一中一中二中二 A A 男男中一中一男男 4 4 B B 女女中二中二女女 C C 女女中一中一 D D 男男中一中一 E E 女女中一中一 F F 男男中二中二 G G 女女中二中二 H H 男男中一中一 I I 男男中一中一 J J 女女中一中一 S SU UM MP PR RO OD DU UC CT T B B 2 2 B B 1 11 1 F F2 2 C C 2 2 C C 1 11 1 G G 1 1 D D 2 2 D D 1 11 1 计计算算男男 女女 分分别别是是中中一一或或中中二二的的总总课课时时数数 在在 1 15 5 节节单单元元格格中中输输入入公公式式 姓名姓名性别性别职称职称课时课时性别性别中二中二中一中一 A A 男男中一中一 1515 男男 1515 B B 女女中二中二 1616 女女 C C 女女中一中一 1414 D D 男男中一中一 1313 E E 女女中一中一 1818 F F 男男中二中二 1515 G G 女女中二中二 1616 H H 男男中一中一 1414 I I 男男中一中一 1717 J J 女女中一中一 1818 SUMSQSUMSQ 函数 计算多个数值的平方和 如函数 计算多个数值的平方和 如 SUMSQSUMSQ B2 C2 B2 B2 C2 B2 的平方的平方 C2 C2 的平方 的平方 ROUNDROUND 函数 如函数 如 ROUND B2 2 ROUND B2 2 就是对就是对 B2B2 进行四舍五入保留进行四舍五入保留 2 2 位小数 位小数 INTINT 将数字向下舍入到最接近的将数字向下舍入到最接近的取整函数 取整函数 7 IFIF 和和 ANDAND 嵌套使用 嵌套使用 IF AND A1 60 B1 60 C1 60 IF AND A1 60 B1 60 C1 60 及格及格 不及格不及格 当 当 A1A1 B1 C1B1 C1 都大于都大于 6060 时时 返回返回 及格及格 IF A1 60 IF A160 A160 A170 A170 A185 IF A1 85 优秀优秀 当 当 A1 60A1 60 时返回时返回 不及格不及格 当 当 60 A1 7060 A1 70 时返回时返回 及格及格 当当 70 A1 8570 A185A1 85 时返回时返回 优秀优秀 COUNTIFCOUNTIF 函数函数 计算其中满足条件的单元格数目计算其中满足条件的单元格数目 如 如 COUNTIF B4 B10 90 COUNTIF B4 B10 90 计算计算 B4B4 到到 B10B10 这个范围各科成绩中有多少个数值大于这个范围各科成绩中有多少个数值大于 9090 的单元格 如的单元格 如 COUNTIF C 2 C 13 A17 COUNTIF C 2 C 13 A17 计算 计算 C 2 C 13 C 2 C 13 这个范围有多少个这个范围有多少个 A17A17 A17A17 存放的是姓名 存放的是姓名 SUMIF C 2 C 13 A17 B 2 B 13 SUMIF C 2 C 13 A17 B 2 B 13 计算其中 计算其中 A17A17 的销售奖金 的销售奖金 C 2 C 13 C 2 C 13 是是 销售人员的姓名 销售人员的姓名 A17A17 是其中的一个姓名 是其中的一个姓名 B 2 B 13 B 2 B 13 是销售金额区域 是销售金额区域 IF C17 50000 10 15 C17IF C17 50000 10 15 C17 如果订单总额小于如果订单总额小于 5000050000 则奖金为则奖金为 10 10 如果订单总 如果订单总 额大于等于额大于等于 5000050000 则奖金为 则奖金为 15 15 十二 字母大小写转换十二 字母大小写转换 LOWERLOWER A1 A1 将将 A1A1 文字串中的所有字母转换为小写字母 文字串中的所有字母转换为小写字母 UPPERUPPER A1 A1 将将 A1A1 文本转换成大写形式 文本转换成大写形式 PROPERPROPER A1 A1 将将 A1A1 文字串的首字母及任何非字母字符之后的首字母转换成大写 将文字串的首字母及任何非字母字符之后的首字母转换成大写 将 其余的字母转换成小写 其余的字母转换成小写 十三 取出字符串中的部分字符十三 取出字符串中的部分字符 LEFT ThisLEFT This isis anan apple 4 Thisapple 4 This 从前面取从前面取 RIGHT ThisRIGHT This isis anan apple 5 appleapple 5 apple 从后面取从后面取 MID ThisMID This isis anan apple 6 2 isapple 6 2 is 从中间取从中间取 十四 取出当前系统时间十四 取出当前系统时间 日期信息日期信息 NOW NOW 取当前系统取当前系统 年月日时分年月日时分 TODAY TODAY 取当前系统取当前系统 年月日年月日 YEAR E5 2001YEAR E5 2001 取单元格的取单元格的 年年 MONTH E5 5MONTH E5 5 取单元格的取单元格的 月月 8 DAY E5 30DAY E5 30 取单元格的取单元格的 日日 HOUR E5 12HOUR E5 12 取单元格的取单元格的 时时 DATEDIFDATEDIF 计算两个日期之间的天数 月数或年数 其中计算年数为 计算两个日期之间的天数 月数或年数 其中计算年数为 DATEDIF A24 TODAY y DATEDIF A24 TODAY y Y Y 时间段中的整年数 时间段中的整年数 M M 时间段中的整月数 时间段中的整月数 D D 时时 间段中的天数 间段中的天数 MD MD 为日期中天数的差 忽略日期中的月和年 直接天数相减 不够减为日期中天数的差 忽略日期中的月和年 直接天数相减 不够减 要向上月借一 要向上月借一 YM YM 为日期中月数的差 忽略日期中的日和年 直接月数相减 不够为日期中月数的差 忽略日期中的日和年 直接月数相减 不够 减要向上月借一 减要向上月借一 YD YD 为日期中天数的差 忽略日期中的年 月日合计相减 不够减为日期中天数的差 忽略日期中的年 月日合计相减 不够减 要向上月借一 要向上月借一 VALUEVALUE 将代表数字的文字串转换成数字 语法形式为 将代表数字的文字串转换成数字 语法形式为 VALU