MANUAL DE COMANDOS AVANZADOS DE EXCEL
Sin entregar Combinar ficheros – M.S. Formatos condicionales – A.A. Como buscar celdas con formatos condicionales, copiar formatos, el iminar formatos condicionales – M. Exportar / Importar – C.G.R Parcialmente hechos Operaciones usando Excel como BD (Usando y sin usar formulario, haciendo consultas) – J.P Meter una macro y controles de otros programa en la barra de herramientas – J.S.
Mirar ademas para el examen La hoja “Ejercicios Funciones Logicas” y opcionalmente la hoja “Ejercicios BD” (puede servir de ayuda a entender las tablas dinamicas) que adjunto en el correo
1
MANUAL DE COMANDOS AVANZADOS DE EXCEL Indice 3 1 Creación de listas (Por Isaac Manzano).... ........ ........ ........ ........ ....... ...4 4 2 Proteger hoja ho ja en Excel (por Manuel Manue l Saborido) Sabori do)..... ........ .......5 5 3 Archivos Incrustados y Vinculados ( por José Antonio Marin)................................................... ..................................................................... ......................7 7 4 Compartir ficheros fi cheros ( por José Jo sé Mª Ramírez Ruiz Rui z ). . . .9 5 Base de datos ( por Javier Pan) Pan) .................................. ................................ ..11 11 6 Como crear una macro ( Por José Antonio Jaén Lobón)................................................. ........................................................... ................ ............ ............. ............. ......12 12 7 Macros Absoluta Absolutass y Relativas Re lativas (por José Jo sé A. Marin). 15 8 Ejemplos de macros m acros ( Por Miguel Migue l Valiente) Valiente )... ....... ....... ...... .......17 17 9 Como asignar una macro a una imagen y ejecutarla usando esta ( Por Isabel Rosado y Amanda Duran ). .19 10 Como asignarle un boton a una macro ( Por Isabel Rosado y Amanda Duran )................................ ....................................... ............. .......... ....20 20 11 Como asignar distintas zonas de una imagen a distintas macros ma cros (Por zonas) (Por (Po r Miguel Troncoso) Troncoso ). .21 12 Como integrar un nuevo menú en la barra de herramientas herramie ntas ( Por Juan de Dios Segovia).... ....... ...... ...... ...... ...... ...24 24 13 Funcion logica logic a BUSCARV (Por Jesús Jesú s Segovia)..............26 14 Validación Validac ión (Por José Antonio A ntonio Jaen )..... ........... ........... .......... ........ .....27 15 Usando la validación para crear listas desplegables (Por José Antonio Jaen ).................................. ......................................... ............. ......... ...29 29 16 Tabla Dinámica ( Por Daniel Sañudo)..... ......... ........ ........ ........ ....... ...31 31 2
3
1 Creació Creación n de de list listas as (Por (Por Isaac Isaac Manzano) * HERRAMIENTAS OPCIONES LISTAS PERSONALIZADAS
* NUEVA LISTA: ESCRIBE LOS ELEMENTOS DE TU LISTA Y LE DAS A AGREGAR
*LUEGO: PARA USAR UNA LISTA COMO POR EJEMPLO LA DE LOS DIAS DE LA SEMANA(AUNQUE ESTA LISTA YA ESTA CREADA COMO PREDEFINIDA EN EXCEL), TE VAS AL MICROSOFT EXCEL. PONES EN CUALQUIER CUALQUIER LUGAR UN DIA DE LA SEMANA SEMANA . Y ARRASTRA HACIA ABAJO Y SALEN LOS SIGUIENTES.
4
2 Protege Protegerr hoja hoja en Exce Excell (por (por Manuel Manuel Saborido) Lo primero que hay que hacer es seleccionar toda la hoja entera. Para ello nos situamos en la parte derecha superior de la tabla hasta que aparezca una cruz indicando que seleccionamos seleccionamos toda la tabla. De esta forma :
Luego con el botón derecho del ratón buscamos la opción de configuración de celdas y nos aparecerá algo asi:
5
Pues en la pestaña de proteger tendremos la imagen del dibujo de arriba, pues quitamos la opción de bloqueada y aceptamos. Una vez hecho esto, hacemos lo que queramos con nuestra hoja de Excel y luego seleccionamos las celdas que queremos proteger, para que no puedan ser borradas. Al igual que antes le damos al botón derecho del ratón y ponemos ahora en la opción de proteger la opción de bloqueada activada. Posteriormente, nos vamos a herramientas - proteger y se nos abrirá un nuevo menú diciendo que si queremos proteger la hoja, libro , etc. Pues seleccionamos la opción que más nos convenga. Se nos pedirá una contraseña que debemos escribir dos veces. Una vez hecho esto aceptamos y nuestra pagina quedara protegida. Para quitar esta protección le damos a herramientas y a la opción de desproteger y para llevar a cabo dicha opción debemos introducir la contraseña que le asignamos y entonces de desbloqueara las celdas protegidas.
6
3 Archiv Archivos os Incru Incrustad stados os y Vinc Vincula ulados dos ( por José Antonio Marin) En primer lugar nos vamos a la barra de herramientas Insertar objeto Crear de un archivo.
Nos sale una ventana en la cual podemos seleccionar si lo queremos vincular (1) (acceso directo) o copiar (si lo dejamos sin seleccionar). Tambien nos sale una opcion recomendable que es mostrarlo como icono (2), normalmente nos ensena todo el documento en el excel pero seleccionandolo nos muestra un icono y el documento se mostrara al pinchar sobre el icono.
7
Aquí está la diferencia con sin icono (izquierda) e icono (derecha):
Si no seleccionamos ninguna de estas casillas, una vez Finalizado esto ya tendremos el archivo incrustado (Por que no ha sido vinculado). Ahora debemos saber la diferencia entre Incrustar y Vincular. •
•
Incrustar: Crea una copia del archivo, por tanto los cambios que le hagas al archivo original no sufrirán sufrirán cambios desde/al “Excel”. La desventaja desventaja es que el archivo ocupará más. Vincular: Es crear un acceso directo del archivo, por tanto debemos tener los 2 archivos juntos para poderlos vincular, y los cambios del “real” le afectaran al vinculado a Excel. Se realiza de la misma forma solo que seleccionando la opcion “vincular”.
8
4 Comparti Compartirr fiche ficheros ros ( por por José José Mª Ramírez Ruiz ) Paso 1. Creamos una carpeta y la compartimos (Clic con el botón derecho en la carpeta -> Propiedades > Compartir > Compartir esta carpeta).
Paso 2. Creamos un documento Excel dentro de el, le ponemos por nombre por ejemplo “compartir ficheros” Paso 3. Seleccionamos Herramientas > Compartir libro…
9
Paso 4. Seleccionamos “Permitir la modificación por varios usuarios a la vez” y aceptamos.
6º. Y ahora desde otro PC podemos modificar lo que queramos del documento Excel creado. Todas las modificaciones de todos los usuarios se veran a la par en el documento.
10
5 Base Base de dato datos s ( por por Javie Javierr Pan) Pan) De forma sencilla podemos indicar que una base de datos no es más que un conjunto de información relacionada que se encuentra agrupada o estructurada. estructurada. Una base de datos es por ejemplo en una biblioteca ha de mantener listas de los libros que posee, de los usuarios que tiene, una clínica, de sus pacientes y médicos, una empresa, de sus productos, ventas y empleados Desde el punto de vista informático, una base de datos es un sistema formado por un conjunto de datos almacenados en discos que permiten el acceso directo a ellos y un conjunto de programas que manipulan ese conjunto de datos. Un gestor de base de datos es un programa que permite introducir y almacenar datos, ordenarlos y manipularlos. Excel puede ser usado como un rudimentario gestor de base de datos
11
6 Como crear crear una una macr macro o ( Por Por José José Antonio Jaén Lobón) Paso 1: Abrimos un documento de Excel, nos vamos a herramientas, macro, nueva macro.
Paso 2: En esa ventana que nos sale insertamos el nombre de la macro y el método abreviado que le queremos definir (Tecla rapida con la que se puede llamar a la macro), en guardar macro seleccionamos libro de macros personal (que sirve para que cuando queramos acceder a una macro esta esta guardada en un sitio donde se puede acceder desde todos los libros y hojas que queramos para poder ser utilizada por todos) y le damos a aceptar en esa ventana.
Paso 3: cuando estamos en la hoja de Excel grabamos una macro, por ejemplo, la que vamos ha hacer consiste en rellenar una celda de color verde, seleccionamos la celda que queremos rellenar en verde, cuando este rellena paramos la macro.
12
Paso 4: Vamos a probar la macro. En la celda que hemos rellenado en verde la ponemos sin relleno para ver si nuestra macro ha funcionado.
Paso 5: Le damos a control v que es el método abreviado que le hemos asignado a nuestra macro y la celda sin relleno se pone en verde esto es que nuestra macro esta correctamente hecha. hecha. Tambien se puede ejecutar ejecutar desde herramientas, macro, macros y le damos ha ejecutar la macro verde.
13
Para ver el codigo que tiene debajo esa macro, vamos al editor. Para ello nos vamos en la hoja de Excel a herramientas, macro, macros y en la macro que hemos creado le damos ha modificar.
Aquí podemos cambiar el tipo de color (cambiando en colorindex), pero tambien podemos hacer cosas como cambiar la fuente o el tamano de letra, etc.….los cambios que realizan en esa ventana deberán afectar a la macro.
14
7 Macros Macros Absol Absolutas utas y Relat Relativa ivas s (por (por José A. Marin) A la hora de realizar una macro se pueden realizar de dos formas diferentes: Absoluta: siempre le afectan los cambios a las mismas celdas (como la macro anterior verde, que en la grabacion incluia posicionarse en determinadas celdas y hacer cambios alli). Relativa: Le afectan los cambios a las celdas dependiendo cada vez de donde coloques el cursor o el rango elegido, esto es sobre las que hagamos selección, sea del lugar que sea. Se puede hacer, no eligiendo las celdas dentro de la ejecución de la macro sino antes de empezar a grabar. Pero la opcion mas facil es que se haga mediante un botón (que quita la referencia a una determinadas celdas) como veremos a continuación. Los pasos para realizarlos son los siguientes: siguientes: •
•
-
En primer lugar nos vamos a Herramientas Macros Grabar nueva macro.
-
En la ventana que nos sale podemos elegir la tecla de acceso abreviado, una descripción y el lugar donde queremos guardar la macro. Una vez aquí si queremos realizar una macro absoluta dejamos el boton desactivado, si queremos una macro relativa, lo dejamos seleccionado.
-
15
Realizamos las acciones que queremos grabar y paramos la grabacion y ya dispondremos de nuestras macros.
16
8 Ejempl Ejemplos os de de macro macros s ( Por Miguel Miguel Valiente) Macro Limpiar
Le damos a grabar nueva macros
Asignamos un nombre (limpiar) y un método abreviado (CTRL+l) desde Grabar macro. Grabamos la macro limpiando la parte pintada y le damos a parar.
17
Grabar: Igual que el anterior pero grabando los datos.
Cambiar impresora/pagina impresión:
Igual que las dos anteriores pero cambiando la impresora y configurando configurando la pagina a horizontal. 18
9 Como Como asign asignar ar una una macr macro o a una una imagen y ejecutarla usando esta ( Por Isabel Rosado y Amanda Duran ) Paso 1. Abrimos Excel y nos vamos a herramientas – macro – y grabar una nueva macro. Paso 2. Grabamos nuestra macro. Por ejemplo, cogemos el ratón y pulsamos el botón derecho – formato de celdas – tramas y elegimos el color que se desee. Paso 3. Detenemos la grabación de la macro y para ello nos vamos a herramientas – macro y detener grabación de macro. Paso 4. Insertamos una imagen y con el boton derecho le damos asignar macro Seleccionando la macro que hemos creado antes. Paso 5. Vamos a probar que funciona, para ello ponemos la hoja Excel blanca. Se selecciona la celda a color pulsamos sobre ella con el botón derecho – formato de celda – tramas – sin color. 6º. Ahora cuando pulsamos la imagen y sale una celda con el color que hemos elegido.
19
10 Como Como asign asignar arle le un boton boton a una una macro ( Por Isabel Rosado y Amanda Duran ) 1º. Para hacer un botón para una macro simplemente hacemos clic en el botón de la barra de herramientas herramientas Formulario como que que te indicamos a continuación continuación (Si no esta, ir a Ver-Barra de herramientas y seleccionarlo)
Nos saldrá una cruz en lugar de el puntero y lo hacemos al tamaño que queramos y le ponemos en el boton el nombre de el color que hemos puesto en nuestra macro anterior para colorear. Y por último comprobamos lo realizado seleccionando una celda y le damos al botón creado y nos tiene que salir de el color que le hayamos asignado y listo.
20
11 Como asignar asignar distint distintas as zona zonas s de una una imagen a distintas macros (Por zonas) (Por Miguel Troncoso) Insertamos una imagen.
21
Una vez insertada la imagen y, teniendo en cuenta que tenemos una o varias macros (que dan colores distintos a las celdas) hechas vamos a proceder a insertar una autoforma en forma de triángulo (que coincide con la forma de los quesitos).
La creamos de forma que cubra totalmente el triángulo azul, en este caso.
Le damos con el botón derecho derecho a la autoforma y seleccionamos seleccionamos Formato de autoforma. Dentro de Relleno en color pondremos “Sin relleno” y dentro de Línea en color pondremos “Sin línea” para que sea completamente invisible. 22
Ahora procederemos a asignarle la macro a la autoforma para que cuando seleccionemos la autoforma se ejecute la macro en donde estemos en ese momento. Para ello le daremos a la macro con el botón derecho y Asignar macro, ahora seleccionamos la macro deseada y le damos a aceptar.
Ahora cuando seleccionemos un rango de celdas por ejemplo y pulsemos sobre la partición azul el rango de celdas se pondrá azul. Funciona de manera similar con el resto de quesitos. 23
12 Como integr integrar ar un nuevo nuevo menú menú en la la barra de herramientas ( Por Juan de Dios Segovia) Paso 1: Con el Excel abierto, clic con el boton derecho a la barra de herramientas y después personalizar.
Paso 2: En la pestana comandos, menú de categorías, abajo del todo, seleccionamos nuevo menú y en el menú comando, aparece “Nuevo menú”, lo arrastramos hasta la zona de menus y lo colocamos en la posición que queramos. Este primer menú sera para organizar el resto de menus que estan asociados a macros
24
Paso 3: Después pulsamos en “Modificar selección” y en “Nombre” escribimos el nombre que queramos para el nuevo menú personalizado.
Paso 4: Después, volvemos a arrastrar un nuevo menú, pero esta vez dentro del menú que creamos anteriormente, dándole un nombre de la misma forma y después volvemos a pulsar en “Modificar selección” y a este le asignamos una macro pulsando sobre “Asignar macro”.
Paso 5: Ya solo tenemos que hacer clic en el nuevo menú y seleccionar la macro que queramos. 25
13 Func uncion ion logic logica a BUSCAR BUSCARV V (Por (Por Jesús Jesús Segovia) Paso 1: Cuando tengamos una tabla en la que queramos utilizar BUSCARV, ejemplo esta: TABLA FRUTAS (Definida como nombre en Insertar -> Nombre) Código 1 2 3 4
Producto Peras Manzanas Naranjas Limones
Cantidad 100 11 64 33
Paso 2: Y con esta tabla para que nos indique los datos que hemos buscado: Códi Código go a bus busca carr Producto Cantidad
celd celdaa B11 B11
Paso 3: Pues para empezar a utilizar la función BUSCARV, tenemos que escribir en la fila “Producto” y en la fila “Cantidad” los siguientes comandos. comandos. PRODUCTO: =BUSCARV(B11;FRUTAS;2), =BUSCARV(B11;FRUTAS;2), que busca el valor de la celda B11 en la tabla frutas, y en la fila que tenga el valor codigo igual a B11 se mira la columna 2. CANTIDAD: =BUSCARV(B11;FRUTAS;3), =BUSCARV(B11;FRUTAS;3), analogo pero con la columna 3. Paso 4: Ahora para probarla solo tendremos que introducir un número que aparezca en la columna “Código” de nuestra tabla, y automáticamente automáticamente nos saldrán los resultados en la segunda tabla. Código a buscar Producto Cantidad
2 Manzanas 11
Códi Códiggo a bus busccar 4 Producto Limones Cantidad 33
Código a bu buscar Producto Cantidad
Código a buscar Producto Cantidad
1 Peras 100
3 Naranjas 64
26
14 Validaci alidación ón (Por (Por José José Antoni Antonio o Jaen Jaen ) Como admitir solo un intervalo de valores. Ejemplo con la hora Paso1: Abrimos el programa Excel y creamos un documento nuevo.
Paso 2: En el documento de Excel, le damos a datos, validación. En la ventana validación le damos a permitir Hora y en la pestaña de configuración ponemos entre una hora y otra (de las 8:10 hasta las 14:30) que es la hora en la que se abre el instituto y se cierra.
27
Paso 3: en la pestaña de mensajes entrantes de la ventana validación ponemos ponemos un titulo “horario del instituto” y en el mensaje de entrada “el instituto esta abierto entre las 8:10 y las 14:30”. Esto es la información que vera el usuario cuando quiera rellenar ese campo.
Paso 4: en la pestaña de mensaje de error ponemos otra vez un titulo “horario del instituto” y en mensaje de error ponemos “el instituto esta cerrado”.
Paso 5: Entonces cuando ponemos una hora en la celda de las hora si no esta entre las horas puestas te mostrara el mensaje de error.
28
15 Usando Usando la la valid validació ación n para para crea crearr listas desplegables (Por José Antonio Jaen ) Paso 1: Abrimos Abrimos el programa Excel y creamos las filas que queremos que que pertenezcan a nuestra lista desplegable.
Paso 2: Nos vamos a datos, validación, en la pestaña configuración en el espacio permitir ponemos lista. Y en origen ponemos ponemos el rango donde estan puestos puestos los elementos de mi lista (futura lista desplegable) .
29
Paso 3: Debajo de la celda validación tendrá que salir la lista que hemos creado
30
16 Tabla Dinámic Dinámica a ( Por Por Daniel Daniel Sañudo) Paso 1: Después de abrir el Excel y de tener una tabla ya hecha, seleccionamos la tabla y pulsamos sobre el menú “DATOS” y después sobre “INFORME DE TABLAS Y GRAFICOS DINAMICOS”
Paso 2: En el asistente que nos nos aparece, seleccionamos seleccionamos “LISTA O BASE DE DATOS DE MICROSOFT EXCEL” y “TABLA DINAMICA” y pulsamos siguiente.
31
Paso 3: En la siguiente pantalla seleccionamos la tabla, si no la hemos seleccionado antes
Paso 4: Y en la l a ultima pantalla, seleccionamos “HOJA DE CALCULO NUEVA” por ejemplo (Esto es opcional)
Paso 5: Nos aparece un nuevo libro con un pequeño asistente para crear nuestra tabla dinamica.
32
Paso 6: En la pequeña ventana que aparece con todos los nombres de las columnas de la tabla, vamos arrastrando a las diferentes partes de la tabla dinámica como mejor nos convenga
Paso 7: Cuando hayamos terminado de colocar todas las columnas en su sitio, ya tendremos nuestra tabla dinámica terminada, ahora podremos configurarla a nuestro gusto y como mejor nos convenga
A fijarse – Esto sirve para hacer informes, y pedir información distinta cada vez. Salen totales para las distintas opciones de los campos.
33
