计算机高级应用导言

1、计算机高级应用信息学院 唐小毅基本信息教师：唐小毅邮箱：xiaoyi-tang考核方式：平时作业上课方式：讲解+练习作业上交方式：机房提交上机地点：6号楼108主要内容Excel应用Excel2010介绍数据有效性函数图表制作主要内容数据库及其查询与分类汇总分析在经济管理中常常会遇到大量的数据，学会对这些数据进行组织、查询与汇总分析，也就是学会了计算机在经济管理中的一项基本应用。以商贸公司的数据为例，如果只是把公司发生的业务订单简单地记录下来，而没有对公司记录下来的历史业务数据进行很好的挖掘，其实是忽略了企业业务信息这座数据金矿。主要内容数据查询使用Query查询数据库中数据的方法分类汇总分析

2、主要介绍三种方法数据列表功能方法数据透视表方法,涉及数据透视表和数据透视图的灵活性、利用数据透视表生成时间序列、频率分布的方法，以及多重区域数据的合并汇总D函数加模拟运算表方法，涉及D函数加模拟运算表的汇总方法、D函数与控件配合控制分类汇总结果的方法等主要内容投资决策模型货币的时间价值、贴现率、净现值和内部报酬率、折旧等基本概念常用的财务函数，包括PV()函数、FV()函数、PMT()函数、PPMT()函数、IPMT()函数、ISPMT()函数、NPER()函数、NPV()函数、RATE()函数、IRR()函数SLN()函数和DB()函数基于净现值的投资决策概念及多个项目投资决策模型的建立方法

3、企业经营决策的概念及模型的建立方法金融市场投资决策的概念及模型的建立方法主要内容最优化决策目标函数和约束条件与决策变量之间都是线性关系的规划问题，产品混合线性规划问题的求解目标函数或者约束条件与决策变量之间不是线性关系的规划问题，产品混合非线性规划问题的求解运输、选址、资金管理、生产管理等常见规划问题的求解多目标规划问题的概念和求解规划求解报告的生成与分析主要内容经济管理决策模拟分析模拟模型的基本知识、基本概念和建模步骤，以及生成一些常见分布的伪随机数的方法对依据若干概率输入变量而定的结果变量的分布进行估计的抽样试验方法蒙特卡洛模拟模型的建模方法随时间推移而出现的事件序列的模型系统模拟模型的建

4、模方法系统模拟模型的一种建模方法活动扫描法Excel应用基本操作工作表操作工作表命名工作表标签命名和颜色移动和复制工作表同一工作簿不同工作簿隐藏工作表保护工作表单元格操作区域选中单元格命名固定活动单元格按住Ctrl键，单击单元格选中超链接单元格按住鼠标左键，直至鼠标变成空心十字用光标键选中单元格操作隐藏或显示单元格保护单元格隐藏单元格中的公式隐藏单元格数值利用“单元格格式”的“数字”选项卡，通过自定义选项，将“类型”框中已有的代码删除，输入“；”（三个西文分号） 窗口操作拆分冻结重排数据操作输入数据数值普通数值输入数字符号串日期型数据快速填充行或列的重复填充周围数据填充简单序列填充自定义序列填

5、充其他序列填充记忆填充选择填充重复填充移动填充数据有效性数值范围设置文本长度设置输入列表圈释无效数据格式单元格格式数据格式边框和底纹自动套用格式条件格式窗体控件分组框选项按钮复选框列表框组合框滚动条微调按钮图表制作饼图突出型饼图复合饼图双子饼图母子饼图动态饼图层叠图金字塔图帕累托图公式引用相对引用绝对引用混合引用数组公式常见错误信息# #div/0！ #N/A #NAME？ #NULL！ #NUM！ #REF！ #VALUE！ 函数日期函数财务函数查找和引用函数文本函数逻辑函数地址函数数据库函数统计函数数学函数信息函数工资计算函数计算年龄函数：NOW() ：返回系统日期YEAR(Serial_

