第04讲（第四章） Excel数据分类与处理

模块一：EXCEL技术基础第四章数据分类与处理（二）上节回顾知识点回顾单元格引用：相对引用、绝对引用、混合引用、跨表引用、跨工作簿引用名称引用：名称定义、修改和引用数据筛选：三种数据筛选方式（自动筛选、自定义筛选、高级筛选）分类汇总：按字段分类，并统计汇总问题：例举每种引用有何不同，写法用法？问题：例举名称的使用？问题：哪三种筛选方式，其中高级筛选能实现哪些功能？问题：汇总制作步骤？上节回顾高级筛选复习提问性别为男，或工资>3000条件区域：性别为男，且工资>3000条件区域：性别为男且工资>3000，或性别为女且工资>5000条件区域：上节回顾复习练习1.完成人员销售量的统计：相对引用，绝对引用，混合引用2.完成冠军销量统计：跨表引用，名称的使用其中1－12月使用跨表引用全年使用名称框3.完成销售额的统计：跨簿引用其中每月单价在文件“知识回顾：单价”中。4.查找筛选1月销量>=80，或12月销量>=85的人员名单10分钟目录页CONTENTSPAGE2.时间函数3.文本函数4.其他函数1.日期函数本节知识点日期函数：TODAY,YEAR,MONTH,DAY,

NETWORKDAYS,DATE时间函数：NOW,HOUR,MINITUE,SECOND文本函数：UPPER,LOWER,PROPER,MID,LEFT,RIGHT,FIND其他函数：YEARFARC日期函数1日期函数Today（）Year（）Networkdays（）Date（）返回日期格式的当前日期返回日期的年份值返回两个日期之间的完整工作日返回excel日期时间代码中代表日期的数字日期函数1.日期函数：TODAY()返回日期格式的当前日期当前日期是Excel日期和时间计算使用的日期-时间代码。如果在输入函数前，单元格的格式为“常规”，Excel会将单元格格式自动更改为“日期”。如果要查看序列号，则必须将单元格格式更改为“常规”或“数值”。TODAY函数也可以用于计算时间间隔。TODAY()参数引用：公式说明（结果）=TODAY()返回日期格式的当前日期（2020/03/30）例1：在登记表上，设置自动标上当天的制表日期。日期函数返回日期的年份值。以此可类推MONTH、DAY函数。Serial_number为一个日期值，其中包含要查找年份的日期。应使用DATE函数输入日期，或者将函数作为其他公式或函数的结果输入。YEAR()参数引用：2.年份函数：YEAR(serial_number)公式说明（结果）=YEAR(B2)第一个日期的年份（2016）=YEAR(C2)第一个日期的年份（2018）=YEAR(C2)-YEAR(B2)2018-2016=2例2：求各工程竣工历时多少年，已完工多久。日期函数实训任务一5分钟知识点日期时间函数todayyear完成实验报告，题1工龄计算；原文件详见工作簿“01：日期函数”。日期函数返回参数start-date和end-date之间完整的工作日（不包括周末和专门指定的假期）NETWORKDAYS(start_date，end_date，holidays)Start_date代表开始日期。End_date代表终止日。Holidays是表示不在工作日历中的一个或多个日期所构成的可选区域，法定假日以及其他非法定假日。此数据清单可以是包含日期的单元格区域，也可以是由代表日期的序列号所构成的数组常量。3.工作日函数：Networkdays（）公式说明（结果）=NETWORKDAYS(A2,A3)开始日期-终止日期，实际工作日=NETWORKDAYS(A2,A3,A4)扣除第一个假日=NETWORKDAYS(A2,A3,A4:A11)扣除所有假日日期函数例3：求某企业员工工作天数。3.工作日函数：Networkdays（）日期函数实训任务一5分钟知识点日期时间函数networkdays完成实验7，题2工作日计算；原文件详见工作簿“01：日期函数”。日期函数返回excel日期时间代码中代表日期的数字DATE（year,month,day）Year参数year可以为1到4位数字。Month代表每年中月份的数字。如果所输入的月份大于12，将从指定年份的一月份开始往上加算。例如：DATE(2018,14,2)返回代表2019年2月2日的序列号。Day代表在该月份中第几天的数字。如果day大于该月份的最大天数，则将从指定月份的第一天开始往上累加。例如，DATE(2018,1,35)返回代表2018年2月4日的序列号。4.日期函数：Date（）例4：计划撰写一本西藏游记完成时间。日期函数实训任务一5分钟知识点日期时间函数DATE日期减法完成实验7，题3-5生产天数计算等；原文件详见工作簿“01：日期函数”。时间函数2时间函数Second（）Minute（）Hour（）时间函数1.时间函数：Second函数返回时间值的秒数。SECOND(serial_number)Serial_number必需。表示一个时间值，其中包含要查找的秒数。时间有多种输入方式：带引号的文本字符串（例如"6:45PM"）、十进制数（例如0.78125表示6:45PM）或其他公式或函数的结果（例如TIMEVALUE("6:45PM")）。返回的秒数为0到59之间的整数。时间函数2.时间函数：Minute函数返回时间值的分钟数。MINUTE(serial_number)Serial_number必需。表示一个时间值，其中包含要查找的分钟数。返回的分数为0到59

