在Google表格中创建每周/每月/每年折线图，以汇总同一周/月/年范围内的金额 [英] Create Weekly/Monthly/Yearly line chart in Google Sheets to sum amounts within same week/month/year range

问题描述

我想创建一个折线图，表示两列数据:F(输入日期)和H(美元金额).日期应为X轴，美元金额应为Y轴.

问题在于，我希望折线图上的日期代表在给定的一周，一个月或一年中输入的所有金额的总和.

这张照片来自YouTube分析工具，它创建的图形与我想要在工作表中创建的图形类似:

类似于这些分析如何为您提供选择数据收集时间的方式(在此示例中，以我的工作表为例，视图是金额)，我只是想做单独的图来描述每周，每月和每年的不同范围.

电子表格演示

I want to create a line graph representing two columns of data: F, the date of entry, and H, the dollar amount. The date should be the X-axis, and the dollar amounts on the Y-axis.

The catch is that I'd like the dates on the line graph to represent the sum of all amounts entered in a given week, month, or year.

This photo is of YouTube analytics, which creates a similar graph to what I'd like to create in sheets:

Similar to how these analytics give you the option to choose how you'd like the data (in this example, views, in my Sheets case, amounts)to be summed by the time it was collected, I simply want to make separate​ graphs to depict the different ranges of weekly, monthly and annually.

https://docs.google.com/spreadsheets/d/1P2vFfCVmsJwPLyD48YWQkwCR0jY3CPg7S9uOVlYhvkk/edit?usp=sharing This is a link to the type of data that I'd like to visualize.

解决方案

=ARRAY_CONSTRAIN(ARRAYFORMULA(
 IF(E1="weekly", 
 QUERY({"week "&WEEKNUM(A2:A)&" "&YEAR(A2:A), C2:C, YEAR(A2:A)+WEEKNUM(A2:A)*0.083}, 
 "select Col1,sum(Col2),Col3 where Col2 is not null group by Col1,Col3 order by Col3 label sum(Col2)''", 0), 
 IF(E1="monthly", 
 QUERY({TEXT(A2:A, "mmmm")&" "&YEAR(A2:A), C2:C, YEAR(A2:A)+MONTH(A2:A)*0.083}, 
 "select Col1,sum(Col2),Col3 where Col2 is not null group by Col1,Col3 order by Col3 label sum(Col2)''", 0), 
 IF(E1="yearly", 
 QUERY({YEAR(A2:A), C2:C}, 
 "select Col1,sum(Col2) where Col2 is not null group by Col1 label sum(Col2)''", 0), )))), 999^99, 2)

spreadsheet demo

这篇关于在Google表格中创建每周/每月/每年折线图，以汇总同一周/月/年范围内的金额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！