搜索
Excel高级数据处理与分析张祖林在我的电脑地址栏输入：下载：选择“张祖林”——“讲义资料”——EXCEL数据分析——下载你需要的文件交作业和签到:文件名——姓名（选择—EXCELA班——周次）18990482988FTP使用Excel内容目录一、Excel概述二、创建和编辑工作表三、格式化工作表四、公式和函数的使用五、创建图表六、数据管理和分析七、工作薄管理及打印工作表一、Excel概述Excel是Windows环境下的电子表格软件，是Office成员之一。它具有强有力的数据库管理功能、丰富的宏命令和函数，强有力的决策支持工具。1、简介（1）分析能力Excel除了可以做一些一般的计算工作外，还有若干个函数，用来做财务、数学、字符串等操作以及各种工程上的分析和计算。（2）操作简便Windows平台下软件的共同特点。（3）图表功能在Excel中，系统大约有100种不同格式的图表可供选用。用户可以通过图表指南一步步的引导，得到所需的图表。（4）数据库管理能力在Excel中提供了的数据库管理功能使Excel具备了能组织和管理大量数据的能力，因而使它的用途更广泛。2、特点5）宏语言能力使用VB进行宏命令的开发。6、样式功能在Excel中，用户利用各种文字格式化的工具和制图能力制作出精美的报表，并可以将之存储成样式，用于以后的读取。7、对象的连接与嵌入（OLE）功能用户利用OLE可以将其它软件制作的图插入到工作表中。8、连接和合并功能Excel利用此功能可以很容易地将多个工作表连接起来，并进行汇总。3、Excel界面及基本操作编辑栏工具栏菜单栏地址栏（名称栏）（1）编辑栏给用户提供活动单元格的信息。在编辑栏中用户可以输入和编辑公式。由名字栏和公式栏组成。取消按钮确认按钮函数指南按钮名称栏公式栏（2）工作薄（BOOK）窗口“全选”按钮活动单元格行号列标工作表导航按钮当前工作表工作表标签（3）工作表（SHEET）一个工作薄最多可由255个工作表组成。工作薄窗口中默认有3个工作表，只有一个为当前工作表。工作表是一个由行和列组成的表格。行号和列号分别用字母和数字区别。行号为1—65536，列标采用字母A—IV。因此每张表为256列65536行。每个单元格都有固定的行号和列标。系统默认的工作表的标签名称为“Sheet1”、“Sheet2”……等，用户可以根据需要自己重新命名工作表。（3）工作表（SHEET）一、工作表删除、插入和重命名1、选取工作表2、插入（删除）工作表选标签，右击——插入——空工作表。选标签，右击——删除工作表；或选标签，[编辑]—[删除工作表]。3、重命名工作表双击标签二、复制或移动工作表1、使用菜单：[右击标签]——选择复制（移动）2、使用鼠标：复制按CTRL，移动不需要。二、创建、打开和保存文件⑴创建空白工作簿方法一:启动Excel-“Book1”。方法二：“文件”/“新建”/“常用”－“工作簿”。方法三：、Ctrl+N。(2)创建基于模板的工作簿“文件”/“新建”/“电子表格模板”标签中的模板。1、工作薄的新建2、保存工作簿注意:.选择存放位置.输入文件名.确定文件类型(2)为已有工作簿保存副本“文件”/“另存为”(1)新工作簿的保存①“文件”/“保存”。②按钮。③Ctrl+s。作用:便于查找操作。“文件”/“属性”/“摘要信息”(3)保存摘要信息3、工作薄的打开⑴在Excel窗口中:•“文件”/“打开”•按钮•组合键Ctrl+o⑵在“我的电脑”或“资源管理器”中:双击图标对应的文件。“文件”菜单/“另存为”/“选项”（2）实现方法:（1）保护方法有两种:打开口令修改口令设置4、保护工作簿文件三、编辑工作表（1）字符的输入在一个单元格中最多可放32000个字符。输入多行字符：按[Alt]+回车键；默认单元格宽度为8个字符宽度。输入由数字组成的字符串时应在数字前加单引号。如：’08362512112字符的默认对齐方式为左对齐。当字符宽度超过单元格宽度，如果右边单元格无数据则延伸到后面的单元格，如果右边单元格有数据则截去超宽的字符。快速输入对号与错号：按住Alt键，在数字键区输入41420——√按住Alt键，在数字键区输入41409——×3.1数据输入（2）数字的输入数字太大用科学计数法。公式栏中显示输入数字，最多显示15位。•可输入数值：0、—9、+、-、()、/、*、$、%、e等。•输入负数：前加负号或将数值放在圆括号里。•输入分数：先输入一个0及空格，再输入分数。分数：05/9数值:右对齐。空格日期格式通常为：年月日、月日。如:2012/9/1、2012－9－1、9－1、9/1时间格式通常为：“时：分：秒”；上午加AM，下午加PM。如20:23AM。如果省略默认为上午插入当前日期：CTRL+；插入当前时间：CTRL+：或CTRL+SHIFT+；空格（3）日期和时间的输入1、数字的填写n在该数字的填写行（列）的前两个单元格内输入开始的2个数字；n选择这两个单元格；①用鼠标按住第二个单元格的填充柄，将其拖曳到结束单元格，松开鼠标3.2使用自动填充柄①在“工具”菜单中，选择“选项”，单击“自定义序列”标签；②确定输入序列；③在单元格中输入所选序列的任一项，并选择该单元格；④用鼠标左键拖曳所选单元格右下角的填充柄到结束处3.3自定义序列的自动填写1、用Ctrl+Enter组合选定区域——输入数据——按住Ctrl再按Enter2、双击单元格的填充柄在某列的第一个单元格输入数据——双击该单元格的填充柄（不选区域、不拖鼠标，但是需要前列有数据）3.4用Ctrl+Enter输入相同数据1、输入如P00-000-0001格式选定区域—设置单元格格式—数字—自定义—类型—输入”P00-000-0000”。结果是输2就是P00-000-0002是输112225678就是P11-222-56782、输入如相同部分51332118010034数据选定区域—设置单元格格式—数字—自定义—类型—输入”51332118010034“0000。后面的4个0表示4位可变数字。（注意这种编号不是真实的，需要数据统计分析的表不用，最好采用分列输入相同部分，再用&连接）3.5利用自定义格式输入特殊编号数据有效性数据有效性——允许——序列3.6利用下拉列表进行数据选择3.7利用公式与函数从已有数据表查找输入（后面介绍VLOOUP函数3.8行列转置输入3.9数据有效性限制性输入3.10大量仿真数据的产生用随机函数RAND()结合INT()，输入大量的数字数据。如INT(RAND()*90)3.7Excel2007的汉子简转繁•Excel2007的审阅选项——汉繁转换2、编辑工作表（1）单元格内容的移动、复制和删除A.用鼠标拖曳的方法移动和复制单元格的内容选择要移动、复制的单元格移动：将光标移到单元格边框的下侧或右侧，出现箭头状光标时，用鼠标拖曳到单元格的新位置即可复制：在按住Ctrl键的同时，按上述方法用鼠标拖曳单元格到新的位置2、编辑工作表B.用剪切和粘贴的方法移动和复制单元格的内容①选择要移动、复制的单元格②移动：可以单击工具栏中的剪切按钮复制：可以单击工具栏中的复制按钮③单击目标单元格④单击工具栏中的粘贴按钮⑤按Enter键即可2、编辑工作表C.单元格内容的删除①选择所要删除内容的单元格②在“编辑”菜单中选择“清除”项，在子菜单中选择相应项③或按Del键直接删除单元格内容2、编辑工作表（2）单元格的删除①选择所要删除的单元格②在“编辑”菜单中选择“删除”项，打开删除对话框③做相应选择后按“确定”钮完成操作（3）单元格的插入①选择所要插入单元格的位置②在“插入”菜单中选择“单元格”项，在出现的“插入”对话框中选择插入后当前单元格移动的方向③按“确定”按钮即可2、编辑工作表（4）行、列的删除和插入行、列的删除①单击所要删除的行、列的标头②选择“编辑”/“删除”项即可行、列的插入①单击所要插入行、列所在任一单元格②选择“插入”/“行”或“列”项即可2、编辑工作表（5）编辑批注•插入批注插入——批注——输入批注——确定•删除批注编辑——清除——批注（6）选择性粘贴编辑——选择性粘贴（或右击）（7）查找与替换编辑——查找与替换3、常见错误提示“#####”单元格宽度不够。“#Value!”引用公式时有错误的输入类型。“#NUM!”数字、参数有错误。“#REF”单元格引用无效。“#NAME!”使用Excel不能识别的名称“NULL!”指定了两个不相交区域的交叉点4、窗口的冻结、分割与缩放（1）冻结窗口窗口冻结拆分窗口（2）分割窗口窗口拆分窗口（3）缩放窗口水平移动时，左边窗口冻结垂直移动时，上面窗口冻结常用工具栏中的缩放控制三、格式化工作表1、改变行高、列宽Excel默认行高为14.25，列宽为8.38。当输入的文字或数字超过行高或列宽时会出现错误信息（如“########”）。方法：用鼠标直接拖动列标或行号的间隔。格式行行高列列宽2、数字显示格式的设定格式单元格选定单元格货币样式格式工具栏按钮百分比样式千位分隔样式增加小数位数减少小数位数如何设置不显示“0”？工具选项3、表格边框的设置（1）取消网格线工具选项（2）添加表格边框选定格式单元格边框4、对齐工具的使用5、字体、底纹及文字方向6、自动套用表格格式选定格式自动套用格式7、条件格式选定对象格式条件格式8、格式复制格式刷：只复制对象的格式。选定复制编辑选择性粘贴四、公式和函数的使用1、数据的自动求和选定工具栏自动求和按钮以“=”开始，由常数、单元格引用、函数和运算符组成。即：“=”+“表达式”2、公式=IF(A3300,A3+20,”FILSE”)函数名运算符常数文本注意：1、函数名不分大小写。2、单元格引用不能写在引号中间。3、标点符号必须是半角英文输入。4、各参数之间用逗号隔开，参数必须用小括号括起。算术运算符：+、-、*、/、∧比较运算符：=、、、=、=、字符运算符：&（连接）2、公式——运算符2、EXCEL运算符的优先级运算符运算功能优先级（）括号1-负号2%百分号3^乘方4*与/乘、除法5+与-加、减法6&文本连接7=、、、=、=、83、单元格的引用•相对引用：列标和行号的直接组合。如B5•绝对引用：在列标和行号前加上“$”。如$B$5•混合引用：如：$B5或B$5•同一工作表中单元格的引用——内部引用•不同工作表中单元格的引用——三维引用•不同工作簿中单元格的引用——外部引用说明：1、在复制公式时，单元格的绝对引用是不变的。2、善于使用F92、RAC1引用——在宏程序中1、A1引用——在工作表中4、函数“：”和“，”操作符•“：”操作符操作对象为以“：”前后的两个单元格为角的矩形区域。•“，”操作符操作对象为“，”前后的两个单元格。常用函数——Sum返回某一单元格区域中所有数字之和。格式：SUM(参数1,参数2,...)说明：1、计算机1到30个需要求和的参数。2、如果参数为数组或引用，只有其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。如果参数为错误值或为不能转换成数字的文本，将会导致错误。例：=SUM(3,2)将3和2相加=SUM(5,15,TRUE)将5、15和1相加，因为文本值被转换为数字，逻辑值TRUE被转换成数字1SUMIF——求满足条件的和语法：SUMIF(range,criteria,sum_range)•Range为用于条件判断的单元格区域。•Criteria为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本•Sum_range是需要求和的实际单元格。•说明：只有在区域中相应的单元格符合条件的情况下，sum_range中的单元格才求和。如果忽略了sum_range，则对区域中的单元格求和。例：=SUMIF(A2:A5,160,B2:B5)说明：求A2:A5区域内满足条件“160”的单元格，在B2:B5区域的和。常用函数——Average：求平均值函数（算