Usare Conta.se (CountIf) per ricerca, confronto, inserimento o estrazione dati. - pagina vista: volte

Spesso ci arrovelliamo nel cercare soluzioni con cicli a volte anche complessi, quando dobbiamo trovare dati nelle nostre tabelle o database, come ricerche doppioni, oppure inserimento dati con controllo che il dato da immettere non sia già presente, o ancora per modificare, eliminare dati se presenti, ecc. ecc., mentre invece, usando un pò il cervello (io per primo) potremmo risolvere con soluzioni semplici che Excel ci mette già a disposizione usando le sue Funzioni.

E' il caso della funzione CONTA.SE . La funzione restituisce un valore numerico indicante quante volte un'occorrenza è presente in un determinato intervallo. Il bello è che la restituzione del valore è velocissima, anche se l'elenco (intervallo) in cui cercare è molto lungo o ampio. Usando invece dei cicli che scorrano tutte le celle dell'intervallo alla ricerca di un dato, i tempi di risposta possono richiedere anche molto tempo.

Il tema di questo articolo mi è stato fornito da un lettore, il quale usando una routine, mi ha chiesto come mai a lui non funzionasse, ed è in queste istruzioni che ho notato l'uso del CONTA.SE per ricerca di dati; l'idea l'avevo già proposta in un mio precedente articolo sul primo sito (Evitare Ripetizioni (nell'inserimento di dati in una tabella)) e la ripresento qui, riscritta modificata ed ampliata.

  • Se CONTA.SE restituisce un valore maggiore di zero, vuol dire che il dato cercato è presente nell'intervallo di ricerca, altrimenti non è presente.

Diventa quindi chiaro che, almeno per certe esigenze, risulta molto più facile impostare istruzioni che si basino sulla condizione del valore ottenuto in risposta alla funzione usata. Potremo inserire dati in una tabella, controllando che non esistano già duplicati del dato da immettere, oppure potremo decidere di esportare in un'altra colonna, i dati (assenti o presenti) confrontando due elenchi di due colonne distinte, o ancora estrarre dati correlati al dato cercato se presenti in un'altra colonna, o ancora.....

Facciamo quindi qualche esempio, precisando che la funzione CONTA.SE (CountIf) richiede due argomenti : CONTA.SE(Intervallo dove cercare, cosa cercare) e che in vba l'istruzione diventa : Application.CountIf(Intervallo dove cercare, cosa cercare). Ricordo che la funzione CountIf restituisce un valore corrispondente al numero di occorrenze trovate, e NON la loro posizione all'interno dell'intervallo.

Premessa : per curiosità ho provato ad impostare un elenco di 65536 nomi, nella colonna A, inserendo il nome da cercare nella cella A65536, l'ultima; poi ho usato due routine: la prima sfruttando la funzione CONTA.SE, la seconda svolgendo un ciclo For Each Next facendo cercare il nome nell'intervallo A1:A65536. Nel primo caso la risposta (un messaggio) è stata immediata, istantanea, nel secondo il ciclo ha impiegato poco più di 1 secondo. Tempo comunque più che accettabile, ma dimostra che l'uso della funzione risulterebbe vantaggioso in termini di risposta, specie con elenchi molto lunghi o estesi, o con computer poco potenti.

Primo esempio: desideriamo inserire un nominativo in un elenco, e desideriamo controllare che non esista già; se esiste, avvisiamo con un messaggio, altrimenti lo aggiungiamo alla fine dell'elenco esistente. (Potremo poi aggiungere una routine per l'ordinamento alfabetico dei nominativi).

Sub InserisciDato()
nome = InputBox("SCRIVI NOME DA INSERIRE")
If nome = "" Then Exit Sub
Set Intervallo = Range("A:A")  'con Intervallo impostiamo tutta la colonna A come area di ricerca
If Application.CountIf(Intervallo, nome) = 0 Then  ' se CountIf restituisce zero (quindi "nome" non è presente), allora
cellavuota = Range("A1").End(xlDown).Row + 1  'cerchiamo l'ultima cella occupata nell'elenco, +1 sarà quella vuota
Cells(cellavuota, 1) = nome     'e scriviamo "nome" nella cella
'Cells(cellavuota, 1).Select  'eventualmente la selezioniamo
Else
MsgBox "Nominativo già Presente"  'altrimenti avvisiamo che "nome" è già presente
End If
End Sub

