Buscar Objetivo (calculando mediante prueba y error en Microsoft Excel)

En ocasiones hay problemas que es muy complicado resolver.

Sin embargo a veces es fácil realizar la aproximación por prueba y error.

Hoy os traigo una herramienta que permita hacer este tipo de aproximaciones en Microsoft Excel.

Se trata de "Buscar objetivo".

Esta opción permite para un problema conocido ir modificando los datos de entrada hasta obtener un resultado deseado.

 En el vídeo de Youtube podéis ver el proceso completo.

 

Cómo ejemplo vamos a ver cómo calcular radio que debería tener un depósito esférico para poder contener 1000 litros de agua.

Disponemos de una tabla en la cual introduciendo el radio se conocen el volumen del depósito esférico.

Pero no sabemos como calcularlo al revés.

Por lo tanto vamos a intentar a través de prueba hierro encontrar un depósito de mis litros.

Aunque podríamos hacerlo a mano este es el típico ejemplo en el que una máquina es mucho más rápida y precisa que un humano.

En la cinta de opciones, datos, análisis de hipótesis,  encontramos la opción buscar objetivo.

Se nos muestra una ventana muy simple con solo tres casillas que cubrir.

Para mi gusto están muy pero que muy mal explicadas.

Empezaremos con la del medio que es la que más fácil se entiende.

Esta celda se denomina "Con el valor", y permite introducir un número.

Este será el número que se corresponda con el resultado de la fórmula que queremos calcular, en nuestro caso se dan los 1000 litros de capacidad del depósito.

Este valor siempre se corresponderá al valor que se obtenga en una celda que contenga una fórmula.

La celda que contiene la fórmula hay que indicar la en la primera casilla la denominada "Definir la celda".

Recordar esta celda tiene que contener una fórmula.

Finalmente la última casilla "Cambiando la celda" es una referencia a una celda en la que se encuentra un número, NO una fórmula.

Sería la celda en la que nosotros introduciría los a mano distintos valores para buscar aquel que consiga que el resultado de la fórmula sea el que estamos buscando.

Una vez qué podamos el botón aceptar se comienza a ver cómo cambian los valores en la celda que indicamos en la casilla "cambiando la celda", hasta que Microsoft Excel alcanza un valor lo más aproximado posible.

Es importante tener en cuenta que no siempre es posible encontrar una solución.

En la siguiente animación podéis ver qué es lo que sucede al pulsar el botón aceptar.

Es posible modificar la precisión y el salto de los valores desde la opción fórmulas de la configuración de Microsoft Excel.

Una de las utilidades de este herramienta es conseguir un resultado cuando no tenemos acceso a la fórmula aunque si tengamos acceso a las casillas tanto de los datos de entrada la fórmula como del resultado.

Filtros mediante segmentación en tablas en Microsoft Excel

Los filtros en Excel son muy útiles, pero si usamos tablas en lugar de usar rangos podemos enriquecerlos aún más.

Usualmente los filtros se aplican sobre rangos, sin embargo el uso de tablas tiene ciertas ventajas.

El tamaño de la tabla se incrementa automáticamente cuando se incluyen datos debajo de ella, esto puede permitirnos seguir entre luciendo datos y teléfono la zona filtrada se incremente automáticamente.

Una de las características más interesantes de los filtros en las tablas es que podemos aplicarles segmentación de datos.

La segmentación lo que hace es mostrar una ventanita con todos los datos que se pueden aplicar en el filtro haciendo más sencilla y rapida la selección del filtro.

En el vídeo de Youtube podéis ver el proceso, y a continuación os lo muestro paso a paso.

En un rango de celdas se puede aplicar un filtro sin ningún problema.

Sin embargo puede ser complicado seleccionar los elementos del filtro y a algunos a usuarios les puede resultar complicado trabajar con los filtros.

En el caso de tener que andar marcando desmarcando elementos del filtro es posible sin querer desmarcar los todos.

Se tenéis que crear una tabla con filtros para una persona que no tiene mucha habilidad al trabajar ellos, una opción mejor es crear una tabla y sobre ella aplicarle segmentación de datos.

Una ventaja evidente tras segmentación de datos es que nuestros elementos según tamaño mucho mayor lo que es muy interesante para gente con deficiencias visuales.

El primer paso será seleccionar nuestro rango de datos y convertirlo en tabla, para ello podemos ir al menú insertar tabla o usar la combinación de teclas CONTROL + T.

