Excel数据分析

Excel数据分析（精选十篇）

Excel数据分析 篇1

1.一篇语文阅读引发的思考

曾看到一篇阅读课文《北京的空气质量》,其中有这样一段文字:北京市环保局环保监测中心发布2013年北京市空气质量报告,空气质量总体情况为一至六级天数分别为41、135、84、47、45和13天。提出的问题是:请用概括性语言阐述2013年北京的空气质量。大多数学生的答案如下:北京空气质量二级最多、一级很少;雾霾天气很多;全年有13天处于六级天气,对身体非常不好……这些只是阐述数字的表面现象,所有的分析要从“结果”出发,没有结论的数字罗列并不是分析,而“结果”是发现问题和解决问题。如果借助Excel图表,可以对数据进行更加深入的分析,更加直观、形象有效地表达数据信息,并帮助学生迅速掌握数据的发展趋势和分布情况,有利于分析、比较和预测数据。

2.初中信息技术Excel课程理念

《中小学信息技术课程指导纲要》明确指出,课程基本定位是以培养学生的信息素养为主要目标。通过信息技术课程使学生具有获取信息、传输信息、处理信息和应用信息的能力,学生能够使用电子表格对信息进行合理分类、恰当处理和有效分析,得出结论用于解决实际生活中的问题。在初中阶段课程指导纲要中提出,要使学生学会使用与学习和实际生活直接相关的工具和软件;学会应用多媒体工具、相关设备和技术资源来支持其他课程的学习,能够与他人协作或独立解决与课程相关的问题,完成各种任务。数据分析并不是一门复杂的学科,而是一些简单的具有判断力的常识,而复杂的运算通常只会让分析结果更差。数据分析是一门艺术,对同样的数据会有不同的解读。

3.学习者特征分析

初中学生在信息技术Excel课程方面已经基本掌握了在Excel中对数据进行处理的方法,能够运用公式法、 函数法对数据进行计算,初步掌握图表的制作方法,但是对图表类型并不熟悉,不清楚其使用范围。初中学生已经具备一定的逻辑思维能力,学生思维活跃,喜欢动手实践,对图表的应用求知欲强,参与学习的积极性很高,喜欢接受挑战,但他们在自学能力、认知能力、实际操作能力等多方面差异较大,呈现出多层次的特点, 数据分析和表达能力有待提高。

数据分析的方法

1.黄金圆环法则

在教师讲解数据分析时,要思考黄金圆环法则的三个问题:Why——How——What(如图1)。即:信念(Why)的确定与传递,导致行为(How)的变化与改进,影响结果(What)的产生与呈现。这也启发教师, 与其告诉学生做什么,不如引导他们理解为什么要做和怎么做。Excel课程理念已经告诉我们做什么,现在需要解决为什么做、怎么做的问题。

2.Excel图表分析数据的基本流程

(1)设计数据表。从文字中提取有效的信息,在Excel中建立数据表。如《北京的空气质量》一文中,可建立如下数据表。

(2)根据数据内容选择适合的图表类型。图表的选择应该逻辑严谨,可读性强。同样的数据使用不同的图表类型,表达的结果也不尽相同,所以选择图表类型时需要考虑结果的产生与呈现,这也符合“黄金圆环”中 “What”的思想理念。

(3)制作图表。在Excel中制作图表比较简单,可分为“四部曲”:插入图表、选择图表类型、完成图表的版式制作和调整图表。

(4)进行数据分析,撰写结论。根据制作的图表, 解读数据,正确反映数据状况,结论合理,有一定的挖掘深度。结论结果顺畅、合理,信息丰富。

基于Excel图表的数据分析

1.掌握数据分析方法,根据数据背景选择合适的图表

单看数据表,还是难以对数据进行深入分析。在选择图表类型前,应了解常见的图表类型及适用范围。初中生在电视、报纸、杂志上经常看到图表,但是对具体的应用范围不是很了解。Excel支持各种各样的图表,因此可以采用最有意义的方式来显示数据。以Excel2010为例, 当使用图表向导创建图表,或者使用“图表类型”命令更改现有图表时,可以很方便地从标准图表类型或自定义图表类型列表中选择自己所需的类型。Excel中有八大类图形类型,每一类中又分为若干图表,通过鼠标悬停的简单方法可了解每种图表的含义及适用范围(如图2)。

常见的图表类型及其适用范围如表2所示。

2.经历数据的分析过程,体会数据中蕴含的信息

根据《北京的空气质量》数据表,我们最关注的是各级别的分布情况,所以应当选用饼图(如图3)。

从图表中很容易看到各级别的百分比情况,下面就应当撰写结论。许多学生此时依然只是对数字进行简单的描述,没有深入地分析数据,这也就失去了使用图表表达的意义。结论的撰写分为3个层次,要求不同,其阅读者也有所不同。

(1)最基本的层次是基于图表的数字发现,不是罗列图表中已经表示的数字,重点在于找到数据间的差别。从饼图中可以看出,2013年北京空气质量为二级的比例比较高,其次是三级,空气质量为一级的比例只有11%,同时中度和重度污染的比例也超过了10%,证明北京的空气质量不是很好,这仅仅达到了数据分析的最基本层次。

(2)提高层次是每一部分基于数字发现获得的小结,不是简单地将各图表得到的数据间差别汇总在一起,而是将该部分前后的数据串在一起,表明各部分的基本结论。教师需要引导学生深挖数据,再观察数据之间的联系与差异。有的学生会回答空气质量为优良的比例只有48%,不到一半;轻度和中度污染的比例达到了36%;重度和严重污染的天气占到了16%。学生已经将数据间建立了联系,这些数据意味着在2013年北京每两天就有1天的雾霾天气,每6天就会有1天的空气质量非常糟糕,此时学生已经提高一个层次分析数据。

(3)专家层次是在综合考查各部分小结基础上得到的总体结论和建议,关键需要将各部分的小结汇总升华成总体结论,并给出相应的建议。如果教师再提出问题:作为中学生我们该如何保护环境?让学生思考,将分析结果及建议写到结论部分,一份专家层次的数据分析报告就完成了。不仅达到了信息学科的教学目标,并且教会学生思考问题的方法,帮助学生提高语文阅读能力,解决学习方面的很多问题。

3.培养学生表达和分析数据的意识和能力

学生在平时的生活和学习中会遇到各种数据表,虽然有些图表类型学生比较生疏,但是借助于学生平时见到过的图表进行引导,问题也很容易解决。例如,表3是两名同学的期中考试成绩表。

教师在课堂中询问学生谁的学习综合实力更强?此时让学生自主探究,用哪种图表类型可以更好地表达数据。大多数学生选择柱形图,也有学生选择条形图。而柱形图和条形图仅可以分析两名同学各科成绩的构成, 用哪种图表表达综合实力更好呢?教师不要直接给出答案,可以向男生提问:在游戏中有没有见到过表达综合实力的图表?肯定会有男生回答是雷达图,再让学生更改图表类型,如图4所示。

此时教师再和学生达成共识,牛露强学习的综合实力更强。教师再追问:为什么卢天东的平均分高,反而他学习的综合实力不如牛露强?你能给卢天东一些建议吗?答案全从学生口中得出,不仅达成了教学目标,并且很容易突破重难点,课堂气氛活跃,知识点学生也会很容易接受。

除了单一的图表类型,还要让学生学会综合运用图表分析数据。鼓励学生相互讨论、自主探究,以提高分析数据的意识和能力。

4.在Excel图表教学策略上的建议

数据一定要真实,可从现实问题或学生其他学科课本上选取。教师需要提供学习的支架,比如讲授、教材、Office帮助、鼠标悬停等方法。在教学策略上着重体现对学生整个学习过程的引导,教师的演示和分析仅仅是帮助学生在头脑中建立思考的问题链条,学生以自主探究的方式解决学习中遇到的难点问题,从而提升学生分析问题、解决问题的能力。让学生学会使用选择数据源和更改图表类型命令编辑图表,能够根据不同的数据内容使用适合的图表类型表达、分析数据,能够在一张数据表中使用不同的图表类型,提升学生数据分析、表达的能力。

Excel图表在其他学科上的应用

1.利用Excel图表绘制数学函数图像

众所周知,在数学函数表达中,图像能直观、形象反映出变量之间的变化依赖关系及其变化趋势、变化率、 最值与极值。可用于函数图像绘制的工具软件有很多,但是使用Excel图表绘制图像最简单,而且可以绘制出很多软件做不出的幂函数负半轴的图像等。借助Excel控件还可以绘制出动态的函数图像,揭示数学的特殊规律。

2.在物理学科上的应用

物理学科是一门以实验为基础的基础学科,而在实际的课堂教学过程中,由于受到学生能力和课堂上时间的限制,要让学生通过手工计算和画图来从实验数据中归纳出实验结论是比较困难的,所以教师往往会直接给出最终的实验结论,甚至连实验探究的环节也被免掉了。借助Excel强大的表格计算功能和图表功能,在课堂上就能轻松处理实验数据并得出图像,引导学生根据实验数据和图像得出实验结论,同时得出各物理量之间的定量关系。这种方法也可以用在其他实验教学中。在直观地得出各个物理量的关系的同时,也免去了繁杂的手工计算和画图。

3.在化学、生物学科上做数据分析

化学、生物学科也包含了大量的实验。化学学科中的定量分析、生物学科中的统计分析都可以借助Excel图表来完成。学生在学习过程中需要具有准确的“量”的概念(如图5)。

在传统的学习过程中,学生虽然在实验过程中记录了大量的数据,但是还需要通过教师用语言表述或者利用板书展示图表的内容,这种学习方式并不能传递更多的教学信息。利用Excel图表功能解决化学、生物理论学习中的曲线绘制、数据处理、实验标准曲线制作等问题,可使学习取得更好的效果。

通过将信息技术中Excel图表和生活、学习中的数据有机结合起来,大大提高了学生的学习积极性,使得学生认识到问题的探究并不是遥不可及的。在实际教学过程中,由于课堂上用到的数据源于学生生活、学习中的实际数据,这就大大提高了学生对问题进行探究的积极性,同时可以让学生亲自将数据输入到Excel中来得出结论,使得学生能客观地看待自己的研究结果,主动学习,提高效率。

Excel数据分析 篇2

样本数据分布区间、标准差等都是描述样本数据范围及波动大小的统计量，统计标准差需要得到样本均值，计算较为繁琐。这些都是描述样本数据的常用变量，使用Excel数据分析中的“描述统计”即可一次完成。

注：本功能需要使用Excel扩展功能，如果您的Excel尚未安装数据分析，请依次选择“工具”-“加载宏”，在安装光盘中加载“分析数据库”。加载成功后，可以在“工具”下拉菜单中看到“数据分析”选项。

操作步骤

1.打开原始数据表格，制作本实例的原始数据无特殊要求，只要满足行或列中为同一属性数值即可。

2. 选择“工具”-“数据分析”-“描述统计”后，出现属性设置框，依次选择：

输入区域：原始数据区域，可以选中多个行或列，注意选择相应的分组方式;

如果数据有标志，注意勾选“标志位于第一行”;如果输入区域没有标志项，该复选框将被清除，Excel 将在输出表中生成适宜的数据标志;

输出区域可以选择本表、新工作表或是新工作簿;

汇总统计：包括有平均值、标准误差(相对于平均值)、中值、众数、标准偏差、方差、峰值、偏斜度、极差、最小值、最大值、总和、总个数、最大值、最小值和置信度等相关项目，

其中：

中值：排序后位于中间的数据的值;

众数：出现次数最多的值;

峰值：衡量数据分布起伏变化的指标，以正态分布为基准，比其平缓时值为正，反之则为负;

偏斜度：衡量数据峰值偏移的指数，根据峰值在均值左侧或者右侧分别为正值或负值;

极差：最大值与最小值的差。

第K大(小)值：输出表的某一行中包含每个数据区域中的第 k 个最大(小)值。

平均数置信度：数值 95% 可用来计算在显著性水平为 5% 时的平均值置信度。

结果示例如下(本实例演示了双列数据的描述统计结果)： 结果省略!

原始数据文档在这里下载>>操作结果文档在这里下载>>

Excel语音朗读表格数据 篇3

在Excel 2003中

在Excel2003中，我们可以通过“视图/工具栏”，选中“文本到语音”复选框来使用朗读单元格功能。由于这个功能在安装Excel时不是默认的，所以在第一次使用时会提示插入Office安装光盘来安装这个功能。

使用的时候打开“文本到语音”工具栏，数据文件中用鼠标选择要朗读的第一个数据，接着根据数据的排列情况来选择是“按行”还是“按列”来进行朗读，设置完成后点击“朗读单元格”按钮就可以了。

如果选择“按回车开始朗读”按钮，还可以一边输入数据一边来进行语音校对，这样在完成一个单元格的输入后按回车，Excel就会自动来朗读这个单元格中的内容。

在Excel 2007中

Excel2007中也有这个功能，但是不太好找，默认标签或是功能区中都找不到，需要用户自定义设置。下面我们一起来看Excel2007中的语音朗读功能究竟藏在哪里，然后将语音朗读这个功能加在Excel2007的快速启动工具栏中。

鼠标右键单击Excel2007左上角的圆形“Office按钮”，从右键菜单中选择“自定义快速访问工具栏”。在“自定义快速访问工具栏”选项卡中，在“从下列位置选择命令”下拉菜单中选择“不在功能区中的命令”（如图1）。在这个下菜单中我们可以看到“按Enter开始朗读单元格”，双击这个命令，把它加到右边的快速访问工具栏中，最后单击“添加”按钮完成操作（如图2）。

现在我们再回到Excel2007主窗口中，快速启动工具栏已经多了一个按钮（如图3）。保证声卡和音箱或者耳机工作正常，现在我们选择表格中的某一个单元格，按下Enter键即可听见Excel清晰的朗读声音，感觉很神奇吧？这样不论是输入数据时按下Enter键还是直接不断按下Enter键，我们都可以听到Excel清晰的朗读声，输入和检查都很方便。

如果想对朗读的声音做设置，可以打开“控制面板”的“语音识别”，点击“文本到语音转换”，在这里可以选择朗读的声音和速度。Excel朗读的是工作表中当前可见的内容，包括数字、中文、英文等，如果英文是正确单词会朗读单词，如果不是单词则会挨个朗读字母；如果工作表为公式视图，还会朗读公式；如果工作表中的某些单元格数据是隐藏的，则不朗读。

