TECNOLOGIAS DE LA INVESTIGA-CIÓN

Y LA COMUNICACIÓN (TIC´S)

HOJA ELECTRÓNICA EXCEL

(Nivel Intermedio)

Prof. Edwin Oswaldo Raymundo D.

Docente -TICS-

INTRODUCCION

Sin lugar a dudas, una de las herramientas de productividad que debe afianzar el estudiante graduando de la carrera de Perito Contador, son las denominadas Hojas Electrónicas.

Existen diversas suites de ofimática, como Microsoft Office, Perfect Office, Star Office, Open Office, Lotus Smart Suite, Google Docs, cada una incluye su propia hoja electrónica. A continuación invitamos al alumno a reforzar y profundizar sobre la aplicación que tiene Microsoft Excel en el campo de la contabilidad. Para este bimestre se persiguen alcanzar algunas competencias de acuerdo al contenido declarativo establecido para la asignatura: Tecnología de la Información y Comunicación (TIC’S) para la carrera de Perito Contador con Especialidad en Computación.

Para afianzar la práctica de este contenido, se recomienda crear y contar con un único libro de trabajo de Microsoft Excel y que las hojas que lo conforman posean los distintos ejercicios, ya que en su momento servirán de base para darle continuidad a otros temas.

Así mismo, es importante que el alumno elabore y resuelva cuestionarios, mapas cognitivos, mapas conceptuales, cuadros comparativos, resúmenes, investigaciones, de acuerdo a determinados temas.

Los contenidos a trabajar son:

a) Funciones Básicas y su aplicación =Suma(), =Max(), =Min(), =Promedio() =Contar(), =producto(), =contar.blanco()

b) Funciones Condicionales =contar.si() =sumar.si()

c) Decisiones en la hoja electrónica Uso y aplicación de la función =SI() Decisiones anidadas simples y con “n” anidaciones

d) Formato Condicional Aplicación de formato condicional por criterios

APLICACIÓN DE FUNCIONES

REPASANDO FUNCIONES BASICAS

La hoja de cálculo ofrece muchas categorías de funciones como lo son: funciones matemáticas, funciones para manejo de fechas y hora, funciones de búsqueda y referencia, funciones lógicas, etc. Recuerde que las funciones se pueden escribir en una celda y siempre poseen una sintaxis o argumentos, rangos de celdas que son fundamentales para devolver un resultado, y que ese resultado puede ser un dato alfabético, un dato numérico, un dato fecha, una hilera de caracteres (string), etc.

En la siguiente tabla encontrará una lista de funciones, complétela escribiendo a que categoría pertenece cada función, su aplicación o uso, y escriba un ejemplo. En los siguientes sitios de Internet encontrará información más detallada de funciones.1

No. Función Categoría Aplicación o Uso Ejemplo1 =suma()2 =max()3 =min()4 =promedio()5 =contar()6 =producto()7 =contar.blanco()8 =truncar()9 =redondear()10 =ahora()

EJERCICIO 1 Para la empresa ENVAICA S.A. el equipo de ventas esta integrado por 8 vendedores. El gerente general, requiere que usted como auxiliar del departamento de contabilidad, diseñe un informe de ventas, con su

respectivo encabezado, que incluya la fecha y la hora del sistema.

Necesitan un registro de las ventas que se realizaron en el presente mes, anotando el monto de las ventas realizadas por cada vendedor en cada semana (semana1, semana2, semana3 y semana4). Así mismo, se le informa que debe incluir un resumen detallando lo siguiente:

a) El total de lo vendido en cada semana, la venta más alta y la venta más baja por cada semana.

b) El total de lo vendido por cada vendedor y el promedio de las ventas realizadas, debidamente redondeado.

1 http://www.funcionesexcel.com/

http://www.mailxmail.com/curso-excel-xp-primera-parte/funciones-max-min

FUNCIONES CONDICIONALES

Estas funciones utilizan una condición para poder cumplir con su misión o aplicación. La función =Contar.si() pertenece a la categoría estadistica, y la función =sumar.si() pertenece a la categoría matemática, y aunque ambas se parecen realizan tareas muy distintas al momento de aplicarlas dentro del trabajo cotidiano con Excel.

La importancia de ambas funciones radica en el resultado que devuelven. En el siguiente cuadro comparativo, se verán las características, el formato y un ejemplo de aplicación para el ejercicio 1 de la anterior semana. Se sugiere que visite las siguientes paginas web de Internet para una mejor comprensión del tema.2

