Hoja de referencia de fórmulas de Excel: Guía avanzada

Así que ha recorrido los conceptos básicos, ha abordado las cosas intermedias y ahora está listo para luchar con las armas grandes: ¡las fórmulas avanzadas de Excel! Excel tiene muchas funciones y fórmulas avanzadas para cálculos sofisticados, por lo que es útil tener una guía que le señale las que necesita.

Esta hoja de trucos de fórmulas de Excel cubre fórmulas de pronóstico avanzadas, análisis estadístico, funciones de manipulación de datos, manejo de errores y más.

Esta referencia lo equipará con el conocimiento de cómo usar estas funciones avanzadas. Cada fórmula está acompañada de explicaciones claras, sintaxis y ejemplos prácticos para ayudar a los usuarios intermedios de Excel a convertirse en usuarios avanzados.

Descargue e imprima la hoja de trucos y téngala a mano.

Bien, comencemos.

Excel-formulas-cheat-sheet-advanced-1 Descargar

En primer lugar, entremos en fórmulas de matriz.

Tabla de contenido

• Fórmulas de matriz
  • 1. Función ÚNICA
  • 2. Función CLASIFICAR
  • 3. Función FILTRO
• Aleatorizar funciones de Excel
  • 1. Función ALEATORIO ENTRE
  • 2. Función RANDARRAY
• Fórmulas de previsión avanzadas en Microsoft Excel
  • 1. Función PRONÓSTICO.ETS
  • 2. Función TENDENCIA
• Fórmulas estadísticas avanzadas
  • 1. Función PERCENTIL
  • 2. Función CUARTIL
• Fórmulas avanzadas de análisis y manipulación de datos
  • 1. Función TRANSPONER
  • 2. Función FRECUENCIA
  • 3. Función GETPIVOTDATA
• Manejo avanzado de errores
• Fórmulas de búsqueda avanzada
  • 1. Función BUSCARX
  • 2. Función COINCIDIR
• Pensamientos finales

Fórmulas de matriz

Nuestra hoja de trucos para principiantes le muestra cómo ordenar y filtrar sus datos manualmente. Los usuarios avanzados hacen esto mediante programación con fórmulas de matriz.

Las fórmulas de matriz le permiten realizar cálculos en varias celdas simultáneamente. Estas son tres funciones clave:

1. ÚNICO

2. CLASIFICAR

3. FILTRAR

Algunas de estas funciones solo están disponibles en las versiones más recientes de Microsoft Excel.

1. Función ÚNICA

La función ÚNICA acepta un rango y devuelve una lista de valores únicos.

Suponga que tiene datos de ventas de prendas de vestir. Para encontrar los artículos únicos en la columna A, use esta fórmula:

=ÚNICO(A2:A6)

2. Función CLASIFICAR

La función ORDENAR ordena el contenido de un rango. La sintaxis es:

CLASIFICAR(matriz, [índice_clasificación], [orden_clasificación], [por_columna])

• matriz : el rango de valores a ordenar.

• sort_index : la columna para ordenar (1 por defecto)

• sort_order : 1 para ascender (predeterminado) o 2 para descender).

• by_col : VERDADERO para ordenar por columna (predeterminado) o FALSO para ordenar por fila.

Los últimos tres argumentos son opcionales y los valores predeterminados suelen ser los que desea.

Para ordenar los datos de muestra por la primera columna, use esta fórmula:

=ORDENAR(A2:C6)

3. Función FILTRO

La función FILTRO le permite filtrar un rango en una condición específica. Esta es la sintaxis:

=FILTRO(matriz, incluir, [si_vacío])

• array : el rango a filtrar.

• include : la condición que determina qué valores filtrar.

• if_empty : especifica qué devolver si ningún valor cumple con los criterios de filtrado (el valor predeterminado es "").

Suponga que desea filtrar las filas en los datos de muestra para mostrar solo dónde el valor de las ventas es superior a $15. Usa esta fórmula:

=FILTRO(A2:C6, C2:C6>15)

Aleatorizar funciones de Excel

Nuestro truco intermedio muestra cómo usar la función RAND que produce un número aleatorio entre 0 y 1.

Los usuarios avanzados de Excel saben cómo usar las funciones aleatorias para generar rápidamente datos de muestra.

1. Función ALEATORIO ENTRE

La función ALEATORIO ENTRE es más flexible que ALEATORIO porque puede especificar los números superior e inferior como algo distinto de 0 y 1.

Para generar datos con números entre 1 y 100, ingrese esta fórmula en la celda A1:

=ALEATORIO ENTRE(1, 100)

Luego, copie la celda en tantas filas y columnas como desee. Se necesitan segundos para producir una cuadrícula de números aleatorios:

2. Función RANDARRAY

Puede estar pensando que sería bueno evitar la copia manual de la función ALEATORIO ENTRE. Para ser súper avanzado, puede usar la nueva función RANDARRAY en la última versión de Microsoft Excel.

La sintaxis es:

RANDARRAY([filas], [columnas], [min], [max], [número entero])

