Microsoft Office Excel 2007 by VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

VICENTA MEGÍA MOLERO APLICACIONES INFORMÁTICAS 1º A.F.I.

CURSO 2010-2011

Microsoft Office Excel 2007 INDICE: ¿QUÉ ES UNA HOJA DE CÁLCULO?...................................................................................... 7 TIPOS DE DATOS ................................................................................................................ 8 VENTANA PRINCIPAL DE MICROSOFT EXCEL 2007 .............................................................. 9 OPERADORES EN EXCEL ................................................................................................... 10 OPERADORES DE COMPARACIÓN ............................................................................................................................... 10 OPERADORES ARITMÉTICOS ...................................................................................................................................... 10 OPERADORES DE REFERENCIA ................................................................................................................................... 10

DESPLAZAMIENTO POR LA HOJA DE CÁLCULO.................................................................. 11 SEGURIDAD Y PROTECCIÓN EN EXCEL .............................................................................. 11 IMPRESIÓN ..................................................................................................................... 14 VISTA PRELIMINAR.................................................................................................................................................. 14 CONFIGURAR PÁGINA.............................................................................................................................................. 16 IMPRIMIR ............................................................................................................................................................. 19

INDICES ........................................................................................................................... 21 MACRO PARA CREAR ÍNDICES EN EXCEL............................................................................................................ 21 Paso 1: Insertar una nueva hoja de cálculo .................................................................................................. 21 Paso 2: Insertar macro de Excel en la nueva hoja......................................................................................... 22

AUTORRELLENO Y SERIES................................................................................................. 23 REPETIR AUTOMÁTICAMENTE ELEMENTOS YA ESCRITOS EN LA COLUMNA ........................................................................... 23 CÓMO ACTIVAR O DESACTIVAR LA FINALIZACIÓN AUTOMÁTICA DE VALORES DE CELDA .......................................................... 24 UTILIZAR EL CONTROLADOR DE RELLENO PARA RELLENAR DATOS ...................................................................................... 25 Cómo mostrar u ocultar el controlador de relleno........................................................................................ 25 Cómo activar o desactivar Opciones de autorrelleno ................................................................................... 26 RELLENAR DATOS EN CELDAS ADYACENTES................................................................................................................... 26 Rellenar la celda activa con el contenido de una celda adyacente ............................................................... 27 Arrastrar el controlador de relleno para rellenar datos en celdas adyacentes............................................. 27 Rellenar fórmulas en celdas adyacentes ....................................................................................................... 27 RELLENAR UNA SERIE DE NÚMEROS, FECHAS U OTROS ELEMENTOS DE SERIE INTEGRADOS ..................................................... 28 Otros ejemplos de series que se puede rellenar............................................................................................ 28 CÓMO RELLENAR CELDAS CON UNA SERIE DE NÚMEROS ................................................................................................. 29 RELLENAR DATOS MEDIANTE UNA SERIE DE RELLENO PERSONALIZADA ............................................................................... 30 Cómo dar formato de texto a números ........................................................................................................ 30 UTILIZAR UNA SERIE DE RELLENO PERSONALIZADA BASADA EN UNA LISTA EXISTENTE DE ELEMENTOS ........................................ 31 UTILIZAR UNA SERIE DE RELLENO PERSONALIZADA BASADA EN UNA LISTA NUEVA DE ELEMENTOS ............................................ 32 MODIFICAR O ELIMINAR UNA SERIE DE RELLENO PERSONALIZADA ..................................................................................... 33

EJERCICIOS BÁSICOS DE EXCEL ......................................................................................... 34 FUNCIÓN PROMEDIO ........................................................................................................................................ 34 FUNCIÓN SUMA ................................................................................................................................................ 35 FUNCION SUMAR.SI .......................................................................................................................................... 36

Página 2 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 FUNCION MAX, MIN .......................................................................................................................................... 37 CÁLCULO DEL PORCENTAJE EN EXCEL ............................................................................................................... 38 SACAR POR CIENTO ....................................................................................................................................... 38 SUMAR POR CIENTO ..................................................................................................................................... 39 RESTAR POR CIENTO ..................................................................................................................................... 39 FUNCIÓN CARÁCTER ......................................................................................................................................... 39 FORMATO CONDICIONAL .................................................................................................................................. 40 EJERCICIO PASO A PASO DEL FORMATO CONDICIONAL .................................................................................... 45

ENCONTRAR DATOS DIFERENTES ..................................................................................... 49 BARRA DE DESPLAZAMIENTO .......................................................................................... 50 REFERENCIAS................................................................................................................... 52 REFERENCIA RELATIVA ............................................................................................................................................ 53 REFERENCIA ABSOLUTA ........................................................................................................................................... 53 REFERENCIA MIXTA ................................................................................................................................................ 54 COMO CAMBIAR EL TIPO DE REFERENCIA ..................................................................................................................... 54 REFERENCIAS A OTRAS HOJAS O LIBROS ....................................................................................................................... 55 EJERCICIO CON REFERENCIAS ............................................................................................................................ 55

GRÁFICOS........................................................................................................................ 57 INTRODUCCIÓN...................................................................................................................................................... 57 CREAR GRÁFICOS.................................................................................................................................................... 58 AÑADIR UNA SERIE DE DATOS ................................................................................................................................... 59 MODIFICAR LAS CARACTERÍSTICAS DEL GRÁFICO ........................................................................................................... 60 CONFIGURACIÓN DEL GRÁFICO .................................................................................................................................. 61 MODIFICAR EL TAMAÑO DE UN GRÁFICO ..................................................................................................................... 61 MODIFICAR LA POSICIÓN DE UN GRÁFICO .................................................................................................................... 62 EJERCICIOS CON GRÁFICOS ............................................................................................................................... 62 GRÁFICOS DE IMAGEN....................................................................................................................................... 67 COMENTARIOS TABLA ....................................................................................................................................... 67 GRÁFICO MIXTO ................................................................................................................................................ 68 GRÁFICO CON FLECHAS INDICATIVAS................................................................................................................ 68

HIPERVÍNCULOS EN EXCEL 2007 ....................................................................................... 69 EJERCICIO HIPERVÍNCULOS ............................................................................................................................... 71

MACROS ......................................................................................................................... 72 CREAR UNA MACRO AUTOMÁTICAMENTE .................................................................................................................... 72 EJECUTAR UNA MACRO............................................................................................................................................ 73 CREAR UNA MACRO MANUALMENTE .......................................................................................................................... 74 GUARDAR ARCHIVOS CON MACROS ........................................................................................................................... 74 EJERCICIOS CON MACROS ......................................................................................................................................... 76 Ejercicio 1. Uso de las referencias relativas .................................................................................................. 78 Ejercicio 2. Macro con método abreviado .................................................................................................... 79 Ejercicio 3. Macro con tabla.......................................................................................................................... 80 Ejercicio 4. Macros con Visual Basic.............................................................................................................. 81 Ejercicio 5. Asignar icono a una macro ......................................................................................................... 84

Página 3 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 FORMULARIOS CON CONCEPTOS BÁSICOS DE LAS MACROS ............................................. 86 HERRAMIENTA SOLVER (BUSCAR OBJETIVO) .................................................................... 94 RESUMEN AVANZADO CON DATOS DE DIFERENTES HOJAS EXCEL .................................... 99 LA FUNCIÓN BUSCAR V (COLUMNAS)............................................................................. 101 BÚSQUEDA DE OBJETIVOS ............................................................................................. 104 FUNCIONES MATEMÁTICAS ........................................................................................... 105 FUNCIONES ESTADÍSTICAS ............................................................................................. 106 FUNCIONES LÓGICAS ..................................................................................................... 107 FUNCIONES FECHA Y HORA............................................................................................ 108 EJERCICIO REPASO DE FUNCIONES: CONTARA, CONTAR, CONTAR SI, REDONDEAR ......... 109 FUNCIÓN SUMAPRODUCTO ........................................................................................... 111 FUNCION SUMAR.SI, SUMAR.SI.CONJUNTO, CONTAR.SI Y CONTAR.SI.CONJUNTO .......... 112 FUNCIÓN SUMAR.SI ........................................................................................................................................... 112 FUNCIÓN SUMAR.SI.CONJUNTO ........................................................................................................................ 114 FUNCIÓN CONTAR.SI.......................................................................................................................................... 115 FUNCIÓN CONTAR.SI.CONJUNTO ...................................................................................................................... 115

FUNCIÓN O, FUNCIÓN Y ................................................................................................ 116 FUNCIÓN ELEGIR ........................................................................................................... 118 FUNCIÓN POSICIÓN ....................................................................................................... 119 INCRUSTAR OBJETOS EN EXCEL ...................................................................................... 120 FUNCION BIN.A.DEC ...................................................................................................... 122 FUNCION BIN.A.HEX ...................................................................................................... 123 FUNCION BIN.A.OCT ...................................................................................................... 123 FUNCION DEC.A.BIN ...................................................................................................... 124 FUNCION DIAS.LAB ........................................................................................................ 125 FUNCIÓN DÍA.LAB.......................................................................................................... 127 FUNCION FIN.MES ......................................................................................................... 128 FUNCION ES.PAR ........................................................................................................... 129 FUNCION ES.IMPAR ....................................................................................................... 130 FUNCIÓN ESNUMERO .................................................................................................... 130 FUNCION ESTEXTO ........................................................................................................ 131

Página 4 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 FUNCION ESNOTEXTO ................................................................................................... 131 FUNCION DECIMAL ........................................................................................................ 132 FORMULARIOS EN EXCEL ............................................................................................... 133 FÓRMULAS MATRICIALES .............................................................................................. 134 FUNCIONES SENO, COSENO Y RADIANES CON GRÁFICO DE LÍNEAS................................. 136 EJERCICIO CON FILTROS ................................................................................................. 137 FILTRO AVANZADO .......................................................................................................................................... 141

EJERCICIOS DE CÁLCULOS EN FACTURAS ........................................................................ 142 FECHA DE VENCIMIENTO DE UNA FACTURA. CUENTAS A 30, 60 Y 90 DÍAS ..................................................... 142 EJERCICIO APOYO. CÁLCULO DE BENEFICIOS .................................................................................................. 143 FACTURA CONFECCIONES MARÍA ................................................................................................................... 144

ESQUEMAS .................................................................................................................... 144 SUBTOTALES ................................................................................................................. 146 REGISTRO DE FACTURAS ................................................................................................ 147 VALIDACIÓN AUTOMÁTICA DE DATOS ........................................................................... 149 PROTEGER ..................................................................................................................... 151 DESPLAZARSE DENTRO DE UN RANGO EN UN ORDEN ESTABLECIDO .............................. 152 RANGOS DINÁMICOS: FUNCIÓN DESREF ........................................................................ 153 RANGOS DINÁMICOS CON VALIDACIÓN DE DATOS: FUNCIÓN DESREF............................................................................. 154

ESCENARIOS .................................................................................................................. 156 TABLAS DE DATOS ......................................................................................................... 158 EJEMPLO DE TABLA DE DATOS-EXCEL 2007............................................................................................................... 162

TABLAS DINÁMICAS....................................................................................................... 163 TABLAS O LISTAS EN EXCEL I .......................................................................................... 165 TABLAS O LISTAS EN EXCEL II ......................................................................................... 167 REFERENCIAS CIRCULARES ............................................................................................. 169 FUNCIONES BASE DE DATOS .......................................................................................... 171 LA FUNCIÓN BDMAX ........................................................................................................................................... 171 LA FUNCIÓN BDSUMA......................................................................................................................................... 172 LA FUNCIÓN BDCONTAR ..................................................................................................................................... 173 LA FUNCIÓN BDPROMEDIO ................................................................................................................................ 173

BOTÓN DE OPCIÓN ........................................................................................................ 174 CUADROS DE GRUPO ............................................................................................................................................. 176

Página 5 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 FUNCIÓN REPETIR ......................................................................................................... 179 GRÁFICO EN LAS PROPIAS CELDAS ............................................................................................................................ 179

INMOVILIZAR FILAS Y COLUMNAS ................................................................................. 180 RELACION ENTRE INT.EFECTIVO Y TASA.NOMINAL ......................................................... 180 CONVERSIÓN ENTRE INT.EFECTIVO Y TASA.NOMINAL .......................................................................................... 181

ELIMINAR DATOS PRIVADOS EN DOCUMENTOS EXCEL 2007 .......................................... 182 FUNCIÓN IMPORTARDATOSDINÁMICOS ........................................................................ 184 FUNCIÓN JERARQUÍA .................................................................................................... 185 FUNCIONES FINANCIERAS .............................................................................................. 187 FUNCIÓN PAGO .................................................................................................................................................. 187

CÓMO ARMAR EN EXCEL UN LISTADO CON LOS 5 DATOS MAYORES Y 5 MENORES A PARTIR DE UNA LISTA DESORDENADA. .......................................................................... 188 EJERCICIOS EXCEL REPASO ............................................................................................. 191 EJERCICIO 1. REPASO EJERCICIOS POR CIENTOS. .......................................................................................................... 191 EJERCICIO 2. FUNCIONES AHORA Y HOY ................................................................................................................ 192 EJERCICIO 3. FUNCIÓN HOY Y SUMA ..................................................................................................................... 193 EJERCICIO 4. REFERENCIAS RELATIVAS, ABSOLUTAS Y MIXTAS ........................................................................................ 193 EJERCICIO 5. INCREMENTAR. ................................................................................................................................. 194 EJERCICIO 6. TABLA DE MULTIPLICAR........................................................................................................................ 195 EJERCICIO 7. FUNCIÓN PRODUCTO Y SUMAPRODUCTO ....................................................................................... 195 EJERCICIO 8. FUNCIONES MAX, MIN, SUMA Y SUMAR.SI ....................................................................................... 196 EJERCICIO 9. FUNCIONES O, Y, SI............................................................................................................................ 196 EJERCICIO 10. FUNCIONES CARÁCTER, CÓDIGO, ENCONTRAR, EXTRAE, MAYUSC, MONEDA Y REPETIR ............... 198 EJERCICIO 11. EXISTENCIAS DE ALMACÉN .................................................................................................................. 201 EJERCICIO 12. FUNCIÓN BUSCARV ........................................................................................................................ 202 EJERCICIO 13. FUNCIONES SI, MAX, MIN, PROMEDIO, MODA Y CONTAR.SI .......................................................... 204 EJERCICIO 14. CÁLCULOS RESULTADO DE EVALUACIÓN ................................................................................................ 205 EJERCICIO 15. FUNCIÓN DÍA.LAB .......................................................................................................................... 207 EJERCICIO 16. CUADRO COMBINADO Y FUNCIÓN INDICE...................................................................................... 208 EJERCICIO 17. FUNCIÓN INDIRECTO...................................................................................................................... 209 EJERCICIO 18. FÓRMULAS CON RANGOS............................................................................................................ 209 EJERCICIO 19. FUNCIÓN ALEATORIO.ENTRE ......................................................................................................... 210 EJERCICIO 20. APLICAR NOMBRES A RANGOS ............................................................................................................. 211 EJERCICIO 21. FUNCIONES JERARQUÍA, DESREF Y COINCIDIR ................................................................................ 212 EJERCICIO 22. FUNCIÓN BUSCARV ........................................................................................................................ 213 EJERCICIO 23. FUNCIONES Y, MAX, MIN ................................................................................................................ 214 EJERCICIO 24. FUNCIÓN SI, Y, O............................................................................................................................. 214

EJERCICIO DE APLICACIÓN. FLORISTERIA LOS NENÚFARES .............................................. 215 EXAMEN EXCEL RESUELTO I ........................................................................................... 216 EXAMEN EXCEL RESUELTO II .......................................................................................... 220 Página 6 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

¿Qué es una hoja de cálculo? Es una herramienta que permite realizar cálculos u operaciones matemáticas, estadísticas financieras y de tabla de base de datos. También se pueden crear y extraer gráficos de las hojas de trabajo realizadas. Los documentos en Excel se denominan libros. En esta nueva versión de Excel estos ficheros tendrán una extensión .xlsx a diferencia de versiones anteriores cuya extensión era .xls. Por supuesto que en esta nueva versión los tipos de ficheros antiguos son soportados. Excel es capaz, además, de guardar nuestros libros en otros formatos. Un libro está compuesto por varias hojas de cálculo. Es posible seleccionar varias hojas para realizar las mismas tareas en todas ellas de forma simultánea. También podemos trabajar con hojas de cálculo en OpenOffice.org Calc que es una licencia de software libre y cuyo formato de documento es .ods.

Cada vez que abramos un libro nuevo de trabajo se abrirá con tres hojas de cálculo.

La hoja activa aparece con el nombre en negrita. Haciendo clic en las etiquetas, es posible desplazarse entre las hojas de un libro. Es como una gran hoja cuadriculada formada por 16384 columnas y 1.048.576 filas. Las hojas de cálculo están formadas por columnas y filas. Una columna es el conjunto de celdas seleccionadas verticalmente. Cada columna se nombra por letras, por ejemplo A, B, C,.......AA, AB,........IV.

Página 7 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Cada fila se numera desde 1 hasta 1.048.576 y es la selección horizontal de un conjunto de celdas de una hoja de datos.

La intersección de una columna y una fila se denomina Celda y se nombra con el nombre de la columna a la que pertenece y a continuación el número de su fila, por ejemplo la primera celda pertenece a la columna A y la fila 1 por lo tanto la celda se llama A1. Cuando el cursor está posicionado en alguna celda preparado para trabajar con ésta, dicha celda se denomina Celda activa y se identifica porque aparece más remarcada que las demás. De igual forma tenemos la fila activa, fila donde se encuentra la celda activa y columna activa, columna de la celda activa. Otro concepto muy importante en una hoja de cálculo es el de Rango, que es un bloque rectangular de una o más celdas que Excel trata como una unidad. Los rangos son vitales en la Hoja de Cálculo, ya que todo tipo de operaciones se realizan a base de rangos.

Tipos de datos Generalmente se sigue un orden para introducir los datos: primero los rótulos, a continuación los números para el cálculo y por último las fórmulas que realicen dicho cálculo. Para introducir cualquier tipo de dato se hace lo siguiente; se selecciona la celda, se introduce el dato y se acepta pulsando intro o la marca verde de la barra de fórmulas. Si el dato es texto se alinea a la izquierda de la celda y podremos cambiar la alineación de texto (seleccionamos la celda, vamos a formato, alineación de celdas, elegimos la alineación deseada y pulsamos aceptar). Si el texto es mas largo que la celda se seguirá escribiendo en ella aunque solo veamos el principio del texto. Los números se alinean automáticamente a la derecha. La fórmulas se introducen activando = en una casilla y a continuación introducimos la fórmula. Tipos de datos:

Texto o rótulos. Números. Fórmulas. Funciones. Fechas y horas. Página 8 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Las funciones pueden considerarse como herramientas disponibles para la ejecución de operaciones de una forma abreviada. En la barra de herramientas estándar aparece el botón de auto-suma [∑] , equivalente a la función [=SUMA(‫ٱ‬:‫ ])ٱ‬que permite sumar de forma automática el rango de las celdas situado por encima o ala izquierda de la celda activada. Las fechas y horas pueden ir en una misma celda archivadas simultáneamente siempre y cuando ambos formatos vayan separados por un espacio en blanco. Un rango de celdas es un conjunto contiguo de celdas. Se identifica mediante la localización de la esquina superior izquierda y de la esquina inferior derecha y se expresa separando ambas localizaciones con dos puntos, ejemplo (A1:C5).

Ventana principal de Microsoft Excel 2007

Página 9 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Operadores en Excel Operadores de comparación

Operadores aritméticos

Operadores de referencia : Para definir rangos , Para unir varios rangos espacio Genera una referencia a las celdas comunes a dos referencias.

Página 10 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Desplazamiento por la hoja de cálculo Empezaremos por utilizar la barra de etiquetas.

Observamos que tenemos 3 hojas de cálculo, siendo la hoja activa, es decir, la hoja en la que estamos situados para trabajar, la Hoja1. Haciendo clic sobre cualquier pestaña cambiará de hoja, es decir, si hacemos clic sobre la pestaña Hoja3 pasaremos a trabajar con dicha hoja. Si el número de hojas no caben en la barra de etiquetas, tendremos que hacer uso de los botones de la izquierda de dicha barra para visualizarlas: Para visualizar a partir de la Hoja1. Para visualizar la hoja anterior a las que estamos visualizando. Para visualizar la hoja siguiente a las que estamos visualizando. Para visualizar las últimas hojas. Una vez visualizada la hoja a la que queremos acceder, bastará con hacer clic sobre la etiqueta de ésta. Si todas las hojas del libro de trabajo caben en la barra, estos botones no tendrán ningún efecto. También se pueden utilizar combinaciones de teclas para realizar desplazamientos dentro del libro de trabajo, como pueden ser: MOVIMIENTO

TECLADO

Hoja Siguiente

CTRL+AVPAG

Hoja Anterior

CTRL+REPAG

Seguridad y protección en Excel En ocasiones necesitamos proteger nuestros archivos en Excel para impedir que nos modifiquen los datos, le agreguen o eliminen filas y columnas, etc. Se pueden proteger celdas, hojas de cálculo y libros completos, dependiendo de las necesidades de seguridad de cada usuario.

Página 11 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Proteger libro: Se puede proteger de dos formas. Para impedir que un usuario no autorizado pueda abrir el libro, y para permitir que los usuarios puedan abrir el libro pero que no puedan realizarle cambios.

El procedimiento es dar clic en el botón de office en la parte superior izquierda de la pantalla, elegir “Guardar como”, y en la parte inferior izquierda de la ventana que se abre, dar clic en “Herramientas”. Allí elegir “Opciones generales” que es donde se le asignan las contraseñas, ya sea para apertura, para escritura o para las dos.

Proteger hoja: Es la más sencilla de todas. Simplemente se da clic con el botón derecho del ratón sobre la pestaña de la hoja que se quiere proteger, y allí se despliega un menú en el que aparece la opción “Proteger hoja”.

Página 12 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Allí se pueden marcar las opciones que se pueden o no realizar en la hoja como insertar filas, columnas, aplicar formato, insertar hipervínculos, ordenar, seleccionar celdas, etc.

Proteger celdas: Todo lo que hay que hacer es seleccionar el rango de celdas que se quieren proteger y dar clic con el botón derecho del ratón, tras lo cual emerge un menú, en donde se elige la opción “Formato de celdas”. En la ventana que aparece, se elige la opción “Proteger” y se selecciona la opción “Bloqueada” u “Oculta”.

RANGO DE CELDAS SELECCIONADAS

La opción “Oculta”, se utiliza para ocultar las formulas que contienen las celdas, opción muy útil cuando se quiere evitar que los usuarios se enteren como se ha obtenido algún resultado. Para que las celdas se puedan proteger, la respectiva hoja de cálculo debe estar protegida, por tanto, lo primero que se debe hacer es proteger la hoja de cálculo.

Página 13 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Este tipo de seguridad es apropiada para usuarios que tengan pocos conocimientos sobre sistemas, ya que para usuarios avanzados les resultará fácil burlar este tipo de contraseñas.

IMPRESIÓN Vista preliminar La vista preliminar es una herramienta que nos permite visualizar nuestra hoja antes de imprimirla. Pero primero es aconsejable ver la Vista de Diseño de página para poder ver los saltos de página, márgenes, encabezados y pies de página, el formato completo de la hoja. Para visualizar la Vista de Diseño de página, seguir los siguientes pasos: Selecciona la pestaña Vista. Elige la opción Vista Diseño de página.

En el centro de la ventana aparecerá una página de nuestra hoja de cálculo. Podemos observar como en la parte inferior de la ventana nos informa que estamos visualizando la página 1 de un total de 2 páginas (Vista previa: Página 1 de 2). Desde esta vista podemos seguir trabajando con la hoja de cálculo como hasta ahora, la única diferencia es que sabremos cómo quedará en la hoja al imprimirla. Una vez terminado, para ver la vista preliminar de la hoja en una página utilizaremos la opción Vista preliminar que encontrarás en el Botón Office, haciendo clic en la flecha a la derecha de Imprimir.

Página 14 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

La página se mostrará de la siguiente manera:

En caso de tener más de una página, podremos cambiar la página a visualizar utilizando los botones: para ir a la página anterior. También podríamos pulsar la tecla RE PAG del teclado. para ir a la página siguiente. También podríamos pulsar la tecla AV PAG del teclado.

Página 15 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Si nuestra hoja sólo tiene 1 página estos botones estarán desactivados. Al situarnos sobre la hoja, dentro de vista preliminar, el puntero del ratón se convierte en una lupa, de tal forma que podremos ampliar o reducir cualquier zona de la página. Si nos situamos sobre cualquier parte de la página y hacemos clic, se ampliará la parte de la página donde nos encontramos situados. Y si volvemos a hacer clic se volverá a visualizar la página entera.

Esta operación se puede realizar también haciendo clic sobre el botón

Si la hoja de cálculo se encuentra preparada para la impresión, hacer clic sobre el botón para que aparezca el cuadro de diálogo Impresión.

Si la hoja de cálculo necesita alguna modificación de aspecto, hacer clic sobre el botón para que aparezca el cuadro de diálogo Configurar página.

En caso de desear volver a la hoja, hacer clic sobre el botón preliminar.

para cerrar la Vista

Cuando volvemos a nuestra hoja de cálculo, aparecerán unas líneas discontinuas que nos indicarán donde hará Excel 2007 los saltos de página, estas líneas no se imprimirán.

