Referencias dinámicas en Excel con INDICE(INDEX) y COINCIDIR (MATCH)

En mi blog en inglés he mostrado un ejemplo del uso combinado de las funciones INDICE (INDEX) y COINCIDIR (MATCH). Aquí mostraré otro uso posible.

Uno de los muchachos del departamento contable vino a verme con este problema. En una hoja hay una lista que muestra las cantidades, los precios y el total vendido de una serie de productos. Los totales vendidos hay que pasarlos a otra hoja, pero creando una referencia dinámica. Lo que quiere decir, que si actualizan los datos en una hoja, los cambios se reflejen en la otra.
Hasta aquí no lograba entender dónde estaba el problema, hasta que mi amigo me explica que en la hoja original los datos están ordenados verticalmente, es decir una línea para cada producto. Pero en la otra hoja los datos tienen que aparecer ordenados horizontalmente. Y además, no todos los productos, sólo parte de ellos. Aclaremos que nuestra empresa tiene un catálogo de más de 17,000 productos.
OK, manos a la obra!
Supongamos que esta es una de las hojas

Y esta la otra hoja.



Queremos escribir una fórmula que introduzca los totales que aparecen en la hoja 1 en las celdas correspondientes en la hoja 2.

En primer lugar crearemos dos nombres (una explicación sobre uso de nombres (NAMES) en Excel puede leerse aquí):
Producto, que contiene al rango A2:A11 de la hoja 1
Total_Vendido, que contiene al rango C2:C11 de la hoja 1

Para crear los nombres señalamos el rango con el mouse, escribimos el nombre en el cuadro de nombre



y apretamos Enter.

Abrimos la hoja 2 y en la celda B2 escribimos esta fórmula

=INDICE(Total_Vendido,COINCIDIR(B1,Producto,0))

Copiamos esta fórmula al resto del rango (C2:F2) y ya está!

Esta fórmula funciona de la siguiente manera:
INDICE da como resultado el valor del rango Total_Vendido que se halla en la posición que del rango Producto que coincida con el valor de la celda B1 en la hoja 2.
Si cambiamos los nombres de los productos en la línea 1 de la hoja 2, los valores cambiaran de acuerdo, automáticamente.

Si te gustó esta nota anotala en del.icio.us

Technorati Tags: INDICE (INDEX), COINCIDIR (MATCH), Referencias Dinámicas

Categorías: Funciones&Formulas_, LOOKUPS_

Una pequeña aclaración

Acabo de recibir un comentario sobre la comparación entre dos listas en Excel. El ejemplo que allí aparece aparentemente no funciona. Pues, el ejemplo funciona, pero por favor prestar atención. Hasta hace una semana atrás no contaba con la version española del Office. Por lo tanto todos los ejemplos aparecían en la "versión inglesa" de Excel. En esa nota, por ejemplo, en lugar de CONTAR.SI explico como usar COUNTIF.
Así que por favor, si leen los ejemplos en las primeras notas, presten atención que las funciones aparecen en su versión inglesa.
En otro orden de cosas, si hago a tiempo empezaré hoy una serie de notas sobre referencias dinámicas a celdas.

Contar condicional con más de un criterio usando SUMAPRODUCTO

En mi nota anterior di una explicación básica sobre el funcionamiento de SUMAPRODUCTO. Veamos ahora un ejemplo de lo que se puede hacer con esta función.
Siguiendo con el ejemplo de la nota anterior, supongamos que queremos escribir una fórmula que nos permita saber cuántos productos hay entre dos precios. Por ejemplo, cuántos productos hay en la lista con un precio superior a los 50$ e inferior a los 80$.
En lugar de usar CONTAR.SI dos veces y restar entre los resultados (tres fórmulas), con SUMAPRODUCTO podemos hacerlo en una sola fórmula.
La fórmula en la celda C16 utiliza los valores en las celdas B13 y B14 como parámetros de los precios mínimos y máximos y da como resultado la cantidad de productos que cumplen con estas condiciones

Analicemos la fórmula en la celda C16:

=SUMAPRODUCTO(((B2:B11)>=B13)*((B2:B11)<=B14)*1)

Cuando Excel evalúa una fórmula del tipo A1=A2, puede dar como resultado o FALSO o VERDADERO (FALSE o TRUE). Estos resultados se pueden expresar como números 0 o 1 respectivamente si los multiplicamos por 1.

En nuestra fórmula la expresión (B2:B11)>=B13 da como resultado un vector/matriz que contiene los valores falso o verdadero. La segunda expresión (B2:B11)<=B14) hace lo mismo respecto al segundo parámetro.

Al multiplicar ambas expresiones obtenemos una matriz cuyos valores son o Falso o Verdadero. Esto se puede ver en esta tabla

La fórmula en el rango C2:C11 es =B2>=50; en el rango D2:D11 =B2<=90. EN el rango E2:E11 multiplicamos las celdas correspondientes de los rangos anteriores.

Podemos ver el resultado: 5 celdas con valor 1 y 5 celdas con valor 0. La suma de las 10 celdas en el rango E2:E11 da como resultado 5.

Todo esto hace nuestra fórmula con SUMAPRODUCTO en un solo paso!

Si te gustó esta nota anotala en del.icio.us

Technorati Tags: SUMAPRODUCTO, SUMPRODUCT, CONTAR.SI con más de un criterio

Categorías: Funciones&Formulas_