funciones avanzadasaladinofg.com/files/download/capitulo_05.pdf · excel), aún necesitamos este...

HOJA DE CÁLCULO. FUNCIONES AVANZADAS CAPÍTULO 5

ACTIVIDADES

1. Condición simple. 2. Condición múltiple. 3. Eliminar el formato

condicional. 4. Localizar celdas con formato

condicional. 5. Validación de datos. 6. Localizar celdas con

validación. 7. Usar el autofiltro. 8. Autofiltro con varios campos. 9. Salir del autofiltro. 10. Filtros avanzados. 11. Definir el rango de criterios. 12. Ordenar por una columna. 13. Ordenar por varias columnas. 14. Ordenador rangos. 15. Crear subtotales. 16. Cómo son los esquemas. 17. Creación automática de

esquemas. 18. Crear una tabla dinámica. 19. Crear un gráfico dinámico. 20. Tablas. Sus orígenes. 21. Tabla de una entrada. 22. Tabla de dos entradas. 23. Protección de la celda. 24. Protección de la hoja. 25. Protección del libro. 26. Protección de archivo.

Hasta ahora hemos estado descubriendo aspectos de Excel que podríamos considerar como básicos. Para muchos usuarios esto es suficiente, dado el tipo de trabajo que desarrollan, pero el potencial que oculta este programa es muy superior, tanto que si nos atenemos a la norma 80/20 (el 80% de los usuarios sólo usa el 20% de los recursos de Excel), aún necesitamos este capítulo.

Funciones avanzadas5

5.1 Formato condicional

Ya sabemos cómo ajustar la apariencia de una celda utilizando las opciones de formato, pero estos atributos permanecerán fijos en tanto no volvamos a modificarlos. No obstante, Excel nos permite, a través del formato condicional, hacer que ese formato cambie en función del contenido de la celda.

¿Qué puede hacer por nosotros? Pues imagínate que quieres que el texto de una celda cambie dinámicamente de color o de contenido si los datos indican un saldo negativo, cero o positivo; o que la identificación de una factura se destaque si tiene una antigüedad mayor a un límite; o que un fondo de color señale el máximo valor de una lista. En pocas palabras, que el formato de una celda sea dinámico para localizar rápidamente las cosas de un vistazo.

1. Desde , localiza y abre el libro ‘Calificaciones.xls’.

HOJA DE CÁLCULO EN LA ENSEÑANZA -163-


Esta hoja se encarga de recoger las calificaciones escolares obtenidas por el alumnado durante todo el curso en tres apartados: Teoría, Práctica y Actitud. La nota de cada evaluación se obtendrá calculando la media ponderada (que no aritmética) de estos tres apartados. El peso de la ponderación se especifica en el rango C17:C19. La nota final vendrá dada por la media aritmética de la obtenida en las tres evaluaciones.

Sin que sirva de precedente, para ahorrarnos el trabajo de cumplimentar las calificaciones, en cada evaluación y a todos los alumnos, hemos empleado la función ALEATORIO(), así que con cada pulsación de los valores de las celdas se actualizarán. Pruébalo para comprobar que todo funciona según lo previsto.

El primer propósito de este ejercicio es que la hoja destaque en rojo aquellas calificaciones inferiores a 5 (suspenso) y en azul el resto (aprobado), tanto en la nota de cada evaluación como en la nota final. Éstas serán las condiciones de partida.

Para aplicar el formato condicional a una celda o rango hay que empezar por seleccionarlo. Como en este caso son cuatro los rangos a los que hay que aplicar la condición, realizaremos una selección múltiple de rangos no adyacentes:

Actividad obligatoria

Condición simple

2. Selecciona el rango F4:F15, y manteniendo pulsada la tecla haz clic y arrastra sobre los rangos J4:J15, N4:N15 y O4:O15. Verás los cuatro rangos seleccionados.

3. Acude al menú Formato – Formato condicional… y se abrirá el cuadro de diálogo Formato condicional .

4. En el primer cuadro desplegable seleccionamos Valor de la celda, pues eso es lo que queremos evaluar en nuestra hoja.



Aquí permite también la selección del valor Fórmula para cuando nuestra condición estuviera basada en el resultado de una fórmula, pero ahora no es el caso. Si así fuera, es obligatorio que la fórmula devuelva un valor lógico VERDADERO o FALSO. Si el resultado de la fórmula es VERDADERO, es que la condición se cumple y se aplicaría el formato condicional especificado. Pero si fuera FALSO, no se aplicaría el formato condicional.

5. En el segundo cuadro, desplegamos sus opciones y elegimos ‘menor que’, y en el tercer cuadro escribimos el valor mínimo para aprobar: 5.

Con esta acción estamos construyendo la condición asociada al formato que ahora especificaremos:

6. Haz clic en el botón para abrir el cuadro de diálogo Formato de celdas .

7. Desde la pestaña elige Estilo: Negrita, el Color: Rojo y pulsa para retornar al cuadro de diálogo Formato condicional .

Ten presente que la condición se evalúa sobre el valor real de la celda, que pudiera estar forzada a mostrarse redondeado, por lo que hay que elegir muy bien la condición, no vaya a ser que, por ejemplo, mostrando un 5 (por la acción de un redondeo), se pusiera de color rojo (recuerda que una

cosa es el valor real de la celda y otra, el valor mostrado en la celda) (sería más acertada la condición ‘menor o igual que’ 4,99).

Parecería aconsejable ahora añadir una nueva condición para aplicar el color azul a los aprobados, creando así una condición múltiple (luego crearemos una), pero en este caso, bastará con aplicar por defecto el color azul a todos los rangos seleccionados y dejar que la única condición creada (condición simple) aplique el color rojo a los suspensos cuando proceda. Así que:

8. Pulsa para aplicar el formato condicional a los rangos y, sin abandonar la selección, aplícales el color Azul desde el control Color de fuente de la barra de herramientas Formato.



Desde este momento, aquellos valores que cumplan la condición aparecerán en color rojo y todos los demás en azul.

9. Pulsa para recalcular varias veces y comprobar que el formato condicional es dinámico, tal como te indicamos. Aprovecha para guardar el libro con el nombre ‘Ejercicio 13.xls’.

¿Y si quisiéramos que además de poner en rojo a los suspensos, nos señalase con un fondo de celda rojo y el texto en blanco, la nota final más baja de la clase? Pues entonces hay que añadirle al rango esa nueva condición, creando una condición múltiple.


Condición múltiple

Puedes especificar hasta tres condiciones haciendo clic sobre el botón y cumplimentando los nuevos cuadros que van apareciendo, pero en este caso tienes que tener presente que cuando hay varias condiciones, si una celda cumple más de una, Excel aplica solamente el formato de la primera condición que se cumpla y deshecha las demás. O sea, que si en nuestra hoja dejamos en primer lugar la condición de poner en rojo los suspensos y luego le añadimos, como segunda condición, que la nota más baja (que probablemente también será suspenso) tenga otro formato distinto, nunca llegaremos a verlo al predominar el formato de la primera condición. Por esta razón hay que empezar por eliminar el formato del rango de la nota final (O4:O15), crear como primera condición la de avisarnos de la nota más baja y añadirle, como segunda condición, la de poner en color rojo la nota de los suspensos.


Eliminar el formato

condicional

Eliminar un formato condicional no es tan sencillo como pulsar . Así sólo conseguirás eliminar el contenido de la celda, pero nunca su formato condicional, recuérdalo.



Podrías acudir a algo tan agresivo como Edición - Borrar - Formatos o Edición - Borrar - Todo, pero si solamente quieres eliminar el formato condicional:

10. Selecciona el rango O4:O15; a continuación ejecuta Formato - Formato condicional… para abrir el cuadro de diálogo Formato condicional .

