📈 +25 fórmulas de Excel avanzado +EJEMPLOS
Estas son las fórmulas de Excel avanzado que te ahorrarán horas de trabajo

Estas son las fórmulas de Excel avanzado que te ahorrarán horas de trabajo

Última actualización 12 de Abril del 2022Tiempo de lectura: 7 min.

Alexandra Carranza

Si no has escuchado hablar sobre las fórmulas de Excel avanzado, no sabes la oportunidad que te estás perdiendo de explotar al máximo esta herramienta de Office, y sobre todo de optimizar tu tiempo. ¿Estás aburrido de pasar horas de horas frente a una computadora repitiendo las mismas acciones? No sufras más, haciendo uso de las fórmulas en Excel Avanzado, podrás acabar con ese trabajo tedioso. 

Como menciona el portal Medium, existen varias fórmulas de Excel que pueden servirte para realizar cualquier operación que desees con tan solo haciendo algunos clics. Básicamente, una fórmula de Excel se efectúa en una celda con la finalidad de generar una operación, un cálculo o un procesamiento de valores, donde el nuevo valor o resultado será visualizado en dicha celda.

Aunque no lo creas, dominar las funciones avanzadas de Excel puede darte varias oportunidades y asegurarte trabajos en el mercado laboral. Si eres de los que todavía no conoce estas maravillosas funcionalidades, en este artículo te compartiremos las fórmulas más usadas en Excel avanzado para negocios, o bien para aplicarlas en tu día a día.

1. Fórmula SI

Sintaxis: =SI(C2=5, entonces devolver un VERDADERO, en caso contrario devolver un FALSO)

Esta fórmula de Excel avanzado te permite realizar comparaciones lógicas entre un valor y un resultado que esperas. Por esta razón, la fórmula SI puede tener dos resultados: si la comparación es Verdadera y si la comparación es Falsa. Esta fórmula de Excel permite evaluar texto, valores y errores. 

Por esta razón, es muy importante aprender a utilizar la función SI en Excel para prevenir cualquier clase de fallos. También, puedes usar operadores matemáticos y realizar cálculos adicionales según criterios. Además, esta fórmula avanzada de Excel se puede combinar con otras fórmulas.

Eso sí, recuerda que si usas texto en las fórmulas de Excel avanzado, este debe ir entre comillas ("Texto"). Sólo no se aplica a VERDADERO o FALSO, porque Excel entiende de forma automática.

fórmula si en ExcelFuente: Support Microsoft

2. Fórmula SUMAR.SI

Sintaxis: =SUMAR.SI(rango, criterio, [rango_suma])

Estas fórmulas de Excel avanzado permiten realizar una suma o un promedio solamente para los valores que cumplen una condición. Las condiciones que se pueden ingresar siguen el mismo formato que las condiciones utilizadas en CONTAR.SI y el SI.

 

3. Fórmula CONTAR.SI

Sintaxis: =CONTAR.SI(rango;"criterio")

Esta fórmula de Excel avanzado se usa para sumar los valores de un intervalo que cumple los criterios especificados. También, puedes aplicar los criterios de un rango y sumar los valores correspondientes de otro rango. Usando esta fórmula, Excel contará las celdas que ofrecen la condición que hayas marcado. 

Un ejemplo de función avanzada de Excel sería el nº de casos en un tiempo determinado, el nº de depósitos de una persona concreta o el número de veces que un nombre aparece en una lista, etc.

La función CONTAR.SI devuelve resultados incorrectos cuando se usa para comparar cadenas de más de 255 caracteres o con la cadena #VALUE! Por eso, es uno de los trucos para Excel más útiles que debe estar en tu box de funciones sí o sí.

fórrmula contar siFunción: Excel Total

4. Fórmula CONCATENAR

Sintaxis: =CONCATENAR(“A1”, “espacio“, “B1”)

Esta fórmula de Excel avanzado te permite unir dos o más cadenas de texto en una sola celda. Por ejemplo, si tienes una columna “nombres” y otra “apellidos”, y quieres crear una tercera columna llamada “nombre completo”, esta fórmula de Excel te ahorra el trabajo manual y te permite colocar un espacio entremedio.

fórmula concatenarFuente: Excel Total

5. Fórmula IZQUIERDA | Fórmula DERECHA

Sintaxis: =IZQUIERDA("texto",[núm_de_caracteres])

Sintaxis: =DERECHA(texto,[núm_de_caracteres])

IZQUIERDA devuelve el primer carácter o caracteres de una cadena de texto, según el número de caracteres que especifique el usuario.

DERECHA devuelve el último carácter o caracteres de una cadena de texto, según el número de caracteres especificado.

