问题描述
我有一个电子表格,我在其中收集数据,并输入收集数据的日期.我想获取图表中每天,每周,每月和每年的电子表格中收集的数据的移动平均值.
I have a spreadsheet where I'm collecting data, and entering a date the data is collected. I would like to get the moving averages of collected data in my spreadsheet on a daily, weekly, monthly, and yearly for charts.
我要处理的两列是A列中的日期"(收集数据时)和C列中的数据"(实际收集的数据).该日期始终在增加,格式为"mm/dd/yyyy".C列中的数据是整数,并且运行总和几乎一直增加,除了必须手动校正的四个地方.
The two columns that I'm working off of are "Date" in column A (when the data was collected) and "Data" in column C (the actual collected data). The date is always increasing and is "mm/dd/yyyy" format. The data in column C are integers, and an almost-always increasing running total, except in four places where manual corrections had to be made.
并非每天都输入收集的数据,因此,日期"(Date)列中的日期之间存在间隙.有时需要2或3天才能收集到数据,有时甚至更多.最大差距是没有收集数据的98天.
The collected data is not entered every day, and as such, there are gaps between dates in the "Date" column. Sometimes 2 or 3 days go by without collected data, sometimes more. The largest gap is 98 days without collected data.
E.G.:
+ ---------- + - + ----- +
| A | B | C |
+ - + ---------- + - + ----- +
| 1 | Date | | Data |
| 2 | 6/15/2016 | | 1263 |
| 3 | 6/30/2016 | | 1371 |
| 4 | 7/1/2016 | | 1382 |
| 5 | 7/7/2016 | | 1429 |
| 6 | 10/13/2016 | | 2588 |
我已经收集了217行中近3年的数据.
I have collected almost 3 years of data across 217 rows.
获取每日移动平均线看起来就像 =(C3-C2)/DATEDIF(A2,A3,"D"))
一样简单.每周,每月和每年的平均水平都让我感到沮丧.
Getting a moving daily average seems as simple as =(C3-C2)/DATEDIF(A2, A3, "D"))
. Getting the moving weekly, monthly, and yearly averages are stumping me.
如何获取每天未在Google表格中输入的数据的每周,每月和每年的平均值?
How can I get moving weekly, monthly, and yearly averages for data that isn't entered every day in Google Sheets?
推荐答案
G2:
=IFERROR(MINUS(QUERY($A2:$C,
"select C
where A >= date'"&TEXT($A2, "yyyy-mm-dd")&"'
and A <= date'"&TEXT($A2+7, "yyyy-mm-dd")&"'
limit 1 offset "&COUNTA(QUERY($A2:$C,
"select C
where A >= date'"&TEXT($A2, "yyyy-mm-dd")&"'
and A <= date'"&TEXT($A2+7, "yyyy-mm-dd")&"'"))-1), $C2)/7, )
H2:
=IFERROR(MINUS(QUERY($A2:$C,
"select C
where A >= date'"&TEXT($A2, "yyyy-mm-dd")&"'
and A <= date'"&TEXT($A2+30, "yyyy-mm-dd")&"'
limit 1 offset "&COUNTA(QUERY($A2:$C,
"select C
where A >= date'"&TEXT($A2, "yyyy-mm-dd")&"'
and A <= date'"&TEXT($A2+30, "yyyy-mm-dd")&"'"))-1), $C2)/30, )
I2:
=IFERROR(MINUS(QUERY($A2:$C,
"select C
where A >= date'"&TEXT($A2, "yyyy-mm-dd")&"'
and A <= date'"&TEXT($A2+365, "yyyy-mm-dd")&"'
limit 1 offset "&COUNTA(QUERY($A2:$C,
"select C
where A >= date'"&TEXT($A2, "yyyy-mm-dd")&"'
and A <= date'"&TEXT($A2+365, "yyyy-mm-dd")&"'"))-1), $C2)/365, )
这篇关于Google表格:每天,每周,每月和每年的平均值移动的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!