Curso de Excel: Fórmulas y funciones avanzadas en Excel.

En esta clase presento el contenido de la sección dedicada a los primeros pasos con las Fórmulas Matriciales Dinámicas y los Rangos de Desbordamiento Dinámicos.

En esta clase explico que son las Matrices y como nos las podemos encontrar en Excel.

En esta clase creamos el primer ejemplo de Fórmula Matricial Dinámica que nos servirá para dar los primeros pasos con este tipo de fórmulas. En concreto crearemos un primer ejemplo de fórmula que devuelve resultados en varias celdas, devolverá el resultado de restar dos rangos de celdas.

En esta clase repetimos el ejemplo de la clase anterior en una versión de Excel que no tiene el nuevo motor de Fórmulas de Excel, para comprobar las diferencias que hay a la hora de crear Fórmulas Matriciales en versiones anteriores de Excel.

En esta clase seguimos con los ejemplos básicos de Fórmulas Matriciales Dinámicas. En concreto creamos el ejemplo más simple de fórmula matricial que nos puede servir para crear copias de rangos que estarán conectadas a los datos originales.

En esta clase os voy a explicar como podemos utilizar los rangos de desbordamiento dinámicos en fórmulas y como argumentos de funciones.

En esta clase os voy a explicar algunas limitaciones que tienen las fórmulas matriciales dinámicas.

En esta clase os demuestro que todos los rangos de desbordamiento dinámicos que hemos obtenido hasta ahora  son de tamaño fijo y porque sería más interesante que fueran de tamaño variable.

En esta clase os enseño a crear rangos de desbordamientos de tamaño variable usando la función INDIRECTO.

En esta clase os enseño a crear rangos de desbordamiento de tamaño variable usando Tablas de Excel.

En esta clase os presento la sección del curso en la que empezamos con las nuevas funciones matriciales. En concreto en esta sección vamos a ver las funciones que nos permiten ordenar las funciones ORDENAR y ORDENARPOR.

En esta clase realizamos el primer ejemplo con la función ORDENAR, una función que de resultado nos devolverá ordenados los datos que le pasemos como argumento.

En esta clase os explico las opciones de la función ORDENAR que nos permiten indicar el dato por el que se ordena y el sentido del orden que se aplicará.

En esta clase os explico como ordenar datos organizados en columnas con la función ORDENAR.

En esta clase os explico como utilizar una Tabla de Excel como origen de datos de la función ORDENAR y las ventajas que  tiene hacerlo.

En esta clase os explico como ordenar por varias columnas y varios sentidos utilizando la función ORDENAR y matrices de constantes.

En esta clase creamos un ejemplo práctico en el que conseguiremos ordenar con la función ORDENAR seleccionando de una lista el nombre de la columna que queremos utilizar para ordenar y con unos botones de opción el orden que queremos aplicar, ascendente o descendente. Para lograrlo usaremos la función ORDENAR, tablas de Excel, la función COINCIDIR, validaciones de datos y controles de formulario.

En esta clase empezamos con las explicaciones dedicadas a la función ORDENARPOR.

En esta clase os voy a explicar la principal diferencia entre las funciones ORDENAR y ORDENARPOR, que es que en el caso de la función ORDENARPOR podemos ordenar por datos que no están en el origen de datos de la función.

En esta clase os muestro como en las funciones de Excel normales también se pueden usar ahora matrices. Donde antes solo se podía pasar un dato ahora se pueden pasar varios en forma de matriz.

En esta clase os explico como podemos simplificar las fórmulas en las que usamos rangos variables en tamaño usando nombres que referencien a esos rangos.

En esta clase explico que podemos utilizar para ordenar matrices que se crean al vuelo, matrices que se crean en tiempo de cálculo de la fórmula. De esta manera vamos a descubrir que podemos usar conjuntos de datos que no tienen porque existir en celdas para ser utilizados.

En esta clase pasamos a utilizar Tablas de Excel como origen de datos de la función ORDENAPOR. Vamos a comprobar si igual que con la función ORDENAR es mejor usar tablas que rangos de celdas como origen de datos de la función.

En esta clase os muestro como podemos ordenar por datos que no aparecen en los resultados usando Tablas de Excel. De nuevo vamos a ver como el uso de las tablas facilita mucho todo el trabajo que hagamos con la función ORDENARPOR.

En esta clase empezamos un ejemplo práctico en el que os mostraré como usando usando dos listas creadas con validaciones de datos podemos elegir como las columnas por las que se ordenarán unos datos. Estableceremos dos datos por los que ordenar que se podrán elegir en las dos listas que vamos a crear. En esta clase vamos a utilizar nombres de rangos para lograrlo pero, veremos que este sistema tiene una limitación que superaremos en la próxima clase.

En esta clase finalizamos el ejemplo que hemos empezado a crear en la clase anterior cambiando los nombres de rango por tablas de Excel. De esta manera conseguiremos que en los resultados para ordenar se tengan en cuenta las filas de datos que se añadan a los datos de origen.

