ms excel 2007-avanzado

2

CARRERAS PROFESIONALES

CIBERTEC

SOFTWARE DE NEGOCIOS II

3

Pgina

NDICEPresentacin Red de contenidos 5 6

Funciones2.1 Tema 6 2.1.1. 2.1.2. 2.2 Tema 7 2.2.1. 2.2.2. 2.3 Tema 8 2.3.1. 2.3.2. 2.4. Tema 9 2.4.1. 2.4.2. : : : : Funciones matemticas y de texto Funciones matemticas Funciones de texto Funciones estadsticas y de bases de datos Funciones estadsticas Funciones de Bases de Datos Funciones lgicas y de bsqueda Funciones lgicas Funciones de bsqueda Funciones financieras Funciones de pago Cronograma de pagos 70 77 52 59 28 40 88 20

Herramientas de anlisis3.1 Tema 10 3.1.1. 3.1.2. 3.1.3. : Herramientas de anlisis Escenarios Buscar objetivo Solver 87 93 96

Formularios y macros4.1 Tema 11 4.1.1. : Formularios Controles de formularios 107

CIBERTEC


4

4.1.2. 4.2 Tema 12 4.2.1. 4.2.2. :

Diseo de formularios Macros Grabacin de macros Aadir botn de comando

109

120 124


CIBERTEC


5

P RESENTACINSoftware de Negocios IIes un curso que pertenece a la lnea de

software de aplicaciones y se dicta en las carreras de Administracin, Administracin de Negocios Internacionales, Contabilidad y Marketing. El curso brinda a los alumnos la herramienta de software Microsoft Excel para la creacin de aplicaciones informticas ms utilizadas en el medio laboral.

El manual para el curso ha sido diseado 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 sesin, que le permitirn reforzar lo aprendido en la clase.

El curso es eminentemente prctico. Se inicia con la creacin y uso de bases de datos; luego, con la aplicacin de funciones. Se emplean herramientas de anlisis para toma de decisiones. Finalmente se utilizan elementos de formularios para crear aplicaciones en Excel y automatizacin de procesos a travs de macros.

CIBERTEC


6

R ED DE CONTENIDOSCreacin, validacin y ordenamiento de datos Filtros automticos y avanzados Base de datos Subtotales y esquemas de registros Consolidacin, tablas y grficos dinmicos Acceso a datos externos Matemticas Texto Estadsticas Funciones Base de datos Lgicas Bsqueda Financieras Escenarios Herramientas de anlisis Buscar objetivo Solver Insertar elementos de formularios Formularios y macros Crear macros


Software de Negocios II

CIBERTEC


7

UNIDAD DE APRENDIZAJE

2

LOGROS DE LA UNIDAD DE APRENDIZAJE Al trmino de la unidad, el alumno escribe frmulas con la insercin de funciones, de manera individual o anidada para elaborar cuadros, reportes y estadsticas.

TEMARIO Funciones matemticas Funciones de texto Funciones estadsticas Funciones de bases de datos Funciones lgicas Funciones de bsqueda Funciones financieras

ACTIVIDADES PROPUESTAS Los alumnos insertan funciones de las diferentes categoras, de tal modo que se utilicen para crear cuadros, reportes, estadsticas con informacin confiable. Los alumnos insertan frmulas con funciones anidadas.

CIBERTEC


8

FUNCIONESUna funcin es una frmula predefinida por Excel que opera sobre uno o ms valores (argumentos) en un orden determinado (estructura). El resultado se mostrar en la celda donde se introdujo la formula

La sintaxis de cualquier funcin es la siguiente:

NOMBRE_FUNCIN (argumento 1; argumento 2;...;argumento n)

Para escribir una funcin, debe tomar en cuenta lo siguiente:

Una frmula o funcin debe empezar con el signo =. Los argumentos o valores de entrada van siempre entre parntesis. No deje espacios antes o despus de cada parntesis. Los argumentos pueden ser valores constantes (nmero o texto), frmulas o funciones. Los argumentos deben separarse por un punto y coma (;). En ocasiones, el separador de argumentos es la coma. Ello depender de la configuracin regional de la computadora.Existen casos en los que, para obtener una respuesta, se deben utilizar varias funciones en una sola frmula. A este tipo de funciones se les denomina Funciones Anidadas. Si utiliza funciones anidadas debe saber que slo puede anidar hasta un mximo de siete niveles.

Insertar funciones manualmente Podemos insertar una funcin escribindola directamente en la celda o en la barra de frmulas, y han de ir precedidas del signo = (igual), del mismo modo que las frmulas. Ejemplo: SUMA(), es una funcin de Microsoft Excel 2010


CIBERTEC


9

Insertar funciones desde el asistente Podemos insertar funciones usando un asistente incorporado en Microsoft Excel 2010, para ello, despus de habernos situado en la celda deseada, haremos clic en el icono Insertar funcin de la cinta de opciones correspondiente a la ficha Frmulas

En el siguiente cuadro de dilogo, escribiremos el nombre de una funcin o parte de ella en Buscar una funcin y pulsaremos la tecla Enter, o bien seleccionaremos una categora en el cuadro de lista O seleccionar una categora y seleccionaremos la funcin deseada en Seleccionar una funcin. Una vez elegida una, clic en Aceptar.

A continuacin, en el cuadro de dilogo 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 parmetros a usar, haremos clic en Aceptar para insertar la funcin.

CIBERTEC


10

Insertar funciones desde la Biblioteca de funciones: Podemos acceder rpidamente a las funciones usando los iconos del grupo Biblioteca de funciones, correspondiente a la ficha Frmulas:

En dicha cinta de opciones disponemos de varias opciones ms para el tratamiento de funciones:

Novedades en el Excel 2010 Excel 2010 incorpora ahora un conjunto de funciones estadsticas ms exactas, entre otras funciones. Tambin 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 ms exactos para las distribuciones Chi cuadrado y Beta. Se ha cambiado el nombre de ciertas funciones estadsticas para que sean ms coherentes con las definiciones de funciones de la comunidad cientfica y con otros nombres de funciones en Excel. Los nombres de funciones nuevos tambin describen con mayor exactitud su funcionalidad. Los libros creados con versiones anteriores de Excel continuarn funcionando a pesar de estos cambios en los nombres debido a que las funciones originales an existen en la categora Compatibilidad.


CIBERTEC


11

2.1 FUNCIONES matemticas y de texto2.1.1. Funciones matemticasPuede insertar cualquier funcin matemtica desde la ficha Frmulas, el grupo Biblioteca de funciones y el comando Matemticas y trigonomtricas.

FUNCIN: POTENCIA

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

Donde Nmero es el nmero base y puede ser cualquier nmero real. Potencia es el exponente al que desea elevar el nmero base.Se puede utilizar el operador ^ en lugar de la funcin POTENCIA para indicar a qu potencia se eleva el nmero base, por ejemplo 3^2. Si no encuentra el operador en el teclado, utilice la siguiente combinacin ALT 94.

Ejemplo: Para comprender cmo funciona la POTENCIA, escriba la siguiente informacin en una hoja de clculo en blanco.

CIBERTEC


12

i.

Escriba la siguiente frmula en C4.

ii. Presione la tecla ENTER y observe el resultado: 9 iii. Escriba las frmulas para completar la expresin X2.

Ejercicio: Obtenga los resultados de las expresiones restantes.

X3 4 5

Y2 7 9

X29 16 25

Y24 49 81

3

X1.44 1.59 1.71

4

Y1.19 1.63 1.73

FUNCIN: RCUAD

Devuelve el resultado de calcular la raz cuadrada a un nmero. Sintaxis: RCUAD(nmero)

Donde Nmero es el nmero base y puede ser cualquier nmero real.

Si nmero es negativo, RCUAD devuelve el valor de error #NUM!

Ejemplo: Para comprender cmo funciona la RCUAD, escriba la siguiente informacin en una hoja de clculo en blanco.


CIBERTEC


13

i.

Escriba la siguiente frmula en C4: =RCUAD(A4)

