1、EXCEL高级技巧大全有史以来最全共45页文档Excel 第一章 数据的整理和分析要练说，得练看。看与说是统一的，看不准就难以说得好。练看，就是训练幼儿的观察能力，扩大幼儿的认知范围，让幼儿在观察事物、观察生活、观察自然的活动中，积累词汇、理解词义、发展语言。在运用观察法组织活动时，我着眼观察于观察对象的选择，着力于观察过程的指导，着重于幼儿观察能力和语言表达能力的提高。 1.1 数据的排序“师”之概念，大体是从先秦时期的“师长、师傅、先生”而来。其中“师傅”更早则意指春秋时国君的老师。说文解字中有注曰：“师教人以道者之称也”。“师”之含义，现在泛指从事教育工作或是传授知识技术也或是某方面有特
2、长值得学习者。“老师”的原意并非由“老”而形容“师”。“老”在旧语义中也是一种尊称，隐喻年长且学识渊博者。“老”“师”连用最初见于史记，有“荀卿最为老师”之说法。慢慢“老师”之说也不再有年龄的限制，老少皆可适用。只是司马迁笔下的“老师”当然不是今日意义上的“教师”，其只是“老”和“师”的复合构词，所表达的含义多指对知识渊博者的一种尊称，虽能从其身上学以“道”，但其不一定是知识的传播者。今天看来，“教师”的必要条件不光是拥有知识，更重于传播知识。 Excel 提供了多种方法对工作表区域进行排序，用户可以根据需要按行或列、按升序或语文课本中的文章都是精选的比较优秀的文章,还有不少名家名篇。如果有选
3、择循序渐进地让学生背诵一些优秀篇目、精彩段落,对提高学生的水平会大有裨益。现在,不少语文教师在分析课文时,把文章解体的支离破碎,总在文章的技巧方面下功夫。结果教师费劲,学生头疼。分析完之后,学生收效甚微,没过几天便忘的一干二净。造成这种事倍功半的尴尬局面的关键就是对文章读的不熟。常言道“书读百遍,其义自见”,如果有目的、有计划地引导学生反复阅读课文,或细读、默读、跳读,或听读、范读、轮读、分角色朗读,学生便可以在读中自然领悟文章的思想内容和写作技巧,可以在读中自然加强语感,增强语言的感受力。久而久之,这种思想内容、写作技巧和语感就会自然渗透到学生的语言意识之中,就会在写作中自觉不自觉地加以运用
4、、创造和发展。 降序已经使用自定义排序命令。当用户按行进行排序时，数据列表中的列将被重新排列，但行保持不变，如果按列进行排序，行将被重新排列而列保持不变。没有经过排序的数据列表看上去杂乱无章，不利于我们对数据进行查找和分析，所以此时我们需要按照对数据表进行整理。我们可以将数据列表按“出生年月”进行排序。如下图：首先我们需要单击数据列表中的任意一个单元格，然后单击数据标签中的排序按钮，此时会出现排序的对话框。看到弹出的排序对话框中，在“主要关键字”下拉列表框中选择“出生年月”，在设置好主要关键字后，可以对排序依据进行设置，例如，数值、单元格颜色、字体颜色、单元格图标，在这样我们可以选择默认的数据
5、作为排序依据。最后我们可以对数据排序次序进行设置，在次序下拉菜单中选择升序、降序或自定义排序。在这里我们选择升序，设置完成后单1击确定即可。如下图：我们除了可以对数据表进行单一列的排序之外，如果用户希望对列表中的数据按“性别”的升序来排序、性别相同的数据按“文化程度”升序排序、“性别”和“文化程度”都相同的记录按照“基本工资” 从小到大的顺序来排序，此时我们就要对 3 个不同的列进行排序才能达到用户的要求。首先我们需要单击数据列表中的任意一个单元格，然后单击数据标签中的排序按钮，此时会出现排序的对话框。看到弹出的排序对话框中，在“主要关键字”下拉列表框中选择“性别”，在添加好主要关键字后，单击
6、“添加条件”按钮，此时在对话框中显示“次要关键字”，同设置“主要关键字”方法相同，在下拉菜单中选择“文化程度”，然后再点击“添加条件”添加第三个排序条件，选择“基本工资”。在设置好多列排序的条件后，单击确定即可看到多列排序后的数据表。如下图：在 Excel 2019 中，排序条件最多可以支持 64 个关键字。1.2 数据的筛选筛选数据列表的意思就是将不符合用户特定条件的行隐藏起来，这样可以更方便的让用2户对数据进行查看。Excel 提供了两种筛选数据列表的命令。自动筛选：适用于简单的筛选条件高级筛选：适用于复杂的筛选条件想要使用 Excel 的自动筛选功能，首先要单击数据列表中的任意单元格，然
7、后选择数据标签，单击筛选按钮即可。单击数据列表中的任何一列标题行的下拉箭头，选择希望显示的特定行的信息，Excel会自动筛选出包含这个特定行信息的全部数据。如下图：在数据表格中，如果单元格填充了颜色，使用 Excel 2019 我们还可以按照颜色进行筛选。如果条件比较多，可以使用“高级筛选”来进行。使用高级筛选功能可以一次把我们想要看到的数据都找出来。例如数据表中我们想要把性别为男、文化程度为研究生、工资大于 2000 的人显示出来。我们可以这么来做：先设置一个条件区域，第一行输入排序的字段名称，在第二行中输入条件，建立一个条件区域。如下图：3然后选中数据区域中的一个单元格，单击数据标签中的高
8、级筛选命令。Excel 自动选择好了筛选的区域，我们单击这个条件区域框中的拾取按钮，选中我们刚才设置的条件区域，单击拾取框中的按钮返回高级筛选对话框，单击确定按钮，现在表中就是我们希望看到的结果了。设置高级筛选条件高级筛选可以设置行与行之间的“或”关系条件，也可以对一个特定的列指定三个以上的条件，还可以指定计算条件，这些都是它比自动筛选优越的地方。高级筛选的条件区域应该至少有两行，第一行用来放置列标题，下面的行则放置筛选条件，需要注意的是，这里的列标题一定要与数据清单中的列标题完全一样才行。 在条件区域的筛选条件的设置中，同一行上的条件认为是“与”条件，而不同行上的条件认为是“或”条件。1.3
9、 在筛选时使用通配符在设置自动筛选的自定义条件时，可以使用通配符，其中问号（？）代表任意单个字符，星号（*）代表任意一组字符。41.4 数据的分类汇总与分级显示分类汇总是 Excel 中最常用的功能之一，它能够快速地以某一个字段为分类项，对数据列表中的数值字段进行各种统计计算，如求和、计数、平均值、最大值、最小值、乘积等。比如这张各部门工资统计表，我们希望可以得出数据表中每个部门的员工实发工资之和。现在我们用分类汇总来做：首先单击部门单元格，单击数据标签中的升序按钮，把数据表按照“部门”进行排序，然后在数据标签中，单击分类汇总按钮，在这里的分类字段的下拉列表框中选择分类字段为“部门”，选择汇总
10、方式为“求和”，汇总项选择一个“实发工资”，单击“确定”按钮。如下图：5当我们点击确定后，我们就可以看到已经计算好各部门实发工资之和了。如下图：在分类汇总中我们的数据是分级显示的，现在工作表的左上角出现了这样的一个区域，我们单击这个 1，在表中就只有这个总计项出现了。6单击这个 2，出现的就只有这些汇总的部分，这样我们可以清楚地看到各部门的汇总。单击 3，可以显示所有的内容。复制汇总结果当我们使用分类汇总后，往往希望将汇总结果复制到一个新的数据表中，如下图。但当我们直接进行复制后，无法只复制汇总结果，而复制的是所有数据。此时我们就需要使用“Alt+;”组合键选取当前屏幕中显示的内容，然后在进行
11、复制粘贴。1.5 条件格式使用 Excel 中的条件格式功能，可以预置一种单元格格式，并在指定的某种条件被满足时自动应用于目标单元格。可以预置的单元格格式包括边框、底纹、字体颜色等。此功能可以根据用户的要求，快速对特定单元格进行必要的标识，以起到突出显示的作用。7例如下图中的产品销售标，我们需要快速找出所有与“防水键盘”相关的销售数据。首先我们全选所有数据，然后在开始标签中找到条件格式按钮，单击突出显示单元格规则，在打开的选项中单击等于按钮。8在弹出的对话框中输入我们想要查找的“防水键盘”，然后设置单元格显示样式，例如让单元格以“浅红填充色深红色文本”显示，设置完毕后，点击确定按钮。在点击确定
12、后，我们可以数据表中已经显示出和防水键盘相关的所有信息。这样我们就可以快速查看我们所关心的信息了。如下图：在 Excel 2019 中，使用条件格式不仅可以快速查找相关数据，还可以以数据条、色阶、图标的方式显示数据，让用户可以对数据一目了然。首先我们全选所有数据，然后在开始标签中找到条件格式按钮，单击数据条，在打开的选项中选择任意颜色。当我们选择颜色时，即使不点击，也可以即时的看到数据变化。通过条件格式，我们就可以清楚的看到那条数据销售额最高，那条数据销售额最低。如下图：91.6 数据透视表数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式动态表格，能帮助用户分析、组织数据。例如，计算平
13、均数、标准差，建立列联表、计算百分比、建立新的数据子集等。建好数据透视表后，可以对数据透视表重新安排，以便从不同的角度查看数据。数据透视表可以从大量看似无关的数据中寻找背后的联系，从而将纷繁的数据转化为有价值的信息，以供研究和决策所用。我们来看下面这个数据表，在这个数据表中显示了每个财季产品销售的明细数据。10现在我们想把这里的统计每个财季的利润，可以结合条件格式判断最低利润出自哪个财季。首先打开插入标签，单击数据透视表按钮，打开创建数据透视表对话框。然后选择透视表的数据来源的区域，Excel 已经自动选取了范围，这里它的选取是正确的，我们不做什么改动，接下来我们选择透视表放置的位置，选择新建
14、工作表项，单击确定按钮。现在就出现了我们建立的数据透视表。在工作表的透视表的各个部分都有提示，同时界面中出现了一个数据透视表字段列表，里面列出了所有我们可以使用的字段，我们的目的是查看统计每个财季的利润，因此我们在要添加到报表的字段中选择财季，然后拖动利润和数量字段到数值区域，此时，我们就可以看到每个财季的利润和销售数量显示在数据透视表中了。同样的，在数据透视表中可以直接看到利润和销售数量的汇总数目。现在我们就可以结合条件格式功能，查看出那个财季利润最低。如下图：111.7 数据透视图的使用我们还可以根据数据透视表直接生成图表：点击选项标签，点击数据透视图按钮，在弹出的对话框中选择图表的样式后
15、，单击确定就可以直接创建出数据透视图。不难看出这个图表跟我们平时使用的图表基本上一致，所不同的只是这里多了几个下拉箭头，单击“财季”的下拉箭头，这实际上是透视表中字段，选择 “Q1”，可以看到图表中的数据也发生了变化，其它有很多在透视表中使用的方法也可以在这个图表中使用，把图表的格式设置一下，一个漂亮的报告图就完成了。121.8 获取外部数据在 Excel 中，我们可以获取外部的数据到 Excel 中，这样我们就可以利用 Excel 的功能对数据进行整理和分析。以一个例子来看：这里有这样的一个文本文件，我们把其中的数据引入到 Excel 中来。打开 Excel，打开数据标签，我们可以看到很多种
16、文件类型都可以倒入到 Excel 表格中。我们点击“自文本”按钮，然后选择我们文本所在位置，选择完毕后，此时会弹出“文本导入向导”，这里的原始数据类型就选择“分隔符号”一项，在这个对话框的下面有一个预览框，从这里可以看出来现在的第一行没有什么用处，因此我们将这里的 “导入起始行”输入框中的数值改成 2，单击“下一步”。13现在我们要选择文本文件的数据字段分隔符，根据文件的特点，我们选择为“Tab 键”，单击“下一步”按钮。现在要选择导入数据的默认格式，现在的默认格式是“常规”，我们这里不做什么改动，单击“完成”按钮，此时出现“导入数据”对话框，我们可以选择将数据导入到现有工作表或新建工作表。把
17、文本文件中的数据全部引入进来以后，把它们稍微调整一下，我们就可以使用这些数据了。141.9 冻结和拆分窗口拆分窗口：我们可以使用视图标签中的拆分按钮来拆分窗口。而如果只需要垂直拆分，可以直接拖动垂直滚动条到最顶端，此时横项拆分线就会消失。撤消窗口的拆分：同样在视图标签下，再次点击拆分按钮即可撤销窗口的拆分。冻结窗格：我们可以使用冻结窗格来更好的查看数据表头。首先选中一个单元格，选择视图标签，点击冻结窗，在下拉菜单中我们可以选择冻结第一行或第一列，当然我们也可以自行选择想要冻结的位置。如下图：如果我们想要冻结第一行以及 A、B 两列，此时我们需要将光标放在第一行的下面，并且 A、B 两列的右侧，
18、即 C2 单元格，此时点击冻结拆分窗格即可冻结第一行以及 A、B 两列。151.10 表对象在 Excel 2019 中，我们可以直接将数据表格直接转换为表对象，这样我们就可以直接对数据表格进行排序、筛选、调整格式等操作。首先我们找到插入标签，点击表按钮，此时我们就将数据表格转换为了表对象。我们可以看到，当我们把数据转换为表对象后，他会自动出现筛选功能。图下图：并且在设计标签中，我们可以即时调整表的样式等。如下图：1.11 插入 SmartArt在 Excel 2019 中，有了一种新的对象可以更好的丰富 Excel 数据表，那就是 SmartArt。我们可以通过插入标签，点击 SnartAr
19、t，在弹出得对话框中选择想要插入 SmartArt 类型以及具体样式，如下图：16我们选择基本列表后，点击确定，这样一个 SmaerArt 对象就直接添加到我们的数据表当中，此时我们就可以输入文字、数据并且调整样式了。很快，一个漂亮的 SmartArt 就已经出现在我们的 Excel 当中了。如下图：1.12 插入其他的对象插入一个已存在的对象：在插入标签中，点击对象按钮，在对象对话框中（如下图）选择“Microsoft Office Visio 绘图”，点击确定，此时就会在 Excel 中启动 Visio 绘图功能，当我们绘制好图形后，就可以将 Visio 图直接插入到 Excel 中。17
20、1.13 选择性粘贴Excel 为我们提供了一些自动功能。例如选择性粘贴。这里的选择性粘贴是指把剪贴板中的内容按照一定的规则粘贴到工作表中，而不是像前面那样简单地拷贝。就拿这个数据表来举例。如下图：这里的“销售利润”一栏是我们使用公式计算得到的，选择这一栏，复制到 Sheet2 中，可以看到数值并没有跟着复制过来；这时就可以使用选择性粘贴了：单击鼠标右键，单击“选择性粘贴”命令，打开“选择性粘贴”对话框（如下图），在“粘贴”一栏中选择“数值”，单击“确定”按钮，数值就可以粘贴过来了。这种情况不仅是在几个工作表之间复制时会发生，在同一个工作表中进行复制时也会遇到，到时可要注意。18选择性粘贴还有
21、一个很常用的功能就是转置功能。简单地理解就是把一个横排的表变成竖排的或把一个竖排的表变成横排的：选择这个表格，复制一下，切换到另一个工作表中，打开“选择性粘贴”对话框，选中“转置”前的复选框，单击“确定”按钮，可以看到行和列的位置相互转换了过来。另外一些简单的计算也可以用选择性粘贴来完成：选中这些单元格，复制一下，然后打开“选择性粘贴”对话框，在“运算”一栏选择“加”，单击“确定”按钮，单元格的数值就是原来的两倍了。 此外你还可以粘贴全部格式或部分格式，或只粘贴公式等等。1.14 自动更正现在我们在单元格中输入（c）然后按一下回车，可以看到输入的字符变了。 这是 Excel的自动更正功能，我们
22、再输入(r)，回车；同样可以看到输入的字符又变了。其实这些都是可以设置的：点击 office 按钮，单击选项，在校对中找到自动更正选项按钮，单击会打开自动更正对话框（如下图），这里列出了所有自动更正的选择，在“键入时自动替换”列表中的头两个就是我们刚才看到的，输入(c)则替换为(c)，输入(r)则替换为(r)。19我们还可以把自己容易犯错的词做一个自动更正的词条，以后再输入起来就很方便了：在“替换”输入框中写入“制做”，在“替换为”输入框中填上“制作”，单击“添加”按钮将其添加到列表中，单击“确定”按钮；在单元格中输入“制做”，回车，可以看到“制做”就被替换成了“制作”。如果你不想使用哪个自动
23、更正的选项了，就在自动更正对话框中把这个选项去掉就行了。1.15 宏的使用在 Excel 中有一种重要的自动功能，那就是“宏”。 我们每次听到“宏”总是和病毒在一块儿，一般自己做的宏是不会对自己有害的。合理地使用宏可以大幅度地提高工作效率，而且有些操作不使用宏的话会很麻烦。 最简单的宏可以通过录制来做。在 Excel 2019 中要想使用宏功能，需要添加开发工具标签。首先点击 Office 按钮，单击 Excel 选项按钮，在常用选项中，将“在功能区显示开发工具选项卡”勾选，点击确定返回 Excel 界面后，我们就可以看到在标签中多了一个“开发工具”。20现在比如我们写一个宏，让它删除 A3
24、单元格的内容：点击开发工具标签，单击录制宏按钮，在弹出“录制新宏”对话框（如下图），在“宏名”输入框中输入宏的名字，单击“确定”按钮。此时我们就可以进行宏的录制了，在开发工具标签中最左侧可以看到目前的状态是“停止录制”，表示当我们在创建一个宏后，就已经开始在录制宏了；单击 A3 单元格，按一下Delete 键，这个宏的操作就算完成了，单击“停止录制”。现在在 A3 单元格中随便填上数值，选择其它的单元格，找到开发工具标签，单击宏按钮，打开“宏”对话框（如下图），选择我们刚才录制的宏，单击“执行”按钮，A3 单元格的内容就没有了。现在我们打开一个带有宏的工作簿，Excel 会提醒我们打开的文件中
25、带有宏，如果你不能确定宏是否带有恶意的成分就选择“禁用宏”，否则可以选择“启用宏”；在禁用之后即使宏中有恶意成份也不会对你的机器起作用了。211.16 超链接的使用在 Excel 中我们可以使用超级链接连接其它的文件或 Web 地址，也可以粘贴一个电子邮件地址，在用鼠标单击这个地址时自动启动相应的电子邮件程序，发送电子邮件等等。选中要插入超级链接的单元格，找到插入标签，单击超链接按钮，打开“插入超级链接”对话框（如下图），这里的“要显示的文字”输入框中显示的就是单元格中的文字；先来插入一个经常去的 Web 页面：注意左边这个“原有文件或网页”按钮处于“按下”的状态，在列表中显示出了最近浏览过的
26、页面，在上面的输入框中我们输入 microsoft，单击“确定”按钮将这个超级链接设置好；现在将鼠标移动到单元格上，鼠标变成了手的形状，单击，系统就会自动启动浏览器对我们输入的页面进行浏览了。不过这只是超级链接作用的一个小方面，更多的时候使用超级链接是为了查阅工作表的方便；在 A1 单元格上单击右键，从弹出的菜单中单击“超链接”按钮，就可以打开“插入超链接”对话框（如下图），单击左边“链接到”列表中的“本文档中的位置”按钮，对话框的样子就发生了改变，单击“或在这篇文档中选择位置”：列表中“单元格引用”前面的加号，选择“Sheet2”，在“请键入单元格引用”输入框中就显示出了“A1”，这正是我们
27、想要链接的单元格，单击“确定”按钮设置好链接，现在单击这个单元格，Excel 就自动跳转到了 Sheet2 的 A1 单元格上。22我们还可以像在网页上那样插入一个邮件地址：选择单元格，打开单击右键，从弹出的菜单中选择“超链接”按钮，打开“插入超链接”对话框，单击左边“链接到”列表中的“电子邮件地址”按钮，然后在“电子邮件地址”输入框中输入要插入的电子邮件地址，单击“确定”按钮，就将电子邮件地址链接到了单元格中；单击单元格，就会启动默认的电子邮件程序给刚才插入的电子邮件地址发送信件了。我们还可以链接到其它的文档中，打开“插入超链接”对话框，选择“链接到”列表中的“原有文件或网页”按钮，然后单击
28、“当前文件夹”按钮，选择要链接的文件，单击“确定”按钮设置好链接，单击刚才设置的单元格，就可以转而查阅链接的文档了。1.17 数据的有效性使用 Excel 的数据有效性功能，可以对输入单元格的数据进行必要的限制，并根据用户的设置，禁止数据输入或让用户选择是否继续输入该数据。例如在一个员工报销单中，用户只能在日期中输入 2019 年度、报销类型中只能进行选择规定的类型、发票号码只能输入 10 位数等。我们一起来看一下例子：在下图中是一个员工报销单，我们需要对表中的数据进行有效性设置。首先想要设置的单元格，比如我们想要对“日期”列进行设置，点击“日期”列中任意单元格，点击数据标签中的有效性按钮，在
29、“设置”选项卡中找到“允许”下拉菜单，单击“日期”，在“数据”中选择“介于”，在“开始日期”和“结束日期”中分别输入“2019-1-1”和“2019-12-31”。如下图：然后选择“输入信息”选项卡，在“标题”和“输入信息”中分别输入“请输入日期”和“输入日期范围：2019-1-1 至 2019-12-31”。最后选择“出错警告”选项卡，同样在“标题”和“错误信息”中输入“输入错误”和“输入日期范围：2019-1-1 至 2019-12-31”。在输入完信息后，点击确定。此时我们返回数据表，点击单元格就可以看到我们所设置的。23除了可以对单元格进行日期的设置外，我们还可以对单元格进行序列的设置
30、，让用户在单元格中对所有信息进行挑选。同样在数据标签中点击数据有效性按钮，在“设置”选项卡“允许”中找到“序列”，在“来源”中输入所选信息，例如“住宿,交通费,其他”，同样设置好“输入信息”和“出错警告”点击确定即可。点击单元格，我们会看到下拉箭头，点击之后，我们就可以对单元格中的数据进行选择了。如下图：24Excel 第二章 Excel 中的图表2.1 图表的建立图表是图形化的数据，它由点、线、面等图形与数据文件按特定的方式而组合而成。一般情况下。用户使用 Excel 工作薄内的数据制作图表，生成的图表也存放在工作薄中。图表是 Excel 的中要组成部分，具有直观形象、双向联动、二维坐标等特
31、点。我们拿一个例子来讲吧：这是一个市场调查表，显示了几种品牌的饮料在各个季度的销量百分比。我们来做一个表示第一季度的几种商品所占比例的饼图，首先选择数据区域，然后选择插入选项卡，单击饼图按钮，再打开的下拉菜单中选择饼图样式，此时我们就可以看到我们已经创建了一个饼图。如下图：单击创建好的图表，此时点击设计标签，在这里我们可以对图表的布局和样式进行选择，或者修改选择的数据等。通过 Excel 2019 新的样式，我们可以简单的设计出漂亮的图表。如下图：252.2 图表的修改我们经常可以看到那种有一部分同其它的部分分离的饼图，这种图的做法是：单击这个圆饼，在饼的周围出现了一些句柄，再单击其中的某一色块，句柄到了该色块的周围，这时向外拖动此色块，就可以把这个色块拖动出来了；同样的方法可以把其他各个部分分离出来。或者我们在插入标签中直接选择饼图下拉菜单，选择分离效果即可。如下图：把它们合起来的方法是：先单击图表的空白区域，取消对圆饼的选取，单
