项目一  Excel基础知识—教学案例

任务一  函数的使用

一、何为Microsoft Excel

Microsoft Excel是微软公司的办公软件Microsoft office的组件之一，是由Microsoft为Windows和Apple Macintosh操作系统的电脑而编写和运行的一款试算表软件。直观的界面、出色的计算功能和图表工具，再加上成功的市场营销，使Excel成为最流行的微机数据处理软件。在1993年，作为Microsoft Office的组件发布了5.0版之后，Excel就开始成为所适用操作平台上的电子制表软件的霸主。Excel的工作界面与Word的工作界面有着类似的标题栏、菜单、工具栏，也有自已独特的功能界面，如名称框、工作表标签、公式编辑框、行号、列标等。

从启动的Excel窗口可以看到，该窗口主要由标题栏、工具栏、编辑栏、单元格区域、状态栏和滚动条等部分组成。

二、 Excel的优点

Excel是一个电子表格程序，它也是微软Office套件的一部分。还有其他一些电子表格程序，但Excel是最流行的。

Excel的魅力在于它的通用性。当然，Excel的长处是进行数字计算，但它对于非数字计算的应用也是非常有用的。一下是Excel应用的几个方面：

1. 数值处理

创建预算、分析调查结果和实施你能想到的任意类型的财务数据分析。

2. 创建图表

可创建多种图表。

3. 组织列表

使用行—列的形式高效地存储列表。

4. 访问其他数据

从多种数据源导入数据。

5. 创建图形和图表

使用Excel的自选图形来创建简单（但并不非常简单的）图表。

6. 自动化复杂的任务

通过使用Excel的宏功能只需单击鼠标一次就可以执行一个复杂的任务。

三、单元格、工作薄和工作表

1.输入和编辑工作表

一个工作簿中可以含有任意多个工作表。每一个工作表是由大量的单元格所组成。一个单元格可以保存三种基本类型的数据：数值、文本、公式。除了数据，工作表还能够存储图表、绘图、图片、按钮和其他的对象。这些对象并不包含在单元格中，而是主流在工作表的绘图层中。

（1）数据输入

在单元格中输入文本和数值非常简单。吧单元格指针移到合适的单元格，使它成为当前活动单元格，输入值后按Enter键。当单元格被激活时数值会在单元格中显示，同时也会在编辑栏中显示。当然，用户在输入数值和货币符号时，可以包括小数点和美元符号，还可以包括加号、减号和逗号。如果在数值前加负号或两边加括号，Excel将它看作为负数。

在单元格中输入文本与输入数值一样简单：激活单元格，输入文本，然后按Enter键就可以了。一个单元格最多能够容纳32000个字符——足够容纳书本一章的内容。尽管一个单元格可以容纳大量的字符，但是用户会发现，完全显示出这些字符是不可能的。

默认情况下，输入数据后，按Ｅnter键，将确认输入并切换到下方的单元格．按Ｔab 键，可切换到右方的单元格；按键盘上的单元格，可灵活选择下一个数据输入的位置

（2）数据修改

   ① 直接替换数据。单击选中要修改的单元格，输入新内容，会替换原单元格中的内容。

   ②修改单元格中的部分内容。双击单元格，单元格变录入状态，光标成“Ｉ”形，表示文字插入的位置，然后在要修改的文字上拖动鼠标选中要修改的文字，然后输入新的内容。

（3）填充和自动填充

在Excel中，对于一些规律性比较强的数据，如果逐个手动输入比较浪费时间，而且容易造成数据输入错误，为此， Excel提供了填充和自动填充功能，可帮助用户快速输入此类数据．

①   填充：单击编辑－填充菜单命令。

②自动填充：就是通过简单的双击或拖动操作快速、准确地输入大量有规律的数据。选定一个单元格，仔细观察后，会发现在单元格右下方有一个小黑方块，这就是填充句柄。当鼠标光移动到填充句柄上方时,鼠标会变成“ + ”字形状,此时可能通过按住鼠标左键拖动实行自动填充操作。竖向单击拖动鼠标,释放鼠标左键后,自动完成填充操作。

