Excel数据分析

Excel数据分析（共12篇）

Excel数据分析 篇1

摘要：介绍数据分析概念和知识, 了解Excel应用软件提供数据分析功能, 且具体案例实施在Excel平台数据分析, 以便掌握分析方法和技术。

关键词：数据分析,Excel,大数据

1 数据分析

纵观计算机发展历史, 无论是计算机硬件改革还是软件推出新产品新版本, 主要体现在各行各业的应用领域中, 以解决更多实际问题, 提高工作效率;多年实践经验与研究, 感悟计算机应用领域中最重要、最普及的数据处理与数据分析。

数据分析是将一大批零散、凌乱、无关联的数据进行整理、归类和提炼, 以获取数据内在规律、联系, 推断出发展趋势与展望, 是从数据要信息, 为决策提供依据。

数据分析是使用统计方法对目标数据进行分析, 将这些数据整理、筛选、分类、汇总处理, 以获取最大的数据利用, 挖掘数据深层次的有用信息, 形成分析结论并展现其过程、规律与变化。

数据分析过程包含以下几个方面:数据分析的目的与内容、数据收集、数据整理、数据分析、数据展现与数据分析报告, 首先明确项目分析的目标、清楚功能和用户要求、数据量充实全面, 然后采用最佳的、最合适的数据分析方法来实现。

数据收集是按照数据分析的目标, 收集人员数据、业务数据、市场数据等为数据分析提供原始素材和依据, 无论是直接和间接数据, 无论是以文本形式保存, 还是以Excel、Word或数据库方式保存的数据, 它们相互之间是可以转换的。

数据整理是对收集的数据需要规范处理、统一格式、符合数据分析的模式, 其过程将从大量不规则的数据中提取有效数据, 实现必要的格式化、类型转换、建立关联、排序、分类汇总和简单计算等处理。

数据分析是通过统计运算、常用分析模型、分析工具和数据挖掘等技术, 寻找数据内部的关联、规律和变化趋势, 为问题解决、多种方案比较和决策等提供支撑数据。正确的数据分析方法、从哪些侧面去分析、关注哪些数据的动态变化都是需要仔细考虑和甄别, 他人经验与自己创新结合, 获取一套行之有效的优秀思路与分析方法。

数据展现是将数据分析结果或需要表达的内容通过适当的图表类型和相关文本展示出来, 把数据之间的关联、规律与变化明确显示出来, 以图表说话, 一目了然, 吸引关注。数据分析是数学与计算机科学相结合的, 与时俱进, 才有了大数据时代更为强大的数据分析工具和威力。

2 Excel数据分析功能

Excel 2013可视化工具能帮助用户分析信息, 做出商业或管理方面的明智决策。若与Excel Services一起使用, 可以强有力地与同事、客户和合作伙伴一起共享和管理业务分析、进行深入了解。Excel 2013有十种主要方式帮助用户更有效地创建电子表格, 数据分析、数据共享和管理信息。

大容量的电子表格中使创建、导入、浏览和分析大量数据更加便捷可行。在Excel 2013中可以处理大量数据, 它支持的每张工作表可以包含多达100万行和16000列的数据;Excel 2013还支持多核处理器平台以提高公式密布的电子表格的计算速度。

利用完全重新设计的制图引擎和具有专业外观的图表中实现分析和交互信息。通过Office Fluent用户界面中的制图工具, 仅单击鼠标即可创建具有专业外观的图表;还可以在图表中应用丰富的视觉增强效果, 例如, 三维效果、柔和阴影和透明效果。

利用“表”对象的改进支持增强了电子表格的功能。由于Excel 2013显著改进了对“表”的功能支持, 可以在Excel公式中创建、扩展、筛选和引用表的功能, 还可以设置表的格式化。在浏览大型表格中的数据时, Excel 2013在滚动表的数据时仍能在屏幕上保留表的标题, 类似于拆分与冻结功能。

轻松创建排序、筛选、分类汇总和交互式数据透视表和透视图。使用排序、筛选、分类汇总可以实现一般数据分析与统计, 其功能随着版本更新而增强, 使用数据透视表可以快速创建各种报表格式, 便捷地拖动字段布局位置以构成新的报表、新的数据分析结果以展现数据相互关联, 并凸显关注的数据侧面。

根据数据“展示”重要趋势寻找数据逻辑关联。利用Excel 2013条件格式功能可以实现由数据找出内在关联模式和突出显示某种特殊趋势, 此功能包括颜色渐变、图标集、数据条和色阶等, 出此以外, 若应用函数到条件格式, 其功能更为强大, 为数据分析提供极大便捷。

大量不同功能的函数提供个性化数据分析工具。函数是Excel主要数据分析工具之一, 由于函数在数据分析中起到灵活多变的作用, 用来解决个性化、特殊要求或复杂的数据分析问题;函数使用面很广, 除了正常使用在单元格和数据区域外, 还可以使用在排序、条件格式、数据有效性、名称、图表、数据透视表等工具上, 后者使用为Excel带来了锦上添花的作用与美妙效果。

使用Office Excel 2013和Excel Services有助于更加安全地与其他人共享电子表格。Excel Services是Microsoft Office Share Point Server 2013的一种功能, 它以HTML格式动态地呈现电子表格, 因此可以使用Web浏览器访问信息。由于与Excel 2013客户端的保真度很高, 因此, Excel Services用户可以在Web浏览器中进行浏览、排序、筛选、输入参数以及与数据透视表视图进行交互数据的操作。

分析工具库提供先进的数据分析。Excel提供了三类数据分析工具:假设分析、规划求解和数据分析工具库。掌握各种分析工具的基本操作, 灵活地运用它们, 以常用的数学统计模型和分析模型实现数据分析。

由于Excel 2013完全支持Microsoft SQL Server2005 Analysis Services从而使商业智能投资得到扩展。可以使用Excel 2013的灵活性和新的多维函数, 从OLAP数据库中创建自定义报表。使用数据连接库, 还可以更轻松地连接到外部数据源。

来自Office应用程序插件。以前需要下载第三方插件软件现在已经安装在Office 2016版本支助, 加载或直接可以应用, 例如, 图表Infogram for office提供了30种原Excel难以实现的图表类型;插件People graph可以实现数据与小人组成图表同步变化;Pro World Cloud插件实现以字体云方式对输入文字的字频显示其字体大小等。

3 Excel数据分析案例

这是某公司某年1到12月份各项经费使用情况数据表 (见图1) , 针对这个数据表, 若需要进行数据分析, 应该怎样入手、关注什么呢?重点是首行与最左列的数据, 首行是关于日期的月份, 一年的12个月;最左列是各项费用名称, 它们的相交单元格是货币数据, 那么我们首先是实施趋势分析, 每项费用在12个月中是如何变化?然后观察结构分析, 所有项费用在某月使用的状况, 最高或最低, 支出是否正常?

我们需要创建两个图表 (见图2) , 使用Excel“组合框”控件选择不同费用, 差旅费或不同月份, 不同费用在不同月份的情况、变化趋势或结构分析。

生成左图表, 首先要创建数据区域 (见图3上部分) , 其中单元格B38绑定组合框控件, 使用Index函数获取来源于“源数据”工作表里不同费用的数据;单元格C38为图表标题而设置, 这样标题是动态的, 随着组合框选择内容而变化来设置图表标题, 选中它, 在编辑栏输入公式:=分析报告!$C$37;

生成右图表, 使用VLOOKUP和MATCH函数创建数据区域 (见图3下部分) , 其中单元格B41绑定组合框控件, 单元格C41为图表设置动态标题。

此时, 一旦选择左图表中组合框的其他费用选项, 即可以浏览其他费用在1~12月份支出情况, 最大与最小花费月份一目了然, 凸出显示某月的活动或投入状况;右图表展示了某月份各项费用支出的比例, 扇区大小表示了费用多少, 清晰明了、直接便捷呈现数据之间的关联和变化趋势。

4 结语

随着大数据日益普及和广泛应用, 对数据分析与挖掘技术显示出其重要性, 而更多应用者经常使用唾手可得的Excel应用程序实现数据分析, 他们逐步了解Excel具有很强大的数据分析功能, 完全可以处理数百万个记录的大数据, 2013版本及2016版本一直在不断强化数据分析, 提供了更加便捷使用的分析工具和插件。

Excel数据分析 篇2

下面我们针对三种不同的分类汇总需求，为大家介绍不同的解决方案。 这三种需求分别是：既想分类汇总又想分类打印、不想分类打印只是想随时查看各类数据的明细和统计情况、不想打乱正常的流水式数据表格的数据顺序而是想随时查看各类数据的统计结果(此处假定将统计结果保存在另外一个工作表中)。

