• Author / Uploaded
• Raúl Pruna

• Categories
• Hoja de cálculo
• Microsoft Excel
• Fórmula
• Función (Matemáticas)
• Física y matemáticas

Citation preview

BIENVENIDO AL CURSO DE EXCEL AVANZADO PRESENTACION DEL CURSO Introducción Muchas personas usan el programa Microsoft Excel, pero realmente no aprovechan todas las posibilidades de la aplicación; Microsoft Excel es una herramienta muy potente que nos permite optimizar nuestro trabajo, desarrollar habilidades y destrezas para aplicarlas en nuestro diario desempeño tanto personal como laboral. Este curso ha sido diseñado con el fin de ofrecerle al participante una referencia para profundizar en el uso de las herramientas de Excel. El curso esta dividido en cinco semanas, en cada semana usted encontrará: − Una guía de las actividades que usted debe desarrollar durante el curso. − Material de apoyo, lecturas donde se explican diferentes temas. − Ejemplos desarrollados paso a paso de cada lección. − Talleres propuestos, para que usted los realice, practiqué y aplique lo estudiado − Evaluaciones para evidenciar su aprendizaje. Los ejercicios presentados son de aplicación general permitiendo una fácil comprensión de los temas desarrollados. Objetivo General Adquirir conocimientos avanzados sobre el manejo y aplicación de la Hoja Electrónica. Objetivos Específicos − − − − − −

Adquirir habilidades en el manejo de rangos. Manipular eficientemente funciones simples y anidadas. Ordenar, filtrar, subtotal izar, validar y consolidar listas de información Elaborar resúmenes y configurar correctamente gráficos dinámicos Comprender el uso de las macros automáticas Diseñar eficazmente formularios.

A quien va dirigido Está dirigido a usuarios con experiencia básica en Excel. Pre-requistos

Excel Avanzado Presentación Curso

1

Ing. Leila Sofía Farfán

Es necesario que el estudiante interesado en participar en este curso, evidencie competencias en el uso y manejo eficiente de las siguientes herramientas: − Destreza en el manejo de Sistema Operativo Windows, − Competencia en Excel básico; con buen manejo del menú Archivo, Edición y Formato; formulas con expresiones aritméticas, manejo de referencias relativas y absolutas. Requerimientos de Software − Windows XP − Microsoft Excel 2003 ó XP Tiempo en semanas El curso dura 5 semanas, usted debe dedicar mínimo dos horas diarias. Metodología Durante el desarrollo del curso se desarrollarán una serie de lecciones, evaluaciones, talleres guiados y propuestos que le permitirán al participante desarrollar competencias avanzadas y un buen manejo de la herramienta. Estrategias de evaluación − Resolución de problemas propuestos.

Excel Avanzado Presentación Curso

2

Ing. Leila Sofía Farfán

Lectura 1.1: TRABAJANDO CON NOMBRES INTRODUCCION En esta lectura se explican los conceptos básicos para definir, crear, pegar, aplicar y eliminar rangos de nombres en Excel.

OBJETIVO − Identificar nombres de rangos − Definir, crear, pegar, aplicar y eliminar nombres − Reconocer la utilidad de trabajar con nombres en una hoja de Excel

Dar nombres a rangos o celdas. Excel nos permite asignar un nombre a una celda o rango de celdas; de esta forma podremos: − Utilizar los nombres en una formula − Desplazarnos a ese rango más fácilmente − Identificar mejor los rangos. Definir un nombre a un rango o celda: 1. Seleccionar el rango o la celda a nombrar. 2. Seleccionar menú Insertar, Nombre, Definir. Nos aparece la ventana: Definir Nombre

3. Digitar el nombre que deseamos asignar para nuestro ejemplo VENTAS; En el cuadro de dialogo Se refiere a: nos indica cual es el rango que seleccionamos. 4. Aceptar. Eliminar un nombre

Excel Avanzado Lectura 1.1 - Nombres

3

Ing. Leila Sofía Farfán

5. Clic en menú Insertar, Nombre, Definir. Seleccionar el nombre de la lista de nombres y Clic en botón Eliminar. Creando Nombres Se puede crear nombres usando la información escrita en una celda. El nombre de la celda puede estar a la derecha, izquierda, por encima o debajo del rango o celda a nombrar. 6. Seleccionar el rango, según nuestro ejemplo A2:A9

7. Clic en Insertar, Nombre, Crear. Aparece la ventana Crear nombres 8. Seleccionamos Fila Superior, ya que el nombre de nuestro rango será VENTAS y la palabra ventas se encuentra en la fila superior de nuestro rango seleccionado. Pegar Nombres Sirve para pegar los nombres dentro de las fórmulas a medida que se van creando, escogiéndolos de la lista que presenta la opción Pegar Aplicar Nombres Se aplica a formulas ya realizadas con direcciones de celdas, cuando se desea reemplazar por los nombres de estas celdas. 9. Se selecciona la celda que contiene la formula, clic en Insertar, Nombre, Aplicar 10. Seleccionar los nombres que se desean aplicar a la formula usando clic sostenido sobre los nombres y Aceptar.

Excel Avanzado Lectura 1.1 - Nombres

4

Ing. Leila Sofía Farfán

Ejemplo 1.1: TRABAJANDO CON NOMBRES Objetivos − Poner en práctica el uso de nombres de rangos − Crear, definir, pegar y aplicar nombres de rangos en una tabla de Excel. Ejemplo Supongamos que tenemos una tabla en Excel, con la siguiente información: Columna A: Código Artículo Columna B: V.compra Necesitamos hallar en la columna C el valor de venta para cada artículo, teniendo en cuenta que este valor es el 20% más sobre el valor de compra. También deseamos conocer en la columna D cual sería el valor de IVA para cada artículo, sobre este valor de venta 1. Digite la siguiente tabla en un libro nuevo

2. Seleccione el rango A2:C10 3. Clic en menú Insertar, Nombres, Crear 4. Aparece la ventana Crear Nombres, deje activa la opción Fila superior y Aceptar. NOTA: Para consultar los nombres creados y el rango, solo tenemos que ingresar por el menú Insertar, Nombre, Definir y seleccionar el nombre del rango, en el cuadro de dialogo de la parte inferior aparecerá el nombre de la hoja y el rango de celdas que esta definido bajo este nombre. En esta ventana aparecen todos los nombres creados.

Excel Avanzado Ejemplo 1.1 - Nombres

5

Ing. Leila Sofía Farfán

5. Ubique el cursor en la celda C3, digite el signo igual (=) para indicar que vamos a introducir una formula; vamos a calcular el valor de las ventas, usando el nombre V.COMPRA. 6. Clic en Insertar, Nombre, Pegar 7. Seleccione el nombre V.COMPRA y Aceptar 8. Digite el signo por (*) 9. Clic en la celda B13, presione la tecla F4 dos veces lpara indicarle a Excel que la fila 13 es absoluta ó fija. 10. Enter Copie la formula en las otras celdas. Rango C4:C10 Vamos a crear otros rangos. 11. Seleccione el rango A12:B13 12. Clic en Insertar, Nombre, Crear; dejar activa la opción Columna izquierda y Aceptar. 13. Clic en Insertar, Nombre, Definir, en este momento se han agregado los nuevos nombres a la lista. Ahora, vamos a reemplazar en la fórmula creada la referencia B$13 por el nombre V. Venta 14. Seleccione el rango C3:C10 15. Clic en Insertar, Nombre, Aplicar, seleccione los nombres que se necesitan para calcular el valor de venta

Excel Avanzado Ejemplo 1.1 - Nombres

6

Ing. Leila Sofía Farfán

16. Aceptar Vamos a calcular el IVA 17. Clic en la celda D3 y digite el signo igual (=) 18. Clic en Insertar, Nombre, Pegar, 19. Seleccione el nombre VENTAS, Aceptar 20. Digite el signo por (*) 21.Clic en Insertar, Nombre, Pegar 22.Seleccione el nombre IVA, Aceptar y Enter 23. Copie la fórmula en el rango D4:D10 24. Guarde el archivo con nombre Ejemplo 1

Excel Avanzado Ejemplo 1.1 - Nombres

7

Ing. Leila Sofía Farfán

Taller 1.1: TRABAJANDO CON NOMBRES Objetivo Practicar, crear y aplicar nombres Taller 1. Digite la siguiente tabla

2. Defina nombres para los rangos de las columnas B y C, ENTRADAS y SALIDAS respectivamente. 3. Calcule el saldo, usando los nombres de los rangos. 4. Guarde el archivo con nombre TALLER1

Excel Avanzado Taller 1.1 - Nombres

8

Ing. Leila Sofía Farfán

Lectura 1.2: FUNCIONES INTRODUCCION Las funciones permiten hacer más fácil el uso de Excel e incrementar la velocidad de cálculo, en comparación con la tarea de escribir una fórmula. Son más rápidas, ocupan menos en la barra de fórmulas y reducen los errores. Pueden utilizarse solas o como componentes para construir fórmulas más extensas. El uso de funciones simplifica y acorta las fórmulas en las hojas de cálculo, y es una de las herramientas más potentes de Excel. En esta guía se explican los conceptos básicos de una función, su estructura y las diferentes formas de introducir una función.

OBJETIVO − Conocer la definición de Función − Comprender la estructura y sintaxis de una función − Utilizar y comprender las diferentes formas de introducir una función

CONTENIDO Definición: Una función consiste en fórmulas abreviadas que posee Excel para solucionar aplicaciones de orden estadístico, financiero, matemático, etc. La función permite al usuario obtener resultados con tan sólo definir variables en la misma. Las funciones aceptan información o argumentos, y devuelven un resultado. Estructura La sintaxis de cualquier función es: =nombre_función(argumento1;argumento2;...;argumentoN) Siguen las siguientes reglas: − Si la función va al comienzo de una fórmula debe empezar por el signo =. − Una palabra reservada por Excel, la cual identifica el nombre de la función a ejecutar. − Los argumentos o valores de entrada van siempre entre paréntesis. No deje espacios antes o después de cada paréntesis. Los argumentos pueden ser números, texto, valores lógicos como VERDADERO o FALSO, valores de error como #N/A o referencias de celda. El argumento que se designe deberá generar un valor válido para el mismo. Los argumentos pueden ser también constantes, fórmulas u otras funciones. Excel Avanzado Lectura 1.2 - Funciones

9

Ing. Leila Sofía Farfán

Referencias de celdas: Conjunto de coordenadas que ocupa una celda en una hoja de cálculo. Por ejemplo, la referencia de celda que aparece en la intersección de la columna C y la fila 5 es C5. Constantes: Un valor numérico o de texto que se escribe directamente en una celda o en una fórmula o que se representa mediante un nombre. Por ejemplo, la fecha 12/05/04, un número como 520 y el texto “Total Ventas” son constantes. Una fórmula o un valor que sean el resultado de una fórmula no es una constante. En la mayoría de los casos, el resultado es un valor numérico, pero también pueden devolver resultados con texto, referencias, valores lógicos, valores de error o información sobre la hoja de cálculo. −

