Gestione prenotazioni: come realizzare un Calendario in Excel

Il tutorial è stato pensato per facilitare la conduzione di una piccola attività – nel nostro caso un autonoleggio – ma può  essere adattato a qualsiasi tipo di impresa che ricorra alla gestione delle prenotazioni.

Ambiente di lavoro

Il primo passo consiste nel preparare l’ambiente di lavoro. A questo scopo, dopo aver aperto una nuova cartella eliminiamo tutti i fogli tranne il primo:

1. Click destro sull’etichetta Foglio2
2. Click su Elimina
3. Ripetere l’operazione con il foglio3

Ora, con l’ausilio del tasto destro, rinominiamo il foglio1 come “2012”, ipotizzando che le prenotazioni del nostro autonoleggio si riferiscano all’anno corrente.

Tabella

Ora è necessario riprodurre la tabella che conterrà il  calendario automatizzato:

Scarica il Modello pronto

Per chi preferisce imparare a realizzarlo da sé, ecco i passaggi da seguire.

Scarica il Modello da compilare

Scrivere nella cella A1 la formula che riporterà automaticamente nella cella il nome del foglio (in questo caso “2012”): =STRINGA.ESTRAI(CELLA(“FILENAME”;G76);TROVA(“]”;CELLA(“FILENAME”;G76))+1;255)

Scrivere su A2 “Day”, in basso troveranno posto i giorni della settimana del nostro calendario. Infine, scrivere su B2 “Calendar”, in questa colonna creeremo il calendario vero e proprio con tutte le date dell’anno 2012.

In orizzontale, partendo da C2, iniziate a scrivere i nomi  delle auto che potranno essere noleggiate durante tutto l’anno 2012. Successivamente si potrà formattare la tabella.

Generare il calendario

Posizionarsi sulla cella B3 e digitare la formula =DATA.VALORE(“01/”&”01/”&$A$1) per impostare come primo valore del calendario la data primo gennaio dell’anno trascritto su A1, cioè 2012.

Se, nella cella A1, anziché la data viene rappresentato un valore numerico, occorre modificare il formato della cella in questo modo:

• Click destro sulla cella e poi su “Formato Celle”
• Nella finestra omonima, click in alto sulla scheda Numero
• Selezionare la voce DATA nell’elenco e poi scegliere il formato senza l’anno (solo giorno e mese) e poi click su OK

Posizionarsi su B4 e digitare la  formula =B3+1 per aumentare di un’unità il valore della data trascritta in precedenza. Se necessario, impostare lo stesso formato della cella precedente e utilizzare il quadratino di riempimento per ricopiare la formula appena trascritta fino alla cella B368.

Estrapoliamo ora dalle date appena generate i giorni della settimana: posizionarsi nella cella A3 e digitare la formula =SCEGLI(GIORNO.SETTIMANA(B3); “dom”; “lun”; “mar”; “mer”; “gio”; “ven”;”sab”). Poi utilizziamo il quadratino di riempimento per ricopiare la formula fino alla cella A368.

Formattare il calendario

La procedura seguente ci consentirà di colorare di rosso i giorni dell’anno corrispondenti alla domenica. Selezioniamo tutti i giorni appena ottenuti (ovvero l’intervallo tra A3 e A368) e richiamiamo la formattazione condizionale per colorare di rosso le date corrispondenti alla domenica:

• Attiviamo la barra multifunzione HOME
• Dal riquadro STILI  clicchiamo il pulsante FORMATTAZIONE CONDIZIONALE
• Nel menù scegliamo REGOLE EVIDENZIAZIONE CELLE e successivamente UGUALE A…

Nella finestra visualizzata digitiamo “dom” nel campo “Formatta celle con…” e dal menù adiacente scegliamo “Testo rosso”: cliccando su Ok  tutte le celle corrispondenti alle domeniche diverranno rosse.

Selezioniamo tutte le date (intervallo di celle tra B3 e B368), richiamiamo la FORMATTAZIONE CONDIZIONALE, come prima, e scegliamo NUOVA REGOLA.

Viene visualizzata la finestra omonima da cui scegliamo come “Tipo di regola” la voce “UTILIZZA UNA FORMULA PER DETERMINARE LE CELLE DA FORMATTARE”.

Nella casella vuota sotto l’elenco digitiamo =$A3=”dom”, che individuerà le celle con le domeniche: clicchiamo sul pulsante FORMATO, scegliamo il colore ROSSO e clicchiamo su  OK per chiudere entrambe le finestre.

Bloccare le intestazioni di riga

Con la procedura seguente bloccheremo le intestazioni in modo che rimangano sempre in primo piano, anche quando utilizziamo la barra di scorrimento: selezioniamo la riga successiva a quella delle intestazioni, ovvero la numero tre; posizioniamoci sulla barra multifunzione VISUALIZZA e clicchiamo sul pulsante BLOCCA RIQUADRI.

