10 fórmulas avanzadas de Excel que debes conocer

1. Coincidencia de índice

Fórmula: =índice (C3: E9, coincidencia (B13, C3: C9, 0), coincidencia (B14, C3: E3, 0))

Esta es una alternativa avanzada a las fórmulas BUSCARV o BUSCARH, que tienen varias desventajas y limitaciones. INDEXMATCH es una poderosa combinación de fórmulas de Excel que le permite llevar su análisis y modelado financiero al siguiente nivel.

ÍNDICE devuelve el valor de la celda de la tabla según el número de columna y el número de fila.

COINCIDIR devuelve la posición de la celda en la fila o columna.

Este es un ejemplo de combinación de fórmulas de indexación y coincidencia. En este ejemplo, buscamos y devolvemos la altura de una persona según su nombre. Debido a que el nombre y la altura son variables en la fórmula, ¡podemos cambiarlos al mismo tiempo!

Fórmula avanzada de Excel - Coincidencia de índice

Para obtener instrucciones paso a paso o cómo utilizar esta fórmula, consulte la guía gratuita Cómo utilizar INDEXMATCH en Excel.

2. Si se usa en combinación con y/o

Fórmula: = IF(AND(C2 & gt; = C2 C4 & lt; = C5), C6, C7)< / p>

Cualquiera que haya pasado algún tiempo trabajando en varios tipos de modelos financieros sabe que las fórmulas IF anidadas pueden ser una pesadilla. El uso de IF con las funciones AND u OR puede hacer que las fórmulas sean más fáciles de ver y comprender para otros usuarios. En los ejemplos siguientes, verá cómo podemos combinar varias funciones para crear fórmulas más avanzadas.

Para obtener más información sobre cómo realizar esta función en Excel, consulte la guía gratuita sobre cómo usar IF y/o. Fórmulas avanzadas de Excel: si y

3. Compensación combinada con suma o promedio.

Fórmula:= suma (B4: offset (B4, 0, E2-1))

La función OFFSET en sí no es especialmente avanzada, pero cuando la combinamos con suma o promedio Combinando las funciones de las clases, puedes crear una fórmula bastante compleja. Suponga que desea crear una función dinámica donde pueda sumar un número variable de celdas. Con una fórmula de suma normal solo puedes hacer un cálculo estático, pero al agregar un desplazamiento puedes mover la referencia de la celda.

Cómo funciona: Para que esta fórmula funcione, usamos la función SOFF para reemplazar la celda de referencia al final de la función SUMA. Esto hace que la fórmula sea dinámica y la celda a la que se hace referencia como E2 le dice a Excel cuántas celdas consecutivas desea acumular. ¡Ahora tenemos algunas fórmulas avanzadas de Excel!

Aquí tienes una captura de pantalla de esta fórmula ligeramente compleja.

Fórmula de compensación total

Como puede ver, la fórmula de suma comienza con la celda B4, pero termina con una variable. Esta variable es una fórmula de compensación, que comienza con B4. ?3?) para continuar restando uno. Esto moverá el final de la fórmula de suma a 2 celdas y sumará 3 años de datos, incluido el punto de partida. Como puede ver en la celda F7, la suma de las celdas B4:D4 es 15, que es el resultado dado por las fórmulas de compensación y suma.

Aprende cómo construir esta fórmula paso a paso en nuestro curso avanzado de Excel.

Elección

Fórmula:=ELEGIR(elección, opción1, opción2, opción3)

La función de elección es un buen análisis de escenarios para el modelado financiero. ¿Te permite elegir entre un número específico de opciones y devuelve la seleccionada? ¿elegir? . Por ejemplo, digamos que tiene tres supuestos diferentes para el crecimiento de los ingresos del próximo año: 5%, 12% y 18%. Si le dice a Excel que desea elegir el número 2, puede usar la fórmula ELEGIR para devolver el 12%.

Lea más sobre el análisis de esquemas en Excel.

Selección de una función

Para ver una demostración en vídeo, consulte nuestro curso Fórmulas avanzadas de Excel.

5. , planificación y análisis financieros (FP&A) u otros analistas de finanzas corporativas que necesitan descontar el flujo de caja, ¡estas fórmulas le ahorrarán mucho tiempo!