ii. Presione la tecla ENTER y observe el resultado: 5 iii. Hacer un arrastre para obtener los dems 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

FUNCIN 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 nmero, expresin 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 slo 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 funcin SUMAR.SI.CONJUNTO, que suma las celdas de un rango que cumplen varios criterios.

CIBERTEC


14

Ejemplo: Copie la informacin anterior a una hoja nueva y agregue la siguiente informacin:

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 posicin C19. de la barra de frmulas.

ii. Haga clic en el botn

iii. Seleccione la categora Matemticas y Trigonomtricas. Luego haga clic en Aceptar. iv. Seleccione la funcin SUMAR.SI. Luego, haga clic en Aceptar.


CIBERTEC


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.

MARCALG AIWA PHILLIPS SONY

VENTA S/.

Ejercicio3. Utilice la funcin SUMAR.SI para responder las siguientes preguntas: Cul es la suma de las cantidades de los productos cuyos precios son menores a 1000? Cul es la venta total de los productos cuyas cantidades son menores a 20?

CIBERTEC


16

FUNCIN: SUBTOTALES

Devuelve un subtotal en una lista o base de datos. Una vez creada la lista de subtotales, puede cambiarse modificando la frmula SUBTOTALES. Sintaxis: SUBTOTALES(nm_funcin;ref1, ref2, ...) Donde Nm_funcin es el nmero que indica que funcin debe utilizarse. Ref1, Ref2 son de 1 a 254 rangos o referencias para los cuales desea calcular el subtotal.

Nm_funcin es un nmero de 1 a 11 (incluye valores ocultos) o de 101 a 111 (pasa por alto valores ocultos).

Nm_funcin1 2 3 4 5 7 8 9 10 11

FuncinPROMEDIO CONTAR CONTARA MAX MIN DESVEST DESVESTP SUMA VAR VARP

Ejemplo: Para comprender cmo funcionan los SUBTOTALES, escriba la siguiente informacin en una hoja de clculo en blanco. Luego, calcule la suma y el promedio de las cantidades en las celdas E19 y E22.


CIBERTEC


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 frmula correspondiente.

Ahora, se calcularn los subtotales de suma y promedio. i. Coloque el puntero de celda en la posicin E20.

ii. Haga clic en el botn Fx de la barra de frmulas.

iii. Seleccione la categora Matemticas y Trigonomtricas. Luego, haga clic en Aceptar.

CIBERTEC


18

iv. Seleccione la funcin SUBTOTALES. Luego, haga clic en Aceptar.

v. En el argumento Nm_funcin, escriba 9 (porque es el nmero que corresponde a la suma) y seleccione el rango E6:E17 en el cuadro Ref1. Luego, haga clic en Aceptar.


CIBERTEC


19

Al parecer la funcin subtotales realiza la misma operacin que la funcin 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.

Podra indicar cul es la diferencia entre la funcin SUMA y la funcin SUBTOTALES?

Ejercicio: Obtenga el subtotal promedio.

Guarde el archivo con el nombre de Funciones Matemticas.

CIBERTEC


20

2.1.2. Funciones de textoLas 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 categora Texto utilizando la ficha Frmulas y el grupo Biblioteca de funciones.

FUNCIN IZQUIERDA

Devuelve el primer caracter o caracteres de una cadena de texto, teniendo en cuenta el nmero de caracteres que se especifique. Sintaxis: IZQUIERDA(texto;nm_de_caracteres) Donde Texto; es la cadena de texto que contiene los caracteres que desea extraer. Nm_de_caracteres especifica el nmero de caracteres que desea extraer con IZQUIERDA. Nm_de_caracteres debe ser mayor que cero. Si nm_de_caracteres se omite, se calcular como 1. Ejemplo: Para comprender cmo trabajan las funciones de texto, escriba la siguiente informacin en un libro nuevo. Seleccione la celda B5.


CIBERTEC


21

i. Haga clic en el botn de la barra de frmulas. ii. Seleccione la Categora Texto y la funcin IZQUIERDA. Luego, haga clic en el botn Aceptar. iii. En el argumento Texto, escriba B3 y, en Nm_de_caracteres, 6 (porque el nmero de letras del primer nombre es 6). Luego, haga clic en el botn Aceptar.

Observe el resultado.Primer nombreRafael

FUNCIN DERECHA

Devuelve el ltimo caracter o caracteres de una cadena de texto, segn el nmero de caracteres que se especifique. Sintaxis: DERECHA(texto;nm_de_caracteres) Donde Texto es la cadena de texto que contiene los caracteres que desea extraer. Nm_de_caracteres especifica el nmero de caracteres que desea extraer con DERECHA. Nm_de_caracteres debe ser mayor o igual que cero. Si nm_de_caracteres es mayor que la longitud del texto, DERECHA devolver todo el texto. Si nm_de_caracteres se omite, se calcular como 1. Ejemplo: Para comprender cmo trabaja esta funcin, se utilizar el cuadro anterior. Seleccione la celda B6. i. Haga clic en el botn de la barra de frmulas.

ii. Seleccione la Categora Texto y la funcin DERECHA. Luego, haga clic en el botn Aceptar.

CIBERTEC


22

iii. En el argumento Texto escriba B3 y en Nm_de_caracteres 8 (por el nmero de letras del apellido MATERNO). Luego, haga clic en el botn Aceptar.

Observe el resultado.Apellido maternoCastillo

FUNCIN MED

Devuelve un nmero especfico de caracteres de una cadena de texto, comenzando en la posicin que especifique y en funcin del nmero de caracteres que indique. Sintaxis: MED(texto;posicin_inicial;nm_de_caracteres)

Donde Texto es la cadena de texto que contiene los caracteres que desea extraer. Posicin_inicial es la posicin del primer carcter que desea extraer de texto. La posicin_inicial del primer carcter de texto es 1 y as sucesivamente. Nm_de_caracteres especifica el nmero de caracteres que desea que MED devuelva del argumento texto.Si posicin_inicial es mayor que la longitud de texto, MED devuelve "" (texto vaco). Si posicin_inicial es menor que la longitud de texto, pero posicin_inicial ms nm_de_caracteres excede la longitud de texto, MED devuelve los caracteres hasta el final de texto. Si posicin_inicial es menor que 1, MED devuelve el valor de error #VALOR! Si nm_de_caracteres es negativo, MED devuelve el valor de error #VALOR!

Ejemplo: Con la informacin anterior, realice lo siguiente: i. Seleccione la celda B7.


CIBERTEC


23


de la barra de frmulas.

iii. Seleccione la categora Texto y la funcin MED. Luego, haga clic en el botn Aceptar. iv. En el argumento Texto escriba B3, en Posicin_inicial, escriba 9; y en Nm_de_caracteres, 6 (recuerde que el texto que desea extraer es Isabel). Luego, haga clic en el botn Aceptar.

Observe el resultado.Segundo NombreAlberto

FUNCIN 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 unirn en un texto nico. Los elementos de texto pueden ser cadenas de texto, nmeros o referencias a celdas nicas.

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

Ejemplo: Con la informacin anterior realice lo siguiente i. Seleccione la celda B8. de la barra de frmulas.


CIBERTEC


24

iii. Seleccione la categora Texto y la funcin CONCATENAR. Luego, haga clic en el botn Aceptar. iv. En el argumento Texto1, escriba B7; en Texto2, escriba , y, en Texto3, escriba B6. Luego, haga clic en el botn Aceptar.

v. Observe el resultado.Segundo Nombre + Apellido maternoAlberto Castillo

Guarde el archivo con el nombre de Funciones de Texto.


CIBERTEC


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 informacin.

1. Genere el cdigo del empleado tomando en cuenta que est conformado por la primera letra del nombre, las dos ltimas letras del apellido, un guin y el ao de ingreso. 2. Concatene el nombre y el apellido. El resultado ser el siguiente:Cdigo Nombre Daniel Rita Alberto Hugo Carla Apellido Castro Abanto Ubidia Llanos Vilela Ao de Ingreso Nombre Apellido Daniel Castro Rita Abanto Alberto Ubidia Hugo Llanos Carla Vilela

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

