网易云课堂上有王佩丰老师的Excel教学视频,以下是链接地址。
跟王佩丰学Excel视频教程:Excel实战1800分钟 - 网易云课堂study.163.com![6a2ee28ce789fe2db0c70d74dd9798c0.png](https://i-blog.csdnimg.cn/blog_migrate/04919c92cfad782e7220048cdc4a1cd9.jpeg)
第九讲 COUNTIF函数
COUNT函数——计算包含数字的单元格个数以及参数列表中数字的个数
COUNT(value1, [value2], ...)
value1:要计算其中数字的个数的第一项、单元格引用或区域
value2:要计算其中数字的个数的其他项、单元格引用或区域
如果参数为数字、日期或者代表数字的文本(例如,用引号引起的数字,如 “1”),则将被计算在内
COUNTIF函数——统计符合指定的条件的单元格的数量
COUNTIF(range, criteria)
range:要进行计数的单元格组。可以包括数字、数组、命名区域或包含数字的引用。空白和文本值将被忽略。
criteria:用于决定要统计哪些单元格的数量的数字、表达式、单元格引用或文本字符串。
COUNTIF函数写大于小于这样的条件时,要用双引号括起来,比如”>500”
对于银行卡号这种大于15位的数字,像下面这样运用COUNTIF函数会出错,COUNTIF最多判断15位的数字。 要得出正确的结果,需要将COUNTIF函数中的条件A2
改为A2&“*”
,相当于将数字格式改为文本格式
![e7d8334013466a7b014747b2faeb50a8.png](https://i-blog.csdnimg.cn/blog_migrate/71a83e5795caa2adb9a094e16ad3caf3.jpeg)
注意,将B2单元格向下填充,CONTIF函数中的单元格区域会改变,应该将A2:A3改为绝对引用$A$2:$A$3
![fd89d5f966c11215d209c6597fa43fe7.png](https://i-blog.csdnimg.cn/blog_migrate/d91a58916cd35154eea691e2b3f3ac60.jpeg)
COUNTIF函数的应用
![b3a61311e084c9cd60ba9557cfd902f2.png](https://i-blog.csdnimg.cn/blog_migrate/ef10b9a88c975af27924bed9603fc737.jpeg)
需要找出学生名单中已参加体检的和没有参加体检的人员名单
方法1
IF函数和COUNTIF函数嵌套
![2d260b697360855c994031a67bf77440.png](https://i-blog.csdnimg.cn/blog_migrate/cefa5a62e75dbb7e1b6959094ca24075.jpeg)
方法2
选中所有的学生名单 开始——条件格式——新建规则,样式选经典,使用公式确定要设置格式的单元格,在下方填入公式=COUNTIF(G:G,A2)=0
。没有参加体检的人的名字被标记为红色。后续如果在已参加体检人员名单中增加姓名,比如王晓冬,学生名单中王晓东的名字会变成白色,是否体检会变为是,表格可以自动更新
![6287b885b38209a74f88514ce20ccfeb.png](https://i-blog.csdnimg.cn/blog_migrate/d6deafe02c28d4b8c4d2095628534c8e.jpeg)
统计符合多个条件的单元格的数量——COUNTIFS
COUNTIFS(条件区域1,条件1,[条件区域2,条件2],……)
第十讲 SUMIF函数和SUMIFS函数
SUMIF函数
SUMIF(条件区域,条件,求和区域)
SUMIFS函数
SUMIFS(求和区域,条件区域1,条件1,条件区域2,条件2,……)
第十一讲 VLOOKUP函数
VLOOKUP函数——在表中查找值
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
lookup_value:要查找的值,必须位于table-array中指定的单元格区域的第一列中
table_array:VLOOKUP 在其中搜索lookup_value和返回值的单元格区域。 该单元格区域中的第一列必须包含lookup_value,此单元格区域中还需要包含您要查找的返回值
col_index_num:其中包含返回值的单元格的编号(table-array最左侧单元格为 1 开始编号)
range_lookup:一个逻辑值,该值指定希望VLOOKUP查找近似匹配还是精确匹配。TRUE表示模糊匹配,FALSE表示精确匹配,默认为TRUE
VLOOKUP跨表引用
VLOOKUP跨表引用方式与同一张表引用方式相同,在table_array前会出现跨表的名称
通配符查找
假设要查找工作表1中一家公司的地址,公司名称为“三川实业有限公司”,查找的结果放在工作表2中。在工作表2中使用VLOOKUP函数,但表2中的公司名称为“三川实业”,在写VLOOKUP函数时,lookup_value应写为A2&“*“
(假设公司名称在A2单元格)。这种情况仍然是精确匹配,range_lookup=0
VLOOKUP模糊匹配
![acf71ec800acb0f5853a73aa65998ac6.png](https://i-blog.csdnimg.cn/blog_migrate/853391c00200874ba28b3525c34fd70c.jpeg)
假设要计算每个人的提成比例,使用VLOOKUP函数的模糊匹配,会找到排序之后不大于这个值的最接近的值,也就是说,白桦的提成比例是10%而不是15%,虽然36835855离4000000比离2000000更近
第十二讲 VLOOKUP嵌套MATCH返回多列
MATCH函数和INDEX函数
VLOOKUP函数集成了查找和引用两个功能,而MATCH函数和INDEX函数分别具有查找和引用的功能。
MATCH函数——返回匹配值在lookup_array中的位置
MATCH(lookup_value, lookup_array, [match_type])
lookup_value:要在lookup_array中匹配的值
lookup_array:要搜索的单元格区域
match_type:数字 -1、0 或 1,查找方式。1或省略,MATCH查找小于或等于lookup_value的最大值;0,MATCH查找完全等于lookup_value的第一个值;-1,MATCH查找大于或等于lookup_value的最小值
INDEX函数——返回表格或数组中的元素值,此元素由行号和列号的索引值给定
INDEX(array, row_num, [column_num])
Array:单元格区域或数组常量
Row_num:选择数组中的某行,函数从该行返回数值
Column_num:选择数组中的某列,函数从该列返回数值
VLOOKUP函数的缺点与解决办法
VLOOKUP函数中 lookup_value,即要查找的值,必须位于table-array中指定的单元格区域的第一列中,如果要查找的值所在的列位于返回值所在的列的右侧,则VLOOKUP函数无法进行查找。比如有以下这张表1
![da8005e2e2e7316c4ddb9c6cf79ec9a8.png](https://i-blog.csdnimg.cn/blog_migrate/897f63af5fbca4567446a2d2f9072440.jpeg)
现在要在表2中根据公司名称查找客户ID
![3ff9b73be7ac8befda90a942357366ce.png](https://i-blog.csdnimg.cn/blog_migrate/f7fc7155cf5fef52360e16e60a5648e1.jpeg)
方法1:要搜索的单元格区域table_array=IF({1,0},B:B,A:A)
。利用IF({1,0}, )
将A列和B列的顺序调换
方法2:先利用MATCH函数查找出表2中的公司名称位于表1中B列的什么位置,然后将该返回值作为INDEX函数的参数返回表1中A列相应的位置 =INDEX(表1!A:A,MATCH(表2!A2,表1!B:B,0))
VLOOKUP函数嵌套MATCH函数返回多列
假设有下表3,要根据表1的内容返回多列
![b091b26bc9899d6ffeaebc467df17ac2.png](https://i-blog.csdnimg.cn/blog_migrate/b78df1bc30954f82266badfd9727362d.jpeg)
关键是col_index_num要根据列名称的不同返回不同的值。可以在col_index_num中使用MATCH函数,并使用绝对引用和相对引用,注意VLOOKUP中的$A3和MATCH中的B$2。公式为=VLOOKUP($A3,表1!$A:$K,MATCH(表3!B$2,表1!$A$1:$K$1,0),0)
利用INDEX和MATCH函数返回照片
一般查询函数只能返回值,如果要返回照片,比如在I、J列中选取人的姓名,返回其性别、年龄和照片,可以利用INDEX、MATCH和相机功能。 先假设照片处是一个数值,用INDEX函数和MATCH函数写出如何查询,=INDEX(Sheet1!$D$2:$D$5,MATCH(Sheet1!$J$2,Sheet1!$A$2:$A$5,0))
,然后在公式选项卡中点击定义名称,新建一个名叫照片的公式,将该公式复制进去,然后任意点击一个空单元格,利用新建选项卡中的相机(需要在设置中自己调出来)生成一个空单元格的快照,将该快照放入J5单元格中,点击它,在编辑栏中输入=照片
,即可以返回照片
![60710148b6c58d1388a09373ec58c63d.png](https://i-blog.csdnimg.cn/blog_migrate/34bfe3c6c16969213742281cedd72517.jpeg)
第十三讲 邮件合并
假设有这样一张表1,里面是各个公司的相关信息。现在要给每个公司发一封邮件,内容相同,只是公司、联系人这些特征参数不同。可以在Word中使用邮件合并功能
![b447a0dceda0c6a29b18acb548ecebd0.png](https://i-blog.csdnimg.cn/blog_migrate/0c8a88f49f7ac9e1c3b5e70a33f9b1e9.jpeg)
邮件—选择收件人—使用现有列表,选择表1,插入合并域中会出现公司名称、联系人姓名等条目,选择相应的条目即可
开始邮箱合并选择信函,可以批量生成多个文档,每个文档中是一条记录;选择电子邮件,可以批量发送多个邮件;选择目录,可以在每页显示多条记录,比如有一个包含题目和选项的Excel文件,利用它在Word中生成一张试卷
注意:针对Mac系统,如果点击完成并合并选项,合并到电子邮件为灰色,请打开系统的邮件客户端,在偏好设置中将默认的电子邮箱软件改为Outlook,并重启电脑
如果在邮件合并时要更改格式,比如数字要加千位分隔符,选中该数字,按ALT+F9
(Windows)或者Option+F9
(Mac),会显示域代码,如{ MERGEFIELD “奖金” }
。在Excel中,通过设置单元格格式中的自定义,可以查看域代码的写法。将奖金的域代码改成{ MERGEFIELD “奖金” # “#,##0”}
,域代码改完之后按F9键更新域,这样就可以更改想要的格式。不同的东西域代码不同,比如数字、日期等的域代码写法不一样,请自行搜索
![caea3408800ceddc449ff543577270c7.png](https://i-blog.csdnimg.cn/blog_migrate/3d25bbeceab9e083b9b36657ba5c2abb.jpeg)
第十四讲 日期与时间运算
Excel中的时间表示方法
假如有一个数值,2.5,将它表示成年-月-日 时-分-秒的格式,会得到1900年1月2日 12:00:00,Excel会将整数部分表示为距离1900年1月1日的天数,将小数部分表示为该时间占一天24小时的多少。 因此,要看两个日期相距多少天,直接将这两个日期相减即可。 要看9:00和12:00相距多少分钟,(12:00-9:00)*24*60
即可
和时间有关的函数
YEAR(日期)—日期的年份;MONTH(日期)—日期的月份;DAY(日期)—日期的天数;DATE(年,月,日)—输入年、月、日,返回日期
DATEDIF函数——计算天数、 月或两个日期之间的年数
DATEDIF(start_date,end_date,unit)
start_date——起始日期
end_date——结束日期
unit——要返回的信息类型:”Y”,返回整年数;”M”,返回整月数;”D”,返回天数;”MD”,返回天数之差,忽略年份和月份,比如两个日期相差1年2个月3天,则返回3;”YM”,返回月份之差,忽略年份;”YD”,返回天数之差,忽略年份
WEEKNUM函数和WEEKDAY函数——返回一个日期是那一年的第几周和第几天
WEEKNUM(serial_number,[return_type])
serial_number—日期
return_type—1或省略,一周的第一天为周日;2,一周的第一天为周一
WEEKDAY(serial_number,[return_type])
serial_number—日期
return_type—1或省略,数字 1(星期日)到 7(星期六);2,数字 1(星期一)到 7(星期日)
TEXT函数——通过格式代码向数字应用格式,进而更改数字的显示方式
TEXT(value, format_text)
value—要转换为文本的数值
format_text—一个文本字符串,定义要应用于所提供值的格式。
TEXT(“2013/03/10”,”aaaa”)
:得到“星期六”这个文本(“aaaa”将日期转换为星期几) TEXT(“20130616”, “0000-00-00”)*1
:得到2013/06/16这个日期(0表示数字占位符)
第十五讲 条件格式与公式
简单的条件格式
利用开始菜单中的条件格式工具,可以将特定范围的值、重复值、文本中包含特殊字段的值等一些满足特定条件的值标记为特殊颜色
数据条
![ff6d23cda2a1593dea9b98a31bb44f9e.png](https://i-blog.csdnimg.cn/blog_migrate/f3538154ba7afb79682a45e84e8a6704.jpeg)
利用左边的表生成了右边的数据
![af3dce1c79ef1530310570c385a00376.png](https://i-blog.csdnimg.cn/blog_migrate/e8a0b44eb3ec508dc7d2b16f02510c91.jpeg)
利用条件格式中的数据条,可以用可视化的方法看到每个数据的大小
![8b875e8322bde02bf4fcb1d9cecc7bdc.png](https://i-blog.csdnimg.cn/blog_migrate/424ca8ef4bffb015b69ff5e579ff3949.jpeg)
利用插入选项卡中的切片器功能,可以在数据透视表的基础上进一步对数据进行筛选
使用公式定义条件格式
假如有下表1,要把数量大于100的订购日期的单元格的背景颜色变为红色
![d0be80cb9d2b1d3fdf9e6b9517e45c41.png](https://i-blog.csdnimg.cn/blog_migrate/131a6e86fc496a083d357389908b3c4b.jpeg)
选中A2至A22单元格,在条件格式的新建规则中,选择使用公式确定要设置格式的单元格,公式中输入=D2>100
即可
第十六讲 简单文本函数
截取字符串函数
LEFT函数——从文本字符串的第一个字符开始返回指定个数的字符
LEFT(text, [num_chars])
text:包含要提取的字符的文本字符串
num_chars:指定要由 LEFT提取的字符的数量,缺省的话默认值为1
RIGHT函数——从文本字符串的最后一个字符开始返回指定个数的字符
RIGHT(text, [num_chars])
text:包含要提取的字符的文本字符串
num_chars:指定要由RIGHT提取的字符的数量,缺省的话默认值为1
MID函数——返回文本字符串中从指定位置开始的特定数目的字符
MID(text, start_num, num_chars)
text:包含要提取的字符的文本字符串
start_num:文本中要提取的第一个字符的位置,第一个字符值为1
num_chars:指定希望 MID 从文本中返回字符的个数
定位函数
FIND函数——在第二个文本串中定位第一个文本串,并返回第一个文本串的起始位置的值(区分大小写)
FIND(find_text, within_text, [start_num])
find_text:要查找的文本
within_text:包含要查找文本的文本
start_num:指定开始进行查找的字符,省略的话默认为1
计算长度函数
LEN函数——返回文本字符串中的字符个数
LENB函数——返回文本字符串中用于代表字符的字节数(中文一个字符有两个字节)
eg. LEN(张三)=2
LENB(张三)=4
如果一个字符串a中有中文、英文、数字混合的,那么LENB(a)-LEN(a)
就等于a中中文字符的个数