Hola, Hola mi Amig@,
¿Sabías que la función INDICE tiene un comportamiento bastante misterioso en su segundo argumento cuando queremos darle un comportamiento matricial por medio de constantes o rangos?
Hmmm no es muy claro, ¡Soy consciente de ello!
Pero, no te preocupes vamos a explorar algunos fundamentos de manera breve sobre INDICE y la combinación explosiva INDICE – COINCIDIR para posteriormente saber de qué se trata el comportamiento misterioso y como solventarlo con un truco, un: As Matricial Bajo la Manga INDICE.
Veamos de qué se trata esto y como se podría solucionar.
Explicación Básica de INDICE y COINCIDIR:
Función INDICE
INDICE: Devuelve un valor dada una intersección fila, columna en una matriz.
Matriz: Un rango de datos
Núm_Fila: Un número entero que indica la fila en la matriz
[Núm_Columna]: Un número entero que indica la columna en la matriz
– Ejemplo
Observa la siguiente tabla:
Dado el dato de Ciudad y Día (s) queremos retornar la tarifa; utilizando INDICE es bastante sencillo porque simplemente le indicamos la matriz, el número de fila y columna, que para este caso específico sería la fila donde se encuentra el elemento Barranquilla: fila 4, y el elemento Sábado: columna 3. La imagen a continuación muestra la fórmula:
¿Qué pasa si queremos que los números cambien dinámicamente? – Para ello COINCIDIR es nuestra aliada:
.
Función COINCIDIR
COINCIDIR: Devuelve la posición relativa de un elemento en una matriz mediante un número entero, es decir, «El 4 y 3 que necesitamos para los argumentos de INDICE».
Valor_Buscado: Cuál es el elemento que se le desea conocer la posición relativa en la matriz
Matriz_Buscada: Rango de datos, matriz donde se desea hacer la ubicación relativa
[Tipo_de_Coincidencia]: Corresponde al tipo de búsqueda: Exacto, ascendente o descendente.
– Ejemplo
Si extendemos nuestra función previa con COINCIDIR quedaría:
A continuación, visualicemos la siguiente situación: Supongamos que dada la tabla de comisiones y los dos porcentajes de cumplimiento de ventas más altos para el mes, queremos encontrar la suma de las comisiones respectivas.
¡El problema anterior lo podemos resolver de múltiples maneras! Pero aquí vamos a analizarlo tratando de pasarle los dos datos de porcentaje en el primer argumento de la función INDICE, con ello podremos ver el comportamiento misterioso que este tiene.
Es importante que entiendas los fundamentos de fórmulas y funciones matriciales para que lo descrito aquí tenga mayor sentido, en cuyo caso sino cuentas con las bases esenciales te recomiendo el siguiente vídeo: Funciones 03. Fórmulas y Funciones Matriciales. Excel 2013 Estudia el vídeo y regresa a este artículo más tarde para retomar y comprender 100% lo que sigue. ¡No Hagas trampa, solamente las personas que tengan conocimiento de fórmulas matriciales podrán apreciar la majestuosidad del truco más adelante! – Primero Vídeo, Luego Artículo =)
Sabemos a «Ojo» que los dos valores de % Cumplimiento de Ventas que se indican en las celdas H3 y H4 en la imagen anterior, corresponde a las filas 7 y 9 respectivamente (En La matriz: B7:E17), conociendo estos datos podemos tratar la siguiente fórmula (Véase la Barra de Fórmulas):
Sin embargo, si seleccionamos la fórmula y la evaluamos pulsando la tecla F9 vamos a poder ver que, solamente retorna el valor de la primera parte de la constante matricial, dicho de otro modo, devuelve la comisión para la fila 7, pero ignora por completo la comisión para la fila 9. ¡Misteriosos No! – Casi siempre damos por sentado que podemos tratar cualquier argumento de cualquier función de manera matricial, pero he aquí una excepción.
Tal vez nos falte encerrar la función INDICE dentro de SUMA y ver si realiza la suma de las dos comisiones:
Evaluando la fórmula anterior:
El problema persiste, la función INDICE no detecta matrices en su segundo argumento por lo que no podemos darle un tratamiento matricial, en lugar a ello debemos buscar otro tipo de solución, por ejemplo:
Si volvemos los argumentos Núm_Fila dinámicos con COICNDIR la fórmula anterior quedaría:
Resuelve el problema, pero qué pasaría si deseamos hacer lo anterior no con 2 valores sino con 50 para una tabla gigantesca. De seguro, se te ocurrirán varias alternativas, pero no sería genial poder hacerlo con el segundo argumento de la función INDICE
Además de buscar opciones creativas lo más probable que es sea una Mega-Fórmula, de hecho, la solución anterior es bastante larga para llevar acabo una tarea tan sencilla.
¿Estas List@? – Hay As Bajo la Manga para la función INDICE, con lo cual podemos jugar esta carta y darle comportamiento matricial.
As (Matricial) Bajo la Manga: INDICE
Ok, aquí viene la parte interesante, resulta que hay un truco un – As (Matricial) Bajo la Manga con INDICE – para solventar el inconveniente del segundo argumento de la función INDICE para que detecte matrices allí, tomemos las filas 7 y 9 y escribamos la siguiente fórmula (Véase la Barra de Fórmulas) :
Mira detenidamente como la constante matricial: {7,9} se encerró dentro de la siguiente estructura de funciones N(SI(1; Constante Matricial o Rango)) para posteriormente situarlo dentro del argumento Núm_Fila de INDICE. Procedamos a evaluar la fórmula para ver que sucede:
Whaaaaaaaaaaaaat!!! – Estupendo no?
Así es – resulta que encerrando la constante matricial dentro de N(SI(1; Constante Matricial o Rango)) hacemos que la función INDICE lo detecte como tal (Como Matriz) y así poder evaluar múltiples resultados, bien probemos añadiendo la función SUMA, así:
Pulsamos la combinación de teclas: Ctrl + Shift + Enter y miremos el resultado en la celda:
Wow! El resultado ahora es el deseado.
Por otra parte, sabemos que la función COINCIDIR maneja constantes matriciales y rangos sin ningún problema, por lo que podemos volver la constante {7,9} dinámica dependiendo de lo que se seleccione en las celdas H2 y H3, Así: COINCIDIR(H3:H4;B7:B17), esta parte de la función retornaría {7,9} y cambiaria dinámicamente de acuerdo a los porcentajes que se inserten en las H1 y H2, es decir podemos reajustar nuestra fórmula principal de la siguiente forma:
Y si pulsamos la combinación Ctrl + Shift + Enter veremos que funciona perfectamente.
¡Qué truco scado de la manga!: Nuestro As bajo la manga definitamente para aquellas situaciones donde la fórmula sea bien extensa y queramos acortarla un poco.
¿Qué te ha parecido este truco? Este As bajo la Manga: INDICE, Comenta y discutamos un poco diversas vías de solución
– Gracias por leer este artículo
– Eso es todo de parte mía, hasta la próxima oportunidad.