vba 将 10 分钟间隔数据转换为每小时平均值

Converting 10 minute interval data to hourly average

提问by small_world

I have an excel file (.xlsx) that contains three columns, with headers:'datetime'(examples: 10/1/2008 0:10, 10/1/2008 0:20 etc), 'RH'(Example: 0.46) and 'wind_mps'(Example: 3.71). I wish to convert the 10 minute interval data into hourly average data for both RH and wind_mps columns.

我有一个包含三列的 excel 文件 (.xlsx)，标题为：'datetime'（示例：10/1/2008 0:10、10/1/2008 0:20 等）、'RH'（示例：0.46 ) 和 'wind_mps'（示例：3.71）。我希望将 10 分钟间隔数据转换为 RH 和 wind_mps 列的每小时平均数据。

I was unable to insert the Excel data into this question. Sorry about that. I can edit my question if someone tells me how to.

我无法在这个问题中插入 Excel 数据。对于那个很抱歉。如果有人告诉我怎么做，我可以编辑我的问题。

A similar question has been answered at how to convert by the minute data to hourly average data in R, but I'm new to R and unable to use the same technique for my data. I also tried to use the 'zoo', 'chron' and 'xts' packages to do this, as in http://rpubs.com/hrbrmstr/time-series-machinationsbut they seem to not work in R 3.02.

关于如何将分钟数据转换为 R 中的每小时平均数据，已经回答了一个类似的问题，但我是 R 的新手，无法对我的数据使用相同的技术。我还尝试使用 'zoo'、'chron' 和 'xts' 包来执行此操作，如http://rpubs.com/hrbrmstr/time-series-machinations，但它们似乎在 R 3.02 中不起作用。

I tried to do this in Excel, but couldn't find a reasonably easy technique.

我尝试在 Excel 中执行此操作，但找不到相当简单的技术。

I was able to achieve a similar task of converting hourly data to daily average for another data set using an Excel macro, but I'm unable to do this for 10 minute data. The Macro is given below:

我能够使用 Excel 宏完成将每小时数据转换为另一个数据集的每日平均值的类似任务，但我无法对 10 分钟数据执行此操作。宏如下：

Global year As Integer
Sub Calculate()
    Dim start_year As Integer
    Dim end_year As Integer
    Dim cell_count As Integer
    start_year = Cells(19, "M").Value
    end_year = Cells(48, "M").Value
    year = start_year
    cell_count = 19
    Do While year < (end_year + 1)
        Dim row As Integer
        Dim sum As Double
        Dim count As Integer
        Dim init_row As Integer
        init_row = 6
        sum = 0
        count = 0
        Dim cv As Integer
        cv = 3
        Do Until cv = year
            cv = Cells(init_row, "C").Value
            init_row = init_row + 1
        Loop
        row = init_row - 1
        Worksheets("Sheet1").Activate
        Dim cv1 As Integer
        cv1 = Cells(row, "C").Value
        Do While cv1 = year
            sum = sum + Cells(row, "F").Value
            count = count + 1
            row = row + 1
            cv1 = Cells(row, "C").Value
        Loop
        Cells(cell_count, "N").Value = sum
        Cells(cell_count, "O").Value = count
        Cells(cell_count, "P").Value = sum / count
        cell_count = cell_count + 1
        year = year + 1
    Loop
End Sub

It doesn't really matter to me whether I use R, Excel function, Macros or any other technique. It'd be great if someone can tell me how to convert this data set of about 50000 values for RH and wind_mps each to hourly average.

我是否使用 R、Excel 函数、宏或任何其他技术对我来说并不重要。如果有人能告诉我如何将这个包含 50000 个 RH 和 wind_mps 值的数据集转换为每小时平均值，那就太好了。

Thanks in advance.

提前致谢。

回答by John Bustos

This can be VERY easily done via Excel Pivot Tables...

这可以通过 Excel 数据透视表轻松完成...

1. Select your data and, in the top menu ribbon, go to Insert> Pivot Table

1. 选择您的数据，然后在顶部菜单功能区中，转到Insert>Pivot Table

In the Pivot Table designer:

在数据透视表设计器中：

1. Select your datetime as your Row Labels
2. Select RH & wind_mps as your Values
3. For both your values, click on them and select Value Field Settings> Average

1. 选择您的日期时间作为您的行标签
2. 选择 RH & wind_mps 作为您的值
3. 对于您的两个值，单击它们并选择Value Field Settings>Average

In the Pivot Table itself:

在数据透视表本身中：

1. Now, go to your Pivot Table itself and right click on any of the dates in the first column
2. Select Groupfrom the context menu that shows up and select Hoursfrom the list that comes up

1. 现在，转到您的数据透视表本身并右键单击第一列中的任何日期
2. 选择Group从上下文菜单中显示出来，并选择Hours从出现在列表

That should give you what you're looking to do with no programming at all

这应该给你你想要做的事情，根本不需要编程

Hope this does what you wanted

希望这能满足您的需求