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!