Costruire formule - mdamato.altervista.orgmdamato.altervista.org/File/Excel - Costruzione...

1

1Costruire formule

Le formule sono il cuore e l’anima di un foglio di calcolo; MicrosoftExcel offre un ambiente adatto in cui costruire formule complesse.Armato di pochi operatori matematici e regole per l’inserimentonelle celle, puoi trasformare un foglio di lavoro in un potente calco-latore.

■ Nozioni fondamentali sulle formuleTutte le formule in Excel iniziano con il segno di uguale. Il segnouguale indica a Excel che i caratteri successivi costituiranno unaformula. Se ometti il segno di uguale, Excel può interpretare icaratteri come testo.

Per osservare come funzionano le formule, ne analizzeremo alcunetra le più semplici. Inizia selezionando la cella vuota A10. Digitaquindi =10+5 e premi INVIO. Il valore 15 appare nella cella A10.Se ora selezioni la cella A10, la barra della formula mostra la formu-la che hai appena inserito. Nella cella apparirà il valore visualizzato,mentre nella barra della formula osserverai il valore sottostante, chein questo caso è una formula.

Funzioni con Excel

2

Comprendere la precedenza degli operatoriGli operatori sono simboli che rappresentano particolari operazionimatematiche; comprendono il segno più (+), il segno meno (-), ilsegno di divisione (/) e il segno di moltiplicazione (*). Quando ese-gui queste operazioni in una formula, Excel segue determinateregole di precedenza:

■ Le espressioni tra parentesi sono elaborate per prime.

■ Le moltiplicazioni e le divisioni sono eseguite prima di addizionie sottrazioni.

■ Gli operatori consecutivi con lo stesso livello di precedenza sonocalcolati da sinistra verso destra.

Inserisci alcune formule per vedere come vengono applicate questeregole. Seleziona una cella vuota e digita =4+12/6. Premi INVIO eotterrai il valore 6. Excel prima divide 12 per 6, poi somma il risul-tato a 4. Se Excel avesse usato regole di precedenza diverse, il risul-tato sarebbe stato diverso. Per esempio, seleziona un’altra cellavuota e digita =(4+12)/6. Premi INVIO e otterrai il valore2,666667. Questo dimostra come puoi modificare l’ordine di prece-denza utilizzando le parentesi. Le formule nella tabella 1.1 conten-gono gli stessi valori e operatori, ma osserva i diversi risultati dovutialla posizione delle parentesi:

Tabella 1.1. Posizione delle parentesi

Formula Risultato=3*6+12/4-2 19

=(3*6)+12/(4-2) 24

=3*(6+12)/4-2 11,5

=(3*6+12)/4-2 5,5

=3*(6+12/(4-2)) 36

Se non inserisci una parentesi chiusa per ciascuna parentesi apertain una formula, Excel visualizza il messaggio “È stato riscontrato unerrore nella formula” e suggerisce una soluzione. Se il suggerimentocorrisponde a ciò che avevi in mente, premi semplicemente INVIOed Excel provvederà a completare la formula.

Quando digiti una parentesi chiusa, Excel visualizza brevemente lacoppia di parentesi in grassetto. Questa funzione è utile quando

1: Costruire formule

3

inserisci una lunga formula e non ti ricordi quali sono le relativecoppie di parentesi.

SUGGERIMENTO Nel dubbio, usare le parentesi Se non sei sicuro del-l’ordine che Excel seguirà per elaborare una sequenza di operatori, utiliz-za le parentesi, anche se non sono necessarie. Le parentesi rendono inol-tre più semplice la lettura e l’interpretazione delle formule, un grosso van-taggio se tu, o qualcun altro, dovrai preoccuparti di modificarle.

Usare riferimenti di cella nelle formuleUn riferimento di cella identifica una cella o un gruppo di celle inuna cartella di lavoro. Quando inserisci riferimenti di cella in unaformula, quest’ultima viene collegata alle celle cui fa riferimento. Ilvalore risultante dalla formula dipende dai valori nelle celle di rife-rimento e cambia automaticamente quando cambiano i valori nellecelle di riferimento.

Per capire come funzionano i riferimenti di cella, seleziona la cellaA1 e digita la formula =10*2. Ora seleziona la cella A2 e digita laformula =A1. Il valore in entrambe le celle è 20. Se modifichi ilvalore nella cella A1, cambia anche il valore nella cella A2.Seleziona ora la cella A3 e digita =A1+A2. Excel restituisce il valore40. I riferimenti di cella sono particolarmente utili quando crei for-mule complesse.

Inserire riferimenti di cella con il mousePuoi risparmiare tempo e migliorare l’accuratezza quando inserisciriferimenti di cella in una formula selezionandoli con il mouse. Peresempio, per inserire riferimenti alle celle A9 e A10 in una formulanella cella B10, esegui queste operazioni:

1 Seleziona la cella B10 e digita un segno di uguale.

2 Fai clic sulla cella A9 e digita un segno più.

3 Fai clic sulla cella A10 e premi INVIO.

Quando fai clic su ogni cella, un bordo tratteggiato circonderà lacella e nella cella B10 sarà inserito un riferimento alla cella. Quandohai terminato l’inserimento di una formula, premi INVIO. Se nonpremi INVIO e selezioni un’altra cella, Excel ipotizza che tu vogliaincludere il riferimento di cella nella formula.

La cella attiva non deve essere visibile nella finestra corrente perinserire un valore nella cella. Puoi scorrere il foglio di lavoro senza

Funzioni con Excel

modificare la cella attiva e fare clic sulle celle nelle aree remote delfoglio di lavoro corrente, in altri fogli di lavoro o in altre cartelle dilavoro, mentre crei una formula. La barra della formula visualizza ilcontenuto della cella attiva, indipendentemente da quale area delfoglio di calcolo è attualmente visibile.

SUGGERIMENTO Visualizzare la cella attiva Se scorri il foglio di lavoro ela cella attiva non è più visibile, puoi visualizzarla premendo CTRL+BACK-SPACE.

Comprendere riferimenti relativi, assoluti e mistiI riferimenti relativi – quelli che abbiamo usato nelle formule diesempio – fanno riferimento alle celle in base alla posizione dellacella che contiene la formula, per esempio “la cella collocata duerighe sopra questa cella”. I riferimenti assoluti si riferiscono allaposizione fissa delle celle nel foglio di lavoro, per esempio “la cellacollocata all’intersezione della colonna A e della riga 2”. Un riferi-mento misto contiene un riferimento assoluto e un riferimento rela-tivo, per esempio “la cella posizionata nella colonna A e due righesopra questa cella”. I riferimenti assoluti e misti sono importantiquando inizi a copiare le formule da una posizione all’altra nelfoglio di lavoro. Quando copi e incolli, i riferimenti relativi vengonoregolati automaticamente, al contrario di quelli assoluti. Per infor-mazioni sulla copia dei riferimenti di cella, vedi “Come la copiainfluisce sui riferimenti di cella” a pagina 7.

Un riferimento relativo alla cella A1, per esempio, appare come diseguito: =A1. Un riferimento assoluto alla cella A1 appare così:=$A$1. Puoi combinare riferimenti relativi e assoluti alla cella A1per creare questi riferimenti misti: =$A1 o =A$1.

Se il simbolo del dollaro precede solo la lettera (A, per esempio), lacoordinata della colonna è assoluta e quella della riga è relativa. Se ilsimbolo del dollaro precede solo il numero (1, per esempio), lacoordinata della colonna è relativa e quella della riga è assoluta.

Quando inserisci o modifichi una formula, premi F4 per cambiarerapidamente i tipi di riferimento. I seguenti passaggi mostrano comeeseguire l’operazione:

1 Seleziona la cella A1 e digita =B1+B2 (ma non premere INVIO).

2 Premi F4 per convertire in assoluto il riferimento più vicino alcursore lampeggiante. La formula diventa =B1+$B$2.

4


3 Premi ancora F4 per rendere misto il riferimento (la coordinatadella colonna sarà relativa e quella della riga assoluta). La formu-la diventa =B1+B$2.

4 Premi ancora F4 per invertire il riferimento misto (la coordinatadella colonna sarà assoluta e quella della riga relativa). La formu-la diventa =B1+$B2.

5 Premi ancora F4 per tornare al riferimento relativo originale.

Assistenti per formule e funzioniSul sito del libro, troverai la versione demo di un componenteaggiuntivo chiamato Spreadsheet Assistant, che fornisce deci-ne di funzionalità utili per risparmiare tempo. Queste funziona-

lità comprendono un menu Assistants, con un sottomenu di comandiper formule e funzioni, che eseguono numerose operazioni come laconversione di testo numerico in voci numeriche e l’applicazione del-l’arrotondamento alle celle selezionate. Il comando Assistants, For-mulas & Functions, Hide Formula Errors apre la finestra di dialogo mo-strata di seguito, consentendoti di selezionare un intervallo di celle incui nascondere eventuali valori di errore visibili.

Per installare Spreadsheet Assistant, segui le istruzioni visualizzate.

Creare riferimenti ad altri fogli di lavoro nella stessa cartella di lavoroPuoi creare riferimenti alle celle di altri fogli di lavoro all’internodella stessa cartella di lavoro, con la stessa facilità con cui crei riferi-menti nello stesso foglio di lavoro. Per esempio, per inserire nellacella B10 di Foglio1 un riferimento alla cella A9 di Foglio2, eseguiquesti passaggi:

1 Seleziona la cella B10 in Foglio1 e digita un segno di uguale.

2 Fai clic sulla scheda Foglio2.

3 Fai clic sulla cella A9 e premi INVIO.

Dopo aver premuto INVIO, viene attivato Foglio1. Seleziona lacella B10 e osserva che contiene la formula =Foglio2!A9.

5

Funzioni con Excel

La porzione del riferimento relativa al foglio di lavoro è separatadalla porzione relativa alla cella da un punto esclamativo. Osservainoltre che il riferimento di cella è relativo, impostazione predefinitaquando selezioni le celle con lo scopo di creare riferimenti ad altrifogli di lavoro.

Creare riferimenti a fogli di lavoro in altre cartelle di lavoroPuoi creare riferimenti a celle in fogli di lavoro appartenenti a car-telle di lavoro separate, ricorrendo allo stesso metodo usato per l’o-perazione precedente. Questi riferimenti sono chiamati riferimentiesterni. Per esempio, per inserire un riferimento a Cartel2 inCartel1, segui questi passaggi:

1 Crea una nuova cartella di lavoro – Cartel2 – facendo clic sulpulsante Nuovo sulla barra degli strumenti Standard.

2 Seleziona Finestra, Disponi e fai clic sull’opzione Verticale.

3 Seleziona la cella A1 nel Foglio1 di Cartel1 e digita un segno diuguale.

4 Fai clic in un punto qualsiasi di Cartel2 per attivare la finestra diquesta cartella di lavoro.

5 Fai clic sulla scheda Foglio2 nella parte inferiore della finestraCartel2.

6 Fai clic sulla cella A2. Prima di premere INVIO per bloccare laformula, lo schermo dovrebbe apparire come in figura 1.1.

7 Premi INVIO per bloccare il riferimento.

6

Figura 1.1 Puoi inserire riferimenti esterni con estrema facilità facendo semplicemente clicsulla cella alla quale si desidera creare il riferimento.

Comprendere lo stile di riferimento righe/colonneNello stile di riferimento R1C1, sia le righe sia le colonne sono nume-rate. Il riferimento di cella R1C1 significa riga 1 e colonna 1; per-tanto, R1C1 e A1 fanno riferimento alla stessa cella. Sebbene l’u-tilizzo dello stile di riferimento R1C1 non sia più molto diffuso, esso


rappresentava lo stile standard in alcuni programmi per fogli di calco-lo, come Multiplan. Il tradizionale stile di riferimento in Excel as-segna lettere alle colonne e numeri alle righe, per esempio A1 o Z100.

Per attivare lo stile di riferimento R1C1, seleziona Strumenti, Op-zioni, fai clic sulla scheda Generale e seleziona l’opzione Stile di ri-ferimento R1C1. I riferimenti di cella in tutte le formule vengonoimpostati secondo lo stile R1C1. Per esempio, la cella M10 diventaR10C13 e la cella IV65536, l’ultima cella del foglio di lavoro, diven-ta R65536C256.

Nella notazione R1C1, i riferimenti di cella relativi sono visualizzatiin base alla corrispondente relazione con la cella che contiene la for-mula, anziché in base alle coordinate effettive. Questo metodo puòessere utile quando sei più interessato alla posizione relativa di unacella che alla sua posizione assoluta. Per esempio, supponi di volerinserire nella cella R10C2 (B10) una formula che sommi le celleR1C1 (A1) e R1C2 (B2). Dopo aver selezionato la cella R10C2, digi-ta il segno di uguale, seleziona la cella R1C1, digita un segno più eseleziona la cella R1C2, infine premi INVIO. Excel visualizza =R[-9]C[-1]+R[-9]C. I numeri di riga e colonna negativi indicano chela cella cui si fa riferimento si trova sopra o a sinistra della cella dellaformula; i numeri positivi indicano che la cella cui si fa riferimentosi trova sotto o a destra della cella della formula. Le parentesi qua-dre indicano riferimenti relativi. Questa formula si legge “Somma lacella nove righe sopra e una colonna a sinistra alla cella nove righesopra nella stessa colonna”.

Un riferimento relativo a un’altra cella deve includere le parentesi.In caso contrario Excel presume che tu stia usando riferimenti asso-luti. Per esempio, la formula =R8C1+R9C1 usa riferimenti assolutialle celle nelle righe 8 e 9 della colonna 1.

Come la copia influisce sui riferimenti di cellaUno degli aspetti più utili nell’utilizzo dei riferimenti è la possibilitàdi copiare e incollare le formule. Occorre però che tu capisca ciòche accade ai riferimenti dopo aver incollato, in modo da riuscire acreare formule con riferimenti che operino come desideri.

