I'm relatively new to excel programming. I'm working on making a spread sheet that shows exponential decay. I have one column (A1:A1000) of 1000 random numbers between 1 & 10 using the TRUNC(RAND()*10,0) in each cell. The next Column (B1:B1000) has a logic mask =IF(A1=0,1,0) , where if the value in the A cell is 0, then the B cell shows a 1. Next, to find the number of 0's in the A column, I have the next column taking the sum of B1:B1000, which returns the number of 0's that showed up in the first column. I'm sure there's an easier way to do that, but this seems to work fine.
我对excel编程比较陌生。我正在做一个显示指数衰减的展板。我在每个单元格中使用TRUNC(RAND()*10,0)，有一列(A1:A1000)在1到10之间的1000个随机数。下一列(B1:B1000)有一个逻辑掩码=IF(A1=0,1,0)，其中如果a单元格中的值为0，则B单元格显示1。接下来，为了求A列中0的个数，我让下一列取B1:B1000的和，它返回第一列中出现的0的个数。我确信有一种更简单的方法可以做到这一点，但这似乎很有效。
Here's my problem, hopefully it's clear what I'm asking:
这是我的问题，希望我的问题是清楚的:
Next, I want to take the sum of the logic column (B) from B1:B(1000- the value of the sum from (B1:1000)) in the cell below the cell that calculates sum(B1:B1000). Is there a way to to algebra in a cell formula to reference a cell? More simply, if I want to refer to A3, for example, is there a way to input something like A(2+1) to get A3? Does this make sense?
接下来，我想取逻辑列(B)在计算和(B1:B1000)的单元格下面的单元格(B1:B1000)的和。是否有一种方法可以在计算单元公式中引用单元格?更简单地说，如果我想引用A3，是否有办法输入a(2+1)来得到A3?这是否有意义吗?
2 个解决方案
#1
12
You can very easily do, in VBA:
在VBA中很容易做到
ActiveCell.Formula = "=A" & (2+1) & "+15"
In Excel:
在Excel中:
=INDIRECT(ADDRESS(2+1, COLUMN(A1)))+15
This will set the formula to "=A3+15". Generally, this is best done with variables, so remember to do that.
这将把公式设置为“=A3+15”。一般来说，这是用变量做的最好的方法，所以记住要这么做。
#2
0
In Excel, and as pointed out by Eric, you can write the referance to the cells just like normal strings thanks to INDIRECT() function.
在Excel中，正如Eric指出的，通过使用间接()函数，您可以像编写普通字符串一样编写单元格的引用。
Just make sure that the string passed to INDIRECT() is a valid cell reference.
只需确保传递给间接()的字符串是一个有效的单元引用。
For example :
例如:
=SUM(INDIRECT("B" & 2+7*(H2-1)):INDIRECT("B"&(2+7*H2)-1))
=总和(间接(" B " & 2 + 7 *(H2-1)):间接(" B " &(2 + 7 * H2)1))
Here, I sum 7 lines for each week (H2). It gives the sum of B2:B8, B9:B15, B16:B22, etc.
在这里，我每周加7行(H2)。它给出的是B2:B8, B9:B15, B16:B22，等等。
Hope my example will help you figure out how to use it in real situation.
希望我的示例能帮助您理解如何在实际情况中使用它。
#1
12
You can very easily do, in VBA:
在VBA中很容易做到
ActiveCell.Formula = "=A" & (2+1) & "+15"
In Excel:
在Excel中:
=INDIRECT(ADDRESS(2+1, COLUMN(A1)))+15
This will set the formula to "=A3+15". Generally, this is best done with variables, so remember to do that.
这将把公式设置为“=A3+15”。一般来说，这是用变量做的最好的方法，所以记住要这么做。
#2
0
In Excel, and as pointed out by Eric, you can write the referance to the cells just like normal strings thanks to INDIRECT() function.
在Excel中，正如Eric指出的，通过使用间接()函数，您可以像编写普通字符串一样编写单元格的引用。
Just make sure that the string passed to INDIRECT() is a valid cell reference.
只需确保传递给间接()的字符串是一个有效的单元引用。
For example :
例如:
=SUM(INDIRECT("B" & 2+7*(H2-1)):INDIRECT("B"&(2+7*H2)-1))
=总和(间接(" B " & 2 + 7 *(H2-1)):间接(" B " &(2 + 7 * H2)1))
Here, I sum 7 lines for each week (H2). It gives the sum of B2:B8, B9:B15, B16:B22, etc.
在这里，我每周加7行(H2)。它给出的是B2:B8, B9:B15, B16:B22，等等。
Hope my example will help you figure out how to use it in real situation.
希望我的示例能帮助您理解如何在实际情况中使用它。