人事必须知道的80个Excel函数 双色版PDF电子书下载

第1章　Excel函数知识1.1　Excel常见名词 2

1.2　单元格引用 3

1.3　公式基础 9

1.4　函数基础 12

1.5　错误分析与检查 22

1.6 Excel数据安全 24

第2章　数学和三角函数2.1 SUM 返回某一个单元格区域中所有数字之和 30

2.2　SUMIF 按条件对指定单元格求和 40

2.3　SUMIFS 对区域中满足多个条件的单元格求和 43

2.4　SUMPRODUCT 返回数组间对应的元素乘积之和 47

2.5 PRODUCT 返回所有参数乘积值 53

2.6 MMULT 返回两个数组的矩阵乘积 55

2.7　MOD　返回两数相除的余数 59

2.8 QUOTIENT 返回两数相除的整数部分 62

2.9　ODD　返回沿绝对值增大方向取整后最接近的奇数 64

2.10　INT　返回参数的整数部分 66

2.11 FLOOR　返回向零方向的舍入值 68

2.12　MROUND　返回参数按指定基数舍入后的数值 70

2.13　FACT　返回数字的阶乘 72

2.14　MDETERM　返回一个数组的矩阵行列式的值 74

2.15　MINVERSE　返回数组中存储的矩阵的逆矩阵 76

2.16 ROUND　返回指定位数取整后的数字 78

2.17　CEILING　返回沿绝对值增大方向的舍入值 81

2.18　EVEN　返回沿绝对值增大方向取整后最接近的偶数 83

第3章　逻辑函数 86

3.1 OR　判定指定的任一条件是为真，即返回真 86

3.2　IF　根据指定的条件返回不同的结果 89

3.3　AND　判定指定的多个条件是否全部成立 92

3.4　NOT　对其参数的逻辑求反 94

第4章　文本函数 98

4.1 CONCATENATE 将两个或多个文本字符串合并为一个文本字符串 98

4.2　TEXT　设置数字格式并将其转换为文本 100

4.3　FIND　查找字符串字符起始位置（区分大小写） 107

4.4　REPLACE　替换字符串中的部分字符（根据字符数） 110

4.5　SEARCH　查找字符串字符起始位置（不区分大小写） 113

4.6　LEFT　返回文本值中最左边的字符 116

4.7　MID　从文本字符串中的指定位置起返回特定个数的字符 119

4.8　RIGHT　返回文本值中最右边的字符 121

4.9　FIXED　将数字按指定的小数位数显示，并以文本形式返回 125

4.10　DOLLAR　将数字转换为＄（美元）货币格式的文本 129

4.11 RMB　将数字转换为￥（人民币）货币格式的文本 131

4.12　FINDB　查找字符串字节起始位置 133

4.13　SEARCHB　查找字符串字节起始位置（不区分大小写） 135

4.14　MIDB　从文本字符串中的指定位置起返回特定个数的字符 138

4.15　LEFTB　根据所指定的字节数返回文本字符串中最左边的字符 140

第5章　统计函数 144

5.1　COUNT　返回参数列表中的数字个数 144

5.2　COUNTA　返回参数列表中非空值的单元格个数 148

5.3　COUNTIF　计算区域中满足给定条件的单元格的个数 153

5.4　AVERAGE　返回参数的平均值 159

5.5　AVERAGEA　计算参数列表中数值的平均值 162

5.6　MODE　返回在某一数组或数据区域中频率最高的数值 165

5.7　MAX　返回一组值中的最大值 167

5.8　MIN　返回参数中最小值 169

第6章　日期和时间函数6.1　DATE　返回特定日期的年、月、日 176

6.2 DAY　返回某日期对应当月的天数 179

6.3　EDATE　返回指定月数之前或之后的日期 181

6.4　EOMONTH　返回指定日期之前或之后月份的最后一天的日期 184

6.5　DATEDIF　返回两个日期之间的年、月、日间隔数 187

6.6　MINUTE　返回时间值中的分钟 189

6.7 DATEVALUE　返回文本字符串所代表的日期序列号 191

6.8　TODAY 返回当前日期 193

6.9　NOW　返回当前的日期和时间 195

6.10 WEEKDAY 返回某日期为星期几 197

6.11 HOUR 返回时间值的小时数 200

第7章　查找和引用函数7.1 COLUMN　返回引用的列标 204

7.2　OFFSET 以指定引用为参照系，通过给定偏移量得到新的引用 207

7.3　INDEX　返回指定单元格或单元格数组的值（数组形式） 214

7.4　VLOOKUP　在数据表的首列查找指定的值，并返回数据表当前行中指定列的值 223

7.5 MATCH 返回指定方式下与指定数值匹配的元素位置 230

7.6 LOOKUP 从单列或单行区域返回值（向量形式） 234

7.7　CHOOSE 返回指定数值参数列表中的数值 242

7.8　ADDRESS　按照指定的行号和列标返回单元格引用地址 245

