代码之家 › 专栏 › 技术社区 › VoiceOfUnreason

excel公式中的行反转

excel

VoiceOfUnreason · 技术社区 · 15 年前

相反,我想要的是

(B) 积(A1:A3,反向(B1:B3))==A1*B3+A2*B2+A3*B1

在Excel2003/Excel2007中是否有一种干净的方法来实现这一点?这些值的自然顺序为A1->A3和B1->B3,因此颠倒单元格的含义并不令人满意;但是在工作表的其他地方创建B1:B3的反向副本似乎很笨拙。

3 回复 | 直到 15 年前

Oddthinking 15 年前

我看不到一个解决方案不涉及(a)VBA中的自定义函数(或类似函数)或(b)具有部分结果的额外列。

如果您不喜欢C列变成(隐藏的)反向列表,您会接受C列变成这样的列表:A1*B3、A2*B2、A3*B1,然后可以求和吗?可以使用与@e.tadeu的答案中提到的公式类似的公式(使用OFFET和ROW函数)

Community CDub 7 年前

http://www.cpearson.com/EXCEL/lists.htm

user1938578 9 年前

我偶然遇到这个问题,因为我正试图找到这个问题的答案,特别是一个用于SUMPRODUCT的解决方案。

上一篇带有链接的帖子很有用,下面的内容已经设计好了。请注意,为了简单明了,原始参考已被更改,以使电子表格公式更易于理解。如果需要对列应用反转,则可以通过使用间接和R1C1引用样式来实现必要的更改(间接、列和列函数文档将证明是有用的)。

=SUMPRODUCT(A6:A8,N(OFFSET(B6:B8,ROWS(B6:B8)-ROW(INDIRECT("A1:A"& ROWS(B6:B8))),0)))

Excel的反转部分是 N(OFFSET(B6:B8,ROWS(B6:B8)-ROW(INDIRECT("A1:A"& ROWS(B6:B8))),0)) ,它也可以在任何位置用作多单元格数组公式(ctrl-shift-enter)-要反转的数组位于B6:B8中。

N()

偏移量获取电子表格上的数组,并将值按相反顺序放置(在这种情况下,3-1=2处的原始偏移量变为新的数组位置0,3-2=1变为1,最后3-3=0变为2。)ROW()函数创建一个连续数字数组,可以从总数组的长度中减去该数组[请注意,没有最终的S,并且A1:A3已用于获得{1,2,…},最多为原始数组/范围中的行数。]

此公式在OpenOffice/LibreOffice电子表格应用程序中不起作用。但是,随着模拟方法变得更加灵活,索引也可以变得更加灵活。

OpenOffice/LibreOffice可以使用相同的方法,但具有索引功能。OpenOffice/LibreOffice解决方案在Excel中不起作用,因为Excel不接受索引的行\u num/col\u num中的数组(任何作为参数的数组都将成为数组的单个顶部元素)。

=SUMPRODUCT(A6:A8,INDEX(B6:B8,1+ROWS(B6:B8)-ROW(INDIRECT("A1:A"& ROWS(B6:B8))),0))

OpenOffice的反转部分是 INDEX(B6:B8,1+ROWS(B6:B8)-ROW(INDIRECT("A1:A"& ROWS(B6:B8))),0) ,它也可以在任何位置用作多单元格数组公式(ctrl-shift-enter)-要反转的数组位于B6:B8中。

使用此方法的Excel版本(谢谢,XOR LX: see here

=SUMPRODUCT(A6:A8,INDEX(B6:B8,N(INDEX(1+ROWS(B6:B8)-ROW(INDIRECT("A1:A"& ROWS(B6:B8))),,)),0))

[与上面Excel的偏移版本一样,带有N(索引([…],)修改的公式在OpenOffice中不起作用,如果使用该应用程序,则必须删除包装函数。]

该方法类似于Excel中用于偏移的方法。考虑到索引使用从1开始的索引,而偏移量从0开始。与前一种情况一样,从A1:A3中的行号动态创建一个数组,用作数组和位置更改索引的模板。

这个迟来的答案不太可能对原始海报有所帮助,但它可能会为未来的用户节省时间,让他们回答类似的问题。