Función =CONTAR.SI() Función =SUMAR.SI()Cuenta las celdas, dentro del rango, que no están en blanco y que cumplen con el criterio especificado.

SintaxisCONTAR.SI(rango;criterio)

Rango    es el rango dentro del cual desea contar las celdas.

Criterio    es el criterio en forma de número, expresión o texto, que determina las celdas que se van a contar. Por ejemplo, los criterios pueden expresarse como 32, "32", ">32", "manzanas".

Suma las celdas en el rango que coinciden con el argumento criterio.

SintaxisSUMAR.SI(rango;criterio;rango_suma)

Rango    es el rango de celdas que desea evaluar.

Criterio    es el criterio en forma de número, expresión o texto, que determina las celdas que se van a sumar. Por ejemplo, los criterios pueden expresarse como 32, "32" ">32", "manzanas".

Rango_suma    son las celdas que se van a sumar.

Ejemplos de Aplicación:

Suponiendo que las ventas de la semana1 están en el rango que comprende de c10 a c20, y necesitamos saber cuantas ventas estuvieron arriba de 5000 escri-biriamos:

=contar.si(c10..c20,”>=5000”)

Ejemplos de Aplicación:

Suponiendo que tenemos en el registro de ventas una columna con el titulo Ruta (Columna B), y en esta columna las rutas son: Jutiapa, Jalapa y Zacapa. Para averiguar cuanto fue el monto vendido en cada ruta escribiríamos en las celdas respectivas lo siguiente:

=sumar.si(b10..b20,”Jutiapa”,c10..c20)=sumar.si(b10..b20,”Zacapa”,c10..c20)=sumar.si(b10..b20,”Jalapa”,c10..c20)

Veremos en la siguiente página el formato propuesto para el registro de las ventas por semana, del equipo de vendedores que laboran para la empresa ENVAICA, añadiendo la columna RUTA.

2 http://www.territoriopc.com/excel/tutorial_excel_sumar_si.php

http://sabanet.unisabana.edu.co/cursosinteract/excel/sesiones/funciones.html

EJERCICIO 2

El “Automático” S.A. es una empresa que se encarga de importar y comprar vehículos que pueden ser americanos, europeos o japoneses (origen). Las

marcas con que trabajan son: Nissan, Toyota, Mazda y Mercedes Benz

El propietario de esta empresa, le solicita un inventario de los automóviles, el cual debe incluir: No. de Chasis, No. de Motor, Marca, Color, Modelo, Origen y el precio en dólares. Se le informa, que dicho registro contable, debe existir una columna con el precio en Quetzales (utilice la formula y la tasa de cambio del día), y que la ganancia estimada para cada vehiculo es de un 25% del precio en Quetzales.

El registro debe tener la capacidad de determinar el precio de venta de cada automóvil en quetzales, así mismo por cuestiones de inventario, necesitan contar con un resumen con la siguiente información:

a) Cuantos automóviles por marca se tienen en venta.b) Cuantos automóviles de modelo reciente tienen en la empresa

(modelos arriba de 2000) y cuantos no reciente (modelos menores o iguales a 2000).

c) El total invertido en dólares por cada marca de automóviles que se venderán.

=sumar.si(b10..b20,”Jutiapa”,c10..c20)

=sumar.si(b10..b20,”Zacapa”,c10..c20)

=sumar.si(b10..b20,”Jalapa”,c10..c20)

d) El total a percibir en concepto de precios de venta según la marca de los autos.

DECISIONES EN LA HOJA DE CÁLCULO

USO DE LA FUNCIÓN LÓGICA =SI()

La función =SI() es, una función condicional. Al mencionar la palabra “Sí”, hacemos referencia a una condición lógica. Un ejemplo de ello es: “Si el sueldo de una persona es mayor a Q.1200.00, debo pagarle con cheque, de lo contrario debo pagarle en efectivo”

Nuestras hojas de cálculo nos permiten realizar este tipo de operaciones con datos, ya sea, mostrando texto o bien realizando algún cálculo específico mediante condiciones.

Estas condiciones son importantes puesto que nos permiten realizar cálculos que no podemos realizar por medio de las operaciones matemáticas básicas a las que estamos acostumbrados (suma, resta, multiplicación, división).

Realicemos entonces un ejemplo de una hoja de cálculo con alguna condición lógica. Para ello debemos basarnos en la función SI o si tiene una instalación en inglés entonces deberá utilizar la función IF con la siguiente sintaxis:

=SI(condicion, acciones_si_es_verdadera, acciones_si_es_falsa)

¿QUE ES LA CONDICION?

Es la base fundamental para formular una buena decisión al momento de aplicar la función =si(). Una condición por lo general siempre estará formada por tres elementos:

a. Una celdab. Un operador relacional (>,<, >=,<=,<>)c. Un valor o una celda a comparar

CASO DE APLICACIÓN Y EXPLICACION No. 1

Una empresa debe pagarle a su planilla de trabajadores de dos maneras distintas:

A las personas que tienen un sueldo menor a Q.1200.00 deben pagarle en efectivo, y a las personas que tienen un sueldo de Q.1200.00 o más deben pagarle con cheque. Usted debe agregar en una nueva columna el tipo de pago que se le hará a cada trabajador más o menos como se muestra en la siguiente imagen.

La condición que se aplicará en la celda C3, y que se empleará para la toma de decisión es la siguiente:

=SI(B2<1200,”Efectivo”,”Cheque”)

Hay que tomar en cuenta que de acuerdo a los valores que se van a comparar, hay condiciones de varios tipos: Condiciones Alfabéticas, Condiciones Alfanuméricas Condiciones Numéricas, Condiciones de tipo fecha, y Condiciones Lógicas.

En la siguiente tabla se muestran algunas formulaciones de decisiones utilizando diversos tipos de condiciones:

Tipo de Condición

Ejemplificación Significado

Alfabética =si(d10=”Contado”,e10*15%,0) Si la forma de pago en d10 es al Contado, se otorgará un descuento del 15% del valor de la compra, por el contrario no habrá descuento.

Alfanumérica =si(j20<>”SOL-01”,30,0) Si el status en la celda j20 es diferente a “SOL-01” habrá un incremento de mora de 30.00, por el contrario no hay incremento de mora.

Numérica =si(b8>=5,f8*10%,f8*5%) Si la cantidad de artículos que compra el cliente en b8 es mayor o igual que 5, del total de compra hará un descuento del 10%, por el contrario si lleva menos únicamente 5% de descuento.

Fecha =si(b8=hoy(),”Factura Vencida”,”Factura no vencida”)

Si el dato en la celda b8 es igual a la fecha de hoy del sistema, entonces que indique el mensaje “Factura Vencida”, por el contrario otro mensaje.

En la hoja de cálculo, cuando se evalúa la condición si se cumple y es valida, se realizará lo que se conoce como salida verdadera (acciones o enunciados si es verdadera) y cuando no se cumple la salida falsa (acciones o enunciados si es falsa).

Para mayor información viste los siguientes sitios: http://usuarios.lycos.es/danielestb/excel2.htm y http://henryrios.unlugar.com/formulas.htmlCASO DE APLICACIÓN Y EXPLICACION No. 2

El profesor de computación necesita realizar un reporte y clasificar a sus

Observe la escritura de la función

alumnos, considerando la Nota Final que obtuvieron en el curso de Tecnología de la Información y Comunicación (TICS). A todos aquellos alumnos que obtuvieron una nota de 60 puntos o más se consideran “Aprobados” y los que obtuvieron una nota final menor de 60 como No Aprobados.

La decisión que debe formularse en la celda C2 es la siguiente:

=si(b2>=60, “Aprobado”,”Reprobado”)

En el campo de la computación, existen programas como DFD que nos permiten graficar e ilustrar entradas de datos, salidas y decisiones.

En el diagrama de Flujo de la izquierda se presenta la grafica de la secuencia de la decisión que tomaría la computadora para determinar si un alumno Aprobó o Reprobó el curso.

Como se podrá observar, existen dos caminos o alternativas a seguir, y que eso dependerá de la notafinal que se tenga ingresada.

La salida verdadera (SI) será el mensaje “Aprobado”, y la salida falsa (NO) será el mensaje “Reprobado”.

EJERCICIO 3

En la fabrica de ropa “El Buen Vestir”, propiedad del señor Gustavo Berganza, se encuentran vendiendo un sin fin de prendas de vestir para toda la familia.

Con la finalidad de conocer sus habilidades en el manejo de la hoja de cálculo, el gerente de ventas, necesita que usted en base a las facturas de las ventas del día, diseñe un pequeño control en Excel para registrar la cantidad de prendas que se vendieron, el tipo de prenda (dama, caballero y niño), el precio unitario y el total de la compra.