Excel多种数据汇总方法分析 篇4

1 分类汇总

分类汇总是将数据按照某个关键词段分类, 并对关键词段值相同的记录进行汇总的方法。以下面图1“考勤应扣款计算表”为例子, 用分类汇总计算每个部门的基本工资的总和以及扣款合计的总和。

操作如下:先按部门排序 (和排序次序无关, 可以升序排序也可以降序排序) , 然后单击“数据”选项卡的“分类汇总”命令, 在“分类汇总”对话框中选择分类字段为部门, 汇总方式为求和, 汇总项为基本工资和扣款合计, 将得到所需要的汇总结果。

分类汇总的特点:

1) 以三级结构的形式显示出汇总结果和明细数据, 既可只显示汇总数据, 也可以根据需要显示明细数据。

2) 不适合进行多级汇总分析。当分类字段增加或对某一个被汇总字段进行多种不同的汇总时, 需要完成多级分类汇总, 而分级结构的层数增加使得表的结构复杂。如在“考勤应扣款计算表”中计算不同部门、不同职位的基本工资的最大值时或计算不同部门基本工资的最大值和最小值时, 需要做多级分类汇总。

3) 不能直接“剥离”汇总结果。若要复制汇总结果, 简单的操作方式是单击分级显示符号数字2后, 选择汇总结果, 在“定位条件”对话框中选择“可见单元格”, 进行复制粘贴到目标区域。

4) 当数据源需要更新时, 只能在“分类汇总”对话框中选择“删除分类汇总”, 重新完成分类汇总。

2 数据透视表

数据透视表是Excel提供的可用来快速汇总大量数据的工具, 可交互式地进行数据的分析, 被公认为是Excel最强大的数据分析工具。

操作如下:单击目标单元格, 选择“插入”选项卡的“数据透视表”命令, 在“创建数据透视表”命令中选择数据源和数据透视表的位置, 在“数据透视表字段列表”窗格中设置筛选字段、行标签字段、列标签字段、数字字段以及汇总方式。

数据透视表的特点:

1) 运算速度快, 与函数相比, 计算速度提升很快, 可以认为是Excel计算速度最快的功能。

2) 布局结构非常灵活, 修改非常方便。可以用不同的角度对数据进行汇总, 可以对多个汇总字段进行汇总, 也可以对同一个被汇总字段设置不同的汇总方式, 还可以增加计算字段或计算项。

3) 数据透视表的数据源可以是数据列表, 也可以是数据库等外部数据源, 也就是说可以直接根据外部数据源创建数据透视表, 这是其他汇总方式所没有的特点。

4) 数据透视表不能自动更新, 当需要更新时, 需单击数据透视表工具中的“刷新”按钮。

数据透视表功能非常强大, 是用户首选的汇总工具, 但使用时也有一些缺陷。如:

1) Excel 2007开始, 数据透视表创建时不能以多重合并计算数据区域为数据源。若需要使用此项功能, 只能自定义功能区, 自行添加“数据透视表和数据透视图向导”按钮, 并单击此按钮创建数据透视表。

2) 数据透视表对数据透视图的格式有一定的限制。如数据透视图的图表类型不能是XY散点图、股价图及气泡图等, 当对数据透视表进行筛选或刷新时, 数据透视图的有些自定义格式会受到影响。

3 合并计算

合并计算是大家不太熟悉的一个汇总工具。以图1为数据源, 利用合并计算工具计算每个部门的基本工资的总和以及扣款合计项目的总和。

操作如下:先用鼠标单击目标单元格, 再单击“数据”选项卡的“合并计算”按钮, 在“合并计算”对话框中设置参数, 如图2所示, 得到的合并计算的结果如图3所示。

合并计算的特点:

1) 选中“创建指向源数据的链接”复选框, 结果以二级分级结构的形式显示出汇总结果和明细结果, 且计算结果随着数据源的改变自动更新。若没有选中此复选框, 结果将以二维表格的形式显示, 不能显示明细数据, 结果也不能随着数据源的改变自动更新。

2) 合并计算效果有一点类似于一级分类汇总, 但合并计算比分类汇总方便, 不需要排序, 且汇总结果和数据源是分开的。

3) 适合于汇总多个独立的数据列表, 可计算同一工作簿不同工作表或不同工作簿不同工作表中的数据, 前提条件是这些数据所在数据列表具有相同标签, 而且合并计算前工作簿文件需要打开。如汇总12个月每个部门基本工资的总和以及扣款合计的总和, 而每个月的明细数据在不同的工作表内。操作时, 选择目标单元格, 在“合并计算”对话框中将每一张工作表的数据区域添加到引用区域中, 标签位置选择“首行”和“最左列”。

操作时注意, 当创建链接时, 存放合并计算结果的区域不能位于数据源所在的工作表中。

4 模拟运算表 (也称数据表)

模拟运算表是Excel提供的假设分析的数据分析工具, 可以分析模型中参数值的改变对结果的影响。如果要考察一个参数值的改变对结果的影响, 则可以使用单变量模拟运算表。如果考察两个参数值的改变对结果的影响, 则可以使用双变量模拟运算表。在实践中, 也可以用模拟运算表进行数据汇总。

下面以图1为数据源, 用双变量模拟运算表汇总不同部门、不同职位的人数。这时部门可能的值和职位可能的值就是两个可变的参数值, 公式中用数据库函数计算某一个部门某一个职位的人数。

操作如下:在数据列表区域外, 创建数据库函数的条件区域。设条件区域为A18:B19, 其中作为条件值的A19单元格和B19单元格的值各自输入某一部门和某一职位的值, 这两个单元格在模拟运算表中是两个变量参数。选择某一目标单元格A22, 输入数据库函数=DCOUNT (A2:F16, “基本工资”, A18:B19) , 并以A22为交叉点, 在连续的行区域和列区域中输入部门和职位可能的值, 在“模拟运算表”对话框的“输入引用行的单元格”中输入$B$19, “输入引用列的单元格”中输入$A$19, 如图4所示。

若只需汇总每个部门的基本工资的总和以及扣款合计的总和, 则可以利用单变量模拟运算表计算, 公式中可以使用SUMIF函数或DSUM函数。

模拟运算表的特点:1) 模拟运算表需自行设计, 公式和参数的位置以及公式的内容等。结果区域为数组, 不可以单独修改某一个汇总数据。2) 布局上不如数据透视表灵活, 但可以用来描述和分析被汇总字段的值随着汇总参考字段也就是分类字段的值变化的函数关系。当使用数据库函数进行分类统计时, 不需要建立多个不同的条件区域, 只需要一个条件区域即可。3) 数据源改变时, 结果也会自动更新。

5 结论

本文介绍的Excel数据汇总方法, 各自有不同的特点, 适合于不同的情况。即使是数据透视表工具也不是万能的, 也有局限性。因此, 在实践中用户应从实际需求出发, 综合考虑运算速度、布局、数据更新、数据的图形表示等方面的因素, 合理选择最适合的工具, 才能达到最好的效果。

摘要：Excel提供了多种数据汇总方法, 可以对数据列表中所包含的大量数据进行汇总并加以分析, 从而提炼出有助于决策的信息。通过实例, 介绍了几种常用的数据汇总方法的具体应用, 并指出了各自的特点, 可以帮助用户在实践中高效地进行数据分析。

