Excel的统计功能及其运用

第五讲Excel的统计功能及其运用魏代俊2013-06本次课要达到的目的课时：6课时Excel作为教育管理中统计图表的处理工具，通过温习Excel的数据处理和统计功能，为学习教育管理技术中图表的处理奠定基础。Excel正确输入身份证号码设置单元格格式为文本；或者在输入数值前加入英文输入法下的’号。Excel数据输入范围控制成绩一般为0-100，设置有效性时，选定小数，0.0到100.0Excel数据表格中如何按条件显示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”，再敲回车就行了。不连续单元格填充同一数据启动Excel，选中一个单元格，按住Ctrl键，用鼠标单击其他单元格，就将这些单元格全部选中了，在编辑区中输入数据，然后按住Ctrl键，同时按一下回车，在所有选中的单元格中都出现了同一数据。自动调整列宽选中要调整的列，从Excel菜单栏中选择：格式→列→最合适的列宽即可。或者选中要调整的列，将鼠标移动到任意被选中的两列的列标交界处，光标会变为带左右箭头形状（就是我们要手动调整列宽的位置），左键双击平均分布各行/列在标尺栏上选中相应的行/列：再拖动其中一个行/列的行宽/列高即可调整所有行或列为同一宽度/高度。另外一种方法就是选中相应的行/列，在行高/列宽对话框中直接输入相应的数值即可。让序号原地不动有时我们对数据进行排序时，序号全乱了。如果在序号列与正文列之间插入一个空列，再排序时，序号就不会乱了。位于序号前面的列中的内容均不发生改变；为了不影响显示美观和正常打印，可将这一空列隐藏起来。平均分布各行/列自动调整列宽选中要调整的列，从Excel菜单栏中选择：格式→列→最合适的列宽即可。或者选中要调整的列，将鼠标移动到任意被选中的两列的列标交界处，光标会变为带左右箭头形状（就是我们要手动调整列宽的位置），左键双击在标尺栏上选中相应的行/列：再拖动其中一个行/列的行宽/列高即可调整所有行或列为同一宽度/高度。另外一种方法就是选中相应的行/列，在行高/列宽对话框中直接输入相应的数值即可。复制、粘贴中回车键妙用①先选要复制的目标单元格，复制后，直接选要粘贴的单元格，回车OK；②先选要复制的目标单元格，复制后，选定要粘贴的区域，回车OK；③先选要复制的目标单元格，复制后，选定要粘贴的不连续单元格，回车OK。多张工作表中输入相同的内容几个工作表中同一位置填入同一数据时，可以选中一张工作表，然后按住Ctrl键，再单击窗口左下角的Sheet1、Sheet2......来直接选择需要输入相同内容的多个工作表，接着在其中的任意一个工作表中输入这些相同的数据，此时这些数据会自动出现在选中的其它工作表之中。输入完毕之后，再次按下键盘上的Ctrl键，然后使用鼠标左键单击所选择的多个工作表，解除这些工作表的联系。Excel中的公式和函数公式的概念公式Formula就是由用户自行设计并结合常数数据、单元格引用、运算符等元素进行数据处理和计算的算式。用户使用公式是为了有目的地计算结果，因此Excel的公式必须且只能返回值。公式的结构下面表达式就是一个简单的公式实例：=（C2+D2）*5从公式结构来看，构成公式的元素通常包括等号、常量、引用和运算符等元素。其中，等号是不可或缺的，公式通常以“=”开始（Excel的智能识别功能也允许使用“+”、“-”作为公示的开始，系统会自动前置“=”），否则Excel只能将其识别为文本。公式的输入、编辑和复制公式的输入、编辑和复制当单元格中输入“=”时，Excel就会识别其为公式输入的开始，按Enter结束公示的编辑。如果要对原有公式进行编辑，使用以下几种方法可以进入单元格编辑状态。（1）选中公式所在单元格，并按住F2键。（2）双击公式所在单元格。（3）选中公式所在单元格，单击列表上方的编辑栏。公式中输入负数只需在数字前面添加“-”即可，而不能使用括号。例如：=5*-10的结果是“-50”。Excel中规定所有的运算符号都遵从“由左到右”的次序来运算。数字居中小数点对齐①选中某列，点击“居中”工具按钮；②格式－单元格－自定义，输入“????.????”或“????.0????”类型的字符，问号个数可根据实际添减。Excel公式的复制如果在某个区域使用相同的计算方法，不必逐个编辑函数公式，因为公式也具有可复制性。在连续的区域中使用相同算法的公式，可以通过双击或拖动单元格右下角的填充柄向下填充即可进行公式的复制；如果公式所在的单元格区域不连续，还可以借助“复制”和“粘贴”功能来实现公式的复制。Step1：选中被复制运算公式的单元格，按下Ctrl+C组合键，或者用鼠标右键复制公式。Step2：选择要复制的单元格区域，点击鼠标右键，点击“选择性粘贴”。Step3：在“选择性粘贴”对话框的“粘贴”类型下选择“公式”选项，单击“确定”按钮完成公式复制。条件显示利用If函数，可以实现按照条件显示。一个常用的例子，就是教师在统计学生成绩时，希望输入60以下的分数时，能显示为“不及格”；输入60以上的分数时，显示为“及格。这样的效果，利用IF函数可以很方便地实现。假设成绩在A2单元格中，判断结果在A3单元格中。那么在A3单元格中输入公式：=If（A260，“不及格”，“及格”）同时，在IF函数中还可以嵌套IF函数或其它函数。例如，如果输入：=If（A260，“不及格”，If（A2=90，“及格”，“优秀））就把成绩分成了三个等级。如果输入=If（A260，“差，If（A2=70，“中”，If（A290，“良”，“优”）））就把成绩分为了四个等级。再比如，公式：=If（SUM（A1：A50，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从文本中返回字符的个数。假定某单位人