google-sheets - 当数据列为整数时如何使用日期相关函数

Question

这是从雅虎财经提取股息信息的功能。

=importdata("http://real-chart.finance.yahoo.com/table.csv?s=T&a=1&b=1&c=2010&d="&
month(today())&"&e="&DAY(today())&"&f="&year(today())&"&g=v&ignore=.csv")

这会返回...

  A       B
Date    Dividends
42648   0.48
42557   0.48
42466   0.48
42375   0.48
42284   0.47
42193   0.47
42102   0.47
42011   0.47
41920   0.46
41828   0.46
41737   0.46
41647   0.46
41555   0.45
41463   0.45
41372   0.45
41282   0.45
41187   0.44
41096   0.44
41004   0.44
40914   0.44
40821   0.43
40730   0.43
40639   0.43
40549   0.43
40457   0.42
40366   0.42
40275   0.42

我想做的是按每笔交易的年份进行分组。我找到了一个解决方法来实现这一目标。

=QUERY( A:B, 
"Select year(A) , sum(B) Where A is not null Group by year(A) Label year(A) 'Year',  
sum(B) 'Total'" , 1)

*结果

Year    Total
2010    70.530003
2011    85.077798
2012    85.877801
2013    99.133401
2014    90.649999
2015    87.259999
2016    104.349998

虽然如果我手动将 A 列的单元格格式更改为日期格式，这非常有效，但我想将其变成一个函数。更多类似的事情。

=query(importdata("http://real-chart.finance.yahoo.com/table.csv?s=T&a=00&b=3&c=2000&d="&
month(today())&"&e="&DAY(today())&"&f="&year(today())&"&g=v&ignore=.csv"),
"Select year(Col1) , sum(Col2) Where Col1 is not null Group by year(Col1) 
Label year(Col1) 'Year',  sum(Col2) 'Total'")

此函数给我一条错误消息，内容为

Can't perform the function year on a column that is not a Date or a DateTime column

我猜这是因为 Col1 是整数，而不是日期格式。

有什么解决方法可以使这项工作正常进行吗？

Answer 1

简短回答

使用 TO_DATE 将 A 列数字值转换为日期内置函数。

公式

=ArrayFormula(query({TO_DATE(A:A),B:B},
"Select year(Col1) , sum(Col2) Where Col1 is not null Group by year(Col1) Label year(Col1) 
'Year',  sum(Col2) 'Total'" , 1))

说明

简化案例

• 以下公式假设导入的数据位于单元格 A2:B28 上。
• TO_DATE 应用于 A 列值，然后构建一个数组用作 QUERY 的第一个参数。

=ArrayFormula(query({TO_DATE(A2:A28),B2:B28},"select year(Col1)"))

单个公式

尽管这是可能的，但这需要非常复杂(难以阅读和维护，具有重复的嵌套函数)，因此脚本很可能是比单个公式更好的替代方案。