疑难千寻千解丛书:Excel 2013 函数与公式

编辑:擂鼓网互动百科 时间:2020-02-28 09:39:07
编辑 锁定
《Excel 2013 函数与公式》是“疑难千寻千解”丛书之一,为读者展示运用函数与公式解决疑难问题的实战技巧,包括基础理论、操作技巧、分类函数应用、综合应用及开发实战。《Excel 2013 函数与公式》将系统的知识点融入于各种实战案例,在解决问题的同时,引导读者学习深层次原理和扩展应用思路。阅读《Excel 2013 函数与公式》,可以直接使用成熟的通用公式和案例,解决工作中的诸多疑难杂症,大大提升工作效率,且有助于提升函数与公式的理论水平和综合实战能力。
书    名
疑难千寻千解丛书:Excel2013 函数与公式
作    者
黄朝阳 主编  陈国良荣胜军编著
ISBN
978-7-121-26441-2
页    数
564页
定    价
89.00元
出版社
电子工业出版社
出版时间
2015年7月
装    帧
平装
开    本
16

疑难千寻千解丛书:Excel 2013 函数与公式内容提要

编辑
《Excel 2013 函数与公式》是“疑难千寻千解”丛书之一,为读者展示运用函数与公式解决疑难问题的实战技巧,包括基础理论、操作技巧、分类函数应用、综合应用及开发实战。《Excel 2013 函数与公式》将系统的知识点融入于各种实战案例,在解决问题的同时,引导读者学习深层次原理和扩展应用思路。阅读《Excel 2013 函数与公式》,可以直接使用成熟的通用公式和案例,解决工作中的诸多疑难杂症,大大提升工作效率,且有助于提升函数与公式的理论水平和综合实战能力。
《Excel 2013 函数与公式》在函数语法描述中,采用中文表述习惯,对Excel 2013 帮助文件进行了提炼和修正,帮助读者在理解函数时少走弯路。
《Excel 2013 函数与公式》包括311 个案例,最后一章是职工信息、公文管理、工资管理系统3 个大型案例。案例采取疑难描述、解决方案、操作方法、原理分析、知识扩展5 个方面进行讲解,力图解决问题之外还让读者可以通晓其思路和原理。

疑难千寻千解丛书:Excel 2013 函数与公式作者简介

编辑
黄朝阳,网名apolloh,ExcelTip.net站长,微软全球最有价值专家(MVP)。拥有十多年丰富的企业信息化实践和管理经验,精通Excel和MS SQL在企业中的应用。早期曾在多个Office技术社区担任版主,参与过多部Excel畅销书的编著工作。
陈国良,网名gouweicao78,微软全球最有价值专家(MVP),ExcelTip.net技术社区总版主,多个Office技术社区资深版主,从事公路、铁路工程建设管理十多年,精通Excel函数与公式,具有丰富的实战经验,参与过多部Excel畅销书的编著工作。
荣胜军,网名rongjun,微软全球最有价值专家(MVP),ExcelTip.net技术社区总版主,网络工程师,长期从事建筑工程造价、工程管理工作,精通Excel函数与公式,具有丰富的实战经验,致力于专研Excel应用技术,提高工作效率。

疑难千寻千解丛书:Excel 2013 函数与公式目录