11. Haz clic en el botón y aparecerá el cuadro de diálogo Eliminar formato condicional .donde debes seleccionar las condiciones que quieres eliminar y pulsar

para retornar de nuevo al cuadro Formato condicional .

Este cuadro de diálogo siempre te mostrará la posibilidad de eliminar las tres posibles condiciones, aunque no las hayas creado todas.

Ahora hay que empezar a crear las nuevas condiciones, pero en el orden correcto:

12. En el primer cuadro de lista desplegable deja marcado Valor de la celda; en el segundo cuadro elige ‘igual a’, y en el tercero escribe la función ‘=MIN($O$4:$O$15)’ usando referencias absolutas.


14. Desde la pestaña elige Estilo: Negrita, Color: Blanco.

15. Desde la pestaña selecciona Color: Rojo y pulsa para retornar al cuadro de diálogo Formato

condicional .

16. Pulsa el botón y aparecerán nuevos cuadros para introducir la Condición 2.

17. El primer cuadro permanecerá con Valor de la celda. En el segundo cuadro elegimos ‘menor que’, y en el tercer cuadro escribimos el valor mínimo para aprobar: 5.




19. Desde la pestaña elige Estilo: Negrita, el Color: Rojo y pulsa dos veces para ver el resultado.

20. Pulsa para recalcular varias veces y observar los resultados de tu primera condición múltiple. Guarda el libro con el nombre ‘Ejercicio 14.xls’.

A simple vista es imposible apreciar si una celda contiene formato condicional. Sin embargo, puedes acudir al cuadro de diálogo Ir a para que te indique las celdas que lo contienen.


Localizar celdas con

formato condicional 21. Selecciona Edición - Ir a… (o pulsa )

para acceder al cuadro de diálogo Ir a .

22. Haz clic sobre el botón y accederás al

cuadro de diálogo Ir a Especial , donde debes marcar la opción Celdas con formatos condicionales y Todos. Finaliza con

y Excel mostrará seleccionadas las celdas que tienen formato condicional:



Algunas consideraciones sobre las celdas con formato condicional: Consejos

para usar el formato

condicional

Copiar el contenido de una celda y pegarlo en otra celda o rango que contenga formato condicional, eliminará el formato condicional del rango de destino (y no te avisará, ¡ojo!).

Si tienes que pegar datos copiados en una celda que contenga formato condicional, acude al cuadro de diálogo Pegado especial (Edición - Pegado especial…) y selecciona Valores.

Cuando copies una celda que contenga formato condicional, también copias el formato condicional, así que cuando desees copiar sólo los formatos condicionales, usa el cuadro de diálogo Pegado especial y selecciona la opción Formatos.

Insertar filas o columnas en un rango que ya contenga un formato condicional hará que las nuevas celdas hereden el mismo formato condicional.

5.2 Trabajar con listas

Curiosamente, y aunque no ha sido creado para ello, uno de los usos más comunes de Excel es la organización de listas o bases de datos de hoja, pues, si sabes extraer la información que realmente necesitas, resulta ser una estupenda herramienta de análisis de datos.

Una lista es una colección de datos organizados, con una primera fila en forma de encabezados, seguida de filas adicionales de datos. Tiene toda la pinta de ser una tabla de una base de datos (en realidad eso es lo que es), tanto es así que se habla de las

columnas de una lista como campos y de las filas como registros (terminología propia de las bases de datos).

¿Qué es una lista?

Sí, esta figura es una lista. Y también lo es el contenido de la hoja ‘Listado de artículos’, del libro ‘Productos.xls’, con la que ya has trabajado anteriormente.



Y lo son porque cumplen con todos los requisitos para serlo:

Utilizan títulos en cada columna y que no se repiten. Todos los datos de una misma columna son del mismo

tipo. No podemos hacer coincidir, en un mismo campo, datos numéricos con fechas, por ejemplo, o cualquier otro tipo.

La lista está sola en una hoja de trabajo independiente. No contienen filas en blanco. En las operaciones con

listas, Excel determina sus límites de manera automática y una fila en blanco será interpretada como el final de la lista.

No utilizan las celdas de la derecha o de la izquierda de la lista.

Se le pueden añadir o eliminar nuevas filas y columnas (registros y campos, recuerda), en cualquier momento.

Para introducir datos en una lista no hay que hacer nada especial, sólo tienes que moverte por la hoja e introducir los datos en las celdas apropiadas (recuerda no dejar nunca filas en blanco), bien manualmente, copiando los datos desde otra hoja o a través de un cuadro de diálogo.

Introducir datos en una lista

Excel te ofrece dos ayudas inestimables para cuando tengas que introducir manualmente datos repetitivos en una lista: Autocompletar y Listas de elección.

Autocompletar ya te es conocido desde capítulos anteriores: ese comportamiento del programa que hace que, al escribir las primeras letras de un texto en una celda (que ha de ser contigua), Excel complete la entrada teniendo en cuenta otras entradas anteriores realizadas en la misma columna.

Una lista de elección es una lista desplegable que aparece al hacer clic con el botón secundario del ratón sobre la celda que estás editando, y seleccionando Elegir de la lista desplegable… en el menú contextual que aparece. Esta lista mostrará los nombres de todos los valores de la columna (sin repeticiones); si está ahí el que quieres utilizar, selecciónalo con un clic y no tendrás que escribir nada más.



Pero tal vez quieras, de manera más refinada, utilizar un cuadro de diálogo para introducir los datos. En ese caso, bastará

con que selecciones cualquier celda de la lista y acudas al menú Datos – Formulario.... Excel calculará los límites de la lista y mostrará un cuadro de diálogo, que se llamará igual que la hoja activa, con los campos de la lista (si algún campo contiene una fórmula, aquí no aparecerá) y una serie de botones autoexplicativos.

El cuadro de diálogo muestra siempre el primer

registro de la lista. En la esquina superior derecha dispones de un indicador que te avisa de cuál es el registro que tiene ahora mismo seleccionado y cuántos hay. Cualquier cosa que escribas aquí será trasladada a la lista de inmediato. Para moverte por los distintos campos puedes usar la tecla de tabulación . Si quieres avanzar o retroceder un registro, puedes usar las teclas y , o los botones y ; para moverse en saltos de diez usa y . Para introducir un nuevo registro usa el botón

. Si la lista contiene fórmulas, Excel las introduce automáticamente en su lugar.

Por medio del botón podrás establecer una condición para buscar los registros. Por ejemplo, para encontrar

aquellos que tengan un precio inferior a 100, introduce ‘<100’ en el campo ‘Precio unidad’, en los campos vacíos que te muestra al pulsar (puedes combinar más opciones para crear un criterio múltiple). Luego, regresas al y con los botones y

(no valen las flechas de movimiento) irás mostrando los registros que cumplan la condición.

Cuando termines, no te olvides de el criterio establecido para dejar libre la lista.



La selección correcta de criterios te ayudará en la entrada de nuevos datos, al permitirte ver los valores de la lista relacionados, pero no te ayudará más en la protección contra los errores. Por ejemplo, si te empeñas en introducir el mes 14, o el 30 de febrero, u otras barbaridades de este tipo. Para eso Excel dispone de la función de validación de datos.

La validación de datos es una funcionalidad que te permitirá configurar ciertas reglas para la introducción de datos en una celda. En definitiva, establecerás unos criterios que harán que el programa, ante la entrada de valores no válidos por parte del usuario, muestre un mensaje de advertencia personalizado, permitiendo impedir, incluso, el avance en tanto no se resuelva.


Validación de datos

Llevémoslo a la práctica en nuestra hoja ‘Nota final’, haciendo que no se puedan introducir calificaciones menores de 0 y mayores de 10.

23. Sobre la hoja ‘Nota final’, selecciona los rangos donde se introducirán las notas de los alumnos: C4:E15, G4:I15 y K4:M15.

24. En el menú principal elige Datos - Validación…. Se abrirá el cuadro de diálogo Validación de datos . Asegúrate de tener activa la pestaña

.

25. En la lista desplegable Permitir:, elige Decimal. Se abrirá una nueva lista desplegable, Datos:, donde elegirás entre y marcarás un Mínimo de 0 y un Máximo de 10.

En función de la condición que selecciones desde esta lista desplegable Datos:, Excel determinará a qué otros controles tendrás acceso. Aquí hemos introducido valores absolutos, pero puedes utilizar el contenido de celdas, bien introduciendo su referencia o por medio del control .

Si dejas activada la marca de Omitir blancos, quedarán permitidas las celdas vacías. Si marcas la opción Aplicar estos cambios a otras celdas con la misma configuración, hará exactamente eso: todas aquellas celdas de las mismas características que la que estás modificando, adquirirán el mismo criterio de



validación. De haberla marcado en este ejemplo, bastaría con construir la validación en cualquiera de las celdas del rango, haciendo innecesaria la selección de los tres rangos, dado que todas sus celdas tienen la misma configuración, pero usa esta opción con cuidado, no vaya a ser que haga extensiva esta propiedad a otra parte de la hoja que no debiera; mejor no utilizarla por ahora.

26. De manera opcional, puedes hacer clic en la pestaña y especificar un Título: y un Mensaje de entrada: que aparecerá, a modo de cuadro de texto, cuando el usuario seleccione la celda. Si lo deseas, el cuadro donde aparece el mensaje puedes arrastrarlo a otro lugar de la hoja.

27. Y en la pestaña puedes especificar (también opcionalmente) el Título: y Mensaje de error: que quieres que aparezca cuando se realice una entrada de datos no válidos. Asegúrate de que se quede marcada la casilla de verificación Mostrar mensaje de error si se introducen datos no válidos.

Atento, pues la selección que realices en Estilo: determinará las opciones del usuario que introduzca un valor no permitido. Si quieres evitar la entrada de datos no válidos, debes seleccionar Límite. Cualquiera de las otras

opciones sólo provocan el aviso, pero permitirán la entrada de valores prohibidos. Además, la validación de datos no se aplica a los resultados calculados de fórmulas.

Desde la barra de herramientas Auditoría de fórmulas (Ver - Barras de herramientas - Auditoría de fórmulas) puedes usar el botón

para descubrir celdas validadas que contienen datos incorrectos: las rodea con un círculo, que desaparecerá cuando las corrijas.

28. Pulsa para terminar, salva la hoja con el nombre ‘Ejercicio 15.xls’ y comprueba si funciona tal como esperabas.



Aunque no es ningún problema para comprobar esta funcionalidad, no olvides que la hoja ‘Nota final’ usa la función ALEATORIO() en todas sus celdas de entrada de datos, así que cuando escribas un valor en una celda, eliminarás la fórmula que contiene y el valor ya no se modificará cuando provoques el recálculo de la hoja.

Al igual que en el formato condicional, si quieres localizar las celdas sujetas a validación:


Localizar celdas con validación

29. Selecciona Edición - Ir a… (o pulsa ) para acceder al cuadro de diálogo Ir a .

30. Haz clic sobre el botón y accederás al cuadro de diálogo Ir a Especial , donde esta vez debes marcar la opción Celdas con validación de datos y Todos. Finaliza con y verás seleccionadas las celdas que tienen validación.

5.3 Trabajar con filtros

Filtrar una lista es el proceso de ocultar todas aquellas filas o registros que no cumplan una condición establecida. Repetimos: ¡ocultar, no eliminar!

Hay dos formas de filtrar una lista: usando el Autofiltro (en el caso de criterios simples de filtrado), o utilizando el Filtro avanzado (cuando los filtros han de ser más complejos). Veamos cómo funcionan, pues el filtrado es una técnica que te resultará extraordinariamente útil.

31. Abre el libro ‘Productos.xls’. Quedará activa su única hoja ‘Listado de artículos’.


Usar el autofiltro 32. Selecciona el comando

Datos – Filtro - Autofiltro, en la barra del menú principal.

Excel analiza la lista y añade las flechas de lista desplegable a los nombres de las distintas columnas o campos, en su encabezado.



Al hacer clic sobre una cualquiera de esas flechas, su lista desplegable te mostrará todos los elementos que están contenidos en la columna (sin repeticiones).

33. Haz clic sobre la lista desplegable de la columna Categoría y selecciona, por ejemplo, ‘Mariscos’.

A partir de este momento, la flecha de la lista desplegable cambia de color con el fin de recordarte que es una lista filtrada según uno de los elementos contenidos en ese campo y Excel sólo mostrará los registros que tengan ese elemento:

El autofiltro tiene un límite: la lista desplegable sólo puede mostrar los primeros 10.000 elementos distintos del campo. Y cuando quieras desactivar el filtrado, sólo tienes que elegir la opción (Todas) en la lista desplegable.

34. Guarda el libro con el nombre ‘Ejercicio 16.xls’.

concreto, podrías acudir a su lista

Habrá ocasiones en las que tendrás que filtrar una lista según los valores de más de una columna. Una vez filtrada nuestra lista para ver toda la oferta de productos de la categoría ‘Mariscos’, si necesitases conocer los de un proveedor en

desplegable y aplicarle un filtro más:


Autofiltro con varios campos



35. Haz clic sobre la lista desplegable de la columna Proveedor y selecciona, por ejemplo, ‘Tokyo Traders’.

Ahora son dos las condiciones de filtrado, la lista resultante se reduce. Y podrías seguir estableciendo filtros con más columnas si fuera necesario, hasta con condiciones de filtrado creadas por ti:

36. Haz clic sobre la lista desplegable de la columna Precio unidad y selecciona ‘(Personalizar…)’. Se abrirá el cuadro de diálogo Autofiltro personalizado .

37. En Precio unidad selecciona ‘es mayor que’ y asígnale un valor de, por ejemplo, ‘100’.

para aplicar el filtro y salva el fichero ercicio 17.xls’.

38. Pulsa con el nombre ‘Ej

En este filtrado m‘Mariscos’ servidos por ‘Toky‘100’.

últiple, Excel mostrará sólo aquellos o Traders’ y con un precio superior a



Para salir del modo de filtrado automático y eliminar las flechas de lista desplegable:


Salir del autofiltro

39. Desmarca la casilla de verificación en el menú Datos – Filtro - Autofiltro.

La mayor parte de las veces, el autofiltro será suficiente para nuestras selecciones, pero tiene sus límites. Imagínate que en nuestra hoja deseas conocer los productos de repostería de ‘Boston’ o ‘Barcelona’. Por lo que has aprendido hasta ahora, el proceso lógico sería:


Filtros avanzados

40. Abre el libro ‘Productos.xls’. Quedará activa su única hoja ‘Listado de artículos’.

41. Selecciona el comando Datos – Filtro - Autofiltro, en la barra del menú principal.

42. Haz clic sobre la lista desplegable de la columna Categoría y selecciona, por ejemplo, ‘Repostería’.

43. Haz clic sobre la lista desplegable de la columna Ciudad y seleccionas ¿‘Boston’? ¿’Barcelona’? ¡! No puedes elegir las dos.

Éste es el problema: cuando los datos que usaremos como condición para crear el filtro están en la misma columna, el autofiltro no sirve. Hay que acudir a los filtros avanzados, un poco más trabajosos, pero es el precio de la potencia.

Antes de usar un filtro avanzado hay que crear lo que se llama un rango de criterios. Este rango va a contener la información que Excel usará para filtrar la lista y debe cumplir los siguientes requisitos:

Tener, al menos, dos filas, siendo la fila superior la que debe contener los nombres de los campos de la lista a filtrar.

