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

1、1http:/202.192.18.352Excel的考核要求掌握工作表的建立1掌握工作表的编辑以及格式化操作2掌握公式和函数的使用3 3掌握图表处理操作4掌握数据库的应用3 53Excel的操作考点1 1、数据库（工作表）的建立、数据库（工作表）的建立 理解数据库的概念，理解字段与记录的基本概念，掌握各各种类型数据的输入种类型数据的输入 公式的定义和复制公式的定义和复制（相对地址、绝对地址、混合地址的使用；表达式中数学运算符、文本运算符和比较运算符、区域运算符的使用） 掌握单元格、工作表与工作簿之间数据的传递 保存工作簿文件4Excel的操作考点2 2、工作表中单元格数据的修改，常用的、工作

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

3、学函数：数学函数：ABS，INT，ROUND， TRUNC， RAND。 统统计函数：计函数：SUM，SUMIF，AVERAGE，COUNT，COUNTIF，COUNTA，MAX， MIN，RANK。 日期函数：DATE，DAY，MONTH，YEAR，NOW，TODAY，TIME。 条件函数：IFIF，AND，OR。 财务函数：PMT，PV，FV。 频率分布函数： FREQUENCY。 数据库统计函数：DCOUNT，DCOUNTA，DMAX，DMIN，DSUM，DAVERAGE 选择函数：CHOOSE。 查找函数：VLOOKUP。6Excel的操作考点4 4、图表操作：、图表操作： 图表类型、

4、分类、数据系列。 图表的建立与编辑图表的建立与编辑：图表的建立，插入 / 删除 / 修改图表（字体：名称、倾斜、加粗、字号、下划线、颜色、删除线、上下标；边框：位置、线型、线宽、颜色；图案：底纹颜色、图案、图案颜色、边框、图案和填充）。5 5、数据库应用：、数据库应用： 数据的排序排序（包括自定义排序） 筛选筛选（自动筛选，高级筛选） 分类汇总 透视表的应用7 Excel 2003概述 工作表的编辑与格式化 公式与函数本次导学讲座主要内容本次导学讲座主要内容81、 Excel 2003概述1.1 Excel 2003的窗口组成的窗口组成1.2 Excel 的基本概念的基本概念 1.3 工作簿管

5、理工作簿管理1.4 Excel的数据输入的数据输入9标标 题题 栏栏菜菜 单单 栏栏工工 具具 栏栏滚滚 动动 条条名名 称称 框框编编 辑辑 框框列列 号号行行 号号工作表标签工作表标签工作簿文件名工作簿文件名成绩统计成绩统计.xls活动单元格活动单元格工作表滚动按钮工作表滚动按钮=3+6=3+6101. 1. 工作簿工作簿n工作簿工作簿: : 列列 A,B.,IV(256) ；行；行 1,2, ., 65536工作表名! 由列标和行标来引用： 工作表名! 左上角单元地址：右下角单元地址11 数据清单与数据库在Excel中，数据清单就是一个数据库。清单中的列被认为是数据库的字段，清单中的列标

6、题认为是数据库的字段名，清单中的每一行被认为是数据库的一条记录。121.3 管理工作簿切换工作表切换工作表插入工作表插入工作表重命名重命名工作表工作表复制复制工作表工作表隐藏隐藏/显示工作表显示工作表删除工作表删除工作表移动工作表移动工作表13操作实例v工作表的插入、复制、移动、删除、重命名；工作表的插入、复制、移动、删除、重命名；打开工作簿文件2007202.xls，并按指定要求完成有关的操作：1.将“sheet2”工作表删除；2.复制“sheet1”工作表，并命名为“长虹商场”；3.插入“sheet4”工作表，并移动到sheet3的前面14 1.4 输入数据字符型数据字符型数据日期型数据日

7、期型数据数值型数据数值型数据公式公式序列数序列数15 字符 / 文本：默认左对齐 数值：默认右对齐 逻辑值：默认居中对齐输入数据输入数据指向该单元格右下指向该单元格右下角的自动填充柄角的自动填充柄 拖曳填充拖曳填充柄到结束柄到结束 自动填充序列数自动填充序列数 自定义序列自定义序列 162. 工作表的编辑与格式化v数据的移动、复制、选择性粘贴选择性粘贴、单元格/行/列的插入与删除、清除（对象包括全部、内容、格式、批注），插入批注，下拉列表数据及数据有有效性效性v页面设置（页面方向、缩放、纸张大小，页边距、页眉/页脚）v增加/删除/修改/应用样式v单元格或区域格式（数字、字体、对齐、边框、图案、

