Aprender fórmulas y funciones con Excel 2010 con 100 ejercicios prácticos

EXCEL 2010 INCLUYE EN EL GRUPO de herramientas llamado Gráficos de la ficha Insertar de la Cinta, una galería de todos los tipos de gráficos disponibles, que permite aplicarlos con un clic.

IMPORTANTE

Gracias a la nueva Vista previa en directo podrá ver el efecto que se conseguirá con cada uno de los elementos de las galerías de estilos antes de aplicarlos. Sólo debe situar el puntero del ratón sobre ellos.

1 En este ejercicio crearemos un gráfico de columnas a partir de los precios unitarios sin descuentos de la hoja Descuentos. En primer lugar debe seleccionar el rango de celdas que incluye los datos que va a representar en el gráfico. Haga clic sobre la celda C1, pulse la tecla Mayúsculas y, sin soltarla, haga clic sobre el celda O2.

2 Haga clic en la pestaña Insertar de la Cinta de opciones.

3 En el grupo Gráficos puede ver los distintos tipos de gráficos disponibles. Pulse en el botón Columna.

4 Ahora puede ver los diseños de gráficos de columnas que ofrece la aplicación. Seleccione el primero de la categoría Cilíndrico.

5 Aparece así el gráfico en el centro de la hoja. Los datos que refleja el gráfico se encuentran rodeados de un marco azul en la tabla original. Al mismo tiempo que el gráfico, ha aparecido la ficha contextual Herramientas de gráficos mostrando el contenido de la subfichas Diseño, que permite cambiar el estilo visual del gráfico por otro de los prediseñados de la aplicación. Active la subpestaña Presentación.

6 Desde esta ficha podemos modificar el nombre del gráfico, sus etiquetas, sus ejes y su fondo. Haga clic en el comando Leyenda.

7 Este menú nos permite modificar la ubicación de la leyenda que, por defecto, es a la derecha del área del gráfico. También es posible acceder al cuadro Formato de leyenda, cuyas opciones también encontraremos en la subficha Formato. Seleccione la opción Ninguno para eliminarla. El menú del comando Leyenda le permite desactivar la leyenda o cambiar su ubicación.

8 Vamos ahora a añadir un rótulo a cada uno de los ejes. También en el grupo Etiquetas, despliegue el comando Rótulos del eje y escoja las opciones Título de eje horizontal primario y Título bajo el eje sucesivamente. Ubique fácilmente títulos, leyendas y rótulos donde prefiera.

9 Se añade la etiqueta indicada pero es genérica porque no hemos suministrado ninguna referencia de la cual extraer los datos. Despliegue de nuevo el comando Rótulos del eje y seleccione las opciones Título de eje vertical primario y Título girado.

10 Haga un clic en el rótulo del eje vertical para hacerlo editable y cambie su texto por Precios. Repita la operación para cambiar el rótulo del eje horizontal por Artículos.

11 Active la pestaña Formato, asegúrese de tener seleccionado todo el gráfico y pulse en alguna muestra de Estilos de forma para aplicar un contorno al área del gráfico.

12 En el grupo Selección actual se muestra indica el elemento seleccionado, que es el área del gráfico. Haga clic en el botón de flecha que está a su derecha y seleccione la opción Series 1.

13 Se seleccionan las columnas. Despliegue ahora la galería Estilos de forma pulsando en su botón Más, aplique alguna muestra que le guste y deseleccione para ver el resultado.

LOS GRÁFICOS CIRCULARES SON DE GRAN utilidad para demostrar la forma en la que cada una de los valores analizados contribuye al total presentado. Sólo pueden ser utilizados si todos los valores con los que trabaja son positivos.

1 En este ejercicio crearemos un gráfico circular a partir de los datos de la hoja Ventas1 del libro Precios y cantidades. Ábrala y seleccione desde la celda B1 hasta la E14.

2 Nos centraremos en los datos de la columna Total (€) sin IVA pero seleccionamos este rango más amplio para disponer de rótulos apropiados. Active la ficha Insertar y, para ver todos los tipos de gráficos a la vez, haga clic en el iniciador de cuadro de diálogo del grupo de herramientas Gráficos.

