1.1.3　不为人知的Excel高手定律

高手是怎么使用Excel的，应遵循什么定律？下面将详细介绍。

定律1：规范的Excel数据表格比复杂的公式更重要

我们知道，Excel中有几种不同的表格类型，它们都是在Excel工作表中设计的，只不过是工作表的不同形态。在实际使用中，我们要尽量区分不同表格的功能和形态。特别是对数据记录表而言，Excel新手极易形成不合理的用表习惯，而数据记录表又是其他各类表格的基础，一旦造成表格内容和结构上的缺陷，就会给后续的处理和利用带来问题。

《“偷懒”的技术：打造财务Excel达人》作者龙逸凡曾举过一个例子：表哥龙逸凡要对销售员的销售情况做一个登记台账，他做出的销售台账如图1-8所示。

由于表格设计不规范，如果要统计某产品的销售数量和销售金额，不能使用SUMIF函数，编制的求和公式是复杂的数组公式：=SUM((LOOKUP(ROW(4:22), ROW(4:22)/(A4:A22>0),A4:A22)=K3)*D4:F22)。此公式很复杂，思路也很巧妙，我们能说龙逸凡精通Excel吗？

如果将图1-8所示的表格做成规范的一维记录表，那么用一个简单的条件求和函数SUMIF就能统计出来，如图1-9所示。

在这个案例中，不规范的表格要使用复杂的数组公式来弥补，而规范的表格可以使后续的公式变得简单。到底是复杂的公式好还是规范的数据表格好？当然是后者。要轻松驾驭Excel，把Excel发挥到极致，首先要做到规范。

下面从结构和数据两个角度来介绍Excel表格的规范性。

（1）合理的结构

①保证一维表结构。这是最基本的习惯，一维数据表极大地方便了数据的录入和批量化操作，而且更利于后续的数据分析（无论是使用函数还是数据透视表）。

试想：如果将数据记录表设计成如图1-10所示的二维表格，那么根据销售订单记录数据时，就需要汇总不同订单的销量，然后计算并填入表中，这个过程不管是汇总销量还是计算总数，效率都很低。

②不使用合并单元格。合并单元格归纳起来有两方面的作用，一是将相同的内容合并为一个，做到视觉上的统一；二是将内容居中显示。可见合并单元格最主要的作用是为了使外观好看，因此用在结果报表中是没有问题的，但如果用在数据记录表里，就会给后续操作带来意想不到的后果。

例如，对含有合并单元格的数据表进行排序，会提示“若要执行此操作，所有合并单元格需大小相同”，导致无法顺利排序。再如，剪切包含合并单元格的行或列时会弹出提示“无法对合并单元格执行此操作”。

关于合并单元格带来的后果还有很多，比如使用VLOOKUP函数查找无法返回正确值，无法准确插入智能表等。这是因为合并单元格是将相邻的一些单元格组合在一起，破坏了表中的单元格布局结构，由此会对单元格的复制、粘贴、排序、填充等操作造成影响。因此，在设计数据记录表时，应尽量避免使用合并单元格。

③使用单行标题字段。我们知道，数据记录表由“字段+记录”组成，字段其实就是表格第一行的标题，它的作用是表明这组数据所属的类别。

很多Excel新手在设计表格时，为了展示更多的信息而经常采用两行甚至多行空间作为表格的标题字段，这样的标题字段在Excel中是无法识别的。默认情况下，Excel只能把首行识别为标题行，因此占据多行的标题会让Excel产生混乱，甚至“认为”没有标题行。比如，在插入智能表格时，会打散第一行的标题，如图1-11所示。

因此，在设计数据记录表时，要确保有且只有一行标题字段。

④避免使用空行、空列。由于大量的数据连在一起看起来有些乱，一些Excel新手通常会使用空行（或者空列）进行视觉上的隔断，如图1-12所示，5月13日和5月14日的两批数据之间使用了空行进行隔断。

这种隔断一方面在视觉上没有任何效果（因为数据记录表的作用不是用来看的，是用来录入数据和分析的），另一方面，破坏了Excel表格的连续性。这样，不管是后续的排序、筛选、填充，还是生成数据透视表，Excel会认为图1-12所示的数据表格是两个表，它们无法连在一起进行排序或者生成完整的数据透视表。

