View
261
Download
4
Category
Preview:
Citation preview
EJERCICIO 1: GRAFICOS
EJERCICIO 1: GRAFICOSACTIVIDAD 1: CONSTRUYA LA LINEA DE TENDENCIA PARA LOS DATOS QUE SE PRESENTAN EN LA TABLA
1. Crear lista personalizada con nombres de los meses2. Construya la tabla que se muestra a continuacin
3. Construya el grafico.4. Agregar las lneas de tendencia tipo lineal, logartmica, poli nmica y potencial.
5. Seleccione la lnea de tendencia optimaACTIVIDAD 2: CONSTRUYA UN GRFICO PARA LOS DATOS QUE SE PRESENTAN EN LA TABLA
1. Crear una lista personalizada con los productos que se muestra2. Crear un grafico combinado donde muestre tanto los montos en bolvares como las cantidades.
ACTIVIDAD 3: COPIE Y PEGUE TANTO LA TABLA COMO EL GRAFICO EN UN DOCUMENTO WORD Y EN UNA PRESENTACIN EN PowerPoint. MODIFIQUE LOS DATOS DE LA TABLA Y VERIFIQUE QUE LA INFORMACIN DE WORD Y LA PRESENTACIN DE PowerPoint SE MODIFIQUEN.
EJERCICIO 2: MACROS BASICAS PARA FORMATOS
ACTIVIDAD 1: MACRO TABLA_FORMATO1
1. Construya una macro que permita generar una tabla con formato como se muestra en la figura. La tabla debe siempre estar ubicada en las celdas b2 hasta g9
2. La macro debe ejecutarse a travs de: Teclado
Por menHerramientas / macro / macros / tabla_formato1 / ejecutar Por icono
Mediante la ejecucin de un objeto
ACTIVIDAD 2: MACRO TABLA UBICACIN VARIABLE
1. Construya una macro que permita generar una tabla la cual se pueda ubicar en cualquier parte de su hoja de trabajo. Utilizar la opcin relativa.
2. La macro debe ejecutarse a travs de: Teclado Por menHerramientas / macro / macros / tabla_formato1 / ejecutar Por icono
Mediante la ejecucin de un objeto
EJERCICIO 03_ MACRO PARA RELLENAR ETIQUETASTAREA 1: Abrir el archivo de texto denominado Oct2007.txt y separarlo en columnas y pasarlo al libro de trabajo denominado EJERCICIO03 MACROS ETIQUETAS PERDIDAS.Pasos previos: Abra un libro nuevo de Excel y grbelo con el nombre de EJERCICIO03 MACROS ETIQUETAS PERDIDAS.XLSNotas: El primer carcter del nombre de la macro debe ser una letra. Los dems caracteres pueden ser letras, nmeros o caracteres de subrayado. No se permiten espacios en un nombre de macro; puede utilizarse un carcter de subrayado como separador de palabras. No utilice un nombre de macro que tambin sea una referencia de celda; de lo contrario puede aparecer un mensaje indicando que el nombre de la macro no es vlido.
Procedimiento a seguir en la tarea 1:
1. Pulse el botn de Grabar Macro
En el cuadro de dialogo de Grabar Macro, indique la informacin que se muestra en la figura.
Nombre de la macro: Importar_archivo
Guardar macro en: Este libro Seleccione el botn Aceptar.
Nota: Si desea que la macro est disponible siempre que utilice Excel, seleccione Libro de macros personal.
COMIENCE A GRABAR LA MACRO
2. Importar Archivo Oct2007.txt
Pasos:
Para ello seleccione del Men las opciones: Archivo / Abrir.
Cuando aparezca el cuadro de dialogo en la opcin Tipo de archivo seleccione Archivos de texto. Seleccione el archivo Oct2007.txt
Pulse el botn Abrir
En el paso 1 del asistente para importar texto, donde aparece Comenzar a importar fila seleccione 4
Pulse el botn Siguiente
En el paso 2 del asistente, presione el botn Siguiente, luego presione el botn Finalizar
Se crear un nuevo archivo denominado Oct2007.txt
Seleccione la fila 2 de la hoja que se le muestra y elimnela.
Seleccione en el men la opcin Ventana / Organizar / Mosaico / Aceptar
En el archivo Oct2007, seleccione la hoja Oct2007 y arrstrela hasta el libro EJERCICIO03 MACROS ETIQUETAS PERDIDAS
La hoja de clculo del archivo Oct2007, desaparecer y solo quedar el libro EJERCICIO03 MACROS ETIQUETAS PERDIDAS con una cuarta hoja denominada Oct2007.
Maximice la Hoja
Grabe el archivo
Detenga la Grabacin de la Macro.
TAREA 2: Crear una macro que permita rellenar las etiquetas perdidas de la hoja Oct2007
Denominada a la macro Rellenar_Etiquetas
Procedimiento: Seleccione el rea de trabajo denominada Regin Actual, para ello se pueden utilizar uno de dos procedimientos.
Men Edicin / Ir a / Especial / Regin Actual
Presionar Ctrl. + Shift + *
Seleccione las celdas en blanco. Men Edicin / Ir a / Especial / Celdas en blanco Seleccione la primera celda en blanco activa, de acuerdo a la figura
Presione Ctrl. + Enter
Vuelva a seleccionar la regin actual
Copie la tabla de datos completa
Pegue los datos copiados pero utilizando la opcin Edicin / Pegado Especial / Pegar Valores Presione Esc.
Detenga la Macro
TAREA 3: Disee una macro que le permita ejecutar de manera general las dos macros desarrolladas en las tareas 1 y 2. Denomine a esta macro: Etiquetas _ prdidasTAREA 4: Elabore un Men donde pueda ejecutar las macros Importar_Archivo, Rellenar_Etiquetas y Etiquetas_Perdidas
Utilice la opcin Herramientas / Personalizar / Nuevo Men
EJERCICIO 04: MACROS EN VISUALBASICELABORE UNA MACRO QUE PERMITA IMPORTAR CUALQUIER ARCHIVO DE TEXTO, INDEPENDIENTEMENTE DE SU NOMBRE.TAREA Uno: Construya una macro denominada Importar_Cualquier_Texto que permita desde un libro denominado EJERCICIO04 MACROS EN VISUALBASIC Abrir el archivo Oct2007.txt
Copiarlo al archivo EJERCICIO04 MACROS EN VISUALBASIC
Posteriormente grabarloTAREA Dos: Modifique la macro Importar_Cualquier_Texto e incorpore los siguientes cambios en VisualBasicProcedimiento a seguir:
Seleccione en el men las opciones: Herramientas / Macro / Macros o en su defecto pulse el botn de Ejecutar Macro. Cuando aparezca el cuadro de dialogo de Ejecucin de macros, seleccione la macro Importar_cualquier_texto.
Pulse el botn Modificar.
Le mostrar las siguientes instrucciones en VisualBasic de la Subrutina
Sub Importar_Cualquier_Texto()
' Importar_Cualquier_Texto Macro
' Macro grabada el 26/03/2008 por portatiluno
'
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\portatiluno.BOMBEROS\Mis documentos\EXCEL AVANZADO EJERCICIOS MARZO 2008\Oct2007.txt" _
, Origin:=xlMSDOS, StartRow:=4, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(8, 1), Array(23, 1), Array(34, 1), Array(50, 1), Array(58, 1)), _
TrailingMinusNumbers:=True
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Windows.Arrange ArrangeStyle:=xlTiled
Sheets("Oct2007").Select
Sheets("Oct2007").Move Before:=Workbooks( _
"EJERCICIO04 MACROS EN VISUALBASIC.xls").Sheets(1)
ActiveWindow.WindowState = xlMaximized
ActiveWorkbook.Save
End Sub
Realice los siguientes cambios
a. Divida la sentencia que comienza con Workbooks.OpenText en variar lneas para facilitar su lectura. Colocar un espacio y un guin bajo detrs de cada lnea parcial. Siga el siguiente ejemplo Workbooks.OpenText _
Filename:= _
"C:\Documents and Settings\portatiluno.BOMBEROS\Mis documentos\EXCEL AVANZADO EJERCICIOS MARZO 2008\Oct2007.txt", _
Origin:=xlMSDOS, _
StartRow:=4, _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(8, 1), Array(23, 1), Array(34, 1), Array(50, 1), Array(58, 1)), _
TrailingMinusNumbers:=True
b. Inserte una lnea nueva inmediatamente delante de la sentencia Workbooks.OpenText
miArchivo = Application.GetOpenFilename("Archivos de texto,*.txt")c. En la sentencia Workbooks.OpenText seleccione el nombre del archivo completo, incluyendo las comillas y brrelo. En su lugar escriba miArchivo. La sentencia debe quedar de la siguiente manera: miArchivo = Application.GetOpenFilename("Archivos de texto,*.txt")
Workbooks.OpenText _
Filename:= miArchivo, _ Origin:=xlMSDOS, _
StartRow:=4, _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(8, 1), Array(23, 1), Array(34, 1), Array(50, 1), Array(58, 1)), _
TrailingMinusNumbers:=True
d. Borre las sentencias que desplazan y modifican el tamao de la ventana, adems de la sentencia que selecciona la hoja. Es decir, borre las siguientes sentencias:
Windows.Arrange ArrangeStyle:=xlTiled
Sheets("Oct2007").Select
e. Cambie la palabra Sheets("Oct2007") por ActiveSheet en la sentencia Sheets("Oct2007").Move Before:=Workbooks("EJERCICIO04 MACROS EN VISUALBASIC.xls").Sheets(1)
De manera que la nueva sentencia quedeActiveSheet.Move Before:=Workbooks("EJERCICIO04 MACROS EN VISUALBASIC.xls").Sheets(1)
f. Cambie el nombre de la subrutina por el de Sub Importar_Cualquier_Texto1()
g. La macro deber quedar de la siguiente manera:
Sub Importar_Cualquier_Texto1()
' Importar_Cualquier_Texto Macro
' Macro grabada el 26/03/2008 por portatiluno
miArchivo = Application.GetOpenFilename("Archivos de texto,*.txt")
Workbooks.OpenText _
Filename:=miArchivo, _
StartRow:=4, _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(8, 1), Array(23, 1), Array(34, 1), Array(50, 1), Array(58, 1)), _
TrailingMinusNumbers:=True
Rows("2:2").Select
Selection.Delete Shift:=xlUp
ActiveSheet.Move Before:=Workbooks( _
"EJERCICIO04 MACROS EN VISUALBASIC.xls").Sheets(1)
ActiveWindow.WindowState = xlMaximized
ActiveWorkbook.Save
End Sub Una vez realizados los cambios grbelos y salga del editor de Visual Basic.
Pruebe los cambios realizados
EJERCICIO 05: FORMULAS FINANCIERAS
ACTIVIDAD 1: CALCULAR EL PAGO MENSUAL PARA UN PRESTAMO DE ACUERDO A LA INFORMACION DE LA TABLA
Escriba los valores
Importe del prstamo 35.000.000
Inters anual18,0%
Inters MENSUAL1,5%
Perodo del prstamo en aos20
Nmero de pagos anuales12
Total Cuotas a Cancelar240
Fecha inicial del prstamo01/05/2006
Pago por periodo540.159,03
ACTIVIDAD 2: CONSTRUYA UNA TABLA DONDE CALCULE LA AMORTIZACIN DE CAPITAL Y EL PAGO DE INTERESES DE ACUERDO A LA TABLA QUE SE MUESTRA EN LA FIGURA
EXTIENDA DICHO CLCULO A 240 MESES
PERIODOCAPITALINTERESESpagoSaldo
1-15.159,03 Bs-525.000,00 Bs-540.159,03 Bs34.984.840,97 Bs
2-15.386,42 Bs-524.772,61 Bs-540.159,03 Bs34.969.454,55 Bs
3-15.617,21 Bs-524.541,82 Bs-540.159,03 Bs34.953.837,33 Bs
4-15.851,47 Bs-524.307,56 Bs-540.159,03 Bs34.937.985,86 Bs
5-16.089,25 Bs-524.069,79 Bs-540.159,03 Bs34.921.896,61 Bs
6-16.330,58 Bs-523.828,45 Bs-540.159,03 Bs34.905.566,03 Bs
7-16.575,54 Bs-523.583,49 Bs-540.159,03 Bs34.888.990,49 Bs
8-16.824,18 Bs-523.334,86 Bs-540.159,03 Bs34.872.166,31 Bs
9-17.076,54 Bs-523.082,49 Bs-540.159,03 Bs34.855.089,77 Bs
10-17.332,69 Bs-522.826,35 Bs-540.159,03 Bs34.837.757,09 Bs
11-17.592,68 Bs-522.566,36 Bs-540.159,03 Bs34.820.164,41 Bs
12-17.856,57 Bs-522.302,47 Bs-540.159,03 Bs34.802.307,84 Bs
13-18.124,42 Bs-522.034,62 Bs-540.159,03 Bs34.784.183,43 Bs
238-516.563,26 Bs-23.595,77 Bs-540.159,03 Bs1.056.488,10 Bs
239-524.311,71 Bs-15.847,32 Bs-540.159,03 Bs532.176,39 Bs
240-532.176,39 Bs-7.982,65 Bs-540.159,03 Bs-0,00 Bs
ACTIVIDAD 3: CONSTRUYA UN GRAFICO DONDE muestre TANTO LA AMORZACION DE CAPITAL COMO LOS INTERESES PAGADOS DURANTE LOS 240 MESES DEL PRESTAMO.
ACTIVIDAD 4: REALICE UN ANALISIS DE SENSIBILIDAD A LOS PARAMETROS DE LA FORMULA DEL PAGO MENSUAL. EVALUE EL COMPORTAMIENTO DE LAS VARIABLES INTERES Y PERIODO.
PROCEDIMIENTO Disee la tabla que se muestra en la figura
Seleccione el rango de datos de la tabla, empezando desde la celda d4 hasta la celda v25 (d4:v25) Seleccione en el men la opcin datos / tabla Indique las celdas de entrada de las filas y columnas como se muestra en la figura
EJERCICIO 06: VALIDACIN DE DATOS
ACTIVIDAD 1: VALIDACION DE UNA LISTA CON ORIGEN DE DATOS LOCAL
ACTIVIDAD 2: VALIDACION DE UNA LISTA CON ORIGEN DE DATOS EN UN RANGO DE DATOS DE LA MISMA HOJA
ACTIVIDAD 3: VALIDACION DE UNA LISTA CON ORIGEN DE DATOS EN UN RANGO DE DATOS EN OTRA HOJA DEL MISMO LIBRO
ACTIVIDAD 4: VALIDACION DE DATOS QUE SOLO PERMITAN:
El monto de facturacin sea como mnimo de 200$
Solo se acepten fechas correspondientes al ao 2008
EJERCICIO 07: FUNCIONES VARIAS
Abrir el archivo denominado, datos licores
Incorpore las siguientes columnas
UTILIZAR LA FUNCIN MES( ) PARA CALCULAR EL NUMERO DE MES ASOCIADO A LA FECHA DEL PEDIDO. UTILIZAR LA FUNCIN BUSCARV PARA CALCULAR EL MES, TRIMESTRE Y SEMESTRE. Puede crear la matriz de bsqueda en la misma hoja o en otra hoja del mismo archivo. Utilice la Funcin AO() para determinar el ao de la transaccin. (Funcin Year)
Evale otras funciones alternativas tales como:
Funcin ELEGIR, para determinar el Nombre del Mes
Funcin SI (IF) y Funcin O (OR) para determinar el Trimestre
Funcin SI (IF) y Funcin O (OR) para determinar el Semestre
EJERCICIO 08: ORDENAMIENTO DE DATOS
ACTIVIDAD 1: ORDENE LOS DATOS DE ACUERDO AL ESQUEMA QUE SE LE PRESENTA EN LA FIGURA.
Utilice las distintas alternativas de ordenamiento vistas en clase tales como: Ordenamiento por un solo campo clave. Utilizar los iconos
Ordenamiento por dos o tres campos claves. Utilizar el men datos / ordenar
Ordenamientos especiales tales como ordenar por meses, por una lista de productos especfica la cual empieza por el producto lder. Utilizar el men datos / ordenar / opciones
ACTIVIDAD 2: ORDENE LOS DATOS POR FILAS DE ACUERDO AL SIGUIENTE ESQUEMA
PROCEDIMIENTO: Debe crear en primera instancia una lista personalizada con los campos de ordenamiento como se muestra en la figura
Seleccione en el Men las opciones Datos / Ordenar / Opciones / Primer criterio de ordenamiento (seleccione la lista personalizada) / Orientacin: ordenar de izquierda a derecha. De acuerdo a la figura.
ACTIVIDAD 3: Desarrolle las macros de ordenamiento que se muestran en la figura.
EJERCICIO 09: FILTROS Y SUBTOTALES
ACTIVIDAD 1: ACTIVE LOS FILTROS AUTOMATICOS QUE SE LE SOLICITAN EN LA FIGURA
ACTIVIDAD 2: ACTIVE LOS FILTROS AVANZADOS QUE SE LE SOLICITAN EN LA FIGURA
PROCEDIMIENTO: MENU DATOS / FILTRO / FILTRO AVANZADO
ACTIVIDAD 3: ELABORE UNA MACRO PARA LOS SIGUIENTES FILTROS AVANZADOS.
ACTIVIDAD 4: ELABORE UNA MACRO PARA LOS SIGUIENTES SUBTOTALES
EJERCICIO 10: TABLAS DINAMICAS
ACTIVIDAD 1: ELABORE LA SIGUIENTE TABLA DINAMICA.
Para ello ubquese en cualquier celda de la base de datos fuente y utilice las opciones de men: Datos / informe de tablas y grficos dinmicos / Hoja de calculo nueva / Diseo
ACTIVIDAD 2: ELABORE LA SIGUIENTE TABLA DINAMICA.
Para ello ubquese en cualquier celda de la base de datos fuente y utilice las opciones de men: Datos / informe de tablas y grficos dinmicos / Hoja de calculo nueva / Diseo
Haga doble click sobre el campo Suma de monto Factura Cambie la opcin de Resumen y cambie el Nombre del Campo
ACTIVIDAD 3: ELABORE LA SIGUIENTE TABLA DINAMICA.
Para ello ubquese en cualquier celda de la base de datos fuente y utilice las opciones de men: Datos / informe de tablas y grficos dinmicos / Hoja de calculo nueva / Diseo
Ordene la lista de vendedores descendentemente.
Haga doble click sobre el campo Suma de monto Factura
En la opcin Mostrar Datos como seleccione Diferencia de
Seleccione el campo base vendedor
Seleccione el Elemento base Leverling, Janet (este es el vendedor con mayores ventas)
ACTIVIDAD 4: ELABORE LA SIGUIENTE TABLA DINAMICA.
ACTIVIDAD 5: ELABORE LA SIGUIENTE TABLA DINAMICA.
ACTIVIDAD 6: ELABORE LA SIGUIENTE TABLA DINAMICA.
ACTIVIDAD 7: ELABORE LA SIGUIENTE TABLA DINAMICA.
Para ello ubquese en cualquier celda de la base de datos fuente y utilice las opciones de men: Datos / informe de tablas y grficos dinmicos / Hoja de calculo nueva / Diseo
Para crear las zonas proceda de la siguiente manera:
Seleccione los vendedores de la zona occidental
Seleccione en el men Datos / Agrupar esquema / Agrupar
ACTIVIDAD 8: ELABORE LA SIGUIENTE TABLA DINAMICA. CAMPOS CALCULADOS
Para ello ubquese en cualquier celda de la base de datos fuente y utilice las opciones de men: Datos / informe de tablas y grficos dinmicos / Hoja de calculo nueva / Diseo
Los campos Bono Produccin Vendedor, Bono de Ventas y Sueldo, son denominados campos calculados y utilizan formulas. Para incorporar campos calculados proceda de la siguiente manera:
Ubquese en la tabla dinmica, donde estn los datos de la tabla, a fin de incorporar un nuevo campo calculado.
Verifique que se encuentre activa la barra de herramientas de Tabla Dinmica
Seleccione en esa barra de herramientas la opcin Tabla dinmica / Formula / campo calculado
Incorpore los campos deseados y sus correspondientes formulas.
Por Ejemplo: En Nombre, asgnele un nombre al campo. En Formula, coloque la formula que desea utilizar, insertando los campos de la lista que usted considere convenientes.
Repita el procedimiento anterior e incorpore los campos Bono de ventas y Sueldo, utilizando las formulas mostradas en las figuras.
ACTIVIDAD 9: ELABORE LA SIGUIENTE TABLA DINAMICA.
Para ello ubquese en cualquier celda de la base de datos fuente y utilice las opciones de men: Datos / informe de tablas y grficos dinmicos / Hoja de calculo nueva / Diseo
Ubquese en la tabla dinmica en la columna del producto, a fin de incorporar un nuevo producto (nuevo elemento).
Verifique que se encuentre activa la barra de herramientas de Tabla Dinmica
Seleccione en esa barra de herramientas la opcin Tabla dinmica / Formula / elemento calculado
Coloque el nombre del nuevo producto y la formula que estar asociada a l, de acuerdo a la figura.
ACTIVIDAD 10: ELABORE LA SIGUIENTE TABLA DINAMICA, basandose en la tabla dinmica realizada en la actividad 9.
EJERCICIO 11: GRFICOS DINMICOS
ACTIVIDAD 1: ELABORE LA SIGUIENTE TABLA DINAMICA.
Ubquese sobre la tabla dinmica y presione la tecla F11 para generar el grfico dinmico basado en dicha tabla, de acuerdo a la figura.
ACTIVIDAD 2: ELABORE LA SIGUIENTE TABLA DINAMICA.
Genere el Grfico dinmico, basado en dicha tabla dinmica-
EJERCICIO 12: TABLA DINMICA BASICA CON RANGOS DE CONSOLIDACIN MULTIPLESACTIVIDAD 1: Elabore las siguientes hojas de trabajo en un mismo libro de Excel.Hoja MES DE ENERO
HOJA MES DE FEBRERO
HOJA MES DE MARZO
ACTIVIDAD 2: Inserte una nueva hoja y ubique su curso en la celda A1. Proceda a crear la TABLA DINMICA de acuerdo a lo siguiente:Men DATOS / INFORME DE TABLAS Y GRFICOS DINMICOS / RANGOS DE CONSOLIDACIN MLTIPLE
CREAR UN SOLO CAMPO DE PGINA
AGREGUE LOS RANGOS DONDE SE ENCUENTRAN UBICADOS LOS RANGOS DE CADA NOMINA
VAYA AL SIGUIENTE PASO
HAGA CLICK EN LA OPCIN DISEO
HAGA DOBLE CLICK EN EL CAMPO CUENTA VALOR Y CAMBIE LA OPCION A SUMA
AL FINALIZAR DEBE TENER UNA TABLA DINAMICA COMO SE MUESTRA EN LA FIGURA
ELIMINE ALGUNAS OPCIONES DEL CAMPO COLUMNA DE ACUERDO A LO SIGUIENTE:
EJERCICIO 13: TABLA DINMICA BASICA CON RANGOS DE CONSOLIDACIN MULTIPLESEn este ejercicio se ver un segundo mtodo para consolidar datos. Para ello utilice el mismo libro que elabor en el ejercicio 12. Es decir:Hoja MES DE ENERO
HOJA MES DE FEBRERO
HOJA MES DE MARZO
ACTIVIDAD 1: Inserte una nueva hoja y ubique su curso en la celda A1. Proceda a consolidar la informacin de estas tres hojas de la siguiente manera: Men Datos / Consolidar / Asigne los rangos de datos o referencias de acuerdo a la siguiente figura
Seleccione en ese cuadro de dialogo las opciones Usar rtulos en: Fila Superior y Columna Izquierda
Seleccione tambin Crear Vnculos con los datos de origen
Haga clic en aceptar
Obtendr en la hoja los siguientes resultados correspondientes a la consolidacin de datos.
Realice los ajustes necesarios eliminando las columnas que no necesite.
EJERCICIOS ADICIONALES
AUTOFILTROS
FILTRO AVANZADO
SUBTOTALES
PAGE 1
Recommended