我有一个 Excel 工作簿,其中只包含日期(已排序),形式如下:
11/14/2011 11:53
10/14/2012 11:53
11/23/2012 19:28
12/24/2012 12:27
...
12/1/2013 11:48
我想创建一个折线图来显示每个月有多少条记录。我该怎么做?
答案1
如果您尝试统计某个月份(无论年份)的所有值,Mike Fitzpatrick 的答案会很有效。如果您希望获得按年份统计每个月的计数图表(即能够区分 2012 年 11 月和 2013 年 11 月),则需要更详细一点。
- 利用现有数据创建一个标准表。
- 添加一个名为“Year”的列,其公式为 =Year(A2)。
- 添加一个名为Month的列,公式为=Month(A2)。
- 在数据透视表中,使用年份和月份作为行标签,使用日期时间作为值(仍使用计数)。
- 您的数据透视表现在将区分不同年份的同一个月份。
- 最后,选择数据透视表,插入柱形图以创建包含每月计数的数据透视图。
通过使用标准表格来存储原始数据,当您添加值时,数据透视表和数据透视图将会更新。
答案2
假设您的数据是有效的 Excel 日期时间,请为您的范围提供一个标题(例如“日期时间”),然后创建一个数据透视表。
使用日期时间(或任何你称之为的时间)作为行标签和您的价值观。确保 Values 函数是 Count,而不是 Sum。
最后,按月份或年份对行标签进行分组。
答案3
我注意到,在你的样本数据中,你实际上有时间跨度相对较大,时间间隔不规律。虽然其他答案指出了如何在 Excel 中单独制作图表,但在这里我提出了另一种解决方案,可以制作交互式折线图来解决我提到的问题。下图显示了我根据您的描述制作的示例。
我使用 Funfun Excel 插件制作了此图表。基本上,此插件允许您直接在 Excel 中使用 JavaScript,因此您可以使用 HighCharts.js 或 D3.js 等库来制作交互式图表。我在这个特定示例中使用了 HighCharts.js。在此图表中,您可以使用鼠标放大特定时间跨度以查看详细数字。如果您有多年的日志,这将非常有用。
回到你的数据。在制作此图表之前,你确实需要计算每个月的频率。这可以通过用 JavaScript 编写一个小函数来完成。以下是一些示例代码。
var frequencyPerMonth = new Object();
var monthLast = 1;
var yearLast = 1970;
for(var i=0;i<data.length;i++){
var monthThis = parseInt(data[i][0].split('/')[0]-1);
var yearThis = parseInt(data[i][0].split('/')[2]);
if(monthThis == monthLast && yearThis == yearLast){
frequencyPerMonth[data[i][0].split('/')[2]+monthThis.toString()] += 1;
}
else{
frequencyPerMonth[data[i][0].split('/')[2]+monthThis.toString()] = 1;
}
monthLast = monthThis;
yearLast = yearThis;
}
Funfun 还有一个在线编辑器,您可以在其中探索 JavaScript 代码和结果。您可以在下面的链接中查看我如何制作示例图表的详细信息。
https://www.funfun.io/1/#/edit/5a4e0d461010eb73fe125c4e
一旦您对在线编辑器中的结果感到满意,您就可以使用上面的 URL 轻松地将结果加载到 Excel 中。但当然,首先,您需要通过以下方式将 Funfun 插件添加到 Excel 中:插入 - Office 加载项。下面是一些屏幕截图,展示如何将示例加载到 Excel 中。
披露:我是 Funfun 的开发人员