EXCEL 2000 Le Formule e le Funzioni Stampa...

EEXXCCEELL 22000000 LLee FFoorrmmuullee ee llee FFuunnzziioonnii WWOORRDD eedd EEXXCCEELL 22000000

SSttaammppaa UUnniioonnee

Office 2000 – Le Formule e le Funzioni di Excel – La Stampa Unione ________________________________________________________________________________

- 2-

LE FORMULE E LE FUNZIONI

INTRODUZIONE Le formule utilizzano operatori aritmetici per lavorare con valori, testo, funzioni del foglio di lavoro e altre formule per calcolare un valore nella cella. Excel calcola di nuovo le formule se il valore in una cella utilizzato da una formula viene modificato. In sostanza, si possono osservare rapidamente scenari multipli cambiando i dati in un foglio di lavoro e lasciando che le formule calcolino il risultato. Una formula immessa in una cella può essere costituita da uno dei seguenti elementi: • Operatori. • Riferimenti di cella (comprese le celle e gli intervalli denominati). • Valori. • Funzioni del foglio di lavoro. Una formula può essere costituita al massimo da 1024 caratteri. Dopo avere immesso una formula all'interno di una cella, questa visualizza il risultato della formula. La formula stessa compare nella barra della formula quando la cella viene attivata. LA MIA PRIMA FORMULA Le formule sono delle espressioni aritmetiche del tipo somma di due numeri

10+5 Ad essere precisi una formula può anche essere un’espressione di testo. Se la cella A1 contiene il nome Giovanni e B1 Pascoli, possiamo unirne il contenuto con la & (e commerciale) che compone seguente formula:

=A1&” “&B1 ed il relativo risultato:

Giovanni Pascoli Il doppio apice occorre venga aggiunto, con il carattere di SPAZIO all’interno, per offrire il giusto spazio tra i contenuti delle celle A1 e B1 così concatenate. Se al posto della & si indicava l’operatore di somma + , il risultato forniva un errore dato dall’avviso:

#VALORE!


- 3-

Portiamoci nella cella A1 e digitiamo la nostra prima formula:

=10+5 e confermiamo con il tasto Invio. NOTA. E’ fondamentale notare la presenza del segno uguale (=), che serve a segnalare ad

Excel che i caratteri seguenti danno vita ad una formula. Dopo la conferma la cella A1 visualizza il valore 15, mentre nella barra della formula, appare ciò che abbiamo digitato, cioè la formula. Quindi possiamo trarre le seguenti conclusioni:

• quello che appare nella cella è il valore visualizzato (il risultato); • quello che appare nella barra della formula è il valore memorizzato (gli elementi

costituenti la formula).

Gli operatori aritmetici Gli OPERATORI ARITMETICI, dal retaggio delle elementari, detti rispettivamente il segno PIU’, il MENO, il PER e il DIVISO, assumono una forma un po’ diversa dalla tradizionale in quanto ci si deve aiutare con la tastiera; i simboli corretti sono:

PIU’ (Somma)

MENO (Sottrazione)

PER (Prodotto)

DIVISO (Rapporto)

+ - * (Asterisco) / (Barra)

Introduciamo ora la seguente espressione in A1:

=10+5*2 Si otterrà come risultato 20. Seguendo le regole delle precedenze fra gli operatori aritmetici è molto semplice ottenere come risultato il valore 30:

=(10+5)*2


- 4-

OPERATORI UTILIZZATI NELLE FORMULE Excel consente di utilizzare nelle formule una varietà di operatori: + Addizione - Sottrazione * Moltiplicazione / Divisione ^ Elevamento a potenza & Concatenamento (in genere usato per unire celle con contenuti diversi) = Confronto logico (uguale a) > Confronto logico (maggiore di) < Confronto logico (minore di) >= Confronto logico (maggiore o uguale a) <= Confronto logico (minore o uguale a) <> Confronto logico (diverso da). All'interno di ciascuna formula, Excel esegue le operazioni da sinistra verso destra seguendo uno specifico ordine di priorità noto come precedenza degli operatori, che stabilisce la precedenza di alcune operazioni rispetto ad altre: 1. Tutte le operazioni in parentesi. 2. Equazioni e operazioni esponenziali. 3. Moltiplicazioni e divisioni. 4. Addizioni e sottrazioni. Si possono utilizzare le parentesi per modificare l'ordine di precedenza di Excel. È molto importante attenersi a questa regola, perché da essa dipende la correttezza del risultato. FUNZIONI INCORPORATE DI EXCEL Excel fornisce un incredibile numero di funzioni incorporate che possono essere utilizzate nelle formule. Le funzioni possono migliorare notevolmente la potenza delle formule, semplificare le formule e renderle più facili da leggere; in molti casi le funzioni consentono di eseguire calcoli che sarebbero diversamente impossibili. Inoltre, qualora le funzioni disponibili non siano sufficienti a soddisfare le proprie esigenze, Excel consente di creare funzioni personalizzate.


- 5-

IMMISSIONE DELLE FORMULE Una formula deve iniziare con un segno di uguale (=) per indicare ad Excel che la cella contiene un valore diverso dal singolo dato: in tal caso una formula anziché un testo. Esistono due metodi per immettere una formula in una cella: manualmente oppure puntando con il mouse a riferimenti di cella. L'immissione manuale di una formula implica l'immissione diretta della formula alla tastiera; si deve digitare un segno uguale (=) seguito dalla formula. Durante la digitazione, i caratteri compaiono nella cella e nella barra della formula. Si possono utilizzare tutti i normali tasti di editing durante l'immissione di una formula. L'altro metodo di immissione di una formula comporta una parziale digitazione manuale, ma consente di puntare ai riferimenti di cella anziché immetterli manualmente. Ad esempio, per immettere la formula =Al +A2 nella cella A3, si deve operare come segue: 1. Spostare il puntatore nella cella A3. 2. Digitare un segno di uguale (=) per iniziare la formula. Si noti che Excel sostituisce

alla Casella Nome la lista delle Funzioni e rende disponibili i tasti di Invio nella barra di stato (In basso a sx, in luogo di Pronto).