El resto de filas contendrán los criterios para construir el filtro.

¿Lioso, verdad? Ya verás como no es tan difícil si sigues los siguientes pasos:



44. Comienza por desmarcar la casilla de verificación en el menú Datos – Filtro - Autofiltro para deshabilitar el autofiltro, que aquí ya es inútil.

45. Para construir el rango de criterios, ponte al final de la hoja que tienes abierta, por ejemplo en C2158.

Tienes que crear el rango en la misma hoja y no puede quedar oculto, por eso nunca utilices celdas de una fila con datos ya que, al aplicar el filtrado, pueden quedar ocultas las del rango de criterios. También se puede crear el rango en la parte superior de la hoja, si hay o creas filas libres.

46. En C2158 escribe ‘Categoría’ y en D2158 escribe ‘Ciudad’. Estos son los campos implicados en el filtrado, el resto no hace falta ponerlos. Para evitar errores, procura escribir todos los valores exactamente igual a como están en la hoja de cálculo, respetando las tildes, mayúsculas, etc.

47. En C2159 escribe ‘Repostería’, en D2159 ‘Barcelona’, en C2160 repite ‘Repostería’ y en D2160 ‘Boston’ y valida la celda.

48. En la barra del menú principal selecciona Datos - Filtro - Filtro avanzado… para abrir el cuadro de diálogo Filtro avanzado .

49. Indica el rango de la lista, incluyendo el encabezado: ‘Listado de artículos’!$A$1:$J$2156, el rango de criterios: ‘Listado de artículos’!$C$2158:$D$2160 (incluye siempre el nombre de la hoja y usa referencias absolutas) y asegúrate de que está seleccionada la opción Filtrar la lista sin moverla a otro lugar, a no ser que la quieras Copiar a otro lugar. Pulsa para aplicar el filtrado.

El rango de criterios evalúa los campos de cada fila (salvo la fila de encabezamiento) uniéndolos con el operador ‘Y’, y los campos de cada columna los une con el operador ‘O’.


Definir el rango de criterios

En otras palabras, en este ejemplo, el filtrado mostrará sólo aquellas filas en las que la columna Categoría sea ‘Repostería’ Y la



columna Ciudad sea ‘Barcelona’ O ‘Boston’. Por supuesto, puedes seguir añadiéndole todas las filas y columnas que quieras al rango de criterios para crear filtros mucho más complejos.

50. Salva el libro con el nombre ‘Ejercicio 18.xls’ y no lo cierres aún.

5.4 Poner orden en los datos

No siempre es importante tener ordenadas las filas de una lista, pero si fuera necesario Excel dispone de una potente herramienta para hacerlo. Puede ordenar las filas por una o varias columnas, usando un orden determinado, e incluso puedes pedirle que ordene también rangos independientes.

La forma más rápida de ordenar es seleccionar una celda de la columna a ordenar y utilizar los botones (Orden ascendente) y (Orden descendente), que localizarás en la barra de herramientas Estándar. Por ejemplo, en la hoja ‘Ejercicio 18.xls’ que tienes abierta, vamos a ordenar por la columna Precio con descuento.


Ordenar por una columna

51. Selecciona cualquier celda de la columna Precio con descuento.

52. Pulsa el botón (Orden ascendente) y ya está. El producto más barato se pondrá en primer lugar de la lista.

53. Ahora pulsa el botón (Orden descendente) para ver cómo el producto más caro se coloca al principio.

El criterio que sigue Excel para ordenar las filas es, para el orden ascendente, como sigue:

En primer lugar coloca los valores numéricos, ordenados del menor negativo al mayor positivo.

A continuación pone los valores de texto, en orden alfabético, no distinguiendo, por defecto, las



mayúsculas de las minúsculas (esto se puede cambiar y luego te diremos cómo).

Sigue con los valores lógicos, poniendo FALSO antes que VERDADERO.

Luego las celdas que contienen valores de error.

Y, finalmente, las celdas vacías.

Si se empleara el orden descendente, esta secuencia se invierte (salvo para las celdas vacías, que seguirán estando en último lugar).

Ten en cuenta que si ordenas una lista filtrada, Excel ordenará sólo las filas visibles, así que cuando elimines el filtrado, la lista dejará de estar ordenada.

Del mismo modo, debes tener cuidado si ordenas una lista con fórmulas, pues si las fórmulas hacen referencia a celdas situadas en una fila distinta o invocan celdas de otras filas, hojas o libros, las fórmulas no serán válidas después de ordenar la lista.

Serán muchas las ocasiones en las que un solo criterio de ordenación no será suficiente y querrás ordenar por dos o más columnas, sobre todo para deshacer empates. Por ejemplo, en nuestra lista vamos a ordenar los productos por orden alfabético (orden ascendente), y a productos iguales, que nos ponga los más caros primero (orden descendente). El proceso sería como sigue:


Ordenar por varias columnas

54. Selecciona Datos – Ordenar…, en la barra del menú principal. Se abre el cuadro de diálogo Ordenar .

55. Selecciona el campo ‘Nombre de producto’ de la lista desplegable Ordenar por. Marca Ascendente.

56. Selecciona el campo ‘Precio con descuento’ de la lista desplegable Luego por. Marca Descendente.

Vigila si está activada la opción El rango de datos tiene fila de encabezamiento, porque si es así, la primera fila (la que contiene los nombres de las columnas) quedará fuera de la ordenación.

Si accedes al botón de este cuadro de diálogo, podrás hacer, entre otras cosas, que distinga las mayúsculas y las minúsculas durante el proceso de ordenación, colocando primero las minúsculas.



57. Finalmente, pulsa para comprobar el resultado y guarda el libro como ‘Ejercicio 19.xls’.

Si quieres ordenar un rango, no tienes más que seleccionarlo y utilizar los botones de ordenación de la barra de herramientas Estándar, pero ten presente que las celdas que quedan fuera del rango no son ordenadas, dejando, probablemente, inservible la tabla de datos.

Ordenar rangos

Si necesitas ordenarlo para realizar una consulta o análisis, después puedes deshacer la acción para que todo vuelva a quedar como estaba (Edición - Deshacer).

5.5 Subtotales

Crear subtotales te será muy útil para disponer de información inmediata. Eso sí, es condición muy conveniente que la lista esté ordenada antes de realizar un subtotal, porque sino, como verás a continuación, perdería todo su sentido.

Los subtotales no son más que operaciones parciales sobre un conjunto de datos que tienen en común el valor de un campo, de tal modo que se insertarán en la lista, como una fila más, cada vez que



el valor de un campo concreto cambie. Como siempre, acudamos a un ejemplo esclarecedor:

58. Desde la carpeta , en el CD-ROM del curso, localiza y abre el libro ‘Personal.xls’. Quedará activa su única hoja ‘Plantilla’.


Crear subtotales

Se trata de la plantilla de empleados de una empresa ficticia y que se presenta como una lista ordenada por el campo Categoría. Si quisieras saber cuántos empleados hay en cada categoría, podrías contarlos, ahora que están ordenados, pero también podrías construir subtotales basados, precisamente, en el campo Categoría. Sigue estos pasos:

59. Selecciona una celda cualquiera de la lista, no importa cuál, el caso es hacer que Excel sepa con qué lista tiene que trabajar. Asegúrate de tenerla ordenada por el campo que usarás como referencia (en este caso, Categoría).

60. Selecciona Datos - Subtotales… en la barra del menú principal. Se abrirá el cuadro de diálogo Subtotales .

61. Despliega la lista de la opción Para cada cambio en: (que te mostrará todos los campos de la lista) y selecciona el que usarás como referencia para calcular el subtotal, en este caso ‘Categoría’ (recuerda que este campo tiene que estar ordenado).

62. En el cuadro de lista Usar función: elegirás ‘Cuenta’ de entre las 11 funciones que permite.

