Curso excel avanzado by JOSE MANUEL GARCIA

CONSOLIDACIÓN DE CONCEPTOS .................................................................... 4 TIPOS DE DATOS EN EXCEL ................................................................................................... 4 Valores Constantes ..................................................................................................................... 4 Números .................................................................................................................................. 4 Fecha y hora ............................................................................................................................ 5 Texto ........................................................................................................................................ 5 Fórmulas ...................................................................................................................................... 5 Funciones .................................................................................................................................... 7 Errores de datos .......................................................................................................................... 7

CARACTERÍSTICAS AVANZADAS DE EXCEL .................................................... 9 SELECCIÓN DE CELDAS .............................................................................................................. 9 FORMATO DE CELDAS .............................................................................................................. 10 Fuente ....................................................................................................................................... 10 Alineación .................................................................................................................................. 12 Bordes ....................................................................................................................................... 14 Rellenos ..................................................................................................................................... 15 Número ...................................................................................................................................... 16 Protección de celdas ................................................................................................................. 17 REFERENCIAS Y NOMBRES ...................................................................................................... 19 Referencias. .............................................................................................................................. 19 Referencia Relativa: .............................................................................................................. 19 Referencia Absoluta: ............................................................................................................. 19 Referencia Mixta:................................................................................................................... 19 Como cambiar el tipo de referencia ...................................................................................... 19 Referencias a otras hojas o libros ......................................................................................... 20 Nombres de celdas y rangos ..................................................................................................... 20 Tipos de nombres .................................................................................................................. 21 Ámbito de los nombres .......................................................................................................... 21 Definir y especificar nombres ................................................................................................ 22 Auditar nombres .................................................................................................................... 22 Reglas de sintaxis de los nombres........................................................................................ 22 Administrador de nombres .................................................................................................... 24 FORMATO CONDICIONAL .......................................................................................................... 25 VALIDACIÓN DE DATOS ............................................................................................................ 26 AUDITORÍA DE FÓRMULAS ....................................................................................................... 27 FUNCIONES ................................................................................................................................. 28 Insertar Funciones con el Asistente .......................................................................................... 28

ESQUEMAS Y VISTAS ........................................................................................ 31 INTRODUCCION A LOS ESQUEMAS Y VISTAS ....................................................................... 31 CÓMO CREAR ESQUEMAS ........................................................................................................ 31 Creación Automática de Esquemas .......................................................................................... 31 Creación Manual de Esquemas ................................................................................................ 32

CURSO EXCEL 2007 AVANZADO

ELIMINAR ESQUEMAS ............................................................................................................... 33 VISTAS .......................................................................................................................................... 33 Ver una Hoja en Varias Ventanas ............................................................................................. 33 Dividir una Hoja en Paneles ...................................................................................................... 34 Inmovilizar Paneles ................................................................................................................... 34

IMPORTAR DATOS .............................................................................................. 36 LA IMPORTACIÓN DE DATOS ................................................................................................... 36 IMPORTAR TEXTO ...................................................................................................................... 36 Utilizar el Asistente para Importar Texto ................................................................................... 36 LA SECCIÓN CONEXIONES ....................................................................................................... 38 IMPORTAR DATOS DE WORD 2007 A EXCEL 2007 ................................................................ 40 De Word a Excel........................................................................................................................ 40 De Excel a Word....................................................................................................................... 41 De Access 2007 a Excel 2007 .................................................................................................. 41

TABLAS DE DATOS ............................................................................................ 43 CREAR UNA TABLA .................................................................................................................... 43 MODIFICACIÓN DE DATOS EN UNA TABLA ............................................................................ 44 MODIFICAR LA ESTRUCTURA DE UNA TABLA ...................................................................... 45 ESTILOS DE TABLAS DE DATOS .............................................................................................. 46 ORDENAR UNA TABLA DE DATOS........................................................................................... 47 FILTRAR EL CONTENIDO DE UNA TABLA ............................................................................... 48 Autoflitro .................................................................................................................................... 49 Filtro avanzado .......................................................................................................................... 49 Definir Criterios de Filtrado ........................................................................................................ 50 RESUMEN DE DATOS ................................................................................................................. 50

TABLAS DINÁMICAS .......................................................................................... 52 CREACIÓN DE TABLAS DINÁMICAS ........................................................................................ 52 Eliminar Tablas Dinámicas ......................................................................................................... 53 FILTRAR TABLAS DINÁMICAS .................................................................................................. 53 PROMEDIOS................................................................................................................................. 54

PROGRAMACION Y MACROS ............................................................................ 56 INTRODUCCIÓN A LA PROGRAMACIÓN ................................................................................. 56 MACROS ....................................................................................................................................... 59 Crear una Macro Automáticamente .......................................................................................... 59 Crear una Macro Manualmente ................................................................................................. 61 Ejecutar una Macro ................................................................................................................... 62 Guardar Archivos con Macros ................................................................................................... 63

CURSO EXCEL 2007 AVANZADO

APENDICE A: FUNCIONES DE EXCEL .............................................................. 64 FUNCIONES DE BASE DE DATOS............................................................................................. 64 FUNCIONES DE FECHA Y HORA ............................................................................................... 65 FUNCIONES FINANCIERAS ........................................................................................................ 66 FUNCIONES DE INFORMACIÓN ................................................................................................ 69 FUNCIONES LÓGICAS ................................................................................................................ 69 FUNCIONES DE BÚSQUEDA Y REFERENCIA.......................................................................... 70 FUNCIONES MATEMÁTICAS Y TRIGONOMÉTRICAS ............................................................. 71 FUNCIONES ESTADÍSTICAS ...................................................................................................... 74 FUNCIONES DE TEXTO .............................................................................................................. 78

APENDICE B: EJEMPLOS DE MACROS ............................................................ 80

CURSO EXCEL 2007 AVANZADO

CONSOLIDACIÓN DE CONCEPTOS TIPOS DE DATOS EN EXCEL Valores Constantes Un Valor Constante, es un Dato que se introduce directamente en una Celda, puede ser un número, una fecha u hora, o un texto Números



Para introducir números podemos incluir los caracteres 0,1,2,3,4,5,6,7,8,9 y los signos especiales + - ( ) / % E, €.



Los signos (+) delante de los números se ignoran, y para escribir un número negativo éste tiene que ir precedido por el signo (-)



Al escribir un número entre paréntesis, Excel 2007 lo interpreta como un número negativo, lo cual es típico en contabilidad.



El carácter E o e es interpretado como notación científica. Por ejemplo, 3E5 equivale a 300000 (3 por 10 elevado a 5).



Se pueden incluir los puntos de miles en los números introducidos como constantes.



Cuando un número tiene una sola coma se trata como una coma decimal.



Si al finalizar un número se escribe €, Excel 2007 asigna formato Moneda al número y así se verá en la Celda, pero en la barra de Fórmulas desaparecerá dicho símbolo.



Si introducimos el símbolo % al final de un número, Excel 2007 lo considera como símbolo de porcentaje.



Si introduces fracciones, debemos de escribir primero un cero para que no se confundan con números de fecha.



Si un número no cabe en su Celda como primera medida se pasa automáticamente a anotación científica.



Por defecto los números aparecen alineados a la derecha en la Celda

CURSO EXCEL 2007 AVANZADO

Fecha y hora Para introducir una fecha u hora, no tienes más que escribirla de la forma en que deseas que aparezca Al igual que los números (ya que realmente lo son), las fechas y las horas también aparecen alineados a la derecha en la Celda

Texto  Para introducir texto como una constante, selecciona una Celda y escribe el texto. 

El texto puede contener letras, dígitos y otros caracteres especiales que se puedan reproducir en la impresora



Una Celda puede contener hasta 16.000 caracteres de texto



Si un texto no cabe en la Celda podemos utilizar todas las adyacentes que están en blanco a su derecha para visualizarlo, no obstante el texto se almacena únicamente en la primera Celda



El texto aparece, por defecto, alineado a la izquierda en la Celda

Fórmulas Una Fórmula es una secuencia formada por valores constantes, Referencias a otras Celdas, Nombres, Funciones, u Operadores Una Fórmula es una técnica básica para el Análisis de Datos. Podemos realizar diversas operaciones con los datos de las hojas de cálculo como *, +, -, Seno, Coseno, etc... En una Fórmula se pueden mezclar constantes, nombres, referencias a otras Celdas, operadores y Funciones La Fórmula se escribe en la barra de Fórmulas y debe empezar siempre por el signo =. Los distintos Tipos de Operadores que se pueden utilizar en una Fórmula son los siguientes:

1. Operadores Aritméticos, se emplean para producir resultados numéricos CURSO EXCEL 2007 AVANZADO

suma

Resta

Producto

División

Exponenciación

2. Operador Tipo Texto, se emplea para concatenar Celdas que contengan texto. El operador es el símbolo &

3. Operadores Relacionales, se emplean para comparar valores y proporcionar un valor lógico (verdadero o falso) como resultado de la comparación

Mayor que

Menor que

Mayor ó igual que

Menor ó igual que

Distinto a

4. Operadores De Referencia, indican que el valor producido en la Celda referenciada debe ser utilizado en la Fórmula. 5. Operador de rango indicado por dos puntos (:), se emplea para indicar un rango de Celdas. Ejemplo: C1:D8 6. Operador de unión indicado por una coma (,), une los valores de dos o más Celdas. Ejemplo: C1,D8 Cuando hay varias operaciones en una misma expresión, cada parte de la misma se evalúa y se resuelve en un orden determinado.Ese orden se conoce como Prioridad de los Operadores. Se pueden utilizar paréntesis para modificar el orden de prioridad y forzar la resolución de algunas partes de una expresión antes que otras

CURSO EXCEL 2007 AVANZADO

Las operaciones entre paréntesis son siempre ejecutadas antes que las que están fuera del paréntesis. Sin embargo, dentro de los paréntesis se mantiene la prioridad normal de los operadores. Cuando hay expresiones que contienen operadores de más de una categoría, se resuelve antes las que tienen operadores aritméticos, a continuación las que tienen operadores de comparación y por último las de operadores lógicos . Los operadores de comparación tienen todos la misma prioridad, es decir que son resueltos de izquierda a derecha, en el orden en que aparecen. Los operadores lógicos y aritméticos son resueltos en el siguiente orden de prioridad (de mayor a menor): Cuando hay multiplicación y división en la misma expresión, cada operación es resuelta a medida que aparece, de izquierda a derecha. Del mismo modo, cuando se presentan adiciones y sustracciones en una misma expresión, cada operación es resuelta en el orden en que aparece, de izquierda a derecha El operador de concatenación de cadenas de caracteres (&) no es realmente un operador aritmético pero es prioritario respecto a todos los operadores de comparación

Funciones Una Función es una Fórmula especial escrita con anticipación y que acepta un valor o valores, realiza unos cálculos con esos valores y devuelve un resultado 

Todas las Funciones tienen que seguimos una sintaxis y si ésta no se respeta Excel 2007 nos mostrará un mensaje de error.



Los argumentos o valores de entrada van siempre entre Paréntesis



Los argumentos pueden ser valores constantes (número o texto), Fórmulas o Funciones



Los argumentos deben de separarse por un punto y coma ";".

Errores de datos Cuando introducimos una Fórmula en una Celda puede ocurrir que se produzca un error. Dependiendo del tipo de error puede que Excel 2007 nos avise o no. Cuando nos avisa del error, aparece el siguiente Cuadro de Diálogo

