Excel2010应用

1、 张桂香张桂香Excel 2010Excel 20102ExcelExcel应用技术应用技术之之应用篇应用篇3/143一、基本函数(补充)二、完善学生信息表三、 加减法运算测试四、货物出库单的制作主要内容4/143一、基本函数(补充) 日期和时间函数日期和时间函数 TODAYTODAY函数函数 功能功能 返回当前日期的序列号。如果在输入函数前，单元格的格返回当前日期的序列号。如果在输入函数前，单元格的格式为式为“常规常规”，则结果为日期格式。，则结果为日期格式。 语法格式为：语法格式为：TODAYTODAY（）。（）。 示例示例 = =TODAY()TODAY() 得到计算机系统的当前日期。得

2、到计算机系统的当前日期。 说明说明 TODAYTODAY函数是一个无参函数，但是在应用时候后面的左函数是一个无参函数，但是在应用时候后面的左右括号不能漏掉右括号不能漏掉。5/143一、基本函数(补充)日期和时间函数日期和时间函数 YEARYEAR函数函数 功能功能 返回某日期对应的年份，返回值为返回某日期对应的年份，返回值为 1900 1900 到到 9999 9999 之间的整之间的整数。数。 语法格式为：语法格式为：YEAR(Serial_number)YEAR(Serial_number)。 其中的参数其中的参数Serial_numberSerial_number为一个日期值，其中包含要

3、查找为一个日期值，其中包含要查找年份的日期。年份的日期。 示例示例 = =YEAR(2008/5/12)YEAR(2008/5/12) 返回返回20082008。6/143一、基本函数(补充)日期和时间函数日期和时间函数 MONTHMONTH函数函数 功能功能 返回指定日期中的月份，返回值是介于返回指定日期中的月份，返回值是介于1 1到到1212之间的整数。之间的整数。其语法格式为：其语法格式为：MONTH(Serial_number)MONTH(Serial_number)，参数同，参数同YEARYEAR函函数中的参数意义一样。数中的参数意义一样。 示例示例 = =MONTH(2008/5/

4、12)MONTH(2008/5/12) 返回返回5 57/143一、基本函数(补充)日期和时间函数日期和时间函数 DAYDAY函数函数 功能功能 是返回指定日期的天数，返回值用整数是返回指定日期的天数，返回值用整数 1 1 到到 31 31 表示。表示。其语法格式为：其语法格式为：DAY(Serial_number)DAY(Serial_number)，参数同，参数同YEARYEAR函数中函数中的参数意义一样。的参数意义一样。 示例示例 = =DAY(2008/5/12)DAY(2008/5/12) 返回返回1212。8/143一、基本函数(补充)日期和时间函数日期和时间函数 DATEDATE

5、函数函数 功能功能 返回代表特定日期的序列号。返回代表特定日期的序列号。 其语法格式为：其语法格式为：DATE(Year,Month,Day)DATE(Year,Month,Day) 其中的三个参数中：其中的三个参数中： YearYear为年份，也可以为年份的序列号，用一到四位数字表为年份，也可以为年份的序列号，用一到四位数字表示。如果所输入的年份小于示。如果所输入的年份小于19001900，将输入的数据与，将输入的数据与19001900相加。相加。 MonthMonth为月份，如果所输入的月份大于为月份，如果所输入的月份大于1212，将从指定年份，将从指定年份下一年的一月份开始往上加算。下一

6、年的一月份开始往上加算。 DayDay代表在该月份中的天数，如果代表在该月份中的天数，如果 day day 大于该月份的最大大于该月份的最大天数，则将从指定月份的下一月的第一天开始往上累加。天数，则将从指定月份的下一月的第一天开始往上累加。9/143一、基本函数(补充)日期和时间函数日期和时间函数 DATEDATE函数函数 示例示例 在有些数据库中，日期的年、月、日数字可能分为三个字在有些数据库中，日期的年、月、日数字可能分为三个字段来保存，当导入到段来保存，当导入到ExcelExcel工作表后，就可以利用工作表后，就可以利用DATEDATE函函数将年、月、日合并成一个具体的日期数据。假设单元

7、格数将年、月、日合并成一个具体的日期数据。假设单元格A1A1是年份数字，是年份数字，B1B1是月份数字，是月份数字，C1C1是日数，则公式是日数，则公式“= =DATEDATE（A1A1，B1B1，C1C1）”就将就将A1A1的年份数字，的年份数字，B1B1的月份的月份数字和数字和C1C1的日数，合并到一起组成了一个具体的日期数据。的日数，合并到一起组成了一个具体的日期数据。 10/143一、基本函数(补充)日期和时间函数日期和时间函数 WEEKDAYWEEKDAY函数函数 功能功能 返回某日期的星期数。返回某日期的星期数。 默认情况下，其值为默认情况下，其值为 1 1（星期天）到（星期天）到

8、 7 7（星期六）之间的（星期六）之间的整数。整数。 其语法格式为：其语法格式为：WEEKDAY(Serial_numberWEEKDAY(Serial_number，Return_type)Return_type) 其中参数其中参数Serial_numberSerial_number为日期序列号为日期序列号, ,可以是日期数据或可以是日期数据或是对日期数据单元格的引用。是对日期数据单元格的引用。 参数参数Return_typeReturn_type用来确定返回值类型的数字。用来确定返回值类型的数字。11/143一、基本函数(补充)日期和时间函数日期和时间函数 WEEKDAYWEEKDAY函数

