Google表添加序列号和计算

Question

关于

原始数据：

02/06/2000 15:30:00 5.12
05/06/2000 15:30:00 5.2
06/06/2000 15:30:00 5.28
07/06/2000 15:30:00 5.26
08/06/2000 15:30:00 5.33
09/06/2000 15:30:00 5.36
12/06/2000 15:30:00 5.24
13/06/2000 15:30:00 5.33
14/06/2000 15:30:00 5.39
15/06/2000 15:30:00 5.3
16/06/2000 15:30:00 5.29
19/06/2000 15:30:00 5.27
20/06/2000 15:30:00 5.26
21/06/2000 15:30:00 5.33
22/06/2000 15:30:00 5.25
23/06/2000 15:30:00 5.15
26/06/2000 15:30:00 5.21
27/06/2000 15:30:00 5.15
28/06/2000 15:30:00 5.09
29/06/2000 15:30:00 5.11
30/06/2000 15:30:00 4.82
03/07/2000 15:30:00 4.87
04/07/2000 15:30:00 4.92
05/07/2000 15:30:00 4.87
06/07/2000 15:30:00 4.93
07/07/2000 15:30:00 4.85
10/07/2000 15:30:00 5.2
11/07/2000 15:30:00 5.43
12/07/2000 15:30:00 5.54
13/07/2000 15:30:00 5.29
14/07/2000 15:30:00 5.17
17/07/2000 15:30:00 5.29
18/07/2000 15:30:00 5.07
19/07/2000 15:30:00 5.05
20/07/2000 15:30:00 4.92
21/07/2000 15:30:00 5.04
24/07/2000 15:30:00 4.77
25/07/2000 15:30:00 4.91
26/07/2000 15:30:00 4.62
27/07/2000 15:30:00 4.8
28/07/2000 15:30:00 4.59

我想以相反顺序进行序列号：

3. 06/2000 5.225
2. 07/2000 4.98  10.205 (*addition of value 1 and 2)
1. 08/2000 4.44  5.765  (substration of output of 2 and value of 3) 

系列应以下内容： +， - ， - ， - 等等。 请使用GoogleFinance（）函数建议 提前致谢。 此致，

Answer 1

使用：

=ARRAYFORMULA({SUM(QUERY(
 GOOGLEFINANCE("NASDAQ:GOOG", "price", DATE(2020,1,1), TODAY(), "DAILY"),
 "select Col2 limit 2 offset 1", )); SUM(QUERY(
 GOOGLEFINANCE("NASDAQ:GOOG", "price", DATE(2020,1,1), TODAY(), "DAILY"),
 "select Col2 limit 2 offset 1", ))-MMULT(TRANSPOSE((SEQUENCE(ROWS(
 GOOGLEFINANCE("NASDAQ:GOOG", "price", DATE(2020,1,1), TODAY(), "DAILY"))-3) <=SEQUENCE(1, ROWS(
 GOOGLEFINANCE("NASDAQ:GOOG", "price", DATE(2020,1,1), TODAY(), "DAILY"))-3))*QUERY(
 GOOGLEFINANCE("NASDAQ:GOOG", "price", DATE(2020,1,1), TODAY(), "DAILY"), 
 "select Col2 offset 3", )), SEQUENCE(ROWS(
 GOOGLEFINANCE("NASDAQ:GOOG", "price", DATE(2020,1,1), TODAY(), "DAILY"))-3, 1, 1, ))})

请注意LTE的限制为＆lt;因此，在您的情况下， googlefinance 公式最多可以输出3162行。在 GoogleFinance <代码> arrayformula

Answer 2

要获取两行连续行之间的差异，请使用mmult如下以下

=ARRAYFORMULA(if(B2:B="",,mmult(-1*(ROW(B2:B)=TRANSPOSE(ROW(B2:B)+1))+1*(ROW(B2:B)=TRANSPOSE(ROW(B2:B))),if(B2:B="",0,B2:B))))

限制行数，以防止长计算

mmult构建一个数组，如下所示

https://i.sstatic.net/nzpdl.png“ alt =”在此处输入图像描述”>