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

《Office 2010图解应用大全》张玉良，中国人民大学新闻学院毕业，具有丰富的出版经验，工作期间也一直在提供各种Excel技术咨询服务和培训，并在各大论坛为网友解答办公软件困惑。2010年下半年创办知行时代（北京）文化传播有限公司，从事图书的策划与编写。近期出版图书：《Office办公秘技360招》、《这么玩Excel最给力》、《Office 2010图解应用大全》均有不错的销量

目 录

第 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 计算多个汇总值的总和···································································2100433B

这书的基础是一本台湾引进的日本书，在台湾排在网络销售的前列，找到这本书时，大约排在第12位。本书主要针对有一定的Excel操作基础，同时需要利用多个函数解决实际工作问题的读者，可以作为常备的参考工具书

详细说明函数的基本概念、单元格与名称定义、函数与公式建立，详细介绍每个函数的参数用法、数据类型与限制，学习对话框与窗体的制作，建立更为弹性的运算模式，范例导向精辟解说如何使用函数式宏建立“自定义函数”，每章提供函数速查表，方便读者查询，快速上手。汇集334个工作表函数，223个实用案例，完全适用Excel2007。

《Excel函数范例完整学习应用大全》针对函数中的自变量，与其所需的数据类型与限制详加说明。同时附有范例光盘提供完整的操作范例文件，使读者在阅读函数后马上可以在计算机上得到验证。

《Excel函数范例完整学习应用大全》共收录Excel中339个常用的工作表函数，共分14章，第、章主要介绍Excel。

本书以技巧的形式，介绍如何快速将自己打造成Excel公式与函数应用高手。全书共10章，分别介绍公式编辑与数据源引用技巧、逻辑函数范例应用技巧、日期函数范例应用技巧、数学函数范例应用技巧、文本函数范例应用技巧、统计函数范例应用技巧、财务函数范例应用技巧、查找和引用函数范例应用技巧、数据库函数范例应用技巧，以及函数返回错误值的解决方法等方面的内容。

本书有很强的实用性和可操作性，非常适合经常使用Excel函数的读者随时查阅。