9、函数 当参数当参数Return_typeReturn_type选选1 1或确省或确省时：时： 1 1表示星期日，表示星期日，2 2表示星期一，表示星期一，7 7表示星期六表示星期六 当参数当参数Return_typeReturn_type选选2 2时：时： 1 1表示星期一，表示星期一，2 2表示星期二，表示星期二，7 7表示星期日表示星期日 当参数当参数Return_typeReturn_type选选3 3时：时： 0 0表示星期一，表示星期一，1 1表示星期二，表示星期二，6 6表示星期日表示星期日 12/143一、基本函数(补充)日期和时间函数日期和时间函数 WEEKDAYWEEKDAY

10、函数函数示例示例 20132013年年9 9月月1717日是星期二，则：日是星期二，则：= =WEEKDAY(2013/9/17) WEEKDAY(2013/9/17) 返回返回“3” 3”； = =WEEKDAY(2013/9/17,2) WEEKDAY(2013/9/17,2) 返回返回“2” 2”； = =WEEKDAY(2013/9/17,3) WEEKDAY(2013/9/17,3) 返回返回“1” 1”。 13/143一、基本函数(补充)日期和时间函数日期和时间函数 NOWNOW函数函数 功能功能 返回当前日期和时间的序列号。如果在输入函数前，单元返回当前日期和时间的序列号。如果在

11、输入函数前，单元格的格式为格的格式为“常规常规”，则结果为日期格式。其语法格式为：，则结果为日期格式。其语法格式为：NOWNOW（）。（）。 示例示例 输入公式输入公式 = =NOW()NOW() 都得到计算机系统的当前时间都得到计算机系统的当前时间:2013-3-11 19:58:2013-3-11 19:58。 说明说明 NOWNOW函数同函数同TODAYTODAY函数一样，也是一个无参函数，但应函数一样，也是一个无参函数，但应用时后面括号不能漏掉用时后面括号不能漏掉14/143一、基本函数(补充)日期和时间函数日期和时间函数 TIMETIME函数函数 功能功能 返回某一特定时间的值。返回

12、某一特定时间的值。 如果在输入函数前，单元格的格式为如果在输入函数前，单元格的格式为“数值数值”，则结果为，则结果为 0,1) 0,1) 之间的数值，代表从之间的数值，代表从 0:00:00 (12:00:00 AM) 0:00:00 (12:00:00 AM) 到到 23:59:59 23:59:59 (11:59:59 PM) (11:59:59 PM) 之间的时间。之间的时间。 如果在输入函数前，单元格的格式为如果在输入函数前，单元格的格式为“常规常规”，则结果将，则结果将设为日期格式。设为日期格式。 其语法格式为：其语法格式为：TIMETIME（hour,minute,secondho

13、ur,minute,second） 15/143一、基本函数(补充)文本函数文本函数 TEXTTEXT函数函数 功能功能 将数值转换为按指定数字格式表示的文本。将数值转换为按指定数字格式表示的文本。 其语法格式为：其语法格式为：TEXTTEXT（value,format_textvalue,format_text） 其中包括两个参数，意义分别为：其中包括两个参数，意义分别为： ValueValue为数值为数值 format_textformat_text为所要选用的文本型数字格式。为所要选用的文本型数字格式。 示例示例 如果工作表中如果工作表中A1A1单元格的数值为单元格的数值为5232986

