Download nivel 3 - Colegio Sagrado Corazón de Granada

NIVEL 3
Curso TICs
Colegio Sagrado Corazón Granada
Andrés Castillo Martín
Índice





Clonación de hojas
Fórmulas que relacionan distintas hojas y libros
Hipervínculos entre hojas y libros: botones
Insertar objetos OLE
Macros:
 Crear macros
 Librerías de macros en Internet
 Seguridad




Incrustación y exportación
Datos: Ordenar, filtros
Proteger: celdas, hojas y libros
Ejemplos
Curso TICs. Colegio Sagrado Corazón de Granada
2
Clonación de hojas I
La clonación de hojas es una solución ideal cuando
debemos crear hojas iguales, pero que nos permite
hacer modificaciones “masivas” en varias hojas a la
vez.
 La clonación debe realizarse una vez que el diseño
de la hoja, fórmulas, encabezamientos y formatos
los hemos dado por buenos.
 Para clonar una hoja, situamos el cursor sobre la
pestaña de la hoja, y con el botón derecho
seleccionamos la opción Mover o copiar. En la
nueva ventana emergente, seleccionamos la opción
Crear una copia, y elegimos donde queremos crear
la copia, que puede ser incluso en otro libro. Damos
Aceptar.

Curso TICs. Colegio Sagrado Corazón de Granada
3
Clonación de hojas II
Podemos realizar esta opción varias veces,
obteniendo varias hojas “clónicas”
 Una vez terminado, y cambiado el nombre a las
nuevas hojas de nuestro libro, podemos comprobar
cómo se puede hacer un cambio a la vez en varias
de las hojas “clonadas”
 Teniendo pulsada la tecla Control, podemos
seleccionar con el ratón varias pestañas de hojas a
la vez.
 Nos situamos en una celda cualquiera de una de las
hojas seleccionadas, y lo que cambiemos o
escribamos en esa celda, se hará en todas las que
hemos seleccionado simultáneamente.

Curso TICs. Colegio Sagrado Corazón de Granada
4
Fórmulas que relacionan distintas hojas y libros

Las fórmulas que se crean en Excel
pueden hacer referencia a celdas de la
misma hoja, de hojas del mismo libro, o
de libros distintos. La única precaución
que se ha de tener si utilizamos
referencias a libros distintos (libros
vinculados), es que el programa pueda
tener acceso a los demás libros cuando
queramos tener actualizados los datos.
Curso TICs. Colegio Sagrado Corazón de Granada
5
Hipervínculos entre hojas y libros: botones
Para tener organizado nuestros datos y mejorar la
presentación de nuestro libro, podemos crear una
primera hoja índice, y crear hipervínculos al resto de
las hojas.
 Para crear un hipervínculo, seleccionamos el texto
de una celda, botón derecho, opción Hipervínculo.
 Se abre una nueva ventana. Seleccionamos la
opción Vincular a: Lugar de este documento.
Seleccionamos la hoja y celda a la que queremos ir
cuando se pulse el hipervínculo. Como se ve, las
opciones son muy variadas.
 Para volver al Índice desde cualquier hoja, podemos
crear otro hipervínculo sobre una imagen o botón
insertado. Una vez creado el botón con hipervínculo,
se copia y pega en el resto de las hojas.

Curso TICs. Colegio Sagrado Corazón de Granada
6
Insertar objetos OLE

En Excel podemos insertar imágenes,
videos, audio y muchos otros objetos
OLE (Object Linking and Embedding).
Tecnología de Microsoft para compartir
información entre distintas aplicaciones
de Windows. Permite la creación de
documentos o programas incorporando
elementos de otros.
Curso TICs. Colegio Sagrado Corazón de Granada
7
Macros: definición
Las macros son un grupo de instrucciones programadas
bajo entorno VBA (visual Basic para aplicaciones), cuya
tarea principal es la automatización de tareas repetitivas y
la resolución de cálculos complejos.
 El lenguaje VBA, es un lenguaje de programación basado