Configurar página Antes de imprimir una hoja de cálculo, Excel 2007 nos permite modificar factores que afectan a la presentación de las páginas impresas, como la orientación, encabezados y pies de página, tamaño del papel,... Si deseamos modificar algunos de los factores anteriores, desde la vista preliminar, deberemos hacer clic en el botón Configurar página... Aparecerá el cuadro de diálogo Configurar página, dicho cuadro consta de 4 fichas. La primera de las fichas se denomina Página y permite indicar características como la orientación del papel, el tamaño del papel que utilizamos y otros parámetros.

Página 16 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Selecciona la orientación del papel, vertical u horizontal. (En la impresora se colocará el papel siempre de la misma forma). En el recuadro Escala nos permitirá indicarle si deseamos que la salida a impresora venga determinada por un factor de escala (100%, 50%, 200%,...) o bien ajustando automáticamente la hoja en un número de páginas específico (una página de ancho por 1 de alto, así se imprimirá en una sola hoja,...). Podemos observar como en la parte derecha disponemos de un botón para ir a opciones específicas de la impresora seleccionada para imprimir. Para modificar los márgenes superior, inferior, derecho e izquierdo de las hojas a imprimir, utilizar la ficha Márgenes.

En esta ficha podremos modificar los márgenes superior, inferior, derecho e izquierdo de las hojas a imprimir. Si la hoja tiene encabezado o pie de página, también nos permite indicar a cuántos centímetros del borde del papel queremos que se sitúen.

Página 17 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Si quieres que tu salida tenga centradas las hojas tanto horizontal como verticalmente, Excel nos lo realizará automáticamente activando las casillas Horizontalmente y/o Verticalmente respectivamente. Para definir los encabezados y pies de páginas se utiliza la ficha Encabezado y Pie de página.

En esta ficha tenemos dos recuadros. En estos recuadros aparece una muestra del encabezado y pie de nuestra hoja utilizada para el ejemplo. En el recuadro Encabezado aparece el tipo de encabezado elegido, en este caso no hay ninguno. Pulsando la flecha de la derecha aparecerán posibles encabezados a utilizar. Para modificar el encabezado, hacer clic sobre el botón Personalizar encabezado... En el recuadro Pie de página aparece el tipo de pie de página elegido. Pulsando la flecha de la derecha aparecerán posibles pies de página a utilizar.

Para modificar el pie de página, hacer clic sobre el botón Personalizar pie de página... Dentro del cuadro de diálogo Configurar página tenemos la ficha Hoja que nos permite definir cómo queremos que se impriman los datos contenidos en la hoja.

Página 18 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

En Área de impresión: podremos indicar qué rango de celdas queremos imprimir. En Imprimir títulos podremos activar cualquiera de las siguientes opciones: •

Repetir filas en extremo superior para que en cada página que se imprima aparezca como título de columnas aquella fila que está indicada en ese recuadro.

•

Repetir columnas a la izquierda para que en cada página que se imprima aparezca como título de la fila aquella columna indicada en el recuadro.

En el recuadro Imprimir podremos activar cualquiera de las siguientes opciones: •

Líneas de división para imprimir las líneas que delimitan cada celda de la hoja.

•

Blanco y negro por si tenemos asignados colores en nuestra hoja y vamos a utilizar una impresora en blanco y negro o no queremos gastar el cartucho de colores.

•

Calidad de borrador para realizar una impresión rápida pero menos bonita de nuestra hoja. Sólo tendrá sentido si la impresora dispone de esta herramienta.

•

Encabezados de filas y columnas para imprimir los encabezados de filas (los números de filas de la izquierda) y columnas (las letras de los nombres de las columnas superiores) de la hoja.

Imprimir Una vez nuestra hoja esté preparada para imprimir, es aconsejable guardarla, y después, seguir los siguientes pasos: • •

Hacer clic sobre la opción Imprimir del Botón Office. Se abrirá un cuadro de diálogo desde donde podremos cambiar alguna opción de impresión como puede ser la impresora donde queremos que imprima o el número de copias deseadas.

Página 19 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

En el recuadro Impresora, aparecerá el nombre de la impresora que tenemos instalada en nuestro ordenador. En caso de desear cambiarla, hacer clic sobre la flecha derecha del Nombre para elegir otra impresora de la lista desplegable que se abrirá. Con el botón Propiedades podremos cambiar algunas características de la impresora. En el recuadro Intervalo de páginas, tendremos que indicar si deseamos imprimir Todas las páginas, o bien sólo un rango de Páginas, especificándolo en Desde y hasta En el recuadro Imprimir, podremos indicar si deseamos imprimir Todo el libro, o sólo las Hojas activas (seleccionadas o nuestra hoja activa), o bien, Selección para imprimir sólo los objetos seleccionados de la hoja. Por último en Número de copias podremos indicar el número de copias a realizar y si las queremos Intercalar. Podemos imprimir la hoja directamente sin pasar por este cuadro de diálogo haciendo clic en la opción Impresión Rápida que se encuentra en el desplegable Imprimir del Botón Office.

Página 20 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

INDICES MACRO PARA CREAR ÍNDICES EN EXCEL Paso 1: Insertar una nueva hoja de cálculo El primer paso será insertar una nueva hoja a nuestro libro la cual llamaremos Índice; en ella alojaremos en un módulo privado del objeto Sheet nuestra macro de Excel para crear un índice de hojas. Para crear nuestra nueva hoja tan solo es necesario hacer clic derecho con el ratón sobre alguna de las hojas ya existentes y en la lista de acciones que nos aparecerá seleccionar Insertar.

Una vez hayas hecho clic en la opción Insertar accederás al menú Insertar en el cual en la ficha General seleccionaremos la opción Hoja de Cálculo:

Recordar que también es posible insertar una hoja a través del método abreviado Mayus + F11 o haciendo clic en el botón insertar Hoja de Cálculo que se encuentra en la parte inferior de la pantalla, siempre al lado de la pestaña ña de la última hoja de cálculo del libro Excel, tal como se puede ver en la siguiente imagen:

Página 21 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 No olvidar cambiar el nombre por defecto de la hoja a Índice antes de continuar con el siguiente paso. Paso 2: Insertar macro de Excel en la nueva hoja Una vez creada la hoja índice procederemos a insertar nuestra macro de Excel que nos permitirá crear un índice de hojas de forma automática. Para ello haremos clic derecho sobre la pestaña de nuestra hoja Índice y en el menú de acciones que se despliega seleccionaremos seleccionaremos la opción Ver código:

Recordar que también podemos acceder al menú de VBA y macros de Excel manteniendo activa la hoja y tecleando el método abreviado Alt + F11. Una vez accedemos al menú de VBA y macros de Excel escribiremos el siguiente código c de Visual Basic, nuestra macro para crear el índice de hojas:

Página 22 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Una vez copiada la macro que nos permitirá crear el índice de hojas de forma automática podemos presionar Alt + Q para volver al libro y guardar los cambios. Para ejecutar el código solo es necesario que vayamos a otra hoja de cálculo y regresemos luego a nuestra hoja Índice. Cada vez que ingresemos a esta hoja se generará de forma automática nuestro índice. Nota Importante: Nuestra macro para crear un índice de las hojas emplea la celda A1 de cada hoja en tu libro de cálculo por lo que si tenemos datos importantes en esta celda deberemos modificarla para que no perdamos información. Para hacerlo deberemos cambiar cualquier mención a A1 en el código por alguna celda que consideremos más adecuada. RESULTADO DEL ÍNDICE:

Autorrelleno y series Para escribir datos de forma rápida, puede configurar Microsoft Office Excel de manera que repita los datos o puede rellenar los datos de manera automática.

Repetir automáticamente elementos ya escritos en la columna Si los primeros caracteres que se escriben en una celda coinciden con una entrada existente en esa columna, Microsoft Excel completará automáticamente los caracteres restantes. Excel sólo completa automáticamente las entradas que contengan texto o una combinación de texto y números. Las entradas que contengan sólo números, fechas u horas no se completan automáticamente.

Página 23 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Seguir uno de los procedimientos siguientes: •

Para aceptar una entrada propuesta, presionar ENTRAR.

La entrada completa coincide exactamente con el modelo de mayúsculas y minúsculas de la entrada existente. •

Para reemplazar los caracteres escritos automáticamente, continuar escribiendo.

•

Para eliminar los caracteres escritos automáticamente, presionar RETROCESO.

Notas •

Si no deseamos que las entradas que escribamos se completen automáticamente, podemos desactivar esta opción.

Cómo activar o desactivar la finalización automática de valores de celda 1. Hacer clic en el botón de Microsoft Office Excel.

y, a continuación, hacer clic en Opciones de

2. Para activar o desactivar la finalización automática de valores de celda, haga clic en Avanzadas y, a continuación, bajo Opciones de edición, active o desactive la casilla de verificación Habilitar Autocompletar para valores de celda.

•

Excel completará las entradas sólo cuando el punto de inserción esté al final del contenido de la celda activa.

Página 24 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 •

Excel basa la lista de entradas posibles de Autocompletar en la columna que contiene la celda activa (celda activa: celda seleccionada en la que se escriben datos cuando comienza a escribir. Sólo una celda está activa cada vez. La celda activa tiene un borde grueso.). Las entradas que se repiten en una fila no se completan automáticamente.

Utilizar el controlador de relleno para rellenar datos Se puede utilizar el comando Rellenar para rellenar datos en las celdas de una hoja de cálculo. También se puede configurar Excel para que continúe automáticamente una serie de números, combinaciones de números y texto, fechas o períodos de tiempo, en función de un modelo establecido. Sin embargo, para rellenar rápidamente varios tipos de series de datos, seleccione las celdas y arrastre el controlador de relleno (control de relleno: pequeño cuadrado negro situado en la esquina superior derecha de la selección. Cuando se sitúa el puntero del mouse sobre el controlador de relleno, el puntero cambia a una cruz negra.) El controlador de relleno se muestra de forma predeterminada, pero se puede ocultar si se desea. Cómo mostrar u ocultar el controlador de relleno •

Haga clic en el botón de Microsoft Office Excel.

•

Para mostrar u ocultar el controlador de relleno, haga clic en Avanzadas y, a continuación, bajo Opciones de edición, active o desactive la casilla de verificación Permitir arrastrar y colocar el controlador de relleno y las celdas.

•

Para evitar reemplazar datos existentes al arrastrar el controlador de relleno, asegúrese de que la casilla de verificación Mensaje de alerta antes de reemplazar celdas está activada. Si no desea recibir un mensaje sobre la sobrescritura de celdas no vacías, desactive esta casilla de verificación.

Página 25 VICENTA MEGIA MOLERO

y, a continuación, haga clic en Opciones de

Microsoft Office Excel 2007 Tras arrastrar el controlador de relleno, aparecerá el botón Opciones de autorrelleno para elegir el modo en que se rellenará la selección. Por ejemplo, para rellenar sólo los formatos de celda, hacer clic en Rellenar formatos sólo y para rellenar sólo el contenido de la celda, hacer clic en Rellenar sin formato.

Si no desea que aparezca el botón Opciones de autorrelleno cada vez que arrastre el controlador de relleno, puede desactivarlo. Cómo activar o desactivar Opciones de autorrelleno •

Haga clic en el botón de Microsoft Office de Excel.

•

Haga clic en Avanzadas y, a continuación, bajo Cortar, copiar y pegar, desactive la casilla de verificación Mostrar botones de opciones de pegado.

y, a continuación, haga clic en Opciones

Rellenar datos en celdas adyacentes Puede utilizar el comando Rellenar para rellenar la celda activa o el rango seleccionado con el contenido de una celda o rango adyacente, o bien, rellenar rápidamente las celdas adyacentes arrastrando el controlador de relleno (control de relleno: pequeño cuadrado negro situado en la esquina superior derecha de la selección. Cuando se sitúa el puntero del mouse sobre el controlador de relleno, el puntero cambia a una cruz negra.)

Página 26 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Rellenar la celda activa con el contenido de una celda adyacente 1. Seleccione una celda vacía debajo, a la derecha, por encima o a la izquierda de la celda que contiene los datos que va a utilizar para rellenar dicha celda. 2. En el grupo Modificar de la ficha Inicio, haga clic en Rellenar y, a continuación, en Abajo, Derecha, Arriba o Izquierda.

Sugerencia Para rellenar una celda rápidamente con el contenido de la celda situada encima o a la izquierda, puede presionar CTRL+J o CTRL+D. Arrastrar el controlador de relleno para rellenar datos en celdas adyacentes • • •

Seleccione las celdas que contienen los datos con los que va a rellenar las celdas adyacentes. Arrastre el controlador de relleno por las celdas que desea rellenar. Para elegir la forma de rellenar la selección, haga clic en Opciones de autorrelleno continuación, haga clic en la opción que desea usar.

y, a

Nota: Si arrastramos el controlador de relleno hacia arriba o hacia la izquierda de una selección y se detiene en las celdas seleccionadas sin pasar por la primera columna o por la fila superior de la selección, Excel eliminará los datos que haya dentro de la selección. Deberemos arrastrar el controlador de relleno fuera del área seleccionada antes de soltar el botón del mouse (ratón). Rellenar fórmulas en celdas adyacentes •

Seleccione la celda que contiene la fórmula con la que desea rellenar las celdas adyacentes.

•

Arrastre el controlador de relleno (control de relleno: pequeño cuadrado negro situado en la esquina superior derecha de la selección. Cuando se sitúa el puntero del mouse sobre el controlador de relleno, el puntero cambia a una cruz negra.) rellenar.

•

por las celdas que desea

Para elegir la forma de rellenar la selección, haga clic en Opciones de autorrelleno continuación, haga clic en la opción que desea usar.

y, a

Sugerencias: •

También se puede rellenar la celda activa con la fórmula de una celda adyacente. Para ello, utilizaremos el comando Rellenar (en la ficha Inicio, en el grupo Modificar), o presione CTRL+J o CTRL+D para rellenar una celda situada debajo o a la derecha de la celda que contiene la fórmula.

Página 27 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 •

Puede rellenar automáticamente una fórmula hacia abajo, para todas las celdas adyacentes a las que se aplica, haciendo doble clic en el controlador de relleno de la primera celda que contiene la formula. Por ejemplo, tiene números en las celdas A1:A15 y B1:B15, y escribe la fórmula =A1+B1 en la celda C1. Para copiar esta fórmula en las celdas C2:C15, seleccione la celda C1 y haga doble clic en el controlador de relleno.

Rellenar una serie de números, fechas u otros elementos de serie integrados Con el controlador de relleno (control de relleno: pequeño cuadrado negro situado en la esquina superior derecha de la selección. Cuando se sitúa el puntero del mouse sobre el controlador de relleno, el puntero cambia a una cruz negra.) ,podemos llenar rápidamente las celdas de un rango con una serie de números o fechas, o con una serie integrada para días, días de la semana, meses o años. 1. Seleccione la primera celda del rango que desea rellenar. 2. Escriba el valor inicial de la serie. 3. Escriba un valor en la siguiente celda para establecer un modelo. Por ejemplo, si desea utilizar la serie 1, 2, 3, 4, 5,..., escriba 1 y 2 en las primeras dos celdas. Si desea usar la serie 2, 4, 6, 8,..., escriba 2 y 4. Si desea que la serie sea 2, 2, 2, 2,..., puede dejar en blanco la segunda celda. Otros ejemplos de series que se puede rellenar Al rellenar una serie, las selecciones se extienden como se muestra en la tabla siguiente. Los elementos separados por puntos y coma se colocan en celdas adyacentes individuales.

Selección inicial

Serie extendida

1, 2, 3

4, 5, 6,...

9:00

10:00, 11:00, 12:00,...

lun

mar, mié, jue,...

Lunes

martes, miércoles, jueves,...

ene

feb, mar, abr,...

ene, abr

jul, oct, ene,...

Ene-99, Abr-99

jul-99, oct-99, ene-00,...

15-ene, 15-abr

15-jul, 15-oct,...

1999, 2000

2001, 2002, 2003,...

Página 28 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 1-ene, 1-mar

1-may, 1-jul, 1-sep,...

Trim3 (o T3 o Trimestre3)

Trim4, Trim1, Trim2,...

texto1, textoA

texto2, textoA, texto3, textoA,...

1er período

2do período, 3er período,...

Producto 1

Producto 2, Producto 3,...

4. Seleccione la celda o las celdas que contengan los valores iniciales. 5. Arrastre el controlador de relleno

por el rango que desea rellenar.

Para rellenar en orden ascendente, arrastre hacia abajo o hacia la derecha. Para rellenar en orden descendente, arrastre hacia arriba o hacia la izquierda. Sugerencias: •

También podemos especificar el tipo de serie utilizando el botón secundario del mouse para arrastrar el controlador de relleno sobre el rango y después hacer clic en el comando adecuado del menú contextual (menú contextual: menú que muestra una lista de comandos relacionados con un elemento determinado. Para mostrar un menú contextual, haga clic con el botón secundario del mouse en el elemento, o presione MAYÚS+F10.). Por ejemplo, si el valor inicial es la fecha ENE-2002, haga clic en Rellenar meses para la obtener la serie FEB2002, MAR-2002, etc. También puede hacer clic en Rellenar años para obtener la serie ENE2003, ENE-2004, etc.

•

Si la selección contiene números, podemos controlar el tipo de serie que desea crear.

Cómo rellenar celdas con una serie de números 1. En el grupo Modificar de la ficha Inicio, haga clic en Rellenar y, a continuación, en Series.

2. En Tipo, haga clic en una de las opciones siguientes: •

Haga clic en Lineal para obtener una serie que se calcula agregando el valor del cuadro Incremento a cada valor de celda.

•

Haga clic en Geométrica para crear una serie que se calcula multiplicando el valor del cuadro Incremento por cada valor de celda.

Página 29 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

•

Haga clic en Cronológica para crear una serie que rellena valores de fecha incrementalmente por el valor del cuadro Incremento y que depende de la unidad definida en Unidad de tiempo.

•

Haga clic en Autorrellenar para obtener una serie que produce los mismos resultados que arrastrar el controlador de relleno.

La función Autorrellenar se puede suprimir manteniendo presionada tecla CTRL mientras se arrastra el controlador de relleno de una selección de dos o más celdas. Los valores seleccionados se copian en las celdas adyacentes y Excel no extiende una serie.

Rellenar datos mediante una serie de relleno personalizada Para facilitar la especificación de una secuencia determinada de datos (como una lista de nombres o zonas de venta), podemos crear una serie de relleno personalizada. Las series de relleno personalizadas pueden basarse en una lista de elementos existentes en una hoja de cálculo o puede crearse la lista desde cero. No es posible modificar ni eliminar una serie de relleno integrada (como una serie de relleno para meses y días), pero se puede modificar o eliminar una serie de relleno personalizada. Nota Una lista personalizada sólo puede contener texto o texto combinado con números. Para crear una lista personalizada que contenga sólo números, por ejemplo de 0 a 100, deberemos crear en primer lugar una lista de números con formato de texto. Cómo dar formato de texto a números 1. Seleccione suficientes celdas para la lista de los números a los que va a dar formato de texto. 2. En el grupo Número de la ficha Inicio, haga clic en la flecha del cuadro Formato de número y, a continuación, en Texto.

Página 30 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Tal vez sea necesario hacer clic en Más para ver la opción Texto. 3. En las celdas con formato, escriba la lista de números.

Utilizar una serie de relleno personalizada basada en una lista existente de elementos 1. En la hoja de cálculo, seleccione la lista de elementos que desea utilizar en la serie de relleno. 2. Haga clic en el botón de Microsoft Office Excel.

y, a continuación, haga clic en Opciones de

3. Haga clic en Más frecuentes y, a continuación, bajo Opciones principales para trabajar con Excel, haga clic en Modificar listas personalizadas.

Página 31 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 4. Compruebe que la referencia de celda de la lista de elementos seleccionada aparece en el cuadro Importar lista desde las celdas y, a continuación, haga clic en Importar.

Los elementos de la lista seleccionados se agregarán al cuadro Listas personalizadas.

5. Haremos clic dos veces en Aceptar. 6. En la hoja de cálculo, haremos clic en una celda y escribiremos el elemento de la serie de relleno personalizada que desea utilizar para comenzar la lista. 7. Arrastraremos el controlador de relleno (control de relleno: pequeño cuadrado negro situado en la esquina superior derecha de la selección. Cuando se sitúa el puntero del mouse sobre el controlador de relleno, el puntero cambia a una cruz negra.) que desea rellenar.

por las celdas

Utilizar una serie de relleno personalizada basada en una lista nueva de elementos 1. Haga clic en el botón de Microsoft Office Excel.

y, a continuación, haga clic en Opciones de

2. Haga clic en Más frecuentes y, a continuación, bajo Opciones principales para trabajar con Excel, hacer clic en Modificar listas personalizadas. 3. En el cuadro Listas personalizadas, haremos clic en NUEVA LISTA y, a continuación, escriba las entradas en el cuadro Entradas de lista, comenzando por la primera entrada. Presione ENTRAR después de cada entrada. 4. Cuando la lista esté completa, haga clic en Agregar y, a continuación, haga clic dos veces en Aceptar. 5. En la hoja de cálculo, haga clic en una celda y escriba el elemento de la serie de relleno personalizada que desea utilizar para comenzar la lista.

Página 32 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 6. Arrastre el controlador de relleno

por las celdas que desea rellenar.

Modificar o eliminar una serie de relleno personalizada 1. Haga clic en el botón de Microsoft Office Excel.

y, a continuación, haga clic en Opciones de

2. Haga clic en la categoría Más frecuentes y, a continuación, bajo Opciones principales para trabajar con Excel, haga clic en Modificar listas personalizadas.

3. En el cuadro Listas personalizadas, seleccione la lista que va a modificar o eliminar y siga uno de los procedimientos siguientes: •

Para modificar la serie de relleno, realice los cambios que considere oportunos en el cuadro Entradas de lista y, a continuación, haga clic en Agregar.

•

Para eliminar la serie de relleno, haga clic en Eliminar.

Página 33 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

EJERCICIOS BÁSICOS DE EXCEL FUNCIÓN PROMEDIO El siguiente ejercicio nos enseña a cómo sacar un promedio general de un alumno y sus materias. Recordando siempre que si el alumno lleva 3 materias, la fórmula sería sumar el promedio de las tres materias y dividirlo entre 3 que son el total de materias que se están sumando. Para sacar el promedio general de Ernesto, es necesario sumar las tres calificaciones y dividirla entre 3 (porque son tres calificaciones), el resultado sería el promedio general del alumno. ¿Por qué se utilizan paréntesis? Los paréntesis indican que primero se sumen las tres calificaciones y por último que se divida entre tres. Si no se ponen los paréntesis afectaría al resultado, porque Excel primero dividiría D2 entre 3 y posteriormente sumaría a C2 y B2, el resultado sería incorrecto. Para insertar la función seleccionamos la celda y pulsamos en la pestaña Fórmulas/ Insertar función:

Una vez hayamos seleccionado la función promedio seleccionaremos el rango de celdas sobre el que queremos calcular el promedio:

Página 34 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

FUNCIÓN SUMA Si deseamos sumar un grupo de celdas sería muy complicado hacer una fórmula, por ejemplo sumar 20 celdas. Para ello utilizaremos la función SUMA. En el ejemplo se deben sumar todas las cantidades, es decir, desde la columna A hasta la columna D. Una forma sería con la fórmula siguiente: =A1+A2+A3+A4+A5+A6+B1+B2+B3+B4+B5+B6+C1+C2+C3+C4+C5+C6+D1+D2+D3+D4+D5+D6 Con la función SUMA se haría: =SUMA(A1:D6)

Página 35 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Para sumar tenemos varias formas, lo podemos hacer a través de pulsando en este botón y seleccionando el rango de celdas que queramos sumar o bien insertando la función SUMA a través de

, o en la pestaña Fórmulas/Insertar función.

FUNCION SUMAR.SI Con esta función se puede sumar por medio de una condición. Para ir a esta función procederemos como siempre a través de

o en la ficha Fórmulas/Insertar función:

Página 36 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Con esta función indico que tome en cuenta todos los pantalones desde A2 hasta A6; indico que sólo los LEVIS voy a sumar, indicando que el precio se encuentra desde B2 hasta B6. El resultado será la suma de todos los LEVIS. De igual manera sumaremos todos los pantalones de la marca TOMY.

FUNCION MAX, MIN Estas funciones detectan el valor más alto y el valor más bajo de una tabla de datos.

Siempre para calcular cualquier forma deberemos decirle cual es el rango de celdas o las celdas que queremos tener en cuenta en el cálculo.

Página 37 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

CÁLCULO DEL PORCENTAJE EN EXCEL Si desea extraer un tanto por ciento de una cantidad, debes multiplicar, de la misma forma si vas a sumar o restar un por ciento, debes de multiplicar. Vamos a ver cómo sacar, sumar y restar un por ciento por medio de una fórmula. Sacar el 10% Sumar el 10% Restar el 10% Recuerda que el sacar un por ciento es extraer el por ciento de una cantidad. Para sumar siempre se multiplica por 1 y por el porcentaje que deseas agregar. Para restar el 10%, ¿cuánto te falta para llegar al 100?, 90, por ello hay que multiplicar por 0,90, utilizando siempre el número 100 como base. SACAR POR CIENTO

Página 38 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 SUMAR POR CIENTO

RESTAR POR CIENTO

FUNCIÓN CARÁCTER Tabla de caracteres ANSI, códigos ANSI. Del número 1 al 31 son códigos que no se transcriben porque son cambios de línea,… El número 32 es un espacio en blanco por eso aparece vacío en la imagen. Para crear la tabla escribiremos en Código los números hasta el 255, sólo necesitaremos escribir los dos primeros y seleccionándolos, rellenaremos los demás a través del autorrelleno. Centraremos los números. En la celda de carácter escribiremos =CARÁCTER (número de la celda) y nos aparecerá el símbolo que le corresponde. Arrastrando hacia abajo sacaremos los demás con el autorrelleno.