Negli articoli sui siti ennius esistono molti esempi di ricerca doppioni ed inserimento dati, ma sicuramente, se confrontata con le istruzioni usate, questa routine sopra è decisamente più breve come compilazione istruzioni. Sempre nella routine sopra, ho usato la funzione End(xlDown) per trovare l'ultima cella occupata: esistono casi in cui questa funzione restituirebbe una riga errata rispetto all'ultima cella occupata dell'intervallo, come in elenchi lunghi ma con presenza di celle vuote nell'intervallo. la funzione End(xlDown) si fermerebbe infatti alla prima cella vuota che troverebbe, trascurando il resto dell'elenco. Andrebbe meglio se usassimo la funzione End con la costante xlUp (verso l'alto) partendo dal basso (dall'ultima cella, la 65536) per ottenere quante righe sono occupate (in questo caso vengono contate anche le eventuali celle vuote) e sapremmo quindi quale è l'ultima riga occupata. Comunque una variante in questo senso potrebbe essere:

  • numerorighe = Range("A65536").End(xlUp).Row
    Cells(numerorighe + 1, 1) = nome

Secondo esempio: desideriamo confrontare due elenchi di dati che potranno essere in due colonne sullo stesso foglio, oppure in colonne su fogli diversi, oppure su due cartelle diverse entrambe aperte; la necessità sarà: confrontare dati NON presenti in una delle due colonne, e vogliamo estrarre, componendo in una terza colonna, i dati discordanti. Dovremo quindi affidarci ad un ciclo For Next o For Each Next che scorra cella dopo cella di una delle due colonne, cercando con CountIf nell'altra colonna la presenza o meno del valore letto in quel momento. E' implicito che potremo usare lo stesso concetto anche se volessimo verificare che in entrambe le colonne esistano gli stessi dati, anche se non nello stesso ordine alfabetico o di grandezza.

Simuliamo quindi di avere due elenchi nelle colonne A e B dello stesso foglio: scorriamo con un ciclo le celle della colonna B, cercando i nominativi che NON sono presenti nella colonna A; con i dati letti in B non presenti in A, creiamo un elenco nella colonna C :

Sub ConfrontaAeB()
Dim IntervalloDoveCercare As Range
Dim IntervalloRicerca As Range
Dim RigaDestino As Long
Set IntervalloDoveCercare = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))  'impostiamo gli intervalli
Set IntervalloRicerca = Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp))
RigaDestino = 1    'impostiamo il numero della riga iniziale della colonna dove estrarre i dati non presenti
For Each cell In IntervalloRicerca 
If Application.CountIf(IntervalloDoveCercare, cell.Value) = 0 Then  'se il dato non è presente (CountIf = 0)
Cells(RigaDestino, 3).Value = cell.Value      'scriviamo il dato nella colonna C (la 3)
RigaDestino = RigaDestino + 1    'incrementiamo il numero della riga dove scrivere, colonna C.
End If
Next
End Sub


La sub sopra gira benissimo, solo che può esistere un problema di fondo legato alla presenza di dati doppioni nell'intervallo ricerca, nell'esempio la colonna B (non nell'intervallo dove cercare, colonna A). Se in B infatti esistono due o più valori uguali, nella colonna C verranno estratti tanti dati quanti sono i dati doppioni letti dal ciclo: Nel caso in cui si voglia un estrazione dati univoca, dovremo eseguire un controllo anche nella colonna C: se il dato letto in B da inviare a C, è già presente in C, evitiamo di ripeterlo. Useremo quindi un CountIf anche sulla colonna C , impostando la condizione che se CountIf restituisce un valore maggiore di 0, saltiamo alla riga successiva senza scrivere il dato. Vediamo questa soluzione:

Sub ConfrontaAeBeC()
Dim IntervalloDoveCercare As Range
Dim IntervalloRicerca As Range
Dim ColonnaEstrazione As Range  'dichiariamo una variabile anche per l'intervallo destinazione estratti
Dim RigaDestino As Long
Set IntervalloDoveCercare = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
Set IntervalloRicerca = Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp))
RigaDestino = 1
For Each cell In IntervalloRicerca
If Application.CountIf(IntervalloDoveCercare, cell.Value) = 0 Then
Set ColonnaEstrazione = Range(Cells(1, 3), Cells(Rows.Count, 3).End(xlUp))  'impostiamo l'intervallo di destinazione
If Application.CountIf(ColonnaEstrazione, cell.Value) > 0 Then  'ripetiamo il CountIf su questo intervallo, e se maggiore di 0
GoTo 10   'saltiamo alla riga 10 senza scrivere il valore
Else
Cells(RigaDestino, 3).Value = cell.Value
End If
RigaDestino = RigaDestino + 1
End If
10:
Next
End Sub


