《Excel的功能和实现路径》讲义(第一讲至第十讲)

前 言

大家好,我是一丁老师。最近看了网上不少关于Excel知识小视频,有一种冲动,就试着把这些年应用Excel知识的体会写下来,与各个同仁分享。

本套课程作为一个完整的知识体系,适合操作Excel一年以上的办公室人员学习,也就是说学习这套课程的朋友应该掌握了关于Excel的基本知识,至少包括熟悉Excel的基本界面、工具栏的基本操作如数据的输入、复制、粘贴、筛选、汇总。

这套课程以WPS2019为操作平台,并兼顾讲解微软表格在操作中的不同之处。我相信,如果能真正消化本套课程,扎实基础,将有关Excel的知识串起来,能够解决我们工作中遇到的至少80%与数据相关的问题。

为了便于学习和交流,特编写了讲义的wold版本,在wold版本中详细地介绍了操作方法,大家可以下载学习。本讲义分两部分编写,第一部分编写了前10讲,第二部分编写了14讲,共24讲,并制作了24讲的视频讲解资料。

第一讲:Excel的主要功能和实现路径概述

我们学习Excel,首先要知道Excel是干什么用的,如何才能实现这些功能,顺着这个思路,能更好地将Excel的知识点串起来,避免知识的碎片化和学习的低效率。

Excel的主要功能和实现路径为:

  1. 记录数据。这是Excel最基本最初级的功能,其实现的路径是按照Excel对数据的要求录入数据,制作“表”。Excel中数据的概念要比数学中数据的概念要广得多,不同的数据有不同的录入要求,虽然它属于Excel中基础知识的范畴,但也是容易忽略的地方。这里打引号的“表”,也称超级表,是Excel中和普通表不同的概念。
  2. 管理数据。其实现的路径,一是按照“三表思想”构造工作簿;二是通过“透视表”功能,实现对数据的汇总、筛选、分析;三是通过函数实现对数据的查找、汇总、计算、分析。函数和透视表有一部分功能是重合的,在功能选择上可以优先选择透视表,透视表要比函数来得快捷些。
  3. 数据网上共享。其实现的路径是通过“协作与共享”功能。
  4. 制作图表。可以根据数据表制作各种需要的图表,更形象地反映数据之间的关系。
  5. 开发应用系统。

用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

第二个原则:数据矢量化和颗粒化原则

矢量化:指的是如果数据中包含数量的含义,就应该尽量将这些含义具体量化,不要以文字或其他模糊的方式描述这些数量(比如半年、三个月、一倍都是错误示例),不能将数据的格式设置为文本。

颗粒化:指的是每个单元格只存放单个有效数据,不要将数值与文字粘连。

非矢量化非颗粒化的数据无法进行数据的统计和分析。

第三个原则:便于将表格区域转换为“表”的原则

这里提出了一个概念,就是“表”,也有的称为“超级表”,“表”和我们普通表比较起来,有许多优点。关于“表”的知识,我们在下一讲进行专门的讲解。要将普通表转换为“表”,需要遵循下列规定:

  1. 列标题应为非数据,且列标题不得重复;
  2. 不要使用斜线表头;
  3. 不要使用合并单元格;(当标题行有合并单元格时,我们可以变换列字段的内容,将合并单元格转换为非合并单元格后,与原合并单元格表达相同的意思;当表格中的具体内容有合并单元格时,我们应该将有合并单元格的内容排列在一列,要打印基础数据表时,保留这些合并单元格,要分析汇总基础数据表时,采取移动复制工作表的办法,建立和原基础数据表一样的工作表,然后再删除有合并单元格的列,再基于新形成的表进行数据的分析汇总。)
  4. 不能有空行和空列;

第四个原则:列字段要满足我们统计分析的需要

一般地讲,我们需要对什么内容进行分析汇总,就将该内容作为列字段的内容。我们可以根据工作的需要添加列。

第五个原则:不要将基础数据表分布在工作表中的不同区域

将基础数据表分布在工作表中的不同区域,有时是这些区域隔行,有时是隔列,这样不利于数据的分析和汇总,我们对这样的表格进行适当的改造,将分散的表统一到一个区域上来。(以2020年和2021年的21世纪不动产昌盛区域代理的工资表为例,进行讲解)

02 优居区域代理视频素材改动版(2020年).xlsx表12

