excel在统计分析中的应用.docx
《excel在统计分析中的应用.docx》由会员分享，可在线阅读，更多相关《excel在统计分析中的应用.docx（12页珍藏版）》请在冰豆网上搜索。
excel在统计分析中的应用
EXCEL在统计分析中的应用
第一章Excel2010数据基本操作
1.1数据的输入
1.1.1基本数据的输入
文本型数据适用于在单元格中输入中文汉字，也可以根据实际需要将输入的数字设置为文本格式。
1.输入员工身份证号码
在单元格中输入一长串数字是，系统会默认为显示为科学计数的方式。
方法：
选中输入身份证号码的单元格区域，右键—设置单元格格式—数字—文本
2.输入以“0”开头的编号
方法：
选中要输入以0开头编号的单元格或单元格区域，在“开始”一栏里有一“常规”选项，下拉后，选择“ABC文本”。
3.同理，货币数据，百分比数据，指定时间格式，指定日期格式都可以用此方法。
其中，如果输入文字和字母等内容，其默认作为文本来处理，无须特意设置单元格的格式为“文本”。
1.1.2批量数据的输入
1.在连续单元格区域中输入相同的数据
①在单元格中输入第一个数据，将光标定位到该单元格的右下角，指光标变成十字形状
②按住鼠标左键不放，向下拖动至填充结束的位置
③松开鼠标左键，拖动过的位置上会出现与该单元格相同的数据
2.在不连续单元格中输入相同的数据
①按住Ctrl键，将需要输入相同数据单元格依次选中
②在最后一个选择的单元格输入要输入的内容，按Ctrl+Enter快捷键，这时所有选取的单元格中将会自动录入相同的内容。
其中，要在不连续单元格中输入相同的数据也可以在多个工作表进行，即在选择要输入相同数据的单元格时，切换到不同的工作表中进行选择，然后输入数据后，按Ctrl+Enter快捷键即可。
1.1.3特殊符号的输入
1.插入特殊符号
方法：
插入—符号—符号
2.插入特殊字符
方法：
插入—符号—特殊字符
1.1.4外部数据的导入
1.从Word中导入数据
①在Word中选中需要复制的数据，开始—剪贴板—复制
②在Excel工作表中放置复制数据的其实单元格，开始—剪贴板—粘贴—匹配目标格式
2.从网页中导入数据
①打开要导入数据的工作表，选中导入数据要放置位置的起始单元格，数据—自网站
②打开了“新建Web查询”对话框，在“地址”文本框中输入网站的网址，单击“导入”
③找到需要导入的内容，选中后，进行“导入”
④打开“导入数据”对话框，在“数据的放置位置”栏中设置导入数据显示的位置，单击“确定”
⑤返回工作表，即可将网站中选定的部分数据导入工作表中
1.1.5应用案例：
请款单
请款单常用于企业内部或企业之间借支或回收账款的一种单据，通常包括申请部门，日期，项目，客户名称，付款摘要，金额，付款条件，凭证号，备注，财务审核等项目。
1.2数据的编辑
1.2.1数据的选取
在工作表中输入数据后，可以对数据进行选取，对数据选取的方法主要包括两种，一种是对连续数据的选取，另一种是对不连续数据的选取。
1.连续数据的选取
选中第一个要选取的数据所在的单元格，此时鼠标变为空白十字形状，按住鼠标不放，向上向下，向左向右即可选取连续的数据。
2.不连续数据的选取
选中第一个数据所在的单元格，按住Ctrl键不放，依次单击其他需要选中数据所在单元格，即可选中不连续的数据
1.2.2数据复制、粘贴与删除
1.数据复制和粘贴
方法:
开始—剪贴板—复制，然后再粘贴
2.数据删除
方法：
右键—清除内容
1.2.3数据区域转换
例01/源文件/数据转换
①将光标定位到表格任意位置，设计—转换为区域
②此时系统会弹出提示框，提示是否将表转换为普通区域，单击“是”
③设置完成后，系统自动将表格转换为普通表格，此时表格不再有“表格工具”选项卡
1.2.4数据的查找与替换
例01/源文件/数据查找与替换
①打开工作表，按Ctrl+F快捷键，打开“查找和替换”对话框，在“查找内容”文本框中输入查找信息
②单击“查找全部”按钮，即可满足条件的记录显示出来
③选择“替换”选项卡，在“替换为”文本框中输入需要替换的信息，单击“替换”按钮就可替换当前选定的数据
④单击“全部替换”按钮，即可满足条件的记录全部替换为设定的内容
1.2.5数据条件格式标识
例01/源文件/数据条件格式标识
通过“条件标识”功能可以对数据进行有效的条件格式设置，将单元格中满足大于指定数值的数据以特殊的标记显示出来。
1.标识大于50的数据
①打开工作表，选中区域，开始—样式--条件格式—突出显示单元格规则—大于
②打开“大于”对话框，在“为大于以下值的单元格设置格式”，在文本框中输入“50”，在“设置为”下拉列表中选择“黄填充色深黄色文本”选项，单击确定
③返回工作表，则大于50的数据被标识出来
2.标识前三名成绩
①打开工作表，选中单元格区域，开始—样式—条件格式—项目选取规则—值最大的10项
②打开“10个最大的项”对话框，将“10”改为“3”，在“设置为”下拉列表框中选择“浅红色填充色深红色文本”选项，单击“确定”按钮
③返回工作列表中，即可将前三名的数据用指定格式标识出来
1.2.6应用案例：
销售记录表
销售记录表记录了销售员,产品名称，销售数量，销售单价等信息。
1.3数据编辑窗口设置
1.3.1窗口冻结
源文件：
01/源文件/窗口冻结
当工作表中含有大量数据时，数据不能再同一界面显示，如果想要继续查看数据时始终显示标识项，可以冻结窗口，方便数据查看。
①选中要冻结的单元格所在行的下一行单元格，视图—窗口—冻结拆分窗格
②冻结窗口后，向下滚动鼠标查看数据，冻结窗口始终显示
1.3.2数据对比
源文件：
01/源文件/数据对比
在打开多个工作表时，可以使用并排查看的方法对数据进行对比。
如销售11月和12月销售奖金情况。
打开工作表，视图—窗口—并排查看
1.3.3新建窗口
视图—窗口—新建窗口
1.3.4应用案例：
各部门费用明细表
各部门费用明细表记录了各部门每一个月使用费用情况，方便财务部门对公司各部门使用的了解。
第二章数据处理与计算
在Excel中简历表格后，需要对数据进行合理的分类，排列，计算，以及统计分析
2.1数据有效性设置
2.1.1设置数据有效性
源文件02/源文件/设置数据有效性
设置数据有效性，可以建立一定的规则来限制向单元格输入内容，也可以有效地防止输入错误。
方法：
①选中区域---数据—数据工具---数据有效性
②打开数据有效性对话框，在允许下拉列表框中选择“文本长度”
③在“数据”下拉列表框中选择“介于”，分别在最大值与最小值中输入相应的数字，
④返回到工作表中，若是输入的不是介于最值之间的数据，即会弹出错误信息。
2.1.2输入提示信息
同理数据有效性。
方法：
打开数据有效性对话框，切换到“输入信息”选项，在“标题”和“输入信息”文本框中输入要提示的信息；然后切换到“出错警告”选项卡，在“样式”下拉列表中选择“警告”选项，接着在“标题”和“错误信息”文本框中输入要提示的信息，单击“确定”；返回工作表中，在单元格中显示了提示信息，若不符合数据有效性则会警告。
2.1.3在Excel工作表中添加下拉列表框
源文件：
02/源文件/添加下拉列表框
在制作Excel表格时，如果需要输入部门，希望部门所在的数据以通过下拉列表选择，而不是输入，可以使用有效性创建下拉列表。
同理2.1.1
方法：
打开“数据有效性”对话框，在“允许”下拉列表中选择“序列”选项，在来源中输入选项，单击“确定；返回到工作列表中，单击单元格，即可弹出下拉菜单。
2.1.4圈释无效数据
源文件：
02/源文件/圈释无效数据
在“数据有效性”对话框中为数据设置了有效的范围后，但数据不在这个范围是就会弹出提示信息，但是用户还是可以正常的输入无效数据，这时候利用“圈释无效数据”按钮就可以将其中的无效数据标识出来。
2.2数据排序
2.2.1快速对数据进行排序
数据—排序与筛选—升序、降序、、
2.2.2双关键字排序
源文件：
02/源文件/双关键字排序
方法：
①选中区域，数据—排序与筛选—排序
②打开“排序”对话框，在“主要关键字”下拉列表中选择选项，在“次序”下拉列表中选择“升序”选项。
单击“添加条件”按钮
③在“次要关键字”下拉列表中选择选项，在“次序”下拉列表中选择“降序”，单击确定
④则一部分从小到大排序，一部分从大到小排序
2.2.3自定义排序
同理2.2.2
在“次序”下拉列表中选择“自定义序列”，然后在“自定义序列”对话框，在“输入序列”列表框中输入自定义序列，并单击确定。
2.3数据的筛选
2.3.1快速筛选数据
源文件：
02/源文件/快速数据筛选
Excel的自动筛选功能还可以把暂时不需要的数据隐藏起来，而只显示符合条件的数据记录，这在管理工作表时相当有用。
方法：
①选中A1单元格，数据—排列与筛选—筛选
②此时工作表会进入自动筛选状态，标题栏中每一个单元格旁都会有一个下拉三角号，这是自动筛选的标志，单击“班级”单元格的右侧下拉按钮，在其下拉菜单中取消选中“全选”复选框，接着选中”计算机三班“复选框，单击“确定”按钮
③此时系统会自动筛选出计算机三班的成绩
2.3.2自定义筛选
源文件：
02/源文件/自定义筛选
Excel2010中，用户还可以在自动筛选功能下通过自定义筛选来设置筛选条件，如在2.3.1节数据中筛选出C语言成绩大于或等于75的学生。
方法：
①单击“C语言”单元格右侧的筛选按钮，在其下拉菜单中选择“数据筛选”命令，在弹出的子菜单中选择“自定义筛选”命令
②打开“自定义自动筛选方式”对话框，在右边下拉列表框中选择“大于或等于”选项
③在“大于或等于”文本框中输入“75”，单击“确定”按钮
④返回工作列表，系统会自动筛选出C语言大于或等于75分的成绩
2.3.3使用通配符进行筛选
源文件;02/源文件/通配符
在Excel中，用户可以利用“*”，“？
”通配符对工作列表进行模糊筛选。
方法：
①单击“姓名”文本框右侧的筛选按钮，在其下拉菜单中选择“文本筛选”命令，在弹出的子菜单中选择“自定义筛选”命令
②打开“自定义自动筛选方式”对话框，在“姓名”栏的下拉列表框中选择“包含”选项，在后面的文本框中输入“杨*”，单击“确定”按钮
③返回工作表，系统会自动筛选出表格中姓杨的同学的考试成绩
2.3.4利用高级筛选工具
源文件：
02/源文件/高级筛选
如果工作表中所含信息较多，对单一列的数据筛选很难找到自己想要的数据，则利用高级筛选进行多个数据条件数据筛选，使用Excel高级筛选时，工作表的“数据区域”与“条件区域”之间至少要有一个空行做间隔，而且“条件区域“必须有列标志
方法：
①在A13：
A14单元格区域输入高级筛选条件，选择“数据”选项卡，在“排序和筛选”选项组中单击“高级”按钮
②打开“高级筛选”对话框，将光标定位到“列表区域”文本框，然后在工作表中拖动鼠标选中A1：
I11单元格区域，接着将光标定位在“条件区域”文本框中，然后在工作表中拖动鼠标选中A13：
B13单元格区域
③选中“将筛选结果复制到其他位置”单选按钮，接着将光标定位到“复制到”文本框中，然后在工作表中选中A16单元格，单击确定
④返回工作表中，系统就会筛选出C语言成绩大于或等于60分且离散数学成绩大于等于70分的成绩
2.4数据的分类汇总
2.4.1简单的分类汇总
源文件：
02/源文件/分类汇总
进行分类汇总前需要对所汇总的文件进行排序，即将同一类别的数据排列在一起，然后将各个类别的数据按指定方式汇总
方法：
①选中B2单元格，选择“数据”选项卡，单击“排列与筛选”选项组中的“降序”按钮
②此时系统对数据进行降序排序，在“分级显示”选项组中单击“分类汇总”按钮
③打开“分类汇总”对话框，在“分类字段”下拉列表框中选择分类字段“产品类别”，接着在“选定汇总项”列表框中选中“销售金额”复选框，单击“确定”按钮
④返回工作表中，系统根据设置条件进行分类汇总
2.4.2嵌套分类汇总
源文件：
02/源文件/嵌套汇总
嵌套分类汇总是指在分类汇总中进行一次或几次其他方式汇总，几种汇总方式同时存在与工作表中，具体方法如下：
①对“产品类别”进行降序，在“分级显示”选项组中单击“分类汇总”按钮
②打开“分类汇总”对话框，在“分类字段”下拉列表框中选中分类字段“产品类别”，在“汇总方式”下拉列表中选择“平均值”选项，单击“确定”
③返回工作表中，即可对数据进行分类汇总
④再次打开“分类汇总”对话框，在“分类字段”下拉列表中选择“产品名称”选项，接着在“分类汇总”下拉列表框中选择“求和”，单击“确定”按钮
⑤返回工作表中，即可在对产品分类汇总的基础上，对产品名称进行分类汇总
注意：
重复选择时要讲“替换当前分类汇总”的取消
即
2.5数据合并计算
2.5.1对数据进行合并计算
源文件：
02/源文件/合并计算
“合并计算”功能将多个区域中的值合并到一个新的区域中，并利用合并计算功能可以为数据计算提供很大便利，利用合并求和计算可以将几个工作表的数据在新的工作表中合并计算
方法：
①打开工作表，在创建新工作表中并输入输入产品的基本信息，选中D2单元格，数据—数据工具—合并计算
②打开“合并计算”对话框，在“函数”下拉列表框中选择“求和”选项，接着将光标定位到“引用位置”文本框中，切换到“10月销售额”工作表，在工作表中拖动鼠标选取各产品的销售数量区域，在“合并计算”对话框中单击“添加”按钮
③切换到“11月销售额”工作表中，系统会自动选中相同的单元格区域到“所有引用位置”列表中，单击“添加”按钮
④切换到“12月销售额”工作表中，系统会自动选中相同的单元格区域到“引用位置”文本框，单击“添加”按钮，系统会自动将“12月销售额”添加到“所有引用位置”列表中，单击“确定”
⑤返回到工作表中，即可将各个月的销售金额汇总到D列单元格中
⑥选中F2单元格，在公式编辑栏中输入公式“=C2*D2”,按Enter键后复制公式，即可得到总销售金额
2.5.2按类合并各类产品销售情况
源文件：
02/源文件/按产品类别汇总
通过分类来合并计算数据的特点是当选定的表格中具有不同的内容时，可以根据这些内容的分类进行合并工作
方法：
①新建工作表，设置工作标题为“第四季度销售汇总”，选中A2单元格，数据—数据工具—合并计算
②打开“合并计算”对话框，在“函数”下拉列表中选择“求和”选项，接着将光标定位到“引用位置”文本框中，切换到“10月销售额”工作表，在工作表中拖动鼠标选中A1:
E13单元格区域，在“合并计算”对话框中单击“确定”
③切换到“11月销售额”工作表中，系统会自动选中相同的单元格区域到“所有引用位置”列表中，单击“添加”按钮
④切换到“12月销售额”工作表中，系统会自动选中相同的单元格区域到“引用位置”文本框，单击“添加”按钮，系统会自动将“12月销售额”添加到“所有引用位置”列表中，选中“首行”和“最左列”复选框，单击“确定”
⑤返回工作表中，即可计算出第四季各月各产品销售情况，也可对单元格进行美化
注意：
在“合并计算”对话框中选中“创建指向源数据的链接”复选框，可以使合并计算结果依据源数据的更新而自动更新。
升级会员 