excel技巧:强制返回数组的公式技术
发布时间:2023-05-02 17:28:13
有时候,我们希望将公式应用于一组值而不是一个值,这可以简单地将公式作为数组公式(按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函数的组合,并使用一些特定的语法。
猜你喜欢
- 当计算机出现异常或错误提示时,将自动记录任务日志。我们可以随时随地通过日志查看电脑的状态,使用起来非常方便~win10日志怎么查看:第一种方
- iPhone的功能很强大,不仅可在相册或其他app打开的图片上用它识别文字和数据,还能直接开启相机app用它挖掘真实世界的文字信息。今天我们
- 系统升级到win10以后,打不开office2013文件,其实出现这种问题跟要打开的文件本身没有多大关系,只是Office 2013到了Wi
- Excel2016怎么更改标识错误的颜色?这篇文章主要介绍了Excel2016设置错误颜色教程,需要的朋友可以参考下 Excel2
- Q:昨天都好好的,不知道动了什么,今天双击Excel文件时不能打开了,老是报错,如图1所示,非要先启动Excel程序,然后单击“文件——打开
- 在excel的过程使用过程中,经常遇到文本转数字,或者数字转文本的操作,如果文本转化不成数字,那么就无法进行求和,数值转化不成文本,就可能出
- Excel2016怎么计算开根号?excel中想要计算二次方根,该怎么计算呢?下面我们就来看看Excel2016二次方根的两种计算方法,很简
- 如果你在Excel工作表中需要输入大量的诸如“3+7+9”之类的算式,而且要求单元格中显示其计算结果,于是你在每个算式前都得键入一个等号“=
- 平时我们只是在Word文档中排版,处理一些文字,大部分表格之类的工作都会在Excel中去完成。但有时候一些简单表格还是会用Word来做,今天
- 很多人是很讨厌自己设计的,自己又不懂得设计。所以绝大多数时候,人们都是在网络上寻找简历素材来套用。如果你选中的素材是jpg格式的文件,并且你
- 在微信公众号上看推文有些小伙伴看到内容不错的就想要将其复制到文档中打印出来留存,但是无论在微信里怎么复制一旦粘贴到文档内图片总是显示不了,只
- 为了防止他人剽窃或是窜改Word文档中的内容,最常用的办法就是给Word文档添加密码,有了密码文档是很安全,但是时间长了难免有可能会忘记密码
- 在excel中分页预览虚线拉不动怎么办?你是否遇到这样的问题,想左右拖动调整,但每次调整之后都自动还原,这个问题怎么解决呢?1、预览虚线拉不
- 1、选择【插入】菜单 2、在【插入】菜单下最右边选择【公式】下拉列表——选择【插入新公式】 &nbs
- 一、快速插入可以通过“插入——符号”菜单,选择“宋体——拉丁语-1”便可以看到×÷号了,选中符号后,单击插入,或者直接双击,就可插入到文档中
- Excel中如果带有超链接,最简单直接的办法就是选中单元格,单击右键选择“取消超链接”,但这样只能一个单元格一个单元格地操作,如果数量巨大,
- 轻快PDF阅读器怎么给pdf文件添加书签?pdf文档需要添加书签,该怎么添加呢?下面我们就来看看轻快PDF阅读器添加书签的教程,需要的朋友可
- Win10系统bitlocker加密密钥无法保存怎么办?很多用户为了防止密钥的丢失,都会选择将bitlocker加密密钥保存在微软账户中,但
- 很多小伙伴拿到电脑之后都想要把自己的电脑装饰成自己喜欢的样子,比如给电脑更换壁纸、更换桌面图标、使用个性化字体等等。但是最近很多小伙伴跟小编
- word是我们常用的办公软件,有时需要给内容添加一些表格,那么word中怎样给内容添加表格?下面小编带来word中给数据加上表格的设置方法