He hecho una hoja de calendario y me gustaría llenarlo usando una arrayformula o algún tipo de búsqueda. El problema es que el código en cada celda es diferente, ¿necesito que todo sea el mismo código o es posible realizar una fórmula de matrayformula que hace una fórmula diferente para cada línea?

Pasé las edades que trabajan en el código del calendario, pero ahora le gustaría simplificar el código y no estoy seguro de cuál debería ser mi siguiente paso:

https://docs.google.com/spraadsheets/d/1u_j7bmofydlyxhcl5dw3chfj1esksakk_ypc6nftdla/edit?usp=sharing

Cualquier consejo sería muy apreciado.

2
JoeW 29 may. 2021 a las 06:27

2 respuestas

La mejor respuesta

He agregado una nueva hoja en su archivo llamado 'ARESVIK'.

Las células verdes tienen una nueva fórmula.

Cell B3 puede ser =date(B1,1,1)

Entonces cada mes sucesivo puede ser =eomonth(B3,0)+1, =eomonth(J3,0)+1 etc.

La fórmula de fecha en celda B5 es:

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

Se puede copiar mutuamente en la celda debajo de Mo, por lo que B5 cambiará a J5, R5, Z5, Z5, etc.

enter image description here

Notas

El concepto gira en torno a la función SEQUENCE para generar una cuadrícula de números, 6 filas, 7 columnas:

sequence(6,7)

Que se ve así:

 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

Luego, utilizando estos números en un VLOOKUP para obtener una fecha correspondiente para el calendario. Si el primero del mes cae en un jueves (de abril de 2021), la gama Vlookup necesita 3 huecos en la parte superior de la lista de fechas. El jugador0 tiene una solución más elegante que mi original query usando offset, así que lo he incorporado a continuación. Cell Z3 es la fecha 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)
,))

La primera columna en el rango de Vlookup es:

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

Que es una matriz de números de 0, correspondiente a la cantidad de días en el mes más el número de huecos antes del 1er día.

La segunda columna en el rango de Vlookup es:

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

Es una matriz de 2 columnas en este formato: {x;y}, donde y se encuentra debajo de x debido a la ;.

Estos son los huecos: iferror(sequence(weekday(Z3,2),1)/0,), seguido de los números de fecha: sequence(day(eomonth(Z3,0)),1,Z3)

(El siguiente ejemplo es de abril de 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

El Vlookup toma cada número en la secuencia inicial (diseño 6x7) y devuelve la fecha correspondiente de COL2 en el rango, en función de una coincidencia en COL1.

Cuando el primer día del mes es un lunes, iferror(sequence(weekday(BB1,2),1)/0,) genera una brecha en COL2 del rango de Vlookup. Esta es la razón por la que COL1 en la gama VLOOKUP tiene que comenzar con 0.

He actualizado la hoja en https://docs.google.com / SpreadSheets / D / 1U_J7BMOFYDLYXHCL5DW3CHFJ1ESYSYAKKK_YPC6NFTDLA / Editar # GID = 68642071

Los valores en el calendario son fechas para que el formato tiene que ser d.

Si quieres números, entonces usa:

=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)
,))
3
Aresvik 31 may. 2021 a las 01:40

Solución más corta:

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

enter image description here

2
player0 29 may. 2021 a las 21:17