模块四 Excel2010电子表格的应用.ppt

1、计算机应用基础win7,志远教育 教材征订与服务电话01082477073,志远教育作品,LOGO,1,LOGO,*,过渡页,Excel 2010工作表,工作表数据输入与工作表格式化,过渡页,2,LOGO,*,Excel 2010基本概念,1,启动Excel后，其操作界面如图4-1所示。Excel的窗口主要包括快速访问工具栏、标题、窗口控制按钮、选项卡、功能区、名称框、编辑栏、工作区、行号、列标、状态栏和滚动条等。,Excel 2010的用户界面,图4-1 Excel 的工作界面,3,LOGO,*,Excel 2010基本概念,1,Excel 2010专业术语,单元格引用,工作簿,单元格地址,

2、单元格和单元格区域,工作表,工作簿: 一般由3个工作表组成, 分别命名为：Sheet1Sheet3,最多可有255个工作表。当前活动的白色,其余灰色，其扩展名为.xlsx 工作表:工作的主要对象,由行,列组成的表格, 有：A,B,.Z,AA,AB,.AZ, .IA,IB,.IV(256)列; 1,2.65536行; 单元格:行和列的交叉处，表格的最小单位,每个单元格最多可填写32767字符 活动单元格: 当前正在操作的单元格，被黑线框住。 单元格区域：是一个矩形块，由工作表中相邻的若干个单元格组成。单元格地址：每个单元格的行列地址，如，B2,A3; 单元格区域：B2:E5。引用B3、A2单元格

3、,C4：F7区域，就用B3,A2,C4:F7表示。单元格引用：绝对坐标：$B,$2,$D$6;相对坐标：A1，C4; 混合坐标：A$4,4,LOGO,*,正文 . 模块四,Excel 2010工作表,目,一,Excel 2010基本操作,1,选择“文件”“新建”命令，在弹出的窗口中可看到有“可用模板”和“O模板”两大部分，如图4-2所示，双击“可用模板”中的“样本模板”可以看到本机上可用的模板。“O模板”是放在指定服务器上的资源，用户必须联网才能使用这些功能。,项,工作簿的操作,图4-2 Excel模板,5,LOGO,*,正文 . 模块四,Excel 2010工作表,目,一,Excel 201

4、0基本操作,1,（1）新建工作表新建工作表最快捷的方法是在现有工作表的末尾单击屏幕底部的“新建工作表”按钮。（2）移动或复制工作表移动工作表最快捷的方式：选中要移动的工作表，然后将其拖动到想要的位置。复制工作表，右击需要复制的一个或多个工作表，单击右键，在弹出的快捷菜单中选择“移动或复制工作表”命令，弹出如图4-3所示的对话框，按图示操作即可。,项,工作表的操作,6,LOGO,Excel 2010工作表,目,一,1,（3）删除工作表选中要删除的一个或多个工作表，右击，在弹出的快捷菜单中选择“删除”命令。（4）重命名工作表选中要重命名的工作表，右击，在弹出的快捷菜单中选择“重命名”命令，或者双击

5、工作表标签，均可对工作表表标进行重命名。（5）改变工作表标签颜色选中要改变标签颜色的工作表，右击，在弹出的快捷菜单中选择“工作表标签颜色”命令。 （6）更改新工作簿中的默认工作表数选择“文件”“选项”命令，然后在“常规”类别中的“新建工作簿时”下的“包含的工作表数”文本框中，输入新建工作簿时默认情况下包含的工作表数。默认新建工作表数最少为1，最多为255。,项,工作表的操作,7,LOGO,*,Excel 2010基本操作,1,单元格及单元格区域的操作,选中单元格或单元格区域 单元格（或单元格区域）的插入与删除：“开始”-“单元格” 清除：单击“编辑”功能区的橡皮擦按钮,8,1,单元格及单元格区

6、域的操作,（4）移动与复制移动操作：移动操作会移走除单元格本身之外的所有信息，包括公式及其结果值、单元格格式和批注等，粘贴时也包括所有信息。操作方法为：选中要移动的单元格或单元格区域，单击“开始”选项卡上的“剪贴板”功能区的“剪切”按钮（也可以使用【Ctrl+X】组合键）。选中目标单元格，再单击粘贴按钮即可。在移动公式时，无论使用哪种单元格引用，公式内的单元格引用都不会更改。,9,LOGO,*,Excel 2010工作表,目,一,Excel 2010基本操作,1,项,单元格及单元格区域的操作,（5）查找与替换单击“编辑”功能区的“查找”按钮可对工作表进行查找、替换、定位等操作，操作方法与Wor

