Almacén con Excel, 2

Detalle de las ubicaciones

La hoja de cálculo resultante del primer artículo permitía guardar las referencias que poseíamos y su cantidad, además de algunos otros datos adicionales.

En este artículo gestionaremos las ubicaciones del almacén.  Esto nos va a permitir saber dónde tenemos la mercancía.

Este es el layout (mapa) de nuestro almacén.  Tiene una entrada y una salida.  Hay tres filas de estanterías para palets, con tres alturas cada una.  Las filas A y B son accesibles desde el mismo pasillo.  La C está separada por una pared.

Objetivo

Controlar la ubicación de la mercancía.

Motivo

Layout del almacén

Queremos conocer dónde tenemos localizada la mercancía para poder hacer pedidos y reposiciones.

Desarrollo de diseño

Abrir el libro creado en el artículo anterior.

Desproteger la hoja Menú:Herramientas→Opción:Proteger→Desproteger hoja.

Seleccionar las celdas G1:L1 y poner el fondo amarillo.

Seleccionar las celdas G2:L2 y poner el fondo morado.

Seleccionar las celdas G3:H47 y poner el fondo naranja.

Seleccionar las celdas J3:J47 y poner el fondo amarillo.

Seleccionar las celdas L3:L47 y poner el fondo amarillo.

Seleccionar las celdas I3:I47 y Menú:Formato→Opción:Celdas→Pestaña:Proteger→ Bloqueada(desmarcar).

Seleccionar las celdas K3:K47 y Menú:Formato→Opción:Celdas→Pestaña:Proteger→ Bloqueada(desmarcar).

Colocar títulos

En G2 escribir “Ubicación”

En H2 escribir “Altura”

En I2 escribir “Ref”

En J2 escribir “Artículo”

En K2 escribir “Cantidad”

En L2 escribir “Peso”

Y ahora los datos de las ubicaciones y alturas.

En G3 escribir “A1”, en H3 escribir “1” (Ubicación y altura)

En G4 escribir “A1”, en H4 escribir “2” (Ubicación y altura)

En G5 escribir “A1”, en H5 escribir “3” (Ubicación y altura)

En G6 escribir “A2”, en H6 escribir “1” (Ubicación y altura)

En G7 escribir “A2”, en H7 escribir “2” (Ubicación y altura)

En G8 escribir “A2”, en H8 escribir “3” (Ubicación y altura)

…etc…

En G47 escribir “C5”, en H47 escribir “3” (Ubicación y altura)

Fórmulas en la fila de totales

En I1 escribir “=CONTARA(I2:I100)”.  Cuenta la cantidad de estanterías usadas.

En K1 escribir “=SUMA(K3:K47)”.  Suma la cantidad de unidades totales en el almacén.

Fórmula de la hoja

Ventana para crear la lista de validación

Seleccionar I3:I47 y Menú:Datos→Opción:Validación→Pestaña:Configuración;Permitir=Lista;Origen=”=$A$3:$A$102”;Omitir blancos(marcar);Celda con lista desplegable(marcar)

Con esto aparece un desplegable que nos permite seleccionar los artículos del maestro.

En J3 escribir “=BUSCARV(I3;$A$3:$B$102;2;0)”

Esta función busca el nombre del artículo cuya referencia está en la celda de su izquierda.

BuscarV es una función de búsqueda de Excel en vertical.  BuscarV(I3;$A$3:$B$102;2;0) significa que va a buscar el valor que hay en la celda I3 en la primera columna del rango de celdas $A$3:$B$102 y si la encuentra devuelve el valor de la columna 2. El último 0 indica que los valores que se buscan no están ordenados.  Si no encuentra nada muestra un error “#N/A”.

Copiar esta fórmula en I4:I47.  Al copiarla “BuscarV(I3…)”  cambiará por “BuscarV(I4…)” y sucesivos porque son referencias relativas. “$A$3:$B$102” lo mantendrá intacto porque se trata de referencias absolutas, al estar fijadas con el símbolo “$”.

En L3 escribir “=BUSCARV(I3;$A$3:$C$102;3;0)*K3”.  La función es similar a la anterior, pero ahora se selecciona un rango de tres columnas; A, B y C; y se coge el valor de la columna 3.  Copiar esta fórmula en L4:L47.

Formatear L3:L47 con dos decimales.

Proteger la hoja

Proteger hoja

Menú:Herramientas→Opción:Proteger→Proteger hoja→Proteger hoja y contenido de las celdas bloqueadas: Seleccionar celdas bloqueadas, Seleccionar celdas desbloqueadas, Ordenar, Usar Autofiltro.

Con esto ya tenemos controlada la mercancía que hay en cada ubicación.

Los datos

Ubicación Altura Ref Artículo Cantidad Peso
A1

1

RefAzul02 Azul

5

3,00

A1

2

RefRojo02 Rojo

4

3,00

A1

3

RefAmarillo02 Amarillo

6

3,00

A2

1

RefAzul01 Azul Oscuro

3

3,00

A2

2

RefAzul01 Azul Oscuro

6

3,00

A2

3

RefAzul01 Azul Oscuro

8

3,00

A3

1

RefRojo02 Rojo

10

3,00

A3

2

#N/A

#N/A

A3

3

#N/A

#N/A

A4

1

#N/A

#N/A

Lo siguiente

Algunas celdas muestran errores “#N/A” porque no han encontrado el valor relacionado.  Además la referencia al maestro “$A$3:$E$102” es un poco engorrosa y si la usamos muchas veces y luego queremos cambiarla será muy laborioso.

En la tabla de maestro de artículos, las cantidades no se suman automáticamente cuando depositamos mercancía en las ubicaciones, esto lo solucionaremos en los siguientes artículos.

Aquí puede encontrar el fichero Excel resultante.

Artículos relacionados:

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *