王佩丰excel课件_王佩丰Excel 2010视频教程笔记(9-16讲)

网易云课堂上有王佩丰老师的Excel教学视频，以下是链接地址。

跟王佩丰学Excel视频教程：Excel实战1800分钟 - 网易云课堂​study.163.com

在网页版左下角的参考资料里可以下载每节课的课件

第九讲 COUNTIF函数

COUNT函数——计算包含数字的单元格个数以及参数列表中数字的个数

COUNT(value1, [value2], ...)
value1：要计算其中数字的个数的第一项、单元格引用或区域
value2：要计算其中数字的个数的其他项、单元格引用或区域

如果参数为数字、日期或者代表数字的文本（例如，用引号引起的数字，如 “1”），则将被计算在内

COUNTIF函数——统计符合指定的条件的单元格的数量

COUNTIF(range, criteria)
range：要进行计数的单元格组。可以包括数字、数组、命名区域或包含数字的引用。空白和文本值将被忽略。
criteria：用于决定要统计哪些单元格的数量的数字、表达式、单元格引用或文本字符串。

COUNTIF函数写大于小于这样的条件时，要用双引号括起来，比如”>500”

对于银行卡号这种大于15位的数字，像下面这样运用COUNTIF函数会出错，COUNTIF最多判断15位的数字。 要得出正确的结果，需要将COUNTIF函数中的条件A2改为A2&“*”，相当于将数字格式改为文本格式

注意，将B2单元格向下填充，CONTIF函数中的单元格区域会改变，应该将A2:A3改为绝对引用$A$2:$A$3

COUNTIF函数的应用

需要找出学生名单中已参加体检的和没有参加体检的人员名单

方法1

IF函数和COUNTIF函数嵌套

方法2

选中所有的学生名单 开始——条件格式——新建规则，样式选经典，使用公式确定要设置格式的单元格，在下方填入公式=COUNTIF(G:G,A2)=0。没有参加体检的人的名字被标记为红色。后续如果在已参加体检人员名单中增加姓名，比如王晓冬，学生名单中王晓东的名字会变成白色，是否体检会变为是，表格可以自动更新

统计符合多个条件的单元格的数量——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模糊匹配

假设要计算每个人的提成比例，使用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

表1

现在要在表2中根据公司名称查找客户ID

表2

方法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的内容返回多列

关键是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单元格中，点击它，在编辑栏中输入=照片，即可以返回照片

第十三讲 邮件合并

假设有这样一张表1，里面是各个公司的相关信息。现在要给每个公司发一封邮件，内容相同，只是公司、联系人这些特征参数不同。可以在Word中使用邮件合并功能

表1

邮件—选择收件人—使用现有列表，选择表1，插入合并域中会出现公司名称、联系人姓名等条目，选择相应的条目即可

开始邮箱合并选择信函，可以批量生成多个文档，每个文档中是一条记录；选择电子邮件，可以批量发送多个邮件；选择目录，可以在每页显示多条记录，比如有一个包含题目和选项的Excel文件，利用它在Word中生成一张试卷

注意：针对Mac系统，如果点击完成并合并选项，合并到电子邮件为灰色，请打开系统的邮件客户端，在偏好设置中将默认的电子邮箱软件改为Outlook，并重启电脑

如果在邮件合并时要更改格式，比如数字要加千位分隔符，选中该数字，按ALT+F9(Windows)或者Option+F9(Mac)，会显示域代码，如{ MERGEFIELD “奖金” }。在Excel中，通过设置单元格格式中的自定义，可以查看域代码的写法。将奖金的域代码改成{ MERGEFIELD “奖金” # “#,##0”}，域代码改完之后按F9键更新域，这样就可以更改想要的格式。不同的东西域代码不同，比如数字、日期等的域代码写法不一样，请自行搜索

第十四讲 日期与时间运算

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表示数字占位符）

第十五讲 条件格式与公式

简单的条件格式

利用开始菜单中的条件格式工具，可以将特定范围的值、重复值、文本中包含特殊字段的值等一些满足特定条件的值标记为特殊颜色

数据条

利用左边的表生成了右边的数据

利用条件格式中的数据条，可以用可视化的方法看到每个数据的大小

利用插入选项卡中的切片器功能，可以在数据透视表的基础上进一步对数据进行筛选

使用公式定义条件格式

假如有下表1，要把数量大于100的订购日期的单元格的背景颜色变为红色

表1

选中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中中文字符的个数