Se nos muestra una ventana para confirmar el rango de datos que tenemos convertir en tabla y una opción para indicar si la primera fila son encabezados o no.

Una vez aceptamos cambian los colores del rango ahora es una tabla y automáticamente tiene aplicados unos autofiltros en los encabezados.

Hasta ahora el funcionamiento es exactamente igual que un rango con autofiltro.

Con la salvedad de que siento si se introducen datos debajo de la tabla, la tabla crece, y por lo tanto se filtran todos los datos, cosa que no pasa con rango.

La diferencia está en que si vamos a la nueva pestaña tabla podemos insertar una secuenciación.

Se nos preguntará de los campos disponibles a cuales queremos aplicar la reglamentación.

En este caso debe cenamos el campo comunidad autónoma.

Inmediatamente aparece una ventana con todas las comunidades autónomas.

El funcionamiento por defecto de la segmentación es que cada vez que pulsamos le explicamos un filtro distinto.

Si deseamos que cenar más de una opción de funcionamiento estándar es mantener pulsada la tecla CTRL mientras aplicamos clic.

Si pulsamos clic en una selección que ya está seleccionada se desmarca.

La ventana de segmentación tiene un botón que permite cambiar este comportamiento de forma que antes de que vayamos critican dos añade opciones al filtro o si están marcadas se quitan.

Esta opción resulta muy cómoda para elegir elementos salteados.

Cordón derecho podemos acceder a un menú en el que podemos configurar algunos aspectos de la ventana de segmentación.

El más interesante es quizás el número de columnas lo que nos va a permitir tener los datos en varias columnas en forma tabular en lugar de tenerlos a una columna muy larga de esta forma la mayoría de las ocasiones podremos ver todos los datos del filtro en pantalla, sin tener que hacer scroll.

Os muestro un ejemplo la pantalla de segmentación en dos columnas.

Si la hoja de cálculo que esté realizando con filtros es para que lo usé otra persona esta opción es muy interesante de aplicar.

Filtro top 10 en Microsoft Excel

Mucha gente solo usa los filtros de Excel para seleccionar algunos elementos entre una lista.

Otros para un poquito más allá y son capaces de seleccionar los que cumplen un criterio por ejemplo los valores mayores que cero.

Incluso es posible que haya gente que filtro por color.

Una de las opciones de filtrado menos conocida es la denominada los 10 mejores.

Esta opción permite filtrar por defecto los 10 elementos numéricos con mayor valor de una lista, esto es realizar un top 10.

Para ello accederemos a filtros filtros numéricos, "Diez mejores ..."

Los valores por defecto del filtro permiten realizar el top 10 seleccionando los 10 elementos superiores de la lista,  esto es los 10 elementos con el valor numérico más alto.

Una variación que permite es en lugar de seleccionar los 10 elementos seleccionar el 10% de los elementos más altos, esto significa que un listado de 300 elementos seleccionar a los 30 elementos de mayor valor numérico.

Al igual que podemos seleccionar los 10 elementos de mayor valor numérico también podemos seleccionar los 10 elementos inferiores los de menor valor numérico .

En la captura el top de los 10 elementos de mayor valor de una lista de 130 elementos.

Es importante entender que aunque genera un top10 al tratarse de un filtro simplemente filtra los 10 elementos de mayor valor, y por lo tanto los datos mostrados no aparecen ordenados de mayor a menor.

En la siguiente imagen es el top diez por ciento de la misma lista como se ve se ven los mismos elementos y 13 elementos adicionales.

Es la siguiente imagen es el resultado de los 10 elementos inferiores.

Y finalmente el 10% de los elementos de menor valor.

Como veis es una opción que puede ser interesante al tener que obtener una muestra de una lista.

Invertir selección en Explorador Windows

En ocasiones es necesario seleccionar casi todos los archivos de una carpeta.

Como casi siempre es más sencillo seleccionar los pocos que no necesitamos marcar que todos los que necesitamos marcar. Esto es aplicarlo lógica negativa.

En el explorador de Windows existe una opción que nos permite justamente hacer esto seleccionar los elementos que no queremos y con esta opción invertir la selección marcando todos los demás.

 En la animación podéis ver a lo rápido que resulta.

Si deseas hemos seleccionar una gran cantidad de ficheros resultaría tremendamente incómodo además en ocasiones mientras estamos seleccionando marcamos mal la tecla control y dejamos de seleccionarlos con lo cual tenemos que volver a empezar.

