如何创建平均表 [英] How do I create an Average table

问题描述

我正在尝试创建一个函数来接收一个列数不断变化的表（每次调用该函数）。

第一个列总是时间，格式为dd / mm / yyyy hh：nn：ss。每隔十秒就有一个包含许多数字数据列的新行。

用户在文本框中写入分钟数，例如10，

和函数返回一个包含所有原始列的表，但值将平均十分钟（当然减少行数）


提前致谢，


Izhar

Hi,
I am trying to create a function to receive a table with a changing number of columns (for each time the function is called).
The first column is always time, in the format "dd/mm/yyyy hh:nn:ss". Every ten seconds there is a new row with many columns of numerical data.
The user writes a number of minutes in a text box, for instance 10,
and the function returns a table containing all the original columns but the values would be averaged by ten minutes (less rows of course)

Thanks in advance,

Izhar

推荐答案

@izharmel


请发布一些具体数据以及结果平均后你想看到的。平均10分钟到底意味着什么？

@izharmel
Kindly post some concrete data as well as the results that you would like to see after averaging. What exactly do you mean by Averaging by 10 Minutes?

好的，原始表格如下：


15/06/2010 12： 31:00 1970 151.7 2360 116.8

15/06/2010 12:31:10 2000 163.4 2370 127.5

15/06/2010 12:31:20 1990 142.8 2360 112.7

15/06/2010 12:31:30 1990 142.8 2360 109.4

15/06/2010 12:31:40 1960 144.3 2360 110.7

15/06/2010 12:31:50 1950 157.2 2360 121.5

15/06/2010 12:32:00 1940 155.8 2360 122

。

。

。


和结果表看起来像这样：


15/06/2010 12:31:00 1990 150.7 2340 114.2

15/06/2010 12:41:10 2020 167.4 2390 187.9

15/06/2010 12 ：51：20 1700 145.8 2330 165.4

。

。

。


其中值将是十分钟的原始列表的平均值。


我到目前为止所做的是尝试创建一个记录集e原始数据，每次需要十分钟的数据（使用DateAdd）到另一个rs。然后使用AVG（SQL），但我不知道如何正确地写这个，或者如何在不知道列名的情况下运行查询。


提前谢谢

Izhar

OK, the original table would look like:

15/06/2010 12:31:00 1970 151.7 2360 116.8
15/06/2010 12:31:10 2000 163.4 2370 127.5
15/06/2010 12:31:20 1990 142.8 2360 112.7
15/06/2010 12:31:30 1990 142.8 2360 109.4
15/06/2010 12:31:40 1960 144.3 2360 110.7
15/06/2010 12:31:50 1950 157.2 2360 121.5
15/06/2010 12:32:00 1940 155.8 2360 122
.
.
.

and the the result table would look like that:

15/06/2010 12:31:00 1990 150.7 2340 114.2
15/06/2010 12:41:10 2020 167.4 2390 187.9
15/06/2010 12:51:20 1700 145.8 2330 165.4
.
.
.

where the values would be averages of the original list, upon periods of ten minutes.

What I''ve done so far is try to create a record set of the original data, and each time take ten minutes worth of data (using DateAdd) into another rs. then use AVG (SQL) but I don''t know how to properly write this, or how to run a query without knowing the name of the column.

Thanks in advance
Izhar

@izharmel

@izharmel

1. 我'我们以公共功能的形式为您创建了通用代码模板。只需将有效表，查询或SQL语句的名称传递给函数。然后，函数将计算数据源中每个列的平均值''除了''第一列。结果将显示在调试窗口中，作为每个字段名称（第1个除外），以及这些字段的平均值。我已经包含了基本的错误处理，但为了简洁和简单起见，我将保留验证和格式化问题供您解决，例如：确保所有字段值本质上都是数字，处理NULL，格式化平均值到适当的小数位数等。代码很容易适应，希望能指向正确的方向。祝你好运。
2. 代码示例：
   

1. I''ve created a Generic Code Template for you in the form of a Public Function. Simply pass to the Function the Name of a valid Table, Query, or SQL Statement. The Function will then calculate the Average of every Column in the Data Source ''except'' the first Column. The Results will be displayed in the Debug Window as each Field Name (except the 1st), along with the Averages for those Fields. I''ve included basic Error Handling, but for the sake of brevity and simplicity, I''ll leave the Validating and Formatting issues for you to resolve such as: making sure all Field Values are Numeric in nature, handling of NULLs, Formatting Averages to the proper number of decimal places, etc. The code is easily adaptable, and hopefully should point you in the right direction. Good Luck.
2. Code Example:
   

展开 | 选择 | Wrap | 行号

这篇关于如何创建平均表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！