10 fórmulas avanzadas de Excel que debes conocer
Cada analista financiero pasa más tiempo en Excel del que probablemente admite. Basándonos en años de experiencia, hemos recopilado las fórmulas de Excel más importantes y avanzadas que todo analista financiero de clase mundial debe conocer.
1. Coincidencia de índice
Fórmula: =INDEX(C3:E9,MATCH(B13,C3:C9,0),MATCH(B14,C3:E3,0))
Esta es una alternativa avanzada a la fórmula BUSCARV o BUSCARH (que tiene varias desventajas y limitaciones). INDEXMATCH es una poderosa combinación de fórmulas de Excel que 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 las fórmulas INDEX y MATCH. En este ejemplo, buscamos y devolvemos la altura de una persona según su nombre. Dado que tanto el nombre como 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 información sobre cómo usar esta fórmula, consulte nuestra guía gratuita sobre cómo usar INDEXMATCH en Excel.
2. Combinación de SI y Y/O
Fórmula: =SI (Y (C2>=C4, C2<=C5), C6, C7)
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 revisar y de entender para otros usuarios. En el siguiente ejemplo, verá cómo podemos usar funciones individuales juntas para crear fórmulas más avanzadas.
Para obtener detalles sobre cómo realizar esta función en Excel, consulte nuestra guía gratuita sobre cómo usar IF con AND/OR. Fórmulas avanzadas de Excel: SI Y
3.OFFSET combinado con SUMA o PROMEDIO
Fórmula: =SUM(B4:OFFSET(B4,0,E2-1))
La función COMPENSACIÓN en sí no es particularmente avanzada, pero cuando la combinamos con funciones como suma o promedio, podemos crear una fórmula bastante compleja. Suponga que desea crear una función dinámica que sume un número variable de celdas. Con la fórmula SUMA normal solo puedes hacer un cálculo estático, pero agregando DESREF puedes hacer que la referencia de la celda se mueva.
Cómo funciona: Para que esta fórmula funcione, reemplazamos la celda de referencia final de la función SUMA con la función SOFF. 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 sumar. ¡Ahora tenemos algunas fórmulas avanzadas de Excel!
A continuación se muestra una captura de pantalla de esta fórmula un poco más compleja.
Fórmula de compensación total
Como puedes ver, la fórmula SUMA comienza en la celda B4 pero termina con una variable, que es la fórmula COMPENSACIÓN, que comienza en B4 y termina en E2 El valor (?3?) continúa disminuyendo en uno. Esto moverá el final de la fórmula de suma a 2 celdas, sumando 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.
4. Elección
Fórmula: =ELEGIR(elección, opción1, opción2, opción3)
La función ELEGIR es excelente para el análisis de escenarios de modelos financieros. Le permite elegir entre un número específico de opciones y devuelve la 'opción' seleccionada. 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 la opción n.° 2, usar la fórmula ELEGIR arroja un 12%.
Lea más sobre análisis de escenarios en Excel.
Elige una función
Para ver una demostración en vídeo, consulta nuestro curso Fórmulas avanzadas de Excel.
5.XNPV y XIRR
Fórmula: =XNPV (tasa de descuento, flujo de caja, fecha)
Si se dedica a la banca de inversión, investigación de acciones, Planificación y Análisis Financiero (FP&A), o analistas en otras áreas de las finanzas corporativas que requieren flujos de efectivo descontados, ¡entonces estas fórmulas le ahorrarán mucho tiempo!
En resumen, XNPV y XIRR te permiten aplicar una fecha específica a cada flujo de efectivo después del descuento. El problema con las fórmulas básicas de VAN y TIR de Excel es que suponen períodos de tiempo iguales entre los flujos de efectivo. A menudo, como analista, encontrará situaciones en las que los flujos de efectivo se distribuyen de manera desigual y esta fórmula es la respuesta.
Fórmulas financieras avanzadas en XNPVExcel
Para obtener un desglose más detallado, consulte la guía gratuita de fórmulas TIR vs. XIRR y la guía XNPV.
6.SUMIF y CONTAR.
Fórmula: =CONTAR.SI(D5:D12,?>=21?)
Estas dos fórmulas avanzadas son muy adecuadas para usar las condiciones funcionan. SUMAR.SI suma todas las celdas que cumplen una condición específica y CONTAR.SI cuenta todas las celdas que cumplen una condición específica. Por ejemplo, digamos que desea contar todas las unidades que tienen 21 años o más (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. Puede utilizar CONTAR.SI 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, número de períodos, valor presente)
Si estás en banca comercial, bienes raíces, FP&A o cualquier Para trabajar en un puesto de analista financiero que se encarga de la planificación de la deuda, necesitará conocer estas dos fórmulas detalladas.
La fórmula PMT le proporciona el valor de pagos iguales durante la vigencia del préstamo. Puede combinar esto con IPMT (que le indica los pagos de intereses del mismo tipo de préstamo) y luego pagar el capital y los intereses por separado.
A continuación se muestra un ejemplo de cómo utilizar la función PMT para obtener un pago mensual del 5 % en una hipoteca de 1 millón de dólares con una tasa de interés del 30 %.
8.LEN y TRIM
Fórmulas: =LEN(texto) y =TRIM(texto)
Estas fórmulas son menos comunes, pero ciertamente muy complejas. 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, a veces, puede haber problemas como espacios adicionales al principio o al final de las celdas.
En el siguiente ejemplo puedes ver cómo borrar datos de Excel con la fórmula TRIM.
Excel-TRIM avanzado
9. Concatenación
Fórmula: =A1&?¿Más texto?
La concatenación en sí no es realmente una característica. , es simplemente una forma innovadora de conectar información de diferentes unidades y hacer que las hojas de trabajo sean más dinámicas. Esta es una herramienta muy poderosa para los analistas financieros que realizan modelos financieros (consulte nuestra guía gratuita de modelos financieros para obtener más información).
En el siguiente ejemplo puedes ver cómo el texto ?NuevaYork? más ?,? se combina con ?NY? Esto le permite crear títulos y etiquetas dinámicos en su hoja de trabajo. Ahora puede actualizar las celdas B2 y D2 directamente en lugar de la celda B8 directamente. Tener un gran conjunto de datos es una habilidad valiosa a tu disposición.
Ejemplo de fórmula concatenada de Excel
10. Funciones CELDA, IZQUIERDA, MEDIA y DERECHA
Estas funciones avanzadas de Excel se pueden combinar para crear algunas funciones muy avanzadas y complejas. Fórmulas a utilizar. La función CELDA puede devolver diversa información sobre el contenido de una celda (como su nombre, posición, fila, columna, etc.).
La función IZQUIERDA puede devolver texto desde el principio de la celda (de izquierda a derecha), MID devuelve texto desde cualquier punto inicial de la celda (de izquierda a derecha) y DERECHA devuelve texto desde el final de la celda (de derecha a izquierda).
A continuación se explican las funciones de estas tres fórmulas.
Para ver cómo combinarlos de manera efectiva con la función CELDA, lo desglosaremos paso a paso en la clase de fórmulas avanzadas de Excel.
Fórmulas CELDA, IZQUIERDA, MEDIA y DERECHA