8、设置行高/列宽）、自动套用格式、条件条件格式格式的设置v插入/删除/修改页眉、页脚17打开2007201.xls，对“Sheet1”工作表进行如下设置： 当用户选中“职务”列的任一单元格时，在其右则显示一个下拉列表框箭头，并提供“副处长”，“处长”和“科长”的选择项供用户选择 (提示：通过提示：通过“数据数据”|“有效性有效性”进行设进行设置，有效性条件为序列置，有效性条件为序列 ) ； 当选中“年龄”列的任一单元格时，显示“请输入1-100的有效年龄”，其标题为“年龄”，当用户输入某一年龄值时，即进行检查，如果所输入的年龄不在指定的范围内，错误信息提示“年龄必须在1-100之间”，“停止”样

9、式，同时标题为“年龄非法”。以上单元格均忽略空值。操作实例（有效性）操作实例（有效性）18打开20072002.xls，并按指定要求完成有关的操作：1. 将B2:B21的数据采用会计专用格式、保留2位小数、使用“$”货币符号；2. 将C2:C21的格式设置成与B2:B21区域相同的格式；3. 请将“sheet3”中A1：H4区域的内容转置（即行和列的内容置换），转置结果放在A7开始的区域中，并格式化为单元格带红色框线（样式；右列第5行，单元格底纹颜色浅绿(第5行第4个)。4. 利用自动套用格式“古典2”格式化sheet1的A1：C21；5. 利用“格式”菜单的条件格式，将B2:B21中50-5

10、9用蓝色表示，80-89绿色表示。操作实例（基本编辑）操作实例（基本编辑）19边框线的设置 利用利用“单元格格式单元格格式”对话框设置单元格的边框对话框设置单元格的边框 “格式格式”“”“单元格单元格” “” “边框边框”第一步：先选样式和颜色第二步：作用的地方203. 公式与函数 3.1 创建公式创建公式 3.2 单元格的引用（地址）单元格的引用（地址） 3.4 常用函数常用函数3.3 公式中的错误信息公式中的错误信息21格式： = =操作数+运算符 在在A1中输入公式中输入公式:=8*5 在在B1中输入公式中输入公式:=16/2 在在A2中输入公式中输入公式:=A1/B1-2 在在A3中输

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

12、公式为：=A1+b2+100,将F1的公式复制到H2，H2的公式变为:253.2.2 绝对地址的引用v 对于包括绝对引用的公式，无论将公式复制到什么位置，总是引用那些单元格。v 实例：在F3中的公式为：=$A$1+100,将F3的公式复制到H4，H4的公式还是= $A$1+100263.2.3 混合地址的引用v 单元格的混合引用是指公式中参数的行采用相对引用，列采用绝对引用；或列采用绝对引用、行采用相对引用，如$A3，A$3。当含有公式的单元格因插入、复制插入、复制等原因引起行、列引用的变化，公式中相对引用相对引用部分随公式位置的变化而变化变化，绝对引用部分不随公式位置的变化而变化。 273.

13、2.4 三维地址的引用v 引用包含一系列工作表名称和单元格或单元格区域引用。v 三维引用的一般格式为： “工作表标签！单元格引用”“工作簿名称工作表标签！单元格引用”例如，如果想引用“Sheet1”工作表中的单元格B2，则应输入“Sheetl!B2”。例如，Book1Sheet1!B2例 如 ， 求 S h e e t 1 到 S h e e t 5 中 区 域 A 2 : A 5 的 和 : =SUM(Sheet1:Sheet5!A2:A5)28数学函数：ABS，INT，ROUNDROUND，TRUNC, RAND统计函数：SUMSUM，AVERAGEAVERAGE，COUNTCOUNT，C

14、OUNTACOUNTA，COUNTBLANK，COUNTIFCOUNTIF，MAXMAX，MINMIN，RANK日期函数：DATE，DAY，MONTH，YEARYEAR，NOW，TODAYTODAY，TIME条件函数：IFIF，AND，OR频率分布函数： FREQUENCYFREQUENCY数据库统计函数：DCOUNT，DCOUNTA，DMAX，DMIN，DSUM，DAVERAGE选择函数：CHOOSE财务函数：PMT，PV，FV 29 使用公式选项板输入函数由于Excel提供了大量的函数，并且有许多函数不经常使用，很难记住它们的参数。使用“公式选项板”能够通过自动更正常见的错误和提供即时帮助

