Excel教案.doc

第1 单元建立Excel工作簿和工作表一、工作簿和工作表l 工作区和单元格l 活动单元格l 名称框和编辑栏l 工作表标签和当前工作表二、.输入数据1、修改输入的内容：l 整体覆盖（单击单元格，使成为活动单元格，直接输入）l 部分修改（双击单元格，在单元格内编辑内容）2、输入日期和时间 ：用 / 或 - （斜杠或减号）l 按Ctrl+; （分号），可以输入系统当前日期l 按Shift+Ctrl+；（分号），可以输入系统当前时间3、命名工作表，保存工作簿l 重命名工作表l 保存并关闭工作簿：保存时，在另存为对话框中，工具选项常规选项保存选项 （生成备份文件、设置打开权限密码、修改权限密码）4、新建工作簿l 新建空白工作簿 Book1 Book2 l 利用模板建立具有一个格式的新工作簿（新建工作簿本机上的模板）三、各类数值数据的输入1、输入分数：整数+空格+分数的形式 2 2/3 、 0 1/5 2、输入特殊符号：l 使用软件盘l 插入符号 选择Wingdings Wingdings2 Webdings 有很多好看的符号3、为工作簿设置密码保护：l 工具选项安全性设置密码保护（打开权限密码、修改权限密码）l 取消密码：重新打开，把密码删除4、输入身份证号和电话区号：文本型数据，输入前先加一个英文的单引号，数字在单元格内默认是左对齐第2单元编辑充实工作表的内容一、有规律的输入数据1、创建工作簿2、选定单元格l 选定连续的单元格区域l 选定一行或连续的多行l 选定一列或连续的多列l 选定不相邻的几个区域3、单元格区域的名称 B3:C6 B2:D4，E64、填充柄：找不到时，工具选项编辑单元格拖放功能5、使用填充柄快速输入等差序列：按住Ctrl、不按Ctrl 2种（数值型、文本型、日期型）6、填充等比序列：输入第一个数，然后选定要填充区域，编辑填充序列等比，选择步长7、自定义填充序列：工具选项自定义序列，输入自定义的序列后，选择添加二、 添加数据1、插入新行、新列：在所选中行的上面插入新行，在所选中列的左边插入新列（行是上插入、列是左插入），一次选中多行、多列，可以一次插入多行或多列2、删除行或列：选中后，编辑删除编辑和清除的区别：3、复制数据l 使用填充柄复制l 使用剪贴板复制4、调整行高与列宽：l 双击行号或列标之间的边线，调整到合适的高度或宽度l 选中多行或多列，格式行（列）最合适行高（列宽）l 手工拖动行号或列标之间的边线，调整三、利用拆分或冻结完成大型表格的输入1、利用冻结窗格完成数据添加l 选中需要冻结的单元格，成为冻结点，该点以上和左边的所有单元格都将被冻结l 窗口冻结单元格，冻结的窗格之间以细实线分割l 撤销窗口冻结：窗口取消冻结窗口2、利用拆分窗口浏览数据l 选择合适的单元格，成为活动单元格，该单元格成为分割点l 窗口拆分l 双击分割点，可以取消拆分拆分和冻结窗格都是为了把工作表窗口分为几个部分，但拆分窗格后的各部分都能滚动，而冻结窗格，是为了防止上部与左侧窗格的滚动3、隐藏行或列：隐藏而不是删除l 选中要隐藏的行或列，格式行（列）隐藏l 取消隐藏：选中已隐藏行或列的相邻区域，格式行（列）取消隐藏4、工作表的管理l 插入新工作表：插入工作表l 移动工作表：选中工作表，移动或复制工作表l 复制工作表：l 删除工作表：选中工作表，成为当前工作表，编辑删除工作表l 改变默认工作表的数目：工具选项常规新工作簿内的工作表数，设置第3单元公式和函数的应用一、公式的填充与复制公式中的运算符和运算次序：l 算术运算：+ - * / % l 比较运算符：= = l 文本连接运算符 &:把2串文字或符号连接在一起产生一个连续的文本值，字符串常量以西文双引号 ” 括起来l 公式中的运算顺序：( )、-（取负）、%（百分比）、（乘方）、 * /、+ - 、 &（文本连接符） 、比较运算 = = 二、常用函数：求和、平均值、最大值、最小值、计数单元格的绝对引用和相对引用日期函数的计算l 取整函数INT：对参数值向下取整为最接近的整数 l 日期函数TODAY()：返回日期格式的系统当前日期l 根据参加工作日期计算工龄、根据出生日期计算年龄 条件函数IF的使用If（条件，表达式1，表达式2）使用统计函数Countif Countif(指定区域，条件) 条件一定用英文 ” ” 引起来三、工作表的安全与保护1、保护工作表l 选中整个工作表：单击全选按钮l 格式单元格保护锁定或隐藏锁定：表示工作表中选定的单元格区域不能被更改、移动、调整或删除；隐藏：表示工作表中选定的单元格区域中的公式不会在编辑栏中显示。隐藏和锁定单元格的操作必须在对工作表实施保护之后才有效l 保护工作表：工具保护保护工作表允许此工作表的所有用户进行选择进行的操作l 在“取消工作表保护时使用的密码”中设置密码2、取消对工作表的保护：工具保护撤销工作表保护3、保护工作表中的部分区域：工具保护允许用户编辑区域选择区域（可以修改的区域）设置密码确定 其余区域不能编辑4、撤销保护单元格区域5、保护工作簿不被更改：工具保护保护工作簿第4单元美化与打印工作表一、1、合并单元格2、设置字体与字号3、设置对齐方式：跨列居中4、设置文字颜色和表格底纹：单元格背景的填充效果5、添加边框：内、外边框，清除边框（选择无边框）6、使用格式刷二、调整内容的显示样式1、设置数字的显示样式：货币样式、百分比样式、增加小数位、减少小数位2、使用条件格式突出显示特定数据：符合一定条件时，自动应用于单元格的格式选中要设置格式的单元格区域格式条件格式进行格式设置添加其它条件：在条件格式对话框中选择“添加”，并为此条件设置一种格式，最多可以设置3个条件 3、设置日期格式4、为单元格添加批注：批注通常是一些说明文字或重要提示l 指向某单元格，右击插入批注，含有批注的单元格右上角有红色的三角形批注标识l 删除批注：右击批注删除批注5、自动套用表格格式格式自动套用格式6、在单元格内加入斜线选中单元格格式单元格格式边框，选择斜线样式l 在单元格内自动换行：希望换行输入。先按Alt，在敲回车，在单元格内换行三、打印工作表1、打印预览2、使工作表打印到一页纸上：文件页面设置调整为一页高、一页宽3、使表格跨页时显示表头：文件页面设置工作表打印标题顶端标题行选择标题行，同时可以选择先行后列或先列后行打印次序，定义页眉、页脚、页边距4、设置打印范围及份数：文件打印设置第5单元制作图表制作图表1、使用图表向导制作图表l 选择图表类型l 选定数据区域（只选择需要的单元格区域，不要选择全部区域）l 设置图表选项：标题、坐标轴、网格线、图例、数据标志、数据表l 确定图表位置：图表工作表（新建工作表）、嵌入式图表2、移动图表与更改图表大小3、修饰与调整图表对象l 调整绘图区的格式：设置网格线、绘图区背景效果l 设置分类轴与数值轴的格式：字体、数据刻度l 设置图例的格式：位置、背景填充效果l 设置图表标题的格式：l 设置坐标轴的格式l 设置数据系列的格式4、编辑图表数据l 向图表中添加数据:图表添加数据选择添加的数据区域l 删除图表中的数据：选中需要删除的数据系列删除l 显示次坐标轴二、使用图表分析与预测数据1、使用图表转置功能比较数据使用图表转置功能分析“山东路分店销售图表”中的数据，并更改这个图表的类型，从不同角度观察、分析比较各种商品在不同季度的数量，以指导销售工作和生产生成图表后，删除全年列数据，图表按商品分类，从图中可以看出热饮的总销售量较少，要增加热饮的销售力度。选定图表，图表工具栏中，单击按行排列，进行图表转置更改图表类型，将图表改为折线图，数据点折线图第6单元简单数据管理一、排序1、单字段排序：光标放在要排序的列中，不必选中这一列，升序、降序2、多个关键字排序：光标放在数据区域中，数据排序主要关键字、次要关键字、第三关键字 升序、降序3、设置排序选项：在排序中，选项按笔划、字母、自定义排序二、数据筛选1、自动筛选：数据筛选自动筛选2、取消自动筛选：数据筛选取消自动筛选3、多个条件的自动筛选 通配符的使用 * ? 4、高级筛选：l 根据筛选要求写出条件区域：必须先写出用到字段的字段名（最好是复制表中的字段名），所有的字段名写在一行且连续，中间不允许有空格；字段值与的关系写在一行，或的关系写在不同的行l 确保条件区域与表格源数据至少有一个空行或一个空列l 光标放在有数据的单元格中，数据筛选高级筛选，先选择数据区域，数据区域（列表区域）包括所有有数据的区域条件区域复制到的区域三、分类汇总将同类数据放在一起，再进行数量求和、计数、求平均值之类的汇总运算l 分类字段：选定要进行分类汇总的列，对数据表按这个列进行排序l 汇总方式：求和、最大值、最小值、计数、求平均值等l 汇总项：可以选择多个字段进行汇总多个字段一次只能进行同一方式的汇总分级显示清除分类汇总第7单元高级数据管理一、 销售表的合并计算(一)、功能介绍1、合并计算的两类操作：（1）汇总计算；（2）内容合并。2、合并计算实现的两种途径：（1）通过位置合并计算；（2）通过类别合并计算。3、三种具体情况：（1）当数据列表的列标题和行标题相同时，无论这种相同是发生在同一工作表中，还是在不同的工作表中的数据列表，合并计算所执行的操作将是按相同的行或列的标题项进行计算，这种计算可能包括求和、计数或是求平均值等等。（2）当数据列表有着不同行标题或列标题时，合并计算则执行合并的操作，将同一工作表或不同工作表中的不同的行或列的数据进行内容合并，形成包括数据源表中所有不同行标题或不同列标题的新数据列表。（3）如果数据列表没有行标题和列标题时，合并计算将按数据所在单元格位置进行计算。4、功能具体操作方法：以上分类合并的启用与否以及分类依据的选取可以通过“合并计算”对话框中的“首行”和“最左列”两个复选项的选择来实现，实现的方式如下：（1）当仅需要根据列标题进行分类合并计算时，则选取“首行”；（2）当仅需要根据行标题进行分类合并计算时，则选取“最左列”；（3）如果需要同时根据列标题和行标题进行分类合并计算时，则同时选取“首行”和“最左列”。（4）如果数据源列表中没有列标题或行标题（仅有数据记录），而用户又选择了“首行”和“最左列”，Excel则将数据源列表的第一行和第一列分别默认作为列标题和行标题。（5）如果用户对“首行”或“最左列”两个选项都不选取，则Excel将按数据源列表中数据的单元格位置进行计算，不会自动分类。二、购房贷款方案分析陈先生计划向银行贷款10万元购买住房一套，以分期付款方式按月偿还，计划8-15年还清贷款。10年以内年利率5.58%，10年以上年利率为6.03%。l 计算不同贷款期限以及不同的贷款数额下，每月还款额；l 在确定了每月还款额的情况下计算出还款期限；l 以及在力所能及的还款数量与还款期限下，计算出能贷出的钱数。1、分期付款函数PMT每月还款金额=PMT(年利率/12，贷款期限*12，贷款总额*10000)贷款期限以“年”为单位 金额为负时表示支出2、 计算月还款额贷款总额（万元）年利率贷款期限（年）月还款额（元）105.58%1￥-8,587.356.03%10￥-1,111.71函数中所用的数据单位必须一致。计算月还款额，函数中的利率、贷款期限一律换成以月为单位；贷款总额转换为元。如果计算年还款额，函数中的利率、贷款期限是以年为单位的数值。3、 使用单变量模拟运算表计算利率不变时不同还款期限的月偿还额希望显示某个参数的变化对计算结果的影响，使用单变量模拟运算表。模拟运算表作为工作表中的一个区域，可以将公式中某个变量取不同值时所对应的结果一次计算出来，并将结果显示在工作表中l 选定计算区域，数据模拟运算表l 输入单元格是指数据可被替换的参数所在的单元格。计算利率不变时不同还款期限的月偿还额，贷款期限就是可变的，输入引用列的单元格选择C5（期限），（输入引用行的单元格不必选）确定，计算出结果。使用单变量模拟运算应遵循以下规则：l 替换输入单元格的数据必须排列在一列（列方向）或一行（行方向）中。l 公式的位置是有限制的，如果替换值排列成一列，公式应在第一个替换值所在单元格区域右上方单元格内；如果替换成一行，公式应在第一个替换值所在单元格区域的左下方单元格内。l 公式中必须引用输入单元格。4、 使用双变量模拟运算表计算贷款总额与贷款期限改变时的月还款额单变量模拟运算表对数据进行分析处理时，只能查看单个变量对计算结果的影响，查看公式中2个变量同时改变时对计算结果的影响，必须用双变量模拟运算表。例、计算在固定利率6.03%时 ，贷款期限与贷款总额同时变化时的月还款额在G14:K14中输入8、12、15、18、20 代表不同的贷款总额F15：F25中输入1020代表不同的贷款期限利用PMT公式计算出贷款总额为10万元，贷款期限为10年、利率为6.03%时月还款额1111.71元双变量模拟运算表中公式的位置是有限制的，要用到的公式必须放在替换行与替换列交叉的单元格中，本例是F14选定F14:K25，选定的单元格必须包含公式和替换值所在的行列数据模拟运算表输入引用行的单元格选择$A$5(10万元)，输入引用列的单元格选择$C$15(10年)确定， 得到结果5、 单变量求解贷款期限目标值（公式的结果）是一个已知值，而公式中的某个参数是需要计算的值，参数的值取多少时，公式的计算结果等于我们的目标值呢？这就是单变量求解问题。已知不同贷款总额下，贷款期限从10年到20年时每月的还款额，如果固定贷款总额是10万，而我们每个月决定偿还1200元，贷款期限需要多少年呢？选定用公式计算出来的月还款额，工具单变量求解 目标单元格（就是刚才选定的单元格，）输入目标值（每月还款1200，输入-1200），可变单元格 选择目标值左边的还款期限单元格确定作业1、张先生的儿子考上大学，每年学费8000元，张先生决定使用银行贷款缴付4年学费，教育贷款利率如下表贷款期限年利率0=期限=14.5%1=期限=44. %4=期限=105%张先生有以下几种选择：l 4年内每年贷8000元，一年还清，共贷款4次。l 一次性贷款32000元，分4年还清。l 一次性贷款32000元，分510年还清请使用PMT函数和模拟运算表，计算各