填写 Google 表格中缺失的日期 [英] Fill missing dates in Google Sheets

问题描述

我有两列:

Col A Col B01.02.2020 1703.11.2020 2403.11.2020 12

正如我在另一个

I have two columns:

Col A                    Col B
01.02.2020               17
03.11.2020               24
03.11.2020               12

As I stated in another question, I tried to sum Col B, based on the month in Col A. The solution was the following formula (without the sort):

=ARRAYFORMULA(
  SUMIF(
    MID(A:A, 4, 2),
    SORT(UNIQUE(MID(FILTER(A3:A, A3:A <> ""), 4, 2))),
    B:B
  )
)

Something I missed was the population of missing months. Therefore my question is: How can I populate the result table with the missing months and zeroes until values are entered? The desired output for the table above would be:e

Col A                    Col B               Col C
01.02.2020               17                  0
03.11.2020               24                  17
14.12.2020               100                 0
03.11.2020               12                  0
                                             0
                                             0
                                             0
                                             0
                                             0
                                             0
                                             36
                                             100

解决方案

If just doing it for the current year, this should be enough

=ArrayFormula(sumif(mid(A2:A,4,2),sequence(12),B2:B))

这篇关于填写 Google 表格中缺失的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！