Página 39 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Si nos situamos en cualquier celda y escribimos =CARÁCTER (128) nos aparecerá el símbolo del euro

€, al igual que si pulsamos ALT + 0128.

FORMATO CONDICIONAL

Para ver cómo funciona el formato condicional vamos a hacer dos ejemplos, en el primero vamos a crear una tabla con una lista de clientes los cuales tendrán un saldo pendiente y como formato condicional vamos a utilizar un semáforo, el cual será de un color u otro dependiendo de la cantidad del saldo pendiente. El resultado final es el siguiente:

Página 40 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Los pasos que he seguido para crearla han sido los siguientes: Primero aplicaremos a Id Cliente el formato de celda texto para que nos pueda aparecer 001, para ello haremos en la celda un clic derecho y seleccionaremos Formato de celdas y en la pestaña Número seleccionaremos la categoría Texto:

Después escribiremos el nombre y los apellidos de los clientes y el saldo pendiente, a las celdas del saldo le aplicaremos un formato de celdas moneda. Para aplicar el formato moneda seleccionaremos las celdas y pulsaremos en la ficha Inicio/Número/Moneda . O haciendo un clic derecho sobre una de las celdas y seleccionaremos Formato de celdas/Número/Moneda donde elegiremos el símbolo del euro y el número de decimales que queremos que aparezcan:

Página 41 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Una vez tengamos la tabla preparada tendremos que seleccionar el rango de celdas al que le queremos aplicar el formato condicional que en este caso son las del saldo pendiente. Iremos a Inicio/Estilos/Formato condicional/Conjunto de iconos y seleccionaremos el semรกforo sin marco:

Una vez hecho esto aparecerรกn los semรกforos en las celdas del saldo pendiente pero ahora tendremos que aplicarle las condiciones y para ello iremos a Formato condicional/Administrar reglas/Editar reglas:

Pรกgina 42 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Nos aparecerá el cuadro de diálogo Administrador de reglas de formato condicionales donde seleccionaremos Editar regla:

Nos aparecerá el cuadro de diálogo Editar regla de formato donde seleccionaremos como tipo de regla Aplicar formato a todas las celdas según sus valores. En editar una descripción de regla escribiremos los valores y como tipo Número. Para que los valores se ajusten a los colores que queremos activaremos la opción Invertir criterio de ordenación de icono:

Página 43 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Si quisiéramos borrar las reglas sólo tendríamos que ir a Formato condicional/Borrar reglas. En el segundo ejemplo vamos a crear una tabla con los alumnos y sus notas donde resaltaremos las reglas de las celdas igual a SOBRESALIENTE. Para ello escribiremos los datos e iremos a Inicio/Formato condicional/Resaltar reglas de celdas:

Nos aparecerá un cuadro donde escribiremos la condición y seleccionaremos el formato de la celda que en este caso elegiremos Relleno rojo claro con texto rojo oscuro:

Podremos insertar un comentario a la celda que hemos resaltado, para ello podemos hacer un clic derecho en la derecha y elegir la opción Insertar comentario o ir a la ficha Revisar/Nuevo comentario. Para que el comentario sea visible iremos a Botón Office/Opciones de Excel/Avanzadas/Mostrar y activaremos la casilla Comentarios e indicadores.

Página 44 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

La tabla quedará de la siguiente manera:

EJERCICIO PASO A PASO DEL FORMATO CONDICIONAL 1. 2. 3. 4. 5.

Si no tienes abierto Excel 2007, ábrelo para realizar el ejercicio. Abre un nuevo libro de trabajo. Selecciona la celda A5. Accede a la pestaña Inicio. Selecciona el menú Formato condicional y selecciona la opción Nueva regla.

Página 45 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

6. 7. 8. 9. 10. 11. 12. 13. 14.

Aparecerá el cuadro de diálogo Nueva regla de formato. Seleccionaremos el tipo de regla Aplicar formato únicamente a las celdas que contengan… En el primer cuadro combinado escoge la opción Valor de la celda. En el segundo recuadro selecciona entre. En el tercer recuadro escribe 50. En el último recuadro escribe 250. Pulsa sobre Formato… En el cuadro de diálogo Formato de celdas, en la pestaña Fuente en Estilo selecciona Negrita cursiva. En Color selecciona el color Marrón. En la pestaña Relleno selecciona el color gris claro. Pulsa el botón Aceptar en los dos cuadros de diálogo. Vamos a crear otra regla para esta celda.

Página 46 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

15. Vuelve al cuadro de diálogo Nueva regla de formato seleccionando la opción Nueva regla. 16. En el primer cuadro combinado escoge la opción Valor de la celda. 17. En el segundo recuadro seleccionar entre. 18. En el tercer recuadro escribe 251. 19. En el último recuadro escribe 300. 20. Pulsa sobre Formato… 21. En el cuadro de diálogo Formato de celdas, en la pestaña Fuente en Estilo selecciona Negrita. 22. En Color selecciona el color azul fuerte. 23. Pulsa el botón Aceptar en los dos cuadros de diálogo. Vamos a comprobar su funcionamiento.

24. Sitúate en la celda A5. 25. Escribe el número 5. La celda debe quedar tal cual estaba, el valor no cumple ninguna de las dos condiciones incluidas en el formato condicional.

Página 47 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 26. Ahora escribe en la celda A5 el número 120. Ahora se debe de haber activado el formato condicional y el número aparecerá en negrita, cursiva de color Marrón con fondo gris.

27. Ahora escribe en la celda A5 el número 300. El número aparecerá en negrita, de color azul y sin fondo.

28. 29. 30.

31.

Ahora eliminaremos el formato condicional. Haz clic en la pestaña Inicio. Accede al menú Formato condicional. Selecciona la opción Administrar reglas… Se abrirá un cuadro de diálogo donde podrás ver todas las reglas creadas para la selección, o para cualquiera de las hojas. Selecciona la Condición 1 y pulsa sobre Eliminar. La regla dejará de actuar.

32. Pulsa sobre Aceptar para aplicar los cambios. El número 300 sigue apareciendo con el formato, pero si escribes en la celda el número 120, ya no aparecerá con el formato, hemos eliminado la primera condición del formato.

Página 48 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 33. Cerrar el libro sin guardar cambios. Para salir pulsaremos con el ratón con clic sobre o iremos a botón de Office/ Cerrar.

ENCONTRAR DATOS DIFERENTES Introduciremos los siguientes datos:

Las columnas son prácticamente idénticas, excepto por algunas filas donde los datos no coinciden. Para identificarlos: 1. Seleccionamos el rango con los datos A3:B11 2. Abrimos el menú Ir a … (CTRL + i es la forma más fácil) 3. Hacemos clic en Especial… y seleccionamos Diferencia entre filas. Con esto indicamos a Excel que queremos encontrar aquellos datos en cada fila que son diferentes.

Página 49 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 4. Clic en Aceptar, Excel seleccionará todas las celdas de la segunda columna que no coinciden con la primera.

BARRA DE DESPLAZAMIENTO Insertar los siguientes datos:

El control de barra de desplazamiento Programador/Insertar/Controles de formulario.

encuentra

dentro

pestaña

Aunque sea vertical, si dibujamos el control en forma horizontal, Excel interpretará que ésta es la orientación que queremos darle. Con el ratón sobre la letra de la columna A, hacemos clic derecho/Ancho de columna e ingresamos por ejemplo 30. Haciendo clic y dejando oprimido el ratón, dibujamos el control a la altura de la fila 12.

Página 50 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Tenemos ahora una barra que, si oprimimos las flechas de los extremos, se moverá la barra lentamente, y si oprimimos en el área dentro de la barra, pegará saltos más altos. Para entender cuáles son los parámetros y cómo podemos empezar a hacer que haga algo, la seleccionamos con el botón derecho y seleccionamos Formato de control. A continuación seleccionamos el Formato de Control con el botón derecho del ratón:

Vincularemos con la celda D2, al introducir un valor nuevo la barra de desplazamiento cambiará. Es decir funciona de ida y vuelta. Si se modifica la celda, se mueve la barra. Si se cambia el indicador en la barra, cambia el valor de la celda.

Página 51 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Ahora vamos a insertar un gráfico con el área C1:D3 y dentro de la pestaña Insertar seleccionaremos el gráfico Columna apilada. Nos mostrará dos barras, una al lado de la otra, al 100%. Haremos clic el Cambiar filas y columnas (que se encuentra en la pestaña Diseño dentro de Herramientas de gráficos) y tendremos el gráfico tal y como aparece en la imagen. Arrastraremos el gráfico a la columna A y ahora cada vez que interactuemos con la barra, cambiará el resultado del gráfico. Ejemplo 1

Ejemplo 2

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 fórmula.

Página 52 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Las referencias son enlaces a un lugar, es decir, cuando en una fórmula 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. Supongamos el ejemplo: A

=A1+2

3 Si ahora copiamos la celda A2 en B3, como la copiamos una columna hacia la derecha y en una fila hacia abajo, la fórmula cambiará por: =B2+2. Lo que variará es la referencia a la celda A1, al copiarla una columna hacia la derecha se incrementará el nombre de la columna en uno, es decir, en vez de A pondrá B y al copiarla una fila hacia abajo en vez de fila 1 pondrá 2, resultado =B2+2. Para mantener en la fórmula sumar 2 al contenido de la celda superior.

Referencia Absoluta Las referencias de filas y columnas no cambian si se copia la fórmula a otra celda, las referencias a las celdas de la formula son fijas.

Supongamos el ejemplo: A

=$A$1+2

3 Si ahora copiamos la celda A2 en B3 , aunque la copiemos una columna hacia la derecha y en una fila hacia abajo, como delante de la columna y delante de la fila encuentra en signo $ no variará la fórmula y en B3 pondrá =$A$1+2 . Para asignar a una celda la referencia absoluta la seleccionaremos y pulsaremos F4.

Página 53 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Referencia Mixta Podemos hacer una combinación de ambas referencias, podemos hacer que las filas sean relativas y las columnas absolutas o viceversa. Supongamos el ejemplo: A

=$A1+2

3 Si ahora copiamos la celda A2 en B3 , como hay un signo $ delante de la columna aunque se copie una columna más a la derecha ésta no variará, pero al no tener el signo $ delante de la fila, al copiarla una fila hacia abajo la fila cambiará por 2 en vez de 1 y el resultado será =$A2+2 . Supongamos el ejemplo: A

=A$1+2

3 Si ahora copiamos la celda A2 en B3 , como hay un signo $ delante de la fila aunque se copie una fila hacia abajo ésta no variará, pero al no tener el signo $ delante de la columna, al copiarla una columna más a la derecha la columna cambiará por B en vez de A y el resultado será =B$1+2 .

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

Página 54 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Otra opción, en lugar de escribirlo a mano es hacerlo cuando estemos editando la fórmula, en el 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: Fijarse bien que al escribir una cadena de caracteres que incluya espacios debemos ponerlo siempre entre comillas simples ‘’. Fuente: www.aulaclic.es

EJERCICIO CON REFERENCIAS En este ejercicio vamos a ver como calcular los gastos e ingresos de un representante y después los reflejaremos en un gráfico. Hoja de gastos: Los datos en negrita son los que hemos calculado. Para calcular los euros por kilómetro hemos puesto una referencia absoluta a la celda B4 pues será fija para el cálculo de todos los días de la semana, lo calcularemos para el lunes y arrastraremos la fórmula hasta el viernes. Los demás cálculos los hemos hecho a través de la Autosuma ∑ seleccionando las celdas que queremos sumar. En color verde están las celdas con referencia absoluta y en amarillo las celdas calculadas por Autosuma.

Página 55 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Hoja de ingresos: En la hoja de ingresos dejaremos la celda B4 como referencia absoluta pues la comisión será fija para todos los cálculos. Las celdas calculadas son las que se encuentran en negrita. Las celdas que se encuentran dentro del cuadro verde contienen la referencia absoluta de la comisión y las de color amarillo han sido calculadas a través de la AutosumaΣ Σ, seleccionando en cada caso las celdas o rango de celdas a sumar.

Hoja resumen:

Página 56 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Vamos a hacer el cálculo total del gasto semanal y lo representaremos en un gráfico circular, para ello relacionaremos las hojas de gastos e ingresos. Para relacionar varias hojas escribiremos =NOMBRE DE LA HOJA!CELDA . INTRO directamente en la hoja donde tomemos los datos

Los totales los calcularemos a través de la función Autosuma. Para crear el gráfico primero seleccionaremos que datos queremos que nos refleje dicho gráfico que en este caso serán las celdas de Lunes a Viernes (B5:F5) y las celdas del líquido (B8:F8) e iremos a Insertar/Gráficos/Circular y seleccionaremos el que nos interese en ese momento. En este caso al tratarse solamente de un dato (Líquidos diarios) seleccionaremos el circular. Una vez lo hayamos creado podremos modificarle el formato de leyenda, el formato del área del gráfico, el formato del título del gráfico o los colores del gráfico. Todo ello haciendo un clic derecho y seleccionando la opción Formato de… Si queremos cambiar el título del gráfico sólo tendremos que hacer doble clic dentro del título y aparecerá el cursor parpadeando para que podamos escribir lo que queramos, en este caso hemos puesto Líquidos diarios.

GRÁFICOS Introducción Un gráfico es la representación gráfica de los datos de una hoja de cálculo y facilita su interpretación. Vamos a ver en esta unidad, cómo crear gráficos a partir de unos datos introducidos en una hoja de cálculo. La utilización de gráficos hace más sencilla e inmediata la interpretación de los datos. A menudo un gráfico nos dice mucho más que una serie de datos clasificados por filas y columnas.

Página 57 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Cuando se crea un gráfico en Excel, podemos optar por crearlo: - Como gráfico incrustado: Insertar el gráfico en una hoja normal como cualquier otro objeto. - Como hoja de gráfico: Crear el gráfico en una hoja exclusiva para el gráfico, en las hojas de gráfico no existen celdas ni ningún otro tipo de objeto. Veamos cómo crear de un gráfico.

Crear gráficos Para insertar un gráfico tenemos varias opciones, pero siempre utilizaremos la sección Gráficos que se encuentra en la pestaña Insertar.

Es recomendable que tengamos seleccionado el rango de celdas que queramos que participen en el gráfico, de esta forma, Excel podrá generarlo automáticamente. En caso contrario, el gráfico se mostrará en blanco o no se creará debido a un tipo de error en los datos que solicita. Como puedes ver existen diversos tipos de gráficos a nuestra disposición. Podemos seleccionar un gráfico a insertar haciendo clic en el tipo que nos interese para que se despliegue el listado de los que se encuentran disponibles. En cada uno de los tipos generales de gráficos podremos encontrar un enlace en la parte inferior del listado que muestra Todos los tipos de gráfico... Hacer clic en esa opción equivaldría a desplegar el cuadro de diálogo de Insertar gráfico que se muestra al hacer clic en la flecha de la parte inferior derecha de la sección Gráficos.

Aquí podemos ver listados todos los gráficos disponibles, seleccionaremos uno y pulsaremos en Aceptar para empezar a crearlo.

Página 58 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Si seleccionamos un rango de celdas veremos el nuevo gráfico inmediatamente y lo insertará en la hoja de cálculo con las características predeterminadas del gráfico escogido. Si hemos decidido probar suerte y no teníamos celdas seleccionadas, deberemos seguir leyendo los siguientes apartados.

Añadir una serie de datos Este paso es el más importante de todos ya que en él definiremos qué datos queremos que aparezcan en el gráfico. Una vez tengamos un gráfico sobre la hoja de cálculo, aparecerán nuevas pestañas para mostrarnos nuevas opciones. Si observamos la pestaña Diseño encontraremos dos opciones muy útiles:

Primero nos fijaremos en el botón Seleccionar datos. Desde él se abre el siguiente cuadro de diálogo:

Observa detenidamente el contenido de esta ventana. Como ya hemos dicho es la más importante porque se encargará de generar el gráfico. Así pues tenemos un campo llamado Rango de datos del gráfico donde podremos seleccionar el rango de celdas que se tomarán en cuenta para crearlo. En el caso de la imagen, las celdas que se tomaron eran 5 y tenían los valores 445, 453, 545, 453 y 345. Pulsa el botón rango correcto.

y selecciona las celdas, automáticamente se rellenará el campo de texto con el

Una vez hayamos acotado los datos que utilizaremos, Excel asociará unos al eje horizontal (categorías) y otros al eje vertical (series). Ten en cuenta que hay gráficos que necesitan más de dos series para poder crearse (por ejemplo los gráficos de superficie), y otros en cambio, (como el que ves en la imagen) se bastan con uno solo. Utiliza el botón Editar de las series para modificar el literal que se muestra en la leyenda del gráfico. Del mismo modo también podremos modificar el rango de celdas que se incluirán tanto en las series como en las categorías. Haciendo clic en el botón Cambiar fila/columna podremos permutar los datos de las series y pasarlas a las categorías y viceversa. Este botón actúa del mismo modo que el que podemos encontrar en la banda de opciones Cambiar entre filas y columnas (pestaña Diseño).

Página 59 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Si hacemos clic en el botón Celdas ocultas y vacías abrirás un pequeño cuadro de diálogo desde donde podrás elegir qué hacer con las celdas que no tengan datos o estén ocultas.

Modificar las características del gráfico En la pestaña Presentación podremos encontrar todas las opciones relativas al aspecto del gráfico. Por ejemplo, podremos decidir que ejes mostrar o si queremos incluir una cuadrícula de fondo para poder leer mejor los resultados. Todo esto lo encontraremos en la sección Ejes:

Utilizaremos las opciones de la sección Etiquetas para establecer qué literales de texto se mostrarán en el gráfico:

De todas formas, recuerda que podemos seleccionar las etiquetas dentro del gráfico y arrastrarlas para colocarlas en la posición deseada. Desde esta sección también podremos configurar la Leyenda del gráfico. Finalmente destacaremos las opciones de la sección Fondo que nos permitirán modificar el modo en el que se integrará el gráfico en el cuadro de cálculo.

La primera opción Área de trazado, sólo estará disponible para los gráficos bidimensionales (como el de la imagen de ejemplo anterior). Cuadro Gráfico, Plano interior del gráfico y Giro 3D modifican el aspecto de los gráficos tridimensionales disponibles:

Página 60 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Configuración del gráfico Para terminar de configurar el gráfico podemos ir a la pestaña Formato, donde encontraremos la sección Estilos de forma (que utilizaremos también más adelante para enriquecer la visualización de los objetos que insertemos).

Estas opciones nos permitirán aplicar diversos estilos sobre los gráficos. Para ello, simplemente seleccionaremos el área completa del gráfico o de uno de sus componentes (áreas, barras, leyenda...) y luego haremos clic en el estilo que más se ajuste a lo que buscamos. Si no queremos utilizar uno de los preestablecidos podemos utilizar las listas Relleno de forma, Contorno de forma y Efectos de forma para personalizar aún más el estilo del gráfico.

Modificar el tamaño de un gráfico También podemos seleccionar un elemento del gráfico para modificarlo. Cuando tenemos un elemento seleccionado aparecen diferentes tipos de controles que explicaremos a continuación: Los controles cuadrados establecen el ancho y largo del objeto, haz clic sobre ellos y arrástralos para modificar sus dimensiones. Haciendo clic y arrastrando los controles circulares podremos modificar su tamaño manteniendo el alto y ancho que hayamos establecido, de esta forma podremos escalar el objeto y hacerlo más grande o pequeño. Colocando el cursor sobre cualquier objeto seleccionado, cuando tome esta forma podremos hacer clic y arrastrarlo a la posición deseada.

Página 61 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Modificar la posición de un gráfico Excel te permite decidir la posición del gráfico en el documento. Además de poder establecer su tamaño y moverlo por la hoja de cálculo también podremos establecer su ubicación.

Para ello haz clic en el botón Mover gráfico que encontrarás en la pestaña Diseño

Se abrirá el siguiente cuadro de diálogo:

La primera opción Hoja nueva nos permite establecer el gráfico como una hoja nueva. Tendremos la ventaja de que no molestará en la hoja de cálculo, pero no podremos contrastar los datos numéricos si la mantenemos en una hoja aparte. Dependerá del formato que queramos utilizar. Escribir un nombre en la caja de texto y pulsa Aceptar. Utilizando la segunda opción, Objeto en, podremos mover el gráfico a una hoja ya existente. Si utilizamos este método, el gráfico quedará flotante en la hoja y podremos situarlo en la posición y con el tamaño que elijamos. Fuente: www.aulaclic.es

EJERCICIOS CON GRÁFICOS Vamos a realizar un gráfico sobre el porcentaje electoral, introduciendo las fórmulas correspondientes y aplicando el gráfico a dicho porcentaje. Esta hoja se llamará Elecciones. Para realizar este gráfico seleccionaremos las celdas de los partidos (A,B,C,D) y el porcentaje de cada partido e iremos a Insertar gráfico/Circular. Una vez tengamos el gráfico le haremos las modificaciones necesarias hasta dejarlo como en la imagen:

Página 62 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

AUTOSUMA ∑

Haremos el cálculo del porcentaje como se ve en la imagen y el total de votos a través de AutosumaΣ Σ. Las celdas de color azul son las que seleccionaremos para crear el gráfico. Si creamos el gráfico y los datos no se corresponden como deberían, haremos un clic derecho sobre el gráfico e iremos a Seleccionar datos. Ahora en otra hoja vamos a crear una comparación con los porcentajes electorales de las elecciones actuales y de las elecciones anteriores. El cálculo del porcentaje y el total de los votos lo haremos igual que en el ejercicio anterior. A esta hoja la llamaremos COMPARACIÓN. Para crear el gráfico cogeremos las celdas de la columna Partido y los Porcentajes tanto de las elecciones actuales como de las anteriores.

Página 63 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Ahora vamos a realizar otro grรกfico y a esta hoja la llamaremos Gasto familia. En este caso hemos usado el grรกfico columna apilada con efecto 3D.

Pรกgina 64 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Grรกfico de cotizaciones donde llamaremos a la hoja COTIZACIONES:

Para realizar este grรกfico seleccionaremos toda la tabla.

Pรกgina 65 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

En este último gráfico insertaremos las fórmulas necesarias para calcular la media de las temperaturas de lunes a domingo y necesitaremos seleccionar toda la tabla para crearlo. En este caso también utilizaremos un gráfico de cotización y llamaremos a la hoja TEMPERATURAS.

Página 66 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 GRÁFICOS DE IMAGEN Los gráficos de imagen utilizan pequeñas imágenes prediseñadas en los gráficos de barras, líneas y de sectores para representar los datos de un gráfico. Por ejemplo: una moneda para representar las ventas, una cesta de la compra para ver la evolución de los precios, un coche para representar el número de vehículos fabricados, etc. Represente los datos en un gráfico de columnas, barras o líneas. Hacer clic en el gráfico para seleccionarlo. Hacer clic en la serie donde deseemos que aparezca la imagen gráfica. Aparece el cuadro de diálogo Dar formato a serie de datos…Opciones de serie/ Relleno/ Relleno con imagen o textura/ Insertar desde Archivo o Imagen prediseñada/ Seleccionar/ Cerrar. En el gráfico aparece la imagen gráfica.

COMENTARIOS TABLA Realizaremos la siguiente tabla e insertaremos las fórmulas correspondientes y los comentarios que se muestran en la imagen. Calcularemos los totales Inicio/Modificar/Autosuma∑ ∑.

con

función

Autosuma

que

encuentra

Para insertar el comentario iremos a la pestaña Revisar/ Comentario/ Insertar comentario. Para poder visualizar los comentarios iremos a botón de Office/ Opciones de Excel/Avanzadas/Mostrar/ Comentarios e indicadores.

Página 67 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 GRÁFICO MIXTO Realizaremos el siguiente ejercicio aplicando la fórmula correspondiente. Realizar el gráfico de columnas, pero al Departamento C se le aplicará el gráfico de Líneas. Guardaremos el libro con el nombre de Beneficios. Para poder aplicarle el gráfico de líneas al departamento C seleccionaremos las tres columnas correspondientes a dicho departamento e iremos a Herramientas de Gráfico/ Diseño/Tipo/ Cambiar tipo de gráfico.

GRÁFICO CON FLECHAS INDICATIVAS

Página 68 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Realizar el siguiente ejercicio introduciendo la fórmula adecuada:

HIPERVÍNCULOS EN EXCEL 2007 Un hipervínculo es un vínculo de un documento que abre otra página u otro archivo cuando se hace clic en él. El destino es frecuentemente otra página web, pero también puede ser una imagen, o una dirección de correo electrónico o un programa. El hipervínculo mismo puede ser un texto o una imagen. Cuando el usuario de un sitio hace clic en el hipervínculo, el destino se muestra en un explorador web (explorador Web: software que interpreta archivos HTML, les da formato en páginas Web y los muestra. Un explorador Web, como Microsoft Internet Explorer, puede ir a hipervínculos, transferir archivos y reproducir archivos de sonido o vídeo que están incrustados en páginas Web.), ya sea abierto o ejecutado, según el tipo de destino. Por ejemplo, un hipervínculo a una página muestra la página en el explorador web y un hipervínculo a un archivo AVI (AVI: formato de archivo multimedia de Microsoft Windows para sonido y vídeos que utiliza la especificación Microsoft Resource Interchange File Format (RIFF)) abre el archivo en un reproductor multimedia. Para establecer un vínculo a una ubicación del libro actual o de otro libro, puede elegir entre definir un nombre (nombre: palabra o cadena de caracteres que representa una celda, rango de celdas, fórmula o valor constante. Utilice nombres fáciles de entender, como Productos, para referirse a rangos difíciles de entender, como Ventas!C20:C30) para las celdas de destino (destino: término general para designar el nombre del elemento al que se salta desde un hipervínculo) o utilizar una referencia de celda. Para usar un nombre, debe asignar un nombre a las celdas de destino del libro de destino.