之间的整数。时间函数3.时间函数：Hour函数返回时间值的小时数。HOUR(serial_number)Serial_number必需。表示一个时间值，其中包含要查找的小时数。返回的小时数为0到23

之间的整数。时间函数实训任务二5分钟知识点日期时间函数日期减法secondminutehour完成实验7，题1运动会成绩计算；原文件详见工作簿“02：时间函数”。时间函数实训任务二8分钟知识点日期时间函数日期减法secondminuteHourIf函数完成实验7，题2停车费计算；原文件详见工作簿“02：时间函数”。3文本函数文本函数Proper（）Upper（）Lower（）Mid（）Mod（）Len（）Find（）Left（）Right（）Reft（）文本函数1.文本函数：Proper函数将文本字符串的首字母及任何非字母字符之后的首字母转换成大写，将其余的字母转换成小写。PROPER(text)text必需。用引号括起来的文本、返回文本值的公式或是对包含文本（要进行部分大写转换）的单元格的引用。=PROPER(B2)文本函数2.文本函数：Upper函数将文本转换为大写字母。UPPER(text)text必需。用引号括起来的文本、返回文本值的公式或是对包含文本（要进行部分大写转换）的单元格的引用。=UPPER(B2)文本函数3.文本函数：Lower函数将文本转换为小写字母。LOWER(text)text必需。用引号括起来的文本、返回文本值的公式或是对包含文本（要进行部分大写转换）的单元格的引用。=LOWER(D2)文本函数4.文本函数：Mid函数返回文本字符串中从指定位置开始的特定数目的字符。MID(text,start_num,num_chars)text:为包含要提取字符的文本字符串。start_num:为文本中要提取的第一个字符的位置。文本中第一个字符的start_num为1，以此类推。num_chars:指定希望MID从文本中返回字符的个数。=MID(B2,7,8)文本函数5.取模函数：Mod函数返回两数相除的余数。MOD(number,divisor)Number：为被除数。Divisor：为除数。如果divisor为零，函数MOD返回错误值#DIV/0!。结果的正负号与除数相同。=IF(MOD(MID(B2,17,1),2)=0,"女","男")文本函数实训任务三10分钟知识点文本函数properupperlowermidmod完成实验报告，1身份证信息处理；原文件详见工作簿“03：文本函数”。文本函数1.文本函数：Len函数返回文本字符串中的字符个数。LEN(text)text：为要计算其长度的文本字符串；包括空格。=LEN(A2)文本函数2.文本查找函数：Find函数用于查找其他文本字符串(within_text)内的文本字符串(find_text)，并从within_text的首字符开始返回find_text的起始位置编号。FIND(find_text,within_text,start_num)Find_text：是要查找的文本。Within_text：是包含要查找文本的文本。Start_num：指定开始进行查找的字符。如果忽略start_num，则假设其为1。FIND函数区分大小写并且不允许使用通配符。=FIND(B1,D2,1)文本函数3.文本函数：Left函数从文本字符串的开头返回指定数目的字符。LEFT(text,num_chars)text：包含要提取的字符的文本字符串，或对包含文本的列的引用。num_chars：（可选）您希望LEFT提取的字符的数目；如果省略，则为1。=LEFT(D2,4)=LEFT(B8,FIND("-",B8)-1)=MID(B8,1,FIND("-",B8)-1)文本函数4.文本函数：Right函数从文本字符串的末尾开始返回指定数目的字符。RIGHT(text,num_chars)text：包含要提取的字符的文本字符串，或对包含文本的列的引用。num_chars：（可选）您希望RIGHT提取的字符的数目；如果省略，则为1。=RIGHT(B12,LEN(B12)-FIND("-",B12))=MID(B12,FIND("-",B12)+1,LEN(B12)-FIND("-",B12))文本函数实训任务三10分钟知识点文本函数LenFindLeftRightMid完成实验报告，2电话号码处理；原文件详见工作簿“03：文本函数”。文本函数1.文本函数：Rept函数按照定义的次数重复显示文本。REPT（text，number_times)text：表示需要重复显示的文本number_times：表示指定文本重复显示的次数=REPT("|",100*B2/$B$18)文本函数实训任务三6分钟知识点文本函数/查找函数REPTLOOKUP完成实验报告，3全年生产统计；原文件详见工作簿“03：文本函数”。4其他函数其他函数1.效益函数：Yearfrac函数判别某一特定条件下全年效益或债务的比例。YEARFRAC（tstart_date,end_date,basis)Start_date：表示开始日期;End_date：代表结束日期。函数中的日期有多种输入方式：带引号的文本串(如"1998/01/30")、序列号(如35829表示1900日期系统中的1998年1月30日)，或其他公式或函数的结果(例如DATevalUE("1998/1/30"))。Basis：表示日计数基准类型，其中：0或省略为US(NASD)30/3601实际天数/实际天数2实际天数/3603实际天数/3654欧洲30/360。=YEARFRAC(B2,C2,2)其他函数实训任务四5分钟知识点