02_Excel数据处理.ppt

《02_Excel数据处理.ppt》由会员分享，可在线阅读，更多相关《02_Excel数据处理.ppt（62页珍藏版）》请在三一办公上搜索。

1、第一章 Excel应用基础（Excel application fundament）,主要内容：Excel简介、运行Excel、建立工作表、工作表的公式计算、工作表的函数计算、数据统计图表、宏、数据管理的组织。教学重点：工作表的公式计算、工作表的函数计算、数据统计图表、宏,1.1 Excel简介,1.1.1Excel的主要功能(1)整个表格十分庞大 每页工作表都可达到256列16384行共4194304个单元。列号AZ、AA、AB、AZ、BA、BB、BC。IV 行号116384(2)表中每个单元都可以以公式的形式引用其它单元中的内容而显示的则是根据该公式自动进行计算所得到的结果。(3)操作十分

2、简便。(4)功能十分强大 1）根据数据列表自动生成各种二维和三维图形，而且还能在工作表中直接画任意形状的图形，并将其嵌入文件。2）具有很好的可编程性 用Visual Basic语言自动生成宏程序。3）具有强大的数据库功能 用户不仅可以储存大量的原始数据，而且可以有效地管理和组织数据。,1.1.2 Excel在食品行业中应用,(1)食品试验数据的处理 采用SAS、Excel等软件，利用方差分析、回归分析及图形处理等功能，进行食品科学研究的数据处理。（2）计算机辅助制图 采用AutoCAD等绘图软件进行工程图纸的绘制。（3）食品配方及仪器分析 用线性规划等方法进行食品配方的设计与优化，推出了一些食

3、品营养分析及配菜系统，出现了各种食品专业分析仪器及配套软件。（4）仿真技术 采用数学模拟、蒙特卡罗等方法，进行食品加工和贮藏过程、工程设计、企业管理、项目风险投资等模拟技术的研究。（5）企业信息化、电子政务、电子商务、办公自动化、财务自动化等，建立企业网站，进行企业宣传、产品展示、技术交流、供销、人力资源管理、MIS、ERP、CIMS等现代企业管理。,1.2 实验数据的记录,1.记录数据要求完整、准确、整齐、清楚和条理分明。所有数据应直接记在记录本上，不要只拣好的记或随意涂改；2.如果确需校正，可在不正确的数据上划一条线，以便识别它是否舍弃，然后在原地方或旁边直接写上正确的数据；3.不得用铅笔

4、或红笔记录；4.不要将任何数据写在小纸片上或者再抄一遍。,实验数据的表达,1.列表法是常用的实验数据表达方法，它使得全部数据一目了然，这样不但便于处理运算，也易于参考比较，而且检查方便、减少差错。,2.列表法的一般规则,1.3 Excel法处理数据,（一）公式的运用,（二）图表的处理,Excel 工作界面,列号,名字框,行号,当前单元格,工作表标签,建立基本概念,工具栏,标准工具栏：文件操作 New Open Save打印功能 Print Print Preview拷贝功能 Cut Copy Paste Format Painter反悔功能 Undo Redo排序功能 Sort Ascendi

5、ng Sort Descending函数功能 AutoSum FunctionWizard对象插入功能 Chart Wizard Text Box Drawing辅助功能 Zoom Control Tip Wizard Help格式化工具栏：选择字体字形 Font Font Size Bold Italic Underline位置安排功能 Align Left Center Align Right Center Across Coloum数值型单元的显示格式 Currency Style Percent Comma Style Increase Decimal Decrease Decimal