63. En el cuadro de lista Agregar subtotal a: tendrás que marcar dónde quieres que la función ponga sus resultados. Como verás, creará una línea por cada resultado que obtenga, ubicándolo en la columna que aquí le indiques. En este caso marca el valor ‘Categoría’.

64. Marca la casilla Reemplazar subtotales actuales para que los recalcule, por si nuestra hoja ya los tuviera de antes.



65. Si marcas Salto de página entre grupos, el programa colocará un salto de página después de cada subtotal. En este caso déjala desmarcada.

66. Activa la casilla Resumen debajo de los datos, para que los subtotales se coloquen debajo de cada agrupamiento de datos. Si estuviera desmarcada, los datos se colocarían encima de los agrupamientos.

67. Pulsa y Excel analizará la lista, insertando las fórmulas (con la función SUBTOTALES()) en los lugares especificados (además creará un esquema), y ya puedes disfrutar del resultado.

Si te desplazas al final de la lista, verás la cuenta general, o sea el total de empleados, en este caso.

68. Guarda el libro con el nombre ‘Ejercicio 20.xls’ y no lo cierres. Aún trabajaremos con él.

Otro ejemplo: ¿Quieres saber cuánto se gasta la empresa en pagar los sueldos a sus empleados y en cada categoría? Es un problema hecho a la medida, de nuevo, para los subtotales. Sigue estos pasos:

69. Selecciona Datos - Subtotales… en la barra del menú principal. Se abrirá el cuadro de diálogo Subtotales .

70. Despliega la lista de la opción Para cada cambio en: y selecciona ‘Categoría’. Asegúrate de que este campo esté ordenado.



71. En el cuadro de lista Usar función: elegirás ‘Suma’.

72. En el cuadro de lista Agregar subtotal a: marca el valor ‘Total percibido’.

73. Marca las casillas Reemplazar subtotales actuales y Resumen debajo de los datos. Desmarca Salto de página entre grupos.

74. Pulsa para ver el resultado.

75. Guarda el libro con el nombre ‘Ejercicio 21.xls’ y no lo cierres. Lo necesitamos para el próximo apartado.

Y así sucesivamente podríamos estar creando cuantos subtotales precisásemos. El proceso es siempre el mismo, sólo hay que seleccionar adecuadamente el campo para la agrupación de registros, ordenar la lista por ese mismo campo y acertar con la función a utilizar.

5.6 Esquemas

Una vez creados los subtotales, en hojas relativamente grandes, tendremos que recorrerlas con calma para ir tomando nota de los distintos valores que cada subtotal nos va ofreciendo. ¿No habrá una forma más resumida de presentar la información



relevante de la lista, una vez creados los subtotales, sin tanto detalle? Pues sí, la hay, son los llamados esquemas.

Los esquemas son muy útiles a la hora de mostrar la información de manera resumida, sin que tengan que aparecer todos los datos presentes en la hoja de trabajo. Eso sí, los esquemas no se podrán aplicar a todas las hojas, sólo a aquellas cuyos datos estén jerarquizados, es decir, que puedan agruparse en niveles. Pero veamos primero un ejemplo para hacernos una composición de lugar:

La hoja ‘Plantilla’, con los subtotales calculados, pero sin esquema:


Cómo son los esquemas

Ahora la misma hoja, pero con un esquema de fila aplicado:



Observa cómo Excel crea un nuevo borde en la parte izquierda de la ventana de la hoja, con una serie de controles de esquema que te permitirán definir el nivel de detalle que quieres ver.

Bastará con hacer clic sobre los controles del esquema , y para mostrar rápidamente las filas que muestran resúmenes o

encabezados de secciones de la hoja de trabajo, entre otros.

Nuestro ejemplo muestra tres niveles, representados por los tres números (pueden crearse hasta un máximo de 8). Si haces clic en cada uno de ellos irás descubriendo las jerarquías. Cada nivel puede expandirse haciendo clic sobre su botón , o contraerse con el botón .

Observa la misma hoja anterior, pero presentando el segundo nivel, de modo que sólo muestre el total para cada Categoría:

El nivel 1 sería bastante más escueto:

76. Usa ‘Ejercicio 21.xls’ que tienes abierto y practica con los símbolos del esquema, expandiendo y contrayendo los distintos niveles.

Excel puede crear también esquemas de columna, o sea, que permite las agrupaciones jerárquicas por el contenido de los campos (columnas) de la lista. Creemos uno de este tipo.

Aunque pueden crearse manualmente, desde luego, el método más seguro y fiable de crear esquemas es dejar que lo haga Excel de manera automática. Pero antes de crearlos asegúrate de que todas las fórmulas de los subtotales son correctas y que son consistentes, es decir, que todas están o debajo o encima de los datos a los que hacen referencia, no pueden estar repartidas.


Creación automática

de esquemas



Como ya viste, Excel creó automáticamente un esquema al crear los subtotales sobre una lista, pero si quieres crearlos en cualquier otro momento, haz lo siguiente:

77. Sobre la misma hoja ‘Plantilla’ que tienes ya abierta, selecciona cualquier celda.

78. Desde la barra del menú principal, elige Datos-Agrupar y Esquema-Autoesquema. Excel analiza las fórmulas y crea el esquema.

79. Cuando la hoja ya tiene un esquema (como en este caso), el programa te preguntará si quieres modificarlo. Haz clic en para actualizarlo con el nuevo esquema.

Dependiendo de las fórmulas existentes y de la disposición de las filas y columnas, creará un esquema de filas, de columnas o mixto. En este caso es mixto:

Las operaciones básicas que puedes realizar con un esquema de columna son las mismas que para con un esquema de fila.


Y si llegado el momento, ya no necesitas trabajar con un esquema, puedes quitarlo acudiendo al menú Datos - Agrupar y esquema - Borrar esquema.

Eliminar un esquema

Excel expandirá todas las filas y columnas ocultas y cualquier resto de los controles de esquema desaparecerán. Eso sí, piénsatelo



bien antes de eliminarlo, porque aquí no hay marcha atrás y no se puede deshacer esta operación.

Quizás por esto, Excel te da la posibilidad de ocultar los símbolos del esquema, que tanto ocupan en la pantalla, pero sin eliminar el esquema. Y es que en nuestros ejemplos hemos trabajado con un máximo de tres niveles, pero imagínate lo que ocuparían en pantalla los controles de un esquema de ocho niveles.

Ocultar los controles de

esquema

Tienes dos maneras de ocultarlos (y de mostrarlos, pues son las mismas):

Desde el menú Herramientas – Opciones…, en la pestaña , desactivar la casilla Símbolos de esquema.

Pulsar la combinación de teclas + .

Cuando ocultas los controles de esquema, éste sigue activo y la hoja mostrará los datos al nivel del esquema actual, por lo que algunas filas o columnas estarán ocultas. Mira cómo quedaría nuestro ejemplo mostrando el segundo nivel del esquema y con los controles ocultos:

Echa una ojeada a las letras y los números de los encabezados de las columnas y de las filas, verás que existen ocultos.

5.7 Tablas dinámicas

Cuando trabajamos con enormes listas de datos, la información de la mayoría de detalles se nos queda oculta entre tantos valores. A veces nos sería útil elegir la información de manera que pudiéramos obtener datos muy selectivos del contenido de la hoja.

Imagínate una hoja de cálculo con las cifras de ventas de tus dos tiendas de deporte, en la que se recoge cada operación de venta



de artículos y qué vendedor la ha realizado. A medida que avanza la jornada, la información crece y crece, y así cada día, cada semana, cada mes,… ¿Te imaginas cuántas filas de datos tendría tu hoja al cabo de un año?

