1 / 118

数学建模培训 —— Excel 的应用技巧

数学建模培训 —— Excel 的应用技巧. 主讲：曹清洁. 电子表格的历史. 表格处理软件（ spreadsheet ） 表格处理软件能对文字和数据的表格进行编辑、计算、存储、打印等，并具有数据汇总、分析、制图等功能，常用的表格处理软件有 Excel 和 Lotus1-2-3 。一部 Spreadsheet 软件的演化史就是一部轰轰烈烈、一波几折、王朝更迭、情感交织的历史. VisiCalc.

jennifer-jenkins
Download Presentation

数学建模培训 —— Excel 的应用技巧

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 数学建模培训——Excel的应用技巧 主讲：曹清洁

  2. 电子表格的历史 • 表格处理软件（spreadsheet） • 表格处理软件能对文字和数据的表格进行编辑、计算、存储、打印等，并具有数据汇总、分析、制图等功能，常用的表格处理软件有Excel和Lotus1-2-3。一部Spreadsheet软件的演化史就是一部轰轰烈烈、一波几折、王朝更迭、情感交织的历史

  3. VisiCalc • 1979年 Dan Bricklin 和Bob Frankston 在 “苹果Ⅱ”计算机的一个叫VisiCalc（可视化计算器）的程序；1981年他们发布了支持“命令行宏”的VisiCalc扩展版；1985年该公司由于法律纠纷倒闭。

  4. Lotus1-2-3 • 1983年 Mitch Kapor设计了Lotus1-2-3，第一年销售额达到5300万美元，其核心思想是围绕VisiCalc展开的；1985年推出Lotus1-2-3第2版，提供8192行、256列，可以方便简单的录制宏；它给用户一张足够大的电子数据表，并以此为基础，将数据表、数据库管理和图形处理功能集于一身，该软件在20世纪80年代到90年代初几乎垄断了这个市场，成为了会计标准；但是该公司在20世纪90年代初犯了战略性错误，重点转向开发CP/M操作系统上的Lotus版本，没有及时开发出能很好的支持windows操作系统的版本，市场份额锐减。目前是IBM旗下莲花软件（LOTUS）公司的产品。

  5. 20世纪90年代初，微软推出了Excel；1990年Excel3.0还不如Lotus1-2-3，但是每1到2年就改进一次；1992年Excel4.0提供了XML宏语言；1993年Excel5.0引入一个工作薄放置多个工作表的概念，并提供VBA宏语言；20世纪中期Excel95和Excel97进一步拓展了“数据透视表”、“自动筛选”和“自动分类汇总”等功能，取代了市场上Lotus1-2-3的位置。 • Excel把数据表、图表和数据库等功能有机地组合在一起，为用户提供了一个集成操作环境。它继承了windows的优秀风格，具备窗口、菜单、对话框和图标，并且增加了工具栏和快显菜单。Excel采用了新的工作簿形式，即三维电子表格，给数据操作提供了更广阔的空间。它使用先进的智能感知技术，简化了操作，而且可以对用户的要求智能地作出反应。Excel被推出后，迅速在世界范围内受到欢迎和广泛应用。 • 目前Excel已经成为了一个集数据库软件、数学运算软件、财务软件、运筹学（管理科学）软件和统计学软件于一体的综合性软件。而且大量加载宏软件拓展了其各方面的功能，加载到Excel上的比较著名的统计软件有XLSTAT（http://www.xlstat.com/en/home/）、Analyse-it（http://www.analyse-it.com/）等

  6. Excellent + Cell. “Cell” 是“单元格”, 是Excel表格处理的最基本组成部分. Microsoft 取了“Ex-cel” 这个名字, 一语双关, 充分表达了Excel是一个优秀的表格处理文件，同时也为了超越其他的电子表格软件.

  7. CCED是一款中文字表处理软件，由原北京乾为天电子技术研究所开发，主要开发者是朱崇君。问世于1988年，在DOS时代曾是中国最流行的文字处理与电子表格软件之一。1998年以后推出了Windows版本。CCED是一款中文字表处理软件，由原北京乾为天电子技术研究所开发，主要开发者是朱崇君。问世于1988年，在DOS时代曾是中国最流行的文字处理与电子表格软件之一。1998年以后推出了Windows版本。 • 上世纪九十年代中期，DOS版的CCED一度辉煌，连续多年是中国最畅销的软件之一，是全国计算机等级考试以及国内不少计算机教育培训指定内容。九十年代后期，Windows操作系统普及。CCED虽然推出了Windows版本，但在功能和界面上与Microsoft Office等成熟的“所见即所得”办公软件相比劣势显现。尤其在盗版Microsoft Office流行、老对手WPS借中国政府采购计划恢复生机的情况下，CCED市场迅速萎缩，已淡出主流软件市场。

  8. Excel基本知识 • Excel在数学建模中的高级应用技巧

  9. Excel基本知识 • 手动输入数据 • 自动输入数据 • 公式的使用 • 函数的引用 • 数据管理与分析

  10. 手动输入数据 • 文本输入默认方式为“左对齐”.由数字组成的字符串前加一个’号 . • 数值输入默认“右对齐”方式.输入分数时，要先输入“0”和空格，否则作为日期型数据处理。

  11. 自动输入数据 • 自动填充鼠标拖动填充柄向下、右拖数字增量为1，向上、左拖增量为-1 . • 自定义序列 ① 工具——选项——新序列——输入序列内容，每个序列间按回车键——添加 ② 选中序列区域——工具——选项——导入——确定

  12. 公式的使用 用公式计算 所有的公式运算均以“=”开头。错误的公式以#开头。 （1）数学运算符： +、-、*、/、^、%（计算准则：先乘方，再乘、除，后加、减） （2）文本连接符：&——将两段用双引号（英文状态下）括起的文本连接为一段连续的文本。 （3）比较运算符：>、<、=、>=、<=、<>（用于比较两个数据的大小，比较结果是一个逻辑值，即TRUE（真）或FALSE（假））。 （4）引用运算符：冒号、空格、逗号 ①冒号：用于定义一个单元格区域，以便在公式中使用。 例：=Sum(A3：A7) ——A3至A7的内容之和 ②空格：交集运算符，表示只处理几个单元格区域之间互相重叠部分。 例：=Sum（A3：A5 A4：A7）——A4+A5的结果 ③逗号：并集运算符，用于连接两个或多个单元格区域。 例：=Sum(A3:A5,A4:A7)——A3、A4、A5及A4、A5、A6、A7之和(单元格有可能重复)。 （5）运算符优先级 （）——%——^——*、/——+、-、——&——比较运算符。（若优先级相同，则按从左到右的顺序计算）

  13. 函数的引用 • 手工输入：在编辑栏或单元格内输入 • 引用单元格地址:

  14. 相对引用 利用单元格间的相对位置关系引用单元格内容。公式中的相对引用随单元格的移动而修改，但原来的位置不变。 例：打开文件“数据编辑”，在工作表“电视机的销售统计表” 的G3中输入“=E3*F3，则可在G4、G5、G6、G7、G8中填入相应公式来计算商品的销售金额。 • 绝对引用 指引用单元格和被引用单元格位置关系是固定的，公式中的引用不随单元格地址变化而变化。 通常在地址前加“$”表示绝对引用。 例：打开文件“数据编辑”，计算其金额与利润值。 计算金额：在G3中输入：=E3*F3，并将之复制至G8。 计算利润：在I3中输入：=G3-（G3*$G$1+H3+E3），然后复制到I8 • 混合引用 即采用行为相对地址、列为绝对地址或列为相对地址、行为绝对地址来表示地址。

  15. Excel的数据管理和分析 1设计数据清单 2记录编辑 3排序数据 4筛选数据 5分类汇总数据

  16. 1 设计数据清单 数据清单的条件 • 工作表中只含有一张数据表 • 数据表应在第1行设置列标题 • 数据表不应含有空行或空列

  17. 2记录编辑 • 逐条显示 • 添加记录 • 删除记录 • 查询记录

  18. 3排序数据 • 排序依据： • 主要关键字 • 第二关键字 • 第三关键字 • 排序选项： • 自定义排序次序 • 区分大小写 • 排序方向 • 排序方法

  19. 4筛选数据 • 筛选种类：自动筛选、高级筛选 • 自动筛选方式： • 前10个 • 自定义

  20. 5分类汇总数据 • 汇总步骤 • 按汇总依据字段排序 • 指定汇总依据字段 • 设定汇总方式 • 指定汇总字段 • 汇总显示控制 删除汇总

  21. 数据透视表 • 功能 • 按多个字段分类汇总 • 步骤 • 指定数据源 • 选定数据区域 • 决定布局 • 确定位置

  22. 返回

  23. Excel在数学建模中的高级应用技巧 • Excel的数据处理功能 • 用Excel绘制图表 • 相关与回归分析

  24. 一、Excel的数据处理功能 1、Excel的函数 函 数的结构以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。 (1)函数以公式的形式出现，请在函数名称前面键入等号（=）。 如： =sum(A1,A4:A10,56)

  25. （2）使用函数向导插入函数：按fx即可调入粘贴函数对话框（2）使用函数向导插入函数：按fx即可调入粘贴函数对话框

  26. 例1：已知某班级学生成绩，统计其中某科成绩为80分（含）-90分（不含）的人数。例1：已知某班级学生成绩，统计其中某科成绩为80分（含）-90分（不含）的人数。 例2：计算 例3：计算 的值。 例4：求矩阵 的逆矩阵 注：鼠标先选中一块4*4区，先按F2键，再同时按下Shift+Ctrl+Enter三个键，则选定区域内出现逆矩阵的计算结果。

  27. 例5：利用自定义函数进行计算 （1）当x=3,2,1,0,-1,-2,-3时，计算分段函数 的值。 （2）用迭代法求非线性方程x-cosx=0的数值解，迭代公式是xk=cos(xk-1)，取x0=1,试用Excel计算，要求精度达到10-12。 （3）利用公式 计算 的近似值，使误差小于10-14。

  28. 2、Excel的数据分析功能 “数据分析”的统计分析工具包，包含方差分析、回归分析、协方差和相关系数、傅里叶分析等分析工具 首次使用时需要进行安装，方法如下： （1）点击“工具”----”加载宏”； （2）在弹出对话框中列出各种可以加载的项目，按照需要选择“分析工具库”、“规划求解”等等项目，点“确定”。 安装完后，“工具”菜单中多出了“数据分析”子菜单，点击它，弹出对话框，显示各种数据分析工具。

  29. 1、描述统计 数据分析功能： 主要统计数据的平均值、中位数、标准差、方差等等统计量 例6：某炼钢厂测了120种炉钢中的Si含量，得到120个原始数据，见Excel.

  30. 2、直方图分析工具 直方图是一大批数据的频率分布图，由直方图可以观察和分析数据的概率分布。见数据分析实例 直方图分析工具可完成数据的分组、频数分布与累积频数的计算、绘制直方图与累积折线图等一系列操作。以例7为操作范例，阐述直方图分析工具的统计整理功能，其操作过程如下。 例7：根据抽样调查，某月某市50户居民购买消费品支出资料。 （单位：元）

  31. 首先，将样本数据排成一列，最好对数据进行排序，本例中已利用排序操作排好序，为A1：A51。输入分组标志，本例中为B1：B10，分别是899、999、1099、1199、1299、1399、1499、1599、1699（如图所示）首先，将样本数据排成一列，最好对数据进行排序，本例中已利用排序操作排好序，为A1：A51。输入分组标志，本例中为B1：B10，分别是899、999、1099、1199、1299、1399、1499、1599、1699（如图所示） 然后 ，利用直方图分析工具进行分析，具体操作步骤如下。

  32. 第一步：单击“工具”菜单，选择“数据分析”选项；打开“数据分析”对话框，从“分析工具”列表中选择“直方图”选项，（如图1所示）第一步：单击“工具”菜单，选择“数据分析”选项；打开“数据分析”对话框，从“分析工具”列表中选择“直方图”选项，（如图1所示）

  33. 第二步：打开“直方图”对话框，确定输入区域、接收区域和输出区域，（如图所示）第二步：打开“直方图”对话框，确定输入区域、接收区域和输出区域，（如图所示）

  34. （1）“输入区域”输入待分析数据区域的单元格引用，若输入区域有标志项，则选中“标志”复选框；否则，系统自动生成数据标志。“接收区域”输入接收区域的单元格引用，该框可为空，则系统自动利用输入区域中的最小值和最大值建立平均分布的区间间隔的分组。本例中输入区域为$A$2:$A$51，接收区域为$B$2:$B$10。（1）“输入区域”输入待分析数据区域的单元格引用，若输入区域有标志项，则选中“标志”复选框；否则，系统自动生成数据标志。“接收区域”输入接收区域的单元格引用，该框可为空，则系统自动利用输入区域中的最小值和最大值建立平均分布的区间间隔的分组。本例中输入区域为$A$2:$A$51，接收区域为$B$2:$B$10。 • （2）在“输出”选项中可选择输出去向，输出去向类似于“抽样”对话框的输出去向。本例中选择“输出区域”为$C$1。 • （3）选择“柏拉图”可以在输出表中同时按降序排列频数数据；选择“累积百分率”可在输出表中增加一列累积百分比数值，并绘制一条百分比曲线；选择“图表输出”可生成一个嵌入式直方图。

  35. 第三步：单击“确定”按钮，在输出区域单元格可得到频数分布，（如图所示）第三步：单击“确定”按钮，在输出区域单元格可得到频数分布，（如图所示）

  36. 第四步：将条形图转换成标准直方图 （如图所示）

  37. 二、用Excel绘制图表 （以文件“图表”为例） 图表是数据表现的另一种形式。数据的图表化就是将单元格中的数据以各种统计图的形式显示。 1.图表种类 （1）独立的图表：单独占据一个工作表，打印时也将与数据表分开打印。 （2）附属于工作表的嵌入式图表：它和数据源放置在同一张工作表中，同时打印。 这两种表所依据的数据都来自工作表上选中的区域，数据变化，则图表随之变化。

  38. 2．图表术语 （1）数据系列：图表中决定图形Y轴取值的数值集合，对应工作表中的数据行。 （2）分类：图表中决定数据系列的X轴的标题值，对应工作表中的数据列。 （3）坐标轴：图表的一边。 （4）图例：定义图表的图形的含义。 （5）网格线：帮助确定数据点在Y轴或X轴刻度上的确切值。

  39. 3．创建图表 • （1）利用图表向导分四个步骤创建图表：选中区域——图表向导工具 • （2）按F11键快速创建图表：选中区域——F11

  40. 4．编辑图表 （1）选中图表元素：单击 （2）图表移动及改变大小：拖动鼠标 （3）改变图表图案、颜色、设置刻度等：双击图表 （4）改变图表类型：右键单击 （5）数据系列编辑 ①添加数据系列：选中区域—拖曳到图标；选中图表—图表—添加数据 ②删除数据系列：选中系列—Delete；选中系列—编辑—清除—系列 ③系列次序的调整：选中系列—格式—数据系列—系列次序 ④修改系列颜色：选中系列双击 （6）修改图表中的数据 ①修改图表中的数据：双击要修改数据的单元格—修改数据 ②修改图表中的图形：选中系列—有间断地单击结点—拖动鼠标 （7）增加图表标题及数据标志 ①双击某一系列 ②选中图表—图表—图表选项—标题（数据标志）

  41. 绘图实例： 用Excel绘制任意一元函数的图像 以 为例介绍：

  42. 三、用Excel进行相关与回归分析 1、用Excel计算描述统计量 2、用Excel进行相关分析 3、用Excel进行回归分析

  43. 三-1、用Excel计算描述统计量 （1）用函数计算描述统计量 （2）描述统计菜单项的使用

  44. （1）用函数计算描述统计量 • 常用的描述统计量有众数、中位数、算术平均数、调和平均数、几何平均数、极差、四分位差、标准差、方差、标准差系数等。下面介绍如何用函数来计算描述统计量。

More Related