在表12中,最后面的一个工资表添加了月份一列,就可以把全年的工资表放到一张工作表上了,这样便于数据的分析和汇总,如对某个人全年的工资进行汇总,对各个月的工资发放情况进行比较等。如果要打印某个月的工资表,只需要筛选当月的月份,就可以打印某个月的工资表。

  1. 不规则数据的整理方法

在实际工作中,我们的基础数据表往往不是我们自己编制的表格,可能来源于其他人,也可能是从系统导出的,这些基础数据往往不规则,需要我们对它进行整理。

其一、不规范文本的整理方法

不规范文本的表现形式有文本中含有空格、不可见字符、分行符号等。对于空格、不可见字符,是无法用眼睛观察出来的,但在进行计算、查找、筛选时,会引发错误。

好用,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会认可其为时间。

第三讲:“表”(上)

“表”的概念是全套课程中贯穿始终的,不管是基础数据表、透视表还是图表都与这个概念相关。将普通表转换为“表”,不仅能够起到美化表格的作用,还赋予它许多新的功能。

  1. “表”的定义

在Excel中,工作表、区域、“表”是三个不同的概念。

当我们新建立一个工作簿的时候,里面默认的电子表格是3个,并分别起名为sheet1、sheet2、sheet3这三个表,我们称为工作表,它们是存放Excel数据的平台。

区域,是指一个工作表上的一个范围,这个范围可能有数据,也可能没有数据。

“表”,是将工作表中的一个填充了数据的区域设定为一个表,当Excel把一个区域认定一个“表”的时候,会赋予这个表许多功能。

第二、将一个区域转换为一个“表”的方法

将一个区域认定为一个“表”的路径为:点击菜单栏的“开始”,再点击其工具“表格样式”,根据我们的喜好选定表格样式,进入套用表格样式对话框,在WPS表格中,在此对话框中,不能选择“仅套用表格样式”(这是原来对话框的默认选择),而要选择“转换成表格,并套用表格样式”,再点击“确定”。这时在菜单栏中会有“表格工具”这个菜单,并在其相应的工具栏提供了许多功能。至此,表明我们将区域转换为了“表”。

微软表格中,在套用表格对话框中,没有是否仅套用格式的选择,直接点击“确定”就可以了。这时菜单栏出现“表格设计”这个菜单。

将一个区域转换为“表”以后,会给这个表取一个名称,一般其初始名称为“表1”、“表2”,这个名称就代表这个区域。我们也可以更改这个名称的名字。更改的方法是,点击菜单栏的“表格工具”(WPS表格)或“表格设计”(微软表格),再点击其工具栏最左边的“表名称”下面的方框,就可以更改了。

对于已经形成的“表”,“表格工具”下的功能键“镶边行”的作用是使相邻的两行数据显示不同的颜色,“镶边列”的作用是使相邻的两列数据显示不同的颜色,以增加阅读性。对于微软表格而言,这种功能更显著,但对于WPS表格而言,主要通过表格样式来实现这种功能,当我们的表格样式是针对行标志不同的颜色,应该勾选“镶边行”,是否勾选“镶边列”的作用不大。基于增加阅读性,应用“镶边行”或“镶边列”的功能,应该选择表格样式中颜色较深的样式,这样颜色对比度更高,阅读爽目。

第三、将一个区域转换为“表”的时应注意的问题

  1. 我们的标题行只能是一行,且标题行中不能有合并单元格。当我们的标题行有两行的时候,我们可以只框选最下面的一行标题和内容组成的区域转换为“表”。有的版本允许有两行标题,但这样在汇总行的计数,会出现错误,它会把其中的一个标题行也计为一个数。也会有“列1”,“列2”,.....覆盖第一行标题,表格打印出来,也会把这些覆盖的字符打印出来,达不到我们想要的效果。
  2. 要转换为“表”的区域不能有合并单元格。
    3、新建立的“表”不能包括原来的“表”。当我们表格的一部分区域已经转换为“表”,我们要将这一部分还原为区域,还原的方法是将光标放在“表”中,点击“表格工具”下面的菜单“转换为区域”即可。

4、表中不能有斜线表头和空白行、空白列。

第四讲:“表”(下)

大家好,我是一丁老师。这一讲,我们继续讲解“表”,或者说是超级表。在上一讲,我们讲了与“表”有关的三个问题,下面我们讲解与表有关的第四个问题和第五个问题。

第四、“表”区别于“区域”的特色功能

“表”区别于“区域”具有许多特色功能。

销售表(表和透视表).xlsx

