Lezione 8: PROMEMORIA FORMULE e funzionalità EXCEL
FORMULE PRINCIPALI:
SOMMA, MEDIA, MIN, MAX, CONTA.VALORI
=nome_della_formula(intervallo)
es. =SOMMA(A4:B5) =MEDIA(A4:B5) =CONTA.VALORI(A4:B5)
CONTEGGI (si vuole ottenere un conteggio di celle con una o più condizioni)
Conta le celle in un intervallo se soddisfano una condizione sullo stesso intervallo.
=CONTA.SE(intervallo;"condizione")
Conta le celle che soddisfano due o più condizioni su due o più intervalli.
=CONTA.PIU.SE(int1;"cond1";int2;"cond2";...)
SOMME CONDIZIONATE (vogliamo ottenere un totale con una o più condizioni)
Somma i valori di una colonna con una condizione sulla stessa colonna
=SOMMA.SE(intervallo;"condizione")
Somma i valori dell'intervallo iniziale (int_dasommare), verificando una o più condizioni su altri intervalli
=SOMMA.PIU.SE(int_dasommare;int1;"cond1";...)
LOGICHE (restituiscono come risultato VERO o FALSO)
E' vera se TUTTE le relazioni contenute sono VERE E(relazione1;relazione2;...)
E' vera se ALMENO UNA delle relazioni contenute è VERA O(relazione1;relazione2;...)
SE
Restituisce 2 valori alternativi tra loro in base al valore di verità di una condizione da testare. N.B. la condizione può essere composta; un'eventuale condizione multipla (in più step) si ottiene con un SE annidato
Se semplice
=SE(condizione;uscita_vera;uscita_falsa) es. =SE(A5>5;"OK";"NO")
Se ANNIDATO
=SE(condizione1;SE(condizione2;uscita_v;uscita_f);uscita_f)
CERCA.VERT
Ricerca un valore (chiave) all'interno di una matrice tabella situata dalla colonna in cui si trova la chiave verso destra. In uno dei parametri (il terzo) viene indicata la colonna in cui si vuole trovare il valore corrispondente alla chiave.
Es. Cerca il valore corrispondente alla chiave "GA776JH" (Colonna A) sulla colonna Proprietari (colonna C)
=CERCA.VERT("GA776JH";$A$1:$C$100;3)
Casi particolari: Se come quarto parametro si inserisce FALSO si trova una corrispondenza ESATTA; se omesso o VERO si trova la corrispondenza più vicina.
DATE
=OGGI() restituisce la data odierna
=ADESSO() restituisce la data e l'ora attuali
=DATA.DIFF(data1;data2;"parametro") parametro può essere Y, M, D in base a ciò che vogliamo: Y=anno, M=mese, D=giorno
=ANNO(data) restituisce l'anno a partire da una data
=MESE(data) restituisce il mese a partire da una data
=DATA(ANNO(cella);MESE(cella);GIORNO(cella)) si usa per aggiungere un numero di anni o un numero di mesi ad una data e trovarne una successiva
=data+numero somma un numero di giorni ad una data
TESTI
=CONCATENA(cella1;cella2;...)
Unisce due o più celle (o anche testi o valori numerici).
Es. =CONCATENA(A1;"OK";A4) mostrerà il contenuto di A1 seguito da "OK" seguito dal contenuto di A4
=MAIUSC(cella) Riporta in maiuscolo il contenuto di una cella o anche un testo
=MAIUSC.INIZ(cella) Riporta in maiuscolo solo la prima lettera di una cella o di un testo
=MINUSC(cella) Riporta in minuscolo il contenuto di una cella
=SOSTITUISCI(cella_in_cui_si_trova_testo;”parte_da_sostituire”;”valore_sostituto”)
=STRINGA.ESTRAI(cella_da_cui_estrarre;posizione_nel_testo;num_caratteri)
Estrae una parte del testo contenuto in una cella a partire da una posizione e indicando il numero di caratteri da estrarre.
Es. A2="Testo di prova"
Vogliamo estrarre 3 caratteri a partire dalla quinta posizione
=STRINGA.ESTRAI(A2;5;3) -> o d
FORMULE DI RILEVAZIONE ERRORI NEL FOGLIO
Si utilizzano a scopo "diagnostico" sul foglio per rilevare eventuali errori nelle formule o nei valori.
Rilevano quindi tutti gli errori principali di excel: #NOME?, #DIV/0?, #RIF?, #N/D?, #VALORE?
=SE.ERRORE(cella;"messaggio da mostrare") visualizza il messaggio di errore se nella cella segnalata compare uno degli errori possibili
=VAL.ERRORE(cella) visualizza VERO se nella cella c'è un errore; FALSO altrimenti
RIFERIMENTI
SEMPLICE o RELATIVO es. A2 una cella con la sua colonna e riga MISTO $A2 oppure A$2 si "blocca" o la colonna o la riga ASSOLUTO $A$2 sono bloccate sia colonna che riga FORMULE 3D Sintassi: NomeFoglio!cella oppure NomeFoglio!intervallo oppure
Es. A5+Foglio2!SOMMA(A5:A10) (significato: somma il valore di A5 del foglio attuale con la somma dell'intervallo A5:A10 situato nel foglio2.
Simboli Excel
+ addizione - sottrazione
* moltiplicazione oppure nei criteri indica una parte del contenuto sconosciuta / divisione
^ potenza es. A2^A4 elevo il valore di A2 al valore di A4
; separatore tra parametri in una formula : posto tra 2 celle rappresenta un intervallo
? ha una funzione simile all'* nei criteri ma rappresenta UN SOLO carattere sconosciuto es. "A*"
significa parole che iniziano per A
es. "A?" significa che la parola inizia per A e dopo c'è UN SOLO carattere sconosciuto
! si pone tra il nome del foglio e le celle di quel foglio da utilizzare in un foglio differente.
es. Foglio1!$A$1:$A$6 voglio utilizzare questo intervallo nel foglio3
$ "blocca" colonna e/o riga, per rappresentare un riferimento misto/assoluto
& svolge la stessa funzione della CONCATENAZIONE es. =A2&A5 è equivalente a =CONCATENA(A2;A5)
FILTRI
Possono essere:
1) Intestazioni di colonna (nelle tabelle) in cui è possibile scegliere dei criteri di ordinamento/filtro 2) Criteri impostati in una tabella pivot per poter visualizzare i dati che soddisfano quei criteri
N.B. FILTRI AVANZATI: richiedono un'impostazione particolare del foglio di lavoro
AREA CRITERI: i criteri scritti in orizzontale si intendono in AND; in verticale si intendono in OR AREA DATI: i dati da filtrare
N.B. Le intestazioni dei criteri e delle colonne dei dati devono essere UGUALI.
TABELLE PIVOT
Sono utilizzate per realizzare report (solitamente in altri fogli di lavoro) per filtrare dati rispetto alle colonne/righe ed eventualmente calcolarne totali, medie, conteggi, deviazione std.
Si seleziona la tabella o intervallo su cui applicarla -> INSERISCI ->TABELLA PIVOT
FORMATTAZIONE CONDIZIONALE
Applicare colori/stili alle celle di una tabella in base ai valori che compaiono nelle stesse celle da formattare.
La regola si applica NELLE STESSE CELLE (stesso intervallo) interessato dalla modifica del colore/stile.
HOME->Formattazione condizionale->Regole evidenziazione celle
FORMATTAZIONE CONDIZIONALE AVANZATA
In questo caso l'intervallo da modificare nello stile/colore è diverso da quello/i in cui eseguire un test/condizione.
In questo caso bisogna accedere da
HOME->Formattazione condizionale->Nuova regola->Utilizza una formula...
Il risultato della formula inserita deve essere un VERO/FALSO Esempi:
=$A2>5 colorerà tutte le celle la cui riga nella colonna A è >5
=E($A2>5;$B2>6)colorerà tutte le celle la cui riga nella colonna A è >5 e nella colonna B è >6
=O vedi regola precedente
=CONTA.SE($A2:$C2;">1000")>=2 colorerà tutte le celle per cui l'intervallo segnalato soddisfa la condizione ">1000" almeno 2 volte
CONVALIDA DATI
Inserisce un set di dati possibili che l'utente può scrivere nelle celle interessate dalla convalida.
1) Scelta del tipo di dato e relazione (minore di, maggiore, tra, esclusi) 2) Scelta del tipo con impostazione di una formula
3) Elenco: in questo caso si imposta un elenco di valori ammissibili DATI->CONVALIDA DATI
COMPONENTI AGGIUNTIVI: RISOLUTORE
Su Excel sono disponibili funzionalità aggiuntive non sempre preinstallate FILE->OPZIONI->COMPONENTI AGGIUNTIVI
Risolutore: una volta impostato il foglio di lavoro con i dati e impostati i vincoli, trova una soluzione OTTIMA che soddisfi i vincoli.
In questo tipo di problema si vuole solitamente MASSIMIZZARE, MINIMIZZARE o EGUAGLIARE AD UN VALORE PARTICOLARE una soluzione.
CSV
Formato testo per veicolare dati, dove i dati sono scritti in forma "tabellare" e le colonne sono rappresentate da campi separati da un carattere (virgola, punto e virgola)
Ogni riga rappresenta un elemento o record.
Importazione (DATI->CARICA DATI ESTERNI ->TESTO/CSV) Esportazione (salvataggio come .csv)
RIMOZIONE DEI DUPLICATI
: rimuove le righe che presentano valori uguali tra loro in uno o più campi.DATI -> RIMUOVI DUPLICATI
PROTEZIONE DEL FOGLIO con PASSWORD
1) Protezione dell'intero foglio in ACCESSO: REVISIONE->PROTEGGI FOGLIO 2) Protezione cella/formula: HOME->FORMATO->BLOCCA CELLA Il punto 2) è possibile solo nel caso di protezione dell'intero foglio
N.B. in fogli di calcolo come Fogli Google la "logica" di protezione è differente, nel senso che si basa principalmente sul blocco della cella/intervallo.