En la parte superior derecha del explorador de Windows encontramos el grupo seleccionar y dentro de él varias opciones que nos permiten seleccionar todo no seleccionar nada, y la que hoy nos interesa invertir selección.

Sobre la imagen anterior en la que estaban seleccionadas dos carpetas hemos pulsado la opción invertir selección.

De esta forma quedan marcados el resto de los elementos de la carpeta.

El funcionamiento de las otras dos opciones es evidente. Pulsamos seleccionar todo se seleccionan todos los elementos de la carpeta.

Curiosamente pasará lo mismo si pulsamos invertir selección sin haber realizado una selección previa.

Y finalmente la última opción que nos queda es no seleccionar nada pulsando la opción del mismo nombre.

Dependiendo de la configuración de Windows es posible que tengamos configurado el sistema operativo para que abra los elementos con un solo click en ese caso esta opción puede ser muy interesante para evitar que se nos abran elementos al ir a eliminar la selección.

Contar elementos separados en una celda de Microsoft Excel

Vamos a implementar un caso en el que usemos el truco de "Contar el número de veces que aparece una letra en una celda de Microsoft Excel".

En este caso vamos a partir de una lista de libros con sus autores.

La columna de autores puede tener uno o más autores, separados por el signo de dividir.

Se nos pide saber cuantos autores tiene un libro.

Con Microsoft Excel no es posible resolver este problema, pero...

El truco es sencillo, si nos damos cuenta de que siempre habrá un autor más que separadores, por lo tanto implementando el ejemplo anterior y sumándole uno, obtendremos el número de autores.

Primero contaremos el número de caracteres de la celda con los autores.

Luego crearemos una versión de la celda con los nombres de autores eliminando los separadores. Para ello usaremos la función sustituir.

Contamos el número de caracteres de la celda sin separadores.

Restamos los tamaños de ambos textos, esto nos dá el número de separadores.

Existe la posibilidad de que no haya ninguno, y el resultado sea cero.

Finalmente al número de separadores le sumamos uno, de forma que este valor representa el número de autores en la celda.

Como se ve es un proceso muy sencillo.

También se puede implementar en una única celda.

Finalmente os muestro en la animación el proceso completo.

Contar el número de veces que aparece una letra en una celda de Microsoft Excel

Microsoft Excel no dispone de una forma nativa de contar el número de apariciones de una letra en una celda.

Sin embargo con un poco de imaginación, y aplicando lógica negativa, se puede llegar al mismo resultado.

Os muestro el procedimiento paso a paso y finalmente en una única función.

En el vídeo podéis ver el proceso completo, y más adelante os lo explico paso a paso.

Comencemos la explicación en este caso vamos a realizar el proceso en cuatro pasos independientes, para que sea más sencillo de entender.

Usaremos como ejemplo un listado de títulos de libros en inglés de los que queremos conocer cuántas letras "a" tiene cada título.

El primer paso es conocer el número de letras que tiene el título para ello usaremos la función largo.

=LARGO(A2)

El segundo paso contiene el truco de este sistema que es eliminar todas las letras que queremos contar.

En este caso usaremos en la función sustituir para sustituir las letras a por nada. En Excel este valor nada se representa por un par de Comillas.

Sustituimos en el texto de la celda A2 las letras a por nada.

=SUSTITUIR(A2;"a";"")

El resultado de este paso es el título en el que le faltan justo las letras que queremos contar.

Hemos aplicado lógica negativa para resolver el problema.

En estos momentos tenemos una versión del título sin las letras que queremos contar.

El tercer paso será conocer cuántas letras tiene esta nueva versión del título.

Para ello volveremos a usar la función largo sobre este nuevo título.

=LARGO(C2)

En estos momentos tenemos dos versiones del título una con todas las letras y otra con todas las letras que no nos interesan.

La respuesta que estamos buscando es la diferencia del número de letras de estos dos textos.

El cuarto y último pasó es pues simplemente restar del largo del primer texto el largo del segundo.

=B2-D2

Cómo resultado obtendremos lo que diferencia los dos textos esto es el número de letras a en el primer texto.

Este truco puede ser aplicable a cualquier letra o carácter.

Finalmente os muestro cómo quedarían estos cuatro pasos unidos en uno único.

=LARGO(A2)-LARGO(SUSTITUIR(A2;"a";""))