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:

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

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.

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.

haz clic en la imagen

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)