在 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屋！