VBA Excel - 基于不同范围的单元格的结果对一系列单元格进行求和 [英] VBA Excel - Summing a range of cells based on result in a different range of cells
问题描述
我正在尝试使用基于一组预先注册的历史数据创建excel中的预测功能。我是VBA的新手,我无法做这个功能。
我有三个范围:
- 范围1在一年中的每一天列出。所有单元格都以date的格式进行格式化。
- 名为C的不同表格上的范围2也会列出一年中的每一天。所有单元格的格式为date。
- 表格C上的范围3包含范围2中提到的特定日期中实现的美元价值。 >
要求:
该函数应该采用3个不同的变量,这三个不同的范围。该功能应首先在所选单元格的月份和年份 LOOKUP
中将其与范围2中的单元格的月份和(年-1)匹配。 p>
因此, LOOKUP
与Range 2匹配的同一行范围3中的单元格应该总结然后除以计数的细胞计数。
到目前为止,我已经能够创建一个名为 MNAME的功能MNAME
。
函数MNAME(x As Variant)As String
Dim CurrentMonth As Date
CurrentMonth = x
MNAME = MonthName(Month(CurrentMonth),True)
结束函数
但是我无法嵌套查找并总结出这些值。
你不需要VBA。
阅读在 SUMPRODUCT()
函数 - 这里是一个很好的解释
总结您想要找到的问题:
-
如果
Range2
中的单元格的年份与YEAR(reference_cell)-1
- > IF(YEAR(reference_cell)-1 = YEAR(Range2))
-
Range2
中的单元格与MONTH(reference_cell)相同
- > IF(MONTH(reference_cell)= MONTH(Range2))
-
其中1. IS code> TRUE 和2. IS
TRUE
,在Range3
/ p>
- > = SUMPRODUCT( - (YEAR(reference_cell)-1 = YEAR(Range2))* - (MONTH(reference_cell)= MONTH(Range2))* Range3)
I am attempting create a forecasting function in excel using based on a set of preregistered historical data. I am new to VBA and I am unable to make this function.
I have three Ranges:
- Range 1 lists every day in a year. All cells are formatted in the form of "date".
- Range 2 on a different sheet named "C" also lists every day in a year. All cells are formatted in the form of "date".
- Range 3 on sheet "C" contains the dollar value actualized in the specific date of the year mentioned in Range 2.
Requirement:
The function should take 3 different variables which are the three different ranges. The Function should first "LOOKUP
" the month and year of the selected cell and match it with the month of and (year -1) of the cells in Range two.
Accordingly, The cells in Range 3 on the same row in which the "LOOKUP
" matches with Range 2 should sum up and then divide by the count of cells counted.
So far I have been able to create a function named MNAME
.
Function MNAME(x As Variant) As String
Dim CurrentMonth As Date
CurrentMonth = x
MNAME = MonthName(Month(CurrentMonth), True)
End Function
But I am failing to nest the lookups and sum up the values.
you dont need VBA.
Read Up on the SUMPRODUCT()
function - Here is a good explanation
to summarise your problem you want to find out:
if the year of the cells in
Range2
is the same asYEAR(reference_cell)-1
--> IF(YEAR(reference_cell)-1=YEAR(Range2))
if the month of the cells in
Range2
is the same asMONTH(reference_cell)
--> IF(MONTH(reference_cell)=MONTH(Range2))
where 1. IS
TRUE
and 2. ISTRUE
, sum corresponding cells inRange3
--> =SUMPRODUCT(--(YEAR(reference_cell)-1=YEAR(Range2))*--(MONTH(reference_cell)=MONTH(Range2))*Range3)
这篇关于VBA Excel - 基于不同范围的单元格的结果对一系列单元格进行求和的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!