章函数基础知识:Excel数据处理利器1
1.1函数基础1
1.2函数小技巧2
第2章基础函数:打好基础,轻松实现数据处理5
2.1文本函数5
2.1.1RIGHT:从右侧提取字符函数5
案例01使用RIGHT函数提取单元格中的字符5
2.1.2LEFT:从中间提取字符函数6
案例02使用LEFT函数提取单元格中的字符6
2.1.3MID:从中间提取字符函数7
案例03使用MID函数提取单元格中的字符7
2.1.4LEN:计算文本长度函数7
案例04使用LEN函数统计单元格中有多少个字符7
2.1.5LENB:文本长度计算函数(区分单双字节)8
案例05使用LENB函数统计单元格有几个字符8
案例06使用LEFT函数提取单元格中左边的汉字9
案例07使用RIGHT函数提取单元格中右边的数字9
2.1.6MIDB:从指定位置提取字符函数(区分单双字节)10
案例08使用MIDB函数从单元格中提取指定的字符10
2.1.7SEARCH:不区分大小写、支持通配符的查找函数10
案例09使用SEARCH函数查找指定字符的位置11
2.1.8SEARCHB:查找指定字符的位置函数(区分单双字节)11
案例10使用SEARCHB函数提取汉字中间的数字11
2.1.9FIND:区分大小写、不支持通配符的查找函数12
案例11使用FIND函数从字符串中查找某个字符串所在的位置12
2.1.10ASC:将全角双字节转换为半角单字节函数13
案例12使用LENB函数把逗号转为单字节13
2.1.11WIDECHAR:把单字节转换为双字节函数13
案例13使用WIDECHAR函数把单字节字符转换成双字节字符14
2.1.12CHAR:将数字转换为字符函数14
案例14使用CHAR函数自动填充26个大写字母14
2.1.13CODE:将字符转换为数字函数15
案例15使用CODE函数求字母A的ASCII码15
2.1.14UPPER:将小写字母转换为大写字母函数15
案例16使用UPPER函数把字母由小写转换为大写15
2.1.15REPLACE:查找和替换函数16
案例17使用REPLACE函数把银行卡号每隔4位加一个空格16
2.1.16TEXT:格式转换函数17
案例18用TEXT函数将0值屏蔽且保留1位小数。17
案例19用TEXT函数计算2008-8-8是星期几17
案例20使用TEXT函数根据分数判断成绩等级18
案例21使用TEXT函数将大于100的数值显示为100,将小于或等于100的数值显示为其本身19
2.1.17T:检测给定值是否为文本函数19
案例22使用T函数实现只保留文本20
2.1.18TRIM:清除空格字符专用函数20
案例23使用TRIM函数清除单元格两端的空格20
2.1.19SUBSTITUTE:按值替换函数21
案例24使用SUBSTITUTE函数统计单元格中有多少个“c”21
2.2查找引用函数21
2.2.1LOOKUP:查找函数21
案例25使用LOOKUP函数查找A列中最后一个数值22
案例26使用LOOKUP函数查找A列中的最后一个文本22
案例27使用LOOKUP函数查找A列中的最后一个值23
案例28使用LOOKUP函数根据分数判断成绩等级24
2.2.2MATCH:查找址函数24
案例29使用MATCH函数查找某一个值的位置25
案例30使用MATCH函数根据日期返回对应的季度25
案例31使用MATCH函数根据数值返回对应的位置26
案例32使用MATCH函数查找最后一个销售数量出现的位置26
2.2.3VLOOKUP:垂直查找函数27
案例33使用VLOOKUP函数根据姓名查找对应的销量27
案例34使用VLOOKUP函数根据学生分数判断成绩等级28
案例35使用VLOOKUP函数查找和数据源列中的字段顺序不一样的信息28
2.2.4HLOOKUP:水平查找函数29
案例36使用HLOOKUP函数根据月份查找销量29
2.2.5INDEX:引用函数①30
案例37使用INDEX函数引用单元格区域中的数值30
案例38使用INDEX函数实现反向查找31
案例39使用INDEX函数动态查询每门科目的总分数31
2.2.6OFFSET:引用函数②32
案例40使用OFFSET函数向下引用某个单元格中的内容32
案例41使用OFFSET函数向上引用某个单元格中的内容33
案例42使用SUM函数动态求每列的数量之和33
2.2.7INDIRECT:引用函数③34
案例43使用INDIRECT函数引用单元格中的值34
案例44使用INDIRECT函数根据工号查找姓名35
2.2.8CHOOSE:引用函数④35
案例45使用CHOOSE函数引用单元格中的值36
案例46更改列数据的位置36
案例47使用VLOOKUP函数轻松实现反向查找36
2.2.9ROW:返回行号函数37
案例48使用ROW函数输入26个英文字母37
案例49使用SUM和ROW函数求从1加到100的结果38
2.2.10COLUMN:返回列函数38
案例50使用COLUMN函数计算数值39
2.2.11ADDRESS:单元格地址函数39
案例51使用ADDRESS函数根据列号返回对应的字母39
2.2.12TRANSPOSE:转置函数40
案例52使用TRANSPOSE函数把单元格区域中的内容横向显示40
2.2.13HYPERLINK:超链接函数41
案例53使用HYPERLINK函数为单元格中的值设置超链接41
案例54使用HYPERLINK函数实现单元格之间的跳转41
2.3逻辑函数42
2.3.1IF:条件判断函数42
案例55使用IF函数判断学生成绩:小于60分为不及格,否则为及格42
案例56使用IF函数判断成绩43
2.3.2TRUE:逻辑真函数43
案例57求TRUE加TRUE等于几43
2.3.3FALSE:逻辑假函数44
案例58求TRUE+FALSE等于几?44
案例59使用IF函数把0值屏蔽44
2.3.4AND:检查所有参数是否为TRUE函数45
案例60使用AND函数判断学生成绩:如果三科成绩都大于或等于60就返回“通过”45
2.3.5OR:检查所有参数是否为FALSE函数45
案例61使用OR函数判断学生成绩46
2.3.6NOT:相反函数46
案例62使用NOT函数判断参数的逻辑值46
2.3.7IFERROR:屏幕错误值函数47
案例63使用IFERROR函数屏蔽公式中的错误值47
2.4日期和时间函数47
2.4.1YEAR:年函数47
案例64使用YEAR函数提取日期中的年份48
2.4.2MONTH:月函数48
案例65使用MONTH函数提取日期中的月份48
2.4.3DAY:日函数49
案例66使用DAY函数提取日期中的日49
2.4.4DATE:日期函数49
案例67使用DATE函数根据年、月、日返回“年-月-日”格式的日期49
2.4.5EOMONTH:月末函数50
案例68使用EOMONTH函数统计每个月有多少天50
2.4.6HOUR:时函数51
案例69使用HOUR函数提取时间中的小时数51
2.4.7MINUTE:分函数51
案例70使用MINUTE函数提取时间中的分钟数51
2.4.8SECOND:秒函数52
案例71使用SECOND函数提取时间中的秒数52
2.4.9NOW:系统时间函数52
案例72使用NOW函数返回当前日期和时间52
2.4.10TODAY:系统日期函数53
案例73使用TODAY函数返回当前日期53
2.4.11WEEKDAY:计算星期几函数53
案例74使用WEEKDAY函数高亮显示周六和周日54
2.4.12DATEDIF:日期处理函数55
案例75使用DATEDIF函数根据出生日期计算年纪56
2.5其他函数56
2.5.1AVERAGE:求平均值函数56
案例76使用AVERAGE函数求单元格区域的平均值57
2.5.2AVERAGEIF:单条件求平均值函数57
案例77使用AVERAGEIF函数求单元格区域中大于或等于某个值的值的平均值57
2.5.3AVERAGEIFS:多条件求平均值函数58
案例78求大于300且小于800的值的平均值58
2.5.4COUNT:计算数字个数函数59
案例79使用COUNT函数统计单元格区域中的数据有多少个为数值型59
2.5.5COUNTA:非空计数函数59
案例80统计单元格区域中非空单元格的个数60
2.5.6COUNTIF:单条件计数函数60
案例81使用COUNTIF函数统计字符60
2.5.7COUNTIFS:多条件计数函数61
案例82使用COUNTIFS函数统计业务员是“曹丽”且销量大于500的记录个数61
2.5.8SUM:求和函数62
案例83使用SUM函数求多个工作表单元格中的值的和,但不包括当前的工作表62
2.5.9SUMPRODUCT:计算乘积之和函数62
案例84使用SUMPRODUCT函数求产品名称是A且型号是大号的产品数量63
2.5.10PRODUCT:计算所有参数的乘积函数63
案例85计算体积63
2.5.11SUMIF:单条件求和函数64
案例86使用SUMIF函数汇总数据64
2.5.12SUMIFS:多条件求和函数64
案例87使用SUMIFS函数多条件求产品数量65
2.5.13MIN:最小值函数65
案例88使用MIN函数判断单元格中的数值65
2.5.14MAX:优选值函数66
案例89使用MAX函数判断单元格中的数值66
2.5.15SMALL:返回第k个最小值函数67
案例90使用SMALL函数升序排序单元格中的数值67
2.5.16LARGE:返回第k个优选值函数67
案例91使用LARGE函数降序排序单元格区域中的数值67
2.5.17SUBTOTAL:分类汇总函数68
案例92使用SUBTOTAL函数给隐藏的行自动编号68
2.5.18ROUND:四舍五入函数69
案例93使用ROUND函数将单元格区域中的数值保留两位小数69
2.5.19ROUNDDOWN:向下舍入函数69
案例94使用ROUNDDOWN函数保留一位小数,不进行四舍五入,全部舍弃69
2.5.20ROUNDUP:向上舍入函数70
案例95使用ROUNDUP函数保留一位小数,不进行四舍五入,全部进入70
2.5.21CEILING:按倍数向上进位函数70
案例96使用CEILING函数进行数值舍入:十分位不足0.5就按0.5算,大于或等于0.5就向上进171
2.5.22FLOOR:按倍数向下舍入函数71
案例97使用FLOOR函数进行数值舍入,十分位不足0.5就按向下舍去,大于或等于0.5就按0.5处理71
2.5.23INT:取整函数72
案例98使用INT函数把日期提取出来72
2.5.24MOD:取余函数73
案例99使用MOD函数求余数73
2.5.25REPT:重复函数73
案例100使用REPT函数制作符号编号73
2.5.26N:将非数值型数值转换为数值型数值函数74
案例101使用N函数将数值进行转换74
2.5.27ABS:取绝对值函数75
案例102使用ABS函数求绝对值75
2.5.28CELL:获取单元格信息的函数75
案例103使用CELL函数获取工作簿路径及工作表信息76
2.5.29ISNUMBER:检测是否为数值型数字函数76
案例104使用ISNUMBER函数判断数值型数值76
2.5.30ISTEXT:检测一个值是否为文本函数77
案例105使用ISTEXT函数判断文本型数值77
2.5.31PHONETIC:另类文本字符连接函数77
案例106使用PHONETIC函数连接文本77
2.5.32RAND:取随机小数函数78
案例107使用RAND函数生成随机数78
2.5.33RANDBETWEEN:取随机整数函数78
案例108使用RANDBETWEEN函数生成指定大小的随机整数79
2.5.34MODE:取众数函数79
案例109使用MODE函数判断出现次数最多的数值79
2.6初级函数综合案例80
案例110使用TEXT函数把秒数转换为分钟数80
案例111为什么使用SUMIF函数的求和结果是081
案例112对比两张表中的数据83
案例113判断奇偶行的两种方法85
案例114使用SUMIF函数遇到通配符时如何解决86
案例115提取单元格中靠左侧的汉字87
案例116从汉字中提取数字的最简单的方法88
案例117求A1:C1的和(简单的数值相加为什么会报错)88
案例118使用LOOKUP函数实现反向查找89
案例119比IF函数还经典的判断用法90
案例120引用每个表的C列中的最后一个值92
案例121将单元格中的内容进行分列93
案例122求18:00―23:00有几个小时94
案例123为什么使用SUM函数无法求和95
案例124提取小括号里的数据95
案例125每隔4行提取数据组成新的一列97
案例126最简单的分类汇总方法98
案例127判断汉字和字母99
案例128用VLOOKUP函数实现多表查找100
案例129将一列数据快速转换为两列数据101
案例130根据产品名返回最后一次进价101
案例131提取最后一个月的数据102
案例132为什么使用VLOOKUP函数得不到正确的结果103
案例133为什么使用SUMPRODUCT函数得不到正确的结果104
案例134如何实现“六舍七入”105
案例135动态获取当前工作表名称107
案例136Excel中的两个通配符的用法108
案例137ROW函数与ROWS函数的区别108
案例138如何把“2017-10-20”转换为“20171020”111
案例139为什么公式=IF(2,3,4)返回3112
案例140隐藏0值112
案例141计算表达式113
案例142如何把“2017.8.30”转换成“2017年8月30日”114
第3章中级函数:实现批量数据处理115
3.1数组115
3.2中级数组函数经典案例118
案例143用数组求1~100的和118
案例144用数组求文本中的数字之和119
案例145使用MID函数求单元格中的数字之和119
案例146使用LEN函数统计单元格区域中有多少个字母A119
案例147使用RIGHT函数提取单元格中右边的数字120
案例148使用SUMIF函数求张三和李四的销量之和120
案例149使用VLOOKUP函数求每个员工上半年和下半年的销量之和121
案例150使用COUNTIF函数统计字符共出现多少次121
案例151使用MATCH函数统计不重复值的个数122
案例152使用FIND函数查找最后一个“/”的位置123
案例153使用FIND函数查找单元格中个数字出现的位置123
案例154使用数组根据日期返回对应的季度124
案例155使用数组隔行求和124
案例156使用数组引用每一行单元格中的最后一个数据125
案例157使用数组引用每一行单元格中的个数据125
案例158使用数组统计超过15位数字的个数126
案例159使用MID函数提取单元格中最后一个逗号后面的数据126
案例160使用MID函数从中英文中提取数字127
案例161使用IF+VLOOKUP函数实现反向查找127
案例162使用CHOOSE函数实现反向查找128
案例163使用COUNTIF函数统计大于100且小于200的数字个数128
案例164双条件查找的7种方法129
案例165使用INDEX函数实现一对多查询并且纵向显示结果132
案例166使用INDEX函数实现一对多查询并且横向显示结果133
案例167实现一对多查询并且将结果用顿号分隔133
案例168LOOKUP+FIND函数的经典组合应用134
案例169单列去重135
案例170多列去重135
案例171中国式排名136
案例172美国式排名137
案例173多工作表汇总137
案例174目录制作138
案例175VLOOKUP函数的参数数组用法139
案例176计算体积139
案例177把9*20*30中的数字分别提取到3个单元格中140
案例178将多列转为一列140
案例179将一列转为多列141
案例180用全称匹配简称141
案例181用简称匹配全称142
案例182使用LOOKUP函数实现多条件查找142
案例183对合并单元格按条件求和143
案例184查找最后一次出现的位置对应的值144
案例185双条件计数144
案例186如何生成序列145
案例187引用合并单元格中的数据145
案例188从汉字中提取数字146
案例189将通话记录里的分和秒相加146
案例190使用COUNTIF函数统计不连续列中的字符个数147
案例191使用COUNTIF函数统计不重复值的个数147
案例192使用COUNT函数统计不重复值的个数且排除空单元格148
3.3中级函数149
案例193根据身份证号提取户籍所在地址149
案例194根据身份证号提取出生日期149
案例195根据身份证号提取性别150
案例196根据身份证号计算年龄150
案例197根据名称显示照片151
案例198使用VLOOKUP函数制作工资条152
案例199将周末高亮显示152
案例200使用定义名称功能+INDIRECT函数实现二级下拉菜单154
案例201使用数组公式实现二级下拉菜单155
案例202将TEXT函数当IF函数用155
案例203为银行卡号每隔4位加空格156
案例204动态求每周的销量156
案例205设置七天内生日提醒157
案例206使序号随着筛选而自动编号158
案例207给合并单元格编号158
案例208不显示错误值的3种方法159
案例209TEXT函数十!的用法159
案例210计算经过多少个工作日完成任务160
案例211向下和向右填充公式生成26个字母161
案例212提取括号里的数据161
案例213计算一个日期为当月的第几周162
案例214隔列求和的3种方法163
案例215取得单元格的列号164
案例216筛选在19:00―23:00范围内的时间164
案例217判断某月有多少天165
案例218获取当前工作表的名称165
案例219输出4位数,不足4位在左边加0166
案例220限制单元格中只能输入15位或者18位字符167
第4章不错函数:Excel函数不错验法168
4.1MMULT:矩阵乘积函数168
案例221使用MMULT函数求各科成绩总和169
案例222通过MMULT函数求每一个人的总分169
案例223使用MMULT函数单条件求和170
案例224使用MMULT函数实现多行多列查找171
案例225找出每个销售员销量优选的4个数值171
案例226按数量生成姓名172
4.2FREQUENCY:频率函数173
案例227使用FREQUENCY函数统计分数出现的频率173
案例228使用FREQUENCY函数统计不重复值的个数174
案例229使用FREQUENCY函数实现去重174
案例230合并单元格条件求和175
4.3降维函数176
案例231使用N函数降维求奇数行的和176
案例232使用T函数降维动态求和177
案例233使用SUMIF函数进行降维汇总多个工作表178
案例234使用SUBTOTAL函数降维实现隔列求和178
案例235为何MATCH函数会报错?179
4.4加权函数180
案例236提取多段数字180
案例237动态引用每一列单元格中的最后一个值并求和180
4.5不错函数径变案例181
案例238把月份数转为“#年#月”的格式181
案例239提取单元格中的数字再相乘182
案例240使用SUBSTITUTE函数根据身份证号计算年龄是几岁几个月183
案例241VLOOKUP函数的参数为数组的用法184
案例242使用VLOOKUP函数实现一对多查询184
案例243使用MATCH和MID函数找到单元格中个出现的数字185
案例244如何给LOOKUP函数构建参数186
案例245把小括号里的数字相加186
案例246提取多段数字并放在多个单元格中187
案例247将文字和数字分开188
案例248提取多段数字且求和189
第5章Excel中常用技巧:提高数据处理效率191
5.1个技巧:批量填充191
5.2第2个技巧:批量填充上一个单元格中的内容191
5.3第3个技巧:把不规范的日期转为规范的日期192
5.4第4个技巧:自动为单元格添加边框193
5.5第5个技巧:使单元格中的内容自动适合列宽194
5.6第6个技巧:批量快速定义单元格区域名称195
5.7第7个技巧:Tab键的妙用195
5.8第8个技巧:设置文档自动保存时间196
5.9第9个技巧:从身份证号码中提取出生日期196
5.100个技巧:制作斜线表头197
5.111个技巧:计算文本表达式198
5.122个技巧:冻结单元格199
5.133个技巧:标示单元格中的重复值200
5.144个技巧:给工作簿加密201
5.155个技巧:使用快捷键Ctrl+快速对比两列数据202
5.166个技巧:使用快捷键Alt+=一键求和202
5.177个技巧:快速合并单元格中的内容203
5.188个技巧:隔列复制数据203
5.199个技巧:输入当前的日期和时间204
5.20第20个技巧:数值和日期之间的转换204
5.21第21个技巧:妙用快捷键F4隔行插入空行205
5.22第22个技巧:使用快捷键F4切换单元格引用方式205
5.23第23个技巧:输入1显示“男”;输入2显示“女”206
5.25第24个技巧:显示和隐藏Excel的功能区206
5.26第25个技巧:跨列居中优于合并单元格207
5.27第26个技巧:如何输入以0开头的数字208
5.28第27个技巧:通过自定义单元格格式快速录入数据208
5.29第28个技巧:快速跳转到数据列的最后一个单元格209
5.30第29个技巧:让每一页工作表打印出来都有标题行209
5.31第30个技巧:把“*”替换成“×”210
5.32第31个技巧:使用快捷键Alt+↓快速弹出下拉菜单211
5.33第32个技巧:按所选内容进行筛选211
5.34第33个技巧:使用快捷键Ctrl+D复制上一个单元格中的内容212
5.35第34个技巧:在多个工作表中批量输入212
5.36第35个技巧:设置单元格区域保护213
5.37第36个技巧:设置数值以万为单位214
5.38第37个技巧:如何让复制的表格列宽不变215
5.39第38个技巧:快速打开“选择性粘贴”对话框216
5.40第39个技巧:快速添加边框216
5.41第40个技巧:快速删除边框217
|
商品评价
100%好评
共有0人参与评分评价商品