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.
Fuente: 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í.
Funció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.
Fuente: 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.
Fuente: 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.
Fuente: 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.
Fuente: 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.
Fuente: 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!
Fuente: 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.
Fuente: 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.
Funció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.
Fuente: 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.
Fuente: 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.
Fuente: 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.
Fuente: 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.
Fuente: 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.
Fuente: 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.
Fuente: 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.
Fuente: 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:
Fuente: 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))
Fuente: 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])
Fuente: 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:
-
Selecciona la pestaña Inicio en el menú.
-
Haz clic en Formato condicional (en la barra de herramientas).
-
Escoge la opción Usar una fórmula para determinar qué celdas formatear.
-
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.
Fuente: 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.
Fuente: 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)
Fuente: 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)
Fuente: 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!