Universidad Nacional del Comahue –
Facultad de Economía y Administración
Procesamiento Electrónico de Datos
1ra y 2da Parte
Guía Práctica - TP de Planilla de Cálculos – 2do Cuatrimestre 2020
Realizado por: Lic. Mª Gladis Ferraro (Jefa Cátedra) y Lic. Pedro R. Landaveri (Ayte. Práctica)
I N D I C E
Pág.
Algunas consideraciones previas. Incluye servicio
de consultas
1
Pautas para resolver el T.P. y su presentación 2
Pautas de Cumplimiento Obligatorio (PCO) 3
Ejercicios Primera Parte
Nº Nombre
1A Referencias de celdas y Gráficos (torta y columna)
5
1B Referencias de celdas y Gráficos (lineal y col.3D)
6
2 Listado de Clientes
7
3 Listado de Empleados 8
4 Listado de Artículos 10
5 Planilla Liquidación de Sueldos 12
6 A Funciones BD del Ej. 4
15
6 B
Funciones BD del Ej. 3
6 C Funciones BD del Ej. 5
7 Factura de Venta 16
Ejercicios Segunda Parte
Nº Nombre
8 A Escenarios “A” del Ej. 4
19
8 B Escenarios “B” del Ej. 4
9 Escenarios y Gráficos del Ej. 5 21
10 Escenarios y Gráficos del Ej. 4 24
11 A
Búsqueda Objetivo: variar % x Año Antigüedad
27
11 B
Búsqueda Objetivo: variar Importe Base
Ejercicios Tercera Parte
Nº Nombre
En proceso de modificación
Universidad Nacional del Comahue
Facultad de Economía y Administración
Procesamiento Electrónico de Datos
1ra y 2da Parte
Guía Práctica - TP de Planilla de Cálculos – 2do Cuatrimestre 2020
Realizado por: Lic. Mª Gladis Ferraro (Jefa Cátedra) y Lic. Pedro R. Landaveri (Ayte. Práctica) Pág. 1 de 28.
IMPORTANTE
A partir de este T.P. los alumnos deben ir adquiriendo autonomía en la resolución del mismo en
cuanto a ´como resolver los ejercicios y que funciones que deben usar. Es importante, ante cual-
quier duda, que usen la Ayuda de Excel. Ver Servicio de consultas (más abajo en esta página)
Recomendamos lean atentamente estas cuatro primeras páginas.
Este Trabajo Práctico se divide en 3 partes según lo indicado en el Índice, y se realizará usando la
versión 2010 (o superior) de Excel. Esta Guía está confeccionada teniendo en cuenta esta versión.
Se han agregado algunas características propias de versiones superiores de Excel.
Algunas consideraciones previas:
Esta Cátedra considera que la Planilla de Cálculos es una herramienta fundamental para procesar
datos dentro de lo que es un Sistema de Información (ingreso datos proceso datos obtengo
información) donde ésta debe ser confiable y entregada en tiempo y forma, es decir:
• Confiable: la información obtenida debe ser exacta ya que luego, en el mundo laboral, ter-
mina siendo usada para hacer un simple asiento contable, o como para ser tomada en
cuenta a nivel gerencial para toma de decisiones. De ahí nuestro énfasis en el redondeo de
las operaciones matemáticas.
• Entregada en tiempo: damos una serie de pautas para mejorar vuestra eficiencia en el di-
seño de las planillas, y optimizar el armado de fórmulas y funciones entre otros temas. Lo
suelen llamar “tips” Pautas de Cumplimiento Obligatorio. De esta manera evitamos
que se aplique la Ley de Murphy.
• y forma: la presentación de la información debe ser clara y legible. Cosas como alinear por
la coma decimal los campos numéricos o que los gráficos sean de fácil lectura y compren-
sión, completos, etc. deben ser comprendidas y valoradas,
que en este Trabajo Práctico adquiere mayor transcendencia dado que varios de los ejercicios son
ejemplos de la realidad donde se tienen en cuenta aspectos contables e impositivos entre otros.
Servicio de consultas:
Esta Cátedra no está disponible las 24 hs, todos los días. Estamos en los horarios normales de
clases que se detallaron en la Presentación de la materia.
Excepcionalmente estamos en otros horarios.
Pero siempre hay urgencias Por eso aconsejamos que “googleen” cuando tienen alguna duda
esto les va a pasar siempre que usan Excel, por eso recomendamos dos sitios donde están
las Ayudas Oficiales de Microsoft para todas las versiones de Excel:
1.
Ayuda (y Aprendizaje) en Excel: https://support.office.com/es-es/excel
2.
Aprendizaje de Excel para Windows https://support.office.com/es-es/article/aprendi-
zaje-de-excel-para-windows-9bc05390-e94c-46af-a5b3-d7c22f6990bb?ui=es
El primero es para "googlear" dentro del sitio de Soporte Oficial de Microsoft sobre Excel.
El segundo es para aprender sobre Excel, donde disponen de tutoriales de las principales funciones
y herramientas de este aplicativo.
Sugerimos copiar estos links en los Marcadores de su navegador de Internet para tener siempre un
rápido acceso a los mismos.
Esto no invalida que accedan al material que hay en la PEDCO o que se encuentra en otros sitios
de internet (Excel Total, V&BA Excel, etc.).
Universidad Nacional del Comahue
Facultad de Economía y Administración
Procesamiento Electrónico de Datos
1ra y 2da Parte
Guía Práctica - TP de Planilla de Cálculos – 2do Cuatrimestre 2020
Realizado por: Lic. Mª Gladis Ferraro (Jefa Cátedra) y Lic. Pedro R. Landaveri (Ayte. Práctica) Pág. 2 de 28.
Recomendaciones:
Para trabajar en las Aulas de Informática, cuyas computadoras están "freezadas", se aconseja
que dispongan de un dispositivo con memoria flash: pendrive, mp3, celulares, Ipad, etc., para
“transportar” los archivos y poder continuar con el Trabajo Práctico en otras PCs.
También podrán usar el servicio de Internet que está disponible en estas Aulas lo cual les per-
mite bajar el archivo desde vuestra casilla de correo electrónico u otro sitio de internet, trabajar
en la PC del aula y luego se enviaran un e-mail con el archivo del Trabajo Práctico actualizado.
Hay que tener en cuenta que a veces este Servicio no está disponible.
Pautas para resolver el Trabajo Práctico y su presentación
Este trabajo está dividido en 3 partes. Para ello deberán:
1. Crear 2 archivos (Libros de Excel), cuyos nombres serán:
Nombre del
Primera y Segunda Parte: Tercera Parte:
archivo
NroG - AyN Integrantes - P01y02 NroG - AyN Integrantes - P03
siendo:
NroG: el número de Grupo (2 dígitos numéricos) asignado por esta cátedra.
AyN Integrantes: ApellidoyNombre1 - ApellidoyNombre2 de los integrantes del grupo.
Luego Excel agregará la extensión ".xlsx". ”33 - Pérez Juan – García Ana - P01y02.xlsx”
2. Para la 1ra. Parte: Ejercicios 1 a 7: Harán el envío del archivo según las pautas que se
definen más adelante.
3. Para la 2ra. Parte: Ejercicios 8 a 11: Deberán continuar trabajando sobre el mismo archivo
que usaron en la 1ra. Parte (
AyN - Trabajo Cátedra 01y02.xlsx
) sin esperar el resultado de la
corrección del 1er envío de la 1ra. Parte.
4. Para la 3ra. Parte: Ejercicios 12 al 16.
5. Los nombres de las Hojas deberán ser así: “Ej. nn” (siendo nn el número que corresponda)
o los que se indican en este Práctico. Cada Ejercicio irá en una hoja y en el caso de que se
solicite un “Resumen de Escenario”, esta Herramienta de Excel generará 1 hoja por esce-
nario, que colocarán a continuación de la hoja del Ejercicio base.
6. Archivos a descargar de la plataforma PEDCO para la realización de este Trabajo:
• TP Excel - Tablas a importar.zip
• "Anexo Guía Práctica Excel....pdf" correspondiente al cuatrimestre en curso.
• Apunte "Referencias de una celda" (Referencias relativas, absolutas y mixtas en Excel)
• Apunte "La Función BUSCARV".
• Apunte sobre Gráficos.
• Tablas dinámicas en Excel v.2007-2010.
7. Presentación del Trabajo Práctico:
• Se presentará a través de la plataforma PEDCO antes de la fecha de vencimiento.
• Existe la dirección de correo electrónico: pe[email protected] la cual usarán sólo en el
caso en el que no funcione la PEDCO en el momento del vencimiento.
• Si lo envían por e-mail, en el Subjet (Asunto) colocarán una leyenda identificadora lo
suficientemente clara del contenido del e-mail.
Universidad Nacional del Comahue
Facultad de Economía y Administración
Procesamiento Electrónico de Datos
1ra y 2da Parte
Guía Práctica - TP de Planilla de Cálculos – 2do Cuatrimestre 2020
Realizado por: Lic. Mª Gladis Ferraro (Jefa Cátedra) y Lic. Pedro R. Landaveri (Ayte. Práctica) Pág. 3 de 28.
Pautas de cumplimiento obligatorio (PCO):
Fueron creadas por esta Cátedra para optimizar el uso de Excel llamadas TIPs.
Su incumplimiento dará lugar a la no aprobación del/los ejercicio/s o del Trabajo Práctico.
1. Deberán requerir a los profesores de la parte práctica, si no están en la PEDCO, los archivos y
parámetros (alguno de ellos son individuales) que se utilizarán en este práctico, no aceptándose
archivos o parámetros de cuatrimestres anteriores al momento de la entrega del mismo.
2. No se aceptarán archivos cuyos nombres no se ajusten a lo indicado en los capítulos I y II como
así tampoco podrán usar funciones que no están previstas en esta Guía Práctica.
3. Importación de Datos: Para facilitar la ejecución de este TP se ha previsto obtener los datos
de las distintas tablas a partir de archivos en formato “txt”. Por ello es obligatorio ejecutar este
proceso que está en ficha Datos Obtener datos externos desde texto Importar ar-
chivo de Texto .... y los datos quedan en la hoja de Excel.
Los datos se obtendrán descomprimiendo el archivo "Tp Excel - Tablas a importar.zip" que
está en la plataforma
PEDCO
.
Cualquier otro proceso de importación, para este TP, se considera inválido.
Usuarios de Excel 2019: Deberán convertir la Tabla importada a Rango. clic botón derecho
el mouse sobre la Tabla clic en Tablas clic en Convertir en rango.
4. Referencias Relativas, Absolutas y Mixtas: Cuando en una celda se ingresa una función o se
ejecutan cálculos matemáticos, cuyo contenido luego es copiado a otras celdas, o rango de
celdas, contiguas deberán usar obligatoriamente esta propiedad de Excel.
5. Función SUMA: Cuando se suman celdas contiguas (horizontales o verticales) es obligatorio
el uso de esta función .=
SUMA
(desde:hasta;[ etc. ]).. Esta opción nos permite hacer más
dinámica a cualquier planilla. Expresiones como “=D29+E29+F29” o “=SUMA(D29;E29;F29)”
no son válidas, por ser ineficientes en la resolución de este TP. Ídem con “=SUMA(A1+C1)” y
similares, por ser redundantes. Leer el siguiente link:
https://support.office.com/es-es/article/suma-funci%C3%B3n-suma-043e1c7d-7726-4e80-
8f32-07b23e057f89
ir a “Prácticas recomendadas para SUMA” , donde se describen las características de es-
ta función y su incidencia en otras funciones, Con esto aprenderán a adoptar una metodolo-
gía de trabajo en Planilla de Cálculos al brindarle mayor seguridad y eficiencia en su tarea.
6. Función REDONDEAR: Sólo se la debe usar (uso obligatorio) en toda celda que contenga:
• una operación de división, o
• una multiplicación por un porcentaje o por un número con decimales, y
• algunas funciones cuyo resultado exceda los dos decimales y el mismo sea un campo de
valor monetario o contable.
En el caso de porcentajes, la cantidad de decimales depende del formato de exhibición:
Ejemplo: Resultado de una división =0,125682 y queremos mostrarlo en “Formato Porcen-
taje con 2 decimales” redondear a 4 decimales =0,1257 se exhibe =12,57%,
y si redondeamos a 2 decimales =0,13 se exhibe =13,00%.
En algunos ejercicios se indica en forma clara la cantidad de decimales a usar.
7. Uso de Constantes: En el caso de hallar en algunas celdas valores constantes en lugar de funcio-
nes y/o fórmulas matemáticas, dará lugar a la no aprobación del Ejercicio.
Igual criterio se adoptará si en una operación matemática se usa una constante en lugar de
usar la referencia a la celda que contiene a la misma.
Universidad Nacional del Comahue
Facultad de Economía y Administración
Procesamiento Electrónico de Datos
1ra y 2da Parte
Guía Práctica - TP de Planilla de Cálculos – 2do Cuatrimestre 2020
Realizado por: Lic. Mª Gladis Ferraro (Jefa Cátedra) y Lic. Pedro R. Landaveri (Ayte. Práctica) Pág. 4 de 28.
Quedan exceptuados:
• el número 1 cuando se hace referencia a un porcentaje (1 = 100%)
o cuando es consecuencia de aplicar factor común en una fórmula
= Precio + Precio
∗
%recargo = Precio
∗
(1 + %recargo)
• el indicador de columna en la función
BUSCARV
,
• la cantidad de decimales en la función
REDONDEAR
.
• cuando en la función SI, en la prueba lógica se compara el valor de una celda (o fórmula
o función) con el nro. 0 (cero) ó 1, o con ”” (vacío). Ídem si en Verdadero o Falso coloca-
mos estos valores. Ejemplo: =SI (J20=””; 0 ; ejecuto una fórmula o una función).
De persistir esta situación en varias hojas, se desaprobará el Trabajo Práctico.
8. Totales o Totales de control: Los ejercicios cuyos totales no coincidan con los que figuran en
esta Guía serán desaprobados. En caso de ser otros los totales, se les informará previamente.
Los Totales en los Escenarios, al ser personalizados, son distintos a los de esta Guía.
Estos Totales siempre contienen la función
SUMA
, excepto en el Resumen de Escenario.
9. Bordes y tramas (sombreados): No válido para los ejercicios 3 a 7. Deberán realizar todos los
que correspondan según los modelos de ejercicios que se exhiben más adelante, como así
también todo lo relativo a “Formato de celdas” (alineación, negritas, formato numérico, etc.).
En Resumen de Escenarios y en Gráficos dejar los colores originales que Excel asigna automáti-
camente. En esta Guía se modificaron, en algunos casos, colores y tramas para permitir una
mejor visualización usando escala de grises u otros colores.
10. Formatos numéricos A los campos que contienen:
a. números que representan importes deben darle un formato adecuado. Tienen dos opciones:
Ir a Ficha Inicio Cinta de Opciones “Números” clic en el ícono (Estilo Millares).
Opción recomendada: cuando un campo tiene un 0 (cero), Excel coloca un guion “-“.
Clic en el extremo inferior derecho de “Números” se abre el cuadro de diálogo “Formato
de Celdas” Número con 2 decimales, siempre activar opción “separador de miles”.
b. números que representan cantidades deben darle formato numérico de acuerdo a si son
enteros o si tienen decimales a veces deben activar la opción “separador de miles”
c. números que representan porcentajes el formato debe ser “Porcentaje con 2 decimales” ex-
cepto que se indique lo contrario.
Nota La Cátedra no aconseja el uso del signo $ en las celdas de importes porque se hace difícil
la lectura segura de los valores.
11. Gráficos: Deben ser similares (casi iguales) a los exhibidos en esta Guía, respetando los for-
matos numéricos y los mismos deben ser claros, precisos y de fácil lectura.
12. Función PRODUCTO: No usar esta función para realizar una multiplicación porque cuando uno
de los términos es una celda vacía o sin contenido numérico, el resultado es incierto.
13. Combinar celdas: Esta cátedra NO permite su uso. Sólo está permitida en títulos, siempre y
cuando el rango a combinar sea de celdas verticales. Ver el siguiente link:
http://jldexcelsp.blogspot.com.ar/2009/04/buenas-y-malas-practicas-en-excel.html
14. Por último, se les recuerda que los Trabajos Prácticos debe ser únicos para cada grupo.
Universidad Nacional del Comahue
Facultad de Economía y Administración
Procesamiento Electrónico de Datos
1ra y 2da Parte
Guía Práctica - TP de Planilla de Cálculos – 2do Cuatrimestre 2020
Realizado por: Lic. Mª Gladis Ferraro (Jefa Cátedra) y Lic. Pedro R. Landaveri (Ayte. Práctica) Pág. 5 de 28.
Ejercicio 1.- Referencias de celdas y Gráficos
Ejercicio 1A.- Confeccionar la tabla (Lista de Datos) que figura más abajo. No es “Tabla de Datos”.
Pasos a seguir:
1. En las celdas vacías, ingresar una sola fórmula y/o función que pueda ser copiada en todas
las filas, o en todas las columnas (según el caso).
2. Fórmulas y funciones a utilizar:
• IVA = Precio Mercadería Valor celda donde está el Impuesto. Redondear a 2 decimales.
• Subtotal = Precio Mercadería + IVA.
• Monto Venta = Subtotal
*
Cantidad.
• % Participación en las Ventas: nos indica cual es el % de Participación del Monto de Ventas
de un Artículo con respecto al Total del Monto de Venta. Redondear a 4 decimales.
• Totales: es la suma de las celdas de cada columna. Usar la función
SUMA
.
Impuesto
21%
Artículo
Precio
Mercadería
IVA Subtotal Cantidad
Monto Venta
% Particip. en
las Ventas
LAVARROPAS Común 337,20
10
LAVARROPAS Redondo 134,88
15
VENT. DE TECHO 3 Metal 59,57
14
VENT. DE TECHO 4 Madera
68,79
10
TELEVISOR 21" 483,32
5
AIRE ACONDICIONADO 786,80
4
Totales
392,82
2.263,38
5
8
1
5
.
101
,
84
100,00%
3. Tomando los montos de ventas realizar estos dos gráficos:
Para crear estos gráficos ver el apunte "Gráficos en Excel 2007". Colocarles un título acorde y res-
petar formato %
Universidad Nacional del Comahue
Facultad de Economía y Administración
Procesamiento Electrónico de Datos
1ra y 2da Parte
Guía Práctica - TP de Planilla de Cálculos – 2do Cuatrimestre 2020
Realizado por: Lic. Mª Gladis Ferraro (Jefa Cátedra) y Lic. Pedro R. Landaveri (Ayte. Práctica) Pág. 6 de 28.
Ejercicio 1B.- Confeccionar la tabla (Lista de Datos) que figura más abajo. No es “Tabla de Datos”.
Pasos a seguir:
1. En las celdas vacías, ingresar una sola fórmula y/o función que pueda ser copiada en todas
las filas, o en todas las columnas (según el caso).
2. Fórmulas y funciones a utilizar:
• Precio Venta (para c/Modelo) = Costo unidad + Costo unidad x
Δ
(%)
• Unidades vendidas = suma de las cantidades de unidades de los tres modelos.
• Ingreso por ventas = Cant Modelo1
*
Precio Modelo1 + ...
• Costo de las ventas = Cant Modelo1
*
Costo Modelo1 + ....
• Rentabilidad bruta = Ingreso por ventas – Costo de las ventas.
• Redondear a 2 decimales cuando corresponda.
Costo x unidad
Δ (%)
Precio Venta
Modelo1
75,53
39 %
Modelo2
108,71
35 %
Modelo3
138,76
28 %
Totales por trimestre
1° Trim 2° Trim 3° Trim 4° Trim Total Anual
Modelo 1
49
32
44
37
Modelo 2
38 25 35 28
Modelo 3
21 15 20 16
Cant Unidades vendidas
360
Ingreso por ventas
Costo de los productos
Rentabilidad bruta
12.364,02
3. Crear los siguientes gráficos:
Para crear estos gráficos ver el apunte "Gráficos en Excel 2007". Colocarles un título acorde, res-
petar formato numérico y el valor mínimo del eje "y"
Universidad Nacional del Comahue
Facultad de Economía y Administración
Procesamiento Electrónico de Datos
1ra y 2da Parte
Guía Práctica - TP de Planilla de Cálculos – 2do Cuatrimestre 2020
Realizado por: Lic. Mª Gladis Ferraro (Jefa Cátedra) y Lic. Pedro R. Landaveri (Ayte. Práctica) Pág. 7 de 28.
Ejercicio 2.- Listado de Clientes:
Confeccionar la Base de Datos de Clientes (BDdC). Los datos se importarán desde el archivo “Da-
tos Clientes.txt”. Para la importación seguir lo indicado en el punto 3 de las PCO. El CUIT es un
campo numérico con formato personalizado (máscara de edición) “##-########-#”.
Ejercicio 2.- .-
Listado de Clientes
Uso in-
terno Código
Razón Social Domicilio Localidad
Tipo
de IVA
CUIT LP
cli01
10001
Daniel Lombardi Av. Rivadavia 6250
Neuquén Capital CF 20-22641901-9
2
cli02
10002
Latorre Hermanos S.A Arenales 1625 Neuquén Capital RI 30-10021901-4
2
cli03
20025
Multi-Hogar S.R.L. Asmarl 478 Neuquén Capital RI 30-20236001-3
1
cli04
20031
Audio City S.R.L Av. Boedo 869 Gral. Roca RI 30-60032871-5
1
cli05
30125
José Luis Ramírez Av. San Martín 360 Neuquén Capital CF 20-11640790-6
2
cli06
21453
Dante García El Huemul 34 Cipolletti CF 20-10660208-6
2
cli07
54789
Smartmacri S.A Láinez 78 Plottier RI 30-45871239-2
1
cli08
10020
Pérez y Asociados S.A. Buenos Aires 1400 Neuquén Capital RI 30-62548752-2
1
cli09
10021
Técnica Lalo S.A. Los Tilos 223 Cinco Saltos RI 30-58623100-0
1
cli10
10022
Julián Hnos. S.A. Las Heras 123 Neuquén Capital RI 30-23154803-9
1
cli11
20045
Rivas S.A Mitre 2560 Cipolletti RI 30-45700123-7
1
cli12
30014
Rodolfo Fritz El Palmar 496 Plottier CF 20-98741245-9
2
cli13
10030
Julio César Trivia Salta 12 Plottier CF 30-98120000-2
2
cli14
20012
Lo de Cacho S.A. Palacios 236 Neuquén Capital RI 30-87452114-9
2
cli15
12154
Juanito López Av. Argentina 123 Cipolletti CF 20-21100014-1
1
Nota: Este Listado no debe ser modificado ni alterado su ordenamiento bajo ninguna circunstancia,
ni tampoco ser copiado en otra hoja, excepto en el Ejercicio 14.Se le puede agregar columnas.
- - - - - - - -
Proceso alternativo para insertar Plantillas
Debido a incompatibilidades entre Excel 2007 (o 2010) y las versiones 2013, 2016 y 2019, y
hasta que se resuelvan las mismas, se ha decidido el siguiente procedimiento:
1. Una vez realizado los ejercicios n° 1 y 2, enviarán al correo pedfaea3@gmail.com el
archivo correspondiente.
El mismo debe tener el nombre que se indica en el Punto1 – Pautas para resolver el
T.P. ….. (Ver pág. 2 de esta Guía).
2. La Cátedra, en un lapso que no excederá las 24 horas hábiles, les devolverá el archivo con
las Plantillas necesarias para continuar resolviendo la primera parte del Trabajo Práctico.
3. Se les recuerda que no se aceptan Plantillas que no sean las que les enviamos a través
de este procedimiento.
4. Quedan anulados los puntos 1° de los Ejercicios 3, 4, 5 y 7. A estos ejercicios no se les
aplica el punto 9 de las PCO.
5. En estas plantillas no se deben modificar: ancho y alto de columnas, las Opciones de
Excel (excepto en los ejs. 11), los bordes y colores de las celdas.
Universidad Nacional del Comahue
Facultad de Economía y Administración
Procesamiento Electrónico de Datos
1ra y 2da Parte
Guía Práctica - TP de Planilla de Cálculos – 2do Cuatrimestre 2020
Realizado por: Lic. Mª Gladis Ferraro (Jefa Cátedra) y Lic. Pedro R. Landaveri (Ayte. Práctica) Pág. 8 de 28.
Ejercicio 3.- Listado de Empleados
(Ver Modelo en la pág. siguiente).
1. Insertar la plantilla “TP Excel - Listado de Empleados.xltx” en el libro de Excel según se explica
en Pág. 1.. del Anexo Guía Práctica (“Trabajar con Plantillas Pasos a Seguir”).
2. Darle el formato adecuado a todas las columnas. El CUIL es un campo numérico con formato per-
sonalizado (máscara de edición) “##-########-#”. Ídem con el Nro. de Caja de Ahorros (###-#).
3. El campo “Fecha” contiene la fecha actual (función
HOY
()).
4. Cargar los valores del Importe Base (Ver Modelo en pág. sigte.)
5. Incorporar tres tablas auxiliares:
BANCOS, SECTORES
y
TAREAS DESEMPEÑADAS
cuyos datos
(ver más abajo) se importarán desde el archivo “Datos Tablas varias.txt”. Para su importación
seguir lo indicado en el punto 3 de las PCO. Ubicarlas a la derecha de la Base de Datos ampliada.
6. Completar la Base de Datos de Empleados (BDdE). agregar las funciones que correspondan
en las siguientes columnas:
a. Antigüedad: son los años desde “Fecha Ingreso” hasta el día actual.
Utilizar la función
SIFECHA.
Sintaxis =
SIFECHA
(
fecha menor;fecha mayor;“y”
).
se obtiene los años de antigüedad.
No utilizar la función
AÑOS
por no ser exacta la resta.
b. Coef. Sueldo Básico: Es un coeficiente que tiene cada Función/puesto de trabajo y se en-
cuentra en la “
TABLA de FUNCIONES DESEMPEÑADAS
”. Usar la función
BUSCARV.
c. Importe x Esposa: Se paga el “Importe Base” que corresponde si el campo Esposa es = 1.
d. Importe x Hijos: Se paga el “Importe Base” que corresponde por cada hijo.
7. Calcular los totales de las siete columnas de la Base de Datos. (Ver Modelo en pág. sigte.)
8. En la parte inferior izquierda de la Base de Datos:
Calcular: ♦ Cantidad total de empleados.
♦ Cant. de empleados con días ausentes. Usar la función
CONTAR
♦ Cant. de empleados que cobran: Adicional por función. o
CONTARA
♦ Asig. Familiar x esposa.
♦ Asig. Familiar x hijo
Tablas auxiliares: Las siguientes tablas se importarán desde el archivo “Datos Tablas varias.txt”.
Insertarlas a la derecha de la BDdE.
TABLA de BANCOS
TABLA de FUNCIONES DESEMPEÑADAS
Código
Nombre Largo Nombre corto
Código Función Coef. Básico
1
Banco Pcia. Neuquén S.A.
BPN
1
Gerente
4,75
2
Banco Nación Argentina
BNA
2
Supervisor
3,35
3
Banco Credicoop
Credicoop
3
Técnico instalador
1,70
4
Banco de la Plaza
De la Plaza
4
Técnico reparador
1,80
5
Vendedor
1,80
TABLA de SECTORES
6 Secretaria 1,50
Código
Nombre Largo
Nombre corto
7
Ayte técnico instalador
1,00
1
Administración
Admin
8
Ayte técnico reparador
1,10
2
Venta de artículos
Venta
9
Cobranzas
1,35
3
Servicio Post venta
Servicio
10
Contable
2,00
4
Logística y Mantenimiento
Log&Man
11
Embalaje
1,35
Existe una cuarta tabla "TABLA DE % FONDO
ANTICRISIS" que deberán mover al Área de Trabajo
de la Hoja del Ejercicio 5 a partir de la celda Z4.
12
Facturación
1,80
13 Cadete 1,10
14
Chofer
2,00
ANULADO. Ver en Pág. 7 el proceso alternativo de inserción de Plantillas
Universidad Nacional del Comahue
Facultad de Economía y Administración
Procesamiento Electrónico de Datos
1ra y 2da Parte
Guía Práctica - TP de Planilla de Cálculos – 2do Cuatrimestre 2020
Realizado por: Lic. Mª Gladis Ferraro (Jefa Cátedra) y Lic. Pedro R. Landaveri (Ayte. Práctica) Pág. 9 de 28.
Ejercicio 3.- Listado de Empleados (
Los datos son a titulo ilustrativo
)
Fecha
27/05/2015
Pueden cambiar
Importes Base
Grupo Familiar
80,00
100,00
CUIL
Le-
gajo
Apellido Nombre
Fecha
Ingreso
Cód
Sector
Cód.
Función
Ausen-
cias
% Adic.x
Función
Esposa
Cant.
Hijos
Cód
Banco
Cuenta
Antigüe-
dad
Coef.Sdo
Básico
Importe
x.Esposa
Importe x
Hijos
20-10234567-4
1
Cerruti Julio Andrés
02/01/1992
3 4 1
1 3 567-4
20-20345875-5
2
Jara Raúl Ezequiel
01/04/1992
2 11
1 1 4 875-5
20-16678765-4
3
López Carlos Hernán
01/04/1992
3 8 5
1 765-4
20-20567345-9
4
Castillo Marcos Alex
21/06/1992
1 12 3
1 5 2 345-9
20-14545689-5
5
Méndez Pablo Cesar
02/06/1993
1 9
1 2 3 689-5
20-19923456-1
6
Morales
Luciano Martín
26/10/1994
3 3 3
1 456-1
20-28456852-4
28
Garrido Roque Alberto
12/11/2013
3 4 5
1 562-1
27-31684259-1
29
Cayupan Ana Cecilia 02/01/2014
3 7 6
1 4 2 658-7
27-32951486-2
30 Rotelli Josefina 02/01/2014
1 6 4
60%
3 852-4
63
15 31
Totales
51,85 1.200,00
3.100,00
Cantidad. de empleados: en la empresa
30
Totales por Sector
con ausencias
18
Criterios x Sector →
Empleados que cobran: Adic x Función
9
Asignación x Esposa
15
Nombre Sector
Totales
Asignación x hijo
13
Días Ausentes
63
Cantidad de Empleados
:
Por Sector
30
Con Ausencias
18
:
Empleados que cobran Adicional x Función
9
Asignac. x Esposa
15
Asignación x hijo
13
Prom. Antigüedad
-
-
-
Ejercicio 6-B
Será informado
vía PEDCO
Calcular los totales
usando las funciones
de Base de Datos que
correspondan
Universidad Nacional del Comahue
Facultad de Economía y Administración
Procesamiento Electrónico de Datos
1ra y 2da Parte
Guía Práctica - TP de Planilla de Cálculos – 2do Cuatrimestre 2020
Realizado por: Lic. Mª Gladis Ferraro (Jefa Cátedra) y Lic. Pedro R. Landaveri (Ayte. Práctica) Pág. 10 de 28.
Ejercicio 4.- Listado de Artículos
: (Ver Modelo en página siguiente)
1. Insertar la plantilla “TP Excel - Listado de Artículos.xltx” en el libro de Excel según se explica en
Pág. 1.. del Anexo Guía Práctica (“Trabajar con Plantillas Pasos a Seguir”).
2. Parámetros a Ingresar: los porcentajes del Margen de Utilidad y del Recargo.
3. Completar las siguientes columnas:
a. Alícuota IVA: Usar la función SI anidada para obtener el % usando los datos de la “TABLA
DE PAÍSES”. No usar constantes.
b. Gastos de Aduana (%): Ídem al punto anterior (3.a).
c. Gastos de Aduana y otros ($) = "Costo de Origen"
*
"% de Gastos de Aduana".
d. Costo Mercadería = “Costo en Origen”
+
“Gastos Aduana y otros ($)”.
e. Precio Especial: Se le adiciona un Margen de Utilidad al Costo de la Mercadería.
f. Precio Normal: Tiene un Recargo sobre el Precio Especial.
Obligatorio: En “e” deberán confeccionar 1 fórmula que sirva también para resolver el punto “f”.
4. Para verificar si los cálculos del punto anterior son correctos, deberán sumar las columnas que
correspondan cuyos totales deben coincidir con los que figuran en “Totales para Control”.
5. Completar la plantilla:
a. Ingresar Cód. Valorización: Ingresar un número entero (0, 1 ó 2) que será sometido a una
“Validación de Datos (por Lista)” tal como se explica en Pág. 2. del Anexo Guía Práctica. Este
número se utilizará para saber que celdas se usan para el cálculo del Stock Valorizado.
b. Stock Valorizado: Calcularlo para cada artículo Diseñar una función que sirva para los
tres códigos del punto anterior. Al final de la columna exhibir el importe del Total Stock Va-
lorizado.
c. Constancia de Ejecución: Realizar los cálculos para Cód. de Valorización= 0 (Costo Merca-
dería) Copiar los valores del Stock Valorizado en la columna que corresponda utilizando
Pegado Especial.
El Pegado especial” se ejecutará 2 veces: 1º vez ”Valores” y 2ª vez ”Formato"
Repetir el proceso para los Cód. de Valorización igual a 1 e igual a 2.
ANULADO. Ver en Pág. 7 el proceso alternativo de inserción de Plantillas
Universidad Nacional del Comahue
Facultad de Economía y Administración
Procesamiento Electrónico de Datos
1ra y 2da Parte
Guía Práctica - TP de Planilla de Cálculos – 2do Cuatrimestre 2020
Realizado por: Lic. Mª Gladis Ferraro (Jefa Cátedra) y Lic. Pedro R. Landaveri (Ayte. Práctica) Pág. 11 de 28.
Ejercicio 4. - Listado de Artículos
1 2 3 4 5 6 7 8 9 10 11 12 13 N (O) (P)
(Q)
Gastos Aduana
Margen
utilidad
Recargo
Ingresar Cód
Valorización
Constancia de Ejecución
y otros
15%
20%
0 1 2
Uso In-
terno
Código
articulo
Articulo Descripción
Cód.
Origen
Costo en
Origen
Alícuota
IVA
% $
Costo Mer-
cadería
Precio
Especial
Precio
Normal
Unid.
Stock
Stock
Valorizado
Costo Mer-
cadería
Precio
Especial
Precio
Normal
101 1243
LAVARROPAS Comú
Modelo V50 1 337,20
10
102 1244
LAVARROPAS Redo
Semiautomático 1 134,88
15
103 1245
LAVARROPAS Autom
12 Programas 1 618,20
12
201 3245
VENT. DE TECHO 3
Mod T-
echo 3 Pala
3 58,57
14
202 3246
VENT. DE TECHO 3
Mod T-
echo 3 Pala
3 59,82
12
……
…………
……….
…
……
…
……
…………
……….
…
……
…
703 3547 RADIO RELOJ
Digital con Despert
3 6,74
30
704 9987 IMPRESORA 720X720 DPI 2 249,53
4
705 10245
FAX Mod. TS83 2 432,74
2
706 5587 PLANCHA SECA Con rociador 3 46,08
20
707 5588 PLANCHA SECA
Base de acero inox
3 25,85
16
Totales de control
3.509,61
12.324,89
14.173,64
17.008,37
289
101.252,65
116.440,57
139.728,68
TABLA DE PAÍSES
TABLA DE TOTALES POR PAÍS
Cód.
Origen
País
% Gastos
Alícuota
IVA
CRITERIOS por
1 Argentina
25%
10,50%
Cód. Origen
2 Mercosur 30% 15,00%
País
3 China 100% 21,00%
% Gastos Aduana
Tot.Control
4 Otros 50% 18,00%
Cant. de Artículos
28
Cant. unid en Stock
289
Stock Valorizado
Copiar y Pegado Especial (valores y
formatos para los 3 cálculos s/cód. Va-
lorización).
Calcular los valores de las 12 celdas usando las fun-
ciones de Base de Datos que correspondan
Ingresar
:
0 por Costo Mercadería
1 por Precio Mayorista
2 por Precio Minorista
previa Validación de Datos
Confeccionar 1 sola
fórmula que sirva
para las 56 celdas
Totales para Control
:
Cód.Valoriz. Importe
0 101.252,65
1 116.440,57
2
139.728,68
Ejercicio
6
-
A
Universidad Nacional del Comahue
Facultad de Economía y Administración
Procesamiento Electrónico de Datos
1ra y 2da Parte
Guía Práctica - TP de Planilla de Cálculos – 2do Cuatrimestre 2020
Realizado por: Lic. Mª Gladis Ferraro (Jefa Cátedra) y Lic. Pedro R. Landaveri (Ayte. Práctica) Pág. 12 de 28.
Ejercicio 5.- Planilla de Liquidación de Sueldos:
(Ver Modelo en Pág. 14.).
1. Insertar la plantilla “TP Excel - Liquidación de Suelo.xltx” en el libro de Excel según se explica
en Pág. 1.. del Anexo Guía Práctica (“Trabajar con Plantillas Pasos a Seguir”).
2. Parámetros para la liquidación:
• Fecha: contiene la fecha de proceso. Hacer enlace con la fecha del Ejercicio 3.
• Máximo de Ausencias Permitidas (Cant. de días): Se usa para calcular el Adicional por
Presentismo.
Hay que ingresar un número entero que será sometido a
una “Validación de Datos por Lista”, y en función de este
valor buscar el “Porcentaje de descuento x día de ausencia”
que corresponda en esta Tabla que ingresarán Uds.- - -
Los únicos valores que se aceptan son estos - - - - - -
• Porcentaje descuento x día: varía según sea la cantidad de
días de ausencias del empleado.
• Otros parámetros: Ingresar los porcentajes y el “Impor-
Lista de
Validación
Días Max Ausen-
cias Permitidas
Porcentaje Des-
cuento x día
3 25,000%
4 20,000%
5 16,667%
6 14,286%
7 12,500%
te Base” que figuran arriba de los títulos de cada columna (Ver modelo).
3. Para el 1er. Renglón (área de trabajo):
Hay 3 tipos de celdas que contienen:
a. Enlaces (traer del Ejercicio 3): Legajo, Sector, Días Ausente, Cód. Banco, Coef. Sueldo
Básico, Antigüedad; y %.Adicional x Función. No enlazar los rótulos (Títulos) de columna.
b. Cálculos variables: están en función de un porcentaje o de una tabla (los valores, en general,
están en la/s celda/s de arriba de cada título de columna). No usar constantes. Estos son:
•
% Presentismo a Pagar: es un porcentaje que está en función de la cantidad:
♦ de “Días ausente” de cada empleado, y
♦
de "Días Máximo de Ausencias Permitidas”.
Se calcula solo si “Días Ausente” es menor o igual a “Días Max Ausencias Permitidas”.
Redondear a 4 decimales.
Ejemplo: si se elige 4 "Días Max. Ausencias Permitidas", significa que al porcentaje
máximo de Presentismo (100%) se le descontará un 20% por cada día de ausencia del
empleado, es decir que:
- si faltó 1 día % Presentismo a Pagar = 100% - 20%
*
1.= 80%
- si faltó 4 días % Presentismo a Pagar = 100% - 20%
*
4.= 20%
- si faltó más de 4 días % Presentismo a Pagar = 0%
• Sueldo Básico: es un importe que está en función de:
♦ el Coeficiente de Sueldo Básico, y
♦ el Importe Base.
• Presentismo: es un importe que está en función de:
♦
el Sueldo Básico, y
♦ el “% Presentismo a Pagar”.
• Bonif. x Antigüedad: es un importe que está en función de:
♦
el Sueldo Básico,
♦ un porcentaje x cada año de antigüedad, y
♦ los años de antigüedad del empleado en la empresa.
ANULADO. Ver en Pág. 7 el proceso alternativo de inserción de Plantillas
Universidad Nacional del Comahue
Facultad de Economía y Administración
Procesamiento Electrónico de Datos
1ra y 2da Parte
Guía Práctica - TP de Planilla de Cálculos – 2do Cuatrimestre 2020
Realizado por: Lic. Mª Gladis Ferraro (Jefa Cátedra) y Lic. Pedro R. Landaveri (Ayte. Práctica) Pág. 13 de 28.
• $ Adic. x Función: es un importe que está en función de:
♦
el Sueldo Básico, y
♦ un "% de Adicional por Función" de cada empleado.
• Asignaciones Familiares: es la suma de dos importes (x Esposa y x Hijos), que figuran en
el Ejercicio 3 para cada empleado.
Los aportes personales: cuyos importes están en función del Total Sujeto a Aportes y el porcen-
taje que corresponda.
Deberán elaborar una sola fórmula que sirva para todas las celdas con los descuentos de apor-
tes personales y para todos los empleados.
• Fondo Anticrisis. Es un Importe que está en función del Total Sujeto a Aportes y de un
porcentaje de Descuento variable.
♦ % Descuento: es un porcentaje que está en función del Total Sujeto a Aportes de cada
empleado, según se indica en la “
TABLA DE % FONDO ANTICRISIS
”.
Usar la función
BUSCARV
búsqueda aproximada para obtener el porcentaje
de Descuento. Previo a ello hay que completar la columna “Valor”.
c. Cálculos simples: Son sumas (usar rangos) o restas:
• Total Sujeto a Aportes: es la suma de las tres celdas contiguas a la izquierda.
• Total de Remuneraciones: es la suma del Total Sujeto a Aportes, del $ Adicional x Función
y de las Asignaciones Familiares.
• Total Descuentos: es la suma de las celdas (contiguas) de “Aportes Personales” más la
celda “Importe” del Fondo Anticrisis.
• Neto a Pagar/Depositar: es = Total Remuneraciones – Total Descuentos.
4. Renglones 2do a 30mo: A medida que se termina de procesar cada celda del primer renglón,
seleccionar esta celda y estirarla (doble clic o arrastrar) con el cursor del Mouse “
+
” hasta el
Renglón 30mo. Esto se repite tantas veces como celdas haya en el 1er renglón. De esta forma
se completará la Planilla (BD) de Liquidación de Sueldos.
5. Calcular los totales de cada columna.
Tabla auxiliar
TABLA DE % FONDO ANTICRISIS
La siguiente tabla, que está en el ar-
chivo “Datos Tablas varias.txt” a
continuación de las usadas en el
Ejercicio 3 deberán moverla al Área
de Trabajo de este Ejercicio.
Para llenar la columna “Valor” Ver
el apunte “Función BUSCARV”, bús-
queda aproximada.
Total Sujeto a Aportes Valor
Hacer des-
cuento de
< a 1000
0%
Entre 1000 y < a 1500
5%
Entre 1500 y < a 2000
6%
Entre 2000 y < a 2500
7%
Entre 2500 y < a 3000
8%
Entre 3000 y < a 3500
9%
Entre 3500 y < a 4000
10%
Entre 4000 y < a 4500
11%
Entre 4500 y < a 5000
12%
>= a 5000
13%
Universidad Nacional del Comahue
Facultad de Economía y Administración
Procesamiento Electrónico de Datos
1ra y 2da Parte
Guía Práctica - TP de Planilla de Cálculos – 2do Cuatrimestre 2020
Realizado por: Lic. Mª Gladis Ferraro (Jefa Cátedra) y Lic. Pedro R. Landaveri (Ayte. Práctica) Pág. 14 de 28.
Ejercicio 5.- Liquidación de Sueldos (es un Modelo ilustrativo)
Fecha
27/05/2015
Sólo ejecutar con la fecha HOY() del Ej
.3
Los datos son ilustrativos
Máxi Aus. permitidas →
3
Importe Base % x Año
Importe Base2
Aportes personales
Buscar % Dto. x día →
25,000%
698,50
5,00%
←
11% 3% 5% 0,30%
0,80%
Fdo.Anticrisis
Legajo
Sector
Ausencias
Cód Banco
Coef. Sdo
Básico
Antigüe-
dad
%
Adic. x
Función
%
Prese
a Pagar
Sueldo
Básico
Presen-
tismo
Bonif x An-
tigüedad
Total sujeto
a aportes
$
Adic. x
Función
Asig.
Familia-
res
Total Re-
munera-
ciones
Jubilación
Ley 19032
Obra
Social
ANSSAL
Seguro
de vida
%
Des-
cuen
to
Importe
Total
Descuen-
tos
Neto a
Pagar/ De-
positar
1 3 1 3 1,80
23
75,00%
1.257,30
942,98
1.445,90
3.646,18
60,00
3.706,18
401,08
109,39
182,31
10,94
29,17
10%
364,62
1.097,51
2.608,67
2 2 4 1,35
23
100,00%
942,98
942,98
1.084,42
2.970,39
100,00
3.070,38
326,74
89,11
148,52
8,91
23,76
8%
237,63
834,67
2.235,72
3 3 5 1 1,10
23
768,35
883,60
1.651,95
1.651,95
181,71
49,56
82,60
4,96
13,22
6%
99,12
431,17
1.220,78
….
….
….
….
….
..
…. …. …. …. …. …. …. …. ….
…. ….
…. …. ….
….
….
….
….
….
…
…. …. …. …. …. …. …. …. ….
…. ….
…. …. ….
28 3 5 1 1,80
1
29 3 6 2 1,00
1
30 1 4 3 1,50
1
60%
1.047,75
52,39
1.100,14
628,65
1.700,14
121,02
33,00
55,01
3,30
8,80
5%
55,01
276,14
1.435,25
63
Totales
251
36.042,62
22.369.51
14.048,65
72.460,78
4.498,35
4.220,00
81.182,28
7.970,69
2.173,83
3.623,04
217,39
579,69
- - -
6.074,11
20.638,75
60.543,53
Totales por Sector
Los totales de Control,
serán informados para
la fecha HOY() vía PE-
DCO
Sector
Sueldo
Básico
Presen-
tismo
Bonif x An-
tigüedad
Total sujeto
a aportes
$
Adic. x
Función
Asig.
Familia-
res
Total Re-
munera-
ciones
Jubilación
Ley
19032
Obra
Social
ANS-
SAL
Seguro
de vida
Fondo
Anti-
crisis
Total
Descuen-
tos
Neto a Pa-
gar/ Depo-
sitar
1
2
3
4
Totales 36.042,62
22.369.51
14.048,65
72.460,78
4.498,35
4.220,00
81.182,28
7.970,69
2.173,83
3.623,04
217,39
579,69
- - -
6.074,11
20.638,75
60.543,53
Totales por Banco
CRITERIOS por Sector CRITERIOS
por Banco
Nombre Banco
Totales
Cant. de Clientes
30
Neto a Pagar/Depositar
60.543,53
Confeccionar 1 sola
fórmula que sirva para
las 1
5
0
celdas
ver Ej. 6-C pto. 2
Ejercicio 6-C
Ver
Ej. 6
-
C p
to.
3
Este documento contiene más páginas...
Descargar Completo
Pautas.pdf
Estamos procesando este archivo...
Lamentablemente la previsualización de este archivo no está disponible. De todas maneras puedes descargarlo y ver si te es útil.
Descargar
Estamos procesando este archivo...
Lamentablemente la previsualización de este archivo no está disponible. De todas maneras puedes descargarlo y ver si te es útil.