EXCEL avanzato
Certificazione Microsoft
Riferimenti assoluti e relativi
Filtri
Funzioni Conta e Cerca
1
Formule e riferimenti
• In una formula è consentito inserire un
riferimento ad un’altra cella
• Il modo più comune di riferimento è quello
relativo, fatto indicando la cella
semplicemente con lettera di colonna e
numero di riga
• in seguito alla modifica del contenuto di una
cella tutte le espressioni che fanno
riferimento a quella cella vengono
ricalcolate
2
Formule e riferimenti (2)
• Ad esempio se in B1 c’è la formula =A1*2
B
A
10
1
20
cambiando il valore di A1 in 15
automaticamente il valore di B1 diventa 30
B
A
1
15
30
3
Copiare formule
• Quando si crea una formula, i riferimenti alle
celle o agli intervalli si baseranno di solito sulla
posizione di questi rispetto alla cella contenente
la formula.
• Quando si copia una formula che utilizza
riferimenti relativi, i riferimenti della formula
incollata verranno modificati automaticamente
per adeguarsi alla nuova posizione della
formula.
• Se si desidera che i riferimenti non vengano
adeguati durante la copia di una formula in una
cella differente, utilizzare un riferimento assoluto4
Riferimenti relativi
• il riferimento è relativo alla posizione della
cella in cui lo si sta usando.
• se la stessa espressione viene spostata
da una cella ad un’altra cella, il riferimento
viene aggiornato relativamente alla
posizione della nuova cella.
5
Riferimenti relativi - esempio
• I riferimenti relativi sono molto utili per
costruire tabelle del tipo
A
1 prodotto
2 aaa
3 bbb
4 ccc
5 ddd
6 eee
B
C
D
prezzo
quantità spesa
100
10
200
15
300
20
50
5
100
15
6
Riferimenti assoluti
• il riferimento è indipendente dalla
posizione alla cella che lo usa.
• Per indicare un riferimento assoluto viene
anteposto il simbolo di dollaro ($) alle parti
del riferimento che si desidera lasciare
inalterate.
7
Riferimenti assoluti - esempio
• Ad esempio se in A2 c’è la formula
=$D$7+4 e se copiamo il contenuto di A2 in
B4, in questa cella sarà memorizzata la
stessa formula =$D$7+4
• Si noti invece che se la formula in A2 fosse
=$D$7*A3, allora copiandola in B4
diventerebbe =$D$7*B5
• Il primo riferimento ($D$7) è assoluto e
quindi rimane inalterato, mentre il secondo
riferimento è relativo (A3) è relativo ed è
soggetto alla trasformazione vista
8
precedentemente
Rif. relativi vs rif. assoluti
• I riferimenti relativi vengono adattati
automaticamente quando vengono copiati,
mentre i riferimenti assoluti rimangano tali
– quando si copia una formula, i riferimenti
di cella assoluti non verranno modificati,
mentre verranno modificati i riferimenti
di cella relativi
9
Passare dai rif. relativi ai rif.
assoluti
• Quando si porta il cursore su una cella che
contiene una formula
– sulla cella nel foglio comparirà il valore
attuale della formula
– sulla barra di immissione comparirà
l’espressione della formula
• Sulla barra della formula selezionare il
riferimento che si desidera modificare,
quindi premere F4.
10
Passare dai rif. relativi ai rif.
assoluti (2)
• Ogni volta che si preme F4, si passa a un
tipo di riferimento diverso:
– colonna assoluta e riga assoluta (ad es.
$C$1),
– colonna relativa e riga assoluta (C$1),
– colonna assoluta e riga relativa ($C1),
– colonna relativa e riga relativa (C1).
11
Riferimenti misti
• E’ possibile creare dei riferimenti misti del
tipo $D7 e D$7
• Questi riferimenti sono modificati solo in
parte quando viene copiata la formula
• Ad esempio se in A1 c’è la formula =$D7*2
e A1 viene copiata su B2, allora la formula
si muta in =$D8*2
• In pratica il $ blocca la riga o la colonna del
riferimento
12
Filtri
• E’ possibile associare un filtro ad uno o più
campi di un elenco
• Un filtro dà la possibilità di selezionare un
valore di un campo attraverso una finestra
e di escludere dall’elenco tutti quei record
che non presentano quel determinato
valore nel campo in questione
• Il filtro si attiva con Dati|Filtro|Filtro
automatico
13
Filtro - esempio
• Ad esempio cliccando su anno
14
Filtro – continuazione esempio
Scegliendo 1998 come anno si avrà
rappresentante anno
bianchi
1998
bianchi
1998
gialli
1998
rossi
1998
cliente
xyz
abc
efg
xyz
prodotto quantità
1
20
2
5
1
6
1
10
15
Filtro - note
• Si noti che le righe escluse sono solo non
visibili, ma comunque non sono state
cancellate
• Si possono scegliere valori su più campi o
attraverso l’opzione Filtro Avanzato fare
ricerche più complesse
• Per escludere il filtro su un campo
selezionare Tutto come valore
• Per eliminare il filtro ridare Dati|Filtro|Filtro
Automatico
16
Le somme nei filtri (SUBTOTALI)
• Applicato un filtro, SUBTOTALE(9;C3:C5)
genererà un subtotale delle celle C3:C5
tramite la funzione SOMMA
• SUBTOTALE ignorerà tutte le righe
nascoste originate da un elenco filtrato.
Questo risulta importante quando si
desidera calcolare solo il subtotale dei dati
visibili di un elenco precedentemente filtrato
17
La funzione SUBTOTALE
• SUBTOTALE(num_funzione;rif1;rif2;...)
– Num_funzione è un numero tra 1 e 11 che
specifica quale funzione utilizzare per il
calcolo dei subtotali all'interno di un elenco.
Ad es.
•
•
•
•
•
•
1  MEDIA
2  CONTA.NUMERI
3  CONTA.VALORI
4  MAX
5  MIN
9  SOMMA
18
CONTA.NUMERI
• Conta il numero di celle contenenti numeri
e i numeri presenti nell'elenco degli
argomenti
– Nel conteggio vengono inclusi argomenti
rappresentati da numeri, date oppure
rappresentazioni di numeri in formato testo
– Non verranno invece considerati gli
argomenti rappresentati da valori di errore o
da testo che non può essere convertito in
numeri
19
CONTA.VALORI
• Conta il numero di celle non vuote e di
valori presenti nell'elenco degli argomenti
– un valore può essere rappresentato da
qualsiasi tipo di informazione, incluso del
testo vuoto (""), ma non da celle vuote
20
CONTA.VUOTE
• Conta il numero di celle vuote in un
intervallo specificato
– Vengono contate anche le celle contenenti
formule che restituiscono "" (testo vuoto). Non
vengono invece contate le celle contenenti i
valori zero
21
CONTA.SE
• Conta il numero di celle in un intervallo
che soddisfano i criteri specificati
CONTA.SE(intervallo;criteri)
– Intervallo è l'intervallo di celle a partire dal
quale si desidera contare le celle.
– Criteri sono i criteri in forma di numeri,
espressioni o testo che determinano quali
celle verranno contate.
22
CERCA.VERT
CERCA.VERT(valore;tabella_matrice;indi
ce;intervallo)
– trova un valore nella colonna più a sinistra di
una tabella e lo restituisce nella colonna
indicata in corrispondenza della stessa riga
23
CERCA.VERT - parametri
• Valore: è il valore da ricercare nella prima
colonna della matrice
• Tabella_matrice: è la tabella di informazioni
nella quale vengono cercati i dati. Si può anche
utilizzare un riferimento a un intervallo
• Indice è il numero di colonna in tabella_matrice
dal quale deve essere restituito il valore corrisp.
• Intervallo è un valore logico che specifica il tipo
di ricerca: FALSO  corrispondenza esatta
• VERO (o omesso)  corrisp. approssimativa
24
Diapositiva sommario
•
•
•
•
•
•
•
•
Formule e riferimenti
Copiare formule
Riferimenti relativi
Riferimenti assoluti
Rif. relativi e rif. Assoluti a confronto
Riferimenti misti
Filtri
Le somme nei filtri (la funzione
SUBTOTALE)
• Le funzioni
25