Formulas y funciones matriciales

Microsoft Excel

Ratings 5.00 / 5.00

What You Will Learn!

En este curso, aprendera a usar las funciones que usabas, pero de forma matricial, lo que hara su trabajo mucho mas efectivo.
Veremos que son los derrames, el operador de rango derramado, las funciones heredadas, entre otras.
Crearemos una formula de matriz en una sola celda, veremos las ventanas que ofrecen, veremos como modificar formulas de matriz.
Hablaremos de las constantes de matriz unidimensionales, y, bidimensionales, también, usaremos constantes en formulas.
Por supuesto, conoceremos las funciones matriciales, disponibles hasta el momento, porque este curso siempre estará actualizado a la ultima versión.

Description

Microsoft ha cambiado la forma en la que podemos introducir fórmulas. Ha llegado la nueva forma de introducir datos en Excel.

Una fórmula de matriz es una fórmula que puede realizar varios cálculos en uno o más elementos de una matriz. Puede considerar una matriz como una fila o columna de valores, o una combinación de filas y columnas de valores. Las fórmulas de matriz pueden devolver varios resultados o un solo resultado.

A partir de la actualización de septiembre de 2018 para Office 365, cualquier fórmula que pueda devolver varios resultados los hará de forma automática o bien en las celdas vecinas. Este cambio de comportamiento también viene acompañado de varias funciones de matrices dinámicas nuevas. Las fórmulas de matrices dinámicas ya utilizan funciones existentes o las funciones de matriz dinámica, solo deben introducirse en una sola celda y, a continuación, confirmarse pulsando entrar. Antes, las fórmulas de matriz heredadas requieren que se seleccione primero todo el rango de salida y, a continuación, se confirma la fórmula con Ctrl + Mayús + entrar. Suelen denominarse fórmulas matriciales o derrame (CSE).

Temario

Parte 1. Funciones y formulas de matriz.