7、d类似，在此不再赘述。,10,LOGO,*,Excel 2010工作表,目,一,Excel 2010基本操作,1,项,工作表的保护和共享,（1）保护工作簿的结构和窗口 单击“审阅”选项卡，在“更改”功能区单击“保护工作簿”按钮，弹出如图4-12所示的对话框，用户可以锁定工作簿的结构，以禁止用户添加或删除工作表，或显示隐藏的工作表。同时，还可禁止用户更改工作表窗口的大小或位置。工作簿结构和窗口的保护可应用于整个工作簿中的所有工作表。,11,LOGO,*,Excel 2010工作表,目,一,Excel 2010基本操作,1,项,工作表的保护和共享,（2）保护工作表单击图4-11中的“保护工作表”按

8、钮，弹出如图4-13所示的对话框，在该对话框中可以设置“允许此工作表的所有用户进行”列表中，选择希望用户能够进行的操作。,12,Excel 2010工作表,目,一,1,项,工作表的保护和共享,（3）保护单元格或单元格区域 设置不受保护的区域（即用户可以更改的区域），操作步骤如下： 选中要解除锁定（或隐藏）的单元格或单元格区域。 在“开始”选项卡上的“单元格”功能区中，单击“格式”按钮，然后选择弹出菜单最下端的“设置单元格格式”命令。 在“保护”选项卡上，根据需要选择清除“锁定”复选框或“隐藏”复选框，然后单击“确定”按钮。 在“审阅”选项卡上的“更改”功能区中单击“保护工作表”按钮。设置不受保

9、护区域也可以通过单击“允许用户编辑区域”按钮来设置选择希望用户能够更改的元素。 设置受保护的区域（即用户不可以更改的区域），操作步骤如下： 选中整张工作表中的所有单元格。 在“开始”选项卡上的“单元格”功能区中，单击“格式”，然后选择“设置单元格格式”命令。 单击“保护”选项卡，根据需要选择清除“锁定”复选框或“隐藏”复选框，然后单击“确定”按钮。则此状态下该工作表中所有的单元格都处于未被保护的状态。 选中需要保护的单元格区域，例如A3:D5。 再次单击“保护”选项卡，根据需要选中“锁定”复选框或“隐藏”复选框，然后单击“确定”按钮，则该工作表中仅A3:D5单元格区域处于被保护的状态。 在“审

10、阅”选项卡上的“更改”功能区中选择“保护工作表”命令。,13,LOGO,*,过渡页,Excel 2010工作表,输入Word文稿,过渡页,工作表数据输入与工作表格式化,14,LOGO,*,工作表数据输入与工作表格式化,目,二,1,要用Excel 2010进行数据信息的统计和分析，首先要建立一个二维表格。二维表格可以称为数据库中的关系型数据库。因此，一定要根据自己工作的目标对数据进行统计或分析前，选定要进行统计的各个项目，并冠以名字，在数据库中称为“字段”，在二维表格中是“列名称”，而参加统计的各元素，在同一行的所有单元格称为“记录”。,项,工作表数据输入基础,建立工作表,15,LOGO,*,工

11、作表数据输入与工作表格式化,目,二,1,使用Excel工作表的目的是要利用工作表进行数据的统计和分析。所以，在统计和分析之前，必须建立一个以统计或分析为目标的工作表格。工作过程如下： 根据工作目标要求，建立二维表格。尤其对目标统计或分析的项目，要建立完整的列字段名称。 对表格内的数据进行统计或分析。 可在表格中增加图表，以增加形象说明的力度。 工作表格格式化，增加工作表的视觉效果。,项,工作表数据输入基础,Excel 2010数据统计分析过程,16,LOGO,*,正文 . 模块四,工作表数据输入与工作表格式化,目,二,1,Excel 2010中的文本通常是指字符或者是任何数字和字符的组合。任何

12、输入到单元格内的字符集，只要不被系统识别成数字、公式、日期、时间、逻辑值，则Excel一律将其视为文本。在Excel中输入文本时，默认对齐方式是单元格内靠左对齐。在一个单元格内最多可以存放32767个字符。对于全部由数字组成的字符串，如邮政编码、身份证号码、电话号码等这类字符串，为了避免输入时被Excel认为是数值型数据，Excel 2010提供了在这些输入项前添加“”（英文的单引号）的方法，来区分是“数字字符串”而非“数值”数据。例如，要在“B5”单元格中输入非数字的电话号“02088886666”，则可在输入框中输入“02088886666”。,项,文本输入,文本输入,17,LOGO,*,