7.9　ROW　返回引用的行号 248

7.10　AREAS 返回引用中包含的区域个数 250

7.11 INDIRECT 返回由文本字符串指定的引用 252

第8章　信息函数 258

8.1 ISODD 奇数判断 258

8.2 ISERR 判断＃N/A以外的错误值 261

8.3 ISERROR 判断错误值 266

8.4　ISNA　错误值判断 268

8.5 ERROR.TYPE 返回对应错误类型数值 270

第9章　财务函数 273

9.1 FV　基于固定利率及等额分期付款方式返回某项投资的未来值 273

9.2　PV　返回投资的现值 276

9.3　NPER　基于固定利率及等额分期付款方式返回某项投资的总期数 279

9.4　RATE　返回年金的各期利率 282

9.5 CUMIPMT 返回一笔贷款在给定的START_PERIOD到END_PERIOD期间累计偿还的利息数额 285

9.6　CUMIPRINC　返回一笔贷款在给定的START_PERIOD到END_PERIOD期间累计偿还的本金数额 288

9.7　DB　使用固定余额递减法计算折旧值 291

9.8　DDB　使用双倍余额递减法或其他指定方法计算折旧值 294

第2章　数学和三角函数案例1 对三组生产数据求和（SUM） 30

案例2　对生产表中大于100的产量求和（SUM） 31

案例3　对生产表中大于110或者小于100的数据求和（SUM） 33

案例4　求前三名产量之和（SUM） 34

案例5　求所有工作表相同区域数据之和（SUM） 35

案例6　求当前表以外的所有工作表相同区域的总和（SUM） 36

案例7　用SUM函数计数（SUM） 37

案例8　多个工作表不同区域求前三名产量和（SUM） 38

案例9　汇总姓赵、刘、李的业务员提成金额（SUMIF） 41

案例10　汇总车间女性人数（SUMIFS） 44

案例11　计算车间男性与女性人员的差（SUMIFS） 45

案例12　计算参保人数（SUMPRODUCT） 47

案例13　求25岁以上男性人数（SUMPRODUCT） 48

案例14　汇总一车间男性参保人数（SUMPRODUCT） 50

案例15　汇总业务员业绩（SUMPRODUCT） 51

案例16　计算每小时生产产值（PRODUCT） 53

案例17　计算车间盈亏（MMULT） 55

案例18　计算各组别第三名产量是多少（MMULT） 57

案例19　根据身份证号计算性别（MOD） 59

案例20　根据业绩计算业务员奖金（QUOTIENT） 62

案例21　抽取奇数行姓名（ODD） 64

案例22　隔两行进行编号（INT） 66

案例23 统计业务员提成金额，不足20000元忽略（FLOOR） 69

案例24 以超产80为单位计算超产奖（MROUND） 70

案例25　计算50到60之间的整数相乘的结果（FACT） 72

案例26　解二元联立方程组（MDETERM） 74

案例27　解多元联立方程组（MINVERSE） 76

案例28　将个人所得税的金额四舍五入（ROUND） 78

案例29　工资表的格式转换（GEILING） 81

案例30　统计参考人数（EVEN） 83

第3章　逻辑函数 86

案例31 根据年龄判断职工是否退休（OR） 86

案例32　根据年龄与职务判断职工是否退休（OR） 87

案例33　根据业绩计算需要发放多少奖金（IF） 90

案例34　有选择地汇总数据（IF） 90

案例35　判断毕业生是否符合留用条件（AND） 92

案例36　没有任何领导给“不通过”就发放奖金（NOT） 94

第4章　文本函数 99

案例37　根据身份证号码提取出生年月日（CONCATENATE） 99

案例38　根据身份证号码判断性别（TEXT） 102

案例39　根据身份证号码计算出生年月日（TEXT） 103

案例40　从身份证号码中提取表示性别的数字（TEXT） 104

案例41　计算年终奖（TEXT） 105

案例42　通信录单列转三列（FIND） 108

案例43　将15位身份证号码升级为18位（REPLACE） 111

案例44　统计名为“刘星”的人数（SEARCH） 114

案例45　通过身份证号码计算年龄（LEFT） 117

案例46　从卡机数据提取打卡时间（MID） 120

案例47　根据卡机数据判断员工部门（RIGHT） 121

案例48　根据身份证号码统计男性人数（RIGHT） 123

案例49　将业务员销售金额保留一位小数并显示千分位分隔符（FIXED） 126

案例50　计算某业务员承接的订单金额并以“百万”为单位显示（FIXED） 127

案例51 用＄表示某业务员本月的销售金额（DOLLAR） 130

案例52　用￥表示某业务员本月的销售业绩（RMB） 132

案例53　提取员工英文名字（FINDB） 134

案例54　提取各软件的版本号（SEARCHB） 136

案例55　提取各车间负责人姓名（MIDB） 139

案例56　提取技术人员所在省市名称（LEFTB） 141

