如何使用空白单元作为参考来执行sumif? [英] How to perform a sumif using blank cells as a reference?
问题描述
请参阅以下示例数据:
Time Date Result
00:21.6 10/1/2012 1:43 FALSE
01:47.7 10/1/2012 2:13 FALSE
00:56.7 10/1/2012 2:49 FALSE
00:54.9 10/1/2012 3:43
00:11.8 10/1/2012 3:43
02:10.9 10/1/2012 3:46 FALSE
01:05.4 10/1/2012 3:58 FALSE
00:55.8 10/1/2012 4:53
04:41.8 10/1/2012 4:52
00:26.3 10/1/2012 4:58
00:04.2 10/1/2012 4:58
00:15.3 10/1/2012 4:59
00:06.4 10/1/2012 4:57
00:10.7 10/1/2012 4:56
00:04.4 10/1/2012 4:56
00:04.2 10/1/2012 4:57
00:29.2 10/1/2012 4:57
00:34.5 10/1/2012 4:56
01:22.4 10/1/2012 4:55
00:08.1 10/1/2012 4:55 FALSE
03:20.9 10/1/2012 4:51 FALSE
00:56.3 10/1/2012 5:42 FALSE
02:23.1 10/1/2012 5:51
01:20.6 10/1/2012 5:48
00:09.8 10/1/2012 5:49 FALSE
01:40.0 10/1/2012 7:47 FALSE
01:13.4 10/1/2012 8:11 FALSE
00:41.6 10/1/2012 9:49 FALSE
01:08.1 10/1/2012 11:56 FALSE
我需要执行某种类型的计算。如果结果中没有空白单元格,则需要在结果旁边的时间单元格中的数据。但是,如果结果列中有空白单元格,则需要执行包含空白单元格的行中的时间和在空白单元格旁边包含FALSE的第一个单元格。
I need to perform a certain type of calculation. If there is no blank cell in the result, I need the data in time cell next to the result. But if there is a blank cell in the result column, I need to perform a sum of the time in the rows that contain the blank cell and the first cell containing FALSE next to the blank cell.
请参阅下面的示例输出:
Please see the example output below:
00:21.6 10/1/2012 1:43 FALSE 00:21.6
01:47.7 10/1/2012 2:13 FALSE 01:47.7
00:56.7 10/1/2012 2:49 FALSE 00:56.7
00:54.9 10/1/2012 3:43
00:11.8 10/1/2012 3:43
02:10.9 10/1/2012 3:46 FALSE 03:17.6(i.e., 00:54.9+00:11.8+02:10.9)
01:05.4 10/1/2012 3:58 FALSE 01:05.4
00:55.8 10/1/2012 4:53
04:41.8 10/1/2012 4:52
00:26.3 10/1/2012 4:58
00:04.2 10/1/2012 4:58
00:15.3 10/1/2012 4:59
00:06.4 10/1/2012 4:57
00:10.7 10/1/2012 4:56
00:04.4 10/1/2012 4:56
00:04.2 10/1/2012 4:57
00:29.2 10/1/2012 4:57
00:34.5 10/1/2012 4:56
01:22.4 10/1/2012 4:55
00:08.1 10/1/2012 4:55 FALSE 09:23.3(i.e., 00:55.8+04:41.8+00:26.3+00:04.2+00:15.3+00:06.4+00:10.7+00:04.4+00:04.2+00:29.2+00:34.5+01:22.4+00:08.1)
03:20.9 10/1/2012 4:51 FALSE 03:20.9
00:56.3 10/1/2012 5:42 FALSE 00:56.3
02:23.1 10/1/2012 5:51
01:20.6 10/1/2012 5:48
00:09.8 10/1/2012 5:49 FALSE 03:53.5(i.e., 02:23.1+01:20.6+00:09.8)
01:40.0 10/1/2012 7:47 FALSE 01:40.0
01:13.4 10/1/2012 8:11 FALSE 01:13.4
00:41.6 10/1/2012 9:49 FALSE 00:41.6
01:08.1 10/1/2012 11:56 FALSE 01:08.1
如果可以通过任何公式或使用可视化的基本编辑器,请让我知道。我现在手动手动。我每天要处理大约10000-15000行。如果你帮助我,我可以节省很多小时,并用它来学习新的东西。
If it is possible by any formulas or using visual basic editor please let me know. I am doing this manually right now. I have to handle about 10000-15000 rows per day. If you help me out I could save a lot of hours and use it to learn something new.
推荐答案
您更喜欢使用VBA:
Sub SmartRunningTotals()
Dim rng As Range
Dim cell As Range
Dim lastRow As Long
Dim totalTime As Double
' I'm assuming your time column is in column A
lastRow = Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row
Set rng = Range("A2:A" & lastRow)
For Each cell In rng
totalTime = totalTime + cell.Value
If cell.Offset(, 2).Value <> "" Then
cell.Offset(, 3).Value = totalTime
' reset total after we write it to column D
totalTime = 0
End If
Next
End Sub
这篇关于如何使用空白单元作为参考来执行sumif?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!