（4）增加行和列

 ①在要插入的行的下方，单击左侧的行号，选择一行单元格，单击右键弹出快捷菜单，选择插入命令。

②单击要插入位置后面的列标，选择一列单元格，单击右键弹出快捷菜单，选择插入命令。

（5）数据排序

 排序可以让表格中的数据按某种规则排列，数据间的顺序只有两种：升序与降序。对于数值， Excel按数值大小排序；对于文本，Excel将根据字母顺序、首字母的拼音顺序或笔画顺序来排序，空格将始终被排在最后。

 ①简单排序：选中要排序内容，单击工具栏中的升序排序或降序排序按钮，可以完成简单的排序。

②   复杂排序：通过单击数据菜单下的【排序】菜单命令，弹出“排序”对话框。

（6）数据运算

 ①工具栏操作：如：“求和”，应选择要求和的数据，并多选一格用于显示结果，然后单击工具栏上的自动求和按钮。                         

②输入公式法：在单元格或公式栏中输入计算公式。

2.常见函数的运用
    Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式，以常用的求和函数SUM为例，它的语法是“SUM（number1，number2，……）”。其中“SUM”称为函数名称，一个函数只有唯一的一个名称，他决定了函数的功能和用途。函数名称后紧跟左括号接着使用逗号分隔为称为参数的内容，最后用一个右括号表示函数结束。

Excel函数一共有11类，分别是数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。本节将对在财务管理中常见的函数的应用进行说明，后面章节专用的函数将在以后内容中介绍。

    （1）求和函数SUM

函数格式为：SUM（Number1，Number2，Number3，……）。

功能：求参数表中所有参数的和。

如求总价，则可以单击单元格G13，然后再单击求和按钮，按回车键即可。

（2）求平均值函数AVERAGE

函数格式为：AVERAGE（Number1，Number2，Number3，……）。

功能：求解参数表中所有参数的平均数。

   （3）求最大值函数MAX

函数格式为：MAX（Number1，Number2，Number3，……）。

功能：找出一组数据中的最大值。

MAX函数使用同于AVERAGE函数，可参考上面内容。

（4）求最小值函数MIN

函数格式为：MINmber1，Number2，Number3，……）。

功能：找出一组数据中的最小值。

（5）计数函数COUNT

可以计算数组或单元格区域中数字项的个数。

语法：COUNT(value1,value2, ...)

Value1, value2, ... 是包含或引用各种类型数据的参数（1～30个），但只有数字类型的数据才被计数。函数 COUNT 在计数时，将把数字、空值、逻辑值、日期或以文字代表的数计算进去；但是错误值或其他无法转化成数字的文字则被忽略。如果参数是一个数组或引用，那么只统计数组或引用中的数字；数组中或引用的空单元格、逻辑值、文字或错误值都将忽略。如果要统计逻辑值、文字或错误值，请使用函数 COUNTA。

（6）数组计数函数COUNTA

可以计算数组或单元格区域中数据项的个数。

语法：COUNTA(value1,value2, ...)

Value1, value2, ... 所要计数的值，参数个数为 1～30 个。在这种情况下，参数值可以是任何类型

（7）SUMPRODUCT函数

SUMPRODUCT函数的功能是在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。公式为

= SUMPRODUCT(array1,array2,array3,…)

式中，array1,array2,array3,...为1至30个数组。

需注意的是，数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE!。对于非数值型的数组元素将作为0处理。

（8）条件函数IF

IF函数也称条件函数，它根据参数条件的真假，返回不同的结果。在实践中，经常使用函数IF对数值和公式进行条件检测。

公式为“= IF(logical_test,value_if_true,value_if_false)”

式中  logical_test—条件表达式，其结果要么为 TRUE，要么为 FALSE，它可使用任何比较运算符；

value_if_true—logical_test 为 TRUE 时返回的值；

value_if_false—logical_test 为 FALSE 时返回的值。

IF函数在财务管理中具有非常广泛的应用。

任务二数据及图表处理

