Excel公式与函数实例应用PDF电子书下载

第1章 Excel 2007基本操作 1

1.1 Excel的启动与退出 1

1.1.1 Excel的启动 1

1.1.2 Excel的工作界面 1

1.1.3 Excel的退出 3

1.2 工作簿的基本操作 3

1.2.1 打开工作簿 3

1.2.2 创建新的工作簿 4

1.2.3 保存工作簿 5

1.2.4 关闭工作簿 6

1.3 工作表的基本操作 6

1.3.1 重命名工作表 7

1.3.2 切换工作表 7

1.3.3 插入和删除工作表 8

1.3.4 复制和移动工作表 9

1.3.5 改变工作表默认个数 10

1.3.6 改变工作表标签颜色 11

1.4 数据输入 11

1.4.1 选定单元格 11

1.4.2 输入数据 11

1.4.3 自动填充数据 13

1.5 数据的保护与隐藏 14

1.5.1 隐藏工作表中的行和列 14

1.5.2 保护工作簿 15

1.5.3 保护工作表 15

1.6 设置单元格格式 16

1.6.1 设置文字格式 16

1.6.2 设置数字格式 17

1.6.3 设置文本对齐方式 17

1.7 设置表格格式 18

1.7.1 单元格的合并与拆分 18

1.7.2 设置行高和列宽 19

1.7.3 设置表格边框 20

1.7.4 设置背景和底纹 20

1.7.5 自动套用样式 21

1.8 打印工作表 24

1.8.1 页面设置 24

1.8.2 打印预览 24

1.8.3 打印文件 25

第2章 Excel公式使用 26

2.1 公式基础 26

2.1.1 公式中的常量 26

2.1.2 在公式中使用计算运算符 26

2.1.3 Excel执行公式运算的次序 27

2.1.4 输入公式 28

2.1.5 复制公式 28

2.1.6 隐藏或显示公式 29

2.1.7 用计算结果替换公式 30

2.1.8 允许、取消或更正循环引用 32

2.2 使用名称公式 33

2.2.1 使用名称的优点 33

2.2.2 名称适用的范围 34

2.2.3 定义工作表级使用的特殊名称 34

2.2.4 建立一个名称 34

2.2.5 自动命名 35

2.2.6 名称的修改和删除 36

2.2.7 名称的应用——建立中文公式 36

2.3 在公式中使用引用 38

2.3.1 引用样式 38

2.3.2 绝对引用、相对引用和混合引用 39

2.3.3 三维引用样式 39

2.3.4 R1C1引用样式 40

第3章 查找和更正公式中的错误 41

3.1 显示公式与单元格之间的关系 41

3.2 更正输入公式时的常见错误 43

3.3 更正公式中的常见问题 44

3.3.1 更改Excel检查的常见错误 44

3.3.2 一次更正一个常见公式错误 45

3.3.3 在工作表上标记常见公式错误 46

3.4 使用“监视窗口”来监视公式 46

3.4.1 向“监视窗口”中添加单元格 47

3.4.2 从“监视窗口”中删除单元格 48

3.5 更正错误值 48

3.5.1 更正＃错误 49

3.5.2 更正＃DIV/O！错误 49

3.5.3 更正＃N/A错误 50

3.5.4 更正＃NAME？错误 52

3.5.5 更正＃NULL！错误 53

3.5.6 更正＃NUM！错误 55

3.5.7 更正＃REF！错误 55

3.5.8 更正＃VALUE！错误 56

第4章 函数应用基础 58

4.1 函数入门 58

4.1.1 什么是函数 58

4.1.2 函数的种类 59

4.1.3 函数的参数 60

4.1.4 函数输入方法 62

4.2 函数在数据处理的中的作用 63

4.2.1 简化公式 63

4.2.2 实现特殊运算 64

4.2.3 实现智能判断 66

4.2.4 提高工作效率 67

第5章 文本函数 68

5.1 文本函数基础 68

5.1.1 大小写转换 68

5.1.2 取出字符串中的部分字符 68

5.1.3 去除字符串的空白 69

5.1.4 字符串的比较 69

5.2 文本函数实例应用 69

5.2.1 用REPT函数计算商品热门程度 69

5.2.2 用REPLACE函数更新手机号 71

5.2.3 用DOLLAR和RMB转换货币格式 71

5.2.4 用LEN函数计算字符个数 72

5.2.5 用LEFT和RIGHT提取区号和电话号码 73

5.2.6 用CONCATENATE返回个人称呼 74

5.2.7 用MID、LEN、RIGHT提取用户资料 75

5.3 文本函数速查 76

1．ASC函数 76

