Δ
1. COMPARACIÓN DE ÍNDICES
Fórmula: =INDEX(C3:E9,MATCH(B13,C3:C9,0),MATCH(B14,C3:E3,0))
Es una alternativa avanzada a las fórmulas VLOOKUP o HLOOKUP (que tienen varios inconvenientes y limitaciones). La combinación de las funciones INDEX y MATCH es una fórmula de búsqueda más potente que VLOOKUP. Aprenda a utilizar INDEX MATCH en este tutorial de Excel. es una poderosa combinación de fórmulas de Excel que llevará su análisis financiero y sus modelos financieros al siguiente nivel.
INDEX devuelve el valor de una celda de una tabla basándose en el número de columna y de fila.
MATCH devuelve la posición de una celda en una fila o columna.
He aquí un ejemplo de las fórmulas INDEX y MATCH combinadas. En este ejemplo, buscamos y devolvemos una persona’La altura de los números se basa en su nombre. Como el nombre y la altura son variables en la fórmula, podemos cambiar ambas!
Fórmula de Comparación de ÍndicesLa combinación de las funciones INDEX y MATCH es una fórmula de búsqueda más potente que VLOOKUP. Aprende a usar INDEX MATCH en este tutorial de Excel.
Para una explicación paso a paso o cómo utilizar esta fórmula, por favor, vea nuestra guía gratuita sobre cómo utilizar INDEX MATCH en ExcelFórmula de coincidencia de índicesCombinar las funciones INDEX y MATCH es una fórmula de búsqueda más potente que VLOOKUP. Aprende a usar INDEX MATCH en este tutorial de Excel..
2. IF combinado con AND / OR
Fórmula: =IF(AND(C2>=C4,C2<=C5),C6,C7)
Cualquiera que’s pasó mucho tiempo haciendo varios tipos de modelos financierosTipos de modelos financierosLos tipos más comunes de modelos financieros incluyen: Modelo de 3 estados financieros, modelo DCF, M&Modelo A, modelo LBO, modelo de presupuesto. Descubra los 10 tipos principales sabe que las fórmulas IF anidadas pueden ser una pesadilla. Combinar IF con la función AND o la función OR puede ser una gran manera de mantener las fórmulas más fáciles de auditar y más fáciles de entender para otros usuarios. En el siguiente ejemplo, verás cómo hemos utilizado las funciones individuales en combinación para crear una fórmula más avanzada.
Para un desglose detallado de cómo realizar esta función en Excel, por favor vea nuestra guía gratuita sobre cómo usar IF con AND / ORIF Statement Between Two NumbersDescargue esta plantilla gratuita para una declaración IF entre dos números en Excel. En este tutorial, le mostramos paso a paso cómo calcular IF con la sentencia AND..Declaración IF entre dos númerosDescargue esta plantilla gratuita para una declaración IF entre dos números en Excel. En este tutorial, le mostramos paso a paso cómo calcular IF con la sentencia AND.
3. OFFSET combinado con SUM o AVERAGE
Fórmula: =SUMA(B4:OFFSET(B4,0,E2-1))
La función OFFSETLa función OFFSET se clasifica dentro de las funciones de búsqueda y referencia de Excel. OFFSET devolverá un rango de celdas. Es decir, devolverá un número determinado de filas y columnas a partir de un rango inicial especificado. por sí sola no es especialmente avanzada, pero cuando la combinamos con otras funciones como SUM o AVERAGE podemos crear una fórmula bastante sofisticada. Suponga que quiere crear una función dinámica que pueda sumar un número variable de celdas. Con la fórmula SUM normal, estás limitado a un cálculo estático, pero añadiendo OFFSET puedes hacer que la referencia de la celda se mueva.
Cómo funciona: Para que esta fórmula funcione, sustituimos la celda de referencia final de la función SUM por la función OFFSET. Esto hace que la fórmula sea dinámica y que la celda referenciada como E2 sea la que le diga a Excel cuántas celdas consecutivas quiere sumar. Ahora bien, nosotros’Tenemos algunas fórmulas avanzadas de Excel!
A continuación se muestra una captura de pantalla de esta fórmula ligeramente más sofisticada en acción.
Como ves, la fórmula SUM comienza en la celda B4, pero termina con una variable, que es la fórmula OFFSET que comienza en B4 y continúa por el valor en E2 (“3”), menos uno. Esto desplaza el final de la fórmula de la suma sobre 2 celdas, sumando 3 años de datos (incluyendo el punto de partida). Como puedes ver en la celda F7, la suma de las celdas B4:D4 es 15, que es lo que nos da la fórmula de desplazamiento y suma.
Aprenda a construir esta fórmula paso a paso en nuestro curso avanzado de Excel.
4. ELIGE
Fórmula: =ELIGIR(opción, opción1, opción2, opción3)
La función CHOOSEFunción CHOOSELa función CHOOSE se clasifica dentro de las funciones de búsqueda y referencia de Excel. Devolverá un valor de un array correspondiente al número de índice proporcionado. La función devolverá la enésima entrada de una lista dada. Como analista financiero, la función CHOOSE es útil para seleccionar de un conjunto de datos dado. Por ejemplo, es ideal para el análisis de escenarios en la modelización financiera. Permite elegir entre un número específico de opciones, y devuelve el “elección” que’he seleccionado. Por ejemplo, imagine que tiene tres hipótesis diferentes sobre el crecimiento de los ingresos el próximo año: 5%, 12% y 18%. Usando la fórmula CHOOSE puedes devolver el 12% si le dices a Excel que quieres la opción #2.
Lea más sobre el análisis de escenarios en ExcelAnálisis de escenariosEl análisis de escenarios es un proceso de examen y evaluación de posibles eventos o escenarios que podrían tener lugar en el futuro y la predicción de la.
Para ver un vídeo de demostración, consulte nuestro Curso de Fórmulas Avanzadas de Excel.
5. XNPV y XIRR
Fórmula: =XNPV(tipo de descuento, flujos de caja, fechas)
Si’Soy un analista que trabaja en la banca de inversiónSeguimiento de la carrera en la banca de inversiónGuía de la carrera en la banca de inversión – planifica tu trayectoria profesional en el IB. Conozca los salarios de la banca de inversión, cómo ser contratado y qué hacer después de una carrera en IB. La división de banca de inversión (IBD) ayuda a gobiernos, empresas e instituciones a conseguir capital y a realizar fusiones y adquisiciones (M&A)., investigación de valores, planificación financiera & análisis (FP&AFP&A AnalistaConviértase en un FP&Un analista de una empresa. Esbozamos el salario, las aptitudes, la personalidad y la formación que necesita para la FP&Un trabajo y una carrera financiera de éxito. FP&Los analistas, gerentes y directores son responsables de proporcionar a los ejecutivos el análisis y la información que necesitan), o cualquier otra área de las finanzas corporativas que requiera descontar flujos de caja, entonces estas fórmulas son un salvavidas!
En pocas palabras, XNPV y XIRR le permiten aplicar fechas específicas a cada flujo de caja individual que’s que se descuentan. El problema de Excel’El problema de las fórmulas básicas de VAN y TIR es que suponen que los períodos de tiempo entre los flujos de caja son iguales. Como analista, normalmente’Tendremos situaciones en las que los flujos de caja no son uniformes, y esta fórmula es la forma de solucionarlo.
Para un desglose más detallado, consulte nuestra guía gratuita de fórmulas de TIR vs XIRRXIRR vs IRRPor qué usar XIRR vs IRR. XIRR asigna fechas específicas a cada flujo de caja individual, por lo que es más preciso que IRR cuando se construye un modelo financiero en Excel. así como nuestra guía XNPV.
6. SUMIF y COUNTIF
Fórmula: =COUNTIF(D5:D12,”>=21″)
Estas dos fórmulas avanzadas son grandes usos de las funciones condicionales. SUMIF suma todas las celdas que cumplen ciertos criterios, y COUNTIF cuenta todas las celdas que cumplen ciertos criterios. Por ejemplo, imagina que quieres contar todas las celdas que son mayores o iguales a 21 años (la edad legal para consumir alcohol en los EE.UU.).S.) para saber cuántas botellas de champán necesita para un evento de un cliente. Puede utilizar COUNTIF como solución avanzada, como se muestra en la siguiente captura de pantalla.
En nuestro curso avanzado de Excel, desglosamos estas fórmulas con más detalle aún.
7. PMT e IPMT
Fórmula: =PMT(tipo de interés, nº de periodos, valor actual)
Si trabajas en la banca comercialPerfil profesional de la banca comercialUna trayectoria profesional en la banca comercial consiste en ofrecer a los clientes productos crediticios como préstamos a plazo, líneas de crédito renovables, facilidades sindicadas, bienes inmuebles, FP&AFP&AFinancial Planning and Analysis (FP&A) es una función importante en una empresa. FP&Los profesionales de la A apoyan la toma de decisiones ejecutivas para o cualquier puesto de analista financiero que se ocupe de los calendarios de deuda, usted’querrá entender estas dos fórmulas detalladas.
La fórmula PMT le da el valor de los pagos iguales durante la vida de un préstamo. Puede utilizarlo junto con el IPMT (que le indica los pagos de intereses para el mismo tipo de préstamo), y luego separar los pagos de capital e intereses.
Este es un ejemplo de cómo utilizar la función PMT para obtener el pago mensual de una hipoteca de 1 millón de dólares al 5% durante 30 años.
8. LEN y TRIM
Fórmulas: =LEN(texto) y =TRIM(texto)
Las fórmulas anteriores son un poco menos comunes, pero ciertamente muy sofisticadas. Son ideales para los analistas financierosLa trifecta del analista® GuíaLa guía definitiva sobre cómo ser un analista financiero de talla mundial. ¿Quieres ser un analista financiero de primera categoría?? ¿Busca seguir las mejores prácticas de la industria y distinguirse de los demás?? Nuestro proceso, llamado The Analyst Trifecta® consiste en el análisis, la presentación & que necesitan organizar y manipular grandes cantidades de datos. Desafortunadamente, los datos que obtenemos no siempre están perfectamente organizados y, a veces, puede haber problemas como espacios extra al principio o al final de las celdas.
La fórmula LEN devuelve una cadena de texto dada como el número de caracteres, lo que es útil cuando se quiere contar cuántos caracteres hay en algún texto.
En el siguiente ejemplo, puedes ver cómo la fórmula TRIM limpia los datos de Excel.
9. CONCATENAR
Fórmula: =A1&” más texto”
Concatenar no es realmente una función en sí misma – it’Son una forma innovadora de unir información de diferentes celdas y hacer que las hojas de trabajo sean más dinámicas. Se trata de una herramienta muy potente para los analistas financieros que realizan modelizaciones financieras (consulte nuestra guía gratuita de modelización financieraGuía gratuita de modelización financieraEsta guía de modelización financiera cubre consejos y mejores prácticas de Excel sobre supuestos, impulsores, previsiones, vinculación de los tres estados, análisis DCF, etc. para obtener más información).
En el siguiente ejemplo, se puede ver cómo el texto “Nueva York” más “, “ se une con “NY” para crear “Nueva York, NY”. Esto le permite crear encabezados y etiquetas dinámicas en las hojas de trabajo. Ahora, en lugar de actualizar la celda B8 directamente, puedes actualizar las celdas B2 y D2 independientemente. Con un gran conjunto de datos, esta es una valiosa habilidad que debe tener a su disposición.
10. Funciones CELL, LEFT, MID y RIGHT
Estas funciones avanzadas de Excel pueden combinarse para crear algunas fórmulas muy avanzadas y complejas de utilizar. La función CELL puede devolver una variedad de información sobre el contenido de una celda (como su nombre, ubicación, fila, columna y más). La función IZQUIERDA puede devolver texto desde el principio de una celda (de izquierda a derecha), MEDIA devuelve texto desde cualquier punto de inicio 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 muestra una ilustración de las tres fórmulas en acción.
Para ver cómo se pueden combinar de forma potente con la función CELL, te lo explicamos paso a paso en nuestra clase de fórmulas avanzadas de Excel.
Más formación en fórmulas de Excel
Esperamos que estas 10 fórmulas avanzadas de Excel le hayan resultado útiles. Deberían ser de gran ayuda para mejorar sus habilidades de análisis financiero y modelización financieraHabilidades de modelización financieraAprenda las 10 habilidades de modelización financiera más importantes y lo que se requiere para ser bueno en la modelización financiera en Excel. Habilidades más importantes: contabilidad.
A continuación encontrará más recursos de nuestro sitio web para ayudarle a convertirse en un usuario avanzado de Excel:
Tutorial gratuito de Excel
Para dominar el arte de Excel, consulte nuestro sitio web’Curso acelerado de Excel GRATUITOFormulas para finanzasEstá buscando un curso acelerado de Excel? Obtenga formación gratuita en Excel para una carrera en finanzas corporativas y banca de inversión del Instituto de Finanzas Corporativas., que le enseña a convertirse en un usuario avanzado de Excel. Aprenda las fórmulas, las funciones y los atajos más importantes para tener confianza en su análisis financiero.
Inicie nuestro sitio web’Fundamentos de Excel – Fórmulas para las finanzas¿Está buscando un curso intensivo de Excel?? Obtenga formación gratuita en Excel para una carrera en finanzas corporativas y banca de inversión del Corporate Finance Institute.
para llevar tu carrera al siguiente nivel y ascender en el escalafón!