Terzo esempio: mi sembra superfluo aggiungere che le esigenze degli utenti sono varie e troppo personali, a volte, per essere esemplificate tutte; di sicuro però la base a cui fare riferimento sul tema dell'articolo: è confrontare dati tra due database (o elenchi, o tabelle) diversi. Che si confronti date, orari, nomi o valori poco cambia, ma dovremmo prima pensare e ciò che vorremo ottenere una volta che sia stata riscontrata assenza o presenza di un dato tra le due tabelle, impostando le giuste condizioni da verificare (If...Then) e le colonne su cui cui eseguire il ciclo. Simuliamo quindi due elenchi a più colonne, dove confronteremo dati simili, e in caso di assenza del dato letto nel corrispondente intervallo dove cercare, estrarre il dato più un valore correlato al dato estratto, una situazione del genere, dove vogliamo conoscere, confrontando due mesi di fatturazione, i nuovi clienti ai quali sono state emesse fatture, e vogliamo il totale degli importi dei nuovi fatturati.

  A B   D E   G H
1  Nominativi  Importi    Nominativi  Importi    Nuovi  Importi
2  Bianchi 782,00    Rossi 700,50    Cesari 789,00
3  Rossi 500,00    Bianchi 458,50    Filippi 500,00
4  Verdi 1512,12    Cesari 789,00    Giudici 888,88
5  Neri 655,10    Neri 632,40      
6  Orange 444,78    Filippi 500,00      
7  Luci 489,25    Giudici 888,88      
8  Pancrazzi 714,15    Pancrazzi 612,00      
9  Fluxing 985,02    Verdi 700,00    Totale 2177,88

Il ciclo scorre le celle della colonna D confrontando se i nominativi non sono presenti nella colonna A; trovato un CountIf = 0, preleviamo il nominativo e lo inviamo nella colonna G, insieme al valore correlato, stessa riga, colonna E (con Offset) e lo inviamo nella stessa riga del nominativo copiato, ma nella colonna E. Contemporaneamente avremo inizializzato un totalizzatore a 0, e lo incrementiamo col valore letto (e copiato in H) in modo da avere alla fine del ciclo il totale dei valori. potremo avvisare con un messaggio, oppure decidere di inviare il totale in una cella. Vediamo la routine:

Sub ConfrontaAeDinG()
Dim IntervalloDoveCercare As Range
Dim IntervalloRicerca As Range
Dim RigaDestino As Long
Dim Tot As Double
Tot = 0    'impostiamo il totalizzatore a zero
Set IntervalloDoveCercare = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))  'colonna A
Set IntervalloRicerca = Range(Cells(1, 4), Cells(Rows.Count, 4).End(xlUp))  'ciclo che scorre le celle colonna D
RigaDestino = 1
For Each cell In IntervalloRicerca
If Application.CountIf(IntervalloDoveCercare, cell.Value) = 0 Then  'se il dato nella cella letta non è presente in A
Cells(RigaDestino, 7).Value = cell.Value         'si copia il dato nella colonna G
Cells(RigaDestino, 8).Value = cell.Offset(0, 1).Value  'nella H scriviamo il valore presente nella cella a lato (in E) il dato letto
Tot = Tot + cell.Offset(0, 1).Value  'quindi incrementiamo il totale con questo valore
RigaDestino = RigaDestino + 1
End If
Next
MsgBox Tot  'avvisiamo con un messaggio l'importo totale
End Sub

Come si diceva, è necessario avere chiaro ciò che vorremmo ottenere, quando impostiamo istruzioni: se avessimo scorso col ciclo le celle della colonna A, confrontandole con l'elenco presente in D, avremmo ottenuto il contrario, cioè i clienti vecchi ai quali non avremmo fatturato nuovamente, e relativo totale.

Un ultima cosa: se le tabelle da confrontare sono su fogli diversi è necessario usare una modifica nelle istruzioni; infatti l'argomento Rows.Count non si può applicare se non si è sul foglio dove contare le righe, ma possiamo usare questa modifica, supponendo che la seconda tabella sia sul foglio2, colonna A:

  • riga = Sheets(2).Range("A65536").End(xlUp).Row   'con "riga" otteniamo il numero delle righe occupate
    Set IntervalloDoveCercare = Sheets(2).Range("A1:A" & riga)     ' e lo concateniamo al secondo argomento dell'intervallo dove cercare

Questo è tutto.

 

 

Buon lavoro.

prelevato sul sito www.ennius.altervista.org