Práctica Nº 2: Microsoft Excel

1.     Vamos a elaborar la nómina semanal de los empleados de una empresa y para ello realice una hoja como la siguiente:

2.     Ahora, que ya introdujo los datos, establezca las siguientes características que Ud. aprendió en la práctica Nº1.

·         El título va en letra ARIAL tamaño 14, en negrita y centrado entre las columnas C, D y E. (Debe combinar las celdas C1, D1, E1)

·         El resto de los datos va en letra ARIAL tamaño 12.

·         Los encabezados van en negrita y centrados.

·         El ancho de las columnas es el siguiente:

A Þ 18                               D Þ 14

B Þ 11                               E Þ 15

C Þ 14                               F Þ 15

·         Las columnas B, D, E y F llevan formato numérico, con 2 posiciones decimales y el punto separador de miles.

·         Grabe la hoja como Practi2.

3.     Tenga en cuenta la siguiente información para realizar los cálculos

·         Todos los empleados trabajan 40 horas a la semana

·         La hora extra tiene un incremento de 18% respecto a la hora normal

·         A cada empleado se le descuenta un 15% de su sueldo por concepto de Paro forzoso y Política Habitacional

4.     Vamos a calcular el valor de la hora extra

·         Ubíquese en la celda D3 y escriba la siguiente fórmula  =(B3*0,18)+B3

Esta fórmula calcula el incremento que tiene la hora extra sobre el valor de la hora normal (B3*0,18) y se lo suma al valor de la hora normal (+B3). Observe que se usa la celda B3, porque es la que hace contiene el valor de la hora normal para el primer empleado.

·         Observe como en la celda D3 aparece la cantidad 1.034,27 que es el valor de la hora extra para el primer empleado.

·         Copie (con los comandos Copiar y Pegar) la fórmula al resto de los empleados.

NOTA: Observe que la fórmula es antecedida por el signo igual (=). En Excel, toda fórmula o función debe ir precedida por dicho signo, para que pueda ser diferenciada de un texto cualquiera.

5.     Vamos a calcular el pago bruto, que es lo que devenga el empleado semanalmente sin las deducciones.

·         Ubíquese en la celda E3 y escriba la siguiente fórmula =(40*B3)+(C3*D3). Observe que en dicha celda aparece 45.402,70.

Esta fórmula multiplica las 40 horas que normalmente trabaja el empleado por el valor de la hora normal (40*B3) y le suma lo que corresponde a las horas extras (C3*D3) o lo que es lo mismo, el Nº. de horas extras multiplicado por el valor de la hora extra previamente calculado.

·         Copie ahora la fórmula hacia los demás empleados.

6.     Ahora, calculemos el pago neto, que es el pago bruto menos las deducciones.

·         Ubíquese en la celda F3 y escriba =E3-(E3*0,15)

Observe que aparece 38592,30, cantidad que corresponde al sueldo bruto (E3) menos las deducciones, que equivalen al 15% de lo devengado (E3*0,15).

·         Copie ahora la fórmula hacia los demás empleados.

7.     Calcule el total de las columnas Pago Bruto y Pago Neto en las celdas E11 y F11 respectivamente. (Recuerde que lo aprendió en la Práctica Nº1)

8.     Vamos ahora a diferenciar lo que es una referencia absoluta de una referencia relativa

Una referencia identifica una celda o un rango de celdas en una hoja de cálculo e indica a Microsoft Excel en qué celdas debe buscar los valores o los datos que se desea utilizar en una fórmula. En otras palabras, una referencia es la dirección de la celda. Ejm: la celda F5 hace referencia a la columna F con la fila 5.

Cuando se crea una fórmula, normalmente las referencias de celda o de rango se basan en su posición relativa respecto a la celda que contiene la fórmula. En el cálculo anterior, la celda F3 contiene el pago neto del  empleado que está en la fila 3, observe que aún cuando la fórmula original era para el empleado de la fila 3 =E3-(E3*0,15), al copiarla, la misma cambió para el empleado de la fila 4 =E4-(E4*0,15), para el empleado de la fila 5 =E5-(E5*0,15), etc. Esto es lo que se denomina referencia relativa.

Si no desea que cambien las referencias cuando se copie una fórmula en una celda diferente, utilice una referencia absoluta. Para esto coloque un signo de dólar ($) delante de las partes de la referencia que no cambia. Veamos cómo es esto:

·         Inserte una nueva columna entre Pago Bruto y Pago Neto, es decir, que Pago Neto pasa a ser la columna G y la columna F la destinamos a la nueva columna.

