|
Ricerca valori
duplicati:
Si supponga di avere una lista di valori come quella seguente e di
averla denominata Elenco.
<Intervallo Elenco del Foglio di lavoro Duplicati>
L’utilizzo di una formula ordinaria come “=CONTA.SE(Elenco;”a”)” ci
informa se il valore “a” risulta presente più di una volta nella
lista. In tal caso, infatti, restituirà un valore superiore ad 1.
Possiamo sfruttare la formula nella formattazione condizionale.
Selezioniamo le celle A2:A15 e dal menù Formato clicchiamo su
Formattazione Condizionale. Nella finestra successiva scegliamo dalla
casella combinata “la formula è” e digitiamo nella casella di testo =CONTA.SE(Elenco;$A2)>1.
La possibilità di inserire nella formula un riferimento misto come $A2
ci risparmia l’alternativa di selezionare una alla volta le celle
dell’intervallo A2:A15 per inserire la formula della formattazione
condizionale “=CONTA.SE(Elenco;$A$2)>1” nella cella A2; “=CONTA.SE(Elenco;$A$3)>1”
nella cella A3 e così via. Il riferimento nella forma di sintassi $A2
( simbolo $ alla sola sinistra del riferimento di cella) indica ad
Excel che la formula deve essere copiata nelle celle dell’intervallo
selezionato avendo come riferimento assoluto la colonna A e come
riferimento relativo le riga 2. Se infatti dopo aver inserito nella
finestra anche i dati relativi al formato ( nel foglio di lavoro è
scelto il Motivo di colore rosso ) e chiuso la finestra con OK,
selezioniamo la cella A5 e apriamo ancora la finestra della
Formattazione Condizionale la formula del criterio che possiamo
leggere è “=CONTA.SE(Elenco;$A5)>1” . Se, invece, selezioniamo un
intervallo di più celle la formula farà riferimento alla prima di tali
celle; sempre, comunque, con riferimento di tipo misto.
Esemplificando, dopo aver selezionato l’intervallo A7:A10 e navigato
fino alla finestra della Formattazione Condizionale leggeremo “=CONTA.SE(Elenco;$A7)>1”
che per quanto esposto ci indica che il criterio della cella A8 perché
questa appaia rossa non è quello per il quale sia duplicato il valore
testuale “e” nella serie, bensì il valore “f”.
Sempre con modalità di formula ordinaria si può utilizzare la funzione
CONTA.SE nella cella B2 per esempio come ” =SE(CONTA.SE(Elenco;A2)>1;"duplicato";"")”
e trascinare la formula fino a B15 per ottenere informazioni simili al
primo metodo.
Fin qui solo formule ordinarie. Come, però, risolvere il problema di
ottenere con una formula in una sola cella l’informazione se uno
qualsiasi dei valori del nostro elenco risulta duplicato?
Utilizzeremo la formula CONTA.SE in versione matriciale!
Procediamo per Step. Nonostante un passaggio implichi una formula
matriciale multicella, in realtà tutti i passaggi possono essere
riassunti in un’unica formula restituita da una singola cella. In
sostanza i passaggi intermedi non sono obbligatori e si procede
attraverso essi solo per fini esemplificativi.
Se in una formula CONTA.SE,
all’argomento criterio ( che è il secondo argomento della formula)
assegniamo una matrice piuttosto che un valore singolo, l’operazione
di ricerca verrà reiterata per ogni valore della matrice e restituita
in una cella diversa per ogni valore ricercato. Esemplificando, se
vogliamo conoscere quante volte si ripetono i valori “a”, “y” e “p”
nel nostro elenco, avremo bisogno di 3 celle nelle quali inserire la
nostra formula matriciale. Selezioniamo per es. le celle E8:E10 e
scriviamo : =CONTA.SE(Elenco;{"a"\"y"\"p"}), quindi premiamo Ctrl +
Maiusc + Invio ottenendo {CONTA.SE(Elenco;{"a"\"y"\"p}. Otteniamo
nelle celle il risultato di “2”,”1”,”1”. Per verificare che tutto
funzioni cambiamo il valore della cella A4 da “b” a “p”. Notiamo che
il valore della cella E10, varia coerentemente da 1 a 2. Alcune
considerazioni sulla sintassi: abbiamo passato il secondo argomento
sotto forma di “matrice di costanti” e, pertanto, è stato necessario
racchiudere i nostri valori costanti tra parentesi graffe. Queste,
contrariamente a quelle più esterne, debbono essere direttamente
digitate ( eventualmente con l’ausilio dell’utilità inserisci simbolo
oppure con la scorciatoia da tastiera Alt+0123 e Alt+0125 ). Ancora,
la matrice di costanti passata è di tipo verticale e, pertanto i
valori devono essere separati non dal punto e virgola “;” ma bensì dal
back slash “\” (In USA si usa la virgola per la separazione degli
elementi orizzontali e il punto e virgola per quelli verticali). In
sostanza la matrice di costanti {"a"\"y"\"p} equivale ad un
riferimento del tipo C16:C18 posto che nella cella C16 vi sia il
valore “a”; in quella C17 il valore “y” e in quella C18 il valore “p”
(vedi formule nelle celle F8:F10). Se, invece, utilizzassimo la
sintassi {"a";"y";"p"}, come nelle celle J14:J16 avremmo i valori
“2”,”2”,”2”., in quanto Excel nelle formule delle 3 celle poste
verticalmente reitera la ricerca del valore “a”. In sostanza si passa
erroneamente un matrice di costanti orizzontale, equivalente ad un
riferimento di un gruppo di celle orizzontali ( vedi formula nelle
celle L14.L16 con riferimento alle celle N8:P8 ), invece che di una
matrice di costanti verticali.
Ora è facile comprendere
che se vogliamo effettuare una ricerca su tutti gli elementi del
nostro elenco avremo bisogno di tante celle quante sono quelle
dell’elenco. Selezioniamo allora le celle H2:H15 e scriviamo =CONTA.SE(Elenco;Elenco)
seguito da Ctrl + Maiusc + Invio ottenendo {=CONTA.SE(Elenco;Elenco)}.
Ogni cella conterrà il numero di volte che ogni valore si ripete nel
nostro elenco. In sostanza avremo il valore 1 per tutti i valori non
duplicati; valori > 1 per quelli duplicati ed eventualmente il valore
0 se la lista contiene celle vuote. È intuitivo che una funzione che
ci informa se vi è almeno un valore superiore a 1 in una serie ci
completerà il lavoro. Ci viene in aiuto la funzione MAX. Se, infatti,
nella cella I2 ( potremmo scegliere qualsiasi altra cella ) scriviamo
la formula ordinaria =MAX(H2:H15) otteniamo il valore 2 ( se vi fosse
una terza “a” nell’elenco ovviamente otterremmo 3 ). Una formula
condizionale che analizza la condizione I2>1 ci informerà
dell’esistenza di duplicati nella forma di testo o di formattazione
condizionale che sceglieremo ( cella J2 ). La “magia” delle formule
matriciali è testimoniata dal fatto che tutto quanto esposto è
ottenibile con una sola formula (cella E2 del nostro foglio) : {=SE(MAX(CONTA.SE(Elenco;Elenco))>1;"duplicati
presenti";"duplicati assenti")}
del tutto indipendentemente dalle formule precedentemente scritte che,
pertanto, si possono
cancellare.
Valori Unici o Non
Duplicati
Si supponga di avere un elenco di valori denominato Elenco1 ( celle
A2:A15) come il seguente
<Intervallo Elenco1 del Foglio di lavoro valori unici 1>
Potremmo essere interessati a conoscere il numero dei valori senza
duplicati, cioè quello che si ottiene contando una sola volta valori
che eventualmente ricorrono più di una volta, o, se interessa, il
numero dei valori unici che si ottiene eliminando dal conteggio
completamente i valori che si ripetono. L’utilizzo delle denominazioni
“valori unici” e “valori senza duplicati” con il significato esposto è
convenzionale; si possono, infatti trovare lavori che con “valori
unici” indicano quello che qui indichiamo con “valori senza
duplicati”. Una task complementare a quella che individua il numero
totale dei valori unici e non duplicati è quella della lista completa
dei valori non duplicati. Nel nostro caso, visivamente, notiamo che i
valori con completa esclusione di “a” ammontano a 11 e, viceversa
ammontano a 12 se “a” è contata una sola volta.
segue..................(leggere il file .doc) |