（2）规范的数据

①同一列数据保证最小颗粒化。每个单元格只存放单个有效数据，千万不要把数值和单位放在一起，否则填入单元格中的数值就会被当作文本看待，从而导致计算出错。

如图1-13所示，将数值和单位写在同一个单元格中，那么在计算产品的总销量时将会出错，因为此时F列中的数值都是文本格式。

如果确实需要显示单位，除了将数值和单位分别写到不同的列，还可以这样处理：选中需要设置单位的数值列后按“Ctrl+1”组合键，单击“自定义”→“类型”，在原有的“G/通用格式”后面加上单位即可，如图1-14所示，所需要显示的单位为“台”。

这样设置之后，单元格中显示为“数值+单位”，实际上单元格中只有数字，格式为数值格式，可以参与数学运算，如图1-15所示。

②使用正确的日期格式。很多初学者普遍认为Excel中的日期是一种文本格式，无法进行运算，因此随意输入日期格式。其实，在Excel中，日期也是可以使用数值的方式进行转换和存储的，它有统一的标准格式。

日期格式混淆性很强，我们通常认为的日期格式在Excel“眼中”却是文本。举例说明，判断以下几个日期格式，哪一个是正确的日期格式，哪一个是错误的日期格式？

事实上，这5种日期的写法全是错的。只有使用短横线“-”或者斜杠“/”作为间隔符的日期才是真正的日期格式，比如2017/5/20、2017-5-20。另外，中文输入的2017年5月20日也是正确的日期格式。

只有用了正确格式的日期，才可以准确地转换格式，或者做计算，一旦写成错误的日期格式，就无法进行后续的计算分析等。而且，如果写错了日期格式，是无法通过设置单元格格式将其转换为正确的日期的，如果是批量录入的数据，要一个一个地修改，其工作量就变得巨大。

③不要在单元格中键入空格。在输入姓名的时候，很多用户为了将两个字的姓名和三个字的姓名对齐，喜欢在姓名中间插入空格。如图1-16所示，在两个字的姓名中间插入空格后，视觉上确实整齐了，但是这小小的空格带来的危害也是巨大的。

在姓名之间插入空格之后，“星爷”就不是原本的“星爷”了，由于空格隐蔽性强，肉眼很难发现插入了几个空格，所以在使用VLOOKUP查找匹配时，就查找不到“星爷”。

其实，有更加专业的方法将姓名对齐。如图1-17所示，在设置单元格格式中，将文本对齐方式改为“分散对齐”，这样两个字和三个字的姓名就自动对齐了。

定律2：结构化引用比普通引用更高效

我们一直说的Excel表格到底什么是表？是一个Sheet，还是工作表区域？其实Excel中真的有一个叫作表（Table）的元素，这个表是一个结构化的、完整的数据区域，引用表中的数据将自动变为结构化引用。相应地，引用普通单元格区域则为普通引用。

因为表格数据区域经常变化，而结构化引用的单元格引用可随之自动调整，这样便在最大程度上减少了在表格中添加或删除行和列时或刷新外部数据时重写公式的需要。

基于上述表的特性，得出高手定律2：结构化引用比普通引用更高效。因此，我们在制作Excel表格时，要尽可能将其转化为Excel表（Table）。

这里探讨以下两个问题。

• 表（Table）的做法和意义。

• 为什么结构化引用更高效。

（1）表（Table）的做法和意义

Excel中的Table在中文语境下叫作智能表格，本书都以智能表格指代Table，而用单元格区域指代普通的单元格。智能表格是工作表中的一个区域，因此创建智能表格其实就是对工作表中的数据区域进行“表格化”操作。

Step1：将鼠标光标定位在数值区域中的任一单元格。

Step2：在功能区中依次单击“插入”→“表格”，单击“表格”。也可用创建Table的快捷键：“Ctrl+T”组合键。

这样创建的智能表格会默认扩展至所有的数据区域，如果只是希望数据区域的一部分创建为智能表格，可以先选中需要创建为智能表格的区域，然后执行上述操作。通常情况下，对于规范的数据表，出于更轻松地管理和分析的需要，我们都可以将普通单元格区域转化为智能表格，其式样如图1-18所示。

创建完智能表格之后，Excel会自动填充颜色，将此区域独立标识出来。除此之外，似乎看不出智能表格与普通表格之间的差异。