Copiare riferimenti relativiQuando copi una cella contenente una formula con riferimenti dicella relativi, i riferimenti cambiano automaticamente, in base alla

7

Funzioni con Excel

posizione della cella in cui hai copiato la formula. Facendo riferi-mento alla figura 1.2, supponi di digitare =MEDIA(B4:E4) nellacella F4. Questa formula calcola la media dei valori nell’intervallo diquattro celle che inizia quattro colonne a sinistra della cella F4.

8

Figura 1.3 Abbiamo copiato i riferimenti relativi dalla cella F4 alle celle F5:F8.

Copiare riferimenti assolutiSe desideri che i riferimenti di cella rimangano gli stessi dopo lacopia, utilizza i riferimenti assoluti. Per esempio, nel foglio di lavo-ro a sinistra in figura 1.4, la cella B2 contiene la tariffa oraria da

Figura 1.2 La cella F4 contiene riferimenti relativi alle celle alla sua sinistra.

NOTA Puoi trovare il file di esempio usato in questa sezione,Matrici.xls, sul sito del libro.

Desideri ripetere questo calcolo anche per le restanti righe. Invecedi digitare una nuova formula in ogni cella nella colonna F, selezio-na la cella F4 e quindi Modifica, Copia. Seleziona poi le celleF5:F8, seleziona Modifica, Incolla speciale, quindi attiva l’opzioneFormati formule e numero (per conservare la formattazione). Irisultati sono mostrati in figura 1.3. Poiché la formula nella cella F4contiene un riferimento relativo, Excel regola i riferimenti in ognicopia della formula. Il risultato di questa operazione è che la copiadella formula calcola la media delle celle nella riga corrispondente.Per esempio, la cella F7 contiene la formula =MEDIA(B7:E7).


corrispondere ai dipendenti e la cella C5 contiene la formula conriferimento relativo =B2*B5. Supponi di voler copiare la formuladalla cella C5 all’intervallo C6:C8. Il foglio di lavoro a destra in figu-ra 1.4 mostra ciò che accade se copi la formula esistente nell’inter-vallo: ottieni un risultato sbagliato. Anche se le formule nelle celleC6:C8 dovrebbero riferirsi alla cella B2, non lo fanno. Per esempio,la cella C8 contiene la formula non corretta =B5*B8.

9

Figura 1.4 La formula nella cella C5 contiene riferimenti relativi. È stata copiata la formularelativa della cella C5 nelle celle C6:C8, producendo risultati non corretti.

NOTA Puoi trovare il file di esempio usato in questa sezione,Stipendi.xls, sul sito del libro.

Poiché il riferimento alla cella B2 nella formula originale è relativo,cambia quando copi la formula in altre celle. Per applicare corretta-mente la tariffa oraria nella cella B2 a tutti i calcoli, devi convertireil riferimento alla cella B2 a un riferimento assoluto prima di copia-re la formula.

Per cambiare lo stile di riferimento, fai clic sulla barra della formu-la, quindi sul riferimento alla cella B2 e premi F4. Il risultato è laseguente formula: =$B$2*B5.

Quando copi la formula modificata nelle celle C6:C8, il secondoriferimento di cella, ma non il primo, viene adattato all’interno diciascuna formula. Nella figura 1.5, la cella C8 contiene la formulacorretta: =$B$2*B8.

Figura 1.5 Abbiamo creato un riferimento assoluto alla cella B2 prima di copiare la formula.

Funzioni con Excel

Copiare riferimenti mistiPuoi usare i riferimenti misti nelle formule per fissare solo una partedi un riferimento di cella. In un riferimento misto, una parte è asso-luta e l’altra è relativa. Quando copi un riferimento misto, Excelfissa la parte assoluta e adatta la parte relativa in modo che rifletta laposizione della cella nella quale hai copiato la formula.

Per creare un riferimento misto, puoi premere F4 per attivare cicli-camente le quattro combinazioni di riferimenti assoluti e relativi;per esempio, da B2 a $B$2 a B$2 a $B2.

La tabella dei pagamenti di un mutuo, visibile in figura 1.6, usa rife-rimenti misti (e un riferimento assoluto). Occorre inserire solo unaformula nella cella C6 e copiarla fino a riempire la tabella. La cellaC6 contiene la formula =-RATA($B6;$C$3;C$5) che calcola i paga-menti annuali per un mutuo di 10.000, in un periodo di 15 anni aun interesse del 6%. Abbiamo copiato questa formula nell’interval-lo C6:F10 per calcolare i pagamenti di altri tre importi per il mutuoutilizzando quattro tassi di interesse aggiuntivi.

10

Figura 1.6 Questa tabella utilizza formule che contengono riferimenti misti.

NOTA Puoi trovare il file di esempio usato in questa sezione,Mutuo.xls, sul sito del libro.

Il primo riferimento di cella, $B6, indica che vogliamo sempre fareriferimento ai valori nella colonna B, tuttavia il riferimento di riga(Tasso) può cambiare. Allo stesso modo, il riferimento misto C$5indica che vogliamo sempre fare riferimento ai valori nella riga 5,tuttavia il riferimento di colonna (Importo mutuo) può cambiare.Per esempio, la cella E8 contiene la formula =-RATA($B8;$C$3;E$5). Senza riferimenti misti, dovremmo modificare le formulemanualmente in tutte le celle dell’intervallo C6:F10.


SOLUZIONELe celle inserite non sono incluse nelle formule

Se disponi di una formula SOMMA in fondo a una riga di numeri einserisci nuove righe tra i numeri e la formula, il riferimento all’intervallonella funzione SOMMA non considera le nuove celle. Sfortunatamente,non puoi fare nulla per modificare questa impostazione. Questo è unvecchio problema dei fogli di calcolo, che Excel tenta di correggereautomaticamente. Il riferimento all’intervallo nella formula SOMMA noncambia quando inserisci nuove righe. Se invece digiti nuovi valori nellecelle inserite, il riferimento all’intervallo si adatta a ogni nuovo inseri-mento. L’unica accortezza è quella di inserire i nuovi valori uno allavolta, iniziando dalla cella immediatamente sotto la prima colonna del-l’elenco esistente. Se invece inizi a inserire i valori al centro di un grup-po di celle appena inserite, l’intervallo di riferimento rimane invariato.Per ulteriori informazioni sulla funzione SOMMA, vedi “Utilizzare la fun-zione SOMMA” a pagina 72.

Modificare le formulePuoi modificare le formule come modifichi un normale testo. Pereliminare i caratteri in una formula, trascina il mouse sui caratterinella cella o nella barra della formula e premi BACKSPACE oCANC. Per sostituire un carattere, evidenzialo e digita il caratteresostitutivo. Per sostituire un riferimento, evidenzialo e fai clic sullanuova cella cui desideri che la formula faccia riferimento. Vieneinserito automaticamente un riferimento relativo.

Puoi inoltre inserire riferimenti di cella aggiuntivi in una formula.Per esempio, per immettere un riferimento alla cella B1 nella formu-la =A1+A3, posiziona il punto di inserimento tra A1 e il segno più edigita +B1, oppure digita un segno più e fai clic sulla cella B1. Laformula diventa =A1+B1+A3.

Comprendere la sintassi dei riferimentiFinora, per gli esempi di questo libro, abbiamo usato i nomi prede-finiti del foglio di lavoro e della cartella. Quando salvi una cartelladi lavoro, devi assegnarle un nome. Se prima crei una formula e poisalvi la cartella di lavoro con un nuovo nome, la formula viene adat-tata di conseguenza. Per esempio, se salvi Cartel2 con il nomeVendite.xls, la formula con riferimenti remoti =[Cartel2]Fo-glio2!$A$2 cambia in =[Vendite.xls]Foglio2!$A$2. Inoltre, se rino-

11

Funzioni con Excel

mini Foglio2 di Vendite.xls in Febbraio, il riferimento cambia in=[Vendite.xls]Febbraio!$A$2. Se la cartella di lavoro cui fai riferi-mento è chiusa, l’intero percorso della cartella in cui è memorizzatala cartella di lavoro appare nel riferimento, come nell’esempio=’C:\Lavori\[Vendite.xls]Febbraio’!$A$2.

Nell’esempio precedente, osserva gli apostrofi che circondano la car-tella di lavoro e la parte del foglio cui si fa riferimento. Excel ag-giunge gli apostrofi intorno al percorso quando chiudi la cartella dilavoro. Se digiti un nuovo riferimento per la cartella chiusa, tuttavia,devi aggiungere manualmente gli apostrofi. Per evitare errori di digi-tazione, apri la cartella di lavoro chiusa e usa il mouse per inserire iriferimenti, in modo che Excel li inserisca usando la sintassi corretta.

Usare testo numerico nelle formuleIl termine “testo numerico” si riferisce a un inserimento costituitoda numeri e specifici caratteri di testo. Puoi eseguire operazionimatematiche su valori di testo numerici, sempre che la stringa con-tenga solo i caratteri seguenti:0 1 2 3 4 5 6 7 8 9 . + - E e

Inoltre, puoi usare il carattere / nelle frazioni. Puoi utilizzare anche iseguenti cinque caratteri di formattazione numerica:$ , % ( )

Devi inserire le stringhe di testo numerico all’interno delle virgolet-te. Per esempio, se digiti la formula=1234+123, Excel mostra unmessaggio di errore che spiega che Excel ha riscontrato un errorenella formula inserita. Il messaggio di errore offre inoltre una poten-ziale correzione dell’errore che ignora i simboli dell’euro. Tuttavia laformula =”1234”+”123” genera il risultato 1357 (ignorando i sim-boli dell’euro). Quando Excel esegue l’addizione, traduce automati-camente le voci di testo numerico in valori numerici.

Informazioni sui valori di testoIl termine “valori di testo” indica tutte le voci che non sono né valo-ri numerici né valori di testo numerico; l’inserimento è gestito comesolo testo. Puoi gestire i valori di testo nella stessa maniera in cuiutilizzi i valori numerici. Per esempio, se la cella A1 contiene il testoABCDE e digiti la formula =A1 nella cella A10, quest’ultima visua-lizzerà ABCDE.

12


Puoi utilizzare l’operatore & (e commerciale) per concatenare, ounire, diversi valori di testo. Proseguendo con l’esempio preceden-te, se la cella A2 contiene il testo FGHIJ e digiti la formula=A1&A2 nella cella A3, quest’ultima visualizzerà il testo ABCDEF-GHIJ. Per includere uno spazio tra le due stringhe, cambia la for-mula in =A1&” “&A2. Questa formula utilizza due operatori diconcatenazione e una stringa letterale, o costante stringa (uno spazioracchiuso tra due virgolette).

Puoi utilizzare l’operatore & anche per concatenare stringhe divalori numerici. Per esempio, se la cella A3 contiene il valore nume-rico 123 e la cella A4 racchiude il valore numerico 456, la formula=A3&A4 genera la stringa 123456. Quest’ultima è allineata a sini-stra nella cella, perché viene considerata come valore di testo.Ricorda che puoi utilizzare valori di testo numerico per eseguirequalsiasi operazione matematica, purché la stringa numerica conten-ga solo i caratteri numerici elencati in precedenza.

In conclusione, puoi utilizzare l’operatore & per concatenare unvalore di testo e un valore numerico. Per esempio, se la cella A1contiene il testo ABCDE e la cella A3 contiene il valore numerico123, la formula =A1&A3 produce la stringa ABCDE123.

Concatenazione praticaA seconda del tipo di lavoro in cui sei impegnato, le efficaci potenzialitàdi manipolazione del testo di Excel potrebbero rivelarsi come l’aspettopiù importante che apprenderai leggendo questo libro. Se devi gestirespesso delle mailing list, per esempio, utilizzerai probabilmente un’ap-plicazione di elaborazione testi come Microsoft Word. Tuttavia, prose-guendo nella lettura, potresti scoprire che Excel possiede gli strumentiche hai sempre desiderato e che potrebbe dunque diventare la tuaapplicazione di gestione testi preferita.Supponi di disporre di un database di nomi nel quale nome e cognomesono memorizzati in colonne separate. La figura mostra come creare

un elenco di nomi completi.

I nomi completi elencati nelle colonne D ed E sono stati creati utilizzan-do formule simili a quella visibile nella barra della formula in figura. Per

13

Funzioni con Excel

esempio, la formula nella cella D2 è =B2&” “&A2, che concatena icontenuti delle celle nelle colonne A e B e aggiunge un carattere dispazio tra gli elementi. La formula nella cella E2 (visibile in figura)inverte la posizione del nome e del cognome e aggiunge una virgolaprima del carattere di spazio.Ben presto userai il termine concatenazione nelle tue conversazioniquotidiane. Invece di mettere in fila i famosi quarantaquattro gatti, pre-ferirai… concatenarli. Presta attenzione.

NOTA Puoi trovare il file di esempio usato in questa sezione,Concatenazione.xls, sul sito del libro.

■ Utilizzare funzioni: un’anteprimaIn parole semplici, una funzione è una formula predefinita. Moltefunzioni di Excel sono versioni abbreviate delle formule usate piùfrequentemente. Per esempio, la funzione SOMMA genera lasomma una serie di valori di cella attraverso la selezione di un inter-vallo. Metti a confronto la formula =A1+A2+A3+A4+A5+A6+A7+A8+A9 +A10 con la formula =SOMMA(A1:A10). La funzioneSOMMA accorcia leggermente la formula e ne facilita la lettura,nonché la creazione.

Alcune funzioni di Excel eseguono calcoli complessi. Per esempio,utilizzando la funzione RATA, puoi calcolare il pagamento di unmutuo per un certo capitale a un determinato tasso di interesse.

Tutte le funzioni sono costituite da un nome per la funzione, seguitoda una serie di argomenti racchiusi tra parentesi. Nell’esempio pre-cedente, A1:A10 rappresenta l’argomento della funzione SOMMA.Se ometti una parentesi chiusa quando inserisci una funzione, Excelaggiunge la parentesi quando premi INVIO, purché sia ovvia laposizione in cui dovrà essere inserita la parentesi. Se ti affidi com-pletamente a questa funzione, potresti generare risultati inaspettati;per una questione di precisione, controlla sempre due volte leparentesi contenute in una formula.

