date - 使用 Arrayformula 或 LOOKUP 填充日历

Question

我制作了一张日历表，并想使用数组公式或某种查找来填充它。问题是，每个单元格中的代码都不同，我是否需要全部都是相同的代码，或者是否可以做一个 Arrayformula 为每行执行不同的公式？

我花了很长时间让日历代码正常工作，但现在想简化代码，但我不确定下一步应该是什么:

https://docs.google.com/spreadsheets/d/1u_J7bmOFyDlYXhcL5dW3CHFJ1esySAKK_yPc6nFTdLA/edit?usp=sharing

如有任何建议，我们将不胜感激。

Answer 1

我在您的文件中添加了一个名为“Aresvik”的新工作表。

绿色细胞有新的配方。

单元格B3可以是=date(B1,1,1)

然后每个连续的月份可以是 =eomonth(B3,0)+1、=eomonth(J3,0)+1 等。

单元格B5中的日期公式为:

=arrayformula(iferror(vlookup(序列(7,7,1),{array_constrain(序列(40,1),日(eomonth(B3,0))+工作日(B3,3),1 ),查询({展平(分割(rept(",",日(eomonth(B3,0))-1),",",0,0));序列(日(eomonth(B3,0)), 1,1)},"偏移量"&day(eomonth(B3,0))-weekday(B3,3)&"",0)},2,false),))

可以复制到Mo下面的各个单元格，因此B5将更改为J5、R5 、Z5 等

注释

该概念围绕使用 SEQUENCE 函数生成 6 行 7 列的数字网格:

序列(6,7)

看起来像这样:

 1  2  3  4  5  6  7
 8  9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31 32 33 34 35
36 37 38 39 40 41 42

然后在 VLOOKUP 中使用这些数字来获取日历的相应日期。如果该月的第一天是星期四(2021 年 4 月)，则 vlookup 范围需要在日期列表顶部有 3 个间隙。与我原来使用 offset 的 query 相比，player0 有一个更优雅的解决方案，因此我将其合并到下面。单元格 Z3 的日期为 2021 年 1 月 4 日:

=arrayformula(
iferror(
vlookup(sequence(6,7),
   {sequence(day(eomonth(Z3,0))+weekday(Z3,2),1,0),
   {iferror(sequence(weekday(Z3,2),1)/0,);sequence(day(eomonth(Z3,0)),1,Z3)}},
2,false)
,))

vlookup 范围中的第一列是:

序列(日(eomonth(Z3,0))+工作日(Z3,2),1,0)

这是一个从 0 开始的数字数组，对应于该月的天数加上第一天之前的间隔数。

vlookup 范围中的第二列是:

{iferror(sequence(weekday(Z3,2),1)/0,);sequence(day(eomonth(Z3,0)),1,Z3)}},

它是一个由 2 列组成的数组，格式如下:{x;y}，其中 y 位于 x 下方，因为 ;.

这些是间隙:iferror(sequence(weekday(Z3,2),1)/0,)，后跟日期数字:sequence(day(eomonth(Z3, 0)),1,Z3)

(以下示例为 2021 年 4 月):

0   
1   
2   
3   
4   
5   
6   44317
7   44318
8   44319
9   44320
10  44321
11  44322
12  44323
13  44324
14  44325
15  44326
16  44327
17  44328
18  44329
19  44330
20  44331
21  44332
22  44333
23  44334
24  44335
25  44336
26  44337
27  44338
28  44339
29  44340
30  44341
31  44342
32  44343
33  44344
34  44345
35  44346
36  44347

vlookup 获取初始序列(6x7 布局)中的每个数字，并根据 col1 中的匹配从范围内的 col2 中返回相应的日期。

当该月的第一天是星期一时，iferror(sequence(weekday(BB1,2),1)/0,) 会在 vlookup 范围的 col2 中生成间隙。这就是为什么 vlookup 范围中的 col1 必须以 0 开头。

我已将表格更新为 https://docs.google.com/spreadsheets/d/1u_J7bmOFyDlYXhcL5dW3CHFJ1esySAKK_yPc6nFTdLA/edit#gid=68642071

日历上的值是日期，因此格式必须为 d。

如果您想要数字，请使用:

=arrayformula(
iferror(
vlookup(sequence(6,7),
   {sequence(day(eomonth(Z3,0))+weekday(Z3,2),1,0),
   {iferror(sequence(weekday(Z3,2),1)/0,);sequence(day(eomonth(Z3,0)),1)}},
2,false)
,))