大学计算机基础系列课件 excel上

1、1欢迎同学们来到大学计算机应用基础导学讲堂5主讲：刘敏华.2Excel的考核要求掌握工作表的建立1掌握工作表的编辑以及格式化操作2掌握公式和函数的使用33掌握图表处理操作4掌握数据库的应用35.3Excel的操作考点1、数据库（工作表）的建立 理解数据库的概念，理解字段与记录的基本概念，掌握各种类型数据的输入 公式的定义和复制（相对地址、绝对地址、混合地址的使用；表达式中数学运算符、文本运算符和比较运算符、区域运算符的使用） 掌握单元格、工作表与工作簿之间数据的传递 保存工作簿文件.4Excel的操作考点2、工作表中单元格数据的修改，常用的编辑与格式化操作： 数据的移动、复制、选择性（转置）粘

2、贴、单元格/行/列的插入与删除、清除（对象包括全部、内容、格式、批注），插入批注，数据的有效性 页面设置（页面方向、缩放、纸张大小，页边距、页眉/页脚） 工作表的复制、移动、重命名、插入、删除 增加/删除/修改/应用样式 单元格或区域格式化（数字、字体、对齐、边框、图案、设置行高/列宽）、自动套用格式、条件格式的设置 插入/删除/修改页眉、页脚、批注4.5Excel的操作考点3、函数应用：掌握以下函数，按要求对工作表进行数据统计或分析 数学函数：ABS，INT，ROUND， TRUNC， RAND。 统计函数：SUM，SUMIF，AVERAGE，COUNT，COUNTIF，COUNTA，MAX

3、， MIN，RANK。 日期函数：DATE，DAY，MONTH，YEAR，NOW，TODAY，TIME。 条件函数：IF，AND，OR。 财务函数：PMT，PV，FV。 频率分布函数： FREQUENCY。 数据库统计函数：DCOUNT，DCOUNTA，DMAX，DMIN，DSUM，DAVERAGE 选择函数：CHOOSE。 查找函数：VLOOKUP。.6Excel的操作考点4、图表操作： 图表类型、分类、数据系列。 图表的建立与编辑：图表的建立，插入 / 删除 / 修改图表（字体：名称、倾斜、加粗、字号、下划线、颜色、删除线、上下标；边框：位置、线型、线宽、颜色；图案：底纹颜色、图案、图案颜

4、色、边框、图案和填充）。5、数据库应用： 数据的排序（包括自定义排序） 筛选（自动筛选，高级筛选） 分类汇总 透视表的应用6.7 Excel 2003概述 工作表的编辑与格式化 公式与函数本次导学讲座主要内容.81、 Excel 2003概述1.1 Excel 2003的窗口组成1.2 Excel 的基本概念 1.3 工作簿管理Excel 20031.4 Excel的数据输入.91.1 窗口组成标 题 栏菜 单 栏工 具 栏滚 动 条名 称 框编 辑 框列 号行 号工作表标签工作簿文件名成绩统计.xls活动单元格工作表滚动按钮=3+6.101. 工作簿工作簿: 有若干个工作表组成1.2 基本概

5、念工作表:一个工作表有65536行、256列： 列 A,B.,IV(256) ；行 1,2, ., 65536单元格：行和列的交叉点 单元格地址：工作表名! 由列标和行标来引用区域：由相邻单元格构成的矩形区域地址： 工作表名! 左上角单元地址：右下角单元地址.11 数据清单与数据库在Excel中，数据清单就是一个数据库。清单中的列被认为是数据库的字段，清单中的列标题认为是数据库的字段名，清单中的每一行被认为是数据库的一条记录。.121.3 管理工作簿工作表的操作BGDEA切换工作表插入工作表重命名工作表复制工作表隐藏/显示工作表C删除工作表F移动工作表.13操作实例工作表的插入、复制、移动、删

