Grazie a un video di Youtube ho trovato una formula intrigante che permette di formattare i giorni di un calendario mensile su 7 colonne in modo che la domenica sia sempre a sinistra.
Il video non spiega come funziona la formula e ho voluto cercare di capirlo, per impossessarmi dei "trucchi" usati. Il link del video è questo. Immagino che la stessa formula sia stata presentata anche altrove.
https://www.youtube.com/watch?v=i3mPE7c-wt8
La formula di Youtube si basa su un uso "creativo" della funzione QUERY() (ma si può usare anche JOIN()) per comporre una stringa con tutti i "vuoti" e i numeri utili a comporre la griglia del mese. La stringa ha un separatore per ogni elemento più un secondo separatore ogni sette elementi. La stringa, viene elaborata con la funzione SPLIT() per dare luogo alla matrice di 6 righe da 7 colonne.
Io l'ho riscritta utilizzando JOIN() invece di QUERY() perché mi pare abbia più senso.
In coda inserisco le due versioni, formattate in modo per me più leggibile della versione originale.
Al fondo c'è una versione a mio avviso migliorata.
Anticipazione: Dopo una prima stesura mi sono accorto che c'erano ancora dei casi che portavano a malfunzionamenti (il gennaio 2022 inizia da sabato e metteva in crisi la riga 5, o il febbraio 2015, che inizia da domenica e sta tutto nelle prime 4 righe). Ora mi pare tutto a posto e ho cercato di riportare tutto con precisione perché la minima svista può impedire il funzionamento.
Lo scopo è comunque quello di documentare le varie soluzioni che sono state messe insieme per ottenere il risultato.
La formula che presento è diversa da quella trovata (che riporto insieme a delle variazioni, in fondo).
Mentre studiavo il funzionamento della formula di Youtube ho voluto provare a fare delle variazioni e vedere se si poteva evitare la composizione e la divisione delle stringhe, generando direttamente le sei righe.
Sono riuscito, ma la formula ottenuta è più ingombrante.
Per certe parti ho sperimentato diverse soluzioni ed è stato interessante.
Direi che la soluzione con JOIN() e SPLIT() è più concisa e elegante, forse anche più flessibile se la si comprende bene.
La versione presentata è più lunga e si basa solo sulla composizione di array, non ci sono più JOIN() e SPLIT() e per ottenere il risultato ho dovuto aggirare diversi vincoli.
Il vincolo di non appoggiarsi a celle esterne fa sì che parti della formula siano ripetute molte volte quando in un normale linguaggio di programmazione si assegnerebbe il valore a una variabile e si utilizzerebbe quella.
Un altro vincolo è che generare la sequenza di giorni di un mese occorre usare una espressione tipo
(lo spiego dopo).
Parte della complessità è il vincolo di allineare i numeri dei giorni in modo da avere la domenica a sinistra, che implica che la prima riga può contenere da nessuna a 6 celle vuote (è difficile gestire il caso di "0" celle vuote).
Dopo tanto lavoro ho deciso di documentare il lavoro fatto, per mia futura memoria e per chi sarà curioso.
Una formula "monocella" e senza riferimenti per "stampare" un calendario mensile su una griglia di 6 righe per 7 colonne, facendo in modo di avere tutte le domeniche del mese sulla colonna di sinistra e gli altri giorni a seguire.
come indicato più sotto.
La formula completa è la seguente. ho cercato di formattarla in modo che sia leggibile. Conviene forse incollarla in un differente editor di testo per evitare gli accapo.
=ARRAYFORMULA({
SE(GIORNO.SETTIMANA(z1DataIni; 1) = 1;
MATR.TRASPOSTA(RIF.RIGA(INDIRETTO(z1DataIni & ":" & z1DataIni + 6)));
MATR.TRASPOSTA({
SE.ERRORE(RIF.RIGA(INDIRETTO(1 & ":" & GIORNO.SETTIMANA(z1DataIni) - 1; 1)) / 0; "-");
RIF.RIGA(INDIRETTO(z1DataIni & ":" & (z1DataIni + 6 - (GIORNO.SETTIMANA(z1DataIni; 1) - 1))))
})
);
MATR.TRASPOSTA(
{RIF.RIGA(INDIRETTO((z1DataIni + 7 - RESTO(GIORNO.SETTIMANA(z1DataIni) + 7 - 1; 7)) & ":" & (z1DataIni + 7 - RESTO(GIORNO.SETTIMANA(z1DataIni) + 7 - 1; 7)) + 7 - 1))}
);
MATR.TRASPOSTA(
{RIF.RIGA(INDIRETTO((z1DataIni + 14 - RESTO(GIORNO.SETTIMANA(z1DataIni) + 7 - 1; 7)) & ":" & (z1DataIni + 14 - RESTO(GIORNO.SETTIMANA(z1DataIni) + 7 - 1; 7)) + 7 - 1))}
);
MATR.TRASPOSTA(
{RIF.RIGA(INDIRETTO((z1DataIni + 21 - RESTO(GIORNO.SETTIMANA(z1DataIni) + 7 - 1; 7)) & ":" & (z1DataIni + 21 - RESTO(GIORNO.SETTIMANA(z1DataIni) + 7 - 1; 7)) + 7 - 1))}
);
ARRAY_CONSTRAIN(
SE(((z1DataIni + 28 - RESTO(GIORNO.SETTIMANA(z1DataIni) + 7 - 1; 7)) <= FINE.MESE(z1DataIni; 0));
MATR.TRASPOSTA({RIF.RIGA(INDIRETTO((z1DataIni + 28 - RESTO(GIORNO.SETTIMANA(z1DataIni) + 7 - 1; 7)) & ":" & MIN(FINE.MESE(z1DataIni; 0); (z1DataIni + 28 - RESTO(GIORNO.SETTIMANA(z1DataIni) + 7 - 1; 7) +7 - 1))));
MATR.TRASPOSTA(SPLIT(RIPETI("-,"; MAX(1; (7 - (FINE.MESE(z1DataIni; 0) - (z1DataIni + 28 - RESTO(GIORNO.SETTIMANA(z1DataIni) + 7 - 1; 7)) + 1)))); ",")) });
MATR.TRASPOSTA({"-"; "-"; "-"; "-"; "-"; "-"; "-"})
)
; 1; 7);
SE(((z1DataIni + 35 - RESTO(GIORNO.SETTIMANA(z1DataIni) + 7 - 1; 7)) <= FINE.MESE(z1DataIni; 0));
MATR.TRASPOSTA({RIF.RIGA(INDIRETTO((z1DataIni + 35 - RESTO(GIORNO.SETTIMANA(z1DataIni) + 7 - 1; 7)) & ":" & MIN(FINE.MESE(z1DataIni; 0); (z1DataIni + 35 - RESTO(GIORNO.SETTIMANA(z1DataIni) + 7 - 1; 7) +7 - 1))));
MATR.TRASPOSTA(SPLIT(RIPETI("-,"; MAX(0; (7 - (FINE.MESE(z1DataIni; 0) - (z1DataIni + 35 - RESTO(GIORNO.SETTIMANA(z1DataIni) + 7 - 1; 7)) + 1)))); ",")) });
MATR.TRASPOSTA({"-"; "-"; "-"; "-"; "-"; "-"; "-"})
)
} )
Come funziona la formula in generale
Dal punto di vista generale, questa formula, messa in una cella, genera sei array orizzontali (di 7 elementi ciascuno) e li impila uno sopra l'altro, a formare la matrice di 7 colonne per 6 righe.
Dato che la colonna di sinistra corrisponde alla domenica, la prima riga può contenere da 7 giorni interi (se il mese inizia con una domenica) a un solo giorno (se il mese inizia con un sabato).
Le righe 2, 3, 4 saranno comunque piene di numeri, poiché febbraio, che è il mese più corto, ha almeno 28 giorni.
La riga 5 potrà essere da totalmente vuota a totalmente piena.
La riga 6 potrà avere al massimo 2 numeri.
Le celle occupate dai giorni possono essere da 28 a 31 e l'ultima cella può essere la 28ma (se un febbraio inizia con una domenica) fino alla (31+6)ma, nel caso estremo in cui un mese di 31 giorni inizi con un sabato. Per questo occorre avere disponibili 6 righe.
La formula è complessa perché si basa sul solo primo giorno del mese (indicato come z1DataIni) e non si appoggia a nessuna altra cella.
Come funzionano le righe 2, 3, 4, le più semplici
Le date, in Google Sheets sono indicate con "il numero di giorni dal 30 dicembre 1899". Per esempio, il 15 gennaio 2022 è rappresentato del numero 44562.
La formula deve mettere questi numeri nelle celle della griglia.
Ricordo che la formula che riempirà la griglia si trova in una singola cella, quella in alto a sinistra. Occorre pertanto che la formula crei, come risultato una matrice bidimensionale di 7x6 elementi.
I numeri dei giorni sono sequenziali e questo ci permette di usare un "trucco" per generare la sequenza che ci serve. Parlo di trucco perché usa delle funzioni solo per certi loro effetti collaterali, per di più combinati...
La funzione RIF.RIGA(), usata senza parametri riporta il numero della riga corrente ma se le si passa un intervallo, allora riporta il numero di riga. Per la precisione, se l'intervallo riguarda più righe, riporta la prima riga e basta. Sembra inutile ma...
La funzione ARRAYFORMULA() ha un effetto "esplosivo" su certe funzioni come RIF.RIGA(). Se si usa RIF.RIGA() all'interno di una funzione ARRAYFORMULA(), allora RIGA() diventa in grado di riportare "tutte le righe dell'intervallo dato", e le riporta sotto forma di un array, che sul foglio appare in forma di colonna di numeri successivi.
Se si volesse mostrare questa sequenza di numeri in orizzontale, anziché in verticale? Si può usare MATR.TRASPOSTA(), che "ruota di 90°... NO... che scambia righe e colonne... le ruota di 180° intorno alla bisettrice, direi. Comunque nel caso di una colonna, questa diventa una riga, e viceversa.
Ora potremmo avere una sequenza di numeri che viene mostrata in orizzontale:
La seguente formula rappresenta un array (statico). Gli elementi compaiono in verticale sul foglio:
={"D"; "L"; "M"; "M"; "G"; "V"; "S"}
Se li si vuole mettere in orizzontale:
=MATR.TRASPOSTA({"D"; "L"; "M"; "M"; "G"; "V"; "S"})
Da quanto detto prima, se su vuole creare un array con dei numeri consecutivi (da 1 a 7, per esempio) , si può fare:
=ARRAYFORMULA(MATR.TRASPOSTA(RIF.RIGA(A1:A7)))
oppure
=MATR.TRASPOSTA(ARRAYFORMULA(RIF.RIGA(A1:A7)))
Invece di A1:A7 si può "risparmiare" usando 1:7, che rappresenta sempre un intervallo valido (tutte le colonne, dalla riga 1 alla riga 7):
=MATR.TRASPOSTA(ARRAYFORMULA(RIF.RIGA(1:7)))
Questo è ancora un intervallo "statico". A meno che non sostituiamo "1:7" con INDIRETTO(). Potremmo ottenere lo stesso intervallo con:
=MATR.TRASPOSTA(ARRAYFORMULA(RIF.RIGA(INDIRETTO("1:7"))))
La differenza è che questa volta l'intervallo è espresso da una stringa... Se abbiamo una cella chiamata "Inizio" e una cella chiamata "Fine", nulla vieta di indicare "1" in Inizio e "7" in Fine e poi scrivere:
=MATR.TRASPOSTA(ARRAYFORMULA(RIF.RIGA(INDIRETTO(Inizio & ":" & Fine))))
Se però scrivessimo
=MATR.TRASPOSTA(ARRAYFORMULA(RIF.RIGA(INDIRETTO(OGGI() & ":" & OGGI() + 6))))
otterremmo 7 "date" (quello che si vedrà saranno probabilmente 7 numeri di 5 cifre ma impostando il formato delle celle in un formato data si potrà ottenere le date complete, o il nome del giorno, o il solo anno, o il giorno del mese (che ci potrebbe servire per il calendario).
Occorre tenere conto dell'allineamento del giorno, che fa partire il primo giorno del mese da una delle sette caselle della prima riga, e la formula della riga (la seconda, in questo caso) diventa:
MATR.TRASPOSTA(
{RIF.RIGA(INDIRETTO((z1DataIni + 7 - RESTO(GIORNO.SETTIMANA(z1DataIni) + 7 - 1; 7)) & ":" & (z1DataIni + 7 - RESTO(GIORNO.SETTIMANA(z1DataIni) + 7 - 1; 7)) + 7 - 1))}
);
Ho indicato "+ 7 -1" quando avrei potuto indicare "+ 6" ma è per chiarire l'intento della operazione.
la seconda riga parte da +7, la terza da +21, la quarta da +35, che significa che la seconda potrebbe iniziare dall'ottavo giorno del mese, o dal settimo, ... fino al secondo, in base all'allineamento.
Se per il calendario volessimo mostrare il mese corrente, potremmo partire da OGGI(), e dalla data odierna ricavare l'anno corrente (=ANNO(OGGI())) e il mese corrente (=MESE(OGGI())). A questo punto possiamo calcolare la data del primo giorno del mese corrente, con la funzione DATA(), con la forrmula:
=DATA(ANNO(OGGI()); MESE(OGGI()); 1)
Questa formula ci servirà varie volte, e se veramente vogliamo solo il calendario del mese corrente possiamo pensare di inserirla ovunque occorra avere il primo giorno del mese corrente.
Altrimenti possiamo mettere la formula in una cella "di servizio" e richiamare il contenuto di quella cella ogni volta che ci serve (diventa così la "variabile" z1DataIni che compare diverse volte nella formula completa.
Quanto abbiamo visto spiega abbastanza il modo in cui vengono generate le righe 2, 3, 4, che saranno sempre piene di numeri.
La riga 1
La riga 1 deve essere una riga di 7 elementi, dove il primo giorno del mese deve trovarsi allineato con la colonna del giorno corrispondente. Se il primo è una domenica, il primo giorno sarà nella prima colonna, se è un lunedì dovrà partire dalla seconda colonna e via di seguito, fino al sabato, che inizierà nell'ultima colonna.
Quello che serve è "riempire di vuoti" (da 0 a 6 vuoti) le prime celle, in base al giorno della settimana.
Un ausilio arriva dalla funzione GIORNO.SETTIMANA() che, in base a una data riporta un numero da 1 a 7 (1 per la domenica, 7 per il sabato, ma potrebbe cominciare anche dal lunedì). La formula
=(GIORNO.SETTIMANA(z1DataIni; 1) - 1)
ci darebbe esattamente il numero di caselle vuote da anteporre al primo giorno del mese. E il complemento di questo numero per arrivare a 7 ci darebbe quante caselle dobbiamo ancora riempire.
Il problema è che non sembra esserci modo di "gestire lo 0"... Ci arriviamo.
Al momento, come modo "dinamico" per riempire un array conosco solo l'uso di RIF.RIGA(), funzione che produce un numero. Ora ci occorrerebbe riempire un certo numero di celle con "il nulla"...
Quando occorre eseguire una funzione, e riportare un risultato anche se la funzione genera un errore si ricorre alla funzione SE.ERRORE() che ha la particolarità di valutare l'espressione data ma se questa per qualche motivo segnala un errore, allora può valutare una seconda espressione (che può anche essere un valore costante, e in genere lo è).
Immaginiamo di avere in A1 il valore, da 1 a 7 riportato da GIORNO.SETTIMANA().
Se A1 vale 3, vuol dire che il primo giorno del mese è martedì, e che nella prima riga del calendario ci occorrono due celle vuote all'inizio.
Un avvicinamento alla soluzione ci arriva dal considerare questa formula:
=ARRAYFORMULA(RIF.RIGA(INDIRETTO(1 & ":" & A1 - 1)))
che, almeno per i valori di
A1 tra 2 e 7 ci permette di ottenere da 1 a 6 numeri.
Un problema è che
INDIRETTO() e
RIF.RIGA() non accettano "numeri di riga inferiori a 1 e nemmeno intervalli di 0 celle. Ci dev'essere almeno una "cella", quindi quella formula ci darà almeno un risultato, oppure un errore (quando A1 vale 1, perché A1-1 diventa 0).
Un secondo problema è che a noi serve riempire le celle col "nulla" non con un numero.
Si può rimediare applicando due soluzioni.
Soluzione 1: eseguire RIF.RIGA(INDIRETTO(...)) trasformando il numero in un'altra cosa:
=ARRAYFORMULA(SE.ERRORE(RIF.RIGA(INDIRETTO(1 & ":" & A1-1)) / 0: ""))
Con questa formula, ogni numero generato verrà diviso per 0, generando un errore, e in caso di errore, la funzione SE.ERRORE, invece di riportare l'errore, riporterà ciò che è stato indicato come secondo parametro, in questo caso una stringa vuota.
Abbiamo così ottenuto di poter generare da una a 6 celle vuote in base a quello che sarà il giorno della settimana. Resta il problema della domenica, che richiederebbe 0 celle vuote.
Una via è quella di non utilizzare la formula precedente se il primo giorno del mese è domenica.
Un modo sarebbe questo: se domenica prendo i primi 7 giorni, come si fa per le righe 2, 3 e 4, mentre se è un altro giorni prendo da uno a 6 "vuoti" e da 6 a 1 giorno del mese.
Ecco la parte relativa alla generazione della prima riga:
MATR.TRASPOSTA(
SE(GIORNO.SETTIMANA(z1DataIni; 1) = 1;
RIF.RIGA(INDIRETTO(z1DataIni & ":" & z1DataIni + 6));
{ SE.ERRORE(RIF.RIGA(INDIRETTO(1 & ":" & GIORNO.SETTIMANA(z1DataIni) - 1; 1)) / 0; " ");
RIF.RIGA(INDIRETTO(z1DataIni & ":" & (z1DataIni + 6 - (GIORNO.SETTIMANA(z1DataIni; 1) - 1)))) }
)
);
Se la si vuole provare separatamente occorre eliminare i ";" e includere il tutto in un ARRAYFORMULA(), così:
=ARRAYFORMULA(
MATR.TRASPOSTA(
SE(GIORNO.SETTIMANA(z1DataIni; 1) = 1;
RIF.RIGA(INDIRETTO(z1DataIni & ":" & z1DataIni + 6));
{ SE.ERRORE(RIF.RIGA(INDIRETTO(1 & ":" & GIORNO.SETTIMANA(z1DataIni) - 1; 1)) / 0; " ");
RIF.RIGA(INDIRETTO(z1DataIni & ":" & (z1DataIni + 6 - (GIORNO.SETTIMANA(z1DataIni; 1) - 1)))) }
)
)
)
Un secondo modo sarebbe questo: se il primo giorno del mese è domenica prendo il primo giorno del mese altrimenti prendo da uno a 6 vuoti; in seguito, se il primo giorno del mese è domenica (e ho già il primo giorno) parto dal secondo giorno, altrimenti parto dal primo giorno e riempio la riga.
MATR.TRASPOSTA({
SE(GIORNO.SETTIMANA(z1DataIni; 1) > 1;
SE.ERRORE(RIF.RIGA(INDIRETTO(1 & ":" & GIORNO.SETTIMANA(z1DataIni) - 1; 1)) / 0; " ");
RIF.RIGA(INDIRETTO(z1DataIni & ":" & z1DataIni))
);
RIF.RIGA(INDIRETTO(SE(GIORNO.SETTIMANA(z1DataIni; 1) = 1; z1DataIni + 1; z1DataIni) & ":" & (z1DataIni + 6 - (GIORNO.SETTIMANA(z1DataIni; 1) - 1))))
}
);
La stessa sezione può essere scritta anche in questo modo. Se il primo giorno è domenica prende 7 giorni, se è uno degli altri giorni la formula compone alcuni "-" e i primi giorni del mese, per un totale di 7. In questo esempio, la funzione MATR.TRASPOSTA è applicata, ove occorre, "dentro" al SE() invece di essere esterna:
SE(GIORNO.SETTIMANA(z1DataIni; 1) = 1;
MATR.TRASPOSTA(RIF.RIGA(INDIRETTO(z1DataIni & ":" & z1DataIni + 6)));
MATR.TRASPOSTA({
SE.ERRORE(RIF.RIGA(INDIRETTO(1 & ":" & GIORNO.SETTIMANA(z1DataIni) - 1; 1)) / 0; "-");
RIF.RIGA(INDIRETTO(z1DataIni & ":" & (z1DataIni + 6 - (GIORNO.SETTIMANA(z1DataIni; 1) - 1))))
})
);
Un altro modo può essere questo, che usa FLATTEN() invece di {} e usa SPLIT() e RIPETI() per generare da 1 a 6 elementi vuoti "-" anziché il metodo della generazione di un errore. In questo caso viene gestita prima la condizione coln alcune celle vuote e poi quella con tutte le celle piene:
MATR.TRASPOSTA(FLATTEN(
SE(GIORNO.SETTIMANA(z1DataIni; 1) > 1;
MATR.TRASPOSTA(SPLIT(RIPETI("-,"; GIORNO.SETTIMANA(z1DataIni) - 1);","));
RIF.RIGA(INDIRETTO(z1DataIni & ":" & z1DataIni))
);
RIF.RIGA(INDIRETTO(SE(GIORNO.SETTIMANA(z1DataIni; 1) = 1; z1DataIni + 1; z1DataIni) & ":" & (z1DataIni + 6 - (GIORNO.SETTIMANA(z1DataIni; 1) - 1))))
)
);
E con questo abbiamo la prima riga, la seconda, la terza e la quarta.
La riga 5
La riga 5 è stata risolta in un modo "ridondante".Quando si lavora con ARRAYFORMULA() è importante che tutti gli intervalli abbiano la stessa misura, altrimenti viene generato un errore. Ci servono tutte righe di 7 celle ciascuna, non basta riempire le celle necessarie e lasciare vuote le celle a destra, se non sono usate.
Una soluzione è però quella di creare un array più lungo del necessario e poi tagliarlo. Questo possiamo farlo con ARRAY_CONSTRAIN()), che limita l'estensione di un array a un massimo di righe e di colonne.
Oltre a questo abbiamo anche il caso speciale che quando un febbraio di 28 giorni inizia con una domenica, la riga 5 deve stare completamente vuota.
In un linguaggio di programmazione normale avremmo un ciclo con una variabile che rappresenta il giorno, e sarebbe facile incrementare il giorno a ogni ciclo e se diventa maggiore dell'ultimo giorno del mese indicare "" invece del numero. Qui non è possibile.
Se usiamo RIF.RIGA(INDIRETTO() siamo vincolati a generare almeno un numero.
La riga 5 ha re casi da considerare:
- 1) se sono sufficienti le prime 28 celle, allora la riga 5 avrà 7 celle vuote;
- 2) se il calendario richiede più di 28 celle, ma meno di 35 allora ci sarà almeno un vuoto;
- 3) se il calendario richiede tutte le celle della riga 5, la riga avrà 7 celle piene e nessun vuoto.
Dato che non si può prendere "0 giorni" e non si possono creare "0 vuoti" occorrerebbe usare un SE() dentro a un SE e il test diventerebbe lunghissimo.
Per ora ho risolto distinguendo il caso "tutti vuoti" dal 1-7 pieni e in quest'ultimo caso, quando sono 7 pieni (che si verifica ad esempio col gennaio 2022) ho dovuto creare almeno un vuoto e utilizzare ARRAY_CONSTRAIN() per tagliare a 7 i risultati.
Nel primo caso, questa volta si generano tutti i giorni, dal 29esimo in poi e a questi elementi vengono accodate 7 stringhe nulle, quelle eccedenti verranno tagliate.
Nella espressione, estratta dalla formula generale, ARRAY_CONSTRAIN() "taglia" il risultato a 7 colonne.
Il MAX(1; ...) nella formula doveva essere un MAX(0; ... ), per generare anche 0 vuoti ma ho dovuto evitare che accadesse
ARRAY_CONSTRAIN(
SE(((z1DataIni + 28 - RESTO(GIORNO.SETTIMANA(z1DataIni) + 7 - 1; 7)) <= FINE.MESE(z1DataIni; 0));
MATR.TRASPOSTA({RIF.RIGA(INDIRETTO((z1DataIni + 28 - RESTO(GIORNO.SETTIMANA(z1DataIni) + 7 - 1; 7)) & ":" & MIN(FINE.MESE(z1DataIni; 0); (z1DataIni + 28 - RESTO(GIORNO.SETTIMANA(z1DataIni) + 7 - 1; 7) +7 - 1))));
MATR.TRASPOSTA(SPLIT(RIPETI("-,"; MAX(1; (7 - (FINE.MESE(z1DataIni; 0) - (z1DataIni + 28 - RESTO(GIORNO.SETTIMANA(z1DataIni) + 7 - 1; 7)) + 1)))); ",")) });
MATR.TRASPOSTA({"-"; "-"; "-"; "-"; "-"; "-"; "-"})
)
; 1; 7);
Questa non funzionava col febbraio 2022 perché
MAX(0; ...) riportava 0 e veniva generato un errore)
SE(((z1DataIni + 28 - RESTO(GIORNO.SETTIMANA(z1DataIni) + 7 - 1; 7)) <= FINE.MESE(z1DataIni; 0));
MATR.TRASPOSTA({RIF.RIGA(INDIRETTO((z1DataIni + 28 - RESTO(GIORNO.SETTIMANA(z1DataIni) + 7 - 1; 7)) & ":" & MIN(FINE.MESE(z1DataIni; 0); (z1DataIni + 28 - RESTO(GIORNO.SETTIMANA(z1DataIni) + 7 - 1; 7) +7 - 1))));
MATR.TRASPOSTA(SPLIT(RIPETI("-,"; MAX(0; (7 - (FINE.MESE(z1DataIni; 0) - (z1DataIni + 28 - RESTO(GIORNO.SETTIMANA(z1DataIni) + 7 - 1; 7)) + 1)))); ",")) });
MATR.TRASPOSTA({"-"; "-"; "-"; "-"; "-"; "-"; "-"})
);
Una versione diversa creava semplicemente un array più lungo del necessario accodando 6 elementi vuoti e lo troncava usando ARRAY_CONSTRAIN()
ARRAY_CONSTRAIN(
SE(((z1DataIni + 28 - RESTO(GIORNO.SETTIMANA(z1DataIni) + 7 - 1; 7)) <= FINE.MESE(z1DataIni; 0));
MATR.TRASPOSTA({RIF.RIGA(INDIRETTO((z1DataIni + 28 - RESTO(GIORNO.SETTIMANA(z1DataIni) + 7 - 1; 7)) & ":" & MIN(FINE.MESE(z1DataIni; 0); (z1DataIni + 28 - RESTO(GIORNO.SETTIMANA(z1DataIni) + 7 - 1; 7) +7 - 1))));
{""; ""; ""; ""; ""; ""} });
MATR.TRASPOSTA({""; ""; ""; ""; ""; ""; ""})
); 1; 7);
La riga 6, ultima
Un po' come per la riga 5, ma in questo caso si verifica se il calendario richiede più di 35 celle. Questa compone un array di 7 elementi (Da tutti vuoti a due "pieni") usa SPLIT() e RIPETI() per generare da 1 a 6 "vuoti" e usa un array statico per il caso di 7 vuoti, ma anche lì potrebbe essere usato SPLIT/RIPETI.
SE(((z1DataIni + 35 - RESTO(GIORNO.SETTIMANA(z1DataIni) + 7 - 1; 7)) <= FINE.MESE(z1DataIni; 0));
MATR.TRASPOSTA({RIF.RIGA(INDIRETTO((z1DataIni + 35 - RESTO(GIORNO.SETTIMANA(z1DataIni) + 7 - 1; 7)) & ":" & MIN(FINE.MESE(z1DataIni; 0); (z1DataIni + 35 - RESTO(GIORNO.SETTIMANA(z1DataIni) + 7 - 1; 7) +7 - 1))));
MATR.TRASPOSTA(SPLIT(RIPETI("-,"; MAX(0; (7 - (FINE.MESE(z1DataIni; 0) - (z1DataIni + 35 - RESTO(GIORNO.SETTIMANA(z1DataIni) + 7 - 1; 7)) + 1)))); ",")) });
MATR.TRASPOSTA({"-"; "-"; "-"; "-"; "-"; "-"; "-"})
)
}
Questa era la versione con ARRAY_CONSTRAIN(), che accodava sempre 6 "vuoti" e tagliava l'eccesso (ricordo che per generare la matrice di 7 x 6, ARRAYFORMULA esige che tutte le righe siano di 7 elementi esatti):
ARRAY_CONSTRAIN(
SE(((FINE.MESE(z1DataIni; 0) - z1DataIni +1) + (GIORNO.SETTIMANA(z1DataIni) - 1)) > 35;
MATR.TRASPOSTA({RIF.RIGA(INDIRETTO((z1DataIni + 35 - (GIORNO.SETTIMANA(z1DataIni) - 1)) & ":" & FINE.MESE(z1DataIni; 0))); {""; ""; ""; ""; ""; ""; ""} });
MATR.TRASPOSTA({ {""; ""; ""; ""; ""; ""; ""} })
);1;7)
La formula "originale" trovata su Youtube e formattata in più righe
Purtroppo non riesco a commentarla adesso ma gli elementi dati prima possono servire a comprenderne una parte.
La funzione QUERY utilizzata qui è fuorviante, e può essere sostituita da JOIN, come nella versione che segue più sotto.
Quello che si ottiene dalla funzione QUERY() (o con JOIN()) è una stringa di questo tipo:
,,,,,44652,44653,|44654,44655,44656,44657,44658,44659,44660,|44661,44662,44663,
dove le virgole separano i singoli elementi e i caratteri "|" separano i gruppi di 7 elementi tra loro.
Lo SPLIT() più interno separa i blocchi di 7 mentre quello più esterno separa gli elementi.
Ho capito che vengono generati elementi, fatti seguire da "," e, ogni 7 viene anche aggiunto il carattere "|"... Capisco che lo fa ma non ho ancora inteso bene secondo quale principio questo avviene. Sono abituato a ragionare secondo una logica sequenziale, invece quando si è dentro un ARRAYFORMULA() le cose mi pare accadano diversamente.
=ArrayFormula(
split(
MATR.TRASPOSTA(
split(
QUERY(
SE(
GIORNO.SETTIMANA(DATA(K3; K2; 1); 1) = 1;
RIF.RIGA(INDIRETTO(DATA(K3; K2; 1) & ":" & FINE.MESE(DATA(K3; K2; 1); 0)));
{
SE.ERRORE(RIF.RIGA(INDIRETTO("A1:A" & GIORNO.SETTIMANA(DATA($K$3; K2; 1)) - 1; 1)) / 0; " ");
RIF.RIGA(INDIRETTO(DATA(K3;K2;1) & ":" & FINE.MESE(DATA(K3; K2; 1); 0)))
}
) & "," &
SE(
RESTO(RIF.RIGA(INDIRETTO("A1:A" & (DATA.DIFF(DATA($K$3; K2; 1); FINE.MESE(DATA($K$3; K2; 1); 0); "D") + (GIORNO.SETTIMANA(DATA($K$3; K2; 1)))))); 7) = 0;
"|";
);
;
9^9
);
"|"
)
);
","
)
) La formula presentata su Youtube modificata con (JOIN() invece di QUERY():
K3 è l'anno
K2 è il mese
=ARRAYFORMULA(
SPLIT(
MATR.TRASPOSTA(
SPLIT(
JOIN(
"";
SE(GIORNO.SETTIMANA(DATA(K3; K2; 1); 1) = 1;
{
RIF.RIGA(INDIRETTO(DATA(K3; K2; 1) & ":" & FINE.MESE(DATA(K3; K2; 1); 0)))
};
{
SE.ERRORE(RIF.RIGA(INDIRETTO(1 & ":" & GIORNO.SETTIMANA(DATA(K3; K2; 1)) - 1; 1)) / 0; " ");
RIF.RIGA(INDIRETTO(DATA(K3; K2; 1) & ":" & FINE.MESE(DATA(K3; K2; 1); 0)))
}
) & "," &
SE(
RESTO(RIF.RIGA(INDIRETTO(1 & ":" & DATA.DIFF(DATA(K3; K2; 1); FINE.MESE(DATA(K3; K2; 1); 0); "D") + (GIORNO.SETTIMANA(DATA(K3; K2; 1))))); 7) = 0;
"|";
)
);
"|"
)
);
","
)
)
Variazione nella generazione delle celle vuote
K3 è l'anno
K2 è il mese
Una variazione che usa MATR.TRASPOSTA(SPLIT(RIPETI())) per generare i "vuoti" iniziali, invece di SE.ERRORE(RIF.RIGA(...) /0; " ")
=ARRAYFORMULA(
SPLIT(
MATR.TRASPOSTA(
SPLIT(
JOIN(
"";
SE(GIORNO.SETTIMANA(DATA(K3; K2; 1); 1) = 1;
{
RIF.RIGA(INDIRETTO(DATA(K3; K2; 1) & ":" & FINE.MESE(DATA(K3; K2; 1); 0)))
};
{
MATR.TRASPOSTA(SPLIT(RIPETI(" ,"; GIORNO.SETTIMANA(DATA(K3; K2; 1)) - 1); ","));
RIF.RIGA(INDIRETTO(DATA(K3; K2; 1) & ":" & FINE.MESE(DATA(K3; K2; 1); 0)))
}
) & "," &
SE(
RESTO(RIF.RIGA(INDIRETTO(1 & ":" & DATA.DIFF(DATA(K3; K2; 1); FINE.MESE(DATA(K3; K2; 1); 0); "D") + (GIORNO.SETTIMANA(DATA(K3; K2; 1))))); 7) = 0;
"|";
)
);
"|"
)
);
","
)
)
Versione del 18 gennaio 2022. Ora mi piace
Ecco, a questa ci sono arrivato dopo essere passato per tutto il resto.
Meriterebbe una esplorazione.
La cosa centrale è che la parte SE.ERRORE(...) risolve il problema di generare zero o più "vuoti ("x", in questo caso). L'espressione parte dalla prima cella del calendario e, se non è un giorno di quel mese mette la x, altrimenti mette il giorno del mese. In particolare la parte (1 / RIF.RIGA...) >= C5) ha la funzione di valere 1 se è un giorno del mese oppure generare un errore se è ancora un giorno del mese precedente.
Questa si basa su una unica cella dove si trova il primo giorno del mese (ottenibile con =DATA(anno, mese, 1)