•

Cómo asignar un nombre a una celda o rango de celdas

→ Seleccione la celda, el rango de celdas o selecciones no adyacentes (selección no adyacente: selección de dos o más celdas o rangos que no se tocan. Cuando trace selecciones no adyacentes en un gráfico, asegúrese de que las selecciones combinadas constituyen una forma rectangular.) a los que desee asignar un nombre.

Página 69 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 → Haga clic en el cuadro Nombre situado en el extremo izquierdo de la barra de fórmulas (barra de fórmulas: barra de la parte superior de la ventana de Excel que se utiliza para escribir o editar valores o fórmulas en celdas o gráficos. Muestra la fórmula o el valor constante almacenado en la celda activa.) .

Cuadro Nombre → En el cuadro Nombre, escriba el nombre de las celdas y a continuación, presione ENTRAR. Nota: Los nombres no pueden contener espacios y deben comenzar con una letra. En una hoja de cálculo del libro de origen (archivo de origen: el archivo que contiene información que se ha utilizado para crear un objeto vinculado o incrustado. Cuando se actualiza la información en el archivo de origen, también es posible actualizar el objeto vinculado en el archivo de destino.), haga clic en la celda en la que desea crear un hipervínculo. En el grupo Vínculos de la ficha Insertar, haga clic en Hipervínculo.

Sugerencia Asimismo, puede hacer clic con el botón secundario del mouse en la celda o el objeto y después elegir Hipervínculo en el menú contextual, o bien, presionar CTRL+ALT+K. En Vincular a, siga uno de los procedimientos siguientes: •

Para establecer un vínculo a una ubicación del libro actual, haga clic en Lugar de este documento.

•

Para establecer un vínculo a una ubicación de otro libro, haga clic en Archivo o página Web existente, busque y seleccione el libro con el que desea crear el vínculo y, a continuación, haga clic en Marcador.

Siga uno de los procedimientos siguientes: •

En el cuadro o seleccione un lugar de este documento, en Referencia de la celda, haga clic en la hoja de cálculo con la que desea establecer el vínculo y, a continuación, escriba la referencia de la celda en el cuadro Escriba la referencia de celda y haga clic en Aceptar.

•

En la lista que aparece bajo Nombres definidos, haga clic en el nombre que representa las celdas con las que desea establecer el vínculo y haga clic en Aceptar.

•

En el cuadro Texto, escriba el texto que desea utilizar para representar el hipervínculo.

•

Para mostrar información útil cuando se coloque el puntero sobre el hipervínculo, haga clic en Info. de pantalla y escriba el texto que desee en el cuadro Información en pantalla. Haga clic en Aceptar. Fuente: Microsoft

Página 70 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 EJERCICIO HIPERVÍNCULOS En este ejercicio vamos a crear tres tablas calculando por trimestres las compras que hemos realizado al 18%, 8% y 4% y una vez creadas mediante un hipervínculo podremos acceder desde una de las hojas a las demás. Para crear el hipervínculo iremos a Insertar/Hipervínculo/Lugar de este documento y seleccionaremos la hoja a la cual queremos acceder con el mismo.

EN ESTAS CELDAS SERÁ DONDE CREAREMOS LOS HIPERVÍNCULOS

Página 71 VICENTA MEGIA MOLERO

AUTOSUMA ∑

Microsoft Office Excel 2007

MACROS Cuando trabajamos con un libro personalizado, es decir, que nos 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 formatear todo el libro si disponemos de muchas hojas. Con las macros lo que se pretende es automatizar varias tareas y fusionarlas en una sola, añadiendo por ejemplo un botón en nuestro libro que al pulsar sobre él realice todas esas tareas.

Crear una macro automáticamente La forma más fácil e intuitiva de crear macros es crearlas mediante el grabador de macros del que dispone Excel. Este grabador de macros te permite grabar las acciones deseadas que posteriormente las traduce a instrucciones en VBA, las cuales podemos modificar posteriormente si tenemos conocimientos de programación. Para grabar una macro debemos acceder a la pestaña Vista y despliega el submenú Macros y dentro de este submenú seleccionar 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.