智能表格到底是什么？我们切换到“公式”选项卡，单击“名称管理器”，在弹出的“名称管理器”中可以看到，自动新建了一个自定义名称，这个名称的类型是表。其实智能表格除了名称被Excel通过“名称管理器”记住外，它的结构也被Excel记住了，这就为智能表格后续的一系列功能带来了操作便利。

使用Excel智能表格的优势如下。

①自动扩展：表格数据区域经常变化，而结构化引用的单元格引用可随之自动调整。

②结构化引用：使用名称代替单元格地址，使写出的公式易于理解。

③自动汇总：智能表格自带丰富的汇总能力，不用编写公式。

（2）为什么结构化引用更高效

我们通过智能表格的结构化引用和普通表格的普通引用来做几组对比，从而体验结构化引用如何使计算更高效。

①自动扩展。在普通的表格中，使用公式的最大痛点之一就是：如果计算区域有增减，公式就需要重新写，因为公式引用的数据区域不会变。而使用智能表格后，当新增行或者列时，表格中的公式将自动应用到新增加的列或行中，无须更改公式中的引用。

如图1-19所示，数据区域已经设置为智能表格，我们希望求出E列销售量之和，在G2单元格输入公式=SUM(E2:E8)，公式将自动转换为=SUM(表5_78[销售量])的结构化引用式样，正是因为会转化成结构化引用，因此这个引用并不是固定的某几个单元格。

如果在图1-19的第9行追加一行数据，公式内的引用将自动扩展到E9，无须修改公式，这极大地提高了工作效率。

②公式更清晰。如图1-20所示，是一个创建好的Excel智能表格，在这个表格中，我们需要在E列求出佣金金额。

针对Excel智能表格，结构化引用是怎么实现的呢？下面一步一步地输入公式。

Step1：在E2单元格中输入一个等号（=），并单击单元格C2，这时结构化引用[@销售额]出现在等号后。

Step2：在右方括号之后直接输入星号（*），然后单击单元格D2，这时结构化引用[@佣金比率]出现在星号之后，生成的公式如图1-21所示。

Step3：按回车键后，Excel会自动创建一个计算列并将公式向下复制填充到整列。

上述结构化引用的公式一气呵成，其体验远超普通单元格中的公式。更重要的是，我们通过公式“=[@销售额]*[@佣金比率]”，就知道表达的含义是什么，可谓一目了然。

但是，当我们使用普通的单元格引用时，情况如何呢？在公式栏中输入公式=C2*D2，我们只知道这个公式是两个单元格相乘，但其背后表达的含义不得而知，公式的可读性很差。

③自动汇总。将鼠标光标定位于智能表格区域，会在菜单栏激活智能表格的“设计”选项卡，在这个选项卡中可以修改“属性”，设置“表格样式选项”，如图1-22所示。

在“表格样式选项”分区中，最有用的功能当属为智能表格添加汇总行。勾选“汇总行”复选框，智能表格中将在最后一行自动添加汇总行，汇总行可以快速汇总表中的数据，并且Excel为每个汇总行单元格提供了多种下拉函数。

添加汇总行之后，在智能表格的最后一行自动添加了带公式的汇总行，它可以瞬间提高工作效率：汇总行提供了8个函数，我们无须输入任何函数就能进行汇总计算，瞬间得出想要的汇总结果，如图1-23所示。

定律3：Excel元素+自定义名称=通行证

如图1-24所示，这是一个大型的分析报告中的名称管理器界面，这个分析报告中，自定义的名称有50多个，涉及智能表格构建的名称、形状定义的名称、单元格区域定义的名称、公式定义的名称等。

而一个Excel新手制作的表格虽然也可能很复杂，但合理利用自定义名称的却寥寥无几。可以说，Excel高手的第三个定律就是活用Excel自定义名称，实现“Excel元素+自定义名称=通行证”的捷径。

“名称”就是用来封装常量、单元格区域和公式的，具体的作用有以下5点。

①作为单元格区域的代称，名称使公式便于被理解和维护。

②替代多次出现的公式，简化输入，便于统一更改、维护。

③作为公式、单元格区域的代表，它被Excel各模块引用。

④Excel中特殊的宏表函数，比如EVALUATE、Get.cell和Get.workbook只能在自定义名称中使用。