3 En el cuadro Insertar gráfico se muestran por defecto los gráficos de Columna. A la izquierda dispone de una lista de las diferentes categorías de gráficos así como una vista previa de todos ellos. Haga clic sobre la categoría Circular.

4 Seleccione la primera muestra de esta categoría y pulse el botón Aceptar.

5 Aparece el gráfico en el centro de la hoja y los datos de los que se nutre están seleccionados en la hoja de cálculo. Active la subficha Presentación, despliegue el comando Etiquetas de datos y seleccione la opción Extremo externo.La leyenda se ubica por defecto del lado derecho del gráfico.

6 Por ahora el gráfico resulta un poco confuso pero con una rápida ojeada podrá notar que representa sólo los datos de la columna Cantidad. Active la ficha Diseño en la Cinta y pulse en el comando Seleccionar datos del grupo Datos.

7 En el cuadro Seleccionar datos de origen puede modificar los datos analizados modificando el primer campo: Rango de datos del gráfico. Sin embargo en nuestro caso nos conviene más eliminar algunas series. Seleccione la serie Cantidad en el panel de la izquierda y pulse el botón Quitar.

8 Compruebe el cambio en el gráfico. Seleccione ahora la serie Precio, bórrela también y pulse el botón Aceptar.

9 Alargue el gráfico, tirando desde el centro de su borde inferior, para poder ver todo el contenido de la leyenda.

10 El color anaranjado, que corresponde a las Gomas de borrar es excesivamente grande y se debe a que, tal como sucedía en la hoja Descuentos, el precio unitario es incorrecto. Cambie el contenido de la celda D7 por el valor 0,18 y compruebe cómo el cambio se refleja de inmediato en el gráfico.

11 Pulse ahora en la pestaña Diseño y en el botón Mover gráfico, que es el único del grupo Ubicación.

12 En el cuadro Mover gráfico, seleccione la opción Hola nueva, mantenga el nombre propuesto y pulse el botón Aceptar.

13 Nuestro gráfico circular se ubica en una hoja nueva con el nombre designado y, al ampliarse, sus contenidos son mucho más fáciles de leer.

14 Seleccione la segunda opción del grupo Diseño de gráfico, aún en la subficha Diseño, y observe el resultado.

15 Despliegue ahora la galería de Estilos de diseño pulsando en su botón Más y seleccione alguno que le agrade. Puede cambiar completamente el aspecto de todos los elementos de un gráfico usando las múltiples herramientas que Excel 2010 pone a su disposición. De este modo sus presentaciones gráficas serán absolutamente personalizadas.

IMPORTANTE

Excel 2010 le permite guardar sus gráficos personalizados como plantillas de gráfico, lo que le permitirá crear nuevos gráficos directamente con el diseño creado. Para ello deberá usar la opción Guardar como plantilla de la subficha Diseño. El diseño de gráfico estará disponible en la categoría Plantillas del cuadro Insertar gráfico.

EXCEL 2010 DISPONE DE UN NUEVO comando, denominado Minigráficos, que permite crear pequeños gráficos incrustado en una celda de una hoja de cálculo. Los minigráficos ayudan a detectar modelos en los datos introducidos, proporcionando una representación visual de los mismos. Es posible escribir texto en la misma celda en la cual se inserta el minigráfico.

1 En la hoja Ventas1 del libro Precios y cantidades, seleccione los datos de la columna Cantidad, es decir, de C2 a C14.

2 Crearemos un minigráfico de tendencias que nos permita representar de una manera genérica los momentos de mayores ventas, aprovechando que los datos de esta tabla están organizados cronológicamente. Active la ficha Insertar de la Cinta de opciones.

3 El grupo Minigráficos ofrece opciones: lineal, de columnas o de pérdidas y ganancias. Crearemos un minigráfico lineal así que pulse sobre la primera opción. Al seleccionar la celda de destino, se inserta su referencia en el campo Ubicación.