2000 2003 2004 2005 2004

Ejercicio 2 En la Hoja2, escriba la siguiente informacin.

1. Obtenga la categora del empleado utilizando la primera letra del cdigo. 2. Obtenga el nmero de ingreso con los 3 ltimos dgitos del cdigo.

CIBERTEC


26

3. Obtenga el ao de ingreso teniendo en cuenta que son los 4 dgitos desde la segunda posicin del cdigo. 4. Genere el cdigo 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 categora. 9999 es el ao de ingreso. El resultado ser el siguiente:

Ejercicio 3 En la Hoja3, escriba la siguiente informacin. 1. Calcule los totales de ventas por tipo utilizando las funciones matemticas ms convenientes. 2. Complete el cuadro de ventas por mes utilizando las funciones matemticas ms convenientes.


CIBERTEC


27

Ejercicio 4 En la Hoja4, escriba la siguiente informacin.

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

1. Calcule las tasas efectivas para cada perodo teniendo en cuenta que la frmula siguiente:n

1 i 1

Donde i es la tasa anual y n es el perodo.

CIBERTEC


28

2.2 FUNCIONES estadsticas y de base de datos2.2.1. Funciones estadsticasLas aplicaciones prcticas de las estadsticas de Excel son numerosas. Quiz tenga sus propias razones para utilizar las funciones estadsticas de Excel. A continuacin, le presentamos algunos ejemplos a modo de sugerencia.

Puede insertar funciones de la categora Estadsticas utilizando la ficha Frmulas y el grupo Biblioteca de funciones. Luego, haga clic en el botn Ms funciones.

FUNCIN CONTAR.SI

Cuenta las celdas que no estn 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 nmero, expresin 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 funcin CONTAR.SI.CONJUNTO, que cuenta el nmero de celdas, dentro del rango, que cumplen varios criterios.


CIBERTEC


29

Ejemplo: Abra un libro nuevo y escriba la siguiente informacin en la Hoja1:

i.

Seleccione la celda C18.

ii. Se insertar la funcin CONTAR.SI con la ayuda del asistente para funciones. Para ello, haga clic en el botn de la barra de frmulas.

iii. Seleccione la categora Estadsticas y localice a la funcin CONTAR.SI. Luego, haga clic en el botn Aceptar. iv. En el argumento Rango, seleccione C4:C15 y, en Criterio la celda, B18. Luego, haga clic en el botn 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 funcin CONTAR.SI para responder las siguientes preguntas: Cuntos televisores se vendieron?

CIBERTEC


30

Cuntos pedidos se efectuaron cuya cantidad super las 20 unidades?

FUNCIN PROMEDIO.SI

Devuelve el promedio (media aritmtica) 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 nmeros, o nombres, matrices o referencias que contengan nmeros. Criterio es el criterio en forma de nmero, expresin, 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 funcin PROMEDIO.SI.CONJUNTO, que devuelve el promedio (media aritmtica) de todas las celdas que cumplen mltiples criterios.

Ejemplo. Escriba la siguiente informacin en la Hoja2:


CIBERTEC


31

i.

Seleccione la celda C18.

ii. Se insertar la funcin PROMEDIO.SI con la ayuda del asistente para funciones. Para ello, haga clic en el botn de la barra de frmulas.

iii. Seleccione la categora Estadsticas y localice a la funcin PROMEDIO.SI. Luego, haga clic en el botn 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 botn 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 informacin:

Ejercicio: Utilice la funcin PROMEDIO.SI para responder las siguientes preguntas: Cul es el promedio de los precios de las licuadoras que se vendieron? Cul es el promedio de la cantidad cuyos precios fueron inferiores a 1000?

CIBERTEC


32

FUNCIN MEDIANA

La mediana es el nmero que se encuentra en medio de un conjunto de nmeros, es decir, la mitad de los nmeros es mayor que la mediana y la otra mitad es menor. Sintaxis: MEDIANA(nmero1;nmero2; ...) Donde: Nmero1, nmero2,... son entre 1 y 255 nmeros cuya mediana desea obtener. Los argumentos deben ser nmeros o nombres, matrices o referencias que contengan nmeros.Si el argumento matricial o de referencia contiene texto, valores lgicos o celdas vacas, estos valores se pasan por alto; sin embargo, se incluirn las celdas con el valor cero. Si la cantidad de nmeros en el conjunto es par, MEDIANA calcula el promedio de los nmeros centrales. Vea la segunda frmula del ejemplo.

Ejemplo: Copie la siguiente informacin en la Hoja3:

i.

Seleccione la celda D20. de la barra de frmulas.


iii. Seleccione la categora Estadsticas y la funcin MEDIANA. Luego, haga clic en el botn Aceptar.


CIBERTEC


33

iv. Seleccione el rango B7:B18 y haga clic en el botn Aceptar.

v. Observe el resultado. Complete el cuadro y los resultados que obtendr sern los siguientes:

Valor medio de produccin de crucetas Valor medio de produccin de poleas Valor medio de produccin de cuas

280 350 350

FUNCIN MODA.UNO

Devuelve el valor que se repite con ms frecuencia en una matriz o rango de datos. MODA es una medida de posicin. Sintaxis: MODA.UNO(nmero1;nmero2; ...nmero30)

Donde: Nmero1, nmero2... son de 1 a 255 argumentos cuya moda desea calcular. Tambin, puede utilizar una matriz nica o una referencia matricial en lugar de argumentos separados con punto y coma o coma (segn sea la configuracin regional del computador). Los argumentos deben ser nmeros, nombres, matrices o referencias que contengan nmeros.Si el argumento matricial o de referencia contiene texto, valores lgicos o celdas vacas, estos valores se pasan por alto; sin embargo, se incluirn 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 funcin MODA.VARIOS, que devuelve una matriz vertical de los valores que se repiten con ms frecuencia en una matriz o rango de datos

CIBERTEC


34

Ejemplo: Copie la informacin en la Hoja4 y agregue la siguiente informacin:

i.



iii. Seleccione la categora Estadsticas y la funcin MODA. Luego, haga clic en el botn Aceptar. iv. Seleccione el rango B7:B18 y haga clic en el botn Aceptar.

v. Observe el resultado. Complete el cuadro y los resultados que obtendr sern los siguientes:Produccin de crucetas ms frecuente Produccin de poleas ms frecuente Produccin de cuas ms frecuente250 350 150


CIBERTEC


35

FUNCIN FRECUENCIA

Calcula la frecuencia con que se repiten los valores de un rango y devuelve una matriz vertical de nmeros. 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 ningn valor, la funcin 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 ningn valor, FRECUENCIA devuelve el nmero de elementos contenido en datos. Consideraciones FRECUENCIA se introduce como una frmula matricial despus de seleccionar un rango de celdas adyacentes en las que se desea que aparezca el resultado de la distribucin. El nmero de elementos de la matriz devuelta supera en una unidad el nmero 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, asegrese de introducir FRECUENCIA en cuatro celdas para los resultados. La celda adicional devuelve el nmero de valores en grupos que sean superiores al valor del tercer intervalo.La funcin FRECUENCIA pasa por alto celdas en blanco y texto. Las frmulas que devuelven matrices deben introducirse como frmulas matriciales.

CIBERTEC


36

Ejemplo: Escriba la siguiente informacin 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 botn de la barra de frmulas.

iv. Seleccione la categora Estadsticas y la funcin FRECUENCIA. Luego, haga clic en el botn Aceptar. v. En el argumento Datos, escriba CRUCETAS, en el argumento Grupos seleccione el rango A21:A26, y, luego, haga clic en el botn Aceptar.


CIBERTEC


37