⑤生成动态的区域引用，用于动态数据透视表、动态图表、数据有效性列表等。

在具体应用之前，我们需要先弄明白“名称”的作用范围，这对深刻理解名称有很大帮助。名称既可以属于某个工作表，也可以直属于工作簿，因此它可以直接调用工作簿的资源，并且可以在整个工作簿中被看到，可以说在这个层面上它的“身份”是要高于函数与公式的。

这就是为什么说“Excel元素+自定义名称=通行证”的主要原因。

自定义名称可以对单元格区域、公式和常量进行封装，封装之后，名称就可以代替对应的单元格区域、公式和常量。更重要的是，名称比单元格区域、公式和常量在工作簿中的通行级别更高，可以突破限制，被更多的模块识别和引用。比如图表原本不能引用公式，封装之后，公式可以进入图表中，VBA代码可以操控Excel形状等。也就是说，自定义名称赋予了Excel各类元素更高的通行能力。

关于自定义名称带来的通行证效应，我们来看一个案例：自定义名称传递“序列”数据源。

我们知道，可以通过INDIRECT函数和创建自定义名称来创建二级联动菜单，在创建这个二级菜单的过程中，我们只是创建了名称，并没有使用该名称，那么，会有一个问题：创建的这些名称到底有什么作用？

注：关于通过INDIRECT函数创建二级菜单的方法，具体请学习2.4.4节的内容，这里对创建过程不再赘述。

为了弄清楚这个问题，我们先来看看数据验证中关于“序列”的要求，在“数据验证”对话框中，“允许”选择“序列”之后，会要求设置序列的“来源”。

如果在“来源”输入框中输入不符合规则的内容，比如输入“=2”，单击“确定”按钮，Excel就会弹出警告“列表源必须是划定分界后的数据列表，或是对单一行或一列的引用”。也就是说，作为序列的来源，必须是一个序列，这个序列可以是直接输入的序列（用英文状态下的逗号隔开）、引用的行或列、函数返回的数组序列。

直接输入和引用行列这两种方法比较简单，难点是函数，如果来源是函数，那么函数的返回值必须是数组，否则会出错。

正常情况下，通过INDIRECT函数创建二级菜单时，我们需要先对“广东省”、“江苏省”创建自定义名称，它们对应的数值就是两个省下辖的城市。为了验证INDIRECT函数的作用，我们先把定义的“广东省”、“江苏省”这两个名称删除，然后在H4单元格使用INDIRECT创建二级菜单，如图1-25所示。创建完成后，在H4单元格单击下拉菜单，发现没有任何菜单，这是因为序列的“来源”并不是一个序列，把=INDIRECT($G$4)公式输入到单元格，按回车键会发现返回的其实是一个错误值，这个公式没有实质意义。

下面再进行正向验证，如果函数返回的是数组系列，结果如何？仍然不创建名称，这次将序列来源设置为公式：

=OFFSET($B$3,MATCH(G4,$B$4:$B$5,0),1,,3)

关于这个公式的意义，其实就是根据G4单元格中的内容返回一个“1行3列”的数据序列。完成之后会发现，H4单元格下拉菜单的内容会随着G4单元格中省份的不同而不同。

由此一反一正地验证，我们推理出通过“名称+INDIRECT函数”创建二级菜单，名称在此起的作用就是通过INDIRECT函数的解析，向数据验证中的序列“来源”传递一组序列。

这里讲解这个案例，主要是为了让大家明白自定义名称起到通行证的作用。本例中涉及INDIRECT函数、OFFSET函数都是我们后续要学到的知识，大家在此了解即可。

定律4：可视化——字不如表，表不如图

所谓“一图抵千言”，说的就是“字不如表，表不如图”。关于可视化中“字不如表，表不如图”，有两个经典的案例。

案例1：下面是某个网站描述商旅乘客生活轨迹的内容，这段内容如果不仔细看，会摸不着头绪。

商旅人士的订票节奏非常有特点：从早上7点开始，这一群体的订票便开始出现增长，在9点至11点呈现高峰活跃状态，在中午12点至下午1点因为就餐时间，订票量趋于回落。不过在下午2点又恢复到活跃状态，这个势头一直保持到下午5点结束，不过，从下午5点至晚上9点，订票的比例分布相差并不大，在此之后缓慢回落。