4 Se abre el cuadro de diálogo Datos, donde se indican los datos que deben analizarse para la creación del minigráfico. Como hemos seleccionado previamente el rango de datos a representar, su referencia ya se encuentra en el primer campo. Pulse en el botón Contraer del campo Ubicación, que es el que está a la derecha del campo.

5 Selecciónela celda E16, que es donde ubicaremos el gráfico y pulse de nuevo sobre el icono de contracción para volver a maximizar el cuadro Crear grupo Minigráfico.

6 Pulse el botón Aceptar para crear el minigráfico.

7 El minigráfico se inserta en la celda indicada. Amplíe la fila 16 para que el gráfico pueda verse mejor.

8 Se ha cargado la ficha contextual Herramientas para mini-gráficos. Active su subficha Diseño.

9 Seleccione la celda que contiene el minigráfico, haga clic en la punta de flecha del comando Editar datos y escoja la opción Editar ubicación y datos del grupo.

10 En el cuadro Editar grupo Minigráfico, cambie el contenido del campo ubicación por la referencia $F$16 y pulse Aceptar.

11 Pegue el formato de las celdas E16 a E20 en las celdas que están a la derecha del rango y elimine las que están en las mismas filas, pero en la columna G.

12 Seleccione el minigráfico de nuevo y, en el grupo Mostrar de la subficha Diseño, seleccione las opciones Punto alto y Punto bajo.

13 Aunque sobre todo el punto más alto era muy fácil de distinguir a primera vista, se han añadido dos marcadores que destacan los puntos indicados. Despliegue la galería de Estilos y escoja uno que le agrade. Excel 2010 ofrece gran variedad de estilos predeterminados que puede aplicar a sus minigráficos.

14 Observe el resultado.

15 Cree con los datos de la columna Precio, de D2 a D14 un gráfico de columnas en la celda F17 siguiendo el procedimiento que acaba de conocer y compruebe el resultado.

IMPORTANTE

Para que la información suministrada por un minigráfico sea realmente útil, es necesario que quede muy claro qué datos representa. Esto se logra, básicamente, o con la proximidad clara a las celdas de origen, o con un texto que puede introducirse perfectamente en la misma celda que alberga el minigráfico.

SI CREA UNA TABLA A PARTIR de los datos de su hoja de cálculo, podrá obtener poderosas herramientas de administración de información. Los filtros, que se añaden automáticamente en cada una de las cabecera de columna, le ayudarán a buscar, gestionar y organizar los datos. Además, podrá darle a su tabla una excelente presentación sin ningún esfuerzo.

IMPORTANTE

Puede pulsar Ctrl.+E para seleccionar los datos de una tabla sin sus encabezados, o pulsar la misma combinación dos veces seguidas para seleccionarla entera. Una vez tiene seleccionada una tabla, puede eliminarla junto a sus datos pulsando la tecla Suprimir.

1 Descargue de nuestra web el archivo RefBolsas.xlsx, ábralo en la hoja Referencias y compruebe, desplazándose por él, la gran cantidad de datos que contiene.

2 Se trata de 151 referencias de un comercio para distintos tipos de bolsas. Vamos a crear una tabla con estos datos. Pulse Ctrl.+E para seleccionar todos los contenidos de la hoja.

3 Pulse el botón Tabla del grupo Tablas, ubicado en la ficha Insertar. El comando Tabla se encuentra en el grupo Tablas de la ficha Insertar y le dirige al cuadro de diálogo Crear Tabla.Si selecciona previamente los datos con los cuales desea crear una tabla, al abrirse el cuadro Crear tablas encontrará su referencia en el lugar adecuado.

4 Como hizo la selección de los datos previamente, el rango que muestra la aplicación en el campo ¿Dónde están los datos de la tabla? es correcto. Seleccione la opción Tiene encabezados y pulse en botón Aceptar.

5 Los datos conforman ahora una tabla que dispone de filtros en la cabecera de cada columna. Asegúrese de tener al menos una celda de la tabla seleccionada y muestre la subficha contextual Diseño, de Herramientas de tabla, que se ha habilitado al cambiar el formato.

