Excel公式与函数实用技巧辞典 超值双色印刷PDF|Epub|txt|kindle电子书版本网盘下载

Excel公式与函数实用技巧辞典 超值双色印刷PDF格式电子书版下载

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

Chapter 01 公式的应用技巧1

1.1 认识公式2

001 了解公式的组成结构2

002 使用复制公式与填充的方法快速计算数据3

003 自定义公式的应用5

1.2 公式中的运算符6

004 认识Excel 2010中应用的运算符6

005 了解运算符的顺序8

1.3 单元格地址的引用9

006 使用相对引用选择参与计算的单元格10

007 使用绝对引用固定某一单元格10

008 使用混合引用固定引用行或列11

009 使用跨表引用快速选择参与计算的数据源13

1.4 公式审核15

010 使用追踪引用查看值的引用15

011 使用追踪从属单元格查看数据的应用范围16

012 使用显示公式查看编辑的公式17

013 使用公式求值分步计算单元格数据17

014 对工作表中的公式进行错误检查19

015 使用监视窗口查看工作表中的变化20

1.5 公式中计算出错的处理方法21

016 “＃＃＃＃＃”错误及解决方法21

017 “＃DIV/0！”错误及解决方法22

018 “＃N/A”错误及解决方法23

019 “＃NAME？”错误及解决方法24

020 “＃NULL！”错误及解决方法25

021 “＃NUM！”错误及解决方法26

022 “＃REF！”错误及解决方法27

023 “＃VALUE！”错误及解决方法29

Chapter 02 定义与使用名称技巧31

2.1 定义名称的方法32

024 使用名称框定义名称32

025 根据所选内容创建名称34

2.2 定义名称的对象35

026 定义工作表名称区分工作表35

027 定义单元格区域37

028 在工作表中定义常量39

029 在工作表中定义常量数组39

2.3 管理名称40

030 使用名称管理器新建名称40

031 使用名称管理器删除不需要的名称42

032 使用名称管理器编辑已有名称43

2.4 使用名称工作45

033 在公式中使用定义的名称45

034 将定义的数组名称应用于公式46

035 根据定义的名称计算数据47

Chapter 03 函数基础与常用函数应用技巧49

3.1 认识Excel函数50

036 了解函数的组成结构50

037 使用函数的优势51

038 了解参数的可选与必选52

039 通过帮助学习函数的应用54

3.2 函数编辑与使用56

040 使用“插入函数”对话框输入函数56

041 通过单击“插入函数”按钮输入函数58

042 使用自动完成功能输入函数59

043 自定义函数60

044 使用函数提示工具61

3.3 常用函数的使用63

045 使用SUM函数快速求和63

046 使用AVERAGE函数求平均值64

047 使用MAX函数查找出最大值65

048 使用MIN函数查找出最小值67

049 使用COUNT函数统计单元格个数68

Chapter 04 财务函数应用技巧70

4.1 基本财务函数71

050 使用PV函数计算投资的现值71

051 使用FV函数计算一笔投资的期值72

052 使用RATE函数计算年金的各期利率73

053 使用NPER函数计算总期数75

4.2 计算本金和利息函数77

054 用PMT函数计算贷款偿还额77

055 用IPMT函数计算贷款在给定期间内支付的利息78

056 用PPMT函数计算贷款在给定期间内偿还的本金79

057 用ISPMT函数计算特定投资期内支付的利息80

058 用CUMIPMT函数计算两个付款期之间累计支付的利息81

059 使用CUMPRINC函数计算两个付款期之间累计支付的本金83

060 使用EFFECT函数计算有效的年利率84

061 使用NOMINAL函数计算名义年利率85

4.3 计算投资预算函数87

062 用FVSCHEDULE函数基于一系列复利计算未来值87

063 使用NPV函数计算投资的净现值88

064 使用XNPV函数计算现金流的净现值89

4.4 计算收益函数90

065 使用IRR函数计算一系列现金流的内部收益率90

066 使用MIRR函数计算修正内部收益率92

067 使用XIRR函数计算现金流的内部收益率93

4.5 计算折旧值函数94

068 使用AMORDEGRC函数计算每个结算期间的折旧值94

069 使用AMORLINC函数计算每个结算期间的折旧值95

070 使用DB函数计算一笔资产在给定期间内的折旧值96

071 使用DDB函数双倍余额递减计算折旧值98

