Corso di Excel - Livello Avanzato: Guida Completa alle Funzioni e alle Tecniche Avanzate, Schemi e mappe concettuali di Informatica gestionale

Scarica Corso di Excel - Livello Avanzato: Guida Completa alle Funzioni e alle Tecniche Avanzate e più Schemi e mappe concettuali in PDF di Informatica gestionale solo su Docsity!

CORSO DI EXCEL

Livello avanzato

Livia G. Garzanti

Formule

La formula è quell’elemento di Excel che consente di fare operazioni (da semplici a molto complesse) sui dati raccolti nelle celle dei suoi fogli di lavoro. È possibile inserire una formula in una cella semplicemente digitandola al suo interno. Resta inteso che uno sappia il significato teorico della formula (per esempio che l’addizione è l'operazione aritmetica che consiste nel sommare i singoli elementi, di tipo numerico, detti “addendi”). Per inserire una formula prima di tutto va digitato il simbolo = poi a seguire dati e operatori. Il calcolo procede da sinistra verso destra, vengono calcolate prima moltiplicazioni e divisioni, poi addizioni e sottrazioni È possibile utilizzare parentesi tonde (Excel accetta solo le tonde) per definire l’ordine di esecuzione dei calcoli. Esempio: = 350 – (3(45+17)) È possibile utilizzare i riferimenti alle celle contenenti i dati da elaborare. Esempio: =A1(B2+C2) Nella cella verrà visualizzato il risultato del calcolo; la formula resterà visibile nella barra della formula (visualizzata normalmente sotto la barra multifunzione). Esempi molto semplici sono le formule delle quattro operazioni aritmetiche fondamentali: Sottrazione Il suo operatore è rappresentato dal segno – Esempio: = A1–B Addizione Il suo operatore è rappresentato dal segno + Esempio: = A1+B Moltiplicazione Il suo operatore è rappresentato dal segno ***** Esempio: = A1*B Divisione Il suo operatore è rappresentato dal segno / Esempio: = A1/B

• Utilizzando Punto e virgola si identifica l’unione delle celle, cioè un intervallo di celle non contigue (p.e. A1; A4; B2; C9 individua l’insieme delle 4 celle A1, A4, B2, C9) Infine i riferimenti possono essere estesi anche ad altri fogli della stessa cartella di lavoro e persino a diverse cartelle di lavoro. Riferimenti tra fogli Nelle formule è possibile fare riferimento alle celle di altri fogli di lavoro, con la sintassi seguente: = NOMEFOGLIO! RIFERIMENTOCELLA Possono essere utilizzati sia riferimenti assoluti che relativi. Esempi: Foglio1!A1 Foglio1!$A$1 Foglio23!C12 Foglio23!$C$  ESEMPIO. Rendere attivo il Foglio1, scrivere nella cella A1 BIANCHI e nella cella B1 10.000.000, come mostrato nella figura sotto: Rendere attivo il Foglio2, scrivere nella cella A1 ROSSI e nella cella B1 13.000.000. Rendere attivo il Foglio3, scrivere nella cella A1 TOTALE COSTO DIPENDENTI. Nella cella B1 si vuole calcolare il totale del costo annuo dei dipendenti, la formula è la seguente: = FOGLIO1!B1 + FOGLIO2!B Il risultato è riportato sulla figura seguente: NOTA: Un particolare tipo di riferimento tra fogli è il riferimento 3D , cioè un riferimento che rimanda alla stessa cella o allo stesso intervallo di celle in più fogli. Esempio: =SOMMA(Foglio2:Foglio6!A2:A5) per sommare le celle da A2 ad A5 dei fogli di lavoro da 2 a 6. Il riferimento 3D è un sistema pratico ed efficiente per fare riferimento a più fogli di lavoro che seguono lo stesso schema e contengono lo stesso tipo di dati. figura 1 figura 2

Riferimenti tra file Quando c’è bisogno di riferirsi a celle che si trovano in altri file di Excel, la sintassi è molto più complessa di quella vista per i collegamenti tra fogli. Si devono indicare le seguenti informazioni:

• Nome del file di origine
• Nome del foglio di lavoro
• Riferimento alle celle La sintassi è la seguente: =’[NOMEFILE.xls]NOMEFOGLIO'!RIFERIMENTOCELLE Per esempio: =’[Cartel1.xls]Foglio3'!$B$ Dovendo scrivere le formule con questa sintassi, diverrebbero più lunghe e più complesse, si preferisce, quindi, non scrivere a mano tali formule, ma fare quanto segue:

1. Creare il collegamento con le celle del file origine, nelle celle del foglio che dovrà contenere la formula;
2. Scrivere la formula utilizzando i nomi delle celle che contengono i collegamenti. Con questo sistema le formule sono scritte come tutte le altre. I collegamenti servono per “copiare” i valori contenuti nelle celle di altri file, nel foglio di lavoro corrente. Per capire quanto detto conviene provare i collegamenti con un esempio.  ESEMPIO. Aprire un nuovo file. Nel Foglio1 copiare la tabella visualizzata nella figura seguente: Nella cella B10 si deve calcolare il totale utilizzando la SOMMA AUTOMATICA. Salvare il file nella cartella DOCUMENTI con il nome ACQUISTI Aprire un nuovo file, nel Foglio1 copiare la tabella visualizzata nella Figura sottostante e nella cella B7 calcolare il totale utilizzando la SOMMA AUTOMATICA. FIG. 2. Figura 3

Questo è il collegamento che si sarebbe dovuto scrivere a mano, se non fossero stati utilizzati INCOLLA SPECIALE – INCOLLA COLLEGAMENTO. Nel file ACQUISTI, rendere attiva la cella B10, che contiene il totale e fare un click sul pulsante COPIA, nella barra degli strumenti. Nel file UTILE, selezionare la cella B2, aprire il menu MODIFICA e fare un click sul comando INCOLLA SPECIALE. Fare un click sul pulsante INCOLLA COLLEGAMENTO. Il risultato è mostrato nella figura seguente: Nella cella B3 scrivere la seguente formula: = B1 - B Si ottiene l’utile, come mostrato nella figura seguente: Per utilizzare i collegamenti si deve considerare quanto segue:

1. I file di origine, cioè quelli che contengono i valori che interessano, devono essere salvati su disco, altrimenti Excel non può creare i collegamenti
2. Il file di destinazione non deve necessariamente essere salvato su disco
3. È meglio aprire tutti i file coinvolti prima di iniziare i collegamenti. Perché si utilizza il comando INCOLLA COLLEGAMENTO e non semplicemente il comando INCOLLA? Il comando INCOLLA copia il valore contenuto in una cella. Quando la cella di origine è modificata, la cella di destinazione rimane con il valore vecchio, quello che era stato copiato. Il comando INCOLLA COLLEGAMENTO non copia il valore contenuto in una cella, ma fa un collegamento con la cella di origine. Quando è modificata la cella di origine, automaticamente anche nella cella destinazione sono riportati i nuovi valori. figura 8 figura 9

Funzioni

Excel prevede formule predefinite per fare operazioni, da semplici a molto complesse. Queste formule predefinite sono chiamate FUNZIONI. Una funzione è, quindi, una formula predefinita da Excel, che elabora uno o più valori detti argomenti, producendo come risultato uno o più valori. Esempi di funzioni sono ARROTONDA(), MEDIA(), SEN(), COS(). La maggior parte di queste riguarda funzioni matematiche, ciò significa che per utilizzare una funzione si dovrebbe conoscere la base matematica di quella funzione. Per esempio COS() è la funzione di Excel per calcolare il coseno, il programma trova il risultato, dice che COS serve per calcolare il coseno, ma non spiega cos’è il coseno. In Excel, nella guida in linea, in un qualunque manuale di Excel non si trova la spiegazione teorica di una funzione, per questo si deve consultare un testo di matematica, statistica, analisi finanziaria o altra materia cui la funzione appartiene. La difficoltà maggiore per l’uso di molte funzioni non sta in Excel, ma nel suo concetto teorico. La funzione SOMMA() è un esempio di funzione che si utilizza spesso. C’è un pulsante per utilizzare la somma automatica oppure si può scrivere a mano la funzione utilizzando la sintassi: = SOMMA (intervallo di celle) Ogni funzione in Excel ha una sintassi, che in generale è: = NOMEFUNZIONE(arg1; arg2; arg3; …; argn) Ogni funzione è composta da:

• Un Nome (normalmente scritto in maiuscolo ma non ci sono problemi nemmeno scrivendolo in minuscolo)
• La parentesi tonda aperta
• Un eventuale insieme di argomenti separati normalmente dal simbolo ;
• La parentesi tonda chiusa Gli argomenti possono essere uno solo, come nel caso della somma (un intervallo di celle) oppure più di uno. Il numero degli argomenti dipende dal calcolo che compie la funzione, cioè dalla sua definizione. Ogni funzione può essere scritta a mano, purché se ne conosca la sintassi, oppure usata utilizzando il comando per cercare ed inserire le funzioni. Inserisci Funzione Il metodo più semplice per inserire una formula con una funzione è fare click sul pulsante INSERISCI FUNZIONE Figura 10

Figura 15 La finestra di dialogo è divisa in due parti: una relativa alla Categoria e l’altra al Nome della funzione. Nella prima (in alto) si deve scegliere (aprendo un menu a tendina e selezionandone una voce) la categoria di cui fa parte la funzione che si intende utilizzare. Se non si conosce la categoria si utilizza Tutte. Dopo aver scelto la categoria si passa nella sezione in basso dove sono elencate in ordine alfabetico tutte le funzioni della categoria scelta. Ci sono centinaia di funzioni diverse alcune semplici, altre più complesse. Facendo click sul nome di una funzione Excel visualizza sotto le due sezioni una breve spiegazione della funzione. Nella finestra INSERISCI FUNZIONE precedentemente aperta selezionare la categoria Statistiche, cercare la funzione MIN (la funzione che esamina un intervallo di celle e fornisce come risultato il valore più piccolo di tutte le celle), e selezionarla con un click. Fare un click sul pulsante OK. Appare una seconda finestra di dialogo, ARGOMENTI FUNZIONE (figura 16), in cui devono essere inseriti gli argomenti della funzione, chiamati anche parametri. Il numero e il tipo di parametri dipende dalla funzione scelta. Gli argomenti possono essere obbligatori oppure facoltativi. Quelli obbligatori sono indicati in grassetto quelli facoltativi sono indicati con testo normale. Nella parte inferiore della finestra di dialogo c’è la spiegazione degli argomenti e, subito sotto, si trova l’anteprima del risultato della funzione.

Figura 16 Excel suggerisce da solo i parametri della funzione, infatti in num1 si trova scritto A1:D5. Non sempre però il programma propone quelli giusti, come in questo esempio: in questi casi è necessario correggere manualmente. Gli argomenti possono essere digitati all’interno della casella corrispondente, ma esiste anche un metodo più semplice: selezionare i parametri nel foglio di lavoro. Per completare l’esempio verrà utilizzato questo metodo. Fare un click sul pulsante che si trova vicino alla casella dell’argomento. Il pulsante è mostrato con un rettangolo nella figura 16. La finestra di dialogo viene momentaneamente nascosta per permettere la selezione dei parametri. Selezionare le celle relative all’argomento da inserire: A1:D5, come mostrato in figura: Figura 17 Sono stati scritti gli argomenti corretti nella casella di testo Num1. Nel caso ci fossero altri argomenti si dovrebbero ripetere le stesse operazioni, per ogni argomento. Fare un click sul pulsante OK. Nella cella F1 verrà visualizzato il risultato, cioè 0. Nella barra della formula si trova scritto: = MIN (A1:D5)

 ESEMPIO

Funzioni SE annidate. Mentre una funzione SE semplice restituisce solo due risultati (Vero o Falso), quelle annidate possono generare da 3 a 64 risultati. Figura 18 =SE(D2=1;"SÌ";SE(D2=2;"No";"Forse")) Nella figura precedente la formula nella cella E2 dice: SE(D2 è uguale a 1, allora restituisci "Sì", altrimenti SE(D2 è uguale a 2, allora restituisci "No", altrimenti restituisci "Forse")). Notare che alla fine della formula sono presenti due parentesi di chiusura. Queste sono necessarie per completare entrambe le funzioni SE e se si prova a immettere la formula senza entrambe le parentesi di chiusura, Excel tenterà di correggerla automaticamente. Messaggi di errore per le formule e le funzioni Se si verifica un errore nella valutazione di una formula o una funzione, Excel ce lo segnala tramite un opportuno codice. Può essere utile capire i messaggi di errore più frequenti che Excel ci invia dopo aver inserito una funzione: #div/0! nella funzione compare una divisione per 0 #nd manca un valore necessario per il calcolo #nome? il nome della funzione non è corretto #num! tipo di numero non è valido per la funzione #rif! la funzione contiene un riferimento errato #valore! valore non valido, ad esempio un testo invece di un numero

Nomi

Nomi a celle e intervalli I riferimenti relativi e assoluti sono la base per le formule di Excel, ma risulta spesso difficile leggere una formula e capire subito a cosa serve. I nomi, in generale, servono per scrivere le formule in modo chiaro e leggibile. È possibile assegnare un nome ad una sola cella o ad un intervallo di celle. Per assegnare un nome si procede come segue:

1. Selezionare la cella o l’intervallo di celle da nominare;
2. Fare un click sulla casella del nome (a sinistra nella barra della formula);
3. Digitare il nome;
4. Premere INVIO per confermare. Il nome viene assegnato alle celle e può essere utilizzato ogni qual volta si debba riferirsi a quelle celle, sia nello stesso foglio, nello stesso file o in un'altra cartella di lavoro: il nome di una cella è un riferimento assoluto ad essa. Un nome assegnato è unico all’interno della cartella di lavoro. Non si può assegnare lo stesso nome ad una cella o gruppo di celle, nemmeno in fogli diversi dello stesso file. Per sapere quali sono i nomi assegnati in una cartella di lavoro è sufficiente fare un click sulla tendina (freccia nera) vicino alla casella del nome, da qualsiasi posizione. Si apre la lista dei nomi assegnati. Facendo un click su un nome, Excel rende attiva/e la/le cella/e corrispondente/i. Cancellare o modificare i nomi Per cancellare o modificare un nome assegnato si utilizza la finestra di dialogo (figura 20) che si apre con il comando GESTIONE NOMI presente nella scheda FORMULE della barra multifunzione. Figura 19

Convalida dei dati

La convalida dei dati è uno strumento di Excel che consente di limitare il tipo di dati o i valori immessi dagli utenti in una cella. Viene inoltre usata comunemente per creare un elenco a discesa , cioè un menu a tendina da cui selezionare la voce da immettere in una cella anziché digitarla. Aggiungere la convalida dei dati a una cella o a un intervallo

1. Selezionare una o più celle da convalidare.
2. Nel gruppo STRUMENTI DATI della scheda DATI della barra multifunzione fare click su CONVALIDA DATI. Figura 21
3. Nella casella CONSENTI della scheda IMPOSTAZIONI della finestra di dialogo CONVALIDA DATI selezionare uno dei criteri di convalida (p.e. Numero intero ). Figura 22
4. Per specificare come gestire i valori vuoti (nulli), selezionare o deselezionare la casella di controllo IGNORA CELLE VUOTE.
5. Nella scheda MESSAGGIO DI INPUT della finestra di dialogo CONVALIDA DATI digitare il testo che verrà visualizzato quando si attiva la cella (p.e. Immettere un numero intero compreso

tra 1 e 1000 ) attivarne la visualizzazione con la relativa check box e, opzionale, immettere un titolo per il messaggio di input.

6. Nella scheda MESSAGGIO DI ERRORE della finestra di dialogo CONVALIDA DATI, selezionare lo stile della finestra di errore (tra INTERRUZIONE, AVVISO, INFORMAZIONE), digitare il testo che verrà visualizzato in caso il valore inserito non sia valido (p.e. Numero non valido ) e l’eventuale titolo per la finestra che avviserà dell’errore di validità.
7. Al termine di tutte le impostazioni della finestra di dialogo CONVALIDA DATI fare click su OK.
8. Verificare la convalida dei dati per accertarsi che funzioni correttamente. Provare a immettere sia dati validi sia dati non validi nelle celle per verificare il funzionamento delle impostazioni e accertarsi che vengano visualizzati i messaggi previsti. Esempi di convalida dei dati LIMITARE L'IMMISSIONE DI DATI A NUMERI INTERI COMPRESI TRA LIMITI. In questo caso nella fase di scelta del criterio di convalida:
9. Selezionare NUMERO INTERO dall'elenco CONSENTI.
10. Selezionare il tipo di restrizione desiderato nella casella DATI. Per impostare ad esempio i limiti superiore e inferiore, selezionare TRA.
11. Immettere il valore minimo, massimo o lo specifico valore consentito. Figura 23 NOTA: È anche possibile immettere una formula che restituisca un valore numerico. Per impostare ad esempio un limite massimo per commissioni e bonus sul 6% dello stipendio di un venditore nella cella E1, selezionare MINORE O UGUALE A nella casella DATI e immettere la formula =E16%* nella casella MASSIMO. LIMITARE L'IMMISSIONE DI DATI A UNA DATA COMPRESA IN UN INTERVALLO DI DATE. In questo caso nella fase di scelta del criterio di convalida:
12. Selezionare DATA nella casella CONSENTI.
13. Scegliere il tipo di restrizione desiderato nella casella DATI. Per impostare ad esempio limiti superiori e inferiori, scegliere MAGGIORE DI.
14. Immettere la data di inizio, fine o la specifica data consentita.

Creare un elenco a discesa per l’immissione di dati utilizzando la convalida dei dati

1. Creare l’elenco delle voci in un foglio a parte e assegnare all’intervallo di celle dell’elenco un nome significativo. Per esempio, nella figura seguente, l’elenco è contenuto nell’intervallo A2:A8 cui è stato dato il nome Reparti :
2. Selezionare una o più celle da convalidare.
3. Nel gruppo STRUMENTI DATI della scheda DATI fare click su CONVALIDA DATI.
4. Nella casella CONSENTI della scheda IMPOSTAZIONI selezionare ELENCO. Figura 26
5. Nella casella ORIGINE della convalida dei dati, aggiungere il nome dell’elenco definito, preceduto da un segno di uguale (=).

Figura 27

6. Verificare che la casella di controllo ELENCO NELLA CELLA sia selezionata (vedi figura 27, sopra), altrimenti non sarà visibile la freccia accanto alla cella (vedi esempio in figura 28, sotto). Figura 28
7. Per specificare come gestire i valori vuoti (nulli), selezionare o deselezionare la casella di controllo IGNORA CELLE VUOTE. Se i valori consentiti si basano su un intervallo di celle con un nome definito e questo intervallo contiene una cella vuota, selezionando la casella di controllo Ignora celle vuote viene consentita l'immissione di qualunque valore nella cella convalidata. Questo vale anche per celle a cui fanno riferimento formule di convalida: se vi sono celle di riferimento vuote, selezionando la casella di controllo Ignora celle vuote viene consentita l'immissione di qualunque valore nella cella convalidata.
8. Verificare la convalida dei dati per accertarsi che funzioni correttamente. Provare a immettere sia dati validi sia dati non validi nelle celle per verificare il funzionamento delle impostazioni e accertarsi che vengano visualizzati i messaggi previsti. Rimuovere la convalida dei dati. Per togliere la convalida dati applicata a una o più celle:
9. Selezionare le celle che contengono la convalida che si vuole eliminare.