6、除、重命名；打开工作簿文件2007202.xls，并按指定要求完成有关的操作：将“sheet2”工作表删除；复制“sheet1”工作表，并命名为“长虹商场”；插入“sheet4”工作表，并移动到sheet3的前面.14 1.4 输入数据数据的类型BECDA字符型数据日期型数据数值型数据公式序列数.15输入数据1、数据的输入 输入.xls 字符 / 文本：默认左对齐 数值：默认右对齐 逻辑值：默认居中对齐2、默认的对齐方式3、数据自动输入输入数据指向该单元格右下角的自动填充柄 拖曳填充柄到结束 自动填充序列数 自定义序列 .162. 工作表的编辑与格式化数据的移动、复制、选择性粘贴、单元格/行/

7、列的插入与删除、清除（对象包括全部、内容、格式、批注），插入批注，下拉列表数据及数据有效性页面设置（页面方向、缩放、纸张大小，页边距、页眉/页脚）增加/删除/修改/应用样式单元格或区域格式（数字、字体、对齐、边框、图案、设置行高/列宽）、自动套用格式、条件格式的设置插入/删除/修改页眉、页脚.17打开2007201.xls，对“Sheet1”工作表进行如下设置： 当用户选中“职务”列的任一单元格时，在其右则显示一个下拉列表框箭头，并提供“副处长”，“处长”和“科长”的选择项供用户选择 (提示：通过“数据”|“有效性”进行设置，有效性条件为序列 ) ； 当选中“年龄”列的任一单元格时，显示“请输

8、入1-100的有效年龄”，其标题为“年龄”，当用户输入某一年龄值时，即进行检查，如果所输入的年龄不在指定的范围内，错误信息提示“年龄必须在1-100之间”，“停止”样式，同时标题为“年龄非法”。以上单元格均忽略空值。操作实例（有效性）.18打开20072002.xls，并按指定要求完成有关的操作：将B2:B21的数据采用会计专用格式、保留2位小数、使用“$”货币符号；将C2:C21的格式设置成与B2:B21区域相同的格式；请将“sheet3”中A1：H4区域的内容转置（即行和列的内容置换），转置结果放在A7开始的区域中，并格式化为单元格带红色框线（样式；右列第5行，单元格底纹颜色浅绿(第5行第

9、4个)。利用自动套用格式“古典2”格式化sheet1的A1：C21；利用“格式”菜单的条件格式，将B2:B21中50-59用蓝色表示，80-89绿色表示。操作实例（基本编辑）.19边框线的设置利用“单元格格式”对话框设置单元格的边框 “格式”“单元格” “边框”第一步：先选样式和颜色第二步：作用的地方.203. 公式与函数 3.1 创建公式 3.2 单元格的引用（地址） 3.4 常用函数公式与函数3.3 公式中的错误信息.21格式： =操作数+运算符 输入.XLS实例 在A1中输入公式:=8*5 在B1中输入公式:=16/2 在A2中输入公式:=A1/B1-2 在A3中输入公式:=A1B13.

10、1 创建公式.22操作数：常量、区域名、单元格引用（地址）和函数乘方运算符：数学运算符、比较运算符、文字运算符数学运算符：、*、%、（）文字运算符：&比较运算符： =、=、=、实例 在A4中输入公式:=(5-3)A2 在B4中输入公式:=A1=B1 在A5中输入公式:=A4-B1=A2.233.2 单元格的引用相对地址引用绝对地址引用 混合地址引用三维地址引用.243.2.1 相对地址的引用如果将含有单元地址的公式复制或移动到别的单元格时，这个公式中的单元格引用将会根据公式移动的相对位置作相应的改变。 实例：在F1中的公式为：=A1+b2+100,将F1的公式复制到H2，H2的公式变为:.25

11、3.2.2 绝对地址的引用对于包括绝对引用的公式，无论将公式复制到什么位置，总是引用那些单元格。 实例：在F3中的公式为：=$A$1+100,将F3的公式复制到H4，H4的公式还是= $A$1+100.263.2.3 混合地址的引用单元格的混合引用是指公式中参数的行采用相对引用，列采用绝对引用；或列采用绝对引用、行采用相对引用，如$A3，A$3。当含有公式的单元格因插入、复制等原因引起行、列引用的变化，公式中相对引用部分随公式位置的变化而变化，绝对引用部分不随公式位置的变化而变化。 .273.2.4 三维地址的引用引用包含一系列工作表名称和单元格或单元格区域引用。三维引用的一般格式为： “工作