CURSO EXCEL 2007 AVANZADO

Nos da una posible propuesta que podemos aceptar haciendo clic sobre el botón Sí o no aceptarla, utilizando el botón No Podemos detectar un error sin que nos avise cuando aparece la Celda con es símbolo en la esquina superior izquierda

Al hacer clic sobre el Símbolo aparecerá este Cuadro que nos permitirá saber más sobre el error

Si lo que queremos es comprobar la Fórmula para saber si hay que modificarla o no podríamos utilizar la opción Modificar en la barra de Fórmulas Si la Fórmula es correcta, se utilizará la opción Omitir error para que desaparezca el Símbolo de la esquina de la Celda. Puede que al introducir la Fórmula nos aparezca como contenido de la Celda #TEXTO, siendo TEXTO un valor que puede cambiar dependiendo del tipo de error:

#####

se produce cuando el ancho de una columna no es suficiente o cuando se utiliza una fecha o una hora negativa

#¡NUM!

cuando se ha introducido un tipo de argumento o de operando incorrecto, como puede ser sumar textos

#¡DIV/0!

cuando se divide un número por cero

#¿NOMBRE?

cuando Excel 2007 no reconoce el texto de la Fórmula

- #N/A

cuando un valor no está disponible para una Función o Fórmula

- #¡REF!

se produce cuando una referencia de Celda no es válida

- #¡NUM!

cuando se escriben valores numéricos no válidos en una Fórmula o Función

- #¡NULO!

cuando se especifica una intersección de dos áreas que no se intersectan

También en estos casos, la Celda, como en el caso anterior, contendrá además un símbolo en la esquina superior izquierda tal como:

CURSO EXCEL 2007 AVANZADO

CARACTERÍSTICAS AVANZADAS DE EXCEL SELECCIÓN DE CELDAS Existen una serie de combinaciones de teclas que nos pueden hacer los movimientos por la hoja de trabajo mucho más cómodos: MOVIMIENTO TECLADO Celda Abajo ............................................................... FLECHA ABAJO Celda Arriba ............................................................... FLECHA ARRIBA Celda Derecha ............................................................ FLECHA DERECHA Celda Izquierda .......................................................... FLECHA IZQUIERDA Pantalla Abajo............................................................ AVPAG Pantalla Arriba........................................................... REPAG Celda A1 ..................................................................... CTRL+INICIO Primera celda de la columna activa .......................... FIN FLECHA ARRIBA Última celda de la columna activa ............................. FIN FLECHA ABAJO Primera celda de la fila activa ................................... FIN FLECHA IZQUIERDA o INICIO Última celda de la fila activa .................................... FIN FLECHA DERECHA Existen también combinaciones de teclado para desplazarse entre hojas del mismo libro: MOVIMIENTO TECLADO Hoja Siguiente ............................................................ CTRL+AVPAG Hoja Anterior ............................................................ CTRL+REPAG Vamos a Profundizar en los Métodos de Selección, haciendo uso del Teclado y la selección de Varias Hojas de Cálculo. 1. Si queremos seleccionar un Rango de Celdas consecutivas, nos situamos sobre la primera Celda a seleccionamos y Pulsamos la TECLA F8 Observamos como en la Barra de Estado aparece el identificativo Ampliar Selección que nos indica que vamos a ampliar o extender nuestra Selección. Nos desplazamos con las flechas de dirección para ampliar la selección Una vez terminada la selección, Pulsamos la TECLA F8 para desactivar el modo Extender. 2. Si lo que queremos es seleccionar Varias Celdas no Consecutivas, seleccionamos el primer rango a seleccionamos, y pulsamos la tecla MAYUSCULAS y al mismo tiempo F8 Observamos como en la Barra de Estado aparece el identificativo Agregar a la Selección que nos indica que vamos a añadir o agregar a nuestra selección, hacemos Clic sobre las Celdas a añadir a la selección, hasta tener toda la selección deseada. Pulsamos las teclas MAYUSCULAS y F8 para desactivar el modo Agregar. 3. Para seleccionar una Columna entera, nos situamos en cualquier Celda de la columna a seleccionar, y pulsamos la tecla CTRL y al mismo tiempo BARRA ESPACIADORA.

CURSO EXCEL 2007 AVANZADO

4. Si lo que queremos es seleccionar una Fila entera, nos situamos en cualquier Celda de la Fila a seleccionamos y pulsamos la tecla MAYUSCULAS y al mismo tiempo BARRA ESPACIADORA. 5. Y Finalmente, para seleccionar todas las Celdas de la Hoja, nos situamos en cualquier Celda y pulsamos la tecla CONTROL+MAYUS y al mismo tiempo la BARRA ESPACIADORA.

FORMATO DE CELDAS Excel nos permite no solo realizar cuentas sino que también nos permite darle una buena presentación a nuestra hoja de cálculo resaltando la información más interesante, de esta forma con un solo vistazo podremos percibir la información más importante y así sacar conclusiones de forma rápida y eficiente. Por ejemplo podemos llevar la cuenta de todos nuestros gastos y nuestras ganancias del año y resaltar en color rojo las pérdidas y en color verde las ganancias, de esta forma sabremos rápidamente si el año ha ido bien o mal.

A continuación veremos las diferentes opciones disponibles en Excel 2007 respecto al cambio de aspecto de las celdas de una hoja de cálculo y cómo manejarlas para modificar el tipo y aspecto de la letra, la alineación, bordes, sombreados y forma de visualizar números en la celda.

Fuente Excel nos permite cambiar la apariencia de los datos de una hoja de cálculo cambiando la fuente, el tamaño, estilo y color de los datos de una celda. Para cambiar la apariencia de los datos de nuestra hoja de cálculo, podemos utilizar los cuadros de diálogo o la banda de opciones, a continuación te describimos estas dos formas, en cualquiera de las dos primero deberás previamente seleccionar el rango de celdas al cual se quiere modificar el aspecto:

Utilizando el cuadro de dialogo

CURSO EXCEL 2007 AVANZADO

Del cuadro de diálogo que se abre, Formato de celdas, haciendo clic sobre la pestaña Fuente, aparecerá la ficha. Una vez elegidos todos los aspectos deseados, hacemos clic sobre el botón Aceptar.

Conforme vamos cambiando los valores de la ficha, aparece en el recuadro Vista previa un modelo de cómo quedará nuestra selección en la celda. Esto es muy útil a la hora de elefir el formato que más se adapte a lo que queremos. A continuación pasamos a explicarte las distintas opciones de la ficha Fuente. Se elegirá de la lista una fuente determinada, es decir, un tipo de letra. Si elegimos un tipo de letra con el identificativo delante de su nombre, nos indica que la fuente elegida es True Type, es decir, que se usará la misma fuente en la pantalla que la impresora, y que además es una fuente escalable (podemos escribir un tamaño de fuente aunque no aparezca en la lista de tamaños disponibles) . Se elegirá de la lista un estilo de escritura. No todos los estilos son disponibles con cada tipo de fuente. Los estilos posibles son: Normal, Cursiva, Negrita, Negrita Cursiva. Dependiendo del tipo de fuente elegido, se elegirá un tamaño u otro. Se puede elegir de la lista o bien teclearlo directamente una vez situados en el recuadro. Observa como la opción activa es Ninguno, haciendo clic sobre la flecha de la derecha se abrirá una lista desplegable donde tendrás que elegir un tipo de subrayado. Por defecto el color activo es Automático, pero haciendo clic sobre la flecha de la derecha podrás elegir un color para la letra.

CURSO EXCEL 2007 AVANZADO

Tenemos disponibles tres efectos distintos: Tachado, Superíndice y Subíndice. Para activar o desactivar uno de ellos, hacer clic sobre la casilla de verificación que se encuentra a la izquierda. Si esta opción se activa, se devuelven todas las opciones de fuente que Excel 2007 tiene por defecto.

Alineación Las distintas opciones de la ficha son: Alineación del texto Horizontal: Alinea el contenido de las celdas seleccionadas horizontalmente, es decir respecto de la anchura de las celdas. Al hacer clic sobre la flecha de la derecha podrás elegir entre una de las siguientes opciones: General: Es la opción de Excel 2007 por defecto, alinea las celdas seleccionadas dependiendo del tipo de dato introducido, es decir, los números a la derecha y los textos a la izquierda. Izquierda (Sangría): Alinea el contenido de las celdas seleccionadas a la izquierda de éstas independientemente del tipo de dato. Observa como a la derecha aparece un recuadro Sangría: que por defecto está a 0, pero cada vez que se incrementa este valor en uno, la entrada de la celda comienza un carácter más a la derecha, para que el contenido de la celda no esté pegado al borde izquierdo de la celda. Centrar: Centra el contenido de las celdas seleccionadas dentro de éstas.

Derecha (Sangría): Alinea el contenido de las celdas seleccionadas a la derecha de éstas, independientemente del tipo de dato. Observa como a la derecha aparece un recuadro de Sangría: que por defecto está a 0, pero cada vez que se incrementa este valor en uno, la entrada de la celda comienza un carácter más a la izquierda, para que el contenido de la celda no esté pegado al borde derecho de la celda. Llenar: Esta opción no es realmente una alineación sino que que repite el dato de la celda para rellenar la anchura de la celda. Es decir, si en una celda tenemos escrito * y elegimos la opción Llenar, en la celda aparecerá ************ hasta completar la anchura de la celda. Justificar: Con esta opción el contenido de las celdas seleccionadas se alineará tanto por la derecha como por la izquierda. Centrar En La Selección: Centra el contenido de una celda respecto a todas las celdas en blanco seleccionadas a la derecha, o de la siguiente celda en la selección que contiene datos. Alineación del texto Vertical: Alinea el contenido de las celdas seleccionadas verticalmente, es decir, respecto de la altura de las celdas. Esta opción sólo tendrá sentido si la altura de las filas se ha ampliado respecto al tamaño inicial. Al hacer clic sobre la flecha de la derecha podrás elegir entre una de las siguientes opciones: Superior: Alinea el contenido de las celdas seleccionadas en la parte superior de éstas. Centrar: Centra el contenido de las celdas seleccionadas respecto a la altura de las celdas. CURSO EXCEL 2007 AVANZADO

Inferior: Alinea el contenido de las celdas seleccionadas en la parte inferior de éstas. Justificar: Alinea el contenido de las celdas seleccionadas tanto por la parte superior como por la inferior. Orientación: Permite cambiar el ángulo del contenido de las celdas para que se muestre en horizontal (opción por defecto), de arriba a abajo o en cualquier ángulo desde 90º en sentido opuesto a las agujas de un reloj a 90º en sentido de las agujas de un reloj. Excel 2007 ajusta automáticamente la altura de la fila para adaptarla a la orientación vertical, a no ser que se fije explícitamente la altura de ésta. Ajustar texto: Por defecto si introducimos un texto en una celda y éste no cabe, utiliza las celdas contiguas para visualizar el contenido introducido, pues si activamos esta opción el contenido de la celda se tendrá que visualizar exclusivamente en ésta, para ello incrementará la altura de la fila y el contenido se visualizará en varias filas dentro de la celda. Reducir hasta ajustar: Si activamos esta opción, el tamaño de la fuente de la celda se reducirá hasta que su contenido pueda mostrarse en la celda. Combinar celdas: Al activar esta opción, las celdas seleccionadas se unirán en una sola. Dirección del texto: Permite cambiar el orden de lectura del contenido de la celda. Se utiliza para lenguajes que tienen un orden de lectura diferente del nuestro por ejemplo árabe, hebreo, etc...

