如何根据Excel中特定列的结果计算结果并绘制数据? [英] How to calculate results and plot data depending on results of a specific column in excel?

问题描述

我有一个.csv文件，它包含数千行.我收集了此数据文件作为60分钟运行程序的输出.此输出文件包含时间列(在论坛HH:MM:SS:MS中)，此时间列记录我的输出时间.我想根据时间列获取output.csv文件中其他列的图(每1分钟获取所有列的结果).

I have a .csv file and it contains thousands of rows. I collected this data file as output of running my program for 60 minutes. This output file contains time column (in forum HH:MM:SS:MS), this time column is recording time for my outputs. I want to get plot of my other columns in my output.csv file according to my time column (taking the results for all columns every 1 minutes).

例如:

我有这样的一行:

Data             Time
-----                 -----
455              10:00:00
894              10:00:00 
785              10:00:00
898              10:00:01
789              10:00:01
748              10:00:02
248              10:00:02
788              10:00:02
148              10:00:02
742              10:00:02
...              ...
266              10:01:00
...              ...

是否有任何简便的方法可以根据时间列来绘制其他具有行的列(每1分钟获取所有列的结果)?

Is there any easy way to plot other columns with rows according to time column (taking the results for all columns every 1 minutes) ?

推荐答案

虽然这个问题并不完全清楚/一致，但我理解您想对前15个间隔中的每个间隔计算数据数量

While the question is not completely clear/consistent, I understand you want to count the number of data for each of the first 15 intervals

10:00 <= time < 10:01
etc.

对于第一个间隔，您可以使用

For the first interval, you can use

=SUMPRODUCT(($B$2:$B$8>=TIME(10;0;0))*($B$2:$B$10000<TIME(10;1;0)))

我假设您的时间数据在 B2:B10000 中.您可以根据需要扩展此范围，超出范围没有问题(不计算空白单元格).或者您可以使用

I assume your time data is in B2:B10000. You can expand this range as needed, there is no problem in having an excess range (blank cells will not be counted). Or you could use

=SUMPRODUCT(($B:$B>=TIME(10;0;0))*($B:$B<TIME(10;1;0)))

您可以轻松地创建一个列，其中包含每个间隔的开始时间，而另一列使用此公式(的修改)来计算数据计数.

You can easily create a column with the start time for each interval, and another column using (a modification of) this formula to for the data count.

然后，您将绘制刚刚创建的两列.

Then you would plot the two columns just created.

这篇关于如何根据Excel中特定列的结果计算结果并绘制数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！