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.
Deja una respuesta