14、.638 5232986.638 ，则公式，则公式“= =TEXT(TEXT(A1A1,#,#0.00),#,#0.00)返回字符串返回字符串 “5,232,986.64”5,232,986.64”。 16/143一、基本函数(补充)文本函数文本函数 TEXTTEXT函数函数 说明说明 使用使用“单元格格式单元格格式”对话框的对话框的“数字数字”选项卡设置单元格选项卡设置单元格格式，只会改变单元格的格式而不会影响其中的数值。而格式，只会改变单元格的格式而不会影响其中的数值。而使用使用TEXTTEXT函数可以将数值转换为带格式的文本，而其结果函数可以将数值转换为带格式的文本，而其结果将不再作为数

15、字参与计算将不再作为数字参与计算。 17/144一、基本函数(补充)文本函数文本函数利用函数将利用函数将A列转换列转换为文本数据为文本数据18/144一、基本函数(补充)文本函数文本函数将将B列粘贴到列粘贴到D列列(仅粘数值仅粘数值)19/144一、基本函数(补充)文本函数文本函数通过通过“单元格格式单元格格式”修修改改D列的数据类型列的数据类型20/144一、基本函数(补充)文本函数文本函数D列的数据是数列的数据是数值吗？值吗？21/144一、基本函数(补充)文本函数文本函数输入公式输入公式=SUM(D1:D13)结果是结果是“0”说明说明什么问题？什么问题？22/144一、基本函数(补充)

16、文本函数文本函数选中选中D列列23/144一、基本函数(补充)文本函数文本函数24/143一、基本函数(补充)文本函数文本函数结果是结果是“0”说明说明什么问题？什么问题？输入公式输入公式=SUM(C1:D1)25/144一、基本函数(补充)文本函数文本函数=TEXT(A1,0.0)=TEXT(A1,0.0)26/143一、基本函数(补充)文本函数文本函数=TEXT(A1=TEXT(A1* *100,dbnum10100,dbnum10万万0 0千千0 0百百0 0十十0 0元元0 0角角0 0分分)=TEXT(A1=TEXT(A1* *100,dbnum20100,dbnum20万万0 0千

17、千0 0百百0 0十十0 0元元0 0角角0 0分分)=TEXT(A1=TEXT(A1* *100,dbnum30100,dbnum30万万0 0千千0 0百百0 0十十0 0元元0 0角角0 0分分)=TEXT(A1=TEXT(A1* *100,dbnum40100,dbnum40万万0 0千千0 0百百0 0十十0 0元元0 0角角0 0分分)27/143一、基本函数(补充)文本函数文本函数数字转换的另数字转换的另一个函数：一个函数：=NUMBERSTRING(A1,1)=NUMBERSTRING(A1,1)=NUMBERSTRING(A1,2)=NUMBERSTRING(A1,2)=NU

18、MBERSTRING(A1,3)=NUMBERSTRING(A1,3)28/143一、基本函数(补充)文本函数文本函数 例例. .从身份证号码中提取身份信息。从身份证号码中提取身份信息。=MID(B2,7,4)&=MID(B2,7,4)&年年&MID(B2,11,2)&MID(B2,11,2)&月月&MID(B2,13,2)&MID(B2,13,2)&日日 目录目录29/143二、完善学生信息表 在原在原“学生信息表学生信息表”的基础上增加的基础上增加“身份证号身份证号”、“出生日期出生日期”和和“计算机入学测试成绩计算机入学测试

19、成绩”等三个字段。等三个字段。30/143二、完善学生信息表31/143二、完善学生信息表同时：同时：1. 按按“计算机入学测试成绩计算机入学测试成绩”降序排列；降序排列； 2. 按按“籍贯籍贯”分类统计人数；分类统计人数； 3. 只显示只显示“河南省河南省”考生考生“入学测试成绩入学测试成绩”在在 60分以上的学生。分以上的学生。32/143二、完善学生信息表2.1 2.1 字符函数字符函数 2.2 2.2 逻辑函数逻辑函数2.3 2.3 引用函数引用函数33/143二、完善学生信息表2.1 2.1 字符函数字符函数 LENLEN函数函数 功能功能 返回文本字符串中的字符数。返回文本字符串中

20、的字符数。 其语法格式为：其语法格式为：LEN(text)LEN(text) 其中参数其中参数texttext为要查找其长度的文本，空格也将作为字符为要查找其长度的文本，空格也将作为字符进行计数。进行计数。 示例示例 如果如果A1A1单元格中字符串为单元格中字符串为“电脑电脑 爱好者爱好者”，则公式，则公式“= =LEN(A1)”LEN(A1)”返回返回6 6。 如果如果A1A1单元格中字符串为单元格中字符串为“abcd”abcd”，则公式，则公式“= =LEN(A1)”LEN(A1)”返回返回4 4。34/143二、完善学生信息表2.1 2.1 字符函数字符函数 LEFTLEFT函数函数 功

21、能功能 根据所指定的字符数从文本字符串左边取出前几个字符。根据所指定的字符数从文本字符串左边取出前几个字符。也就是说，也就是说，LEFTLEFT函数对字符串进行函数对字符串进行“左截取左截取”。 其语法格式为：其语法格式为：LEFTLEFT（text,num_charstext,num_chars） 其中参数其中参数TextText是要提取字符的文本字符串；是要提取字符的文本字符串； Num_charsNum_chars是是指定提取的字符个数，它必须大于或等于指定提取的字符个数，它必须大于或等于0 0。如果省略参数。如果省略参数 num_charsnum_chars，默认值为，默认值为 1 1

22、。如果参数。如果参数 num_chars num_chars 大于文本长大于文本长度，则度，则LEFTLEFT函数返回所有文本。函数返回所有文本。 示例示例 = =LEFT(LEFT(电脑爱好者电脑爱好者,2),2) 返回字符串返回字符串“电脑电脑”。35/143二、完善学生信息表2.1 2.1 字符函数字符函数 RIGHTRIGHT函数函数 功能功能 根据所指定的字符数从文本字符串右边取出前几个字符。根据所指定的字符数从文本字符串右边取出前几个字符。也就是说，也就是说，LEFTLEFT函数对字符串进行函数对字符串进行“右截取右截取”。 其语法格式为：其语法格式为：RIGHTRIGHT（tex

23、t,num_charstext,num_chars） 其中参数其中参数TextText是要提取字符的文本字符串；是要提取字符的文本字符串； Num_charsNum_chars是指定提取的字符个数，它必须大于或等于是指定提取的字符个数，它必须大于或等于0 0。如果省略参。如果省略参数数 num_charsnum_chars，默认值为，默认值为 1 1。如果参数。如果参数 num_chars num_chars 大于文大于文本长度，则本长度，则RIGHTRIGHT函数返回所有文本。函数返回所有文本。 示例示例 =RIGHT(=RIGHT(电脑爱好者电脑爱好者,3),3) 返回字符串返回字符串“爱

24、好者爱好者”。36/143二、完善学生信息表2.1 2.1 字符函数字符函数 MIDMID函数函数 功能功能 返回文本字符串中从指定位置开始的特定数目的字符，该返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。数目由用户指定。 其语法格式为：其语法格式为：MID(text,start_num,num_chars)MID(text,start_num,num_chars) 其中参数其中参数texttext为要提取字符的文本字符串；为要提取字符的文本字符串；start_numstart_num为要为要提取的第一个字符的位置（文本中第一个字符的位置为提取的第一个字符的位置（文本中第一

25、个字符的位置为 1 1，其余依此类推）；其余依此类推）；num_charsnum_chars为指定提取的字符个数。为指定提取的字符个数。 示例示例 = =MID(MID(电脑爱好者电脑爱好者,3,2),3,2) 返回字符串返回字符串“爱好爱好”。37/143二、完善学生信息表2.1 2.1 字符函数字符函数 例例. .电话号码升位的方法是：由电话号码升位的方法是：由“1 1”、“9 9”开头的特开头的特服号不变，其余的在原号码前加服号不变，其余的在原号码前加“2 2”。38/143二、完善学生信息表2.1 2.1 字符函数字符函数 例例. .电话号码升位的方法是：由电话号码升位的方法是：由“1

26、 1”、“9 9”开头的特开头的特服号不变，其余的在原号码前加服号不变，其余的在原号码前加“2 2”。=IF(OR(LEFT(A2)=1,LEFT(A2)=9),A2,“2&A2)=IF(OR(LEFT(A2)=1,LEFT(A2)=9),A2,“2&A2)39/143二、完善学生信息表2.1 2.1 字符函数字符函数 例例. .电话号码升位的方法是：由电话号码升位的方法是：由“1 1”、“9 9”开头的特开头的特服号不变，其余的在原号码前加服号不变，其余的在原号码前加“2 2”。40/143二、完善学生信息表2.1 2.1 字符函数字符函数 如果电话号码升如果电话号码升位中，

27、如果不考虑特位中，如果不考虑特服号，操作还可以这服号，操作还可以这样样：41/143二、完善学生信息表2.1 2.1 字符函数字符函数 如果电话号码升如果电话号码升位中，如果不考虑特位中，如果不考虑特服号，操作还可以这服号，操作还可以这样样：将将A列的数据粘列的数据粘贴到贴到B列列42/143二、完善学生信息表2.1 2.1 字符函数字符函数 如果电话号码升如果电话号码升位中，如果不考虑特位中，如果不考虑特服号，操作还可以这服号，操作还可以这样样：将将B列的数据调列的数据调整为整为“数值数值”43/143二、完善学生信息表2.1 2.1 字符函数字符函数 如果电话号码升如果电话号码升位中，如果

28、不考虑特位中，如果不考虑特服号，操作还可以这服号，操作还可以这样样：选中选中B列，打开列，打开“单元格格式单元格格式”对对话框话框44/143二、完善学生信息表2.1 2.1 字符函数字符函数 如果电话号码升如果电话号码升位中，如果不考虑特位中，如果不考虑特服号，操作还可以这服号，操作还可以这样样：显示的数据已显示的数据已经被修改！经被修改！45/143二、完善学生信息表2.1 2.1 字符函数字符函数 例例. .从身份证号码中提取身份信息。从身份证号码中提取身份信息。46/143二、完善学生信息表2.1 2.1 字符函数字符函数 例例. .从身份证号码中提取身份信息。从身份证号码中提取身份信

29、息。=IF(MOD(MID(B2,17,1),2)=0,=IF(MOD(MID(B2,17,1),2)=0,女女,男男) MODMOD函数函数 功能功能 返回两个数相除的余数。返回两个数相除的余数。 其语法格式为：其语法格式为：MODMOD（Number,DivisorNumber,Divisor） 其中包括两个参数，意义分别为：其中包括两个参数，意义分别为： Number, Number,被除数被除数 Divisor Divisor，除数，除数 示例示例 “= =MODMOD(3,5)”,(3,5)”,返回的值为：返回的值为：3 3 “= =MODMOD(5,3)”,(5,3)”,返回的值为

30、：返回的值为：2 2二、完善学生信息表2.1 2.1 字符函数字符函数 例例. .从身份证号码中提取身份信息。从身份证号码中提取身份信息。47/1431818位身份证号码的含义：位身份证号码的含义：所在省、市、县所在省、市、县出生年、月、日出生年、月、日123456789101112131415161718派出所派出所性别性别校验位校验位奇数为男生，奇数为男生，偶数为女生偶数为女生48/143二、完善学生信息表2.1 2.1 字符函数字符函数 例例. .从身份证号码中提取身份信息。从身份证号码中提取身份信息。49/143二、完善学生信息表2.1 2.1 字符函数字符函数 例例. .从身份证号码

31、中提取身份信息。从身份证号码中提取身份信息。50/143二、完善学生信息表2.2 2.2 逻辑函数逻辑函数 AND (logical1,logical2, )AND (logical1,logical2, ) OR (logical1,logical2,) OR (logical1,logical2,) 参数参数Logical1, logical2, Logical1, logical2, 表示待测试的条件值或表达式，表示待测试的条件值或表达式，其结果可为其结果可为 TRUE TRUE 或或 FALSEFALSE，最多不超过，最多不超过3030个。个。 当使用当使用ANDAND函数时，只有当运

32、算对象都为函数时，只有当运算对象都为TRUETRUE的情况下，的情况下，运算结果才为运算结果才为TRUETRUE，否则为，否则为FALSEFALSE； 当执行当执行OROR运算时，只有当运算对象都为运算时，只有当运算对象都为FALSEFALSE的情况下，的情况下，运算结果才为运算结果才为FALSEFALSE，否则为，否则为TRUETRUE51/143二、完善学生信息表2.2 2.2 逻辑函数逻辑函数例例 确定每位学生的确定每位学生的“评优资格评优资格”（不能有不及格的课程）。（不能有不及格的课程）。52/143二、完善学生信息表2.2 2.2 逻辑函数逻辑函数例例 确定每位学生的确定每位学生的

33、“评优资格评优资格”（不能有不及格的课程）。（不能有不及格的课程）。在在“G3”单元格输入判断的条件单元格输入判断的条件“AND(C3=60,D3=60,E3=60,F3=60)”输入条件为输入条件为“true”时的值时的值“Y”输入条件为输入条件为“false”时的值时的值“N”53/143二、完善学生信息表2.2 2.2 逻辑函数逻辑函数例例 确定每位学生的确定每位学生的“评优资格评优资格”（不能有不及格的课程）。（不能有不及格的课程）。快速填充54/143二、完善学生信息表2.2 2.2 逻辑函数逻辑函数例例 确定每位学生的确定每位学生的“评优资格评优资格”（不能有不及格的课程）。（不能

34、有不及格的课程）。如何让如何让“Y“值的单值的单元格清晰显示元格清晰显示?(黄色填充黄色填充)55/143二、完善学生信息表2.2 2.2 逻辑函数逻辑函数例例 确定每位学生的确定每位学生的“评优资格评优资格”（不能有不及格的课程）。（不能有不及格的课程）。通过通过“条件格式条件格式”！56/143二、完善学生信息表2.2 2.2 逻辑函数逻辑函数例例 确定每位学生的确定每位学生的“评优资格评优资格”（不能有不及格的课程）。（不能有不及格的课程）。57/143二、完善学生信息表2.2 2.2 逻辑函数逻辑函数例例 确定每位学生的确定每位学生的“评优资格评优资格”（不能有不及格的课程）。（不能有

35、不及格的课程）。58/143二、完善学生信息表2.2 2.2 逻辑函数逻辑函数例例 确定每位学生的确定每位学生的“评优资格评优资格”（不能有不及格的课程）。（不能有不及格的课程）。59/143二、完善学生信息表2.2 2.2 逻辑函数逻辑函数例例 请依据请依据“生日生日”数据生成数据生成“年龄年龄”字段的值，并在生日字段的值，并在生日当天，在当天，在“生日祝词生日祝词”字段中显示字段中显示“* * *岁生日快乐！岁生日快乐！”。60/143二、完善学生信息表2.2 2.2 逻辑函数逻辑函数例例 请依据请依据“生日生日”数据生成数据生成“年龄年龄”字段的值，并在生日字段的值，并在生日当天，在当天

36、，在“生日祝词生日祝词”字段中显示字段中显示“* * *岁生日快乐！岁生日快乐！”。=YEAR(TODAY()-YEAR(B2)=YEAR(TODAY()-YEAR(B2)61/143二、完善学生信息表2.2 2.2 逻辑函数逻辑函数例例 请依据请依据“生日生日”数据生成数据生成“年龄年龄”字段的值，并在生日字段的值，并在生日当天，在当天，在“生日祝词生日祝词”字段中显示字段中显示“* * *岁生日快乐！岁生日快乐！”。62/143二、完善学生信息表2.2 2.2 逻辑函数逻辑函数例例 请依据请依据“生日生日”数据生成数据生成“年龄年龄”字段的值，并在生日字段的值，并在生日当天，在当天，在“生

37、日祝词生日祝词”字段中显示字段中显示“* * *岁生日快乐！岁生日快乐！”。=IF(AND(MONTH(TODAY()=MONTH(B2),DAY(TODAY()=DAY(B2),C2&=IF(AND(MONTH(TODAY()=MONTH(B2),DAY(TODAY()=DAY(B2),C2&岁生日快乐岁生日快乐,),)完成后，向下完成后，向下“快速填充快速填充“！63/143二、完善学生信息表2.2 2.2 逻辑函数逻辑函数例例 请依据请依据“生日生日”数据生成数据生成“年龄年龄”字段的值，并在生日字段的值，并在生日当天，在当天，在“生日祝词生日祝词”字段中显示字段中显示“

38、* * *岁生日快乐！岁生日快乐！”。假如今天是假如今天是3月月28日日64/143二、完善学生信息表2.3 2.3 引用函数引用函数格式：格式：VLOOKUP(VLOOKUP(查找值，查找数组，指定列，选项查找值，查找数组，指定列，选项) ) HLOOKUP( HLOOKUP(查找值，查找数组，指定行，选项查找值，查找数组，指定行，选项) ) 功能：返回功能：返回“查找数组查找数组”中第一列（行）与中第一列（行）与“查找值查找值”相相同的同的“指定列（行）指定列（行）”的值。的值。 “选项选项”缺省、缺省、1 1、TRUETRUE，模糊查找，要求有序；，模糊查找，要求有序； 0 0、FALS

39、EFALSE，精确查找，支持无序。，精确查找，支持无序。 如果如果“查找值查找值”没有出现在没有出现在“查找数组查找数组”中，则中，则 返回返回“#N/A”#N/A”。65/143二、完善学生信息表2.3 2.3 引用函数引用函数例如：例如：依据依据“奖励奖励表表”的信息完成的信息完成“奖励奖励”字段的填字段的填写。写。66/143二、完善学生信息表2.3 2.3 引用函数引用函数例如：例如：依据依据“奖励奖励表表”的信息完成的信息完成“奖励奖励”字段的填字段的填写。写。=VLOOKUP(D3,=VLOOKUP(D3,奖励表奖励表!A$3:B$8,2,0)!A$3:B$8,2,0)向下填充向下

40、填充至至G1267/143二、完善学生信息表2.3 2.3 引用函数引用函数例如：例如：依据依据“奖励奖励表表”的信息完成的信息完成“奖励奖励”字段的填字段的填写。写。68/143二、完善学生信息表2.3 2.3 引用函数引用函数例如：例如：依据依据“奖励奖励表表”的信息完成的信息完成“奖励奖励”字段的填字段的填写。写。=VLOOKUP(D3,=VLOOKUP(D3,奖励表奖励表!A$3:B$32,2,0)!A$3:B$32,2,0)向下填充向下填充至至G12修改各表。增加一个修改各表。增加一个“积分积分”项，前项，前30名都有积分，同名都有积分，同时限定选前时限定选前30名。名。69/143

41、二、完善学生信息表2.3 2.3 引用函数引用函数例如：例如：依据依据“奖励奖励表表”的信息完成的信息完成“奖励奖励”字段的填字段的填写。写。当对应的值为空的时候当对应的值为空的时候将返回将返回“0”0”( (因为引用时的第一个因为引用时的第一个值是数值值是数值) )70/143二、完善学生信息表2.3 2.3 引用函数引用函数例如：例如：依据依据“奖励奖励表表”的信息完成的信息完成“奖励奖励”字段的填字段的填写。写。=VLOOKUP(D3,=VLOOKUP(D3,奖励表奖励表!A$3:B$32,2,0)&!A$3:B$32,2,0)&为了避免为了避免“0”0”的的出现，公式中

42、添加出现，公式中添加&71/143二、完善学生信息表2.3 2.3 引用函数引用函数例如：例如：依据依据“奖励奖励表表”的信息完成的信息完成“奖励奖励”字段的填字段的填写。写。“0”值不显示的处值不显示的处理也可以通过理也可以通过“选项选项”设置设置72/143二、完善学生信息表2.3 2.3 引用函数引用函数例如：例如：依据依据“奖励奖励表表”的信息完成的信息完成“积分积分”字段的填字段的填写。写。=VLOOKUP(D3,=VLOOKUP(D3,奖励表奖励表!A$3:C$32,3)!A$3:C$32,3)“积分积分”项的项的计算类似计算类似“奖奖励励”73/143二、完善学生信息表3

43、.1 3.1 完善表格完善表格 插入两列增加一列74/143二、完善学生信息表3.2 3.2 完善数据完善数据 身份证号身份证号18位，位，可以通过可以通过“数据有效数据有效性性”控制！控制！“出生日期出生日期”可以通过可以通过“身份证号身份证号”获得！获得！75/143二、完善学生信息表1818位身份证号码的含义：位身份证号码的含义：所在省、市、县所在省、市、县出生年、月、日出生年、月、日123456789101112131415161718派出所派出所性别性别校验位校验位奇数为男生，奇数为男生，偶数为女生偶数为女生3.2 3.2 完善数据完善数据 76/143二、完善学生信息表3.2 3.

44、2 完善数据完善数据 =MID(D4,7,4)&=MID(D4,7,4)&年年&MID(D4,11,2)&MID(D4,11,2)&月月&MID(D4,13,2)&MID(D4,13,2)&日日 77/143二、完善学生信息表3.2 3.2 完善数据完善数据 快速填充78/143二、完善学生信息表3.2 3.2 完善数据完善数据 =IF(MOD(MID(D4,17,1),2)=0,=IF(MOD(MID(D4,17,1),2)=0,女女,男男)79/143二、完善学生信息表3.2 3.2 完善数据完善数据 “测试成绩测试成绩”可以

45、从可以从“全校测试成绩表全校测试成绩表”中中获得！获得！80/143二、完善学生信息表3.2 3.2 完善数据完善数据 =VLOOKUP(K4,2013=VLOOKUP(K4,2013计算机分级考试成绩计算机分级考试成绩.xls.xls全校全校( (含艺术含艺术)!$A$3:$D$5876,4,0)!$A$3:$D$5876,4,0)81/143二、完善学生信息表3.2 3.2 完善数据完善数据 快速填充82/143二、完善学生信息表3.3 3.3 按按“成绩成绩”排序排序 在建立工作表时，记录（或称数据）是按照输入顺序在建立工作表时，记录（或称数据）是按照输入顺序排列的，通常没有规律。排列的

46、，通常没有规律。 要查看满足某些特定条件的记录不是很方便。为此，要查看满足某些特定条件的记录不是很方便。为此，可以对工作表中的记录进行排序，从而提高查找效率。可以对工作表中的记录进行排序，从而提高查找效率。 排序是对一个工作表中的全部记录进行的。排序是对一个工作表中的全部记录进行的。83/143 选中数据区域，通过选中数据区域，通过“数据数据”菜单下菜单下“排序和筛选排序和筛选”组组中的中的“排序排序”命令，打开命令，打开“排序排序”对话框。在对话框。在“主要关键字主要关键字”下拉列表中选择下拉列表中选择“计算机入学测试成绩计算机入学测试成绩”，在，在“排序依据排序依据”中选择中选择“数值数值

47、”，在，在“次序次序”中选择中选择“降序降序”。二、完善学生信息表3.3 3.3 按按“成绩成绩”排序排序 84/143 选中数据区域，通过选中数据区域，通过“数据数据”菜单下菜单下“排序和筛选排序和筛选”组组中的中的“排序排序”命令，打开命令，打开“排序排序”对话框。在对话框。在“主要关键字主要关键字”下拉列表中选择下拉列表中选择“计算机入学测试成绩计算机入学测试成绩”，在，在“排序依据排序依据”中选择中选择“数值数值”，在，在“次序次序”中选择中选择“降序降序”。二、完善学生信息表3.3 3.3 按按“成绩成绩”排序排序 如果需要如果需要85/143二、完善学生信息表3.3 3.3 按按“

48、成绩成绩”排序排序 86/143二、完善学生信息表分类：按某个字段进行分类，将同类记录放在一起。为分类：按某个字段进行分类，将同类记录放在一起。为此，需要对指定字段进行此，需要对指定字段进行“排序排序”操作操作( (排序的排序的本质就是分类本质就是分类) )。汇总：排序后，按指定方式进行数据汇总。汇总：排序后，按指定方式进行数据汇总。3.4 3.4 按按“籍贯籍贯”分分类统计人数类统计人数 87/143二、完善学生信息表3.4 3.4 按按“籍贯籍贯”分分类统计人数类统计人数 先按先按“籍贯籍贯”排序。然后再通过排序。然后再通过“数据数据”菜单菜单下下“分级显示分级显示”组中的组中的“分类汇总

49、分类汇总”命令，打开命令，打开“分类汇总分类汇总”对话框。对话框。88/143二、完善学生信息表3.4 3.4 按按“籍贯籍贯”分分类统计人数类统计人数 89/143二、完善学生信息表3.4 3.4 按按“籍贯籍贯”分分类统计人数类统计人数 390/143二、完善学生信息表3.4 3.4 按按“籍贯籍贯”分分类统计人数类统计人数 1291/143二、完善学生信息表3.5 3.5 筛选筛选“河南省河南省”考生考生“入入学测试成绩学测试成绩”在在 60分以上的学生分以上的学生大表大表明晰的小表明晰的小表有条件的筛选有条件的筛选数据筛选目的数据筛选目的：使用户能够快速地在数据清单的大量：使用户能够快

50、速地在数据清单的大量数据中提取出满足条件的有用数据，隐藏暂时没用的数据中提取出满足条件的有用数据，隐藏暂时没用的数据。数据。一旦筛选条件被撤走，这些数据又重新出现一旦筛选条件被撤走，这些数据又重新出现。92/143二、完善学生信息表3.5 3.5 筛选筛选“河南省河南省”考生考生“入入学测试成绩学测试成绩”在在 60分以上的学生分以上的学生8自动筛选自动筛选用于筛选出在筛选条件中包含复杂的“与”、“或”关系的数据。适用于复杂条件。按选定内容筛选，适用于简单条件。8高级筛选高级筛选93/143二、完善学生信息表3.5 3.5 筛选筛选“河南省河南省”考生考生“入入学测试成绩学测试成绩”在在 60

51、分以上的学生分以上的学生“高级筛选高级筛选”多用于多用于包含复杂的包含复杂的“与与”和和“或或”的关系中：的关系中：94/143二、完善学生信息表3.5 3.5 筛选筛选“河南省河南省”考生考生“入入学测试成绩学测试成绩”在在 60分以上的学生分以上的学生 选中数据区域，通过选中数据区域，通过“数据数据”菜单下菜单下“排序和筛选排序和筛选”组组中的中的“筛选筛选”命令，每个字段都会出现下拉标记。命令，每个字段都会出现下拉标记。95/143二、完善学生信息表3.5 3.5 筛选筛选“河南省河南省”考生考生“入入学测试成绩学测试成绩”在在 60分以上的学生分以上的学生96/143二、完善学生信息表

52、3.5 3.5 筛选筛选“河南省河南省”考生考生“入入学测试成绩学测试成绩”在在 60分以上的学生分以上的学生 单击单击“籍贯籍贯”字段的下拉标记。字段的下拉标记。 只保留只保留“河南省河南省”选项。选项。97/143二、完善学生信息表3.5 3.5 筛选筛选“河南省河南省”考生考生“入入学测试成绩学测试成绩”在在 60分以上的学生分以上的学生 单击单击“计算机入学测试成绩计算机入学测试成绩”字段的下拉标记。字段的下拉标记。98/143二、完善学生信息表3.5 3.5 筛选筛选“河南省河南省”考生考生“入入学测试成绩学测试成绩”在在 60分以上的学生分以上的学生 选择选择“数字筛选数字筛选”级

53、联下的级联下的“大于或等于，打开对话框。大于或等于，打开对话框。99/143二、完善学生信息表3.5 3.5 筛选筛选“河南省河南省”考生考生“入入学测试成绩学测试成绩”在在 60分以上的学生分以上的学生目录目录一旦筛选条件被撤走，这些数据又重新出现。一旦筛选条件被撤走，这些数据又重新出现。100/143三、 加减法运算测试系统自动产生若干道算术系统自动产生若干道算术题，由学生回答。系统要自动题，由学生回答。系统要自动判断对错，并统计成绩。判断对错，并统计成绩。 设计一个设计一个100以内的加减法运以内的加减法运算自动判分表。算自动判分表。101/143三、 加减法运算测试2.1 2.1 随机

54、函数随机函数2.2 2.2 宏宏102/143三、 加减法运算测试2.1 2.1 随机函数随机函数 格式：格式：RAND()RAND()功能：返回一个大于等于功能：返回一个大于等于0, 0,小于小于1 1的均匀分布的随机数。的均匀分布的随机数。注意：每当编辑工作表时便会重新计算一个新的数值。注意：每当编辑工作表时便会重新计算一个新的数值。 103/143三、 加减法运算测试2.1 2.1 随机函数随机函数 格式：格式：RAND()RAND()功能：返回一个大于等于功能：返回一个大于等于0, 0,小于小于1 1的均匀分布的随机数。的均匀分布的随机数。注意：每当编辑工作表时便会重新计算一个新的数值

55、。注意：每当编辑工作表时便会重新计算一个新的数值。 104/143三、 加减法运算测试2.1 2.1 随机函数随机函数 格式：格式：RANDBETWEEN(RANDBETWEEN(数值数值M,M,数值数值N)N)功能：返回一个功能：返回一个M,NM,N的均匀分布的随机整数。的均匀分布的随机整数。注意：如果该函数不可用，则应该通过注意：如果该函数不可用，则应该通过“文件文件”菜单下的菜单下的“选项选项”命令，打开命令，打开“ExcelExcel选项选项”对话框，单击其中的对话框，单击其中的“加载项加载项”，加载，加载“分析工具库分析工具库” 。 105/143三、 加减法运算测试2.1 2.1

56、随机函数随机函数 格式：格式：RANDBETWEEN(RANDBETWEEN(数值数值M,M,数值数值N)N)例如，产生一个由大写英文字母组成的四位随机密码。例如，产生一个由大写英文字母组成的四位随机密码。大写英文大写英文字母的字母的ASCIIASCII码是码是 65906590，因此我们可，因此我们可以通过以通过RANDBETWEEN()RANDBETWEEN()生成生成 6590 6590 的随机数，然后的随机数，然后使用使用CHARCHAR函数将其转换为相应的字符。函数将其转换为相应的字符。 =CHAR(RANDBETWEEN(65,90)=CHAR(RANDBETWEEN(65,90)

57、J J W W A A G G106/143三、 加减法运算测试2.2 2.2 宏宏 宏是一个指令集，是一系列的命令和函数，用来告诉宏是一个指令集，是一系列的命令和函数，用来告诉ExcelExcel完成用户指定的动作。宏类似于计算机程序，但是它完成用户指定的动作。宏类似于计算机程序，但是它是完全运行于是完全运行于ExcelExcel之中的，我们可以使用之中的，我们可以使用“宏宏”来完成枯来完成枯燥的、频繁的重复性工作。燥的、频繁的重复性工作。“宏宏”完成动作的速度比用户完成动作的速度比用户自己做要快得多。自己做要快得多。 由于宏病毒的影响和对编程的畏惧心理，使很多人不敢由于宏病毒的影响和对编程

58、的畏惧心理，使很多人不敢用用“宏宏”，或是不知道什么时候可以找，或是不知道什么时候可以找“宏宏”来帮忙。其来帮忙。其实你尽管放心大胆地去用，也就是说如果我们要在实你尽管放心大胆地去用，也就是说如果我们要在 ExcelExcel中重复完成某项任务，那么可以用宏自动执行该任务，并中重复完成某项任务，那么可以用宏自动执行该任务，并且在需要执行该项任务时可随时运行。且在需要执行该项任务时可随时运行。107/143三、 加减法运算测试2.2 2.2 宏宏 首先我们可以通过首先我们可以通过“录制宏录制宏”的方法，将需要重复的方法，将需要重复执行的操作录制到执行的操作录制到“宏宏”中，这就好比用录音机把一些

59、操中，这就好比用录音机把一些操作录下来一样，只是在此我们录的不是声音而是作录下来一样，只是在此我们录的不是声音而是“操作操作”。到需要重复执行的时候，只要执行这个宏，系统就会把录到需要重复执行的时候，只要执行这个宏，系统就会把录制好的操作再执行一遍。制好的操作再执行一遍。我们可以通过我们可以通过“开发工具开发工具”菜单下菜单下“代码代码”组中的组中的“录制宏录制宏”命令，打开命令，打开“录制新宏录制新宏”对话框。对话框。108/143三、 加减法运算测试2.2 2.2 宏宏 单击单击“确定确定”按钮开始录制按钮开始录制可以修改可以修改“宏宏”的名称的名称可以设置可以设置“宏宏”的快捷键的快捷键

60、109/143三、 加减法运算测试2.2 2.2 宏宏 录制完成以后选录制完成以后选择择“停止录制停止录制”110/143三、 加减法运算测试3.1 3.1 出题出题先利用随机函数产生加法先利用随机函数产生加法算式的两列数据和减法算式的算式的两列数据和减法算式的后一列数据。后一列数据。 111/143三、 加减法运算测试3.1 3.1 出题出题考虑到随机函数是每次运考虑到随机函数是每次运算都改变，因此我们需要有一算都改变，因此我们需要有一个过度。个过度。我们可以在我们可以在L L列选列选1010个单个单元格，分别产生元格，分别产生1010，4949区间区间的随机整数。的随机整数。=RANDBETWEEN(10,49)=RANDBETWEEN(10,49)112/143三、 加减法运算测试选中选中L L列的列的1010个随机整数，个随机整数，单击单击“复制复