martes, 10 de abril de 2012

Obtener Dígito Control del codigo EAN 13 con Excel.

Un asíduo del blog me planteaba la siguiente cuestión, que me pareció interesante, por lo que puede aportar Excel a nuestra vida o 'problemas' corrientes.
En concreto se plantea la duda de cómo conseguir un Dígito Control sobre el código EAN-13 que se emplea para codificar los diferentes productos que nos encontramos en cualquier tienda (el de los códigos de barras):

...necesito calcular el digito verificador para un codigo de barras EAN-13.
La idea es poner un codigo en, por ejmplo, la celda A1 (779505123456) y que en B1 me ponga el mismo codigo con el digito verificador...

Tenemos que saber que estos códigos son otorgados por organismos autorizados, y que no es algo que podamos inventarnos, toda la numeración tiene un sentido (podemos leer algo al respecto en wikipedia). Básicamente se explica que el European Article Number 13 es una numeración de trece dígitos que se compone de:
  • Código del país en donde radica la empresa, compuesto por 3 digitos.
  • Código de empresa. Es un número compuesto por 4 o 5 dígitos, que identifica al propietario de la marca.
  • Código de producto. Completa los 12 primeros dígitos.
  • Dígito de control. (que es el que vamos a aprender a calcular en base a las indicaciones del organismo europeo).
Para comprobar el Dígito de control numeraremos los dígitos de derecha a izquierda. A continuación se suman los dígitos de las posiciones impares, el resultado se multiplica por 3, y se le suman los dígitos de las posiciones pares. Se busca la decena inmediatamente superior y se le resta el resultado obtenido. El resultado final es el Dígito de control de la codifación EAN-13. Si el resultado es múltiplo de 10 el dígito de control será 0.

Visto las normas impuestas para determinar ese Dígito Control, procederemos a desgranar las partes de nuestra fórmula:
=REDONDEAR.MAS(SUMAPRODUCTO((0&EXTRAE($A2;COLUMNA(2:2);1))*(ES.PAR(COLUMNA(2:2))))*3+SUMAPRODUCTO((0&EXTRAE($A2;COLUMNA(2:2);1))*(ES.IMPAR(COLUMNA(2:2))*1)*1);-1)-(SUMAPRODUCTO((0&EXTRAE($A2;COLUMNA(2:2);1))*(ES.PAR(COLUMNA(2:2))))*3+SUMAPRODUCTO((0&EXTRAE($A2;COLUMNA(2:2);1))*(ES.IMPAR(COLUMNA(2:2))*1)*1))
ó
=10-RESIDUO(SUMAPRODUCTO((0&EXTRAE($A2;COLUMNA(2:2);1))*(RESIDUO(COLUMNA(2:2);2)=0))*3+SUMAPRODUCTO((0&EXTRAE($A2;COLUMNA(2:2);1))*(RESIDUO(COLUMNA(2:2);2))*1);10)
ó una mezcla de las dos anteriores
=10-RESIDUO(SUMAPRODUCTO((0&EXTRAE($A2;COLUMNA(2:2);1))*(ES.PAR(COLUMNA(2:2))))*3+SUMAPRODUCTO((0&EXTRAE($A2;COLUMNA(2:2);1))*(ES.IMPAR(COLUMNA(2:2)))*1);10)

Una primera línea para ver el efecto matricial de la función {=COLUMNA(2:2)}; con la que conseguimos una ordenación:

Obtener Dígito Control del codigo EAN 13 con Excel.


A continuación explicaré como conseguir bien el desglose por dígito de manera inversa (de derecha a izquierda) o bien de manera directa (de izquierda a derecha) sobre el código a estudiar. Realmente da igual realizar esta ordenación, siempre que tengamos claro que, en sentido directo, los dígitos en posición impar, se encuentran en posición par en sentido inverso; lo caul es importante a la hora de multiplicar unos u otros por 1 ó por 3.
La fórmula matricial para sacar los dígitos numerados de derecha a izquierda es:
{=1*EXTRAE($A2;LARGO($A$2)-COLUMNA(2:2)+1;1)}
y en sentido de izquierda a dercha es:
{=EXTRAE($A2;COLUMNA(2:2);1)}
vemos que anidamos el resultado de COLUMNA(2:2) para conseguir la posición inicial de EXTRAER

Obtener Dígito Control del codigo EAN 13 con Excel.