15、来协助用户工作。公式选项板是帮助用户创建或编辑公式的工具，当在公式中输入函数时，公式选项板会显示函数的名称，它的每个参数、函数功能和参数的描述。函数的当前结果和整个公式的结果。 “插入”“函数”30请打开工作簿文件2007203.xls，并按指定要求完成有关的操作：1.在B列计算原数值的绝对值。2.在C列计算不大于原数值的最大整数3.在D2：D31中产生30个随机数操作实例31绝对值函数ABS格式格式: :ABS(number)功能功能: :返回参数的绝对值。随机函数RAND格式:RAND()功能：返回0,1)范围内的随机数本操作实例中使用的函数32函数INT格式：INT(Number)功能：

16、返回小于或等于数值number的最大整数numberINT(number)本操作实例中使用的函数33请打开2007203.xls，按要求完成有关的操作：v在H列计算四门课程的平均分，并使用函数四舍五入取整。v在I列计算四门课程的总分操作实例（统计、数学函数）操作实例（统计、数学函数）34格式格式: :AVERAGE(number1,number2,)功能功能: :计算参数中数值的平均值。说明说明: :每个参数可以是数值、单元格引用坐标或函数。求平均值函数AVERAGE35格式：ROUND(number, num_digits)功能：按num-digits指定位数,将number进行四舍五入。说

17、明：Number需要进行舍入的数字。Num_digits指定的位数，按此位数进行舍入。 四舍五入函数四舍五入函数ROUND36格式格式:SUM（number1,number2,）其中其中:number1，number2等表示参数。功能功能:计算参数中数值的总和。说明说明:每个参数可以是数值、单元格引用坐标或函数。求和函数SUM37v请打开2007203.xls，在K列按总分计算每位学生的排名。排位函数RANK格式：RANK(number,ref,order)功能：功能：返回一个数值在一组数值中的排位。数值的排位是与数据清单中其他数值的相对大小说明：说明： Number为需要找到排位的数字。Re

18、f 为包含一组数字的数组或引用（绝对地址）。如果order为0或省略，则按降序进行排位。操作实例（操作实例（rank函数）函数）38请打开2007203.xls，按要求完成有关操作：v在N1单元格计算全班总人数v在N2单元格计算女生总人数v在N3单元格计算英语最高分操作实例（统计函数）操作实例（统计函数）39求数字个数函数COUNT格式:COUNT(value1,value2, .)功能:求参数中数值数据的个数。求参数组中非空值的数目函数COUNTA格式:COUNTA(value1,value2, .)功能:返回参数组中非空值的数目。求参数组中非空值的数目函数COUNTBLANK格式:COUN

19、TBLANK(value1,value2, .)功能:返回参数组中空值的数目。40求满足特定条件的单元格数目COUNTIF格式格式:COUNTIF(range,criteria)功能功能：计算给定区域内满足特定条件的单元格的数目。说明说明：Criteria是条件，其形式可以为数字、表达式或文本，还可以使用通配符。 Criteria表达式一般要加引号。41求最大值函数MAX格式:MAX(number1,number2,)功能:求参数中数值的最大值。求最小值函数MIN格式:MIN(number1,number2,)功能:求参数中数值的最小值。42打开2007203.xls，请使用IF函数根据平均分

20、，在J列给出级别。分三个级别：v优秀（100平均分80）v及格（80平均分60）v不及格（60平均分0）操作实例（操作实例（IF函数）函数）43功能功能：判断条件，根据不同的情况返回不同的结果：判断条件，根据不同的情况返回不同的结果Logical_test ：条件，即逻辑表达式。条件，即逻辑表达式。Value_if_true ：logical_test 为为“TRUE”时返回的结果时返回的结果Value_if_false：logical_test 为为“FALSE”时返回的结果。时返回的结果。IF(logical_test,valuel_if_true,value_if_false)44v 请

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

