(完整版)Excel全套基础课件.ppt

1、Excel Excel 部分知识要点部分知识要点Excel文档的创建与保存Excel的编辑表格的格式化表格中的计算表格中的信息处理图表使用宏表格和表格数据的共享第一部分：第一部分：ExcelExcel文档的创建与文档的创建与保存保存第一部分：文档的创建与保存认识Excel工作簿、工作表、行列的表示及单元格创建工作簿的方法同创建word相同文档的保存在word和Excel另存为对话框的工具下拉框中，选择常规选项可以为文档设置密码，保护文档。一个Excel 文件叫做一个工作簿，后缀名为xlsx，创建方法与word文档相同。第二部分：第二部分：ExcelExcel的编辑的编辑Excel文档包括工作表

2、行与列单元格数据窗口包括工作表的插入删除改名改色复制和移动显示与隐藏工作表编辑工作表的选中是利用工作表标签完成的，ctrl，shift有效，选中多张工作表，可设置共有格式。注意事项：一、插入和删除的数量与选中数量相同二、复制粘贴的工作表名称为原工作表名称后加（n）三、复制和移动可以跨工作表进行，目标工作簿应事先打开移动还是复制移动还是复制设置默认工作表数目文件选项常规。保护工作表取消的位置在此取消的位置在此可以设置工作表不被乱改区域编辑需要区域编辑需要密码密码行列的操作行列的选中：点击行号和列标插入与删除：复制与移动：Ps：不要在粘贴时选择区域选从哪里开始就可以了，行高和列宽的调整，行列的隐藏

3、单元格操作单元格的操作包括选中，鼠标单击（不是双击），划过，Ctrl，Shift有效。此外在名称框内直接输入单元格名称。复制，粘贴时有多个选项，鼠标指向可了解每种粘贴选项。插入、移动、删除，可能破坏结构 清空内容，Delete键有效，不是删除单元格 PS：粘贴时不选区域。数据的编辑单击输入为覆盖式改写，双击为单元格内文本修改，使用编辑栏与双击效果相同。数据的类型：数字，文本，日期和逻辑数字直接录入，超过11位自动显示为科学技术法，但不影响数据的使用，超过15位会丢失精度（含小数），分数输入时要加“0空格”。文本的录入，凡是不能被当作公式和数字的，都被视为文本，比如3+2，X3等，直接输入数据的

4、编辑日期的录入：2/3 2015-11-25 2016-13-12？逻辑型数据的录入，true，false 文本true和false的录入，加英文单引号。文本2015-11-25 的录入，加英文单引号编号的录入，英文单引号+数字，被视为文本编号与数字的区别在于，计算是否有意义公式的录入，以=开头设置数据格式单元格快捷菜单，或者“开始”单元格组的格式下拉框可以打开“设置单元格格式”对话框设置为文本的单元格无论填写什么数据均被视为文本便捷录入-填充填充柄：选中单元格将鼠标放在单元格右下角，出现的黑色+号单一数字或者文本填充：相同内容，Ctrl填充自动加1数字和文本混合填充：文本不变数字部分自动加1

5、，Ctrl填充，内容不变数字型文本填充同文本数字混合。内置序列填充。在文件选项高级中可以设置自定义序列便捷录入-填充两个数字选中的填充：左键等差，右键可选使用开始-编辑-填充命令数据有效性检查控制无效数据，例如年龄150岁，电话号码10位 在数据数据有效性窗口的编辑第三部分：第三部分：ExcelExcel的格式化的格式化 Excel工作表的格式化包括设置字体和对齐方式设置数据格式设置边框和底纹 自动套用格式使用主题条件格式页面设置与打印单元格格式对话框字体和对齐边框和底纹自动套用格式 单元格样式自定义样式自动套用格式 表格样式自定义样式不适合单元格合并的表格使用主题主题是组成画面的各种元素的各

6、种格式的集合修改主题修改主题条件格式不及格蓝色，及格黑色，优秀红色开始样式组符合条件的显示颜色选择最高最低值选择最高最低值色条长短表示数据底纹显示底纹显示用图形表示规则的管理和修改规则的管理和修改规则被修改后格式立刻发生变化，规则被清除后格式立刻被全部取消新建规则及应用Excel的页眉和页脚Excel在此设置页眉和页脚一般在下拉框中选择，也可以自定义工作表的打印区域只打印指定部分跨页打印时的标题跨页打印时的标题表格太小时的重复打印顺序Excel的页面设置页边距、纸张方向和纸张大小参考word缩小打印复杂设置第四部分：表格中的计算第四部分：表格中的计算计算的类型和运算符数字的运算：+，-，*，/