一是区域选择和区域拓展功能。“表”不需要选择区域,当我们把光标放在“表”中任一个单元格的时候,Excel默认为选择全部区域;当我们不隔行的添加行或者不隔列的添加列时,“表”会自动地将这些行或列拓展为全部区域。只要我们在表格中相邻列添加列字段,并按回车键,表格就会自动地向下添加一列。

“表”的自动区域拓展功能,极大地方便了我们的表格制作。我们在制作表格时,可以先画一个行数和列数都很小的表,如4-5行和列,然后转换为“表”,再根据需要直接不隔行地添加行或列,录入数据即可。表格会自动地添加网格线(此时工具栏的镶边行,镶边列前面的小方框应打上√),自动地将新添加的行或列,设置成与前面格式相同的行或列。

采用插入的办法在工作表的左边添加列,“表”不会将左边的插入列自动拓展为“表”的一部分。

二是同时提供筛选和排序功能。点击列字段上的倒三角符号,在接下来的对话框中,这个对话框既提供了排序功能,又提供了筛选功能。

三是提供了便捷的统计功能。勾选“表格工具”下的功能键“汇总行”,会在表格底部加入一行,并且自动汇总出最右边的数据,点击底部汇总行的其他单元格,这些单元格的下拉菜单会提供“平均值”、“计数”、“求和”等功能,我们可以针对不同的列,选择不同的汇总方式。

如果我们应用了筛选功能,汇总行可以统计出筛选后的数据汇总值。在“表”中执行汇总的函数是SUBTOTAL读作 [ˈsʌbˌtəutəl] ,这个函数的意思是对可见单元格进行汇总,实际上就是对筛选出来的单元格进行汇总。

如果我们去掉汇总行,继续无间隔地(不能空行)向下添加行,“表”在执行相关功能的时候,会把这些添加的部分,视为“表”的一部分。

四是上下并排的表可同时筛选排序,互不影响。如果在一个工作表里有两个区域都有数据,而且把这两个区域都变成了“表”,我们可以分别针对这两个表进行排序筛选操作,互不影响。

五是添加计算列。我们可以根据需要在表的相邻列(不能隔一列)输入公式,形成对原表数据的计算,这时的“表”会把这一列包括到“表”中来,这一列也可以应用“表”的功能了。

在微软表格中这个公式具有向下自动填充功能,而且这个公式的表达形式也具有特色,它把对单元格的引用汉字化,增强了公式的可阅读性,如“=[@销售金额]/[@销售价格]”来表达销售数量公式。但在WPS表格中,没有向下自动填充功能,需要采取拖曳的方式向下填充。

例如:销售表(表和透视表).xlsx中的销售记录表I列的公式。

六是提供删除重复项功能。这里的重复项,指的是两行单元格的所有内容相同,假如两行表格各有5个单元格,这5个单元格的数据都在同一列内相同。

其删除重复项的步骤为:在表格转换为“表”以后,将鼠标放在表中的任意一个单元格,在菜单栏就会出现“表格工具”菜单,在此菜单下有一个工具叫“删除重复项”,点击这个工具,进入“删除重复项”对话框。根据需要请选择一个或多个包含重复项的列,或者选择全部,就可以确定是否有重复项了,点击“确定”,就可以删除重复项了。

显示重复项的方法:要找打那个重复项,最简单的操作方法是,第一,在表格的最右边添加一列,用连接符号将一行中所有单元格的数据连接起来,再向下拖曳,形成一列。第二,将表格还原为区域。第三,点击“数据”菜单下的“重复项”功能键,框选添加的一列,选择“高亮度重复项”,就可以看到重复行的数据了。

七是基于“表”形成的透视表和图表。我们在原“表”添加行或列以后,透视表能够自动刷新,当然自动刷新要进行相关的操作,其具体方法我们在下面讲解透视表时再讲。

第五、“表”中选择列或行的方法

一、选择一列数据而不选择整列的原因。不管是普通工作表还是我们这里讲的“表”,选择或者移动行和列时,应该遵循规范的方法。

直接移动表格中行或列的方法,会给表格的使用带来许多不利的影响,一是这样的操作会使表格所占的内存变大,导致打开缓慢。二是选择移动整列会导致这个表格以外的数据跟着移动,当我们的表格下面还有其他表格的时候,会破坏其他表格的结构。三是选择移动整列的操作会破坏我们当前“表”的结构,导致许多功能不能使用。当然,这种操作还会带来其他的一些负面影响,使工作表在运用中出现一些非正常的情况。