Dicho reporte de ventas y descuentos debe llevar un encabezado, con el nombre de la fábrica, dirección, página Web, correo electrónico y números de teléfonos. Así mismo, debe incluir el no. de factura que se extiendo en cada venta realizada, la fecha, el nombre del cliente y el no. de nit. (Trabaje con listas de datos).

Dentro de las especificaciones que se le dan, le indican que si el cliente lleva de 1 a 6 unidades, tendrá un descuento del 5% del total de la compra, por el contrario si lleva más de 6 obtiene un 10% de descuento del total de la compra.

Como parte de las necesidades de la fábrica, ellos quieren que usted reporte el total de dinero recaudado por concepto de ventas, considerando el tipo de prenda. Para lo cual debe incluir lo siguiente:

a. Total de ventas ropa para damasb. Total de ventas ropa para caballerosc. Total de ventas ropa de niños(as)

Para que el reporte de ventas posea presentación, usted incluirá formato a sus celdas como bordes, color, tipo de letra, formato de moneda donde considere necesario.

DECISIONES ANIDADAS SIMPLES

Cuando se habla de un anidamiento, nos referimos que los elementos serán consecutivos. De esta manera la función =Sí() también puede anidarse de tal manera

TABLA DE COTEJO PARA EVALUAR - EJERCICIO 3

ASPECTOS A EVALUAR PUNTEO ESTIMADO PUNTEO OBTENIDO

Diseño y formato del control de ventas 3

Decisión para el descuento 5

Aplicación de =sumar.si() 2

TOTAL PUNTAJE

que permita evaluar más de una condición, así se construyen condiciones complejas que nos devuelvan más de 2 posibles respuestas de salida.