2．CHAR函数 76

3．CLEAN函数 77

4．CODE函数 77

5．CONCATENATE函数 77

6．DOLLAR或RMB函数 77

7．EXACT函数 77

8．FIND函数 78

9．FINDB函数 78

10．FIXED函数 78

11．JIS函数 78

12．LEFT或LEFTB函数 79

13．LEN或LENB函数 79

14．LOWER函数 79

15．MID或MIDB函数 79

16．PHONETIC函数 80

17．PROPER函数 80

18．REPLACE或REPLACEB函数 80

19．REPT函数 80

20．RIGHT或RIGHTB函数 81

21．SEARCH或SEARCHB函数 81

22．SUBSTITUTE函数 81

23．T函数 82

24．TEXT函数 82

25．TRIM函数 82

26．UPPER函数 82

27．VALUE函数 82

第6章 日期／时间函数 83

6.1 日期与时间函数基础 83

6.2 日期系统实例 83

6.2.1 日期系统 83

6.2.2 日期位数问题 84

6.3 日期函数实例 85

6.3.1 用Date和Today函数设计倒计时 85

6.3.2 用DATEDIF函数计算日期间隔 85

6.3.3 用YEAR、DAY和DATE判断年龄 87

6.3.4 用TODAY函数设计商品过期提醒 88

6.4 时间函数实例 88

6.4.1 用HOUR和MINUTE计算租车费用 88

6.4.2 用HOUR和SECOND计算话费 90

6.5 日期／时间函数综合应用实例 91

6.6 日期与时间函数速查 93

1．DATE函数 93

2．DATEVALUE函数 93

3．DAY函数 94

4．DAYS360函数 94

5．EDATE函数 94

6．EOMONTH函数 95

7．HOUR函数 95

8．MINUTE函数 95

9．MONTH函数 95

10．NETWORKDAYS函数 95

11．NOW函数 96

12．SECOND函数 96

13．TIME函数 96

14．TIMEVALUE函数 96

15．TODAY函数 96

16．WEEKDAY函数 97

17．WEEKNUM函数 97

18．WORKDAY函数 97

19．YEAR函数 97

20．YEARFRAC函数 98

第7章 查询与引用函数 99

7.1 查询与引用函数应用基础 99

7.1.1 引用知识 99

7.1.2 ADDRESS的使用方法 100

7.1.3 INDEX、ROWS的使用方法 100

7.1.4 INDIRECT、OFFSET的使用方法 101

7.1.5 使用HLOOKUP、LOOKUP、VLOOKUP 101

7.1.6 HYPER LINK的使用方法 104

7.1.7 TRANSPOSE的使用方法 104

7.2 查询数据实例 105

7.2.1 用CHOOSE函数在列表中选择值 105

7.2.2 用LOOKUP函数查找数据 107

7.2.3 用HLOOKUP函数进行水平查找 108

7.2.4 用VLOOKUP函数进行竖直查找 109

7.2.5 用MATCH函数返回数组中的位置 111

7.3 引用数据实例 112

7.3.1 用ADDRESS函数显示引用地址 112

7.3.2 COLUMN、ROW返回列标和行号 112

7.3.3 COLUWNS、ROWS返回引用数量 113

7.3.4 用AREAS函数返回区域数量 114

7.3.5 INDEX函数返回表或区域中的值 114

7.3.6 用INDIRECT返回指定引用及结果 116

7.3.7 用OFFSET函数实现新引用 116

7.3.8 用HYPER LINK函数实现快速跳转 117

7.4 查询和引用函数综合案例 118

7.4.1 自动考核及计算奖金 118

7.4.2 典型案例欠款人信息统计 120

7.5 查找和引用函数速查 121

1．ADDRESS函数 121

2．AREAS函数 121

3．CHOOSE函数 121

4．COLUMN函数 121

5．COLUMNS函数 122

6．HLOOKUP函数 122

7．HYPERLINK函数 122

8．INDEX函数 122

9．INDIRECT函数 123

10．LOOKUP函数 123

11．MATCH函数 124

12．OFFSET函数 124

13．ROW函数 125

14．ROWS函数 125

15．RTD函数 125

16．TRANSPOSE函数 125

17．VLOOKUP函数 125

第8章 财务函数 127

8.1 财务函数基础 127

8.2 投资计算函数实例 127

8.2.1 求某项投资的未来值FV 127

8.2.2 求投资的净现值NPv 128

8.2.3 求贷款分期偿还额PMT 129

8.2.4 利用Pv函数预测投资的未来值 129

8.2.5 FVSCHEDULE函数计算投资未来值 130

