• Author / Uploaded
• Hermis Ramos Espinal

• Categories
• Hoja de cálculo
• Matriz (Matemáticas)
• Point and Click
• Exponenciación
• Microsoft Excel

Citation preview

2

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

3

Página

Í NDICE Presentación

5

Red de contenidos

6

Funciones 2.1 Tema 6

:

Funciones matemáticas y de texto

2.1.1.

Funciones matemáticas

88

2.1.2.

Funciones de texto

20

2.2 Tema 7

:

Funciones estadísticas y de bases de datos

2.2.1.

Funciones estadísticas

28

2.2.2.

Funciones de Bases de Datos

40

2.3 Tema 8

:

Funciones lógicas y de búsqueda

2.3.1.

Funciones lógicas

52

2.3.2.

Funciones de búsqueda

59

2.4. Tema 9

:

Funciones financieras

2.4.1.

Funciones de pago

70

2.4.2.

Cronograma de pagos

77

Herramientas de análisis 3.1 Tema 10

:

Herramientas de análisis

3.1.1.

Escenarios

87

3.1.2.

Buscar objetivo

93

3.1.3.

Solver

96

Formularios y macros 4.1 Tema 11 4.1.1.

CIBERTEC

:

Formularios Controles de formularios

107

CARRERAS PROFESIONALES

4

4.1.2. 4.2 Tema 12

Diseño de formularios :

109

Macros

4.2.1.

Grabación de macros

120

4.2.2.

Añadir botón de comando

124

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

5

P RESENTACIÓN Software de Negocios II

es un curso que pertenece a la línea de

software de aplicaciones y se dicta en las carreras de Administración, Administración de Negocios Internacionales, Contabilidad y Marketing. El curso brinda a los alumnos la herramienta de software Microsoft Excel para la creación de aplicaciones informáticas más utilizadas en el medio laboral.

El manual para el curso ha sido diseñado bajo la modalidad de unidades de aprendizaje, las que se desarrollan durante semanas determinadas. En cada una de ellas, hallará los logros que debe alcanzar al final de la unidad; el tema tratado, el cual será ampliamente desarrollado; y los contenidos, que debe tratar, es decir, los subtemas. Por último, encontrará actividades que deberá desarrollar en cada sesión, que le permitirán reforzar lo aprendido en la clase.

El curso es eminentemente práctico. Se inicia con la creación y uso de bases de datos; luego, con la aplicación de funciones. Se emplean herramientas de análisis para toma de decisiones. Finalmente se utilizan elementos de formularios para crear aplicaciones en Excel y automatización de procesos a través de macros.

CIBERTEC

CARRERAS PROFESIONALES

6

R ED DE CONTENIDOS Creación, validación y ordenamiento de datos Filtros automáticos y avanzados Base de datos

Subtotales y esquemas de registros Consolidación, tablas y gráficos dinámicos Acceso a datos externos

Software de Negocios II

Matemáticas Texto Estadísticas Funciones

Base de datos Lógicas Búsqueda Financieras Escenarios

Herramientas de análisis

Buscar objetivo Solver Insertar elementos de formularios

Formularios y macros Crear macros

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

7

UNIDAD DE APRENDIZAJE

2

LOGROS DE LA UNIDAD DE APRENDIZAJE 

Al término de la unidad, el alumno escribe fórmulas con la inserción de funciones, de manera individual o anidada para elaborar cuadros, reportes y estadísticas.

TEMARIO       

Funciones matemáticas Funciones de texto Funciones estadísticas Funciones de bases de datos Funciones lógicas Funciones de búsqueda Funciones financieras

ACTIVIDADES PROPUESTAS  

Los alumnos insertan funciones de las diferentes categorías, de tal modo que se utilicen para crear cuadros, reportes, estadísticas con información confiable. Los alumnos insertan fórmulas con funciones anidadas.

CIBERTEC

CARRERAS PROFESIONALES

8

FUNCIONES Una función es una fórmula predefinida por Excel que opera sobre uno o más valores (argumentos) en un orden determinado (estructura). El resultado se mostrará en la celda donde se introdujo la formula

La sintaxis de cualquier función es la siguiente:

NOMBRE_FUNCIÓN (argumento 1; argumento 2;...;argumento n)

Para escribir una función, debe tomar en cuenta lo siguiente:



Una fórmula o función debe empezar con el signo =.



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 valores constantes (número o texto), fórmulas o funciones.



Los argumentos deben separarse por un punto y coma (;). En ocasiones, el separador de argumentos es la coma. Ello dependerá de la configuración regional de la computadora. Existen casos en los que, para obtener una respuesta, se deben utilizar varias funciones en una sola fórmula. A este tipo de funciones se les denomina Funciones Anidadas. Si utiliza funciones anidadas debe saber que sólo puede anidar hasta un máximo de siete niveles.

Insertar funciones manualmente Podemos insertar una función escribiéndola directamente en la celda o en la barra de fórmulas, y han de ir precedidas del signo = (igual), del mismo modo que las fórmulas. Ejemplo: SUMA(), es una función de Microsoft Excel 2010

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

9

Insertar funciones desde el asistente Podemos insertar funciones usando un asistente incorporado en Microsoft Excel 2010, para ello, después de habernos situado en la celda deseada, haremos clic en el icono Insertar función de la cinta de opciones correspondiente a la ficha Fórmulas

En el siguiente cuadro de diálogo, escribiremos el nombre de una función o parte de ella en Buscar una función y pulsaremos la tecla Enter, o bien seleccionaremos una categoría en el cuadro de lista O seleccionar una categoría y seleccionaremos la función deseada en Seleccionar una función. Una vez elegida una, clic en Aceptar.

A continuación, en el cuadro de diálogo que se muestre, escribiremos las referencias a las celdas que contienen los valores a trabajar, o bien haremos clic en y las seleccionaremos. En la parte inferior derecha se irá mostrando el resultado actual de la misma. Cuando hayamos definido los parámetros a usar, haremos clic en Aceptar para insertar la función.

CIBERTEC

CARRERAS PROFESIONALES

10

Insertar funciones desde la Biblioteca de funciones: Podemos acceder rápidamente a las funciones usando los iconos del grupo Biblioteca de funciones, correspondiente a la ficha Fórmulas:

En dicha cinta de opciones disponemos de varias opciones más para el tratamiento de funciones:

Novedades en el Excel 2010 Excel 2010 incorpora ahora un conjunto de funciones estadísticas más exactas, entre otras funciones. También se ha cambiado el nombre de determinadas funciones existentes para que describa mejor su funcionalidad. Se han optimizado varias funciones para mejorar la exactitud. Por ejemplo, Excel 2010 devuelve resultados más exactos para las distribuciones Chi cuadrado y Beta. Se ha cambiado el nombre de ciertas funciones estadísticas para que sean más coherentes con las definiciones de funciones de la comunidad científica y con otros nombres de funciones en Excel. Los nombres de funciones nuevos también describen con mayor exactitud su funcionalidad. Los libros creados con versiones anteriores de Excel continuarán funcionando a pesar de estos cambios en los nombres debido a que las funciones originales aún existen en la categoría Compatibilidad.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

11

2.1 FUNCIONES matemáticas y de texto 2.1.1. Funciones matemáticas Puede insertar cualquier función matemática desde la ficha Fórmulas, el grupo Biblioteca de funciones y el comando Matemáticas y trigonométricas.

 FUNCIÓN: POTENCIA

Devuelve el resultado de elevar un número a una potencia. Sintaxis: POTENCIA(número; potencia)

Donde  Número es el número base y puede ser cualquier número real.  Potencia es el exponente al que desea elevar el número base. Se puede utilizar el operador ^ en lugar de la función POTENCIA para indicar a qué potencia se eleva el número base, por ejemplo 3^2. Si no encuentra el operador en el teclado, utilice la siguiente combinación ALT 94.

Ejemplo: Para comprender cómo funciona la POTENCIA, escriba la siguiente información en una hoja de cálculo en blanco.

CIBERTEC

CARRERAS PROFESIONALES

12

i.

Escriba la siguiente fórmula en C4.

ii. Presione la tecla ENTER y observe el resultado: 9 iii. Escriba las fórmulas para completar la expresión X2.

Ejercicio: Obtenga los resultados de las expresiones restantes.

X

Y

3 4 5

2 7 9

X2

3

Y2 9 16 25

4 49 81

4

X 1.44 1.59 1.71

Y 1.19 1.63 1.73

 FUNCIÓN: RCUAD

Devuelve el resultado de calcular la raíz cuadrada a un número. Sintaxis: RCUAD(número)

Donde  Número es el número base y puede ser cualquier número real.

Si número es negativo, RCUAD devuelve el valor de error #¡NUM!

Ejemplo: Para comprender cómo funciona la RCUAD, escriba la siguiente información en una hoja de cálculo en blanco.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

i.

13

Escriba la siguiente fórmula en C4: =RCUAD(A4)

ii. Presione la tecla ENTER y observe el resultado: 5 iii. Hacer un arrastre para obtener los demás resultados de la columna

Ejercicio: Obtenga los resultados de las expresiones restantes.

X 25 81 16

Y 144 4 9

X

5 9 4

X  Y 17 11 7

X  Y 13.00 9.22 5.00

 FUNCIÓN SUMAR.SI

Suma las celdas en el rango que coinciden con el argumento criterio. Sintaxis: SUMAR.SI(rango;criterio;rango_suma)

Donde  Rango es el rango de celdas que desea evaluar.  Criterio es el criterio en forma de número, expresión o texto, que determina las celdas que se van a sumar.  Rango_suma es el rango de las celdas que se van a sumar. Las celdas contenidas en rango_suma se suman sólo si las celdas correspondientes del rango coinciden con el criterio. Si rango_suma se omite, se suman las celdas contenidas en el argumento rango.

Existe la función SUMAR.SI.CONJUNTO, que suma las celdas de un rango que cumplen varios criterios.

CIBERTEC

CARRERAS PROFESIONALES

14

Ejemplo: Copie la información anterior a una hoja nueva y agregue la siguiente información:

Tome en cuenta que el campo Venta S/. lo deberá calcular multiplicando el precio por la cantidad.

i.

Coloque el puntero de celda en la posición C19.

ii. Haga clic en el botón

de la barra de fórmulas.

iii. Seleccione la categoría Matemáticas y Trigonométricas. Luego haga clic en Aceptar. iv. Seleccione la función SUMAR.SI. Luego, haga clic en Aceptar.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

15

v. En el argumento Rango, seleccione C6:C17, escriba LG en el argumento Criterio y finalmente, seleccione el rango E6:E17 en el Rango_suma. Luego, haga clic en Aceptar.

Observe el resultado.

Ejercicio1. Obtenga la suma de la cantidad para la marca Sony.

Ejercicio2. Obtenga los resultados para el siguiente cuadro.

MARCA

VENTA S/.

LG AIWA PHILLIPS SONY

Ejercicio3. Utilice la función SUMAR.SI para responder las siguientes preguntas: ¿Cuál es la suma de las cantidades de los productos cuyos precios son menores a 1000? ¿Cuál es la venta total de los productos cuyas cantidades son menores a 20?

CIBERTEC

CARRERAS PROFESIONALES

16

 FUNCIÓN: SUBTOTALES

Devuelve un subtotal en una lista o base de datos. Una vez creada la lista de subtotales, puede cambiarse modificando la fórmula SUBTOTALES. Sintaxis: SUBTOTALES(núm_función;ref1, ref2, ...) Donde  Núm_función es el número que indica que función debe utilizarse.  Ref1, Ref2 son de 1 a 254 rangos o referencias para los cuales desea calcular el subtotal.

Núm_función es un número de 1 a 11 (incluye valores ocultos) o de 101 a 111 (pasa por alto valores ocultos).

Núm_función

Función

1

PROMEDIO

2

CONTAR

3

CONTARA

4

MAX

5

MIN

7

DESVEST

8

DESVESTP

9

SUMA

10

VAR

11

VARP

Ejemplo: Para comprender cómo funcionan los SUBTOTALES, escriba la siguiente información en una hoja de cálculo en blanco. Luego, calcule la suma y el promedio de las cantidades en las celdas E19 y E22.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

17

Los resultados de la suma y el promedio son los siguientes:

Tome en cuenta que el total 271 es el resultado de la suma de las cantidades. De la misma manera, los promedios 687.42 y 22.58 son resultados del promedio tanto del precio como la cantidad. En estos 3 casos, escriba la fórmula correspondiente.

Ahora, se calcularán los subtotales de suma y promedio. i.

Coloque el puntero de celda en la posición E20.

ii. Haga clic en el botón Fx de la barra de fórmulas.

iii. Seleccione la categoría Matemáticas y Trigonométricas. Luego, haga clic en Aceptar.

CIBERTEC

CARRERAS PROFESIONALES

18

iv. Seleccione la función SUBTOTALES. Luego, haga clic en Aceptar.

v. En el argumento Núm_función, escriba 9 (porque es el número que corresponde a la suma) y seleccione el rango E6:E17 en el cuadro Ref1. Luego, haga clic en Aceptar.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

19

Al parecer la función subtotales realiza la misma operación que la función suma o promedio, pero si usted aplica autofiltros a la lista, notará la diferencia entre ambos.

vi. Active autofiltros a la base de datos y seleccione la marca Phillips.

¿Podría indicar cuál es la diferencia entre la función SUMA y la función SUBTOTALES?

Ejercicio: Obtenga el subtotal promedio.

Guarde el archivo con el nombre de Funciones Matemáticas.

CIBERTEC

