Excel 2007函数与公式实战技巧精粹PDF|Epub|txt|kindle电子书版本下载

Excel 2007函数与公式实战技巧精粹PDF格式电子书版下载

注意：本站所有压缩包均有解压码： 点击下载压缩包解压工具

绪论 最佳Excel学习方法1

1成为Excel高手的捷径1

1.1积极的心态1

1.2正确的学习方法2

1.3善用资源，学以致用4

1.4多阅读，多实践5

2通过互联网获取学习资源和解题方法5

2.1搜索引擎的使用6

2.2搜索业内网站的内容7

2.3在新闻组或BBS中学习7

2.4在视频网站中学习10

2.5利用微博和微信学习10

3函数与公式学习指南11

3.1打好函数基础11

3.2由浅入深，由简至繁学好函数公式11

3.3学好函数公式的基本方法11

3.4厚积薄发，脱胎换骨12

3.5 学以致用，自由驰骋12

第一篇 函数导读13

第1章 函数与公式基础介绍14

技巧1什么是公式14

技巧2快速掌握运算符14

2.1运算符的类型及用途14

2.2公式的运算顺序15

技巧3透视“单元格引用”16

3.1 A1引用16

3.2 R1C1引用17

3.3引用运算符17

3.4相对引用18

3.5 绝对引用19

技巧4灵活处理跨工作表、工作簿引用20

4.1跨表引用20

4.2跨工作簿引用21

技巧5公式的快速批量复制21

技巧6公式的自动扩展22

技巧7公式中的结构化引用23

7.1直观的结构化引用23

7.2自动扩展25

技巧8慧眼识函数26

技巧9函数的类型27

技巧10 Excel2007函数的新特性29

技巧11函数参数的省略与简写29

技巧12使用通配符进行模糊匹配30

技巧13通配符在多表引用中的妙用32

技巧14认识公式中的数据33

14.1数据类型33

14.2数据排序规则33

14.3常量数据33

技巧15文本型数字转换为数值34

15.1利用查错工具批量转换34

15.2文本型数字转数值的公式方法35

技巧16逻辑值与数值的互换36

16.1逻辑值与数值互换准则36

16.2用数学运算替代逻辑函数37

技巧17正确区分空文本与空单元格38

17.1空单元格与空文本的差异38

17.2防止空单元格影响统计结果39

17.3让空单元格不显示为039

技巧18计算规范与限制40

18.1数字计算精度限制40

18.2函数嵌套层数限制41

18.3函数参数个数的限制41

技巧19以显示的精度进行计算42

技巧20易失性函数43

20.1什么是易失性43

20.2具有易失性表现的函数43

20.3易失性的利用与规避44

技巧21自动重算和手动重算44

技巧22循环引用和迭代计算45

22.1 For.Next循环46

22.2 Do .While循环48

22.3记录操作时间48

22.4已知和值求组合49

技巧23自动完成公式50

技巧24妙用函数提示51

24.1提示函数语法51

24.2参数含义提示52

24.3快速选定相应参数52

24.4获取函数帮助信息52

技巧25公式的查错53

25.1公式常见错误类型53

25.2 错误自动检查54

技巧26公式审核和监控55

26.1错误检查55

26.2审核和监控55

技巧27分步查看公式结果56

27.1使用公式审核工具分步求值57

27.2用F9键查看公式运算结果58

技巧28如何阅读和理解复杂公式58

第2章 数组公式入门62

技巧29神奇的数组62

29.1数组的概念及分类62

29.2数组的维度和尺寸63

技巧30多项计算和数组公式63

30.1多项计算63

30.2数组公式64

技巧31多单元格数组公式65

技巧32数组的直接运算66

32.1数组与单值直接运算66

32.2同方向一维数组之间的直接运算66

32.3不同方向一维数组之间的直接运算67

32.4一维数组与二维数组之间的直接运算67

32.5二维数组之间的直接运算68

技巧33数组公式中的逻辑运算69

第3章 灵活使用名称71

技巧34名称的奥秘71

技巧35多种方法定义名称73

35.1使用“定义名称”功能73

35.2使用名称框创建74

35.3根据所选内容批量创建75

技巧36名称的规范与限制76

技巧37通过名称定位区域77

技巧38以图形方式查看名称77

技巧39查看和修改名称78

39.1导出名称列表78

