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
Ho utilizzato la formula per "dare vita" a un modello di calendario del 2019 che ho trovato tra i modelli di Google. Con questo link è possibile vedere il calendario e si può farne una copia da modificare:
https://docs.google.com/spreadsheets/d/1W1Z9JkUb58wDi8LRxS4XcrKcwWbTFWxmNxUq_cHx8BA/edit?usp=sharing
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
ARRAYFORMULA( RIF.RIGA(INDIRETTO(inizioMese & ":" & fineMese)) )
(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.
Formula monocella, indipendente
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.
Nella formula, z1DataIni è la data del primo giorno del mese, supponendo di avere assegnato alla cella contenente la data, il nome z1DataIni (per farlo si seleziona la cella e nella finestrella dove compare la coordinata, si scrive il nome che si vuole assegnare).
Volendo utilizzare la coppia Anno-Mese, si può sostituire ogni occorrenza di z1DataIni con
"DATA(Anno; Mese; 1)"
Se si volesse ottenere solo il mese corrente allora si potrebbe sostituire a z1DataIni l'espressione
DATA(ANNO(OGGI()); MESE(OGGI()); 1)
come indicato più sotto.
=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: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.
Una via è quella di non utilizzare la formula precedente se il primo giorno del mese è domenica.
Ecco la parte relativa alla generazione della prima riga:
La riga 5
La riga 5 è stata risolta in un modo "ridondante".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.
La riga 5 ha re casi da considerare:
- 1) se sono sufficienti le prime 28 celle, allora la riga 5 avrà 7 celle vuote;
- 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.
Nella espressione, estratta dalla formula generale, ARRAY_CONSTRAIN() "taglia" il risultato a 7 colonne.
La riga 6, ultima
La formula "originale" trovata su Youtube e formattata in più righe
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.
La formula presentata su Youtube modificata con (JOIN() invece di QUERY():
=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
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
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.
Anno in C3
Mese in C4
Nessun commento:
Posta un commento
NOTA: Attualmente i commenti sono moderati, vale a dire che non compariranno immediatamente. Questo mi consente sia eliminare eventuale spam sia di leggere i messaggi e eventualmente rispondere.