Google 表格:每日、每周、每月和每年的平均值移动 [英] Google Sheets: Moving daily, weekly, monthly, and yearly averages

问题描述

我有一个电子表格，用于收集数据，并输入收集数据的日期.我想获取电子表格中每天、每周、每月和每年收集的图表数据的移动平均值.

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.

收集的数据并非每天都输入，因此日期"列中的日期之间存在差距.有时 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.

例如:

    + ---------- + - + ----- +
    |     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  |

我收集了近 3 年的 217 行数据.

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 表格:每日、每周、每月和每年的平均值移动的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！