39.2编辑修改名称78

39.3删除名称79

技巧40认识名称的作用范围80

技巧41根据单元格颜色进行统计81

技巧42固定引用区域的名称82

技巧43使用名称动态引用图片83

技巧44在其他功能中使用名称85

44.1在条件格式和有效性中使用他表数据85

44.2在图表中使用名称86

技巧45创建动态引用名称87

技巧46自动更新的打印区域88

第二篇 函数常用技巧91

第4章 逻辑与信息函数92

技巧47逻辑函数的判断功能92

技巧48逻辑值在条件汇总中的妙用93

48.1条件计数93

48.2条件汇总94

48.3多条件逻辑94

技巧49快速判决数据的简单特性95

技巧50从身份证号码中提取性别96

技巧51数据的模糊汇总97

技巧52获取当前操作环境信息97

技巧53捕捉当前活动单元格信息98

技巧54提取工作表和工作簿的名称100

54.1提取工作表名称100

54.2提取工作簿名称101

技巧55当公式报错时返回空白101

55.1旧版美化公式102

55.2新版简化公式102

技巧56 N函数在函数公式中的应用103

56.1 将逻辑值转化为数值103

56.2生成内存数组104

技巧57强制INDEX函数返回内存数组105

第5章 文本处理函数107

技巧58意义非凡的文本连接符107

技巧59处理文本的基本手段108

59.1文本截取108

59.2查找定位109

59.3文本替换110

59.4文本比较112

59.5 文本重复112

技巧60处理字符的基本技巧112

60.1字符和数字代码112

60.2 字母大小写转换113

60.3字符全半角转换113

60.4 删除多余的空格114

60.5 处理非打印字符114

技巧61生成字母序列ABC115

61.1 CHAR+CODE方式115

61.2 列标提取方式116

技巧62生成可换行的文本116

技巧63规整不规范的日期数据118

63.1 MID函数文本截取法119

63.2 TEXT函数方式119

技巧64巧用身份证填写个人信息120

64.1提取性别121

64.2出生日期121

64.3年龄121

技巧65财务数字金额拆分实例122

技巧66分离中文、数字和字母123

66.1分离中文与数字124

66.2字母与中文124

66.3提取数字125

技巧67统计不重复数字个数126

技巧68根据固定间隔符号截取字符串127

68.1 FIND+SUBSTITUTE127

68.2展宽隔离符号127

技巧69根据间隔符统计项目个数128

技巧70格式化设备编号129

技巧71数字格式化为中文数字130

71.1数字整体转换130

71.2逐字符转换131

71.3日期格式131

技巧72转换中文大写金额132

技巧73以小时累计时间132

技巧74合并单元格区域中的文本133

第6章 数学计算函数135

技巧75基本的数学计算函数135

75.1求和与求积135

75.2乘幂与对数136

75.3组合数与阶乘137

75.4 最小公倍数和最大公约数137

75.5进制转换函数138

75.6绝对值和π值138

75.7弧度转换为度数139

75.8符号函数139

技巧76出神入化的舍入函数139

76.1基本取整函数139

76.2指定数字位舍入140

76.3特定基数取整141

76.4舍入至奇数和偶数141

技巧77特定条件的舍入设计142

77.1设计一舍二入方式142

77.2 0.2单位和0.5单位舍入143

技巧78四舍六入五单双143

技巧79指定有效数字144

技巧80驾驭求模函数145

80.1初始求模函数145

80.2判断数字奇偶性146

80.3生成循环序列147

技巧81正整数逐位累加至1位数字148

81.1速算法148

81.2速算扩展149

技巧82数字校验码的应用149

技巧83随机产生数字与字母150

83.1初识随机函数150

83.2随机产生数字与字母150

技巧84生成不重复随机序列151

84.1辅助列方式152

84.2直接方式152

第7章 日期与时间函数153

技巧85认识日期与时间数据153

85.1日期与时间数据的本质153

85.2日期与时间数据的识别153

85.3输入日期时间的注意事项154

技巧86基本的日期函数154

86.1日期生成函数154

86.2年、月、日函数155

86.3当前日期155

86.4星期相关函数155

86.5 工作日相关函数156

技巧87基本的时间函数157

87.1时间生成函数157

87.2时、分、秒158

87.3当前时间158

技巧88处理1900年之前的日期158

技巧89判断闰年和平年159