二、选择并移动整列数据的方法。选择要移动列的数据的方法是,将鼠标移到该列标题行顶部单元格划线的位置,这时鼠标变成了一个黑色的向下方向键,点击鼠标左键,即可选定该列的数据。这时我们可以看到选择的区域,不包括该列的标题,在鼠标变成了一个黑色向下方向键的同时,再次点击鼠标左键,就包括该列的标题了。我们将鼠标放在此列的边框线上,当鼠标变成四个方向键图标时,拖动鼠标,就可以移动这一列了。

选择不包括标题行的数据区域,便于我们根据需要对数据区域进行操作,如同时加上一个数,修改数值金额格式等等。

  1. 选择多列数据的方法。如果我们要选多列的数据,可以将鼠标放在靠一边的列标题行顶部单元格划线的位置,这时鼠标变成了一个黑色的向下方向键,按下鼠标左键,再向右或向左拖动鼠标,即可选择多列。

如果我们选择的多列需要和列标题一起移动,应该在选择的起始列时,就将标题和内容一起选定(选定方法同前面讲的选定单列的方法一致),再拖动需要选择更多的列。

这样做的前提是,要选定的列没有内部的区域框选。

移动行和选择多行的方法,与移动列和选择多列的方法类似。

第五讲:下拉菜单

我们在前面讲解基础数据表的时候,说到过下拉菜单是用来规范基础数据的录入的,它还能提高我们录入数据的速度,下面我们来介绍下拉菜单的相关知识。

在微软表格中,称下拉菜单为数据的有效性。

建立下拉菜单讲解表格.xlsx

第一、用手动输入的方法建立下拉菜单

用手动方式建立下拉菜单的路径与方法是:

其一,单个表格下拉菜单的设置方法。首先选定单元格,点击菜单栏上的“数据”,再点击其工具栏“下拉列表”,进入插入下拉列表对话框,在光标闪动处分行输入需要的内容,在输入第二行时,点击右上角的“+”号,输入完成后,再点击“确定”。

其二,批量表格下拉菜单的设置方法。将设置有下拉菜单的单元格向下拖曳填充,填充的表格也会有下拉菜单;将设置有下拉菜单的单元格向右拖曳,填充的表格也会有下拉菜单;在表格中插入行,这些插入的行相应的单元格也会有相同的下拉菜单。

我们还可以选定一个区域,针对这个区域设置下拉菜单,其设置下拉菜单的方法和单元格设置下拉菜单的方法一样,不再需要进行拖曳填充,区域中的每一个单元格都会设置相同的下拉菜单。

  1. 用导入的方式建立下拉菜单

采用导入的方式建立下拉菜单,直观性强,操作简单。

首先提前准备好下拉菜单表格(我们的这个表格可以适当留有空白,当我们框选包括空白单元格在内的区域作为下拉菜单时,这些空白单元格可以在今后添加内容,但这个区域只能是一行或一列,不能是多行或多列);

接下来选定要设置下拉菜单的区域或单元格;

再依次点击菜单栏的“数据”,进入其工具栏“下拉列表”,在插入下拉列表对话框中,选择“从单元格选择下拉选项”(而不是选择“手动添加下拉列表”),这时鼠标会在“从单元格选择下拉选项”下面的一个长方格中闪动,再框选提前准备好的下拉菜单表格;

最后点击“确定”。

  1. 在微软表格中,建立下拉菜单的方法

以上讲述的建立下拉菜单方法,都是在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的一项重要功能,我们将这部分内容作为单独的一讲。

  1. 生成图表的数据来源及生成方法。

图表既可基于基础数据表生成,也可直接基于数据透视表生成。直接基于基础数据表生成图表时,要框选基础数据表的区域,而且要求表格中的行字段不重复,只适用于简单的表格。透视表是在基础数据表进行分类汇总后形成的,基于透视表生成的图表,能正确反映我们需要的数量关系,也适合于复杂的基础数据表,从本质上讲,还是反映基础数据表的数量关系,是我们工作中经常采用的方法。

点击已经生成的数据透视表,将光标放在透视表中,再点击菜单栏的“插入”,进入其工具栏,点击“全部图表”,选择恰当的图形,即可形成图表。

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、删除名称的方法。点击“名称管理器”,进入“名称管理器”对话框,选中名称管理器中排列的任意一条名称,点击“删除”可以删除名称。对“表”取的名称不允许删除。

(0)

相关推荐