Observe que el resultado es 1, porque el nmero de producciones de las crucetas en el rango de [150, 200> es slo 1.

Ahora se debe convertir la frmula en su forma matricial para llenar el cuadro. vi. Seleccione el rango B21:B26. vii. Presione la tecla de funcin F2 y, luego, la combinacin CTRL + SHIFT + ENTER. viii. Observe el resultado:Produccin de Crucetas150 200 250 300 350 400 1 0 3 2 1 4

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

1 0 3 2 1 4

Ejercicio: Realice un cuadro similar para la produccin de Poleas.Produccin de Poleas150 250 350 450 2 1 4 4

FUNCIN JERARQUIA.EQV

Devuelve la jerarqua de un nmero en una lista de nmeros. La jerarqua de un nmero es su tamao en comparacin con otros valores de la lista. (Si ordenara la lista, la jerarqua del nmero sera su posicin.) Sintaxis: JERARQUIA.EQV(nmero;referencia;orden)

CIBERTEC


38

Donde Nmero es el nmero cuya jerarqua desea saber. Referencia es una matriz de una lista de nmeros o una referencia a una lista de nmeros. Los valores no numricos se pasan por alto. Orden es un nmero que especifica cmo clasificar el argumento nmero. Si el argumento orden es 0 (cero) o se omite, Excel determina la jerarqua de un nmero 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 jerarqua de un nmero como si la lista definida por el argumento referencia se ordenara en forma ascendente.La funcin JERARQUA.EQV asigna la misma jerarqua a los nmeros duplicados. Sin embargo, la presencia de nmeros duplicados afecta la jerarqua de los nmeros subsiguientes. Por ejemplo, en una lista de nmeros enteros ordenados en orden ascendente, si el nmero 10 aparece dos veces y tiene una jerarqua de 5, entonces el nmero 11 tendra una jerarqua de 7 (ningn nmero tendra jerarqua de 6).

Ejemplo: Escriba la siguiente informacin en la Hoja6:

i.

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

ii. Seleccione la celda D7. iii. Haga clic en el botn de la barra de frmulas.

iv. Seleccione la categora Estadsticas y la funcin JERARQUIA.EQV Luego, haga clic en el botn Aceptar. v. En el argumento Nmero, seleccione la celda C7, en el argumento Referencia escriba Ponderado. Luego, presione Aceptar.


CIBERTEC


39

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

Guarde el archivo con el nombre de Funciones Estadsticas.

CIBERTEC


40

2.2.2. Funciones de Bases de DatosComo se ha visto anteriormente en Excel, una lista o Base de Datos es un conjunto de informacin relacionada entre s. Para poder obtener informacin 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 algn clculo 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 funcin. Nombre_de_campo puede ser texto con el rtulo encerrado entre dobles comillas, como por ejemplo "Edad" o "Pedido $", o como un nmero que represente la posicin 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 comparacin de criterios. Aunque el rango de criterios puede ubicarse en cualquier parte de la hoja de clculo, no coloque el rango de criterios debajo de la lista. Si agrega ms informacin a la lista, la nueva informacin se agrega a la primera fila debajo de la lista. Si la fila de debajo no est vaca, Microsoft Excel no podr agregar la nueva informacin. Asegrese de que el rango de criterios no queda superpuesto a la lista.


CIBERTEC


41

Ejemplos: Para desarrollar los siguientes ejemplos, escriba la informacin 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. Tambin, necesita la definicin de criterios o condiciones que son las que determinarn qu informacin debe calcularse.

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

CIBERTEC


42

Escriba la siguiente informacin en la Hoja2:

Para obtener la informacin del reporte especial, se debe escribir el rango de criterios que satisfagan las condiciones solicitadas. Tome en cuenta que el reporte deber contener la informacin de pedidos que atendi la empleada Valeria Villacorta en el perodo del 1/1/2009 al 30/6/2009 que corresponde al primer semestre del ao 2009.

Definicin de criterios Escriba la siguiente informacin 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.

FUNCIN BDSUMA Suma los nmeros 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 botn de la barra de frmulas.

ii. Seleccione la categora Base de Datos y la funcin BDSUMA. Luego, haga clic en el botn Aceptar.


CIBERTEC


43

iii. Escriba, en cada argumento, la informacin tal como se muestra en la siguiente ventana:

iv. Luego, haga clic en el botn Aceptar y observe que el resultado ser 10189,27.

FUNCIN 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)


ii. Seleccione la categora Base de Datos y la funcin BDPROMEDIO. Luego, haga clic en el botn Aceptar. iii. Escriba, en cada argumento, la informacin tal como se muestra en la siguiente ventana:

CIBERTEC


44


FUNCIN BDMIN - BDMAX

Devuelve el valor mnimo / mximo 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)


ii. Seleccione la categora Base de Datos y la funcin BDMIN. Luego, haga clic en el botn Aceptar. iii. Escriba, en cada argumento, la informacin tal como se muestra en la siguiente ventana:


CIBERTEC


45



ii. Seleccione la categora Base de Datos y la funcin BDMAX. Luego, haga clic en el botn Aceptar. iii. Escriba en cada argumento la informacin tal como se muestra en la siguiente ventana:


FUNCIN BDCONTAR

Cuenta las celdas que contienen nmeros 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 botn de la barra de frmulas.

ii. Seleccione la categora Base de Datos y la funcin BDCONTAR. Luego, haga clic en el botn Aceptar.

CIBERTEC


46

v. Escriba, en cada argumento, la informacin tal como se muestra en la siguiente ventana:

iii. Luego, haga clic en el botn Aceptar y observe que el resultado ser 4.

La funcin BDCONTAR y BDCONTARA son muy similares. La nica diferencia es que BDCONTARA, adems de contar datos numricos, cuenta datos tipo texto.

FUNCIN BDCONTARA

Cuenta las celdas que no estn 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 botn de la barra de frmulas.

ii. Seleccione la categora Base de Datos y la funcin BDCONTARA. Luego haga clic en el botn Aceptar. vi. Escriba, en cada argumento, la informacin tal como se muestra en la siguiente ventana:


CIBERTEC


47

iii. Luego, haga clic en el botn 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 informacin 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


48

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

1. En la Hoja1, escriba los siguientes datos y, utilizando las funciones estadsticasconvenientes, complete la informacin faltante.

Los resultados sern as:


CIBERTEC


49

2. En la Hoja2, escriba los siguientes datos y, utilizando las funciones estadsticasconvenientes complete la informacin faltante.

Los resultados sern los siguientes:

CIBERTEC


50

3. En la Hoja3, escriba los siguientes datos y, utilizando las funciones de bases dedatos convenientes, complete la informacin 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 informacin faltante utilizando los datos de la base de datos anterior.


CIBERTEC


51

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

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

CIBERTEC


52

2.3 FUNCIONES lgicas y de bsqueda2.3.1. Funciones LgicasEste 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 categora Lgicas utilizando la ficha Frmulas, y el grupo Biblioteca de funciones.

FUNCIN Y Devuelve VERDADERO si todos los argumentos son VERDADEROS; devuelve FALSO si uno o ms argumentos son FALSOS. Sintaxis: Y(valor_lgico1;valor_lgico2; ... valor_lgico255)

Donde: Valor lgico1; valor_lgico255 son entre 1 y 255 condiciones que desea comprobar y que pueden ser verdadero o falso, y que pueden ser valores lgicos, matriciales o referencias. Si recuerda, en lgica, la conjuncin se representaba a travs del smbolo ^ y la conjuncin slo es verdadera si los componentes son verdaderos. Aqu se le muestra la tabla de verdad para la conjuncin que es, exactamente, la de la funcin Y.

pV V F F

qV F V F

p^qV F F F


CIBERTEC


53

Los argumentos deben evaluarse como valores lgicos, como VERDADERO o FALSO, o los argumentos deben ser matrices o referencias que contengan valores lgicos. Si un argumento matricial o de referencia contiene texto o celdas vacas, dichos valores se pasarn por alto. Si el rango especificado no contiene valores lgicos, la funcin Y devuelve el valor de error #VALOR!

Ejemplo: En un archivo nuevo, escriba la siguiente informacin en la Hoja1:

A continuacin, se evaluar si el valor A es mayor que 10 y si el valor B es menor que 5. La respuesta deber ser VERDADERO segn la tabla de verdad de la conjuncin. i. Seleccione la celda C5. de la barra de frmulas.


iii. Seleccione la categora Lgicas y la funcin Y. Luego, haga clic en el botn Aceptar. iv. En el argumento Valor_lgico1, escriba A5>10 y, en Valor_lgico2, escriba B510 Y B 10 y, en Valor_lgico2, 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 botn Aceptar.

v. Autollene las celdas siguientes y observe el resultado.

Cuadro de NotasCODIGOA120 A121 A122 A123

NOTA14 12 10 8

COMENTARIOAprob el curso Desaprob el curso Desaprob el curso Desaprob el curso

Ejercicio: Escriba la siguiente informacin en la Hoja3 para desarrollar otro ejemplo.

i.



iii. Seleccione la categora Lgicas y la funcin Si. Luego, haga clic en el botn 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 botn Aceptar.

CIBERTEC


58

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

Guarde el archivo con el nombre de Funciones Lgicas.


CIBERTEC


59

2.3.2. Funciones de BsquedaLas funciones de bsqueda tienen tanto uso como las funciones lgicas. Esto se debe a que permiten localizar informacin asociada a una fila o columna y, con ello, es posible preparar reportes sencillos.

Puede insertar funciones de la categora Bsqueda y referencia utilizando la ficha Frmulas, y el grupo Biblioteca de funciones.

FUNCIN CONSULTAV

Realiza una bsqueda 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 informacin 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, nmeros o valores lgicos. Indicador_columnas es el nmero de columna de matriz_buscar_en desde la cual debe devolverse el valor coincidente. Ordenado es un valor lgico 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 ms 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 estn ubicados en una columna situada a la izquierda de los datos que desea encontrar, es decir, se utiliza en cuadros organizados verticalmente.

CIBERTEC


60

Antes de utilizar la funcin CONSULTAV se definirn las matrices o rangos a usarse, ello simplemente para facilitar la comprensin de la funcin.

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

Definicin 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.


CIBERTEC


61

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

Escriba la siguiente informacin en la Hoja2:

Utilizando la funcin CONSULTAV, se escribir el nombre del artculo asociado al cdigo ingresado. Para ello, i. Seleccione la celda B4. de la barra de frmulas.


iii. Seleccione la categora Bsqueda y Referencia y la funcin CONSULTAV. Luego, haga clic en el botn Aceptar. iv. Escriba, en cada argumento, la informacin tal como se muestra:

CIBERTEC


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 artculo asociado a este cdigo se encuentra en la segunda columna de la matriz; y ordenado est en 0, porque la bsqueda que se necesita realizar debe buscar la coincidencia exacta. Note que tambin podra usar, en lugar de 0, el valor lgico FALSO.

v. Luego, haga clic en el botn Aceptar y observe que el resultado ser Cuaderno 100h. Ejercicio1: Utilizando la funcin CONSULTAV, complete la informacin de la marca, precio y stock que estn asociados al cdigo ingresado.

Ejercicio2: Utilizando la funcin CONSULTAV, complete la informacin que corresponde al mensaje teniendo en cuenta que es el stock el dato asociado a la tabla o matriz MENSAJES. Indique qu informacin escribi en cada argumento y por qu. Argumento Valor_buscado Matriz_buscar_en Indicador_columnas Ordenado Valor Justificacin o razn

FUNCIN CONSULTAH

Realiza una bsqueda 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.


CIBERTEC


63

Matriz_buscar_en es la tabla de informacin 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, nmeros o valores lgicos. Indicador_filas es el nmero 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 nmero de filas en matriz_buscar_en, CONSULTAH devuelve el valor de error #REF! Ordenado es un valor lgico 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 ms 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 bsqueda 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 comparacin se encuentren en la primera fila de una tabla de datos y desee encontrar informacin que se encuentre dentro de un nmero especificado de filas. Utilice esta funcin en cuadros organizados horizontalmente.

Ejemplo: Utilizando la funcin CONSULTAH, se escribir el porcentaje de oferta asociado a la marca. Para ello, realice lo siguiente: i. Seleccione la celda B7. de la barra de frmulas.


iii. Seleccione la categora Bsqueda y Referencia y la funcin CONSULTAH. Luego, haga clic en el botn Aceptar. iv. Escriba, en cada argumento, la informacin tal como se muestra:

CIBERTEC


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 bsqueda que se necesita realizar debe buscar la coincidencia exacta. Note que tambin podra usar, en lugar de 0, el valor lgico FALSO.

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

Ejercicio: Utilizando la funcin CONSULTAH, complete la informacin del regalo que est asociado a la marca.

Guarde el archivo con el nombre de Funciones de Bsqueda.


CIBERTEC


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 informacin:

2. En el campo OBSERVACION, muestre el comentario Mquina operativa si se hautilizado la mquina; en caso contrario, deber mostrar Revisin Pendiente. El resultado ser el siguiente:

CIBERTEC


66

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

2. Utilizando funciones lgicas, escriba Aprobado en la columna OBSERVACION siambas notas son aprobadas y Debe dar sustitutorio si alguna es desaprobatoria. Considere como mnima nota aprobatoria 13. El resultado ser el siguiente:

3. Agregue una columna llamada CURSO APROBADO que indique el nombre delcurso que obtuvo nota aprobatoria. Si fueran ambos cursos, deber mostrar el texto Aprob ambos cursos. Ejercicio 03 1. En la Hoja3, escriba la siguiente informacin:


CIBERTEC


67

2. Utilizando las funciones ms convenientes, complete la informacin faltante,tomando en cuenta lo siguiente: La fecha de compra debe ingresarse. La forma de pago puede ser Contado o Crdito. 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 automticamente la Oferta y el IGV correspondiente. El Total es el importe menos la Oferta.

Ejercicio 04 1. En la Hoja4, escriba la siguiente informacin.

2. Se tiene la lista de datos y se desea generar un pequeo reporte de tal modo que,al ingresar un cdigo a buscar, se muestren automticamente la descripcin y stock asociados al cdigo. 3. Asimismo, deber mostrar una oferta del 15% slo en el caso de los monitores; en caso contrario, la oferta ser del 5%.

CIBERTEC


68

Ejercicio 05 1. En la Hoja5, escriba la siguiente informacin:

2. disee un reporte con la informacin anterior, tomando en cuenta las siguientesconsideraciones: El campo Id.Pedido deber seleccionarse de una lista. Al elegir un Id.Pedido; los datos del cliente, empleado, fecha de pedido, forma de envo, Pedido $ y Local debern mostrarse de manera automtica. Agregue un campo llamado Comentario Especial. Este deber mostrar el mensaje correspondiente segn la siguiente tabla: Pedido $ < 2500 >=2500 Comentario Especial Revise historial del Cliente. Pedido en parmetros esperados.

Tambin, 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.


CIBERTEC


69

2.4 FUNCIONES financierasEjecutan operaciones contables comunes, como determinar los pagos de un prstamo, el valor futuro o el valor neto actual de una inversin, y los valores de obligaciones y bonos.

Puede insertar funciones de la categora Financieras utilizando la ficha Frmulas y el grupo Biblioteca de funciones.

Los argumentos ms comunes de las funciones financieras son los siguientes: Valor futuro (vf) Nmero de perodos (nper) Pago (pago) Valor actual (va) Inters (interes) Tipo (tipo) Valor de la inversin o del prstamo una vez realizados todos los pagos

Nmero total de pagos o perodos de una inversin Importe pagado, peridicamente, en una inversin o prstamo. No puede cambiar durante la vigencia de la anualidad. Valor de una inversin o prstamo al comienzo del perodo de inversin. Por ejemplo, el valor presente de un prstamo es el importe principal que se toma prestado. Inters o el descuento de un prstamo o una inversin

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

CIBERTEC


70

2.4.1. Funciones de Pago FUNCIN PAGO