El siguiente paso es determinar cuales son los valores en posición par o impar; lo que conseguimos por dos métodos. Un primer aplicando directamente la función ES.PAR o ES.IMPAR sobre el resultado de COLUMNA(2:2), multiplicado por 1 para convertirlo en un valor numércio (siempre matricialmente).
Para los pares:
{=ES.PAR(COLUMNA(2:2))*1}
y para los impares:
{=ES.IMPAR(COLUMNA(2:2))*1}
El segundo método se basa en que el RESIDUO o resto de un número par dividido por 2 es cero y el de un número impar es uno.

Obtener Dígito Control del codigo EAN 13 con Excel.


Si seguimos con las indicaciones de la norma, ahora nos tocaría multiplicar los dígitos de las posiciones pares por uno, y las de las posiciones impares por tres, para luego sumar ambas.
Si hubieramos optado por colocar los dígitos del código en sentido directo de izquierda a derecha, multiplicaríamos las posiciones pares por tres y las impares por uno.


Si sumamos el rango obtenido para los pares E6:P6 y para los impares E11:P11:
=SUMA(E6:P6) obtenemos 28
=SUMA(E11:P11) obtenemos 66
sumando ambos llegamos a 28+66 = 94

El último paso es restar este valor de la decena inmediatamente superior, que conseguimos aplicando a nuestro resultado la función REDONDEAR.MAS:
En nuestra hoja lo podemos ver en la celda F15:
=REDONDEAR.MAS(E15;-1)
Con lo que conseguimos en H15 el deseado Dígito Control =F15-E15, y por tanto, resultado 2.

La otra forma de llegar ea este Dígito Control es obtener el resto o RESIDUO del cociente entre nuestra suma de pares e impares (=94) y 10, residuo que restaremos a 10 para alcanzar el mismo resultado final para el D.C. =2.

Todo este desglose de cálculo, realizado sólo a efectos didácticos, queda resumido en una única celda con una fórmula matricial:
=REDONDEAR.MAS(SUMAPRODUCTO((0&EXTRAE($A2;COLUMNA(2:2);1))*(ES.PAR(COLUMNA(2:2))))*3+SUMAPRODUCTO((0&EXTRAE($A2;COLUMNA(2:2);1))*(ES.IMPAR(COLUMNA(2:2))*1)*1);-1)-(SUMAPRODUCTO((0&EXTRAE($A2;COLUMNA(2:2);1))*(ES.PAR(COLUMNA(2:2))))*3+SUMAPRODUCTO((0&EXTRAE($A2;COLUMNA(2:2);1))*(ES.IMPAR(COLUMNA(2:2))*1)*1))
ó
=10-RESIDUO(SUMAPRODUCTO((0&EXTRAE($A2;COLUMNA(2:2);1))*(RESIDUO(COLUMNA(2:2);2)=0))*3+SUMAPRODUCTO((0&EXTRAE($A2;COLUMNA(2:2);1))*(RESIDUO(COLUMNA(2:2);2))*1);10)
ó una mezcla de las dos anteriores
=10-RESIDUO(SUMAPRODUCTO((0&EXTRAE($A2;COLUMNA(2:2);1))*(ES.PAR(COLUMNA(2:2))))*3+SUMAPRODUCTO((0&EXTRAE($A2;COLUMNA(2:2);1))*(ES.IMPAR(COLUMNA(2:2)))*1);10)