CURSO EXCEL 2007 AVANZADO

Bordes Excel nos permite crear líneas en los bordes o lados de las celdas.

Para cambiar la apariencia de los datos de nuestra hoja de cálculo añadiendo bordes, seguir los siguientes pasos: 

Seleccionar el rango de celdas al cual queremos modificar el aspecto.



Seleccionar la pestaña Inicio.



Hacer clic sobre la flecha que se encuentra bajo la sección Fuente.



En el cuadro de diálogo que se abrirá hacer clic sobre la pestaña Bordes.



Aparecerá el cuadro de diálogo de la derecha.



Elegir las opciones deseadas del recuadro.



Una vez elegidos todos los aspectos deseados, hacer clic sobre el botón Aceptar.



Al elegir cualquier opción, aparecerá en el recuadro Borde un modelo de cómo quedará nuestra selección en la celda.

CURSO EXCEL 2007 AVANZADO

A continuación pasamos a explicarte las distintas opciones del recuadro. 

Preestablecidos: Se elegirá una de estas opciones: o

Ninguno: Para quitar cualquier borde de las celdas seleccionadas.

Contorno: Para crear un borde únicamente alrededor de las celdas seleccionadas.

Interior: Para crear un borde alrededor de todas las celdas seleccionadas excepto alrededor de la selección.



Borde: Este recuadro se suele utilizar cuando no nos sirve ninguno de los botones preestablecidos. Dependiendo del borde a poner o quitar (superior, inferior, izquierdo,...) hacer clic sobre los botones correspondientes. ¡CUIDADO! Al utilizar los botones preestablecidos, el borde será del estilo y color seleccionados, en caso de elegir otro aspecto para el borde, primero habrá que elegir Estilo y Color y a continuación hacer clic sobre el borde a colocar.



Estilo: Se elegirá de la lista un estilo de línea.



Color: Por defecto el color activo es Automático, pero haciendo clic sobre la flecha de la derecha podrá elegir un color para los bordes.

Rellenos Excel nos permite sombrear las celdas de una hoja de cálculo para remarcarlas de las demás. Las opciones las encontramos en la pestaña RELLENO de la ficha FORMATO DE CELDAS.

CURSO EXCEL 2007 AVANZADO

Número Excel nos permite modificar la visualización de los números en la celda.

General: Visualiza en la celda exactamente el valor introducido. Es el formato que utiliza Excel por defecto. Este formato admite enteros, decimales, números en forma exponencial si la cifra no coge por completo en la celda. Número: Contiene una serie de opciones que permiten específicar el número de decimales, también permite especificar el separador de millares y la forma de visualizar los números negativos. Moneda: Es parecido a la categoría Número, permite especificar el número de decimales, se puede escoger el símbolo monetario como podría ser € y la forma de visualizar los números negativos. Contabilidad: Difiere del formato moneda en que alinea los símbolos de moneda y las comas decimales en una columna. Fecha: Contiene números que representan fechas y horas como valores de fecha. Puede escogerse entre diferentes formatos de fecha. Hora: Contiene números que representan valores de horas. Puede escogerse entre diferentes formatos de hora. CURSO EXCEL 2007 AVANZADO

Porcentaje: Visualiza los números como porcentajes. Se multiplica el valor de la celda por 100 y se le asigna el símbolo %, por ejemplo, un formato de porcentaje sin decimales muestra 0,1528 como 15%, y con 2 decimales lo mostraría como 15,28%. Fracción: Permite escoger entre nueve formatos de fracción. Cientifica: Muestra el valor de la celda en formato de coma flotante. Podemos escoger el número de decimales. Texto: Las celdas con formato de texto son tratadas como texto incluso si en el texto se encuentre algún número en la celda. Especial: Contiene algunos formatos especiales, como puedan ser el código postal, el número de teléfono, etc. Personalizada: Aquí podemos crear un nuevo formato, indicando el código de formato.

Protección de celdas Vamos a estudiar la protección de celdas disponible en Excel 2007 para no permitir la modificación dceldas por error o por no tener permiso para ello. Proteger celdas Además de la protección mediante contraseñas para los libros de trabajo, Excel2007 ofrece varias órdenes para proteger las celdas del libro. Para ello tenemos que realizar dos operaciones: la primera que consiste en proteger las celdas que no queremos que sufran variaciones, y la segunda que consisten proteger la hoja. Cuando una celda está bloqueada no podrá sufrir variaciones. Realmente por defecto todas las celda están protegidas o bloqueadas para que no sufran cambios, pero no nos damos cuenta ya que la hoja está protegida, para que realmente se bloqueen las celdas antes hay que proteger la hoja de cálculo. Para desbloquear las celdas que queremos variar en algún momento sigue los siguientes pasos: Seleccionar el rango de celdas que queremos desbloquear para poder realizar variaciones .

CURSO EXCEL 2007 AVANZADO

Si se activa la casilla Oculta, lo que se pretende es que la fórmula o el valor de la celda no se pueda visualizar en la barra de fórmulas. Las operaciones de la ficha Proteger no tienen efecto si no protegemos la hoja de cálculo, por lo tanto a continuación tendremos que realizar los siguientes pasos: Seleccionar la pestaña Revisar. Hacer clic sobre el botón Proteger hoja que se encuentra en la sección Cambios.

Aparecerá el cuadro de diálogo Proteger Dejar activada la casilla Proteger hoja y contenido de celdas bloqueadas para proteger el contenido de las celdas de la hoja activa. Activar las opciones deseadas de la casilla Permitir a los usuarios de esta hoja de cálculo para que no tenga efecto la protección para la modificación seleccionada y desactivarla para tener en cuenta la protección. Si queremos asignar una contraseña para que solamente pueda desproteger la hoja la persona que sepa la contraseña, escribirla en el recuadro Contraseña. Si hemos puesto contraseña nos pedirá confirmación de contraseña, por lo tanto tendremos que volver a escribirla y hacer clic sobre el botón Aceptar. A partir de ahora la hoja activa se encuentra protegida, por lo que no se podrán modificar aquellas celdas bloqueadas en un principio. Si queremos desproteger la hoja, volveremos a realizar los mismos pasos que en la protección, es decir: Seleccionar la pestaña Revisar. Hacer clic sobre el botón Desproteger hoja que se encuentra en la sección Cambios. Si habíamos asignado una contraseña nos la pedirá, por lo que tendremos que escribirla y hacer clic sobre el botón Aceptar. Si no había contraseña asignada, automáticamente la desprotege.

CURSO EXCEL 2007 AVANZADO

REFERENCIAS Y NOMBRES Referencias. Cuando trabajamos en Excel y más concretamente cuando hacemos usos de fórmulas y funciones casi es seguro que pongamos referencias a celdas o conjunto de celdas que no son propiamente la misma celda donde tenemos la formula. Las referencias son enlaces a un lugar, es decir, cuando en una formula escribimos =SUMA(A1;B1) nos estamos refiriendo a que sume el contenido de A1 y el contenido de B1. Existen 3 tipos de referencias: Referencia Relativa:

Las referencias de filas y columnas cambian si se copia la formula en otra celda, es decir se adapta a su entorno porque las referencias las hace con respecto a la distancia entre la formula y las celdas que forman parte de la formula. Esta es la opción que ofrece Excel por defecto. Referencia Absoluta:

Las referencias de filas y columnas no cambian si se copia la formula a otra celda, las referencias a las celdas de la formula son fijas. Referencia Mixta:

Podemos hacer una combinación de ambas referencias, podemos hacer que las filas sean relativas y las columnas absolutas o viceversa. Como cambiar el tipo de referencia Una opción para cambiar el tipo de referencia una vez sabemos distinguir entre los diferentes tipos de referencias que existen y la que más nos interesa en cada momento es hacerlo a mano. Las referencias relativas se escriben tal cual vemos la intersección de la celda con la columna y la fila (A2, B3, D1...) . Para que la referencia sea absoluta, es decir que sea fija, debemos anteponer a la columna y a la fila el signo $ ($A$2, $B$3, $D$1...) . Para las referencias mixtas como hemos dicho puede ser una mezcla entre relativa y absoluta por tanto pueden ser de este tipo ($A2, B$3, $D1...) . Otra opción, en lugar de escribirlo a mano es hacerlo cuando estemos editando la formula, en el

CURSO EXCEL 2007 AVANZADO

momento en el que se incluyan las celdas referenciadas podemos pulsar sobre la tecla F4 y vemos que va cambiando a los posibles tipos de referencias que podemos hacer con la celda. Referencias a otras hojas o libros Otra funcionalidad muy interesante de las referencias es la posibilidad de escribir referencias a celdas que se encuentran en otras hojas o incluso en otros libros. Referencia a otras hojas. Para hacer referencia a celdas de otras hojas debemos indicar el nombre de la hoja seguido del signo de exclamación y el nombre de la celda. Por ejemplo: Hoja2!A2 esta referencia está diciendo que coja la celda A2 de la hoja Hoja2. Si la hoja tuviera un nombre personalizado con espacios incluidos, la referencia sería de este modo 'Nombre de la hoja externa'!A2, habría que encerrar el nombre de la hoja entre comillas simples ' '. Referencia a otros libros. Para hacer referencia a celdas de otros libros debemos indicar el nombre del libro entre corchetes y el resto como acabamos de ver. Por ejemplo: '[presupuesto 2007]Hoja1'!B2 esta referencia indica que la celda se encuentra en el libro "Presupuesto 2007", en la Hoja1 y en la celda B2. Muy importante: Fíjense bien que al escribir una cadena de caracteres que incluya espacios debemos ponerlo siempre entre comillas simples ' '.

Nombres de celdas y rangos Si utiliza nombres, sus fórmulas serán mucho más fáciles de entender y mantener. Puede definir un nombre para un rango de celdas, una función, una constante o una tabla. Una vez que haya adoptado la práctica de utilizar nombres en su libro, podrá actualizar, auditar y administrar esos nombres con facilidad. Un nombre es una abreviación con significado que facilita la comprensión del propósito de una referencia de celda, una constante, una fórmula o una tabla que pueda ser difícil de comprender a primera vista. En la siguiente información se muestran ejemplos comunes de nombres y cómo pueden mejorar la claridad y la comprensión.

CURSO EXCEL 2007 AVANZADO

Tipos de nombres

Existen varios tipos de nombres que se pueden crear y utilizar. Nombre definido Nombre que representa el valor de una celda, un rango de celdas, una fórmula o una constante. Puede crear su propio nombre definido, aunque Microsoft Office Excel en ocasiones lo hace por usted, por ejemplo cuando establece un área de impresión. Nombre de tabla Nombre de una tabla de Excel, que es una recopilación de datos sobre un tema concreto que están almacenados en registros (filas) y campos (columnas). Excel crea un nombre de tabla de Excel predeterminado Tabla1, Tabla2, etc., cada vez que se inserta una tabla de Excel, pero puede cambiarle el nombre para que tenga más significado.

Ámbito de los nombres