En esta clase modificamos el ejemplo final de la función ORDENAR para que se usen datos externos en lugar de datos que se almacenen inicialmente en una hoja de Excel. En concreto cargaremos datos de una base de datos de Access al pulsar en un botón que tendrá automatizado el proceso de importación de esos datos. Una vez cargados, el proceso para ordenarlos será exactamente el mismo que hemos visto en las clases anteriores.

En esa sección os explico las nuevas funciones que ha incorporado Excel para filtrar datos, las funciones FILTRAR y UNICOS.

En esta clase empezamos con las funciones dedicadas a filtrar datos y, lo hacemos con le primer ejemplo en el que usamos la función FILTRAR. Con esta función podemos obtener datos filtrados por varias condiciones pero en esta clase empezaremos filtrando con una sola condición. Este ejemplo nos permitirá explicar casi todos los detalles base del trabajo con esta función.

En esta clase os explico como trabajar con la función FILTRAR y rangos de origen que pueden tener un número variable de filas. Vamos a ver como hacerlo con rangos variables creados con la función INDIRECTO y con Tablas de Excel.

En esta clase os explico algunos detalles que debemos tener en cuenta si vamos a filtrar datos usando criterios en los que intervienen valores numéricos. Sobre todo hay un detalle muy importante a tener en cuenta si vamos a crear criterios variables que usan celdas. En concreto crearemos un ejemplo en el que con la función FILTRAR obtendremos los datos que tenemos en una tabla a partir del año que indiquemos en una celda.

En esta clase empezamos con los ejemplos en los que vamos a filtrar configurando varias condiciones y lo hacemos con un primer ejemplo en el que utilizaremos el operador Y para obtener datos que cumplen varias condiciones en diferentes columnas o datos. Se deberán cumplir todas para que los datos se devuelvan en los resultados.

En esta clase seguimos con los ejemplos en los que vamos a filtrar configurando varias condiciones y lo hacemos con un ejemplo en el que controlaremos que se cumplan varias condiciones en la misma columna. Este tipo de situaciones sobre todo suelen ser casos en los que queremos controlar que un dato esté dentro de un intervalo de valores y el ejemplo que os muestro en esta clase va en ese sentido. Crearemos un filtro que mostrará los vehículos que están en un intervalo dado de precios.

En esta clase seguimos con los ejemplos en los que vamos a filtrar configurando varias condiciones pero en esta ocasión cambiamos el planteamiento ya que no se deben cumplir todas las condiciones para devolver los datos en los resultados. Con que se cumpla una de ellas ya se devolverán los datos en los resultados. En concreto en esta clase controlaremos varias condiciones en los mismos datos o columnas. Como ejemplo crearemos un filtro que nos devolverá los vehículos de dos marcas que indicaremos en unas celdas.

En esta clase vamos a ver uno de los momentos más interesantes de todo el curso, ya que os voy a mostrar el primer ejemplo en el que usaremos juntas dos de las nuevas funciones que se han añadido a Excel. Vamos a usar la función ORDENAR junto con la función FILTRAR para ordenar los resultados que devuelve la función la función FILTRAR. Con esto volvemos a multiplicar las posibilidades al poder utilizar de forma conjunta todo lo que os he explicado hasta ahora en el curso y todo lo que vendrá después.

En esta clase seguimos con los ejemplos dedicados a filtrar datos con varias condiciones usando la función FILTRAR. En concreto, seguiremos con los ejemplos en los que usamos el O para filtrar datos que cumplen alguna de las condiciones que estableceremos en el filtrar pero, en varias columnas o datos. En el ejemplo anterior controlábamos varias condiciones para que se cumpliera cualquiera de ellas pero, en la misma columna. Lo de esta clase es diferente en ese sentido.

En esta clase acabamos con los ejemplos dedicados a filtrar datos con varias condiciones usando la función FILTRAR. En concreto, vamos a crear un ejemplo en el que utilizaremos juntos los operadores Y y O para crear un filtro en el que se filtraran datos que pueden cumplir una de dos condiciones que a su vez internamente tienen otras dos condiciones que se deben cumplir obligatoriamente. Es decir crearemos dos condiciones que con que los datos cumplan cualquiera de las dos ya aparecerán en los resultados pero, internamente esas dos condiciones tendrán dos criterios que obligatoriamente se deberán cumplir para dar por cumplida la condición que los engloba.

En esta clase os muestro un uso diferente de la función FILTRAR, un uso que la equipara con las típicas funciones de búsqueda como el BUSCARV pero con algunas ventajas sobre esas funciones que creo que son muy interesante. Vamos a realizar varios ejemplos de búsqueda con la función FILTRAR para que veáis que también la podemos utilizar para realizar búsquedas ya que, la diferencia entre BUSCAR y FILTRAR es mínima. En los ejemplos buscaremos productos por su referencia e incluso realizaremos una búsqueda que buscará por dos referencias distintas, la propia y la del proveedor.

En esta clase empezamos con los ejemplos dedicados a la función UNICOS. Con esta función podemos obtener valores y registros únicos de un conjunto de datos. En concreto en esta clase obtendremos registros únicos de una columna de datos para cargar las opciones que se muestran en una validación de lista.

