Excel函数×函数组合应用范例

目 录

第 1 章 基本操作

1.1 创建与修改公式··············································································2

1.1.1 创建公式················································································ 2

1.1.2 修改公式················································································3

1.2 复制与隐藏公式··············································································4

1.2.1 复制公式················································································ 4

1.2.2 隐藏公式················································································ 6

1.3 单元格的引用···············································································7

1.3.1 相对引用················································································ 7

1.3.2 绝对引用················································································ 8

1.3.3 混合引用················································································ 9

1.4 应用单个函数···············································································11

1.4.1 初识函数················································································11

1.4.2 插入函数················································································12

1.5 应用多个函数···············································································15

1.5.1 嵌套函数················································································15

1.5.2 组合应用函数··············································································18

1.6 公式错误的解析··············································································19

1.6.1 错误值的含义··············································································19

1.6.2 应用错误检查选项············································································20

1.6.3 公式审核················································································21

第 2 章 输入与条件格式

001 限定输入指定范围的日期·······································································24

应用 限定输入今年元旦至今日的日期 （TODAY + 数据有效性）

002 限定输入文本长度且以特定字符开头···················································27

应用 限定产品编号以“D”开头且共4位 （LEFT + LEN + AND + 数据有效性）

003 避免一列中输入重复数据·······································································30

应用 在员工信息表中输入重复工号时弹出警告 （COUNTIF + 数据有效性）

004 生成动态下拉列表进行输入···································································33

应用 输入部门名称时通过下拉列表选择部门 （INDIRECT + 数据有效性）

005 提示输入内容且输入正确时自动计算···················································36

应用 提示输入数量并自动计算总额 （IF + AND + ISNONTEXT）

006 给奇数、偶数行设置不同的格式···························································39

应用 让表格内容蓝白相间显示 （MOD + ROW + 条件格式）

007 设置工作表中非空单元格格式·······························································42

应用 制作成本表后仅显示表格区域 （条件公式 + 条件格式）

008 屏蔽错误值················································································45

应用 将错误值显示为空白 （ISERROR + 条件格式）

009 比较两个工作表检查输入错误·······························································48

应用 检查在两个工作表中输入的公司名称是否相同 （EXACT + IF）

拓展 常用同类函数···············································································51

第 3 章 统计分析

010 计算多个汇总值的总和···································································

在日常办公中，单个Excel函数的功能有限，往往需要组合运用各种函数才能真正解决问题。《新世纪书局：Excel函数×函数组合应用范例（附配套光盘）》以经典范例的形式讲解Excel函数的组合应用技巧。全书共分为7章，第1章从Excel函数与公式的基础知识入手，让读者快速了解函数的用法；2-6章通过83个实例介绍了组合函数在数据分析处理、日期与时间、数据检索、字符串操作等方面的应用技巧；第7章为综合实例，以此巩固并实践前面各章学到的组合函数应用技巧。
　　《新世纪书局：Excel函数×函数组合应用范例（附配套光盘）》以实例+图解的形式展示了组合函数的强大功能，非常实用，又简单易学。同时，在随书光盘中提供了书中实例的素材文件与最终文件，方便读者进行实操演练。

《新世纪书局：Excel函数×函数组合应用范例（附配套光盘）》主要针对有一定的Excel操作基础，同时需要利用多个函数解决实际工作问题的读者，可以作为常备的参考工具书。