En definitiva, XNPV y XIRR permiten aplicar una fecha concreta a cada flujo de caja descontado. El problema con las fórmulas básicas de VAN y TIR de Excel es que suponen que los períodos de tiempo entre los flujos de efectivo son iguales. A menudo, como analista, se encontrará con situaciones en las que los flujos de efectivo se distribuyen de manera desigual y esta fórmula es la solución.

Fórmulas financieras avanzadas en XNPVExcel

Para obtener un desglose más detallado, consulte la Guía de fórmulas TIR y XIRR y la Guía XNPV gratuitas.

6.SUMAR SI y CONTAR.

Fórmula: = contarsi (D5: d12,? & gt=21?)

Estas dos fórmulas avanzadas son muy adecuadas para utilizar la función condicional. SUMAR.SI suma todas las celdas que coinciden con una condición específica y CONTAR.SI cuenta todas las celdas que coinciden con una condición específica. Por ejemplo, digamos que desea contar todas las unidades mayores o iguales a 21 (la edad legal para beber en los Estados Unidos) para saber cuántas botellas de champán se necesitan para asistir al evento de un cliente. CONTAR.SI se puede utilizar como una solución avanzada, como se muestra en la siguiente captura de pantalla.

Fórmula CONTAR.SI

En el curso avanzado de Excel, desglosaremos estas fórmulas con más detalle.

7.PMT e IPMT

Fórmula: =PMT (tasa de interés, número de períodos, valor presente)

Si estás en banca comercial, bienes raíces , FP&A o cualquier Para trabajar como analista financiero que se encarga de la planificación de la deuda, necesita conocer estas dos fórmulas detalladas.

La fórmula PMT puede brindarle el mismo valor de pago durante la vigencia del préstamo. Puedes combinarlo con IPMT (que te indica los pagos de intereses para el mismo tipo de préstamo) y luego pagar el capital y los intereses por separado.

Este es un ejemplo de cómo utilizar la función PAGO para obtener un pago hipotecario mensual del 5% de $654,38+0 millones a una tasa de interés del 30%.

8.LEN y TRIM

Fórmulas: =LEN(texto) y =TRIM(texto)

Estas fórmulas no son comunes, pero deben ser complicadas . Estas aplicaciones son ideales para analistas financieros que necesitan organizar y procesar grandes cantidades de datos. Desafortunadamente, los datos que obtenemos no siempre están bien organizados y en ocasiones puede haber problemas como espacios extra al principio o al final de las celdas.

En el siguiente ejemplo, puedes ver cómo la fórmula TRIM borra los datos de Excel.

Excel Avanzado-TRIM

9. Conexión

Fórmula:= a1&? ¿Más palabras?

Tándem en sí no es una función real, es simplemente una forma innovadora de conectar información de diferentes unidades para hacer la hoja de trabajo más dinámica. Esta es una herramienta muy poderosa para que los analistas financieros realicen modelos financieros (consulte nuestra guía gratuita de modelos financieros para obtener más información).

En el siguiente ejemplo puedes ver cómo se coloca el texto. ¿Nueva York? ¿además? ,?¿Qué usar? ¿Nueva York? ¿Combinar para crear? ¿Nueva York, Nueva York? . Esto le permite crear títulos y etiquetas dinámicos en su hoja de trabajo. Ahora, en lugar de actualizar la celda B8 directamente, puede actualizar las celdas B2 y D2 directamente. Tener un gran conjunto de datos es una habilidad valiosa para usted.

Ejemplo de fórmulas de serie en Excel

10. Funciones de celda, izquierda, centro y derecha

Puedes combinar estas funciones avanzadas de Excel para crear algunas muy avanzadas y complejas. fórmulas. La función CELDA puede devolver diversa información sobre el contenido de la celda (como nombre, posición, fila, columna, etc.). La función IZQUIERDA puede devolver texto desde el principio de la celda (de izquierda a derecha), la función MEDIA puede devolver texto desde cualquier punto inicial de la celda (de izquierda a derecha) y la función DERECHA puede devolver texto desde la celda). El final de la celda (de derecha a izquierda) devuelve texto.

A continuación se explican las funciones de estas tres fórmulas.

Para ver cómo combinarlas efectivamente con funciones de celda, lo desglosaremos paso a paso en la clase de fórmulas avanzadas de Excel.

Fórmulas de celda, izquierda, media y derecha