还剩20页未读,继续阅读
本资源只提供10页预览,全部文档请下载后查看!喜欢就下载吧,查找使用更方便
文本内容:
财务人员计算机数据处理(Excel)培训
一、基础知识
1、工作簿指在Excel中保存表格内容的文件通常说的Excel文档,就是工作簿文档,一个工作簿包含若干个工作表一个工作簿最多可有1024个工作表,分别以Sheet
1、Sheet
2、Sheet3自动命名
2、工作表工作表是由多行多列构成的一个平面二维表格,通常称电子表格,一个工作表共有1048576行和16384列
3、单元格工作表中行与列相交形成的长方形区域,称单元格,默认情况下,Excel用列标和行标表示某个单元格的位置,称为单元格地址,例如A1表示第A列第一行的单元格1相对地址B32绝对地址$B$33三维地址Sheet2!B3
二、工作表的操作
1、输入数据若想输入的数据转换为文本,只需在输入数值前输入一个单引号‘;
2、分数的输入在单元格输入分数,必须在整数和分数之间留出一个空格例如输入3(1/2),正确方式为输入3空格1/2,若无整数必须先输入数字0,再输入分数
4、复制粘贴1常规粘贴2选择性粘贴例“复制粘贴”文件中数据复制和点拆线图表格式复制;
5、条件格式条件格式将工作表的表格数据区进行设置;例Ks4-1文件Sheet2中介于5至18之间的数据设置为鲜绿色底纹
三、窗口操作
1、拆分工作表
2、冻结工作表请见文件“拆分和冻结”
3、保护工作表工具/保护/保护工作表或工具/选项/安全性
四、数据处理、数据透视表Excel工作表汇总大量数据,并且使用排序、分类汇总等工具,但仍然不能满足对数据处理的需求,为此Excel提供了数据透视表和透视图强大的功能
1、数据清单数据清单相当于一张完整的报表,是位于工作表中的有组织的信息集合;或相当于数据库中的记录规则每个工作表仅使用一个数据清单
2、数据排序分为升序和降序两种;以主要关键字和次要关键字和再次要关键字排序汉字一般以拼音的顺序进行排序,若希望以汉字的笔画排序,数据/排序,在选择主关键字字段,选项/笔划排序例Ks4-7文件使用SHEET2工作表中的表格按“一月”为主关键字递增,“二月”为次关键字递增,“三月”为最次关键字递减排序
3、数据筛选1自动筛选适用于简单的筛选数据;例Ks4-14文件SHEET2工作表的表格中自动筛选出“服装”大于60000,“鞋帽”大于200000的值例Ks4-4文件自动筛选出SHEET2工作表的表格中“地区“为华北、西北的值2高级筛选复杂筛选数据,并且可分离出所需的数据清单;例Ks4-4文件高级筛选出SHEET2工作表的表格中“地区“为华北、西北的值操作单击B13-B15,键入“地区”回车、“华北”回车、“西北”回车,单击B2-H11任意单元格,单击菜单栏“数据”/筛选/高级筛选命令,单击选定“将筛选结果复制到其他位置”单选项,单击列表区域折叠按钮,选定B2H11,单击条件区域数据折叠按钮,单击选定B13-B15,单击复制到折叠按钮,单击B16单元格,单击“确定”例“产品销售分析与预测”文件工作表中的销售数据利用高级筛选筛选出为“地区”为“华北”、城市为“天津”、联系人为“陈先生”和“林小姐”及“周先生”记录;操作在数据列表下输入“地区”、“城市”、“联系人”等内容,数据/筛选/高级筛选,单击选定“将筛选结果复制到其他位置”单选项,单击列表区域折叠按钮,选定数据区域,单击条件区域数据折叠按钮,单击选定输入的条件区域,单击复制到折叠按钮,选择需要分离出的位置单元格,单击“确定
4、分类汇总分类汇总是对数据列表进行数据分析的一种方法,对数据列表中指定的字段进行分类,然后统计同一类记录的有关信息,要正确使用分类汇总的功能必须对汇总条件列的数据进行排序,然后才能根据数据列中的分类项目,对指定列的数据进行分类汇总例Ks4-5文件使用SHEET2工作表中的表格以“职业”为主要关键字,以“递增”方式排序以“职业”为分类字段,以“计数”为汇总方式,以”模块”为选定汇总项进行分类汇总例Ks4-2并以”处室”为分类汇总字段,将“交通”、“软件开发”、“书藉”、“翻译”、“设备”、“差旅费”、“平均值”以求和项汇总;
5、合并计算合并计算是指用来汇总一个或多个源区域中的数据的方法合并计算数据,首先必须为汇总信息定义一个目的区,此目的区域可位于与源数据相同的工作表上,或在另一个工作表上,其次需要选择合并计算的数据源,此数据源可以来自单个工件表、多个工作表或多重工作簿中例Ks4-15文件使用SHEET2工作表中的表格内容,在SHEET2工作表中的“各年级平均年龄及成绩”表中进行平均值合并计算Sheet5工作表中,对课程名称进行人数、课时合并计算Sheet6工作表中,对二个销售表中的销售额进行合并计算操作Sheet2中,单击D15,数据/合并计算,在对话框中选函数中的平均值,单击引用位置折叠按纽,选定D4F11,选择“标签位置”中的“最左列”复选框,确定Sheet5中,单击F3,数据/合并计算,在对话框中选函数中的求和,单击引用位置折叠按纽,选定B3D27,选择“标签位置”中的“最左列”复选框,确定Sheet6中,单击B12,数据/合并计算,在对话框中选函数中的求和,单击引用位置折叠按纽,选定B3C8,单击“添加”按纽,单击引用位置折叠按纽,选定E3F7,单击“添加”按纽,选择“标签位置”中的“最左列”复选框,确定
6、数据透视表数据透视表是一种交互式的数据报表,可以快速合并和比较大量数据,可以深入分析数值数据,同时可以通过选择其中页、行和列中的不同数据元素,以快速查看源数据的不同统计结果,并能随意显示和打印出明细数据报表数据透视表制作步骤为选择数据源类型选择数据源区域指定数据透视表位置布局更改统计报表布局和显示内容创建数据透视表菜单数据/数据透视表和数据透视图命令,启动数据透视表向导,步骤一选择默认Excel数据列表可选下一步;步骤二选择数据源区域,通过手动或自动选择数据源区域,下一步;步骤三选择将透视表创建在一个新工作表,还是在当前工作表,下一步;步骤四布局,该步骤是创建透视表最关键的一步;用鼠标拖动右边字段到“页”区域、“行”区域、“列”区域、数据区域;例Ks4-12文件中使用SHEET2工作表表格中的数据,以“名称”为分页,“单位”为行字段,“日期”为列字段,“份数”为求和项,从SHEET2工作表的B15单元格处建立数据透视表例打开“产品销售分析与预测”文件,要求统计每个季度中各地区的销售情况将“地区”字段拖动“页”区域;将“城市”字段拖动到“行”区域;将“订货日期”字段拖动到“列”区域;将“订货金额”字段拖动到“数据”区域a创建销售统计年、季、月报表(以1997年为例)在数据透视表中,选中“订货日期”字段按纽单击鼠标右键,选择“级及显示明细数据”/组合;打开“分组”对话框,选中“起始于”和“终止于”前面的复选框,并在“起始于”和“终止于”文本框中输入一个时间间隔,然后在“步长”下拉列表中选择“月”,即要对1997年的销售金额按照月的方式查阅b创建销售统计季报表“级及显示明细数据/组合”中取消“月”选项,选择“季度”;c创建销售统计年报表“级及显示明细数据/组合”中取消“季度”选项,选择“年”;步骤五更改布局,每个公司的报表格式不会全部一样,但可借助数据透视表功能,可以快速改变报表布局例如希望生成综合报表,可将“地区”字段拖动到“城市”字段前面即可,这样可以制作出不同风格、不同用处的报表甚至如果希望想查看某个地区、某个城市的明细数据,只需双击相应的数据条目,即可打开“显示明细数据”对话框,然后选择相关字段即可;如果想添加联系人,在表格中右击选择“选择数据透视表工具栏”,选择最后一项“显示字段列表”,将“联系人”字段添加到“行”区域
7、数据透视图数据透视图具有丰富的图表类型,包括柱形图、条形图、折线图、饼图等等,数据透视图创建步骤为在数据透视表中单击右键,选择“数据透视图”;自动形成数据图然后对数据透视图调即可
五、工作表的审核在使用公式对数据运算、管理和分析时,使用“审核”工具栏提供的工具可以检查工作表公式与单元格之间的相互关系;追踪箭头为公式提供数据的引用单元格、哪些单元格包含相关的公式,如果公式产生各种无效的错误值,还可以定位造成错误的单元格工作表审核功能可以快捷地分析单元格中数据、公式关系,查找引起公式错误的原因例打开文件ks5-
4.xls运用数组创建公式运用数组为“东部”、“西部”、“北部”3行及“上半年销售量”一列单元格设置求和公式格式;工作簿的链接同时打开ksml3\ks5-4a.xls工作簿和ksml3\ks5-4b.xls工作簿,使用单元格的绝对引用,将数据链接到ks5-4工作表的就位置数据审核运用审核的“追踪引用活动单元格”工具,验证“西部”的“一月”和“上半年”一列数组公式设置的正确性六模拟运算表
1、单变量模拟运算表单变量模拟运算表的结构特点是其输入数值被排列在一列中(列引用)或一行中(行引用)
2、双变量模拟运算表双变量模拟运算表的特点模拟运算表中输入的两组数值使用同一个公式这个公式必须引用两个不的的输入单元格例打开文件ks5-
13.xls定义数据的有效性将对外借款一列数据的有效性设定为500至5000之间的小数;利用审核功能在表中圈释出无效数据单元格,并将所有无效数字字体改为红色操作选定C6C27,单击数据/有效数据,单击设置,单击“有效条件”的“许可”下拉箭头,在列表中选中“整数”,单击“数据”列表中选择“介于”,在最小值输入500,最大值输入5000;工具/审核/单击“显示审核工具栏”,在选中C6C27单元格的情况下,单击“审核”工具栏中的“圈释无效数据”选中C6C27单元格,格式/条件格式,单击列表中“并非介于”条件输入500和5000,单击“格式”,单击“字体”,选择“红色字体”,确定数组的应用利用数组求出各国利用外交的“总计”;操作选中E7E27,输入“=”,选定C7C27,输入“+”,选定D7D27,按“Ctrl+Shift+Enter”数据链接将亚洲各国利用外资的总计值与“各国利用外资总额”相链接;操作选定E27,单击复制,单击D30,编辑/选择性粘贴,单击选定“粘贴链接”模拟运算表的运用利用模拟运算表求出各国利用外资的总额在亚洲总额中所占的百分比值,计算结果设置为3位小数;操作方法一选定F6,输入“=”,单击选定D29,输入“/”,单击选定D30,按Enter,单击选中E6F27,数据/模拟运算表,单击“输入引用列的单元格”编辑框,单击D29,确定设置3位小数操作方法二单击F6,输入“=”,单击E6,输入“/”,单击D30,按Enter,在公式栏中将E6/D30改为E6/$D$30,F6单元格左下角变成“+”时,拖动到E27设置工作簿为允许多用户同时编辑,同时允许工作簿合并操作工具/共享工作簿,单击“允许我用户同时编辑、同时允许工作簿合并”复选框例打开文件ks5-5公式运用利用函数PMT计算出“货款计算表1”中的应付款;操作单击C7,单击粘贴函数,选择PMT,单击Rate,选定C5,输入“/12”,单击Nper选定C6,输入“*12”,单击Pv,单击C4,确定双变量分析运用模拟运算表,分析并计算Sheet2中“货款计算表2”6年期,“应付款”随“本金”和“利率”的变化而相应变动的结果;操作在Sheet2中,单击B4,单击粘贴函数,选择PMT,单击Rate,选定B10,输入“/12”,在Nper栏中输入“6*12”,单击Pv,单击H4,确定选定B4G9,数据/模拟运算表,单击“输入引用行的单元格”,单击H4,单击“输入引用列的单元格”,单击B10,确定七单变量求解运用单变量求解可以通过调其它单元格的数值,为某一单元格寻求一个特定值例打开文件ks5-
12.xls,公式及数组的应用按公式“资金利税率(%)=利税总额/(固定资产平均余额+流动资产平均余额)”运用数组的功能计算出表1中资金利税率的值,并以百分比的格式表示操作单击选中F4F8,输入“=”,选中E4E8,输入“/(”,选中D4D8,输入“+”,选中C4C8,输入“)”,按Ctrl+Shift+Enter;单击右键,选择“设置单元格格式”,单击“数字”,选定“分类中的百分比”,设定“小数点后位数”为2;单变量求解以表2中资金利税率的值作为目标单元格的值,依次求出表1中所有空白单元格的值操作工具/单变量求解,单击选定F4,在“目标值”输入栏中输入
0.0968,单击“可变单元格”编辑框,单击选定E4,单击确定,确定工具/单变量求解,单击选定F5,在“目标值”输入栏中输入
0.0672,单击“可变单元格”编辑框,单击选定C5,单击确定,确定工具/单变量求解,单击选定F6,在“目标值”输入栏中输入
0.0749,单击“可变单元格”编辑框,单击选定E6,单击确定,确定工具/单变量求解,单击选定F7,在“目标值”输入栏中输入
0.1308,单击“可变单元格”编辑框,单击选定D7,单击确定,确定工具/单变量求解,单击选定F8,在“目标值”输入栏中输入
0.0858,单击“可变单元格”编辑框,单击选定D8,单击确定,确定
八、典型范例范例
一、Word文档中链接插入Excle表格主要知识点表格插入方式复制以对象方式插入表格粘贴链接操作打开Excel文件,选择工作表中相关单元格区域;右击/复制;切换到Word文件中,选择粘贴的位置,编辑/选择性粘贴/MicrosoftExcle工作表对象;单击粘贴链接,确定;保存文件数据刷新当Excel文件工作表中的数据发生改变已后,重新打开Word文件时会自动提示是否更新数据?范例
二、Web行情信息分析表打开文件Web行情信息分析表主要知识点使用Web查询导入因特网数据操作数据/导入外部数据/新建外部查询,在地址栏中输入网址如上海有色金属网4月铅锭价格(j0020318572214),选择Web页中的数据区域,单击导入,选择新建或现有工作表,单击属性,选择刷新控件中打开工作簿时,自动刷新复选框工作表数据的动态刷新重新打开Excle文件时会自动提示是否链接更新数据;范例
三、制作与打印工资表(宏的应用)打开文件制作与打印工资表主要知识点公式的输入与复制数据有效性录制宏命令、编辑宏和VBA完成自动化操作操作工具/宏/录制新宏,输入新宏的名称如打印工资表单击停止宏按纽工具/宏/宏,选择宏打印工资表,单击编辑,将下面的代码输入到中间单击左上角“视图MicrosoftExcle”按纽切换到工作表中电子表格的安全性设置工具/选项,打开选项对话框单击“安全性”标签,打开“安全性”选项卡,单击宏安全性,在安全性对话框中,选择“低”单选项,单击确定单击工具/宏/宏,选择宏打印工资表,单击执行CellsSelection.RowSelection.Column.SelectRangeSelectionSelection.EndxlToRight.SelectSelection.CopyActiveCell.Offset
20.Range“A1”.SelectDoUntilActiveCell=““Selection.InsertShift:=xlDownRangeSelectionSelection.EndxlToRight.SelectSelection.CopyActiveCell.Offset
20.Range“A1”.SelectLoopApplication.CutCopyMode=False以下为在电子表格软件中打开文档A
4.XLS,进行如下操作4.1第1题(以下为Excel电子表格)【操作要求】
1.表格的环境设置与修改在SHEETl工作表表格的标题行之前插入一空行将标题行行高设为25,将标题单元格名称定义为”比例表”
2.表格格式的编排与修改将SHEETl工作表表格的标题行格式设置为跨列居中,垂直居中,楷体,加粗,12磅,黄色底纹,蓝色字体将数字单元格的数字居中将最后一行的底纹设置为鲜绿色将KSML3\CLOUDS-BMP设定为工作表背景3数据的管理与分析计算SHEETl工作表中表格的四列数据平均值,填入“平均增长率”一行的单元格中在SHEET2工作表中按“最高气温“递增排序利用条件格式将SHEET2工作表的表格数据区中介于5至18之间的数据设置为鲜绿色底纹4图表的运用以SHEET3工作表的表格中的内容,在SHEET3工作表中插入创建一个饼图三维饼图图表,设置图表中字体大小和数字类型图表标题格式为宋体,字号为10,数据标志格式为宋体,字号为8,并将“激光视盘机“改为“VCD”机
5.数据、文档的修订与保护保护SHEET3工作表内容,密码为“gjks4-1”
4.2第2题【操作要求】
1.表格的环境设置与修改在SHEETl工作表中表格“加元”列之前插入一空列,填入如下值“瑞朗、
1.
433、
1.
4291、
1.
4255、l.
4127、
1.
408、
1.
404、
1.414”将标题行行高设为20,将标题单元格名称定义为“预测表”
2.表格格式的编排与修改将标题行设置成如下列格式跨列居中,垂直居中,隶书,加粗倾斜,16磅将日期行跨列居右;将表格设为自动套用“彩色2”格式,对3-11行设置为无填充色,加框线将数字使用货币样式,居右,其余文字单元格全部居中将KSMl3\LoWDUNES.GIF设定为工作表背景
3.数据的管理与分析计算SHEET2工作表中表格中各列的平均值,填入“平均值”列中的相应单元格中;并以”处室”为分类汇总字段,将“交通”、“软件开发”、“书藉”、“翻译”、“设备”、“差旅费”、“平均值”以求和项汇总;利用条件格式将SHEET2工作表中表格数据区中介于2至6之间的数据设置为蓝色底纹
4.图表的运用以SHEET3工作表中的表格中的各类商品的数据为数据源在SHEET3工作表中嵌入创建一个圆环图圆环图图表设置图表中图表标题格式宋体,字号16图例格式为宋体,字号8数据标志格式为宋体,字号
85.数据、文档的修订与保护保护工作薄结构,密码为“gjks4-2”
4.3第3题【操作要求】
1.表格的环境设置与修改删除SHEETl工作表中表格中的空行,将表格中“甲方案“行与”乙方案“行对调将标题行行高设为25,将标题单元格名称定义为“决策分析”
2.表格格式的编排与修改将SHEET1工作表中表格的标题行设置成如下列格式跨列居中,垂直居中,隶书,加粗,18磅,淡灰色底纹将表格的表头和左端两列文字居中,“第一年“和“第二年“在本栏跨列居中,并为除标题外的各行添加浅绿色网格底纹将KSML3\ALTAQUAJPG设定为工作表背景
3.数据的管理与分析计算SHEET2工作表的“世界各大城市1~6月份气温极值℃”表格中各列的平均值填入”平均气温”行的单元格中;并进行最低值合并计算利用条件格式将SHEET2工作表“世界各大城市1~6月份气温最低值”表格数据区中大于18的数据设置为蓝色底纹
4.图表的运用以SHEET3工作表中的表格使用一至四季度各种商品销售额的数据在SHEET3工作表中插入创建一个柱形图簇状柱形图图表设置图表中图表标题格式为宋体、12号,数值轴、分类轴格式为宋体、9号
5.数据、文档的修订与保护保护工作薄窗口,密码为“gjks-3”
4.4第4题【操作要求】
1.表格的环境设置与修改删除SHEETl工作表表格中的空列,在标题行前插入一空行将“收盘价”列移至“最高价“列的前面,标题行行高设为20,标题单元格名称定义为“股票行情”
2.表格格式的编排与修改在SHEETl工作表表格中将标题行设置成如下列格式跨列居中,垂直居中,宋体,加粗,16磅;将表头文字居中,字体为加粗表格日期单元格中的数据类型设为如样文类型,价格单元格中数据设为“会计专用“型,货币符号表格设置为自动套用格式“三维效果2”将KSML3\STRMTBMP设定为工作表背景
3.数据的管理与分析计算SHEET2工作表的表格中各列的总计填入“总计”行的单元格中高级筛选出SHEET2工作表的表格中“地区“为华北、西北的值
4.图表的运用以SHEET3工作表中的表格中的内容,在SHEET3工作表中插入创建一个柱形图簇状柱形图图表设置图表标题格式为宋体,字号为11数据轴格式为宋体,字号为8分类轴格式为宋体,字号为10图例格式为宋体,字号
105.数据、文档的修订与保护将SHEET2工作表中的表格设为突出显示修订,时间为起始日期,修订人为每个人,在屏幕上突出显示
4.5第5题【操作要求】
1.表格的环境设置与修改将SHEET1工作表中的表格标题行行高设为20,将SHEETl2工作表重命名为“登记表“
2.表格格式的编排与修改在“登记表“工作表表格中统计人数并计算平均总分填入到相应单元格中,将表格中的标题行设置成如下列格式跨列居中,垂直居中,宋体,加粗,12磅将表格的表头文字设为居中,加粗将整个表格设为,浅绿色底纹,深红色字体,加边框;将KSML3\ASPHALT.JPG设定为“登记表“工作表的背景
3.数据的管理与分析利用条件格式将SHEET2工作表中表格的“教师”设置为红色底纹、白色字体使用SHEET2工作表中的表格以“职业”为主要关键字,以“递增”方式排序以“职业”为分类字段,以“计数”为汇总方式,以”模块”为选定汇总项进行分类汇总
4.图表的运用以SHEET3工作表中的表格内容,在SHEET3工作表中插入创建一个分离型圆环图图表设置图表标题格式为宋体,字号为12图例格式为宋体,字号为10数据标志格式为宋体,字号为
85.数据、文档的修订与保护保护并共享工作簿,密码为“gjks4-5”
4.6第6题【操作要求】在电子表格软件中打开文档A
4.XLS,进行如下操作
1.表格的环境设置与修改在SHEET1工作表中的B6单元格前插入一行,填入相应的内容将SHEETl工作表中的表格标题行行高设为20,并将标题行单元格名称定义为“学生成绩”
2.表格格式的编排与修改计算SHEET1工作表的表格中各列数据的平均值,填入“平均成绩”一行的单元格中将表格套用“彩色2”自动套用格式将KSML3\KYBD.JPG设定为工作表背景设置SHEETl工作表表格的打印标题,顶端标题行为第一行,左端标题行为B列,以网格线,单色打印
3.数据的管理与分析使用KSML1\SJY
1.DBF的数据,以“学校代码”为分页,“办学类型”为列字段,“XXMC”,“BMDM”,“BMMC”,“SSDM”,“SSMC”为计数项,从工作表SHEET2的Al单元格处起建立数据透视表
4.图表的运用以SHEET3工作表中表格的内容,在SHEET3工作表中插入创建一个条形图三维簇状条形图图表设置图表标题格式为宋体,字号为14数值轴格式为宋体,字号为8坐标轴格式为宋体,字号为10数据表格式为宋体,字号为
105.数据、文档的修订与保护保护SHEET1工作表内容、对象、方案,密码为“gjks4-6”
4.7第7题【操作要求】
1.表格的环境设置与修改在SHEET1工作表中总计行前插入一行,填入相应的内容将标题行行高设为20,并将SHEET1工作表重命名为“统计表“
2.表格格式的编排与修改在SHEETl工作表中计算各项总计并填入相应单元格中,将标题行设置为跨列居中,垂直居中,楷体,加粗,字号为14将表头行和第一列为居中,宋体,加粗,字号为12全表浅绿色底纹,数字格式为会计专用,无小数位,添加货币符号
3.数据的管理与分析使用SHEET2工作表中的表格按“一月”为主关键字递增,“二月”为次关键字递增,“三月”为最次关键字递减排序利用条件格式将大于500的数据设置为红色字体
4.图表的运用以SHEET3工作表中的表格创建一个饼图分离型三维饼图图表设置图表标题格式为宋体,字号为12数据标志字号为10图例格式为宋体,字号为
105.数据、文挡的修订与保护将SHEET4工作表中的表格进行突出显示修订,时间为全部,修订人为每个人,在屏幕上突出显示修订
4.8第8题【操作要求】
1.表格的环境设置与修改在SHEETl作表表格中的标题行后插入一行将表格的标题行行高设为22,并将标题单元格命名为“销售表”
2.表格格式的编排与修改计算SHEETl工作表表格中各行产品的盈利额,并填入“盈利”一列的单元格中;将表格套用“会计2”自动套用格式;将SHEETl工作表表格的标题行设置为跨列居中,垂直居中,仿宋表头行为仿宋,后三列数字格式为货币式样,无小数位,符号如样文
3.数据的管理与分析使用SHEET2作表中的表格,在“设备汇总表”中进行“求和”合并计算
4.图表的运用以SHEET3工作表中的表格内容,在SHEET3工作表中创建一个条形图三维堆积条形图图表;设置图表标题格式为宋体,字号为12分类轴格式为宋体,字号为10数值轴格式为宋体,字号为8图例格式宋体,字号为
105.数据、文档的修订与保护按样文4-8D,在SHEET4工作表表格中将“激光打印机”全部替换为“HP\7585A打印机”保护工作表方案,密码为“gjkS4-8”
4.9第9题【操作要求】
1.表格的环境设置与修改在SHEETl作表表格中的D列前插入一空列,并填入相应的内容在SHEETl工作表表格中合并B4至B6单元格,将其中内容水平居中,垂直居中将表格的标题行行高设为
202.表格格式的编排与修改计算SHEETl工作表表格中各年度的平均值,并填入“平均值”一行的单元格中将表格套用“经典2”自动套用格式,将SHEETl工作表表格的标题行设置为跨列居中,垂直居中将表格表头行设为粗体,居中其余单元格居中
3.数据的管理与分析在SHEET2工作表中的表格中利用高级筛选筛选出品牌为“牡丹”的值;利用条件格式将表格中各季度小于2500和大于8000的价格设置为紫色底纹,白色字体
4.图表的运用以SHEET3工作表表格中的内容,在SHEET3工作表中插入创建一个饼图复合柱饼图图表设置图表标题格式为宋体,字号为12数据标志格式为宋体,字号为8图例格式为宋体,字号为
85.数据、文档的修订与保护将SHEET4工作表表格中的“PHILIPS”替换为“飞利浦”,并保护工作表内容,密码为“gks4-9”
4.10第10题【操作要求】
1.表格的环境设置与修改删除SHEETl工作表表格中的空行,合并B3至B4单元格,合并C3至C4单元格,将其中内容水平居中,垂直居中将SHEETl工作表表格中的标题行行高设为20,并将标题名称定义为“预算表”
2.表格格式的编排与修改计算SHEETl工作表表格中各列的和,填入“合计”一行的单元格中,将SHEET1工作表表格中的标题行设置为跨列居中,垂直居中,隶书,字号为20将表格中的数据设置为货币样式,无小数位全表字体为白色将KSML3\ASHWOOD.JPG设定为SHEETl工作表背景
3.数据的管理与分析以SHEET2工作表表格中的“帐目”为分组分类汇总字段,将表格中的“实际支出”、“预计支出”、“调配拨款”和“差额”的平均值汇总
4.图表的运用以SHEET3工作表中的表格内容,在SHEET3工作表中插入创建一个饼图三维饼图图表设置图表标题格式为宋体,字号为12数据标志格式为宋体,字号为8图例格式为宋体,字号为
85.数据、文档的修订与保护保护工作薄的窗口,密码为“gjks4-10”
4.11第11题【操作要求】
1.表格的环境设置与修改在SHEETl工作表中的D列后插入一空列,并填入相应的内容将SHEETl工作表表格中的标题行行高设为
202.表格格式的编排与修改计算SHEETl工作表中各列的总计,填入“合计”一行的单元格中将表格套用“三维效果1”自动套用格式将表格的标题设置为黑体,字号为14将表格中的数据设置为货币样式,无小数位
3.数据的管理与分析在SHEET2工作表的“平湖、海宁两县预算内财政支出表”中进行求和计算
4.图表的运用1以SHEET3工作表表格中的内容,在SHEET3工作表中插入创建一个圆锥图柱形圆锥图图表;设置图表标题格式为宋体,字号为12数值轴格式为宋体,字号为8分类轴格式为宋体,字号为8数据标志格式为宋体,字号为8图例格式为宋体,字号为
125.数据、文档的修订与保护保护SHEET4工作表的内容和方案,密码为“gks4-11”
4.12第12题【操作要求】
1.表格的环境设置与修改插入SHEET4工作表,并把它移至SHEET3工作表之后将SHEETl工作表重命名为“市场报告”
2.表格格式的编排与修改计算SHEETl工作表表格中各列的总计,填入”总计一行的单元格中,将SHEETl工作表表格中的标题设为跨列居中,垂直居中,隶书,字号为20将表格的表头行设置为宋体,加粗,居中将表格的第一列设置为宋体,加粗,字号为10将表格中的价格数据设置为货币样式,无小数位将表格中的增长率设置为百分比样式,无小数位给全表加上淡灰色底纹,白色字体
3.数据的管理与分析使用SHEET2工作表表格中的数据,以“名称”为分页,“单位”为行字段,“日期”为列字段,“份数”为求和项,从SHEET2工作表的B15单元格处建立数据透视表使用条件格式将SHEET2工作表透视表中“考评员”统计项的份数小于20或份数大于80的数据设置成红色底纹,保持结果
4.图表的运用以SHEET3工作表表格中的内容,在SHEET3工作表中插入创建一个饼图复合饼图图表设置图表中字体大小和数字类型图表标题格式为宋体,字号为12数据标志格式为宋体,字号为8图例格式为宋体,字号为
105.数据、文档的修订与保护保护工作薄的结构和窗口,密码为“gks4-12“
4.13第13题【操作要求】
1.表格的环境设置与修改在SHEET1工作表中,合并B4至B6单元格,合并B7和B8单元格将其中内容水平居中与垂直居中.将SHEETl工作表表格中的标题行高设为20,将表格中的标题定义名称为“销售统计表”
2.表格格式的编排与修改计算SHEETl工作表表格中各列的总计,填入“总计”一行的单元格中,将SHEET1工作表表格中的标题跨列居中,垂直居中,楷体,加粗,字号为16将表格中的表头行和前两列文字设置为楷体,加粗,字号为12将表格中的价格数据设置为货币样式,无小数位给全表加上边框为表格设置打印标题,以表格中的标题行为顶端标题行,按先行后列的顺序打印
3.数据的管理与分析使用SHEET2工作表表格中的内容以“产品类型”为分组分类汇总字段,将“一月”、“二月”、“三月”和“四月”的最大值汇总利用条件格式将SHEET2工作表表格中的销售额大值得60的数据设置为蓝色底纹
4.图表的运用以SHEET3工作表表格的内容,在SHEET3工作表中创建一个雷达图数据点雷达图图表按样文设置图表中字体大小和数字类型图表标题格式为宋体,字号为12坐标轴格式为宋体,字号为8分类标志格式为宋体;字号为8图例格式为宋体,字号为
105.数据、文挡的修订与保护保护并共享工作薄,密码为“gks4-13”
4.14第14题【操作要求】
1.表格的环境设置与修改在SHEETl工作表表格中的表头行下插入一空行,并填入相应内容将表格的标题行高设为25,将SHEETl工作表重命名为“销售统计表”工作表
2.表格格式的编排与修改计算SHEET1工作表表格中的各行的总计,各列的最高额、平均额,分别填入“总计”一列、“最高额”一行和“平均额”一行的单元格中;将表格套用自动套用格式“简单“;将SHEETl工作表表格中的价格数据设置为货币样式,无小数位,适当调整列宽
3.数据的管理与分析在SHEET2工作表的表格中自动筛选出“服装”大于60000,“鞋帽”大于200000的值利用条件格式将SHEET2工作表的表格中的各产品大于200000小于1000000的价格设置为粉红色字体
4.图表的运用以SHEET3工作表表格中的内容,在SHEET3工作表中插入创建一个面积图堆积面积图图表设置图表标题格式为宋体,字号为12数值轴格式为宋体,字号为8坐标轴格式为宋体,字号为10分类标志格式为宋体,字号为80图例格式为宋体,字号为
105.数据、文挡的修订与保护保护并共享工作薄,密码为“gks4-14”
4.15第15题【操作要求】
1.表格的环境设置与修改复制SHEETl工作表,并将新表命名为“商品分析表”在“商品分析表”工作表中合并B3至B4单元格,将其中内容水平居中,垂直居中将“商品分析表”工作表的表格中标题行高设为
252.表格格式的编排与修改计算“商品分析表”工作表表格中各列的平均值,填入“平均率”一行的单元格中将表格中的标题行跨列居中,垂直居中,将标题设为隶书,字号为20,加粗将表格中的表头行与第一列设为楷体,字号为l2,加粗并为表格加上如样文的边框,淡蓝色底纹将表格巾的标题行设置为打印标题的顶端标题行
3.数据的管理与分析使用SHEET2工作表中的表格内容,在SHEET2工作表中的“各年级平均年龄及成绩”表中进行平均值合并计算
4.图表的运用以SHEET3工作表中的表格内容,在SHEET3工作表中插入创建一个股价图开盘-盘高-盘低-收盘图图表设置图表标题格式为宋体,字号为12数值轴格式为宋体,字号为8分类轴格式为宋体,字号为8;图例格式为宋体,字号为10,图案带阴影
5.数据、文档的修订与保护对SHEET4工作表中的表格进行突出显示修订,时间为起始日期,修订人为每个人,在屏幕上显示修订将表格中的“品牌”替换成“名牌”
4.16第16题【操作要求】
1.表格的环境设置与修改在SHEETl工作表中的E列前插入一空列,并填入相应的内容合并B3至B4单元格将SHEETl工作表表格中的标题行高设为20将SHEETl工作表重命名为“学生人数统计表“
2.表格格式的编排与修改计算SHEET1工作表表格中各列的总计,填入“总计”一行的单元格中将表格套用“三维效果1”自动套用格式将SHEETl工作表表格中的标题设为楷体,字号为16,居中将表格的表头行与第一列设为楷体,字号为12将表格中的数据使用千位分隔符,无小数位将KSML3\EVRGREENJPG设定为SHEETl工作表背景适当调整列宽
3.数据的管理与分析使用SHEET2工作表中的表格内容,以“故事片”为主关键字,“美术片”为次关键字,“科学教育片”为第三关键字,都以递增顺序排序利用条件格式将SHEET2工作表中的表格中各片产量小于100的数据设置为绿色底纹4图表的运用以SHEET3工作表中的表格创建一个XY散点图折线散点图图表设置图表标题格式为宋体,字号为12数值轴格式为宋体,字号为8;分类轴格式为宋体,字号为8;图例格式为宋体,字号为10,图例框为蓝色
5.数据、文档的修订与保护保护工作薄的结构和窗口,密码为“gks4-16”
4.17第17题【操作要求】
1.表格的环境设置与修改插入SHEET4工作表,并移至SHEET3工作表之后在SHEETl工作表中合并B4至B7单元格,合并B8和B9单元格,合并B10和B11单元格将其中内容水平居中,垂直居中将SHEETl工作表表格的标题行高设为20将SHEETl工作表重命名为“全年销售表”
2.表格格式的编排与修改计算SHEETl工作表表格中的各行的最高值与各列的平均值,并填入到“最高值”一行和“平均值”一列的相应单元格中将SHEETl工作表表格中的标题设为跨列居中,隶书,字号为20,加粗将表格的表头行与第
一、二列设为居中,宋体,字号为12,加粗将表格中的数据设为货币样式,无小数位为表格添加绿色底纹,白色字体适当调整列宽
3.数据的管理与分析使用SHEET2工作表表格中的内容,以“类型”为分组分类汇总字段,将“第一季度”、“第二季度”、“第三季度”、“第四季度”和“最高额”求和汇总4图表的运用以SHEET3工作表表格中的内容,在SHEET3工作表中插入创建一个面积图三维而积图图表设置图表标题格式为宋体,字号为12,数据轴格式格式为宋体,字号为8;分类轴格式为宋体,字号为8;系列轴格式为宋体,字号为8图例格式为宋体,字号为10,为图例框填充绿色底纹
5.数据、文档的修订与保护保护工作薄的窗口,密码为“gks4-17”
4.18第18题【操作要求】1.表格的环境设置与修改在SHEETl工作表中的B列前插入一空列,并填入相应的内容将SHEET1工作表的表格中的标题名称定义为“消费水平调查表”
2.表格格式的编排与修改计算Sheet1工作表的表格中各列的最高消费与最低消费,并填入相应单元格中将SHEET1工作表的表格中的标题设为跨列居中,使用表格自动套用格式“三维效果2”将表格中的最高消费和最低消费合并跨列居中适当调整列宽将图片KSML3\FABRIC2JPG设定为SHEETl工作表背景3数据的管理与分析使用SHEET2工作表的表格中的数据,以“品牌”为分页,以“大小”为列字段,以“第一季度”、“第二季度”、“第三季度”和“第四季度”为求和项,从SHEET2工作表的B14单元格起,建立数据透视表
4.图表的运用以SHEET3工作表中的表格内容,在SHEET3工作表中插入创建一个折线图堆积折线图图表设置图表标题格式为宋体,字号为12数值轴格式为宋体,字号为8;分类轴格式为宋体,字号为10;图例格式为宋体,字号为10;数据标志格式全部定义为宋体,字号为8;为图例框增加阴影
5.数据、文档的修订与保护将SHEET4工作表表格中的“HP”替换为“惠普”,并保护工作表内容,密码为“gks4-18”
4.19第19题【操作要求】
1.表格的环境设置与修改插入SHEET4工作表,并移至SHEET3工作表之后将SHEETl工作表命名为“工资表”,在基本工资后插入“效益工资”一列,按样文输入相应数据设置表格的标题行行高为
252.表格格式的编排与修改计算SHEETl工作表的表格中各行的实发工资与各列的总计并填入相应单元格中将SHEET1工作表的表格中的标题设为跨列居中,隶书,字号为20,将表格的表头行与第一列设置为楷体,字号为l2,加粗,居中为表格添加淡黄色底纹,靛蓝色字体将表格中的数据设为货币样式
3.数据的管理与分析在SHEET2工作表的表格中自动筛选出“预计招收”小于等于500,“实际招收”大于300的值;利用条件格式将SHEET2工作表的表格等于500的数据设置为绿色底纹
4.图表的运用以SHEET3工作表中的表格内容,在SHEET3工作表中插入创建一个面积图三维堆积面积图图表;设置图表标题格式为宋体,字号为12数值轴格式为宋体,字号为8;分类轴格式为宋体,字号为10;图例格式为宋体,字号为
125.数据、文档的修订与保护保护工作薄的结构,密码为“gks4-19”
4.20第20题【操作要求】
1.表格的环境设置与修改将SHEETl工作表重命名为“电影产量表“在“电影产量表“工作表表格中的标题行后插入一空行;合并B
3、B4单元格,合并H
3、H4单元格,将其中内容居中,垂直居中;设置表格标题行行高为25;
2.表格格式的编排与修改计算SHEETl工作表表格的各列平均值和各行总合,分别未填入到“平均年产量”一行与“中年总产量”一列中的单元格中;将SHEETl工作表表格中的标题设为跨列居中,楷体,字号为20将表格的表头行设置为楷体,字号为12,加粗,居中;将数据设为无小数点位将表格的标题行添加蓝色底纹,标题字体为绿色其余为深黄色底纹,深红色字体
3.数据的管理与分析使用SHEET2工作表中的表格中的内容,在“各部门人员平均工资表”中进行平均值计算并利用条件格式将表格中“工资”大于1500的数据设置为蓝色字体
4.图表的运用以SHEET3工作表中的表格内容,在SHEET3工作表中插入创建一个圆锥图条形圆锥图图表设置图表标题格式为宋体,字号为12;数值轴格式为宋体,字号为8;分类轴格式为宋体,字号为8;图例格式为宋体,号为
105.数据、文档的修订与保护在SHEET4作表中进行突出显示修订,时间为起始日期,修订人为每个人,在屏幕上突出显示修订将“电子设备”替换为“产品”
5.2第2题【操作要求】
1.数组的应用处理“工业总产值”工作表中的数据数组求和利用数组创建公式,统计“工业总产值”一列的值数据的检索使用数组检索数据,计算出1992年的“工业总产值”、“国有工业”、“集体企业”、“城乡个体企业”各项的值
2.创建图表在Sheet2工作表中创建一个数据点折线图,使用工业总产值工作表的数据,显示系列仅保留“工业总产值”一项,分类X)轴标志为“1952~1993年”;将图表标题设为“工业总产值”,分类X轴为“年份”,数值Y轴为“产值”;重命名工作表为“工业总产值图”
3.添加趋势线在“工业总产值图”工作表中添加线性趋势线5.4第4题【操作要求】
1.运用数组创建公式运用数组为“东部”、“西部”、“北部”3行及“上半年销售量”一列单元格设置求和公式格式;
2.工作簿的链接同时打开ksml3\ks5-4a.xls工作簿和ksml3\ks5-4b.xls工作簿,使用单元格的绝对引用,将数据链接到ks5-4工作表的就位置
3.数据审核运用审核的“追踪引用活动单元格”工具,验证“西部”的“一月”和“上半年”一列数组公式设置的正确性5.5第5题【操作要求】
1.公式的运用利用函数PMT计算出“贷款计算表1”中的“应付款”
2.双变量分析运用模拟运算表,分析并计算出SheeT2工作表中“贷款计算表2”为6年期时,“应付款”随“本金”和“利率”的变化而相应变动的结果
3.格式设置设置Sheet2工作表“贷款计算表2”中计算结果单元格的数字格式为货币、小数点后保留两位数5.5第7题【操作要求】
1、定义单元格名称打开KSML3\KS5-7AXLS工作簿,在“货物运输”工作表中,定义单元格B6L10范围的名称为”运距1”,保存到考生文件夹中,命名为A5-A.XLS打开KSML3\KSMB.XLS工作簿,在“旅客运输”工作表中,定义单元格B6L9范围的名称为“运距2”,保存到考生文件夹中,命名为A5-B.xls
2、工作簿的链接将A5-A.XLS和A5-B.XLS工作簿己定义单元格区域“运距1”、“运距2”中的数据进行平均值合并计算,结果链接到A5XLS工作簿SHEETl工作表的相应位置
3、创建图表创建一数据点折线图,使用SHEETl工作表的数据,显示系列仅保留“铁路”和“公路”两项标题中图表标题为“85年至95年铁路和公路平均运距图”将图表放在“运距图”新工作表中
4、添加趋势线在“运距图“工作表中添加相应的线性趋势线
5.11第11题【操作要求】
1.获取外部数据新建数据源新建一个名为TREE的数据源,连接数据库文件KSMLl\TREE.DBF执行查询编辑查询数据源TREE,设置过滤列为“总面积_2”,条件为“大于或等于3”,按照主要关键字“总面积_1”“升序”排列,将所得的结果返回到A
5.XLS中
2.创建图表使用Sheet1工作表的表格中“国家”和”1981-1985年森林消失面积“一栏中“总面积“两列的内容,新建一个簇状柱形图
3.从外部导入图片在新建图表中插入KSMLM\TREE.BMP图片,作为图表中的柱形填充效果5.12第12题【操作要求
11.公式及数组的应用按照公式“资金利税率%=利税总额/固定资产平均余额+流动资产平均余额”运用数组的功能计算出表中资金利税率的值,并以百分比的格式表示,将结果在考生文件夹下另存为A5-A.XLS
2.单变量求解按照表中资金利税率的值作为目标单元格的值,依次求出表1中所有空白单元格的值
3.利用图表标志单变量求解选定表-1中行业、资金利税率两列内容,新建一幅簇状柱形图在图中将“外商投资经济资金利税率”,改为
8.01%从而更改表-1中“固定资产平均余额”相应的值为
1580.17将“股份制经济资金利税率”,改为
7.49%,从而更改表-l中“利税总额”相应的值为
145.
604.数据格式的转化将表中”金利税率”一列的值改为数据常量的百分比格式显示
5.13第13题【操作要求】
1.数据的有效性及审核将对外借款一列数据的有效性设定为500~5000之间的任意整数,圈释出无效数据,并把无效数的字体设为红色
2.数组的应用利用数组求出各国利用外资的“总计”
3.数据的链接将亚洲各国利用外资的总计值与“各国利用外资总额”相链接
4.模拟运算表的运用运用模拟运算表求出各国利用外资的总额在亚洲总额中所占的百分比值,计算结果设置为3位小数
5.数据格式的转化将以上运用模拟运算表所得的结果转化为常量
5.17第17题【操作要求】l.创建图表选取Sheet1中适当的数据新建一张“彩色堆积图”,名为A5A
2.设定图表的格式添加标题“中国主要农作物产量”;格式为白色38号隶书,定义分类轴和数值轴的文字为20号斜体,图例中的文字为28号“幼圆体”
3.修改图表中的数据将1992年的玉米产值的图表标志从
95.38改为50~55之间的任一值,并以常规20号字体格式在图中相应位置显示出来,从而改变工作表中的数据
4.为图表添加外部数据从Sheet2中添加1994,1995年各农作物的产值到图表中5.19第19题【操作要求】
1.创建图表根据Sheetl中“产量”和“消费”两行数据,新建一幅数据点折线图,标题为“能源产量与消费量”
2.图形格式的修定为图表中“产量”的折线添加数据标志的显示值,将折线的线形和数据标记包括前景和背景的颜色都改为黄色,再把此线形格式设为最粗
3.添加趋势线选择“消费”数据系列,添加一条线性趋势线
4.图表格式的传递打开工作表Sheet2,根据Sheetl中1991年各种能源消费量嵌入一幅三维饼图将Sheet2表中原图表中的格式传递到新建的三维饼图中,将三维饼图的标题改为“1991年能源消费量”并将图中饼形第一扇区起始角度设为200度
7.1第1题【操作要求】打开考生目录中的A
7.DOC,按要求进行如下操作1文档中插入声音文件在文件的末尾插入声音文件KSML3\KSWAV7-
1.SHS,替换图标KSML3\KSICO7-
1.IC0,设置对象格式大小宽2cm高3cm,位置浮于文字上方
2.文档中插入水印按样文7-lA所示,在当前文档中创建水印,图片存放在KSML3\KSBMP7-
1.BMP中,按如下要求处理对象格式,无环绕、置水平Ocm、垂直
1.04cm、大小高
21.46cm、宽
14.64cmo3从数据源插入外部数据在当前文档段落下方插入数据图表,先将数据表KSMLl\KSSJB7-
1.XLS生成三维簇状柱形图,再以Execl对象的形式粘贴至当前文档的第二页中,要求第二页中不能有水印
7.2第2题【操作要求】打开考生目录中的A
7.DOC,按要求进行如下操作
1.文档中插入声音文件在文件的末尾插入声音文件KSMU\KSWAV7-
2.WAV,替换图标KSML3\KSIC07-2ICO,设置对象格式大小宽2cm;高
2.91cm,位置浮于文字上方
2.文档中插入水印在当前文档中创建水印,图片存放在KSML3\KSBMP7-
2.BMP中,按如下要求外理对象格式,文字无环绕、位置水平Ocm、垂直
1.64cm、大小高
21.46cm、宽
14.64cm;3从数据源插入外部数据先将文档表格KSML\KSSJB7-
2.DOC转换成数据表格,生成如样文73B所示的百分比堆积条形圆柱图,再将文档表格以Excel工作表的形式插入第二页中,要求第二页中不能有水印
7.5第5题【操作要求】打开考生目录中的A
7.DOC,按要求进行如下操作
1.文档中插入声音文件在文件的末尾插入声音文件KSML3\,图标存放在KSML3\KSIC07-
5.ICO设置对象格式为,大小宽
2、高32cm,位置浮于文字上方,垂直
2.13cm度量依据段落,环绕方式上下
2.文档中插入水印在当前文档中创建水印,图片存放在KSML3\KSBMP7-
5.BMP中,按如下要求外理对象格式,文字无环绕、位置垂直
1.06cm、大小高
21.46cm、宽
14.56cm
3.从数据源插入外部数据按样文7-5B在当前文档段落下方插入数据表先将文档表格KSMLlkSSJB7-
5.DOC转换成数据表格,生成如样文所示的堆积面积图,再以EXCEL工作表对象形式插入当前文档的第二页中,在第二页中不能有背景水印720第20题【操作要求】在电子表格软件中打开考生目录下的文件A
7.XLS
1.录制宏在工作表sheetl中完成宏的录制定义宏命名宏为“KSMCR20”,创建快捷键“Ctrl+a”把宏保存在“当前工作簿中”设置字体格式用单元格的绝对引用录制,将标题设置为字体隶书字号18号字形加粗设置单元格格式设置“总计”和“合计”两栏的单元格,数值的小数点后位数为0,使用千位分隔符底纹浅黄色管理数据用单元格的相对引用录制,运用数组,分别计算出“总计”和“合计”一栏的结果
2.运行宏在工作表SHEET2中,运用快捷键来运行宏KSMCR20使其自动完成频繁执行的任务范例
一、制作目录打开文件制作目录主要知识点视图设置大纲视图样式设置目录制作范例
二、Word文档中链接插入Excle表格打开文件在Word文档中链接插入Excel表格主要知识点表格插入方式复制以对象方式插入表格粘贴链接数据刷新范例
三、制作与打印工资表(宏的应用)打开文件制作与打印工资表主要知识点公式的输入与复制数据有效性电子表格的安全性设置录制宏命令、编辑宏和VBA完成自动化操作CellsSelection.RowSelection.Column.SelectRangeSelectionSelection.EndxlToRight.SelectSelection.CopyActiveCell.Offset
20.Range“A1”.SelectDoUntilActiveCell=““Selection.InsertShift:=xlDownRangeSelectionSelection.EndxlToRight.SelectSelection.CopyActiveCell.Offset
20.Range“A1”.SelectLoopApplication.CutCopyMode=False范例
四、产品销售分析表要求在已有的销售状况明细表中,统计每个季度的销售情况打开文件产品销售分析表主要知识点认识数据透视表结构创建数据透视表使用数据透视表分析数据将分析结果发布到因特网范例
五、Web行情信息分析表打开文件Web行情信息分析表主要知识点使用Web查询导入因特网数据工作表数据的动态刷新范例
六、方案管理系统(详细分析过程)打开文件方案的管理;主要知识点使用规划求解实现最优化分析方案管理器的使用合并并比较方案结果利用MicrosoftExcel不仅可以在工作表中存储数据、进行数据的计算、分析数据,还可以实现数据的预测和最优化管理某公司每个季度要制定本季度的生产计划,对每月中预计生产的产品数量、生产成本、日常损耗、广告费、售后服务及收益做出预算现在公司决定从今年第一季度起要增加对用户的售后服务费用,在总收益基本不变的前提下,生产产品的成本和最大生产数量不可变,只有重新规划日常损耗和广告费两项支出,但这两项支出金额又有一定的条件限制每月的日常损耗最少为该月生产成本的
0.5%,每月广告费用最低为2000元现在要为公司在第一季度进行预算,要求分别就”减少日常损耗”、”减少广告费”和”同时减少日常损耗和广告费”3种情况做出最优的预算并进行比较需要用到Excel另两项功能,即规划求解和l方案管理器
1.规划求解“规划求解”是一组命令的组成部分,这些命令有时也称作假设分析,即通过更改单元格中的值来查看这些更改对工作表中公式结果的影响,例如,在本章案例中,更改日常损耗费用和广告费可以在总收益不变的情况下相应增加售后服务费用借助”规划求解”工具,可以求得工作表上某个单元格即目标单元格中公式的最优值”规划求解”将对直接或问接与目标单元格中公式相关联的一组单元格中的数值进行调整最终在目标单元格公式中求得期望的结果”规划求解”通过调整所指定的可更改的单元格即可变单元格中的值,从目标单元格公式中求得所需的结果在创建模型过程中,可以对”规划求解”模型中的可变单元格数值应用约束条件,而且约束条件可以引用其他影响目标单元格公式的单元格
2.方案管理器读者可能曾遇到过这样的情况很想知道改变工作表中的一个或几个参数对整个工作表的结果会有什么影响利用Excel2003提供的方案管理器,可以模拟为达到目标而选择的不同方式对于每个变量改变的结果都被称之为一个方案,根据多个方案的对比分析,可以考查不同方案的优劣,从中选择最合适公司目标的方案利用本例的方法,无需分别创建3个工作表,Excel的”方案”允许用户在一张工作表中存放这些值,并在任意的时刻将其应用到工作表中一个方案是对应于特定工作表单元格的一组值,通过在方案而不是在单元格中保存这些值,用户可以实现以下操作修改工作表中单元格的值随后可以从方案中调回已经存储的值定义若干个方案每个方案存放一组单元格的值而后采用不同的方案来观察其对相应的公式所产生的影响一次使用一个方案当需要向他人展示每个方案所产生的影响时时,这种方式将非常合适制作步骤在制作过程中,首先使用”规划求解”的方法来求得3种预算方案的结果,再利用”方案管理器”对这3种方案进行比较在制作本案例之前,需要先来了解案例的实际需求见方案管理原始记录工作表;
1.预算过程中的问题和需求第一季度投资收益预算 一月二月三月合计产品数量42004500460013300生产成本¥
252000.00¥
270000.00¥
276000.00¥
798000.00日常损耗¥
2500.00¥
2800.00¥
3300.00¥
8600.00广告费¥
5000.00¥
5200.00¥
6000.00¥
16200.00售后服务¥
2500.00¥
2500.00¥
2500.00¥
7500.00收益¥
95000.00¥
102000.00¥
103200.00¥
300200.00产品成本¥
60.00出厂价¥
85.00在该预算表中,产品的成本价和出厂价是不可变的,而每个月的产品数量也是根据订单得出的结果,不能改变生产成本是根据产品成本和产品数量计算出来的,例如一月份的生产成本为”=C4*C11”,即“产品数量”乘以”产品成本”而一月份的”收益”计算公式为”=C42*Cl2-C5-C6-C-C8”即”产品数量”乘以”出厂价”减去”生产成本”、”日常损耗”、”广告费”、”售后服务”现在的问题是,如果希望能够调整”售后服务”的费用由原来的每月2500元增加到每月4000元,那么每月的”日常损耗”、”广告费”如何变化才可以保证第一季度的总收益最大经过讨论,有3种方法可以实施:方法l提高售后服务费用,单方面降低日常损耗但是在实际的生产过程中,每月的日常损耗额不少于该月产品成本的
0.5%每季度总损耗额可以不超过该季度产品总成本的
0.8%;方法2:提高售后服务费用,单方而降低广告费但是在实际的生产过程中,每月的广告费用不少于2000元,每季度广告费用可以不超过8000元;方法3:提高售后服务费用,同时降低日常损耗和广告费但是在实际的生产过程中,每月的日常损耗额不少于该月产品成本的
0.5%每季度总损耗额可以不超过该季度产品总成本的
0.8%:每月的广告费用不少于2000元每季度广告费用可以不超过8000元;这3种方法哪一种是最佳的方案呢?并且应用每一种方法的最终收益又是多少了?相信这样的预算如果通过手工计算的方法,是非常繁琐的
2.开启规划求解加载宏“规划求解”虽然是Excel2003提供的工具,但并不在菜单中显示,它存在于”加载宏”中执行【工具】菜单中的【加载宏】命令,在弹出的”加载宏”对话框中选择”规划求解”复选框,如图所示单击【确定】按钮;单击【工具】菜单,单击【规划求解】命令,
3.单方面降低日常损耗的规划求解利用”规划求解”工具来计算提高售后服务费用并单方面降低日常损耗的收益状况1执行菜单栏中的【工具】|【规划求解】命令打开”规划求解参数”对话框如图所示2在”设置目标单元格”对话框中键入”总收益”所在单元格引用”$F$8”作为目标单元格为使目标单元格中的数据即总收益保持最大,单击”最大值”单选框3在”可变单元格”编辑框中输入每个可变单元格的引用或名称,并用”逗号”分隔不相邻单元格的引用”可变单元格”必须直接或间接与目标单元格相联系在本例的第l种方案中,为了达到增加”售后服务费”并保持”总收益”不变的目标,要改变”日常损耗”值,所以把”日常损耗”和”售后服务”单元格均作为”可变单元格”,因此在该编辑框中输入”$C$5:$E$5$C$7:$E$7”4添加约束条件约束条件是”规划求解”中所设置的限制条件,可以将约束条件应用于可变单元格、目标单元格或与可变单元格或目标单元格直接或间接相关的单元格单击”规划求解参数”对话框中的【添加】按钮,打开”添加约束”对话框,如图所示添加约束的方法是在”单元格引用位置”编辑框中输入单元格引用,或单击编辑框右侧按钮,直接在工作表中单击要设置约束条件的单元格在中间的下拉列表的右箭头中选择运算符在”约束值”编辑框中键入数值或对单元格的引用第l种方案要求是对”日常损耗”的限制条件是每月的日常损耗额不少于该月产品成本的
0.5%,每季度总损耗额不超过该季度产品总成本的
0.8%;对”售后服务”的限制条件是将每月售后服务费用增加到4000元;所以约束条件为$C$5=$C$4*
0.5%$D$5=$D$4*
0.5%$E$5=$E$4*05%$F$5=$F$4*
0.8%$C$7=4000$D$7=4000$E$7=40005将上述约束条件依次添加到”约束”列表后,规划求解参数已设置完成,如图所示6单击【求解】按钮,使Excel2003按设定值开始进行求解过程求解完成后将打开”规划求解结果”对话框,如图所示7单击【保存方案】按钮,对话框中输入方案名称”方案1”,单击【确定】按钮返回到”规划求解结果“对话框中8除了将求解的结果保存为方案外,还可以在当前工作簿中生成显示求解结果的报告;在“规划求解结果“对话框右侧的列表框中有3种类型的报告运算结果报告在报告中列出用户设置的目标单元格和可变单元格以及它们的初始值、最终结果、约束条件的信息敏感性报告在报告中显示了关于求解结果对于一些微小变化的敏感信息对非线性模型,此报告提供缩减梯度和拉格朗日乘数极限值报告在报告中列出目标单元格和可变单元格以及它们的数值、上下限和目标值将3种报告同时选中,并且为了保持原始表不变,单击【恢复为原值】选择框最后单击【确定】按钮,工作簿中自动生成3个工作表,分别以”运算结果报告1”、”敏感性报告1”和”极限值报告1”;
4.单方面降低广告费的规划求解利用”规划求解”工具,来计算提高售后服务费用并单方而降低广告费的收益状况1执行【工具】菜单中的【规划求解】命令,打开”规划求解参数”对话框,单击【全部重置】按钮,清空之前步骤设置的条件2在”设置目标单元格”对话框中键入”总收益”所在单元格引用”$F$8”为使目标单元格中的数据即总收益保持最大,单击”最大值”选项3在”可变单元格”编辑框中输入“$C$6:$E$7”4添加约束条件对”广告费”的限制条件是每月的广告费用不少于2000元,每季度广告费用不超过8000元对”售后服务”的限制条件是将每月售后服务费用增加到4000元;5在输入约束条件时,如果单元格的约束条件相同,可以将所有要添加该约束条件的单元各选中,然后设置条件即可所以,单击”规划求解参数”对话框中的【添加】按钮,打开”添加约束”对话框,在约束条件列表中依次输入约束条件为$C$6:$E$6=2000$C$7:$E$7=4000$F$6=80006单击【求解】按钮,使Excel2003按设定置开始进行求解过程求解完成后将打开”规划求解结果”对话框7单击【保存方案】按钮,在”保存方案”对话框中输入方案名称”方案2”,单击【确定】返回到”规划求解结果”对话框中8将3种报告同时选中,并且为了保持原始表不变,单击”恢复为原值”;选择框最后单击【确定】,工作簿中自动生成3个工作表,分别以”运算结果报告2”、”敏感性报告2”和”极限值报告2”;
5.同时降低日常耗损和广告费的规划求解再利用”规划求解”工具,来计算提高售后服务费用,并同时降低日常耗损和广告费的收益状况1执行【工具】菜单中的【规划求解】命令,打开”规划求解参数”对话框,单击【全部重置】按钮,清空之前步骤设置的条件2在”设置目标单元格”对话框中键入”总收益”所在单元格引用”$F$8”;为使目标单元格中的数据即总收益保持最大,单击”最大值”选项3在”可变单元格”编辑框中输入”$C$5:$E$7”4单击”规划求解参数”对话框中的【添加】按钮,打开”添加约束”对话框,添加如下的约束条件$C$5=$C$4*
0.5%$D$5=$D$4*05%$E$5=$E$4*05%$F$5=$F$4*
0.8%$C$6:$E$6=2000$F$6=8000$C$7:$E$7=40005单击【求解】按钮,使Excel2003按设定置开始进行求解过程求解完成后将打开”规划求解结果”对话框6单击【保存方案】按钮,在”保存方案”对话框中输入方案名称”方案3”,单击【确定】返回到”规划求解结果”对话框中7将3种报告同时选中,并且为了保持原始表不变,单击”恢复为原值”选择框最后单击【确定】,工作簿中自动生成3个工作表,分别以”运算结果报告3”、”敏感性报告3”和”极限值报告3”;
6.使用方案管理器进行方案比较在Excel2003中,方案可以很好地表现数量关系对用”模拟运算表”、”规划求解”、”变量求解”等工具生成的预测结果,可以利用方案管理器进行管理在”原始记录”工作表中,执行【工具】菜单中的【方案】命令,打开”方案管理器”对话框;在”方案管理器”对话框中列出了己创建的方案名称、当前方案中的可变单元格和该方案的备注信息提示:创建方案有多种方法,可以在进行规划求解后将当前数据保存为一个方案,也可以在”方案管理器”中单击【添加】按钮,在打开的”添加方案”对话框中输入方案名称、可变单元格及各注内容使用方案的最终目的是要对各个方案进行分析对比和评价虽然可以在同一张工作表中显示方案结果并逐个进行分析,但对较复杂的方案来说,最好的方法是将所有方案集中到一个工作表中并生成总结报告,使用户可以方便地对照多个方案在”方案管理器”对话框中单击【摘要】按钮,打开”方案摘要”对话框,其中有两个选项:”方案摘要”和”方案数据透视表”选择”方案摘要”选项,在”结果单元格”文本框中输入目标单元格所在位置即$F$8,再单击【确定】按钮,生成名为”方案摘要”的工作表,创建的”方案摘要”工作表左侧和上部的灰色区域中有分级的符号”+”和”-”,单击”+”号可显示”方案摘要”的详细内容,单击”-”号则将部分内容隐藏起来这样,根据各方案的数据计算出的目标值显示在”总收益”单元格中,便于管理人员比较分析比较3个方案的结果单元格”总收益”的数值,可以看出”方案3”效果最好,”方案2”次之,”方案1”对目标值的影响最小
7.实例总结在本章的实例中介绍了”规划求解”的使用方法,利用”规划求解”来完成变量对结果影响的分析,然后使用”方案管理器”对不同的方案进行管理和比较,从而找到最优的方案其实,在MicrosoftExcel2003中,除了规划求解外还提供了其他假设分析的方法,如模拟运算表和变量求解所谓模拟运算表实际上是工作表中的一个单元格区域,它可以显示一个计算公式中某些参数的值的变化对计算结果的影响模拟运算表为同时求解某一运算中所有可能的变化值的组合提供了捷径,由于它可以将所有不同的计算结果以列表方式同时显示出来,因而便于查看、比较和分析单变量求解实际上是已知单个公式的预期结果,并且用于确定此公式结果的一个输入值未知,然后通过公式求得这个未知的输入值通过这些工具的使用可以使管理决策数量化、科学化,提高管理水平
8.如何删除工作表中的方案如果需要将工作表中的某个方案删除,可以执行【工具】菜单中的【方案】命令,打开方案管理器,在”方案”列表中选中需要删除的方案,然后单击【删除】按钮。