英文:
Which chart type counts the amount of timestamp instances inside a column?
问题
上周，我想要统计日志文件中异常发生的次数。因此，我在我的文件中执行了grep "Exception"命令，并使用了awk -F "." '{print $1}'来仅显示时间戳，结果如下所示（"Tijdstip" 是荷兰语中的 "timestamp"）：
Tijdstip
07:18:24
07:18:24
07:25:10
07:25:10
07:25:31
07:34:18
07:34:18
07:34:18
...
然后，我想要显示该时间戳显示的次数，我使用了一个帮助列，其中包含公式=COUNTIF(A$2:A$3013,A2)。
这导致了以下图表（"Aantal" 是帮助列的名称）：
顶部的信息是通过将鼠标拖动到顶部点上方提供的，以工具提示的方式显示出来。这确实是我正在寻找的，并解决了我的问题。
今天早上，我自己想：“天哪，使用带有绝对和相对引用混合的 CountIF() 公式，这相当复杂。应该有一种更简单的方法来获得这个结果。”但是，在尝试了其他图表类型（柱状图、直方图等，甚至是数据透视图）后，我没有找到一种图表类型可以选择一组条目、计数它们并清晰地显示它们及其频率。
我是对的吗，还是有一种图表类型可以实现这个？
在第一条评论后编辑：
似乎有一个简单的解决方案，但Excel 搞砸了它，试图分析这些值并将它们转换为小时和分钟，然后执行计数，正如您在以下截图中所看到的：
英文:
Last week, I wanted to count the amount of times an exception occured in a logfile.
Therefore, I had done a grep "Exception" in my file and used an awk -F "." '{print $1}' to show only the timestamps, the result looked as follows ("Tijdstip" is Dutch for "timestamp"):
Tijdstip
07:18:24
07:18:24
07:25:10
07:25:10
07:25:31
07:34:18
07:34:18
07:34:18
...
Then, I wanted to show the amount of times that timestamp was shown, which I did using a helper column, containing the formula =COUNTIF(A$2:A$3013,A2).
This led to the following chart ("Aantal" is the name of the helper column):
The information on the top was provided, dragging my mouse above the top point, showing that information as a tooltip. This was indeed what I was looking for and it solved my problem.
This morning, I thought by myself "Jezus, man, using a CountIF() formula with a mixture of absolute and relative references, that's quite complicated. There should be an easier way to obtain this result.", but after some tests with other chart types (column, histogram, ..., even pivotchart), I did not find one chart type which can select a group of entries, count them and show them and their frequency in a clear way.
Am I right or is there a chart type which covers this?
Edit after first comment:
There seems to be an easy solution, but Excel messes it up, trying to analyse the values and turning them into hours and minutes, and perform the count on this, as you can see in the following screenshot:
答案1
得分: 3
总结提出的解决方案：
您可以使用数据透视表（和数据透视图）功能，其中您将应用时间戳列作为两个字段：
行和值，并选择计数作为聚合函数。
为了调整Excel处理日期和时间的方式，您可能需要分组/取消分组时间戳的组成部分，以符合您的要求（参考链接，也请查看评论）。
额外说明：
首先，数据透视表可能如下所示（按小时分组时间戳）：
取消分组可以通过右键单击第一列来完成：
这将导致以下正确的结果：
英文:
To sum up the proposed solution:
You can use the Pivot table (and Pivot chart) feature where you would apply the timestamp column for both fields:
rowsandvalues, and choose tocountas aggregation.
In order to adjust the way Excel handles date and time you may want to group/ungroup the components of the timestamp to match with your requirements (reference links see also in the comments).
Extra remark:
At first, the pivottable might look as follows (grouping the timestamps per hour):
Ungrouping can be done, right-clicking in the first column:
This results in the following correct result:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论