8.2.6 用NPv函数确定某项投资的价值 131

8.2.7 XNPv计算变化现金流的净现值 132

8.3 折旧计算函数实例 133

8.3.1 利用DB、DDB、SLN计算折旧 133

8.3.2 利用SLN计算各个时间段的折旧值 135

8.4 偿还率计算函数实例 136

8.4.1 RATE函数计算投资的实际赢利 136

8.4.2 用IRR函数计算现金流内部收益率 137

8.4.3 用MIRR函数计算修正内部收益率 138

8.4.4 用多个函数制作贷款投资经营表 140

8.5 本利函数实例 144

8.5.1 用PPMT等函数计算本金、利息 144

8.5.2 用NPER函数确定贷款期限 149

8.6 财务函数速查 149

1．ACCRINT函数 149

2．ACCRINTM函数 150

3．AMORDEGRC函数 150

4．AMORLINC函数 150

5．COUPDAYBS函数 151

6．COUPDAYS函数 151

7．COUPDAYSNC函数 151

8．COUPNUM函数 151

9．COUPPCD函数 152

10．CUMIPMT函数 152

11．CUMPRINC函数 152

12．DB函数 152

13．DDB函数 152

14．DISC函数 153

15．DOLLARDE函数 153

16．DOLLARFR函数 153

17．DURATION函数 153

18．EFFECT函数 154

19．FV函数 154

20．FVSCHEDULE函数 154

21．INTRATE函数 154

22．IPMT函数 155

23．IRR函数 155

24．ISPMT函数 155

25．MDURATION函数 155

26．MIRR函数 156

27．NOMINAL函数 156

28．NPER函数 156

29．NPv函数 156

30．ODDFPRICE函数 157

31．ODDFYIELD函数 157

32．ODDLPRICE函数 158

33．ODDLYIELD函数 158

34．PMT函数 158

35．PPMT函数 159

36．PRICE函数 159

37．PRICEDISC函数 159

38．PRICEMAT函数 159

39．Pv函数 160

40．RATE函数 160

41．RECEIVED函数 160

42．SLN函数 160

43．SYD函数 161

44．TBILLEQ函数 161

45．TBILLPRICE函数 161

46．TBILLYIELD函数 161

47．VDB函数 161

48．XIRR函数 162

49．XNPv函数 162

50．YIELD函数 162

51．YIELDDISC函数 163

52．YIELDMAT函数 163

第9章 数学和三角函数 164

9.1 数学和三角函数基础 164

9.1.1 与求和有关的函数应用 164

9.1.2 与函数图像有关的函数应用 165

9.1.3 常见函数使用技巧——四舍五入 166

9.2 常用数学函数实例应用 166

9.2.1 用SUBTOTAL和SUM计算销售数据 166

9.2.2 用SUMIF函数统计成绩表 168

9.2.3 用ABS函数计算销量差额 169

9.2.4 用CEILING函数计算书稿的印张 170

9.2.5 使SUMPRODUCT、ROUNDUP 171

9.2.6 用INT,MOD计算各种面额的数量 173

9.3 常用三角函数实例应用 175

9.3.1 用RADIANS和SIN/COS计算三角值 175

9.3.2 用ASIN、ACOS计算反三角函数值 176

9.4 数学和三角函数速查 177

1．ABS函数 177

2．ACOS函数 177

3．ACOSH函数 177

4．ASIN函数 177

5．ASINH函数 177

6．ATAN函数 177

7．ATAN2函数 177

8．ATANH函数 178

9．CEILING函数 178

10．COMBIN函数 178

11．COS函数 178

12．COSH函数 178

13．COUNTIF函数 178

14．DEGREES函数 179

15．EVEN函数 179

16．EXP函数 179

17．FACT函数 179

18．FACTDOUBLE函数 179

19．FLOOR函数 179

20．GCD函数 179

21．INT函数 180

22．LCM函数 180

23．LN函数 180

24．LOG函数 180

25．LOG10函数 180

26．MDETERM函数 180

27．MINVERSE函数 181

28．MMULT函数 181

29．MOD函数 181

30．MROUND函数 181

31．MULTINOMIAL函数 182

32．ODD函数 182

33．PI函数 182

34．POWER函数 182

35．PRODUCT函数 182

36．QUOTIENT函数 182

37．RADIANS函数 183

38．RAND函数 183

39．RANDBETWEEN函数 183

40．ROMAN函数 183

41．ROUND函数 183

42．ROUNDDOWN函数 184

43．ROUNDUP函数 184

44．SERIESSUM函数 184

45．SIGN函数 184

46．SIN函数 184

47．SINH函数 185