13、正文 . 模块四,1,在Excel 2010中，当建立新的工作表时，所有单元格都采用默认的通用数字格式。通用格式一般采用整数（无千位分隔符）、小数（二位，如7.89）、负数格式，而当数字的长度超过单元格的宽度时，Excel将自动使用科学计数法来表示输入的数字。在Excel中，输入单元格中的数字按常量处理。输入数字时，自动将它沿单元格右对齐。有效数字包含09、+、-、（）、/ 、$、%、.、E、e等字符。输入数据时可参照以下规则： 可以在数字中包括逗号，以分隔千分位。 输入负数时，在数字前加一个负号（-），或者将数字置于括号内。例如，输入“-20”和“（20）”都可在单元格中得到-20。 Exc

14、el忽略数字前面的正号（+）。 输入分数（如2/3）时，应先输入“0”及一个空格，然后输入“2/3”。如果不输入“0”，Excel会把该数据作为日期处理，认为输入的是“2月3日”。 当输入一个较长的数字时，在单元格中显示为科学计数法（如2.56E09），意味着该单元格的列宽不能显示整个数字，但实际数值仍不变。,数字输入,18,LOGO,*,工作表数据输入与工作表格式化,目,二,1,项,数字输入,减少小数位数,使用货币样式,增加小数位数,使用千位分隔样式,使用百分比样式,使用“开始”选项卡“数字”功能区按钮快速格式化数字“格式”,详见：P143-P144,19,LOGO,*,1,数字输入,使用“

15、设置单元格格式”对话框设置数字格式,右击使用快捷菜单或“数字”功能区右下角的下拉按钮，可打开“设置单元格格式”对话框。,20,LOGO,1,公式输入,算术运算符 算术运算符可以完成基本的数学运算，如加、减、乘、除等，还可以连接数字并产生数字结果。算术运算符包括：加号（+）、减号（-）、乘号（*）、除号（/）、百分号（%）以及乘幂（）.文本运算符 在公式中使用文本运算符（）时，以等号开头输入文本的第一段（文本或单元格引用），加入文本运算符，输入下一段（文本或单元格引用）。例如，用户在单元格A1中输入“第一季度”，在A2中输入“销售额”。在C3单元格中输入“A1累计A2”，结果会在C3单元格显示“

16、第一季度累计销售额”。比较运算符 比较运算符可以比较两个数值并产生逻辑值TRUE或FALSE。比较运算符包括=（等于）、（大于）、（不等于）、=（大于等于）引用运算符 一个引用位置代表工作表上的一个或者一组单元格，引用位置告诉Excel在哪些单元格中查找公式中要用的数值。有三种引用运算符：冒号(区域运算符)、逗号(联合运算符)以及空格(交叉运算符)。见P146。,在Excel工作表的单元格输入公式时，必须以一个等号(=)作为开头。等号后面的“公式”中可以包含各种运算符号、常量、变量、函数以及单元格的引用。,21,LOGO,*,工作表数据输入与工作表格式化,目,二,1,项,公式和函数输入,公式输

17、入,公式的修改和编辑在Excel 2010编辑公式时，被该公式所引用的所有单元格及单元格区域的引用都将以彩色显示在公式单元格中，并在相应单元格及单元格区域的周围显示具有相同颜色的边框。当用户发现某个公式中含有错误时，需单击选中要修改公式的单元格。按【F2】键使单元格进入编辑状态，或直接在编辑栏中对公式进行修改。此时，被公式所引用的所有单元格都以对应的彩色显示在公式单元格中，使用户很容易发现哪个单元格引用错了。编辑完毕后，按【Enter】键确定或单击编辑栏中的按钮确定。如果要取消编辑，按【Esc】键或单击编辑栏中的按钮退出编辑状态。,22,1,函数输入,LOGO,23,LOGO,*,1,公式和函

18、数输入,函数输入,【例1】函数的使用。要求使用函数，求出图4-15所示工资表中职工号为51001职工的实发工资。使用函数的操作: 第一步，选中需要使用函数的单元格F2，单击编辑栏中的插入函数按钮。第二步，根据工作目标，找到最适合目标要求的函数，如本例要求求出工资表中第一个职工的实发工资，实际上，实发工资是由职务工资与补贴之和组成，所以要用求和函数SUM()。第三步，要将合适的数据“填入”函数的括号中的参数，就可以完成或求出函数操作的结果数值。,24,LOGO,1,公式和函数的复制单元格公式引用,（1）相对引用在输入公式的过程中，除非用户特别指明，Excel一般是使用相对地址来引用单元格的位置。

