注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

快乐老头的博客

已是青年老人啦!哈哈!哈哈!

 
 
 

日志

 
 
关于我

身体健康快乐.学到老.活到老.快乐自己 退休快十年了,在家无事可做,每天上电脑玩。有时也帮别人用“Excel”给做些统计表,退体后的时间里约做过100多种统计表。我做这些统计表的目的是让别人的工作更轻松,只求别人满意,从不收取一分钱。我从来就把别人让我做的事看成是一种乐趣,没事干反而难受。

网易考拉推荐

Excel办公实用操作技术(5)  

2009-02-19 14:30:46|  分类: Excel办公实用操 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

 

第五章  Excel数据库管理

一、单变量求解

假如我们存入银行1000元钱,按照年利率0.0225元计算,10年后的本息合计为1249.403元,如果在10年后要将本息合计变为2000元,其利率应是多少?

第一步我们先做一张表,分别在“A1、B1、C1”单元格中输入“可变值、现值、目标值”,并在“A2、B2”单元格中输入“0.0225、1000”在C2单元格中输入公式“=B2*(1+A2)^10”(如E5-01:单变量求解1);

Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客

第二步将光标放在C2单元格,自菜单栏选:工具→单变量求解→出现“单变量求解”对话框后,在“目标单元格”中输入“C2”、在“目标值”中输入“2000”、在“可变单元格”中输入“$A$2”(如E5-02:单变量求解2)→确定。

Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客

这时便出现“单变量求解状态”对话框(如图5-03),再次点击确定后可知,如果要10年期间将1000元的本利合变为2000元,其利率应当是0.0718(见E5-03:单变量求解3)中的“A2”单元格。

Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客

二、模拟运算表

以还贷为例。假定购房时,用政策性贷款20万元,用商业性贷款40万元。打算用10年期还清全部贷款,每月还贷款的本利是多少?

贷款利率见下表:

Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客

计算方法分三步进行。

第一步:在Excel中做一张工作表,将上面的基本数输入工作表中,同时输入计算公式(如E5-04:模拟运算表1)。

Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客

在E5-04中已将所有计算条件输入到了工作中。从图中可见B2单元格的公式=PMT(X1,120,Y1)。

PMT是基于固定利率及等额分期付款方式,返回投资或贷款的每期付款额函数。公式中的X1、Y1是函数的设置条件,120是10年的月数。

第二步:选工作表的B2到D4(将其涂黑)。自菜单栏选:数据→模拟运算表→出现“模拟运算表”对话框(如E5-05:模拟运算表2)后,在“输入引用行的单元格”文字框中输入X1,在“输入引用列的单元格”文字框中输入Y1,用鼠标左键点击确定,在工作表出现计算结果(如E5-06:模拟运算表3)。

Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客

第三步:计算每月的还贷金额。E5-06的工作表中,C4单元格的“-4162.9118”和D3单元格的“-2178.4622”两个数,因是过路的无效数,可不考虑。将C3单元格中政策性还贷款金额-2081.4559和D4单元格中商业性还贷金额-4356.9244两个数相加,约等于-6438.3803。每月应还贷6438.4元。

E5-06所示的工作表,如因贷款数额或利率发生改变,只要重新输入改变后的数额或利率,即可自动重新计算出新的还贷金额。

三、还贷金额速查表

随着住房制度的改革,住房已全面货币化,购房的住户也越来越普遍,但对于工薪族来说,采用贷款方式,花明天的钱住今天的房,更是平常的事。现用Excel制作一张(E4-07:住房贷款还贷金额速查表),帮助大家随时掌握还贷金额情况。Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客

 

由于贷款利率是分5年及以下、5年以上两段,对分别在B列计算1~5年段利率;C列6~30年段利率。

1、设置贷款性质变换单元格

在A2设置贷款性质变换单元格。光标进入A2单元格,自菜单栏点击“数据→有效性”出现“数据有效性”对话框后,选“设置→有效条件”,在“允许”条幅式菜单中选“序列”,在“来源”的引用区域,直接写入用半角“,”号隔开的“公积金贷款金额,商业性贷款金额”后(见E4-08:数据有效性菜单1),确定退出。当光标进入A2单元格时,便可点击右边的Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客按钮,从下拉列表中的两字段中选择输入。

Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客2、设置滚动条