Devuelve el pago peridico de una anualidad, basndose en pagos constantes y la tasa de inters constante. Sintaxis: PAGO(tasa,nper,va,[vf],[tipo])

Donde Tasa es el tipo de inters del prstamo. Nper es el nmero total de pagos del prstamo. Va es el valor actual o lo que vale ahora la cantidad total de una serie de pagos futuros. Tambin, se conoce como el principal. Vf es el valor futuro o un saldo en efectivo que desea lograr despus 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 prstamo es 0). Tipo es el nmero 0 (cero) 1 e indica el vencimiento de los pagos. TIPO 0 u omitido 1 VENCIMIENTO PAGO Al final del perodo Al inicio del perodo

Puede apreciar que de manera automtica aparece un esquema en el lado izquierdo de la lista. Con ello, puede mostrar u ocultar la informacin por niveles; por ejemplo, para observar los resultados de una mejor manera, utilice el nivel 2. Para ello, haga clic en el nmero 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, depsitos en cuentas de ahorros, se representa con nmeros negativos; el efectivo que recibe, por ejemplo, cheques de dividendos, se representa con nmeros positivos.

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


CIBERTEC


71

i.

Seleccione la celda B7 para calcular la tasa efectiva mensual e ingrese la siguiente frmula: =((1+B5)^(1/B6))-1La frmula financiera corresponde al clculo de la tasa efectiva mensual, dada una tasa anual (i) y un perodo en meses (n).

En general, la frmula financiera corresponde al clculo de la tasa efectiva, dada una tasa anual (i) y un perodo (n).n

1 i 1

La cuota fija mensual se obtendr con la funcin PAGO. ii. Seleccione la celda B11 y escriba la frmula siguiente: =PAGO(B7;B6;-B4)

El dato del valor actual (VA) es el monto del prstamo 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 funcin, ya que no son requeridos en la solucin del caso.

CIBERTEC


72

Ejemplo2: Se desea obtener el importe que generara una cuota vencida y una cuota adelantada por un prstamo de 1500 soles durante 1 ao con una tasa de inters mensual del 4%. Escriba la siguiente informacin:

Para calcular el importe de la cuota vencida, se debe tomar en cuenta, en la funcin 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 frmula: =PAGO(E5;E6;-E4;;0) iii. Seleccione la celda E8 para calcular la cuota mensual adelantada. iv. Escriba la siguiente frmula: =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 dems argumentos no son requeridos en la solucin del caso.

El resultado final ser el siguiente:


CIBERTEC


73

Ejemplo3: Se desea obtener 20000 dlares luego de 1 ao y medio. Cunto se debe depositar mensualmente en el banco si ste paga 1.5% como tasa de inters mensual? Escriba la siguiente informacin:

i.

Seleccione la celda H7 para calcular la cuota mensual.

ii. Escriba la siguiente frmula: =PAGO(H6;H5;;-H4)Observe que los argumentos utilizados han sido TASA, NPER y VF (que debe ir en su forma negativa). Los dems argumentos no son requeridos en la solucin del caso.

El resultado ser el siguiente:

CIBERTEC


74

FUNCIN VF

Devuelve el Valor Futuro de una inversin basndose en pagos peridicos constantes y en una tasa de inters constante. Sintaxis: VF(tasa;nper;pago;va;tipo)

Donde Tasa es el tipo de inters del prstamo. Nper es el nmero total de pagos del prstamo. Pago es el pago efectuado en cada periodo y no puede cambiar durante la vigencia de la inversin. Va es el valor actual o la suma total de una serie de pagos futuros. Si se omite VA = 0 Tipo es el nmero 0 (cero) 1 e indica el vencimiento de los pagos. TIPO 0 1 VENCIMIENTO PAGO Al final del perodo Al inicio del perodo

En todos los argumentos, el efectivo que paga, por ejemplo, depsitos en cuentas de ahorros, se representa con nmeros negativos; el efectivo que recibe, por ejemplo, cheques de dividendos, se representa con nmeros positivos.

Ejemplo: Se desea capitalizar los cuotas de 680 soles mensuales, los cuales se depositan al Banco Internacional durante 1ao, a una tasa de inters efectiva del 3.5%, cunto se cobrar al final del plazo? Escriba la siguiente informacin en la Hoja2:

i.

Seleccione la celda B7 para calcular la cuota mensual.

ii. Escriba la siguiente frmula: =VF(B5;B6;-B4)


CIBERTEC


75


FUNCIN VA

Devuelve el valor actual de una inversin. El valor actual es el valor que tiene, actualmente, la suma de una serie de pagos que se efectuarn en el futuro. Por ejemplo, cuando pide dinero prestado, la cantidad del prstamo es el valor actual para el prestamista. Sintaxis: VA(tasa;nper;pago;vf;tipo) Donde Tasa es el tipo de inters del prstamo. Nper es el nmero total de pagos del prstamo. Pago es el pago efectuado en cada periodo y no puede cambiar durante la vigencia de la inversin. Vf es el valor futuro o saldo en efectivo que se desea lograr despus de efectuar el ltimo pago Tipo es el nmero 0 (cero) 1 e indica el vencimiento de los pagos. TIPO 0 1 VENCIMIENTO PAGO Al final del perodo Al inicio del perodo

Ejemplo: Hallar el valor actual de $5000 pagaderos en 5 aos con una tasa anual efectiva de 6%. Escriba la siguiente informacin en la Hoja3:

i.

Seleccione la celda B6 para calcular el valor actual.

ii. Escriba la siguiente frmula: =VA(B3;B4;;-B2)

CIBERTEC


76

Observe que no ha sido necesario hacer ninguna conversin en los datos ya que todos guardan relacin.


Ejercicio: Ahorramos 350 mensuales durante 5 aos en un banco que paga el 18% nominal anual y deseamos saber cunto representan estas mensualidades al da de hoy. Escriba la siguiente informacin:

Observe que ser necesario obtener la tasa efectiva mensual. Recuerda cmo obtenerlo? En la celda E6, escriba la siguiente frmula: =((1+E3)^(1/12))-1

i.

Seleccione la celda E7 para calcular el valor actual.

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


CIBERTEC


77

2.4.2. Cronogramas de PagoDespus 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. ii. iii. iv. v. El monto del prstamo Los saldos deudores Las amortizaciones realizadas Los intereses de cada perodo Las cuotas que debemos cancelar

En las finanzas la expresin amortizar se utiliza para denominar un proceso financiero mediante el cual se extingue, gradualmente, una deuda y sus intereses por medio de pagos peridicos, que pueden ser iguales o diferentes. Para la amortizacin 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 an falta por amortizar.

CIBERTEC


78

FUNCIN PAGOPRIN

Devuelve el pago sobre el capital de una inversin durante un perodo determinado basndose en pagos peridicos y constantes, y en una tasa de inters constante. Sintaxis: PAGOPRIN(tasa;perodo;nper;va;vf;tipo) Donde Tasa es la tasa de inters por perodo. Perodo especifica el perodo, que debe estar entre 1 y el valor de nper. Nper es el nmero total de perodos 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 despus 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 prstamo es 0). Tipo es el nmero 0 1 e indica cundo vencen los pagos. TIPO 0 u omitido 1 VENCIMIENTO PAGO Al final del perodo Al inicio del perodo

Consideraciones Mantenga uniformidad en el uso de las unidades con las que especifica los argumentos tasa y nper. Si realiza pagos mensuales de un prstamo de cuatro aos con un inters anual del 12 por ciento, calcule la tasa efectiva mensual con la frmula. Ejemplo. Elabore el programa de amortizacin (cronograma de pagos) para una deuda de S/. 1,000 pactada a una tasa de inters anual del 10%, mediante la modalidad de cuotas constantes en 4 aos. Escriba el siguiente cuadro en la hoja 4:


CIBERTEC


79

i.

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

ii. En la celda C10, se calcular la amortizacin del primer mes. Escriba la siguiente frmula =PAGOPRIN($B$6;A10;$B$5;-$B$4) o ingrsela a travs del botn Insertar funcin.

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