Automatizzare il sistema di booking

Per colorare automaticamente le celle corrispondenti ad una macchina prenotata in un certo intervallo di tempo sarà sufficiente scrivere qualcosa nelle celle (per esempio il cognome del cliente che ha prenotato o il numero della  carta di credito o entrambe le cose):  Excel marcherà quelle celle con un colore che segnerà l’avvenuta prenotazione.

1. Selezionare tutte le celle vuote della tabella (nel nostro esempio l’intervallo tra C3 ed L368)
2. Richiamare la finestra della FORMATTAZIONE CONDIZIONALE come già visto e scegliere di impostare una nuova regola.
3. Nella finestra omonima scegliere dall’apposito elenco “FORMATTA SOLO LE CELLE CHE CONTENGONO”
4. Aprire il menù “FORMATTA SOLO CELLE CON”, presente sotto l’elenco precedente, e selezionare “VALORE NON VUOTO”

Ora clicchiamo il pulsante FORMATO e,nella finestra FORMATO CELLE, la scheda RIEMPIMENTO. Scegliamo un colore (es.: rosso) e confermiamo con il tasto Ok per rendere effettive le modifiche.

Immaginando ora che il Sig. Rossi debba prenotare la prima auto inserita nella tabella dal 2 al 3 gennaio, scriviamo ROSSI nelle due celle corrispondenti (ovvero C4 e C5) e subito queste verranno marcate di rosso. Se successivamente, il Sig. VERDI richiederà la stessa auto per i giorni indicati in precedenza basterà dare un occhiata alla tabella per rispondere immediatamente al cortese cliente che la cosa non è fattibile poiché la vettura risulta già prenotata.

Filtrare le prenotazioni

Per una ricerca rapida dei dati nella nostra tabella-calendario, utilizziamo i filtri  Excel: facciamo click su un’etichetta di intestazione della nostra tabella (per esempio A3), posizioniamoci nella barra DATI e clicchiamo il pulsante FILTRO. Accanto a ciascuna etichetta comparirà una freccetta: basterà cliccarci sopra per avere la possibilità, attivando o disattivando una serie di voci, di fare delle ricerche più o meno complesse.

Macro

La tabella-calendario appena generata ci consentirà di gestire le prenotazioni nel 2012.

Per il 2013 avremo bisogno di un nuovo prospetto, teoricamente potremo riadattare la stessa tabella rinominando il foglio con il nuovo anno e cancellando le prenotazioni già inserite ma così perderemmo i  dati di archivio delle prenotazioni 2012, per cui la soluzione ideale è inserire un nuovo foglio in cui copiare tutto il contenuto del primo.

Per non ripetere ogni anno le stesse operazioni, generiamo una macro in grado di automatizzarle. Apriamo Visual basic cliccando ALT+F11, e poi sul menu INSERISCI e infine su MODULO. Ricopiamo il seguente codice VBA dopodiché chiudiamo la finestra di Visual Basic:

Sub ins()
Dim var
var = MsgBox(“Verrà inserito un nuovo foglio che dovrai rinominare con l’ANNO!”, vbYesNo)
If var = vbYes Then
Cells.Select
Selection.Copy
Range(“A1:B1”).Select
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Paste
Rows(“3:3”).Select
ActiveWindow.FreezePanes = True
Range(“D1:L1”).Select
Else
Exit Sub
End If
End Sub

Queste istruzioni consentiranno, quando eseguite, di:

• Visualizzare un messaggio di conferma per creare un nuovo calendario in un altro foglio.
• Inserire un nuovo foglio.
• Ricopiare le nuovo foglio la tabella-calendario del primo.

Al termine dovremo rinominare il foglio c(es.: 2013) e per mandare in esecuzione la macro appena creata:

1. Clicchiamo la combinazione ALT+F8 per visualizzare la finestra MACRO
2. Selezioniamo INS (…è il nome che identifica il codice inserito in precedenza)
3. Clicchiamo OPZIONI per far apparire la finestra omonima da cui, dopo CTRL, digitiamo un carattere (es.: i) e confermiamo con OK

Tutte le volte che utilizzeremo la combinazione di tasti CTRL+i verrà eseguito il codice in oggetti: ricordatevi che occorre rinominare i nuovi fogli con l’anno giusto per rendere attiva la tabella calendario.

Se avete inserito altri fogli oltre a “2012”, eliminateli. Posizionatevi nella cella C1 e scrivete la formula per far apparire un messaggio quando, rinominando in maniera errata un foglio, generiamo errori nel calendario:

=SE((SE.ERRORE(B3>=0;))=VERO;”CTRL+i PER INSERIRE UN NUOVO FOGLIO “;”RINOMINA IL FOGLIO CORRETTAMENTE!!!”)

Se  diamo un nome pertinente la formula visualizzerà nella cella C1 un promemoria sulla combinazione di tasti da usare per inserire un nuovo foglio.