在B2、B4及C4单元格中分别 “滚动条”。以B2单元格中设置贷款金额为例:

(1)在菜单栏任意位置的右键菜单上,点选“窗体”,并自“窗体”菜单上点击滚动条Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客按钮,再进入C2单元格(按钮可放置在任何一个单元格中),画出并调整好按钮的大小;

(2)在按钮的右键菜单上,点击“设置控件格式”并点选“控制”,出现“设置控件格式”如(E4-09:设置控制条件对话框)后,将光标进入“单元格链接”右边的文字框中,然后点击工作表上的B2单元格,完成控件与单元格的链接;

Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客

(3)将“当前值”设为2000、最小值设为100、“最大值”设为3000,“步长值”设为100、“页步长”设为10,确定退出。

B2单元格“设置控件格式”的方法同C2单元格,只是要将“当前值”不小于“1”、“最小值”设“1”、步长”设为“1”、“最大值”设为“5”。

B4单元格“设置控件格式”的方法同C2单元格,只是要将“当前值”不小于“6”、“最小值”设“6”、步长”设为“1”、“最大值”设为“30”。

3、设置贷款序列及公式

首先说明一下,序列及公式中的标点符号,必须是半角的。

(1)输入利率公式:

在B5单元格中输入公式:“=IF($A$2="公积金贷款金额",3.6%,4.77%)”;

在C5单元格中输入公式:“=IF($A$2="公积金贷款金额",4.05%,5.04%)”。

(2)输入月偿还贷金额公式:

在B6单元格中输入公式:“=PMT(B5/12,B4*12,$B$2)*100”;

在C6单元格中输入公式:“=PMT(C5/12,C4*12,$B$2)*100”。

(3)输入累计还款金额公式:

在B7单元格中输入公式:“=B4*12*B6”;

在C7单元格中输入公式:“=C4*12*C6”。

至此,“住房贷款还贷金额速查表”全部做完。从E4-07图中可以看出,300000元的商业性贷款金额,在20年还清,月偿还金额-1986.50元,累计还款金额-476760.55元。

需要说明的是,还贷金额的变量数是副数,如果在公式的函数前加上一个“-”号,将B6单元格的公式变为“=-PMT(B5/12,B4*12,$B$2)*100”时,其变量数就成为正数了。原来副副得正。

有关B2单元格的“百元”格式及B6、B7、C6、C7单元的“元”格式的设置方法,请参阅“第二章、十四”中的有关内容。

四、数据筛选

数据筛选,是从工作表单元格区域的项目记录中,按所选择的条件集中显示其相同内容。方法是先在工作表中选取所有项目标题,然后从菜单栏选:数据→筛选“出现对话框后”选→自动筛选(这是一个复选命令,选用与取消选用都要点击它,以左边有无“P”为标志)→这时在工作表的所有项目栏中均加上一个下三角按钮(如E5-10:自动筛选标志)。当需要对某一个项目进行筛选时,用鼠标左键点击其三角按钮,便出现一个对话框(如E5-11:筛选对话框),再按框内提示选择需要显示的内容,或自定义筛选条件(如E5-12:筛选显示),即可显示所选内容或符合条件的所有记录条。返回时自菜单栏选:数据→筛选“出现对话框后”选→全部显示。

用筛选的办法显示出筛选内容后,再选取显示的内容,并用“快速计算工具”中的“计数”加以配合,可进行一般性统计工作。但在学会设置计算公式以后,这种统计办法就显得有些不足。

关于“高级筛选”,其中涉及的问题较多。有兴趣的同志可自己去研究。

Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客

Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客

五、合并计算

在实际工作中,对几张表项不相同的工作表,可用合并计算的方法进行计算。方法是用鼠标单击菜单栏的“数据→合并计算”,就会出现(如E5-17:合并计算对话框),点击“函数”下面文字框中的下三角,便出现一个有“求和、计数、均值、最大值、最小值、计数值、标准偏差、总体标准偏差、方差、总体方差”的菜单,这就是合并计算的全部可选工作项目。在日常工作中用得最多的是求和。现以求和为例:

求和的合并计算,就是将几张工作表中的相同或不同项目,通过合并计算的设置后,让计算机自动检索,将条件不同的部分单列、将条件相同部分合并,并显示到指定的工作表中去。

如在一个工作簿内分别有:复印纸、文头纸、办公用品三张工作表(如E5-13:复印 纸、E5-14:文头纸、E5-15:办公用品、E5-16:卫生用品),现用合并计算的方法建一张总报表。

Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客

其方法是新建一张总报表,在总报表中确定一个起始单元格(假定A1单元格),再自菜单栏选:数据→合并计算,出现对话框(如E5-17:合并计算对话框),在“函数”菜单中选“求和”。这时可以点击对话框中“引用位置”栏右角的按钮,再点击“复印纸”工作表中起始单元格,按住“Shift”再点击“复印纸”的终止单元格,完成一张工作表的引用;再点击“引用位置”栏右角的按钮,返回到“合并计算”对话框;再选“添加”,并按上述方法完成对其余两张工作表的引用。如E5-17中“所有引用位置”的显示,最后“确定”即完成以上四张工作表的合并计算(如E5-18:合并计算结果)。完成后的总报表,需要重新对他进行必要的格式化操作,让总报表更美观。

Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客

Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客

注:如其中某一工作表的数据有改变时,必须再执行一次合并计算。如其中某张工作表的项目有增减时,也必须改变引用位置再重新合并计算。

六、数据分类汇总

(一)项目排序

进行分类汇总的运算前,首先要对汇总的关键项目进行排序。排序方法有二:

一是用工具栏上的升序或降序Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客按钮排序。方法是在工作表中选定要排序的项目后,用鼠标左键点击升序或降序按钮进行排序;

二是自菜单栏上选:数据→排序→出现的对话框(如E5-19排序对话框),如不改变对话框中的设置要求,点击确定完成排序。这时所做排序是计算机系统所默认的按列排序。

Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客

如有必要按行排序,可自(E5-19:排序对话框)的“选项”中出现的对话框内进行设置。

(E5-20:按部门排序)是一张按“部门名称”排序的《工资表》。

Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客


(二)项目排序种类

1、按对象:可以是“文字”、也可以是“数字”;

2、按序列:可以“递增”、也可以“递减”;

3、按方向:可以“按列排序”、也可以“按行排序”;

4、按方法:可按“字母排序”、也可以按“笔画排序”;

5、按需要:可以整列、整行进行排序,也可以对行、列中某一段进行排序。

对以上“3、4、”的操作均可从E5-19的对话框中点击各选项,并自选项对话框中选择需要的排序方法。

(三)分类汇总

因为分类汇总运算结果会取代原表。故在进行分类汇总操作之前,请先将需要分类汇总的工作表复制一张。

现对上面的《工资表》按“部门名称”进行分类汇总。

1、自菜单栏选:数据→分类汇总→在出现的对话框(如E5-21:分类汇总对话框)中,从“分类字段”中选“部门名称”、从“汇总方式”中选“求和”、用鼠标左键点击“选定汇总项”中的“基本工资、资金、书报费、洗理费、应发工资、所得税、房租、水电、实发工资”,使选项中各项目前的复选框都出现“Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客”,再用鼠标左键点击“替换当前分类汇总”前的复选框,出现“Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客”后确定。其结果如(E5-23:分类汇总表2)的分类汇总表。

Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客

Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客

2、(E5-22:分类汇总表1)是一张按行分类汇总产生的结果表,在图的左上角出现的“1、2、3”字样,其左边又出现有几个“-”号。这时如果点击左上角的“2”,或由下而上的点击“-”号使他都变为“+”,即出现如(E5-23:分类汇总表2)的分类汇总表。

Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客

3、掌握了上述分类汇总的方法后,在实际工作中可根据需要进行多种形式的分类汇总。以统计部门名称及人数的汇总为例。如(E5-24:按部门名称汇总)所示.

 Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客

