excel公式教程:TRANSPOSE,非数组版本
发布时间:2023-08-19 01:07:36
有时候,我们想要寻找通常需要数组公式的非数组版本,其理由可能是:
1. 认为这样可以提高工作表的性能(有时可以,有时不能)
2. 不喜欢必须使用输入数组公式所必需的Ctrl+Shift+Enter组合键
3. 从理论上讲,可能只是感兴趣
本文研究数组公式的非数组版本,更多地属于第三类。强迫TRANSPOSE正常运行而不进行数组输入的必要强制措施令人费解且不切实际。这并不是说对它们没有兴趣,但这些强制性的使用并非TRANSPOSE独有。实际上,可以在许多函数中使用它们来生成返回值,否则将需要数组输入。
下面是一个示例。假设从单元格A2起其下的单元格相加,想要确定这样的和在哪个单元格等于或超过某个数,例如5。这些单元格依次相加的和显示在列B中,假设我们不希望在解决方案中使用这样的辅助列。并且,假设我们希望返回必须相加才能达到该数的单元格数,如下图1所示。
图1
从图1中可以清楚地看出,答案是3,因为前3个单元格中1+2+3=6大于我们给定的目标数5。
有两种标准的技术可以得到我们想要的结果。一种是使用数组公式:
=MATCH(TRUE,MMULT(0+(ROW(A2:A6)>=TRANSPOSE(ROW(A2:A6))),A2:A6)>5,0)
另一种是在公式中引入INDEX函数来代替数组公式(有关此技术的详细讨论,请参见《Excel公式技巧03:INDEX函数,给公式提供数组》)。公式为:
=MATCH(TRUE,MMULT(0+(ROW(A2:A6)>=TRANSPOSE(N(INDEX(ROW(A2:A6),,)))),A2:A6)>5,0)
注意,这里不仅添加了INDEX函数,也添加了N函数。
如果传递给TRANSPOSE的数组不是其他函数生成的,而是实际的单元格区域引用,那么对于等效的非数组的必要强制将更加复杂。例如,下面的示例:
图2
想要求出B1和A2、C1和A3、D1和A4、E1和A5的乘积之和,可直接使用公式:
=(B1*A2)+(C1*A3)+(D1*A4)+(E1*A5)
答案是70。
先试试SUMPRODUCT函数来简化求乘积之和的公式:
=SUMPRODUCT(B1:E1*A2:A5)
结果是260,显然是错的。这是因为传递给SUMPRODUCT函数的两个数组是正交的,所以求出来的和是这些数字交叉相乘后的和,如下图3所示。
图3
在B1:E1和A2:A5中的每个值依次相乘后的结果为260。
这样,我们需要将B1:E1和A2:A5进行转换,以确保这两个数组是相同类型的向量,即都是单行或都是单列。下面,尝试公式:
=SUMPRODUCT(B1:E1*TRANSPOSE(A2:A5))
得到的结果是78。并不是我们想要的结果,这个公式的中间结果为:
=SUMPRODUCT(B1:E1*3)
也就是说,TRANSPOSE函数仅返回数值3。
如果输入下面的数组公式:
=SUM(B1:E1*TRANSPOSE(A2:A5))
得到正确的结果70。上面的公式能够像我们预想的那样转换:
=SUM({5,6,7,8}*{1,2,3,4})
如果我们不输入本文开头的连乘和的公式(对于大量的数据是不可取的)或者刚才的数组公式,那么能够编写其他公式获得正确的结果吗?
使用非数组公式:
=SUMPRODUCT(B1:E1,TRANSPOSE(INDEX(A2:A5,N(IF(1,INDEX(ROW(A2:A5)-MIN(ROW(A2:A5))+1,,))))))
得到正确的结果70。
我们可以仔细地研究一下本文展示的技术,它们既可以与TRANSPOSE函数结合使用,也可以应用于其他函数。不推荐单纯为避免输入数组公式而想方设法采用其他公式,然而研究一下非数组公式解决问题也是一项有趣的练习!
猜你喜欢
- Excel表格怎么快速圈出高于80分的成绩?excel表格中的数据想要圈出高于80分的成绩,该怎么快速圈出符合条件的数据呢?下面我们就来看看
- 许多小伙伴知道有ahci功能可以提高硬盘的性能,但他们只知道一点点,却不知道如何启用它。ahci功能就像一个加速器,那么如何启用这个功能呢?
- 有时我们打开word时会出现一个对话框,“Microsoft Office Word 遇到问题需要关闭。我们对此引起的不便表示抱歉。”步骤/
- win10系统如何查看电脑显存呢?很多小伙伴对此还不是很清楚,小编这里就给大家带来有关win10系统如何查看电脑显存的回答,希望能够对大家有
- 在 2007 Microsoft Office system 中,您可以使用密码防止其他人打开或修改文档、工作簿和演示文稿。请记住,Micr
- wps是一款很好用的文字编辑软件,并且深度兼容word,很受大众喜欢。今天这里要分享一下,如何设置wps的行间距。当我们在日常的办公中,如果
- 长期以来,我们一直在抱怨微软,没有提供一个比&和函数CONCATENATE更方便的连接文本的函数,如,我们一直不能轻易地将一个区域内
- Win10电脑第一次连接网络要怎么设置网络?许多用户在使用电脑连接宽带网络的时候,都不知道如何连接如何拨号,导致自己无法连接宽带网络。下面小
- project2013怎么激活?是什么新用户都想知道的问题,今天脚本之家小编就给大家带来microsoft project Pro 2013
- 在使用电话、短信、邮件、QQ时代,收到一封真迹书信一定会激动不已。等拿起笔给父母写信时才发现昔日书法“练家子”的我写出的字还没阿Q画的圆那么
- 又一次出现这个问题:word无法读取文档,文档可能损坏。估计是上次断电造成的。以下是解决办法可以试试:上面提示打开并修复文件,我就想,应该可
- Excel怎么使用And函数检查数据是否满足条件?Excel中想要知道一组数据是否同事满足条件,该怎么判断呢?下面我们就来看看使用and函数
- 通常我们在使用WPS文字或者Word时,可能会因为复制而产生很多的回车符;或者从网页复制的文字粘入Word时候,会带来大量换行符。怎样能批量
- 先讲解下什么叫三线表:三线表以其形式简洁、功能分明、阅读方便而在科技论文中被推荐使用。三线表通常只有3条线,即顶线、底线和栏目线(见图,注意
- 大家有没有发现,Excel中没有字号的名称,只有字号的磅值。Excel中默认字号是11磅,如图所示Excel中的字号都是用磅值表示Word中
- 1、自动保存功能office365推出了一个新功能,叫“自动保存”,解决了忘记保存和误编辑,又点击了保存,想要回退时的问题。如下图所示:
- Excel2007中合并单元格的方法步骤一、选中需要合并的几个单元格,然后右键选择“设置单元格格式”; Excel
- 如何用OCR技术把车牌上的文字识别出来?如果我们想对车牌进行识别的话,哪个OCR识别软件可以识别车牌?下面就来给大家推荐一款非常好用的OCR
- 1、首先我们应该先打开需要进行设置目录的文件夹。2、在打开相关的文件夹后,我们找到菜单栏中的“引用”。3、我们选择需要设计一级目录的文字,点
- 1、选中需要设置的文本框;2、在自定义动画窗格中,选择添加效果----进入,在弹出的选项中选择一种进入效果;3、单击如图所示的倒三角,在下拉