·         Dé un formato porcentaje de dos decimales a la columna nueva (F).

·         Llámela %Pago Neto (celda F2). Este porcentaje pago neto no es más que la porción que el empleado aporta del monto total (sumatoria) de los pagos netos.

·         Ubíquese en F3 y escriba =G3/G11. Observe que aparece 16,77%. Esto significa, que el pago neto del empleado de la fila 3 representa un 16,77% de la suma total de todos los pagos netos.

·         Copie la fórmula al resto de los empleados y observe cómo aparece el siguiente mensaje: #¡DIV/0!, el cual nos indica que hay un error en la fórmula, pues al copiarla, la referencia relativa hace que para el empleado de la fila 4 sea =G4/G12, para la fila 5 sea =G5/G13 y así sucesivamente, el denominador de la fórmula avanza aún cuando G12, G13, G14, …,etc. no existan. Es aquí donde necesitamos la referencia absoluta para que la celda G11 permanezca fija en la fórmula.

·         Ubíquese en F3 y cambie la fórmula por la siguiente: =G3/$G$11. El signo dólar delante de la columna (G) y delante de la fila (11) nos garantiza que la celda G11 no cambiará a medida que copiamos la fórmula. Esto es referencia absoluta.

·         Ahora, copie la fórmula nuevamente.

9.     Vamos a colocar los datos dentro de un cuadro

·         Seleccione el siguiente rango: desde A2 hasta G9

·         Active el menú Inicio y seleccione la opción Formato de Celdas…

·         Seleccione la pestaña Bordes, y dentro de ésta, seleccione Contorno.

·         Seleccione ahora la pestaña Diseño, y escoja por color el siguiente:

·         Presione el botón Aceptar.

·         Observe cómo nuestros datos aparecen dentro de un cuadro color gris.

10.  Ya tenemos calculada la nómina, vamos ahora a ordenar los datos de los empleados por nombre alfabéticamente.

·         Seleccione el siguiente rango: desde A2 hasta G9.

·         Active el menú Datos y seleccione la opción Ordenar…

·         Aparecerá la ventana Ordenar. Verifique que en el recuadro Ordenar por aparezca Nombre, si no es así, selecciónelo de la lista.                   

·         Al final de la ventana aparece el siguiente mensaje Mi lista tiene encabezado. Asegúrese de que la opción esté activa, ya que en nuestro rango de datos seleccionado, incluimos los encabezados. En caso de no incluirlos, se debe desactivar la opción.

·         Presione el botón Aceptar y observe como la lista de empleados aparece ordenada ascendentemente.

NOTA: Es muy importante que al ordenar un rango de datos Ud. seleccione todas las columnas a ordenar, y no solamente la columna por donde se establece el orden. Ejemplo: En nuestro caso ordenamos por Nombre, pero al seleccionar los datos, no solo seleccionamos la columna de los Nombre sino todas las demás (Bs./Hora, Horas Extras…Pago Neto)

11.  Vamos a definir nuestra hoja de datos como una base de datos para buscar y extraer datos.

Una base de datos es una colección de datos relacionados acerca de un objeto en particular. Ej.: la base de datos de Alumnos contiene toda la información de los mismos, nombre, cédula, edad, etc.

En Microsoft Excel, puede utilizarse fácilmente la lista de datos definidos en la hoja de cálculo como una base de datos, para lo cual se establece lo siguiente:

a.      Las columnas son los campos de la base de datos y los encabezados de dichas columnas son los nombres de los campos.

b.     Cada fila de la lista es un registro en la base de datos.

El definir la hoja como una base de datos, agiliza la búsqueda y/o extracción de los datos. Cuando se define una base de datos en Excel, se tienen 3 áreas: la primera  es el rango de la lista, el cual corresponde a todos los datos incluyendo los encabezados (en nuestro caso es desde A2 hasta G9). La segunda es el rango de criterios, que es donde le decimos a Excel cuáles registros queremos buscar o extraer, es decir, donde establecemos nuestros criterios de búsqueda (en nuestro caso será desde A13 hasta G14) y, finalmente la tercera que es el rango de extracción, que es a donde Excel lleva los registros que cumplen con los criterios previamente establecidos (en nuestro caso lo estableceremos desde A16 hasta G30).

¿Cómo haremos la búsqueda?

·         Seleccione primero los encabezados (A2:G2) y cópielos hasta la fila 13 (A13:G13) y luego cópielos nuevamente pero hacia la fila 16 (A16:G16). Una vez copiados, éstos deben aparecer tanto en la fila 13 y en la 16.