CARRERAS PROFESIONALES

20

2.1.2. Funciones de texto Las funciones de texto se utilizan para manipular datos tipo texto, cadena de caracteres o string. Es importante tomar en cuenta que cuando desee referirse a un texto deberá hacerlo utilizando las comillas “texto”.

Puede insertar funciones de la categoría Texto utilizando la ficha Fórmulas y el grupo Biblioteca de funciones.

 FUNCIÓN IZQUIERDA

Devuelve el primer caracter o caracteres de una cadena de texto, teniendo en cuenta el número de caracteres que se especifique. Sintaxis: IZQUIERDA(texto;núm_de_caracteres) Donde  Texto; es la cadena de texto que contiene los caracteres que desea extraer.  Núm_de_caracteres especifica el número de caracteres que desea extraer con IZQUIERDA.  Núm_de_caracteres debe ser mayor que cero. Si núm_de_caracteres se omite, se calculará como 1. Ejemplo: Para comprender cómo trabajan las funciones de texto, escriba la siguiente información en un libro nuevo. Seleccione la celda B5.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

21

i. Haga clic en el botón de la barra de fórmulas. ii. Seleccione la Categoría Texto y la función IZQUIERDA. Luego, haga clic en el botón Aceptar. iii. En el argumento Texto, escriba B3 y, en Núm_de_caracteres, 6 (porque el número de letras del primer nombre es 6). Luego, haga clic en el botón Aceptar.

Observe el resultado. Primer nombre

Rafael

 FUNCIÓN DERECHA

Devuelve el último caracter o caracteres de una cadena de texto, según el número de caracteres que se especifique. Sintaxis: DERECHA(texto;núm_de_caracteres) Donde  Texto es la cadena de texto que contiene los caracteres que desea extraer.  Núm_de_caracteres especifica el número de caracteres que desea extraer con DERECHA. Núm_de_caracteres debe ser mayor o igual que cero.  Si núm_de_caracteres es mayor que la longitud del texto, DERECHA devolverá todo el texto. Si núm_de_caracteres se omite, se calculará como 1. Ejemplo: Para comprender cómo trabaja esta función, se utilizará el cuadro anterior. Seleccione la celda B6. i.

Haga clic en el botón

de la barra de fórmulas.

ii. Seleccione la Categoría Texto y la función DERECHA. Luego, haga clic en el botón Aceptar.

CIBERTEC

CARRERAS PROFESIONALES

22

iii. En el argumento Texto escriba B3 y en Núm_de_caracteres 8 (por el número de letras del apellido MATERNO). Luego, haga clic en el botón Aceptar.

Observe el resultado. Apellido materno

Castillo

 FUNCIÓN MED

Devuelve un número específico de caracteres de una cadena de texto, comenzando en la posición que especifique y en función del número de caracteres que indique. Sintaxis: MED(texto;posición_inicial;núm_de_caracteres)

Donde  Texto es la cadena de texto que contiene los caracteres que desea extraer.  Posición_inicial es la posición del primer carácter que desea extraer de texto. La posición_inicial del primer carácter de texto es 1 y así sucesivamente.  Núm_de_caracteres especifica el número de caracteres que desea que MED devuelva del argumento texto. Si posición_inicial es mayor que la longitud de texto, MED devuelve "" (texto vacío). Si posición_inicial es menor que la longitud de texto, pero posición_inicial más núm_de_caracteres excede la longitud de texto, MED devuelve los caracteres hasta el final de texto. Si posición_inicial es menor que 1, MED devuelve el valor de error #¡VALOR! Si núm_de_caracteres es negativo, MED devuelve el valor de error #¡VALOR!

Ejemplo: Con la información anterior, realice lo siguiente: i. Seleccione la celda B7.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

ii. Haga clic en el botón

23

de la barra de fórmulas.

iii. Seleccione la categoría Texto y la función MED. Luego, haga clic en el botón Aceptar. iv. En el argumento Texto escriba B3, en Posición_inicial, escriba 9; y en Núm_de_caracteres, 6 (recuerde que el texto que desea extraer es Isabel). Luego, haga clic en el botón Aceptar.

Observe el resultado. Segundo Nombre

Alberto

 FUNCIÓN CONCATENAR

Concatena o une argumentos de texto. Sintaxis: CONCATENAR (texto1;texto2; ...;texto30)

Donde:  Texto1, texto2, ... son de 1 a 30 elementos de texto que se unirán en un texto único. Los elementos de texto pueden ser cadenas de texto, números o referencias a celdas únicas.

Puede utilizar el operador "&" en lugar de CONCATENAR para unir elementos de texto.

Ejemplo: Con la información anterior realice lo siguiente i.

Seleccione la celda B8.

ii. Haga clic en el botón

CIBERTEC

de la barra de fórmulas.

CARRERAS PROFESIONALES

24

iii. Seleccione la categoría Texto y la función CONCATENAR. Luego, haga clic en el botón Aceptar. iv. En el argumento Texto1, escriba B7; en Texto2, escriba “ ”, y, en Texto3, escriba B6. Luego, haga clic en el botón Aceptar.

v. Observe el resultado. Segundo Nombre + Apellido materno

Alberto Castillo

Guarde el archivo con el nombre de Funciones de Texto.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

25

ACTIVIDAD 06: Abra un libro nuevo y asigne como nombre al archivo por ACTIVIDAD06 Ejercicio 1 En un libro nuevo y en la Hoja1, escriba la siguiente información.

1. Genere el código del empleado tomando en cuenta que está conformado por la primera letra del nombre, las dos últimas letras del apellido, un guión y el año de ingreso. 2. Concatene el nombre y el apellido. El resultado será el siguiente: Código

Dro-2000 Rto-2003 Aia-2004 Hos-2005 Cla-2004

Nombre Daniel Rita Alberto Hugo Carla

Apellido Castro Abanto Ubidia Llanos Vilela

Año de Ingreso

2000 2003 2004 2005 2004

Nombre Apellido Daniel Castro Rita Abanto Alberto Ubidia Hugo Llanos Carla Vilela

Ejercicio 2 En la Hoja2, escriba la siguiente información.

1. Obtenga la categoría del empleado utilizando la primera letra del código. 2. Obtenga el número de ingreso con los 3 últimos dígitos del código.

CIBERTEC

CARRERAS PROFESIONALES

26

3. Obtenga el año de ingreso teniendo en cuenta que son los 4 dígitos desde la segunda posición del código. 4. Genere el código especial Aa-C9999, tomando en cuenta lo siguiente: “A” es el primer caracter del nombre. “a” es el último caracter del apellido. “C” es la categoría. 9999 es el año de ingreso. El resultado será el siguiente:

Ejercicio 3 En la Hoja3, escriba la siguiente información. 1. Calcule los totales de ventas por tipo utilizando las funciones matemáticas más convenientes. 2. Complete el cuadro de ventas por mes utilizando las funciones matemáticas más convenientes.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

27

Ejercicio 4 En la Hoja4, escriba la siguiente información.

1. Calcule la suma de la cantidad por Origen (se muestran con la letra O) y la suma de la cantidad por Aplicación (se muestran con la letra A). Ejercicio 5 En la Hoja5, escriba la siguiente información.

1. Calcule las tasas efectivas para cada período teniendo en cuenta que la fórmula siguiente: n

1  i 1

Donde i es la tasa anual y n es el período.

CIBERTEC

CARRERAS PROFESIONALES

28

2.2 FUNCIONES estadísticas y de base de datos 2.2.1. Funciones estadísticas Las aplicaciones prácticas de las estadísticas de Excel son numerosas. Quizá tenga sus propias razones para utilizar las funciones estadísticas de Excel. A continuación, le presentamos algunos ejemplos a modo de sugerencia.

Puede insertar funciones de la categoría Estadísticas utilizando la ficha Fórmulas y el grupo Biblioteca de funciones. Luego, haga clic en el botón Más funciones.

 FUNCIÓN CONTAR.SI

Cuenta las celdas que no están en blanco dentro de un rango y que cumplen con el criterio especificado. Sintaxis: CONTAR.SI(rango;criterio)

Donde  Rango es el rango de celdas que desea contar.  Criterio es el criterio en forma de número, expresión o texto, que determina las celdas que se van a contar. Por ejemplo, los criterios pueden expresarse como 2, "3", ">4", "verdes", “*chocolates*”.

Existe la función CONTAR.SI.CONJUNTO, que cuenta el número de celdas, dentro del rango, que cumplen varios criterios.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

29

Ejemplo: Abra un libro nuevo y escriba la siguiente información en la Hoja1:

i.

Seleccione la celda C18.

ii. Se insertará la función CONTAR.SI con la ayuda del asistente para funciones. Para ello, haga clic en el botón

de la barra de fórmulas.

iii. Seleccione la categoría Estadísticas y localice a la función CONTAR.SI. Luego, haga clic en el botón Aceptar. iv. En el argumento Rango, seleccione C4:C15 y, en Criterio la celda, B18. Luego, haga clic en el botón Aceptar.

v. Observe el resultado 3 que corresponde a los 3 pedidos hechos para la marca LG. Complete el cuadro y el resultado que obtendrá será el siguiente:

Ejercicio: Utilice la función CONTAR.SI para responder las siguientes preguntas: ¿Cuántos televisores se vendieron?

CIBERTEC

CARRERAS PROFESIONALES

30

¿Cuántos pedidos se efectuaron cuya cantidad superó las 20 unidades?

 FUNCIÓN PROMEDIO.SI

Devuelve el promedio (media aritmética) de todas las celdas de un rango que cumplen unos criterios determinados. Sintaxis: PROMEDIO.SI(rango;criterio;rango_promedio) Donde:  Rango es la celda o las celdas cuyo promedio se desea obtener; deben contener números, o nombres, matrices o referencias que contengan números.  Criterio es el criterio en forma de número, expresión, referencia de celda o texto, que determina las celdas cuyo promedio se va a obtener. Por ejemplo, los criterios pueden expresarse como 32, "32", ">32", "*manzanas" o B4.  Rango_promedio es el conjunto real de celdas de las que se va a obtener el promedio. Si se omite, se utiliza el rango.

Existe la función PROMEDIO.SI.CONJUNTO, que devuelve el promedio (media aritmética) de todas las celdas que cumplen múltiples criterios.

Ejemplo. Escriba la siguiente información en la Hoja2:

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

i.

31

Seleccione la celda C18.

ii. Se insertará la función PROMEDIO.SI con la ayuda del asistente para funciones. Para ello, haga clic en el botón

de la barra de fórmulas.

iii. Seleccione la categoría Estadísticas y localice a la función PROMEDIO.SI. Luego, haga clic en el botón Aceptar. iv. En el argumento Rango, seleccione C4:C15; en Criterio la celda, B18; y en Rango_promedio, E4:E15. Luego, haga clic en el botón Aceptar.

v. Observe el resultado 3 que corresponde a los 3 pedidos hechos para la marca LG. Complete el cuadro y aplique 2 decimales a los resultados. Al terminar, obtendrá la siguiente información:

Ejercicio: Utilice la función PROMEDIO.SI para responder las siguientes preguntas: ¿Cuál es el promedio de los precios de las licuadoras que se vendieron? ¿Cuál es el promedio de la cantidad cuyos precios fueron inferiores a 1000?

CIBERTEC

CARRERAS PROFESIONALES

32

 FUNCIÓN MEDIANA

La mediana es el número que se encuentra en medio de un conjunto de números, es decir, la mitad de los números es mayor que la mediana y la otra mitad es menor. Sintaxis: MEDIANA(número1;número2; ...) Donde:  Número1, número2,... son entre 1 y 255 números cuya mediana desea obtener. Los argumentos deben ser números o nombres, matrices o referencias que contengan números. Si el argumento matricial o de referencia contiene texto, valores lógicos o celdas vacías, estos valores se pasan por alto; sin embargo, se incluirán las celdas con el valor cero. Si la cantidad de números en el conjunto es par, MEDIANA calcula el promedio de los números centrales. Vea la segunda fórmula del ejemplo.

Ejemplo: Copie la siguiente información en la Hoja3:

i.

Seleccione la celda D20.

ii. Haga clic en el botón

de la barra de fórmulas.

iii. Seleccione la categoría Estadísticas y la función MEDIANA. Luego, haga clic en el botón Aceptar.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

33

iv. Seleccione el rango B7:B18 y haga clic en el botón Aceptar.

v. Observe el resultado. Complete el cuadro y los resultados que obtendrá serán los siguientes:

Valor medio de producción de crucetas Valor medio de producción de poleas Valor medio de producción de cuñas

280 350 350

 FUNCIÓN MODA.UNO

Devuelve el valor que se repite con más frecuencia en una matriz o rango de datos. MODA es una medida de posición. Sintaxis: MODA.UNO(número1;número2; ...número30)

Donde:  Número1, número2... son de 1 a 255 argumentos cuya moda desea calcular. También, puede utilizar una matriz única o una referencia matricial en lugar de argumentos separados con punto y coma o coma (según sea la configuración regional del computador). Los argumentos deben ser números, nombres, matrices o referencias que contengan números. Si el argumento matricial o de referencia contiene texto, valores lógicos o celdas vacías, estos valores se pasan por alto; sin embargo, se incluirán las celdas con el valor cero. Si el conjunto de datos no contiene puntos de datos duplicados, MODA.UNO devuelve el valor de error #N/A. Existe la función MODA.VARIOS, que devuelve una matriz vertical de los valores que se repiten con más frecuencia en una matriz o rango de datos

CIBERTEC