072 使用VDB函数返回折旧值99

073 使用SLN函数计算某项资产在一个期间内的线性折旧值101

074 使用SYD函数按年限总和折旧法计算折旧值102

4.6 计算债券函数103

075 使用ACCRINT函数计算证券的利息103

076 使用COUPDAYBS函数计算付息期与成交日的天数104

077 使用COUPDAYS函数计算付息期的天数105

078 使用COUPDAYSNC函数计算固定付息日间的天数106

079 使用COUPNCD函数计算结算日之后的付息日107

080 使用COUPNUM函数计算付息次数109

081 使用COUPPCD函数计算结算日之前的付息日110

082 使用DISC函数计算证券的贴现率111

083 使用DURATION函数计算证券的修正期限112

084 使用INTRATE函数计算一次性付息证券的利率114

085 使用ODDFPRICE函数计算有价证券的价格115

086 使用ODDFYIELD函数计算有价证券的收益率116

087 使用ODDLPRICE函数计算有价证券的价格118

088 使用ODDLYIELD函数计算有价证券的收益率119

089 使用PRICE函数计算定期付息的有价证券价格121

090 使用PRICEDISC函数计算折价发行的有价证券价格122

091 使用PRICEMAT函数计算到期付息的有价证券价格123

092 使用RECEIVED函数计算有价证券的金额124

093 使用TBILLPRICE函数计算国库券的价格125

094 使用TBILLYIELD函数计算国库券的收益率126

095 使用YIELD函数计算有价证券的收益率127

096 使用YIELDDISC函数计算证券的年收益率128

097 使用YIELDMAT函数计算到期付息的有价证券年收益率129

098 使用MDURATION函数计算假设面值的修正Macauley131

099 使用TBILLEQ函数计算国库券的等效收益率132

Chapter 05 查找与引用函数应用技巧134

5.1 查找表中数据函数135

100 使用CHOOSE函数根据序号从列表中选择对应的内容135

101 使用HLOOKUP函数在区域或数组的行中查找数据136

102 使用VLOOKUP函数在区域或数组的列中查找数据138

103 使用LOOKUP函数以向量形式仅在单行单列中查找139

104 使用INDEX函数以引用形式返回指定位置中的内容140

5.2 引用表中数据函数142

105 使用MATCH函数返回指定内容所在的位置142

106 使用ADDRESS函数返回指定行号和列标对应的单元格地址144

107 使用COLUMN函数返回单元格或单元格区域首列的列标145

108 使用ROW函数返回单元格或单元格区域首行的行号146

109 使用OFFSET函数根据给定的偏移量返回新的引用区域148

110 使用TRANSPOSE函数转置数据区域的行列位置149

111 使用INDIRECT函数返回由文本值指定的引用151

5.3 显示数量函数152

112 使用AREAS函数返回引用中包含的区域数量152

113 使用COLUMNS函数返回数据区域包含的列数153

114 使用ROWS函数返回数据区域包含的行数154

Chapter 06 统计与求和函数应用技巧156

6.1 基本计数函数157

115 使用COUNTA函数返回指定内容所在的位置157

116 使用COUNTBLANK函数返回单元格个数158

6.2 条件统计函数159

117 使用COUNTIF函数根据条件进行计数160

118 使用COUNTIFS函数根据多条件进行计数161

6.3 条件求和类函数162

119 使用SUMIF函数根据条件求和162

120 使用SUMIFS函数计算多重条件的和163

6.4 条件均值类函数164

121 使用AVERAGEA函数计算参数中非空值的平均值165

122 使用AVERAGEIF函数计算指定条件的平均值166

123 使用AVERAGEIFS函数计算多条件平均值167

124 使用TRIMMEAN函数计算去除最高最低分的平均值169

6.5 方差函数170

125 使用VAR函数计算方差170

126 使用VARA函数计算样本方差171

127 使用VARP函数计算给定样本总体方差172

128 使用VARPA函数计算整个样本总体方差173

6.6 频率统计函数174

129 使用FREQUENCY函数分段统计学生成绩174

130 使用MODE函数计算出现频率最多的数值175

131 使用MODE.SNGL函数返回数组或区域出现频率最多的数值176

132 使用MODE.MULT函数返回阵列中频率最多的数值177

6.7 极值应用函数178

133 使用MAXA函数返回列表中的最大值179

134 使用MINA函数返回列表中的最小值180

