Si eres un usuario frecuente de Excel, seguramente no es la primera vez que escuchas sobre la función de coincidencia de índice. Pero ¿sabes realmente cómo aprovechar al máximo esta poderosa fórmula? En este artículo, exploraremos en detalle la función de coincidencia de índice en Excel y cómo puedes utilizarla para simplificar tus cálculos y optimizar tus tareas diarias. Además, te presentaremos una útil calculadora de coincidencia de índice que te ayudará a visualizar de manera clara los resultados obtenidos. ¡Prepárate para mejorar tus habilidades en Excel y descubrir todo lo que esta fórmula puede hacer por ti!
En una publicación anterior, hablé del análisis de escenarios utilizando la función OFFSET. Hoy quiero presentarles dos fórmulas útiles, las funciones «Índice» y «Coincidencia», que en realidad se pueden combinar para crear una «súper» fórmula aún más útil, por así decirlo. Esta fórmula se puede utilizar en lugar de las funciones BUSCARH y BUSCARV comúnmente utilizadas en modelos financieros y es más útil cuando se considera información en el contexto de cosas como tablas de «comps» o análisis de sensibilidad.
PARA COINCIDIR
La función COINCIDIR devuelve la posición relativa de un elemento en una matriz o serie de datos que coincide con un valor específico en un orden específico. La sintaxis de la función COINCIDIR es:
=Coincidencia(valor_buscado,matriz_buscada,tipo_coincidencia)
A continuación se muestra un ejemplo de fórmula COINCIDIR en Excel. En este ejemplo, le hemos indicado a la fórmula COINCIDIR que busque en la celda B11 el valor «Preferido» de un conjunto de opciones capturadas en la serie de datos en las celdas A5 a A9. También especificamos el tipo de coincidencia «0» para indicar que estamos interesados en una coincidencia exacta. (1).
Recuerde: MATCH devuelve la posición del valor coincidente dentro de Lookup_Array, no el valor real en sí. En el siguiente caso, MATCH nos ha dicho que «Preferido» se encuentra en el 3aprox Posición (desde arriba) en el área seleccionada.
ÍNDICE
La función ÍNDICE se puede utilizar para devolver un valor real encontrado en una celda específica de una tabla o matriz seleccionando una fila y columna específicas en dicha tabla. La sintaxis de la función ÍNDICE es:
= ÍNDICE (matriz, núm_fila, núm_columna)
Piensa en jugar el juego Battleship. La matriz representa el paisaje del océano y el número de fila y el número de columna simplemente nos dan las coordenadas.
A continuación se muestra un ejemplo de fórmula ÍNDICE en Excel. En este ejemplo, le hemos indicado a la fórmula ÍNDICE que busque en una tabla definida por el rango de las columnas C a E y las filas 5 a 9. Al buscar en la tabla, la fórmula comienza buscando en la celda superior izquierda de la tabla (en este caso, la celda C5), donde la posición se definiría como Fila 1, Columna 1. En nuestro caso buscamos la celda que está en la intersección de las 3aprox fila y 3aprox columna de la tabla y desea devolver el valor que se encuentra en esa celda. La posición de la celda deseada es E7 y notará que la fórmula en B13 devolvió el valor correcto del 25% que se encontró en E7.
Un PARTIDO perfecto (ÍNDICE)
Ahora que hemos visto que las funciones COINCIDIR e ÍNDICE se usan por separado, ¡podemos combinar las dos fórmulas en una! Echemos otro vistazo a la tabla anterior, que contiene mucha información sobre las TIR para diferentes grupos de inversores y para diferentes años de salida de inversiones. Nuestra fórmula ÍNDICE en la celda B13 parece estar limitada por el hecho de que hemos codificado exactamente qué fila (3) y qué columna (3) queremos seleccionar para devolver un valor para los accionistas preferentes en el año de salida 2010 (25% ).
Para hacer que la fórmula ÍNDICE sea más dinámica, a continuación usamos la fórmula COINCIDIR para indicarle a la función ÍNDICE qué fila y qué columna seleccionar. La segunda parte de la fórmula ÍNDICE es indicarle a la fórmula qué fila seleccionar, y en lugar del número «3» ingresamos «MATCH(A13,$A$5:$A$9,0)». Si recuerda cómo funciona la fórmula COINCIDIR, le dice a Excel que devuelva la ubicación de un valor específico. En este caso, nuestro valor establecido está en la celda A13, «Preferido». Nuestro campo de búsqueda de “Preferido” es A$5: A$9 o la lista de varios inversores. Porque “Preferido” está en el 3er.aprox En la posición en la matriz, la fórmula COINCIDIR devuelve el resultado numérico «3» y le dice a la fórmula ÍNDICE que seleccione un valor en el 3aprox Fila de la matriz INDEX.
Se utiliza la misma técnica para indicarle a la fórmula ÍNDICE cómo seleccionar su número de columna. Nuestro resultado final es un valor de retorno del 25%, ¡la TIR correcta para los inversores preferentes en el año de salida de 2010!
Alcanzar resultados:
En el futuro, podemos simplemente ingresar un nuevo año en la celda B12 o una nueva clase de inversionista en la celda B13 para obtener nuestros resultados. Este es otro ejemplo de lo poderosa que puede ser una herramienta Excel. Le recomendamos que conozca las características adicionales de estas dos fórmulas simplemente presionando “F1” en Excel para buscar más información. ¡Estén atentos para obtener más consejos útiles sobre modelaje de Wall Street Prep!
(1) Match_type puede ser el número -1, 0 o 1 (el valor predeterminado es 1), donde «1» encuentra el valor más grande que es menor o igual al valor de búsqueda (lookup_array debe colocarse en orden ascendente). orden), «0» encuentra el primer valor que coincide exactamente con el valor de búsqueda y «-1» encuentra el valor más pequeño que es mayor o igual al valor de búsqueda (se debe incluir lookup_array). orden descendiente).