12、表标签！单元格引用”“工作簿名称工作表标签！单元格引用”例如，如果想引用“Sheet1”工作表中的单元格B2，则应输入“Sheetl!B2”。例如，Book1Sheet1!B2例如，求Sheet1到Sheet5中区域A2:A5的和: =SUM(Sheet1:Sheet5!A2:A5).28数学函数：ABS，INT，ROUND，TRUNC, RAND统计函数：SUM，AVERAGE，COUNT，COUNTA，COUNTBLANK，COUNTIF，MAX，MIN，RANK日期函数：DATE，DAY，MONTH，YEAR，NOW，TODAY，TIME条件函数：IF，AND，OR频率分布函数： FRE

13、QUENCY数据库统计函数：DCOUNT，DCOUNTA，DMAX，DMIN，DSUM，DAVERAGE选择函数：CHOOSE财务函数：PMT，PV，FV 3. 常用函数.29 使用公式选项板输入函数由于Excel提供了大量的函数，并且有许多函数不经常使用，很难记住它们的参数。使用“公式选项板”能够通过自动更正常见的错误和提供即时帮助来协助用户工作。公式选项板是帮助用户创建或编辑公式的工具，当在公式中输入函数时，公式选项板会显示函数的名称，它的每个参数、函数功能和参数的描述。函数的当前结果和整个公式的结果。 “插入”“函数”.30请打开工作簿文件2007203.xls，并按指定要求完成有关的操

14、作：1.在B列计算原数值的绝对值。2.在C列计算不大于原数值的最大整数3.在D2：D31中产生30个随机数操作实例.31绝对值函数ABS格式:ABS(number)功能:返回参数的绝对值。随机函数RAND格式:RAND()功能：返回0,1)范围内的随机数本操作实例中使用的函数.32函数INT格式：INT(Number)功能：返回小于或等于数值number的最大整数numberINT(number)本操作实例中使用的函数.33请打开2007203.xls，按要求完成有关的操作：在H列计算四门课程的平均分，并使用函数四舍五入取整。在I列计算四门课程的总分操作实例（统计、数学函数）.34格式:AVE

15、RAGE(number1,number2,)功能:计算参数中数值的平均值。说明:每个参数可以是数值、单元格引用坐标或函数。求平均值函数AVERAGE.35格式：ROUND(number, num_digits)功能：按num-digits指定位数,将number进行四舍五入。说明：Number需要进行舍入的数字。Num_digits指定的位数，按此位数进行舍入。 四舍五入函数ROUND.36格式:SUM（number1,number2,）其中:number1，number2等表示参数。功能:计算参数中数值的总和。说明:每个参数可以是数值、单元格引用坐标或函数。求和函数SUM.37请打开2007

16、203.xls，在K列按总分计算每位学生的排名。排位函数RANK格式：RANK(number,ref,order)功能：返回一个数值在一组数值中的排位。数值的排位是与数据清单中其他数值的相对大小说明： Number为需要找到排位的数字。Ref 为包含一组数字的数组或引用（绝对地址）。如果order为0或省略，则按降序进行排位。操作实例（rank函数）.38请打开2007203.xls，按要求完成有关操作：在N1单元格计算全班总人数在N2单元格计算女生总人数在N3单元格计算英语最高分操作实例（统计函数）.39求数字个数函数COUNT格式:COUNT(value1,value2, .)功能:求参数