Todos los nombres tienen un ámbito, ya sea una hoja de cálculo concreta (lo que también se denomina nivel de hoja de cálculo local) o el libro completo (también denominado nivel de libro global). El ámbito de un nombre es la ubicación dentro de la cual el nombre es reconocido sin cualificación. Por ejemplo: Si ha definido un nombre, como Presupuesto_AÑO08, y su ámbito es Hoja1, ese nombre, si no está cualificado, sólo se reconoce en Hoja1, pero no en otras hojas mientras no cuente con cualificación. Para utilizar un nombre de hoja de cálculo local en otra hoja de cálculo, puede cualificarlo si lo precede del nombre de la hoja de cálculo, como en el siguiente ejemplo: Hoja1!Presupuesto_AÑO08 Si ha definido un nombre, como Objetivos_Dept_Ventas, y su ámbito es el libro, ese nombre se reconocerá en todas las hojas de cálculo del libro, pero no en otros libros. Un nombre siempre debe ser único en su ámbito. Excel no permite que se defina un nombre que no sea único en su ámbito. Sin embargo, es posible utilizar el mismo nombre en diferentes ámbitos. Por ejemplo, puede definir un nombre, como BeneficioBruto, para los ámbitos Hoja1, Hoja2 y Hoja3 en el mismo libro. Aunque todos los nombres son iguales, cada uno es único en su ámbito. Podría hacer esto para asegurarse de que una fórmula que emplee el nombre BeneficioBruto siempre haga referencia a las mismas celdas en el nivel de hoja de cálculo local. Incluso es posible definir el mismo nombre, BeneficioBruto, para el nivel de libro global, aunque una vez más el ámbito es único. Sin embargo, en este caso puede producirse un conflicto de nombre. Para solucionarlo, Excel usa de forma predeterminada el nombre definido para la hoja de cálculo, ya que el nivel de hoja de cálculo local tiene prioridad sobre el nivel de libro global. Si desea anular la prioridad y utilizar el nombre del libro, puede eliminar la ambigüedad del nombre si le agrega el nombre del libro al principio como en el siguiente ejemplo: ArchivoLibro!BeneficioBruto

CURSO EXCEL 2007 AVANZADO

Puede anular el nivel de hoja de cálculo local para todas las hojas de cálculo del libro, con excepción de la primera, que siempre utiliza el nombre local si hay un conflicto de nombre y que no se puede anular. Definir y especificar nombres

Puede definir un nombre mediante: 

El cuadro Nombre de la barra de fórmulas nivel de libro para un rango seleccionado.



La creación de un nombre a partir de una selección Puede crear nombres cómodamente a partir de rótulos existentes de filas y columnas mediante una selección de celdas de la hoja de cálculo.



El cuadro de diálogo Nombre nuevo Es lo mejor que se puede utilizar cuando se desea más flexibilidad a la hora de crear nombres, por ejemplo para especificar un ámbito de nivel de hoja de cálculo local o para crear un comentario del nombre.

NOTA

Es la mejor forma de crear un nombre de

De forma predeterminada, los nombres utilizan referencias de celda absolutas.

Auditar nombres

También puede crear una lista de nombres definidos en un libro. Busque un área con dos columna en blanco en la hoja de cálculo (la lista incluirá dos columnas, una para el nombre y otra para una descripción de éste). Seleccione una celda que será la esquina superior izquierda de la lista. En la ficha Fórmulas, en el grupo Nombres definidos, haga clic en Utilizar en la fórmula, en Pegar y, a continuación, en el cuadro de diálogo Pegar nombres, haga clic en Pegar lista. Reglas de sintaxis de los nombres

A continuación se enumeran las reglas de sintaxis que necesita conocer para crear y modificar nombres.



Caracteres válidos El primer carácter de un nombre debe ser una letra, un carácter de subrayado (_) o una barra invertida (\). El resto de los caracteres del nombre pueden ser letras, números, puntos y caracteres de subrayado. NOTA No se pueden utilizar los caracteres en mayúsculas y minúsculas "C", "c", "R" o "r" como nombre definido, ya que todos ellos se utilizan como una abreviatura para seleccionar una fila o columna para la celda seleccionada al escribirlas en un cuadro de texto Nombre o Ir a.



Referencias de celda no permitidas celda, como Z$100 o R1C1.



Los espacios no son válidos Los espacios no están permitidos como parte de un nombre. Use el carácter de subrayado (_) y el punto (.) como separadores de palabra, por ejemplo Impuesto_Ventas o Primer.Trimestre.

Los nombres no pueden ser idénticos a una referencia de

CURSO EXCEL 2007 AVANZADO



Longitud del nombre



Distinción entre mayúsculas y minúsculas Los nombres pueden incluir letras en mayúscula y minúscula. Excel no distingue entre caracteres en mayúscula y minúscula en los nombres. Por ejemplo, si ha creado el nombre Ventas y luego crea otro nombre VENTAS en el mismo libro, Excel le pedirá que seleccione un nombre único.

Un nombre puede contener hasta 255 caracteres.

Seleccione la celda, el rango de celdas o las selecciones no adyacentes a los que desee poner un nombre. Haga clic en el cuadro Nombre en el extremo izquierdo de la barra de fórmulas.

Escriba el nombre que desea utilizar para referirse a su selección. Los nombres pueden tener hasta 255 caracteres de longitud. Presione ENTRAR.

Definir un nombre mediante una selección de celdas de la hoja de cálculo

Puede convertir rótulos existentes de filas y columnas en nombres. Seleccione el rango al que desea poner nombre, incluidos los rótulos de las filas y columnas. En el grupo Nombres definidos de la ficha Fórmulas, haga clic en Crear desde la selección.

En el cuadro de diálogo Crear nombres a partir de la selección, designe la ubicación que contiene los rótulos al activar la casilla Fila superior,Columna izquierda, Fila inferior o Columna derecha. NOTA

Un nombre creado mediante este procedimiento sólo hace referencia a las celdas que

contienen los valores y no incluye los rótulos existentes de filas y columnas.

CURSO EXCEL 2007 AVANZADO

Definir un nombre mediante el cuadro de diálogo Nombre nuevo

En el grupo Nombres definidos de la ficha Fórmulas, haga clic en Definir nombre.

En el cuadro de diálogo Nombre nuevo, en el cuadro Nombre, escriba el nombre que desea utilizar para su referencia. Para especificar el ámbito del nombre, en la lista desplegable Ámbito, seleccione Libro o el nombre de una hoja de cálculo del libro. De forma opcional escriba un comentario descriptivo de hasta 255 caracteres en el cuadro Comentario. Administrador de nombres

Use el cuadro de diálogo Administrador de nombres para trabajar con todos los nombres definidos y los nombres de tabla del libro. Por ejemplo, es posible que desee buscar nombres con errores, confirmar el valor y la referencia de un nombre, ver o modificar comentarios descriptivos o determinar el ámbito. También puede ordenar y filtrar la lista de nombres y agregar, cambiar o eliminar nombres de una ubicación con facilidad. Para abrir el cuadro de diálogo Administrador de nombres, en la ficha Fórmulas, en el grupo Nombres definidos, haga clic en Administrador de nombres.

CURSO EXCEL 2007 AVANZADO

FORMATO CONDICIONAL El Formato Condicional sirve para que dependiendo del valor de la Celda, Excel 2007 aplique un formato especial o no sobre esa Celda. El Formato Condicional suele utilizarse para resaltar errores, para valores que cumplan una determinada condición, para resaltar las Celdas según el valor contenido en ella, etc... Para aplicar un Formato Condicional a una Celda, seguimos los siguientes pasos. Seleccionamos la Celda a la que vamos a aplicar un Formato Condicional, nos desplazamos a la Pestaña Inicio y accedemos al Menú Formato Condicional.

Tenemos varias opciones, como resaltar algunas Celdas dependiendo de su relación con otras, o Resaltar aquellas Celdas que tengan un valor mayor o menor que otro. Utilizamos las Opciones Barras de datos, Escalas de color y Conjunto de Iconos para aplicar diversos efectos a determinadas Celdas. Por ejemplo la Opción Nueva Regla, permite crear una Regla personalizada para aplicar un formato concreto a aquellas Celdas que cumplan Determinadas Condiciones.

CURSO EXCEL 2007 AVANZADO

En este cuadro seleccionamos un Tipo de Regla. En el marco Editar una Descripción de Regla deberemos indicar las condiciones que debe cumplir la Celda y de qué forma se marcará. Si nos basamos en el Valor de la Celda podemos escoger entre varias opciones como pueden ser un valor entre un rango mínimo y máximo, un valor mayor que, un valor menor que y condiciones de ese estilo. Los valores de las condiciones pueden ser Valores Fijos o Celdas que contengan el valor a comparar. Si pulsamos sobre el botón Formato entramos en un cuadro de diálogo donde podemos escoger el formato con el que se mostrará la Celda cuando la condición se cumpla. El Formato puede modificar, el color de la fuente de la letra, el estilo, el borde de la Celda, el color de fondo de la Celda, etc. Si pulsamos sobre Aceptar se creará la regla y cada Celda que cumpla las condiciones se marcará. Si el valor incluido en la Celda no cumple ninguna de las condiciones, no se le aplicará ningún formato especial. Si pulsamos sobre Cancelar, no se aplicarán los cambios efectuados en el Formato Condicional.

VALIDACIÓN DE DATOS La Validación de Datos es muy similar al Formato Condicional, salvo que esta característica tiene una Función muy concreta y es validar el contenido de una Celda, pudiendo incluso mostrar un mensaje de error o aviso. Para aplicar una validación a una Celda, lo primero que debemos de hacer es seleccionar la Celda que queremos Validar, a continuación, nos desplazamos a la pestaña Datos, y hacemos clic en Validación de Datos.

CURSO EXCEL 2007 AVANZADO

En la sección Criterio de Validación indicamos la condición para que el Dato sea Correcto. Dentro de Permitir podemos encontrar Cualquier valor, Número entero, Decimal, Lista, Fecha, Hora, Longitud de texto y personalizada. Por ejemplo si elegimos Número entero, Excel 2007 sólo permitirá números enteros en esa Celda, si el usuario intenta escribir un número decimal, se producirá un Error. Podemos restringir más los valores permitidos en la Celda con la opción Datos, donde, por ejemplo, podemos indicar que los valores estén entre 10 y 20. Si en la opción Permitir, elegimos Lista, podremos escribir una Lista de Valores para que el usuario pueda escoger un valor de los disponibles en la lista. En el recuadro Origen, podremos escribir los distintos valores separados por ; (punto y coma) para que aparezcan en Forma de Lista. En la pestaña Mensaje de Entrada podemos introducir un mensaje que se muestre al acceder a la Celda. Este mensaje sirve para informar de qué tipos de datos son considerados válidos para esa Celda. En la pestaña Mensaje de Error podemos escribir el mensaje de error que queremos se le muestre al usuario cuando introduzca en la Celda un Valor Incorrecto.

AUDITORÍA DE FÓRMULAS Excel 2007 nos ofrece una serie de Herramientas de Auditorías de Fórmulas que pueden sernos de gran utilidad. Para acceder a estas Herramientas, nos desplazamos a la Pestaña Fórmulas. Aparecen una serie de opciones que describimos a continuación. : Analiza la Hoja de Trabajo actual en busca de Errores.

: Dibuja unas Flechas indicando dónde están las Celdas involucradas en la Fórmula. : Dibuja Flechas indicando a qué Fórmula pertenece la Celda seleccionada, si es que pertenece a alguna Fórmula. : Elimina las Flechas indicativas de Celdas creadas con Rastrear dependientes o Rastrear precedentes.