6 Seleccione la opción Convertir en rango del grupo Herramientas y confirme que desea convertir la tabla en rango.

7 Los datos ya no están en una tabla, a pesar de que mantiene el estilo básico aplicado. Pulse Ctrl.+E, use el botón Dar formato como tabla, del grupo Estilos de la ficha Inicio y seleccione algún estilo que le guste. Puede convertir un conjunto de datos en una tabla de excelente presentación visual con filtros incorporados con sólo pulsar en uno de los estilos del menú Dar formato como tabla.

8 En el cuadro Dar formato como tabla, confirme que esté seleccionado el rango correcto, seleccione la opción La tabla tiene encabezados y pulse en botón Aceptar.

9 Se convierte de nuevo en tabla pero el aspecto se parece más a la tabla creada anteriormente que al estilo seleccionado. Pulse Ctrl.+E para seleccionar la tabla, y en la ficha Inicio, pulse el botón Color de relleno del grupo Fuente.

10 Seleccione la opción Sin rellenar y compruebe cómo ahora sí luce el estilo seleccionado.

11 Pulse el botón de punta de flecha que está al lado de la cabecera de la columna Ref. Fabricante y seleccione la opción Ordenar de A a Z.

12 La columna se organiza alfabéticamente y el resto de la tabla se reorganiza en función de este orden. Despliegue el cuadro de filtros de la columna Id. marca.

13 Pulse en la casilla de selección de la opción Seleccionar todo para desactivarla, seleccione la opción 61 y pulse Aceptar.

14 Compruebe cómo ahora sólo se ven las filas con esta identidad y pase al ejercicio siguiente.

UNA VEZ HA CREADO UNA TABLA, si usa el botón Autosuma para calcular el total de una columna, se insertará la función SUBTOTALES y se incorpora a la celda un botón que despliega un menú que le permite cambiar la función usada por el cálculo con sólo un clic.

1 En este ejercicio conoceremos otras de las prestaciones de Excel 2010 para la administración de datos en tablas. Ubíquese en la celda H153.

2 En la ficha Fórmulas de la Cinta, pulse en el botón Autosuma del grupo Biblioteca de fórmulas.

3 Se inserta en la celda indicada la sumatoria de todos los P. V. P. de la columna y esto no es nada nuevo. Pero observe la fórmula: Se trata de la función SUBTOTALES que ya conocemos. Como recordará, el primer argumento establece la función aplicada, que en este caso es SUMA y, como viene precedido por el número 10, indica que se ignoran las celdas ocultas. El siguiente dato simplemente indica la columna analizada. Pulse el botón Introducir.El argumento 109 en la función SUBTOTALES indica que para el cálculo serán consideradas sólo las celdas visibles (10) y que se efectuará una suma (9).

4 Se muestra el resultado de sumar todos los datos visibles. Despliegue el menú de filtros de la columna Identidad de marca y seleccione la identidad 89 sin desactivar la 61 y pulse en botón Aceptar.Tenga en cuenta que también puede combinar filtros de distintas columnas. Por ejemplo, para que se muestren sólo los artículos de la Identidad de marca 61 que pertenezcan a la familia 1011.

5 Se añaden las referencias con la identidad indicada y la suma se actualiza automáticamente. Sin embargo, el dato que nos proporciona la celda H153 es bastante inútil. Vamos a cambiarlo por otro que nos arroje una información más valiosa. ¿Se ha fijado en el botón de punta de flecha de esta celda? Pulse sobre él.

6 Puede en estos momentos cambiar la fórmula aplicada por cualquiera de las que se indican en este menú, o por cualquier otra si pulsa en la opción Más funciones, que le llevaría al cuadro Insertar funciones. Vamos a escoger una de las que ya hemos conocido: Promedio. Pulse sobre ella.

7 La celda nos muestra ahora, efectivamente, el promedio de todos los PVP. Con la celda aún seleccionada, coloque el cursor en su esquina inferior derecha y arrastre el ratón hacia la izquierda, para copiar su contenido en la celda G153.