Formulas de matriz dinámica frente a formulas de matriz CSE heredadas.
Formulas de matriz.
Mejor formulas matriciales dinámicas.
Atenciones en formulas matriciales.
¿Qué son los derrames?
Ejemplo de derrame, o, desbordamiento.
Error de desbordamiento.
Operador implícito (@).
Operador de rango derramado (#).
Matrices dinámicas con derrame.
Símbolo dólar ($).
Ejemplo 1.
Ejemplo 2.
Uso del símbolo dólar.
La función ORDENAR.
La función ORDENARPOR.
La función UNICOS.
La función TRANSPONER.
La función SECUENCIA.
La función FILTRAR.
Nombres y días semana (FILTRAR).
La función FRECUENCIA.
Encontrar valores repetidos (FRECUENCIA).
La función MATRIZALEAT.
La función MMULT.
Funciones TEXTOANTES, TEXTODESPUES, DIVIDIRTEXTO.
Funciones VSTACK y HSTACK.
Aves repetidas consecutivas (hstack/apilarh).
Las funciones TOCOL y TOROW.
Ejemplo. Crear modelo normalizado.
Las funciones WRAPCOLS y WRAPROWS.
Las funciones TOMAR (TAKE) y EXCLUIR (DROP).
Las funciones ELEGIRFILAS y ELEGIRCOLUMNAS.
La función EXPANDIR.
Error de la función WRAPROWS.

Parte 2. Ejemplos matriciales.

Función UNICOS. Conteo de valores duplicados.
Extraer valores únicos de una matriz.
Ejemplo de funciones con, y, sin funciones matriciales.
Cuadro de meses y años creados de forma matricial, con gráficos, y, mini gráficos.
Grafico dinámico a partir de una matriz derramada.
SUMAR_SI INDICE, TRANSPONER con matrices derramadas.
Las funciones FRECUENCIA, e, INDICE.
Matrices con derrames.
Crear una tabla dinámica a partir de matrices dinámicas.
Calculo de máximos y mínimos por años.
Crear formula personalizada con matrices derramadas.
Creación de calendario.
Crear listas desplegables con operador de rango derramado.
Crear una lista desplegable con auto relleno.
Crear calendario anual y mensual actualizable.
Lista desplegable con auto relleno manual.
Total por producto y encabezado seleccionado con la función SUMA.
Ejemplo de la función FILTRAR y SI.CONJUNTO.
Ejemplo de la función FILTRAR y UNIRCADENAS.
Ejemplos de las funciones SECUENCIA, BUSCARV, CONTARA, y, COINCIDIR.
Como calcular el total del ultimo mes de cada trimestre.
Ejemplo de las funciones FILTRAR, TEXTOANTES, y, TEXTODESPUES.
Segunda parte ejemplo FITRAR, TEXTOANTES, TEXTODESPUES.
Ejemplo de la función FILTRAR, y, BUSCARX.
Búsqueda por nombre, apellido 1, y, apellido 2.
Encontrar la primera venta de cada mes para cada año.
Ejemplo de las funciones INDICE, SECUENCIA, y, AGREGAR.
Ejemplo de las funciones FILTRAR, TEXTO, HSTACK, UNICOS.
Ejemplo de personas recortadas.
Poner bordes a matriz desbordada.
Extraer registros duplicados.
Comparar fechas de mismo mes en dos años hasta ultimo día venta del primer año.
Cuadro estadístico del año.
Calcular totales por provincias, en hojas diferentes.
Recuento de provincias por centros y productos.
Obtener provincias de únicas de todas las hojas, estando, la columna provincia en otra posición.
Colocar los productos en su columna.
Encontrar mes, o, meses de mas ventas.
Recuperar encabezados de tablas seleccionadas.
Crear una copia de una tabla sustituyendo los ID por sus nombres.
Crear una lista desplegable para tres encabezados de tabla.
Filtrar por provincia, centro, producto, y, año.
Filtrar por primer y segundo encabezado de forma dinámica.
Ejemplo de la función FILTRAR, y, EXPANDIR.
Seleccionar un año, y, que aparezcan los años siguientes al seleccionado.
Calculo de total menos descuento.
Calcular coste de envío.
Calcular ventas y ventas enviadas.
Encontrar los empleados para cada da de mes.
Dividir una tabla en diferentes hojas.
Distinguir entre mayúsculas minúsculas.
Filtrar por cuatro criterios.
Calculo de total por producto, y, los cinco totales mayores.
Calculo de total por encabezado seleccionado.
Calcular vetas para un día y una hora determinado.
Cálculo de descuento según cantidad y producto.
Calculo de total por provincia, y, tabla.
Informe por temporadas de liga.
Comisiones por vendedor y producto, y, total por vendedor.
Total por fecha y provincia.
Productos vendidos por meses.
Recuento de cantidades.
Total por provincia, centro, y, año.
Buscar precio por centro comercial.
Corregir nombre.
Crear matriz en filas.
Cantidades entre una y dos provincias.
Suma de total excluyendo celdas vacías.
Encontrar precio más cercano a fecha.
Reporte de fechas según producto.
Transponer modelo de datos.
Calculo del total por articulo y color.
Producto más y menos vendido.
Total por provincia.
Encontrar provincias que no se encuentran.
Productos por provincias.
Dividir modelo en columnas.
Pasar filas a columnas.
Dividir centro, producto y fecha en filas.
Informe por centros y productos iguales.
Encontrar centro para cada producto.
Meses en terminar una tarea.
Productos vendidos después de 90 días.
Búsqueda por las iniciales de una provincia.
Recuento provincia/centro en una sola funcion.
Total centro/producto y total por producto.
Cantidad por provincia, producto y fecha.
Pasar modelo en fila a columnas.
Encontrar precio de producto y envase seleccionado.
Filtrar modelo sin columnas y filas vacías.
Encontrar los días de no ventas.
Calculo días no ventas por años.
Buscar centro por las iniciales.
Insertar línea en blanco entre provincias.
Totalizar por provincias.
Informe sin fines de semana.
Veces que aparece cada provincia y total por provincia.
Quitar filas iguales a Granada y Huelva.
Extraer producto y precio.
Graficar desde enero hasta mes seleccionado.
Sustituir más de una palabra.
Unir 2 tablas sin columna vacía.
Calendario con fechas con y sin ventas.
Eliminar filas y columnas vacías.
Reportes por provincia, centro, y, producto.
Reporte de comparación de años y trimestres.
Sustituir una frase por otra.
Asignar trabajadores a días de semana.
Crear reporte duplicado, y, horas de ventas por fechas.
Convertir numero en años y meses.
Reporte por valor mínimo y máximo.
ID por fechas.

Tareas

Filtrar por mas de una condición.
Sustituir la ultima letra "a".
Rellenar celdas vacias con fecha anterior, o, posterior.

Parte 3. Desafíos matriciales.

Posiciones de las letras mayúsculas y sepáralas por guiones.
Primeras damas que comienzan con las letras E, L, M, J, y, H.
Número disarium.
Ordene las columnas de ciudades en función del número de celdas pobladas en orden descendente.
Encontrar las letras del alfabeto que no aparecen.
Veces que aparece cada sub-cadena.
Extraer los últimos números impares.
Encuentra las palabras comunes entre la oración 1 y la oración 2 y ordena las palabras.
Poner frase correcta según numero de nombres.
Transponer los datos en una sola fila para todos los departamentos.
Proporcione una fórmula para contar el número de celdas que tienen solo 2 palabras en el rango A2: A20.
Proporcione una fórmula para enumerar el número máximo de artículos (una sola unidad de cada artículo), que se pueden comprar en 150 desde A2: A10.
Proporcione una fórmula para sumar los últimos 3 valores distintos de cero en el rango A2:A10.
Extraiga los dígitos de la cadena dada y sume los dígitos de esa cadena de extracción en cada fila.
Proporcione una fórmula para invertir un número dado.
Multiplicar columnas Alpha Beta Gamma pro columna Factor.
Devolver números impares mayores, o, numero pares mayores.
Ordenar según frecuencia.
Debemos de encontrar las letras que faltan entre la primera y la última letra.
Repita un carácter según su posición en la cadena e inserte espacios entre ellos.
Enumere al jugador calificado de cada grupo.
Encontrar operadores usados.
Top 3 en todas las materias.
Ordenar palabras según numero que contenga, y, obtener palabras sin números.
Convertir 0 y 1 en meses y trimestres.
Calculo numero de paso.