Crea un calendario in Excel con una formula utilizzando la formula inserita in matrice.
Guarda questa figura:
Quella formula =Coolè la stessa in ogni cella da B5: H10! Guarda:
È stato inserito in matrice una volta selezionato B5: H10. In questo articolo vedrai cosa c'è dietro la formula.
A proposito, c'è una cella che non è ancora mostrata che è il mese da visualizzare. Cioè, la cella J1 contiene =TODAY(), (e sto scrivendo questo a dicembre) ma se lo cambi in 5/8/2012, vedresti:
Questo è maggio 2012. OK, decisamente fantastico! Inizia dall'inizio e procedi fino a questa formula nel calendario e guarda come funziona.
Inoltre, supponiamo che oggi sia l'8 maggio 2012.
Per prima cosa, guarda questa figura:
La formula non ha davvero senso. Lo sarebbe, se fosse circondato da =SUM, ma vuoi vedere cosa c'è dietro la formula, quindi la espanderai selezionandola e premendo il tasto F9.
La figura sopra diventa la figura sotto quando si preme il tasto F9.
Nota che c'è un punto e virgola dopo il 3: questo indica una nuova riga. Le nuove colonne sono rappresentate da una virgola. Quindi ne trarrai vantaggio.
Il numero di settimane in un mese varia, ma nessun calendario necessita di più di sei righe per rappresentare un mese e, naturalmente, hanno tutti sette giorni. Guarda questa figura:
Inserisci manualmente i valori da 1 a 42 in B5: H10, e se inserisci =B5:H10una cella e poi espandi la barra della formula, vedrai cosa viene mostrato qui:
Notare la posizione del punto e virgola - dopo ogni multiplo di 7 - che indica una nuova riga. Questo è l'inizio della formula, ma invece di una così lunga, puoi usare questa formula più breve. Seleziona B5: H10. genere
=(0;1;2;3;4;5)*7+(1,2,3,4,5,6,7)
come formula, ma non premere Invio.
Per dire a Excel che questa è una formula di matrice, devi tenere premuto Ctrl + Maiusc con la mano sinistra. Tenendo premuto Ctrl + Maiusc, premi Invio con la mano destra. Quindi, rilascia Ctrl + Maiusc. Per il resto di questo articolo, questo set di sequenze di tasti si chiamerà Ctrl + Maiusc + Invio.
Se hai fatto Ctrl + Maiusc + Invio correttamente, le parentesi graffe appariranno intorno alla formula nella barra della formula e i numeri da 1 a 42 appariranno in B5: H10 come mostrato qui:
Nota che stai prendendo i numeri da 0 a 5 separati da punto e virgola (nuova riga per ciascuno) e moltiplicandoli per 7, ottenendo effettivamente questo:
L'orientamento verticale di questi valori aggiunto all'orientamento orizzontale dei valori da 1 a 7 restituisce gli stessi valori mostrati. L'espansione di questo è identica a quella che avevi prima. Supponi ora di aggiungere OGGI a questi numeri?
Nota: la modifica di una formula di matrice esistente è molto complicata. Attentamente, segui questi passaggi: Seleziona B5: H10. Fare clic su nella barra della formula per modificare la formula esistente. Digita + J1 ma non premere Invio. Per accettare la formula modificata, premi Ctrl + Maiusc + Invio.
Il risultato per l'8 maggio 2012 è:
Questi numeri sono numeri di serie (il numero di giorni dall'1 / 1/1900). Se li formatti come date brevi:
Chiaramente non va bene, ma ci arriverai. Che cosa succede se li formatti semplicemente come "d" per il giorno del mese:
Sembra quasi un mese, ma nessun mese inizia con il nono del mese. Ah, ecco un problema. Hai usato J1 che contiene 5/8/2012 e devi davvero usare la data del primo del mese. Quindi supponi di inserire =DATE(YEAR(J1),MONTH(J1),1)J2:
La cella J1 contiene 5/8/2012 e la cella J2 lo cambia al primo del mese di qualunque cosa sia stata inserita in J1. Quindi, se cambi J1 nella formula del calendario in J2:
Più vicino, ma ancora non corretto. È necessario un ulteriore aggiustamento, ovvero devi sottrarre il giorno della settimana del primo giorno. Cioè, la cella J3 contiene =WEEKDAY(J2). 3 rappresenta martedì. Quindi ora se sottrai J3 da questa formula, ottieni:
Ed è proprio così per maggio 2012!
Ok, sei davvero vicino. Ciò che è ancora sbagliato è che il 29 e il 30 di aprile vengono visualizzati nel calendario di maggio e anche dall'1 al 9 giugno. Devi cancellarli.
È possibile assegnare un nome alla formula per un riferimento più semplice. Chiamalo "Cal" (non ancora "cool"). Vedi questa figura:
Quindi puoi modificare la formula in semplicemente =Cal(sempre Ctrl + Maiusc + Invio):
Ora puoi cambiare la formula per leggere che se il risultato è nella riga 5 e il risultato è superiore a 20, diciamo, il risultato dovrebbe essere vuoto. La riga 5 conterrà la prima settimana di qualsiasi mese, quindi non dovresti mai vedere valori superiori a 20 (o qualsiasi numero superiore a sette sarebbe sbagliato - un numero come 29 che vedi nella cella B5 della figura sopra è del mese precedente). Quindi puoi usare =IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),Cal):
Innanzitutto, nota che le celle B5: D5 sono vuote. La formula ora legge "se questa è la riga 5, quindi se il GIORNO del risultato è superiore a 20, mostra vuoto".
Puoi continuare a rimuovere i numeri bassi alla fine, i valori del mese prossimo. Ecco come farlo facilmente.
Modifica la formula e seleziona il riferimento finale a "Cal"
Inizia a digitare IF (ROW ()> 8, IF (DAY (Cal) <15, "", Cal), Cal) per sostituire la Cal.
La formula finale dovrebbe essere
=IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),IF(ROW()>8,IF(DAY(Cal)<15,"",Cal),Cal))
Premi Ctrl + Maiusc + Invio. Il risultato dovrebbe essere:
Rimangono due cose da fare. Puoi prendere questa formula e darle un nome, "Fantastico":
Quindi usalo nella formula mostrata qui:
A proposito, i nomi definiti vengono trattati come se fossero inseriti in array.
Quello che resta da fare è formattare le celle e inserire i giorni della settimana e il nome del mese. Quindi allarghi le colonne, aumenti l'altezza della riga, aumenti la dimensione del carattere e allinea il testo:
Quindi metti i bordi attorno alle celle:
Unisci e centra il mese e l'anno e formattalo:
Quindi disattiva le linee della griglia e voilà:
Questo articolo ospite è tratto da Bob Umlas, MVP di Excel. È tratto dal libro, Excel Outside the Box. Per vedere gli altri argomenti del libro, fare clic qui.