技巧90 2014年巴西世界杯倒计时160

技巧91快速确定员工的退休日期160

技巧92判断一个日期所在的季度161

技巧93推算所在季度的季末日期162

技巧94计算日期所在季度天数163

技巧95计算指定日期邻近月份的天数163

技巧96动态生日提醒164

技巧97计算公司员工的工龄165

97.1以年为单位165

97.2精确到月份166

技巧98身份证中提取周岁信息167

技巧99计算食品的保质期169

技巧100目标日期是所在年第几天169

100.1点对点相减169

100.2日期值序列170

技巧101计算两个日期相差的年月日数170

技巧102下一个星期日和最近的星期日171

102.1日期值序列思路171

102.2星期几的归零思路172

技巧103指定日期范围内星期几出现的次数172

技巧104推算指定月份第几个星期几的日期173

技巧105日期A从日期B算起是第几周173

技巧106计算指定月份第几周的天数174

技巧107计算两个日期之间的净工作日175

技巧108根据停车时间计算费用177

108.1计费时间177

108.2每小时费用178

108.3总费用178

技巧109将时间转换成标准格式178

技巧110三班倒工作时长计算179

技巧111在时间日期数据中提取时间或日期180

111.1提取日期数据180

111.2提取时间数据180

技巧112生成指定范围的随机时间181

112.1以分为单位随机变化181

112.2以秒为单位随机变化181

第8章 查找与引用函数182

技巧113动态按行或列生成序列号182

技巧114认识INDIRECT函数183

技巧115建立恒定的引用区域183

技巧116轻松调用名称和“表”数据184

技巧117应用INDIRECT函数组合查询185

技巧118实现数据区域的行列转换186

技巧119引用区域的位置变换187

技巧120批量生成工资条187

技巧121现解OFFSET函数188

121.1图解OFFSET参数含义188

121.2参数为数组产生多维引用189

技巧122应用OFFSET函数分析销售业绩190

技巧123按自定义顺序排序191

技巧124根据多个条件查询数据192

技巧125查找不重复值193

技巧126为图书目录自动编号193

技巧127根据首行或首列查找记录194

127.1函数语法和特性194

127.2正向查找195

127.3逆向查找196

技巧128为股票名称编制拼音简码196

技巧129模糊查找符合条件的记录198

技巧130模糊转换数据198

技巧131应用LOOKUP函数实现单条件多值求和199

技巧132多区间判断数值等级200

技巧133乱序查找最后一个满足条件的记录201

技巧134填补合并单元格的空缺202

技巧135多条件逆序筛选202

技巧136多条件顺序筛选203

技巧137根据查找结果建立超链接204

第9章 统计分析函数206

技巧138统计总分前N名的平均成绩206

技巧139 AVERAGEIF和AVERAGEIFS函数求解条件均值207

技巧140返回数据表中的最小正数208

技巧141利用极限函数设定销售限价209

技巧142实现多表统计人员工资209

技巧143校验编码中数字位数的有效性211

技巧144认识COUNTA与COUNTBLANK函数212

技巧145 COUNTIF函数条件判断介绍213

技巧146无序情况下的分类汇总215

技巧147查找不重复值216

技巧148单字段多条件计数217

技巧149多字段多条件计数217

技巧150应用SUMPRODUCT函数多条件计数218

技巧151应用SUMPRODUCT函数多条件求和218

技巧152应用SUMPRODUCT函数排序219

技巧153计算绝对值之和220

技巧154二维区域条件求和220

技巧155计算综合得分222

技巧156多字段多条件求和223

技巧157多表数据条件求和223

技巧158 FREQUENCY函数技巧二则224

158.1分段统计学生成绩225

158.2计算连续相同值的最多个数226

技巧159按成绩实现班级和年级排名226

159.1班级内部排名227

159.2年级内排名227

技巧160计算百分位排名227

技巧161剔除极值算平均得分228

技巧162筛选和隐藏状态下的统计229

技巧163生成连续的序号230

技巧164众数的妙用231

第10章 数据库函数233

技巧165数据库函数的基本技巧233

技巧166利用通配符查询产品信息235

技巧167理解条件区域使用相对引用产生的偏移236

技巧168统计指定条件下公司部门的人员数238

技巧169统计企业各部门的人员工资238

技巧170返回车间生产产品的最低成本239