CURSO EXCEL 2007 AVANZADO

: Abre el cuadro de diálogo de Evaluar Fórmula para que pueda ver la Fórmula y los resultados de la Fórmula de la Celda activa.

: Muestra la ventana de inspección para hacer un seguimiento del valor de las Celdas.

FUNCIONES Insertar Funciones con el Asistente Una Función como cualquier dato se puede escribir directamente en la Celda si conocemos su sintaxis, pero Excel 2007 dispone de una ayuda o asistente para utilizarlas, así nos resultará más fácil trabajar con ellas. Nos situamos en la Celda donde queremos introducir la Función, hacemos Clic en la pestaña Fórmulas, y elegimos la opción Insertar Función .

También podemos hacer clic sobre el botón cuadro de diálogo Insertar Función.

de la Barra de Fórmulas. Aparece el siguiente

Excel 2007 nos permite buscar la Función que necesitamos escribiendo una breve descripción de la Función en el recuadro Buscar una Función. CURSO EXCEL 2007 AVANZADO

A Continuación, hacemos Clic sobre el Botón Ir De esta forma no es necesario conocer cada una de las funciones que incorpora Excel 2007, porque nos mostrará en el cuadro. Seleccionamos una Función, las funciones asociadas a la descripción escrita. Para que la lista de Funciones no sea tan extensa podemos seleccionamos previamente una categoría del cuadro combinado Seleccionar una Categoría.

De esta manera, en el cuadro de lista sólo aparezcan las Funciones de la categoría elegida y reduzca por lo tanto la lista. Si no estamos muy seguros de la categoría podemos elegir Todas En el cuadro de lista Seleccionamos una Función elegimos la Función que deseamos haciendo clic sobre ésta. Conforme seleccionamos una Función, en la parte inferior nos aparecen los distintos argumentos y una breve descripción de ésta. También disponemos de un enlace Ayuda sobre esta Función para obtener una descripción más completa de dicha Función. Justo por debajo de la barra de Fórmulas nos aparece el cuadro de diálogo Argumentos de Función. Introducimos los Argumentos de la Función, este cuadro variará según la Función que hayamos elegido.

En el recuadro Número 1, indicamos el Primer argumento que generalmente será una Celda o Rango de Celdas. Para ello, hacemos Clic sobre le Botón

, y seleccionamos el rango de

Celdas o la Celda deseadas como Primer Argumento. Hacemos Clic con el botón izquierdo de l ratón sobre la primera Celda del rango y sin soltar el botón lo arrastramos hasta la última Celda del Rango. Pulsamos INTRO para volver al Cuadro de Diálogo. En el recuadro Número 2, indicamos el segundo argumento . (Sólo en caso de que existiera). Si introducimos segundo argumento, aparecerá otro recuadro para el tercero, y así sucesivamente. CURSO EXCEL 2007 AVANZADO

Cuando tengamos introducidos todos los argumentos, hacemos Clic sobre el botón Aceptar. Si insertáramos una fila en medio del rango de una Función, Excel 2007 expande automáticamente el rango incluyendo así el valor de la Celda en el rango.

En la pestaña Inicio o en la de Fórmulas encontrarás el botón nos permite realizar la Función SUMA de forma más rápida.

Autosuma que

Con este botón tenemos acceso también a otras Funciones utilizando la flecha de la derecha del botón. Podremos utilizar otra Función que no sea la Suma , como puede ser :    

Promedio, que calcula la Media Aritmética Cuenta, que Cuenta los Valores Máx, que obtiene el Valor Máximo Mín, que obtiene el Valor Mínimo

Al final del manual se comentan funciones de uso común en excel.

CURSO EXCEL 2007 AVANZADO

ESQUEMAS Y VISTAS INTRODUCCION A LOS ESQUEMAS Y VISTAS Un esquema podríamos definirlo como un resumen preciso que refleja los conceptos más importantes o de ma yor trascendencia del documento esquematizado. Antes de Crear un esquema debemos tener en cuenta algunas características.    

Debemos asegurarnos de que los datos sean apropiados para crear un esquema. Los datos apropiados para crear un esquema deben tener una jerarquía o disponer de una estructura por niveles En una hoja solo podemos incluir un esquema Para tener más de un esquema sobre los mismos datos, debemos copiar los datos a otra hoja.

Existen dos formas de crear un esquema en Excel 2007 Manual y Automática.

CÓMO CREAR ESQUEMAS Creación Automática de Esquemas La mejor opción para crear esquemas es que lo haga Excel 2007 automáticamente, puesto que tarda mucho menos tiempo que haciéndolo manualmente. Debemos de tener en cuente, unos requisitos previos para que Excel 2007 pueda crear automáticamente el esquema:  

Las filas de Sumario, deben estar por encima o por debajo de los datos, nunca entremezclados. Las columnas de Sumario , deben estar a la derecha o a la izquierda de los datos, nunca entremezclados.

CURSO EXCEL 2007 AVANZADO

Para Crear el Esquema, nos situamos sobre una Celda cualquiera y seleccionamos la opción Autoesquema del menú Agrupar que se encuentra en la pestaña Datos.

Automáticamente, Excel 2007 nos genera los niveles del esquema. En la esquina superior izquierda nos podemos ver unos números que nos indican cuántos niveles tiene el esquema. Para comprimir y expandir el esquema sólo tenemos que hacer clic en los símbolos - y + de cada nivel.

Creación Manual de Esquemas Una vez que hemos visto cómo crear un Esquema Automáticamente, vamos a ver cómo crearlo manualmente. Para crear un esquema manualmente debemos crear grupos de filas o de columnas dependiendo del ti po de esquema que queramos hacer. Para crear un grupo debemos seleccionamos las filas (seleccionando los números de las filas) o columnas (seleccionando las letras de las columnas) de las cuales vayamos a crear un Grupo. No debemos incluir las filas o columnas que contengan Fórmulas de sumario, una vez seleccionadas las filas o columnas vamos al menú Agrupar de la pestaña Datos, seleccionamos Agrupar . Para trabajar más rápido podemos utilizar los botones de agrupar y desagrupar.

CURSO EXCEL 2007 AVANZADO

ELIMINAR ESQUEMAS Ahora vamos a ver los pasos, para Borrar un Esquema. Para borrar un esquema debemos acceder a la opción Desagrupar, seleccionamos la opción Borrar esquema. Antes de eliminar un esquema , debemos estar seguros, pues una vez que lo hacemos, Excel 2007 no nos ofrece la posibilidad de deshacer, si no estamos seguros de querer eliminar el esquema es mejor ocultarlo. Al ocultarlo un esquema, lo que hacemos es ocultar las marcas que nos ayudan a expandir/contraer un esquema, pero el esquema sigue estando Activo. Para ocultar un esquema basta con presionar las teclas CONTROL+8 y automáticamente desaparecen las marcas del esquema, para volverlas a mostrar volvemos a Pulsamos la combinación de teclas CONTROL+8.

VISTAS Ver una Hoja en Varias Ventanas Al ir realizando hojas de cálculo, a veces vamos ampliando cada vez más el campo de visión de la pantalla, llegando a ocupar más de una Página por hoja. Cuando estamos comprobando Datos resulta bastante incómodo tener que desplazarse cada vez de arriba hacia abajo o de un lado al otro. Podemos utilizar la opción de ver la misma hoja en varias ventanas, o utilizar la opción de ver la misma hoja en varios paneles. Para ver la misma hoja en varias ventanas, debemos acceder a la pestaña Vista y seleccionar Nueva ventana,

En la Barra de Título podemos ver que ahora el nombre del documento además tiene añadido al final ":2" indicando que es la segunda ventana del mismo documento. Para ver las dos ventanas al mismo tiempo hacemos clic en Organizar Todo

CURSO EXCEL 2007 AVANZADO

Seleccionamos Vertical, Horizontal, Mosaico o Cascada, dependiendo de como estén situados los datos en la hoja.

Dividir una Hoja en Paneles Podemos utilizar también la opción de Dividir la Hoja por Paneles. Para dividir la hoja en paneles podemos hacer clic en el botón Dividir en la pestaña Vista. Automáticamente nos aparecen dos barras, una vertical y otra horizontal las cuales podemos desplazar par a ajustar el tamaño de las ventanas. Otra opción para definir los paneles y dividir la pantalla, es utilizar las Barras de División, tenemos dos Barras Disponibles 1. La Barra de División Horizontal que aparece en la barra de Desplazamiento Vertical. Al situar el puntero del ratón sobre la línea gris que está encima de la flecha de desplazamiento el puntero del ratón toma el aspecto de dos Flechas, desplazamiento hacia arriba y hacia abajo.

indicando el

2. La Barra de División Vertical que aparece en la barra de desplazamiento horizontal a la derecha del todo Al situar el puntero del ratón sobre la línea gris vertical que está a la derecha de la flecha de desplazamiento, el puntero del ratón adopta la forma de dos

Flechas izquierda.

, indicando el desplazamiento de la barra hacia la derecha y hacia la

Inmovilizar Paneles También podemos utilizar la opción de Inmovilizar los paneles. Si lo que queremos es dejar inmóviles las cabeceras de los datos y así desplazarnos únicamente sobre los datos teniendo siempre disponible la vista de las cabeceras, utilizamos la opción de Inmovilizar los Paneles. Desplegamos el menú Inmovilizar Paneles que se encuentra en la pestaña Vista. Si queremos mantener la primera fila (como cabecera) o la primera columna (para que ejerza la misma función) seleccionamos esta Opción. Seleccionamos el Rango de Celdas, pulsamos la opción Inmovilizar paneles , ahora la zona seleccionada podrá desplazarse, el resto permanecerá Inmovilizado.

CURSO EXCEL 2007 AVANZADO

Para desactivar la Inmovilizaci贸n de los Paneles, seleccionamos Inmovilizar Paneles, y hacemos clic sobre Movilizar Paneles.

CURSO EXCEL 2007 AVANZADO

IMPORTAR DATOS LA IMPORTACIÓN DE DATOS En muchas ocasiones tenemos la necesidad de trabajar en Excel 2007 con datos procedentes de otras aplicaciones. Para poder realizarlo en el menor ti empo posible, podemos utilizar algunas de las herramientas disponibles en Excel 2007 para importar datos. El problema fundamental de la importación de datos externos, es el formato de los propios archivos, porque que cada aplicación genera un archivo con un formato propio para identificar mejor el contenido de los Datos. Excel 2007 genera un archivo .xls, que no solamente guarda los datos que hemos introducido, sino que lo guarda con un formato especial para interpretar el documento. De esta manera sabe exactamente dónde están las Fórmulas, qué Formato Estético tiene el Documento, etc. Al importar Datos de una Aplicación Externa a Excel 2007, debemos tener en cuenta que pueden surgir los siguientes fallos:   

Algunas de las Fórmulas no ajustan correctamente. El formato no se ajusta al Original. Las Fórmulas y Funciones que no entiende no las copia.

IMPORTAR TEXTO Utilizar el Asistente para Importar Texto Al referirnos a Archivos de Texto nos estamos refiriendo a Archivos que no tienen formato, los denominados como archivos de texto plano (ASCII). Los Archivos de Texto sin Formato (ASCII) , se importan de manera totalmente distinta que los Archivos de Texto con formato como los Archivos de Word 2007 (.docx) o los (.rtf). Para importar archivos de texto c on el asistente, vamos a ver los pasos necesarios.

