Clase 03

CAPICITACION EXCEL 2010 (DIRECTORES Y JEFES)

MCAS Ing. Dávila Rodríguez Víctor

-1-

SESION 3: FUNCIONES LÓGICAS 1. FUNCIÓN SI: Comprueba si se cumple una condición y devuelve un valor si se evalúa como VERDADERO y otro valor si se evalúa como FALSO.

Sintáxis:

=SI (prueba_lógica, prueba_lógica, valor_si_verdadero,valor valor_si_verdadero,valor_si_falso _si_falso )

Dónde: Prueba_lógica : Expresión Lógica que se desea evaluar como VERDADERO o FALSO. Valor_si_verdadero : Es el valor que se devolverá si prueba_lógica es VERDADERO. Valor_si_falso : Es el valor que se devolverá si prueba_lógica prueba_lógica es FALSO.

Ejercicio 01: Se desea mostrar el estado de los alumnos, a partir del promedio obtenido.

Ejercicio 02: Se desea calcular la comisión del vendedor a partir de su venta.

Ejercicio 03: Se desea calcular la bonificación del vendedor a partir de su Nº de ventas.


-2-

Ejercicio 04: Se desea calcular los días de mora, condición e importe de mora en el siguiente cuadro:

2. FUNCIÓN Y: Devuelve VERDADERO si todos los argumentos son VERDADEROS; devuelve FALSO si algún argumento es FALSO. La función Y, nos permitirá usar la función SI con varias condiciones verdaderas en la prueba lógica.

Sintaxis: =Y (valor_lógico1, valor_lógico2 ,...) Dónde: valor_lógico1, valor_lógico2 o FALSO.

: Son criterios que se evalúan como VERDADERO

,…

Ejercicio 01: Evalúa si la Aptitud de un postulante al puesto de Asistente de Cómputo es VERDADERA o FALSA. Se pide como requisitos indispensables: COMPUTACION e INGLES.

Observación: La función Y , de por sí, devuelve sólo VERDADERO o FALSO, que en muchos casos no es muy útil en los reportes de una organización. Pero si la combinamos con la función Si, es muy usual .

Ejercicio 02: Calcular el Aumento y Nuevo Sueldo asignado para los empleados de la Minera ANDOSMAR. Sabemos que dicho aumento se hará efectivo si el Sueldo está entre 600 y 900 soles (>=600 y <=900).


-3-

Nota : Observe que hemos unido con la función Y , los criterios: C5>=600 y C5<=900.

Ejercicio 03: Lea el enunciado descrito, y devuelva el texto SI o NO en la columna Agenda, correspondiente al cliente que cumpla con los criterios especificados.

Nota : Observe que hemos unido con la función Y , los criterios: C12=”Libro” y D12>30.

Ejercicio 04: Distribuidora LUIGI S.A . tiene el siguiente cuadro de Control de Pedidos, que permiten establecer el reclamo o no del pedido. La política de la empresa es “Si la fecha actual es superior a la fecha de recepción prevista, se debe hacer el reclamo del pedido, salvo que ya se recibió el pedido” .


-4-

Haga clic en la celda F8 e inserte la fórmula: =SI (Y (C8<>"", HOY ()>D8, E8<>"x"),"Reclamar","No")

3. FUNCIÓN O: Devuelve VERDADERO si alguno de los argumentos es VERDADERO; devuelve FALSO si todos los argumentos son FALSOS.

Sintaxis: Dónde:

=O (valor_lógico1, valor_lógico2 ,...)

valor_lógico : VERDADERO o FALSO.

condiciones que se desea comprobar y que pueden ser

Ejercicio 01: Evalúa si la Aptitud de un postulante al puesto de Asistente de Cómputo es VERDADERA o FALSA. Se pide como requisitos uno de los siguientes: COMPUTACION o INGLES.

Observación: La función O , de por sí, devuelve sólo VERDADERO o FALSO, que en muchos casos tampoco es muy útil en una organización. Pero si la combinamos con la función Si, es muy usual .


-5-

