Блог

Я сделал лист календаря и хотел бы заполнить его с помощью Arrayformula или какого-то поиска. Проблема в том, что код в каждой ячейке разный, нужно ли мне, чтобы все это было одним и тем же кодом, или можно создать Arrayformula, которая выполняет разные формулы для каждой строки?

Я потратил целую вечность на то, чтобы код календаря работал, но теперь хотел бы упростить код, и я не уверен, каким должен быть мой следующий шаг:

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

Любой совет был бы очень признателен.

Я добавил новый лист в ваш файл под названием «Аресвик».

Зеленые клетки имеют новую формулу.

Ячейка B3 может быть =date(B1,1,1)

Затем каждый последующий месяц может быть =eomonth(B3,0) 1 и =eomonth(J3,0) 1 т. Д.

Формула даты в ячейке B5 выглядит так:

=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 "amp;day(eomonth(B3,0))-weekday(B3,3)amp;" ",0)},2,false),))

Его можно скопировать в каждую другую ячейку ниже Mo , поэтому B5 он изменится на J5 R5 , Z5 и т. Д.

Примечания

Концепция вращается вокруг использования SEQUENCE функции для создания сетки чисел, 6 строк, 7 столбцов:

sequence(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
 

Затем используйте эти цифры в a VLOOKUP , чтобы получить соответствующую дату для календаря. Если первое число месяца приходится на четверг (апрель 2021 года), диапазон vlookup нуждается в 3 пробелах в верхней части списка дат. У player0 есть более элегантное решение , чем мое первоначальное query использование offset , поэтому я включил его ниже. Ячейка Z3-это дата 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)
,))
 

Первый столбец в диапазоне vlookup-это:

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

который представляет собой массив чисел от 0, соответствующий количеству дней в месяце плюс количеству пробелов до 1-го дня.

Второй столбец в диапазоне 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 года):

 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 принимает каждое число в начальной последовательности (макет 6×7) и возвращает соответствующую дату из col2 в диапазоне на основе совпадения в col1.

Когда первый день месяца приходится на понедельник, iferror(sequence(weekday(BB1,2),1)/0,) создается разрыв в col2 диапазона vlookup. Вот почему col1 в диапазоне vlookup должен начинаться с 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)
,))
 

более короткое решение:

 =INDEX(IFNA(VLOOKUP(SEQUENCE(6, 7), {SEQUENCE(DAY(EOMONTH(B3, )) WEEKDAY(B3, 2), 1, ), 
 {IFERROR(ROW(INDIRECT("1:"amp;WEEKDAY(B3, 2)))/0); SEQUENCE(DAY(EOMONTH(B3, )), 1, B3)}}, 2, )))