22、eMONTH(date) )功能：功能：返回参数对应的月份返回参数对应的月份v DAY(dateDAY(date) )功能：功能：返回参数对应的日的数字返回参数对应的日的数字v TODAY( )TODAY( )功能：功能：返回系统的当前日期，没有参数。返回系统的当前日期，没有参数。v DATE(DATE(yearyear, , monthmonth, , day) day)功能：功能：把三个数组合成一个日期，把三个数组合成一个日期，3 3个参数。个参数。47操作实例(财务函数)v请打开2007209.xls工作簿，进行相关财务计算： 若两年后需要一笔学习费用支出，计划从现在起每月初存入2000

23、元，如果按年利2.25%，问两年后该账户的存款（未来值FV）？ 假设要购买一项保险年金，该保险可以在今后二十年内于每月末回报￥600。此项年金的购买成本为80,000，假定投资回报率为8%。请问该项投资合算吗（现值PV）？ 若年利率为8%，支付的月份数为10个月，贷款额为￥10，000元，那么在这样的条件下贷款的月支付额是多少呢？（每期支付额PMT）2007209as.xls答案48财务函数使用说明v rate 为各期利率。例如，如果按 10% 的年利率借入一笔贷款来购买汽车，并按月偿还贷款，则月利率为 10%/12（即 0.83%）。可以在公式中输入 10%/12、0.83% 或 0.008

24、3 作为 rate 的值。 v nper为总投资（或贷款）期，即该项投资（或贷款）的付款期总数。例如，对于一笔 4 年期按月偿还的汽车贷款，共有 4*12（即 48）个偿款期数。可以在公式中输入 48 作为 nper 的值。v pmt 为各期所应支付的金额，其数值在整个年金期间保持不变。通常 pmt 包括本金和利息，但不包括其他费用及税款。例如，$10,000 的年利率为 12% 的四年期汽车贷款的月偿还额为 $263.33。可以在公式中输入 -263.33 作为 pmt 的值。如果忽略 pmt，则必须包含 fv 参数。v fv 为未来值，或在最后一次支付后希望得到的现金余额，如果省略 fv，

25、则假设其值为零（一笔贷款的未来值即为零）。例如，如果需要在 18 年后支付 $50,000，则 $50,000 就是未来值。可以根据保守估计的利率来决定每月的存款额。如果忽略 fv，则必须包含 pmt 参数。v pv 为现值，即从该项投资开始计算时已经入帐的款项，或一系列未来付款的当前值的累积和，也称为本金。如果省略 PV，则假设其值为零，并且必须包括 pmt 参数。49格式:FVFV(raterate,npernper,pmtpmt,pv,type)功能:基于固定利率及等额分期付款方式，返回某项投资的未来值。参数说明：Rate：各期利率。 Nper：总投资期，即该项投资的付款期总数。 Pmt

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

27、值。参数说明：Rate：各期利率。 Nper：总投资期，即该项投资的付款期总数。 Pmt：各期所应支付的金额，其数值在整个年金期间保持不变。通常 pmt包括本金和利息，但不包括其他费用及税款。如果忽略 pmt，则必须包括fv参数。Fv：未来值，或在最后一次支付后希望得到的现金余额，如果省略 fv，则假设其值为零（一笔贷款的未来值即为零）。 Type：数字 0 或 1，用以指定各期的付款时间是在期初还是期末。如果省略 type，则假设其值为零。 pv函数51格式:PMTPMT(raterate,npernper,pvpv,fv,type)功能:基于固定利率及等额分期付款方式，返回贷款的每期付款额

28、。 参数说明：Rate：贷款利率。Nper：该项贷款的付款总数。 Pv：现值，或一系列未来付款的当前值的累积和，也称为本金。 Fv：为未来值，或在最后一次付款后希望得到的现金余额，如果省略fv，则假设其值为零，也就是一笔贷款的未来值为零。 Type：数字 0 或 1，用以指定各期的付款时间是在期初还是期末。 pmt函数52频率分布统计函数FREQUENCY 频率分布统计函数用于统计一组数据在各个数值区间的分布情况，这是对数据进行分析的常用方法之一。功能：以一列垂直数组返回某个区域中数据的频率分布。格式：FREQUENCY(data_array，bins_array) 数据对象数据对象频率分布统计频率分布统计的分段点的分段点53操作实例v 请打开2007203.xls工作簿，对“频度分析”工作表对学生语文成绩各分值段人数的统计，结果输出在G3：G7。54自主学习平台网址http:/202.192.18.35