Come verificare il codice fiscale con Excel

Verificare il codice fiscale di una persona può essere un'operazione complessa se non utilizziamo uno strumento adeguato. In questa guida, scopriamo come verificare i codici fiscali di un numero di persone a piacere utilizzando Excel - sfrutteremo la versione 365 per creare una struttura scalabile e in grado di gestire una mole di dati importante, creando una funzione LAMBDA personalizzata.

Il tema di come si crei un codice fiscale è piuttosto ampio e articolato, se è un tema che ti interessa ti consiglio di scoprire meglio questa guida su come creare un codice fiscale su Excel e su come ricavare le informazioni di una persona dal codice fiscale, sempre utilizzando Excel.

Se vuoi provare velocemente inserendo a mano un solo codice fiscale, puoi farlo qui!

Come verificare il codice fiscale con Excel in un file dedicato

La procedura che seguiremo farà tutti i controlli del caso per poi restituirci una formula univoca, una LAMBDA chiamata VERIFICA_CODICE_FISCALE, che potremo portarci su tutti i file di lavoro che desideriamo. Se vuoi scoprire di più sulle funzioni LAMBDA, scopri questa guida che spiega nel dettaglio come creare una funzione LAMBDA da zero su Excel. In sostanza le LAMBDA sono funzioni personalizzate per calcoli complessi, che possono tornare molto utili per evitare di rivedere la logica ogni volta che seguiamo delle procedure articolate come questa.

Detto ciò - avremo diversi controlli da fare ma tutti i check che vedrai hanno una logica booleana, dove ci sono due output possibili:

• VERO: il controllo effettuato ha un esito positivo
• FALSO: il controllo effettuato ha un esito negativo

Questo succede per semplificare anche la parte successiva di controllo, come vedremo, dato che ci permetterà di avere un messaggio personalizzato per ognuno degli errori riscontrati. Questi errori saranno evidenziati a cascata, partendo dal primo check fino ad arrivare al settimo.

Vediamo come fare, vedendo tutti i controlli in sequenza.

Disclaimer: ho effettuato numerosi controlli con codici fiscali trovati online e, dopo varie iterazioni, il file restituisce i commenti corretti. Posso quindi affermare con ragionevole certezza che il file funzioni per la stragrande maggioranza dei codici fiscali. Tuttavia, per verificare al 100% che un codice fiscale sia corretto (ad esempio casi di omocodia), l'unico modo è utilizzare le risorse messe a disposizione dall'agenzia delle entrate, disponibili a questo link.

Verificare che il codice fiscale abbia la lunghezza corretta

Prima di tutto, dobbiamo verificare che il codice fiscale sia della lunghezza giusta. Vediamo la formula associata:

=LUNGHEZZA(B3)=16

In sostanza LUNGHEZZA prende il numero di caratteri di B3 e controlla se questo sia uguale a 16. Se non è uguale a 16, abbiamo già un errore nel nostro codice fiscale e quindi falliamo il nostro controllo.

Questo è il nostro primo check (check_1).

Verificare che i primi sei caratteri del codice fiscale siano testi

Come secondo check, verifichiamo che i primi sei caratteri del codice fiscale siano tutti testi. Se ci fossero dei numeri, saremmo certi di avere fra le mani un codice fiscale sbagliato:

=6=SOMMA(CONTA.SE(TBL_NC[CAR];STRINGA.ESTRAI(B3;SEQUENZA(6);1)))

STRINGA.ESTRAI prende, grazie a SEQUENZA, i primi 6 caratteri del codice fiscale singolarmente. Per ognuno di questi valori viene poi fatto un controllo con la tabella chiamata TBL_NC che ha dentro i caratteri da A a Z, dato che viene conteggiato quante volte venga trovato questo valore - se il valore c'è, appare 1, altrimenti 0. Dato che sommiamo questi sei valori, se il totale viene sei, allora questi sono tutti valori testuali. Se così non è, vuol dire che c'è almeno un numero di troppo.

Ammetto che avrei pensato di potermela cavare con una soluzione più semplice di questa, ma la funzione VAL.TESTO riconosce come testi anche i numeri quindi è necessario arrangiarsi con questa combinazione di formule.