19、所谓相对地址是指：如果将含有单元地址的公式复制到另一个单元格时，这个公式中的各单元格地址将会根据公式移动到的单元格所发生的行、列的相差值，作同样的改变，以保证这个公式对表格其他元素的运算的正确。例如，将如图4-17所示的F2单元格复制到F3:F9，把光标移至F5单元格，会发现公式已经变为“=(C5+D5+E5)/3”，因为从F2到F5，列的偏移量没有变，而行作了一行的偏移，所以公式中涉及的列的数值不变而行的数值自动加3。其他各个单元格也做出了改变。,25,LOGO,1,公式和函数的复制单元格公式引用,（2）绝对地址的使用如果公式运算中，需要某个指定单元格的数值是固定的数值，在这种情况下，就必须

20、使用绝对地址引用。所谓绝对地址引用，是指对于已定义为绝对引用的公式，无论把公式复制到什么位置，总是引用起始单元格内的“固定”地址。在Excel中，通过在起始单元格地址的列号和行号前添加美元符“$”，如$A$1来表示绝对引用。例如，在如图4-17所示的例子中，如果将F2中输入的相对地址改为绝对地址，当F2复制到F3:F9时，会出现如图4-18所示的结果，所有的学生的平均成绩都是“李晓科”的平均成绩。,26,LOGO,1,公式和函数的复制单元格公式引用,（3）混合地址引用单元格的混合引用是指公式中参数的行采用相对引用、列采用绝对引用；或列采用绝对引用、行采用相对引用，如$A3、A$3。当含有公式的

21、单元格因插入、复制等原因引起行、列引用的变化时，公式中相对引用部分随公式位置的变化而变化，绝对引用部分不随公式位置的变化而变化。例如，制作九九乘法表。步骤如下： 在B2单元格中输入“=B$1&*&$A2&=&B$1*$A2”。 将B2复制到B3:B10。 将B3复制C3，再将C3复制到C4:C10。 将C4复制到D5，再将D5复制到D6:D10。 依此类推，可完成九九乘法表的制作，如图4-19所示。,27,LOGO,*,工作表数据输入与工作表格式化,目,二,1,项,创建迷你图,什么是迷你图,迷你图是工作表单元格中的一个微型图表（不是对象），可提供数据的直观表示。使用迷你图可以显示一系列数值的趋

22、势（例如，季节性增加或减少、经济周期），或者可以突出显示最大值和最小值。在数据旁边放置迷你图可达到最佳效果。下面将以一个市场咨询发布的20082014中国网购市场交易规模及预测数据（见图4-20）为例来创建“较易额”的迷你图。,28,LOGO,*,1,创建迷你图,迷你图的创建, 选择要在其中插入迷你图中的一个空白单元格或单元格区域。本例选中I3。 单击“插入”选项卡，在“迷你图”功能区选择要创建的迷你图的类型“柱形图”，如图4-21所示。弹出如图4-22所示的对话框。 在“创建迷你图”对话框时 “数据范围”框中，输入包含创建迷你图所基于的数据单元格区域B3:H3。”位置范围”为$I$3，单击“

23、确定”按钮。注：如果需要在多个单元格创建相同类型的迷你图，此处也可以选择一个单元格区域。用同样的方法可在I4单元格中创建一个“同比增大”折线图，结果如图4-23所示。,迷你图的创建,29,LOGO,*,工作表数据输入与工作表格式化,目,二,1,项,创建迷你图,迷你图的编辑与格式化,在工作表中选中一个或多个迷你图，将会出现“迷你图工具”，并显示“设计”选项卡。在“设计”选项卡中包括 “迷你图”、“类型”、“显示”、“样式”和“分组”等多个功能区，如图4-24所示。,30,LOGO,*,工作表数据输入与工作表格式化,目,二,1,项,提高数据输入正确性和效率的方法,自动完成,在同一列中，对于在上面单