第5章　统计函数 144

案例57　计算完工率（COUNT） 144

案例58　统计各完成数量段的人数（COUNT） 145

案例59　统计有多少个选手（COUNT） 146

案例60　统计出勤异常人数（COUNTA） 148

案例61　判断是否有未参加考核的人（COUNTA） 149

案例62　根据毕业学校统计中学学历人数（COUNTIF） 153

案例63　计算两列数据相同个数（COUNTIF） 154

案例64　统计连续三次进入前十名的人数（COUNTIF） 156

案例65　统计淘汰者人数（COUNTIF） 157

案例66　计算平均产量（忽略离职人员）（AVERAGE） 159

案例67　计算产量在90以上的平均产量（AVERAGE） 160

案例68　计算二车间所有人员的平均获奖率（AVERAGEA） 163

案例69　计算哪种产品生产次数最多（MODE） 165

案例70　计算卫冕失败最多的次数（MAX） 167

案例71　确定最佳成绩（MIN） 169

第6章　日期和时间函数案例72　将身份证号码转换成出生日期序列（DATE） 177

案例73　计算员工转正时间（DAY） 179

案例74　提示合同续约（EDATE） 181

案例75　统计两倍工资的加班小时数（EOMONTH） 185

案例76　计算员工工作天数和月数（DATEDIF） 188

案例77　根据完工时间计算奖金（MINUTE） 189

案例78　计算两个月之间相差的天数（DATEVALUE） 191

案例79　统计已到达收款时间的货品数量（TODAY） 193

案例80　确定是否已到换班时间（NOW） 195

案例81　计算周末奖金补贴（WEEKDAY） 198

案例82　计算本日工时工资（HOUE） 200

第7章　查找和引用函数案例83　重组人事资料表（COLUMN） 204

案例84　计算所有人的一周产量并排名（COLUMN） 205

案例85　计算连续三天之总产量大于等于25万元的次数（OFFSET） 208

案例86　提取组名及计算每组平均达标率（OFFSET） 209

案例87　判断是否超过一半人达标率在90％以上（OFFSET） 210

案例88　计算哪一个月完成目标（OFFSET） 211

案例89　计算有几次连续三个月的平均值低于整体平均值（OFFSET） 212

案例90　从电话簿中选择性引用数据（INDEX） 215

案例91 罗列优秀员工（INDEX） 216

案例92　插入空行分割数据（INDEX） 217

案例93　仅仅提取通信录中四分之三信息（INDEX） 218

案例94　罗列12月中产量倒数第一名次数最多者名单（INDEX） 219

案例95　按投诉次数升序排列客服姓名（INDEX） 221

案例96　多条件查找（VLOOKUP） 225

案例97　提取姓名拼音的首字母（VLOOKUP） 226

案例98　用不确定条件查找（VLOOKUP） 227

案例99　使用通配符查找（VLOOKUP） 228

案例100　产生混合编号（MATCH） 231

案例101　将一维人事资料表转二维（MATCH） 232

案例102　根据姓名查找左边的身份证号（LOOKUP） 235

案例103　将姓名按拼音升序排列（LOOKUP） 236

案例104　计算每个职工的得分（LOOKUP） 237

案例105　查询业务员的负责地区（LOOKUP） 238

案例106　根据产量计算员工产量得分（LOOKUP） 239

案例107　区分工种和达标率计算奖金（LOOKUP） 240

案例108　根据不良率判断送货品处理办法（CHOOSE） 243

案例109　返回最高工资的地址（ADDRESS） 246

案例110　对车间和产量升序排列（ROW） 249

案例111 统计分公司数量（AREAS） 250

案例112 整理产量表（INDIRECT） 253

案例113　合并三个工作表的数据（INDIRECT） 254

第8章　信息函数 258

案例114　根据身份证号码汇总男、女职工总数（ISODD） 258

案例115　计算生产部人数和非生产部人数（ISERR） 261

案例116　累计每日得分（N） 262

案例117 设置D列只能录入男职工的姓名（VLOOKUP） 264

案例118　引用单价并去除干扰符…（ISERROR） 266

案例119　查询书籍在七年中的最高单价（ISNA） 268

案例120　判断错误类型（ERROR.TYPE） 270

第9章　财务函数 274

案例121 根据利率、存款与时间计算存款加利息数（FV） 274

案例122　计算七个投资项目在相同收益条件下谁投资更少（PV） 277

案例123　根据利息和存款数计算存款达到1万元需要几个月（NPER） 280

案例124　根据投资金额、时间和目标收益计算增长率（RATE） 283

案例125　根据贷款、利率和时间计算某段时间的利息（CUMIPMT） 286

案例126　根据贷款、利率和时间计算需偿还的本金（CUMPRINC） 289

案例127　以固定余额递减法计算资产折旧值（DB） 292

案例128 以双倍余额递减法计算资产折旧值（DDB） 295