Almacén con Excel, 3

En este artículo vamos a solucionar algunos errores y mejorar la operativa de la hoja de cálculo que estamos usando.

Si definimos nombres para conjuntos de celdas podemos olvidarnos de las celdas que ocupan y usar siempre el nombre.  Además si luego deseamos ampliar el rango de celdas solo modificaremos la definición del nombre.

Desproteger la hoja.

Definir nombres para área de Maestro de artículos

Seleccionar las celdas A3:E102 y Menú:Insertar>Opción:Nombre>Opción:Definir…;Nombres del libro=”areaMaestro”;Se refiere a=”=Maestro!$A$3:$E$102”.

Definir nombre para área de Referencias

Seleccionar las celdas A3:A102 y Menú:Insertar>Opción:Nombre>Opción:Definir…;Nombres del libro=”areaReferencias”;Se refiere a=”=Maestro!$A$3:$A$102”.

Definir nombre para área de Referencias en Ubicaciones

Menú:Insertar>Opción:Nombre>Opción:Definir…;Nombres del libro=” areaRefUbicaciones”;Se refiere a=”=Maestro!$I$3:$I$47”.

Definir nombre para área de Cantidad en Ubicaciones

Menú:Insertar>Opción:Nombre>Opción:Definir…;Nombres del libro=” areaCantUbicaciones”;Se refiere a=”=Maestro!$K$3:$K$47”.

Sustituir la lista desplegable de referencias

Seleccionar I3:I47 y Menú:Datos>Opción:Validación;Origen=”=areaReferencias”.

Sustituir las fórmulas de búsqueda

En J3 está la fórmula “=BUSCARV(I3;$A$3:$B$102;2;0)”, la sustituiremos por “=BUSCARV(I3;areaMaestro;2;0)”.

Con esto hemos simplificado la escritura y es mucho más clara y sencilla de entender.

Quitar errores “#N/A”

Cuando no encuentra nada aparece un error muy molesto.

En J3 ponemos una nueva fórmula:

“=SI(ESERROR(BUSCARV(I3;areaMaestro;2;0));””;BUSCARV(I3;areaMaestro;2;0))”

Son funciones anidadas unas dentro de otras.

La exterior “=SI(Test;Verdadero;Falso)” verifica el resultado de la condición Test, si es verdad entonces muestra lo contenido en Verdadero y si es falso muestra lo contenido en Falso.

Test es otra función llamada ESERROR().  ESERROR(Test2) es verdad si Test2 produce errores y falso si Test2 es correcto.

Test2 es la función BuscarV.

Esto significa que si BuscarV es error, entonces Test es verdadero.

Si Test es verdadero, la función SI mostrará su segundo argumento, “”; o lo que es lo mismo, no mostrará nada.

Si Test es falso, significa que no hay errores, entonces muestra el resultado de la misma búsqueda con la que se comprobó.

Ahora se copia la fórmula a las celdas de J4:J47.  Y los errores han tenido que desaparecer.

Hacemos lo mismo con L3, la fórmula será:

“=SI(ESERROR(BUSCARV(I3;areaMaestro;3;0));””;BUSCARV(I3;areaMaestro;3;0)*K3)”

Sumar totales de artículos según la cantidad almacenada en las ubicaciones

En E3 escribir “=SUMAR.SI(areaRefUbicaciones;A3;areaCantUbicaciones)”

La función SUMAR.SI suma las celdas de “areaCantUbicaciones” si la celda que ocupa el mismo orden en “areaRefUbicaciones” es igual a “A3”, en este caso.

Alerta de rotura de Stock

Vamos a controlar la posible rotura de Stock, cuando alcancemos mínimos.

Insertar dos columna entre Volumen y Cantidad.  Las nuevas columnas serán E y F.

En E2 escribir “Mínimo” y en F2 “Alerta”.

Seleccionar las celdas F3:F102 y poner el fondo amarillo; además Menú:FormatoàOpción:CeldasàPestaña:Protegerà Bloqueada(desmarcar).

En F3 escribimos la fórmula que nos avisará de la posible rotura de stock:

“=SI(G3<E3;”¡ALERTA!”;””)”.  Si el valor de Cantidad (G3) es menor que el valor Mínimo (E3) entonces muestra “¡ALERTA!”, si no mostrar nada.

Proteger la hoja.

Conclusión

Por curiosidad puede mirar cómo han quedado definidos los nombre de las áreas, en especial la areaMaestro.  Observará que se ha actualizado a los nuevas columnas y no hemos tenido que modificar ninguna de las fórmulas creadas anteriormente.

Escribir algunos datos para probar el funcionamiento.

Lo siguiente

En el próximo artículo comenzaremos a preparar la hoja en la que pondremos los pedidos, para que luego nos ayude a seleccionar la mercancía y la ubicación.

Aquí puede encontrar el fichero Excel resultante.