iii. Copie la frmula hasta el cuarto perodo. Los resultados que obtendr sern los siguientes:

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

FUNCIN PAGOINT

Devuelve el inters pagado en un perodo especfico por una inversin basndose en pagos peridicos constantes y en una tasa de inters constante. Sintaxis: PAGOINT(tasa;perodo;nper;va;vf;tipo)

Ahora, se calcular el inters pagado ao tras ao.

CIBERTEC


80

i.

En la celda D10, escriba la siguiente frmula: =PAGOINT($B$6;A10;$B$5;-$B$4) o ingrsela a travs del botn Insertar funcin.

ii. Copie la frmula hasta el cuarto perodo. Los resultados que obtendr sern los siguientes:

Para completar el cronograma realice el siguiente procedimiento: Complete el saldo para cada perodo (ao) i. En la celda B10, escriba la frmula =B9-C10.

ii. Luego, cpiela hasta el final del perodo.

Complete el pago para cada perodo (ao) iii. En la celda E10, escriba la frmula =PAGO($B$6;$B$5;-$B$4) o ingrsela a travs del botn Insertar funcin.


CIBERTEC


81

iv. Luego, cpiela hasta el final del perodo.

Compruebe los resultados obtenidos. v. Finalmente, sume las columnas de amortizacin, inters y pago. El cronograma de pagos tendr la siguiente informacin:

Guarde el archivo con el nombre de Funciones Financieras.

CIBERTEC


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 depsitos de S/. 80 mensuales desde el primer mes de su nacimiento. A cunto ascender el fondo cuando su hijo cumpla los 5 aos si el banco le paga un inters de 1.15% mensual? En una hoja nueva, escriba la siguiente informacin.

Utilizando la funcin financiera conveniente, calcule el fondo de ahorro que se obtendr al finalizar el 5to. Ao. Respuesta: 6858.30

2.

Escriba los siguientes datos y responda a la consulta del siguiente caso: Cunto deber depositar mensualmente en el banco si desea obtener 15000 dlares luego de medio ao si ste paga 20% como tasa de inters anual?

3.

Escriba los siguientes datos y responda a la consulta del siguiente caso: Calcule la cuota fija mensual que deber pagar por un prstamo de 60000 dlares durante 30 meses con una tasa efectiva anual del 30%.

Caso 1Monto o Valor futuro $ Plazo (meses) TEA 15,000.00 6 22%

Tasa mensual Cuota mensual $

Caso 2Prstamo $ 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 generara una cuota vencida y una cuota adelantada, por un prstamo de 3500 soles durante 9 meses con una tasa de inters mensual del 3.60%.


CIBERTEC


83

Caso 3Prstamo S/. Tasa mensual Plazo 3,500.00 3.60% 9

Importe cuota vencida Importe cuota adelantada5. Utilizando las frmulas y funciones financieras convenientes responda a la consulta del siguiente caso: A cunto equivalen hoy 3 pagos futuros consecutivos y anuales de 1000 soles obtenidos a una tasa de inters anual del 4% Utilizando las frmulas y funciones financieras convenientes responda a la consulta del siguiente caso: Hallar el valor actual de $20,000, pagaderos en 3 aos a la tasa efectiva mensual de 1.2 % Elabore el programa de amortizacin (cronograma de pagos) para una deuda de 25000 dlares a una tasa de inters anual del 18%, mediante la modalidad de cuotas mensuales constantes durante 36 meses.

6.

7.

CIBERTEC


84

R ESUMEN Existen varias maneras de ingresar una funcin. Una de ellas, la ms simple: consiste en escribir la funcin. Otra es utilizando el asistente para funciones. Dependiendo de la complejidad de lo que se desea obtener con la frmula se elige el mtodo para insertar la funcin. Existen algunos operadores que reemplazan funciones. Por ejemplo, el smbolo ^ puede utilizarse en lugar de la funcin POTENCIA, mientras que el smbolo & puede usar en lugar de la funcin CONCATENAR. Si se necesita anidar funciones, el mtodo ms prctico consiste en utilizar el asistente para funciones .

Para insertar funciones de bases de datos, se necesita escribir los criterios o condiciones que se tomarn en cuenta para utilizar la funcin. Puede obtener informacin y ms ejercicios sobre el tema de funciones en los siguientes vnculos: 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


CIBERTEC


85


3

LOGROS DE LA UNIDAD DE APRENDIZAJE Al trmino de la unidad, el alumno realiza anlisis 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 anlisis de datos. El alumno resuelve casos especiales con las herramientas Buscar Objetivo y Solver.

CIBERTEC


86

3.1 HERRAMIENTAS DE ANLISISLas herramientas de anlisis estn orientadas a personas que deseen sacar el mximo partido a Excel con funciones ms especficas relacionadas a materias como la educacin, la investigacin, el negocio, la ingeniera, la estadstica. En esta unidad, abordaremos las siguientes herramientas para efectuar anlisis de datos:

Escenarios Bsqueda de objetivo Solver

Los escenarios determinan posibles resultados, estos pueden tener muchas variables, pero puede acomodar hasta 32 valores. La bsqueda 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. Adems de estas herramientas, puede instalar complementos que le ayudarn a realizar anlisis de datos, como el complemento Solver. El complemento Solver es similar a la bsqueda de objetivo, pero puede acomodar ms variables. Tambin, puede crear previsiones mediante varios comandos y el controlador de relleno que estn integrados en Excel. Para modelos ms avanzados, puede usar el complemento Analysis Pack.En Excel 2010, las herramientas de Anlisis Y si se encuentran en la ficha Datos, en el grupo Herramientas de datos y en el comando Anlisis Y si.


CIBERTEC


87

3.1.1. EscenariosUn escenario es un conjunto de valores que Microsoft Excel guarda y puede sustituir, de manera automtica, en la hoja de clculo. Son utilizados para prever el resultado de un modelo de hoja de clculo. Puede crear y guardar diferentes grupos de valores en una hoja de clculo y, a continuacin, 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 anlisis. Al usar escenarios, usted realiza un anlisis que Excel denomina Anlisis Y si.

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

La informacin necesaria, para crear escenarios, se debe encontrar en una hoja de clculo o en un libro, pero puede recopilar informacin de escenario de otros orgenes mediante el comando Combinar. Por ejemplo, suponga que est intentando crear un presupuesto para una compaa ms grande. Puede recopilar escenarios de distintos departamentos como Nminas, Produccin, Marketing y Legal, ya que cada uno de estos orgenes usa informacin diferente al crear escenarios. Para comparar varios escenarios, puede crear un informe que los resuma en la misma pgina. El informe puede enumerar los escenarios en paralelo o presentarlos en un informe de tabla dinmica (informe de tabla dinmica: informe de Excel interactivo de tablas cruzadas que resume y analiza datos, como registros de una base de datos, de varios orgenes, incluidos los que son externos). Para desarrollar los temas de esta unidad, abra un libro nuevo y en la Hoja1 escriba la siguiente informacin:

CIBERTEC


88

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

Crear un escenarioA continuacin, 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 anlisis de la ficha Datos, haga clic en el comando Anlisis Y si.

iii. Haga clic en Administrador de escenarios. iv. Haga clic en el botn Agregar.


CIBERTEC


89

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

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

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

Ejercicio.

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

CIBERTEC


90

Mostrar un escenario i. Seleccione la hoja donde cre el escenario.

ii. En el grupo Herramientas de anlisis de la ficha Datos, haga clic en el comando Anlisis Y si. iii. Haga clic en Administrador de escenarios. iv. Seleccione el escenario Caso Pesimista y haga clic en el botn Mostrar.

v. Haga clic en el botn Cerrar y observe el resultado.

Cuando cierra el cuadro de dilogo Administrador de escenarios, los valores del ltimo escenario que mostr permanecen en la hoja de clculo. Si guard los valores iniciales como un escenario, podr mostrar esos valores antes de cerrar el cuadro de dilogo Administrador de escenarios.

Crear un informe resumen de escenario i. Seleccione la hoja donde cre el escenario.