Los argumentos de la función deben estar separados por comas (,) o punto y coma (;) según la configuración del equipo

Ejemplos: =PROMEDIO(A3;A10;A15) es igual a (A3 + A10 + A15)/3 =MAX(A3;A10;A15) halla el valor máximo del valor contenido en las celdas A3, A10 y A15 =SUMA(A1:A8) sería equivalente a: =A1+A2+A3+A4+A5+A6+A7+A8

Las fórmulas pueden contener más de una función, y pueden tener funciones anidadas dentro de la fórmula. Ejemplos de tipos de Argumentos Valores numéricos individuales separados por punto y coma ó coma; • =suma(228;450;25;36) • =promedio(228;450;25;36) Referencia de celdas individuales • =suma(A5;B7;C6) • =promedio(A5;B7;C6) Referencia a una celda • •

=raiz(A5) =redondear(a5;2)

Referencia de un rango de celdas • =suma(A7:C20) • =contar(A7:C20) Referencia de varios rangos de celdas separados por punto y coma; • =promedio(B5:C10;G5:H8;K7:L9) • =suma(B5:C10;G5:H8;K7:L9) Excel Avanzado Lectura 1.2 - Funciones

10

Ing. Leila Sofía Farfán

Otra función anidada • =raiz(promedio(A5:C15)) • =redondear(raiz(promedio(A5:C15));2) Una referencia EXTERNA de otra hoja de un libro de trabajo • =promedio(HOJA2!B2:C8) • =suma(HOJA2!B2:C8) Una referencia REMOTA a otro libro de trabajo • =suma([Ventas.xls]Enero:Marzo!A5:C10) • =promedio([Ventas.xls]Enero:Marzo!A5:C10) Existen diferentes categorías de funciones; encontramos funciones matemáticas y trigonométricas, estadísticas, financieras, de texto, de fecha y hora, lógicas, de base de datos, de búsqueda y referencia y de información dependiendo del tipo de operación o cálculo que realizan. Formas de introducir una función. Las funciones se pueden utilizar de tres formas diferentes. Estas son: • Tecleándolas directamente en la celda. • Utilizando el cuadro de diálogo Insertar función. • Utilizando el Cuadro de Nombres ó Celda Activa.

Para activar el cuadro de diálogo Insertar función disponemos de dos formas diferentes: •

Seleccionando el menú Insertar y seguidamente la opción función.

•

Pulsando el botón

(Insertar función) de la Barra de fórmulas.

Ejemplos Excel Avanzado Lectura 1.2 - Funciones

11

Ing. Leila Sofía Farfán

Si queremos introducir una función en una celda: Situarse en la celda donde queremos introducir la función. 1. Seleccionar el menú Insertar. 2. Elegir la opción Función... O bien, hacer clic sobre el botón fórmulas.

de la barra de

Aparecerá la siguiente ventana:

3. En el recuadro O seleccionar una categoría tendremos que elegir a qué categoría pertenece nuestra función. Si no estamos muy seguros, podemos elegir Todas. 4. En el recuadro Seleccionar una función hay que elegir la función que deseamos haciendo clic sobre ésta. Observe como conforme seleccionamos una función, en la parte inferior nos aparece la sintaxis, los distintos argumentos y una breve descripción de ésta. 5. Seleccionemos la función MAX, hacer clic sobre el botón Aceptar.

Excel Avanzado Lectura 1.2 - Funciones

12

Ing. Leila Sofía Farfán

Excel Avanzado Lectura 1.2 - Funciones

13

Ing. Leila Sofía Farfán

Ejemplo 1.2: FUNCIONES EJEMPLO PASO A PASO Objetivo. Practicar las diferentes formas de introducir una función en una Hoja de cálculo. Ejemplo. La hoja de cálculo que vamos a crear deberá calcular las estadísticas de ventas anuales en una empresa. Cree el siguiente libro de trabajo

Una vez diseñada la Hoja pasaremos a calcular la venta media, máxima, mínima y total utilizando para ello las funciones MAX, MIN, PROMEDIO y SUMA. En nuestra práctica emplearemos el método del teclado para las funciones PROMEDIO y SUMA, el Cuadro de Nombres para la función MAX y por último la opción Insertar función para la función MIN. Método del teclado 1. Desplace el cursor a la celda E5 2. Digite =PROMEDIO(B5:B16) y pulse la tecla Enter. Usando el Cuadro de Nombres 1. Desplace el cursor a la celda E7 2. Digite el signo =. Observe como se activa la opción de selección de función.

3. Pulse sobre el botón desplegable, seleccione la función Max.

Excel Avanzado Ejemplo 1.2: Funciones

14

Ing. Leila Sofía Farfán C.

A continuación en su pantalla aparecerá un cuadro de diálogo donde se muestra el nombre de la función, los argumentos, la descripción de la función, el resultado actual de la función y el resultado actual de toda la fórmula.

En el interior del cuadro tenemos que introducir el argumento (valores que utilizará la función para hacer el cálculo). Para ello pulsaremos el botón del cuadro Número1 y seleccionaremos las referencias de celda que utilizará el argumento. del cuadro Número1. 4. Pulse el botón 5. Seleccione el rango B5:B16 y pulse Enter. 6. Pulse

para finalizar la fórmula.

Si ha realizado correctamente los pasos anteriores, el promedio de las celda E5 será igual a $390.833 y el máximo de la celda E7 será igual a $850.000 Usando la opción Insertar función 1. Desplace el cursor a la celda E9 2. Pulse el botón (Insertar función) de la barra de fórmulas o bien seleccione el menú Insertar / Función. En su pantalla aparecerá la ventana Insertar Función:

Excel Avanzado Ejemplo 1.2: Funciones

15

Ing. Leila Sofía Farfán C.

3. Seleccione Estadísticas de la opción O seleccionar una categoría. 4. Digite M para que nos muestre las funciones que empiezan por esta letra, desplácese con la Barra de desplazamiento hasta que se muestre la función MIN y haga clic sobre ella. 5. Pulse el botón con lo que aparecerá el cuadro de diálogo que estudiamos anteriormente. 6. Pulse el botón del cuadro Numero 1. 7. Seleccione el rango B5:B16 y pulse Enter. 8. Pulse a $50.000.

para finalizar la fórmula. El resultado de la celda E9 será igual

Para el cálculo de ventas totales utilizaremos el botón de herramientas. Realice los siguientes pasos:

(Autosuma) de la barra

1. Desplace el cursor a la celda E11. (Autosuma) 2. Pulse el botón 3. Seleccione el rango B5:B16 y pulse la tecla Enter. 4. Guarde el archivo con nombre Ejemplo 2

Excel Avanzado Ejemplo 1.2: Funciones

16

Ing. Leila Sofía Farfán C.

Taller 1.2: FUNCIONES Objetivo Practicar las diferentes formas de introducir una función en una Hoja de cálculo. Ejercicio 1 1. Cree el siguiente libro de trabajo

Halle los totales para cada columna usando el botón autosuma Halle el valor promedio para cada columna usando el método del teclado. Halle el valor máximo para cada columna usando el cuadro de nombres. Halle el valor mínimo para cada columna usando la opción insertar función. 6. Guarde el taller con nombre PRESTAMOS

2. 3. 4. 5.

Al final del ejercicio su hoja de cálculo, mostrara los siguientes resultados:

Ejercicio 2. 1. Cree el siguiente libro de trabajo

Excel Avanzado Taller 1.2: Funciones

17

Ing. Leila Sofía Farfán C.

2. 3. 4. 5. 6.

Halle los totales para cada columna usando el botón autosuma Halle el valor promedio en pesos usando el método del teclado. Halle el valor máximo en pesos usando el cuadro de nombres. Halle el valor mínimo en pesos usando la opción insertar función. guarde el taller con nombre EUROS

Al final del ejercicio su hoja de cálculo, mostrara los siguientes resultados:

Excel Avanzado Taller 1.2: Funciones

18

Ing. Leila Sofía Farfán C.

Lectura 1.3: FUNCIONES LOGICAS Introducción Excel puede realizar operaciones, escribir valores, de acuerdo con la información de otras celdas, dependiendo de si se cumple o no una condición. Este tipo de funciones son importantes para el análisis de valores y de acuerdo a los resultados permiten tomar decisiones. Hoy, estudiaremos las funciones lógicas y su utilidad en la toma de decisiones. Objetivo − Adquirir conocimientos sobre el manejo y aplicación de funciones lógicas. − Conocer la sintaxis de las funciones lógicas − Comprender la utilidad del uso de funciones lógicas Función =SI El uso de funciones condicionales nos permite tomar una decisión dependiendo de una condición; lo cual posibilita que una celda o rango adopten distintos valores en función de cuáles sean los valores de otras celdas (sea de la misma hoja, de otras hojas del mismo libro o, incluso, de libros diferentes).

Condición

Proceso Verdadero

Proceso Falso

Si la Condición es verdadera, entonces realiza Proceso Verdadero, en caso contrario realiza Proceso Falso En su formato más simple, la función =SI esta compuesta de tres argumentos separados por punto y coma (;) la sintaxis de la función condicional es: =Si(Condición; valor verdadero; valor falso) Así, el valor que se le asignará a la celda que contenga esta fórmula será el que se derive del valor verdadero si la condición es cierta y el de valor falso en caso de que la condición sea falsa. La condición puede ser más o menos simple; algo similar ocurre con las acciones. Analicémoslo más detenidamente. Excel Avanzado Lectura 1.3: Funciones Lógicas

19

Ing. Leila Sofía Farfán

Condición: Es una indagación que se hace acerca del un valor, una cadena de caracteres, una celda determinada, es una expresión lógica tal como A5>2*C3 ó C3 >= = salario mínimo;0;pague subsidio) En este ejemplo se esta preguntado que SI el salario base es mayor que el salarió mínimo, si es verdadero; el resultado de la formula será cero (0); en caso contrario deberá pagar subsidio. =SI(A8=B1;A8+5;A8*5) En este otro ejemplo se esta preguntado que SI el contenido de A8 es igual al contenido de la celda B1, si es verdadero; debe sumarle 5 al contenido de A8; en caso contrario es decir es falsa la condición, debe Multiplicar por 5 el contenido de A8. Operadores lógicos: Los operadores lógicos permiten unir varias condiciones en una sola, se pueden realizar varias preguntas a la vez. De acuerdo con el operador que se esté usando se obtendrá el resultado verdadero o falso. Función lógica Y

Excel Avanzado Lectura 1.3: Funciones Lógicas

20

Ing. Leila Sofía Farfán

Operador de inclusión, si se unen dos condiciones con el operador Y, deben cumplir ambas condiciones para asumir la expresión verdadera. Sintaxis: =Y(condición 1; condición 2; condición 3;...) Si todas las condiciones se cumplen devuelve la expresión verdadero, basta con que una sea falsa para que el resultado sea falso, tal como se muestra en la tabla de verdad. . cond1 cond2 F F F V V F V V

