lunes, 28 de marzo de 2011

La función DESREF aplicada a búsquedas.

Hace algunos días me llegó un correo planteándome una cuestión sobre la forma de construir un condicional SI anidado:

...En la formula siguiente hay algo que está mal y se me escapa: error #¡VALOR!, ¿sabe alquien por qué? Un saludo
=SI(Y(A2<=100;C2="contado");$B$14;$C$14);SI(Y(A2>100;A2<150;C2="contado");$B$15;$C$15);SI(Y(A2>150;A2<150;C2="contado");$B$16;$C$16);SI(Y(A2>200;C2="contado");$B$17;$C$17)
Te adjunto el problema. En la columna F, tengo que hallar los dtos, que dependerán del tamaño del pedido y de si el pago es al contado o a 30 días exclusivamente...


Mi respuesta, después de ver el ejemplo, fue clara, no siempre la función SI condicional es la respuesta óptima. Mi recomendación fue el uso de la función DESREF, de la que ya hablé en esta entrada del blog.
En definitiva se trata de determinar un porcentaje de 'Descuento' condicionado a dos variables ('Cantidad de pedido' y 'Forma de pago'), siendo nuestras tablas de trabajo:


Sobre el rango A8:E12 deberemos obtener el porcentaje de descuento que le corresponde, descuento sólo válido para los pagos al contado o aplazados 30 días; esto es, para el resto de formas de pago (60 días o 90 días) no existe descuento, o lo que es lo mismo, el descuento es 0%.
En nuestra columna F, para cada registro de pedido añadiremos una función DESREF en su modalidad de búsqueda:
=DESREF(celda ancla; filas; columnas)
siendo nuestra 'celda ancla' la celda A8, celda superior izquierda de la tabla de datos. La clave es, por tanto, determinar correctamente el cruce de filas por columnas que devuelva el 'descuento' correcto. Esto lo conseguiremos con otra función ya conocida:
=COINCIDIR(valor buscado; en matriz; [coincidencia])
fundamentas en este caso es el argumento [coincidencia], ya que para la 'Cantidad de pedido', la primera de nuestras variables, se requiere una búsqueda por tramos, es decir, fila 9 cantidades entre 0 y 99, fila 10 cantidades entre 100 y 149, fila 11 cantidades entre 150 y 199, y por último, fila 9 cantidades superiores a 199.
La correcta asunción de fila la lograremos con un tipo de coincidencia 1; que encuentra el mayor valor que es menor o igual que el valor buscado. Es importante que los valores de la matriz de busqueda se encuentren ordenados de forma ascendente!!
=COINCIDIR(A2;$A$9:$A$12;1)
De manera similar realizaremos una búsqueda con la función COINCIDIR para la otra variable ('Forma de pago'), pero en esta ocasión el argumento 'coincidencia' deberá ser 0, i.e., coincidencia exacta:
=COINCIDIR(C2;$B$8:$E$8;0)
En ambos casos obtendremos un valor numérico que definirá la situación del porcentaje correspondiente; para la coincidencia de la variable 'Cantidad de pedido' tendremos el número de filas hacia abajo (ya que es un valor positivo) y para la variable 'Forma de pago' el número de columnas a la derecha (también un valor positivo) que nos debemos desplazar desde la celda ancla (celda A8) dentro de la tabla de porcentajes (rango A8:E12).


haz click en la imagen

3 comentarios:

  1. Excelforo, muchas gracias por tu aportación. Me ha costado un poco, pero al final lo he entendido.
    Gracias otra vez!!

    ResponderEliminar
  2. Buenas me dijeron que esto podia ser con la funcion DESREF y quisiera ayuda.
    Lo que deseo es trasponer filas pero de 4 en 4 a columnas por ejemplo en la hoja de excel tengo:
    Gil, Enrique
    Avenida Pino 111
    Alcorcón, Madrid
    (555) 128-549
    Cornejo, Cecilia
    Calle Roble 222
    Ibi, Alicante
    (555) 238-1845
    Bermejo, Antonio
    Avenida Cereza 333
    Illescas, Toledo
    (555) 581-4914

    Y quiero trasponer a:
    Gil, Enrique Avenida Pino 111 Alcorcón, Madrid (555) 128-549
    Cornejo, Cecilia Calle Roble 222 Ibi, Alicante (555) 238-1845
    Bermejo, Antonio Avenida Cereza 333 Illescas, Toledo (555) 581-4914

    ResponderEliminar
  3. Hola,
    bueno se puede intentar con la función DESREF, pero creo que tal cual tienes planteada tu tabla te interesa lo comentado días atrás.
    Revisaré tu propuesta.
    Slds

    ResponderEliminar

Nota: solo los miembros de este blog pueden publicar comentarios.