38
MANUAL DE COMANDOS AVANZADOS DE EXCEL Sin entregar Combinar ficheros – M.S. Formatos condicionales – A.A. Como buscar celdas con formatos condicionales, copiar formatos, eliminar 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 Comandos Avanzados de Excel

Embed Size (px)

Citation preview

Page 1: Manual Comandos Avanzados de Excel

MANUAL DE COMANDOSAVANZADOS DE EXCEL

Sin entregar

Combinar ficheros – M.S. Formatos condicionales – A.A.Como buscar celdas con formatos condicionales, copiar formatos, eliminar formatos condicionales – M.Exportar / Importar – C.G.R

Parcialmente hechos Operaciones usando Excel como BD (Usando y sin usar formulario, haciendo consultas) – J.PMeter 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

Page 2: Manual Comandos Avanzados de Excel

MANUAL DE COMANDOS AVANZADOS DE EXCEL

Indice1 Creación de listas (Por Isaac Manzano)............42 Proteger hoja en Excel (por Manuel Saborido). 53 Archivos Incrustados y Vinculados ( por José Antonio Marin)......................................................74 Compartir ficheros ( por José Mª Ramírez Ruiz )

95 Base de datos ( por Javier Pan)......................116 Como crear una macro ( Por José Antonio Jaén Lobón)................................................................127 Macros Absolutas y Relativas (por J. A. Marin)158 Ejemplos de macros ( Por Miguel Valiente)....179 Como asignar una macro a una imagen y ejecutarla usando esta ( Por Isabel Rosado y Amanda Duran ).................................................1910Como asignarle un boton a una macro ( Por Isabel Rosado y Amanda Duran ).......................2011Como asignar distintas zonas de una imagen a distintas macros (Por zonas) (Por Miguel Troncoso)...........................................................2112Como integrar un nuevo menú en la barra de herramientas ( Por Juan de Dios Segovia)..........2413Funcion logica BUSCARV (Por Jesús Segovia).2614Validación (Por José Antonio Jaen ).................2715Usando la validación para crear listas desplegables (Por José Antonio Jaen )................2916Tabla Dinámica ( Por Daniel Sañudo).............31

2

Page 3: Manual Comandos Avanzados de Excel

1 Creación de listas (Por Isaac Manzano)

* HERRAMIENTASOPCIONESLISTAS 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 LUGAR UN DIA DE LA SEMANA . Y ARRASTRA HACIA ABAJO Y SALEN LOS SIGUIENTES.

3

Page 4: Manual Comandos Avanzados de Excel

2 Proteger hoja en Excel (por 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 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:

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

4

Page 5: Manual Comandos Avanzados de Excel

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.

5

Page 6: Manual Comandos Avanzados de Excel

3 Archivos Incrustados y Vinculados ( 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.

Aquí está la diferencia con sin icono (izquierda) e icono (derecha):

6

1

2

Page 7: Manual Comandos Avanzados de Excel

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 cambios desde/al “Excel”. La 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”.

7

Page 8: Manual Comandos Avanzados de Excel

4 Compartir ficheros ( por 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…

Paso 4. Seleccionamos “Permitir la modificación por varios usuarios a la vez” y aceptamos.

8

Page 9: Manual Comandos Avanzados de Excel

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.

9

Page 10: Manual Comandos Avanzados de Excel

5 Base de datos ( por Javier 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.

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

10

Page 11: Manual Comandos Avanzados de Excel

6 Como crear una macro ( Por 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.

11

Page 12: Manual Comandos Avanzados de Excel

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. Tambien se puede ejecutar desde herramientas, macro, macros y le damos ha ejecutar la macro verde.

12

Page 13: Manual Comandos Avanzados de Excel

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.

13

Page 14: Manual Comandos Avanzados de Excel

7 Macros Absolutas y Relativas (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:

- 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.

14

Page 15: Manual Comandos Avanzados de Excel

Realizamos las acciones que queremos grabar y paramos la grabacion y ya dispondremos de nuestras macros.

15

Page 16: Manual Comandos Avanzados de Excel

8 Ejemplos de macros ( Por 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

Page 17: Manual Comandos Avanzados de Excel

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 la pagina a horizontal.

18

Page 18: Manual Comandos Avanzados de Excel

9 Como asignar una macro a 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

Page 19: Manual Comandos Avanzados de Excel

10 Como asignarle un boton a 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 Formulario como que te indicamos a 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

Page 20: Manual Comandos Avanzados de Excel

11 Como asignar distintas zonas de una imagen a distintas macros (Por zonas) (Por Miguel Troncoso)

Insertamos una imagen.

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

21

Page 21: Manual Comandos Avanzados de Excel

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 a la autoforma y 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

Page 22: Manual Comandos Avanzados de Excel

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

Page 23: Manual Comandos Avanzados de Excel

12 Como integrar un nuevo menú en 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

Page 24: Manual Comandos Avanzados de Excel

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

Page 25: Manual Comandos Avanzados de Excel

13 Funcion logica BUSCARV (Por 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 Producto Cantidad1 Peras 1002 Manzanas 113 Naranjas 644 Limones 33

Paso 2: Y con esta tabla para que nos indique los datos que hemos buscado:

Código a buscar celda B11ProductoCantidad

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.

PRODUCTO: =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), 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 nos saldrán los resultados en la segunda tabla.

Código a buscar 2Producto ManzanasCantidad 11

Código a buscar 1Producto PerasCantidad 100

Código a buscar 3Producto NaranjasCantidad 64

Código a buscar 4Producto LimonesCantidad 33

26

Page 26: Manual Comandos Avanzados de Excel

14 Validación (Por José Antonio 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

Page 27: Manual Comandos Avanzados de Excel

Paso 3: en la pestaña de mensajes entrantes de la ventana validación 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

Page 28: Manual Comandos Avanzados de Excel

15 Usando la validación para crear listas desplegables (Por José Antonio Jaen )

Paso 1: Abrimos el programa Excel y creamos las filas que queremos 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 el rango donde estan puestos los elementos de mi lista (futura lista desplegable) .

29

Page 29: Manual Comandos Avanzados de Excel

Paso 3: Debajo de la celda validación tendrá que salir la lista que hemos creado

30

Page 30: Manual Comandos Avanzados de Excel

16 Tabla Dinámica ( Por 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 aparece, seleccionamos “LISTA O BASE DE DATOS DE MICROSOFT EXCEL” y “TABLA DINAMICA” y pulsamos siguiente.

31

Page 31: Manual Comandos Avanzados de Excel

Paso 3: En la siguiente pantalla seleccionamos la tabla, si no la hemos seleccionado antes

Paso 4: Y en la 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.

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

32

Page 32: Manual Comandos Avanzados de Excel

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