6、边框与颜色选择功能 Border Color Font Color,使用公式 公式形式：操作数和运算符 操作数：单元格、数字、字符、区域名、区域 运算符：算术、*、%（百分号）、（乘方）、（）字符（文本连接）关系=、=、引用运算符：1、:（冒号）区域运算符，产生对包括在两个引用之间的所有单元格的引用(B5:B15)2、（逗号）联合运算符，将多个引用合并为一个引用(SUM(B5:B15,D5:D15)3、（空格）交叉运算符产生对两个引用共有的单元格的引用。(B7:D7 C6:C8),1.公式中的运算符,加号+示例 1+2 减号-示例 5-3 乘号*示例 3*3 除号/示例 9/3 乘幂 示例 3

7、2(3的平方),工作表的公式计算,数据类型及运算这里所说的数据是指保存在工作表各单元中的内容。数据数值型数据 非数值型数据：主要是文字型数据，又称为正文型或文本型数据,数据型数据及其运算,（1）算术运算：加、减、乘、除用+、-、*、/四个符号表示。（2）求余数：求A被B除的余数用函数MOD（A，B）表示。（3）取整：取A的整数部分用函数INT（A）表示。（4）日期与时间的运算及其含义：如果D代表日期，D5就是5天后的日期。D1D2为两日期的间隔。如果T代表时间，T0.5就是12：00（中午12点）T2为早上6：00点如果D中又有日期又有时间，则其整数部分INT（D）为日期部分，而小数部分MOD

8、（D，1）为时间部分。计算机从1900年1月0日0点起算天数，小数部分为一天内的时间。如1.5表示1900年1月1日中午12点 12.25表示1900年1月12日上午6点,比较运算,数值型数据之间的比较运算有、（不等于），比较运算的结果是TRUE（1）或FALSE（0）。例如：如果在某单元中打入公式“（53）”，则该单元中显示TRUE如果在某单元中打入公式“（53）”，则该单元中显示FALSE如果在某单元中打入公式“（53）（59）则结果为11，显示2如果在某单元中打入公式“（53）（59）则结果为00，显示0,逻辑运算,AND（A，B）读作A与B，仅当A、B均为TRUE时结果才为TRUE。例

9、如，AND（C30，C35）当单元C3中的内容在05之间时，结果为TEUE，否则为FALSE。OR（A，B）读作A或B，仅当A、B均为FALSE时结果才为FALSE，否则结果为TRUE。例如，OR（C30，C35）当C30或C35时结果为TRUE，否则为FALSE。NOT（A）读作非A，当A为TRUE时结果为FALSE，否则为TRUE。例如，NOT（C30），当C30时结果为FALSE，否则为TRUE。逻辑函数可以连用如：OR（C31，NOT（AND（C20，C25）C31或C2不在0与5之间，当此条件成立时，结果为TRUE，否则为FALSE。,数据与公式的输入,数据 每个单元格最多能显示15

10、位数字，超过15以科学计数法计数公式 公式Enter,创建工作表（创建公式）,=（B4+25）/SUM（D5:F5）,工作表的操作与编辑,数据自动输入(1)自动填充。指一批有规律的数据，例等差、等比数列,输入前2个数,选中该2个单元格,指向第2个单元格右下角的自动填充柄,拖曳填充柄到结束,工具选项自定义序列,在输入序列框依次输入序列，之间用逗号分隔,(2)系统提供的序列数据 例：甲、乙、丙.；?3、6、9.；星期一、星期二.方法：输入一个数据，利用自动填充柄拖曳到结束填充。,(3)用户自定义序列数据,输入第一个数,选中包含该数的区域,设置填充序列,编辑/填充/序列,等差,比差,公式的输入,（1

11、）建立一个工作表,（2）选定单元格,（3）在单元格（或编辑栏）中输入“=”后再输入相关公式（例如=A1+B1/C1）,按Enter键即可在此单元格中得到此 公式的计算结果,技巧：在输入公式中的单元格地址时，也可通过单击鼠 标相应的单元格而输入其地址,（4）若同一列中的各单元格中的公式相同，第一个单元格中的公式输入后，按住该单元格右下角的黑色小方块拖拽至所需位置即可。,(5)常用函数：LN()LOG(),单元格地址在公式中的作用,绝对地址：A1相对地址：$A$1混合地址：$A1 A$1,E4=B1+A2+C2,?,?,公式的自动重算功能基于参加计算的数据均引用其存放位置（单元格地址）。,公式与函

12、数的应用,相对引用与绝对引用,相对引用 直接用单元格或单元格区域名，不加“$”符，如A5，B4，相对引用的公式随着复制公式的移动而调整，即公式中引用的单元格位置将随公式引用而发生相对变化。绝对引用 在引用单元格的行列之前都有“$”，公式复制与填充后，单元格引用地址不发生变化，即无论将这个公式复制到何处，公式中引用的还是原来单元格的数据。混合引用 在填充过程中只变化行、列地址中的一个时用混合引用，绝对行、相对列引用，只在行前加“$”，绝对列、相对行引用，只在列前加“$”。,（1）相对引用单元格引用会随公式所在单元格的位置变更而改变,（2）绝对引用 指引用特定位置的单元格，如果公式中的引用是绝对引

13、用，那么复制后公式引用不会改变。绝对引用的样式是在列字母和行数字之前加上美元符号$。,例子,（3）选择性粘贴,函数的分类,EXCEL有200多种函数，分为13类，包括常用、全部、财务、日期与时间、数学与三角、统计、查找与引用、数据库、文字、逻辑、信息、工程、自定义。在函数所提供的“函数向导”对话框中有两个列表，左边为函数分类表（13类），右边为函数名表,工作表的函数计算,函数的语法和输入法 函数的语法(1)函数由函数名和参数两部分组成,函数公式的开头一定要有等于号“=SUM（B3：B12)”(2)参数必须有圆括号”（）“括起来，括号与函数名之间不能有间隔（3）函数的参数多于一个时，用”，“分开

