使用excel vba将每天的结果转换成每周的结果 [英] Translating results by day to results by week with excel vba

问题描述

在我的Excel工作表中，A列中的日期不同，而B列中的每个日期都有相关的总计.日期有些随机，但是我想找到每周相关总计的总和.我是vba的新手，在弄清楚如何每周计算总计时遇到了一些麻烦.

In my excel sheet I have an varying number of dates in column A with associated totals for each date in column B. The dates are somewhat random but I want to find the sum of the associated totals per week. I'm new to vba and I'm having a bit of trouble figuring out how to calculate the totals per week.

我在想的伪代码是:

buttonClicked()
   Dim sum As Integer, tempDate As Date, current As Date, i As Integer, j As Integer, match As Boolean

   sum = 0
   tempDate = Range("A1").Value
   current = tempDate

   For i = 1 To Rows.Count 

       for j = 0 to 7
          tempDate = addDate(d, i, tempDate)           
          If cell(i,1).Value = tempDate Then sum = sum + cell(i, 2).Value
              match = true
              Break
           End If
        Next j

       If match = true Then tempDate = current
       Else
          `next open space in column D is current
          current = tempDate
          `next open space in column E is sum
          sum = 0
   Next i
end

请让我知道，除了遍历整个列表并提前7天计算之外，还有什么更好的方法可以解决此问题.我应该注意，A1已经被假定为星期日，我不确定注释行应如何实施.我将非常感谢您提供任何建议.

Please let me know if there's any better way to solve this problem other than iterating through the entire list and counting 7 days ahead. I should note that A1 is already assumed to be a sunday and I'm not sure how the commented out lines should be implemented. I would be very grateful for any advice at all.

推荐答案

如果您要从B列中查找一系列由7个行组成的总和，那么使用本机工作表函数的此公式应该可以.

If you are looking for a series of 7 row sums from column B, then this formula using native worksheet functions should do.

=SUM(INDEX(B:B, (ROW(1:1)-1)*7+1):INDEX(B:B, (ROW(1:1)-1)*7+7))

向下填充以获取连续的一周时间.

Fill down to catch successive week periods.

如果您可以提供开始日期(例如 = MIN(A:A))并在连续的行中添加7，则每周开始日期可能是 SUMIFS函数.在E9中，

If you can supply a starting date (e.g. =MIN(A:A)) and add 7 to it in successive rows, then the weekly starting date could be a reference point for a SUMIFS function. In E9 as,

=SUMIFS(B:B,A:A, ">="&D9,A:A, "<"&D9+7)

根据需要填写.

如果需要VBA解决方案，则可以将该逻辑带入VBA子函数或功能中.

If a VBA solution is necessary, that logic can be brought into a VBA sub or function.

这篇关于使用excel vba将每天的结果转换成每周的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！