计算机文化基础-2014版课件第4章excel

第4章电子表格处理软件Excel2010广东工业大学计算机学院物理团学学风建设部本章内容4.1Excel2010概述4.2Excel2010的文件及窗口的操作4.3工作表的管理操作4.4工作表中数据的输入及编辑4.5工作表的格式化4.6公式及函数4.7图表4.8数据分析和管理4.9页面布局及打印物理团学学风建设部4.1Excel2010概述4.1.1Excel2010功能介绍4.1.2Excel2010工作环境4.1.3Excel基本知识物理团学学风建设部4.1.1Excel2010功能介绍Excel的主要功能制作各种样式电子表格使用公式和函数制作各种统计图表数据分析和管理物理团学学风建设部4.1.2Excel2010工作环境编辑栏行号列号拆分条工作表工作区工作表标签

全选按钮工作表标签滚动按钮

当前单元格

页面布局切换按钮显示比例状态栏注意：Excel的工作界面和Word的工作界面有何相似、有何不同？物理团学学风建设部4.1.3Excel基本知识工作簿、工作表、单元格的基本概念一个工作簿就是一个Excel文件一个工作簿中可包含若干工作表，每个工作表都有名称，如Sheet1一个工作表中含有若干个单元格，每个单元格都有编号，如B5当前正在操作的单元格称为当前单元格若干个连续的单元格构成单元格区域，区域的表示例如A3:D7物理团学学风建设部工作簿一个工作簿就是一个Excel文件，工作簿名就是文件名，扩展名为“.xlsx”。对于新建的文件，Excel会自动给文件取名为“工作薄1”、“工作薄2”，……，用户可在保存文件时更改文件名。一个工作簿中包含者若干个工作表。物理团学学风建设部工作表一个工作簿中可以包含若干个工作表。默认情况下，Excel新建的工作簿中包含3个名称分别为Sheet1、Sheet2、Sheet3的工作表。当前工作表为Sheet1。物理团学学风建设部改变新工作簿中所包含的工作表的数量的操作最大可设为255物理团学学风建设部单元格单元格是组成工作表的最小单位。一张Excel工作表16384（列）×1048576（行）组成，每一行列交叉处即为一单元格。列号用英文字母A、B、…表示；行号用数字1、2、3、…表示。如B5表示第2列的第5行单元格。活动单元格（当前单元格）：正在操作的单元格。活动单元格列号行号物理团学学风建设部单元格区域在Excel中，区域是连续的单元格，一般习惯上用“单元格左上角:单元格右下角”表示。如“A3:D7”表示左上起于A3右下止于D7的20个单元格。还可用“D7:A3”、“A7:D3”、“D3:A7”表示该区域。A3:D7D7:A3A7:D3D3:A7物理团学学风建设部4.2Excel文件及窗口的操作4.2.1新建Excel文件4.2.2Excel文件的打开、保存与关闭4.2.3保护Excel工作簿4.2.4窗口的切换及排列窗口4.2.5新建窗口4.2.6工作表窗口的拆分4.2.7冻结窗格物理团学学风建设部4.2.2Excel文件的打开、保存与关闭打开操作：“文件”→“打开”命令保存操作：（1）单击“文件”→“保存”命令，可使文件按原名保存（2）单击“文件”→“另存为”命令，可改变文件的保存位置及文件名关闭操作：单击“文件”→“关闭”命令物理团学学风建设部4.2.3保护Excel工作簿为工作簿文件加密操作：“文件”→“信息”→“保护工作簿”→“用密码进行加密”设置密码后，以后再打开文件时必须正确输入密码，否则文件无法打开。取消密码操作：需重复上述操作，在“加密文档”对话框中，将密码删除。物理团学学风建设部4.2.3保护Excel工作簿保护工作簿操作：“文件”→“信息”→“保护工作簿”→“保护工作簿结构”→勾选保护对象→设置密码。保护结构：工作簿的结构被锁定，用户无法对工作簿进行插入、删除、移动、重命名工作表等操作保护窗口：用户无法放大缩小Excel窗口中的工作簿窗口。取消保护：重复上述操作。物理团学学风建设部4.2.4窗口的切换及排列窗口文件窗口的切换排列窗口物理团学学风建设部4.2.5新建窗口同一文件，两个窗口物理团学学风建设部4.2.6工作表窗口的拆分窗口拆分条物理团学学风建设部4.2.7冻结窗格冻结窗格物理团学学风建设部4.3工作表的管理操作4.3.1选取工作簿中的工作表4.3.2插入工作表4.3.3删除工作表4.3.4移动、复制工作表4.3.5重命名工作表4.3.6隐藏工作表及取消隐藏4.3.7保护工作表物理团学学风建设部4.3.1选取工作簿中的工作表（1）选取单个工作表：单击要选取的工作表标签；（2）选取相邻的多个工作表：先单击第一个工作表标签，然后按住Shift键再单击最后一个工作表标签；（3）选取不相邻的工作表：先单击第一个工作表标签，然后按住Ctrl键再依次单击其他工作表的标签；（4）选取所有工作表：在工作表标签处单击鼠标右键“选定全部工作表”物理团学学风建设部4.3.2插入工作表物理团学学风建设部4.3.3删除工作表提示：删除工作表后，不能通过撤销按钮