7、，mod（）文本的运算：&文本的连接 日期的运算：+，-计算单位“天”逻辑的计算：not（），and（），or（）实际上是函数Not规则：非真即假，非假即真and规则：两真得一真，否者为假Or 规则：两假得一假，否者为真比较运算规则 比较运算符：,=,=,数值型数据按数值大小比较。字符型数据按照字符的ASCII码值进行比较，中文字符按照拼音(字典)进行比较。逻辑值数据：FALSETRUE。日期型、日期时间型数据：昨天今天明天单元格中公式的输入必须以开头，否则会被视为文本公式中除了运算符外，参与运算的量可以是单元格名称，常数，函数等。公式中的运算对象不要使用单元格中的数据，二是使用单元格引用，即

8、单元格名称。公式可以通过复制|粘贴（或选择性粘贴公式），自动填充的方式，计算其余部分。各种输入框中区域的录入A1：F5 A1：F5，G3：H4A1：F5 C3：G6鼠标直接圈选！左上左上A1A1，右下，右下F5F5矩形区域矩形区域，代表并集，但和数学上并集不，代表并集，但和数学上并集不同，不去掉重复的部分同，不去掉重复的部分空格代表交集，与数学上相同空格代表交集，与数学上相同引用其他工作簿或工作表单元格的格式工作薄名称工作表名称!单元格引用aa sheet1!A3单元格引用公式可以通过复制|粘贴（或选择性粘贴公式），自动填充的方式，计算其余部分。根据插入点的变化，公式中引用的单元格行列做相应的

9、移动，例如。在H5中输入=B2+C3+D4。复制到K8中，行加了3，所有引用行都要加3，列增加了2，所有引用列都要加2。则公式将变为？绝对和混合引用使用公式计算总成绩。计算总成绩所占的比例。？。自动填充其他同学总成绩。H2蓝色H13绿色混合引用和绝对应用上例中的H13是希望在公式插入点发生改变时，它不改变。在不变的行或者列上加$,给它钱，让他老老实实待着！此时公式中13行的前边加了,公式复制时，13行永不改变由于列前边没有$,公式复制时，H列会改变，如果希望它也保持不变，给钱！H2蓝色，行未固定H13绿色，行前固定行未固定，变化行前有行前有$，固定，不变化，固定，不变化名称定义在Excel中频

10、繁使用的公式或者引用可以定义为“名称”。定义名的规则：禁止重名：某些符号不能出现：？，*，空格等不能与单元格重名不能超过255个字符区分大小写将所选区域定义为aa使用名称使用名称将现有行和列标题转化为名称下拉框可查看所有名称下拉框可查看所有名称另外一种定义方式点此可将名称插入公式中点此可将名称插入公式中使用函数插入函数的方法使用函数的一般方法搜索函数搜索函数函数分类函数分类函数选择函数选择函数参数及功能说明函数参数及功能说明helphelp以以排排名名函函数数来来进进行行说说明明常用函数SUM(number1,number2,.)SUM(number1,number2,.)求和函数，计算参数的

11、合求和函数，计算参数的合=SUM(A1:A5)=SUM(A1:A5)是将单元格是将单元格A1A1至至A5A5中的所有数值相加中的所有数值相加=SUM(A1,A3,A5)=SUM(A1,A3,A5)是将单元格是将单元格A1A1、A3A3和和A5A5中的数字相加。中的数字相加。类似函数有：类似函数有：average max minaverage max minSumIf函数之要计算的区域要计算的区域设置条件此处不填AverageifAverageif与与sumifsumif用法基本相用法基本相似，不再切图似，不再切图要计算的区域要计算的区域设置条件结果结果解释：将解释：将E2E2到到G5G5所有数

12、值大于所有数值大于7575的单元格里的值加起来，求的单元格里的值加起来，求和和SumIf函数之设置条件的区域设置条件的区域设置条件要计算的部分在此区域中搜索在此区域中搜索确定哪些行要计算的部分要计算的部分行列交叉点参与计算AverageIfS 与SumIfS要计算的区域要计算的区域条件区域和条件条件区域和条件1 1条件区域和条件条件区域和条件2 2解释：计算解释：计算E2E2：E12E12的平均值的平均值只计算物理系的男生，这里有两个只计算物理系的男生，这里有两个条件，条件之间是并且关系条件，条件之间是并且关系要计算的区域要计算的区域筛选物理系筛选物理系筛选性别男筛选性别男最终参与计算Rank

