Primi passi con Excel

 

---

Primi passi con Excel formule macro e tanto altro

 

Questo sito utilizza cookie, anche di terze parti. Se vuoi saperne di più leggi la nostra Cookie Policy. Scorrendo questa pagina o cliccando qualunque suo elemento acconsenti all’uso dei cookie.I testi seguenti sono di proprietà dei rispettivi autori che ringraziamo per l'opportunità che ci danno di far conoscere gratuitamente a studenti , docenti e agli utenti del web i loro testi per sole finalità illustrative didattiche e scientifiche.

 

 

Excel

Cartelle e fogli di lavoro
I file di Excel vengono chiamati anche Cartelle di lavoro (creando purtroppo confusione ai neofiti, in quanto vengono chiamati "Cartelle" anche i "contenitori" di file a livello di Sistema Operativo).

Una Cartella di lavoro Excel può contenere uno o più Fogli di lavoro, detti anche Fogli di calcolo. Questi sono i documenti principali utilizzati in Microsoft Excel per memorizzare, elaborare ed analizzare dati, sia numerici che alfabetici, tracciare grafici, ecc.
Un foglio di lavoro si presenta come un foglio di registro contabile, a forma di tabella o matrice, costituito da tante celle disposte in righe e colonne.
È possibile immettere e modificare dati su diversi fogli di lavoro contemporaneamente ed eseguire calcoli basati su dati provenienti da più fogli di lavoro.

Cartella di lavoro
Una cartella di lavoro è il file in cui si elaborano e si memorizzano i dati Excel ed è caratterizzato dall'estensione ".xls". Poiché ciascuna cartella di lavoro può contiene molti fogli, in un singolo file sarà possibile organizzare vari tipi di informazioni tra loro correlate.

Foglio di lavoro
E' il documento principale utilizzato in Microsoft Excel per memorizzare ed elaborare dati. Un foglio di lavoro è costituito da celle disposte in righe e colonne e fa sempre parte di una cartella di lavoro. È anche denominato foglio di calcolo.
I nomi dei fogli vengono visualizzati sulle schede poste nella parte inferiore della finestra della cartella di lavoro. Per spostarsi da un foglio all'altro fare clic sui nomi dei fogli. Per operare sui fogli di lavoro (eliminarli, inserirne di nuovi, rinominarli, ecc.) fare clic col tasto destro del mouse in basso sul nome del foglio.

Foglio attivo
E' il foglio di una cartella di lavoro su cui si sta lavorando. Il nome della scheda del foglio attivo viene visualizzato in grassetto.

Foglio grafico
Un foglio di una cartella di lavoro contenente solo un grafico. I fogli grafici sono collegati ai dati del foglio di lavoro e vengono aggiornati in seguito alla modifica dei dati di quest'ultimo. In alternativa i grafici possono essere inseriti nel foglio attivo come oggetti.