135 使用LARGE函数返回指定的第N大的值181

136 使用SMALL函数返回指定的第N小的值182

6.8 概率分布函数183

137 使用BETADIST函数计算概率密度183

138 使用BETAINV函数计算概率密度的反函数值184

139 使用BINOMDIST函数计算概率值185

140 使用CHIDIST函数计算单尾概率186

141 使用CHIINV函数计算单尾概率的反函数值188

142 使用EXPONDIST函数计算指数分布189

143 使用FDIST函数计算F概率分布190

144 使用FINV函数计算F概率分布的反函数值192

145 使用GAMMADIST函数计算伽马累积分布193

146 使用GAMMAINV函数计算伽马累积分布函数的反函数值194

147 使用HYPGEOMDIST函数计算超几何分布196

148 使用LOGNORMDIST函数计算累积分布函数197

149 使用LOGINV函数计算累积分布函数的反函数值198

150 使用NEGBINOMDIST函数计算负二项式分布199

151 使用NORMDIST函数计算正态分布函数200

152 使用NORMINV函数计算正态累积分布函数的反函数值202

153 使用NORMSDIST函数计算正态累积分布函数203

154 使用NORMSINV函数计算正态累积分布函数的反函数值205

155 使用STANDARDIZE函数计算正态化数值206

156 使用POISSON函数计算泊松分布208

157 使用TDIST函数计算t分布的百分点210

158 使用TINV函数计算t分布的t值211

159 使用WEIBULL函数计算韦伯分布213

6.9 检验函数214

160 使用CHITEST函数计算独立性检验值215

161 使用FTEST函数计算F检验结果216

162 使用TTEST函数计算t检验概率217

163 使用ZTEST函数计算z检验的单尾概率值218

6.10 排名应用类函数220

164 使用RANK函数对数据进行排名220

165 使用RANK.EQ函数返回一个数字在一组数字中的排位221

166 使用RANK.AVG函数返回单元格地址223

Chapter 07 日期和时间函数应用技巧225

7.1 年月日函数226

167 使用YEAR函数返回年份数226

168 使用MONTH函数返回指定月数228

169 使用DAY函数返回指定日期的具体天数229

170 使用DATE数返回指定日期的序列号231

171 使用EDATE函数返回指定日期232

172 使DAYS360数计算两日期之间的天数234

173 使用EOMONTH函数返回指定月份的最后一天236

174 使用YEARFRAC函数返回两日期天数占全年天数的百分比237

7.2 星期相关函数239

175 使用WEEKDAY函数返回一周中的第几天的数值239

176 使用WEEKNUM函数返回一年中的周数241

7.3 工作日相关函数242

177 使用NETWORKDAYS函数返回两日期之间的完整工作日数243

178 使用NETWORKDAYS.INTL函数按周末参数返回工作日数244

179 使用TODAY函数返回日期格式的当前日期246

180 使用WORKDAY函数返回指定日期之前或之后的日期值248

181 使用WORKDAY.INTL函数返回指定日期之前或之后的日期249

7.4 返回时间段函数250

182 使用HOUR函数返回小时数值251

183 使用MINUTE函数返回分钟数值252

184 使用NOW函数返回当前日期和时间253

185 使用SECOND函数返回秒值254

186 使用TIME函数返回时间的小数值255

7.5 文本与日期和时间格式转换函数257

187 使用DATEVALUE函数将文本格式的日期转换为序列号257

188 使用TIMEVALUE函数将文本格式的时间转换为序列号259

Chapter 08 数学和三角函数应用技巧261

8.1 常规数学计算函数262

189 使用ABS函数计算数字的绝对值262

190 使用SIGN函数获取数值的符号263

191 使用PRODUCT函数计算乘积265

192 使用PI函数返回pi值266

193 使用SQRT函数计算正平方根268

194 使用SQRTPI函数计算pi乘积的平方根269

195 使用MOD函数计算两数相除的余数270

196 使用QUOTIENT函数返回商的整数部分271

197 使用GCD函数计算最大公约数271

198 使用LCM函数计算最小公倍数273

8.2 舍入与取整函数274

199 使用TRUNC函数返回数字的整数部分274

200 使用INT函数返回永远小于原数字的最接近的整数275

201 使用CEILING函数按条件向上舍入276

202 使用EVEN函数沿绝对值增大的方向舍入到最接近的偶数277