Al seleccionar la opción Grabar macro..., lo primero que vemos es el cuadro de diálogo Grabar macro donde podemos dar un nombre a la macro (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". El problema está en encontrar una combinación que no utilice ya Excel. En Guardar macro en: podemos seleccionar guardar 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.

Página 72 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Para comenzar la grabación de la macro pulsamos el botón Aceptar y a continuación, si nos fijamos en la barra de estado, encontraremos este botón en la barra de estado tenemos la opción de detener la grabación.

donde

A partir de entonces debemos realizar las acciones que queramos grabar, es conveniente no seleccionar ninguna celda a partir de la grabación, ya que si seleccionamos alguna celda posteriormente, cuando ejecutemos la macro, la selección nos puede ocasionar problemas de celdas fuera de rango. Una vez concluidas las acciones que queremos grabar, presionamos sobre el botón Detener barra de estado, o accediendo al menú de Macros y haciendo clic en

de la

Ejecutar una macro Una vez creada una macro, la podremos ejecutar las veces que queramos. Antes de dar la orden de ejecución de la macro, dependiendo del tipo de macro que sea, será necesario seleccionar o no las celdas que queramos 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 al menú Ver Macros..., que se encuentra en el menú Macros de la pestaña Vista, y nos aparece el cuadro de diálogo Macro como el que vemos en la imagen donde tenemos una lista con las macros creadas. Debemos seleccionar la macro deseada y pulsar sobre el botón Ejecutar. Se cerrará el cuadro y se ejecutará la macro. En cuanto al 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 sapan 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.

Página 73 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Crear una macro manualmente Para crear una macro de forma manual es necesario tener conocimientos de programación en general y de Visual Basic en particular, ya que es el lenguaje de programación en el que se basa el VBA de Excel. Primero debemos abrir el editor Visual Basic presionando la combinación de 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. Para insertar un módulo accedemos al menú Insertar → Módulo. A continuación debemos plantearnos si lo que vamos a crear es una función (en el caso que devuelva algún valor), o si por el contrario es un procedimiento (si no devuelve ningún valor). Una vez concretado que es lo que vamos a crear, accedemos al menú Insertar → Procedimiento... Nos aparece un cuadro de diálogo como vemos en la imagen donde le damos el Nombre: al procedimiento/función sin insertar espacios en su nombre. También escogemos de qué Tipo es, si es un Procedimiento, Función o es una Propiedad. Además podemos seleccionar el Ámbito de ejecución. Si lo ponemos como Público podremos utilizar el procedimiento/función desde cualquier otro módulo, pero si lo creamos como Privado solo podremos utilizarlo dentro de ese módulo. Una vez seleccionado el tipo de procedimiento y el ámbito presionamos sobre Aceptar y se abre el editor de Visual Basic donde escribimos las instrucciones necesarias para definir la macro.

Guardar archivos con Macros Cuando guardamos un archivo y queremos que las Macros que hemos creado se almacenen con el resto de las hojas de cálculo deberemos utilizar un tipo de archivo diferente. Para ello deberemos ir al Botón Office y seleccionar la opción Guardar como. Se abrirá el cuadro de diálogo Guardar como. En el desplegable Guardar como tipo seleccionar Libro de Excel habilitado para macros (*.xlsm).

Página 74 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

. Dale un nombre y el archivo se almacenará. Cuando abrimos un archivo que tiene Macros almacenadas se nos mostrará este anuncio bajo la banda de opciones:

Esto ocurre 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 has creado tú pulsa el botón Opciones para activarlas. Aparecerá este cuadro de diálogo:

Selecciona la opción Habilitar este contenido y pulsa Aceptar. Fuente: www.aulaclic.es

Página 75 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Ejercicios con macros Con la ficha Programador podremos acceder a funciones específicas, que encontraremos divididas en tres grupos principales: Código, Controles y XML. Grupo: Código Dentro de este grupo encontraremos los comandos que accionan herramientas relacionadas con el código VBA de las macros. Grupo Código: Visual Basic El primer icono, denominado Visual Basic, iniciará el editor de código VBA. Este editor es la herramienta principal para la elaboración de macros complejas, dado que nos permite realizarle modificaciones al código de las macros en una ventana especial con un formato similar a los lenguajes de programación. Además de abrirlo desde este icono, también podemos iniciar el editor si presionamos la combinación de teclas ALT+F11. Grupo Código: Grabar macros Desde este comando lanzaremos la grabadora de macros. Esta herramienta guarda todas las acciones que realizamos y las transforma en código VBA. LA SEGURIDAD: CENTRO DE CONFIANZA Si presionamos el botón denominado Seguridad de macros, que se incluye dentro del grupo Código en la ficha Programador, accederemos a la configuración del Centro de confianza de Excel 2007. En él encontraremos la configuración de seguridad. Macros con controles de formulario Las macros son utilidades que facilitan la realización de algunas tareas repetitivas, como pasar de una hoja a otra, saltar a una celda concreta, abrir una aplicación, etc. Realizaremos la siguiente actividad: Crear dos botones, en la hoja 1, que nos permitan ir a la hoja 2, celdas B3 y B6. Para ello iremos a la pestaña Programador/Insertar controles/Botón (control de formulario).

Le daremos al botón toda la anchura de toda la columna D y el ancho normal de la fila. Al terminar de dibujar el botón, aparecerá una ventana para asignar macro. Dejamos los datos que tiene por defecto y haremos clic en el botón Grabar.

Página 76 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Aceptamos y grabamos nuestra macro: Ir a la hoja 2 celda B3 y una vez realizados los pasos, haremos clic en Detener grabación.

Con el botón derecho del ratón sobre el botón podremos modificar el texto.

Realizamos el mismo procedimiento para crear el otro botón del comando. Ahora vamos a crear en la hoja 2 un botón, similar a los anteriores, pero que nos devuelva a la hoja 1, lo llamaremos Volver al índice, y su función será llevarnos a la celda D1 de la hoja 1.

Página 77 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Ahora vamos a realizar otro ejercicio donde realizaremos macros con tres botones: Azul, Verde y Rojo. Al pulsar el rojo, el rango F4:H13, se volverá de color rojo. Igualmente si pulsamos verde, se volverá dicho rango en verde y en azul, respectivamente.

Ejercicio 1. Uso de las referencias relativas Crear una macro con el nombre de Texto. Utilizar fuente, tamaño fuente y color de relleno. Seleccionar Referencias relativas para que nos permita ejecutar la macro en cualquiera de las celdas de la hoja. Si no utilizamos esta opción, si la macro ha sido grabada en una determinada celda, se ejecutará siempre en esa celda, como referencia absoluta.

Pulsaremos en Grabar macro y escribiremos el nombre de la misma, en este caso Texto. Pulsamos Aceptar.

Página 78 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Una vez hayamos creado la macro pulsaremos en Detener grabación e iremos a Macros/Ver macros.

Ahora crearemos un botón para que nos ejecute la macro. Para ello una vez creemos el botón haremos clic derecho sobre el mismo y seleccionaremos Asignar macro…

Ejercicio 2. Macro con método abreviado Ahora vamos a crear una macro con método abreviado donde insertaremos un hipervínculo a una página web que en este caso será la dirección de mi blog. Para ello pulsaremos en Grabar macro y rellenaremos las casillas que necesitemos y pulsaremos en Aceptar.

Ahora nos situaremos en una de las celdas, haremos clic derecho y pulsaremos en hipervínculo para insertar la dirección del blog y pulsaremos en Aceptar y Detener grabación y ya habremos creado la macro a nuestra dirección de correo. Para ejecutarla CTRL+W.

Página 79 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Ejercicio 3. Macro con tabla. Realizar una macro suponiendo que todos los días tenemos que generar una lista con el movimiento de una tienda de venta de ordenadores, para organizar los datos que iremos anotando, es conveniente escribir los encabezados de cada columna, supongamos que estos encabezados son: MARCA, PRODUCTO, CANTIDAD, PRECIO, VENDEDOR. Guardar la macro con el nombre de Encabezados. Crear el botón Encabezados a la macro Encabezados.

Primero crearemos la macro y después crearemos el botón donde haciendo un clic derecho seleccionaremos Asignar macro…

Página 80 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Ejercicio 4. Macros con Visual Basic Para crear una macro en Visual Basic iremos a la pestaña Programador/Visual Basic.

Se nos abrirá una nueva ventana, como si fuera una hoja y veremos una gran zona gris. A la izquierda encontraremos dos ventanas: Proyecto y Propiedades. En el menú Ver se activa la ventana Propiedades si no se encuentra activa:

Página 81 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Ahora pulsaremos en la pestaña Insertar/Módulo:

Nos aparecerá una pantalla de edición para escribir nuestra macro. Códigos en inglés. Toda macro empieza por Sub, (podría significar Subproyecto, subtareas, macros pequeñas que al final se unen) espacio, nombre de la macro, que no lleva espacios y tiene que empezar por una letra. La macro la llamaremos Frasededespedida.

Pulsaremos Intro. El editor nos ayuda, y como nos ayuda nos pone paréntesis paréntesis (), que siempre hay que ponerlos y End Sub, dejando líneas de programa.

En las líneas de programa ponemos el rango, la celda donde aparecerá la frase de despedida. Range(“B10”)=”Se despide atentamente,”. El texto irá entre comillas.

Página 82 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Cuando pulsamos o bajamos líneas lo que hace el editor es poner los espacios necesarios y si hubiera algún error de sintaxis, nos avisa y los marca para su corrección.

Ahora ejecutaremos la macro, y se puede hacer desde el editor o desde la hoja. Desde el editor, podemos ver las dos ventanas para comprobar. Si estamos dentro de la macro lo haremos pulsando el botón Ejecutar Sub, sabe que macro es y no tiene que preguntar qué macro queremos ejecutar. Con F5, obtendremos el mismo resultado.

Como podemos ver en la imagen se ejecuta la frase de despedida que indicamos en la macro y podremos salir del editor sin ningún problema ya que la macro no se pierde al estar asociada a la hoja de cálculo. Si ahora grabamos, lo tenemos que guardar, en Guardar como Frasededespedida, en libro de macros, con la extensión .XLSM. El icono del libro se diferencia de un libro de Excel. Podremos abrir un libro nuevo y ejecutar la macro creada con el editor.

Página 83 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Ejercicio 5. Asignar icono a una macro En Excel existen varios métodos para activar una macro. En Excel 2007 podemos, como en las versiones anteriores, ligar la macro a objetos o asignarle una combinación de teclas pero no tenemos barras de herramientas donde poner el icono o menús donde incluir la macro. Tenemos la alternativa de poner un icono ligado a la macro en la barra de acceso rápido. Poner un icono ligado a una macro que ya hayamos realizado, para ello: 1. Abrir el menú Opciones de Excel/Personalizar (o clic en la flecha en el extremo derecho de la barra de acceso rápido). En la ventanilla Comandos disponibles en elegimos Macros. 2. Aparece la lista de macros disponibles, ordenadas alfabéticamente.

Si no viéramos el nombre completo, podemos apuntar con el ratón y después de unos instantes veremos el nombre completo de la macro. Seleccionamos la macro y apretamos el botón Agregar.

3. Para cambiar el icono que Excel pone por defecto seleccionamos la macro que acabamos de agregar y hacemos clic en Modificar. 4. Elegimos un icono adecuado y en la ventanilla Nombre para mostrar cambiamos el texto que Excel asigna por defecto (el nombre de la macro) por un texto descriptivo. Finalmente clic en Aceptar.

Página 84 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Excel agrega el icono a la barra de acceso rรกpido y al apuntar con el ratรณn podemos ver la descripciรณn de la acciรณn que ejecuta la macro.

Pรกgina 85 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

FORMULARIOS CON CONCEPTOS BÁSICOS DE LAS MACROS Tenemos la siguiente tabla en una hoja de Excel:

Vamos a crear un formulario con macros que nos permita introducir datos en la tabla diseñada en la hoja de cálculo. Pestaña Programador / Visual Basic

Entramos al editor de Visual Basic. Si no están activadas, activar las siguientes opciones: o o

Menú Ver / Explorador de Proyectos. Menú ver / Ventana Propiedades.

Menú Insertar / UserForm. Inserta el Formulario que programaremos con controles.

En el Explorador de Proyecto se observará que se insertó el UserForm.

Página 86 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 También se debe de activar el Cuadro de Herramientas, si no se activa, clic en el Menú Ver / Cuadro

de Herramientas, o en la barra de herramientas

Ponemos un color a nuestro formulario. Seleccionado UserForm1

Página 87 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Clic en formulario (aparece el Cuadro de herramientas), elegimos Botรณn Etiqueta para seleccionar el nombre de la etiqueta. Serรกn los nombres que tenemos en nuestra lista en la hoja de excel: NOMBRE, APELLIDOS, EDAD. Clic y arrastrar dibujando en el Formulario la etiqueta. Podemos escribir en la etiqueta o en Caption

Copiamos y pegamos la etiqueta; cambiamos en Caption el nombre de la etiqueta copiada. Escribimos APELLIDOS y en otra etiqueta que copiamos nuevamente, EDAD.

Pรกgina 88 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Ahora realizamos los campos en blanco con Cuadro de texto. Realizaremos uno para cada etiqueta creada.

Copiamos y pegamos para que tengan el mismo tamaño.

Podemos aplicar colores de forma individual a cada una de las partes del formulario con seleccionarlo. Para el campo ESTUDIANTE tenemos dos posibilidades: “Sí es estudiante” “No es estudiante” Añadimos Botón de opción a nuestro formulario.

Página 89 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

En Caption, pongo SI. Copio el bot贸n y en Caption cambio NO.

Seleccionamos la herramienta Marco, le cambiamos el nombre, ESTUDIANTE, e introducimos los botones de opci贸n.

P谩gina 90 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Vamos a crear dos botones de Comando para Limpiar y Enviar a la lista que hemos diseñado en la hoja de cálculo.

Para pasar los datos de nuestro formulario a la lista que tenemos debemos programar nuestros botones. Observamos los espacios en blanco que tienen nombre, Cuadro de texto, los botones igual, en pestaña Apariencia:

Página 91 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Con los nombres de cada control tenemos que trabajar para programar nuestro formulario. Programamos el Bot贸n LIMPIAR. Doble Clic en 茅l. Accedemos a Visual Basic.

Correspondientes a los cuadros de texto NOMBRE, APELLIDOS, EDAD

Opciones Campo ESTUDIANTE

Empty es empleado para vaciar los Textbox . Vamos a comprobar si funciona. Clic en Ejecutar o F5.

Aparece el formulario en la hoja de Excel. Introducimos datos, clic bot贸n LIMPIAR.

P谩gina 92 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Cerramos la ventana del formulario. Accedemos al formulario en VB. Vamos a programar el botón ENVIAR. Doble clic en él. Programamos el botón de comando.

Indicar en la hoja que estamos, y el rango en que vamos a iniciar Mientras no esté vacio, empieza en la siguiente celda, va a ir a la siguiente celda activa, va a la siguiente fila (1, 0). Siguiente columna (0,1) Con los botones Si es verdad entonces que haga…activa ”Si es estudiante” sino Activa “No es estudiante” Cerrar los dos If OptionButton Clic en Ejecutar o F5

Nota.-Lo que está en azul lo genera Excel automáticamente.

Página 93 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Aparece nuestro formulario, introducimos datos, clic ENVIAR.

HERRAMIENTA SOLVER (Buscar objetivo) El complemento Solver es un programa de complemento (complemento: programa suplementario que agrega funciones o comandos personalizados a Microsoft Office.) de Microsoft Office Excel que está disponible cuando instalas Microsoft Office o Excel. Sin embargo, para utilizarlo en Excel primero deberemos cargarlo. Haz clic en el Botón de Microsoft Office y, a continuación, haz clic en Opciones de Excel. Haz clic en Complementos y, en el cuadro Administrar, selecciona Complementos de Excel. Haz clic en Ir.

En el cuadro Complementos disponibles, activa la casilla de verificación Complemento Solver y, a continuación, haz clic en Aceptar.

Página 94 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Si complemento Solver no aparece en la lista del cuadro Complementos disponibles, haz clic en Examinar para buscar el complemento. Si te indica que el complemento Solver no está instalado actualmente en el equipo, haz clic en Sí para instalarlo. Una vez cargado el complemento Solver, el comando Solver estará disponible en la pestaña Datos/Análisis. En ocasiones se requerirá del Cd de instalación de Office.

Ejemplo del uso de la herramienta análisis SOLVER Solicitud de pedido Carnicería PerniLight Crearemos la siguiente tabla con los datos correspondientes:

Página 95 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Como podemos ver tenemos ciertas condiciones (restricciones en Solver) a la hora de realizar el pedido donde sólo podremos gastar 3.000 €, las piezas deberemos comprarlas completas y tenemos que comprar un mínimo de unidades de cada uno de los productos. Ahora deberemos prepararle el camino a Solver, el cual necesita que tengamos las fórmulas hechas. En unidades escribiremos unas unidades al azar y en total escribiremos la fórmula necesaria que será el precio de la pieza por el número de unidades. Para el total presupuesto utilizaremos la función =SUMA(D4:D6).

Ahora deberemos borrar las unidades pero dejando las fórmulas.

Página 96 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Una vez hecho esto iremos a Datos/Análisis/Solver:

Lo primero que nos pide Solver es la Celda objetivo, la cual siempre deberá tener alguna fórmula. En nuestro caso la celda objetivo es D8(Total presupuesto) en el Valor de… la celda objetivo insertaremos 3.000 que es el dinero que queremos gastar en el pedido. En Cambiando las celdas(donde queremos que nos devuelva los valores) seleccionaremos el rango C4:C6 (UNIDADES). Ahora insertaremos las restricciones que hemos marcado. Para decirle a Solver que el mayorista sólo nos vende las piezas enteras pulsaremos en Agregar y le diremos:

Haremos lo mismo con las celdas C5 y C6 que corresponden a las unidades del chorizo y de los quesos. Para decirle el mínimo de unidades que queremos comprar agregaremos la siguiente restricción para los jamones:

Página 97 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Agregaremos de igual manera las restricciones sobre el pedido mínimo de chorizos y de quesos. Una vez hayamos agregado todas las restricciones pulsaremos en Aceptar y ya tendríamos insertadas las condiciones que queremos para el pedido. Pulsaremos en Resolver y nos aparecerá un cuadro de diálogo como el siguiente:

Aquí nos dice que Solver ha encontrado una solución. Podremos ver informes aunque generalmente dicen poco. También podríamos Guardar este escenario el cual sería toda la consulta que hemos hecho. En nuestro caso pulsaremos en Aceptar. Y como podemos ver se cumplen las condiciones que necesitamos en el pedido:

Fuente: www.jaumenet.com

Página 98 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

RESUMEN AVANZADO CON DATOS DE DIFERENTES HOJAS EXCEL Tenemos las compras de cada una de las sucursales de nuestra empresa. En cada una de las hojas tenemos el resumen de las compras realizadas: SUCURSAL A:

SUCURSAL B:

SUCURSAL C:

Pรกgina 99 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Poner un color a cada etiqueta:

Vamos a crear un resumen en el cual obtengamos la suma total de cada una de las sucursales de forma automática: 1. En cada una de las hojas creamos un resumen en la primera fila, es importante que en todas las hojas de las sucursales estén los datos en las mismas celdas. El total del importe estará en la celda F1.

2. Realizar hoja Resumen, donde recogeremos los datos totales de cada una de las sucursales. Vamos a indicar los totales del importe y la cantidad de cada una de ellas. Primero creamos una tabla donde en la primera columna aparecerán los nombres de las diferentes hojas de Excel, escritos exactamente igual, y en la primera fila pondremos el nombre de la celda donde están los datos que deseamos recoger de todas las hojas de las Sucursales. Con la fórmula =INDIRECTO(“”&$B3&”’!”&C$2) ya tenemos un primer resumen con los datos totales.

3. Ahora vamos a sacar un resumen más avanzado utilizando la función SUMAPRODUCTO e INDIRECTO. Una vez que conocemos los datos totales para cada sucursal, queremos conocer las compras realizadas a cada uno de los proveedores o incluso que productos se han comprado.

Página 100 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Con una tabla dinámica podríamos conseguir estos datos fácilmente para una hoja Excel, pero cuando se combinan varias hojas Excel debemos utilizar un método más minucioso. Con la fórmula =INDIRECTO("'"&C$10&"'!"&"B3:B15") obtenemos el rango de celdas entre B3 y B15 (columna donde están los nombres de los proveedores) de la hoja de Excel con el nombre que aparece en la celda C$10 (es decir, “Sucursal A”). Ahora comparamos la matriz de datos obtenida con $B11 (el nombre del proveedor, en este caso “Proveedor 1”) =INDIRECTO("'"&C$10&"'!"&"B3:B15")=$B11, esta fórmula nos devuelve una matriz de 0s y 1s que indican si la fila es o no del proveedor (coincide con el nombre del proveedor). Por último, sólo es necesario multiplicar la matriz por los valores de las compras realizadas que las obtenemos con la siguiente fórmula INDIRECTO("'"&C$10&"'!"&"F3:F15". El resultado de la fórmula final es: =SUMAPRODUCTO(1*(INDIRECTO("'"&C$10&"'!"&"B3:B15")=$B11)*INDIRECTO("'" &C$10&"'!"&"F3:F15")) Ya tenemos un resumen que permite obtener el detalle de cada proveedor en cada una de las sucursales.

LA FUNCIÓN BUSCAR V (columnas) La función BUSCAR V la encontraremos FUNCIONES/BÚSQUEDA Y REFERENCIAS.

Página 101 VICENTA MEGIA MOLERO

pestaña

FÓRMULAS/BIBLIOTECA

Microsoft Office Excel 2007 Para ver el funcionamiento de esta función realizaremos una tabla cuyo nombre de la hoja será ARTÍCULOS y el rango de la misma será PIEZAS.

En la hoja del mismo libro que llamaremos CONSULTAS realizaremos otra tabla. La información de cada artículo la presentaremos en cada fila. Lo que queremos es automatizar la búsqueda en la tabla. Introduciendo la referencia solicitada nos dará la información correspondiente. Colocaremos el cursor en la celda correspondiente a Descripción y pulsaremos el botón

Una vez dentro del asistente, elegiremos la categoría de función BÚSQUEDA Y REFERENCIA y el nombre de la función BUSCARV. El argumento será: Para proporcionar el Precio unitario y las Existencias, copiar la función que ya se ha creado, pero hay que modificar los argumentos de función de Indicador columnas y poner 3 y 4.

Página 102 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Realizaremos la función lógica SI para conocer el precio a fijar a un determinado cliente, por ejemplo para clientes privilegiados se aplicará el precio igual al 90% del que figura en la tabla. Si no es privilegiado, se aplicará el precio de la tabla. Para esta operación nos situaremos en la celda Precio e insertaremos la función SI.

Página 103 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

BÚSQUEDA DE OBJETIVOS Esta sencilla opción se utiliza para buscar un valor específico como resultado de una fórmula, modificando el contenido de una celda. Excel buscará que valor debería tomar esa celda para conseguir el resultado esperado. A esa celda se la denomina Valor independiente y a la celda que contiene la fórmula se la denomina Dependiente. Vamos a ver un ejemplo. Imaginemos una sencilla hoja de cálculo que nos servirá para averiguar el precio de un producto sin el IVA (Impuesto aplicado en España).

B3 contiene un valor constante introducido. La fórmula de B5 será: =B3*B4 La fórmula B7 será: =B3+B5 Imaginemos que se nos ha dado un precio de 300,51 € y nos gustaría saber el PRECIO DEL ARTÍCULO. -

Seleccionar la celda B7 que es la que contiene la fórmula que deseamos que valga un determinado valor, que en este caso es de 300,51 €. En el grupo Herramientas de datos de la ficha Datos, hacer clic en Análisis Y si y, a continuación, en Buscar objetivo.

Nos aparecerá un pequeño cuadro de diálogo. Definir la celda: indica la celda que contiene la fórmula. Al haber situado primero el cursor en ella, aparece por defecto. Con el valor: es el valor que tomará la celda anterior, o sea, el valor que queremos obtener. Escribiremos 300,51 como valor a obtener. Para cambiar la celda: celda que se utiliza en la fórmula. Indicaremos la celda B3 como celda que nos interesa conocer.

Página 104 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Acepta el cuadro y fíjate como Excel nos muestra un mensaje con una solución encontrada. Podemos aceptar o cancelar esa posibilidad.

De esta forma observamos que hemos conseguido el precio que queríamos, por lo que ya sabemos a qué precio estará el artículo: 254,67 €

FUNCIONES MATEMÁTICAS

Página 105 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

FUNCIONES ESTADร STICAS

Pรกgina 106 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

FUNCIONES Lร GICAS

Pรกgina 107 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

FUNCIONES FECHA Y HORA

Pรกgina 108 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

EJERCICIO REPASO DE FUNCIONES: CONTARA, CONTAR, CONTAR SI, REDONDEAR Tenemos un listado de alumnos, con notas en letra (Apto y No Apto) y en nº, con decimales. El programa que utilizamos, nos obliga a redondear la nota. Damos 1.000 puntos a los APTOS y 500 puntos a los NO APTOS, con intención de que vayan sumando puntos a lo largo del curso y llevarlos de excursión si, al final del curso, tienen un mínimo de 5.000 puntos. Nuestra tarea consiste en insertar las funciones necesarias para realizar el trabajo con seguridad y rapidez. El trabajo que tenemos que hacer es:

Contar las celdas donde hay texto (Aptos y No aptos) Contar las celdas donde hay números. Redondear los decimales Contar las celdas vacías Dar formato condicional a la segunda columna y a la última. (Donde aparezca Apto y la cantidad 1.000, el color de la fuente en azul. Donde aparezca No apto y 500, el color de la fuente en rojo)

Laura Gómez José Gutiérrez Rodrigo Sánchez Aníbal Torreverde Eduardo Pérez Mario de la Vega Natalia Hidalgo Víctor Rizaldos Carlos Pérez Vanesa Cifuentes Demetrio Aguirre Marcos Polo Adriana Suárez Jorge Abad

Apto No apto Apto Apto Apto No apto No apto

8,33 3,45 6,51 5,82 9,83 4,00 2,00

1000 500 1000 1000 1000 500 500

No apto Apto

3,42 7,86

500 1000

No apto Apto

4,21 6,67

500 1000

Quedará de la siguiente manera:

Página 109 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Pasos a seguir: 4. Primero en la columna de Función CONTARA nos situaremos en la celda B2 e insertaremos la función SI para que si la nota es mayor de 4,9 nos aparezca Apto y si fuera menor nos apareciera No apto. La función sería =SI(C2>4,9;"Apto";"No apto").

Para aplicarle el formato condicional del color según sea Apto o No apto iremos a la pestaña Inicio/Estilos/Formato Condicional/Resaltar reglas de celdas/Texto que contiene donde le diremos que al texto que contenga Apto aparezca en color azul y No apto en rojo. Tendremos que seguir los pasos para cada uno de los colores. 5. Después iremos a la columna Función CONTAR.SI e insertaremos la función SI quedando de la siguiente manera: =SI(C2>4,9;1000;500).

Para que nos aparezca 1000 en color azul y 500 en color rojo iremos a Inicio/Estilos/Formato condicional/Resaltar reglas de celdas/Es igual a. 6. En la columna Función CONTAR escribiremos los datos manualmente y en la columna Función CONTAR.BLANCO insertaremos la función REDONDEAR a cero decimales y arrastraremos hacia abajo para que nos redondee todos los datos. La función queda: =REDONDEAR(C2;0)

Página 110 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

7. En la fila 16 será donde aplicaremos las funciones CONTARA, CONTAR, CONTAR BLANCO y CONTAR.SI FUNCION CONTARA: =CONTARA(B2:B15) FUNCION CONTAR: =CONTAR(C2:C15) FUNCIÓN CONTAR BLANCO: =CONTAR.BLANCO(D2:D15) FUNCION CONTAR SI: =CONTAR.SI(E2:E15;"=1000")

FUNCIÓN SUMAPRODUCTO La función SUMAPRODUCTO se encuentra entre las funciones Matemáticas y trigonométricas.

Para ver cómo funciona haremos el siguiente ejercicio: Recibimos un memorando que nos solicita lo siguiente:

MATRIZ 1

Página 111 VICENTA MEGIA MOLERO

MATRIZ 2

Microsoft Office Excel 2007 Para calcular la SUMA TOTAL nos hemos situado en la celda inferior y hemos insertado la función SUMAPRODUCTO de la siguiente manera:

FUNCION SUMAR.SI, SUMAR.SI.CONJUNTO, CONTAR.SI Y CONTAR.SI.CONJUNTO Función SUMAR.SI La función SUMAR.SI podemos encontrarla también entre las funciones Matemáticas y trigonométricas.

Para ver cómo funciona haremos el siguiente ejercicio: Realizaremos la siguiente tabla en Excel, donde haremos los siguientes cálculos: 1. Calcular la comisión al 7% Para ello insertaremos en una celda el 7% y ésta la utilizaremos para el cálculo dejándola como una celda absoluta y arrastraremos hacia abajo.

CÁLCULO DE LA COMISIÓN

Página 112 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 2. En la casilla B9, tiene que aparecer una cantidad de acuerdo a las siguientes condiciones: • Si el valor de la propiedad es superior o igual a 160.000, entonces se sumarán las condiciones correspondientes a las citadas cantidades.

Para el siguiente ejercicio introduciremos los siguientes datos en la hoja 1 del libro de cálculo:

Ahora calcularemos el montante de las propiedades de más de 160.000 € y las comisiones que se cobran al comprador por propiedades que superan 160.000 €. Resultado:

Página 113 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Función SUMAR.SI.CONJUNTO Para ver cómo utilizar esta fórmula introduciremos los siguientes datos en la hoja 2 del libro:

Lo que queremos saber es cuál fue la suma de las ventas de Juan López, en el sector Electrodomésticos que superaron los 200 €; cómo podemos observar los criterios son los siguientes: 5. “Juan López” 6. “Electrodomésticos” 7. “>200”

Página 114 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Función CONTAR.SI En la hoja 3 introduciremos los siguientes datos:

Ahora calcularemos las personas que pesan más de 70 kg y el número de hombres de la lista. RESULTADO:

Función CONTAR.SI.CONJUNTO Ahora copiaremos la lista de la hoja 2 en la hoja 4 y calcularemos el número de veces que aparece Juan López en la lista cumpliendo las mismas condiciones:

Página 115 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

FUNCIÓN O, FUNCIÓN Y Estas funciones podemos encontrarlas dentro de las funciones lógicas.

Para ver cómo funcionan vamos a realizar el siguiente ejercicio: En un concurso de animales se van a dar 2 sistemas de selección para el orden de salida a escena de sus dueños: 1. Que los dueños de los animales tengan o un perro o un periquito. FUNCION O 2. Que los dueños de los animales tengan un perro y un loro. FUNCION Y Considerando que van a salir a escena por orden alfabético, haremos un listado de los dueños que van a salir en cada una de las selecciones. NOTA: Para ordenar podemos ir a Inicio/Modificar/Ordenar y filtrar o en Datos/Ordenar y filtrar/Ordenar. Si pulsamos en filtrar nos permitirá restringir la selección según necesitemos en ese momento, por ejemplo que sólo nos aparezcan los dueños que tengan perro:

1. Que los dueños de los animales tengan o un perro o un periquito. FUNCION O

Página 116 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

2. Que los dueños de los animales tengan un perro y un loro. FUNCION Y

Ahora ordenaremos las tablas por orden alfabético y les insertaremos un filtro por si queremos hacer una selección concreta.

Página 117 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

FUNCIÓN O:

FUNCIÓN Y:

FUNCIÓN ELEGIR Esta función la podemos encontrar en las de Búsqueda y Referencia:

Para ver cómo funciona vamos a crear una tabla con distintos valores y a través de la función Elegir le diremos que nos busque la posición 3 la cual corresponderá al índice.

Página 118 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Para conseguir este resultado en el cuadro Argumentos de función escribiremos lo siguiente:

FUNCIÓN POSICIÓN Esta función también podemos encontrarlas en las de Búsqueda y Referencia:

Para ver el funcionamiento escribiremos en cualquier celda el número 200 y escribiremos en que fila y en que columna se encuentra situado dicho número. Podremos elegir entre términos absolutos o términos relativos. Primero veremos cómo se hace dentro de una misma hoja y después en una hoja diferente.

Página 119 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

INCRUSTAR OBJETOS EN EXCEL Hacer: Incrustar un documento de Word existente en una hoja de cรกlculo de forma que se muestre como un icono. Realizar modificaciones en el documento dentro de Excel, cerrarlo y volverlo a abrir, comprobando que los cambios permanecen en la copia incrustada pero no en el original.

Pรกgina 120 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Solución: seleccionamos una celda de la hoja de cálculo y ejecutamos la opción de menú Insertar/Texto/Objeto.

Nos situamos en la pestaña Crear de un archivo y apretamos el botón Examinar para seleccionar el documento de Word que incrustaremos. Marcamos la casilla Mostrar como icono y salimos con el botón Aceptar.

Abrimos el documento incrustado haciendo doble clic sobre su icono, y realizamos algún cambio en su contenido. Una vez efectuados los cambios deseados cerramos el documento para volver a ver la hoja de Excel. Si abrimos de nuevo el documento Word podremos observar que los cambios se mantienen aún cuando no hayamos guardado el archivo en ningún momento. Esto es así porque en realidad el documento de texto no se guardará hasta que hagamos lo propio con la hoja de cálculo. Es entonces cuando decidiremos si conservar o no las modificaciones realizadas en el documento Word. Nos aseguramos que se guardan los cambios en el documento incrustado guardando la hoja de cálculo. Para comprobar si el documento original permanece inalterado lo abrimos desde Microsoft Word y veremos que así es, ya que presenta el aspecto inicial.

Página 121 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

FUNCION BIN.A.DEC Esta función nos convierte un número Binario a Decimal y se encuentra dentro de las funciones de INGENIERIA. Recuerda que los números binarios se representan solamente utilizando como base dos, están compuestos sólo con los dígitos 0 y 1. En esta función el número a convertir en decimal no puede tener más de 10 dígitos. Estructura: BIN.A.DEC (Número). En Número pondremos el valor en base 10 que deseemos convertir en Decimal. Puedes introducir un número, o una celda en la que se encuentre un valor en formato decimal. Ejemplo: En la celda A1 escribe el valor 10010. En la celda A2 escribe la función =BIN.A.DEC(A1). Esta función nos devolverá el número 18. Error: esta celda nos devolverá #¡NUM! Si el valor que introducimos dentro de la función no es un valor binario.

Argumentos de función:

Página 122 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

FUNCION BIN.A.HEX Esta función nos convierte un número Binario a Hexadecimal y se encuentra entre las funciones de INGENIERIA. Recuerda que los números binarios se representan solamente utilizando como base dos, están compuestos sólo con los dígitos 0 y 1. En esta función el número a convertir en hexadecimal no puede tener más de 10 dígitos. El sistema de numeración Hexadecimal se basa en 16 dígitos. El sistema Decimal que utilizamos normalmente sólo consta de 10 dígitos con lo que tenemos que utilizar letras de la A a la F para así conseguir poder trabajar con 16 dígitos.. Estructura: BIN.A.HEX(Número; Caracteres) En Número pondremos el valor en base 10 que deseemos convertir en Hexadecimal. Puedes introducir un número, o una celda en la que se encuentre un valor en formato decimal. En Caracteres deberás introducir el número de caracteres que quieras que se muestre. Excel introducirá tantos ceros a la izquierda como sea necesario para llegar al número de caracteres que deseas ver. Ejemplo: en la celda A1 escribe el valor 11111011. En la celda A2 escribe la función =BIN.A.HEX(A1;5). Esta función como resultado nos devolverá el valor 000FB. Error: esta celda nos devolverá #¡NUM! Si el valor que introducimos es negativo o no es un valor binario.

Argumentos de función:

FUNCION BIN.A.OCT Esta función nos convierte un número Binario a Octal y se encuentra entre las funciones de INGENIERIA.

Página 123 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Recuerda que los números Binarios se representan solamente utilizando como base dos, están compuestos sólo con los dígitos 0 y 1. En esta función el número a convertir a octal no puede tener más de 10 dígitos. El sistema de numeración Octal se basa en 8 dígitos. Estructura: BIN.A.OCT(Número;Caracteres) En Número pondremos el valor en base 10 que deseemos convertir en Octal. Puedes introducir un número, o una celda en la que se encuentre un valor en formato decimal. En caracteres deberás introducir el número de caracteres que quieras que se muestre. Excel introducirá tantos ceros a la izquierda como sea necesario como para llegar al número de caracteres que deseas ver. Ejemplo: En la celda A1 escribe el valor 1001. En la celda A2 escribe la función =BIN.A.OCT(A1;5). Esta función como resultado nos devolverá el valor 00011. Error: esta celda nos devolverá #¡NUM! Si el valor que introducimos es negativo o no es un valor binario.

Argumentos de función:

FUNCION DEC.A.BIN Esta función nos convierte un número Decimal a Binario y se encuentra dentro de las funciones de INGENIERIA. Recuerda que los números Binarios se representan solamente utilizando como base dos, están compuestos sólo con los dígitos 0 y 1. En esta función el número a convertir a Binario no puede tener más de 10 dígitos. Se tiene que destacar que Excel no convertirá números más grandes del 511 que en binario es el 1111111111. En caso de ser valores negativos no convertirá números más pequeños del 512. Si ponemos números que no cumplan estas condiciones nos aparecerá el mensaje: #¡NUM!

Página 124 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Estructura: DEC.A.BIN(Número;Caracteres) en el argumento Número pondremos el valor entero y decimal que deseamos convertir. Puedes introducir un número o una celda en la que se encuentre el valor en decimal a convertir. En Caracteres deberás introducir el número de caracteres que quieras que se muestre. Excel introducirá tantos ceros a la izquierda como sea necesario para llegar al número de caracteres que deseas ver. Ejemplo: en la celda A1 escribe el valor 20. En la celda A2 escribe la función =DEC.A.BIN(A1;7). Esta función nos devolverá como resultado el número 0010100. Observa que a la izquierda aparecen dos ceros para llegar a los caracteres que le hemos indicado deseamos obtener en el resultado. Si esta misma función la escribiéramos asi: =DEC.A.BIN(A1) obtendríamos como resultado: 10100 Error: esta celda nos devolverá #¡NUM! Si el valor que introducimos dentro de la función no es un valor decimal.

Argumentos de función:

FUNCION DIAS.LAB Se encuentra dentro de las funciones de FECHA Y HORA. Realiza la diferencia de días que hay entre la fecha inicial y la fecha final contabilizando solamente los días laborales, no tiene en cuenta los sábados y domingos. Si deseamos que la función tampoco tenga en cuenta otros festivos podremos incorporar un rango de fechas en el argumento Festivos. Estructura: DIAS.LAB(Fecha inicial;Fecha final;Festivos) Festivos será el rango de fechas que queremos considerar como festivos y no queremos que el ordenador contabilice como días laborales. Ejemplo: en este ejemplo utilizaremos dos celdas en las que pondremos, en una la fecha inicial y en la otra la fecha final para calcular el número de días laborales que hay entre las dos. En la celda A1 escribiremos “01/01/2010” y en la A2 “12/01/2010”. En la celda B2 introduciremos la siguiente

Página 125 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 función: =DIAS.LAB(A1;A2), la cual nos devolverá un 8. Si consultamos un calendario podemos ver que el día 2,4,9 y 10 caen en fin de semana con lo que la fórmula no los contabiliza. Si quisiéramos contabilizar el número de días entre las dos fechas simplemente deberíamos introducir una simple resta de esta forma =A2-A1, la cual nos devolvería un 11 que son los días que hay entre una y otra fecha. Recuerda que al ser una resta el ordenador realiza la diferencia que hay entre los días al igual que si hiciéramos una resta entre el número 12 y 1.

Argumentos de función:

Vamos a ampliar la función =DIAS.LAB(A1;A2) en la que incluiremos los días festivos que hay en el mes de enero, que sería el día 1 y el día 6 para que no se contabilicen como días laborales. Para ello realizaremos una lista de días festivos (en este ejemplo sólo pondremos los días festivos de enero, si quisiéramos podríamos hacerlo con todos los días del año). En la celda A5 introduciremos el 01/01/2010 y en la A6 el 06/01/2010. Ahora para que la función tenga en cuenta estos nuevos días festivos realizaremos una modificación en la función e introduciremos el rango de festivos en el tercer argumento de la función. La función quedará de la siguiente forma: =DIAS.LAB(A1;A2;A5:A6) con lo que el resultado de la misma será 6.

Argumentos de función:

Página 126 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

FUNCIÓN DÍA.LAB Se encuentra entre las funciones FECHA Y HORA y nos devuelve un día laborable que se calcula a partir de una fecha inicial que se obtiene al sumar un número determinado de días dados a esta fecha inicial. Por ejemplo si partimos del día 1/1/2010 y le sumamos 8 días laborales obtendremos como día laborable más cercano a esta fecha inicial el 13 de enero, ya que no se contabilizará ni sábado ni domingo. En esta función al igual que en DIAS.LAB se podrá añadir una lista de festivos que no serán contabilizados como días laborales. Estructura: DÍA.LAB(Fecha inicial;Días laborables;Festivos), donde días laborables es el número de días laborables a contabilizar a partir de la fecha inicial y Festivos será el rango de festivos que queremos que no se consideren como laborables. Ejemplo: en la celda A1 escribiremos la fecha inicial, por ejemplo 01/01/2010 y en la celda A2 pondremos el número de días laborables que deseamos sumar al que aparece en la celda A1, en este caso pondremos un 8 para intentar sumar 8 días laborables a partir del 1 de enero. En la celda B4 escribiremos esta función =DÍA.LAB(A1;A2) y obtendremos como resultado el 13/01/2010 (si nos aparece un número sólo tendremos que cambiar el formato de la celda por formato fecha y hora), observaremos que sólo se han contabilizado días laborables.

Argumentos de función:

Página 127 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Si deseamos añadir fechas que no sean laborables tendremos que realizar una pequeña modificación en esta función. En las celdas A7 y A8 introduciremos el día 01/01/2010 y 06/01/2010. Nos situaremos en la celda B5 y añadiremos la siguiente fórmula: =DÍA.LAB(A1;A2;A7:A8), ahora la fecha resultante será el día 14 de enero.

Argumentos de función:

FUNCION FIN.MES Esta función podemos encontrarla entre las funciones de FECHA Y HORA. Al utilizar esta fórmula obtendremos el último día del mes a partir de la fecha inicial y contabilizando una cantidad de meses. Estructura: FIN.MES(Fecha inicial;Número de meses)

Página 128 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Ejemplo: en la celda A1 escribiremos la fecha de partida 12/01/2010 y en la celda A3 introduciremos la función =FIN.MES(A1;0) y de esta forma obtendremos el 31/01/2010. El 0 del segundo argumento indica que deseamos conocer el último día del mismo mes de la fecha inicial. Si cambiáramos 0 por un 1 obtendríamos como fecha final el 28/02/2010.

Argumentos de función:

FUNCION ES.PAR Pertenecen a las fórmulas de INFORMACIÓN. Esta fórmula nos devolverá el valor VERDADERO si es un número par. Estructura: ES.PAR(Número), donde Número es el valor que se desea saber si es par. Ejemplo: en la celda A1 introduce cualquier número, por ejemplo 1234 y en la celda A3 escribe la función: =ES.PAR(A1) y obtendremos como resultado VERDADERO. Si el valor fuera impar obtendremos como resultado FALSO.

Argumentos de función:

Página 129 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

FUNCION ES.IMPAR Pertenece a las fórmulas de INFORMACIÓN. Con esta función sabremos si un valor es impar. Estructura: ES.IMPAR(Número), donde Número es el valor que deseamos saber si es impar. Ejemplo: en la celda B1 introduciremos como ejemplo el número 2345 y en la celda B3 escribiremos la función =ES.IMPAR(B1) y obtendremos como resultado VERDADERO. Si en este caso el valor fuera par obtendremos como resultado FALSO.

Argumentos de función:

FUNCIÓN ESNUMERO Se encuentra dentro de las funciones de INFORMACIÓN. Esta función nos permite saber si el valor de una celda es un número. Estructura: ESNUMERO(Valor), donde Valor es la celda o valor que deseamos conocer si es o no es un número. Por ejemplo en la celda A1 introduciremos el valor 2345 y para saber si es un número introducimos en la celda A3 la fórmula =ESNUMERO(A1), en este caso obtendremos como resultado VERDADERO. Si el valor no fuera un número el resultado sería FALSO.

Argumentos de función:

Página 130 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

FUNCION ESTEXTO Se encuentra dentro de las funciones de INFORMACIÓN. Con esta función podremos conocer si el valor de una celda es texto. Estructura: ESTEXTO(Valor), donde el argumento Valor lo sustituiremos por una celda o por un valor para conocer si es texto o no. Ejemplo: en la celda A1 introduciremos, por ejemplo, el texto abcdf y en la celda A3 la fórmula =ESTEXTO(A1). El resultado de la función será VERDADERO ya que abcdf es una cadena de texto, si no fuera así el resultado sería FALSO.

Argumentos de función:

FUNCION ESNOTEXTO Se encuentra dentro de las funciones de INFORMACIÓN. Con esta función podremos conocer si el valor de una celda no es un texto.

Página 131 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Estructura: ESNOTEXTO(Valor), donde el argumento Valor lo sustituiremos por una celda o por un valor para conocer si es texto o no. Ejemplo: en la celda A1 introduciremos, por ejemplo, el texto 1234 y en la celda A3 la fórmula =ESNOTEXTO(A1). El resultado de la función será VERDADERO ya que 1234 es un número, si no fuera así el resultado sería FALSO.

Argumentos de función:

FUNCION DECIMAL Se encuentra dentro de las funciones de TEXTO. Esta función nos devuelve en una nueva celda un número o valor de una celda con un número determinado de decimales, además de la posibilidad de poder mostrarlo con separaciones de millares. Estructura: DECIMAL(Valor;Número de decimales;No separación de miles) donde Valor es un número o una celda la cual contiene un número, Número de decimales es el número total de decimales que se desea obtener y No separación de miles es un argumento que puede tener dos posibles valores VERDADERO o FALSO para indicar si deseamos ver o no ver el nuevo valor con el separador de miles. Ejemplo: en la celda A1 introduciremos el valor: 2345,678 con el que trabajaremos. Si en la celda A3 quisiéramos obtener el valor 2.345,68 tendremos que introducir la siguiente función: =DECIMAL(A1;2;FALSO). Hay que observar que el número original tenía tres decimales 678 y al mostrar solo dos, se redondea el tercero al alza. Si el tercer decimal es menor de 5 al reducirlo a dos decimales este se redondeará a la baja.

Argumentos de función:

Página 132 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

FORMULARIOS EN EXCEL Excel contiene diversas opciones para realizar formularios, que nos permitan introducir datos más fácilmente, así como dar al resultado una apariencia más vistosa. Así podemos diseñar, por ejemplo, formularios de factura con listas desplegables (que recojan opciones que puedan admitirse en una celda, sin necesidad de escribirlas), insertar casillas de verificación, etc. Para utilizar los controles de formularios en Microsoft Office Excel 2007, deberemos habilitar la ficha Programador, si no está habilitada. Para ello, seguiremos los siguientes pasos: 8. Hacer clic en el Botón de Microsoft Office y, a continuación, hacer clic en Opciones de Excel. 9. En la ficha Más frecuentes, hacer clic en la casilla Mostrar ficha programador en la cinta de opciones para activarla y, a continuación, hacer clic en Aceptar.

En el panel Programador se encuentra el icono Insertar controles donde aparecen los Controles de formulario:

Vamos a realizar un ejercicio utilizando un cuadro combinado

de los Controles de formulario.

Se trata del cálculo final de una factura, pero utilizando un cuadro combinado. Pasos: 5. 6. 7. 8.

Primero inserta los conceptos de la columna A. En B1 escribe el importe. En D2 y D3 escribe CONTADO y APLAZADO, respectivamente. En B2 insertamos el cuadro combinado. En la barra formulario seleccionamos Cuadro combinado. En B2 dibujamos un rectángulo del mismo tamaño que la celda. Pulsa sobre el rectángulo con el botón derecho del ratón, clic en Formato de control.

Página 133 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

9. 10. 11. 12.

Aparece la ventana para configurar el cuadro de control. En el rango de entrada, seleccionamos el rango D2:D3. En el formato de objeto nos aparecerá el rango en términos absolutos, pulsar Intro. En la ventana de Formato de objeto en Vincular con la celda selecciona la celda E2, también en términos absolutos, y acepta. Haciendo clic en la flecha del cuadro combinado se podrá elegir entre CONTADO y APLAZADO. Si eliges al CONTADO en la celda E2 aparecerá un 1, y si eliges APLAZADO, en la celda E2 aparecerá un 2. La celda de referencia será E2. En B3, supongamos que queremos hacer un descuento del 5% a los clientes que paguen a plazos y un 20% a los que paguen al contado. En B4, la Base Imponible será el importe menos el tanto por ciento elegido sobre el importe. En B5, formato de celda en porcentaje. En B6, total factura, será la Base Imponible más el 18% de esa Base Imponible.

Las celdas D2, D3 y E2 no tienen porqué ser visibles, podemos cambiar el formato de los caracteres a color blanco.

FÓRMULAS MATRICIALES En algunos cálculos interesa que en lugar de producirse un único resultado en una única celda se produzca múltiples resultados simultáneos en varias celdas diferentes a partir de una única colección de datos. Las fórmulas que permiten realizar estos cálculos se denominan fórmulas matriciales, pues utilizan el concepto matemático de matriz (colección de números en forma de tabla). Para comprender mejor el párrafo anterior vamos a ver un ejemplo: Supongamos que una hoja de cálculo contiene en el rango A1:B10 las calificaciones de un examen de 20 alumnos, y queremos que la hoja nos diga automáticamente cuántos insuficientes, suficientes, bien, notables y sobresalientes ha habido. En otras palabras, queremos que el ordenador rellene de manera automática la siguiente tabla: Para ello haremos uso de una función predefinida de la hoja de cálculo que no es otra que la función FRECUENCIA. Piensa que se trata de averiguar con qué frecuencia se ha producido cada una de las calificaciones de la tabla entre las veinte notas de la izquierda. La idea entonces es seleccionar el rango E3:E7 que es donde queremos que aparezcan los resultados y escribir la siguiente fórmula (la escribiremos manualmente):

=FRECUENCIA(datos;grupos)

Página 134 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 donde datos es el rango de datos del que se extrae la información (en nuestro caso A1:B10) y grupos es un rango que contiene los extremos de los intervalos dentro de los cuales se desean agrupar los valores de datos. Con más claridad: una nota es insuficiente si está en el intervalo [0,5), por lo tanto, pondremos como extremo de este intervalo el valor 4,99; una nota es suficiente si está en el intervalo [5,6), por lo tanto, el extremo del intervalo será el valor 5,99; una nota es bien si está en el intervalo [6,7):extremo=6,99; una nota es notable si está en el intervalo [7,8’5), extremo=8,49; y una nota es sobresaliente si está en el intervalo [8’5,10): extremo=10. Debemos escribir estos valores extremos en algún sitio de la hoja de cálculo porque los vamos a utilizar, aunque no es necesario que sean visibles. Para hacerlos invisibles podemos hacer dos cosas: colocarlos en un rango muy apartado, o seleccionar el rango que ocupan y en la opción Formato de celdas seleccionar Proteger y ocultar (otra opción más sencilla es seleccionar el rango que ocupan y poner el color del texto en blanco). Por último, para realizar el cálculo matricial deberemos seguir los siguientes pasos: 1. Seleccionar el rango E3:E7 2. Teclear la fórmula =FRECUENCIA(A1:B10;C3:C7) PASO FUNDAMENTAL: Pulsar la combinación de teclas CONTROL+MAYUS+INTRO para aceptar.

Página 135 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

FUNCIONES SENO, COSENO Y RADIANES CON GRÁFICO DE LÍNEAS

CÁLCULO DEL NIF. FUNCIONES BUSCARV, ENTERO Y

RESIDUO La tabla la podemos hacer vertical u horizontal lo único que cambiaría la función BUSCARV POR BUSCARH.

Página 136 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

EJERCICIO CON FILTROS Confecciona la siguiente lista de información:

Visualiza los datos uno a uno con el formulario de datos: 1. Para abrir el formulario de datos, tenemos que posicionarnos en la lista para que esté activa, y pulsar en el icono Formulario . Como esta opción no está directamente disponible en la Cinta de opciones, podemos añadirla a la Barra de acceso rápido, pulsando el botón Office/Opciones de Excel/Personalizar, y Agregar el icono Formulario…, en la sección de Comandos que no están en la cinta de opciones.

Página 137 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

2. Añade el siguiente registro a través del formulario: Agente 2, Filtros, 20, 64 €. El 15/09/2009, Norte. Para añadir el registro pulsaremos en el botón Formulario y nos aparecerá un cuadro de diálogo donde seleccionaremos la opción Nuevo y rellenaremos los datos del nuevo registro y pulsaremos en Cerrar.

3. Modifica en el primer registro la cantidad de 200 en vez de 100. Para hacer modificaciones en un registro iremos a botón Formulario, seleccionaremos el registro y haremos la modificación y pulsaremos en Cerrar. 4. Busca aquellos registros en el formulario relativo a las ventas realizadas por el Agente 1. Para ello utilizaremos el botón Criterios del botón Formulario, una vez hayamos escrito los criterios pulsaremos INTRO y saldrán los registros especificados en el criterio.

5. Busca aquellos registros con las siguientes condiciones o criterios: 1. Agente 1 2. Producto Cds

Página 138 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

6. Busca aquellos registros con las siguientes condiciones o criterios: 1. Agente 1 2. Producto Cds. 3. Importe ventas <501 € Para los criterios lógicos utilizaremos los signos (> < =).

7. Vamos a ordenar los datos según el valor de los mismos. Nos colocaremos dentro de la tabla y dentro de la columna Código agente para ordenarla según este criterio. Pulsaremos el botón ascendente y después el descendente para comprobar la diferencia:

8. Vamos a ordenar la tabla por dos criterios: Código del agente y según el artículo vendido. Para ello iremos a menú Datos/Ordenar:

Página 139 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 1º Código agente 2º Producto

Ascendente Ascendente

9. Filtras datos. Para ello nos situaremos dentro de la lista. Mostrar ventas del Agente 3. Pulsaremos en Datos/Filtro.

Aparece el número de filas en morado de aquellos registros filtrados. Como podemos observar en la columna donde se ha realizado el filtro aparece el icono del filtro Para que aparezca toda la lista pincharemos en la flecha (Seleccionar todo). 10. Mostrar ventas del Agente 2.

Página 140 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Mostrar ventas del Agente 1 y 2.

Para volver a la situación normal de la tabla hacer clic en Datos/Filtro.

FILTRO AVANZADO Disposición de los distintos criterios en el rango de criterios----la diferencia que hay en poner los datos en una fila o en otra. Los datos de una misma fila se enlazarán con el operador “Y” y los que se encuentren en filas distintas con el operador “O”. Por ejemplo: la información incluida en el rango se interpreta como----seleccionar los registros con el código del Agente 1 y con producto Cds. o los que sumen del Agente 2 >=500 unidades.

Ya se puede activar el filtro avanzado y para ello nos situaremos dentro de la tabla cuyos registros vamos a filtrar y pulsaremos en Datos/Filtro/Avanzadas.

Aparece el rango de la tabla. Pinchar rango de criterios (tabla anterior). Se selecciona las celdas del rango que contiene los criterios; y elegir filtrar sin mover. Para recuperar la información total de la tabla pulsaremos en Borrar.

Página 141 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

EJERCICIOS DE CÁLCULOS EN FACTURAS FECHA DE VENCIMIENTO DE UNA FACTURA. CUENTAS A 30, 60 Y 90 DÍAS En este ejemplo que vamos a ver a continuación, se pretende controlar la fecha de vencimiento de una factura. Las fórmulas tendrán en cuenta la fecha de la factura y la fecha actual. Cuando la fórmula detecte que han pasado más de 30 días, (en un supuesto de factura con vencimiento a 30 días), automáticamente la cantidad saltará a la siguiente columna recordándonos que se han sobrepasado los 30 días de vencimiento. Celdas: B1: =HOY() Esta función muestra la fecha actual del ordenador. F4: =D4+E4 Suma los días de vencimiento más la fecha actual y nos da la fecha de vencimiento. G4: =SI(F4=$B$1;C4;0) Aparece la cantidad facturada si la fecha de vencimiento coincide con la de hoy. H4: =SI(Y(F4<$B$1;($B$1-30)<F4);C4;0) Si la fecha de vencimiento es menor a la de hoy y mayor que la actual menos 30 días, aparecerá la cantidad. I4: =SI(Y(F4<$B$1;($B$1-60)<F4);C4;0)

Página 142 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Si la fecha de vencimiento es menor a la actual menos 30 días, y mayor al actual menos 60 días, aparecerá la cantidad. J4: =SI(F4<$B$1-60);C4;0) Si la fecha de vencimiento es menor a la actual menos 60 días, aparecerá la cantidad.

EJERCICIO APOYO. CÁLCULO DE BENEFICIOS Completar la hoja con las siguientes condiciones: a) b) c) d) e) f) g) h) i) j) k)