技巧171计算商场指定商品的平均销售价格240

技巧172提取符合条件的信息240

第11章 财务金融函数242

技巧173普通复利终值与现值计算242

173.1普通复利终值计算242

173.2普通复利现值计算242

技巧174年金终值与现值计算243

174.1年金终值计算243

174.2年金现值计算243

技巧175固定利率下混合现金流的终值计算244

技巧176变动利率下混合现金流的终值计算244

技巧177固定资产投资的动态回收期计算245

技巧178现金流不定期条件下的决策分析246

178.1计算净现值247

178.2计算内部收益率247

技巧179名义利率与实际利率248

179.1将名义利率转为实际利率248

179.2将实际利率转为名义利率249

技巧180债券发行价格的计算250

技巧181每年付息债券的持有收益率计算251

技巧182折旧计算函数251

182.1用直线法计算固定资产折旧251

182.2用年数总和法计算固定资产折旧252

182.3用双倍余额递减法计算固定资产折旧253

第12章 工程函数256

技巧183贝塞尔（Bessel）函数256

技巧184度量衡转换函数257

技巧185数字进制转换函数258

技巧186用于筛选数据的函数259

186.1 DLETA函数260

186.2 GESTEP函数261

技巧187误差函数相关计算的函数262

187.1 ERT函数262

187.2 ERTC函数263

技巧188复数计算的函数263

第13章 宏表函数264

技巧189认识宏表及宏表函数264

189.1什么是宏表264

189.2建立自己的宏表264

189.3如何让宏表程序工作265

189.4认识宏表函数268

189.5在Excel中触发重新计算的条件及解决方法268

技巧190取指定路径下的所有文件名269

技巧191判断是否打开了指定的工作簿270

技巧192取得当前工作簿中所有工作表名称271

技巧193取得当前行所在的打印页码271

技巧194取得单元格属性值272

194.1利用分级显示的级数来计算提成量273

194.2提取单元格数字格式进行计算274

技巧195计算文本表达式中的结果274

技巧196将文本字符串变成常量数组276

第14章 循环引用277

技巧197认识循环引用277

技巧198记录单元格操作时间278

技巧199循环引用计算的一般技巧279

技巧200利用循环引用实现字符串连接280

第三篇 函数综合应用技巧281

第15章 数字及文本处理技术282

技巧201提取文本中的数值282

201.1基础知识282

201.2当文本中只有一处数值时提取数值282

201.3当文本中只有一处数值时提取数值文本283

201.4当文本中有多段数值时提取某段数值284

技巧202提取文本中所有的数字284

202.1按原序排列提取所有数字284

202.2按升序排列提取不重复数字285

技巧203数值格式化修约286

技巧204插值计算286

技巧205提取带有固定分割符文本中的一项内容287

技巧206提取含有某些特性的文本288

206.1基础知识288

206.2只有一段单字节字符文本的提取289

206.3多段文本取其中一段289

第16章 条件筛选技术291

技巧207一列数据提取不重复数据291

207.1方法一291

207.2方法二292

技巧208二维表提取不重复值292

技巧209带条件的提取不重复值293

209.1筛选条件下提取唯一值293

209.2有其他附加条件下提取唯一值294

209.3另类的提取唯一值295

技巧210统计唯一值的数量296

技巧211根据铁路里程表筛选里程297

技巧212用先进先出法计算出货价值297

第17章 数据排名与排序技术299

技巧213对数据进行中式排名299

213.1对一列数据进行中式排名299

213.2有条件的中式排名300

技巧214按排名占比确定等级301

技巧215对相同成绩平均排名名次301

技巧216对数值大小进行排序302

216.1方法一302

216.2 方法二303

技巧217对文本进行排序303

217.1 MMULT函数法303

217.2 COUNTIF 函数法304

技巧218按自定义顺序排序305

技巧219按数据出现次数排序305

第18章 数据重构技巧307

技巧220数字的按位与或非307

技巧221利用MMULT函数实现数据累加308

技巧222几个典型构造矩阵的应用308

222.1行倒置309

222.2列倒置309

222.3全倒置310

222.4两次复制310

222.5插入空行310

技巧223计算各类货品最大入货量中的最小值311

技巧224填充合并单元格312

第19章 数据表处理技巧313

技巧225对数据总表的模糊查询313

225.1一次性显示313