CARRERAS PROFESIONALES

34

Ejemplo: Copie la información en la Hoja4 y agregue la siguiente información:

i.

Seleccione la celda D20.

ii. Haga clic en el botón

de la barra de fórmulas.

iii. Seleccione la categoría Estadísticas y la función MODA. Luego, haga clic en el botón Aceptar. iv. Seleccione el rango B7:B18 y haga clic en el botón Aceptar.

v. Observe el resultado. Complete el cuadro y los resultados que obtendrá serán los siguientes: Producción de crucetas más frecuente Producción de poleas más frecuente Producción de cuñas más frecuente

CARRERAS PROFESIONALES

250 350 150

CIBERTEC

SOFTWARE DE NEGOCIOS II

35

 FUNCIÓN FRECUENCIA

Calcula la frecuencia con que se repiten los valores de un rango y devuelve una matriz vertical de números. Sintaxis: FRECUENCIA(datos;grupos)

Donde  Datos es una matriz de un conjunto de valores cuyas frecuencias se desea contar. Si este rango de datos no contiene ningún valor, la función devuelve una matriz de ceros.  Grupos es una matriz de intervalos o una referencia a intervalos dentro de los cuales desea agrupar los valores del argumento datos. Si grupos no contiene ningún valor, FRECUENCIA devuelve el número de elementos contenido en datos. Consideraciones FRECUENCIA se introduce como una fórmula matricial después de seleccionar un rango de celdas adyacentes en las que se desea que aparezca el resultado de la distribución. El número de elementos de la matriz devuelta supera en una unidad el número de elementos de grupos. El elemento adicional de la matriz devuelta devuelve la suma de todos los valores superiores al mayor intervalo. Por ejemplo, al sumar tres rangos de valores (intervalos) introducidos en tres celdas, asegúrese de introducir FRECUENCIA en cuatro celdas para los resultados. La celda adicional devuelve el número de valores en grupos que sean superiores al valor del tercer intervalo. La función FRECUENCIA pasa por alto celdas en blanco y texto. Las fórmulas que devuelven matrices deben introducirse como fórmulas matriciales.

CIBERTEC

CARRERAS PROFESIONALES

36

Ejemplo: Escriba la siguiente información en la Hoja5:

i.

Seleccione el rango de celdas B7:B18 y escriba CRUCETAS en el cuadro de nombres. Luego, presione la tecla ENTER.

ii. Seleccione la celda B21. iii. Haga clic en el botón

de la barra de fórmulas.

iv. Seleccione la categoría Estadísticas y la función FRECUENCIA. Luego, haga clic en el botón Aceptar. v. En el argumento Datos, escriba CRUCETAS, en el argumento Grupos seleccione el rango A21:A26, y, luego, haga clic en el botón Aceptar.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

37