关键词：Excel,数据汇总,数据分析

参考文献

[1]陈景惠.对Excel数据汇总教学方法的探讨[J].硅谷, 2009 (4) :166:167.

[2]王兴德.面向决策的Excel高级数据处理[M].北京:清华大学出版社, 2009.

Excel数据分析 篇5

1. 先在一个空白区域内输入好序列：“初三1班、初三2班、初三3班”，如表中“G3:G5”区域。

2. 选中要输入数据的区域，如选中“A2:A12”。

3. 依次选择“数据→有效性”菜单命令，打开“数据有效性”对话框，

在这里设置有效性条件为“序列”，选中“提供下拉箭头”。并在来源里选择序列区域“G3:G5”，单击“确定”按钮。

4. 把光标放在需要输入数据的单元格内，此时便会有一个下拉箭头，按下“Alt+↓”，在弹出的下拉列表中选择需要的数据，按回车键即可。

怎么样，如果数据很多的话，比逐个输入要方便的多吧，此例仅在说明其用法，希望大家能在实际工作中触类旁通。

三维数据地图 Excel轻松作 篇6

接下来制作所要显示的各个场景。点击“新场景”中的“世界地图”，添加一个新场景。选择这个场景，对图层进行重命名；位置处点“添加字段”，区域处选“城市”，点城市右侧向下箭头，选“城市”；高度处点“添加字段”，区域处选“销售”，点销售右侧向下箭头，选“平均”；类别处点“添加字段”，区域处选“城市”。最后，利用控制地球的按钮来调整数据地球的显示角度（图4）。

主场景设置完成后，设计显示各县区销售情况的分场景。点击“新场景”中的“世界地图”，选择新添加的场景，对图层进行重新命名；位置处点“添加字段”，区域处选“区县”，点击城市右侧向下箭头，选“县市”；高度处点“添加字段”，区域处选“销售”，点击销售右侧向下箭头，选“求和”；类别处点“添加字段”，区域处选“区县”；点击筛选器中的“添加筛选”，区域处选“城市”，勾选所需城市（如唐山）。同样需要调整数据地球的显示角度。

其他城市各县区销售情况的显示，可以点击工具栏上的“新场景”选择“复制 场景3”，设置方法同上，只是在筛选时要选择不同的城市。

Excel数据分析 篇7

在日常生产和生活中,经常会遇到数据的提取和统计工作,比如现代企业集团公司不同时期、地点、品种、销售点的销售量和销售额的统计;比如现代宏观经济总量的GDP、GNP和就业率等的统计;比如高校对教学和科研工作量的统计。看似容易简单的结果其实少不了大量人员的细致的工作和大量基础数据的准备。计算机技术应用和普及应该是几大程度的方便以上的工作要求,而目前基础数据存储的格式或中转数据的格式有相当一部分是以EXCEL格式存在的。这并不是说Microsoft公司的EXCEL产品具有无比的优越性,但EXCEL的广泛应用及普及说明EXCEL的方便性和易用性,例如数据编辑和显示一体化、数据之间可关联性操作、大量基础和专业函数的支持等。对数据一般性的查询和查找在EXCEL来说比较容易,而对于统计工作中的数据分组汇总和同比环比的概念就有一定的不方便性。而有人会说EXCEL本身可以实现和胜任这些工作的,但问题如果一个两个文件进行一些复杂操作是没有问题的,如果要考虑这方面工作的长期性和重复性,就会觉得费时费力。这里需要指出的是同比数值主要是本期值与上一年同期值的比较值,环比是指本期值与上期值的比较值,比较值有差比和百分比,比如说今年销售收入比去年增加或减少了300万元(差比)或者是30%(百分比)。

对于这样的问题,很容易想到的方案会有这么两种,一是采用利用EXCEL现有的强大的数据关联计算和显示功能;二是采用数据库技术,将EXCEL数据导入到ACCESS或其他类型的数据库当中再进行相关功能分析及处理。前一个方法明显不适合大量重复性工作,后一种方法要不断的进行EXCEL数据的导出也不适合大量重复性劳动。下面介绍另外一种方法,就是直接将EXCEL文件作为数据库,并对其进行结构化查询操作,虽然不是很难,但需要一定的用户界面支持。

2 查询分析与实现

EXCEL文件的结构是工作簿(Workbook),工作表(Worksheet),行(Row),列(Column),单元格(Cell),一个EXCEL文件可以看作是一个工作簿,一个工作部里面可以多个工作表,一个工作表又有多行和多列,行与列交叉有相应的单元格。这种结构很容易与数据建立对应关系,一个数据库有多个数据表组成,一个数据表又有相应的属性列和元组(行)组成,行与列交叉部分的单元数据为数据项。也就是说EXCEL文件完全可以看作是一个数据库,只不过没有支持如ACCESS里面的查询或存储过程等大小型数据库还应该有的元件。值得推荐的是Microsoft的数据库引擎的连接语法就包括EXCEL文件,连接后,可以如操作ACCESS数据库引擎一样,进行数据的更新和查询。以下简介连接EXCEL文件进行数据查询方面的操作。

2.1 查询语法与功能需求分析

2.1.1 查询语法分析

对数据库的数据进行查询操作,最灵活和经典的语法便是使用结构化查询语言(SQL)语句。虽然定义为查询语言,SQL语言还包含数据定义语言(DDL),数据操作语句(DML)和数据控制语句(DCL),数据查询语句涵盖在数据操作语言里面。SQL的魅力与核心所在就是其灵活而复杂的SELECT语句,下面是SQL的SELECT语句的一般性语法结构,更复杂的可以参考其他资料[1,2,3]。

SELECT[前导谓词]*|字段列表|字段的计算列表FROM数据表列表[WHERE条件组合][GROUP BY字段列表][ORDER BY字段列表]

其中“[”与“]”表示可选项,“|”表示任选项,WHERE、GROUP BY、ORDER BY分别是查询的条件语句、分组语句和排序语句。通过他们可以实现对数据的条件、分组和排序性的组合式查询。前导谓词有ALL、DISTINCT、DISTINCTROW和TOP n等几个,分别表示查询返回记录数的控制。数据计算方面包括合计计算及算数混合运算,一方面可以对于某个字段进行求和(或者汇总SUM)、平均(AVG)、最大或最小(MAX|MIN)、计数(COUNT)、最前最后记录(FIRST|LAST)、方差(Var|VarP)、标准偏差(StDev|StDevP);另一方面可以进行连个字段之间的算数混合运算,例如Table1.field1+Table2.field1 as fieldsum。

2.1.2 查询功能需求分析

一个数据库中包含一个或一个以上的数据表,则实际上的查询需求可能不仅局限于单一数据表的查询,很多需求是多表数据的查询,甚至是跨库复合查询。从单表角度,查询的功能需求有:(1)能够查询相应的自由字段,并能够按照要求排序;(2)能够实现相应的字段数据的分组合计计算(包括汇总、平均等),并按照指定的信息进行排序;(3)数据表中两个字段的算数混合运算;(4)在前述几个需求功能基础上,再附带一定的条件组合。多表查询的功能也有前面4个功能需求,只是字段的数据源于同一数据库的不同数据表或不同数据库的多个数据表。

