专栏首页完美ExcelExcel公式技巧15: 探讨强制返回数组的公式技术

Excel公式技巧15: 探讨强制返回数组的公式技术

有时候,我们希望将公式应用于一组值而不是一个值,这可以简单地将公式作为数组公式(按Ctrl+Shift+Enter键)来实现。然而,并不是所有公式都能如此轻松地产生这样的效果,有些公式很“顽强”地抵制任何试图强制让它们返回数组的尝试。本文将探讨一些技术,除了数组形式的输入外,可以帮助强制达到想要的结果。

例如,下图1中单元格区域A1:A5是要使用的数据,右侧的数组公式并没有给出想要的结果。(特别说明:示例纯粹是为了演示我们要讲解的技术。)

图1

第一个公式使用了INDIRECT函数和ADDRESS函数组合来求单元格区域A1:A5中的数值之和。显然,诸如下面的非数组公式:

=INDIRECT(ADDRESS(1,1))

解析成:

=INDIRECT("$A$1")

结果为:

9.2

因此,我们可能希望使用数组公式将这种公式构造应用于多个单元格。然而,使用数组输入后的公式:

=SUM(INDIRECT(ADDRESS({1,2,3,4,5},1)))

并不会像你所希望的那样解析,而是转换为:

=SUM(INDIRECT("$A$1"))

ADDRESS函数仅仅处理了数组中的第一个元素,就像我们将其作为非数组公式输入一样。

在这种情况下,所需的强制并不是特别复杂,也不需要涉及OFFSET函数或INDEX函数的构造。实际上,所需要做的就是包含一个小的N函数:

=SUM(N(INDIRECT(ADDRESS({1,2,3,4,5},1))))

解析为:

=SUM(N(INDIRECT({"$A$1","$A$2","$A$3","$A$4","$A$5"})))

解析为:

=SUM(N({9.2,1.1,5.5,7.4,3.3})))

即:

=SUM({9.2,1.1,5.5,7.4,3.3})

结果是:

26.5

在这种公式结构中使用N函数并不是为了将其用作函数(广义上讲,是在可能的情况下将非数字转换为数字),而是因为它具有附带的(也是非常有用的)特性:能够促使其他函数操作一组数值数组,例如此处的ADDRESS函数不能操作数组的情况。

当然,我们应该意识到我们使用N函数是因为此处的值是由数字组成。但是,如果我们想使用带有INDIRECT和ADDRESS函数的公式构造生成一个数组,但是其值不是数字,而是文本,并且所得到的数组不被求和而是传递给其他函数,该怎么办?

幸运的是,Excel为提供了一个与N函数具有相似功能的T函数。与N函数一样,T函数同样具有强制返回数组的特性。N函数用于数字,T函数用于文本值。

例如,如果单元格区域A1:A5中的值是“A”、”B“、”C“、”D”、“E”,那么公式:

=LOOKUP(REPT("z",255),T(INDIRECT(ADDRESS({1,2,3,4,5},1))))

LOOKUP函数也具有强制返回数组的特性。公式解析为:

=LOOKUP(REPT("z",255),T(INDIRECT({"$A$1","$A$2","$A$3","$A$4","$A$5"})))

解析为:

=LOOKUP(REPT("z",255),T({"A","B","C","D","E"}))

解析为:

=LOOKUP(REPT("z",255),{"A","B","C","D","E"})

结果为:

E

但是,使用N或T函数强制转换为数组不适用于所有函数,以包含DEC2BIN函数的公式为例:

=SUM(DEC2BIN(A1:A5))

解析为:

=SUM(#VALUE!)

结果为:

#VALUE!

这不是我们想要的结果。

试着在其中添加N函数:

=SUM(N(DEC2BIN(A1:A5)))

情况并不好,因为DEC2BIN函数拒绝N函数试图强制返回数组的尝试,解析为:

=SUM(N(#VALUE!))

那么,在这些情况下,我们需要寻求替代方法来强制得到我们所需的数组处理。一种方法是使用OFFSET函数,如下所示:

=SUM(0+DEC2BIN(OFFSET(A1,ROW(A1:A5)-MIN(ROW(A1:A5)),,,)))

解析为:

=SUM(0+DEC2BIN(OFFSET(A1,{1;2;3;4;5}-1,,,)))

解析为:

=SUM(0+DEC2BIN(OFFSET(A1,{0;1;2;3;4},,,)))

解析为:

=SUM(0+DEC2BIN({9.2;1.1;5.5;7.4;3.3}))

解析为:

=SUM(0+{"1001";"1";"101";"111";"11"})

解析为:

=SUM({1001;1;101;111;11})

结果为:

1225

再提醒一下,虽然以这种方式求二进制之和并不实用,但我们这里重在用其来演示公式技术。

这种包含OFFSET函数的公式技术可用于多个函数,这些函数最初拒绝数组输入。在第三个示例中,使用了DOLLARDE函数,公式如下:

=SUM(DOLLARDE(A1:A5,2))

结果为#VALUE!,而公式:

=SUM(DOLLARDE(OFFSET(A1,ROW(A1:A5)-MIN(ROW(A1:A5)),,,),2))

可以正确地处理数组,并得到想要的结果32.5。

第四个示例:

=IMSUM(COMPLEX(A1:A5,1))

结果返回错误值,而公式:

=IMSUM(COMPLEX(OFFSET(A1,ROW(A1:A5)-MIN(ROW(A1:A5)),,,),1))

解析为:

=IMSUM(COMPLEX(OFFSET(A1,{0;1;2;3;4},,,),1))

解析为:

=IMSUM(COMPLEX({9.2;1.1;5.5;7.4;3.3},1))

解析为:

=IMSUM({"9.2+i";"1.1+i";"5.5+i";"7.4+i";"3.3+i"})

得到想要的结果:

26.5+5i

但是,这种包含OFFSET函数的公式技术并不是使我们能够强制这些“顽固”的函数产生数组返回值的唯一方法。在《Excel公式技巧03:INDEX函数,给公式提供数组》中,讲解了从INDEX函数强制返回数组的技术,在这里也可以使用。上述示例公式的等效的INDEX函数构造如下:

=SUM(0+DEC2BIN(INDEX(A1:A5,N(IF(1,ROW(A1:A5)-MIN(ROW(A1:A5))+1)))))

=SUM(DOLLARDE(INDEX(A1:A5,N(IF(1,ROW(A1:A5)-MIN(ROW(A1:A5))+1))),2))

=IMSUM(COMPLEX(INDEX(A1:A5,N(IF(1,ROW(A1:A5)-MIN(ROW(A1:A5))+1))),1))

实际上,因为INDEX函数是非易失性的,所以可以说这个版本的公式更优于OFFSET版本的公式,尽管多使用了两个函数N和IF。

现在,看看最后一个示例,该示例涉及到很少使用的CELL函数。在这里,我们尝试与第一个示例中的INDIRECT/ADDRESS函数组合类似的方式使用此函数,即指定参数info_type为“address”来获得要传递给SUM的一组单元格引用(当然是在通过INDIRECT函数处理后)。公式:

=SUM(INDIRECT(CELL("address",A1:A5)))

会解析为:

=SUM(INDIRECT("$A$1"))

这不是我们想要的结果。

像上文介绍的那样,先尝试在公式中添加OFFSET函数来解决这个问题:

=SUM(INDIRECT(CELL("address",OFFSET(A1,ROW(A1:A5)-MIN(ROW(A1:A5)),,,))))

这次仍没有达到我们预想的结果,尽管是数组公式输入,但这里的OFFSET函数并未操作ROW函数构造中的所有元素。实际上,公式中的:

ROW(A1:A5)-MIN(ROW(A1:A5))

我们希望返回的结果是:

{0;1;2;3;4}

但实际上不是这样,结果是:

0

等价于:

ROW(A1)-MIN(ROW(A1:A5))

只是处理了ROW(A1:A5)数组中的第一个元素。

奇怪!完全不是我们期望的数组公式,也不确定CELL函数拒绝接受生成数组输入的原因。不知道有没有人知道?

这样,现在需要采取更多的强制办法:

=SUM(INDIRECT(CELL("address",OFFSET(A1,N(INDEX(ROW(A1:A5)-MIN(ROW(A1:A5)),,)),,,))))

解析为:

=SUM(INDIRECT(CELL("address",OFFSET(A1,N(INDEX({0;1;2;3;4},,)),,,))))

解析为:

=SUM(INDIRECT(CELL("address",OFFSET(A1,N({0;1;2;3;4}),,,))))

解析为:

=SUM(INDIRECT(CELL("address",OFFSET(A1,{0;1;2;3;4},,,))))

解析为:

=SUM(INDIRECT({"$A$1";"$A$2";"$A$3";"$A$4";"$A$5"}))

这样,就与第一个示例一样了。

值得记住的是,无论强制性需求的程度如何,无论它是否必然涉及一个、两个甚至三个额外的函数,这些强制性函数很大程度上来自于INDEX、N(或T)和OFFSET函数的组合,并使用一些特定的语法。

注:本技巧整理自excelxor.com,有兴趣的朋友可以研阅原文,特别是原文后面的评论。

本文分享自微信公众号 - 完美Excel(excelperfect),作者:fanjy

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-03-04

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Excel公式练习72: 提取大写字母创建缩写

    导语:本次的练习整理自myspreadsheetlab.com,可以很好的帮助我们练练手!

    fanjy
  • Excel实用公式6:求每隔n行中的单元格之和

    很多时候,我们都可能想要对每隔n行中的单元格求和,其中n是一个整数。如下图1所示,每隔1行求和、每隔2行求和、每隔3行求和,等等。

    fanjy
  • Excel公式技巧23: 同时定位字符串中的第一个和最后一个数字

    在很多情况下,我们都面临着需要确定字符串中第一个和最后一个数字的位置的问题,这可能是为了提取包围在这两个边界内的子字符串。然而,通常的公式都是针对所需提取的子字...

    fanjy
  • Android样式的开发:Style篇

    前面铺垫了那么多,终于要讲到本系列的终篇,整合所有资源,定义成统一的样式。 哪些该定义成统一的样式呢?举几个例子吧:

    Keegan小钢
  • Android虚拟导航栏遮挡底部的输入框的解决方法

    最近忙着app的适配,在这个过程问题中,各种机型的奇葩问题都出来了,适配真尼玛痛苦!今天就oppo机型虚拟导航栏遮挡底部的输入框的问题作个记录。

    砸漏
  • 【专业技术】linux启动流程剖析

    半年前,我写了《计算机是如何启动的?》,探讨BIOS和主引导记录的作用。 那篇文章不涉及操作系统,只与主板的板载程序有关。今天,我想接着往下写,探讨操作系统接管...

    程序员互动联盟
  • Linux 的启动流程

    半年前,我写了《计算机是如何启动的?》,探讨BIOS和主引导记录的作用。 那篇文章不涉及操作系统,只与主板的板载程序有关。今天,我想接着往下写,探讨操作系统接管...

    ruanyf
  • 五类受自然启发的AI算法

    摘要: 本文主要讲解了受自然启发的五类AI算法以及各自的实际用例:神经网络、遗传算法、群集集体智慧、强化学习、人体免疫。 ? 搜索/寻路算法 搜索算法...

    小莹莹
  • Linux 启动流程分析

    这个部分比较有意思。因为在BIOS阶段,计算机的行为基本上被写死了,程序员可以做的事情并不多;但是,一旦进入操作系统,程序员几乎可以定制所有方面。所以,这个部分...

    良月柒
  • Linux 启动流程分析

    这个部分比较有意思。因为在BIOS阶段,计算机的行为基本上被写死了,程序员可以做的事情并不多;但是,一旦进入操作系统,程序员几乎可以定制所有方面。所以,这个部分...

    用户6543014

扫码关注云+社区

领取腾讯云代金券