14、。函数的参数可以的数值、有数值的单元格或单元格的数据，也可以是一个表达式，但总长度不能超过1024个字符。（4）函数的参数是文本时，必须用双引号括起，如=LEFT（“王平”，2）函数的输入法：（1）键盘输入（2）利用菜单的函数命令,函数是Excel自带的一些已经定义好的公式。,在工作表中应用函数,不同类型的函数要求给定不同类型的参数,函数名（参数1，参数2，）,代表函数功能,=（B2+B3+B4+B5+B6+B7+B8+B9+B10）/9,=Average（B2:B10）,公式与函数的应用,函数应用举例,函数按功能分为以下几种类型：数学与三角函数（比如，SUM）统计函数（比如，AVERAGE、

15、COUNT）逻辑函数（比如，IF）财务函数（比如，PMT）日期和时间函数（比如，TODAY）数据库函数（比如，DCOUNT）查找与引用函数（比如，VLOOKUP）文本函数（比如，CONCATENAT）信息函数（比如，ISBLANK）,公式与函数的应用,Excel数据处理中常用的函数,求 和：=sum(a1:a5)；求平均值：=average(a1:a5)；求标准偏差：=stdev(a1:a5)；相关系数：=pearson(B2:B17,C2:C17)自由度：=count(B2:B17)-2F值：=F72/(1-F72)/F8)P值：=fdist(F9，1，F8),求 和：=sum(a1:a5)

16、；求平均值：=average(a1:a5)；求标准偏差：=stdev(a1:a5)；,Excel的函数（补充）,Excel的错误信息,（二）图表的使用,1.创建图表,A建立工作表，输入数据,B选择要包含在图表中的数据单元格,C单击“插入”菜单中的图表选项，在弹出来的图表导向对话框中选择标准类型标签下的XY散点图条目中的第一行中的类型比较成对的数值。,2.图表的处理,A在图表区域单击右键，在弹出的快捷菜单中选择“图表选项”菜单，可对图表标题、横纵坐标名称进行填写。,B在图中选择其中一个数据点，然后单击右键，在弹出的快捷 菜单中选择添加趋势线选项。,C 在“添加趋势线”对话框中的类型标签下选择所需

17、类型，然后在 选项标签下选择“显示公式”和“显示R平方值”，然后单击确定按钮，即可得到所要图形。,更直观地显示数据（图表）,图表：数值数据的图形化,创建步骤：选取数据插入图表独立图表嵌入图表编辑图表修饰图表,数据表格,图表相关名词术语,图表相关名词术语,图例,数据标记,类型轴(X),刻度线,值轴（Y）,图表相关名词术语,编辑图表,选择图表类型增加或删除图表元素图表标题X轴标题Y轴标题图表标记数据标签图例刻度线,创建和修饰图表,修饰图表样例,创建和修饰图表,三、标准曲线的制作,1、制作依据,吸光度与吸光物质的含量呈正比关系,制作方法：,在选择的实验条件下，从低浓度到高浓度分别测量一系列（不少于5

18、）不同含量的标准溶液的吸光度，以标准溶液中待测组分的含量为横坐标，吸光度为纵坐标作图，得到一条通过原点的直线，称为标准曲线（或工作曲线）。,再测量待测溶液的吸光度，在标准曲线上就可以查到与之相对应的被测物质的含量。,3、标准曲线不通过原点原因,参比选择不当；吸收池厚度不等；吸收池位置不妥；吸收池不清洁等。,标准曲线法实验设计：序号 1.2.3.4.5.6.标准浓度项 xs1 xs2 xs3 xs4 xs5 xs6信号值 ys1 ys2 ys3 ys4 ys5 ys6,xS1,xS2,图2-1,不能无限延长，必须在线性范围内点在直线两侧的数目近似相等单点校正,Excel中标准曲线的制作,可以在绘

19、图区格式里面改变背景的颜色，一般默认的颜色是灰色，不是很好看。在图标选项里面把图的一些项目名称写入。如果你指在曲线上点右键，可以在添加趋势线找到回归方程和R。,标准曲线及线性回归,相关系数 Correlation coefficient,相关系数的定义为：,1、应怎样估计线性的好坏？相关系数的问题,判断一元回归线是否有意义，可用相关系数来检验。,相关系数的物理意义,3.当 R 的值在0与1之间时，可根据测量的次数及置信水平与相应的相关系数临界值比较，绝对值大于临界值时，则可认为这种线性关系是有意义的。,1.当所有的yi值都在回归线上时，R=1。,2.当y与x之间不存在直线关系时，R=0。,相关系数及显著性计算,数据处理中图的表达,数据处理中表的表达,