Estas fórmulas de Excel avanzado son muy útiles para crear códigos en las bases de datos. Puedes usarlo para cortar la parte derecha o izquierda de una celda. Si combinas esto con Concatenar, puedes crear códigos para usuarios de manera rápida.

fórmula izquierda de ExcelFuente: Capacitación virtual

6. Fórmula ELEGIR

Sintaxis: =ELEGIR(núm_índice; valor1; [valor2]; ...)

Esta fórmula de Excel avanzado sirve para el análisis de escenarios financieros. Usa el argumento núm_índice para devolver un valor de una lista de argumentos de valores. Para ello, debes seleccionar uno de los 254 valores posibles a partir del rango del argumento índice.

Por ejemplo, si valor1 a valor7 son los días de la semana, ELEGIR devuelve uno de los días cuando se usa un número entre 1 y 7 como argumento núm_índice. 

fórmula elegirFuente: Excel Total

7. Fórmula BUSCARV

Sintaxis: =BUSCARV (valor de búsqueda, rango que contiene el valor de búsqueda, el número de columna del rango que contiene el valor devuelto, la coincidencia aproximada (TRUE) o la coincidencia exacta (FALSO)).

Esta fórmula de Excel avanzado permite buscar elementos en una tabla o en un rango por fila, y retornar el valor en la misma fila para otra columna diferente (el valor de búsqueda debe estar siempre en la primera columna del rango para que la fórmula de Excel funcione correctamente).

Puedes especificar VERDADERO si deseas una coincidencia aproximada o FALSO si deseas una coincidencia exacta del valor devuelto. Si no especificas nada, el valor predeterminado de esta fórmula avanzada de Excel siempre será VERDADERO o la coincidencia aproximada.

fórmula BUSCARVFuente: Support Microsoft

8. Fórmula PARTIDO DE ÍNDICE

Sintaxis: =ÍNDICE(C3: E9, PARTIDO (B13, C3: C9,0), PARTIDO (B14, C3: E3,0))

Esta fórmula de Excel avanzado es una excelente función de finanzas que las fórmulas VLOOKUP o HLOOKUP. ÍNDICE devuelve el valor de una celda en una tabla basada en la columna y el número de fila. PARTIDO devuelve la posición de una celda en una fila o columna. 

fórmula indice ExcelFuente: Excel Total

9. Fórmula SI.ERROR

Sintaxis: =SI.ERROR(valor;valor_si_error)

Esta es otra de las fórmulas de Excel avanzado que sirve para interceptar y controlar errores en una función. Esta fórmula de Excel devuelve un valor que se especifica si una fórmula lo evalúa como un error; en caso contrario, devuelve el resultado de la fórmula. 

Sin duda, puede ser muy útil para limpiar resultados que dan error, ya que puedes sustituir estos valores por un texto o número de tu elección. Se evalúan los siguientes tipos de error: #N/A, #¡VALOR!, #¡REF!, #¡DIV/0!, #¡NUM!, #¿NOMBRE? o #¡NULO!

fórmula si errorFuente: Aglaia

10. Fórmula EXTRAE

Sintaxis: =EXTRAE("texto", posición_inicial, núm_de_caracteres)

Esta fórmula de Excel avanzado sirve para devolver un número específico de caracteres de una cadena de texto, comenzando en la posición y en función del número de caracteres que especifiques.

Además, es muy útil cuando la extracción de datos que tienes está «sucia» y necesitas extraer una parte del texto de las celdas. EXTRAE siempre cuenta cada carácter como 1, ya sea de un byte o de doble byte e independientemente de la configuración predeterminada de idioma.

fórmula extraeFuente: Excel Total

11. Fórmula SI.CONJUNTO

Sintaxis: =SI.CONJUNTO(Prueba_lógica1; Valor_si_verdadero1; Prueba_lógica2; Valor_si_verdadero2)

Esta fórmula de Excel avanzado sirve para comprobar si se cumplen una o varias condiciones y devolver un valor que corresponda a la primera condición VERDADERO. Asimismo, te permite añadir variables a otras fórmulas complejas en Excel para, por ejemplo, realizar una operación siempre que un conjunto de pruebas se cumplan. 

fórmula conjuntoFunción: Support Microsoft

12. Fórmula COINCIDIR

Sintaxis: =COINCIDIR (valor_buscado,matriz_buscada, [tipo_de_coincidencia]) 

Esta es una de las fórmulas de Excel avanzado que sirve de búsqueda y referencia. Con esta puedes buscar un elemento determinado en un intervalo de celdas y después devolver la posición relativa de dicho elemento en el rango. 