• filas : número de filas

• columnas : número de columnas

• min : número más bajo

• máx : número más alto

• número entero : por defecto es VERDADERO, de lo contrario usa números decimales.

Para generar números aleatorios entre 1 y 100 en seis filas y nueve columnas, use esta fórmula:

=RANDAR(6, 9, 1, 100, VERDADERO)

Fórmulas de previsión avanzadas en Microsoft Excel

Las funciones de pronóstico de Excel se utilizan para pronosticar valores futuros en función de las tendencias de datos existentes. Estas funciones ayudan a identificar patrones y tendencias de proyectos en función de sus datos.

1. Función PRONÓSTICO.ETS

La función PRONÓSTICO anterior se reemplazó con un conjunto de funciones más nuevas en Excel 2016.

Usted elige la función en función del modelo de previsión específico que desee. Por ejemplo, la función FORECAST.ETS utiliza el algoritmo de suavizado exponencial .

La sintaxis es:

PRONÓSTICO.ETS(fecha_objetivo, valores, cronograma)

• target_date : la fecha para la que desea un valor calculado.

• valores : los datos históricos.

• línea de tiempo : un rango de fechas

Suponga que tiene fechas del 1 al 5 de enero en la columna A y montos de ventas en la columna B. Esta fórmula predecirá el próximo monto de ventas:

=PRONÓSTICO.ETS(“6 de enero de 2023”, B2:B6, A2:A6)

2. Función TENDENCIA

La función TENDENCIA proyecta un conjunto de valores basados ​​en el método de mínimos cuadrados. Devuelve una matriz. La sintaxis es:

TENDENCIA(conocido_y, [conocido_x], [nuevo_x], [const])

• conocido_y : rango de valores y

• conocido_x : rango de valores x

• new_x : rango de valores calculados

A menudo, el conocido_y son los puntos de datos, mientras que el conocido_x son las fechas.

Usando los mismos datos que en el ejemplo anterior, puede ingresar la siguiente fórmula en la celda C2. Se generará un conjunto de valores.

=TENDENCIA(B2:B6, A2:A6)

Fórmulas estadísticas avanzadas

Las funciones estadísticas avanzadas incluyen el cálculo de percentiles y cuartiles. Algunas funciones matemáticas están disponibles para la compatibilidad con versiones anteriores, pero se recomienda utilizar las versiones más actualizadas.

1. Función PERCENTIL

Esta función calcula el porcentaje de puntos de datos que caen por debajo de un valor particular. La sintaxis es:

PERCENTIL.INC(matriz, k)

• matriz : el rango de celdas

• k : el percentil de 0 a 1

Suponga que desea calcular el percentil 70 de los datos en la columna B. Use esta fórmula:

=PERCENTIL.INC(B2:B6, 0.7)

2. Función CUARTIL

Esta función es una variación de la función percentil pero usa cuartos para dividir los datos. Esta es la sintaxis:

CUARTIL.INC(matriz, cuarto)

• matriz : el rango de datos

• cuarto : 1 para el percentil 25, 2 para el 50, 3 para el 75 y 4 para el máximo.

La siguiente fórmula calculará el primer cuartil de datos en la columna A.

=CUARTIL.INC(A2:A5, 1)

Fórmulas avanzadas de análisis y manipulación de datos

Varias funciones avanzadas le permiten cambiar el formato de los datos, analizar distribuciones de frecuencia y extraer datos de tablas dinámicas.

1. TRANSPONER

2. FRECUENCIA

3. OBTENER DATOS DE PIVOTE

1. Función TRANSPONER

A veces desea mover los datos de sus filas a columnas y viceversa. Puede hacerlo manualmente o usar la función TRANSPONER en su lugar.

Suponga que tiene los artículos "Camiseta", "Sudadera con capucha" y "Jeans" en las celdas A2, A3 y A4. Desea convertirlos en encabezados de columna. Esta función devuelve los valores en una sola fila:

=TRANSPONER(A2:A4)

2. Función FRECUENCIA

Esta función calcula la distribución de frecuencia de valores dentro de un conjunto de datos. Esta es la sintaxis:

FRECUENCIA(data_array, bins_array)

• data_array : rango de valores.

• bins_array : los intervalos a usar.

Suponga que tiene datos de ventas en la columna B y desea analizar la distribución de frecuencia de los valores en función de cuántas cantidades son:

• por debajo de 20

• de 20 a 80.

• por encima de 80.

Eso representa tres contenedores y se puede calcular con esta fórmula:

=FRECUENCIA(A2:A6, {20,80})

Para obtener más información sobre las distribuciones de frecuencia en Excel, consulte este video:

3. Función GETPIVOTDATA

Esta función le permite extraer información resumida de tablas dinámicas. Esta es la sintaxis:

GETPIVOTDATA(campo_datos, tabla_pivote, [campo1, elemento1], [campo2, elemento2], …)

• data_field : el campo de datos o el valor que desea recuperar de la tabla dinámica.

• pivot_table : una referencia a la tabla dinámica.