(图1)

需求一、既想分类汇总，又想分类打印

解决方案：直接利用Excel内置的“分类汇总”功能来实现。

1、选中工程类型列的任意一个单元格，按一下“常用”工具栏上的“升序排序”或“降序排序”按钮，对数据进行一下排序。

注意：使用“分类汇总”功能时，一定要按分类对象进行排序!

2、执行“数据→分类汇总”命令，打开“分类汇总”对话框(如图2)。

图2

3、将“分类字段”设置为“工程类型”;“汇总方式”设置为“求和”;“选定汇总项”为“面积”和“造价”;再选中“每组数据分页”选项。最后，确定返回。

4、分类汇总完成(图3是“综合办公”类的打印预览结果)。

图3

需求二、不想分类打印，只是想随时查看各类数据的明细和统计情况

解决方案：利用Excel自身的“自动筛选”功能来实现。

1、任意选中数据表格中的某个单元格，执行“数据→筛选→自动筛选”命令，进入“自动筛选”状态。

2、分别选中F203、G203单元格(此处假定表格中共有200条数据)，输入公式：=SUBTOTAL(9,F3:F202)和=SUBTOTAL(9,G3:G202)，

小提示：此函数有一个特殊的功能，就是后面进行自动筛选后，被隐藏行的数据不会被统计到其中，达到分类统计的目的。

3、以后需要随时查看某类(如“经济住宅”)数据的明细和统计情况时，点击“工程类型”右侧的下拉按钮，在随后弹出的快捷菜单中(如图4)即可。

图4

需求三、如果我们不想打乱正常的流水式数据表格的数据顺序，而是想随时查看各类数据的统计结果(此处假定将统计结果保存在另外一个工作表中)

解决方案：利用Excel的函数来实现。

1、切换到Sheet2工作表中，仿照图5的样式，制作好一个统计表格。

2、分别选中B3、C3、D3单元格，输入公式：=COUNTIF(Sheet1!$E$3:$E$202,A3)、=SUMIF(Sheet1!$E$3:$E$202,A3,Sheet1!$F$3:$F$202)、=SUMIF(Sheet1!$E$3:$E$202,A3,Sheet1!$G$3:$G$202)。

3、同时选中B3、C3、D3单元格，将鼠标移至D3单元格右下角成细十字线状时，按住左键向下拖拉至

Excel数据分析 篇3

Excel数据表格中输入的数据可以事先指定其类型，或者在输入以后更改其数据类型当单元格的数据类型被指定以后，我们同时还可以指定单元格中输入数据的有效性范围。

在“数据有效性”对话框中选择“设置”选项卡，在“允许”下拉列表中，用户可以选择下面的这些类型：任何值、整数、小数、序列、日期、时间、文本长度和自定义。当选择了不同的类型后，Excel还会要求用户做出一些具体的设置，比如当选择了“整数”时，还会要求用户选择一个最大值和最小值。

温馨提示：在做出了这些设置之后，如果用户在该单元格中输入了非法的数据并按下回车键，系统就会自动弹出一个警告对话框来提醒用户输入数据非法。

在Excel表格中，我们可以通过设置实现在选定某个单元格时为用户提供一个输入提示信息。选定单元格区域，选择“数据有效性”对话框中的“输入信息”标签，在其中的“标题”和“输入信息”中填入相关的内容，按下“确定”按钮即可。这样，当用户选定了这个单元格时，就会看到这个提示信息了。

温馨提示：在设定了数据输入有效性条件之后，如果再加上数据输入范围提示，则更加直观和有效，这样的用户界面也会显得更加体贴。在为别人设计的输入表格中请尽量采用这种提示的方法。

如果Excel表格中某个单元格中的数据与其他单元格中的数据有关联，存在一定的函数关系，那么，我们可以在数据有效性的设定窗口中指定这种关系

如果需要A1单元格的数值大干c1单元格的数值，应该先选择A1单元格，然后打开“数据有效性”列表，并在“允许”下拉列表中选择“自定义”选项，在出现的“公式”编辑框中为其指定一个公式“=AI>C1”。按下“确定”按钮后，如果在A1中输入的数值小于C1中的数值，那么就会弹出警告对话框了。

温馨提示：这种方法只能保证在输入A1单元格数值时的有效性，而不能防止在C1中输入数据时大于A1。

在通常情况下，如果你设定了数据输入的有效性范围，当输入非法数据时，Excel将会自动弹出一个标准出错信息，用户的选择只能是“重试”或“取消”。其实，用户可以自定义出错信息的。选择“数据有效性”对话框中的“出错信息”选项卡，然后在显示的对话框中输入该信息的标题和内容即可。

温馨提示：用户还可以在“样式”下拉列表中进一步选择显示的信息的类型，它们是“中止”(强制用户“重试”或“取消”)、“警告”(仅告知用户数据不合法，但可以保留数据)和“信息”(告知用户何处出错)。

我们可以把网络上的数据轻松地导入到Excel表格中。选择“数据”菜单中“导入外部数据”子菜单中的“新建Web查询”命令，在弹出的“新建Web查询”对话框中，输入Web查询的网址，通过页面右上角的“选项”按钮设置各种选项，选择之后，单击页面右下角的“导入”按钮导入网页上的数据到Excel表格中。

如果你有一些文字保存在一个标准的TXT文本文件中，而你又希望在Excel表格中使用这些文本数据，该怎么办呢?

我们可以选择“文件”菜单中的“打开”命令，在弹出的打开对话框中的“文件类型”下拉列表中选择“文本文件”，这样就可以从打开的文本文件中获得所需数据了。

温馨提示：用这种方法导入数据有一个缺点，就是一旦数据进入Excel后，就无法与该文本文件同步了，也就是说，即使文本文件中的数据进行了更新．Excel也不会同时进行刷新的，必须重新导入才可以。

随着信息高速路的建设，Excel的数据将会越来越多地通过网络来传输。而Excel从2000版本开始便已经拥有了这个功能。如果用户位于局域网中，那么就可以直接打开网络上的数据了。选择“文件”菜单中的“打开”命令，在弹出的“打开”对话框中，按下“查找范围”中的“网上邻居”，找到需要的文件并打开即可。

温馨提示：如果把网上邻居用户共享的文件夹映射为本地电脑中的一个盘符，则可以方便地如同使用本地硬盘那样使用网上的文件资源了。

如果希望将外部的文本文件与Excel表格建立链接，通过更新外部数据TXT文件来达到更新Excel表格中数据的目的，又该怎么办呢?

只要执行“数据”菜单中“导入外部数据”子菜单上的“导入数据”命令，选择文本文件所在的目录，选择文件类型为TXT类型，获得文本文件中的数据。在导入数据的同时Excel会将其作为外部数据区域，这样用户就可以在需要的时候刷新数据或者定期对数据进行刷新了。

《Excel输入数据》说课稿 篇4

1. 讲练结合

单就内容而言, 学生掌握起来并不是很难。因此, 在课堂上坚持精讲多练的原则, 重难点知识精讲。

2.任务驱动法

事先设计好的由易到难的任务, 让学生在完成任务的同时, 掌握新课的内容, 解决重点难点。

3.探究法

对于每个操作任务, 鼓励学生思考探究, 独立尝试操作, 自行分析问题并寻求解决问题的方法, 让学生从“学会”转化为“会学”。

说教材

1. 教材处理

本节课要求学生首先掌握E xcel不同类型数据的输入方法, 掌握各种输入数据的技巧。

2. 教学目标

知识目标:掌握不同类型数据输入, 掌握快速输入数据的方法。

能力目标:帮助学生提高处理数据的能力, 培养学生处理信息的能力, 养成良好的思维习惯和行为方式。

情感目标:主要是培养学生的审美意识和养成严谨的学习态度以及团结协作的作风, 在学习过程中体验成功的喜悦和“学有所用”的快乐。

说教学过程

1.导入新课

用大屏幕向学生展示成绩表, 里面有很多相同的数据或有规律的数据, 应该如何输入呢?有没有快速输入方法呢?通过两个提问, 自然引出本节课的内容, 明确了学习任务, 激发学生求知欲, 提高学习兴趣。

2. 角色扮演, 任务驱动

在该环节采用三个任务。三个任务的目的不同, 按照循序渐进的顺序由易到难, 由简单到复杂, 由掌握一般方法到实践应用。下面逐个说明任务的实现方法, 以及如何通过各任务来解决知识的重点、难点。

