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

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

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

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

第九讲 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

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

fd89d5f966c11215d209c6597fa43fe7.png

COUNTIF函数的应用

b3a61311e084c9cd60ba9557cfd902f2.png

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

方法1

IF函数和COUNTIF函数嵌套

2d260b697360855c994031a67bf77440.png

方法2

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

6287b885b38209a74f88514ce20ccfeb.png

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

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

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

3ff9b73be7ac8befda90a942357366ce.png
表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的内容返回多列

b091b26bc9899d6ffeaebc467df17ac2.png

关键是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

第十三讲 邮件合并

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

b447a0dceda0c6a29b18acb548ecebd0.png
表1

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

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

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

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

caea3408800ceddc449ff543577270c7.png

第十四讲 日期与时间运算

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

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

af3dce1c79ef1530310570c385a00376.png

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

8b875e8322bde02bf4fcb1d9cecc7bdc.png

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

使用公式定义条件格式

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

d0be80cb9d2b1d3fdf9e6b9517e45c41.png
表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中中文字符的个数

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值