Usare il pulsante Somma automaticaLa funzione SOMMA è usata più frequentemente delle altre. Perrenderla più accessibile, Excel presenta il pulsante Somma automa-tica sulla barra degli strumenti Standard, che consente di inserire la

14


funzione SOMMA in una cella. Per provare la funzione Sommaautomatica:

1 Inserisci una colonna di numeri, come in figura 1.7.

2 Seleziona la cella sotto la colonna di numeri e fai clic sul pulsanteSomma automatica. Il pulsante inserisce automaticamente l’inte-ra formula e suggerisce un intervallo per la somma. Se il pulsanteSomma automatica non appare sulla barra degli strumentiStandard, fai clic sulla freccia Opzioni barra degli strumenti all’e-stremità destra della barra degli strumenti, per visualizzare unelenco di pulsanti “nascosti”. Dopo aver fatto clic sul pulsanteSomma automatica, esso diventerà visibile sulla barra degli stru-menti.

3 Se l’intervallo suggerito non è corretto, trascina per selezionarequello corretto e premi INVIO.

15

Figura 1.7 Fai clic sul pulsante Somma automatica per aggiungere una formula di riepilogo inuna cella adiacente a colonne o righe di numeri.

SUGGERIMENTO Ottenere con rapidità i risultati delle formule Puoiottenere velocemente il risultato di una somma selezionando le celle chedesideri sommare e osservando poi la barra di stato, dove appare il totaledell’intervallo selezionato. Fai clic con il pulsante destro del mouse sultotale visibile nella barra di stato, per stabilire se per l’intervallo dovràessere calcolata la somma, la media, il conteggio o il valore massimo ominimo.

Il pulsante Somma automatica comprende un menu che apparequando fai clic sulla freccia accanto al pulsante, come mostrato infigura 1.7. Puoi inserire le funzioni Media, Conteggio, Max o Mincon la stessa facilità con cui inserisci una funzione Somma; è necessa-rio solo un ulteriore clic per selezionare la funzione che desideri dalmenu. Inoltre, il comando Altre funzioni apre la finestra di dialogoInserisci funzione, dove puoi accedere a tutte le funzioni di Excel.

Funzioni con Excel

Puoi fare clic sul pulsante Somma automatica per inserire più fun-zioni SOMMA contemporaneamente. Per esempio, se selezioni lecelle B9:C9 in figura 1.5 e fai clic sul pulsante Somma automatica,Excel inserisce le formule SOMMA in entrambe le celle.

Inserire funzioniQuando vuoi usare una funzione incorporata, fai clic sul pulsanteInserisci funzione, vale a dire il pulsante fx sulla barra della formula.Puoi anche selezionare Inserisci, Funzione. Appare la finestra didialogo Inserisci funzione, mostrata in figura 1.8. Per ulteriori detta-gli sull’impiego della finestra di dialogo Inserisci funzione, vedi lasezione “Inserire funzioni” a pagina 66.

16

Figura 1.8 La finestra di dialogo Inserisci funzione permette di accedere a tutte le funzioniincorporate di Excel.

Creare formule tridimensionaliPuoi usare i riferimenti per eseguire calcoli nelle celle incluse in unintervallo di fogli di lavoro in una cartella. Questi riferimenti sonochiamati riferimenti 3D o riferimenti tridimensionali. Ipotizza diimpostare 12 fogli di lavoro nella stessa cartella di lavoro – uno perogni mese – con un foglio di riepilogo annuale all’inizio. Se i foglidi lavoro mensili dispongono dello stesso layout, puoi utilizzare for-mule con riferimenti tridimensionali per riepilogare i dati mensilinel foglio di riepilogo. Per esempio, la formula =SOMMA(Fo-glio2:Foglio13!B5) somma tutti i valori contenuti nella cella B5 intutti i fogli di lavoro compresi tra Foglio2 e Foglio13. Per costruirequesta formula tridimensionale, segui questi passaggi:

1 Nella cella B5 di Foglio1, digita =SOMMA(.

2 Fai clic sulla scheda Foglio2 e seleziona la cella B5.


3 Fai clic sui pulsanti di scorrimento delle schede (a sinistra delleschede del foglio di lavoro) fino a quando è visibile la scheda diFoglio13.

4 Tieni premuto MAIUSC e fai clic sulla scheda di Foglio13. Tuttele schede da Foglio2 a Foglio13 diventano bianche, a indicareche i fogli sono selezionati per essere inclusi nel riferimento chestai costruendo.

5 Seleziona la cella B5.

6 Digita la parentesi di chiusura e premi INVIO.

Puoi utilizzare le seguenti funzioni con i riferimenti tridimensionali:SOMMA, MIN, VAR, CONTA.VALORI, PRODOTTO,VAR.POP, MEDIA, DEV.ST, CONTA.NUMERI, MAX eDEV.ST.POP.

Formattazione nella barra della formulaPuoi inserire spazi e interruzioni di riga in una formula per render-ne più semplice la lettura, senza influire sul calcolo della formula.Per inserire un’interruzione di riga, premi ALT+INVIO. La figura1.9 mostra una formula che contiene interruzioni di riga.

17

Figura 1.9 Puoi inserire interruzioni di riga in una formula per renderla più leggibile.

Creare formule usando un linguaggio naturalePuoi utilizzare etichette invece che riferimenti di cella quando creiformule all’interno delle tabelle del foglio di lavoro. Le etichettesopra le colonne e a sinistra delle righe identificano le celle adiacen-ti nella tabella, quando usi le etichette in una formula. Questa fun-zionalità è chiamata formula in linguaggio naturale, che è unadescrizione piuttosto accurata del modo in cui essa funziona, per-ché ti consente appunto di usare un linguaggio naturale. Il foglio dilavoro in figura 1.10 mostra un esempio di come funziona una sem-plice tabella di vendite.

SUGGERIMENTO Superare la barriera della lingua La capacità di creareformule in linguaggio naturale è un’opzione normalmente disattivata. Perattivarla, seleziona Strumenti, Opzioni; nella scheda Calcolo, fai clic sul-l’opzione Accetta etichette nelle formule. Inoltre, ricorda che l’opzione

Funzioni con Excel

“Accetta etichette nelle formule” non costituisce un’impostazione globale,ma è memorizzata all’interno di ogni singola cartella di lavoro. Devi dunqueattivarla in ciascuna cartella in cui desideri utilizzare formule costruite conun linguaggio naturale.

18

Figura 1.10 Puoi usare etichette di colonne e righe in una tabella per fare riferimento a celle eintervalli nelle formule.

NOTA Puoi trovare il file di esempio usato in questa sezione,Vendite regionali.xls, sul sito del libro.

Le formule nelle righe 9 e 10 potrebbero normalmente contenereformule con riferimenti a intervalli, come =SOMMA(B4: B7).Tuttavia, invece di un riferimento a intervalli, la formula con il lin-guaggio naturale usa un’etichetta di colonna della tabella, per esem-pio =SOMMA(Trim 1), come mostrato in figura 1.10. Quando usiil testo invece dei riferimenti di cella nelle formule, Excel cerca leetichette di colonna e riga corrispondenti. Se Excel individua un’e-tichetta corrispondente, estrapola quello che stai cercando utiliz-zando una serie di complesse regole interne. Per i nostri scopi pos-siamo affermare che:

■ Se una formula contiene un’etichetta della stessa colonna o rigain cui risiede la formula, Excel ipotizza che vuoi usare l’interointervallo di inserimenti adiacente all’etichetta (sotto un’etichet-ta di colonna o a destra di un’etichetta di riga).

■ Se una formula contiene un’etichetta di colonna o riga diversada quella in cui risiede la formula, Excel pensa che tu vogliaagire sulla singola cella all’intersezione della colonna o della rigaetichettata e della colonna o riga contenente la formula.

Se invece non riesce a intuire cosa vuoi fare, Excel mostra una fine-stra di dialogo come quella visibile in figura 1.11, chiedendoti diselezionare l’etichetta.


SOLUZIONEErrori nella creazione di formule con un linguaggio naturale

Le formule create utilizzando un linguaggio naturale sono molto utili,soprattutto in termini di facilitazione della loro lettura e comprensione.Tuttavia, l’impiego di questo tipo di formule può dare luogo ad alcuniproblemi cui è necessario prestare attenzione.■ Potresti visualizzare messaggi di errore del tipo “Nome non valido”.

■ La formula potrebbe generare il valore di errore #NULLO!.

■ Durante la modifica di una formula esistente, parte di essa potrebbeessere convertita in una funzione. Per esempio, la formula “=ProdottoValore”, che fa riferimento alla cella all’intersezione delle colonneProdotto e Valore, potrebbe essere convertita in “=Prodotto VALORE()”.

Questi problemi possono verificarsi per numerosi motivi:■ La formula contiene una funzione o una subroutine VBA con un

nome identico a quello delle etichette.

■ La formula contiene un riferimento a un’altra cartella di lavorocontenente una funzione o una subroutine VBA con un nomeidentico a quello delle etichette.

■ La cartella di lavoro contiene un nome definito identico a quellodelle etichette.

■ Una delle etichette è identica a una funzione incorporata.

Per risolvere questi problemi, racchiudi le etichette tra apici (‘). Peresempio, cambia la formula che appare come =Trim 1 Prodotto 1 in=’Trim 1’ ‘Prodotto 1’.

Per identificare una specifica cella in una formula in linguaggionaturale, usiamo l’intersezione. Per esempio, la formula =Regione 2Trim 2 individua la cella all’intersezione della riga Regione 2 e dellacolonna Trim 2, vale a dire la cella C5. Osserva che devi inserire unospazio tra le etichette di riga e di colonna nella formula.

Figura 1.11 Se Excel non individua la cella che si desidera usare, appare la finestra di dialogoIdentifica etichetta.

19

Funzioni con Excel

Puoi copiare e incollare formule in linguaggio naturale; le colonne ele righe alle quali fai riferimento si adattano all’operazione eseguita.In figura 1.11, la formula nella cella B9 è stata copiata nelle celleC9:E9. Tutte le formule nell’intervallo di copia sono state regolateper usare l’etichetta della colonna corrente. Per esempio, dopo avercopiato a destra la formula nella cella B9, la formula nella cella E9diventa =SOMMA(Trim 4).

Osserva che, se copi le formule in linguaggio naturale dove non c’èalcuna logica, Excel ti avvisa che c’è qualcosa di sbagliato. Peresempio, se copi la formula della cella H9 nella cella J9, Excelmostra il valore di errore #NOME? perché non esiste alcuna eti-chetta nella colonna J.

Se cambi le etichette delle colonne e delle righe nelle tabelle, le eti-chette che hai digitato nelle formule si adatteranno automaticamen-te alla modifica. Per esempio, se trasformi l’etichetta Totale dellacella G3 visibile in figura 1.11 in Anno, la formula nella cella G9cambierà automaticamente in =SOMMA(Anno).

SUGGERIMENTO Utilizzare nomi per maggiore controllo Puoi usare for-mule in linguaggio naturale nelle righe e nelle colonne del foglio di lavorocontenente la tabella e le relative etichette, ma non puoi usare le etichettenelle formule di altri fogli o cartelle di lavoro. Se utilizzi nomi di intervalli dicelle definiti al posto delle etichette, puoi usare i nomi nelle formule diqualsiasi foglio in qualsiasi cartella di lavoro.

Essere espliciti con le intersezioniNel foglio di lavoro in figura 1.11, se digiti la formula =Trim 1*4 nella cellaI4, Excel pensa che tu voglia utilizzare solo un valore nell’intervallo Trim1 (B4:B7): quello che si trova sulla stessa riga della formula che contieneil riferimento. Questa funzione è chiamata intersezione implicita. Poichéla formula è nella riga 4, Excel utilizza il valore nella cella B4. Se inseriscila stessa formula nelle celle I5, I6 e I7, tutte le celle nell’intervallo conter-ranno la formula =Trim 1*4, ma nella cella I5 la formula farà riferimentoalla cella B5, nella cella I6 farà riferimento alla cella B6 e così via.L’intersezione esplicita si riferisce a una specifica cella con l’aiuto del-l’operatore di intersezione. Il cosiddetto operatore di intersezione è ilcarattere di spazio che appare quando premi la BARRA SPAZIATRICE.Se digiti la formula =Trim 1 Regione 1*4 in qualsiasi posizione delfoglio di lavoro, Excel sa che desideri fare riferimento al valore cherisiede nell’intersezione dell’intervallo etichettato Trim 1 e dell’intervalloetichettato Regione 1, vale a dire la cella B4.

20


Sostituire automaticamente le etichetteExcel può sostituire automaticamente le etichette per i riferimenti dicella nelle formule. A tal fine, devi prima definire gli intervalli di eti-chette come segue:

1 Seleziona Inserisci, Nome, Etichetta. Appare la finestra di dialo-go Intervalli etichette mostrata in figura 1.12.

2 Fai clic sulla casella nella sezione Aggiungi intervallo etichette,quindi trascina il mouse nel foglio di lavoro per selezionare le eti-chette che desideri definire.

3 Seleziona l’opzione Etichette di riga o Etichette di colonna.

4 Fai clic sul pulsante Aggiungi per aggiungere l’intervallo di eti-chette nell’elenco Intervalli etichette esistenti.

Dopo aver definito gli intervalli di etichette, ogni volta che farai clicsu una cella all’interno di tali intervalli per creare una formula,Excel sostituirà automaticamente il nome della cella con le etichettein linguaggio naturale.

21

Figura 1.12 Utilizzare la finestra di dialogo Intervalli etichette per sostituire le etichette deiriferimenti di cella nelle formule.

Quando definisci le etichette, i riferimenti nelle formule esistentinon vengono modificati automaticamente, ma Excel sostituirà leetichette appropriate quando inserirai nuove formule. Per esempio,dopo aver definito le etichette in figura 1.12, puoi fare clic sullacella I5, digitare il segno uguale e fare clic sulla cella C5.Normalmente Excel inserisce il riferimento di cella C5 nella formu-la, ma in questo caso appare l’intersezione di etichetta Trim 2Regione 2.

Funzioni con Excel

SOLUZIONELe formule generano risultati imprevisti con l’impiego delle etichette

Quando usi le etichette nelle formule, lascia una colonna o una rigavuota tra la tabella del foglio di lavoro e le formule. In caso contrariopotresti ottenere risultati inaspettati, soprattutto quando lavori con piùrighe o colonne di formule. Per esempio, senza una riga vuota chesepara le formule Totale e Media in fondo alla tabella in figura 1.10, leformule per il calcolo della media includerebbero i valori restituiti dalleformule Totale quando vengono usate le etichette. Un altro modo perrisolvere questo problema è usare nomi definiti al posto delle etichettenelle formule.

■ Assegnare nomi a celle e intervalliSe devi digitare ripetutamente indirizzi di cella complessi, comeFoglio3!A1:AJ51 nelle formule, non devi preoccuparti: Excel offreuna soluzione al problema rendendo molto più semplice l’operazio-ne. Assegna un nome breve e facile da ricordare alla cella o all’inter-vallo e usa quel nome invece del crittogramma nelle formule.

Dopo aver definito i nomi in un foglio di lavoro, tali nomi vengonoresi disponibili in qualsiasi altro foglio di lavoro nella cartella. Unnome che definisce un intervallo di cella in Foglio6, per esempio,può essere usato nelle formule di Foglio1, Foglio2 e così via nellacartella di lavoro. Come risultato, ogni cartella di lavoro conterrà unproprio gruppo di nomi. Puoi inoltre definire nomi a livello difoglio, disponibili solamente nel foglio di lavoro in cui sono definiti.

SUGGERIMENTO Non definire nomi per tabelle semplici In una semplicetabella con intestazioni di riga e di colonna, puoi usare le intestazioni stes-se nelle formule collocate nelle medesime righe e colonne, senza doverprima definire i nomi. Vedi “Creare formule usando un linguaggio naturale”a pagina 17.

Usare nomi nelle formuleQuando usi il nome di una cella o di un intervallo in una formula, ilrisultato è uguale a quello che otterresti inserendo il riferimento dicella o di intervallo. Per esempio, supponi di aver inserito la formula=A1+A2 nella cella A3. Se hai definito il nome Marco per la cellaA1 e il nome Vittoria per la cella A2, la formula =Marco+Vittoriagenera lo stesso risultato.

22


Quello che segue è il modo più semplice per definire un nome:

1 Seleziona una cella.

2 Fai clic sulla casella Nome sulla barra della formula, come mo-strato in figura 1.13.

3 Digita NomeDiProva e premi INVIO.

23

Figura 1.13 Usa la casella Nome sulla barra della formula per assegnare rapidamente nomi acelle e intervalli.

Ricorda i concetti base che seguono quando usi nomi nelle formule:

■ La casella Nome normalmente mostra l’indirizzo della cella sele-zionata. Se la cella o l’intervallo selezionato possiede un nome,quest’ultimo ha la precedenza sull’indirizzo ed è visualizzatonella casella Nome.

■ Quando definisci un nome per un intervallo di celle, il nome diquest’ultimo non appare nella casella Nome a meno che sia sele-zionato l’intero intervallo.

■ Quando fai clic sulla casella Nome e selezioni un nome, la sele-zione della cella si sposta sulle celle con nome.

■ Se digiti un nome già definito nella casella Nome, Excel spostala selezione invece di ridefinire il nome.

■ Quando definisci un nome, l’indirizzo include il nome del fogliodi lavoro e il riferimento di cella è assoluto. Per esempio, quan-do definisci il nome NomeDiProva per la cella C5 di Foglio1, ladefinizione attuale del nome è registrata come Foglio1!$C$5.

Definire e gestire nomiInvece di cercare nuovi nomi per le celle e gli intervalli, puoi usaresemplicemente le etichette di testo esistenti per creare i nomi.Selezionando Inserisci, Nome, Definisci, puoi usare il testo nellecelle adiacenti per definire i nomi di celle e intervalli, come puoiosservare in figura 1.14. Puoi inoltre selezionare questo comandoper ridefinire nomi esistenti.

Funzioni con Excel

Regole per l’assegnazione dei nomiLe regole che seguono si applicano quando assegni un nome a celle eintervalli in Excel:■ Tutti i nomi devono iniziare con una lettera, una barra rovesciata (\)

oppure un carattere di sottolineatura (_).

■ I numeri possono essere usati.

■ Gli spazi non possono essere usati.

■ Excel converte gli spazi vuoti nelle etichette in caratteri di sottoli-neatura nei nomi definiti.

■ I simboli diversi dalla barra rovesciata e dal carattere di sottolineatu-ra non possono essere usati.

■ I nomi che ricordano riferimenti di cella (per esempio, AB$5 oR1C7) non possono essere usati.

■ Le singole lettere, fatta eccezione per le lettere R e C, possonoessere usate come nomi.

Un nome può contenere 255 caratteri, ma se ne contiene più di 253non può essere selezionato dalla casella Nome. Excel non fa distinzio-ne tra lettere maiuscole o minuscole nei nomi. Per esempio, se crei ilnome Imposta e poi crei il nome IMPOSTA nella stessa cartella di lavo-ro, il primo nome viene sovrascritto dal secondo.

SUGGERIMENTO Premi CTRL+F3 per visualizzare la finestra di dialogoDefinisci nome.

Se selezioni l’intervallo al quale desideri assegnare un nome primadi scegliere Inserisci, Nome, Definisci e vuoi utilizzare l’etichettaadiacente come nome, premi semplicemente INVIO per definire ilnome. La volta successiva che aprirai la finestra di dialogo Definiscinome, tale nome apparirà nella casella Nomi nella cartella di lavoro,che mostra tutti i nomi definiti per la cartella.

Puoi definire un nome anche senza aver selezionato prima una cellao un intervallo nella cartella di lavoro. Per esempio, nella finestra didialogo Definisci nome, digita Prova2 all’interno della casella Nominella cartella di lavoro, quindi digita =D20 nella casella di testoRiferito a. Fai clic su Aggiungi per aggiungere il nome all’elenco.

La finestra di dialogo Definisci nome rimane aperta; la casella ditesto Riferito a mostra la definizione del nome =Foglio1!D20. Excelaggiunge il riferimento al foglio di lavoro, ma il riferimento della

24


cella rimane relativo, proprio come lo avevi inserito. Se non inserisciil segno di uguale prima del riferimento, Excel interpreta la defini-zione come testo. Per esempio, se hai digitato D20 invece di =D20,la casella Riferito a visualizza la costante di testo =”D20” come defi-nizione del nome Prova2.

Quando viene aperta la finestra di dialogo Definisci nome, puoiinserire riferimenti nella casella Riferito a anche selezionando lecelle nel foglio di lavoro. Se assegni un nome a più celle o intervallinella finestra di dialogo Definisci nome, assicurati di fare clic suAggiungi dopo aver inserito ogni definizione. Se fai clic su OK,Excel chiude la finestra di dialogo.

25

Figura 1.14 Quando scegli Inserisci, Nome, Definisci, eventuali etichette presenti nelle celleadiacenti della stessa riga o colonna sono indicate come nomi.

Modificare nomi Per ridefinire il nome di una cella o di un intervallo esistente nellafinestra di dialogo Definisci nome, devi innanzi tutto selezionarlonell’elenco Nomi nella cartella di lavoro e quindi modificare il rife-rimento della cella o dell’intervallo, nella casella Riferito a. Puoidigitare un nuovo riferimento o selezionare una nuova cella o inter-vallo di celle direttamente nel foglio di lavoro, mentre è aperta lafinestra di dialogo Definisci nome.

Per eliminare un nome dalla finestra di dialogo Definisci nome,selezionalo dall’elenco Nomi nella cartella di lavoro e fai clic suElimina. Ricorda che, quando elimini un nome, qualsiasi formulanel foglio di lavoro che fa riferimento a quel nome restituisce ilvalore di errore #NOME?.

Nomi a livello di cartella e nomi a livello di foglioI nomi in Excel sono normalmente a livello di cartella di lavoro. Ciòsignifica che un nome definito in qualsiasi foglio di lavoro può esse-

Funzioni con Excel

re usato nelle formule da qualsiasi altro foglio di lavoro. Tuttavia,puoi anche creare nomi a livello di foglio, che sono disponibili sola-mente nel foglio di lavoro in cui sono definiti. Per definire un nome alivello di foglio per una cella o un intervallo di celle, fai precedere ilnome dal nome del foglio di lavoro, seguito da un punto esclamativo.

Per esempio, per definire NomeFoglioProva come nome a livello difoglio in Foglio1, seleziona l’intervallo che desideri, seleziona Inse-risci, Nome, Definisci e quindi digita Foglio1!NomeFoglioProvanella casella Nomi nella cartella di lavoro. Quelli che seguono sonoalcuni fattori aggiuntivi che devi ricordare quando lavori con i nomi alivello di foglio o a livello di cartella di lavoro:

■ I nomi a livello di foglio non appaiono nella casella Nome e nellafinestra di dialogo Definisci nome nei fogli diversi da quelli in cuisono definiti.

Selezionare celle quando una finestra di dialogo è apertaLa casella Riferito a nella finestra di dialogo Definisci nome contieneun pulsante Comprimi finestra, che ti consente di esplorare e selezio-nare le celle. Per esempio, dopo aver fatto clic sulla casella Riferito a,puoi fare clic sulla scheda di qualsiasi altro foglio di lavoro, trascinare lebarre di scorrimento e rendere attiva un’altra cartella di lavoro selezio-nandone il nome dal menu Finestra. Inoltre, facendo clic sul pulsanteComprimi finestra, la finestra di dialogo viene ridotta e potrai cosìosservare uno spazio più ampio del foglio di lavoro.

Puoi trascinare la finestra di dialogo ridotta sullo schermo usando lasua barra del titolo. Quando hai terminato, fai clic nuovamente sul pul-sante Comprimi finestra: la finestra di dialogo tornerà alle sue dimen-sioni originali.

■ Quando selezioni una cella o un intervallo che hai definito conun nome a livello di foglio, il nome della cella o dell’intervallo(NomeFoglioProva, per esempio) appare nella casella Nomesulla barra della formula; il nome del foglio di lavoro (Foglio1!,

26


per esempio) rimarrà tuttavia nascosto. Per questa ragione, assi-curati che i nomi a livello di foglio siano facilmente distinguibili.Per esempio, potresti includere la parola “Foglio” nella partetestuale del nome, nel momento in cui lo definisci.

■ Per modificare un nome a livello di foglio, attiva il foglio di lavo-ro nel quale è definito e seleziona Inserisci, Nome, Definisci.

■ Quando un foglio di lavoro contiene un nome a livello di cartella eun nome a livello di foglio duplicati, il nome a livello di foglio hala precedenza sul nome a livello di cartella nel foglio in cui risiede.

■ Non puoi usare un nome a livello di cartella duplicato nel fogliodi lavoro in cui è definito il nome a livello di foglio.

■ Puoi usare un nome a livello di foglio in una formula su altri foglidi lavoro includendo nella formula il nome nella sua interezza. Peresempio, puoi digitare la formula =Foglio2!NomeFoglioProva inuna cella su Foglio3.

Creare nomi in modo semiautomaticoPuoi selezionare Inserisci, Nome, Crea per assegnare un nome a piùcelle o intervalli adiacenti contemporaneamente, usando etichette diriga, di colonna o entrambe. Quando selezioni questo comando,Excel visualizza la finestra di dialogo Crea nomi, mostrata in figura1.15.

27

Figura 1.15 Usa la finestra di dialogo Crea nomi per assegnare un nome a più celle o intervallicontemporaneamente utilizzando le etichette. I nomi risultanti appaiono nella casella Nome.

Seleziona le opzioni appropriate per le celle selezionate nella sezio-ne Crea nomi nella; Excel usa le etichette incluse nella selezione perassegnare un nome a ciascun intervallo. Per esempio, la figura 1.15mostra che, selezionando l’intervallo A3:E7, sono selezionate leopzioni Riga superiore e Colonna sinistra nella finestra di dialogo

Funzioni con Excel

Crea nomi, creando così un gruppo per ogni Trim e Regione.Osserva che quando usi il comando Crea nomi, devi selezionare leetichette nonché i dati. Quando fai clic sulla freccia a destra dellacasella Nome sulla barra della formula, puoi visualizzare i nomiTrim_1, Trim_2, Trim_3 e Trim_4. Il nome Trim_1, per esempio, èdefinito come intervallo $B$4:$B$7.

Assegnare nomi a costanti e formule Puoi creare nomi definiti da costanti e formule invece che da riferi-menti di cella. Puoi usare riferimenti assoluti e relativi, numeri,testo, formule e funzioni come definizioni di nomi. Per esempio, seusi frequentemente il valore 8,3% per calcolare un’imposta sullevendite, puoi selezionare Inserisci, Nome, Definisci, digitare il nomeTassa nella casella Nomi nella cartella di lavoro e digitare 8,3% (o,083) nella casella Riferito a. A questo punto, puoi usare il nomeTassa in una formula, per esempio =Prezzo+(Prezzo*Tassa), percalcolare il costo degli articoli con un’imposta sulle vendite pari a8,3%. Osserva che le costanti e le formule con nome non appaiononella casella Nome della barra della formula, ma appaiono nellafinestra di dialogo Definisci nome.

Puoi inoltre inserire una formula nella casella Riferito a. Per esem-pio puoi definire il nome Prezzo con una formula come=Foglio1!A1*190%. Se definisci questa formula con nome mentreè selezionata la cella B1, puoi digitare =Prezzo nella cella B1; in talmodo la formula definita provvede al calcolo che ti interessa. Poichéil riferimento nella formula con nome è relativo, puoi digitare=Prezzo in qualsiasi cella nella cartella di lavoro per calcolare unprezzo utilizzando il valore nella cella direttamente a sinistra. Seinserisci una formula nella casella Riferito a che fa riferimento a unacella o a un intervallo nel foglio di lavoro, Excel aggiorna la formulaogni volta che cambia il valore nella cella.

Utilizzare riferimenti relativi nelle formule con nomeQuando crei una formula con nome, contenente riferimenti relativi,per esempio =Foglio1!B22+1,2%, Excel interpreta la posizionedelle celle con riferimento nella casella Riferito a come relativa allacella attiva, al momento della definizione. Successivamente, quandoutilizzi questo nome in una formula, la formula con nome usa ilvalore corrispondente al riferimento relativo. Per esempio, se lacella B21 era la cella attiva quando hai definito il nome Tassa come

28


=Foglio1!B22+1,2%, il nome Tassa farà sempre riferimento allacella una riga sotto la cella in cui è attualmente collocata la formula.

Creare nomi tridimensionaliPuoi creare nomi tridimensionali che usano riferimenti tridimensio-nali come loro definizioni. Per esempio, supponi di avere una cartel-la di lavoro costituita da 13 fogli per ogni mese, uno identico all’al-tro, più un foglio di riepilogo. Puoi definire un nome tridimensiona-le che possa essere usato per riepilogare i totali da ciascun fogliomensile. A tal fine, esegui questi passaggi:

1 Seleziona la cella B5 in Foglio1 (il foglio Riepilogo).

2 Seleziona Inserisci, Nome, Definisci.

3 Digita Tre_D (o qualsiasi altro nome) nella casella Nomi nellacartella di lavoro e digita =Foglio2:Foglio13!B5 nella casellaRiferito a.

4 Premi INVIO.

Ora puoi usare il nome Tre_D nelle formule contenenti una delleseguenti funzioni: SOMMA, MIN, VAR, CONTA.VALORI, PRO-DOTTO, VAR.POP, MEDIA, DEV.ST, CONTA.NUMERI, MAX eDEV.ST.POP. Per esempio, la formula =MAX(Tre_D) restituisce ilvalore più grande nell’intervallo tridimensionale di nome Tre_D.Poiché hai usato riferimenti relativi nel passaggio 3, la definizionedell’intervallo Tre_D cambia quando selezioni diverse celle nellacartella di lavoro. Per esempio, se selezioni la cella C3 e visualizzi lafinestra di dialogo Definisci nome, nella casella Riferito a appare=Foglio2:Foglio13!C3.

Incollare nomi nelle formuleDopo aver definito uno o più nomi all’interno del foglio di lavoro,puoi inserirli nelle formule utilizzando la finestra di dialogo Incollanomi, mostrata in figura 1.16.

29

Figura 1.16 Usa la finestra di dialogo Incolla nomi per inserire nomi nelle formule.

Funzioni con Excel

Per esempio, per incollare il nome Trim_1 in una formula:

1 Digita un segno di uguale e quindi digita gli operatori, le funzio-ni o le costanti della formula.

2 Posiziona il punto di inserimento nella formula, nel punto in cuivuoi inserire il nome, quindi seleziona Inserisci, Nome, Incolla(oppure premi F3).

3 Seleziona Regione_1 e fai clic su OK per inserire il nome nellaformula.

4 Digita qualsiasi altro operando e operatore per completare la for-mula, quindi premi INVIO.

Utilizzando l’esempio precedente, Incolla nomi potrebbe non sem-brare una funzionalità molto vincolante, a meno che i nomi definitinon siano troppo lunghi o criptici da digitare in modo affidabilenelle formule.

Creare un elenco di nomi Nei modelli di foglio di lavoro grandi, è facile accumulare un lungoelenco di nomi definiti. Per tenere traccia di tutti i nomi utilizzati,puoi incollare un elenco di nomi definiti nel foglio di lavoro facendoclic sul pulsante Incolla elenco nella finestra di dialogo Incollanomi, come mostrato in figura 1.17. Excel incolla l’elenco nel fogliodi lavoro iniziando dalla cella attiva.

30

Figura 1.17 Usa il pulsante Incolla elenco per creare un elenco di nomi e delle relative defini-zioni.

NOTA Quando Excel incolla l’elenco, sovrascrive qualsiasi dato esistente.Se inavvertitamente sovrascrivi i dati, premi CTRL+Z per annullare l’ope-razione.

Sostituire i riferimenti con i nomiPer sostituire i riferimenti nelle formule con i corrispondenti nomi,seleziona Inserisci, Nome, Applica. Excel trova tutti i riferimenti di


cella e intervallo per i quali esistono nomi definiti e li cambia. Seselezioni una cella singola prima di selezionare il comando Applica,Excel applica i nomi nell’intero foglio di lavoro attivo; se selezioniun intervallo di celle, Excel applica i nomi solo alle celle selezionate.

La figura 1.18 mostra la finestra di dialogo Applica nomi, che elencatutti i nomi di cella e intervallo che hai definito. Se non desideriincludere tutti i nomi, fai clic su un nome presente nell’elenco percancellarlo; fai clic nuovamente per riselezionarlo.

31

Figura 1.18 Usa la finestra di dialogo Applica nomi per sostituire i nomi ai riferimenti di cella eintervallo nelle formule. Fai clic su Opzioni per visualizzare tutte le opzioni mostrate in figura.

Excel normalmente non applica il nome della colonna e della rigase comunque è superfluo. Per esempio, la figura 1.18 mostra unfoglio dopo l’applicazione dei nomi utilizzando le opzioni predefi-nite nella finestra di dialogo Applica nomi. È selezionata la cella I14e la barra degli strumenti mostra la formula in essa contenuta, valea dire =Regione_2 Trim_3, che prima dell’applicazione dei nomiconteneva la formula =D5. Poiché la cella I14 non si trova sullastessa riga o colonna di intervalli definiti, entrambi i nomi dellerighe e delle colonne sono inclusi nella nuova formula. La cella D14conteneva la stessa formula, =D5. Tuttavia, poiché D14 si trova

Funzioni con Excel

nella stessa colonna di una cella con riferimento, solo è necessario ilnome della riga grazie all’intersezione implicita; viene quindi gene-rata la formula =Regione_2.

Se preferisci osservare entrambi i nomi di colonna e riga anchequando non sono necessari, deseleziona le opzioni Ometti il nomedella colonna se è la stessa e Ometti il nome della riga se è la stessa.

Le opzioni Ordine dei nomi controllano l’ordine in cui appaiono icomponenti di riga e colonna. Per esempio, utilizzando l’opzioneColonna, riga, la formula nella cella I14 mostrata in figura 1.8 diven-ta =Trim_3 Regione_2.

Seleziona l’opzione Ignora relativo/assoluto per sostituire i riferi-menti con i nomi senza considerare il tipo di riferimento. In genera-le, lascia selezionata questa casella di controllo. La maggior partedelle definizioni dei nomi usa riferimenti assoluti (l’impostazionepredefinita quando definisci e crei nomi) e la maggior parte delleformule usa riferimenti relativi (l’impostazione predefinita quandoincolli riferimenti di cella e intervalli nella barra della formula). Sedeselezioni questa opzione, i riferimenti assoluti, relativi e mistisono sostituiti da definizioni di nomi solo se le definizioni usano lostile di riferimento corrispondente.

L’opzione Usa i nomi di riga e di colonna è necessaria se desideriapplicare i nomi nei casi di intersezione, come mostrato negli esem-pi. Se definisci nomi per celle singole, tuttavia, puoi deselezionareUsa i nomi di riga e di colonna per applicare i nomi solo a specificiriferimenti di cella nelle formule.

Usare Vai con i nomi degli intervalli Quando selezioni Modifica, Vai (o premi F5), i nomi degli intervalliappaiono nella finestra di dialogo Vai a, come mostrato in figura1.19. Seleziona un nome e fai clic su OK per spostarti nell’intervalloselezionato. Osserva che i nomi definiti con costanti o formule nonappaiono nella finestra di dialogo Vai a.

SUGGERIMENTO Spostarsi con la casella Riferimento La finestra di dia-logo Vai a ricorda le ultime quattro posizioni visitate. L’ultima posizioneappare sempre nella casella Riferimento quando visualizzi la finestra didialogo, quindi puoi spostarti tra le due posizioni premendo F5 e poiINVIO.

32


Figura 1.19 Usa la finestra di dialogo Vai a per selezionare il nome di una cella o di un interval-lo, in modo da spostarsi rapidamente nella cella o nell’intervallo desiderati.

■ Comprendere i valori di erroreUn valore di errore è il risultato di una formula che Excel non puòrisolvere. I sette valori di errore sono descritti nella tabella 1.2.

Tabella 1.2 Valori di errore

Valore Causadi errore#DIV/0! Hai tentato di dividere un numero per zero. Questo errore

solitamente si verifica quando crei una formula con un divi-sore che si riferisce a una cella vuota.

#NOME? Hai inserito in una formula un nome che non è contenutonell’elenco della finestra di dialogo Definisci nome. Potrestiaver commesso un errore di ortografia o digitato un nomeche hai eliminato. Excel visualizza questo valore di erroreanche se non inserisci una stringa di testo tra virgolette.

#VALORE Hai inserito una formula matematica che si riferisce a uninserimento di testo.

#RIF! Hai eliminato un intervallo di celle i cui riferimenti sonoinclusi in una formula.

#N/D Non esistono informazioni disponibili per il calcolo chevuoi eseguire. Quando costruisci un modello, puoi digitare#N/D in una cella per mostrare che sei in attesa di dati.Qualsiasi formula che fa riferimento alle celle contenenti ilvalore #N/D restituisce #N/D.

33

Funzioni con Excel

Tabella 1.2 Continua

Valore Causadi errore#NUM! Hai inserito un argomento non valido in una funzione del

foglio di lavoro. #NUM! può anche indicare che il risultatodi una formula è troppo grande o troppo piccolo per essererappresentato nel foglio di lavoro.

#NULLO! Hai inserito uno spazio tra due intervalli in una formula perindicare un’intersezione, ma gli intervalli non hanno celle incomune.

■ Calcoli nel foglio di lavoroGli sviluppatori di Excel indossavano magliette con il grido di bat-taglia “Ricalcola o muori”. Ti assicuro che nessuno tra gli sviluppa-tori di Excel ha mai subito danni durante la creazione di qualsiasiversione del programma. L’apparentemente semplice processo dicalcolo computa tutte le formule e visualizza poi i risultati nelle celleche le contengono. Quando modifichi i valori nelle celle alle quali siriferiscono queste formule, Excel aggiorna anche i valori delle for-mule. Questo processo di aggiornamento è chiamato ricalcolo eriguarda solo le celle contenenti riferimenti alle celle modificate.

Per impostazione predefinita, Excel esegue il ricalcolo ogni voltache apporti modifiche a una cella. Se deve essere ricalcolato un grannumero di celle, all’estrema sinistra della barra di stato appaiono leparole “Calcola celle:” e un numero.

Questo numero indica la percentuale di ricalcolo completato. Puoiinterrompere il processo di ricalcolo semplicemente utilizzando icomandi o inserendo dati nelle celle; Excel si interrompe e riprendeil ricalcolo quando termini le operazioni.

Eseguire il ricalcolo in modo manualePer risparmiare tempo, soprattutto quando inserisci dati in un’am-pia cartella di lavoro con numerose formule, puoi passare dal rical-colo automatico a quello manuale; vale a dire che Excel eseguirà ilricalcolo solo quando sarai tu a chiedergli di farlo. Per impostare ilricalcolo manuale, seleziona Strumenti, Opzioni, quindi fai clic sullascheda Calcolo per visualizzare le opzioni mostrate in figura 1.20.

34


Figura 1.20 La scheda Calcolo della finestra di dialogo Opzioni controlla il calcolo e le iterazionidel foglio di lavoro.

Ecco alcune informazioni che devi ricordare sulle opzioni di calcolo:

■ Per disattivare il ricalcolo automatico, seleziona l’opzioneManuale. Se apporti modifiche che normalmente darebbero ini-zio al ricalcolo, la barra di stato mostra la parola Calcola invecedi ricalcolare automaticamente.

■ L’opzione Ricalcola prima di salvare ti assicura che la maggiorparte dei valori correnti sia memorizzata su disco.

■ Per disattivare il ricalcolo automatico solo per i dati delle tabelle,seleziona l’opzione Automatico escluso tabelle. Per ulterioriinformazioni, vedi “Usare tabelle di dati” a pagina 171.

■ Per ricalcolare tutte le cartelle di lavoro aperte, fai clic sul pul-sante Calcola (F9).

■ Per calcolare solo il foglio di lavoro attivo di una cartella di lavo-ro, fai clic sul pulsante Calc. documento o premi MAIUSC+F9.

Copiare formule e incollare solo i valori risultantiSi tratta indubbiamente di una delle funzionalità più utilizzate diExcel. Normalmente, quando copi una cella che contiene una for-mula, anche la formula viene incollata (funzione molto utile).Tuttavia, se vuoi copiare solo il risultato senza la formula, selezionaModifica, Incolla speciale. Appare la finestra di dialogo Incolla spe-ciale, mostrata in figura 1.21. Tra le altre cose, puoi selezionare

35

Funzioni con Excel

Incolla speciale per estrarre i risultati dalle formule. Per trasferiresolo i valori risultanti delle formule, seleziona l’opzione Valori.

36

Figura 1.21 Seleziona Modifica, Incolla speciale per estrarre informazioni specifiche dallecelle copiate.

Valutare parte di una formulaPotresti anche voler vedere il risultato solo di una parte di una for-mula complessa se, per esempio, stai cercando una discrepanza. Perconvertire solo una parte della formula in un valore, seleziona laparte che desideri modificare e premi F9. Puoi utilizzare questa tec-nica anche per cambiare i riferimenti di cella evidenziati nelle for-mule nei corrispondenti valori. La figura 1.22 mostra un esempio.

Se stai semplicemente controllando i dati, premi ESC per ignorarela formula modificata. In caso contrario, se premi INVIO sostitui-rai la porzione selezionata della formula.

SUGGERIMENTO Utilizzare Valuta formula per risolvere i problemi Puoianche selezionare Strumenti, Verifica formule, Valuta formula per risolverei problemi derivanti dai modelli di cartelle di lavoro.

Figura 1.22 Seleziona parte di una formula e premi F9 per convertirla nel suo valore risultante.


Utilizzare riferimenti circolariUn riferimento circolare è una formula che dipende dal suo stessovalore. Il riferimento circolare più ovvio è una formula che contieneun riferimento alla stessa cella in cui è stata inserita. Per esempio, sedigiti =C1-A1 nella cella A1, Excel visualizza il messaggio di erroremostrato in figura 1.23.

37

Figura 1.23 Questo messaggio di errore appare quando si inserisce una formula che contieneun riferimento circolare.

Se vieni sorpreso dall’avvertimento di un riferimento circolare, que-sto solitamente significa che hai commesso un errore in una formu-la. Fai clic su OK e osserva la formula. Se l’errore non è ovvio, con-trolla le celle alle quali fa riferimento la formula.

Seleziona Visualizza, Barre degli strumenti, Riferimento circolareper riuscire a delineare il problema utilizzando le funzioni di verifi-ca incorporate in Excel. Fai clic su Individua precedenti o Indi-vidua dipendenti per tracciare frecce di controllo sul foglio di lavo-ro. Queste frecce ti mostrano un percorso visivo che comprendetutte le celle interessate, come mostrato in figura 1.24.

Figura 1.24 Visualizza la barra degli strumenti Riferimento circolare per individuare riferimenticircolari indesiderati.

Se fai clic sul pulsante Individua precedenti, Excel disegna unalinea dalla cella che contiene la formula a tutte le celle usate dallaformula. Ogni volta che fai clic, appaiono le linee di individuazioneper il livello successivo, se una delle celle usate dalla formula con-tiene formule.

Funzioni con Excel

È possibile risolvere molti riferimenti circolari. Alcune formule cir-colari sono utili o anche essenziali, per esempio il gruppo di riferi-menti circolari in figura 1.25. Queste formule sono circolari perchéla formula nella cella M29 dipende dal valore nella cella M30 e laformula in quest’ultima cella dipende dal valore della cella M29.

38

Figura 1.25 La formula di sconto della cella M29 è circolare perché dipende dal totale, che asua volta dipende dal valore di sconto nella cella M29.

NOTA Puoi trovare il file di esempio usato in questa sezione,Fattura vendite.xls, sul sito del libro.

Dopo aver ignorato il messaggio di errore mostrato in figura 1.23,la formula non sarà risolta fino a quando permetterai a Excel dieseguire un ricalcolo dei passaggi controllati. A tal fine, selezionaStrumenti, Opzioni, fai clic sulla scheda Calcolo mostrata in figura1.20 e quindi seleziona l’opzione Iterazioni. Excel ricalcola tutte lecelle in tutti i fogli di lavoro aperti che contengono un riferimentocircolare.

Se necessario, il ricalcolo sarà ripetuto per il numero di volte speci-ficato nella casella Numero massimo (100 è l’impostazione predefi-nita). Ogni volta che Excel ricalcola le formule, i risultati nelle celleappaiono più vicini ai valori corretti. Se necessario, Excel continuafino a quando la differenza tra le iterazioni è inferiore al numeroinserito nella casella Scarto consentito (0,001 è l’impostazione pre-definita). Pertanto, usando le impostazioni predefinite, Excel rical-


cola per un numero massimo di 100 volte oppure fino a quando ladifferenza tra i valori durante le interazioni è inferiore a 0,001, aseconda di quale condizione si verifica prima.

Se sulla barra di stato appare la parola Calcola al termine delle itera-zioni, sono possibili ulteriori iterazioni. Puoi accettare il risultatocorrente, aumentare il numero di iterazioni o diminuire il valoreScarto consentito. Excel non ripete il messaggio se la risoluzione delriferimento fallisce. Devi stabilire quando la risposta può essereconsiderata sufficientemente corretta. Excel può eseguire le itera-zioni in pochi secondi, ma in situazioni di riferimenti circolari com-plessi devi impostare l’opzione Calcolo su Manuale; se non lo faces-si, Excel ricalcolerebbe i riferimenti circolari ogni volta che inserisciun dato in una cella. L’utile scenario di riferimento circolare descrit-to in questa sezione è chiamato convergenza: la differenza tra i risul-tati diventa più piccola a ogni calcolo iterativo. Nel processo oppo-sto, chiamato divergenza, la differenza tra i risultati diventa piùgrande a ogni calcolo. Excel continua le iterazioni fino a quando neesegue il numero specificato.

Comprendere la precisione dei valori numericiDi seguito, vengono presi in esame tre interessanti aspetti della pre-cisione numerica in Excel.

■ Excel memorizza numeri con una precisione massima di 15 cifree converte in zero ogni cifra successiva alla quindicesima.

■ Excel ignora ogni cifra successiva alla quindicesima anche per lefrazioni decimali.

■ Inoltre, utilizza la notazione scientifica per visualizzare numeritroppo lunghi per essere contenuti nelle relative celle.

La tabella 1.3 contiene esempi di come Excel tratta frazioni decima-li e interi più lunghi di 15 cifre, quando vengono inseriti nelle celleaventi una larghezza predefinita di colonna di 8,43 caratteri.

39

Funzioni con Excel

Tabella 1.3 Esempi di precisione numerica

Inserimento Valore visualizzato Valore memorizzato123456789012345678 1,23457E+17 123456789012345000

1.23456789012345678 1,234568 1,23456789012345

1234567890.12345678 1234567890 1234567890,12345

123456789012345.678 1,23457E+14 123456789012345

Excel può calcolare valori positivi elevati come 9,99E+307 e appros-simativamente piccoli come 1,00E-307. Se una formula crea un ri-sultato esterno a questo intervallo, Excel memorizza il numero cometesto e assegna un valore di errore #NUM! alla cella della formula.

SOLUZIONEI valori arrotondati del foglio di lavoro creano somme errate

Il foglio di lavoro può apparire errato se usi valori arrotondati. Peresempio, se utilizzi la formattazione delle celle per visualizzare i numeriin formato valuta con due cifre decimali, il valore 10,006 viene visualiz-zato in forma arrotondata 10,01. Se sommi 10,006 a 10,006, il risultatocorretto è 20,012. Se tutti questi numeri sono in formato valuta, tutta-via, il foglio di lavoro visualizzerà i valori arrotondati 10,01 e 10,01 e ilvalore arrotondato del risultato sarà 20,01. Il risultato è corretto, consi-derando l’arrotondamento, ma il suo aspetto potrebbe risultare inaccet-tabile per alcune finalità specifiche come una presentazione o un’ispe-zione.Puoi correggere questo problema modificando il formato Valuta, oppureselezionando Strumenti, Opzioni e quindi scegliendo l’opzioneApprossimazione come visualizzata dalla scheda Calcolo. In ogni caso,devi selezionare questa opzione con estrema cautela perché convertepermanentemente i valori sottostanti del foglio di lavoro nei valorivisualizzati. Per esempio, se una cella contenente il valore 10,006 èformattata come valuta, selezionando l’opzione Approssimazione comevisualizzata, modifichi in modo permanente il valore in 10,01.

■ Usare le matriciLe matrici sono concetti familiari ai programmatori. Per definizione,una matrice è un insieme di elementi. Excel è una delle poche appli-cazioni che semplifica le operazioni con le matrici, in cui gli elemen-ti che compongono la matrice possono essere indirizzati in gruppo o

40


singolarmente in semplici termini matematici. Quella che segue è laterminologia di base delle matrici che devi conoscere:

■ Una formula in forma di matrice agisce su due o più gruppi divalori, chiamati argomenti della matrice, per restituire uno o piùrisultati.

■ Un intervallo di matrice è un blocco di celle che condivide unacomune formula in forma di matrice.

■ Una costante di matrice è un elenco di valori costanti organizzatoin modo particolare, che puoi utilizzare come argomenti nelleformule in forma di matrice.

Le matrici eseguono calcoli in modo diverso da qualsiasi altro stru-mento. Possono essere usate per la protezione del foglio di lavoro,per le tabelle di regressione lineare e così via.

Matrici monodimensionaliIl modo più semplice per comprendere e conoscere le matrici è os-servare esempi pratici. Per esempio, puoi calcolare le medie mostra-te in figura 1.26 inserendo una singola formula in forma di matrice.

41

Figura 1.26 Abbiamo inserito una formula in forma di matrice nell’intervallo selezionato F4:F8.

Questo esempio particolare può essere usato per proteggere le for-mule da eventuali alterazioni, poiché è impossibile modificare sin-gole formule in celle facenti parte di una matrice. Per inserire que-sta formula, esegui i passaggi che seguono:

Funzioni con Excel

1 Seleziona l’intervallo F4:F8.

2 Digita la formula nella barra della formula, come mostrato infigura 1.26.

3 Premi CTRL+MAIUSC+INVIO.

La formula in forma di matrice risultante è presente in cinque cellecontemporaneamente. Sebbene la formula in forma di matrice possasembrare composta da cinque formule separate, non puoi apportarealcuna modifica alle formule senza selezionare l’intera formula, valea dire l’intero intervallo F4:F8.

Regole per le formule in forma di matricePer inserire una formula in forma di matrice, seleziona prima lacella o l’intervallo che conterrà i risultati. Se la formula produce piùrisultati, devi selezionare un intervallo che abbia la stessa dimensio-ne e forma dell’intervallo o degli intervalli sui quali esegui i calcoli.

Segui queste linee guida quando inserisci e lavori con le formule informa di matrice:

■ Premi CTRL+MAIUSC+INVIO per bloccare una formula informa di matrice. Excel posizionerà quindi una serie di parentesigraffe intorno alla formula nella barra della formula, per indicareche si tratta di una formula in forma di matrice. Non digitare leparentesi graffe: se lo fai Excel interpreta l’inserimento comeun’etichetta.

■ Non puoi modificare, cancellare o spostare singole celle in unintervallo di matrice, né inserire o eliminare celle. Devi gestire lecelle di un intervallo di matrice come una singola unità e quindimodificarle tutte contemporaneamente.

■ Per modificare una matrice, selezionala completamente, fai clicsulla barra della formula e modifica la formula. PremiCTRL+MAIUSC+INVIO per bloccare la formula.

■ Per cancellare una matrice, seleziona l’intera matrice e premiCANC.

■ Per selezionare un’intera matrice, fai clic su qualsiasi cella nellamatrice e premi CTRL+MAIUSC+/.

■ Per spostare un intervallo di matrice, devi selezionare l’interamatrice e tagliare e incollare la selezione o trascinarla in unanuova posizione.

42


■ Non puoi tagliare, cancellare o modificare parte di una matrice,ma puoi assegnare formati diversi a singole celle nella matrice.Puoi anche copiare celle da un intervallo di matrice e incollarlein un’altra area del foglio di lavoro.

Matrici bidimensionaliNell’esempio precedente, la formula in forma di matrice generavauna matrice monodimensionale verticale. Puoi anche creare matriciche comprendono due o più colonne e righe, conosciute anchecome matrici bidimensionali. In figura 1.27 è mostrato un esempio.

43

Figura 1.27 Abbiamo usato una formula in forma di matrice bidimensionale nell’intervalloB10:E14 per calcolare il rango di ogni punteggio dell’esame. Nell’intervallo F10:F14 appare unamatrice monodimensionale simile.

Per inserire una matrice bidimensionale, esegui queste operazioni:

1 Seleziona un intervallo per contenere la matrice, che abbia lastessa dimensione e forma dell’intervallo con cui vuoi lavorare.

2 Digita la formula nella barra della formula e premiCTRL+MAIUSC+INVIO.

NOTA Sfortunatamente, non puoi creare matrici tridimensionali in più foglidi lavoro della cartella.

Formule in forma di matrice per celle singolePuoi eseguire calcoli su una vasta gamma di valori all’interno di unasingola cella, usando una formula in forma di matrice che producaun singolo valore come risultato. Per esempio, per contare il nume-ro di valori di errore in un intervallo di celle, puoi creare una for-mula in forma di matrice in una sola cella, come mostrato in figura1.28.

Funzioni con Excel

Figura 1.28 Abbiamo usato una formula in forma di matrice in una cella singola (A3) per conta-re i valori di errore che appaiono nell’intervallo B4:N38.

Nell’esempio mostrato in figura 1.28, la formula può essere inseritacome formula normale (premendo INVIO) o come una formula informa di matrice (premendo CTRL+MAIUSC+INVIO). In questocaso, l’utilizzo di una formula in forma di matrice fa la differenza.Indipendentemente da quanti errori appaiono nel foglio di lavoro,la versione non in forma di matrice della formula restituisce il valore1, perché la funzione SOMMA ha essenzialmente un solo argomen-to: il risultato della singola funzione VAL.ERRORE. Se inserisci lastessa formula in forma di matrice, tuttavia, la formula restituisce ilnumero totale di errori perché la funzione SOMMA vede una matri-ce di singole funzioni VAL.ERRORE invece che una sola. Per infor-mazioni sulla funzione VAL.ERRORE, vedi “Utilizzare le funzioniinformative VAL” a pagina 87.

Applicazione delle formule in forma di matrice per cellesingoleSupponi di voler calcolare il numero totale di elementi in una tabellache soddisfano due criteri. Vuoi sapere quante transazioni superiori a1.000 si sono verificate dopo una determinata data. Puoi aggiungereuna colonna alla tabella, contenente una funzione SE, per trovare tuttele transazioni che soddisfano questi criteri e quindi ottenere il totale deirisultati di tale colonna. Un modo più semplice per eseguire questaoperazione è usare una formula in forma di matrice singola simile allaseguente: =SOMMA((A1:A100>37266)*(C1:C100>999)).Il numero 37266 della formula è il valore seriale della data 10/1/2002.Inserisci la formula premendo CTRL+MAIUSC+INVIO. Ogni elementonella prima espressione tra parentesi valuta un valore 1 (VERO) o unvalore 0 (FALSO), in base alla data; anche ciascun elemento nellaseconda espressione tra parentesi valuta un valore 1 o 0, verificandose il valore è superiore a 999. La formula quindi moltiplica i valori 1 e 0e, quando entrambi sono VERO, il valore risultante sarà 1. La funzioneSOMMA somma gli 1 e presenta il totale. Puoi inserire ulteriori criteriaggiungendo altri elementi tra parentesi nella formula; qualsiasi

44


espressione considerata falsa (0) elimina la transazione (ogni numeromoltiplicato per 0 restituisce 0).Puoi migliorare questa formula in molti modi. Per esempio, puoi sosti-tuire il numero seriale della data con la funzione DATA.VALORE inmodo da usare 10/1/2002 come argomento, evitando di dover trovare ilvalore della data. Un’alternativa anche migliore è usare riferimenti dicella come argomenti per ciascun elemento, in modo da digitare i crite-ri all’interno delle celle, invece di modificarli nella formula. Per informa-zioni sulla funzione DATA.VALORE, vedi il capitolo 4 “Formattare e cal-colare date e ore”.

Utilizzare costanti di matriceUna costante di matrice è un elenco organizzato in modo particolaredi valori, che puoi utilizzare come argomenti nelle formule in formadi matrice. Le costanti di matrice sono costituite da numeri, testo ovalori logici. Devi inserire una costante matrice tra parentesi graffe esepararne gli elementi con punti e virgola e barre rovesciate. I puntie virgola indicano i valori in colonne separate, mentre le barre rove-sciate indicano valori in righe separate. La formula in figura 1.29,per esempio, esegue nove calcoli in una cella.

45

Figura 1.29 L’argomento di questa formula in forma di matrice è una costante di matrice.

Per inserire una formula utilizzando una costante di matrice, seguiquesti passaggi:

1 Seleziona un intervallo di celle sufficientemente ampio da conte-nere il risultato. In figura 1.29, l’argomento della matrice INTcontiene tre gruppi (separati da barre rovesciate) di tre valori(separati da punti e virgola), che creano un intervallo formato datre righe e tre colonne.

2 Digita le parentesi intorno all’argomento, per indicare che ivalori inclusi formano una costante di matrice. Osserva che gliargomenti sono diversi dalle formule di matrice, in cui non puoidigitare le parentesi graffe.

3 Premi CTRL+MAIUSC+INVIO. La formula in forma di matri-ce risultante contiene due gruppi di parentesi graffe: uno com-

Funzioni con Excel

prende la costante di matrice e l’altro l’intera formula in forma dimatrice.

Quando inserisci costanti di matrice, ricorda che i punti e virgolepresenti tra i due elementi della matrice posizionano quegli elementiin colonne separate e che le barre rovesciate, invece, li inserisconoin righe separate.

Comprendere l’espansione delle matriciQuando usi le matrici come argomenti in una formula, tutte lematrici devono avere le stesse dimensioni. Se le dimensioni degliargomenti o degli intervalli della matrice non corrispondono, Excelsolitamente espande gli argomenti. Per esempio, per moltiplicaretutti i valori presenti nelle celle A1:B5 per il valore 10, puoi usare leseguenti formule in forma di matrice: {=A1:B5*10} oppure{={1;2\3;4\5;6\7;8\9;10}*10}.

Osserva che queste due formule non sono bilanciate; ci sono diecivalori a sinistra dell’operatore di moltiplicazione e solo uno sullaparte destra. Excel espande il secondo argomento per adattarlo alladimensione e alla forma del primo. Nell’esempio precedente, le dueformule erano {=A1:B5*{10;10\10;10\10;10\10;10\10;10}} e{={1;2\3;4\5;6\7;8\9;10}*{10;10\10;10\10;10\10;10\10;10}}.

Quando lavori con due o più gruppi di matrici multivalore, ciascungruppo deve avere lo stesso numero di righe dell’argomento con ilmaggior numero di righe e lo stesso numero di colonne dell’argo-mento avente il maggior numero di colonne.

■ Collegare cartelle di lavoroLa creazione di collegamenti dinamici tra cartelle di lavoro conte-nenti formule con riferimenti esterni ti offre una serie di vantaggi.Per esempio, puoi dividere un complesso modello di budget azien-dale in diversi modelli sfruttabili dai vari reparti. Puoi quindi colle-gare tutte le cartelle di lavoro dei reparti (cartelle di supporto) a unacartella principale (cartella di lavoro dipendente). Oltre a crearemodelli più gestibili e flessibili, le cartelle di lavoro collegate posso-no risparmiare tempo di ricalcolo e memoria.

Questa sezione affronta alcune considerazioni speciali di cui deviessere consapevole quando lavori con le cartelle di lavoro collegateda formule con riferimenti esterni. Per ulteriori informazioni suiriferimenti esterni, vedi “Creare riferimenti ad altri fogli di lavoro

46


nella stessa cartella di lavoro” a pagina 5 e “Creare riferimenti afogli di lavoro in altre cartelle di lavoro” a pagina 6.

Salvare cartelle di lavoro collegateQuando crei una serie di cartelle di lavoro collegate, devi salvare lecartelle di supporto prima di quelle dipendenti. Per esempio, sup-poni di voler creare un modello per il budget 2004 della tua aziendain una cartella di lavoro non salvata chiamata Cartel1. Quando salvila cartella di lavoro, assegnale il nome Budget 2004.

Supponi ora di avere un’altra cartella di lavoro attiva nella quale haipianificato di inserire le spese effettive (in opposizione a quelle pre-ventivate); hai già salvato la cartella di lavoro con il nome Effettivo2004. Questa cartella di lavoro contiene collegamenti alla cartella dilavoro Budget 2004 per alcune informazioni. Quando hai creato ini-zialmente questi collegamenti, la cartella di lavoro Budget 2004 eraidentificata con il nome Cartel1.

Se salvi Cartel1 con il nome Budget 2004 mentre la cartella Effettivo2004 è ancora aperta, tutti i riferimenti a Cartel1 nella cartella dilavoro Effettivo 2004 sono automaticamente convertiti in Budget2004. Per esempio, se Effettivo 2004 contiene il riferimento=[Cartel1]Foglio1!$A$1, il riferimento diventa =’[Budget2004.xls]Foglio1’!$A$1.

Se cerchi di chiudere la cartella di lavoro dipendente Effettivo 2004prima di salvare la cartella di supporto Cartel1 (Budget 2004), appa-re il messaggio di avviso Salvare ‘Effettivo 2004.xls’ con riferimentia documenti non salvati? Fai clic su OK per salvare e chiudere.Quando poi salvi Cartel1 come Budget 2004, i riferimenti a Cartel1nella cartella Effettivo 2004 non vengono aggiornati perché nonsono aperti; le formule continuano a fare riferimento a Cartel1.Quando apri nuovamente Effettivo 2004, Excel visualizza una fine-stra di messaggio che notifica che la cartella di lavoro contiene colle-gamenti a un’altra cartella e suggerisce di aggiornare le informazionicollegate. Se fai clic su Sì, Excel ovviamente non è in grado di trova-re Cartel1. Devi trovare la cartella di lavoro Budget 2004 in modoche Excel possa ristabilire i collegamenti.

Aprire una cartella di lavoro dipendenteQuando salvi una cartella di lavoro che contiene formule dipenden-ti, Excel memorizza i risultati più recenti di quelle formule. Se apri emodifichi la cartella di lavoro di supporto dopo aver chiuso la car-

47

Funzioni con Excel

tella di lavoro dipendente, i valori delle celle modificate nella cartel-la di lavoro di supporto potrebbero essere diversi. Quando aprinuovamente la cartella di lavoro dipendente, questa contiene i vec-chi valori dei riferimenti esterni nelle formule dipendenti; tuttavia,Excel visualizza una finestra di avviso con il messaggio “La cartelladi lavoro contiene collegamenti ad altre origini dei dati”. La finestradi avviso ti consente di comunicare a Excel se leggere o meno i valo-ri correnti dalla cartella su disco chiusa.

Se fai clic su Non aggiornare, Excel apre la cartella di lavoro dipen-dente senza aggiornare i riferimenti alla cartella di lavoro di suppor-to. Tutte le formule dipendenti conservano gli ultimi valori salvati.

Se fai clic su Aggiorna, Excel cerca la cartella di lavoro di supporto.Se la trova, Excel legge i valori di supporto e aggiorna le formuledipendenti nella cartella di lavoro dipendente. Excel non apre lacartella di lavoro di supporto; legge semplicemente i valori appro-priati da essa.

Se Excel non riesce a trovare la cartella di lavoro di supporto, visua-lizza il messaggio di avviso “La cartella di lavoro contiene uno o piùcollegamenti che non possono essere aggiornati”. Puoi fare clic suContinua per aprire comunque la cartella di lavoro, oppure puoifare clic su Modifica collegamenti per identificare, nella finestra didialogo Modifica collegamenti, un nuovo file di supporto da usare.

Modificare collegamentiPuoi aprire le cartelle di lavoro di supporto, nonché specificare unadiversa cartella di lavoro di supporto, selezionando Modifica,Collegamenti; il comando è disponibile solo quando esiste un riferi-mento esterno nella cartella di lavoro attiva. Per esempio, la finestradi dialogo mostrata in figura 1.30 visualizza un collegamento a unacartella di lavoro di supporto.

Quelle che seguono sono alcune informazioni utili sull’utilizzo dellafinestra di dialogo Modifica collegamenti:

■ Una lettera A nella colonna Aggiorna indica un collegamento cheè stato aggiornato automaticamente.

■ Una lettera M nella colonna Aggiorna indica un collegamentomanuale che non viene aggiornato se non fai clic sul pulsanteAggiorna valori.

■ Fai clic su Apri origine per aprire la cartella di lavoro di supporto.

48


Figura 1.30 Usa la finestra di dialogo Modifica collegamenti per individuare velocemente tuttele cartelle di lavoro di supporto.

■ Fai clic su Cambia origine per selezionare una diversa cartella dilavoro di supporto.

■ Fai clic su Interrompi collegamento per convertire tutti i riferi-menti esterni esistenti nelle formule nei loro valori correnti. Nonpuoi annullare questa operazione, pertanto fai clic su Interrompicollegamento con attenzione.

■ Fai clic su Aggiorna valori per prelevare le ultime cifre dalla car-tella di lavoro di supporto senza doverla aprire.

■ Puoi collegare oggetti e documenti creati in altre applicazioni,quali Microsoft Word, ai fogli di lavoro e grafici di Excel.Quando esegui questa operazione, la colonna Tipo visualizza ilnome dell’applicazione e il tipo di oggetto.

APPROFONDIMENTOFai attenzione quando sposti i documenti

Normalmente devi prestare attenzione quando sposti documenti ai qualihai creato collegamenti in altre cartelle di lavoro. Questo non è vero, tut-tavia, se utilizzi Windows XP, Windows 2000 o Windows NT. Per esem-pio, se crei un collegamento a un testo in un documento Word e poimodifichi il nome o la posizione di quel collegamento, Excel ne trovaancora l’origine e aggiorna il collegamento, se l’origine è memorizzatasu un file system NTFS (il file system di Windows NT/2000/XP). Riesci aeseguire questa operazione perché NTFS mantiene un file di registroche Excel può consultare. Se usi i collegamenti ipertestuali di Excel, tut-tavia, la modifica della posizione del file di origine interrompe il collega-mento, anche se il file si trova su un volume NTFS. Se sposti il docu-mento di or igine in un volume FAT32 (il f i le system di MS-DOS/Windows), tuttavia, Excel non può trovare il documento.

49

Funzioni con Excel

Copiare, tagliare e incollare nelle cartelle di lavoro collegatePuoi usare riferimenti relativi e assoluti alle celle in altre cartelle dilavoro, come per una cartella singola. I riferimenti relativi e assolutialle celle nelle cartelle di lavoro di supporto rispondono ai comandiCopia, Taglia e Incolla e ai pulsanti sulla barra degli strumenti,come i riferimenti alle celle nella stessa cartella di lavoro.

Per esempio, supponi di creare la formula =[Modulo2]Foglio1!F1nella cella A1 di Foglio1 del file Modulo1; quindi usa Copia eIncolla per copiare questa formula nella cella B1. La formula nellacella B1 diventa =[Modulo2]Foglio1!G1. La formula originale ècambiata quando è stata copiata nella cella B1 perché il riferimentoalla cella F1 è relativo. In ogni caso, se la formula nella cella A1 diModulo1 conteneva un riferimento assoluto, come =[Modulo2]Foglio1!$F$1, il riferimento nella formula copiata non sarebbe cam-biato.

Copiare e incollare tra cartelle di lavoroQuando copi una formula dipendente da una cartella di lavoro aun’altra e tale formula include un riferimento relativo a una terzacartella di lavoro, il riferimento viene adattato per riflettere la nuovaposizione della formula. Per esempio, supponi che la cella A1 inModulo1 contenga la formula =[Modulo2]Foglio1!A1. Se copi eincolli la formula nella cella B5 di Modulo3, il risultato è la formula=[Modulo2]Foglio1!B5. La formula è regolata per riflettere lanuova posizione relativa.

Se copi una formula che contiene un riferimento assoluto a un’altracartella di lavoro, la formula rimane la stessa. Per esempio, supponiche la cella A1 in Modulo1 contenga la formula =[Modulo2]Foglio1!$A$1. Se copi e incolli la formula nella cella B5 diModulo3, la formula risultante è la stessa.

Anche se copi una formula dipendente nella cartella di lavoro allaquale la formula fa riferimento, rimane ancora una formula dipen-dente. Per esempio, se copi la formula =[Modello2]Foglio1!$A$1dalla cella A1 del Modello1 alla cella A3 sul Foglio1 del Modello2,la formula risultante essenzialmente è la stessa, con l’eccezione che ilriferimento alla cartella non è necessario perché la formula si trovanella stessa cartella di lavoro. Di conseguenza, la formula diventa=Foglio1!$A$1.

50


Tagliare e incollare tra cartelle di lavoroExcel non adatta i riferimenti relativi in una formula quando la taglida una cartella di lavoro e la incolli in un’altra, come accade quandocopi una formula. Per esempio, supponi che la cella A1 in Foglio1del file Modulo1 contenga la formula =[Modulo2]Foglio1!A1. Setagli la formula e la incolli nella cella B5 di Modulo3, la formula noncambia.

Tagliare e incollare celle cui fanno riferimento formule dipendentiQuando tagli e incolli celle, Excel normalmente aggiorna tutti i rife-rimenti alle celle nelle formule della cartella di lavoro. Le formuledipendenti, tuttavia, non seguono le stesse regole. Quando tagli eincolli una cella alla quale fa riferimento una formula dipendente inuna cartella di lavoro chiusa, tale formula non viene aggiornata perriflettere il cambiamento.

Per esempio, supponi di creare la formula =[Modulo2]Foglio1!A10nella cella A1 di Modulo1. Se chiudi Modulo1 e utilizzi Copia eIncolla per spostare l’inserimento alla cella B10 di Modulo2, la for-mula nella cella A1 di Modulo1 rimane la stessa. Puoi aspettarti cheil collegamento venga interrotto, dato che il foglio di lavoro conte-nente la formula era chiuso quando hai modificato la cella di riferi-mento. In ogni caso, Excel cerca di tenere traccia di tutto. Quandoapri una cartella di lavoro, il messaggio “La cartella di lavoro contie-ne collegamenti ad altre origini dei dati” ti avvisa che i dati dai qualidipende la cartella di lavoro sono stati modificati.

■ Creare test condizionaliUn test condizionale confronta due numeri, funzioni, formule, eti-chette o valori logici. I test condizionali possono essere usati percontrassegnare valori inferiori o superiori a una certa soglia, peresempio. Puoi usare semplici operatori logici e matematici percostruire formule logiche, oppure puoi usare un assortimento difunzioni incorporate. Per informazioni sull’utilizzo delle funzioniper test condizionali, vedi “Comprendere le funzioni logiche” apagina 83.

Tutte le formule seguenti eseguono test condizionali rudimentali:=A1>A2=5-3<5*2=MEDIA(B1:B6)=SOMMA(6;7;8)

51

Funzioni con Excel

=C2=”Femmina”=CONTA.NUMERI(A1:A10)=CONTA.NUMERI(B1:B10)=LUNGHEZZA(A1)=10

Ogni test condizionale deve includere almeno un operatore logico,che definisce la relazione tra gli elementi del test. Per esempio, neltest condizionale A1>A2, l’operatore logico maggiore di (>)con-fronta i valori nelle celle A1 e A2. La tabella 1.4 elenca i sei operato-ri logici.

Tabella 1.4 Operatori logici

Operatore Definizione= Uguale a

> Maggiore di

< Minore di

> = Maggiore o uguale a

< = Minore o uguale a

< > Diverso da

Il risultato di un test condizionale è il valore logico VERO (1) o ilvalore logico FALSO (0). Per esempio, il test condizionale =A1=10restituisce VERO se il valore nella cella A1 è uguale a 10 oppureFALSO se la cella A1 contiene qualsiasi altro valore.

Usare Creazione guidata Somma condizionale e Ricerca guidataExcel include due utili creazioni guidate che ti aiutano a raggrupparei tipi di formule che usi più frequentemente. Creazione guidata Som-ma condizionale e Ricerca guidata sono fornite come componentiaggiuntivi, vale a dire speciali tipi di macro progettati per essere inte-grati in Excel. Per vedere se hai installato queste procedure, apri ilmenu Strumenti. Se trovi i comandi Somma condizionale o Ricerca,significa che sono installate anche le rispettive creazioni guidate.

Se tali comandi non sono disponibili, scegli Strumenti, Componentiaggiuntivi. Se Creazione guidata Somma condizionale e Ricerca gui-data sono presenti nell’elenco dei componenti aggiuntivi disponibili,selezionali entrambi (oppure seleziona qualsiasi altro componentedesideri) e fai clic su OK per installarli. Se nessuno dei due compo-nenti aggiuntivi è presente nell’elenco, devi avviare il programma diinstallazione.

52


Creare formule di somma condizionaleCreazione guidata Somma condizionale crea formule utilizzando lefunzioni SOMMA e SE. Questa creazione guidata non solo facilitala costruzione di queste formule, ma mostra anche come vengonogenerate queste formule in modo che tu possa creare formule condi-zionali personali senza la creazione guidata.

Per costruire una formula condizionale:

1 Seleziona la tabella o l’elenco contenente i valori che vuoi usare eseleziona Strumenti, Creazione guidata, Somma condizionale pervisualizzare la finestra di dialogo mostrata in figura 1.31.

53

Figura 1.31 Creazione guidata Somma condizionale ti aiuta a costruire formule SOMMA cherichiedono una selezione degli elementi da sommare.

Se fai clic in un punto qualsiasi della tabella prima di avviare lacreazione guidata, Excel seleziona automaticamente l’area daticorrente. Se Excel seleziona l’area dati corretta, fai clic suAvanti. In caso contrario, trascina il mouse per selezionare l’in-tervallo desiderato. Ricorda di includere le etichette di riga e dicolonna. Dopo aver fatto clic su Avanti, viene aperta la finestradi dialogo mostrata a sinistra, in figura 1.32.

Funzioni con Excel

Figura 1.32 Questi passaggi della procedura guidata consentono di selezionare le celle daincludere nel calcolo.

2 Nella casella di riepilogo Colonna da sommare, seleziona il nomedella colonna dalla quale vuoi estrarre i totali.

È per questo motivo che devi selezionare le etichette nella fine-stra di dialogo del passaggio 1. Se queste non appaiono nell’elen-co, fai clic su Indietro e seleziona nuovamente l’intervallo.

NOTA Puoi trovare il file di esempio usato in questa sezione,Transazioni vendite Paganini.xls, sul sito del libro.

3 Sempre nel passaggio 2 della procedura guidata, indica la condi-zione da usare nella selezione dei valori da includere nel totale.Nella casella di riepilogo Colonna, seleziona il nome della colon-na contenente le etichette che desideri controllare in modo con-dizionale, seleziona un operatore nella casella di riepilogoOperatore e quindi un valore dalla casella di riepilogo Valore.

I contenuti dell’elenco Valore cambiano in base alla colonna sele-zionata nella casella di riepilogo Colonna. La casella di riepilogoValore visualizza solo i valori univoci presenti nella colonna sele-zionata, ignorando i duplicati.

4 Fai clic su Aggiungi condizione.

I criteri che hai specificato vengono aggiunti all’elenco nellaparte inferiore della finestra di dialogo. Puoi aggiungere fino asette condizioni. Se cambi idea su qualche condizione, seleziona-la dall’elenco e fai clic su Rimuovi condizione. Dopo aver termi-nato l’eliminazione delle condizioni, fai clic su Avanti.

54


5 Nel Passaggio 3 della creazione guidata, seleziona l’opzioneCopia la formula in una cella singola oppure Copia la formula e ivalori condizionali.

6 Fai clic su Avanti e seleziona la cella in cui vuoi posizionare laformula risultante.

Se nel passaggio 3 hai selezionato l’opzione Copia la formula e ivalori condizionali, la creazione guidata aggiunge un ulteriorepassaggio. Seleziona la cella in cui desideri inserire il valore con-dizionale. Quindi fai clic su Avanti, poi seleziona la cella in cuidesideri inserire la formula.

7 Fai clic su Fine. La formula risultante (e il valore condizionaleopzionale) viene incollata nel foglio di lavoro nella posizione spe-cificata.

APPROFONDIMENTOPrestare attenzione durante la creazione guidata

In molti casi, nel primo passaggio della Creazione guidata Somma con-dizionale (vedi figura 1.31), Excel seleziona automaticamente un inter-vallo di celle o una tabella. Questa opzione non sempre funziona comedovrebbe. Nel foglio di lavoro di esempio mostrato in figura 1.31, Excelha selezionato automaticamente l’intervallo A1:C20, che include unacella unita nella parte superiore; questo tipo di operazione non è con-sentita. Abbiamo quindi dovuto selezionare l’intervallo corretto prima diprocedere. Excel dovrebbe riconoscere questo tipo di errore prima diselezionarlo automaticamente, ma non lo fa.

Puoi aggiungere altre formule condizionali oppure, se hai già unelenco di valori unici (come i nomi dei venditori), puoi copiare laformula in maniera adeguata (ma solo se hai usato l’opzione Copiala formula e i valori condizionali nel passaggio 3 della creazione gui-data), come mostrato in figura 1.33.

APPROFONDIMENTOAttenzione agli spazi

Creazione guidata Somma condizionale non è una funzione intelligenteriguardo agli spazi. Per esempio, se un’etichetta nella colonna dei crite-ri include un carattere di spazio invisibile alla fine della stringa di testo,Excel lo esclude dal totale anche se tutte le istanze sono identiche.

55

Funzioni con Excel

Figura 1.33 Abbiamo aggiunto un elenco di nomi unici di venditori (condizioni) nella colonna E,quindi abbiamo copiato la formula della somma nelle celle F5:F9.

Creare formule di ricercaRicerca guidata crea formule utilizzando le funzioni INDICE eCONFRONTA. Come Creazione guidata Somma condizionale,questa procedura facilita la costruzione di formule di ricerca, oltre aillustrare il metodo di creazione delle formule stesse per imparare acrearne personalizzate. Per ulteriori informazioni sulle funzioniINDICE e CONFRONTA, vedi “Comprendere le funzioni di ricer-ca e riferimento” a pagina 89. Per costruire una formula di ricerca:

1 Seleziona Strumenti, Creazione guidata, Ricerca per visualizzarela finestra di dialogo mostrata a sinistra in figura 1.34.

56

Figura 1.34 Indica l’intervallo di ricerca e la riga e la colonna da trovare utilizzando i primi duepassaggi di Ricerca guidata.


NOTA La formula risultante mostrata nella barra della formula in figura1.33 è racchiusa tra due parentesi graffe, { e }, per indicare una formula informa di matrice. Per ulteriori informazioni sulle matrici, vedi “Usare lematrici” a pagina 40.

2 Seleziona la tabella o l’elenco contenente i valori che vuoi usare.Se fai clic in un punto qualsiasi della tabella prima di avviare lacreazione guidata, Excel seleziona automaticamente l’area daticorrente. Se l’area dati selezionata da Excel è corretta, fai clic suAvanti; in caso contrario trascina il mouse per selezionare l’inter-vallo da usare. Ricorda di includere le etichette di riga e di colon-na. La finestra di dialogo appare come mostrato a destra in figu-ra 1.34.

3 Seleziona il nome della colonna contenente il valore desideratodalla casella di riepilogo Selezionare l’etichetta di colonna. È perquesto motivo che devi selezionare le etichette nella finestra didialogo del passaggio 1. Se queste non appaiono nell’elenco, faiclic su Indietro e seleziona nuovamente l’intervallo.

4 Decidi se i parametri di ricerca e il risultato dovranno essereinseriti nel foglio di lavoro, come mostrato in figura 1.35. È con-sigliabile inserire parametri (condizioni), come mostreremo piùavanti. Seleziona l’opzione Copia la formula e i parametri diricerca e fai clic su Avanti.

57

Figura 1.35 Copiando la formula in una cella singola, i parametri sono fissi; copiando sia laformula sia i parametri di ricerca, è possibile creare una tabella di ricerca.

5 Seleziona la cella in cui desideri posizionare la formula risultan-te. Se nel passaggio 3 hai selezionato l’opzione Copia la formulae i parametri di ricerca, la creazione guidata aggiunge due ulte-

Funzioni con Excel

riori passaggi. In questo caso, seleziona la cella in cui desideriinserire il primo parametro, fai clic su Avanti e quindi su un’altracella per il secondo parametro. Quindi fai clic su Avanti, poi sele-ziona la cella in cui desideri inserire la formula condizionale.

6 Fai clic su Fine.

La figura 1.36 mostra un esempio di come puoi usare Ricerca guida-ta per costruire una tabella di ricerca.

58

Figura 1.36 Puoi inserire diversi mesi e numeri di prodotti per modificare il valore corrispon-dente nella cella R5.

Come affermato in precedenza, quando selezioni l’opzione Copia laformula e i parametri di ricerca nel passaggio 3 di Ricerca guidata, iparametri sono inseriti nel foglio di lavoro; nel nostro esempio,abbiamo specificato le celle P5 e Q5. La formula di ricerca risultan-te (nella cella R5) fa riferimento ai valori inseriti utilizzando riferi-menti relativi. Come puoi vedere nella barra della formula in figura1.36, i primi argomenti delle funzioni CONFRONTA sono riferi-menti relativi alle celle specifiche.

In questo modo, puoi eseguire due operazioni. Puoi digitare altriparametri validi (Set, Prodotto 12 o entrambi, per esempio) nellecelle dei parametri (P5 e Q5): la formula di ricerca troverà il valorecorrispondente alla nuova intersezione. Inoltre, poiché i riferimentidei parametri sono relativi, puoi copiare la formula in altre celle edigitare parametri aggiuntivi nelle celle nelle stesse posizioni relative.

Costruire formule - mdamato.altervista.orgmdamato.altervista.org/File/Excel - Costruzione...

Documents

Transcript of Costruire formule - mdamato.altervista.orgmdamato.altervista.org/File/Excel - Costruzione...