一、Excel图表的种类

Excel有14种标准图表类型，每一种类型又有2~7个子类型。同时，还有20种自定义图表类型，他们可以是标准类型的变异，也可以是标准类型的组合，每种类型主要是在颜色和外观上有所区别。下面对每一个标准图表类型加以描述。

1.面积图：面积图表现了数据在一段时间内或者类型中相对关系。一个值所占的面积越大那么它在整体关系中所占的比重就越大。

2.条形图：条形图使用水平条的长度表示它所代表的值的大小。

3.气泡图：气泡图对三个系列的数据进行比较。它与XY图标很相似，X轴和Y轴共同表示两个值，但是气泡的大小由第三值确定。

4.柱形图：柱形图是条形图的变体。在Excel中，柱形图是默认图表类型。

5.锥形图：锥形图是条形图或柱形图的变体。唯一不同之处是它使用锥体表示数据。

6.圆柱形：圆柱形是条形图或柱形图的变体。唯一不同之处是它使用圆柱体表示数据。

7.圆环图：圆环图大体上和饼状图相似，只是不局限于单一的数据系列。每一个系列使用的圆环的一个环表示数据，而不是饼状图的片。

8.折线图：在折线图中，对于每一个X的值，都有一个Y值与其对应，像一个数学函数一样。折线图常用于表示一段时期内的变化。

9.饼状图：饼状图的绘制局限于一个单一的数据系列，并且不能显示更复杂一点的数据系列。但是，饼状图通常非常生动，容易理解。

10.棱锥图：棱锥图是条形图或柱形图的变体。唯一不同之处是它使用棱锥表示数据。

11.雷达图：雷达图表示由一个中心点向外辐射的数据。中心是零，各种轴线由中心扩展出来。

12.股价图：股价图常用于绘制股票的价值。.

13.曲面图：曲面图可以用二维空间的连续曲线表示数据的走向。

14.XY散点图：XY散点图通过把数据描述成一系列的XY坐标值来对比一系列数据。散点图的一个应用是表示一个实验中的多个实验值。

二、图表的编辑与修改

通过图表向导建立的图表可能不尽人意，如标题太大或太小、坐标系列太多、图表尺寸太小、漏掉了数据系列、需要添加数据标志等等，这时就需要对图表进行修改和格式化。

1．格式设置

设置坐标、标题、图例等的格式的方法非常简单，可将鼠标移到坐标、标题、图例等上，单击右键，在快捷菜单上选择相应的项目即可。例如要改变X坐标大小，单击右键，出现快捷菜单，选择【坐标轴格式】项，就会弹出【坐标轴格式】对话框，选择需要修改的项目，进行设置即可。

2．改变图表大小

单击图表区域，将它激活，图表边框出现8个操作柄，用鼠标指向某个操作柄，当鼠标指针呈现双箭头时，按住左键不放，拖动操作柄到需要的位置上，然后放开鼠标左键，即可完成。

3．移动或复制图表

移动：单击图表区域，将它激活，图表边框出现8个操作柄，在图表区域按住鼠标左键不放，拖动鼠标将图表移到需要的地方。

复制：单击图表区域，将它激活，图表边框出现8个操作柄，在图表区域按住鼠标左键不放，拖动鼠标将图表移到需要的地方，按Ctrl键，然后放开鼠标。若需要将图表复制到其他工作表或其他文件中，可选中图表，按“Ctrl+C”键，再在需要安置图表的工作表或其他文件的适当位置，按“Ctrl+V”键。

任务三数据分析工具

一、分析工具库的安装与卸载

1.分析工具库的安装

“分析工具库”是一个加载宏文件，文件名为Analys32.xll（加载宏文件是可以自动执行一系列复杂任务的操作命令的组合）。但这一工作在安装Microsoft Office组件时已经加载。

安装步骤如下：

（1）打开Excel工作簿，选择菜单栏中【工具】菜单中的【加载宏】。

（2）在打开“加载宏”对话框，单击【分析工具库】复选框，框前即出现√号，表明已被选定，选择【确定】。