225.2分页显示314

技巧226按日期和品牌进行汇总316

226.1 SUMPRODUCT或SUM函数方式316

226.2 SUMIFS 函数方式317

技巧227按客户名对送货清单进行分组318

227.1辅助列+基本操作318

227.2辅助列+函数公式319

技巧228将分表数据合并到总表320

技巧229将总表数据拆分到分表321

第20章 处理日期数据323

技巧230转换中文日期为规范日期323

技巧231将标准日期转为英文日期324

技巧232推算母亲节和父亲节324

技巧233制作简易万年历325

233.1普通公式326

233.2 数组公式326

技巧234阳历与农历之间的转换327

234.1阳历转换为农历327

234.2农历转换为阳历329

234.3直接使用比对表330

技巧235推测某年度二十四节气的具体日期330

技巧236推算阳历日期所属生肖年331

236.1由阳历日期返回对应生肖年份332

236.2由生肖年份返回对应的生肖332

技巧237年月日时的天干地支333

237.1干支纪年333

237.2干支纪月334

237.3干支纪日336

237.4干支纪时336

第21章 多维引用技术338

技巧238计算应收款账龄338

技巧239统计营销任务完成的员工人数339

技巧240多表多条件汇总技术341

技巧241创建动态适应筛选条件的统计公式342

技巧242对数据列进行分区域统计343

第四篇 其他功能中的函数应用345

第22章 条件格式中使用函数346

技巧243标识未达标的记录346

技巧244根据关键字动态标识行记录348

技巧245标识周末日期350

技巧246实现生日提醒352

技巧247标识汇总行记录353

技巧248不显示错误值355

技巧249智能添加报表边框357

技巧250根据首次出现的关键字凸显整行359

技巧251根据职位凸显低于基础奖金的数据363

技巧252根据单吨利润表凸显低于平均值的产品365

第23章 数据有效性中使用函数367

技巧253限制只能输入数字367

技巧254限制只能输入文本368

技巧255限制只能输入以往日期369

技巧256限制只能输入手机号码370

技巧257限制只能输入不重复数据371

技巧258限制只能输入不重复的18位身份证号码372

技巧259去除重复值和空值设置数据有效性序列来源373

技巧260根据星期选择智能更新提供的促销品374

技巧261根据库存台账智能设最大可出库数量375

技巧262根据输入的关键字动态更新下拉菜单序列来源377

第24章 数据透视表函数综合应用382

技巧263初识数据透视表函数382

技巧264自动汇总方法下静态获取数据透视表数值384

技巧265自动汇总方法下动态获取数据透视表数值386

技巧266自定义汇总方法下获取数据透视表数据388

技巧267特殊的Excel 2000版数据透视表函数390

267.1使用Excel 2000版数据透视表函数公式静态获取数据391

267.2使用Excel 2000版数据透视表函数公式动态获取数据392

技巧268数据透视表函数与其他函数联合使用394

技巧269同时引用多个字段进行计算396

技巧270从多个数据透视表中获取数据397

技巧271应用数据透视表函数为排名评定星级399

技巧272应用数据透视表函数根据关键字取值401

技巧273应用数据透视表函数制作进货单402

技巧274计算分类百分比404

第25章 图形制作中的函数应用406

技巧275突出显示极值406

技巧276制作可排序的动态条形图408

技巧277高亮显示光标选定数据411

技巧278制作动态滚动曲线图412

第五篇 实例作证415

第26章 经典实例416

技巧279先进先出计算进货库存和销售成本416

279.1单商品的进货库存和销售成本416

279.2多商品的进货库存和销售成本418

技巧280汽车里程修理费补贴计算421

技巧281动态有效性点菜单423

281.1思路解析423

281.2具体实现424

技巧282速算个人所得税425

技巧283提示丰富的万年历426

283.1丰富的提示信息427

283.2条件格式凸显特殊日期429

283.3显示生肖图像430

283.4轮询日期的天干地支431

283.5当月的二十四节气432

技巧284加油站投资项目动态盈亏平衡分析433

技巧285按照BOM表自动生成材料用量计划表439

技巧286动态统计截至当月的计划完成率441

技巧287从多张工作表中按需查找引用数据442

技巧288根据房间人数智能分摊水电费444

附录A Excel限制和规范445

附录BExcel常用快捷键448

附录C Excel常用函数453