fórmula coincidirFuente: Excel Total

13. Fórmula ESERROR

Sintaxis: =ESERROR (valor que se desea evaluar) 

La función ESERROR sirve para comprobar si un valor —una celda o una fórmula— es un error y devuelve el valor VERDADERO si existe un error, o FALSO si la fórmula o celda tiene un valor correcto. 

fórmula eserrorFuente: Excel Total

14. Fórmula PROMEDIO.SI

Sintaxis: =PROMEDIO.SI (rango; criterios; [rango_promedio])

Esta es una fórmula avanzada de Excel para fines estadísticos. La fórmula PROMEDIO SI en Excel sirve para devolver el promedio de todas las celdas de un rango que cumplen criterios determinados.

La fórmula PROMEDIO SI puede ser muy útil para sacar diferentes promedios de una base de datos en función de variables que establezcas. Por ejemplo, un promedio por cliente, por ticket medio de venta, por producto, etc.

fórmula promedioFuente: Capacitación virtual

15. Fórmula Y

Sintaxis: =Y(valor_lógico1, [valor_lógico2], ...)

Esta fórmula avanzada de Excel permite unir dos o más pruebas lógicas. Si las pruebas lógicas tienen como resultado el valor VERDADERO, entonces la función Y devolverá VERDADERO. En caso de que una sola de las pruebas lógicas resulte FALSO, entonces la función devolverá FALSO.

 

16. Fórmula O

Esta fórmula avanzada de Excel es un poco diferente de la función Y.  La función O verifica que solo un criterio sea VERDADERO y extrae el valor, mientras que Y necesita que todos los criterios coincidan para dar un resultado VERDADERO. Si ningún criterio coincide, entonces sale el valor FALSO.

Fórmula O ExcelFuente: Excel Easy

17. Fórmula REDONDA

Esta fórmula avanzada de Excel se usa para redondear datos con muchos dígitos después del punto decimal para facilitar el cálculo. No es necesario formatear la celda.

Fórmula REDONDA en ExcelFuente: ExtendOffice

18. Fórmula ADECUADA

Si estás buscando fórmulas de Excel avanzado para poner en mayúsculas o minúsculas las letras de una oración en las celdas, esta es la idónea para ti. Puedes cambiar selectivamente las letras en cualquier formato que desees de manera personalizada.

Fórmula ADECUADA en ExcelFuente: EduCBA

19. Fórmula AHORA

Esta fórmula avanzada de Excel se usa para insertar una columna que define la hora en que se realiza la entrada de datos en un conjunto de celdas en particular en el lado derecho de la columna AHORA. También puedes cambiar el valor AHORA solo hasta la fecha marcada.

Fórmula AHORA en ExcelFuente: ExcelJET

20. Fórmula MAX. MIN.

Esta opción de fórmulas de Excel avanzado se utiliza para extraer los valores máximo y mínimo del conjunto de datos seleccionado. Para obtener el valor máximo, debes ingresar 'MAX' en la pestaña de función. En tanto, para obtener el valor mínimo; tienes que introducir 'MIN'. La fórmula extraerá automáticamente los valores de la tabla de datos primarios.

Fórmula MAX. MIN. ExcelFuente: ExtendOffice

21. Fórmula TRIM

Esta fórmula avanzada de Excel se utiliza para eliminar los espacios sin importancia del texto. Si quieres borrar espacios al comienzo de algún nombre, puedes hacerlo usando la fórmula TRIM en Excel como en el siguiente ejemplo:

ejemplo de fórmula TRIM en ExcelFuente: Upgrad

22. Fórmula DESPLAZAMIENTO

Quizá la fórmula OFFSET o DESPLAZAMIENTO puede no ser útil a primera vista, pero cuando se combina con otras fórmulas avanzadas de Excel puedes obtener una fórmula compleja con resultados más rápidos

Por ejemplo, si deseas crear un rol dinámico que pueda sumar cualquier número variable de celdas, la fórmula SUM debe combinarse con la función DESPLAZAMIENTO.

Entonces, para encontrar la suma de valores en celdas variables, la fórmula en Excel debería ser la siguiente:

  • =SUMA(B4:DESPLAZAMIENTO(B4,0,E2-1))

Fórmula DESPLAZAMIENTO en ExcelFuente: ExtendOffice

23. Fórmula PAGO

Esta función avanzada de Excel te ayudará a calcular los pagos futuros adeudados por un préstamo, dada una tasa de interés específica, monto principal y duración del préstamo. 