24、元格曾经输入过的字符，在紧接的单元格如输入其中的第一个字时，Excel能自动填入其后的字符，如图4-25所示。当在C6单元格中输入“人”后，在“人”后Excel能自动填入“事部”，并以反白显示，按【Enter】键即可，否则无须理会，继续输入其他字符。,31,LOGO,*,工作表数据输入与工作表格式化,目,二,1,项,提高数据输入正确性和效率的方法,选择列表,在同一列中反复输入相同的几个字段值，如要反复输入“人事部”和“销售部”。可以在输入了“人事部”和“销售部”以后，在待输入的新单元格上右击，在弹出的快捷菜单中选择“从下拉列表中选择”命令，单元格下方就会弹出一下拉列表框，该列表中记录了该列出现

25、过的所有数据，如“人事部”和“销售部”，如图4-26所示，只要从列表中选择“人事部”即可完成输入。,32,LOGO,*,1,提高数据输入正确性和效率的方法,利用“自定义序列”自动充填数据,对于需要经常使用的特殊数据序列，例如一组多次重复使用的字符或中文序列号，可以将其定义为一个序列，在输入表格数据时，可使用“自动填充”功能，将数据自动输入到工作表中。（1）使用自动填充功能之前，必须利用“自定义序列”增加本次要输入的数据系列。操作步骤如下： 选择“文件”“选项”命令，弹出“Excel选项”对话框， 选择“Excel选项”对话框中的“高级”选项，在右侧窗口中单击“编辑自定义列表”按钮，如图4-27

26、所示。,33,LOGO,*,工作表数据输入与工作表格式化,目,二,1,项,提高数据输入正确性和效率的方法,利用“自定义序列”自动充填数据, 在“输入序列”文本框中输入“主机”，然后按【Enter】键，然后输入“显示器”，再次按【Enter】键，重复操作该过程，直到输入所有的数据。 单击“添加”按钮，就可以看到自定义的序列已经出现在对话框中，如图4-28所示。,34,LOGO,*,工作表数据输入与工作表格式化,目,二,1,项,提高数据输入正确性和效率的方法,数据的自动填充,字符自动填充功能可以把单元格的内容复制到同行或同列的相邻单元格，也可以根据单元格的数据自动产生一串递增或递减序列。例如，在图

27、4-29中，把光标移至C6单元格右下角的填充柄（此时鼠标会变成十字形状），拖动至C8单元格，那么C6单元格的内容就被复制到C7:C8区域，如图4-29所示。,35,LOGO,*,1,提高数据输入正确性和效率的方法,序列填充,上面所列的自动填充一般是以列（或以行）为填充对象进行有规律的填充。但对于等比数列或工作日的自动填充上述方法就很难完成。对于特殊情况可用下面的方法完成。 选择初始单元格A2，填入第一个序列号，如输入10001。 单击“开始”选项卡“编辑”功能区的“填充”按钮，选择“序列”命令，弹出如图4-30所示的对话框。在对话框的“序列产生在”选项区域中选中“列”单选按钮，之后在“类型”选

28、项区域中选中“等差序列”单选按钮。在“步长值”文本框中输入“3”，终止值填入10019，单击“确定”按钮，就能看到如图4-31所示的序列。,36,LOGO,*,1,提高数据输入正确性和效率的方法,数据有效性输入,（1）数据有效性的设置数据有效性的输入提示信息和出错提示信息功能，是利用数据有效性功能，在用户选定的限定区域的单元格，或在单元格中输入了无效数据时，显示自定义的提示信息或出错提示信息。例如，在工作表中，为C4:C11单元格区域按如下步骤进行数据有效性设置。 选择单元格区域C4:C11。 选择“数据”选项卡，单击“数据工具”功能区的“数据有效性”按钮，如图4-32所示。在弹出的对话框中选

29、择“设置”选项卡，在“有效性条件”选项区域的“允许”下拉列表框中选择“整数”选项，然后完成如图4-33所示的设置。,37,LOGO,*,1,提高数据输入正确性和效率的方法,数据有效性输入,（1）数据有效性的设置 单击“输入信息”选项卡，在“标题”文本框中输入“成绩”，在“输入信息”文本框输入“请输入口语成绩”。 单击“出错警告”选项卡，在“标题”文本框中输入“错误”，在“出错信息”文本框输入“必须介于0100之间”。 单击“确定”按钮。设置完成后，当指针指向该单元格时，就会出现如图4-34所示的提示信息。如果在其中输入了非法数据，系统还会给出警告信息。,38,LOGO,*,1,提高数据输入正确