=SI(condicion, acciones_si_es_verdadera,SI(condicion, acciones_si_es_verdadera, acciones_si_es_falsa)

CASO DE APLICACIÓN Y EXPLICACION No. 3

La empresa Transmares S.A. debe pagar su planilla de trabajadores en este mes, acordándose en gerencia realizar los pagos de tres maneras distintas.

El encargado de pagos, le informa que usted realizará un formato aplicando la función “si” para formular una decisión anidada para determinar la forma o tipo de pago para cada trabajador, de acuerdo a las siguientes condiciones:

Si gana más de 3000, el pago será con Depósito Si gana entre 1200 y 3000 el pago será en Cheque Si gana menos el pago será en Efectivo

Esta decisión se lee o se interpretaría así:Si el valor contenido en la celda B2 es menor a 1200, entonces el tipo de pago será “Efectivo” para la celda C2 (que es donde esta la decisión), pero si el valor de B2 es menor o igual a 3000, el tipo de pago será “Cheque”, y si b2 es mayor que 3000 el tipo de pago será “Deposito”.

Para ampliar sus conocimientos sobre decisiones anidadas, puede visitar el siguiente sitio web: http://www.bufoland.cl/apuntes/excel/condicional.php

Ejercicio 4

En una tabla de Excel, la empresa Transmares S.A. desea calcular el sueldo líquido a recibir por cada empleado.

Los datos a ingresar para dicha planilla son los siguientes:

Código de empleado

Observe la escritura de la función

Nombre del trabajador Salario Base Nominal Salario por Hora Horas Laboradas por cada día de la semana (de lunes a sábado)

Utilizando las formulas respectivas, Excel deberá mostrar el Total de Horas laboradas en la semana y la cantidad de Horas Extras. Para ello se debe considerar que si el trabajador laboró más de 44 horas a la semana, las restantes se consideran Horas Extras, por lo que deben calcularse por medio de una decisión.

Otro de los cálculos consiste en determinar el valor de la Hora Extra y el Salario Extraordinario tomando como base las horas extras de la semana. También debe calcularse el Salario Ordinario, el descuento del Igss (4.83%) y el Salario Liquido a Recibir aplicando las formulas respectivas.

DECISIONES con N AnidacionesAPLICACION DE LA FUNCIÓN SI CON “n” ANIDACIONES

Es posible anidar hasta siete funciones SI como argumentos valor_si_verdadero y valor_si_falso para construir pruebas más elaboradas. De acuerdo a las necesidades o del planteamiento de determinado problema, se pueden aplicar varias decisiones unidas y encadenadas para tomar una salida.

Ejercicio 5 La empresa Comercial Caribe desea establecer mediante funciones SI anidadas en una hoja de Excel, el Enganche y el Interés Anual que se debe pagar por un determinado articulo, tomando en cuenta que para ello

cuenta con una escala de días de crédito y de acuerdo a esta escala establece el porcentaje (%) de enganche y de Interés Anual de los productos.

CUOTAS DE CREDITOCUOTAS INTERES ANUAL ENGANCHE

1 0% 0%30 5% 15%60 10% 20%90 15% 25%

Nota: Observe que se utiliza valor en celdas fijas para aplicar a la función.

Considerando el No. De Cuotas, agregue en la celda E9, el titulo: Interés Anual, y diseñe mediante funciones SI anidadas de Excel, el cálculo del interés anual sobre el valor, que se debe pagar por cada uno de los artículos del listado.

FORMATO CONDICIONALFORMATO CONDICIONAL

El formato condicional se utiliza para resaltar un resultado que cumpla con una determinada condición. El resultado puede ser resaltado por un cambio de color, de tipo de letra, de formato de letra y otros. En la versión de Excel 2007, nos ubicamos en la Ficha Inicio/Estilos allí encontraremos el icono FORMATO CONDICIONAL. A continuación se presenta la pantalla de la versión de Excel 2007 en Ingles.

Observe la escritura de la función

En Excel 2003 solo se pueden configurar hasta 3 condiciones para aplicar formato condicional. En la ventana que aparece se establecen las condiciones de acuerdo a los parámetros requeridos. En el botón formato se ingresa a la ventana donde se puede establecer el formato que se desee.

Al finalizar de establecer las condiciones presionar el botón aceptar, para que los resultados queden como se visualiza en el ejemplo.

Ejemplo de Aplicación

Para el presente ejemplo, tomaremos en consideración el ejercicio de la empresa ASTORIA S.A., por lo que debe de realizarse los siguientes pasos:

1. Ingresar los datos que se muestran en la imagen siguiente.2. Colocarse en una celda determinada.3. Versión 2003: Hacer clic en el menú Formato y luego en la opción Formato

Condicional Versión 2007: Seleccione la ficha: Inicio, Estilos y elija el icono Formato Condicional

En el siguiente ejemplo se establece un color condicional para cada semana, de acuerdo a las condiciones siguientes:

a. Si la venta esta por debajo de 10000 esta tendrá un texto de color rojo.

b. Si la venta es mayor o igual a 10000, esta tendrá un texto de color azul.

Nota:Para mejorar sus conocimientos sobre formato condicional, se recomienda visitar los siguientes sitios web:

http://www.jegsworks.com/Lessons-sp/numbers/design/step-conditionalformat.htm

http://www.microsoft.com/spain/empresas/demos/formatos_condicionales.aspx#

REPASEMOS DECISIONES Y FORMATO CONDICIONAL

Ejercicio 6 Utilizando la información del ejercicio No. 6, agregue una columna con el titulo CLASIFICACION y una decisión considerando el Total Vendido. Si el vendedor vendió entre 1000 y 15000 “Debe Mejorar”, si vendió entre

15000 y 35000 “Buen Vendedor”, y si vendió mas de 35000, “Excelente Vendedor”.

Aplique un formato condicional para los mismos criterios.

Ejercicio 7 Utilizando los datos del ejercicio 5 de esta unidad, establezca formato condicional a la columna de No. De Cuotas, de acuerdo al siguiente criterio:

1. 30 Cuotas con Negrita y en Color azul.2. 60 Cuotas con Negrita y en Color amarillo.3. 90 Cuotas con Negrita y en Color Rojo.

INFOGRAFIA RECOMENDADA POR TEMAS

1. FUNCIONES BASICAS

http://www.mailxmail.com/curso-funciones-excel

http://www.territoriopc.com/excel/tutorial_excel_funciones.php

2. FUNCIONES CONDICIONALES

http://office.microsoft.com/es-es/excel/HP052092923082.aspx

http://office.microsoft.com/es-es/excel/HP052090293082.aspx

3. DECISIONES Y USO DE LA FUNCION =SI()

http://usuarios.lycos.es/danielestb/excel2b.htm

http://www.bufoland.cl/apuntes/excel/condicional.php

http://grupos.emagister.com/video/funcion_si_de_excel/1013-71763

4. Formato Condicional

http://www.microsoft.com/spain/empresas/demos/formatos_condicionales.aspx

http://www.aulaclic.es/excel2003/t_17_3.htm