en el visual Basic, enfocado a la realización de
programas sobre las herramientas Excel, Access,
Word...., mediante macros en VBA podemos crear
nuevas funciones para nuestras hojas Excel, personalizar
estilos y formatos, crear programas para la resolución de
cálculos complejos, automatizar tareas...
 Una vez realizado el código VBA, podemos ejecutar el
programa mediante la creación de botones, nuevos
menús... de tal forma que la aplicación creada resulte
intuitiva y de fácil manejo.

Curso TICs. Colegio Sagrado Corazón de Granada
8
Macros: Crear macros







Para crear una macro, una vez que tenemos claro la secuencia
de las distintas operaciones que vamos a automatizar, según
las versión del Office que tengamos, buscamos la opción
Macros, Crear Macros.
Se nos abre una nueva ventana donde se nos pide cambiar el
nombre por defecto, si queremos que la macro pueda ser
usada solo para este archivo o para todos, si queremos
asignarle una combinación de teclas, etc.
Pulsamos sobre la opción Grabar.
A partir de este momento, cada acción que realicemos sobre la
hoja de cálculo se estará grabando para reproducirse
automáticamente cada vez que ejecutemos la macro.
Una vez completada la secuencia, no olvidar Parar la macro.
La macro grabada la podemos ejecutar desde esta ventana de
macros, con la combinación de teclas, o asignándola a un
botón.
También la podemos editar en visual Basic para corregirla.
Curso TICs. Colegio Sagrado Corazón de Granada
9
Macros: bibliotecas

Algunas páginas web donde podemos
encontrar más información, o
colecciones de macros ya creadas son:
 http://www.webandmacros.com/macro_excel
_gestion_stocks.htm
 http://support.microsoft.com
Curso TICs. Colegio Sagrado Corazón de Granada
10
Macros: seguridad
Debido a que una macro no es más que un pequeño
programa en visual Basic que incrustamos dentro de
un programa como Excel, Word o PowerPoint, la
posibilidad de que pueda ser usado de forma
maliciosa como virus informático es lo que ha
llevado a que por defecto estos programas de la
suite Office no ejecutan las macros, salvo que
nosotros configuremos el nivel de seguridad para
que se puedan ejecutar.
 Dependiendo de la versión, esta opción está en
Menú en Macros, o la herramienta Macros Nivel de
seguridad, o en las versiones nuevas, Opciones
generales del programa, Seguridad.

Curso TICs. Colegio Sagrado Corazón de Granada
11
Incrustación y exportación

Excel puede ser exportado a otro programa de Office (objeto OLE), bien
como una imagen fija, o bien incrustado como algo que puede variar si los
datos de la hoja de cálculo cambian. Para ello será necesario que la hoja
que se incrustó esté disponible siempre que se quieran actualizar los datos
Notas de los alumnos
8,0
7,0
6,0
5,0
4,0
3,0
2,0
1,0
0,0
1ª evaluación
2ª evaluación
3ª evaluación
Media
Curso TICs. Colegio Sagrado Corazón de Granada
12
Datos: Ordenar
Aunque Excel no es una base de datos tipo Access
o SQL server, tiene funciones que permite manejar,
organizar, y gestionar datos.
 Una de las más fáciles y usadas es la de ordenar
datos según distintos criterios que podemos definir
(esto ha mejorado mucho en las actuales versiones)
 Una vez seleccionado el rango de celdas sobre las
cuales queremos realizar el cambio, en la barra de
Menús seleccionamos Datos, y dentro de ésta,
Ordenar.
 Se nos abre nueva ventana, en la cual
seleccionamos la columna en función de la cual
vamos a realizar el ordenamiento. Luego elegimos el
criterio, de menor a mayor, orden alfabético, etc.