2.2 查询实现

2.2.1 查询界面的设计

前面分析了SQL查询语句的SELECT句法和相应的查询功能需求,然而对于一般不需要编程或系统开发的使用者则需要一个可视化的交互式界面。通过这个界面使用者只需要进行数据文件、数据表、字段等信息选取就能够实现查询结果的返回,同时能够将查询结果导出到使用者预定的文件中。图1是针对这样的需求而开发的EXCEL数据查询交互操作界面,语言开发平台为Visual Basic 6.0[4],使用的是ADO技术实现查询功能。这是(1)一个单表或多表结合的查询界面,最多支持三个数据表的支持;(2)数据文件、数据表及其字段都是支持动态选取的,即先选定预定的数据文件,系统自动关联给出其所包含的数据表,当选择数据表后系统会自动显示其所有字段;(3)提供自由字段、运算字段、分组字段、排序字段及条件的组合的输入框,通过“→”按钮可以从左边的字段列表框直接选取;(4)界面的右下的数据表格用于查询结果的显示,其左上角的选择框支持数据表格尺寸的放大和回缩;(5)界面还支持查询结果的导出到相应的EXCEL文件,相应方法在后面进行介绍;(6)为了进一步为使用者考虑,界面支持模板以利于操作者进行重复或近似性查询操作。

2.2.2 查询语句的组织

在前面查询语法分析里,可以知道SQL的SELECT语句的组成结构,但是如何通过具体的代码实现的呢?使用Visual Basic语言和ADO技术,第一步是需要创建并打开一个数据库的连接(Connection);第二步是使用前面的连接对象创建并打开一个记录集(RecordSet)。其中SQL的SELECT语句就涵盖在记录集打开的参数里。具体代码如下:

上面两段代码可以分别放在两个函数里,假设左边的函数名为conn_open返回类型为ADODB.Connection,则在函数后面在写一句Set conn_open=conn_ff,就可以将函数内的conn_ff变量通过函数名传递出来,当然s_file为此函数的字符串性参数;同理右边记录集可以定义这样的函数Function rset_open(sqlss as string,conn_oo as adodb.connection,r_w as integer)as adodb.recordset,当然函数最后一句是set rset_open=rs_oo,这样就可以将函数内部记录集变量传递到外面来。这样做的目的是增强代码的灵活性和可维护性。

通过上面两个函数,可以很容易获取一个二维结构的数据记录集,但上面右边代码里面有一个参数很关键,就是sqlss这个变量。下面写一个双表关联的复合查询的SELECT语句以说明问题,其他简单情况读者可以参考相关资料类推。

Sql_str=“SELECT[03月$].销售额-[04月$].销售额AS销售环比增长额0304 FROM[03月$],[04月$]WHERE[03月$].销售产品代码=[04月$].销售产品代码”

上面SELECT语句句法与ACCESS数据库连接使用的SELECT句法没有什么大的区别,就是需要在数据表应用的地方添加了“[”“$”“]”等符号。将上述sqlss变量带入右边代码函数的参数中就可以实现相应产品销售额的03月与04月的数据比较了。

明白一个功能的实现原理不算难,很多程序员面对同样的需求和同样的实现原理,但具体实现的路径差别很大。程序代码的可维护性及灵活性就需要程序员在编写程序代码要进行代码结构的构思与设计。所以本查询系统的灵活性就是体现在如何将使用在交互式界面的输入的查询参数变量转变为类似上面产品销售额查询的SELECT语句。目前笔者已经实现上述所有功能,并能够应用图1所以界面进行EXCEL数据的多种查询,甚至数据表来源于不同的EXCEL文件。

2.2.3 查询数据的输出

关于实现外部数据向EXCEL文件“自动化”输入方面,有这样的几种方法[5,6,7]:(1)逐单元格传输数据;(2)将数组中的数据传输到单元格区域;(3)使用CopyFromRecordset方法向单元格区域传输ADO记录集中的数据;(4)使用VBA技术在Excel工作表上创建一个QueryTable,它包含对ODBC或OLEDB数据源进行查询的结果;(5)将数据传输到剪贴板,然后将剪贴板内容粘贴到Excel工作表中;(6)将数据传输到制表符分隔或逗号分隔的文本文件,然后Excel可以将该文本文件分析为工作表上的单元格;(7)使用A-DO将数据传输到工作表;(8)使用动态数据交换(DDE)将数据传输到Excel。

其中比较方便地将记录集数据导入到EXCEL的方法是第(3)、(4)和(7)方法,然而使用QueryTable方法具有优于方法(3)和(7)的优点[7],一则查询可以保存在QueryTable中,以便稍后能够刷新以获取更新的记录集;二则当向工作表中添加新的QueryTable时,可以指定将工作表上的单元格中已经存在的数据移位,以便容纳新数据。使用QueryTable语法例子是:WorkSheet1.QueryTables.Add(Connection:=rstRecordset,Destination:=Range("A1")),Add方法的两个相应参数分别是指定的记录集和EXCEL数据导入的目标起始单元格,更详细的信息可以参考Microsoft的关于EXCEL的VBA参考帮助[5]。

3 结论

许多使用EXCEL文件进行数据存储和其他功能的操作者可能会遇到这样的高级而复杂的需求,而使用EXCEL一般交互式功能又很难解决的时候,就可以利用类似这样的软件进行复杂的交互式查询。网上也有类似的EXCEL文件数据处理软件,但多是功能比较固定的,比如EXCEL数据表的批量合并等,而上述的查询软件具有一定的通用性,当然也可以进一步扩展到更多表源或更复杂功能的组合查询,且系统中还设计了查询模板的管理,这个附带功能虽然看似简单,然而在实现上也有一定难度。

参考文献

[1]SQL语法参考手册[EB/OL].[2009-01-05].http://tech.ddvip.com/2006-04/11442661803903.html.

[2]循序渐进讲解SQL查询语句高级应用技巧[EB/OL].[2009-01-05].http://tech.ddvip.com/2008-05/121006809344252.html.

[3]罗朝胜.Visual Basic6.0程序设计实用教程[M].第2版.北京:清华大学出版社,2008.

[4]HOW TO:使用Visual C#.NET向Excel工作簿传输数据任务的内容[EB/OL].[2009-01-05].http://www.cnblogs.com/Wiseman/archive/2004/11/26/69113.html.

Excel数据分析 篇8

我们身处大数据时代,数据分析从自然科学领域被借鉴到经济领域,经济领域的观察、研究、分析逐步被量化, 数据由为员工服务转而为领导服务,数据成为预测、管理和决策的依据。最小二乘法是经济数据分析中的一种重要方法,借助Excel,我们能方便地实现经济数据的最小二乘拟合。

1最小二乘线性拟合

最小二乘法( 又称最小平方法) 是一种数学优化技术。 它通过最小化误差的平方和寻找数据的最佳函数匹配。利用最小二乘法可以简便地求得未知的数据,并使得这些求得的数据与实际数据之间误差的平方和为最小。最小二乘法还可用于曲线拟合。

