特别的Excel公式，返回一组数据的累加值

excelperfect

引言：本文的练习整理自chandoo.org。多练习，这是我们从小就在使用的学习方法。在练习的过程中，认真思考，不断尝试，以此来磨练自己的公式与函数应用技能，也让研究Excel的大脑时刻保持着良好的状态。同时，想想自己怎么解决这个问题，看看别人又是怎样解决的，从而快速提高Excel公式应用水平。

Q：在一列中有一系列数值，能否使用公式返回由累加值组成的数组。例如下图1所示的数据，返回数组{3;11;16;17;23}。

图1

规则：1.使用一个公式返回累加值组成的数组，而不能使用多个公式。例如，使用：=SUM($A$1:A1)

=SUM(OFFSET($A$1,,,ROWS($A$1:A1),1))

向下拖拉所得到的数值，不符合要求。

2.不使用易失性函数。

写下你的公式。

A：

公式1：数组公式。

=MMULT(--(ROW(A1:A5)>=TRANSPOSE(ROW(A1:A5))),A1:A5)

或者：

=MMULT(N(ROW(A1:A5)>=TRANSPOSE(ROW(A1:A5))),A1:A5)

注意，公式中的矩阵转换技巧。

公式2：数组公式。

=SUM($A$1:INDEX($A$1:$A$5,ROW($1:$5)))

公式3：数组公式。

=PROB(ROW(A1:A5),A1:A5/SUM(A1:A5),,ROW(A1:A5))*SUM(A1:A5)

第一次接触到PROB函数。

公式4：数组公式。

=PROB(IF(FREQUENCY(1,0*A1:A5),,ROW(A1:A5)),IF(FREQUENCY(1,0*A1:A5),1-SUM(A1:A5),A1:A5),1,ROW(A1:A5))

公式5：数组公式。

=PROB(ROW(A1:A5)*{1,0,0,0},A1:A5*{1,0,0,0}+{0,9,-9,1}^99,1,ROW(A1:A5))

下面的公式使用了易失性函数：

=SUBTOTAL(9,(OFFSET(A$1:A5,,,ROW(INDIRECT('1:5')),1)))

=SUBTOTAL(109,OFFSET(A1,,,ROW($1:$5)))

=SUMIF(OFFSET(A1,,,ROW($1:$5)),'>0')

大家可以使用F9键或者公式求值功能自行解析，以理解公式的运作原理，从而更好地理解公式和所使用的函数。