第1章 EXCEL基础.ppt

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,第,1,章,EXCEL,基础,1.1 EXECL,简介,1.2,表格设计,1.3,图表制作,1.4,数据管理,1.1 EXECL,简介,工作表结构,工作簿操作,工作表操作,窗口操作,系统选项,常见错误类型,工作表结构,工作簿,即,EXCEL,文件，后缀名为,.,xls,，存贮,EXCEL,文件自动生成。,工作表,一个工作簿由若干张工作表组成。,EXCEL,具体数据由工作簿存储与管理。,单元格,一张工作表由,65536,行、,256,列组成。列号与行号交叉即为单元格，如,A1,、,D10,等。所有数据均存贮于单元格中。,区域,由若干个单元格构成。三种：,1.,连续区域：,矩形区域的第,1,个单元格和最后,1,个单元格中间用“,:”,表示，如“,B10:G25”,。,2.,集合区域：,由互不相连的单元格或区域组成，中间用,”,”,连接。如：“,B10:C15,C18,D20:E25”,。,3.,交集区域：,由两个区域的公共单元格组成，中间用“”连接，如“,A1:B2 B1:C2”,，包括,B1,、,B2,共,2,个单元。,工作簿操作,新建工作簿,保存工作簿,关闭工作簿,打开工作簿,工作表操作,切换工作表,当前工作表（活动工作表）。窗口左下点击可切换工作表，或,ctrl+pagedown/pageup前一张,/,后一张。,插入工作表,默认工作表为,3,。二法：,其一，右键单击工作表标签，“插入”，在当前工作表前插入新工作表。,其二，菜单，“插入,/,工作表”，同上。,删除工作表,二法。操作类同插入。,移动工作表,改变工作表排列顺序。二法：,其一，左键点击目标工作表标签，拖动至目标位置即可；,其二，右键点击目标工作表，选“复制或移动工作表”，在窗口下部选择移动位置，“确定”即可。该功能也可将工作表从一个工作簿移动至另一个工作簿，方法是窗口中上部改变工作簿。,复制工作表,二法：,其一，,ctrl,目标工作表，左键拖至目标位置即可获得副本。,其二，右键点击目标工作表，选“复制或移动工作表”，从窗口中选“建立副本”，操作同移动工作表。,重合名工作表,二法：,其一，双击目标工作表，直接更名；,其二，右键点击目标工作表，选“重命名”。,窗口操作,打开多个工作簿,EXCEL,环境下，可同时打开多个工作簿，但只有一个为活动工作簿。,多个窗口中同时打开一个工作簿,“窗口,/,新建工作簿”即可。,重排窗口（打开多工作簿情况下）,平铺、水平并排、垂直并排、层叠四种。,“窗口,/,重排”即可。,隐藏窗口,/,取消隐藏窗口,“窗口,/,隐藏”，可将当前窗口隐藏。,拆分窗口（方便对照查看工作表不同部分）,垂直拆分：点击某一列，“窗口,/,拆分”；,水平拆分：点击某一行，“窗口,/,拆分”；,垂直与水平拆分：点击某一单元格，“窗口,/,拆分”，产生四个拆分窗口。,(,名单演示）,冻结窗口（固定某几行或某几列浏览时不动）,点击某一行或某一列，“窗口,/,冻结窗口”。,注意：,拆分与冻结的区别。,系统选项,“工具”,/“,选项”，设置各种选项。常用：,1,、最近使用的文件列表,常规,2,、新建工作簿的工作表数,常规,3,、默认文件位置经,常规,4,、按,Enter,键后移动位置,编辑,5,、是否显示网络线,视图,6,、是否显示零值,视图,7,、是否保存自动恢复信息,保存,8,、密码设置,安全性,9,、自定义序列,自定义序列,常见错误类型,1,、,DIV/0!,除数为,0,了,2,、,N/A,有些单元格没有数值，导致函数或公式出错,3,、,NUM,！数值函数或公式中产生太大或太小的数字，单元格不能表示,4,、,VALUE,！不能运算的参数或不能运算的对象,5,、,NULL,！不正确的区域运算符或不正确的单位格引用,6,、,REF,！单位格引用无效,7,、,NAME,？名称有拼写错误、使用了不存在的名称、删除了公式中使用的名称,8,、输入到单元格的数值太长或公式计算结果太长，单元格不能容纳。,1.2,表格设计,编辑表格,单元格引用,单元格、行、列操作,设置表格样式,绘图工具,插入对象,保护工作簿和工作表,编辑表格,1,、输入数据,（,1,）输入基本数据,A,、选择单元格，直接输入。数据同时在单元格与编辑栏显示。确认，回车，取消，,ESC,键。,B,、输入文本型数值，“,”,括起来,右对齐。,C,、输入日期型数据，按“年月日”。“格式”,/“,单元格”可改变日期显示格式。,D,、输入时期型数据，按“时：分：秒”。改变显示格式同上。,E,、对连续单元格输入数据，可选择区域，再输入，,Enter,后在区域内移动。,（,2,）输入公式,A,、公式构成：常数、单元格、函数、运算符。,如：,A1,60%,、,SUM,（,B1,：,C20,）,B,、公式前需加“”，否则系统认为输入字符串。,C,、输入函数，可者直接输入，或者“插入”,/“,函数”或者,fx,键。,（,3,）输入序列,A,、数值序列,等差序列。选择起始单元格（为某一数值），,Ctrl+,填充句柄，拖动至目标单元格，即可获得加,1,序列。若步长值不为,1,，“编辑”,/“,填充”,/“,序列”，修改步长值即可。步长值为负值，可获等减值序列。,等比序列。同上，将等差修改为等比。,B,、日期序列,默认按日形成序列。可修改为按月、年形成日期序列。方法同上。,C,、文本序列（,CTRL,的作用与,A,不同）,如一月至十二月；星期一至星期天等。操作方法同上。,2,、选择区域,对数据操作，往往先选择区域，再操作。,（,1,）选择一个单元格。直接点击。,（,2,）选择矩形区域。两法：其一，确定起始单元格，左键拖动至最后一个单元格；其二，确定起始单元格，,Shift+,最末单元格。,（,3,）选择一行或一列。单击行、列标。,（,4,）选择连续多行、多列。对行列标操作，方法可（,2,）。,（,5,）选择不连续单元格、区域或行列。,Ctrl,键，其他同上。,（,6,）选择整张工作表。单击表中行列交叉处（空白处）。,3,、删除数据,（,1,）删除单元格部分数据。双击单元格，在单元格内或编辑栏内，用,Delete,键、,Backspace,键操作。,（,2,）删除单元格或区域全部数据。几法：,A,、选中区域，,Delete,键；,B,、选中区域，快捷键,清除内容,；,C,、选中区域，“编辑”,/“,清除”，清除全部、清除内容、清除格式、清除批注；,D,、填充句柄清除：选中区域，用填充句柄拖动，即清除；或用临近空白单元格句柄，拖动覆盖要删除区域。,4,、移动与复制,（,1,）移动,第一步：选择移动区域，,Ctrl+X,或右键选“剪切”或“编辑”,/“,剪切”或工具栏剪切按键；,第二步：选择目标区域左上角单元格，,Ctrl+V,或右键选“粘贴”或“编辑”,/“,粘贴”或工具栏粘贴按键。,（,2,）复制,将,Ctrl+X,改为,Ctrl+C,，其他同移动。,5,、查找与替换,（,1,）查找,Ctrl+F,或“编辑”,/“,查找”，在对话框中输入查找内容。,（,2,）替换,Ctrl+H,或“编辑”,/“,替换”，在对话框中输入查找内容与替换内容。,单元格引用,1,、单元格引用方式,（,1,）地址引用,A,、相对引用。相对某一单元格地址的引用。当公式复制或其他操作时，相对地址会随目标单元格变化而变化。如：,A,B,10,100,2,20,200,A1,A2,当将,A3,单元复制到,B3,时，,B3,公式为,B1,B2,，这就是相对引用。,B,、绝对引用。用于表示单元格在工作表中的确切位置，当公式复制时，绝对地址不会随目标单元格的变化而变化。,如：,A3=$A$1+$A$2,，将,A3,复制到,B3,时，,B3,=$A$1+$A$2,。,绝对引用又细分行绝对引用、列绝对引用，如,A3,A$1+A$2,、,$A1+$A2,。,（,2,）名称引用,可以给某一单元格或区域取名，引用时用名称即可。同一工作簿内不能取重名。,A,、选择要取名的单元格或区域；,B,、取名两法：,其一，名称框,(,左上）中输入名称；,其二，“插入”,/“,名称”,/“,定义”，对话框中增加、删除已有名称。,如定义：,A1,：,A10,名称为“流动资产”，,A11=sum(,流动资产,),。,2,、引用同一工作表单元格数据,如,A3=C1,；,A3=data1(,某一单元格取名为,data1);,A3=sum(c1:c10,b1:b10),。,3,、引用同一工作簿不同工作表数据,如,A3=sheet2!c1;,A3=data2(data2为,sheet3,的,A3);,A3=sum(sheet2!c1:sheet2!c10),4,、引用不同工作簿的工作表数据,如,A3=c:mydatabook2.xlssheet2!c1,单元格、行、列操作,1,、插入单元格,“插入”,/“,单元格”，对话框选择。,2,、删除单元格或区域,“编辑”,/“,删除”，对话框选择。,3,、合并单元格,选择要合并单元格，“格式”,/“,单元格”,/“,对齐”，选择合并单元格。注：合并单元格以左上单元格取名。,4,、取消合并单元格,操作同上。,5,、设置行高列宽,两法：其一，第,N,与第,N+1,行分隔线调整；其二，右键行标,+,行高,，精确调整；或“格式”,/“,行”,/“,行高”调整。列同理。,5,、插入与删除行、列,右键行标,+,插入,行，或者“插入”,/“,行”，当前行上方插入一行。删除及列操作同理。,6,、隐藏和取消隐藏行、列,右键行标或区域,+,隐藏,，或“格式”,/“,行”,/“,隐藏”，隐藏选中行。取消隐藏行及列操作同理。,设置表格样式,表格样式：数值的显示格式、日期及时间显示格式、字体、字号、字形、对齐方式、边框、颜色等。“格式”,/“,单元格”进入。,1,、数字,调整数值、货币、会计专用、日期等显示格式。,2,、对齐,调整对齐方式、文本控制、从右到左等。,3,、字体,调整字体、字形、字号、特殊效果等。,4,、边框,对选择单元格或区域的上、下、左、右边框线调整。,5,、图案,调整单元格或区域的底纹、图案。,6,、保护,默认情况下，所有单元格都具保护属性，该属性只有选择“工具”,/“,保护”，保护属性才生效。但取消“保护”属性，设置工作表保护，也能修改。,案例,7,、条件格式,如,A B,选中区域,A1,：,A4,1 30 “,格式”,/“,条件格式”，对话框中：小于,2 40 35,，“格式”选择红色，则,A1,、,A4,变,3 35,色。,4 20,如何进行工作表保护,当下发工作表时，只希望填表录入数据，不能修改格式，方法：,1,、选定数据区（用户可修改），“工具”,/“,保护”,/“,允许用户编辑区域”（最好不要输入密码）,再在选项中“保护工作表”；,2,、对工作表保护，同“工具”,/“,保护”,/“,保护工作表”。,绘图工具,“视图”,/“,工具栏”,/“,绘图”，窗口显示工具栏。如文本框、矩形、椭圆、直线、箭头等。,1,、文本框,单击文本框，可在工作表中放置。右键文本框边，可设置文本框格式。,2,、改变对象的叠放次序,多个对象框重叠时，右键,+,叠放次序,。,3,、组合对象,选择：,Shift+,组合对象，再右键,+,组合,，则移动某一组合对象是地，其他组合对象一并移动。,插入对象,1,、插入批注,右键,+,插入批注,，或“插入”,/“,批注”，单元格右上角显示线色三角形。,右键,+,编辑批注,，或“插入”,/“,编辑批注”。,右键,+,删除批注,，或“编辑”,/“,清除”,/“,批注”。,2,、插入图片,“插入”,/“,图片”,/“,来自文件”，选“打开”。,3,、插入公式,“插入”,/“,对象”,/“Microsoft,公式,3.0”,，可插入复杂公式。,保护工作簿和工作表,1,、保护工作表,(1),设置单元格锁定状态。“格式”,/“,单元格”,/“,保护”。不选“锁定”，则表被保护后，不选锁定的单元格能编辑，而锁定的单元格不能编辑。,(2),保护工作表。“工具”,/“,保护”,/“,保护工作表”，输入密码及确定密码。,2,、允许用户编辑区域,以上对未设置“保护”的单元格，所有用户都可修改，不安全。选择保护区域,(,全部,),，“工具”,/“,保护”,/“,允许用户编辑区域”，选择“新建”，输入区域，“确定”后输入区域密码（密码可为空），再“保护工作表”，输入密码后，用户对未保护区域修改也需输入密码。,3,、保护工作簿,“工具”,/“,保护”,/“,保护工作簿”，则不能再插入、删除、更名工作表。,1.3,图表制作,部门,第一季度,第二季度,第三季度,第四季度,合计,销售一部,1000,1200,1500,600,4300,销售二部,800,1000,1300,500,3600,销售三部,700,900,1300,500,3400,合计,2500,3100,41001600,11300,以下资料讲述图表制作：,图表制作的一般方法,1,、柱形图,(1),选中区域数据,(,含行列标题,),；,(2)“,插入”,/“,图表”,/“,柱形图”，向导式完成；,(3),系列选择：以行为系列，则每组图形中反映行数，以列为系列，则每组图中反映列数。,(4),图表存放：两种，本表插入，工作簿中新增工作表插入。,图表标题,X,轴标题,Y,轴标题,图例,同理，可制作折线图、圆形图等图形。,图表编辑,(1),移动图表：空白处,+,左键,+,拖动；,(2),改变大小：,8,个小黑点,+,左键,+,拖动；,(3),删除图表：选中,+Delete,键；,(4),图表类型：右键图表,图表类型”改变；,(5),图表数据源：同上，选择“数据源”，重置数据源、系列、系列属性等；,(6),图表选项：同上，选择“图表选项”，可重置图表标题、坐标轴、网格线、图例、数据标志等。,1.4,数据管理,通过以下资料讲授,EXCEL,数据管理功能：,日期,部门,商品,单价,销量,销售额,2007.1.10,销售一部,冰箱,2000,50,100000,2007.1.11,销售一部,彩电,3000,100,300000,2007.1.12,销售一部,洗衣机,1200,100,120000,2007.1.13,销售一部,空调,2200,150,330000,2007.1.20,销售二部,冰箱2000,100,200000,2007.1.21,销售二部,彩电,3000,150,450000,2007.1.22,销售二部,洗衣机,1200,120,144000,2007.1.23,销售二部,空调,2200,160,352000,2007.1.25,销售三部,冰箱,2000,120,240000,2007.1.26,销售三部,彩电,3000,200,600000,2007.1.27,销售三部,洗衣机,1200,150,180000,2007.1.28,销售三部,空调,2200,220,484000,1,、数据有效性,(,数据输入控制,),(1),日期：对单元格数据输入的有效性进行控制并提示信息。“数据”,/“,有效性”，按提示设置。如日期必需在,2007.1.10-1.28,之间。,(2),部门：选择部门档案表输入，可控制差错。方法：第一，在其他表中建立部门档案，并对档案区域取名；第二，在数据输入区，“数据”,/“,有效性”,/“,序列”，,=,取名，则数据只能从档案表选择输入。,(3),商品：同部门。,(4),单价：第一，在另一工作表中定义含商品的单价表，并给这一区域取名,JMB,；第二，数据输入区,=,vlookup,(,单元格，,JMB,，取,JMB,区域的第几列，,false),。使单价自动获取。,(5),销售量：合理控制，如,=and(isnumber(e4)e40),，,E4,为数值，且不为,0,。,2,、记录单,选择带项目（第一行）的区域，“数据”,/“,记录单”，可逐条编辑或增加、删除记录。常用于：表格太宽，记录单方便。但该状态无法控制和校验。,3,、排序,重排记录显示顺序。光标在数据表内，“数据”,/“,排序”，“商品,+,部门,+,日期”重排。,4,、筛选,(1),自动筛选：“数据”,/“,筛选”,/“,自动筛选”，各列标题均出现下拉列表框，可筛选数据。注：各筛选条件为并且关系。取消则同上进入，选全部显示。,(2),高级筛选：第一，建立筛选条件,(,表“高级筛选条件”,),；第二，选择筛选区域，“数据”,/“,高级筛选”，列表区域为要筛选区域，条件区域为设置条件。确定可获取。,如：筛选销量大于,150,的彩电记录或冰箱大于,100,的销售记录。再如：筛选销量大于,100,或销售额大于平均销售额的记录。,5,、分类汇总,按部门分类汇总：,“数据”,/“,分类汇总”，分类字段：部门；求和；汇总项：销售额。即可获得分类汇总表。,隐藏与展开明细记录。,取消分类汇总：再次进入分类汇总对话框，“删除全部”，可返回。,6,、数据透视表和数据透视图,(1),对数据进行全方位、多角度汇总与对比分析。,(2),选择区域,(,含行列标题,),，“数据”,/“,数据透视表和数据透视图”，向导建立。生成新的数据区或表，不同于筛选。,(3),字段选择：日期,页字段；部门,-,行字段；商品,-,列字段；销售额,-,数据区域。可生成透视表。,(4),设置报表格式：“数据透视表工具栏”,/“,设置报表格式”，可按模板输出。,(5),改变透视表布局：“数据透视表工具栏”,/“,数据透视表向导”,/“,布局”，可重置各种字段,(,拖入与拖出,),。,(6),删除数据透视表：“数据透视表工具栏”,/“,数据透视表”,/“,选定”,/“,整张表格”，再“编辑”,/“,清除”,/“,全部”。,(7),数据透视图：“数据透视表工具栏”,/“,图表向导”，即可。,