Centro Supérate

ADOC

TEMA:

Actividad Integral 1,

Excel 2010

PROFESOR:

Ricardo Fonseca

Integrantes:

Andrea Cecilia

Montoya Reyes

Beatriz Elizabeth

Moreno Mendoza

Leslie Evelyn Argueta

Morán

Roberto Isaac Sánchez

Mena

FECHA DE ENTREGA:

Lunes 22 de Abril de

2013

TERCER AÑO

Centro Supérate ADOC Actividad Integral

Excel 2010

1

Tabla de contenido Impresión ......................................................................................................................... 2

Plantillas y Proteccion de datos ................................................................................... 2

Filtros Automáticos ......................................................................................................... 3

Filtros Avanzados ............................................................................................................ 3

Subtotales ........................................................................................................................ 3

Funciones Condicionales .............................................................................................. 4

Auditoria de Fórmulas ................................................................................................... 5

Formato Condicional .................................................................................................... 5

Formato Condicional con Formula ............................................................................. 6

Validación de Datos ...................................................................................................... 6

Tablas y gráficos dinámicos ......................................................................................... 6

Búsquedas ....................................................................................................................... 7

Centro Supérate ADOC Actividad Integral

Excel 2010

2

Impresión Queremos que al imprimir nuestro proyecto, no salga la tabla cortada o solo

de un lado, sino que mantenga su tamaño con todas las filas y columnas.

Para ello, utilizaremos la opción “Ajustar hoja en la página”. De esta forma,

TODAS las filas y columnas se ajustarán al tamaño de la página. Incluiremos

también las opciones de centrado horizontal y verticalmente, que se

encuentran en configurar página, para darle un mejor aspecto a nuestro

trabajo.

Plantillas y Proteccion de datos Para comenzar, diseñamos nuestra tabla, incluyéndole los títulos rotulados, y

las formulas, para que estas puedan aplicarse cada vez que se introduzcan

datos en la tabla.

Usaremos las formulas siguientes:

Salario Diario

=E7/30

Salario Hora

=F7/G7

Horas Extras a Pagar

=I7*J7

Total de Percepciones

=E7+K7+L7

ISSS

=SI(M7<=685,71;M7*3%;20,57)

AFP

=M7*$V$7

FSV

=SI(M7<1155;N7*0,5%;"5,77")

Renta

=M7*10%

Total de deducciones

=SUMA(N7:R7)

Salario Neto:

=M7-S7Estas fórmulas quedarán como predeterminadas en nuestra

plantilla.

Es opcional Validar datos o Proteger la hoja; en nuestro caso solo aplicaremos

protección de hoja.

Seleccionamos los rangos que en los que queremos que se puedan introducir

datos. Desbloqueamos los rangos por medio del Inicializador de cuadro de

Centro Supérate ADOC Actividad Integral

Excel 2010

3

dialogo del grupo Número en la ficha Inicio. Luego, en la ficha Revisar,

procedemos a proteger la hoja.

Protegeremos también el libro al final, para que sus hojas no puedan eliminarse

ni agregarse. Incluiremos también la protección de Archivo, utilizando la

opción Guardar como, Opciones Generales. De esa forma nos aseguramos

que nuestro archivo tenga una seguridad muy completa, en el caso que

alguien quiera eliminar información.

Filtros Automáticos Para obtener Filtro Automático debemos ir al grupo Ordenar y Filtrar que se

encuentra en la ficha Datos, seleccionamos el filtro y automáticamente se

activan flechas en los títulos, que nos servirán para seleccionar que tipo de

información queremos que aparezca en la tabla.

Luego seleccionamos una flecha, en la cual nosotros podemos la condición

del filtro que queramos obtener, en este caso SUCURSAL deseleccionamos

todas las opciones y elegimos San Salvador clic en aceptar y podemos ver

que el filtrado se aplica.

Podemos usar varios filtros, ya que actúa como condición para que un dato

aparezca en una tabla, pero por el momento, solo lo utilizaremos con

SUCURSAL.

Filtros Avanzados Para obtener un Filtro Avanzado lo que debemos hacer es:

Copiar los títulos que deseamos filtrar y pegarlos en otro rango,

podemos pegar todos los títulos, o también podemos poner solo los

títulos que necesitemos. En este caso N° DE HORAS EXTRAS y SALARIO

NETO.

Luego debemos de ir al grupo Ordenar y Filtrar que se encuentra en la

ficha Datos y seleccionamos el comando Avanzadas, llenamos el

cuadro que se nos presenta.

En rango de lista se selecciona toda la tabla: $A$1:$U$36 , rango de

criterio se selecciona solo los títulos que pegamos anteriormente:

$A$38:$U$39y en copiar a: seleccionamos la celda en la cual deseamos

que estén los filtros: $A$41 , clic en aceptar y podemos observar los

resultados; El filtro se cumple.

Subtotales Para obtener una forma más resumida de los datos que tenemos, hemos

optado por los subtotales. Para ello hemos llevado el siguiente proceso:

Centro Supérate ADOC Actividad Integral

Excel 2010

4

1. Nos ubicamos en una celda dentro de la tabla, luego activamos el

comando subtotal ubicado en la ficha datos.

Después complementamos los datos que se requieren:

Para cada Cambio en: en esta casilla colocamos el término SUCURSAL, ya

que es así como esta ordenada nuestra tabla.

Función a usar: en este caso elegimos lo que es la SUMA.

Agregar subtotal a: en este último colocamos los valores de los que queremos

obtener un subtotal, el cual sería SALARIO NETO.

Así, Excel nos mostrará 3 niveles. TOTAL GENERAL, el TOTAL POR SUCURSALES, y

el TOTAL incluyendo todos los datos de la tabla.

Funciones Condicionales Para este tema se utilizó las funciones:

SI simple

SI anidada (Y)

CONTAR.SI

En el cual fueron efectuadas en la hoja llamada: Funciones Condicionales.

Por lo que para la función simple se utilizó la formula:

=SI(U7<250;"Mal desempeño";"Buen desempeño")

Con la que se decía que: si U7, es decir, el SALARIO NETO era menor a $250 el

resultado debía ser: “Mal desempeño” y si no se cumplía con lo pedido que el

resultado sería: “Buen desempeño”.

Y para la función anidada se utilizó la fórmula:

=SI(Y(D8="Vendedor";W8="Buen desempeño");"Aumento de Salario";"Sueldo

Normal")

Con la que se decía que si D8, el CARGO, era igual a Vendedor y W8, la

CONDICION DE EMPLEADO era “Buen desempeño” entonces el resultado sería

Aumento de salario y si no se cumplía ninguna de las condiciones el resultado

sería: Sueldo Normal.

Usamos la función CONTAR.SI con los préstamos:

=CONTAR.SI(S7:S41;100)

Si la persona pagaba un préstamo con $100, este se contaría. Dandonos el

número exacto de personas que pagan $100, 6.

Centro Supérate ADOC Actividad Integral

Excel 2010

5

En el otro caso, para las personas que pagan únicamente $50:

=CONTAR.SI(S7:S41;50)

El resultado a este conteo es 3. Ya que son las únicas personas que pagan esta

cantidad.

Auditoria de Fórmulas Cuando necesitamos analizar cuáles son las celdas que una formula toma en

cuenta para realizar un cálculo, usamos Rastrear precedentes. En nuestro caso

queremos rastrear los precedentes del TOTAL DE PERCEPCIONES.

Como podemos observar, el TOTAL DE PERCEPCIONES, suma las celdas

E7+K7+L7, por lo tanto, depende de ellas para obtener un resultado.

Si aplicamos Rastrear dependientes, tendremos:

Ya que el ISSS, AFP, FSV y RENTA necesitan al TOTAL DE PERCEPCIONES para

hacer sus cálculos, Excel los resalta. Sin ese dato, ninguna de las formulas

podría funcionar.

Formato Condicional Primeramente debemos de seleccionar el rango, en este caso, E7:E41.

Luego nos vamos al grupo Estilos que se encuentra en la ficha Inicio, clic

en el comando Formato Condicional, seleccionamos la opción Nueva

Regla y se nos presenta una serie de opciones la cual elegimos : Aplicar

formato únicamente a las celdas que contengan, luego seleccionamos

la opción: igual a, a la par se escribe la “condición” que deseamos

ponerle. Esta será igual a San Vicente en la columna de sucursales.

En formato modificamos como nosotros deseamos que quede el

Formato Condicional que será rojo claro con puntos blancos, clic en

aceptar .

Queremos resaltar también cuales empleados tienen mayores ingresos,

específicamente, aquellos cuyo total de percepciones es mayor a $450.

Centro Supérate ADOC Actividad Integral

Excel 2010

6

Seguimos el mismo proceso, cambiándole ahora el igual a, por mayor

que, 450, aplicaremos un color morado claro con puntos blancos, clic

en aceptar.

Igualmente, en nuestra empresa deseamos saber que empleado gana

menos de $300, y el formato condicional nos ayudará a hacerlo.

Indicándole que: Valor de celda <300 y se le aplicará un color de

relleno verde con puntos blancos, y clic en aceptar.

Formato Condicional con Formula Los resultados son los mismos que el formato condicional simple, pero

usando las funciones de Excel.

Estableceremos una regla que resalte a San Vicente de las sucursales, así

como en el ejercicio anterior.

Utilizaremos la función: =SI(E7=$E$7;VERDADERO;FALSO) para el rango

E7:E35, con un formato de relleno anaranjado con puntos blancos

=SI(J7=$J$7;VERDADERO;FALSO) si las HORAS EXTRAS son iguales a 4,

aplicándole relleno de trama color azul.

Validación de Datos Para este tema se trabajó con la hoja llamada: Validación de datos.

En el cual para la columna titulada: HORAS LABORADAS se dijo que se debía

Ingresar :únicamente datos igual a 8. Este proceso se indicó en configuración

de validación de datos, que se permitieran números enteros, iguales a 8, y

cuyo mensaje de entrada fuera : “Ingresar únicamente números igual a 8”

También para la columna Nº DE HORAS EXTRAS se realizó una validación de

datos con la condición de que solo se debían insertar números enteros entre: 2

y 8 .

Tablas y gráficos dinámicos Las tablas dinámicas son muy útiles a la hora de organizar datos para que sean

mucho más fáciles de analizar, por eso, la utilizaremos en este ejercicio.

Ubicándonos dentro de la tabla de datos que ya tenemos, activamos

el comando “Tabla dinámica”, ubicado en la ficha insertar. Luego

completamos lo que se nos pide y aceptamos

Posterior a esto aparecerá la tabla dinámica la cual deberá ser completada.

En este caso hemos ordenado nuestra tabla de la siguiente manera:

Campos de filtro de informe: SUCURSAL

Centro Supérate ADOC Actividad Integral

Excel 2010

7

Campos de fila: NOMBRE

Campos de columna: CARGO

Valores: SUMA de SALARIO NETO

Con los campos de la tabla ya completos, nos disponemos a crear el grafico,

basado en estos mismos datos, de manera que podamos representarlos. Para

esto simplemente activamos el comando “Gráfico Dinámico”, de la ficha

emergente “Opciones”. Seleccionamos el tipo de gráfico, en este caso

Columnas 3D y este automáticamente se creará.

Búsquedas Usando una hoja llamada: Lista de búsquedas se pudo obtener el resultado.

Primeramente se hizo otra hoja donde se usara la función llamada: Búsquedas.

Usando la columna llamada: Código del trabajador se pudo obtener el

nombre, el cargo y la sucursal.

Las fórmulas que se utilizaron respectivamente fueron:

Nombre: =BUSCARV(A2;'Lista de Busquedas'!$A$1:$T$37;2;FALSO)

Cargo: =BUSCARV(A2;'Lista de Busquedas'!$A$1:$T$37;3;FALSO)

Sucursal: =BUSCARV(A2;'Lista de Busquedas'!$A$1:$T$37;4;FALSO)