Excel的统计功能及其运用.ppt

第五讲Excel的统计功能及其运用,主讲人：冯艳2010-9-29,本次课要达到的目的,课时：6课时Excel作为教育管理中统计图表的处理工具，通过温习Excel的数据处理和统计功能，为学习教育管理技术中图表的处理奠定基础。,Excel正确输入身份证号码,Excel数据输入范围控制,防止Excel数据重复输入,用Excel函数向导输入公式,Excel数据表格中如何按条件显示,Excel自定义输入数据下拉列表,Excel数据格式设置,Excel中直接输入函数,给Excel中的数据添加单位,用好Excel的搜索函数,Excel分区域锁定,一次打开多个Excel文件,Excel中特殊符号的输入,在Excel中插入超级链接,Excel大写数字设置,Excel工作表的移动,Excel工作表的复制,给Excel数据表添加页眉页脚,Excel表格标题重复打印,在Excel中设置打印区域,设置Excel文件只读密码,密码保护,Excel工作簿加密保存,保护Excel工作簿,共享Excel工作簿,锁定和隐藏Excel公式,在Excel中打印指定页面,Excel模版的保存和调用,更改Excel缺省文件保存位置,设置Excel标签颜色,在Excel中添加常用文件夹,Excel页面背景设置,Excel监视窗口,每次选定同一单元格,为了测试某个公式，需要在某个单元格内反复输入多个测试值。但是，每次输入一个值后按下Enter键查看结果后，活动单元格就会默认移到下一个单元格上，必须用鼠标或上移箭头重新选定原单元格，极不方便。如果你按“Ctrl+Enter”组合键，则题会立刻迎刃而解，既能查看结果，而活动单元格仍为当前单元格。,日期快捷输入如果要输入“4月5日”，直接输入“4/5”，再敲回车就行了。如果要输入当前日期，按一组合键“Ctrl+；”即可。,不连续单元格填充同一数据启动Excel，选中一个单元格，按住Ctrl键，用鼠标单击其他单元格，就将这些单元格全部选中了，在编辑区中输入数据，然后按住Ctrl键，同时按一下回车，在所有选中的单元格中都出现了同一数据。,自动调整列宽选中要调整的列，从Excel菜单栏中选择：格式列最合适的列宽即可。或者选中要调整的列，将鼠标移动到任意被选中的两列的列标交界处，光标会变为带左右箭头形状（就是我们要手动调整列宽的位置），左键双击,平均分布各行/列在标尺栏上选中相应的行/列：再拖动其中一个行/列的行宽/列高即可调整所有行或列为同一宽度/高度。另外一种方法就是选中相应的行/列，在行高/列宽对话框中直接输入相应的数值即可。,让序号原地不动,有时我们对数据进行排序时，序号全乱了。如果在序号列与正文列之间插入一个空列，再排序时，序号就不会乱了。位于序号前面的列中的内容均不发生改变；为了不影响显示美观和正常打印，可将这一空列隐藏起来。,平均分布各行/列,自动调整列宽选中要调整的列，从Excel菜单栏中选择：格式列最合适的列宽即可。或者选中要调整的列，将鼠标移动到任意被选中的两列的列标交界处，光标会变为带左右箭头形状（就是我们要手动调整列宽的位置），左键双击在标尺栏上选中相应的行/列：再拖动其中一个行/列的行宽/列高即可调整所有行或列为同一宽度/高度。另外一种方法就是选中相应的行/列，在行高/列宽对话框中直接输入相应的数值即可。,钩（）的快捷输入按住ALT键输入41420后放开ALT键即出现“”。Excel中为汉字加注拼音格式_拼音信息_编辑/显示或隐藏,复制、粘贴中回车键妙用先选要复制的目标单元格，复制后，直接选要粘贴的单元格，回车OK；先选要复制的目标单元格，复制后，选定要粘贴的区域，回车OK；先选要复制的目标单元格，复制后，选定要粘贴的不连续单元格，回车OK。,多张工作表中输入相同的内容几个工作表中同一位置填入同一数据时，可以选中一张工作表，然后按住Ctrl键，再单击窗口左下角的Sheet1、Sheet2.来直接选择需要输入相同内容的多个工作表，接着在其中的任意一个工作表中输入这些相同的数据，此时这些数据会自动出现在选中的其它工作表之中。输入完毕之后，再次按下键盘上的Ctrl键，然后使用鼠标左键单击所选择的多个工作表，解除这些工作表的联系。,Excel中的公式和函数,公式的概念公式Formula就是由用户自行设计并结合常数数据、单元格引用、运算符等元素进行数据处理和计算的算式。用户使用公式是为了有目的地计算结果，因此Excel的公式必须且只能返回值。,公式的结构,下面表达式就是一个简单的公式实例：=（C2+D2）*5从公式结构来看，构成公式的元素通常包括等号、常量、引用和运算符等元素。其中，等号是不可或缺的，公式通常以“=”开始（Excel的智能识别功能也允许使用“+”、“-”作为公示的开始，系统会自动前置“=”），否则Excel只能将其识别为文本。,公式的输入、编辑和复制,公式的输入、编辑和复制当单元格中输入“=”时，Excel就会识别其为公式输入的开始，按结束公示的编辑。如果要对原有公式进行编辑，使用以下几种方法可以进入单元格编辑状态。（1）选中公式所在单元格，并按住键。（2）双击公式所在单元格。（3）选中公式所在单元格，单击列表上方的编辑栏。,公式中输入负数只需在数字前面添加“-”即可，而不能使用括号。例如：=5*-10的结果是“-50”。Excel中规定所有的运算符号都遵从“由左到右”的次序来运算。数字居中小数点对齐选中某列，点击“居中”工具按钮；格式单元格自定义，输入“?.?”或“?.0?”类型的字符，问号个数可根据实际添减。,Excel公式的复制,如果在某个区域使用相同的计算方法，不必逐个编辑函数公式，因为公式也具有可复制性。在连续的区域中使用相同算法的公式，可以通过双击或拖动单元格右下角的填充柄向下填充即可进行公式的复制；如果公式所在的单元格区域不连续，还可以借助“复制”和“粘贴”功能来实现公式的复制。Step1：选中被复制运算公式的单元格，按下组合键，或者用鼠标右键复制公式。Step2：选择要复制的单元格区域，点击鼠标右键，点击“选择性粘贴”。Step3：在“选择性粘贴”对话框的“粘贴”类型下选择“公式”选项，单击“确定”按钮完成公式复制。,条件显示,利用If函数，可以实现按照条件显示。一个常用的例子，就是教师在统计学生成绩时，希望输入60以下的分数时，能显示为“不及格”；输入60以上的分数时，显示为“及格。这样的效果，利用IF函数可以很方便地实现。假设成绩在A2单元格中，判断结果在A3单元格中。那么在A3单元格中输入公式：=If（A20，SUM（A1：A5），0）此式就利用了嵌套函数，意思是，当A1至A5的和大于0时，返回这个值，如果小于0，那么就返回0。,输入分数,几乎在所有的文档中，分数格式通常用一道斜杠来分界分子与分母，其格式为“分子/分母”，在Excel中日期的输入方法也是用斜杠来区分年月日的，比如在单元格中输入“1/2”，按回车键则显示“1月2日”，为了避免将输入的分数与日期混淆，我们在单元格中输入分数时，要在分数前输入“0”（零）以示区别，并且在“0”和分子之间要有一个空格隔开，比如我们在输入1/2时，则应该输入“01/2”。如果在单元格中输入“81/2”，则在单元格中显示“81/2”，而在编辑栏中显示“8.5”。,输入负数,在单元格中输入负数时，可在负数前输入“-”作标识，也可将数字置在（）括号内来标识，比如在单元格中输入“（88）”，按一下回车键，则会自动显示为“-88”。,输入小数,当需要输入大量带有固定小数位的数字或带有固定位数的以“0”字符串结尾的数字时，可以采用下面的方法：选择“工具”、“选项”命令，打开“选项”对话框，单击“编辑”标签，选中“自动设置小数点”复选框，并在“位数”微调框中输入或选择要显示在小数点右面的位数，如果要在输入比较大的数字后自动添零，可指定一个负数值作为要添加的零的个数，比如要在单元格中输入“88”后自动添加3个零，变成“88000”，就在“位数”微调框中输入“-3”，相反，如果要在输入“88”后自动添加3位小数，变成“0.088”，则要在“位数”微调框中输入“3”。,输入日期,Excel是将日期和时间视为数字处理的，它能够识别出大部分用普通表示方法输入的日期和时间格式。用户可以用多种格式来输入一个日期，可以用斜杠“/”或者“-”来分隔日期中的年、月、日部分。比如要输入“2001年12月1日”，可以在单元各种输入“2001/12/1”或者“2001-12-1”。如果要在单元格中插入当前日期，可以按键盘上的Ctrl+;组合键。,输入时间,在Excel中输入时间时，用户可以按24小时制输入，也可以按12小时制输入，这两种输入的表示方法是不同的，比如要输入下午2时30分38秒，用24小时制输入格式为：2:30:38，而用12小时制输入时间格式为：2:30:38p，注意字母“p”和时间之间有一个空格。如果要在单元格中插入当前时间，则按Ctrl+Shift+;键。,工作表的管理,设定工作簿中工作表的数目“工具”“选项”；选择“常规”选项卡，在“新工作簿内的工作表数”框中写入或选择所需工作表数目，不得大于255。单击“确定”按钮。,插入工作表用鼠标单击欲插入位置的工作表标签，将要插入的工作表在选中位置的左侧；选择“插入”菜单中的插入类型：其中有工作表、图表供用户选择。,删除工作表用鼠标单击欲删除的工作表标签;“编辑”“删除工作表”，屏幕将显示提示：“要删除的工作表中可能存在数据。如果要永久删除这些数据，请按“删除”。单击“删除”。,工作表的改名用鼠标选中欲改名的工作标签;“格式”“工作表”,在其子菜单中再选择“重命名”,在标签中写入新名即可。,分类汇总,建立分类汇总表：分类汇总包含分类和汇总两个运算：先对某个字段进行分类（排序），然后再按照所分之类对指定的数值型字段进行某种方式的汇总。常用汇总方式有：求和、计数、求平均值、求最大值、求最小值等等。以学生成绩表为例，对各班的“英语”、“平均分”求平均值。先对“班级”字段排序，选数据库，然后“数据”“分类汇总”,数据透视表分类汇总适合于按一个字段分类；若要按多个字段分类，可利用数据透视表。数据透视表和数据透视图向导：步骤1指定数据源类型和报表类型。步骤2指定数据源区域。步骤3指定数据透视表的显示位置。,数据透视图数据透视图是数据透视表的图解，建立方法与数据透视表的基本相同。在数据透视表中执行快捷菜单的数据透视图命令可以更方便地建立数据透视图。,在Excel中自动推测出生年月日及性别的技巧,身份证号码已经包含了每个人的出生年月日及性别等方面的信息，新式的18位身份证而言，7-14位代表个人的出身年月日，而倒数第二位的奇数或偶数则分别表示男性或女性。根据身份证号码的这些排列规律，结合Excel的有关函数，我们就能实现利用身份证号码自动输入出生年月日及性别等信息的目的，减轻日常输入的工作量。,Excel中提供了一个名为MID的函数，其作用就是返回文本串中从指定位置开始特定数目的字符，该数目由用户指定，利用该功能我们就能从身份证号码中分别取出个人的出生年份、月份及日期，然后再加以适当的合并处理即可得出个人的出生年月日信息。,MID函数的格式为MID(text,start_num,num_chars)或MIDB(text,start_num,num_bytes)，其中Text是包含要提取字符的文本串；Start_num是文本中要提取的第一个字符的位置（文本中第一个字符的start_num为1，第二个为2以此类推）；至于Num_chars则是指定希望MID从文本中返回字符的个数。,假定某单位人员管理希望通过身份证号码自动得出员工的出生年月日及性别，现以此为例，将有关步骤向广大用户作一简要介绍：1.选中整个B列，然后执行“格式”菜单中的“单元格”命令，打开“单元格格式”窗口。2.选择“数字”选项卡。3.在“分类”栏中选择“文本”选项，然后单击“确定”按钮，关闭“单元格格式”窗口，将所有包含有身份证号码的单元格设置为文本格式。,4.将光标移至C3单元格中，然后输入“=MID(B3,7,4)&年&MID(B3,11,2)&月&MID(B3,13,2)&日)，（如果是15位身份证号“=19&MID(B3,7,2)&年&MID(B3,9,2)&月&MID(B3,11,2)&日内容）。其中MID(B3,7,2)就是从身份证号码的第7位开始取2位数，得出该员工的出生年份，MID(B3,9,2)就是得出该员工的出