Cella
È l'elemento fondamentale del foglio. Ogni operazione fa sempre riferimento ad una cella. La cella viene individuata dall'incrocio di una riga (numerata da 1 a 65536) con una colonna (ce ne sono 256, individuate da una o due lettere dell'alfabeto: A, B,...Z, AA, AB,...AZ, BA, BB,...IU, IV).
Le celle possono essere celle di input e contenere quindi delle costanti (di norma numeri o testi) oppure celle di output e contenere delle formule (caratterizzate dal fatto di iniziare con il segno "="), che visualizzano il risultato delle operazioni in esse indicate.

Nella figura 1 sono riportate alcune definizioni degli elementi fondamentali di una videata di Excel.

Figura 1:        Tipica schermata iniziale di Excel.

 

 

 

 

Selezione di celle ed intervalli
Per selezionare una cella e renderla attiva, cioè consentire di modificare il suo contenuto, basta cliccare su di essa con il mouse o selezionarla da tastiera facendo uso dei tasti di spostamento orizzontale e verticale.

Per selezionare una intera riga (o una intera colonna) fare clic sul numero della riga (o colonna) corrispondente.

Per selezionare un intervallo (un'area rettangolare di celle) fare clic sulla cella in alto a sinistra dell'intevallo e trascinare il mouse (tenendo premuto il tasto sinistro) fino a raggiungere la cella diametralmente opposta. Un intervallo viene definito dagli indirizzi della sua cella in alto a sinistra (che resta sempre bianca!) e della cella in basso a destra, separati da ":" (Es. B5:H12).

Importante!. Visto che spesso, a causa della velocità degli attuali microprocessori (e spesso della inaffidabilità dei mouse…), si presentano delle difficoltà nel raggiungere e fermarsi sulla seconda cella della selezione, si consiglia vivamente di imparare quanto prima a selezionare l'intervallo con il seguente procedimento:

1. selezionare (cliccando) la prima cella dell'intervallo (di solito quella in alto a sinistra);
2. portarsi con il mouse sopra la cella diametralmente opposta (eventualmente facendo uso delle barre di scorrimento orizzontale e verticale), senza ancora fare clic con il mouse sul foglio (altrimenti l'operazione non riesce);
3. cliccare sulla cella opposta, tenendo premuto il tasto Maiuscolo o Shift (NON il tasto Blocca Maiuscolo, come fanno molti!!).

Lo stesso procedimento può essere utilizzato anche per selezionare due o più righe o colonne adiacenti, facendo attenzione che il cursore del mouse abbia l'aspetto di una piccola croce bianca e non sia a forma di freccia, né tantomeno a forma di segmento con due frecce contrapposte. (Nel primo caso si otterrebbe uno spostamento del contenuto originale, mentre nel secondo si otterrebbe una modifica delle dimensioni della riga o della colonna).

Spesso risulta utile saper selezionare più celle disgiunte (per così dire "a macchia di leopardo"). Per ottenerlo, fare uso del tasto Ctrl (Control), tenendolo premuto mentre si fanno le diverse selezioni fino a ché non si è terminata l'operazione.

Per selezionare tutto il foglio, cliccare sul pulsante posto nell'angolo in alto a sinistra del foglio stesso, all'intersezione tra le intestazioni di colonna e di riga, oppure usare il comando di menu Modifica Seleziona Tutto.

Fare diversi esercizi sui sistemi di selezione, perché non c'è nulla di più penoso agli esami che assistere a tentativi maldestri e improvvisati.

 

   

Riferimenti di celle ed intervalli
Un riferimento identifica una cella o un intervallo di celle su un foglio di lavoro.
Tramite i riferimenti è possibile utilizzare in una formula i dati contenuti in diversi punti di un foglio di lavoro.
È anche possibile fare riferimento a celle di altri fogli della stessa cartella di lavoro, di altre cartelle di lavoro oppure a dati contenuti in altri programmi. In questo caso, davanti al riferimento di cella deve essere riportata l'indicazione del foglio o del file in cui la cella si trova, seguito da un "!".
(Es. Foglio3!A5, oppure 'C:\NomeCartella\[NomeFile]NomeFoglio'!A1).
I riferimenti a celle di altre cartelle di lavoro sono denominati riferimenti esterni. I riferimenti a dati di altri programmi sono denominati riferimenti remoti.

Lo stile di riferimento predefinito è il cosiddetto stile "A1", che etichetta le colonne con lettere (da A ad IV, per un totale di 256 colonne) e le righe con numeri (da 1 a 65536). L'espressione D50 ad esempio fa riferimento alla cella posta all'intersezione della colonna D con la riga 50. Per fare riferimento a un intervallo di celle, immettere il riferimento alla cella posta nell'angolo superiore sinistro dell'intervallo, un segno di due punti (:) e il riferimento alla cella posta nell'angolo inferiore destro dell'intervallo. Di seguito sono riportati alcuni esempi di riferimenti in stile A1.

Per fare riferimento a	Utilizzare
La cella posta nella colonna A e nella riga 10.........................................	A10
L'intervallo di celle della colonna A compreso tra le righe 10 e 20.........	A10:A20
L'intervallo di celle della riga 15 compreso tra le colonne B ed E..........	B15:E15
L'intervallo dalla cella D5 alla cella K13.................................................	D5:K13
Tutte le celle della riga 5.........................................................................	5:5
Tutte le celle delle righe da 5 a 10...........................................................	5:10
Tutte le celle della colonna H..................................................................	H:H
Tutte le celle delle colonne da H a J........................................................	H:J

 

È anche possibile utilizzare uno stile di riferimento in cui sia le righe sia le colonne sono numerate (stile "R1C1"). Lo stile "R1C1" è utile per il calcolo della posizione delle righe e delle colonne nelle macro ed è utile per mostrare i riferimenti relativi delle celle.

Nello stile R1C1 la posizione di una cella viene indicata da una R seguita da un numero di riga e da una C seguita da un numero di colonna. Il riferimento di cella assoluto R2C4, ad esempio, corrisponde al riferimento assoluto D2 nello stile di riferimento "A1". Se la cella attiva è la D10, il riferimento di cella relativo R[3]C[-1] si riferirà alla cella posta 3 righe in basso e una colonna a sinistra, ovvero alla cella C13.

Di seguito sono riportati esempi di riferimenti in stile R1C1.

 

Riferimento	Significato
R[-2]C..........	Un riferimento relativo alla cella posta nella stessa colonna, due righe più in alto.
R[2]C[2] ......	Un riferimento relativo alla cella posta due righe più in basso e due colonne più a destra.
R2C2............	Un riferimento assoluto alla cella posta nella seconda riga e nella seconda colonna.
R[-1] ............	Un riferimento relativo all'intera riga sovrastante la cella attiva.
R...................	Un riferimento assoluto alla riga corrente.

Per rendere più leggibili le formule, è possibile assegnare dei nomi o etichette alle celle o agli intervalli.
Così ad esempio, nella tabella sottostante, rappresentante un ipotetico bilancio trimestrale, è possibile assegnare a tutto l'intervallo A1:E4 il nome "Bilancio", all'intervallo B2:B4 il nome "Gennaio", all'intervallo B2:E2  il nome "Entrate" e a quello B3:E3 il nome "Uscite", ecc.. Così, nello scrivere la formula delle celle della riga 4 (Saldo), anziché scrivere, ad esempio nella cella B4, la formula:   =B2-B3, si potrà scrivere:   =Entrate-Uscite.

Figura 4:        Esempi di denominazioni di intervalli

	A	B	C	D	E	F	G
1		gennaio	febbraio	marzo	totale
2	Entrate
3	Uscite
4	Saldo
5
6

 

Nota   In base all'impostazione predefinita, i nomi costituiscono dei riferimenti di cella assoluti. Tale concetto verrà ripreso quando parleremo di copia di celle.

Ecco quanto dice in proposito la guida in linea di Excel.

Denominare le celle di una cartella di lavoro
È possibile assegnare un nome a una singola cella o a un intervallo di celle per semplificare la lettura e la comprensione delle formule. Se il foglio di lavoro dispone di etichette di riga e di colonna, sarà possibile farvi direttamente riferimento in una formula oppure utilizzarle come nomi per rappresentare le celle associate.

Denominare una cella o un intervallo di celle

1. Selezionare la cella, l'intervallo di celle oppure le selezioni multiple che si desidera denominare.
2. Fare clic sul pulsante Casella Nome   all'estremità sinistra della barra della formula.
3. Digitare il nome che si desidera assegnare alle celle.
4. Premere INVIO.

Non è possibile modificare il nome di una cella mentre se ne sta modificando il contenuto.

Denominare le celle utilizzando etichette di riga e di colonna esistenti

1. Selezionare l'intervallo che si desidera denominare, incluse le etichette di riga e di colonna.
2. Scegliere Nome dal menu Inserisci, quindi scegliere Crea.
3. Indicare la posizione delle etichette selezionando a seconda dei casi la casella di controllo Riga superiore, Colonna sinistra, Riga inferiore o Colonna destra nella casella Crea nomi nella.

Indicazioni per la denominazione di celle, formule e costanti

1. È necessario che il primo carattere di un nome sia una lettera o un carattere di sottolineatura. Per i restanti caratteri del nome è possibile utilizzare lettere, numeri, punti e caratteri di sottolineatura.
2. Non è possibile definire un nome uguale a un riferimento di cella, quale Z$100 o R1C1.
3. Non è consentito l'uso di spazi. È possibile utilizzare i caratteri di sottolineatura e i punti come separatori di parola, quale Primo.Trimestre o Tassa_comunale.
4. Un nome può contenere fino a 255 caratteri.
5. I nomi possono contenere lettere maiuscole e minuscole. La differenza tra caratteri maiuscoli e minuscoli nei nomi non viene rilevata. Se ad esempio è stato creato il nome Vendite e successivamente si crea un altro nome VENDITE nella stessa cartella di lavoro, il primo nome verrà automaticamente sostituito dal secondo.

Etichette e nomi nelle formule
I fogli di lavoro presentano di solito delle etichette poste all'estremità superiore di ciascuna colonna e a sinistra di ciascuna riga. Tali etichette descrivono i dati contenuti nel foglio di lavoro ed è possibile utilizzarle nelle formule come riferimento ai valori in esse contenuti. È anche possibile creare nomi descrittivi, anziché etichette, per rappresentare celle, intervalli di celle, formule o costanti.
Quando si crea una formula che fa riferimento ai dati di una foglio di lavoro, è possibile utilizzare le etichette di riga e di colonna del foglio di lavoro per riferirsi ai dati. Se ad esempio una tabella contiene il totale vendite in una colonna denominata Vendite e una riga relativa a un reparto denominato Alimentari, sarà possibile trovare il totale delle vendite del reparto denominato Alimentari immettendo la formula =Alimentari Vendite. Lo spazio tra le etichette viene interpretato come operatore di intersezione, per cui la formula restituirà il valore della cella posta all'intersezione tra la riga denominata Alimentari e la colonna denominata Vendite.
Se i dati non dispongono di etichette o se si desidera utilizzare le informazioni memorizzate nello stesso foglio o in altri fogli della stessa cartella di lavoro, sarà possibile creare un nome che descriva la cella o l'intervallo. L'utilizzo di un nome descrittivo in una formula consente di comprenderne l'utilità. La formula =SOMMA(VenditePrimoTrimestre), ad esempio, sarà più facilmente identificabile della formula =SOMMA(Vendite!C20:C30). In questo esempio il nome VenditePrimoTrimestre rappresenta l'intervallo C20:C30 del foglio di lavoro denominato Vendite.
I nomi sono visibili da qualunque foglio della stessa cartella di lavoro. Se ad esempio il nome VenditePreventivate si riferisce all'intervallo A20:A30 del primo foglio di lavoro della cartella di lavoro, sarà possibile utilizzare il nome VenditePreventivate in qualunque altro foglio della stessa cartella di lavoro per riferirsi all'intervallo A20:A30 del primo foglio di lavoro. È possibile utilizzare i nomi anche per rappresentare formule o valori costanti. È possibile ad esempio utilizzare il nome IVA per rappresentare l'imposta sul valore aggiunto applicata alle transazioni.

 

Inserimento di dati nelle celle

Per inserire un dato in una cella basta scriverlo, ovviamente dopo aver selezionato la cella stessa, cioè dopo averla resa attiva.

 

In particolare, se la cella attiva contiene già qualcosa, la pressione di un tasto qualunque cancella automaticamente il contenuto precedente. Questo è importante per due motivi:
1. Non è necessario cancellare il contenuto precedente (e questo è il meno), ma soprattutto
2. Se non si fa attenzione, si rischia di perdere inavvertitamente il contenuto precedente.
In questo caso (e comunque ogniqualvolta ci si rendesse conto di aver commesso qualche errore), ricordarsi di ristabilire immediatamente la situazione precedente col comando Modifica Annulla.

Se si desidera solo modificare il contenuto di una cella senza cancellarlo, basta cliccare sulla barra della formula e procedere come con un word processor, apportando solo le correzioni necessarie.

Questo è il metodo standard. In alternativa è possibile anche:

1. Fare doppio clic sulla cella contenente i dati da modificare.
2. Apportare le modifiche desiderate al contenuto della cella.
3. Premere INVIO per rendere effettive le modifiche.

In qualunque caso l'input della cella termina solo:

1. con la pressione del tasto INVIO, oppure
2. facendo uso delle frecce di spostamento, oppure ancora
3. facendo clic col mouse.

Per annullare le modifiche prima di averle confermate con INVIO, premere il tasto ESC.

Attenzione! Fino a quando non si è completato l'input, cioè fintantoché continua la fase di editing, molte operazioni sono disabilitate ed alcuni comandi (tipo quelli di formato) sono diversi dagli originali, in quanto si riferiscono al solo testo.

Una cella può contenere Costanti o Formule, essere cioè cella di input o di output.

Le Costanti possono essere:

1. Numeriche e il programma le allinea automaticamente a destra, oltre a consentire il loro utilizzo nelle operazioni matematiche. Le costanti numeriche devono contenere solo numeri, date (che in realtà vengono trattate come numeri seriali, come vedremo parlando dei formati), parentesi o simboli "+" e "-".
2. Di tipo Testo (o stringhe), che vengono allineate a sinistra e con le quali sono possibili solo operazioni di tipo testo. (Se si cercano di eseguire operazioni matematiche viene visualizzato l'errore #VALORE!) Le stringhe di testo devono contenere almeno una lettera o un carattere non numerico. Per fare in modo che Excel consideri un numero come testo (si pensi ai numeri telefonici o ai codici postali) è necessario impostare preventivamente a testo il formato della cella.

NB. Quando Si trattano testi nelle formule è invece necessario inserirli sempre tra doppie virgolette (Es. "Casa", "Il risultato è ", ecc.).

Le Formule consentono di calcolare (e visualizzare) dei risultati ottenuti compiendo delle operazioni logico-matematiche sui numeri oppure di testo sulle stringhe. Tutte le formule devono cominciare con il simbolo "=", altrimenti quello che viene scritto è interpretato come testo.
Cancellazione o eliminazione di celle, righe, colonne
Quando si cancella una cella, una riga o una colonna, ne vengono rimossi il contenuto (ovvero i dati o la formula), i formati e le note, ma la cella vuota rimane sul foglio di lavoro.
Quando si elimina una cella, una riga o una colonna, queste vengono rimosse dal foglio di lavoro e le celle, righe o colonne circostanti vengono spostate per riempire lo spazio lasciato vuoto.

 

Cancellazione di una cella o di un intervallo

1. Selezionare le celle da cancellare
2. Usare dal menu il comando Modifica Cancella e poi scegliere Tutto, Contenuto, Formati o Note. (In qualche traduzione il Contenuto e le Note vengono chiamati Sommario e Commenti).

 

Per cancellare solo il contenuto di una cella è sufficiente premere il tasto Canc.
Attenzione! NON cercare di cancellare una cella premendo la Barra Spaziatrice; ciò equivarrebbe all'inserimento di uno o più Spazi nella cella, che quindi non risulterebbe più vuota.
Una cella cancellata (o vuota) non contiene nulla e viene interpretata come 0 (zero) nelle operazioni matematiche o come stringa vuota in quelle di testo.

 

Eliminazione di una cella o di un intervallo

1. Selezionare le celle da cancellare
2. Usare dal menu il comando Modifica Elimina. Le celle circostanti verranno spostate per riempire lo spazio lasciato vuoto.

 

Nota   I riferimenti alle celle spostate vengono aggiornati automaticamente in modo da rifletterne la nuova posizione e tenere aggiornate le formule. Tuttavia le formule del foglio di lavoro contenenti un riferimento a una cella eliminata visualizzeranno per ovvi motivi il valore di errore #RIF!.

 

Formule con riferimenti relativi
Quando si scrive una formula, i riferimenti alle celle o agli intervalli si basano di solito sulla posizione relativa di questi rispetto alla cella contenente la formula.

Ad esempio, nella figura 5 la cella A4 contiene una formula in grado di sommare il contenuto della cella A2 (100) col contenuto della cella A3 (200), dando come risultato 300. Essa contiene cioè la formula   =A2+A3. Bene, in termini relativi questo viene letto come:
"Somma il contenuto della cella che sta due righe sopra con quello della cella che sta una riga sopra".

Analogamente, la cella E4 calcola l'importo totale (125000) come imponibile (100000) + imposta (100000*0,25). Essa contiene cioè la formula   =E2+(E2*G2)  oppure   =E2*(1+G2) (che fa lo stesso). In termini relativi tali formule si leggono rispettivamente:
"Somma il contenuto della cella che sta due righe sopra con il prodotto del contenuto della cella che sta due righe sopra per quello della cella che sta due righe sopra e due colonne a destra", oppure:
"Moltiplica il contenuto della cella che sta due righe sopra per 1+ il contenuto della cella che sta due righe sopra e due colonne a destra".
(E' molto più difficile a scriversi che a capirsi, basta guardare la figura…)

Figura 5:        Esempi di formule con riferimenti relativi (stile "A1")

	A	B	C	D	E	F	G
1
2	100			imponibile	100000	aliquota	0,25
3	200
4	300			totale	125000
5
6

 

Lo stesso può dirsi se si usa lo stile di riferimento "R1C1", che (una volta superato un primo comprensibile sconcerto) può risultare addirittura più facile a capirsi. (figura 6). Comunque, niente paura, da questo punto in poi si useranno solo i riferimenti in stile "A1".

Figura 6:        Esempi di formule con riferimenti relativi (stile "R1C1")

	1	2	3	4	5	6	7
1
2	100			imponibile	100000	imposta	0,25
3	200
4	300			totale	125000
5
6

 

Vedremo, parlando della copia di formule, l'enorme importanza del concetto di riferimenti relativi, cui verranno contrapposti i cosiddetti riferimenti assoluti.

 

Copia di celle

Le informazioni che verranno fornite qui di seguito sono solo quelle essenziali, rivolte a coloro che non hanno ancora dimestichezza col computer e che abbisognano di poche nozioni abbastanza solide cui aggrapparsi nei numerosi momenti di sconforto che tutti abbiamo passato agli inizi.
Per questo motivo si farà riferimento preferibilmente alle voci del menu, raggiungibili anche quando si guasta il mouse, facendo uso del tasto ALT in combinazione con le lettere sottolineate del menu stesso. Superata la prima fase di timore reverenziale verso la macchina, sarà bene impratichirsi con tutte le "scorciatoie" messe a disposizione dai programmi Windows. (Per la loro "scoperta" si rimanda alla guida in linea o al "passaparola". Non verranno chieste all'esame.)

Per effettuare qualunque operazione di copia, procedere secondo le quattro fasi seguenti:

 

Per spostare una cella, usare il Modifica Taglia (o Ctrl+X) al posto del Modifica Copia.

La cella o l'intervallo selezionato con Modifica Copia restano contornati da una linea tratteggiata lampeggiante e restano in memoria fino al completamento delle operazioni di Incolla.
Attenzione! Contrariamente al funzionamento standard della memoria Appunti di Windows, la selezione viene persa alla prima pressione del tasto INVIO o del tasto ESC, rendendo così impossibili ulteriori operazioni di Incolla.

Esempi.

Esempio 1.
Per copiare il contenuto della cella B3 nella cella D5:

1. Selezionare la cella B3;
2. Modifica Copia (o Ctrl+C);
3. Selezionare la cella D5;
4. Modifica Incolla (o Ctrl+V).

	A	B	C	D	E	F	G
1
2
3		100
4
5				100
6

 

Esempio 2.
Per copiare il contenuto della cella B3 nell'intervallo D5:G5

1. Selezionare la cella B3;
2. Modifica Copia (o Ctrl+C);
3. Selezionare l'intervallo D5:G5;
4. Modifica Incolla (o Ctrl+V).

	A	B	C	D	E	F	G
1
2
3		100
4
5				100	100	100	100
6

 

Esempio 3.
Per copiare il contenuto della cella B3 nelle celle D3:G3 e D5:G5

1. Selezionare la cella B3;
2. Modifica Copia (o Ctrl+C);
3. Selezionare (usando il tasto Ctrl) gli intervalli D3:G3 e D5:G5;
4. Modifica Incolla (o Ctrl+V).

	A	B	C	D	E	F	G
1
2
3		100		100	100	100	100
4
5				100	100	100	100
6

 

Esempio 4.
Per copiare il contenuto delle celle B3:B5 nelle celle D3:D5

1. Selezionare l'intervallo B3:B5;
2. Modifica Copia (o Ctrl+C);
3. Selezionare solo la cella D3 (volendo si può selezionare l'intero intervallo D3:D5, ma in questo caso, se ci si sbaglia e i due intervalli non sono identici, l'operazione non riesce);
4. Modifica Incolla (o Ctrl+V).

	A	B	C	D	E	F	G
1
2
3		100		100
4		200		200
5		500		500
6

 

Esempio 5.
Per copiare il contenuto delle celle B3:B5 nell'intervallo D3:F5

1. Selezionare l'intervallo B3:B5;
2. Modifica Copia (o Ctrl+C);
3. Selezionare (volendo) l'intero intervallo D3:F5, ma è molto più semplice selezionare solo le celle della prima riga D3:F3;
4. Modifica Incolla (o Ctrl+V).

	A	B	C	D	E	F	G
1
2
3		100		100	100	100
4		200		200	200	200
5		500		500	500	500
6

 

Copia di formule con riferimenti relativi ed assoluti
Quando si copia una formula, i riferimenti di cella vengono copiati di norma in modo relativo, nel senso che i riferimenti della formula incollata verranno modificati per adeguarsi alla nuova posizione della formula.
Nella figura 7, ad esempio, il saldo di gennaio (cella B4) è stato ottenuto facendo la differenza tra la cella B2 e la cella B3. La formula contenuta nella cella B4 è pertanto: =B2-B3. Se tale formula viene copiata nelle altre celle della riga otterremo le formule seguenti:
cella C4:          =C2-C3
cella D4:         =D2-D3
cella E4:          =E2-E3

Il risultato non è per nulla banale né scontato, anche se è proprio quello che ci saremmo aspettati.
Come fanno a cambiare i riferimenti di colonna?
Per comprendere quello che è avvenuto, basta pensare alla formula in termini relativi.
La formula =B2-B3 si può leggere nel modo seguente: "Fai la differenza tra il contenuto della cella situata nella stessa colonna, due righe sopra  ed il contenuto della cella situata nella stessa colonna, una riga sopra". Letta in questi termini (relativi, appunto), la formula risulta essere la stessa in tutte le celle della riga 4.

Figura 7:        Copia con riferimenti relativi

	A	B	C	D	E	F	G
1		gennaio	febbraio	marzo	totale
2	Entrate	10000	20000		30000
3	Uscite	3000	5000		8000
4	Saldo	7000	15000	0	22000
		=B2-B3	=C2-C3	=D2-D3	=E2-E3

 

 

	A	B	C	D		F	G
1	Articolo	Costo unit.	Num. pezzi	Totale
2	Art. 1	100000	10	1000000	=B2*C2
Direzione della copia  3	Art. 2	50000	50	2500000	=B3*C3
4	Art. 3	20000	20	400000	=B4*C4
5	Art. 4	70000	60	4200000	=B5*C5
6

 

	A	B	C	D	E	F	G
1	100	200
2		300
3		=A1+B1		500	600
4					1100
5					=D3+E3
6

In quest'ultimo esempio la cella B2 contiene la formula =A1+B1. Se la si copia nella cella E4, la stessa diventa =D3+E3, dando come risultato 1100.

 

Un altro modo per interpretare i tre esempi della figura 7 è quello di incrementare le lettere delle colonne (o i numeri di riga) di tante posizioni di quante è stata spostata la formula nella copia.
Nel primo esempio è possibile prevedere quello che diventerà la formula originale (della cella B4) una volta copiata nella cella immediatamente a destra. Basta infatti incrementare tutte le lettere di 1 (la B diventa C, la C diventa D, ecc.)
Analogamente, nel secondo esempio, se la formula viene copiata in basso di una riga, tutti i numeri di riga verranno incrementati di 1. Così il 2 diventa 3, il 3 diventa 4, ecc.
Nel terzo esempio la formula viene copiata a destra di 3 colonne (e allora la A diventa D e la B diventa E) ed in basso di 2 righe (e quindi l'1 diventa 3).

In conclusione è molto importante ricordare che

1. Nella copia in orizzontale cambiano solo i riferimenti di colonna.
2. Nella copia in verticale cambiano solo i riferimenti di riga.
3. Nella copia in diagonale cambiano sia i riferimenti di riga che quelli di colonna

E' peraltro tutt'altro che infrequente il caso in cui si desidera che alcuni riferimenti non si modifichino durante l'operazione di copia, come nel caso di figura 8. Nel primo quadro sono riportati gli importi ottenuti dai calcoli, mentre nel secondo sono evidenziate le formule effettivamente contenute nelle celle.

Figura 8:        Esempi

	A	B	C	D	E	F	G
1
2	Imponibile	Iva	Totale		IVA	20%
3	100000	20000	120000
4	200000	40000	240000
5	500000	100000	600000
6

	A	B	C	D	E	F	G
1
2	Imponibile	Iva	Totale		IVA	20%
3	100000	=A3*F2	=A3+B3
4	200000	=A4*F2	=A4+B4
5	500000	=A5*F2	=A5+B5
6

Si osservi che, mentre la formula della cella C3 può essere copiata verso il basso senza problemi (i riferimenti di colonna restano invariati, mentre quelli di riga si incrementano automaticamente), quella della cella B3 no, in quanto il riferimento alla cella F2 deve restare fisso e non incrementarsi di riga in riga come avviene per il riferimento alla cella A3 (che diventa A4 e A5). In questo caso si parla di riferimenti assoluti.

È possibile creare un riferimento assoluto anteponendo il simbolo di dollaro ($) alle parti del riferimento che si desidera lasciare inalterate.

Nell'esempio precedente, per rendere copiabile la formula della cella B3, basta porre davanti al 2 (che deve restare fisso) il simbolo $. La formula diventa pertanto  =A3*F$2.

L'argomento della copia con riferimenti relativi ed assoluti è estremamente semplice una volta capito il meccanismo, ma, per esperienza, l'impatto iniziale non va sottovalutato ed è necessario fare parecchio esercizio prima di acquisire un sufficiente automatismo nel ragionamento.
Qui di seguito sono riportati alcuni esempi banali, ma che molto spesso riescono già a mettere in difficoltà chi affronta per la prima volta l'argomento.

Si supponga che una ditta voglia accantonare il 20% degli utili mensili, come riportato in figura 9. Si supponga ovviamente che lo schema debba essere ottenuto scrivendo le formule di gennaio e poi copiandole per tutti i 12 mesi.
Le relative formule sono indicate nella seconda tabella.

 

La formula per il calcolo del saldo di gennaio, scritta nella cella D4, è:  =D2-D3.
Essa, copiata verso destra, diventa:  =E2-E3,  =F2-F3, ecc.

La formula per il calcolo dell'accantonamento di gennaio (cella D6) è:  =D4*B5.

Ora si faccia attenzione alla domanda (può essere una domanda d'esame):

"Cosa diventa la formula della cella D6 se viene copiata nella cella E6 così com'è?"

Si invita caldamente a scriversi la risposta su un pezzo di carta prima di sbirciare la soluzione. Pare incredibile, ma per esperienza più di metà delle persone rispondono sbagliato le prime volte.

Figura 9

	A	B	C	D	E	F	G
1				gennaio	febbraio	marzo	aprile
2			entrate	300000	300000	500000
3			uscite	200000	100000	200000
4			saldo	100000	200000	300000	0
5	% accantonamento	20%
6				20000	40000	60000	0

	A	B	C	D	E	F	G
1				gennaio	febbraio	marzo	aprile
2			entrate	300000	300000	500000
3			uscite	200000	100000	200000
4			saldo	=D2-D3	=E2-E3	=F2-F3	=G2-G3
5	% accantonamento	20%
6				=D4*B5	?	?	?

 

Il fatto è che siamo portati a proiettare nella risposta quello che vorremmo ottenere piuttosto che analizzare freddamente ed asetticamente il problema ed eseguire meccanicamente le operazioni richieste, così come lo farebbe l'elaboratore.
La regola della copia in orizzontale (di ciò si tratta in questo caso) è:
"Nella copia verso destra cambiano solo i riferimenti delle colonne, di tante posizioni quante sono le colonne saltate".
In questo caso viene richiesto di prevedere cosa succede della formula contenuta nella cella D6 se viene copiata a destra di una colonna (e solo successivamente di copiarla nelle colonne adiacenti).
La risposta è quindi =E4*C5, e non E4*B5, che è invece quello che vorremmo ottenere! (Siete tra quelli che hanno risposto sbagliato? Non preoccupatevi, siete in buona compagnia! Avete risposto giusto? Non cantate vittoria, avrete tante occasioni per sbagliare!)
Ma non è quello che volevamo! Noi vogliamo che nella cella E6 ci sia la formula =E4*B5.
Ed allora, per impedire che il riferimento alla colonna B si modifichi e diventi C durante la copia, basta bloccarlo anteponendovi il simbolo $. La formula della cella D6 diventa così  =D4*$B5.

Un altro esempio può essere quello di costruire una tavola pitagorica automatica, come in figura 10.

Figura 10

	A	B	C	D	E	F	G	H	I	J	K	L	M
1		1	2	3	4	5	6	7	8	9	10
2	1	1	2	3	4	5	6	7	8	9	10
3	2	2	4	6	8	10	12	14	16	18	20
4	3	3	6	9	12	15	18	21	24	27	30
5	4	4	8	12	16	20	24	28	32	36	40
6	5	5	10	15	20	25	30	35	40	45	50
7	6	6	12	18	24	30	36	42	48	54	60
8	7	7	14	21	28	35	42	49	56	63	70
9	8	8	16	24	32	40	48	56	64	72	80
10	9	9	18	27	36	45	54	63	72	81	90
11	10	10	20	30	40	50	60	70	80	90	100
12

Scrivere la formula nella cella B2 in modo tale da poterla copiare con una sola operazione in tutto l'intervallo B2:K11. (Per curiosità, annotarsi tutti gli errori fatti prima di trovare la soluzione giusta!). Provare anche a modificare la larghezza delle colonne.

Per ridurre le sorprese (soprattutto all'esame, dove di solito la tranquillità non è di casa…), si consiglia di attenersi ai seguenti consigli.

• Il problema dei riferimenti assoluti va affrontato solo se si pensa di dover copiare la formula in un'altra posizione, altrimenti va del tutto ignorato.
• Prima di copiare una formula bisogna sempre porsi la domanda se sarà necessario usare i riferimenti assoluti.

 

Per farlo, seguire le seguenti fasi:

• Scrivere, su un pezzo di carta, la formula che si intende copiare.
• Scrivere nella riga sottostante, la formula che si prevede di ottenere copiando la formula così come sta. (Attenzione in questa fase di non confondere quello che si ottiene con quello che si desidera ottenere!).
• Scrivere nella riga successiva la formula che si vorrebbe avere nella cella di destinazione.
• Confrontare le formule dei punti 2. e 3., per trovare le eventuali differenze. (Se non ce ne sono, vuol dire che la copia riuscirà e che non servono ulteriori modifiche alla formula originale).
• Verificare se è possibile rendere copiabile la formula facendo uso dei riferimenti assoluti ($) (non sempre ciò è possibile!).

Qualche esempio chiarirà meglio quanto detto.

Si faccia riferimento alla figura 9.
Applichiamo il procedimento alla formula della cella D4 (=D2-D3), dopo esserci accertati che desideriamo copiarla verso destra (altrimenti non occorre fare nulla).

• =D2-D3
• =E2-E3
• =E2-E3
• Non ci sono differenze tra i punti 2. e 3.
• La formula è copiabile così come sta.

Applichiamo ora il procedimento alla cella D6, che contiene la formula =D4*B5.

• =D4*B5
• =E4*C5 (Ricordarsi che nella copia verso destra cambiano tutti e soli i riferimenti di colonna)
• =E4*B5
• Va tutto bene, tranne che la "B" diventa "C" ed invece vogliamo che resti fissa "B".
• Posso intervenire "fissando" la B, anteponendovi il simbolo $.

 

La formula della cella D6 diventerà così  =D4*$B5 e potrà essere copiata verso destra.
Si noti che molti testi consigliano di bloccare entrambe le coordinate, visto che la cella deve rimanere fissa (la formula diverrebbe =D4*$B$5. Niente di più sbagliato (come vedremo nell'esempio successivo), anche se in questo caso non ci sarebbero controindicazioni (a parte l'inutilità dell'operazione), visto che comunque la riga resterebbe invariata, trattandosi di una copia in orizzontale.

 

Proviamo ora ad applicare il procedimento al problema della tavola pitagorica (figura 10).

La formula di partenza, scritta nella cella B2 è:  =A2*B1. (Per curiosità, siete tra quelli che avrebbero scritto =A1*B1, confondendo il numero di riga (2) con il numero 1 contenuto nella cella A2?).

Cominciamo col verificare la copiabilità della formula verso il basso, lungo la colonna B. Limitiamoci a verificare cosa succede copiando la formula giù di una riga.

• =A2*B1
• =A3*B2
• =A3*B1
• Va tutto bene, tranne che la riga 1 diventa 2 ed invece vogliamo che resti 1.
• Si può bloccare quindi il numero della riga, e la formula diventa:  =A2*B$1.

 

Verifichiamo ora la copiabilità nella prima cella a destra (cella C2).

• =A2*B$1 (ormai la formula è questa, se vogliamo mantenere la copiabilità verso il basso).
• =B2*C$1
• =A2*C1 (questa è la formula che avremmo scritto nella cella C2 se non avessimo optato per la copia).
• Va tutto bene (anche il $, che non dà fastidio), tranne la "A", che diventa "B" ed invece vogliamo che resti "A".
• Possiamo quindi bloccare la A, e la formula originale diventa quindi:  =$A2*B$1.

 

Notare che in questo caso sarebbe stato sbagliato bloccare tutte le coordinate, con una formula del tipo  =$A$2*$B$1. Avremmo ottenuto come risultato sempre "1".

Per copiare la formula su tutto l'intervallo in una volta sola:

• Selezionare la cella B2
• Modifica Copia
• Selezionare l'intervallo B2:K11 (ad esempio cliccando sulla cella K11 tenendo premuto il tasto Maiuscolo)
• Modifica Incolla

Ipotizziamo ora di voler gestire la contabilità di tre autovetture, come rappresentato nella figura 11.
Per ognuna di esse va conteggiato:

• un costo fisso per l'uso del box (uguale per tutte)
• un costo fisso per il bollo (diverso per ogni vettura)
• un costo variabile per i consumi, ipotizzando l'uso di tre carburanti diversi, del costo rispettivamente di 1,1 , 1 e 1,2  Euro per litro

Tutti gli importi sono espressi in Euro.

Figura 11

	A	B	C	D	E
1		Vettura A	Vettura B	Vettura C
2	Costo box	50	50	50
3	Bollo	100	110	90
4	Litri carburante	500	200	300
5	Costo carburante	550	200	360
6
7	Totale	700	360	500
8
9	Valori comuni di riferimento
10	Costo box	50
11
12

	A	B	C	D	E
1		Vettura A	Vettura B	Vettura C
2	Costo box	=$B10	=$B10	=$B10
3	Bollo	100	110	90
4	Litri carburante	500	200	300
5	Costo carb.	=B4*1,1	=C4*1	=D4*1,2
6	Formule alternative per la riga 5	=B4*B11	=C4*C11	=D4*D11
7	Totale	=B2+B3+B5	=C2+C3+C5	=D2+D3+D5
8
9	Valori comuni di riferimento
10	Costo box	50
11	Costo carb. (E/litro)	1,1	1	1,2
12

Si osservi come è ben vero che è possibile calcolare il costo del carburante relativo ad ogni vettura scrivendo delle formule che moltiplichino il numero dei litri (riga 4) direttamente per il costo al litro di ciascun carburante(come fatto nella riga 5), ma questo comporta due svantaggi non trascurabili:

• non è possibile copiare la formula verso destra, il ché costringe ad una riscrittura della stessa nelle colonne C e D, aumentando sia l'onere lavorativo sia il rischio di errore;
• se il costo di riferimento cambia, si è costretti ad andare a modificare le formule stesse, con gli stessi oneri di cui sopra.

L'alternativa è quella proposta in caratteri corsivi rossi (righe 6 ed 11), che consente di:

• rendere copiabile la formula della colonna B
• poter esplicitare i costi unitari dei carburanti (nella riga 11), aumentando così la leggibilità dello schema e la praticità degli interventi correttivi.

La formula della cella B7 è copiabile senza problemi, mentre quella della cella B2 deve essere predisposta per la copia anteponendo al riferimento di colonna (B) il simbolo $.

 

Supponiamo infine di voler mettere a punto uno schema in grado di calcolare il capitale di quattro soci, che possiedono ciascuno una partecipazione in percentuali fisse a tre aziende più una quota fissa uguale per tutte le aziende.

La quota fissa comune di partecipazione a tutte le aziende sia pari a 1000.
Le quote di partecipazione dei 4 soci alle 3 aziende siano rispettivamente 20%, 40%, 10%, 30%.
I valori delle tre aziende siano 10000, 50000, 20000.

Con riferimento alla figura 12, la quota di partecipazione del Socio 1 all'Azienda A è data da:
quota fissa + percentuale di partecipazione al capitale aziendale A, cioè
1000+(20%*10000)=3000
Analogamente la quota di partecipazione del Socio 3 all'Azienda B sarà:
1000+(10%*50000)=6000

La formula di partenza della cella C5 sarà pertanto:  =G2+(B5*C4)
(Le parentesi non sono necessarie; sono state inserite solo per maggior chiarezza).

Figura 12.

	A	B	C	D	E	F	G	H
1							Quota fissa
2							1000
3			Azienda A	Azienda B	Azienda C
4		% partecip.	10000	50000	20000
5	Socio 1	20%	3000	11000	5000
6	Socio 2	40%	5000	21000	9000
7	Socio 3	10%	2000	6000	3000
8	Socio 4	30%	4000	16000	7000
9

Verifichiamo innanzitutto se questa formula è copiabile in orizzontale nella cella a destra (D5).

• =G2+(B5*C4)
• =H2+(C5*D4)
• =G2+(B5*D4)
• Osserviamo che, mentre ci sta bene che il riferimento alla cella C4 passi alla cella D4, non ci sta bene che i riferimenti alle celle G2 e B5 diventino rispettivamente H2 e C5.
• Fissiamo i riferimenti delle colonne G e B

La formula nella cella C5 diventa così:  =$G2+($B5*C4).

Verifichiamo ora se tale formula può essere copiata verso il basso di una riga (nella cella C6).

• =$G2+($B5*C4)
• =$G3+($B6*C5)
• =$G2+($B6*C4)
• Osserviamo che, mentre ci sta bene che il riferimento alla cella B5 passi alla cella B6, non ci sta bene che i riferimenti alle celle G2 e C4 diventino rispettivamente G3 e C5.
• Si rende quindi necessario fissare i riferimenti alle righe 2 e 4.

La formula nella cella C5 diventa così:  =$G$2+($B5*C$4).

Adesso la formula è copiabile su tutto l'intervallo C5:E8.

Copia e Incolla Speciale
Spesso succede di voler copiare solo le formule, ma non i formati di una cella (un esempio tipico è costituito dai bordi esterni di una tabella, che verrebbero riportati in tutte le celle interne). In questo ed in altri casi, dopo aver eseguito il comando Modifica Copia, è necessario usare il comando Modifica Incolla speciale. Della finestra di dialogo verranno commentate solo le opzioni più comuni, lasciando al lettore il compito di soddisfare autonomamente l'eventuale ulteriore curiosità.

Tutto          Incolla il contenuto e la formattazione della cella. Questa opzione equivale a utilizzare il comando Modifica Incolla.
Formule     Incolla solo le formule come immesse nella barra della formula.
Valori        Incolla solo i valori come visualizzati nelle celle (cioè le costanti o i risultati delle formule, non le formule stesse).
Formati     Incolla solo la formattazione delle celle.
Tutto eccetto i bordi    Incolla il contenuto e la formattazione applicati alla cella copiata tranne i bordi.
Incolla collegamento   Crea una formula del tipo  =$C$7.

 

Riferimenti circolari

Per applicazioni particolari (in ambito scientifico o ingegneristico) è possibile utilizzare i cosiddetti riferimenti circolari . La loro gestione è abbastanza complessa ed esula dalle finalità di un corso di base. Si danno comunque delle indicazioni di massima perché talvolta tali riferimenti vengono introdotti inavvertitamente, creando non pochi problemi ai neofiti.
Un riferimento circolare si instaura quando più celle dipendono una dall'altra in modo da non consentire di individuare la prima della catena.
Si riportano tre casi tipici di riferimenti circolari non voluti.

Caso 1. Il caso più semplice è costituito da una cella che dipende da se stessa.
cella B5:          =B5+1.

Caso 2. Esempio di catena di dipendenze:
cella A1:         =C3/2
cella B2:          =A1+7
cella C3:          =B2*4
(La cella C3 dipende dalla B2, che dipende dalla A1, che dipende di nuovo dalla C3.)

Caso 3. Esempio di errore molto frequente nell'uso della funzione SOMMA().
cella C10:        =SOMMA(C1:C10)

A scanso di malfunzionamenti, i riferimenti circolari vanno individuati e rimossi immediatamente.
Formato delle celle
La voce Formato del menu di Excel consente di modificare l'aspetto della cella e del suo contenuto, senza modificarne la sostanza. E' molto importante non confondere mai il contenuto con il formato della cella. In questo contesto non verranno trattati i formati rivolti all'estetica della cella (bordi, sfondi, ecc.), nonché quelli relativi al carattere ed all'allineamento, che risultano oltremodo intuitivi. Si ritiene invece indispensabile spendere due parole sui formati numerici.
Attivando la voce di menu Formato Cella Numero, è possibile scegliere tra diversi modi per presentare il dato visualizzato nella cella. Qui di seguito vengono esaminate le categorie più comuni.

Generale        Il dato viene presentato con i formati di default (testi allineati a sinistra, numeri allineati a destra, ecc.)

Numero          Se il dato è numerico, con questa opzione può essere scelto il numero (fisso) di decimali da visualizzare. Soprattutto in questo caso non bisogna confondere il contenuto con il suo formato. Il numero effettivo può avere anche un numero diverso di decimali, ma con questa opzione posso decidere ad esempio di visualizzarne sempre tre, effettuando nel contempo un arrotondamento al valore più vicino.
Esempi di numeri cui è stato applicato il formato con 3 decimali:
125,3678    ®    125,368
0,25            ®        0,250
0,00215      ®        0,002
678             ®    678,000
Si insiste sul fatto che, agendo sul formato, il numero mantiene in realtà tutte le sue cifre.
Diverso è il caso in cui venga adoperata una delle funzioni matematiche di arrotondamento, la più tipica delle quali è la funzione ARROTONDA().
Essa permette di ottenere un nuovo numero, con un numero di cifre prefissato.
Un esempio concreto forse chiarirà questo aspetto.
Il numero 10 diviso per 6 dà come risultato 1,66666, col 6 periodico.
Tale numero, espresso in formato con 2 decimali si presenta come 1,67, ma mantiene la sua essenza di numero periodico, per cui, se viene moltiplicato di nuovo per 6 dà ancora come risultato 10.
Se invece applichiamo al numero 1,66666 la funzione ARROTONDA() a 2 decimali, otteniamo effettivamente il nuovo numero 1,67, che, moltiplicato per 6 darà come risultato 10,02.
Si invita a provare quanto sopra, ricordando che l'uso corretto della funzione è:
ARROTONDA(numero;decimali), dove
numero è in questo caso la cella che contiene il numero 1,66666;
decimali è il numero di decimali a cui arrotondare (in questo caso 2).

Valuta e Contabilità Sono formati dedicati specificatamente all'uso contabile.

 

Percentuale    Il formato percentuale moltiplica il valore per 100, mostrano il numero di decimali specificato ed aggiungono il simbolo "%". Esempi
0,256          ®      26%           con 0 decimali
25,60%      con 2 decimali
1                 ®    100%           con 0 decimali
100,0%        con 1 decimale
2,5              ®    250%           con 0 decimali
Data e Ora     I Formati Cella Numero Data e Ora richiedono un pizzico di attenzione in più.
I valori di tempo vengono trattati da Excel come "numero di rotazioni terrestri a partire da una data prefissata" . I numeri interi (chiamati in questo contesto numeri seriali) rappresentano i giorni trascorsi, mentre le parti decimali rappresentano le ore, minuti e secondi intesi come frazione del giorno.
Esempi:
1                 ®      01/01/1900
31               ®      31/01/1900
32               ®      01/02/1900
61               ®      01/03/1900
27904         ®      24/05/1976
36526         ®      01/01/2000
1,5              ®      01/01/1900            ore 12:00:00
1,25            ®      01/01/1900            ore 06:00:00
28511,607  ®      21/01/1978            ore 14:34:05
Tale soluzione è stata adottata per semplificare gli algoritmi di calcolo necessari agli utilizzatori finanziari (si pensi al calcolo degli interessi bancari).
Anche in questo caso comunque non dimenticare mai che il numero effettivo è quello che è e la data o l'ora non sono che un particolare formato, cioè un modo diverso di rappresentare quel numero.
Fare attenzione che il separatore decimale nei sistemi italiani è la virgola (",") e non il punto. Se si digita un punto, il numero viene formattato per default come un'ora, creando non poche difficoltà ai neofiti. Per tornare a visualizzare il numero effettivo, usare Formato Celle Numero Generale.

Testo               Questo formato viene utilizzato se si vogliono digitare dei numeri da interpretare come testo (si pensi ai numeri telefonici ed in particolare ai prefissi, che cominciano per 0).

Personalizzato           Questo formato consente all'utente di crearsi propri formati personalizzati. A titolo di esempio si provi ad usare su una cella contenente una data il formato "gggg", per visualizzare il giorno della settimana corrispondente.

Formule e Funzioni del foglio di calcolo
La funzione fondamentale del foglio di calcolo è appunto quella di eseguire calcoli, cioè elaborare dati numerici o di testo per produrre risultati della stessa natura (numerici o di testo). Si è già detto che le celle del foglio possono essere intese come celle di input (dove si inseriscono i dati da elaborare) e celle di output (che contengono formule di calcolo e mostrano i risultati delle operazioni). Bene, le espressioni che consentono di elaborare dati vengono dette formule e devono essere precedute dal simbolo "=". Esistono inoltre delle formule predefinite, atte a svolgere operazioni più complesse, chiamate funzioni.

Una formula è in pratica una equazione che analizza i dati del foglio di lavoro e produce dei risultati. Le formule eseguono operazioni come l'addizione, la moltiplicazione, il confronto tra:

• valori costanti (numeri o testi) o
• valori contenuti in altre celle dello stesso foglio di lavoro, in celle di altri fogli della stessa cartella di lavoro oppure in celle appartenenti a fogli di altre cartelle di lavoro. Come già visto, per rendere più comprensibile la formula, è possibile assegnare un nome alle celle di riferimento. Nel seguente esempio è riportata l'addizione del valore contenuto nella cella B4 e del valore costante 25, il cui risultato viene diviso per la somma dei contenuti delle celle dell'intervallo "Vendite".

=(B4+25)/SOMMA(Vendite)

La cella contenente la formula è denominata cella dipendente, poiché il valore in essa contenuto dipende dai valori contenuti in un'altra cella. Ogni volta che viene modificato il valore della cella a cui la formula fa riferimento, verrà modificato anche il valore della cella contenente la formula.

Il caso più semplice di formula è costituito dall'esigenza di visualizzare in una cella il valore presente in una cella di riferimento. Supponiamo di voler riproporre nella cella H6 il contenuto della cella A2; in questo caso basterà inserire nella cella H6 la seguente formula elementare:   =A2.
Si invita a non sottovalutare questo fatto apparentemente banale, data la frequenza con cui tale situazione si presenta ed alla difficoltà che molti (troppi) incontrano quando una richiesta del genere viene fatta all'esame!

Sintassi delle formule
Come nelle espressioni matematiche, nell'esecuzione delle operazioni indicate in una formula viene seguito un certo ordine, indicato appunto col nome di sintassi.. Valgono le seguenti regole:

• La formula inizia col il simbolo "=", seguito dagli elementi del calcolo (operatori e operandi).
• Ciascun operando può essere un valore costante, una cella o un intervallo di celle, un'etichetta, un nome o una funzione del foglio di lavoro.
• Le formule vengono calcolate da sinistra a destra, a partire dal segno di uguale (=), rispettando l'ordine gerarchico attribuito agli operatori.
• E' possibile modificare l'ordine di esecuzione delle operazioni racchiudendo in parentesi quelle che devono essere eseguite per prime.

Esempio:
=5+2*3           dà come risultato 11
=(5+2)*3         dà come risultato 21

Operatori di calcolo delle formule
Gli operatori specificano il tipo di calcolo che si desidera eseguire sugli elementi di una formula. Sono disponibili quattro diversi tipi di operatori di calcolo: aritmetici, di confronto, di testo e di riferimento.

• Gli operatori aritmetici eseguono le operazioni matematiche di base come l'addizione, la sottrazione o la moltiplicazione; operano solo sui numeri e generano risultati numerici.

Operatore aritmetico	Significato	Esempio	Risultato
+ (simbolo più)	Addizione	3+2	5
- (simbolo meno)	Sottrazione	3-1	2
	Negazione	-A6	-2 (se A6 contiene 2)
* (asterisco)	Moltiplicazione	3*C8	12 (se C8 contiene 4)
/ (simbolo di frazione)	Divisione	10/5	2
^ (accento circonflesso)	Elevamento a potenza	B7^2	9 (se B7 contiene 3)
% (simbolo di percentuale)	Percentuale	20%	0,2

 

• Gli operatori di confronto confrontano due valori, generando il valore logico VERO o FALSO; operano su numeri o testi.

Operatore di confronto	Significato	Esempio	Risultato (per A1=5)
= (simbolo uguale)	Uguale a	A1=3	FALSO
> (simbolo maggiore)	Maggiore di	A1>3	VERO
< (simbolo minore)	Minore di	A1<3	FALSO
>=	Maggiore di o uguale a	A1>=8	FALSO
<=	Minore di o uguale a	A1<=5	VERO
<> (simbolo diverso)	Diverso da	A1<>3	VERO

 

• L'operatore di testo unisce due stringhe di testo generando una stringa unica.

Operatore di testo	Significato	Esempio	Risultato
& (simbolo e commerciale)	Unisci a	"Salva"&"Gente"	SalvaGente

 

• Gli operatori di riferimento uniscono intervalli di celle.

Operatore di riferimento	Significato	Esempio	Risultato
: (simbolo due punti)	Operatore di intervallo: genera un riferimento a tutte le celle comprese tra due riferimenti, inclusi i due riferimenti stessi	B5:B15	Tutte le celle comprese tra B5 e B15  (incluse)
; (simbolo punto e virgola)	Operatore di unione: unisce più riferimenti, anche disgiunti, generando un unico riferimento	B1:B5;D1:D5	Individua come unico elemento l'intervallo da B1 a B5 più quello da D1 a D5
(simbolo spazio)	Operatore di intersezione: genera un riferimento alle celle comuni a due riferimenti	A7:D7 B5:B15	Individua la cella B7

Gerarchia degli operatori
Quando in una formula sono presenti diversi operatori, l'ordine di esecuzione delle operazioni sarà quello illustrato nella tabella che segue. Se una formula contiene operatori dotati della stessa precedenza, come quelli di moltiplicazione e divisione, essi verranno calcolati da sinistra a destra. Per modificare l'ordine del calcolo, racchiudere tra parentesi la parte della formula che si desidera calcolare prima.

Precedenza	Operatore	Descrizione
1	: (due punti)   ; (punto e virgola)     (spazio singolo)	Operatori di riferimento
2	–	Negazione (–1)
3	%	Percentuale
4	^	Elevamento a potenza
5	*    /	Moltiplicazione e Divisione
6	+    –	Addizione e Sottrazione
7	&	Concatenazione di stringhe
8	=   <   >   <=   >=   <>	Confronto

 

Funzioni
Per estendere le potenzialità di calcolo del foglio elettronico, Excel mette a disposizione molte formule predefinite (oltre 250), denominate funzioni, che vanno ad aggiungersi agli operatori fondamentali visti in precedenza.
La funzione più comune nei fogli di lavoro è la funzione SOMMA, utilizzata per sommare intervalli di celle. Benché sia possibile creare una formula per il calcolo del totale dei valori contenuti in poche celle, usando il normale operatore "+", la funzione del foglio di lavoro SOMMA consente di includere nel calcolo un intero intervallo, se non addirittura diversi intervalli di celle.

Sintassi delle funzioni
Le funzioni vengono utilizzate nelle formule alla stregua degli altri operatori. Valgono pertanto per loro le stesse regole fondamentali viste in precedenza (inizio con "=", precedenze degli operatori, parentesi, ecc.). Volendo banalizzare, le funzioni possono essere considerate parti delle formule racchiuse tra parentesi. Oltre a ciò le funzioni seguono una loro sintassi specifica, che, se non viene rispettata, genera un errore del programma.

La sintassi di una funzione inizia con il nome della funzione, seguito da una parentesi aperta, dagli argomenti della funzione separati da punti e virgola e da una parentesi chiusa. Se una formula inizia con una funzione, digitare un segno di uguale (=) prima del nome della funzione.
Esempi:
SOMMA(B1:B10)
CONCATENA("Nome";" ";"Cognome")
SE(A5>1000;"OK";A5/2)

• Il nome deve essere necessariamente compreso tra quelli previsti (vedi l'elenco delle funzioni presente sulla guida in linea di Excel). L'inserimento di un nome sbagliato farà comparire nella cella la segnalazione di errore #NOME?.
• Le parentesi sono sempre necessarie, anche quando la funzione non necessita di argomenti (come la funzione PI.GRECO())
• Gli argomenti possono essere costanti (numeri, testo, valori logici come VERO o FALSO, matrici, valori di errore) oppure riferimenti di cella. È necessario che l'argomento indicato fornisca un valore valido per quella funzione. Gli argomenti possono anche essere formule o altre funzioni; in questo caso si parla di funzioni nidificate. Nella versione italiana gli argomenti sono separati dal punto e virgola (;).

Inserimento delle funzioni
Quando si è acquisita una certa esperienza sull'uso delle funzioni (almeno di quelle principali) è certamente conveniente digitarle direttamente da tastiera, ma nella fase di apprendimento o comunque quando si necessita di un aiuto, è possibile fare uso dell'inserimento guidato. Con il comando di menu Inserisci Funzione (oppure cliccando sul pulsante Modifica formula col simbolo "=" a fianco della barra della formula, oppure ancora cliccando sul pulsante fx della barra degli strumenti) viene attivata la cosiddetta Finestra della funzione, che guida nella autocomposizione della stessa.

La Finestra della funzione presenta due pagine.

• La prima mostra tutte le funzioni raggruppate in categorie, consentendo così di scegliere la funzione desiderata. Cliccando sul nome della funzione, compare in neretto nella parte bassa della finestra la sua sintassi ed una breve (e spesso incomprensibile…) descrizione della stessa.
• La seconda, a cui si accede con un doppio clic sul nome della funzione o cliccando sul pulsante OK, presenta tante caselle di input quanti sono gli argomenti.

Se l'argomento da inserire è un riferimento ad una cella o ad un intervallo di celle, è possibile passare temporaneamente al foglio di calcolo (per selezionare l'intervallo col mouse o anche solo per verificare i riferimenti) cliccando sul pulsante con la freccetta rossa posto all'estremità destra della casella. La finestra viene ridotta alla sola casella di input. Per tornare alla finestra intera basta cliccare nuovamente sul pulsante all'estremità destra della casella.
Definito l'argomento, a destra della casella compare il suo contenuto, per un'eventuale verifica.
Se necessitano ulteriori informazioni, nella parte bassa della finestra c'è una breve descrizione della natura dell'argomento da inserire.
Per finire, nella finestra viene visualizzato il risultato finale della formula, consentendo così una sua verifica prima della definitiva immissione.
Il pulsante a forma di ? consente un rapido accesso alla guida in linea sulla formula.

Funzioni nidificate
È possibile utilizzare le funzioni come argomenti di altre funzioni.
Nella formula seguente ad esempio la funzione MEDIA è nidificata (funzione di secondo livello).

 =SE(A5>=MEDIA(Incassi);A5*20%;"Incasso insufficiente")

Una formula può contenere fino a sette livelli di funzioni nidificate.
Utilizzo delle funzioni più comuni
Di seguito vengono presentate e commentate solo alcune delle funzioni di uso più comune (in tutto sono più di 250!), in quanto una trattazione completa esula dalle finalità di un corso di base.
Le descrizioni delle funzioni sono tratte dalla guida in linea di Excel. I termini usati per la denominazione degli argomenti sono quelli ufficiali riportati dalla guida in linea stessa. Ci si scusa col lettore per la loro frequente ermeticità….
Gli argomenti delle funzioni sono scritti in corsivo. Per distinguere gli argomenti obbligatori da quelli opzionali nella descrizione della sintassi, i primi sono scritti in neretto mentre i secondi in caratteri normali.
Chi volesse approfondire l'argomento (in particolare gli studenti di materie economiche o scientifiche) potrà farlo valendosi della guida in linea di Excel o di pubblicazioni specifiche sull'argomento.
Alcuni esempi di utilizzo delle funzioni più comuni sono contenuti nel file Funzioni.xls.

 

Per comodità di trattazione le funzioni sono raggruppate nelle categorie proposte da Excel.

FUNZIONI DI TESTO
Le funzioni di testo consentono di gestire stringhe di testo, ad esempio concatenando due o più stringhe, estraendo alcuni caratteri da un testo, modificando l'impostazione maiuscolo-minuscolo o determinando la lunghezza di una stringa di testo.
NB. Le stringhe di testo usate come costanti devono essere racchiuse tra virgolette doppie.

Le funzioni di testo più comuni sono:

 

CONCATENA()
Unisce diversi elementi di testo in uno solo.

Sintassi
CONCATENA(testo1;testo2;testo3;...)
Testo1;testo2;...   sono da 1 a 30 elementi di testo da unire in un unico elemento.
Gli elementi di testo possono essere stringhe di testo, numeri o riferimenti di celle singole.

Osservazioni
Al posto della funzione CONCATENA(), per unire gli elementi di testo può venire utilizzato molto più semplicemente l'operatore "&".

Esempi
CONCATENA("Valore";" ";"totale") restituisce "Valore totale"
Questo equivale a digitare "Valore"&" "&"totale".
(Notare lo spazio dopo la parola "Valore".

Si supponga che in un foglio di lavoro la cella C2 contenga la stringa "altezze" e la cella C18 contenga il valore 32.
CONCATENA("La media delle ";C2;" è uguale a ";C18) darà come risultato:
"La media delle altezze è uguale a 32".
(Notare in questo caso l'inserimento dello spazio nel testo stesso.)
LUNGHEZZA()
Restituisce il numero di caratteri in una stringa di testo.

Sintassi
LUNGHEZZA(testo)
Testo è il testo (come costante o come contenuto di una cella) di cui si desidera conoscere la lunghezza. Gli spazi vengono considerati come caratteri.

Esempio
LUNGHEZZA("20100 Milano") è uguale a 12

 

SINISTRA()
Restituisce il primo carattere o i primi caratteri di una stringa di testo partendo da sinistra.

Sintassi
SINISTRA(testo;num_caratt)
Testo è la stringa di testo che contiene i caratteri che si desiderano estrarre.
Num_caratt specifica il numero di caratteri che si desidera vengano estratti a partire dal primo carattere a sinistra della stringa.

• Num_caratt deve essere maggiore o uguale a zero.
• Se num_caratt è maggiore della lunghezza di testo, SINISTRA() restituirà l'intero testo.
• Se num_caratt è omesso, verrà considerato uguale a 1.
• Se num_caratt è uguale a 0, verrà restituita una stringa vuota ("").
• Se num_caratt è negativo, verrà restituita il valore di errore #VALORE!.

Esempi
SINISTRA("Prezzo di vendita";6) è uguale a "Prezzo"
Se A1 contiene la stringa "Svezia":
SINISTRA(A1) è uguale a "S"

 

DESTRA()
Restituisce l'ultimo carattere o gli ultimi caratteri di una stringa di testo a partire da destra.

Sintassi
DESTRA(testo;num_caratt)
Per la descrizione degli argomenti, vale quanto detto per la funzione SINISTRA().

Esempio
DESTRA("Prezzo di vendita"; 7) è uguale a "vendita"
STRINGA.ESTRAI()
Restituisce il numero richiesto di caratteri di una stringa a partire dalla posizione specificata.

Sintassi
STRINGA.ESTRAI(testo;inizio;num_caratt)
Testo è la stringa di testo che contiene i caratteri che si desiderano estrarre.
Inizio è la posizione del primo carattere che si desidera estrarre da testo. Il valore di inizio per il primo carattere in testo è uguale a 1 e così via.

• Se inizio è maggiore della lunghezza di testo, STRINGA.ESTRAI restituirà  "" (testo vuoto).
• Se inizio è minore della lunghezza di testo, ma inizio e num_caratt superano complessivamente la lunghezza di testo, STRINGA.ESTRAI restituirà tutti i caratteri fino alla fine di testo.
• Se inizio è minore di 1, STRINGA.ESTRAI restituirà il valore di errore   #VALORE!.

Num_caratt specifica il numero di caratteri che devono essere restituiti da testo, a partire da inizio.
Se num_caratt è negativo, STRINGA.ESTRAI restituirà il valore di errore   #VALORE!.

Esempi
STRINGA.ESTRAI("Flusso instabile";1;6) è uguale a "Flusso"
STRINGA.ESTRAI("Flusso instabile";8;16) è uguale a "instabile"
STRINGA.ESTRAI("1234";5;5) è uguale a "" (testo vuoto)

 

MAIUSC() (e MINUSC())
Converte tutto il testo in caratteri maiuscoli (o, rispettivamente, minuscoli).

Sintassi
MAIUSC(testo)
Testo è il testo che si desidera convertire in maiuscolo.

Esempio
MAIUSC("totale") è uguale a "TOTALE"

Per analogia la funzione MINUSC() viene usata per convertire una stringa in caratteri minuscoli.

 

ANNULLA.SPAZI()
Rimuove tutti gli spazi superflui dal testo ad eccezione degli spazi singoli tra le parole.

Sintassi
ANNULLA.SPAZI(testo)
Testo è il testo da cui si desidera rimuovere gli spazi.

Esempio
ANNULLA.SPAZI(" Utili     primo     trimestre     ") corrisponde a "Utili primo trimestre"
FUNZIONI MATEMATICHE
Sono le classiche funzioni dedicate ai calcoli matematici e trigonometrici, ma tra di esse sono comprese anche alcune tra le funzioni più importanti ed usate nei fogli di calcolo, come la funzione SOMMA(). ( )

 

SOMMA()
Somma tutti gli argomenti numerici o, in particolare, tutti i numeri presenti in un intervallo di celle.

Sintassi
SOMMA(num1;num2; ...)
Num1; num2;... sono da 1 a 30 argomenti numerici di cui si desidera il valore totale o somma.

• Gli argomenti devono essere numeri oppure riferimenti, nomi o matrici che contengono numeri.
• Anche se è possibile indicare più di un argomento, l'utilizzo più comune (direi quasi esclusivo) consiste nell'applicare la funzione SOMMA() ad un intervallo, cosa frequentissima in qualunque resoconto contabile.
• Se un argomento è costituito da una matrice o da un riferimento, verranno utilizzati solo i numeri presenti nella matrice o nel riferimento, mentre le celle vuote, i valori logici, il testo o i valori di errore verranno trattati come 0.
• Gli argomenti singoli rappresentati da valori di errore o da testo non convertibile in numeri determinano degli errori.

Esempi

Se le celle C1:C7 contengono rispettivamente: "Totale", "Vendite", 5, 15, 30, 40, 50, allora:
SOMMA(C3:C7) è uguale a 140
SOMMA(C1:C2) è uguale a 0  (perché le celle C1 e C2 vengono trattate come 0)
SOMMA(C1:C5) è uguale a 50  (perché le celle C1 e C2 vengono trattate come 0)
SOMMA(C2:C3;15) è uguale a 60
SOMMA(C2:C3;"totale") dà un errore  #VALORE!
SOMMA(3;2) è uguale a 5

Osservazioni
Anche se è possibile quindi usare la funzione SOMMA() per sommare due numeri, si consiglia di farlo solo in circostanze particolari e di utilizzare invece il classico e comodo simbolo "+".
Un cosa poi da non fare mai, ma che capita talvolta di vedere agli esami è scrivere ad esempio SOMMA(3+2) per ottenere 5. L'esempio si commenta da solo!

E' sempre meglio verificare il comportamento della funzione in presenza di celle vuote o contenenti testi.

ARROTONDA()
Arrotonda un numero ad un numero specificato di cifre.

Sintassi
ARROTONDA(num;num_cifre)
Num è il numero che si desidera arrotondare.
Num_cifre specifica il numero di cifre a cui si desidera arrotondare num.

• Se num_cifre è maggiore di 0 (zero), num verrà arrotondato al numero di decimali specificato.
• Se num_cifre è uguale a 0, num verrà arrotondato all'intero più vicino.
• Se num_cifre è minore di 0, num verrà arrotondato a sinistra della virgola.

Esempi
ARROTONDA(2,15;1) è uguale a 2,2
ARROTONDA(2,149;1) è uguale a 2,1
ARROTONDA(-1,475;2) è uguale a -1,48
ARROTONDA(321,5;-1) è uguale a 320
ARROTONDA(321,5;-2) è uguale a 300

Osservazioni
La funzione ARROTONDA() trasforma il numero di partenza in un altro numero arrotondato. Non va confusa quindi con l'opzione del menu Formato Cella Numero, mediante la quale è possibile visualizzare il numero desiderato di cifre decimali di un numero, mantenendo però inalterato il numero di partenza.

 

INT()
Restituisce la parte intera di un numero, arrotondandolo per difetto all'intero più vicino.

Sintassi
INT(num)
Num è il numero reale che si desidera arrotondare per difetto ad un intero.

Esempi
INT(8,74) è uguale a 8
INT(-8,74) è uguale a -9
A1-INT(A1)  restituisce la parte decimale di un numero reale positivo contenuto nella cella A1.

Osservazioni
Attenzione. L'utilizzo è abbastanza intuitivo per i numeri positivi, lo è un po’ meno per quelli negativi. Da usare quindi con cautela.

RESTO()
Restituisce il resto di una divisione tra due numeri specificati.
NB. Il segno del risultato coinciderà con quello di divisore.

Sintassi
RESTO(dividendo;divisore)
Dividendo è il numero da dividere.
Divisore è il numero per il quale si desidera dividere il dividendo.
Se divisore è 0, RESTO restituirà il valore di errore  #DIV/0!.

Osservazioni
Si richiama l'attenzione sul fatto che il modo di trattare i segni non è del tutto intuitivo (vedere gli esempi).

Come esercizio didattico si osservi che il risultato fornito dalla funzione RESTO() può essere ottenuto anche tramite la funzione INT():
RESTO(dividendo;divisore) = dividendo - divisore*INT(dividendo/divisore)

Esempi
RESTO(10;6) è uguale a 4
RESTO(3;2) è uguale a 1
RESTO(-3;2) è uguale a 1
RESTO(3;-2) è uguale a -1
RESTO(-3;-2) è uguale a -1

 

PI.GRECO()
Restituisce il numero 3,14159265358979, cioè il valore della costante matematica pi greco, con una precisione di 15 cifre.

Sintassi
PI.GRECO()

Osservazioni
E' un esempio di funzione senza argomenti.

Esempi
PI.GRECO()/2 è uguale a 1,57079...
SEN(PI.GRECO()/2) è uguale a 1
Se il raggio di un cerchio è memorizzato nella cella denominata Raggio, l'area del cerchio sarà determinata dalla seguente formula:
PI.GRECO()*(Raggio^2)

CONTA.SE()
Conta, di tutte le celle di un intervallo, solo quelle che soddisfano un criterio assegnato.

Sintassi
CONTA.SE(intervallo;criteri)
Intervallo è l'intervallo contenente le celle da contare.
Criteri sono i criteri in forma di numeri, espressioni, testo o riferimenti che determinano quali celle verranno contate. Ad esempio, criteri può essere espresso come 32, "32", ">32", "mele", D15.

Osservazioni
Purtroppo, contrariamente a quanto avviene per le funzioni logiche, le modalità con cui devono essere specificati i criteri non si presentano per nulla lineari. Suggerisco di attenersi ad una regola valida per tutte la situazioni (che ho trovato dopo penosi tentativi), salvo poi utilizzare le solite "scorciatoie" nei casi più frequenti di criteri che prevedono solo l'uguaglianza con dati o riferimenti.
La regola sarebbe quella di "costruire" la stringa del criterio concatenando l'operatore di confronto (racchiuso tra virgolette doppie) con il valore o il riferimento contenente il valore da confrontare.
E' macchinoso, ma funziona, anche se non lo trovate scritto da nessuna parte.
Esempi:
"="&100,   ">="&"mele",   "="&D15,   "<"&"100+50", ecc.
Prima eccezione: Nel caso di confronto con valori costanti, la stringa può essere già completa, contenere cioè sia l'operatore che la costante.
Esempi:
"=100",  ">100",  ">=100",  "=mele", "<100+50", ecc.
Seconda eccezione. Nel caso di uguaglianza può essere omesso il simbolo = e, nel caso di un numero, possono essere omesse le virgolette.
Esempi:
100, "mele".
Terza eccezione. Nel caso di uguaglianza con dati contenuti in celle di cui si indica il riferimento, devono essere omessi sia il simbolo = sia le virgolette.
Esempio:
D17

Si è cercato di riprodurre tutte le varianti nella tabella sottostante.
Nelle colonne sono riportate le diverse tipologie di confronto.
Nelle righe sono riportate le diverse tipologie di dati per il confronto.
I testi in grassetto indicano le scritture consigliate, quelli in caratteri normali le alternative a scelta.

	=	>	<	>=	<=
Numeri	100 "="&100,  "=100",  "100"	">100" ">"&100	"<100" "<"&100	">=100" ">="&100	"<=100" "<="&100
Testi	"Testo" "="& "Testo",  "=Testo"	">Testo" ">"&"Testo"	"<Testo" "<"&"Testo"	">=Testo" ">="&"Testo"	"<=Testo" "<="&"Testo"
Riferimenti	D15 "="&D15	">"&D15	"<"&D15	">="&D15	"<="&D15

Esempi
Si supponga che l'intervallo A3:A6 contenga rispettivamente la voci "mele", "arance", "pesche", "mele".
CONTA.SE(A3:A6;"mele") è uguale a 2
Si supponga che l'intervallo B3:B6 contenga rispettivamente i valori 32, 54, 75, 54 e che la cella D15 contenga il valore 50.
CONTA.SE(B3:B6;">=50") è uguale a 3; si ottiene lo stesso risultato con l'espressione
CONTA.SE(B3:B6;">="&D15)
CONTA.SE(B3:B6;54) è uguale a 2

 

SOMMA.SE()
Somma, di tutte le celle di un intervallo, solo quelle che soddisfano un criterio assegnato.

Sintassi
SOMMA.SE(intervallo;criteri;int_somma)
Intervallo è l'intervallo di celle su cui viene applicato il test indicato da criteri.
Criteri sono i criteri in forma di numeri, espressioni, testo o riferimenti che determinano quali celle verranno sommate. Ad esempio, criteri può essere espresso come 32, "32", ">32", "mele", D15 (v. Osservazioni della funzione CONTA.SE()).
Int_somma è l'intervallo che contiene i dati da sommare.

• Le celle in int_somma vengono sommate solo se le celle corrispondenti in intervallo soddisfano i criteri.
• Se int_somma è omesso, vengono sommate le celle in intervallo.

Esempio
Si supponga che l'intervallo A2:A5 contenga rispettivamente i seguenti valori patrimoniali relativi a quattro abitazioni: L. 100.000.000, L. 200.000.000, L. 300.000.000, L. 400.000.000. L'intervallo B2:B5 contiene le seguenti commissioni sulle vendite su ciascuno dei corrispondenti valori patrimoniali: L. 7.000.000, L. 14.000.000, L. 21.000.000, L. 28.000.000. In questo caso

SOMMA.SE(A2:A5;">160000000";B2:B5) è uguale a L. 63.000.000

	A	B	C	D	E
1	Valore vendita	Commissione
2	100000000	7000000
3	200000000	14000000
4	300000000	21000000		Totale commissioni per vendite >160000000:	63000000
5	400000000	28000000
6

 

Osservazioni
Per le osservazioni in merito alle modalità di scrittura dei criteri, si rimanda alla funzione CONTA.SE().
FUNZIONI STATISTICHE
Le funzioni statistiche consentono di eseguire analisi statistiche su intervalli di dati.

MEDIA()
Restituisce la media aritmetica degli argomenti.

Sintassi
MEDIA(num1; num2; ...)
Num1; num2;... sono da 1 a 30 argomenti numerici di cui si desidera calcolare la media.

• Gli argomenti devono essere numeri o nomi, matrici oppure riferimenti che contengono numeri.
• Se una matrice o un riferimento contiene testo, valori logici o celle vuote, tali valori vengono ignorati. Le celle contenenti il valore zero vengono invece incluse nel calcolo.

Osservazioni
Quando si utilizza la funzione MEDIA(), fare molta attenzione alla differenza esistente tra celle vuote e celle che contengono il valore zero, soprattutto nel caso in cui si sia scelto di non visualizzare gli zeri per motivi estetici (scelta vivamente sconsigliata ai neofiti, perché può generare confusione).
E' sempre meglio verificare il comportamento della funzione in presenza di celle vuote o contenenti testi.
Si osservi che il risultato ottenuto con la funzione MEDIA(), può essere ottenuto anche con l'utilizzo combinato delle funzioni SOMMA() e CONTA.NUMERI():
MEDIA(A1:A5)   è uguale a   SOMMA(A1:A5)/CONTA.NUMERI(A1:A5)

Esempi
Se l'intervallo A1:A5 viene denominato Punteggi e contiene i numeri 10, 7, 9, 27 e 2 (somma degli elementi: 55, numero degli elementi: 5, media=somma/numero elem.), allora
MEDIA(A1:A5) è uguale a 11
MEDIA(Punteggi) è uguale a 11 (come sopra)
MEDIA(A1:A5;5) è uguale a 10 (perché fa la media anche con il numero 5)
Se l'intervallo A1:A5 contiene dei testi e dei numeri, quali "Totali", 10, 7, 13 e 2 (la somma degli elementi è uguale a32, il numero degli elementi numerici è uguale a 4), allora
MEDIA(A1:A5) è uguale a 8
Se l'intervallo A1:A5 contiene solo celle vuote (somma=0, num.elementi=0), allora
MEDIA(A1:A5) dà il messaggio di errore #DIV/0!
Se tutte le celle dell'intervallo A1:A5 contengono il valore 0 (somma=0, num.elementi=5), allora
MEDIA(A1:A5) è uguale a 0

 

MAX()
Restituisce il numero più grande contenuto in un insieme di numeri.

Sintassi
MAX(num1;num2;...)
Num1;num2;... sono da 1 a 30 argomenti numerici di cui si desidera trovare il valore massimo.

• Gli argomenti devono essere numeri oppure riferimenti, nomi o matrici che contengono numeri.
• Anche se è possibile indicare più di un argomento, l'utilizzo più comune consiste nell'applicare la funzione MAX() ad un intervallo.
• Se un argomento è costituito da una matrice o da un riferimento, verranno utilizzati solo i numeri presenti nella matrice o nel riferimento, mentre le celle vuote, i valori logici, il testo o i valori di errore verranno ignorati.
• Gli argomenti singoli rappresentati da valori di errore o da testo non convertibile in numeri determinano degli errori.

Esempi
Se l'intervallo A1:A5 contiene i numeri 10, 7, 9, 27 e 2:
MAX(A1:A5) è uguale a 27
MAX(A1:A5;30) è uguale a 30

 

MIN()
Restituisce il numero più piccolo contenuto in un insieme di numeri.

Sintassi
MIN(num1;num2;...)
Num1; num2;...   sono da 1 a 30 numeri di cui si desidera trovare il valore minimo.

MIN è simile alla funzione MAX(), cui si rimanda per ulteriori chiarimenti.

 

CONTA.NUMERI()
Conta i numeri presenti nell'elenco degli argomenti o, in particolare, il numero di celle contenenti numeri presenti in un intervallo.

Sintassi
CONTA.NUMERI(num1;num2; ...)
Num1; num2; ... sono da 1 a 30 argomenti che possono contenere anche dati di diverso tipo, di cui però vengono contati soltanto i numeri.

• Nel conteggio vengono inclusi solo argomenti rappresentati da numeri o date. Non vengono invece considerati gli argomenti rappresentati da valori di errore o da testo.
• Se un argomento è un riferimento o una matrice, vengono contati soltanto i numeri in essi contenuti. Le celle vuote, i valori logici, il testo o i valori di errore della matrice o del riferimento verranno ignorati. Se è necessario contare valori logici, testo o valori di errore, utilizzare la funzione CONTA.VALORI.

Esempi

 

Se l'intervallo A1:A7 contiene i seguenti dati: "Vendite", 31/12/1999, cella vuota, 19, 22.53, VERO, #DIV/0!, allora:
CONTA.NUMERI(A1:A7) è uguale a 3
CONTA.NUMERI(A4:A7) è uguale a 2
CONTA.NUMERI(A1:A7;158) è uguale a 4

 

CONTA.VALORI()
Conta il numero di celle non vuote e di valori presenti nell'elenco degli argomenti. Utilizzare la funzione CONTA.VALORI per contare il numero di celle non vuote, cioè contenenti qualunque tipo di dato in un intervallo o in una matrice.

Sintassi
CONTA.VALORI(val1;val2; ...)
Val1; val2; ... sono da 1 a 30 argomenti che rappresentano i valori che si desiderano contare.

• Un valore può essere rappresentato da qualsiasi tipo di informazione, incluso del testo vuoto (""), ma non da celle vuote.
• Se un argomento è un riferimento o una matrice, le celle vuote all'interno del riferimento (o della matrice verranno ignorate.
• Se non è si vogliono contare valori logici, di testo o valori di errore, utilizzare la funzione CONTA.NUMERI().

 

Esempi
Se l'intervallo A1:A7 contiene i seguenti dati: "Vendite", 31/12/1999, cella vuota, 19, 22.53, VERO, #DIV/0!, allora:
CONTA.VALORI(A1:A7) è uguale a 6
CONTA.VALORI(A4:A7) è uguale a 4
CONTA.VALORI(A1:A7;2) è uguale a 7
CONTA.VALORI(A1:A7;"Due") è uguale a 7

 

Funzioni statistiche avanzate (per studenti di Economia).

MEDIANA()
Restituisce la mediana dei numeri specificati.

Sintassi
MEDIANA(num1;num2;...)
Num1;num2;... sono da 1 a 30 argomenti di cui si desidera calcolare la mediana.

• Gli argomenti devono essere numeri oppure riferimenti, nomi o matrici che contengono numeri.
• L'utilizzo tipico è appicare la funzione MEDIANA() ai numeri di un intervallo.
• Vengono esaminati tutti i numeri contenuti in ogni argomento, riferimento o matrice.
• Le celle vuote o contenenti testo o valori logici vengono ignorate.
• Le celle contenenti il valore zero vengono invece incluse nel calcolo.

Esempi
MEDIANA(1; 2; 3; 4; 5) è uguale a 3
MEDIANA(1; 2; 3; 4; 5; 6) è uguale a 3,5, vale a dire la media di 3 e 4

 

MODA()
Restituisce il valore più ricorrente, o ripetitivo, di una matrice o di un intervallo di dati. Analogamente a MEDIANA, la funzione MODA è una misura relativa alla posizione dei valori.

Sintassi
MODA(num1;num2;...)
Num1;num2;... sono da 1 a 30 argomenti di cui si desidera calcolare la moda.

• Gli argomenti devono essere numeri oppure riferimenti, nomi o matrici che contengono numeri.
• L'utilizzo tipico è applicare la funzione MEDIANA() ai numeri di un intervallo.
• Vengono esaminati tutti i numeri contenuti in ogni argomento, riferimento o matrice.
• Le celle vuote o contenenti testo o valori logici o vengono ignorate.
• Le celle contenenti il valore zero vengono invece incluse nel calcolo.
• Se l'insieme dei dati non contiene valori ripetuti, MODA() restituirà il valore di errore #N/D.

Osservazioni
La funzione MODA() non si adatta completamente alle esigenze della teoria statistica, in quanto:

• non è in grado di individuare le serie plurimodali e indica sempre un unico risultato. Non solo, il numero restituito è quello che compare per primo nella sequenza, tanto che basta cambiarne l'ordine per avere un altro risultato;
• non è in grado di segnalare il caso anomalo in cui tutti i numeri si presentino con la stessa frequenza.

Per un approfondimento di tali problematiche si veda il file di esercizio Medie.xls.
Esempi
MODA(5,6;4;4;3;2;4) è uguale a 4
MODA(2,6;4;4;5;5;5;4) è uguale a 4, perché compare prima (e invece la moda è 4 e 5)
MODA(5,6;4;4;5;3;5;4) è uguale a 5, perché compare prima (e invece la moda è 4 e 5)
MODA(5,6;4;4;5;6) è uguale a 5, perché compare prima (e invece la moda non esiste)

 

FREQUENZA()
Calcola la frequenza di occorrenza di una serie di numeri riferita a dei limiti di classe specificati.

Sintassi
FREQUENZA(matrice_dati;matrice_classi)
Matrice_dati è un intervallo verticale di celle contenente l'insieme di valori di cui si desidera calcolare la frequenza. Se matrice_dati non contiene alcun valore, FREQUENZA() restituirà una matrice di zeri.
Matrice_classi è un intervallo verticale di celle contenenti i limiti superiori delle classi in cui si desidera raggruppare i valori contenuti in matrice_dati. Se matrice_classi non contiene alcun valore, FREQUENZA() restituirà il numero degli elementi contenuti in matrice_dati.

Osservazioni
FREQUENZA() viene immessa come formula matrice dopo aver selezionato un intervallo verticale di celle nel quale dovrà apparire il risultato (matrice dei risultati).
Il numero di celle contenute nella matrice dei risultati deve essere maggiore di una unità rispetto al numero di celle contenute in matrice_classi. Le eventuali celle in più conterranno l'errore #ND!.
FREQUENZA() ignora le celle vuote e i testi.

Esempio
Si supponga che nell'intervallo di celle A1:A9 di un foglio di lavoro siano elencati i seguenti punteggi: 79, 85, 78, 85, 83, 81, 95, 88 e 97. Questo intervallo costituisce la matrice_dati.
Si supponga di voler raggruppare questi punteggi nelle classi seguenti: 0-70; 71-80; 81-90, 91-oltre. Bene, i limiti superiori di tali classi (cioè i numeri 70, 80, 90) dovranno essere inseriti in un altro intervallo verticale (ad esempio C4:C6), che viene denominato matrice_classi.
La formula dell'esempio sottostante va inserita come formula matrice in un altro intervallo verticale, ad es. nell'intervallo F4:F7 (matrice dei risultati), più lungo di una cella rispetto alla matrice_classi.
{FREQUENZA(A1:A9;C4:C6) } è uguale a {0;2;5;2}

• Per le classi intermedie il computo della frequenza viene fatto dal limite precedente escluso al limite successivo incluso (in questo esempio  >70 ¸ <=80).
• Per la prima classe il conteggio viene fatto da   - infinito  al primo limite incluso (nell'es. <=30).
• Per l'ultima classe (cella in più rispetto a matrice_classi) il conteggio va dall'ultimo limite escluso all'infinito (nell'es. >70).

FUNZIONI LOGICHE
Le funzioni logiche vengono utilizzate per stabilire se una condizione è vera o falsa o per verificare più condizioni e, nel caso della funzione SE(), per comportarsi di conseguenza.

SE()
Restituisce un valore se la condizione specificata ha valore VERO e un altro valore se essa ha valore FALSO.
Utilizzare la funzione SE per eseguire dei test condizionali su valori e formule.

Sintassi
SE(test;se_vero;se_falso)
Test è un valore o un'espressione qualsiasi che può dare come risultato VERO o FALSO.
Se_vero è il valore che viene restituito se test è VERO. Se_vero può anche essere un'altra formula.
Se_falso è il valore che viene restituito se test è FALSO. Se_falso può anche essere un'altra formula. Se test è FALSO e se se_falso è omesso, verrà restituito il valore FALSO.

Osservazioni

• Se alcuni degli argomenti se_vero e se_falso sono funzioni che eseguono operazioni, verranno eseguite tutte le operazioni e SE() restituirà il valore calcolato.
• È possibile nidificare fino a sette funzioni SE() come argomenti se_vero e se_falso in modo da creare test più elaborati. Vedere l'ultimo degli esempi che seguono.

Esempi
Nel seguente esempio, se il valore contenuto nella cella A10 è 100, test sarà VERO e verrà calcolata la somma di tutti i valori contenuti nell'intervallo B5:B15. In caso contrario, test sarà FALSO e verrà restituita la stringa vuota, in modo che la cella contenente la funzione SE() si presenti come una cella vuota.
SE(A10=100,SOMMA(B5:B15),"")

Si supponga di voler assegnare un gruppo di lettere ai numeri (compresi tra 0 e 100) ai quali si riferisce il nome Punteggio secondo la seguente tabella.

se Punteggio è	restituirà
minore di 20	A
compresa tra 20 (incluso) e 40 (escluso)	B
compresa tra 40 (incluso) e 60 (escluso)	C
compresa tra 60 (incluso) e 80 (escluso)	D
compresa tra 80 (incluso) e 100	F

Si possono utilizzare le seguenti funzioni SE nidificate:
SE(Punteggio<20;"A";SE(Punteggio<40;"B";SE(Punteggio<60;"C"; SE(Punteggio<80;"D";"F"))))
La seconda istruzione SE() è anche l'argomento se_falso della prima istruzione SE().
Analogamente, la terza istruzione SE() è l'argomento se_falso della seconda istruzione SE().
Ad esempio, se il primo test (Punteggio<20) è VERO, verrà restituito "A". Se il primo test è FALSO (il numero è >=20), verrà calcolata la seconda istruzione SE() e così via. L'ultima classe (F) risulta per esclusione dal non verificarsi di tutti i casi precedenti, trattandosi dell'argomento se_falso dell'ultima funzione SE().
Si ricordi che è possibile arrivare fino a 7 livelli di nidificazione, e comunque la formula che ne deriva può diventare complessa. Se i casi da trattare sono più numerosi e se gli argomenti se_vero e se_falso sono costanti, conviene utilizzare la funzione CERCA.VERT().

 

E(), O() e NON()
Consentono di predisporre test più elaborati, applicando le funzioni booleane AND, OR e NOT. Vengono comunemente utilizzate per estendere le potenzialità della funzione SE().

Osservazioni

• Gli argomenti  possono essere da 1 a 30 (1 solo per la funzione NON()) e devono dare come risultato valori logici, quali VERO o FALSO, oppure devono essere riferimenti a celle che contengono valori logici.
• Se un riferimento contiene testo o celle vuote, tali valori vengono ignorati.
• Se l'intervallo specificato non contiene valori logici, le funzioni E(), O() e NON() restituiscono il valore di errore #VALORE!.

Sintassi

E(logico1;logico2; ...)
Restituisce VERO se tutti gli argomenti hanno valore VERO e restituisce FALSO se uno o più argomenti hanno valore FALSO.

O(logico1;logico2; ...)
Restituisce VERO se uno o più argomenti hanno valore VERO e restituisce FALSO se tutti gli argomenti hanno valore FALSO.

NON(logico)
Inverte il valore logico dell'argomento. Utilizzare NON() per assicurarsi che un dato valore non coincida con un altro. (Usare la funzione IDENTICO() in caso di testo).

 

Esempi

Valori della cella B5	Risultato della formula: =E(B5>50;B5<100)	Risultato della formula: =O(B5<50;B5>100)
20	FALSO	VERO
70	VERO	FALSO
150	FALSO	VERO
Valori dell'intervallo B1:B4	Risultato della formula: =E(B1:B4)	Risultato della formula: =O(B1:B4)
VERO, VERO, VERO, VERO	VERO	VERO
VERO, VERO, FALSO, VERO	FALSO	VERO
FALSO, FALSO, FALSO, FALSO	FALSO	FALSO
Valori della cella B1	Risultato della formula: =NON(B1)
VERO/FALSO	FALSO/VERO

FUNZIONI DATA E ORA
Queste funzioni consentono di analizzare e utilizzare valori data e ora nelle formule.

 

DATA()
Calcola il numero seriale corrispondente alla data indicata da anno, mese e giorno.

Sintassi
DATA(anno;mese;giorno)
Anno è un numero compreso tra 1900 e 9999.
Mese è un numero che rappresenta il mese dell'anno. Se mese è maggiore di 12, aggiungerà quel numero di mesi al primo mese dell'anno specificato. DATA(90;14;2), ad esempio, restituirà il numero seriale che rappresenta il 2 febbraio 1991.
Giorno è un numero che rappresenta il giorno del mese. Se giorno è maggiore del numero di giorni del mese specificato, aggiungerà quel numero di giorni al primo giorno del mese successivo. DATA(91;1;35), ad esempio, restituirà il numero seriale che rappresenta il 4 febbraio 1991.

Osservazioni

• Il sistema di data predefinito di Microsoft Excel per Windows è diverso da quello di Microsoft Excel per Macintosh., per il quale anno è un numero compreso tra il 1904 ed il 9999.
• La funzione DATA risulta particolarmente utile nei calcoli finanziari e nei casi in cui anno, mese e giorno sono risultati di formule e non delle costanti.

Esempio
DATA(91;1;1) sarà uguale a 33239, il numero seriale che corrisponde all'1 gennaio 1991.

 

ADESSO
Restituisce il numero decimale della data e dell'ora correnti, desumendoli dall'orologio interno del computer.

Sintassi
ADESSO()
La funzione ADESSO() apporta la modifica ogni volta che si ricalcola il foglio di lavoro o si esegue una macro contenente la funzione . L'aggiornamento non viene quindi effettuato continuamente.

Esempio

Data e ora di sistema	=ADESSO()
01 gennaio 1987  12:00	31778,5
01 gennaio 1987  12:40	31778,5277778

GIORNO()
Restituisce il giorno del mese corrispondente ad un numero seriale. I giorni vengono rappresentati con numeri interi compresi tra 1 e 31.

Sintassi

 

GIORNO(num_seriale)
Num_seriale è il codice data-ora utilizzato da Microsoft Excel per il calcolo della data e dell'ora.
È possibile (anche se decisamente poco usato) immettere num_seriale in formato testo, ad esempio "15/04/93" o "15_apr_1993", anziché in formato numerico. Il testo viene convertito automaticamente in numero seriale.

Esempi
GIORNO(B7) è uguale a 4 se B7 contiene la data 04/03/2000 oppure il numero 36589
GIORNO(35625) è uguale a 14, in quanto il numero 35625 corrisponde alla data 14/07/1997
GIORNO("11/08/93") è uguale a 11

Funzioni analoghe sono:

MESE()
Restituisce il numero del mese corrispondente ad un numero seriale. I mesi vengono rappresentati con numeri interi compresi tra 1 e 12.

ANNO()
Restituisce il numero dell'anno corrispondente ad un numero seriale. Per il metodo di numerazione degli anni, vedere la funzione DATA().

GIORNO.SETTIMANA()
Restituisce il giorno della settimana corrispondente a num_seriale.

Sintassi
GIORNO.SETTIMANA(num_seriale;tipo_restituito)
Num_seriale è il codice data-ora utilizzato da Microsoft Excel per il calcolo della data e dell'ora.
Tipo_restituito è un numero che determina il tipo di valore restituito.

1. Tipo 1 o omesso:  restituisce un numero da 1 (domenica) a 7 (sabato)
2. Tipo 2:                  restituisce un numero da 1 (lunedì) a 7 (domenica)
3. Tipo 3:                  restituisce un numero da 0 (lunedì) a 6 (domenica)

Esempi
Se B7 contiene il numero 35627, pari alla data 16/07/1997, che corrispondeva ad un mercoledì:
GIORNO.SETTIMANA(B7;1) è uguale a 4
GIORNO.SETTIMANA(B7;2) è uguale a 3
GIORNO.SETTIMANA(B7;3) è uguale a 2

FUNZIONI DI RICERCA E RIFERIMENTO
Vengono utilizzate per trovare valori all'interno di elenchi o tabelle.

 

CERCA.VERT()
Cerca un valore nella prima colonna a sinistra di una tabella e, una volta individuata la riga in cui tale valore si trova, restituisce il valore che si trova in una colonna specificata. Utilizzare la funzione CERCA.VERT() quando i dati sono organizzati in una tabella verticale, altrimenti utilizzare la funzione CERCA.ORIZZ().

Sintassi
CERCA.VERT(valore;tabella_matrice;indice;intervallo)
Valore è il valore da ricercare nella prima colonna della matrice.
Valore può essere una costante (testo, nunero o valore logico) o un riferimento.
Tabella_matrice è la tabella di informazioni nella quale vengono cercati i dati. Per definirla, utilizzare un riferimento ad un intervallo oppure un nome di intervallo.

1. I valori nella prima colonna di tabella_matrice devono essere disposti in ordine crescente: ...; -2; -1; 0; 1; 2; ...; A-Z; FALSO; VERO. In caso contrario, CERCA.VERT potrebbe non restituire il valore corretto.
2. L'intervallo tabella_matrice deve comprendere solo l'area dati e non l'eventuale prima riga contenente le etichette o intestazioni di colonna.
3. La funzione non rileva le maiuscole.

Indice è il numero di colonna nella tabella_matrice dalla quale deve essere restituito il valore corrispondente.

1. Se indice è uguale a 1 la funzione restituisce il valore nella prima colonna di tabella_matrice, se indice è uguale a 2 restituisce il valore nella seconda colonna di tabella_matrice e così via.
2. Se indice è minore di 1, CERCA.VERT() restituirà il valore di errore #VALORE!.
3. Se indice è maggiore del numero di colonne della tabella_matrice, CERCA.VERT() restituirà il valore di errore #RIF!.

Intervallo è un valore logico che specifica il tipo di ricerca che CERCA.VERT() dovrà eseguire.

1. Se intervallo è VERO (o 1) o è omesso, CERCA.VERT() cercherà una corrispondenza approssimativa, nel senso che la ricerca si fermerà sull'ultimo valore precedente quello cercato (definito da valore). In questo caso quindi viene restituito sempre e comunque un dato.
2. Se intervallo è FALSO (o 0), CERCA.VERT() cercherà una corrispondenza esatta. Qualora non venisse trovata alcuna corrispondenza, verrà restituito il valore di errore #N/D.
3. In tutti e due i casi, se il valore cercato è minore del primo valore della prima colonna di tabella_matrice, verrà restituito il valore di errore #N/D.

Esempi
Data la seguente tabella di dati, che per comodità viene chiamata Listino (in azzurro):

	A	B	C	D	E	F	G
1	Codice	Descrizione	Prezzo
2	10	Mele	1000
3	20	Pere	2000
4	30	Carote	3000
5
6

CERCA.VERT(10;Listino;2;VERO) è uguale a "Mele" (anche se la ricerca viene fatta con intervallo VERO, il valore cercato (10) esiste e quindi viene usato quello).
CERCA.VERT(10;Listino;2,1) è uguale a "Mele" (Al posto di VERO si può scrivere 1).
CERCA.VERT(10;Listino;2) è uguale a "Mele" (VERO si può omettere).
CERCA.VERT(10;Listino;2;FALSO) è uguale a "Mele" (trova una corrispondenza esatta).
CERCA.VERT(10;Listino;2;0) è uguale a "Mele" (al posto di FALSO si può scrivere 0).
CERCA.VERT(25;Listino;3;1) è uguale a 2000 (si ferma su 20, l'ultimo valore che precede 25).
CERCA.VERT(25;Listino;3;0) è uguale a #N/D (non trova la corrispondenza con intervallo FALSO).
CERCA.VERT(20;Listino;1;0) è uguale a 20 (restituisce il dato della colonna 1).
CERCA.VERT(20;Listino;5;1) è uguale a #RIF! (non esiste la colonna 5).

 

INDICE()
Restituisce un valore o un riferimento all'interno di una tabella. La funzione INDICE() dispone di due sintassi: matrice e riferimento. La sintassi 1 (matrice) restituisce sempre un valore o una matrice di valori, mentre la sintassi 2 (riferimento) restituisce sempre un riferimento. In questo contesto verrà esaminata solo la sintassi 1 (matrice).

Sintassi 1
INDICE(matrice;riga;col)
Restituisce il valore di un elemento in una tabella, selezionato dagli indici di riga e di colonna.
Matrice è un intervallo di celle contenente dati.
Riga seleziona la riga in matrice dalla quale restituire un valore. Se riga è omesso, sarà necessario specificare col.
Col seleziona la colonna in matrice dalla quale restituire un valore. Se col è omesso, sarà necessario specificare riga.

Osservazioni

1. Riga e col devono fare riferimento ad una cella all'interno di matrice. In caso contrario, INDICE restituirà il valore di errore #RIF!.
2. Se vengono utilizzati entrambi gli argomenti riga e col (caso più frequente), INDICE() restituirà il valore nella cella all'intersezione di riga e col.
3. Se matrice contiene solo una riga o una colonna, l'argomento riga o col corrispondente sarà facoltativo.
4. Se matrice contiene più righe e più colonne e viene utilizzato soltanto riga o col, INDICE restituirà una matrice dell'intera riga o dell'intera colonna in matrice.
5. E' quindi una funzione molto comoda, ma limitata dal fatto che presuppone la conoscenza della riga in cui si trova il dato richiesto.

Esempi
Data la seguente tabella di dati, che per comodità viene chiamata Listino (in azzurro):

	A	B	C	D	E	F	G
1	Codice	Descrizione	Prezzo
2	10	Mele	1000
3	20	Pere	2000
4	30	Carote	3000
5
6

INDICE(Listino;2;3) è uguale a 2000.
INDICE(Listino;3;2) è uguale a "Carote".

 

FUNZIONI DI DATABASE
Le funzioni di database (chiamate anche DB.funzione) sono utilizzate per l'analisi dei dati memorizzati in elenchi o database di Excel. Una di esse in particolare offre una valida alternativa alle funzioni di ricerca già esaminate (CERCA.VERT() e INDICE()).

 

DB.VALORI()
Estrae da una colonna in un elenco o database un singolo valore che soddisfa le condizioni specificate.

Sintassi
DB.VALORI(database;campo;criteri)
Database è l'intervallo di celle che costituiscono l'elenco o database.

1. Un database di Microsoft Excel è un elenco di dati correlati in cui le righe di informazioni costituiscono i record e le colonne di dati costituiscono i campi.
2. La prima riga del database deve contenere le etichette relative a ciascuna colonna e deve essere compresa nell'eventuale nome di intervallo assegnato al database.
3. Non ci devono essere righe o colonne vuote all'interno del database.
4. Non ci devono essere altri dati contigui all'intervallo del database (in altre parole ci devono essere almeno una riga ed una colonna vuote attorno al database).

Campo indica da quale colonna dell'elenco viene estratto il dato cercato.

1. I campi del database sono colonne di dati omogenei con un'etichetta che le contraddistingue indicata nella prima riga.
2. Campo può essere specificato come testo, utilizzando l'etichetta di colonna racchiusa tra virgolette doppie, (ad es. "Età" o "Prezzo"), oppure come un numero che rappresenta la posizione della colonna all'interno dell'elenco (1 per la prima colonna, 2 per la seconda ecc.).

Criteri è un intervallo di celle contenente le condizioni in base alle quali la funzione ricerca il dato richiesto.

1. È possibile utilizzare come argomento di criteri un intervallo qualsiasi, purché includa almeno una delle etichette di colonna del database e almeno una cella immediatamente sotto l'etichetta di colonna in cui specificare la condizione.
2. Il riferimento di criteri può essere immesso sia come intervallo di celle (es. A1:F2), sia con un nome rappresentante l'intervallo (es. "Criteri").
3. È possibile teoricamente collocare l'intervallo di criteri in qualsiasi posizione sul foglio di lavoro, ma, onde evitare sovrapposizioni o interferenze indesiderate, si consiglia vivamente di posizionarlo in un foglio distinto.
4. Assicurarsi che l'intervallo dei criteri non si sovrapponga all'elenco.

Osservazioni

1. Se nessun record soddisfa i criteri, DB.VALORI restituirà il valore di errore #VALORE!.
2. Se più record soddisfano i criteri, DB.VALORI restituirà il valore di errore #NUM!.
3. Si noti che questa funzione funziona come la funzione CERCA.VERT() in modalità ricerca esatta, con la grossa differenza che la ricerca può essere estesa a qualunque elemento di qualunque colonna e non solo della prima.

Esempi
L'intervallo A1:D6 di un foglio di calcolo contiene un elenco di prodotti, cui viene assegnato il nome Listino (in azzurro). Tale elenco può essere considerato un database, in quanto:

1. Le sue righe e colonne possono essere interpretate come record e campi di un database;
2. Ogni colonna ha nella prima riga un'etichetta che funge da nome del campo;
3. In ogni campo i dati sono omogenei, cioè della stessa natura;
4. Non ci sono né righe né colonne vuote all'interno dell'intervallo;
5. Attorno all'intervallo ci sono una riga (7) ed una colonna (E) completamente vuote.

L'intervallo F3:F4 viene invece chiamato intervallo dei criteri e ad esso assegnamo il nome Criterio (in verde). Si ricordi che, per essere un intervallo di criteri, bisogna che:

1. La prima cella contenga esattamente uno dei nomi dei campi (per sicurezza conviene copiarlo);
2. La cella immediatamente sottostante contenga il valore da cercare nel campo.

	A	B	C	D	E	F	G
1	Codice	Prodotto	Prezzo	Scorta
2	100	Viti	20	500
3	200	Bulloni	15	1000		Prezzo
4	300	Dadi	10	12000		15
5	600	Chiodi	14	8800
6	400	Tasselli	10	4000
7
8

Con i valori dell'esempio,
DB.VALORI(Listino;"Scorta";Criterio) restituisce 1000.
Si noti la comodità offerta dall'uso dei nomi, rispetto alla formula seguente:
DB.VALORI(A1:D6;4;F3:F4), che pure restituisce 1000.
Si noti ancora che i nomi degli intervalli vanno senza virgolette doppie (essendo in fondo dei riferimenti), mentre quello del campo sì (può essere usato al posto del numero di colonna).

Se nella cella F4 viene inserito il valore 18 (non presente nel campo Prezzo)
DB.VALORI(Listino;"Scorta";Criterio) restituisce l'errore #VALORE!
Se nella cella F4 viene inserito il valore 10 (presente più volte nel campo Prezzo)
DB.VALORI(Listino;"Scorta";Criterio) restituisce l'errore #NUM!

 

FUNZIONI INFORMATIVE
Le funzioni informative servono per verificare il tipo di dati contenuto in una cella.

 

Funzioni VAL
Tra le funzioni informative esiste un gruppo, denominato Funzioni VAI, ciascuna delle quali esamina il tipo di valore e restituisce VERO o FALSO a seconda dell'esito. Tra le più significative ci sono quelle che consentono di verificare se una cella contiene un valore di errore, in modo da intercettarlo ed evitarne la propagazione nelle celle dipendenti.

Sintassi
VAL.ERR(val)
Val si riferisce ad un qualsiasi valore di errore eccetto #N/D.
VAL.ERRORE(val)
Val si riferisce ad un qualsiasi valore di errore (#N/D, #VALORE!, #RIF!, #DIV/0!, #NUM!, #NOME? o #NULLO!).
VAL.NON.DISP(val)
Val si riferisce al valore di errore #N/D (valore non disponibile).

Esempio
Si supponga di voler calcolare la media dell'intervallo A1:A4, ma di non poter essere certi che le celle contengano dei numeri. Se A1:A4 non contiene alcun numero, la formula MEDIA(A1:A4), inserita ad es. nella cella A6, restituirà il valore di errore #DIV/0!. Un test del tipo VAL.ERR(A6) restituirebbe quindi VERO. Per tenere conto di questa possibilità e per rendere più "umano" il messaggio di errore, nella cella A6 si può utilizzare la seguente formula per individuare errori potenziali:
SE(VAL.ERRORE(MEDIA(A1:A4));"Nessun numero";MEDIA(A1:A4))
(Cioè: Se il calcolo della media dà come risultato un errore, in quanto tutte le celle sono vuote, scrivi "Nessun numero", altrimenti calcola e visualizza la media dei numeri.)

 

DATABASE E FILTRI
Uno degli aspetti fondamentali dell'informatica è quello di disporre di banche dati da cui estrarre nel modo più efficiente possibile le informazioni che interessano. Anche in Excel è possibile gestire banche dati sotto forma di elenchi.
Per elenco si intende una tabella o una serie di righe del foglio di lavoro contenenti dati correlati, come un elenco di indirizzi, un listino prezzi, ecc.. È possibile considerare l'elenco come un database, in cui le righe rappresentano i record e le colonne i campi.
In Microsoft Excel sono disponibili varie caratteristiche che semplificano la gestione e l'analisi dei dati di un elenco. Per poter utilizzare tali caratteristiche, quando si immettono i dati in un elenco occorre attenersi alle istruzioni indicate di seguito:

Dimensione e posizione dell'elenco

1. Evitare di creare più elenchi su uno stesso foglio di lavoro. Alcune caratteristiche per la gestione degli elenchi, quali i filtri, possono infatti essere utilizzate su un solo elenco alla volta.
2. Evitare di inserire righe o colonne vuote nell'elenco per semplificare le operazioni automatiche di individuazione e selezione dell'elenco.
3. Lasciare almeno una colonna e una riga vuote tra l'elenco e gli altri dati del foglio di lavoro. In tal modo, quando si ordinano dei dati, si applica un filtro o si inseriscono dei subtotali automatici, le operazioni automatiche di individuazione e selezione dell'elenco risulteranno semplificate.
4. Evitare di inserire a sinistra o a destra dell'elenco dei dati importanti, che altrimenti potrebbero venire nascosti quando si applica un filtro all'elenco.
5. In conclusione è fortemente consigliato che il database si trovi da solo su un foglio di lavoro a lui dedicato; è buona norma inoltre che inizi proprio dalla cella A1.

Etichette di colonna

1. Creare delle etichette di colonna nella prima riga dell'elenco. Tali etichette costituiranno i nomi dei campi del database e verranno utilizzate per creare rapporti e per trovare e organizzare i dati.
2. Utilizzare per le etichette di colonna un tipo di carattere, un allineamento, un formato, un motivo, una combinazione di maiuscole e minuscole o un bordo diverso da quello applicato ai dati dell'elenco; ciò faciliterà la distinzione tra etichette e dati.
3. Se si desidera separare le etichette dai dati, non inserire delle righe al di sotto delle etichette (né vuote né contenenti linee tratteggiate), ma modificare l'altezza o utilizzare i bordi delle celle.

 

Contenuto di righe e colonne

1. Impostare l'elenco in modo che tutte le righe contengano voci simili all'interno della stessa colonna.
2. Fare attenzione agli spazi, che, pur essendo invisibili, sono a tutti gli effetti dei caratteri e come tali intervengono nelle operazioni di ricerca e filtro. In particolare non inserire all'inizio di una parola degli spazi aggiuntivi, che potrebbero influenzare le operazioni di ordinamento e ricerca.

Nomi ed etichette
Per facilitare l'individuazione del database e dei suoi campi è utile fare ricorso ai nomi di intervallo. Per assegnare un nome al database (per chiamarlo ad esempio Database o Elenco o Tabella…), selezionare l'intero elenco ed usare i comandi di menu Inserisci Nome Definisci, mentre per assegnare automaticamente alle colonne i nomi delle etichette contenute nella prima riga, selezionare l'intero elenco ed usare i comandi Inserisci Nome Crea.

Estrazione di dati da un elenco (Filtri)
Per la ricerca di dati singoli da un elenco si rimanda alle funzioni di ricerca e di database.

Per trovare all'interno di un elenco tutti e soli i dati che soddisfano certe condizioni, è possibile utilizzare i filtri, che consentono di visualizzare solo le righe contenenti i dati desiderati. In un elenco contenente i dati relativi agli acquisti ed alle vendite effettuate sia sul territorio nazionale che all'estero è ad esempio possibile visualizzare solo le voci relative alle vendite effettuate all'estero.
Si ricordi comunque che è possibile applicare filtri ad un solo elenco del foglio di lavoro alla volta.

Filtro automatico
Il modo più semplice di impostare una ricerca su un elenco è quella di usare i filtri automatici.

1. Selezionare l'elenco che si desidera filtrare, facendo clic su una cella al suo interno o selezionando il nome eventualmente assegnatogli.
2. Selezionare dal menu Dati Filtro Filtro automatico.
3. Per mostrare solo le righe contenenti un determinato valore, fare clic sulla freccia visualizzata nella colonna contenente il dato che si desidera visualizzare.
4. Selezionare il valore.
5. Per applicare una ulteriore condizione basata sul valore di un'altra colonna, ripetere i passaggi 3 e 4 nell'altra colonna.
6. Per togliere il filtro cliccare sulla freccia e scegliere Tutto oppure deselezionare l'opzione Filtro automatico dal menu Dati Filtro.

 

Note

1. Si noti che vengono visualizzati solo i record (le righe) che soddisfano la condizione. I relativi numeri di riga e la freccetta di selezione della colonna in base alla quale è stato eseguito il filtro assumono il colore azzurro. Tale sistema di visualizzazione sta ad indicare che la situazione è potenzialmente a rischio, in quanto l'operatore potrebbe essere indotto ad eseguire delle operazioni pensando di applicarle a tutto il database e dimenticando che sta vedendo solo i dati filtrati.
2. Per filtrare un elenco in base a due valori della stessa colonna o per applicare operatori di confronto diversi da Uguale a, fare clic sulla freccia visualizzata nella prima cella della colonna e scegliere Personalizza.
3. Per indicare un criterio, nella casella di gruppo "Mostra le righe" selezionare l'operatore di confronto che si desidera utilizzare nella prima casella (aiutarsi col pulsante a freccia), quindi immettere il valore che si desidera soddisfare nella casella posta immediatamente a destra dell'operatore di confronto (per scegliere tra i valori presenti cliccare sul pulsante a freccia).
4. Per visualizzare righe che soddisfano due condizioni contemporaneamente, immettere l'operatore di confronto ed il valore desiderati, quindi fare clic sul pulsante AND. Immettere quindi il secondo operatore di confronto e il relativo valore.
5. Per visualizzare le righe che soddisfano indifferentemente una condizione o un'altra, immettere l'operatore di confronto ed il valore desiderati, quindi fare clic sul pulsante OR. Immettere quindi il secondo operatore di confronto e il relativo valore.
6. Dopo aver applicato un filtro a una colonna, i soli filtri disponibili per le altre colonne saranno i valori rimasti nell'elenco filtrato.
7. Il comando Filtro automatico consente di applicare a una colonna un massimo di due condizioni. Utilizzare invece i filtri avanzati se occorre applicare ad una colonna tre o quattro condizioni, utilizzare valori calcolati come criteri oppure copiare record in un'altra posizione.

Filtro avanzato
Quando le potenzialità del filtro automatico (già in grado di risolvere la maggior parte delle esigenza normali) non sono sufficienti, si può ricorrere al filtro avanzato. I vantaggi nell'uso del filtro avanzato sono principalmente:

1. Possibilità di impostare dei criteri di ricerca fissi, e quindi riutilizzabili più volte anche da parte di procedure automatiche (macro).
2. Possibilità di impostare criteri di ricerca più complessi, non ottenibili col filtro automatico (si pensi ad esempio di voler fare una statistica su tutti i maschi del centro Europa con una età compresa tra 20 e 30 anni oppure tra 50 e 60 anni).
3. Possibilità di immettere dei criteri basati su formule di calcolo (non sviluppata in questa sede).
4. Possibilità di trascrivere automaticamente i dati estratti in un'altra posizione del foglio di calcolo o in un altro foglio oppure addirittura in un altro file, con il non trascurabile vantaggio di poterli elaborare successivamente, senza correre il rischio di intaccare i dati originali.

Per utilizzare un filtro avanzato è necessario preventivamente:

1. Disporre di un database (cui conviene eventualmente assegnare un nome, come Database, Elenco, Listino, ecc.), che abbia le caratteristiche viste in precedenza. Nell'Esempio 1 useremo il nome Database, assegnato all'intervallo A1:D8.
2. Assicurarsi che durante le operazioni di filtro non vengano intaccate altre parti utili del foglio di lavoro (meglio, come si vedrà, lavorare su fogli diversi); si osservi che tale raccomandazione non è stata seguita nell'Esempio 1, infatti Criteri è situato a fianco del database.
3. Predisporre un intervallo dei criteri (anche questo meglio se individuato con un nome, del tipo Criteri, Filtro, Proprietà, ecc.). Nell'Esempio 1 useremo il nome Criteri, assegnato all'intervallo F2:F3 . L'intervallo Criteri deve contenere:
4. nella prima riga (cella F2) il nome del campo sul quale verrà eseguita l'operazione di confronto (nell'esempio "Sesso");
5. nella seconda riga (cella F3) il valore che verrà ricercato nel campo Sesso ("F").

 

 

Esempio 1

	A	B	C	D	E	F	G	H
1	Nome	Anni	Sesso	Città
2	Mario	18	M	Trieste		Sesso
3	Gigi	30	M	Udine		F
4	Maria	45	F	Gorizia
5	Antonio	34	M	Pordenone
6	Laura	28	F	Gorizia
7	Mario	42	M	Trieste
8	Carla	23	F	Udine
9

1. Selezionare il database. Si può fare in diversi modi:
2. Usare l'opzione di menu Modifica Vai a… e selezionare il nome dell'intervallo (Database).
3. In alternativa selezionare il nome Database dalla "Casella del nome" a sinistra della barra della formula.
4. Usare il mouse per selezionare l'intervallo A1:D8 (sconsigliato se l'elenco è lungo).
5. Se il database è corretto, cioè non contiene righe o colonne vuote, basta fare clic su una qualunque cella interna e verranno automaticamente selezionati tutti i dati fino alle righe e colonne lasciate vuote al contorno.
6. Selezionare da menu Dati Filtro Filtro avanzato.
7. Completare i dati richiesti dalle caselle di input, verificando in particolare che gli intervalli del database e dei criteri corrispondano a quelli previsti (spesso sono in agguato sorprese dovute all'eccesso di automatismo che permea il programma).
8. Dare OK.

 

Se avete operato correttamente ed in particolare avete mantenuto l'opzione "Filtra l'elenco sul posto", il risultato ottenuto è il seguente (Esempio 2):

Esempio 2

	A	B	C	D	E	F	G	H
1	Nome	Anni	Sesso	Città
4	Maria	45	F	Gorizia
6	Laura	28	F	Gorizia
8	Carla	23	F	Udine
9
10
11
12
13

Si noti in particolare che

1. sono visibili solo i record relativi alle femmine (e questo va bene),
2. i numeri delle righe corrispondenti sono diventati blu, ad indicare che si tratta di un elenco parziale (e anche questo va bene),
3. sono sparite le informazioni relative ai criteri (e con esse tutte le eventuali informazioni a lato), perché si trovavano sulle righe 2 e 3, nascoste dall'operazione di filtro, in quanto corrispondenti a record relativi a maschi (e questo non va bene).

Se a ciò si aggiunge il fatto che è possibile fare inavvertitamente delle operazioni sui soli dati visibili, ritenendo di operare su tutto il database (contare ad es. con CONTA.VALORI(C4:C8) quanti sono i record "F" estratti), si comprende la raccomandazione fatta in precedenza di lasciare il database integro sul suo foglio di lavoro e compiere tutte le operazioni di filtro da un'altra parte.

Per ottenerlo, operare come segue:

1. Sistemare, come detto, il database su un foglio dedicato (che chiameremo Foglio1).
2. Predisporre i criteri su un altro foglio (Foglio 2), per esempio nelle celle A1:A2.
3. Selezionare il foglio nel quale vogliamo portare i dati estratti con l'operazione di filtro (Foglio3).
4. Attivare la procedura Dati Filtro Filtro avanzato.
5. Inserire nelle caselle di input "Intervallo elenco" ed "Intervallo criteri" gli intervalli richiesti:
6. con il mouse , selezionare il Foglio1 e quindi l'intervallo del database (idem per l'intervallo criteri), oppure
7. stando sulla casella di input dell'intervallo elenco, selezionare l'opzione del menu Inserisci Nome Incolla e scegliere dall'elenco il nome Database (idem per Criteri).
8. Selezionare il bottone "Copia in altra posizione" (si rende attiva la terza casella di input "Copia in" presente sulla finestra di dialogo).
9. Con il mouse o in un altro dei modi conosciuti inserire nella casella "Copia in" il riferimento dell'angolo superiore sinistro dell'area su cui si vogliono incollare i dati estratti (ad es. la cella A1 del Foglio3).
10. Dare OK.

Si ottiene lo stesso risultato dell'esempio 2, solo che i dati estratti sono stati incollati nel Foglio3, senza intaccare quelli del Database originale. Si noti che in questo modo tali dati possono essere ad esempio incollati in uno schema di calcolo predisposto per eseguire delle operazioni standard sugli stessi e che tutta la procedura può essere ulteriormente automatizzata (con le macroistruzioni), rendendo il tutto estremamente più efficiente e sicuro.

Filtrare un elenco utilizzando criteri avanzati
L'impostazione dei criteri di ricerca è un argomento particolarmente delicato, non tanto per gli aspetti formali, che anzi nel caso dei filtri automatici risultano anche particolarmente semplici ed intuitivi, quanto soprattutto per gli aspetti concettuali, che presuppongono una adeguata comprensione degli operatori logici fondamentali. Si invitano pertanto gli studenti a non sottovalutare tali aspetti e a fare una adeguata esperienza sull'argomento.
Negli esempi precedenti sono stati usati criteri di filtraggio molto semplici, del tipo "tutti i record che hanno il contenuto del campo x uguale a y". Ci sono però numerose situazioni in cui questo semplice tipo di criterio non basta.
Si pensi ad esempio di dover estrarre tutti i record relativi alle spese effettuate nel marzo 2000. Se ricordiamo che per Excel le date sono dei numeri seriali che partono dall' 1 gennaio 1900, si capisce che ciò corrisponde a cercare "tutti i record che hanno una data compresa tra 36586 (01/03/2000) e 36616 (31/03/2000)", o meglio, per dirla in termini rigorosi, "tutti i record, la cui data è maggiore di o uguale a 36586 e contemporaneamente minore di o uguale a 36616".
Un altro esempio potrebbe essere quello di estrarre tutti i record corrispondenti ad acquisti fatti da Rossi e da Verdi . Matematicamente ciò si traduce in un criterio del tipo "tutti i record in cui il nome dell'acquirente è Rossi o Verdi".
Si impone a questo punto la necessità di rinfrescare alcune conoscenze elementari di algebra di Boole (o algebra della logica o algebra degli insiemi) che dovrebbero far parte del bagaglio culturale di un'istruzione superiore.
Il database è in fondo un insieme di record, nel quale possiamo individuare diversi sottoinsiemi costituiti da tutti i record che soddisfano certi criteri descritti da proposizioni logiche.
Le proposizioni logiche possono essere tra loro combinate per ottenere proposizioni più complesse mediante gli operatori logici fondamentali.
Gli operatori logici fondamentali sono (con i loro sinonimi ed i simboli presi in prestito dalle espressioni algebriche, che sono quelli più comodi per rappresentarli):
NOT   (NON, Negazione, simbolo "-")
AND   (E, Intersezione, Prodotto logico, simbolo "." o assente)
OR      (O, Unione, Somma logica, simbolo "+")
Ad esempio, date le proposizioni logiche A e B, le espressioni seguenti si leggono:
A+B:                          "A OR B", oppure "A unito B", oppure più semplicemente "A più B"
A.B oppure AB          "A AND B", oppure "A intersecato B", oppure ancora "AB"
-A                               "NOT A", oppure "NON A" o "A negato"
Ci sono diversi modi per rappresentare graficamente il significato degli operatori logici (diagrammi di Venn, tavole della verità, ecc.). Qui si farà riferimento alle tavole della verità nelle quali per comodità si useranno i simboli 1 e 0, per indicare rispettivamente VERO e FALSO.
Date quindi le seguenti proposizioni logiche attribuite ad un elenco di acquisti di articoli vari fatti da ipotetici clienti:
A         acquisto fatto dal Cliente "Rossi"
B         acquisto relativo all'Articolo "Scarpe"
C         acquisto fatto dal Cliente "Verdi"

Operatore NOT
Data la proposizione logica A, la proposizione NOT A è vera quando A è falsa e viceversa

Nel nostro esempio NOT A significa "acquisto fatto da chiunque, fuorché da Rossi"

Tavola della verità

Operatore AND
Date le proposizioni logiche A e B, la proposizione A AND B è vera solo quando sono contemporaneamente vere entrambe le proposizioni.

Nel nostro esempio A AND B significa "tutti gli acquisti di scarpe fatti da Rossi".

Tavola della verità

Operatore OR
Date le proposizioni logiche A e C, la proposizione A OR C è vera quando è vera almeno una delle due.

Nel nostro esempio A OR C significa "tutti gli acquisti fatti da Rossi più tutti quelli fatti da Verdi".

Tavola della verità

Nel formalismo di Excel, i criteri suddetti vengono realizzati nel modo seguente:

il NOT si ottiene usando l'operatore diverso da (<>)
l'AND si ottiene mettendo i due criteri affiancati sulla stessa riga
l'OR si ottiene scrivendo i criteri su righe diverse

Esempio 3

	A	B	C	D	E	F	G
1	Criterio NOT		Criterio AND			Criterio OR
2
3	Cliente		Cliente	Articolo		Cliente
4	<>Rossi		Rossi	Scarpe		Rossi
5						Verdi
6

Si noti che in questo contesto le stringhe (cella A4) non vanno racchiuse tra virgolette doppie! (Mistero della incongruenza, che la dice lunga sulle cautele da adottare nell'uso di questi prodotti.)

In questo modo si possono realizzare criteri anche complessi, in grado di soddisfare le esigenze più sofisticate di filtraggio. Si vedano gli esempi che seguono, facendo riferimento al database dell'Esempio 1. Per ogni criterio sono indicati sia la lettura formale, sia la relativa interpretazione.

Esempio 4

	A	B	C	D	E	F	G	H	I
1	Criterio 1			Criterio 2			Criterio 3		Criterio 4
2	Anni	Anni		Anni	Anni		Anni		Anni
3	>20	<30		<20	>30		<20		>20
4							>30		<30
5
6	Criterio 5			Criterio 6
7	Nome	Nome		Sesso	Anni	Anni	Città
8	>=M	<N		M			Trieste
9				F	>=30	<=50
10

Criterio 1
Si legge:          "Vengono estratti tutti i record per i quali Anni > 20 AND Anni < 30"
Si interpreta:   "Vengono estratti tutti i record che hanno un'età compresa tra 20  e 30 (estremi esclusi)"

Criterio 2
Si legge:          "Vengono estratti tutti i record per i quali Anni < 20 AND Anni > 30"
Si interpreta:   "Vengono estratti tutti i record che hanno un'età inferiore ai 20 anni e contemporaneamente superiore ai 30"
Nota:               Questo criterio non potrà trovare alcun record, in quanto non è possibile che un numero (in questo caso rappresentativo dell'età, ma il discorso è generale) sia contemporaneamente inferiore a 20 e maggiore di 30! Si ricordi che l'operatore AND è VERO solo se entrambe le proposizioni logiche sono VERE.

Criterio 3
Si legge:          "Vengono estratti tutti i record per i quali Anni < 20 OR Anni > 30"
Si interpreta:   "Vengono estratti tutti i record che hanno un'età minore di 20  più tutti quelli che hanno un'età maggiore di 30 (estremi esclusi)"

Criterio 4
Si legge:          "Vengono estratti tutti i record per i quali Anni > 20 OR Anni < 30"
Si interpreta:   "Vengono estratti tutti i record che hanno un'età maggiore di 20  più tutti quelli che hanno un'età minore di 30 (estremi esclusi)"
Nota:               Se si presta attenzione, questo criterio è soddisfatto da tutti i record.

Criterio 5
Si legge:          "Vengono estratti tutti i record per i quali Nome >= M AND Nome < N"
Si interpreta:   "Vengono estratti tutti i record che hanno un nome che inizia per M"

Criterio 6
Si legge:          "Vengono estratti tutti i record per i quali Sesso = M AND Città = Trieste OR Sesso = F AND Anni >= 30 AND Anni <= 50"
Si interpreta:   "Vengono estratti tutti i maschi abitanti a Trieste più tutte le femmine con un'età compresa tra i 30 ed i 50 anni (compresi)"

 

Nell'interpretazione dei criteri complessi si ricordi che, analogamente agli operatori aritmetici, tra gli operatori logici l'AND (prodotto logico) ha la precedenza sull'OR (somma logica); pertanto prima vengono eseguiti gli AND delle singole righe (come se fossero racchiusi in parentesi), poi viene eseguito l'OR tra i risultati delle diverse righe.

Esempio

	A	B	C	D	E	F	G
1	Campo1	Campo2	Campo3
2	A	B	C
3		D	E
4	F		G
5

Dette A, B, C, … le diverse proposizioni che costituiscono il criterio, l'interpretazione globale è:
(A AND B AND C) OR (D AND E) OR (F AND G),
oppure, utilizzando la scrittura algebrica,:
ABC+DE+FG

Nella verifica del funzionamento del criterio bisogna individuare tutti i casi che si possono presentare nel database originale e, per ciascuno di essi, eseguire con calma le operazioni descritte del criterio, verificando se alla fine il risultato ottenuto (VERO o FALSO) corrisponde a quanto richiesto.

Utilizzando il criterio 6 applicato al database dell'Esempio 1 e chiamando A, B, C, … le proposizioni logiche elementari in esso presenti, la proposizione logica che ne segue è:

AB+CDE  =  X+Y  (chiamando X il termine AB e Y il termine CDE)

Nella verifica del database il programma procede sequenzialmente dal primo all'ultimo record nel modo seguente:

Prende il primo record
A: Sesso = M?            VERO
B .Città = Trieste?      VERO
C: Sesso = F?             FALSO
D: Età >=30?              FALSO
E: Età <=50?              VERO

X = A AND B è VERO
Y = C AND D AND E è FALSO
X OR Y è VERO

Il risultato dell'espressione è VERO, per cui il primo record viene visualizzato.

Prende il secondo record
A: Sesso = M?            VERO
B .Città = Trieste?      FALSO
C: Sesso = F?             FALSO
D: Età >=30?              VERO
E: Età <=50?              VERO

X = A AND B è FALSO
Y = C AND D AND E è FALSO
X OR Y è FALSO

Il risultato dell'espressione è FALSO, per cui il secondo record non viene visualizzato.

Prende il terzo record
A: Sesso = M?            FALSO
B .Città = Trieste?      FALSO
C: Sesso = F?             VERO
D: Età >=30?              VERO
E: Età <=50?              VERO

X = A AND B è FALSO
Y = C AND D AND E è VERO
X OR Y è VERO

Il risultato dell'espressione è VERO, per cui il terzo record viene visualizzato.

E così via.

Si raccomanda di fare parecchio esercizio su questo argomento, per evitare brutte sorprese agli esami.

 

Macroistruzioni (avanzato)
Se si eseguono attività ripetitive, è possibile automatizzarle mediante l'utilizzo di una macro.
Una macroistruzione è costituita da una serie di comandi e funzioni memorizzati in un modulo Visual Basic ed eseguibili in qualsiasi momento occorra svolgere una determinata attività ripetitiva.
Per sfruttare appieno le molteplici possibilità delle macro sarebbe necessario addentrarsi nella conoscenza del linguaggio di programmazione Visual Basic, cosa che esula dalle finalità di questo corso. Ci si limiterà quindi ad un accenno ai concetti fondamentali, in modo da mettere in grado anche i non programmatori di realizzare delle mini-procedure personali.
La registrazione di una macro è simile alla registrazione di un brano musicale, che può successivamente essere rieseguito quante volte si vuole.

Le fasi necessarie alla realizzazione di una macro sono:

1. Rendersi conto che ci sono una o più azioni ripetitive nella normale attività legata all'uso di un certo foglio di calcolo.
2. Analizzare a fondo tali operazioni, per individuare sia le operazioni stesse sia le condizioni che le rendono veramente ripetibili.
3. Trascrivere su un pezzo di carta (almeno fino a che non si è acquisita una certa dimestichezza) tutte i singoli passaggi e i comandi che si desidera che la macro esegua.
4. Provare a rieseguire le operazioni trascritte partendo dalle condizioni più generali possibile, al fine di individuare eventuali errori o dimenticanze.
5. Registrare la macro, con le modalità operative descritte più oltre. (Al termine non dimenticarsi di interrompere la registrazione!)
6. Provare ad eseguire la macro col comando Strumenti Macro Macro Esegui.

Come si vede in questa fase non sono state ancora definite le modalità operative, perché la funzionalità di una macro dipende quasi esclusivamente dall'attenzione che si è dedicata alla fase di analisi del problema. Un esempio chiarirà meglio quanto detto.

Si faccia riferimento alla figura 13, contenente un data base anagrafico nell'intervallo A1:E8, al quale assegnamo per comodità il nome "Anagrafica" (comando Inserisci Nome Definisci).

Figura 13.

	A	B	C	D	E	F	G
1	Cognome	Nome	Residenza	Età	Sesso
2	Rossi	Mario	Trieste	19	M
3	Rossi	Franca	Gorizia	38	F
4	Verdi	Erik	Udine	25	M
5	Neri	Barbara	Pordenone	23	F
6	Bianchi	Antonio	Padova	38	M
7	Bianchi	Ada	Venezia	52	F
8	Neri	Paolo	Treviso	48	M
9

 

Fase 1
Si immagini di avere la frequente necessità di ordinare i dati in base a campi di volta in volta diversi (ad es. per Cognome, per Età, per Residenza, ecc.). Questo è un tipico esempio di operazione ripetitiva, che, pur nella sua semplicità, alla lunga può risultare quanto meno fastidiosa se non addirittura portare ad errori di distrazione.

 

Fase 2

 

Passando all'analisi del problema, si riconosce una prima fase operativa, che prevede l'individuazione dell'intervallo e l'attivazione del menu Dati Ordina, seguita dalla scelta del campo in base al quale ordinare.
E già qui ci si rende conto che non è possibile automatizzare tutti i tipi di ordinamento, perché è richiesto di volta in volta un diverso intervento dell'operatore (quello appunto di indicare la colonna in base alla quale ordinare). Non si dimentichi infatti che, in questa visione semplificata, la macro deve essere tale da eseguire tutte le operazioni fino in fondo, senza ulteriori interventi dell'utente. Sarà necessario quindi prevedere tante macro quanti sono i criteri di ordinamento che si vogliono automatizzare.
Si osservi inoltre che è estremamente importante rendersi conto di quale sarà la più generica delle situazioni dalla quale partiremo per mandare in esecuzione la macro, pena la sua non affidabilità. Tale concetto sarà più chiaro dopo l'analisi delle fasi successive.

Fase 3
Cominciamo ora a prendere nota di tutti i passaggi necessari per ordinare i dati, ad esempio in funzione del Cognome.
Supponiamo per un momento che la cella attiva sia una qualunque cella interna all'intervallo Anagrafica, ad es. la cella C4. Le operazioni da effettuare per ordinare i dati (provarle su un foglio di calcolo reale) sono:

1. Attivare l'opzione di menu Dati Ordina.
2. Nella finestra di dialogo Ordina, selezionare "Cognome" nella prima casella di input (dove ora compare l'etichetta "Residenza").
3. Dare OK.

Fase 4
La sequenza di operazioni descritta, applicata al caso reale, danno l'effetto voluto.
Sembrerebbe quindi tutto a posto, ma vedremo più avanti che non è così. Per il momento comunque proseguiamo, dopo aver annullato l'operazione per riportare i dati nel "disordine" precedente.

Fase 5
È la fase più delicata, perché, una volta avviata la registrazione della macro, tutto viene registrato, anche gli eventuali errori. L'unico vantaggio è che vengono registrate anche le correzioni, se fatte in tempo…
Si supponga allora di trovarsi ancora nella cella C4. Procedere nel modo seguente:

1. Cliccare su Strumenti Macro Registra nuova macro. Compare la finestra di dialogo Registra Macro, nella quale, tra l'altro, è possibile modificare il nome di default della macro stessa (in questo caso "Macro1". Cambiarlo per esempio in "Ordina_per_Cognome " e lasciare le altre opzioni inalterate.
2. Dare OK. Da questo momento in poi comincia la registrazione della macro.
3. Cliccare su Dati Ordina.
4. Nella prima casella di input della finestra Ordina, selezionare o confermare "Cognome".
5. Dare OK.
6. Cliccare sul pulsante Interrompi registrazione della finestrella omonima. In alternativa (nel caso in cui la finestra fosse chiusa o comunque inaccessibile) cliccare su Strumenti Macro Interrompi registrazione.
7. Ci si può rilassare… (il gioco di parole si riferisce beninteso alla registrazione di macro più complesse di questa. Comunque si ripete l'invito a ricordarsi di fermare la registrazione, altrimenti tutto quello che viene fatto in seguito viene registrato, generando un vero e proprio macello).

Fase 6
Per verificare l'effetto della macro bisogna prima riportare i dati nel "disordine" precedente.
E qui c'è la prima sorpresa: non è possibile fare uso dell'opzione di menu Modifica Annulla, che ci ha salvato in tante altre situazioni (il che è una bella fregatura, soprattutto se abbiamo sbagliato qualcosa in precedenza e se i dati sono qualche migliaio, come si conviene ad un database serio…).
Per fortuna in questo frangente stiamo facendo solo delle prove, per cui possiamo ad esempio ordinare i dati in funzione della Residenza per ottenere il "disordine" voluto nella prima colonna.
Successivamente, per eseguire la macro procedere nel modo seguente:

1. Selezionare una cella all'interno del database (ad es. la cella C4).
2. Cliccare su Strumenti Macro Macro; compare la finestra di dialogo Macro.
3. Selezionare il nome della macro da eseguire (in questo caso ce ne dovrebbe essere solo una, cui abbiamo assegnato il nome Ordina_per_Cognome).
4. Cliccare sul pulsante Esegui.

Se non si sono commessi errori, la macro esegue tutti i passaggi precedentemente registrati e il database risulta alla fine ordinato come richiesto.

Facile? Attenti alle sorprese.

Riportare i dati in forma "disordinata".
Questa volta, prima di eseguire la macro ci "dimentichiamo" di selezionare una cella interna al database, cliccando su una cella esterna, ad es. la G6.
Se cerchiamo ora di eseguire la macro (con Strumenti Macro Macro), ci accorgiamo che non funziona. Compare infatti un messaggio d'errore di quelli tremendi (Errore di run-time '1004, ecc.), che in sostanza dice che il programma non ha trovato nessuna tabella da ordinare (si ricorda che l'automatismo inserito in Excel riconosce un intervallo solo se si parte da una cella interna allo stesso e solo fino alla prima riga ed alla prima colonna vuote). Ciò significa che la macro appena realizzata non garantisce una operatività in qualunque condizione, ma funziona solo se, al momento dell'esecuzione, la cella attiva sta all'interno dell'intervallo Anagrafica.

Per rendere il tutto funzionante in condizioni generali, dobbiamo inserire nella macro un comando che selezioni inequivocabilmente tale intervallo prima di procedere all'ordinamento. Chi ha esperienza di programmazione e conosce il linguaggio Visual Basic può farlo anche andando a modificare direttamente il codice del programma, altrimenti, ed è questo il livello del corso, si tratterà di sostituire la macro sbagliata con un'altra.

In primo luogo quindi è necessario cancellare la macro precedente, usando il pulsante Elimina della finestra Macro.
Successivamente è opportuno selezionare una cella generica, non necessariamente interna all'intervallo dei dati. (Si potrebbe addirittura partire da un altro foglio).
Alla fine procedere come segue.

1. Attivare il registratore con Strumenti Macro Registra nuova Macro.
2. Assegnare il nome desiderato (Ordina_per_Cognome) e dare OK.
3. Selezionare l'intervallo Anagrafica in uno dei modi conosciuti (ad es. con Modifica Vai a...)
4. Cliccare su Dati Ordina.
5. Selezionare il campo in base al quale ordinare (Cognome).
6. Confermare con un clic il bottone Con riga di intestazione (potrebbe non essere attivo in una situazione generica, sempre a causa degli automatismi di Excel).
7. Dare OK.
8. Deselezionare l'intervallo cliccando nuovamente su una cella generica.
9. Fermare la registrazione come visto in precedenza (cliccando sul pulsante Interrompi registrazione della finestrella omonima oppure selezionando dal menu Strumenti Macro Interrompi registrazione).

 

Adesso abbiamo una vera macro, una procedura cioé in grado di funzionare a prescindere dalle condizioni iniziali, in quanto in essa sono previste tutte le operazioni per consentire la sua corretta esecuzione (in questo caso l'individuazione manuale dell'intervallo da ordinare).

Per dare il tocco finale non resta che rendere ancora più efficiente l'esecuzione della macro (senza dover passare ogni volta per Strumenti Macro Macro Esegui). Si tratta di creare un "controllo" al quale associare la macro (di solito un pulsante, del tutto simile a quelli con cui siamo abituati a lavorare).

1. Col menu Visualizza, attivare la barra degli strumenti Moduli.
2. Tra i diversi oggetti in essa contenuti selezionare il "Pulsante" (rettangolare).
3. Portarsi con il mouse (che ora ha l'aspetto di una crocetta) nella posizione desiderata (ad es. sopra la cella G2) e disegnare col mouse (tenendo premuto il tasto sinistro) un pulsante delle dimensioni desiderate.
4. Appena si rilascia il tasto del mouse compare la finestra di dialogo Assegna Macro, dalla quale selezionare la macro da assegnare al pulsante (in questo caso dovrebbe essercene solo una, la nostra Ordina_per_Cognome).
5. Dare OK.
6. Cliccare all'esterno del pulsante per deselezionarlo.

 

Mentre il pulsante risulta selezionato è possibile personalizzarlo (lo si può spostare, ridimensionare, si può modificare il testo al suo interno, ecc.).
Dal momento in cui si clicca fuori dal pulsante, deselezionandolo, lo stesso diventa "attivo", nel senso che, se si clicca su di lui, viene mandata in esecuzione la macro associata.
Per consentire una successiva modifica del pulsante, cliccare sullo stesso col tasto destro del mouse oppure anche col tasto sinistro, ma tenendo contemporaneamente premuto il tasto Ctrl.

Provare ad esercitarsi creando altre macro in grado di ordinare il database in funzione di altri campi. I vari esercizi proposti contengono diversi esempi di macro.

 

 

Fonte: http://digilander.libero.it/nome6/Corso/Excel/Dispensa_Excel.doc
Sito web: http://digilander.libero.it/nome6/
Autore: Gianluca ciocia

 

Primi passi con Excel

 

 

Visita la nostra pagina principale

 

Primi passi con Excel

 

Termini d' uso e privacy