Formulario de contacto

Nombre

Correo electrónico *

Mensaje *

domingo, 1 de febrero de 2015

Calcular qué dígitos entre 0-9 faltan en una celda en Excel

Hola, la entrada de hoy viene de un problema que un usuario me expuso por privado a través de esta página, y trata de obtener una fórmula que calcule qué dígitos entre 0-9 se encuentran repetidos en una celda y que dígitos están ausente.

En la parte 1 veremos cómo calcular los dígitos entre 0 y 9 que se encuentran ausentes en una celda.


Así pues, imaginemos que tenemos la celda C5 con el número 78459741, lo que estamos buscando es una fórmula que nos devuelva como ausentes el 6, el 3, el 2 y el 0.


Número: 78459741 (celda C5)


Dígitos ausentes: 6320



Y qué fórmula es esa?




Pues la fórmula a utilizar sería la siguiente:



Ausentes:


{=SUMA(SI.ERROR(K.ESIMO.MENOR(SI(ESERROR(ENCONTRAR(FILA(1:10)-1,C5)),FILA(1:10)-1),FILA(1:10)),0)*10^(FILA(1:10)-1))}




Cómo??? Qué cosa más rara!?



Bueno, antes de que nos volvamos locos con esa fórmula, decir que es una fórmula de matriz o fórmula matricial. Aunque parezca complicado (y lo es, para ser sincero), explicándola paso a paso verás que se puede entender, y si la entiendes bien verás que las fórmulas de matriz son muy útiles en Excel. 


Recuerda que los signos "{}" al principio y al final de una fórmula indican que se trata de una fórmula de matriz, lo cual implica que para ejecutarla en vez de dar a ENTRAR o ENTER hay que darle a CTRL+MAYÚS+ENTRAR. También para ver el resultado en forma de matriz una vez en la celda hay que pulsar F2+F9.



RECOMENDACIÓN:


------------------------------------------------------------------------------------------------------------------------

1. Para poder entender el artículo recomiendo que primero te leas la siguiente entradas sobre las fórmulas de matriz (array fórmulas en inglés).

2. Es también recomendable descargar el archivo para seguir el proceso. Para descargarse el archivo pincha en: Dígitos ausentes en celda


------------------------------------------------------------------------------------------------------------------------




Paso 1: crear una matriz con todos los dígitos a evaluar 



Debemos construir una matriz que contenga todos los dígitos a evaluar, en nuestro caso del 0 al 9.

Para ello podemos hacemos lo siguiente:


{=FILA(1:10)-1}


El resultado de esta fórmula produce el siguiente resultado: ={0;1;2;3;4;5;6;7;8;9}, que es una matriz con los dígitos que necesitamos. 


Enlace: FILA (ROW en inglés)





Paso 2: ver si esos dígitos se encuentran en la celda a evaluar


Debemos ahora ver si esos dígitos se encuentran en la celda C5. 

{=ENCONTRAR(FILA(1:10)-1,C5)}


La función ENCONTRAR() nos ayuda en esta ocasión. Lo que hará en averiguar si los dígitos obtenidos en el paso 1 se encuentran en la celda a comprobar. 

El resultado de esa fórmula sería:

={#¡VALOR!;8;#¡VALOR!;#¡VALOR!;3;4;#¡VALOR!;1;2;5}


Enlace: ENCONTRAR (FIND en inglés)



------------------------------------------------------------------------------------------------------------------------
RECUERDA QUE:

Tú puedes colaborar para seguir manteniendo este blog de una manera gratuita haciendo varios clics en los anuncios de la página. Como referencia, 2 clics por artículo. Muchas gracias por querer colaborar.

------------------------------------------------------------------------------------------------------------------------


Paso 3: Encontrar los errores en la fórmula del paso 2



La siguiente fórmula a utilizar es:

{=ESERROR(ENCONTRAR(FILA(1:10)-1,C5))}


Aquí pretendemos obtener un VERDADERO/FALSO que nos ayudará en el paso 4. Si el dígito aparece en la celda produce FALSO.

El resultado que se obtiene es:={VERDADERO;FALSO;VERDADERO;VERDADERO;FALSO;FALSO;VERDADERO;FALSO;FALSO;FALSO}

Enlace: ESERROR (ISERROR en inglés)




Paso 4: si el dígito está ausente devolver el dígito




La siguiente fórmula a utilizar es:

{=SI(ESERROR(ENCONTRAR(FILA(1:10)-1,C5)),FILA(1:10)-1)}


Con un resultado de:={0;FALSO;2;3;FALSO;FALSO;6;FALSO;FALSO;FALSO}


La función SI() en esta caso nos ayuda a devolver el dígito en caso de que la función ESERROR() de VERDADERO, lo cual como hemos visto en el paso 3 indicaba que el dígito no estaba presente en la celda a evaluar.

Enlace: SI (IF en inglés)



Paso 5: Devolver los dígitos ausentes en orden de menor a mayor




La siguiente fórmula a utilizar es:

{=K.ESIMO.MENOR(SI(ESERROR(ENCONTRAR(FILA(1:10)-1,C5)),FILA(1:10)-1),FILA(1:10))}


Una vez que tenemos los dígitos ausentes, lo que hacemos a continuación es ordenarlos. Para ello utilizamos la función K.ESIMO.MENOR(), que tendrá como matriz la matriz obtenida en el paso 4, y como K tendrá los dígitos del 1-10 obtenidos de la fórmula FILA(1:10).


Con un resultado de:={0;2;3;6;#¡NUM!;#¡NUM!;#¡NUM!;#¡NUM!;#¡NUM!;#¡NUM!}

Enlace: K.ESIMO.MENOR (SMALL en inglés)



Paso 6: Convertir los errores del paso 5 en ceros



La siguiente fórmula a utilizar es:

{=SI.ERROR(K.ESIMO.MENOR(SI(ESERROR(ENCONTRAR(FILA(1:10)-1,C5)),FILA(1:10)-1),FILA(1:10)),0)}


Con un resultado de:={0;2;3;6;0;0;0;0;0;0}


Enlace: SI.ERROR (IFERROR en inglés)




Paso 7: Multiplicamos la matriz resultante por 10 a la potencia del dígito en orden de 0-9


La siguiente fórmula a utilizar es:

{=SI.ERROR(K.ESIMO.MENOR(SI(ESERROR(ENCONTRAR(FILA(1:10)-1,C5)),FILA(1:10)-1),FILA(1:10)),0)*10^(FILA(1:10)-1)}


Este paso es un poco difícil de ver. Teniendo la matriz obtenida en el paso 6, lo que hacemos es multiplicar cada valor por 10 elevado a su posición menos 1 en la matriz, con un resultado de:'={0;20;300;6000;0;0;0;0;0;0}



Paso 8: Sumamos los valores de la matriz


La siguiente fórmula a utilizar es:

{=SUMA(SI.ERROR(K.ESIMO.MENOR(SI(ESERROR(ENCONTRAR(FILA(1:10)-1,C5)),FILA(1:10)-1),FILA(1:10)),0)*10^(FILA(1:10)-1))}


Ahora simplemente sumamos los valores de la matriz obtenida en el paso anterior, con el resultado final de:=6320

Enlace: SUMA (SUM en inglés)






Bueno pues eso ha sido todo por hoy, recuerda que la siguiente entrada será la segunda parte que será cómo calcular los dígitos repetidos en una celda, o en nuestro ejemplo de hoy los dígitos repetidos que son el 7 y el 4.



Te ha gustado el artículo?



No te olvides de mostrar tu gratitud hacia el autor (yo) mediante una de las 3 siguientes acciones:

1. Puedes invitarme a un café pinchando en la siguiente imagen.

Image result for coffee icon


2. Puedes hacer clic en los anuncios mostrados en la página

3. Puedes recomendar el artículo en Google haciendo clic en g+1 (justo después del artículo), también puedes compartirlo en Facebook, y/o dejar comentarios.


Quieres aprender más en Excel o tienes algún problema que necesita solución?

Puedo ofrecer servicios de consultoría así como cursos personalizados online. Mírate el siguiente enlace si estás interesado/a.



Subscríbete y recibe todos las entradas por email


Para ello regístrate suministrando tu email en el sitio "Recibe los trucos en tu email" del menú de la derecha.

O sígueme por las redes sociales