of 156/156
Organización Educativa Leonardo Davinci Curso: Excel 1 Nivel: Avanzado NOTA: La información contenida en este material, tiene un fin exclusivamente didáctico, y por lo tanto, no está previsto su aprovechamiento a nivel profesional o industrial. Organización Leonardo Da Vinci no será jurídicamente responsable por errores u omisiones; daños y perjuicios que se pudieran atribuir al uso de la información comprendida en este libro, ni por la utilización indebida que pueda dársele.

Microsoft Excel 2007 - Basico & Avanzado

  • View
    25

  • Download
    2

Embed Size (px)

DESCRIPTION

Los nombres comerciales que aparecen en este libro son marcas registradas de sus propietarios y se mencionan únicamente con fines didácticos, por lo que Organización Leonardo Da Vinci no asume ninguna responsabilidad por el uso que se dé a esta información, ya que no infringe ningún derecho de registro de marca.

Text of Microsoft Excel 2007 - Basico & Avanzado

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 1 Nivel: Avanzado

    NOTA: La informacin contenida en este material, tiene un fin exclusivamente didctico, y por lo tanto, no est previsto su aprovechamiento a nivel profesional o industrial. Organizacin Leonardo Da Vinci no ser jurdicamente responsable por errores u omisiones; daos y perjuicios que se pudieran atribuir al uso de la informacin comprendida en este libro, ni por la utilizacin indebida que pueda drsele.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 2 Nivel: Avanzado

    Los nombres comerciales que aparecen en este libro son marcas registradas de sus propietarios y se mencionan nicamente con fines didcticos, por lo que Organizacin Leonardo Da Vinci no asume ninguna responsabilidad por el uso que se d a esta informacin, ya que no infringe ningn derecho de registro de marca. Colabor con la recopilacin de esta informacin: Ing. Nathaly Prado Ramrez

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 3 Nivel: Avanzado

    NDICEDECONTENIDOS

    FILTROSDEDATOS................................................................................................................6FILTROSDEDATOS............................................................................................................7

    AUTOFILTRO.......................................................................................................................7FILTROSAVANZADOS.........................................................................................................9VALIDACINDEDATOS....................................................................................................11

    PRCTICAGUIADA..........................................................................................................14EXCELFINANCIERO..............................................................................................................15

    FUNCIONESFINANCIERAS...............................................................................................16FUNCINPAGO................................................................................................................16FUNCINPAGOINT..........................................................................................................20FUNCINPAGOPRIN........................................................................................................22FUNCINVALORACTUALNETO(VNA)............................................................................24FUNCINTIR....................................................................................................................27

    PRCTICAGUIADA..........................................................................................................30BASEDEDATOSI.................................................................................................................31

    FUNCIONESBASEDEDATOS...........................................................................................32CREARBASEDEDATOS....................................................................................................32FUNCINBDCONTARA.....................................................................................................33FUNCINBDCONTAR.......................................................................................................35FUNCINBDMAX.............................................................................................................37FUNCINBDMIN..............................................................................................................39FUNCINBDPRODUCTO..................................................................................................41FUNCINBDPROMEDIO..................................................................................................43FUNCINBDSUMA..........................................................................................................45

    PRCTICAGUIADA..........................................................................................................47BASEDEDATOSII................................................................................................................48

    APLICACINDELASBASEDEDATOS...............................................................................49CONSULTADEBASEDEDATOS........................................................................................49

    OBTENERDATOSEXTERNOS...........................................................................................52IMPORTARDESDEACCESS...............................................................................................52IMPORTARDESDEWEB....................................................................................................53IMPORTARDESDETEXTO.................................................................................................55

    PRCTICAGUIADA..........................................................................................................58TABLASDINMICAS.............................................................................................................59

    TABLASDINMICAS........................................................................................................60

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 4 Nivel: Avanzado

    INGRESOYANLISISDEDATOS.......................................................................................60DISEODELATABLADINMICA.....................................................................................60GENERARGRFICODINMICO........................................................................................63

    PRCTICAGUIADA..........................................................................................................66ANLISISDEDATOS.............................................................................................................68

    ANLISISDEDATOS........................................................................................................69ESCENARIOS.....................................................................................................................69SUBTOTALES.....................................................................................................................72TABLASDEDATOS............................................................................................................74TABLASDEDATOSDEUNAVARIABLE.............................................................................75TABLASDEDATOSDEDOSVARIABLES............................................................................77BUSCAROBJETIVO............................................................................................................79

    PRCTICAGUIADA..........................................................................................................81OPTIMIZACIN....................................................................................................................82

    OPTIMIZARDATOSENEXCEL..........................................................................................83FUNCINSOLVER.............................................................................................................84DEFINIRYRESOLVERUNPROBLEMA...............................................................................85CREARINFORMES............................................................................................................88ADMINISTRARRESTRICCIONES........................................................................................91SOLVERYPROGRAMACINLINEAL.................................................................................93

    PRCTICAGUIADA..........................................................................................................99FORMULARIOS...................................................................................................................100

    FORMULARIOSENEXCEL...............................................................................................101FORMULARIOS...............................................................................................................101CONTROLESDEFORMULARIOSENEXCEL.....................................................................102DISEOYCREACINDEFORMULARIOS........................................................................103

    PRACTICACLASE............................................................................................................111MACROSI...........................................................................................................................116

    CREACINDEMACROS..................................................................................................117SEGURIDADPARAMACROS...........................................................................................117GRABARMACROS..........................................................................................................117EJECUTARMACROS........................................................................................................120ASIGNARMACROSAOBJETOS.......................................................................................121CREARMACROSMANUALMENTE..................................................................................122ELENTORNODELEDITORDEVISUALBASIC..................................................................123GUARDARLIBROSENEXCELCONMACROS...................................................................126

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 5 Nivel: Avanzado

    PRCTICAGUIADA.........................................................................................................128FORMULARIOSDEVBA.......................................................................................................129

    FORMULARIOSDEVISUALBASICPARAMACROS...........................................................130USERFORM.....................................................................................................................130CREARUSERFORM.........................................................................................................130CONTROLESDEUSERFORM...........................................................................................132PROGRAMACIONDELOSCONTROLES...........................................................................135ENLAZARCONTROLESALASCELDAS.............................................................................136

    PRCTICAGUIADA.........................................................................................................14211MACROSIII....................................................................................................................144

    PROGRAMACIONENVISUALBASICPARAMACROS.......................................................145DECLARACINDEVARIABLES........................................................................................145ESTRUCTURAIFTHENELSE.......................................................................................145ESTRUCTURAFORTONEXT....................................................................................147ESTRUCTURAWHILEWEND.......................................................................................150

    PRCTICAGUIADA.........................................................................................................154

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 6 Nivel: Avanzado

    Sesin 1

    FILTROS DE DATOS

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 7 Nivel: Avanzado

    FILTROS DE DATOS AUTOFILTRO Seleccionar los datos que forman la lista y elegir la ficha Datos, elegir

    opcin Filtro. O tambin desde la Ficha Inicio, seleccionar Ordenar y Filtrar y seleccionar la opcin Filtro.

    Los nombres de campo que estn como encabezados de las columnas

    pasan a ser listas desplegables que permiten realizar la seleccin. Desde la opcin de Autofiltro, podemos seleccionar los datos que se

    desean mostrar.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 8 Nivel: Avanzado

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

    Ingresar la condicin respectiva.

    EL resultado sera:

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 9 Nivel: Avanzado

    Tambin podemos ordenar los datos de mayor a menor o viceversa. Ejemplo: Mostrar todos los productos ordenados por PrecioBase de Mayor a Menor:

    El resultado mostrar todos los datos de la siguiente manera:

    FILTROS AVANZADOS Es una manera de seleccionar algunos datos y mostrarlos en una ubicacin diferente. Esta opcin utiliza los cuadros de criterios vistos en las funciones de Base de Datos. Ejemplo 01: Filtrar los datos de los Abarrotes: Crear el cuadro de Criterios:

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 10 Nivel: Avanzado

    Seleccionar la opcin de Filtro Avanzado de la Ficha Datos: El resultado sera:

    Ejemplo 02: Filtrar los datos de las golosinas cuyo precio base sea menor que S/. 220 Crear el cuadro de Criterios:

    Destino de los Datos

    Cuadro de Criterios

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 11 Nivel: Avanzado

    Seleccionar la opcin de Filtro Avanzado de la Ficha Datos: El resultado sera:

    VALIDACIN DE DATOS Se utiliza para definir restricciones sobre los datos que se pueden insertar en una celda, y para mostrar mensajes que insten a los usuarios a especificar entradas correctas y les notifiquen las entradas incorrectas. Seleccionar las celdas para validacin, luego seleccione ficha Datos, en

    el grupo Herramienta de datos, hacer clic en el botn Validacin de datos, y se muestra la siguiente figura:

    Destino de los Datos

    Cuadro de Criterios

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 12 Nivel: Avanzado

    Ficha Configuracin: Permite ingresar la condicin que debe cumplir el contenido de la celda. Ejemplo: Que la celda no acepte nmeros menores que 0.

    Ficha Mensaje Entrante:

    Se define un mensaje informativo donde se indican los valores que se deben ingresar en esa celda.

    Ficha Mensaje de Error:

    Es un mensaje de Error que se mostrar cada vez que se ingresan nmero que no cumplen con la condicin ingresada en la celda.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 13 Nivel: Avanzado

    En la celda se mostrar el Mensaje de Entrada, tal como se muestra en

    la siguiente figura. Si se ingresa un dato que no cumple con la condicin, se mostrar el

    mensaje de error:

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 14 Nivel: Avanzado

    PRCTICA GUIADA En un nuevo libro de trabajo, copiar los siguientes datos y resolver lo

    que se te pide. Validar el ingreso de datos de la columna de Hijos, slo debe aceptar

    nmeros, en caso contrario debe mostrar un mensaje de Error. Grabar el libro con el nombre de Filtros. Usando la opcin Autofiltro ordenar los datos de mayor a menor por

    Nmero de Hijos. Usando la opcin Autofiltro ordenar los datos de menor a mayor por

    Bonificacin. Usando Filtros avanzados: Filtrar todos los vendedores. Filtrar todos los que no tienen hijos. Filtrar a los gerentes con hijos. Filtrar a los que ganan mas de S/. 1,000 o menos de S/. 800. Filtrar a los Tcnicos con hijos o a los Vendedores cuya bonificacin es

    mas de S/. 80. Filtrar a los que apellidan rojas y no tienen hijos o a las Secretarias

    cuyo sueldo neto es menos que S/. 900. Filtrar a los que no tienen asignacin familiar y el descuento por AFP es

    mas de S/. 70.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 15 Nivel: Avanzado

    Sesin 2

    EXCEL FINANCIERO

    Al finalizar la Sesin usted ser capaz de:

    Trabaja eficientemente con una hoja de Clculo.

    Identifica y aplica adecuadamente los distintos formatos de celdas.

    Usa correctamente las referencias relativas y absolutas.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 16 Nivel: Avanzado

    FUNCIONES FINANCIERAS FUNCIN PAGO Objetivo: Calcula el pago de un prstamo basndose en pagos constantes y en una tasa de inters constante. Estructura: = PAGO(tasa, nper, va, [vf], [tipo]) Tasa: Es el tipo de inters del prstamo. Nper: Es el nmero total de pagos del prstamo. Va: Es el valor actual, o la cantidad total de una serie de futuros pagos.

    Tambin se conoce como valor burstil. 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, Indica cundo vencen los pagos.

    0 u omitido Al final del perodo 1 Al inicio del perodo

    NOTA: El pago devuelto por PAGO incluye el capital y el inters, pero

    no incluye impuestos, pagos en reserva ni los gastos que algunas veces se asocian con los prstamos.

    Categora: Financieras Pasos:

    1. Seleccionar la celda donde se mostrar el resultado.

    2. Buscar la Frmula

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 17 Nivel: Avanzado

    Tambin puede usar la ficha Frmulas.

    Categora Financieras

    Seleccionar la funcin PAGO

    Seleccionar las celdas a evaluar

    Categora Financieras

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 18 Nivel: Avanzado

    El resultado sera:

    El resultado sale con signo negativo debido a que se trata de un pago a realizarse. Para visualizar el resultado con signo positivo se debe agregar la funcin de valor absoluto a la funcin pago para que se muestre solo el valor numrico del resultado.

    Obteniendo como resultado: Se debe mantener 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, use 12%/12 para tasa y 4*12 para nper. Si realiza pagos anuales del mismo prstamo, use 12% para tasa y 4 para nper.

    Frmula insertada

    Resultado de la operacin

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 19 Nivel: Avanzado

    Cunto se pagara mensualmente? Se deben expresar los datos en meses.

    Cul es el pago total por el prstamo? Cunto se paga de Intereses?

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 20 Nivel: Avanzado

    FUNCIN PAGOINT Objetivo: Devuelve el inters pagado en un perodo especfico por una inversin basndose en pagos peridicos constantes y en una tasa de inters constante. Estructura: = PAGOINT(tasa, perodo, nper, va, [vf], [tipo]) Perodo: Es el perodo para el que se desea calcular el inters y que

    debe estar entre 1 y el argumento nper. Categora: Financieras Pasos:

    1. Seleccionar la celda donde se mostrar el resultado.

    Categora Financieras

    Seleccionar la funcin PAGOINT

    2. Buscar la Frmula

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 21 Nivel: Avanzado

    Tambin puede usar la ficha Frmulas. Expresamos los pagos en Meses Usando la funcin ABS, el resultado sera:

    Frmula insertada

    Resultado de la operacin

    Seleccionar las celdas a evaluar

    Categora Financieras

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 22 Nivel: Avanzado

    FUNCIN PAGOPRIN Objetivo: 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 Estructura: = PAGOPRIN(tasa, perodo, nper, va, [vf], [tipo]) Perodo: Especifica el perodo, que debe estar entre 1 y el valor de

    nper. Categora: Financieras Pasos:

    1. Seleccionar la celda donde se mostrar el resultado.

    Categora Financieras

    Seleccionar la funcin PAGOPRIN

    2. Buscar la Frmula

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 23 Nivel: Avanzado

    Tambin puede usar la ficha Frmulas. Expresamos los pagos en Meses Usando la funcin ABS, el resultado sera: Pago del principal para el 5to. mes del prstamo.

    Resultado de la operacin

    Seleccionar las celdas a evaluar

    Categora Financieras

    Frmula insertada

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 24 Nivel: Avanzado

    FUNCIN VALOR ACTUAL NETO (VNA) Objetivo: Calcula el Valor Neto Presente de una inversin a partir de una tasa de descuento y una serie de pagos futuros (valores negativos) e ingresos (valores positivos). Estructura: = VNA(tasa,valor1,valor2, ...) Tasa: Tasa de descuento a lo largo de un perodo. Valor1, valor2, ...: Son de 1 a 254 argumentos que representan los

    pagos e ingresos. Deben tener la misma duracin y ocurrir al final de cada perodo.

    VNA usa el orden de valor1; valor2; ... para interpretar el orden de los flujos de caja. Asegrese de escribir los valores de los pagos y de los ingresos en el orden adecuado.

    La inversin VNA comienza un perodo antes de la fecha del flujo de caja de valor1 y termina con el ltimo flujo de caja de la lista. El clculo VNA se basa en flujos de caja futuros. Si el primer flujo de caja se produce al principio del primer perodo, el primer valor se debe agregar al resultado VNA, que no se incluye en los argumentos valores. Para obtener ms informacin, vea los siguientes ejemplos.

    Si n es el nmero de flujos de caja de la lista de valores, la frmula de VNA es:

    VNA es similar a la funcin VA (valor actual). La principal diferencia

    entre VA y VNA es que VA permite que los flujos de caja comiencen al final o al principio del perodo. A diferencia de los valores variables de flujos de caja en VNA, los flujos de caja en VA deben permanecer constantes durante la inversin. Para obtener ms informacin acerca de anualidades y funciones financieras, vea VA.

    Categora: Financieras Pasos:

    1. Seleccionar la celda donde se mostrar el resultado.

    2. Buscar la Frmula

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 25 Nivel: Avanzado

    Tambin puede usar la ficha Frmulas.

    Seleccionar la funcin VNA

    Seleccionar las celdas a evaluar

    Categora Financieras

    Categora Financieras

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 26 Nivel: Avanzado

    El resultado sera: Al resultado obtenido le sumamos la inversin inicial y nos obtendremos

    el resultado real del Valor Actual Neto de la Inversin.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 27 Nivel: Avanzado

    FUNCIN TIR Objetivo: Devuelve la tasa interna de retorno de los flujos de caja representados por los nmeros del argumento valores. Estos flujos de caja no tienen por qu ser constantes, como es el caso en una anualidad. Sin embargo, los flujos de caja deben ocurrir en intervalos regulares, como meses o aos. La tasa interna de retorno equivale a la tasa de inters producida por un proyecto de inversin con pagos (valores negativos) e ingresos (valores positivos) que se producen en perodos regulares. Estructura: = TIR(valores, [estimar]) Valores: Es una matriz o una referencia a celdas que contienen los

    nmeros para los cuales desea calcular la tasa interna de retorno. El argumento valores debe contener al menos un valor positivo y uno

    negativo para calcular la tasa interna de retorno. TIR interpreta el orden de los flujos de caja siguiendo el orden del

    argumento valores. Asegrese de escribir los valores de los pagos e ingresos en el orden correcto.

    Si un argumento matricial o de referencia contiene texto, valores lgicos o celdas vacas, esos valores se pasan por alto.

    Estimar es un nmero que el usuario estima que se aproximar al resultado de TIR.

    Microsoft Excel utiliza una tcnica iterativa para el clculo de TIR. Comenzando con el argumento estimar, TIR reitera el clculo hasta que el resultado obtenido tenga una exactitud de 0,00001%. Si TIR no llega a un resultado despus de 20 intentos, devuelve el valor de error #NUM!

    En la mayora de los casos no necesita proporcionar el argumento estimar para el clculo de TIR. Si se omite el argumento estimar, se supondr que es 0,1 (10%).

    Categora: Financieras Pasos:

    1. Seleccionar la celda donde se mostrar el resultado.

    2. Buscar la Frmula

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 28 Nivel: Avanzado

    Tambin puede usar la ficha Frmulas. Expresamos los pagos en Meses

    Seleccionar la funcin TIR

    Seleccionar las celdas a evaluar

    Categora Financieras

    Categora Financieras

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 29 Nivel: Avanzado

    El resultado sera:

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 30 Nivel: Avanzado

    PRCTICA GUIADA

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 31 Nivel: Avanzado

    Sesin 3

    BASE DE DATOS I

    Al finalizar la Sesin usted ser capaz de:

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 32 Nivel: Avanzado

    FUNCIONES BASE DE DATOS Una base de datos se puede definir como un conjunto de informacin homognea que mantiene una estructura ordenada, y que toda ella se encuentra relacionada con un mismo tema. Est compuesta por Columnas (Campos) y por Filas (Registros). CREAR BASE DE DATOS Seleccionar todos los datos que formarn la Base de Datos.

    En la Ficha Frmulas, seleccionar la opcin asignar nombre a un rango: En el ejemplo, la Base de Datos se llama Productos, y tiene 10

    Campos y 15 Registros.

    Crear una Base de Datos

    Ingresar el Nombre de la Base de Datos

    Campos

    Reg

    istr

    os

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 33 Nivel: Avanzado

    FUNCIN BDCONTARA Objetivo: Cuenta las celdas no vacas dentro de una columna (campo) que cumple con ciertas condiciones o criterios. Estructura: BDCONTARA(base_de_datos, nombre_de_campo, criterios) Donde: Base de Datos: Es el nombre de la base de datos. Nombre de Campo: Es el nombre campo que se desea contar. Criterios: Son las condiciones que deben cumplir los datos. Se deben ingresar en una tabla aparte, la cual lleva como encabezado el nombre del Campo que se desea Evaluar junto a la condicin respectiva. Ejercicio:

    1. Crear Cuadro de Criterios.

    2. Seleccionar la celda donde se el resultado

    Categora Base de Datos

    Seleccionar la funcin BDCONTARA

    3. Buscar la Frmula

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 34 Nivel: Avanzado

    El resultado sera:

    Frmula insertada

    Resultado de la operacin

    Base de Datos: Productos

    Nombre del Campo a Evaluar

    Celdas que contienen los criterios

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 35 Nivel: Avanzado

    FUNCIN BDCONTAR Objetivo: Cuenta las celdas con contenido numrico dentro de una columna (campo) que cumple con ciertas condiciones o criterios. Estructura: BDCONTAR(base_de_datos, nombre_de_campo, criterios) Donde: Base de Datos: Es el nombre de la base de datos. Nombre de Campo: Es el nombre campo que se desea contar. Criterios: Son las condiciones que deben cumplir los datos. Se deben ingresar en una tabla aparte, la cual lleva como encabezado el nombre del Campo que se desea Evaluar junto a la condicin respectiva. Ejercicio:

    1. Crear Cuadro de Criterios.

    2. Seleccionar la celda donde se el resultado

    Categora Base de Datos

    Seleccionar la funcin BDCONTAR

    3. Buscar la Frmula

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 36 Nivel: Avanzado

    El resultado sera:

    Frmula insertada

    Resultado de la operacin

    Base de Datos: Productos

    Nombre del Campo a Evaluar

    Celdas que contienen los criterios

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 37 Nivel: Avanzado

    FUNCIN BDMAX Objetivo: Devuelve el valor mximo de un campo especificado. Estructura: BDMAX(base_de_datos, nombre_de_campo, criterios) Donde: Base de Datos: Es el nombre de la base de datos. Nombre de Campo: Es el nombre campo que se desea contar. Criterios: Son las condiciones que deben cumplir los datos. Se deben ingresar en una tabla aparte, la cual lleva como encabezado el nombre del Campo que se desea Evaluar junto a la condicin respectiva. Ejercicio:

    1. Crear Cuadro de Criterios.

    2. Seleccionar la celda donde se el resultado

    Categora Base de Datos

    Seleccionar la funcin BDMAX

    3. Buscar la Frmula

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 38 Nivel: Avanzado

    El resultado sera:

    Frmula insertada

    Resultado de la operacin

    Base de Datos: Productos

    Nombre del Campo a Evaluar

    Celdas que contienen los criterios

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 39 Nivel: Avanzado

    FUNCIN BDMIN Objetivo: Devuelve el valor mnimo de un campo especificado. Estructura: BDMIN(base_de_datos, nombre_de_campo, criterios) Donde: Base de Datos: Es el nombre de la base de datos. Nombre de Campo: Es el nombre campo que se desea contar. Criterios: Son las condiciones que deben cumplir los datos. Se deben ingresar en una tabla aparte, la cual lleva como encabezado el nombre del Campo que se desea Evaluar junto a la condicin respectiva. Ejercicio:

    1. Crear Cuadro de Criterios. 2. Seleccionar la celda

    donde se el resultado

    Categora Base de Datos

    Seleccionar la funcin BDMIN

    3. Buscar la Frmula

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 40 Nivel: Avanzado

    El resultado sera:

    Frmula insertada

    Resultado de la operacin

    Base de Datos: Productos

    Nombre del Campo a Evaluar

    Celdas que contienen los criterios

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 41 Nivel: Avanzado

    FUNCIN BDPRODUCTO Objetivo: Devuelve la multiplicacin de los Datos de un campo especificado. Estructura: BDPRODUCTO(base_de_datos, nombre_de_campo, criterios) Donde: Base de Datos: Es el nombre de la base de datos. Nombre de Campo: Es el nombre campo que se desea contar. Criterios: Son las condiciones que deben cumplir los datos. Se deben ingresar en una tabla aparte, la cual lleva como encabezado el nombre del Campo que se desea Evaluar junto a la condicin respectiva. Ejercicio: En este caso se tienen que evaluar que se cumplan una de las dos condiciones: que sea Pera O que sea Papel.

    1. Crear Cuadro de Criterios.

    2. Seleccionar la celda donde se el resultado

    Categora Base de Datos

    Seleccionar la funcin BDPRODUCTO

    3. Buscar la Frmula

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 42 Nivel: Avanzado

    El resultado sera:

    Frmula insertada

    Resultado de la operacin

    Base de Datos: Productos

    Nombre del Campo a Evaluar

    Celdas que contienen los criterios

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 43 Nivel: Avanzado

    FUNCIN BDPROMEDIO Objetivo: Devuelve el Promedio de los Datos de un campo especificado. Estructura: BDPROMEDIO(base_de_datos, nombre_de_campo, criterios) Donde: Base de Datos: Es el nombre de la base de datos. Nombre de Campo: Es el nombre campo que se desea contar. Criterios: Son las condiciones que deben cumplir los datos. Se deben ingresar en una tabla aparte, la cual lleva como encabezado el nombre del Campo que se desea Evaluar junto a la condicin respectiva. Ejercicio: En este caso se tienen que evaluar que se cumplan dos condiciones: que sea Fruta Y que su Ganancia sea mayor que 40.

    1. Crear Cuadro de Criterios.

    2. Seleccionar la celda donde se el resultado

    Categora Base de Datos

    Seleccionar la funcin BDPROMEDIO

    3. Buscar la Frmula

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 44 Nivel: Avanzado

    El resultado sera:

    Frmula insertada

    Resultado de la operacin

    Base de Datos: Productos

    Nombre del Campo a Evaluar

    Celdas que contienen los criterios

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 45 Nivel: Avanzado

    FUNCIN BDSUMA Objetivo: Devuelve la suma de los Datos de un campo especificado. Estructura: BDSUMA(base_de_datos, nombre_de_campo, criterios) Donde: Base de Datos: Es el nombre de la base de datos. Nombre de Campo: Es el nombre campo que se desea contar. Criterios: Son las condiciones que deben cumplir los datos. Se deben ingresar en una tabla aparte, la cual lleva como encabezado el nombre del Campo que se desea Evaluar junto a la condicin respectiva. Ejercicio: En este caso se tienen que evaluar que se cumplan las condiciones: que sea Golosina y que cueste menos de 150 o Abarrotes y que cueste ms de 200.

    1. Crear Cuadro de Criterios.

    2. Seleccionar la celda donde se el resultado

    Categora Base de Datos

    Seleccionar la funcin BDSUMA

    3. Buscar la Frmula

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 46 Nivel: Avanzado

    El resultado sera:

    Frmula insertada

    Resultado de la operacin

    Base de Datos: Productos

    Nombre del Campo a Evaluar

    Celdas que contienen los criterios

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 47 Nivel: Avanzado

    PRCTICA GUIADA Copie y complete la siguiente tabla.

    Comisin: 4.8% del Monto Vendido SueldoFinal: SueldoBase + Comisin.

    Convierta la Tabla en una Base de Datos con el nombre Ventas. Responda: Cuntos Contadores ganan entre 1000 y 1100? Cuntos empleados con nombre Rosa han realizado ventas superiores a

    15000? Cul es sueldo total de los jefes? Cuntos empleados que apellidan Prez tienen sueldo mayor a 1500? Cul es el sueldo promedio de todos los empleados? Cul es la venta mxima de aquellos empleados que apellidan

    Bocanegra? Cul es la venta mnima los empleados cuyo sueldo es mayor 950? Grabe el libro en su carpeta de Trabajo con el nombre de Base de Datos.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 48 Nivel: Avanzado

    Sesin 4

    BASE DE DATOS II

    Al finalizar la Sesin usted ser capaz de:

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 49 Nivel: Avanzado

    APLICACIN DE LAS BASE DE DATOS CONSULTA DE BASE DE DATOS Se trata de Insertar datos existentes en una base de datos, hacia una hoja de clculo nueva, se pueden insertar todos los datos, o slo aquellos que cumplan con determinada condicin o Criterio.

    Seleccionar Importar Datos

    Elegir el origen de datos, en este caso Excel.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 50 Nivel: Avanzado

    Elegir el archivo que contiene la Base de Datos

    Seleccionar los Datos que se desean consultar

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 51 Nivel: Avanzado

    En este ejemplo se filtraran solo los productos de Tipo abarrotes. Si se desean todos los datos, no se colocar ningn filtro.

    Seleccionar el campo por el que se desea ordenar los datos

    Seleccionar la celda a donde se copiaran

    los datos

    Resultado de la Consulta a la Base de Datos

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 52 Nivel: Avanzado

    OBTENER DATOS EXTERNOS IMPORTAR DESDE ACCESS Permite insertar en Excel datos de una Base de Datos hecha en Access. Seleccionar Ficha Datos, Obtener Datos externos, luego seleccionar

    Desde Access: Se mostrarn todas las tablas existentes en la Base de Datos. Seleccionar y abrir el archivo de la Base de Datos en Access

    Seleccionar la celda a donde se importarn los Datos.

    Seleccionar Obtener Datos Externos

    Desde Access

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 53 Nivel: Avanzado

    El resultado sera todos los datos existentes en la Tabla en Access copiados en una Hoja de Clculo en Excel:

    IMPORTAR DESDE WEB Permite insertar en Excel datos de alguna tabla mostrada en una Pgina Web. Luego seleccionar Desde Web, tal como se muestra en la siguiente

    Figura:

    Seleccionar Obtener Datos Externos

    Desde Web

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 54 Nivel: Avanzado

    Escribir la Pgina Web que se desea importar. Marcar con un aspa la tabla que se desea importar:

    Seleccionar la celda a donde se importarn los Datos.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 55 Nivel: Avanzado

    El resultado sera todos los datos existentes en la Pgina Web, copiados en una Hoja de Clculo en Excel:

    IMPORTAR DESDE TEXTO Permite insertar en Excel datos de alguna tabla mostrada en una Pgina Web. Luego seleccionar Desde Texto:

    Seleccionar el Archivo que se desea Importar:

    Seleccionar Obtener Datos Externos

    Desde Texto

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 56 Nivel: Avanzado

    Se iniciarn los 3 pasos del Asistente de Importacin: Seleccionar Tipo de Datos.

    Personalizar el ancho da las columnas importadas.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 57 Nivel: Avanzado

    Seleccionar el tipo de datos de cada columna. Seleccionar la celda a donde se importarn los Datos.

    El resultado sera todos los datos existentes en el archivo de Texto,

    copiados en una Hoja de Clculo en Excel:

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 58 Nivel: Avanzado

    PRCTICA GUIADA Copie y complete la siguiente tabla.

    Comisin: 4.8% del Monto Vendido SueldoFinal: SueldoBase + Comisin.

    Convierta la Tabla en una Base de Datos con el nombre Ventas.

    En un nuevo Libro, importe: Todos los datos de los Contadores. Todos los datos de los que tienen Comisin menor a 3000. Todos los datos de los que ganan menos de 1500. Grabe el libro en su carpeta de Trabajo con el nombre Datos

    Importados.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 59 Nivel: Avanzado

    Sesin 5

    TABLAS DINMICAS

    Al finalizar la Sesin usted ser capaz de:

    Ejecutar Aplicaciones

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 60 Nivel: Avanzado

    TABLAS DINMICAS INGRESO Y ANLISIS DE DATOS Un informe de tabla dinmica es una tabla interactiva que combina y compara rpidamente grandes volmenes de datos. Podr girar las filas y las columnas para ver diferentes resmenes de los datos de origen, y mostrar los detalles de determinadas reas de inters. Se utiliza un informe de tabla dinmica cuando se desea comparar totales relacionados, sobre todo si tiene una lista larga de nmeros para resumir y desea realizar comparaciones distintas con cada nmero. En los informes de tabla dinmica, cada columna o campo de los datos de origen se convierte en un campo de tabla dinmica que resume varias filas de informacin. DISEO DE LA TABLA DINMICA En la Ficha Insertar, seleccionar Tabla Dinmica.

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

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 61 Nivel: Avanzado

    Disear la Tabla Dinmica En la opcin Valores se colocan los Datos numricos centrales del

    Anlisis En Rtulos de Fila y/o Columna, se ingresan los criterios de Anlisis

    (Tipo de Producto, producto, Marca, Color, etc.)

    Clasificacin de los Datos

    Datos Numricos

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 62 Nivel: Avanzado

    Por defecto la operacin usada es la SUMA. Para modificar el Criterio del Anlisis de los Datos, se puede seleccionar otra operacin aritmtica. Seleccionar el campo respectivo y se mostrar el men contextual siguiente:

    Seleccionar la operacin respectiva. Se puede cambiar tambin el texto

    del campo.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 63 Nivel: Avanzado

    La Tabla Dinmica Final sera: GENERAR GRFICO DINMICO A partir de cada Tabla Dinmica, se puede generar un Grfico Dinmico, con solo seleccionar la opcin Grfico Dinmico de la Ficha Herramientas de Tablas Dinmicas. Seleccionar el tipo de Grfico.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 64 Nivel: Avanzado

    Y se mostrar automticamente el Grfico Dinmico respectivo.

    Se pueden hacer consultas a los Grficos Dinmicos, para mostrar u ocultar algunos datos; estas consultas tambin afectarn a la Tabla correspondiente.

    Seleccionar la

    Categora

    Seleccionar los Datos a mostrar Slo se

    mostrarn Frutas y tiles

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 65 Nivel: Avanzado

    Y el grfico y la tabla a mostrarse se modificarn de la siguiente manera:

    Slo se muestran

    Frutas y tiles

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 66 Nivel: Avanzado

    PRCTICA GUIADA Copie los datos de la siguiente tabla:

    Crear una Tabla Dinmica que permita analizar los ingresos de todas

    las AFP por Semanas, de acuerdo a los siguientes requerimientos: Organizar los datos por semanas. Para Integra Mostrar el Mximo Ingreso. Para Prima Mostrar la Suma total. Para Unin mostrar el Promedio de los ingresos. Para ProFuturo mostrar el mnimo ingreso.

    Crear un Grfico Dinmico que muestre los aportes de los Meses de Enero, Marzo y Junio.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 67 Nivel: Avanzado

    Copiar las siguientes tablas: Usando tabla de Datos de una entrada, evaluar que pasara con el

    saldo, si el alquiler cambiara a S/. 1,000 y S/. 1,800. Usando Tabla de Datos de doble entrada, evaluar que pasara con el

    saldo. Si el alquiler cambiara a S/. 1,600 y 1,800, y los Servicios cambiaran a S/. 1,200 y S/. 1,450.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 68 Nivel: Avanzado

    Sesin 6

    ANLISIS DE DATOS

    Al finalizar la Sesin usted ser capaz de:

    Realiza satisfactoriamente anlisis a los datos ingresados en las hojas de

    clculo.

    Usa correctamente tablas dinmicas para anlisis de datos.

    Simula eficazmente cambios de escenarios de problemas planteados.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 69 Nivel: Avanzado

    ANLISIS DE DATOS ESCENARIOS Conjunto de valores de entrada, al que se ha asignado un nombre, que puede sustituirse en un modelo de hoja de clculo. Ejercicio:

    Desarrollo: Calcular el Valor Hora.

    Seleccionar la opcin Administrador de Escenarios, de la ficha datos:

    Agregar los escenarios respectivos.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 70 Nivel: Avanzado

    Definir las opciones del Escenario:

    Definir un escenario con los datos iniciales, para preservar los valores

    originales de las celdas que se cambiarn. Agregar uno a uno los dems escenarios e ingresar los valores

    planteados en el ejercicio.

    Nombre del Escenario

    Celdas Cambiantes

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 71 Nivel: Avanzado

    Valores para Empresa1: Una vez agregados todos los escenarios, se mostrar la siguiente

    ventana. Seleccionar Resumen. Seleccionar la celda Resultado, que es la que se analizar en el

    ejercicio. Se mostrar una Nueva Hoja conteniendo el Resumen del Escenario

    que nos permitir comparar los valores de la Celda de Resultado.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 72 Nivel: Avanzado

    Para MODIFICAR un escenario: Para ELIMINAR un escenario:

    SUBTOTALES Puede calcular automticamente subtotales y totales generales en una lista de datos o calcular subtotales de una columna utilizando el comando Subtotal del grupo Esquema de la ficha Datos; en base a una funcin de resumen (Sumar, Contar, Promedio). Ejemplo: Dada la siguiente tabla de Datos, calcular el Promedio de Ventas por

    Tipo de Producto.

    Seleccionar Escenario

    Clic en Modificar e ingresar los nuevos valores

    Seleccionar Escenario

    Clic en Eliminar

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 73 Nivel: Avanzado

    Solucin: Para poder calcular el subtotal por Tipo de Producto, debemos de tener

    ordenados los datos por este criterio. El resultado de los datos ordenados por Tipo de Producto sera:

    Seleccionar la opcin Subtotal de la Ficha Datos:

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 74 Nivel: Avanzado

    Ingresar las consideraciones del Ejercicio: El resultado del SubTotal sera:

    TABLAS DE DATOS Las tablas de datos forman parte de una serie de comandos a veces denominados herramientas de anlisis Y si. Es un rango de celdas que muestra cmo afecta el cambio de algunos valores de las frmulas a los resultados de las mismas. Constituyen un mtodo abreviado para calcular varias versiones en una sola operacin, as como una manera de ver y comparar los resultados de todas las variaciones distintas en la hoja de clculo.

    Criterio del SubTotal

    Funcin a Utilizar

    Campo a Analizar

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 75 Nivel: Avanzado

    Existen dos tipos de tablas de datos: tablas de una variable y tablas de dos variables. TABLAS DE DATOS DE UNA VARIABLE Los valores de entrada aparezcan en una columna (orientacin por

    columnas) o en una fila (orientacin por filas). Las frmulas que se utilicen en la tabla de una variable debern hacer

    referencia a una celda de entrada (celda en la que se sustituye cada valor de entrada de una tabla de datos).

    Escriba la lista de valores que desea sustituir en la celda de entrada debajo de una columna o en una fila.

    Ejercicio: Se desea saber qu pasara con la Utilidad si el costo de produccin varia a S/.30000, S/. 25000 y S/. 60000. Platear el Problema y calcular la Utilidad

    Ingresar los valores cambiantes

    Utilidad: Venta Neta Total Gastos

    Cambios en el costo de Produccin

    Dato a

    Reemplazarse

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 76 Nivel: Avanzado

    Seleccionar el rango de datos a analizar Seleccionar la opcin Tabla de Datos de la Ficha Datos:

    Si la tabla de datos est orientada por columnas, escriba la referencia

    de la celda de entrada en el cuadro Celda de entrada (columna). Si la tabla de datos est orientada por filas, escriba la referencia de

    celda de la celda de entrada en el cuadro Celda de entrada (fila). En nuestro ejemplo, los datos estn ordenados en Columnas, pues en

    una columna se muestran los Costos de Produccin cambiados y en otra columna se desea mostrar las utilidades respectivas. Por lo tanto para nuestro caso debemos ingresar la Celda de Entrada (el costo de produccin inicial) en Columna.

    Incluir celda que contiene el valor de

    las utilidades.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 77 Nivel: Avanzado

    La tabla llena con los nuevos datos de utilidades sera: TABLAS DE DATOS DE DOS VARIABLES Las tablas de datos de dos variables solamente utilizan una con dos

    listas de valores de entrada. La frmula deber hacer referencia a dos celdas de entrada la que se

    sustituye cada valor de entrada de una tabla de datos. Inserte una lista de la segunda variable a lado de la frmula.

    Ejercicio: Ejemplo: Al ejemplo anterior agregarle la variable de deudas con los cambios de 10,000 y 8,000. Seleccione el rango de celdas que contenga la frmula y los valores de fila y de columna. Ingresar los valores cambiantes

    Seleccionar el rango de datos a analizar

    Cambios en el costo de Produccin

    Incluir celda que contiene el valor de

    las utilidades.

    Cambios en las Deudas

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 78 Nivel: Avanzado

    Seleccionar la opcin Tabla de Datos de la Ficha Datos: Ingresar las referencias de las Celdas.

    La tabla llena con los nuevos datos de utilidades sera:

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 79 Nivel: Avanzado

    BUSCAR OBJETIVO Permite determinar el valor de una celda o rango de celdas, de tal manera que se llegue a cumplir un objetivo. Ejemplo: Qu valor debe tener las ventas y los costos de produccin, para que mi utilidad sea de S/. 60,000? Plantear los Datos

    Seleccionar la Funcin Buscar Objetivo de la Ficha Datos:

    Ingresar el valor Objetivo.

    Datos que se busca cambiar

    Valor a Ajustar

    Valor a Ajustar

    Celda Objetivo

    Valor Objetivo

    Celdas Cambiantes

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 80 Nivel: Avanzado

    El resultado sera:

    Nuevo Valor para Deudas

    Nuevo Valor para Costo

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 81 Nivel: Avanzado

    PRCTICA GUIADA Copiar las siguientes tablas:

    Usando tabla de Datos de una entrada, evaluar que pasara con el

    saldo, si el alquiler cambiara a S/. 1,000 y S/. 1,800. Usando Tabla de Datos de doble entrada, evaluar que pasara con el

    saldo. Si el alquiler cambiara a S/. 1,600 y 1,800, y los Servicios cambiaran a S/. 1,200 y S/. 1,450.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 82 Nivel: Avanzado

    Sesin 7

    OPTIMIZACIN

    Al finalizar la Sesin usted ser capaz de:

    Utiliza correctamente la funcin Solver para ajustes de gastos y

    maximizacin de ganancias.

    Elabora reportes de comparacin de ingresos y egresos para solucionar

    problemas de inversiones.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 83 Nivel: Avanzado

    OPTIMIZAR DATOS EN EXCEL Un problema de optimizacin consiste en encontrar aquellos valores de ciertas variables que optimizan (es decir, hacen mxima o mnima, segn el caso), una funcin de estas variables. A las variables las llamaremos variables controlables o variables de decisin. Matemticamente, significa encontrar los valores de x1, x2,..., xn, tales que hacen mxima (o mnima) a la funcin f (x1, x2,..., xn). El mtodo ms conocido para encontrar el ptimo de una funcin es a travs del anlisis de sus derivadas. Este mtodo tiene dos limitaciones: no siempre la funcin es derivable, y, adems, no siempre el ptimo nos da una solucin que tenga sentido en la prctica. Debido a la primera limitacin, surgieron los mtodos numricos, que parten de una solucin inicial, y mediante algn algoritmo iterativo, mejoran sucesivamente la solucin. Debido a la segunda limitacin, surgieron los mtodos de optimizacin restringida. El nombre se debe a que podemos ponerle restricciones a las variables, de modo que cumplan una o ms condiciones. La restriccin ms comn que se da en la prctica es que las variables deben ser no negativas. No tiene ningn sentido una "solucin" que implique producir cantidades negativas, o sembrar un nmero negativo de hectreas, o llevar un nmero negativo de paquetes, por ejemplo. Pero, adems, surgen naturalmente otras restricciones en el mundo real, debido a limitaciones de horas de trabajo, capital, tiempo, insumos, o a que quizs deseamos imponer ciertos mnimos o mximos de calidad, riesgo, etc.. Estas restricciones pueden ser funciones de las variables controlables. Podramos resumir diciendo que en un problema de optimizacin restringida buscamos los valores de ciertas variables que optimizan una funcin objetivo, sujetas a restricciones, dadas tambin en trminos de funciones. Matemticamente, significa encontrar los valores de x1, x2, ..., xn, tales que hacen mxima (o mnima) a f (x1, x2, ..., xn), sujeto a restricciones de tipo gj (x1, x2, ..., xn) , = cj , donde cj es una constante.

    No

    S Solucin Inicial

    Es ptima? Fin

    Nueva Solucin

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 84 Nivel: Avanzado

    FUNCIN SOLVER Solver es una herramienta para resolver y optimizar ecuaciones mediante el uso de mtodos numricos. Con Solver, se puede buscar el valor ptimo para una celda, denominada celda objetivo, en donde se escribe la frmula de la funcin objetivo f (x1, x2, ..., xn). Solver cambia los valores de un grupo de celdas, denominadas celdas cambiantes, y que estn relacionadas, directa o indirectamente, con la frmula de la celda objetivo. En estas celdas se encuentran los valores de las variables controlables x1, x2, , xn. Puede agregar restricciones a Solver, escribiendo una frmula gj (x1, x2, ..., xn) en una celda, y especificando que la celda deber ser mayor o igual, igual, o menor o igual que otra celda que contiene la constante cj. Tambin puede especificar que los valores sean enteros, para evitar dar resultados absurdos de algunos problemas, tales como que se necesitan 3,5 empleados. Solver ajustar los valores de las celdas cambiantes, para generar el resultado especificado en la frmula de la celda objetivo. Instalar Solver En el men Herramientas, fjese si aparece el comando Solver. Si no aparece, deber instalar el complemento o macro automtica Solver.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 85 Nivel: Avanzado

    En la ficha Datos aparecer la siguiente opcin: DEFINIR Y RESOLVER UN PROBLEMA Puede utilizar Solver para determinar el valor mximo de una celda cambiando el valor de otra. Las dos celdas deben estar relacionadas por medio de las frmulas de la hoja de clculo. Si no es as, al cambiar el valor de una celda no cambiar el valor de la otra celda. Ejemplo: Llenar los datos y completar la tabla segn las

    consideraciones siguientes:

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 86 Nivel: Avanzado

    Datos: Se Obtienen los siguientes resultados:

    Ejercicio: Se desea saber cunto es necesario gastar en publicidad para generar el mximo beneficio en el primer trimestre. El objetivo es MAXIMIZAR el beneficio cambiando los gastos en publicidad.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 87 Nivel: Avanzado

    Solucin: Seleccionar la opcin Solver de la Ficha Datos. En el cuadro Celda objetivo, seleccione la celda B15. Seleccione la opcin Mximo. En el cuadro Cambiando las celdas, seleccione la celda B11.

    Haga clic en Resolver. Aparecer un mensaje advirtiendo que Solver ha encontrado una solucin.

    Haga clic en Utilizar la solucin de Solver y, a continuacin, haga clic en Aceptar para mantener los resultados que se muestran en la pantalla.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 88 Nivel: Avanzado

    Interpretacin: El resultado es que un gasto en publicidad en T1 de S/. 17,093.06 produce un beneficio mximo de S/. 15,093.06 El margen de beneficio, sin embargo, ha disminuido.

    CREAR INFORMES Al utilizar la herramienta Solver, tambin se pueden generar informes de la solucin encontrada para el caso planteado. Antes de aceptar la solucin se debe dar clic en la opcin Informe de

    Respuestas. Se generar una nueva hoja de Clculo mostrando la solucin

    encontrada.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 89 Nivel: Avanzado

    Ejercicio 02: Optimizar una Funcin de Varias Variables Tambin puede utilizar Solver para encontrar los valores que deben tomar varias celdas a la vez para maximizar o minimizar otra celda que tenga una frmula que dependa de ellas. Utilizando los datos del ejercicio anterior, averige cul es el presupuesto publicitario de cada trimestre que produce el mayor beneficio durante el ao. Debido a que el factor de temporada en la fila 3 se tiene en cuenta en el clculo de la unidad de ventas en la fila 5 como multiplicador, parece lgico que se gaste ms del presupuesto publicitario en el trimestre T4 cuando la respuesta a las ventas es mayor, y menos en el T3 cuando la respuesta a las ventas es menor. Solucin: Seleccionar la opcin Solver de la Ficha Datos. Clic en Restablecer todo, para anular la solucin anterior y recuperar

    los datos originales. Confirmar la accin:

    En el cuadro Celda objetivo, escriba f15 o seleccione la celda F15

    (beneficios totales del ao) en la hoja de clculo. Asegrese de que la opcin Mximo est seleccionada. En el cuadro Cambiando las celdas, escriba b11:e11 o seleccione las

    celdas B11:E11 (el presupuesto publicitario de cada uno de los cuatro trimestres) en la hoja de clculo.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 90 Nivel: Avanzado

    Haga clic en Resolver. Haga clic en Utilizar la solucin de Solver y, a

    continuacin, haga clic en Aceptar.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 91 Nivel: Avanzado

    Acaba de solicitar a Solver que resuelva un problema de optimizacin no lineal moderadamente complejo, es decir, debe encontrar los valores para las incgnitas en las celdas de B11 a E11 que maximiza el beneficio anual. Se trata de un problema no lineal debido a los exponentes utilizados en las frmulas de la fila 5.

    El resultado de esta optimizacin sin restricciones muestra que se

    pueden aumentar los beneficios durante el ao a S/. 79,705.62 si se gastan S/. 89,705.59 en publicidad durante el ao, de la siguiente manera:

    ADMINISTRAR RESTRICCIONES Hasta ahora, el presupuesto recupera el costo publicitario y genera beneficios adicionales, pero se est alcanzado un estado de disminucin de flujo de caja. Debido a que nunca es seguro que el modelo de ventas y publicidad vaya a ser vlido para el prximo ao (de forma especial a niveles de gasto mayores), no parece prudente dotar a la publicidad de un gasto no restringido. Ejercicio 03: Agregar Restricciones Supongamos que desea mantener el presupuesto original de publicidad

    en S/. 40.000. Agregue al problema una restriccin que limita la cantidad en publicidad durante los cuatro trimestres a S/. 40.000.

    Aparecer el cuadro de dilogo Agregar restriccin.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 92 Nivel: Avanzado

    En el cuadro Referencia de celda, escriba f11 o seleccione la celda F11 (total en publicidad) en la hoja de clculo.

    La celda F11 debe ser menor o igual a S/. 40.000. La relacin en el

    cuadro Restriccin es

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 93 Nivel: Avanzado

    Haga clic en Utilizar la solucin de Solver y, luego clic en Aceptar para mantener los resultados que se muestran en la pantalla.

    La solucin encontrada por Solver realiza una redistribucin del

    presupuesto original de S/. 40,000, desde un mnimo de S/. 5 117 en el T3 hasta S/. 15 263 en el T4.

    El beneficio total aument desde S/. 69,662 en el presupuesto original a S/. 71 446.79, sin ningn aumento en el presupuesto publicitario.

    SOLVER Y PROGRAMACIN LINEAL Veremos ahora la utilizacin de Solver para resolver casos de Programacin Lineal, aplicndolas a un ejemplo muy elemental, tomado del libro de Eppen, Gould y Schmidt, Investigacin de Operaciones en la Ciencia Administrativa, 3ra edicin, Editorial Prentice Hall. En ste y en otros libros de Investigacin Operativa, se encontrarn numerosas aplicaciones de Programacin Lineal y no Lineal. Nota: Las inestabilidades (por malas soluciones iniciales) del algoritmo de optimizacin no lineal no se presentan en casos de Programacin Lineal, dado que Solver utiliza el Mtodo Simplex. Ejercicio 04: El Modelo de la Protrac La Protrac Inc., fabrica dos tipos de productos qumicos, E y F, cuya utilidad neta es de $5000 y $4000 por tonelada respectivamente. Ambos pasan por operaciones de 2 departamentos de produccin, que

    tienen una disponibilidad limitada. El departamento A dispone de 150 horas mensuales; cada tonelada de

    E utiliza 10 horas de este departamento, y cada tonelada de F, 15 horas.

    El departamento B tiene una disponibilidad de 160 horas mensuales. Cada tonelada de E precisa de 20 horas, y cada tonelada de F precisa de 10 horas para su produccin.

    Para la produccin global de E y F, se debern utilizar al menos 135 horas de verificacin en el prximo mes; el producto E precisa de 30 horas y F de 10 horas por tonelada de verificacin.

    La alta gerencia ha decretado que es necesario producir al menos una tonelada de F por cada 3 de E.

    Un cliente ha solicitado 5 toneladas, cualquiera sea su tipo, de E o F.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 94 Nivel: Avanzado

    Por otro lado, es evidente que no pueden producirse cantidades negativas de E ni de F.

    Se trata de decidir, para el mes prximo, las cantidades a producir de cada uno de los productos para maximizar la utilidad global. Solucin: Variables controlables

    E: toneladas de tipo E a producir; F: toneladas de tipo F a producir;

    Modelo: Max 5000 E + 4000 F

    {Funcin objetivo: maximizar la utilidad global} Sujeto a: {escribimos ahora las restricciones o requerimientos} 10 E + 15 F 150 {horas del departamento A} 20 E + 10 F 160 {horas del departamento B} 30 E + 10 F 135 {horas de verificacin} E - 3 F 0 {al menos una de F cada 3 E significa E 3 F} E + F 5 {al menos 5 toneladas} E 0, F 0 {no negatividad}

    Copiar la frmula indicada

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 95 Nivel: Avanzado

    Observe que en la planilla hemos introducido la funcin objetivo en la celda A2; el lado izquierdo de las restricciones en el rango D7:D11, y el lado derecho de las restricciones en el rango F7:F11. Solucin: Seleccione de la Ficha Datos la opcin Solver. Aparecer el cuadro de dilogo Parmetros de Solver, en la que

    ingresaremos los datos. Con el cuadro de dilogo abierto, haga clic en la celda A2 de la planilla.

    En la caja debajo de Celda objetivo se borra el contenido anterior y se muestra $A$2.

    Haga clic en la opcin Mximo. Haga clic en la caja debajo de Cambiando las celdas. Haga clic en la

    celda B5, y arrastre el mouse sin soltarlo para seleccionar tambin la celda C5.

    Para Ingresar las restricciones, hacemos clic en el Botn Agregar, y

    aparecer la siguiente ventana, donde se deben ingresar las restricciones respetivas.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 96 Nivel: Avanzado

    Use Referencia de la celda para ingresar la restriccin. Use la lista desplegable del centro para elegir la condicin adecuada. Use Restriccin para completar la restriccin.

    Haga clic en el botn Agregar para agregar ms restricciones, o en el

    botn Aceptar para finalizar. El cuadro de dilogo Parmetros de Solver debe quedar:

    Aceptar la solucin de Solver:

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 97 Nivel: Avanzado

    Solver nos devolver las siguiente solucin: Haga clic en el botn Opciones, con lo que aparecer el cuadro de dilogo Opciones de Solver. Como nuestro modelo es lineal, seleccione la casilla de verificacin

    Adoptar modelo lineal, y luego haga clic en el botn Aceptar. Solucin Una vez introducidos estos datos, seleccione Resolver, y Solver, si todo anduvo bien, mostrar un mensaje con:

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 98 Nivel: Avanzado

    Informe de Respuestas:

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 99 Nivel: Avanzado

    PRCTICA GUIADA

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 100 Nivel: Avanzado

    Sesin 8

    FORMULARIOS

    Al finalizar la Sesin usted ser capaz de:

    Disear correctamente formularios en Excel.

    Usar eficientemente las propiedades de los controles de formularios.

    Crear adecuadamente aplicaciones con formularios para solucionar

    problemas.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 101 Nivel: Avanzado

    FORMULARIOS EN EXCEL FORMULARIOS Microsoft Excel funciona con varios tipos de informes. Puede utilizar los formularios que se proporcionan con Excel para escribir datos en rangos, listas o en otras bases de datos. Se pueden disear formularios para imprimirlos o utilizarlos en pantalla, as como para abrirlos en Excel o incluirlos en pginas Web. Para capturar y organizar los datos en los formularios en pantalla, se puede utilizar un libro de Excel u otro programa o base de datos. Formularios integrados para datos de Excel

    Para los rangos o listas en hojas de clculo de Excel, puede mostrar un formulario de datos que permite escribir nuevos datos, buscar filas basndose en el contenido de las celdas, actualizar los datos y eliminar filas del rango o de la lista.

    Formularios predefinidos para tareas de oficina comunes

    Excel proporciona Soluciones de hoja de clculo: plantillas predefinidas que le ayudarn a crear informes de gastos, facturas y pedidos. Estas plantillas tambin permiten almacenar la informacin que se escribe en los formularios de una base de datos.

    Disear un formulario propio en Excel

    Puede crear formularios de Excel para imprimirlos o utilizarlos en pantalla. Los formularios en pantalla pueden incluir controles (objetos de interfaz grfica para el usuario, como un cuadro de texto, una casilla de verificacin, una barra de desplazamiento o un botn de comando, que permite a los usuarios controlar el programa. Utilice los controles para mostrar datos y opciones, realizar una opcin o facilitar la lectura de la interfaz.), como botones de opcin y listas desplegables. Puede proteger un formulario en pantalla de modo que slo estn disponibles ciertas celdas para la entrada de datos, y tambin puede validar los datos para asegurarse de que los usuarios slo escriben los tipos de datos que requiere el formulario. Los formularios pueden facilitarse desde Excel, en pginas Web o desde programas de Microsoft Visual Basic para Aplicaciones (VBA: versin del lenguaje de macros de Microsoft Visual Basic que se utiliza para programar aplicaciones Windows y que se incluye en varias aplicaciones Microsoft.).

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 102 Nivel: Avanzado

    CONTROLES DE FORMULARIOS EN EXCEL Los controles son objetos grficos que se colocan en un formulario para mostrar o introducir datos, realizar una accin o facilitar la lectura del formulario. Estos objetos incluyen cuadros de texto, cuadros de lista, botones de opciones, botones de comandos y otros elementos. Los controles ofrecen al usuario opciones para seleccionar botones en los que hacer clic para ejecutar macros (accin o conjunto de acciones que se pueden utilizar para automatizar tareas. Las macros se graban en el lenguaje de programacin Visual Basic para Aplicaciones.), o secuencias de comandos Web (tipo de cdigo de equipo que se utiliza para realizar tareas en pginas Web tales como incrementar el contador de "nmero de visitantes" cada vez que hay un nuevo visitante. Es posible escribir las secuencias de comandos Web en varios lenguajes de secuencias de comandos. No es necesario compilar las secuencias de comandos para ejecutarlas.). Para insertar los controles debemos mostrar la ficha Programador. Microsoft Excel tiene dos tipos de controles: Los controles ActiveX (Control, como una casilla de verificacin o un

    botn, que ofrece opciones a los usuarios o ejecuta macros o secuencias de comandos que automatizan una tarea. Puede escribir macros para el control en Microsoft Visual Basic para Aplicaciones o secuencias de comandos en el Editor de secuencias de comandos de Microsoft.), son apropiados para la mayor parte de las situaciones y funcionan con las macros y secuencias de comandos Web de Microsoft Visual Basic para Aplicaciones (VBA).

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 103 Nivel: Avanzado

    Los controles de la barra de herramientas Formularios son compatibles con versiones anteriores de Excel, comenzando por Excel 5.0, y pueden utilizarse en hojas de macro XLM.

    DISEO Y CREACIN DE FORMULARIOS Cuadro Combinado Ejemplo: Insertar un Cuadro Combinado que muestre la lista de trabajadores escritos en las celdas A3:A8. Arrastre el control y dibjelo en la Hoja de Clculo.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 104 Nivel: Avanzado

    Ahora tenemos que personalizar el Cuadro Combinado. Para ello damos Anticlic sobre el Control y seleccionamos Formato del Control.

    Seleccionar el Rango de Entrada. Es la lista de Celdas que contienen los

    datos que se mostrarn en el Cuadro Combinado. Seleccionar la celda con la cual se vincular el Cuadro Combinado, en

    esta celda se mostrar el ndice del Elemento seleccionado.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 105 Nivel: Avanzado

    El resultado sera: Al seleccionar un Dato de la lista, en la celda Vinculada se mostrara el

    ndice respectivo. Botn de Opcin Permite seleccionar una opcin de una lista de mostrada. Ejercicio: Permitir seleccionar el Estado Civil de un trabajador. Arrastre el control y dibjelo en la Hoja de Clculo.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 106 Nivel: Avanzado

    Escribir el texto respectivo para cada Botn de Opcin. Ahora tenemos que personalizar el Botn de Opcin. Para ello damos

    Anticlic sobre el Control y seleccionamos Formato del Control.

    Vincular el control con alguna celda.

    Automticamente los Botones de Opcin restantes se vinculan con la

    celda seleccionada en el control anterior. Si agregamos una opcin, tambin se vincular con la misma celda.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 107 Nivel: Avanzado

    Casilla de Verificacin Permite seleccionar varias opciones de una lista, mostrando los valores de Verdadero o Falso segn sea el caso seleccionado. Ejemplo: Marcar los das que trabaj alguna persona Arrastre el control y dibjelo en la Hoja de Clculo.

    Ahora tenemos que personalizar la Casilla de Verificacin. Para ello

    damos Anticlic sobre el Control y seleccionamos Formato del Control. Vincular el control con alguna celda, en la que se mostrar VERDADERO

    en caso de que la opcin sea seleccionada y FALSO en caso contrario.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 108 Nivel: Avanzado

    Agregar los controles que sean necesarios y a cada uno de ellos se lo vincula con una celda diferente, obtenindose:

    Control de Nmero Muestra datos numricos que avanzan o retroceden con un intervalo determinado. Ejemplo: Permitir que una persona seleccione el Nmero de Hijos que tiene. Arrastre el control y dibjelo en la Hoja de Clculo.

    Ahora tenemos que personalizar el Control de Nmero. Para ello damos

    Anticlic sobre el Control y seleccionamos Formato del Control.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 109 Nivel: Avanzado

    Indicar el Mnimo y Mximo valor que se mostrar en la celda, as como el incremento.

    Vincular el control con alguna celda, en la que se mostrar el valor

    correspondiente. Al ir haciendo clic en el control el valor de la celda vinculada ir

    aumentando o disminuyendo. Cuadro de Lista Muestra una lista de datos y devuelve el ndice respectivo del elemento seleccionado. Ejemplo: Seleccionar el cargo del Trabajador

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 110 Nivel: Avanzado

    Arrastre el control y dibjelo en la Hoja de Clculo. Ahora tenemos que personalizar el Cuadro de Lista. Para ello damos

    Anticlic sobre el Control y seleccionamos Formato del Control. Seleccionar el rango de entrada, que son los datos que se mostrarn en

    el Cuadro de Lista. Vincular el control con alguna celda, en la que se mostrar el ndice

    correspondiente al cargo seleccionado. Al seleccionar un dato del Cuadro de Lista, se mostrar el ndice

    respectivo en la celda Vinculada.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 111 Nivel: Avanzado

    PRACTICA CLASE Agregue una Nueva Hoja y colquele como nombre DATOS. Copie los siguientes datos:

    Agregue una Nueva Hoja y colquele como nombre DATOS. Disee el siguiente formulario, teniendo en cuenta los siguientes

    criterios: o En la celda D13 inserte un Cuadro combinado cuyo rango de entrada

    son las Celdas G5:G7 de la Hoja Datos. Y vincularlo con la celda F13 de la hoja Ejemplo.

    o En la celda J13 inserte un Cuadro combinado cuyo rango de entrada son las Celdas E5:E7 de la Hoja Datos. Y vincularlo con la celda M13 de la hoja Ejemplo.

    o Combine la Celdas C19 con D19 e inserte un Cuadro combinado cuyo

    rango de entrada son las Celdas B5:B14 de la Hoja Datos. Y vincularlo con la celda F19 de la hoja Ejemplo. Repetir para las filas 21, 23, 25, 27 y 29.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 112 Nivel: Avanzado

    o En la celda F19 debe salir el ndice el producto seleccionado, con el cual se debe realizar una bsqueda del precio y mostrarlo en la celda H19. Repetir para las filas 21, 23, 25, 27 y 29.

    o En la Celda L19 ingresar un Cuadro de Nmero que muestra valores del 1 al 15 incrementndose de 1 en 1; vincularlo a la celda J19. Repetir para las filas 21, 23, 25, 27 y 29.

    o En la celda M19, multiplicar el precio unitario por la cantidad. Repetir para las filas 21, 23, 25, 27 y 29.

    o En la Celda M31, calcular el Sub Total sumando todos los Importes. o En la Celda M33, calcular el I.G.V. multiplicando Sub Total por 19%. o En la Celda M35, calcular el Total sumando Sub Total + I.G.V.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 113 Nivel: Avanzado

    o El resultado, seleccionando algunos datos sera:

    o En las Celdas C36 D36, Ingresar Botones de Opcin con el texto Contado y Crdito respectivamente; vincularlos con la celda F39.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 114 Nivel: Avanzado

    o En la Celda D41 inserte un Cuadro combinado cuyo rango de entrada son las Celdas J5:J9 de la Hoja Datos. Y vincularlo con la celda F41 de la hoja Ejemplo.

    o En la Celda J41, buscar el inters respectivo segn la tarjeta seleccionada.

    o En la celda L43, ingresar un Cuadro de Nmero que muestre datos desde 1 hasta 36, incrementndose de 1 en 1; simulando el nmero de cuotas. Vincularlo con la celda J43.

    o En la Celda J45, calcular el valor de la cuota segn el inters respectivo. Si el nmero de cuotas es 1, el valor de la cuota es el mismo que el Total, no tiene ningn incremento.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 115 Nivel: Avanzado

    o En la Celda M47, mostrar el total a Pagar, teniendo en cuenta que si la compra es al Crdito el total a Pagar es el Valor de la cuota, si la compra es al contado, el Total a Pagar es el Total Inicial calculado.

    o El diseo final del detalle de pago sera:

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 116 Nivel: Avanzado

    Sesin 9

    MACROS I

    Al finalizar la Sesin usted ser capaz de:

    Modificar correctamente la seguridad

    en Excel para el uso de las Macros.

    Grabar y ejecutar adecuadamente las macros.

    Asignar eficientemente las macros creadas a los controles de los

    formularios.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 117 Nivel: Avanzado

    CREACIN DE MACROS SEGURIDAD PARA MACROS Las macros", son una serie de comandos, funciones, instrucciones o tareas previamente guardadas y encadenadas, las que son ejecutadas en forma automtica cada vez que el usuario as lo disponga. Estas tareas o instrucciones son almacenadas en lo que tcnicamente se denomina como mdulo. Un mdulo es una coleccin de declaraciones, instrucciones y procedimientos almacenados juntos como una unidad con nombre. En Microsoft Office Excel, puede cambiar la configuracin de seguridad de macros para controlar qu macros se ejecutan y en qu circunstancias al abrir un libro. Por ejemplo, puede permitir la ejecucin de macros en funcin de si estn firmadas digitalmente por un programador de confianza. Para modificar las opciones de seguridad de Macros en Excel se selecciona la ficha Programador, luego en el grupo de opciones Cdigo, abrir Seguridad de Macros. Se mostrar la siguiente ventana, en la cual se debe seleccionar la opcin Habilitar Todas las Macros. GRABAR MACROS La forma ms fcil e intuitiva de crear macros es crearlas mediante el grabador de macros del que dispone Excel. Este grabador de macros te permite grabar las acciones deseadas que posteriormente las traduce a instrucciones en VBA, las cuales podemos modificar posteriormente si tenemos conocimientos de programacin. Para grabar una macro debemos acceder a la Ficha Vista y despliega el

    submen Macros y dentro de este submenu seleccionar la opcin Grabar macro.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 118 Nivel: Avanzado

    O tambin desde la Ficha Programador.

    Al seleccionar la opcin Grabar macro, lo primero que vemos es el cuadro de dilogo Grabar macro donde podemos dar un nombre a la macro (no est permitido insertar espacios en blanco en el nombre de la macro). Podemos asignarle un Mtodo abreviado: mediante la combinacin de las tecla CTRL + "una tecla del teclado". El problema est en encontrar una combinacin que no utilice ya Excel. En Guardar macro en: podemos seleccionar guardar la macro en el

    libro activo, en el libro de macros personal o en otro libro. En Descripcin: podemos describir cul es el cometido de la macro o

    cualquier otro dato que creamos conveniente.

    Para comenzar la grabacin de la macro pulsamos el botn Aceptar y a continuacin, si nos fijamos en la barra de estado, encontraremos este botn en la barra de estado donde tenemos la opcin de detener la grabacin.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 119 Nivel: Avanzado

    A partir de entonces debemos realizar las acciones que queramos grabar, es conveniente no seleccionar ninguna celda a partir de la grabacin, ya que si seleccionamos alguna celda posteriormente, cuando ejecutemos la macro, la seleccin nos puede ocasionar problemas de celdas fuera de rango. Realizar las acciones que se desean grabar en la macro.

    Una vez concluidas las acciones que queremos grabar, presionamos

    sobre el botn Detener de la barra de estado, o accediendo al men de Macros y haciendo clic en Detener Grabacin.

    La Macro quedar grabada en el Libro de Trabajo. Para visualizarlas

    debemos seleccionar Ver macros de la opcin Macros.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 120 Nivel: Avanzado

    EJECUTAR MACROS Una vez creada una macro, la podremos ejecutar las veces que queramos. Las opciones para ejecutar la macro encuentran en el men Macros de

    la pestaa Vista. Debemos seleccionar la macro deseada y pulsar sobre el botn

    Ejecutar. Se cerrar el cuadro y se ejecutar la macro. En cuanto al resto de botones: Cancelar: Cierra el cuadro de dilogo sin realizar ninguna accin. Paso a paso: Ejecuta la macro instruccin por instruccin abriendo el

    editor de programacin de Visual Basic. Modificar: Abre el editor de programacin de Visual Basic para

    modificar el cdigo de la macro. Estos dos ltimos botones son para los que sapan programar.

    Eliminar: Borra la macro. Opciones: Abre otro cuadro de dilogo donde podemos modificar la

    tecla de mtodo abreviado (combinacin de teclas que provoca la ejecucin de la macro sin necesidad de utilizar el men) y la descripcin de la macro.

  • Orga

    Curs

    ASILas form

    anizacin Ed

    so: Excel

    IGNAR Mmacros

    mulario llamSe inserta

    Se selecc

    Se escribe

    Clic sobre

    ucativa Leon

    MACROS Acreadas

    mados bota un botn

    iona la ma

    e el texto

    e el botn

    nardo Davin

    A OBJETpueden s

    tones. n.

    acro que s

    que se mo

    para ejec

    nci

    121

    TOS ser ejecu

    se ejecutar

    ostrar en

    utar la Ma

    utadas de

    r con ese

    n el Botn.

    acro.

    N

    esde los

    e botn.

    .

    Nivel: Avan

    controles

    nzado

    del

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 122 Nivel: Avanzado

    CREAR MACROS MANUALMENTE Para crear una macro de forma manual es necesario tener conocimientos de programacin en general y de Visual Basic en particular, ya que es el lenguaje de programacin en el que se basa el VBA de Excel. El lenguaje de programacin Visual Basic, es un lenguaje orientado a objetos, es decir, la filosofa de los lenguajes orientados a objetos es que el mundo real lo podemos abstraer de tal forma que lo podemos representar como objetos y programar con ellos. Un objeto es algo con entidad propia dentro de una clase. Y una clase es un conjunto de objetos que tienen propiedades en comn y se comportan de una manera igual o similar al resto de objetos pertenecientes a esa misma clase. Por ejemplo tenemos la clase "persona" que tiene una serie de propiedades y comportamiento claramente diferenciables de la clase "casa" y dentro de la clase "persona" existen muchos objetos que son cada una de las personas por ejemplo pertenecientes a un colegio. Las propiedades hemos dicho que son aquellas caractersticas que definen a los objetos de una clase, diferencindolos del resto de clases, siguiendo con el ejemplo persona unas propiedades podrn ser, la estatura, el peso, el color del pelo, el sexo, el color de los ojos, y todas aquellas propiedades que puedan diferenciar ms a cada objeto dentro de una clase, como nombre, apellido, DNI, etc. Adems de las propiedades, hemos dicho que las clases se caracterizan por su comportamiento, en orientado a objetos se le suele llamar mtodos que estn diferenciados en funciones y procedimientos. Los mtodos caractersticos de la clase persona sera, hablar, andar, escribir, escuchar, estudiar, etc. La diferencia fundamental entre funciones y procedimientos, es que las funciones al llevarse a cabo devuelven algn tipo de valor mientras que los procedimientos realizan su cometido y pueden o no devolver algn valor. Por ejemplo los mtodos mencionados en el prrafo anterior se pueden clasificar la mayora como procedimientos porque no tienen necesariamente que devolver ningn valor, pero por ejemplo el mtodo estudios se podra calificar como funcin que devuelve los ltimos estudios de esa persona. En la programacin orientada a objetos existe tambin un concepto muy importante que son los eventos. Los eventos son sucesos que son provocados por algn tipo de estmulo externo y que hacen que pueda alterarse el comportamiento de la clase. Seguimos con el ejemplo persona, un evento sobre persona sera el evento "despertarse", que provoca que la persona comience a funcionar, o un evento externo como "llamar", que provoca que la persona atienda a la persona que le ha llamado.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 123 Nivel: Avanzado

    EL ENTORNO DEL EDITOR DE VISUAL BASIC Para acceder al entorno de programacin de Excel debemos: Abrir el editor Visual Basic desde la Ficha Programador o tambin

    presionando la combinacin de teclas Alt + F11. Insertar un mdulo de trabajo que es donde se almacena el cdigo de

    las funciones o procedimientos de las macros. Plantearnos si lo que vamos a crear es una funcin (en el caso que

    devuelva algn valor), o si por el contrario es un procedimiento (si no devuelve ningn valor).

    Una vez concretado que es lo que vamos a crear, accedemos al men Insertar Procedimiento.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 124 Nivel: Avanzado

    Nos aparece un cuadro de dilogo como vemos en la imagen donde le damos el Nombre: al procedimiento/funcin sin insertar espacios en su nombre.

    Tambin escogemos de qu Tipo es, si es un Procedimiento, Funcin o es una Propiedad.

    Adems podemos seleccionar el mbito de ejecucin. Si lo ponemos como Pblico podremos utilizar el procedimiento/funcin desde cualquier otro mdulo, pero si lo creamos como Privado solo podremos utilizarlo dentro de ese mdulo.

    Una vez seleccionado el tipo de procedimiento y el mbito presionamos

    sobre Aceptar y se abre el editor de Visual Basic donde escribimos las instrucciones necesarias para definir la macro.

  • Organizacin Educativa Leonardo Davinci

    Curso: Excel 125 Nivel: Avanzado

    Como hemos dicho, el lenguaje que utiliza Excel para las macros es el Visual Basic, por lo tanto veamos algunas sentencias de programacin bsicas. Seleccionar una celda especfica:

    Range("A1").Select Escribir texto en una celda especfica:

    ActiveCell.FormulaR1C1 = "Ofimtica Empresarial" Escri