Questo è il nostro secondo check (check_2).

Verificare che, nel codice fiscale, ci sia un valore numerico relativo all'anno di nascita

Tutti i codici fiscali devono avere, come 7' e 8' carattere, dei valori numerici. Vediamo come verificare se questo avvenga nel nostro codice fiscale:

=VAL.NUMERO(NUMERO.VALORE(STRINGA.ESTRAI(codice_fiscale;7;2)))

La funzione prende il settimo e l'ottavo carattere del codice fiscale, unendoli in una sola stringa. A questo punto NUMERO.VALORE trasforma questo valore in numero, se i valori sono numerici - mentre VAL.NUMERO restituisce, come output, o VERO oppure FALSO.

Questo è il nostro terzo check (check_3).

Verificare che, nel codice fiscale, la lettera del mese di nascita sia corretta

Il codice fiscale comprende anche una lettera corrispondente a un mese di nascita e dobbiamo controllare che questo valore sia effettivamente presente a sistema:

1=CONTA.SE(TBL_M[LETTERA];STRINGA.ESTRAI(codice_fiscale;9;1))

La TBL_M presenta tutte le lettere possibili per questo valore. Prendiamo il carattere numero nove del nostro codice fiscale e controlliamo se esista anche in questa tabella per verificare che sia tutto corretto. Anche in questo caso, l'output sarà VERO o FALSO.

Ho modificato il carattere inserendo una X al posto della L e, come vedi, in questo caso abbiamo un errore sia in questo controllo sia nel controllo finale del carattere del codice fiscale.

Questo è il nostro check numero quattro (check_4).

Verificare che, nel codice fiscale, il giorno inserito sia verosimile

Il calcolo del codice fiscale ha una regola strana per il giorno di nascita - se è un uomo, allora sarà inserito il giorno del mese di nascita. Se fosse invece una donna, l'inserimento cambierebbe perchè il calcolo sarebbe giorno del mese + 40.

A questo punto dovremmo dire che il valore del giorno di nascita può avere senso solo se questo valore è compreso fra 1 e 71 (31+40), in formato Excel lo possiamo spiegare così visto che sono il carattere numero 10 e 11 del codice fiscale:

=(NUMERO.VALORE(STRINGA.ESTRAI(codice_fiscale;10;2))<=71)*(NUMERO.VALORE(STRINGA.ESTRAI(codice_fiscale;10;2))>0)=1

Moltiplicando due condizioni avremo, come negli altri casi, un risultato del tipo VERO (1) o FALSO (0). In questo caso verifichiamo che il numero generatosi convertendo in numero il decimo e l'undicesimo carattere sia superiore a 0 e anche inferiore o uguale a 71. Se avessimo 0 non avremmo il giorno della data di nascita, mentre se avessimo un valore superiore a 71 questo valore non sarebbe possibile viste le regole di creazione.

Questo è il quinto controllo, chiamato check_5.

Verificare che, nel codice fiscale, il valore legato al luogo sia possibile

Ogni codice fiscale viene generato considerando una lista possibile di codici legati al luogo di nascita. Queste informazioni le troviamo nella tabella chiamata TBL_COMUNE e possiamo procedere in questo modo per trovare i valori:

=CONTA.SE(TBL_COMUNE[CODICE];STRINGA.ESTRAI(codice_fiscale;12;4))>=1

Prendiamo i 4 valori corrispondenti al luogo di nascita, ovvero i caratteri 12,13, 14 e 15. Verifichiamo se questo codice esista almeno una volta nella TBL_COMUNE e otteniamo VERO oppure FALSO.

Questo è il nostro sesto check (check_6).

Verificare che il codice di controllo del codice fiscale sia corretto

Dobbiamo anche verificare che il codice di controllo del codice fiscale sia corretto. Quest'ultimo calcolo è ben più complesso di quanto pensiamo, dato che c'è una procedura diversa a seconda che un singolo carattere sia in una posizione pari o dispari . Ognuno dei valori pari va riportato a una tabella di conversione chiamata TBL_PARI mentre i valori dispari sono riportati in una tabella chiamata TBL_DIS. Una volta trovati questi valori, bisogna fare una somma del valore totale, dato che sono tutti numeri. Questo numero viene poi diviso per 26 e il resto che ne risulta deve essere confrontato con una terza tabella, chiamata TBL_CONT.