17、中数值数据的个数。求参数组中非空值的数目函数COUNTA格式:COUNTA(value1,value2, .)功能:返回参数组中非空值的数目。求参数组中非空值的数目函数COUNTBLANK格式:COUNTBLANK(value1,value2, .)功能:返回参数组中空值的数目。.40求满足特定条件的单元格数目COUNTIF格式:COUNTIF(range,criteria)功能：计算给定区域内满足特定条件的单元格的数目。说明：Criteria是条件，其形式可以为数字、表达式或文本，还可以使用通配符。 Criteria表达式一般要加引号。.41求最大值函数MAX格式:MAX(number1,n

18、umber2,)功能:求参数中数值的最大值。求最小值函数MIN格式:MIN(number1,number2,)功能:求参数中数值的最小值。.42打开2007203.xls，请使用IF函数根据平均分，在J列给出级别。分三个级别：优秀（100平均分80）及格（80平均分60）不及格（60平均分0）操作实例（IF函数）.43功能：判断条件，根据不同的情况返回不同的结果Logical_test ：条件，即逻辑表达式。Value_if_true ：logical_test 为“TRUE”时返回的结果Value_if_false：logical_test 为“FALSE”时返回的结果。IF(logical

19、_test,valuel_if_true,value_if_false).44请打开2007203.xls，在“Sheet3”工作表中通过函数和公式计算每位教师的职称津贴，计算标准为：职称 津贴教授 2000元副教授 1800元讲师 1500元助教 1000元要求:当J2:J5的数据发生变化时,计算结果自动更新。操作实例（IF函数）.45操作实例请打开2007203.xls工作簿，对“Sheet4”工作表进行以下操作： 1.在F2单元格中输入函数求出当前日期。2.求出各学生的出生年份。3.求出各学生的出生月份。.46本操作实例中使用的函数YEAR(date)功能：返回参数对应的年份MONTH(

20、date)功能：返回参数对应的月份DAY(date)功能：返回参数对应的日的数字TODAY( )功能：返回系统的当前日期，没有参数。DATE(year, month, day)功能：把三个数组合成一个日期，3个参数。.47操作实例(财务函数)请打开2007209.xls工作簿，进行相关财务计算：若两年后需要一笔学习费用支出，计划从现在起每月初存入2000元，如果按年利2.25%，问两年后该账户的存款（未来值FV）？假设要购买一项保险年金，该保险可以在今后二十年内于每月末回报￥600。此项年金的购买成本为80,000，假定投资回报率为8%。请问该项投资合算吗（现值PV）？若年利率为8%，支付的月

21、份数为10个月，贷款额为￥10，000元，那么在这样的条件下贷款的月支付额是多少呢？（每期支付额PMT）2007209as.xls答案.48财务函数使用说明rate 为各期利率。例如，如果按 10% 的年利率借入一笔贷款来购买汽车，并按月偿还贷款，则月利率为 10%/12（即 0.83%）。可以在公式中输入 10%/12、0.83% 或 0.0083 作为 rate 的值。 nper为总投资（或贷款）期，即该项投资（或贷款）的付款期总数。例如，对于一笔 4 年期按月偿还的汽车贷款，共有 4*12（即 48）个偿款期数。可以在公式中输入 48 作为 nper 的值。pmt 为各期所应支付的金额，

22、其数值在整个年金期间保持不变。通常 pmt 包括本金和利息，但不包括其他费用及税款。例如，$10,000 的年利率为 12% 的四年期汽车贷款的月偿还额为 $263.33。可以在公式中输入 -263.33 作为 pmt 的值。如果忽略 pmt，则必须包含 fv 参数。fv 为未来值，或在最后一次支付后希望得到的现金余额，如果省略 fv，则假设其值为零（一笔贷款的未来值即为零）。例如，如果需要在 18 年后支付 $50,000，则 $50,000 就是未来值。可以根据保守估计的利率来决定每月的存款额。如果忽略 fv，则必须包含 pmt 参数。pv 为现值，即从该项投资开始计算时已经入帐的款项，或

23、一系列未来付款的当前值的累积和，也称为本金。如果省略 PV，则假设其值为零，并且必须包括 pmt 参数。.49格式:FV(rate,nper,pmt,pv,type)功能:基于固定利率及等额分期付款方式，返回某项投资的未来值。参数说明：Rate：各期利率。 Nper：总投资期，即该项投资的付款期总数。 Pmt：各期所应支付的金额，其数值在整个年金期间保持不变。通常 pmt 包括本金和利息，但不包括其他费用及税款。如果忽略 pmt，则必须包括pv 参数。Pv：现值，即从该项投资开始计算时已经入帐的款项，或一系列未来付款的当前值的累积和，也称为本金。如果省略 PV，则假设其值为零，并且必须包括pmt 参数。 Type：数字 0 或 1，用以指定各期的付款时间是在期初还是期末。如果省略 type，则假设其值为零。 FV函数.50格式:PV(rate,nper,pmt,fv,type)功能:返回投资的现值。现值为一系