Y lo mejor es que puedes arrastrar la esquina inferior derecha de las celdas de PAGO a través de los campos para calcular automáticamente el monto del pago mensual para todos los campos. Esto es lo que necesitas para comenzar:

  • El plazo del préstamo.

  • El capital inicial (dinero) del préstamo.

  • El valor futuro.

  • El tipo de préstamo.

Ahora, si deseas encontrar el pago mensual por un monto de capital, la fórmula de Excel para el mismo será la siguiente:

  • = PAGO (tasa, por, PV, [fv], [tipo])

Fórmula PAGO en ExcelFuente: ExtendOffice

24. Fórmula LARGO

Este es uno de los trucos avanzados de Excel que te dice el número de caracteres en una cadena de texto. 

Por ejemplo, si deseas resaltar a las personas que donaron más de $1,000 contando el número de dígitos en la columna de donación. Para ello, debes hacer lo siguiente:

  1. Selecciona la pestaña Inicio en el menú.

  2. Haz clic en Formato condicional (en la barra de herramientas).

  3. Escoge la opción Usar una fórmula para determinar qué celdas formatear.

  4. Si estableces la condición "> 3", entonces todo lo que supere los $1,000 recibirá el formato único, que puedes elegir haciendo clic en la opción Formato.

Fórmula LARGO de ExcelFuente: TecnoExcel

25. Fórmulas XNPV y XIRR

Para todos los analistas que se encuentran sumergidos en la banca de inversión, la investigación de acciones o cualquier otra área de las finanzas corporativas que requiera descontar los flujos de efectivo, estas fórmulas de Excel avanzado seguramente les ahorrarán mucho tiempo.

Por ejemplo, si deseas calcular el valor neto actual (NPV) de cualquier inversión utilizando una tasa específica de descuento y flujos de caja que se producen en intervalos irregulares, utiliza la siguiente fórmula avanzada de Excel:

  • =XNPV(tasa de descuento, flujos de caja, fechas)

Por otro lado, la fórmula XIRR te dice la tasa interna de retorno (flujo de salida = flujo de entrada) para una serie de flujos de efectivo que ocurren a intervalos irregulares.

Fórmulas XNPV y XIRR en ExcelFuente: ExtendOffice

26. Fórmula ALEATORIO ENTRE

Esta fórmula avanzada de Excel te ayuda a seleccionar aleatoriamente un número dentro de un rango predefinido de números. Una vez que colocas los números más bajos y más altos en la fórmula, Excel puede elegir los datos correctos de los campos a los que se adjuntan los nombres en el rango y seleccionarlos al azar. Esta es la fórmula:

  • =ALEATORIO ENTRE(punto inicial, punto final)

Fórmula ALEATORIO ENTRE de ExcelFuente: YouTube

27. Fórmula CUARTILLA

Esta formulación avanzada en Excel devuelve el cuartil (cada uno de los cuatro grupos iguales) en un conjunto de datos dado y puede devolver el valor mínimo, el primer cuartil o el valor máximo del segundo cuartil. 

Esta fórmula trae la cantidad de cuartiles de los campos en una matriz. La función devuelve un valor numérico según el percentil solicitado. Esta es la sintaxis:

  • =CUARTIL (matriz, cuarto)

Fórmula CUARTILLA en ExcelFuente: YouTube

Tutorial de fórmulas de Excel avanzado

Ahora que ya sabes cuáles son las fórmulas y funciones avanzadas de Excel, es momento de poner a prueba tus conocimientos. Pero si aún te quedan dudas al respecto, no te preocupes, nuestro profesor Valentín Rodríguez, del curso online de Excel avanzado para expertos, te explica a profundidad las fórmulas de Excel para no tener errores.

En este tutorial de fórmulas de Excel Avanzado aprenderás a analizar grandes bases de datos, incluso los que no caben en una planilla de Excel. También conocerás los principios básicos de programación de macros en Excel.

Como menciona Valentín, saber el manejo de fórmulas en Excel avanzado puede ser un pilar decisivo a la hora de conseguir un ascenso o bien para conseguir un nuevo empleo. Así que ¡dale play al siguiente video!

Teniendo a la mano varias de las fórmulas de Excel avanzado, podrás tener más tiempo para dedicarte a otras actividades, recuperarás tus horas de sueño y tus trabajos serán impecables.

Asimismo, sabrás cómo enfrentarte al manejo de tablas dinámicas, bases de datos en access, ejecución de macros, fórmulas y funciones avanzadas de Excel de manera profesional.

¿Quieres aprender a manejar este programa y saber cómo emplear los trucos avanzados de Excel para tu empresa? Si es así, dale un vistazo a nuestros cursos online de emprendimiento y negocios.

¡Suerte!

También podría interesarte