13、.eq和rank.avg这个数这个数在这一堆数中在这一堆数中排第几排第几按什么顺序排解释：查看解释：查看A2A2在在A2A2：A10A10这些数中的这些数中的排位，要注意相对与绝对引用，也排位，要注意相对与绝对引用，也要注意排序方式，升序还是降序要注意排序方式，升序还是降序rank.eq和rank.avg的区别解释：解释：EQEQ最好排名最好排名AVGAVG平均排名平均排名Vlookup函数在此区域的第一列在此区域的第一列查找第一个这个数查找第一个这个数返回的第几列第几列此处汉化有错此处汉化有错解释：因为不是精确匹配，所解释：因为不是精确匹配，所以第一个参数从以第一个参数从7777到到8686

14、结果都结果都一样，自己总结下。如果是文一样，自己总结下。如果是文字非精确匹配包含在内，或者字非精确匹配包含在内，或者完全相同，例如使用完全相同，例如使用“马马”或或“力力”都能找到都能找到“马力马力”“”“绝对值函数绝对值函数 ABS(number)ABS(number)功能：返回数值number的绝对值 例如：=ABS(-2)表示求-2的绝对值；=ABS(A2)，表示对单元格A2中的数值求取绝对值。向下取整函数向下取整函数INT(number)INT(number)功能：将数值number向下舍入到最接近的整数 例如：=INT(8.9)表示将 8.9 向下舍入到最接近的整数，结果为8；=IN