30、性和效率的方法,数据有效性输入,（2）特定数据序列利用数据有效性功能，设置特定的数据系列。例如，在“加班情况登记”工作表中，当鼠标指针指向C2:C19单元格区域任意一个单元格的时候，显示下拉列表框，提供“技术部”、“销售部”、“办公室”三个数据供选择，如图4-35所示。设置特定数据序列的操作步骤如下： 选择单元格区域C2:C19。 选择“数据”选项卡，单击“数据工具”功能区的“数据有效性”按钮，在弹出的对话框中选择“设置”选项卡，在“有效性条件”选项区域的“允许”下拉列表框中，选择“序列”选项，在“来源”文本框中输入“技术部,销售部,办公室”，需要注意的是各选项之间要用英文的逗号相隔。 单击“

31、确定”按钮，如图4-36所示。,39,LOGO,*,工作表数据输入与工作表格式化,目,二,1,项,提高数据输入正确性和效率的方法,特定区域内一组数字的快速输入,【例2】请输入学生成绩表中6个同学语文、数学、英语和生物四科的成绩，如图4-37所示。 鼠标在需要输入的起始单元格C2开始拖动到F9。这个区域已变成蓝色，只有起始单元格C2是白色。 在白色单元格输入第一个学生的语文成绩80后按【Tab】键，白色区域右移，再输入该学生的数学成绩，按【Tab】键，依此类推，直至全部成绩输入完成。,40,LOGO,*,工作表数据输入与工作表格式化,目,二,1,项,提高数据输入正确性和效率的方法,自定义输入,在

32、数据输入的过程中有许多的重复项，但又没有很强的规律性，无法使用前面所讲的方法进行快速录入。图4-38所示的第F列的数据，如果每一个数据都重复录入“p00-000-”等内容，将是一件很繁琐的事。解决这个问题可以使用“单元格格式”对话框中的“数学”选项卡下的“自定义”。操作步骤如下：,41,LOGO,*,工作表数据输入与工作表格式化,目,二,1,项,提高数据输入正确性和效率的方法,自定义输入, 选中第F列需要自定义格式的单元格区域，在本例中可选中F2:F7。 在“开始”选项卡下单击“数字”功能区右下角的下拉按钮弹出如图4-39所示的对话框。,42,LOGO,*,工作表数据输入与工作表格式化,目,二

33、,1,项,提高数据输入正确性和效率的方法,自定义输入, 在“数字”选项卡下选“自定义”选项，在“类型”文本框中输入“p00-000-0000”，单击“确定”按钮。 在F2:F7中分别输入：123、24、8即可得到如图4-38所示的结果。自定义格式有很多的格式设置，限于篇幅本节不再讲述，希望本例能起到抛砖引玉的作用。,43,LOGO,*,工作表数据输入与工作表格式化,目,二,1,项,提高数据输入正确性和效率的方法,获取外部数据,【例3】将记事本文件中的“学生信息.txt”导入到Excel表中。操作步骤如下： 选择“数据”选项卡，在“获取外部数据”功能区单击“自文本”按钮，如图4-40所示。,44

34、,LOGO,*,正文 . 模块四,工作表数据输入与工作表格式化,目,二,1,项,提高数据输入正确性和效率的方法,获取外部数据, 在弹出如图4-41所示的对话框中，找到需要导入的数据源“学生信息.txt”，单击“导入”按钮弹出“文本导入向导”的三个步骤，可以分别对源文本的分隔符、导入起始行、目标数据区的格式等项目进行设置。单击“完成”按钮弹出如图4-42所示的对话框。,45,LOGO,*,1,提高数据输入正确性和效率的方法,获取外部数据, 在如图4-42所示的对话框中设置导入数据的位置为“现有工作表”的$A$1单元格，单击“确定”按钮。操作结果如图4-43所示。,46,LOGO,*,1,工作表格

35、式化,【例4】请对“餐馆进货单”进行格式设置，设置前后的效果如图4-44所示。具体要求如下： 将A1单元格中的表格标题“某餐馆进货单”格式设置为12号宋体加粗，并使用A1:D1单元格合并居中； 将A2:D2单元格区域跨列合并，单元格内容右对齐； 将单价(即B4:B14)和金额(即D4:D15)设置为保留两位小数，并在数据前加人民币符号。 将A3:D14单元格区域的内边框设置为虚线，外加边框设置为双实线。 将B4:B14区域中超过5的数据设置为加粗倾斜并加12.5%的浅灰色底纹。,47,LOGO,*,1,工作表格式化,获取外部数据,操作步骤如下： 选中A1:D1单元格区域，在“开始”选项卡中单击

36、“对齐方式”功能区中的“合并后居中”按钮，在“字体”功能区将字体设置为“宋体”，字号设置为“12”，单击按钮使字体加粗。 选中A2:D2单元格区域，在“开始”选项卡，单击“对齐方式”功能区中右面的下拉按钮，在弹出的选项中选“跨越合并”按钮，再单击使单元格中的数据右对齐。,48,LOGO,*,1,工作表格式化,获取外部数据, 同时选中B4:B14和D4:D15单元格区域(方法为：先选中B4:B14单元格区域，按住Ctrl键不放，再选中D4：D15单元格区域)，在“数字”功能区单击按钮“中文(中国)”按钮。 选中A3:D14单元格区域，右击，在弹出的快捷菜单中选择“设置单元格格式”命令，在弹出的窗

37、口中单击“边框”选项卡，如图4-45所示。 选中B4:B14单元格区域，在如图4-46所示的“样式”功能区选择“条件格式”“突出显示单元格规则”“大于”命令，可弹出如图4-47所示的对话框。,49,LOGO,*,过渡页,工作表的数据统计和分析,输入Word文稿,过渡页,50,LOGO,*,要用好数据统计和数据分析，首先必须熟悉函数的使用功能以及参数设置规则，有针对性地利用单元格复制（地址引用）的相对地址和绝对地址的运用，准确输入函数统计分析用的数值或数据区域，才能使函数的统计分析有满意的运行结果。“数据”选项卡中的排序、筛选、合并计算、模拟分析、分类汇总和“插入”选项卡中的数据透视表等分析工具

