Excel 80105487

Curso de Excel XP (segundaparte)Autor: Xavi Llunell[Ver curso online]

Descubre miles de cursos como ste en www.mailxmail.com 1

mailxmail - Cursos para compartir lo que sabes

Presentacin del cursoExcel XP, incluido en el paquete de ofimtica Office de Microsoft, es el programams utilizado por las empresas de todo el mundo. Con l podemos crear ymodificar hojas de clculo, as como realizar grficas muy completas. mailxmail te ofrece un curso completo de Excel XP, dividido en dos partes (sta esla segunda), en el que encontrars la respuesta a la mayora de las preguntas quepuedan surgirte sobre su funcionamiento.

Con este servicio, queremos garantizarte el aprendizaje y la desenvoltura en elmanejo de la aplicacin sin que tengas la necesidad de moverte de casa.

Visita ms cursos como este en mailxmail:[http://www.mailxmail.com/cursos-informatica][http://www.mailxmail.com/cursos-ofimatica]

Tu opinin cuenta! Lee todas las opiniones de este curso y djanos la tuya:[http://www.mailxmail.com/curso-excel-xp-segunda-parte/opiniones]

Cursos similaresCursos Valoracin Alumnos Vdeo

Power point y sus mensPower Point es un programa que nos permite realizar presentaciones paranuestras reuniones o proyectos con variedas de objetos, tanto de texto,grficos, imgenes y sonid... [23 /03/05]

10.866

Cmo utilizar fcilmente el PaintEn este curso se presenta una manera fcil de aprender a manejar el Paint.Con l podrs modificar y crear dibujos, textos y todo lo que quieras paraambientar todos aque... [18 /07/06]

7.609

Curso de Excel XP (primera parte)Excel XP, incluido en el paquete de ofimtica Office de Microsoft, es elprograma ms utilizado por las empresas de todo el mundo. Con lpodemos crear y modificar hojas... [25 /04/03]

170.213

SGBD. Sistemas gestores de bases dedatos (primera parte)SGBD, Sistemas gestores de bases datos, en un curso de informtica serio,pero accesible, sobre las funciones y ventajas de un sistema gestor debases de datos, los incon... [21 /10/08]

4.033

Word. Barra de herramientasLa utilidad de la barra de herramientas de Microsoft Word vas a aprenderen este curso. Tendrs en tus manos la posibilidad de conocer las msimportantes herramientas pa... [19 /09/08]

4.575



1. Introduccin [http://www.mailxmail.com/curso-excel-xp-segunda-parte/introduccion]En esta primera leccin de la segunda parte del curso de Excel XP, slo queramoshacerle una recomendacin general sobre el buen uso de las frmulas en Excel y quequeremos que tenga en cuenta.Un punto importante para el trabajo continuado con Excel es tener claro que todaslas hojas que hagamos tenemos que pensar que sean lo ms dinmicas y lo msautomatizadas posible. Tenemos que acostumbrarnos que la hoja con la queestamos trabajando, si realmente nos es til, puede ser que se vaya ampliando pocoa poco, con lo que nos tendremos que acostumbrar a utilizar frmulas fcilmenteeditables y modificables. Un ejemplo.-Imagine que queremos hacer una suma de cuatro valores que tenemosen diferentes celdas: pongamos A1; B1; C1; D1. Para poner la suma de estos valoresen la celda F1, podemos escribirla de dos formas: =A1+B1+C1+D1 o bien=SUMA(A1:D1). Ahora imagine que insertamos una nueva columna entre la B y la C yescribimos un nuevo valor, que igualmente queremos que se sume en la celda F1,que ahora habr pasado a ser G1. Le invito a que te hagas un pequeo ejemplo con ambos casos y veas que es lo queocurre y que es lo ms cmodo y en cual de los dos casos tenemos que tener menosmiedo de equivocarnos y de estar verificando frmulas.La verdad es que en el momento en el que se acostumbra a utilizar Excel cada vezbusca el realizar hojas, intentar completarlas al mximo y despus al irlascompletando olvidarse de las modificaciones y que el resultado de las frmulas estcorrectamente.

Le aconsejo encarecidamente que siempre que pueda utilice los rangos de celdas enuna frmula, aunque sean de pocas celdas. Los rangos facilitan mucho el trabajo.Otro punto muy importante dentro de las hojas de Excel es la correcta utilizacin delas celdas absolutas ($B$4). En muchas ocasiones realizamos una hoja en la quetenemos una celda que siempre ser un valor de referencia que tendremos en cuentapara una serie de frmulas. Normalmente, lo que nos pasa es que, en un primermomento, puede ser que tengamos una hoja pensada de una forma concreta, noveamos una modificacin inminente y no pongamos esta celda como absoluta,pero despus al necesitar ampliar esta hoja, aadiendo filas y columnas, nos damoscuenta que los valores de las frmulas empiezan a ser diferentes de los que habansido hasta este momento. Esto se debe a que la referencia a la celda que hubisemosquerido sea fija se va modificando, con lo que puede apuntar a valores que nospueden producir un error en un resultado o incluso un error en la frmula en s. Es por esto que tambin le recomiendo que siempre que piense que un valor de unacelda debe actuar como un valor absoluto, no se lo piense y convierta esa celda dela frmula en una celda absoluta. Seguramente esto le evitar muchos problemas ala larga. Le recomiendo que realice multitud de ejemplos y ver la importancia de lo que lehe comentado.



La ltima recomendacin que te hago antes que entres de lleno en la segunda partedel curso es que hagas todas las hojas de Excel que puedas e inventes todos losejemplos que puedas por muy sencillos que te puedan parecer, seguro que poco apoco los amplias y los complicas. Adems la mejor manera de aprender a utilizar Excel es practicando y practicando.



2. Las matrices[http://www.mailxmail.com/curso-excel-xp-segunda-parte/matrices]El concepto de Matriz viene de los lenguajes de programacin y de la necesidad detrabajar con varios elementos de forma rpida y cmoda. Podramos decir que unamatriz es una serie de elementos que forman filas (matriz bi-dimensional) o filas ycolumnas (matriz tri-dimensional). La siguiente tabla representa una matriz bidimensional:

1 2 3 4 5

...ahora una matriz tridimensional:

1,1 1,2 1,3 1,4 1,52,1 2,2 2,3 2,4 2,53,1 3,2 3,3 3,4 3,5

Observa, por ejemplo, el nombre del elemento 3,4 que significa que est en laposicin de fila 3, columna 4. En Excel, podemos tener un grupo de celdas en formade matriz y aplicar una frmula determinada en ellas de forma que tendremos unahorro del tiempo de escritura de frmulas.En Excel, las frmulas que hacen referencia a matrices se encierran entre corchetes{}. Hay que tener en cuenta al trabajar con matrices lo siguiente:-No se puede cambiar el contenido de las celdas que componen la matriz-No se puede eliminar o mover celdas que componen la matriz-No se puede insertar nuevas celdas en el rango que compone la matriz1. Crea la siguiente hoja:

En la celda B4, observars que hemos hecho una simple multiplicacin para calcularel precio total de las unidades. Lo mismo pasa con las dems frmulas.En vez de esto, podramos haber combinado todos los clculos posibles en uno soloutilizando una frmula matricial.Una frmula matricial se tiene que aceptar utilizando la combinacin de teclas



CTRL+MYSC+Intro y Excel colocar los corchetes automticamente.

2. Borra las celdas adecuadas para que quede la hoja de la siguiente forma:

3. Sita el cursor en la celda B7 e introduce la frmula: =SUMA(B3:E3*B4:E4)

4. Acepta la frmula usando la combinacin de teclas adecuada.

Observa cmo hemos obtenido el mismo resultado tan slo con introducir unafrmula.

Observa la misma en la barra de frmulas. Ahora hay que tener cuidado en editarceldas que pertenezcan a una matriz, ya que no se pueden efectuar operaciones queafecten slo a un rango de datos. Cuando editamos una matriz, editamos todo elrango como si de una sola celda se tratase.

Constantes matriciales.- Al igual que en las frmulas normales, podemos incluirreferencias a datos fijos o constantes. En las frmulas matriciales tambin podemosincluir datos constantes. A estos datos se les llama constantes matriciales y se debeincluir un separador de columnas (smbolo ;) y un separador de filas (smbolo \ ). Porejemplo, para incluir una matriz como constante matricial:

1.Escribe estas celdas en la hoja2



2.Selecciona el rango C1:D2

3.Escribe la frmula: =A1:B2*{10;20\ 30;40}

4.Acepta la frmula con la combinacin de teclas adecuada.

Observa que Excel ha ido multiplicando los valores de la matriz por los nmerosintroducidos en la frmula:

Cuando trabajamos por frmulas matriciales, cada uno de los elementos de lamisma, debe tener idntico nmero de filas y columnas porque, de lo contrario,Excel expandira las frmulas matriciales. Por ejemplo:= {1;2;3}*{2\ 3}

se convertira en = {1;2;3\ 1;2;3}*{2;2;2\ 3;3;3}

5.Selecciona el rango C4:E5

6.Introduce la frmula: =A4:B4+{2;5;0\ 3;9;5} y acptala.

Observemos que Excel devuelve un mensaje de error diciendo que el rangoseleccionado es diferente al de la matriz original.

7.Graba la hoja si lo deseas.



3. Vinculos y referencias en Excel[http://www.mailxmail.com/curso-excel-xp-segunda-parte/vinculos-referencias-excel]Excel permite utilizar en sus frmulas referencias a otras celdas, hojas o inclusolibros de trabajo. A veces es ms prctico dividir el trabajo en pequeos libros yposteriormente unirlos en uno. Imagnate una empresa con tres sucursales, lascuales llevan por separado una serie de hojas. En un momento dado, interesaraunirlas todas en una sola hoja a modo de resumen.Excel permite varios tipos de referencias en sus frmulas:

-Referencias externas: cualquier referencia a celdas y rangos de otros libros detrabajo.

-Libro independiente: un libro que contiene vnculos con otros libros y, por lotanto, depende de los datos de los otros libros.

-Libro de trabajo fuente: libro que contiene los datos a los que hace referencia unafrmula de un libro dependiente a travs de una referencia externa.

Por ejemplo, la referencia: 'C:\ Mis documentos\ [Ventas.xls]Enero'!A12harareferencia a la celda A12 de la hoja Enero del libro Ventas.xls que est guardado enla carpeta Mis documentos de la unidad C:

1.Crea en un libro nuevo la siguiente hoja:

2.Guarda el libro con el nombre: Empresa1

3.Cierra el libro de trabajo.4.En un nuevo libro de trabajo, crea la siguiente hoja:



5.Sitate en la celda B4.6.Escribe la frmula: (suponiendo que la tengas guardada en la carpeta Misdocumentos: ='C:\ Mis documentos\ [empresa1.xls]Hoja1'!B4:D4)

7.Cpiala dos celdas hacia abajo.8.Graba el libro con el nombre: empresa2.xls

9.Abre el libro empresa1.xls

10.Accede a Ventana - Organizar y acepta la opcin Mosaico.

Ahora tenemos dos ventanas correspondientes a los dos libros de trabajo abiertos.Para pasar de una a otra, debemos activarla con un clik en su ttulo o en cualquierparte de la misma. Por ejemplo, si deseamos situar el cursor en la ventana inactiva,primero debemos pulsar un click para activarla y despus otro click para situar ya elcursor.

11.Sita el cursor en la celda B4 del libro empresa2.

Observa la barra de frmulas. Ahora no vemos el camino marcado que hacereferencia a un archivo grabado en disco. Cuando tenemos abiertos los archivos, nose observa el camino de unidades y carpetas.

Si ahora modificamos cualquier dato del libro empresa1, se actualizaran lasfrmulas del libro empresa2.

12.Cierra los dos libros.

Auditora de hojas.- Esta sencilla opcin sirve para saber a qu celdas hacereferencia una frmula determinada, posibles errores en frmulas, etc.

1.Crea un libro nuevo.2.Crea una sencilla hoja con sus frmulas:

3. Sita el cursor en la celda D 2

3.Accede a Herramientas - Auditora - Rastrear precedentes

4.Accede a Herramientas - Auditora - Rastrear dependientes

Excel nos muestra que la frmula hace referencia al rango B2:C2 (precedentes) yque a su vez, otra celda, la E2, depende del resultado de la celda actual(dependientes).Descubre miles de cursos como ste en www.mailxmail.com 9


(dependientes).

A travs de esta opcin podemos localizar qu celdas dependen de otras en susfrmulas; a qu celdas hace referencia la frmula e; incluso podemos, en caso deerror, localizar el mismo (opcin Rastrear error)

5.Accede a Herramientas - Auditora - Quitar todas las flechas



4. Proteccin de hojas[http://www.mailxmail.com/curso-excel-xp-segunda-parte/proteccion-hojas]La proteccin de hojas nos permite proteger contra borrados accidentales algunasceldas que consideremos importantes. Podemos proteger toda la hoja, el libroentero, o bien slo algunas celdas. Para realizar estos pasos, abre cualquier prcticaguardada anteriormente.

1.Accede a Herramientas - Proteger - Proteger hoja y acepta el cuadro de dilogoque aparece.2.Intenta borrar con la tecla Supr cualquier celda que contenga un dato.

La hoja est protegida por completo. Imaginemos ahora que slo deseamosproteger las celdas que contienen las frmulas, dejando libres de proteccin el restode celdas.

3.Desprotege la hoja siguiendo el mismo mtodo que antes.4.Selecciona, por ejemplo, el rango B2:C4 y accede a Formato - Celdas - (Pestaaproteger).

5.Desactiva la opcin Bloqueada y acepta el cuadro.6.Vuelve a proteger la hoja desde Herramientas - Proteger - Proteger hoja.

7.Cambia algn valor del rango B2:C4

8.Intenta cambiar algo o borrar alguna celda del resto de la hoja.

Con la opcin anterior (Bloqueada), hemos preparado un rango de celdas para queest libre de proteccin cuando decidamos proteger toda la hoja. De esta forma, nohabr fallos de borrados accidentales en celdas importantes.

Si escribimos una contrasea al proteger la hoja, nos la pedir en caso de quererdesprotegerla posteriormente.

Si elegimos la opcin Proteger libro, podemos proteger la estructura entera dellibro (formatos, anchura de columnas, colores, etc...)

Insertar comentarios.- Es posible la insercin de comentarios en una celda a modode anotacin personal. Desde la opcin Insertar - Comentario podemos crear unapequea anotacin.

1.Sita el cursor en cualquier celda

y accede a Insertar - Comentario.

2.Escribe el siguiente texto:Descuento aplicado segn la ltima reunin del consejo de administracin



3.Pulsa click fuera de la casilla amarilla.

Dependiendo de qu opcin est activada en el men Herramientas - Opciones -Ver, podemos desactivar la visualizacin de una marca roja, la nota amarilla, activarslo la marca, o todo.

4.Accede a Herramientas - Opciones y observa en la pestaa Ver (seccin Comentarios) las distintas casillas de opcin. Prueba a activar las tres saliendo delcuadro de dilogo y observa el resultado.5.Finalmente, deja la opcin Slo indicador de comentario activada.6.Sita el cursor sobre la celda que contiene el comentario.7.Pulsa el botn derecho del ratn sobre esa misma celda.

Desde aqu, o bien desde Edicin, podemos modificar o eliminar el comentario.

Subtotales.- En listas de datos agrupados por un campo, es til mostrar a veces, noslo el total general de una columna, sino tambin los sub-totales parciales de cadaelemento comn.

1.Crea una sencilla hoja:

2. Ordnala por Marca.3.Selecciona todo el rango de datos (A1:C6)

4.Accede a Datos - Subtotales.

Excel nos muestra, por defecto, una configuracin para crear sub-totales agrupadospor Marca (casilla Para cada cambio en), utilizando la funcin SUMA y aadiendo elresultado bajo la columna Ventas.

5.Acepta el cuadro.

Observa la agrupacin que ha hecho Excel, calculando las ventas por marcas yobteniendo las sumas parciales de cada una de ellas.

En el margen izquierdo de la ventana se muestran unos controles para obtenermayor o menor nivel de resumen en los subtotales.

6.Pulsa los botones y observa el resultado.



7.Vuelve a Datos - Subtotales.

8.Abre la lista de Usar funcin y elige la funcin PROMEDIO.9.Desactiva la casilla Reemplazar subtotales actuales porque borrara los que yahay escritos.10.Acepta.

11.Pulsa un click uno a uno en los 4 botones y observa el resultado.12.Accede a Datos - Subtotales y pulsa en Quitar todos.

Si se quisiera crear subtotales por otro campo (por ejemplo el campo Pas),deberamos primero ordenar la lista por ese campo para que Excel pueda agruparposteriormente la tabla.



5. Las tablas dinmicas[http://www.mailxmail.com/curso-excel-xp-segunda-parte/tablas-dinamicas]Una tabla dinmica nos permite modificar el aspecto de una lista de elementos deuna forma ms fcil, cmoda y resumida. Adems, podemos modificar su aspecto ymover campos de lugar.

Para crear tablas dinmicas hemos de tener previamente una tabla de datospreparada y posteriormente acceder a Datos - Informe de tablas y grficosdinmicos.

1.Crea la siguiente tabla de datos:

2.Selecciona toda la tabla y accede a Datos - Informe de tablas y grficosdinmicos.

En primer lugar aparece una pantalla que representa el primer paso en el Informe detablas y grficos dinmicos. Aceptaremos la tabla que hay en pantalla.

3.Pulsa en Siguiente.4.Acepta el rango pulsando en Siguiente.Como ltimo paso, Excel nos propone crear la tabla en la misma hoja de trabajo apartir de una celda determinada, o bien en una hoja completamente nueva (opcinelegida por defecto).5.Asegrate de que est activada esta ltima opcin y pulsa en Terminar.Se crea una hoja nueva con la estructura de lo que ser la tabla dinmica. Lo que hayque hacer es "arrastrar" los campos desde la barra que aparece en la parte inferior,hacia la posicin deseada en el interior de la tabla.6.Arrastra los campos Producto y Mes a la posicin que se muestra en la siguientefigura:



7.Arrastra ahora el campo Precio en el interior (ventana grande). Automticamenteaparecer el resultado:

Hemos diseado la estructura para que nos muestre los productos en su parteizquierda, los meses en columnas, y adems, el precio de cada producto en lainterseccin de la columna.

Observa tambin que se han calculado los totales por productos y por meses.Si modificamos algn dato de la tabla original, podemos actualizar la tabla dinmicadesde la opcin Datos - Actualizar datos siempre que el cursor est en el interiorde la tabla dinmica.

Al actualizar una tabla, Excel compara los datos originales. Pero si se han aadidonuevas filas, tendremos que indicar el nuevo rango accediendo al paso 2 delAsistente. Esto podemos hacerlo accediendo nuevamente a Datos - Informe detablas y grficos dinmicos y volviendo atrs un paso.Es posible que al terminar de disear la tabla dinmica nos interese ocultar algnsubtotal calculado. Si es as, debemos pulsar doble click en el campo gris querepresenta el nombre de algn campo, y en el cuadro de dilogo que aparece, elegirla opcin Ninguno. Desde este mismo cuadro podemos tambin cambiar el tipo declculo.

Es posible tambin mover los campos de sitio simplemente arrastrando su botngris hacia otra posicin. Por ejemplo, puede ser que queramos ver la tabla con ladisposicin de los campos al revs, es decir, los productos en columnas y los mesesen filas.

Prueba a mover el Mes y el Producto a la parte izquierda. Vers que ahora seorganiza y suma a travs del mes.



Desde la barra de modificacin de la tabla, podemos realizar operaciones deactualizacin, seleccin de campos, ocultar, resumir, agrupar, etc. Puedes practicarsin miedo los diferentes botones de la barra.

Bsqueda de objetivos.- Hay veces en las que al trabajar con frmulas, conocemosel resultado que se desea obtener, pero no las variables que necesita la frmula paraalcanzar dicho resultado. Por ejemplo, imaginemos que deseamos pedir unprstamo al bando de 2.000.000 de pts y disponemos de dos aos para pagarlo.Veamos cmo se calcula el pago mensual:

La funcin =PAGO(inters/12;perodo*12;capital) nos da la cuota mensual a pagarsegn un capital, un inters y un perodo en aos.

1.Escribe los siguientes datos:

2.Escribe en la celda B5 la frmula: =PAGO(B2/12;B3*12;B1).

3.Quita los decimales.4.Vemos que la cuota a pagar es de 87.296 Pts.

La funcin =PAGO() siempre nos dar el resultado en nmeros negativos. Siqueremos convertirlo en resultado positivo, debemos encerrar la funcin en otrafuncin: la funcin =ABS() Esta funcin convierte cualquier nmero en positivo(valor absoluto)

5. Modifica la funcin y escribe: =ABS(PAGO(B2/12;B3*12;B1))

Ahora podemos variar los valores de las tres casillas superiores para comprobardiferentes resultados. Pero vamos a lo que vamos: imaginemos que slo disponemosde 80.000 pts para pagar cada mes. El banco actual nos ofrece un inters del 4,5%,as que vamos a ver qu inters tendramos que conseguir para llegar a pagar las80.000 que podemos pagar. Podramos ir cambiando manualmente la celda delinters hasta conseguir el resultado requerido, pero a veces hay clculos complejosy nos llevara tiempo ir probando con decimales hasta conseguirlo.

Para ello, tenemos la opcin Buscar objetivos, a travs de la cual Excel nosproporcionar el resultado buscado.

5.Sita el cursor en B5 si no lo est ya.6.Accede a Herramientas - Buscar objetivos.



7.Rellena las casillas como ves a continuacin y acepta el cuadro.

Excel avisa que ha hallado una solucin al problema.8.Acepta este ltimo cuadro de dilogo.

Sin embargo, si observas la celda del inters, aparece en negativo, por lo que elresultado no ha sido el esperado (evidentemente, el banco no nos va a pagar elinters a nosotros), por lo que nos vemos obligados a cambiar otra celda.El capital no podemos cambiarlo. Necesitamos los 2.000.000, as que, vamos aintentarlo con los aos.

9.Deshaz la ltima accin desde 10.Vuelve a preparar las siguientes casillas:

11.Acepta la solucin de Excel.

Observa que han aparecido decimales pero; ya sabemos que podemos cambiar elnmero de meses a pagar si es que no podemos tocar el inters. Quita losdecimales. Necesitaremos dos aos y dos meses.Posiblemente otro banco nos ofrezca un inters ms bajo, por lo que podemosvolver a buscar un nuevo valor para el perodo.Para trabajar con la opcin de Buscar objetivos, hay que tener presente lo siguiente:

-Una celda cambiante (variable) debe tener un valor del que dependa la frmula parala que se desea encontrar una solucin especfica.-Una celda cambiante no puede contener una frmula.-Si el resultado esperado no es el deseado, debemos deshacer la accin.



6. Tablas de datos de una y dos variables[http://www.mailxmail.com/curso-excel-xp-segunda-parte/tablas-datos-dos-variables]Existe otro mtodo para buscar valores deseados llamado tablas de variables.Existen dos tipos de tablas:

-Tabla de una variable: utilizada cuando se quiere comprobar cmo afecta un valordeterminado a una o varias frmulas.

-Tabla de dos variables: para comprobar cmo afectan dos valores a una frmula.

A continuacin, modificaremos la tabla de amortizacin del prstamo de forma queExcel calcule varios intereses y varios aos al mismo tiempo. Para crear una tablahay que tener en cuenta:-La celda que contiene la frmula deber ocupar el vrtice superior izquierdo delrango que contendr el resultado de los clculos.-Los diferentes valores de una de las variables debern ser introducidos en unacolumna, y los valores de la otra variable en una fila, de forma que los valoresqueden a la derecha y debajo de la frmula.-El resultado obtenido es una matriz, y deber ser tratada como tal.

1.Prepara la siguiente tabla. En ella, hemos dispuesto varios tipos de inters y variosaos para ver distintos resultados de una sola vez.

2.Selecciona el rango B5:F9 y accede a Datos - Tabla

3.Rellena las casillas como ves a continuacin y acepta.

4.Debes seleccionar el rango C6:F9 y arreglarlo de forma que no se vean decimales,formato millares y ajustar el ancho de las columnas.



De esta forma, podemos comprobar de una sola vez varios aos y varios tipos deinters.

Escenarios.- Un Escenario es un grupo de celdas llamadas Celdas cambiantes que seguarda con un nombre.

1.Haz una copia de la hoja con la que estamos trabajando y en la copia, modifica losdatos:

2.Accede a Herramientas - Escenarios y pulsa en Agregar.3.Rellena las casillas tal y como ves en la pgina siguiente:

4.Acepta el cuadro de dilogo.5.Vuelve a aceptar el siguiente cuadro de dilogo.6.Vuelve a pulsar en Agregar.



7.Colcales el nombre:

8.Acepta y modifica el siguiente cuadro:

9.Acepta y agrega otro escenario.10.Vuelve a escribir igual que antes:

11.Acepta y modifica la lnea del inters:

11.Acepta.

Acabamos de crear tres escenarios con distintas celdas cambiantes para un mismomodelo de hoja y una misma frmula.

12.Selecciona el primer escenario de la lista y pulsa en Mostrar. Observa elresultado en la hoja de clculo.13.Haz lo mismo para los otros dos escenarios. Mustralos y observa el resultado.Podemos tambin crear un resumen de todos los escenarios existentes en una hojapara observar y comparar los resultados.14.Pulsa en Resumen y acepta el cuadro que aparece.

Observa que Excel ha creado una nueva hoja en formato de sub-totales (o enformato tabla dinmica si se hubiera elegido la otra opcin). Esta hoja puede sertratada como una hoja de sub-totales expandiendo y encogiendo niveles.



7. El programa solver[http://www.mailxmail.com/curso-excel-xp-segunda-parte/programa-solver]El programa Solver se puede utilizar para resolver problemas complejos; creandoun modelo de hoja con mltiples celdas cambiantes.

Para resolver un problema con Solver debemos definir:-La celda objetivo (celda cuyo valor deseamos aumentar, disminuir o determinar)-Las celdas cambiantes (son usadas por Solver para encontrar el valor deseado en lacelda objetivo)-Las restricciones (lmites que se aplican sobre las celdas cambiantes)1.Crea la hoja que viene a continuacin teniendo en cuenta las frmulas de lassiguientes celdas:

B9 =B4-B8 B3 =35*B2*(B6+3000)^0,5B4 =B3*35

(Al margen de las tpicas sumas de totales)

Hemos calculado el beneficio restando los gastos de los ingresos. Por otro lado, losingresos son proporcionales al nmero de unidades vendidas multiplicado por elprecio de venta (35 pts).

2. Observa la frmula de la celda B3

Las unidades que esperamos vender en cada trimestre son el resultado de unacompleja frmula que depende del factor estacional (en qu perodos se esperavender) y el presupuesto en publicidad (supuestas ventas favorables). No tepreocupes si no entiendes demasiado esta frmula.3.Sita el cursor en F9.El objetivo es establecer cul es la mejor distribucin del gasto en publicidad a lolargo del ao. En todo caso, el presupuesto en publicidad no superar las 40.000pesetas anuales.

Resumiendo: queremos encontrar el mximo beneficio posible (F9), variando el



valor de unas determinadas cedas, que representan el presupuesto enpublicidad(B6:E6), teniendo en cuenta que dicho presupuesto no debe exceder las40.000 pesetas al ao.

4.Elige Herramientas - Solver.

La Celda objetivo es aquella cuyo valor queremos encontrar (aumentndolo odisminuyndolo).

El campo Cambiando las celdas indicar las celdas cuyos valores se puedencambiar para obtener el resultado buscado. En nuestro ejemplo sern aquellasceldas donde se muestra el valor del gasto en publicidad para un perododeterminado.

5.Sita el cursor en el campo Cambiando las celdas y pulsa el botn rojo(minimizar dilogo).

6.Introduce (o selecciona con el ratn) el rango B6:E6.

7.Vuelve a mostrar el cuadro de dilogo desde el botn rojo.

A continuacin vamos a aadir las restricciones que se debern cumplir en losclculos. Recuerda que el presupuesto en publicidad no exceder las 40.000 pts.

8.Pulsa el botn Agregar.9.Pulsa F6 en la hoja de clculo.10.Haz click en el campo Restriccin.11.Escribe el valor: 40000.



12.Pulsa el botn Agregar del mismo cuadro de dilogo.Otra restriccin es que el gasto de cada perodo sea siempre positivo.13.Pulsa en el gasto de publicidad del primer perodo B6.14.Elige el operador > = de la lista del medio y completa el cuadro de la siguienteforma:

15.Introduce las dems restricciones correspondientes a los tres perodos que faltande la mima forma.

16.Acepta el cuadro para salir al cuadro de dilogo principal.17.Pulsa en el botn Resolver.

Observa que Excel ha encontrado una solucin que cumple todos los requisitosimpuestos. Ahora podemos aceptarla o rechazarla.

18.Pulsa en Aceptar.

Observa que ahora la hoja de clculo muestra el beneficio mximo que podemosconseguir jugando con el presupuesto en publicidad.Como detalle curioso, observa cmo no deberamos programar ninguna partidapresupuestaria para la publicidad del primer perodo.



Configuracin del Solver.- Desde Herramientas - Solver

(botn Opciones...) tenemos varias opciones para configurar Solver. Las ms importantes son:

-Tiempo mximo: segundos transcurridos para encontrar una solucin. El mximoaceptado es de 32.767 segundos.

-Iteraciones: nmero mximo de iteraciones o clculos internos.

-Precisin: nmero fraccional entre 0 y 1 para saber si el valor de una celda alcanzasu objetivo o cumple un lmite superior o inferior. Cuanto menor sea el nmero,mayor ser la precisin.

-Tolerancia: tanto por ciento de error aceptable como solucin ptima cuando larestriccin es un nmero entero.

-Adoptar modo lineal: si se activa esta opcin, se acelera el proceso de clculo.

-Mostrar resultado de iteraciones: si se activa, se interrumpe el proceso paravisualizar los resultados de cada iteracin.

-Usar escala automtica: se activa si la magnitud de los valores de entrada y los desalida son muy diferentes.



8. Acceso a dotos del exterior[http://www.mailxmail.com/curso-excel-xp-segunda-parte/acceso-dotos-exterior]A veces puede ocurrir que necesitemos datos que, originalmente, se crearon conotros programas especiales para ese cometido. Podemos tener una base de datoscreada con Access o dBASE que son dos de los ms conocidos gestores de bases dedatos y, posteriormente, querer importar esos datos hacia Excel para poder trabajarcon ellos.

Para ello, necesitaremos una aplicacin especial llamada Microsoft Query que nospermitir acceso a datos externos creados desde distintos programas.

Tambin es posible que slo nos interese acceder a un conjunto de datos y no atodos los datos de la base por completo; por lo que utilizaremos una Consulta queson parmetros especiales donde podemos elegir qu datos queremos visualizar oimportar hacia Excel.

Si deseamos acceder a este tipo de datos, es necesario haber instalado previamentelos controladores de base de datos que permiten el acceso a dichos datos. Esto lopuedes comprobar desde el Panel de Control y accediendo al icono:

All, te aparecer un cuadro de dilogo con los controladores disponibles:

Creacin de una consulta de datos.- Para comenzar, es necesario definirpreviamente la consulta que utilizaremos indicando la fuente de datos y las tablasque queremos importar. Si no tienes nociones de la utilizacin de los programasgestores de bases de datos; no te preocupes porque slo vamos a extraer datos deellos.

Veamos cmo hacerlo:



1.Accede a Datos - Obtener datos externos - Nueva consulta de base de datos

Aparecer la pantalla de Microsoft Query. Ahora podemos dar un nombre a lanueva consulta.

2.Pulsa en Aadir y aade los siguientes datos:

3.Haz click en Conectar.4.Click en Seleccionar

Ahora debemos indicarle la ruta donde buscar el archivo a importar. Nosotroshemos elegido la base de datos Neptuno.MDB que viene de ejemplo en lainstalacin de Microsoft Office XP. La puedes encontrar en la carpeta C:\Archivosde programa\ Microsoft Office\ Office\ Ejemplos. Observa la siguiente ilustracin:

5.Selecciona la base de datos NEPTUNO.MDB y acepta.6.Acepta tambin el cuadro de dilogo que aparece (el anterior)7.Selecciona la tabla CLIENTES

8.Acepta los cuadros de dilogo que quedan hasta que aparezca en pantalla elasistente de creacin de consultas tal y como aparece en la pgina siguiente:



9.Carga los campos IdCliente, Direccin, Ciudad y Telfono seleccionando click enel campo y pulsando el botn 10.Pasa al paso Siguiente.

Ahora podemos elegir de entre los campos alguna condicin para la importacin delos datos. Es posible que slo nos interesen los clientes cuya poblacin seaBarcelona. Si no modificamos ninguna opcin, Excel importar todos los datos.

11.Modifica las casillas de la siguiente forma:

12.Pulsa en Siguiente.13.Elige el campo IdCliente como campo para la ordenacin y Siguiente.A continuacin, podramos importar los datos directamente a Excel, pero vamos aver cmo funciona la ventana de Query. Tambin podramos guardar la consulta.14.Elige la opcin Ver datos...

15.Pulsa en Finalizar.



9. Microsoft Query[http://www.mailxmail.com/curso-excel-xp-segunda-parte/microsoft-query]Aparece la pantalla de trabajo de Microsoft Query. Desde esta pantalla podemosmodificar las opciones de consulta, el modo de ordenacin, aadir o eliminarcampos, etc.

Observa las partes de la pantalla, en la parte superior tenemos la tpica barra debotones. En la parte central, el nombre y los campos de la tabla que hemos elegido,as como la ventana de criterios de seleccin; y en la parte inferior, los campos enforma de columna.

Podemos aadir campos a la consulta seleccionndolos de la tabla y arrastrndoloshacia una nueva columna de la parte inferior. En nuestro caso, vemos que slo hayun cliente que cumpla la condicin de ser de la ciudad de Barcelona.

16.Borra el criterio Barcelona de la casilla de criterios.

17.Pulsa el botn Ejecutar consulta ahora situado en la barra de herramientassuperior y observa el resultado.18.Abre el men Archivo y selecciona la opcin Devolver

datos a Microsoft Excel.

19.Acepta el cuadro de dilogo que aparece.

Devolver datos a Excel.- Ahora podemos tratar los datos como si fueran columnasnormales de Excel, pero con la ventaja que tambin podemos modificar algunosparmetros desde la barra de herramientas que aparece.

A travs de esta barra tendremos siempre la posibilidad de actualizar la consulta,haya o no haya ocurrido alguna modificacin en ella.Fjate que es posible porque el programa almacena en un libro de trabajo ladefinicin de la consulta de donde son originarios los datos, de manera que puedaejecutarse de nuevo cuando deseemos actualizarlos.Si desactivamos la casilla Guardar definicin de consulta y guardamos el libro,Excel no podr volver a actualizar los datos externos porque stos sern guardadoscomo un rango esttico de datos.Tambin podemos indicar que se actualicen los datos externos cuando se abra ellibro que los contiene; para ello hay que activar la casilla Actualizar al abrir elarchivo.

Recuerda que, para que sea posible la actualizacin de los datos externos, senecesita almacenar la consulta en el mismo libro o tener la consulta guardada yejecutarla de nuevo.



Impresin de una hoja.- Utilizando la ltima hoja que tenemos en pantalla, veamosqu hacer en el caso de impresin de una hoja. En principio, tenemos el botn Vistapreliminar situado en la barra superior de herramientas; que nos permite obteneruna visin previa del resultado de la hoja antes de imprimir.

1.Accede a esta opcin: observa la parte superior: tenemos varios botones paracontrolar los mrgenes (arrastrando), o bien para modificar las caractersticas de laimpresin (botn Configurar)

2.Accede al botn Configurar.

Desde este cuadro de dilogo, podemos establecer el tamao del papel, orientacinen la impresora, cambiar la escala de impresin, colocar encabezados, etc.

Observa que en la parte superior existen unas pestaas desde donde podemosmodificar todos estos parmetros. Puedes realizar distintas pruebas ycombinaciones sin llegar a imprimir; as como, observar el resultado en la pantallade presentacin preliminar.

Seleccin del rea de impresin.- Es posible seleccionar slo un rango de celdaspara que se imprima. Para hacer esto, sigue estas instrucciones.

1.Selecciona el rango a imprimir2.Ir a Archivo - rea de impresin - Establecer rea de impresin

Correccin ortogrfica.- Excel XP incorpora un corrector ortogrfico que podemosactivar al ir escribiendo texto sobre la marcha o bien una vez hayamos terminado deescribir.

El corrector que acta sobre la marcha podemos encontrarlo en Herramientas -Autocorreccin. En este men, aparece un cuadro de dilogo donde podemosaadir palabras para que Excel las cambie automticamente por otras.

Otro mtodo es corregir, una vez finalizado el trabajo, desde Herramientas -Ortografa. Aparecer un men que nos ir indicando las palabras que Excelconsidera falta de ortografa. Podemos omitirlas o bien cambiarlas por las que nosofrece el programa.

Si elegimos la opcin Agregar palabras a..., podemos elegir el diccionario quequeremos introducir la palabra que no se encuentra en el diccionario principal deExcel. Por omisin, disponemos del diccionario PERSONAL.DIC, que se encuentravaco hasta que le vamos aadiendo palabras nuevas.A partir de introducir una nueva palabra en el diccionario, sta deja de serincorrecta. Hay que hacer notar que Excel comparte los diccionarios con otrasaplicaciones de Office, por lo que si hemos aadido palabras, stas estarndisponibles en una futura correccin desde Word, por ejemplo.



10. Los macros[http://www.mailxmail.com/curso-excel-xp-segunda-parte/macros]En ocasiones, tenemos que realizar acciones repetitivas y rutinarias una y otra vez.En vez de hacerlas manualmente, podemos crear una macro que trabaje pornosotros. Las macros son funciones que ejecutan instrucciones automticamente yque nos permiten ahorrar tiempo y trabajo.

Los pasos para crear una macro son: 1.Acceder a Herramientas - Macro - Grabar macro

2.Pulsar las teclas o tareas, una tras otra, teniendo cuidado de no equivocarnos. 3.Detener la grabacin de la macro. 4.Depurar posibles errores o modificar la macro.

Las macros tambin pueden ejecutarse pulsando una combinacin de teclasespecfica, por lo que ni siquiera debemos acceder a un men para invocar a lamacro, o bien asignrsela a un botn. Cuando creamos una macro, en realidad Excel est creando un pequeo programautilizando el lenguaje comn en aplicaciones Office: el Visual Basic.

Creacin de una macro.-

1.Accede a Herramientas - Macro - Grabar nueva macro. Te aparecer un men:

2.Acepta el nombre propuesto (Macro1) y acepta el cuadro de dilogo. A continuacin, aparecer un pequeo botn desde el que podrs detener lagrabacin de la macro.

A partir de estos momentos, todo lo que hagas (escribir, borrar, cambiar algo...) seir grabando. Debemos tener cuidado, porque cualquier fallo tambin se grabara.



3.Pulsa Control + Inicio

4.Escribe: Das transcurridos y pulsa Intro . 5.En la celda A2 escribe: Fecha actual y pulsa Intro. 6.En la celda A3 escribe: Fecha pasada y pulsa Intro. 7.En la celda A4 escribe: Total das y pulsa Intro. 8.Selecciona con un click la cabecera de la columna A (el nombre de la columna) deforma que se seleccione toda la columna. 9.Accede a Formato - Columna - Autoajustar a la seleccin

10.Pulsa click en la celda B2 y escribe: =HOY(). Pulsa Intro. 11.Escribe: 2 9 / 0 9 / 9 8 y pulsa Intro. 12.Accede a Formato - Celda elige el formato N mero y acepta. 13.Sita el cursor en la celda A1. 14.Pulsa la combinacin de teclas Control + * (se seleccionarn todo el rangono-vaco). 15.Accede a Formato - Autoformato - Multicolor 2 y acepta. 16.Finaliza la grabacin desde el botn Detener grabacin o bien desde el menHerramientas - Macro - Detener grabacin. Ahora vamos a ver si la macro funciona:

1.Colcate en la Hoja2

2.Accede a Herramientas - Macro - Macros.

3.Elige tu macro y pulsa el botn Ejecutar. 4.Observa su comportamiento.

La macro ha ido realizando paso a paso todas las acciones que hemos preparado.

Creacin de una macro ms compleja.- La creacin de macros no se limita apequeas operaciones rutinarias como acabamos de ver en el ltimo ejemplo;podemos crear macros ms complejas que resuelvan situaciones complicadas deformateo y clculo de celdas que nos ahorrarn mucho trabajo. Excel crea sus macros utilizando el lenguaje comn de programacin de loscomponentes de Office: el Visual Basic; por lo que, si tenemos idea de dicholenguaje, podremos modificar el cdigo de la macro manualmente. Pero vamos a crear una macro ms completa. Supongamos que queremos conseguirun informe mensual de una tabla de datos de ventas, aadiendo columnas,clasificndolas, imprimirlas, clasificarlas con otros criterios, etc. Tendrs que abrir elfichero que se adjunta en esta leccin y trabajar con l.



1.Abre el fichero Pedidos.XLS haciendo un click sobre el nombre de dicho archivo. Ala hora de descargarte el archivo, no hagas caso de las advertencias, ya queest comprobado que el contenido de dicho archivo no contiene ningn virus.

2.Observa sus dos hojas: Precios y Pedidos.

Imagina que se trata de una empresa textil que tiene que elaborar una macro querealice tareas de fin de mes. La hoja nos muestra una clasificacin por estados,canales (minorista y mayorista), categoras, precios y cantidad. La macroautomatizar el trabajo de forma que cada mes podremos recoger un informe de lospedidos de mes anterior extrayndolo del sistema de proceso de pedidos.

El secreto de una macro larga es dividirla en varias macros pequeas y luego unirlas.Si intentamos crear toda una gran macro seguida, habr que realizar cuatrocientospasos, cruzar los dedos, desearse lo mejor y; que no hayan demasiados fallos.

La hoja que hemos recuperado nos muestra las unidades y totales netos. Lospedidos del mes anterior, Marzo de 1994, se encuentran en la hoja 2. Como vamosa crear una macro, y estamos sometidos al riesgo de fallos, vamos a crear una copiade nuestra hoja. De todas formas, aunque la macro funcione perfectamente,tendremos una copia para practicar con ella.

1.Crea una copia de la hoja Pedidos (arrastrndola hacia la derecha con la tecla decontrol pulsada).



11. Primera tarea: rellenar etiquetas perdidas[http://www.mailxmail.com/...-excel-xp-segunda-parte/primera-tarea-rellenar-etiquetas-perdidas]Cuando el sistema de pedidos produce un informe, introduce una etiqueta en una columnala primera vez que aparece la etiqueta. Vamos a crear la macro. Te pedimos que prestesatencin a las acciones que vamos creando y su resultado en pantalla.

1.Crea una nueva macro con el nombre: RellenarEtiquetas y acepta.

Pasos de la macro:

1.Pulsa Ctrl + Inicio para situar el cursor en la primera celda.2.Pulsa Ctrl + * para seleccionar el rango completo.3.Pulsa F5 (Ir a...)

4.Pulsa el botn Especial de ese mismo cuadro de dilogo.5.Activa la casilla Celdas en blanco y acepta.6.Escribe: =C2 y pulsa Ctrl + Intro.

7.Pulsa Ctrl + Inicio

8.Pulsa Ctrl + *.

9.Elige Edicin - Copiar (o el botn Copiar).10.Elige Edicin - Pegado

especial....

11.Activa Valores y acepta.12.Finaliza la grabacin.

Hemos utilizado combinaciones de teclas y mtodos rpidos de seleccionar y rellenar celdaspara agilizar el trabajo.Observa que hemos finalizado la macro sin desactivar la ltima seleccin de celdas. Conuna simple pulsacin de la tecla Esc y despus mover el cursor, habra bastado, pero lohemos hecho as para que puedas ver cmo se modifica una macro.

1.Elimina la hoja copia de Pedidos.2.Vuelve a crear otra copia de Pedidos.3.Ejecuta la macro en la hoja copia.

Si todos los pasos se han efectuado correctamente, la macro debera funcionar sinproblemas.

4.Vuelve a borrar y crear otra copia de Pedidos.

Ver el cdigo de la macro.- Hemos dicho que Excel trabaja sus macros bsicamente en ellenguaje comn Visual Basic. Veamos qu ha sucedido al crear la macro a base depulsaciones de teclas y teclear texto:



1.Accede a Herramientas - Macros - Editor de Visual Basic

Te aparecer una pantalla especial dividida en tres partes:

-Pantalla de proyecto: es donde se almacenan los nombres de las hojas y las macros quehay creadas.

-Pantalla de mdulos: un mdulo es una rutina escrita en Visual Basic que se almacena enforma de archivo y que puede ser utilizada en cualquier programa.

-Pantalla de cdigo: aqu es donde podemos escribir y modificar el cdigo de la macroactual.

1.En la pantalla de Proyecto, pulsa doble click en Mdulos y luego en Mdulo 1. Aparecerel cdigo Visual Basic en la parte derecha.

Si ya conoces Visual Basic.- Si ya has programado con Visual Basic vers que el sistema paraExcel es prcticamente idntico. No tendrs demasiados problemas en comprender las



sentencias de programacin.

Si no conoces Visual Basic: aunque este curso no trata de programacin, puede servirtecomo iniciacin a la misma aunque no hayas hecho nunca. De esta forma, te pones encontacto con Visual Basic, uno de los ms extendidos lenguajes mundialmente.

Normalmente, una rutina en lenguaje Visual Basic de macros, se lee de derecha a izquierda.Fjate que comienza con la sentencia Sub

RellenarEtiquetas(), esto es, la orden Sub y elnombre de la macro. Fjate tambin que la rutina finaliza con la orden End Sub. Todas lasrdenes contenidas entre ellas son las secuencias de pulsaciones que has ido ejecutando enla creacin de la macro.

Recuerda que la primera pulsacin fue ir a la primera celda con la combinacin Ctrl +Inicio . Observa la traduccin en Visual Basic:

Range("A1").SelectSelection.CurrentRegion.SelectSelecciona la regin actual de la seleccin original. Selection.SpecialCells(xlCellTypeBlanks).SelectSelecciona las celdas en blanco de la seleccin actual.Selection.FormulaR1C1 = "=R[-1]C"Significa: "La frmula para todo lo seleccionado es...". La frmula =L(-1) significa: "leer elvalor de la celda que se encuentra justo encima de m".

Cuando utilizamos Ctrl + Intro para rellenar celdas, la macro tendr la palabra Selectiondelante de la palabra Frmula. Cuando se introduce Intro para rellenar una celda, la macrotendr la palabra ActiveCell delante de la palabra Frmula.

El resto de sentencias de la macro, convierten las frmulas en valores. Observa el resto desentencias y relacinalos con las pulsaciones que has ido realizando en la creacin de lamacro. Recuerda leerlas de derecha a izquierda.



12. Ampliacin de la macro[http://www.mailxmail.com/curso-excel-xp-segunda-parte/ampliacion-macro]En este e-mail veremos cmo se amplia una macro.

1.Cierra la ventana del editor de Visual Basic.2.Accede a Herramientas - Macro - Macros.

3.Selecciona la macro y pulsa en el botn Opciones.4.Asigna la letra r como combinacin de teclas de la macro y acepta.5.Cierra el ltimo cuadro de dilogo.6.Accede a Herramientas - Macro - Editor de Visual Basic

7.Aade al final del cdigo y antes del fin de la rutina End Sub las siguientes lneas:

Application.CutCopyMode = False

Range("A1").Select

8.Cierra y ejecuta de nuevo la macro.

Observa que las ltimas lneas hacen que el modo de Copiar se cancele y el cursorvuelva a la celda A1. Es lo mismo que si hubisemos pulsado la tecla Esc y Ctrl +Inicio cuando grabbamos la macro.

Ver cmo trabaja una macro paso a paso.- La ejecucin de una macro es muyrpida. A veces nos puede interesar ver paso a paso lo que hace una macro, sobretodo cuando hay algn fallo, para localizarlo y corregirlo.

1.Elimina y vuelve a hacer otra copia de la hoja actual.2.Accede a Herramientas - Macro - Macros

3.Selecciona la macro y pulsa en el botn Paso a paso.

Observa cmo la macro se ha detenido en la primera lnea y la ha marcado en coloramarillo.

4.Ve pulsando la tecla F8 y observa cmo la macro se va deteniendo en lasdiferentes lneas de la rutina.5.Finalmente, cierra la ventana de cdigo.

Segunda tarea: aadir columnas de fechas.- Nuestro informe no incluye la fecha encada fila, por lo que vamos a aadir una nueva columna para aadir el mes de cada



registro.

1.Ejecuta la macro en la nueva hoja copiada.2.Crea una nueva macro con el nombre: AadirFecha y acepta.

Pasos de la macro:

1.Sitate en la celda A1.

2.Accede a Insertar - Columnas.

3.Escribe: Fecha y pulsa Intro.4.Vuelve a la celda y convirtela en formato negrita.5.Selecciona el rango A2:A179

6.Escribe: Mar -98 y pulsa Ctrl + Intro.

7.Pulsa Ctrl + Inicio y finaliza la grabacin.8.Borra la hoja.9.De la hoja original, haz una copia.10.Ejecuta las dos macros en el orden que las hemos creado.

Evidentemente, cada vez que ejecutemos la macro, Excel rellenar las celdas recincreadas con la palabra "mar-98". Una solucin sera cambiar la macro cada mes conla nueva fecha, pero no parece la solucin ms adecuada. Vamos a hacer que elprograma nos pida el mes y posteriormente lo rellene l.

Peticin de datos al usuario.-

1.Accede al cdigo Visual Basic de la ltima macro creada.2.Selecciona el texto "mar-98" (comillas incluidas)

3.Pulsa la tecla Supr para borrarlo.4.Escribe en su lugar: InputBox

("Introduce la fecha en formato MM-AA: ")

5.Sal del cuadro de dilogo y ejecuta la macro de nuevo.En alguna hoja copia eloriginal. En alguna hoja copia el original, o bien borra la columna A de la ltimahoja y ejecuta la macro.6.Cuando te pida la fecha, escribe por ejemplo: 4 - 1 1

La orden InputBox es una funcin de Visual Basic que visualiza un cuadro con unmensaje personalizado para la entrada de datos cuando se est ejecutando la macro.



13. Aadir columnas calculadas[http://www.mailxmail.com/curso-excel-xp-segunda-parte/anadir-columnas-calculadas]Esta es la tercera tarea que consiste en aprender a aadir columnas calculadas.

Observa que en la hoja tenemos tres precios por diseo: Bajo, Medio y Alto. Siqueremos comparar el valor de los pedidos sin descuento con el de los mismos condescuento, precisaremos aadir en cada fila la lista de precios. Una vez hayamosobservado la lista de precios de cada fila, podremos calcular el importe total de lospedidos, multiplicando las unidades por los precios.

Finalmente, convertiremos las frmulas en valores como preparacin para aadir lospedidos al archivo histrico permanente.

1.Crea una nueva macro llamada: AadirColumnas.

2.Pulsa F5, ve a la celda H 1 utilizando este cuadro y escribe en esa celda: Tarifa.3.Ve a la celda I 1 y escribe: Bruto.4.Ve a la celda H2 y escribe la siguiente frmula: =BUSCARV(E2;Precios!$A$2:$C$4;SI('Pedidos'!C2="Minorista";2;3))

5.Ve a la celda I 2 e introduce: =F2*H2. Pulsa Intro.

6.Selecciona el rango de celdas H2:I179

7.Accede a Edicin - Rellenar - Hacia abajo

8.Pulsa Ctrl + Inicio

9.Finaliza la grabacin de la macro.En la celda H 2 aparece el valor 4.5. Esta frmula busca el precio Medio (E2) de laprimera columna del rango A2:C4 de la hoja Precios. A continuacin devuelve elvalor de la columna nmero 2 de la lista por ser Minorista la celda C2. El precio parala venta Minorista de un diseo con un precio Medio es de 4.50 dlares.

Para comprobar su funcionamiento:

10.Borra las dos columnas H e I y ejecuta la macro.Las frmulas de BUSCARV son an frmulas. En nuestro archivo histrico depedidos, no debemos aadir frmulas, sino resultados. Vamos a transformar lasfrmulas en valores.

11.Crea una nueva macro llamada: ConvertirValores.

12.Selecciona el rango H2:I179.13.Cpialo al portapapeles.



14.Ve a Edicin - Pegado especial.

15.Selecciona Valores y acepta.16.Finaliza la grabacin de la macro.

Cuarta tarea: Ajustar columnas y abrir histrico de pedidos.- Finalmente, queremosaadir los nuevos pedidos del mes al archivo histrico acumulativo de pedidos.Necesitamos asegurarnos de que las columnas de los nuevos pedidos del mes seajustan adecuadamente a las columnas del archivo de pedidos.

El archivo histrico de pedidos es un archivo en formato del programa dBASE (dbf)que cre nuestro compaero Pepito del departamento de Facturacin. Vamos aabrirlo desde Excel para manipularlo.

1.Abre el archivo Pedidos.dbfhaciendo un clic sobre el nombre de dicho archivo.Debers elegir el tipo de archivo dbf:

2.Observa las cabeceras de las columnas del archivo histrico; son diferentes.Puedes organizarte las dos ventanas para compararlas. Observa que el orden de lascolumnas Categora y Precio no coincide una hoja con otra. Adems, las etiquetas de Unidades y BrutoUnidades y Bruto son diferentes.

3.Crea una nueva macro llamada: FijarColumnas.

4.Selecciona con un click la cabecera de la columna E del libro Pedidos.xls y elige EdiciEdicin - Cortar.

5.Pulsa una vez sobre la cabecera de la columna D para seleccionarla y elige Insertar - Cortar celdas.Insertar - Cortar celdas.



6.Ve a la celda F1 (contiene la palabra Cantidad), escribe en su lugar: Neto y pulsas Intro.

7.Finaliza la grabacin.8.Comprueba el funcionamiento de la macro. Quiz debas hacer una copia de lahoja anterior.

Quinta tarea: Unificar los pedidos.- La ltima hoja con la macro ejecutada, posee undiseo de columnas igual que el archivo histrico. Vamos a aadir la hoja a partir dela primera lnea en blanco de la parte inferior del archivo.

1.Activa el libro Pedidos.dbf

2.Ve a la celda A1 y pulsa Ctrl + *

3.Comprueba el nombre del rango en la casilla de nombres:

4.Crea una nueva macro llamada AmpliarBaseDatos.

5.Pulsa Ctrl + Inicio.

6.Sitate en la primera celda en blanco del rango pulsando las teclas Fin, Flechaabajo y de nuevo la Flecha abajo.

7.Pulsa Ctrl + Tabulador para volver a la hoja Pedidos.xls.8.Selecciona la celda A2.9.Dejando pulsada la tecla Shift, pulsa las teclas: Fin, Flecha abajo, Fin, Flechaderecha.

10.Pulsa Ctrl + C para copiar las celdas al portapapeles.11.Pulsa Ctrl + Shift + Tab para volver al libro Pedidos.dbf.

12.Pulsa Ctrl + V para pegar el contenido del portapapeles.13.Pulsa Esc para cancelar el estado de copia.14.Pulsa Ctrl + * para seleccionar todo el rango de datos.15.Accede a Insertar - Nombre - Definir para volver a definir el nombre del rangonuevo.

16.Escribe Base_de_datos

NOTA fjate que no hemos elegido el mismo nombre que tena antes pulsando sobreel nombre que aparece en la ventana, sino que hemos definido un nuevo nombrepara el rango. Si hubiramos elegido el mismo nombre que tena, Excel guardara laantigua definicin.

17.Accede a Cerrar del men Archivo.



NOTA en un caso real, ahora podramos elegir la orden de Guardar, pero en estecaso, al ser una macro de prueba, no grabaremos ningn cambio.

18.Pulsa en No para cancelar el guardado.19.Finaliza la grabacin de la macro.

Enlazar todas las macros.- Llega el momento de la verdad. Vamos a crear una macroque ejecute una a una las dems macros que hemos preparado. Si te has aseguradode que cada macro por separado funciona, no debe haber ningn problema.

1.Debers dejar slo el libro Pedidos.xls a la vista.2.Deja tambin una copia de la hoja Pedidos para probar las macros.3.Crea una nueva macro llamada: HacerTodo.

Pasos de la macro:

1.Accede a Herramientas - Macros - Macro

2.Elige de la lista de macros RellenarEtiquetas y acepta.3.Haz exactamente lo mismo para las dems macros en este orden:

AadirFecha (cuando te pida la fecha, introduce: 0 5 - 1 1)AadirColumnas

FijarColumnas

AmpliarBaseDatos

4.Finaliza la grabacin de la macro.

Como ya hemos dicho, en un caso real, la ltima pregunta de si queremos guardarel libro Pedidos.dbf contestaramos que s.



14. Macro para crear una tabla dinmica[http://www.mailxmail.com/curso-excel-xp-segunda-parte/macro-crear-tabla-dinamica]En esta leccin continuaremos profundizando en el estudio de las macros ycrearemos nuevas para nuestra hoja de Pedidos.xls.

En tu capacidad de contable y analista de la empresa cuya hoja utilizamos en lapasada leccin, te habrs sentido admirado de cmo se distribuyen en las diferenteslneas de diseo de camisetas en las diferentes reas geogrficas de Amrica y porlos diferentes canales de ventas.

Vamos a crear una tabla dinmica que muestre las unidades de los pedidos porcategoras, resaltando celdas que contengan ventas excepcionales. Ms adelantecrearemos otra tabla para producir grficos.

Macro para crear una tabla dinmica de referencias cruzadas.-

1.Sin nada en pantalla, abre la hoja Pedidos.dbf para abrir nuestra base de datoshistrica de pedidos que realizamos en la leccin anterior.2.Accede a Datos - Informe de tablas y grficos dinmicos.

3.En el paso 1, pulsa en Siguiente.4.En el paso 2, selecciona todo el rango de datos y pulsa en Siguiente.5.En el paso 3 finaliza y despus coloca los campos como sigue:

6.Pulsa en Siguiente.7.En el ltimo paso, acepta de forma que la tabla se cree en una nueva hoja.

8.Baja el zoom al 75%

9.Cmbiale el nombre a la hoja por el de: Tabla dinmica.

10.Desde la opcin Archivo - Guardar como... guarda el libro con el nombre: CategorCategoras.xls (asegrate de que guardas con formato XLS).



La tabla muestra una informacin global de los productos, pero vamos a ver larelacin que existe entre las distintas categoras de diseo. Para ello, convertiremosla tabla para que produzca en porcentajes y as poder comparar mejor la relacinexistente.

11.Ve a la celda A1.

12.Pulsa sobre el botn Configuracin de campo de la barra de herramientas:

Aparece el cuadro de dilogo del campo de la tabla con informacin sobre el campo Suma de unidades.Suma de unidades.

13.Pulsa sobre el botn Opciones para expandir el cuadro de dilogo.14.Selecciona de la lista la opcin Mostrar datos como... - % de la fila.

15.Selecciona la palabra Suma del nombre del cuadro y sustityelo por Porcentajes:

16.Sal del cuadro aceptando los cambios.

Observa cmo los datos se han convertido a porcentajes. La columna de la derechavisualiza los porcentajes al 100%. Vamos a hacer que no se visualicen:

17.Selecciona cualquier celda de la columna K.18.Accede a Formato - Columna - Ocultar.

Ahora nadie podr ver que el total es el porcentaje 100% del total de la fila.

Crear una macro que marque las excepciones manualmente.- Imaginemos quequeremos marcar en color amarillo todas aquellas celdas cuya cantidad sea superioral nmero 30. Manualmente, si la hoja es muy grande, puede ser un trabajo mortal.

1.Selecciona la celda D3.

2.Abre la paleta porttil de colores y selecciona el color amarillo. (El sexto color). Elfondo se convertir en amarillo.3.Busca hacia abajo en la columna D para la siguiente columna con valor superior al



30%, es decir, la celda D 7, y cambia su fondo a amarillo igual que la celda anterior.

Dar formato a una celda para que disponga de color y un aspecto especial puede serdivertido las dos o tres primeras veces. Pero cuando se repite la misma accin una yotra vez, puede ser bastante aburrido.Vamos a crear una macro que mirar si la celda es superior a un valor. Si lo es, ledar el color amarillo de fondo.

1.Crea una nueva macro y la llamas: FormatoCelda.

2.En Opciones, asgnale la combinacin Ctrl + K

3.Coloca el fondo amarillo.4.Finaliza la grabacin de la macro.5.Sita el cursor en cualquier celda con valor superior a 30%6.Pulsa Ctrl + K

Evidentemente, esto es como hacerlo manualmente, pero con una combinacin deteclas que llame a una macro. Veamos cmo modificarla:

7.Accede a Herramientas - Macro - Macros, selecciona la macro y pulsa enModificar.

8.Observa el cdigo. Siempre har lo mismo.9.Modifcalo aadiendo estas lneas:

La rutina If...Then - End If comprueba si la condicin que sigue a I f es cierta. Si loes, se ejecutan las sentencias del interior. Si no lo es, no se ejecutan. Esta ordendebe acabar con la sentencia End If.

10.Cierra la ventana del editor y sita el cursor sobre alguna celda cuyo valor nopase del 30%. Ejecuta la macro pulsando Ctrl + K y observa que no aparece el colorde fondo.

11.Haz lo mismo con cualquier celda que s pase del 30%.La macro va tomando cuerpo, pero todava tenemos que desplazar el cursormanualmente y mirar si el contenido de la celda es superior a la condicinestablecida.

Vamos a hacer que el cursor se desplace automticamente una celda hacia abajo.Para ello, utilizaremos la orden offset (fila,columna).

12.Agrega estas lneas:



12.Agrega estas lneas:



15. Cmo hacer que un macro se repita[http://www.mailxmail.com/curso-excel-xp-segunda-parte/como-hacer-que-macro-se-repita]Hacer que la macro se repita mediante un bucle.- Con esto, conseguiramos que elcursor se desplazase una fila hacia abajo, pero luego se parara. Tendramos que irpulsando Ctrl + K constantemente. Debemos crear un bucle controlado de formaque la macro se ejecute una y otra vez hasta que nosotros lo decidamos.

Para ello, crearemos un procedimiento personalizado en el que se crear un bucleque contendr la macro:

Procedimiento

Comienzo del bucle

Macro

Fin del bucle y volver a comenzar bucle

Fin del procedimiento

Ahora bien, cmo sabe l cuando tiene que parar el bucle? Evidentemente nocontinuar hasta la fila 65.536. Cundo debe parar? Cuando encuentre la primeracelda vaca. En ese momento parar.

Procedimiento

Comienzo del bucle. Repetir bucle hasta que celda activa = ""

Macro

Fin del bucle y volver a comenzar bucle

Fin del procedimiento

Su equivalente en lenguaje basic sera:

El bucle Do Until...Loop (repetir hasta que se cumpla la condicin) verifica que cadavuelta se vaya comprobando que la condicin no se cumple. En el momento en quese cumple, es decir, en que la celda activa no contiene nada (""), se detiene el bucle.Descubre miles de cursos como ste en www.mailxmail.com 49


se cumple, es decir, en que la celda activa no contiene nada (""), se detiene el bucle.

13.Modifica el cdigo de la macro como este ltimo ejemplo, sitate en la celda D3 yejecuta la macro.

A que ya va pareciendo otra cosa? No obstante continan los inconvenientes. Lamacro se detiene. Tendramos que volver a situar el cursor en la primera celda acomprobar de la segunda columna. Vamos a desplazar la celda activa para que sesite automticamente en la siguiente columna.

Podramos, al finalizar el bucle, aadir la siguiente lnea:

LoopRange("E3").Select

End Sub

Y Excel situara el cursor automticamente en la siguiente columna. A continuacinslo quedar volver a ejecutar la macro. El problema viene cuando haya que volver aejecutarla en la siguiente columna; el cursor volver a la celda E3.

Vamos a aadir lneas de cdigo que desplacen el cursor hacia arriba y lo siten enla siguiente celda con un valor numrico. Corresponde a las pulsaciones Flechaderecha, Flecha arriba, Fin, Flecha arriba, Flecha abajo que seran las encargadasde situar el cursor en la siguiente columna.

ActiveCell.Offset(0, 1).Activate ActiveCell.Offset(-1, 0).Activate Selection.End(xlUp).Select ActiveCell.Offset(1, 0).Activate

De esta forma, controlamos la posicin del cursor de forma que se site en laprimera celda numrica de la siguiente columna.

1.Modifica la macro de esta forma.

2.Prueba la macro.

3.En la siguiente columna, vuelve a ejecutar la macro.

La macro debera pasar siempre de una columna a otra.



16. Anexo[http://www.mailxmail.com/curso-excel-xp-segunda-parte/anexo]A continuacin te ofrecemos ejemplos de estructuras de diferentes bucles.-Do While...Loop: seguir en el bucle mientras o hasta una condicin se cumpla.

Dim Comprobar, Contador ' Creamos dos variables.Comprobar = True: Contador = 0 ' Inicializa su valor.Do ' Bucle externo.

Do While Contador < 20 ' Bucle interno.Contador = Contador + 1 ' Incrementa el contador.If Contador = 10 Then ' Si la condicin es verdadera.Comprobar = False ' Establece el valor a False.Exit Do ' Sale del bucle interno.End If

Loop Loop Until Comprobar = False ' Sale inmediatamente del bucle externo.

-For...Next: utilizar un contador para ejecutar las instrucciones un nmerodeterminado de veces.

For j = 0 To 10 ' Bucle controlado. Se repetir 10 vecesinstrucciones

Next j

-For Each...Next: repeticin del grupo de instrucciones para cada uno de los objetosde una coleccin.

For Each frm In Application.Forms If frm.Caption Screen.ActiveForm.Caption Then frm.Close Next



-While... Wend: ejecuta una serie de instrucciones mientras una condicin seaverdadera.

Dim Contador ' Creamos una variable.Contador = 0 ' Inicializa la variable con el valor 0While Contador < 20 ' Comprueba el valor del Contador.Contador = Contador + 1 ' Incrementa Contador.Wend ' Finaliza el bucle End While cuando Contador > 19.Debug.Print Contador ' Imprime 20 en la ventana Depuracin.



17. La funcin =PAGO()[http://www.mailxmail.com/curso-excel-xp-segunda-parte/funcion-pago]La funcin =PAGO() calcula los pagos peridicos que tendremos que "soltar" sobreun prstamo, a un inters determinado, y en un tiempo x. Os ir de maravilla a losque queris pedir un prstamo o ya lo estis pagando. Podremos ver cuantotendremos que pagar mensualmente, o cuanto nos clavan los bancos de intereses.Nos permitir jugar con diferentes capitales, aos o tipos de inters. La sintaxis dela orden es:

=PAGO(Inters;Tiempo;Capital)

Esta frmula nos calcular el pago anualmente. Si queremos saber los pagosmensuales tendremos que dividir el inters por 12 y multiplicar el tiempo por 12.Observa:

=PAGO(Inters/12;Tiempo*12;Capital)

Ejemplo:

Supongamos que hemos de calcular los pagos mensuales y anuales peridicos delsiguiente supuesto:

Celda B5: =PAGO(B2;B3;B1)

Celda B6: =PAGO(B2/12;B3*12;B1)

Observa que la frmula PAGO ofrece un resultado en negativo (rojo). Si queremosconvertir el resultado en un nmero positivo, debemos encerrar la funcin dentro deotra funcin: =ABS(). La funcin ABS significa absoluto. Un nmero absoluto de otronmero, siempre ser positivo. La frmula en ese caso sera: =ABS(PAGO(B2/12;B3*12;B1))

Como ya hemos dicho, en este tipo de hojas podemos probar a cambiar cantidadesde las celdas B1,B2 y B3 y comprobar los distintos resultados. A continuacin tienesun completo e interesante ejemplo de un supuesto de crdito desglosado mes ames. En este ejemplo se utiliza una funcin nueva: =PAGOINT(), que desglosa elinters que pagamos de la cantidad mensual.



La funcin =PAGO() nos muestra lo que debemos pagar, pero no nos dice cuantopagamos de capital real y de intereses. La funcin =PAGOINT() realiza esto ltimo.

Colocaremos y comentaremos las frmulas de las dos primeras filas. A partir de lasegunda fila, slo restar copiar las frmulas hacia abajo. Supongamos un crdito de2.000.000 de pts con un inters del 8,5% en un plazo de 2 aos, es decir, 24 meses.

Observa la primera lnea de frmulas:

- A 6 Nmero de mes que se paga

- B 6 Clculo del pago mensual con la funcin =ABS(PAGO($B$2/12;$B$3*12;$B$1))=ABS(PAGO($B$2/12;$B$3*12;$B$1))

- C 6 Restamos la cantidad pagada de los intereses y tenemos el capital real quepagamos = B 6 - D 6

- D 6 Desglose del inters con la funcin =ABS(PAGOINT(B2/12;1;B3*12;B1))

-E6 El primer mes tenemos acumulado el nico pago de capital real = C 6

- F 6 Pendiente nos queda el capital inicial menos el que hemos pagado en el primerpago =B1-E6

Bien, ahora hemos de calcular el segundo mes. A partir de ah, slo habr quecopiar la frmula hacia abajo.

Las celdas que cambian en el segundo mes son:

-D7 =ABS(PAGOINT($B$2/12;1;$B$3*12;F6)) Calculamos el pago sobre el capitalpendiente (F6) en vez de sobre el capital inicial como en el primer mes (B1).Convertimos las celdas B2 y B3 en absolutas, ya que copiaremos la funcin haciaabajo y queremos que se actualice slo la celda F6 a medida que se copia la frmula.

-E7 El acumulado del mes ser igual al acumulado del mes anterior ms el capital



del presente mes. =E6+C7

- F 7 Nos queda pendiente el capital pendiente del mes anterior menos el capital quepagamos el presente mes. =F6-C7

Ahora slo nos queda seleccionar toda la segunda fila y copiarla hacia abajo, hastala fila 29, donde tenemos la fila del ltimo mes de pago.



18. Resultado completo de la hoja[http://www.mailxmail.com/curso-excel-xp-segunda-parte/resultado-completo-hoja]Observa cmo a medida que vamos pagando religiosamente nuestro prstamo, losintereses se reducen, hasta que el ltimo mes no pagamos prcticamente nada deintereses. Observa las sumas al final de la hoja que nos informan del total deintereses que hemos "soltado": al final del prstamo, hemos pagado 181.872 pts deintereses:

Trabajo con botones de control.- En esta leccin veremos cmo se programanbotones de control. La utilizacin de los controles en forma de botn agilizan elmanejo de las hojas de clculo. Antes que nada debemos activar la barra de botones(si no lo est ya). La barra se activa con la opcin Ver - Barras de herramientas yactivando la casilla Formularios.

Vamos a disear una hoja de clculo de prstamo para un coche. Supongamos quetenemos la siguiente hoja de clculo con las frmulas preparadas.

Comentario de las celdas:

B1: aqu introducimos manualmente el precio del cocheB2: la reduccin puede ser un adelanto en pts del precio total del coche. Se reflejaen porcentaje.B3: Frmula =B1-(B1*B2), es decir, lo que queda del precio menos el adelanto. Eseser el precio.



B4 y B5: el inters y el nmero de aos a calcular.B6: Frmula =ABS(PAGO(B4/12;B5*12;B3)). Calcula el pago mensual tal y comovimos en la leccin anterior.

Esta hoja sera vlida y podra calcular los pagos peridicos mensuales. Tan slotendramos que introducir o variar las cantidades del precio, reduccin, inters oaos. El problema viene cuando en esta misma hoja podemos:

-Introducir cantidades desorbitantes como 1.500.000.000.000.000

-Borrar sin querer alguna celda que contenga frmulas

-Introducir palabras como "Perro" en celdas numricas

-Otras paranoias que se nos ocurran

Lo que vamos a hacer es crear la misma hoja, pero de una forma ms "amigable",sobre todo para los que no dominan mucho esto del Excel. La hoja ser msatractiva a la vista, ms cmoda de manejar, y adems no nos permitir introducirbarbaridades como las anteriormente expuestas. Para ello utilizaremos los controlesde dilogo.

Bien, supongamos que hemos creado una lista de coches con sus correspondientesprecios, tal que as:

Fjate que hemos colocado el rango a partir de la columna K. Esto se debe a quecuando tengamos la hoja preparada, este rango "no nos moleste" y no se vea. Esterango de celdas comienza a la misma altura que el anterior, es decir, en la fila 1.Ahora haremos lo siguiente:

1. Selecciona el rango entero (desde K1 hasta L6)2. Accede al men Insertar - Nombre - Crear y desactiva la casilla Columnaizquierda del cuadro de dilogo que aparece.3. Acepta el cuadro de dilogo.

Con esto le damos el nombre Coche a la lista de coches y el de Precio a la lista deprecios. Estos nombres nos servirn ms adelante para incluirlos en frmulas, deforma que no utilicemos rangos como D1:D6, sino el nombre del mismo (Coche).

Vamos ahora a crear una barra deslizable que nos servir para escoger un coche dela lista.



la lista.

1. Pulsa un click en el botn (Cuadro combinado)2. Traza un rectngulo desde la celda D 2 hasta la celda E2

3. Coloca un ttulo en D 1: Coche

Observa ms o menos el resultado hasta ahora:

Es muy importante resaltar el hecho de que en este cuadro de dilogo, si pulsamosun click fuera, al volver a colocar el ratn sobre el mismo, aparecer una mano paraposteriormente utilizarlo. Si queremos editarlo para modificarlo, hemos de pulsarun click manteniendo la tecla de Control del teclado pulsada. Una vezseleccionado, pulsaremos doble click para acceder a sus propiedades.

1.Pulsa doble Click (manteniendo Control pulsada) sobre el cuadro que acabamosde crear y rellena el cuadro de dilogo que aparece con las siguientes opciones:

-Rango de entrada: Coche

-Vincular con la celda: H2

-Lneas de unin verticales: 8

Qu hemos hecho? En la opcin Rango de entrada le estamos diciendo a estecuadro de dilogo que "mire" en el rango que hemos definido como Coche, es decir:K2:K6 o lo que es lo mismo, los precios. De esta forma, cuando abramos esta listaque estamos creando y escojamos un coche, aparecer un nmero en la celda H 2.Este nmero ser la posicin en la lista que se encuentra el coche que hayamosescogido. Por ejemplo, si desplegamos la lista y escogemos el coche Ford, apareceren la celda H2 el nmero 2. Puedes probarlo. Pulsa un click fuera del cuadro de listapara poder utilizarlo. Cuando salga el dedito, abre la lista y escoge cualquier coche.Su posicin en la lista aparecer en la celda H2. Esta celda servir como celda decontrol para hacer otro clculo ms adelante. De igual forma, si escribiramos unnmero en la celda H2, el nombre del coche aparecera en la lista desplegable.



19. Recuperacin del precio de la lista[http://www.mailxmail.com/curso-excel-xp-segunda-parte/recuperacion-precio-lista]Recuperacin del precio de la lista.-

1.Selecciona la celda B1 y escribe: =INDICE(Precio;H2)

Observa que en la celda aparece el precio del coche escogido en la lista desplegable.Esto es gracias a la funcin =INDICE. Esta funcin busca el nmero que haya en lacelda H 2 en el rango Precio y nos devuelve el contenido de ese mismo rango. Deesta forma slo encontraremos coches de una lista definida con unos precios fijos.As no hay posibles equivocaciones.

Limitacin de la reduccin para validar valores.- Por desgracia an podemosintroducir un porcentaje inadecuado para la reduccin del precio.

1.Pulsa un click en la herramienta Control de nmero y crea un control ms o menoscomo ste:

2.Con la tecla de control pulsada, haz doble click sobre el control recin creado paraacceder a sus propiedades.3. Rellena las casillas con los siguientes datos:

Valor actual: 2 0

Valor mnimo: 0

Valor mximo: 2 0

Incremento: 1

Vincular con la celda: H 3

4. Acepta el cuadro y pulsa Esc para quitar la seleccin del control y poder utilizarlo5. Pulsa sobre las flechas del control recin creado y observa cmo cambia el valorde la celda H 3

6. Sitate en la celda B3 y escribe: = H 3 / 1 0 0 Esto convierte en porcentaje el valor deH 3



El control se incrementa slo con nmeros enteros pero es preciso que la reduccinse introduzca como un porcentaje. La divisin entre 100 de la celda H 3 permite queel control use nmeros enteros y a nosotros nos permite especificar la reduccincomo un porcentaje.

Creacin de un control que incremente de cinco en cinco.- Si queremos introducirreducciones por ejemplo del 80%, deberamos ir pulsando la flecha arriba bastantesveces.

1.Accede a las propiedades del control recin creado2.Escribe 100 en el cuadro Valor mximo, un 5 en el cuadro Incremento, y acepta.3.Pulsa Esc para desactivar el control.

Observa que ahora la celda B3 va cambiando de 5 en 5. Ya puedes probar unaamplia variedad de combinaciones de modelos y de porcentajes de reduccin.

Limitacin del rdito para validar sus valores.- El rdito es el tanto por ciento de lareduccin. Nos van a interesar porcentajes que vayan variando de cuarto en cuarto ydentro de un rango del 0% al 20%. Ya que posibilitan porcentajes decimales, vamos anecesitar ms pasos que los que precisamos con el pago de la reduccin, y es poreso que vamos a usar una barra de desplazamiento en vez de un control como elanterior.

1.Crea una Barra de desplazamiento ms o menos as:

2.Accede a sus propiedades y modifcalas de la siguiente forma:

Valor mnimo: 0

Valor mximo: 2000

Incremento: 2 5

Vincular con celda: H 5

3.Acepta el cuadro de dilogo y pulsa Esc para quitar la seleccin4.Selecciona la celda B4 y escribe en ella: = H 5 / 1 0 0 0 0

5.Con el botn Aumentar decimales, aumntala en 2 decimalesPrueba ahora la barra de desplazamiento. La celda B4 divide por 100 para cambiarel nmero a un porcentaje y por otro 100 para poder para poder aproximar a lascentsimas. Ahora slo nos falta el control para los aos.

1.Crea un nuevo Control numrico y colcalo ms o menos as:



2.Accede a sus propiedades y cmbialas de la siguiente forma:

Valor mnimo: 1

Valor mximo: 6

Incremento: 1

Vincular con la celda: H 6

3.Prueba este ltimo control y verifica que los aos cambian de uno en uno.

Muy bien, el modelo ya est completo. Ya podemos experimentar con variosmodelos sin tener que preocuparnos de que podamos escribir entradas que no seanvlidas. De hecho, sin tener que escribir nada en el modelo. Una de las ventajas deuna interfaz grfica de usuario es la posibilidad de reducir las opciones para validarvalores. Vamos ahora a darle un ltimo toque:

4.Selecciona las columnas desde la G hasta la J y ocltalas. El aspecto final ser elsiguiente:

Visita ms cursos como este en mailxmail:[http://www.mailxmail.com/cursos-informatica][http://www.mailxmail.com/cursos-ofimatica]

Tu opinin cuenta! Lee todas las opiniones de este curso y djanos la tuya:[http://www.mailxmail.com/curso-excel-xp-segunda-parte/opiniones]

Cursos similaresCursos Valoracin Alumnos Vdeo



Cmo utilizar fcilmente el PaintEn este curso se presenta una manera fcil de aprender a manejar el Paint.Con l podrs modificar y crear dibujos, textos y todo lo que quieras paraambientar todos aque... [18 /07/06]

7.609

Informtica en la administracin pblica(3/3)Informtica en la administracin pblica. Proceso de informatizacin. Ahoraveremos la aplicacin de las herramientas inform&... [31 /03/09]

4.228

Ordenador. Componentes internos(primera parte)Curso de informtica sobre componentes del ordenador, la memoria RAM yla placa base. Conoce todos los componentes que integran un ordenadorlogrando identificar cada uno... [11 /11/08]

5.444

Excel. Hojas de clculo (primera parte)Curso del software Excel y formacin ofimtica. En esta primera parte denuestro curso de uso avanzado de hojas de clculo conocers y aprendersa utilizar cada una de l... [07 /11/08]

22.534

Informacin y comunicacin tecnolgica enCubaCon las Tecnologas de Informacin y Comunicaciones (TIC), elperfeccionamiento de la Enseanza Tcnica y Profesional y la vinculaci... [25 /06/09]

209



Presentacin del curso1. Introduccin 2. Las matrices3. Vinculos y referencias en Excel4. Proteccin de hojas5. Las tablas dinmicas6. Tablas de datos de una y dos variables7. El programa solver8. Acceso a dotos del exterior9. Microsoft Query10. Los macros11. Primera tarea: rellenar etiquetas perdidas12. Ampliacin de la macro13. Aadir columnas calculadas14. Macro para crear una tabla dinmica15. Cmo hacer que un macro se repita16. Anexo17. La funcin =PAGO()18. Resultado completo de la hoja19. Recuperacin del precio de la lista

Documents

Excel 80105487