Ahora podrías preguntarte si desde esta hoja tan grande es posible obtener información de un vendedor en concreto, de un producto, de un periodo de tiempo, de… lo que se te ocurra. ¿Cómo obtener respuestas a estos interrogantes? Pues a través de las tablas dinámicas, mediante las cuales podrás disponer de una organización y resumen de los datos que precises. O sea, que una tabla dinámica no es más que un informe resumido generado a partir de un conjunto de datos, que puede ser desde una hoja de cálculo hasta una base de datos externa a Excel, y sin tener que crear ni una sola fórmula.

Tienen un pequeño inconveniente, a diferencia de los informes basados en fórmulas, las tablas dinámicas no se actualizan de manera automática cuando modificamos los datos que contienen (“… entonces ¿por qué las llamarán dinámicas?...”). Sin embargo no es tan grave, ya que sólo hay que pulsar un botón de su barra de herramientas para solucionarlo.

Veámoslo en la práctica.

81. Desde la carpeta , en el CD-ROM del curso, localiza y abre el libro ‘Tabla dinámica.xls’. Quedará activa su única hoja ‘Diario’.


Crear una tabla dinámica

No todas las hojas servirán para crear tablas dinámicas, pues es condición necesaria que, como en este ejemplo, estén estructuradas a modo de lista o base de datos de hoja (pero no



precisa estar ordenada), con sus campos (columnas), registros (filas) y fila de encabezamiento, con los nombres de los campos, claramente estructurados. También pueden crearse desde una base de datos externa, tal como te hemos dicho antes.

82. Selecciona cualquier celda de la lista y ejecuta Datos -Informe de tablas y gráficos dinámicos… Se abrirá el primer cuadro de diálogo del Asistente para tablas y gráficos dinámicos – paso 1 de 3 . Aquí le indicaremos dónde están los datos y qué queremos hacer con ellos.

Las opciones adecuadas del asistente suelen ser las que ya están seleccionadas, así que como el origen de los datos es una Lista o base de datos de Microsoft Office Excel y vas a crear una Tabla dinámica, tienes que:

83. Hacer clic en para avanzar al próximo paso del asistente y señalarle el rango de datos.

De nuevo el asistente ya te sugiere el rango correcto. Si no fuera así, corrígelo (debes incluir todos los datos de la lista y la fila del encabezado).

84. Pulsa para ir al tercer y último paso, y elegir dónde depositar el informe de tabla dinámica creado.

85. Déjalo en la primera opción (Hoja de cálculo nueva) y haz clic en el

botón .



Y es que será a partir de este momento cuando comiences a crear la tabla dinámica propiamente dicha. Hasta ahora sólo has construido un esqueleto de condiciones sobre el que depositar los elementos de la tabla. El asistente continuará echándonos una mano, mostrándonos la estructura final que tendrá la tabla:

Los campos del origen de los datos están situados en la parte derecha del cuadro de diálogo, a modo de botones. Lo que hay que hacer es arrastrar los botones de los campos de la derecha hacia la posición deseada en el interior de la tabla, en alguna de sus cuatro áreas: PAGINA, COLUMNA, FILA y DATOS.

86. Arrastra el botón de campo al área FILA, al área COLUMNA y al área DATOS.



¿Qué pretendemos con esto? Cada elemento que coloques en el campo FILA ocupará una fila de la tabla dinámica, de igual modo, los que coloques en el campo COLUMNA serán una columna, y en el campo DATOS situaremos aquellos con los queremos realizar los cálculos. Un salvedad, si en DATOS colocamos un campo numérico, por defecto el asistente les aplicará la función SUMA(), pero si es un campo no numérico les aplica la función CONTAR().

87. Pulsa y para ver el resultado de nuestra primera tabla dinámica:

En resumen, que hemos diseñado una estructura para que nos muestre los artículos en su parte izquierda, los empleados a modo de columnas y la cantidad de artículos vendidos por cada uno, sin aplicar ni una sola fórmula. Observa también que se han calculado los totales de los artículos vendidos y por vendedores. Acabas de descubrir que tu mejor vendedora es Conchita y que el producto más vendido es el Balón. Pero puedes seguir contestándote preguntas como: ¿quién ha vendido más bañadores? ¿Y más camisetas?

88. Dado que la tabla se ha creado en una hoja nueva, cámbiale el nombre a ‘Tabla dinámica’.

¿Y si quieres saber cómo son estos datos en cada una de tus tiendas? Tendríamos que diferenciar los datos de ‘Oviedo’ de los de ‘Gijón’. Entra en juego el área PAGINA.



Seguro que no te han pasado desapercibidos dos elementos que se habrán solapado con la tabla: la barra de herramientas Tabla dinámica y la Lista

de campos de tabla dinámica, que mostrará en negrita los campos que están siendo utilizados en la tabla actualmente. Te aconsejamos que los arrastres a donde no estorben, pero no los cierres.

89. En la Lista de campos de tabla dinámica, arrastra el campo Tienda hasta el área de PAGINA, situada en la parte superior de la tabla.

Ésta es otra manera de crear las tablas dinámicas, un poco más espartana, pero igual de efectiva, y que utilizarás cuando tengas que editar y modificar una tabla dinámica ya creada.

90. Ahora haz clic sobre cualquier celda libre ajena a la tabla, para que se oculte la Lista de campos de tabla dinámica y desaparezcan las líneas de color que delimitan las áreas de la tabla. Quedarán a la vista solamente la tabla y su barra de herramientas:

Cada campo colocado en la tabla dinámica presenta un botón de lista desplegable que te servirá para filtrar la información a mostrar seleccionando aquellos elementos que te interesen.

Si despliegas cualquiera de ellos, te mostrará la lista de todos sus elementos, sin repetición. Salvo las listas de los campos ubicados en el área PAGINA, que contienen opciones excluyentes, todas muestran cuadros de selección para elegir los elementos a visualizar.



91. Haz clic sobre el botón de lista del campo Tienda que acabas de colocar y selecciona una ciudad. Pulsa

para filtrar la información con ese criterio.

De nuevo vuelves a tener jugosa información de los datos de la tabla.

92. Salva el libro con el nombre de ‘Ejercicio 23.xls’

En fin, ésta es parte de la mecánica de construcción y funcionamiento de las tablas dinámicas. Si necesitas eliminar algún campo, haces clic sobre la zona gris del nombre y lo arrastras fuera de la tabla. Si haces clic sobre cualquier celda de la tabla, vuelves a recuperar la Lista de campos de tabla dinámica. Es posible también mover los campos de sitio simplemente arrastrándolos hacia otra posición.

Últimos consejos

Si modificas algún dato de la tabla original, debes actualizar la tabla dinámica con la opción Datos - Actualizar datos, desde el botón Actualizar datos de la barra de herramientas Tabla dinámica (siempre que el cursor esté en el interior de la tabla) o desde el menú contextual de cualquier celda. Pero si has añadido nuevas filas, tendrás que indicar el nuevo rango accediendo al paso 2 del asistente. Esto puedes hacerlo desde Datos – Asistente para tablas dinámicas y volviendo atrás un paso.

Una tabla dinámica es un objeto muy especial y no puedes manipularla como si de una tabla cualquiera se tratase. Por ejemplo, no podrás insertar una nueva fila o introducir fórmulas en celdas de la tabla. Cuando quieras manipular o aprovechar la tabla para otras operaciones, lo mejor es que te hagas una copia en otro sitio. Eso sí, el proceso de copia mejor lo haces desde Edición - Copiar y Edición - Pegado especial…, seleccionando la opción Valores. Nótese que la información copiada ya no está vinculada al origen de



datos, así que cualquier modificación en los datos originales no será reflejada en la tabla copiada.

No estaría nada mal que investigases las distintas opciones que te brinda la barra de herramientas Tabla dinámica. Desde ella podrás realizar operaciones de actualización, selección de campos, ocultar, resumir, agrupar, etc. Puedes practicar sin miedo los diferentes botones de la barra.