11 comentarios:

  1. Hola ExcelForo.

    Me gustaría saber qué fórmulas has utilizados en la celdas E4 y E9.
    De igual manera quisiera saber que has utilizado en la celdas E7 y E12 ya que se repiten los resultados con las celdas E6 y E11.

    De antemano muchas gracias y felicidades por tú blog.

    ResponderEliminar
    Respuestas
    1. Hola,
      bueno, en las celdas E4 (RESIDUO(COLUMNA(2:2);2)=0)y E9 (RESIDUO(COLUMNA(2:2);2)) replica el resultado de aplicar lass fórmulas RESIDUO, que es una manera de indicar si la posición es PAR o IMPAR.
      E7 y E12 no es relevante, simplemente son comprobaciones de como quedaría el producto de cada posición par por 1 y cada impar por 3. Simplemente realizamos por celdas el trabajo que luego se consigue con la matricial.
      Slds

      Eliminar
  2. Les dejo el código para crear una función en VBA (Macros) que genera el código verificador

    Public Function CodBarDigVerificador(Numero As Double)
    Dim CodInvertido As String
    Dim sumPares As Integer
    Dim sumImpares As Integer
    Dim suma As Integer

    CodInvertido = StrReverse(CStr(Numero))

    'Suma de numeros en posiciones impares (Codigo inverido)
    For i = 1 To Len(CodInvertido)
    sumPares = sumPares + CInt(Mid(CodInvertido, i, 1))
    i = i + 1
    Next i
    sumPares = sumPares * 3

    'Suma numeros en posiciones pares del codigo invertido
    For i = 2 To Len(CodInvertido)
    sumImpares = sumImpares + CInt(Mid(CodInvertido, i, 1))
    i = i + 1
    Next i

    suma = sumPares + sumImpares
    'Obtiene la Decena superior
    decena = CInt(CStr(suma / 10)) * 10 + 10

    CodBarDigVerificador = Right(CStr(decena - suma), 1)

    End Function

    Atte Guillermo Rmz P.

    ResponderEliminar
    Respuestas
    1. Muchas gracias Guillermo por compartir ese código, desde luego es más sencillo que sobre la hoja de cálculo
      ;-)
      de nuevo gracias!!

      Eliminar
    2. como aplico la formula

      Eliminar
    3. Hola Fredy,
      que tal estás?, un placer saludarte igualmente.

      Supongo te refieres a la fórmula aportada por Guillermo R..
      Al ser una UDF programada por el usuario, tendrás que incorporar el código en un módulo del proyecto de VBA del Libro de trabajo (desde el Editor de VB)... luego simplemente utiliza la función como cualquier otra estándar sobre una celda...

      Un saludo

      Eliminar
    4. amigos como puedo llevar esta formula a funcion =CAR(MED(C1;1;1)+33)&CAR(MED(C1;2;1)+96)&CAR(MED(C1;3;1)+SI(O(MED(C1;1;1)="0";MED(C1;1;1)="1";MED(C1;1;1)="2";MED(C1;1;1)="3");48;64))&CAR(MED(C1;4;1)+SI(O(MED(C1;1;1)="0";MED(C1;1;1)="4";MED(C1;1;1)="7";MED(C1;1;1)="8");48;64))&CAR(MED(C1;5;1)+SI(O(MED(C1;1;1)="0";MED(C1;1;1)="1";MED(C1;1;1)="4";MED(C1;1;1)="5";MED(C1;1;1)="9");48;64))&CAR(MED(C1;6;1)+SI(O(MED(C1;1;1)="0";MED(C1;1;1)="2";MED(C1;1;1)="5";MED(C1;1;1)="6";MED(C1;1;1)="7");48;64))&CAR(MED(C1;7;1)+SI(O(MED(C1;1;1)="0";MED(C1;1;1)="3";MED(C1;1;1)="6";MED(C1;1;1)="8";MED(C1;1;1)="9");48;64))&"|"&CAR(MED(C1;8;1)+80)&CAR(MED(C1;9;1)+80)&CAR(MED(C1;10;1)+80)&CAR(MED(C1;11;1)+80)&CAR(MED(C1;12;1)+80)&CAR(MED(D1;1;1)+112)

      Eliminar
    5. y agregar la funcion del numero verificador

      Eliminar
    6. Hola David,
      entiendo que estás 'reconvirtiendo' una cadena de caracteres que tienes en la celda C1 en otra 'cosa', concatenándolo empleando el operador de unión &.

      Si quieres generar una UDF (mediante programación VB) tendrás en esencia que repetir el proceso de extracción, conversión/tratamiento del caracter y posterior unión... no aportando nada nuevo en realidad.

      Te dejo un link similar para que eches un vistazo
      http://excelforo.blogspot.com.es/2014/04/vba-obtener-el-iban-de-una-cuenta.html

      Saludos

      Eliminar
    7. exelente pero el codigo del link es para 24 caracter y el que busco yo es para 12 mas codigo verificador si es pocible que me ayuden se les agrdeseria mucho

      Eliminar
    8. Hola David,
      se trata que a través de las pistas encuentres tu propia solución.

      Si necesitas un desarrollo a medida mejor lee las Normas de uso del blog.

      Un cordial saludo y a tu disposición.

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.