2.分析工具库的卸载

当不需要使用分析工具库时，可以通过设置来卸载分析工具库。

操作步骤：

（1）打开Excel工作簿，选择菜单栏中【工具】菜单中的【加载宏】，如图5-1所示。

（2）在打开“加载宏”对话框，对话框如图5-4所示，单击【分析工具库】复选框，将框前出现√号取消。

（3）选择取消后，单击【确定】按钮，即可自动卸载分析工具库。自此在“工具”菜单中的“数据分析”选项自动隐藏。

二、模拟运算表

1.模拟运算表是对工作表中一个单元格区域内的数据进行模拟运算，测试使用一个或两个变量的公式中变量对运算结果的影响。

2．模拟运算表的类型

（1）基于一个输入变量的表，用这个输入变量测试它对多个公式的影响——单模拟运算表。

（2）多个输入变量的表，用这两个变量测试它们对于单个公式的影响——双模拟运算表。

模拟运算表的操作步骤如下：

（1）在Excel工作表中输入数据。

（2）将光标定位到单元格1中，点击编辑兰中的“＝”，输入公式后点击“确定”。可算出结果。

（3）选中单元格1，将指针放在右下角，当指针变成“＋”时，拖拉到右端即可算出第一行数据的结果。

（4）将数据区域选中，点击“数据”“模拟运算表”命令，在出现的对话框中将光标定位到输入引用列中，点击单元格2，点击“确定”即可得出模拟运算结果。

三、单边量求解

所谓单变量求解，就是求解具有一个变量的方程，Excel通过调整可变单元格中的数值，使之按照给定的公式来满足目标单元格中的目标值。单变量求解就是求解只有一个变量的方程的根，方程可以是线性方程，也可以是非线性方程。

对于模拟运算表中的数值，不允许通过手工改变具体值，若需要改变，则需点击“工具”“单变量求解”命令，出现对话框。在目标单元格框中输入需要改变的单元格的坐标。

操作步骤如下：

1.选中单元格1作为目标单元格；

2.在目标值框中输入要改变为的目标值，在可变单元格中输入单元格2的坐标，然后点击“确定”，即可求出单元格2的值。

请注意：如果不通过单变量求解计算模拟运算表中的值，而是通过手工改动，则会出现对话框，提示“不能改变模拟运算表的某一部分”，此时，只能是结束任务，或是重新启动计算机。

四、规划求解

1.单变量求解是决定单个变量的好方法，但是对于复杂的问题就需要用到功能强大的规划求解。规划求解是一种增加的功能，只有安装以后才能在工具菜单中找到它。

2.特点

（1）所求问题都有单一的目标，如求生产的最低成本，求运输的最佳路线，求产品的最大盈利，求产品周期的最短时间以及求其他目标函数的最佳值等。

（2）总是有明确的不等式约束条件。比如库存不能低于一定的数量，否则造成原料短缺或产品缺货；生产产品不能超过一定额度，否则会造成商品积压等。

（3）问题都有直接或间接影响约束条件的一组输入值。

3.基本构成

（1）决策变量（variable）

一个或一组可变单元格，可变单元格称为决策变量，一组决策变量代表一个规划求解的方案

（2）目标函数

目标函数表示规划求解要达到的最终目标，是规划求解的关键。它是规划求解中可变量的函数         

（3）约束条件

约束条件是实现目标的限制条件。

4.模型建立

（1）输入原始数据及相应的各公式，建立求解工作表。

（2）设置求解参数。

①选择“工具” —“规划求解”菜单，设置以下求解的各项参数：

②设置目标单元格：输入目标函数所在单元格(为总余额单元格)

③设置目标：最大值、最小值或值的数值(最大利润,即最大值)

④设置可变单元格：它的确定决定结果（为生产数量）

⑤设置约束条件：单击【添加】按钮—输入约束条件—按添加—依次输入所有约束条件—确定

（3）保存求解结果。

在规划求解对话框中按“求解”—在规划求解结果对话框中按“保存规划求解结果”。