48．SQRT函数 185

49．SQRTPI函数 185

50．SUBTOTAL函数 185

51．SUM函数 185

52．SUMIF函数 186

53．SUMPRODUCT函数 186

54．SUMSQ函数 186

55．SUMX2MY2函数 186

56．SUMX2PY2函数 186

57．SUMXMY2函数 186

58．TAN函数 187

59．TANH函数 187

60．TRUNC函数 187

第10章 统计函数 188

10.1 统计函数基础 188

10.1.1 计函数AVERAGE、TRIMMEAN 188

10.1.2 求单元格个数的统计函数COUNT 189

10.1.3 数据频率分布FREQUENCY 189

10.1.4 用于求数据集数值的函数 190

10.1.5 用来排位的函数 191

10.2 常规统计函数应用实例 191

10.2.1 用MAX和MIN确定最高和最低业绩 191

10.2.2 用FREQUENCY函数计算员工业绩 193

10.2.3 用RANK函数确定员工业绩名次 194

10.2.4 用SMALL取得第K小的数据 195

10.2.5 用FORECAST预测未来用电量 196

10.2.6 用TRIMMEAN函数计算员工平均分 196

10.2.7 用COUNTA计算按时上下班次数 197

10.2.8 用COUNT、COUNTIF统计考勤表 198

10.2.9 用MODE函数统计民主选票 199

10.2.10 用GEOMEAN函数计算平均增长率 200

10.2.11 STDEVA计算总体和标准方差 201

10.2.12 用TREND函数预测销售额 203

10.3 数理统计函数实例应用 203

10.3.1 用CONFIDENCE计算置信区间 203

10.3.2 用CORREL计算销售单价 204

10.3.3 用FTEST计算销售量的F检验结果 205

10.3.4 用HYPGEOMDIST计算概率 205

10.3.5 用VAR、VARA计算样本和总体方差 207

10.3.6 用POISSON函数计算产品合格率 208

10.3.7 用SKEW函数计算销量的偏斜度 209

10.3.8 用KURT函数计算销量分布 210

10.3.9 用BINOMDIST计算抛硬币的概率 210

10.4 统计函数速查 211

1．AVEDEV函数 211

2．AVERAGE函数 211

3．AVERAGEA函数 211

4．BETADIST函数 212

5．BETAINV函数 212

6．BINOMDIST函数 212

7．CHIDIST函数 212

8．CHIINV函数 212

9．CHITEST函数 213

10．CONFIDENCE函数 213

11．CORREL函数 213

12．COUNT函数 213

13．COUNTA函数 214

14．COUNTBLANK函数 214

15．COUNTIF函数 214

16．COVAR函数 214

17．CRITBINOM函数 215

18．DEVSQ函数 215

19．EXPONDIST函数 215

20．FDIST函数 215

21．FINV函数 215

22．FISHER函数 216

23．FISHERINV函数 216

24．FORECAST函数 216

25．FREQUENCY函数 216

26．FTEST函数 216

27．GAMMADIST函数 217

28．GAMMAINV函数 217

29．GAMMALN函数 217

30．GEOMEAN函数 217

31．GROWTH函数 217

32．HARMEAN函数 218

33．HYPGEOMDIST函数 218

34．INTERCEPT函数 218

35．KURT函数 218

36．LARGE函数 219

37．LINEST函数 219

38．LOGEST函数 219

39．LOGINV函数 219

40．LOGNORMDIST函数 220

41．MAX函数 220

42．MAXA函数 220

43．MEDIAN函数 220

44．MIN函数 220

45．MINA函数 220

46．MODE函数 221

47．NEGBINOMDIST函数 221

48．NORMDIST函数 221

49．NORMSINV函数 221

50．NORMSDIST函数 221

51．NORMSINV函数 222

52．PEARSON函数 222

53．PERCENTILE函数 222

54．PERCENTRANK函数 222

55．PERMUT函数 222

56．POISSON函数 222

57．PROB函数 223

58．QUARTILE函数 223

59．RANK函数 223

60．RSQ函数 224

61．SKEW函数 224

62．SLOPE函数 224

63．SMALL函数 224

64．STANDARDIZE函数 224

65．STDEV函数 224

66．STDEVA函数 225

67．STDEVP函数 225

68．STDEVPA函数 225

69．STEYX函数 226

70．TDIST函数 226

71．TINV函数 226

72．TREND函数 226

73．TRIMMEAN函数 226

74．TTEST函数 227

75．VAR函数 227

76．VARA函数 227

77．VARP函数 227

78．VARPA函数 227

79．WEIBULL函数 228

80．ZTEST函数 228

