Descarga la aplicación para disfrutar aún más
Vista previa del material en texto
1 Piensa… ¿Conoces qué son los Nombres de Rangos? ¿Sabías que se puede hacer un Esquema de una Tabla en forma simple para mostrar su información agrupada? ¿Recuerdas el Formato Condicional? ¿Esperabas que tuviera más opciones de aplicación? Asignatura: INFORMÁTICA Profesor: Cadoni, Jorge Pieza Nro. 11 del “Puzzle”. Excel. Clase 2 11.2 Rangos: Denominación de rangos Excel. Empleo de nombres de rango. Formularios de datos: Agregar, Modificar y Eliminar un registro a una lista utilizando un formulario de datos. Esquemas en una hoja de cálculo: Funcionamiento de los esquemas. Mostrar u ocultar datos detallados en un esquema. Formas de trazar el esquema de los datos. Trazar manualmente el esquema de una hoja de cálculo. Trazar automáticamente el esquema de una hoja de cálculo utilizando Subtotales: Introducción a los subtotales de las listas. Insertar subtotales en una lista. Funciones de resumen para listas con subtotales. Crear informes de resumen. Representar los datos del resumen en un gráfico. Quitar subtotales de una lista. Rangos: Nombres. Las personas estamos más acostumbradas a nombrar las cosas por su nombre que por números de referencia: lo vemos todo mucho más claro e intuitivo. Podemos ver una fórmula relativamente sencilla pero, si contiene muchas referencias, nos puede costar interpretarla. Por eso Excel nos facilita un poco las cosas permitiendo que podamos ponerles nombre a las celdas y a las fórmulas para así identificarlas más fácilmente. ¿Cuáles son las ventajas de la asignación de nombres a celdas y cómo implementarla? Excel tiene una forma muy práctica de denominar a las celdas de una hoja: combinando el nombre de la columna y la fila donde está ubicada. De esta manera, a la celda que está en la columna B y la fila 3 se la identifica como B3, a la celda que está en la columna AC y la fila 159 se la identifica como AC159 y así sucesivamente. El nombre de la celda activa aparece en el Cuadro de nombres, en la zona superior izquierda de la hoja. 2 Se puede asignar un nombre personalizado a una celda de manera muy sencilla: Basta con posicionarte en la celda que quieres nombrar, hacer clic dentro del Cuadro de nombres, digitar el nombre y presionar Enter. Esto tiene varias ventajas. Una vez que hemos asignado un nombre a una celda, Excel lo reconoce como otra referencia a esa celda. De modo que podemos usar esos nombres como sustitutos de las referencias estándar. Las referencias con nombres personalizados son más fáciles de recordar y las fórmulas en las que se usan son más fáciles de entender. Casi todos concordaremos en que es mucho más conveniente usar fórmulas del estilo =B5/dólar que del estilo =B5/'Hoja3'!$AB$7. Por ejemplo, si en la celda C3 tenemos la fecha del día, en C5 tenemos la cotización del dólar y en C6 tenemos la cotización del euro y luego otras cotizaciones de moneda, resulta más práctico asignar a dichas celdas los nombres respectivos de "fecha", "dólar", "euro", etc., y usar estas denominaciones en lugar de C3, C5, C6, etc. Tenemos una libertad bastante amplia a la hora de asignar nombres a celdas; podemos usar prácticamente cualquier denominación, evitando unas pocas excepciones que se alistan más adelante en el artículo. Otra gran ventaja Siguiendo con el ejemplo anterior, imaginemos que necesitamos obtener el equivalente en dólares de varios importes en moneda nacional. Supongamos que los importes en moneda nacional están en la columna E y empiezan en la fila 5. Los valores en dólares los vamos a calcular en la columna F. Si usáramos las referencias de celda que trae Excel por defecto, colocaríamos la siguiente fórmula en F5: =E5/C5 Como tal vez sepas, antes de arrastrar esta fórmula a lo largo de las demás filas donde deba aparecer, deberíamos tener la precaución de fijar C5 como una referencia absoluta, de la siguiente manera: =E5/$C$5 Sin embargo, si utilizamos el nombre personalizado para la celda que contiene la cotización del dólar (es decir: "dólar", en este caso), Excel lo considerará directamente como una referencia absoluta, porque con ese nombre identifica 3 exclusivamente a esa celda. De manera que ya no deberíamos preocuparnos por colocar los famosos $ para fijar la referencia y alcanzaría con escribir la siguiente fórmula en F5: =E5/dólar Una gran ventaja de los nombres personalizados de celda es que equivalen a referencias absolutas. Dar nombres a rangos También podemos darles nombres personalizados a rangos de celdas y no solo a celdas individuales. Imaginemos que necesitamos sumar los valores contenidos en el rango C4:C166. La forma tradicional de hacerlo sería: =SUMA(C4:C166) Pero también podemos seleccionar el rango en cuestión, colocarle un nombre en el Cuadro de nombres (por ejemplo: "total") y usar ese nombre en la función SUMA: =SUMA(total) En este artículo se usan ejemplos simples de entender, para no agregar una complejidad innecesaria a la explicación. No obstante, las mayores ventajas las obtendremos al usar los nombres de rangos personalizados en funciones de búsqueda y referencia como BUSCARV, INDICE y COINCIDIR. 4 Limitaciones a la hora de asignar nombres Hay algunas expresiones que no pueden usarse como nombres de celda o rango. Aquí va un resumen de las restricciones más importantes (en Excel 2007 y posteriores): No se permiten nombres equivalentes a las referencias de Excel: como A1, B3, XZ48, C2:H15, etc. (Si se ingresan en el Cuadro de nombres, Excel seleccionará las celdas o rangos en cuestión.) Tampoco es lícito usar nombres que contengan espacios, guiones y otros caracteres especiales: como Venta-Total, Gastos del Mes, LIBROS PRESTADOS, etc. Los únicos caracteres válidos, ademas de las letras [A...Z, a...z] y los números [0...9], son: el guión bajo [_], el punto [.], la contrabarra [\] y los signos de interrogación [¿, ?]. De todos estos caracteres no alfabéticos solo tres, el guión bajo [_], la contrabarra [\] y el signo de interrogación inicial [¿], pueden usarse al comienzo del nombre. (Nombres válidos: Venta_Total, Gastos.del.Mes, LibrosPrestados) Serán rechazados también los nombres que comiencen por un número: como 10ENERO, 2014, 2ºPeriodo, etc. (Nombres válidos: ENERO10, _2014, SegundoPeriodo) No serán admitidos tampoco los nombres que excedan los 255 caracteres de longitud. (Si se ingresan nombres de más de 255 caracteres, Excel corta el nombre, eliminando los caracteres excedentes.) El Administrador de nombres Ya hemos visto que dar nombre a una celda o rango es muy sencillo desde el Cuadro de nombres. Pero ¿cómo podemos modificar o eliminar un nombre ya creado? Para eso debemos acudir al Administrador de nombres, que encontramos en la ficha Formulas (en el grupo Nombres definidos). También podemos acceder a él con el atajo de teclado CTRL+F3 (esto es: presionar la tecla CTRL y, sin soltarla, presionar F3). El Administrador de nombres nos da la posibilidad de modificar tanto el nombre como la referencia asociadas. Basta con hacer clic en el botón Modificar, hacer los cambios deseados en la nueva ventanita que se abre y presionar el botón Aceptar. 5 Desde el Administrador de nombres también podemos borrar completamente cualquier nombre de la lista. Solo hay que cliquear el botón Eliminar y, luego, aceptar la confirmación que requiere Excel. Ámbito del nombre Ya terminando, me parece importante considerar el tema del ámbito en que será reconocido el nombre. De forma predeterminada, cuando creamos una designación para una celda o rango desde el Cuadro de nombres, Excel le asigna un ámbito global, es decir: podrás usar el nombre en todas las hojas del libro que desees. Pero, si por alguna razón, necesitas que el nombre de una celda o rango solo esté disponible en una hoja concreta y en ninguna otra,deberás precisar que su ámbito se limite a dicha hoja. Esto debes hacerlo al momento de crear el nombre y a través del Administrador, presionando el botón Nuevo y especificando todos los datos requeridos. El ámbito de un nombre no se puede modificar una vez creado. En caso de que necesites cambiarlo, no tendrás otra opción que eliminar el nombre y volverlo a crear con el ámbito correcto. 6 Ayuda para recordar los nombres Si no recuerdas los nombres de celda o rangos disponibles al momento de escribir una fórmula, puedes presionar la tecla F3 para que aparezca una ventana con todos los nombres disponibles. Puedes seleccionar cualquiera de ellos haciendo doble clic sobre él. Y ahora a practicar el empleo de Nombres de Rangos: Confeccionaremos a continuación una serie de fórmulas, de forma que puedas experimentar lo cómodo que resulta utilizar nombres de rango en lugar de referencias directas a celdas. Sigue estos pasos: • Abre un Libro Excel y en la Hoja1 crea el Nombre de Rango Codigo_artic referenciando las celdas H2:H15. Escribe el Título Código de Artículo en H2 y desde H3 hasta H15 ingresa códigos numéricos de artículos. En I3:I15 debes completar con Nombres de Artículos y en J3:J15 ingresa los precios de los artículos, titulando Precio en J2 y llamando Precio al rango J2:J15. • Selecciona la celda D4, para calcular en ella el código más pequeño de los artículos existentes actualmente. Utilizaremos para ello la función MIN y el nombre de rango Codigo_artic creado anteriormente. • Introduce la expresión =MIN(Codigo_artic) en la celda que acabas de seleccionar y pulsa <Intro>. Para calcular el código máximo haremos algo muy parecido. • Selecciona la celda D5 y escribe en ella =MAX( • Desde el menú Insertar, selecciona la opción Nombre y a continuación Pegar..., te aparecerá el cuadro de la derecha desde el que podrás seleccionar el nombre de rango a utilizar. Selección del nombre de rango a utilizar • Selecciona el nombre Codigo_artic y haz clic en el botón Aceptar. El nombre de rango ha sido introducido en la celda. • Para acabar de introducir la fórmula, escribe ) y pulsa <Intro>. Observa cómo en la celda D5 ha aparecido calculado el número que corresponde al código más grande de los artículos existentes. Calcularemos ahora algunos datos de interés con respecto a los precios de nuestros productos. • En la celda C8 calcularemos el precio medio de los artículos. Para ello introduce la expresión =PROMEDIO(Precio) en dicha celda. 7 • En la celda C9 calcularemos el precio más bajo, con la expresión =MIN(J2:J15) • Para consultar el nombre del artículo que tiene este precio mínimo, introduce en la celda D9 la expresión =BUSCARV(C9;H2:J15;2) • Introduce en C10 la expresión =MAX(Precio). Con ella calcularemos el precio más alto. • Para consultar el nombre del artículo con el precio más alto, copia la fórmula que introdujiste en D9 a la celda D10. Por último, introduciremos una serie de fórmulas que permitan calcular los datos de un determinado artículo a partir de su código. • Introduce un código de artículo en la E12, entre los valores que elegiste ingresar como Códigos de Artículos. Designa a E12 con el Nombre de Rango "Consultado" y a toda el área de datos como " Artículos”. • Para calcular la descripción del artículo, introduce en C13 la expresión =BUSCAR(Consultado;Artículos;2). • Para calcular el precio unitario del artículo, introduce en C15 la expresión =BUSCARV(Consultado;Artículos;3). • Cambia varias veces el código del artículo, para comprobar que siempre localizas sus datos correspondientes. • Guarda y cierra el libro de trabajo. Como habrás podido comprobar, las fórmulas que acabamos de introducir y las que usamos en el ejemplo anterior son básicamente las mismas. Sin embargo, al utilizar nombres de rango en lugar de referencias directas a celdas, la claridad de dichas fórmulas, ha aumentado en gran medida. Es la ventaja de utilizar nombres de rango. Formularios de datos: Agregar, Modificar y Eliminar un registro a una lista utilizando un formulario de datos. Este tema se vio, por razones de practicidad, en la Unidad anterior, relacionándolo con el concepto y empleo de Tablas. Esquemas en una hoja de cálculo Te invito a ver el video denominado “Esquemas” que nos mostrará cómo “empezar” a usar esta herramienta en Excel 2016. ¿Vamos a verlo? Después de visualizar el video, comencemos a aprender paso a paso y a reafirmar conceptos de lo que has visualizado recientemente. https://www.youtube.com/watch?v=j0JYCPujMbE https://www.youtube.com/watch?v=j0JYCPujMbE 8 Podríamos definir un esquema como un resumen preciso que refleja los conceptos más importantes o de mayor trascendencia del documento esquematizado. Así pues, un esquema puede ser perfectamente el índice de un libro, donde vemos todos los puntos tratados en el libro. También podemos ver como ejemplo de esquema el índice de este curso, que contiene los puntos más importantes que se tratan en él y, además, está estructurado por niveles de profundización sobre un tema en concreto, es decir, vamos desplegando el esquema de los puntos contenidos en el tema. Antes de ponernos a crear un esquema debemos tener en cuenta algunos aspectos. - Debemos asegurarnos de que los datos sean apropiados para crear un esquema. Los datos apropiados para crear un esquema deben tener una jerarquía o disponer de una estructura por niveles. Por ejemplo, si tenemos datos sobre las precipitaciones ocurridas a lo largo del año en toda España con las precipitaciones mensuales de todas las provincias, estos datos son buenos candidatos a formar un esquema. Pero si únicamente tenemos datos sobre los gastos efectuados en una compra, con una estructura compuesta por Producto---Precio, no disponemos de niveles suficientes para hacer un esquema. - En una hoja sólo podemos incluir un esquema. Para tener más de un esquema sobre los mismos datos, debemos copiar los datos a otra hoja. - Para crear esquemas automáticamente, debemos preparar la hoja con un formato adecuado, como veremos más adelante. Existen dos formas de crear un esquema en Excel 2016: Manual y Automática. Creación automática de esquemas La mejor opción para crear esquemas es que lo haga Excel automáticamente, puesto que tarda mucho menos tiempo que si lo hacemos nosotros a mano. Existen unos requisitos previos para que Excel 2016 pueda crear el esquema de forma automática: - Las filas sumario deben estar por encima o por debajo de los datos, nunca entremezcladas. - Las columnas sumario deben estar a la derecha o a la izquierda de los datos, nunca entremezcladas. Si la disposición de los datos no se corresponde con estas características, nos veremos obligados a definir el esquema manualmente. En la imagen podemos ver un ejemplo de datos bien estructurados: 9 Podemos ver que existen subtotales en las celdas B6, C6, D6, B11, C11, D11, B20, C20, D20 y la columna E está llena de subtotales correspondientes a las filas donde se encuentran. En las celdas B21, C21, D21 y E21 aparecen los totales de los subtotales. En este ejemplo, podemos hacer un esquema tanto de filas como de columnas, puesto que se han organizado los subtotales de cada comunidad autónoma (filas) y se han calculado los subtotales de cada trimestre (columnas). Por tanto, como ya tenemos los datos, vamos a realizar el esquema. Para ello nos situamos en la pestaña Datos. En la sección Esquema encontraremos el botón Agrupar. Al pulsarlo, elegiremos la opción Autoesquema del menú. Automáticamente, Excelnos genera los niveles del esquema, como podemos ver a continuación: En la imagen anterior, podemos ver que ahora aparecen unas líneas en la zona situada a la izquierda de las filas y otra línea sobre las columnas de la tabla. Además, vemos en la esquina superior izquierda unos números que nos indican cuántos niveles tiene el esquema. Por columnas, podemos ver que existen dos niveles: Por filas, tenemos tres niveles: - La tabla desplegada por completo - y la tabla con los datos trimestrales. - La tabla desplegada completamente, - por autonomías - y sólo con España. Para comprimir y expandir el esquema sólo tenemos que hacer clic en los símbolos - y + de cada nivel. Por ejemplo, en la tabla del ejemplo, si hacemos clic sobre el - encima del primer trimestre, comprimiremos ese trimestre. Si hacemos lo mismo sobre los niveles de cada autonomía, el esquema se nos quedaría como vemos en la imagen. 10 NOTA: Si los totales de una lista se escribieron como números, no como fórmulas calculadas, no podrá trazarse el esquema automáticamente. SUBTOTALES Introducción a los subtotales de las listas Cuando hablamos de crear un resumen de los datos de una tabla, nos estamos refiriendo a crear subtotales agrupando los registros por alguno de los campos de la lista. Por ejemplo, si tenemos una lista de niños con los campos nombre, dirección, localidad y edad, podemos obtener un resumen de la edad media de los niños por localidad. En otro ejemplo, el que te enseñamos a la derecha, disponemos de una lista de vehículos clasificados por marca y modelo; y queremos averiguar el coste total de cada marca. Para agregar los subtotales automáticamente, debemos situarnos sobre una celda cualquiera de la lista y marcar la opción Fila de totales en las Opciones de estilo de tabla, en la pestaña Diseño. Al seleccionar una celda de la fila de totales, aparece una pestaña con una lista de las funciones que podemos usar para calcular el total de esa columna. Microsoft Excel puede resumir datos automáticamente calculando valores de subtotales y de totales en una lista. Para usar los subtotales automáticos, la lista debe contener columnas rotuladas y debe estar ordenada por las columnas para las que desee calcular los subtotales. Cuando se insertan subtotales automáticos, Excel esquematiza la lista agrupando las filas con detalles con la fila de subtotal asociada y agrupando las filas de subtotales con la fila del total general. 11 Puedes elegir la función de Excel que desees para calcular los totales. En este ejemplo se utiliza la función Suma para calcular los subtotales de Buchanan y Davolio y el total general de toda la lista. Insertar subtotales en una lista 1. Ordena la lista por la columna cuyos subtotales desees calcular. Por ejemplo, para resumir las unidades vendidas por cada vendedor de una lista de vendedores, el importe de las ventas y el número de unidades vendidas, ordena la lista por la columna Vendedor. 2. Haz clic en una celda de la lista. 3. En el menú Datos, haz clic en Subtotales. 4. En el cuadro Para cada cambio en, haz clic en la columna que contenga los grupos cuyos subtotales desees calcular. Deberá ser la misma columna por la que se haya ordenado la lista en el paso 1. 5. En el cuadro Usar función, selecciona la función que desees emplear para calcular los subtotales. 6. En el cuadro Agregar subtotal a, activa las casillas de verificación correspondientes a las columnas que contengan los valores cuyos subtotales desees extraer. Funciones de resumen para listas con subtotales Para resumir datos de una lista, puede utilizarse cualquiera de las siguientes funciones. Haz clic en la función que desees en el cuadro Usar función del cuadro de diálogo Subtotales (menú Datos, comando Subtotales). Usa esta función Para obtener Usa esta función Para obtener Suma La suma de los valores de una lista. Esta es la función predeterminada para datos numéricos. Contar números El número de registros o filas de una lista que contiene datos numéricos. Contar El número de elementos de una lista. Esta es la función predeterminada para datos no numéricos. Desvest Una estimación de la desviación estándar de una población, donde la lista es la muestra. Promedio El promedio de los valores de la lista. Desvestp La desviación estándar de una población, donde la lista es toda la población. Máx El valor más grande de una lista. Var Una estimación de la varianza de una población, donde la lista es la muestra. Mín El valor más pequeño de una lista. Varp La varianza de una población, donde la lista es toda la población. Producto El resultado de multiplicar todos los valores de una lista. Crear informes de resumen Al agregar subtotales automáticos a una lista, esta aparecerá esquematizada para que pueda ver su estructura. Puedes crear un informe de resumen haciendo clic en los símbolos del esquema para ocultar los detalles y mostrar sólo los subtotales. Resumen. 12 Representar los datos del resumen en un gráfico Puede crear un gráfico que utilice sólo los datos visibles de una lista que contenga subtotales automáticos. Si muestra u oculta los detalles de la lista, el gráfico asociado también se actualizará para mostrar u ocultar los datos. Gráfico a partir de un Resumen. Quitar subtotales de una lista Al quitar subtotales de una lista, Microsoft Excel también eliminará el esquema y todos los saltos de página que se hayan insertado en la lista al insertar los subtotales. 1. Haz clic en una celda de la lista que contenga los subtotales. 2. En el menú Datos, haz clic en Subtotales. 3. Haz clic en Quitar todos. 13 11.3 Formato condicional avanzado: Resaltar celdas que cumplan condiciones específicas. Aplicación de formatos condicionales: utilización y ventajas. Fórmulas como criterios de formato. Comprobar un formato condicional antes de aplicarlo. Buscar celdas que tengan formatos condicionales. Mientras tomas un café te invito a ver el video denominado “Formato Condicional” que nos mostrará algunos pormenores del empleo de esta herramienta….. pormenores que te resultarán “conocidos”….. ¿Vamos a verlo? El formato condicional nos va a permitir definir un formato determinado para un conjunto de celdas que cumplen con una condición específica, por ejemplo, que una cifra sea menor que otra, etc. Vamos a ver cómo, de una manera sencilla y dinámica, podemos establecer los diferentes tipos de formato condicional en Excel 2016. En el último video recomendado (llamado Formato Condicional) te habrás dado cuenta que visualizaste un video que ya habías visto en este curso. Era solamente para “refrescar” conocimientos. HAREMOS ALGO AHORA. Visualizarás los 4 videos siguientes antes de continuar. Tratan sobres formas especiales de utilizar el Formato Condicional. Luego, a medida que en el texto se traten estas formas, si lo deseas, puedes volver a ver el video correspondiente. Formato Condicional más allá de lo básico Parte 1. Formato Condicional más allá de lo básico Parte 2. Formato Condicional más allá de lo básico Parte 3. Formato Condicional más allá de lo básico Parte 4. 1. Acceder a la ficha Formato condicional En Excel 2016 la opción de Formato condicional la encontramos en la pestaña Inicio campo Estilos Al desplegar esta ficha tenemos las siguientes opciones disponibles.https://www.youtube.com/watch?v=L1W7T8nQ9Hw https://www.youtube.com/watch?v=L1W7T8nQ9Hw http://www.solvetic.com/tutoriales/article/1971-como-usar-excel-2016-guia-rapida-y-novedades/ https://www.youtube.com/watch?v=0ZYC5lbAq1M https://www.youtube.com/watch?v=qdXWUesIK7M https://www.youtube.com/watch?v=aDfrFEAp_Gs https://www.youtube.com/watch?v=1IC4nRleNj0 14 Resaltar reglas de celdas Esta opción nos permite comparar entre diferentes valores y determinar aspectos como: Es mayor o menor que, está entre x o y valor, texto contiene alguna palabra, etc. Reglas superiores o inferiores Esta regla nos permite definir valores inferiores o superiores al determinado con aspectos como promedio, los 10 valores inferiores, los 10 valores superiores al indicado, etc. Barras de datos Esta regla nos permite definir el aspecto de la barra ya sea en colores degradados o sólidos Escalas de color Esta regla nos permite establecer un color determinado a cada celda Conjuntos de iconos Usando esta regla podemos representar los datos en forma de iconos para llamar la atención de lo que estamos presentando. Además tenemos las siguientes opciones: Nueva regla Nos permite crear una regla personalizada Borrar reglas Esta opción nos permite borrar las reglas definidas para un conjunto de celdas o toda la hoja en general Administrar reglas Esta opción nos permite definir parámetros en las reglas establecidas. 2. Resaltar reglas de celdas Para este ejemplo hemos seleccionado el rango de celdas D2 a D7. Al seleccionar la opción Resaltar reglas de celdas tenemos las siguientes opciones: Allí podemos definir qué parámetro usar para resaltar esa celda sobre las demás. En este ejemplo usaremos la opción Entre y definiremos que las celdas resaltadas estén entre 10 y 25. Al pulsar esta opción veremos lo siguiente: 15 Allí definiremos lo siguiente: El rango de valores que Excel analizará para desplegar el resultado En el campo con debemos definir el aspecto que tendrán las celdas resaltadas, podemos elegir alguna de las opciones disponibles o crear la nuestra usando la opción Formato personalizado. Podemos ver la vista previa del resultado en las celdas que cumplen los parámetros. 3. Reglas superiores o inferiores Continuando con el mismo rango de celdas seleccionadas estas son las opciones con dicha regla: Allí podemos elegir entre que valores desplegar, si los 10 superiores al valor definido, los 10 inferiores, valores encima del promedio, etc. Para este ejemplo hemos elegido la opción Por encima del promedio 16 En este caso Excel suma todos los valores de las celdas, saca su promedio (como vemos en la parte inferior) y todos los valores que estén por encima de este valor, en este caso 15,83, serán resaltados. 4. Barras de datos Continuando con el mismo rango, la barra de datos nos desplegará una representación visual de cada valor en formato de barra. Podemos definir el color deseado para que nuestros datos estén representados como los gráficos en barra. 5. Escalas de color Con esta regla podemos crear un entorno visual que cambia a medida que los datos han sido modificados, esto es de gran ayuda cuando manejamos información variable como el clima, la bolsa, etc. 6. Conjunto de iconos 17 Esta regla es muy interesante cuando debemos representar de forma gráfica el estado de ciertos datos, por ejemplo notas de un estudiante, promedio de ventas, etc., ya que con estos íconos podemos ver en qué nivel estamos de acuerdo al objetivo trazado. En este caso podemos pulsar sobre la opción más reglas para ver cómo podemos establecer los parámetros para dicho fin El resultado obtenido se muestra a la derecha: Vemos como el formato condicional es una gran ayuda para representar de forma gráfica o resumida gran cantidad de información sin mucho esfuerzo y con las grandes bondades del asistente de Excel que será de gran ayuda para todos nosotros en las tareas diaria con hojas de cálculo. 18 Veamos algunos casos que pueden presentarse: Resaltar celdas que cumplan condiciones específicas Si una celda contiene los resultados de una fórmula u otros valores de la celda que desee supervisar, pueden identificarse las celdas aplicando formatos condicionales. Por ejemplo, se puede aplicar un sombreado de color verde (resaltar) a la celda si las ventas sobrepasan a las ventas pronosticadas y un sombreado de color rojo si las ventas son menores que las ventas pronosticadas. Cuando cambian las condiciones: Si el valor de la celda cambia y ya no cumple la condición especificada, Microsoft Excel suprimirá temporalmente los formatos que resalten esa condición. Los formatos condicionales continúan aplicados a las celdas hasta que se quiten, aunque no se cumpla ninguna de las condiciones y no se muestren los formatos de celda especificados. Aplicando formatos condicionales 1. Selecciona las celdas que desee resaltar. 2. En el menú Formato, haga clic en Formato condicional. 3. Sigue uno de estos procedimientos: • Para utilizar los valores de las celdas seleccionadas como el criterio de formato, haz clic en Valor de la celda, selecciona la frase de comparación e introduce un valor en el cuadro correspondiente. Puede introducirse un valor constante o una fórmula. Si se introduce una fórmula, se la debe iniciar con un signo igual (=). • Para utilizar una fórmula como criterio de formato (para evaluar datos o una condición que no sean los valores de las celdas seleccionadas), haz clic en Fórmula en el cuadro de la izquierda y, a continuación, escribe la fórmula en el cuadro de la derecha. La fórmula debe evaluarse contra un valor lógico VERDADERO o FALSO. 4. Haz clic en Formato. 5. Selecciona el estilo de fuente, el color, el subrayado, los bordes o la trama que desees aplicar. Microsoft Excel solamente aplicará los formatos seleccionados si el valor de la celda cumple la condición o si la fórmula devuelve un valor VERDADERO. 6. Para agregar otra condición, haz clic en Agregar y repite los pasos del 3 al 5. 7. Pueden especificarse hasta tres condiciones. Si ninguna de las condiciones que se han especificado es verdadera, las celdas conservarán los formatos existentes. Pueden utilizarse los formatos existentes para identificar una cuarta condición. Sugerencias • Copiar formatos en otras celdas Seleccione las celdas que tengan los formatos condicionales que desee copiar. En la barra de herramientas Formato, haga clic en Copiar formato y, a continuación, seleccione las celdas a las que desee dar formato. Para copiar sólo los formatos condicionales, seleccione las celdas a las que desee dar formato e incluya por lo menos una celda en la selección que contenga los formatos condicionales que desee copiar. En el menú Formato, haga clic en Formato condicional y, a continuación, haga clic en Aceptar. • Utilizar varias condiciones Si más de una condición especificada es verdadera, Microsoft Excel solamente aplicará las fórmulas de la primera condición que sea verdadera. 19 Fórmulas como criterios de formato Los valores de las celdas seleccionadas pueden compararse con una constante o con los resultados de una fórmula. Para evaluar los datos en las celdas no comprendidas dentro del rango seleccionado o para examinar varios conjuntos de criterios, puede utilizarse una fórmula lógica para especificar los criterios de formato. • Utilizar el valor de una celda como condición Si selecciona la opción Valor de la celda y se comparan los valores de las celdas seleccionadas con el resultado de la fórmula, deberá comenzarse la fórmula por el signo igual (=). • Utilizar una fórmula como condición Si se selecciona la opciónFórmula, la fórmula que se especifique deberá devolver un valor VERDADERO (1) o FALSO (0). La fórmula, que deberá comenzar con un signo igual (=), puede evaluar los datos en la hoja de cálculo activa. Para evaluar datos en otra hoja o libro puede definir un nombre en la hoja de cálculo activa o introducir una referencia a los datos en una celda de la hoja. A continuación, haga referencia a la celda o nombre en la fórmula. Por ejemplo, para evaluar los datos de la celda A5 en la Hoja1 del libro AñoFiscal.xls, introduzca la referencia siguiente, incluyendo el signo igual (=), en una celda de la hoja activa. =[Año fiscal.xls]HOJA1!$A$5 La fórmula puede también evaluar criterios que no estén basados en datos de hoja de cálculo. Por ejemplo, la fórmula =DIASEM("12-5-99")=1 devuelve VERDADERO si la fecha es 12-5-99 es domingo. A menos que una fórmula haga referencia de forma específica a las celdas seleccionadas a las que se encuentra dando formato, los valores de las celdas no afectarán si la condición es verdadera o falsa. Si una fórmula no hace referencia a las celdas seleccionadas, debe introducir las referencias de celda en la fórmula. • Utilizar las referencias de celda como condición Pueden introducirse referencias de celda en una fórmula seleccionando directamente las celdas en una hoja de cálculo. Cuando se seleccionan celdas en la hoja se insertan referencias de celda absolutas. Si desea que Microsoft Excel adapte las referencias a cada celda del rango seleccionado, utilice referencias de celda relativas. • Utilizar fechas Las fechas y las horas se evalúan como números de serie. Por ejemplo, si se compara el valor de la celda con fecha 7 de enero, 2001, la fecha se representará mediante el número de serie 36898. Veamos tres ejemplos prácticos de la aplicación de Formato Condicional. Conviene realizar estos ejemplos en un Libro Excel para afianzar los conocimientos. Ejemplo 1: Utilizar los datos de una celda En el siguiente ejemplo, se aplican formatos condicionales al rango B2:E3 para analizar las contribuciones trimestrales de todo el año. Los resultados trimestrales que contribuyan en un porcentaje del 30% o mayor al resultado total, se mostrarán en negrita y en color verde. Los resultados trimestrales que contribuyan en un porcentaje del 20% o menor, se mostrarán en negrita y en color rojo. 20 En la siguiente tabla se resumen los formatos condicionales que se aplican al rango B2:E3 MUY IMPORTANTE: Microsoft Excel adaptará la parte relativa (el número de fila) o la referencia de celda $F2 en la fórmula de modo que cada celda en el rango B2:E3 se compare con el total correspondiente en la columna F. Ejemplo 2: Utilizar una fórmula y referencias de celda externas En el siguiente ejemplo, una fórmula examina los datos ubicados fuera de las celdas seleccionadas a los que se aplican los formatos condicionales. Si el valor promedio del rango $A$1:$A$5 es mayor que 3.000 y los valores del rango son de 1.800, como mínimo, la fórmula devolverá un valor VERDADERO y se aplicará un sombreado en color verde. Las celdas a las que se ha dado formato pueden contener texto o cualquier valor, ya que no se utilizan en la fórmula. La fórmula Formatos Condición 1 =Y(PROMEDIO($A$1:$A$5)>3000, MIN($A$1:$A$5)>=1800) Sombreado en color verde Ejemplo 3: Utilizar una fórmula y una referencia de celda En el ejemplo siguiente, el formato condicional muestra números pares en el rango seleccionado A4:D20 en color de fuente azul. La función RESIDUO divide cada número por 2 y si no hay residuo o resto la fórmula devuelve VERDADERO. La fórmula Formatos Condición 1 =RESIDUO(A4;2)=0 Fuente azul Esta fórmula debe evaluar a cada celda en el rango. Cuando escriba cada fórmula en el cuadro de diálogo Formato condicional, sin embargo, introduzca sólo la referencia de celda para LA celda activa en el rango seleccionado. Microsoft Excel ajustará las referencias a otras celdas relativas a cada celda activa. Comprobar un formato condicional antes de aplicarlo Una manera fácil de asegurar que las referencias de la fórmula son correctas es aplicar el formato condicional primero a una celda en el rango. Después seleccionar todo el rango, haga clic en Formato condicional en el menú Formato y, a continuación, haga clic en Aceptar. El formato condicional aplicado a la primera celda se aplicará a todo el rango, con la fórmula ajustada de forma correcta en cada celda. FELICITACIONES !!! Has terminado de utilizar el material de la 3ra. Parte de Excel. Se viene alguna actividad….. luego las preguntas de repaso y, finalmente, la Actividad Final de la Unidad. Descansa….. diviértete un poco……. Y, luego, a continuar esforzándote. Te dejo un cordial saludo. Valor de la celda Fórmula Formatos Condición 1 Mayor o igual que =$F2*0,3 Negrita, fuente color verde Condición 2 Menor o igual que =$F2*0,3 Negrita, fuente color rojo Buscar celdas que tengan formatos condicionales Para buscar las celdas que tengan formatos condicionales específicos, haga clic en una celda que cumpla el criterio condicional y los formatos que desee localizar. Para buscar todas las celdas de la hoja de cálculo que tengan formatos condicionales, haga clic en cualquier celda. 1. En el menú Edición, haga clic en Ir a. 2. Haga clic en Especial. 3. Haga clic en Celdas con formatos condicionales. Para buscar las celdas que tengan formatos condicionales idénticos, haga clic en la opción Iguales a celda activa en Celdas con validación de datos. Para buscar las celdas que tengan cualquier formato condicional, haga clic en Todos.
Compartir