Excel函数的七个高阶技巧

在计算数据,特别是需要进行一些复杂的数据计算时,只有通过函数才能快速地完成。

Excel 2019 中提供了大量的函数,要想从中选择合适的函数,且保证计算结果正确,就必须掌握函数的基础应用知识。

而函数中也有一些鲜为人知的小技巧,这些小技巧可以帮助我们大幅提高使用函数的效率~

技巧 01:将表达式作为参数使用

在 Excel 中可以将表达式作为参数使用,表达式是公式中的公式。

下面来了解这种情况下函数的计算原理。

在遇到作为函数参数的表达式时,Excel 会先计算这个表达式,然后将结果作为函数的参数再进行计算。

例如,公式【=SQRT(PI() * (2.6^2)+PI() * (3.2^2))】中使用了 SQRT 函数,它的参数是两个计算半径分别是 2.6和3.2的圆面积表达式【PI() * (2.6^2)】和【PI() * (3.2^2)】。

Excel在计算公式时,首先计算这两个圆的面积,然后计算该结果的平方根。

技巧 02:使用数组型函数参数

函数也可以使用数组作为参数,使用数组类型的参数一般可以简化公式。

例如,要计算 208 * 1、1.2 * 2、3.06 * 3 的和,可以使用公式【=SUM(208 * 1,1.2 * 2,3.06 * 3)】,利用数组型参数,则可以写成【=SUM( * )】,它们的结果是相同的。

技巧 03:将整行或整列作为函数参数

在一个随时增加记录的销售表格中,要随时计算总销售量,尽管使用函数比较容易得到结果,但是需要不断更改销售参数,仍然很麻烦。

遇到上述情况时,可以将这些作为函数参数,但汇总的范围又是变化的数据单独存储在一行或一列中,然后将存储数据的整行或整列作为函数的参数进行计算。

例如,将销售量数据单独存储在 A 列中,再输入【=SUM(A:A)】,将整列作为参数计算总和。

在使用整行或整列作为函数参数时,一定要确保该行或该列中的数据没有不符合参数要求的类型。

有些人可能会认为,计算整行或整列这种大范围的数据需要很长时间,实际上并非如此,Excel 会跟踪上次使用的行和列,计算引用了整行或整列的公式时,不会计算范围以外的单元格。

技巧04:使用名称作为函数参数

名称也可以作为函数的参数进行使用,使用方法与公式中的使用方法相同。

函数可以把单元格或单元格区域引用作为其参数,如果为这些范围定义了名称,则可直接使用名称作为参数。

例如,要求 A1:D50 单元格区域中的最大值,则输入函数【=MAX(A1:D50)】。若为 A1:D50 单元格区域定义名称【cj】后,则可使用名称来替换引用,将函数简化为【=MAX(cj)】。

在使用查找与引用函数时,有时需要进行模糊查找,此时就需要使用到通配符。

Excel 中主要包含【 *】 、【?】和【~3】种通配符,可用于查找、统计等运算的比较条件中。

技巧 05:简写函数的逻辑值参数

由于逻辑值只有TRUE或FALSE,当遇到参数为逻辑值的函数时,这些参数即可简写。

函数中的参数为逻辑值时,当要 指 定 为 FALSE 时, 可 以 用 0 来替代,甚至连 0 也不写,而只用半角逗号占据参数位置。

例如,VLOOKUP 函数的参数 range_lookup需要指定逻辑值,因此可以将公式进行简写,如函数【=VLOOKUP(A5,B5:C10,2,FALSE)】可简写为【=VLOOKUP(A5,B5:C10,2,0)】,也可简写为【=VLOOKUP(A5,B5: C10,2,)】。

需要指定参数为 0 时,可以只保留该参数位置前的半角逗号。

例如,函数【=MAX(A1,0)】可以简写为【=MAX(A1,)】,函数【=IF(B2=A2,1,0)】可以简写为【=IF(B2=A2,1,)】。

技巧 06:省略函数参数

函数在使用过程中,并非所有参数都需要书写完整,可以根据实际需要省略某些参数,以达到缩短公式长度或减少计算步骤的目的。

在【Excel 帮助】窗口中,就可以看到有些函数的参数很多,但仔细看又会发现,在很多参数的描述中包括【忽略】【省略】【可选】等词语,而且会注明如果省略该参数则表示默认该参数为某个值。

在省略参数时,需要连同该参数存在所需的半角逗号间隔都省略。

例如,判断 B5 是否与 A5 的值相等,若是则返回 TRUE,否则返回 FALSE。

函数【=IF(B5-A5,TRUE,FALSE)】可省略为【=IF(B5=A5, TRUE)】。常见的省略参数还有以下几种情况。

FIND 函数的参数 start_num 若不指定,则默认为 1。

INDIRECT 函数的参数 al 若不指定,则默认为 A1 引用样式。

LEFT 函数、RIGHT 函数的参数num_chars 若未指定,则默认为1。

SUMIF 函数的参数 sum_range 若未指定,则默认对第 1 个参数range 进行求和。

OFFSET函数的参数height和width若不指定,则默认与 reference 的尺寸一致。

技巧 07:简写函数中的空文本

并非所有函数参数简写都表示该参数为 0,如函数【=IF(A1="","",A1 * B1)】中简写了该参数的空文本,表示若 A1 单元格为空,则显示为空,否则返回 A1 与 B1 的乘积。

在函数应用中,经常也会简写空文本参数。

这里所谓的空文本,不是指单元格中不包含数据,而是指单元格中包含一对英文双引号,但是其中的文本没有,是一个空的字符串,其字符长度为0。

省略参数是根据函数的默认规则将整个参数(包括所需的逗号间隔)从函数公式中移除。

而参数的简写则需要用半角逗号保留参数的对应位置,表示简写了该参数的常量 0、FALSE或空文本等。

相关文章