Observe que el resultado es 1, porque el número de producciones de las crucetas en el rango de [150, 200> es sólo 1.

Ahora se debe convertir la fórmula en su forma matricial para llenar el cuadro. vi. Seleccione el rango B21:B26. vii. Presione la tecla de función F2 y, luego, la combinación CTRL + SHIFT + ENTER. viii. Observe el resultado: Producción de Crucetas 150 200 250 300 350 400

1 0 3 2 1 4

Los resultados deben interpretarse del siguiente modo:  Producción de crucetas menores o iguales a 150 =  Producción de crucetas entre 151 y 200 =  Producción de crucetas entre 201 y 250 =  Producción de crucetas entre 251 y 300 =  Producción de crucetas entre 301 y 350 =  Producción de crucetas entre 351 y 400 =

1 0 3 2 1 4

Ejercicio: Realice un cuadro similar para la producción de Poleas. Producción de Poleas 150 250 350 450

2 1 4 4

 FUNCIÓN JERARQUIA.EQV

Devuelve la jerarquía de un número en una lista de números. La jerarquía de un número es su tamaño en comparación con otros valores de la lista. (Si ordenara la lista, la jerarquía del número sería su posición.) Sintaxis: JERARQUIA.EQV(número;referencia;orden)

CIBERTEC

CARRERAS PROFESIONALES

38

Donde  Número es el número cuya jerarquía desea saber.  Referencia es una matriz de una lista de números o una referencia a una lista de números. Los valores no numéricos se pasan por alto.  Orden es un número que especifica cómo clasificar el argumento número.  Si el argumento orden es 0 (cero) o se omite, Excel determina la jerarquía de un número como si la lista definida por el argumento referencia fuese ordenada en forma descendente.  Si el argumento orden es diferente de cero, Microsoft Excel determina la jerarquía de un número como si la lista definida por el argumento referencia se ordenara en forma ascendente. La función JERARQUÍA.EQV asigna la misma jerarquía a los números duplicados. Sin embargo, la presencia de números duplicados afecta la jerarquía de los números subsiguientes. Por ejemplo, en una lista de números enteros ordenados en orden ascendente, si el número 10 aparece dos veces y tiene una jerarquía de 5, entonces el número 11 tendría una jerarquía de 7 (ningún número tendría jerarquía de 6).

Ejemplo: Escriba la siguiente información en la Hoja6:

i.

Seleccione el rango C7:C16 y defina el nombre Ponderado.

ii. Seleccione la celda D7. iii. Haga clic en el botón

de la barra de fórmulas.

iv. Seleccione la categoría Estadísticas y la función JERARQUIA.EQV Luego, haga clic en el botón Aceptar. v. En el argumento Número, seleccione la celda C7, en el argumento Referencia escriba Ponderado. Luego, presione Aceptar.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

39

vi. El resultado será 1, porque el promedio ponderado del alumno le permite estar en el primer lugar. Autollene la fórmula para completar el cuadro.

Guarde el archivo con el nombre de Funciones Estadísticas.

CIBERTEC

CARRERAS PROFESIONALES

40

2.2.2. Funciones de Bases de Datos Como se ha visto anteriormente en Excel, una lista o Base de Datos es un conjunto de información relacionada entre sí. Para poder obtener información importante proveniente de una lista de datos, Excel proporciona funciones especiales llamadas Funciones de Bases de datos. Estas requieren establecer, previamente, los criterios o condiciones para los cuales llevará a cabo algún cálculo especial como una suma, promedio, etc. Sintaxis General: BDFUNCION(base_de_datos;nombre_de_campo;criterios) Donde  BDFUNCION es el nombre del proceso a realizar, va precedido de la sigla BD. Para el curso aprenderemos las funciones BDSuma, BDPromedio, BDMax, BDMin y BDContar.  Base_de_datos es el rango de celdas que compone la lista o base de datos. Puede utilizar un rango definido; por ejemplo, Datos.  Nombre_de_campo indica el campo que se utiliza en la función. Nombre_de_campo puede ser texto con el rótulo encerrado entre dobles comillas, como por ejemplo "Edad" o "Pedido $", o como un número que represente la posición de la columna en la lista: 1 para la primera columna, 2 para la segunda y así sucesivamente.  Criterios es el rango de celdas que contiene las condiciones especificadas. Puede utilizar cualquier rango en el argumento; incluso, uno definido previamente.

Si utiliza un nombre de rango definido, no olvide que los nombres definidos no utilizan comillas.

Consideraciones  



Cualquier rango se puede usar como argumento criterios, siempre que incluya por lo menos un nombre de campo y por lo menos una celda debajo del nombre de campo para especificar un valor de comparación de criterios. Aunque el rango de criterios puede ubicarse en cualquier parte de la hoja de cálculo, no coloque el rango de criterios debajo de la lista. Si agrega más información a la lista, la nueva información se agrega a la primera fila debajo de la lista. Si la fila de debajo no está vacía, Microsoft Excel no podrá agregar la nueva información. Asegúrese de que el rango de criterios no queda superpuesto a la lista.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

41

Ejemplos: Para desarrollar los siguientes ejemplos, escriba la información en un libro nuevo y en la Hoja1.

Para facilitar el uso de las funciones de bases de datos, puede utilizarse nombres de rango definidos. También, necesita la definición de criterios o condiciones que son las que determinarán qué información debe calcularse.

Antes de insertar las funciones de bases de datos, se deberá definir el nombre llamado DATOS con el rango de celdas.

CIBERTEC

CARRERAS PROFESIONALES

42

Escriba la siguiente información en la Hoja2:

Para obtener la información del reporte especial, se debe escribir el rango de criterios que satisfagan las condiciones solicitadas. Tome en cuenta que el reporte deberá contener la información de pedidos que atendió la empleada Valeria Villacorta en el período del 1/1/2009 al 30/6/2009 que corresponde al primer semestre del año 2009.

Definición de criterios Escriba la siguiente información en la Hoja2:

Observe que los criterios escritos son similares a los que se utilizaron con los filtros avanzados. Tome en cuenta las mismas consideraciones que ya se comentaron respecto a los criterios de los filtros avanzados.

 FUNCIÓN BDSUMA Suma los números de una columna de una lista o base de datos que concuerden con las condiciones especificadas. Sintaxis: BDSUMA(base_de_datos;nombre_de_campo;criterios)

Ejemplo: Seleccione la celda B7. i.

Haga clic en el botón

de la barra de fórmulas.

ii. Seleccione la categoría Base de Datos y la función BDSUMA. Luego, haga clic en el botón Aceptar.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

43

iii. Escriba, en cada argumento, la información tal como se muestra en la siguiente ventana:

iv. Luego, haga clic en el botón Aceptar y observe que el resultado será 10189,27.

 FUNCIÓN BDPROMEDIO

Devuelve el promedio de los valores de una columna de una lista o base de datos que coinciden con las condiciones especificadas.

Sintaxis: BDPROMEDIO(base_de_datos;nombre_de_campo;criterios)

Ejemplo: Seleccione la celda B8. i.

Haga clic en el botón

de la barra de fórmulas.

ii. Seleccione la categoría Base de Datos y la función BDPROMEDIO. Luego, haga clic en el botón Aceptar. iii. Escriba, en cada argumento, la información tal como se muestra en la siguiente ventana:

CIBERTEC

CARRERAS PROFESIONALES

44

iv. Luego, haga clic en el botón Aceptar y observe que el resultado será 2547,3175.

 FUNCIÓN BDMIN - BDMAX

Devuelve el valor mínimo / máximo de una columna de una lista o base de datos que coincida con las condiciones especificadas. Sintaxis: BDMIN(base_de_datos;nombre_de_campo;criterios) Sintaxis: BDMAX(base_de_datos;nombre_de_campo;criterios)

Ejemplo: Seleccione la celda B9. i.

Haga clic en el botón

de la barra de fórmulas.

ii. Seleccione la categoría Base de Datos y la función BDMIN. Luego, haga clic en el botón Aceptar. iii. Escriba, en cada argumento, la información tal como se muestra en la siguiente ventana:

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

45

iv. Luego, haga clic en el botón Aceptar y observe que el resultado será 2513,97.

Ejemplo: Seleccione la celda B10. i.

Haga clic en el botón

de la barra de fórmulas.

ii. Seleccione la categoría Base de Datos y la función BDMAX. Luego, haga clic en el botón Aceptar. iii. Escriba en cada argumento la información tal como se muestra en la siguiente ventana:

iv. Luego, haga clic en el botón Aceptar y observe que el resultado será 2565,83.

 FUNCIÓN BDCONTAR

Cuenta las celdas que contienen números en una columna de una lista o base de datos y que concuerdan con las condiciones especificadas.

Sintaxis: BDCONTAR(base_de_datos;nombre_de_campo;criterios) El argumento nombre_de_campo es opcional. Si se pasa por alto, BDCONTAR cuenta todos los registros de la base de datos que coinciden con los criterios. Ejemplo: Seleccione la celda D7. i.

Haga clic en el botón

de la barra de fórmulas.

ii. Seleccione la categoría Base de Datos y la función BDCONTAR. Luego, haga clic en el botón Aceptar.

CIBERTEC

CARRERAS PROFESIONALES

46

v. Escriba, en cada argumento, la información tal como se muestra en la siguiente ventana:

iii. Luego, haga clic en el botón Aceptar y observe que el resultado será 4.

La función BDCONTAR y BDCONTARA son muy similares. La única diferencia es que BDCONTARA, además de contar datos numéricos, cuenta datos tipo texto.

 FUNCIÓN BDCONTARA

Cuenta las celdas que no están en blanco dentro de una columna de una lista o base de datos que cumplen las condiciones especificadas. Sintaxis: BDCONTARA(base_de_datos;nombre_de_campo;criterios) El argumento nombre_de_campo es opcional. Si se pasa por alto, BDCONTARA cuenta todos los registros de la base de datos que coinciden con los criterios. Ejemplo: Seleccione la celda D7 y presione la tecla SUPR. i.

Haga clic en el botón

de la barra de fórmulas.

ii. Seleccione la categoría Base de Datos y la función BDCONTARA. Luego haga clic en el botón Aceptar. vi. Escriba, en cada argumento, la información tal como se muestra en la siguiente ventana:

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

47

iii. Luego, haga clic en el botón Aceptar y observe que el resultado será 4. Aplique formato de celdas con 2 decimales y compare el resultado final con el siguiente:

Ejercicio:

Escriba la siguiente información en la Hoja3 y complete el cuadro utilizando funciones de bases de datos:

Guarde el archivo con el nombre de Funciones de Bases de Datos.

CIBERTEC

CARRERAS PROFESIONALES

48

ACTIVIDADES 07: En un nuevo archivo, asígnele como nombre ACTIVIDAD07 y resuelva los siguientes ejercicios:

1. En la Hoja1, escriba los siguientes datos y, utilizando las funciones estadísticas convenientes, complete la información faltante.

Los resultados serán así:

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

49

2. En la Hoja2, escriba los siguientes datos y, utilizando las funciones estadísticas convenientes complete la información faltante.

Los resultados serán los siguientes:

CIBERTEC

CARRERAS PROFESIONALES

50

3. En la Hoja3, escriba los siguientes datos y, utilizando las funciones de bases de datos convenientes, complete la información faltante teniendo en cuenta que pertenece al local de San Isidro.

4. En la Hoja4, escriba los siguientes datos y, utilizando las funciones convenientes, complete la información faltante utilizando los datos de la base de datos anterior.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

51

5. En la Hoja6, escriba los siguientes datos y halle las tendencias para los años 5, 6, 7 y 8.

6. En la Hoja7, escriba los siguientes datos y halle las frecuencias para el conjunto de datos mostrado.

CIBERTEC

CARRERAS PROFESIONALES

52

2.3 FUNCIONES lógicas y de búsqueda 2.3.1. Funciones Lógicas Este tipo de funciones son sumamente importantes sobre todo por la frecuencia con las que se usan. Por lo general, son utilizadas en funciones anidadas.

Puede insertar funciones de la categoría Lógicas utilizando la ficha Fórmulas, y el grupo Biblioteca de funciones.

 FUNCIÓN Y Devuelve VERDADERO si todos los argumentos son VERDADEROS; devuelve FALSO si uno o más argumentos son FALSOS. Sintaxis: Y(valor_lógico1;valor_lógico2; ... valor_lógico255)

Donde:  Valor lógico1; … valor_lógico255 son entre 1 y 255 condiciones que desea comprobar y que pueden ser verdadero o falso, y que pueden ser valores lógicos, matriciales o referencias. Si recuerda, en lógica, la conjunción se representaba a través del símbolo ^ y la conjunción sólo es verdadera si los componentes son verdaderos. Aquí se le muestra la tabla de verdad para la conjunción que es, exactamente, la de la función Y.

CARRERAS PROFESIONALES

p

q

p^q

V

V

V

V

F

F

F

V

F

F

F

F

CIBERTEC

SOFTWARE DE NEGOCIOS II

53

Los argumentos deben evaluarse como valores lógicos, como VERDADERO o FALSO, o los argumentos deben ser matrices o referencias que contengan valores lógicos. Si un argumento matricial o de referencia contiene texto o celdas vacías, dichos valores se pasarán por alto. Si el rango especificado no contiene valores lógicos, la función Y devuelve el valor de error #¡VALOR!

Ejemplo: En un archivo nuevo, escriba la siguiente información en la Hoja1:

A continuación, se evaluará si el valor A es mayor que 10 y si el valor B es menor que 5. La respuesta deberá ser VERDADERO según la tabla de verdad de la conjunción. i.

Seleccione la celda C5.

ii. Haga clic en el botón

de la barra de fórmulas.

iii. Seleccione la categoría Lógicas y la función Y. Luego, haga clic en el botón Aceptar. iv. En el argumento Valor_lógico1, escriba A5>10 y, en Valor_lógico2, escriba B510 Y B 10 y, en Valor_lógico2, escriba B510 Y B 10 O B =13; en el Valor_si_verdadero, escriba “Aprobó Curso” y en Valor_si_falso, escriba “Desaprobó Curso”. Luego, haga clic en el botón Aceptar.

v. Autollene las celdas siguientes y observe el resultado.

Cuadro de Notas CODIGO

NOTA

A120 A121 A122 A123

14 12 10 8

COMENTARIO Aprobó el curso Desaprobó el curso Desaprobó el curso Desaprobó el curso

Ejercicio: Escriba la siguiente información en la Hoja3 para desarrollar otro ejemplo.

i.

Seleccione la celda D5.

ii. Haga clic en el botón

de la barra de fórmulas.

iii. Seleccione la categoría Lógicas y la función Si. Luego, haga clic en el botón Aceptar. iv. En el argumento Prueba_logica, escriba B5=”A”; en Valor_si_verdadero, escriba C5*15%; y en el Valor_si_falso, escriba 0. Luego, haga clic en el botón Aceptar.

CIBERTEC

CARRERAS PROFESIONALES

58

v. Autollene las celdas siguientes, aplique formato millares y observe el resultado:

Guarde el archivo con el nombre de Funciones Lógicas.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

59

2.3.2. Funciones de Búsqueda Las funciones de búsqueda tienen tanto uso como las funciones lógicas. Esto se debe a que permiten localizar información asociada a una fila o columna y, con ello, es posible preparar reportes sencillos.

Puede insertar funciones de la categoría Búsqueda y referencia utilizando la ficha Fórmulas, y el grupo Biblioteca de funciones.

 FUNCIÓN CONSULTAV

Realiza una búsqueda vertical en la primera columna de un rango o matriz y devuelve el dato asociado de una columna especificada. Sintaxis: CONSULTAV(valor_buscado;matriz_buscar_en; indicador_columnas;ordenado) Donde  Valor_buscado es el valor que se busca en la primera columna de la matriz. Valor_buscado puede ser un valor, una referencia o una cadena de texto.  Matriz_buscar_en es la tabla de información donde se buscan los datos. Utilice una referencia a un rango o un nombre de rango.  Los valores de la primera columna de matriz_buscar_en pueden ser texto, números o valores lógicos.  Indicador_columnas es el número de columna de matriz_buscar_en desde la cual debe devolverse el valor coincidente.  Ordenado es un valor lógico que especifica si CONSULTAV debe localizar una coincidencia exacta o aproximada. Si se omite o es VERDADERO, devolverá una coincidencia aproximada. En otras palabras, si no localiza ninguna coincidencia exacta, devolverá el siguiente valor más alto inferior a valor_buscado. Si es FALSO, CONSULTAV encontrará una coincidencia exacta. Si no encuentra ninguna, devolverá el valor de error # N/A. Utilice CONSULTAV cuando los valores que desea localizar estén ubicados en una columna situada a la izquierda de los datos que desea encontrar, es decir, se utiliza en cuadros organizados verticalmente.

CIBERTEC

CARRERAS PROFESIONALES

60

Antes de utilizar la función CONSULTAV se definirán las matrices o rangos a usarse, ello simplemente para facilitar la comprensión de la función.

Ejemplo: Para desarrollar el siguiente ejemplo, escriba la información en un libro nuevo y en la Hoja1.

Definición de una matriz 

Seleccione el rango A6:E11 y dele el nombre de ARTICULOS.



Seleccione el rango B15:F17 y dele el nombre de MARCAS.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II



61

Seleccione el rango A21:B24 y dele el nombre de MENSAJES.

Escriba la siguiente información en la Hoja2:

Utilizando la función CONSULTAV, se escribirá el nombre del artículo asociado al código ingresado. Para ello, i.

Seleccione la celda B4.

ii. Haga clic en el botón

de la barra de fórmulas.

iii. Seleccione la categoría Búsqueda y Referencia y la función CONSULTAV. Luego, haga clic en el botón Aceptar. iv. Escriba, en cada argumento, la información tal como se muestra:

CIBERTEC

CARRERAS PROFESIONALES

62

Observe que el valor buscado es B3, porque este dato se encuentra en la primera columna de la matriz llamada ARTICULOS; el indicador de columnas es 2, porque el nombre del artículo asociado a este código se encuentra en la segunda columna de la matriz; y ordenado está en 0, porque la búsqueda que se necesita realizar debe buscar la coincidencia exacta. Note que también podría usar, en lugar de 0, el valor lógico FALSO.

v. Luego, haga clic en el botón Aceptar y observe que el resultado será Cuaderno 100h. Ejercicio1: Utilizando la función CONSULTAV, complete la información de la marca, precio y stock que están asociados al código ingresado.

Ejercicio2: Utilizando la función CONSULTAV, complete la información que corresponde al mensaje teniendo en cuenta que es el stock el dato asociado a la tabla o matriz MENSAJES. Indique qué información escribió en cada argumento y por qué. Argumento

Valor

Justificación o razón

Valor_buscado Matriz_buscar_en Indicador_columnas Ordenado

 FUNCIÓN CONSULTAH

Realiza una búsqueda vertical en la primera columna de un rango o matriz y devuelve el dato asociado de una columna especificada. Sintaxis: CONSULTAH(valor_buscado;matriz_buscar_en; indicador_filas;ordenado) Donde  Valor_buscado es el valor que se busca en la primera fila de la matriz. Valor_buscado puede ser un valor, una referencia o una cadena de texto.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

  



63

Matriz_buscar_en es la tabla de información donde se buscan los datos. Utilice una referencia a un rango o un nombre de rango. Los valores de la primera fila de matriz_buscar_en pueden ser texto, números o valores lógicos. Indicador_filas es el número de fila de matriz_buscar_en desde la cual debe devolverse el valor coincidente. Si indicador_filas es 1, devuelve el valor de la primera fila en matriz_buscar_en; si indicador_filas es 2, devuelve el valor de la segunda fila en matriz_buscar_en y así sucesivamente. Si indicador_filas es menor que 1, CONSULTAH devuelve el valor de error #¡VALOR!; si indicador_filas es mayor que el número de filas en matriz_buscar_en, CONSULTAH devuelve el valor de error #¡REF! Ordenado es un valor lógico que especifica si CONSULTAH debe localizar una coincidencia exacta o aproximada. Si se omite o es VERDADERO, devolverá una coincidencia aproximada. En otras palabras, si no localiza ninguna coincidencia exacta, devolverá el siguiente valor más alto inferior a valor_buscado. Si es FALSO, CONSULTAH encontrará una coincidencia exacta. Si no encuentra ninguna, devolverá el valor de error # N/A.

Realiza una búsqueda horizontal en la primera fila de un rango o matriz y devuelve el dato asociado de una fila especificada. Use CONSULTAH cuando los valores de comparación se encuentren en la primera fila de una tabla de datos y desee encontrar información que se encuentre dentro de un número especificado de filas. Utilice esta función en cuadros organizados horizontalmente.

Ejemplo: Utilizando la función CONSULTAH, se escribirá el porcentaje de oferta asociado a la marca. Para ello, realice lo siguiente: i.

Seleccione la celda B7.

ii. Haga clic en el botón

de la barra de fórmulas.

iii. Seleccione la categoría Búsqueda y Referencia y la función CONSULTAH. Luego, haga clic en el botón Aceptar. iv. Escriba, en cada argumento, la información tal como se muestra:

CIBERTEC

CARRERAS PROFESIONALES

64

Observe que el valor buscado es B7 porque este dato se encuentra en la primera fila de la matriz llamada MARCAS; el indicador de filas es 2, porque el porcentaje de oferta asociado a esta marca se encuentra en la segunda fila de la matriz; y ordenado está en 0, porque la búsqueda que se necesita realizar debe buscar la coincidencia exacta. Note que también podría usar, en lugar de 0, el valor lógico FALSO.

v. Luego, haga clic en el botón Aceptar y observe que el resultado será 0.05. vi. Aplique formato de estilo porcentual para tener como resultado 5%.

Ejercicio: Utilizando la función CONSULTAH, complete la información del regalo que está asociado a la marca.

Guarde el archivo con el nombre de Funciones de Búsqueda.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

65

ACTIVIDAD 08: En un archivo nuevo, asigne como nombre ACTIVIDAD08, y resuelva los siguientes ejercicios Ejercicio 01: 1. En un libro nuevo y en la Hoja1, escriba la siguiente información:

2. En el campo OBSERVACION, muestre el comentario “Máquina operativa” si se ha utilizado la máquina; en caso contrario, deberá mostrar “Revisión Pendiente”. El resultado será el siguiente:

CIBERTEC

CARRERAS PROFESIONALES

66

Ejercicio 02 1. En un libro nuevo y en la Hoja2, escriba la siguiente información:

2. Utilizando funciones lógicas, escriba “Aprobado” en la columna OBSERVACION si ambas notas son aprobadas y “Debe dar sustitutorio” si alguna es desaprobatoria. Considere como mínima nota aprobatoria 13. El resultado será el siguiente:

3. Agregue una columna llamada CURSO APROBADO que indique el nombre del curso que obtuvo nota aprobatoria. Si fueran ambos cursos, deberá mostrar el texto “Aprobó ambos cursos”. Ejercicio 03 1. En la Hoja3, escriba la siguiente información:

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

67

2. Utilizando las funciones más convenientes, complete la información faltante, tomando en cuenta lo siguiente: 

La fecha de compra debe ingresarse.



La forma de pago puede ser Contado o Crédito.



Si la forma de pago es al Contado, el % Oferta es de 20%; en caso contrario, el % Oferta es 0.



El %IGV será del 19% si la fecha de compra es menor al 28/7/2010; en caso contrario, será del 18%.



El Importe debe ingresarse y mostrarse con 2 decimales.



Deberá mostrar automáticamente la Oferta y el IGV correspondiente.



El Total es el importe menos la Oferta.

Ejercicio 04 1. En la Hoja4, escriba la siguiente información.

2. Se tiene la lista de datos y se desea generar un pequeño reporte de tal modo que, al ingresar un código a buscar, se muestren automáticamente la descripción y stock asociados al código. 3. Asimismo, deberá mostrar una oferta del 15% sólo en el caso de los monitores; en caso contrario, la oferta será del 5%.

CIBERTEC

CARRERAS PROFESIONALES

68

Ejercicio 05 1. En la Hoja5, escriba la siguiente información:

2. diseñe un reporte con la información anterior, tomando en cuenta las siguientes consideraciones: 

El campo Id.Pedido deberá seleccionarse de una lista.



Al elegir un Id.Pedido; los datos del cliente, empleado, fecha de pedido, forma de envío, Pedido $ y Local deberán mostrarse de manera automática.



Agregue un campo llamado Comentario Especial. Este deberá mostrar el mensaje correspondiente según la siguiente tabla: Pedido $ < 2500 >=2500



Comentario Especial Revise historial del Cliente. Pedido en parámetros esperados.

También, agregue un campo llamado Descuento Local. Este deberá mostrar 10%, en caso el local sea San Isidro o La Molina; si fuera Lima o Callao, 15%; y en caso el local no exista, deberá mostrar el siguiente mensaje: “No existe local”. Resuelva este ejercicio utilizando las funciones vistas con anterioridad y que sean, estrictamente, necesarias.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

69

2.4 FUNCIONES financieras Ejecutan operaciones contables comunes, como determinar los pagos de un préstamo, el valor futuro o el valor neto actual de una inversión, y los valores de obligaciones y bonos.

Puede insertar funciones de la categoría Financieras utilizando la ficha Fórmulas y el grupo Biblioteca de funciones.

Los argumentos más comunes de las funciones financieras son los siguientes: Valor futuro (vf) Número de períodos (nper)

CIBERTEC

Valor de la inversión o del préstamo una vez realizados todos los pagos

Número total de pagos o períodos de una inversión

Pago (pago)

Importe pagado, periódicamente, en una inversión o préstamo. No puede cambiar durante la vigencia de la anualidad.

Valor actual (va)

Valor de una inversión o préstamo al comienzo del período de inversión. Por ejemplo, el valor presente de un préstamo es el importe principal que se toma prestado.

Interés (interes)

Interés o el descuento de un préstamo o una inversión

Tipo (tipo)

Intervalo en que se realizan los pagos durante el período de pago, como al comienzo o al final de mes

CARRERAS PROFESIONALES

70

2.4.1. Funciones de Pago  FUNCIÓN PAGO

Devuelve el pago periódico de una anualidad, basándose en pagos constantes y la tasa de interés constante. Sintaxis: PAGO(tasa,nper,va,[vf],[tipo])

Donde  Tasa es el tipo de interés del préstamo.  Nper es el número total de pagos del préstamo.  Va es el valor actual o lo que vale ahora la cantidad total de una serie de pagos futuros. También, se conoce como el principal.  Vf es el valor futuro o un saldo en efectivo que desea lograr después de efectuar el último pago. Si el argumento vf se omite, se asume que el valor es 0 (es decir, el valor futuro de un préstamo es 0).  Tipo es el número 0 (cero) ó 1 e indica el vencimiento de los pagos. TIPO

VENCIMIENTO PAGO

0 u omitido

Al final del período

1

Al inicio del período

Puede apreciar que de manera automática aparece un esquema en el lado izquierdo de la lista. Con ello, puede mostrar u ocultar la información por niveles; por ejemplo, para observar los resultados de una mejor manera, utilice el nivel 2. Para ello, haga clic en el número 2 del nivel de esquema.

Consideraciones 

Mantenga uniformidad en el uso de las unidades con las que especifica los argumentos tasa y nper.



En todos los argumentos, el efectivo que paga, por ejemplo, depósitos en cuentas de ahorros, se representa con números negativos; el efectivo que recibe, por ejemplo, cheques de dividendos, se representa con números positivos.

Ejemplo1: Se requiere calcular la cuota fija mensual que deberá pagarse por un préstamo escolar de 3500 soles durante 12 meses con una tasa efectiva anual del 29.85%. Escriba la siguiente información en un nuevo libro y en la Hoja1:

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

i.

71

Seleccione la celda B7 para calcular la tasa efectiva mensual e ingrese la siguiente fórmula: =((1+B5)^(1/B6))-1 La fórmula financiera corresponde al cálculo de la tasa efectiva mensual, dada una tasa anual (i) y un período en meses (n).

En general, la fórmula financiera corresponde al cálculo de la tasa efectiva, dada una tasa anual (i) y un período (n). n

1  i 1

La cuota fija mensual se obtendrá con la función PAGO. ii. Seleccione la celda B11 y escriba la fórmula siguiente: =PAGO(B7;B6;-B4)

El dato del valor actual (VA) es el monto del préstamo escolar y debe estar como un valor negativo.

iii. El resultado será el siguiente:

Los argumentos VF y TIPO son opcionales y no han sido utilizados en la función, ya que no son requeridos en la solución del caso.

CIBERTEC

CARRERAS PROFESIONALES

72

Ejemplo2: Se desea obtener el importe que generaría una cuota vencida y una cuota adelantada por un préstamo de 1500 soles durante 1 año con una tasa de interés mensual del 4%. Escriba la siguiente información:

Para calcular el importe de la cuota vencida, se debe tomar en cuenta, en la función PAGO, que el argumento TIPO deberá ser 0 y, para calcular el importe de la cuota adelantada, se debe tomar en cuenta que el argumento TIPO deberá ser 1

i.

Seleccione la celda E7 para calcular la cuota mensual vencida.

ii. Escriba la siguiente fórmula: =PAGO(E5;E6;-E4;;0) iii. Seleccione la celda E8 para calcular la cuota mensual adelantada. iv. Escriba la siguiente fórmula: =PAGO(E5;E6;-E4;;1)

Observe que los argumentos utilizados han sido TASA, NPER, VA (que debe ir en su forma negativa) y Tipo. Los demás argumentos no son requeridos en la solución del caso.

El resultado final será el siguiente:

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

73

Ejemplo3: Se desea obtener 20000 dólares luego de 1 año y medio. ¿Cuánto se debe depositar mensualmente en el banco si éste paga 1.5% como tasa de interés mensual? Escriba la siguiente información:

i.

Seleccione la celda H7 para calcular la cuota mensual.

ii. Escriba la siguiente fórmula: =PAGO(H6;H5;;-H4) Observe que los argumentos utilizados han sido TASA, NPER y VF (que debe ir en su forma negativa). Los demás argumentos no son requeridos en la solución del caso.

El resultado será el siguiente:

CIBERTEC

CARRERAS PROFESIONALES

74

 FUNCIÓN VF

Devuelve el Valor Futuro de una inversión basándose en pagos periódicos constantes y en una tasa de interés constante. Sintaxis: VF(tasa;nper;pago;va;tipo)

Donde  Tasa es el tipo de interés del préstamo.  Nper es el número total de pagos del préstamo.  Pago es el pago efectuado en cada periodo y no puede cambiar durante la vigencia de la inversión.  Va es el valor actual o la suma total de una serie de pagos futuros. Si se omite VA = 0  Tipo es el número 0 (cero) ó 1 e indica el vencimiento de los pagos. TIPO

VENCIMIENTO PAGO

0

Al final del período

1

Al inicio del período

En todos los argumentos, el efectivo que paga, por ejemplo, depósitos en cuentas de ahorros, se representa con números negativos; el efectivo que recibe, por ejemplo, cheques de dividendos, se representa con números positivos.

Ejemplo: Se desea capitalizar los cuotas de 680 soles mensuales, los cuales se depositan al Banco Internacional durante 1año, a una tasa de interés efectiva del 3.5%, ¿cuánto se cobrará al final del plazo? Escriba la siguiente información en la Hoja2:

i.

Seleccione la celda B7 para calcular la cuota mensual.

ii. Escriba la siguiente fórmula: =VF(B5;B6;-B4)

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

75

El resultado final será el siguiente:

 FUNCIÓN VA

Devuelve el valor actual de una inversión. El valor actual es el valor que tiene, actualmente, la suma de una serie de pagos que se efectuarán en el futuro. Por ejemplo, cuando pide dinero prestado, la cantidad del préstamo es el valor actual para el prestamista. Sintaxis: VA(tasa;nper;pago;vf;tipo) Donde  Tasa es el tipo de interés del préstamo.  Nper es el número total de pagos del préstamo.  Pago es el pago efectuado en cada periodo y no puede cambiar durante la vigencia de la inversión.  Vf es el valor futuro o saldo en efectivo que se desea lograr después de efectuar el último pago  Tipo es el número 0 (cero) ó 1 e indica el vencimiento de los pagos. TIPO

VENCIMIENTO PAGO

0

Al final del período

1

Al inicio del período

Ejemplo: Hallar el valor actual de $5000 pagaderos en 5 años con una tasa anual efectiva de 6%. Escriba la siguiente información en la Hoja3:

i.

Seleccione la celda B6 para calcular el valor actual.

ii. Escriba la siguiente fórmula: =VA(B3;B4;;-B2)

CIBERTEC

CARRERAS PROFESIONALES

76

Observe que no ha sido necesario hacer ninguna conversión en los datos ya que todos guardan relación.

El resultado final será el siguiente:

Ejercicio: Ahorramos 350 mensuales durante 5 años en un banco que paga el 18% nominal anual y deseamos saber cuánto representan estas mensualidades al día de hoy. Escriba la siguiente información:

Observe que será necesario obtener la tasa efectiva mensual. ¿Recuerda cómo obtenerlo? En la celda E6, escriba la siguiente fórmula: =((1+E3)^(1/12))-1

i.

Seleccione la celda E7 para calcular el valor actual.

ii. Escriba la siguiente fórmula: =VA(E6;E4*12;-E2) El resultado final será el siguiente:

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

77

2.4.2. Cronogramas de Pago Después de conocer las funciones principales para pagos, veremos este cuadro muy importante, pues nos permitirá conocer y evaluar las exigencias de las fuentes de financiamiento. En este cuadro se reportan: i.

El monto del préstamo

ii.

Los saldos deudores

iii.

Las amortizaciones realizadas

iv.

Los intereses de cada período

v.

Las cuotas que debemos cancelar

En las finanzas la expresión amortizar se utiliza para denominar un proceso financiero mediante el cual se extingue, gradualmente, una deuda y sus intereses por medio de pagos periódicos, que pueden ser iguales o diferentes. Para la amortización de deudas se aplican diversos sistemas y dentro de cada sistema, hay numerosas variantes. Así mismo para una deuda determinada, se llama saldo al cabo de un tiempo, a la cantidad o suma que en ese momento aún falta por amortizar.

CIBERTEC

CARRERAS PROFESIONALES

78

 FUNCIÓN PAGOPRIN

Devuelve el pago sobre el capital de una inversión durante un período determinado basándose en pagos periódicos y constantes, y en una tasa de interés constante. Sintaxis: PAGOPRIN(tasa;período;nper;va;vf;tipo) Donde  Tasa es la tasa de interés por período.  Período especifica el período, que debe estar entre 1 y el valor de nper.  Nper es el número total de períodos de pago en una anualidad.  Va es el valor actual, es decir, el valor total que tiene, actualmente, una serie de pagos futuros.  Vf es el valor futuro o un saldo en efectivo que se desea lograr después de efectuar el último pago. Si el argumento vf se omite, se supone que el valor es 0 (es decir, el valor futuro de un préstamo es 0).  Tipo es el número 0 ó 1 e indica cuándo vencen los pagos. TIPO

VENCIMIENTO PAGO

0 u omitido

Al final del período

1

Al inicio del período

Consideraciones  Mantenga uniformidad en el uso de las unidades con las que especifica los argumentos tasa y nper.  Si realiza pagos mensuales de un préstamo de cuatro años con un interés anual del 12 por ciento, calcule la tasa efectiva mensual con la fórmula. Ejemplo. Elabore el programa de amortización (cronograma de pagos) para una deuda de S/. 1,000 pactada a una tasa de interés anual del 10%, mediante la modalidad de cuotas constantes en 4 años. Escriba el siguiente cuadro en la hoja 4:

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

i.

79

Seleccione la celda B9 para calcular la cuota mensual y escriba = B4

ii. En la celda C10, se calculará la amortización del primer mes. Escriba la siguiente fórmula =PAGOPRIN($B$6;A10;$B$5;-$B$4) o ingrésela a través del botón Insertar función.

Observe que las celdas que representan a la tasa, período y valor actual (deuda), utilizan los direccionamientos absolutos.

iii. Copie la fórmula hasta el cuarto período. Los resultados que obtendrá serán los siguientes:

Para seguir con el cronograma, se deben calcular los intereses. Para ello, abordaremos la función PAGOINT.

 FUNCIÓN PAGOINT

Devuelve el interés pagado en un período específico por una inversión basándose en pagos periódicos constantes y en una tasa de interés constante. Sintaxis: PAGOINT(tasa;período;nper;va;vf;tipo)

Ahora, se calculará el interés pagado año tras año.

CIBERTEC

CARRERAS PROFESIONALES

80

i.

En la celda D10, escriba la siguiente fórmula: =PAGOINT($B$6;A10;$B$5;-$B$4) o ingrésela a través del botón Insertar función.

ii. Copie la fórmula hasta el cuarto período. Los resultados que obtendrá serán los siguientes:

Para completar el cronograma realice el siguiente procedimiento: Complete el saldo para cada período (año) i.

En la celda B10, escriba la fórmula =B9-C10.

ii. Luego, cópiela hasta el final del período.

Complete el pago para cada período (año) iii. En la celda E10, escriba la fórmula =PAGO($B$6;$B$5;-$B$4) o ingrésela a través del botón Insertar función.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

81

iv. Luego, cópiela hasta el final del período.

Compruebe los resultados obtenidos. v. Finalmente, sume las columnas de amortización, interés y pago. El cronograma de pagos tendrá la siguiente información:

Guarde el archivo con el nombre de Funciones Financieras.

CIBERTEC

CARRERAS PROFESIONALES

82

ACTIVIDADES 09: En un nuevo archivo, resuelva los siguientes ejercicios, luego grabarlo como ACTIVIDAD09 1.

Un padre de familia decide acumular un fondo de ahorro para su hijo y realiza depósitos de S/. 80 mensuales desde el primer mes de su nacimiento. ¿A cuánto ascenderá el fondo cuando su hijo cumpla los 5 años si el banco le paga un interés de 1.15% mensual? En una hoja nueva, escriba la siguiente información.

Utilizando la función financiera conveniente, calcule el fondo de ahorro que se obtendrá al finalizar el 5to. Año. Respuesta: 6858.30

2.

Escriba los siguientes datos y responda a la consulta del siguiente caso: ¿Cuánto deberá depositar mensualmente en el banco si desea obtener 15000 dólares luego de medio año si éste paga 20% como tasa de interés anual?

3.

Escriba los siguientes datos y responda a la consulta del siguiente caso: Calcule la cuota fija mensual que deberá pagar por un préstamo de 60000 dólares durante 30 meses con una tasa efectiva anual del 30%.

Caso 1 Monto o Valor futuro $

15,000.00

Plazo (meses)

6

TEA

22%

Tasa mensual Cuota mensual $

Caso 2 Préstamo $ TEA Plazo (meses)

60,000.00 30% 30

Tasa mensual Cuota mensual $ 4.

Escriba los siguientes datos y responda a la consulta del siguiente caso: Se desea obtener el monto que generaría una cuota vencida y una cuota adelantada, por un préstamo de 3500 soles durante 9 meses con una tasa de interés mensual del 3.60%.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

83

Caso 3 Préstamo S/. Tasa mensual Plazo

3,500.00 3.60% 9

Importe cuota vencida Importe cuota adelantada 5.

Utilizando las fórmulas y funciones financieras convenientes responda a la consulta del siguiente caso: A cuánto equivalen hoy 3 pagos futuros consecutivos y anuales de 1000 soles obtenidos a una tasa de interés anual del 4%

6.

Utilizando las fórmulas y funciones financieras convenientes responda a la consulta del siguiente caso: Hallar el valor actual de $20,000, pagaderos en 3 años a la tasa efectiva mensual de 1.2 %

7.

Elabore el programa de amortización (cronograma de pagos) para una deuda de 25000 dólares a una tasa de interés anual del 18%, mediante la modalidad de cuotas mensuales constantes durante 36 meses.

CIBERTEC

CARRERAS PROFESIONALES

84

R ESUMEN  Existen varias maneras de ingresar una función. Una de ellas, la más simple: consiste en escribir la función. Otra es utilizando el asistente para funciones. Dependiendo de la complejidad de lo que se desea obtener con la fórmula se elige el método para insertar la función.  Existen algunos operadores que reemplazan funciones. Por ejemplo, el símbolo ^ puede utilizarse en lugar de la función POTENCIA, mientras que el símbolo & puede usar en lugar de la función CONCATENAR.  Si se necesita anidar funciones, el método más práctico consiste en utilizar el asistente para funciones

.

 Para insertar funciones de bases de datos, se necesita escribir los criterios o condiciones que se tomarán en cuenta para utilizar la función.  Puede obtener información y más ejercicios sobre el tema de funciones en los siguientes vínculos:  http://excellentias.com/category/formula-funciones-excel/  http://www.taringa.net/posts/ciencia-educacion/9454221/FuncionesFinancieras-en-Excel-2010.html  http://www.aulaclic.es/excel2010/t_6_1.htm  http://www.youtube.com/watch?v=dHi_Qcip4O0&feature=BFa&list=PLD3EEDEB6 A1DB88F3&lf=results_video

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

85

UNIDAD DE APRENDIZAJE

3

LOGROS DE LA UNIDAD DE APRENDIZAJE 

Al término de la unidad, el alumno realiza análisis de datos utilizando herramientas de Excel como escenarios, buscar objetivo y solver.

TEMARIO 

Escenarios



Buscar Objetivo



Solver

ACTIVIDADES PROPUESTAS  

El alumno utiliza escenarios para efectuar análisis de datos. El alumno resuelve casos especiales con las herramientas Buscar Objetivo y Solver.

CIBERTEC

CARRERAS PROFESIONALES

86

3.1 HERRAMIENTAS DE ANÁLISIS Las herramientas de análisis están orientadas a personas que deseen sacar el máximo partido a Excel con funciones más específicas relacionadas a materias como la educación, la investigación, el negocio, la ingeniería, la estadística. En esta unidad, abordaremos las siguientes herramientas para efectuar análisis de datos:



Escenarios



Búsqueda de objetivo



Solver

Los escenarios determinan posibles resultados, estos pueden tener muchas variables, pero puede acomodar hasta 32 valores. La búsqueda de objetivo funciona de forma distinta a los escenarios, ya que toma un resultado y determina los valores de entrada posibles que producen ese resultado. Además de estas herramientas, puede instalar complementos que le ayudarán a realizar análisis de datos, como el complemento Solver. El complemento Solver es similar a la búsqueda de objetivo, pero puede acomodar más variables. También, puede crear previsiones mediante varios comandos y el controlador de relleno que están integrados en Excel. Para modelos más avanzados, puede usar el complemento Analysis Pack. En Excel 2010, las herramientas de Análisis Y si se encuentran en la ficha Datos, en el grupo Herramientas de datos y en el comando Análisis Y si.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

87

3.1.1. Escenarios Un escenario es un conjunto de valores que Microsoft Excel guarda y puede sustituir, de manera automática, en la hoja de cálculo. Son utilizados para prever el resultado de un modelo de hoja de cálculo. Puede crear y guardar diferentes grupos de valores en una hoja de cálculo y, a continuación, pasar a cualquiera de estos nuevos escenarios para ver distintos resultados. Los escenarios son parte de una serie de comandos a veces denominados herramientas de análisis. Al usar escenarios, usted realiza un análisis que Excel denomina Análisis Y si.

Una vez que tenga todos los escenarios que necesita, puede crear un informe resumen de escenario que incluya la información de todos los escenarios.

La información necesaria, para crear escenarios, se debe encontrar en una hoja de cálculo o en un libro, pero puede recopilar información de escenario de otros orígenes mediante el comando Combinar. Por ejemplo, suponga que está intentando crear un presupuesto para una compañía más grande. Puede recopilar escenarios de distintos departamentos como Nóminas, Producción, Marketing y Legal, ya que cada uno de estos orígenes usa información diferente al crear escenarios. Para comparar varios escenarios, puede crear un informe que los resuma en la misma página. El informe puede enumerar los escenarios en paralelo o presentarlos en un informe de tabla dinámica (informe de tabla dinámica: informe de Excel interactivo de tablas cruzadas que resume y analiza datos, como registros de una base de datos, de varios orígenes, incluidos los que son externos). Para desarrollar los temas de esta unidad, abra un libro nuevo y en la Hoja1 escriba la siguiente información:

CIBERTEC

CARRERAS PROFESIONALES

88

Tome en cuenta que, en la celda B8, debe ingresar la fórmula que calcule la diferencia entre los ingresos y el costo. Para ello, use la fórmula = B6 – B7

Crear un escenario A continuación, se creará un escenario llamado “Caso Optimista” que guarde los valores actuales del presupuesto de enero de 2009. i.

Seleccione las celdas B6:B7.

ii. En el grupo Herramientas de análisis de la ficha Datos, haga clic en el comando Análisis Y si.

iii. Haga clic en Administrador de escenarios. iv. Haga clic en el botón Agregar.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

89

v. En el cuadro Nombre del escenario, escriba Caso Optimista. Luego, haga clic en el botón Aceptar.

vi. Verifique que los valores a guardar son 25000 y 9300 respectivamente. Luego, haga clic en el botón Aceptar.

vii. Observe que el escenario se ha agregado exitosamente. Luego, haga clic en el botón Cerrar.

Ejercicio.

CIBERTEC

Cree el escenario “Caso Pesimista” que guarde los valores del ingreso y costo como 19000 y 13000 respectivamente.

CARRERAS PROFESIONALES

90

Mostrar un escenario i.

Seleccione la hoja donde creó el escenario.

ii. En el grupo Herramientas de análisis de la ficha Datos, haga clic en el comando Análisis Y si. iii. Haga clic en Administrador de escenarios. iv. Seleccione el escenario “Caso Pesimista” y haga clic en el botón Mostrar.

v. Haga clic en el botón Cerrar y observe el resultado.

Cuando cierra el cuadro de diálogo Administrador de escenarios, los valores del último escenario que mostró permanecen en la hoja de cálculo. Si guardó los valores iniciales como un escenario, podrá mostrar esos valores antes de cerrar el cuadro de diálogo Administrador de escenarios.

Crear un informe resumen de escenario i.

Seleccione la hoja donde creó el escenario.

ii. En el grupo Herramientas de análisis de la ficha Datos, haga clic en el comando Análisis Y si. iii. Haga clic en Administrador de escenarios.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

91

iv. Haga clic en el botón Resumen.

Observe que Excel, automáticamente, selecciona la celda B8 porque es la que contiene la fórmula.

v. Ahora, haga clic en el botón Aceptar. El resultado será el siguiente:

vi. Luego, proceda a modificar las celdas cambiantes y resultantes por un nombre que los represente adecuadamente:

Los informes de escenario no se vuelven a calcular automáticamente. Si cambia los valores de un escenario, esos cambios no aparecerán en ningún informe de resumen existente sino que aparecerán al crear un nuevo informe de resumen.

Crear un informe de tabla dinámica de escenario i.

Seleccione la hoja donde creó el escenario.

ii. En el grupo Herramientas de análisis de la ficha Datos, haga clic en el comando Análisis Y si.

CIBERTEC

CARRERAS PROFESIONALES

92

iii. Haga clic en Administrador de escenarios. iv. Haga clic en el botón Resumen. v. Seleccione la opción Informe de tabla dinámica de escenario. Luego, haga clic en el botón Aceptar.

vi. El resultado será el siguiente:

vii. Luego, proceda a modificar las celdas cambiantes y resultantes por un nombre que los represente adecuadamente:

Eliminar un escenario i.

Seleccione la hoja donde creó el escenario.

ii. En el grupo Herramientas de análisis de la ficha Datos, haga clic en el comando Análisis Y si. iii. Haga clic en Administrador de escenarios. iv. Seleccione el escenario a eliminar. Y luego, haga clic en el botón Eliminar.

Debe estar seguro cuando elimine un escenario, porque Excel no le pedirá que confirme la eliminación, simplemente eliminará el escenario elegido de manera inmediata.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

93

3.1.2. Buscar Objetivo Si sabe el resultado que desea de una fórmula, pero no está seguro de cuáles son los datos que requiere la fórmula para obtener dicho resultado, puede usar la herramienta Buscar Objetivo

Buscar objetivo es parte de una serie de comandos a veces denominados herramientas de análisis Y si.

La Búsqueda de objetivo funciona con un sólo valor de entrada de variable. Si desea determinar más de un valor de entrada, por ejemplo, la cantidad del préstamo y el importe de pago mensual del préstamo, deberá usar el complemento Solver.

Escriba en la Hoja2 la siguiente información:

Tome en cuenta que el valor de venta del producto es el resultado de la fórmula: Costo Producto A * (1+ Porcentaje de utilidad)

Ejemplo. Se desea incrementar el valor de venta del producto a 1500, ¿en cuánto variará el porcentaje de Utilidad? i.

Seleccione la celda B6.

ii. En el grupo Herramientas de análisis de la ficha Datos, haga clic en el comando Análisis Y si. iii. Luego, elija la opción Buscar Objetivo.

CIBERTEC

CARRERAS PROFESIONALES

94

iv. Escriba la siguiente información en la ventana Buscar objetivo. Luego, haga clic en el botón Aceptar.

v. Si la herramienta Buscar objetivo encontró una solución para la solicitud, entonces mostrará la siguiente ventana. Luego, haga clic en Aceptar.

Los resultados serán los siguientes:

Observe que el porcentaje de utilidad ha variado de 20% a 25% después de emplear la función Buscar objetivo.

Ejercicio1. La competencia vende a 1150 el mismo producto, ¿cuánto tendría que disminuir el porcentaje de utilidad para poder venderlo a ese mismo precio?

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

95

Los resultados que obtendrá serán los siguientes:

¿Considera que es conveniente vender el producto a 1150?. Sustente su respuesta.

Ejercicio2. Se desea vender a 1150 el mismo producto, ¿cuánto tendría que variar el costo del producto?. Los resultados que obtendrá serán los siguientes:

CIBERTEC

CARRERAS PROFESIONALES

96

3.1.3. Solver Con Solver, puede buscarse el valor óptimo para una fórmula, trabaja con el grupo de celdas que estén relacionadas, directa o indirectamente, con la fórmula de la celda objetivo. Pueden aplicarse restricciones, condiciones o limitaciones aplicadas a un problema. Solver forma parte de una serie de comandos denominados herramientas de análisis.

Solver ajusta los valores de las celdas variables que se especifiquen, denominadas celdas cambiantes, para obtener el resultado especificado en la fórmula de la celda objetivo.

Si no tiene acceso a esta herramienta, deberá cargar el complemento Solver. Para ello, realice el siguiente procedimiento: i. Seleccione la ficha Archivo, haga clic en Opciones de Excel y, a continuación, haga clic en la categoría Complementos. ii. En el cuadro Administrar, haga clic en Complementos de Excel y, a continuación, en Ir. iii. En el cuadro Complementos disponibles, active la casilla de verificación Complemento Solver y, a continuación, haga clic en Aceptar.

Ejemplo. Añada los datos faltantes tal como se muestra.

Complete las celdas B8 y B9 con las siguientes fórmulas respectivamente: = B4*1.18 =B4*(B5+1)*1.18

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

97

Los resultados serán los siguientes:

A continuación, se utilizará la herramienta Solver con la información del cuadro anterior para responder la siguiente pregunta. Ejemplo1. ¿Cuál será el costo del producto, si se desea que el valor de venta (incluido IGV) sea 2000 y el porcentaje utilidad sea cómo mínimo 20%? i.

Seleccione la celda B9.

ii. En el grupo Análisis de la ficha Datos, haga clic en el comando Solver.

iii. En el cuadro Celda objetivo, seleccione la celda B9 y luego escriba 2000 como valor de la celda objetivo.

En el cuadro Celda objetivo, escriba una referencia de celda o un nombre para la celda objetivo. La celda objetivo debe contener una fórmula. Si desea que la celda objetivo tenga un valor determinado, haga clic en Valor de y, a continuación, escriba el valor en el cuadro. Si desea que el valor de la celda objetivo sea el valor máximo posible, haga clic en Máx. Si desea que el valor de la celda objetivo sea el valor mínimo posible, haga clic en Mín.

CIBERTEC

CARRERAS PROFESIONALES

98

iv. Seleccione la celda B4 porque según el enunciado se desea obtener el nuevo costo del producto.

En el cuadro Cambiando celdas, escriba un nombre o referencia para cada celda ajustable. Separe con comas las referencias no adyacentes. Las celdas ajustables deben estar directa o indirectamente relacionadas con las celdas objetivo. Pueden especificarse 200 celdas ajustables como máximo.

v. En el cuadro Sujeto a restricciones, haga clic en el botón Agregar y escriba la siguiente información. Luego haga clic en el botón Aceptar.

En el cuadro Sujeto a las restricciones, especifique todas las restricciones o condiciones que desee aplicar. Los tipos de restricciones son =, int o bin. Si hace clic en int, en el cuadro Restricción aparecerá entero. Si hace clic en bin, en el cuadro restricción aparecerá Binario. Sólo pueden aplicarse las relaciones ent y bin en las restricciones en celdas cambiantes.

vi. Al aceptar, la ventana de parámetros de solver, se verá de la siguiente manera. Luego, haga clic en Resolver.

vii. Finalmente, haga clic en Aceptar y observe que la herramienta encontró una solución para el enunciado propuesto.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

99

Puede guardar los valores trabajados bajo el nombre de un escenario, tal como lo vimos en el tema anterior.

Observe los resultados obtenidos.

Ejemplo2. ¿Cuánto será el % de utilidad, si se desea que el valor de venta (incluido IGV) sea el mínimo y el porcentaje utilidad sea cómo mínimo 35%? Antes de realizar el siguiente procedimiento, escriba el valor de 1500 en la celda B4 para restaurar los valores originales. i.

Seleccione la celda B9.

ii. En el grupo Análisis de la ficha Datos, haga clic en el comando Solver. iii. En el cuadro Celda objetivo, seleccione la celda B9 y, luego, en el grupo Valor de la celda objetivo, active Mínimo. iv. Seleccione la celda B5 porque según el enunciado se desea obtener el nuevo % de utilidad. v. En el cuadro Sujeto a restricciones, haga clic en el botón Agregar y escriba la siguiente información. Luego, haga clic en el botón Aceptar.

CIBERTEC

CARRERAS PROFESIONALES

100

vi. Al aceptar, la ventana de parámetros de solver, se verá de la siguiente manera. Luego, haga clic en Resolver.

vii. Finalmente, haga clic en el Aceptar y observe que la herramienta encontró una solución para el enunciado propuesto. Observe los resultados obtenidos.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

101

ACTIVIDADES 10 En un nuevo archivo, resuelva los siguientes ejercicios; luego grábelo como ACTIVIDAD10 Ejercicio 1 En la Hoja1, escriba la siguiente información:

Tome en cuenta las siguientes fórmulas: A = Ventas Netas - Costo de Ventas B = G. Ventas + G. Administrativos + G. Financieros C = Utilidad Bruta - Gastos de Operación D = Ingresos Varios + Intereses Ganados - G. Extraordinarios E = Utilidad de Operación + (D) F = Utilidad antes del Impuesto a la Renta*30% D = Utilidad antes del Impuesto a la Renta - Impuesto a la Renta 1. Cree un escenario llamado “Caso Mejor Imposible” que guarde los valores originales de las ventas netas y el costo de ventas. 2. Cree un escenario llamado “Caso No deseado” que guarde en ventas netas y costo de ventas los valores de 300000 y 160000. 3. Cree un resumen tipo informe para el escenario anterior. 4. Localice a las celdas dependientes de las ventas netas y el costo de ventas.

CIBERTEC

CARRERAS PROFESIONALES

102

Ejercicio 2 CURSO DE INGLESde notas del curso Inglés: En la Hoja2, escriba la siguiente información

Cuadro de Notas Bimestre 1 Bimestre 2 Bimestre 3 Bimestre 4

15.00 12.00 15.00

PROM FINAL 1. Calcule el promedio final teniendo en cuenta que es la media aritmética de las notas de los 4 bimestres.

2. ¿Qué nota deberá obtener el alumno en el cuarto bimestre para poder aprobar el curso con la nota mínima de 11? Respuesta.- ___________.

3. ¿Cuál será el mínimo promedio final teniendo en cuenta que la nota del bimestre 4 sea cómo mínimo 15? Respuesta.- ___________.

4. Localice a las celdas precedentes del promedio final. Indique cómo lo hizo.

Ejercicio 3 En la Hoja3, escriba la siguiente información de notas del curso de Software de Negocios II:

1. Calcule el promedio final teniendo en cuenta la fórmula de evaluación del curso en el ciclo actual.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

103

2. ¿Qué nota deberá obtener el alumno en el examen final para poder aprobar el curso con la nota mínima de 13?. Respuesta.- ___________.

3. ¿Si se desea obtener un promedio final de 15, cual será la nota del examen final teniendo en cuenta que debe ser superior a 14? Respuesta.- ___________.

CIBERTEC

CARRERAS PROFESIONALES

104

R ESUMEN  Los escenarios, con frecuencia, son utilizados para realizar análisis de Estados de resultados, financieros o cualquier otro análisis que requiera una comparación de valores.  Puede comparar los valores guardados en el escenario a través de la creación de un informe tipo resumen o tipo tabla dinámica. En cualquiera de los casos al obtener el informe, este debe ser editado con los nombres de las celdas o valores guardados y las celdas resultantes.  Para utilizar la herramienta Buscar objetivo, debe tomar en cuenta que la celda por definir con un nuevo valor debe contener una fórmula y que la celda que deberá modificar o cambiar debe contener un valor. En caso no se cumplan estas condiciones, la herramienta Buscar objetivo no podrá aplicarse.  Puede obtener información y más ejercicios sobre el tema de herramientas de análisis en los siguientes vínculos:  http://www.emagister.com/cursos-gratis/curso-gratis-herramientas-analisisdatos-kwes-9390.htm  http://www.youtube.com/watch?v=spJFTuWSsR0&feature=results_video&playnext=1&l ist=PLD3EEDEB6A1DB88F3  http://exceltotal.com/buscar-objetivo/  http://office.microsoft.com/es-hn/excel-help/definir-y-resolver-un-problema-consolver-HP010342416.aspx

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

105

UNIDAD DE APRENDIZAJE

4

LOGROS DE LA UNIDAD DE APRENDIZAJE 

Al término de la unidad, el alumno diseña formularios insertando controles de formulario como cuadro combinado, cuadro de lista, cuadro de opciones, botón de número, casilla de verificación y botón de comando. Asimismo, automatiza tareas repetitivas usando macros.

TEMARIO 

Formularios



Macros

ACTIVIDADES PROPUESTAS  

Los alumnos diseñan formularios para utilizarse en el ingreso de datos. Los alumnos graban macros para automatizar tareas repetitivas. Asimismo, asignan macros a botones de formularios para facilitar su ejecución.

CIBERTEC

CARRERAS PROFESIONALES

106

4.1 FORMULARIOS Un formulario, ya sea impreso o en línea, es un documento diseñado con formato y estructura estándar que facilita la captura, la organización y la edición de la información. Si necesita un formulario de introducción de datos complejo o especial, puede crear una hoja de cálculo o una plantilla para utilizarlos como formulario y, después, personalizar el formulario de la hoja de cálculo según sus necesidades. Para crear un formulario en Microsoft Excel 2010, antes de poder insertar controles, deberemos mostrar la ficha Programador que por defecto no es visible. Para ello, tenemos que hacer clic en la ficha Archivo y seleccionar Opciones; a continuación, en el cuadro de diálogo seleccionamos Personalizar cinta de opciones y, en la parte derecha, marcamos la casilla Programador; finalmente hacemos clic en Aceptar.

Haciendo clic en la ficha Programador, veremos:

Para insertar un control, hemos de hacer clic en el comando Insertar de la ficha Programador y seleccionar el deseado; para después hacer clic en la Hoja de cálculo. Para usar los controles ActiveX, son necesarios conocimientos de programación en Visual Basic for Applications (VBA):

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

107

4.1.1. Controles de formulario Dentro de una Hoja de Excel 2010, podemos usar controles de formulario que nos permitan una gestión más sencilla y organizada de nuestros datos. i.

Seleccione la ficha Programador y el grupo Controles.

ii. Haga clic en el botón Insertar.

En el cuadro anterior, se aprecian los controles de formulario y los controles Active X disponibles en la versión de Excel 2010.

Los controles de formulario observados son los siguientes:

Botón, ejecuta una macro cuando se hace clic en él. Cuadro combinado, es un cuadro de lista desplegable. El elemento que se seleccione en el cuadro de lista aparecerá en el cuadro de texto. Casilla de verificación, activa o desactiva una opción. Puede activarse más de una casilla de verificación a la vez en una hoja o en un grupo. Control de número, facilita el incremento o disminución de un valor, por ejemplo, el incremento numérico de una hora o fecha. Cuadro de lista, muestra una lista de elementos.

CIBERTEC

CARRERAS PROFESIONALES

108

Botón de opción: Selecciona una de las opciones de un grupo contenido en un cuadro de grupo. Utilice los botones de opción para permitir sólo una de varias posibilidades. Cuadro de grupo, agrupa los controles relacionados, como los botones de opción o las casillas de verificación. Etiqueta, contiene texto que identifica el propósito de una celda o un cuadro de texto o muestra texto descriptivo, por ejemplo: títulos, leyendas o instrucciones breves. Barra de desplazamiento, sirve para desplazarse a través de un rango de valores cuando se hace clic en las flechas de desplazamiento o cuando se arrastra un cuadro de desplazamiento.

Los últimos 3 botones llamados campo de texto, cuadro combinado de lista y cuadro combinado desplegable no están habilitados en esta versión.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

109

4.1.2. Diseño de formularios En un archivo nuevo, escriba la siguiente información en la Hoja1:

Escriba la siguiente información en la Hoja2, ya que se utilizarán para llenar la información del formulario. Luego, defina el rango A4:D13 con el nombre de Datos. También, defina el rango B4:B13 con el nombre Producto.

Insertar Cuadro combinado Antes de crear el cuadro combinado, seleccione la Hoja1.

i.

Haga clic en el control Cuadro combinado

ii. Dibuje un rectángulo sobre la celda B5. iii. Cuando termine de hacer el rectángulo, aparecerá en la ventana el siguiente control:

CIBERTEC

CARRERAS PROFESIONALES

110

iv. Haga clic derecho sobre el cuadro combinado.

v. En la ventana Formato de control, escriba la siguiente información exactamente igual a la que se muestra en el modelo. Luego, haga clic en el botón Aceptar.

vi. Seleccione cualquier celda. Luego, haga clic sobre el cuadro combinado y observe.

vii. Seleccione el producto Windows Vista.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

111

Si observa el contenido de la celda B5, encontrará el valor de 4, porque el producto elegido “Office 2003” ocupa la cuarta posición de la lista de productos.

En las celdas indicadas, ingrese las siguientes fórmulas: B7

=CONSULTAV(B5;Datos;3;0)

B8

=CONSULTAV(B5;Datos;4;0)

B10

=B8*B9

B13

=B10+B11

La siguiente secuencia que aplicará para crear un cuadro combinado será la misma para insertar un cuadro de lista.

Insertar control de número i.

Haga clic en el control de número

ii. Dibuje un pequeño rectángulo sobre la celda C9. Observe el modelo siguiente:

iii. Haga clic derecho sobre el cuadro combinado y elija Formato de control. iv. En la ventana Formato de control, escriba la siguiente información como se muestra en el modelo. Luego, haga clic en el botón Aceptar.

CIBERTEC

CARRERAS PROFESIONALES

112

v. Seleccione cualquier celda y haga clic sobre el control de número para aumentar o disminuir la cantidad. Insertar casilla de verificación

i.

Haga clic en la casilla de verificación

ii. Dibuje un pequeño rectángulo sobre la celda D11. Observe el modelo siguiente:

iii. Cambie el nombre de la casilla por “Con IGV”. iv. Haga clic derecho sobre la casilla de verificación y elija Formato de control. v. En la ventana Formato de control, escriba la siguiente información, exactamente, como se muestra en el modelo. Luego, haga clic en el botón Aceptar.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

113

vi. Observe que de inmediato, se escribe en la celda E11 el contenido “VERDADERO”.

Si desea, puede cambiar el color de fuente de la celda E11 por el color blanco para ocultar su visibilidad.

Insertar cuadro de grupo

i.

Haga clic en el botón Cuadro de grupo

ii. Dibuje un rectángulo en la misma posición según el siguiente modelo y luego escriba “Total en Soles”.

Insertar botón de opción

i.

Haga clic en el botón de opción

ii. Dibuje 2 rectángulos dentro del cuadro de grupo y luego, escriba los nombres “Sí” y “No”.

iii. Haga clic derecho sobre el botón “Sí” y elija Formato de control. iv. En la ventana Formato de control, escriba la siguiente información, exactamente, como se muestra en el modelo. Luego, haga clic en el botón Aceptar.

CIBERTEC

CARRERAS PROFESIONALES

114

v. Observe que de inmediato, en la celda D16 se escribe el valor de “1”. Para que los controles puedan afectar los resultados faltantes, escriba las siguientes fórmulas: B11

=SI(E11;B10*19%;0)

B15

=SI(D16=1;B13*E7;0)

A las celdas D16 y E11, cambie el color de fuente por blanco para que no se visualicen los valores. Observe el resultado final.

Guarde el archivo como Formularios.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

115

ACTIVIDADES 11 En un nuevo archivo, resuelva los siguientes ejercicios, luego, grábelo como ACTIVIDAD11 Ejercicio 1 1. En la Hoja1, escriba la siguiente lista de datos:

2. En la hoja 2 y utilizando la información de la hoja anterior, cree el siguiente formulario:

 El cuadro combinado mostrará los códigos de los empleados.  Al elegir un código de empleado, deberá mostrar los campos asociados.  Al activar el botón de opción AFP o SNP, deberá calcular el Sueldo a Pagar S/. teniendo en cuenta respectivamente.

los

porcentajes

de

descuento

11,8%

o

9.8%

Ejercicio 2 1. En la hoja3, diseñe una factura utilizando los controles de la barra de formularios. Tome en cuenta que deberá crear la base de datos con la información de la factura.

CIBERTEC

CARRERAS PROFESIONALES

116

2. Si en el diseño, ha utilizado alguno de los siguientes controles de formulario, llene la siguiente tabla: Elemento de formulario

Dato

Cuadro combinado Cuadro de lista Cuadro de grupo Casilla de verificación Botón de opción Botón de comando

3. Si en el formulario ha realizado algún cálculo, llene el siguiente cuadro: Elemento calculado

Fórmulas utilizadas

Ejercicio 3 1. En la hoja 04, diseñe y cree un formulario para el recibo de luz. Tome en cuenta que deberá crear la base de datos con la información del recibo.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

117

4.2 MACROS Cuando las tareas con las hojas de cálculo se vuelven repetitivas o se recurren a los mismos comandos, entonces lo conveniente es automatizar estas tareas bajo una sola acción. Esta se denomina Macro. Excel proporciona las macros que son herramientas sencillas, pero en suma, efectivas para reducir el tiempo y el esfuerzo. Por lo tanto, una macro en Excel es un conjunto de expresiones o instrucciones escritas en un lenguaje de programación llamado Visual Basic. Una vez creadas puede asignarlas a botones de comando de las barras de herramientas, de esa forma su ejecución es mucho más directa.

El propósito de una macro es automatizar tareas de uso frecuente. Aunque algunas macros son, simplemente, la grabación de las pulsaciones de tecla o los clic del mouse, los programadores escriben macros de VBA (Visual Basic para Aplicaciones) más eficaces que utilizan código que puede ejecutar muchos comandos en el equipo. Por esta razón, las macros de VBA suponen un riesgo para la seguridad. Un pirata informático puede pasar una macro malintencionada en un documento que, si se abre, permite que se ejecute la macro y puede propagar un virus en el equipo.

Configuración de seguridad de macros y sus efectos En Microsoft Excel, se puede cambiar la configuración de seguridad de las macros para especificar qué macros se ejecutarán, y en qué circunstancias, al abrir un libro. Por ejemplo, se puede permitir la ejecución de macros si están firmadas digitalmente por un desarrollador de confianza (una persona que escribe código de programación). En la siguiente lista, se resumen las distintas configuraciones de seguridad de macros. En todas las configuraciones, si hay instalado un software antivirus que funciona con 2010 Microsoft Office System y si el libro contiene macros, el libro se examina para detectar virus conocidos antes de abrirlo.

CIBERTEC

CARRERAS PROFESIONALES

118

Las opciones son las siguientes:



Deshabilitar todas las macros sin notificación. Haga clic en esta opción si no confía en las macros. Se deshabilitan todas las macros de los documentos y todas las alertas de seguridad que informan de que las macros están deshabilitadas.



Deshabilitar todas las macros con notificación. Ésta es la configuración predeterminada. Haga clic en esta opción si desea deshabilitar las macros, pero desea recibir alertas de seguridad si existen macros. De esta manera, puede elegir cuándo habilitar las macros caso por caso.



Deshabilitar todas las macros excepto las firmadas de forma digital. Esta configuración es la misma que la de la opción Deshabilitar todas las macros con notificación, con la excepción de que, si la macro está firmada digitalmente, por un editor de confianza, podrá ejecutarse. Si el editor no es de confianza, se le notifica este hecho para que pueda elegir si desea habilitar las macros firmadas o confiar en el editor. Todas las macros no firmadas digitalmente se deshabilitan sin ninguna notificación.



Habilitar todas las macros Haga clic en esta opción para permitir la ejecución de todas las macros. No se recomienda usar esta opción, ya que con esta configuración el equipo es vulnerable a código, posiblemente, malintencionado.



Confiar en el acceso al modelo de objeto de proyectos de VBA. Esta configuración está dirigida a los programadores y se usa para bloquear deliberadamente o permitir el acceso mediante programación al modelo de objetos de VBA de cualquier cliente de automatización. Es decir, ofrece una opción de seguridad para el código escrito para automatizar un programa de Office y manipular mediante programación el entorno y el modelo de objetos de Microsoft Visual Basic para Aplicaciones (VBA).

Cambiar la configuración de seguridad de macros La configuración de seguridad de macros se puede cambiar en el Centro de confianza a menos que un administrador del sistema de la organización haya cambiado la configuración predeterminada para impedir que se modifique la configuración.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

i.

119

En la ficha Programador, en el grupo Código, haga clic en Seguridad de macros.

Se sugiere que si la ficha Programador no se muestra, haga clic en el botón de Microsoft Office y elija Opciones de Excel. A continuación, en la categoría Más frecuentes de Opciones principales para trabajar con Excel, haga clic en Mostrar ficha Programador en la cinta de opciones.

ii. En la categoría Configuración de macros, en Configuración de macros, haga clic en la opción que desee. En este ejemplo, active Deshabilitar todas las macros con notificación. Luego, haga clic en el botón Aceptar. Tome en cuenta que cualquier cambio que realice en la categoría Configuración de macros en Excel, sólo se aplica a Excel y no afecta a los demás programas de Microsoft Office.

CIBERTEC

CARRERAS PROFESIONALES

120

4.2.1. Grabación de macros Cuando graba una macro, la grabadora de macros graba todos los pasos necesarios para completar las acciones que desea que realice la macro. En los pasos grabados, no se incluye el desplazamiento por la cinta de opciones.

Ejemplo. Grabe una macro llamada Formato que permita aplicar el estilo de celda Neutral, negrita, tamaño 14, color azul y borde grueso. Antes de empezar, abra un archivo nuevo. Guarde el archivo con el nombre de Macros i.

Haga clic en ficha Programador, en el grupo Código. Luego, haga clic Grabar nueva macro.

ii. Escriba el nombre de la macro Formato y verifique que la macro se estará guardando en Este libro. Si desea, en el cuadro de Descripción, escriba su nombre. Luego, haga clic en el botón Aceptar.

Es importante saber que el nombre de la macro no debe contener espacios en blanco ni caracteres especiales como -, *, entre otros. Al elegir donde guardar la macro, podrá seleccionar la opción “Este libro” o la opción “Libro de Macros Personal”. Esta última opción le permitirá tener disponible la macro en cualquier archivo de Excel.

En este momento, todo comando que ejecute en Excel se grabará en la macro; por ello, debe tener mucho cuidado con los comandos que utilice. iii. Aplique el estilo de celda Neutral, negrita, tamaño 14, color azul y borde grueso. iv. Haga clic en el botón Detener de la barra de estado.

.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

121

En el momento de guardar, Excel notificará sobre la existencia de macros en el archivo. Para evitar cualquier inconveniente, seleccione en el cuadro Guardar como tipo Libro de Excel habilitado para macros.

Abrir un archivo con macros Al abrir un archivo que contiene macros, Excel las deshabilitará, automáticamente, permitiéndole poder activar dichas macros. Para ello, haga clic en el botón Opciones.

Luego, active la opción Habilitar este contenido.

Grabar macros personales Este tipo de macro estará disponible para cualquier archivo Excel guardado en un libro especial llamado Personal (Libro de Macros Personales). Características del libro Personal:



Tiene el nombre de Personal.xls.



Contiene código VBA para las macros, funciones etc.



Se carga de manera automática al iniciar Excel.



Tiene el atributo Hidden (escondido).

CIBERTEC

CARRERAS PROFESIONALES

122

Ejercicio 1. Grabe una macro que permita crear el siguiente cuadro en cualquier hoja de cálculo y en cualquier posición:

Para resolver este caso, debe grabarse la macro activando la opción Referencia relativa, porque lo que se necesita es que el cuadro se pueda escribir a partir de la celda que elija el usuario en el momento de la ejecución de la macro. Si no se activara esta opción, la macro escribirá el cuadro siempre a partir de la celda A1. i.

Seleccione la Hoja2 del archivo Macro. Luego, haga clic en la celda A1.

ii. Haga clic en ficha Programador, en el grupo Código. Luego, haga clic Grabar nueva macro. iii. Escriba el nombre de la macro NotaPedido y guarde la macro en el Libro de macros personal. Si desea, en el cuadro de Descripción escriba su nombre. Luego, haga clic en el botón Aceptar.

iv. Active la opción Usar referencias relativas.

v. Escriba el cuadro de la nota de pedido considerando los formatos mostrados. vi. Haga clic en el botón Detener.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

123

Ejecutar macros El único modo de comprobar si una macro fue grabada de manera exitosa, es ejecutándola. Véase el siguiente ejemplo: i.

Seleccione la Hoja2 del archivo Macros.

ii. Haga clic en ficha Programador, en el grupo Código. Luego, haga clic en el comando Macros.

iii. Seleccione la macro Formato y luego, haga clic en el botón Ejecutar.

iv. Guarde y Macros.

CIBERTEC

cierre

el

archivo

CARRERAS PROFESIONALES

124

4.2.2. Añadir botón de comando Un botón de comando se usa en un formulario para iniciar una acción o un conjunto de acciones. Para que un botón de comando realice una acción, se escribe una macro o un procedimiento de evento que se asocia a la propiedad. Asimismo, se puede incrustar una macro directamente en la propiedad. Además de ejecutar una macro con el botón Macros, también puede utilizar botones de formulario para tal fin.

Ejercicio. Seleccione la Hoja3 del archivo Macros. i.

Escriba la siguiente información en la celda B2:

ii. Haga clic en el botón de formulario iii. Dibuje un pequeño rectángulo según la siguiente muestra.

iv. Seleccione la macro Formato y luego, haga clic en el botón Aceptar.

v. Cambie el nombre del botón por Formato y luego, haga clic en cualquier celda de la hoja.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

125

vi. Para ejecutar la macro, simplemente, haga clic en el botón Formato; por ejemplo, seleccione la celda B2. vii. Haga clic en el botón Formato y observe el resultado:

CIBERTEC

CARRERAS PROFESIONALES

126

ACTIVIDADES 12 En un nuevo archivo, resuelva los siguientes ejercicios, luego grábelo como ACTIVIDAD12 Ejercicio 1 Cree una macro en el libro Personal que escriba la siguiente información:

Guarde la macro con el nombre de Factura. Ejercicio 2 Grabe una macro en el libro de macros personal llamada Eliminación que permita borrar el contenido y el formato de un rango de celdas. Ejercicio 3 Cree una macro en el libro Personal que escriba la siguiente información:

Guarde la macro con el nombre de Fondo_Ahorro.

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

127

R ESUMEN  Los formularios son utilizados para facilitar el ingreso de datos a una hoja de cálculo. Puede obtener información y más ejercicios en el siguiente vínculo:  http://www.aprender-informatica.com/excel-2010/microsoft-excel-2010formularios.php  http://office.microsoft.com/es-hn/excel-help/CH010369012.aspx  http://www.youtube.com/watch?v=x2-Rb-2dkPM  Las macros son, extremadamente, poderosas cuando se trata de automatizar tareas repetitivas. Evalúe de forma conveniente en dónde debe guardar la macro, ya que de eso depende la amplitud de su ejecución. Puede obtener información y más ejercicios en el siguiente vínculo:  http://office.microsoft.com/es-hn/excel-help/ejecutar-una-macroHP010342865.aspx  http://www.aulaclic.es/excel2010/t_18_2.htm

CIBERTEC

CARRERAS PROFESIONALES

128

CARRERAS PROFESIONALES

CIBERTEC