5.8 Gráficos dinámicos

Los gráficos dinámicos muestran la misma información que una tabla dinámica, pero de modo más visual, y disfrutando de las mismas opciones de filtrado y selección que ya usaste en las tablas dinámicas.

Siempre están asociados a una tabla dinámica y crearlos es tan sencillo como:


Crear un gráfico

dinámico 93. Accede al menú

contextual de cualquier celda de la tabla dinámica y selecciona la opción Gráfico dinámico, o usa el botón de la barra de herramientas Tabla dinámica.

Se insertará una nueva hoja con el equivalente gráfico de la tabla:



De nuevo dispones de los elementos Lista de campos de tabla dinámica, la barra de herramientas Tabla dinámica y la barra de herramientas Gráfico. Si todo está a tu gusto y no los necesitas, puedes cerrarlos para dejar libre el gráfico, pero si tienes que arrastrar algún campo al gráfico, éste es el momento, teniendo

presente que el esqueleto del gráfico está compuesto, al igual que la tabla dinámica, de las cuatro zonas: FILA (aquí campos de categoría), COLUMNA (aquí campos de serie), DATOS y PAGINA.

Cuentas también con todas las listas desplegables (Tienda, Artículo y Vendedor) para crear filtros y seleccionar, por ejemplo, todos o parte de

los artículos, o empleados, o tiendas, en definitiva, igual que has hecho en la tabla.

Todo lo que has estudiado acerca de los gráficos normales es aquí de aplicación. Tienes un buen aliado en la barra de herramientas Gráfico para modificar todos los parámetros que se te ocurran. Practica con este ejemplo:

94. Selecciona, desde la lista desplegable Tienda, una cualquiera de las ciudades, y desde la barra de herramientas Gráfico, en el botón Tipo de gráfico, el modelo Gráfico de columnas 3D.



95. Guarda el libro como ‘Ejercicio 24.xls’.

Recuerda que este tipo de gráfico está basado en los datos de la tabla dinámica y se actualiza automáticamente desde ella, pero no se actualizará cuando cambien los datos de la fuente original, en tanto no se actualice primero la tabla dinámica.

5.9 Tablas de datos

Excel es una extraordinaria herramienta para crear modelos dinámicos. Digamos que un modelo dinámico es un escenario que presenta unas fórmulas, a las que se les pasan valores, y que ofrecen un resultado inmediato. O sea, que en función de esos valores obtendremos unos resultados que nos permitirán realizar análisis hipotéticos sobre los datos, algo así como ¿qué pasaría si…?

96. Desde la carpeta , en el CD-ROM del curso, localiza y abre el libro ‘Crédito.xls’. Asegúrate de queda activa la hoja ‘Tablas’.


Sus orígenes

Se trata de una hoja de trabajo en la que se realizan una serie de operaciones orientadas a calcular el coste de un crédito personal. La hoja se encuentra dividida en dos secciones, claramente diferenciadas: las celdas de datos y las de resultados. Bastará con dar valores a las celdas de datos (rango C4:C6) y obtendremos resultados en las celdas de resultados (rango C9:C11). He aquí un escenario dinámico al que podríamos hacerle preguntas como:

¿Qué pasaría si el crédito fuera a 10 años? ¿Cuánto me ahorraría si el tipo de interés bajase al 6%? ¿Puedo permitirme una subida del tipo hasta el 10%?



Si cumplimentamos las celdas de datos con nuestras dudas, el programa nos responderá. Estamos realizando lo que los entendidos llaman un análisis hipotético manual. Aquí todas las respuestas precisarían que las anotásemos en otro sitio para luego contrastarlas, porque cada vez que hago una pregunta, me desaparecen los resultados de la pregunta anterior.

Por esta razón Excel nos presenta un nuevo recurso para el análisis hipotético, la tabla de datos, un rango de celdas que mostrará el valor de una o varias fórmulas en función de la variación de uno (tabla de datos de una entrada) o dos valores (tabla de datos de dos entradas).

Este tipo de tablas muestra el resultado de varias fórmulas en función de los valores de una celda (la celda de entrada). Es importante que en su construcción respetes el esquema general de este tipo de tablas:


Tabla de datos de

una entrada

Hagamos una:

97. Completa la hoja ‘Tablas’ con los valores siguientes: En F1 escribe ‘Pago Mensual’; en G1 escribe ‘Pago Total’ y en H1 escribes ‘Interés Total’. Estas celdas no son necesarias para la tabla, pero ayudarán a identificar mejor los resultados.

El objetivo de este ejemplo es crear una tabla que nos muestre los valores de las tres fórmulas (Pago mensual, Pago total e Interés total soportado) para varios tipos de interés, desde el 6%

Truco: Cuando tengas que escribir en dos renglones dentro de una celda, no podrás usar Enter (pues validarás la celda). Usa la combinación de teclas + Enter.



hasta el 10%, así que, teniendo en cuenta el esquema general para te tipo de tablas, complétala como sigue:

98. En el rango F2:H2 hareferencias, que es más cescribe la fórmula ‘=C9’, en G2 escribe ‘=C10’ y

es

y que poner las fórmulas (o ómodo y rápido), así que en F2

en H2 escribe ‘=C11’. Se mostrarán

dificará las (recuerda

pa

100. Para crear la tabla, selecciona el rango completo (en

los valores de las celdas origen.

99. En la columna E, rango E3:E11 pondremos los valores de la celda de entrada, es decir, aquella que mosu valor para resolver en cada caso las fórmuque estamos ante una tabla de una sola entrada).

El aspecto de la hoja podría ser algo parecido a:

(Los colores de fondo y del texto los hemos puesto nosotros ra resaltar los elementos, no aparecen al crear la tabla).

este caso E2:H11) y elige el comando Datos - Tabla. Aparecerá el cuadro de diálogo Tabla .

Aquí tienes que indicarle dónde encontrar la celda de entrada. Dado que los distiuna columnasiguiente:

101. Cumplimenta sólo el campo Celda de

ntos valores de la celda de entrada aparecen en de la tabla de datos, en lugar de en una fila, haz lo

entrada (columna):, en términos absolutos: $C$6, o por medio de su control .

102. Pulsa y Excel completará la tabla con los resultados apropiados.



No estaría mal que retocases un poco el formato de las celdas de datos, aplicándoles un formato moneda y con dos decimales, por pura estética:

Ahora, a través del resultado de esta tabla, puedes ver los valores calculados del préstamo para diferentes tipos de interés, de un solo vistazo y sin necesidad de escribir una fórmula para cada celda de datos.


Este tipo de tablas también te permite cambiar valores de dos celdas de entrada. Observa su esquema general:


Tabla de datos de

dos entradas

Aunque parezca similar a una tabla de una entrada, la tabla de dos entradas sólo puede mostrar los resultados de una única fórmula a un tiempo.

Aprovechando la hoja ‘Tablas’ que tienes abierta, vamos a crear una tabla de doble entrada, que manejará dos variables, el Tipo de interés y el Importe solicitado, de manera que mostrará los distintos pagos mensuales que se tendrían que realizar con los distintos valores de estas variables.



104. Para crear una tabla de dos variables, construye dos ejes, pon la primera variable en las columnas (Importe solicitado). En el rango F16 escribe ‘6000’, en G16 pones ‘12000’ y en H16 escribes ‘18000’. Aplícales formato moneda, con dos decimales.

105. Pon la segunda variable en las filas (Tipo de interés). Copia el rango anterior E3:E11 a E17:E25.

106. La única fórmula que maneja este tipo de tablas debe colocarse, según su esquema general, en la celda de intersección de la columna y fila de variables. En este caso, es la celda E16, así que escribe ‘=C9’ y aparecerá su valor.

