Excel

1. Excel

2. Fórmula con la función SILa función SI le ofrece dos valores; basados en si una declaración es VERDADERA o FALSA. Un valor puede ser un número; un texto entre comillas; una celda de referencia o una fórmula. La forma general de una función SI es -   =SI(comparación lógica; valor si es VERDADERO; valor si es FALSO)Más de 2 valores: Usted puede incluir hasta 7 declaraciones de SI al apodo más de dos valores. Por ejemplo; usted puede mostrar tres resultados diferentes usando un declaración de SI como la parte; "valor si es Falso".=si(E13>100;"Extraordinario“;si(E13=100;”Perfecto“;E13))La declaración encima de muestra 3 resultados diferentes; dependiendo de si E13 es más que 100 ("extraordinario"; igualan a 100 ( "Perfecto"); o menos de 100 (el valor en E13).

4. Anidado Puede anidar hasta 7 declaraciones Y SI... para crear pruebas complejas. Por ejemplo; para mostrar una letra de calificación en la celda vecina a Calificación Promedio; podría usar 4 declaraciones anidadas en una sola fórmula: =SI(NotaFinal>89;“E";SI(NotaFinal>79;“S";SI(NotaFinal>69;“A";SI(NotaFinal>59;“I";“D")))) En lugar de escribir complicadas expresiones dentro de una declaración de SI ; se puede hacer cada cálculo en una celda por separado y usar solo las celdas de referencias en la declaración SI. Desde luego que si hace eso; mirando la fórmula SI no le dirá nada sobre lo que realmente está pasando. Nombrar las celdas sería mucho más útil en este tipo de situación.

5. La función =PAGO() calcula los pagos periódicos que tendremos que “pagar" sobre un préstamo; a un interés determinado; y en un tiempo x. Les irá de maravilla a los que quieren pedir un préstamo o ya lo están pagando. Podremos ver cuanto tendremos que pagar mensualmente; o cuanto nos clavan los bancos de intereses. Nos permitirá jugar con diferentes capitales; años o tipos de interés. La sintaxis de la orden es:=PAGO(Interés;Tiempo;Capital)Esta fórmula nos calculará el pago anualmente. Si queremos saber los pagos mensuales tendremos que dividir el interés por 12 y multiplicar el tiempo por 12. Observa:=PAGO(Interés/12;Tiempo*12;Capital)

6. Ejemplo:Supongamos que hemos de calcular los pagos mensuales y anuales periódicos del siguiente supuesto: Celda B5: =PAGO(B2;B3;B1) Celda B6: =PAGO(B2/12;B3*12;B1) Observa que la fórmula PAGO ofrece un resultado en negativo (rojo). Si queremos convertir el resultado en un número positivo; debemos encerrar la función dentro de otra función: =ABS(). La función ABS significa absoluto. Un número absoluto de otro número; siempre será positivo. La fórmula en ese caso sería: =ABS(PAGO(B2/12;B3*12;B1))

7. Como ya hemos dicho; en este tipo de hojas podemos probar a cambiar cantidades de las celdas B1;B2 y B3 y comprobar los distintos resultados. A continuación tienes un completo e interesante ejemplo de un supuesto de crédito desglosado mes a mes. En este ejemplo se utiliza una función nueva: =PAGOINT(); que desglosa el interés que pagamos de la cantidad mensual. La función =PAGO() nos muestra lo que debemos pagar; pero no nos dice cuanto pagamos de capital real y de intereses. La función =PAGOINT() realiza esto último. Colocaremos y colocaremos las fórmulas de las dos primeras filas. A partir de la segunda fila; sólo restará copiar las fórmulas hacia abajo. Supongamos un crédito de 2.000.000 de pesos con un interés del 8;5% en un plazo de 2 años; es decir; 24 meses.

8. A6 Número de mes que se paga B6 Cálculo del pago mensual con la función =ABS(PAGO($B$2/12;$B$3*12;$B$1)) C6 Restamos la cantidad pagada de los intereses y tenemos el capital real que pagamos =ABS(pagoprin($b$2/12;a6;$b$3*12;$b$1)) D6 Desglose del interés con la función =ABS(PAGOINT(B2/12;A6;B3*12;B1)) F6 Pendiente nos queda el capital inicial menos el que hemos pagado en el primer pago =$B$1-E6 E6 El primer mes tenemos acumulado el único pago de capital real =C6

9. Bien; ahora hemos de calcular el segundo mes. A partir de ahí; sólo habrá que copiar la fórmula hacia abajo. -E7 El acumulado del mes será igual al acumulado del mes anterior más el capital del presente mes. =E6+C7-F7 Nos queda pendiente el capital pendiente del mes anterior menos el capital que pagamos el presente mes. =F6-C7

10. Tablas dinámicas Una tabla dinámica nos permite modificar el aspecto de una lista de elementos de una forma más fácil; cómoda y resumida. Además; podemos modificar su aspecto y mover campos de lugar.Para crear tablas dinámicas hemos de tener previamente una tabla de datos preparada y posteriormente acceder a Datos - Informe de tablas y gráficos dinámicos.1.Crea la siguiente tabla de datos:

11. 2.Selecciona toda la tabla y accede a Datos - Informe de tablas y gráficos dinámicos.En primer lugar aparece una pantalla que representa el primer paso en el Informe de tablas y gráficos dinámicos. 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 a partir de una celda determinada; o bien en una hoja completamente nueva (opción elegida por defecto).5.Asegúrate de que está activada esta última opción y pulsa en Terminar.Se crea una hoja nueva con la estructura de lo que será la tabla dinámica. Lo que hay que hacer es "arrastrar" los campos desde la barra que aparece en la parte inferior; hacia la posición deseada en el interior de la tabla.6.Arrastra los campos Producto y Mes a la posición que se muestra en la siguiente figura:

13. 7.Arrastra ahora el campo Precio en el interior (ventana grande). Automáticamente aparecerá el resultado:

14. Hemos diseñado la estructura para que nos muestre los productos en su parte izquierda; los meses en columnas; y además; el precio de cada producto en la intersección de la columna. Observa también que se han calculado los totales por productos y por meses.Si modificamos algún dato de la tabla original; podemos actualizar la tabla dinámica desde la opción Datos - Actualizar datos siempre que el cursor esté en el interior de la tabla dinámica.

15. Al actualizar una tabla; Excel compara los datos originales. Pero si se han añadido nuevas filas; tendremos que indicar el nuevo rango accediendo al paso 2 del Asistente. Esto podemos hacerlo accediendo nuevamente a Datos - Informe de tablas y gráficos dinámicos y volviendo atrás un paso.Es posible que al terminar de diseñar la tabla dinámica nos interese ocultar algún subtotal calculado. Si es así; debemos pulsar doble click en el campo gris que representa el nombre de algún campo; y en el cuadro de diálogo que aparece; elegir la opción Ninguno. Desde este mismo cuadro podemos también cambiar el tipo de cálculo.

16. Es posible también mover los campos de sitio simplemente arrastrando su botón gris hacia otra posición. Por ejemplo; puede ser que queramos ver la tabla con la disposición de los campos al revés; es decir; los productos en columnas y los meses en filas.Prueba a mover el Mes y el Producto a la parte izquierda. Verás que ahora se organiza y suma a través del mes.

17. Desde la barra de modificación de la tabla; podemos realizar operaciones de actualización; selección de campos; ocultar; resumir; agrupar; etc. Puedes practicar sin miedo los diferentes botones de la barra.

18. Herramienta que permite localizar de forma fácil los datos que utiliza una fórmula (precedentes) o los que dependen de ella (dependientes); comprobar errores; marcarlos e insertar comentarios.  Para utilizar la Auditoría de fórmulas:   Colocamos el cursor en la fórmula a evaluar.   Seleccionamos del menú Herramientas la opción Auditoría de Fórmulas.   Seleccionamos la opción deseada o activar la barra de herramientas   AUDITORIA DE FORMULAS

19. Borrar círculos de validación Quitar todas las flechas Comprobación de errores Rastrear Dependientes Nuevo Comentario Evaluar formula Rodear con un circulo datos no validos Quitar un nivel de Dependientes Rastrear Precedentes Rastrear todos los errores Mostrar ventana de inspección Quitar un nivel de precedentes

20. Mostrar las relaciones entre fórmulas y celdas En ocasiones; comprobar si las fórmulas  son precisas o buscar el origen de un error puede resultar difícil si la fórmula utiliza celdas precedentes o dependientes: Las celdas precedentes   son celdas a las que se hace referencia mediante una fórmula en otra celda. Por ejemplo; si la celda D10 contiene la fórmula =B5; la celda B5 es precedente con respecto a la celda D10.

21. Mostrar las relaciones entre fórmulas y celdas Las celdas dependientes contienen fórmulas que hacen referencia a otras fórmulas. Por ejemplo; si la celda D10 contiene la fórmula =B5; la celda D10 es dependiente de la celda B5. Para recibir ayuda a la hora de comprobar las fórmulas; puede utilizar los comandos Rastrear precedentes y Rastrear dependientes para mostrar gráficamente o para rastrear las relaciones entre las celdas y las fórmulas con flechas de rastreo.

22. Rastrear celdas que proporcionan datos a una fórmula (precedentes) Seleccione la celda que contenga la fórmula para la que se desee buscar las celdas precedentes. Para que aparezca una flecha de rastreo para cada celda que proporcione directamente datos a la celda activa; en la ficha Fórmulas; en el grupo Auditoría de fórmulas; haga clic en Rastrear precedentes . Las flechas de color azul muestran las celdas sin errores. Las flechas de color rojo muestran las celdas que generan errores. Si una celda de otra hoja de cálculo o de otro libro hace referencia a la celda seleccionada; se mostrará una flecha de color negro que señala desde la celda seleccionada hasta un icono de hoja de cálculo . El otro libro debe estar abierto para que Excel pueda rastrear estas dependencias.

23. Rastrear celdas que proporcionan datos a una fórmula (precedentes) Para identificar el siguiente nivel de celdas que proporcionan datos a la celda activa; haga clic otra vez en Rastrear precedentes Para quitar las flechas de rastreo un nivel cada vez; empezando por la celda precedente que esté más distante de la celda activa; en la ficha Fórmulas; en el grupo Auditoría de fórmulas; haga clic en la flecha situada junto a Quitar flechas y; a continuación; en Quitar un nivel de precedentes . Para quitar otro nivel de flechas de rastreo; haga clic otra vez en el botón.

24. Rastrear fórmulas que hacen referencia a una celda en concreto (dependientes) Seleccione la celda para la que desea identificar las celdas dependientes. Para que aparezca una flecha de rastreo  para cada celda que sea dependiente de la celda activa; en la ficha Fórmulas; en el grupo Auditoría de fórmulas; haga clic en Rastrear dependientes . Las flechas de color azul muestran las celdas sin errores. Las flechas de color rojo muestran las celdas que generan errores. Si una celda de otra hoja de cálculo o de otro libro hace referencia a la celda seleccionada; se mostrará una flecha de color negro que señala desde la celda seleccionada hasta un icono de hoja de cálculo . El otro libro debe estar abierto para que Excel pueda rastrear estas dependencias.

25. Rastrear fórmulas que hacen referencia a una celda en concreto (dependientes) Para identificar el siguiente nivel de celdas que dependen de la celda activa; haga clic otra vez en Rastrear dependientes . Para quitar las flechas de rastreo un nivel cada vez; empezando por la celda dependiente que esté más distante de la celda activa; en la ficha Fórmulas; en el grupo Auditoría de fórmulas; haga clic en la flecha situada junto a Quitar flechas y; a continuación; en Quitar un nivel de dependientes . Para quitar otro nivel de flechas de rastreo; haga clic otra vez en el botón.

26. Quitar Flechas Para quitar todas las flechas de rastreo  de la hoja de cálculo; en la ficha Fórmulas; en el grupo Auditoría de fórmulas; haga clic en Quitar flechas .

27. ¿Qué es la validación de datos? • La validación de datos de Microsoft Excel permite definir el tipo de datos que se desea introducir en una celda. • Por ejemplo, se puede permitir la entrada de una puntuación por letras con sólo las letras comprendidas entre la A y la F. Se puede configurar la validación de datos para evitar que los usuarios introduzcan datos no válidos o permitir su entrada y comprobarlos tras finalizar el usuario. También se pueden proporcionar mensajes que definan la entrada que se espera en la celda así como instrucciones para ayudar a los usuarios a corregir errores. • Cuando se introducen datos que no cumplen los requisitos, Excel muestra un mensaje con instrucciones proporcionadas por el usuario.

28. EJEMPLO:

29. ¿PARA QUÉ? • La validación de datos resulta especialmente útil al diseñar formularios u hojas de cálculo que otras personas utilizarán para introducir datos como formularios presupuestarios o informes de gastos.

30. Tipos de datos que se pueden validar • Excel permite designar los siguientes tipos de datos válidos para una celda: • Números   Especifique que la entrada en una celda debe ser un número entero o un número decimal. Puede establecer un mínimo o máximo, excluir un número o intervalo determinado, o utilizar una fórmula para calcular la validez de un número. • Fechas y horas   Establezca un mínimo o máximo, excluya determinadas fechas u horas, o utilice una fórmula para calcular la validez de una fecha u hora. • Longitud   Limite el número de caracteres que se puede escribir en una celda o exija un número mínimo de caracteres. • Lista de valores   Elabore una lista de las opciones para una celda como, por ejemplo, pequeño, mediano, grande, y admita sólo esos valores en la celda. Se puede mostrar una flecha de lista desplegable cuando un usuario hace clic en la celda para facilitar la selección de una opción de la lista.

31. Tipos de mensajes que se puede mostrar • Para cada celda que se valida, se pueden mostrar dos mensajes diferentes: uno que aparece antes de que el usuario introduzca los datos y otro que aparece después de que el usuario intente introducir los datos que no cumplen los requisitos. Si los usuarios tienen activado el Ayudante de Office, éste mostrará los mensajes.

32. Mensaje de entrada • Mensaje entrante, Este tipo de mensaje aparece cuando un usuario hace clic en la celda validada. Se puede utilizar este tipo de mensaje para facilitar instrucciones sobre el tipo de datos que se han de introducir en la celda.

33. Mensaje de Error. • Este tipo de mensaje aparece sólo cuando el usuario escribe datos no válidos y presiona la tecla ENTRAR. Se puede elegir entre tres tipos de mensajes de error:

34. Mensaje de información. • Este mensaje no impide la entrada de datos no válidos. Además del texto que proporciona el usuario, incluye un icono de información, un botón Aceptar, que introduce los datos no válidos en la celda, y un botón Cancelar, que restaura el valor anterior de la celda.

35. Mensaje de Advertencia • Este mensaje no impide la entrada de datos no válidos. Incluye el texto que proporciona el usuario, un icono de advertencia y tres botones: Sí introduce los datos no válidos en la celda, No regresa a la celda para más modificaciones y Cancelar restaura el valor anterior de la celda.

36. Mensaje de detención • Este mensaje no permite la entrada de datos no válidos. Incluye el texto que proporciona el usuario, un icono de detención y dos botones: Reintentar regresa a la celda para más modificaciones y Cancelar restaura el valor anterior de la celda. Observe que este mensaje no está concebido como medida de seguridad: si bien los usuarios no pueden introducir datos no válidos escribiendo y presionando ENTRAR, pueden eludir la validación copiando y pegando o rellenando datos en la celda.

37. Comprobar si hay entradas no válidas en una hoja de cálculo • Al recibir hojas de cálculo de usuarios que pueden haber introducido datos no válidos, puede configurar Excel de modo que muestre círculos rojos alrededor de los datos que no cumplan los criterios, facilitando así la búsqueda de errores en las hojas de cálculo. Utilice los botones Rodear con un círculo datos no válidos y Borrar círculos de validación en la barra de herramientas Auditoría.

38. El valor de estas celdas están rodeados con un círculo porque no cumple una regla de validación. El círculo desaparecerá cuando corrija los datos de la celda.

39. Listas desplegables • Las listas desplegables se crean cuando necesite limitar las selecciones que pueden realizar los usuarios, y cuando quiera asegurarse de que un dato, por ejemplo un número de pieza o un código de diagnóstico, se especifica con exactitud.

40. Pasos Para la creación de una lista desplegable 1 • Crea un rango de datos

41. Pasos Para la creación de una lista desplegable 2. Selecciona la celda en la cual desea dejar la lista desplegable. 3. Clic en el menú “datos”/ Validación… 4. Opción “permitir” /Lista 5. Seleccionar el rango de origen

43. En ocasiones, tenemos que realizar acciones repetitivas y rutinarias una y otra vez. En vez de hacerlas manualmente, podemos crear una macro que trabaje por nosotros. Las macros son funciones que ejecutan instrucciones automáticamente y que 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 grabación de la macro.4.Depurar posibles errores o modificar la macro. Crear una macro

44. Las macros también pueden ejecutarse pulsando una combinación de teclas específica, por lo que ni siquiera debemos acceder a un menú para invocar a la macro, o bien asignársela a un botón.Cuando creamos una macro, en realidad Excel está creando un pequeño programa utilizando el lenguaje común en aplicaciones Office: el Visual Basic.Creación de una macro.- 1.Accede a Herramientas - Macro - Grabar nueva macro. Te aparecerá un menú:

45. 2.Acepta el nombre propuesto (Macro1) y acepta el cuadro de diálogo.A continuación, aparecerá un pequeño botón desde el que podrás detener la grabación de la macro. • A partir de estos momentos, todo lo que hagas (escribir, borrar, cambiar algo...) se irá grabando. Debemos tener cuidado, porque cualquier fallo también se grabaría.4.Escribe: Días transcurridos y pulsa Intro. 5.En la celda A2 escribe: Fecha actual y pulsa Intro.

46. 6.En la celda A3 escribe: Fecha pasada y pulsa Intro.7.En la celda A4 escribe: Total díasy pulsa Intro.8.Selecciona con un click la cabecera de la columna A (el nombre de la columna) de forma que se seleccione toda la columna. • 9.Accede a Formato - Columna - Autoajustar a la selección10.Pulsa click en la celda B2 y escribe: =HOY(). Pulsa Intro. • 11.Escribe: 29/09/98 y pulsa Intro.12.Accede a Formato - Celda elige el formato Número y acepta.

47. 3.Sitúa el cursor en la celda A1.14.Pulsa la combinación de teclas Control + * (se seleccionarán todo el rango no-vacío). 15.Accede a Formato - Autoformato - Multicolor 2 y acepta.16.Finaliza la grabación desde el botón Detener grabación o bien desde el menú Herramientas - Macro - Detener grabación. Ahora vamos a ver si la macro funciona:1.Colócate en la Hoja22.Accede a Herramientas - Macro - Macros.3.Elige tu macro y pulsa el botón Ejecutar. 4.Observa su comportamiento.

48. CÓMO ESCRIBIR UNA MACRO UTILIZANDO EL EDITOR DE VISUAL BASIC • Para ponerlo en funcionamiento deberemos realizar los siguientes pasos: • 1.- Dentro del menú Herramientas - Macro deberemos escoger la opción: Editor de Visual Basic. • Una vez seleccionada se abrirá una nueva ventana con dos ventanas acopladas a la izquierda. La superior es la ventana que llamamos: de Proyecto y la inferior la de Propiedades. En la primera aparecerán los elementos que forman parte de cada Proyecto (grupo de macros y hojas de Excel) y la segunda son las propiedades de los objetos que se pueden incorporar a nuestras macros. Más adelante veremos como se utilizan estas dos ventanas y todas sus características.

49. Vamos a prepararnos para poder escribir nuestra primera macro • 2.- Escoge la opción Módulo del menú Insertar. • Observa como en la ventana superior nos aparece una nueva carpeta llamada Módulos y en su interior un nuevo elemento llamado Módulo1. Dentro de este módulo será donde guardemos las macros que creemos. • También podrás ver como la parte derecha de la ventana ahora es completamente blanca. Aquí es donde podemos escribir las instrucciones que formarán parte de nuestra Macro. • Vamos a crear una Macro y esta nos servirá de ejemplo para ver como se deben escribir. • Crearemos una Macro muy sencilla la cual nos servirá para que la página activa pase a ser la segunda.

50. 3.- En la página en blanco de la derecha escribe lo siguiente: • Sub Cambiardehoja() •          Worksheets(2).Activate •       End Sub • El comando Sub indica el principio de la Macro, mientras que End Sub marca el final de esta. A continuación del comando Sub hemos puesto el nombre que deseamos dar a nuestra Macro. Observa como después del nombre aparece un paréntesis que se cierra y otro que se abre. • Entre el principio y el final de la Macro escribimos las instrucciones que deseamos se realicen en esta Macro. • Si deseamos guardar la Macro simplemente deberemos guardar el libro activo con el nombre que deseamos. Ambos elementos se guardarán juntos. • En la siguiente lección veremos como podemos poner en funcionamiento una Macro creada por nosotros.