任务一, 输入不同类型的数据, 目的是让学生理解数据的不同类型, 不同数据类型的不同输入方法。让学生按照大屏幕上给出的数据输入不同类型的数据到E xcel表中。首先要求学生自己输入, 学生会遇到问题, 查课本寻找解决方法。有的学生可能不会邮编这样的字符串的输入方法, 有的学生可能不会分数的输入方法。通过大屏幕给学生展示输入的方法。教师再进行讲解, 问题解决了, 学生会记得更加牢固。这种学生发现问题、师生共同解决问题的方法, 使学生全身心地投入学习讨论中, 发挥了团结协作的力量, 解决了本节课难点。

任务二, 输入学生成绩表, 目的是进一步理解不同数据类型的输入方法, 让学生掌握自动填充的方法, 为多个不相连的单元格输入相同数据的输入方法。学生自己输入, 教师巡回辅导, 发现问题及时解决, 有相同问题, 通过大屏幕演示解决。

通过以上两个任务, 学生学习了数据输入的方法和快速填充数据的方法, 如何引导学生将学会的方法用于实践, 才是关键所在, 所以设置任务三。

任务三, 综合训练。这个任务注重实践应用, 将学到的输入数据的方法用于实践。要求学生输入练习册上的富强公司上半年营业报告, 练习序列填充的方法。综合训练提高了学生的实际应用能力, 引导学生运用先进的信息技术去分析日常中遇到的问题, 将复杂的问题简单直观化, 提高学生运用信息技术与其他学科整合的能力。

3. 归纳总结, 布置作业

Excel数据分析 篇5

数据有效性分类

Excel可以将数据有效性定义为如下几种状态：

任何值、整数、小数、序列、日期、时间、文本长度及自定义(通过公式返回值来设置数据的有效性)，

Excel2013的数据有效性excel办公/数码

，

Excel2013的数据有效性

巧用Excel分析评价英语试卷 篇6

关键词：Excel； 英语试卷； 测试； 统计分析

目前，90%的初中每周会有一次单元测验，再加上月考，期中考，期末考，教师仅凭手工进行数据资料的整理和计算，工作繁重枯燥，只能提供考生的考试分数、平均分和及格率等基本考试情况。

利用Excel 可以轻松完成复杂的试卷质量分析与评价。

在教育统计学中，对统计资料的处理分为三个步骤进行：第一步是全面而准确地收集客观数据资料；第二步是整理数据资料；第三步是统计资料的计算和分析。

一、数据资料的计算和分析

数据资料的收集和整理以后，为了更客观地解释考生分数，更好地了解教学班的考试情况，一般通过如下几个统计参数进行计算和分析，在此举例说明。

下例对某班一次“英语单元测试”成绩进行统计分析，将学生成绩填入Excel的单元格区域“B2:B15”中，如表1所示。

1． 班级人数。在Excel表中可用COUNT函数计算，例如：在单元格E3中填入公式=COUNT(B2：B15)。如果有学生缺考，应该用COUNTA函数对非数值型数据进行统计,例如:在单元格D3填入公式= COUNTA(A2：A15)。

2． 班级最高分。可用MAX函数计算，例如：在单元格F3中填入=MAX(B2：B15)。

3． 班级最低分。可用MIN函数计算，例如：在单元格中G3填人=MIN(B2：B15)。

4． 班级平均分。可用AVERAGE函数来计算，例如：在单元格H3中填入公式：AVERAGE(B2:B15)。

5． 及格率。通常将60分设定为及格线。在Excel中可用COUNTIF函数来计算，例如：在单元格J3中填入=COUNTIF(B2:B15，“>=60”)／COUNTA(A2：A15)。

6． 优秀率。初中英语教学通常将95分以上设定为优秀分。还是用COUNTIF函数来计算，例如：在单元格D9中填入=COUNTIF(B2：B15，“>=95”)／COUNTA(A2：A15)。

7．分数段。将考生成绩按照高低分进行分数段的统计，可以大致了解考试出题难度组合状况、考生成绩平衡性等。在Excel中可用COUNTIF函数来统计。例如：“80—89”分数段可用公式=COUNTIF(B2：B15，“>=80”)—COUNTIF(B2：B15，“>=90”)，“70—79”分数段可用=COUNTIF(B2：B15．“>=70”)—COUNTIF(B2：B25，“>=80”)，“60-69”分数段可用=COUNTIF(B2：B15，“>=60”)—COUNTIF(B2：B15，“>=70”)，不及格人数可用=COUNTIF(B2：B15，“<60”)进行统计，其余类似。

8． 平均差。该参数表示全班学生成绩与全班平均分的绝对偏差的平均值，用于测评全部学生成绩与平均分的差异情况。在Excel中可用AVEDEV函数来计算。例如：在单元格E9中填入公式=AVEDEV(B2:B15)。

9． 标准差。这是反映考生考试成绩波动情况和判断、区分考生成绩的最重要的统计量。理论上认为标准差大，区分效果好，标准差小，成绩分布则比较集中，通常要求将该值控制在“8—10”之间。在Excel中可用STDEV函数，例如：在单元格F9中填入公式=STDEV(B2:B15)。

10． 标准分。这是以标准差为单位，表示某一分数与平均数的差。标准分数在教育工作中的用途很多，它可以用来比较各个学生的成绩在班级成绩中的地位，也可以用来比较某个学生在两种或多种测验中所得分数的优劣。计算机可以方便的将原始分转换成标准分，使课程之间和班级之间具有可比性，找到各班、年级在校内的位置，从而对全校教学情况一目了然。例如：计算某个学生(陈一)的标准分，在单元格中填入公式=(B2—AVERAGE(B2：B15))／STDEV(B2：B15)。

二、考卷的分析和评估

直方图工具：以某校八年级某班期末考试成绩为例，将考试成绩输入Excel表格C1:C41。该测验最高分为100，最低分为78，先确定一个区间[77.1, 102.1] (组限通常取比数据的精度高一位，即多一位小数点或个位数，以免数据刚好落在端点上)。将区间[77.1, 102.1]等分为5个小区间，即组距△=（102.1-71.1）/ 5=5。这样各小区间的组限端点从左至右依次为：77.1，82.1，87.1，92.1，97.1和102.1

打开EXCEL文档，点开“工具”——“数据分析”——“直方图”，在“输入区域”对话窗输入C1:C41（即学生成绩所在栏）,在“接受区域”输入组限端点所在地单元格区域，选中“标志位在第一行”。即可得到直方图：

根据直方图，我们可以大致看出某项目（如某次测试的成绩）的分布图形，从而判定该项目所蕴涵的意义。分布图形一共分六种：

正态分布 说明测试结果与学生的实际情况一致，各种难度的项目比例合理。

正偏态分布 说明试题难度偏高，难度较大的项目比例偏大。呈这种分布的试题有利于将

成绩优秀的学生和中等程度的学生区别开，但不利于将中等程度的学生和成绩较差的学生区别开。

负偏态分布 说明试题难度偏低，难度较低的项目比例偏大。呈这种分布的试题有利于将成绩较差的学生和中等程度的学生区别开，但不利于将中等程度的学生和成绩优秀的学生区别开。

尖峰态/陡峭型分布 说明试题中同等难度的项目较多，梯度偏小。呈这种分布的试题几乎不能将不同程度的学生区分开，分数分布过于集中。

低峰态/平坡型分布 说明试题中各种难度的项目比例接近，梯度较大。呈这种分布的试题区分度较高，但分数之间的差异偏大。

双峰型分布 说明试题存在两极分化现象，即难度偏高的和难度偏低的项目较多，而中等难度的项目偏少，项目难度的分布缺乏梯度，不够合理。呈这种分布的试题可以区别中等程度的学生，但不利于区别出成绩优秀的学生和成绩较差的学生。

于是我们可以得到结论：

1. 根据该班级测试的直方图，可以判定该正态分布为负偏态分布。有利于将成绩较差的学生和中等程度的学生区分开，但不利于将中等程度的学生和优秀学生区分开。

2. 该正态分布为尖峰态分布。不能将不同程度的学生区分开，分数过于集中。

参考文献：

1. 黄光扬.《教育测量与评价》华东师范大学出版社，2002

2. 侯晓霞，王子勇.《试卷质量分析与评价系统地设计》计算机工程,2000,26(11):151-153

3. 李翠，李进才《利用Excel对试卷质量进行系统分析》集宁师专学报，2001，23（4）：26-28

Excel数据分析 篇7

一、教学前期准备