恢复被删除的工作表，因此执行删除工作表的操作应谨慎。物理团学学风建设部4.3.4移动、复制工作表勾选：复制工作表不选：移动工作表方法一方法二物理团学学风建设部4.3.5重命名工作表物理团学学风建设部4.3.6隐藏工作表及取消隐藏物理团学学风建设部4.3.7保护工作表保护工作表可以防止数据被修改操作：“文件”→“信息”→“保护工作簿”→“保护当前工作表”输入密码，设置保护内容物理团学学风建设部4.4工作表中数据的输入及编辑4.4.1单元格及单元格区域的选定4.4.2数据的输入4.4.3设置输入数据的有效性4.4.4单元格内容的修改及清除4.4.5移动或复制单元格4.4.6插入或删除行、列及单元格4.4.7改变单元格的大小4.4.8单元格的查找和选择4.4.9插入批注物理团学学风建设部4.4.1单元格及单元格区域的选定选择内容操作方法连续区域（1）按住鼠标左键并拖动，所选择的区域会出现一个黑框（2）单击区域的第一个单元格（左上角），然后按住Shift键再单击区域的最后一个单元格（右下角）（3）在名称框中输入区域地址（如A3:C8），然后按Enter键整行单击行号整列单击列号连续的多行（或多列）（1）在行号或列号中拖动鼠标（2）先选定第一行（或第一列），然后按住Shift键再单击最后一行的行号（或最后一列的列号）不相邻的单元格或单元格区域先选定第一个单元格或单元格区域，然后再按住Ctrl键用鼠标依次选定其他的单元格或单元格区域工作表中所有单元格单击行号列号交界处的“全选”按钮（见图4-1）已命名的单元格区域在名称框下拉列表中，选择区域的名称物理团学学风建设部键盘控制活动单元格的移动键名操作效果回车键向下移动。Tab键向右移动。方向键上下左右移动。Home键移动到本行的第一个单元格。Ctrl+Home移动到工作表的第一个单元格。Ctrl+End移动到工作表数据区的最后一个单元格，这个单元格位于数据区的最右列的最下行中。Ctrl+箭头键移动到当前数据区域的边缘。PageDown向下移动一屏。PageUp向上移动一屏。Alt+PageDown向右移动一屏。Alt+PageUp向左移动一屏。Ctrl+PageDown移动到工作簿中下一个工作表。Ctrl+PageUp移动到工作簿中前一个工作表。ScrollLock打开或关闭滚动锁定。打开滚动锁定功能后，方向键就不是用来选定相邻的单元格，而是使当前工作表在窗口中整体滚动。物理团学学风建设部给单元格区域命名方法一：选定区域在名称框中输入名称方法二：选定区域“公式”选项卡“定义的名称”组“定义名称”命令物理团学学风建设部4.4.2数据的输入Excel数据类型包括文本、数字及日期时间型，默认情况下，输入后文本数据左对齐，数字和日期时间右对齐除直接输入外，利用Excel填充功能的可以快速输入有规律的数据物理团学学风建设部Excel的数据类型文本型Excel中文本可以是数字、空格和非数字字符的组合。默认情况下，所有文本在单元格中均左对齐。输入的数据超长时，扩展显示到右边列；若右边有数据，截断显示。在所输入的数字前加上一个单引号，则Excel将把它当作文本，在单元格中左对齐。输入时如需要单元格内换行输入，可插入硬回车，方法是按下Alt+Enter键。物理团学学风建设部Excel的数据类型数字型Excel中，数字只可以包含字符：0123456789+-(),/$%.Ee分数的输入应先输入整数及空格。默认状态下，所有数字在单元格中均右对齐。当输入的数字超长时，自动以科学记数法显示。无论显示的数字的位数如何，Excel都只保留15位的数字精度。当单元格的宽度不够时，单元格中将出现“####”符号。物理团学学风建设部Excel的数据类型时间和日期型应按Excel内置的日期时间格式输入，常用格式有：“yy-mm-dd” “yy/mm/dd”