Ventas de Enero: 265 € Gastos fijos (todos los meses): 10 € Costes: 60% de las Ventas Beneficio bruto: Ventas menos Costes. Gastos variables: 12 % de las Ventas Total Gastos=Gastos fijos más gastos variables Beneficio neto=Beneficio Bruto menos Gastos Totales Las ventas de febrero son un 15% mayores que las ventas de Enero Copiar la fórmula de ventas de febrero al resto de los meses. Copiar el resto de conceptos de enero al resto de meses. Dar formato a la hoja, según el enunciado y poniendo punto de miles y dos decimales.

Página 143 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 FACTURA CONFECCIONES MARÍA Realizar la factura con la siguiente estructura y en la deberemos introducir las fórmulas correspondientes:

ESQUEMAS Para crear un esquema manualmente debemos crear grupos de filas o de columnas dependiendo del tipo de esquema que queramos hacer. Para crear un grupo debemos seleccionar 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, pero no debemos incluir las filas o columnas que contengan fórmulas de sumario. Realizar el siguiente ejercicio, insertando las fórmulas correspondientes:

Página 144 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Una vez seleccionadas las filas o columnas iremos al menú Agrupar de la pestaña Datos y seleccionamos Agrupar/Autoesquema.

Para trabajar un poco más rápido podemos utilizar las teclas de Agrupar y Desagrupar. Para agrupar, una vez tengamos el grupo seleccionado presionamos Alt + Shift + Flecha derecha.

Página 145 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Para desagrupar, una vez tengamos el grupo seleccionado presionamos Alt + Shift + Flecha izquierda. Para borrar el esquema iremos a Datos/Desagrupar/Borrar esquema.

SUBTOTALES Obtiene el total de varias filas de datos relacionados insertando automรกticamente subtotales y totales para las celdas seleccionadas. Realizar la siguiente tabla e insertar la fรณrmula correspondiente:

Con la opciรณn Subtotales de la etiqueta Datos podemos obtener distintos niveles de informaciรณn, como por ejemplo al seleccionar la tabla ver los subtotales por empresa, promedios, etc.

Pรกgina 146 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

REGISTRO DE FACTURAS Realizaremos el siguiente ejercicio y lo guardaremos con el nombre Registro de facturas. Nombre de la hoja, Fras. Emitidas. Introducir las fórmulas adecuadas teniendo en cuenta que el descuento se aplicará para aquellos clientes cuya venta sea igual o superior a 360,60 €. Y que los portes se cobrarán a aquellos clientes cuya venta sea inferior a 360,60 €.

Realizar en la hoja 2 con el nombre Fras. Recibidas la siguiente tabla:

Página 147 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

En la tercera hoja realizar la liquidación del IVA y llamarla LIQUIDACIÓN IVA.

En la hoja 4 que llamaremos GRÁFICO, realizaremos uno que nos refleje la liquidación del IVA.

Página 148 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

VALIDACIÓN AUTOMÁTICA DE DATOS Consiste en autorizar o no la entrada de unos datos determinados en las celdas de datos cuyo contenido nos interesa controlar. Indica valores incorrectos de datos y determina el rango de datos válidos. Ejemplo:

Seleccionamos la celda o celdas donde queremos realizar el control donde en este caso serán las celdas de la columna Edad ya que por razones laborales la edad es de 18 a 65 años. Para ello iremos a la pestaña Datos/Herramientas de datos/Validación de datos:

•

Ofrecer un mensaje de información: Datos/Validación de datos/Mensaje de entrada En la ficha Configuración entrar los datos: Número entero; entre; 18; 65

Página 149 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

En la ficha Mensaje entrada: Título: Edades Válidas. Mensaje de entrada: El valor de la edad debe encontrarse entre 18 y 65.

Cada vez que nos situemos sobre una de las celdas de la columna Edad nos aparecerá un mensaje informativo sobre los datos que podemos introducir.

•

Advertir al usuario si cometen un error. Datos/ Validación de datos/Mensaje de error Se activa Mostrar mensaje de error…

Página 150 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 En la ficha Mensaje de error en Estilo: Información. Título: Error. Mensaje de error: El valor de la edad debe encontrarse entre 18 y 65.

El resultado obtenido al introducir una edad fuera de los límites establecidos es el siguiente:

PROTEGER En la pestaña Revisar podremos proteger una hoja o un libro:

En este ejercicio veremos cómo proteger una hoja. A esta hoja la llamaremos IMPRESO y la protegeremos pero de forma que las celdas que tiene un usuario que contestar no las dejemos protegidas. Para ello primero seleccionaremos las celdas que no queremos proteger y haremos un clic derecho seleccionando Formato de celdas/ Proteger (desactivar bloqueada) y después iremos a Revisar/Proteger hoja/Seleccionar celdas desbloqueadas.

Página 151 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Una vez hayamos realizado todos los pasos solo podremos modificar las celdas que deberán ser rellenadas por los usuarios. Colocaremos un fondo a la hoja.

Desplazarse dentro de un rango en un orden establecido Puede ser de utilidad para gestores de bases de datos a la hora de introducir valores en los diferentes campos de ésta. O también para restringir el uso de celdas concretas de nuestra hoja de cálculo. Hay que asignar nombre a un rango de celdas, pero una a una en el orden en el que queramos movernos dentro de él; una vez creado el nombre accedemos y una vez activado, bien validando con Enter o Tabulador, nos desplazaremos dentro de éste con el orden previamente establecido. Con CTRL se selecciona cada una de las celdas antes de asignar el nombre al conjunto de celdas.

Nos posicionamos en el cuadro de nombres, y escribimos Por_orden (para separar palabras utilizaremos _) que será el nombre del rango seleccionado y presionaremos ENTER.

Página 152 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Al insertar los datos, hay que activar nuestro rango, desplegaremos el cuadro de nombres y seleccionamos Por_orden.

Al introducir los datos lo haremos en el orden que establecimos, con ENTER o el TABULADOR.

RANGOS DINÁMICOS: FUNCIÓN DESREF Definiremos primero qué entendemos por rango dinámico en Excel; sabemos que podemos nombrar los rangos que utilizamos, pero lo habitual es que estos rangos no sean fijos, es decir, no tenga siempre la misma dimensión, el mismo número de registros. Es en este punto cuando Excel nos proporciona la posibilidad de crear un rango dinámico. Y lo haremos desarrollando la función DESREF, anidando otra función importante como CONTARA en ella. Introducir el siguiente rango de datos, por ejemplo A1:A3.

Dar nombre a este rango con el nombre RangoNot y para ello iremos a la pestaña Fórmulas/Nombres definidos/Asignar nombre a un rango.

Introducir la siguiente fórmula en Hace referencia a: =DESREF(A1;0;0;CONTARA(A1:A100);1) Al seleccionar las celdas para añadir el rango automáticamente sale con referencias absolutas.

Página 153 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Como argumento hemos introducido la función CONTARA que nos va a permitir que el rango solo sea el conjunto de celdas contiguas y con datos; pulsamos en Aceptar y ya está el rango definido. Para ver cuántas celdas ocupa el rango, escribimos el nombre en el cuadro nombre, ENTER.

Vemos que es un rango de 3 celdas. Vamos a introducir más datos en la columna A y volveremos a escribir en el cuadro de nombres RangoNot, pulsaremos INTRO, y obtendremos:

Como podemos ver el tamaño del rango es mayor y va en función de las celdas que están ocupadas en la columna A. Más celdas con datos, rango más amplio, menos celdas rango más pequeño. La función DESREF nos ha ayudado a lograrlo.

Rangos dinámicos con validación de datos: función DESREF Utilizaremos la función DESREF Y CONTARA. Para visualizar el resultado que obtendremos combinando ambas funciones, ejercitaremos otra herramienta de Excel como es la validación. Supongamos un listado de clientes por comunidades autónomas, que en un futuro se puede ampliar o disminuir con el tiempo. Lo primero que tenemos que realizar es el crear un nombre, al que asignaremos la característica de dinámico mediante la función: =DESREF(Hoja 1!$A$2;;;CONTARA(Hoja1!$A:$A)-1;1)

Página 154 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 En esta ocasión empleamos DESREF para determinar un rango que empieza en la celda A2 y que tendrá un alto dado por la función CONTARA(A:A)-1, es decir, cuenta todas las celdas no vacías de la columna A, y le resta Uno para discriminar el rótulo de la columna. Una vez generado el nombre COMUNIDADES, ya podremos emplearlo con la herramienta Validación con la característica Lista:

Que aplicado en la celda C4 nos permite desplegar el listado de comunidades:

La verdadera ventaja de esta función es visible cuando incrementamos el listado de comunidades; situación por la que no nos tendremos que preocupar a la hora de redefinir rangos ni funciones, ya que tal cual quedó configurada reconocerá los nuevos elementos del listado.

Página 155 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

ESCENARIOS Supongamos que tenemos varios supuestos de petición de un crédito con varios tipos de interés, varios posibles períodos, etc. Podríamos crear una hoja de amortización de préstamos y cambiar las celdas manualmente. Otra forma de hacerlo es utilizando escenarios. Un escenario es un conjunto de celdas cambiantes que puede grabarse para estudiar diferentes resultados. Crear una sencilla hoja para un cálculo de amortización de préstamo:

La fórmula de la celda B5 es: =ABS(PAGO(B2/12;B3*12;B1)), calculando así los pagos mensuales. Podemos utilizar la función financiera =PAGO(B2/12;B3*12;B1) y el resultado nos saldrá negativo(celda D5). O bien, función ABS. Bien, imaginaremos varios supuestos: Interés al 5% y 5 años Interés al 5% y 6 años Interés al 4,5% y 3 años Capital 9.000, interés 4% y 5 años. Para crear un escenario: 1. En la ficha Datos, en el grupo Herramientas de datos, hacer clic en Análisis Y si y después en Administrador de escenarios.

2. Hacer clic en Agregar

Página 156 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 3. En el cuadro Nombre del escenario, escriba un nombre para el escenario. 4. En el cuadro Celdas cambiantes, especifique las referencias de las celdas que desee cambiar. Nota: para preservar los valores originales de las celdas cambiantes, crear un escenario que utilice los valores originales de las celdas antes de crear escenarios que cambien los valores. 5. En Proteger, seleccionar las opciones que deseemos. 6. Hacer clic en Aceptar.

7. En el cuadro de diálogo Valores del escenario, introducir los valores que deseemos para las celdas cambiantes. 8. Para crear el escenario, hacer clic en Aceptar.

Instrucciones para el ejercicio: • • • • •

•

Como nombre del escenario, escribir cualquier texto (Escenario 1) Como celdas cambiantes, marcar el rango B1:B3 y pulsar Aceptar. Observar que aparecen tres celdas que permitirán los cambios. Escribir en la primera 12000, en la segunda 0,05 y en la tercera 5 y pulsar Aceptar. El escenario ya se ha creado y añadiremos los ejemplos restantes. Una vez finalizado, seleccionaremos el primer escenario y pulsaremos en Mostrar. Haremos lo mismo con los demás y observaremos como cambian las celdas de la hoja del ejemplo. De esta forma podemos preparar varios escenarios y visualizar los resultados de una forma más cómoda. Pulsaremos en el botón Resumen…

Página 157 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

•

Como celdas resultantes, seleccionaremos B5 y aceptaremos. Se habrá creado una hoja nueva con el resultado de los 4 estudios de posibilidades y podremos observar y estudiar los resultados.

TABLAS DE DATOS Una tabla de datos es un conjunto de celdas relacionadas mediante una o varias fórmulas, aplicando diferentes valores constantes y analizando e interpretando los resultados. Una variable es una entrada sobre la que ejercemos un control, y que afecta a una serie de cálculos y resultados que dependen de ella. Supongamos que queremos saber la cuota a pagar de 3 a 7 años, a un interés que va del 4% al 7% con unos incrementos de 0,25% en 0,25%. Podríamos crear 45 escenarios distintos, pero incluso con esa opción sería una pasada de trabajo. Veremos la forma de hacerlo utilizando tablas. Escribiremos la siguiente tabla en la misma hoja anterior:

Página 158 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

En la fila 7 hemos puesto los años, y en la columna A los incrementos de interés. Es obligatorio colocar como primera celda (A7) el valor con el que se desea jugar. Pasos a seguir: 1. Seleccionaremos B8:B16 y colocaremos el símbolo de millares. (Por defecto, el resultado de una tabla se muestra con varios decimales). 2. Ahora seleccionaremos todo el rango de datos A7:F16 y accederemos a Datos/Análisis Y si/Tabla de datos

3. Como celda de entrada de la fila, pulsar en B3 que es la que contiene el dato para calcular la fila 7. 4. Como celda de entrada de columna, seleccionar B2. 5. Aceptar.

Ahora solo es cuestión de arreglarla un poco, resaltando el valor inicial que coincide con el cálculo de la tabla y así de esta forma podremos ver de un vistazo el resultado con varios años y varios tipos de interés.

Página 159 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Ahora analizaremos la variación de una cuota anual de un préstamo hipotecario según cambien dos de sus parámetros (tipo de interés y plazo). Conocidas las condiciones de un préstamo: Principal: 150.000,00 € Tipo interés: 3,36% anual Plazo: 25 años Y aplicada la función financiera PAGO sobre éstos =PAGO(tasa, número períodos, valor). Obtendríamos inicialmente una cuota anual de 8.963,36 €.

Es a partir de este resultado desde donde vamos a obtener una Tabla de datos donde poder observar la sensibilidad al cambio de la cuota respecto de dos de sus tres variables. Empezaremos preparando lo que será nuestra tabla; para lo que en primer lugar vinculamos la celda donde habíamos obtenido la cuota anual (calculada con la función PAGO) en el lugar elegido, a continuación proyectamos por filas y/o columnas ambas variables de estudio.

Página 160 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 En las filas dispondremos distintos valores de la variable Tipo de interés y en la columna valores del Plazo (estos valores los podremos cambiar en cualquier momento, según nuestras necesidades). Llegados a este punto sólo nos queda aplicar sobre nuestro rango definido la herramienta de análisis, tan sólo remarcaremos el rango en cuestión y ejecutaremos desde el menú Datos la herramienta Tabla (para Excel 2003) o desde el menú Datos/Análisis Y Si/Tabla de datos (para Excel 2007), nos aparecerá una ventana con dos argumentos por definir donde seleccionaremos en el argumento “Rango de entrada(fila)” aquella celda de nuestra formulación original la variable que represente los valores especificados en la fila; para nosotros la celda B3 que es la del Tipo de interés. De igual forma actuaremos sobre el argumento “Rango de entrada columna”, donde seleccionaremos la celda B4 que era en origen nuestra celda de Plazo. Sólo nos queda Aceptar para que se genere nuestra Tabla de datos sensible a las variaciones de dos variables.