3. Premere due volte la freccia verso l'alto. Quando si preme questo tasto, Excel

visualizza un bordo attorno alla cella e il riferimento di cella compare nella cella A3 e nella barra della formula. Si noti inoltre che Excel visualizza Puntamento nella barra di stato.

4. Digitare un segno più (+). Il bordo scompare e nella barra della formula compare di

nuovo Invio. 5. Premere la freccia verso l'alto. La cella A2 viene aggiunta alla formula. 6. Premere Invio per concludere la formula. Puntare agli indirizzi di cella anziché immetterli manualmente è un metodo più preciso e meno noioso. Excel include la cosiddetta Finestra della formula che può essere utilizzata per l'immissione o la modifica delle formule. Per accedere alla finestra della formula, fare


- 6-

clic sul pulsante Modifica formula nella riga di edit (il pulsante è contrassegnato dal segno di uguale). La Finestra della formula consente di immettere formule manualmente o utilizzare le tecniche di puntamento precedentemente descritte. Il vantaggio è costituito dal fatto che la finestra della formula visualizza il risultato della formula durante la sua immissione. USO DEI RIFERIMENTI Nell’uso reale di Excel le formule non contengono quasi mai i valori, ma i riferimenti ai valori. Chiariamo questo concetto, inserendo in una cella, ad es. A1, il solo risultato dell’operazione 10+5*2=30; successivamente introduciamo nella cella C1 il valore 10, in C2 il valore 5, infine in C3 il valore 2. Selezioniamo la cella A3 (la scelta delle celle è puramente casuale) e digitiamo la formula:

=C1+C2*C3

In A3 otterremo come risultato 20, mentre se indichiamo tra parentesi la somma tra C1 e C2, otterremo il nuovo valore 30, proprio come nella formula in A1, dove avevamo invece introdotto direttamente i valori calcolati manualmente.

Ma allora dove sta la differenza fra i due metodi? Sostituiamo ora il contenuto di C3 con il numero 4 e il risultato in A3 varierà di conseguenza, mentre in A1 non subirà variazioni. L’uso dei riferimenti di cella si rivelerà tanto più utile, quanto più saranno complesse le formule utilizzate. TIPOLOGIE DI RIFERIMENTI In base all'operazione che si desidera eseguire in Excel, è possibile scegliere fra i riferimenti relativi, misti o assoluti.


- 7-

Riferimenti relativi Quando si crea una formula, i riferimenti alle celle si basano di solito sulla posizione di questi rispetto alla cella contenente la formula. Se ad esempio la cella A2 contiene la formula =B1 il valore verrà rintracciato nella cella posta immediatamente al di sopra e a destra della cella A2. Questo tipo di riferimento è denominato riferimento relativo. Nell’esempio illustrato a lato la cella B1 contiene il valore 10, pertanto la cela A2 contenente la formula =B1 riporta il valore 10.

Quando si trascina o copia una formula che utilizza riferimenti relativi, i riferimenti della formula trascinata o incollata verranno modificati automaticamente per adeguarsi alla nuova posizione della formula. Nell'esempio seguente la formula contenuta nella cella A2, ovvero =B1 che si trova immediatamente al di sopra e a destra di A2, è stata trascinata o copiata nella cella A3. La formula contenuta nella cella A3 è divenuta =B2, riferendosi infatti alla cella posta immediatamente al di sopra e a destra della cella A3.

In questo caso la cella A3 riporterà il valore contenuto nella cella B2, cioè 15.

Trascinando la formula contenuta nella cella A3 ovvero =B2, in B3 si trasformerà in =C2. In questo caso abbiamo effettuato uno “spostamento” laterale e quindi la formula si è aggiornata in base alla colonna e non alla riga come negli esempi precedenti.

Riferimenti assoluti Se si desidera che i riferimenti non vengano adeguati durante il trascinamento o la copia di una formula in una cella differente, occorre utilizzare un riferimento assoluto.


- 8-

Prendiamo ad esempio il contenuto della cella B1, la formula =A3*C3.

Operando il trascinamento dalla cella B1 alla cella B2 ci troveremo come risultato un deludente 0, ovvero la formula =A4*C4. Risulterà evidente che il modificarsi del riferimento da A3 ad A4 rappresenta un errore. Per bloccare il riferimento ad A3 occorre trasformare la formula della cella B1 in =$A$3*C3. Anteponendo il simbolo del dollaro ($) alle parti del riferimento che s’intende lasciare inalterate si ottiene il riferimento assoluto desiderato.

Riferimenti misti Un riferimento assoluto del tipo =$A$3 impedisce l’adeguamento dei riferimenti sia che si operi un trascinamento verticale che orizzontale. Ma in talune circostanze occorre bloccare verticalmente, ma non orizzontalmente (o viceversa) i riferimenti, cioè occorre utilizzare riferimenti di tipo misto. Nell’immagine sottostante la formula utilizza due riferimenti misti. Con =A$3 si lasciano liberi di adeguarsi gli spostamenti orizzontali, cioè il cambiamento di colonna e si bloccano gli spostamenti verticali, cioè i cambi di riga. Il secondo riferimento $C5, sempre di tipo misto, opera esattamente al contrario.


- 9-

RIFERIMENTI DI CELLE ALL'ESTERNO DEL FOGLIO DI LAVORO Le formule possono fare riferimento a celle contenute in altri fogli di lavoro (e in altre cartelle di lavoro). Excel utilizza uno speciale tipo di notazione per gestire questi tipi di riferimento: NomeFoglio!IndirizzoCella. In altre parole, l'indirizzo della cella deve essere preceduto dal nome del foglio di lavoro, seguito da un punto esclamativo. Per fare riferimento a una cella contenuta in una cartella di lavoro differente, si utilizza questo formato: =[NomeCartella]NomeFoglio!IndirizzoCella In questo caso, l'indirizzo della cella è preceduto dal nome della cartella di lavoro (tra parentesi quadre), dal nome del foglio di lavoro e da un punto esclamativo Quando una formula fa riferimento a celle contenute in un'altra cartella di lavoro, quest'ultima non deve necessariamente essere aperta. INSERIMENTO DI NOMI Se la formula utilizza celle o intervalli denominati è possibile digitare il nome in sostituzione dell'indirizzo o scegliere il nome da una lista e fare in modo che Excel lo inserisca automaticamente. Esistono due metodi per inserire un nome in una formula: • Selezionare il comando Inserisci */* Nome */* Incolla. Excel visualizza la finestra di

