Validaciones de CUIT (AFIP Argentina) en Excel y afines
Hace unos días me encomendaron llevar a cabo una auditoría de datos fiscales. Hacía un tiempo que no usaba hojas de cálculo para auditoría, así que fue entretenido reencontrarme con esta herramienta.
La auditoría
Debía identificar aquellos números de CUIT que no fueran válidos. Como siempre el primer paso fue definir “no válido”, y mi hipótesis fue:
- Que sea un valor numérico: no almacenamos los guiones, por lo cual el CUIT puede evaluarse como un número entero;
- Que la longitud sea correcta: sin contar los guiones, 11 dígitos;
- Que la categoría o tipo de contribuyente sea válida;
- Que el dígito verificador sea correcto
Comencé entonces tomando unos pocos números de CUIT para completar la tarea. Por comodidad y llevando la contraria al título, usé Google Spreadsheets, me parece más cómodo, potente (y barato ja!) que Excel.
Verificar si es un valor numérico
Esto es simple:
=NOT(ISTEXT(A2))
Como sheets ya “interpretó” el valor como un número, al usar la función IS_TEXT() podemos asegurarnos que así sea. Entonces, al negar esa aserción con NOT(), nos dirá qué valores no son numéricos. Si aún no lo sabes, la forma fácil de darse cuenta cuándo la hoja de cálculo tomó un valor como numérico al pegarlo es que lo alinea a la derecha por defecto, más allá de que el formato de alineación no haya sido establecido aún.
Validar la longitud
Similar al caso anterior, basta con preguntarlo de forma directa:
=IF(LEN(A2)<>11,"MAL","ok")
Si la longitud no es 11, entonces mostramos “MAL”, caso contrario “ok”.
Validar el tipo de contribuyente (o tipo de sujeto)
Para este caso debemos contar, además de una fórmula, con el nombre o razón social del CUIT para corroborarlo contra el tipo de contribuyente.
=ifs(countif({"20","23","24","25","26","27"},left(A2,2))>0,"Física", countif({"30","33","34"},left(A2,2))>0,"Jurídica", true,"")
No te asustes!!! vamos a analizarla juntos: la función IFS es similar a IF, pero permite hacer varias comparaciones en lugar de una sola. Lo que verificamos aquí es, según la denominación de AFIP, es persona física o jurídica. Antes era posible determinar si una persona física era mujer/varón pero el año pasado esta condición fue modificada, por lo que una mujer puede recibir el prefijo 20 que antes era asignado a hombres, y un hombre un 27 o 23, antes exclusivo para mujeres. En fin, a la fórmula:
=ifs(
countif({"20","23","24","25","26","27"},left(A2,2)) > 0, "Física",
countif({"30","33","34"},left(A2,2)) > 0, "Jurídica",
true, ""
)
¿Más claro? ¿aún no? no hay problema: como ya dijimos, IFS permite comparar varias condiciones. Si vienes del mundo de la programación, sería como un elseif. Sino, explicado para humanos:
left(A2,2) nos devuelve los primeros dos dígitos del CUIT, en este caso, "30".
Entonces,
countif({"20","23","24","25","26","27"},left(A2,2)) > 0, "Física"
es una forma de buscar en el rango de posibles prefijos para personas físicas (20,23, …); si encuentra al menos una coincidencia (left(A2,2)) > 0) entonces imprime que es persona “Física” y sale de la función, caso contrario prueba la siguiente condición:
countif({"30","33","34"},left(A2,2)) > 0, "Jurídica",
que es lo mismo que la anterior, pero en este caso verifica si es persona jurídica. Por último y para que la función no arroje error:
true, ""
es decir, que si llegó a este caso final, imprima texto vacío. Esto es más una cuestión estética que funcional.
Hecho esto y habiendo descartado todos los prefijos no válidos para AFIP, sólo nos quedaría validar este resultado con el nombre o razón social: si el nombre es “Juan Pérez” y el prefijo representa a una persona “Jurídica”, sabremos a simple vista que es incorrecto.
Validar el dígito verificador
Y hemos llegado a la parte más compleja. Si aún no te preparaste un café, este es el momento ideal para hacerlo.
Estás listo? Empecemos:
Unas palabras antes
La formula para obtener el dígito verificador, también conocida como módulo 11, requiere de varios pasos y es común encontrarnos con implementaciones en macros, es decir, funciones programadas específicas para una suite de oficina. La mayoría de estas están desactualizadas tomando todo lo que hemos visto hasta aquí. La idea es lograr una solución que pueda implementarse en cualquier plataforma y que sea fácilmente adaptable por cualquier persona con conocimientos intermedios en hojas de cálculo.
¿Cómo funciona esta fórmula?
1 - Se obtiene un digesto (suma de productos): teniendo el prefijo de tipo de contribuyente y la mantisa, se concatenan ambos, se toma cada dígito y se hace una multiplicación por una secuencia de dígitos, para luego sumar todos los resultados intermedios. En el caso de AFIP dicha secuencia, de izquierda a derecha, es 5432765432. La mantisa es la parte central de un código, en el caso del CUIT es el DNI de la persona física o en número de sociedad en personas jurídicas.
Entonces, si tomamos por ejemplo el CUIT 3067137590 (el cual no tiene dígito verificador), haremos:
3 0 6 7 1 3 7 5 9 0
*
5 4 3 2 7 6 5 4 3 2
=
15 0 18 14 7 18 35 20 27 0
y luego:
15 + 0 + 1 + 14 + 7 + 1 + 3 + 20 + 27 + 0 = 154
Y ese es nuestro digesto.
En fórmula, lo anterior se puede expresar como:
=(RIGHT(F2,1)*2)+ (LEFT(RIGHT(F2,2),1)*3)+ (LEFT(RIGHT(F2,3),1)*4)+ (LEFT(RIGHT(F2,4),1)*5)+ (LEFT(RIGHT(F2,5),1)*6)+ (LEFT(RIGHT(F2,6),1)*7)+ (LEFT(RIGHT(F2,7),1)*2)+ (LEFT(RIGHT(F2,8),1)*3)+ (LEFT(RIGHT(F2,9),1)*4)+ (LEFT(F2,1)*5)
Nuevamente, no te asustes, es mayoritariamente funciones LEFT() y RIGHT() que se usan para obtener cada dígito. En este caso, por practicidad propia, la fórmula toma los dígitos de derecha a izquierda, por lo que la secuencia a multiplicar también está invertida (2345672345). Como sabemos, orden no afecta al resultado de la suma.
2 - Se aplica el módulo 11: =11-mod(G2,11), cuyo resultado en nuestro ejemplo es 11-(154 mod 11) = 7
Eso sería todo!
Verificar el resultado
Sólo nos restaría verificar el dígito verificador calculado contra el informado:
=if(value(right(A2,1))=I2,"ok","MAL")
Luego de esto
Una vez listas las validaciones, me distraje asignando reglas de formato condicional para facilitar la identificación de errores. Si aún no los has usado, te las recomiendo.