第11章 工程函数 229

11.1 工程函数基础 229

11.1.1 贝赛尔（Bessel）函数 229

11.1.2 在不同的数字系统间进行数值转换 229

11.1.3 用DELTA与GESTEP筛选数据 230

11.1.4 度量衡转换函数CONVERT 230

11.1.5 与积分有关的函数ERF与ERFC 231

11.1.6 与复数运算有关的函数 231

11.2 工程函数速查 231

1．BESSELI函数 231

2．BESSELJ函数 231

3．BESSELK函数 231

4．BESSELY函数 232

5．BIN2DEC函数 232

6．BIN2HEX函数 232

7．BIN2OCT函数 232

8．COMPLEX函数 232

9．CONVERT函数 232

10．DEC2BIN函数 233

11．DEC2HEX函数 233

12．DEC2OCT函数 233

13．DELTA函数 233

14．ERF函数 233

15．ERFC函数 233

16．GESTEP函数 233

17．HEX2BIN函数 233

18．HEX2DEC函数 234

19．HEX2OCT函数 234

20．IMABS函数 234

21．IMAGINARY函数 234

22．IMARGUMENT函数 234

23．MCONJUGATE函数 234

24．IMCOS函数 234

25．IMDIV函数 234

26．IMEXP函数 235

27．IMLN函数 235

28．IMLOG10函数 235

29．IMLOG2函数 235

30．IMPOWER 235

31．IMPRODUCT函数 235

32．IMREAL函数 235

33．IMSIN函数 236

34．IMSQRT函数 236

35．IMSUB函数 236

36．IMSUM函数 236

37．OCT2BIN函数 236

38．OCT2DEC函数 236

39．OCT2HEX函数 236

第12章 数据库函数 237

12.1 数据库函数基础 237

12.1.1 函数的共同特点 237

12.1.2 数据库函数列表 237

12.1.3 数据库函数的参数含义 237

12.1.4 实例说明 237

12.1.5 关于条件的建立 239

12.2 数据库函数实例应用 241

12.2.1 用常见数据库函数管理数据 241

12.2.2 利用DCOUNT函数管理学生成绩 244

12.3 数据库函数速查 245

1．DAVERAGE函数 245

2．DCOUNT函数 245

3．DCOUNTA函数 245

4．DGET函数 245

5．DMAX函数 246

6．DMIN函数 246

7．DPRODUCT函数 246

8．DSTDEV函数 246

9．DSTDEVP函数 246

10．DSUM函数 247

11．DVAR函数 247

12．DVARP函数 247

13．GETPIVOTDATA函数 247

第13章 信息函数 248

13.1 信息函数应用基础及实例 248

13.1.1 返回格式、位置或内容的函数CELL 248

13.1.2 计算空白单元格个数COUNTBLANK 249

13.1.3 返回对应的错误类型数字ERROR.TYPE 250

13.1.4 返回当前操作环境信息INFO 250

13.1.5 用来检验数值或引用类型IS 250

13.1.6 检验参数奇偶性ISEVEN与ISODD 251

13.1.7 返回转化为数值后的值 251

13.1.8 返回错误值＃N/A的函数NA 252

13.1.9 返回数值的类型TYPE 252

13.2 信息函数速查 252

1．CELL函数 252

2．ERROR.TYPE函数 252

3．INFO函数 253

4．IS类函数 253

5．ISEVEN函数 254

6．ISODD函数 254

7．N函数 254

8．NA函数 254

9．TYPE函数 255

第14章 逻辑函数 256

14.1 逻辑函数应用及实例 256

14.1.1 AND、OR、NOT函数的使用 256

14.1.2 TRUE、FALSE函数的使用 257

14.1.3 IF函数 257

14.2 逻辑函数应用实例 260

14.2.1 工资表的建立 260

14.2.2 建立工资模板 260

14.2.3 扣除所得税 261

14.2.4 用数据透视表 261

14.3 逻辑运算符速查 262

1．AND函数 262

2．FALSE函数 262

3．IF函数 262

4．NOT函数 262

5．OR函数 262

6．TRUE函数 263

第15章 Excel函数大型综合案例应用 263

15.1 逻辑函数应用实例 264

15.1.1 工资表的建立 264

15.1.2 建立工资模板 264

15.1.3 扣除所得税 264

15.1.4 用数据透视表 265

15.2 财务预算 265

15.2.1 相关资料的搜集 265

15.2.2 编制方法简介 267

15.2.3 进行预算编制 267

15.3 企业毛利与盈亏平衡点分析 270

15.3.1 进行毛利分析 270

15.3.2 盈亏平衡点分析 272