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.
------------------------------------------------------------------------------------------------------------------------
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)
{=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)
{=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)}
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}
{=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.
{=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.
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.
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.
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.
Puedo ofrecer servicios de consultoría así como cursos personalizados online. Mírate el siguiente enlace si estás interesado/a.
No hay comentarios:
Publicar un comentario