大家知道,将观测数据放到坐标轴上,利用作图法只要求出直线的斜率a和截距b, 即可确定此直线公式,但用作图法拟合直线时, 由于作图连线有较大的随意性,尤其是观测数据比较分散时,对同一组观测数据,不同的人去处理,所得结果大相径庭,求出的a和b误差较大。 用最小二乘法拟合直线处理数据时, 任何人去处理同一组数据,只要处理过程无误,得到的斜率a和截距b就只可能是唯一的。

最小二乘法就是将一组符合y=a+bx关系的观测数据,用相应方法求出最佳的a和b。

设直线方程的表达式为:

要根据观测数据求出最佳的a和b,对满足线性关系的一组等精度观测数据(xi,yi),假定自变量xi的误差可以忽略,则在同一xi下,观测点yi和直线上的点a+bxi的偏差di如下:

D对a和b分别求一阶偏导数为

再求二阶偏导数为:

显然:

满足最小值条件,令一阶导数为零:

引入平均值:

将a、b值代入线性方程y=a+bx,即可得到回归直线方程。

由公式( 2) 知,观测值与预测值两者之间的均方误差:

最小二乘法中,取D为最小,也即MSE最小。

对于一个单自变量的问题,一条回归直线拟合得好不好是一个比较直观的问题,如果它能够比较好地解释因变量y的变化,就说明它拟合得较好,当然要具体描述好到什么程度,即拟合优度,在回归分析中通常用判定系数来说明拟合优度。

R2被称为判定系数, 用它来判断回归方程的拟合优度。 尽管在数值上没有严格的规定,但通常可以认为当R2大于0.9时, 所得回归直线拟合得较好, 但当R2小于0.5时,所得到的回归直线很难说明变量之间的依赖关系。

2 Excel中拟合直线实现经济数据回归分析和预测

最小二乘法在诸多数据分析方法中,误差小、精确度高,但在实际使用中计算较为繁琐,不便使用。随着计算机的普及,可以编写程序利用最小二乘法迅速、方便处理数据,但对于非计算机专业人员,特别是经济类专业人员来说,编程难度较大。当然也可以使用专门的数据分析软件, 如SAS、SPSS、EViews、Statistic、Mat Lab等, 但此类软件不太普及。 好在Micro Soft公司在其电子表格软件Excel中提供了相应的数据分析工具,加之Excel又是一普及率极高的软件,我们利用最小二乘法对经济数据进行回归分析和预测的问题也就迎刃而解了。

我们通过实例在Excel中完成回归分析和数据预测, 所用软件版本为Excel 2013, 使用Excel过程中需要用到 “规划求解”和“数据分析”工具。 这两个工具插件需要安装,使用“ 文件”->“ 选项”命令,在弹出的对话框中,选择 “加载项”,“管理” 下拉列表中选择“Excel加载项”, 点击 “转到”按钮,在“ 加载宏”对话框中勾选“ 分析工具库”和 “规划求解加载项”,点击“确定”按钮,完成插件的安装,即可在“数据”命令选项卡中使用这两个工具。

从国家统计局网站获取2000年至2015年国内旅游人均花费和人均GDP的统计数据如表1所示, 建立国内旅游人均花费( y) 对人均GDP( x) 的回归方程。

将所有观测数据以及必要的名称标识输入到Excel工作表的单元格A1:D17中,以人均GDP的值为x值( 数据所在单元格为C2:C17) , 国内旅游人均花费的值为y值( 数据所在单元格为B2:B17) ,绘制xy散点图( 如图1所示) 。

从图1上可以看出两者之间存在着大体上的线性依赖关系。 因此可以判断要解决的是一个线性回归问题。 回归方程的形式如方程( 1) :y=a+bx。 Excel提供了几种不同的工具来确定回归系数a和b。

2.1规划求解

此方法直接利用均方误差极小化的原理。首先假定回归系数的值,用假定系数的回归直线方程对自变量的各观测值求出相应的因变量估计值,并计算出因变量估计值与观测值之间的均方误差。 最后利用Excel的规划求解工具找到均方误差极小值所对应的回归系数的值。

在单元格G5中计算国内旅游人均花费和观测值之间的均方误差MSE,即在G5中输入公式“ =SUMXMY2(B2: B17,D2:D17)/COUNT(B2:B17)”,然后,启动规划求解工具, 弹出“ 规划求解参数”对话框,在该对话框中将目标单元格设置为SGS5,使其等于最小值,将可变单元格设置为SGS2: SGS3, 无须添加任何约束条件即可直接求解( 如图2所示) , 保存规划求解结果, 得到a =291.599152和b = 0.011246,即回归方程为y=0.011246x+291.599152。

通过回归方程所求得的国内旅游人均花费估算值见表1,对比估算值和观测值,偏差在适当范围内。 方程很好地描述了两变量之间的依赖关系。

当然,要定量检测该回归方程的拟合优度,就应计算判定系数R2,Excel中专门用来计算一元线性回归判定系数的内建函数是RSQ(),在G7中输入公式“=RSQ(B2:B17,C2:C17)”,得到R2的值为0.952483928。这表明,回归方程可以很好地用来描述国内旅游人均花费与人均GDP之间的关系。

2.2内建函数INTERCEPT()和SLOPE()

Excel中线性回归拟合直线方程的截距函数INTERCEPT()、斜率函数SLOPE()以及相关系数函数CORREL()可以用来确定线性方程y=a+bx的a、b两个系数和计算相关系数以判别线性回归是否合理。

如在上例中, 在单元格B19中输入公式“ = INTERCEPT(B2:B17,C2:C17)”, 在单元格C19中输入公式 “ =SLOPE(B2:B17,C2:C17)”, 在单元格D19中输入公式“ = CORREL(B2:B17,C2:C17)”,即可得到截距a=291.5991509, 斜率b=0.011246162和相关系数R=0.975952831,显然,由前述可知,R2可由RSQ()函数求得。

2.3内建函数LINEST()

LINEST()函数是一个数组函数,它的输出为一系列回归分析的结果,包括回归系数的值、标准误差、判定系数等一系列统计量,我们通常只用它来计算回归系数。 选中相邻的两个单元格如B21和C21, 输入公式“ =LINEST(B2: B17,C2:C17)”后,按组合键“ Ctrl+Shift+Enter”来完成数组函数的输入,即可得到斜率b和截距a的值0.011246162和291.5991509。 由于Excel把LINEST()函数的输出格式设置成b在前、a在后,从左到右排列,因此必须在两个左右相邻的单元格中建立此公式。

2.4回归分析报告

这样,Excel自动生成一个回归分析报告( 如图5所示) 。其中J17和J18中分别列出了回归系数a和b的值,单元格J5中为判定系数R2的值。当然它们与前面几种方法得到的结果是完全一致的。可以直接利用这些结果进行分析和预测。

2.5散点图中添加趋势线

通过“ 插入”->“ 图表”作出国内旅游人均花费与人均GDP散点图( 如图1所示) ,选中所作的图表,使用“ 图表工具”->“ 设计”->“ 添加图表元素”->“ 趋势线”->“ 线性”命令,接着使用“ 其它趋势线选项”命令,在弹出的对话框中勾选“ 显示公式”和“ 显示R平方值”的复选框,便可得到拟合直线方程和判定系数( 如图6所示) 。拟合直线方程为y=0.0112x+291.6,判定系数R2=0.9525,显然同前面几种方法得到的结果是一致的。

