Excel Avanzado

Excel está conformada por un conjunto de herramientas de propósito muy diverso, y muchas de ellas están particularmente relacionadas con la gestión empresarial, pero también suele ser usado desde otras áreas como la estadística, las matemáticas, la investigación de operaciones, el análisis de datos, cálculo y análisis financiero.:

– Crear un libro de trabajo, insertar hojas, eliminar hojas, abrir y guardar archivos.

– Uso del libro de trabajo con el Mouse y el teclado.– Usar los menús y algunos atajos de teclado.– Usar las funciones básicas

El uso de algunas funciones, hace necesario comprender el significado del cálculo que se esta realizando, esto resulta particularmente notorio en el caso de las fórmulas financieras.

El uso de un considerable número de funciones puede considerarse como un dominio intermedio de Excel.

El uso de tablas dinámicas para algunas instituciones es considerado como propio de Excel avanzado o de un dominio de Excel, sin embargo debido al uso generalizado y a la simplicidad con que estas son creadas, podemos señalar que en la actualidad esto correspondería más bien a un dominio intermedio de Excel. Similar situación ocurre con el uso de filtros.



Ejercicios

FORMULAS EN EXCEL: Cálculo de Pago.

En el ejercicio de Planilla de Pagos se debe de calcular, por cada uno de los empleados listados en la tabla sus respectivos descuentos y luego calcular el total a pagar.

Cada empleado tiene el mismo descuento del ISSS, el descuento del AFP es diferenciado: CONFIA es 7.5% y CRECER es 8.5%.  El cálculo del descuento sobre la renta es diferenciado también y se aplica según el intervalo de sueldo al que pertenece cada salario respectivo del empleado.

La tabla se complementó utilizando fórmulas matemáticas para hacer el cálculo del descuento aplicado a cada sueldo.

Para utilizar fórmulas en una celda en Excel se escribe, antes que nada, el símbolo igual “=”. Después del signo igual se procede a escribir la fórmula que se desee.

Las fórmulas a utilizar fueron:

ISSS:

=[CELDA CON VALOR DEL SUELDO]*0.05

AFP CONFÏA:

=[ CELDA CON VALOR DEL SUELDO]*0.075   

AFP CRECER:

=[ CELDA CON VALOR DEL SUELDO]*0.085

IMPUESTO SOBRE LA RENTA:

Sueldos de $0 a $500.

No se utilizó fórmula y se colocó un cero ya que esta categoría de salarios no tienen descuento.  

Sueldos de $501 hasta $1000.

=([ CELDA CON VALOR DEL SUELDO]*0.055)+50

Sueldos de $1001 hasta $1500.

=([ CELDA CON VALOR DEL SUELDO]*0.065)+100

Sueldos de $1501 en adelante.

=([ CELDA CON VALOR DEL SUELDO]*0.075)+150

Luego de completar los descuentos se sumaron los descuentos:

= [CELDA ISSS]+[CELDA RENTA]+[CELDA AFP]

Luego se calculó el total a pagar:

= [CELDA CON SUELDO TOTAL] – [CELDA CON TOTAL DE DESCUENTOS]

Luego se aplicó la fórmula de sumatoria “∑” al final de cada columna que contenía tanto los sueldos como los respectivos descuentos.

Finalmente se elaboró un cuadro comparativo para determinar la diferencia entre el sueldo nominal  el total a pagar después de aplicar los descuentos.

Para elaborar el gráfico se sombrean las celdas de forma ordenada así: Primero las columnas de Apellidos y Nombres, Teniendo la tecla “ctrl” presionada se sombrea, con el cursor del mouse, la columna de Salario y luego la columna de “Total a Pagar”.

Luego se va al menú “Insertar” -> “Gráficos”, y en el menú de gráficos elegimos el gráfico más apropiado, en el caso de éste ejercicio se eligió gráfico de barras en 3D.

 Filtros
Para Filtros avanzados en Excel los pasos a seguir son los siguientes:
En el documento en Excel donde tenemos la tabla de base de datos que se desea filtrar, agregamos una hoja más donde pondremos las tablas resultantes de nuestros filtros.
En la hoja que se acaba de agregar escribimos el encabezado del criterio que vamos a utilizar para filtrar: Nombre, Apellido, Nota, edad, etc.   Debajo de éste encabezado digitamos el criterio en sí con el que vamos a filtrar los datos. Es decir, el nombre en sí, el apellido, las notas, el departamento, la sucursal, el salario, la nota, la fecha de ingreso, etc.
Luego elegimos en el menú de Excel: DATOS-> Ordenar y filtrar -> Avanzadas.  Aquí nos aparecerá un cuadro, que nos da la opción de aplicar el filtrado en la misma hoja de la base de datos o copiar en otra ubicación el filtrado. La opción de copiar en otra ubicación es la recomendable y es la que se explica: El cuadro nos pide tres parámetros para filtrar: Primero que tabla de base de datos es a la que queremos aplicarle el filtro, para lo cual se coloca el cursor en el campo respectivo del cuadro y luego nos vamos a la hoja en donde tenemos la base de datos, la cual debemos seleccionarla toda.  Segundo nos pide el criterio a utilizar en el filtrado, aquí lo que hacemos es seleccionar las celdas descritas en el paso No. 2.  Tercero nos pide a donde queremos ubicar la tabla resultante del filtro, para lo que solo es necesario seleccionar una celda dentro de la hoja donde queremos que la tabla filtrada aparezca.

Tablas dinámicas

Las tablas dinámicas permiten realizar diferentes arreglos de tablas a partir de una tabla general de bases de datos.

El ejercicio presentado pide que a partir de una tabla general de ventas de una cadena de tiendas por departamentos en España, se elabore una tabla dinámica que presente la información por ciudad y por categoría de producto. Totalizando las ventas por ciudad y por categoría de producto. Aplicando filtro de forma de pago (crédito o contado) y de la zona del país a la que cada ciudad pertenece.

Para el desarrollo del ejercicio elegimos del menú: INSERTAR-> Insertar Tabla Dinámica. Luego nos aparecerá un cuadro donde debemos de indicar el rango de datos de la tabla base con la que queremos construir nuestra(s) tabla(s) dinámica(s). Luego tenemos la opción de crear la tabla en la misma hoja en la que se está actualmente o en otra hoja. Después de configurar lo anterior, Excel nos muestra el espacio donde quedará la tabla dinámica y a la par de ésta tenemos un cuadro donde se nos muestra cuatro categorías: Columnas, Filas, Filtros Y Sumatorias. Nosotros ahí podremos arrastrar y soltar cada uno de los encabezados de la tabla original.

Para el ejercicio solicitado lo que haremos es arrastrar a “Filas” la categoría de “Ciudad”, a “Columnas” arrastraremos la categoría de “Categoría de Producto”, arrastraremos a “Sumatoria” la categoría de “Ventas” y  a “Filtros”   arrastraremos la categoría de “Zona” y “Forma de Pago”.

Con esto queda creada la tabla dinámica que el ejercicio nos pide.