Descrizione

Esercitazioni

Obiettivi della lezione

Saper utilizzare il consolidamento e le varie funzioni di excel.

Indice lezione

Lezione 26 - Foglio Elettronico

26.1 Esercizio sul consolidamento;

26.2 Esercizio sulle funzioni.

Dati sulla lezione

Versione:	v1.0
Tempi:	1 ora / 60 minuti
Data svolgimento lezione:	04 Giugno 2003
Autore dei contenuti:	Vastapane Marco Attilio
Autore materiali didattici:	Togni Francesca

La Pratica

26.1 - Esercizio sul consolidamento

Nel seguente e esercizio andremo ad esaminare le potenzialità del consolidamento.

q Nella figura sotto riportata sono raffigurate tre diverse cartelle di lavoro composte da un unico foglio. Esse verranno consolidate. Questi fogli di lavoro riepilogano le vendite dei prodotti mensili distribuite sulle 4 zone, ma non tutti considerano gli stessi prodotti ed inoltre questi non sono elencati nel medesimo ordine. In altre parole, i fogli di lavoro non sono progettati in modo identico, il che rende difficile la creazione delle formule di consolidamento. Ho utilizzato prezzi assai bassi, per rendere più immediata la potenzialità di questo strumento.

Prodotto	Nord	Sud	Est	File : Regione1.xls Foglio1 Ovest
Gonna	14	23	40	21
Minigonna	16	28	35	30
Felpa	18	33	30	39
Polo	20	38	25	48

File : Regione2.xls

Foglio1

Prodotto	Nord	Sud	Est	Ovest
Pantaloni	20	31	60	72
Gonna	24	33	65	77
Minigonna	28	35	70	82
T-shirt	32	37	75	87
Bermuda	36	39	80	92
Maglione	40	41	85	97
Felpa	44	43	90	43

Prodotto	Nord	Sud	Est	File : Regione3.xls Foglio1 Ovest
Giacca	30	51	71	91
Gonna	34	56	80	104
Minigonna	38	61	89	117
Pantaloni	42	66	98	130
Jeans	46	71	107	143
Polo	50	76	116	156

q Per consolidare queste informazioni occorre iniziare con una nuova cartella di lavoro. Le cartelle di lavoro di origine possono essere aperte o chiuse. Scegliere DatièConsolida; sullo schermo compare la finestra di dialogo Consolida

q Selezionare il tipo di consolidamento; nel nostro esempio andare a selezionare somma nella casella Funzione

q Inserire il riferimento per il primo foglio di lavoro da consolidare. Se la cartella di lavoro è aperta si può porre il puntatore sul riferimento; se è chiusa si faccia clic sul pulsante sfoglia per individuare il file sul disco.Per effettuare selezioni in modo più semplice, è possibile in alternativa aprire le cartelle di lavoro, per poi andarle a richiamare dal menu Finestra.

q Il riferimento deve includere un intervallo: nel nostro esempio andiamo ad utilizzare l’intervallo A1:E40 o superiore; come avrete notato questo intervallo è più esteso del vero intervallo da consolidare, ma assicura che il consolidamento funzionerà anche se vengono aggiunte nuove righe al file di origine. Quando il riferimento nel riquadro Riferimento è corretto, cliccare il pulsante Aggiungi per aggiungerlo all’elenco tutti i riferimenti.

q Inserire il riferimento per il secondo foglio di lavoro. In questo caso sarà sufficiente andare a modificare il riferimento esistente cambiando il nome Regione1 in Regione 2; procedere cliccando il pulsante aggiungi. Questo riferimento finale viene aggiunto all’elenco tutti i riferimenti.

q Inserire il riferimento per il terzo foglio di lavoro. In questo caso sarà sufficiente andare a modificare il riferimento esistente cambiando il nome Regione2 in Regione 3; procedere cliccando il pulsante aggiungi. Questo riferimento finale viene aggiunto all’elenco tutti i riferimenti..

q Poiché i fogli di lavoro non sono stati progettati in modo identico, andiamo a selezionare le caselle di controllo colonna sinistra e riga superiore in modo che Excel confronti dati usando le etichette.

q Selezionare la casella di controllo crea collegamenti con i dati originari così che Excel crei una struttura con riferimenti esterni.

q Premere il pulsante Ok per iniziare il consolidamento

