表格学习完整本.pptx

1.方便的表格制作方便的表格制作2.强大的计算能力强大的计算能力 3.丰富的图表制作能力丰富的图表制作能力4.数据库管理功能数据库管理功能5.具有WebWeb特性的协作和信息共享 的基本功能的基本功能第1页/共208页基本概念基本概念1.Excel的启动与退出1）启动启动Excel启动Excel的方法有很多种,常用的三种启动方法：单击桌面上的【开始】菜单，选择“程序”一栏，在其中找到Microsoft Excel，然后直接单击它就可以了；双击桌面上已有的Microsoft Excel的快捷方式启动Excel2000；双击已有的Excel工作簿文件（扩展名为XLS）启动Excel2000。第2页/共208页2）退出退出Excel下列介绍常用的五种退出Excel的方法：单击Excel窗口标题栏右端的关闭按钮。单击Excel窗口【文件】菜单的“退出”命令。双击Excel窗口标题栏的控制菜单按钮。单击Excel窗口标题栏的控制菜单按钮，并选择“关闭”命令。按快捷键Alt+F4。Excel 基本概念基本概念-第3页/共208页2.Excel 窗口窗口-参看书窗口由标题栏、菜单栏、工具栏、编辑栏和状态栏等组成。其中的标题栏、菜单栏、常用工具栏、格式工具栏、状态栏等与Word2000基本上相同。我们来看一下不同的地方，如编辑栏，该栏是用来输入或编辑当前（活动）单元格的值或公式。该栏的左侧为名称框，它显示当前单元格或区域的地址或名称。第4页/共208页3工作簿工作簿Excel的文件（扩展名为XLS）就是工作簿，即工作簿是一个Excel文件。一个工作簿由一个或多个工作表（最多255个）组成。默认情况下，一个新的工作簿由3个工作表组成，它们分别为Sheet1、Sheet2和Sheet3，但用户可以根据需要修改工作表的名称，工作表的个数也可以增减。第5页/共208页工作表是Excel完成一项工作的基本单位。工作表就象一个表格，由含有数据的行和列组成。纵向称为列，由列号区的字母分别加以命名（A、B、C）；横向称为行，由行号区的数字分别加以命名（1、2、3）。每一张工作表最多可以有65536行256列数据。每张工作表都由一个工作表标签（如Sheet1）与之对应。工作表工作表第6页/共208页工作表中行列交汇处的区域称为单元格。单元格由它在工作表中的位置所标识，列用字母标识，行用数字标识，如图5-1所示，A2、B1、C3就是单元格对应的位置。A2表示单元格位于第一列（A）第二行（2）。A2、B1、C3等也就是单元格的地址，称为单元格引用。目前高亮（粗边框）的单元格为当前单元格，也称活动单元格。单击某一单元格，该单元格就变为活动单元格。由一些相邻单元格组成的矩形区域，称为单元格区域，可以用矩形区的两对角单元格地址来标识它，两地址间加冒号隔开，如C2:F4。4.4.单元格与当前（活动）单元格单元格与当前（活动）单元格 第7页/共208页5.2 Excel2000基本操基本操作作 建立、保存、打开和关闭工作簿工作表的数据录入处理工作簿中的工作表编辑工作表 第8页/共208页1.创建新工作簿创建新工作簿启动Excel后，会自动产生一个名为Book1的新工作簿。若还要创建其他新工作簿，可用以下3种方法创建新工作簿。单击“文件”菜单的“新建”命令，以后用“【文件】新建”表示；直接按快捷键+;鼠标单击常用工具栏上的“新建”按钮，以后用“常用新建”表示。通过新建工作簿的操作，新工作簿的默认名字为Book1、Book2、Book3，。建立、保存、打开和关闭工作簿建立、保存、打开和关闭工作簿 第9页/共208页2.保存工作簿当退出ExcelExcel时，必须保存经过编辑修改的工作簿信息，以防数据丢失。当新工作簿首次保存时，必须给工作簿命名，保存方法如下：单击【文件】保存；单击【文件】另存为；按快捷键+,+,或单击常用保存。注意：工作簿文件默认扩展名为“.xls.xls”，保存时不必键入扩展名。建立、保存、打开和关闭工作簿建立、保存、打开和关闭工作簿-2 第10页/共208页3.打开工作簿打开已存在的工作簿有4 4种方法：单击【文件】打开；或单击常用打开；在“资源管理器”中找到并双击要打开的ExcelExcel文件（扩展名为XLSXLS），系统将启动ExcelExcel，同时打开该文件；选择【文件】菜单，在菜单底部有最近使用过的文件列表，单击要打开的文件；使用快捷键+。建立、保存、打开和关闭工作簿建立、保存、打开和关闭工作簿-3 第11页/共208页4.关闭工作簿 关闭当前工作簿的方法主要有以下三种：单击【文件】关闭；单击工作簿窗口的“关闭”按钮；双击ExcelExcel窗口左上角的“控制菜单”按钮（将关闭所有打开的工作簿文件）。建立、保存、打开和关闭工作簿建立、保存、打开和关闭工作簿-4 第12页/共208页工作表的数据录入工作表的数据录入 在Excel工作簿中，单元格是最小的组成单位。在单元格内可以存放多达32000个字符，允许在单元格中输入中、西文文本、数字或公式。在工作表中输入数据有两种类别：常量和公式。l l常量常量又包括两种类型：数值型和字符型，可以将它直接键入到单元格中（即不以等号“=”开头）。数值型包括日期、时间、货币、百分比、分数、科学记数等，字符型包括有以字符或符号开头的字符串。常量是不可变的，除非人为地改变其值。第13页/共208页工作表的数据录入工作表的数据录入-2-2 l l公式公式是单元格内的一系列数值、单元格引用、名称、函数和运算符的集合，可以通过现有数据计算出新的数值。公式总是以等号“=”开头。当工作表中公式所涉及到的单元格的值发生改变时，由公式结果生成的值也随之改变。第14页/共208页工作表的数据录入工作表的数据录入-3-31.1.输入文本（自动左对齐）输入步骤如下：选定要输入文本的单元格（单击目标单元格）；键入文本；按下EnterEnter键或TabTab键或上、下、左、右的光标键或在其它单元格单击鼠标，都存储当前单元格中输入的数据。若在未存储之前，取消输入的数据，按EscEsc键。注意：“”和“”以及“显示截断”等问题。第15页/共208页工作表的数据录入工作表的数据录入-4-42.2.输入数值（自动右对齐）数值型只能包含以下字符：0909数字 .,$￥+-（）/%E E e e。输入步骤同上，只是输入的内容为数值。注意：l l当要输入并显示多于1111位的数字时，系统自动以科学记数法（如7.89E+087.89E+08）表示，而编辑栏中仍将显示输入的内容；l l无论显示的数字的位数如何，ExcelExcel单元格中只保留1515位数字精度。若超过1515位的数字，则多余数字位将转换为0 0（低位）。l l带分数应使用整数加空格加分数的形式，如：3 3 1/21/2。要避免把输入的数字当作日期。如：1/21/2要写成：0 1/20 1/2。l l前置正号“+”被忽略；负数表示在为数值前使用“-”号或将数值括入（）中。（如：-15-15或（1515）均表示负数）。第16页/共208页工作表的数据录入工作表的数据录入-5-53.3.输入日期或时间（自动右对齐）输入日期和时间时，必须严格按照规则，否则将被当做字符串来处理。1 1）输入日期输入日期用户可以用如下形式（不是全部）输入日期（以19961996年1010月1 1日为例）：1996/10/11996/10/1，1996-10-11996-10-1，01-OCT-96,1/OCT/9601-OCT-96,1/OCT/962 2）输入时间输入时间用户可以用如下形式输入时间（以1919点1515分为例）：1919：1515，7 7：15PM15PM，1919时1515分，下午7 7时1515分PMPM或P P表示下午，AMAM或A A表示上午。3 3）日期和时间的组合输入日期和时间的组合输入如：1996-10-01 191996-10-01 19：15 15，9696年1010月1 1日1919时1515分等。第17页/共208页工作表的数据录入工作表的数据录入-6-63.3.输入日期或时间（自动右对齐）注意：l l日期在ExcelExcel系统内是用19001900年1 1月1 1日起至该日期的天数存储的。如1900/02/011900/02/01在系统内部存储的是3232（天），当我们把该单元格改为数值格式时就可以看到；当年份以2 2位数输入时，若年份在0000至2929之间，ExcelExcel将其作为20002000至20292029年处理。否则（即3030至9999），将其作为19301930至19991999年处理。若某单元格首次输入的日期，则该单元格就格式化为日期格式，以后再输入数值仍然换算成日期，除非人工改变该单元格的格式为数值格式。第18页/共208页工作表的数据录入工作表的数据录入-7-74.4.智能填充数据 当相邻单元格中输入相同数据或按某种规律变化的数据时，可用ExcelExcel的智能填充功能实现快速输入。在当前单元格的右下角有一小黑块，称为填充柄，见图5-15-1 。1)1)填充相同数据填充相同数据在当前单元格A1A1中输入“顺德大学”，鼠标指针移到填充柄，此时，指针呈“+”状，拖动它向右直到E1E1，松开鼠标键。从B1B1直到E1E1均填充了“顺德大学”（如图5-25-2）。第19页/共208页工作表的数据录入工作表的数据录入-8-82)2)填充已定义序列填充已定义序列在单元格A3A3输入“一月”，拖动填充柄向右直到单元格E3E3，松开鼠标键，则从A3A3起，依次为“一月”，“二月”“五月”（如图5-25-2）。ExcelExcel中已定义的序列可以通过【工具】菜单的“选项”命令下的“自定义序列”标签查到。它们是：Sun,Mon,Tue,Wed,Thu,Fri,Sat Sun,Mon,Tue,Wed,Thu,Fri,Sat Sunday,Monday,Tuesday,Wednesday,Sunday,Monday,Tuesday,Wednesday,Saturday,SaturdayJan,Feb,Mar,Apr,Jan,Feb,Mar,Apr,Dec,Dec January,February,March,April,January,February,March,April,December,December日，一，二，三，,六 星期日，星期一，星期二，,星期六一月，二月，,十二月 正月，二月，三月，,十一月，腊月甲，乙，丙，丁，,癸 子，丑，寅，卯，辰，,亥第一季，第二季，第三季，第四季 。第20页/共208页工作表的数据录入工作表的数据录入-9-93)3)建立等差序列的数据除了利用已定义的序列进行自动填充外，还可以指定某种规律（等差、等比序列等）进行智能填充。下面介绍两种常用的等差序列填充法：方法一，菜单法：（如：在A5A5到E5E5建立等差序列3 3，6 6，9 9，1212，1515）在A5A5单元格输入等差序列初值3 3；选择填充区域（包括初始单元格）；单击【编辑】填充序列，出现如图5-35-3所示的对话框；输入步长值为3 3和终止值为1515（实际上执行了步骤就可不输入终止值，输入了终止值就可不执行步骤），单击“确定”按钮。填充结果如图5-25-2所示。等比序列的填充方法和步骤与此基本相同 。第21页/共208页工作表的数据录入工作表的数据录入-10-10方法二，拖动法：l l按左键拖动（如：也是在A5A5到E5E5建立等差序列3 3，6 6，9 9，1212，1515）在第一个单元格（A5A5）输入初值3 3，在第二个单元格（B5B5）输入第二个值6 6；同时选中这两个单元格（方法参见多个单元格的选定）；按鼠标左键，拖动填充柄到终值单元格（E5E5），执行结果同方法一。l l按右键拖动（如：在A5A5到E5E5建立等差序列3 3，4 4，5 5，6 6，7 7）若等差序列的公差为1 1时，也可以只输入第一个数（在A5A5单元格输入3 3），再按鼠标右键拖动直至最后的单元格（E5E5），当松开鼠标时，在弹出的快捷菜单中选择“以序列方式填充”。结果在A5A5到E5E5单元格的数值分别为3 3，4 4，5 5，6 6，7 7。第22页/共208页工作表的数据录入工作表的数据录入-11-114)4)自定义序列 在实际工作中，往往有些常用的序列文字，可用自定义的方法加入到新序列中。操作方法如下：（如：自定义“第一车间，第二车间，第三车间，第四车间”序列）单击【工具】选项，在弹出的对话框中选择“自定义序列”标签，再在“自定义序列”栏目中选“新序列”；在“输入序列”栏目中输入要自定义序列的内容，按键分隔列表条目。类似于图5-45-4所示的“输入序列”栏中的内容；单击“添加”按钮，再单击“确定”按钮。注：若数据已在工作表中，则可用“导入”方法将现有数据加载入自定义序列中。第23页/共208页工作表的数据录入工作表的数据录入-12-12【例5 51 1】假定工作表中已有“第一车间”，“第五车间”，用“导入”方法加载到自定义序列中。操作方法如下：选定A1A1：A5A5；单击【工具】选项，在弹出的对话框中选择“自定义序列”标签；单击“导入”按钮后（见图5-45-4），再单击“确定”按钮即可 。(演示）第24页/共208页处理工作簿中的工作表处理工作簿中的工作表 新建立的工作簿默认有3个工作表。可以选择对某个工作表进行操作，还可以对工作表进行重命名、复制、移动、隐藏和分割等操作。第25页/共208页处理工作簿中的工作表处理工作簿中的工作表-2-2 1.选定工作表选定工作表在编辑工作表之前，必须先选定它，使之成为当前工作表（就象操作单元格一样）。选定工作表的方法是：单击要选择的工作表标签，则该工作表成为当前工作表，其名字以白底显示，且有下划线。若目标工作表标签未显示在工作表标签行，可通过单击工作表标签滚动按钮，使目标工作表标签出现并单击它。有时需要同时对多个工作表进行操作，如：输入几个工作表共同的标题，删除多个工作表等。第26页/共208页处理工作簿中的工作表处理工作簿中的工作表-3-3 选定多个工作表的方法如下：1)选定多个相邻的工作表选定多个相邻的工作表单击要选定的多个工作表中的第一个工作表，然后按住Shift键并单击要选定的最后一个工作表标签。此时这几个工作表均以白底显示，工作簿标题出现“工作组”字样。2)选定多个不相邻的工作表选定多个不相邻的工作表按住键并单击每一个要选定的工作表。1.1.2.工作表重命名工作表重命名双击要改名的工作表标签，使其反白显示，直接输入新名，然后按即可。第27页/共208页处理工作簿中的工作表处理工作簿中的工作表-4-4 3.工作表的移动和复制工作表的移动和复制在实际工作中，有时会遇到两张很相似的表格。若已经制作好其中一张表格，另一张表格可用“复制表格，适当修改个别不同点”的方法来完成，以提高工作效率。工作表在工作簿中的次序可能需要调整，有的工作表可能要归类到另一工作簿。以上这些是造成要对工作表进行移动和复制的主要原因。对工作表的移动和复制，只需对这个工作表标签进行操作。第28页/共208页处理工作簿中的工作表处理工作簿中的工作表-5-5 1)同一工作簿内的移动（或复制）工作表同一工作簿内的移动（或复制）工作表单击要移动（或复制）的工作表标签，沿着标签行拖动（或按住Ctrl键拖动）工作表标签到目标位置。2)在不同工作簿之间的移动（或复制）工作表在不同工作簿之间的移动（或复制）工作表不同工作簿之间工作表的移动和复制，在多窗口环境下进行比较方便。操作步骤是：分别打开要互相操作的多个工作簿；选择【窗口】重排窗口垂直并排；按住源工作簿的工作表标签，拖动到目标工作簿中工作表标签上所需位置（若要复制，则按住Ctrl键再拖）。第29页/共208页处理工作簿中的工作表处理工作簿中的工作表-6-64.插入工作表插入工作表单击某工作表标签（如Sheet3），新工作表将插在该工作表之前。单击【插入】工作表。新插入的工作表Sheet4出现在Sheet3之前，且为当前工作表。5.删除工作表删除工作表单击要删除的工作表标签，使之成为当前工作表；单击【编辑】删除工作表；在出现的对话框中单击“确定”按钮。第30页/共208页处理工作簿中的工作表处理工作簿中的工作表-7-7分割（拆分）屏幕使之成为多个窗口，目的是使同一工作表中相距较（甚）远的数据能同时显示在同一屏幕上。分割有菜单法和鼠标拖动法两种。我们这里介绍拖动法。拖动“水平分割条”可将屏幕分成上下两个部分，拖动“垂直分割条”可将屏幕分为左右两个部分。水平、垂直同时都分割，最多可以分割成四个窗口。取消拆分可通过鼠标双击拆分线来完成。第31页/共208页处理工作簿中的工作表处理工作簿中的工作表-8-8当一个工作表数据超过一屏幕时，滚动会使表头移出当前屏幕，而不利于观看。有时，我们希望数据上下移动时，表头不动，左右移动时，姓名列不动，可通过“冻结窗格”来实现，操作步骤如下：先实现窗口拆分（水平拆分表头，或（和）垂直拆分如姓名列）；再选择【窗口】冻结窗格；若要取消冻结，可选择【窗口】撤消窗口结。第32页/共208页“先选定，后操作”是Windows对所有对象操作的重要特点，Excel也是如此。对工作表中单元格进行操作之前，必须选定需要对其操作的单元格或区域。根据不同的需求，可能有以下几种。编辑工作表编辑工作表 第33页/共208页1.选定单元格选定单元格1)单个单元格的选定单个单元格的选定用鼠标单击所要选定的单元格即可。被选定的单元格也叫“活动单元格”，高亮显示。下面介绍两种选定的方法：鼠标法：按下鼠标左键沿着所要选定区域的对角线方向拖动即可。如：要选定B2:d9，可从B2向右下拖至D9，也可从D9B2，或B9D2，或D2B9。编辑工作表编辑工作表-2第34页/共208页用定位法选定（当选择区域较大时，可用此方法解决）：选择【编辑】定位，出现“定位”对话框，在“引用位置”栏中键入完整的单元格引用。如键入“A10:M20,B25:K30”，再单击“确定”按钮，此时，选定了两个指定的区域。如图5-5所示。结果演示。编辑工作表编辑工作表-3第35页/共208页2)不相邻的单元格区域的选定不相邻的单元格区域的选定选定第一个区域，然后按住键不放，再选定第二、第三个区域。3)整列或整行的选定整列或整行的选定一行或一列的选定：单击行号或列标。连续行、列的选定：从起始行（列）拖至终止行（列）即可。不连续行（列）的选定：先选定一行（列），按住 键，再选择其他行（列）。编辑工作表编辑工作表-4第36页/共208页4)全部单元格的选定全部单元格的选定单击“全选框”（位于工作表左上角行号和列标相交处的矩形）或按+键。5)取消单元格区域的选定取消单元格区域的选定单击任何单元格即可释放所有选定区域。编辑工作表编辑工作表-5第37页/共208页1.编辑单元格数据编辑单元格数据若在原数据上略加修改且数据较长时，可通过双击单元格来进行修改，此时状态栏显示为“编辑”。否则，单击要修改的单元格，重新录入新数据，状态栏显示为“就绪”。2.移动与复制单元格数据移动与复制单元格数据若数据输错了位置，可以将它移到正确的单元格中，不必重新输入；某些单元格具有相同的数据，可以采用复制的方法，避免重复输入，提高效率。编辑工作表编辑工作表-6第38页/共208页1)拖动法拖动法选定要移动（复制）数据的单元格区域；鼠标指针移到所选区域的边框线上，指针由空心十字变为指向左上方的空心箭头时，拖动（或按住键拖动）到目标位置即可。2)剪切剪切-粘贴法粘贴法选定要移动（复制）数据的单元格区域；单击常用剪切（或“复制”）。单击目标区域左上角的单元格并单击常用粘贴。编辑工作表编辑工作表-7第39页/共208页注意：关于“复制”和“剪切”，Excel和Word略有不同的是：l l从源区域“剪切”下来，其内容并不立即消失，直到“粘贴”到目标区域，源区域的内容才消失；l l“剪切”下来的内容只能“粘贴”一次；而“复制”的内容可“粘贴”多次；Excel无论是“复制”还是“剪切”到剪贴板的内容，按键就可清除（源区域四周活动的小黑点消失），而Word不会清除。编辑工作表编辑工作表-8第40页/共208页4.选择性粘贴数据的值、格式和公式等单项的复制在【编辑】菜单中选择“选择性粘贴”，即可弹出 图5-65-6所示的对话框，其中各单项的选择皆为单选按钮，选择其中一种即可。若要复制两个以上的单项，必须分别（用多次操作）完成。“选择性粘贴”还可以用来完成一些特殊的功能，如“转置”复制，数值的对应加、减、乘、除运算（两个矩阵运算）等。有例，演示 编辑工作表编辑工作表-9第41页/共208页5.清除单元格数据 数据的清除包括四个方面：数据的格式、批注、内容及全部。我们可以只清除其中一个方面，如只清除数据的内容，这时格式仍然保留。批注就是单元格的说明、备注。【例5 52 2】清除单元格B3B3，B4B4，B5B5的全部内容。操作步骤如下：选定区域B3:B5;B3:B5;选择【编辑】清除全部。编辑工作表编辑工作表-10第42页/共208页说明：在“清除”子菜单中：“全部”：清除所有信息；“格式”：只清除格式，内容和批注不变“内容”：只清除内容，格式和批注不变；“批注”：只清除批注（在单元格的右上角有红色小三角的表示有批注）。若只需要清除数据的内容，保留数据的格式、批注等，还可以用以下简单的方法。方法一：选定B3:B5，按键。方法二：选定B3:B5，指针放在单元格区域内并用快捷菜单中的“清除内容”项。编辑工作表编辑工作表-11第43页/共208页6.行、列及单元格的插入和删除 1)1)插入插入（1）整行、整列的插入 操作较简单，只要单击行号（或列标）再选择【插入】菜单的“行”（或“列”）命令，即可在选中的行的上边（或列的左边）插入一行（或一列）。如果要插入多行（或多列），则只需在要插入的行号处（或列标处）拖动鼠标选中要插入的数行（或数列），再选择【插入】菜单的“行”（或“列”）命令即可。编辑工作表编辑工作表-12第44页/共208页（2）单元格的插入选中要插入的单元格或区域；选择【插入】单元格，弹出“插入”对话框（如图5-7所示）；在提供的四种选择中选择所需的一种，再单击“确定”按钮。由图5-75-7可看出，这种方法也可用于整行、整列的插入，不过操作比较麻烦，因此，一般整行（或列）的操作都采用前面（1 1）中所介绍的方法。编辑工作表编辑工作表-13第45页/共208页（3）插入批注选择【插入】菜单的“批注”命令，就会出现一个文本框，直接输入内容即可。此时，在当前单元格的右上角有一个红色三角形，当鼠标经过它时，批注会自动显示。编辑工作表编辑工作表-14第46页/共208页2)删除删除我们必须明确“删除”和“清除”是两个不同的概念。清除：将单元格或区域的内容清除，使之成为空白区域。删除：将单元格或区域的整个内容（包括内容和单元格本身）去掉，然后它的右邻（或下邻）单元格产生移动来顶替当前空间。（1）整行、整列的删除选中要删除的整行（或整列），再用【编辑】菜单（或快捷菜单）中的“删除”命令。编辑工作表编辑工作表-15第47页/共208页（3）单元格或区域的删除选中要删除的单元格或区域；用【编辑】（或快捷菜单）删除，出现如图5-8所示对话框；根据需要选择其中一种方式，再按“确定”按钮。这种方法也可以用来删除单元格或区域所在的整行或整列。（4）撤消删除在执行了删除操作后，如果当时发现是误操作，可立即用【编辑】菜单中的“撤消删除”命令，或用常用撤消，也可用+来恢复原内容。编辑工作表编辑工作表-16第48页/共208页7.查找和替换 1)1)1)查找查找查找是一种“条件定位”，即根据给定的某一条件，快速寻找满足条件的单元格。方法：选择【编辑】查找，或按+键，或按+键，屏幕弹出如图5-95-9所示的“查找”对话框。编辑工作表编辑工作表-17第49页/共208页其中:“查找内容”：输入要查找的内容；“搜索方式”：提供按行、列两种选择；“搜索范围”：在下拉列表中提供以下三种选项供选择：l l公式：用来查找公式、数值和文字；l l值：只查找数值和文字；l l批注：查找带批注的单元格。单元格匹配：若选择此项，搜索内容必须与单元格内容完全相同。否则，部分内容相匹配的也会被选中。编辑工作表编辑工作表-18第50页/共208页2)替换替换替换是查找的进一步操作，通过替换操作，可以成批地、高效地用一个数据代替另一个（或多个）数据。选择【编辑】替换，出现“替换”对话框，如图5-10所示。其中各个按钮的意义为：“查找下一个”：不替换；“替换”：只替换当前一个；“全部替换”：全部自动替换。说明：在“查找”对话框中也有“替换”按钮，单击这个按钮，效果一样。编辑工作表编辑工作表-19第51页/共208页5.3 公式与函数的使用公式与函数的使用 输入公式复制公式自动求和按钮的使用函数第52页/共208页5.3 公式与函数的使用公式与函数的使用-2到目前为止，工作表中的数据均以原来的面目出现，与普通表格相比，看不出优越性何在。在实际工作中，除了在表格中输入原始数据外，还要进行统计计算（如：小计、合计、平均等），并把计算结果也反映在表格中。Excel提供各种统计计算功能，可以根据系统提供的运算符和函数构造公式，系统将按公式自动计算。特别是当有关数据修改后，Excel会自动重新计算。这就显示了Excel的优越性。第53页/共208页输入公式输入公式Excel允许使用算术运算、关系运算和字符运算。将数字、文字和函数通过运算符连接起来就成为公式。公式中不能包括空格，当然对象（如字符串）中可以包含空格。1）运算符运算符 算 术 运 算 符 如：B32B32、B4*5B4*5、A1/4A1/4、A1+B3A1+B3、B3-B4B3-B4、(A1+B3+B4)/3(A1+B3+B4)/3 字符运算符如：A1&B2 A1&B2(&:(&:连接运算符，如：”顺德”&”大学”=”顺德 大学”)关系运算符如：E1=5E1=5、C290C260C360、C4=70C4=80C5=80、E24E24引用运算符如：A1:F4A1:F4、$A$1$A$1、$A1$A1、A$1A$1、E:EE:E、2:42:4或A1:C5,E3:G5A1:C5,E3:G5第54页/共208页输入公式输入公式-2-2表5-1 5-1 常见运算符第55页/共208页输入公式输入公式-3-3 2 2）公式输入公式输入 选定输入公式的单元格；键入等号“=”以激活编辑栏。或直接单击编辑栏中的编辑公式按钮 ，键入公式，之后按键完成输入。公式的修改也是通过编辑栏来完成。图5-115-11为输入公式的一个例子（D1D1单元格的值为“A1+B1+C1A1+B1+C1”）。第56页/共208页输入公式输入公式-4-4注意：lExcel的所有公式都以“=”号开头，输入公式后按键确认。在单元格输入公式后，单元格会显示公式运算的结果，而在编辑栏内显示该公式本身。l所有公式中输入的符号一律使用西文标点符号。在单元格输入数据或等号后，编辑栏左边出现。如果单击或按键为放弃输入，单击为确认输入项。第57页/共208页输入公式输入公式-5-53）公式返回的错误值和产生的原因公式返回的错误值和产生的原因表表5-2公式返回的错误值及产生的原因（参考）公式返回的错误值及产生的原因（参考）第58页/共208页输入公式输入公式-6-64）若要同时在几个单元格中输入相同内容，可按下面步骤进行若要同时在几个单元格中输入相同内容，可按下面步骤进行 选定要输入数据的单元格（单元格可以相邻，也可以不相邻）；输入数据，之后按下+键，数据将输入到所有选定单元格中。第59页/共208页复制公式复制公式由图5-12我们可以看出，各个学生的总分都是由语文、数学和英语三门得分相加得到。若不用复制，我们要在E2单元格输入公式“=B2+C2+D2”，在E3输入“=B3+C3+D3”，在E4输入“=B4+C4+D4”，这样做十分麻烦，象这样形式类似，其中的单元格有规律变化的公式不必重复输入，而采用复制公式的方法，其中的单元格地址的变化由系统去推算。复制公式类似复制单元格，同行或同列一般采用自动填充法复制。方法如下：先在E2单元格输入公式“=B2+C2+D2”，拖动填充（复制）柄向下直到目标地址（如E7）即可。演示文件演示文件第60页/共208页复制公式复制公式-2-21.相对地址引用相对地址引用在公式中指定与包含该公式所在单元格具有相对位置的单元格地址。引用形式为B2B2，C2C2，D2D2等，例如：=B2+C2+D2=B2+C2+D2；在复制这个公式时，包含新的公式单元格和被引用的单元格始终保持这种相对位置。从图5-125-12中可理解，第二行的总分单元格由第二行的三个成绩相加，第三行的总分单元格由第三行的三个成绩相加同样，第七行的总分单元格由第七行的三个成绩相加。图5-125-12中的公式复制就是相对地址引用的一个典型例子。演示文件演示文件第61页/共208页复制公式复制公式-3-32.绝对地址引用绝对地址引用在公式中引用单元格是固定不变的，而不考虑包含该公式的单元格位置。绝对引用形式为$A$1,$B$1$A$1,$B$1等，即行号和列标前都有“$”符号，例如：=$A$1+$B$1=$A$1+$B$1；采用绝对引用的公式，该公式无论复制到任何位置，都将引用同一单元格。第62页/共208页复制公式复制公式-4-4【例5 53 3】图5-135-13中给出洗衣机在第1 1、2 2、3 3、4 4季度的销售量，要求算出年度总销售量和各季度占年度总销售量的比例。操作步骤如下：在F2F2单元格算出年度总销售：=B2+C2+D2+E2=B2+C2+D2+E2（这里是相对地址引用，也可用绝对地址，因为我们不需要复制此公式到其它单元格）；在B3B3单元格引入公式：=B2/$F$2,=B2/$F$2,然后用填充法从B3B3复制公式到C3,D3C3,D3和E3E3。在这些单元格中分别得到以下公式：（=C2/$F$2,=C2/$F$2,=D2/$F$2=D2/$F$2和=E2/$F$2=E2/$F$2）。从这些单元格的公式中我们可看出：分子是相对地址引用，是变化的；而分母是绝对地址引用，是固定不变的。我们要理解相对地址引用和绝对地址引用各自的用途，以免出现差错。演示文件第63页/共208页复制公式复制公式-5-5 注意：若我们在B3B3单元格中引入公式为“=B2/F2=B2/F2”,此单元格的计算结果不会出现问题，而当我们用填充法从B3B3复制公式到C3,D3C3,D3和E3E3。在这些单元格中分别得到以下公式：（=C2/G2,=C2/G2,=D2/H2=D2/H2和=E2/I2=E2/I2）,就会出现错误的结果。第64页/共208页复制公式复制公式-6-6 3.3.混合地址引用混合地址引用 混合地址引用形式为：$A1($A1(列绝对，行相对)和A$1(A$1(列相对，行绝对)。单元格引用$A1$A1总是参照第A A列，左右移动时列号不变，而行地址1 1引用时，若有上下移动，行号自动作相应的调整。注意：若F4F4中的公式为“=$C$4+D$4+$E4=$C$4+D$4+$E4”，复制公式到G5G5，则G5G5中公式为“=$C$4+E$4+$E5=$C$4+E$4+$E5”，我们大家一定要深刻理解怎么会这样，这对理解单元格地址引用将有很大的帮助。第65页/共208页复制公式复制公式-7-7 4.4.跨工作表的单元格地址引用跨工作表的单元格地址引用公式中可能用到同一工作簿的另一工作表的单元格中的数据，如，F4F4中的公式为：=（C4+D4+E4C4+D4+E4）*Sheet2!B1Sheet2!B1其中，“Sheet2!B1Sheet2!B1”表示工作表Sheet2Sheet2中的B1B1单元格地址。这个公式表示计算当前工作表中的C4C4、D4D4和E4E4单元格数据之和与Sheet2Sheet2中的B1B1单元格数据的相乘，结果存放在当前工作表中的F4F4单元格。第66页/共208页复制公式复制公式-8-8地址引用的一般形式为：工作表名！单元格地址，当前工作表的单元格的地址可以省略“工作表名！”，即用二维地址。以上的地址形式为三维地址格式。注意：若要跨工作簿引用单元格地址，则要用外部引用（四维地址）。引用的格式为：=工作簿名工作表名！单元格地址第67页/共208页自动求和按钮的使用自动求和按钮的使用 【例5 54 4】图5-145-14所示，要求用“自动求和”按钮，求出A1:B2,D1:D2,C4:C5A1:B2,D1:D2,C4:C5三个单元格区域的和，并把结果存放在A7A7单元格内。操作步骤如下：单击目标单元格A7A7；单击常用自动求和；用鼠标拖动法选定三个区域数据源（不连续区域选定用键），选定后的显示结果如图5-14所示。在编辑栏中自动生成公式“=SUM(A1:B2,C4:C5,D1:D2)”，其中SUM为求和函数。按键确认，在A7单元格中显示求和结果为36。第68页/共208页自动求和按钮的使用自动求和按钮的使用-2-2 当我们选定包含二个及以上数值的单元格区域时，在Excel窗口下方的“状态”栏的中央会显示所选区域的统计值情况（如：求和=1000），鼠标右键单击此位置，可弹出快捷菜单，我们可选择显示：求和、均值、最大值、最小值、计数或计数值。我们也可以选择“无”选项，不显示。第69页/共208页函数函数 在Excel中，函数就是预定义的内置公式，它使用参数并按照特定的顺序进行计算，我们只需简单地操作就可以实现比较抽象或比较复杂的运算。函数按特定的指令对参数进行计算，把计算的结果返回给用户。函数的参数可以是数字、文本、逻辑值或单元格的引用，也可以是常量公式或其他函数。1.函数的功能分类函数的功能分类Excel提供了10类函数，每一类有若干个不同的函数，一共有几百个之多，可以满足各种领域的计算要求。表5-3给出了这些函数按照功能的分类。第70页/共208页函数函数-2-2 表5-3函数功能分类第71页/共208页函数函数-3-32.函数的形式函数的形式函数的形式：函数名（参数1，参数2，）其中为可选项,函数名后紧跟括号，可以有一个或多个参数，参数间用逗号分隔。函数也可以没有参数，但函数名后的括号是必需的。如：AVERAGE（26，C2，A1:C1）有3个参数，表示求26、C2中的数据、A1:C1区域中的数据（3个参数共5个数据）的平均值。PI（）返回的值（3.141592654），无参数。第72页/共208页函数函数-4-43.常用函数常用函数在提供的众多函数中有些是经常使用的，下面介绍几个常用函数：1)SUM（A1，A2，）功能：求各参数的和。A1，A2等参数可以是数值或含有数值的单元格引用。至多包含30个参数。2)AVERAGE（A1，A2，）功能：求各参数的平均值。A1，A2等参数可以是数值或含有数值的单元格引用。3)MAX（A1，A2，）功能：求各参数中的最大值。4)MIN（A1，A2，）功能：求各参数中的最小值。第73页/共208页函数函数-5-55)COUNT（A1，A2，）功能：求各参数中数值型参数和包含数值的单元格个数。参数的类型不限。如：“=COUNT（12，D1:D3,”CHINA”）”，若D1:D3中存放的全是数值，则函数的结果是4。若D1:D3中只有一个单元格存放的是数值，则结果为2。6)ROUND（A1，A2）功能：对数值项A1进行四舍五入。若：A20，表示保留A2位小数；A2=0，表示保留整数；A25,10,-10）结果取10；IF（56,10,-10）结果取-10。IF函数可以嵌套使用，最多可嵌套7层。如E2中存放某学生的某科考试成绩，则其成绩的等级（A、B、C、D、F）可表示为：IF(E289,”A”,IF(E279,”B”,IF(E269,”C”,IF(E259,”D”,”F”)10)SQRT（A1）功能：求A1的平方根值。如：SQRT（16）=4。11)PRO