Nos desplazamos al de Botón Office

y seleccionamos Abrir .

CURSO EXCEL 2007 AVANZADO

En la Opción Tipo de datos seleccionamos Todos los archivos (*.*), y dentro del Cuadro de Diálogo, seleccionamos el Archivo a Importar.

Una vez seleccionado el Archivo de Texto nos aparecen, una serie de tres pantallas correspondientes al asistente para importar texto.

En esta Primera Pantalla, podemos indicar si el texto importado tiene los Campos delimitados o no para que Excel 2007 sepa donde empieza un campo y dónde acaba. Indicamos a partir de qué fila queremos Importar, archivo.

asimismo, podemos decidir el Origen del

Si todos los datos son correctos pulsamos sobre Siguiente> En la segunda pantalla del asistente podemos elegimos los Separadores de los Datos, que por defecto viene marcado el separador de Tabulación. Si seleccionamos Espacio , el carácter espacio en blanco delimitaría los Campos. Cuando utilizamos esta opción, es conveniente activar como uno solo.

Considerar separadores consecutivos

Para continuar con el asistente pulsamos sobre Siguiente> En la tercera y última pantalla del asistente, seleccionamos el Formato de los datos . Normalmente Excel 2007 puede determinar el tipo de los datos de una columna por los valores contenidos en ella, pero podemos cambiar ese formato si no nos parece el más adecuado.

CURSO EXCEL 2007 AVANZADO