RESULTADO:

Observar que nos ha generado una Función matricial llamada Tabla, que no será modificable. Remarcar también que el análisis se podrá realizar sobre una sola variable.

Página 161 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Ejemplo de Tabla de datos-Excel 2007 Desarrollaremos un análisis de sensibilidad sobre dos variables mediante la herramienta, ya conocida, de la Tabla de datos. Suponemos una inversión con una tasa de rentabilidad del 2,25 % y los siguientes flujos:

En la situación actual el Valor actual de la operación es 1.031,53 € =VNA.NO.PER(C5;B3:G3;B2:G2) Con los siguientes argumentos: =VNA.NO.PER(tasa;valores;fechas) Lo que nos interesa es analizar distintos aspectos de la inversión según varíen dos valores, por ejemplo, la tasa de rentabilidad y el importe del último cobro. Como dato especial mencionar que al ser fechas no periódicas hemos empleado la función de VNA.NO.PER que tiene en cuenta esa circunstancia. Para ejecutar esta herramienta desde Excel 2007 nos deberemos dirigir al menú Datos/Herramientas de datos/Análisis Y si/Tabla de datos:

Marcaremos por tanto el rango donde pretendemos analizar esta variación de resultados, sabiendo que previamente hemos vinculado la celda superior izquierda del rango a seleccionar con el resultado que pretendemos estudiar, en nuestro caso, con el valor actualizado (=VNA.NO.PER). Seleccionaremos, en la ventana diálogo de Tabla de datos, como Celda de entrada(fila) la celda C5, i.e., donde está la rentabilidad sobre la que se calcula el valor actual; y como Celda de entrada(columna) la celda G3, que refleja el último cobro de la inversión.

Página 162 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

El resultado final obtenido será:

La interpretación de los datos era muy obvia desde el planteamiento, a mayor rentabilidad y mayor fuera el último cobro, mayor es el valor actual de la operación.

TABLAS DINÁMICAS Un informe de tabla dinámica es una forma interactiva de resumir rápidamente grandes volúmenes de datos. Utilizaremos un informe de tabla dinámica para analizar datos numéricos en profundidad y para responder preguntas no anticipadas sobre los datos. Un informe de tabla dinámica está especialmente diseñado para: 1. Consultar grandes cantidades de datos de muchas maneras diferentes para el usuario. 2. Calcular el subtotal y agregar datos numéricos, resumir datos por categorías y subcategorías, y crear cálculos y fórmulas personalizados. 3. Expandir y contraer niveles para destacar los resultados y desplazarse hacia abajo para ver los detalles de los datos de resumen de las áreas de interés. 4. Desplazar filas a columnas y columnas a filas para ver resúmenes diferentes de los datos de origen. 5. Filtrar, ordenar, agrupar y dar formato condicional a los subconjuntos de datos más útiles e interesantes para poder centrarse en la información que nos interese. Para crear un informe de tabla dinámica, en la ficha Insertar, en el grupo Tablas, hacer clic en Tabla dinámica, y a continuación en Tabla dinámica.

Página 163 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

El empleo de las Tablas dinámicas se justifica si la cantidad de datos a manejar es importante. Por ejemplo:

Opciones que contiene: en Tabla dinámica/Opciones/Mostrar páginas de filtros de informes…, se puede abrir un reporte en varias páginas, siempre que se encuentre en el diseño como un campo de página.

Página 164 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Para realizar gráficos contamos con la herramienta Gráfico dinámico:

TABLAS O LISTAS EN EXCEL I De la versión Excel 2003 (Lista) a Excel 2007 han cambiado de nombre aunque básicamente sigue siendo la misma utilidad. ¿Pero para qué sirve esto de las Tablas o Listas? Bien, cuando se crea una “Tabla” en algunas de nuestras hojas de cálculo, los datos se podrán administrar y analizar independientemente de la información que haya fuera de la tabla; por ejemplo, podremos filtrar las columnas de la tabla, agregar una fila para totales, aplicar un formato a la tabla, etc. Una de las ventajas es que podremos añadir columnas anexándolas como parte de la “Tabla”, e incluso incluir en éstas fórmulas o funciones que de manera automática actualicen sus valores, autorrellenándose y adaptándose al número de registros existentes cada momento en nuestra Tabla de datos. Cuando no queramos seguir trabajando con los datos en una “Tabla”, la convertiremos en un rango normal a la vez que conserva el formato de estilo de tabla que habíamos aplicado. Si no necesitamos la tabla, también se puede eliminar. Muy importante, no confundir esta herramienta con las Tablas de datos!!!, ya que estas últimas corresponden a las herramientas de análisis de sensibilidad (Tasa de rentabilidad, etc.) El primer paso es generar la “Tabla”. Suponemos que tenemos los siguientes datos:

Página 165 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Para crear nuestra Tabla o Lista iremos al menú Insertar/ grupo de opciones Tablas/Tabla. También podremos hacer uso del método abreviado del teclado, pulsando CTRL + q o CTRL + t.

Convertimos nuestro rango de datos en una Tabla. Desde este momento ya podremos tratar nuestro rango como una unidad, para darles formatos, actualizaciones, etc.