8 La función se pega en la celda indicada y se adapta correctamente para mostrarnos el promedio de la columna G, Coste neto. Pulse en el botón de punta de flecha de esta celda y seleccione la opción Máx.

9 Ahora ya sabe cuál es el coste máximo de las referencias visibles en este momento en la columna G. Reduzca el zoom si hace falta, ubíquese en la celda K1, introduzca el texto Cantidad y pulse la tecla Retorno.

10 Automáticamente se amplía la tabla y la columna K asume mismo formato y apariencia del resto de la tabla.

11 Guarde los cambios para terminar y pasar al próximo ejercicio.

IMPORTANTE

La etiqueta inteligente que aparece a la derecha de la celda al añadir una nueva columna a una tabla, se llama Opciones de autocorrección y le permite eliminar el autoformato establecido.

EXCEL ES CAPAZ DE ANALIZAR LOS DATOS de una tabla y luego modificar el formato de determinadas celdas de acuerdo a su contenido. El comando Formato condicional usado para ello se incluye en el grupo Estilo de la ficha Inicio y permite marcar fácilmente excepciones o tendencias con degradados de color, barras de datos y conjuntos de iconos en los datos que cumplan una regla concreta.

IMPORTANTE

Puede eliminar las reglas de una selección usando la opción Borrar reglas de las celdas seleccionadas, que se encuentra en el submenú Borrar del comando Formato condicional.

1 En este ejercicio aprenderá a aplicar formatos condicionales a las celdas de una tabla. Imagine, en primer lugar, que quiere resaltar los artículos que cuestan menos de diez euros. Para comenzar, seleccione los datos de la columna G.

2 Haga clic sobre el comando Formato condicional del grupo Estilos, que se encuentra en la ficha Inicio de la Cinta. El comando Formato condicional se encuentra en el grupo Estilo de la ficha Inicio.

3 Como ve, el formato condicional permite resaltar reglas de celdas y reglas superiores e inferiores, así como utilizar barras de datos, escalas de color o conjuntos de iconos con fines analíticos y de presentación. Además, desde este menú de opciones, puede crear nuevas reglas, borrar las aplicadas y acceder al cuadro Administrador de reglas de formato condicionales, con el que trabajaremos en el siguiente ejercicio. Haga clic sobre la opción Resaltar reglas de celdas.

4 Es posible resaltar las celdas que contienen valores superiores o inferiores a un número, las que contienen un texto o una fecha concretos, etc. En este caso, haga clic sobre la opción Es mayor que.

5 Se abre el cuadro Es menor que, donde debe especificar el valor al que se aplicará la regla y el formato que se aplicará a las celdas que la cumplan. En el campo Aplicar formato a las celdas que son MENORES QUE escriba el valor 10.

6 Al introducir el valor, se van marcan las celdas que cumplen la regla con el formato seleccionado por defecto, que es Relleno rojo con texto rojo oscuro. Haga clic en el botón de punta de flecha del campo Con, pulse sobre la opción Relleno verde con texto verde oscuro y haga clic en la opción Aceptar para ver el resultado.

7 Ahora utilizaremos las barras de datos para representar gráficamente una escala de valores, donde los más altos mostrarán la barra más larga y los más bajos, la más corta. Seleccione de nuevo los datos de la columna Coste neto y asegúrese de incluir la celda G153, que contiene el precio medio.

8 Despliegue el comando Formato condicional.

9 Pulse sobre la opción Barras de datos y seleccione alguno de los estilos que aparecen en la categoría Relleno degradado.

10 Esta representación gráfica permite visualizar rápidamente aquellas celdas con valores mayores y menores. Pulse Ctrl.+Z dos veces y seleccione ahora los precios de la columna PVP (Sin IVA).

11 Despliegue el comando Formato condicional, luego las opciones de Escala de color y seleccione el primer estilo. La opción Más reglas le permite crear sus propias reglas a partir de una serie de opciones.

Se aplica el formato condicional: los colores más oscuros indican menor precio y los más amarillos y verdosos, un precio mayor.

Puede establecer un degradado entre un color que indicará cuál es el valor máximo y otro que indicará cuál es el mínimo.