Y F F F V

Tabla de verdad de la función lógica Y Función Lógica O Operador de exclusión, analiza dos o más condiciones. Si se aplica este tipo de operador analizando dos condiciones, basta con que una de ellas se cumpla para asumir la expresión verdadera. Sintaxis: =O(condición 1; condición 2; condición 3;...) Sólo será Falsa, si todas las condiciones son falsas. cond1 cond2 F F F V V F V V

O F V V V

tabla de verdad de la función lógica O Función Lógica NO Operador de negación, niega el valor de la condición NO(condición) devuelve verdadero si la condición es falsa y viceversa. cond

NO

F V

V F

Excel Avanzado Lectura 1.3: Funciones Lógicas

21

Ing. Leila Sofía Farfán

Tabla de verdad de la función lógica NO Como puede observarse, esta función se puede obviar, ya que podemos hacer lo mismo mediante el uso correcto de los operadores lógicos, como vemos en el ejemplo siguiente: NO(A1>B1) es igual que A1=3.5 y Enter 8. En la barra de formulas debe aparecer la condición

botón del cuadro

9. Ubique el cursor en el cuadro de dialogo del segundo argumento Valor_si_verdadero y digite "Apto". 10. Ahora en el tercer argumento y digite "Deficiente"

Excel Avanzado 24 Ejemplo 1.3: Funciones Lógicas y Estadísticas.

Ing. Leila Sofía Farfán C.

11. Clic en el botón 12. Repita los pasos para C4 y C5, copie la función de C6 en adelante. 13. Guarde el archivo con nombre NOTAS Ejercicio Paso a Paso 2 En el siguiente ejercicio, vamos a calcular el subsidio de transporte para los empleados de una empresa. Si el salario del empleado es < que dos salarios básico legales, entonces, tiene derecho a subsidio de transporte, sino, no se le paga subsidio. Observe que el mismo planteamiento del problema, me indica que función se debe usar. 1. Cree la siguiente tabla

2. Ubicar el cursor en la celda C7 3. Ingresar a funciones, seleccione funciones lógicas, función SI Excel Avanzado 25 Ejemplo 1.3: Funciones Lógicas y Estadísticas.

Ing. Leila Sofía Farfán C.

4. Los Argumentos quedarán como en el siguiente cuadro