Ejercicio 02: Se desea calcular el aumento para los empleados de la empresa, de la siguiente manera, si son del área de VENTAS o COMPRAS el porcentaje aplicado es de 10%, de lo contrario 8%, aplicándolo sobre el sueldo. Considere : Aumento =10%*Sueldo (si es de las areas de Compras o Ventas) O 8%*Sueldo (si es de otra area d la empresa. Nuevo Sueldo= Sueldo + Aumento El uso de la función Si con la función O, permite evaluar varios criterios excluyentes en la prueba lógica.

Ejercicio 03: Se desea cubrir 2 plazas vacantes en el área de Sistemas , por ello se evalúa los expedientes de ciertos postulantes. Los postulantes Aptos para la entrevista personal, serán aquellos que tienen más de 3 años de experiencia O que sean titulados en Computación.


-6-

Ejercicio 04: Se desea mostrar el código de categoría correspondiente a cada país según las condiciones establecidas:

En la celda E2, escriba la siguiente fórmula: =SI (B2<40000, SI(O (C2>4000, D2>90),"A","C"), SI (C2>4500,"B","C"))

Ejercicio 05: El Dpto. de Personal de una empresa, desea recategorizar a sus empleados a partir de capacitaciones que establecerán ciertos puntajes de evaluación. Los puntos se establecen según lo indicado:

En la celda E2, escriba la siguiente fórmula: =SI(O (Contar.Si (B3:D3,"X")=2, Contar.Si (B3:D3,"X")=3), 10, SI (Contar.Si (B3:D3,"X")=1, 5, SI (Contar.Blanco (B3:D3)=3,0)))


-7-

FUNCIÓN SI ANIDADA Está función se emplea cuando evaluamos una condición con más de dos posibilidades dentro de la solución, usando dentro de la función SI otra función SI.

Ejercicio 01: Se desea saber el Nivel obtenido de cada alumno de acuerdo a la tabla de lado derecho.

Procedimiento: 1. Seleccionar la celda D3 2. En la ficha Fórmulas abrir el botón

Lógicas, luego ingresar a la función SI

3. Ingresar los argumentos de la función en la ventana que se muestra a continuación: 4

1 2 3

1

En Prueba_lógica ingresar: C3>18

2

En el Valor_si_verdadero ingresar: “EXCELENTE”

3

En el valor_si_falso se debe dar clic dentro del cuadro.

4

Se debe hacer clic sobre la lista desplegable del cuadro de nombres para activar otra función Si e ir completando la información requerida.


-8-

El resultado de todo el procedimiento anterior de la fórmula es: =SI (C3>18,”EXCELENTE”, SI (C13>15,”BUENO”, SI (C13>10,”REGULAR”,”MALO”)))

Observación Es posible anidar hasta 64 funciones SI como argumentos valor_si_verdadero y valor_si_falso para crear pruebas más complicadas. Tome en cuenta que a veces se puede realizar otro tipo de anidamientos (una función como uno de los argumentos de otra función). Por ejemplo, la siguiente fórmula utiliza la función anidada PROMEDIO y compara el resultado con el valor 50: =SI(PROMEDIO(F2:F5)>50,SUMA(G2:G5),0)

Ejercicio 02: Se tiene el siguiente reporte de evaluación de los alumnos de una Institución Educativa referente a un curso, y se desea conocer su condición final

Procedimiento: 1. Haga clic en la celda I4 2. Escriba la fórmula siguiente: =SI(G4<=4,SI(H4>=10.5,"Aprobado","Desaprobado"),"Inhabilitado")

Ejercicio 03: Una agencia de turismo ofrece comisiones a sus vendedores, de acuerdo al sector que pertenezcan. Completar la tabla de datos de acuerdo a las consignas que aparecen debajo de la misma.

Procedimiento: 1. Seleccione la celda D13.


-9-

2. Digite lo siguiente:

=SI (B13=”INT”,5%, SI (B13=”NAC”,3%,0))*C13 Otra forma sería usando las celdas, de la siguiente manera: =SI (B13=”INT”,$B$9,SI(B13=”NAC”,$B$10,0))*C13 3. Presione la tecla Enter. 4. Copie la fórmula a las celdas siguientes y observe el resultado.

Ejercicio 04: Determinar el Aumento y Nuevo Sueldo de cada Empleado, teniendo en cuenta que la tasa de Aumento (% Aumento) se asigna en función de la Zona en donde trabaja. Considere: Aumento = % Aumento*Sueldo actual y Nuevo sueldo = Sueldo Aumento .

Actual +

Haga clic en la celda D7 e inserte la fórmula:

=SI (B7=”SUR”, $B$2, SI (B7=”CENTRO”, $B$3, SI (B7=”NORTE”, $B$4)))*C7

Ejercicio 05:

Observación colocar “Alto”, si el sueldo es menor que 1000 colocar “Bajo”, caso contrario colocar “Medio.” Si sueldo es mayor que 2000, entonces en la columna

Haga clic en la celda C2 e inserte la fórmula:

=SI (B2>2000,”ALTO”, SI (B2<1000,”BAJO”,”MEDIO”))*C7


- 10 -

Ejercicio 06: En un depósito se encuentran almacenados distintos productos. En la tabla adjunta debajo se detallan los códigos de identificación y otras características más.

1. Haga clic en la celda E5 2. Escriba la función si anidada: =SI(D5="A","Máxima Precaución",SI(D5="B","Precaución Moderada", SI(D5="C", "Precaución de Rutina")))

FUNCIÓN SIERROR Está función devuelve un valor determinado en caso que una fórmula o celda contenga un error. Utilice la función SIERROR para interceptar y controlar errores en una fórmula. Sintaxis:

=SIERROR(valor, valor_si_error )

Dónde:

valor: puede ser una referencia a otra celda, o una fórmula. valor_si_error : valor a mostrar en caso que el argumento valor devuelva un error.

Ejercicio 01: En la empresa SNACKS se desea obtener el porcentaje de gastos con relación a las ventas para cada zona.

Procedimiento: 1. Seleccionar la celda E3 2. En la ficha Fórmulas abrir el botón ingresar a la función SIERROR.

Lógicas, luego

3. Ingresar los argumentos de la función en la ventana que se muestra a continuación:


- 11 -

1 2

1

En Valor ingresa: D3/C3, gastos sobre ventas.

2

En Valor_si_error ingresa: “-“, nos devolverá un guión en caso se produzca un error.


- 12 -

BASE DE DATOS: ORDENAR, SUBTOTALES Y FILTROS BÁSICOS Y AVANZADOS Las bases de datos en Excel son una colección organizada de información dividida en campos los cuales nos proporcionan datos detallados e individuales de un universo de datos. Su estructura lógica de una base de datos en Excel se puede ver por una tabla compuesta por columnas y filas, en las cuales las columnas definen a los campos y a las filas a los registros. Un campo es una pieza única de información, un registro es un sistema completo de campos. Los registros nos indican el número de datos que existe en la tabla. Cada campo tiene un nombre que lo identifica, al cual se le denomina encabezado de campo o nombre de campo. Por ejemplo una guía de teléfono es análoga a una base de datos en Excel, Contiene una lista de registros, cada uno de los cuales consiste en tres campos: nombre, dirección y número de teléfono. Encabezados de campos

REGISTROS

C AM P O S

ORDENACION DE REGISTROS A. ORDENACION POR UN CAMPO: Consiste en redistribuir los registros en un sentido ASCENDENTE o DESCENDENTE del campo elegido según la naturaleza del dato. Ejercicio: Ordenar la base de datos por el campo AREA en forma descendente. Procedimiento: 1. Ubicarse en cualquier celda del campo AREA (Por ejemplo C3)


- 13 -

2. En la ficha Datos, grupo Ordenar y Filtrar, haga clic en el botón Ordenar de A a Z.

B. ORDENACION POR MÁS DE UN CAMPO: En nuestro ejemplo sencillo, hemos ordenado la base de datos por el campo AREA. Ahora tenemos los registros agrupados por AREA , y las AREAS están en orden alfabético descendente. Pero dentro de cada grupo los registros no tienen un orden. Supongamos que dentro de cada AREA queremos ordenar por los sueldos de menor a mayor. Procedimiento: 1. Ubicar el cursor en cualquier celda de la base de datos. 2. En la ficha Datos, grupo Ordenar y Filtrar, haga clic en el botón Ordenar. 3. Clic en el botón Agregar nivel para ordenar por un segundo campo (y así sucesivamente, cuando sea necesario).

El resultado es el siguiente:


- 14 -

AUTOFILTROS 

Seleccionar los datos que forman la lista y elegir la Ficha Datos, elegir la opción Filtro. O también desde la Ficha Inicio, seleccionar Ordenar y Filtrar seleccionar la opción Filtro.



Los nombres de campo que están como encabezados de las columnas pasan a ser listas desplegables que permiten realizar la selección.



Desde la opción de Autofiltro, podemos seleccionar los datos que se desean mostrar.

Ejemplo: Mostrar todos los productos cuyo precio base es m ayor que S/. 180.


- 15 -



Ingresar la condición respectiva.



El resultado seria



También podemos ordenar los datos de mayor a menor o viceversa. Ejemplo: Mostrar todos los productos ordenados por Precio Base de Mayor a menor:



El resultado mostrara todos los datos de la sigui9ente manera


- 16 -

FILTROS AVANZADOS Es una manera de seleccionar algunos datos y mostrarlos en una ubicación diferente. Esta opción utiliza los cuadros de criterios vistos en las funciones de Base de Datos. Ejemplo 01: Filtrar los datos de los abarrotes:  Crear el cuadro de criterios:





Seleccionar la opción de Filtro Avanzado de la Ficha Datos

El resultado seria.Cuadro

de Criterio

Destino de los Datos

Ejemplo 02: Filtrar los datos de las golosinas cuyo Precio Base sea menor que S/. 220  Crear el cuadro de criterios:



Seleccionar la opción de Filtro Avanzado de la Ficha Datos:


- 17 -

Cuadro de criterio

Destino de los



El resultado seria

SUBTOTALES Puedes calcular automáticamente subtotales y totales generales en una lista de datos o calcular subtotales de una columna utilizando el comando subtotal del grupo esquema de la ficha Datos; en base a una función de resumen (Sumar, Contar, Promedio).

Ejemplo: 

Dada la siguiente tabla de datos, calcular el promedio de ventas por tipo de producto.


- 18 -

Solución: 

Para poder calcular el subtotal por Tipo de Producto, debemos de tener ordenados los datos por este criterio.



El resultado de los datos ordenados por Tipo de Producto seria:



Seleccionar la opción Subtotal de la Ficha Datos:



Ingresar las consideraciones del ejercicio:


- 19 -

Criterio del Subtotal Función a utilizar Campo a analizar



El resultado del subtotal seria:


- 20 -

TABLAS Y GRÁFICOS DINÁMICOS Un informe de tabla dinámica es una tabla interactiva que combina y compara rápidamente grandes volúmenes de datos. Podrá girar las filas y las columnas para ver diferentes resúmenes de los datos de origen, y mostrar los detalles de determinadas áreas de interés. Se utiliza un informe de tabla dinámica cuando se desea comparar totales relacionados, sobre todo si tiene una lista larga de números para resumir y desea realizar comparaciones distintas con cada número. En los informes de tabla dinámica, cada columna o campo de los datos de origen se convierten en un campo de tabla dinámica que resume varias filas de información.

DISEÑO DE LA TABLA DINÁMICA 

En la Ficha Insertar, seleccionar Tabla Dinámica.



Seleccionar la tabla que contiene los datos que se desean analizar.

 Diseñar

la tabla Dinámica


- 21 -

 En

la opción valores se colocan los datos numéricos centrales de análisis.  En rótulos de fila y/o columna, se ingresan los criterios de análisis (Tipo de Producto, Producto, Marca, Color, etc.).



Por defecto ola operación usada es la SUMA. Para modificar el Criterio del Análisis de los Datos, se puede seleccionar otra operación aritmética. Seleccionar el campo respectivo y se mostrara el menú contextual siguiente:


- 22 -



Seleccionar la operación respectiva. Se puede cambiar también el texto del campo.

 La

tabla dinámica final seria


- 23 -

GENERAR GRÁFICO DINÁMICO A partir de cada tabla dinámica, se puede generar un Gráfico dinámico, con solo seleccionar la opción Gráfico Dinámico de la Ficha Herramientas de Tablas Dinámicas.



Seleccionar el tipo de Gráfico.

Y

se mostrara automáticamente el Gráfico Dinámico respectivo.

 Se

pueden hacer consultas a los Gráficos Dinámicos, para mostrar u ocultar algunos datos; estas consultas también afectaran a la tabla correspondiente.


- 24 -



Y el gráfico y la tabla a mostrarse se modificaran de la siguiente manera:


- 25 -

VALIDACIÓN DE DATOS Se utiliza para definir restricciones sobre los datos que se pueden insertar en una celda, y para mostrar mensajes que insten a los usuarios a especificar entradas correctas y les notifiquen las entradas incorrectas.  Seleccionar las celdas para validación, luego seleccione Ficha Datos, en el grupo Herramienta de Datos, hacer clic en el botón Validación de datos, y se muestra la siguiente figura:



Ficha Configuración: Permite ingresar la condición que debe cumplir el contenido de la celda. Ejemplo: que la celda no acepte números menores que 0



Ficha Mensaje Entrante: Se define un mensaje informativo donde se indican los v alores que se deben ingresar en esa celda.


- 26 -



Ficha Mensaje de Error: Es un mensaje de error que se mostrara cada vez que se ingresan número que no cumplan con la condición ingresada en la celda.





En la celda se mostrará el mensaje de entrada, tal como se muestra en la siguiente figura.

Si se ingresa un dato que no cumple con la condición, se mostrara el mensaje de error:


- 27 -

Clase 03

Recommend Documents