以上,使用不同的方法拟合了国内旅游人均花费与人均GDP的直线方程:y=0.0112x+291.6, 通过方程, 给定人均GDP值即可求出国内旅游人均花费,如我们通过权威部门获知我国2016年上半年GDP同比增长6.7%,按此增速求得2016年人均GDP预测值为52658元,通过方程可求得2016年国内旅游人均花费估算预测值为883.8元, 预测数据可为旅游管理部门和相关从业企业的管理、经营决策提供依据。

3几种回归分析方法的比较

在Excel中进行一元线性回归分析可以采用不同的方法,我们用到的这五种方法各有优缺点。 以下,通过表2从直观性、操作难易性及应用范围等方面对这几种回归分析方法进行比较。

4结论

对于经济数据的分析,使用最小二乘法进行一元线性回归是一种重要方法,但最小二乘法涉及到的计算较为繁琐。 本文借助Excel,归结了五种计算回归系数的方法, 通过变量的观测值,进行直线拟合,以函数及图表的形式展示两个变量之间的依赖关系,从而建立预测模型,为管理决策提供依据。

摘要：最小二乘法是经济数据分析中的一种重要方法,介绍了最小二乘法的基本原理,对Excel中使用最小二乘法的五种方法进行探讨,通过拟合直线方程建立预测模型,为管理决策提供依据。

关键词：最小二乘法,excel,回归分析,经济数据

参考文献

[1]刘兰娟,等.经济管理中的计算机应用[M].北京:清华大学出版社,2006.

[2]http://data.stats.gov.cn/easyquery.htm?cn=C01中华人民共和国国家统计局年度统计数据[EB/OL].

用EXCEL设计试卷分析模板 篇9

随着教学管理越来越规范, 学院规定了每个学期期末考试的考试科目课程在得出成绩的基础上要对各个班级的考试科目试卷进行分析, 以便检查出教师教学和学生学习中的各种问题。以前都是填写学院专门的纸质试卷分析表。但在填写的过程中出现了各种问题, 第一主要有在统计各项数据时, 由于手工填写, 容易出现各种各样的计算错误;第二由于是手工作曲线图, 所以所作的图不规范、不美观。

所以用EXCEL的设计了一个专门用于试卷分析EXCEL工作薄, 现将其具体的设计过程介绍如下:

1 试卷分析各表的命名

试卷分析工作薄共有三个表, 第一张表命名为“成绩输入表”、第二张表命名为“试卷分析表1”, 第三张表命名为“试卷分析表2”。

2 试卷分析各分表的设置

2.1 成绩输入表的设置

“成绩输入表”是为了输入期末考试成绩, 此表是为第二表“试卷分析表1”提供分析数据之用, 其设置方法如图1所示。

在第一张表中的的A1单元格输入“期末考试成绩”的标题, 其下面成绩可以逐个手工输入, 也可以从其他的现成的EXCEL电子表格中的成绩复制粘贴过来。该表中不必输入学生的姓名和学号, 成绩的顺序对统计没有影响, 但所有数据必须在一列中。

2.2 试卷分析表1的设置

“试卷分析表1”中分别是有关考试试卷的相关信息、分析数据和图表, 如图2所示。

本表主要是试卷分析的各项统计数据, 例如:平均分、最高分、最低分、不及格的人数、不及格人数所占全班的百分比和各个分数段, 还有是根据各分数段的所做的一个曲线图。

从本表第一行到第三行分别输入学校的名称, “试卷分析”标题, 学年学期。这三行都是从A列到P列, 分别地合并居中。其他字体、字号可以根据自己的需要设置。

第四行为系部、专业、班级、课程四个信息点标题, 为了输入方便, 必须将这四个信息点标题分为四个大单元格, 可以在A例到P例每四个小单元格合并成一个大的单元格。设置这四个大单元格的信息点标题的水平对齐方式为“左对齐”。在输入数据时只要在这信息点标题后双击则可以输入系部、专业、班级、课程等试卷的相关信息。

第五行为“考试时间”、“命题教师”、“选用试卷 (A、B) ”三个信息点标题, 其格式的设定做法和第四行一样。其单元格合并可根据需要来设定。

第六行为标题“全班考试成绩分析”, 下面为各项分数的数据统计表。表格的做法可根所上图所示。而其各项统计数据可以通过如下的公式得出。

2.2.1“平均成绩”的统计:

运用函数“=AVERAGE (成绩输入表!A2:A1000) ”, (公式中1000由班级人数不可定所以尽量设定得大一些) 也可以用表达式:“=SUM (成绩输入表!A2:A1000) /COUNT (成绩输入表!A2:A1000) ”。

2.2.2“最高分”的统计:

运用函数“=MAX (成绩输入表!A2:A1000) ”。

2.2.3“最低分”的统计:

运用函数“=MIN (成绩输入表!A2:A1000) ”。

2.2.4“不及格人数”的统计:

运用函数“=COUNTIF (成绩输入表!A2:A65, ”<60“) “可以得出, ”不及格率“用表达式:“=D9/SUM (G9:P9) ”。

2.2.5 各分数段人数的统计:

运用“COUN-TIF”函数对“成绩输入表”中的成绩利用引用的方法进行统计, “COUNTIF”函数功能是统计符合设定条件的数据的个数。对于90-100分数段的人数统计, 其公式为:“=COUNTIF (成绩输入表!A1:A1000, ">=90") ”, 其他分数段人数的统计则需用另外的表达式, 例如:80-90分数段的人数, 其表达式:“=COUNTIF (成绩输入表!A2:A1000, ">=80") -COUNTIF (成绩输入表!A2:A1000, ">=90") ”;其原理是利用“COUNTIF”函数统计中出一个班成绩中大于等于80分的人数再减去大于等于90分的人数即可得出80分-90分之间的人数。同理70-80分数段的表达式为:“=COUNTIF (成绩输入表!A2:A1000, ">=70") -COUNTIF (成绩输入表!A2:A1000, ">=80") ”, 其他分数段可依此类推, 最后一个分数段表达表则只要:“=COUNTIF (成绩输入表!A2:A1000, "<10") ”。

以上的数据来源都是应用“相对引用”方法来引用“成绩输入表”中的数据。

2.2.6 各分数段人数所占总全班总人数的百分比统计:

运用表达式:“=G9/SUM (G9:P9) ”, 其他相应的单元格则可以用填充柄进行拖动得到。

2.2.7 在“全班考试成绩分析”表下面则插入图表, 第一步图表类型中选择“折线图”中的“数据点折线图”。

第二步图表的数据源为“=成绩分析表1!$F$7:$P$8, 成绩分析表1!$F$10:$P$10”, 即分别选中上表中F7-P8单元格和G10-P10单元格。第三步“图表选项”中“图表标题”不填, 其标题可在图表上方一行输入“成绩分析曲线”, “分类 (X) 轴”中填“分数段”, “分类 (Y) 轴”中填“百分比 (%) ”。第四步“位置”选择“作为其中的对象插入”。将图表拖到单元格A15-P32之间完成本表全部设定。

2.3 试卷分析表达2的设置

第三张表“试卷分析表2”是填写本次考试试卷的题型、数量、难易程序、学生掌握情况、学生和试卷存在的问题和改进措施的相关信息。其设置如图3。

