Excel中IF函数的嵌套用法

第 1 页 共 13 页 Excel 中 IF 函数的嵌套用法 多条件 函数格式 if logical test value if true value if false 其中 logical test 表示设定的条件 value if true 表示当目标单元格与设定条件相符 时返回的函数值 value if false 表示当目标单元格与设定条件不符时返回的函数值 一 IF 函数的语法结构 IF 是执行真假值判断 根据逻辑测试的真假值返回不同的结果 语法结构 IF 条 件 结果 1 结果 2 二 操作方法 打开所需软件 Excel 输入所需的的表格 再找到所填等级资料的第一行 然后 找 到工具栏的的 fx 或者点菜单 插入 fx 函数 在出现的粘贴函数窗口中选择 全部 移动滚动条选择 IF 此时出现 IF 函数编辑窗口 在第一个文本框内输入第一 个条件 第二个文本框内输入第一个条件结果 第三个文本框内输入以后所有的 条件并相 应的结果 如公式 IF B2 89 A IF B2 79 B IF B2 69 C IF B2 59 D F 第一 条件 B2 89 第一条件结果 A 第三个文本框输入 IF B2 79 B IF B2 69 C IF B2 59 D F 第二个方法是在编辑公式栏内直接输入以下的公式 三 示例 1 在学生成绩工作表 中 单元格 B2 中包含计算当前成绩等级的公式 如果 B2 中的公式结果大于等于 60 则下面的函数将显示 及格 否则将显示 不及格 条件 结果 1 结果 2 IF B2 60 及格 不及格 2 如果要给以学生成绩为名称所引用的数字设置字母级别 请参阅下表 学生成绩统计情况 大于 89 A 或优 80 到 89 B 或良 70 到 79 C 或中 60 到 69 D 或及格 小于 60 F 或差 可以使用下列嵌套 IF 函数 IF B2 89 A IF B2 79 B IF B2 69 C IF B2 59 D F 或 IF B2 89 优 IF B2 79 良 IF B2 69 中 IF B2 59 及格 差 还有一种方法为 IF B2 60 F IF B2 69 D IF B2 79 C IF B2 89 B A 或 IF B2 60 差 IF B2 69 及格 IF B2 79 中 IF B2 89 良 优 当在第一个空格出现结果后 下面结果如下操作 按住 Ctrl 把鼠标放在格子右下角 当 鼠标变成十字时间向下拖动 即可产生所有结果 注 1 B2 是所要计算的值所在的列和 行号 B 为列号 数字 2 为第一个值所在的行 2 IF 函数的结尾的 反括号的 个数应为 IF 的个数 如 IF B2 60 差 IF B2 69 及格 IF B2 79 中 IF B25 if a1 6 六 明白意思 就是截为两段再做判 断 这样可以不超过 7 重 2 可以用自定义数字格式 也可以用 CHOOSE A1 1 一二三四五六七八九十 问 实际上我的要求是现行高一成绩统计中 b if a1 语文 语文 if a1 数学 数学 if a1 英语 英语 if a1 物理 物理 if a1 化学 化学 if a1 历史 历史 if a1 政治 政治 if a1 生物 生物 if a1 地理 地理 这样 超过了 7 层 我不知如何处理 因为下面的公式要引用语 数 英 等 答 新建一表 取名 Data 找一区域设置名称为 SubjectTable 语文 Chinese 英语 English vlookup SubjectTable a1 2 false 可以有 65536 个 够了吧 其实 稍加改进 理 论上 可以有达到你硬盘空间的个数 或用 if 和 or 的组合可以解决 15 个 再举个例子 IF A16 IF B16 样办尚未交 IF OR B16 内部检查中 B16 数据查询中 B16 数据查询中 CONCATENATE IF B16 内部检查中 品质检测中 IF B16 数 第 5 页 共 13 页 据查询中 图纸未确认 IF B16 为不合格 需要修正 CONCATENATE IF C16 客户检查中 待客回复 IF C16 合格 待 P O 生产 IF C16 取消 客户取消 IF C16 为客户设变中 客户设变中 IF C16 不 合格 需要修正 从个人所得税的计算谈从个人所得税的计算谈 IFIF 函数的使用函数的使用 最近 国家采取提高公务员和职工的工资水平 低收入阶层的收入的政策以启动消费 推动经济增长 相当一部分人工资收入达到和超过了交纳个人所得税的水平 根据我国税 法的规定 个人所得税是采用超额累进税率分段计算 见表一 级数应纳税所得额税率 速算扣除数 1 不超过 500 元的 50 2 超过 500 元 2000 元的部分 1025 3 超过 2000 元 5000 元的部分 15125 4 超过 5000 元 20000 元的部分 20375 5 超过 20000 元 40000 元的部分 251375 6 超过 40000 元 60000 元的部分 303375 7 超过 60000 元 80000 元的部分 356375 8 超过 80000 元 100000 元的部分 4010375 9 超过 100000 元的部分 4515375 这时 用 Excel 的 IF 函数来进行计算是再方便不过的了 对原工资表只要增加 应纳 税所得额 和 应交个人所得税 两列就可以 应纳税所得额 根据 应发工资 数扣 除 800 元和其他可免税的部分计算 这一列也可省略 应交个人所得税 设在工资表 的应扣金额部分 将其象 代扣房租 等等项目一样作为工资表中的一个扣除项目 计算 个人所得税的公式就设置在此列 Excel 中的逻辑函数 IF 一个适用范围很广 功能极强的函数 IF 函数在工作表中的用 途是用于对数值和公式进行条件检测 然后根据不同的检测结果 返回不同的结果 执行 不同的操作命令 它的这种功能用来判断应纳税所得额的适用税率再合适不过 第 6 页 共 13 页 理解理解 IFIF 函数在计算个人所得税中的用法函数在计算个人所得税中的用法 IF 函数的语法 IF 参数 1 参数 2 参数 3 该函数的含义是在单元格中以参数 1 为条件进行检测 当检测结果符合参数 1 时 执 行参数 2 的命令 反之则执行参数 3 的命令 其中 参数 1 为函数执行检测的条件 它一 般是一个公式或一个数值表达式 参数 2 和参数 3 可以是显示一个字符串 显示一个数值 或显示某一公式的计算结果 参数之间用半角的 隔开 字符串也要用半角引号括住 设工资表中 应纳税所得额 在 E 列 应交个人所得税 在 H 列 我们要在 H 列的 各行设置 IF 函数公式 由函数公式来对 E 列各行的应纳税工资进行判断 并自动套用适用 税率和速算扣除数计算应纳税额 可在 H 列设置函数 以第 2 行为例 IF E2 100000 E2 0 45 15375 IF E2 80000 E2 0 40 10375 IF E2 60000 E2 0 35 6375 IF E2 40000 E2 0 30 3375 IF E2 20000 E2 0 25 1375 IF E2 5000 E2 0 20 375 IF E2 2000 E2 0 15 125 IF E2 500 E2 0 10 25 E2 0 05 该函数十分亢长 其中嵌套了 7 个同样的 IF 函数 从第 2 个 IF 函数开始到最后是第 1 个嵌套函数 从第 3 个 IF 开始到最后是第 2 个嵌套函数 为帮助理解 我们将这些 嵌套函数分别设为 X1 X2 于是将整个函数简化如下 IF E2 100000 E2 0 45 15375 X1 该函数意为 当 E2 中工资额大于 100000 元时 H2 中计算出的应纳所得税额为 E2 45 15375 否则 指当工资额等于或小于 100000 元时 则 H2 应按 X1 的方法计算 把 X1 展开 IF E2 80000 E2 0 40 10375 X2 其含义与上面相仿 最后一个嵌套函数 X7 展开为 IF E2 500 E2 0 10 25 E2 0 05 该函数意为 当 E2 中工资额大于 500 元时 H2 中计算出的应纳所得税额为 E2 10 25 否则 当工资额等于或小于 500 元时 H2 等于 E2 5 怎么样 明白了吗 够复杂的 好在计算个人所得税只有一种规定 将别人的公式搬 过来用就是了 一般单位个人的工资不会高的太吓人 我们可根据人员的实际工资水平减 少函数的嵌套数目简化运算 学会灵活使用学会灵活使用 IFIF 函数函数 第 7 页 共 13 页 从以上计算个人所得税的例子中可以看出 学会在 Excel 的工作表中运用这个函数设 定各种条件 可实现许多特定的操作要求 起到事半功倍的作用 本文再举几个实例来讲 解 IF 函数的使用方法 读者们可举一反三 在自己的 Excel 工作表中灵活运用 例一 图一 图一是一份会计记帐凭证 为了判断输入的数据借贷是否相等 我们在 A8 中利用 IF 函数进行检测 选定 A8 双击 输入函数 IF C8 D8 借贷不平 合 计 当 C8 D8 时 不符合参数 1 的条件 在 A8 中即显示参数 3 中的字符串 合计 当 C8 D8 时 符合参数 1 的条件 A8 中显示的就是参数 2 中的字符串 借贷不平 给予 提示 见图二 图二 例二 第 8 页 共 13 页 图三 在图三的表中 要求当 C5 C12 区域中的数值为正数时 在 D5 D12 区域中用相同的 正数反映 当 C5 C12 区域中的数值为负数时 则要在 E5 E12 区域中用它们的绝对值反 映 在 D5 中建立函数如下 IF C5 0 C5 0 在 E5 中建立函数如下 IF C5 0 C5 1 0 再将已建立的函数复制到其他相应的单元格中去即可 图四 如果在单元格中不想将 0 显示出来 则第三个参数可表示为 如图四单 元格 E5 所示 例三 图五 在图五表中如果只要求计算盈利企业的利税总额则可在 F 列各单元格中建立函数如下 以 F2 为例 IF B2 0 SUM B2 E2 当 IF 函数的参数 1 中含有两个或两个以上的检测条件时 就要在参数中嵌套另外两个 逻辑函数 AND 和 OR 了 例四 第 9 页 共 13 页 设单元格 A4 的数值只能严格为正小数 当 A4 符合此条件时 则在 B4 中显示该数值 否则提示错误信息 从 A4 的条件看它应同时满足 0 且 1 的要求 因而应结合运用 AND 函数 在 B4 中建立函数如下 IF AND 0 A4 A4 1 A4 数值超出范围 例五 设单元格 A4 的数值应该是绝对值大于 10 的任何数值 当 A4 符合此条件时 则在 B4 中显示该数值 否则提示错误信息 这里 A4 的条件既可 10 又可 10 两个条件只要 满足一个即可 因而应结合 OR 函数 在 B4 中建立函数如下 IF OR 10 A4 A4 10 A4 数值超出范围 从以上例子可以看出 IF 函数是一个十分有用的函数 要用 Excel 不可不学 IF 函数 本文所举例子并不能包括它的所有用法 相信大家一定能发现 IF 函数的更多功能 原载 CPCW 网站 例 1 发奖金 姓名销售额奖金 10000 以内的提 1 王五 512451 24 20000 以内的提 1 2 赵六 25135402 16 30000 以内的提 1 4 黑七 215421 54 40000 以内的提 1 6 白云 21354341 664 50000 以内的提 1 8 红枣 45111811 998 50000 以上的提 2 啊好 85135217027 04 公式 IF C4 10000 C4 0 01 IF C4 20000 C4 0 012 IF C4 3000 C4 0 014 IF C4 40000 C4 0 016 IF C450000 C4 0 02 例 2 取款 654321 卡资料请插入磁卡 卡号 654321 卡号 654321654321 密码 123456 请输入密码 余额1000 元密码 123456123456 请输入您要支取的 数额 金额 50005000 第 10 页 共 13 页 取款机反 应 对不起你的卡上金额不足对不起你的卡上金额不足 请重输 请重输 公式 IF E16 请插入磁卡 IF E18 输入密码 IF OR E16B16 B17E18 您的磁 卡无效 或密码不正确 请重新输入 IF E20 请输入金额 IF E20 B18 对不起你的卡 上金额不足 请重输 IF INT E20 50 50E20 对不起 您输入的金额应是 50 的倍数 您的申请成功 系统正在操作中 请稍候 大凡所有的程序都是从这些最基础的判断做起的 例 2 模仿了取款机用户取款的反应过程 实际的程序比这复杂的多 但原理是一样的 看起来公式很长 但是它是由几个很简单的 IF 函数嵌套而成的 实际的应用当中有很多是比这个复杂的 我们看一这一段公式 IF E16 请插入磁卡 IF E18 输入密码 p 里面是 两个 IF 的嵌套 第一个 if 条件 E16 成立执行第一个分支 请插入磁卡 就不管后面 的了 因为对于第一个 IF 来说后面的公式只是其中的一个参数 同理 条件 E16 不成 立 excel 就不理第一个分支了 就会直接跳到第二个分支了 IF E18 输入密码 碰到第二个 if 时 又对条件 E18 进行判断 条件 E18 成立执行它的第一个分 支 输入密码 不成立又会跳过第一个分支 输入密码 执行后面的公式 以此类推 再示例 if a2 700 700 if a2 600 600 699 if a2 500 500 599 if a2 400 400 499 if a2 300 300 399 if a2 200 200 299 if a2 100 100 199 D8 D14 E8 E14 G8 G14 MIN X X C8 C14 C8 C14 MAX X C8 C14 E8 E14 此公式较长 实用性不大 只在公式栏按 F9 即可转为常量数组公式 第二种 速算扣除数法 可能有人会问 速算扣除数是怎么计算得出的 在些我们先简单介绍一下速算扣除数的由来 速算扣除数实际上是在级距和税率不变条件下 全额累进税率的应纳税额比超额累进税率的应 纳税额多纳的一个常数 因此 在超额累进税率条件下 用全额累进的计税方法 只要减掉这个常数 就等于用超额累 进方法计算的应纳税额 故称速算扣除数 速算扣除数计算公式推导过程 第 1 级速算扣除数等于 0 因为此处的全额累进税等于超额累进税 第 2 级速算扣除数 4500 按全额累计计算 4500 10 但 1500 部分已经交纳 3 的税 因此多 交纳的税为 1500 10 3 第 3 级速算扣除数 4900 按全额累进计算 4900 20 但 1500 已按 3 计算 3000 已经按 10 交税 多交的税为 1500 20 3 3000 20 10 4500 20 1500 3 3000 10 4500 20 10 1500 10 3 依此类推 但出速算扣除数公式如下 本级速算扣除额 上一级最高所得额上一级最高所得额 本级税率 上一级税率 上一级速算扣除数 那么理解了上述原理后 公式 4900 20 555 运用速算扣除数计算税的公式特别多 如 IF 函数法 Vlookup 辅助列方法等 对应 EXCEL 公式 此类公式直观 易理解 所以不做解释 此处仅解释比较难理解的一个公式 Max 函数法 MAX X 0 01 3 10 20 25 30 35 45 5 0 21 111 201 551 1101 2701 0 MAX X 0 01 3 10 20 25 30 35 45 5 0 21 111 201 551 1101 2701 0 我们知道个税的起征点为 3500 因此 X 少于等于 3500 时 不需交税 故要用得出的数值与 0 进行比较 为什么根据各级税率减速算扣除数的最大值为应交税呢 分解公式的结构 第一部分即 X 0 01 3 10 20 25 30 35 45 为全额累进计算的税 第二部分 5 0 21 111 201 551 1101 2701 为速算扣除数 即全额累计计算所得税 超额累计 应纳税 那么为什么最大值就是正确的税呢 原因就在于减去重复计算值得时候 公式直接按照重复的 区间计算减去值 减去的比正常减去的多 第 12 页 共 13 页 所以高税率计算出的值是一个错误值 所以 该公式计算得到的数组中最大值只有一个 等于合适税率下计算的答案 而比这个值小 的数显然都不是正确答案 以上述 X 为例子说明 MAX X 0 01 3 10 20 25 30 35 45 5 0 21 111 201 551 1101 2701 0 MAX 147 385 425 220 1285 3790 11300 0 4900 按高税率 20 计算时 多计算的税应当是 1500 3 3000 10 400 20 555 而运用速算扣除数时 是按高一区间的速算扣除数 多计算的税 计算的 上述公式中 5 111 计算的 所以按高税率计算时 减去多计算的税时 速算扣除数 比正常减去的要多 所以 公式中的最大值才是正确结果 不知道理解了没有 有点绕啊 呵呵 不知道理解了没有 有点绕啊 呵呵 第三种方法 全额累进减多算的税 这实际上是根据计算速算扣除数的原理来计算税 如果 4900 按 20 计算 那么对应的 1500 由于已经按 1