英文:
How to correctly expand Excel formula if number of elements in sum must change when expanding?
问题
我有一个非常简单的示例来演示（我的问题的核心）。请参考以下表格：
| A | B | C | |
|---|---|---|---|
| 1 | 1951 | TR-Price S&P 500 | |
| 2 | 1950 | 150 | |
| 3 | 1951 | 152 | |
| 4 | 1952 | 151 | |
| 5 | 1953 | 157 | |
| 6 | 1954 | 155 | |
| 7 | 1955 | 159 | |
| 8 | |||
| 9 | 年度循环投资 | ||
| 10 | $1 |
目标是根据起始年份（字段B1）和结束年份（字段A2到A7），根据字段C10中的年度重复投资，计算在字段B2到B7之间的总投资组合规模。假设每年都会根据字段C10进行投资。这意味着我想要计算每个起始年和结束年之间导致的B2到B7的复利利息。例如，字段B6代表从1951年到1954年的投资期间。但是利率并不是恒定的。相反，每年的有效利率是根据相应年份的列C中的TR-Price变化而得出的。
正确结果的示例：
- 对于B2字段：零或n/a（投资开始年份晚于结束年份）
- 对于B3字段：零或n/a（投资开始年份等于结束年份）
- 对于B4字段：$0.9934（计算：$1 * 151/152，代表从1951年到1952年的复利利息）
- 对于B5字段：$2.0726（计算：$1 *（157/152 + 157/151），代表从1951年到1953年的复利利息）
因此，对于B4字段，可以在Excel中使用以下公式进行计算：
= $C$10 * ($C4/$C$3)，因为只有一年的复利利息（从1951年到1952年=> 152减少到151）。
但是对于B5字段，计算需要使用类似以下的公式：
= $C$10 * ($C5/$C$3 + $C5/$C$4)，因为有第一个美元，在两年的复利利息（从1951年到1953年，152增加到157），以及第二个美元，在最后一年的复利利息（从1952年到1953年，151增加到157）。
**这带来了我的问题：**如何以一种只需创建单个通用公式的方式来实现这一点，例如在B4字段中，然后将其向下扩展？问题在于，如果起始年和结束年之间的距离发生变化，公式中的元素数量必须发生变化。Excel是否能够做到这一点，还是我需要一些具有for循环的VBA宏？
英文:
I have a very simple example to demonstrate (the core of) my problem. Please see the following table:
| A | B | C | |
|---|---|---|---|
| 1 | 1951 | TR-Price S&P 500 | |
| 2 | 1950 | 150 | |
| 3 | 1951 | 152 | |
| 4 | 1952 | 151 | |
| 5 | 1953 | 157 | |
| 6 | 1954 | 155 | |
| 7 | 1955 | 159 | |
| 8 | |||
| 9 | yearly recurring investment | ||
| 10 | $1 |
The goal is to calculate the total portfolio size within the fields B2 to B7, based on the start year (field B1) and the end year (fields A2 to A7). It is assumed that a yearly recurring investment according to field C10 is invested. This means I want a calculation of the compounded interest for all combinations of start year and end year, which result between B2 and B7. For example field B6 would represent the investing period from 1951 to 1954. But there's not a constant rate of interest. Instead the effective rate for each year results from the TR-Price change in Column C for the corresponding years.
Examples for correct results:
- for field B2: zero or n/a (start year of investment later than end year)
- for field B3: zero or n/a (start year of investment equal than end year)
- for field B4: $0.9934 ( calculation: $1 * 151/152, representing compunded interest from 1951 to 1952)
- for field B5: $2,0726 ( calculation: $1 * (157/152 + 157/151), representing compunded interest from 1951 to 1953)
So as for field B4 the calculation could be done in Excel via:
= $C$10 * ($C4/$C$3) as there's only one year of compunded interest (from 1951 to 1952 => decrease of 152 to 151).
But for B5 the formula the calculation needs to be done with something like:
= $C$10 * ($C5/$C$3 + $C5/$C$4) as there is the first dollar, which obtains an increase by two years of compunded interest (from 152 to 157 during 1951 to 1953) and the second dollar, which obtains the latest year of compunded interest (from 151 to 157 during 1952 to 1953).
Which brings me to my question: How can implement this in a way that I only need to create a single universal formula - e.g. in field B4 - which can then be expanded downwards? The problem is: the number of elements within the formula must change if the distance between start and end year changes. Is Excel capable of something like that or will I need some VBA Macro with a for loop?
答案1
得分: 0
根据您上面的描述，您可以在单元格B2中使用下拉公式如下：
=IF(A2<=B$1,"",SUM(C2/INDEX(C$2:C$10,MATCH(B$1,A$2:A$10)):C1))
英文:
According to your description above you could use a pull-down formula like this in B2:
=IF(A2<=B$1,"",SUM(C2/INDEX(C$2:C$10,MATCH(B$1,A$2:A$10)):C1))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论