exc07 excel 2007 avanzado apuntes castellano (1)
TRANSCRIPT
EXCEL 2007
Documentación realizada por: José Luis Egido
Excel 2007
Página 2
ÍNDICE DE CONTENIDOS EDICION ..................................................................................................................... 6
Buscar y reemplazar datos .................................................................................................................. 6 Selección especial .............................................................................................................................. 7 Pegado ............................................................................................................................................. 9 Pegado especial ................................................................................................................................. 9 Panel Portapapeles ........................................................................................................................... 11
FORMULAS ................................................................................................................ 12 Operadores: ..................................................................................................................................... 12
Operadores Aritméticos .......................................................................................................................... 12
Operadores Relacionales o de Comparación .......................................................................................... 12
Operadores de Texto ............................................................................................................................... 12
Operadores de Referencia ...................................................................................................................... 12
Precedencia de operadores ..................................................................................................................... 12
Referencias: concepto y tipos ............................................................................................................ 13
Referencias relativas ............................................................................................................................... 13
Referencias absolutas ............................................................................................................................. 13
Referencias mixtas .................................................................................................................................. 14
Modo de especificar o cambiar las referencias a una celda. .................................................................. 14 Modo de extender una formula .......................................................................................................... 14 Ejemplos de tipos de referencias ........................................................................................................ 15
Referencias relativas ............................................................................................................................... 15
Referencias absolutas ............................................................................................................................. 15
Referencias mixtas .................................................................................................................................. 16
Nombres .......................................................................................................................................... 17
Definir nombres ...................................................................................................................................... 17
Dar nombres a celdas o rangos ............................................................................................................... 17
Cuadro de nombres: ................................................................................................................................ 18
Utilizar nombres: ..................................................................................................................................... 18
Gestionar nombres: ................................................................................................................................. 18
Dar nombres a constantes y formulas internas ...................................................................................... 19
FORMATO ................................................................................................................. 19 Formatos predefinidos ....................................................................................................................... 20 Formatos personalizados ................................................................................................................... 20 Formato condicional .......................................................................................................................... 23
Definir un formato condicional ............................................................................................................... 23
Administrar reglas de formato condicional ............................................................................................. 24
Excel 2007
Página 3
Copiar y pegar formato ..................................................................................................................... 25
FUNCIONES ............................................................................................................... 26 Estructura de una función .................................................................................................................. 26 Tipos de argumentos ........................................................................................................................ 26 Categorías de funciones .................................................................................................................... 26 Introducción de funciones ................................................................................................................. 27 Grupos de funciones importantes ....................................................................................................... 29
FUNCIONES ESTADISTICAS ...................................................................................................................... 29
FUNCIONES LÓGICAS ............................................................................................................................... 35
FUNCIONES DE BUSQUEDA Y REFERENCIA ............................................................................................. 38
FUNCIONES MATEMÁTICAS Y TRIGONOMÉTRICAS ................................................................................. 43
FUNCIONES DE TEXTO ............................................................................................................................. 46
FUNCIONES DE FECHA Y HORA ............................................................................................................... 49
FUNCIONES DE BASES DE DATOS ............................................................................................................ 51
BASES DE DATOS ...................................................................................................... 52 Creación de una base de datos .......................................................................................................... 52 Gestión de una base de datos ............................................................................................................ 52 Validación de datos ........................................................................................................................... 53
Configuración .......................................................................................................................................... 54
Mensaje de entrada ................................................................................................................................ 54
Mensaje de error ..................................................................................................................................... 55
Validación de Lista ................................................................................................................................... 56
Validación Personalizada ......................................................................................................................... 56
Ordenación ....................................................................................................................................... 58 Filtrado o Extracción o de información ................................................................................................ 58
Filtro ........................................................................................................................................................ 59
Filtro Avanzado ....................................................................................................................................... 59
Subtotales ........................................................................................................................................ 62
Creación de subtotales ............................................................................................................................ 62
Utilizar distintas funciones sobre uno o mas campos de la misma agrupación ...................................... 63
Creación de varios niveles de subtotales ................................................................................................ 63
FUNCIONES DE BASES DE DATOS ..................................................................................................... 65
Tablas ....................................................................................................................... 67 Creación de tablas ............................................................................................................................ 67 Utilización de tablas .......................................................................................................................... 67
Uso de referencias a la tabla ................................................................................................................... 68
VINCULACIÓN DE DATOS. .......................................................................................... 69 Referencias externas ......................................................................................................................... 69
Referencias externas entre libros ............................................................................................................ 69
Excel 2007
Página 4
Resúmenes de datos ......................................................................................................................... 71
Referencias 3D ........................................................................................................................................ 71
Consolidación de datos ........................................................................................................................... 72
GRÁFICOS ................................................................................................................. 75 Conceptos de gráficos ....................................................................................................................... 75 Terminología de gráficos de Excel ...................................................................................................... 76 Tipos de gráficos .............................................................................................................................. 77
Subtipos de gráficos .......................................................................................................................... 80 Interrelación Datos – Gráfico ............................................................................................................. 80 Añadir o eliminar datos ..................................................................................................................... 81
Añadir una series o categorías de datos contigua a las ya existentes. . .................................................. 81
Añadir series o categorías de datos al gráfico no contiguas a las ya existentes. .................................... 81
Añadir series o categorías de datos al gráfico copiando y pegando con el ratón. .................................. 81
Gráficos dinámicos ............................................................................................................................ 81 Gráficos Combinados ......................................................................................................................... 81
Eje secundario .................................................................................................................................. 82 Opciones Especiales .......................................................................................................................... 83
Eje de valores .......................................................................................................................................... 83
Eje de categorías ..................................................................................................................................... 84
Superposición de series y ancho del intervalo ........................................................................................ 84
Barras de error ........................................................................................................................................ 85
Líneas de tendencia ................................................................................................................................. 86
Representación de valores ocultos, nulos, o vacios ................................................................................ 88
TABLAS DINAMICAS ................................................................................................... 90 Concepto y Componentes .................................................................................................................. 90 Creación de una tabla dinámica ......................................................................................................... 90 Variar la perspectiva de los datos ....................................................................................................... 93 Filtrar Información ............................................................................................................................ 93
Filtros de Campo ..................................................................................................................................... 93
Filtros de etiqueta ................................................................................................................................... 93
Filtros de fecha ........................................................................................................................................ 93
Filtro personalizado ................................................................................................................................. 93
Filtros por valor: ...................................................................................................................................... 93
Ordenar Información ......................................................................................................................... 94
Orden Manual ......................................................................................................................................... 94
Orden automático ................................................................................................................................... 94
Ordenar los campos en la "Lista de campos de la tabla dinámica". ........................................................ 95
Esquemas: agrupar mostrar y ocultar información ............................................................................... 96
Mostrar detalle ........................................................................................................................................ 96
Excel 2007
Página 5
Múltiples campos o funciones en el área "Σ Valores" ............................................................................ 97
Cálculos personalizados en tablas dinámicas ....................................................................................... 97
Mostrar valores como ............................................................................................................................. 97
Campos calculados .................................................................................................................................. 98
Elementos calculados .............................................................................................................................. 99
Orden de resolución .............................................................................................................................. 100
Listado de todos los campos y elementos calculados ........................................................................... 101
Gráficos dinámicos .......................................................................................................................... 101
MACROS ................................................................................................................. 102 Concepto ........................................................................................................................................ 102 Creación de macros ........................................................................................................................ 102
Consideraciones antes de grabar una macro: ....................................................................................... 102
Ejemplo de creación de una macro: ...................................................................................................... 104
Opciones ................................................................................................................................................ 104
Ejecución de macros. ...................................................................................................................... 105
Desde el cuadro de dialogo Macro. ....................................................................................................... 105
Mediante una combinación de teclas ................................................................................................... 105
Mediante un botón en la barra de herramientas de acceso rápido ...................................................... 105
Mediante un botón o control de formulario o un objeto gráfico en la propia hoja de cálculo. ............ 106
Entorno de programación Visual Basic para Aplicaciones (VBA) ........................................................... 107
Ventana Explorador de Proyectos ......................................................................................................... 109
Ventana de Propiedades ....................................................................................................................... 110
Ventana de Código ................................................................................................................................ 110
Ejercicios: ...................................................................................................................................... 113
Excel 2007
Página 6
EDICION
Buscar y reemplazar datos Se pueden localizar datos en una hoja de Excel o en todas las hojas de un cierto libro mediante el comando Buscar o Reemplazar situados en la ficha Inicio, grupo Modificar, opción Buscar y seleccionar. Al hacer clic en cualquiera de ellos aparece el cuadro de dialogo Buscar y Reemplazar siguiente.
Dicho cuadro nos permite también reemplazar lo buscado por nuevos valores, utilizando para ello la ficha Reemplazar.
Se puede buscar por contenido, es decir escribiendo el texto, numero, o fecha a buscar en el cuadro de texto Buscar, se puede buscar según el formato de los datos o ambas posibilidades combinadas.
Al hacer la búsqueda se puede avanzar de una ocurrencia a otra con el botón “Buscar siguiente” o bien hacer que Excel confeccione una lista con todas las ocurrencias, para lo cual clicaremos el botón “Buscar todos”.
En las fichas Buscar y Reemplazar podemos buscar según el tipo de datos (valores, formulas, comentarios), teniendo en cuenta el ámbito de búsqueda y reemplazo (a nivel hoja o todos el libro)y diversas opciones que se resumen en la tabla siguiente:
Tabla: Controles y funcionalidad asociada el cuadro de dialogo Buscar y Reemplazar
Control Función
Campo Buscar Contiene el valor que se quiere buscar o reemplazar.
Botón Buscar todos Selecciona cada celda que contiene el valor del campo Buscar.
Botón Buscar siguiente Selecciona la siguiente celda que contiene el valor del campo Buscar.
Campo Reemplazar con Contiene el valor para sobrescribir el valor del campo Buscar.
Botón Reemplazar todos Reemplaza cada ocurrencia del valor del campo Buscar con el valor del campo Reemplazar.
Botón Reemplazar Reemplaza la siguiente ocurrencia del valor del campo Buscar y marca o selecciona la siguiente celda que contiene dicho valor.
Botón Opciones Expande el cuadro de diálogo Buscar y reemplazar para mostrar diversas posibilidades adicionales en la búsqueda y reemplazo.
Botón Formato Despliega el cuadro de diálogo Buscar Formato, que se puede utilizar para especificar el formato de los valores a buscar o a reemplazar
Excel 2007
Página 7
Cuadro de lista Dentro de Permite seleccionar el ámbito de búsqueda. Se puede buscar en la hoja de cálculo activa o en el libro entero.
Cuadro de lista Buscar Permite seleccionar el orden o dirección de búsqueda: Se puede buscar por columnas o filas.
Cuadro de lista Buscar dentro de.
Sirve para definir el tipo de dato o búsqueda a realizar. Permite seleccionar si busca dentro del contenido de formulas, valores o comentarios de las celdas seleccionadas.
Casilla de verificación Coincidir mayúsculas y minúsculas
Si se activa tendrá en cuenta la diferencia entre mayúsculas y minúsculas, así por ejemplo Paris, PARIS y paris, serán ocurrencias diferentes de una misma palabra.
Casilla de verificación Coincidir con el contenido de toda la celda
Busca todas aquellas celdas cuyo contenido sea el mismo valor que en el cuadro Buscar.
Botón Cerrar Cierra el cuadro de diálogo Buscar y Reemplazar.
Selección especial En muy diversas circunstancias es necesario seleccionar todas las celdas dentro de un rango seleccionado o que tengan una o varias características comunes. Para ello Excel nos facilita el comando ·”Ir a Especial”.
Para activarlo debemos seleccionar la ficha Inicio, grupo Modificar, opción Buscar y seleccionar, elemento “Ir a Especial..” o bien pulsando la tecla F5 y el dialogo “Ir A” clicando sobre el botón “Especial…”. En ambos casos aparece el cuadro de dialogo “Ir a Especial”:
Previo al uso de cualquiera de las opciones del cuadro de dialogo anterior, se debe definir el alcance o ám-bito de la selección a efectuar. Existen dos posibilidades:
• Ámbito a nivel de hoja entera: Es el existente por defecto cuando solo tenemos seleccionada una celda cualquiera.
• Ámbito a nivel de un cierto rango de la hoja: Solo buscara en las celdas del rango seleccionado. El rango puede ser continuo o discontinuo.
Nota: El ámbito de actuación de este comando es solo a nivel de rango o de hoja, no de libro.
Usos comunes de esta herramienta son:
Excel 2007
Página 8
• Dar formato: Por ejemplo, se puede desear seleccionar todas aquellas celdas que contengan formu-las para marcarlas con un determinado color de fondo o un cierto tipo de letra que las diferencie de las demás o bien marcar todas las celdas que son rótulos, es decir constantes de tipo Texto
• Marcar celdas que contengan errores. • Selección de celdas con contenidos específicos: por ejemplo las que estén en blanco dentro de un
rango dado, las que contengan comentarios, etc. • Seleccionar celdas visibles: para darles formato, borrarlas, copiarlas, etc.
Opción Selección
Comentarios Celdas que contienen comentarios.
Constantes Celdas que contienen constantes, es decir valores que no varían, ya sean texto, números, fechas, valores lógicos, etc. Se puede seleccio-nar según tipos de contenido (números, texto etc.), marcando la co-rrespondiente casilla de verificación que haya debajo.
Fórmulas Celdas que contienen fórmulas. Se puede seleccionar según el tipo de resultado que produzcan (números, texto etc.), marcando la co-rrespondiente casilla de verificación que haya debajo.
Celdas en blanco Celdas en blanco.
Región actual Selecciona el rango de celdas adyacentes a la que estemos situado extendiéndose en todas las direcciones hasta hallar la hasta la prime-ra fila o columna vacía en cada una de dichas direcciones, que es lo que delimita la “región actual”.
Matriz actual Selecciona todas las celdas que compongan el rango de la matriz actual. Esta opción solo tiene sentido si estamos situados en alguna de las celdas de un rango-matriz, caso contrario nos aparecerá el mensaje informativo. “No se encontraron celdas”.
Objetos Selecciona los objetos gráficos (gráficos, botones, cuadros de texto, fotografías, etc.)
Diferencias entre filas Todas las celdas que se diferencian de la celda activa y que se hallan en la misma fila. Por defecto la celda activa es la primera celda de la fila o parte de fila seleccionada, pero se puede cambiar presionando ENTRAR o TAB. Si se ha seleccionado más de una fila, la compara-ción se hace para cada fila individual de dicha selección y la celda que se utiliza en la comparación para cada fila adicional se encuentra en la misma columna que la celda activa.
Diferencias entre columnas Análogo a la “Diferencia entre filas”. Selecciona todas las celdas que se diferencian de la celda activa en una columna seleccionada. Por defecto la celda activa es la primera celda de la columna o parte de columna seleccionada. Para cambiar la ubicación de la celda activa, basta presionar la tecla ENTRAR o TAB.
Celdas precedentes Celdas a las que hace referencia la fórmula de la celda activa. Se pueden buscar sólo las directamente relacionadas, es decir aquellas que se mencionan en la formula o todas, lo que incluye tanto las refe-renciadas directamente como indirectamente a cualquier nivel.
Celdas dependientes Celdas con fórmulas que hacen referencia a la celda activa. Es el reciproco de “Celdas precedentes”, y al igual que en dicha opción, tenemos dos posibilidades: seleccionar solo las directamente relacio-nadas (aquellas que tienen una referencia directa a la celda activa) o todas las que tienen relación con la celda activa, ya sea directa o indirectamente.
Excel 2007
Página 9
Última celda Celda en la intersección de la ultima fila y columna que contiene o datos o formato.
Sólo celdas visibles Selecciona sólo las celdas que están visibles en un rango en el que puede haber celdas, filas o columnas ocultas.
Celdas con formatos condicionales Celdas que tienen aplicados formatos condicionales. En esta opción existen dos posibilidades: o buscar todas las celdas con formato con-dicional existentes o buscar todas aquellas que tienen un formato condicional igual al de la celda activa seleccionada.
Celdas con validación de datos Celdas a las que se han aplicado las reglas de validación de datos. Existen, dos posibilidades de selección, que son análogas a las de la opción “Celdas con formatos condicionales”
Pegado Cuando se mueve o se copia una información, al final del proceso se debe de Pegar mediante alguna de las diversas posibilidades existentes., por ejemplo usando el menú contextual de la celda de destino o bien la ficha Inicio > Pegar. Una vez pegada la información, aparece al lado del área de pegado el icono de “Opcio-nes de pegado”, que al desplegarlo haciendo clic en la flecha que hay en su lateral derecho , presentara una serie de opciones de pegado, que son las siguientes:
• Mantener formato de origen: Mantiene el formato de las celdas que se han copiado
• Mantener tema de destino: Mantiene el tema del libro u hoja de destino, es decir el tipo de letra, esquema de color y diseño.
• Coincidir con formato de destino: Lo copiado adopta el formato de las celdas de destino.
• Formatos de números y valores: Mantiene el formato numérico de los datos copiados.
• Mantener ancho de columnas de origen: Le aplica a las celdas destino de la copia la misma anchura de columna que la que tenia la celda donde estaban los datos originales.
• Sólo formato: Copia y aplica únicamente el formato. • Vincular celdas: Establece un vinculo entre las celdas origen copiadas y las celdas destino de la co-
pia. Si se cambia el contenido de las celdas origen, automáticamente cambiara también el de la co-pia.
Estas opciones y algunas otras mas especializadas existen en las opciones de “Pegado especial” que se vera en el próximo apartado.
Pegado especial Usualmente, cuando se copia y pega algo dentro de un mismo libro de Excel, o entre diferentes libros, se hace con la opción por defecto, que pega todo, dado que es lo generalmente se quiere, no obstante existen una serie de opciones o posibilidades de pegado mas especificas y muy interesantes.
Una vez seleccionada la celda o celdas a copiar y habiéndonos desplazado a la celda o celdas de pegado, para tener acceso a dicha opciones al cuadro de dialogo “Pegado especial” tenemos dos posibilidades:
• Usar el menú contextual de la celda y hacer clic en la opción “Pegado especial…” • Seleccionar ficha Inicio, grupo Portapapeles, desplegable Pegar, opción “Pegado especial…”
en ambos casos nos aparecerá el cuadro de dialogo “Pegado especial” según figura adjunta:
Excel 2007
Página 10
Las opciones disponibles de “Pegado especial” se agrupan en tres bloques diferenciados: Opciones de pe-gado en “Pegar”, opciones de pegado con operación en “Operación” y varios
Las opciones de ”Pegar” son las siguientes:
• Todo: Realiza la misma función que el pegado normal, es decir pega todo en la nueva ubicación, tanto datos como formato de la celda o celdas copiadas.
• Fórmulas: Pega únicamente las formulas existentes en la celda copiada. • Valores: Pega solo el valor de la celda o en el caso de una formula, solo el resultado, no la formula. • Formatos: Pega solo el formato de la celda seleccionada en la celda o celdas de destino, es el equi-
valente a usar el icono “Copiar formato”·existente en ficha Inicio, grupo Portapapeles. • Comentarios: Pega los comentarios asociados a las celdas seleccionadas. • Validación: Pega únicamente las reglas de validación de datos. La validación es una característica
que permite poner condiciones o restricciones respecto al tipo y rango de datos que se pueden in-troducir en una celda o rango determinado.
• Todo utilizando el tema de origen: Pega el contenido de la celda copiada usando como formato el tema que tenga asociado el documento.
• Todo excepto bordes: Pega el contenido de la celda y el formato completo de dicha celda, excepto los bordes.
• Ancho de las columnas: Cambia el ancho de las columnas de pegado de destino por el ancho de las celdas de origen seleccionadas.
• Formatos de números y fórmulas: Es una variante o ampliación de la opción “Fórmulas”, aquí ade-más de pegar la formula pega también el formato numérico de las celdas seleccionadas.
• Formatos de números y valores: Variante o aplicación de la opción “Valores”. Además de pegar el valor pega todas las opciones de formato numérico que tuviera la celda de origen.
El grupo de “Operación” sirve para elegir la operación a efectuar entre los datos que hemos copiado y los datos que puedan existir en destino, de forma que si en las celdas destino hay valores, de que lo que se pega es el resultado de dicha operación. Las operaciones disponibles son: Ninguna, Sumar, Restar, Multi-plicar y Dividir
Nota: Las celdas destino vacías se consideran como ceros.
Existen además otras opciones disponibles que son:
• Saltar blancos: Si un rango copiado tiene celdas en blanco, las celdas de destino de pegado corres-pondientes no se sobrescribirán.
Excel 2007
Página 11
• Transponer: Intercambia los datos copiados cambiando filas por columnas y viceversa. • Pegar Vínculos: Sirve para vincular datos. No pega el contenido de la celda o celdas copiadas, sino
la referencia absoluta a las celdas copiadas. Así, cuando la celda original sea modificada el cambio se reflejara también en la celda vinculada. Es una forma de creación de vínculos entre hojas del mismo o distinto libro. Existe otra forma de realizar la vinculación de datos.
Panel Portapapeles Existe una herramienta denominada “Portapapeles” o “Panel Portapapeles” que facilita el trabajar con mayor comodidad a la hora de copiar y pegar, cuando hemos de hacer varios copiados y pegados sucesivos, dado que nos permite acumular hasta 24 ítems de cualquier tipo de información y después pegar selectivamente los que queramos y en el orden que queramos.
Para activar dicha herramienta hemos de ir a la ficha Inicio, grupo Portapapeles y darle al cuadro lanzador el portapapeles que esta situado en la esquina inferior derecha de dicho grupo. Al hacerlo aparecerá el panel Portapapeles a la izquierda de la pantalla, con todos los elementos copiados hasta el momento.
Para copiar información es suficiente hacerlo de las formas habituales. Dicha información se ira acumulando en le Portapapeles de forma secuencia, quedan la mas reciente siempre arriba de todo.
Para pegar la información copiada simplemente nos posicionaremos en cada caso en la celda deseada y haciendo clic en el trozo de información deseada este se pegara en la celda activa.
Excel 2007
Página 12
FORMULAS
Para crear formulas en una celda de Excel se debe escribir primero el signo igual (=) y después la expresión deseada. Una expresión es una combinación de operadores, operandos, referencias a otras celdas o fun‐ciones de Excel que al ser evaluada por Excel da un cierto resultado o valor. EL valor retornado por la for‐mula puede ser un número, una fecha, un texto, un valor lógico o un valor de error. Expresión y formula son sinónimos en este contexto. Al crear formulas nos encontramos con dos conceptos importantes: los operadores y su precedencia y las referencias y sus diferentes tipos.
Operadores:
Los operadores que se pueden usar para crear una formula en Excel son los siguientes:
Operadores Aritméticos + Suma - Diferencia * Producto / División ^ Exponente
Operadores Relacionales o de Comparación Operador Definición
= Igual que <> Distinto de < Menor que
<= Menor o igual que > Mayor que
>= Mayor o igual que
Operadores de Texto & Concatenación
Concatena (conecta o enlaza) dos valores o cadenas de texto para producir una cadena de texto continuo.
Operadores de Referencia Operador Definición Ejemplo : Rango =SUMA(A1:A10) ; Unión =SUMA(A1;A10;B12) espacio Intersección =año2007 Ventas
Precedencia de operadores Se llama precedencia o rango a la importancia de unos operadores respecto de otros. La precedencia de-termina como evalúa Excel una formula para producir el resultado final.
En una formula siempre se evalúan primero las operaciones cuyo operador tiene mayor precedencia. En caso de haber dos operadores con la mima precedencia o un mismo operador repetido varias veces, se evalúan de forma secuencial de izquierda a derecha.
Tabla: Operadores de Excel y orden de precedencia
Excel 2007
Página 13
Precedencia Operador Definición 1 () Paréntesis 2 : Rango 3 espacio Intersección 4 ; Unión 5 - Negación 6 % Porcentaje 7 ^ Potencia o Exponente 8 * / Producto y División 9 + - Suma y Resta
10 & Concatenación 11 =,<,>,<=,>=,<> Comparación
Ejemplo: Usando los mismos operandos: 3, 6, 12, 4 y 2 y operadores: (), *, /, +, y – si introduci-mos cuatro formulas según la figura adjun-ta, veremos que los resultados obtenidos (19, 24, 11,50 y 27) son todos diferentes. Ello es debido a la precedencia asignada a cada operador y/o al lugar en el que apa-rece en la formula.
Referencias: concepto y tipos Las referencia son los nombres o direcciones de las celdas. Las referencias se utilizan al escribir formulas que deben tomar datos de otras celdas. El tipo de referencia que se use al escribir una formula no afecta al resultado de dicha formula, pero si es importante si dicha formula va a ser copiada a otras celdas, pues determina el comportamiento y resultado de la formula en esas celdas.
Existen tres tipos de referencias a celdas o rangos: relativas, absolutas y mixtas.
Referencias relativas Cuando se efectúa una copia de una celda que contiene una formula o función con referencias relativas, las celdas de destino desplazan (adaptan) sus direcciones tantas filas y columnas como se haya desplazado la celda original respecto a la celda de destino.
Son las referencias por defecto de Excel. Para ponerlas basta con escribir la letra de la columna, seguida del numero de fila de la celda a la que queremos hacer referencia. No importa mayúsculas y minúsculas, pero no debe de haber ningún espacio entre letra y numero y por supuesto no podemos usar letras o núme-ros de celdas que no existan, por ejemplo no podemos usar la referencia a la celda XXV100 por que aunque si existe la fila 100, no existe la columna XXV.
Las referencias relativas son las mas usuales en Excel dado que muchos cálculos que se efectúan usan la misma formula, solo que esta se debe aplicar en diferente fila o columna a donde se ha definido.
Referencias absolutas La referencias absolutas se usan para que la referencia a una cierta celda no cambie independientemente de donde se copie después la formula que contiene dicha referencia. Son utiles para referenciar aquellas celdas que contienene valores constantes que se usan en múltiples lugares (p.e. el valor del I.V.A.)
Las referencias absolutas se denotan poniendo delante de la letra de la columna y del numero de la fila el símbolo del dólar “$”.
Excel 2007
Página 14
Referencias mixtas Las referencias mixtas son aquellas que en la dirección de una celda tiene una parte relativa y otra absoluta, por ejemplo:
$A1: Seria la una referencia absoluta a la columna A y relativa a la fila 1
A$1: Seria la una referencia relativa a la columna A y absoluta a la fila 1
Modo de especificar o cambiar las referencias a una celda. La forma mas cómoda de introducir o cambiar las referencias a la dirección de una celda es la siguiente:
1. Seleccionar la celda que contiene la formula a modificar. 2. Seleccionar en la barra de herramientas, dentro de la formula, la dirección de la celda cuya referen-
cia queremos modificar. 3. Pulsar la tecla de función F4 de forma continuada, esto hace que Excel vaya cambiando el tipo de
referencia cada vez que pulsamos F4, pasando en cada pulsación por uno de los 4 tipos posible de referencia.
Modo de extender una formula Para extender una formula ya este hecha esta con referencias relativas, absolutas o mixtas, se debe seguir el siguiente procedimiento:
1. Seleccionar la celda donde se halla la formula a extender. 2. Hacer clic en el cuadro de llenado, que se halla en la parte inferior derecha de la celda. 3. Arrastrar hacia derecha, izquierda, arriba o abajo, según la dirección de llenado hacia donde se
desee extender la formula, hasta la celda final de destino. Al arrastrar formulas, al igual que sucede con celdas que contienen fechas o números, se generan series aritméticas, es decir en el caso de formulas si nos desplazamos a derecha o izquierda dentro de la misma fila, solo cambiara la letra de columna de las celdas referenciadas y si nos movemos dentro de una misma columna hacia arriba o hacia abajo solo cambiara el numero de fila de las filas referenciadas en la formula.
Excel 2007
Página 15
Ejemplos de tipos de referencias
Referencias relativas En el ejemplo de la figura, para calcular el Total de ventas por producto para cada producto solo ha sido necesario escribir la formula en B17 y después arrastrarla con el botón o cuadro de llenado en la misma fila hasta E17, dado que la formula es idéntica en todos los casos y lo que cambia es la referencia de las celdas a sumar.
Referencias absolutas Se trata de calcular el Importe total a pagar a cada empleado que ha hecho horas extras, teniendo en cuen-ta que el precio de la hora extra es igual para todos los empleados. Dicho precio se halla en la celda C2. Para calcular correctamente el pago para cada empleado se debe multiplicar las horas que ha hecho el empleado por el precio hora. Para lo cual basta escribir la formula inicial en D5 arrastrarla hasta D8, pero teniendo en cuenta que una de las referencias, la del precio hora, debe de estar fijada como absoluta, para que no varié cuando sea arrastrada la formula. El detalle se puede ver en la siguiente figura
Excel 2007
Página 16
Referencias mixtas Las referencias mixtas se aplican a aquellos problemas en los que intervienen dos elementos variables. Están relacionadas siempre con estructuras de calculo en forma de tabla. Por ejemplo, supongamos que quisiéramos calcular la tabla de multiplicar del número 1 al número 10, empezando el primer calculo en la celda C5, según el desarrollo mostrado en la siguiente ilustración:
Para hacerla bastaría con situarse en dicha celda C5, escribir la formula: =$B5 * C$4 y extenderla arras-trando el cuadro de llenado, primero horizontalmente desde C5 hasta L5 y después, manteniendo la selec-ción de C5 a L5 arrastrarla hacia abajo hasta la fila 10. Dado que tanto los valores de la fila de referencia (fila 4) como los de la columna (columna B) variaran al desplazarnos, las referencias para mantener esta variación deben de ser forzosamente mixtas.
Excel 2007
Página 17
Nombres Los nombres es un mecanismo que facilita enormemente la claridad y compresión de formulas y funciones en cuyo enunciado o argumentos se hace referencia a celdas o rangos de celdas.
Al referirse a una celda o rango en una formula se usa por defecto el lenguaje estándar de Excel, que con-siste en escribir la letra de la columna y el numero de fila de la celda que queremos referenciar o si se trata de un rango, escribir la celda inicia de la misma forma, el operador rango (:) y la celda final del rango. Hay otra forma alternativa para hacer esto, y es asignar nombres a celdas individuales o rangos y después utili-zar estos nombres en las formulas, en lugar del lenguaje referencial de Excel.
Se trata por tanto de crear “alias” bautizando las celdas con nombres propios que tengan un sentido practico y funcional y así prescindir del lenguaje referencial y mas críptico que nos da Excel por defecto. Crear nom-bres para celdas o rangos es totalmente opcional, pero muy practico y recomendable incluso si no se crean formulas de gran complejidad.
Además de nombres a rangos y celdas, se pueden dar nombres a constantes y formulas que no se hallen situadas en ninguna celda, sino que son internas al libro u hoja.
Los nombres, además de para hacer mas inteligibles las formulas y funciones, si son de celdas o rangos sirven también para desplazarnos de forma rápida a la celda o rango que definen, para ello basta con selec-cionar el deseado en el “Cuadro de nombres”.
Definir nombres Se pueden dar nombres a celdas individuales y a rangos (continuos o discontinuos).
Ámbito o visibilidad:
Al definir un nombre tenemos que tener en cuenta el ámbito (visibilidad o alcance), es decir desde que par-tes del libro se puede ver y usar. Excel soporta dos tipos de alcance diferente para los nombres:
• Ámbito global o de libro: el nombre es visible y utilizable en todas las hojas del libro donde se ha de-finido.
• Ámbito local o de hoja: el nombre solo esta disponible en la hoja de calculo en la cual ha sido defini-do.
El ámbito de un nombre no tiene por que coincidir con la hoja a la que pertenece el rango, se puede tener un nombre con ámbito en la Hoja2, pero que se refiera a las celdas de la Hoja5. El ámbito para constantes o formulas internas es siempre global.
Reglas para nombres:
• Deben empezar siempre por una letra • No pueden tener espacios. Si se desea poner un nombre compuesto, se puede usar el guion bajo
como separador o poner la primera letra de cada palabra en mayúscula. Por ejemplo: Importe_total o ImporteTotal.
• Pueden tener hasta 255 caracteres • No pueden usarse nombres que tengan una denominación que coincida con la referencia a una cel-
da. Por ejemplo A123, o IVA16 no son validos. • Para los nombres Excel no distingue entre mayúsculas y minúsculas • No se pueden tener dos nombre iguales dentro del mismo ámbito. Si en ámbitos diferentes o libros
diferentes. Si escribimos un nombre no permitido, el propio Excel nos avisa del error.
Una celda o rango puede tener varios nombres o alias, aunque no es muy usual dar mas de un nombre a un mismo rango.
Dar nombres a celdas o rangos Una vez seleccionada una celda o rango de celdas hay dos formas de darle un nombre
1. Situándonos en el "Cuadro de nombres" de la barra de formulas, escribir el nombre y darle a la tecla ENTER para terminar.
Excel 2007
Página 18
Con esta opción los nombres creados son de ámbito global por defecto. 2. Situarnos en la ficha Formulas, grupo "Nombres definidos", elegir la opción "Asignar nombre a un
rango", "Definir nombre…", lo cual hará que aparezca el cuadro de dialogo “Nombre nuevo" Esta segunda forma nos permite tres posibilidades adicionales:
• Podemos definir el ámbito tanto global, como local. Aquí también podemos crear nombres con ámbito de hoja, pero que hagan referencia a otra hoja, para lo cual basta poner el nom-bre de la hoja y el signo de exclamación antes del nombre del rango, (NombeHo-ja!NombreRango).
• Podemos de añadir opcionalmente un comentario explicativo, que aparecerá en el "Adminis-trador de nombres".
• Podemos dar nombres a constantes o formulas internas.
Cuadro de nombres: Esta situado en la parte izquierda de la barra de formulas. En su parte derecha tiene una flecha que al cli-carla nos muestra todos los nombres a los que se puede acceder desde la hoja en la que estemos en ese momento. Muestra tanto los nombres que tienen ámbito de libro, como aquellos cuyo ámbito es solo la hoja donde estamos situados. En el cuadro de nombre no aparecen los nombres dados a constantes o formulas, para poder ver estos últimos es necesario usar la herramienta "Administrador de nombres".
Seleccionando un nombre cualquiera, Excel nos desplaza y marca el rango que tiene dicho nombre.
sirven también para desplazarnos de forma rápida a la celda o rango que definen, para ello basta con selec-cionar el deseado en el “Cuadro de nombres”.
Utilizar nombres: Para utilizar un nombre en una formula o función basta con escribirlo. En la mayoría de los casos con la nueva funcionalidad de Excel de ayuda automática en la sintaxis, en cuanto hayamos escrito las primeras silabas del nombre aparecerá el mismo en una lista desplegable, basta entonces seleccionarlo con el ratón o darle a la tecla TAB para que Excel termine de escribirlo completo. Caso de no acordarnos de cómo co-mienza el nombre que hemos definido, tenemos la opción de pulsar la tecla F3 que hará aparecer el cuadro de dialogo “Pegar nombre”, donde esta la lista de todos los nombres existentes, basta entonces buscar y seleccionar el deseado y darle al botón Aceptar.
Gestionar nombres: Para controlar que nombres existen en un libro, a que celda o celdas se refieren, conocer su ámbito, o si son nombres de constantes o formulas disponemos de dos herramientas: "Pegar nombres" y "Administrador de nombres"
Excel 2007
Página 19
Pegar nombres: Para disponer de un listado de todos los nombres definidos en el libro en el que estamos, basta con pulsar la tecla F3 y elegir el botón "Pegar lista".
Excel nos creara una lista de dos columnas, a partir de la celda en la que estemos situados, hacia abajo y a derecha. En la primera columna nos da en orden alfabético el nombre de todos nombres definidos, sean del tipo que sean, y en la segunda columna la referencia del rango o formula que representan.
Administrador de nombres :ficha Fórmulas, grupo “Nombres definidos”, “Administrador de nombres”. Esta es una herramienta de gestión o auditoria de nombres mas potente, dado que nos permite editar los existen-tes, borrarlos, ordenarlos por alguna de sus características, saber su ámbito, filtrarlos por diversos criterios, así como crear nuevos nombres.
Dar nombres a constantes y formulas internas Para crear nombre para una constante o formula interna, debemos realizar los siguientes pasos:
Ir a la ficha Formulas, grupo "Nombres definidos", elegir la opción "Asignar nombre a un rango", "Definir nombre…" y aparecerá el cuadro de dialogo "Nombre nuevo"
Escribir en el cuadro de texto Nombre, el nombre que deseamos para la constante o formula.
Escribir en el cuadro de texto "Hace referencia a:" un signo igual (=) y el valor de la contante o la formula o función deseada.
Ejemplos:
IVA=0,16
Total_IVA =TotalImporte * IVA (donde TotalImporte es un nombre de rango existente en el libro)
Si se definie Total= SUMA(A$1;A$10) después bastara poner =Total en una celda cualquiera de cual-quier fila mayor que 10, para que Excel sume las celdas desde la fila 1 a la fila 10 de la columna en la que estemos situados, dado que la referencia a las columnas es relativa, mientras que para las filas es absoluta.
FORMATO
Formatear datos implica darles una apariencia distinta a la que tienen por defecto, no se cambian los datos en si, solo su aspecto. Dar formato sirve no solo para que los datos tengan una mejor apariencia o presen-tación y resulten mas atractivos, sino que en muchos casos sirve también para facilitar mejor su compren-sión o lectura o para marcar o analizar de una forma más rápida dichos datos.
Excel 2007
Página 20
Excel nos facilita una serie de utilidades para dar formato a los datos y resultados de formulas de una forma fácil, rápida y fiable
La mayoría de las opciones directas y esenciales de formato se encuentra en la ficha Inicio, grupos Fuente, Alineación, Número y Estilos y Celdas, según la figura siguiente:
Formatos predefinidos Para la mayoría de tipos de datos existen una serie de formatos de uso común, ya predefinidos. Para acce-der a ellos basta seleccionar primero las celdas a las que se desea aplicar formato y después en el grupo Número, se halla un desplegable donde están definidos los formatos de datos mas usuales, y caso de no hallar el deseado, al final del desplegable aparece la opción "Más formatos de numero…" que lanza el cua-dro de Dialogo "Formato de celdas", donde se halla entre otras la ficha "Número" en la que están el resto de los formatos predefinidos que Excel nos facilita.
Los formatos aparecen agrupados en función de las diversas categorías de formatos que podemos aplicar-les, a los datos, por ejemplo si se tratase de aplicar a un grupo de celdas un cierto formato de fecha, elegi-ríamos la categoría Fecha, donde aparecen todos los formatos disponibles. En el momento que seleccio-nemos cualquiera de ellos, podremos ver una pre visualización de cómo quedarían los datos en la parte superior del cuadro de dialogo, en el cuadro Muestra. De esta forma podremos decidir que formato nos in-teresa antes de aplicarlo definitivamente.
Formatos personalizados
Excel 2007
Página 21
Excel nos permite una serie de posibilidades para que caso de no hallar un formato predefinido que se adapte a nuestras necesidades, poder crearlo. Para ello en el dialogo anterior "Formato de celdas", ficha “Número”, existe la opción “Personalizada”, que permite crear formatos específicos que no existan en nin-guna de las categorías predefinidas.
Los formatos personalizados pueden constar de hasta cuatro secciones diferentes separadas por punto y como. La primera sección define el formato para números positivos, la segunda para números negativos, la tercera para valores 0 y la cuarta y ultima, para el dato que sea texto.
Para la creación de formatos personalizados se pueden emplear los siguientes símbolos o códigos:
Carácter Función
0 Especifica o reserva lugar para un numero por cada cero que se ponga. Si al introducir la cifra seleccionada, tiene menos dígitos de los indicados en el formato, en los lugares donde no exista número se mostraran ceros.
# Especifica una cifra opcional por cada símbolo que se ponga. Es decir si el numero introduci-do tiene un valor en la posición del símbolo lo mostrara y si no lo hay, no mostrara nada.
? Agrega los espacios de los ceros sin valor a cada lado del separador para alinear los decima-les. También puede utilizarse este símbolo para las fracciones que tengan un número de dígitos variable.
. Especifica el lugar de aparición del punto separador de millares.
, Especifica el lugar de aparición de la coma separadora de decimales.
* Especifica que el carácter que se ponga después del símbolo sea repetido en todas las posi-ciones de la celda hasta rellenar el espacio no utilizado de dicha celda.
_ El guion bajo representa un espacio en blanco. Se suele usar para alinear cifras numéricas con decimales.
@ Indicador de texto. Presenta el valor de la celda cuando sea texto.
\ Muestra el carácter que siga a la barra como un literal y no con el significado especial que pudiera tener. Por ejemplo \E mostraría una E no lo tomaría como símbolo de exponencial.
E Muestra un numero en notación científica o exponencial en base diez, diez elevado a …
"€/h" Permiten introducir texto literal en el formato. Se usa usualmente en números.
[color] Se puede hacer que cualquier sección del formato tome uno de los colores básicos existente, para ello basta poner el nombre del color entre corchetes cuadrados ([]).Los colores que po-demos utilizar tienen los siguientes nombres: Negro, Azul, Verde, Fucsia, Rojo, Blanco, Ama-rillo y Aguamarina.
<,>,= Permiten escribir formatos condicionales usando los operadores relacionales entre corchetes cuadrados ([])
Excel 2007
Página 22
Ejemplos
Formato para texto:
1- Formato para un número de Teléfono: ## ### ###-##-## (# =no obligatorio) Si escribo 9876543211 me presentará: 987 654-32-11
2- Formato para un número de Teléfono: 00 000 000 00 00 (0 = obligatorio) Si escribo 9876543211 me presentará: 00 987 654 3211
Formato para números
1- Hacer un formato para números que presente separador de miles y un digito decimal, pero que solo incluya la primera sección:
#.##0.0 Presentara el mismo formato para números positivos, negativos o cero.
2- El mismo ejemplo anterior, pero incluyendo formato también para la sección de los números negati-vos.
#.##0.0; "NEGATIVO" Así si escribo 1000,456 presentara 1.000,5. Si escribo -45 presentara "NEGATIVO"
A los números 0 se les aplica el mismo formato que a los positivos, pues no se ha definido formato especifico para la tercera sección.
3- Mismo ejemplo, pero incluyendo formato para las tres primeras secciones.
#.##0.0; "NEGATIVO"; "CERO"
4- Mismo ejemplo anterior incluyendo un formato especifico para la cuarta sección.
#.##0;-#.##0,00;"CERO";"ERROR! No es un número"
5- Mismo ejemplo anterior, al que se le añade color a la sección segunda , tercera y cuarta.
#.## "h";[rojo]"NEGATIVO";[azul]"CERO"; [Rojo]"ERROR! No es un numero"
Excel 2007
Página 23
Formato condicional Excel permite resaltar el contenido de una celda aplicando diferentes formatos en función de que el valor que exista dentro de la celda cumpla uno o mas criterios específicos o en función de la evaluación de una cierta formula que esta relacionada de una forma lógica con dicha celda. Por ejemplo es posible marcar con un color de fuente o fondo todas las celdas de un rango cuyo valor sea superior a una cantidad y marcar el resto con otro color diferente.
El formato condicional es una herramienta de análisis visual de datos, dado que nos permite resaltar la in-formación según nos interese y después localizarla de un vistazo. Es una herramienta que refuerza o com-plementa al formato personalizado y se vuelve especialmente útil cuando la cantidad de datos a evaluar es elevada y por tanto es difícil localizar rápidamente los datos de nuestro interés.
Se puede aplicar formato condicional tanto a celdas que contengan constantes como a celdas que conten-gan formulas. En este ultimo caso Excel primero evalúa la formula y después aplica el formato al resultado obtenido teniendo en cuenta las condiciones o criterios especificados para la celda en cuestión.
El formato condicional permite aplicar a una celda o grupo de celdas diferente color o estilo de fuente, bor-des, color de fondo, tramas, gradación de colores en función del valor o conjuntos de iconos semánticos seleccionados de una colección existente en Excel.
Se puede establecer un número ilimitado de condiciones de formato condicional para cada celda o rango de celdas.
Definir un formato condicional Para establecer un formato condicional se deben dar los siguientes pasos:
Seleccionar la celda o celdas a las que se desee aplicar formato condicional.
Ir a ficha Inicio, grupo Estilos, desplegable Formato Condicional, según muestra la imagen siguiente, donde hallamos las posibilidades mas usuales en opciones directas.
Si queremos aplicar algún tipo de regla no existente dentro de estas opciones directas deberemos seleccio-nar o bien la opción "Nueva regla…" o bien "Administrar reglas…", botón "Nueva regla", ambas hacen apa-recer el cuadro de dialogo "Nueva regla de formato" donde podemos definir la regla deseada.
Excel 2007
Página 24
En cuadro de dialogo "Nueva regla de formato" que aparece en ambos casos, existen seis posibilidades o tipos de reglas diferentes (ver figura Dialogo "Nueva regla de formato"), que se pueden sintetizar en lo si-guiente:
A una celda o grupo de celdas se les puede aplicar formato condicional según:
• El valor de la celda. • El contenido de la celda. Es decir el tipo de dato de la celda • El resultado de evaluar una cierta formula o función relacionada de forma lógica con dicha celda.
Superposición de formatos y multiples condiciones Los formatos condicionales pueden superponerse, por ejemplo se puede aplicar un color a una celda y al mismo tiempo mostrar un icono.
También puede haber mas de una condición o regla aplicable a una misma celda. Las reglas se irán apli-cando en el orden en que aparezcan. En caso de conflicto entre dos criterios de aplicara el que aparezca primero en la lista de reglas definidas. Por ejemplo se puede aplicar un color de fondo a una celda si cumple un cierto criterio y al mismo tiempo un color de letra, o negrita o cursiva si ademas cumple otro criterio
Cuando una condición dada se cumple, se puede determinar que no se sigan evaluando el resto de condi-ciones, para ello basta con activar la casilla "Detener si es verdad" dentro del "Administrador de reglas de formato condicional"·
Administrar reglas de formato condicional En la misma ficha Inicio, grupo Estilos, Formato Condicional, al final del desplegable tenemos la opción "Administrar reglas…" que lanza el cuadro de dialogo "Administrador de reglas de formato condicionales"
Dicho administrador permite gestionar diversos aspectos de las reglas que ya tengamos definidas o quera-mos definir para la celda o celdas actualmente seleccionadas. Tiene las siguientes opciones para el control del formato condicional:
• Crear una nueva regla haciendo clic en el botón "Nueva regla…".
Excel 2007
Página 25
• Cambiar una regla existente seleccionando la regla y pulsando después el botón "Editar regla…" • Eliminar una regla existente seleccionando la regla y pulsando después el botón "Eliminar regla…" • Cambiar el orden de evaluación de una regla moviéndola hacia arriba o abajo haciendo clic en el
botón Subir o Bajar. • Determinar si Excel debe de seguir evaluando formatos condicionales después de encontrar una re-
gla que se cumple, marcando o desmarcando para ello la casilla "Detener si es verdad". • Guardar cualquier regla nueva o cambiada y cerrar el dialogo haciendo clic en Aceptar. • Guardar cualquier regla nueva o cambiada sin cerrar el dialogo haciendo clic en Aplicar. • Descartar cualquier cambio hecho que no se haya guardado haciendo clic en Cerrar. • En detalle nos permite controlar los formatos condicional
Copiar y pegar formato Existe una opción que nos permite copiar el formato de una determinada celda o celdas y asignárselo a otra celda o rango de celdas. Esta opción es la herramienta copiar formato, que se halla situada en la ficha Inicio
> Portapapeles > Copiar formato( ) La forma de proceder es la siguiente
1. Seleccionar la celda o celdas que tienen el formato que queremos copiar. 2. Clicar encima del botón “Copiar formato” de la ficha Inicio > Portapapeles. 3. Clicar encima de la celda a partir de la cual queremos aplicar el formato o bien hacer clic sostenido
y arrastrar por el conjunto de celdas a las que queremos aplicar dicho formato. Si se desea aplicar el mismo formato mas de una vez en varias celdas o rangos distintos, en el paso segun-do, deberíamos hacer doble clic sobre el icono “Copiar formato” lo cual hará que la herramienta permanezca operativa hasta que no volvamos a clicar encima de dicho icono.
Excel 2007
Página 26
FUNCIONES
Una función es una formula programada o abreviada, que efectúa una o varias operaciones simultáneamen-te, devolviendo un resultado determinado. Las funciones es un mecanismo para facilitarnos el calculo de la mayor parte de operaciones, sean del tipo que sean.
Las funciones pueden utilizarse en una formula o expresión de forma independiente o combinadas con ope-radores y operandos diversos. También pueden formar parte de otra función (funciones anidadas) o usarse para la creación de formatos condicionales, reglas de validación, campos calculados o funciones internas personalizadas definidas por el usuario de Excel.
Estructura de una función Toda función de Excel tiene una estructura o sintaxis básica, que es
=NOMBRE_DE_LA_FUNCION(Lista de argumentos)
NOMBRE_DE_LA_FUNCION: Es el nombre de la función a usar. El nombre debe de ser exacto, excepto mayúsculas o minúsculas que Excel no distingue aquí, caso contrario Excel devolverá el valor de error: #¿NOMBRE?.
Lista de argumentos. Los argumentos son la información que necesita conocer la función para hacer su trabajo. La lista de argumentos o parámetros siempre esta encerrada entre paréntesis, incluso si la función no necesita argumentos. Cada argumento se separa del siguiente por un punto y coma (;).
La longitud máxima en caracteres que puede tener una formula o función contando todos sus caracteres es de 8.192.
Tipos de argumentos Los argumentos de una función pueden ser obligatorios y opcionales. Los obligatorios están marcados en negrita, tanto en la ayuda automática que va apareciendo a medida que vamos escribiendo la función, como si usamos el asistente “Insertar función”, los no obligatorios están en letra normal y aparecen entre corche-tes en la ayuda automática.
Una función pude admitir un número fijo de argumentos (incluso ninguno) o un numero variable, depende del tipo de función. En cada caso la ayuda automática o el asistente “Insertar función” nos aclaran la cues-tión. En el caso de funciones con un numero variable de argumentos el numero máximo que pueden llegar a tener es de 225.
Los tipos de argumentos que una función puede admitir son:
• Ningún argumento. Por ejemplo las funciones PI((), HOY() y AHORA();
• Números: SUMA(43;300;2)
• Referencias a celdas individuales o rangos: SUMA(A1;B10:C20;D50)
• Nombres de celdas o rangos: PRODUCTO(Cantidad; Precio)
• Texto: Cadenas de texto: MAYUSCULAS(“jesús”)
• Valores lógicos: SI(A1>50;89;A2) o pruebas lógicas
• Valores de Error: TIPO.DE.ERROR(#Nombre)
• Matrices: SUMAPRODUCTO(A1:A10;B1:B10). Algunas funciones como TRANSPONER o TENDEN-CIA requieren el uso de matrices como argumentos.
• Otras funciones: SUMA(MAX(A1:A20);B1:B20). A estas funciones internas se les denomina funciones anidadas. Excel admite un máximo de 64 niveles de anidamiento.
• Una mezcla de algunos o todos los tipos mencionados anteriormente.
Categorías de funciones
Excel 2007
Página 27
De forma intrínseca Excel posee mas de 400 funciones que permiten efectuar los mas variados cálculos, desde los mas sencillos hasta los más complejos. Para un acceso mas rápido a la función deseada y a su ayuda, Excel agrupa las funciones en diversos tipos o categorías funcionales. En concreto existen las si-guientes categorías de funciones:
• Funciones de complemento y automatización
• Funciones de cubo
• Funciones de base de datos
• Funciones de fecha y hora
• Funciones de ingeniería
• Funciones financieras
• Funciones de información
• Funciones lógicas
• Funciones de búsqueda y referencia
• Funciones matemáticas y trigonométricas
• Funciones estadísticas
• Funciones de texto
Dentro de cada categoría las funciones aparecen ordenadas en orden alfabético.
Si conocemos el nombre de una función, pero no sabemos en que categoría esta, podemos buscarla dentro de la categoría agrupadora “Todas”, donde se hallan todas las funciones existentes en el resto de catego-rías, ordenadas también alfabéticamente.
Para acceder a estas categorías y ver las funciones que componen cada una de ellas, podemos hacerlo de dos formas:
• Desplazándonos a la ficha Fórmulas, grupo “Biblioteca de funciones” donde aparecen las categorías mas relevantes. Las categorías restantes están dentro del desplegable “Más funciones”,
• Situarnos en la celda donde deseemos escribir la función y clicar en el asistente “Insertar función” que se halla en misma ficha Fórmulas, grupo “Biblioteca de funciones” y también en la “Barra de fórmu-las”.
En ambos casos accederemos a todas las categorías de funciones de Excel excepto a “Funciones de com-plemento y automatización”. Esta categoría engloba diversas funciones o subprogramas muy especializa-dos, que es necesario cargar previo a su uso. Para ello debemos ir a “Botón de Office” >”Opciones de Ex-cel” > ”Complementos” y allí habilitar el complemento deseado. Entre los mas destacados están: “Herra-mientas para análisis” y “Solver”.
Introducción de funciones Existen dos procedimientos para introducir una función en una celda, manualmente o mediante el asistente “Insertar función”
A) Procedimiento semi-manual
1. Seleccionar la celda donde se desea poner la formula.
2. Escribir el signo igual (=) y empezar a escribir el nombre de la función, p.e. PROMEDIO. Nada mas escribirla aparece un desplegable con todas las funciones de Excel o nombres de rangos definidos por nosotros. A medida que vamos escribiendo mas letras de la función la lista se va ajustando para mostrarnos solo aquellas funciones que comienzan por lo escrito. En el momento que estemos en la deseada no hace falta escribir toda la función, solo picar la tecla TAB y Excel escribe el resto del nombre y el paréntesis de apertura necesario para toda función.
3. Escribir los argumentos, o si son referencias a celdas o rangos marcarlos con el cursor.
Excel 2007
Página 28
4. Finalmente escribir el paréntesis de cierre de la función y terminar pulsando ENTER.
B) Procedimiento semi-automatizado utilizando el asistente “Insertar función”
1. Seleccionar la celda donde se desea poner la formula.
2. Hacer clic en el botón “Insertar función” del barra de formulas o de la ficha Formulas.
3. Seleccionar la categoría adecuada, aquí funciones Estadísticas
4. Seleccionar la función: p.e. PROMEDIO. En la parte inferior del cuadro de dialogo “Insertar función”
no aparecerá una breve descripción del cometido de la función y un hipervínculo a la pagina de ayuda donde nos explica mas en detalle dicho cometido así como la sintaxis exacta de la función y probablemente algún ejemplo clarificador de su uso.
5. Clicar sobre el botón “Aceptar” para proseguir con la creación de la función. Se escribirá el nombre de la función en la barra de formulas y nos aparecerá el dialogo “Argumentos de la función” para que introduzcamos manualmente o mediante el ratón los valores o referencias a celdas y rangos donde estén las cantidades de las que queremos hallar el promedio.
6. Hacer clic en el cuadro de texto Número1 y después seleccionar directamente en la hoja de calculo el rango A1:A10. De forma similar hacerlo con Número2 y rango C1:C7 y con Número3 rango K1:K4.
7. En la parte inferior del dialogo se mostrara el resultado de la función a medida que se vayan aña-diendo argumentos.
8. Cuando hayamos añadido todos los argumentos basta hacer clic en el botón “Aceptar” y Excel in-sertara debidamente la formula en la celda seleccionada en el punto 1.
Excel 2007
Página 29
Grupos de funciones importantes Como se ha mencionado Excel posee mas de 400 funciones. A continuación se van a detallar las más ge-néricas y útiles de cada categoría. El orden en el que aparecen tanto las categorías, como las funciones dentro de su categoría es un orden funcional, es decir se ha pretendido poner las funciones por orden de relevancia o importancia.
FUNCIONES ESTADISTICAS Las funciones estadísticas permiten analizar grupos de números o datos. Aquí se mencionaran las funcio-nes estadísticas mas usuales y utilizadas. En esta categoría existen muchas funciones especializadas y funciones avanzadas como ESTIMACION.LINEAL, ESTIMACION.LOGARITMICA, TENDENCIA, CRECI-MIENTO, PROBABILIDAD, JERARQUIA, etc.
CONTAR.BLANCO
Devuelve un número que representa la cantidad de cedas en blanco existentes dentro de un rango dado.
Sintaxis
CONTAR.BLANCO(rango)
Rango es el rango seleccionado dentro del cual se quiere contar el numero de celdas en blanco existentes.
Si existen formulas que devuelven "" (cadena o texto vacio) también se cuentan. Las celdas cuyo contenido son valores cero no se cuentan.
Ejemplos
CONTAR.BLANCO(A1:A7) es igual a 3
CONTAR.BLANCO(A2:A7) es igual a 2
CONTAR
Cuenta el número de celdas que contienen números.
Sintaxis:
CONTAR(ref1;ref2;…)
Ref1; ref2; ... son entre 1 y 225 argumentos que pueden contener o hacer referencia a distintos tipos de datos, pero sólo se cuentan los números.
Los argumentos que son números, fechas o representaciones textuales de números se cuentan. También se cuentan las celdas cuyo contenido son formulas que devuelven un número o fecha. Los argumentos que son valores de error o texto que no puede traducirse a números se pasan por alto.
Si un argumento es una matriz o una referencia, sólo se cuentan los números de esa matriz o referencia. Se pasan por alto las celdas vacías, valores lógicos, texto o valores de error de la matriz o de la referencia.
Utilizar la función CONTARA si se necesita contar valores lógicos, texto o valores de error.
A 1
2 Felipe Rodríguez 3
4 1245,33 5 21/12/2009 6
7 1.581
Excel 2007
Página 30
Ejemplos
CONTAR(A1:A10) es igual a 3, que son las celdas que contienen un numero, una fecha y una formula que retorna un número (1.581) respectivamente.
CONTARA
Cuenta el número de celdas que no están vacías y los valores que hay en la lista de argumentos, Usar CONTARA para contar el número de celdas que contienen datos en un rango o matriz. Si no se necesita contar valores lógicos, texto o valores de error, usar la función CONTAR.
Sintaxis
CONTARA(valor1 ;valor2; ... )
Valor1; valor2; ... son de 1 a 225 argumentos que representan los valores que se desea contar. En este caso, un valor es cualquier tipo de información, incluyendo texto vacio (""), pero excluyendo celdas vacías. Si un argumento es una matriz o una referencia, se pasan por alto las celdas vacías que se encuentran en la matriz o en la referencia.
Ejemplos
CONTARA(A1:A10) es igual a 8, pues también incluye el valor de A8 que es una cadena vacía o de longitud cero, que es distinto de celda vacía.
CONTAR.SI
Cuenta las celdas, dentro del rango, que no están en blanco y que cumplen con el criterio especificado. Es una extensión de la función CONTAR.
Sintaxis
CONTAR.SI(rango; criterio)
Rango es el rango dentro del cual desea contar el número de celdas que no están en blanco.
Criterio es el criterio en forma de número, expresión o texto, que determina las celdas que se van a contar. Por ejemplo, el argumento criterio puede expresarse como 55; "55"; ">55" o "Sevilla".
A 1
2 Felipe Rodríguez 3 #¡NUM! 4 1245,33 5 21/12/2009 6
7 1.581 8 "" 9 Alberto San Juan 10 VERDADERO
A 1
2 Felipe Rodríguez 3 #¡NUM! 4 1245,33 5 21/12/2009 6
7 1.581 8 "" 9 Alberto San Juan 10 VERDADERO
Excel 2007
Página 31
Ejemplos
Supongamos que el rango A1:A5 contiene los valores: “Madrid”, ”Barcelona”, ”Sevilla”, ”Barcelona” y ”Va-lencia”, respectivamente, entonces CONTAR.SI(A1:A5;”Barcelona”) es igual a 2.
Supongamos que el rango B1:B5 contiene los valores: 12,50,33,20 y 31, entonces CONTAR.SI(B1:B5; ">30") es igual a 3.
CONTAR.SI.CONJUNTO
Cuenta el número de celdas en un rango para celdas que tienen múltiples criterios. Es una extensión o am-pliación de CONTAR.SI que en lugar de un solo criterio admite múltiples criterios.
Sintaxis
CONTAR.SI.CONJUNTO(rango_criterios1;criterio1;[rango_criterios2;criterio2];…)
Rango_criterios1, Rango_criterios2,…Son los rangos dentro de los cuales se compara el criterio correspon-diente para ver que celdas del rango correspondiente lo cumplen.
Criterio1, Criterio2,… son los criterios en forma de número, texto o fecha que determinan las celdas que se contarán.
Esta función admite múltiples criterios a la vez, pero sobre campos o columnas distintas. Para aplicar múlti-ples criterios sobre un mismo campo debemos usar funciones de tipo “Bases de datos”, como por ejemplo BDCONTAR o BDCONTARA
Ejemplos
Supongamos la siguiente tabla de clientes y ventas:
A B C D E F 1 Clientes Zona Ciudad Mes Cantidad Importe 2 EL CORTE INGLES Centro Madrid 9 100 1000 3 IKEA Catalunya Barcelona 9 50 900 4 MUEBLES LA FABRICA Centro Madrid 11 25 1200 5 OFIMUEBLE Catalunya Barcelona 5 100 1800 6 LA CASA DE LAS LAMPARAS Andalucía Sevilla 6 120 2000 7 IKEA Andalucía Sevilla 9 50 1900 8 TODO HOGAR Catalunya Barcelona 9 100 800 9 NOVALINEA Aragón Zaragoza 8 200 1200
10 EL CORTE INGLES Aragón Zaragoza 3 120 1300 11 OFIMUEBLE Catalunya Barcelona 2 100 1200
Si quisiéramos hallar el número de clientes de Barcelona a los que se les facturo menos de 1000 €, escribi-ríamos la función así: CONTAR.SI.CONJUNTO(C2:C11;"Barcelona";F2:F11;"<1000")
MIN
Devuelve el valor mínimo de un conjunto de valores.
Sintaxis
MIN(número1 ;número2; ...)
Número1;número2; ... son entre 1 a 225 números cuyos valores mínimos se desea encontrar. Se pueden especificar argumentos que sean números, celdas vacías, valores lógicos o representaciones textuales de números. Los argumentos que son valores de error o texto que no se puede traducir a números, provocan errores.
Si un argumento es una matriz o referencia, sólo se usan los números de esa matriz o referencia. Las cel-das vacías, valores lógicos o texto que se encuentren dentro de la matriz o referencia se pasan por alto. Utilizar la función MINA si los valores lógicos o el texto no deben pasarse por alto.
Si los argumentos no contienen números, MIN devuelve 0.
Ejemplos
Si A1 :A5 contiene los números 36, 12, 5, 43, y 14, entonces:
Excel 2007
Página 32
MIN(A1:A5) es igual a 5
MIN(A1:A5;8) es igual a 8.
MAX
Devuelve el valor máximo de un conjunto de valores.
Sintaxis
MAX(número1 ;número2; ... )
Número 1; número2; ... son entre 1 y 225 números para los que se desea encontrar el valor máximo. Se pueden especificar argumentos que sean números, celdas vacías, valores lógicos o representaciones de números en forma de texto. Los argumentos que sean valores de error o de texto que no se puedan traducir a números provocan errores.
Si un argumento es una matriz o referencia, se usarán sólo los números de esa matriz o referencia. Las· celdas vacías, valores lógicos o texto que se encuentren dentro de la matriz o referencia se pasarán por alto. Si no se desea pasar por alto los valores lógicos y el texto, utilizar MAXA.
Si el argumento no contiene números, MAX devuelve 0
Ejemplos
Si A1:A5 contiene los números 36, 12, 5, 43 y 14, entonces:
MAX(A1 :A5) es igual a 437
MAX(A1 :A5;80) es igual a 80
PROMEDIO
Devuelve la media aritmética de los argumentos.
Sintaxis
PROMEDIO(número1 ;número2; ...)
Número1;número2; ... son de 1 a 225 argumentos numéricos cuyo promedio se desea obtener.
Los argumentos deben ser números o nombres, matrices o referencias que contengan números.
Si un argumento matricial o de referencia contiene texto, valores lógicos o celdas vacías, esos valores se pasan por alto; sin embargo, se incluyen las celdas cuyo valor sea 0.
Al calcular el promedio de las celdas, hay que tener en cuenta la diferencia entre las celdas vacías y las que contienen el valor cero, especialmente si ha desactivado la casilla de verificación “Mostrar un cero en celdas que tienen un valor cero” de “Opciones de Excel” > Avanzadas > “Mostrar opciones para esta hoja” ,no se cuentan las celdas vacías, pero se cuentan los valores cero.
Ejemplos
Si A1:A5 se denomina Unidades y contiene los números 10, 7, 9, 27 y 2 entonces:
PROMEDIO(A1:A5) es igual a 11
PROMEDIO(Unidades) es igual a 11
PROMEDIO(A1:A5;5) es igual a 10
PROMEDIO(A1:A5) es igual a SUMA(A1:A5)/CONTAR(A1:A5), que es igual a 11
Excel 2007
Página 33
MEDIANA
Devuelve la mediana de los números. La mediana es el número que se encuentra en medio de un conjunto de números, es decir, la mitad de los números es mayor que la mediana y la otra mitad es menor.
Sintaxis
MEDIANA(número1 ;número2; ..)
Número1;número2; .... son entre 1 y 225 números cuya mediana desea obtener.
Los argumentos deben ser números o nombres, matrices o referencias que contengan números. Excel examina todos los números en cada argumento matricial o de referencia.
Si un argumento matricial o de referencia contiene texto, valores lógicos o celdas vacías, esos valores se pasan por alto; sin embargo, se incluyen las celdas cuyo valor sea 0.
Si la cantidad de números en el conjunto es par, MEDIANA calcula el promedio de los números centrales.
Ejemplos
MEDIANA(1; 2; 3; 4; 5) es igual a 3
MEDIANA(1; 2; 3; 4; 5; 6) es igual a 3,5, el promedio de 3 y 4
MODA
Devuelve el valor que se repite con más frecuencia en una matriz o rango de datos. Al igual que MEDIANA, MODA es una medida de posición o centralización
Sintaxis
MODA(número1 ;número2; ...)
Número1;número2; ... son entre 1 y 225 argumentos cuya moda se desea calcular. También se puede usar una matriz individual o una referencia matricial en lugar de argumentos separados por punto y coma.
Los argumentos deben ser números, nombres, matrices o referencias que contengan números.
Si un argumento matricial o de referencia contiene texto, valores lógicos o celdas vacías, esos valores se pasan por alto; sin embargo, se incluirán las celdas cuyo valor sea 0.
Si el conjunto de datos no contiene puntos de datos duplicados, MODA devuelve el valor de error #N/A.
Ejemplo
MODA({5,6: 4; 4; 3; 2; 4}) es igual a 4
DESVEST
Calcula la desviación estándar de una muestra. La desviación estándar es la medida de la dispersión de los valores respecto a la media (valor promedio).
Sintaxis
DESVEST(número1; número2; ...)
Número 1; número2; ...son de 1 a 225 argumentos numéricos que corresponden a una muestra de pobla-ción. También se puede utilizar una matriz única o una referencia matricial en lugar de argumentos separa-dos con punto y coma.
Se pasan por alto los valores lógicos como VERDADERO y FALSO y el texto. Si los valores lógicos y el texto no deben pasarse por alto, utilizar la función de hoja de cálculo DESVESTA.
DESVEST parte de la hipótesis de que los argumentos representan la muestra de una población. Si los datos representan la población total, utilizar DESVESTP para calcular la desviación estándar.
Ejemplo
Supongamos que tenemos una muestra aleatoria de 10 herramientas forjadas por la misma máquina du-rante un ciclo de producción y cuya resistencia a la rotura se desea medir. Los valores de la muestra (1345; 1301; 1368; 1322; 1310; 1370; 1318; 1350; 1303; 1299) se almacenan en las celdas A2:E3 res-pectivamente. DESVEST calcula la desviación estándar de la resistencia a la rotura de todas las herra-mientas producidas.
Excel 2007
Página 34
DESVEST(A2:E3) es igual a 27,46
VAR
Calcula la varianza de una muestra.
Sintaxis
VAR(número1 ;número2;.) .
Número 1; número2; ... son de 1 a 225 argumentos numéricos que se corresponden con una muestra de población.
La función VAR parte de la hipótesis de que los argumentos representan una muestra de la población. Si los datos representan fa población total;·utilizar VARP·para calcular la varianza.
Se pasan por alto los valores lógicos como VERDADERO y FALSO Y el texto Utilizar la función de hoja de cálculo DESVESTA si los valores lógicos y el texto no se deben pasar por alto.
Ejemplo
Supongamos que toma una muestra aleatoria de 10 herramientas forjadas por la misma máquina duran-te un ciclo de producción y cuya resistencia a la rotura desea medir. Los valores de la muestra (1345; 1301; 1368; 1322; 1310; 1370; 1318; 1350; 1303; 1299) se almacenan en las celdas A2:E3 respectiva-mente. VAR calcula la varianza de la resistencia a la rotura de todas las herramientas producidas.
VAR(A2:E3) es igual a 754,3
Excel 2007
Página 35
FUNCIONES LÓGICAS Las funciones lógicas sirven para evaluar pruebas lógicas, es decir comparar valores o comprobar si se cumple una o varias condiciones. Las funciones lógicas suelen emplear los operadores relaciones o de comparación en alguno de sus argumentos y en general retornan los valores lógicos VERDADERO o FAL-SO.
SI
Se utiliza para realizar pruebas condicionales en valores y fórmulas. Devuelve un valor si la condición espe-cificada es VERDADERO y otro valor si dicho argumento es FALSO.
Sintaxis
SI(prueba-lógica;valor_si_verdadero;valor_si_falso)
Prueba_lógica es cualquier valor o expresión que pueda evaluarse como VERDADERO o FALSO.
Valor_si_verdadero es el valor que se devolverá si prueba_lógica es VERDADERO. Si prueba_lógica es VERDADERO y se omite el argumento valor_si_verdadero, la función devuelve VERDADERO. Este argu-mento puede ser a su vez otra fórmula.
Valor_si_falso es el valor que se devolverá si prueba_lógica es FALSO. Si prueba_lógica es FALSO y se omite el argumento valor_si_falso, la función devuelve FALSO. También puede ser una fórmula.
Es posible anidar hasta 64 funciones SI como argumentos valor_si_verdadero y valor_si_falso para cons-truir pruebas con más condiciones o condiciones más elaboradas.
Cuando los argumentos valor_si_verdadero y valor_si_falso se evalúan, la función SI devuelve el valor de-vuelto por la ejecución de las instrucciones.
Si uno de los argumentos de la función SI es una matriz, cada elemento de la matriz se evaluará cuando se ejecute la instrucción SI.
Ejemplos
En el siguiente ejemplo, si el valor en la celda A:10 es 100, prueba_lógica será VERDADERO y se calcu-lará el valor total del rango B5:B 15. De lo contrario, prueba_lógica será FALSO y se devolverá una ca-dena de texto vacía (""') que borrará el contenido de la celda que contenga la función SI.
SI(A10=100;SUMA(B5:B15);"")
Supongamos que una hoja de cálculo contiene las cifras de los gastos actuales y los pronosticados. Las celdas B2:B4 contienen los "Gastos actuales" para enero, febrero y marzo: 1500; 500; 500. Las cel-dasC2:C4contienen k>s "Gastos pronosticados" para los mismos periodos: 900; 900;,925,..
Con las siguientes fórmulas puede escribir una fórmula que compruebe si se ha excedido el presupuesto:
SI(B2>C2;"Presupuesto excedido";"Aceptar") es igual a "Presupuesto excedido"
SI(B3>C3;"Presupuesto excedido";"Aceptar") es igual a "Aceptar"
O
Devolverá VERDADERO si alguno de los argumentos es VERDADERO; devolverá FALSO si todos los ar-gumentos son FALSO.
Sintaxis
O(valor_lógico1 ;valor_lógico2;… )
Valor_lógico1; valor_lógico2; ... son entre 1 y 225 condiciones que se desean comprobar y que pueden ser VERDADERO o FALSO.
Los argumentos deben ser valores lógicos como VERDADERO O FALSO, o matrices o referencias que contengan valores lógicos.
Si un argumento matricial o de referencia contiene texto, números o celdas vacías, dichos valores se pasa-rán por alto.
Si el rango especificado no contiene valores lógicos, O devolverá el valor de error #¡VALORI
Excel 2007
Página 36
Se puede utilizar la fórmula matricial O para comprobar si un valor aparece en una matriz. Para introducir una fórmula matricial pulsar Ctrl+Mayúsculas+lntro.
Ejemplos
O(VERDADERO) es igual a VERDADERO
O(1+1 =1 ;2+2=5) es igual a FALSO
Si el rango A1:A3 contiene los valores VERDADERO, FALSO Y VERDADERO, entonces:
O(A1:A3) es igual a VERDADERO
Y
Devuelve VERDADERO si todos los argumentos son VERDADERO; devuelve FALSO si uno o más argu-mentos son FALSO.
Sintaxis
Y(valor_lógico1 ;valor_lógico2;… )
Valor_lógico1;valor_lógico2; ... son de 1 a 225 condiciones que desea comprobar y que pueden ser VER-DADERO o FALSO.
Los argumentos deben evaluarse como valores lógicos (VERDADERO O FALSO), o los argumentos deben ser matrices o referencias que contengan valores lógicos.
Si un argumento matricial o de referencia contiene texto o celdas vacías, esos valores se pasan por alto.
Si el rango especificado no contiene valores lógicos, la función Y devuelve el valor de error #¡VALORI
Ejemplos
Y(VERDADERO;VERDADERO) es igual a VERDADERO
Y(VERDADERO;FALSO) es igual a FALSO
Y(2+2=4; 2+3=5) es igual a VERDADERO
Si B1:B3 contiene los valores VERDADERO, FALSO y VERDADERO, entonces:
Y(B1:B3) es igual a FALSO
Si B4 contiene un número entre 1 y 100, entonces:
Y(1<B4;B4<100) es igual a VERDADERO
Supongamos que se desea mostrar la celda B4 sólo si contiene un número entre 1 y 100, Y que debe mostrar un mensaje si no lo contiene. Si B4 contiene 104, entonces:
SI(Y(1 <B4; B4<100);B4;"El valor queda fuera del rango") es igual a "El valor queda fuera del rango."
Si B4 contiene 50, entonces: SI(Y(1 <B4; B4<100); B4; "El valor queda fuera del rango") es igual a 50
SI.ERROR
Sirve para desplegar un mensaje de error personalizado en la propia celda en la que se utiliza. Es decir devuelve un valor que es el resultado de ejecutar una formula o función o caso de producirse un error en dicha formula, retorna un mensaje de error personalizado. Se debe usar para interceptar y controlar errores en formulas.
Sintaxis
SI.ERROR(valor ;valor_si_error)
Valor es una, formula, expresión o función que puede o no producir un error en su evaluación.
Valor_si_error es el valor que se devuelve si la fórmula lo evalúa como error. Se evalúan los tipos siguientes de error: #N/A, #¡VALOR!, #¡REF!, #¡DIV/0!, #¡NUM!, #¿NOMBRE? o #¡NULO!.
Excel 2007
Página 37
Ejemplo
Supuesta la siguiente tabla de Clientes, se desea buscar los valores de Ciudad para un cliente determi-nado dando como información inicial el nombre del cliente en la celda A10.
A B C 1 Clientes Zona Ciudad 2 EL CORTE INGLES Centro Madrid 3 IKEA Catalunya Barcelona 4 MUEBLES LA FABRICA Centro Madrid 5 OFIMUEBLE Catalunya Barcelona 6 LA CASA DE LAS LAMPARAS Andalucía Sevilla 7 IKEA Andalucía Sevilla
Si introduzco al formula siguiente en la celda B10: =SI.ERROR(BUSCARV(A10;B1:C7;3;FALSO);"El cliente no existe")
Si en A 10 escribo OFIMUEBLE el resultado que se mostrara en B10 es: Barcelona.
Si en A10 escribo OFIMUEBLES el resultado que se mostrara en B10 es: "El cliente no existe"
Excel 2007
Página 38
FUNCIONES DE BUSQUEDA Y REFERENCIA Las funciones de Búsqueda y Referencia sirven para buscar o examinar información almacenada en rangos, listas o tablas, devolviendo como resultado un valor asociado al que se busca, o la posición que ocupa el valor buscado dentro del rango o bien una referencia a una cierta celda o grupo de celdas del rango.
BUSCARV
Busca un valor específico en la columna más a la izquierda de una matriz y devuelve el valor en la misma fila de una columna especificada en la tabla. Se debe utilizar BUSCARV en lugar de BUSCARH cuando los valores de comparación se encuentren en una columna situada a la izquierda de los datos que se desea encontrar.
Sintaxis
BUSCARV(valor_buscado;matriz_buscar_en;indicador_columnas;ordenado)
Valor_buscado es el valor que se busca en la primera columna de la matriz. Valor_buscado puede ser un valor, una referencia o una cadena de texto.
Matriz_buscar_en es el conjunto de información donde se buscan los datos. Se puede utilizar una referencia a un rango o el nombre de un rango como, por ejemplo, Base_de_datos o Lista.
Si el argumento ordenado es VERDADERO, los valores de la primera columna del argumento ma-triz_buscar_en deben colocarse en orden ascendente (…; -2;-1;0;1;2; ... ; A-Z; FALSO; VERDADERO) caso contrario, BUSCARV podría devolver un valor incorrecto.
Para colocar los valores en orden ascendente, se debe elegir el comando Ordenar de la ficha Datos y se-leccionar la opción Ascendente.
Los valores de la primera columna de matriz_buscar_en pueden ser texto, números o valores lógicos.
El texto escrito en mayúsculas y minúsculas es equivalente.
Indicador_columnas es el número de columna de matriz_buscar_en desde la cual debe devolverse el valor coincidente. Si el argumento indicador_columnas es igual a 1, la función devuelve el valor de la primera columna del argumento matriz_buscar_en; si el argumento indicador_columnas es igual a 2, devuelve el valor de la segunda columna de matriz_buscar_en y así sucesivamente. Si indicador_columnas es menor que 1, BUSCARV devuelve el valor de error #¡VALOR!; si indicador_columnas es mayor que el número de columnas de matriz_buscar_en, BUSCARV devuelve el valor de error #iREF!
Ordenado es un valor lógico que indica si se desea que la función BUSCARV busque un valor igual o apro-ximado al valor especificado. Si el argumento ordenado es VERDADERO o se omite, la función devuelve un valor aproximado, es decir, si no encuentra un valor exacto, devolverá el valor inmediatamente menor que valor_buscado. Si ordenado es FALSO, BUSCARV devuelve el valor buscado. Si no encuentra ningún valor, devuelve el valor de error #N/A.
Si BUSCARV no puede encontrar valor_buscado y ordenado es VERDADERO, utiliza el valor más grande que sea menor o igual que el valor_buscado.
Si valor_buscado es menor que el menor valor de la primera columna de matriz_buscar_en, BUSCARV devuelve el valor de error #N/A.
Si BUSCARV no puede encontrar valor_buscado y ordenado es FALSO, devuelve el valor de error #N/A.
Ejemplos
Supuesta la siguiente hoja de calculo, donde el rango A2:C6 se denomina RangoDatos
A B C
1 Densidad Viscosidad Temperatura 2 0,457 3,55 500 3 0,525 3,25 400 4 0,616 2,93 300 5 0,675 2,75 250 6 0,746 2,57 200
BUSCARV(0,746;RangoDados;3;FALSO) es igual a 200
Excel 2007
Página 39
BUSCARV(0,745;RangoDados;3;VERDADERO) es igual a 250
BUSCARV(0,1;RangoDados;2;VERDADERO) es igual a #N/A debido a que 0,1 es menor que el menor valor de la columna A.
BUSCARH
Busca un valor en la fila superior de una tabla o una matriz de valores y, a continuación, devuelve un valor en la misma columna de una fila especificada en la tabla o en la matriz.
Sintaxis
BUSCARH(valor_buscado;matriz_buscar_en;indicador_filas;ordenado)
Valor_buscado es el valor que se busca en la primera fila de matriz_buscar_en. Puede ser un valor, una referencia o una cadena de texto.
Matriz_buscar_en es una tabla de información en la que se buscan los datos. Se puede utilizar una referen-cia a un rango o el nombre de un rango.
Los valores de la primera fila del argumento matriz_buscar_en pueden ser texto, números o valores lógicos. Si el argumento ordenado es VERDADERO, los valores de la primera fila del argumento matriz_buscar_en deberán colocarse en orden ascendente:...-2;-1;0;1;2; ..., A-Z, FALSO, VERDADERO; de lo contrario, es posible que BUSCARH no devuelva el valor correcto.
El texto en mayúsculas y minúsculas es equivalente. Se pueden poner los datos en orden ascendente de izquierda a derecha seleccionando los valores y eligiendo el comando Ordenar de la ficha Datos.
Indicador_filas es el número de fila en matriz_buscar_en desde el cual se deberá devolver el valor coinci-dente. Si indicador_filas es 1, devuelve el valor de la primera fila en matriz_buscar_en; si indicador_filas es 2, devuelve el valor de la segunda fila en matriz_buscar_en y así sucesivamente. Si indicador_filas es me-nor que 1, BUSCARH devuelve el valor de error #¡VALORI; si indicador_filas es mayor que el número de filas en matriz_buscar_en, BUSCARH devuelve el valor de error #¡REF!
Ordenado es un valor lógico que especifica si se desea que el elemento buscado por la función BUSCARH coincida exacta o aproximadamente. Si ordenado es VERDADERO o se omite, la función devuelve un valor aproximado, es decir, si no se encuentra un valor exacto, se devuelve el mayor valor que sea menor que el argumento valor_buscado. Si ordenado es FALSO, la función BUSCARH encontrará el valor exacto. Si no se encuentra dicho valor, devuelve el valor de error #N/A.
Si BUSCARH no logra encontrar valor_buscado, utiliza el mayor valor que sea menor que valor_buscado. Si valor_buscado es menor que el menor valor de la primera fila de matriz_buscar_en, BUSCARH devuelve el valor de error #N/A...
Ejemplos
Supongamos que en una hoja se guarda un inventario de repuestos, según la siguiente tabla:
A B C
1 Ejes Cojinetes Engranajes 2 4 4 9 3 5 7 10 4 6 8 11
BUSCARH("Ejes"; A1:C4;2;VERDADERO) es igual a 5
BUSCARH("Cojinetes";A1 :C4;3;VERDADERO) es igual a 8
BUSCARH("Engranajes";A1:C4;4;) es igual a 11
Matriz_buscar_en también puede ser una constante matricial:
BUSCARH(3;{1;2;3\"a";"b";"c"\"d";"e";"f'};2;VERDADERO) es igual a "c"
COINCIDIR
Excel 2007
Página 40
Devuelve la posición relativa de un elemento en una matriz que coincida con un orden especificado. Se debe utilizar COINCIDIR en lugar de las funciones BUSCAR cuando se necesite conocer la posición de un elemento en un rango, en lugar del elemento en sí.
Sintaxis
COINCIDIR(valor_buscado;matriz_buscada;tipo_de_coincidencia)
Valor_buscado es el valor que se usa para encontrar el valor deseado en la tabla.
Valor_buscado es el valor que desea hacer coincidir en la matriz_buscada. Por ejemplo, al buscar algún número en la guía telefónica, se estará usando el nombre de la persona como valor de búsqueda, pero el valor que realmente se desea es el número de teléfono.
Valor_buscado puede ser un valor (número, texto o valor lógico) o una referencia de celda a un número, a un texto o a un valor lógico.
Matriz_buscada es un rango múltiple de celdas que contienen posibles valores a buscar. Puede ser una matriz o una referencia matricial.
Tipo_de_coincidencia es el número -1, 0 ó 1.
Si tipo_de_coincidencia es 1, COINCIDIR encuentra el mayor valor que es inferior o igual al valor_buscado.
Los valores en el argumento matriz_buscada deben colocarse en orden ascendente (...-2; -1; 0; 1; 2;...A-Z; FALSO; VERDADERO).
Si tipo_de_coincidencia es 0, COINCIDIR encuentra el primer valor que es exactamente igual al va-lor_buscado. Los valores en matriz_buscada pueden estar en cualquier orden.
Si tipo_de_coincidencia es -1, COINCIDIR encuentra el menor valor que es mayor o igual al valor_buscado.
Los valores en matriz_buscada deben colocarse en orden descendente (VERDADERO; FALSO; Z-A;...2; 1; 0; -1; -2;) y así sucesivamente.
Si se omite tipo_de_coincidencia, se supondrá que es 1.
COINCIDIR devuelve la posición del valor coincidente dentro de la matriz_buscada y no el valor en sí.
Por ejemplo:
COINCIDIR("b";{"a";"b";"c"};0) devuelve 2, la posición relativa de "b" dentro de la matriz {"a";"b";"c"}.
COINCIDIR no distingue entre mayúsculas y minúsculas cuando hace coincidir valores de texto. Si COIN-CIDlR no puede encontrar una coincidencia, devuelve el valor ·de error #N/A:
Si tipo_de_coincidencia es 0 y valor_buscado es texto, valor_buscado puede contener los caracteres como-dines asterisco (*) y signo de interrogación (?). El asterisco equivale a una secuencia de caracteres y el signo de interrogación a un único carácter.
Ejemplos
COINCIDIR(4000;A2:A8;1) es igual a 4, usando coincidencia aproximada.
COINCIDIR(2000;A2:A8;0) es igual a 2, usando coincidencia exacta.
COINCIDIR(4000;A2:A8;-1) es igual al valor de error #N/A debido a que el rango A2:A8 está ordenado incorrectamente para el tipo_de_coincidencia (-1). El orden debe ser descendente para que sea correcto.
INDICE
A 1 Importe 2 1000 3 2000 4 3000 5 4000 6 5000 7 6000 8 7000
Excel 2007
Página 41
Devuelve un valor o la referencia a un valor en una tabla o rango. La función INDICE tiene dos formas: ma-tricial y referencial. La forma matricial siempre devuelve un valor o una matriz de valores y la forma de refe-rencia siempre devuelve una referencia.
Sintaxis Forma matricial
INDICE(matriz;núm_fila;núm_columna)
Matriz es un rango de celdas o una matriz de constantes.
Núm_fila selecciona, en el rango matriz, la fila desde la cual se devolverá un valor. Si se omite núm_fila, se requiere el argumento núm_columna.
Núm_columna selecciona, en el rango matriz, la columna desde la cual se devolverá un valor. Si se omite núm_columna, se requiere el argumento núm_fila.
Si se utilizan ambos argumentos núm_fila y núm_columna, INDICE devuelve el valor en la celda de inter-sección de los argumentos núm_fiIa y núm_columna.
Si matriz contiene sólo una fila o columna, el argumento núm_fila o núm_columna que corresponde es op-cional.
Si matriz tiene más de una fila y más de una columna y sólo utiliza núm_fila o núm_columna, INDICE de-vuelve una matriz con toda una fila o columna.
Si se define núm_fiIa o núm_columna como 0, INDICE devuelve la matriz de valores de toda la columna o fila, respectivamente. Si se desea utilizar valores devueltos en forma de matriz, se debe introducir la función INDICE como una fórmula matricial en un rango horizontal de celdas. Para introducir una fórmula matricial, pulsar Ctrl+Mayúsculas+lntro.
Los argumentos núm_fila y núm_columna deben indicar una celda contenida en matriz; de lo contrario, IN-DICE devuelve el valor de error #iREF!
Ejemplos
INDICE({1;2\3;4};2;2) es igual a 4, si se introduce de forma referencial.
INDICE({1;2\3;4};0;2) es igual a {2/4}, si se introduce como una formula matricial ocupando dos celdas.
Si las celdas B5:B6 contienen el texto Manzanas y Bananas y las celdas C5:C6 el texto Limones y Peras respectivamente, entonces:
INDICE(B5:C6;2;2) es igual a Peras
INDICE(B5:C6;2;1) es igual a Bananas
Sintaxis Forma de referencia
INDICE(ref;núm_fila;núm_columna;núm_área)
Ref selecciona un rango en el argumento ref desde el cual se devolverá la intersección de núm_fila y núm columna.
Si se introduce una selección no adyacente como argumento ref, hay que escribirla entre paréntesis.
Si cada área del argumento ref contiene una sola fila o columna, el argumento núm_fila o núm_columna respectivamente, es opcional.
Núm_fila es el número de·la fila en el argumento ref desde la cual se devolverá una referencia,
Núm_columna es el número de la columna en el argumento ref desde la cual se devolverá una referencia.
Núm_área selecciona un rango en el argumento ref desde el cual se devolverá la intersección de núm_fila y núm_columna. La primera área seleccionada o introducida se numera con 1, la segunda con 2 y así sucesi-vamente. Si se omite núm_área, INDICE usa área 1.
Por ejemplo, si ref describe las celdas (A1:B4;D1:E4;G1:H4), entonces núm_área 1 es el rango A1 :B4, núm_área 2 será el rango D1:E4, y núm_área 3 es el rango G1:H4.
Después de que ref y núm_área hayan seleccionado un rango determinado, núm_fila y núm_columna se-leccionan una celda específica: núm_fiIa 1 es la primera fila del rango, núm_columna 1 es la primera colum-
Excel 2007
Página 42
na y así sucesivamente. La referencia devuelta por INDICE es la intersección entre núm_fila y núm colum-na.
Si se define núm_fila o núm_columna como 0, INDICE devuelve la referencia de toda la fila o columna, se-gún corresponda.
Núm_fila, núm_columna y núm_área deberán dirigirse a una celda en ref, de lo contrario la función INDICE devuelve el valor de error #¡REF! Si núm_fila y núm_columna se omiten, INDICE devuelve el área del ar-gumento ref definido por núm_área.
El resultado de la función INDICE es una referencia y será interpretada como tal por otras fórmulas. El valor devuelto por la función INDICE se puede utilizar como una referencia o como un valor, dependiendo de la fórmula. Por ejemplo, la fórmula CELDA("ancho";INDICE(A1:B2;1;2)) es igual a CELDA("ancho";B1). La función CELDA utiliza el valor devuelto por INDICE como referencia a una celda. Por otra parte, una fórmula como 2*INDICE(A1:B2;1;2) traduce el valor devuelto por INDICE en el número de la celda B1.
Ejemplos
En la siguiente hoja de cálculo, el rango A2:C6 se denomina Fruta, el rango A7:C10 se denomina Fru-tosSecos y el rango C21:C11 se denomina Existencias.
INDICE(Frutas;2;3) es igual a la referencia C3, que con-tiene 38.
INDICE((A1:C6;A8:C11);2;2;2) es igual a la referencia B9, que contiene 3,55
SUMA(INDICE(Existencias;0;3;1)) es igual a SUMA(C1:C11), cuyo resultado es 218.
SUMA(B2:INDICE(Frutas;5;2)) es igual a SUMA(B2:B6), cuyo resultado es 2,42
A B C 1 Frutas Precio Existencias 2 Manzanas 0,69 40 3 Plátanos 0,34 38 4 Limones 0,55 15 5 Naranjas 0,25 25 6 Peras 0,59 40 7 Frutos secos
8 Almendras 2,80 10 9 Anacardos 3,55 18 10 Cacahuetes 1,25 20 11 Nueces 1,75 12
Excel 2007
Página 43
FUNCIONES MATEMÁTICAS Y TRIGONOMÉTRICAS En esta categoría se halla un amplio conjunto de funciones que permiten ejecutar cálculos aritméticos y matemáticos muy variados, desde simples sumas a cálculos con números complejos, pasando por funcio-nes para el redondeo de números, funciones logarítmicas, funciones trigonométricas, etc.
SUMA
Suma todos los números de un rango.
Sintaxis
SUMA(número1 ;número2; ... )
Número 1; número2; ... son entre 1 y 255 números o referencias a celdas o rangos cuya suma se desea obtener.
Se toman en cuenta números, valores lógicos y representaciones de números escritos directamente en la lista de argumentos.
Si un argumento es una matriz o una referencia, solamente se contarán los números de esa matriz o refe-rencia. Se pasan por alto las celdas vacías, valores lógicos, texto o valores de error en esa matriz o referen-cia.
Los argumentos que sean valores de error o texto que no se pueda traducir a números causarán errores.
Ejemplos
SUMA(3; 2) es igual a 5 SUMA("3"; 2; VERDADERO) es igual a 6, ya que los valores de texto se tradu-cen a números y el valor lógico VERDADERO se traduce como 1.
A diferencia del ejemplo anterior, si A1 contiene "3" y B1 contiene VERDADERO, entonces:
SUMA(A1; B1; 2) es igual a 2, ya que las referencias a valores no numéricos de las referencias no se traducen.
Si las celdas A2:E2 contienen 5; 15; 30; 40 Y 50, entonces:
SUMA(A2:C2) es igual a 50 y SUMA(B2:E2; 15) es igual a 150
SUMAR.SI
Suma las celdas en el rango que coinciden con el argumento criterio. Es una extensión de la función SUMA
Sintaxis
SUMAR.SI(rango;criterio;rango_suma)
Rango es el rango de celdas que se desea evaluar.
Criterio es el criterio en forma de número, expresión o texto, que determina qué celdas se van a sumar. Por
ejemplo, el argumento criterio puede expresarse como 55; "55"; ">55"; "Sevilla".
Rango_suma son las celdas que se van a sumar. Las celdas contenidas en rango_suma se suman sólo si las celdas correspondientes del rango coinciden con el criterio Si rango_suma se omite, se suman las cel-das contenidas en el argumento rango.
Ejemplo
Supongamos que el rango A1 :A4 contiene los siguientes valores de propiedad para cuatro casas: 100.000, 200.000, 300.000 y 400.000 respectivamente. El rango B1 :B4 contiene las siguientes comisio-nes de venta correspondientes a estos valores de propiedad: 7.000, 14.000, 21.000 y 28.000.
SUMAR.SI(A1:A4;">160000";B1:B4) es igual a 63000.
SUMAR.SI.CONJUNTO
Suma los valores de las celdas que cumplen un determinado conjunto de condiciones. Es una extensión o ampliación de SUMAR.SI que en lugar de un solo criterio admite múltiples criterios.
Sintaxis
SUMAR.SI.CONJUNTO(rango_suma,rango_criterios1,criterio1;[rango_criterios2;criterio2];…)
Excel 2007
Página 44
Rango_suma es una o más celdas para sumar, incluidos números o nombres, rangos o referencias de celda que contienen números. Se omiten los valores en blanco o de texto.
Rango_criterios1, Rango_criterios2,…Son los rangos dentro de los cuales se compara el criterio correspon-diente para ver que celdas del rango correspondiente lo cumplen.
Criterio1, Criterio2,… son los criterios en forma de número, texto o fecha que determinan las celdas que se incluirán en la suma.
Esta función admite múltiples criterios a la vez, pero sobre campos o columnas distintas. Para aplicar múlti-ples criterios sobre un mismo campo debemos usa la función de tipo “Bases de datos”, BDSUMA.
Ejemplos
Supongamos la siguiente tabla de clientes y ventas:
A B C D E F 1 Clientes Zona Ciudad Mes Cantidad Importe 2 EL CORTE INGLES Centro Madrid 9 100 1000 3 IKEA Catalunya Barcelona 9 50 900 4 MUEBLES LA FABRICA Centro Madrid 11 25 1200 5 OFIMUEBLE Catalunya Barcelona 5 100 1800 6 LA CASA DE LAS LAMPARAS Andalucía Sevilla 6 120 2000 7 IKEA Andalucía Sevilla 9 50 1900 8 TODO HOGAR Catalunya Barcelona 9 100 800 9 NOVALINEA Aragón Zaragoza 8 200 1200
10 EL CORTE INGLES Aragón Zaragoza 3 120 1300 11 OFIMUEBLE Catalunya Barcelona 2 100 1200
Si quisiéramos hallar el importe total de ventas a clientes para clientes de Barcelona a los que se les facturo menos de 1000 €, escribiríamos la función así:
SUMAR.SI.CONJUNTO(F6:F15;C6:C15;"Barcelona";F6:F15;"<1000")
ENTERO
Redondea un número hasta el entero inferior más próximo.
Sintaxis
ENTERO(número)
Número es el número real que desea redondear al entero inferior más próximo.
Ejemplos
ENTERO(8,9) es igual a 8 y ENTERO(-8,9) es igual a -9
REDONDEAR
Redondea un número al número de decimales especificado. Sintaxis
REDONDEAR(número;núm_de_decimales)
Número es el número que se desea redondear.
Núm_de_decímales especifica el número de dígitos al que se desea redondear el argumento número. Si el argumento núm_de_decímales es mayor que 0, número se redondeará al número de lugares decimales especificado. Si el argumento núm_de_decímales es 0, número se redondeará al entero más próximo Si el argumento núm_de_decímales es menor que 0, número se redondeará hacia la izquierda del separador decimal.
Existen varias funciones, extensiones especializadas de esta, y son; REDONDERAR.IMPAR, REDON-DEAR.PAR, REDONDEAR.MAS y REDONDEAR.MENOS.
Ejemplos
REDONDEAR(2,15; 1) es igual a 2,2.
Excel 2007
Página 45
REDONDEAR(2,149; 1) es igual a 2,1.
REDONDEAR(-1 ,475; 2) es igual a -1,48.
REDONDEAR(21 ,5; -1) es igual a 20.
TRUNCAR
Trunca un número a un entero, suprimiendo la parte fraccionaria de dicho número.
Sintaxis
TRUNCAR(número; núm_de_decimales)
Número es el número que se desea truncar.
Núm_de_decímales es un número que especifica la precisión al truncar. El valor predeterminado del argu-mento núm_de_decímales es 0. TRUNCAR y ENTERO son similares, ya que ambos devuelven enteros. TRUNCAR suprime la parte fraccionaria del número. ENTERO redondea los números al entero menor más próximo, según el valor de la porción fraccionaria del número. ENTERO y TRUNCAR son diferentes sola-mente cuando se usan números negativos: TRUNCAR(-4,3) devuelve -4, pero ENTERO(-4,3) devuelve -5, ya que -5 es el número entero menor más cercano.
Ejemplos
TRUNCAR(8,9) es igual a 8. TRUNCAR(-8,9) es igual a -8. TRUNCAR(PI()) es igual a 3.
DECIMAL
Redondea un número al numero especificado de decimales, dando formato al número usando puntos y co-mas y retornando el resultado como texto.
Sintaxis
DECIMAL(número; decimales, no_separar_millares)
Número es el número que se desea redondear y convertir en texto
Decimales es el número de dígitos a la derecha del separador decimal.
No_separar_millares determina si se incluye o no el separador de miles en el resultado textual que retorna. Es un valor lógico, que si es VERDADERO no incluirá el separador de miles en el resultado.
La principal diferencia entre dar formato a una celda que contiene un número con un comando (en ficha Inicio, en el grupo Número) y dar formato a un número directamente con la función DECIMAL es que DE-CIMAL convierte el resultado en texto. Un número que recibe formato con el comando CELDAS sigue sien-do un número.
Dado que el resultado de esta función es texto, no se podrá usar dentro de funciones que requieran argu-mentos numéricos.
ABS
Devuelve el valor absoluto de un número. El valor absoluto de un número es el número sin su signo.
Sintaxis
ABS(número)
Número es el número real cuyo valor absoluto desea obtener.
Ejemplos
ABS(2) es igual a 2
ABS(-2) es igual a 2
Si A1 contiene -16, entonces: RAIZ(ABS(A1)) es igual a 4
SUBTOTALES
Devuelve un subtotal en una lista o base de datos, aunque generalmente es más fácil crear una lista con subtotales utilizando el comando Subtotal de la ficha Datos, grupo Esquema. Las función SUBTOTALES engloba dentro de si once funciones diferentes con dos posibilidades cada una de ellas
Excel 2007
Página 46
Sintaxis
SUBTOTALES(núm_función;ref1)
Núm_función es un número de 1 a 11 (incluye valores ocultos) o de 101 a 111 (no incluye valores ocultos) que indica qué función debe ser utilizada para calcular los subtotales dentro de una lista.
Núm_función (incluye valores ocultos)
Núm_función (no incluye valores ocultos)
Función
1 101 PROMEDIO 2 102 CONTAR 3 103 CONTARA 4 104 MAX 5 105 MIN 6 106 PRODUCTO 7 107 DESVEST 8 108 DESVESTP 9 109 SUMA
10 110 VAR 11 111 VARP
Ref1 es el rango o referencia para el cual se desea calcular los subtotales.
Si hay otros subtotales dentro de ref1 (o subtotales anidados), estos subtotales anidados se pasarán por alto para no repetir los cálculos.
La función SUBTOTALES pasa por alto las filas ocultas que no se hayan incluido en el resultado de un filtro, independientemente del valor de núm_función que se use, es dicir que las dos posibilidades de cada fun-cion daran igual sobre el conjunto de registros resultante de un Filtro o Filtro avnzado, en ambos casos solo incluiran en el subtotal los registros visibles.
Si alguna de las referencias es una referencia 3D, SUBTOTALES devolverá el valor de error #¡VALOR!.
Ejemplo
SUBTOTALES (9;C3:C5) producirá un subtotal de las celdas C3:C5 utilizando la función SUMA.
FUNCIONES DE TEXTO Las funciones de esta categoría o bien manipulan cadenas de texto o bien convierten entradas de texto a valores numéricos o entradas numéricas a valores de texto. La mayor de ellas toman como argumento un texto y nos permiten operaciones como buscar, reemplazar o eliminar caracteres dentro del mismo, conver-tirlo a mayúsculas o minúsculas, contar su longitud en caracteres, eliminar espacios o caracteres no impri-mibles, etc.
CONCATENAR
Concatena argumentos de texto.
Sintaxis
CONCATENAR(texto1, texto2;….)
Texto1,texto2,… son de 1 a 225 elementos de teso que se unirán en un elemento de texto único. Los ele-mentos de texto pueden ser cadenas de texto, números o referencias a celdas individuales.
Se puede realizar la misma operación de unión de elementos de texto usando el operador de concatenación "&" que es totalmente equivalente.
Ejemplo
Suponiendo tuviésemos una base de datos sobre empleados según la siguiente tabla
A B C
Excel 2007
Página 47
1 Apellidos Nombre Concatenado 2 Abellán Bermejo Sonia Sra/Srta. Sonia, Abellán Bermejo 3 Barragán Peréz Laura Sra/Srta. Laura, Barragán Peréz 4 Cerqueda Pino Milagros Sra/Srta. Milagros, Cerqueda Pino 5 Chimeno Casas Cristina Sra/Srta. Cristina, Chimeno Casas 6 Díaz Fernández Mirian Sra/Srta. Mirian, Días Fernández
Y suponiendo que quisiéramos concatenar en la columna C, primero el nombre y después el apellido con un calificativo inicial deberíamos introducir de C2 a C6 la siguiente formula:
=CONCATENAR("Sra/Srta. ";B2;", ";A2)
Para producir el resultado que aparece en la columna C de la tabla
IZQUIERDA
Extrae el carácter o los caracteres situados en el extremo izquierdo de una cadena de texto.
Sintaxis
IZQUIERDA(texto;núm_de_caracteres)
Texto es la cadena de texto que contiene los caracteres que desea extraer.
Núm_de_caracteres especifica cuántos caracteres desea que devuelva, debe ser mayor o igual a 0. Si núm_de_caracteres es mayor que la longitud del texto, IZQUIERDA devuelve todo el texto. Si núm_de_caracteres se omite, el valor predeterminado es 1.
Ejemplos
IZQUIERDA("Precio de venta"; 6) es igual a "Precio"
Si A1 contiene "Suecia", entonces: IZQUIERDA (A1;1) es igual a "S"
DERECHA
Devuelve el último carácter o caracteres del extremo derecho de una cadena de texto.
Sintaxis
DERECHA(texto;núm_de_caracteres)
Texto es la cadena de texto que contiene los caracteres que se desea extraer.
Núm_de_caracteres especifica el número de caracteres que se desea extraer, debe ser igualo mayor que o. Si el argumento núm_de_caracteres es mayor que la longitud de texto, DERECHA devolverá todo el texto. Si el argumento núm_de_caracteres se omite, se asume que es 1.
Ejemplos
DERECHA("Precio Venta"; 7) es igual a "Venta"
DERECHA("Número Inventario") es igual a "o"
Excel 2007
Página 48
EXTRAE
Devuelve un número específico de caracteres de una cadena de texto, comenzando en la posición que es-pecifique.
Sintaxis
EXTRAE(texto;posición_inicial;núm_de_caracteres)
Texto es la cadena de texto que contiene los caracteres que se desea extraer.
Posición_inicial es la posición del primer carácter que se desea extraer del argumento texto. La posi-ción_inicial del primer carácter de texto es 1 y así sucesivamente.
Si posición_inicial es mayor que la longitud de texto, EXTRAE devuelve texto vacío.
Si posición_inicial es menor que la longitud de texto, pero posición_inicial más núm_de_caracteres excede la longitud de texto, EXTRAE devuelve los caracteres hasta el final de texto. Si posición_inicial es menor que 1, EXTRAE devuelve el valor de error #¡VALOR!
Núm_de_caracteres especifica el número de caracteres de texto que se han de devolver. Si núm_de_ ca-racteres es negativo, EXTRAE devuelve el valor de error #¡VALOR!
Ejemplos
EXTRAE("Flujo de liquido"; 1; 5) es igual a "Flujo"
EXTRAE("Flujo de liquido"; 7; 20) es igual a "de líquido"
EXTRAE("1234"; 5; 5) es igual a .... (texto vacío)
IGUAL
Compara dos cadenas de texto y devuelve VERDADERO si son exactamente iguales y FALSO si no lo son. IGUAL reconoce mayúsculas y minúsculas, pero pasa por alto las diferencias de formato
Sintaxis
IGUAL(texto1;texto2)
Texto1 es la primera cadena de texto, entre comillas.
Texto2 es la segunda cadena de texto, entre comillas.
Ejemplos
IGUAL("palabra";"palabra") es igual a VERDADERO
IGUAL("Palabra";"palabra") es igual a FALSO
IGUAL("p alabra";"palabra") es igual a FALSO.
Excel 2007
Página 49
FUNCIONES DE FECHA Y HORA Excel almacena las fechas como números de serie secuenciales para que puedan utilizarse en los cálculos. De forma predeterminada, Excel para Windows emplea el sistema de fechas 1900, en el cual los números de serie corresponden a fechas comprendidas entre el 1 de enero de 1900 y el 31 de diciembre de 9999, así pues el 1 de enero de 1900 es el número de serie 1 y el 10 de diciembre de 2009 equivale al número de serie 40157, porque viene 40.157 días después del 1 de enero de 1900.
En los números de serie, los dígitos a la derecha del separador decimal representan la hora y los números a la izquierda representan la fecha. Por ejemplo, en el sistema de fechas 1900, el número de serie 40157,5 representa la combinación de fecha y hora 10 de diciembre de 2009, 12:00 PM.
El sistema de fechas cambia automáticamente cuando se abre un documento creado en otra plataforma. Por ejemplo, si se está trabajando en Excel para Windows y se abre un documento creado en Excel para Macintosh, la casilla de verificación Usar sistema de fechas 1904, de Botón de Office > Opciones de Excel > Avanzadas > Al calcular este libro, se activará automáticamente.
La mayoría de las funciones convierten automáticamente los valores de fecha en números de serie.
Las funciones de fecha y hora permiten hacer cálculos precisos y rápidos sobre contajes de tiempo.
HOY
Devuelve la fecha actual con formato de fecha. El número de serie es el código de fecha/hora que Excel usa para los cálculos de fecha y hora.
Sintaxis
HOY( )
AHORA
Devuelve el número de serie de la fecha y hora actuales. Si el formato de celda era General antes de escri-bir la función, el resultado tendrá formato de fecha.
Sintaxis
AHORA( )
Esta función sólo cambia cuando se realiza un cálculo en la hoja de cálculo o cuando se ejecuta una macro que contiene la función. No se actualiza constantemente.
Ejemplo
Si se está usando el sistema de fechas 1900 y el reloj de nuestro equipo está ajustado a las 12:30 del día 10 de diciembre de 2009:
AHORA() es igual a 10/12/2009 12:30
FECHA
Devuelve el número de serie de una fecha especificada.
Sintaxis
FECHA(año;mes;día)
Año es un número entre 1900 y 9999 en Excel para Windows o entre 1904 y 9999 en Excel para Macintosh. Mes es un número que representa el mes del año. Si mes es mayor que 12, el argumento mes agrega dicho número de meses al primer mes del año especificado.
Día es un número que representa el día del mes. Si día es mayor que el número de días en el mes especifi-cado, el argumento día agrega dicho número de días al primer día del mes.
La función FECHA es muy útil en las fórmulas donde año, mes y día son fórmulas, no constantes.
Ejemplo
FECHA(2009;11 ;10) es igual a 39412, es decir, el número de serie que corresponde a la fecha 10 de no-viembre de 2009.
NSHORA
Excel 2007
Página 50
Devuelve el número de serie para una hora determinada. El número de serie devuelto por NSHORA es una fracción decimal entre O y 0,99999999 que representa las horas entre 0:00:00 (12:00:00 AM) y 23:59:59
(11 :59:59 PM).
Sintaxis
NSHORA(hora;minuto;segundo)
Hora es un número entre 0 y 23 que representa las horas.
Minuto es un número entre 0 y 59 que representa los minutos.
Segundo es un número entre 0 y 59 que representa los segundos.
Ejemplo
NSHORA(12;0;0) es igual al número de serie 0,5, que es equivalente a 12:00:00 PM
Funciones de extracción de parte de fecha/hora
Existen un conjunto de funciones muy similares en sintaxis y funcionalidad que sirven para extraer u obtener una parte concreta de una fecha u hora. El resultado que retornan se debe interpretar como un número, que en la mayoría de las ocasiones sirve como base para algún calculo sobre tiempo.
Sintaxis
Nombre_Funcion(núm_de_serie)
Nombre_Funcion puede ser AÑO, MES, DIA, HORA, MINUTO o SEGUNDO
núm_de_serie puede proporcionarse como texto, por ejemplo, "10-dic-2009" o "10-12-09", en lugar de como número. El texto se convierte automáticamente en un número de serie.
Las funciones AÑO, MES y DIA retornan la parte correspondiente de la fecha como un número entero
Las funciones HORA, MINUTO Y SEGUNDO retornan la parte correspondiente de la fecha como un número decimal, sin parte entera.
Ejemplos:
AÑO("10-12-09') es igual a 2009. AÑO(40157) es igual a 2009
AÑO(A1) es igual a 2009, si el valor de la celda A1 es una fecha comprendida entre el1 de enero y el 31 de diciembre del año 2009.
MES("10-noviembre") es igual a 11
DIA("6-feb") es igual a 6 . DIA("10-dic-2009") es igual a 10.
HORA(0,7) es igual a 16. HORA("3:30:30 PM") es igual a 15
MINUTO("4:48:00 PM") es igual a 48
SEGUNDO("4:48: 18 PM") es igual a 18
DIASEM
Devuelve el día de la semana correspondiente al argumento núm_de_serie. El día se devuelve como un número entero entre 1 (domingo) y 7 (sábado).
Sintaxis
DIASEM(núm_de_serie;tipo)
Núm_de_serie es el código de fecha-hora que Excel usa para los cálculos de fecha y hora. El argumento núm_de_serie puede proporcionarse como texto, por ejemplo, "10-nov-2009" o "10-11-09", en lugar de co-mo número. El texto se convierte automáticamente en un número de serie.
Tipo es un número que determina que tipo de valor debe ser devuelto.
1 u omitido Números del 1 (domingo) al 7 (sábado)
2 Números del 1 (lunes) al 7 (domingo)
Excel 2007
Página 51
3 Números del 0 (lunes) al 6 (domingo)
También puede usarse la función TEXTO para convertir un valor en un formato de número especificado usando el sistema de fechas 1900:
TEXTO("10-11-09";"dddd") es igual a martes
Ejemplos
=DIASEM("27-11-09") es igual a 6 (domingo)
FECHANUMERO
Devuelve el número de serie de la fecha representada por texto_de_fecha.
Sintaxis
FECHANUMERO(texto_de_fecha)
Texto_de_fecha es el texto que devuelve una fecha en un formato de fecha de Excel. Si se usa el sistema de fecha predeterminado de Excel para Windows, el argumento texto_de_fecha debe representar una fecha desde el1 de enero de 1900 hasta el 31 de diciembre del año 9999. Devuelve el valor de error #¡VALORI si texto_de_fecha no está comprendido en ese rango.
Si se omite la parte texto_de_fecha correspondiente al año, se usa el año corriente del reloj integrado en el PC. La información de hora de texto_de_fecha se pasa por alto.
Ejemplo
FECHANUMERO("6-feb") es igual a 39850. Siendo el 6 de febrero de 2009
HORANUMERO
Devuelve el número de serie de la hora representada por texto_de_hora. El número de serie es una fracción decimal entre 0 y 0,99999999 que representa las horas entre 0:00:00 (12:00:00 AM) y 23:59:59 (11 :59:59 PM).
Sintaxis
HORANUMERO(texto_de_hora)
Texto_de_hora es una cadena de texto entre comillas que devuelve una hora en uno de los formatos hora-rios de Excel. La información de fecha en el argumento texto_de_hora se pasa por alto.
Ejemplos
HORANUMERO("2:24 AM") es igual a 0,1
HORANUMERO("10-nov-07 12:30") es igual a 0,520833333335759
FUNCIONES DE BASES DE DATOS Véase el apartado del mismo nombre en el tema "BASES DE DATOS"
Excel 2007
Página 52
BASES DE DATOS
Una de las funcionalidades de Excel es manejar listas o tablas de información de cualquier tipo: nombres, direcciones, teléfonos, salarios, importes, etc..Excel puede trabajar con listas, que se componen de una sola columna o con tablas o bases de datos que se componen de varias columnas de información.
Una base de datos en Excel es por tanto o bien una Tabla o bien un grupo de columnas que almacenan un conjunto de información homogénea organizada de una forma estructurada y sistemática; un ejemplo usual es una lista de empleados o clientes con sus correspondientes datos.
La información de una base de datos se estructura en forma de registros y campos:
• Un registro o fila representa el conjunto de la información relativa a cada uno de los elementos indi-viduales de una base de datos: en una base de datos de empleados la información de cada uno de los empleados representaría un registro.
• Un campo, representa cada uno de los fragmentos individuales de información que componen un registro. Cada empleado de la base de datos está definido por su nombre, su dirección, su número telefónico, el departamento en el que trabaja; cada uno de estos elementos constituye un campo.
Las bases de datos en Excel se construyen introduciendo cada registro en una fila de una hoja de calculo. Una base de datos solo puede ocupar una única hoja de calculo. Excel no admite o permite trabajar con Bases de datos Relacionales, que son bases de datos en las cuales la información se puede almacenar en varias tablas o ficheros con campos relacionados, lo cual evita la redundancia (duplicación) de información. Para trabajar con bases de datos Relacionales debemos usar Microsoft Access u otro programa similar.
A las bases de datos que se guardan en un único fichero u hoja de calculo se les denomina “bases de datos planas” y sus funcionalidades son mas limitadas que las de tipo Relacional
Dentro de cada hoja de calculo se pueden crear varias bases de datos independientes, utilizando la opción “Crear tabla”, que se detallará mas adelante.
Creación de una base de datos En el momento de la creación de una base de datos en Excel debemos tener en cuenta las siguientes con-sideraciones:
Registros: No pueden ocupar mas de una fila de la hoja de calculo. En número máximo de registros admiti-dos viene limitado por el numero máximo de filas que puede contener una hoja de calculo, 1.048.576.
Campos: Los nombres de cada uno de los campos pueden contener como máximo 1.024 caracteres y no pueden existir dos nombres de campo iguales en la misma base de datos. El número máximo de campos se encuentra limitado a su vez por el número máximo de columnas en una hoja de cálculo,16.384
Los datos introducidos en una base de datos pueden ser de cualquier tipo, texto, números, funciones o for-mulas.
Los datos se pueden introducir en mayúsculas o minúsculas. En la mayor parte de opciones, excepto en la ordenación de datos, Excel no distingue entre ambos casos.
El inicio del área que compondrá la base de datos puede ser cualquier fila y columna, no es necesario em-pezar en la fila 1 y columna A. El área que contiene los nombres de los campos y los datos de los registros ha de ser contigua. El inicio de la base de datos lo marcan los encabezados correspondientes a los nom-bres de los campos.
Gestión de una base de datos La gestión de una base de datos comprende todos aquellas operaciones necesarias para manejar la infor-mación contenida en ella, así como su actualización, modificación, añadido o eliminación. Excel nos permite realizar de una forma sencilla las siguientes operaciones de gestión:
• Validación de datos: fijar reglas o criterios de restricción para la introducción de nuevos datos.
• Edición de datos: modificar, añadir, eliminar datos
• Ordenación de la información: según valores de uno o varios campos y siguiendo diversos criterios
Excel 2007
Página 53
• Búsqueda de registros: bajo criterios específicos para cada campo. • Filtrado o Extracción o de información: a través de diversos filtros automatizados o manuales • Creación de subtotales: usando un mecanismo automatizado. • Cálculos y filtros avanzados usando funciones de Bases de datos.
La mayor parte de estas operaciones se pueden llevar a cabo, desde la ficha Datos, principalmente en los grupos Ordenar y filtrar ,Herramientas de datos y Esquema.
Validación de datos La validación de datos es un mecanismo que nos facilita Excel para minimizar al máximo la posibilidad de cometer errores durante la introducción de datos en las celdas.
En muchas ocasiones se deben introducir datos que han de cumplir unos criterios o siguen un cierto patrón, por ejemplo podemos tener una base de datos con una columna donde se debe especificar el precio y sa-bemos que los precios deben ser números (con o sin decimales) y que están en un rango de valores posi-bles, además de ser evidentemente números positivos, o podemos tener una columna de fechas de pedi-dos, donde la fecha ha de ser una fecha del año actual, etc.
Con la validación de datos podemos seleccionar una celda o rango de celdas y especificar que tipo de datos son los permitidos en dichas celdas. Se pueden restringir los datos para que sean de uno de los tipos si-guientes: números enteros, números decimales, fechas, horas, texto, o bien solo valores seleccionables de una lista de referencia creada previamente. Así mismo se pueden definir limites a dichas entradas, por ejemplo limitar el rango numérico o de fechas aceptable, o si son entradas de texto, limitar el número de caracteres admisibles. Para especificar los limites se puede usar valores (constantes), referencias a celdas o fórmulas.
Para llevar a cabo la operación de restricción primero debemos seleccionar las celdas que deseamos res-tringir y después en la ficha Datos > Herramientas de datos > seleccionar la opción "Validación de datos" que lanza el siguiente cuadro de dialogo:
Excel 2007
Página 54
El dialogo "Validación de datos" se divide en tres fichas o pestañas: Configuración, Mensaje de entrada y Mensaje de error: Cada una de ellas con cometidos o funcionalidad especifica, pero complementaria a las otras.
Configuración Es la pestaña mas importante. Aquí es donde se debe seleccionar el tipo de dato permitido y las limitacio-nes adicionales que se pueden aplicar sobre dicho tipo. Las limitaciones son especificas para cada tipo y aparecerán cuando se seleccione el tipo. En la misma existen dos casillas de verificación:
• Omitir blancos. Determina el tratamiento de los valores en blanco (nulos), es decir el comportamien-to en el caso de querer dejar la celda vacía después de haber comenzado a introducir datos. Solo si esta casilla esta activada, nos permitirá dejar la celda en blanco.
Nota: También nos permitirá dejar la celda en blanco aunque no este habilitada la opción "Omi-tir blancos" en el caso de escribir en una celda en la que previamente no había nada, para ello basta con pulsar la tecla ESC.
• Aplicar estos cambios a otras celdas con la misma configuración: Cuando estamos modificando una regla de validación existente podemos decirle a Excel si queremos que aplique también los cambios a todas las celdas dentro de la hoja actual que tengan la misma regla de validación que la de la cel-da que estamos modificando, lo que resulta bastante útil, dado que no tenemos que seleccionar, previamente al cambio, todas las celdas afectadas, con la posibilidad además de olvidarnos alguna.
Nota: Los mensajes de validación de datos sólo se muestran al introducir manualmente los da-tos de uno en uno, es decir de celda en celda. Por tanto no se muestran al pegar valores co-piados de otra celda, aunque sea de una celda individual, ni por supuesto en copiados y pega-dos de múltiples celdas.
Mensaje de entrada Esta pestaña nos facilita una opción complementaria que consiste en hacer aparecer un mensaje descripti-vo en la celda con validación en el momento en que nos posicionamos en dicha celda para escribir. El obje-tivo es servir de guía en la introducción de datos clarificando con mas o menos detalle las condiciones que deben cumplir los datos permitidos.
Es mensaje aparece como una pequeña nota de color amarillo en la parte inferior de la celda, un poco por debajo de esta, como en el siguiente ejemplo.
Excel 2007
Página 55
Mensaje de error Después de introducir datos no permitidos en una celda con validación Excel lanza un mensaje para adver-tirnos del error, siempre que este activada la opción “Mostrar mensaje de error si se introducen datos no validos”, que se halla en esta pestaña. Si esta seleccionada esta opción, pero no hemos escrito nada en el cuadro Titulo, ni en el cuadro “Mensaje de error” Excel crea un mensaje de error genérico.
Es conveniente construir nuestro propio mensaje cuando pueda haber lugar a ambigüedad con el mensaje predeterminado o bien por que queremos ser muy específicos respecto a lo permitido y no permitido en dicha celda.
En la lista desplegable “Estilo” podemos seleccionar el tipo de mensaje o mejor dicho la severidad de la restricción. Con la opción “Grave”, la restricción es estricta, no se puede escribir en la celda ningún dato que no cumpla la regla de validación. Las dos opciones restantes, "Advertencia” e “Información” también lanzan el mensaje de error, pero Excel nos permite a voluntad propia continuar o no con los datos incorrectos, es decir nos permite aceptar casos especiales para los cuales no se debe aplicar la regla de validación. Estas dos ultimas son idénticas en funcionalidad, simplemente varia el icono del mensaje y el numero de botones, cada una de ellas se adapta mejor a ciertas situaciones.
Excel 2007
Página 56
En Validación de datos hay dos casos especiales, la validación de tipo "Lista" y la "Personalizada".
Validación de Lista La validación a través de una lista nos permitirá escribir solo un valor de los que estén en una cierta lista, cuyo rango de referencia (posición) debemos escribir el cuadro Origen al crear la regla. Una vez creada la regla, la validación funciona exactamente igual que el resto de casos anteriores, es decir nos posicionamos en la celda y escribimos el valor deseado. No obstante en este caso existe la casilla opcional "Celda con lista desplegable", que caso de activarla en cada celda, en el momento de situarnos en la misma, nos pre-sentara en la parte derecha de la celda y fuera de esta una flecha que hace aparecer la lista con todos los valores que tiene la lista Origen y en el mismo orden en el que están en dicha lista, por lo cual no tenemos por que acordarnos de ningún valor.
La Lista puede ser un rango vertical u horizontal de celdas, pero de una sola fila o columna respectivamen-te.Los datos pueden ser texto, números, fecha u horas, y en cada caso los tratara como tales cuando se escriban en la celda correspondiente.Al introducir datos no se distingue entre mayúsculas y minúsculas.
Nota: La validación tipo "lista" solo admite como rango lista un rango que este en la misma hoja donde se usará para validar. No obstante esta restricción es fácil de superar, basta con crear un rango con nombre y así la lista puede estar en cualquier otra hoja del libro o en otro libro di-ferente.
Validación Personalizada Este tipo de validación nos permite construir reglas verdaderamente potentes y sofisticadas y ajustarlas a cualquier necesidad por muy compleja o exigente que esta sea.
Una regla de validación Personalizada consiste en una formula cuyo resultado es un valor lógico. Después de haber introducido los datos se evalúa la formula, y solo en caso de dar VERDADERO, se admitirán di-chos datos escritos.
La formula puede ser tan sencilla o compleja como se quiera y se introduce de la misma forma en que se escribe una formula en una celda, es decir, primero el signo igual y después la expresión con los operado-res o funciones que sean necesarios.
Esta opción es un mecanismo muy potente y versátil, dado que por ejemplo no permite la introducción de ciertos datos, si previamente no se han escrito otros, o introducir un dato si y solo si no se ha introducido otro relacionado o viceversa, etc.
Ejemplos de reglas de validación personalizada:
Ejemplo 1: Supuesta una base de datos en la que existen entre otros los campos "Fecha pedido" y "Fecha de entrega". Se desea que nunca se admita como "Fecha de entrega" una fecha que sobrepase en mas de 30 días a "Fecha pedido" y que en ningún caso sea menor que la "Fecha de entrega". Si "Fecha pedido" estuviese por ejemplo en la columna D de nuestra base de datos, la regla para las celdas de "Fecha de entrega" seria:
Permitir Fecha Datos entre Fecha inicial: =D2 Fecha final =D2+30
Ejemplo 2: Supuesto una base de datos con, entre otros, los tres siguientes campos: "Precio Compra", en columna B, "Portes" en columna C y "Precio Venta" en columna D. Se desea controlar que los valores en
Excel 2007
Página 57
"Precio Venta" nunca sean menores que la suma de los valores "Precio Compra" + "Portes" de su misma fila. Para ello la regla de validación de las celdas de "Precio Venta" debería de ser la siguiente:
Permitir Decimal Datos: mayor o igual que Mínimo =B2+C2
Excel 2007
Página 58
Ordenación Excel provee varias herramientas para ordenar una base de datos o tabla. Todas ellas están situadas en la ficha Datos, grupo “Ordenar y filtrar”.
Si se desea ordenar de forma rápida y sencilla por cualquier campo de la base de datos, basta con situarse en cualquier registro de dicho campo y clicar en los botones “Ordenar de A a Z” para orden ascendente o “Ordenar de Z a A” para orden descendente.
Si se quiere ordenar por mas de un campo a la vez, o utilizar opciones especiales, se debe clicar el botón “Ordenar” que hará aparecer el cuadro de dialogo Ordenar, en el cual podemos poner hasta un total de 64 condiciones de ordenación.
Para cada campo, tenemos la opción de ordenarlo según una de las cuatro posibilidades existentes en el desplegable “Ordenar según”, que son: Valores (la más usual), Color de celda, Color de fuente e Icono de celda.
Para cada criterio, la lista desplegable “Criterio de ordenación” nos presentará diferentes posibilidades, que dependerán del tipo de datos del campo seleccionado. En el caso de los campos de tipo texto es particu-larmente interesante la opción “Lista personalizada…” que nos permite hacer un orden jerárquico en base a una lista personalizada que hayamos definido previamente.
Existen además algunas opciones adicionales que podemos usar. Para hacerlo basta con hacer clic en el botón “Opciones…” del cuadro de dia-logo Ordenar, lo cual hará aparecer a su vez el cuadro de dialogo "Opcio-nes de ordenación", con las siguientes posibilidades:
La principal y mas importante opción del cuadro anterior es la posibilidad de distinguir entre mayúsculas y minúsculas en la ordenación.
Ordenar de arriba hacia abajo se refiere a ordenar por registros (filas).
Ordenar de izquierda a derecha se refiere a ordenar por campos (colum-nas), es decir mueve columnas enteras a basándose en el nombre del campo.
La mayor parte de opciones de ordenación también aparecen al aplicar la opción Filtro en el desplegable que se crea en la cabecera de cada campo de la base de datos.
Filtrado o Extracción o de información Filtrar información es seleccionar un subconjunto de registros que cumplan unos determinados criterios. Al hacerlo Excel oculta el resto de registros que no cumplen los criterios y de esta forma nos permite manejar con mayor facilidad y rapidez los datos que nos interesan que son solo una parte de los datos totales.
Existen dos opciones para el filtrado de información: Filtro y Filtro Avanzado
Excel 2007
Página 59
Filtro Para poder usar el "Filtro" o "Autofiltro" basta ponerse en cualquier celda de un rango de datos y Excel se encargara de seleccionar todo el rango continuo y poner en la primera fila de cada campo del rango un des-plegable. Dicho desplegable es similar pero con opciones especificas en función del tipo de datos de cada columna. El desplegable, además de las opciones de filtrado propias del campo muestra una lista ordenada ascendentemente con todos los valores distintos que contiene dicho campo. En la figura siguiente podemos ver un ejemplo para un campo de tipo texto llamado POBLACION.
Para filtrar por un valor o valores concretos basta con deseleccionar la casilla (Seleccionar todo) y seleccio-nar las casillas de los valores deseados.
Una vez aplicado un filtro a un campo, en la cabecera de dicho campo y como indicativo de que los datos están filtrados para dicho campo, aparece un pequeño embudo. Así mismo en la parte izquierda de la barra de estado, si esta visible, veremos un mensaje que nos dice cuantos registros respecto al total cumplen el criterio o criterio seleccionados, que son los que quedan visibles.
Se puede filtrar por uno, varios o todos los campos a la vez. Al filtrar dos o mas campos se dice que se usan criterios tipo "Y", dado que los registros visibles han de cumplir los criterios del primer campo y los del se-gundo y así sucesivamente.
Después de haber aplicado un filtro es posible ordenar los datos filtrados utilizando la opción existente en el propio desplegable.
Filtro Avanzado El filtro avanzado es una herramienta más potente que permite sobrepasar las limitaciones del "Filtro" auto-mático. Con Filtro Avanzado podemos:
• Aplicar un numero indeterminado de criterios sobre el mismo campo • Aplicar criterios "Y" , criterios "O" o ambos al mismo tiempo • Hacer referencia a celdas en la construcción de los criterios, lo cual nos permite una mayor versati-
lidad al poder configurar criterios semi-dinamicos. • Se pueden usar formulas en la construcción de los criterios. • Copiar los resultados que cumplan los criterios en otro lugar de la hoja de calculo. A esto se le de-
nomina "Extracción de información". • Filtrado de registros únicos.
Excel 2007
Página 60
• Poder definir los filtros en cualquier hoja de calculo de cualquier libro, y no necesariamente en la misma hoja de calculo donde se hallan los datos
Definición de un filtro avanzado
Para crear un filtro avanzado debemos seleccionar un rango en la primera fila del cual pondremos los nom-bres de los campos a filtrar y en las filas de debajo de esta los criterios que debe cumplir cada campo.
Todos los criterios que estén en la misma fila se denominan criterios "Y" y deben cumplirse todos a la vez para un registro determinado.
Todos los criterios que estén en filas diferentes se denominan criterios "O" y son independientes de los crite-rios de las demás filas.
Ejemplos:
Supuesta una base de datos sobre empleados que tiene los campos PROVINCIA y EDAD entre otros.
Criterios "O"
EDAD Todos los empleados cuya edad sea igual a 38 o a 40 o menor o igual que 34 años. 38
40 ≤34
Criterios "Y"
PROVINCIA EDAD Todos los empleados que sean de la provincia de Barcelona y tengan una edad igual o superior a los 40 años. Barcelona >=40
Criterios "O" e Y conjuntamente
Una vez creados los rangos que harán de filtro, se debe lanzar el cuadro de dialogo "Filtro avanzado" (ver siguiente figura) para lo cual debemos usar la opción “Avanzadas” de la ficha Datos, grupo Ordenar y filtrar. En dicho dialogo debemos rellenar la información de dos parámetros obligatorios:
• Rango de la lista: rango donde esta situada la base de datos. Dicho rango debe incluir también la cabecera o nombres de los campos.
• Rango de criterios: rango donde hemos creado los criterios, incluyendo todo, nombre de campos y criterios.
En ambos parámetros podemos usar nombres de rango en lugar de referencias, si tenemos creados dichos nombres. Además y de forma opcional podemos solicitar las opciones "Sólo registros únicos" y "Copiar a otro lugar"
La opción "Copiar a otro lugar" o extraer información es un caso particular de filtrado, donde además de filtrar la información, Excel nos permite crear una copia de dicha información para poderla usar de forma independiente en otros procesos, por ejemplo darle un formato especial, imprimirla, enviarla por mail a un colaborador, etc.. La información así extraída solo se puede copiar en alguna parte de la misma hoja, si intentásemos hacerlo en otra hoja o libro Excel nos mostraría el siguiente mensaje: “Sólo se pueden copiar los datos filtrados en la hoja actual”.
Las opción "Registros únicos" solo presentara un registro por grupo para todo grupo de registros cuyos valo-res sean exactamente idénticos en sus respectivos campos, es decir ocultara los duplicados.
PROVINCIA EDAD Todos los empleados que sean de la provincia de Barcelona y además todos los que tenga una edad menor que 36 años. Barcelona
<36
Excel 2007
Página 61
Excel 2007
Página 62
Subtotales Para cualquier base de datos Excel permite realizar un agrupamiento automático de valores para uno o varios campos, creando un grupo cada vez que detecte un cambio de valor en los campos que le indique-mos y añadiendo una o mas funciones de totalización para cada grupo creado, usando para ello la función SUBTOTALES.
La forma de proceder de Excel es la siguiente:
• Crea un grupo de registros para cada conjunto de datos que tiene el mismo valor en el campo por el cual se esta agrupando. Los registros no se mueven de posición (fila).
• Por cada cambio en el valor del campo creara un nuevo grupo. Esto lleva implícito el hecho de que en general es necesario tener debidamente ordenados los registros por el campo o campos por los que queramos agrupar.
• Una vez creados los grupos, añade una función SUBTOTALES para cada grupo, con la operación elegida (Suma, Cuenta, Promedio, Producto, etc.) que resume el resultado de dicha operación so-bre todos los registros de cada grupo, poniendo el resultado en una nueva fila encima o debajo de cada grupo.
• Por cada subtotal creado genera un nivel de esquema de forma que podamos ver/ocultar de forma rápida los totales o totales y detalles que los componen.
Creación de subtotales
Para crear subtotales para un cierto campo y usando una cierta función se deben cumplimentar los siguien-tes pasos:
• Ordenar adecuadamente los datos por el campo o campos por los que se desea agrupar y crear subtotales.
• Lanzar el cuadro de dialogo Subtotales desde la ficha Datos, grupo Esquema, opción Subtotal. • Elegir el campo por el que agruparemos en el desplegable "Para cada cambio en...". • Elegir la función a usar en el desplegable "Usar función:" • Dentro de la lista "Agregar subtotal a:" marcar todos los campos a los cuales queremos aplicar al
función elegida.
NOMBRE APELLIDOS DIRECCION POBLACION PROVINCIA UNIDS P/U TOTALLourdes Cruz Ros C/ Molas, 7 Barcelona BARCELONA 400 500 200.000Rosa Perez Lopez C/ Balmes, 123 Barcelona BARCELONA 120 350 42.000jope Puig Ros C/ Muntaner, 546 Barcelona BARCELONA 275 350 96.250Adela Sanchez Lopez C/ Balmes, 123 Barcelona BARCELONA 325 350 113.750Juan Sanchez Lopez C/ Balmes, 123 Barcelona BARCELONA 325 750 243.750Juan Cruz Ros C/ Verano, 23 Sabadell BARCELONA 450 500 225.000Carlos Puig Ros C/ Muntaner, 456 Sabadell BARCELONA 510 1.000 510.000Eva Closgomerzindo C7 Gelabert, 13 Bordils GERONA 700 750 525.000Adela Coll Marti C/ Cereza, 78 Bordils GERONA 310 500 155.000Jose Coll Marti C/ Cerza, 78 Bordils GERONA 550 750 412.500Adela Martin Marti C/ Gilabert, 12 Bordils GERONA 600 500 300.000Adela Saez Pla C/ Soledad, 88 Bordils GERONA 275 500 137.500Fermin Saez Pla C/ Soledad, 88 Bordils GERONA 220 750 165.000Pedro Rodenas Gomez C/ Ajo, 45 Figueres GERONA 400 1.000 400.000Fermin Romenas Gomez C/ Ajo, 45 Figueres GERONA 275 1.000 275.000Eva Clos Reig C/ Sol 23 Reus TARRAGONA 400 1.000 400.000Jose Clos Trull C/ La Luna, 145 Reus TARRAGONA 600 500 300.000Lourdes Clos Trull C/ La Luna, 145 Reus TARRAGONA 345 350 120.750Carlos Clos Reig C/ Sol, 23 Valls TARRAGONA 400 1.000 400.000Juan Perez Lopez C/ Balmes, 123 Valls TARRAGONA 600 750 450.000
RESUMEN VENTAS ANUALES
Excel 2007
Página 63
Se puede aplicar la misma función a varios campos distintos de cada grupo
Utilizar distintas funciones sobre uno o mas campos de la misma agrupación Por defecto inicialmente Excel solo permite aplicar la misma función para los diversos campos selecciona-dos. Si queremos aplicar una función distinta para uno o varios campos de la misma agrupación, tenemos dos opciones
1. Una vez generados los subtotales, modificar manualmente la función SUBTOTALES para los cam-pos deseados, cambiando el primer parámetro de dicha función y seleccionando el número que co-rresponda a la función que queremos aplicar (véase función SUBTOTALES).
2. Volver al lanzar el cuadro de dialogo Subtotales tantas veces como funciones distintas queramos aplicar, eligiendo en cada ocasión la función a aplicar y los campos sobre los que se aplicara y man-tener desmarcada en todos los casos la casilla "Reemplazar subtotales actuales" de forma que nos mantenga los subtotales creados anteriormente y nos agregue los nuevos. Por cada subtotal añadi-do creara una nueva línea con el titulo y resultado de dicho subtotal.
Creación de varios niveles de subtotales Es posible crear los niveles de agrupación que se quieran, es decir se puede agrupar por uno, dos o todos los campos de la base de datos que se quiera.
Para llevar a cabo los subgrupos se debe proceder de una forma análoga a la creación de subtotales para un solo campo. Se deben tener en cuenta las siguientes consideraciones:
• Ordenar debidamente los datos, ordenando primero los del primer campo por el que se quiere agru-par, segundo los del segundo campo de agrupación, y así sucesivamente.
• Lanzar el cuadro de dialogo "Subtotales" una vez por cada campo de agrupación y elegir en cada caso el campo deseado en el desplegable "Para cada cambio en...", pero siguiendo el mismo orden de selección que el de ordenación de los campos.
• Elegir después la función a utilizar y los campos sobre los que se empleara. • Mantener siempre deshabilitada la casilla "Reemplazar subtotales actuales"
Por cada grupo y función distinta aparece una nueva fila con el titulo de Total o Cuenta o Promedio, según la función utilizada. Los datos se muestran agrupados por filas mediante un esquema.
La opción "Salto de pagina entre grupos" esta relacionada con la impresión del documento. Si esta habi-litada Excel creara tantas hojas físicas para imprimir como grupos y subgrupos se generen al hacer el es-quema.
Ejemplos.
Nivel 1
Nivel 2
Nivel 3
Excel 2007
Página 64
Para eliminar los agrupamientos de valores, sus subtotales y sus correspondientes esquemas, basta situar-se en cualquiera de las celdas de la base de datos y clicar el botón "Quitar todos" de cuadro de dialogo Sub-totales.
Excel 2007
Página 65
FUNCIONES DE BASES DE DATOS Las funciones de esta categoría operan sobre los registros de una base de datos, lista o tabla. Todas ellas comienzan por el prefijo BD seguido del nombre de la función y están bastante relacionadas con sus funcio-nes estadísticas homónimas en cuanto a funcionalidad. Se puede decir que en la mayoría de casos son extensiones de estas que además admiten múltiples criterios personalizados para seleccionar solo los regis-tros de la base de datos sobre los que actuaran.
Sintaxis general BDfunción(base_de_datos;nombre_de_campo;criterios)
base_de_datos es el rango de celdas que compone la base de datos. Una base de datos es una lista o tabla de datos relacionados, en la que las filas son los registros y las columnas de datos son los campos.
nombre_de_campo indica el campo que se utiliza en la función. Puede ser un texto con el rótulo encerrado entre dobles comillas como, por ejemplo, "Edad" o "Campo", o como un número que represente la posición de la columna en la lista: 1 para la primera columna, 2 para la segunda y así sucesivamente.
criterios es el rango de celdas que contiene los criterios personalizados de la base de datos. Se puede utili-zar cualquier rango en el argumento criterios mientras éste incluya por lo menos un rótulo de columna.
Entre las principales funciones de bases de datos están las siguientes:
BDCONTAR
Cuenta las celdas que contienen un número en una columna de una lista o base de datos, y que concuer-dan con los criterios especificados.
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.
BDCONTARA
Cuenta el número de celdas que no están en blanco dentro de los registros de la base de datos que cum-plen con los criterios especificados.
BDSUMA
Suma los números de una columna de una lista o base de datos que concuerden con las condiciones espe-cificadas.
BDPROMEDIO
Devuelve el promedio de las entradas seleccionadas de una base de datos que coinciden con los criterios.
BDMAX
Devuelve el valor máximo de las entradas seleccionadas de una base de datos que coincidan con los crite-rios.
BDMIN
Devuelve el valor mínimo de una columna en una lista o base de datos que concuerde con las condiciones especificadas.
BDEXTRAER
Extrae un único valor de la columna de una lista o base de datos que concuerde con las condiciones especi-ficadas. Si ningún registro coincide con los criterios, BDEXTRAER devuelve el valor de error #¡VALOR!. Si coincide más de uno, devuelve el valor de error #¡NUM!.
BDDESVESTP
Calcula la desviación estándar del conjunto de una población basándose en toda la población, utilizando los números de una columna de una lista o base de datos que concuerden con las condiciones especificadas.
BDVARP
Calcula la varianza del conjunto de una población basándose en toda la población, utilizando los números de una columna de una lista o base de datos que concuerde con las condiciones especificadas.
Ejemplos:
Excel 2007
Página 66
Supuesta la base de datos de la figura siguiente:
En dicha base de datos se han definido dos rangos con nombre para usarlos en la construcción de las for-mulas, dichos rangos son BD2_Criterio1 y BD2_Criterio2
En E24 se desea calcular la edad promedio de todos los hombres, para lo cual se debe usar la función DBPROMEDIO, con los siguientes valores:
=BDPROMEDIO(B1:I21;"Edad";BD2_Criterio1)
En E25 se desea calcular la edad promedio de todas las mujeres que vivan en la provincia de BARCELO-NA. Análogamente al caso anterior debemos usar la función DBPROMEDIO con la siguiente sintaxis:
=BDPROMEDIO(BDatos_2;"Edad";BD2_Criterio2)
En la figura anterior se muestran los resultados correspondientes.
Excel 2007
Página 67
Tablas
El concepto de Tabla se introdujo en la versión Excel 2003, aunque con el nombre de Lista. En Excel 2007 se han añadido algunas características adicionales que hacen que sea aun mas útil y manejable.
Una Tabla es un conjunto de datos, con un nombre determinado, al que se le puede aplicar filtros, formatos, ordenar, añadir o quitar filas o columnas, crear campos calculados y agregar funciones de totalización a las diversas columnas de la tabla, trabajando siempre con dicho conjunto como una unidad.
Las Tablas solventan algunos problemas importantes que se producen con rangos de datos que represen-taban un conjunto de información relacionado, y que son dinámicos, es decir van cambiando con el tiempo. Así es normal que se añadan mas registros al final de dicho rango, o se eliminen otros, y análogamente ocurre con los campos o columnas.
En gráficos, formulas, tablas dinámicas, etc., asociadas a datos, al emplear referencias a rangos convencio-nales de datos, no pertenecientes a una tabla, en la mayoría de los casos no se actualizan las referencias existentes cuando el rango de datos cambia, por ejemplo no se detecta que se han añadido mas datos al principio o al final y por lo tanto el primer o ultimo registro están en una posición diferente. La única forma de evitar estas situaciones consiste en insertar filas después de la primera del rango o antes de la ultima, con lo cual las referencias al rango si se adaptan a la amplitud de la nueva área de datos, o bien redefinir los nombres de rangos existentes para que abarquen los nuevos registros o en el caso de formulas, modificar las referencias a los rangos de origen, si en dichas formulas no se han empleado nombres de rangos. Al trabajar con tablas, estas son como rangos dinámicos con nombre que se ajustan (amplían o reducen) se-gún la evolución del área de datos.
Las principales utilidades de una Tabla son:
• Creación rápida y eficaz de rangos dinámicos para su uso en gráficos, formulas, etc., tal y como ya se comento en párrafos anteriores.
• Posibilidad de poder tener mas de una base de datos en la misma hoja y poder aplicarles de forma independiente ordenaciones, filtros, etc.
• Uso sencillo y abreviado de rangos en formulas, bien internas en la propia tabla, bien externas a la tabla.
Creación de tablas Para crear una tabla debemos llevar a cabo los siguientes pasos:
1. Escribir los datos que formaran la tabla inicial, es decir las los registros y campos (filas y columnas) correspondien-tes.
2. Seleccionar dichos datos, incluyendo los encabezados e ir a ficha Insertar, grupo Tablas, opción Tabla. Aparecerá el cuadro de dialogo “Crear Tabla”.
3. Definir si la tabla tiene encabezados o no.
4. Pulsar el botón aceptar.
Utilización de tablas Una vez creada una tabla, si nos posicionamos en cualquier celda de su interior nos aparecerá la ficha con-textual “Herramientas de tabla-Diseño”, en la que existen diversas opciones para aplicar formato, crear una tabla dinámica a partir de la misma, exportar datos, etc.. Hay dos opciones que conviene destacar:
• Nombre de tabla: Sirve para darle un nombre significativo a la tabla, nombre que después se podrá usar para gráficos, formulas, etc.
• Convertir en rango: Si en algún momento no deseamos tener los datos como tabla o se ha de hacer alguna modificación que la tabla no permita, podemos volver a convertir la tabla en un rango normal de datos.
Excel 2007
Página 68
Uso de referencias a la tabla Para hacer referencia a un campo de la tabla dentro de alguna de las celdas de la propia tabla, por ejemplo para crear un campo calculado, basta con poner un signo igual (=) y seleccionar el rango de datos dentro de la tabla, y es el propio Excel el que escribirá con la sintaxis adecuada la referencia a dicha celda o rango de celdas.
Para hacer referencia a un campo de una tabla desde fuera de la tabla, debemos poner el nombre de la tabla y después entre corchetes el nombre del campo. Ejemplo:
=CONTAR(NombreDeLaTabla[NombreDelCampo])
y de forma análoga en cualquier cuadro de dialogo en el que queramos introducir una referencia a un rango.
Existen una serie de modificadores específicos para las tablas (caracteres o palabras reservadas de con-trol), que se pueden usar al escribir referencias a partes de la tabla, dichos modificadores y sus significado son los siguientes:
Ejemplos
=NombreDeLaTabla[[#Modificador];[NombreCampo]]
=ComprasHorasExtras[[#Totales];[H.extras]]
Modificador Función
#Todo Devuelve todo el contenido de la tabla: datos, encabezados y fila totales
#Datos Da solo los datos, sin encabezados ni fila de totales
#Encabezados Da la fila de encabezado
#Totales Devuelve el valor de la fila totales
#Esta fila Da el valor de la tabla que se encuentra en la misma fila que la celda donde esta la formula, aunque se encuentre en distinta hoja.
Excel 2007
Página 69
VINCULACIÓN DE DATOS.
Ya sabemos que Excel nos permite hacer referencia a una celda o rango de celdas desde otra distinta, bien usando formulas que sean directamente referencias o bien usándolas como argumentos de funciones, de-biendo nosotros decidir el tipo de referencia a usar (relativa, absoluta o mixta). De esta manera tenemos una vinculación dinámica de datos, de forma que cuando cambien los datos en la celda referenciada cam-biaran también en todas las celdas o funciones donde se hallen referenciados.
Bajo el punto de vista de la vinculación de datos, Excel distingue dos casos posibles:
• Referencias internas. Aquellas que establecemos entre celdas de la misma hoja de calculo. • Referencias externas. Las que se establecen entre diferentes hojas del mismo libro o entre diferen-
tes libros.
Referencias externas En muchos casos las referencias externas se hacen necesarias, bien por que no es posible por complejidad poner todos los datos en la misma hoja y el tenerlos en hojas o libros distintos facilita su manipulación de forma mas sencilla, bien por que hay demasiados datos, bien por que son datos estructuralmente idénticos pero pertenecen a distintas secciones de la empresa, o son de distinto grupo.
Para establecer referencias externas tenemos cuatro posibilidades distintas equivalentes:
Copiar la celda que queramos vincular, trasladarnos a la celda donde haremos la vinculación y allí usar la ficha Inicio > Pegar > opción "Pegar vínculos" o bien opción "Pegado especial.." y pulsar el botón "Pegar vínculos" situado en la parte inferior.
Copiar la celda que queramos vincular, trasladarnos a la celda donde haremos la vinculación y allí, usando el menú contextual de las celdas, elegir la opción "Pegar" y después usando el desplegable "opciones de pegado" que aparece al lado de la celda, seleccionar "Vincular celdas"
Para la tercera forma debemos tener visibles tanto la celda fuente, como la celda destino del vinculo, es decir que ha de haber dos ventanas distintas, cada una mostrando una de las celdas que intervienen en la operación. Una vez así basta con posicionarse en la celda en la que haremos la vinculación, escribir el signo igual (=) y después clicar sobre la celda que queremos vincular, que esta en la otra ventana.
La última forma consiste en escribir la referencia directamente en la celda de vinculación de forma manual. Esta opción no es muy aconsejable por posibles errores en la sintaxis cuando los nombres de hojas o libros sean largos.
Sintaxis de las referencias externas:
• Entre hojas del mismo libro: NombreHoja!CeldaVinculada, p.e. =Enero!B13. • Entre libros diferentes: [NombreLibro]NombreHoja!CeldaVinculada: p.e.=[Fuente.xlsx]Enero!$B$13.
En el caso de referencias entre hojas del mismo libro, el movimiento de las hojas de posición o el cambio de nombre no afectan para nada a las referencias. En el caso del cambio de nombre, Excel también cambia el nombre automáticamente en todas las referencias en las que se mencione dicho nombre.
Referencias externas entre libros Cada vez que hacemos una referencia a una celda o rango de celdas que se encuentran en otro libro, es decir establecemos un vinculo, Excel almacena no solo el nombre del libro vinculado, sino también la ubica-ción del mismo, para poder acceder a el en el momento en que se quiera actualizar la información.
Si un libro contiene vínculos a otros, podemos ver y manipular dichos vínculos con la opción Botón de Office > Preparar > "Editar vínculos a archivos", que nos muestra el cuadro de dialogo "Modificar vínculos". Aquí podemos actualizar los valores vinculados en el momento deseado, cambiar el origen del vinculo, abrir el origen del vinculo (para hacer nuevas vinculaciones) o visualizar otra información relacionada. También podemos romper el vinculo si hemos borrado la referencia existente y por tanto ya no nos interesa que apa-rezca la línea que referencia al libro donde estaba. Finalmente podemos comprobar el estado de un vinculo.
Excel 2007
Página 70
En este dialogo aparece una línea por cada libro distinto vinculado, no por cada celda vinculada, es decir que aunque haya quince vínculos a un cierto libro, este solo aparecerá una vez en el listado "Modificar vínculos".
Además de vínculos a otros libros de Excel aquí también aparecen todos los vínculos existentes que pudie-ra haber a documentos de otros tipos, como por ejemplo un documento Word o una Base de Datos Access.
Hay dos casos especiales en los que un vinculo puede quedar desconectado:
• Cuando cambiamos de nombre el archivo vinculado • Cuando movemos a otra carpeta o directorio el archivo vinculado
En ambos casos, al abrir el libro de destino donde esta la referencia o referencias a dicho libro, Excel al no poder actualizar los datos vinculados lanza el siguiente mensaje:
A fin de que podamos abrir directamente el dialogo "Modificar vínculos" y actualizar la nueva ubicación o nombre de los archivos vinculados. En botón Office > Opciones de Excel > Avanzadas > "Al calcular este libro" podemos elegir si queremos que los vínculos se actualicen automáticamente al calcular el libro y "Guardar valores de vínculos externos" de forma que aunque no podamos acceder a la información mas reciente si tengamos una copia de los datos obtenidos en la ultima actualización.
Excel 2007
Página 71
Resúmenes de datos Caso de tener información diversa que tiene entre si una relación lógica, pero que esta dispersa en múltiples hojas o libros, bien por que por falta de previsión se hizo así en su momento o bien por que es información que pertenece a distintos grupos, divisiones o departamentos y cada uno la ha escrito en su propia hoja, Excel nos permite "combinar" los valores de un conjunto de hojas del mismo libro o de libros distintos en una hoja maestra u hoja de resumen, siempre que los datos presenten alguna relación o similitud estructural entre ellos. Si no hubiera una mínima relación estructural no tendríamos otro remedio que hacer la hoja resumen a mano, creando cada relación o referencia una por una.
Por "combinar" se debe entender hacer algún tipo de calculo o resumen sobre los datos que se referencia-ran ,es decir aplicar alguna función de contaje o estadística típica sobre esos datos, como por ejemplo la Suma, el Promedio, la Desviación Estándar, etc.
En el caso de existir esas mínimas relaciones estructurales entre los datos a combinar Excel nos ofrece tres posibilidades para resumir los datos de forma automatizada: Referencias 3D, Consolidación de datos por posición y Consolidación de datos por categorías.
Referencias 3D Para poder usar esta característica de Excel es necesario que se cumplan las siguientes condiciones:
• Las hojas sobre las que se podemos crear referencias 3D deben ser del mismo libro de trabajo. • Las hojas a resumir deben de tener la misma estructura de datos. • Las referencias 3D no permiten elegir individualmente las hojas sobre las que operaran, solo permi-
ten elegir la primera y la ultima hoja que formaran parte de las referencias 3D, quedando automáti-camente incluidas en dicha referencia todas las hojas que se encuentren entre ambas en cada mo-mento. Así si se mueve o quita una hoja entre ellas, desaparecerá de las referencias y si por el con-trario de mueve o agrega cualquier hoja entre la primera y la ultima hoja de referencia, entonces pa-sara a formar parte de la referencia y será tenida en cuenta en los cálculos.
Un ejemplo típico de uso es cuando se tienen datos sobre ingresos, gastos, ventas, etc. con la misma es-tructura pero en diferentes hojas, por ejemplo una hoja por cada tienda, delegación o producto, o bien agru-pados en una hoja por cada mes o año contabilizado.
La sintaxis para crear una referencia 3D es :NombreHojaIncial:NombreHojaFinal!CeldaReferenciada
Supuesto tenemos una serie de hojas que son el resumen de ventas del año 2009 para las cuatro tiendas de una cadena de electrónica e informática. Cada hoja contiene los datos resumen de una cierta tienda de la cadena y tienen por nombre 2009_T1, 2009_T2, 2009_T3, 2009_T4
La forma de crear una referencia 3D sería la siguiente:
• Situarnos en la hoja y celda donde queramos hacer la función de resumen. El resumen se puede hacer en cualquier hoja y en cualquier celda, no tiene por que coincidir posicionalmente con la celda de las hojas que referencia. En nuestro caso crearemos una nueva hoja llamada "Ventas Totales" nos posicionaremos en B6 y ahí haremos las referencias 3D aplicando la función SUMA.
• En la celda seleccionada (B6) escribimos el signo igual (=), el nombre de la función (SUMA) y el pa-réntesis de apertura de la función " ( ". Después del paréntesis, el nombre de la primera hoja a su-mar, 2009_T1, seguido del operador rango (:) y el nombre de la ultima hoja a sumar, 2009_T4. Jus-to a continuación, el símbolo admiración (!) y la referencia a la celda sobre la que queremos aplicar el calculo ( aquí B4). Una vez escrito todo realizamos la validación de la formula pulsando la tecla Enter.
De esta forma en la celda B6 de nuestra hoja "Ventas Totales" aparecería el resultado de sumar todas las celdas B4 de todas las hojas que haya entre 2009_T1 y 2009_T4, que aquí en total son cuatro.
La formula de B6 sería por tanto: =SUMA('2009_T1:2009_T4'!B4), según puede verse en la siguiente ilus-tración.
Excel 2007
Página 72
Las funciones que podemos aplicar en formulas con referencias 3D son: SUMA, PROMEDIO, CONTAR, CONTARA, MÁX, MÁXA, MÍN, MÍNA, PRODUCTO, DESVEST, DESVESTA, DESVESTP, VAR, VARA, VARP y VARPA.
Consolidación de datos Además de resumir datos de forma manual o usando referencias tridimensionales si es posible, Excel per-mite consolidar datos repartidos en diferentes hojas o libros, si los datos están dispuestos en forma de tabla o listado y comparten una cierta estructura mínima común, incluso en el caso de no ser completamente iguales en cada hoja o hallarse en la misma posición.
Consolidar permite combinar los valores de hasta 255 hojas de cálculo en una única hoja final de resumen.
Por ejemplo, si tenemos información financiera o de ventas por cada división o tienda de nuestra compañía en hojas o libros de trabajo separados, usando la opción Consolidar podríamos crear una hoja de cálculo resumen que calcule los totales de los artículos correspondientes en cada libro de trabajo de cada división o tienda.
Dependiendo de la estructura mínima común de los datos tenemos dos posibilidades de consolidación: Consolidación por posición y Consolidación por categorías
Consolidación por posición
Para poder usarla solo es necesario que la información de los datos se halle situada en la misma posición en cada una de las hojas referenciadas u hojas fuente.
La información no tiene por que tener títulos o encabezados de fila y columna o los títulos no tienen por que ser igual, aunque es mas practico que existan esos títulos y sean los mismos en todas las hojas a seleccio-nar, de esta forma el propio Excel usará los títulos para construir el rango resumen.
Como es la forma mas sencilla de consolidación de las dos existentes también es la mas fácil de realizar. Para realizarla llevaríamos a cabo los siguientes pasos:
1. Crear la hoja de consolidación o resumen y situarnos en el lugar que queremos que aparezca el primer resultado o titulo.
2. En ficha Datos > Herramientas de datos, pulsar el botón Consolidar lo que hace aparecer el dialogo homónimo.
Excel 2007
Página 73
3. Elegir la función de resumen a utilizar en el desplegable Función. Las funciones existentes posibles
son: Suma, Cuenta (que corresponde a la función CONTARA), Promedio, Máx, Mín, Producto, Con-tar Números (que corresponde a la función CONTAR), Desvest, Desvestp, Var y Varp.
4. En el cuadro de texto "Referencia" seleccionar el rango de datos de la primera hoja que queramos consolidar. En dicho rango además del área de datos fuente podemos incorporar de forma opcional los títulos o encabezados de fila y columna, siempre que sean idénticos en todos los rangos y así Excel los usará para el rango de resumen.
5. Si los datos estuvieran en otro libro debemos pulsar el botón Examinar y localizar el libro y hoja donde se hallen.
6. Una vez seleccionado el primer rango de datos, con o sin títulos, debemos pulsar la tecla Agregar para añadirlo a la lista de referencias "Todas las referencias".
7. Añadir el resto de rangos de las otras hojas, para lo cual, dado que todos los rangos tienen la mis-ma posición basta con hacer clic sobre la pestaña de la hoja para que ya aparezca en el cuadro de texto "Referencia", después pulsamos el botón Agregar y así sucesivamente con cada rango-hoja.
8. Si queremos que Excel use los datos de la fila superior y/o de la columna izquierda de los rangos seleccionados como títulos en la hoja resumen se deben de habilitar las casillas del grupo "Usar ró-tulos en" sito en la parte inferior izquierda del dialogo "Consolidar"
9. Por ultimo existe la opción "Crear vínculos con los datos de origen", si no la activamos, Excel sim-plemente creara una consolidación-resumen estático de los datos de origen, es decir que aunque estos cambien, los cambios no se reflejaran en la hoja resumen, pero si activamos dicha casilla, Ex-cel creara un vinculo con cada celda de datos, de forma que la hoja resumen reflejara siempre un total actualizado de los datos.
Si activamos la opción "Crear vínculos con los datos de origen", Excel actúa de una forma similar a como lo hace cuando creamos subtotales, es decir genera de forma automática un Esquema en el cual los elemen-tos de primer nivel son los resultados de la función aplicada y los de segundo nivel los componentes de cada resultado global. Al mostrar el segundo nivel de cualquier total veremos en una columna, entre los títulos de fila y los datos, el nombre del libro de donde provienen los datos y en cada celda la formula con la referencia al libro, hoja y celda de la cual toma el dato. En la figura siguiente se muestra una consolidación por posición de cuatro tiendas de electrodomésticos y electrónica de una cadena de almacenes. La consolidación se ha hecho sobre las hojas denominadas Bar-celona, Hospitalet, Mataró y Tarragona, pertenecientes todas a un libro llamado ConsolidacionPorPosicion.
Excel 2007
Página 74
Consolidación por categorías
Este tipo de consolidación es más flexible, dado que permite que los rangos sean de distinto ancho (numero de columnas), distinto alto (numero de filas) y pueden estar situados en lugares diferentes en sus corres-pondientes hojas. Aquí si es necesario que haya títulos de filas y columnas, pues se basa en ellos para con-solidar. Por ejemplo suponiendo que tuviéramos unos rangos de datos donde sus títulos de columnas fue-ran meses, seria indiferente si en una hoja Enero esta en la columna A y en otra en la columna F.
La forma de crearla es completamente análoga a la de consolidación por posición, pero aquí si es necesa-rios seleccionar los títulos de filas y columnas para usarlos como rótulos al consolidar.
Ejemplo:
En el ejemplo siguiente se parte de la existencia de cuatro hojas diferentes, cada una de las cuales repre-senta una asignatura de un curso (Matemáticas, Física, Ingles e Informática). En cada hoja se apuntan los Alumnos que esta matriculados, las pruebas parciales que tiene cada asignatura y el resultado que ha obte-nido cada alumno en cada asignatura. No todas las asignaturas tienen el mismo número de pruebas o Exá-menes (por ejemplo Informática tiene un examen mas) y no todos los alumnos están matriculados en todas las asignaturas. Se trata de hallar el promedio del curso por Alumno y Examen para todas las asignaturas en las que esta inscrito cada alumno.
El resultado final se puede observar en la siguiente figura, en donde se puede ver con claridad que la posi-ción de los datos es diferente en cada hoja consolidada (véanse las referencias de los datos del alumno Carlos para el primer examen -de D1 a D8- ).
Excel 2007
Página 75
GRÁFICOS
Conceptos de gráficos Para crear gráficos es necesario familiarizarse con los conceptos mas comunes relacionados con los mis-mos. Los mas importantes son los siguientes:
• Series: Una serie es un conjunto de datos o conjunto de valores relacionados que se disponen en columna o en fila en la hoja de calculo.
• Categorías o Puntos: Una categoría o punto de datos es una agrupación de datos relacionados en forma de fila o columna. Excel considera categorías todos los grupos de datos que no actúen como series.
• Valores: Cada uno de los valores individuales que componen una serie o categoría.
• Rango de datos del grafico: Es el conjunto de valores y rótulos de series y categorías que confor-ma la información para genera el grafico.
Series y Categorías son conceptos equivalentes, simplemente depende de la consideración que hagamos respecto a la forma de ver los datos de las filas y columnas en que se basa un grafico. Excel por defecto considera como series de datos los grupos que abunden menos ya sean filas o columnas. Por ejemplo, supuesta la siguiente tabla de datos, donde el rango de datos comprende desde B2 a E7 a partir del cual generaríamos un grafico:
Excel, por defecto, vería como series los grupos de datos en columnas, es decir: Móviles, MP3 y eReaders, dado que hay menos que filas de datos; las filas de datos las vería como Categorías o Puntos de datos. No obstante al hacer el grafico podemos cambiar esta visión por defecto y considerar las filas como Series (An-dalucía, Aragón, etc.) y las columnas como Categorías (Móviles, MP3 y eReaders).
Para cambiar series por categorías o viceversa debemos seleccionar el grafico y en la ficha contextual Di-seño, grupo Datos, pulsar sobre la opción "Cambiar entre filas y columnas".
En las próximas ilustraciones se ve una representación de la tabla anterior en ambas posibilidades.
Excel 2007
Página 76
Figuras: Ejemplo de gráficos agrupados con series en columnas o en filas.
Aquí las series de datos están en columnas y las categorías en filas, re-presentadas por el eje de categorías o eje horizontal. Esta disposición es útil para comparar valores de diferentes series para una misma categoría, dado que las barras que representan los datos están muy pró-ximas entre si.
Este es el caso inverso al anterior, series en filas, categorías en co-lumnas. Útil para comparar valores de diferentes categorías para una misma serie
Terminología de gráficos de Excel Cuando Excel crea un grafico, lo divide en una serie de partes, que es conveniente conocer para poder in-teraccionar con las mismas a fin de modificarlas si fuera necesario. Podemos acceder a cualquiera de los componentes que pueda tener un grafico, después de creado, seleccionado el grafico y en la ficha contex-tual "Herramientas de gráficos", ficha Presentación, grupo "Selección actual" encontraremos un desplegable con la lista de todas las partes del grafico, seleccionando la deseada, ese elemento queda marcado y po-demos modificarlo
Los elementos mas importantes en un grafico Excel son:
• Área del grafico: Es el grafico propiamente, es decir el contenedor que incluye a los restantes ele-mentos del grafico.
• Área de trazado: El área de trazado de un gráfico es el cuadro interior del gráfico en donde se muestran las columnas, barras, líneas o figuras elegidas para representan los valores del grafico.
• Eje de categorías: Es el eje donde se disponen los valores o títulos de las categorías de datos. Usualmente es el eje horizontal.
• Eje de valores: Es el eje donde se dispone la escala con los valores que abarcan todas las series. En general representado por el eje vertical del grafico.
• Leyenda: Es el cuadro que muestra los nombres de cada serie y el símbolo asociado a las mismas para poder distinguirlas con facilidad en el grafico. Es opcional y se puede posicionar en cualquier parte del "Área del grafico".
• Series: Cada una de las columnas, barras, líneas, etc. que representan los datos del grafico.
50.000
70.000
90.000
110.000
130.000
150.000
170.000
190.000
210.000
Andalucia Aragón Canarias Catalunya Valencia
Moviles MP3 eReaders
50.000
70.000
90.000
110.000
130.000
150.000
170.000
190.000
210.000
Moviles MP3 eReaders
Andalucia Aragón Canarias Catalunya Valencia
Excel 2007
Página 77
Tipos de gráficos Existe una gran variedad de modelos de gráficos. Cada uno de ellos se adecua mejor a la representación de determinado tipo de datos que otro, por lo tanto es conveniente conocer que tipos de gráficos existen y bajo que formas de representación de datos son mas adecuados.
Hay diversas formas de clasificar los gráficos, Excel lo hace en función de la forma en que el grafico presen-ta la información. Las categorías de gráficos de Excel están representadas en la siguiente figura:
Entre las mas importantes destacan:
Columna: Representa las categorías horizontalmente y los valores de cada serie de datos como barras o colum-nas verticales. Son utilices para comparar entre si valo-res de dos o mas series de datos. También se puede usar para comparar los cam-bios que han sufrido los da-tos en el transcurso de un periodo de tiempo, si las categorías representasen datos de tiempo (días, me-ses, años, etc.), aunque en este caso son menos efecti-vos que los de Línea, dado que los valores individuales de cada serie no están co-nectados entre si.
5.000
10.000
15.000
20.000
25.000
30.000
Ene Feb Mar Abr May Jun Jul Ago Sep Oct Nov Dic
Tienda 1 Tienda 2 Tienda 3 Tienda 4
Excel 2007
Página 78
Barra: Son como los de Columna y sirven para lo mismo, simplemente que las categorías aparecen organi-zadas en el eje vertical y los valores de las series se representan en barras o columnas horizontales.
Es especialmente indicado para representa gráficos de control de tiempo y planificación (gráficos de Gantt), donde el eje vertical representa tareas y el horizontal la duración de las mismas.
Cilindro Cono 3D y Pirámide: Dentro de los de Columna o Barra encontramos los subtipos o variantes Cilindro Cono 3D y Pirámide, idénticos en funcionalidad. Son una variación estética de los mismos. En algu-nos casos pueden ser utilices para realzar o mejorar la presentación de los de Barras o Columnas, en espe-cial los Cilíndricos.
Circular: Sirven para mostrar la distribución o aportación de cada valor de datos al total de la serie, es decir relacionan las partes con el todo. Son útiles para comparar entre elementos de datos de la misma serie o para destacar los elementos mas significativos de la serie. Los datos (valores) se pueden representar de la forma usual en valor absoluto (cantidad), como porcentaje o de ambas formas a la vez.
Son los gráficos mas sencillos y por su naturaleza solo pueden mostrar una única serie de datos.
5.000 10.000 15.000 20.000 25.000 30.000
Ene
Mar
May
Jul
Sep
Nov
Tienda 4 Tienda 3 Tienda 2 Tienda 1
Andalucia 13%
Aragón 20%
Canarias 16%
Catalunya 26%
Valencia 25%
Ventas MP3
Excel 2007
Página 79
Líneas: Representan los valores de cada serie de datos como puntos en una línea, por lo cual son útiles para mostrar la continuidad entre puntos individuales de una serie dado que los valores están conectados por las líneas. Especialmente prácticos cuando se trata de mostrar la tendencia de los datos a lo largo de un intervalo de tiempo situado en el eje horizontal o de abcisas.
Dispersión: Estos gráficos se caracterizan por que los dos ejes miden valores. Son útiles para determinar si existe relación causa-efecto entre los valores de las series de datos que se representan. Son muy comunes en representaciones de datos científicos.
En el siguiente ejemplo basado en dos series de datos (Gastos e Ingresos), se trata de ver si existe alguna relación entre el incremento de gasto en publicidad y los ingresos obtenidos por las ventas de lo que se publicita.
5.000
10.000
15.000
20.000
25.000
30.000
35.000
Ene Feb Mar Abr May Jun Jul Ago Sep Oct Nov Dic
En Euros
Tienda 1 Tienda 2 Tienda 3 Tienda 4 Tienda 5
Resumen Anual Ventas
0
40
80
120
160
200
0 5 10 15 20 25 30 35
Ingresos en miles d
e €
Gastos en miles de €
Publicidad ‐ Ventas Ingresos
Excel 2007
Página 80
Subtipos de gráficos Dentro de cada categoría de gráficos existen diversas opciones para ajustar mejor la representación de datos. En las principales categorías (Columna, Barra, Área y Línea) existen las siguientes tres opciones o tipos:
• Agrupados: Son los que se generan por defecto. Comparan valores entre categorías y muestran una columna por cada valor de la categoría dada, separando las categorías unas de otras.
• Apilados: Solo presentan una columna por categoría, que incluye todos los valore de dicha catego-ría. Los valores se van apilando, por lo que da una representación del valor acumulado de cada ca-tegoría. Muestran o sirven principalmente para comparar categorías completas entre si, en lugar de los valores individuales como hacen los agrupados.
• Apilados 100%: Son similares a los Apilados, es decir solo muestran una columna por categoría, pero en estos todas las columnas miden igual, ocupando toda la altura del "Área de trazado", por lo que son útiles para comparar entre valores de la misma categoría y ver que con que porcentaje con-tribuye cada punto de datos al total del grupo, es decir de la categoría.
Ejemplos
Agrupados:
De este tipo hemos visto ya varios ejemplos en apartados anteriores, uno de ellos en la definición de gráfi-cos de columna.
Apilados
Apilados 100%
Interrelación Datos – Gráfico
0
100
200
300
400
500
600
Andalucia Aragón Canarias Catalunya Valencia
Resumen Anual Ventas Moviles MP3 eReaders
En m
iles d
e Eu
ros
0%
20%
40%
60%
80%
100%
Andalucia Aragón Canarias Catalunya Valencia
Euros
Moviles MP3 eReaders Resumen Anual Ventas
Excel 2007
Página 81
Existe una interrelación o dependencia continua o dinámica del gráfico con respecto a los datos que repre-senta en varios aspectos:
• Si modificamos algún dato, este cambio se vera reflejado inmediatamente en el grafico. • Si ocultamos alguna fila o columna (bien manualmente, bien mediante esquemas o bien usando fil-
tros) los datos ocultos de forma predeterminada también se ocultaran en el grafico, es decir no se representaran.
Añadir o eliminar datos A diferencia del cambio de datos, el añadido o eliminación de nuevas series o categorías, no se refleja au-tomáticamente en el grafico en la mayor parte de las situaciones. Al actualizar el grafico podemos encon-trarnos con dos casos diferentes: que la nueva serie o categoría sea contigua a las ya existentes o bien no lo sea.
Añadir una series o categorías de datos contigua a las ya existentes. . Para añadir una serie o categoría de datos contigua a las ya existentes hay que hacer clic con el botón prin-cipal del ratón sobre el gráfico para seleccionarlo. Aparecerá un borde alrededor del rango de datos del gráfico inicial. Usando el cuadro de relleno situado en la zona inferior del rango se debe seleccionar la nue-va serie.
Añadir series o categorías de datos al gráfico no contiguas a las ya existentes. Este método sirve para cualquier caso que se de, y consta de tres pasos:
• Seleccionar el gráfico haciendo clic sobre él. Aparecerá la ficha contextual "Herramientas de gráfi-cos" que contiene todas las opciones disponibles para gráficos.
• Ir a ficha Diseño, grupo Datos y pulsar la opción "Seleccionar datos" que lanzara el cuadro de dialo-go "Seleccionar origen de datos".
• En el cuadro de dialogo "Seleccionar origen de datos" esta el cuadro de texto "Rango de datos del grafico" se debe introducir nuevo rango que ocupan ahora tanto datos como rótulos o bien emplear el botón contraer dialogo para seleccionar dicho rango con el ratón sobre la hoja de calculo. Así el grafico se expandirá o contraerá según dicha área.
Añadir series o categorías de datos al gráfico copiando y pegando con el ratón. También se pueden añadir series o categorías de datos a un gráfico ya creado, con sólo seleccionarlas, copiarlas y situándonos encima del grafico pegarlas encima. La serie o categoría se añadirá de forma auto-mática a las existentes en el gráfico.
Gráficos dinámicos Para hacer un grafico realmente dinámico, sin necesidad de reajustar la propiedad " Rango de datos del grafico" cada vez que este se expande o contrae, es practico y aconsejable convertir primero el rango en tabla y después basar el grafico en dicha tabla. De esta forma, cada vez que ampliemos o reduzcamos la tabla el grafico se ajustara automáticamente. Además como beneficio añadido tenemos la posibilidad de usar los filtros que automáticamente se generan al crear una tabla.
Gráficos Combinados Se denominan gráficos combinados a aquellos que representan una o mas series de datos de una forma y el resto de otra, por ejemplo en un grafico de tres series (Comparas, Ventas, Balance), dos podrían estar en columna y la tercera en línea. Ver ejemplo en Figura: Ejemplo de gráfico combinado
Excel 2007
Página 82
Eje secundario En ocasiones ocurre que alguna de las series tiene un conjunto de valores muy diferente (mucho mayor o menor) al del resto, por lo que para que se vea mejor conviene crear un eje de valores propio para dicha serie. Para crear un eje de valores propio o eje secundario para una cierta serie hay que:
• Seleccionar cualquier punto de datos de la serie.
• Lanzar su menú contextual haciendo clic con el botón secundario del ratón.
• Elegir la opción: "Dar formato a la serie de datos…." que nos llevara el cuadro de dialo-go "Formato de la serie de datos", Opciones de la serie.
• Situarnos en el apartado Trazar serie en: y clicar sobre la opción "Eje secundario"
• Finalmente pulsar el botón "Cerrar".
Figura: Ejemplo de gráfico combinado.
Figura: Ejemplo de gráfico combinado con eje secundario.
‐200
0
200
400
600
800
1000
A1998 A1999 A2000 A2001 A2002 A2003 A2004 A2005 A2006 A2007
COMPRAS VENTAS BALANCE
‐50
‐30
‐10
10
30
50
70
90
110
‐50
50
150
250
350
450
550
650
750
850
A1998 A1999 A2000 A2001 A2002 A2003 A2004 A2005 A2006 A2007
COMPRAS VENTAS BALANCE
Excel 2007
Página 83
Opciones Especiales En muchos gráficos para aprovechar mejor el espacio del Área del grafico o del Área de trazado es con-veniente hacer determinadas modificaciones opcionales que consiguen dicho propósito. Entre las principa-les posibilidades, para tal tarea, tenemos las siguientes:
Eje de valores En el eje de valores (eje Y o eje vertical) hay una serie de posibilidades para ajustar mejor el mínimo, máxi-mo, marcas de gradación o reducción del ancho de las unidades de visualización. Accedemos a estas op-ciones usando del botón secundario del ratón encima de cada uno de los ejes y eligiendo la opción "Dar formato al eje".
Opciones del eje • Mínima, Máxima: Para determinar con precisión el rango de valores del eje y ajustarlo para eliminar
espacio vacio. Es muy adecuado cambiarlos sobre todo en la Mínima cuando el valor mínimo es bastante mayor que cero, dado que por defecto se traza desde cero.
• Unidad mayor, Unidad menor: Determina el intervalo entre cada línea divisoria del grafico y la si-guiente, para las líneas principales y secundarias respectivamente
• Valores en orden inverso: Invierte el eje de ordenadas, de forma que los valores mas altos apare-cen en la parte inferior del grafico y viceversa. Esta opción es útil si todos los valores son negativos y queremos ver los valores absolutos de cada punto.
• Unidades de visualización: Es útil para reducir la escala de los datos del eje, lo que redunda en el uso de menos espacio y más claridad.
• El plano inferior cruza en: Cambia el punto normal de intersección de los ejes X e Y. es decir mueve el eje X. Es útil cuando hay valores menores que cero. Automática: por defecto. El punto de intersección es el valor 0. Valor del eje: para posicionar el plano X en el punto de intersección deseado. Valor máximo del eje: -Eje de categorías(X) cruza en máximo valor. Hace que el eje de cate-
gorías se situé siempre en el máximo valor del eje Y, o sea en general lo desplaza a la parte superior. Esta opción anula a la opción: “Eje de categorías(X) cruza en:”
Excel 2007
Página 84
Eje de categorías Intervalo entre etiquetas: Para reducir el numero de etiquetas o nombres de categoría que apare-
cen. Es útil cuando hay muchas categorías, o sus nombres son muy largos y no se pueden ver bien. Poniendo el valor 1 salen todas las etiquetas, poniendo 2 se muestran la mitad, poniendo 3 se muestra un tercio y así sucesivamente.
Distancia de la etiqueta desde el eje: Fija la distancia entre el eje y los rótulos de categorías. Nú-meros bajos hacen que rótulos y eje estén muy próximos, números altos lo contrario. El valor mas adecuado suele ser 100.
El eje vertical cruza en: Por defecto el eje de valores se sitúa en la parte izquierda del eje de cate-gorías, es decir al inicio del mismo, con esta opción podemos desplazar el eje de valores a cualquier punto del eje horizontal o de categorías. Esta opción es relevante en gráficos de tipo científico, don-de el origen de coordenadas se suele situar en medio del plano que representa el grafico, en lugar de en la parte inferior izquierda como hace Excel por defecto. Es muy similar a la opción del eje ver-tical "El plano inferior cruza en:".
Superposición de series y ancho del intervalo Estas opciones condicionan como de próximas o separadas están las figuras (barras, cilindros, etc. ) que representan los datos dentro del Área de trazado. Su utilidad reside en aproximar o separar los puntos o valores a fin de aprovechar mejor el espacio, sobre todo cuando el grafico representa muchos valores, mu-chas series, o ambos casos a la vez.
A - Superposición de series:
Excel 2007
Página 85
Condiciona la separación entre las columnas (valores o marcadores) de cada punto de datos. El rango de val es va de –100% a 100%.
Si superposición = 0%, no hay separación entre marcadores. Todos los valores (marcadores) de un mismo punto o categoría están juntos (pero no superpuestos)
Si superposición mayor que 0%, los puntos se superponer o solapan. La utilidad es representar mas datos en menos espacio horizontal sin por ello perder información.
Si superposición menor que 0%, los puntos se separan.
B - Ancho del intervalo
Es el espacio (anchura) entre grupos de marcadores, o sea entre puntos o categorías. El rango de valores va de 0% a 500%.
A mayor valor mayor separación entre categorías y menor anchura de cada marcador del grafico
Si valor igual a 0, entonces todas las columnas (marcadores) toman el mismo ancho, quedan juntas y ocu-pan todo el "Área de trazado". La anchura de cada columna viene dada por la fórmula: ancho("Área de tra-zado")/ (núm. de columnas)
Ambas opciones (A y B) están interrelacionadas, por que se distribuyen el espacio existente en el Área de trazado.
Barras de error Las barras de error sirven para mostrar gráficamente la cuantía del error en los datos que representan pro-yecciones o extrapolaciones o bien datos obtenidos de forma experimental en tareas científicas. Las barras de error pueden aplicarse individualmente a cualquier serie de datos.
Los tipos de gráficos que admiten barras de error son los de Columnas, de Barras, de Líneas, de Áreas, de tipo XY (Dispersión) y de Burbujas. Estos dos últimos tipos admiten barras de error tanto para los valores X, como para los valores Y, o ambos a la vez.
Para agregar barras de error a un gráfico se deben seguir los siguientes pasos:
• Seleccionar el Área del grafico si se quiere crear barras para todas las series, o bien marcar un va-lor o punto de datos de la serie individual a la que se quiera añadir barras de error.
• En ficha contextual Presentación, Grupo Análisis, clicar sobre la opción Barras de error, que hará aparecer el cuadro de dialogo "Formato de barras de error" donde están todas las posibilidades re-lacionadas con el tema.
Excel 2007
Página 86
Además de marcar la dirección de las barra y el estilo, se debe seleccionar el método a usar para determi-nar la cuantía del error, en el conjunto de opciones excluyentes marcado como "Cuantía de error", en el que existen las siguientes opciones:
• Valor fijo: Se debe poner un cierto valor de error. Dicho valor será el mismo para todos los puntos o datos de la serie, es decir la barra de error tendrá el mismo tamaño (ancho o alto) para todos los da-tos.
• Porcentaje: Análogo al anterior, es decir debemos escribir el porcentaje. La longitud de la línea de error es el % elegido, pero con respecto al valor de que se trate, es decir que cada dato tendrá una Linea de diferente tamaño, excepto si hay valores iguales.
• Desviación estándar: Creara barras de error cuya longitud será el valor que se ponga. El valor o % que se ponga se crea usando todos los puntos de datos como cuantía del error, ya que se hace su desviación estándar, por ejemplo el 3% de la desviación estándar: Se crean líneas verticales del mismo tamaño, que sirven de guía para ver como esta de separado cualquier punto respecto a la media.
• Error típico: Utiliza el error estándar o típico de los valores trazados como cuantía del error de cada punto de datos. El error típico o estándar es una medida de la cuantía de error en el pronostico del valor de y para un valor individual de x. Todos los puntos de datos de la serie mostraran el mismo tamaño (alto o ancho).
• Personalizado: En esta opción se deben especificar uno o dos conjuntos de valores, bien introdu-ciéndolos manualmente, separados por punto y coma, bien especificando el rango de la hoja de calculo que se usara como valores de la cuantía del error tanto para los valores de error positivo, como para los de error negativo. Para ambas opciones debemos clicar primero el botón "Especificar valor". También es posible incluir formulas en rango de datos para los errores
Líneas de tendencia A partir de los datos representados en un grafico podemos analizar que tipo de función matemática o curva descriptiva han seguido los mismos en el pasado (es decir con los datos existentes) y también extrapolar o
Excel 2007
Página 87
hacer previsiones de evolución futura de dichos datos. Para ello, y a nivel grafico, Excel nos permite probar y ajustar los datos a una línea, función exponencial o logarítmica, un polinomio de grado máximo 6, una función potencial o usar medias móviles. Se trata de probar que tipo de curva describe mejor los datos, es decir la que mejor se adapta a los datos existentes.
Se pueden agregar limas de tendencia en la series de datos de gráficos de Columnas, de Barras, de Líneas, de Áreas 2D no apiladas, de burbujas y de Dispersión (tipo XY), siendo muy usuales en los del ultimo tipo.
Una opción importante de las líneas de tendencia o regresión es que se pueden extrapolar hacia adelante o hacia atrás de los limites de los datos para poder hacer predicciones o previsiones futuras. Esto se lleva acabo con la opción Extrapolar, señalando el numero de periodos hacia Adelante o Hacia atrás. Un periodo es el siguiente valor Adelante o Hacia Atrás en el eje X de la serie de datos representada
Otras opciones interesantes son:
• Señalar intersección: Sirve para definir el punto en que la curva debe intersectar con el eje "Y".
• Presentar ecuación en el gráfico: Nos muestra en el grafico la ecuación matemática que describe la curva, excepto en el caso de medias móviles que no existe tal ecuación. En la ecuación se ven tanto la variable X, como sus coeficientes o exponentes (caso de haberlos).
En el siguiente ejemplo se puede ver una proyección de resultados de ventas esperados extrapolada 2 pe-riodos hacia delante, es decir dos años. Se han hecho dos líneas de tendencia (la lineal de color verde y la polinómica de color rojo) para ver cual se ajusta mejor a los datos existentes: En ambos casos se muestra también la ecuación que describe cada línea.
Excel 2007
Página 88
Los mismos cálculos se pueden llevar a cabo de forma cuantitativa con ciertas funciones estadísticas de Excel. Si deseamos hacer estimaciones lineales de datos existentes, lo adecuado es usar la función ESTI-MACION.LINEAL y caso de querer hacer estimaciones no lineales (exponenciales, polinómicas, etc.) debe-remos usar la función ESTIMACION.LOGARITMICA. Ambas funciones trabajan sobre datos existentes y sirven para ajustar curvas y extraer estadísticas de regresión.
Si lo que deseamos es hacer extrapolaciones o proyecciones hacia el futuro, lo adecuado es utilizar TEN-DENCIA o PRONOSTICO, para estimaciones de tipo lineal, o CRECIMIENTO para estimaciones de tipo no lineal.
Representación de valores ocultos, nulos, o vacios Excel nos proporciona opciones para dos situaciones usuales que se pueden presentar en los gráficos:
A. Que se hayan ocultado valores de datos (filas o columnas), mediante filtros, esquemas o manual-mente. Excel por defecto no muestra estos datos en el gráfico asociado.
B. Que existan valores nulos o vacios en el rango de datos representado.
En ambos casos debemos seleccionar y el grafico y en Herramientas de grafico > Diseño >Seleccionar da-tos, clicar en el botón "Celdas ocultas y vacías", y no aparecerá el cuadro de dialogo "Configuración de cel-das ocultas y vacías" siguiente:
En el caso A. si queremos ver representados en el grafico los datos ocultos, basta con activar la casilla "Mostrar datos en filas y columnas ocultas"
El caso B es relevante cuando el grafico es de Líneas, dado que por defecto la opción activa es "Ran-gos"·que si no hay datos no los representará, por lo que la línea queda interrumpida. Si elegimos la opción "cero" Excel hará la línea continua, pasando por cero para los puntos sin datos
Si lo que queremos es que no se interrumpa la línea, pero que no nos dibuje el dato que falta, elegiremos la opción "Conectar puntos de datos con línea".
y = 2E+06x ‐ 3E+09
y = 121783x2 ‐ 5E+08x + 5E+11
0
2.000.000
4.000.000
6.000.000
8.000.000
10.000.000
12.000.000
14.000.000
2000 2001 2002 2003 2004 2005 2006 2007 2008 2009
Resumen Ventas Anuales Ventas Totales
Lineal (Ventas Totales)
Polinómica (Ventas Totales)
Excel 2007
Página 89
Otra forma de conseguir el mismo efecto de dibujar la línea continua incluso para el caso de datos que fal-tan, es rellenar las celdas vacías con #N/A (not available), valor que deberemos escribir a mano. Así al no estar el dato disponible lo pasa por alto, pero mantiene la continuidad de la serie, dado que la celda no esta ni vacía ni oculta. Esta ultima opción tiene las ventajas de una mayor claridad y el hecho de que no debe-mos cambiar la configuración por defecto en "Configuración de celdas ocultas y vacías".
Excel 2007
Página 90
TABLAS DINAMICAS
Concepto y Componentes Una tabla dinámica es una potente herramienta de análisis de datos que nos facilita Excel que permite pre-sentar resúmenes de los datos de una base de datos de una forma fácil y rápida. Una tabla dinámica nos permite clasificar, filtrar y organizar dinámicamente unos determinados datos para enfatizar diferentes as-pectos. En el apartado organizativo nos permite presentar los campos de la base de datos casi de cualquier forma deseada.
Una tabla dinámica es similar al concepto de referencias cruzadas, es decir se relacionan valores puestos en filas y columnas con un valor numérico que esta asociado a ambas y al que se le aplica una cierta fun-ción matemática o estadística ( suma, promedio, máximo, mínimo, etc.).
Las tablas dinámicas representan un paso adelante en relación a otras herramientas de resumen y análisis de datos, como son Consolidar o Referencias 3D, en el sentido de que nos permiten mas posibilidades de filtrado de datos, ordenación o agrupación. Poseen además una característica propia y única respecto a las anteriores y es la posibilidad de generar nuevos datos y cálculos personalizados mediante las opciones de campos y elementos calculados.
Creación de una tabla dinámica Se puede crear una tabla dinámica de cualquier rango de datos, siempre que los datos estén dispuestos en columnas, existiendo al principio de cada una de ellas el nombre del campo correspondiente.
Antes de crear la tabla dinámica es conveniente, aunque no necesario, convertir el área o rango que ocupan los datos, incluidos los títulos de los campos en una Tabla. De esta forma cada vez que agreguemos o qui-temos datos del principio o del final del rango, no tendremos que volver a redefinir el origen de datos de la tabla dinámica para que tenga constancia de la variación de la amplitud del rango.
Hay dos formas de crear una tabla dinámica:
• Ficha Insertar > Tablas > Tabla dinámica
• Si hemos creado previamente una Tabla con el rango de datos, ir a la ficha contextual "Herramien-tas de tabla - Diseño" > Herramientas > "Resumir con tabla dinámica".
Supuesto tuviésemos la base de datos de la figura anterior, la cual la hemos convertido en una tabla con el nombre de tblVentas, y usando el segundo método de creación de tabla dinámica, al clicar sobre el botón "Resumir con tabla dinámica" nos aparecerá el cuadro de dialogo siguiente:
Excel 2007
Página 91
En el mismo ya aparece seleccionada y con su nombre el rango de la tabla en la cual estamos situados. Caso de no ser esta la fuente de datos deseada tenemos la opción "Utilice una fuente de datos externa" que nos permite usar datos de otros libros de Excel, conexiones Bases de Datos, como Access o conexiones a fuentes de datos ODBC (Object Data Base Conectivity).
Una vez seleccionada la fuente de datos debemos decirle a Excel donde colocar la tabla dinámica: en una hoja de calculo nueva o en una hoja de calculo ya existente en el libro, la cual elegiríamos de la lista des-plegable de la parte inferior derecha del cuadro de dialogo.
Finalmente clicando el botón Aceptar, se nos creara una hoja con el siguiente aspecto:
En la hoja de tabla dinámica aparecen dos elementos diferenciados: a la izquierda, la zona donde se creara la tabla dinámica y se mostraran los resúmenes que se realicen, enmarcada en un recuadro con el titulo "Tabla dinámica1", y a la derecha el panel "Lista de campos de la tabla dinámica".
Excel 2007
Página 92
El panel "Lista de campos de la tabla dinámica" consta de cinco aéreas o partes:
1. La parte superior encabezada por el título "Seleccionar campos para agregar al informe". Aquí aparecen todos los nombres de los campos (columnas) que tenia el área de datos en la que se basa la tabla dinámica.
2. "Rótulos de columna": Aquí se ponen los campos de resumen que aparecerán como filas de la ta-bla dinámica.
3. "Rótulos de fila" : Donde se situaran los campos de resumen que aparecerán como filas de la tabla dinámica
4. "Σ Valores": En este área, para cada valor relacionado de fila y columna aparecerá el resumen o resúmenes de totales elegidos. Es la sección en la que se muestran los resultados numéricos de contar, sumar, hacer el promedio, etc.
5. "Filtro de informe": Es un área opcional, donde podemos situar el resto de campos cuyos valores no deseamos ver explícitamente, pero si queremos que intervengan en las operaciones. Al situarlos aquí tenemos la opción de aplicar filtros mas selectivos y potentes sobre la información que se re-sume en el área "Σ Valores":
Para poner uno o mas campos en cualquiera de las aéreas tenemos dos formas diferentes: 1. Arrastrándolos desde la parte superior del panel "Lista de campos de la tabla dinámica" al área
deseada. 2. En el panel "Lista de campos de la tabla dinámica" haciendo clic con el botón secundario del ratón
nos aparecerá un menú contextual que nos permite elegir el área de destino del campo. En la figura siguiente se muestra un ejemplo realizado con la base de datos de la figura inicial.
Una vez creada la tabla dinámica, vamos a ver en los próximos apartados las acciones u opciones mas interesantes que se pueden llevar a cabo con ella.
Excel 2007
Página 93
Variar la perspectiva de los datos Podemos poner un campo en cualquiera de las cuatro zonas de la tabla, principalmente en las zonas de Filtro de informe, Rótulos de fila y Rótulos de columna. Esto puede ayudarnos a estructurar los datos de una forma mas clara o adecuada, o bien a fijarlos en una cierta forma para imprimirlos.
Para posicionar un campo en una de esas zonas, seguiríamos cualquiera de los dos métodos conocidos, aunque el mas fácil y rápido es el de arrastrar el campo seleccionado al área elegida.
Filtrar Información Se puede filtrar la información de todos los campos que se quiera, de forma análoga a como se hace en una base de datos o tabla. El filtrado de información se puede llevar a cabo solo para campos que aparezcan en cualquier área que no sea la de "Σ Valores". En la parte derecha del nombre de cada campo aparece un botón en forma de flecha que representa el desplegable con todas las opciones posibles tanto de ordena-ción como de filtrado que existen para dicho campo Las opciones dependerán en cada caso del tipo de campo y del área en el que se halle. Entre las mas usuales están:
Filtros de Campo Es la única opción que existe para las aéreas de Filtro de informe, Rótulos de fila y Rótulos de columna, el resto de posibilidades solo están disponibles para las secciones de Rótulos de fila y Rótulos de columna.
El filtro de campo nos presenta la lista de todos los valores de dicho campo. Se pueden seleccionar discre-cionalmente los que se quieran mostrar simplemente activando la casilla que esta a izquierda de cada ele-mento. Por ejemplo: para Producto: Arenque ahumado, Caviar rojo, Cerveza tibetana Barley.
Filtros de etiqueta Filtros disponibles solo para campos de tipo texto. Por ejemplo, se puede filtrar por todos los elementos cuyo valor comience por t* o que contenga unas ciertas silabas 'alg', o que sea mayor o igual que G
Filtros de fecha Tienen un amplio abanico de posibilidades para filtrar de forma general fechas que cumplan alguno de los criterios existentes, como por ejemplo: todas las fechas del periodo trimestre1, Ayer, Hoy, Próxima semana, Próximo mes, Este año, Año pasado, Hasta la fecha. El filtro "Hasta la fecha" mostrara todos los registros cuya fecha sea del año actual y menor o igual a la fecha del dia de hoy.
Filtro personalizado Esta dentro de los filtros para fechas y sirve para acceder a algunos de los filtros predefinidos que no apare-cen por defecto en el desplegable inicial. Los filtros predefinidos extras que aparecen aquí son: "no es igual a", "anterior o igual a", "posterior o igual a", "no está comprendido entre"
Filtros por valor: Para cualquier tipo de campo: Es igual a…, No es igual a …, Mayor que…, Menor que…, Entre…, Diez me-jores….
El filtro por valor actúa sobre los totales de campo por ejemplo si lo aplico sobre el campo: Productos, Entre X e Y, solo me mostrara aquellos elementos (Casco, Zapatillas, etc.) tal que su total (es decir la suma de todos sus elementos) para el campo que sea (por ejemplo Suma de Ingresos) este dentro de los valores especificados por el filtro.
Se refiere o actúa solo sobre campos numéricos que tienen aplicada una función de Agregados (totales) y sirve para mostrar solo aquellas filas o columnas (totales de elemento) que cumplan el criterio seleccionado, de entre las que estén visibles en ese momento debido a la aplicación de filtros de pagina, es decir para filtrar considera los totales del campo (ya sea fila o columna) y no los valores individuales
Excel 2007
Página 94
Filtro Diez mejores: Esta opción esta dentro de filtro por valor:
Se puede tener aplicado un filtro por valor: por ejemplo que solo se vea Casco y Guantes y aplicar un Filtro 10 mejores.
El filtro 10 mejores actúa sobre los totales de los elementos, estén estos o no ocultos y solo muestra los que cumplen el criterio. Un ejemplo: si solo están visibles los productos Casco y Guantes pero Filtro los 2 supe-riores, mostrara dos que estaban ocultos y que son los superiores: RoadTDF y TourItalia, es decir el "Filtro 10 mejores" domina sobre el filtro por valor de campo, si se aplican sobre el mismo campo en el área de "Rótulos de fila" o "Rótulos de columna".
Ordenar Información
Orden Manual Consiste en arrastrar el elemento de campo, de fila o columna, por su cabecera para colocarlo en la posi-ción deseada. Para ello debe de estar habilitada la opción "manualmente" de la ficha Herramientas tabla dinámica > Opciones > Ordenar . Si estamos situados en los títulos (elementos) de un campo al clicar Or-denar aparecerá el cuadro de dialogo Ordenar (NombreDeCampo) con la opción; "Manualmente…".
Orden automático Podemos ordenar según cuatro posibilidades o aéreas:
1. Ordenar por elementos (títulos) de campo: área "Rótulos de fila" o "Rótulos de columna". 2. Ordenar por valor de los elementos: área "Σ Valores". 3. Ordenar por totales de fila o columna. 4. Ordenar en subgrupos.
Para ejecutar cualquiera de estas posibilidades basta situarnos en el área correspondiente y pulsar el botón "Ordenar de A a Z" u "Ordenar de Z a A" de la ficha "Herramientas de tabla dinámica" > Opciones > Orde-nar. En esta misma ficha y grupo al pulsar el botón Ordenar y según el área donde estemos posicionados, aparecerá un cuadro de dialogo con mas opciones o posibilidades en relación al orden en dicha área o po-drá aclararnos dudas respecto a como va a ordenar Excel.
Ordenar por elementos (títulos) de campo: Rótulos de fila o columna
Existen dos comportamientos posibles, dependiendo de si se habilito o no la casilla "Usar listas personaliza-das al ordenar" de "Opciones de tabla dinámica" > "Totales y filtros" > Ordenación. Si no habilitada, simple-mente aplicara el orden Ascendente o Descendente según el alfabeto, o valor del número o fecha, si habili-tada, entonces inicialmente debemos elegir una lista personalizada, que es la que aplicara para ordenar los elementos. La lista personalizada nos sirve aquí para ordenar los datos según un orden jerárquico predefi-nido.
Por ejemplo, podemos ordenar el campo Producto alfabéticamente por el nombre de sus elementos o bien ordenarlo según una lista previamente predefinida, donde los productos se han organizado por nombre se-gún el volumen de ventas que hubo en años anteriores.
Excel 2007
Página 95
Ordenar por valor de los elementos: área "Σ Valores"
Al situarnos en cualquier valor de esta área nos permitirá ordenar en orden ascendente o descendente se-gún los valores de dicho elemento y podremos hacerlo en sentido vertical ("De arriba hacia abajo") o en horizontal("Izquierda a derecha").
Ordenar por totales de fila o columna
Es idéntico al "Ordenar por valor de los elementos", simplemente que se aplica al total general de filas o de columnas. Debemos por tanto situarnos previamente en cualquier total de la columna de totales de filas o de la fila de totales de columna, antes de pulsar el botón de ordenación correspondiente.
Ordenar en subgrupos
Si existen subgrupos en el área "Rótulos de fila" o "Rótulos de columna" se puede a su vez ordenar dentro de cada subgrupo, bien por elementos (titulo) o bien por sus valores. Se hace de forma análoga a los ante-riores casos posicionándose previamente en un titulo o valor del subgrupo.
Ordenar los campos en la "Lista de campos de la tabla dinámica". "Opciones de tabla dinámica" > Mostrar > Lista de campos: "Ordenar de A a Z" u "Ordenar según origen de datos". Se refiere a como presentara la lista de campos en el panel "Lista de campos de la tabla dinámica", no tiene que ver con la ordenación de campos o elementos en la tabla, La opción mas adecuada es "Orde-nar según origen de datos"
Figura: dialogo Ordenar por elemento Figura: dialogo Ordenar por valor
Excel 2007
Página 96
Esquemas: agrupar mostrar y ocultar información Los esquemas consisten en agrupar información por diversos criterios. Se suelen efectuar con los elemen-tos de los campos de Rótulos de fila. Se pueden crear de dos formas:
• Automáticamente: al poner mas de un campo en el área Rótulos de fila, dado que se crean grupos.
• Manualmente: Para ello en el menú contextual del campo o de uno de sus elementos se elige agru-par. Existen dos posibilidades diferentes, según se trate de un campo de fechas, donde podremos agrupar por varios niveles o de un campo de números donde deberemos definir la amplitud de los rangos. No se puede agrupar por campos cuyos elementos sean texto.
Cuando existen esquemas, sus niveles y subniveles se pueden manejar de diversas formas:
• Usando los botones gráficos que se pueden habilitar en ficha Herramienta de tabla dinámica > Op-ciones > Mostrar u ocultar > Botones +/-
• Usando la opción "expandir o contraer" del menú contextual de un elemento dado de uno de los campos agrupados.
• Simplemente haciendo doble clic sobre el elemento de campo deseado para expandirlo y otra vez doble clic para contraerlo.
Mostrar detalle Se añade un campo mas a dichas áreas. Es decir lo que hace es traspasar un campo oculto (que esta en el área de pagina) a las áreas Rótulos de fila o Rótulos de co-lumna y por lo tanto visibiliza sus datos. Para llevarlo a cabo debemos hacer doble clic en un nombre de campo en la áreas de áreas Rótulos de fila o Rótulos de colum-na.
Figura: dialogo Agrupar para fechas Figura: dialogo Agrupar para Números
Excel 2007
Página 97
Múltiples campos o funciones en el área "Σ Valores" Al igual que en el resto de aéreas de la tabla dinámica, en el área Σ Valores podemos situar numerosos campo, aplicándoles a cada uno se ellos una cierta función de totalización de entre las disponibles (Suma, Cuenta, Promedio, Máximo, Mínimo, etc.). Si queremos cambiar la función que por defecto nos ha creado Excel al posicionar un campo en dicha área, basta con hacer clic en la flecha negra que aparece en el nom-bre del campo, dentro del panel "Σ Valores" y seleccionar el menú "Configuración de campo de valor" que hará aparecer el cuadro de dialogo "Configuración de campo de valor" según figura adjunta:
En la ficha "Resumir por" de dicho dialogo tenemos todas las funciones posibles. Además de tener varios campos distintos también podemos tener el mismo campo repetido tantas veces como funciones distintas queramos aplicar sobre el. Evidentemente se puede tener la combinación de am-bas cosas, es decir varios campos distintos y al mismo tiempo alguno de ellos repetido aplicándole una fun-ción distinta en cada caso.
Cálculos personalizados en tablas dinámicas
Mostrar valores como Cuando en una tabla dinámica posiciona-mos un campo en el área "Σ Valores" aso-ciándole una de las funciones de resumen posibles, Excel por defecto nos muestra el resultado de esa función como valor , o "Normal" según su terminología. No obstan-te podemos presentar el resultado bajo alguna de las otras ocho formas restantes que existen, lo cual puede resultar muy útil para ver por ejemplo los datos como por-centaje o para comparar entre datos.
Para cambiar la forma de visualización de los resultados de un campo de resumen en "Σ Valores", se debe seleccionar dicho campo y acceder al cuadro de dialogo "Configuración de campo de valor", de la misma forma que se hizo en el apartado " Múltiples campos o funciones en el área "Σ Valores" y después a la ficha "Mostrar
Excel 2007
Página 98
valores como". En esta ficha encontramos las nueve posibilidades existentes, que para una comprensión mas fácil se pueden organizar en los cinco grupos siguientes:
A. Normal
1 - Desactiva el cálculo personalizado si lo había y muestra los datos de la forma usual, como valor numérico.
B. Comparaciones entre valores de elementos respecto a un elemento base
2 - Diferencia de: Para cada valor de un cierto campo y elemento mostrara la diferencia entre ese valor y el valor del elemento de dicho campo elegido como elemento base para la comparación.
3 - % de: Similar al anterior. Aquí todos los valores del elemento base son el 100% y los demás son relativos a estos, extraídos mediante un simple regla de tres. Con esta opción vemos el porcen-taje total de cada elemento-valor y en que medida sobrepasa al elemento base comparativo.
4 - % de la diferencia: Idéntico a "Diferencia de" pero expresado en %. Muestra el % que le falta o sobra a un valor respecto al 100% del valor de referencia.
C. Total en
5 - Trabaja a nivel de campo, no de elemento. Presenta los valores del total acumulado para el campo elegido. Por ejemplo supuesto el campo Producto en "Rótulos de columna", solo el pri-mer elemento de la izquierda (Casco) presentaría los valores reales, el resto presentarían los valores acumulados de la suma de todos los campos anteriores a ellos.
D. Comparaciones entre valores de elementos respecto a totales generales
6 - % de la fila: El % que representa el valor de dicho elemento respecto al total de su fila que equi-vale al 100%.
7 - % de la columna: El % que representa el valor de dicho elemento respecto al total de su colum-na que equivale al 100%.
8 - % del total: El % que representa el valor de dicho elemento respecto al total general de toda la tabla que representa el 100%.
E. Índice
9 - Calcula un valor (el del índice ) según la fórmula indicada a continuación:
((valor en celda) x (Suma total de sumas totales)) / ((Suma total de fila) x (Suma total de columna))
En todos los casos anteriores es importante observar para una correcta interpretación de los resultados, que los datos por defecto no tienen en cuenta elementos o valores ocultos por filtros.
Campos calculados Como en el caso de las Bases de Datos, además de los campos originales, podemos generar campos nue-vos, denominados campos calculados, a partir de formulas que usen como parámetros o argumentos uno o varios de los campos existentes en nuestros datos.
Para crear un campo calculado nos situamos en cualquiera de las cuatro aéreas se la tabla dinámica y se-leccionamos la opción "Campo calculado…." en la ficha "Herramientas de tabla dinámica" > Opciones > Herramientas > Fórmulas, así aparecerá el cuadro de diálogo "Insertar campo calculado".
En dicho cuadro debemos darle un nombre al campo (se admiten espacios) y después crear la fórmula que generara los datos del nuevo campo calculado. La fórmula se genera como cualquier otra fórmula de Excel, es decir poniendo inicialmente un símbolo de igual y después los nombres de los campos y operadores a usar. Los nombres de campos podemos escribirlos manualmente o seleccionarlos de la lista campos y pul-sando el botón "Insertar campo" el propio Excel se encargara de escribirlos.
Una vez creado un campo calculado, aparecerá en el panel "Lista de campos de la tabla dinámica" como un campo mas.
Podemos modificar el nombre o definición (es decir la fórmula del campo) en cualquier momento, para ello basta con abrir el cuadro de dialogo "Insertar campo calculado", seleccionar el campo en la lista desplegable Nombre, donde aparecen todos los campos calculados, modificar la fórmula y finalmente pulsar el botón Modificar para aplicar y guardar la modificación.
Excel 2007
Página 99
Para eliminar un campo calculado, de forma similar al paso anterior, se selecciona el campo y después se pulsa el botón Eliminar.
Los campos calculados siempre utilizan como función de resumen la función Suma. Como norma de Excel, la fórmula de un campo calculado opera sobre la suma de los datos asociados a los campos que se inclu-yen en la misma, por lo que no es posible cambiar la función de resumen en un campo calculado.
Ejemplos:
Supuesto tuviéramos una base de datos con los campos Ingresos, Gastos, Fecha Pedido y Fecha Entrega entre otros, podríamos generar los campos calculados siguientes:
Campo 1: Saber el Beneficio Bruto obtenido. Nombre: Beneficios Brutos Fórmula: =Ingresos – Gastos Campo 2: Obtener el Beneficio Neto supuesto que el impuesto de sociedades fuese del 40%. Nombre: Beneficios Netos Fórmula: =(Ingresos – Gastos) *0,60 Campo 3: Conocer el retardo entre la fecha de los pedidos y la fecha de entrega. Nombre: RetardoEnEntregas Fórmula: ='Fecha Entrega' - 'Fecha Pedido'
Elementos calculados Es un concepto bastante similar al de campo calculado, pero en lugar de operar con todos los valores de un campo, se opera a nivel de los valores de un elemento de campo. Es decir en los elementos calculados, a diferencia de los campos calculados la fórmula se aplica a cada registro individual y luego se resumen todos en el área de datos.
Solo se pueden generar elementos calculados a partir de campos que estén situados en las aéreas "Rotulo de fila" o "Rotulo de columna", por lo cual antes de iniciar la creación de un "elemento calculado" debemos estar posicionados en una de esas dos aéreas. Una vez hecho esto se procede de forma análoga al caso de los campos calculados, es decir vamos a la ficha "Herramientas de tabla dinámica" > Opciones > Herra-mientas > Fórmulas y seleccionamos la opción "Elemento calculado…" que nos lanza el siguiente cuadro de dialogo:
Excel 2007
Página 100
Para crear el elemento calculado deberemos darle un nombre y crear la fórmula que generara los valores del elemento, bien de forma manual o bien seleccionando el campo, después el elemento que usaremos como base para nuestros cálculos y usando el botón "Insertar campo" para insertar el campo en el cuadro de texto Fórmula.
Una vez creado un elemento calculado, este será añadido como un elemento mas del campo y por tanto aparecerá en su correspondiente área y también en los filtros por valor del campo al que pertenece.
Si los elementos de un campo se encuentran agrupados, no es posible añadir elementos calculados a dicho campo; para hacerlo hay que desagrupar primero el campo, crear el elemento calculado y después volver a agrupar el campo de la forma deseada.
Ejemplos:
Supuesto tuviéramos en nuestros datos un campo llamado Productos, que tuviera los elementos Casco, Guantes, Zapatillas, etc., entre otros, podríamos generar elementos calculados del tipo siguiente:
Elemento 1: Conocer los ingresos esperados en Casco, supuesto las ventas esperadas se incrementen un 5% en el próximo periodo.
Nombre: Casco + 5% Fórmula: =Casco *1,05 Elemento 2: Obtener un elemento calculado que sea la suma de los dos elementos mas importantes,
sin tener que recurrir a ocultar el resto de productos. Nombre: Casco y Zapatillas Fórmula: =Casco+Zapatillas
Orden de resolución En ocasiones puede que existan varios elementos calculados definidos para diferentes campos o bien va-rias formulas para un mismo elemento calculado, o tenemos elementos calculados tanto en rótulos de fila como de columna, de forma que pueda darse el caso de para una misma celda se apliquen dos o mas cálculos. Si el orden puede influir en el resultado correcto tenemos la opción de decirle a Excel en que orden queremos que se ejecuten los elementos calculados, para ello debemos lanzar el cuadro de dialogo "Orden de resolución de elemento calculado" desde la ficha Herramientas de tabla dinámica >Opciones > Herra-mientas > Formulas > Orden de resolución….Las formulas se ejecutaran de arriba hacia abajo, siendo la ultima fórmula la que determinara el valor final de la celda.
Excel 2007
Página 101
Listado de todos los campos y elementos calculados Excel nos ofrece una herramienta de auditoria que nos facilita conocer todos los campos y elementos calcu-lados que tenemos definidos sobre una cierta tabla dinámica. Si nos situamos en cualquier lugar de la tabla dinámica, en la ficha Herramientas de tabla dinámica >Opciones > Herramientas > Formulas > encontrare-mos la opción "Crear lista de formulas", que generara una hoja de calculo nueva con el nombre y fórmula de todos los campos existentes en dicha tabla
Gráficos dinámicos Un grafico dinámico combina la facilidad de resumen de datos de las tablas dinámicas con la apariencia y facilidad de análisis visual de un grafico.
Un grafico dinámico se debe generar a partir de una tabla dinámica. Una vez generado, ambos tabla diná-mica y grafico quedan totalmente vinculados, de forma que cualquier filtro que se haga en la tabla dinámica se reflejara inmediatamente en el grafico y viceversa. Así mismo si en la tabla se agregan, quitan o cambian de área campos, el grafico se actualizara automáticamente con dichos cambios nada mas finalizar los mis-mos.
Para crear un grafico dinámico debemos situarnos en cualquier punto de la tabla dinámica y pulsar la opción "Gráfico dinámico" de la ficha Herramientas de tabla dinámica > Opciones > Herramientas y escoger el tipo de grafico deseado.
Una vez creado el grafico podemos hacerle las modificaciones visuales y de formato deseadas de la forma usual en cualquier tipo de gráficos. Como particularidad, al estar seleccionada alguna parte del grafico nos aparecerá el panel "Panel de filtros de gráficos dinámico" propio solo de gráficos generados a partir de ta-blas dinámicas, donde podremos aplicar filtros a los campos de área o "Filtro de informe" y a los campos de los ejes (de categorías u horizontal y de series o vertical).
3.000
4.000
5.000
6.000
7.000
8.000
9.000
Trim.1 Trim.2 Trim.3 Trim.4
Millares de €
Casco Guantes Zapasllas
Excel 2007
Página 102
MACROS
Concepto Excel incorpora un lenguaje de programación denominado Visual Basic para Aplicaciones (VBA). Mediante este lenguaje es posible ir más allá de las funcionalidades facilitadas por las distintas fichas y comandos y realizar tareas sencillas o complejas de forma automática.
Un macro es la secuencia ordenada de los pasos o instrucciones que se deben llevar a cabo para cumpli-mentar una tarea. Excel permite guardarlos con un nombre determinado y después ejecutarlos tantas veces como se quiera de una forma fácil y rápida. Los macros son muy útiles para tereas repetitivas, dado que una vez grabados nos evitan tener que repetir de nuevo todos y cada uno de los pasos realizados para hacer dicha tarea cada vez que necesitemos repetirla, con la ventaja y seguridad de no olvidarnos de ningún paso, ni del orden correcto en que se debían ejecutar. Por tarea repetitiva se debe entender cualquiera que se vaya a repetir más de cuatro o cinco veces, pero si la tarea consta de un gran número de pasos o es muy compleja, entonces vale la pena hacer un macro aunque solo lo vayamos a ejecutar dos o tres veces.
Creación de macros Existen dos métodos para crear un macro en Excel:
• Automático: Se trata de usar la grabadora de macros que posee Excel. Dicha grabadora ira reco-giendo cada uno de los pasos que demos con el ratón o con el teclado y lo traducirá a la instrucción correspondiente de VBA, de una forma interna y automática. No necesitamos saber nada sobre programación e instrucciones en VBA. Este método es muy adecuado cuando nos iniciamos en el mundo de los macros, cuando los macros no son demasiado complejos y también como método de autoaprendizaje del lenguaje VBA, dado que después de grabar el macro podemos revisar y modifi-car el código generado por la grabadora.
• Manual: Debemos abrir el entorno de programación de Excel-VBA y teclear todas las instrucciones y sentencias necesarias, lo que implica tener conocimientos tanto de programación de VBA, como del el modelo de objetos de Excel. Para abrir el entorno de programación debemos desplazarnos a la fi-cha Programador, grupo Código y clicar sobre el botón visual Basic.
Consideraciones antes de grabar una macro: Tanto para iniciar la grabación de una macro, como para detenerla y aplicar alguna otra opción relacionada debemos hacerlo desde la ficha Programador, para lo cual ha de estar visible La ficha Programador inicial-mente, por defecto, no está visible, para hacerla visible debemos ir a botón de Office, Opciones de Excel, Mas frecuentes y dentro del grupo "Opciones principales para trabajar con Excel" habilitar la casilla "Mostrar ficha de programador en la cinta de opciones".
Figura: Ficha Programador
Antes de empezar a grabar una macro conviene tener claros diversos puntos
Es muy aconsejable conocer los pasos de la tarea que se quiere grabar, especialmente cuando esta consta de un gran número de pasos, dado que es molesto e improductivo darse cuenta en mitad de la grabación que no estamos seguros de cuál es el siguiente paso, o apercibirnos que nos hemos equivocado tres pasos atrás.
Vale la pena, si el macro es complejo, ejecutar más de una vez la rutina o anotarse los pasos de la misma, dado que cuando se graba una macro de forma automática no existen las opciones de parar la grabadora o
Excel 2007
Página 103
volver hacia atrás en algún paso y la grabadora de macros graba todo lo que se hace, incluyendo los erro-res que comentamos. Si la macro se grabo mal, solo hay dos opciones: volver a repetirla de nuevo toda entera, o bien si poseemos conocimientos de VBA modificarla manualmente en el entorno de programación.
Elegir un nombre adecuado para la macro, lo mas descriptivo posible de la función que realiza. Ni muy lar-go, ni muy corto. Los nombres deben de ser continuos, es decir si se componen de varias palabras debere-mos poner la primera silaba de cada palabra en mayúsculas o usar el guion bajo (_) para separarlas.
Definir el ámbito de uso del macro. El ámbito de uso queda determinado por el lugar en el que se guarde la macro grabada, lo cual depende a su vez de lo que haga la macro. Existen las tres posibilidades siguientes:
• Libro de macros personal: Es el lugar adecuado para guardar todos los macros que sean de tipo genérico, es decir que lleven a cabo tareas que no dependan de ningún libro en particular, como por ejemplo organizar en horizontal o vertical las ventanas abiertas existentes, marcar todas las celdas con errores en la hoja que este activa ese momento, dar una lista de los libros abiertos, etc.. Tam-bién es el lugar conveniente para aquellos macros que se usen muy a menudo y por lo tanto deben estar disponibles en cualquier sesión.
El libro de macros personal es un libro normal llamado PERSONAL.XLSB, donde Excel guarda to-dos los macros genéricos. Es un libro que se crea de forma automática al grabar por primera vez un macro genérico. Este libro queda oculto de forma predeterminada y se guarda en el directorio de inicio de Excel, usualmente llamado Inicio o XLSTART. Al activar Excel, este siempre mira en el di-rectorio de inicio y abre de forma automática todos los libros que haya en dicho directorio. Con ello PERSONAL.XLSB siempre estará disponible.
Por tanto si nos queremos llevar las macros grabadas en PERSONAL.XLSB a otro ordenador debe-remos copiar dicho archivo y ponerlo en el directorio de Inicio o XLSTART del ordenador de destino.
Si en el ordenador de destino existiese ya un PERSONAL.XLBS, deberíamos renombrarlo, abrirlo y copiar todas sus macros al nuevo PERSONAL.XLBS que tenemos.
Este libro. Esta opción es la adecuada para macros que realicen tareas específicas ligadas a una determinada hoja u hojas del libro activo. La macro se grabara dentro del libro actual y por tanto solo estará disponible si está abierto el libro que la contiene. Si la macro se necesitase en otros libros, entonces debería o bien guardarse en el "Libro de macros personal" o bien se deberá cargar este li-bro para que esté disponible.
• Libro nuevo. Esta es una opción mixta o intermedia entre las dos anteriores, es decir es recomen-dable cuando las macros se necesiten para varias situaciones distintas en distintos libros, pero a la vez no sean de uso muy frecuente o no se quiera sobrecargar el "Libro de macros personal". Las macros grabadas estarán disponibles siempre que el libro este cargado.
Definir el tipo de referencias a usar.
Por último, caso que el macro vaya a hacer operaciones o referencias a celdas o rangos, se debe definir si se desea emplear referencias absolutas o relativas, dado que después el macro reproducirá este compor-
Excel 2007
Página 104
tamiento al ejecutarlo. Durante la grabación no se puede cambiar de un tipo de referencias a otro. Para acti-var la grabación de referencias relativas debemos pulsar el botón "Usar referencias relativas" que está de-bajo del botón "Grabar macro".
• Referencias absolutas: El macro se reproducirá exactamente en las mismas celdas seleccionadas en la hoja en la que se ejecute. Para grabarlo debemos situarnos en cualquier celda distinta de la celda inicial donde empezaremos a grabar.
• Referencias relativas: En este caso si debemos posicionarnos inicialmente en la celda en la cual queremos empezar a hacer la tarea. Con este método se grabaran las referencias relativas a la cel-da de la esquina superior izquierda de la selección original y cuando se ejecute el macro, este ac-tuara sobre un rango de tamaño igual al definido en la grabación original, pero tomando como celda de inicio la que en ese momento este seleccionada.
Ejemplo de creación de una macro: Como ejemplo crearemos una macro sencilla cuya función será escribir en cualquier celda de cualquier hoja en la que nos posicionemos la fecha actual, que obtendrá del reloj del sistema. La fecha será estática, es decir constante, al contrario del resultado de la función HOY(), que se recálcula cada vez que se abre el libro que la contiene y por tanto siempre refleja la fecha más actual. Así mismo a la fecha le daremos fuente Arial, tamaño 12 puntos y efecto negrita.
Para grabar cualquier macro debemos acometer los siguientes pasos:
• Hacer visible la ficha Programador, si no lo estuviera y activarla.
• Si la macro fuese a operar con celdas, posicionarnos en la celda adecuada antes de empezar a grabar y determinar si usar referencias relativas o absolutas. En nuestro caso nos situaremos en cualquier celda, por ejemplo en B2, dado que vamos a usar referencias relativas, pues queremos que escriba la fecha en la celda que tengamos seleccionada en el momento de la ejecución del ma-cro
• En la ficha Programador, grupo Código, pulsar el botón "Usar referencias relativas" y después pul-sar la opción "Grabar macro", se visualizara el cuadro de diálogo "Grabar macro", donde especifica-remos los dos datos obligatorios:
a. "El nombre de la macro": FechaActualEstatica
b. "Guardar macro en": Libro de macros personal
• Después clicaremos el botón Aceptar, a partir de este momento comienza el proceso de grabación, toda operación que realicemos con el ratón o el teclado se transcribirá a código VBA.
• En la celda B2 escribimos: =HOY() y pulsamos la tecla ENTER.
• Volvemos a seleccionar la celda B2 y en su menú contextual elegimos copiar.
• Pulsamos el botón secundario del ratón para volver a hacer aparecer el menú contextual y elegimos Pegado especial.., Valores, de esta forma no se pegara la función, sino el valor que ha calculado.
• Pulsar la tecla ESC.
• A continuación nos situaremos en la ficha Inicio y seleccionaremos la fuente, tamaño y efecto deseado.
• En este punto ya hemos terminado nuestra macro, solo falta parar la grabadora, para lo cual volve-mos a la ficha Programador, grupo Código y pulsaremos la opción "Detener grabación", que solo es-ta activa durante el proceso de grabación de una macro.
Una vez grabado el macro se debe comprobar que funciona correctamente y después asociarlo a la barra de herramientas de acceso rápido o ejecutarlo mediante alguna de las otras opciones disponibles que se mencionan en secciones siguientes.
Opciones
Excel 2007
Página 105
A parte de los datos obligatorios existen dos opcio-nes que se pueden usar en el momento de la crea-ción del macro o a posteriori, a través de la opción Macros, cuadro de dialogo Macro, botón "Opcio-nes…", son las siguientes
• Descripción: Sirve como documentación de la macro. Se puede apuntar la fecha de creación y una breve descripción de la tarea o tareas que realiza. Puede ser útil espe-cialmente cuando la macro realiza varias ta-reas simultáneamente.
• Tecla de método abreviado: Sirve para asignar una combinación de teclas a la ma-cro de forma que al pulsarlas se ejecute. En un apartado posterior se comentaran con más detalle aspectos relacionados con la asignación de teclas.
Ambas opciones se pueden asignar o desasignar en el momento deseado en el mismo cuadro de dialogo "Opciones de la macro".
Ejecución de macros. Existen diversas formas de ejecutar un macro:
• Desde el cuadro de dialogo Macro.
• Mediante una combinación de teclas.
• Mediante un botón en la barra de herramientas de acceso rápido.
• Mediante un botón o control de formulario o un objeto gráfico en la propia hoja de cálculo.
Desde el cuadro de dialogo Macro. Para ejecutar una macro desde el cuadro de dialogo Macro hemos de posicionarnos en la ficha Programa-dor, grupo Código y clicar sobre el botón Macros. Una vez aparezca el dialogo Macro basta con seleccionar el nombre del macro en el cuadro de lista "Nombre de la macro" y pulsar el botón Ejecutar.
Esta forma no es muy versátil, está pensada esencialmente para probar los macros antes de asociarlos definitivamente a la barra de herramientas o a algún objeto o también para aquellos macros que se usen con poca frecuencia
Mediante una combinación de teclas Se puede asignar una combinación de teclas que comience con la tecla Ctrl+letra o Ctrl+Mayús+letra.
La combinación de teclas de método abreviado suplantará a cualquier combinación de teclas de método abreviado predeterminada equivalente en Excel mientras esté abierto el libro que contiene la macro. Si se quiere evitar esto se debe asignar alguna combinación que no use Excel. En la ayuda de Excel se puede obtener una lista de teclas de método abreviado que utilicen la tecla CTRL que ya están asignadas buscan-do el enunciado "Teclas de función y de método abreviado de Excel".
Mediante un botón en la barra de herramientas de acceso rápido Esta es una de las opciones más útiles si se trata de un macro que realice acciones genéricas aplicables a cualquier libro o ventana activa y se use con frecuencia.
Para colocar un macro en la barra de acceso rápido debemos clicar en el botón de Office, Opciones de Ex-cel, Personalizar y en la lista desplegable "Comandos disponibles en:", elegir la categoría Macros, lo que hará aparecer la lista de todos los macros disponibles.
Seleccionamos el macro deseado y clicando sobre el botón Agregar hacemos que quede incluido en la ba-rra asociado a un botón.
Opcionalmente podemos:
Excel 2007
Página 106
1. Desplazar de posición del botón al que está asociado, clicando sobre los botones arriba/abajo exis-tentes en la parte central derecha del cuadro de dialogo.
2. Asignarle un botón grafico adecuado que se aproxime e identifique la función que lleva a cabo el macro y ponerle una descripción significativa que se visualizara al pasar el cursor del ratón por en-cima del botón. Esto se realiza en el cuadro de dialogo que aparece al clicar sobre el botón "Modifi-car..".
Figura: Cuadro de dialogo "Opciones de Excel", Personalizar.
Mediante un botón o control de formulario o un objeto gráfico en la propia hoja de cálculo. Se puede asociar un macro a controles de formulario, o cualquier tipo de objeto grafico.
Esta posibilidad es útil en diversos contextos, por ejemplo: cuando se trata de macros con ámbito de libro (en este caso no tiene mucho sentido asociarlos a la "barra de herramientas de acceso rápido"), cuando queremos facilitar el uso del libro u hoja por parte de otras personas, o bien para resaltar el hecho de que existen macros asociados al libro abierto.
Podemos emplear dos clases de objetos para asociarles macros dentro de una hoja de cálculo:
• Controles de Formulario: Son los controles disponibles en ficha Programador, grupo Controles, op-ción Insertar "Controles de formulario". El más usual de ellos es el control "Botón (control de formu-lario)".
Excel 2007
Página 107
• Objetos gráficos. Dentro de esta categoría entra cualquiera de las formas existentes en la ficha In-sertar, grupo Ilustraciones, opción Formas, así como fotografías o ilustraciones y los propios gráfi-cos creados con Excel.
Asociar un macro a un control de formulario:
Por ejemplo asociarlo a un Botón. Debemos seguir los siguientes pasos:
• En la ficha Programador, grupo Contro-les, opción Insertar "Controles de formu-lario", elegir el control "Botón (control de formulario)": En ese momento el cursor se convertirá en una delgada cruz griega de color negro, que podremos observar al posicionarnos encima de la hoja de cálculo.
• Pulsar el botón primario del ratón y man-teniéndolo pulsado arrastrar hasta crear el botón del tamaño deseado, en ese momento soltar el botón del ratón. Se dibujara el botón y automáticamente aparecerá el cuadro de dialogo "Asignar macro"
• Seleccionar el nombre de la macro a asociar y pulsar el botón Aceptar.
Asociar un macro a una forma, fotografía u objeto grafico:
• Hacer clic con el botón secundario del ratón encima del objeto (forma, gráfico, etc.), lo que hará aparecer el menú contextual del mismo.
• Seleccionar y pulsar la opción "Asignar macro…", que hará aparecer el cuadro de dialogo "Asignar macro".
• Seleccionar el nombre de la macro a asociar y pulsar el botón Aceptar.
Entorno de programación Visual Basic para Aplicaciones (VBA) Para ver o modificar el código de programación generado al grabar una macro debemos abrir el Entorno de programación de Visual Basic para Aplicaciones (VBA), lo que se puede hacer de las dos formas siguientes desde ficha Programador > Código:
Excel 2007
Página 108
• La opción genérica: pulsando el botón "Visual Basic" que simplemente abre el entorno en el mismo punto y estado en que se cerró en la última ocasión.
• La opción especifica: pulsando el botón "Macros" que abre el cuadro de dialogo "Macro", donde al seleccionar un macro y pulsar el botón "Modificar" no posicionara exactamente en el código de di-cho macro.
La opción especifica es sin duda la más adecuada para no perdernos si tenemos muchos macros, además nos permite también eliminar los macros no deseados, o ejecutar paso a paso los erróneos para ver en qué punto fallan.
Nota: En caso de querer ver el código de un macro grabado en el "libro de macros personal" es necesario previamente desocultar el archivo PERSONAL.XLSB, caso contrario recibiremos el siguiente mensaje de Excel:
Excel 2007
Página 109
En nuestro ejemplo, si desocultamos el libro PERSONAL.XLSB, elegimos la macro FechaActualEstatica y presionamos el botón Modificar, al abrirse el entorno VBA, presentara un aspecto similar al de la figura si-guiente, en la cual podemos ver el código que ha generado la grabadora de macros.
El entorno VBA es un completo entorno de programación, con un amplio abanico de menús, ventanas y opciones. Aquí comentaremos las partes principales, para aprender a movernos dentro de el, así como el código generado por los macros o que nosotros podemos escribir directamente y algunas opciones de los menús. Las partes principales del entorno son las siguientes:
Ventana Explorador de Proyectos Es la ventana que aparece con el titulo "Proyecto - VBAProject", en ella aparecen tantos elementos (proyec-tos) como libros tengamos abiertos en ese momento. Para ver esta ventana caso de no estar visible basta con ir al menú Ver y seleccionar Explorador de proyectos.
Al expandir cualquiera de los elementos de un proyecto encontraremos dos o tres carpetas:
• Microsoft Excel Objetos: Son todas las hojas que contiene el libro, además del propio libro (This-Workbook). En cada una de estos elementos se puede escribir código especifico para el mismo.
• Formularios: Esta carpeta solo existe si se crean específicamente Formularios desde el menú In-sertar > UserForm. Los formularios son módulos especiales que pueden contener controles ActiveX para visibilizar por pantalla.
• Módulos: Contiene tantos módulos como macros grabemos. Aquí se guarda el código que puede ser usado por cualquier hoja del libro. Podemos crear manualmente Módulos propios o cambiar el nombre a los existentes.
Excel 2007
Página 110
Ventana de Propiedades Cada uno de los elementos de la ventana "Explorador de proyectos" tiene un conjunto de propiedades. La cantidad de propiedades depende del tipo de elemento. Las propiedades definen características del elemen-to, como por ejemplo título, tamaño de la letra, color del texto, etc.
Si esta ventana no apareciese visible debemos hacer lo mismo que en el caso del "Explorador de proyec-tos", ir al menú Ver y seleccionar Ventana propiedades.
Figura: Ventana Explorador de Proyectos Figura: Ventana Propiedades
Ventana de Código La ventana código es donde aparecen todas las instrucciones grabadas por una macro o introducidas ma-nualmente. Para cada objeto o elemento de un proyecto podemos tener abierta su propia ventana de códi-go, basta hacer doble clic sobre dicho elemento para que se abra su correspondiente ventana. Para gestio-nar u organizar las ventanas iremos al menú Ventana donde aparece la lista de todas ellas.
En la parte inferior izquierda de una ventana de código aparecen dos botones o iconos: "Ver procedimiento" y "Ver modulo completo" que nos permiten ver solo el código del procedimiento actual o todo el código exis-tente en la hoja, formulario o modulo en el que estemos. En este ultimo caso, para distinguir mejor donde empieza o acaba un procedimiento, entre uno y otro se inserta una línea horizontal que los separa, según se puede ver en la figura de la pagina siguiente.
Entendiendo el código VBA
Si miramos lo escrito por Excel al crear una macro, es decir el código de programación, podemos observar lo siguiente:
• Todos los macros o procedimientos están limitados por las palabras reservadas del lenguaje Sub y End Sud que indican el principio y el final del macro. Después de Sub viene el nombre del macro o procedimiento que debe de ser continuo (sin espacios entre palabras).
• Todo lo que aparece dentro después de un apostrofe ( ' ) es un comentario, es decir Excel lo omite al ejecutar el procedimiento. Los comentarios sirven para documentar mejor el propósito de los ma-cros o procedimientos. Por defecto aparecen en color verde.
• Todas las palabras que aparecen en color azul son palabras especificas del lenguaje VBA, tienen un significado especial y están reservadas por lo que no podemos usarlas para darle nombre a un macro.
Figura: Ventana de código de un modulo
Excel 2007
Página 111
Códigos comunes
Si grabamos macros y observamos su código es muy posible que nos encontremos con alguna de las líneas de código mas comunes que se presentan a continuación, a fin de entender para que sirven:
Trasladarse o seleccionar una celda
Range("B5").Select
Escribir en una celda
ActiveCell.FormulaRlCl="Importes de Ventas"
Letra Negrita
Selection.Font.Bold = True
Letra Cursiva
Selection.Font.Italic = True
Letra Subrayada
Selection Font.Underline= xlUndelineStyleSingle
Centrar texto
With Selection
.HorizontalAlignment =xlCenter
.VerticalAlignment=xlBottom
End With
Alinear a la derecha
With Selection
.HorizontalAlignment = xlRight
End With
Tipo de Letra (Fuente)
With Selection.Font
Excel 2007
Página 112
.Name = " Algerian"
End With
Tamaño de Letra (Tamaño de Fuente)
With Selection.Font
.Size = 14
End With
Copiar
Selection.Copy
Pegar
ActiveSheet.Paste
Cortar
Selection.Cut
Insertar una fila . .
Selection.EntireRow.Insert
Eliminar una fila
Selection.EntireRow.Delete
Insertar una columna . .
Selection.EntireColumn.Insert
Eliminar una columna
Selection.EntireColumn.Delete
Abrir cierto libro
Workbooks.Open Filename:="D:\Mis documentos\Ventas.xlsx"
Grabar un libro
ActiveWorkbook.SaveAs Filenarne := "C: \Mis documentos\Ventas.xlsx", FileFor-mat:=xlNormal, Password:="", ReadOnlyRecommended:=False,CreateBackup:=False
Trasladarse a una hoja determinada
Sheets("BD_Subtotales").Select
Movernos a la ventana siguiente
ActiveWindow.ActivateNext
Movernos a la ventana anterior
ActiveWindow.ActivatePrevious
Ocultar ciertas filas o columnas
Range("7:10,15:20,23:27").Select
Selection.EntireRow.Hidden = True
Range("E:J,L:Q,S:X,AA:AF,CG:CH").Select
Selection.EntireColumn.Hidden = True
Desocultar ciertas filas o columnas
Range("7:10,15:20,23:27").Select
Selection.EntireRow.Hidden = False
Range("E:J,L:Q,S:X,AA:AF,CG:CH").Select
Excel 2007
Página 113
Selection.EntireColumn.Hidden = False
Organizar las ventanas en vertical
Windows.Arrange xlArrangeStyleVertical
Organizar las ventana en horizontal
Windows.Arrange xlArrangeStyleHorizontal
Evitar que parpadee la pantalla al ejecutar algunas acciones
Application.ScreenUpdating = False
Volver la pantalla a su estado normal
Application.ScreenUpdating = True
Ejercicios: Macros con ámbito global (deben guardarse en Libro de macros personal)
1. Grabar una macro que organice todas las ventanas abiertas en mosaico horizontal. 2. Crear una macro que organice todas las ventanas abiertas en mosaico vertical. 3. Grabar una macro que, aplicada a cualquier hoja, disminuya el zoom al 25%. 4. Crear una macro que, aplicada a cualquier hoja, aumente el zoom al 130%. 5. Grabar una macro para desplazarse a la siguiente ventana o a la anterior. 6. Crea una macro que abra dos archivos a la vez, los organice en mosaico vertical posicionándose en
la celda A100 de la segunda hoja de cada uno de ellos. 7. Crear una macro de nombre CabeceraEmpresaAbsoluta usando referencias absolutas, empezando
por la celda B2,que comprenda el nombre y dirección de la empresa. Al hacerlo aplicarle diversas opciones de formato (color de fondo, letra mayor para el titulo, bordes alrededor, etc.). El resultado final debe presentar un aspecto similar al de la siguiente figura:
8. Crear una macro idéntica a CabeceraEmpresaAbsoluta pero usando referencias relativas. Darle en
nombre de CabeceraEmpresaRelativa. 9. Hacer un macro que escriba la fecha actual, pero de forma estática, es decir que la fecha no se ac-
tualice cada vez que se abre el libro. Al grabar la macro debemos usar la función HOY(). Se desea además que la fecha se escriba en la celda en la que estemos posicionadas en el mo-mento de la ejecución del macro y que le de formato negrita a dicha fecha.
10. Crear una macro que prepare las propiedades de formato del documento a nivel de impresión. Se deben fijar las siguientes características: Márgenes superior, inferior, izquierdo y derecha a 2 cm, Encabezado y Pie a 1 cm , Orientación del papel Horizontal, Tamaño del papel A5 y Alineación vertical centrada.
11. Supuesto tuviéramos que crear cada semana una determinada tabla que representa un planning semanal de horarios de atención al publico, que después es rellenada con el nombre de los pacien-tes, según la siguiente ilustración:
Excel 2007
Página 114
Queremos que pueda ser creada en cualquier libro y en una celda inicial no definida previamente. Evidentemente, si la tabla siempre estuviese en la misma posición, se podría utilizar una plantilla, pero como queremos poder crearla en cualquier celda, deberemos grabar el proceso de generación en una macro, llamémosle CrearTablaHorarios. Antes de empezar a grabar las acciones, debemos recordar que se debe grabar en el "Libro de ma-cros personal" y que se debe realizar una grabación relativa.
Macros con ámbito de libro (deben guardarse en Este libro ) 1. Macro para desplazarse a una hoja y a un punto especifico de dicha hoja. Asociarlo a un Botón
(control de formulario) que se pondrá en la hoja inicial a la que llamaremos Control. 2. Hacer un macro, de nombre OcultarColumnas, que oculte un conjunto de columnas disjuntas. Ha-
cerlo con ámbito "Este libro" y asociarlo a un Botón (control de formulario). 3. Hacer el macro reciproco a OcultarColumnas, es decir uno que las desoculte. Dicho macro tendrá
ámbito "Este libro", de nombre MostrarColumnas y será asociado a un Botón (control de formulario). 4. Hacer un macro que habrá un archivo determinado y asociarlo a un Botón (control de formulario)
dentro de un libro especifico. 5. Darle el nombre "FIN" a la celda A100 y después crear un macro que asociaremos a un Botón (con-
trol de formulario) de forma que al pulsarlo siempre se desplace a dicha celda, independientemente de que esta se haya movido de posición.