|
前 言 大家好，我是一丁老师。最近看了网上不少关于Excel知识小视频，有一种冲动，就试着把这些年应用Excel知识的体会写下来，与各个同仁分享。 本套课程作为一个完整的知识体系，适合操作Excel一年以上的办公室人员学习，也就是说学习这套课程的朋友应该掌握了关于Excel的基本知识，至少包括熟悉Excel的基本界面、工具栏的基本操作如数据的输入、复制、粘贴、筛选、汇总。 这套课程以WPS2019为操作平台，并兼顾讲解微软表格在操作中的不同之处。我相信，如果能真正消化本套课程，扎实基础，将有关Excel的知识串起来，能够解决我们工作中遇到的至少80%与数据相关的问题。 为了便于学习和交流，特编写了讲义的wold版本，在wold版本中详细地介绍了操作方法，大家可以下载学习。本讲义分两部分编写，第一部分编写了前10讲，第二部分编写了14讲，共24讲，并制作了24讲的视频讲解资料。 第一讲：Excel的主要功能和实现路径概述 我们学习Excel，首先要知道Excel是干什么用的，如何才能实现这些功能，顺着这个思路，能更好地将Excel的知识点串起来，避免知识的碎片化和学习的低效率。 Excel的主要功能和实现路径为：
用Excel开发应用系统，就是用一套表格（包括若干个工作表）实现数据的录入、传递、自动更新功能，最后形成我们所需要的表格或图形。 一是设计应用系统的操作界面，这个界面应该简洁、明晰，可理解性强，能够实现某种功能的自动进入和退出。 二是通过公式实现数据的计算、提取并能动态更新（或者说自动更新）。公式和数据导入能实现数据的动态更新（实际上公式就能实现数据的导入功能）；对于透视表，要通过刷新或者关闭文件之后再打开之后才能实现更新，不能算作完全的动态更新。 三是通过“链接”功能，实现表格跳转。表格的跳转，能够将许多数据信息放在不同的工作表中，这样把这些数据既独立又联系的组合为一个整体，能达到操作简便，提高工作效率的目的。 四是通过Excel自带的VBA宏语言，自行编写和开发一些满足自身管理需要的应用系统。开发Excel的应用系统中，大部分的都只需要应用Excel自身的功能，很少用到VBA宏语言，在此把它列为第四点。 任何自行编制的程序，都需要通过实际例子的检验，通过不断的调整完善，才能成为真正可靠的工具。我们要尽可能地利用Excel自身的功能，来达到我们的目的。 为什么把Excel的主要功能和实现路径作为第一讲？Excel是一个软件，我们要掌握一个软件，首先是要了解这个软件可以用来干什么？以及如何来实现这些功能。真正的关于Excel的教材，开始讲的就是其功能。 任何一门知识，其功能和实现的路径都是整个知识体系的“四梁八柱”的东西，就是这些梁和柱，将其具体的内容编织在一起。作为一个学习Excel不深的人，看到我在这里讲的功能和实现的路径，会感到比较陌生，会觉得这个东西比较空泛，有些名词还感觉枯燥难懂。当我们学完了这一整套知识，再回过头来看这些东西的时候，应该有一种恍然大悟的感觉，会进一步加深对知识结构和知识内容的理解，达到知识系统化的目的。 第二讲：基础数据表的编制方法 基础数据表的编制，是Excel中最基础的工作，它就像是建筑的地基。在基础数据录入后，利用Excel自身功能管理这些数据时，应该会很方便，如果还要进行许多繁杂的操作，来满足工作需要，其根源在于基础数据表设计不合理，数据录入不规范，这里用专门的一讲来讲基础数据表的编制方法。 第一、Excel的“三表”概念 如果你有心的话，你会发现，Excel默认的新建工作簿时包含的工作表数量就是3。可以看出，其实“三表”这个概念，是被微软Excel团队承认的。 那这“三表”指的是哪三个表呢？ 他们是基础数据表（数据源表）、参数表、报表，也有叫做记录表、参数表、汇总表的，意思都是一样的。 基础数据表是记录最基础最原始数据用的。参数表（也就是下拉菜单）是用来提高基础数据的录入质量用的，它能保证同一事项表述为同一字符。Excel识别的是字符，不同的字符，尽管其表达的含义相同，但会识别为不同的内容，这样会给我们分析汇总数据带来影响。如“华中科技大学”和“华科”都用来表达同一所大学名称的，但Excel会认为他们是两所不同的大学。在基础数据表中，特别是对分析数据有影响的字段，要使用下拉菜单的形式输入。报表是基于基础数据表而形成的分析结果。 在工作中，应将这三种不同功能的表格放在不同的工作表上，就是为了避免操作不同的表格时相互带来负面影响，也让我们查看不同的表格时感觉爽目。 我们在这里反复提到一个概念，就是“数据”，我们回顾一下这个重要概念。 在Excel中，数据包括：文字、数值、日期、时间、公式和函数，也就是说，Excel中的数据概念比起我们通常理解的数据概念，包括的内容要广得多。 就是数值这个概念也比我们通常理解要广，其包括0到9组成数字和特殊字符：+，—，（），/，%，$，.，E，e中的任意字符。 第二、编制基础数据表应遵循的几个原则 第一个原则：一致性原则 一致性原则要求表格内、表格之间的字段名称、数据类型、表格结构格式要保持一致，具体来讲就是三个要求：同物同名称，同表同格式、同列同格式。 同物同名称，就是说同一对象要使用同一名称，以便数据统计和表格间数据的引用。在“若航公司合同登记台账”表格中“安徽新世纪电子有限公司”与“新世纪电子有限公司”实际为同一公司，但写成了两种名称，对于Excel来说就是两家公司，在用数据透视表进行分类汇总时就会出现错误。 好用，Excel财务高手\全稿数据源\第1章\1.2\1.2.5掌握三类表格的设计要求\立航公司订单统计.xlsx 同列同格式：同一列应该保持同一格式，不能某列的一些行设置成文本格式，其他行设置数值或者日期等格式。表格内出现数据带单位，计算时将作为文本数据被忽略，只有数据格式保持一致才能得出正确的计算结果。 我们通常意义上的数值，在Excel中既可以设置成数值，也可以设置为文本。表格的初始格式是将数值（也就是0到9组成的数字）设置为常量或数值，将非数值文本设置为文本，将标准的日期设置为日期格式。 同表同格式：相同的表格其表格结构和格式必须保持一致，以方便公式或函数对数据进行管理。特别是同一类别的表格，由不同的部门或者人员统计填表时，必须制定统一的格式模板，方便最后数据的汇总。 好用，Excel财务高手\全稿数据源\第1章\1.2\1.2.3表格设计原则-统一意识\同列同格式.xlsx 第二个原则：数据矢量化和颗粒化原则 矢量化：指的是如果数据中包含数量的含义，就应该尽量将这些含义具体量化，不要以文字或其他模糊的方式描述这些数量（比如半年、三个月、一倍都是错误示例），不能将数据的格式设置为文本。 颗粒化：指的是每个单元格只存放单个有效数据，不要将数值与文字粘连。 非矢量化非颗粒化的数据无法进行数据的统计和分析。 第三个原则：便于将表格区域转换为“表”的原则 这里提出了一个概念，就是“表”，也有的称为“超级表”，“表”和我们普通表比较起来，有许多优点。关于“表”的知识，我们在下一讲进行专门的讲解。要将普通表转换为“表”，需要遵循下列规定：
第四个原则：列字段要满足我们统计分析的需要 一般地讲，我们需要对什么内容进行分析汇总，就将该内容作为列字段的内容。我们可以根据工作的需要添加列。 第五个原则：不要将基础数据表分布在工作表中的不同区域 将基础数据表分布在工作表中的不同区域，有时是这些区域隔行，有时是隔列，这样不利于数据的分析和汇总，我们对这样的表格进行适当的改造，将分散的表统一到一个区域上来。（以2020年和2021年的21世纪不动产昌盛区域代理的工资表为例，进行讲解） 02 优居区域代理视频素材改动版（2020年）.xlsx表12 在表12中，最后面的一个工资表添加了月份一列，就可以把全年的工资表放到一张工作表上了，这样便于数据的分析和汇总，如对某个人全年的工资进行汇总，对各个月的工资发放情况进行比较等。如果要打印某个月的工资表，只需要筛选当月的月份，就可以打印某个月的工资表。
在实际工作中，我们的基础数据表往往不是我们自己编制的表格，可能来源于其他人，也可能是从系统导出的，这些基础数据往往不规则，需要我们对它进行整理。 其一、不规范文本的整理方法 不规范文本的表现形式有文本中含有空格、不可见字符、分行符号等。对于空格、不可见字符，是无法用眼睛观察出来的，但在进行计算、查找、筛选时，会引发错误。 好用，Excel财务高手\全稿数据源\第3章\3.2\02 不规范文本的整理技巧.xlsx 一种方法是采用查找替换的方法，将空白符“ |”（即输入空格键后，再输入“|”，这个符号在backspace键的下面）替换为空白。 一种方法是采用函数的方法去掉空格符。Trim函数是去掉字符的尾部空白字符，substitute函数只能替换掉字符中的空格，要去掉单元格中的空格，需要将这两个函数嵌套起来用，比较麻烦。 最优的一种方法是将Excel表格粘贴到wold中去，然后再从wold中粘贴回来。Wold是不认可空格、不可见字符、分行符号的，我们利用这种功能能很好的处理这些不规则的字符。 其二、不规范日期的整理技巧 在Excel中必须按指定的格式输入日期，Excel才会把其当作日期型数据，否则会理解为不可计算的文本，输入以下4种日期格式，Excel均可识别： 一是以短横线“-”分隔的日期，如“2017-4-1”、“2017-5-3”； 二是用斜杠“/”分隔的日期，如“2017/4/1”、“2017/5/3”； 三是中文年月日输入的日期，如“2017年4月1日”、“2017年5月3日”： 四是使用包含英文月份或英文月份缩写输入的日期，如“April-1”、“May-17”。 其他符号间隔的日期或数字形式输入的日期，如“2017.4.1”、“2017\4\1”、“20170103”等，Excel无法自动识别为日期数据，而将其视为文本数据。对于这种不规范的数据该如何处理？要根据具体情况来选择不同的处理方法。 类似于“2017.4.1”、“2017\4\1”这类不规则的数据，我们可以通过使用查找和替换功能，将“.”或“\”替换为“-”或“/”即可。 类似于“20170103”这类数据日期可以使用分列功能快速批量处理。其操作步骤是，首先我们点击菜单栏的“数据”，然后点击“分列”，进入文本分列向导对话框，选择分隔符号，然后选择“下一步”，在第二步中什么都不做，继续点击“下一步”，在第三步中将格式设置为日期格式，在日期格式后面有一个下拉菜单，我们在下拉菜单中选择YMD（Y,M,D分别是年year月month日day英文单词的第一个字母,表示按照年月日的顺序进行排列），再点击完成，即可。 下面我们举个例子来说明分列的运用： 好用，Excel财务高手\全稿数据源\第3章\3.2\03 不规范日期的整理技巧.xlsx 值得注意的是，如果自定义单元格的格式为“0000-00-00”的方式，虽然我们显示的形式是Excel认可的时间模式，如把19000102通过格式定义的操作变为“1900-01-02”，但Excel不认可其为时间，它仍然把它看作是文本，是文本，就不能参与有关时间的计算，如计算年龄，提取出生年月，都无法实现。如果用手动的方式直接输入1900-01-02，Excel会认可其为时间。 第三讲：“表”（上） “表”的概念是全套课程中贯穿始终的，不管是基础数据表、透视表还是图表都与这个概念相关。将普通表转换为“表”，不仅能够起到美化表格的作用，还赋予它许多新的功能。
在Excel中，工作表、区域、“表”是三个不同的概念。 当我们新建立一个工作簿的时候，里面默认的电子表格是3个，并分别起名为sheet1、sheet2、sheet3这三个表，我们称为工作表，它们是存放Excel数据的平台。 区域，是指一个工作表上的一个范围，这个范围可能有数据，也可能没有数据。 “表”，是将工作表中的一个填充了数据的区域设定为一个表，当Excel把一个区域认定一个“表”的时候，会赋予这个表许多功能。 第二、将一个区域转换为一个“表”的方法 将一个区域认定为一个“表”的路径为：点击菜单栏的“开始”，再点击其工具“表格样式”，根据我们的喜好选定表格样式，进入套用表格样式对话框，在WPS表格中，在此对话框中，不能选择“仅套用表格样式”（这是原来对话框的默认选择），而要选择“转换成表格，并套用表格样式”，再点击“确定”。这时在菜单栏中会有“表格工具”这个菜单，并在其相应的工具栏提供了许多功能。至此，表明我们将区域转换为了“表”。 微软表格中，在套用表格对话框中，没有是否仅套用格式的选择，直接点击“确定”就可以了。这时菜单栏出现“表格设计”这个菜单。 将一个区域转换为“表”以后，会给这个表取一个名称，一般其初始名称为“表1”、“表2”，这个名称就代表这个区域。我们也可以更改这个名称的名字。更改的方法是，点击菜单栏的“表格工具”（WPS表格）或“表格设计”（微软表格），再点击其工具栏最左边的“表名称”下面的方框，就可以更改了。 对于已经形成的“表”，“表格工具”下的功能键“镶边行”的作用是使相邻的两行数据显示不同的颜色，“镶边列”的作用是使相邻的两列数据显示不同的颜色，以增加阅读性。对于微软表格而言，这种功能更显著，但对于WPS表格而言，主要通过表格样式来实现这种功能，当我们的表格样式是针对行标志不同的颜色，应该勾选“镶边行”，是否勾选“镶边列”的作用不大。基于增加阅读性，应用“镶边行”或“镶边列”的功能，应该选择表格样式中颜色较深的样式，这样颜色对比度更高，阅读爽目。 第三、将一个区域转换为“表”的时应注意的问题
4、表中不能有斜线表头和空白行、空白列。 第四讲：“表”（下） 大家好，我是一丁老师。这一讲，我们继续讲解“表”，或者说是超级表。在上一讲，我们讲了与“表”有关的三个问题，下面我们讲解与表有关的第四个问题和第五个问题。 第四、“表”区别于“区域”的特色功能 “表”区别于“区域”具有许多特色功能。 销售表（表和透视表）.xlsx 一是区域选择和区域拓展功能。“表”不需要选择区域，当我们把光标放在“表”中任一个单元格的时候，Excel默认为选择全部区域；当我们不隔行的添加行或者不隔列的添加列时，“表”会自动地将这些行或列拓展为全部区域。只要我们在表格中相邻列添加列字段，并按回车键，表格就会自动地向下添加一列。 “表”的自动区域拓展功能，极大地方便了我们的表格制作。我们在制作表格时，可以先画一个行数和列数都很小的表，如4-5行和列，然后转换为“表”，再根据需要直接不隔行地添加行或列，录入数据即可。表格会自动地添加网格线（此时工具栏的镶边行，镶边列前面的小方框应打上√），自动地将新添加的行或列，设置成与前面格式相同的行或列。 采用插入的办法在工作表的左边添加列，“表”不会将左边的插入列自动拓展为“表”的一部分。 二是同时提供筛选和排序功能。点击列字段上的倒三角符号，在接下来的对话框中，这个对话框既提供了排序功能，又提供了筛选功能。 三是提供了便捷的统计功能。勾选“表格工具”下的功能键“汇总行”，会在表格底部加入一行，并且自动汇总出最右边的数据，点击底部汇总行的其他单元格，这些单元格的下拉菜单会提供“平均值”、“计数”、“求和”等功能，我们可以针对不同的列，选择不同的汇总方式。 如果我们应用了筛选功能，汇总行可以统计出筛选后的数据汇总值。在“表”中执行汇总的函数是SUBTOTAL读作 [ˈsʌbˌtəutəl] ，这个函数的意思是对可见单元格进行汇总，实际上就是对筛选出来的单元格进行汇总。 如果我们去掉汇总行，继续无间隔地（不能空行）向下添加行，“表”在执行相关功能的时候，会把这些添加的部分，视为“表”的一部分。 四是上下并排的表可同时筛选排序，互不影响。如果在一个工作表里有两个区域都有数据，而且把这两个区域都变成了“表”，我们可以分别针对这两个表进行排序筛选操作，互不影响。 五是添加计算列。我们可以根据需要在表的相邻列（不能隔一列）输入公式，形成对原表数据的计算，这时的“表”会把这一列包括到“表”中来，这一列也可以应用“表”的功能了。 在微软表格中这个公式具有向下自动填充功能，而且这个公式的表达形式也具有特色，它把对单元格的引用汉字化，增强了公式的可阅读性，如“=[@销售金额]/[@销售价格]”来表达销售数量公式。但在WPS表格中，没有向下自动填充功能，需要采取拖曳的方式向下填充。 例如：销售表（表和透视表）.xlsx中的销售记录表I列的公式。 六是提供删除重复项功能。这里的重复项，指的是两行单元格的所有内容相同，假如两行表格各有5个单元格，这5个单元格的数据都在同一列内相同。 其删除重复项的步骤为：在表格转换为“表”以后，将鼠标放在表中的任意一个单元格，在菜单栏就会出现“表格工具”菜单，在此菜单下有一个工具叫“删除重复项”，点击这个工具，进入“删除重复项”对话框。根据需要请选择一个或多个包含重复项的列，或者选择全部，就可以确定是否有重复项了，点击“确定”，就可以删除重复项了。 显示重复项的方法：要找打那个重复项，最简单的操作方法是，第一，在表格的最右边添加一列，用连接符号将一行中所有单元格的数据连接起来，再向下拖曳，形成一列。第二，将表格还原为区域。第三，点击“数据”菜单下的“重复项”功能键，框选添加的一列，选择“高亮度重复项”，就可以看到重复行的数据了。 七是基于“表”形成的透视表和图表。我们在原“表”添加行或列以后，透视表能够自动刷新，当然自动刷新要进行相关的操作，其具体方法我们在下面讲解透视表时再讲。 第五、“表”中选择列或行的方法 一、选择一列数据而不选择整列的原因。不管是普通工作表还是我们这里讲的“表”，选择或者移动行和列时，应该遵循规范的方法。 直接移动表格中行或列的方法，会给表格的使用带来许多不利的影响，一是这样的操作会使表格所占的内存变大，导致打开缓慢。二是选择移动整列会导致这个表格以外的数据跟着移动，当我们的表格下面还有其他表格的时候，会破坏其他表格的结构。三是选择移动整列的操作会破坏我们当前“表”的结构，导致许多功能不能使用。当然，这种操作还会带来其他的一些负面影响，使工作表在运用中出现一些非正常的情况。 二、选择并移动整列数据的方法。选择要移动列的数据的方法是，将鼠标移到该列标题行顶部单元格划线的位置，这时鼠标变成了一个黑色的向下方向键，点击鼠标左键，即可选定该列的数据。这时我们可以看到选择的区域，不包括该列的标题，在鼠标变成了一个黑色向下方向键的同时，再次点击鼠标左键，就包括该列的标题了。我们将鼠标放在此列的边框线上，当鼠标变成四个方向键图标时，拖动鼠标，就可以移动这一列了。 选择不包括标题行的数据区域，便于我们根据需要对数据区域进行操作，如同时加上一个数，修改数值金额格式等等。
如果我们选择的多列需要和列标题一起移动，应该在选择的起始列时，就将标题和内容一起选定（选定方法同前面讲的选定单列的方法一致），再拖动需要选择更多的列。 这样做的前提是，要选定的列没有内部的区域框选。 移动行和选择多行的方法，与移动列和选择多列的方法类似。 第五讲：下拉菜单 我们在前面讲解基础数据表的时候，说到过下拉菜单是用来规范基础数据的录入的，它还能提高我们录入数据的速度，下面我们来介绍下拉菜单的相关知识。 在微软表格中，称下拉菜单为数据的有效性。 建立下拉菜单讲解表格.xlsx 第一、用手动输入的方法建立下拉菜单 用手动方式建立下拉菜单的路径与方法是： 其一，单个表格下拉菜单的设置方法。首先选定单元格，点击菜单栏上的“数据”，再点击其工具栏“下拉列表”，进入插入下拉列表对话框，在光标闪动处分行输入需要的内容，在输入第二行时，点击右上角的“+”号，输入完成后，再点击“确定”。 其二，批量表格下拉菜单的设置方法。将设置有下拉菜单的单元格向下拖曳填充，填充的表格也会有下拉菜单；将设置有下拉菜单的单元格向右拖曳，填充的表格也会有下拉菜单；在表格中插入行，这些插入的行相应的单元格也会有相同的下拉菜单。 我们还可以选定一个区域，针对这个区域设置下拉菜单，其设置下拉菜单的方法和单元格设置下拉菜单的方法一样，不再需要进行拖曳填充，区域中的每一个单元格都会设置相同的下拉菜单。
采用导入的方式建立下拉菜单，直观性强，操作简单。 首先提前准备好下拉菜单表格（我们的这个表格可以适当留有空白，当我们框选包括空白单元格在内的区域作为下拉菜单时，这些空白单元格可以在今后添加内容，但这个区域只能是一行或一列，不能是多行或多列）； 接下来选定要设置下拉菜单的区域或单元格； 再依次点击菜单栏的“数据”，进入其工具栏“下拉列表”，在插入下拉列表对话框中，选择“从单元格选择下拉选项”（而不是选择“手动添加下拉列表”），这时鼠标会在“从单元格选择下拉选项”下面的一个长方格中闪动，再框选提前准备好的下拉菜单表格； 最后点击“确定”。
以上讲述的建立下拉菜单方法，都是在WPS表格中适用的方法。在微软表格中建立下拉菜单的方法，和其相似，具体的操作步骤是： 首先提前准备好下拉菜单表格（我们的这个表格可以适当留有空白，当我们框选包括空白单元格在内的区域作为下拉菜单时，这些空白单元格可以在今后添加内容，但这个区域只能是一行或一列，不能是多行或多列）； 接下来选定要设置下拉菜单的区域或单元格； 再依次点击菜单栏的“数据”，进入其工具栏“数据验证”，再点击这个工具的下拉菜单“数据验证”，进入“数据验证对话框”，在设置页面，设置验证条件，在“允许”下面的方框中，点击其下拉菜单，选择“序列”。再将鼠标放置在“来源”下面的方框中，框选提前准备好的下拉菜单表格； 最后点击“确定”。 如果下拉菜单的内容很简单，如下拉菜单为表达性别的“男”、“女”，我们可以不提前准备下拉菜单，直接在“来源”下面的方框中输入“男，女”，类似于WPS表格中的手动输入下拉菜单。 第四、下拉菜单的复制方法 下拉菜单的格式可以复制到其他表格的单元格里，可以直接复制，如果源单元格有内容，可以点击目标单元格，再点击鼠标右键，选择“清除内容”选项，或者按下“delete”键清除内容。有的版本使用仅粘贴格式的方法可以粘贴下拉菜单，但有的版本不行，要使用我们在这里讲到的方法。 第五、下拉菜单的修改方法 采用导入方式设置下拉菜单，要修改下拉菜单时，我们可以通过修改先前准备好的下拉菜单列表的方法来进行修改，我们修改的范围需要在当初框选的下拉菜单范围内，这种方法较之手动方式添加下拉菜单列表的修改更为简捷。 采取手动的方式输入的下拉菜单修改步骤是： 1、点击菜单栏中的“数据”； 2、点击“数据”菜单下的子目录——“下拉列表”； 3、点击已经设置了下拉菜单列表的单元格； 4、在插入下拉列表对话框中，点击右上角的写字图标； 5、点击需要修改的内容，并进行修改； 6、勾选对有同样设置的单元格应用这些更改。 第六、清除下拉菜单的方法 先选定需要清除下拉菜单的区域，再执行如下操作： 1、点击菜单栏中的“数据”； 2、点击“数据”菜单下的子目录——“下拉列表”； 3、点击已经设置的单元格或区域； 4、在插入下拉列表对话框中，点击"全部清除"。 在微软表格中，修改和删除下拉菜单的方法和WPS表格类似。 第六讲：数据透视表（上） 数据透视表是根据选定的数据源生成的，可以动态改变其版面布局的互交式汇总表格。数据透视表不仅能够按照改变后的版面布局自动重新计算数据，而且能够根据更改后的基础数据或数据源来刷新计算结果。 第一、创建数据透视表时，对基础数据区域的选择 我们在前面说过，“表”的概念是我们这套课程的一个核心知识点。我们对数据透视表的讲述，也是以“表”为基础进行展开。当我们的基础数据区域已经转换为“表”，我们把鼠标放在“表”内，实际上就选定了整个基础数据区域，不需要再去选定区域了。不能将光标置于表格顶部，或拖动光标选择整列的方法来选定整个区域，这样会给应用Excel的其他功能带来不利影响。 在有的微软表格中，在创建数据透视表对话框中，需要输入“表”的名称，这个名称是当初我们将区域转换为“表”时，软件给我们“表”的命名。找到这个名称的方法是，点击“表”中的任意一个单元格，点击菜单栏下的“表格设计”，点击工具栏最左边的工具“表名称”，就可以找到这个表的名称了，我们在将区域转换为“表”时，可以给这个“表”取一个个性化的名称。 如果我们要更改基础数据区域的选择，可以把鼠标放在已经形成的透视表中，点击工具栏中的“数据透视表”，会显示一个“更改数据透视表数据源”的对话框，在这个对话框中，可以修改数据透视表所显示的区域。 第二、创建数据透视表时，对放置区域的选择 生成的透视表放在哪里？Excel为我们提供了三种放置区域的方案。在创建数据透视表对话框中，可以根据需要进行选择。 第一种是放置到基础数据表中。在请选择放置透视表的位置中，选择“现有工作表”，我们可以点击和基础数据表同一个工作表中的一个单元格，作为透视表的起始区域。一般我们不选择这种。选择这种放置方式是违背我们前面讲到的“三表思想”的。选择这种放置方式，一般用在演示讲解稿中。 第二种是放置到Excel重新生成的一张工作表上。在请选择放置透视表的位置中，选择“新工作表”，但是这一张新工作表是Excel自动生成的，在工作簿中工作表较少时可以使用。 第三种是放置到我们选定的工作表上。在请选择放置透视表的位置中，选择“现有工作表”，可以选择我们指定工作表中的一个单元格，作为透视表的起始区域。在许多工作表组成的工作簿中，往往各个工作表的内容都是事先指定的，放置到我们指定的工作表，便于我们对全局的掌控。 我们要正确理解“现有工作表”的界定，基础数据所在的工作表和已经出现在工作簿中的工作表都是“现有工作表”。 06 优居区域代理视频素材改动版（2021年）.xlsx讲义工作表 第三、字段的选择、母字段和子字段的关系，更改字段标题 1、字段的概念。这里首先讲一个概念，就是字段。字段是基础数据表中行或者列的标题，它是对行列数据内容的概括。 2、拖动字段的方法。在“数据透视表”对话框中，我们可以将“字段列表”下面的字段，拖动到数据透视表区域中，形成不同的数据透视表。一般地，我们把反映期间或者日期的字段拖到这个对话框的筛选器方框中，把需要按照行分类的字段拖到行方框中，把需要按照列分类的字段拖到列方框中。如果我们要调整这四个方框中的字段，可以将方框中不需要的字段从方框中拖出，将需要的字段重新拖入。 3、母字段和子字段。在“数据透视表”对话框中，我们如果拖动两个不同的字段到行，意味着形成的报表会依据这两个字段进行分类，对话框列的字段中，放在前面的字段是我们报表行分类的第一依据，放在后面的字段是报表行分类的第二依据（也有的把放在前面的字段称为母字段，把放在后面的字段称为子字段）。上下变化字段在行中的位置，可以改变报表分类的依据。在对话框列字段中放置两个字段的情形，也与之类似。 在一般表格中，既显示文本，又显示时间；既显示类别，又显示加盟商；在财务上既显示大类，又显示大类下的小类，都是母字段、子字段在具体事例中的应用。在应用母字段和子字段功能时，要注意到我们是母字段数据来源于子字段的数据。我们在编制基础数据表时，可能会出现有的母字段有子字段的分类，有的母字段没有子字段的分类，我们应该将母字段包含的字符设置为子字段包含的字符，才能保证数据的正确性。 06 优居区域代理视频素材改动版（2021年）.xlsx表B-1 4、在形成的数据透视表中对行（或列）字段包含的内容进行筛选。在透视表中，行字段和列字段都是基础数据表中文本内容的概括，将行字段拖入行以后，我们会发现这些汇总的行或列，有些的我们不需要的。我们可以打开透视表行、列交叉处的排序筛选图标，将我们不需要的行文本内容，“空白”和“0”前面方框中的“√”去掉，也就是去掉这些选项，留下我们需要的选项。对于列字段包含的内容可以使用同样的方法，进行筛选。 5、数据透视表标题的更改。在形成的数据透视表中，可以更改字段标题。如将“总账科目（贷方）”更改为“收入类别”，直接点击数据透视表该字段所在的单元格进行修改，即可。 第四、更改数据透视表中的数据显示格式 点击数据透视表中的某一个单元格，再点击鼠标右键，点击“数字格式”，进入“单元格格式”对话框，在这里可以选择数据的格式，而且数据透视表中的所有数据都会显示这种格式。 不要点击鼠标右键，在点击“单元格格式”，进入“单元格格式”对话框的路径来更改数据格式。这样做的结果是，今后数据透视表添加数据的时候，添加的数据没有使用新的单元格格式。 第五、透视表的更新设置与其相对应的明细表的更新 当基础数据表的数据发生了更改或者添加删除了内容，透视表需要进行一定的设置，才能让透视表跟随着基础数据表变化而变化。 一种方法是手动更新法。点击数据透视表的一个单元格，点击鼠标右键，再点击“刷新”，这时数据透视表就随着基础数据表更新而更新了，需要注意的是，使用这种功能的前提是，基础数据已经转换为“表”。 另一种方法是自动更新，其操作路径为：点击数据透视表的一个单元格，点击鼠标右键，点击“数据透视表选项”，点击“数据”，勾选“打开文件时刷新数据”。同一张工作簿中，当基础数据表更改后，再去打开工作簿中的数据透视表，数据透视表不会自动更新，Excel不把这种操作视为重新打开。一般情况下，在运用或者打印数据透视表的数据之前，还是选择手动刷新的方法为宜。 数据透视表更新以后，根据数据透视表生成的明细表不会自动更新，需要重新生成。 我们还可以设置，数据透视表不随着数据的更新调整列宽（往往我们的工作表上放有几个数据透视表，如果某一个数据透视表的列宽发生了变化，会影响其他数据透视表的摆放）。其操作路径为：点击数据透视表的一个单元格，点击鼠标右键，点击“数据透视表选项”，将“随着数据的更新调整列宽”前面的单元格去掉。 第七讲： 数据透视表（中） 这一讲，我们继续讲解数据透视表。 第六、更改数据透视表的统计方式 数据透视表的默认统计方式为求和，实际上数据透视表还提供了其他的统计方式。如：计数、乘积、最大值、最小值等等。其操作路径为：点击数据透视表的一个单元格，点击鼠标右键，选择“值汇总依据”，再根据需要选择相应的汇总方式。 第七、通过数据透视表显示明细表及基础数据表的拆分 我们可以通过数据透视表，直接查找到每一个数据的详细信息。这个详细信息，在财务上就是明细表。其操作路径为：点击数据透视表的一个单元格，点击鼠标右键，选择“详细信息”即可。 基础数据表的拆分，实际上是透视表显示明细功能的延伸。我们框选透视表中按行汇总的合计栏所有数据，再点击鼠标右键，选择“详细信息”，这时就会在此工作簿中显示几个工作表，来放置反映合计栏数据相关的明细内容。实际上就是按行分类字段，将工作表进行了拆分。这样的操作有利于我们同时打印这几个拆分的工作表。 因选择“详细信息”生成的表格，是在本工作簿中生成一个新的表格，这个表格的位置不能由我们指定。这些工作表，一般放置在我们放置透视表的工作表前面。 第八、数据透视表的排序 数据透视表可以对统计的数据进行排序。其操作路径为：点击数据透视表的一个单元格，点击鼠标右键，选择“排序”即可。我们将鼠标放在透视表的哪一列，就以哪一列为依据进行排序。 我们还可以用手动的方式，将数据透视表中的一行（列）内容进行移动。其操作路径是，选择要移动行（列），将鼠标移到该列标题行顶部单元格划线的位置，这时鼠标变成了一个黑色的向右（下）方向键，点击鼠标左键，即可选定该行（列）的数据（包括字段）。我们将鼠标放在此列的边框线上，当鼠标变成四个方向键图标的时候，拖动鼠标，就可以移动这一列了。这比我们移动“表”中的行或列，要简单点。（手动排序的功能，在wps表格中不支持，在微软表格中支持这种功能） 第九、数据透视表的筛选 数据透视表可以对统计的数据（包括文本和数值）进行筛选。其操作路径为：点击数据透视表行和列交叉的单元格中的排序筛选功能方框，在选择字段对话框中，根据需要选择“标签筛选”或“值筛选”，再根据需要选择各种不同的方式。 标签筛选，是针对文本而言的筛选，可以实现针对行字段中的内容进行筛选，如是否包括某些内容，是否以某些字符开头，是否以某些字符结尾等；值筛选，是针对数值而言的筛选，如果有多列求和数据，我们的数值筛选是针对总计列而言的。 第十、数据透视表的第二次运算（比率和差异性分析） 在数据透视表中，提供了许多第二次运算方式，主要是各种百分比和差异，反映某一个数据在全局中的比例或与总计数据的差异等等。其操作路径为：点击数据透视表的一个单元格，点击鼠标右键，选择“值显示方式”，再点击其相应的下拉菜单即可。这种功能，可以让我们在提供求和数值以后，再用一张表来反映各种比例值。 第八讲： 数据透视表（下） 这一讲，我们继续讲解数据透视表。 第十一、调整数据透视表的样式和汇总行 1、样式调整。在“报表布局”中，为我们提供了如下几种形式的报表：以压缩形式显示；以大纲形式显示；以表格形式显示；重复所有项目标签；不重复项目标签等等，我们一般选择“以表格形式显示”。 其操作路径为：点击数据透视表的一个单元格，点击菜单栏的“设计”，再点击其工具栏的“报表布局”，再根据需要点击报表布局形式。 对于涉及到母字段和子字段的透视表，改变母字段和子字段在“数据透视表”对话框中的相对位置，即将母字段移到子字段的下面，其实质是将母字段变为子字段，将子字段变为母字段，这时报表的排列方式会发生改变，但这种改变和我们这里讲的调整数据透视表的样式，不是同一个概念，它不需要用到报表布局功能。 2、分类汇总的设计。在“设计”菜单栏下，有一个功能键，叫“分类汇总”，这个功能键按照行字段的类别，为我们提供了如下几种形式的分类汇总方式：不显示分类汇总，在组的底部显示分类汇总，在组的顶部显示分类汇总。 06 优居区域代理视频素材改动版（2021年）.xlsx 第十二、针对数据透视表的日期或数字进行分类统计 我们要对基础数据表的日期按月进行分类汇总，一个思路是在基础数据表添加辅助列为月份（提取字符形成月份），再针对月份这个字段进行汇总，但这样操作起来工作量比较大。还有一种方法可以更简单：点击透视表中的一个日期单元格，点击鼠标右键，点击弹出来的菜单中的“组合”，选择“月”，再点击“确定”。如果选择“季度”，可以按季度进度分类汇总；如果既选择月份又选择季度，可以实现在月份汇总的基础上再按季度汇总。 点击鼠标右键，点击弹出来的菜单中的“取消组合”，可以将月份或者季度汇总返回到日期明细或者数字明细。 我们要对求和列，分区间进行分类汇总，如分为0—1000,1001—2000,2001—3000，进行分类汇总。我们可以将数值先拖入到行方框，再拖入到求和方框，让行方框和求和方框都有数值。点击透视表中行字段的一个数值单元格，点击鼠标右键，点击弹出来的菜单中的“组合”，在“组合”对话框中设置“起始于”、“终止于”、“步长”的值后点击“确定”，即可。起始于的值初始设定为求和值的最小值，终止于的初始设定为求和值的最大值，一般情况下我们不需要更改这些初始设置，步长值我们可以根据需要修改。 这种方法对数据进行分类求和，是对相同的间隔区间的数据进行求和。如果把透视表中的值显示依据由“求和”变为“计数”，就可以求出每一区间的数据个数，常用来求出学生某个分数段的人数。 取消分类汇总的方法是，点击行字段的一个数值，再点击鼠标右键，选择“取消组合”，即可。 销售表（表和透视表）.xlsx 第十三、用切片器控制透视表 在透视表对话框中，有一个方框叫做“筛选器”，但筛选器只能控制一个透视表。切片器能够同时控制多个透视表，实现多种形式的表格汇总，减少工作量。 用切片器控制多个透视表的方法是： 一是进入切片器，确定控制字段。这个字段和我们筛选器中的字段作用是一样的。将光标放在一张透视表上，点击菜单栏的“插入”，再点击下面工具栏的“切片器”，进入“插入切片器”对话框，选择相应的字段。 二是确定要控制的透视表。点击切片器的下拉菜单“报表连接”（也有的版本叫做“数据透视表连接”），进入“数据透视表连接”对话框，选定透视表，关闭此对话框。 三是运用切片器实现对透视表的控制。进入插入的切片器，单选或多选切片器中字段包括的内容，就可以用切片器控制多个透视表。假如切片器的字段是月份，我们选择“一月”，透视表就可以显示一月的数据；我们同时选定一月和二月，透视表就可以显示这两个月的合计数据。 切片器只能针对一个表格生成的不同类型透视表进行操作，不能控制多个表格生成的不同透视表。 06 优居区域代理视频素材改动版（2021年）.xlsx 第十四、数据透视表的复制和移动 数据透视表的复制和移动，是两个不同的概念。框选已经形成的透视表，点击鼠标右键，单击复制菜单，可以将透视表复制到另外一个地方。但这个复制的透视表，是普通的表格，如果要让复制的透视表具有透视表的功能，应该采取移动工作表并建立副本的办法来复制数据透视表。 点击数据透视表的一个单元格，点击菜单栏的“分析”，点击其下的功能键“移动数据透视表”，进入“移动数据透视表”对话框，点击我们要重新放置数据透视表位置的起始区域，就改变了原来放置数据透视表的起始区域，达到了移动数据透视表的目的。 第十五、数据透视表的删除 点击数据透视表的一个单元格，点击菜单栏的“分析”，点击其下的功能键“删除数据透视表”，就可以数据透视表了。 也可以选择已经形成的数据所在行，用删除行的办法来删除数据透视表。 第十六、数据透视表的功能小结 数据透视表把表格制作、筛选、分类汇总、数据的比率和差异性分析等几项功能统一起来了，并且能够实现快速刷新，能满足绝大多数情况下对于数据分析的需要。 其筛选是通过点击某一项数据的汇总值，自动弹出其明细而实现；分类汇总，是需要将汇总的字段列入透视表而实现，而且这种汇总在显示具备结果的同时，还反映了各项汇总的整体情况；数据比率和差异性分析，主要是通过“值显示方式”来实现。 网上有许多关于数据筛选、分类汇总、数据计算和分析的小视频，但它们只是从局部来分析，而不是作为一个整体来分析。从这点上讲，它们都不及数据透视表全面，从深入的角度，它们可能比数据透视表研究得更深。Excel作为一种工具，能够满足我们快速全面地进行数据分析，我们没有必要就工具去研究工具。数据透视表满足了我们提高工作效率和解决问题的需要，是一种简洁实用的工具。 第九讲：数据透视表与图表 图表是数据形象化的表示，图表往往给人高大上的感觉。图表作为Excel的一项重要功能，我们将这部分内容作为单独的一讲。
图表既可基于基础数据表生成，也可直接基于数据透视表生成。直接基于基础数据表生成图表时，要框选基础数据表的区域，而且要求表格中的行字段不重复，只适用于简单的表格。透视表是在基础数据表进行分类汇总后形成的，基于透视表生成的图表，能正确反映我们需要的数量关系，也适合于复杂的基础数据表，从本质上讲，还是反映基础数据表的数量关系，是我们工作中经常采用的方法。 点击已经生成的数据透视表，将光标放在透视表中，再点击菜单栏的“插入”，进入其工具栏，点击“全部图表”，选择恰当的图形，即可形成图表。 06 优居区域代理视频素材改动版（2021年）.xlsxB-1工作表 2、不同图表的功能 一般用柱形图来表示不同系列的比例，用折线图来表示数据随时间变化的趋势，用饼图来显示局部与整体的占比情况。 3、图表在本工作表内的移动 将鼠标放在图表中，当鼠标变成四方箭头的十字架时，我们就可以移动图表了。 4、图表的大小变化 点击图形，这时会发现图形被一个方框框起来了，这个方框的上下边都有3个圆圈，再将鼠标放在图表方框的边角下顶处，当鼠标变为双向箭头时，拖动鼠标可以改变图形的大小。 5、给图表添加标题 将鼠标放在图表上，依次进入“图表工具”、“添加元素”、“图表标题”，可以为图表添加标题。 6、给图表添加数据 将鼠标放在图表上，依次进入“图表工具”、“添加元素”、“数据标签”，可以为图表添加数据。可以根据喜好，选择不同的图例，把数据放在图表的不同地方，如柱形图的上面，柱形图的中间。 7、更改图表颜色 将鼠标放在图表上，依次进入“图表工具”、“更改颜色”，可以更改图表的颜色。 8、更改图表样式 将鼠标放在图表上，进入菜单“图表工具”，下面的各种图示，可以更改图表的样式。 9、移动图表到其他工作表 将鼠标放在图表上，依次进入“图表工具”、“移动图表”，可以将图表移动到另一个工作表的指定位置。 第十讲：名 称 这一讲，我们来讲解名称。 第一、“名称”的概念和使用范围 名称，就是把一个复杂的东西，给它取个名字，让这个名字来代替这个复杂的东西，使复杂问题简单化。我们取的名字，应力求个性化，能反映它所代表的东西。这样便于我们以后在应用名称时，看到该名称，就知道它所代表的对象，特别是在完成表格一段时间以后，再次查阅表格时，会更加感觉到名称取名的重要性。 名称的使用范围，也就是名称所代表的内容，在什么范围内得到电子表格软件的认可。一般设置为本工作簿，也可以设置为工作簿中的一个工作表。点击菜单栏的“公式”，再点击“名称管理器”，进入“名称管理器”对话框，点击“新建”，在“新建名称”对话框中，在“范围”旁边的长方形方框中，点击其下拉菜单，可以设置名称的使用范围。 第二、将一个区域表达为“名称”的方法 将一个区域表达为“名称”的方法是： 1、将一个区域表达为名称的一般方法。点击菜单栏“公式”，点击其工具栏的“名称管理器”，在“名称管理器”对话框中，点击“新建”，进入“新建名称”对话框。在此对话框中，输入适当的名称，选择或默认应用范围，最后框选名称代表的区域，或者在此对话框中的引用位置中输入区域范围的电子地址，如D1:D10。 2、批量设置名称的方法。例如，我们的下拉菜单都建在一个工作表中，每个下拉菜单在首行都有一个标题，可以批量建立名称。 在WPS表格中，进行此设置的方法是，框选包括标题行的下拉菜单区域，点击菜单栏“公式”，点击其工具栏的“名称管理器”旁边的“指定”工具，进入“指定名称”对话框，勾选“名称创建于首行”，即可。 在微软表格中，进行此设置的方法是，框选包括标题行的下拉菜单区域，点击菜单栏“公式”，点击其工具栏的“名称管理器”旁边的“根据所选内容创建”工具，进入“根据所选内容创建名称”对话框中，勾选“首行”，即可（与WPS表格的方法类似）。 3、“名称对话框”中备注的作用，可用来描述这个名称的具体含义。 4、应用区域名称的方法是，在引用这个区域时，直接输入设定的名称即可。当我们输入名称的第一个字符时，系统会自动提示以这个字符开头的名称，避免输错名称。 下面我们举一个例子来说明如何将一个区域表达为“名称”。 将一个区域表达为一个名称.xlsx 我们在前面讲解将区间转换为“表”时，讲过这时Excel会给“表”一个名称，就区域引用来讲，这两个“名称”的含义是相同的，“表”的名称还代表这个区域是“表”，具有特殊的功能。 第三、将一个公式中的整体或片段表达为一个“名称”的方法 给公式取一个“名称”的方法是： 1、点击“名称管理器”，在名称管理器对话框中点击“新建”； 2、在“名称管理器”对话框中的名称栏，给这个公式取一个名字。 3、在“名称管理器”对话框中的引用位置栏，点击设置了公式的一个单位格，将这个单元格的公式写到引用位置栏中（打开名称对话框后，WPS表格不允许采取复制粘贴的办法在这里直接输入公式）；或者先点击设置了公式的单元格，这时编辑栏会显示设置的公式，在编辑栏复制公式，然后按下回车键，让该单元格的公式不再处于编辑状态，再打开“名称管理器”，就可以在“引用位置”栏中复制公式了。 如下拉菜单与VLOOKUP函数的联合应用.xlsx中，给元月工资查找公式取一个名称。 第四、名称在公式中的应用 其一，将一个公式表达为“名称”，其引用单元格表达范围的变化。 下面我们先举一个例子来说明如何将一个公式表达为“名称”。 下拉菜单与VLOOKUP函数的联合应用.xlsx第一张表 K4单元格查找元月份工资的公式为： =VLOOKUP($J$4,$D$6:$F$24,MATCH(K$3,$D$5:$F$5,0),0) L4单元格查找2月份工资的公式为： =VLOOKUP($J$4,$D$6:$F$24,MATCH(L$3,$D$5:$F$5,0),0) L4单元格公式由K4单元格公式向右拖曳而成，仅仅是将“K$3”改为“L$3”，就是说其相对引用功能发挥了作用。如果将k4单元格的公式，设定为一个名称，将这个公式名称向右拖曳，依然可以得到L4单元格的公式。 在原来的WPS表格软件中，名称所对应的公式中，其引用的单元格，是不会随着位置的变化而变化的。各位同仁可以发现，在2021年8月份，WPS的此项功能得到了改进，其引用的单元格，可以随着位置的变化而变化的，给我们设置和应用公式带来了很大地方便，但我们有时需要在另一单元格应用名称所代表的单元格时，应该将其所引用的单元格设置为绝对引用。 我们在这里实际上讲到了一个电子表格中很重要的一对概念，就是“相对引用”和“绝对引用”。简单地说，相对引用，就是所引用单元格的范围，会随着公式的移动而移动，但相对位置不发生变化，我们通常引用的单元格都是相对引用；绝对引用，就是所引用单元格的范围，不会随着公式的移动而发生变化。我们在第十五讲——函数与公式（二），再具体地对这些概念进行详细的讲解。 其二，将公式中的一部分参数表达为一个名称。 我们可以将公式中的一部分参数设置为“名称”，如上面例子中，“$D$6:$F$24”，它表达的是查找区间，我们可以给他取一个名称叫“查找区间”。其方法是，在“名称管理器”对话框中的名称栏输入“查找区间”，在引用位置栏，直接输入“$D$6:$F$24”。 下拉菜单与VLOOKUP函数的联合应用.xlsx第一张表 一般地，跨工作表引用区域，而且这些区域是绝对引用，采用名称的功能来替换参数，可以增强了公式的阅读性，简化了公式，还保证了公式在拖曳时得到我们想要的公式。关于这一部分内容的展开，我们在讲解函数SUMIFS函数和vlookup函数时再讲。 其三，将公式中比较复杂的数学物理公式表达为一个名称。 将公式表达为“名称”的另一个应用是，将公式中比较复杂的数学或物理公式设置为“名称”，如将梯形的面积公式(a+b)h/2，定义为“梯形面积”，就会很方便我们阅读，提高公式书写速度。 其四，“表”与名称定义的区域 我们知道“表”的区域具有拓展功能，当名称定义的区域是“表”的一部分的时候，名称定义的区域不会随着“表”的拓展而拓展。在微软表格中，名称定义的区域可以随着“表”的拓展而拓展。 销售表（表和透视表）.xlsx“表”与名称的应用 其五，下拉菜单与名称的应用 我们在前面讲解下拉菜单时，讲过可用导入的方式建立下拉菜单，将下拉菜中“从单元格选择下拉菜单”的一个区域取一个名字，但用这个名字应用到我们需要引用的区域时，是不可行的。名称是菜单栏“公式”下面的一个功能键，只适用于公式中。 第五、确认、修改、删除名称的方法 确认、修改、删除名称的方法是： 1、确认建立名称的方法。创建名称完成后，我们点击“名称管理器”，进入名称管理器对话框，可以看到已经将我们创建的名称列入其中了。 2、修改名称的方法。点击“名称管理器”，进入“名称管理器”对话框，点击“编辑”可以对名称进行修改。 3、删除名称的方法。点击“名称管理器”，进入“名称管理器”对话框，选中名称管理器中排列的任意一条名称，点击“删除”可以删除名称。对“表”取的名称不允许删除。 |
|
|