“yyyy年mm月dd日”

“hh:mmam/pm”Excel将日期和时间视为数字处理。默认状态下，日期和时间项在单元格中右对齐。如果输入的年份为2位数，则00-29表示的年份为2000-2029年，30-99表示1930-1999年。如要输入当前日期，可按组合键(Ctrl+；)，当前时间的输入则按(Ctrl+Shift+；)。物理团学学风建设部Excel的数据类型逻辑型两个值：TRUE（表示真）、FALSE（表示假）逻辑型的数据在单元格中居中对齐逻辑型数据一般在逻辑运算后出现错误值错误值含义####表示单元格宽度不够#DIV/0!表示公式中有除数为0的运算#REF！表示公式中有无效的单元格引用#N/A表示有数据缺失#NAME?表示公式中包含无法识别的文本#NUM!表示公式中含有无效的数值#VALUE!表示公式中某个值使用了错误的数据类型物理团学学风建设部自动填充数据使用填充柄：先输入初值，然后选定初值，用鼠标拖动填充柄，产生填充数据。使用填充柄可实现复制操作，或产生数据序列（等差序列、日期序列、文字序列、自定义的序列等）。操作的手法有：直接用鼠标左键拖动填充柄按下Ctrl键后拖动填充柄双击填充柄用鼠标右键拖动填充柄，在弹出的快捷菜单中选择命令使用填充命令：“开始”“编辑”“填充”物理团学学风建设部自动填充数据例利用自动填充功能，可快速完成下列数据的输入物理团学学风建设部4.4.3设置输入数据的有效性输入数据前预先设定有效数据范围，可以避免用户输入错误的数据，提高用户输入数据的准确率。物理团学学风建设部①选定需设置数据有效性的区域②③设置有效性条件④设置输入的提示信息⑤设置出错警告物理团学学风建设部4.4.4单元格内容的修改及清除修改单元格内容常用的方法有方法三：按键盘F2键方法二：双击单元格，在单元格中修改方法一：直接在编辑栏中修改物理团学学风建设部Del键清除单元格的内容清除命令可对清除进行选择物理团学学风建设部4.4.5移动或复制单元格注意：粘贴后原区域的数据被覆盖移动：剪切粘贴复制：剪切粘贴物理团学学风建设部粘贴时，按右键打开快捷菜单，选择“插入剪切/复制的单元格”命令，可用插入的方式粘贴，原目标区域的数据不会被覆盖物理团学学风建设部粘贴时，使用“粘贴”菜单或“选择性粘贴”，可以选择粘贴项目“粘贴”菜单粘贴值粘贴格式转置粘贴物理团学学风建设部4.4.6插入或删除行、列及单元格插入单元格插入对话框插入菜单物理团学学风建设部删除单元格删除对话框删除菜单物理团学学风建设部4.4.7改变单元格的大小Excel通过设置行高列宽改变单元格的大小设置行高设置列宽物理团学学风建设部4.4.8单元格的查找和选择物理团学学风建设部4.4.9插入批注物理团学学风建设部4.5工作表的格式化4.5.1字体格式4.5.2对齐格式4.5.3数字格式4.5.4边框和填充格式4.5.5设置条件格式4.5.6套用表格格式4.5.7格式的复制和清除物理团学学风建设部4.5.1字体格式使用“字体”工具组设置选定单元格的字体格式，也可使用对话框设置物理团学学风建设部4.5.2对齐格式使用“对齐方式”工具组设置选定单元格的对齐方式，也可使用对话框设置物理团学学风建设部4.5.3数字格式使用“数字”工具组设置选定单元格的数字格式，也可使用对话框设置物理团学学风建设部4.5.4边框和填充格式使用“边框”工具和“填充颜色”工具设置选定单元格的边框和填充颜色，也可使用对话框设置物理团学学风建设部物理团学学风建设部4.5.5设置条件格式条件格式用于动态地为选定区域中的各单元格设定格式，使满足不同条件的数据以不同的格式显示。使用条件格式可以突出显示所关注的单元格或单元格区域的异常值，还可以使用数据条、色阶和图标集等直观地显示数据。物理团学学风建设部物理团学学风建设部4.5.6套用表格格式利用套用表格格式，可方便快捷地为所选区域按系统提供的表格样式排版。物理团学学风建设部设置样式样式是各种格式的集合。可直接使用已定义好的各种样式，或自己建立样式。物理团学学风建设部4.5.7格式的复制和清除1. 格式的复制复制格式最简单的方法是使用“格式刷”。此外，在“粘贴”菜单中选择“格式”命令也可实现格式的复制。2. 格式的清除选定单元格，单击“开始”→“编辑”→“清除”→“清除格式”命令。格式清除后单元格中的数据以常规格式来显示。物理团学学风建设部4.6公式及函数4.6.1输入公式4.6.2常量4.6.3运算符4.6.4公式中单元格的引用和公式的复制4.6.5函数的格式及输入4.6.6常用函数的介绍4.6.7函数的嵌套4.6.8自动计算物理团学学风建设部4.6.1输入公式Excel中的公式除了可以进行算术运算、比较运算及文字连接运算，还可以使用Excel提供的各种函数对数据进行处理。公式以“=”开头，由运算符、常量、单元格引用及函数等元素构成。公式的输入步骤为：①选择要输入公式的单元格；②输入“=”及公式内容；③输入完毕按Enter键或单击编辑栏中的“输入”按钮.物理团学学风建设部4.6.2常量常量是指在单元格或公式中，值不会发生变化的量。例如直接在单元格中输入的数据，或是在公式中直接输入的数值。例如“100”、“计算机”物理团学学风建设部4.6.3运算符类型运算符说明优先级示例结果算术运算符-负号2=-12-12%百分比3=50%0.5^乘幂4=2^38*乘5=3*824/除=5/41.25+加6=25+4873-减=38-1325文字连接符&连接两个文本字符串，运算结果为文本型数据7="Micro"&"soft"=13&24Microsoft1324比较运算符=等于8=3=5FALSE>