6、number)：返回指定日期的年份MIN(Number1,Number2,)：返回一组参数中最小的值实发工资四舍五入函数Round(Number,Num_digits)计算所得税工资条打印ROW(Reference)：返回指定单元格所在的行号COLUMN(Reference)：返回指定单元格所在的列号INDEX(Array,Row_num,Column_num)：第一个参数指定区域，后两个参数是行和列的序号MOD(Number,Divisor)：求余函数=IF(MOD(ROW(),3)=0,”,IF(MOD(ROW(),3)=1,工资计算!A$1,INDEX(工资计算!$A:$S,(ROW()

7、+4)/3,COLUMN()从数据库中获取数据利用Microsoft Query示例数据库关系图一、单表查询单表查询是指仅涉及一个表的查询操作，这种查询又分为如下几种查询：无条件的查询：从数据库的一个表中查询所有记录中的某几个字段的值涉及一个条件的查询：从数据库的一个表中按一个查询条件挑出部分记录，显示这些记录中全部字段或部分字段的值涉及多个条件的查询：从数据库的一个表中按多个查询条件挑出部分记录，显示这些记录中全部字段或部分字段的值。无条件的查询 【例1】Northwind公司新上任的销售总监想了解该公司所有客户的客户ID、公司名称、地区、城市和电话等信息，试为其设计一个查询。涉及一个条件的

8、查询【例2】Northwind公司负责华东地区销售工作的销售主管希望了解一下该地区客户的客户ID、公司名称、地区、城市和电话等信息，试为其设计一个查询。涉及多个条件的查询【例3】Northwind公司销售总监希望了解位于“华东”或“华南”地区的客户的ID、公司名称、地区、城市和电话等信息，试为其设计一个查询。【例4】查询“Northwind”示例数据库中客户的ID、公司名称、地区、城市和电话等信息。其中的一些客户位于华东或华南地区，另外一些客户所在的城市是天津。涉及多个条件的查询【例5】Northwind公司负责华北地区销售工作的销售主管希望获得华北地区、联系人职务是销售代表的那些客户的ID、

9、公司名称、城市和电话等信息，试为其设计一个查询。【例6】 “Northwind”数据库中存放了三年的订单数据，现有关人员想查询该公司的客户在1996年下半年订购的所有订单的订购日期、订单ID、客户ID和雇员ID等信息。二、多表查询简单的多表查询内连接外连接简单的多表查询对于多表查询须了解如下两个问题：查询中使用到的各个字段分别来自于哪些表分析查询所涉及的表与表之间存在着何种联系，这些联系是通过哪些字段建立起来的简单的多表查询 【例7】Northwind公司的采购人员想了解一下各产品库存量的情况，并根据需要从相应的供应商处订货。试为其设计一个查询，显示库存量小于10的产品的ID、名称、单价、库存

10、量和供应商的公司名称等信息。内连接对多个表的数据，按照一定的条件同时进行查询，组成了一个综合性的、来自多个表的结果集，这样的查询称为连接查询。连接查询中所规定的、用于表示连接内容的条件称为连接条件。连接查询可以分为内连接和外连接，其中内连接是将多个表中符合条件的记录挑选出来组成一个结果集。内连接 【例8】Northwind数据库中存放了三年的订单数据，现有关人员需要查询该公司的客户在1996年下半年订购的所有订单的订购日期、订单ID、相应订单的客户公司名称、负责订单的雇员的姓氏和名字等信息。并将查询结果按雇员的“姓氏”和“名字”字段的升序排列，“姓氏”和“名字”值相同的记录按“订单 ID”的降

11、序排列。内连接在内连接查询中，正确建立表之间的联系是非常重要的，这种表之间的联系主要是通过如下的三种方式建立的：若查询中涉及的表有公共的字段名，则Microsoft Query应用程序会自动根据两个表之间的公共字段名建立联系若查询中涉及的表之间没有直接的联系，则可引入中间表，再由Microsoft Query应用程序自动根据表之间的公共字段名建立联系若查询中涉及的表之间有联系但却没有公共的字段名，则可以通过手工方式来添加联系内连接【例9】Northwind数据库中存放了其所有订单的信息，现要求查询其中的“10248”和“10254”号订单的订单ID、运货商的公司名称以及订单上所订购的产品的名称

12、。【例10】查询Northwind公司所有雇员的ID、姓氏、名字、职务以及其上级的姓氏、名字和职务。外连接有时也需要将不符合连接条件的记录一并查询出来，这些记录可通过外连接获得。【例11】查询Northwind公司所有雇员的ID、姓氏、名字、职务以及其上级的姓氏、名字和职务。若雇员没有上级的话，也必须将雇员的其他信息显示在查询结果中。外连接三、计算字段有时需要查询由表中某些原有字段进行适当计算后生成的新字段的值，这种新字段称为“计算字段” 。【例12】Northwind公司的销售主管希望了解一下每种产品的产品名称以及明细销售数量和销售金额。四、汇总查询在利用Microsoft Query进行查

13、询时，还可以按照某个或某些字段的值来产生对其他字段（包括计算字段）的汇总值。汇总查询【例13】 Northwind公司的销售主管希望了解一下每种产品的总销售数量和销售金额。并在此基础上，分析哪十个产品是公司的滞销产品，即销售数量最低的十大产品。【例14】Northwind公司的人事部门想根据每位销售员的业绩来分配其季度奖金。因此需要查询每位销售人员的姓氏和名字，以及其在最后一年第一季度中所负责的每份订单的订单ID和销售金额等信息。试设计一个查询，并将查询结果按雇员的“姓氏”、“名字”和“订单 ID”字段的升序排列。数据分类汇总分析利用Excel进行数据汇总数据分类汇总的三种方法Excel数据列

14、表功能数据透视表D函数加模拟运算表 Excel数据列表功能数据列表被定义为“包含相关数据的一系列工作表数据行”。数据列表的首行为字段名，首行下的各行是各个记录。数据列表中不能出现空行。数据列表可以像数据库中的表一样使用，行对应于表中的记录，列对应用于表中的字段Excel数据列表功能排序功能可以按照某个字段的升序或降序对数据列表中的所有记录进行排序。筛选功能筛选就是通过设定条件，挑选出满足条件的记录：自动筛选功能高级筛选功能高级筛选需首先在工作表中设定筛选条件。分类汇总功能可自动计算汇总字段的总计值。Excel数据列表功能【例1】利用Excel的数据列表功能和SUM函数，将ABC公司的销售数据，

15、按照下图的形式，汇总出2010年各省份各类别商品的净销售额总计值。数据透视表创建数据透视表容易，且可以对数据透视表进行旋转、变换汇总角度，选择各种汇总形式，求和、计数、求平均值等。数据透视表使用方便、汇总能力强。数据透视表数据透视表汇总数据数据透视表的灵活性数据透视图的灵活性利用数据透视表生成时间序列统计各销量组销售次数的频率分布计算百分比多重区域数据的合并汇总数据透视表汇总数据利用数据透视表进行汇总有两种方法：一种是先将数据导入Excel成为数据列表，再对数据列表进行汇总另一种是利用数据透视表直接从数据库中查询并汇总数据数据透视表汇总数据【例2】利用Excel获取外部数据功能，获取ABC公司

16、2010年的销售数据，使用Excel数据透视表功能，制作分类汇总表。数据透视表汇总数据【例3】利用Excel获取外部数据功能，获取ABC公司2010年的销售数据，使用Excel数据透视表功能，制作数据透视图。数据透视表的灵活性【例4】在上一例题基础上，利用数据透视表，汇总2010年ABC公司儿童用品与服装类别、华东与华南地区、各省、各渠道的净销售额，其中安徽与山东两省按照高、中、低价位分别显示净销售额。数据需按照净销售额的总计列排序，汇总结果如图所示。数据透视表的灵活性分类字段的调整分类字段值的调整汇总字段的调整数据透视表的灵活性分类字段的调整利用报表筛选区域筛选数据：将数据透视表字段列表中的

17、字段直接拖至报表筛选区域；从行标签或列标签区域，拖动字段至报表筛选区域；报表筛选区域，可以放置一个或多个字段；可改变多个字段的排列位置；可挑选一个值或多个值来进行筛选；可将报表筛选区域的字段拖动到其他区域；可删除“报表筛选”区域的字段。数据透视表的灵活性分类字段值的调整改变分类字段值的位置分类字段值的组合行、列标签区域字段值的筛选折叠与展开汇总数据数据透视表的灵活性汇总字段的调整新增、删除和修改汇总字段：新增汇总字段改变汇总字段的位置修改汇总字段名称删除汇总字段改变汇总字段的汇总方式数据透视表的灵活性数据透视表工具的功能数据透视表工具的两个选项卡选项设计数据透视图的灵活性创建数据透视图的灵活性

18、利用数据列表创建数据透视图直接创建数据透视图利用数据透视表（图）向导创建数据透视图分类字段、汇总字段调整利用数据透视表生成时间序列按照一定时间间隔汇总的数据序列，称为时间序列。时间序列可以帮助企业了解经营状况、预测未来的变化趋势。数据透视表能从不同的角度汇总数据，它也是生成时间序列的有效工具。利用数据透视表生成时间序列【例5】利用数据透视表，对Northwind公司的销售数据按月汇总各产品的销售额。统计各销量组销售次数的频率分布频率信息可以帮助管理人员预测未来情况，合理安排生产和销售活动。【例3-6】利用数据透视表，对Northwind公司的销售数据按照产品、年月汇总，统计各规模销量组销售次数

19、的频率分布。计算百分比 【例7】Northwind公司计划，根据客户总销售额，将客户划分为不同级别。销售额超过10000元的客户为重要客户， 10000元以下的为普通客户。 利用如图所示的数据透视表，按照客户级别，汇总销售额、销售额占总销售额的百分比、销售次数占总销售次数的的百分比，并绘制数据透视图。多重区域数据的合并汇总有些企业，由于经营地点比较分散，导致不同地点的各部门将数据存放在多个Excel工作簿或多个工作表中。有些企业，会将数据按照时间存放在多个工作簿或工作表中。这样，就产生了相同字段的数据分散放在多重区域的情况。存放在多重区域的数据，在汇总时需要首先将数据合并，然后再汇总。多重区域

20、数据的合并汇总 【例8】NorthLand公司2011年1月、2月与3月的销售数据，保存在工作簿中“NorthLand公司各月销售数据.xlsx”的三张工作表中，如图所示。 这些销售数据记录了该公司3个月中在南京、苏州、无锡等地的销售情况。请利用这些数据，汇总该公司2011年1季度各地区的销售数量与销售金额。多重区域数据的合并汇总多重区域数据的合并汇总 【例】利用工作簿 “NorthLand公司各月销售数据.xlsx”保存的NorthLand公司2011年1、2、3月的销售数据，汇总该公司2011年1季度各地区、各客户、各产品的销量与销售金额。多重区域数据的合并汇总D函数加模拟运算表D函数模拟

21、运算表利用控件控制汇总结果利用D函数与控件汇总时间序列利用控件控制多个汇总图表利用控件控制多个汇总相关项D函数加控件汇总方法的总结D函数 Microsoft Excel 共有 12 个数据库内建函数，统一称为D函数，用于对数据列表中的数据进行分类汇总分析D函数名功 能DSUM统计满足条件记录的指定字段的总计值DAVERAGE统计满足条件记录的指定字段的平均值DCOUNT统计满足条件记录的行数DCOUNTA统计满足条件而且指定字段值不为空的记录行数DMAX统计满足条件记录的指定字段的最大值DMIN统计满足条件记录的指定字段的最小值DGET提取满足条件记录的指定字段值D函数D函数名称(数据列表，汇总字段，条件区域)数据列表，是包含相关数据的工作表中的一块数据区域；汇总字段，可以是字段名、字段名所在的单元格或字段在数据列表中所在列的序号；条件区域，是一个区域，它规定函数的匹配条件。只有满足条件的记录，才能够被该函数汇总；条件区域首行是字段名，以后各行是字段的值。D函数 【例10】利用ABC公司的销售数据，应用DSUM函数，汇总ABC公司各省份各类别净销售额的总计值，结果如图所示。模拟运算表模拟运