Todo el mundo sabe que en Microsoft Excel se puede ordenar alfabéticamente ya sea en orden creciente o decreciente.
Lo que no todo el mundo sabe es que se pueden aplicar ordenaciones anidadas de forma que una vez aplicado el primer criterio de orden, los elementos que sean iguales para ese criterio, se podrán ordenar por el segundo criterio.
Vamos a mostrar este producto con un clásico, un listado de nombres y apellidos.
Aunque existe un botón directo de para ordenar de forma ascendente (A-Z o 0-9) y otro en forma descendiente (Z-A o 9-0) en este caso, vamos a ordenar mediante la opción "orden personalizado..."
Una vez que pulsamos en la opción "orden personalizado..." , se nos muestra una ventana en la que podemos seleccionar el campo y el criterio de ordenación que queremos aplicar.
Para la primera parte del ejemplo vamos a ordenar solo por el primer apellido de forma ascendente.
El resultado quedaría como se muestra a continuación.
Vemos que hay mucha gente cuyo primer apellido es Arosa y podemos comprobar que el criterio de ordenación es un poco raro.
En este caso lo habitual es que a todos los que tengan mismo primer apellido se desordene por el segundo apellido, de esta forma conseguiremos tener juntos todos los hermanos.
Adicionalmente suele ser normal también finalmente ordenarlos por nombre.
Esto se consigue pulsando el botón agregar nivel.
Cómo vimos en el ejemplo hemos añadido dos niveles.
El segundo nivel ordenará por el segundo apellido todos los que tengo un primer apellido coincidente.
Y del tercer nivel ordenará por nombre todos aquellos que tengan primer y segundo apellido coincidente.
El resultado se muestra a continuación.
Adicionalmente es posible cambiar el criterio de ordenación de alguno de los niveles, por ejemplo ordenar por nombre de forma descendente.
Personalmente si hay algo que odio de Microsoft Excel son las celdas combinadas.
A pesar de ser un recurso muy utilizado por algunas gentes son incomodisimas a la hora de aplicar filtros.
Hoy os muestro una alternativa qué consige el mismo efecto sin tener que combinar las celdas.
Se trata de la opción "centrar en la selección."
La opción típica para conoce quiere poner un título como una varias columnas es seleccionar todas las celdas y aplicar combinación de celdas
La combinación de celdas la tenemos disponible desde el menú inicio.
Esto transforma las celdas seleccionadas en una única celda.
Usualmente se utiliza para centrar un texto en varias columnas.
Otra opción que produce el mismo efecto estético es "centrar en la selección."
Seleccionamos las celdas que queremos usar como título.
Botón derecho. formato de celdas.
Además de las acciones habituales y más conocidas existe una poco conocida denominada centrar en la selección.
Esta opción hace que el texto aparezca centrado en las celdas seleccionadas pero sin llegar a combinarlas en una única celda.
De esta forma se consigue el mismo resultado estético pero evitando la problemática de interferir la gestión de los filtros que tiene las celdas combinadas.
En la imagen podéis ver cómo quedaría un texto alineado en el centro de las columnas ABC.
Otra de las herramientas de Microsoft Excel es la previsión.
Excel puede, a partir de un grupo de datos basados en fechas, predecir en un gráfico los valores más probables.
Partimos de un listado de fechas y datos de ventas.
En el grupo Datos, Previsión, encontramos la herramienta Previsión.
Automáticamente se genera un gráfico, que se prolonga hacia el futuro intentando predecir los valores futuros.
El gráfico por defecto es de líneas, pero se puede seleccionar un gráfico de barras.
También es posible elegir la fecha de Final de pronóstico.
Cambiar esta fecha final, aumenta el tamaño del gráfico.
Evidentemente cuanto mas alejada es la fecha en el futuro mayor es la desviación que se puede obervar.
Además la herramienta cuenta con diversos parámetros configurables, como puede ser la fecha de inicio del pronóstico, que no tiene porqué coincidir con el inicio del gráfico.
Finalmente también es posible marcar el check "Incluir estadísticas de previsión" para conocer los valores que se han usado para realizar la previsión.
Normalmente los ejemplos que vienen en los libros son simplificaciones a veces excesivas.
Hoy os traigo un ejemplo un poco más complicado de la opción que se enseñaba ayer buscar objetivo.
En este caso la celda que vamos a fijar con un valor no está relacionada directamente con la celda que se va a cambiar.
En medio habrá dos fórmulas adicionales.
Para usar buscar objetivo el único requisito es que cambiando la cerda que nos indica la app la herramienta se modifique el valor de la celda objetivo, pudiendo haber cualquier cantidad de fórmulas intermedias.
Cómo ejemplo hoy resolveremos una ecuación.
La celda amarilla representará el valor de la incógnita x.
En la celda verde representaremos la diferencia de las dos partes de la ecuación, para que la cooperación se cumpla la diferencia debe ser cero.
Os muestro las fórmulas que estoy usando.
Si dejo la casilla amarilla de la x vacía este dato cogerá el valor 0 y el resto de las fórmulas tendrán los valores que se muestran a continuación.
Como veis para el valor 0 de la x un lado de la igualdad vale menos uno y el otro vale 8 por lo tanto este valor de x no cumple la igualdad.
Como no sabemos resolver este tipo de ecuaciones podemos afrontarlo asignando valores a la x primero el 0 después el uno después el dos etcétera etcétera etcétera.
En este caso vamos a pedirle a Microsoft Excel que haga ese proceso por nosotros el de prueba-error usando la herramienta buscar objetivo.
El animación veis el proceso y el resultado obtenido.
La igualdad se cumple para un valor de x igual a -3.
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.
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.