Curso TICs. Colegio Sagrado Corazón de Granada
13
Datos: Filtros
Una opción más avanzada es la de insertar la
opción Filtro. Esta opción permite de forma
interactiva la de ordenar la tabla en función de
cualquiera de las columnas de nuestra tabla.
 Para insertar un filtro, seleccionamos la tabla
(podemos definir un nombre para el rango de
celdas) y en el menú Datos elegimos la opción
Filtro.
 En el encabezamiento de la tabla aparecerá
un pequeño icono que nos permite ordenar la
tabla en función de esa columna en concreto.

Curso TICs. Colegio Sagrado Corazón de Granada
14
Datos: Filtros
Curso TICs. Colegio Sagrado Corazón de Granada
15
Proteger: celdas, hojas y libros

Bloquear celdas:
 Esta opción permite evitar que una celda de
nuestra hoja pueda ser modificada. Esto suele
ser oportuno para evitar que accidentalmente o
por desconocimiento, una celda que contenga
fórmulas pueda ser borrada.
 Para realizar esta opción, nos colocaremos
sobre la celda en cuestión, botón derecho,
Formato de celdas, pestaña Proteger, marcar
Bloqueada.
 Una vez protegida la hoja, estas celdas no se
podrán cambiar, ni incluso seleccionar si así se
estima oportuno.
Curso TICs. Colegio Sagrado Corazón de Granada
16
Proteger: celdas, hojas y libros

Proteger hojas:
 Esta opción que también cambia de sitio en
función de la versión, permite bloquear los
cambios en una hoja concreta de nuestro
libro.
 Seleccionamos Proteger Hoja, se abrirá
nueva ventana, seleccionamos que tipo de
protección queremos realizar, y escribimos
una contraseña si así lo estimamos
oportuno. Se puede dejar sin ella.
Curso TICs. Colegio Sagrado Corazón de Granada
17
Proteger: celdas, hojas y libros

Proteger libro:
 Finalmente esta opción permite varios
niveles de protección sobre nuestro libro,
incluyendo la posibilidad de restringir solo
parte del libro, o solo determinadas
operaciones, e incluso su apertura. Las
versiones actuales permiten la encriptación
de los contenidos.
Curso TICs. Colegio Sagrado Corazón de Granada
18
Ejercicio resuelto y comentado
Se trata de una hoja de cálculo que se va
a utilizar para la realización de exámenes
tipo test con ordenadores.
 Tiene tres condicionantes fundamentales:

 Uno es que debe autocorregirse y dar la nota
 Debe ser el mismo para todos los alumnos,
pero al mismo tiempo debe evitar que sea fácil
copiarse.
 Debe ser seguro para evitar su manipulación.
Curso TICs. Colegio Sagrado Corazón de Granada
19
Ejemplo: Hoja Bienvenida
Curso TICs. Colegio Sagrado Corazón de Granada
20
Ejemplo: Hoja Examen
Curso TICs. Colegio Sagrado Corazón de Granada
21
Ejemplo: Hoja Cierre
Curso TICs. Colegio Sagrado Corazón de Granada
22
Ejercicio resuelto
Para resolver el primer punto, en una columna
se pondrá un 1 para la solución correcta, 0
para la incorrecta, y se puede poner -1 si se
considera necesario para evitar que se
conteste al azar. También otro valor si
queremos ponderar al valor de las preguntas.
 En una columna que estará oculta, Q,
crearemos la fórmula


=+SI(E2=1;G3;SI(E2=2;G4;SI(E2=3;G5)))
Esta fórmula condicional anidada es la que valora
la respuesta. Mira que contestación ha dado el
alumno y el valor de solución que le hemos dado
nosotros.
Curso TICs. Colegio Sagrado Corazón de Granada
23
Cálculo de la nota

=+SUMA(Hoja1!Q2:Q198)/20*10