15、T(-8.9)表示将-8.9 向下舍入到最接近的整数，结果为-9。四舍五入函数四舍五入函数 ROUND(number,ROUND(number,num_digitsnum_digits)功能：将指定数值number按指定的位数num_digits进行四舍五入。例如：=ROUND(25.7825,2)，表示将数值25.7825四舍五入为小数点后两位。提示：如果希望始终进行向上舍入，可使用ROUNDUP函数；如果希望始终始终进行向下舍入，则应使用ROUNDDOWN函数。57去尾取整函数去尾取整函数TRUNC(number,num_digits)功能：将指定数值number的小数部分截去，返回整数。

16、num_digits为取整精度，默认为0。例如：=TRUNC(8.9)表示取8.9的整数部分，结果为8；=TRUNC(-8.9)表示取-8.9的整数部分，结果为-8。当前日期和时间函数当前日期和时间函数 NOW()NOW()功能：返回当前计算机系统的日期和时间。当将数据格式设置为数值时，将返回当前日期和时间所对应的序列号，该序列号的整数部分表明其与1900年1月1日之间的天数。函数函数 YEAR(serial_number)YEAR(serial_number)功能：返回指定日期对应的年份。返回值为 1900 到 9999 之间的整数 例如：=YEAR(A2)当在A2单元格中输入日期2008/

17、12/27时，该函数返回年份2008。59当前日期函数当前日期函数 TODAY()功能：返回今天的日期。通过该函数，可以实现无论何时打开工作簿时工作表上都能显示当前日期；该函数也可以用于计算时间间隔，可以用来计算一个人的年龄。例如：=YEAR(TODAY()-1963 假设一个人出生在1963年，该公式使用TODAY函数作为YEAR函数的参数来获取当前年份，然后减去1963，最终返回对方的年龄。6061IF函数判断条件条件成立时条条件件不不成成立立时时If e260 Then 结果为”未通过”Else 结果为”通过”IF嵌套或多重IF大多数Excel都可以嵌套，就是把一个函数作为另外一个函数的

18、参数，写在函数的括号里。F(g(x)+1)IF e260 thenIF e260 then 结果为结果为”挂科挂科”else else（否则）（否则）结果为结果为IF e285IF e285 结果为结果为”中等中等”Else Else（否则）（否则）结果为结果为”优秀优秀”此处隐含条件大于60此处也可嵌套计数函数Count和CountACount：统计参数所选区域内数字的个数日期作为数字处理CountA：统计参数所选区域内不为空的单元格个数空单元格CountIf判断条件CountIf：统计参数所选区域内符合特定条件单元格的个数，不一定是数字。5个女生CountIfsCountIf：统计参数所选

19、区域内符合特定多个条件行的个数，不一定是数字。条件区域一条件区域二统计女生而且数学大于等于85的人数文本合并函数文本合并函数CONCATENATE(text1,text2,.)CONCATENATE(text1,text2,.)功能：将几个文本项合并为一个文本项。可将最多255个文本字符串联接成一个文本字符串。联接项可以是文本、数字、单元格地址或这些项目的组合。例如：=CONCATENATE(B2,C2)表示将单元格B2中的字符串、空格字符以及单元格C2中的值相连接，构成一个新的字符串。提 示：也 可 以 用 文 本 连 结 运 算 符“&”代 替CONCATENATE 函数来联接文本项。例如

20、，=A1&B1与=CONCATENATE(A1,B1)返回的值相同。66第3章 通过 Excel 创建并处理电子表格截取字符串函数截取字符串函数MID(text,start_num,num_chars)功能：从文本字符串中的指定位置开始返回特定个数的字符。例如：=MID(A2,7,4)表示从单元格A2中的文本字符串中的第7个字符开始提取4个字符。67第3章 通过 Excel 创建并处理电子表格3.3 Excel 公式和函数左侧截取字符串函数左侧截取字符串函数 LEFT(text,num_chars)功能功能：从文本字符串最左边开始返回指定个：从文本字符串最左边开始返回指定个数的字符，也就是最前

21、面的一个或几个字符。数的字符，也就是最前面的一个或几个字符。例如：例如：=LEFT(A2,4)=LEFT(A2,4)表示从单元格表示从单元格A2A2中的文中的文本字符串中提取前四个字符。本字符串中提取前四个字符。右侧截取字符串函数右侧截取字符串函数 RIGHT(text,num_chars)功能功能：从文本字符串最右边开始返回指定个：从文本字符串最右边开始返回指定个数的字符，也就是最后面的一个或几个字符。数的字符，也就是最后面的一个或几个字符。例如：例如：=RIGHT=RIGHT (A2,4)(A2,4)表示从单元格表示从单元格A2A2中的中的文本字符串中提取后四个字符。文本字符串中提取后四个

22、字符。68Len和Trim字符个数统计函数 Len(表达式)功能：统计一个表达式内的符号（不一定是文本，任何内容都可以）个数，空格也计算在内。一个单元格可视为一个表达式，但区域内仅统计第一个单元格。字符去空格函数 Trim(字符串)功能：去掉一个字符串两端的空格。公式中的错误错误显示错误显示说说 明明#当某一列的宽度不够而无法在单元格中显示所有字符时，或者单元格包含负的日期或时间值时，Excel将显示此错误。#DIV/0!当一个数除以零(0)或不包含任何值的单元格时，Excel将显示此错误。#N/A当某个值不允许被用于函数或公式但却被其引用时，Excel 将显示此错误。#NAME?当Excel

23、无法识别公式中的文本时，将显示此错误。例如，区域名称或函数名称拼写错误，或者删除了某个公式引用的名称。#NULL!当指定两个不相交的区域的交集时，Excel将显示此错误。交集运算符是分隔公式中的两个区域地址间的空格字符。例如，区域A1:A2和C3:C5不相交，因此，输入公式=SUM(A1:A2 C3:C5)将返回#NULL!错误。#NUM!当公式或函数包含无效数值时，Excel将显示此错误。#REF!当单元格引用无效时，Excel将显示此错误。例如，如果删除了某个公式所引用的单元格，该公式将返回#REF!错误。#VALUE!如果公式所包含的单元格有不同的数据类型，则Excel将显示此错误。如果

24、启用了公式的错误检查，则屏幕提示会显示“公式中所用的某个值是错误的数据类型”。通常，通过对公式进行较少更改即可修复此问题。第五部分：表格中的信息处理第五部分：表格中的信息处理Excel中的信息处理排序筛选分类汇总和分级显示数据透视表和透视图模拟分析与运算合并计算简单排序：选表中用于排序的列中的一个单元格（PS：不要选区域）。排序升序降序严重错误排序复杂排序：多列排序点此打开全表以此列降序的按次的按次关键字关键字排序排序第一行是不是标题，第一行是不是标题，如果是不参与排序如果是不参与排序如果不选，参与排序如果不选，参与排序一般不调整排序排序时隐藏列不参与排序，因此需要将隐藏列先显示出来非汉字按A

25、SCII码顺序，汉字按字典顺序，数字按大小，日期早小于晚，逻辑false小于true除了升序和降序以外，还可以选择自定义序列，这个自定义序列就是自动填充的那个，比如星期一，星期二排序后结果被修改选中要排序的区域，在”插入”选项卡中插入”表格”在生成的表中排序，排序后如果某结果被修改，点此筛选筛选的作用：保留想看的，隐藏不想看的，不是删除，是隐藏。在表区域内任意单元格单击点一次点一次筛选，筛选，再点一再点一次取消次取消是否已应用筛选操作单击某列筛选标志排序选择可见行筛选条件设置筛选条件并且还是或者第二个条件高级筛选-适用情况使用自动筛选，如果同时应用了两列条件，这两个条件是并且的关系。国贸、计算

26、机两系的女生国贸系学生或者计算机系的女生怎么办？高级筛选-设置条件高级筛选需要自己设置条件。也可以自己打上性别两字，但如图操作可减少错误。同行并且，不同行或者。数学成绩85分以上并且是国贸系学生或者是计算机系并且是女生85分以上的国贸系学生或者计算机系女生高级筛选-应用条件筛选何处条件在哪？结果放哪？清除筛选分类汇总和分级显示对每个班级，每个专业，每种产品进行统计。例如：每个专业英语数学计算机（各科）平均成绩。分类依据，即每个XX或者各XX平均？最大？求和？参与汇总的各列此处重要！结果是错的！错误在哪？先用分类字段排序，也就是分类，将每一类数据放在一起。多级汇总例如：各专业女生英语、数学平均值

27、。在上图基础上再次分类汇总，此时应用性别分类。PS：显然专业是主分类标准，性别是次分类标准，所以原始表应以专业为主关键字，性别为次关键字排序。分级显示手动创建分组显示先对分组依据排序。在每一组后插入汇总行。输入函数和汇总名称选中整个表创建组，不选全表汇总行。再选中每个组创建组，不选汇总行。将汇总结果复制到他处通过分级显示操作选中需要的数据，隐藏不需要的数据。选中要复制的区域在“开始”选项卡中。仅复制可见内容。数据透视表与透视图数据透视表。筛选条件列分组行行分分组组参与统计的数据统统计计方方式式每个下拉三角都可以对数据进行筛选。创建数据透视表创建数据透视表数据透视表的维护与格式化在数据透视表中任

28、意位置单击。与与数数据据源源同同步步改改变变数数据据源源自自己己看看看看修修改改计计算算和和显显示示方方式式显显示示与与隐隐藏藏内内容容数据透视图就是以图的形式显示表。创建数据透视图在数据透视表中任意位置单击。创建数据透视图同透视表列标题同透视表行标题设置过程与透视表类似。创建数据透视图选透视表任意位置，选择。Delete删除透视表删除透视表后，透视图变为图表，会有变化。单击透视图delete删除透视图，对表格和透视表无影响。透视图格式化，自己看下吧Excel合并计算有这样两个表，希望对每个专业求各科的平均值，此时需要进行合并计算。进行合并计算汇总方式行分类或列分类选择要计算的区域修改计修改计

29、算和显算和显示方式示方式合并计算-添加区域将区域添加到引用位置另外一个工作簿这两个表中有一个重复行“王志平”，删除其中一个表中的该行，看看结果有没有变化？模拟分析与运算单变量求解模拟运算表方案管理器单变量求解卖多少才可以获利10000？这就是解方程呀。数据选项卡模拟运算表如何确定单价与利润关系。只能选第一列，其他不能选。或者第一行。输入数据看利润变化方案管理器三个三个双边量分析模拟运算、变量求解的不足变量求解只处理一个变量模拟运算最多只能处理两个变量，且方案有限，不能超过32个使用方案管理器可以无限制的添加条件和方案方案管理器使用新方案修改方案查看本方案结果方案管理器使用-显示全部点此第五部分

30、：第五部分：ExcelExcel的图表的图表Mini 图插入Mini图选择放置Mini图的单元格，可以是一个也可以是多个，多个单元格应该与，引用单元格数目相同。点击“插入”选项卡调整mini图的插入Mini图可在Mini图单元格内插入说明文本。可在Mini图单元格内插入说明文本。其余单元格可以进行填充。Mini图是动态图，可以随数据变化而改变形态。修改和删除Mini图Mini图工具选项卡。改变Mini图。因为自动填充所以又组合，先取消。修修改改颜颜色色和和线线形形其他的类型各连接点表中的隐藏或空单元格的处理Mini图工具选项卡。图表（几乎必考）创建方法选择一个单元格或者图表要应用的数据区域，可以是不连续的。在各种图表中选择一个。或者点击最下方所有图表类型。点此图表的移动在同一工作表中点选图表后鼠标拖动。移动到不同工作表。选择为图表新建图表组成图表区绘图区数据标签坐标轴数据点图例区图表标题坐标轴标题图表的格式化更更改改图图表表类类型型添添加加和和删删除除数数据据修修改改图图表表布布局局制制式式图图表表图表的每个组成部分都有自己的样式和布局，双击该部分或者右键点击均可修改其格式与布局为图表添加标题图图表表标标题题坐坐标标轴轴标标题题图图例例显显示示位位置置是是否否显显示示数数据据标标签签是是否否显显示示表表格格图表标签链接到表格设置坐标轴和网格线