Laboratorio

Práctica
Objetivos:
•
•

Filtros avanzados

•

Validación de entrada de datos

•

Empleo de las funciones:
BUSCARV, ESBLANCO, SUMAR.SI., ES.PAR, ES.IMPAR

•

Fórmulas Matriciales

•

Creación de gráficos y líneas de tendencia

•

Uso de la Grabadora de Macros

•

1ra Parte:

Uso de nombres de rango en formulas y opciones de menú

Uso de formatos condicionalesCreación del cuadro Movimientos de Almacén

1. Se va ha confeccionar el siguiente Cuadro de Movimientos de un Almacén de
Vehículos, para lo cual siga los pasos que se indican mas adelante.

2. En una hoja de cálculo de Excel escriba los títulos, códigos y nombres de los vehículos
del cuadro Existencia en Almacén. Luego dele formato a los títulos, y cópielos hacia
abajo, tal y como semuestra a continuación:

3. De nombre al siguiente rango de celdas:
A3:F7  TABLA
4. Seleccione el rango A12:A50 y con la ficha Datos/Validación de datos, permitir que
solo se ingresen datos según la lista ubicada en el rango A4:A7. Luego ingrese el
código de vehículos HP-101 en la celda A12 utilizando la lista desplegable de esta
celda.
5. En la celda B12 escriba la siguiente fórmula:=SI(ESBLANCO(A12),"",BUSCARV(A12,tabla,2,0))
6. Llene las celdas de ingreso y salida de vehículos en las celdas D12 y E12, con los datos
que se muestran en la imagen anterior.
7. En la celda F12 escriba la siguiente fórmula para calcular el saldo de almacén de cada
vehículo a medida que se registran sus movimientos.
=SUMAR.SI(B$12:B12,B12,D$12:D12)-SUMAR.SI(B$12:B12,B12,E$12:E12)
8. Para convertirel cuadro de movimientos de almacén en una tabla, haga lo siguiente:
 Seleccione el rango A11:F12 y presione Ctrl+Q, luego Aceptar.
 Seguidamente con la ficha Datos/Filtro desactive los botones de filtro de la fila 11.
 Y finalmente en la ficha Diseño elegir de la galería el Estilo de tabla medio 15.
9. Ahora ingrese los datos de movimientos de almacén de la fila 13, tal como se ve acontinuación:

10. Ahora vamos a dar nombre de rango a cada columna de datos de los movimientos de
almacén, para lo cual haga usted lo siguiente:
Seleccionar el rango A11:F13 y en la ficha
Fórmulas elegir el comando Crear desde la
selección: En la ventana de diálogo que aparece,
desactive la casilla Columna izquierda y luego
Aceptar.
11. Seguidamente ingrese los datos de movimientos
de losvehículos hasta la fila 35 tal y como se
muestra a continuación. Y cuando lo haga verá que solo deberá ingresar en cada fila el
código, la fecha, el ingreso y la salida. El nombre del vehículo y el saldo lo ingresará
automáticamente Excel.

12. Guarde el libro en disco dándole al archivo el nombre: Almacén de Vehículos.

2da Parte: Creación de la Tabla de Existencia y Gráfico deMovimientos por Vehículo
13. Para obtener la ultima fecha de movimiento de un vehículo, escriba la fórmula
matricial siguiente:
En C4

=MAX(SI(Vehiculo=B4,Fecha))

Cuando termine de escribir la formula anterior presione Ctrl+Shift+Enter para que
ingrese como una formula matricial. Esta fórmula se verá entonces en la barra de
formulas encerrada entre llaves, de la siguiente manera:{=MAX(SI(Vehiculo=B4,Fecha))}
14. Para obtener los saldos de almacén de cada vehículo, escriba las fórmulas siguientes:
En D4
En E4
En F4

=SUMAR.SI(Vehiculo;B4;Ingreso)
=SUMAR.SI(Vehiculo;B4;Salida)
=D4-E4

Copie luego estas 3 últimas fórmulas hacia abajo para los demás vehículos. La hoja
deberá verse entonces así.

15. Crear luego el gráfico de columnas tal como en la figura anterior. Para estoseleccione
los rangos B3:B7 y D3:F7 y luego conviértalos en un grafico de columnas utilizando
la ficha Insertar/Grafico de Columnas. (el gráfico no deberá pasar de la fila 14)
16. Guarde el libro en disco presionando las teclas: Ctrl+G.

3ra Parte: Obtención del Kardex de un vehículo
17. Obtenga una copia del rango A11:B12 y péguela a partir de la celda H15. Luego
copie también los títulos...