本案例是北京市海淀区实验教材《信息技术》初中上册中的第二单元—电子表格的起始课。本单元要求学生能够应用电子表格进行数据分析和评估，从确定主题—采集数据—建立表格—数据分析—到得出结论。本课主要任务是了解电子表格的特点，掌握创建表格的方法。

1. 学生情况分析

本案例是初一年级第二学期的内容，学生经过中小学合计4年半的课堂学习和课后练习，已经熟练掌握了办公软件中的Word和PPT的基本操作，对Microsoft Offi ce软件环境较为熟悉，并且初步具备了一定的自主学习能力和小组合作学习能力，乐于探索，乐于实践，这些都是课堂教学顺利高效进行的有利因素；但是学生此时也同样具有缺乏耐心、急于求成的特点，这对学生通过探究进行自主学习是个不利因素。根据以往的经验，学生从图像处理单元过渡到电子表格单元时有一定的心理落差，往往学习兴趣不高。

(分析：教师首先分析学生的知识储备，学生已经掌握了办公软件中Word和PPT的基本操作对新软件的学习有正迁移作用，所以教师确立了自主学习的教学模式，让学生能够主动探究和构建知识体系。并且根据学生的心理特点，把如何激发学生学习兴趣确定为本课的基点，从兴趣出发教师设计了一系列的任务案例，使得学生在完成任务的过程中掌握知识与技能。由此可见，教学设计应该在分析教材、学生的基础上，了解并尊重学生的需要，从“学生的需要”出发，确定教学目标，设计教学案例。)

2. 教学目标

针对学生情况和本课的教学内容要求，确定如下教学目标：

知识与技能：学会从搜集的素材中，分析、整理、提取有效数据；掌握使用Excel创建表格的方法，合理设置表格标题、表头项目。

过程与方法：在整理从实际生活得到的数据的过程中，体会分析数据关系、提取有效数据的方法；在尝试交流过程中，提升自主学习和小组学习的能力。

情感态度价值观：通过创设“节水视频”的情境，引发关注全球水资源短缺的问题，建立“世界有我，节水由我”的节约环保观念。

教学重点：从搜集的素材中，分析、整理、提取有效数据；根据实际需要创建表格，合理设置表格标题和表头项目。

教学难点：分析整理数据，设置表格标题和表头项目。

(分析：教学目标处于核心地位，教学中的诸多因素都是围绕教学目标，为教学目标服务。在教学中，教师所要做的第一步就是从学生的需要出发，在把握教材的基础上，设定教学目标，明确教学重、难点，使教学活动围绕教学目标展开。

本节课教学目标遵循了新课程标准的三维目标，描述清晰、全面而又富有学科特点。首先，教学目标的表述是以学生为主体，并且阐述了通过整理实际生活中的数据的过程，掌握分析数据关系、提取有效数据的方法。其次，在教学目标的描述上合理运用行为动词，如学会、体会等，便于操作，同时对行为动词的使用条件进行了界定，便于实施和测量，为后期教学策略以及教学评价的选择提供了依据。

由于学生已经掌握了Word和PPT的基本操作，对Excel的基本界面不会陌生，所以本课的教学重点为学会从搜集的素材中，分析整理提取有效数据，以及根据数据合理设置表格标题和表头项目，更注重让学生体验数据处理的过程。)

二、教学过程设计

本案例从世界水资源危机导入，让学生面对一个个问题，在解决问题的过程中学会方法。本案例的教学流程如图1所示：

1. 创设情境，引出课题

教师播放有关“水资源危机”的视频文件，并提出问题：在此短片中，引用了大量的什么资料给你留下深刻的印象？使用这种资料来说明水资源危机的好处是什么？

学生观看视频文件，思考何种信息起到了加深印象的作用，并尝试归纳数据的作用。

教师引出课题从这节课开始，我们将学习第三单元——数据处理与分析初步，使用的软件是专门处理数据的，这个软件的名字是Excel，并提出问题Excel作为Office家族中的一员，它的界面和Word有什么相似之处和不同之处？

(分析：教师精心准备了“水资源危机”的视频文件，使学生关注全球水资源短缺的问题，同时也感受到数据更能清楚地表明水资源的现状，激发学生深入研究数据的兴趣。同时借助与Word软件的对比，利用学生已有的知识结构，同化新知识，构建知识体系。)

2. 提出问题，合作探究

教师引导学生打开“课堂练习”文件，提示学生使用“工作表导航按钮” (学生可以参考演示文稿上的图示) 浏览工作簿中的各张工作表，并给出小组思考题：此文件 (工作簿) 当中有多少张工作表？这些工作表之间有什么共同之处吗？修改单元格中的数据，图表会有变化吗？

学生打开课堂练习文件，尝试探索“工作表导航按钮”的用法，打开工作簿中包含的各张工作表，初步认识工作簿和工作表，思考教师提出的问题。并且修改数据，观察结果，体会Excel“牵一发动全身”的含义。

教师在学生小组讨论的基础上，适时进行总结：Excel和Word与PPT软件都是Office办公软件，它们界面相似，都用来展示、呈现信息，但是Excel更重要的用途就是能够对数据进行加工、处理和分析 (用途) 。

(分析：由于大部分学生第一次接触Excel软件，当看到修改数据后，相应的图表发生变化时，会激起学生进一步探究的兴趣。最终学生在操作实例的过程中，逐步熟悉Excel的界面，自主探究Excel的操作方法。学生在尝试探究的过程中，重新构建对Excel的认识，并且通过小组交流、师生互动，得出结论：Excel分析数据、制作图表不仅仅是为了展示和呈现信息，更重要的用途是能对数据进行加工、处理、分析。教师设计的小组交流环节，既鼓励学生大胆尝试，又培养了学生乐于分享的意识，为今后的合作学习打下基础。)

教师进一步提出问题：观察课堂练习中两张工作表，找出共性，如图2所示。思考是否可以在Word中完成？

学生经过组内梳理、交流，逐步清楚找出工作表的共性：都有标题、表头、数据。在Word中尝试后发现，在Word中制表最大的问题是修改数据，图表不会产生相应的变化。

(分析：通过引导学生观察分析两张工作表的共同之处，培养学生仔细认真地学习态度，并且为后续创建表格打基础。通过与Word中制作表格的对比，使学生感受到电子表格的特点和作用。)

3. 教师引领，创建表格

教师提出新的问题：既然Excel具有强大的数据处理与分析功能，那么数据从哪里来？学生根据以往经验提出各种数据来源，师生共同归纳总结数据来源：直接或者间接途径：直接途径，第一手资料，例如做些社会调查，直接从社会中了解情况、收集资料和数据。或者从其他途径获得第二手资料。 (例如上网查找资料，从新闻、报纸、杂志、文献等渠道获取数据) 。这两种方法，大部分情况下是要综合使用的。

(分析：数据的来源非常重要，这直接影响数据分析得出的结论是否真实、准确，关注数据来源对于后续的研究专题的顺利开展来说是个保证。培养学生做学问认真的态度。)

具体案例：前面观看的视频中存在大量的数据，分别从不同方面描述了水资源短缺的现状，方便在一张表中呈现吗？有些我们需要的数据还不完整，怎么办？

教师引领学生从视频中，提取出需要的数据，分析数据之间的关系，确定表格标题，并且按照数据的共性特点设计出表头名称，将标题和表头分别填写到合适的单元格中，如图3所示。

(分析:从素材中分析整理提取有效数据, 合理设计标题和表头是本课的重点, 但是学生以往这方面经验较少, 不足以支撑自主探究。因此教师通过具体实例分析, 引领学生学习获取数据、处理数据的方法。)

4. 小组合作，自主学习

教师布置探究任务，每个小组根据自己的情况，选择完成一个关于用水情况的电子表格，并分析数据，得出结论，如图4所示。

(分析：由于学生程度参差不齐，教师在安排综合任务时要考虑分层教学。 (1) 知识的分层—根据对教材的把握，哪些知识是每个学生需要掌握的保底知识，哪些是提供给有一定能力的学生学习的提高、拓展知识，本课美化工作表就属于提高任务； (2) 学生的分层—根据学生的认知水平和接受能力进行分层，对不同层次学生要求不同。这两个不同方面的分层结合在一起融入到连贯统一的教学情境中，为处于不同能力水平的孩子提供不同的发展空间。)

5. 交流小结，评价反馈

本案例的最后一个环节是作品展示交流和评价反馈。教师根据巡视记录的结果，选择2～3组有代表性的作品进行评价和总结。对共性问题做出说明，建议学生翻阅教材解决或者等待教师在后续课程中给予解决。并组织学生利用网络提交表单的形式开展自评和互评，评价表如图5所示。

