Scarica Guida all'utilizzo di Microsoft Excel e più Schemi e mappe concettuali in PDF di Inglese solo su Docsity!
Università di Firenze e Siena Prof. D’Agostino
DOCOMENTAZIONE ED ESERCIZI EXCEL
- INTRODUZIONE A MICROSOFT EXCEL.......................................................................... INDICE
- FORMULE...........................................................................................................................
- FUNZIONI............................................................................................................................
- COMANDI DI UTILIZZO PIU’ COMUNE NELLA BARRA DEI MENU’...............................
- UTILIZZO DI FORMULE E FUNZIONI................................................................................
- LE FUNZIONI.......................................................................................................................
- UTILIZZO DI DATE, ORE, PERCENTUALI E FORMATO CELLE.....................................
- RIFERIMENTI A CASELLE NELLE FORMULE..................................................................
- LA FUNZIONE SE...............................................................................................................
- COLLEGAMENTO DI FOGLI DI LAVORO.......................................................................
- COLLEGAMENTO DI CARTELLE DI LAVORO................................................................
- GRAFICI............................................................................................................................
- DATABASE CON EXCEL..................................................................................................
- ESERCITAZIONI...............................................................................................................
INTRODUZIONE A MICROSOFT EXCEL
Excel:
è un applicativo di tipo FOGLIO ELETTRONICO (o FOGLIO DI CALCOLO ) che può essere
utilizzato per:
eseguire calcoli lunghi e ripetitivi
organizzare dati in forma tabellare
produrre grafici (istogrammi, a torta, ...)
memorizzare elenchi di dati (agenda telefonica, elenco clienti, ...)
Ogni documento Excel è detto cartella di lavoro.
Una cartella di lavoro è composto da un insieme di fogli di lavoro.
Un foglio di lavoro è composto da un insieme di celle.
In ogni cella è possibile inserire testo, numeri, date, formule per il calcolo di espressioni.
Ogni riga di un foglio di lavoro è identificata con un numero.
Ogni colonna è identificata con una lettera.
Ogni cella ha un indirizzo che la distingue da tutte le altre caselle, ed è dato da <identificatore
di colonna, identificatore di riga>.
Per cella attiva si intende la casella evidenziata in un certo istante, il cui indirizzo appare nella
barra della formula della finestra dell’applicazione.
Per intervallo di celle si intende un insieme di celle adiacenti:
sulla stessa colonna (ad esempio: B5:B10)
sulla stessa riga (ad esempio: D3:G3)
a forma rettangolare (ad esempio: A2:C7)
FORMULE
Le formule sono espressioni algebriche contenenti riferimenti alle celle, numeri ed operatori
aritmetici (+, -, *, /, ^).
Una formula inizia sempre con il simbolo =.
Grazie al ricalcolo immediato la modifica del valore di una cella implica l’aggiornamento dei
valori prodotti da tutte le formule con riferimenti a quella cella, MA volendo è possibile impostare
anche il calcolo manuale in Strumenti – Opzioni – calcolo.
FUNZIONI
Le funzioni sono operazioni più o meno complesse che è possibile inserire nelle formule.
Una funzione utilizza la seguente sintassi: NomeFunzione (Argomento1; Argomento2; ...)
Gli argomenti (o parametri ) sono i dati di ingresso alla funzione, in base ai quali viene
calcolato il risultato restituito dalla funzione.
Ad esempio, la formula =SOMMA(A1:A5) utilizza la funzione SOMMA per calcolare la somma
dei numeri nell’intervallo A1:A5. Il simbolo “;” invece corrisponde all’utilizzo non di un intervallo
ma dei singoli valori contenuti nelle celle evidenziate all’interno delle parentesi tonde.
Formato
Celle Formattazione dei numeri Allineamento del contenuto delle celle Tipo, stile e dimensione del carattere Impostazione di bordi e motivi per le celle Riga Colonna Impostazione della larghezza delle colonne Formattazione automatica Formattazione automatica del foglio di lavoro
Strumenti
Personalizza Personalizzazione delle barre degli strumenti e dei comandi di Excel Opzioni Configurazione delle opzioni di funzionamento di Excel: in particolare impostazione degli elenchi personalizzati
Finestra
Nuova finestra Creazione di una nuova finestra di documento Disponi Disposizione della visualizzazione di tutte le finestre di documenti aperte
Sommario e indice Guida in linea di Excel: sommario, indice analitico, ricerca con trova Guida rapida Informazioni sintetiche su qualsiasi oggetto visualizzato Informazioni su Excel Versione del programma: ad esempio Excel 97 Esercitazione n. 1
UTILIZZO DI FORMULE E FUNZIONI
Per sommare il contenuto delle caselle A1, A2 e A3 e' possibile utilizzare:
- la formula =A1 + A2 + A
- la formula =SOMMA(A1; A2; A3)
ricorrendo alla funzione SOMMA ed utilizzando tre parametri per indicare le caselle da
sommare
Una soluzione alternativa utilizza comodamente un solo parametro dato dall'indirizzo
dell'intervallo di caselle da sommare: =SOMMA(A1:A3).
Esercitazione n. 2 Esercitazione n. 3 Esercitazione n. 4 Esercitazione n. 5 Esercitazione n. 6
LE FUNZIONI
Le funzioni sono operazioni complesse che è possibile inserire nelle formule.
Gli argomenti (o parametri ) sono i dati in ingresso alla funzioni, in base ai quali viene calcolato
il risultato restituito dalla funzione. Il simbolo ; separa gli argomenti di una funzione.
Ad esempio per calcolare il valore medio contenuto negli intervalli di celle A1:A5 e C1:C5 si
utilizza la funzione MEDIA con la seguente sintassi: = MEDIA (A1:A5; C1:C5)
Le funzioni si possono inserire direttamente nelle caselle come formule (la funzione apparirà
nella Barra della Formula mentre il risultato apparirà nella cella) oppure tramite
l'AUTOCOMPOSIZIONE FUNZIONE:
1. selezionare la casella in cui inserire la funzione
2. pulsante AUTOCOMPOSIZIONE
FUNZIONE (Fx)
3. scegliere la funzione da inserire
4. inserire nelle caselle di
testo NUM1, NUM2, ... gli
argomenti per la funzione
scelta
Esercitazione n. 7 Esercitazione n. 8
La funzione ARROTONDA arrotonda un valore 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.
RELATIVI : modificati nelle copie di formule
ASSOLUTI : non modificati nelle copie di formule (utilizzo del simbolo $);
Ad esempio: nella formula =B1*$C$1 il riferimento alla cella B2 è assoluto e quindi se
trascinata o copiata in verticale accadrà che nella cella
comparirà automaticamente =B2*$C$
Quindi, copiando una formula contenente riferimenti relativi, i riferimenti della formula incollata
verranno modificati automaticamente per adeguarsi alla nuova posizione della formula.
Esercitazione n. 11 Esercitazione n. 12
LA FUNZIONE SE
La funzione logica SE analizza dei dati basati su una condizione. Essa fornisce un dato risultato
se la condizione di partenza risulta vera ed un altro risultato se invece si dimostra falsa.
La funzione SE(test; se_vero; se_falso) è composta da tre argomenti:
un test con esito VERO o FALSO da condurre di solito su una cella. Nel test si possono
usare gli operatori di relazione =, <>, >=, <=, >, <.
L’azione da fare se il test è vero
L’azione da fare se il test è falso
Es: = SE(B2<100C5+C6;”diverso”) che effettua un calcolo o fa comparire un commento a
seconda del test
E’ possibile costruire funzioni con vari SE annidati (uno all’interno dell’altro al posto del se_vero
o del se_falso per avere più di due alternative di calcolo o commenti.
Esercitazione n. 13 Esercitazione n. 14 Esercitazione n. 15
COLLEGAMENTO DI FOGLI DI LAVORO
Una cartella di lavoro Excel è formata in generale da più fogli di lavoro.
Sono possibili riferimenti a caselle o intervalli di altri foglio di lavoro a partire dal foglio attivo.
Ad esempio, per eseguire nel Foglio2 la somma di due caselle contenute nel foglio1, inserire la
formula:
=Foglio1!A1 + Foglio1!A
Esercitazione n. 16
COLLEGAMENTO DI CARTELLE DI LAVORO
Ci sono casi in cui bisogna utilizzare informazioni contenute in un’altra cartella di lavoro (file
Excel).
Ad esempio, si consideri una serie di budget per alcuni dipartimenti che operano nell’area del
marketing di un’ipotetica azienda: rapporti con i clienti, pubbliche relazioni, pubblicità.
Si potrebbe creare una cartella di lavoro con il budget relativo a tutta l’area marketing,
contenente formule con riferimenti esterni.
Ad esempio, per eseguire nel Foglio1 di una cartella di lavoro la somma di due caselle
contenute nel Foglio1 della cartella di lavoro NomeFile, inserire la formula:
=[NomeFile]Foglio1!A1 + [NomeFile]Foglio1!A
Nota bene: il risultato prodotto dalle formule è aggiornato automaticamente al variare dei
dati di origine contenuti in un file distinto.
GRAFICI
Un grafico è una rappresentazione grafica dei dati di un foglio di lavoro.
Per creare un grafico:
1. impostare la tabella dati, con intestazioni di righe e di colonne, senza righe o colonne vuote
2. comando Inserisci, Grafico oppure pulsante Autocomposizione grafico sulla barra degli
strumenti standard
Ad esempio, si consideri il fatturato nei primi sei mesi dell'anno 2001 conseguito con la vendita
di alcuni titoli:
GEN FEB MAR APR MAG GIU
Divina Commedia 2.000.000 3.000.000 4.000.000 2.000.000 3.000.000 4.000. Promessi Sposi 1.500.000 2.000.000 500.000 1.500.000 2.000.000 500. Malavoglia 1.300.000 1.200.000 500.000 1.300.000 1.200.000 500. Si intende creare un istogramma che rappresenta la distribuzione delle vendite nei vari mesi. I principali elementi che possono essere individuati in un grafico sono i seguenti: Titoli : etichette assegnate agli assi X e Y e al grafico nel complesso
Per database si intende una o più tabelle dati contenenti informazioni logicamente collegate
(informazioni su clienti, articoli di magazzino, elenco indirizzi per le spedizioni effettuate della
propria società, ordini, fatture, ...).
La terminologia informatica utilizzata è la seguente:
Tabella dati: insieme di record descritti in una tabella dati
Record : insieme di informazioni su un determinato oggetto (ad esempio un cliente); ogni riga
della tabella è un record.
Campo : ogni informazione che fa parte di un record (ad esempio, cognome e nome del cliente,
numero telefonico, …); le colonne individuano i campi.
Un database si costruisce nel seguente modo:
1. indicare nella riga 1 del foglio di lavoro i nomi dei campi del data base
2. inserire i record nelle righe successive.
E’ possibile:
ordinare i record in base ad uno o più campi (comando Dati, Ordina )
filtrare record in base ai criteri di ricerca (comando Dati, Filtro, Filtro automatico )
I filtri sono espressioni che possono essere vere o false per ciascun record della tabella
dati.
Nei filtri vengono utilizzati:
- operatori di relazione per confrontare campi con valori: >, <, >=, <=, =, <>
- il caratteri jolly * che sostituisce “qualsiasi combinazione di caratteri”
- gli operatori logici AND combinazione di filtri elementari che devono essere tutti
veri
OR : combinazione di filtri elementari di cui almeno uno deve
essere vero
ESERCITAZIONI
Esercitazione n. 1
Creare un documento Excel contenente la seguente tabella dati relativa alle vendite di alcuni libri nel 1^ semestre dell’anno 2001 in lire. Salvare il documento su disco con nome “Vendite primo semestre”.
VENDITE ANNO 2001
GEN FEB MAR APR MAG GIU
Divina Commedia 1000000 3000000 4000000 2000000 3000000 4000000
Promessi Sposi 1500000 2000000 500000 1500000 2000000 500000
Malavoglia 1300000 1200000 500000 1300000 1200000 500000
Mastro Don Gesualdo 600000 300000 100000 600000 300000 100000
Adelchi 1500000 3000000 3000000 1500000 3000000 3000000
Il padrone del mondo 1500000 1000000 500000 1500000 1000000 500000
Piccolo mondo antico 1300000 1200000 540000 1300000 1200000 540000
Il medioevo 600000 300000 100000 600000 300000 100000
Modificare il documento "Vendite primo semestre" nel seguente modo:
Introdurre i bordi e i colori indicati
Piè di pagina in basso a destra con nome e cognome
Centrare orizzontalmente nei margini la tabella di dati
Eventualmente provare il risultato prodotto dalla formattazione automatica
fino ad ottenere il prospetto di cui sopra
Esercitazione n. 2
Chirurgia donne 120 73 Medicina uomini 125 52 Medicina donne 100 71 Urologia 82 72 Ortopedia 72 64 Neurologia 44 24 Infettivi 45 14 Per ciascuno dei due giorni bisogna calcolare: Percentuale di posti occupati sul totale dei posti letto disponibili in ciascun reparto Numero totale di posti letto disponibili Numero totale di posti letto occupati Numero totale di posti letto rimasti liberi Percentuale di posti occupati sul totale dei posti letto disponibili nell’ospedale Piano di lavoro
- creare il documento denominata “Statistica Ospedale” con un foglio di lavoro per i dati del 12/12/
- utilizzare un secondo foglio di lavoro nello stesso documento per memorizzare i dati relativi al giorno 13/12/1998 (utilizzare i pulsanti Taglia, Copia e Incolla per duplicare le formule necessarie per il secondo giorno della statistica) Nota. Dati X e Y, per calcolare la percentuale di X rispetto ad Y si procede nel seguente modo: impostare la formula =X/Y applicare alla casella contenente la formula lo stile percentuale Dato X, per calcolare, ad esempio, il 20% di X, si imposta la formula =X20% Esercitazione n. 5* L’industriale Mario Casali di Milano ha venduto le seguenti merci al commerciante Sergio Varese di Varese: n. 10 armadi di lamiera a porte battenti a euro 567 cad. n. 17 armadi di lamiera a porte scorrevoli a euro 472 cad., sconto incondizionato 6% n. 32 schedari a carrello con separatori interni a euro 235 cad., sconto incondizionato 7% n. 25 sedie girevoli a 5 ruote e con regolazione dell’altezza a Lire 125 cad., sconto incondizionato 6% n. 25 tavoli in laminato 120 * 90 * 72 a euro 130, sconto incondizionato 6% Le spese di trasporto sono a carico del compratore con automezzo del venditore e ammontano a euro 500. L’aliquota IVA applicata per tutti i prodotti acquistati è del 20%. Costruire un foglio di lavoro con i dati per una fattura. Nota: Prima si applica lo sconto incondizionato infine si applica l’IVA Esercitazione 6 Si hanno i seguenti dati sulla produzione di alcuni prodotti nel mese di gennaio in una generica azienda:
- prodotto A: 100 unità prodotte
- prodotto B: 120 unità prodotte
- prodotto C: 130 unità prodotte
- prodotto C: 112 unità prodotte
Si sa che per produrre una unità dei prodotti suddetti sono necessarie le seguenti ore di lavoro per un dipendente:
- prodotto A: 12 ore
- prodotto B: 10 ore
- prodotto C: 11 ore
- prodotto C: 12 ore La mano d’opera costa L. 55.000 all’ora. I costi fissi (bolletta riscaldamento stabilimento, pulizia, ecc…) ammontano complessivamente a L. 11.000.000. Calcolare il costo totale della produzione di gennaio. Confrontare questo costo con quello che si avrebbe se la mano d’opera costasse L.60.000 all’ora.
Esercitazione n. 7
Creare una tabella di dati con Excel contenente le temperature registrate dalle 0.00 alle 24.00 in una città campione in una giornata. Calcolare: la temperatura media registrata l’escursione termica, ossia la differenza tra la massima e la minima temperatura registrata Esercitazione n. 8 L'amministratore di un complesso di cinque villette unifamiliari a schiera con riscaldamento centralizzato ha presentato un rendiconto di fine anno che evidenzia le seguenti spese: spese generali: euro. 3. spese di riscaldamento: euro 14. spese di illuminazione: euro 950 spese di pulizia: euro 2. Tutte le spese sono ripartire in base ai millesimi di proprietà e ogni famiglia ha pagato un anticipo a inizio anno: Millesimi Anticipi Batistuta 120 2. Del Piero 220 3. Ferrara 340 5. Peruzzi 140 1. Simeone 180 3. Piano di lavoro Progettare un prospetto che evidenzi il riparto delle spese e i saldi a credito o a debito per famiglia, tenendo conto degli anticipi Arrotondare tutti i saldi a debito o a credito dei condomini. Indicare, infine, qual è l'importo medio pagato da una famiglia per le spese condominiali. Nota: descrizione della funzione ARROTONDA
Esercitazione n. 9 (ore)
Impostare un foglio di lavoro per il calcolo delle ore lavorate da un consulente di un’azienda in un certo periodo. Per ogni giornata lavorativa si conosce l’ora di ingresso e l’ora di uscita, senza pausa pranzo. Data Ora ingresso Ora uscita 1/2/1999 15.00 19. 2/2/1999 17.00 20. 3/2/1999 15.00 19. 4/2/1999 17.00 21. 5/2/1999 16.30 20. 6/2/1999 15.00 19.
Si ha una tabella con le vendite effettuate dai vari agenti di zona della nostra azienda. Supponiamo che la provvigione agli agenti dipenda dal volume di affari da essi realizzato: pari al 10% per fatturati fino a 50.000, pari al 12% per fatturati superiori. Vogliamo che Excel, automaticamente, analizzi la colonna dei volumi e produca sulla colonna a fianco il calcolo esatto della provvigione. Calcolare, infine, il fatturato totale realizzato e l’importo totale delle provvigioni riconosciute agli agenti. I dati di partenza, da includere nel foglio di lavoro, sono i seguenti:
Calcolo provvigioni
Valore soglia 50. % fino a 50 mil. 10% % oltre 50 mil. 12%
Agente Fatturato …
Rossi 38.000.000 … Crivellaro 55.000 … Mallozzi 49.000 … Macchini 70.000 … Tonelli 65.000 … De Zorzi 42.000 … Esercitazione n. 14 In un archivio delle fatture insolute da parte di un’azienda si decide di compiere un’azione legale per le fatture non pagate da oltre 60 giorni, per le rimanenti si vuole effettuare una telefonata di sollecito. Si vuole costruire in Excel un sistema automatico di segnalazione delle fatture. La data di riferimento è il 20/07/98. Si può utilizzare la funzione OGGI() (vedere la guida in linea per chiarimenti) al posto della data fissa per rendere il foglio di lavoro più flessibile. FATTURE NON RISCOSSE Soglia 60 Data 20/07/ Cliente prodotto Scadenza gg ritardo Azione Guzzonato computer 10/06/98 40 Telefonata Luongo stampanti 05/05/98 76 Azione legale Mallozzi computer 19/05/98 … … Bonollo computer 15/06/98 … … Rossi stampanti 04/04/98 … … Bianchi monitor 10/07/98 … … Esercitazione n. 15 Un test somministrato a 10 candidati consiste nel risolvere 3 esercizi. Il punteggio attribuito a ciascun esercizio è un numero compreso tra 1 e 10. La valutazione è espressa da un aggettivo secondo la seguente tabella: < 18 -> Non sufficiente 18 – 25 -> Sufficiente 26 – 30 -> Buono Nota: tenere conto che per superare il test è necessario riportare almeno 6 in tutti e tre gli esercizi. Esempio: Nominativo Esercizio 1 Esercizio 2 Esercizio 3 Punteggio Esito Rossi 6 6 7 19 Sufficiente Bianchi 5 8 9 22 Non sufficiente Gallo 8 9 9 26 Buono Zanetti Totti Montero Cannavaro
Numero candidati con esito positivo …..
Percentuale candidati con esito positivo …..
Punteggio massimo riportato da un candidato …..
Punteggio minimo riportato da un candidato …..
Punteggio medio del test ….
Utilizzare la funzione logica E , che restituisce VERO se tutti gli argomenti hanno valore VERO, restituisce FALSO in caso contrario. Utilizzare la funzione CONTA.SE per contare tutti i sufficienti Nascondere colonne contenenti dati di supporto con il comando Formato, Colonna, Nascondi Esercitazione n. 16 Costruire una cartella di lavoro Excel contenente 4 fogli di lavoro denominato PrimoT, SecondoT, TerzoT, QuartoT. Ciascun foglio di lavoro contiene alcuni dati sui costi di produzione sostenuti nei vari periodi: prelevare il file 4fogli.xls per utilizzare i dati già inseriti. Il foglio di lavoro Budget contiene il preventivo di spesa per ciascuna costo. Completare il foglio di lavoro Budget con il consuntivo di fine anno per ciascun costo; inserire infine una colonna con lo scostamento di ogni costo rispetto al suo preventivo. Ad esempio: Confronto Budget annuo - consuntivo Categorie Budget annuo Consuntivo Scostamento PERSONALE Salari e stipendi 400.000 350.000 50. Oneri sociali 140.000 155.000 -15. Trattamento di fine rapporto 33.000 33.000 0 Altri costi 9.000 8.500 500 Totale … … … Nota: nella colonna scostamento evidenziare i numeri negativi in rosso (comando Formato, celle…) Esercitazione n. 17 Creare il seguente documento. Istituto Meteorologico Regionale Via Duchessa Jolanda, 12 10121 Torino Torino, 12 gennaio 1999 Come richiesto Vi inviamo i dati sulla piovosità in mm. registrata mediamente a Casale Monferrato nel primo semestre: Gennaio Febbraio Marzo Aprile Maggio Giugno 67 52 112 115 98 42
Esercitazione n. 20 Dopo aver introdotto i dati della seguente tabella (dati CENSIS 1991), memorizzata con il nome IDENTIKIT, creare tre grafici a torta: uno per l’età, uno per il titolo di studio e uno per la qualifica professionale. IDENTIKIT EXTRA-COMUNITARI ETA’ TITOLO DI STUDIO QUALIFICA PROFESSIONALE Fino a 18 anni 1,9 nessuno 74,6 op. generico 81, Da 19 a 24 anni 23,4 obbligo 20,0 op. qualificato 15, Da 25 a 29 anni 33,7 diploma 4,6 op. speciale 1, Oltre 30 anni 41,0 laurea 0,8 impiegati 1, Esercitazione n. 21 Creare la seguente tabella e memorizzarla sotto il nome ISTRUZIONE (dati OCSE). SPESA PUBBLICA PER L’ISTRUZIONE NEI 10 PAESI PIU’ SVILUPPATI. NAZIONE PERCENTUALE SUL PIL PERCENTUALE SUL TOTALE DELLA SPESA PUBBLICA Italia 4,8 9, Francia 5,1 10, Germania 4,3 9, Gran Bretagna 4,7 11, Creare un grafico ad istogramma che contenga come etichette gli stati e come barre le due percentuali; inserire i titoli e le legende opportuni. Esercitazione n. 22 La tabella Fumatori contiene alcuni dati su un campione di persone delle provincie di Biella e di Alessandria.
1. Inserire i nomi dei campi come intestazione di colonne alla tabella dati 2. Ordinamento 2.1. Ordinare i record per Nominativo 2.2. Ordinare i record per provincia e per località di residenza in ogni provincia 2.3. Ordinare i record per sesso 2.4. Ordinare i record per sesso e per codice 3. Filtro automatico 3.1. Filtrare tutti dati sull’individuo con codice C 3.2. Filtrare tutti gli individui il cui cognome è Falciani 3.3. Filtrare tutti i nati dopo il 1/1/ 3.4. Filtrare tutti i nati nel 1968 3.5. Filtrare tutti i nati a Cossato o Santhià 3.6. Filtrare tutte gli individui il cui cognome inizia con la lettera B 3.7. Filtrare tutti i nati dopo il ’70 e residenti nella provincia di Biella 3.8. Filtrare tutti i maschi residenti nella provincia di Alessandria 3.9. Filtrare tutti i fumatori di nome Bruno 3.10. Separare l’elenco dei maschi e l’elenco delle femmine, in ordine di codice in due fogli di lavoro distinti, mantenendo inalterato il foglio Fumatori 3.11. Separare l’elenco dei fumatori e l’elenco dei non fumatori, in ordine di codice in due fogli di lavoro distinti, mantenendo inalterato il foglio Fumatori
