本文介绍了Excel VBA sumifs排序和设置的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
在此查询中我需要一些帮助.
I need some help in this query.
所以我有这个数据:
| A | B | C | D | ---------------------------------- Date int001 int002 int003 2/1/2016 10 11 12 2/2/2016 5 5 -5 2/3/2016 -4 -4 4 2/4/2016 5 5 -5 2/1/2016 2 -2 2 2/2/2016 -3 -2 -1 2/3/2016 1 1 1 2/4/2016 2 -1 3 2/1/2016 -4 -5 6 2/2/2016 -2 -2 2 2/3/2016 -1 1 -1 2/4/2016 -5 -3 1使用此数据,我需要根据唯一的日期和正值来计算总和.
With this data i need to calculate a sum based on unique dates and pozitive values.
我需要第一个unique_date:
For first unique_date i need:
- B列的总和,其中值> 0并在H2中设置
- 列C的总和,其中值> 0并在H3中设置
- D列的总和,其中值> 0并在H4中设置
我需要第二个unique_date:
For second unique_date i need:
- B列的总和,其中值> 0并在H5中设置
- 列C的总和,其中值> 0并在H6中设置
- D列的总和,其中值> 0并在H7中设置
我需要第三个unique_date:
For third unique_date i need:
- B列的总和,其中值> 0并在H8中设置
- 列C的总和,其中值> 0并在H9中设置
- D列的总和,其中值> 0并在H10中设置
我需要第四次unique_date:
For forth unique_date i need:
- B列的总和,其中值> 0并在H11中设置
- 列C的总和,其中值> 0并在H12中设置
- D列的总和,其中值> 0并在H13中设置
具有来自"A"列的唯一数据的"F"列有效.但是"H"列仍然不起作用.
The column "F" with unique data from column "A" is work. But the column "H", still doesn't work.
我需要的结果是:
| F | H | --------------------- Uniq Dates Results: 2/1/2016 12 2/2/2016 11 2/3/2016 20 2/4/2016 5 5 2 1 2 5 7 5 4这是我写的代码:
Sub Tsum() Dim int001 As Range Dim int002 As Range Dim int003 As Range Dim data1 As Date Dim data2 As Date Dim data3 As Date Dim nr_rows As Integer Dim dates As Range Dim nr_unique_dates As Integer Dim unique_dates As Range '-------------------------------------------------------------- nr_rows = Cells(Rows.Count, "A").End(xlUp).Row Set int001 = Range("B2:B" & nr_rows) Set int002 = Range("C2:C" & nr_rows) Set int003 = Range("D2:D" & nr_rows) Set dates = Range("A2:A" & nr_rows) '-------------------------------------------------------------- Range("A1:A" & nr_rows).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("F1"), Unique:=True nr_unique_dates = Cells(Rows.Count, "F").End(xlUp).Row Set unique_dates = Range("F2:F" & nr_unique_dates) Range("H2:H" & nr_rows) = Application.WorksheetFunction.SumIfs(dates, int001, int002, int003, unique_dates, int001, int002, int003, ">0") End Sub----问题出在下面,因为我不知道如何循环播放
---- The problem is somewhere bellow, because i don't know how to loop it
Range("H2:H" & nr_rows) = Application.WorksheetFunction.SumIfs(dates, int001, int002, int003, unique_dates, int001, int002, int003, ">0")在OP澄清后,
推荐答案已编辑:
替代:
Range("H2:H" & nr_rows) = Application.WorksheetFunction.SumIfs(dates, int001, int002, int003, unique_dates, int001, int002, int003, ">0")具有:
Dim iDate As Long With Range("H1") For iDate = 1 To nr_unique_dates - 1 .Offset((iDate - 1) * 3 + 1) = Application.WorksheetFunction.SumIfs(int001, dates, unique_dates(iDate), int001, ">0") .Offset((iDate - 1) * 3 + 2) = Application.WorksheetFunction.SumIfs(int002, dates, unique_dates(iDate), int002, ">0") .Offset((iDate - 1) * 3 + 3) = Application.WorksheetFunction.SumIfs(int003, dates, unique_dates(iDate), int003, ">0") Next iDate End With更多推荐
Excel VBA sumifs排序和设置
发布评论