·         Vamos a buscar en nuestra base de datos aquellos empleados que trabajaron más de 5 horas extras (esto es un criterio de búsqueda). Escriba entonces, en C14 lo siguiente: >5. Recuerde que sólo la fila 14 la usaremos para los criterios y por supuesto, en la columna que corresponda.

·         Seleccione ahora desde A2 hasta G9, para indicarle a Excel que ese es nuestro Rango de lista.

·         Active el Menú Datos. Seleccione la opción Avanzadas.

·         Aparecerá la ventana Filtro Avanzado. Como Acción verifique que esté activa Filtrar la lista sin moverla a otro lugar. Como rango de lista debe aparecer $A$2:$G$9 y como rango de criterios escriba $A$13:$G$14.

·         Presione el botón Aceptar. Observe que la lista quedó reducida a sólo aquellos empleados que cumplen con el criterio de búsqueda, es decir, que sólo Sonia Lozada y Vilma Toro trabajaron más de 5 horas extras.

¿Cómo haremos la extracción?

·         Seleccione ahora desde A2 hasta G9, para indicarle a Excel que ese es nuestro Rango de lista.

·         Active el Menú Datos. Seleccione la opción Avanzadas.

·         Aparecerá la ventana Filtro Avanzado. Como Acción verifique que esté activa Copiar a otro lugar. Como rango de lista debe aparecer $A$2:$G$9, como rango de criterios $A$13:$G$14 y en el recuadro Copiar a (que indica el área a donde extraeremos los datos) escriba $A$16:$G$30.

·         Presione el botón Aceptar. Observe que en el rango definido en Copiar a aparecieron los empleados que trabajaron más de 5 horas extras, es decir, Sonia Lozada y Vilma Toro.

Nota: Es muy importante que los rangos coincidan, es decir, si su rango de lista comienza en la columna A y termina en la columna G, el rango de criterios debe comenzar en la columna A y finalizar en la columna G y el rango de copiar a debe comenzar en la columna A y finalizar en la columna G, por supuesto que en filas diferentes.

Ejercicio:        Recuerde que los criterios de búsqueda debe establecerlos en la fila 14

·         Busque los empleados que tengan un Sueldo Neto mayor a 30.000 Bs.

·         Busque los empleados que trabajaron menos de 5 horas extras y cuyo sueldo neto no excede de 34.000 Bs.

·         Grabe su hoja de trabajo nuevamente.

12.  Vamos a abrir una nueva hoja de cálculo

·         Active el menú Archivo (Esquina superior izquierda, Botón Inicio).

·         Seleccione la opción Nuevo… y seleccione Libro en blanco  y presione el botón Crear.

·         Una nueva hoja aparecerá en pantalla

13.  Escriba la siguiente información en la nueva hoja: (Ajuste Ud. el ancho de las columnas)

·         La información anterior es un listado de compras de los clientes de una tienda. Actualmente dicha tienda tiene la siguiente promoción: Se les otorga el 25% de descuento a aquellos clientes cuya compra excede de 150000 y el 17% es caso contrario. Para calcular el descuento haremos uso de la siguiente función:

Función SI: es una función lógica que devuelve un valor si la condición especificada es VERDADERO y otro valor si dicho argumento es FALSO. Su sintaxis es la siguiente: =SI(Condición; Valor si es verdadero; Valor si es falso)

·         Ubíquese en la celda C3.

·         Escriba lo siguiente: =SI(B3>150000;B3*0,25;B3*0,17) y presione enter.

¯  ¯         

                                                                 Condición        Valor que tomará                    Valor que tomará

                                                                                        la celda C3 si la con-   la celda C3 si la con-

                                                                                        dición es verdadera     dición es falsa

·         Observe que en la celda C3 aparece 87500. ¿Cómo funciona la fórmula?: Excel compara la cantidad que está en la celda B3 con 150000 (350000>150000) = condición. Como la condición es verdadera se ejecuta el B3*0,25, es decir 350000*0,25 y ese resultado queda en la celda C3.  De esta manera calculamos el descuento para el primer cliente.

·         Copie la fórmula para el resto de los clientes

·         Calculemos ahora el Total a pagar, el cual es igual al Monto de compra (columna B) menos el descuento (columna C). Escriba en la celda D3 =B3-C3. Observe que aparece 262500. Copie la fórmula para el resto de los clientes.

14.  Grabe la hoja, active el menú Archivo  y seleccione la opción Cerrar. Presione el botón Inicio  y elija Apagar el sistema…  Espere el mensaje para apagar el equipo.

JSDV/jsdv

 Agradecimientos a la Prof. Jhoanelda Sánchez, autora del material presentado.