38、一般是对整个工作表的统计或数据分析，所以在统计分析前，要选取连字段名在内的工作表的全部区域。,数据统计与分析概述,51,LOGO,*,数据统计与分析概述,52,LOGO,*, 求平均分：求考试成绩的平均值，用函数AVERAGE()，结果放在单元格D11。 函数AVERAGE()的用法是：AVERAGE(number1,number2，），括号内的参数可以是一组数字，最多为30个。如要求15、38、90的平均数，就可以写AVERAGE(15,38,90），或数值所在单元格区域，如本例中，数学考试成绩的区域在D2:D10，语文成绩在E2:E10，就可用AVERAGE(D2:D10）和AVERAGE

39、(E2:E10）。 （请大家分析数学考试中有个字符“缺考”，用了D2:D10区域，对数学的平均数有没有影响？回答是没有，因为区域内只有数值才能参加统计运算）在D11中直接输入=AVERAGE(D2:D10）或者选中D11，单击编辑栏的插入函数按钮，在弹出的对话框中选择“统计”中的“AVERAGE函数”选项，在参数中输入D2:D10，单击“确定”按钮。,求学生每科的平均分、合格率、最高分、最低分、两科总分,统计函数、IF函数、BANK函数、排序、筛选的应用,53,LOGO,*, 求最高分：用MAX(number1,number2,函数，用法同平均值。在D15中输入=MAX(D2:D10)。 求最

40、低分：用MIN(number1,number2,)函数。在D16中输入=MIN(D2:D10)。 求参加考试人数：用COUNT(value1,value2, .)，求区域内有数值的单元格个数，“有数值”是表示有考试成绩，“缺考”不是数值，是字符。参加数学考试的人数：在D12中输入COUNT(D2:D10)。 求合格人数：用COUNTIF(range,criteria)函数，求符合条件的单元格个数，放在D13单元格中。,求学生每科的平均分、合格率、最高分、最低分、两科总分,统计函数、IF函数、BANK函数、排序、筛选的应用,54,LOGO,*, 求合格率：合格率是参加考试的合格人数除以参加考试的

41、人数。本案例数学考试人数在D12，合格人数在D13，在单元格D14中输入公式=D13/D12，“/”表示“除以”。 求每个学生的语文和数学两科成绩总分，有两个方法：方法1：如在F2单元格中输入=D2+E2。方法2：用函数SUM()。本题是在F2中输入=SUM（D2:E2）。,求学生每科的平均分、合格率、最高分、最低分、两科总分,统计函数、IF函数、BANK函数、排序、筛选的应用,55,LOGO,*, 将语文成绩高于80分的学生选出。 操作步骤如下：选中数据区域A1:F10或其中的任意一个单元格。选择“数据”选项卡，在“排序与筛选”功能区单击“筛选”按钮，此时每一个字段名的右边都有一个下拉按钮。

42、如图4-51所示，单击“语文”中的下拉按钮，在下拉菜单中选择“数字筛选”-“大于”命令，打开图4-52 所示的对话框。在对话框左边的输入框中选择“大于”选项，在右边的输入框中输入80，单击“确定”按钮。,挑选出符合分析条件的学生,统计函数、IF函数、BANK函数、排序、筛选的应用,56,LOGO,*, 将语文成绩大于60分，且小于90分的学生选出。分析：语文成绩大于60分，且小于90分，是同一字段列中的“与”条件。因为是在同一列，可以用“自动筛选”完成。操作步骤如下：单击“数据”选项卡，在“排序与筛选”功能区单击“自动筛选”按钮，取消先前的筛选结果。选中A1:F11单元格区域或其中的任意一个单

43、元格。单击“数据”选项卡，在“排序与筛选”功能区单击“自动筛选”按钮。单击“数字筛选”-“自定义筛选”按钮。在图4-54所示的对话框中分别输入大于60，第二行输入小于90，选中“与”单选按钮，单击“确定”按钮，结果如图4-55所示。,挑选出符合分析条件的学生,统计函数、IF函数、BANK函数、排序、筛选的应用,57,LOGO,*, 将语文成绩大于70分的男生选出。 分析：要将符合两个以上不同列的条件的数据筛选出来，如果使用自动筛选来完成，需要对“语文”和“性别”两个字段分别进行筛选。方法与上两例相似，在此不再赘述。 在“高级筛选”操作中，必须在工作表的一个位置设置“条件区域”。 “与”条件：将

44、两个条件放在同一行，表示语文成绩大于70分的男生，如图5-56所示。 “或”条件：将两个条件放在不同行，表示语文成绩大于70的学生或者是男生，如图4-57所示。,统计函数、IF函数、BANK函数、排序、筛选的应用,58, 将语文成绩大于70分的男生选出。操作步骤如下：输入条件区域：在D12处输入“语文”，E12处输入“性别”，对照在其下面的单元格D13中输入“70”和E13中输入“男”。选中A1:F11单元格区域或其中的任意一个单元格。单击“数据”选项卡，在“排序与筛选”功能区单击“高级”按钮。弹出如图4-58所示的对话框。在打开的对话框中选中“将筛选结果复制到其他位置”单选按钮。如果列表区域

45、为空白，可单击“列表区域”右边的按钮，用鼠标从条件区域的A1拖动到F10，输入框中出现“$A$1:$F$10”。再单击“条件区域”右边的按钮，用鼠标从条件区域的D12拖动到E13，输入框中出现“$D$12:$E$13”。单击“复制到”右边的按钮后，选择筛选结果显示区域的第一个单元格，如A15。单击“确定”按钮，结果如图4-59所示。,挑选出符合分析条件的学生,59,LOGO,*, 对两科总分从高分到低分进行排序。（用“数据”功能区的“排序”命令按钮） 对两科总分进行排位操作。（用函数BANK()操作。） 对两科总分高于和等于150分的同学设置“录取”，小于150分的同学设置“不录取”。（用函数

46、IF()操作）,请分别对图4-48的成绩表完成以下操作任务,统计函数、IF函数、BANK函数、排序、筛选的应用,60,LOGO,*,工作表的数据统计和分析,目,三,【例5】对两科总分从高分到低分进行排序。 单击F1:F10数据区域的任意一个单元格。 单击“数据”选项卡中“排序和筛选”功能区的“排序”按钮（如果是升序则单击按钮）即可，如图4-61所示。,项,统计函数、IF函数、BANK函数、排序、筛选的应用,61,LOGO,*,如果是对多个关键字排序或者需要按别的方式排序，需要单击“排序”按钮，弹出图4-62所示的对话框。在该对话框中：单击“添加条件”按钮可设置多个关键字排序。单击“排序依据”按

47、钮可以设置按“数值”或“单元格颜色”或“字体颜色”排序。单击“次序”按钮可以分别对每一个关键字设置排序方式。单击“选项”按钮可以将排序“方向”设置为“按列排序”或“按行排序”。将排序“方法”设置为“字母”排序或“笔划”排序。,统计函数、IF函数、BANK函数、排序、筛选的应用,62,【例6】对两科总分进行排位操作。（用RANK()函数） 在单元格G2中输入函数RANK()，弹出图4-63所示的对话框。 在第一个输入框中输入“F2”，即需要排位的数值所在的单元格。在第二个输入框中输入需要对它进行排位的数值区域F2:F10，但必须用绝对地址表示，要写成$F$2:$F$10。 单击“确定”按钮，结果如图4-64所示。 选取G2后，用填充柄下拉，结果如图4-65所示。,