使用 Arrayformula 或 LOOKUP 填充日历 [英] Filling a calendar using Arrayformula or LOOKUP

问题描述

我制作了一个日历表，并希望使用 Arrayformula 或某种查找来填充它.问题是，每个单元格中的代码都不同，我是否需要所有代码都相同，或者是否可以做一个 Arrayformula 为每一行执行不同的公式?

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

注意事项

这个概念围绕着使用 SEQUENCE 函数来生成一个 6 行 7 列的数字网格:

sequence(6,7)

看起来像这样:

 1 2 3 4 5 6 78 9 10 11 12 13 1415 16 17 18 19 20 2122 23 24 25 26 27 2829 30 31 32 33 34 3536 37 38 39 40 41 42

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

=arrayformula(如果错误(查找(序列(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、假),))

vlookup 范围的第一列是:

sequence(day(eomonth(Z3,0))+weekday(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 下方，因为 x代码>;.

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

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

<预><代码>0123456 443177 443188 443199 4432010 4432111 44​​32212 4432313 4432414 4432515 4432616 4432717 4432818 4432919 4433020 4433121 4433222 4433323 4433424 4433525 4433626 4433727 4433828 4433929 4434030 4434131 4434232 4434333 4434434 4434535 4434636 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上更新了表格google.com/spreadsheets/d/1u_J7bmOFyDlYXhcL5dW3CHFJ1esySAKK_yPc6nFTdLA/edit#gid=68642071

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

如果你想要数字，那么使用:

=arrayformula(如果错误(查找(序列(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、假),))

I've made a calendar sheet and would like to fill it using an Arrayformula or some kind of Lookup. The problem is, the code in each cell is different, do I need it all to be the same code or is it possible to do an Arrayformula that does a different formula for each line?

I spent ages getting the calendar code working but would now like to simplify the code and I'm not sure what my next step should be:

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

Any advice would be much appreciated.

解决方案

I've added a new sheet in your file called 'Aresvik'.

The green cells have new formula.

Cell B3 can be =date(B1,1,1)

Then each successive month can be =eomonth(B3,0)+1, =eomonth(J3,0)+1 etc.

The date formula in cell B5 is:

=arrayformula(iferror(vlookup(sequence(7,7,1),{array_constrain(sequence(40,1),day(eomonth(B3,0))+weekday(B3,3),1),query({flatten(split(rept(",",day(eomonth(B3,0))-1),",",0,0));sequence(day(eomonth(B3,0)),1,1)},"offset "&day(eomonth(B3,0))-weekday(B3,3)&" ",0)},2,false),))

It can be copied to each other cell below Mo, so B5 will change to J5, R5, Z5 etc.

Notes

The concept revolves around using the SEQUENCE function to generate a grid of numbers, 6 rows, 7 columns:

sequence(6,7)

which looks like this:

 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

Then using these numbers in a VLOOKUP to get a corresponding date for the calendar. If the first of the month falls on a Thursday (April 2021), the vlookup range needs 3 gaps at the top of the list of dates. player0 has a more elegant solution than my original query using offset, so I've incorporated it below. Cell Z3 is the date 1/4/2021:

=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)
,))

The first column in the vlookup range is:

sequence(day(eomonth(Z3,0))+weekday(Z3,2),1,0)

which is an array of numbers from 0, corresponding with the number of days in the month plus the number of gaps before the 1st day.

The second column in the vlookup range is:

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

It is an array of 2 columns in this format: {x;y}, where y sits below x because of the ;.

These are the gaps: iferror(sequence(weekday(Z3,2),1)/0,), followed by the date numbers: sequence(day(eomonth(Z3,0)),1,Z3)

(Example below is April 2021):

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

The vlookup takes each number in the initial sequence (6x7 layout), and brings back the corresponding date from col2 in the range, based on a match in col1.

When the first day of the month is a Monday, iferror(sequence(weekday(BB1,2),1)/0,) generates a gap in col2 of the vlookup range. This is why col1 in the vlookup range has to start with 0.

I've updated the sheet at https://docs.google.com/spreadsheets/d/1u_J7bmOFyDlYXhcL5dW3CHFJ1esySAKK_yPc6nFTdLA/edit#gid=68642071

Values on the calendar are dates so the formatting has to be d.

If you want numbers, then use:

=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)
,))

这篇关于使用 Arrayformula 或 LOOKUP 填充日历的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！