Su Excel, questo processo può essere riassunto così:

=LET(dispari;STRINGA.ESTRAI(B3;SEQUENZA(8;;1;2);1);
dispari_val;CERCA.X(dispari;TBL_DIS[CAR];TBL_DIS[VAL];"");
pari;STRINGA.ESTRAI(B3;SEQUENZA(7;;2;2);1);
pari_val;CERCA.X(pari;TBL_PARI[CAR];TBL_PARI[VAL];"");
totale_somma;SOMMA(dispari_val;pari_val);
carattere_finale;RESTO(totale_somma;26);
carattere_finale_val;CERCA.X(carattere_finale;TBL_CONT[R];TBL_CONT[L];"");
check;carattere_finale_val=DESTRA(B3;1);check)

Con la combinazione di STRINGA.ESTRAI e SEQUENZA possiamo prendere i valori presenti sia in posizioni pari sia dispari. Ad esempio, SEQUENZA(8;;1;2) inizierà dal valore 1 per poi muoversi di 2 in 2. Avremo quindi 1, 3, 5, 7, 9, 11, 13 e 15. La stessa regola varrà per i numeri pari, ma in questo caso avremo 2, 4, 6, 8, 10, 12 e 14. Ognuno di questi valori sarà poi utilizzato per recuperarne il valore relativo nelle tabella TBL_PARI e TBL_DIS. Una volta trovati questi valori, li sommiamo e poi troviamo il resto con la funzione RESTO, dividendo per 26. Questo resto numerico viene poi cercato nella tabella TBL_CONT dato che restituisce un valore testuale relativo. Confrontiamo poi se questo valore sia uguale all'ultimo carattere del codice fiscale inserito con la funzione DESTRA e avremo, anche in questo caso, un corrispettivo VERO oppure FALSO.

Questo è il nostro ultimo check (check_7).

Ora sappiamo che, se tutti i 7 controlli sono positivi, il valore inserito sarà un codice fiscale veritiero!

Come verificare il codice fiscale su Excel minimizzando i controlli necessari

Abbiamo appurato che ci servono ben sette controlli per verificare che il codice fiscale sia veritiero. Questi passaggi sono però complessi e rischiano di creare un file ingestibile, dato che dovremmo avere ben sette celle di controllo per ogni codice fiscale e un'ulteriore cella finale che verifichi che tutti i check siano corretti, prima di validare il codice fiscale inserito.

A questo punto dobbiamo ragionare su quali siano gli elementi necessari per semplificare tutte le formule precedenti e riassumerle. A questo fine possiamo notare che:

• In tutti i controlli abbiamo inserito solamente B3, che è dove si trova il codice fiscale. Questo ci semplificherà notevolmente nella parte successiva di riduzione della formula, dato che all'utente si richiederà solamente l'inserimento di una cella per singolo codice fiscale
• Bisogna stare attenti alle tabelle, perchè al variare dei nomi delle tabelle potremmo avere dei problemi. In questo caso i nomi sono TBL_COMUNE o TBL_DIS, ma potresti volerle modificare e in questo caso dovrai rivedere le formule
• A parte l'ultimo check, gli altri sono relativamente veloci come calcoli. Questo ci permetterà di ridurre al minimo la complessità in fase di inserimento, dichiarando chiaramente dall'inizio alcune informazioni per poi effettuare i passaggi necessari
• In nessuno di questi controlli abbiamo inserito un commento per l'utente. Vogliamo quindi inserire un commento per ognuno degli errori, in modo da guidare l'utente nel confronto finale per arrivare più velocemente a comprendere come mai alcuni codici fiscali non possano essere considerati corretti

Possiamo prendere spunto da tutte queste osservazioni creando una funzione personalizzata LAMBDA chiamata VERIFICA_CODICE_FISCALE.

Come verificare il codice fiscale su Excel in un passaggio solo