En esta clase os voy a explicar como podemos usar las funciones FILTRAR y UNICOS para conseguir dos listas conectadas de manera que cuando se seleccione una opción en la primera lista, no se ofrezca la misma opción en la segunda lista. Es decir, ambas listas mostrarán las mismas opciones pero, cuando hagamos una selección en la primera, la opción seleccionada ya no aparecerá en la segunda lista.

En esta clase os voy a explicar como podemos usar también las funciones FILTRAR y UNICOS para conseguir que en una segunda lista aparezcan opciones relacionadas con la primera lista. En concreto crearemos dos listas relacionadas con marcas y modelos de vehículos. Y, cuando seleccionemos una marca en la primera lista, se mostrarán en la segunda los modelos relacionados con la marca seleccionada.

En esta clase os voy a explicar como podemos mejorar la relación que hay entre listas dependientes para que no se produzcan situaciones en las que no concuerdan del todo bien, los valores de las dos listas. Son pequeños detalles que podemos solucionar con un poco de programación VBA, en esta clase os pongo dos ejemplos. En uno evitaremos que se quede seleccionado en dos listas el mismo valor y en otro evitaremos que se quede en la segunda lista un valor que tiene relación con el valor de la primera.

En esta clase empezamos con el ejemplo final de la sección dedicada a las funciones FILTRAR y UNICOS. Se trata de un ejemplo en el que mediante la combinación de diferentes funciones con controles de formulario logaremos establecer dinámicamente los filtros y orden que se aplica a unos datos. En este ejemplo vamos a usar las funciones Y, O, ORDENAR, FILTRAR, UNICOS, validaciones de lista, tablas, casillas de verificación, botones de opción... Es un ejemplo muy completo cuyas explicaciones duran más de una hora, esta es la primera parte.

Segunda parte del ejemplo final de la sección dedicada a las funciones FILTRAR y UNICOS.

En esta clase intento aclarar un poco más el uso de constantes de matrices, cuando debemos usar constantes organizadas en columnas o cuando deben de estar organizadas en filas.

En esta sección os voy a mostrar varios ejemplos avanzados en los que usaremos funciones nuevas y antiguas para demostrar todo le potencial de las nuevas características del motor de fórmulas de Excel. Vamos a crear ejemplos muy avanzados, muchos de los cuales no encontraréis en ningún otro sitio.

En esta clase os explico como ordenar columnas que no están adyacentes usando la función ELEGIR. Esta función es una función clásica de Excel que ahora con el uso de forma matricial nos va a permitir crear matrices de columnas personalizadas, lo que vamos a usar para poder ordenar datos que no están pegados, que no están adyacentes.

Igual que hemos visto como obtener columnas no adyacentes ordenas, también podemos obtenerlas a la hora de filtrar y en esta clase os explico como hacerlo usando de nuevo la función ELEGIR, en esta ocasión acompañada con la función FILTRAR que nos permitirá filtrar la matriz personalizada que crearemos con la función ELEGIR.

En esta clase os explico cómo podemos usar las funciones CONTAR.SI y SUMAR.SI junto con la función UNICOS, para automatizar el contar y sumar celdas que cumplan una condición. Con ello vamos a ver el primer ejemplo claro de las ventajas que obtienen las funciones clásicas de Excel usadas de forma matricial.

En esta clase os voy a explicar como obtener valores únicos de varias columnas, lo que se conoce como valores únicos a nivel de registro y también, aprovecharé la explicación para realizar más ejemplos en los que contaremos celdas que cumplan una condición y que cumplan varias. Para ello usaremos las funciones CONTAR.SI y CONTAR.SI.CONJUNTO.

En esta clase os explico como podemos crear tablas de resultados en las que se tienen en cuenta los rótulos de columnas y filas para controlar condiciones. Usaremos las funciones TRANSPONDER, UNICOS, SUMAR.SI.CONJUNTO y CONTAR.SI.CONJUNTO para crearlas.

En esta clase os explico como añadir la posibilidad de filtrar una tabla de resultados con una validación de lista.

En esta clase os explico como crear secuencias numéricas con la nueva función SECUENCIA. Os mostraré su funcionamiento básico y también veremos un ejemplo de como generar las fechas de un mes seleccionado en una lista.

En esta clase os muestro como obtener los valores máximos que indiquemos usando las funciones K.ESIMO.MAYOR y FILTRAR.

En esta clase os explico como crear criterios parciales de texto para filtrar datos que contienen el texto que indiquemos. Para ello usaremos las funciones FILTRAR, HALLAR y ENCONTRAR.

En esta clase empezamos el primer gran ejemplo final. En concreto vamos a crear un resumen con los modelos que tenemos de cada marca en el stock. Para ello usaremos las funciones FILTRAR, UNICOS, TRANSPONER, SECUENCIA, Formato Condicional...

Seguimos con el ejemplo del concesionario para añadir un ranking en el que se mostrarán los vehículos con más descuento de la marca que elijamos. Para conseguirlo usaremos las funciones K.ESIMO.MAYOR  y FILTRAR.

En esta clase finalizamos por el momento el ejemplo del concesionario añadiendo la posibilidad de crear rankings por marca y precio más barato.