1. UNIVERSIDAD TCNICA PARTICULAR DE LOJA La Universidad Catlica de Loja MODALIDAD ONLINE EDUCACIN CONTINUA Gua didctica EXCEL 2007 INTERMEDIO AUTOR: Ing. Jorge Marcos Cordero Zambrano NOVIEMBRE 2009-ENERO 2010

2. EXCEL 2007 INTERMEDIO Gua Didctica Elaborada por: Jorge Marcos Cordero Zambrano E-mail: jmcorderoz@gmail.com Blog: http://jmcordero.blogspot.com UNIVERSIDAD TCNICA PARTICULAR DE LOJA CC Ecuador 3.0 By NC ND Call Center: 593 - 7 - 2570275 C. P.: 11- 01- 608 www.utpl.edu.ec San Cayetano Alto s/n Loja - Ecuador Primera edicin Esta versin impresa, ha sido licenciada bajo las licencias Creative Commons Ecuador 3.0 de Reconocimiento - No comercial - Sin Obras Derivadas; la cual permite copiar, distribuir y comunicar pblicamente la obra, mientras se reconozca la autora original, no se utilice con fines comerciales ni se realicen obras derivadas. http://www.creativecommons.org/licences/by-nc-nd/3.0/ec/ Noviembre, 2009 3. CONTENIDOS Introduccin.................................................................................................................................................. 8 Objetivos........................................................................................................................................................ 9 Bibliografa.................................................................................................................................................. 10 Orientaciones Generales...................................................................................................................... 11 MODULO 1................................................................................................................................................. 13 Funciones avanzadas............................................................................................................................ 13 Sintaxis de la funcin ............................................................................................................................. 13 Cmo escribir la funcin con el asistente ...................................................................................... 14 Errores en los datos................................................................................................................................ 16 Tipos de Funciones................................................................................................................................. 18 Funciones de fecha y hora................................................................................................................... 18 Funciones de texto.................................................................................................................................. 19 Funciones de bsqueda y referencia............................................................................................... 20 Funciones lgicas.................................................................................................................................... 21 MODULO 2................................................................................................................................................. 22 Aplicacin de Funciones de Fecha y Hora.................................................................................... 22 Funcin AHORA....................................................................................................................................... 22 Funcin HOY............................................................................................................................................. 22 Funcin DIA................................................................................................................................................ 22 Funcin MES ............................................................................................................................................. 23 Funcin AO............................................................................................................................................. 23 Funcin HORA.......................................................................................................................................... 24 Funcin FECHA........................................................................................................................................ 24 Funcin DIAS360..................................................................................................................................... 24 MODULO 3................................................................................................................................................. 26 Aplicacin de Funciones de Texto.................................................................................................... 26 4. Funcin CONCATENAR....................................................................................................................... 26 Funcin ENCONTRAR.......................................................................................................................... 26 Funcin MINUSC..................................................................................................................................... 27 Funcin MAYUSC.................................................................................................................................... 28 Funcin EXTRAE..................................................................................................................................... 28 MODULO 4................................................................................................................................................. 30 Funcin ELEGIR ...................................................................................................................................... 30 Funcin INDICE........................................................................................................................................ 30 Funcin BUSCAR.................................................................................................................................... 31 Funcin BUSCARV................................................................................................................................. 33 Funcin BUSCARH................................................................................................................................. 34 MODULO 5................................................................................................................................................. 36 Aplicacin de Funciones Lgicas...................................................................................................... 36 Funcin SI................................................................................................................................................... 36 Funcin Y.................................................................................................................................................... 37 Funcin O.................................................................................................................................................... 38 Funcin NO ................................................................................................................................................ 38 Edicin Avanzada.................................................................................................................................... 40 Relacin entre hojas y libros (referencias externas vnculos) ........................................... 40 Dnde pueden utilizarse eficazmente las referencias externas ........................................... 40 Aspecto de una referencia externa a otro libro............................................................................ 41 Crear una referencia externa entre celdas de libros distintos............................................... 41 Controlar los vnculos a otros libros................................................................................................. 41 Actualizar de forma manual todos los vnculos o no actualizar ningn vnculo de un libro................................................................................................................................................................ 42 Actualizar de forma manual slo algunos de los vnculos a otros libros........................... 42 Consolidar datos en varias hojas de clculo ................................................................................ 42 5. Consolide por posicin .......................................................................................................................... 43 Consolide por frmula............................................................................................................................ 44 Bloques tridimensionales...................................................................................................................... 44 Formato de celdas................................................................................................................................... 47 Nmero ........................................................................................................................................................ 47 Alineacin.................................................................................................................................................... 49 Fuente .......................................................................................................................................................... 50 Relleno ......................................................................................................................................................... 51 Proteccin................................................................................................................................................... 52 El formato condicional............................................................................................................................ 53 Agregar, cambiar o borrar formatos condicionales.................................................................... 53 Aplicar formato slo a las celdas que contienen valores de texto, nmero, o fecha u hora................................................................................................................................................................ 54 Aplicar formato a todas las celdas utilizando barras de datos.............................................. 54 Aplicar formato a todas las celdas empleando una escala de dos tres colores......... 55 Aplicar formato a todas las celdas empleando un conjunto de iconos.............................. 55 Aplicar formato a los valores de rango inferior o superior y por encima o por debajo del promedio...................................................................................................................................................... 55 Utilizar una frmula que determine las celdas para aplicar formato................................... 56 Borrar formatos condicionales............................................................................................................ 58 La validacin de datos........................................................................................................................... 58 Crear una lista de entradas validas.................................................................................................. 61 MODULO 7................................................................................................................................................. 63 Grficos........................................................................................................................................................ 63 Tipos de grficos...................................................................................................................................... 63 Grficos de columnas ............................................................................................................................ 64 Grficos de lneas.................................................................................................................................... 64 Grficos circulares................................................................................................................................... 64 6. Grficos de barras................................................................................................................................... 65 Grficos de rea....................................................................................................................................... 65 Grficos de tipo XY (Dispersin) ....................................................................................................... 66 Grficos de cotizaciones....................................................................................................................... 66 Grficos de superficie ............................................................................................................................ 67 Grficos de anillos................................................................................................................................... 67 Grficos de burbujas .............................................................................................................................. 68 Grficos radiales ...................................................................................................................................... 68 Crear un grfico........................................................................................................................................ 68 Dar formato a los grficos .................................................................................................................... 70 Seleccionar un diseo de grfico predefinido .............................................................................. 71 Seleccionar un estilo de grfico predefinido................................................................................. 71 Seleccionar elementos de grfico..................................................................................................... 71 Cambiar manualmente el diseo de elementos de grfico..................................................... 72 Cambiar manualmente el estilo de elementos de grfico ....................................................... 72 Usar ttulos en un grfico...................................................................................................................... 72 Aplicar un diseo de grfico que contenga ttulos...................................................................... 73 Agregar un ttulo de grfico manualmente .................................................................................... 73 Guardar un grfico como una plantilla de grfico....................................................................... 73 Cmo aplicar una plantilla de grfico............................................................................................... 74 Cambiar la presentacin de los ejes de grficos........................................................................ 74 Mostrar u ocultar los ejes...................................................................................................................... 75 Cambiar el tipo de grfico de un grfico existente..................................................................... 75 Trazar serie de datos de columnas o filas de hoja de clculo............................................... 76 Modificar el tamao de un grfico..................................................................................................... 77 Modificar la ubicacin de un grfico................................................................................................. 77 MODULO 8................................................................................................................................................. 79 7. Tablas dinmicas..................................................................................................................................... 79 Crear un informe de tabla dinmica................................................................................................. 79 Crear un informe de grfico dinmico ............................................................................................. 83 Aplicar filtros a una tabla dinmica................................................................................................... 85 Eliminar una tabla dinmica................................................................................................................ 86 MODULO 9................................................................................................................................................. 87 Uso de macros para simplificar tareas............................................................................................ 87 Proceso de grabacin............................................................................................................................ 87 Ejecutar una macro................................................................................................................................. 89 Crear una macro manualmente (Uso de la barra de herramientas de Visual Basic)... 90 Guardar archivos con Macros............................................................................................................. 92 Ejecutar una macro haciendo clic en un botn de la barra de herramientas de acceso rpido............................................................................................................................................................ 94 Ejecutar una macro haciendo clic en un rea de un objeto grfico .................................... 94 Creacin de un botn para ejecutar una Macro.......................................................................... 95 8. Excel 2007 Intermedio 8 Introduccin Microsoft Office Excel 2007 es una aplicacin que trabaja con hojas de clculo, que facilita el manejo de nmeros, frmulas matemticas y manipular cualquier dato o informacin de forma rpida. Hoy en da, Excel es la hoja de clculo ms usada en el mercado debido a la gran variedad de funcionalidades. Adems, ofrece varias novedades que permite optimizar el trabajo. Por esta razn, es de gran importancia contar con recurso humano capacitado, que conozca las nuevas formas de cmo debe trabajar en el mercado. Motivo por el cual, el alumno que lleve este curso aprender los conceptos y fundamentos de Excel, los mtodos disear hojas de trabajo que incluyan clculos, frmulas, funciones y grficos estadsticos para llevar a cabo el manejo de la informacin. Vamos a profundizar en el manejo de funciones ya definidas por Excel 2007 para agilizar la creacin de hojas de clculo, estudiando la sintaxis de stas as como el uso del asistente para funciones, herramienta muy til cuando no conocemos muy bien las funciones existentes o la sintaxis de stas. Microsoft Office Excel 2007 a travs de filtros y tablas dinmicas le brindar la posibilidad de resumir, analizar, explorar y presentar datos de resumen. A travs de los informes de grfico dinmico podr ver los datos de resumen contenidos en un informe de tabla dinmica para realizar comparaciones, patrones y tendencias. Con las macros podr optimizar muchos de los proceso rutinarios INTRODUCCIN 9. Excel 2007 Intermedio 9 Objetivos GENERAL Utilizar Microsoft Excel 2007 para disear hojas de trabajo que incluyan clculos, frmulas, uso de funciones y grficos estadsticos, apoyados en el desarrollo de una gran variedad de ejercicios prcticos. ESPECFICOS Conocer, aplicar, y usar las herramientas que ofrece la Hoja Electrnica Excel 2007 Manejar los datos de una hoja de clculo, y utilizar referencias absolutas y relativas. Utilizar y aplicar las funciones en la resolucin de problemas. Aprender a utilizar y aplicar tablas dinmicas para obtener los ms variados reportes. Automatizar tareas con los usos y aplicacin de Macros. OBJETIVOS 10. Excel 2007 Intermedio 10 Bibliografa BSICA Ayuda de Microsoft Excel 2007 (F1) COMPLEMENTARIA JELEN Bill, SYRSTAD Tracy. Excel Macros y VBA Edicin revisada y actualizada 2010. Ediciones ANAYA Multimedia. Madrid 2008 ENLACES EN LA WEB Explorar la Ayuda y procedimientos de Excel 2007 http://office.microsoft.com/es-es/excel/FX100646953082.aspx Video Tutoriales de Excel 2007 http://jmcordero.blogspot.com/ Curso de Excel 2007 http://www.aulaclic.com.es/excel2007/ Lecciones de Microsoft Excel 2007 http://www.aulafacil.com/excel-2007/curso/Temario.htm Es un sitio oficial de Microsoft que presenta varios enlaces a artculos relacionados con la Validacin de datos en Excel. http://office.microsoft.com/es-hn/infopath/CH011097053082.aspx En este sitio encontrars ejemplos sencillos acerca de la validacin de datos en Excel. http://office.microsoft.com/es-hn/excel/HA010346573082.aspx En este sitio encontraras libros gratuitos con funciones financieras. http://www.eumed.net/libros/finanzas.htm Contiene informacin relaciona con las Macros en Excel http://support.microsoft.com/kb/213740/es BIBLIOGRAFA 11. Excel 2007 Intermedio 11 Orientaciones Generales Modalidad y recursos de aprendizaje El presente programa de capacitacin se desarrollar de forma ntegra en nuestra plataforma tecnolgica Online, la que se caracteriza por ser accesible en cualquier horario, disponible las 24 horas del da y desde cualquier computador conectado a internet, adems permite administrar de forma eficiente el proceso de aprendizaje a travs de Internet, sin importar la localizacin geogrfica de nuestros alumnos. Es decir esta plataforma representa el Aula Virtual donde se desarrollan las actividades de capacitacin y en parte de la evaluacin. Durante el periodo de estudios, el participante contar con mltiples recursos de aprendizaje, como: Prcticas en las que aplicar los conocimientos adquiridos, que hacen referencia al trabajo que desarrollan los participantes. Tareas extra clase. Trabajo a distancia. Evaluacin presencial. Duracin El tiempo estimado para la realizacin de este programa de capacitacin es de 20 horas, con la asesora del facilitador, y 20 horas adicionales en las que el participante deber interactuar en el entorno a fin de que cumpla con las actividades extra clase que el facilitador proponga. Metodologa El curso denominado "Excel 2007 Intermedio" se desarrolla de manera exclusivamente virtual durante 4 semanas, tiempo en el que el facilitador brindar apoyo y asesora buscando la interactividad de sus participantes de modo que se puedan lograr el desarrollo de las competencias planteadas en el curso. Para el estudio de cada tema se colocar los recursos necesario que contendrn los contenidos de estudio y para el desarrollo de las actividades habr la respectiva asesora del facilitador. Por lo expuesto, se requiere que el participante ingrese con cierta frecuencia al Entorno Virtual de Aprendizaje a fin de informarse de las indicaciones y actividades planteadas por el facilitador. ORIENTACIONES GENERALES 12. Excel 2007 Intermedio 12 El estudio del curso requiere que el participante dedique al menos dos horas diarias, dado que debe cumplir con el estudio de los contenidos y el desarrollo de actividades que se planteen. Certificados La Universidad Tcnica Particular de Loja, otorgar al trmino del programa de capacitacin los certificados que acrediten al participante la asistencia y/o la aprobacin, de acuerdo a lo siguiente: Certificado de Aprobacin del curso, cuando el participante alcance al menos el 70% de puntaje en la evaluacin de conocimientos y el 75% en asistencia a las clases presenciales, de acuerdo a lo establecido en nuestra Universidad, as: APROVECHAMIENTO Mnimo para aprobar 28/40 puntos PARTICIPACIN ENTORNO Mnimo para aprobar 70% duracin del curso. Las herramientas de evaluacin de cada mdulo sern las siguientes: Participacin en los Foros Cumplimiento de tareas Evaluaciones va Online Evaluacin presencial Esta prevista una sola evaluacin presencial; su realizacin se coordinar con los estudiantes, as como lo referente al lugar en el que se aplicar la misma. 13. Excel 2007 Intermedio 13 MODULO 1 Funciones avanzadas Este modulo es fundamental para el desarrollo del curso, pues en su comprensin y manejo es la base para una buena utilizacin de Microsoft Office Excel 2007. Microsoft Excel es capaz de insertar frmulas y funciones, realizando las operaciones que se le describan con los valores contenidos en las celdas especificadas. Vamos a profundizar en el manejo de funciones ya definidas por Excel 2007 para agilizar la creacin de hojas de clculo, estudiando la sintaxis de stas as como el uso del asistente para funciones, herramienta muy til cuando no conocemos muy bien las funciones existentes o la sintaxis de stas. Sintaxis de la funcin Las funciones son frmulas predefinidas en Excel 2007 que ejecutan clculos utilizando valores especficos, denominados argumentos, y devuelven un resultado. Las funciones pueden utilizarse para ejecutar operaciones simples o complejas La sintaxis de cualquier funcin es: nombre_funcin(argumento1;argumento2;...;argumentoN) El siguiente ejemplo se ilustra la sintaxis de la funcin REDONDEAR. Estructura. La estructura de una funcin comienza por el signo igual =, seguido por el nombre de la funcin, un parntesis de apertura, los argumentos de la funcin separados por signos de punto y coma y un parntesis de cierre. Nombre de funcin. Argumentos. Los argumentos pueden ser nmeros, texto, valores lgicos como VERDADERO o FALSO, matrices, valores de error como #N/A o referencias de celda. Los argumentos pueden ser tambin constantes, frmulas u otras funciones. MODULO 1 14. Excel 2007 Intermedio 14 Informacin sobre herramientas de argumentos. Cuando se escribe la funcin, aparece una informacin sobre herramientas con su sintaxis y sus argumentos. As, escriba =SUMA y aparecer la informacin. Ejemplo: =SUMA(A1:C8) Tenemos la funcin SUMA() que devuelve como resultado la suma de sus argumentos. El operador ":" nos identifica un rango de celdas, as A1:C8 indica todas las celdas incluidas entre la celda A1 y la C8, as la funcin anterior sera equivalente a: =A1+A2+A3+A4+A5+A6+A7+A8+B1+B2+B3+B4+B5+B6+B7+B8+C1+C2+C3+C4+C5+C 6+C7+C8 En este ejemplo se puede apreciar la ventaja de utilizar la funcin. Cmo escribir la funcin con el asistente Una funcin como cualquier dato se puede escribir directamente en la celda si conocemos su sintaxis, pero Microsoft Office Excel 2007 dispone de una ayuda o asistente para utilizarlas, as nos resultar ms fcil trabajar con ellas. Para escribir una funcin en una celda utilizando el asistente se debe: 1. Situarse en la celda donde queremos introducir la funcin. 2. Hacer clic en la pestaa Frmulas. Elegir la opcin Insertar funcin. O bien, hacer clic sobre el botn de la barra de frmulas. 15. Excel 2007 Intermedio 15 Para obtener una lista de funciones disponibles, haga clic en una celda y presione MAYS+F3. Aparece el asistente de funciones, mostrando el cuadro de dilogo Insertar funcin. Excel 2007 nos permite buscar la funcin que necesitamos escribiendo una breve descripcin de la funcin necesitada en el recuadro Buscar una funcin: y a continuacin hacer clic sobre el botn, de esta forma no es necesario conocer cada una de las funciones que incorpora Excel ya que el nos mostrar en el cuadro de lista Seleccionar una funcin: las funciones que tienen que ver con la descripcin escrita. Para que la lista de funciones no sea tan extensa podemos seleccionar previamente una categora del cuadro combinado O seleccionar una categora:, esto har que en el cuadro de lista slo aparezcan las funciones de la categora elegida y reduzca por lo tanto la lista. Si no estamos muy seguros de la categora podemos elegir Todas. En el cuadro Seleccionar una funcin: hay que elegir la funcin que deseamos haciendo clic sobre sta. Se observa como conforme seleccionamos una funcin, en la parte inferior nos aparecen los distintos argumentos y una breve descripcin de sta. Tambin disponemos de un enlace Ayuda sobre esta funcin para obtener una descripcin ms completa de dicha funcin. A final, hacer clic sobre el botn Aceptar. Justo por debajo de la barra de frmulas aparecer el cuadro de dilogo Argumentos de funcin, donde nos pide introducir los argumentos de la funcin: Este cuadro variar segn la funcin que hayamos elegido, en nuestro caso se eligi la funcin SI (). 16. Excel 2007 Intermedio 16 Errores en los datos Cuando introducimos una funcin en una celda puede ocurrir que se produzca un error. Dependiendo del tipo de error puede que Excel nos avise o no. 17. Excel 2007 Intermedio 17 Cuando nos avisa del error, nos da una posible propuesta que podemos aceptar haciendo clic sobre el botn S o rechazar utilizando el botn No. Podemos detectar un error sin que nos avise cuando aparece la celda con un smbolo en la esquina superior izquierda tal como esto: Al hacer clic sobre el smbolo aparecer un cuadro que nos permitir saber ms sobre el error. Si no sabemos qu hacer, disponemos de la opcin Ayuda sobre este error. Si lo que queremos es comprobar la frmula para saber si hay que modificarla o no podramos utilizar la opcin Modificar en la barra de frmulas. Si la frmula es correcta, se utilizar la opcin Omitir error para que desaparezca el smbolo de la esquina de la celda. Puede que al introducir la funcin nos aparezca como contenido de la celda #TEXTO , siendo TEXTO un valor que puede cambiar dependiendo del tipo de error. Por ejemplo: ERROR DESCRIPCIN ##### Este error se produce cuando el ancho de una columna no es suficiente o cuando se utiliza una fecha o una hora negativa. #NULO! Este error se genera cuando se especifica una interseccin de dos reas que no se intersecan. El operador de interseccin es un espacio entre referencias. #DIV/0! Este error se produce cuando se divide un nmero por cero (0). #VALOR! Este error se produce cuando se utiliza un tipo de argumento o de operando incorrecto. #REF! Este error se produce cuando una referencia de celda no es vlida. #NOMBRE? Este error se produce cuando Microsoft Office Excel no reconoce el texto de la frmula. #NM! Este error se produce cuando se escriben valores numricos no vlidos en una frmula o una funcin. #N/A Este error se produce cuando un valor no est disponible para una funcin o una frmula 18. Excel 2007 Intermedio 18 Tambin en estos casos, la celda, como en el caso anterior, contendr adems un smbolo en la esquina superior izquierda tal como: Tipos de Funciones Existen muchos tipos de funciones dependiendo del tipo de operacin o clculo que realizan. As: Funciones por categoras 1. Funciones de complementos y automatizacin 2. Funciones de cubo 3. Funciones de base de datos 4. Funciones de fecha y hora 5. Funciones de ingeniera 6. Funciones financieras 7. Funciones de informacin 8. Funciones lgicas 9. Funciones de bsqueda y referencia 10. Funciones matemticas y trigonomtricas 11. Funciones estadsticas 12. Funciones de texto Funciones de fecha y hora Funcin Descripcin FECHA Devuelve el nmero de serie correspondiente a una fecha determinada FECHANUMERO Convierte una fecha con formato de texto en un valor de nmero de serie DIA Convierte un nmero de serie en un valor de da del mes DIAS360 Calcula el nmero de das entre dos fechas a partir de un ao de 360 das FECHA.MES Devuelve el nmero de serie de la fecha equivalente al nmero indicado de meses anteriores o posteriores a la fecha inicial FIN.MES Devuelve el nmero de serie correspondiente al ltimo da del mes anterior o posterior a un nmero de meses especificado HORA Convierte un nmero de serie en un valor de hora 19. Excel 2007 Intermedio 19 MINUTO Convierte un nmero de serie en un valor de minuto MES Convierte un nmero de serie en un valor de mes DIAS.LAB Devuelve el nmero de todos los das laborables existentes entre dos fechas AHORA Devuelve el nmero de serie correspondiente a la fecha y hora actuales SEGUNDO Convierte un nmero de serie en un valor de segundo HORA Devuelve el nmero de serie correspondiente a una hora determinada HORANUMERO Convierte una hora con formato de texto en un valor de nmero de serie HOY Devuelve el nmero de serie correspondiente al da actual DIASEM Convierte un nmero de serie en un valor de da de la semana NUM.DE.SEMANA Convierte un nmero de serie en un nmero que representa el lugar numrico correspondiente a una semana de un ao DIA.LAB Devuelve el nmero de serie de la fecha que tiene lugar antes o despus de un nmero determinado de das laborables AO Convierte un nmero de serie en un valor de ao FRAC.AO Devuelve la fraccin de ao que representa el nmero total de das existentes entre el valor de fecha_inicial y el de fecha_final Funciones de texto Funcin Descripcin ASC Convierte las letras inglesas o katakana de ancho completo (de dos bytes) dentro de una cadena de caracteres en caracteres de ancho medio (de un byte) TEXTOBAHT Convierte un nmero en texto, con el formato de moneda (Baht) CARACTER Devuelve el carcter especificado por el nmero de cdigo LIMPIAR Quita del texto todos los caracteres no imprimibles CODIGO Devuelve un cdigo numrico del primer carcter de una cadena de texto CONCATENAR Concatena varios elementos de texto en uno solo MONEDA Convierte un nmero en texto, con el formato de moneda $ (dlar) IGUAL Comprueba si dos valores de texto son idnticos ENCONTRAR, ENCONTRARB Busca un valor de texto dentro de otro (distingue maysculas de minsculas) 20. Excel 2007 Intermedio 20 DECIMAL Da formato a un nmero como texto con un nmero fijo de decimales JIS Convierte las letras inglesas o katakana de ancho medio (de un byte) dentro de una cadena de caracteres en caracteres de ancho completo (de dos bytes) IZQUIERDA, IZQUIERDAB Devuelve los caracteres del lado izquierdo de un valor de texto LARGO, LARGOB Devuelve el nmero de caracteres de una cadena de texto MINUSC Pone el texto en minsculas EXTRAE, EXTRAEB Devuelve un nmero especfico de caracteres de una cadena de texto que comienza en la posicin que se especifique FONETICO Extrae los caracteres fonticos (furigana) de una cadena de texto NOMPROPIO Pone en mayscula la primera letra de cada palabra de un valor de texto REEMPLAZAR, REEMPLAZARB Reemplaza caracteres de texto REPETIR Repite el texto un nmero determinado de veces DERECHA, DERECHAB Devuelve los caracteres del lado derecho de un valor de texto HALLAR, HALLARB Busca un valor de texto dentro de otro (no distingue maysculas de minsculas) SUSTITUIR Sustituye texto nuevo por texto antiguo en una cadena de texto T Convierte sus argumentos a texto TEXTO Da formato a un nmero y lo convierte en texto ESPACIOS Quita los espacios del texto MAYUSC Pone el texto en maysculas VALOR Convierte un argumento de texto en un nmero Funciones de bsqueda y referencia Funcin Descripcin DIRECCION Devuelve una referencia como texto a una sola celda de una hoja de clculo AREAS Devuelve el nmero de reas de una referencia ELEGIR Elige un valor de una lista de valores COLUMNA Devuelve el nmero de columna de una referencia 21. Excel 2007 Intermedio 21 COLUMNAS Devuelve el nmero de columnas de una referencia IMPORTARDATOSDINAMICOS Devuelve los datos almacenados en una tabla dinmica BUSCARH Busca en la fila superior de una matriz y devuelve el valor de la celda indicada HIPERVINCULO Crea un acceso directo o un salto que abre un documento almacenado en un servidor de red, en una intranet o en Internet INDICE Utiliza un ndice para elegir un valor de una referencia o matriz INDIRECTO Devuelve una referencia indicada por un valor de texto BUSCAR Busca valores de un vector o una matriz COINCIDIR Busca valores de una referencia o matriz DESREF Devuelve un desplazamiento de referencia respecto a una referencia dada FILA Devuelve el nmero de fila de una referencia FILAS Devuelve el nmero de filas de una referencia RDTR Recupera datos en tiempo real desde un programa compatible con la automatizacin COM TRANSPONER Devuelve la transposicin de una matriz BUSCARV Busca en la primera columna de una matriz y se mueve en horizontal por la fila para devolver el valor de una celda Funciones lgicas Funcin Descripcin Y Devuelve VERDADERO si todos sus argumentos son VERDADERO FALSO Devuelve el valor lgico FALSO SI Especifica una prueba lgica que realizar NO Invierte el valor lgico del argumento O Devuelve VERDADERO si cualquier argumento es VERDADERO VERDADERO Devuelve el valor lgico VERDADERO 22. Excel 2007 Intermedio 22 MODULO 2 Aplicacin de Funciones de Fecha y Hora Funcin AHORA Devuelve la fecha y hora actuales. Si el formato de celda era General antes de escribir la funcin, el resultado tendr formato de fecha. Sintaxis: AHORA( ) Ejemplo Funcin HOY Devuelve la fecha actual. Si el formato de celda era General antes de escribir la funcin, el resultado tendr formato de fecha. Sintaxis: HOY( ) Ejemplo Las funciones AHORA() y HOY() slo cambian cuando se actualiza la hoja de clculo, o cuando se ejecuta una macro que contiene esas funciones. No se actualizan continuamente. La fecha y hora utilizadas se toman del reloj del sistema del equipo. Funcin DIA Devuelve el da de una fecha. El da se expresa como un nmero entero comprendido entre 1 y 31. MODULO 2 23. Excel 2007 Intermedio 23 Sintaxis: DIA(nm_de_serie) nm_de_serie es la fecha del da que intenta buscar. Ejemplo Funcin MES Devuelve el mes de una fecha. El mes se expresa como nmero entero comprendido entre 1 (enero) y 12 (diciembre). Sintaxis: MES(nm_de_serie) nm_de_serie es la fecha del mes que intenta buscar. Ejemplo Funcin AO Devuelve el ao correspondiente a una fecha. El ao se devuelve como nmero entero comprendido entre 1900 y 9999. Sintaxis: AO(nm_de_serie) nm_de_serie es la fecha del ao que se desea buscar. Ejemplo 24. Excel 2007 Intermedio 24 Funcin HORA Devuelve la hora de un valor de hora. La hora se expresa como nmero entero, comprendido entre 0 (12:00 a.m.) y 23 (11:00 p.m.). Sintaxis HORA(nm_de_serie) nm_de_serie es el valor de hora que contiene la hora que se desea obtener. Ejemplo Funcin FECHA Devuelve el nmero de serie secuencial que representa una fecha determinada. Si el formato de celda era General antes de escribir la funcin, el resultado tendr formato de fecha. Sintaxis FECHA(ao;mes;da) Ao puede tener de uno a cuatro dgitos. Mes es un entero que representa el mes del ao, desde el 1 al 12 (de enero a diciembre). Da es un entero que representa el da del mes, desde el 1 al 31. Ejemplo Funcin DIAS360 Calcula el nmero de das entre dos fechas basndose en un ao de 360 das (doce meses de 30 das) que se utiliza en algunos clculos contables. Use esta funcin para facilitar el clculo de pagos si su sistema de contabilidad se basa en 12 meses de 30 das. Sintaxis 25. Excel 2007 Intermedio 25 DIAS360(fecha_inicial;fecha_final;mtodo) fecha_inicial, fecha_final son las dos fechas entre las que se desea calcular el nmero de das. Si la fecha_inicial es posterior a la fecha_final, DIAS360 devuelve un nmero negativo. Ejemplo 26. Excel 2007 Intermedio 26 MODULO 3 Aplicacin de Funciones de Texto Funcin CONCATENAR Concatena dos o ms cadenas en una cadena de texto. Sintaxis CONCATENAR (texto1;texto2; ...) Texto1, texto2...son de 2 a 255 elementos de texto que se unirn en un elemento de texto nico. Los elementos de texto pueden ser cadenas de texto, nmeros o referencias a celdas nicas. Observaciones Tambin puede utilizar el operador de clculo smbolo de "y" comercial (&) en lugar de la funcin CONCATENAR para unir elementos de texto. Por ejemplo =A1&B1 devuelve el mismo valor que =CONCATENAR(A1;B1). Ejemplo Funcin ENCONTRAR La funcin ENCONTRAR busca una cadena de texto dentro de una segunda cadena y devuelven el nmero de la posicin inicial de la primera cadena de texto a partir del primer carcter de la segunda cadena de texto. Sintaxis ENCONTRAR(texto_buscado;dentro_del_texto;nm_inicial) Texto_buscado es el texto que desea encontrar. Dentro_del_texto es el texto que a su vez contiene el texto que desea encontrar. MODULO 3 27. Excel 2007 Intermedio 27 Nm_inicial especifica el carcter a partir del cual comenzar la bsqueda. El primer carcter de dentro_del_texto es el carcter nmero 1. Si omite nm_inicial, se supone que es 1. Observaciones La funcin ENCONTRAR distingue entre maysculas y minsculas y no permite el uso de caracteres comodn. Si no desea realizar una bsqueda con distincin de maysculas y minsculas o donde se utilicen caracteres comodn, puede utilizar HALLAR. Si texto_buscado es "" (texto vaco), BUSCAR coincide con el primer carcter de la cadena de bsqueda (es decir, el carcter de nm_inicial o 1). Texto_buscado no puede contener ningn carcter comodn. Si texto_buscado no aparece en dentro_del_texto, ENCONTRAR devuelve el valor de error #VALOR!. Si nm_inicial no es mayor que cero, ENCONTRAR y ENCONTRARB devuelven el valor de error #VALOR!. Si nm_inicial es mayor que la longitud de dentro_del_texto, ENCONTRAR devuelve el valor de error #VALOR!. Utilice nm_inicial para omitir un nmero especfico de caracteres. Ejemplo Funcin MINUSC Convierte todas las maysculas de una cadena de texto en minsculas. Sintaxis MINUSC(texto) Texto es el texto que se desea convertir en minsculas. MINUSC no cambia los caracteres de texto que no sean letras. Ejemplo 28. Excel 2007 Intermedio 28 Funcin MAYUSC Pone el texto en maysculas. Sintaxis MAYUSC(texto) Texto es el texto que se desea pasar a maysculas. El argumento texto puede ser una referencia o una cadena de texto. Ejemplo Funcin EXTRAE EXTRAE devuelve un nmero especfico de caracteres de una cadena de texto, comenzando en la posicin que especifique y en funcin del nmero de caracteres que especifique. Sintaxis EXTRAE(texto;posicin_inicial;nm_de_caracteres) Texto Cadena de texto que contiene los caracteres que se desea extraer. Posicin_inicial Posicin del primer carcter que se desea extraer del texto. La posicin_inicial del primer carcter de texto es 1, y as sucesivamente. Nm_de_caracteres Especifica el nmero de caracteres que se desea que EXTRAE devuelva del argumento texto. Observaciones Si posicin_inicial es mayor que la longitud de texto, EXTRAE devuelve "" (texto vaco). Si posicin_inicial es menor que la longitud de texto, pero posicin_inicial ms nm_de_caracteres excede la longitud de texto, EXTRAE devuelve los caracteres hasta el final de texto. Si posicin_inicial es menor que 1, EXTRAE devuelve el valor de error #VALOR!. Si nm_de_caracteres es negativo, EXTRAE devuelve el valor de error #VALOR!. Ejemplo 29. Excel 2007 Intermedio 29 30. Excel 2007 Intermedio 30 MODULO 4 Aplicacin de Funciones de Bsqueda y Referencia Funcin ELEGIR Utiliza el argumento nm_ndice para devolver un valor de una lista de argumentos de valores. Utilice ELEGIR para seleccionar uno de los 254 valores posibles a partir del rango del argumento ndice. Por ejemplo, si valor1 a valor7 son los das de la semana, ELEGIR devuelve uno de los das cuando se utiliza un nmero entre 1 y 7 como argumento nm_ndice. Sintaxis ELEGIR(nm_ndice;valor1;valor2;...) nm_ndice especifica el argumento de valor que se selecciona. El argumento nm_ndice debe ser un nmero entre 1 y 254, o bien, una frmula o referencia a una celda que contenga un nmero entre 1 y 254. Si nm_ndice es 1, ELEGIR devuelve valor1; si es 2, ELEGIR devuelve valor2 y as sucesivamente. Si nm_ndice es menor que 1 o mayor que el nmero del ltimo valor de la lista, ELEGIR devuelve el valor de error #VALOR!. Si nm_ndice es una fraccin, se trunca al entero inferior antes de ser utilizada. valor1,valor2,... son de 1 a 254 argumentos de valores entre los cuales la funcin ELEGIR selecciona un valor o accin que se ejecuta basndose en el argumento nm_ndice. Los argumentos pueden ser nmeros, referencias a celdas, nombres definidos, frmulas, funciones o texto. Ejemplo Funcin INDICE Devuelve un valor o la referencia a un valor en una tabla o matriz, seccionado por los ndices de nmero de fila y de columna. Sintaxis INDICE(matriz;nm_fila;nm_columna) MODULO 4 31. Excel 2007 Intermedio 31 Matriz es un rango de celdas. Nm_fila selecciona, en el rango matriz, la fila desde la cual se devolver un valor. Si se omite nm_fila, se requiere el argumento nm_columna. Nm_columna selecciona, en el rango matriz, la columna desde la cual se devolver un valor. Si se omite nm_columna, se requiere el argumento nm_fila. Observaciones Si se utilizan los argumentos nm_fila y nm_columna, INDICE devuelve el valor de la celda donde se produce la interseccin de los argumentos. Los argumentos nm_fila y nm_columna deben indicar una celda incluida en matriz; de lo contrario, INDICE devuelve el valor de error #REF! Ejemplo Funcin BUSCAR Devuelve un valor procedente de un rango de una fila o columna, o bien, de una matriz. La funcin BUSCAR tiene dos formas de sintaxis: vectorial y matricial. Forma vectorial Si desea Uso Buscar en un rango de una fila o una columna (denominado vector) un valor y devolver un valor desde la misma posicin en un segundo rango. Utilice la forma vectorial cuando disponga de una amplia lista de valores en los que buscar o cuando es posible que los valores cambien con el tiempo. Un vector es un rango de una sola fila o columna. La forma vectorial de BUSCAR busca un valor en un rango (denominado vector) y devuelve otro valor desde la misma posicin en un segundo rango. 32. Excel 2007 Intermedio 32 BUSCAR(valor_buscado;vector_de_comparacin;vector_resultado) Valor_buscado Valor que busca la funcin BUSCAR en el primer vector. Valor_buscado puede ser un nmero, texto, un valor lgico, o un nombre o referencia que se refiere a un valor. Vector_de_comparacin Rango que slo contiene una fila o una columna. Los valores del vector_de_comparacin pueden ser texto, nmeros o valores lgicos. Los valores en el vector_de_comparacin deben colocarse en orden ascendente: ...;-2; -1; 0; 1; 2; ...; A-Z; FALSO; VERDADERO; de lo contrario, BUSCAR puede dar un valor incorrecto. El texto en maysculas y en minsculas es equivalente. Vector_resultado Rango que slo incluye una fila o una columna. Debe ser del mismo tamao que vector_de_comparacin. Ejemplo Forma matricial Si desea Uso Buscar el valor especificado en la primera fila o columna de una matriz y devolver un valor desde la misma posicin en la ltima fila o columna de la matriz. Utilice esta forma cuando disponga de una pequea lista de valores y stos permanezcan siempre constantes. Con la forma matricial de BUSCAR se busca el valor especificado en la primera fila o columna de una matriz y se devuelve un valor desde la misma posicin en la ltima fila o columna de la matriz. Utilice esta forma cuando los valores que desee buscar se encuentren en la primera fila o columna de la matriz. La otra forma de la funcin se emplea cuando se desea especificar la ubicacin de la fila o columna. BUSCAR(valor_buscado;matriz) Valor_buscado Valor que busca la funcin BUSCAR en una matriz. Valor_buscado puede ser un nmero, texto, un valor lgico o un nombre o referencia que se refiera a un valor. 33. Excel 2007 Intermedio 33 Matriz Rango de celdas que incluye el texto, los nmeros o los valores lgicos que se desean comparar con valor_buscado. Ejemplo Funcin BUSCARV Busca un valor especfico en la primer columna de una matriz de tabla y devuelve, en la misma fila, un valor de otra columna de dicha matriz de tabla. La V de BUSCARV significa vertical. Sintaxis BUSCARV(valor_buscado;matriz_buscar_en;indicador_columnas;ordenado) Valor_buscado Valor que se va a buscar en la primera columna de la matriz de tabla. Valor_buscado puede ser un valor o una referencia. Si valor_buscado es inferior al menor de los valores de la primera columna de matriz_buscar_en, BUSCARV devuelve al valor de error #N/A. Matriz_buscar_en Dos o ms columnas de datos. Use una referencia a un rango o un nombre de rango. Los valores de la primera columna de matriz_buscar_en son los valores que busca valor_buscado. Estos valores pueden ser texto, nmeros o valores lgicos. Las maysculas y minsculas del texto son equivalentes. Indicador_columnas Nmero de columna de matriz_buscar_en desde la cual debe devolverse el valor coincidente. Si el argumento indicador_columnas es igual a 1, la funcin devuelve el valor de la primera columna del argumento matriz_buscar_en; si el argumento indicador_columnas es igual a 2, devuelve el valor de la segunda columna de matriz_buscar_en y as sucesivamente. Si indicador_columnas es: Si es inferior a 1, BUSCARV devuelve al valor de error #VALUE! Si es superior al nmero de columnas de matriz_buscar_en, BUSCARV devuelve el valor de error #REF! Ordenado Valor lgico que especifica si BUSCARV va a buscar una coincidencia exacta o aproximada: Si se omite o es VERDADERO, se devolver una coincidencia exacta o aproximada. Si no localiza ninguna coincidencia exacta, devolver el siguiente valor ms alto inferior a valor_buscado. Los valores de la primera columna de matriz_buscar_en deben estar clasificados segn un criterio de ordenacin ascendente; en caso contrario, es posible que BUSCARV no devuelva el valor correcto. Para obtener ms informacin, vea Ordenar datos. 34. Excel 2007 Intermedio 34 Si es FALSO, BUSCARV slo buscar una coincidencia exacta. En este caso, no es necesario ordenar los valores de la primera columna de matriz_buscar_en. Si hay dos o ms valores en la primera columna de matriz_buscar_en, se utilizar el primer valor encontrado. Si no se encuentra una coincidencia exacta, se devolver el valor de error #N/A. Ejemplo Funcin BUSCARH Busca un valor en la fila superior de una tabla o una matriz de valores y, a continuacin, devuelve un valor en la misma columna de una fila especificada en la tabla o matriz. Use BUSCARH cuando los valores de comparacin se encuentren en una fila en la parte superior de una tabla de datos y desee encontrar informacin que se encuentre dentro de un nmero especificado de filas. La H de BUSCARH significa "Horizontal". Sintaxis BUSCARH(valor_buscado;matriz_buscar_en;indicador_filas; ordenado) Valor_buscado es el valor que se busca en la primera fila de la tabla. Valor_buscado puede ser un valor, una referencia o una cadena de texto. Matriz_buscar_en es una tabla de informacin en la que se buscan los datos. Utilice una referencia a un rango o el nombre de un rango. Los valores de la primera fila del argumento matriz_buscar_en pueden ser texto, nmeros o valores lgicos. Si ordenado es VERDADERO, los valores de la primera fila de matriz_buscar_en deben colocarse en orden ascendente: ...-2, -1, 0, 1, 2, ..., A-Z, FALSO, VERDADERO; de lo contrario, BUSCARH puede devolver un valor incorrecto. Si ordenado es FALSO, no es necesario ordenar matriz_buscar_en. El texto en maysculas y en minsculas es equivalente. Ordena los valores en orden ascendente, de izquierda a derecha. Para obtener ms informacin, vea Ordenar datos. 35. Excel 2007 Intermedio 35 Indicador_filas es el nmero de fila en matriz_buscar_en desde el cual debe devolverse el valor coincidente. Si indicador_filas es 1, devuelve el valor de la primera fila en matriz_buscar_en; si indicador_filas es 2, devuelve el valor de la segunda fila en matriz_buscar_en y as sucesivamente. Si indicador_filas es menor que 1, BUSCARH devuelve el valor de error #VALOR!; si indicador_filas es mayor que el nmero de filas en matriz_buscar_en, BUSCARH devuelve el valor de error #REF! Ordenado es un valor lgico que especifica si BUSCARH debe localizar una coincidencia exacta o aproximada. Si es VERDADERO o se omite, devolver una coincidencia aproximada. Es decir, si no encuentra ninguna coincidencia exacta, devolver el siguiente valor mayor que sea inferior a valor_buscado. Si es FALSO, BUSCARH encontrar una coincidencia exacta. Si no encuentra ninguna, devolver el valor de error #N/A. Ejemplo 36. Excel 2007 Intermedio 36 MODULO 5 Aplicacin de Funciones Lgicas Funcin SI Devuelve un valor si la condicin especificada es VERDADERO y otro valor si dicho argumento es FALSO. Utilice SI para realizar pruebas condicionales en valores y frmulas. Sintaxis SI(prueba_lgica;valor_si_verdadero;valor_si_falso) Prueba_lgica es cualquier valor o expresin que pueda evaluarse como VERDADERO o FALSO. Por ejemplo, A10=100 es una expresin lgica; si el valor de la celda A10 es igual a 100, la expresin se evala como VERDADERO. De lo contrario, se evaluar como FALSO. Este argumento puede utilizar cualquier operador de comparacin. Valor_si_verdadero es el valor que se devuelve si el argumento prueba_lgica es VERDADERO. Por ejemplo, si este argumento es la cadena de texto "Dentro de presupuesto" y el argumento prueba_lgica se evala como VERDADERO, la funcin SI muestra el texto "Dentro de presupuesto". Si el argumento prueba_lgica es VERDADERO y el argumento valor_si_verdadero est en blanco, este argumento devuelve 0 (cero). Para mostrar la palabra VERDADERO, utilice el valor lgico VERDADERO para este argumento. Valor_si_verdadero puede ser otra frmula. Valor_si_falso es el valor que se devuelve si el argumento prueba_lgica es FALSO. Por ejemplo, si este argumento es la cadena de texto "Presupuesto excedido" y el argumento prueba_lgica se evala como FALSO, la funcin SI muestra el texto "Presupuesto excedido". Si el argumento prueba_lgica es FALSO y se omite valor_si_falso, (es decir, despus de valor_si_verdadero no hay ninguna coma), se devuelve el valor lgico FALSO. Si prueba_lgica es FALSO y valor_si_falso est en blanco (es decir, despus de valor_si_verdadero hay una coma seguida por el parntesis de cierre), se devuelve el valor 0 (cero). Valor_si_falso puede ser otra frmula. Observaciones Es posible anidar hasta 64 funciones SI como argumentos valor_si_verdadero y valor_si_falso para crear pruebas ms complicadas. (Vea el ejemplo 3 para ver una muestra de funciones SI anidadas). Como alternativa, para comprobar muchas condiciones, plantese usar las funciones BUSCAR, BUSCARV o BUSCARH. (Vea el ejemplo 4 para obtener una muestra de la funcin BUSCARH.) Cuando los argumentos valor_si_verdadero y valor_si_falso se evalan, la funcin SI devuelve el valor devuelto por la ejecucin de las instrucciones. MODULO 5 37. Excel 2007 Intermedio 37 Si uno de los argumentos de la funcin SI es una matriz, cada elemento de la matriz se evaluar cuando se ejecute la instruccin SI. Microsoft Excel proporciona funciones adicionales que se pueden utilizar para analizar los datos en funcin de una condicin. Por ejemplo, para contar el nmero de veces que una cadena de texto o un nmero aparecen dentro de un rango de celdas, utilice las funciones de hoja de clculo CONTAR.SI y CONTAR.SI.CONJUNTO. Para calcular una suma basndose en una cadena de texto o un nmero de un rango, utilice las funciones SUMAR.SI y SUMAR.SI.CONJUNTO. Ejemplo 1 Ejemplo 2 Funcin Y Devuelve VERDADERO si todos los argumentos son VERDADERO; devuelve FALSO si uno o ms argumentos son FALSO. Sintaxis Y(valor_lgico1;valor_lgico2;...) Valor_lgico1, Valor_lgico2, ... son de 1 a 255 condiciones que se desea comprobar y que pueden tener el resultado de VERDADERO o FALSO. Observaciones Los argumentos deben evaluarse como valores lgicos, como VERDADERO o FALSO, o bien deben ser matrices o referencias que contengan valores lgicos. Si un argumento matricial o de referencia contiene texto o celdas vacas, dichos valores se pasarn por alto. Si el rango especificado no contiene valores lgicos, la funcin Y devuelve el valor de error #VALOR!. Ejemplo 38. Excel 2007 Intermedio 38 Funcin O Devolver VERDADERO si alguno de los argumentos es VERDADERO; devolver FALSO si todos los argumentos son FALSO. Sintaxis O(valor_lgico1;valor_lgico2; ...) Valor_lgico1, Valor_lgico2, ... son de 1 a 255 condiciones que se desea comprobar y que pueden tener el resultado de VERDADERO o FALSO. Observaciones Los argumentos deben evaluarse como valores lgicos, como VERDADERO O FALSO, o bien en matrices o referencias que contengan valores lgicos. Si un argumento matricial o de referencia contiene texto o celdas vacas, dichos valores se pasarn por alto. Si el rango especificado no contiene valores lgicos, la funcin O devuelve el valor de error #VALOR!. Puede utilizar la frmula de matriz O para comprobar si un valor aparece en una matriz. Para especificar una frmula de matriz, presione CTRL+MAYS+ENTRAR. Ejemplo Funcin NO Invierte el valor lgico del argumento. Use NO cuando desee asegurarse de que un valor no sea igual a otro valor especfico. Sintaxis NO(valor_lgico) 39. Excel 2007 Intermedio 39 Valor_lgico es un valor o expresin que puede evaluarse como VERDADERO o FALSO. Observacin Si valor_lgico es FALSO, NO devuelve VERDADERO; si valor_lgico es VERDADERO, NO devuelve FALSO. Ejemplo 40. Excel 2007 Intermedio 40 Edicin Avanzada En este modulo estudiaremos algunas caractersticas avanzadas de Excel que nos pueden ser de utilidad cuando queramos realizar algn trabajo un poco ms profesional Relacin entre hojas y libros (referencias externas vnculos) Puede hacer referencia al contenido de las celdas de otro libro mediante una referencia externa. Una referencia externa (tambin denominada vnculo) es una referencia a una celda o a un rango de celdas de una hoja de clculo de otro libro de Excel, o una referencia a un nombre definido en otro libro. Puede crear referencias externas entre los libros para traer informacin de un libro de origen a un libro de destino. Desde un libro de destino, puede abrir fcilmente el libro de origen de una referencia externa. Dnde pueden utilizarse eficazmente las referencias externas Las referencias externas son especialmente tiles cuando no resulta prctico mantener juntos en el mismo libro los modelos de hoja de clculo grandes. Combinar datos de varios libros Puede vincular libros de varios usuarios o departamentos e integrar los datos pertinentes en un libro de resumen. De este modo, al cambiar los libros de origen, no ser necesario cambiar manualmente el libro de resumen. Crear distintas presentaciones de los datos Pueden insertarse todos los datos en uno o ms libros de origen y, a continuacin, crear un libro de informe que slo contenga las referencias externas a los datos pertinentes. Simplificar modelos grandes y complejos Al dividir un modelo complicado en una serie de libros interdependientes, podr trabajar en el modelo sin necesidad de abrir todas las hojas relacionadas con l. Los libros de menor tamao son ms fciles de modificar, no necesitan mucha memoria y se pueden abrir, guardar y calcular con ms rapidez. MODULO 6 41. Excel 2007 Intermedio 41 Aspecto de una referencia externa a otro libro Las frmulas con referencias externas a otros libros se muestran de dos maneras, segn el libro de origen, el que proporciona datos a la frmula, se encuentre abierto o cerrado. Cuando el libro de origen est abierto, la referencia externa incluye el nombre del libro entre corchetes, seguido del nombre de la hoja de clculo, un signo de exclamacin (!) y las celdas de las que depende la frmula. Por ejemplo, la siguiente frmula suma las celdas C10:C25 del libro Presupuesto.xls. Referencia externa =SUMA([Presupuesto.xlsx]Anual!C10:C25) Si el libro de origen est cerrado, la referencia externa incluye toda la ruta de acceso. Referencia externa =SUMA('C:Informes[Presupuesto.xlsx]Anual'!C10:C25) Crear una referencia externa entre celdas de libros distintos 1. Abra el libro que contendr la referencia externa (el libro de destino) y el libro que contiene los datos con los que desea establecer el vnculo (el libro de origen). 2. En el libro de origen, haga clic en Guardar en la barra de herramientas de acceso rpido. 3. Seleccione en el libro destino la celda en las que desea crear la referencia externa. 4. Escriba = (signo de igual). Si desea ejecutar clculos o funciones en el valor de la referencia externa, escriba el operador o la funcin que debe preceder a la referencia externa. 5. Cambie al libro de origen y, a continuacin, haga clic en la hoja de clculo que contiene las celdas con las que desea establecer el vnculo. 6. Seleccione la celda o las celdas con las que desee establecer el vnculo. 7. Vuelva al libro de destino y observe que Excel agrega la referencia al libro de origen y a las celdas que seleccion en el paso anterior. 8. Tambin puede modificar o cambiar la frmula en la hoja de clculo de destino. Controlar los vnculos a otros libros Cuando el libro de origen y el libro de destino estn abiertos en el mismo equipo, los vnculos se actualizan de forma automtica. Si el libro de origen no est abierto en el momento de abrir el libro de destino, la barra de confianza puede preguntarle si desea actualizar los vnculos. 42. Excel 2007 Intermedio 42 Actualizar de forma manual todos los vnculos o no actualizar ningn vnculo de un libro 1. Cierre todos los libros. Si un libro de origen se queda abierto y los dems se cierran, las actualizaciones no sern uniformes. 2. Abra el libro que contiene los vnculos. 3. Para actualizar los vnculos, en la barra de confianza, haga clic en Opciones y, a continuacin, en Habilitar este contenido. Actualizar de forma manual slo algunos de los vnculos a otros libros 1. Cierre todos los libros. 2. Abra el libro que contiene los vnculos. 3. En la ficha Datos, en el grupo Conexiones, haga clic en Editar vnculos. 4. En la lista Origen, haga clic en el objeto vinculado que desee actualizar. 5. Para seleccionar varios vnculos, mantenga presionada la tecla CTRL y haga clic en cada objeto vinculado. 6. Para seleccionar todos los vnculos, presione CTRL+E. 7. Haga clic en Actualizar valores. Consolidar datos en varias hojas de clculo Para resumir y registrar resultados de hojas de clculo independientes, puede consolidar datos de cada una de estas hojas en una hoja de clculo maestra. Al consolidar datos, lo que se hace es ensamblarlos de modo que sea ms fcil actualizarlos y agregarlos de una forma peridica o especfica. Por ejemplo, si tiene una hoja de clculo de cifras de gastos para cada una de las oficinas regionales, podra utilizar una consolidacin para resumir estas cifras en una hoja de clculo de gastos corporativa. Esta hoja de clculo maestra podra contener totales de ventas y promedios, niveles de inventario actuales y los productos ms vendidos de toda la organizacin. Otro ejemplo donde se puede utilizar la consolidacin de hojas de trabajo puede ser una compaa que dispone de varios departamentos, cada uno de ellos con un presupuesto, para conocer el presupuesto total de la empresa, crearamos una hoja dependiente y los libros fuentes seran los libros de los departamentos con sus prepuestos. 43. Excel 2007 Intermedio 43 Consolide por posicin 1. Configure los datos que se van a consolidar en cada una de las hojas de clculo independientes. 2. Haga clic en la celda superior izquierda del rea donde desee que aparezcan los datos consolidados en la hoja de clculo maestra. Asegrese de dejar suficientes celdas a la derecha y por debajo de esta celda para los datos de consolidacin. El comando Consolidar rellena el rea segn proceda. 3. En la ficha Datos, en el grupo Herramientas de datos, haga clic en Consolidar. 4. En el cuadro Funcin, haga clic en la funcin de resumen que desea que utilice Microsoft Office Excel para consolidar los datos. 5. Si la hoja de clculo se encuentra en otro libro, haga clic en Examinar para buscar el archivo y, a continuacin, haga clic en Aceptar para cerrar el cuadro de dilogo Examinar. La ruta de acceso del archivo se escribe en el cuadro Referencia seguido de un signo de exclamacin. 6. Escriba el nombre que le asign al rango y, a continuacin, haga clic en Agregar. Repita este paso para cada rango. 7. Decida cmo desea actualizar la consolidacin. Siga uno de los procedimientos siguientes: 8. Deje en blanco las casillas de Usar rtulos en. Excel no copia los rtulos de fila o columna de los rangos de origen a la consolidacin. Si desea rtulos para los datos consolidados, cpielos de uno de los rangos de origen o introdzcalos manualmente. 44. Excel 2007 Intermedio 44 Consolide por frmula 1. En la hoja de clculo maestra, copie o escriba los rtulos de columna o fila que desee para los datos de consolidacin. 2. Haga clic en la celda en que desea incluir los datos de consolidacin. 3. Escriba una frmula que incluya una referencia de celda a las celdas de origen de cada hoja de clculo o una referencia 3D que contenga los datos que desea consolidar. En cuanto a las referencias de celda, siga uno de los procedimientos siguientes: Si los datos que se van a consolidar estn en celdas diferentes de otras hoja de clculo Escriba una frmula con referencias de celda a las otras hojas de clculo, una por cada hoja de clculo independiente. Por ejemplo, para consolidar datos de hojas de clculo denominadas Ventas (en la celda B4), HR (en la celda F5) y Marketing (en la celda B9), en la celda A2 de la hoja de clculo maestra, tendra que escribir lo siguiente: Sugerencia Para especificar una referencia de celda como Ventas3!B4 en una frmula sin escribir, escriba la frmula hasta el punto en el que necesite la referencia, haga clic en la etiqueta de la hoja de clculo y, a continuacin, haga clic en la celda. Si los datos que se van a consolidar estn en las mismas celdas de otras hojas de clculo Escriba una frmula con una referencia 3D que utilice una referencia a un rango de nombres de hojas de clculo. Por ejemplo, para consolidar datos en las celdas A2 desde Ventas hasta Marketing inclusive, en la celda A2 de la hoja de clculo maestra tendra que escribir lo siguiente: Bloques tridimensionales Sabemos que es posible seleccionar ms de una hoja dentro de un libro, por ejemplo para imprimir ms de una hoja o bien para trabajar conjuntamente con los datos de esas hojas. Recordemos los mtodos de seleccin de hojas; son los siguientes: Para seleccionar varias hojas consecutivas, haremos clic en la ficha de la primera del grupo, mantendremos pulsada la tecla MAYS y haremos clic en la 45. Excel 2007 Intermedio 45 ltima -la de ms a la derecha- del grupo. Por ejemplo, tenemos tantas hojas como meses del ao y deseamos seleccionar todas desde la de Enero hasta la de Diciembre. Para seleccionar varias hojas NO consecutivas, haremos clic en la ficha de cada una de las hojas -quizs no consecutivas- que se deseen seleccionar mientras se mantienen pulsada la tecla CTRL. Teniendo seleccionadas varias hojas, cualquier modificacin que se realice en una de esas hojas, se plasmar en todas las hojas que componen el bloque de seleccin. Esta modificacin ya puede ser de formato o bien de contenido de celdas. El empleo de bloques tridimensionales permite realizar de forma rpida una serie de operaciones comunes a todas las hojas seleccionadas. Supongamos, por ejemplo, que deseamos disear una hoja de contabilidad sencilla, que contenga los ingresos, los gastos y los beneficios, para todos los meses del ao y deseamos los datos de cada mes en una hoja; una solapa para enero, otra para febrero, otra para marzo... De este modo, utilizando bloques tridimensionales, podramos realizar operaciones dentro de una de ellas, que se aplicaran a todas las hojas que lo componen. Algunas de las operaciones que van a afectar a todo el bloque tridimensional son las siguientes: Introduccin de datos En este caso se puede introducir datos en todas las hojas sin ms que teclearlo en una de ellas. Para ello debemos crear de manera previa todas las hojas que vamos a utilizar, ordenarlas y asignarles un nombre. Una vez hecho esto, seguiremos los siguientes pasos: 1. Hacer clic sobre la solapa de la primera hoja. 2. Manteniendo la tecla Maysculas pulsada hacer clic sobre la solapa de la tercera hoja. 3. Introducir el contenido deseado en las celdas de cualquiera de las hojas seleccionadas. Automticamente en las celdas de todas las hojas que componen el bloque tridimensional, aparecen los mismos contenidos. Es decir, el contenido se ha copiado ha tantas hojas como tuviramos seleccionadas. Aplicar formato. Tambin es posible dar formato a las celdas de una de las hojas del bloque, consiguiendo la aplicacin de este formato a todas las hojas seleccionadas. Este aspecto es muy til en el caso de que las hojas presenten una misma estructura. Los pasos a seguir son: 1. Seleccionar las hojas sobre las cuales aplicar el mismo formato. 2. Aplicar en una de ellas, el formato comn que queremos que afecte a todas. 3. Si se quiere aplicar un determinado formato slo a parte de las celdas seleccionadas bastar con hacer una nueva seleccin antes de aplicarlo. 46. Excel 2007 Intermedio 46 Realizar operaciones. Tambin se pueden emplear los bloques tridimensionales en la creacin de frmulas o funciones. Por ejemplo, si se quiere calcular en una celda la suma total de los ingresos correspondientes a los aos 2007 y 2008 del ejemplo anterior, los pasos a seguir son: 1. Situarse en la celda en la cual se va a introducir la frmula -por ejemplo en la hoja totales en la que se quieren resumir datos de los ltimos aos-. 2. Hacer clic sobre el botn Autosuma. 3. Hacer clic sobre la solapa de la hoja ao 2007 y seleccionar el rango B4:B7. 4. Pulsar la tecla Maysculas y, sin soltarla, hacer clic sobre la solapa de la hoja ao 2008. 5. Finalmente pulsar la tecla Intro para validar la frmula. Observar como al rango correspondiente a las celdas le precede el rango correspondiente a las hojas. Esta expresin tridimensional se interpreta de la siguiente manera: Sumar las celdas del rango B4:B7 correspondientes a las hojas que van desde la llamada "Ao 2007" hasta la llamada "Ao 2008". Podramos haber seleccionado desde el ao 2004 hasta el 2008 -de haber existido- abarcando de esta manera un rango "cbico" ms amplio. En este caso, no se trata de involucrar en una frmula o funcin a una celda, a varias celdas o bien a un rango de celdas sino a un cubo tridimensional de celdas. 47. Excel 2007 Intermedio 47 Formato de celdas Excel 2007 no solo permite realizar clculos sino que tambin permite darle una buena presentacin a las hojas de clculo resaltando la informacin ms interesante, de esta forma con un solo vistazo podremos percibir la informacin ms importante y as sacar conclusiones de forma rpida y eficiente. Por ejemplo podemos llevar la cuenta de todos nuestros gastos y nuestras ganancias del ao y resaltar en color rojo las prdidas y en color verde las ganancias, de esta forma sabremos rpidamente si el ao ha ido bien o mal. Veremos las diferentes opciones disponibles en Excel 2007 respecto al cambio de aspecto de las celdas de una hoja de clculo y cmo manejarlas para modificar el tipo y aspecto de la letra, la alineacin, bordes, sombreados y forma de visualizar nmeros en la celda. Utilice el cuadro de dilogo Formato de celdas para aplicar formato al contenido de las celdas seleccionadas. Nmero Excel permite modificar la visualizacin de los nmeros en la celda. En la ficha Inicio, en el grupo Nmero, disponemos de una serie de opciones que nos permitir modificar el formato de los nmeros en una forma ms rpida. Otra opcin es utilizar el cuadro de dilogo Formato de celdas Las categoras ms utilizadas son: General: Visualiza en la celda exactamente el valor introducido. Es el formato que utiliza Excel por defecto. Nmero: Contiene una serie de opciones que permiten especificar el nmero de decimales, tambin permite especificar el separador de millares y la forma de visualizar los nmeros negativos. Moneda: Es parecido a la categora Nmero, permite especificar el nmero de decimales, se puede escoger el smbolo monetario como podra ser y la forma de visualizar los nmeros negativos. 48. Excel 2007 Intermedio 48 Contabilidad: Difiere del formato moneda en que alinea los smbolos de moneda y las comas decimales en una columna. Fecha: Contiene nmeros que representan fechas y horas como valores de fecha. Puede escogerse entre diferentes formatos de fecha. Hora: Contiene nmeros que representan valores de horas. Puede escogerse entre diferentes formatos de hora. Porcentaje: Visualiza los nmeros como porcentajes. Se multiplica el valor de la celda por 100 y se le asigna el smbolo %, por ejemplo, un formato de porcentaje sin decimales muestra 0,1528 como 15%, y con 2 decimales lo mostrara como 15,28%. Fraccin: Permite escoger entre nueve formatos de fraccin. Cientfica: Muestra el valor de la celda en formato de coma flotante. Podemos escoger el nmero de decimales. Texto: Las celdas con formato de texto son tratadas como texto incluso si en el texto se encuentre algn nmero en la celda. Especial: Contiene algunos formatos especiales, como puedan ser el cdigo postal, el nmero de telfono, etc. Personalizada: Aqu podemos crear un nuevo formato, indicando el cdigo de formato. 49. Excel 2007 Intermedio 49 Alineacin Se puede asignar formato a las entradas de las celdas a fin de que los datos queden alineados u orientados de una forma determinada. En la ficha Inicio, en el grupo Alineacin, disponemos de una serie de opciones que nos permitir alinear los datos de una hoja de clculo en una forma ms rpida. Otra opcin es utilizar el cuadro de dilogo Formato de celdas Alineacin del texto Horizontal.- permite cambiar la alineacin horizontal del contenido de la celda. De forma predeterminada, Microsoft Office Excel alinea el texto a la izquierda, los nmeros a la derecha y se centran los valores de errores y lgicos. La alineacin horizontal predeterminada es General. El cambio de la alineacin de datos no cambia el tipo de datos. Vertical.- permite cambiar la alineacin vertical del contenido de la celda. De forma predeterminada, Excel alinea el texto verticalmente en la parte inferior de una celda. Sangra.- aplica sangra al contenido de una celda desde cualquier borde de la misma, en funcin de si elige Horizontal y Vertical. Cada incremento del cuadro Sangra es equivalente al ancho de un carcter. Orientacin.- permite cambiar la orientacin del texto en las celdas seleccionadas. Es posible que las opciones de giro no estn disponibles si se seleccionan otras opciones de alineacin. Grados.- establece la cantidad de giro del texto en la celda seleccionada. Utilice un nmero positivo para girar el texto seleccionado desde la esquina inferior izquierda a la esquina superior derecha de la celda. Utilice grados negativos para girar el texto desde la esquina superior izquierda a la esquina inferior derecha en la celda seleccionada. Control del texto Ajustar texto.- ajusta el texto en varias lneas en una celda. El nmero de lneas ajustadas depender del ancho de la columna y de la longitud del contenido de la celda. Reducir hasta ajustar.- reduce el tamao aparente de los caracteres de fuente de forma que se ajusten todos los datos de una celda seleccionada a la columna. El tamao de 50. Excel 2007 Intermedio 50 carcter se ajusta automticamente al cambiar el ancho de la columna. No cambia el tamao de la fuente aplicada. Combinar celdas.- combina dos o ms celdas seleccionadas en una sola celda. La referencia de celda para una celda combinada es la celda de la esquina superior izquierda del rango original seleccionado. De derecha a izquierda Direccin del texto.- seleccione una opcin en el cuadro Texto para especificar el orden de lectura y la alineacin. La configuracin predeterminada es Contexto pero puede cambiarla a De izquierda a derecha o De derecha a izquierda. Fuente Excel nos permite cambiar la apariencia de los datos de una hoja de clculo cambiando la fuente, el tamao, estilo y color de los datos de una celda. En la ficha Inicio, en el grupo Fuente, disponemos de una serie de opciones que nos permitir modificar la apariencia de los datos de una hoja en una forma ms rpida. Otra opcin es utilizar el cuadro de dilogo Formato de celdas Fuente.- Seleccione el tipo de fuente para el texto de las celdas seleccionadas. Estilo de fuente.- Seleccione el estilo de fuente para el texto en las celdas seleccionadas. Tamao.- Seleccione el tamao de fuente para el texto de las celdas seleccionadas. Puede escribir cualquier nmero entre 1 y 1638. Subrayado.- Seleccione el tipo de subrayado que desea utilizar para el texto de las celdas seleccionadas. Color.- Seleccione el color que desea utilizar para texto o celdas seleccionadas. Fuente normal.- Active la casilla de verificacin Fuente normal para restablecer la fuente, el estilo de fuente, el tamao y los efectos al estilo Normal (predeterminado). 51. Excel 2007 Intermedio 51 Efectos.- Le permite seleccionar uno de los siguientes efectos de formato. Tachado.- Active esta casilla de verificacin para que aparezca el texto en las celdas seleccionadas como tachado. Superndice.- Active esta casilla de verificacin para que aparezca el texto en las celdas seleccionadas como superndice. Subndice.- Active esta casilla de verificacin para que aparezca el texto en las celdas seleccionadas como subndice. Vista previa.- Vea un ejemplo de texto que aparece con las opciones de formato que selecciona. Borde Excel nos permite crear lneas en los bordes o lados de las celdas. Lnea.- Seleccione una opcin en Estilo para especificar el estilo y el tamao de la lnea para un borde. Si desea cambiar un estilo de lnea en un borde que ya existe, seleccione la opcin de estilo de lnea que desea y, a continuacin, haga clic en el rea del borde en el modelo Borde donde desea que aparezca el nuevo estilo de lnea. Preestablecidas.- Seleccione una opcin de borde predefinida para aplicar bordes o quitarlos de las celdas seleccionadas. Color.- Seleccione un color en la lista para cambiar el color de las celdas seleccionadas. Borde.- Haga clic en un estilo de lnea en el cuadro Estilo y despus haga clic en los botones bajo Preestablecidas o Borde para aplicar bordes a las celdas seleccionadas. Para quitar todos los bordes, haga clic en el botn Ninguno. Tambin puede hacer clic en las reas del cuadro de texto para agregar o quitar bordes. Relleno Excel nos permite tambin sombrear las celdas de una hoja de clculo para remarcarlas de las dems. 52. Excel 2007 Intermedio 52 Color de fondo.- Seleccione un color de fondo para las celdas seleccionadas utilizando la paleta de colores. Efectos de relleno.- Seleccione este botn para aplicar degradado, textura y rellenos de imagen en las celdas seleccionadas. Ms colores.- Seleccione este botn para agregar colores que no estn disponibles en la paleta de colores. Color de Trama.- Seleccione un color de primer plano para crear una trama que utilice dos colores. Estilo de Trama.- Seleccione una trama para aplicar formato a las celdas seleccionadas con una trama que utiliza los colores seleccionados en los cuadros Color de fondo y Color de Trama. Muestra.- Vea una muestra de las opciones de trama, color y efectos de relleno que selecciona. Proteccin Bloqueado.- Evita que las celdas seleccionadas se cambien, muevan, cambien de tamao o se eliminen. El bloqueo de celdas no tiene ningn efecto a menos que la hoja est protegida. Oculta.- Oculta una frmula de una celda para que no aparezca en la barra de frmulas cuando la celda est seleccionada. Si selecciona esta opcin, no tiene efecto a menos que la hoja est protegida. 53. Excel 2007 Intermedio 53 Cmo proteger una hoja? En la ficha Revisar, en el grupo Cambios, haga clic en Proteger hoja. Asegrese de que la casilla de verificacin Proteger hoja y contenido de celdas bloqueadas est activada. En el cuadro Permitir a los usuarios de esta hoja de clculo, seleccione las actividades que permite que los usuarios ejecuten en una hoja protegida que contiene celdas bloqueadas. Si queremos asignar una contrasea para que solamente pueda desproteger la hoja la persona que sepa la contrasea, escribirla en el recuadro Contrasea. El formato condicional Agregar, cambiar o borrar formatos condicionales El formato condicional sirve para que dependiendo del valor de la celda, Excel aplique un formato especial o no sobre esa celda. El formato condicional suele utilizarse para explorar y analizar datos visualmente, resaltar errores y para valores que cumplan una determinada condicin, para resaltar las celdas segn el valor contenido en ella. Cuando se analizan datos, es frecuente que surjan preguntas como: Dnde estn las excepciones en un resumen de beneficios de los ltimos cinco aos? Cules son las tendencias en una encuesta de opinin de marketing durante los dos ltimos aos? Quin ha vendido ms de 50.000 dlares este mes? Cul es la distribucin de antigedad general de los empleados? De qu productos han aumentado los ingresos ms del 10% de ao en ao? Cules son los estudiantes con mejores resultados y cules los de peores resultados en la clase de 4 de secundaria? El formato condicional ayuda a responder estas preguntas porque facilita el proceso de resaltar celdas o rangos de celdas interesantes, de destacar valores inusuales y de ver datos empleando barras de datos, escalas de colores y conjuntos de iconos. Un formato condicional cambia el aspecto de un rango de celdas en funcin de una condicin (o criterio). Si la condicin es verdadera, el rango de celdas basa el formato en dicha condicin; si la condicin es falsa, el rango de celdas no tiene formato basado en dicha condicin. 54. Excel 2007 Intermedio 54 Aplicar formato slo a las celdas que contienen valores de texto, nmero, o fecha u hora Para encontrar ms fcilmente celdas especficas dentro de un rango de celdas, puede aplicar formato a dichas celdas especficas basndose en un operador de comparacin. Por ejemplo, en una hoja de clculo de inventario clasificada por categoras, puede resaltar los productos con menos de 10 artculos disponibles en amarillo. 1. Seleccione un rango de celdas o asegrese de que la celda activa est en una tabla o en un informe de tabla dinmica. 2. En la ficha Inicio, en el grupo Estilo, haga clic en la flecha junto a Formato condicional y, a continuacin, haga clic en Resaltar reglas de celdas. 3. Seleccione el comando que desea, como Entre, Igual a texto que contiene o Una fecha. 4. Escriba los valores que desee utilizar y, a continuacin, seleccione un formato. Aplicar formato a todas las celdas utilizando barras de datos Una barra de datos le ayuda a ver el valor de una celda con relacin a las dems. La longitud de la barra de datos representa el valor de la celda. Una barra ms grande representa un valor ms alto y una barra ms corta representa un valor ms bajo. Las barras de datos son tiles para encontrar nmeros ms altos y ms bajos especialmente con grandes cantidades de datos, como las mayores y menores ventas de juguetes en un informe de ventas. 1. Seleccione un rango de celdas. 2. En la ficha Inicio, en el grupo Estilo, haga clic en la flecha junto a Formato condicional, haga clic en Barras de datos y, a continuacin, seleccione un icono de la barra de datos. 55. Excel 2007 Intermedio 55 Aplicar formato a todas las celdas empleando una escala de dos tres colores Las escalas de colores son guas visuales que ayudan a comprender la variacin y la distribucin de datos. Una escala de dos o tres colores permite comparar un rango de celdas utilizando una gradacin de dos o tres colores. El tono de color representa los valores superiores o inferiores. 1. Seleccione un rango de celdas o asegrese de que la celda activa est en una tabla o en un informe de tabla dinmica. 2. En la ficha Inicio, en el grupo Estilos, haga clic en la flecha situada junto a Formato condicional y, despus, en Escalas de color. 3. Seleccione una escala de dos colores. Aplicar formato a todas las celdas empleando un conjunto de iconos Utilice un conjunto de iconos para comentar y clasificar datos de tres a cinco categoras separadas por un valor de umbral. Cada icono representa un rango de valores. Por ejemplo, en el conjunto de iconos de 3 flechas, la flecha roja hacia arriba representa valores ms altos, la flecha hacia el lado amarilla representa valores medios y la flecha hacia abajo verde representa valores ms bajos. 1. Seleccione un rango de celdas o asegrese de que la celda activa est en una tabla o en un informe de tabla dinmica. 2. En la ficha Inicio, en el grupo Estilo, haga clic en la flecha junto a Formato condicional, haga clic en Conjunto de iconos y, a continuacin, seleccione un conjunto de iconos. Aplicar formato a los valores de rango inferior o superior y por encima o por debajo del promedio Puede buscar los valores ms altos y ms bajos en un rango de celdas segn un valor de corte que especifique. Puede buscar valores por encima o por debajo del promedio o desviacin estndar en un rango de celdas. 56. Excel 2007 Intermedio 56 Por ejemplo, puede buscar los 5 productos ms vendidos en un informe regional, el 15% de los productos del final de una encuesta al cliente o los 25 mejores salarios de un anlisis de personal de departamento. 1. Seleccione un rango de celdas. 2. En la ficha Inicio, en el grupo Estilo, haga clic en la flecha junto a Formato condicional y, a continuacin, haga clic en Reglas superiores e inferiores. 3. Seleccione el comando que desee, como 10 elementos superiores o 10% inferiores. 4. Escriba los valores que desee utilizar y, a continuacin, seleccione un formato. Utilizar una frmula que determine las celdas para aplicar formato Si el formato condicional tiene que ser ms complejo, puede utilizar una frmula lgica para especificar los criterios de formato. Por ejemplo, puede que desee comparar valores con un resultado devuelto por una funcin o evaluar datos de celdas que se encuentran fuera del rango seleccionado. 1. Seleccione un rango de celdas. 2. En la ficha Inicio, en el grupo Estilos, haga clic en la flecha situada junto a Formato condicional y, despus, en Administrar reglas. Se mostrar el cuadro de dilogo Administrador de reglas de formato condicionales. 57. Excel 2007 Intermedio 57 3. Para agregar un formato condicional, haga clic en Nueva regla. Aparecer el cuadro de dilogo Nueva regla de formato. 4. En Seleccionar un tipo de regla, haga clic en Utilice una frmula que determine las celdas para aplicar formato. 5. En Editar una descripcin de regla, en el cuadro de lista Dar formato a los valores donde esta frmula sea verdadera, escriba las frmula. 6. Haga clic en Formato para mostrar el cuadro de dilogo Formato de celdas. 7. Seleccione el nmero, fuente, borde o formato de relleno que desea aplicar cuando el valor de la celda cumple la condicin y, a continuacin, haga clic en Aceptar. Puede elegir ms de un formato. Los formatos que selecciona aparecen en el cuadro Vista previa. 58. Excel 2007 Intermedio 58 Borrar formatos condicionales Siga uno de los procedimientos siguientes: 1 Hoja de clculo En la ficha Inicio, en el grupo Estilos, haga clic en la flecha situada junto a Formato condicional y, despus, en Borrar reglas. Haga clic en Borrar reglas de toda la hoja. 2 Rango de celdas, tabla o tabla dinmica Seleccione el rango de celdas. En la ficha Inicio, en el grupo Estilos, haga clic en la flecha situada junto a Formato condicional y, despus, en Borrar reglas. Segn lo que haya seleccionado, haga clic en Celdas seleccionadas, Esta tabla o Esta tabla dinmica. La validacin de datos La validacin de datos es muy similar al formato condicional, salvo que esta caracterstica tiene una funcin muy concreta y es validar el contenido de una celda; pudiendo incluso mostrar un mensaje de error o aviso si llegara el caso. En muchas hojas de clculo que cree, los usuarios escribirn datos para obtener los resultados y clculos que desee. Asegurar la entrada vlida de datos es una tarea importante. Puede que desee restringir la entrada de datos a un rango determinado de fechas, limitar las opciones empleando una lista o asegurarse de que slo se entran nmeros enteros positivos. Tambin es esencial proporcionar una ayuda inmediata para dar instrucciones a los usuarios y mensajes claros cuando se escriban datos no vlidos para hacer que el proceso de entrada de datos se lleve a cabo sin problemas. Para aplicar una validacin: 1. Seleccione una o ms celdas para validar. 2. En la ficha Datos, en el grupo Herramientas de datos, haga clic en Validacin de datos. 59. Excel 2007 Intermedio 59 3. Se muestra el cuadro de dilogo Validacin de datos. 4. Haga clic en la ficha Configuracin. 5. En la seccin Criterio de validacin indicamos la condicin para que los datos sean correctos. Dentro de Permitir podemos encontrar Cualquier valor, Nmero entero, Decimal, Lista, Fecha, Hora, Longitud del texto y Personalizada. Por ejemplo si elegimos Nmero entero, Excel slo permitir nmeros enteros en esa celda, si el usuario intenta escribir un nmero decimal, se producir un error. Podemos restringir ms los valores permitidos en la celda con la opcin Datos:, donde, por ejemplo, podemos indicar que los valores estn entre 2 y 8. Si en la opcin Permitir: elegimos Lista, podremos escribir una lista de valores para que el usuario pueda escoger un valor de los disponibles en la lista. En el recuadro que aparecer, Origen: podremos escribir los distintos valores separados por ; (punto y coma) para que aparezcan en forma de lista. 6. Otra opcin es mostrar un Mensaje de entrada cuando se haga clic en la celda. Este mensaje sirve para informar de qu tipos de datos son considerados vlidos para esa celda. 60. Excel 2007 Intermedio 60 7. En la pestaa Mensaje de error podemos escribir el mensaje de error que queremos se le muestre al usuario cuando introduzca en la celda datos no vlidos. Seleccione una de las siguientes opciones en el cuadro Estilo: Informacin.- Para mostrar un mensaje informativo que no evite la especificacin de datos no vlidos. Advertencia.- Para mostrar un mensaje de advertencia que no evite la especificacin de datos no vlidos. 61. Excel 2007 Intermedio 61 Grave.- Para evitar la especificacin de datos no vlidos. Crear una lista de entradas validas Para facilitar la entrada de datos, o para limitar las entradas a determinad