203 使用FLOOR函数按条件向下舍入278

204 使用ROUND函数按指定位数对数字进行四舍五入279

205 使用ODD函数沿绝对值增大的方向舍入到最接近的奇数280

8.3 指数与对数函数281

206 使用POWER函数计算数字的乘幂281

207 使用EXP函数计算e的n次幂282

208 使用LN函数计算自然对数283

209 使用LOG函数计算以指定数字为底数的对数284

8.4 三角函数285

210 使用DEGREES函数将弧度转换为角度285

211 使用RADIANS函数将角度转换为弧度286

212 使用COS函数计算给定角度的余弦值287

213 使用ACOS函数计算反余弦值288

214 使用ACOSH函数计算数字反双曲余弦值288

215 使用SIN函数计算角度的正弦值289

216 使用ASIN函数计算数字的反正弦值290

217 使用ASINH函数计算数字反双曲正弦值291

218 使用ATAN函数计算以弧度表示的反正切值292

219 使用ATAN2函数计算以坐标值表示的反正切值293

220 使用ATANH函数计算反双曲正切值294

221 使用COSH函数计算双曲余弦值295

222 使用SINH函数计算双曲正弦值296

223 使用TAN函数计算正切值297

224 使用TANH函数计算双曲正切值298

Chapter 09 文本函数应用技巧299

9.1 字符串编辑函数300

225 使用CONCATENATE函数将多个字符串合并到一处300

226 使用EXACT函数比较两个字符串是否相同301

227 使用LEN函数计算文本中的字符个数302

228 使用LENB函数计算文本中代表字符的字节数303

229 使用REPT函数按给定次数重复文本305

230 使用FIXED函数将数字按指定的小数位数取整305

9.2 返回文本内容函数306

231 使用LEFT函数从文本左侧起提取指定个数的字符307

232 使用LEFTB函数从文本左侧起提取指定字节数的字符307

233 使用MID函数从文本指定位置起提取指定个数的字符308

234 使用MIDB函数从文本指定位置起提取指定字节数的字符310

235 使用RIGHT函数从文本右侧起提取指定个数的字符311

236 使用RIGHTB函数从文本右侧起提取指定字节数字符312

9.3 转换文本格式函数313

237 使用ASC函数将全角字符转换为半角字符313

238 使用WIDECHAR函数将半角字符转换为全角字符314

239 使用CODE函数将文本字符串中第一个字符转换为数字代码315

240 使用CHAR函数将数字代码转换为对应的字符316

241 使用RMB函数将数字转换为带人民币符号￥的文本317

242 使用DOLLAR函数将数字转换为带美元符号？的文本318

243 使用TEXT函数将数字转换为文本319

244 使用BAHTTEXT函数将数字转换为泰语文本323

245 使用T函数将参数转换为文本323

246 使用LOWER函数将文本转换为小写325

247 使用UPPER函数将文本转换为大写326

248 使用PROPER函数将每个单词的首字母转换为大写326

249 使用VALUE函数将文本格式的数字转换为普通数字327

9.4 查找与替换文本函数328

250 使用FIND函数查找指定字符的位置329

251 使用FINDB函数查找指定字符的位置330

252 使用SEARCH函数以字符为单位查找指定字符的位置331

253 使用SEARCHB函数以字节为单位查找指定字符的位置333

254 使用REPLACE函数以字符为单位根据指定位置进行替换334

255 使用REPLACEB函数按指定位置以字节为单位进行替换335

256 使用SUBSTITUTE函数以指定文本进行替换336

9.5 删除文本中的字符函数338

257 使用CLEAN函数删除无法打印的字符338

258 使用TRIM函数删除多余的空格339

Chapter 10 逻辑函数应用技巧340

10.1 逻辑值函数341

259 使用TRUE函数返回逻辑值TRUE341

260 使用FALSE函数返回逻辑值FALSE343

10.2 交集、并集和求反函数344

261 使用AND函数判断指定的多个条件是否同时成立344

262 使用OR函数判断指定的任一条件为真就返回真345

263 使用NOT函数对逻辑值求反347

264 使用IF函数计算应缴税金348

10.3 用IFERROR函数判断公式结果349

Chapter 11 工程函数应用技巧351

11.1 贝塞尔函数352

265 使用BESSELI函数返回修正的贝塞尔函数352

266 使用BESSELJ函数返回贝塞尔函数353