(分析：设计的评价方式注重将新课改提出的形成性评价与终结性评价相结合、自我评价与他人评价相结合的评价方式；实现了多元化的评价主体，如师评、自评与互评；以及评价内容多样化，教师不仅对学生的任务完成情况以及最终作品进行了评价，而且关注了学生在学习过程中所表现出来的学习态度等方面。并且基于网络提交表单方式的评价方法非常客观、准确、快捷，不仅便于学生再次梳理强化知识，而且帮助教师更加准确客观了解学生学习情况，从整体上看到每个问题的选项情况，从而了解整个班级、整个年级的学习情况，对教师在后续课程中进行有针对性的调整提供了第一手数据资料。)

三、结束语

本案例从学生的需要出发，在分析学生情况和教学内容的基础上制定教学的目标，确定教学的起点，引导学生利用已有的知识、经验，探究新软件的使用方法。在探究学习过程中，使学生经历了提出问题、分析问题、解决问题的过程，掌握解决问题的方法、形成技能，帮助学生把新旧知识经验构建成整体一贯的知识体系，体现了信息素养的培养。

总评:案例研究是教学研究中的一种重要形式, 针对教学中的实际问题, 理论联系实际, 对一线教师改进教学具有显著作用。

做好学生学习需求分析是确定课堂教学目标的重要依据。做学生分析既要结合教学内容分析学生现有知识、能力基础, 也要分析学生心理特点, 之后教学环节、教学手段的设计才有的放矢。

教学目标没有放在Excel软件中的具体知识点、操作步骤上, 而是强调学生分析、整理、提取有效数据, 运用技术解决问题的能力培养, 体现了课程培养学生信息素养的指导思想。

过程与方法目标紧紧围绕教学主要内容, 实实在在。

绿色环保是当前以及今后一个永久的备受世人关注的话题。学生从真实的数据分析得出结论, 亲身体会到环保的意义, 这样得到的教育效果会大大优于言语的说教。

用流程图梳理教学思路, 清晰、易读。

采用播放视频引入课题的方法很多人都会用, 关键是视频的内容要与教学内容紧密相关。这节课的主要内容是利用Excel工具分析数据得出结论, 因此, 作为教学引入环节的视频, 其中应包含有大量可供学生提取的数据。

问题适当, 引导到位, 有了比较明确的思路和方向, 学生的合作与探究才能取得交际效果。

应用软件教学首先要解决的问题就是要让学生明白软件的核心功能是什么, 用这个软件主要解决哪些方面的问题, 它与其他软件的联系和区别在哪里。

在信息技术学科教学中, 学生会遇到很多软件, 有些软件的功能很多、很强, 但这些功能中, 有些是核心功能, 有些是辅助功能, 有些纯属装饰和摆设, 教学中要注意提取软件的核心功能, 避免舍本逐末。

通过对比, 加强对学习内容的理解, 学生学习效果明显, 教学效率提高。

在学习中, 尽管学生的主体地位至关重要, 但教师的作用也不容忽视。

要让教学评价能够充分发挥对学生学习的促进作用, 评价标准的制定是一个关键环节。

评价标准需要具体、可行, 针对性强, 评价的结果才能客观、有效。

如果教师都能这样设计教学, 信息技术学科教学质量将会大大提高。

参考文献

[1]中华人民共和国教育部.关于印发《基础教育课程改革纲要 (试行) 》的通知[S].教基[2001]17号

[2]中华人民共和国教育部.关于印发《中小学信息技术课程指导纲要 (试行) 》的通知[S].教基[2000]35号

Excel数据分析 篇8

关键词：Excel软件,数据管理,数据采集,数据分析

数据建设是信息化建设的核心,是数据分析和知识挖掘等决策支持的基础。大型企业采用大型数据库管理系统进行数据管理,采用专业的数据分析软件如SPSS和SAS等进行数据分析,从海量数据中提取有用的知识,为决策提供支持。对数据量不大的的广大小型企业来说,可以利用Excel这一常用办公软件进行数据管理和数据分析。

1 Excel数据管理理念

作为一种大家都十分熟悉的办公软件,Excel以强大的制作电子表格的能力闻名,除了制作电子表格以外,Excel还是一种方便易用的数据管理工具。在Excel97-2003版本中,一张Excel表能容纳65536行数据,Excel 2007以后的版本,一张表能容纳数据1048576行,如果安装第三方控件,可以对几百万行的数据进行处理,从性能上来说,Excel完全具备了管理数据的能力。

将Excel作为一个小型的数据管理系统,需要将数据管理的理念融入到数据录入、管理的全过程之中。用Excel管理数据,真正困难的地方是初始数据录入、数据处理的问题,没有统一的数据管理理念和数据处理规范结果将导致一个文件夹里表格无数,却无一可再用,每次有新的需求就要重复工作,耗时耗力。针对这种情况,设计一个用Excel表管理数据的应用框架,将录入数据的表格设计成3个表:源数据表、分类汇总表和参数表。其中,源数据表是将来进行数据分析的来源,是填满数据的一维明细表,具有通用、简洁、规范的特点;参数表用来放置数据分析的参数;汇总表放置数据分析的结果。3个表中,源数据表是最重要的,先设计一个标准、正确的源数据表,然后从中“变”出多个分类汇总表。根据这3个表,所有数据、需求、结果都可以囊括其中,进可出结论、退可查源头。在Excel中采用3个表来管理数据的理念,可简称为“三表理念”。

1.1 源数据表

准备工作的重要性是第一位的。把所有初始数据分条缕析,按照类别、关系、重点一一录入,同时保证其无格式的正确性,这样所有的需求分析才可以准确无误地从源数据里获取。这一点需要对整个项目有全局性的了解,能够把每一项实际业务总结为数据显示,不可能苛求第一次就可以把所有的数据纳入其中,但是保证源数据表没有其他数据的话,即使下次有增添修改,也不会牵一发而动全身,不会影响过去的结果。

归纳起来,一张源数据表需满足如下条件:有满足分析需要的字段,数据字段一定要全面;没有表格名字;单层表头;没有空行空列;没有合计行;没有合并单元格;没有空白单元格;一格一属性;数据格式正确。只有保证源头正确,数据分析结果才能正确,设计一张正确全面的源数据表是数据管理和分析最重要的一步。

1.2 参数表

源数据获取后,就需要设置参数来得出结论。在Excel表中其实就是对应数据的名称即可,这样应用vlookup等函数,调用参数表里的参数与源数据对应关系,即可得出相关的结果。当然参数表的设置也是需要对项目的理解,源数据表和参数表这两个表其实是同时进行的,只不过放在不同的表里罢了。

1.3 汇总表

作为源头的源数据表有了,作为工具的参数表有了,剩下的就是结果了。为了让参数、源数据可以重复使用,避免“污染”,需要把结果单独作为一个表,一个需求一个汇总表,这样每一次工作需求,只不过简单地调用或增加参数,新建一个汇总表罢了。

2 数据采集和数据处理

“三表理念”是采用Excel进行数据采集和管理的重要原则,数据采集利用三表中的源表来进行实现。数据处理是指对收集到的数据进行加工整理,形成适合数据分析的形式,是数据分析前必不可少的阶段。数据处理是从大量的、杂乱无章、难以理解的数据中,抽取并推导出对解决问题有价值、有意义的数据。数据处理是数据分析的基础,通过数据处理,将收集到的原始数据转换为可以分析的形式,并且保证数据的一致性和有效性。数据处理主要包括数据清洗和数据加工,数据清洗包括重复数据的处理、缺失数据的处理、错误数据的处理。在Excel中利用筛选、函数等方式能实现数据清洗。

数据加工是数据转化、数据提取和数据计算等处理方法,采集来的原始数据需要进行一定的处理才能进行后续的数据分析工作。在Excel中数据计算采用公式和函数计算这种方式进行。函数是Excel中非常强大的一个功能,几乎所有的查找、统计和分析工作都可以通过函数来完成,总共有几百个之多,常用的函数有20~30个。日常应用的Excel管理不需要特别复杂的各种函数调用,用得最多的是加总、峰值等查询。

数据提取则可以采用Microsoft Query工具利用SQL语句对数据进行抽取。SQL语句是结构化查询语言,是一种通用的关系型数据库操作语言。Microsoft Query是用于将数据从外部数据源检索到Excel中的一个程序,可以从包括Access、SQL Server、Oracle、文本文件和Excel中检索数据到Excel中。对于几百万行的数据,可以利用第三方控件Powerpivot对数据进行处理,采用Power Pivot可对数据进行数据处理,如数据导入、数据合并、数据计算、数据分组等。

3 数据分析

数据分析是指用适当的统计分析方法对收集来的大量数据进行分析,将它们加以汇总和理解并消化,以求最大化地开发数据的功能,发挥数据的作用。数据分析是为了提取有用信息和形成结论而对数据加以详细研究和概括总结的过程,目的是把隐藏在一大批看似杂乱无章的数据背后的信息集中和提炼出来,总结出所研究对象的内在规律。在实际工作中,数据分析能够帮助管理者进行判断和决策,以便采取适当策略和行动。

数据分析分为描述性数据分析、探索性数据分析和验证性数据分析。描述性数据分析属于初级数据分析,探索性数据分析侧重于在数据之中发现新的特征,而验证性数据分析则侧重于验证已有假设的真伪证明。在Excel中,数据排序、筛选和分类汇总是最基本的数据分析,此外,数据透视表和数据分析组件是两种非常强大的数据分析工具。

3.1 数据透视表

数据透视表是用来从Excel数据列表、关系数据文件或OLAP多维数据集等数据源的特定字段中总结信息的分析工具。它是一种交互式报表,可以快速分类汇总比较大量的数据,并可以随时选择其中页、行和列中的不同元素,以达到快速查看数据源的不同统计结果,同时还可以随意显示和打印出用户所感兴趣区域的明细数据。数据透视表有机地综合了数据排序、筛选和分类汇总等数据分析方法的优点,可以方便地调整分类汇总的方式,灵活地以多种不同方式展示数据的特征。一张“数据透视表”仅靠鼠标指针移动字段位置,即可变换出各种类型的报表。同时,应用数据透视表也是解决Excel函数公式速度瓶颈的重要手段之一。数据透视表是Excel中最常用、功能最全的分析工具之一。

3.2 数据分析组件

为方便进行数据统计分析,Excel提供了一个数据分析加载工具-“分析工具库”,相比较专业的统计分析软件SPSS、SAS,Excel分析工具库具有如下优点:与Excel无缝结合、操作简单、容易上手;聚合多种统计函数,在生成结果时,生成相应图表,有助于对统计结果的理解;提高分析效率,降低出错的概率。不足之处在于数据处理量有限,只能处理简单的统计分析,对大型数据或复杂的统计分析,需使用专业的统计分析软件。利用Excel的“分析工具库”,可以进行描述性统计分析、相关分析、回归分析和预测分析等。

描述性统计分析是现状分析,采用Excel分析工具库中的“描述统计”分析工具来进行分析,主要指标有样本的平均数、中位数、众数、标准差、方差等,描述分析对象数据的集中程度和离散程度等。“直方图”是另一种描述性分析工具,用于展示分组数据分布的一种图形,可以直观地看出数据分布的形态、数据分布的中心位置及数据分散的程度,可据此判断数据是否符合正态分布。

数据之间的发展变化受与之相联系的其他现象发展变化的制约与影响,这种依存关系在统计学中可以分成相关关系和回归函数关系两大类。相关关系是现象之间存在的非严格的、不确定的依存关系,其原因是影响现象发生变化的因素不止一个。回归函数关系是指现象之间存在的依存关系中,对于某一变量的每一个数值,都有另一变量值与之相对应,这种依存关系可用一个数学表达式反映出来。这两种依存关系在Excel中可用分析工具库中的“相关系数”和“回归”进行分析。另外,分析工具库还可进行基于历史数据的推断预测分析,预测包括移动平均法、指数平滑法等,利用Excel分析工具库都能方便地实现。

4 数据展现

图表是最直观展现数据的一种形式,具有表达形象化、突出重点的特点,是数据展现的最好方式。大部分的数据间关系可以归纳为以下6种类型:成分、排序、时间序列、频率分布、相关性、多重数据比较。在Excel中用图表展现数据的一个重要原则就是简洁,针对数据的6种关系,从饼图、柱状图、条形图、拆线图等图形中找到最能体现数据间关系的图形展现数据。

水晶易表是一种优秀的数据可视化工具,可将静态的Excel模型转变成生动的数据可视化展示。根据数据展现需求,在建立好的Excel数据报表或模型的基础上,结合相应的数据展现部件,通过鼠标简单的拖拽及关联和设置,将沉闷的数据结果生成、清晰、直观地展现出来。

5 结语

Excel是一种常用的办公软件,在数据量不大的情况下,可以利用Excel进行数据管理和数据分析,“三表理念”是Excel管理数据的重要理念,数据采集、数据管理、数据分析和数据展现是Excel管理和分析数据的4个重要步骤,利用Excel自带的工具和第三方控件能方便地实现数据管理和数据分析。充分利用好Excel这一常用工具,是广大中小企业进行数据分析很好的一个解决方案。

参考文献

[1]伍昊.你早该这么玩Excel.北京联合出版公司,2014.

[2]Excel Home.Excel 2010数据透视表应用大全.人民邮电出版社,2013.

灵活运用Excel分析学生成绩 篇9

1 数据录入

1.1“0”开头数字的录入

数据录入是统计成绩的基础。对于以“0”开头的学号数据(如“0851201”)，若在Excel的单元格中直接输入“0851201”(不包括引号，下同)，则会显示“851201”，前端的“0”被自动舍去。解决这个问题的方法有两种，一种是对单元格进行格式的设置，即将其设为文本格式。具体操作法是选中所有需要输入前端带“0”的单元格，然后单击右键，在弹出的快捷菜单中选择“设置单元格格式...”命令，在弹出的“单元格格式”对话框中单击“数字”选项卡，再在“分类”列表框中选择“文本”，最后点“确定”按钮即可。另一种方法是在单元格中先输入一个半角单引号(')，然后再输入数据。例如：要输入学号0851201,方法是输入“'0851201”，输入完后单引号会自动消失，此时Excel会把带有单引号的数据自动转换为文本数据。值得注意的是，文本数据不具备数值数据的特性，不可以进行运算等操作。

1.2 0～100之间数据的限定

学生的成绩一般只能在0～100分之间，Excel提供了一个“有效性”功能，可以防止由于录入错误而使成绩在0～100分以外。具体操作方法为：选中需要输入成绩的单元格，单击“数据”菜单中的“有效性”命令，在弹出的“数据有效性”对话框中对“设置”选项卡各选项进行设置，如图1所示，最后点击“确定”按钮即可。

进行以上设置后，如果在相应的单元格中输入了0～100以外的数据，则会出现提示信息，提醒数据录入者注意，从而保证了录入数据的准确性。

1.3 冻结窗口

学生成绩表通常数据量较大。随着数据的输入，行数的增加，成绩表标题栏很快就被移动到工作表可视区以外，不利于对工作表的编辑工作。解决这个问题的方法是：单击标题栏下面一行的行号，如“3”，以选定该行，再单击“窗口”菜单中的“冻结窗格”命令。此时第1、2两行已被冻结，不能被移动。这大大方便了对工作表的编辑工作。倘若要取消对标题栏的冻结，则可单击“窗口”菜单中的“撤销窗口冻结”命令即可。

2 成绩分析

2.1 总分和平均分的统计

每次考试后,教师都需要对学生的总分和平均分进行统计计算，我们可以利用Excel提供的SUM函数和AVERAGE函数来实现，以“08512班学生成绩单”为例。

1)在F3单元格输入公式“=SUM(C3:E3)”,按回车键后即可计算出01号学生的总分。

2)同上述的方法，在G3单元格中计算01号学生的平均分，此时的公式为“=AVERAGE(C3:E3)”。

3)拖动鼠标选择F3、G3两个单元格，并将鼠标移至G3右下角的“填充柄”上，待鼠标指针变成“+”时，向下拖动鼠标，完成其他学生总分和平均分的计算，如图2所示。

2.2 根据总分排名

关于排名次的问题，有些老师的做法是先将总分按大到小进行排序，然而利用填充柄输入名次，再按学号的顺序排序后还原，这样做固然很烦琐。Excel中的RANK函数可以方便地解决这个问题。

在H3单元格中输入公式“=RANK(F3,$F$3:$F$14)”，按回车键后即可计算出01号学生按总分在全班的排名情况。利用填充柄工具，向下拖动鼠标完成其他学生的排名计算。需要说明的是，在公式中出现一个“$”符号，是绝对地址的引用(当复制公式时，F3:F14的范围始终保持不变)。若不使用“$”符号，F3:F14的范围将会随着公式的变化而发生改变，从而产生错误的排名。快速输入“$”符号的方法是：在输入“F3”后按一下F4功能键，即可变为“$F$3”。

