$在SUMIF功能中不了解 [英] $ in SUMIF function not understood
问题描述
我们有一个从Excel 2003升级到Excel 2010的Excel工作簿。名为 Calc_Sheet 的工作表之一具有名为A，B，C，D和列的列称为 Calc ，具有以下公式：
We have an Excel workbook that was upgraded from Excel 2003 to Excel 2010. One of the worksheet called Calc_Sheet has columns named A,B,C,D and a column called Calc that has the following formula:
=SUMIF($A:$A,$A2,$B:$B)
上面的公式是针对单元格第二行 Calc 列。同样，对于同一列的行3中的单元格，公式显示为：
The above formula is for the cell in row two of the Calc column. Likewise, for cell in row 3 of the same column the formula displays as:
=SUMIF($A:$A,$A3,$B:$B)
...等等。
我们的工作簿还有一些工作表也有A，B，C的名称。我不知道上面的公式是否与工作表A和B有任何关系，或者它引用了工作表 Calc_Sheet 的列A，B。
Our workbook has some worksheets that also have the names as A,B,C. I don't know if the above formula has anything to do with the worksheets A and B or it is referencing the columns A, B of the worksheet Calc_Sheet.
我知道一个 SUMIF 功能与Microsoft 这里有例子。
I know what a SUMIF function is as explained by Microsoft here with examples.
但是所有的例子都没有使用 $ sign。那么，上面的公式是怎样的？
But all the examples there are using no $ sign. So, what the above formula translate to?
谢谢。
推荐答案
>您的公式的写法方式只是意味着如果您将该公式拖到左边或右边，它仍然会查看列A和B，如果您删除$ then，当您将公式拖到左边或右边时整个公式将改变它正在查看的列。 $代表绝对参考，在其他作品中，无论以下哪一项，不会改变，无论你在哪里配置公式。
The way your formula is written just means that if you drag that formula to the left or the right, it will still look at columns A and B, If you remove the $ then when you drag the formula to the left or the right the entire formula will change what columns it is looking at. The $ represent Absolute References, in other works whatever following the $ will not change no matter where you place the formula.
$ 也将锁定行，如果在行号之前看到。
The $ will also lock in the rows if seen before the Row number also.
A1 = Row and Column Will change when the formula is dragged around
$A1 = The Row number will change but not the Column reference
A$1 = the column may change but the row will not
$A$1 = The Formula will always reference `A1` even when filled down or across.
注意： 即使绝对引用，当列被插入或删除时，excel将修改公式。
NOTE: Even with Absolute Referencing on, excel will modify the formula when columns are either inserted or deleted.
有关示例，请参阅这里
或
For more information please see Here Or Here
SumIf函数
The SumIf Function
SumIf 函数的sytax是：
the sytax of the SumIf Function is:
SUMIF(range,criteria,sum_range)
以下是语法，您正在创建一个逻辑语句（True或False），其中指定范围内的单元格与您提供的条件进行比较。
Following this, syntax, You are creating a a Logical Statement (True or False) where the cells in the range specified, are compared to the criteria you provide.
如果没有提供运算符，但只提供Number或Text值，则所提供范围内的单元格将不得不等于条件。
If no operator is supplied but only a Number or Text value then the cells in the range provided will have to equal the criteria.
您还可以使用运算符，例如< 和> 符号。
You can also use operators like the < and the > symbols.
一旦你有要测试的范围，并且您希望测试的标准测试了功能的最后一部分是 sum_range 这里给出公式一个可以实际计算的单元格范围。
Once you have the Range you want to test and the criteria by which you would like it tested the last part of the function is the sum_range here you give the formula a range of cells that it can actually sum.
该函数将与 sum_range 中的单元格相加，其中范围中的单元格与第一个p [art的语法符合标准。
The function will sum the cells in the sum_range where the cells in the range from the first p[art of the syntax meet the criteria.
=SUMIF($A:$A,$A2,$B:$B)
所以给你上面的例子。它在 B：B 中的所有数字的总和，其中 A：A 中的单元格在同一行等于 A2 中的值。
So given your sample above. It is giving the sum of all the Number in B:B where the cell in A:A on the same row is equal to the value in A2.
这篇关于$在SUMIF功能中不了解的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