即使到了晚上12点，商旅人士也保持着几乎与早上7点持平的订票状态，可见这一群体还是很拼的，加班至深夜也是常态。

较之订票节奏，商旅人士出行的节奏则呈现出多个小高峰分布，和24小时都有人订票的状态不同的是，在凌晨3点至5点期间，出行量为零，而早上7点则是一个高峰。较之夜间飞行的红眼航班，日间出行是商旅人士们出行的首选。

——节选自DT财经《数据揭秘商旅乘客生活轨迹，看完还做“空中飞人”吗？》

通过可视化表达上面的文字，可用如图1-26所示的图表来表示。也就是说，上述文字描述的情景用一个图表就能表达出来，并且更加清晰了然。

案例2：如图1-27是一份销量表，请比较各销售员的销量情况，谁的销量最大？

逐一查找也能找出销量最大的人，但是以数据表格的形式读起来并不直观，用来存储数据尚可，用来展示可视化还远远不够。我们可以对数据表增加条件格式，如图1-28所示，这样数据的大小就一目了然了。

当然，将其做成图表则是更优的方法，如图1-29所示，能快速看出谁的销量最高。

这只是两个简单的案例，但道理都一样：凡是需要展示的数据，都进行可视化处理。关于可视化相关的内容，我们在第4章、第5章会有详细讲解。

定律5：泛泛掌握100个Excel小技巧，不如深入理解一个

作为精通Excel的高手，对Excel知识掌握得既精又深才是应有的标准。Excel很多看似“肤浅”的功能，其实都蕴藏着更深邃的技法。下面以案例形式进行说明。

案例3：SUM函数的灵活应用。

我们对SUM函数参数的引用形式进行灵活设置，可以做到求余额的计算，如图1-30所示，在D2单元格输入公式=SUM($C$2:C2)，将公式向下复制填充到数据区域的所有行，随着引用区域的不断扩展，就能计算出所有金额的累计情况。

再如，一个工作簿中有不同月份的销量，需要将不同月份的销量汇总到表中进行求和，这个问题是典型的跨工作表求和问题。很多人第一次看到这个问题时，认为需要用很复杂的公式，其实使用SUM函数就能完成跨工作表求和，如图1-31所示，在B2单元格中输入公式=SUM('1月:3月'!B2)，向下复制填充即可。

注意，这里“1月:3月”就是需要汇总求和的多个工作表名称，分别是这些工作表的起始位置和结束位置，这样在确保多个工作表结构相同的情况下，使用SUM就能一次性进行汇总求和。

作为精通Excel的高手，对于函数，要深入掌握各类函数的知识点，要知其然，也知其所以然。比如深入理解SUMIF第三个参数的定位原理，对SUMIF第三个参数的定位原理灵活使用。

SUMIF函数的第三个参数单元格区域起的作用就是左上角的单元格，此单元格的作用是定位，只要有定位点，SUMIF会自动以此单元格为原点，按照第一个参数区域符合条件的单元格的坐标，找到相同坐标位置的单元格，并对其数值求和。

我们利用此特点，可以完成看似难以完成的任务。

案例4：SUMIF函数高级应用1——错行求和。

如图1-32所示，求北区2012年度销售额合计，使用SUMIF函数写出的公式为：=SUMIF(B4:B18,"北区",D5)。

案例5：SUMIF函数高级应用2——求最后一行数值之和。

如图1-33所示，求各职员最后一次考评分数的平均值，使用SUMIF函数写出的公式为：=SUMIF(B3:F9,"",B2)/5。

案例6：SUMIF函数高级应用3——错列求和。

使用SUMIF函数错列求和，写出的公式是=SUMIF(A2:C11,F3,B2:D11)，如图1-34所示。

对于SUM和SUMIF函数，能掌握到这个程度可称得上做到了举一反三，这样深入的技能远比掌握100个小技巧重要得多。关于SUMIF函数的高级应用，这里只是起抛砖引玉的作用，详细的知识点将在2.3.3节深入讲解。

通过本节内容，想要传达给大家的观点就是：一定不要盲目追求Excel零散的知识点，要深入核心的技能，这样才能做到举一反三，面对职场中的各类问题才能游刃有余地解决。