107. Para crear la tabla, selecciona el rango completo (en este caso E16:H25) y elige el comando Datos - Tabla. Aparecerá el cuadro de diálogo Tabla .

108. Indica la referencia absoluta de la celda de la variable que aparece en la fila (Importe solicitado), en el campo

Celda de entrada (fila):, en este caso es $C$4.

109. Pon la referencia absoluta de la celda de la variable presentada en forma de columna (Tipo de interés) en el campo Celda de entrada (columna):, en este caso es $C$6 y pulsa .




5.10 Protección de la hoja de cálculo

111. Accede al libro ‘Ejercicio 15.xls’ y activa su hoja ‘Nota final’.

Este ejemplo de aplicación de una hoja de cálculo está pensado para que el usuario introduzca unos valores en determinadas celdas (remarcadas en rojo, en la figura), pero, por el contrario, dispone de otras muchas (zonas desenfocadas de la figura) que contienen las fórmulas de tratamiento de los datos, textos o referencias que no deberían ser modificadas, ni siquiera por error. De ahí que Excel ponga a nuestro alcance medios para proteger los distintos elementos del libro de trabajo contra accesos no autorizados.

La protección en Excel es definible a cuatro niveles: celda, hoja, libro y archivo. Todos ellos, a excepción del último, están pensados para que los usuarios puedan utilizar una hoja de cálculo con ciertas restricciones, normalmente referentes a la modificación de datos o de su aspecto general.

Lo normal para proteger algo es aplicarle un sistema de protección. Pues bien, en Excel es justo al revés, ya que, por defecto, todas las celdas de una hoja de cálculo están originalmente marcadas como bloqueadas. O sea que, lo que normalmente haremos será desbloquear aquellas partes que queramos desproteger para que el usuario pueda hacer sobre ellas lo que considere oportuno, normalmente introducir datos.


Protección de celda



112. Haz una selección múltiple de los rangos, no adyacentes, C4:E15, G4:I15, K4:M15 y C17:C19, correspondientes a las celdas que desprotegeremos.

113. Accede a la pestaña de su cuadro de

diálogo Formato de celdas , desde Formato - Celdas… o

+ .

114. Desmarca la opción Bloqueada y pulsa .

Dado que todas estas celdas contienen la fórmula ‘=ALEATORIO()*10’, que ha sido introducida para ahorrarnos teclear las notas de los alumnos en las prácticas que hemos realizado, Excel incorporará una marca de color verde en la esquina superior izquierda de cada celda (ésta es la manera por la que Excel te advierte de que estás haciendo algo no recomendable para la celda: en este caso desbloquear una celda con una fórmula). Así que dejemos la hoja lista para su uso:

115. Salvo el rango C17:C19, elimina el contenido de las celdas de todos los demás: C4:E15, G4:I15, K4:M15.

La pestaña del cuadro de diálogo Formato de celdas ofrece sólo dos opciones: Bloqueada y Oculta. La primera imposibilita la modificación del contenido y formato de una celda si ésta se ha señalado como bloqueada. La segunda evita que la barra



de fórmulas muestre su contenido, es decir, oculta las fórmulas introducidas en una celda. Pero todo ello no será efectivo hasta que se establezca una protección de hoja.

Esta acción activa la protección de las celdas marcadas como bloqueadas y ocultas, pero además permite configurar el grado de protección de la hoja.


Protección de hoja

116. Selecciona Herramientas - Proteger - Proteger hoja… y abrirás el cuadro de diálogo Proteger hoja .

Desde aquí podrás habilitar aquellas opciones que, a pesar de que la hoja esté protegida, quieras permitir al usuario, como por ejemplo: dar formato, insertar o eliminar filas, ordenar, etc.

117. En el grupo Permitir a los usuarios de esta hoja de cálculo:, marca sólo la opción Seleccionar celdas desbloqueadas.

118. Asegúrate de marcar la opción Proteger hoja y contenido de celdas bloqueadas y poner una contraseña (para el ejemplo usaremos ‘cursoexcel’).

119. Finalmente pulsa y confirma la contraseña en el nuevo cuadro de diálogo que aparece.

120. Ahora intenta hacer clic sobre cualquier celda del rango protegido. No podrás, pues ni siquiera es posible mover el puntero de celda activa fuera de las celdas desprotegidas. Avanza con las flechas de movimiento del cursor y verás que no podrás salir del

espacio desprotegido.

121. Introduce valores numéricos en los rangos desprotegidos y comprueba cómo toda la hoja sigue comportándose según lo previsto.



122. Guarda el libro como ‘Ejercicio 27.xls’ y no lo cierres.

Aumentemos la seguridad, pues puedes aplicar protección a un nivel superior: al libro completo.


Protección de libro 123. Selecciona el menú Herramientas - Proteger -

Proteger libro… para acceder al cuadro de diálogo Proteger libro .

Dispones aquí de dos opciones: Estructura y Ventanas. La primera se encarga de impedir cualquier tipo de modificación en las

hojas del libro en cuanto a reubicación, inserción, eliminación u ocultación de las mismas. Así por ejemplo, si el libro protegido consta de tres hojas de cálculo y quieres insertar una nueva, no podrás hacerlo. Si no seleccionas la opción Estructura, cualquier usuario podría suprimir una hoja, aún estando protegida.

En cuanto a la opción Ventanas, se encarga de impedir que el usuario altere el aspecto de la misma en cuanto a tamaño, posición, apertura o cierre.

124. Así pues, marca ambas opciones, indica la misma contraseña (‘cursoexcel’) y pulsa .

125. Intenta eliminar alguna de las hojas del libro.

Si has hecho bien las cosas, a cualquier usuario le será imposible si no conoce la contraseña, pues tendrá desactivadas las opciones de los menús relacionados.

Ésta es la opción de más alto nivel de protección, puesto que su finalidad es impedir el acceso no autorizado a un archivo de Excel, o bien que el acceso sea de sólo lectura y, por tanto, no modificable por el usuario, es decir, no podrá introducir datos, solamente consultarlos.


Protección de archivo

El acceso a estos parámetros sólo es posible al guardar el archivo, pero no con la opción Archivo - Guardar, sino a través del comando Archivo - Guardar como…. Veamos cómo:



126. Selecciona Archivo - Guardar como… para abrir el cuadro de diálogo Guardar como .

127. Despliega el menú de su botón Herramientas y elige la opción Opciones generales… para abrir el cuadro de diálogo Opciones para guardar .

Aquí puedes decidir si ponerle o no una Contraseña de apertura, lo que obligará a los usuarios a conocerla si quieren ver el libro. O una Contraseña de escritura, si quieres controlar que los usuarios puedan guardar los cambios realizados.

Y si, sin necesidad de contraseña, sólo quieres que puedan consultar el libro (sin posibilidad de hacer nada más con él), entonces deberías marcar la opción Se recomienda sólo lectura.

128. En este caso, pon la contraseña de apertura (‘cursoexcel’), pulsa y confírmala en el nuevo cuadro de diálogo que te mostrará.

129. Guarda el libro con el nombre ‘Ejercicio 28.xls’.Cierra Excel y todos los archivos que tengas abiertos. Vuelve a abrir el programa e intenta cargar el archivo para comprobar su protección.




La desprotección de cualquiera de los niveles se realiza desde las mismas opciones de menú, pero te exigirá siempre la contraseña.

Y esto es todo. Has llegado al final del curso, pero no de tu formación, que deberá tener esta experiencia formativa como punto de arranque para que aproveches, de manera más productiva, las posibilidades de la Hoja de cálculo en tus tareas.

Nuestro último consejo: cierra Excel y celebra, con moderación, este trascendental momento. ¡Enhorabuena!

funciones avanzadasaladinofg.com/files/download/capitulo_05.pdf · excel), aún necesitamos este...

Documents