ii. En el grupo Herramientas de anlisis de la ficha Datos, haga clic en el comando Anlisis Y si. iii. Haga clic en Administrador de escenarios.


CIBERTEC


91

iv. Haga clic en el botn Resumen.

Observe que Excel, automticamente, selecciona la celda B8 porque es la que contiene la frmula.

v. Ahora, haga clic en el botn 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 automticamente. Si cambia los valores de un escenario, esos cambios no aparecern en ningn informe de resumen existente sino que aparecern al crear un nuevo informe de resumen.

Crear un informe de tabla dinmica de escenario i. Seleccione la hoja donde cre el escenario.

ii. En el grupo Herramientas de anlisis de la ficha Datos, haga clic en el comando Anlisis Y si.

CIBERTEC


92

iii. Haga clic en Administrador de escenarios. iv. Haga clic en el botn Resumen. v. Seleccione la opcin Informe de tabla dinmica de escenario. Luego, haga clic en el botn 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 anlisis de la ficha Datos, haga clic en el comando Anlisis Y si. iii. Haga clic en Administrador de escenarios. iv. Seleccione el escenario a eliminar. Y luego, haga clic en el botn Eliminar.

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


CIBERTEC


93

3.1.2. Buscar ObjetivoSi sabe el resultado que desea de una frmula, pero no est seguro de cules son los datos que requiere la frmula para obtener dicho resultado, puede usar la herramienta Buscar Objetivo

Buscar objetivo es parte de una serie de comandos a veces denominados herramientas de anlisis Y si.

La Bsqueda de objetivo funciona con un slo valor de entrada de variable. Si desea determinar ms de un valor de entrada, por ejemplo, la cantidad del prstamo y el importe de pago mensual del prstamo, deber usar el complemento Solver.

Escriba en la Hoja2 la siguiente informacin:

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

Ejemplo. Se desea incrementar el valor de venta del producto a 1500, en cunto variar el porcentaje de Utilidad? i. Seleccione la celda B6.

ii. En el grupo Herramientas de anlisis de la ficha Datos, haga clic en el comando Anlisis Y si. iii. Luego, elija la opcin Buscar Objetivo.

CIBERTEC


94

iv. Escriba la siguiente informacin en la ventana Buscar objetivo. Luego, haga clic en el botn Aceptar.

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


Observe que el porcentaje de utilidad ha variado de 20% a 25% despus de emplear la funcin Buscar objetivo.

Ejercicio1. La competencia vende a 1150 el mismo producto, cunto tendra que disminuir el porcentaje de utilidad para poder venderlo a ese mismo precio?


CIBERTEC


95

Los resultados que obtendr sern los siguientes:

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

Ejercicio2. Se desea vender a 1150 el mismo producto, cunto tendra que variar el costo del producto?. Los resultados que obtendr sern los siguientes:

CIBERTEC


96

3.1.3. SolverCon Solver, puede buscarse el valor ptimo para una frmula, trabaja con el grupo de celdas que estn relacionadas, directa o indirectamente, con la frmula 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 anlisis.

Solver ajusta los valores de las celdas variables que se especifiquen, denominadas celdas cambiantes, para obtener el resultado especificado en la frmula 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 continuacin, haga clic en la categora Complementos. ii. En el cuadro Administrar, haga clic en Complementos de Excel y, a continuacin, en Ir. iii. En el cuadro Complementos disponibles, active la casilla de verificacin Complemento Solver y, a continuacin, haga clic en Aceptar.

Ejemplo. Aada los datos faltantes tal como se muestra.

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


CIBERTEC


97


A continuacin, se utilizar la herramienta Solver con la informacin del cuadro anterior para responder la siguiente pregunta. Ejemplo1. Cul ser el costo del producto, si se desea que el valor de venta (incluido IGV) sea 2000 y el porcentaje utilidad sea cmo mnimo 20%? i. Seleccione la celda B9.

ii. En el grupo Anlisis 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 frmula. Si desea que la celda objetivo tenga un valor determinado, haga clic en Valor de y, a continuacin, escriba el valor en el cuadro. Si desea que el valor de la celda objetivo sea el valor mximo posible, haga clic en Mx. Si desea que el valor de la celda objetivo sea el valor mnimo posible, haga clic en Mn.

CIBERTEC


98

iv. Seleccione la celda B4 porque segn 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 mximo.

v. En el cuadro Sujeto a restricciones, haga clic en el botn Agregar y escriba la siguiente informacin. Luego haga clic en el botn 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 Restriccin aparecer entero. Si hace clic en bin, en el cuadro restriccin aparecer Binario. Slo pueden aplicarse las relaciones ent y bin en las restricciones en celdas cambiantes.

vi. Al aceptar, la ventana de parmetros 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 solucin para el enunciado propuesto.


CIBERTEC


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. Cunto ser el % de utilidad, si se desea que el valor de venta (incluido IGV) sea el mnimo y el porcentaje utilidad sea cmo mnimo 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 Anlisis 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 Mnimo. iv. Seleccione la celda B5 porque segn el enunciado se desea obtener el nuevo % de utilidad. v. En el cuadro Sujeto a restricciones, haga clic en el botn Agregar y escriba la siguiente informacin. Luego, haga clic en el botn Aceptar.

CIBERTEC


100

vi. Al aceptar, la ventana de parmetros 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 solucin para el enunciado propuesto. Observe los resultados obtenidos.


CIBERTEC


101

ACTIVIDADES 10 En un nuevo archivo, resuelva los siguientes ejercicios; luego grbelo como ACTIVIDAD10 Ejercicio 1 En la Hoja1, escriba la siguiente informacin:

Tome en cuenta las siguientes frmulas: A = Ventas Netas - Costo de Ventas B = G. Ventas + G. Administrativos + G. Financieros C = Utilidad Bruta - Gastos de Operacin D = Ingresos Varios + Intereses Ganados - G. Extraordinarios E = Utilidad de Operacin + (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


102

Ejercicio 2 CURSO DE INGLES En la Hoja2, escriba la siguiente informacin de notas del curso Ingls:

Cuadro de NotasBimestre 1 Bimestre 2 Bimestre 3 Bimestre 4 15.00 12.00 15.00

PROM FINAL1. Calcule el promedio final teniendo en cuenta que es la media aritmtica 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 mnima de 11? Respuesta.- ___________.

3. Cul ser el mnimo promedio final teniendo en cuenta que la nota del bimestre 4 sea cmo mnimo 15? Respuesta.- ___________.

4. Localice a las celdas precedentes del promedio final. Indique cmo lo hizo.

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

1. Calcule el promedio final teniendo en cuenta la frmula de evaluacin del curso en el ciclo actual.


CIBERTEC


103

2. Qu nota deber obtener el alumno en el examen final para poder aprobar el curso con la nota mnima 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


104

R ESUMEN Los escenarios, con frecuencia, son utilizados para realizar anlisis de Estados de resultados, financieros o cualquier otro anlisis que requiera una comparacin de valores. Puede comparar los valores guardados en el escenario a travs de la creacin de un informe tipo resumen o tipo tabla dinmica. 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 frmula 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 informacin y ms ejercicios sobre el tema de herramientas de anlisis en los siguientes vnculos: 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


CIBERTEC


105


4

LOGROS DE LA UNIDAD DE APRENDIZAJE Al trmino de la unidad, el alumno disea formularios insertando controles de formulario como cuadro combinado, cuadro de lista, cuadro de opciones, botn de nmero, casilla de verificacin y botn de comando. Asimismo, automatiza tareas repetitivas usando macros.

TEMARIO Formularios Macros

ACTIVIDADES PROPUESTAS Los alumnos disean 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 ejecucin.

CIBERTEC


106

4.1 FORMULARIOSUn formulario, ya sea impreso o en lnea, es un documento diseado con formato y estructura estndar que facilita la captura, la organizacin y la edicin de la informacin. Si necesita un formulario de introduccin de datos complejo o es

ms excel 2007-avanzado

Documents