EXCEL基础与方差分析03.doc

Excel 基础与方差分析第一部分 Excel 基础1. 文件操作1.1 打开或保存 Microsoft Office 文档，可以一次打开1个或多个工作簿。1.2 多次撤消（Office 97 中的新增功能）最多可撤消最近执行的 16 次操作。1.3 逐个关闭或关闭多个文件时（“全是”选项）。2. 工作簿和工作表2.1 工作簿在 Microsoft Excel 中，工作簿是处理和存储数据的文件。由于每个工作簿可以包含多张工作表，因此可在一个文件中管理多种类型的相关信息。2.2 工作表工作表是 Microsoft Excel 中用于存储和处理数据的主要文档。也称为电子表格。工作表由排列成行和列的单元格组成。工作表总是存储在工作簿中。使用工作表可以显示和分析数据。可以同时在多张工作表上输入并编辑数据，并且可以对不同工作表的数据进行汇总计算。2.3 工作表标签工作簿窗口底部的工作表标签上显示工作表的名称。如果要在工作表间进行切换，请单击相应的工作表标签。2.4 单元格表示法工作表内、外直接引用与间接引用，单元格的数值、日期与字符属性。2.4.1绝对引用：不论包含公式的单元格处在什么位置，公式中所引用的单元格位置都是其在工作表中的确切位置。绝对单元格引用的形式为：$A$1、$B$1，以此类推。有些单元格引用是混合型的。绝对行引用的形式为 A$1、B$1，以此类推。绝对列引用的形式为 $A1、$B1，以此类推。与相对引用不同，当跨越行和列复制公式时绝对引用不会自动调整。2.4.2相对引用相对引用是指像 A1 这样的用在公式中的单元格引用，在将公式复制到其他单元格或单元格区域中时，相对引用也会相应更改。在复制和粘贴公式后，新公式中的相对引用将更改为引用相对于新公式所在行或列的另一个单元格（此单元格与现在公式所在位置之间的距离等于公式最初所在位置与公式最初所引用单元格之间的距离：相隔相同的行和列）。例如：如果单元格 A3 中包含公式 =A1+A2，则将单元格 A3 复制到单元格 B3 以后，单元格 B3 中的公式就变成了 =B1+B2。2.5 单元格的自动填充与数值排序（分别在“编辑”和“数据”菜单下）2.6 对单元格的操作可以直接在单元格中输入“=”，或使用鼠标代替选择参入运算的单元格。可以直接输入公式，也可以借助图标和鼠标输入。3. 直接公式计算与函数计算3.1 公式直接计算按照常规直接填入公式，可以引用单元格的数据。3.2计算结果的整理灵活使用数值和字符串运算，以及排序整理计算结果。如平行分析结果与调查数据的整理实例。3.3 Excel的函数及其应用函数是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。例如，SUM 函数对单元格或单元格区域进行加法运算。Excell中有许多类型的函数，如数据库函数、工程函数、统计函数、财会函数等上百个。常见的有average( )；Correl()；Linset( )；Right() 根据所指定的字符数返回文本串中最后一个或多个字符；LEFT（） 基于所指定的字符数返回文本串中的第一个或前几个字符；DEVSQ( )返回数据点与各自样本均值偏差的平方和，DEVSQ=S（x-x）2;参见“EXCEL中的函数”4. 单元格数据自动处理4.1 填充数据序列4.2 创建排序次序4.3 在同一数据列中快速填写重复录入项（记忆式键入法）5. 宏及其作用5.1宏的定义及其应用宏，自动执行的一系列命令和函数的集合，可以由用户自定义，并有单独的名称，用于自动完成频繁执行的计算任务。也就是说，如果经常在 Microsoft Excel 中重复某项任务，那么用户可以自己根据任务需要定义宏，当需要执行该项任务时可随时运行宏。录制宏时，Excel 会存储在执行一系列命令时每个步骤的信息。然后即可运行宏使其重复执行或“回放”这些命令。5.2 录制宏在录制或编写宏之前，应先制订计划以确定宏所要执行的步骤和命令。因为如果在录制宏时出现失误，更正失误的操作也会记录在宏中。录制多个宏时，Visual Basic 会将每个宏存放在附属于工作簿的一个新模块中。用鼠标指向“工具”菜单中的“宏”，然后单击“录制新宏”命令。在“宏名”编辑框中，键入宏的名称。宏名称的首字符必须是字母，其他字符可以是字母、数字或下划线字符。宏名称中不允许有空格；下划线字符也可以作为分词符。在“保存在”下拉编辑框中，单击选定宏所要存放的地址。一般在当前工作薄中。单击“确定”按钮，启动自动录制宏的功能，屏幕上出现提示图标。如果在运行宏时选中了某些单元格，则该宏在每次运行时都将选中原来的这些单元格而无论事先是否选中了其他单元格，因为宏这里记录的是单元格绝对引用。也可以选择相对引用，这儿不讲。在录制宏的操作中，既可以用鼠标，也可以用键盘。录制完成后，选“工具”菜单中的“宏”下的“停止录制”命令。5.3 使用宏可以在“宏”对话框的列表中选择所需的宏然后运行宏，如果希望通过单击特定按钮或按下特定组合键来运行宏，则在录制时就要定义好。5.4 宏与宏病毒注意宏病毒，Excel工作薄打开时，会提醒你注意宏病毒，如果是你自己编辑的宏当然可以安全使用，可以先验证宏的来源后再启用宏。如果工作簿是从不可靠的网络或 Internet 站点上获得的，请考虑宏病毒感染系统的可能性。6. 图表处理与Office联用第二部分利用Excel做统计以利用Excel宏做方差分析为例。1. 数据和计算格式假定有一个品种比较小区试验，有四个品种，各品种重复5次数据和计算格式如下：ABCDEFG1重复132.333.330.329.3公式12重复234.033.034.326.03重复334.336.335.329.64重复435.036.932.328.05重复536.534.535.828.86合计公式27处理品种A1品种A2品种A3品种A48平均公式3910方差分析平方和自由度方差FF0.05=F0.01=11处理间公式43公式8公式93.495.9512重复间公式54公式103.265.4113误差公式71214总平方和公式61915多重比较16处理平均B17-XiB18-XiB19-XiB20-Xi17宏开始18公式1119公式1220公式13宏结束21LSD检验22样本平均数差数标准偏差Sx1-x2=公式14LSD23查t表，误差自由度df=12 时t0.05=2.185%公式1524t0.01=3.061%公式162. 编辑各种公式（也可以制作宏，但必要性不大，因为简单拷贝可行）公式1（F1）：=SUM（B1.E1），将该公式拷贝至F2.F5，求处理和。公式2（B6）：=SUM（B2.B5），将该公式拷贝至C6.F6，求重复和。公式3（B8）：=B6/5，将该公式拷贝至C8.E8，求重复平均值。公式4（B11）：=1/5*DEVSQ(B6:E6)，除以重复数。公式5（B12）：=1/4*DEVSQ(F1:F5)，除以品种数。公式6（B14）：=DEVSQ(B1:E5)，总平方和公式7（B13）：=B14-B11-B12，从总平方和中扣除重复间和品种间平方和为误差平方和。公式8（D11）：=B11/C11，再拷贝D11到D12.D13，计算方差。公式9（E11）：=D11/D13公式10（E12）：=D12/D133. 制作比较宏工具-记录宏-记录新宏，屏幕上出现“停止”窗口”，开始制作：3.1 选择单元格B1.E8，单击鼠标右键或从“编辑”菜单选择“复制”3.2 选择单元格A17，单击鼠标右键或从“编辑”菜单选择“选择性粘贴”，在对话框中选择“数值”和“转置”两项，确定。处理和平均数转置拷贝至A17.B20中。3.3 从“数据”下拉菜单选“排序”，在对话框中“主要关键字”下选平均，方式选“递减”，确定。3.4 逐项比较选择C18作为活动单元格，键入公式11：=$b$17-b18，并用复制把该公式拷贝至C19.C20中。选择D19作为活动单元格，键入公式12：=$b$18-b19，并把该公式拷贝至C20中。选择E20作为活动单元格，键入公式13：=$b$19-b20。4. 最小显著差数检验4.1 计算样本平均数差数标准偏差Sx1-x2：公式14（E22），=POWER(D13*2/5,0.5)4.2 最小显著差数5%公式15（G23），=E22*E234.3 最小显著差数1%公式16（G24），=E22*E245. 模块使用5.1 在一个空工作表中，按B1.E5整理数据快；5.2 选择数据快，用复制方法把数据快拷贝到有宏的工作表中，粘贴时可以只选择A1作为活动单元格，否则应选择B1.B5作为目的单元格；5.3 粘贴完成后，方差分析自动完成；5.4 作多重比较前，选择A17为活动单元格，在“工具”-“宏”-宏名-确定启动宏。计算结果见如下：四处理五重复的方差分析结果ABCDEF1重复132.333.330.329.3125.22重复234.033.034.326.0127.33重复334.336.335.329.6135.54重复435.036.932.328.0132.25重复536.534.535.828.8135.66合计172.1174.0168.0141.7655.87处理品种A1品种A2品种A3品种A4合计8平均34.434.833.628.3910方差分析平方和自由度方差FF 0.05=F0.01=11处理间135.78345.2593319.268173.495.9512重复间22.5145.628252.3961053.265.4113误差28.191