Probaremos a incluir una nueva columna para sumar los datos de las columnas B a H. Al crear una Lista/Tabla Excel agrega automáticamente el Autofiltro. Nos fijaremos en cómo reconoce Excel las distintas celdas seleccionadas: =SUMA(Tabla1[[# #Esta fila];[Lunes]:[Domingo]]) Al aceptar la fórmula, ésta se adapta, se autorrellena con el mismo formato hasta el último registro de la “Tabla”; y no sólo eso, sino que además, al ir introduciendo nuevos registros esta columna que hemos añadido de Total semana se actualizará automáticamente.

Página 166 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Nota: en la fórmula, al hacer el ejercicio en distintas hojas del libro, el asistente, me toma Tabla 2; si se realiza en la misma hoja de datos sería Tabla 1. Se podrá insertar tantas columnas o filas nuevas como queramos y donde queramos; ya que siempre quedará vinculada y formará parte de nuestra “Tabla”. Cuando no queramos seguir trabajando y haciendo uso de las ventajas de la “Tabla” o “Lista” nos iremos a Herramientas de la tabla y la convertiremos en rango; respetando todas las formulaciones añadidas, pero cambiándolas por las referencias relativas o absolutas correspondientes a las celdas afectadas.

TABLAS O LISTAS EN EXCEL II Tenemos los siguientes datos:

Las Tablas/Listas se diferencian de los rangos normales, entre otras cosas: • •

Al crear una Lista/Tabla Excel agrega automáticamente un autofiltro. La Lista/Tabla se expande automáticamente al agregar una celda. Todas las referencias ligadas a la Lista/Tablas se adaptan al cambio automáticamente (gráficos, nombres, fórmulas).

Página 167 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 • •

Al agregar valores a la Lista/Tabla, todas las fórmulas en columnas con fórmulas son copiadas automáticamente. Si usamos la tecla TAB para navegar, Excel selecciona automáticamente la celda siguiente de izquierda a derecha y de arriba hacia abajo.

Generar la tabla:

Agregar la sucursal 5 a nuestra base de datos. Al hacerlo te añade automáticamente el resto de celdas de la tabla. Insertaremos los datos en las diferentes columnas.

Otra característica importante es la posibilidad de agregar una fila de totales al final de la Tabla. Esta fila se adapta automáticamente a los cambios en las dimensiones de la tabla. Para agregar la fila de totales en Excel 2007, pestaña de Herramientas de tabla/Diseño/Fila de totales.

En nuestro caso Excel pone el total sólo en la última columna con datos numéricos:

Podemos ver que Excel ha agregado la función SUBTOTALES con la opción 109. Es decir que si filtramos la tabla, el resultado mostrará sólo el total de las filas visibles.

Página 168 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Podemos cambiar esta función por otras pulsando la flecha en el borde izquierdo de la celda.

Para poner totales en las otras columnas con valores numéricos seleccionamos la última celda de la columna lo que hará aparecer la flecha de opciones de totales.

REFERENCIAS CIRCULARES Las referencias circulares son situaciones de error que aparecen cuando como argumento de una función, especificamos la propia celda en la que estamos escribiendo esa función. Una referencia circular es en suma, un error que aparece cuando introducimos una fórmula en una celda desde la que se alude a ESA propia celda; esto generaría para Excel un cálculo circular que no se podrá concluir. Realizaremos el siguiente supuesto para comprobar esta situación un tanto especial. El sueldo de un vendedor está estipulado en un 20% de los beneficios que obtenga una empresa. La tabla de cálculo sería la siguiente:

Página 169 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Como se observa en la imagen, el beneficio de la empresa será igual a las ventas, menos los gastos y menos los sueldos. Sin embargo el sueldo a su vez será el 20% del beneficio.

De este modo se ha introducido una referencia circular. Para que este cálculo, resultado de una referencia circular se realice correctamente, deberemos acceder a las Opciones de Excel, y dentro del grupo correspondiente a Fórmulas, marcaremos la casilla correspondiente a Habilitar cálculo iterativo. Ahora de este modo Excel calculará los resultados correctos para ambas celdas.

El resultado obtenido de este modo en nuestro ejemplo será el siguiente:

Cuando esta casilla está desactivada y se intenta establecer una referencia circular, Excel impide su utilización advirtiendo del posible error mediante el siguiente cuadro de diálogo:

Tras este aviso por pantalla, los cálculos derivados de la referencia circular no se realizarán. NOTA: En Excel 2007 cuando aparece el panel Advertencia de referencia circular, si aceptamos dicho panel, se da acceso a la Ayuda de Excel en donde se explica con detalle cómo aceptar, quitar o corregir una referencia circular.

Página 170 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

FUNCIONES BASE DE DATOS La función BDMAX Devuelve el máximo valor de una columna de una base de datos. Ejercicio: El Director general de la empresa Valdepeñera Servicios, S.L., quiere analizar las ventas semanales de sus comerciales. Para ello, confecciona una tabla de datos que recoge los artículos y ventas de cada uno de sus vendedores.

Vamos a utilizar la función BDMAX

Sintaxis: = BDMAX(base de datos; nombre de campo; criterios) base de datos: es el rango donde se ubica la base de datos. nombre de campo: es la celda que encabeza la columna de la cual se extrae el máximo valor. También puede ser un número de orden de la columna, contando de izquierda a derecha. criterios: rango que indica cuales son los valores a evaluar para extraer el máximo.

=BDMAX(B2:D9;D2;H2:H3) La función se interpreta dada la base da datos del rango B2:D9, para el campo de la celda D2, devuelve el máximo valor que cumple con la condición del rango H2:H3.

Página 171 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Utilizando la función BDMAX sobre el rango B2:D9, calcula el máximo valor de ventas del vendedor indicado en la celda H6. Inserta la función en la celda F6. =BDMAX(B2:D9;D2;H5:H6)

La función BDSUMA Devuelve la suma de los valores de una columna de una base de datos, según un criterio. Sintaxis: = BDSUMA(base de datos; nombre de campo; criterios) base de datos: es el rango donde se ubica la base de datos. nombre de campo: es la celda que encabeza la columna en la cual se suman los valores. También puede ser un número de orden de la columna, contando de izquierda a derecha. criterios: rango que indica cuales son los valores que se van a sumar. En la misma base del ejercicio anterior, cópiala en otra hoja para calcular la suma de ventas del Artículo indicado en la celda H3.

Página 172 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 La función BDCONTAR Cuenta las celdas que contienen números en una columna de una lista o base de datos y que concuerdan con las condiciones especificadas. El argumento nombre_de_campo es opcional. Si se pasa por alto, BDCONTAR cuenta todos los registros de la base de datos que coinciden con los criterios. Sintaxis: BDCONTAR(base_de_datos;nombre_de_campo;criterios) Siguiendo con el ejemplo de los anteriores ejercicios, copiamos la base de datos en otra hoja para aplicar la función BDCONTAR. Contar el número de veces que ha sido vendido el Art. A.

Resultado:

La función BDPROMEDIO Devuelve el promedio de los valores de una columna de una lista o base de datos que coinciden con las condiciones especificadas. Sintaxis: BDPROMEDIO(base_de_datos;nombre_de_campo;criterios) Aplicar la función BDPROMEDIO con los mismos criterios que la función BDCONTAR.

Página 173 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Resultado:

BOTÓN DE OPCIÓN Es muy común en distintos tipos de formularios donde hay un número de alternativas fijas, pero sólo se debe optar por una única opción. El ejemplo más claro: las respuestas de una encuesta o examen. Tendremos dos botones: uno que diga SI, otro NO y en otra celda nos dirá “Seleccionó” y la opción que hayamos elegido. Pasos a seguir: 1. Insertar el control de “Botón de opción” En la pestaña Programador/Insertar, clic en la flecha y nos desplegará las opciones. Elegimos el “Botón de opción” que se encuentra dentro de Controles de formulario (No el de Controles ActiveX que se utilizan para trabajar con macros).

Página 174 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 En este ejemplo, dibujaremos el control donde se encuentra la celda A2. Borramos el contenido y escribimos “SI”. También podemos modificar el tamaño del control. Si en algún momento llegamos a perder el control del mismo (es decir, si hacemos clic y solamente nos marca el botón), con botón derecho del ratón podemos elegirlo nuevamente. De la misma forma (o simplemente copiando y pegando el primero), dibujamos el botón para la opción “NO”.

2. Vincular la opción elegida con una celda Una vez que la elección está hecha (en este caso entre SI y NO), necesitamos que nos muestre la elección para poder operar sobre ella (elegir un dato a mostrar de una lista, indicar el número de fila o columna donde se encontrará el dato,…) El resultado de los botones es un número que nos indica cuál de todos los botones está seleccionado. En nuestro caso será 1 si está elegido el botón SI y 2 si está seleccionado el botón NO. Para ver este resultado, debemos “vincular” los botones con alguna celda donde nos mostrará el resultado. Haremos clic botón derecho sobre uno de los botones, “Formato de control”. En este menú, vamos a la pestaña Control/Vincular con la celda e introducimos la celda donde queremos nos muestre el valor. En este caso, B2.

Cada vez que seleccionemos alguno de los botones, irá cambiando la celda B2 por los valores 1 ó 2 según la opción elegida:

Página 175 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Con ese valor haremos que muestre el texto “Seleccionó” y la opción seleccionada. Se podría hacer utilizando la función SI =SI(B2=1;”Seleccionó SI”;”Seleccionó NO”), pero lo haremos con la función ELEGIR. En la celda A5 introduciremos la fórmula: =”Seleccionó “&ELEGIR(B2;”SI”;”NO”) Nota: dejaremos un espacio en blanco después de escribir Seleccionó para que haya un espacio entre SI o NO. Lo que estamos uniendo es dos cadenas de texto (a través del operador &, que es equivalente a usar CONCATENAR.CONCATENAR(texto1;texto2;…). Ejemplo tenemos identificados en campos separados el Apellido y el Nombre. Necesitamos unificarlos de la forma que queremos que se muestre. Aplicar la función:

La primera cadena es “Seleccionó”. La segunda es el resultado de la función ELEGIR. Si B2 contiene el valor 1, el resultado será el primero de los valores que se encuentran en esta función después del “;” (“SI”) mientras que si el valor es 2, traerá el segundo valor (“NO”). Todos los botones que se inserten en la hoja estarán encadenados entre sí y solo podremos elegir entre uno de ellos. Si necesitamos tener varios conjuntos de botones que sean independientes entre sí, deberemos agruparlos dentro de “Cuadros de grupo”.

Cuadros de grupo Si queremos realizar varios conjuntos de botones, deberemos crear cuadros de grupo y si lo que queremos es crear un conjunto de botones, deberemos crear dos Cuadros de grupo. De esta forma, podremos manejar dos conjuntos de botones excluyentes entre sí: por un lado se manejarán las comunidades autónomas y por otro los años. Pasos a seguir: 1. Insertar los Cuadros de grupo

Hay que hacerlo antes de insertar los botones. Dibujaremos dos cuadros uno al lado del otro. Si tenemos apretado ALT mientras dibujamos el cuadrado, los bordes coincidirán exactamente con las filas y columnas de Excel.

Página 176 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Para cambiarle el nombre a los cuadros, sólo debemos seleccionarlos y hacer clic nuevamente en la zona del nombre (donde dice “Cuadro de grupo 1”).

2. Insertar los botones

3. Vincular botones Por ahora tenemos una serie de botones que no hacen nada más que rellenarse cuando se los seleccionan. Hay que vincular los botones con una celda donde tendremos un resultado numérico de la selección. Por ejemplo, como celda vinculada a H9 para el resultado de la comunidad autónoma (que nos indica la fila para el posterior ejercicio de la matriz de ventas que haremos) y H10 para el resultado del año (columna de la matriz de ventas). Para realizar el vínculo, debemos hacer clic derecho sobre uno de los botones, ir a Formato de control y en la pestaña Control introducimos la celda dentro de Vincular con la celda.

Página 177 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Introducimos los siguientes datos:

4. Resultado de la selección de los botones Como los resultados numéricos de los botones nos indican a su vez la fila y columna dentro de la matriz de ventas, el resultado de Ventas según la selección lo podemos obtener utilizando la función INDICE. =INDICE(H4:J7;H9;H10)

Donde: • H4:J7 es la matriz con los resultados • H9 es la celda vinculada con la elección de la comunidad autónoma (la fila de la matriz de ventas) • H10 es la celda vinculada con el año (columna de la matriz de ventas) Hemos realizado con dos conjuntos de botones independientes entre sí, el trabajo de una relación con otros datos.

Página 178 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

FUNCIÓN REPETIR Gráfico en las propias celdas Introducir los siguientes datos en un libro de Excel con la fórmula correspondiente:

Añadir una columna con el nombre PORCENTAJE que represente un % sobre el total de ventas, dividimos cada celda entre el total Importe Ventas, multiplicado por 100 para obtener el %.

La idea es usar la función REPETIR para repetir el símbolo “|” (alt gr + 1 en el teclado) un número determinado de veces y así crear el efecto de barra. También tendremos que dar a las celdas en las que se inserte ese símbolo un formato de tamaño 7 u 8, sino aparecerá como “|||||||”. En la cuarta columna y en D3 escribimos =REPETIR(“|”;C3), o con el asistente de funciones, y copiamos la función arrastrando hacia abajo. Nos creará una barra cuyo tamaño dependerá de las ventas de las sucursales. Al estar en % evitamos que la representación de las barras sea muy grande.

Página 179 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

INMOVILIZAR FILAS Y COLUMNAS En la hoja de cálculo podemos bloquear la primera fila o la primera columna, o bien aquellas que nos interese tener siempre visibles. Pestaña Vista/Ventana y hacer clic en Inmovilizar paneles. Son tres las opciones que aparecen: • • •

Inmovilizar fila superior: para mostrar siempre la primera fila Inmovilizar primera columna: para que ésta aparezca fija Inmovilizar paneles: para que se repitan siempre los campos que nos interesen

Para ello pondremos el cursor debajo del punto en el que deseemos que aparezca la división y pulsaremos sobre la opción Inmovilizar paneles. Cuando queramos volver a la situación anterior, en la herramienta Inmovilizar paneles, haremos clic en la opción Movilizar paneles, para que podamos desbloquear filas o columnas fijas.

RELACION ENTRE INT.EFECTIVO Y TASA.NOMINAL Deberemos saber qué es un interés efectivo anual, o una tasa nominal y cómo se relacionan con los tipos de interés efectivo periódicos (diferentes del anual). Las fórmulas financieras serían, siendo m el número de periodos en que dividimos el año:

• •

Para la Tasa nominal: jm = im . m Para el Tipo efectivo: (1 + im)m =(1 + i)

También debemos conocer nuestras funciones de Excel a usar: 1. TASA.NOMINAL(tasa efectiva; núm. periodos en año) 2. INT.EFECTIVO(tasa nominal; núm. periodos en año) Vemos como la una sin la otra no tiene sentido y que es imprescindible conocer la relación entre ambas:

jm = im . m Página 180 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Ejemplo: Tenemos un tipo de interés efectivo anual del 10% y queremos saber cuáles son los diferentes tipos efectivos semestrales, trimestrales, etc.; y cómo puedo relacionar directamente entre un bimestral y un cuatrimestral. Construimos nuestra tabla de inicio con las diferentes periodicidades necesitadas dentro de un año:

Añadimos ahora para cada periodicidad cual es el tipo nominal de nuestro interés efectivo anual, aplicando la función: =TASA.NOMINAL($B$3;C3)

Copiaremos la fórmula para el resto de los períodos. Formato de celdas: Porcentaje. Para obtener el tipo de interés efectivo periódico dividimos la tasa nominal entre el número de periodos (im=jm/m). Arrastraremos la fórmula al resto de los períodos con el formato

porcentaje.

Ya tenemos los diferentes intereses efectivos con distintas periodicidades equivalentes a un 10%. Conversión entre INT.EFECTIVO y TASA.NOMINAL Anidando estas funciones financieras y combinándolas, obtendremos el equivalente entre distintos tipos efectivos periódicos. Con un listado con los períodos más habituales: mensual, trimestral, cuatrimestral y semestral con el número de períodos de interés compuesto por año. Realizaremos una tabla de conversión de tipos efectivos, utilizando la función BUSCARV para obtener el nº de períodos a la tabla:

Página 181 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Por ejemplo, queremos conocer cuál es el tipo efectivo mensual equivalente a un 3% efectivo trimestral. Con la siguiente función anidada: =TASA.NOMINAL(INT.EFECTIVO(E5*$D$5;$D$5);$D$3)/$D$3 Fijémonos que el valor devuelto corresponde a un tipo nominal dividido por el número de períodos, por tanto es un tipo efectivo periódico, en el ejemplo, mensual; que toma como base de cálculo un interés efectivo [INT.EFECTIVO(E5*$D$5;$D$5)] que obtenemos a partir de nuestro efectivo trimestral convertido en nominal.

Eliminar datos privados en documentos Excel 2007 Un libro Excel puede contener una serie de elementos ocultos constituidos por información interesante que sirve para nuestra propia administración, datos tales como información sobre el documento, sobre la empresa, comentarios, encabezados y pies de cara a imprimir, archivos anexos con contenido XML, filas y columnas ocultas... Esta información, en el caso de que el libro de trabajo se desee distribuir y entregar a terceras personas, pudiera ser portadora de datos confidenciales (que hemos colocado como parte del trabajo, pero para nosotros mismos) y que no deseamos que el destinatario del documento (cliente final, proveedor o compañero de trabajo, por ejemplo) vean.

Página 182 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Para eliminar estos datos hay que seguir los siguientes pasos: 1. En Microsoft Excel 2007, clic sobre el Botón de Office. 2. Selecciona la opción Preparar / Inspeccionar Documento.

3. De las opciones de Inspeccionar documento, podemos seleccionar: Comentarios, Revisiones, Versiones y anotaciones, Propiedades del documento e información personal, Datos XML personalizados, Encabezados, Pies de página y marcas de agua y Texto Oculto.

4. Seleccionamos las opciones que quieres revisar y clic sobre el botón Inspeccionar Documento. 5. Una vez realizada la revisión, el sistema te proporciona un informe en el que podrás quitar los elementos que existan dentro del documento. Podrás hacerlo pulsando el botón Quitar todo.

Página 183 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

6. Para continuar con la edición o cerrar el documento, clic sobre el botón Cerrar. AVISO: Una vez borrada información mediante la opción Inspeccionar documento, de ser guardado el libro con el mismo nombre, o aceptado los cambios, el proceso es IRREVERSIBLE, es decir que esa información se pierde y no es recuperable. Opción, guardar con otro nombre para contemplar los cambios realizados.

FUNCIÓN IMPORTARDATOSDINÁMICOS La función IMPORTARDATOSDINAMICOS devuelve un valor de una tabla dinámica, especificando un campo de la misma. Ejercicio: Realiza la siguiente tabla con rango B3:D13:

Crear una tabla dinámica de la tabla creada.

Página 184 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

En la celda F18 se desea extraer de la tabla dinámica los datos del campo especificado en la celda I3. Sintaxis: =IMPORTARDATOSDINAMICOS(tabla dinámica; referencia) tabla dinámica: es la referencia a una tabla dinámica de donde se importan datos. referencia: es uno de los campos de la tabla dinámica del cual se extrae el dato.

FUNCIÓN JERARQUÍA La función JERARQUIA devuelve el número de orden de un valor dentro de una serie de valores. Ejercicio: Realiza la siguiente tabla:

Página 185 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 En el rango se analizan estadísticamente el nº de habitantes de distintas ciudades españolas. Añadir una columna a la tabla con el nombre Jerarquía. Sintaxis: = JERARQUIA(número; referencia; [orden]) número: es el valor del cual se desea averiguar su jerarquía (número de orden) en referencia. referencia: es un rango que contiene valores. orden: indica que se tome a referencia como ordenada en forma descendente si orden es cero o se omite. Si orden es distinto de cero indica que se tome a referencia como ordenada en forma ascendente.

Copiamos la función al resto de las celdas del rango:

Para el valor de la celda B3, indica su posición dentro de los valores del rango B3:B12. La función internamente considera los valores ordenados. Utilizando la función JERARQUIA queremos obtener un número de orden para cada ciudad según su nº de habitantes del rango A15:B24. Considera el orden indicado en la celda E15. Si en el rango existen valores repetidos tendrán el mismo número de jerarquía, y el número que le sigue no tendrá nº de orden (jerarquía), siguiente.

Página 186 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

FUNCIONES FINANCIERAS Para ver cómo funcionan algunas de las funciones financieras vamos a hacer el siguiente ejercicio:

Función PAGO Calcular el pago de un préstamo basado en pagos y tasa de interés constante.

Calcular igualmente los intereses pagados durante el tiempo indicado, basado en pagos periódicos y constantes. Debes emplear las siguientes funciones: PAGO PAGOIN PAGOPRIM opcional. Realiza la siguiente estructura:

Página 187 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Cómo armar en Excel un listado con los 5 datos mayores y 5 menores a partir de una lista desordenada. Las funciones Max y MIN nos permiten encontrar cuál son los valores Máximos y Mínimos dentro de un rango de datos.

Son muy útiles cuando necesitamos justamente encontrar los valores extremos: el mayor de todos los números entre los seleccionados y el menor de todos. Pero, ¿qué pasa cuando necesitamos encontrar por ejemplo los 5 primeros (ver imagen)? ¿Hay alguna forma de hacerlo fácilmente con Excel? Afortunadamente Excel nos brinda un par de funciones muy útiles (aunque poco conocidas): • •

K.ESIMO.MAYOR K.ESIMO.MENOR

Vamos a aprender a utilizar estas funciones a través de un ejemplo. Tenemos una lista alfabética de las provincias de Argentina y su población estimada. 1. Encontrar la mayor y menor población

Página 188 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Repasando las funciones MAX y MIN, la fórmula es: MAX(rango de datos) MIN(rango de datos) Esta es la forma más habitual. Pero en lugar de rangos de datos podemos tener valores separados con “;” Ejemplo: MAX(1;8;3;4) devuelve 8 Por lo tanto, encontrar cuáles son las mayores y menores poblaciones en nuestro ejemplo, se resuelve fácilmente: MAX(B3:B26) MIN(B3:B26) Nos falta ahora identificar a cuáles provincias pertenecen este valor. Esto es un poco más complicado. Lo vemos al final. 2. Encontrar las 5 Mayores y 5 Menores poblaciones Para este caso utilizaremos las funciones K.ESIMO.MAYOR y K.ESIMO.MENOR Ambas funciones tienen la misma estructura. Por ejemplo: K.ESIMO.MAYOR (Rango;k) Donde: • Rango es el rango de datos • k es la posición a partir de la más alta (en caso de K.ESIMO.MAYOR) que se desea buscar. 1 dará el valor más alto (igual a MAX). 2 dará el segundo valor más alto. 3, el tercer valor más alto, etc.

En el caso de K.ESIMO.MENOR, si k=1 la fórmula devolverá el valor más bajo (igual a MIN), 2 es el segundo más bajo y así sucesivamente. Es para esto que en la imagen vemos los números del 1 al 5 al lado de cada nombre de la provincia. Utilizaremos estos valores para indicar qué posición necesitamos. En la celda E8 vamos a identificar la población de la provincia con más habitantes, en la E9 la segunda de mayor cantidad, etc., según los números que tenemos en la columna C. En la celda E8 ingresaremos la fórmula =K.ESIMO.MAYOR($B$3:$B$26;C8) Donde:

Página 189 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 •

•

$B$3:$B$26 es el rango donde se encuentran las poblaciones. Dejamos estas celdas fijas(con signos $ o pulsando F4) porque copiaremos la fórmula a las celdas inferiores. C8 contiene el número de la posición a buscar.

Para los casos de las provincias con menor cantidad de habitantes, el proceso es equivalente: =K.ESIMO.MENOR ($B$3:$B$26;G8) 3. Encontrar el nombre de la provincia a la cual pertenece la población. Ya tenemos identificadas cuáles son las poblaciones que necesitábamos buscar, la mayor, la menor, las 5 mayores y las 5 menores. Ahora, debemos ir para atrás e identificar cuál es el nombre de la provincia a la cual corresponde. ¿Cómo lo haríamos manualmente? Revisando la columna de las poblaciones (columna B) y cuando encontramos la cifra que coincide, buscamos el valor que se encuentra en la misma fila pero en la columna A. Ejemplo: Para identificar a qué ciudad corresponde la población 3.273.049 buscaremos en la columna B el valor hasta encontrarlo. Como éste es el primer valor de la lista desde arriba, la ciudad a la cual corresponde es también el primer valor de la lista de ciudades (columna A). De igual forma lo haremos con Excel ayudándonos de dos fórmulas: • COINCIDIR: A través de esta función podemos identificar la posición relativa de un valor buscado dentro de un rango de datos. • INDICE: Esta función nos permite traer el valor de una matriz de datos al indicarle la posición de la fila y columna donde se encuentra el dato que necesitamos. Paso 1. Encontrar en qué fila de la lista de provincias-poblaciones se encuentra. Vamos a armar el caso de las 5 mayores. El caso de ciudades de menor población es igual. Debemos encontrar entonces en qué fila se encuentra la población que coincida con la del valor de la población buscado. La primera parte de la fórmula es: COINCIDIR(E8;$B$3:$B$26;0) Donde: • •

E8: referencia con la cifra de población que queremos buscar B$3:$B$26: Rango donde debemos buscar el valor anterior.

El resultado de esta fórmula será la posición en el rango que ocupa el número buscado en el rango B3:B26. Paso 2. Buscar en el rango nombres Volviendo al caso de la figura, cuando buscamos la posición (con COINCIDIR) correspondiente a 3.273.049, nos devolverá la posición 1 ya que éste es el primer valor de arriba abajo del rango B3:B26. Ahora, debemos encontrar la primera posición pero de la columna A. Para esto usamos la función INDICE:

Página 190 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Para el caso de la celda D8, la función queda: =INDICE($A$3:$A$26;COINCIDIR(E8;$B$3:$B$26;0)) Ya podemos copiar la fórmula al resto de las celdas de la columna D y tendremos el listado con las 5 mayores completo. De forma equivalente se completa el listado para las cinco ciudades de menor población.

EJERCICIOS EXCEL REPASO Ejercicio 1. Repaso ejercicios por cientos. Sumar al sueldo el 6%. También se puede calcular: =C2*D2*1,06

Restar al sueldo el 12%. También se puede calcular: =C2*D2*0,88

Página 191 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Sumar al sueldo el 8%, teniendo en cuenta que cada trabajador tiene un incentivo. También se puede calcular: = (C2*E2+D2)*1,08

Ejercicio 2. Funciones AHORA y HOY Ejecutar las siguientes funciones. Indicar qué ejecutan estas funciones. =AHORA() Se encuentra en la pestaña FÓRMULAS/BIBLIOTECA DE FUNCIONES/FECHA Y HORA y como resultado nos ofrece la fecha y hora actuales:

=HOY() Se encuentra en la pestaña FÓRMULAS/BIBLIOTECA DE FUNCIONES/FECHA Y HORA y como resultado nos ofrece la fecha actual:

Página 192 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Ejercicio 3. Funci贸n HOY y SUMA Completar con las f贸rmulas adecuadas. En la casilla correspondiente a la fecha, insertaremos la fecha de hoy. Para insertar la fecha utilizaremos la funci贸n HOY().

Ejercicio 4. Referencias relativas, absolutas y mixtas Realizar las siguientes operaciones utilizando las referencias relativas, absolutas y mixtas:

P谩gina 193 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Ejercicio 5. Incrementar. Hacer una tabla con el valor inicial incrementando en 5 unidades hasta llegar a 55. Hacer la tabla de dos formas: 10. Empleando series: Para ello escribiremos las tres primeras celdas y arrastraremos hacia abajo hasta el 55.

11. Con f贸rmula, empleando valores absolutos.

P谩gina 194 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Ejercicio 6. Tabla de multiplicar Realizar en una hoja de cรกlculo la tabla de multiplicar (Referencia mixta).

Ejercicio 7. Funciรณn PRODUCTO y SUMAPRODUCTO Calcula la suma total. Realizar la operaciรณn con las funciones PRODUCTO Y SUMAPRODUCTO.

Para el argumento de la funciรณn SUMAPRODUCTO escribiremos lo siguiente:

Pรกgina 195 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Ejercicio 8. Funciones MAX, MIN, SUMA y SUMAR.SI Vamos a hacer una competición durante una hora. Tendremos que averiguar el nombre del patito que más km nada y el nombre del patito que quedó el último. Si los tres primeros han sumado un total de 250 km, le daremos un premio a cada uno, pero han tenido que nadar un mínimo de 70 km cada uno. Averigua también los km totales y el promedio de km que han recorrido entre todos.

Ejercicio 9. Funciones O, Y, SI Aplica las funciones O, Y, y la función SI, en el siguiente supuesto Premios por partidas ganadas. • • • •

En la columna Condición 1 se reflejará VERDADERO si el jugador tiene más de dos partidas ganadas o más de 1.500 puntos. En la columna Condición 2 se reflejará VERDADERO si tiene más de dos partidas ganadas y más de 1.500 puntos. En la columna Regalo 1 se reflejará el siguiente resultado: Si la Condición 1 es VERDADERO, el regalo será un viaje; si es FALSO, una maleta. En la columna Regalo 2 se reflejará el siguiente resultado: Si la condición 2 es VERDADERO, el regalo será de 3.000 €; si es falso, una cena para dos.

Página 196 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

A continuaci贸n veremos los argumentos de cada una de las funciones que hemos utilizado: Condici贸n 1:

Condici贸n 2:

Regalo 1:

P谩gina 197 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Regalo 2:

Ejercicio 10. Funciones CARÁCTER, CÓDIGO, ENCONTRAR, EXTRAE, MAYUSC, MONEDA y REPETIR Aplicar las siguientes funciones: FUNCIÓN CARÁCTER: Es una función perteneciente a Texto y devuelve el carácter especificado por el número de código a partir del conjunto de caracteres establecido en su PC. FUNCIÓN CÓDIGO: Es una función perteneciente a Texto y devuelve el número de código del primer carácter del texto del conjunto de caracteres usados por su PC.

Argumentos de función:

Página 198 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

FUNCIÓN ENCONTRAR: se encuentra dentro de las funciones Texto y devuelve la posición inicial de una cadena de texto dentro de otra cadena de texto. BUSCAR diferencia entre mayúsculas y minúsculas.

Argumentos de función:

FUNCIÓN EXTRAE: se encuentra dentro de las funciones de Texto y devuelve los caracteres del centro de una cadena de texto, dada una posición y longitud iniciales.

Página 199 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Argumentos de función:

FUNCIÓN MAYÚSCULA: se encuentra dentro de las funciones de Texto y convierte una cadena de texto en letras mayúsculas.

Argumentos de función:

FUNCIÓN MONEDA: se encuentra dentro de las funciones de Texto y convierte un número en texto usando formato de moneda.

Página 200 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Argumentos de función:

FUNCIÓN REPETIR: se encuentra dentro de las funciones de Texto y repite el texto un número determinado de veces.

Argumentos de función:

Ejercicio 11. Existencias de almacén Realiza el siguiente ejercicio en Excel, introduciendo las fórmulas correspondientes, insertar comentario. El libro se llamará Existencias de almacén.

Página 201 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Ejercicio 12. Función BUSCARV Realiza en una hoja de Excel la siguiente tabla:

Ordena la lista de datos alfabéticamente por el nombre. Para ordenar seleccionaremos la columna a ordenar e iremos a Inicio/Modificar/Ordenar y filtrar. En otra hoja del mismo libro, realizar la siguiente tabla y aplicar la función BUSCARV de forma que cuando se introduce el Nombre debe aparecer el Apellido y el Teléfono correspondiente.

Argumentos de función Apellido:

Página 202 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Argumentos de función Teléfono:

Asignar un color a las etiquetas de cada hoja. Para ello: • • • •

Seleccionar el menú Formato de la pestaña Inicio Elegir la opción Color de etiqueta Aparecerá otro submenú Seleccionar el color deseado

Página 203 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Ejercicio 13. Funciones SI, MAX, MIN, PROMEDIO, MODA y CONTAR.SI Realiza el siguiente ejercicio aplicando las funciones correspondientes: Realizar los cálculos correspondientes siguiendo las siguientes instrucciones: Columna C: Que aparezca “APTO” o “NO APTO” en función de la nota obtenida en el examen 1:

Columna E: Que aparezca “APTO” o “NO APTO” en función de la nota obtenida en el examen 2:

Columna F: La media de los 2 primeros exámenes(función PROMEDIO):

Columna H: NOTA FINAL = ((60*Examen final)+ (40*Media))/100 Columna I: Apto/No apto final en función de la nota de la columna H:

Página 204 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Las columnas en gris son en las que hemos calculado los datos, los demás nos venían dados. Insertaremos la función en la primera celda de cada columna o fila y arrastraremos para calcular las demás.

Ejercicio 14. Cálculos resultado de evaluación Realiza el siguiente ejercicio aplicando las funciones necesarias para su resolución. Para la EVALUACIÓN, es la nota en texto de acuerdo con el siguiente criterio: NOTA entre 0 y 4,99, INSUFICIENTE; entre 5 y 7, APROBADO; entre 7,1 y 8,5, NOTABLE; mayor que 8,5, EXCELENTE. Esto es equivalente a escribir los rangos: <5; <=7; <=8,5. Realizar un gráfico de columnas y guardar el libro con el nombre Alumnos.

Página 205 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Argumentos de funci贸n Nota:

Argumentos de funci贸n Evaluaci贸n:

P谩gina 206 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Para insertar el gráfico seleccionaremos los datos de las columnas B, C y D que se corresponden con las notas del cada trimestre e iremos al menú Insertar/Gráficos/Columnas:

Ejercicio 15. Función DÍA.LAB Ejercicio fechas. Un trabajador tiene derecho a disfrutar de 20 días hábiles de vacaciones. Si comienza el 1 de agosto de 2011, que día debe incorporarse al trabajo. Tener en cuenta que el 15 de agosto es fiesta. Función DÍA.LAB

Página 207 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Argumentos de función:

Ejercicio 16. CUADRO COMBINADO y función INDICE Realizar el siguiente ejercicio utilizando un cuadro combinado, de forma que al seleccionar un país de la Unión Europea, nos informe de las entregas intracomunitarias correspondientes a ese país. Realizar CUADRO COMBINADO. Función INDICE.

Para crear el cuadro combinado iremos a la ficha Programador/Insertar/Cuadro combinado y lo dibujaremos en la celda donde queramos que se cree.

Página 208 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Una vez hayamos creado el cuadro haremos un clic derecho sobre él y seleccionaremos la opción Formato de control y le insertaremos los datos necesarios. Para obtener más información sobre los cuadros combinados ir al apartado Formularios en Excel de este tutorial.

Ejercicio 17. Función INDIRECTO. Devuelve una referencia especificada por un valor de texto. Introduce el número 1000 en la celda D2. Dato que corresponde a la Fila 2, Columna 4. Calcula la dirección (Función DIRECCIÓN) de este dato, y el Indirecto del mismo.

Ejercicio 18. FÓRMULAS CON RANGOS. Realiza el siguiente ejercicio que muestra la imagen. Realizar lo que indican los cuadros de texto.

Página 209 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Una matriz se reconoce porque va entre llaves y si se quiere borrar un elemento de la matriz no se permite.

Ejercicio 19. Función ALEATORIO.ENTRE FÓRMULAS CON RANGOS. Realiza el siguiente ejercicio que muestra la imagen. Utiliza la función ALEATORIO.ENTRE Facturación: =ALEATORIO.ENTRE(100;1000) Atípicos: =ALEATORIO.ENTRE(100;300) Personal: =ALEATORIO.ENTRE(100;300) Alquileres: =ALEATORIO.ENTRE(100;200) Amortización: =ALEATORIO.ENTRE(100;100) Aplica la función SUMA. Resultado=INGRESOS – GASTOS. Realizarlo también de forma matricial.

Página 210 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Para crear la fórmula matricial en RESULTADO seleccionaremos el rango de celdas C18:H18 e iremos a la barra de fórmulas:

En la barra de fórmulas escribiremos = (rango de celdas C7:H7)-(rango de celdas C16:H16) y no pulsaremos Intro sino que pulsaremos CTRL+ MAYUS+INTRO.

Ejercicio 20. Aplicar nombres a rangos Dada una tabla de valores (fechas y empresas) deseamos obtener un método de extracción de valores de dicha tabla, conocida una fecha y una empresa. Aplicar nombres: • • •

Al rango B15:B24→Fecha Al rango C14:F14→emp Al rango C15:F24→datos

Extraer los valores que indica la segunda tabla.

Página 211 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Ejercicio 21. Funciones JERARQUÍA, DESREF Y COINCIDIR Realizar la siguiente tabla en un libro de Excel.

Nombra el rango de datos de clientes con el nombre Datos. Aplica la función JERARQUÍA que establece el orden creciente o decreciente de un conjunto de datos. Si el último argumento de la función se deja vacío o se pone cero la ordenación es decreciente. Si en el último argumento se pone otro número distinto de cero la ordenación es creciente.

Añade una nueva columna de nombre Ordenado que refleje la ordenación de las ciudades aplicando la función DESREF y COINCIDIR.

Página 212 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

Ejercicio 22. Función BUSCARV Un profesor-tutor tiene una tabla con las notas de un alumno en números y quiere completarla poniendo las notas en palabras. Realiza la siguiente tabla:

En otra hoja tiene una tabla de calificaciones. Llamar al rango de la tabla CALIFICACIÓN:

Completa la tabla:

Página 213 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Ejercicio 23. Funciones Y, MAX, MIN Se sabe que el promedio de las temperaturas del año en curso de la provincia de Málaga es de 27º y se tiene una tabla con los promedios de las temperaturas de los meses del año anterior, se quiere saber si es verdadero que los 27º entran en el rango de los promedios de los meses del año anterior. Introducir los siguientes datos en un libro:

RESULTADO:

Ejercicio 24. Función SI, Y, O Una empresa quiere promocionar a una nueva sección a los empleados que cumplan con las siguientes condiciones: 1. Pertenecer al turno de mañana 2. Ser de la categoría 1 o que su sueldo sea menor o igual a 7.000 €. Para esto cuenta con la siguiente tabla que debe ser completada: donde los turnos son M, T, N, correspondientes a mañana, tarde y noche respectivamente y las secciones van de 1 a 4. Introduce los datos siguientes:

Página 214 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007

RESULTADO:

EJERCICIO DE APLICACIÓN. FLORISTERIA LOS NENÚFARES Elaboraremos un libro de cálculo para gestionar una floristería, con el fin de poder dar un presupuesto rápido de distintos rangos de ramos de flores. En la hoja 1 escribiremos la siguiente tabla llamando al rango de los valores FLORES.

En la hoja 2 realizaremos una tabla similar a la que aparece en la imagen. Utilizaremos las funciones SI, SI con Y y BUSCARV donde consideremos oportuno. El rango que deberemos seleccionar de la hoja 1 al utilizar BUSCARV será el rango FLORES. En la fila 8 hay cuadros combinados para elegir los distintos tipos de flores. Al elegir el tipo de flor debe aparecer el precio y al poner el nº de ramos el resto. La celda TOTAL PRESUPUESTO IVA INCLUIDO es la suma de los totales de los distintos ramos.

Página 215 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Deberemos poner un botón al final del presupuesto de forma que al pulsarlo todas las celdas queden en blanco y se pueda elaborar un presupuesto distinto. Como ya sabemos al crear los cuadros combinados de las distintas flores deberemos vincularlos con una celda a través de Formato de control, esa celda nos servirá para la creación de las funciones y cuando grabemos la macro del botón para el nuevo presupuesto pues en todas deberá aparecer el 1 que son valores en blanco para realizar un nuevo presupuesto.

Si pulsamos en NUEVO PRESUPUESTO la tabla aparecerá de la siguiente manera:

Como podemos observar las celdas que se han vinculado a los campos combinados aparecen con el número 1 que es el valor de vacío, por ello la tabla queda preparada para un nuevo presupuesto.

EXAMEN EXCEL RESUELTO I Ejercicio 1. Calcular las ventas del mes de enero de un concesionario de coches con la estructura siguiente y lo guardas en la carpeta de examen con tu nombre con el nombre Concesionario, con las siguientes consideraciones: EN LA COLUMNA MATRÍCULA UTILIZAR LA FUNCIÓN SI, TENIENDO EN CUENTA LO SIGUIENTE:

Página 216 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 SI EL P.V.P. DE CADA MODELO ES INFERIOR A 12.000, EL IMPORTE DE LA MATRICULA SERÁ EL QUE FIGURA EN MATRICULA 1, Y EN CASO CONTRARIO SERÁ EL QUE FIGURA EN MATRICULA 2. (TENER EN CUENTA QUE LUEGO HAY QUE MULTIPLICARLO POR EL NÚMERO DE UNIDADES).

RESULTADO:

Ejercicio 2. Calcular el saldo de caja, para cada fecha en el siguiente supuesto:

Página 217 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Guardar el libro en la carpeta de tu examen con el nombre Libro de Caja.

RESULTADO:

Ejercicio 3. Crear un libro en Excel para llevar la contabilidad doméstica: Una familia tiene clara su situación económica. Desde que se han decidido a construir una casa, están muy interesados en saber lo que tienen que ahorrar al mes sin que su economía quede sobrecargada innecesariamente. Para afrontar el problema, se ha decidido a usar el ordenador. Ellos desean un cuadro con los ingresos y pagos mensuales y el total anual, de forma que vean el saldo mensual y anual. Datos sobre la familia: • • •

• • • • • • •

La familia está compuesta por el padre, la madre y una hija. La madre tiene trabajo fijo y recibe un sueldo de 1.600 € al mes. El padre trabaja media jornada y recibe un sueldo de 520 € al mes. Además se dedica a escribir en periódicos y revistas de forma temporal, con lo cual, desde el 1 de enero al 1 de junio recibe un sueldo de 450 € al mes. El resto del año recibe 3.120 €, a dividir entre los 6 meses restantes. Entre lo que tiene que pagar la familia, hay un alquiler mensual de 360 €. El 1 de abril y el 1 de septiembre vence el crédito bancario, con lo que paga 3.000 € cada vez. La electricidad se paga el 15 de enero, y después de cada trimestre, por valor de 300 € el recibo. Los seguros vencen el 15 de marzo y tienen que pagar 500 €. El coche de la familia consume 120 € al mes. Los gastos de colegio de la niña son de 350 € anuales. Los gastos de comida son de 700 € al mes.

Página 218 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 •

El padre y la madre utilizan para uso personal 1.820 € anuales y la hija 700 € anuales. Para estar más seguros, también presupuestan 50 € mensuales por gastos diversos.

Haz también un grafico que muestre el dinero del que puede disponer la familia en los distintos meses del año. En la hoja 2 realiza un esquema de la hoja inicial y nombra a la misma con el nombre de Esquema. Guarda el libro en la carpeta examen de tu nombre con el nombre Contabilidad familiar. RESULTADO:

ESQUEMA:

Página 219 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Ejercicio 4. Nos ha dado un precio de 900 € y nos gustaría saber el PRECIO DEL ARTÍCULO, sabiendo que el IVA es el 18%. Di el método que realizas para averiguar este precio. Guarda el libro con el nombre Precio IVA incluido, en la carpeta Examen de tu nombre.

EXAMEN EXCEL RESUELTO II Ejercicio 1. Diseña una hoja de cálculo que nos permita analizar una oferta que nos han hecho en nuestra inmobiliaria sobre la compra de un apartamento de nueva construcción en la playa donde siempre hemos querido tener nuestro lugar de descanso. Las condiciones que nos ofrecen son: El valor del apartamento es de 100.500 € sin IVA Aplicar IVA. A la firma del contrato hay que pagar el 10 % del valor total del apartamento. Como lo están construyendo y lo entregarán dentro de un año, hay que pagar durante los próximos 12 meses otro 10 % del valor total del apartamento en 12 mensualidades iguales desde septiembre de 2009 hasta agosto de 2010. A la entrega de llaves (aproximadamente en septiembre de 2010) hay que pagar el resto, al contado o suscribiendo una hipoteca con una entidad bancaria que en la fecha correspondiente fijará las condiciones y que proporcionará la propia Constructora. El planteamiento podría ser el siguiente:

Página 220 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Inserta el comentario en el título de la tabla: Inmobiliaria Habitat, S.L. Guarda el libro con el nombra Compra de Apartamento en la carpeta examen de tu nombre. RESULTADO:

Ejercicio 2. Calcula la cuota mensual del supuesto y los siguientes escenarios:

CAPITAL

15000

interés

Años

Cuota mensual 283 Guarda el libro en la carpeta examen de tu nombre, con el nombre de Préstamo. RESULTADO:

Página 221 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Ejercicio 3. Realiza la siguiente tabla en la hoja 1 del libro de Excel con el nombre Libros, en la carpeta examen de tu nombre. A la hoja la llamas también libros, con la siguiente estructura:

En la hoja 2, con el nombre Ventas, realiza la siguiente tabla de forma que cuando introduzcas el código aparezcan los datos de la tabla inicial, teniendo en cuenta que se han realizado las siguientes ventas. La columna Subtotal, utiliza la función Si, para calcular la misma, dependiendo si hay o no unidades de venta.

Crea hipervínculos para ir de una hoja a otra. RESULTADO:

Página 222 VICENTA MEGIA MOLERO

Microsoft Office Excel 2007 Ejercicio 4. Realiza la siguiente lista en la hoja 1 con el nombre de lista.

En la hoja 2 quiero realizar una lista desplegable de forma que cuando selecciones el país, obtenga las ventas del país seleccionado. A la hoja 2 la llamas Lista Ventas. Al libro lo guardas con el nombre Lista desplegable en la carpeta examen de tu nombre. RESULTADO:

Página 223 VICENTA MEGIA MOLERO