2.3 成绩分布统计

在分析考试成绩的时候，经常要对<60,60～69、70～79、80～89、≥90分数段的人数进行统计。利用FREQUENCY函数可以非常高效的来实现这一功能。

FREQUENCY函数的作用是计算数值在某个区域内的出现频率，然后返回一个垂直数组。由于函数FREQUENCY返回一个数组，所以它必须以数组公式的形式输入，即输入完公式后，按“Ctrl+Shift+Enter”组合键形成数组公式。该函数的语法为FREQUENCY(data_array,bins_array)。Data_array是一个数组或对一组数值的引用，要为它计算频率;Bins_array是一个区间数组或对区间的引用，该区间用于对data_array中的数值进行分组。Data_array中的元素个数比Bins_array中的元素个数多1个。多出来的元素表示最高区间之上的数值个数。对“08512班学生成绩单”文件的处理如下：

1)在B16:B20单元格中输入说明性文字“<60,60～69、70～79、80～89、≥90”。

2)在F16:F19单元格(可以是任意单元格输入)中输入学生成绩的分段点，分别为59、69、79和89。即统计<60,60～69、70～79、80～89、≥90五个分数段的人数。

3)选中要进行分段统计的单元格区域C16:C20，输入公式“=FREQUENCY(C3:C14,F16:F19)”。

4)输入完上述内容后，按下“Ctrl+Shift+Enter”组合键以形成数组公式，此时在公式的两端会出现数组公式标志，即一对大括号“{}”。

5)在C16:C20单元格区域便得到所需的统计结果，如图3所示。

3 结束语

本文根据个人经验介绍了利用Excel对学生成绩进行统计分析的一些方法与技巧。通过这些技巧的应用，可以简便和高效的完成学生成绩统计工作，从而使教学工作更加简单、高效。当然Excel的功能远不止这些，需要我们不断的去挖掘。

摘要：考试结束后,通过对学生考试成绩的分析,教师可以对该课程的教学工作进行系统的总结,更好的安排下一阶段的教学工作。该文根据个人经验,介绍了如何灵活运用Excel分析学生成绩的方法与技巧,从而使教学工作变得更加简单、高效。

关键词：文本数据,数据有效性,冻结窗口,函数

参考文献

[1]杜茂康.Excel与数据处理[M].北京:电子工业出版社,2002.

[2]汉龙.中文版Excel2003基础应用与提高[M].上海:上海科学普及出版社,2006.

[3]韩小良.Excel2003/2007公式与函数的使用艺术[M].北京:中国铁道出版社,2009.

[4]王月明,田海宇,王力群.Excel公式和函数在学生成绩管理中的应用[J].中国科技信息,2008(7).

用Excel做聚类分析 篇10

聚类分析法是一种多元统计方法, 主要有迭代聚类法和分层聚类法。这种分类法是以所研究对象之间在相似程度上的大小进行归类, 把相似程度大的归为一类。按照研究对象的不同, 聚类分析法又分为R型聚类分析和Q型聚类分析。R型聚类分析主要是对变量进行分类, Q型聚类分析主要是对样本进行分类。这两种聚类方法都可以用Excel来实现。通过Excel进行数据处理与QExcelExcel统计分析, 为使用者提供了方便。统计分析, 为使用者提供了方便。

2模糊聚类分析的步骤

2.1 样本模型标准化

1) 将n个分类对象的m种指标去量纲, 然后在Excel中表示出来。注意如果该n个对象的某一相同指标全部为0, 应去除该种1) nmExceln指标, 以免使后期处理的数据发生错误。

将样本数据标准化, 可以采用极差规格化法、标准差规格化法、均值规格化法、极大值规格化法等。

1极差规格化法的模型为:

式中:xij——表示第i个对象的第j种指标;

x'jmin——表示第j种指标的最小值;

x'jmax——表示第j种指标的最大值;

2标准差规格化法的模型为[1]:

式中:——表示第j种指标的平均值;

Sj——表示第j种指标的标准差;

3均值规格化法的模型为:

4极大值规格化法的模型为:

通过上述方法中的一种建立矩阵为:

2) 例题

给定几种常见水果的营养成分进行聚类分析。

用Excel自带的函数求均值和标准差。选取A15单元格, 在工具栏中选择求平均值按钮, 在单元格中鼠标选定A1到A14单元格, 或者在编辑栏中输入公式“=AVERAGE (A1:A14) ”, 最后在键盘按“Enter”键, 即在A15单元格出现A列的平均值, 用数据填充求得B15:J15各列的平均值。同理可求得A16:J16的标准差。填充的方法是重新选取A15, 将鼠标放在该单元格右下角, 当鼠标变成黑色实心“+”后, 按住鼠标向右或者下拖拽即可[2]。

选取标准差公式进行计算。选A18单元格, 在编辑栏内输入公式“= (A1-A$15) /A$16”, 对其他单元格进行填充, 得到矩阵X'中的各个元素。如表2。

2.2 建立模糊矩阵

1) 根据相似系数rij的不同, 有多种求距离的方法, 如:欧式距离法、海明距离法、切比雪夫距离法、相关系数法等。

选择其中一种方法可得模糊矩阵:

2) 接上例

选取海明距离|进行模糊矩阵的算法。选A33单元格, 在编辑栏输入计算公式“=SQRT (ABS (A$18-A18) +ABS (B$18-B18) +ABS (C$18-C18) +ABS (D$18-D18) +ABS (E$18-E18) +ABS (F$18-F18) +ABS (G$18-G18) +ABS (H$18-H18) +ABS (I$18-I18) +ABS (J$18-J18) ) ”, 得出d11;选取B33单元格, 在编辑栏输入“=SQRT (ABS (A$19-A18) +ABS (B$19-B18) +ABS (C$19-C18) +ABS (D$19-D18) +ABS (E$19-E18) +ABS (F$19-F18) +ABS (G$19-G18) +ABS (H$19-H18) +ABS (I$19-I18) +ABS (J$19-J18) ) ”, 得出d12;选取C33单元格, 在编辑栏输入“=SQRT (ABS (A$20-A18) +ABS (B$20-B18) +ABS (C$20-C18) +ABS (D$20-D18) +ABS (E$20E18) +ABS (F$20-F18) +ABS (G$20-G18) +ABS (H$20-H18) +ABS (I$20-I18) +ABS (J$20-J18) ) ”, 得出d13;同理求出D33, E33, F33, G33, H33, I33, J33, K333, L33, M33, N33, 其余进行填充即可。

由d值矩阵求模糊矩阵, 选A48单元格, 在编辑栏输入“=1-A33/MAX ($A$33:$N$46) ”, 点击Enter键得出r11, 在其他单元格进行填充, 得到模糊矩阵R如表3。

2.3 求模糊关系矩阵

1) 对模糊矩阵R进行自乘运算, 知道出现最小的k值, 使Rk=R2k, 此时的Rk矩阵为模糊关系矩阵。

2) 接上例

将上述模糊矩阵R复制到新建Excel表格中, 选取A16单元格, 在编辑栏内输入公式“=MAX (MIN ($A1, A$1) , MIN ($B1, A$2) , MIN ($C1, A$3) , MIN ($D1, A$4) , MIN ($E1, A$5) , MIN ($F1, A$6) , MIN ($G1, A$7) , MIN ($H1, A$8) , MIN ($I1, A$9) , MIN ($J1, A$10) , MIN ($K1, A$11) , MIN ($L1, A$12) , MIN ($M1, A$13) , MIN ($N1, A$14) , ) ”, 在其他单元格进行填充, 得到R2, 如此重复进行, 直到R4=R2×4, R4为所求模糊关系矩阵。

2.4 聚类分析

1) 取λ值满足0≤λ≤1, 将模糊关系矩阵中的值大于λ的归为一类, 统一标记为1, 值小于λ的标记为0。归为同一类的对象之间的关系较为亲密, 相似程度大。不同类的对象之间关系比较疏远, 相似程度小。

2) 接上例

将数值大于0.5的标记为1, 其余为0, 得到结果如下。

由表5可知这些常见水果的分类为[1], [2, 3, 4, 5, 6, 8, 13], [7], [9], [10], [11], [12], [14]。每一类中的多个对象的各个指标相近, 在一定的情况下, 可相互替代。

3总结

