Excel2007教程(大学版本)PPT演示课件

第四章,电子表格Excel,1,主要内容,1.Excel概述2.Excel基础3.Excel基本知识4.Excel公式与函数5.数据管理,2,第一节Excel概述,Excel功能,Excel的工作界面,3,1.Excel功能,电子表格（Spreadsheets）概念一种可进行运算的表格软件，如Excel电子表格的主要功能制表功能数据管理数据图表数据分析,4,2.Excel的工作界面,Office按钮功能区编辑栏快速访问工具栏状态栏,5,(1)Office按钮,单击Office按钮，可以完成：新建工作簿保存工作簿打开工作簿关闭工作簿退出Excel设置“Excel选项”,6,(2)功能区,工作区上方的一排选项卡，含有“开始”、“插入”、“页面布局”等。随着选择的对象不同，还会出现一些动态的选项卡。对话框启动器：单击选项卡右下角的按钮,7,(3)编辑栏,由名称框、命令按钮和数据编辑区组成。名称框显示活动单元格的地址或名称；数据编辑区可显示出活动单元格中已输入的常量和公式，可在编辑区内输入数据、编辑数据；命令按钮为确认按钮，为取消按钮。,8,(4)快速访问工具栏,Office按钮右边的一个工具栏，其中含有一些常用的按钮，可以通过单击相应的按钮来快速访问这些命令。快速访问工具栏的位置可调，可以放在功能区下方显示。,9,(5)状态栏,对工作表当前状态的显示，右击状态栏，可以选择状态栏中要显示的内容，包括：大写、数字、滚动、改写键状态，宏录制按钮页码选定单元格区域的平均值、计数、最大值等视图快捷方式、显示比例、缩放滑块,10,第二节Excel的基础知识,Excel的基本概念,单元格区域,11,1.基本概念,(1)工作簿（Workbooks）电子表格软件Excel生成的一个文档文件Excel2003的文件工作簿文件扩展名：.xls；模板文件的扩展名：.xlt,12,工作簿,Excel2007的常用文件工作簿文件的默认扩展名：.xlsx（未启用宏），是基于XML的新的文件格式启用宏的Excel工作簿文件扩展名：.xlsm模板文件扩展名：.xltx默认情况下，可包含三个工作表,13,(2)工作表,文档窗口显示的是一个工作表（Spreadsheet）工作表的标签为Sheet1、Sheet2等，标签可设置颜色工作表的有关操作(1)插入(2)删除(3)重命名(4)移动或复制(5)保护工作表(6)隐藏工作表操作步骤：右击工作表标签，在快捷菜单中选择命令,14,工作表的操作,注意：移动工作表和复制工作表的区别在于复制工作表时需要选择“建立副本”。移动和复制工作表可以在同一个工作簿内也可以在不同工作簿内通过“Excel选项”可以对不同的工作表设置不同的显示选项。,15,(3)单元格,单元格的访问方式：（单元格的地址）A1方式：列号行号，如B3R1C1方式：R行C列，如R3C3,16,2020/5/18,l,单元格,2.列数、行数列标：A,BAAZZ,AAAXFD，共有16384列行号：1,2,3.1048576行最后一个单元格的地址：XFD1048576,17,活动单元格的选定,18,2.单元格区域,拖动鼠标左键可以选定连续的区域，也可以按Ctrl键选择不连续的区域。在公式中引用单元格区域时，可以直接用鼠标选取。,19,单元格区域的表示方法,1矩形区域的种表示：A2:D5A5:D2D5:A2D2:A52单行、单列、若干行、若干列区域:6:6H:H3:5D:F3两区域公共部分：(交)(B4:K6D1:F8)(空格)4多区域：(并)A1:B5,K8,9:9,H:J(逗号),20,单元格区域,5不同工作表单元格的表示:(!)例：=SUM(Sheet1!A2:B4,Sheet2!F5)6不同工作簿的单元格的表示：(工作簿文件名工作表！单元格),21,例1：区域表示,如果当前工作表abcc.xlsxSheet2中的单元格E37的内容为公式：=SUM(Sheet3!D5,G8,练习题_函数.xlsxSheet2!B3)，则单元格E37的内容涉及_个工作表?A）1B）2C）3D）4B（C）,正确答案：C,22,例2：区域表示,区域(A2:K8D:E)是包含多少个单元格？,提示：在名称框中直接输入区域地址按回车键，查看被选取的单元格数目。,23,思考题：,提示：选定连续的工作表或不连续的工作表组成一个工作组，则可同时对多个工作表进行相同的操作。,如何同时对多个工作表进行同样的操作，如在不同工作表的相同位置上编辑数据、设置单元格格式等？,24,第三节Excel的基本操作,数据类型,填充柄,数据的编辑,工作表格式化,25,1.数据类型,在Excel中，对输入的数据自动区分为数值型纯数值型日期型时间型文本型逻辑型,26,1.数据类型,不同的数据类型在单元格内默认的对齐方式不同，数值型右对齐，文本型左对齐，逻辑型居中。,27,数据类型纯数值型,纯数值型由下列数字组成：0123456789+(正号)-(负号)逗号，小数点.￥$%Ee圆括号负数的输入：负号或括号，如：-3或（3）分数的输入：须为带分数如：03/4(空格连接),28,思考题：,如何输入超过15位的数字，如身份证号？,因为数值型数据有效位为15位，超过15位的有效数据将被去掉。输入时应输入为文本型数据才能完整地保留。,29,技巧：,如何快速输入一批相同的数据？,先选择区域再输入数据最后，按Ctrl+Enter键,30,数据类型日期型数据,两种格式：/或-作为分隔符yyyy-mm-ddyyyy/mm/dd如：99/10/3，2002-7-23，Aug-02范围：1900/1/1-9999/12/31对应数字：1-2958465日期序列数,31,数据类型时间型数据,格式：hh:mm:ss或hh:mm时间数据：17:32:28(24时制)11:54:30AM(12时制空格)范围：0:00:00-24:00:00时间序列相当于小数：0.0-1.0日期时间连写：2003/10/511:30中间要加空格,32,数据类型文本型与逻辑型,文本型数据：字母、数字或其它字符组成（只要是非数值型、逻辑型、错误值）逻辑型数据：TRUEFALSE(truefalse)只有两个显示大写,33,2.数据的编辑,(1)修改单元格数据选定单元格，直接在编辑栏内修改双击单元格，在单元格内修改(2)选择性粘贴选择性粘贴时，可以只粘贴数值或格式或公式等(3)清除单元格包含内容、格式、批注等单击“编辑”组|“清除”下拉按钮，可以选择清除格式、清除内容、清除批注等，Delete键只删除内容,34,3.填充柄,填充柄的作用数据和公式的复制产生自定义序列产生等差或等比序列选定单元格，直接用鼠标左键拖动填充柄（右键拖动产生快捷菜单）,35,实例：产生等差和等比序列,A1和A2单元格内容为1和3，若在A1到A6单元格内产生一个步长为2的等差序列如何操作？若要产生步长为3的等比序列如何操作？,等差序列：选定A1和A2两个单元格，鼠标左键直接拖动填充柄即可产生等差序列。等比序列：选定A1和A2两个单元格，鼠标右键拖动填充柄松手，在快捷菜单中选择“等比序列”。,36,4.工作表格式化,列宽、行高单元格格式主题表格套用格式样式,37,(1)设置列宽、行高,鼠标光标移动到两列列标或两行行标的交界处，按下鼠标指针可显示列宽或行高。改变列宽或行高：“开始”选项卡|“单元格”组|“格式”用鼠标拖动列间隔线或行间隔线,38,(2)单元格格式,一个单元格除了包含内容和批注以外，还包括单元格格式。单元格格式中包含“数字”、“对齐”、“字体”、“边框”、“图案”、“保护”。设置单元格格式在“设置单元格格式”对话框中设置“开始”选项卡的“字体”组、“对齐方式”组、“数字”组中设置,39,数字格式,在Excel中，可以使用数字格式更改数字（包括日期和时间）的外观，而不更改数字本身。所应用的数字格式并不会影响单元格中的实际数值（显示在编辑栏中的值），而Excel是使用该实际值进行计算的。,40,数字格式,默认情况下，单元格的数字格式为“常规”，系统提供的常用的数字格式为“数值”、“百分比”、“货币”、“长日期”、“短日期”等各式。设置数字格式：选定单元格，“开始”选项卡|“数字”组，单击“数字格式”下拉按钮，从中选择；“设置单元格格式”对话框中，选择“数字”标签，单击“数字格式”下拉按钮中选择或自定义,41,合并单元格（对齐格式）,操作：选定要合并的多个单元格，“对齐方式”组中，单击“合并后居中”按钮合并单元格，可以合并多行、也可以合并多列。,42,(3)主题,主题是一套统一的设计元素和配色方案，是为文档提供的一套完整的格式集合。其中包括主题颜色、主题字体（标题文字和正文文字）和相关主题效果（线条或填充效果）。“页面布局”选项卡|“主题”,43,(4)单元格样式,基于文档主题，一组定义的单元格格式，包括字体、数字格式、边框和填充、保护等，并可将所定义的样式适用于所选定的单元格或单元格区域。单击“单元格样式”，右键单击样式名，可删除、更改、应用样式,44,(5)套用表格格式,预定义的一组表格样式套用表格样式可自定义、修改、删除（系统提供的样式不能删除）操作步骤：选取表格，“开始”|“样式”|“套用表格样式”,45,注意：,套用表格格式后，表格将变成Excel表，要转化为普通的数据区域，“设计”选项卡|“工具”|“转化为区域”,46,总结：,数据是有数据类型的，系统根据所输入的数据将自动判断数据类型选用默认的格式显示出内容；用户也可以先设置格式再输入数据。,47,案例1：Excel的基本操作,【任务】新建一个工作簿，命名为成绩单.xlsx，在Sheet1工作表中，创建学生成绩单表格，并对表格进行格式化。,48,案例1：操作步骤,【任务1】新建工作簿，保存为“成绩单.xlsx”【任务2】创建表格，输入数据【任务3】表格进行格式化,49,案例相关的知识点,数据类型工作表格式化单元格格式单元格样式套用表格格式,50,【任务1】新建工作簿,操作步骤：单击Office按钮，选择“新建”命令；再次单击Office按钮，选择保存，输入文件名为“成绩单”，保存类型为.xlsx,51,【任务2】输入数据,设置单元格格式,操作步骤：输入表格的标题和字段名；学号字段为文本型，输入时数字前加单引号，输入第一个学号后拖动填充柄产生其余学号姓名和性别为文本型，数学、计算机、英语为数值型；出生年月日为日期型，输入时年/月/日或年-月-日格式输入党员否为逻辑型，输入true或false,52,【任务3】工作表格式化,操作步骤：标题“学生成绩表”，选择A1:J1单元格，设置为“合并后居中”，套用单元格样式“标题”字段名一行对齐方式为居中；选择A2:J11区域，套用表格格式“表样式中等深浅11”，选择选项“表包含标题”转化为区域（“设计”|“工具”|“转化为区域”）,53,案例2：Excel的基本操作,【任务】在工作表内建立一个“学生课程表”表格，建立时尽量使用省事、方便的方法输入数据。,54,知识点,用填充柄产生自定义序列用填充柄产生等差序列或等比系列边框、填充颜色的设置,55,第四节Excel的公式与函数,公式与运算符,常用函数,56,1.公式与运算符,（1）公式的建立（2）运算符（3）公式中常量的表示（4）绝对引用、相对引用、混合引用,57,(1)公式的建立,公式的格式：=表达式公式的确认：Enter对勾叉（或ESC）：-放弃公式的元素：常数（常量）引用（单元格地址、区域地址、名称）函数运算符,58,例：,=3+4；（常量）=A1+B1（单元格引用）=SUM(A2:B4)（单元格引用）=SUM(PP)（PP为名称）,59,(2)运算符,算术运算符：-（负号）、%、乘方、*和/、+和-比较运算符:=、=、文本运算符:&引用运算符:(区域)，(并)空格(交)!,60,运算符优先级,引用运算符：冒号：空格逗号！叹号括号算术运算符：负号-百分号%乘方乘除*/加减+-文本运算符：&（连字符）比较运算符：=，=，（不等于）运算结果为TRUE或FALSE,高,低,61,(3)公式中常量的表示,数值型常量：纯数字：不带引号直接输入日期、时间：半角双引号逻辑型常量：不带引号直接输入TRUEFALSE字符型常量：半角双引号例：=IF(E390,“优”,IF(E380,“良”，IF(E3=60,“及格”,“不及格”),62,(4)相对引用、绝对引用,相对引用:=SUM(A3:B5)绝对引用:=SUM($A$3:$B$5)混合引用:=SUM($A3:B$5),63,在下面的数据表格中用公式求销售总额和销售百分比。,案例3：相对引用和绝对引用,64,操作步骤：,(1)在E4单元格内输入公式：=C4*D4，选定E4单元格，拖动填充柄到E6单元格松手（选择“不带格式填充”）；(2)在F4单元格内输入公式：=E4/SUM($E$4:$E$6)，拖动填充柄到F6单元格思考：红色单元格引用改为相对引用结果会怎样？,65,2.常用函数的输入及格式,函数的格式及输入方法错误值数学函数统计函数条件判断函数文本函数,66,(1)函数的格式及输入方法,函数的格式函数名(参数1,参数2,-)函数的输入方式函数的直接输入单击编辑栏的“插入函数”按钮,67,(2)错误值,当某个单元格的公式不能正确计算或公式中所包含的单元格中含有错误值，则在那个单元格中显示一个错误值。#DIV/0！被零除#VALUE！使用了不正确的参数或运算符#运算结果超长或常量数值太长，单元格放不下#NAME？使用了不可识别的名字,68,错误值,#NUM！数据类型不正确#REF！引用了无效单元格#N/A引用了当前无法使用的数值#NULL！指定了两个不相交区域的交集，所以无效,69,(3)数学函数,求绝对值函数ABS()取整函数INT()取余函数MOD()四舍五入函数ROUND()求和函数SUM(),70,绝对值函数ABS函数,格式：ABS(数值)功能：返回指定数值的绝对值例：ABS(2)等于2ABS(-2)等于2,71,取整函数INT函数,格式：INT(数值)功能：返回实数向下取整后的整数值。例：INT(7.6)等于7INT(-7.6)等于-8,72,求余函数MOD函数,格式：MOD(数值1，数值2)功能：返回两数相除的余数，第一个自变量为被除数，第二个自变量为除数。若分母为负，结果为负。MOD(n,d)=n-d*INT(n/d),MOD(15,4)等于3MOD(-13,-4)等于1MOD(-13,4)等于3MOD(13,-4)等于-3MOD(3,0)等于#DIV/0!,73,格式：ROUND(数值，数位)功能：ROUND函数根据给定数位将指定数值四舍五入。负值表示小数点前对应的位数，小于这些位数的值四舍五入，而该位取0。,四舍五入函数ROUND,ROUND(837.1649,2)等于837.16ROUND(837.1649,-2)等于800ROUND(837.1649,0)等于837ROUND(857.3,-2)等于900,74,求和函数SUM函数,格式：SUM(数值1，数值2，.)功能：用于计算自变量中所有数值型数据项之和。例：=SUM(10,30)=SUM(A1:F1),75,(4)统计函数,计数函数COUNT()求平均值函数AVERAGE()求最大值函数MAX()求最小值函数MIN(),76,计数函数COUNT函数,格式：COUNT(参数1，参数2，)功能：COUNT函数的返回值是自变量数据中数值型数据的个数。参数可以是位置引用、名称等，含有的数据类型任意，但只有数值型数据才被计数。,77,实例：,在学生成绩单中，用函数统计人数。,下面的答案中正确的是：A、=COUNT(A3:A16)B、=COUNT(C3:C16)C、=COUNT(D3:D16)D、=COUNT(F3:F16),正确答案为：C和D,78,求平均值函数AVERAGE函数,格式：AVERAGE(数值1，数值2，.)功能：返回自变量指定的一系列数值的平均值。格式和SUM函数相同。,79,求最大值、最小值函数MAX()、MIN(),(1)格式：MAX(数值1，数值2，)功能：返回自变量中所有数值型数据的最大值(2)格式：MIN(数值1，数值2，)功能：返回自变量中所有数值型数据的最小值,80,(5)条件判断函数IF(),格式：IF(参数1，参数2，参数3)参数1：判断条件参数2：符合条件时返回值参数3：不符合条件时返回值例1：=IF(b2=2时，y=2x+3；当0=x=0,4,-3),82,(6)文本函数,LEN函数LENB函数MID函数,83,字符串长度函数LEN、LENB函数,格式：LEN(字符串)LENB(字符串)功能：返回自变量的字符串的长度，即字符串所含字符的个数。LEN和LENB的区别在于对汉字或全角字符的处理上，LEN函数中一个汉字或全角字符被认为是一个字符，LENB函数中一个汉字或全角字符被认为是两个字符。,84,实例：,=LEN(“englishbook”)的计算结果是12=LEN(“”)的计算结果是1=LEN(“北京”)的计算结果是2=LENB(“北京”)等于4,85,取子字符串函数MID函数,格式：MID(正文，起始，字符个数)功能：从字符串中抽取子字符串。例：=MID(“firstsecondthird”,7,6)返回字符串“second”。=MID(1234,2,2)返回字符串“23”,86,案例4：公式与函数,【任务】在“学生成绩表”中，求每个学生的总分和平均分以及等级，等级要求：85分以上为“优秀”，85分以下60分以上为“合格”，60分以下为“不合格”。,87,案例涉及到的知识点,SUM函数AVERAGE函数IF函数填充柄,88,任务【1】：求和,操作步骤：选择I3单元格，输入“=SUM(F3:H3)”，其中数据区域可用鼠标选取；用鼠标左键向下拖动I3单元格的填充柄，一直拖到I10,89,任务【2】：求平均值,操作步骤：选择J3单元格，输入“=AVERAGE(F3:H3)”，填充柄拖动过程如上；平均分列设置数字格式，保留小数点后两位,90,任务【3】：求等级,操作步骤：选择K3单元格，输入“=IF(k3=85,“优”，IF(J4=60,“合格”,“不合格”)，填充柄拖动过程如上,91,第五节数据清单,数据清单的概念排序数据筛选分类汇总数据图表,92,1.数据清单,MicrosoftExcel提供了一系列数据管理功能，可以很方便地管理和分析数据清单中的数据。如排序、筛选、数据透视表作汇总、用数据图表显示数据的变化等等。,93,数据清单的概念,由包含相关数据的一系列数据行组成，是Excel中的数据库表格。行为记录，列为字段第一行称之为字段名（或称列标）在数据清单中，避免在表内有空行或空列,94,2.数据的排序,普通排序原则：“升序”-从小到大数字：从负到正文本：ASCII码的顺序逻辑值：FALSETRUE错误值：优先级相同空白始终排在最后Excel2007下排序可以按数值大小、单元格颜色、字体颜色、单元格图标排序。,95,排序操作,选择数据区域或鼠标放在数据清单内，单击“数据”|“排序和筛选”|“排序”按钮；在排序对话框中，选择关键字和排序依据、次序注意：选取的数据区域是否包含标题，排序结果将不同排序过程中选中了一个区域，则区域之外的数据不排序,96,排序实例：,在“超市业绩表”中完成如下排序。按地区降序排序，若地区相同再按店面升序排序；按北京、上海、广州顺序排序；,97,操作提示：,以地区为主关键字段，排序依据为“数值”，次序为“降序”；以店面为次关键字段，排序依据为“数值”，次序为“升序”，选择“数据包含标题”；先建立自定义序列，序列为北京、上海、广州，以地区为主关键字段，排序依据为“数值”，次序为“自定义序列”，在对话框中选择序列,98,3.数据筛选,数据筛选：按指定条件对数据清单中的记录进行选取，只显示满足条件的记录，而隐藏其它记录。两种筛选方法：自动筛选和高级筛选，高级筛选适用于条件比较复杂的筛选筛选时，根据数据清单中不同字段的数据类型，显示不同的筛选选项。如字段为文本型，则可以按文本筛选。,99,筛选和取消筛选操作：,筛选鼠标放在数据清单内，单击“数据”|“排序和筛选”|“筛选”按钮；单击筛选字段名右侧的筛选按钮，设置条件取消筛选若取消对某一字段的筛选，单击字段名右侧的筛选按钮，选择“全部”；若取消筛选状态显示所有记录，再次单击“数据”|“排序和筛选”|“筛选”按钮；,100,操作实例：,对学生成绩单，按下面的要求进行筛选。筛选出外语系和法律系的学生记录；筛选出三门课成绩都在85分以上的记录；筛选出1978年到1979年间出生的记录；筛选出数学成绩最高的5个人记录,101,4.分类汇总,目的：在数据清单中快速汇总同类数据，可分类进行求和、计数、求均值、求最值等计算前提：先要将数据按分类字段进行排序，再进行分类汇总,102,操作步骤：,单击数据清单的任一单元格将数据按分类字段进行排序单击“数据”|“分级显示”|“分类汇总”按钮，在“分类汇总”对话框中选择：分类字段：选排序所依据的字段汇总方式：选用于分类汇总的函数方式选定汇总项：选要进行汇总计算的字段单击“确定”按钮,103,分类汇总,取消分类汇总结果在“分类汇总”对话框中选择“全部删除”命令按扭分类汇总结果的层次显示汇总表的左上角有按钮，分别标明“1”，“2”，“3”等，说明层次。汇总表左面的“+”按钮，每个按钮对应一个汇总行，单击“+”按钮，显示出汇总行的明细数据。,104,操作实例：,在“超市业绩表”中用分类汇总完成统计。求每个地区的总销售额求每个店面的平均销售额求每个季度的销售记录次数,105,操作提示：,以地区为关键字段排序（和次序无关）；“分类汇总”对话框中设置以“地区”为分类字段，汇总方式为“求和”，汇总项为“