267 使用BESSELK函数返回修正的贝塞尔函数354

268 使用BESSELY函数返回贝塞尔函数355

11.2 转换函数356

269 使用BIN2DEC函数将二进制数转换为十进制数356

270 使用BIN2HEX函数将二进制数转换为十六进制数357

271 使用BIN2OCT函数将二进制数转换为八进制数358

272 使用COMPLEX函数将实系数及虚系数转换为复数359

273 使用CONVERT函数对数字进行量度转换359

274 使用DEC2BIN函数将十进制数转换为二进制数362

275 使用DEC2HEX函数将十进制数转换为十六进制数363

276 使用DEC2OCT函数将十进制数转换为八进制数364

277 使用HEX2BIN函数将十六进制数转换为二进制数365

278 使用HEX2DEC函数将十六进制数转换为十进制数366

279 使用HEX2OCT函数将十六进制数转换为八进制数367

280 使用OCT2BIN函数将八进制数转换为二进制数368

281 使用OCT2DEC函数将八进制数转换为十进制数369

282 使用OCT2HEX函数将八进制数转换为十六进制数370

11.3 复数函数371

283 使用IMABS函数计算复数的绝对值371

284 使用IMAGINARY函数计算复数的虚系数372

285 使用IMREAL函数返回复数的实系数373

286 使用IMCONJUGATE函数返回复数的共轭复数374

287 使用IMSIN函数计算复数的正弦值375

288 使用IMSQRT函数计算复数的平方根376

289 使用IMSUB函数计算两个复数的差377

11.4 其他工程函数378

290 使用DELTA函数检测两个函数的相等性378

291 使用GESTEP函数检验数字是否大于阈值379

292 使用ERF函数计算误差函数的积分380

293 使用ERFC函数计算补余误差函数381

Chapter 12 其他函数应用技巧382

12.1 数据库函数383

294 使用DAVERAGE函数计算满足给定条件数据的平均值383

295 使用DCOUNT函数计算数据库中满足给定条件的单元格个数384

296 使用DCOUNTA函数计算满足给定条件的非空单元格个数385

297 使用DGET函数计算数据库中满足给定条件的单个值387

298 使用DMAX函数计算数据库中满足给定条件的最大数值388

299 使用DMIN函数计算数据库中满足给定条件的最小数值389

300 使用DPRODUCT函数计算满足给定条件数值的乘积390

301 使用DSTDEV函数计算数据库中样本总体标准偏差391

302 使用DSTDEVP函数计算数据库中总体标准偏差392

303 使用DSUM函数计算数据库中满足给定条件的数字和393

304 使用DVAR函数根据数据库条目样本估算数据的方差395

305 使用DVARP函数计算数据的总体方差396

12.2 信息函数397

306 使用CELL函数返回引用单元格信息397

307 使用ERROR.TYPE函数返回对应错误类型数值400

308 使用INFO函数返回与当前操作环境有关的信息401

309 使用N函数返回转换为数字后的值402

310 使用TYPE函数返回表示值的数据类型的数字403

311 使用NA函数返回错误值404

12.3 检测类函数405

312 使用ISBLANK函数判断测试对象405

313 使用ISEVEN函数判断数值是否为偶数407

314 使用ISODD函数判断数值是否为奇数408

315 使用ISERR函数检测值是否有＃N/A以外的错误值409

316 使用ISERROR函数检测值是否为错误值410

317 使用ISLOGICAL函数检测值是否是逻辑值411

318 使用ISNA函数检测值是否为＃N/A错误值412

319 使用ISNONTEXT函数检测值是否不为文本值413

320 使用ISNUMBER函数检测值是否为数值414

321 使用ISREF函数检测值是否为引用415

322 使用ISTEXT函数检测值是否为文本416

Chapter 13 数组公式应用技巧418

13.1 输入数组公式419

323 输入一维横向数组419

324 输入一维纵向数组420

325 输入二维数组421

326 由行列函数生成数组422

327 将二维数组转换为一维数组423

13.2 提取子数组425

328 从一维数组中提取子数组425

329 从二维数组中提取子数组427

13.3 数组运算428

330 在一个单元格中进行数组运算428

331 多个单元格的数组运算429

13.4 应用数组公式430

332 对数据区域满足条件的值进行求和430

333 为指定范围内的数值分类432

334 创建交叉数据分析表433

335 利用数组公式排序433