• field1, item1, etc. : los pares de campo/elemento para filtrar.

Suponga que tiene una tabla dinámica basada en el color de los artículos vendidos. Para extraer las ventas de artículos rojos, utilice esta fórmula:

=OBTENERDATOSPIVOTD(“Ventas”, A1, “Color”, “Rojo”)

Manejo avanzado de errores

Incluso las fórmulas de Excel más básicas pueden producir errores. Los usuarios intermedios deben saber cómo usar ISERROR para manejar errores. Los usuarios avanzados también deben estar familiarizados con la función ERROR.TYPE para la identificación de errores.

La función ERROR.TYPE ayuda a identificar el tipo específico de error dentro de una celda o fórmula.

Devuelve un valor numérico correspondiente a diferentes tipos de error, como #N/A, #VALUE!, #REF! y más.

Suponga que tiene un error en la celda A1 y desea identificar su tipo de error. La siguiente fórmula devolverá el número que corresponde al error específico:

=TIPO.ERROR(A1)

Puede combinar esto con múltiples funciones para responder de manera diferente según el tipo de error. Estos son los errores más comunes y sus valores:

1. #¡NULO! (no se encontró una celda común en un rango)

2. #DIV/0! ( división por cero o una celda vacía)

3. #¡VALOR! (tipo de datos o argumento inapropiado en una fórmula)

4. #¡ÁRBITRO! (se ha eliminado una celda referenciada o hay una referencia circular )

5. #¿NOMBRE? (Excel no reconoce la función o el rango)

6. #NUM! (valor numérico no válido)

7. #N/A (no se puede encontrar el valor)

Suponga que desea manejar tres tipos de errores específicos. Utilice esta fórmula para mostrar un mensaje de error específico según el tipo:

=SI(ESERROR(C2), SI(ERROR.TIPO(C2)=2, “Dividir por cero error”, SI(ERROR.TIPO(C2)=3, “Error de valor no válido”, SI(ERROR.TIPO(C2) )=7, “Error de valor no encontrado”, “Otro error”))), C2)

Fórmulas de búsqueda avanzada

Nuestras hojas de trucos para principiantes e intermedios cubrieron una selección de funciones de búsqueda . Aquí hay algunas opciones avanzadas:

1. BUSCARX

2. COINCIDIR

1. Función BUSCARX

Esta función de búsqueda le permite buscar un valor en un rango y devolver un valor correspondiente de otra columna o rango.

Ofrece más versatilidad que las funciones de búsqueda más simples como BUSCARV . Esta es la sintaxis:

BUSCARX(buscar_valor, buscar_matriz, devolver_matriz, [modo_coincidencia], [modo_búsqueda], [si_no_encontrado])

• lookup_value : el valor que desea buscar.

• lookup_array : el rango para la búsqueda.

• return_array : el rango que mostrará el valor correspondiente.

• match_mode : coincidencia exacta (0), siguiente menor (1), siguiente mayor (-1) o coincidencia con comodines (2).

• search_mode : -1 para arriba a abajo, 1 para abajo a arriba o 2 para búsqueda binaria.

• if_not_found : establece el valor a devolver si no se encuentra ninguna coincidencia.

Suponga que desea buscar en un rango de datos la primera aparición de una prenda de ropa y obtener los montos de las ventas. Esta fórmula buscará el texto "Sudadera con capucha" y devolverá el valor en la celda adyacente si lo encuentra:

=BUSCARX(“Sudadera con capucha”, A2:A6, B2:B6)

2. Función COINCIDIR

Esta función le permite encontrar la posición de un valor específico dentro de un rango o una matriz. Esta es la sintaxis:

XMATCH(valor_buscado, matriz_buscado, [tipo_coincidencia], [modo_búsqueda])

• lookup_value : el valor que desea encontrar.

• lookup_array : el rango que desea buscar.

• match_type : coincidencia exacta (0), siguiente menor (-1), siguiente mayor (1).

• search_mode : búsqueda binaria (1) o búsqueda lineal (2).

Suponga que desea encontrar la primera aparición de un elemento amarillo en un rango dentro de la columna B. Use esta fórmula:

=XCOINCIDIR(“Amarillo”, B2:B6, 0)

Pensamientos finales

Esta hoja de trucos ha cubierto una amplia gama de funciones, desde análisis estadístico, fórmulas de búsqueda, técnicas de manipulación de datos y estrategias de manejo de errores.

Los ejemplos y explicaciones proporcionados ayudan a desmitificar estas fórmulas avanzadas, haciéndolas accesibles incluso para aquellos con experiencia limitada.

A medida que comience a incorporarlos en sus tareas de Excel, estará en camino de aumentar sus habilidades de Excel a un nivel avanzado.

Pero recuerda, esta hoja de trucos es solo la punta del iceberg. Las cosas realmente sorprendentes suceden cuando te vuelves creativo, mezclas y combinas estas fórmulas y las adaptas para resolver tus desafíos únicos. Excel es como un lienzo, y estas fórmulas son su paleta, ¡así que adelante, pinte su obra maestra!