在本表当中, 主要是主观的文字描述, 可以在电脑上直接输入文字, 也可以是空白表在打印好手写。在用电脑上输入文字时, 主要注意的是在同一单元格内换行时, 按Alt+回车键盘即可在同一单元格中进行文字的换行。

3 页面设置和打印

在三张表都设置完成后, 最终打印的只有“成绩分析表1”和“成绩分析表2”, 由于要考虑到试卷分析是正反打印而且要和试卷一起进行装订。所以对“成绩分析表1”表的页面设置, 纸张可以可选用16K或A4。页边距的设置左边距设置为2.4厘米, 右边距设置为0.4厘米。而“成绩分析表2”表纸张和前面一样, 唯一不同就是页边距和前面的表相反, 左边距0.4厘米, 右边距为2.4厘米。

4 保存模板和调用模板

4.1 保存模板

整个工作薄完成后, 就可使用。但为了以后使用方便, 可将空白中试卷分析存模板, 这样就可以无数次调用空白的试卷分析。其做法如下:

打开空白试卷分析表, 点击“文件”菜单中“另存为”, 弹出“另存为”对话框, 如图4所示。

在“文件名”列表框中输入“试卷分析模板”, 在“保存类型”下拉菜单中选择“模板”, 而“保存位置”会自动跳转到存放模板的文件夹。点击“保存”按钮完成。

4.2 调用模板

要调用此模板时, 在打开EXCEL程序后, 需点击“文件”菜单中“新建”命令, 弹出“新建”对话框, 如图5所示。

点击表格右边的“任务空格”中“本机上的模板”, 弹出“模板”对话框。如图6所示。

双击“试卷分析模板”图标, 即可打开该模板使用。

本表经过几个学期的使用实践, 取得了良好的效果, 并在教师中得到了推广。本表只要在第一张表“成绩输入表”中将成绩在规定的列中输入, 就会自动在第二张“试卷分析表1”中出所有的统计数据和图表曲线。其他一些信息也可以在电脑中输入, 大大减轻教师工作量, 也使试卷分析变得很漂亮美观。

摘要：Excel是一个具有强大统计和计算功能的表格数据管理软件, 它的数据统计计算和数据处理的各种功能能够有效地处理人们日常工作中遇到的很多的数据表, 主要讲述了利用Excel软件制作成电子版的试卷分析, 图文并茂地展现了Excel的数据计算功能和图表的使用以及数据表格的打印问题。解决了学校中的试卷分析一直是手工统计绘制易出错, 不美观的缺点。

关键词：EXCEL,试卷分析,函数,工作表

参考文献

[1]闫保权.用Excel自动统计学生成绩[J].内江科技, 2007, 9.

[2]杨红蕾.Excel在医院统计报表制作中的应用[J].中国医院统计, 2008, 9 (15) .

基于Excel的数据加密方案 篇10

对于Excel文件, 可认为有工作簿组成, 而工作簿由若干工作表组成。因此, 基于Excel本身, 可采取两种保护方法:工作表保护和工作簿保护。

1、工作表保护。

点击“工具”—>“保护”, —>“保护工作表”, 可以设定密码保护你的工作表, 以防止自己无意的修改或他人未经授权的修改。此功能可使非法用户只能看到工作表内容, 但无法对文件进行修改。如果用户想在总体保护表的情况下对表的个别数据进行修改, 可在保护工作表之前, 设置“单元格格式”-“保护”选项, 选择锁定或隐藏复选框。取消锁定则在保护工作表之后仍可修改此区域数据。设置隐藏可使保护工作表之后, 隐藏公式数据。

2、工作簿保护。

Excel为用户提供了二种方式来保护工作薄。点击“工具”—>保护”—>“保护工作簿”, 可以设定密码保护你的工作簿的结构和窗口。保护“结构”, 是指工作簿中的工作表将不能进行移动、删除、隐裁、取消隐跟或重新命名, 不能插入新的工作表。保护“窗口”可以在打开工作簿时保持窗口的固定位位置和大小。

上述两种方法存在一些问题:即在保护之后, 非法用户虽然无法修改文件内容但仍能看到文件内容, 从而泄露重要数据。因此这两种可认为是对合法用户 (管理员) 起到一定的防止误操作的辅助手段, 无法从根本保护数据不被泄露。

3、文件口令保护。

点击“工具”—>“选项”, —>“安全性”可以设定打开权限密码或修改权限密码保护你Excel文件。

设置打开权限密码之后, 在没有输入正确的密码之前, Excel将不允许用户打开这一Excel文件, 从而也就无法看到文件内容。设置修改权限密码之后, 在输入打开权限密码后, Excel文件将继续要求您输入修改密码, 如果密码无效, 则只能以只读方式打开。但是这种方法打开之后, 如果做了修改, 系统会提示你另存文件, 另存之后, 新的文件修改密码自动失效, 打开密码仍起作用。

二、基于Excel文件的加密

鉴于采用上述方法使文件部分数据能够被非法用户得到, 我们采用对整个文件进行加密, 极大地增加了安全性, 原理如下:整个系统有两个模块构成, 加密模块encrypt和解密模块decrypt。当需要加密文件时, 命令格式为:encryptexcel文件名。当需要解密文件时, 命令格式为:decryptexcel文件名。

加密原理为:假设文件原始内容为M, 用户随机选定一个密钥为P, 然后M和P异或运算得到密文C。即:。

解密原理为:密文C和密钥P异或运算得到原始内容。即:。

实际上, 只要对加密文件再加一次密, 即得原始文件, 即只需一个加密模块即可。

实现主要代码如下:

{

FILE*fp1, *fp2;//fp1:原始文件内容;fp2:加密文件内容。

{

exit (0) ;

}

fputc (fgetc (fp1) ^30, fp2) ;

fclose (fp1) ;

fclose (fp2) ;

}

三、基于单元格的加密

在实际应用中, 我们有时并不需要加密所有数据, 如职工工资表, 我们只需对工资数据等设计个人隐私的信息进行加密, 而诸如职工名、性别、籍贯等不敏感信息则不需要保护。在这种情况下, 我们可将加密单位设计为单元格, 从而更方便用户的使用, 并且加密更加安全具体。对于单元格的加密, 我们采用VBA编程技术。比如, 要对应发工资这一项进行加密保护。主要代码如下:

Sub encrpt_pay ()

Dim i As Integer

For i=2 To 10‘加密数据所在的范围

End Sub

这样非法用户看到的只是一些无实际意义的信息, 从而保护了加密信息, 如需解密, 再执行一次即可。对其他重要的信息方法类似。不再说明。下一步, 我们将考虑并设计更加安全并方便用户使用的保护方案。

摘要：Excel是目前办公系统以及实验数据处理中常用的应用系统之一。大量重要的敏感数据被集中存放在文件里, 数据的安全性是大多数用户非常关切的。本文提出了几种加密方案, 并对它们的原理和使用方法做了详细说明。然后对各种加密方案安全性能方面进行对比分析, 为用户在保护excel敏感数据方面提供了一些借鉴。

关键词：Excel,安全性,加密,分析

参考文献

[1]张丹、乔春华:《EXCEL工作表的保护》.内蒙古科技与经济.2004.22[1]张丹、乔春华:《EXCEL工作表的保护》.内蒙古科技与经济.2004.22