Esto se sale bastante de la temática habitual de este blog, pero como se trata de algo muy interesante y que le puede servir a mucha gente, lo voy a incluir.
La situación es algo así... Disponemos de una serie de celdas en Excel que nos marcan unos rangos numéricos (o de fechas, que viene a ser lo mismo) con un valor asociado que queremos utilizar según el tramo. Por ejemplo, una tabla de descuentos de producto según el importe de ventas como esta:
Como vemos en la parte superior tenemos una tabla de descuentos que va por tramos. Así, por ejemplo, si el cliente gasta 3.512€ le corresponderá un descuento del 5% (por estar en el segundo tramo), por lo que al introducir esta cantidad en la parte de abajo, se calculará automáticamente y le daremos el descuento y el precio final.
Parece sencillo ¿verdad?
Yo también lo creía, y supuse que habría alguna manera fácil y directa (alguna función de Excel) para poder conseguirlo en medio minuto. Me equivoqué :-S
Veamos cómo lograrlo...
En este momento podría dar simplemente la fórmula necesaria (que ya adelanto que es larga), pero es muy complicada de entender. Así que es mucho mejor ir por partes y ver uno a uno los conceptos que necesitamos conocer antes de llegar a la fórmula final.
Comencemos...
1.- Averiguar el número de fila en el que está una celda
Bien, lo primero que necesitaremos es saber cómo se averigua en Excel el número de fila de una celda dada. Es decir, una fórmula que si le pregunto por la celda B10 me diga que es la fila 10. Esta función es ROW (o FILA en español, yo uso Excel en inglés, pero es lo mismo). Se puede aplicar a rangos de celdas, devolviendo un rango (o matriz) de números de fila.
2.- Condicionales de múltiples celdas a la vez
Otra cosa que no todo el mundo sabe pero es muy útil, es que en Excel puedes hacer comparaciones de varias celdas a la vez en lugar de ir de una a una. De esta forma podemos obtener matrices de resultados de comparaciones dentro de fórmulas.
Por ejemplo si escribo dentro de una fórmula:
A2:A7<5
obtengo una matriz de TRUE o FALSE por cada celda del rango que he especificado (de la A2 a la A7) según si es menor que 5 o no lo es.
Esto, como veremos enseguida, es muy interesante para lo que nos proponemos.
3.- Convertir booleanos en enteros
Para lo que nosotros necesitamos, mejor que un booleano (verdadero o falso) necesitaremos tener ceros o unos como resultado de las comparaciones de matrices de celdas. Así que para convertirlo en un 0 (falso) o un 1 (verdadero), aunque tenemos varias formas, lo más rápido y directo es usar una doble negación, así:
--(A2:A7<5)
De este modo, el FALSO se convierte en un 0 (al negarlo lo convertimos en un número), y el VERDADERO, que por defecto se convierte en un -1, al negarlo dos veces, se queda como un +1 (dos veces menos es más, como sabemos de álgebra de la escuela).
4.- Multiplicar y sumar matrices de un solo golpe
Esta función, he de confesar que no la conocía en absoluto. Ni me sonaba, Me resultó de gran ayuda la página Chandoo.org, especializada en Excel, que lo explica de maravilla.
La función SUMPRODUCT (SUMAPRODUCTO en español) como su propio nombre indica, lo que hace es multiplicar los elementos correspondientes de las matrices que le pasemos (hasta 255 matrices diferentes, del mismo tamaño), y sumar dichos productos.
Por ejemplo, si tenemos las matrices de antes: {1, 2, 3} y {4, 5, 6}, lo que hace la función es devolvernos el resultado de esto:
1*4 + 2*5 + 3*6
Es decir, devuelve 32.
¡Buff! De momento la cosa pinta muy complicada. ¿Qué tiene que ver todo esto con lo que necesitamos?
Un poco de paciencia, ya falta muy poco :-)
5.- Obtener el valor de una celda a partir de su ubicación puesta en un texto
El último ingrediente que necesitamos es poder obtener el valor de una celda si tenemos su nombre en una cadena de texto. Por ejemplo “C5” o “$C$5” o cualquier otra forma de expresar la ubicación de una celda.
Para obtener el valor de una celda si tenemos su ubicación en texto usaremos la función INDIRECT (INDIRECTO en español). Se le pasa la celda y devuelve el valor, muy sencillo:
INDIRECT(“C5”)
devuelve en nuestro ejemplo el 15%.
La fórmula que usaremos ¡Por fin!
Vale. Ya hemos visto cada una de las piezas del puzle. Ahora ¿cómo las combinamos para obtener lo que necesitamos?
La fórmula utilizada en la celda C10 de la tabla del principio, y que nos devolverá el descuento necesario es la siguiente:
¡Bufff! Es algo “durilla” ¿verdad? Párate a pensarla a la luz de todo lo que hemos visto en los puntos anteriores...
Lo explico:
- El fragmento --(A2:A7<=C9) lo que hace es devolver un 1 o un 0 por cada celda de la primera fila (los rangos inferiores) según si ésta es inferior o igual a la cantidad comprada (celda C9). En el ejemplo de la figura devolvería {1,1,0,0,0,0}. Es decir, tenemos unos por cada rango inferior que nos encaje con la cantidad buscada.
- Del mismo modo exactamente, el fragmento --(B2:B7>=C9) devolverá un 1 o un 0 si cada celda de la segunda columna es superior o igual a la cantidad comprada, es decir, en este caso: {0,1,1,1,1,1}. O sea, tenemos un 1 por cada tramo superior que nos encaje, en su posición concreta.
- El fragmento ROW(A2:A7) lo que nos devuelve es el número de fila de las filas en las que están los rangos con los que queremos trabajar, es decir, en este caso: {2,3,4,5,6,7}.
- Bien, ahora viene lo bueno. Al aplicar SUMPRODUCT sobre las tres matrices anteriores, como la primera solo tiene 1 en los tramos inferiores que encajen, y la segunda tiene 1 en todos los tramos superiores que encajen, al multiplicarlas ambas (como 0 por lo que sea es 0) sólo nos quedará un 1 en el tramo en que coinciden ambas, o sea, ¡el que nos interesa!. En este caso {0,1,0,0,0,0}. Como además la tercera matriz tiene las posiciones de cada fila, al multiplicarla por la anterior, nos deja ceros en todas las posiciones menos en la de la fila que estamos buscando: {0,3,0,0,0,0}. ¡Justo lo que queríamos! Ya sabemos en qué tramo está la cifra de ventas.
- Ahora ya sólo queda obtener el valor de la tercera columna de esa fila con INDIRECT concatenándola a “C” (que es la columna), y multiplicarla por la cantidad comprada (celda C9) para obtener directamente el descuento. Es directo.
- Le he añadido un condicional para que si no hay nada escrito en la cantidad comprada (o está vacía) que no use la fórmula y deje la celda en blanco.
Aquí te dejo la Excel de ejemplo para que puedas descargarla y probarla.
¿A qué no era tan sencillo? :-)
¡Espero que te sea útil!