七、Excel 97数据透视


       (一)从菜单栏选:“数据→数据透视”,出现“数据透视向导—4步骤之1(E5-25:数据透视步骤之1)”;

Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客

(二)从E5-25中选下一步,出现“数据透视向导—4步骤之2(图5-26:数据透视步骤之2)”;

Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客

(三)再从E5-26对话框中选下一步,出现“数据透视向导—4步骤之3(E5-27:数据透视步骤之3)”

Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客

(四)按E5-27所示,先做一张按列计算的透视表,以部门为例,将部门名称拖放到“列”框内,再分别将“基本工资、奖金、书报费、洗理费、应发工资、所得税、房租水电、实发工资”拖到“数据”框内,点击下一步,便出现“数据透视向导—4步骤之4(E5-28:数据透视步骤之4)”

Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客

(五)从E5-28中选择数据透视表显示位置,我们选新建工资表并点击完成,即生成如(E5-29:以行方式显示的数据透视表)所示。

Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客

(六)如果我们需要将透视结果以列方式显示,现以类别名称为例,则按E5-27所示,将类别名称拖到“列”的框内,并将所要透视的数据拖到“数据”框内,进入下一步,进入E5-28,点击完成,即生成(E5-30:以列方式显示的数据透视表)所示的以列透视的数据透视表。 
Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客

八、Excel 2000数据透视表和图表报告

Excel 2000在数据透视方面与Excel 97相比,在功能上有所增强。Excel 2000不仅可以作数据透视表,而且还以可作图表报告。即是作透视表也与Excel 97有许多不同点。本节仅就Excel 2000在数据透视方面作一些说明。

仍以本章E5-20的工资表为例,介绍Excel2000的数据透视方法。
     (一)从E5-20所示的工资表中,选定需要做数据透视的区域,再从菜单栏选:“数据→数据透视表和图表报告”,出现“数据透视表和数据透视图向导—3步骤之1(E5-31:步骤之1)”;在“所需创建的报表类型”下选“数据透视表。

Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客

(二)从E5-31中选“下一步”,出现“数据透视表和数据透视图向导—3步骤之2(E5-32:步骤之2)”的对话框,因“选定区域”的工作在一开始就做了,故“选定区域”的文字框内便自动显示出“$A$3:$M$13”的单元格区域。如果第一步未进行选定区域的设置工作,在此还可以进行(或重新)选定区域的设置工作。

Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客

(三)再从E5-32对话框中选下一步,出现“数据透视表和数据透视图向导—3步骤之3(E5-33:步骤之3)”,按该图在对话框中推荐的“数据透视表显示位置”是“新建工作表”,并有“版式”和“选项”两个主要选项。在这里着重就默认推荐设置方面的问题作以介绍: Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客

1、默认E5-33中的推荐设置,点击完成后出现一个“数据透视表”的对话框(E5-34:步骤之4)。在这个对话框中,除在下方显示出原始工资表中项目外,还在上方增加了“数据透视表”菜单、“设置报告格式”、“图表向导”、“隐藏明细数据”、“显示明细数据”、“更新数据”、“字段设置”和“隐藏字段”等操作按钮。

Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客

2、按E5-34对话框中的提示,假定将“数据透视表”中的“职工名称”拖至“请将列字段拖至此处”位置,并分别将“基本工资、奖金、书报费、洗理费、应发工资、所得税”等项拖至“请将数据项拖至此处”位置,此时生成的数据透视表如(E5-35:步骤之5)所示。

Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客
3、从E5-35的数据透视表中可以看出,“职工名称”单元及“数据”单元的右边都有一个下三角标记,点击这个下三角标记,即可出现一个“职工名称”或“数据”的下拉式菜单,菜单中的右边都有一个复选框,需要显示某个数据时,点击复框让它带上“Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客”,不显示时,点击复框把“Excel办公实用操作技术(5) - 快乐老头 - liangdahuai2008的博客”消去。

 

 

  评论这张
 
阅读(1012)| 评论(1)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017