I have a Macro in my Excel and this Macro writes a formula to one cell in my Excel sheet. The code of my Macro that does this is like below:
我在Excel中有一个宏这个宏将公式写到Excel表格中的一个单元格中。我的宏的代码如下:
Range("F10").Select
ActiveCell.FormulaR1C1 = "=SUMIF('1'!C2,C7,'1'!C[2])"
and the Formula produced for the F10 cell is like below:
F10细胞的公式如下:
=SUMIF('1'!$B:$B,$G:$G,'1'!H:H)
Can someone help me understand this code? What does '1' , ! , [] and ... do exactly? In general I want to know what is the output of " =SUMIF('1'!$B:$B,$G:$G,'1'!H:H)" and why the macro generates this formula?
有人能帮我理解这个代码吗?“1”是什么意思?[],…到底是什么?一般来说，我想知道" =SUMIF('1'!$B:$B，$G:$G，'1'!H:H)的输出是什么，为什么宏生成这个公式?
2 个解决方案
#1
5
You have chosen unfortunate sheet names.
你选择了不幸的被单名。
"=SUMIF('1'!C2,C7,'1'!C[2])"
”=条件求和(' 1 ' ! C2,C7,' 1 ' ! C[2])”
The '1' is the sheet name. Sheet names can be wrapped in single quotes, but you only need to do that if the sheet name contains a space.
“1”是表名。表名可以用单引号括起来，但您只需要在表名包含空格时这样做。
The ! is a separator between sheet name and cell reference.
!是表名和单元格引用之间的分隔符。
C2 means column 2, which is column B. You are using R1C1 syntax for the formula, so the C identifies the following number as related to a column.
C2表示第2列，也就是b列，在公式中使用R1C1语法，因此C将下面的数字标识为与列相关的数字。
C7 means the 7th column, so, column G.
C7表示第7列，也就是G列。
'1'! again means the sheet name and the separator.
' 1 ' !再次表示表名和分隔符。
C[2] means from the current column (the C) go 2 to the right. If the formula returns column H for that, it means that the current cell must be in column F.
C[2]表示从当前列(C)向右2。如果公式为此返回列H，则意味着当前单元格必须在列F中。
R1C1 referencing is not widely used in worksheets these days, but in VBA it makes relative cell referencing very easy. The brackets mean "relative to the current location" and the R or C will determine whether it's a row or column that needs to be traversed.
现在，R1C1引用在工作表中并不广泛使用，但是在VBA中，它使相对单元格引用非常容易。括号表示“相对于当前位置”，R或C将决定它是需要遍历的行还是列。
R1C1 is Row 1, column 1, in other words, cell A1. This is an absolute reference.
R1C1是第1行，第1列，换句话说，单元格A1。这是绝对的参考。
R[4]C[-2] is a relative reference. It will be evaluated based on the current cell and from that position will add 4 rows (positive value 4 for rows) and subtract 2 columns (negative value -2 for columns). So, if the current cell is H4, which translates to R4C8, a reference to R[4]C[-2] means F2.
R[4]C[-2]是一个相对的参考。它将根据当前单元格进行计算，从该位置将添加4行(行为正值4)并减去2列(列为负值-2)。因此，如果当前单元格为H4，即R4C8，则对r[4]C[-2]的引用表示F2。
#2
2
So I'm assuming you know what
我假设你知道什么
=sum($B:$B)
does? If not, then you need to take a basic Excel tutorial before aksing this quesiton.
呢?如果没有，那么在回答这个问题之前，您需要学习一个基本的Excel教程。
!
The $B:$B in my formula is the range. It refers to the range that is the enitre column B on the current worksheet. If you wanted to sum up column B on a different worksheet then you specify that range like worksheetname!$B$B or if the worksheet name has spaces in it then use quotes like 'worksheet name'!$B$B.
B:我的公式里的B是范围。它指的是当前工作表上的enitre列B的范围。如果您想对不同工作表上的B列进行汇总，那么您可以指定工作表名称之类的范围!$B或如果工作表名称中有空格，则使用“工作表名称”之类的引号!$B$B。
Hence '1'!$B$B refers to column B on a worksheet named '1'
因此' 1 ' !$B指的是工作表“1”上的B列
[]
So what do the square brakcets do? That is from the R1C1 notation. So normally you reference a range absolutely. So when you use a range like $B$B, you are refereing to column B no matter where you call that function from (i.e. no matter which cell your function is in). R1C1 notation is Excel's way of allowing to refer to cells relatively, or in other words refer to their locations relative to the cell your formula is written in. c2 in R1C1 notation is actually still absolute, it means the second column (i.e. B). c[2] is now relative, the [2] means two columns to the right of the cell your formula is in.
那么方形的brakcets是做什么的呢?这是R1C1符号。通常情况下，你绝对是指一个范围。因此，当你使用一个范围，比如$B$时，无论你从哪里调用这个函数(也就是说，无论你的函数在哪个单元格中)，你都是在引用B列。R1C1表示法是Excel的一种方法，它允许相对地引用单元格，或者换句话说，是指相对于你的公式所写的单元格的位置。R1C1中的c2仍然是绝对的，它表示第二列(即B) c[2]现在是相对的，[2]表示你的公式在单元格右边的两列。
SUMIF
SUMIF is an excel formula that only sums up the portion of a range that meets a certain condition. In your case we cannot say what it will output because we don't know what is in your sheet. But basically for
SUMIF是一个excel公式，它只计算满足一定条件的范围的一部分。在你的情况下，我们不能说它会输出什么，因为我们不知道你的表格里有什么。但基本上
=SUMIF('1'!$B:$B,$G:$G,'1'!H:H)
The '1'!$B:$B is the range of values you want to test your condition against, the $G:$G is your condition (which I think should point to a single cell rather than a range btw...) and finally '1'!H:H is the range of values that you want to sum.
' 1 ' !$B:$B是你想要测试你的条件的值的范围，$G:$G是你的条件(我认为应该指向一个单元，而不是一个范围的btw…)，最后是“1”!H:H是你想要求和的值的范围。
So if you want to sum values in columns H only when the values in column B are greater than 10 and you sheet 1 looks like
所以如果你想要在H列中求和只有当B列的值大于10时你的表1是这样的
B G H
________________
1 | 5 '>10 1
2 | 12 2
3 | 13 3
The result of =SUMIF('1'!$B:$B,$G:$G,'1'!H:H) will be 5 (i.e. adding 2 and 3 from column H because in column B 12 and 13 were larger than 10 but 5 was not and thus does not meet the criteria for being part of the sum.
=SUMIF('1'!$B:$B，$G:$G，'1'!H:H)的结果为5(即从H列中加入2和3，因为B 12和13列大于10，但5没有，因此不符合作为该金额一部分的标准。
#1
5
You have chosen unfortunate sheet names.
你选择了不幸的被单名。
"=SUMIF('1'!C2,C7,'1'!C[2])"
”=条件求和(' 1 ' ! C2,C7,' 1 ' ! C[2])”
The '1' is the sheet name. Sheet names can be wrapped in single quotes, but you only need to do that if the sheet name contains a space.
“1”是表名。表名可以用单引号括起来，但您只需要在表名包含空格时这样做。
The ! is a separator between sheet name and cell reference.
!是表名和单元格引用之间的分隔符。
C2 means column 2, which is column B. You are using R1C1 syntax for the formula, so the C identifies the following number as related to a column.
C2表示第2列，也就是b列，在公式中使用R1C1语法，因此C将下面的数字标识为与列相关的数字。
C7 means the 7th column, so, column G.
C7表示第7列，也就是G列。
'1'! again means the sheet name and the separator.
' 1 ' !再次表示表名和分隔符。
C[2] means from the current column (the C) go 2 to the right. If the formula returns column H for that, it means that the current cell must be in column F.
C[2]表示从当前列(C)向右2。如果公式为此返回列H，则意味着当前单元格必须在列F中。
R1C1 referencing is not widely used in worksheets these days, but in VBA it makes relative cell referencing very easy. The brackets mean "relative to the current location" and the R or C will determine whether it's a row or column that needs to be traversed.
现在，R1C1引用在工作表中并不广泛使用，但是在VBA中，它使相对单元格引用非常容易。括号表示“相对于当前位置”，R或C将决定它是需要遍历的行还是列。
R1C1 is Row 1, column 1, in other words, cell A1. This is an absolute reference.
R1C1是第1行，第1列，换句话说，单元格A1。这是绝对的参考。
R[4]C[-2] is a relative reference. It will be evaluated based on the current cell and from that position will add 4 rows (positive value 4 for rows) and subtract 2 columns (negative value -2 for columns). So, if the current cell is H4, which translates to R4C8, a reference to R[4]C[-2] means F2.
R[4]C[-2]是一个相对的参考。它将根据当前单元格进行计算，从该位置将添加4行(行为正值4)并减去2列(列为负值-2)。因此，如果当前单元格为H4，即R4C8，则对r[4]C[-2]的引用表示F2。
#2
2
So I'm assuming you know what
我假设你知道什么
=sum($B:$B)
does? If not, then you need to take a basic Excel tutorial before aksing this quesiton.
呢?如果没有，那么在回答这个问题之前，您需要学习一个基本的Excel教程。
!
The $B:$B in my formula is the range. It refers to the range that is the enitre column B on the current worksheet. If you wanted to sum up column B on a different worksheet then you specify that range like worksheetname!$B$B or if the worksheet name has spaces in it then use quotes like 'worksheet name'!$B$B.
B:我的公式里的B是范围。它指的是当前工作表上的enitre列B的范围。如果您想对不同工作表上的B列进行汇总，那么您可以指定工作表名称之类的范围!$B或如果工作表名称中有空格，则使用“工作表名称”之类的引号!$B$B。
Hence '1'!$B$B refers to column B on a worksheet named '1'
因此' 1 ' !$B指的是工作表“1”上的B列
[]
So what do the square brakcets do? That is from the R1C1 notation. So normally you reference a range absolutely. So when you use a range like $B$B, you are refereing to column B no matter where you call that function from (i.e. no matter which cell your function is in). R1C1 notation is Excel's way of allowing to refer to cells relatively, or in other words refer to their locations relative to the cell your formula is written in. c2 in R1C1 notation is actually still absolute, it means the second column (i.e. B). c[2] is now relative, the [2] means two columns to the right of the cell your formula is in.
那么方形的brakcets是做什么的呢?这是R1C1符号。通常情况下，你绝对是指一个范围。因此，当你使用一个范围，比如$B$时，无论你从哪里调用这个函数(也就是说，无论你的函数在哪个单元格中)，你都是在引用B列。R1C1表示法是Excel的一种方法，它允许相对地引用单元格，或者换句话说，是指相对于你的公式所写的单元格的位置。R1C1中的c2仍然是绝对的，它表示第二列(即B) c[2]现在是相对的，[2]表示你的公式在单元格右边的两列。
SUMIF
SUMIF is an excel formula that only sums up the portion of a range that meets a certain condition. In your case we cannot say what it will output because we don't know what is in your sheet. But basically for
SUMIF是一个excel公式，它只计算满足一定条件的范围的一部分。在你的情况下，我们不能说它会输出什么，因为我们不知道你的表格里有什么。但基本上
=SUMIF('1'!$B:$B,$G:$G,'1'!H:H)
The '1'!$B:$B is the range of values you want to test your condition against, the $G:$G is your condition (which I think should point to a single cell rather than a range btw...) and finally '1'!H:H is the range of values that you want to sum.
' 1 ' !$B:$B是你想要测试你的条件的值的范围，$G:$G是你的条件(我认为应该指向一个单元，而不是一个范围的btw…)，最后是“1”!H:H是你想要求和的值的范围。
So if you want to sum values in columns H only when the values in column B are greater than 10 and you sheet 1 looks like
所以如果你想要在H列中求和只有当B列的值大于10时你的表1是这样的
B G H
________________
1 | 5 '>10 1
2 | 12 2
3 | 13 3
The result of =SUMIF('1'!$B:$B,$G:$G,'1'!H:H) will be 5 (i.e. adding 2 and 3 from column H because in column B 12 and 13 were larger than 10 but 5 was not and thus does not meet the criteria for being part of the sum.
=SUMIF('1'!$B:$B，$G:$G，'1'!H:H)的结果为5(即从H列中加入2和3，因为B 12和13列大于10，但5没有，因此不符合作为该金额一部分的标准。