dialogo Incolla nomi con un elenco di tutti nomi. Selezionare il nome e fare clic su OK. Oppure, fare doppio clic sul nome per inserire il nome e chiudere la finestra di dialogo contemporaneamente.

• Premere F3. Anche questo tasto visualizza la finestra di dialogo Incolla nomi.


- 10-

ESEMPI SU COME USARE I NOMI E’ possibile utilizzare un nome per rappresentare una formula od un valore costante.

• Posizionarsi sulla cella alla quale vogliamo attribuire un nome.

• Cliccare sulla Casella del nome. Il riferimento della cella rimarrà evidenziato

• Digitare il nome desiderato, sostituendo così la nuova parola (nome) alle coordinate della cella selezionata e confermare con INVIO.

Nell’esempio illustrato la cella C2 è nominata Never.

Digitiamo il valore 10 nella cella Never (C2). Ripetiamo le operazioni precedentemente descritte per attribuire alla cella B4 il nome di Dilan ed inserirvi il valore 5. Nella cella A1 effettuiamo la somma dei valori 10 e 5, inseriti nelle celle C2 e B4 rispettivamente, utilizzando come riferimenti i nomi Never e Dilan.

=Never+Dilan

Nota: Fare riferimento ad un nome equivale all’utilizzo di un riferimento assoluto.


- 11-

LE MATRICI Le Matrici sono strumenti di calcolo per operare su gruppi di valori invece che su valori singoli. Il metodo più semplice per capire il funzionamento delle Matrici è quello di analizzare un semplice esempio. Mediante le Matrici si può calcolare il prodotto dei valori delle righe 1 e 2 per ogni colonna immettendo una sola formula.

Ecco le indicazioni da seguire: • Selezionare l’intervallo A3:E3. • Digitare =A1:E1*A2:E2. • Premere CTRL+SHIFT+INVIO. (In modo che il calcolo abbia effetto, sulla riga 3,

dalla colonna A alla E) Come si può vedere una sola formula matrice calcola il prodotto di ciascun gruppo di valori.

Regole delle matrici Premendo CTRL+SHIFT+INVIO, per confermare una formula matrice, Excel la chiude nella Barra della formula, fra parentesi graffe, per indicare appunto che si tratta di una formula matrice. Non si devono digitare le parentesi graffe, altrimenti Excel considera i dati inseriti come etichette.


- 12-

