专转本计算机课件-电子表格Excel2003

第九章电子表格Excel2003一、Excel2003基本知识Excel(文件扩展名为“.xls”)，默认文件夹是“Book1”Excel的工作窗口工作簿窗口上方的水平条称为编辑栏（包括：名称框、编辑工具按钮、编辑框），活动单元格的单元格引用（地址）总是出现在编辑栏的名称框中；在编辑栏中总是显示当前活动单元格的内容或公式，用户可以对当前活动单元格的内容或公式进行编辑；工作表区域是Excel的主窗体，其每个单元格由列号和行号来确定；Excel中每个工作簿可以包含多个工作表（右击标签栏可快速找到工作表或给工作表加上颜色），按住Ctrl键单击可以选中多个不连续的工作表；拆分框可用来同时查看同一工作表的不同部分。二、建立和编辑工作表单元格选择：在任意时刻（包括在选中区域时），活动单元格只有一个使用鼠标或键盘（Tab键和光标键）选择单元格；选择连续单元格区域：用鼠标直接拖动或使用shift和光标组合键进行选择；选择不连续单元格区域：用鼠标和Ctrl选择；在编辑栏的“名称框”中，输入要选择的单元格地址或地址范围；输入、编辑数据：2.1输入数值用英文半角输入数字0~9运算符：+、-、*、/、%、^；=、>、<、>=、<=、<>2.2文字连接符：&2.3输入日期时间2000/10/12000-10-1输入分数，如：8/9，方法是：输入0空格8/9输入当前日期：Ctrl+；输入当前时间：Ctrl+shift+；2.4数字组成的文本邮政编码：’5280002.5在单元格中换行格式—单元格—对齐—自动换行。键入Alt+Enter2.6输入序列1.输入等差序列选起始单元格—输入初值—选定区域—编辑—填充—序列—等差序列、步长。例：2000-10-1为星期天，列出后面9个星期天日期。在A1中输入初值：2000-10-1—区域：A1:A10—编辑—填充—序列—序列：列、类型：日期、步长：7。2.输入等比序列例：2，4，8，16，32，64，128，……1024。（只能在菜单中操作）3.输入数字与文本的组合序列例：1990年、1991年、……。（提示：在类型中选择“自动填充”）4.用鼠标（填充柄）进行序列输入左键拖动填充柄—在智能填充选项选择右键拖动填充柄—在弹出菜单中选择双击填充柄—自动填充至左列有数据的位置2.7自定义序列1.从工作表导入选定序列—工具—选项—自定义序列—导入。例如：周一……周日2.直接在自定义序列中建立序列工具—选项—自定义序列—输入序列—添加。例：优、良、中、及格、不及格；计算机，主机，显示器，打印机，键盘，鼠标，音箱。2.8选择性粘贴选择性复制单元格中的公式、格式或数值。复制选定区—指定粘贴区—编辑—选择性粘贴—公式、格式或数值。*2.9有效数据1.设置有效条件数据—有效性—设置—许可：数字、选择条件。例如：日期介于1900-1到2011-1。*2.10输入到同组单元格选中一组单元格—输入数据—按[Ctrl]+[Enter]键。快速填充数据到多个不连续的单元格中三、工作表的管理与格式化工作表的删除、插入和重命名：操作前都要对工作表进行选定操作。对工作表的操作不能撤销“插入”菜单→工作表；“编辑”菜单→删除工作表重命名：鼠标双击工作表标签→输入工作表名→回车复制和移动工作表：“编辑”菜单→移动和复制工作表，选择目标工作簿→选择操作是复制还是移动；右键单击工作表名称→选择操作是复制还是移动鼠标键拖动是移动，按住Ctrl键拖动是复制工作表可以跨工作簿复制或移动工作表的拆分：水平、垂直、水平和垂直拆分选中行、列或单元格→窗口菜单→拆分；拖动拆分条双击拆分线或利用窗口菜单→撤销拆分窗口可以撤销拆分。工作表的冻结：固定显示特定的行和列窗口菜单→冻结拆分窗口工作表的格式化：（P155）5.1数字格式1.改变数字的小数位数、千分位格式。工具栏—增加/减少小数位、千位分隔样式。5.2字符格式字符格式：字体、字号、字形、下划线、颜色。5.3时间和日期格式格式—单元格—数字—日期—类型。5.4自动套用格式选定表格区域—格式—自动套用格式。*5.5条件格式选定表格区域—格式—条件格式—条件1：单元格数值为—小于60分—红色字；条件2：单元格数值为—大于或等于60分—绿色字。5.6合并单元格格式—单元格—对齐—合并单元格。5.7数据的对齐与旋转1.数据的对齐与旋转格式—单元格—对齐—水平对齐/方向。2.跨列居中*5.8控制单元格内容的缩进工具栏—增加/减小缩进量。5.9控制行高和列宽1.自动调整：双击行标、列标分隔线。2.鼠标拖动：鼠标指针移到行标、列标间变成双向箭头时拖动。3.菜单调整：格式—列—列宽—输入列宽值。5.10添加边框1.取消网格线：工具—选项—视图—网格线。2.添加边框：格式—单元格—边框；或工具栏—边框按钮。5.11添加底纹和图案格式—单元格—图案。四、公式与函数输入公式：选择要输入公式的单元格→输入“＝”→输入公式→回车或单击编辑栏中的“√”按钮1.1公式的组成以（=）开始的引导符、数字、单元格引用和运算符。1.2Excel公式中的运算符1.数学运算符：+、-、*、/、%、^；2.比较运算符：=、>、<、>=、<=、<>，结果为TRUE或FALSE；例如：8>5结果为(TRUE),3>5结果为(FALSE).3.文本运算符：&，如="Win"&"dows"的结果为"Windows"。4.引用运算符：:冒号：区域运算符，如A2:A9，=SUM(B3:C5)。,逗号：联合运算符，如=SUM(B3,B15,D5:D15)。空格*：交叉运算符，两单元区域公有部分，如=SUM(A1:C3B2:D4)=SUM(B2:C3)=SUM(B2,C2,B3,C3)。A1B2C2B3C3D45.运算符的优先级引用运算符：:空格，算术运算符：-%^*/+-连接运算符：&比较运算符：=<><=>=<>1.3绝对引用(P159)绝对引用：公式所引用的单元格固定不变。如，A3=$A$1+$A$21.4相对引用(P159)相对引用：公式所引用的单元格跟随当前行列变化。如，A3=A1+A2把公式A3复制到B3后，列增1成为B3=B1+B2A列B列C列D列1行A1B1C1D12行A2B2C2D23行A3B3C3D31.5混合引用及各种引用间的切换（P159）1.混合引用：A3=A$1+A$22.各种引用间的切换按功能键F4第1次$A$1第2次A$1第3次$A1第4次A1行绝对引用绝对引用相对引用相对引用列绝对引用相对引用绝对引用相对引用函数：函数包含三个部分：函数名、圆括号、参数；2.1函数的输入1.使用函数向导插入—函数[fx]；或工具栏—函数按钮[fx]—常用函数—AVERAGE—键入求平均值的数据区域或用鼠标拖动选定区域。2.键盘输入函数=SUM(A1:A10)2.2自动求和选存和数的单元格—常用工具栏—[自动求和]按钮“Σ”。2.3常用函数常用函数：SUM、COUNT、AVERAGE、INT、MAX、IF。数学与三角函数：SUM、INT、ROUND、MOD、SQRT。统计函数：COUNT、AVERAGE、MAX、STDEV。文本函数：MID。逻辑函数：IF、NOT、AND、OR。日期与时间函数：DATE、TIME、NOW、YEAR。1.和函数SUMSUM是数学与三角函数。2.计数函数COUNTCOUNT是统计函数，统计工作表中有效数据个数。例如：COUNT(A3:G6)3.算术平均值函数AVERAGEAVERAGE是统计函数。4.取整函数INTINT是数学与三角函数。例如：INT(0.99)=05.舍入函数ROUND（P131）ROUND是数学与三角函数，按指定位数舍入。例如：ROUND(2.15,1)=2.26.最大值函数MAX（P132）MAX是统计函数。例如：MAX(3,5)=5*公式和函数的深入使用3.1工作表之间引用数据1.工作表间的函数和公式运算Sheet1!A1Sheet2!B2。2.工作簿间的函数和公式运算[Book1.xls]Sheet1!$A$1；[Book2.xls]Sheet2!$B$2。错误值的含义1.错误值"#####!"单元格显示不下。2.错误值"#VALUE!"输入数字或逻辑值时，错为文本。3.错误值"#DIV/0!"除数引用了“空”或“0”的单元格。4.错误值"#NAME?"删除了在公式中使用的名称，或使用了不存在的名称。如，在公式中含有全角引号[“”]、逗号[，]。5.错误值"#N/A"在函数或公式中没有可用数值。6.错误值"#REF!"单元格引用无效。7.错误值"#NUM!"函数的数字参数错用了其它参数。8.错误值"#NULL!"使用了不正确的区域运算符或不正确的单元格引用。9.公式错误疑难解答圆括号要成对引用单元格区域要正确使用区域运算符，外部引用要包含工作簿的路径和名称。公式输入数字不要加格式符号，如$1,000，只输入1000。五、数据管理1）数据排序：（P164）数据菜单→排序→定义主、次、第三关键字和排序方式→确定。在浏览Excel表格中的数据时，为了查找的方便，我们经常要对数据进行排序。下面，我们以“员工工资表”为例，来看看具体的操作过程：职工号姓名职称工龄基本工资奖金水电气实发工资101李芳副教授20880280110.5102王刚教授301200310125103周舟讲师1075021088104吴双副教授15920260101105张峰讲师778816097快捷排序：按“工龄”由长到短进行排列：选中“工龄”列任意一个单元格，然后按一下“常用”工具栏上的“降序排序”按钮即可。注意：①数字按由小到大排序。②如果排序的对象是中文字符，则按“汉语拼音”顺序排序。③如果排序的对象是西文字符，则按“西文字母”顺序排序。2、多条件排序：注意：在“排序”对话框中，应根据数据表的具体情况，确定选中“有标题行”或“无标题行”选项（默认为“有标题行”。）2）数据筛选：数据菜单→筛选→自动筛选（筛选只是暂时隐藏不满足条件的记录，而不是删除）。Excel中提供了两种数据的筛选操作，即“自动筛选”和“高级筛选”。如何区分这两种筛选模式，以便熟练掌握和应用，让我们来看看吧：2.1自动筛选“自动筛选”一般用于简单的条件筛选，筛选时将不满足条件的数据暂时隐藏起来，只显示符合条件的数据。图1为某单位的职工工资表，打开“数据”菜单中“筛选”子菜单中的“自动筛选”命令，以“基本工资”字段为例，单击其右侧向下的列表按钮，可根据要求筛选出基本工资为某一指定数额或筛选出基本工资最高（低）的前10个（该数值可调整）记录。还可以根据条件筛选出基本工资在某一范围内符合条件的记录，“与”、“或”来约束区分条件。如图2，根据给定条件筛选出基本工资大于等于300且小于350的记录。另外，使用“自动筛选”还可同时对多个字段进行筛选操作，此时各字段间限制的条件只能是“与”的关系。如筛选出“基本工资”和“职务工资”都超过380的记录。*2.2高级筛选“高级筛选”一般用于条件较复杂的筛选操作，其筛选的结果可显示在原数据表格中，不符合条件的记录被隐藏起来；也可以在新的位置显示筛选结果，不符合的条件的记录同时保留在数据表中而不会被隐藏起来，这样就更加便于进行数据的比对了。例如我们要筛选出“基本工资”或“职务工资”超过380且“实发”工资超过700的符合条件的记录，用“自动筛选”就无能为力了，而“高级筛选”可方便地实现这一操作。将“基本工资”、“职务工资”和“实发”三字段的字段名称复制到数据表格的右侧（表格中其他空白位置也可以），在图中所示位置输入条件，条件放在同一行表示“与”的关系，条件不在同一行表示“或”的关系。3）分类汇总：（P166）选定要汇总的列→数据菜单→分类汇总图1所示是我们日常工作中经常接触到Excel二维数据表格，我们经常需要通过需要根据表中某列数据字段（如“工程类型”）对数据进行分类汇总。下面我们针对两种不同的分类汇总需求，介绍不同的解决方案。这两种需求分别是：既想分类汇总又想分类打印、不想分类打印只是想随时查看各类数据的明细和统计情况（此处假定将统计结果保存在另外一个工作表中）。需求一、既想分类汇总，又想分类打印解决方案：直接利用Excel内置的“分类汇总”功能来实现。1、选中工程类型列的任意一个单元格，按一下“常用”工具栏上的“升序排序”或“降序排序”按钮，对数据进行一下排序。注意：使用“分类汇总”功能时，一定要按分类对象进行排序！2、执行“数据→分类汇总”命令，打开“分类汇总”对话框（如图2）。3、将“分类字段”设置为“工程类型”；“汇总方式”设置为“求和”；“选定汇总项”为“面积”和“造价”；再选中“每组数据分页”选项。最后，确定返回。4、分类汇总完成（图3是“综合办公”类的打印预览结果）。4）图表：常见图表类型及其作用：饼图、条形图、柱形图、折线图、面积图、股价图、圆环图；插入菜单→图表（插入和编辑图表）Excel图表可以将数据图形化，更直观的显示数据，使数据的比较或趋势变得一目了然，从而更容易表达我们的观点。本课我们将通过“