Vediamo infatti come verificare il codice fiscale su Excel in un solo passaggio, sfruttando le potenzialità di Excel 365. In questo caso possiamo riassumere tutti i passaggi precedenti in una sola formula LAMBDA:

=LAMBDA(codice_fiscale;

LET(dispari;STRINGA.ESTRAI(codice_fiscale;SEQUENZA(8;;1;2);1);

pari;STRINGA.ESTRAI(codice_fiscale;SEQUENZA(7;;2;2);1);

codice_last;DESTRA(codice_fiscale;1);

dispari_val;CERCA.X(dispari;TBL_DIS[CAR];TBL_DIS[VAL];"");

pari_val;CERCA.X(pari;TBL_PARI[CAR];TBL_PARI[VAL];"");

totale_somma;SOMMA(dispari_val;pari_val);

carattere_finale;RESTO(totale_somma;26);

carattere_finale_val;CERCA.X(carattere_finale;TBL_CONT[R];TBL_CONT[L];"");

check_1;LUNGHEZZA(codice_fiscale)=16;

check_2;6=SOMMA(CONTA.SE(TBL_NC[CAR];STRINGA.ESTRAI(codice_fiscale;SEQUENZA(6);1)));

check_3;VAL.NUMERO(NUMERO.VALORE(STRINGA.ESTRAI(codice_fiscale;7;2)));

check_4;1=CONTA.SE(TBL_M[LETTERA];STRINGA.ESTRAI(codice_fiscale;9;1));

check_5;(NUMERO.VALORE(STRINGA.ESTRAI(codice_fiscale;10;2))<=71)*(NUMERO.VALORE(STRINGA.ESTRAI(codice_fiscale;10;2))>0);

check_6;CONTA.SE(TBL_COMUNE[CODICE];STRINGA.ESTRAI(codice_fiscale;12;4))>=1;

check_7;carattere_finale_val=codice_last;

check_last;PIÙ.SE(

NON(check_1);"La lunghezza del codice fiscale è diversa da 16 caratteri";

NON(check_2);"I primi 6 caratteri del codice fiscale non sono tutti valori testuali";

NON(check_3);"I caratteri 7 e 8 del codice fiscale non sono entrambi valori numerici";

NON(check_4);"Il carattere 9, relativo al mese di nascita, è errato";

NON(check_5);"I valori del giorno di nascita sono errati";

NON(check_6);"I 4 valori corrispondenti al luogo di nascita non sono presenti nel database";

NON(check_7);"L'ultimo carattere non è coerente con i valori del codice fiscale";

VERO;"OK");

check_last))

Tutte le informazioni precedenti sono state duplicate, ma abbiamo inserito LAMBDA(codice_fiscale...) modificando poi ognuna delle formule dei singoli check inserendo proprio il parametro codice_fiscale. Questo accorgimento è fondamentale per creare la funzione LAMBDA, dato che il valore del codice fiscale potrebbe essere ovunque sul file e non solo nella cella B3. Considera che le funzioni LAMBDA sembrano un po' delle equazioni, dove non ci sono riferimenti diretti a celle - anche se in questo caso dobbiamo per forza farlo quando parliamo delle tabelle, altrimenti la formula sarebbe decisamente più complessa di quella inserita.

Abbiamo poi inserito check_last che è una verifica di tutti i check precedenti, che sono vagliati sequenzialmente - partiamo quindi dal check_1. Se questo viene superato, andiamo al check_2 e così via al check_3, 4, 5, 6 e 7. Se uno di questi check non passa, avremo un messaggio di errore. Se invece tutti e 7 i check sono corretti, vedremo "OK". Questo è un accorgimento della funzione PIÙ.SE - quando devi inserire l'ultimo caso, dove con la funzione SE metteresti una formula tipo =SE(x3>2;"ERRORE";"OK") per indicare che è da inserire "OK" in tutti gli altri casi. Qui è la stessa cosa, ma dovremo inserire VERO come se fosse un test prima di inserire il risultato con "OK" perchè stiamo dicendo che tutti i check precedenti sono andati a buon fine e quindi non ci resta che inserire l'ultimo caso, quello nel quale il nostro codice fiscale è verosimile. A questo punto, una formula così complessa, se è andato tutto bene, ci darà semplicemente "OK" - se invece ci fosse un errore, potremo andare a capire meglio che cosa è successo tramite i messaggi contestuali collegati.