大于=3>5FALSE<

小于=3<5TRUE>=大于等于=3>=5FALSE<=小于等于=3<=5TRUE<>

不等于=3<>5TRUE引用运算符:区域运算符，连接两个单元格地址表示一个区域1

A3:B5

,联合运算符用于连接多个常量、单元格或单元格区域SUM(3,5,-2)SUM(A3,B3:C4)6

(空格)交集运算符，运算结果为两个引用中的共同区域A3:C8B5:D10B5:C8物理团学学风建设部说明：（1）算术运算符用于算术运算，其结果为数字型数据。（2）比较运算符用于对两个数进行比较，产生的结果为逻辑值TRUE（真）或FALSE（假），默认情况下逻辑值居中对齐。（3）公式中如果有文本数据，应使用用双引号将文本括起来。（4）当公式有多个运算符时，由运算符优先级决定运算顺序，优先级相同时，按从左到右顺序计算。（5）公式中所使用的符号（包括等号、运算符、标点符号等）一律为英文符号。物理团学学风建设部4.6.4公式中单元格的引用和公式的复制在公式中通过使用单元格的地址使得单元格中的数据参与运算的这种方式叫做单元格的引用。公式中单元格的引用分三种方式：（1）相对引用，例如“A1”（2）绝对引用，例如“$A$1”（3）混合引用，例如“A$1”或“$A1”含$符号，公式复制时，行列编号不会变化，不含$符号，公式复制时，行列编号随相对位置变化三种引用方式在输入时可以通过F4功能键进行切换物理团学学风建设部4.6.5函数的格式及输入函数的格式：