Esta fórmula, que se encuentra alojada
en la hoja Datos, en la posición de celda
HH1000 (posición alejada para que no
pueda ser encontrada “curioseando”)
realiza la suma de las puntuaciones de
las preguntas acertadas y lo pasa a una
escala de 10. Nos da el resultado del
examen.
Curso TICs. Colegio Sagrado Corazón de Granada
24
Barajar las preguntas
Para resolver la segunda cuestión, vamos a
realizar una batería de preguntas, pueden ser
hasta 50 en nuestro ejemplo, pero podemos
hacer menos y ocultar las que no vayamos a
usar, que luego cambiaremos de orden para
cada alumno, de forma que todos van a
contestar a las mismas pero en distinto orden,
cosa que además ellos desconocen.
 Para ello asignaremos mediante la fórmula
aleatoria, un valor a cada pregunta, y
ordenaremos las preguntas mediante la
opción Ordenar Datos.

Curso TICs. Colegio Sagrado Corazón de Granada
25
Respuestas
correctas y valor
asignado a la
respuesta acertada
Curso TICs. Colegio Sagrado Corazón de Granada
Fórmulas que
van a permitir
“barajar” las
preguntas
26
Fórmula para “barajar” las preguntas




=+SI(ESBLANCO(F3);-1;ALEATORIO())
=+SI(ESBLANCO(F3);C2;ALEATORIO())
=+I2
=+J2
La fórmula condicional de arriba, que corresponde a la columna I, lo que
hace es asignar un valor negativo si la solución está vacía ( no hay
pregunta) para que al ordenarla siempre salga al final, y si no está vacía le
da un valor aleatorio (entre 0 y 1). Así “barajamos” las preguntas. Las
fórmulas de debajo hacen que todo el grupo de respuestas no se
desagrupen, tendrán el mismo número.
La segunda clave hace lo mismo, por seguridad de que no puedan
coincidir, y la tercera evita que el orden de las respuestas pueda cambiar.
Curso TICs. Colegio Sagrado Corazón de Granada
27
Tercera cuestión
Para resolver la cuestión de seguridad,
todo lo que son fórmulas, respuestas,
etc., serán protegidas mediante las
opciones de ocultar, bloquear celdas y
proteger hoja de Excel.
 Finalmente, para automatizar todo el
proceso, vamos a crear macros y se las
asignaremos a botones.

Curso TICs. Colegio Sagrado Corazón de Granada
28
Análisis de macros: 1ª
•Esta macro selecciona la hoja 1
•Rango de datos llamados “tabla” donde se
encuentran las claves de ordenamiento
(D1:Q101)
•Ordena los datos en función de las columnas I ,
J y K de manera descendente la primera y la
última, y ascendente la otra
Esta macro está
asociada al botón
Aceptar de la hoja
Datos
Finalmente, posiciona el curso en la
Celda E2 y bloquea la hoja
Esta macro es fácil de grabar por el usuario
Curso TICs. Colegio Sagrado Corazón de Granada
31
Contar y proteger
Una segunda macro nos va a permitir
“cerrar” y terminar el examen.
 Una vez ejecutada, cuando el alumno
de por terminado su examen, las hojas
de contestación y la de Datos con su
nota se ocultarán y se protegerán
mediante contraseña, para evitar la
“curiosidad” y que ahora puedan tratar
de copiarse entre ellos.

Curso TICs. Colegio Sagrado Corazón de Granada
32
Análisis de macros 2ª
Curso TICs. Colegio Sagrado Corazón de Granada
33
Corrección
Nosotros podremos abrir el libro y mostrar las hojas
ocultas mediante la contraseña que se escribió en la
macro2
 En la celda hh1000 se encontrará la nota del
examen
 Al guardar nuestra hoja de cálculo, lo haremos como
plantilla de Excel, de manera que nos sea más fácil
cargarlo en los ordenadores antes del examen.
 Se puede mejorar el archivo si además hacemos
que desaparezcan los menús, barras de
herramientas y demás, solo pantalla completa, para
que el alumno se centre en contestar las preguntas.

Curso TICs. Colegio Sagrado Corazón de Granada
34