Il modo più rapido per capire se questa formula funzioni sarebbe di inserire in una cella qualsiasi l'intera LAMBDA precedente, chiudere la parentesi e poi inserire una nuova parentesi con la cella del nostro codice fiscale. Nel nostro caso sarebbe una formula scritta più o meno così: =LAMBDA(....)(B3).

Se questa formula funziona e non ci sono errori di compilazione, possiamo andare allo step successivo.

Appurato che questa formula funzioni correttamente, possiamo andare poi in Formule > Definisci nome per creare la nostra funzione personalizzata. Questa è la procedura standard per creare LAMBDA su Excel e potremo scegliere sia il nome sia la descrizione.

Il nome e la descrizione puoi sceglierli liberamente, ma dovrai inserire, in Riferito a:, l'intera formula che abbiamo visto in precedenza. Come dicevamo, questa funzione è scollegata da una singola cella e quindi funzionerà all'interno dell'intero file di lavoro.

Come noti, la formula VERIFICA_CODICE_FISCALE è stata inserita sul nostro file di lavoro e possiamo quindi riutilizzarla per controllare tutti i codici fiscali che desideriamo.

Può darsi che ci serva una simile funzione per numerosi codici fiscali in un colpo solo, quindi vediamo come fare.

Come verificare numerosi codici fiscali con Excel

A questo punto possiamo verificare numerosi codici fiscali con Excel in un colpo solo. Il tema principale diventa la scalabilità del file e come vogliamo pescare questi nuovi codici fiscali, quindi ho creato una tabella chiamata TBL_CODICI_FISCALI dove ci sono solo due colonne:

• Colonna CF: i codici fiscali che interessano
• Colonna VERIFICA: qui è inserita la funzione LAMBDA che abbiamo creato in precedenza

Se devi controllare numerosi codici fiscali, ti basterà aggiungerli nella colonna CF sotto quelli già esistenti e vedrai, a colpo d'occhio, tutti i messaggi relativi ai singoli codici fiscali. Puoi chiaramente eliminare quelli che vedi inseriti come esempio e utilizzare questo file per i tuoi controlli di lavoro, oppure prendere i tuoi dati con Power Query e sfruttare poi la LAMBDA inserendo sempre =VERIFICA_CODICE_FISCALE(...).

Sulla destra ho creato un piccolo pannello riassuntivo che penso possa aiutare a farsi un'idea di massima sulla correttezza dei codici fiscali, vediamo le formule inserite velocemente:

=CONTA.VALORI(TBL_CODICI_FISCALI[CF])

Questa formula conta quanti siano i valori inseriti nella tabella, per scoprire il numero di codici fiscali considerati.

=CONTA.SE(TBL_CODICI_FISCALI[VERIFICA];"OK")

Questa formula conta quanti di questi codici abbiano "OK" nella colonna VERIFICA, ovvero i codici fiscali corretti.

=DATI.ORDINA(FILTRO(TBL_CODICI_FISCALI[CF];TBL_CODICI_FISCALI[VERIFICA]="OK";""))

Questa formula restituisce tutti i codici fiscali corretti, ordinandoli alfabeticamente.

=DATI.ORDINA(FILTRO(TBL_CODICI_FISCALI[CF];(TBL_CODICI_FISCALI[CF]<>"")*(TBL_CODICI_FISCALI[VERIFICA]<>"OK")*(TBL_CODICI_FISCALI[VERIFICA]<>"");""))

Questa formula restituisce tutti i codici fiscali per i quali il codice fiscale non sia vuoto, la verifica non restituisca "OK" e quelli nei quali la verifica sia diversa da vuoto, ovvero i casi nei quali abbiamo un qualsivoglia errore.

Conclusioni

Siamo arrivati alla fine della guida per scoprire come verificare il codice fiscale con Excel! Come hai visto, è un tema più complesso di quanto si potrebbe pensare, ma adesso abbiamo il nostro file pronto per l'uso e in grado di gestire tutti i codici fiscali che desideriamo.