In pochi secondi viene effettuato e il consolidamento iniziando dalla cella relativa. Si noti in che è stata creata una struttura compressa per mostrare solo i subtotali per ogni prodotto; se la si espande verrebbero visualizzati i dettagli. Esaminando attentamente si scoprirà che ogni cella di dettaglio è una formula di riferimento esterna che usa la cella appropriata nel file di origine. L’intervallo di destinazione viene aggiornato automaticamente se viene modificato un dato.

Andando a modificare i dati di origine, viene aggiornato il file di Excel che ci presenta il riepilogo, creato utilizzando il consolidamento.

Andiamo a notare che viene fornito un riepilogo organizzato in modo relativamente semplice. Quando l’elenco rimane “contratto”, come nei subtotali, abbiamo una visione generale dell’insieme dei dati.

Sono presenti tutti gli articoli venduti nelle varie zone, e di essi viene calcolato il totale.

Ad esempio, il totale del costo dei Pantaloni venduti nella zona Nord è 62. Se andiamo ad esplodere il foglio di Excel possiamo notare i vari dettagli degli articoli,con le quantità prese dalle singole zone,memorizzate sui vari fogli di Excel , Regione1.xls, Regione2.xls, Regione3.xls

Andiamo a modificare ora la tabella della regione1 nel modo seguente, andando a sostituire ai vari prodotti la stessa denominazione.

Prodotto	Nord	Sud	Est	Ovest
Gonna	14	23	40	21
Gonna	16	28	35	30
Gonna	18	33	30	39
Gonna	20	38	25	48

Ripetiamo la procedura prima analizzata, andando a sostituire la funzione somma, con quella conteggio.

Come risultato avremo, ad esempio per le Gonne, il numero di occorrenze di quel dato , diviso per le nostre zone, all’interno dei fogli di lavoro utilizzati come origine.

		Nord	Sud	Est	Ovest
	Regione2	25	31	60	72
	Regione3	42	66	98	130
Pantaloni		2	2	2	2
	Regione3	30	51	71	91
Giacca		1	1	1	1
	Regione1	14	23	40	21
		16	28	35	30
		18	33	30	39
		20	38	25	48
	Regione2	24	33	65	77
	Regione3	34	56	80	104
Gonna		6	6	6	6
	Regione2	28	35	70	82
	Regione3	38	61	89	117
Minigonna		2	2	2	2
	Regione2	32	37	75	87
T-shirt		1	1	1	1
	Regione2	36	39	80	92
Bermuda		1	1	1	1
	Regione2	40	41	85	97
Maglione		1	1	1	1
	Regione2	44	43	90	43
Felpa		1	1	1	1
	Regione3	46	71	107	143
Jeans		1	1	1	1
	Regione3	50	76	116	156
Polo		1	1	1	1

Infatti, l’articolo preso in esame è presente 6 volte in totale per le varie zone: 1 volta nella regione2e regione3, e 4 volte nella regione 1.

26.2 - Esercizio sulle funzioni

Creare un foglio di calcolo che conti tutte le occorrenze dei numeri da 0 a 10, generati con la funzione Casuale, ma prima di iniziare, leggete tutto il testo dell’esercizio.

q Nella cella A1 di una nuova cartella di lavoro inserire la seguente formula:

=INT(CASUALE()*10)

q Trascinarla fino alla cella A100

q Nella calla B1 scrivere la formula che andrà a contare tutte le volte che è stato scritto il numero 0. =CONTA.SE(A1:A100;0)

q Nella cella B2 occorrerà scrivere l’analoga formula per contare tutte le volte che è stato scritto il numero 1. =CONTA.SE(A1:A100;1), e così via fino alla formula = CONTA.SE(L1:L100;10)

q Ovviamente questa via, seppur esatta, è assai lunga e macchinosa. Scrivere quindi un’unica formula,utilizzando i riferimenti Assoluti, in grado, non appena trascinata fino alla cella B11 di contare quante volte sono stati generati i numeri da 0 a 10.

q Come si potrà constatare, il numero 10 non viene MAI scritto, quindi il relativo contatore sarà uguale a 0.

Nell’immagine, ecco come dovrebbe risultare il foglio di calcolo.

In blu, il numero di volte che il numero è “uscito”

In rosso, il numero a cui si riferisce l’estrazione.

Attenzione al numero rosso, necessario per la redazione della formula con i riferimenti assoluti…

Non è obbligatorio inviare l’esercizio al tutor.

Feedback

Per chiarimenti, si prega di lasciare la domanda direttamente sul Forum della piattaforma Maestra.