CONTA.SE E SOMMA.SE CONTA.SE() Osservazioni

CONTA.SE E SOMMA.SE
CONTA.SE()
Conta, di tutte le celle di un intervallo, solo quelle che soddisfano un criterio
assegnato.
Sintassi
CONTA.SE(intervallo;criteri)
Intervallo è l'intervallo contenente le celle da contare.
Criteri sono i criteri in forma di numeri, espressioni, testo o riferimenti che
determinano quali celle verranno contate. Ad esempio, criteri può essere
espresso come 32, "32", ">32", "mele", D15.
Osservazioni
Purtroppo, contrariamente a quanto avviene per le funzioni logiche, le modalità
con cui devono essere specificati i criteri non si presentano per nulla lineari.
Suggerisco di attenersi ad una regola valida per tutte la situazioni (che ho
trovato dopo penosi tentativi), salvo poi utilizzare le solite "scorciatoie" nei casi
più frequenti di criteri che prevedono solo l'uguaglianza con dati o riferimenti.
La regola sarebbe quella di "costruire" la stringa del criterio concatenando
l'operatore di confronto (racchiuso tra virgolette doppie) con il valore o il
riferimento contenente il valore da confrontare.
E' macchinoso, ma funziona, anche se non lo trovate scritto da nessuna parte.
Esempi:
"="&100, ">="&"mele", "="&D15, "<"&"100+50", ecc.
Prima eccezione: Nel caso di confronto con valori costanti, la stringa può
essere già completa, contenere cioè sia l'operatore che la costante. 1
Esempi:
"=100", ">100", ">=100", "=mele", "<100+50", ecc.
Seconda eccezione. Nel caso di uguaglianza può essere omesso il simbolo = e,
nel caso di un numero, possono essere omesse le virgolette.
Esempi:
100, "mele".
Terza eccezione. Nel caso di UGUAGLIANZA con DATI CONTENUTI IN CELLE di cui
si indica il riferimento, DEVONO essere omessi sia il simbolo = sia le
virgolette.
Esempio:
D17
1
Questo non vale se si fa riferimento ad una cella. Ad esempio non si può usare la forma ">=D15", in quanto tutto ciò
che è racchiuso tra virgolette viene interpretato come testo e non come riferimento e quindi, nel caso specifico,
verrebbero conteggiati i testi che seguono in ordine alfabetico il testo D15, e cioè -semplificando- i testi che cominciano
per D16, D17, E…, F…, G…, ecc.!
Si è cercato di riprodurre tutte le varianti nella
tabella sottostante.
Nelle colonne sono riportate le diverse tipologie di confronto.
Nelle righe sono riportate le diverse tipologie di dati per il confronto.
I testi in grassetto indicano le scritture consigliate, quelli in caratteri normali le
alternative a scelta.
Numeri
Testi
Riferime
nti
=
100
"="&100, "=100",
"100"
"Testo"
"="& "Testo",
"=Testo"
D15
"="&D15
>
">100"
">"&100
<
"<100"
"<"&100
>=
">=100"
">="&100
<=
"<=100"
"<="&100
">Testo" "<Testo" ">=Testo "<=Testo
">"&"Testo "<"&"Testo
"
"
"
"
">="&"Test "<="&"Test
o"
o"
">"&D15
"<"&D15
">="&D1
5
"<="&D1
5
Esempi
Si supponga che l'intervallo A3:A6 contenga rispettivamente la voci "mele",
"arance", "pesche", "mele".
CONTA.SE(A3:A6;"mele") è uguale a 2
Si supponga che l'intervallo B3:B6 contenga rispettivamente i valori 32, 54,
75, 54 e che la cella D15 contenga il valore 50.
CONTA.SE(B3:B6;">=50") è uguale a 3; si ottiene lo stesso risultato con
l'espressione
CONTA.SE(B3:B6;">="&D15)
CONTA.SE(B3:B6;54) è uguale a 2
SOMMA.SE()
Somma, di tutte le celle di un intervallo, solo quelle che soddisfano un criterio
assegnato.
Sintassi
SOMMA.SE(intervallo;criteri;int_somma)
Intervallo è l'intervallo di celle su cui viene applicato il test indicato da criteri.
Criteri sono i criteri in forma di numeri, espressioni, testo o riferimenti che
determinano quali celle verranno sommate. Ad esempio, criteri può essere
espresso come 32, "32", ">32", "mele", D15 (v. Osservazioni della funzione
CONTA.SE()).
Int_somma è l'intervallo che contiene i dati da sommare.
- Le celle in int_somma vengono sommate solo se le celle corrispondenti in
intervallo soddisfano i criteri.
- Se int_somma è omesso, vengono sommate le celle in intervallo.
Esempio
Si supponga che l'intervallo A2:A5 contenga rispettivamente i seguenti valori
patrimoniali relativi a quattro abitazioni: L. 100.000.000, L. 200.000.000, L.
300.000.000, L. 400.000.000. L'intervallo B2:B5 contiene le seguenti
commissioni sulle vendite su ciascuno dei corrispondenti valori patrimoniali: L.
7.000.000, L. 14.000.000, L. 21.000.000, L. 28.000.000. In questo caso
SOMMA.SE(A2:A5;">160000000";B2:B5) è uguale a L. 63.000.000
A
B
1 Valore vendita Commission
e
2
100000000
7000000
3
200000000
14000000
4
300000000
21000000
5
6
400000000
C
D
Totale commissioni per vendite
>160000000:
E
63000000
28000000
Osservazioni
Per le osservazioni in merito alle modalità di scrittura dei criteri, si rimanda alla
funzione CONTA.SE().
Media, mediana e moda
MEDIA()
Restituisce la media aritmetica degli argomenti.
Sintassi
MEDIA(num1; num2; ...)
Num1; num2;... sono da 1 a 30 argomenti numerici di cui si desidera calcolare
la media.
- Gli argomenti devono essere numeri o nomi, matrici oppure riferimenti che
contengono numeri.
- Se una matrice o un riferimento contiene testo, valori logici o celle vuote,
tali valori vengono ignorati. Le celle contenenti il valore zero vengono
invece incluse nel calcolo.
Osservazioni
Quando si utilizza la funzione MEDIA(), fare molta attenzione alla
differenza esistente tra celle vuote e celle che contengono il valore
zero, soprattutto nel caso in cui si sia scelto di non visualizzare gli zeri per
motivi estetici (scelta vivamente sconsigliata ai neofiti, perché può generare
confusione).
E' sempre meglio verificare il comportamento della funzione in
presenza di celle vuote o contenenti testi.
Si osservi che il risultato ottenuto con la funzione MEDIA(), può essere
ottenuto anche con l'utilizzo combinato delle funzioni SOMMA() e
CONTA.NUMERI():
MEDIA(A1:A5)
è uguale a
SOMMA(A1:A5)/CONTA.NUMERI(A1:A5)
Esempi
Se l'intervallo A1:A5 viene denominato Punteggi e contiene i numeri 10, 7, 9,
27 e 2 (somma degli elementi: 55, numero degli elementi: 5,
media=somma/numero elem.), allora
MEDIA(A1:A5) è uguale a 11
MEDIA(Punteggi) è uguale a 11 (come sopra)
MEDIA(A1:A5;5) è uguale a 10 (perché fa la media anche con il numero 5)
Se l'intervallo A1:A5 contiene dei testi e dei numeri, quali "Totali", 10, 7, 13 e
2 (la somma degli elementi è uguale a32, il numero degli elementi numerici è
uguale a 4), allora
MEDIA(A1:A5) è uguale a 8
Se l'intervallo A1:A5 contiene solo celle vuote (somma=0, num.elementi=0),
allora
MEDIA(A1:A5) dà il messaggio di errore #DIV/0!
Se tutte le celle dell'intervallo A1:A5 contengono il valore 0 (somma=0,
num.elementi=5), allora
MEDIA(A1:A5) è uguale a 0
MEDIANA()
Restituisce la mediana
2
dei numeri specificati.
Sintassi
MEDIANA(num1;num2;...)
Num1;num2;... sono da 1 a 30 argomenti di cui si desidera calcolare la
mediana.
- Gli argomenti devono essere numeri oppure riferimenti, nomi o matrici che
contengono numeri.
- L'utilizzo tipico è appicare la funzione MEDIANA() ai numeri di un intervallo.
- Vengono esaminati tutti i numeri contenuti in ogni argomento, riferimento o
matrice.
- Le celle vuote o contenenti testo o valori logici vengono ignorate.
- Le celle contenenti il valore zero vengono invece incluse nel calcolo.
Esempi
MEDIANA(1; 2; 3; 4; 5) è uguale a 3
MEDIANA(1; 2; 3; 4; 5; 6) è uguale a 3,5, vale a dire la media di 3 e 4
MODA()
Restituisce il valore più ricorrente, o ripetitivo, di una matrice o di un intervallo
di dati. Analogamente a MEDIANA, la funzione MODA è una misura relativa alla
posizione dei valori.
Sintassi
MODA(num1;num2;...)
Num1;num2;... sono da 1 a 30 argomenti di cui si desidera calcolare la moda.
- Gli argomenti devono essere numeri oppure riferimenti, nomi o matrici che
contengono numeri.
- L'utilizzo tipico è applicare la funzione MEDIANA() ai numeri di un intervallo.
2
La mediana è il numero che occupa la posizione centrale di un insieme di numeri, vale a dire che una metà dei numeri
ha un valore superiore rispetto alla mediana, mentre l'altra metà ha un valore inferiore. Se la serie di numeri è dispari, la
mediana è proprio il numero centrale, altrimenti è la media tra i due numeri centrali.
-
Vengono esaminati tutti i numeri contenuti in ogni argomento, riferimento o
matrice.
Le celle vuote o contenenti testo o valori logici o vengono ignorate.
Le celle contenenti il valore zero vengono invece incluse nel calcolo.
Se l'insieme dei dati non contiene valori ripetuti, MODA() restituirà il valore
di errore #N/D.
Osservazioni
La funzione MODA() non si adatta completamente alle esigenze della teoria
statistica, in quanto:
- non è in grado di individuare le serie plurimodali 3 e indica sempre un unico
risultato. Non solo, il numero restituito è quello che compare per primo nella
sequenza, tanto che basta cambiarne l'ordine per avere un altro risultato;
- non è in grado di segnalare il caso anomalo in cui tutti i numeri si
presentino con la stessa frequenza.
Per un approfondimento di tali problematiche si veda il file di esercizio
Medie.xls.
Esempi
MODA(5,6;4;4;3;2;4) è uguale a 4
MODA(2,6;4;4;5;5;5;4) è uguale a 4, perché compare prima (e invece la
moda è 4 e 5)
MODA(5,6;4;4;5;3;5;4) è uguale a 5, perché compare prima (e invece la
moda è 4 e 5)
MODA(5,6;4;4;5;6) è uguale a 5, perché compare prima (e invece la moda
non esiste)
FREQUENZA()
Calcola la frequenza di occorrenza di una serie di numeri riferita a dei limiti di
classe specificati.
Sintassi
FREQUENZA(matrice_dati;matrice_classi)
Matrice_dati è un intervallo verticale di celle contenente l'insieme di valori di
cui si desidera calcolare la frequenza. Se matrice_dati non contiene alcun
valore, FREQUENZA() restituirà una matrice di zeri.
Matrice_classi è un intervallo verticale di celle contenenti i limiti superiori delle
classi in cui si desidera raggruppare i valori contenuti in matrice_dati. Se
matrice_classi non contiene alcun valore, FREQUENZA() restituirà il numero
degli elementi contenuti in matrice_dati.
3
Si parla di serie plurimodale quando ci sono più elementi della serie che ricorrono con una frequenza pari alla
massima.
Osservazioni
FREQUENZA() viene immessa come formula matrice 4 dopo aver selezionato un
intervallo verticale di celle nel quale dovrà apparire il risultato (matrice dei
risultati).
Il numero di celle contenute nella matrice dei risultati deve essere maggiore di
una unità rispetto al numero di celle contenute in matrice_classi. Le eventuali
celle in più conterranno l'errore #ND!.
FREQUENZA() ignora le celle vuote e i testi.
Esempio
Si supponga che nell'intervallo di celle A1:A9 di un foglio di lavoro siano
elencati i seguenti punteggi: 79, 85, 78, 85, 83, 81, 95, 88 e 97. Questo
intervallo costituisce la matrice_dati.
Si supponga di voler raggruppare questi punteggi nelle classi seguenti: 0-70;
71-80; 81-90, 91-oltre. Bene, i limiti superiori di tali classi (cioè i numeri 70,
80, 90) dovranno essere inseriti in un altro intervallo verticale (ad esempio
C4:C6), che viene denominato matrice_classi.
La formula dell'esempio sottostante va inserita come formula matrice in un
altro intervallo verticale, ad es. nell'intervallo F4:F7 (matrice dei risultati), più
lungo di una cella rispetto alla matrice_classi.
{FREQUENZA(A1:A9;C4:C6) } è uguale a {0;2;5;2}
-
-
4
Per le classi intermedie il computo della frequenza viene fatto dal limite
precedente escluso al limite successivo incluso (in questo esempio >70 ÷
<=80).
Per la prima classe il conteggio viene fatto da
- infinito al primo limite
incluso (nell'es. <=30).
Per l'ultima classe (cella in più rispetto a matrice_classi) il conteggio va
dall'ultimo limite escluso all'infinito (nell'es. >70).
Per immettere una formula matrice, bisogna:
1. selezionare l'intervallo nel quale la formula andrà immessa;
2. scrivere la formula;
3. immettere la formula, premendo la combinazione di tasti Ctrl+Maiusc+Invio.
La formula comparirà inserita in una coppia di parentesi graffe.
FUNZIONI DI RICERCA E RIFERIMENTO
Vengono utilizzate per trovare valori all'interno di elenchi o tabelle.
5
CERCA.VERT()
Cerca un valore nella prima colonna a sinistra di una tabella e, una volta
individuata la riga in cui tale valore si trova, restituisce il valore che si trova in
una colonna specificata. Utilizzare la funzione CERCA.VERT() quando i dati
sono organizzati in una tabella verticale, altrimenti utilizzare la funzione
CERCA.ORIZZ().
Sintassi
CERCA.VERT(valore;tabella_matrice;indice;intervallo
)6
Valore è il valore da ricercare nella prima colonna della matrice. 7
Valore può essere una costante (testo, nunero o valore logico) o un
riferimento.
Tabella_matrice è la tabella di informazioni nella quale vengono cercati i dati.
Per definirla, utilizzare un riferimento ad un intervallo oppure un nome di
intervallo.
- I valori nella prima colonna di tabella_matrice devono essere disposti in
ordine crescente: ...; -2; -1; 0; 1; 2; ...; A-Z; FALSO; VERO. In caso
contrario, CERCA.VERT potrebbe non restituire il valore corretto. 8
- L'intervallo tabella_matrice deve comprendere solo l'area dati e non
l'eventuale prima riga contenente le etichette o intestazioni di colonna.
- La funzione non rileva le maiuscole.
Indice è il numero di colonna nella tabella_matrice dalla quale deve essere
restituito il valore corrispondente.
5
Per elenco si intende una tabella o una serie di righe del foglio di lavoro contenenti dati correlati, come un elenco di
indirizzi, un listino prezzi, ecc.. È possibile considerare l'elenco come un database, in cui le righe rappresentano i record
e le colonne i campi. In genere la prima riga dell'elenco contiene le etichette delle colonne.
6
Tutti i nomi assegnati agli argomenti di questa funzione non brillano per chiarezza (a parte valore), credo sarebbero
stati più comprensibili valore, tabella, colonna, metodo, ma ovviamente si è preferito lasciare i nomi ufficiali
7
Si insiste sul fatto che valore può essere inteso come una chiave di ricerca, non è quasi mai il vero dato cercato (si
pensi di cercare il valore Cognome in un elenco telefonico per trovare il Numero) e come tale deve trovarsi nella
prima colonna della tabella, altrimenti CERCA.VERT() non può funzionare. Se ciò non fosse verificato, bisogna
arrangiarsi per riportare nella prima colonna i dati sui quali eseguire la ricerca di valore.
8
In realtà questo non è indispensabile se il quarto argomento (intervallo) è FALSO o 0, ma anche in questo caso ci sono
situazioni in cui i risultati sono di difficile interpretazione, se non apparentemente imprevedibili, per cui si raccomanda
vivamente di verificare che i dati della prima colonna siano sempre ordinati in ordine crescente.
Per ordinare i dati di una tabella:
1. selezionare tutta la tabella
2. selezionare da menu le voci Dati Ordina
3. confermare l'ordinamento crescente in funzione della prima colonna.
Se indice è uguale a 1 la funzione restituisce il valore nella prima colonna di
tabella_matrice, se indice è uguale a 2 restituisce il valore nella seconda
colonna di tabella_matrice e così via.
- Se indice è minore di 1, CERCA.VERT() restituirà il valore di errore
#VALORE!.
- Se indice è maggiore del numero di colonne della tabella_matrice,
CERCA.VERT() restituirà il valore di errore #RIF!.
Intervallo è un valore logico che specifica il tipo di ricerca che CERCA.VERT()
dovrà eseguire.
- Se intervallo è VERO (o 1) o è omesso, CERCA.VERT() cercherà una
corrispondenza approssimativa, nel senso che la ricerca si fermerà
sull'ultimo valore precedente quello cercato (definito da valore). In questo
caso quindi viene restituito sempre e comunque un dato.
- Se intervallo è FALSO (o 0), CERCA.VERT() cercherà una corrispondenza
esatta. Qualora non venisse trovata alcuna corrispondenza, verrà restituito
il valore di errore #N/D.
- In tutti e due i casi, se il valore cercato è minore del primo valore della
prima colonna di tabella_matrice, verrà restituito il valore di errore #N/D.
-
Esempi
Data la seguente tabella di dati, che per comodità viene chiamata Listino (in
azzurro):
1
2
3
4
5
6
A
Codice
10
20
30
B
Descrizion
e
Mele
Pere
Carote
C
Prezzo
D
E
F
G
1000
2000
3000
CERCA.VERT(10;Listino;2;VERO) è uguale a "Mele" (anche se la ricerca viene
fatta con intervallo VERO, il valore
cercato (10) esiste e quindi viene
usato quello).
CERCA.VERT(10;Listino;2,1) è uguale a "Mele" (Al posto di VERO si può
scrivere 1).
CERCA.VERT(10;Listino;2) è uguale a "Mele" (VERO si può omettere).
CERCA.VERT(10;Listino;2;FALSO) è uguale a "Mele" (trova una corrispondenza
esatta).
CERCA.VERT(10;Listino;2;0) è uguale a "Mele" (al posto di FALSO si può
scrivere 0).
CERCA.VERT(25;Listino;3;1) è uguale a 2000 (si ferma su 20, l'ultimo valore
che precede 25).
CERCA.VERT(25;Listino;3;0) è uguale a #N/D (non trova la corrispondenza
con intervallo FALSO).
CERCA.VERT(20;Listino;1;0) è uguale a 20 (restituisce il dato della colonna 1).
CERCA.VERT(20;Listino;5;1) è uguale a #RIF! (non esiste la colonna 5).
FUNZIONI DI DATABASE
Le funzioni di database (chiamate anche DB.funzione) sono utilizzate per
l'analisi dei dati memorizzati in elenchi o database di Excel. Una di esse in
particolare offre una valida alternativa alle funzioni di ricerca già esaminate
(CERCA.VERT() e INDICE()).
DB.VALORI()
Estrae da una colonna in un elenco o database un singolo valore
le condizioni specificate.
9
che soddisfa
Sintassi
DB.VALORI(database;campo;criteri)
Database è l'intervallo di celle che costituiscono l'elenco o database.
- Un database di Microsoft Excel è un elenco di dati correlati in cui le righe di
informazioni costituiscono i record e le colonne di dati costituiscono i campi.
- La prima riga del database deve contenere le etichette relative a ciascuna
colonna e deve essere compresa nell'eventuale nome di intervallo assegnato
al database.
- Non ci devono essere righe o colonne vuote all'interno del database.
- Non ci devono essere altri dati contigui all'intervallo del database (in altre
parole ci devono essere almeno una riga ed una colonna vuote attorno al
database).
Campo indica da quale colonna dell'elenco viene
estratto il dato cercato.
-
I campi del database sono colonne di dati omogenei con un'etichetta che le
contraddistingue indicata nella prima riga.
Campo può essere specificato come testo, utilizzando l'etichetta di colonna
racchiusa tra virgolette doppie, (ad es. "Età" o "Prezzo"), oppure come un
numero che rappresenta la posizione della colonna all'interno dell'elenco (1
per la prima colonna, 2 per la seconda ecc.).
Criteri è un intervallo di celle contenente le
condizioni in base alle quali la funzione ricerca il
dato richiesto.
-
9
È possibile utilizzare come argomento di criteri un intervallo qualsiasi,
purché includa almeno una delle etichette di colonna del database e almeno
una cella immediatamente sotto l'etichetta di colonna in cui specificare la
condizione.
Si richiama l'attenzione sul fatto che la funzione DB.VALORI, pur utilizzando dei criteri per l'estrazione, va alla
ricerca di un dato singolo. Per l'estrazione di più record che soddisfano certe condizioni, si veda la parte dedicata ai
filtri.
Il riferimento di criteri può essere immesso sia come intervallo di celle (es.
A1:F2), sia con un nome rappresentante l'intervallo (es. "Criteri").
È possibile teoricamente collocare l'intervallo di criteri in qualsiasi posizione
sul foglio di lavoro, ma, onde evitare sovrapposizioni o interferenze
indesiderate, si consiglia vivamente di posizionarlo in un foglio distinto.
Assicurarsi che l'intervallo dei criteri non si sovrapponga all'elenco.
-
-
Osservazioni
Se nessun record soddisfa i criteri, DB.VALORI restituirà il valore di errore
#VALORE!.
Se più record soddisfano i criteri, DB.VALORI restituirà il valore di errore
#NUM!.
Si noti che questa funzione funziona come la funzione CERCA.VERT() in
modalità ricerca esatta, con la grossa differenza che la ricerca può essere
estesa a qualunque elemento di qualunque colonna e non solo della prima.
-
Esempi
L'intervallo A1:D6 di un foglio di calcolo contiene un elenco di prodotti, cui
viene assegnato il nome Listino (in azzurro). Tale elenco può essere
considerato un database, in quanto:
- Le sue righe e colonne possono essere interpretate come record e campi di
un database;
- Ogni colonna ha nella prima riga un'etichetta che funge da nome del
campo;
- In ogni campo i dati sono omogenei, cioè della stessa natura;
- Non ci sono né righe né colonne vuote all'interno dell'intervallo;
- Attorno all'intervallo ci sono una riga (7) ed una colonna (E) completamente
vuote. 10
L'intervallo F3:F4 viene invece chiamato intervallo dei criteri e ad esso
assegnamo il nome Criterio (in verde). Si ricordi che, per essere un intervallo
di criteri, bisogna che:
- La prima cella contenga esattamente uno dei nomi dei campi (per sicurezza
conviene copiarlo);
- La cella immediatamente sottostante contenga il valore da cercare nel
campo. 11
1
2
3
4
5
6
10
A
Codice
100
200
300
600
400
B
Prodotto
Viti
Bulloni
Dadi
Chiodi
Tasselli
C
Prezzo
20
15
10
14
10
D
Scorta
500
1000
12000
8800
4000
E
F
G
Prezzo
15
Nella pratica è fortemente consigliato che il database stia su un foglio a parte, ad esso dedicato, in modo da evitare
qualunque interferenza con gli altri dati.
11
Si possono realizzare anche criteri più complessi, però di difficile utilizzazione in questo contesto Per maggiori
informazioni sui database e sui criteri, si veda la parte dedicata ai filtri.
7
8
Con i valori dell'esempio,
DB.VALORI(Listino;"Scorta";Criterio) restituisce 1000.
Si noti la comodità offerta dall'uso dei nomi, rispetto alla formula seguente:
DB.VALORI(A1:D6;4;F3:F4), che pure restituisce 1000.
Si noti ancora che i nomi degli intervalli vanno senza virgolette doppie
(essendo in fondo dei riferimenti), mentre quello del campo sì (può essere
usato al posto del numero di colonna).
Se nella cella F4 viene inserito il valore 18 (non presente nel campo Prezzo)
DB.VALORI(Listino;"Scorta";Criterio) restituisce l'errore #VALORE!
Se nella cella F4 viene inserito il valore 10 (presente più volte nel campo
Prezzo)
DB.VALORI(Listino;"Scorta";Criterio) restituisce l'errore #NUM!