Muchas veces nos vemos obligados a construir fórmulas complejas, cuando con ciertos cambios menores en el diseño de la hoja podríamos realizar el mismo cálculo con fórmulas más sencillas y por lo tanto más eficientes.
Veamos un ejemplo, basado en una consulta de uno de mis lectores.
En una hoja de Excel queremos calcular el descuento que nos corresponde recibir de una lista de proveedores, basado en la cantidad comprada y el plazo de pago.
En un cuaderno de Excel hemos creado las siguientes tablas:
- en la Hoja1 la tabla donde calculamos los descuentos
- en la Hoja2 las tablas auxiliares
Nuestra primer tarea es calcular el importe por unidad (el rango D2:D6 en la Hoja1), lo cual hacemos fácilmente con BUSCARH. Primero creamos un nombre que contenga el rango de la tabla de proveedores
Proveedor: =Hoja2!$A$1:$E$2
La fórmula para calcular el precio es =BUSCARH(B2,proveedor,2,0)
Los valores del rango E2:E6, Importe pedido, son calculados por el producto =A2*D2 para la fila 2, =A3*D3 para fila 3 y así sucesivamente.
Ahora tenemos que calcular el descuento. Este es función del tamaño del pedido y de la forma de pago. Existen 7 combinaciones posibles:
Podemos crear una fórmula que combine la función SI con la función Y. Dado que hay 7 condiciones posibles, nuestra fórmula "anidaría" 6 funciones SI, algo así como
=SI(Y(A2>=100, A2<150,c2="contado"),10%,si(y(a2>=150, A2<200,c2="contado"),12%,si(a2>=200,C2="contado),14%,SI…lo mismo para 30 días…))))))
Como ven una fórmula complicada, con buenas posibilidades de contener errores difíciles de detectar.
Pero con pequeños cambios en el diseño de las hojas podemos simplificar en gran medida la tarea.
Primero modificamos la tabla de descuentos
Hemos cambiado el encabezamiento de las columnas B y C y hemos agregado una fila para tomar en cuenta los pedidos de menos de 100 unidades.
El cambio de los nombres tiene por objetivo permitirnos crear dos nombres que contengan los rangos
Tamaño_Pedido =Hoja2!$A$13:$A$16
Contado =Hoja2!$B$13:$B$16
desc_30_dias =Hoja2!$C$13:$C$16
Luego modificamos la tabla de forma de pago
Ahora construimos una fórmula mucho más concisa para obtener el descuento en la columna F:
=INDICE(INDIRECTO(C2),COINCIDIR(A2,Tamaño_Pedido))
La función INDICE busca en la matriz definida por INDIRECTO(C2) y da como resultado el valor que se encuentra en la fila definida por la función COINCIDIR(A2,Tamaño_Pedido)
En C2 tenemos las condiciones de pago, que coinciden en dos casos casos, contado y desc_30_dias, con los nombres que hemos definido.
Así, en la fila 2, COINCIDIR da como resultado "1" y por lo tanto INDICE da 0%.
Usamos INDIRECTO para que Excel interprete el valor de la celda C2 como referencia al rango definido por el nombre y no simplemente como texto.
Como podemos ver, en las celda E3, E4 y E6 obtenemos un resultado #REF. Esto se debe a que no hemos definido nombres que coincidan con el valor de la función INDIRECTO.
Una de las formas de solucionar este problema es introduciendo una función SI,
=SI(TIPO.DE.ERROR(INDIRECTO(C2))=4,0,INDICE(INDIRECTO(C2),COINCIDIR(A2,Tamaño_Pedido)))
Aquí debemos usar la función TIPO.DE.ERROR y no ESERROR, dado que al evaluar INDIRECTO(C2) fuera del contexto de la funcion INDICE, siempre nos dará un resultado #VALOR!.
TIPO.DE.ERROR nos permite definir el error que buscamos (4 = #REF, pueden consultar la ayuda en línea de Excel).
Otra posibilidad es ampliar la tabla de descuentos para contemplar todos los casos
Todo lo que debemos hacer es crear nombres para los rangos de las distintas condiciones de pago. No hay necesidad de modificar las fórmulas del rango F2:F6 en las Hoja1.
Esta solución tiene la ventaja que nuestra fórmula será más breve y que nuestro modelo está preparado si quisiéramos agregar descuentos para las otras condiciones de pago en el futuro.
Categorias: Funciones&Formulas_span>
Technorati Tags: Escel
great!!
ResponderBorrar