Hacemos Clic en la Columna para seleccionara y no aparece dicha Columna con fondo negro. Seleccionamos el formato en la sección Formato de los datos en columnas. Esta sección también nos sirve si no queremos importar la columna en nuestra hoja (No importar columna (saltar) En el botón Avanzadas podemos completar ciertas características para los números como los separadores de decimales y millares y la posición del signo en los negativos. Una vez hayamos completado o comproba do la definición, pulsamos sobre Finalizar para que termine el asistente e importe los datos. Si de lo contrario, creemos que se nos ha olvidado algo, podemos volver a las pantallas anteriores pulsando el botón <Atrás. Nos aparece un cuadro de diálogo preguntándonos dónde deseamos Importar los datos .

Marcamos Hoja de cálculo existente y la Celda =$A$1 , para que los importe a partir de la Celda A1 o bien podem os importar los datos en una nueva hoja dentro del libro. Para finalizar pulsamos sobre Aceptar y nos aparecen los datos importados en nuestra Hoja de Cálculo de Excel 2007.

LA SECCIÓN CONEXIONES A Continuación, vamos a ver las Conexiones de Excel 2007 con Archivos Externos de los cuales hayamos importado algún Dato. Nos desplazamos a la Pestaña Datos , y seleccionamos Conexión. Hacemos Clic en la opción Propiedades.

CURSO EXCEL 2007 AVANZADO

En la sección Control de actualización podemos hacer que Excel 2007 actualice automáticamente los datos insertados en la hoja de cálculo cada vez que se abra el libro. Marcamos la casilla Actualizar al abrir archivo Si los datos almacenados en el fichero origen han cambiado, estos cambios quedarán reflejados en nuestra hoja de cálculo. Excel 2007 almacena en el libro de trabajo la definición de la consulta de donde son originarios los datos importados, de manera que puede ejecutarla de nuevo cuando se desee. Si marcamos la casilla Solicitar el nombre del archivo al actualizar, cada vez que se vayan a actualizar los datos importados, Excel 2007 nos pedirá de qué fichero coger los datos , sino los cogerá directamente del archivo que indicamos cuando importamos por primera vez. Esta opción puede ser útil cuando tenemos varios archivos con datos y en cada momento queremos ver los datos almacenados en uno de esos ficheros. En la sección Definición de consulta tenemos activada por defecto la casilla Guardar definición de consulta para que Excel 2007 guarde esta definición y pueda actualizar los datos cuando se lo pidamos. Si desactivamos esta casilla y guardamos el libro, Excel 2007 no podrá volver a actualizar los datos externos porque éstos serán guardados como un rango estático de datos, digamos que se perderá el enlace con el origen de la importación. Por último, en la sección Diseño y formato de datos podemos indicarle a Excel 2007 qué debe de hacer cuando actualiza los datos. La sección Conexiones, también nos permite actualizar manualmente los datos importados de un archivo externo. Hacemos clic en la opción Actualizar que se encuentra en el desplegable Actualizar todo.

CURSO EXCEL 2007 AVANZADO

Seleccionamos la Opción Actualizar Todo, y todos nuestros Orígenes de Datos se actualizarán.

IMPORTAR DATOS DE WORD 2007 A EXCEL 2007 De Word a Excel A veces puede ser útil Importar una Tabla de Word 2007 a Excel 2007 si deseamos agregar algún tipo de Fórmula a los datos de los que disponemos y no queremos volver a teclear todos los datos en un libro de Excel 2007. Estando en Word 2007, seleccionamos la tabla que queremos pasar a Excel 2007. Copiamos la Tabla seleccionada

Ahora en Excel 2007 seleccionamos la Celda donde queremos colocar la Tabla, y la pegamos.

Es posible que nos advierta de que el texto seleccionado es más pequeño que la hoja, pulsamos sobre Aceptar

Nos pegará la tabla con el mismo formato que la teníamos en Word 2007.

CURSO EXCEL 2007 AVANZADO

De Excel a Word Al importar datos de Excel 2007 a Word 2007, podemos hacer que los datos queden Vinculados. Esto significa, que además de copiar los datos de Excel en un documento de Word, si realizamos algún cambio en la hoja de Excel 2007, este cambio queda reflejado en el documento de Word 2007. Seleccionamos la información a copiar de la hoja de Excel 2007, Seleccionados

y copiamos los

Datos

A Continuación, nos desplazamos a Word 2007, y hacemos Clic en el botón Pegar.

Después de pegar los Fatos en Word 2007, nos aparece un indicador de pegado podemos ver las distintas opciones.

donde

Por defecto nos conserva el mism o formato que tenía en la hoja de Excel 2007. Si lo queremos insertar en una tabla podemos escoger la opción Hacer coincidir estilo de tabla de destino. Y si queremos vincular los datos de Excel 2007 para que se actualicen en Word podemos escoger la opción Conservar formato de origen y vincular a Excel 2007.

De Access 2007 a Excel 2007 También disponemos de un botón que nos permitirá importar Datos directamente de Consultas y Tablas de Microsoft Access 2007 a nuestra Hoja de Cálculo. CURSO EXCEL 2007 AVANZADO

En la Pestaña Obtener Datos Externos, pulsamos Desde Access

En el Cuadro de Diálogo Abrir, seleccionamos la Base de Datos de la que vamos a Importar los Datos. Seleccionamos una tabla o consulta, y hacemos Clic en Aceptar. En la siguiente ventana que nos aparece, podemos elegir cómo ver los Datos en el libro

Podemos incluirlos en la Hoja Actual, indicando su Posición, o en una nueva Hoja del Libro. Pulsando en Propiedades , podremos modificar las propiedades de la conexión, como la Descripción o cuando se debe actualizar.

CURSO EXCEL 2007 AVANZADO

TABLAS DE DATOS Una Tabla de Datos en Excel 2007 es un conjunto de datos organizados en filas o registros, en la que la primer a fila contiene las cabeceras de las columnas (los nombres de los campos), y las demás filas contienen los datos almacenados. Es como una tabla de base de datos , de hecho también se denominan listas de base de datos. Cada fila es un registro de entrada, por tanto podremos componer como máximo una lista con 255 campos y 65535 registros. Las Tablas de Datos, son muy útiles porque además de almacenar información, incluyen una serie de operaciones que permiten analizar y administrar esos datos de forma muy cómoda. Estas son las operaciones que podemos realizar con las Tablas de Datos.     

Ordenar la los Registros Filtrar el contenido de la tabla por algún criterio. Utilizar Fórmulas para la lista añadiendo algún tipo de filtrado. Crear un resumen de los Datos. Aplicar Formatos a todos los Datos.

CREAR UNA TABLA A Continuación, vamos a ver cómo crear una Tabla de Datos. Seleccionamos el rango de Celdas que queremos incluir en nuestra Tabla de Datos

Seleccionamos Tabla en la Pestaña Insertar Nos aparece el Cuadro de Diálogo Crear Tabla

Si no hemos seleccionamos previamente las Celdas , lo podemos hacer en esta Pantalla. Si en el rango seleccionado hemos inclui do la fila de cabeceras, activamos la casilla de Verificación La lista tiene Encabezados y pulsamos en Aceptar.

CURSO EXCEL 2007 AVANZADO

Al cerrarse el cuadro de diálogo, podemos ver que en la banda de opciones aparece la pestaña Diseño, correspondiente a las Herramientas de Tabla

Y en la hoja de cálculo aparece en rango seleccionad o con el formato propio de la Tabla de Datos.

MODIFICACIÓN DE DATOS EN UNA TABLA Para modificar o introducir nuevos Datos en la Tabla utilizamos un Formulario de Datos. Un formulario de datos es un cuadro de diálogo que permite al usuario escribir o mostrar con facilidad una fila entera de datos. Para abrir el Formulario de Datos , tenemos que posicionarnos en la lista para que esté activa

Pulsamos en el icono Formulario Al crear el Formulario, disponemos de siguientes botones:       

Nuevo : Sirve para introducir un nuevo registro. Eliminar : Eliminar el registro que está activo. Restaurar: Deshace los cambios efectuados. Buscar anterior: Se desplaza al registro anterior. Buscar siguiente: Se desplaza al siguiente registro. Criterios : Sirve para aplicar un filtro de búsqueda. Cerrar : Cierra el formulario.

CURSO EXCEL 2007 AVANZADO

Para cambiar los datos de un registro , primero nos posicionamos sobre el Registro

Modificamos los Datos que queramos y para desplazarnos por los campos podemos utilizar las teclas de Tabulación. Si nos hemos equivocado y no queremos Restaurar.

guardar los cambios hacemos clic en el botón

Si queremos guardar los cambios pulsamos la tecla Intro Para crear un nuevo registro, hacemos clic en el botón Nuevo, Excel 2007 se posicionará en un registro vacío, sólo nos quedará rellenarlo. Pulsamos Intro o Restaurar para aceptar o cancelar respectivamente. Después de aceptar Excel 2007 se posiciona en un n uevo registro en blanco por si queremos insertar varios registros, una vez agregados los registros, hacemos clic en Cerrar. Para buscar un Registro y posicionarnos en él podemos utilizar los botones Buscar anterior y Buscar siguiente o ir directamente a un registro concreto introduciendo un criterio de búsqueda. Pulsamos en el botón Criterios y se abre el Formulario para introducir el criterio de Búsqueda.

MODIFICAR LA ESTRUCTURA DE UNA TABLA A Continuación, vamos a ver cómo Modificar la Estructura de una Tabla de Datos Si pulsamos en el Icono Cambiar tamaño de la tabla, podemos seleccionamos un nuevo Rango de Datos. Si la tabla contiene Encabezados, estos deben permanecer en la misma posición, así que sólo podremos Aumentar y Disminuir filas.

CURSO EXCEL 2007 AVANZADO

Podemos modificar directamente el Rango de Filas y Columnas, estirando o encogiendo la tabla

desde su esquina inferior derecha. Si queremos añadir una fila al final de la tabla para continuar introduciendo Datos pulsamos la tecla TAB desde la última Celda y aparecerá una Fila Nueva.

Si necesitamos insertar filas y columnas entre las filas existentes de la tabla, nos posicionaremos en la primera Celda que queremos desplazar Seleccionamos la opción Insertar Filas o Insertar Columnas desde el menú Insertar , en la pestaña de Inicio o en el menú contextual de la tabla. Para eliminar filas o columnas, deber emos posicionarnos sobre una Celda, y elegimos Filas o Columnas de la tabla en el menú Eliminar , disponible en la pestaña de Inicio y en el menú contextual de la tabla.

Seleccionando una Celda, fila o columna, y pulsando la tecla SUPRIMIR , eliminamos los datos seleccionados, pero no la estructura de la tabla. Para eliminar la tabla completa, seleccionamos toda la tabla y pulsamos SUPRIMIR Si únicamente deseamos eliminar la estructura de la tabla, pero conservar los datos en la hoja, pulsamos

en la pestaña de Diseño de la Tabla.

ESTILOS DE TABLAS DE DATOS Una forma fácil de dar una combinación de colores a la tabla, es elegir uno de los estilos predefinidos, disponibles en la pestaña Diseño de la tabla. En Opciones de estilo de la tabla , podemos marcar o desmarcar que las columnas o filas aparezcan remarcadas con Bandas, o se muestre un resaltado especial en la primera o última columna.

CURSO EXCEL 2007 AVANZADO

Las Bandas y Resaltados dependerán del Estilo de la Tabla que hayamos elegido.

A cada Celda le podemos aplicar los colores de fuente y fondo, fondo condicional, etc. que a cualquier Celda de la hoja de cálculo.

ORDENAR UNA TABLA DE DATOS A Continuación, vamos a ver cómo Ordenar una Tabla de Datos. A la hora de ordenar una tabla, Excel 2007 puede hacerlo de forma simple, es decir, ordenar por un único Campo u ordenar la Lista por diferentes campos a la vez. Para hacer una Ordenación Simple, debemos posicionarnos en la columna por cuyo criterio que remos ordenar nuestra Tabla de Datos. Accedemos a la pestaña Datos, pulsamos sobre Ordenar y escogemos el Criterio de Ordenación

También podemos pulsar sobre uno de los botones de la sección Ordenar y filtrar para que la ordenación s ea ascendente o descendente respectivamente. Estos botones también están disponibles al desplegar la lista que aparece pulsando la pestaña junto al Encabezado de la Columna. Para ordenar la lista por más de un criterio de ordenación, en la pestaña Datos, pulsamos sobre Ordenar. Nos aparece el cuadro de diálogo Ordenar donde podemos seleccionamos los campos por los que queremos Ordenar Pulsamos Agregar Nivel para añadir un campo, si ordenamos según el valor de la Celda. O por su color o icono en Ordenar Según, y el Criterio de Ordenación

CURSO EXCEL 2007 AVANZADO

Pulsando las flechas hacia arriba o hacia abajo, aumentamos o disminuimos la prioridad de ordenación de este nivel. Los datos se ordenarán, primero, por el primer nivel de la lista, y sucesivamente por los demás niveles en orden descendente. En la parte superior derecha tenemos un botón Opciones, este botón sirve para abrir el cuadro Opciones de Ordenación dónde podremos especificar más opciones en el Criterio de la Ordenación.

FILTRAR EL CONTENIDO DE UNA TABLA Para Filtrar una lista, seleccionamos aquellos Registros que se correspondan con algún criterio fijado por nosotros. Excel 2007 nos ofrece dos forma s de filtrar una lista. 1. Utilizando el Autofiltro 2. Utilizando Filtros Avanzados .

CURSO EXCEL 2007 AVANZADO

Autoflitro Para utilizar el Autofiltro, utilizamos las listas des plegables asociadas a las Cabeceras de Campos.

Y pulsamos, sobre la Flecha del Campo que nos sirve de Criterio para el Autofiltro.

Nos aparece un menú desplegable, donde nos ofrece una serie de Opciones para realizar el Filtro. Para indicarnos que hay un filtro activo, la flecha de la lista desplegable cambia de Icono. Para quitar el filtro, volvemos a desplegar la Lista, y elegimos la opción Seleccionamos Todo Vemos que reaparecen todos los Registros de la Lista. También podemos quitar el filtro pulsando en Borrar filtro en la pestaña Datos .

Filtro avanzado Si queremos filtrar los registros de la lista por una condición más compleja, utilizaremos el cuadro de diálogo Filtro avanzado. Lo primero de debemos de hacer, es indicar los Criterios de Filtrado.

Para abrir el cuadro de diálogo Filtro Avanzado , Pulsamos en el Botón Avanzadas en la sección Ordenar y Filtrar.

CURSO EXCEL 2007 AVANZADO

Nos encontramos las Siguientes Opciones: Rango de la lista : Aquí especificamos los registros de la lista a los que queremos aplicar el filtro. Rango de criterios: Aquí seleccionamos la fila donde se encuentran los criterios de filtrado También podemos optar por guardar el resultado del filtrado en otro lugar, seleccionando la opción Copiar a otro lugar. En este caso rellenaremos el campo Copiar a: con el rango de Celdas que recibirán el resultado del filtrado. Si marcamos la casilla Sólo registros únicos, las repeticiones de registros (filas con exactamente los mismos valores) desaparecerán. Para volver a visualizar todos los registros de la lista, acceder al menú Datos, hacemos clic en Filtro y pulsamos la Opción Mostrar todo.

Definir Criterios de Filtrado Para formar las Condiciones que vaya mos a utilizar en el parámetro rango_criterios, debemos reservar una zona en nuestra hoja de cálculo para dichas condiciones, a esta Zona la llamaremos Zona de Criterios. En esta zona tendremos que tener en una fila los Encabezados de los Campos de la Lista que intervienen en la condición, debajo indicaremos las condiciones. Para combinar varias condiciones se emplean los operadores Y y O.  

En un criterio de filtrado, si las condiciones están escritas en la misma fila, estarán unidas por el operador Y, para que el registro aparezca se deben cumplir todas las condiciones. En un criterio de filtrado, si las condiciones están escritas en distintas filas, estarán unidas por el operador O, el registro aparecerá en el resultado del filtrado si cumple al menos una de las condiciones.

RESUMEN DE DATOS Crear un Resumen de los Datos de una Tabla, es crear Subtotales agrupando los Registros por alguno de los campos de la lista. Para agregar los Subtotales Automáticamente, nos situamos sobre una Celda cualquiera de la lista, y arcamos la opción Fila de Totales en las Opciones de Estilo de Tabla, en la Pestaña Diseño.

CURSO EXCEL 2007 AVANZADO

Al seleccionar una Celda de la Fila de Totales, aparece una pesta単a con una lista de las Funciones que podemos usar para calcular el total de esa Columna.

CURSO EXCEL 2007 AVANZADO

TABLAS DINÁMICAS CREACIÓN DE TABLAS DINÁMICAS Una Tabla Dinámica es el resumen de un Conjunto de Datos, atendiendo a varios criterios de Agrupación, representado como una tabla de doble entrada que nos facilita la interpretación de dichos Datos. Se denomina Dinámica porque nos permite ir obteniendo diferentes totales, filtrando datos, cambiando la presentación de los datos, visualizando o no los datos origen, etc... Para crear una tabla dinámica, Excel 2007 nos proporciona las Tablas y Gráficos Dinámicos. Nos desplazamos a la Pestaña Insertar y seleccionamos Tabla Dinámica.

Desde el Cuadro de Diálogo de Creación de Tablas Dinámicas, podemos indicar el lugar donde se encuentran los datos que queremos analizar y el lugar donde queremos ubicarla. En nuestro caso, por ejemplo, indicamos que vamos a seleccionar los datos de un Rango de Celdas y que queremos ubicarla en una Hoja de Cálculo nueva.

Podríamos crear una Conexión con otra aplicación para obtener los datos desde otra fuente diferente a Excel 2007. Si marcamos la Opción Selecciona una tabla o rango debemos seleccionamos todas las Celdas que vayan a participar, incluyendo las cabeceras.

CURSO EXCEL 2007 AVANZADO

Pulsamos Aceptar y se abre un nuevo Panel en la Derecha de la Pantalla

Podemos personalizar la forma en que van a verse los datos en la Tabla Dinámica a través de este Panel, arrastramos los elementos que vemos en la Lista a su lugar correspondiente al Pie del Panel. Una vez creada la Tabla Dinámica nos aparece la pestaña Opciones:

El Panel Lateral sigue pudiéndose utilizar, así que en cualquier momento podremos quitar un campo de un zona arrastrándolo fuera. De esta manera, podemos variar la Estructura de la Tabla y obtener otros resultados fácilmente.

ELIMINAR TABLAS DINÁMICAS A Continuación, vamos a ver la manera de Eliminar Tablas Dinámicas de nuestra Hoja de Cálculo de Excel 2007. Seleccionamos la Tabla Dinámica, y pulsamos la Tecla SUPRIMIR, de esta manera, ya hemos Eliminado nuestra Tabla Dinámica.

FILTRAR TABLAS DINÁMICAS Las Tablas Dinámicas permiten filtrar los resultados y, visualizar únicamente los que nos interesen en un momento determinado. Esto es muy útil, cuando el Volumen de Datos es elevado.

CURSO EXCEL 2007 AVANZADO

Los Campos Principales en el panel y los rótulos en la tabla están acompañados, en su parte derecha, de una flecha indicando una lista desplegable. Si pulsamos sobre la flecha del rótulo Rótulos de Columna nos aparece una lista con los distintos criterios de Filtrados disponibles en la tabla con una casilla de verificación en cada uno de ellos para indicar si los queremos ver o no, más una opción para Marcar todas las Opciones

Para cerrar este cuadro debemos Pulsamos en Aceptar, o sobre Cancelar para cerrar y no realizar ningún cambio.

PROMEDIOS Por defecto, al crear una Tabla Dinámica, Excel 2007 nos genera unos totales con sumatorio. Podemos modificar esas Fórmulas por otras como pueden ser sacar el Máximo o el Mínimo, el Promedio, etc.

Nos situamos en cualquier Celda de la zona que queremos Modificar, y hacemos clic con el Botón Derecho del Ratón Seleccionamos la opción Configurac ión de Campo de Valor y escogemos cómo queremos hacer el resumen, mediante Suma , Cuenta, Promedio, etc.

CURSO EXCEL 2007 AVANZADO

También podemos abrir el cuadro de diálogo con el botón Pestaña Opciones .

CURSO EXCEL 2007 AVANZADO

de la

PROGRAMACION Y MACROS INTRODUCCIÓN A LA PROGRAMACIÓN Antes de Empezar a trabajar con Macros vamos a Introducirnos en unos Conceptos Básicos de Programación. El Lenguaje de Programación que utiliza Excel 2007 está basado en Visual Basic, un lenguaje de programación Orientado a Objetos. La Filosofía de los Lenguajes orientados a Objetos es que el mundo real lo podemos abstraer de tal forma que lo podemos representar como objetos y programar con ellos. Un Objeto es algo con entidad propia dentro de una Clase  

Una Clase es un Conjunto de Objetos que tienen propiedades en común y se comportan de una manera igual o simila r al resto de objetos pertenecientes a esa misma Clase. Las Propiedades son aquellas características que definen a los objetos de una clase, diferenciándolos del resto de Clases.

Las Clases se caracterizan por su comportamiento, en orientado a objetos se le suele llamar métodos que están diferenciados en Funciones y procedimientos. La diferencia fundamental entre Funciones y Procedimientos, es que las Funciones al llevarse a cabo devuelven algún Tipo de Valor. Los Procedimientos realizan su cometido y pueden o no devolver algún Valor. En la Programación Orientada a Objetos existe también un concepto muy importante que son los Eventos. Los Eventos son Sucesos que son provocados por algún tipo de estímulo externo y que hacen que pueda alterarse el Comportamiento de la Clase. El Lenguaje de Programación que utiliza Excel 2007 para trabajar con Macros es el VBA (Visual Basic Application) que está basado en Visual Basic. VBA es una modalidad adaptada del Lenguaje de Programación Visual Basic, incluida en la mayoría de las aplicac iones MS Office 2007 como Excel, Word y Access. Una diferencia entre VBA y Visual Basic es que el programa VBA no se puede separar del documento de Excel 2007, sino queda totalmente integrado dentro del Libro de Trabajo. El Editor VBA sirve para controlar y manipular tu código VBA de Macros. Se parece mucho a Visual Basic, con la diferencia de que VBA tiene otros objetos (hoja, celda etc) Métodos y Eventos.

CURSO EXCEL 2007 AVANZADO

Esta es la Pantalla General del Editor de Visual Basic.

Esta es la barra de Menú del Editor de Visual Basic donde podemos acceder a las diversas opciones y comandos del propio Editor.

Esta es la Barra de Botones de Visual Basic, donde podemos acceder de una manera más rápida a las opciones más utilizadas, como son la ejecución, la parada, guardar, etc...

En la parte Izquierda de la Pantalla podemos ver dos recuadros, el recuadro superior es el del proyecto. Aquí se encuentran los módulos, las hojas utilizadas en los Procedimientos / Funciones, etc.

CURSO EXCEL 2007 AVANZADO

En la Parte Inferior tenemos otro recuadro, con las propiedades del objeto seleccionado.

En la Parte Derecha tenemos Procedimientos/Funciones.

espacio

dedicado

redactar

Código

los

En la Parte Superior existen dos cuadros combinados donde podemos seleccionamos los objetos y los métodos de ese objeto respectivamente. A Continuación, vamos a ver las distintas Combinaciones del Teclado, para trabajar con el Editor de VBA. Abrir el Editor VBA ALT + F11 Volver a Excel 2007 ALT + F11 Cerrar el Editor VBA ALT + F4 Examinador de Objetos F2 Buscar F3 Ventana Propiedades F4 Cerrar Ventana CONTROL + F4 Ejecutar F5 Ver el Código del Proyecto F7 Paso a Paso por Instrucciones F8 Ejecutar hasta el Cursor CONTROL + F8 Insertar Punto de Interrupción F9 CURSO EXCEL 2007 AVANZADO

Suprimir Punto de Interrupción F9 Suprimir todos los Puntos de Interrupción CONTROL + MAYUSCULAS + F9

MACROS Después de comprender Introducirnos en el Lenguaje de Programación VBA y su Editor VBA, en este Capítulo, vamos a introducirnos en el Mundo de las Macros. Cuando trabajamos con un Libro personalizado en el que hemos definido con una Serie de Características Específicas como puedan ser el tipo de letra, el color de ciertas Celdas, los formatos de los cálculos y características similares. Perdemos mucho tiempo en maquetar todo el libro si disponemos de muchas Hojas. Con las Macros se consigue Automatizar varias Tareas y Fusionarlas en una sola. Podemos crear un Botón en nuestro libro que al Pulsarlo, realice todas las Acciones a la vez.

Crear una Macro Automáticamente La forma más Sencilla de crear Macros es crearlas mediante el Grabador de macros que dispone Excel 2007. El Grabador de Macros nos permite grabar las acciones deseadas que posteriormente las traduce a instrucciones en VBA para poder modificarlas posteriormente con Conocimientos de Programación. Para Grabar una Macro, accedemos a la Pestaña Vista, y desplegamos el submenú Macros Seleccionamos la Opción Grabar Macro

Además de esta opción en el menú podemos encontrar las Siguientes Opciones. Ver Macros, donde accedemos a un listado de las macros creadas en ese libro. Usar referencias relativas, con esta opción utilizaremos Referencias Relativas para que las Macros se Graben con acciones relativas a la Celda inicial seleccionada.

CURSO EXCEL 2007 AVANZADO

Lo primero que nos piden es Escribir un Nombre a la Macro Debemos de tener en cuenta, que no está permitido insertar espacios en Blanco en el Nombre de la Macro.

Podemos asignarle un Método Abreviado, mediante la combinación de las tecla CTRL + "una tecla del teclado". En Guardar Macro En, podemos seleccionamos guard ar la macro en el libro activo, en el libro de macros personal o en otro libro. En Descripción, podemos describir cuál es el cometido de la macro o cualquier otro dato que creamos conveniente. Para Comenzar la Grabación de la Macro pulsamos el botón Aceptar. Si nos fijamos en la Barra de Estado, vemos que ha aparecido un Botón Nuevo de Detener, con el que podemos Detener la Grabación.

A partir de ver el Botón de Detener, realizamos las Acciones que queramos Grabar, es conveniente no seleccionar ninguna Celda a partir de la Grabación. Si seleccionamos alguna Celda, cuando ejecutemos la Macro, la selección nos puede ocasionar problemas de Celdas fuera de Rango, que es un error muy común en Macros. Una vez que hemos acabado las acciones que querem os Grabar, presionamos sobre el botón Detener de la Barra de Estado. Otra forma de detener la

Grabación, es Seleccionar el Menú de

Macros, y hacer Clic en

CURSO EXCEL 2007 AVANZADO

Crear una Macro Manualmente Para crear una Macro de Forma Manual es necesario tener conocimientos de programación y de Visual Basic , como ya hemos visto en el Capítulo anterior, ya que es el lenguaje de programación en el que se basa el VBA de Excel 2007. A Continuación, vamos a ver cómo crear una Macro de Forma Manual. Lo primero que debemos hacer, es Abrir el Editor Visual Basic presionando las Teclas ALT + F11. Una vez abierto el Editor de Visual Basic debemos insertar un módulo de trabajo que es donde se almacena el código de las Funciones o procedimientos de las Macros. Nos desplazamos al Menú Insertar, Seleccionamos Módulo . Accedemos al menú Insertar, pulsamos sobre el Botón Procedimiento y escribimos Nombre a nuestro Procedimiento sin insertar Espacios en Blanco.

 

Si lo que vamos a Crear con el Módulo es una Función , en el caso que devuelva algún Valor, seleccionamos Función. si por el contrario lo que vamos a crear con el Módulo es un Procedimiento, es decir no devuelve ningún Valor, seleccionamos Procedimiento.

CURSO EXCEL 2007 AVANZADO

Además podemos seleccionamos el Ámbito de ejecución.

Si marcamos Público podremos utilizar el Procedimiento o Función desde cualquier otro Módulo. Si lo creamos como Privado solo podremos utilizarlo dentro de ese Módulo. Una vez seleccionado el tipo de procedimiento y el ámbito pulsamos sobre el Botón Aceptar Se abre el Editor de Visual Basic y escribimos las Instrucciones necesarias para definir nuestra Macro.

Ejecutar una Macro Una vez creada nuestra Macro ya sea Automática o Manualmente , podremos ejecutarla las veces que la necesitemos. Antes de dar la orden de Ejecutar nuestra Macro, dependiendo del tipo de Macro que hayamos creado, será necesario seleccionar o no las Celdas que queramos que queden afectadas por las Acciones de la Macro. Por ejemplo si hemos creado una Macro que automáticamente da Formato a las Celdas seleccionadas, tendremos que seleccionar las Celdas previamente antes de ejecutar la Macro. Para ejecutar la macro debemos acceder a la Pestaña Vista, seleccionamos el Menú Macros y hacemos Clic en Ver Macros Nos aparece el cuadro de diálogo Macros, con una lista con las Macros Creadas . Seleccionamos la Macro que queremos Ejecutar, y pulsamos sobre el botón Ejecutar . Se cierra el Cuadro de Diálogo y automáticamente se Ejecuta la Macro. Veamos el Resto de Botones: Cancelar , cierra el cuadro de diálogo sin realizar ninguna acción. Paso a paso, ejecuta la macro instrucción por instrucción abriendo el editor de programación de Visual Basic. Modificar , abre el editor de programación de Visual Basic para modificar el código de la macro. Estos dos últimos botones son para los que sepan programar. Eliminar, borra la Macro. Opciones, abre otro cuadro de diálogo donde podemos modificar la tecla de método abreviado (combinación de teclas que provoca la ejecución de la macro sin necesidad de utilizar el menú) y la descripción de la macro.

CURSO EXCEL 2007 AVANZADO

Guardar Archivos con Macros Cuando guardamos un archivo y querem os que las Macros que hemos creado se almacenen con el resto de las hojas de cálculo deberemos utilizar un tipo de archivo diferente. Nos desplazamos al Botón de Office y seleccionamos la opción Guardar Como. En el desplegable Guardar como tipo seleccionamos Libro de Excel 2007 Habilitado para Macros (*.xlsm).

Escribimos un Nombre y el Archivo se Almacena. Cuando abrimos un archivo que tiene Macros almacenadas se nos muestra este Anuncio bajo la Banda de Opciones:

Esto sucede porque Office no conoce la procedencia de las Macros. Como están compuestas por Código, podrían realizar acciones que fuesen perjudiciales para nuestro Equipo. Si confías en las posibles Macros que contuviese el archivo o las hemos creado nosotros, pulsamos el botón Opciones para Activarlas. Aparecerá este Cuadro de Diálogo:

Seleccionamos la opción Habilitar este Contenido, y pulsamos en Aceptar

CURSO EXCEL 2007 AVANZADO

APENDICE A: FUNCIONES DE EXCEL FUNCIONES DE BASE DE DATOS

CURSO EXCEL 2007 AVANZADO

FUNCIONES DE FECHA Y HORA

CURSO EXCEL 2007 AVANZADO

FUNCIONES FINANCIERAS

CURSO EXCEL 2007 AVANZADO

FUNCIONES DE INFORMACIÓN

FUNCIONES LÓGICAS

CURSO EXCEL 2007 AVANZADO

FUNCIONES DE BÚSQUEDA Y REFERENCIA

CURSO EXCEL 2007 AVANZADO

FUNCIONES MATEMÁTICAS Y TRIGONOMÉTRICAS

CURSO EXCEL 2007 AVANZADO

FUNCIONES ESTADÍSTICAS

CURSO EXCEL 2007 AVANZADO

FUNCIONES DE TEXTO

CURSO EXCEL 2007 AVANZADO

APENDICE B: EJEMPLOS DE MACROS A Continuaci贸n, vamos a ver unos Ejemplos de Macros que nos pueden ayudar con nuestros Libros de Trabajo de Excel 2007

CURSO EXCEL 2007 AVANZADO

Estos otros ejemplos se correspondes a macros realizados con hojas

CURSO EXCEL 2007 AVANZADO