Excel企管应用.doc

（一）组织教学（2分钟）（二）讲授新课（70分钟）第1章 Excel在市场营销与销售管理应用基础知识1.1数据输入与编辑操作1.1.1 常见类型数据的输入要点1.数字的输入数字是数据表中最常用的数据类型。对于数字的输入，需要注意以下几点内容：（1）当输入的数字超过12位时，将会自动地按照科学计数法显示数字。（2）如果要输入负数（比如“-10”），既可输入“-10”，也可输入“（10）”。（3）对身份证号、电话区号、邮政编码等不参与运算的数字，为保持其原貌，要设置为文本型数据输入。2.文本的输入输入文本型数据（包括汉字、字母及键盘能输入的符号）时，需要注意以下内容：（1）一个单元格最多可容纳的字符数是32000个，如果单元格宽度不够的话，默认情况下，它将覆盖掉右侧的单元格，但实际上它仍为本单元格的内容。（2）默认情况下输入的文本显示在一行上，如果需要显示成多行文本，有两种方法：自动换行法强制换行法（3）纯数字组成的字符串的输入（如身份证号、电话号码等）3.日期和时间的输入4.分数的输入5.特殊符号的输入6.数字公式的输入：使用“公式编辑器”1.1.2数据输入的快捷方法1.有一定规律数据的序列输入方法2.多张工作表中相同数据的录入3.带零和小数位数据的快捷输入1.1.3对输入数据进行有效性审核1.数据有效性：利用这个工具，操作者可以针对性地输入数据，在输入错误的数据后，系统会出现错误信息框，提醒用户进行修改。2.命令使用步骤：（1）选取须设置数据有效性的单元格区域（2）单击 数据数据工具数据有效性，打开 数据有效性 对话框，根据题目要求作相应设置1.1.4下拉式选择列表输入的设计1.下拉式列表：指在单元格中建立下拉列表，在需要输入数据时，可以方便地从列表中，这样可以提高数据输入的速度和准确度。该功能利用 数据有效性 的 序列 选项卡来实现2.功能实现步骤：（以例18为例，见书12页）（1）建立数据表的整体框架，另在其它位置列出各种将来准备设置为下拉列表中各个项目的数据，这样做目的是为了将来数据维护的方便。（2）选中第一个须设置下拉列表的单元格区域（3）打开 数据有效性 对话框，设置允许序列（4）在 来源 文本框中选取或输入相应备用数据的单元格区域，确定即可。（5）其它列下拉式输入实现方法：重复（2）（4）1.1.5选择性粘贴的操作与应用1.选择性粘贴：指把剪贴板中从其他位置复制过来的内容按照操作者指定的粘贴要求和规则粘贴到目的工作表中。2.命令使用步骤：（1）对准备进行选择性粘贴 的单元格区域或其他复制的内容进行复制（2）定位到目标单元格，单击 开始剪贴板粘贴选择性粘贴，弹出 选择性粘贴 对话框（3）选择所需要的粘贴方式，确定即可3.讲几个选择性粘贴的典型应用（1）复制公式的计算结果（2）利用选择性粘贴进行数据运算（例19，见书14页）（3）利用 转置 功能来实现表格行列的互换（例110，见书15页）（三）小结（15分钟）（四）作业（2分钟）（一）组织教学（2分钟）（二）复习旧课（10分钟）（三）讲授新课（70分钟）1.2公式、函数与名称应用1.2.1公式输入规则与编辑操作1.公式的输入2.公式的运算符及其优先顺序（表1-1见书16页）3.公式的修改4.公式的移动和复制5.让工作表中的所有公式快速现身1.2.2公式中单元格的引用方式1.按照引用的绝对与相对关系划分（1）相对引用：直接用列标和行号表示单元格，是默认的单元格引用方式。如公式中相对引用A1单元格，即为A1（2）绝对引用：指在公式中引用单元格的地址与单元格的位置无关，单元格的地址不随单元格位置的变化而变化。绝对引用单元格的行和列前都有 $ 符号。如公式中绝对引用A1单元格，即为$A$1（3）混合引用：指行号固定而列标可以变化或列标固定而行号可以变化。其表示形式为将固定的部分前面加上 $ 符号。如$A1或A$1等形式（4）三种引用的切换：F4功能键可实现绝对引用、相对引用和混合引用之间的快速切换2.跨表格单元格引用的情形（1）引用同一工作簿的其他工作表中的单元格或区域，格式：工作表标签！单元格地址（2）引用同一工作簿多张工作表的相同单元格或区域，格式：第一个工作表名：最后一个工作表名！单元格地址（3）不同工作簿之间的数据引用，格式：工作簿名工作表标签名！单元格地址3.单元格引用的应用举例例111，见书18页例112，见书19页例113，见书20页1.2.3函数应用的基础知识1.函数的结构函数结构一般由三部分构成，分别是函数名、括号和参数表，形式如下：函数名（参数1，参数2，参数3）注意：（1）函数使用中，即使函数没有参数，函数名后面也要紧跟一对圆括号（2）函数嵌套使用时，不要忘记书写每个函数的括号，把握一个原则：无论函数嵌套多少层，每个函数名后面都应该紧跟一对圆括号2.函数中的参数类型参数类型主要有以下几种情况：（1） 常量（2） 逻辑值（3） 数组（4） 单元格引用（5） 名称 3.函数的调用 函数的调用就是在公式或表达式中应用函数，它包括如下三种形式：（1） 在公式中直接调用函数（2） 在表达式中调用函数（3） 函数的嵌套调用4.函数的输入（1）借助函数向导输入（2）手工直接输入例115，见书26页5.Excel函数的类型划分：见表12，书26页（四）小结（5分钟）（五）作业（2分钟）（一）组织教学（2分钟）（二）复习旧课（10分钟）（三）讲授新课（70分钟）1.2.4名称的创建与应用1.名称的含义与作用含义：在Excel中，名称是描述工作簿中某些单元格、单元格区域、公式、常量等的一种标识符作用：通过名称，可以方便地了解公式中单元格引用、常量、公式或表格的实际意义，提高公式的清晰度和可理解性2.名称的命名规则（1）（5）条见书29页3.名称的创建方法方法一：使用 新建名称 对话框按照需要定义任意名称方法二：根据所选内容创建将行、列标题定义为名称方法三：使用 名称框 定义名称的快捷方式4.名称的典型应用实例例117，见书31页5.为三维数据定义和应用名称例118，见书32页1.2.5数组公式及其应用1.数组公式的输入数组公式与一般公式不同在于它被括在大括号中，操作步骤如下：（1） 选中单元格或单元格区域（2） 输入公式（3） 按Ctrl+Shift+Enter即转换为数组公式例119，见书33页2.数组公式的编辑修改3.数组公式应用的不同情形举例例120，见书34页（四）小结（5分钟）（五）作业（2分钟）（一）组织教学（2分钟）（二）复习旧课（10分钟）（三）讲授新课（70分钟）1.3数据组织与内容整理1.3.1数据库表格的特点1.3.2数据排序的操作1.Excel中数据排序的基本规则（1）数字：数字按从最小的负数到最大的正数进行排序（2）文本：字母数字文本按从左到右的顺序逐字符进行排序。文本以及包含存储为文本的数字的文本按以下次序排序:0 1 2 3 4 5 6 7 8 9 （空格） ! # $ % & ( ) * , . / : ; ? _ | + A B C D E F G H I J K L M N O P Q R S T U V W X Y Z（3）逻辑：在逻辑值中，FALSE 排在 TRUE 之前。（4）错误：所有错误值（如 #NUM! 和 #REF!）的优先级相同。（5）空白单元格：无论是按升序还是按降序排序，空白单元格总是放在最后。注释空白单元格是空单元格，它不同于包含一个或多个空格字符的单元格。2.使用工具按钮对数据单关键字排序先选排序数据，点击菜单栏的“数据”,选择排序子菜单，在打开的排序对话框中，选择排序方式。3.利用菜单法对汉字字段按笔划进行排序4.实现具有多个关键字的复杂排序1.3.3数据筛选的操作方法一：自动筛选步骤：（1） 打开EXCEL表格，选择数据第一行（2） 选择菜单栏上的数据筛选，各单元格带一个下三解形图标，这就是自动筛选的符号；如果要取消，也是按一下筛选即可（3） 点一下三解形图标，可以进行数据查询，之后数据会根据您选择条件显示，其它都会隐藏起来。如果想要再显示全部记录，只要再点一下三角形图标，然后在全选处打，确定即可。方法二：高级筛选（当多个字段间是 或运算 时，须使用高级筛选）（1） 先找单元格输入条件（2） 如要筛选出语文成绩高于80分的，则在上下两个单元格中分别输入语文 80（3） 单击数据，在“排序和筛选”中 单击“高级”于是弹出“高级筛选”的对话框。（4） 这里 “列表区域”，我们将原来整个成绩单选中；“条件区域”，我们把 我们刚才输入的条件区域选中；我们可以选中“将筛选结果复制到其他位置”，在空白区域选中一个单元格。然后单击确定即可。例127，见书45页（四）小结（5分钟）（五）作业（2分钟）（一）组织教学（2分钟）（二）复习旧课（10分钟）（三）讲授新课（70分钟）1.4 数据显示格式的设置1.4.1 内置数字格式的设置（1）要将一个格式应用到一个单元格或范围，选择单元格或范围。（2）选择格式菜单上的单元格命令，显示单元格格式对话框。见下图（3）在分类列表框中，单击所需要的格式化数字类型，当选择一个分类时，就会显示对应该分类的所有选择。选择一个子分类，单击确定即可。1.4.2 自定义数字格式的设置1.通过实例自定义数字格式的设置2.自定义数字格式的创建方法3.自定义数字格式的代码设置默认情况下，自定义数据的完整格式组成结构为：正数格式；负数格式；零值格式；文本格式4.自定义数字格式的应用实例例129 见书52页1.4.3 条件格式化的设置1.突出显示满足制定规则的单元格2.根据项目选取原则突出显示特定单元格3.用数据条、色阶和图标集来直观地显示数据l 数据条：（1）选中要应用条件格式的列，在开始选项卡中点击条件格式，选择数据条，然后选择一个渐变填充样式（2）结果如图所示，负数的颜色条和其他不一样l 图标集：（1）首先要选中要运用图标集格式的列（2）点击开始选项卡，选择条件格式，然后再选择图标集，在选择你需要的箭头，效果如图所示 例1-30见书53页（四）小结（5分钟）（五）作业（2分钟）（一）组织教学（2分钟）（二）复习旧课（10分钟）（三）讲授新课（70分钟）1.5数据图表的制作1.5.1数据图表的类型介绍1.数据图表类型简介及选择原则条形图：由一系列水平条组成。使得对于时间轴上的某一点，两个或多个项目的相对尺寸具有可比性。面积图：显示一段时间内变动的幅值。面积图使你看见单独各部分的变动，同时也看到总体的变化。柱形图：由一系列垂直条组成，通常用来比较一段时间中两个或多个项目的相对尺寸。条形图是应用较广的图表类型。折线图：被用来显示一段时间内的趋势。股价图：是具有三个数据序列的折线图，被用来显示一段给定时间内一种股标的最高价、最低价和收盘价。通过在最高、最低数据点之间画线形成垂直线条，而轴上的小刻度代表收盘价。股价图多用于金融、商贸等行业，用来描述商品价格、货币兑换率和温度、压力测量等，当然对股价进行描述是最拿手的了。饼形图：在用于对比几个数据在其形成的总和中所占百分比值时最有用。整个饼代表总和，每一个数用一个楔形或薄片代表。XY散点图：展示成对的数和它们所代表的趋势之间的关系。对于每一数对，一个数被绘制在X轴上，而另一个被绘制在Y轴上。过两点作轴垂线，相交处在图表上有一个标记。当大量的这种数对被绘制后，出现一个图形。散点图的重要作用是可以用来绘制函数曲线，从简单的三角函数、指数函数、对数函数到更复杂的混合型函数，都可以利用它快速准确地绘制出曲线，所以在教学、科学计算中会经常用到。雷达图：显示数据如何按中心点或其他数据变动。每个类别的坐标值从中心点辐射。来源于同一序列的数据同线条相连。其它图表:比如圆柱图、圆锥图、棱锥图，只是条形图和柱形图变化而来的，没有突出的特点，而且用得相对较少。在EXCE中允许创建这样的组合图表，但如果想创建一种EXCEL不允许的组合图表类型，比如不可能将一个二维图同一个三维图表混在一起，这种组合是不可能的，系统就会显示错误信息。怎么建立混合图表呢?单击“图表向导”按钮，选择“自定义类型”选项卡，这里还躲着二十种图表类型，也有各种组合形式。如果你对这些内部定义的形式还不满意，就单击“自定义”选项钮，按照向导自己定义全新的图表类型。1.5.2数据图表的创建方法1.图表存在形式：嵌入式图表和独立工作表图表2.创建图表：步骤如下：（1）选择插入图表，选择图表类型，见下图1 （2） 点击下一步，选择数据区域。见上图2（3）在分类轴、数值轴中输入X、Y轴名称，其它选项框还可以更改坐标轴、网格线、图例等等。见上图3（4）点击完成，生成图表。如上图41.5.3 数据图表的编辑与设置1.图表的各个组成部分名称介绍：见下图图表标题绘图区图表区分类轴分类轴标题图例系列数值轴数值轴标题1.5.4 复杂数据图表的制作1.组合图表的制作2.双轴图表的制作例139见书72页（四）小结（5分钟）（五）作业（2分钟）（一）组织教学（2分钟）（二）复习旧课（10分钟）（三）讲授新课（70分钟）第二章 Excel在销售表单制作中的应用2.1销售统计表的制作销售统计表是企业销售管理中一种常用的表格，它将一定时间内计划的销售额与实际的销售额进行对比，分析销售计划的完成比例。 例21 制作某公司上半年销售额完成情况统计表1.表格的框架建立与文本输入2.表格的格式编辑与效果优化3.输入计算公式并进行条件控制要求：底纹部分公式输入且在没有输入基础数据时，底纹部分没有任何显示此处用到的三个公式：在N4中输入 =if(b4=”,”,b4+d4+f4+h4+j4+l4)在B19中输入 =if(b4=”,”,sum(b4:b18)在B20中输入 =if(b19=”,”,c19/b19)（四）小结（5分钟）（五）作业（2分钟）（一）组织教学（2分钟）（二）复习旧课（10分钟）（三）讲授新课（70分钟）2.2 商品订货单的制作商品订货单是企业销售业务流程中一种常用的表格，它是买卖双方签订的代表商品销售意向达成的一种凭证，其中记录了客户方的相关联系信息，以及客户具体购买商品的名称、规格、型号、数量、单价、金额、合计金额等交易细节数据，有的还有发货时间、发货地点、付款时间及付款方式等内容。例2-2 制作某公司的商品订货单1.按下图创建表格2.设置格式3.输入计算公式并设置数字格式，效果图如下图须公式计算的有：（1） 计算订单中每一种商品的金额：I14单元格中输入 =G14*H14，填充；（2） 计算货款小计：I22单元格中输入 =sum(I14：I21)（3） 计算应交税金：I23中输入 =I22*5%（4） 计算金额总计：I24中输入 =sum(I22:I23)（四）小结（5分钟）（五）作业（2分钟）（一）组织教学（2分钟）（二）复习旧课（10分钟）（三）讲授新课（70分钟）2.3 销售费用管理相关单据的制作要做好销售管理工作，一方面要想方设法产品的销售量，增大销售额；另一方面，还必须学会“开源节流”，从多个方面节约销售费用。为此，必须要规范各种销售费用的管理，例如对业务员的差旅费、通讯费、招待费、促销费等要严格地预算和管理。2.3.1 制作手工填写的打印版的差旅费报销单差旅费报销单是一种销售人员经常接触和使用的销售费用管理单据。例23 制作某公司手工填写的差旅费报销单1. 按如下图所示输入文本内容2. 设置格式3. 打印输出2.3.2 创建用于在线填写的电子版差旅费报销单例24制作某公司的电子版差旅费报销单1. 按上图所示输入文本内容2. 指定单元格输入公式（底纹部分由公式计算）3. 效果图如下图2.3.3 销售费用报销时所用单据粘贴单的制作例25 按下图制作报销单据粘贴单（四）小结（5分钟）（五）作业（2分钟）（一）组织教学（2分钟）（二）复习旧课（10分钟）（三）讲授新课（70分钟）2.4 产品目录清单的制作例26 按下图所示制作某通讯公司的一个产品清单1. 表格的创建与格式的优化设置2. 图形对象的插入与编辑3. 打印预览并打印（四）小结（5分钟）（五）作业（2分钟）（一）组织教学（2分钟）（二）复习旧课（10分钟）（三）讲授新课（70分钟）第三章 Excel在销售数据汇总中的应用3.1按指定条件进行销售数据汇总3.1.1 SUMIF函数实现单个条件的数据汇总例31 如下图所示，A1:E21区域为某家电销售2009年2月份的产品销售记录流水帐。右侧是用sumif()函数计算出的销售总额和各业务员的总销售额（四）小结（5分钟）（五）作业（2分钟）（一）组织教学（2分钟）（二）复习旧课（10分钟）（三）讲授新课（70分钟）3.2特定情形下的销售数据汇总3.2.1 销售数据的按行、列累加汇总例36 制作某种产品 2009年5月的销售金额与销售数量要求：在D列和E列汇总也每天对应的累计销售金额和累计销售量。1. 输入A B C三列文本内容2. 设置数据表格格式3. 用sum()函数计算D E两列，效果图如下3.2.3不连续区域的销售数据汇总例38 制作某公司2008年下半年销售计划完成情况汇总表1. 按下图输入文本内容2. 编辑格式3. 用公式函数计算指定单元格区域，效果图如下（四）小结（5分钟）（五）作业（2分钟）（一）组织教学（2分钟）（二）复习旧课（10分钟）（三）讲授新课（70分钟）3.3 销售数据的分类汇总在对数据库表格进行汇总统计时，经常需要按照某些指定列的数据聚会要求，对数据库表格按指定字段分类汇总，这是通过分类汇总命令来实现的。例310 输入下表步骤如下1.选中需要分类汇总的列：选择菜单的“数据”，单击升序排序。2. 分类汇总：选择菜单的“数据”，单击“分类汇总”。3. 在分类汇总对话框选择相应的项：分类字段选择要分类汇总的列名4. 分类汇总图总览：按照如上步骤得出分类汇总后的界面图，如下图所示。单击左侧的1,2,3，可分别显示汇总的项目。（四）小结（5分钟）（五）作业（2分钟）（一）组织教学（2分钟）（二）复习旧课（10分钟）（三）讲授新课（70分钟）3.4多工作表数据的合并汇总在Excel工作表中，如果需要汇总报告多个单独单元格的结果，可以将这些单元格中的数据合并到一个主工作表中。这些工作表可以与主工作表在同一个工作簿中，也可以分别位于不同的工作簿。数据的合并计算就是数据的组合过程。3.4.1利用三维引用公式实现多表汇总进行多表数据汇总，最好理解的方法就是通过公式的“三维引用“来实现，即使用SUM等函数，将来自不同工作表或不同工作簿的数据进行跨表格的引用。例314 按下面三图分别在1号、2号、3号工作表中输入内容，将数据汇总到本月汇总工作表中。本题用到的公式函数为 =SUM(1号:空白表!B2) 汇总效果图如下：（四）小结（5分钟）（五）作业（2分钟）（一）组织教学（2分钟）（二）复习旧课（10分钟）（三）讲授新课（70分钟）第四章 Excel在销售数据透视分析中的应用4.1 通过实例了解数据透视分析的作用例41 下图为某电脑公司散件销售情况记录表通过对上表中原始数据按业务员和品名汇总，作出透视表，效果图如下：4.2 数据透视表的创建4.2.1数据透视表的创建过程介绍以上节例题为例，步骤如下：1.”选项卡中“数据透视表”或“数据透视图”。2. 选择后弹出“创建数据透视表及数据透视图”窗口，见下图3.表/区域”内将第一个表内需要分析的数据选中，点击确定4. 产生一个空白的数据透视表及数据透视图，见下图5. 在右侧“数据透视表字段列表”中的需要分析的在其复选框中打钩。 打钩后的数据被全部插入在“轴字段（分类）”中，可根据实际分许需求拖动至“报表筛选”、“列标签”、“行标签”“数值”。“报表筛选”的功能和“数据”选项卡中“数据”的“筛选”类似。6. 对于图表中的数据标签可以通过选中内容，右键选择“添加数据标签”来在图表上显示数据7. “列标签”、“行标签”顾名思义为透视表列和行的选项内容。以此改变需要分析的主要内容，也可以将报表的字段内容直接拖动到表格内。8. “数值”可以