Università degli Studi di Foggia

D.U. in Economia ed Amministrazione delle Imprese

D.U. in Economia e Gestione dei Servizi Turistici

Corso di Informatica Generale – Prof. Crescenzio Gallo

E S E R C I Z I - F O G L I O E L E T T R O N I C O

A completamento del Corso di Informatica Generale vengono offerti alcuni esercizi con relative soluzioni, relativamente ai fogli elettronici.

Per una completa rassegna dei comandi e delle funzioni richiamate, si prega di fare riferimento ai lucidi ed alle dispense citate nella homepage del Corso all'indirizzo Internet:

http://www.economia.unifg.it/docenti/cgallo

1. FOGLIO ELETTRONICO

1.1 PREREQUISITI

Il foglio elettronico ("Spreadsheet") è uno strumento generalizzato di calcolo. Esso simula un super foglio costituito da una griglia formata da righe e colonne (normalmente migliaia).

Nella risoluzione dei problemi proposti si pone l'attenzione sull'aspetto metodologico piuttosto che sugli aspetti strettamente tecnici legati ai singoli sistemi. Allo scopo di facilitare la comprensione degli algoritmi di calcolo proposti, sono state utilizzate tecniche di programmazione strutturata. Le funzioni e le istruzioni utilizzate hanno nomi e regole che seguono genericamente quelle dei più comuni applicativi software in commercio (LOTUS 1-2-3, EXCEL, etc.). Si ritiene che l'apprendimento di un sistema software richieda un certo esercizio pratico ed esperienza d'uso.

2. GESTIONE DEI FOGLI DI LAVORO

Il foglio di lavoro ha la funzione di memorizzare e manipolare i dati. E' costituito da celle disposte su righe e colonne. Un foglio di lavoro può contenere dati e formule applicate ai dati, grafici che rappresentano i dati (per esempio un istogramma o una torta) e macro (una o più serie di istruzioni) per eseguire operazioni su dati.

	A	B	C	D	E	F	G
1	Codice	Descrizione	Q.tà	C.IVA	Prezzo	Importo
2	XAB01	Viti	100	20	L. 150	L. 3000
3	FCD78	Bulloni	500	20	L. 400	L. 8000
4	…	…	…	…	…	…
5	…	…	…	…	…	…
6	…	…	…	…	…	…
7	…	…	…	…	…	…
8	…	…	…	…	…	…
9	…	…	…	…	…	…
10	…	…	…	…	…	…
11	…	…	…	…	…	…
12	…	…	…	…	…	…
13	…	…	…	…	…	…
14					Imponibile	L.500.000
15					Spese di spedizione	L.10.000
16					IVA 20%	L. 102.000
17					TOTALE FATTURA	L. 612.000
18
19
20

La figura illustra un esempio di foglio elettronico che realizza una fattura.

I dati di un foglio di lavoro sono registrati in una archivio il cui formato dipende dal programma di usato. Un archivio contiene uno o più fogli di lavoro. E' possibile rinominare, aggiungere ed eliminare fogli oppure spostare o copiare fogli all'interno di un archivio di lavoro o tra archivi di lavoro (le modalità operative dipendono dal programma utilizzato).

Gli spreadsheet in commercio forniscono, generalmente, utilità per l'importazione e l'esportazione di dati in diversi formati (per esempio un formato standard è il formato testo ASCII) per permettere l'integrazione con altri software applicativi.

3. STRUTTURA DEL FOGLIO DI LAVORO

La struttura del foglio di lavoro è costituita dalle seguenti unità di riferimento:

CELLA è l'elemento in cui vengono collocati i dati ed è il punto in cui una riga e una colonna si intersecano. Viene identificata con un indirizzo espresso con il numero della riga e il nome della colonna, cioè con una sequenza data da lettere alfabetiche e numero. Per esempio la cella formata dall'intersezione della colonna D con la riga 5 viene denominata D5 (in alcuni casi anche la colonna viene convertita in numero per semplificare l'indirizzamento).

CELLA ATTIVA è la cella che contiene il cursore (rettangolo evidenziato) e indica la posizione sul foglio di lavoro corrente, in cui è possibile immettere dati o attivare comandi. Spostando il cursore da una cella a un'altra, permette di selezionare quella con cui si vuole lavorare. Può essere attiva solo una cella alla volta.

ZONA è costituita da un insieme rettangolare di celle contigue su un foglio. E' individuata dagli indirizzi degli estremi di una diagonale separati dal carattere ".." (oppure ":"). Ad una zona si può assegnare un nome. Una zona larga una colonna individua un segmento di colonna, per esempio A1..A1OO. Una zona alta una riga individua un segmento di riga, per esempio A1..Z1. Una zona 1x1 è una cella: A1..A1 equivale alla cella A1.

PANNELLO si trova generalmente al di sopra della prima riga del foglio (il numero, il tipo di interfaccia e la disposizione grafica dipende dal programma usato). Generalmente, una riga visualizza il menù dei comandi; una riga visualizza le informazioni relative alla cella attiva: l'indirizzo della cella, il formato della cella, il nome del foglio corrente; una riga visualizza il contenuto della cella; una riga visualizza i messaggi del programma.

4. IMMISSIONE DI DATI

L'immissione dei dati in un foglio di lavoro avviene selezionando, tramite posizionamento del cursore, la cella desiderata e quindi digitandovi i dati. Premendo il tasto INVIO la selezione si sposta verso il basso per consentire una nuova immissione.

In un cella del foglio di lavoro è possibile immettere due tipi di dati:

- un valore costante;

- una formula, ossia una sequenza di valori, riferimenti di cella, operatori o funzioni che genera un nuovo valore, risultato della formula, in base a dati esistenti. Una formula inizia sempre con un segno identificatore che la contraddistingue dai valori costanti (normalmente il segno di chiocciola @ oppure il segno di uguale =). Il risultato di una formula è soggetto alle variazioni dei valori delle celle cui fa riferimento. Le formule non si vedono sul foglio di lavoro; solo i risultati ed i valori costanti sono visibili. Se la cella che contiene la formula è attiva, la formula viene visualizzata sulla riga di controllo.

4.1 VALORI COSTANTI

Un valore costante può essere un valore numerico, una data, una percentuale oppure un valore stringa.

4.1.1 I numeri

In tutte le celle di un foglio di lavoro il formato numerico viene assegnato automaticamente all'immissione a meno che il dato non venga riconosciuto come stringa, data o formula. Un numero decimale si distingue dal numero intero tramite il punto decimale o la virgola (a seconda della versione inglese o italiana del programma utilizzato).

4.1.2 Le stringhe (etichette)

Una stringa è costituita da una serie di caratteri o da una qualsiasi combinazione di numeri e caratteri. Qualsiasi serie di caratteri non interpretata come numero, formula o data viene interpretata come stringa. Per immettere un numero come stringa bisogna anteporre al numero un apostrofo.

4.1.3 Le date

Le date immesse in una cella sono interpretate come numeri: indipendentemente dal formato di visualizzazione usato, le date sono memorizzate internamente come numeri seriali. Questo sistema permette l'applicazione delle operazioni aritmetiche alle date come se fossero numeri: ad esempio la somma o la differenza di date. I giorni sono numerati a partire dall'inizio del secolo. La data espressa come numero seriale 1 corrisponde alla data 1 gennaio 1900 (comunque il sistema di datazione dipende dal programma di spreadsheet usato).

Da una data è possibile estrarre, tramite opportune funzioni applicabili ad una cella di tipo data, i campi elementari di cui essa è composta: l'anno, il mese, il giorno; è possibile calcolare anche il giorno della settimana in cui ricade un giorno dell'anno (per i dettagli delle singole funzioni data consultare l'help in linea del programma usato).

4.2 LE FORMULE

Una formula ha lo scopo di generare un valore da quelli esistenti. Una formula combina in una cella:

- valori costanti;

- operatori;

- riferimenti;

- funzioni;

- confronto di valori;

per eseguire diverse operazioni.

Una formula può essere paragonata ad un membro di un'equazione il cui risultato viene visualizzato nella cella.

4.2.1 I riferimenti

Un riferimento identifica una cella o un gruppo di cene su un foglio di lavoro. Tramite i riferimenti è possibile utilizzare in una formula i dati contenuti in diverse parti di un foglio di lavoro o utilizzare il valore di una cella in diverse formule. E' anche possibile fare riferimento a celle presenti su eventuali altri fogli contenuti nello stesso archivio di lavoro.

I riferimenti di cella si basano sulle intestazioni di riga e di colonna di un foglio: le righe sono abitualmente classificate con numeri, mentre le colonne sono classificate con lettere o con combinazioni di lettere (A, B, C, ..., Z, AA, AB, AC, ..., AZ; BA, BB, BC e così via).

il riferimento di questa cella è C2	A	B	C
1
2
3
il riferimento a questa zona è B5..C6 4
5
6

In alcuni spreadsheet può essere usato il riferimento a fogli di altri archivi di lavoro e a dati contenuti in altre applicazioni. I riferimenti a celle di altri archivi di lavoro sono denominati riferimenti esterni. I riferimenti ai dati di altre applicazioni sono denominati riferimenti esterni o remoti.

I riferimenti di cella sono classificati come:

Riferimento relativo	Specifica l'indirizzo di un'altra cella relativamente alla cella contenente la formula con il significato di posizione relativa. Un riferimento di cella relativo assume la forma A1, B1, e così via.
Riferimento assoluto	In una formula, specifica l'indirizzo fisso di una cella indipendentemente dalla posizione della cella contenente la formula. Un riferimento assoluto è normalmente contrassegnato da un segno di dollaro ($) prima della lettera corrispondente alla colonna e del numero corrispondente alla riga. Per esempio, assume la forma $A$1, $B$1, e così via.
Riferimento misto	In una formula, specifica un riferimento di una riga o una colonna fisso con una colonna o riga relativa. Un riferimento misto è contrassegnato da un segno di dollaro prima della lettera corrispondente alla colonna o del numero corrispondente alla riga. Per esempio, nel riferimento misto $A1, il riferimento di colonna ($A) è assoluto, mentre il riferimento di riga (1) è relativo.

La distinzione tra riferimenti assoluti, relativi e misti è importante quando una formula è copiata o spostata da una cella ad un'altra:

· un riferimento assoluto, in una formula copiata o spostata, appare esattamente come nella formula originale;

· un riferimento relativo, in una formula copiata o spostata, viene automaticamente modificato in modo da riflettere la nuova posizione. I riferimenti relativi in una formula copiata o spostata si riferiscono a celle diverse rispetto ai riferimenti della formula originale. La relazione tra queste celle e la cella contenente la formula copiata è la stessa esistente tra le celle cui viene fatto riferimento nella formula originale e la cella contenente la formula stessa;

· un riferimento misto, in una formula copiata o spostata, viene modificato automaticamente per il suo riferimento di riga o di colonna relativo, rimane esattamente come nella formula originale per il suo riferimento ci colonna o di riga assoluto.

Esempio: formula con riferimento relativo.

La cella A2 contiene la formula 3*A1 che moltiplica per 3 il valore nella cella sovrastante di una riga a quella contenente la formula. La formula utilizza il riferimento relativo A1; il risultato della formula è 600.

	A	B
1	200	400
2	3*A1=600

Esempio: effetto della copia di una formula con riferimento relativo.

Se la formula contenuta nella cella A2 è copiata nella cella B2, automaticamente si modifica il riferimento relativo A1 per fare riferimento alla cella B1, la cella sovrastante di una riga a quella contenente la formula. La formula diventa 3*B1 e il risultato della formula è 1200.

	A	B
1	200	400
2	3*A1=600	3*B1=1200

Esempio: formula con riferimento assoluto.

La formula inserita in A2 inizia con un riferimento assoluto anziché con un riferimento relativo. Eseguendo la copia della formula da A2 in B2, sia la formula originale che quella copiata producono lo stesso risultato 600. Il riferimento assoluto $A$1 è rimasto invariato durante la copia della formula e quindi anche il risultato della formula.

	A	B
1	200	400
2	3*$A$1=600	3*$A$1=600

Esempio: formula con riferimento misto.

Nei riferimenti misti, quali A$1 o $A1, le lettere della colonna o il numero della riga vengono modificati automaticamente, purché non siano preceduti da un simbolo di dollaro $, e lasciano l'altra parte del riferimento invariata.

	A	B	C	D
1	100	50
2	3*A$1=300		3*$A1=300
3		3*B$1=150		3*$A2=900

Si può lavorare su più fogli contemporaneamente (ogni foglio deve essere individuato dal suo nome). Per esempio Foglio1!B1..C7 indica una zona sul foglio che si chiama Foglio1. E' impossibile in una cella usare il riferimento di se stessa. Questo tipo di riferimento è denominato riferimento circolare.

4.2.2 Le funzioni

Una funzione calcola un risultato a partire dai valori dei parametri di input, generalmente racchiusi tra parentesi. Per distinguere le funzioni dagli altri identificatori viene spesso usato un carattere iniziale particolare (ad es. @).

E' necessario includere entrambe le parentesi; gli argomenti, quando più di uno, sono separati da un segno separatore, punto e virgola ";" (alcuni spreadsheet usano la virgola ",").

Nella formula @SOMMA(B3..B20), @SOMMA è il nome della funzione e B3..B20 è l'argomento, racchiuso tra parentesi.

Nella formula @SOMMA(10;20;3;4;50) il segno di punto e virgola (;) separa ciascun argomento.

Le funzioni sono utilizzabili nelle formule.

Gli argomenti possono anche essere valori costanti o formule e le formule possono contenere altre funzioni. Un argomento di una funzione è nidificato se a sua volta è una funzione.

4.2.3 Gli operatori

Gli operatori specificano l'operazione da effettuare sugli operandi.

- Gli operatori aritmetici eseguono operazioni aritmetiche e generano risultati numerici.

- Gli operatori di confronto confrontano due valori e generano il valore logico VERO o FALSO.

- L'operatore di stringa unisce due o più valori stringa in un singolo valore stringa.

OPERATORE ARITMETICO	SIGNIFICATO
+ (segno più)	Addizione
- (segno meno)	Sottrazione
/ (barra)	Divisione
* (asterisco)	Moltiplicazione
% (percentuale)	Percentuale di un valore, ad es. 20%
^ (accento circonflesso) o **	Elevazione a potenza

Esempio

La formula (30^2 * 15%) eleva 30 alla potenza di 2 e moltiplica il risultato per 0,15 generando come risultato 135. Alcuni operatori agiscono su due operandi, altri su uno.

OPERATORE DI CONFRONTO	SIGNIFICATO
=	Uguale a
>	Maggiore di
<	Minore di
>=	Maggiore o uguale a
<=	Minore o uguale a
<>	Diverso da

Esempio

La formula (A1<30) genera il valore logico VERO se la cella A1 contiene un valore minore di 30 e genera valore FALSO se A1 contiene un valore maggiore o uguale a 30.

OPERATORE DI STRINGA	SIGNIFICATO
& (e commerciale)	Unisce o concatena due valori stringa per generare un solo valore stringa

Esempio

Se la cella A1 contiene la stringa "Eduardo de Filippo" la formula ("Il noto commediografo " & A1) genera come risultato la nuova stringa "Il noto commediografo Eduardo de Filippo".

OPERATORI LOGICI	SIGNIFICATO
#AND#	Restituisce VERO se tutti gli operandi hanno valore VERO
#NOT#	Inverte il valore logico dell'operando
#OR#	Restituisce VERO se uno o più operandi hanno valore VERO

Esempio

VERO #OR# VERO è uguale a VERO

VERO #AND# FALSO è uguale a FALSO

(2+2)=4 #AND# (2+3)=5 è uguale a VERO

#NOT# (1+1 = 2) è uguale a FALSO

Se le celle B1, B2, B3 contengono rispettivamente i valori VERO, FALSO e VERO:

B1 #AND# B2 #AND#B3 è uguale a FALSO

(B1 #OR# B2) #AND# B3 è uguale a VERO

Se la cella B4 contiene un numero compreso tra 1 e 100:

(B4>1) #AND# (B4<100) è uguale a VERO

5. SELEZIONE DI DATI PER CONDIZIONE (IF..THEN..ELSE)

Per molte analisi di dati, è necessario selezionare dall'elenco dei dati registrati in un foglio elettronico le righe che soddisfano un criterio di ricerca specificato.

L'elenco su di un foglio di lavoro è un intervallo contiguo di dati. Un elenco contiene lo stesso tipo di dati in ciascuna colonna: ad esempio, il nome dello studente in una colonna e il numero degli esami sostenuti nella colonna adiacente. Un elenco può essere utilizzato come database in cui le righe rappresentano i record e le colonne rappresentano i campi.

Ad esempio, in un elenco contenente i nomi degli studenti e l'anno di iscrizione, è possibile estrarre i nomi di quelli fuori corso. Per ogni riga dell'elenco viene eseguito un test tale che, se la differenza tra l'anno in corso e quello di iscrizione supera i quattro anni viene selezionato il nome dello studente, altrimenti viene escluso. La selezione dei dati viene realizzata attraverso la funzione @SE.

La funzione @SE viene utilizzata per eseguire test condizionali su valori e formule e per effettuare dei passaggi in base ai risultati ottenuti.

La sintassi è:

@SE(condizione; formula se condizione vera; formula se condizione falsa)

Argomento	Descrizione
condizione	un valore o un'espressione qualsiasi che può dare come risultato VERO o FALSO
formula se vera	risultato che viene restituito se la condizione è vera
formula se falsa	risultato che viene restituito se la condizione è falsa

La funzione @SE restituisce il valore determinato dalla condizione. Restituisce un valore se la condizione è vera, e un altro valore se risulta falsa.

Esempio

	A	B	C	D
1	Studente	AA. iscrizione
2	Mario Rossi	1998
3	Giovanni Verdi	1995	fuori corso
4	Milena Bianchi	1998
…	…	…
100	Franco Neri	1998

La formula @SE(2000-B2>4;"fuori corso;"") inserita nella cella C2, quando viene calcolata restituisce in C2 il risultato prodotto dal test, e cioè la stringa "fuori corso" se la differenza tra il contenuto della cella B2 ed il valore 2000 (l'anno corrente) è maggiore di 4, altrimenti restituisce la stringa vuota.

L'applicazione della formula alle altre righe si ottiene copiando da C2 per tutto l'intervallo di righe C3..C100. Il riferimento B2, essendo relativo, viene automaticamente modificato in modo da riflettere la nuova posizione di riga B3, B4, etc. Il riferimento di colonna (B) rimane invariato anche se relativo poiché la copia riguarda le stesse colonne, sia quella di origine dei dati sia quella di destinazione dei risultati.

Esempio

	A	B	C	D
1	Mesi	Consuntivo	Preventivo	Importo fuori budget
2	gennaio	L. 500.000	L. 700.000
3	febbraio	L. 700.000	L. 700.000
4	marzo	L. 800.000	L. 725.000	L. 75.000

La formula in D2 è copiata in tutte le celle dell'intervallo D3..D4. La copia trasforma i riferimenti relativi rispetto al numero di riga.

La formula @SE(B2>C2;C2-B2;"") inserita in D2, dopo il calcolo fornisce il valore FALSO (infatti 500.000 non è maggiore di 700.000) e quindi viene restituita la stringa vuota "".

Non confondere la cella o le celle rispetto alle quali viene eseguito il test dalla cella in cui viene registrato il risultato del test ossia il risultato della funzione @SE.

5.1 Selezione nidificata

In una funzione @SE è possibile passare come argomenti di formula_se_vero e di formula_se_falso altre funzioni @SE in modo da creare test più elaborati.

Esempio

La formula: @SE(Somma>89; "A"; @SE(Somma>79; "B"; @SE(Somma>69; "C"; @SE(Somma>59; "D"; "F")))) assegna una lettera ad ogni numero al quale si riferisce il nome somma, secondo il seguente schema:

- se la Somma è maggiore di 89, il risultato è A;

- se la Somma è compresa tra 80 e 89, il risultato è B;

- se la Somma è compresa tra 70 e 79, il risultato è C;

- se la Somma è compresa tra 60 e 69, il risultato è D;

- se la Somma è minore di 60, il risultato è F.

La seconda funzione @SE è anche l'argomento formula_se_falso della prima funzione @SE. Analogamente, la terza funzione @SE è l'argomento formula_se_falso della seconda funzione @SE. Ad esempio, se il primo test (Media>89) è VERO, verrà restituito "A". Se il primo test è FALSO, verrà calcolata la seconda funzione @SE e così via.

6. COPIA DI FORMULE

Quando è necessario eseguire la stessa formula in ciascuna cella di un intervallo di celle, la formula viene scritta in una cella dell'intervallo e quindi copiata nelle altre. Generalmente, si richiama il comando di copia indicando i riferimenti dell'area di origine e dell'area di destinazione. Le modalità operative dipendono dal programma utilizzato e, se il sistema è dotato di mouse, la copia si risolve con due click: uno per la selezione dell'area da copiare, e uno per la selezione dell'area di destinazione.

L'uso di riferimenti assoluti e relativi è importante quando una formula è copiata da una cella ad un'altra. Bisogna usare riferimenti assoluti quando dopo la copia o lo spostamento i riferimenti contenuti nella formula di destinazione appaiano esattamente come nella formula originale; bisogna usare riferimenti relativi o misti, quando il riferimento nella formula copiata o spostata deve essere automaticamente modificato in modo da riflettere la nuova posizione.