Al final en la barra de formulas nos quedará: =SI(B7C8; 1;SI(C7C8), quiere decir que C7 es menor que C8, por lo tanto la segunda condición (C7B1;"Al es mayor que B1";SI(Al=B1;"A1 es igual que B1";SI(A1B1;"A1 es mayor que B1 ";SI(A1=B1;"A1 es igual que B1";"A1 es menor que B1")) Excel Avanzado 32 Ing. Leila Sofía Farfán Lectura 2.1: Funciones Anidadas

Función anidada SI - Y La función Y suele utilizarse conjuntamente con la función SI. Nos permite realizar en lugar de una pregunta, varias. Y sólo se realizará el argumento situado en la parte verdadero del SI, sí el resultado de la función Y es verdadero, es decir, en el momento que todas las condiciones sean verdaderas. =SI(Y(condición 1; condición 2; ... ; condición 30); acción l; acción 2) Si el resultado de la función Y es falso, se ejecuta la acción 2. En las funciones anidadas siempre se desarrolla primero el paréntesis más interno dentro de la formula Función anidada SI - O Dentro de la función =SI =SI(O(condición 1;condición 2; ... ;condición 30);acción 1; acción 2) En esta ocasión, la evaluación de la condición difiere: para que el resultado de la función O sea verdadero, al menos una de las condiciones ha de serlo, ejecutándose la acción 1. Sólo en el caso de que todas las condiciones sean falsas, el resultado de la función O lo será y se ejecutará la acción 2. Al igual que la función Y, primero se desarrolla la función más interna, en este caso, primero se desarrolla la función O, y el resultado de esta, será el primer argumento de la función SI.

Excel Avanzado Lectura 2.1: Funciones Anidadas

33

Ing. Leila Sofía Farfán

Ejemplo 2.1: FUNCIONES ANIDADAS Objetivos − Aplicar el uso de funciones anidadas − Utilizar =SI anidados para validar información con diferentes opciones − Anidar diferentes funciones lógicas. Ejemplo 1 Teniendo en cuenta la información del archivo NOMINA, vamos a suponer que esta empresa, también asigna un subsidio de alimentación y de estudio a sus empleados si cumplen ciertas condiciones: Para el subsidio de alimentación ∗ Si el salario es >= de $900000, no tiene auxilio de alimentación ∗ Si el salario es >= de $600000 y < de $900000 tiene un auxilio del 5% de su salario base; si el salario es < de $600000, entonces el auxilio es del 10% de su salario. Para el auxilio de Estudio El auxilio de estudio se otorga teniendo en cuenta si el salario del empleado es = $900000, si esto es falso, quiere decir que el valor es menor a $900000; por lo tanto en la segunda opción sólo debemos validar si es >= que $600000, si es falso, es porque el valor es menor de $600000, por lo tanto no debemos validar esta opción. Siguiendo las reglas de SI anidados, el número de SI anidados es el número de opciones – 1, tenemos tres opciones, nuestra fórmula constara de 2 SI anidados. 3. En la celda E11, digite la siguiente fórmula =SI(C11>=B$5;C5;SI(C11>=B$6;C11*C$6;C11*C$7)) Pasemos la fórmula a palabras, reemplazado los valores de las respectivas celdas usadas en la fórmula: SI $500000 es >= que $900000; si es verdadero, entonces, el resultado de la función será 0, pero si es falso; quiere decir, que el sueldo es menor de $900000, pero no sabemos si es >= a $600000, por lo tanto, debemos preguntar de nuevo SI $500000 es >= que $600000, si la condición es verdadera, el resultado de la función será $500000 * 5%, pero si es falsa, quiere decir que el sueldo es menor de $600000 y por lo tanto el resultado de la función será $500000 * 10% 4. Copie la función en el rango E12:E20. Ahora hallaremos quienes tienen derecho a Subsidio de Estudio. 5. En la celda F11, digite la siguiente fórmula =SI(Y(C11=2);C$8*B11;C$5) Primero se desarrolla la función Y, el resultado de esta función será el primer argumento de la función SI. La función Y será verdadera, si ambas condiciones son verdaderas de lo contrario será falso; es decir; que el salario sea =2. (Así se lee la función Y) Miremos la función SI Si la función Y da Verdadero, entonces, el resultado de la función SI será los $25000 por el número de hijos, en caso contrario, si el resultado de la función Y es Falso, el resultado será cero. 6. Copie la función en el rango F12:F20 Su tabla en este momento, debe tener el siguiente aspecto: Excel Avanzado Ejemplo 2.1: Funciones Anidadas

35

Ing. Leila Sofía Farfán C.

Cambie diferentes valores en las columnas B y C; observe como cambian los resultados en las columnas D, E y F. Deben funcionar para cualquier caso. 7. Guarde el archivo con los datos iniciales. Ejemplo 2 Teniendo en cuenta la información del archivo NOTAS, vamos a suponer que son notas de la universidad X y desea obtener un reporte de un grupo de estudiantes de primer semestre de ingeniería, quienes aprobaron todos los módulos, quienes hacen curso de vacaciones y quienes pierden el semestre. Las condiciones son las siguientes: Los módulos son tres: modulo de humanidades, módulos transversales y módulos especializados ∗ Si el alumno aprueba los tres módulos, aprueba el semestre. ∗ Si el alumno aprueba uno de los tres módulos, pasa a curso de vacaciones ∗ Si el alumno pierde los tres módulos, pierde el semestre. El módulo se aprueba si la nota es mayor o igual a 3,5 sobre 5,0

Excel Avanzado Ejemplo 2.1: Funciones Anidadas

36

Ing. Leila Sofía Farfán C.

También se desea saber cuantos alumnos aprobaron, cuantos perdieron y cuantos pasaron a curso de vacaciones. 1. Abrir el Archivo Notas 2. Modifíquelo como en la imagen

3. En la celda E4, digite la siguiente fórmula: =SI(Y(B4>=B$16;C4>=B$16;D4>=B$16);"aprueba";SI(O(B4>=B$16;C4>=B$16;D 4>=B$16);"curso de vacaciones";"pierde")) Desarrollemos la Y, Y(B4>=B$16;C4>=B$16;D4>=B$16) Solo será Verdadera cuando las tres notas sean > ó = a 3,5, con una condición que no se cumpla, es decir con una nota < a 3,5 el resultado de la función Y será falso. Ahora la función O O(B4>=B$16;C4>=B$16;D4>=B$16) Solo será Falsa, cuando las tres notas sean < a 3,5, con una sola condición que sea verdadera, es decir que sea >= a 3,5, el resultado de la función será Verdadero. Ahora el SI externo Si el resultado de la función Y es verdadero, el resultado de este SI será Aprueba, y termina la formula; en caso contrario, si el resultado de la función Y es falso, pasará a ejecutar el SI interno. Excel Avanzado 37 Ing. Leila Sofía Farfán C. Ejemplo 2.1: Funciones Anidadas

SI Interno Si el resultado de la función O es Verdadero, el resultado de este SI será Curso de Vacaciones; en caso contrario, si el resultado de la función O es falso, el resultado del SI interno será pierde. Observe que cuando un argumento es texto, el testo va entre comillas. Esta regla aplica a todas las funciones. 4. Copie la fórmula en el rango E5:E13 5. Guarde el archivo. Si Usted hizo todos los pasos, su tabla debe presentar los siguientes resultados.

Excel Avanzado Ejemplo 2.1: Funciones Anidadas

38

Ing. Leila Sofía Farfán C.

Taller 2.1: FUNCIONES ANIDADAS Objetivos − Aplicar el uso de funciones anidadas − Anidar diferentes funciones lógicas. Ejercicio 1 Usted trabaja en una Universidad, y se va ha realizar la fiesta de integración semestral, pero la universidad para cuidarse en salud con los menores de edad y las alumnas tiene las siguientes condiciones para poder asistir. ∗ Los alumnos sin importar el sexo, si son mayores de edad, pueden asistir sin problema. ∗ Si son hombres y son menores de edad, deben traer permiso ∗ Las niñas menores de edad no pueden asistir Este reporte debe entregarlo en cada portería del sitio donde se realizará el evento, para poder controlar esta asistencia. El listado de alumnos es el siguiente,

Al final del ejercicio su hoja de cálculo, mostrara los siguientes resultados:

Excel Avanzado Taller 2.1: Funciones Anidadas

39

Ing. Leila Sofía Farfán

Guarde el archivo con nombre Primíparos.

Excel Avanzado Taller 2.1: Funciones Anidadas

40

Ing. Leila Sofía Farfán

Lectura 2.2: FUNCIONES DE BUSQUEDA INTRODUCCION En esta lección se explicara la sintaxis y utilidad de algunas funciones de esta categoría.

OBJETIVO − Conocer su sintaxis − Identificar sus argumentos − Reconocer la utilidad de trabajar funciones de búsqueda en una hoja de Excel

Definición Las funciones de búsqueda y referencia, son aquellas que nos permiten buscar un valor en listas o tablas de Excel. Función BUSCARV O búsqueda vertical, es una de las funciones más usadas al lado de la función =SI. Nos permite conociendo un valor encontrar otro. Sintaxis =BUSCARV(Valor_Buscado;Matriz_donde_Buscar;número_columna;Ordenado)

Busca un valor específico en la columna más a la izquierda de una matriz y devuelve el valor de la celda cuya fila sea la fila donde encuentre el valor y cuya columna sea aquella de la matriz que corresponda a un número especificado. Esta función se utiliza normalmente para obtener un valor correspondiente a otro dado. − Ingresar por Insertar, Función − Seleccionar en O seleccionar una categoría, la opción Búsqueda y Referencia

Excel Avanzado 41 Lectura 2.2: Funciones de Búsqueda

Ing. Leila Sofía Farfán

− Seleccionar BUSCARV y Aceptar − Excel nos mostrará la ventana para ingresar los argumentos necesarios para trabajar esta función.

Valor Buscado: es un valor conocido y que se puede comparar, ejemplo si conocemos el apellido de una persona, puedo buscar su número telefónico en el directorio. En este ejemplo el apellido es el valor buscado, y el directorio telefónico la matriz. Matriz donde Buscar: Es el conjunto de información, donde se busca el valor buscado. Número de Columna: Es el número de columna de la matriz donde se encuentra la información que debe regresar. Es decir, si la matriz consta de tres columnas, y en la tercera columna esta la información que se esta buscando, el número de columna, será igual a 3. Ordenado: Es un valor lógico, Verdadero ó Falso. Si la función no encuentra el valor buscado en la matriz y Ordenado es Verdadero, regresará el valor más alto de la matriz pero que sea inferior al buscado, y si es falso regresa el error #N/A. Función BUSCARH Ó Búsqueda Horizontal, Esta función Busca en la primera fila de una tabla o matriz de valores y devuelve el valor en la misma columna desde una fila especificada. Para utilizar esta función es necesario que los datos de la tabla en donde se realizará la búsqueda estén dispuestos en forma Horizontal, es decir, los títulos de la tabla estarán ubicados en las filas. Sintaxis: =BUSCARH(valor_buscado,matriz_buscar_en,indicador_filas,ordenado) Excel Avanzado 42 Lectura 2.2: Funciones de Búsqueda

Ing. Leila Sofía Farfán

En donde: valor_buscado: es el valor que se busca en la primera fila de la tabla o matriz de datos, puede ser un valor, una referencia o una cadena de texto. Matriz_buscar_en: es la tabla en donde se encuentran los datos de donde se extraerá la información. Es aconsejable manejar nombres para las tablas como se explicó en la lectura 1.1 Trabajando con Nombres. Indicador_filas: es el número de fila desde el cual se deberá devolver el valor encontrado. Si número-fila es menor que 1, BUSCARH devuelve un valor de error; si número-fila es mayor que el número de filas en nombre-tabla, BUSCARH devuelve el valor de error. Ordenado: Indica si el dato que se está buscando se encuentra o no ordenado en la matriz donde se encuentra. Se debe escribir Falso o Verdadero según corresponda. Función Coincidir Busca un valor en una matriz y regresa un número que es la posición dentro de ella. Nos sirve para confirmar, si un dato existe o no dentro de una lista. Sintaxis =COINCIDIR(Valor_buscado;Matriz donde Buscar) Esta función sólo regresa dos resultados: − El error #N/A, cuando no encuentra el valor buscado en la matriz − Un número, cuando lo encuentra e indica la posición del valor buscado dentro de la matriz. Normalmente la columna es una sola columna o fila, es decir si yo estoy buscando si un código existe, solo debe buscar en la columna o fila donde se encuentra la lista de códigos.

Excel Avanzado 43 Lectura 2.2: Funciones de Búsqueda

Ing. Leila Sofía Farfán

Ejemplo 2.2: FUNCIONES DE BUSQUEDA Y REFERENCIA OBJETIVO − Poner en práctica el uso de funciones de búsqueda − Comprender la utilidad de las funciones de búsqueda

Ejemplo 1 Nuevamente vamos a trabajar con el archivo NOMINA, ahora vamos a suponer que esta empresa tiene una categoría de cargos, los cuales tienen un código que identifica la categoría y que dependiendo de eso código de cargo se asigna el nombre del cargo y el salario base. Para trabajar las funciones de búsqueda, vamos a modificar un poco la tabla. Este es su nuevo aspecto

Note que se han borrado los datos de la columna Salario, ya que lo vamos a calcular con la función =BUSCARV. También fíjese que en las columnas J y L, tienen información, esto ocurre, porque al borrar los salarios, las funciones que están aplicadas toman el nuevo valor del salario, para nuestro caso cero, por lo tanto para la función, todos tiene derecho a subsidio de Transporte y los que tienen 2 o más hijos tienen derecho a subsidio de estudio. Vamos a hallar primero los cargos usando una función =BUSCARV 1. Clic en la celda G14 2. Menú Insertar, Función Excel Avanzado 44 Ejemplo 2.2: Funciones de Búsqueda

Ing. Leila Sofía Farfán C.

3. Seleccione categoría Búsqueda y referencia 4. Seleccione función BUSCARV Aparece la siguiente ventana

5. 6. 7. 8.

Clic en el cuadro de dialogo Valor Buscado, seleccione la celda F14 Clic en Matriz_buscar_en, seleccione el rango H4:J11 Clic en Indicador de columnas, digite 2 Clic en Ordenado, digite Falso

En otras palabras, nuestro valor buscado es el CodCargo, es lo que conocemos de cada uno de nuestros empleados y podemos ir a comparar en la matriz, Indicador de columnas es 2, ya que nuestra matriz esta compuesta de tres columnas y en la columna 2 se encuentra la información que debe regresar, es decir el nombre del cargo, según el código cargo 9. Aceptar. No se olvide de aplicar absolutas. 10. Copie la fórmula en el rango G15:G23 Vamos a hallar el salario 11. Digite la siguiente fórmula en la celda H14 =BUSCARV(F14;H$4:J$11;3;FALSO) Es decir, busque el código del cargo en la matriz H$4:J$11, cuando lo encuentre, regrese el contenido de la columna 3, es decir, el salario. 12. Copie la fórmula hacia abajo, para los otros empleados. Excel Avanzado 45 Ejemplo 2.2: Funciones de Búsqueda

Ing. Leila Sofía Farfán C.

En este momento se actualizan todos los valores de subsidio, teniendo en cuenta las condiciones trabajadas en la sesión anterior. 13. En la celda I14, calculamos el sueldo base, teniendo en cuenta el número de días trabajados. Digitar la siguiente formula: =H14/30*E14 Es decir, el salario dividido 30 días, para hallar el valor de un día y multiplicado por el número de días trabajados. 14. Copiar la formula en el rango I15:I23 15.También debemos arreglar la formula del subsidio de transporte, y así pagar el proporcional al número de días trabajados. En J14, debemos dividir el subsidio en 30 y multiplicarlo por el numero de días trabajados =SI(H14, =, 50000. 8. Clic en el botón

del campo Sexo

Excel aplica automáticamente el filtro mostrando solo los registros con ese contenido específico, ocultando los otros registros de la lista de información.

Vamos a filtrar esta lista y así obtener que empleados de sexo femenino recibieron subsidio de alimentación superior a 50000 pesos. Para realizar este filtro utilizaremos la opción Personalizar. Excel Avanzado Ejemplo 3.2: Filtrar listas

74

Ing. Leila Sofía Farfán C.

9. Clic en el botón del campo Sub. Alimentación. 10. Seleccione la opción Personalizar. En su pantalla aparecerá el cuadro de Autofiltro personalizado del primer cuadro 11. Pulse el botón 12. Seleccione la opción es mayor que 13. Clic en el segundo cuadro y digite 50000 Si ha realizado los pasos correctamente, el cuadro Autofiltro debe tener el siguiente aspecto:

personalizado

14. Clic en el botón Aceptar. Sólo cuatro registros cumplen los criterios asignados, que sean de Sexo Femenino y reciban un Subsidio de alimentación superior a 50000 pesos.

Los filtros aplicados aparecen en otro color. 15. Clic en el botón 16. Clic en el botón

del campo Sexo y seleccione Todas. del campo Sub. Alimentación y seleccione Todas.

Nuevamente obtenemos nuestra lista original. Excel Avanzado Ejemplo 3.2: Filtrar listas

75

Ing. Leila Sofía Farfán C.

Solución Punto c. Empleados que trabajaron entre 15 y 20 días. Usaremos la opción Personalizar. 17. Clic en el botón del campo Días trabajados. 18. Seleccionar la opción Personalizar. 19. Completar el cuadro Autofiltro personalizado como en el gráfico:

20. Como se deben cumplir las dos opciones, es decir que sea mayor o igual a 15 Y menor o igual a 20, seleccione la opción Y. 21. Clic en Aceptar. Sólo tres registros cumples los criterios.

22. Clic en el botón

del campo Días trabajados y seleccione Todas.

Obtenemos nuestro listado original. Ahora desactivaremos la opción Autofiltro. 23. Ubique el cursor en cualquier celda de la lista. 24. Clic en menú Datos, Filtro, Autofiltro.

Ejemplo 2 – Filtro Avanzado Para seleccionar registros conservando los criterios anteriores que vamos utilizando, tenemos que emplear la opción Filtro Avanzado. Aplicaremos los mismos ejemplos, pero usando esta opción. Excel Avanzado 76 Ing. Leila Sofía Farfán C. Ejemplo 3.2: Filtrar listas

Solución Punto a. Filtro por CodCargo Vendedor El primer ejercicio que desarrollaremos, seleccionará las personas que sean Vendedores. En este caso no utilizaremos la opción Copiar a del cuadro Filtro avanzado para crear una copia de la selección. 1. Marque el rango de celdas A13:L13 2. Clic en el botón Copiar. 3. Sitúese en la celda A40. 4. Clic en el botón

Pegar.

A continuación asignaremos el criterio en el campo CodCargo. 5. 6. 7. 8.

Seleccione la celda G41. Digite Vendedor y pulse la tecla Enter. Sitúe el cursor en cualquier celda del rango A13:L33 Clic en menú Datos, Filtro, Filtro Avanzado

En este ejemplo vamos a utilizar la opción activa por defecto Filtrar la lista sin moverla a otro lugar.

Compruebe que el rango de la lista que ha seleccionado Excel es correcto, para nuestro ejemplo A13:L33

El siguiente paso es asignar el rango de criterios. 9. Haga clic en el botón de la opción Rango de criterios. 10. Seleccione el rango A40:L41 En este momento el cuadro Filtro avanzado – Rango de criterios debe ser igual al siguiente:

11. Clic en Aceptar para ejecutar el filtro. Los registros seleccionados son: Excel Avanzado Ejemplo 3.2: Filtrar listas

77

Ing. Leila Sofía Farfán C.

12. Para volver a visualizar todos los registros tenemos que utilizar la opción Datos, Filtro, Mostrar todo.

Solución Punto b. Filtro por sexo Femenino y con Subsidio de Alimentación > 50000. Cuando usamos Filtro avanzado, se ocultan los registros que no cumplen la condición o criterio, utilizando la opción Copiar a otro lugar y el cuadro Copiar a podemos duplicar los registros y conservar la lista original. Los pasos a realizar son muy parecidos al ejemplo anterior, sólo que ahora tenemos que añadir un rango de extracción para aquellos registros que cumplan el o los criterios. 13. Marque el rango de celdas A13:L13 14.Clic en el botón Copiar. 15.Sitúese en la celda A43. 16.Clic en el botón

Pegar.

Ahora asignaremos los criterios 17. Digite F en la celda C44 18. Digite >50000 en la celda K44

Cuando los criterios se encuentran en la misma fila, como en este ejemplo estamos aplicando un Y, es decir, que los registros deben cumplir las dos condiciones: que sean de Sexo F y que el Subsidio de alimentación sea >50000. Excel Avanzado Ejemplo 3.2: Filtrar listas

78

Ing. Leila Sofía Farfán C.

19. Sitúese en una de las celdas que forman la lista A13:L33 20. Seleccione menú Datos, Filtro, Filtro Avanzado 21. Haga clic en la opción Copiar a otro lugar para activarla. de la opción Rango de criterios. 22. Clic en el botón 23.Seleccione el rango A43:L44 24. Pulse el botón de dicho cuadro para regresar al cuadro Filtro Avanzado 25. Clic en el botón de la opción Copiar a. 26. Clic en la celda A47. 27. Pulse el botón Compruebe que el cuadro Filtro Avanzado está completo como el de la figura:

El nombre de la hoja puede variar. 28. Clic en Aceptar. 29. Usando la barra de desplazamiento desplácese hasta la celda A47 para poder ver el resultado. Realicemos el mismo ejemplo pero aplicando el operador lógico O 30. Marque el rango de celdas A13:L13 31.Clic en el botón Copiar. 32.Sitúese en la celda A53. 33.Clic en el botón

Pegar.

Ahora asignaremos los criterios 34. Digite F en la celda C54 35. Digite >50000 en la celda K55 Excel Avanzado Ejemplo 3.2: Filtrar listas

79

Ing. Leila Sofía Farfán C.

Cuando los criterios se encuentran en diferente fila como en este ejemplo estamos aplicando un O, es decir, que si una de las dos condiciones se cumple (que sean de Sexo F ó que el Subsidio de alimentación sea >50000), se mostrará el registro en el rango solución. 36. Sitúese en una de las celdas que forman la lista A13:L33 37. Seleccione menú Datos, Filtro, Filtro Avanzado 38. Haga clic en la opción Copiar a otro lugar para activarla. de la opción Rango de criterios. 39. Clic en el botón 40.Seleccione el rango A53:L55 41. Pulse el botón de dicho cuadro para regresar al cuadro Filtro Avanzado 42. Clic en el botón de la opción Copiar a. 43. Clic en la celda A57. 44. Pulse el botón Compruebe que el cuadro Filtro Avanzado está completo como el de la figura:

45. Clic en Aceptar.

46. Guarde Excel Avanzado Ejemplo 3.2: Filtrar listas

el 80

archivo. Ing. Leila Sofía Farfán C.

Taller 3.2: FILTROS OBJETIVO − Practicar y aplicar lo aprendido − Identificar registros, campos, nombres de campos − Evidenciar su habilidad y destreza para filtrar listas. Ejercicio 1 Empresa Frituras Colombia El Gerente de Frituras de Colombia, desea un informe donde pueda visualizar que productos compro CARULLA. También desea un listado de aquellos productos cuyo costo sea menor o igual a 360 pesos. Para finalizar desea obtener un listado, sin borrar la lista inicial, donde se informe si Carrefour realizo compras mayores a 500000 pesos y menores a 800000. Ejercicio 2 Biblioteca Mi Primer Librito. En la Biblioteca Mi Primer Librito en el Tercer piso en el Costado derecho se presento una sobrecarga eléctrica y se han quemado algunos libros, por lo tanto se necesita de carácter urgente un listado de los títulos de libros que estaban en este sector y así conocer que libros se perdieron y poder adquirirlos nuevamente. Una de las mayores demandas de préstamo de la biblioteca son los libros de Literatura y Lingüística, por lo tanto se desea saber que títulos se encuentran disponibles para poder ofrecerles con certeza.

Excel Avanzado Taller 3.2: FILTROS

81

Ing. Leila Sofía Farfán C.

Lectura 3.3: SUBTOTALES OBJETIVO − Crear resúmenes de listas − Agrupar listas − Aplicar operaciones sobre listas CONTENIDO Una herramienta poderosa de Excel consiste en la posibilidad de aplicar una o más funciones (suma, promedio, máximo, etc.) sobre rangos de información por un campo determinado. En primer lugar, el usuario debe asegurarse de que el rango o lista en donde se aplicarán subtotales tenga una o más columnas en donde se puedan generar grupos de datos al ordenar. Antes de Subtotalizar, siempre se debe ordenar por el criterio que se desea el resumen. Una vez se ha hecho el ordenamiento, basta con ubicarse dentro de la lista o rango a subtotalizar y escoger la opción Subtotales del menú Datos. En la pantalla aparecerá la siguiente ventana:

En la línea Para cada cambio en, se debe especificar por cual columna se ha de subtotalizar, (tener en cuenta que esta columna debe estar ordenada y que al aplicar la función de subtotal se formarán los respectivos grupos). Si se oprime clic sobre la flecha de desplazamiento que está a la derecha del cuadro, se presentará el listado de los títulos de cada columna, permitiendo seleccionar el campo de agrupamiento. Por defecto la operación de subtotalización se ha de realizar con la operación suma; sin embargo se puede seleccionar otro tipo de operación en la línea Usar función, la cual contiene el listado de las operaciones posibles a ejecutar. Excel Avanzado Lectura 3.3: Subtotales

82

Ing. Leila Sofía Farfán C.

Finalmente, se debe indicar en qué columnas se debe aplicar la función seleccionada en donde Excel realizará la respectiva operación cada vez que cambie el dato definido en la primera línea. Si se activa la opción Reemplazar subtotales actuales, los subtotales existentes en la lista se actualizarán en caso de haber modificado algún registro de la lista, o de generar otra operación. Si se desea definir cada agrupamiento en una página diferente, se debe activar la opción Salto de página entre grupos. La casilla Resumen Debajo de los datos escribe las operaciones aplicadas, y los títulos de columnas en donde se aplicó dicha operación. Si se ha realizado la operación de subtotales y se desea cancelar el esquema obtenido, se debe situar nuevamente dentro del rango, entrar nuevamente a subtotales y oprimir clic sobre el botón Quitar todos; de esta forma, la lista volverá a su estado original.

Excel Avanzado Lectura 3.3: Subtotales

83

Ing. Leila Sofía Farfán C.

Ejemplo 3.3: Subtotales OBJETIVO − Aplicar subtotales a una lista de información

Ejemplo d. Ahora necesitamos un listado que nos informe cuantas personas de sexo femenino y cuantas de sexo femenino existen en nuestra nómina. e. También se desea obtener otro listado por cargo, cuanto sumo el Subsidio de alimentación y de estudio f. Y para terminar, un campo CodCargo.

último listado de cual es el promedio de hijos por

Solución Punto a. Subtotal por Sexo Primero se debe ordenar la lista ó base de datos por el campo Sexo para el cual se van a crear los subtotales. a. Clic en cualquier celda de la columna C b. Clic en el botón

Orden Ascendente de la barra de herramientas.

Una vez ordenada la lista ya se puede aplicar los Subtotales. Por cada subtotal crearemos una copia de la hoja, se recomienda tener sólo una opción de subtotales por hoja. c. Clic en menú Edición, Mover o copiar hoja,

d. Activar la opción Crear una copia. e. Aceptar. f. Seleccionar menú Datos, Subtotales. En su pantalla aparece la siguiente figura:

Excel Avanzado Ejemplo 3.3: Subtotales

84

Ing. Leila Sofía Farfán C.

Para cada cambio en: seleccione el campo por el cual ordenó la lista. Para nuestro ejemplo por Sexo. En Usar función: seleccionar la función que se va a utilizar para calcular los subtotales. En nuestro ejemplo deseamos saber cuantos hombres y cuantas mujeres trabajan en la empresa. Seleccionamos función Cuenta. En la lista Agregar Subtotal a: Activar las casillas de los campos numéricos que va a subtotal izar; se puede utilizar campos tipo texto para subtotal izar utilizando la función Cuenta.

g. Clic en Aceptar. Observe los diferentes niveles de resumen de los Subtotales, utilizando los símbolos numéricos del esquema generado por Excel. Niveles de agrupamiento

Subtotal por sexo F

Subtotal por sexo M

Subtotal General El número muestra el resumen Total General h. Clic en el número En número muestra el resumen general por cada subtotal i. Clic en el número Excel Avanzado Ejemplo 3.3: Subtotales

85

Ing. Leila Sofía Farfán C.

En número muestra todo el detalle del subtotal j. Clic en el número

Solución Punto b. Subtotal por campo NombreCargo del Subsidio de alimentación y de estudio. k. Crear una copia de la hoja sin subtotales. Ordenar la lista ó base de datos por el campo NombreCargo. l. Clic en cualquier celda de la columna G m. Clic en el botón

Orden Ascendente de la barra de herramientas.

Una vez ordenada la lista ya se puede aplicar los Subtotales. n. Seleccionar menú Datos, Subtotales. o. Llenar los diferentes cuadros como aparece en el gráfico.

p. En Agregar subtotal a: sólo dejar activas las casillas de Sub. Alimentación y Sub. Estudio. q. Aceptar. En su pantalla aparece la siguiente figura:

Excel Avanzado Ejemplo 3.3: Subtotales

86

Ing. Leila Sofía Farfán C.

Para eliminar subtotales siga los siguientes pasos: r. Clic en cualquier celda de la lista con subtotales s. Menú Datos, Subtotales t.

Clic en el botón

Solución Punto c. Subtotal por Codcargo, promedio de hijos. u. Ordenar por Codcargo v. Seleccionar menú Datos, Subtotales. w. Llenar la ventana Subtotales con la siguiente información

x. Aceptar. Obtendremos el siguiente gráfico:

Excel Avanzado Ejemplo 3.3: Subtotales

87

Ing. Leila Sofía Farfán C.

Excel Avanzado Ejemplo 3.3: Subtotales

88

Ing. Leila Sofía Farfán C.

Taller 3.3: SUBTOTALES OBJETIVO − Practicar y aplicar lo aprendido − Identificar registros, campos, nombres de campos − Evidenciar su habilidad y destreza para subtotal izar listas. Ejercicio 1 Empresa Frituras Colombia Frituras de Colombia, dentro de su programación semanal de inventarios requiere conocer mediante un informe las ventas en unidades y pesos por cada uno de los productos. También desea un informe semanal por Cliente de cuanto sumo el descuento efectuado en las compras. Ejercicio 2 Biblioteca Mi Primer Librito. Debido al inconveniente presentado en el Tercer Piso, Costado derecho de la biblioteca se requiere realizar un inventario de los textos por Tema, para comparar si coinciden los datos con la información física. También se necesita saber cuanto ha recaudado la biblioteca por alquiler, esta información siempre se resume por Código Anexo de cada ejemplar.

Excel Avanzado Taller 3.3: Subtotales

89

Ing. Leila Sofía Farfán C.

Lectura 4.1: TABLAS DINAMICAS INTRODUCCION En esta lectura, se explica como realizar diferentes resúmenes de grandes listas de información de una forma fácil y amigable; como sintetizar estas listas y permitir el análisis y consulta con solo mover campos de lugar.

OBJETIVO − Trabajar con Tablas Dinámicas − Adquirir conocimientos sobre el manejo y aplicación de las Tablas Dinámicas − Comprender la utilidad de trabajar con Tablas Dinámicas CONTENIDO Una Tabla Dinámica es una herramienta de Excel que permite dar un gran dinamismo a la información; las Tablas dinámicas son interactivas, se pueden giras sus filas y columnas para ver diferentes resúmenes de la información original. Se crea usando las funciones =SUMA, =PROMEDIO, =MAX entre otras, para analizar la información de tipo numérico. Una tabla dinámica permite realizar en forma combinada las operaciones de Autofiltro, Filtro avanzado, y Subtotales. Para trabajar Tablas Dinámicas, se deben tener en cuenta las mismas condiciones enunciadas en la lectura 3.1 Listas. Para crear una tabla dinámica se va a utilizar el asistente para tablas dinámicas como guía. Se debe tener una lista o base de datos. Pasos 1. Ingresar por el menú Datos, Informe de tablas y gráficos dinámicos.

Excel Avanzado Lectura 4.1: Tablas Dinámicas

90

Ing. Leila Sofía Farfán C.

Nos aparece la siguiente ventana:

En esta ventana se debe seleccionar de donde proviene la información, tenemos 4 fuentes: • Lista o base de datos de Microsoft Excel: Se crea un resumen de una lista con múltiples columnas creadas en Excel, cada una de estas columnas debe tener rotulo o encabezado, los cuales generan una lista de campos, el nombre de un campo hace referencia al rotulo de una columna. • Fuente de datos externa: Crear una tabla dinámica a partir de Bases de Datos generadas en otras aplicaciones, como por ejemplo: Access, ORACLE ó FoxPro entre otras. • Rangos de Consolidación múltiples: Resumir información de múltiples rangos de Hojas de cálculo de Excel • Otra tabla dinámica: Tomar como fuente de información otra tabla dinámica generada en el mismo libro. También se debe seleccionar que se desea hacer: • Una tabla dinámica ó • Un gráfico dinámico Después de seleccionar la fuente de información y el tipo de informe, clic en Siguiente 2. El segundo paso es seleccionar o confirmar el rango donde se encuentra la información fuente: Excel Avanzado Lectura 4.1: Tablas Dinámicas

91

Ing. Leila Sofía Farfán C.

Seleccionar la información y Siguiente. 3. En el tercer paso se indica la ubicación de la tabla dinámica; existe la opción de crear la tabla en una hoja nueva ó en la hoja de cálculo actual, en este último caso se debe usar el botón de Contraer dialogo y seleccionar la celda inicial donde se creara la tabla dinámica.

Clic en el botón

, Excel nos muestra la estructura de una tabla dinámica:

Una tabla dinámica se compone de cuatro elementos: Excel Avanzado Lectura 4.1: Tablas Dinámicas

92

Ing. Leila Sofía Farfán C.

El primer elemento de una tabla dinámica y el único indispensable en el desarrollo de ésta, es el área de DATOS, que corresponde generalmente a uno o más campos de tipo numérico, a los cuales se les puede realizar un proceso aritmético. Sobre los campos asignados a esta área se pueden aplicar las siguientes funciones: =Suma, =Contar, =Promedio, =Máx, =Mín, =Producto, =Desvest, =Desvestp, =Var, =Varp.

=Contar números,

El segundo elemento, FILA, son campos de la lista original que se ubican en esta área para que se muestren en las filas. COLUMNA, campos asignados en esta área de la tabla, los valores se muestran en columnas dentro de la tabla dinámica. PAGINA, es el cuarto elemento de la tabla dinámica, puede tener uno o más campos y se convertirán en los criterios de la tabla, estos criterios reciben el nombre de criterios dinámicos y permiten filtrar la información y presentar individualmente el resumen. 4. Luego de asignar los campos clic en Aceptar y en Finalizar.

Excel Avanzado Lectura 4.1: Tablas Dinámicas

93

Ing. Leila Sofía Farfán C.

Ejemplo 4.1: Tablas Dinámicas OBJETIVO − Practicar el uso de Tablas Dinámicas − Aplicar Tablas dinámicas a una lista de información.

Ejemplo 1 o Necesitamos un resumen por Nombre Cargo, de cuanto se pago a los empleados por subsidio de alimentación y que empleados pertenecen a cada cargo. o También se necesita consultar cuantos hijos suman por cargo. Solución Punto a) 31.Abrir el archivo de nómina. 32.Ubicar el cursor en cualquier celda de la lista de información. 33.Clic en menú Datos, Informe de tablas y gráficos dinámicos

34.Seleccionar las opciones que aparecen el la siguiente ventana

Seleccionar Lista o base de datos de Microsoft Excel, ya que nuestra fuente de información es una lista de Excel Y tabla dinámica, es lo que deseamos hacer. Excel Avanzado Ejemplo 4.1: Tablas Dinámicas

94

Ing. Leila Sofía Farfán C.

Clic en Siguiente. 35.En este paso nos pide seleccionar el rango

Excel reconoce el rango sobre el cual vamos a trabajar, es importante que estén seleccionados los rótulos de las columnas, según el ejemplo el rango es $A$13:$L$33. Si al iniciar el proceso para crear la tabla dinámica el cursor quedo ubicado fuera de la lista de información, se debe seleccionar el rango manualmente: clic en el botón de Contraer diálogo y seleccionar el rango. Compruebe que el rango es el correcto.

Botón Contraer diálogo

Clic en Siguiente 36.En este paso debemos indicarle a Excel si deseamos crear una nueva hoja para la tabla dinámica o en la hoja ya existente, en el ejemplo seleccionaremos la opción Hoja de calculo nueva

Excel Avanzado Ejemplo 4.1: Tablas Dinámicas

95

Ing. Leila Sofía Farfán C.

37.Clic en el botón tabla.

, este es el momento de empezar a diseñar nuestra

Observe a la derecha los campos, cada uno de ellos representa el rotulo o encabezado de las columnas existentes en nuestra lista o base de datos. Estos campos son los que permiten crear los resúmenes. Como se necesita un resumen por nombre de cargo, con clic izquierdo sostenido arrastrar el botón a la sección FILA, en caso de cometer un error el botón se devuelve a su posición inicial de la misma forma. Ahora, clic izquierdo sostenido sobre el campo DATOS

y arrastrarlo a la sección

Nuestra ventana presenta el siguiente aspecto:

Excel Avanzado Ejemplo 4.1: Tablas Dinámicas

96

Ing. Leila Sofía Farfán C.

38.Clic en Aceptar y Finalizar Aparece la Tabla dinámica diseñada

Lista de campos

Barra de herramientas Tabla dinámica

Observemos en el gráfico: La barra de herramientas la cual permite modificar la tabla dinámica actual, la lista de campos, desde este panel se puede agregar o retirar campos en las diferentes áreas de la tabla dinámica. En la parte superior encontramos la tabla dinámica creada, se obtuvo un resumen de cuanto sumo el Subsidio de alimentación por cargo y un total general de todos los cargos. Pero, como nos solicitan mostrar los empleados que pertenecen a cada cargo, vamos a modificar la tabla

Excel Avanzado Ejemplo 4.1: Tablas Dinámicas

97

Ing. Leila Sofía Farfán C.

39.El cursor debe estar dentro de la tabla dinámica, clic en Tabla dinámica de la barra de herramientas Tabla dinámica y seleccionar Asistente del menú contextual

40. Seleccionar nuevamente Diseño, arrastre el campo Empleado al área FILA debajo de NombreCargo, como aparece en la imagen

Doble clic izquierdo sobre este campo.

41. Vamos a dar formato a los valores numéricos, doble clic izquierdo sobre el campo Suma de Sub. Alimentación que se encuentra en el área Datos, obtenemos una nueva ventana

Excel Avanzado Ejemplo 4.1: Tablas Dinámicas

98

Ing. Leila Sofía Farfán C.

42. Seleccionar el botón 43. Seleccionar formato Contabilidad y configurar como aparece en la imagen

44. Clic en Aceptar, Aceptar, Aceptar y Finalizar. La nueva tabla dinámica nos presenta un resumen por el campo cargo, dentro de cada cargo se puede visualizar los empleados que pertenecen a este y el subsidio de alimentación de cada uno de ellos. También podemos observar que se genera un total por cada cargo y al final de la

Excel Avanzado Ejemplo 4.1: Tablas Dinámicas

99

Ing. Leila Sofía Farfán C.

Campos dinámicos, permiten filtrar la información y obtener resúmenes parciales sobre la tabla dinámica

Tabla un total general. También observe que el formato de los valores numéricos cambio según la configuración establecida. Nuevamente, vamos a modificar la tabla dinámica, no olvide, el cursor debe estar dentro de la tabla dinámica. 45. Clic en tabla dinámica, Asistente y Diseño (paso 9) 46. Arrastre el campo NombreCargo al área PÁGINA, como en la imagen siguiente.

Excel Avanzado Ejemplo 4.1: Tablas Dinámicas

100

Ing. Leila Sofía Farfán C.

47. Doble clic izquierdo sobre el campo Suma de Sub. Alimentación 48. Seleccione la función Promedio

49. Clic en Aceptar, Aceptar y Finalizar. La nueva tabla dinámica presenta el promedio por empleado de Subsidio de alimentación, y un promedio general por todos los empleados. Campos dinámicos, permite filtrar la información por nombre de cargo y por empleado

Excel Avanzado Ejemplo 4.1: Tablas Dinámicas

101

Ing. Leila Sofía Farfán C.

Hasta el momento hemos modificado la tabla dinámica inicial, se puede concluir: las áreas FILAS, COLUMNAS y PÁGINA son diferentes formas de visualizar la información, en DATOS normalmente incluimos campos sobre los cuales se pueden ejecutar funciones. Intente cambiar de posición los campos y generar otras tablas dinámicas. Solución Punto b) Consultar cuantos hijos suman por cargo. Vamos a crear nuevamente una Tabla dinámica nueva. 50. Posicionar el cursor en cualquier celda de la lista de información, recuerde, de esta manera Excel reconoce el rango automáticamente. 51. Clic en menú Datos, Informe de Tablas y gráficos Dinámicos 52. Seleccione las opciones como en la imagen

53. Clic en Siguiente

Excel Avanzado Ejemplo 4.1: Tablas Dinámicas

102

Ing. Leila Sofía Farfán C.

Excel Avanzado Ejemplo 4.1: Tablas Dinámicas

103

Ing. Leila Sofía Farfán C.

Lectura 4.2: TABLAS INTRODUCCION En muchas ocasiones al diseñar un hoja de cálculo nos preguntamos ¿qué pasaría si..?, es decir, realizar cambios en algunos valores constantes de la hoja para ver su efecto en otras celdas dependientes. Para efectuar esta operación Excel dispone de una opción en el menú Datos llamada Tabla, que nos permite realizar este tipo de análisis de una forma sencilla.

OBJETIVO − Crear tablas con una variable − Crear tablas con dos variables. CONTENIDO Las tablas de datos nos permiten hacer supuestos, en Excel pueden ser de dos tipos: − Tabla de una variable − Tabla de dos variables. Tablas de Datos de una variable Para crear una tabla de datos de una variable es necesario tener: 1. Una celda de entrada que se desee modificar. (Variable) 2. Una fila o columna con valores variables que se deseen aplicar a la celda de entrada. 3. Una celda con la fórmula que nos permita obtener los resultados de las tablas de datos. Tabla de datos de dos variables, necesitamos: 1. Dos celdas de entrada que se desean modificar (variables) 2. Una fila con valores variables que se desean aplicar a una de las celdas de entrada. 3. Una columna con valores variables que se desean aplicar a la segunda celda de entrada. 4. Una fórmula en la intersección de la fila y columna de los valores variables para obtener los resultados de la tabla de datos.

Excel Avanzado Lectura 4.2: Tablas

104

Ing. Leila Sofía Farfán C.

TALLER 4.2: TABLAS OBJETIVO Usted desea solicitar un crédito de $10.000.000 por 15 años a una entidad bancaria, ya consulto en un banco, y le informaron que la tasa de interés es del 9,5% anual y también le informaron cual sería su cuota, pero ahora usted quisiera hacer diferentes supuestos, quisiera saber ese mismo valor solicitado pero con otro interés, cual sería su cuota, también le gustaría saber cual sería su cuota si toma el crédito con más o menos números de años. Solución. 1. Digite la siguiente tabla

2. Calculemos el valor de la celda C8 utilizando la función =PAGO, función de tipo financiero, que permite saber cual sería la cuota periódica de un crédito a una tasa de interés constante. Sintaxis de la función =Pago =PAGO(Tasa;Nper;Va) donde Tasa es la tasa de interés por período de préstamo; para nuestro ejemplo la tasa de interés es del 9.5% anual, pero como necesitamos hallar la cuota mensual, dividimos esta tasa en 12 y así obtener la tasa de interés mensual.

Excel Avanzado Ejemplo 4.2: Tablas

105

Ing. Leila Sofía Farfán C.

Nper es el número total de pagos o cuotas de un crédito, en nuestro ejemplo son 15 años pero como la cuota es mensual necesitamos multiplicar los 15 años por 12 para hallar el número total de cuotas. Va es el valor que debemos pagar, en nuestro ejemplo son los $10.000.000 Para concluir la fórmula de la celda C8 es la siguiente: =-PAGO(C5/C7;C6*C7;C4) La función PAGO siempre nos da un valor negativo, por eso el signo menos antes de la función para que el resultado lo muestre positivo. Ahora sí vamos a trabajar con la opción TABLAS. Para crear una tabla de datos de una variable es necesario tener 3. Una celda de entrada que se desee modificar. (Variable). En este caso la celda que se desea modificar dentro de la fórmula es el interés (C5) 4. Una fila o columna con valores variables que se deseen aplicar a la celda de entrada, en el rango B11:B15; encontramos los diferentes valores de intereses. 5. Una celda con la fórmula que nos permitan obtener los resultados de las tablas de datos. En la celda B10, copiamos la fórmula que se encuentra en C8, pues es en esta fórmula donde deseamos variar el interés. 6. Seleccione el rango de la tabla A10:B15 7. Seleccione el menú DATOS, opción TABLA 8. En el cuadro de diálogo que aparece se debe introducir la referencia de la celda de entrada en el interior del recuadro Celda de entrada fila o Celda de entrada columna dependiendo de si los valores variables de la tabla han sido introducidos en fila o en columna. En nuestro caso Celda de entrada Columna, ya que los valores variables están en columna; teclee o seleccione la celda C5. 9. Aceptar. En este momento, hemos averiguado cual sería la cuota pero con otros intereses. Tabla de datos de dos variables, necesitamos: 1. Dos celdas de entrada que se desean modificar (variables), para nuestro ejemplo, vamos a variar los años y el interés. 2. Una fila con valores variables que se desean aplicar a una de las celdas de entrada. Digitar en B17=5; C17=7; D17=10; queremos saber a 5, 7 y 10 años cual sería la cuota. 3. Una columna con valores variables que se desean aplicar a la segunda celda de entrada. En la columna vamos a digitar los intereses; Digitar en A18, 9%; A19, 10%, en A20 12%, en A21, 13%. Excel Avanzado Ejemplo 4.2: Tablas

106

Ing. Leila Sofía Farfán C.

4. Una fórmula en la intersección de la fila y columna de los valores variables para obtener los resultados de la tabla de datos. 5. En A17 digite =C8 Ya tenemos todas las condiciones, 6. Ahora seleccionar el rango A17:D21 7. Menú Datos, Tabla, Nos aparece a siguiente ventana

8. En fila seleccionar C6, ya que los años variables están en fila, y en la fórmula, C6 corresponde al año. 9. En columna, seleccionar C5, es la celda que contiene el interés y los datos variable de tasa de interés se encuentran en columna. Aceptar. 10.Hemos realizado un supuesto de un crédito a diferentes años y con diferentes tasas de interés y así saber cual se acomoda mejor al presupuesto. 11.Guardar con nombre CREDITO

Excel Avanzado Ejemplo 4.2: Tablas

107

Ing. Leila Sofía Farfán C.

TALLER 4.2: TABLAS OBJETIVO − Practicar lo aprendido Taller Usted es el administrador de un almacén de artículos de cuero, en estos momentos hay en inventario de 25 carteras con imperfectos y usted desea aplicarles un descuento para facilitar la venta. Aplique la opción tablas para saber dependiendo del porcentaje, cual sería el descuento Solución. 10.Digite la siguiente tabla

11.Calcule el valor de la celda D7 utilizando la fórmula =(D4*D5)-(D4*D5)*D6 12.Cree una Tabla de Datos de una variable con los siguientes descuentos 5,0%; 5,50%; 6,0%; 6,5% y 7,0% 13.Guarde el libro con el nombre VENTAS CON DESCUENTO.

Excel Avanzado Taller 4.2: Tablas

108

Ing. Leila Sofía Farfán C.

Lectura 4.2: BUSCAR OBJETIVO INTRODUCCION En algunas ocasiones tenemos necesidad de buscar un valor de una celda específica de la que depende una fórmula, con el fin de obtener un resultado correcto. Para ello Excel dispone de la herramienta BUSCAR OBJETIVO con la que es posible ir variando el valor de una celda de entrada hasta lograr el resultado deseado.

OBJETIVO − Analizar información para lograr objetivos. CONTENIDO Esta opción, sirve para lograr “AJUSTAR” el resultado de una fórmula. Esto significa, que, indicando a Excel el resultado que se desea obtener en una celda donde se encuentre una fórmula dada que haga referencia al contenido de otras, y definiendo cuál de esas celdas deberá modificar su valor para la obtención de ese resultado, puede conseguirse el valor de la celda mencionada. 1. Para utilizar la opción BUSCAR OBJETIVO se debe crear primero un modelo de hoja que represente el problema. 2. Clic en menú Herramientas, Buscar Objetivo 3. Aparecerá una ventan como la siguiente

DEFINIR LA CELDA: Es la referencia de la celda que contiene la fórmula con relación a la celda cambiante para buscar una solución CON EL VALOR: Permite escribir el valor deseado para la celda anterior PARA CAMBIAR LA CELDA: Se debe indicar la referencia de la celda que contiene el valor que se desea ajustar. 4. Aceptar. Excel Avanzado Lectura 4.3: Buscar Objetivo

109

Ing. Leila Sofía Farfán C.

5. En breves instantes Excel resuelve el problema, apareciendo un cuadro de dialogo llamado Estado de la búsqueda de Objetivo. En algunos casos puede ocurrir que no exista solución posible al problema planteado.

Excel Avanzado Lectura 4.3: Buscar Objetivo

110

Ing. Leila Sofía Farfán C.

Ejemplo 4.3: BUSCAR OBJETIVO OBJETIVO − Aplicar la herramienta Buscar Objetivo Ejemplo Para ver el funcionamiento de esta opción, vamos a realizar la práctica con el ejercicio crédito. Vamos a suponer que disponemos de $200000 pesos para pago de cuota mensual, por lo tanto, deseo saber cuanto puedo solicitar de crédito, sin variar el plazo o número de periodos y sin variar el interés. 1. Abrir libro crédito, copiar el rango B4:C8 en otra hoja. 2. Clic en Herramientas, Buscar Objetivo

3. DEFINIR CELDA, clic en la celda C8, es la referencia de la celda que contiene la fórmula con relación a la celda cambiante para buscar una solución. 4. CON EL VALOR, digitar 200000, es el valor deseado a consultar 5. PARA CAMBIAR LA CELDA: clic en C4, este es el valor que se desea ajustar. 6. Aceptar, 7. Aparece esta ventana

Excel Avanzado Ejemplo 4.3: Buscar Objetivo

111

Ing. Leila Sofía Farfán C.

8. Como puede observar en la celda C8 aparece el valor de la cuota sugerida y en C4 el valor del préstamo que se puede solicitar. Guarde nuevamente el archivo.

Taller 4.3: BUSCAR OBJETIVO OBJETIVO − Practicar la herramienta Buscar Objetivo Taller 1 En las ventas de cartera, se desea que el valor total de las 25 carteras no baje de $1.000.000, cual sería el porcentaje de descuento a aplicar para lograr este objetivo. Guarde Nuevamente el archivo Taller 2 Un alumno de informática en sus dos primeros talleres obtuvo un 6,5 y un 8,0 respectivamente, si el promedio mínimo para pasar la materia es de 8,0; cual es la nota que debe sacar en su tercer taller para lograr su objetivo.

Excel Avanzado Ejemplo 4.3: Buscar Objetivo

112

Ing. Leila Sofía Farfán C.

Lectura 5.1: MACROS INTRODUCCION Esta semana vamos a estudiar como crear, ejecutar y asignar una macro a un botón de la barra de herramientas. Explicaremos este tema con un ejercicio paso a paso.

OBJETIVO − Crear macros automáticas − Ejecutar macros − Asignar macros a un botón de la barra de herramientas. CONTENIDO Una macro no es más que un sistema de automatizar tareas repetitivas, con el fin de evitar la repetición de las mismas operaciones una y otra vez. Vamos a crear una macro, para darle formato a los encabezados de las columnas. Seleccione el menú Herramientas/macro/grabar nueva macro

Nombre de la macro: permite especificar el nombre que queremos dar a dicha macro. Método abreviado: permite asignar una combinación de teclas para ejecutar la macro después de su creación. Guardar macro en: Permite seleccionar dónde se desea almacenar la macro. Descripción: Para añadir o modificar la información descriptiva de la macro. En el cuadro de texto nombre de la macro, digite formato_especial; teclee f en método abreviado, clic en aceptar. En este momento todas las acciones que realicemos las grabara la macro, por lo tanto siga con atención los próximos pasos, ya que un error significará un error en la grabación Abra el menú formato, seleccione Celdas, Fuentes, elija Times New Roman, con tamaño 14 y un color para la fuente, haga clic en pestaña bordes, seleccione un tipo de borde, Haga clic en pestaña Tramas, seleccione un color, tenga en cuenta el color de la fuente para el fondo, pulse el botón aceptar.

Excel Avanzado Lectura 5.1: Macros

113

Ing. Leila Sofía Farfán C.

Taller 5.1: MACROS OBJETIVO − Aplicar macros automáticos − Asignar macros a un botón de la barra de herramientas. Ejercicio 1 Genere una nueva macro, que le permita imprimir en forma horizontal, tamaño de papel A4, todas las márgenes de 4 cms, centrado en la página tanto horizontal como verticalmente, margen de encabezado y pie de página = 2 cms, un Encabezado que diga Ejemplo de Macros y un pie de página con su nombre, fecha y nombre del archivo con fuente arial 12. Asigne la macro a un botón en la barra de herramientas. Ejecútelo en los otros ejercicios de Excel y compruebe su funcionamiento. Ejercicio 2 Diseñe una macro en el Taller de Friítolay, donde genere una copia de la hoja GESTION y en esta nueva hoja borre el contenido de las celdas, sin borrar las formulas.

Excel Avanzado Taller 5.1: Macros

114

Ing. Leila Sofía Farfán C.

Lectura 5.2: FORMULARIOS INTRODUCCION Esta semana vamos a estudiar como crear, ejecutar y asignar una macro a un botón de la barra de herramientas. Explicaremos este tema con un ejercicio paso a paso.

OBJETIVO − Crear macros automáticas − Ejecutar macros − Asignar macros a un botón de la barra de herramientas. CONTENIDO Un formulario es utilizado generalmente para reunir información del usuario que interactúa con él. La utilización de los controles en forma de botón agilizan el manejo de las hojas de cálculo. Nos permiten hacer consultas, seleccionar información sin equivocarnos. Para trabajar formularios antes que nada debemos activar la barra de botones (si no lo está ya). La barra se activa con la opción Ver - Barras de herramientas y activando la casilla Formularios.

La utilización de los controles en forma de botón agiliza el manejo de las hojas de cálculo. A continuación se detallan las más utilizadas en formularios de pantalla: 1. Etiqueta, permite insertar texto estático a un formulario de Excel. 2. Cuadro de grupo, permite insertar un borde de trabajo, generalmente usado para dividir secciones de un mismo formulario. 3. Casilla de verificación, permite incluir casillas a los formularios. Usadas comúnmente para activar / desactivar opciones. 4. Botón de opción, permite incluir botón de alternativas a un formulario. 5. Cuadro combinado, permite diseñar una ficha o lista de opciones. 6. Control de número, permite aumentar o disminuir un número en una escala personalizada.

Excel Avanzado Lectura 5.2: Formularios

115

CURSOAVA51855842

7. Barra de desplazamiento, conocido elemento que nos permite aumentar o disminuir un valor. Ahora pasemos al ejemplo de formularios, para entender como funcionan.

Excel Avanzado Lectura 5.2: Formularios

116

CURSOAVA51855842

Ejemplo 5.2: FORMULARIOS OBJETIVO − Crear formularios para la consulta de información − Aplicar funciones según los requerimientos − Crear y aplicar macros. Ejemplo 1 FORMULARIO PARA REALIZAR COTIZACIONES Tomaremos como base la Empresa ABC que comercializa un único artículo (mgie), cuyo precio varía en razón del color de dicho artículo, de acuerdo con el siguiente cuadro: Color Amarillo Azul Negro Rojo Verde

Precio 9.900 10.000 8.000 9.600 10.500

Este artículo además, por políticas de la empresa, solo es comercializado en paquetes de 24 unidades cada uno. Con el objetivo de incentivar las ventas, la empresa ofrece una política de descuentos, así entonces, cuado el monto total de la venta supera los $ 500.000 otorga un 3% de descuento sobre el total, ahora bien si el monto supera los $ 800.000 el descuento será de un 5% sobre el total. Debido a lo importante que es el flujo de efectivo para la empresa, ésta ha decidido aplicar también un descuento especial para fomentar el pago al contado, siendo este de un 2% (sobre el monto total, una vez realizados los descuentos por volumen). Las otras opciones de pago que ofrece es dentro de los 30 días siguientes al de la venta, caso en el cual esta empresa no ofrece descuento alguno. Además, ahora ha incorporado también el pago con tarjeta de crédito, pero esta forma de pago tiene un costo para la empresa debido a las comisiones que debe cancelar, razón por la cual ha decido sobrecargar el monto total de venta en un 1,5% cada vez que uno de sus clientes decida pagar su compra con tarjeta de crédito. Teniendo en cuenta todas estas situaciones, ahora deberemos construir un formulario muy fácil de usar, pero que nos permita considerar todos los factores que condicionan el precio final de venta de los artículos. Construcción del Formulario

Excel Avanzado Ejemplo 5.2: Formularios

117

CURSOAVA51855842

En primer lugar abrir un libro en blanco, en el cual llamaremos "formulario" la primera hoja de dicho libro. Si bien es cierto, que usted puede construir el formulario de la forma que le sea más cómoda o agradable, le recomiendo que siga los pasos y posiciones que se indican en este ejemplo, con el objetivo que se más fácil la revisión en caso de que se comentan errores. A modo de guía, utilizare el resultado final que se desea obtener con dicho formulario:

Como podemos apreciar, el formulario abarcará desde B4 hasta H19, por lo que pintaremos el fondo de esta área y además le daremos formato a los bordes. El área entre B2 y H2 la uniremos para colocarle un título al formulario, conjuntamente le aplicaremos formato al texto hasta obtener el efecto deseado. A continuación lo que haremos será construir la estructura de dicho formulario, para lo cual le daremos formato a las distintas celdas que formarán parte de él. En F7 escriba Cantidad En F9 Precio unitario En F11 SUB TOTAL En F13 Dcto. Volumen En F15 Dcto. Pronto Pago, En F17 TOTAL Luego crearemos una tabla que contenga la relación entre el color y el precio del artículo. En el caso de nuestro ejemplo lo hemos hecho en el rango B36:C41. Color Precio Amarillo 9.900 Azul 10.000 Negro 8.000 Rojo 9.600 Verde 10.500 Excel Avanzado Ejemplo 5.2: Formularios

118

CURSOAVA51855842

Seleccionar el rango B36:C41 e ingresar por la opción INSERTAR, NOMBRE, CREAR, desactivar COLUMNA IZQUIERDA, ACEPTAR. Con esta paso creamos los nombres para los rangos. NOTA: Para consultar los nombres que ha creado y el rango, sólo tiene que ingresar por menú INSERTAR, NOMBRE, DEFINIR y seleccionar el nombre del rango, en el cuadro de dialogo de la parte inferior aparecerá el nombre de la hoja y el rango de celdas que esta definido bajo este nombre. En esta ventana aparecen todos los nombres de los rangos creados. Activar la barra de herramientas FORMULARIO (Menú VER, BARRAS DE HERRAMIENTAS)

Pues bien, lo que ahora corresponde hacer es agregar los distintos controles. El primero que agregaremos será el cuadro combinado para la selección del color. En la celda B6 digite el texto Seleccione su color, combine este texto con la celda C6 para que el texto quede centrado en ambas celdas. De la barra de herramientas Formulario, seleccione la opción CUADRO y trace un rectángulo desde la celda B7 hasta la celda C7, COMBINADO, sobre el cuadro dar clic derecho y en este menú contextual seleccionar FORMATO DE CONTROL y rellenar el cuadro de diálogo que aparece con las siguientes opciones: Rango de entrada: Color Vincular con la celda: I9 Líneas de unión verticales: 8 Aceptar Pulsar un clic fuera del cuadro de la lista para poder utilizarlo, cuando salga el dedito, abrir la lista y seleccionar un color. ¿Qué hemos hecho? En la opción Rango de entrada le estamos diciendo a este cuadro de diálogo que “mire” en el rango que hemos definido como Color, es decir: B32:B41; en que posición de la lista esta el color seleccionado; de esta forma cuando abramos la lista que estamos creando y escojamos un color, aparecerá un número en la celda I9, este número será la posición en la lista que se encuentra el color que hayamos escogido. Este número nos servirá para determinar el precio de este color. Determinar el precio Seleccione la celda G9 y digite =INDICE(Precio;I9) Excel Avanzado Ejemplo 5.2: Formularios

119

CURSOAVA51855842

Es decir, busque la posición que aparece en la celda I9, pero dentro del rango Precio, y regrésenos este valor. De esta forma determinamos el precio según el color. Determinar la cantidad Ahora, en D11 digitar Cantidad, de la barra de herramientas FORMULARIOS, selecciona el botón CONTROL DE NUMERO, y dibujar el rectángulo entre las posiciones D12 y D14 (mirar modelo); nuevamente clic derecho sobre el cuadro dibujado para asignarle sus propiedades: Valor Actual: 0 Valor Mínimo: 24 Valor Máximo: 2000 Incremento: 24 Vincular con la celda: G7 En cuanto a sus propiedades, lo vincularemos la celda "G7" que es donde queremos que aparezca la cantidad que esta siendo cotizada. Debido a que el artículo en cuestión sólo se vende por paquetes de 24 unidades, asignaremos este valor a la propiedad Valor Mínimo y a la propiedad Incremento le asignaremos igual cantidad y a la propiedad Max, le asignaremos un valor lo suficientemente alto, por ejemplo 2000. Ahora probemos el control. Descuento por Volumen Nos queda ahora realizar todo aquello correspondiente a los descuentos, con el objetivo de evaluar si el monto de venta será objeto o no de descuento por volumen introduciremos la siguiente fórmula en dicha celda: =REDONDEAR(G11*SI(G11