编辑
第 1 章公式基础知识··················································1
1.1 熟悉 Excel 的工作环境····················································1
疑难 1 如何减小功能区所占屏幕空间·········································1
Excel 2013 操作界面概览···································2
疑难 2 如何不用设置公式实现统计结果预览····································3
设置 Excel 2013 状态栏··········································3
疑难 3 如何在一个工作簿中管理多张表格·······························4
理解工作簿、工作表和单元格之间的关系※························5
疑难 4 为什么表格列标题显示的是数字而不是字母·····························5
设置 R1C1 引用样式············································6
A1 引用样式和 R1C1 引用样式····································6
疑难 5 如何根据开头字母的提示快速输入函数名称·········6
开启“公式记忆式键入”功能的两种方法·······················7
疑难 6 如何不让函数提示信息遮盖到工作表的列号·······················7
函数屏幕提示工具的妙用·····································8
疑难 7 为什么公式计算结果不会变化·······································9
设置自动或手动计算模式············································9
疑难 8 为什么单元格中只显示公式文本而不显示计算结果··············10
检查“显示公式”选项状态·······································10
检查单元格是否为“文本”格式························10
疑难 9 单元格左上角的绿色小三角图形是什么含义·················11
错误检查规则及含义※·················································12
使用错误检查巧换文本形式的数字为数值·······························13
疑难 10 如何在新输入的行中自动填充上一行的公式··························13
设置自动扩展公式功能································13
1.2 公式基础概念···········································14
疑难 11 为什么此“公式”不能根据圆的直径求面积····························14
什么是公式····································15
什么是函数·················································15
公式的组成结构················································15
疑难 12 为什么 IF 函数只有两个参数也能计算···················16
必需参数与可选参数·············································16
省略参数与省略参数的值·································16
疑难 13 为什么上百万的网友算错数学题 6÷2 × (1+2)·····················17
公式中运算符的类型及含义···································18
公式中运算符的计算优先级·····································18
疑难 14 为什么开奖号末位判断总是“大”······································19
Excel 的数据类型※··············································20
数据排列顺序的规则·······································20
公式中文本形式数字如何转换为数值······················20
疑难 15 为什么数字与“空”单元格相加会出错·························20
空文本与空单元格的区别································21
公式中的&""有什么作·······················22
疑难 16 为什么两个时间相减得到一长串的#号····················22
产生一长串#号的原因分析及解决方法·································23
疑难 17 如何输入编号 1-2、1/2 以及比分 1:3 ·······························23
日期、时间数据的表示方法·······························24
打开“设置单元格格式”对话框的 3 种方法····························24
疑难 18 如何快速规范以小数点间隔的伪日期····························25
查找替换法规范“伪日期”数据·····································25
数据分列法规范“伪日期”数据·····························26
根据使用习惯改变默认日期与时间格式·····························26
Excel 中两位数字的年份··············································27
疑难 19 如何拯救小数表示的伪出生年月·······························28
用剪贴板规范含有两位小数的伪日期数据································28
疑难 20 如何实现输入数字 1、2 后自动将其显示为性别·················30
为单元格自定义数字格式·······································30
Excel 的数字格式种类············································31
常用数字格式代码组成部分······································31
疑难 21 如何让单位为元的金额显示为万元····························32
数字格式代码及含义············································33
日期相关格式代码·····································34
时间相关格式代码········································34
数字格式代码中的日历和语言区域······································34
疑难 22 如何让累计求和引用的区域随公式向下复制变化·······················38
相对引用、绝对引用、混合引用····································39
疑难 23 不同表格相同表头能否只填一处··································40
跨表引用单元格的表示方法······························41
疑难 24 为什么身份证号码 15 位以后的数字都显示为 0 ···············42
Excel 的计算限制············································43
疑难 25 为什么有时候汇总金额会比实际差 1 分钱····················44
浮点运算导致意外计算误差······························45
疑难 26 为什么即使没有修改内容关闭表格时也会提示保存文件·········45
哪些函数具有易失性···············································46
增加“撤销”次数····················································46
疑难 27 为什么在 Excel 2003 中打开公式会显示_xlfn. 前缀······················47
Excel 2003 不支持的新增函数有哪些·····································48
疑难 28 为什么用 Excel 2013 打开早期版本的文件后只有 65536 行·············48
Excel 2013 支持哪些文件格式··········································50
如何在早期 Excel 版本中打开、另存为高版本文件·······················50
1.3 公式常用操作技巧···············································50
疑难 29 如何选择合适的函数来解决问题·······················51
如何搜索函数·················································51
从分类中选择所需函数···································52
函数的分类················································52
疑难 30 如何快速获取函数的帮助信息··················53
获取函数实时提示信息······································53
函数帮助文件中有哪些信息····························54
疑难 31 如何快速将公式复制到其他单元格·································54
复制粘贴公式方法·······························55
填充柄复制公式法································55
填充命令法复制公式············································56
多个单元格快速输入相同公式································56
疑难 32 如何复制公式且保持相对引用地址不变····················57
用辅助工作表法保持单元格相对引用·································57
疑难 33 如何快速在多个合计行中填充求和公式································59
定位空单元格与批量求和···········································59
筛选可见单元格求和················································60
疑难 34 如何按照合并单元格求对应金额小计··························61
混合引用妙求合并单元格对应数据之和·····················62
计算合并单元格包含多少行···································62
合并与拆分单元格···············································62
疑难 35 如何让合并单元格中的每个单元格都有内容················63
格式备份让“合并”单元格都有内容··························64
格式合并与单元格合并的区别··········································65
疑难 36 如何查看公式分步运算结果以便找出错误··················66
分步查看运算结果·············································66
用【F9】键查看运算结果······························67
疑难 37 如何去掉报表中的公式只保留计算结果···························68
择性粘贴法保留公式计算结果······························68
粘贴数值的 3 种效果··································69
断开链接法去除公式······················69
疑难 38 如何允许填报数据又防止破坏报表中的公式······················70
保护含有公式的单元格·································71
如何隐藏公式···············································72
如何仅可选定需要填报的单元格···············72
疑难 39 如何不切换工作表监控不同区域变化情况··························72
使用监视窗口监控数据·············································73
神奇的单元格“照相机”···································74
疑难 40 如何让新插入的行被原有公式引用···············75
自动计算新插入行数据····························76
1.4 数组与数组公式··················································· 77
疑难 41 如何不用单元格存储个税税率关系表··············77
什么是数组····················································78
数组的维度和尺寸································79
疑难 42 如何根据一组商品的单价与数量直接求总金额············79
数组多项运算原理··································79
疑难 43 为什么运行公式后大括号{}消失而计算结果出错·············80
什么是数组公式··········································81
如何在合并单元格输入数组公式·····················81
为何建议不使用整列单元格创建数组公式····81
疑难 44 如何判断多项运算是否需要使用数组公式·····························82
图解数组公式与普通公式运算的差异·························82
【Ctrl+Shift+Enter】组合键对于数组公式的意义··············82
疑难 45 为什么会出现“不能更改数组的某一部分”的提示···········83
输入多单元格数组公式································84
为何使用多单元格数组公式··························84
疑难 46 为什么相同公式在不同位置的计算结果不同·························85
什么是绝对交集引用·············································85
数组的绝对交集···················································86
如何判别公式是否生成内存数组····························86
疑难 47 为什么销售业绩与提成点数相乘出现#N/A 错误·······················87
数组间多项运算与数组维度、尺寸的关系·····················88
疑难 48 为什么 AND、OR 函数不能返回多项逻辑判断结果·····················89
多项运算需要逻辑判断返回数组结果······························90
疑难 49 如何快速取得自然数等差数列·····························90
ROW 函数返回行号作为等差数列·······················91
ROWS 函数与 ROW 函数的区别······························91
获取数字 0~9 数列····················································92
疑难 50 如何汇总连续多表相同单元格的销售额················92
什么是连续多表三维引用··············································93
疑难 51 为什么 INDIRECT 函数引用多表相同单元格求和出错··················94
什么是函数产生的多维引用·······························94
为何使用 N 函数计算结果不一定正确························95
函数产生多维引用模型图解································95
1.5 在公式中使用名称···············································96
疑难 52 如何用汉字“税率”代替常量数组·························96
为什么要使用名称·············································97
疑难 53 为什么字母 C 不能作为名称使用·································97
名称的命名原则·············································98
疑难 54 如何快速将单元格区域定义为多个名称·························98
使用名称框快速定义名称·······························99
使用“以选定区域创建名称”功能批量定义名称················99
疑难 55 如何在修改名称中的引用位置时使用方向键··················100
切换编辑框中的点选与编辑模式······················100
疑难 56 如何在不同工作表定义表示不同数据的相同名称·····················100
定义工作表级名称················································101
工作簿级、工作表级名称·····························102
疑难 57 如何在名称中实现工作表标签“相对引用”····················102
名称中工作表的“相对引用”····················103
疑难 58 如何直接使用表格标题来引用数据························104
创建“表格”区域·····················································104
什么是“表格”功能··················································105
什么是“结构化引用”·················································105
疑难 59 如何让图片随选择的生肖自动变化····························106
使用名称动态引用图片················································106
疑难 60 如何让打印区域随数据输入自动扩展····························107
使用名称设置动态打印区域·····································108
疑难 61 如何在单元格中设置打印顶端标题行和左端标题列·················109
使用名称在单元格中设置打印标题行和列······························109
疑难 62 如何快速删除多个错误名称······························111
使用名称管理器筛选错误名称·····································111
疑难 63 如何让名称隐藏起来·································112
使用 VBA 隐藏名称·································112
1.6 练习与思考········································113
第 2 章逻辑判断··································114
2.1 逻辑关系···············································114
疑难 64 如何判断串联、并联、短路、双控电路的连通状态·························114
使用 IF 函数进行逻辑判断···········································115
与、或、非、异或 4 种逻辑关系·························115
疑难 65 如何判断购房是否需要提供个税或社保证明························116
逻辑非关系的三种判断方法········································117
疑难 66 如何判断同时满足购买经济适用住房的 3 个条件··················117
逻辑值与数值转换规则········································118
疑难 67 如何根据职工性别和职务判断退休年龄························118
使用四则运算代替逻辑判断············································119
2.2 多层判断···································119
疑难 68 如何根据成绩判断优良中差等级·································119
多层 IF 函数嵌套的逻辑关系树···················120
避免逻辑关系重复、遗漏、冗余·························120
疑难 69 如何给金卡和银卡客户按消费额派发赠品·····························120
不同分支多层逻辑判断············································121
多层级对应关系逻辑判断问题变通处理··························122
2.3 常用条件设置··············································· 123
疑难 70 如何将公式返回的错误值转为其他值·····················123
IS 类函数判断屏蔽错误值通用公式···································124
IFERROR、IFNA 函数屏蔽错误值通用公式······························124
常见 7 种错误的产生原因及解决方案··························125
疑难 71 如何判断必填字段填写是否完整····································127
判断真空单元格····································127
疑难 72 如何判断单元格内是否有公式并标识颜色····················128
使用 ISFORMULA 函数判断单元格是否为公式···················129
使用宏表函数 GET.CELL 函数判断单元格是否为公式············129
疑难 73 如何判断某年是否为闰年·······························130
判断闰年的 4 种解法··········································131
疑难 74 如何判断开奖号码之和的奇偶性···························131
数字奇偶性判断··············································132
疑难 75 如何根据身份证号码判断性别··································133
取得身份证号中的性别数字······································133
判断奇偶的函数限制···········································134
疑难 76 判断数据是否存在重复现象··········································134
区分大小写判断重复数据·····································135
疑难 77 如何判断一个字符是否汉字···························136
双字节法判断字符是否为汉字··································136
与“吖”字比较判断字符是否为汉字································136
Unicode 函数判断字符是否为汉字····································137
2.4 练习与思考··········································137
第 3 章数据汇总·····················································138
3.1 汇总求和···········································138
疑难 78 如何汇总连续多个表中相同单元格区域的数据···················138
通配符在输入连续多表三维引用中的应用····························139
疑难 79 如何汇总茶叶中铁观音的销量···························139
SUMIF 函数单条件求和··································140
SUMIF 函数第 3 参数的简写形式··································140
在 SUMIF 函数中使用通配符和数组····························141
疑难 80 如何汇总月薪在 2000 至 4000 元的工资总额························141
SUMIFS 单列多条件求和···························141
其他单列多条件求和解法····································142
SUMIFS 与 SUMIF 函数的区别····························142
疑难 81 如何根据考评系数及评分计算员工的综合得分·······················142
SUMPRODUCT 求数组乘积之和······································143
SUM 数组公式与 SUMPRODUCT 求数组乘积之和的区别···············143
疑难 82 如何避开单元格中的错误值求和······························144
使用 SUMIF 函数排错求和············································144
疑难 83 如何验算现金流量表的数据勾稽关系是否正确··············144
使用通配符进行条件求和········································145
疑难 84 如何求出某月各项支出中最大值之和·····························146
数据库函数中使用数组求多列最大值································146
疑难 85 如何忽略隐藏的行汇总数据······································147
SUBTOTAL 函数对应功能及忽略不可见单元格特性······················147
疑难 86 如何忽略隐藏的列汇总数据···································148
忽略隐藏列求和··············································149
疑难 87 如何汇总固定间隔 n 行的数据··································149
MOD 函数构建间隔 n 行的循环················150
间隔 n 行数据求和通用公式································150
疑难 88 如何汇总某月数据······························150
按月汇总数据 ? ·······································151
疑难 89 如何汇总本科学历男员工的工资总和································151
SUMIFS 函数多条件求和通用公式···················152
SUMPRODUCT 或 SUM 函数多条件求和通用公式·····················152
疑难 90 如何按月分别汇总每个销售员的销售额······························153
MMULT 函数双条件求和··········································153
疑难 91 如何根据代码等式求对应数值之和·······················154
根据代码等式对应求和····························155
疑难 92 如何根据多个条件跨表汇总销售数据·······················155
SUMIFS 函数多表多条件求和·····································156
3.2 数据计数························································· 157
疑难 93 如何统计成绩表中的及格人数·················157
COUNTIF 函数常见条件统计用法·················158
常用特殊条件计数············································159
疑难 94 如何统计 35 岁以上有房有车的职工人数·················159
COUNTIFS 函数多条件计数······························159
SUMPRODUCT 或 SUM 函数多条件计数通用公式··························160
COUNT 函数多条件计数通用公式·····························161
疑难 95 如何统计甲车间生产的产品规格带*号的产品种类···················161
包含通配符的条件计数··································162
疑难 96 如何统计不重复数据个数·········································162
1/COUNTIF 函数统计不重复值的优缺点······························163
MATCH=ROW 法统计不重复值个数的优缺点······················164
FREQUENCY 函数统计不重复值的优缺点·······················165
疑难 97 如何区分字母大小写统计不重复数据个数····················165
用 1/MMULT 法求不重复值············································166
3.3 极值均值································· 166
疑难 98 如何设置提成奖金的上、下限·····································166
使用 MAX、MIN 函数设置上、下限通用公式···················167
使用 MEDIAN 函数设置上、下限通用公式·························167
使用 IF、TEXT 函数设置上、下限通用公式····················168
疑难 99 如何按先进先出法进行商品库龄分析························168
混合引用与上下限解决先进先出问题通用公式····························170
疑难 100 如何标记体检指标与参考范围的高低关系·····················171
中值法判断数值与上、下限范围的关系·······················171
复数函数法判断数值与上、下限范围的关系··························172
[1] 
参考资料
词条标签:
文化 出版物