Excel软件具有强大的数据分析统计功能, 用Excel软件做聚类分析不单单只有这一种方法, 如Excel自带的宏功能[3]也可以实现聚类分析, 笔者这里发现的方法只是就冰山一角, 还有很多方法等着大家去发现和研究。

参考文献

[1]徐永安, 徐恒玉.用Excel实现模糊聚类分析[J].安徽农业科学, 2005, 33:740-742.

[2]春乃芽.利用Excel实现R型聚类分析[J].物探与化探, 2007, 31 (4) :374-376.

利用Excel进行市场预测分析 篇11

[关键词] 市场预测 Excel 一次指数平滑 回归分析

在市场经济条件下，企业要生存与发展，必要前提之一是做好市场调查与预测，以便把握瞬息万变的市场，及时调整经营策略，因而，市场预测是企业经营管理的重要组成部分。

一、市场预测的概念

所谓市场预测，就是运用科学的方法，对影响市场供求变化的诸因素进行调查研究，分析和预见其发展趋势，掌握市场供求变化的规律，为市场营销决策提供可靠的依据。

二、市场预测的方法

市场预测的方法很多，由粗略的估计，到比较精确的预测，有定性分析方法，也有定量分析方法。

常用的定性分析方法有：购买者意向调查法，销售人员综合意见法，德尔菲法，市场试验法。

常用的定量分析方法有：时间序列分析法，回归分析法。

其中，时间序列分析法包括：简单平均数法，加权移动平均法，指数平滑法。

回归分析包括:一元回归，二元回归，多元回归、线性回归和非线性回归等分析方法。

三、使用Excel 进行市场预测

Excel作为一个优秀的电子表格处理软件，它不仅具有数据存储的功能，还提供了复杂的公式和函数对数据进行各种运算，甚至可以进行一些数据分析工作，被广泛地应用于各种管理、数据统计、表格制作等业务领域。

1.指数平滑法

例1:某公司前几年的销售统计资料如表1所示，求出2006年的销售预测值。

下面利用Excel分析工具中的一次指数平滑选项，求出2006年的销售预测值。

操作步骤:在Excel工作表中输入以上表1中数据，两行或两列（年份和实际销售量），选择工具菜单中的数据分析命令，此时弹出数据分析对话框。在分析工具列表框中，选择指数平滑选项。 这时出现指数平滑对话框，如图１所示

在输入区域指定数据所在的单元格区域B1:B9；因指定的输入区域包含标志行，所以选中标志复选框；在阻尼系数指定加权系数0.9，因设(α=0.1)。在输出选项框中指定輸出选项。本例选择输出区域为当前工作表以C2为左上角的单元格区域；选中图表输出复选框。单击确定按钮。 这时，Excel给出一次指数平滑值，如表2（第三列预测销售量，α=0.1(α为加权因子或平滑系数)。所以从表中得出该公司2006年的预测销售量为47.2268。

2.回归分析法

回归分析是一种数据统计方法，是建立在大量实际数据基础上，寻求随机性现象的统计规律的一种方法。通过对预测对象的数据分析，可以找出变量之间的相互依存关系，这种关系叫相关关系。下面介绍一元线性回归法。

一元线性回归公式为:y=a+bx

式中:y——因变量；

x——自变量；

a——系数（截距）；

b——回归系数。

x与y的变化关系，将在回归系数的制约下呈现出规律性的变化。手工计算一般依据最小二乘法，推导出系数a，b的估计值，并求得。这样做计算量较大。

现在我们用Excel，求得回归方程。

例２:某企业月产值与某零件需用量的数据如表３所示，试建立月产值(X)与零件需用量(y)的回归方程，并预测出月产值为20万元时零件的需用量。

操作步骤:如图２所示

把x、y数据分别输入二列中，点击这个数据表中任一单元格，然后插入-图表，图表类型选xy散点图，子图表可任选一个(平滑线散点图)，再按下一步 ，下一步，下一步，完成，最后点击完成。在生成的图中右击数据线，在出现的下拉快捷菜单中点击添加趋势线，在类型中选线性（因你的方程是一次线性方程），在选项中选显示公式（并勾选“显示R平方值，这是相关系数，表示线性程度）最后按确定。这时在图中出现的公式就是你要的回归方程。

Y=0.8243x+2.1486

把x=20代入，Y=0.8243*20+2.1486=18.6346

预测出月产值为20万元时，零件的需用量是18.6346箱。

上述利用图表法求一元回归方程，也可以用相关函数求一元回归方程:

相关系数：R=CORREL(X，Y)

斜率:a=SLOPE(Y，X)

截距:b=INTERCEPT(Y，X)

线性拟公式：Y=TREND(Y，X，x)

例2中求x，y间的斜率：

a=SLOPE(B2:B6，A2:A6)=0.824324

求x，y间的截距：

b=INTERCEPT(B2:B6，A2:A6)= 2.148649

求x，y的线性相关系数：

R=CORREL(A2:A6，B2:B6)=0.923183

求y1:

y1=TREND(B2:B6，A2:A6，A7)=18.635

参考文献:

[1]黄渝祥:企业管理概论[Ｍ]．北京：高等教育出版社，2006

[2]刘建臣李文武:信息技术教程[Ｍ]．北京：电子工业出版社，2001

Excel数据分析 篇12

一、使用“数据分析”功能的基本方法

Excel提供了“数据分析”工具包，包含常用的方差分析、描述统计、直方图、回归分析、t-检验等分析工具。若要使用这些工具，应先单击“工具”菜单中的“数据分析”。首次调用，需先加载宏“分析工具库”。步骤如下：

1.在“工具”菜单上，单击“加载宏”。

2.在“可用加载宏”列表中，选中“分析工具库”框，再单击“确定”。出现“数据分析”菜单。

3.选择“工具”菜单中的“数据分析”，出现“数据分析”对话框，单击要使用的分析工具的名称，再单击“确定”。

二、分析学生成绩绘制频率直方图的基本步骤

我们以2010年级某专业同学某门课程期末成绩为例，我们来讨论绘制频率直方图的主要步骤：

（一）输入数据，进行区域排序

为方便起见，将82个数据以矩阵形式输入到学生成绩统计表中，存入Excel的工作表中的适当区域。

学生成绩原始数据：

将学生成绩数据存放后，利用SMALL函数进行排序，排序公式SMALL（数据存放区域，ROW (Amn）*k+column (Amn）-mk-（n-1））, Amn为存放第一个数据所在位置，k为排序后每一行存放数据个数。任选新的区域存放排序后数据，选横拉k个数，再下拉，直到数据排序完毕。得到如下成绩表：当然也可以把学生成绩输入为一列，再利用Excel排序功能进行排序。

（二）点击工具数据分析描述统计，选定原始数据列存放形式，任选结果输出区域，得到统计描述结果

从中看出，82个学生平均成绩为73.89分，方差为307.18.

（三）计算极差R

最大的数据减去最小的数据

R=xmax-xmin=99-18=81

(四) 分组

1. 根据要求，不及格为0～59分，及格60～75，良好

左端点，右端点为b，所表示区间范围：

（五）生成频数分布表和直方图

1. 打开“工具/数据分析”，在分析工具窗口中选择“直方图”；点击确定。

2. 在直方图弹出窗口“输入区域”输入数据方阵“82位学生成绩数据位置”：

在“接收区域”输入分组右端点所在列区域：

3. 在输出选项中，点击“输出区域”，表示频率数表输出存放区域，位置任选。

4. 在输出选项中，点击“图表输出”。其他选项，若没需要，可以不选。

点击“确定”按钮，立即显示如下所示的频率分布表（表1）和直方图（图1）。

注：1.此图表中的“频率”实乃频数，所以应将表中“频率”改为“频数”，接收区改为相应分组文字，得表2

2.直方图是按照左开右闭的方式对落在各区间的数据进行频数统计绘制的；

3.从直方图来看，学生成绩基本符合正态分布。

（六）修改频率分布表

另存表（2）为表（3），将表（2）“频率”改为“频数”，并在频数列右侧增加一列频率，计算频率。即频率=小组频数，本例中，只需计算第一组的频率，下拉即可数据总数

得这列频率数据值。修改后频率分布表见表3

(七) 完成频率分布直方图

将表（2）频数列替换成表（3）频率列，点击右键数据标志格式，得到频率分布直方图（见图2）。

此种绘制方法可以用在工程上，可以作进一步修改，使之符合我们使用要求。

我们还可以用Excel解决回归分析、t-检验等各种统计问题，无需编程序就可以进行工程上的复杂计算。Excel相对简单易学，这对于我们增强职校学生动手解决实际问题能力，是非常有实用价值的。

参考文献