我尝试在Excel中使用N个值执行以下操作:
http://upload.wikimedia.org/math/b/a/d/bad5db400fcfd7092e2008e376993a27.png
我可以使用=COMBIN(2*N;N)/(N+1)来制作Ci,但考虑到我的N>=0,如何制作N-I-1?
感谢
这是可能的,但很草率,因为Excel不容易允许您使用反向数组(例如,如果您输入B5:B1,Excel会将其转换为B1:B5)。
例如,在伪Excel表示法中
C9=C0*C8+C1*C7+C2*C6+…="消耗产品(C0:C8,C8:C0)"
但是C8:C0是不允许的。要得到类似C8:C0的东西,我们必须使用offset
函数。你可以在谷歌上搜索offset
,但这里有一些例子,
ROW(B4)-ROW(B$2:B4) = 4 - [2,3,4] = [2,1,0]
OFFSET(B$2, ROW(B4)-ROW(B$2:B4), 0, 3, 1) = OFFSET(B$2, [2,1,0], 0, 3, 1) = t[B4,B3,B2]
OFFSET(B$2, ROW(B4)-ROW(B$2:B4), 1, 3, 1) = t[C4,C3,C2]
OFFSET(B$2, ROW(B4)-ROW(B$2:B4), -1, 3, 1) = t[A4,A3,A2]
OFFSET(B$2, ROW(B4)-ROW(B$2:B4), 0, 2, 1) = t[B4,B3]
OFFSET(B$2, ROW(B4)-ROW(B$2:B4), 0, 1, 1) = t[B4]
这是一个使用公式计算加泰罗尼亚数字的示例工作表:
A B
1 N C_n
--------
2 0 =1
3 1 =SUMPRODUCT(B$2:B2, N(OFFSET(B$2, ROW(B2)-ROW(B$2:B2),0,A3,1)))
4 2 =SUMPRODUCT(B$2:B3, N(OFFSET(B$2, ROW(B3)-ROW(B$2:B3),0,A4,1)))
5 3 =SUMPRODUCT(B$2:B4, N(OFFSET(B$2, ROW(B4)-ROW(B$2:B4),0,A5,1)))
6 4 cut-and-paste