Non è possibile modificare o cancellare o spostare singole celle dei risultati di una matrice. Le celle di un intervallo matrice vanno considerate come una singola unità e pertanto da modificare tutte assieme. In caso di cancellazione di una singola cella, Excel ricorda che non è un’operazione consentita (in caso di errori o modifiche, conviene pertanto riscrivere la relazione che analizza la matrice). QUANDO UNA FORMULA RESTITUISCE UN ERRORE A volte, quando si immette una formula, Excel visualizza un valore che inizia con un simbolo di cancelletto (#). Questo sta a indicare che la formula restituisce un valore errato. Si deve quindi correggere la formula (o correggere una cella alla quale la formula fa riferimento) per eliminare la visualizzazione dell'errore. Le formule possono restituire un valore di errore se una cella alla quale fanno riferimento contiene un errore. Un singolo valore di errore può propagarsi in numerose altre celle che contengono formule che dipendono dalla cella errata (per sintassi o altro). I valori di errore di Excel sono i seguenti: #DIV/0! La formula sta cercando di eseguire una divisione per zero o per una cella

vuota. #NOME? La formula utilizza un nome che Excel non riconosce. #N/D La formula fa riferimento (direttamente o indirettamente) a una cella che

utilizza la funzione ND per segnalare che non sono disponibili dati. #NULL! La formula utilizza un'intersezione di due intervalli che non sono intersecanti. #NUM! Esiste un problema con un valore; ad esempio, si è specificato un numero

negativo dove avrebbe dovuto trovarsi un numero positivo. #RIF! La formula fa riferimento a una cella non valida.


- 13-

#VALORE! La formula include un argomento od operando di tipo sbagliato. Un operando è un valore o riferimento di cella utilizzato da una formula per calcolare un risultato.

MODIFICA DELLE FORMULE Si possono modificare le formule contenute nel foglio di lavoro nello stesso modo in cui si modifica qualsiasi altra cella. Per modificare una formula attivare la modalità Modifica o fare clic sul pulsante Modifica Formula nella riga di edit per accedere alla finestra della formula. MODIFICA DEL CRITERIO DI RICALCOLO DELLE FORMULE Se si cambia una cella utilizzata da una formula, la formula visualizza immediatamente un nuovo risultato. Questo è quanto accade quando la modalità di calcolo è impostata in Automatico. A volte può essere preferibile controllare il momento nel quale le formule devono essere calcolate. Ad esempio, se si crea un foglio di lavoro con migliaia di formule complesse, le operazioni possono rallentare notevolmente a ogni piccolo passo perché Excel deve eseguire i calcoli. In questo caso, è preferibile impostare la modalità di calcolo manuale di Excel. Questa variazione può essere eseguita nel pannello Calcolo della finestra di dialogo Opzioni dal menù Strumenti. Per selezionare la modalità di calcolo, fare clic sul pulsante di opzione Manuale. Quando si lavora in modalità di calcolo Manuale, Excel visualizza Calcola nella barra di stato quando si ha una formula non calcolata. Per ricalcolare le formule si può utilizzare il tasto F9, che calcola le formule in tutte le cartelle di lavoro aperte, o la combinazione di tasti rapidi Maiusc+F9, che calcola solo le formule nel foglio di lavoro attivo.


- 14-

GESTIONE DEI RIFERIMENTI CIRCOLARI Quando si immettono le formule può accadere che venga visualizzato un messaggio che segnala il fatto che la formula appena immessa definisce un riferimento circolare. Questo si verifica quando una formula fa riferimento al proprio valore, direttamente o indirettamente. In questo caso, quando la formula viene ricalcolata deve essere effettivamente calcolata di nuovo perché è stata modificata. Il calcolo viene ripetuto indefinitamente. Quando si ottiene il messaggio di riferimento circolare dopo aver immesso una formula, Excel fornisce due possibilità: • Fare clic su OK per tentare di individuare il riferimento circolare • Fare clic su Annulla per immettere la formula così com'è. Normalmente, è preferibile correggere i riferimenti circolari; quindi si deve scegliere OK. Facendo ciò Excel visualizza la propria barra degli strumenti Riferimento Circolare, nella quale è possibile fare clic sulle celle all'interno della casella combinata Con Esplora riferimento circolare Excel cerca di individuare l'origine del riferimento circolare. Se si ignora il messaggio di riferimento circolare (facendo clic su Annulla), Excel consente di immettere la formula e visualizza un messaggio nella barra di stato per ricordare che esiste un riferimento circolare.


- 15-

LE FUNZIONI DEL FOGLIO DI LAVORO Le funzioni sono strumenti incorporati che vengono utilizzati nelle formule per eseguire una serie di operazioni. Le funzioni consentono di semplificare le proprie formule e creare formule per eseguire calcoli che diversamente sarebbero impossibili o ripetitivi. ARGOMENTI DI FUNZIONE Tutte le funzioni utilizzano una coppia di parentesi. L'informazione contenuta nelle parentesi consente di comunicare l'indirizzo delle celle che contengono i valori con i quali la funzione deve operare ed è detta argomento. Gli argomenti delle funzioni possono essere costituiti da: • Riferimenti di cella o di intervallo: quando Excel calcola la formula utilizza il

contenuto corrente della cella o intervallo per eseguire i propri calcoli. • Argomenti letterali: sono costituiti da valori o stringhe di testo che si immettono

direttamente in una funzione • Espressioni: si pensi a un'espressione come a una formula all'interno di un'altra

formula. Quando Excel incontra un'espressione come argomento di una funzione, prima calcola l'espressione quindi utilizza il risultato come valore dell'argomento.

• Altre funzioni: espressioni che includono altre funzioni. Il sistema di funzioni

all'interno di altre funzioni nelle formule è noto anche come funzioni nidificate. Excel calcola per prima l'espressione più interna e prosegue verso l'esterno.

IMMISSIONE DI UNA FUNZIONE Se si possiede una certa familiarità con la funzione (si conosce il numero e il tipo di argomenti) si possono digitare la funzione e i relativi argomenti all'interno della formula. Spesso questo metodo è il più efficace. La Finestra della formula permette di immettere una funzione e i relativi argomenti in modo semiautomatico. L'uso della finestra della formula garantisce che la funzione immessa sia corretta e abbia il numero appropriato di argomenti disposti nel giusto


- 16-

ordine (rispettando quindi le regole aritmetiche basilari, come la nidificazione delle parentesi). Per inserire una funzione, il primo passo è quello di selezionare la funzione nella finestra di dialogo Incolla funzione, alla quale si può accedere: • Selezionando nel menu il comando Inserisci */* Funzione. • Facendo clic sul pulsante Incolla funzione nella barra degli strumenti Standard. • Premendo Maiusc+F3. La finestra di dialogo mostra le undici categorie di funzioni nella casella di riepilogo Categoria (può visualizzare ulteriori categorie di funzioni se sono state aggiunte funzioni esterne o personalizzate). Quando si seleziona una categoria, la casella di riepilogo Nome funzione visualizza le funzioni contenute nella categoria selezionata. La categoria Usate più di recente elenca le funzioni usate recentemente. La categoria Tutte elenca tutte le funzioni disponibili appartenenti a tutte le categorie. Si può utilizzare questa impostazione se è noto il nome di una funzione ma non si sa a quale categoria appartiene. Individuata la funzione che si vuole utilizzare, fare clic Su OK. Compare la finestra della formula di Excel. Utilizzare questo strumento per specificare gli argomenti della funzione. Nella figura seguente, la scelta e la sintassi della funzione Logica SE (IF in inglese).


- 17-

Figura:Selezione e scelta di funzioni


- 18-

Le categorie di funzioni elencate nella finestra di dialogo Incolla funzione sono le seguenti: Figura1:Categorie di funzioni

• Funzioni matematiche

e trigonometriche: Excel fornisce più di cinquanta esempi di funzioni, più che sufficienti per eseguire consistenti operazioni di elaborazione numerica.

• Funzioni di testo: pur essendo noto per le sue capacità numeriche, Excel contiene più di venti funzioni incorporate destinate a manipolare il testo.

• Funzioni logiche: questa categoria contiene soltanto sei funzioni (anche se potrebbero esserne inserite altre).

• Funzioni informative: queste quindici funzioni restituiscono informazioni

sullo stato delle celle. Molte di queste funzioni restituiscono valori logici Vero o Falso.

• Funzioni di data e ora: Excel fornisce quattordici tipi di funzioni che consentono di lavorare con date e ore.

• Funzioni finanziarie: questa categoria include 15 funzioni destinate all'esecuzione di calcoli che implicano valori monetari.

• Funzioni di ricerca e riferimento: queste quindici funzioni sono utilizzate per eseguire ricerche all'interno di tabelle e ottenere altri tipi di informazioni.

• Funzioni degli Strumenti di analisi: quando si comincia ad avere una certa familiarità con le formule dei fogli di lavoro di Excel, si possono prendere in esame le funzioni che sono disponibili quando si carica l'aggiunta Strumenti di analisi. Questa fornisce decine di ulteriori funzioni di foglio di lavoro appartenenti alle seguenti categorie: funzioni tecniche e ingegneristiche, funzioni finanziarie,

funzioni di date e ore, funzioni matematiche e trigonometriche e funzioni di informazione.

• Funzioni statistiche: contiene settantadue funzioni che eseguono vari calcoli. Molte sono altamente specializzate ma altre sono utili anche per

attività non statistiche • Funzioni di database: consta di

dodici funzioni che vengono utilizzate nell'attività con le tabelle di database memorizzate in un foglio lavoro.


- 19-

INSERIMENTO DI UNA FUNZIONE: ALCUNI ESEMPI In questa sezione verranno presentati esempi che illustrano come inserire una funzione in una formula. SINTASSI DELLE FUNZIONI Prima di passare alla descrizione di altre funzioni illustriamo brevemente la sintassi generale delle funzioni.

=NomeFunzione(Argomenti) Che è composta dall’uguale (=), come nelle formule, poi dal nome della funzione, ad esempio Media, e dall’apertura e chiusura delle parentesi racchiudenti dagli Argomenti, variabili a seconda della funzione prescelta.

FUNZIONE SOMMA E FUNZIONI STATISTICHE Le funzioni sono un insieme di formule preconfezionate, che rendono più veloci operazioni ripetitive, come ad esempio sommare il contenuto di più celle, o più facili operazioni altrimenti complesse, del tipo il calcolo di un piano di ammortamento.

Somma Automatica

Volendo sommare i valori raffigurati a sinistra, utilizzando una formula occorrerebbe digitare una noiosa serie di riferimenti:

=A1+A2+A3+A4 Naturalmente esiste la possibilità di evitare una simile trafila ricorrendo alla Somma Automatica.

Pertanto, dopo aver correttamente inserito i valori nelle celle desiderate, occorre posizionarsi nella cella dove si vuole ottenere il risultato, in genere sottostante la serie di valori…


- 20-

Funzione Somma Automatica La Somma Automatica è sicuramente uno strumento semplice ed efficace, ma non sempre è possibile o conveniente da utilizzare. Inoltre affrontando lo studio della funzione Somma si prenderà confidenza con la sintassi delle funzioni.

Volendo sommare nella cella A6 le prime tre celle della colonna A digiteremo la seguente funzione.

=SOMMA(A1:A3) che fornisce la somma di tutti i numeri compresi nell’intervallo di celle A1:A3. E’ importante notare i due punti ( : ) che uniscono i due riferimenti A1 e A3; potremmo illustrare tale sintassi come: “dalla cella A1 alla cella A3, quindi comprendendo anche la cella A2”. Volendo sommare i valori delle colonne A e C, ma non quelli compresi nella colonna B, digiteremo la seguente funzione:

=SOMMA(A1:A4;C1:C4) In questo caso è importante notare il punto e virgola ( ; ) che ha una funzione di staccare i due gruppi di riferimenti. Si può dire che la funzione sopra esposta somma tutti i numeri compresi nell’intervallo A1:A4 con tutti i numeri compresi nell’intervallo C1:C4, di seguito, ma senza prendere in considerazione i valori presenti nelle celle della colonna B (da B1 a B4).

…e quindi cliccare sulla lettera greca Sigma

Infine confermare cliccando sul segno di spunta verde.


- 21-

Infine sommiamo tutti i numeri presenti nelle celle delle colonne A, B e C: =SOMMA(A1:C4)

Funzioni Max, Min e Media Max, Min e Media appartengono alla categoria delle funzioni statistiche. Il loro utilizzo è pressoché identico a quello della funzione somma.

UNA FUNZIONE PASSO PASSO: LA FUNZIONE MEDIA La formula utilizza la funzione MEDIA per calcolare la media di un intervallo di celle.

Figura 2: Funzione Media 1. Aprire una nuova

cartella di lavoro e immettere dei valori nell'intervallo H1:H6.

2. Attivare la cella H7.

Questa cella conterrà la formula.

3. Fare clic sul pulsante

Incolla funzione nella barra degli strumenti Standard. Excel visualizza la finestra di dialogo Incolla funzione.


- 22-

4. Poiché la funzione MEDIA fa parte della categoria di funzioni statistiche, fare clic su Statistiche nella casella di riepilogo delle categorie di funzioni. La casella dei nomi di funzioni visualizza le funzioni statistiche.

5. Fare clic su MEDIA nella casella dei nomi di funzione. La finestra di dialogo mostra

la funzione e l'elenco dei suoi argomenti e visualizza anche una breve descrizione della funzione.

6. Fare clic sul pulsante OK. Excel chiude la finestra di dialogo Incolla funzione e

visualizza la finestra della formula. Questo strumento richiede l'immissione degli argomenti della funzione.

7. Attivare la casella di intervallo etichettata Num1. 8. Selezionare l'intervallo H1:H6 nel foglio di lavoro. Questo intervallo compare nella

casella di intervallo e la finestra della formula mostra il risultato. 9. Poiché si sta cercando la media di un singolo intervallo, non sussiste la necessità di

immettere ulteriori argomenti. Fare clic sul pulsante OK. La cella H7 contiene ora la seguente formula, che restituisce la media dei valori contenuti in H1:H6: =MEDIA(H1:H6) LE FUNZIONI LOGICHE

Funzione SE Con la funzione SE operiamo delle scelte. Banalmente: se il mio conto in banca supera il miliardo sono ricco, altrimenti sono povero (si fa per dire).

Nella precedente considerazione abbiamo eseguito un TEST CONDIZIONALE, cioè abbiamo messo a confronto due elementi. Il risultato di un test condizionale è un valore logico VERO (1) oppure FALSO (0). Se il nostro conto in banca supera il miliardo il risultato è VERO, altrimenti il risultato è FALSO.

Ogni test condizionale deve includere almeno un OPERATORE di CONFRONTO


- 23-

Descrizione OPERATORI di CONFRONTO MAGGIORE > DIVERSO <>

MINORE < MAGGIORE O UGUALE >=

UGUALE =

MINORE O UGUALE <=

Prendiamo ad esempio la cella A1 che contiene l’importo dovuto da un ns. creditore. Se tale importo è superiore a € 20.000 vogliamo che nella cella A2 compaia la dicitura “Procedi”, altrimenti con l’importo uguale o inferiore a € 20.000, si dovrà visualizzare “Lascia” (sempre nella cella A2).

Per risolvere il quesito utilizzeremo la funzione LOGICA SE, la cui sintassi è la seguente:

=SE(Condizione;Vero;Falso)

Che tradotta nel nostro esempio prende questa forma:

=SE(A1>20000;”Procedi”;”Lascia”)

Analizziamo i singoli componenti della funzione sopra esposta.


- 24-

= Come tutte le funzioni e formule è assolutamente indispensabile iniziare con il segno uguale

SE Nome della funzione

( Dopo il nome di una funzione troviamo sempre l’apertura di parentesi, al quale interno troviamo gli Argomenti di una funzione.

A1>20000 Questo è il primo argomento della funzione, la CONDIZIONE. A1 è la cella che contiene il valore che stiamo testando e diciamo: se il contenuto di A1 è maggiore ( >) di 20000, passa al secondo argomento della funzione, cioè VERO, altrimenti se uguale o inferiore transita al terzo argomento, cioè FALSO

“Procedi” Questo è il secondo argomento della funzione, la risposta positiva alla CONDIZIONE, cioè VERO. Quindi se l’importo contenuto nella cella A1 è maggiore di 20000 in A2 sarà scritto Procedere. Attenzione alle virgolette, sono indispensabili quando all’interno di una funzione si scrive del testo (formato carattere o generale).

“Lascia” Questo è il terzo argomento della funzione, la risposta negativa alla CONDIZIONE, cioè FALSO. Quindi se l’importo contenuto nella cella A1 è uguale o inferiore di 20000 in A2 sarà scritto Abbandonare

) Naturalmente occorre sempre chiudere una parentesi.

Anche se nell’esempio gli argomenti VERO e FALSO, restituiscono del testo possiamo all’occorrenza usare numeri o espressioni matematiche, così ad esempio:

=SE(A1>20000;A1+A1*10%;”Lascia”)

In questo caso se la risposta è VERO in A2 sarà aggiunto al valore contenuto in A1 il suo 10%; se tale importo fosse 30000, il risultato sarebbe 33000, altrimenti verrebbe restituito Lascia.


- 25-

Funzioni E, O, NON Nell’utilizzo corrente di Excel, le funzioni logiche E ed O sono quasi sempre abbinate alla funzione SE.

Si può dire che sono funzioni ausiliarie della funzione SE. Non per questo va sottovaluto il loro potenziale che è veramente notevole.

1. La funzione logica E ha la seguente sintassi:

=E(Condizione1;Condizione2)

Possiamo rappresentarla anche con questa forma:

=E(Vero;Vero) che darà come risultato VERO

=E(Vero;Falso) che darà come risultato FALSO

=E(Falso;Falso) che darà come risultato FALSO

Adesso proviamo a illustrare un esempio un po’ più concreto e chiarificatore:

Controlliamo i valori contenuti nelle celle A1 e A2 e se entrambi sono superiori rispettivamente a 5 e 10 avremo come risultato VERO

Ma se uno dei numeri contenuti nelle celle risulta più basso o uguale (come si può vedere nell’immagine a lato) al valore con cui si confronta, il risultato diventa FALSO

Nella trattazione della funzione E abbiamo impostato due condizioni, questo non vuol dire che non possiamo aumentarle, come si può vedere nell’esempio seguente.


- 26-

Si noti che in questo esempio sono ben tre le celle oggetto di test e che il risultato è VERO poiché tutte e tre le condizioni danno un esito positivo, cioè tutti i numeri contenuti nelle tre celle superano i valori con cui sono confrontati:

6 (A1) è maggiore di 5, 12 (A2) è maggiore di 10, infine 15 (A3) è maggiore di 12

Come negli esempi precedenti sarebbe bastato che anche un solo valore fosse risultato uguale od inferiore al numero con cui si confrontava per dare come risultato finale FALSO.

2. La funzione logica O ha la seguente sintassi:

=O(Condizione1;Condizione2)

Possiamo renderla anche con questa forma:

=O(Vero;Vero) che darà come risultato VERO

=O(Vero;Falso) che darà come risultato VERO

=O(Falso;Falso) che darà come risultato FALSO

Anche per la funzione O proviamo a illustrare un esempio un po’ più concreto.

Controlliamo i valori contenuti nelle celle A1 e A2, e vedremo che a differenza della funzione E, ne basta uno solo superiore rispetto ai valori di confronto per ottenere il risultato VERO

Pertanto con la funzione O solamente se entrambe le condizioni danno esito negativo, cioè sono false, si avrà come responso FALSO.

3. Possiamo citare una quarta funzione logica, NON, che non può “sopravvivere” da sola e che è sempre usata con SE.

La funzione NON nega una condizione, osservate l’esempio sottostante.


- 27-

In questo caso chiediamo alla nostra funzione di restituire il valore di testo SCARSO se il valore della cella A1 NON è 6

CERCA VERTICALE E ORIZZONTALE (CERCA.VERT)

Estrapolare e localizzare dei dati, possono diventare operazioni alquanto complesse, anche utilizzando Excel. In questi paragrafi si cercherà di prendere confidenza con tre funzioni fondamentali nella localizzazione dei dati: CERCA.VERT.

Per rendere più semplice la comprensione di queste funzioni si proceda subito nel costruire la "palestra" per fare pratica: quattro dati “in croce”.

Digitare i dati relativi alla tabella qui sotto riportata.

SINTASSI

CERCA.VERT(valore; matrice_tabella; indice; intervallo)

DATI RICERCA ESEMPIO

valore = Milano matrice_tabella = A3:E7 (Cioè analizziamo i dati nella matrice da A3 ad E7) indice = quarta colonna intervallo argomento facoltativo che può assumere il valore di VERO o FALSO.


- 28-

SINTASSI IN BASE AI DATI DI ESEMPIO

CERCA.VERT("Milano";A3:E7;4)

Quindi, semplificando ulteriormente, la funzione recita così:

Analizzare la matrice dalla prima colonna della tabella A3 fino ad E7, posizionarsi nella cella che contiene "Milano", quindi rimanendo sulla stessa riga così individuata, spostarsi alla quarta colonna ove, occorre restituire come risultato il valore contenuto in quella cella, che

corrisponde al numero 650.

DA RICORDARE

La funzione CERCA.VERT effettua la ricerca del primo argomento sempre nella prima colonna della tabella.

Se non viene impostato l’argomento opzionale intervallo, viene assunto per default il valore VERO. Con VERO la funzione ricerca nella prima colonna, iniziando dalla prima cella e procedendo verso il basso, sino a localizzare un valore maggiore di quello specificato.

Apparirà abbastanza chiaro che gli elementi della prima colonna devono essere ordinati in modo crescente. Se l’argomento opzionale intervallo, viene impostato su FALSO non sarà obbligatorio ordinare la prima colonna poiché sarà ricercata solo una ricorrenza esatta.

FUNZIONE FINANZIARIA: RATA

La funzione finanziaria RATA, calcola il pagamento per un prestito sulla base di pagamenti e di un tasso di interesse costanti ed assume la seguente sintassi:

=RATA(tasso_int;periodi;capitale) La sintassi sopra esposta, in base all’esempio illustrato nell’immagine sottostante, assume la seguente forma:


- 29-

=RATA(A2/12;A1*12;A3) Funzione Rata

La lettura della funzione è molto semplice: A2/12 Tasso diviso 12 per portarlo su base mensile; A1*12 Durata prestito per 12 per trasformarlo in mesi; A3 Importo oggetto del prestito. La cifra ottenuto in A5 rappresenta l’importo della rata da versare mensilmente per estinguere il prestito di 100 milioni. Volendo effettuare il calcolo su base annua non occorre, evidentemente, nè dividere, nè moltiplicare per 12. E’ inoltre chiaro che si possono inserire il numero dei mesi in una cella e considerare tale cella come divisore e II fattore, rispettivamente, della relazione scritta in precedenza.


- 30-

STAMPA UNIONE

INTRODUZIONE

Inviare una lettera a tutti i nominativi di un elenco, ad esempio di Comuni o associazioni o di clienti, è uno dei compiti più faticosi cui un ufficio deve far fronte.

Con Word il problema è risolvibile utilizzando la "Stampa unione". La stampa unione non è una procedura da affrontare alla leggera. Se si ha la necessità di inviare la stessa lettera ad un numero ristretto di persone, poco meno di una decina, probabilmente converrà creare un testo riutilizzabile e modificare ogni lettera singolarmente.

Con un elenco di trenta e oltre indirizzi e magari dovendo riutilizzare tale elenco per altre lettere, la stampa unione diventa una buona scelta.

La procedura di unione comporta la fusione di due documenti, ovvero di un documento principale (a volte comunemente detto “scheletro”) e di un file di dati (preferibilmente preesistente).

Nel documento principale risiede il testo della lettera o comunicazione, che rimarrà invariato per tutti i nominativi a cui sarà inviato, mentre nel file dati sono contenute tutte le informazioni variabili, quali, ad esempio, i cognomi, i nomi e gli indirizzi dei destinatari della lettera tipo.

Nel documento principale saranno inserite istruzioni particolari, definite "Campi unione", grazie alle quali verranno stampate le informazioni variabili prelevate dal file dati.

Si inizierà con il creare il documento principale.

PRATICAMENTE

1. Selezionare la voce "Stampa unione" dal menu "Strumenti". 2. Cliccare sul pulsante CREA e sceglie "Lettera tipo".


- 31-

Sarà riproposta la finestra di dialogo "Prepara stampa unione", con il pulsante "Dati" attivo, che quando selezionato mostrerà un menu adibito alla scelta dell’origine dati.

Ma qual è la fonte di dati più idonea per la stampa unione? Naturalmente dipende dalla situazione.

• Per un elenco di nomi ed indirizzi breve, che non subirà cambiamenti rilevanti, andrà benissimo un elenco dati gestito in una tabella di Word.

• Per un elenco di medie - grandi dimensioni, in cui si prevede di dover modificare, aggiungere o eliminare voci, impiegare Excel.

• Per poter disporre di funzionalità di ordinamento e ricerca avanzate, servirsi ancora di Excel o Access.

• Per un elenco con funzionalità avanzate di database relazionale, o per un elenco da condividere con altri utenti, utilizzare Access

Si può comunque affermare, che fra i tre applicativi citati, quello che per facilità di uso e per flessibilità meglio si addice alla gestione della origine dati sia Excel.

• Cliccare sul pulsante "Dati" che presenta un menu composto da quattro voci: Crea origine dati, Apri origine dati, Utilizza rubrica, e Opzioni intestazioni.


- 32-

• Basandoci, ed è la scelta più saggia, su di un file già esistente selezionare "Apri origine dati".

• Nella finestra di dialogo "Apri origine dati" innanzi tutto selezionare come tipo file di dati "Foglio di lavoro Excel - XLS", quindi iniziare la ricerca del file desiderato, nella cartella in cui è registrato, ed eseguire un doppio clic su di esso una volta trovato

A questo punto si attiva il collegamento con Excel, nel quale possiamo scegliere se importare l’intero foglio di calcolo, o se importare solo una parte della tabella. Nel caso si desideri importare solo una parte della tabella, conviene denominare le celle precedentemente e direttamente nel foglio di calcolo. (Dopo aver selezionato l’intervallo, scegli dal menu "Inserisci/Nome/Definisci).

Tornando al nostro esempio, procediamo:

• Selezionare "Intero foglio di lavoro", o il nome dell’intervallo di dati prescelto e cliccare su OK.

• La finestra di dialogo successiva indicherà che nel documento principale, cioè nel file di Word, non sono presenti "Campi unione", conseguentemente cliccare sul pulsante "Modifica documento principale".

• Si ritornerà alla lettera tipo alla quale si sarà aggiunta la barra degli strumenti con cui si può gestire la "Stampa unione".


- 33-

• Verificare l’esito dell’inserimento dei campi unione cliccando sul pulsante "Visualizza dati uniti", che permette di visualizzare la pagina relativa ad ogni record (riga) di Excel; per scorrere i record utilizzare i pulsanti di scorrimento record.

o Nota: i record che saranno visibili saranno tutti quelli valorizzati (Non vuoti); pertanto questa opzione è la migliore per controllare il corretto posizionamento dei campi nel documento Origine di Word

• Cliccare sul pulsante "Unione …" per accedere alla finestra di dialogo "Opzioni unione".

• Scegliere se unire creando un nuovo documento, diverso da quello di partenza di Word, o inviare direttamente il risultato dell’unione alla stampante e fare clic sul pulsante "Unisci".


- 34-

Il risultato dell’unione è fornito pertanto con la creazione di un nuovo file, denominato per comodità da Word col nome di “lettere tipo1”; dopo aver controllato l’effetto della stampa unione, lo si potrà salvare nella cartella desiderata o stamparlo immediatamente.

Vediamo ora alcuni casi particolari, molto utili per gestire al meglio la Stampa Unione.

OPZIONI QUERY PER LA STAMPA UNIONE

A volte si vogliono unire solo una parte dei record al documento principale o dare ad essi un particolare ordine. Naturalmente queste operazioni si possono effettuare a monte, con l’applicazione madre dell’origine dati, però lo stesso Word offre un valido aiuto: le "Opzioni query".

Questa possibilità è attivabile cliccando sul pulsante "Stampa unione" e successivamente sul pulsante "Opzioni query".

L’utilizzo di questa finestra con un solo campo è molto semplice, a meno di non utilizzare più campi, poiché in tal caso si dovrà avere un minimo di dimestichezza con gli operatori di confronto e logici (and, or, not, nor, ecc.).

Nell’immagine sottostante sono stati filtrati i soli record relativi alla provincia di Siena.

Selezionando la scheda “Ordina i record”, è anche possibile ordinare i record in base ad un determinato campo prescelto.


- 35-

UNIONE PER L’INVIO TRAMITE POSTA ELETTRONICA O FAX

Oltre che stampare un documento composito mediante la funzionalità della stampa unione, è possibile unire e distribuire i documenti ed inviarli verso la posta elettronica od il fax.

Per utilizzare questa possibilità, occorrerà propedeuticamente utilizzare la finestra di dialogo "Opzioni query", per selezionare tutti i record contenenti i soli dati degli indirizzi di posta elettronica o di un numero di fax.

Nel caso di invio via e-mail, una volta filtrati i record si potrà cliccare sulla slitta della casella "Unisci a", selezionare Posta elettronica, quindi su Imposta e selezionare il Campo con indirizzo posta/fax, eventualmente da inviare come allegato.

Nel caso di invio via fax, una volta filtrati i record si potrà cliccare sulla slitta della casella "Unisci a", selezionare Stampante; si attiva pertanto il dialogo con la Stampante: selezionare da Stampante, il Nome corretto del fax e confermare con OK (verificare se ci sono fax già installati, altrimenti installarlo e procedere ex-novo).


- 36-

CAMPI WORD PER LA STAMPA UNIONE

I campi di Word sono codici particolari che consentono di inserire ulteriori informazioni in un documento e che si affiancano alle proprietà di filtro (Opzioni query), già disponibili nella “Stampa Unione”.

Utilizzandoli insieme alla stampa unione è possibile personalizzare ulteriormente le lettere tipo.

Il campo di Word che meglio può interagire con la stampa unione è il campo "SE" (o “IF” per le versioni Inglesi), che permette di stampare informazioni in base a condizioni specificate dall’utente.

Per inserire il campo Word "Se" fare clic nel punto del documento ove si vuole inserire la condizione e quindi cliccare sul pulsante "Inserisci campo Word" dalla barra degli strumenti "Stampa unione".

Verrà visualizzato il classico menu a tendina dal quale si dovrà selezionare la voce desiderata. Comparirà la finestra di dialogo "Inserisci campo Word: Se". Si dovrà selezionare il campo interessato alla condizione, scegliere il criterio e il valore, quindi impostare le frasi di risposta alla condizione.

Continuando con l’esempio svolto sinora e riferendoci alla figura sottostante è come se avessimo detto:

"Quando nel campo Provincia c’è TO inserisci la frase - E’ possibile consegnare brevi mano - invece se non c’è TO inserisci - Spedire per posta -, ciò per offrire maggiore risalto al documento da spedire fisicamente ai destinatari scelti.


- 37-

Posizionandosi sopra il punto del documento ove si è inserito il campo Word, cliccare, con il tasto destro del mouse e scegliere dal menu contestuale "Attiva/Disattiva codici campo" per visualizzarne la sintassi, qui sotto rappresentata.

{ SE{ CAMPOUNIONE Provincia } = "TO" "E’ possibile consegnare brevi mano" "Spedire per

posta" }

{ IF {CAMPOUNIONE Provincia} = "TO" "E’ possibile consegnare brevi mano" "Spedire per

posta" } (per la versione Inglese)

Una curiosità: è possibile anche effettuare il confronto fra due campi Word; per realizzare questo tipo di espressione, procedere come nell’esempio precedente evitando però di inserire qualsiasi dato nel campo "Valore".

Con i codici campo visualizzati selezionare le due virgolette relative a "Valore", che in questo caso non conterranno alcunché e cliccando sul pulsante "Inserisci campo unione" scegliere il campo che si desidera inserire per il confronto.

Ecco un esempio, utile per confrontare Aliquota e Sconto in un archivio contabile:

{ SE{ CAMPOUNIONE Aliquota} = { CAMPOUNIONE Sconto} "E’ possibile aumentare lo sconto"}

Naturalmente vi sono altri campi Word utilizzabili con la stampa unione, tra i quali: Domanda (Ask..), Riempi (Fill-in ..), Imposta segnalibro (Set ..).

Si prenda in esame "Riempi" per inserire dei contenuti non previsti nel file dell’origine dati.

Posizionarsi nel punto della lettera tipo ove si vuole inserire del testo extra non previsto nel file di origine dati e cliccare sul pulsante "Inserisci campo Word", dal quale si selezionerà la voce "Riempi".


- 38-

A questo punto effettuando l’unione in un nuovo documento verrà presentata la seguente finestra di dialogo per ogni record. Essendo d’accordo con il testo predefinito basterà cliccare su OK; se invece si vorrà cambiarlo potremo modificarlo a piacere ed infine se non si vorrà inserire alcunché si dovrà eliminarlo premendo il tasto CANC.

EXCEL 2000 Le Formule e le Funzioni Stampa...

Documents

Transcript of EXCEL 2000 Le Formule e le Funzioni Stampa...