函数名称(参数1,参数2,……)函数的输入（1）直接输入法（2）使用“自动求和”工具物理团学学风建设部（3）插入函数法（4）使用函数库物理团学学风建设部4.6.6常用函数的介绍1. 财务函数函数格式函数用途PMT(rate,nper,pv,[fv],[type])计算在固定利率下，贷款的等额分期偿还额FV(rate,nper,pmt,[pv],[type])基于固定利率和等额分期付款方式，返回某项投资的未来值物理团学学风建设部2. 日期与时间函数函数格式函数用途示例结果DATE(year,month,day)返回代表特定日期的序列号=DATE(2014,5,10)41769（常规格式）TODAY()返回日期格式的系统当前日期=TODAY()当前系统日期NOW()返回日期时间格式的系统当前日期和时间=NOW()当前系统日期时间YEAR(serial_number)分别返回某大于等于1的序列数对应的年、月、日、时、分、秒=YEAR(12345.678)1933MONTH(serial_number)=MONTH(12345.678)10DAY(serial_number)=DAY(12345.678)18HOUR(serial_number)=HOUR(12345.678)16MINUTE(serial_number)=MINUTE(12345.678)16SECOND(serial_number)=SECOND(12345.678)19物理团学学风建设部3. 数学与三角函数函数格式函数用途示例结果ABS(number)返回给定数的绝对值=ABS(-3.5)3.5PI()返回圆周率π的值（15位精度）=PI()3.14159265358979SIN(number)返回给定角度的正弦值=SIN(30*PI()/180)0.5COS(number)返回给定角度的余弦值=COS(30*PI()/180)0.866025404TAN(number)返回给定角度的正切值=TAN(30*PI()/180)0.577350269LOG(number,[base])根据给定底数base（缺省值是10)，返回number的对数=LOG(8,2)=LOG(100)32EXP(number)返回e的number次方=EXP(3)20.08553692INT(number)向下取整为最接近的整数=INT(3.5)=INT(-3.5)3-4SQRT(number)返回数值的平方根=SQRT(9)3SUM(number1,[number2],...)计算参数中所有数字之和=SUM(3,5,D3:D5)略SUMIF(range,criteria,[sum_range])对满足条件的单元格求和

ROUND(number,num_digits)按指定的小数位数对数值进行四舍五入=ROUND(76.384,1)76.4物理团学学风建设部4. 统计函数函数格式函数用途AVERAGE(number1,[number2],...)返回其参数的算术平均值AVERAGEIF(range,criteria,[average_range])对满足条件的单元格区域求算术平均值，如缺省average_range，则计算区域是range区域MAX(number1,[number2],...)返回一组数值中的最大值MIN(number1,[number2],...)返回一组数值中的最小值COUNT(value1,[value2],...)计算包含数字的单元格以及参数列表中的数字的个数COUNTA(value1,[value2],...)返回参数列表中非空值的单元格个数COUNTIF(range,criteria)计算区域中满足给定条件的单元格数目物理团学学风建设部5. 逻辑函数函数格式函数用途示例结果TRUE()返回逻辑值TRUE=TRUE()TRUEFALSE()返回逻辑值FALSE=FALSE()FALSEAND(logical1,[logical2],...)当所有参数的逻辑值为TRUE时，返回TRUE，否则返回FALSE=AND(true,true)=AND(3>5,5>3)TRUEFALSEOR(logical1,[logical2],...)任何一个参数为TRUE时，返回TRUE；当所有参数为FALSE时，返回FALSE=OR(false,false)=OR(3>5,5>3)FALSETRUENOT(logical)对参数的逻辑值求反=NOT(true)=NOT(3>5)FALSETRUEIF(logical_test,[value_if_true],[value_if_false])执行真假值判断，根据逻辑计算的真假值，返回不同结果=IF(3>5,10,20)=IF(3<5,10,20)2010物理团学学风建设部4.6.7函数的嵌套使用函数时，函数的参数中又引用了函数，这种结构称为函数的嵌套。例如：=ROUND(AVERAGE(B2:D2),0)=IF(AND(B2>=60,C2>=60,D2>=60),"通过","不通过")=IF(E2>=90,"优秀",IF(E2>=80,"良好",IF(E2>=70,"中等",IF(E2>=60,"及格","不及格"))))提示：（1）函数的嵌套中要注意左右括号的匹配。（2）函数内嵌套函数的返回值，必须符合它所处位置的参数所规定的数据类型。物理团学学风建设部4.6.8自动计算自动计算功能，可以自动计算选定单元格区域的数值总和、平均值、计数、最大值、最小值等，并将结果显示在状态栏中物理团学学风建设部4.7图表4.7.1图表的类型4.7.2图表的创建4.7.3图表的编辑4.7.4迷你图物理团学学风建设部4.7.1图表的类型图表，使数据直观呈现、易于比较物理团学学风建设部4.7.2图表的创建1. 快捷创建图表（1）选定要创建图表的数据区域后，按组合键（Alt+F1），可在当前工作表中嵌入一个图表；（2）选定要创建图表的数据区域后，按F11功能键，创建一个图表工作表。物理团学学风建设部2. 使用图表工具创建物理团学学风建设部3. 使用“创建图表”对话框物理团学学风建设部