本文介绍了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排序和设置的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!