• Author / Uploaded
• Jaime Jimenez Ortega

Citation preview

EXCEL MEDIO-AVANZADO AVANZADO 2010 (R-05-09)

EXCEL 2010 NIVEL MEDIO MEDIO-AVANZADO AVANZADO

13

www.grupcief.com

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

1

EXCEL MEDIO-AVANZADO AVANZADO 2010

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

2

EXCEL MEDIO-AVANZADO AVANZADO 2010

TABLA DE CONTENIDO INTRODUCCIÓN AVANZADA UTILIZACIÓN EXCEL ......................................... ......... 10 Métodos abreviados de teclado ............................................................................................ ............................10 Teclas de función y de método abreviado ................................................................ ...........................................13 Teclas de método abreviado combinadas con CTRL ..................................................... ................................ 13 Teclas de Función ................................ ................................................................................................ ..................................................14

PERSONALIZACIÓN CINTA DE OPCIONES ...................................................... ...................... 15 Agregar una ficha o un grupo personalizado ................................................................ .......................................15 Agregar un grupo personalizado a una ficha ................................................................ .......................................16 Agregar comandos a un grupo personalizado ................................................................ ....................................16 Ocultar una ficha ................................ ................................................................................................ .......................................................17 Quitar una ficha personalizada, un grupo o un comando ................................................. ................................ 17 Cambiar el orden de las fichas o los grupos ................................................................ .........................................18 Restablecer la cinta de opciones ................................................................ ...........................................................18 Exportar una cinta de opciones personalizada ................................................................ ....................................18 Problemas de compatibilidad de personalización .............................................................. ..............................18 Personalizar la barra de acceso rápido ................................................................ .................................................19

ADMINISTRACIÓN AVANZADA DE ARCHIVOS ............................................... ............... 20 Cambiar el formato predeterminado al guardar ................................................................ .................................20 Formatos de archivo que admite Excel ................................................................ .............................................20 Formatos de archivo que no admite Excel 2007............................................................... ...............................21 Guardar un archivo en formato PDF PDF................................................................ .......................................................21 Establecer otra carpeta de trabajo predeterminada ......................................................... ................................ 23 Guardar archivos automáticamente ................................................................ .....................................................23 Recuperar versiones no guardadas en Excel 2010 ............................................................... ...............................24 Al cerrar sin guardar nuevos archivos ................................................................ .................................................24 Recuperar los archivos previamente guardados. ............................................................ ............................25 Trabajar con versiones de archivos autoguardados ............................................................ ............................26 Abrir y ver versiones anteriores de su archivo actual ....................................................... ................................ 26 Restablecer las versiones anteriores del archivo actual .................................................. ................................ 26 Guardar automáticamente una copia de seguridad ......................................................... ................................ 26 Reparar un archivo dañado ................................................................................................ ....................................27 Reparar manualmente un libro dañado ................................................................ ............................................27 Personalizar la lista de archivos recientemente utilizados ................................................... ................................ 28

TRABAJAR CON PLANTILLAS ............................................................................. ............. 29 Crear una plantilla ................................ ................................................................................................ .....................................................29

TIPOS DE REFERENCIAS ................................ ...................................................................................... ...................... 31 Referencias relativas................................ ................................................................................................ ..................................................31 Referencias absolutas ................................ ................................................................................................ ...............................................32 Referencias mixtas ................................ ................................................................................................ .....................................................32

TRABAJAR CON UN N LIBRO COMPARTIDO ...................................................... ...................... 34 Compartir un libro ................................ ................................................................................................ ......................................................34 Comprobar y actualizar los vínculos ................................................................ ...................................................36 Modificar el nombre de usuario en un libro compartido ..................................................... ................................ 38 Utilizar filtros y configuración de impresión originales ........................................................... ................................ 38 Quitar un usuario de un libro compartido ................................................................ ..............................................39 Resolver conflictos de cambios en un libro compartido ..................................................... ................................ 39 Control de cambios en un libro compartido ................................................................ .........................................40 Cómo funciona el control de cambios ................................................................ ..............................................41 Activar el control de cambios para un libro ................................................................ ......................................42

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

3

EXCEL MEDIO-AVANZADO AVANZADO 2010 Resaltar cambios ................................ ................................................................................................ ........................................................43 Detener el resaltado de cambios ................................................................ .......................................................44 Aceptar y rechazar cambios ................................................................................................ ...................................44 Ver la hoja de cálculo de historial ................................................................ ...........................................................45 Cambios que Excel no controla ni resalta ................................................................ .............................................46 Especificaciones y límites de los libros compartidos ............................................................. .............................47

PROTECCIÓN EN MICROSOFT EXCEL .............................................................. .............................. 48 Proteger un libro con contraseña ................................................................ ...........................................................48 Cifrar y establecer una contraseña para abrir un libro........................................................ ................................ 48 Establecer una contraseña para modificar un libro............................................................. .............................49 Cambiar una contraseña ................................ ................................................................................................ .........................................50 Quitar una contraseña ................................ ................................................................................................ ..............................................50

PROTEGER ELEMENTOS DE LA HOJA DE CÁLCULO ...................................... ................................ 51 Proteger elementos de la hoja de cálculo ................................................................ ............................................51 Desbloquear objetos gráficos. ................................................................................................ .................................52 Proteger elementos del libro ................................................................................................ ....................................52 Desbloquear áreas específicas de una hoja de cálculo .................................................... ................................ 53

CREAR ESQUEMAS ................................ ............................................................................................. ............................. 55 Crear un esquema de filas ................................ ................................................................................................ .......................................56 Crear un esquema de filas automáticamente ................................................................ .................................56 Crear un esquema de forma manual manual................................................................ .................................................57 Mostrar u ocultar datos de esquemas ................................................................ ....................................................59 Copiar datos esquematizados................................................................................................ .................................59

FUNCIONES ................................ ......................................................................................................... ......... 60 ¿Qué es una función? ................................ ................................................................................................ ...............................................60 Funciones matemáticas temáticas y trigonométricas: ................................................................ ...........................................60 Función ABS(número) ................................ ................................................................................................ ............................................61 Función Aleatorio() ................................ ................................................................................................ ................................................61 Función ALEATORIO.ENTRE() ................................................................................................ .................................62 Función COCIENTE() ................................ ................................................................................................ ..............................................62 Función Entero (número) ................................................................................................ ......................................63 Función EXP() ................................ ................................................................................................ ..........................................................63 Función GRADOS()................................ ................................................................................................ .................................................63 Función M.C.M ................................ ................................................................................................ .......................................................64 Función M.C.D ................................ ................................................................................................ ........................................................64 Función MULTIPLO.INFERIOR NFERIOR ................................................................................................ .................................65 Función MULTIPLO.SUPERIOR ................................................................................................ ................................65 Función POTENCIA() ................................ ................................................................................................ ..............................................66 Función REDOND.MULT................................ ................................................................................................ ..........................................66 Función REDONDEA.IMPAR ................................................................................................ ...................................67 Función REDONDEA.PAR ................................ ................................................................................................ .......................................67 Función REDONDEAR ................................ ................................................................................................ .............................................68 Función SIGNO() ................................ ................................................................................................ .....................................................68 Función SUMA() ................................ ................................................................................................ ......................................................69 Función SUMA.CUADRADOS ................................................................................................ ................................69 Función SUMAR.SI() ................................ ................................................................................................ ................................................70 Funciones de texto ................................ ................................................................................................ ....................................................72 Función CAR()................................ ................................................................................................ .........................................................72 Función CODIGO() ................................ ................................................................................................ ................................................72 Función CONCATENAR () ................................................................................................ ......................................73

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

4

EXCEL MEDIO-AVANZADO AVANZADO 2010 Función DERECHA() ................................ ................................................................................................ ...............................................73 Función ENCONTRAR ................................ ................................................................................................ .............................................74 Función HALLAR() ................................ ................................................................................................ ...................................................75 Función IGUAL()................................ ................................................................................................ ......................................................76 Función IZQUIERDA() ................................ ................................................................................................ ..............................................76 Función LARGO() ................................ ................................................................................................ ...................................................77 Función LIMPIAR(texto)................................ ................................................................................................ ..........................................77 Funciones MAYUSC() Y MINUSC() ................................................................ ........................................................77 Función Moneda() ................................ ................................................................................................ .................................................78 Función RECORTAR() ................................ ................................................................................................ .............................................78 Función REEMPLAZAR() ................................ ................................................................................................ ..........................................79 Función REPETIR()................................ ................................................................................................ ....................................................79 Función SUSTITUIR() ................................ ................................................................................................ .................................................80 Funciones de Búsqueda y Referencia ................................................................ ....................................................81 Función BUSCAR ................................ ................................................................................................ .....................................................81 Función CONSULTAH() ................................ ................................................................................................ ...........................................84 Función CONSULTAV() ................................ ................................................................................................ ...........................................85 Función COINCIDIR() ................................ ................................................................................................ .............................................88 Función ELEGIR()................................ ................................................................................................ .....................................................90 Función Índice() ................................ ................................................................................................ .....................................................91 Funciones lógicas ................................ ................................................................................................ .......................................................94 O (función O) ................................ ................................................................................................ ..........................................................94 Y (función Y) ................................ ............................................................................................................................ ............................95 SI (función SI) ................................ ................................................................................................ ...........................................................96 Función SI.ERROR................................ ................................................................................................ ....................................................97 Funciones Fecha y Hora................................ ................................................................................................ ............................................98 Función Ahora() ................................ ................................................................................................ .....................................................99 Función Dia.Lab() ................................ ................................................................................................ ...................................................99 Función Dia.Lab.INTL() ................................ ................................................................................................ .........................................100 Función Dias.Lab() ................................ ................................................................................................ ...............................................102 Función Dias.360() ................................ ................................................................................................ ................................................103 Función Fecha() ................................ ................................................................................................ ...................................................104 Función Num.de.Semana() ................................................................................................ ................................104 Funciones anidadas ................................ ................................................................................................ ................................................105

DIRECTRICES Y EJEMPLOS DE FÓRMULAS MATRIZ ....................................... ....... 107 Crear una fórmula de matriz sencilla ................................................................ ....................................................107 Crear una fórmula de matriz de una celda ................................................................ .........................................108 Desventajas de utilizar fórmulas de matriz ................................................................ ...........................................110 Constantes de matriz................................ ................................................................................................ ...............................................110 Crear constantes unidimensionales y bidimensionales .................................................. ................................ 111 Elementos que se pueden utilizar en las constantes ...................................................... ................................ 113 Poner nombre a las constantes de matriz................................................................ ........................................113 Solucionar problemas de las constantes de matriz ........................................................ ................................ 114 Ejemplo fórmulas de matriz básicas ................................................................ ......................................................114 Crear una matriz a partir de valores existentes ............................................................... ...............................115 Contar los caracteres de un rango de celdas ................................................................ ................................115 Buscar los n valores más pequeños de un rango ........................................................... ...........................115

IMPORTAR DATOS A EXCEL ................................ ............................................................................ ............ 117 Introducción ................................ ............................................................................................................................. .............................117 Utilizar el asistente nte para importar texto ................................................................ .................................................117

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

5

EXCEL MEDIO-AVANZADO AVANZADO 2010 Conexiones ................................ ............................................................................................................................... ...............................119 Importar datos de Word a Excel. ........................................................................................... ...........................120 Importar datos de Access ................................ ................................................................................................ ......................................121 Importar ar de una página Web. ............................................................................................... ...............................121 Importar de otros programas. ................................................................................................ ................................121

FUNCIONES ESPECÍFICAS DE BASES DE DATOS ............................................ ............ 122 BDCONTAR ................................ ................................................................................................................................ ................................122 BDCONTARA ................................ ............................................................................................................................. .............................124 BDEXTRAER ................................ ................................................................................................................................ ................................125 BDMAX ................................................................ ................................................................................................ .......................................126 BD PROMEDIO................................ ........................................................................................................................... ...........................126 BDSUMA ................................................................ ................................................................................................ .....................................128

GRÁFICOS ................................ ......................................................................................................... ......... 129 Tipos de Gráficos ................................ ................................................................................................ ......................................................131 Gráficos de Columnas ................................ ................................................................................................ ........................................131 Gráficos de barras ................................ ................................................................................................ ...............................................131 Gráficos de líneas ................................ ................................................................................................ ................................................132 Gráficos circulares ................................ ................................................................................................ ...............................................133 Gráficos XY (Dispersión) ................................ ................................................................................................ ......................................135 Gráficos de Superficie ................................ ................................................................................................ .........................................136 Elementos de los gráficos ................................ ................................................................................................ .......................................137 Otros Conceptos de los gráficos ................................................................ .......................................................138 Cambiar el diseño o estilo ................................ ................................................................................................ ......................................139 Aplicar car un diseño de gráfico predefinido ................................................................ ........................................139 Modificar el diseño de un gráfico ................................................................ .....................................................140 Agregar o quitar títulos o etiquetas................................................................ .......................................................140 Agregar un título al gráfico ................................................................................................ ................................140 Agregar títulos de eje ................................ ................................................................................................ ..........................................141 Vincular un título a una celda de hoja de cálculo ........................................................ ................................ 141 Agregar etiquetas de datos ............................................................................................... ...............................142 Cambiar el color de los valores del gráfico ................................................................ .........................................143 Mostrar u Ocultar una leyenda .............................................................................................. ..............................143 Cambiar o modificar los ejes ................................................................................................ .................................144 Mostrar u ocultar los ejes................................ ................................................................................................ .....................................144 Mostrar u ocultar los ejes secundarios ................................................................ ..............................................145 Ajustar las marcas de graduación y los rótulos ............................................................... ...............................145 Cambiar la alineación y orientación de los rótulos ........................................................ ................................ 146 Cambiar la escala del eje vertical (valores) en un gráfico ........................................... ................................ 146 Cambiar las escalas de los ejes ............................................................................................. .............................148 Cambiar la escala del eje horizontal (categorías) en un gráfico ................................148 Cambiar la escala del eje (series) de profundidad en un gráfico...............................149 Guardar un gráfico como plantilla ................................................................ .......................................................150 Problemas de compatibilidad de gráficos ................................................................ ..........................................151 Minigráficos ................................ ............................................................................................................................... ...............................152 Crear un minigráfico ................................ ................................................................................................ ............................................154 Agregar texto a un minigráfico ................................................................ .........................................................154 Personalizar minigráficos ................................ ................................................................................................ .....................................155

FILTROS AVANZADOS ................................ ...................................................................................... ...................... 157 Consideraciones utilización Filtros avanzados ................................................................ .....................................158 Utilizar el signo igual para escribir texto o un valor ......................................................... ................................ 158

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

6

EXCEL MEDIO-AVANZADO AVANZADO 2010 Considerar la distinción entre mayúsculas y minúsculas ............................................... ................................ 158 Utilizar nombres predefinidos.............................................................................................. ..............................158 Crear criterios utilizando una fórmula ................................................................ ...............................................159 Filtrar utilizando varios criterios en una columna en la que puede cumplirse cualquier criterio ................................ ................................................................................................ ........................................159 Filtrar utilizando varios criterios de varias columnas en las que deben cumplirse todos los criterios ................................................................ ......................................................160 Filtrar utilizando varios criterios en varias columnas en las que puede cumplirse cualquier criterio ................................................................ ......................................................161 Filtrar utilizando varios conjuntos de criterios en los que cada conjunto incluye criterios para varias columnas ................................................................ ....................................161 Filtrar utilizando criterios entre dos valores ................................................................ .......................................162

PLANIFICACION DE DATOS: ESCENARIOS .................................................... .................... 163 Definición ................................ ................................................................................................................................ ..................................163 Diseño escenarios ................................ ................................................................................................ ....................................................163 Combinar escenarios de otras hojas, libros o usuarios ....................................................... ................................ 166 Ejercicio práctico escenarios ................................................................................................ .................................166

TABLAS DINÁMICAS ................................ ......................................................................................... ......................... 167 Formas de trabajar con un informe de tabla dinámica .................................................... ................................ 168 Revisar origen de datos antes de empezar ................................................................ .........................................169 Crear una tabla dinámica................................ ................................................................................................ ......................................169 Eliminar una tabla dinámica. ................................................................................................ .................................173 Obtener información sobre la lista de campos de tabla dinámica ................................174 Cómo funciona la lista de campos de tabla dinámica ................................................ ................................ 174 Organizar los campos................................ ................................................................................................ ..............................................174 Quitar campos ................................ ................................................................................................ .........................................................175 Cambiar la vista de lista de campos ................................................................ ....................................................176 Seleccionar datos en un informe de Tabla Dinámica ....................................................... ................................ 176 Seleccionar celdas individuales ................................................................ ........................................................177 Seleccionar un informe orme completo ................................................................ .....................................................177 Seleccionar todos los elementos de un campo a la vez .............................................. ................................ 177 Seleccionar etiquetas de elementos, datos o ambos ................................................... ................................ 178 Seleccionar varios elementos ............................................................................................ ............................178 Seleccionar leccionar subtotales y totales ................................................................ ........................................................178 Mostrar u ocultar los detalles de los datos ................................................................ ...........................................178 Expandir o contraer diferentes niveles de detalle .......................................................... ..........................178 Cálculos lculos y funciones en los campos resumen resumen................................................................ .....................................179 Mostrar u ocultar totales generales para todo el informe ................................................. ................................ 181 Cambiar opciones predeterminadas totales generales ................................................... ................................ 182 Mostrar datos como…................................ ................................................................................................ .............................................182 La segmentación de datos para filtrar datos ................................................................ ......................................183 ¿Qué es la segmentación de datos? ................................................................ ...............................................183 Aplicar formato a una segmentación de datos ............................................................. .............................185 Configuración de la segmentación de datos................................................................. ................................185 Compartir segmentaciones de datos entre tablas dinámicas ..................................... ................................ 186 Usar una segmentación de datos desde otra tabla dinámica .................................... ................................ 187 Desconectar o eliminar una segmentación de datos ................................................... ................................ 188 Crear CAMPOS CALCULADOS ............................................................................................... ...............................189 Consolidar varios rangos en una tabla dinámica............................................................... ...............................190 Ordenar datos de una tabla dinámica................................................................ ................................................192

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

7

EXCEL MEDIO-AVANZADO AVANZADO 2010 Agrupar y Desagrupar elementos de la tabla dinámica .................................................. ................................ 193 Ejemplo 1: Agrupar Elementos desordenados en una tabla dinámica ......................194 Ejemplo 2: Agrupar Elementos desordenados en una tabla dinámica ......................194 Extractos rápidos ................................ ................................................................................................ ......................................................195 Informes de gráficos áficos dinámicos ............................................................................................. .............................196 Crear un gráfico dinámico ................................................................................................ .................................197 Comparar un informe de tabla dinámica y de gráfico dinámico................................... ................................ 199 Diferencias entre gráfico o estándar y gráfico dinámico ..................................................... ................................ 199 ELIMINAR UN INFORME DE GRÁFICO DINÁMICO ................................................................ .................................200 Información general sobre el procesamiento analítico en línea (OLAP)........................200 Diferencias de características entre datos de origen OLAP y los que no son OLAP ................................ ................................................................................................................................ ..................................202 Software necesario para acceder a datos OLAP .......................................................... ..........................204 Trabajar con archivos de cubo sin conexión ................................................................ ..................................205 Utilizar el Asistente para cubos sin conexión ................................................................ ....................................205 Crear un archivo de cubo sin conexión de una base de datos de servidor OLAP ................................ ............................................................................................................................ ............................206 Incluir datos distintos en un archivo de cubo sin conexión ........................................... ................................ 208 Eliminar un archivo de cubo sin conexión ................................................................ .......................................208 Funciones de CUBO ................................ ................................................................................................ .................................................209 Función IMPORTARDATOSDINAMICOS ................................................................ ..................................................209

HERRAMIENTAS DE AUDITORIA Y VALIDACIÓN DE DATOS ........................ 211 Comprobación de errores ................................ ................................................................................................ ......................................213 Corregir un valor de error................................ ................................................................................................ ........................................214 Inspeccionar una fórmula a través de la ventana Inspección ........................................ ................................ 215 Evaluar una fórmula anidada paso a paso ................................................................ .........................................216 Relaciones entre fórmulas y celdas ................................................................ ......................................................218 Validación de datos ................................ ................................................................................................ ................................................219

ENLAZAR Y CONSOLIDAR HOJAS DE TRABAJO ........................................... ........... 224 Enlazar hojas de trabajo. ................................ ................................................................................................ ........................................224 ¿Cómo crear fórmulas de referencias externas? ............................................................... ...............................224 Consolidar hojas de trabajo. ................................................................................................ ..................................225 Consolidar datos en varias hojas de cálculo ................................................................ ......................................226 Consolidar por posición ................................ ................................................................................................ ......................................226 Consolidar por categorías ................................................................................................ ..................................227 Consolidar por fórmula................................ ................................................................................................ ........................................227

BUSCAR OBJETIVO ................................ ........................................................................................... ........................... 228 SOLVER ................................................................ .............................................................................. .............. 230 Desplazarse por las soluciones de prueba en Solver .......................................................... ..........................234 Guardar o cargar un modelo de problema ................................................................ ........................................234 Cambiar la forma en que Solver encuentra soluciones .................................................... ................................ 235 Ejemplo 1: Solver ................................ ................................................................................................ ......................................................235 Ejemplo 2: Solver ................................ ................................................................................................ ......................................................238 Ejemplo 3 Solver ................................ ................................................................................................ .......................................................240

MACROS ................................ ........................................................................................................... ........... 242 Crear una macro automáticamente ................................................................ ...................................................244 Grabación de una macro sencilla ................................................................ ....................................................245 Ejecutar una macro ................................ ................................................................................................ .................................................246 Crear una macro manualmente ........................................................................................... ...........................247 Conceptos básicos de programación. ................................................................ ............................................247

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

8

EXCEL MEDIO-AVANZADO AVANZADO 2010 El entorno del editor de Visual basic ................................................................ .....................................................249 Guardar archivos con Macros ............................................................................................... ...............................251

Usar Office Excel 2010 con versiones anteriores de Excel ........................ 252 Características no compatibles con version versiones anteriores ................................................. ................................ 254 Características de hoja de datos no compatibles ......................................................... ................................ 255 Características de tablas de Excel no compatibles ....................................................... ................................ 255 Características de tablas dinámicas no compatibles ................................................... ................................ 256 Características de ordenación y filtrado no compatibles ............................................ ................................ 257 Características de fórmulas no compatibles ................................................................ ...................................257 Características de formatos condicionales no compatibles ........................................ ................................ 258 Características de gráficos no compatibles compatibles................................................................ ....................................259 Características de personalización no compatibles ...................................................... ................................ 259 Características de colaboración no compatibles ......................................................... ................................ 259 Convertir un libro al formato de archivo de Excel 2010 ..................................................... ................................ 259

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

9

EXCEL MEDIO-AVANZADO AVANZADO 2010

INTRODUCCIÓN AVANZADA UTILIZACIÓN EXCEL

Una de las aplicaciones informáticas más utilizadas en las empresas son las hojas de cálculo, que permiten al usuario manipular manipular,, calcular y realizar diferentes operaciones con los datos e información. Microsoft Excel es una poderosa herramienta de cálculo, gestión y análisis de datos, además de un instrumento polivalente en organización y presentación de la información. El objetivo básico de las hojas de cálculo es proporcionar un entorno entorn simple y uniforme para generar tablas de números y a partir de ellos obtener mediante fórmulas nuevos valores. Las hojas de cálculo permiten a los usuarios manipular grandes cantidades de información de forma rápida y fácil que permiten ver los efectos d de e distintas suposiciones. El área de aplicación más importante ha sido hasta ahora el análisis profesional y ha servido para desarrollar modelos de gestión, entre los que se puede citar la planificación de proyectos y el análisis financiero, el análisis contable, ontable, el control de balances, la gestión de personal, etc.

Métodos abreviados de teclado Microsoft Excel 2010 incorpora nuevos métodos y más sencillos para la utilización del teclado. En usuarios avanzados, la utilización de métodos abreviados para p activar herramientas y realizar acciones, puede suponer un ahorro importante de tiempo. Una característica importante del nuevo diseño es la Cinta de opciones, que aparece en la parte superior de la ventana del programa y reemplaza los menús y las ba barras rras de herramientas de versiones anteriores, es que los comandos y las características ahora se agrupan juntos para reflejar la forma en la que se usan usan.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

10

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 Existen tres partes principales en la cinta de opciones:

fichas

Grupos

Comando

Las fichas, que están en la parte superior de la cinta. Cada una representa un área de actividad. Los grupos, que son conjuntos de comandos relacionados que se muestran en las fichas. Los grupos aglutinan todos los comandos que se necesitan para realizar una tarea concreta. Los comandos, que están organizados en grupos. Un comando puede ser un botón, un menú o un cuadro en el que se especifica información. La cinta de opciones se adaptará dependiendo de en qué se esté trabajando, para mostrar los comandos que se necesitan para realizar esa tarea. Por ejemplo, si está trabajando con un gráfico en Excel, la cinta de opciones mostrará los comandos necesarios para trabajar con gráficos. Si no es ese el caso, esos comandos no estarán visibles. Se incluyen además dos características más: en el que se encuentran los comandos El comando Archivo básicos que tratan los archivos en sí, como abrir, guardar e imprimir. La barra de herramientas de acceso rápido,, situada encima de la cinta de opciones y que de forma predeterminada, contiene los comandos más utilizados como,, Guardar, Deshacer y Repetir. La barra de acceso rápido permite personalizarse.

Hay dos formas de utilizar el teclado. • Una combinación de teclas para obtener acceso a las fichas y los comandos de la pantalla.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

11

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 •

Una combinación de teclas directa no relacionada con la cinta de opciones.

Al presionar la tecla ALT se activarán pequeñas etiquetas identificadores con las sugerencias de teclas de todas las fichas.

o

Según la muestra de la imagen superior, para activar la ficha Insertar será necesario presionar la combinación Alt+B.

Las sugerencias de teclas constituyen una forma relevante de usar métodos abreviados de teclado, porque son un aviso constante en pantalla de las teclas que se deben presionar. No es necesario memorizarlas. Si obtiene las sugerencias de teclas de una ficha no apropiada, presionar ESC para ver de nuevo los identificadores de sug sugerencias erencias de teclas de la ficha deseada.

NOTA: La mayoría de accesos directos de versiones anteriores, siguen funcionando. Sin embargo, se deben tener memorizados ya que no hay recordatorios en pantalla de las teclas a presionar.

Una vez seleccionada la ficha, presionar la tecla de referencia para ejecutar cada comando.. Por ejemplo en la imagen inferior, la tecla T insertará una tabla o las teclas SM abrirán el cuadro de diálogo para insertar Autoformas.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

12

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

Teclas de función y de método abreviado Teclas de método abreviado combinadas con CTRL A continuación se muestran algunas de las combinaciones con Ctrl. Tecla CTRL+MAYÚS+( CTRL+MAYÚS+) CTRL+MAYÚS+& CTRL+MAYÚS_ CTRL+E CTRL+MAYÚS+$ CTRL+MAYÚS+% CTRL+MAYÚS+^ CTRL+MAYÚS+# CTRL+MAYÚS+@ CTRL+MAYÚS+!

Descripción Muestra las filas ocultas de la selección. Muestra las columnas ocultas de la selección. Aplica el contorno a las celdas seleccionadas. Quita el contorno de las celdas seleccionadas. Aplica el formato de número General. Aplica el formato Moneda con dos decimales Aplica el formato Porcentaje si sin decimales. Aplica el formato numérico Exponencial con dos decimales. Aplica el formato Fecha con el día, mes y año. Aplica el formato Hora con la hora y los minutos e indica a.m. o p.m. Aplica el formato Número con dos decimales, separador de miles y signo menos (-)) para los valores negativos. CTRL+MAYÚS+* Selecciona el área actu actual alrededor de la celda activa. En una tabla dinámica, selecciona todo el informe de tabla dinámica. CTRL+MAYÚS+: Inserta la hora actual. CTRL+MAYÚS+" Copia el valor de la celda situada sobre la celda activa en la celda o en la barra de fórmulas. CTRL+MAYÚS+Signo Muestra el cuadro de diálogo Insertar para insertar celdas en blanco. más( +) CTRL+Signo menos (-) Muestra el cuadro de diálogo Eliminar para eliminar las celdas seleccionadas. CTRL+; Inserta la fecha actual. ALT+º Cambia entre mostrar valores de celda y mostrar fórmulas fórmulas.. CTRL+' Copia en la celda o en la barra de fórmulas una fórmula de la celda situada sobre la celda activa. CTRL+1 Muestra el cuadro de diálogo Formato de celdas. CTRL+2 Aplica o quita el formato de negrita negrita. También funciona Ctrl+N CTRL+3 Aplica o quita el formato de cursiva. También funciona Ctrl+K CTRL+4 Aplica o quita el formato de subrayado. También funciona Ctrl+S CTRL+5 Aplica o quita el formato de tachado. CTRL+6 Cambia entre ocultar objetos, mostrarlos o mostrar marcadores de los objetos. CTRL+8 Muestra u oculta símbolos de esquema. CTRL+9 Oculta filas seleccionad seleccionadas. CTRL+0 Oculta columnas seleccionadas. CTRL+E Selecciona cciona toda la hoja de cálculo. Si la hoja de cálculo contiene datos, CTRL+E selecciona la región actual. Si presiona CTRL+E una segunda vez, se selecciona la región actual y sus filas de resumen. Presionando CTRL+E por tercera vez, se selecciona toda la hoja de cálculo. CTRL+C Copia las celdas seleccionadas. CTRL+C presionado 2 veces muestra el Portapapeles. CTRL+J Utiliza el comando Rellenar hacia abajo para copiar el contenido y el formato de la a celda situada más arriba de un rango seleccionado a las celdas de abajo.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

13

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 Tecla CTRL+B CTRL+I CTRL+L

Descripción Muestra el cuadro de diálogo Buscar y reemplazar con la ficha Buscar seleccionada Muestra el cuadro de diálogo Ir a. F5 también muestra este cuadro de diálogo. Muestra el cuadro de diálogo Buscar y reemplazar con la ficha Reemplazar seleccionada. CTRL+ALT+K Muestra el cuadro de diálogo Insertar hipervínculo para hipervínculos nuevos o el cuadro de diálogo Modificar hipervínculo para hipervínculos existentes seleccionados. CTRL+U Crea un nuevo libro en blanco. CTRL+A Muestra el cuadro de diálogo Abrir para abrir o buscar un archivo. CTRL+P Muestra el cuadro de diálogo Imprimir. CTRL+D Utiliza el comando Rellenar hacia la derecha para copiar el contenido y el formato de la celda situada más a la izquierda de un rango seleccionado a las celdas de la derecha. CTRL+G Guarda el archivo activo con el nombre de archivo, la ubicación y el formato de archivo actuales. CTRL+F Muestra el cuadro uadro de diálogo Crear tabla. CTRL+V Inserta el contenido del Portapapeles en el punto de inserción y reemplaza cualquier selección. Disponible solamente después de haber cortado o copiado un objeto, texto o el contenido de una celda. CTRL+R Cierra la ventana del libro seleccionado. CTRL+X Corta las celdas seleccionadas. CTRL+Y Repite el último comando o acción, si es posible. CTRL+Z Utiliza el comando Deshacer para invertir el último comando o eliminar la última entrada que escribió. CTRL+MAYÚS+Z utiliza tiliza los comandos Deshacer o Rehacer para invertir o restaurar la última corrección automática cuando se muestran las etiquetas inteligentes de Autocorrección.

Teclas de Función A continuación se muestran algunas de las acciones a realizar con las teclas de función Tecla Descripción F1 Muestra el panel de tareas Ayuda de Microsoft Office Excel. F2 Modifica la celda activa y coloca el punto de inserción al final del contenido de la celda. También mueve el punto de inserción a la barra de fórmulas cuando la edición en una celda está desactivada. F3 Muestra el cuadro de diálogo Pegar nombre. F4 Repite el último comando o acción, si es posible. F5 Muestra el cuadro de diálogo Ir a.. F6 Cambia entre la hoja de cálculo, la cinta de opciones, el panel de tareas y los controles de Zoom. En una hoja de cálculo que se ha dividido, F6 incluye los paneles divididos cuando se alterna entre los paneles y el área de la cinta de opciones. F7 Muestra el cuadro de diálogo Ortografía para revisar la ortografía de la hoja de cálculo activa o del rango seleccionado. F8 Activa o desactiva el modo extendido. En el modo extendido aparece Selección extendida en la línea de estado y las teclas de direc dirección ción extienden la selección. F9 Calcula todas las hojas de cálculo de todos los libros abiertos. MAYÚS+F9 calcula la hoja de cálculo activa. F10 Activa o desactiva la información de los métodos abreviado de teclado. MAYÚS+F10 muestra el menú contextual de un elemento seleccionado. F11 Crea un gráfico a partir de los datos del rango actual. F12 Muestra el cuadro de diálogo Guardar como

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

14

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

PERSONALIZACIÓN CINTA DE OPCIONES Microsoft Excel 2010 permite la personaliza personalización ción de la cinta de opciones y crear fichas y grupos a la medida de cada usuario, según los comandos utilizados con más frecuencia. No es posible cambiar las fichas y los grupos predeterminad predeterminados. os. En la lista Personalizar la cinta de opciones opciones, las fichas y grupos personalizados incluyen la palabra (personalizado) o (personalizada) después del nombre, aunque ésta no aparece en la cinta de opciones.

Agregar una ficha o un grupo personalizado Para agregar una na ficha personalizada y un grupo personalizado, haga clic en Nueva ficha. 1. Aunque para acceder al cuadro de diálogo para personalizar la cinta de opciones, puede acceder mediante el menú Archivo + Opciones, la forma más rápida es mediante el botón derecho cho sobre cualquier zona de la Cinta de opciones. 2. Escoger del menú contextual 3. Se abrirá el cuadro de diálogo Personalizar Cinta de opciones. 4. Clicar sobre el botón Nueva ficha , situado en la parte inferior derecha. 5. Se añadirá un nuevo concepto con el nombre Nueva ficha personalizada

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

15

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 6. Seleccionar Nueva ficha (personalizada) y presionar el botón Cambiar Nombre

,

7. Teclear el nuevo nombre en el cuadro de diálogo y presionar aceptar.

Este método para el cambio de Nombre puede utilizarlo para realizar el cambio de nombre sobre cualquier ficha, incluso las fichas predeterminadas de Excel 2010. En caso de necesitar volver a los valores predeterminados, clicar sobre el botón Restablecer.

Agregar un grupo personalizado a una ficha Puede agregar un grupo personalizado a una ficha personalizada o a una ficha predeterminada. 1. En el cuadro de diálogo Personalizar la cinta de opciones seleccione la ficha a la que desea agregar un grupo. 2. Clicar sobre el comando Nuevo grupo. 3. Cambiar el nombre del Nuevo grupo (personalizado) mediante el botón Cambiar nombre nombre. 4. Se abrirá el cuadro de diálogo para entrar el nuevo nombre o para agregar un icono que represente al nuevo grupo personalizado. 5. Si necesario crear otro nuevo grupo, repetir los pasos anteriores.

Agregar comandos a un grupo personalizado Sólo se pueden agregar comandos a un grupo personalizado que esté en una ficha personalizada o predeterminada. No es posible agregar comandos a un grupo predeterminado. Sólo se puede cambiar el nombre a los comandos agregados a grupos personalizados. Observación: En el cuadro de diálogo Personalizar la cinta de opciones, opciones las fichas y grupos personalizados incluyen la palabra (personalizado) o (personalizada) da) después del nombre, aunque ésta no aparecerá en la cinta de opciones. 1. En la ventana Personalizar la cinta de opciones seleccione el grupo personalizado al que desea agregar comandos.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

16

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 2. En la zona Comandos disponibles en: abrir el desplegable, desplegable para seleccionar la lista que contiene dicho comando, podrá escoger entre la ficha en cuestión si la conoce, los comandos más utilizados o Todos los comandos. 3. Seleccionar el comando en la parte izquierda de la venta 4. Clicar sobre el botón Agregar. 5. Presionar Aceptar para guardar las actualizaciones. En la imagen inferior muestra la nueva ficha creada Grupcief,, el nuevo grupo creado Cálculos mensuales y el comando añadido.

Ocultar una ficha Microsoft Excel permite ocultar fichas personalizadas y predeterminadas, predeterminad pero sólo se pueden quitar fichas personalizadas. 1. En la ventana Personalizar la cinta de opciones opciones, desactivar la casilla de verificación junto a la ficha predeterminada o personalizada que desea ocultar. 2. Para ver y guardar sus personalizaciones, haga clic en Aceptar. Aceptar

Quitar una ficha personalizada personalizada,, un grupo o un comando 1. En la ventana Personalizar la cinta de opciones seleccionar el elemento a eliminar 2. Haga clic en el botón de comando Quitar. 3. Presionar Aceptar para guardar las actualizaciones.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

17

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

Cambiar el orden de las fichas o los grupos 1. En la ventana Personalizar la cinta de opciones clicar en la ficha o grupo a mover. 2. Clicar en las flechas Subir o Bajar hasta obtener el orden deseado. 3. Para ara ver y guardar sus personalizaciones, haga clic en Aceptar ptar.

Restablecer la cinta de opciones Microsoft Excel permite restablecer stablecer a su estado original todas las fichas de la cinta de opciones o sólo las seleccionadas. Si restablece todas las fichas de la cinta de opciones, también restablecerá la barra de herramientas de acceso rápido, que mostrará solo los comandos predeterminados. 1. Desde la ventana Personalización cinta de opciones 2. Abrir el desplegable del botón Restablecer 3. Escoger la opción necesaria

Exportar una cinta de opciones personalizada Puede exportar las personalizaciones de la cinta de opciones y la barra de herramientas de acceso rápido a un archivo que puede ser importado y usado por otro usuario o en otro equipo. • En la ventana Personalizar la cinta de opciones, clicar en el botón Importar mportar o exportar. exportar

Problemas de compatibilidad de personalización Al personalizar la Cinta de Opciones de Excel 2010, es posible que el e Comprobador de compatibilidad encuentre uno o más problemas de compatibilidad relacionados con dicha personalización. En la versión Excel 2007no es posible personalizarla. Es posible que los problemas que causan una pérdida menor de fidelidad deban ser resueltos o no antes de guardar el libro. No se

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

18

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 perderán datos ni funcionalidad, pero es probable que el libro no tenga el mismo aspecto al abrirlo con una versión anterior de Microsoft Excel o que no funcione exactamente de la misma forma. Problemas que producen una pérdida menor de fidelidad

Problema

Solución

Este libro contiene una barra de herramientas de acceso rápido personalizada y/o partes de interfaz de usuario personalizadas no compatibles con versiones anteriores de Excel. Estas características personalizadas no estarán disponibles en versiones anteriores de Excel.

Qué significa Dado que la Interfaz de usuario de Microsoft Office Fluent en Excel 2010 y Excel 2007 es muy diferente de los menús y las barras de herramientas en Excel 97-2003, 2003, cualquier personalización de la Barra de herramientas de acceso rápido realizada en Excel 2010 no está disponible en Excel 97-2003. 2003, se pueden Qué hacer En Excel 97-2003, agregar comandos personalizados similares a las barras de herramientas y los menús

Personalizar la barra de acceso rápido La barra de herramientas de acceso rápido que contiene los comandos más utilizados, también puede personalizarse. El funcionamiento es el mismo que para la cinta de opciones, pero deberá seleccionar la opción Personalizar barra de herramientas de acceso rápido,, en el menú contextual, al clicar co con n el botón derecho sobre cualquier zona de la Cinta de opciones. Permite agregar botones con otros comandos y moverla a dos posibles ubicaciones.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

19

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

ADMINISTRACIÓN AVANZADA DE ARCHIVOS Cambiar el formato predeterminado al guardar En Microsoft Office Excel 2010 2010,, la extensión de los archivos de forma predeterminada es .xlsx y para los libros que contienen macros .xlsm. .xlsm En caso de trabajar con versiones antiguas de Excel, podrá grabar los archivos en modo compatibilidad, aunque algu algunas nas características, características datos o formatos pueden perderse. Todos los nuevos libros se guardarán en el formato de archivo seleccionado al menos que se especifique un formato diferente en el cuadro de diálogo Guardar como al guardar un libro. Las opciones de formato que ofrece el desplegable son corresponden a la imagen de la izquierda izquierda.

Formatos de archivo que admite Excel A continuación se muestran los formatos de archivo más frecuentes que admite Microsoft Excel 2010. FORMATO

EXTENSIÓN

DESCRIPCIÓN

Libro de Excel

.xlsx

Formato de archivo predeterminado de Excel 2010. 2010

Libro código Excel

.xlsm

Formato de archivo de Excel 2010 habilitado para macros.

Libro de binario

.xlsb

Formato de archivo binario (BIFF12)

Plantilla

.xltx

Formato de archivo predeterminado para una plantilla No puede almacenar código de macros de VBA ni hojas de macros de Excel 4.0 (.xlm).

Plantilla (código)

.xltm

Formato de plantilla habilitado para macros

Excel

Libro de Excel 97Excel 2003

.xls

El formato de archivo binario (BIFF8) de Excel 97 - Excel 2003.

Plantilla de Excel 97- Excel 2003

.xlt

El formato de archivo binario (BIFF8) de Excel 97 - Excel 2003 para una plantilla de Excel.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

20

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

Formatos de archivo que no admite Excel 2007 Los siguientes formatos de archivo ya no se admiten en Office Excel 2007. No se podrán abrir o guardar archivos en estos formatos. FORMATO

Gráfico de Excel

EXTENSIÓN

.xlc

IDENTIFICADORES DE TIPO IPO DE PORTAPAPELES Formatos de archivo antiguos de Excel 2.0, 3.0 y 2.x

WK1, FMT, WK2, WK3, .wk1, .wk2, FM3, WK4 .wk4, .wks

.wk3, Formatos de archivo Lotus 1-2-3 3 (todas las versiones)

Microsoft Works

.wks

Formato de archivo Microsoft Works (todas las versiones)

DBF 2

.dbf

Formato de archivo DBASE II

WQ1

.wq1

Formato de archivo de Quatro Pro para MS-DOS

WB1, WB3

.wb1, .wb3

Quattro Pro 5.0 y 7.0 para Windows.

Guardar un archivo en formato PDF Microsoft Excel permite guardar archivos en Formato de Documento Portátil (PDF, Portable Document Format), que es un formato habitual para compartir documentos. El formato PDF es un archivo electrónico con diseño fijo que conserva el formato del documento, que permite compartir archivos y además tienen poca memoria. Este formato garantiza que, cuando el archivo se ve en línea o se imprime, conserva exactamente el formato deseado y que los datos del archivo no se pueden cambiar fácilmente. El formato PDF también es útil para los documentos que se reproducirán mediante métodos comerciales de impresión. Para ver un archivo ivo PDF, debe tener instalado un lector de archivos PDF como por ejemplo Adobe Reader.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

21

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 Para guardar un archivo como PDF PDF: 1. Clicar en el botón Inicio y escoger la opción Guardar y enviar 2. Escoger la opción Crear documento PDF/XPS A continuación, hacer clic ic sobre el botón Crear documento PDF/XPS, situado en la zona de la derecha, del cuadro de diálogo.

Opciones 3. Se podrán especificar más opciones para el archivo en Opciones, situado en la parte inferior derecha del cuadro de diálogo

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

22

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 También puede realizar esta operación escogiendo en el desplegable el tipo de archivo desde el cuadro de diálogo Guardar como o desde el menú Archivo

Establecer otra carpeta de trabajo predeterminada 1. Hacer clic en el botón Inicio

y, a continuación, clicar en

Opciones de Excel 2. Escoger Guardar. 3. En la zona Guardar libros libros, en el recuadro Ubicación de archivo predeterminada, escribir la nueva ruta de acceso.

Guardar archivos automáticamente Microsoft Excel puede grabar regular regularmente mente cualquier archivo, para poder recuperarlo en caso de producirse un contratiempo durante la ejecución del mismo y que se cierre antes de poder grabar. Cada vez que se guarde el libro, una nueva copia de seguridad reemplazará a la copia de seguridad ex existente Al ejecutar la herramienta Autorrecuperación: • Se guardará el archivo cada x tiempo, según la necesidad del usuario, con lo que el archivo estará actualizado. • Otra ventaja adicional de habilitar la función Autorrecuperación consiste en que algunos aspectos del estado del programa se recuperan cuando se reinicia el programa después de haberse cerrado de manera anómala Para habilitar y ajustar las herramientas Autorrecuperar y Autoguardar: 1. Seleccionar el botón Opciones, desde la ficha Archivo 2. Hacer clic en Guardar

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

23

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 3. Activar la casilla de verificación Guardar información de Autorrecuperación cada x minutos. 4. En el campo minutos minutos, especificar la frecuencia con la que desea que el programa guarde los datos y el estado del programa. 5. En caso necesario, puede cambiar la dirección donde se guardará automáticamente una versión de los archivos, desde el recuadro Ubicación de archivo con Autorrecuperación

6. En caso necesario, puede recuperar las versiones no guardadas, al activar la casilla illa de verificación Conservar la última versión autoguardada cuando se cierra sin guardar. Para que se muestre esta opción, debe tener activada la Autorrecuperación. • •

Cuanta mayor sea la frecuencia, mayor será la cantidad de información que se podrá recup recuperar erar si se produce un problema. problema El comando Autorrecuperación no reemplaza el guardado regular de los archivos. Si no guarda un archivo de recuperación después de abrirlo, se eliminará el archivo y se perderán los cambios no guardados. Si guarda el archivo de recuperación, este reemplazará al archivo original, a menos que especifique un nuevo nombre de archivo.

Recuperar versiones no guardadas en Excel 2010 Para que estas características funcionen, debe tener habilitadas las opciones Guardar información de Autorrecuperación y Conservar la última versión autoguardada cuando se cierra sin guardar, vistas en el apartado anterior.

Al cerrar sin guardar nuevos archivos Si se está trabajando en un archivo creado recientemente o en un archivo ivo temporal (por ejemplo un adjunto de Outlook), y lo cierra sin guardar, realizar los pasos siguientes para abrir el último borrador autoguardado: 1. Seleccionar la ficha Archivo reciente.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

y a continuación hacer clic en

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

24

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 2. En la parte rte inferior derecha de la pantalla, hacer clic en el botón 3. Seleccionar el archivo en la carpeta de borradores que muestra el cuadro de diálogo Abrir. 4. Una vez abierto, seleccionar Guardar como para guardar el archivo de nuevo en su equipo. También puede obtener acceso a estos archivos mediante estos pasos: 1. Abrir un nuevo archivo o cualquier archivo existente. 2. Seleccionar la ficha Archivo y a continuación el comando Información 3. Abrir el desplegable versiones

. Administrar

4. Al seleccionar la opción Recupera libros no guardados aparecerá la lista de los archivos no guardados 5. Seleccionar el archivo y a continuación hacer clic en Abrir. 6. Seleccionar Guardar como, para guardar el archivo en el equipo.

Recuperar los archivos previamente guardados. Si está trabajando en un archivo que se ha guardado anteriormente y lo cierra sin guardar las modificaciones actuales, siga estos pasos para abrir el último borrador autoguardado: 1. Seleccionar Información en la ficha Inicio. 2. Junto al botón Administrar versi versiones, aparecerán los libros con la etiqueta cuando se cerró sin guardar guardar.

3. Acceder a Guardar como, para que el archivo se guarde en el equipo.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

25

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

Trabajar con versiones de archivos autoguardados Abrir y ver versiones anteriores de su archivo actual Para ver las versiones autoguardadas de su archivo actual, seguir los pasos siguientes: 1. Abrir la ficha Archivo. Archivo 2. Haga clic en Información.. Las versiones autoguardadas del archivo actual se enumeran en Versiones. 3. Seleccionar la versión necesaria en la lista.

Restablecer las versiones anteriores de del archivo actual Para reemplazar el archivo actual con una versión anterior anterior: . 1. Abrir la ficha Archivo. 2. Haga clic en Información Información.. Las versiones autoguardadas del archivo actual se enumeran en Versiones. 3. Seleccionar la versión necesaria en la lista. 4. En la parte superior del documento, aparecerá un mensaje. Hacer clic en Restaurar. 5. Seleccionar Aceptar para sobrescribir el documento actual con la versión anterior.

Guardar automáticamente una copia de seguridad Microsoft Excel permite configurar realizar copias de seguridad automáticamente cada vez que se graba un libro. 1. Hacer clic en la ficha Inicio y, a continuación, abrir el desplegable situado junto a Guardar como 2. Abrir el desplegable del botón Herramientas situado en la parte inferior izquierda del cuadro de diálogo. 3. Escoger Opciones generales del desplegable

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

26

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 4. Se abrirá el cuadro de diálogo. álogo. Habilitar la casilla Crear siempre una copia de seguridad

Reparar un archivo dañado Cuando se abre un libro que está dañado, Microsoft Excel inicia automáticamente el modo de recuperación de archivos e intenta abrir y reparar el libro al mismo tiempo, aunque no siempre puede iniciar el modo recuperación. Como medida preventiva, puede ser conveniente guardar el libro a menudo y crear copias de seguridad. También puede especificar que Excel cree automáticamente un archivo de recuperación cada cierto tiempo. De esta forma, tendrá acceso a una copia en buen estado del libro en caso de que el original se elimine limine accidentalmente o resulte dañado.

Reparar manualmente un libro dañado 1. Seleccionar Abrir desde el botón Inicio 2. Seleccionar el archivo en la lista que se muestra en el cuadro de diálogo. 3. Abrir el desplegable del botón Abrir 4. Seleccionar Abrir y Reparar… 5. Seguir uno de los procedimientos siguientes: a. Para recuperar todos los datos posibles del libro, clicar en Reparar. b. Para extraer los valores y las fórmulas del libro cuando no es posible reparar el libro, clicar en Extraer datos datos.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

27

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

Personalizar la list lista a de archivos recientemente utilizados Microsoft Excel muestra los últimos archivos abiertos para tener acceso rápidamente a los documentos. Esta característica está activada de forma predeterminada pero puede desactivarse, volver a activar o ajustar el número úmero de archivos a mostrar. Si se cierra un archivo y, a continuación, se mueve a otra ubicación, el vínculo a ese archivo en el programa en el que lo creó dejará de funcionar. Deberá usar el cuadro de diálogo Abrir para buscar el archivo y abrirlo. Si esta característica se desactivó y después se volvió a activar, sólo aparecerán los archivos que abra y guarde después de activarla. 1. Desde la ficha Inicio, hacer clic sobre Opciones de Excel. 2. Seleccionar la opción Avanzadas. 3. En la zona Mostrar,, en la list lista Mostrar este número de documentos recientes,, seleccionar el número de archivos. Si se desea mostrar ningún archivo, seleccionar 0.

Para mantener un archivo en la lista Documentos recientes, hacer clic en la ficha Inicio y, a continuación, presionar sobre el icono para Agregar el archivo a la lista de documentos recientes recientes. Cuando un documento está agregado a la lista Documentos recientes, en la parte derecha del nombre aparecerá el icono

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

28

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

TRABAJAR CON PLANTILLAS Una plantilla es un libro que se crea y se utiliza como base y ejemplo para otros libros similares. Se pueden crear plantillas para libros y hojas de cálculo. Para ahorrar tiempo en la creación de libros, con estructura similar y fomentar la estandarización, ón, se puede guardar un libro como una plantilla y utilizar posteriormente en la creación de nuevos libros. En Microsoft Office Excel 2007, un archivo de plantilla (.xltx) puede incluir datos y formato, y un archivo de plantilla habilitada para macros (.xltm) (.xl también puede incluir macros macros. Además de usar sar una plantilla de creación propia, también puede usar una de las múltiples plantillas predefinidas que puede descargar en Microsoft Office Online.

Crear una plantilla 1. Abrir un libro o crear un libro nuevo 2. Aplicar formatos y características al libro. 3. Desde el botón Inicio, seleccionar Guardar como 4. Teclear el nombre del archivo y buscar en el desplegable Guardar como tipo, la opción Plantilla de Excel o plantilla de Excel habilitada para macros. 5. Hacer clic sobre Guardar. La plantilla se colocará automáticamente en la carpeta Plantillas para asegurar que estará disponible cuando se necesite. También puede copiar cualquier libro de Excel en la carpeta Plantillas y, después, usarlo como plantilla sin guardarl guardarlo o en el formato de archivo de plantilla (.xltx o .xltm). Para seleccionar la plantilla 1. Desde el botón Inicio

clicar

sobre

el

botón

Nuevo

2. Se abrirá la Galería para seleccionar la plantilla. En la galería deben aparecer tanto las plantillas predeterminadas de Microsoft Excel, como las plantillas de creación propia.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

29

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 Las plantillas están ordenadas por categorías. En la parte derecha de la ventana aparecerá una vista preliminar de la plantilla seleccionada.

De forma predeterminada las plantillas de creación propia se guardan en la carpeta Mis plantillas Podrá descargar más ejemplos de plantilla a través de la conexión a Office online.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

30

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

TIPOS DE REFERENCIAS Excel puede utilizar en la introducción de funciones, referencias relativas de celda, que son referencias a celdas relacionadas con la posición desde la celda activa, referencias absolutas que hacen siempre referencia a las celdas en una posición específica o referencias mixtas donde aparecerá una parte relativa y otra absoluta. Para diferenciarlas se utilizará el signo dólar $ columna y la fila. Por ejemplo la celda A1 Referencia relativa = A1 Referencia absoluta = $A$1 Referencia mixta xta = $A1 o A$1

situado antes de la

Las referencias relativas se ajustan automáticamente cuando se copian celdas, las absolutas no.

Referencias relativas Al crear una fórmula,, normalmente las referencias de celda o de rango se basan en su posición relativa respecto a la celda que contiene la fórmula. Al copiar una fórmula que utiliza referencias relativas, Excel ajustará automáticamente las fórmulas en la fórmula pegada. Ejemplo referencias relativas: Al introducir la fórmula en la celda D4=C4*A4 para calcular el total,, Excel interpreta lo siguiente, siguiente siempre desde la celda activa D4: D4 Multiplica ultiplica la celda situada una columna a la izquierda en la misma fila (C4) * por la celda situada dos columnas a la izquierda en la misma fila (A4).

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

31

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 Al copiar esta fórmula hacia abajo, Excel seguirá Interpretando las mismas direcciones,, pero una fila por debajo. Para seleccionar el tipo de referencia u otro utilizar la tecla de función F4.

Referencias absolutas La referencia absoluta se representa por el signo dólar, delante de la referencia de la columna y de la fila $A$1. Se utiliza cuando, en una fórmula, se quiere hacer referencia a una celda en concreto sin que ésta varíe al copiar la fórmula a otras celdas celdas. Ejemplo referencias absolutas: En el siguiente ejemplo se pretende calcular, en las columnas D y E el tanto por ciento (%) de ventas y gastos que representan cada grupo de artículos en el total de las ventas. El procedimiento para calcularlo es, intr introducir oducir la fórmula en la celda y posteriormente copiarla en las celdas iinferiores feriores de la misma columna.

La fórmula a introducir en la celda D3 y copiar en todas las celdas inferiores será: D3=B3/$B$7. El segundo operador será la referencia absoluta.

Referencias erencias mixtas En las referencias mixtas, los operadores de la fórmula contienen contiene una parte absoluta y una parte relativa, tipo $B2 o B$2. Ejemplo referencias mixtas:: En el ejemplo siguiente se pretende calcular, el valor que tendrán las 1000 acciones según los diferentes valores de las

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

32

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 acciones y del precio del dólar que también varía, introduciendo una sola fórmula y copiándola en las celdas contiguas. La fórmula se introducirá una sola vez en la celda B5 y se copiará en e horizontal y vertical B5=$C$1*$A5*B$4

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

33

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

TRABAJAR CON UN LIBRO COMPARTIDO Existen muchas formas de compartir, analizar y comunicar información empresarial en Microsoft Office Excel 2010.. El modo en que decida compartir los datos dependerá de muchos factores, como la forma en que desee que otros usuarios vean los datos o trabajen con ellos Definición Libro compartido compartido: libro configurado para permitir que varios usuarios de una red lo vean y realicen cambios al mismo tiempo en un mismo archivo. Cada usuario suario que guarda el libro ve los cambios realizados por los demás usuarios. Es posible crear un libro compartido y colocarlo en una ubicación de red donde varias personas puedan editar el contenido al mismo tiempo. Por ejemplo, si las personas de un grup grupo o de trabajo administran varios proyectos cada una y desean conocer el estado de los proyectos de sus compañeros, el grupo puede utilizar un libro compartido para realizar un seguimiento del estado de los proyectos. Todas las personas implicadas podrán especificar ecificar la información sobre sus proyectos en el mismo libro. El propietario del libro compartido puede administrarlo quitando usuarios del mismo y resolviendo los cambios que estén en conflicto con otros. Cuando se hayan incluido todos los cambios, pued puede e dejar de compartir el libro.

Compartir un libro En los libros compartidos no todas las características están disponibles. A continuación se muestra una tabla con las acciones y característica que no están disponibles disponibles,, con lo que para aplicar algunas características de diseño o ejecutar algunas acciones, se deberán hacer antes de compartir el libro. En la tabla siguiente se muestran en la izquierda, las acciones que no pueden realizarse y en la zona derecha las que se pueden realizar.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

34

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

NO SE PERMITE Crear una tabla de Excel Insertar o eliminar bloques de celdas Eliminar hojas de cálculo Combinar celdas o dividir celdas combinadas Agregar o cambiar formatos condicionales

SE PERMITE Nada Insertar filas y columnas completas. Nada Nada Los formatos condicionales existentes siguen apareciendo a medida que cambian los valores de celda, pero no se pueden modificar ni se pueden volver a definir las condiciones. Agregar o cambiar validación de datos Las celdas siguen validándose al escribir nuevos valores, s, pero no se puede cambiar la configuración de validación de datos existente. Crear o cambiar gráficos o informes de gráfico dinámico Ver los gráficos e informes existentes. Insertar o cambiar imágenes u otros objetos Ver las imágenes y otros objetos existentes. Insertar o cambiar hipervínculos Los hipervínculos existentes siguen funcionando. Utilizar herramientas de dibujo Ver los dibujos y gráficos existentes. Asignar, cambiar o quitar contraseñas Las contraseñas existentes permanecen activas. Proteger oteger o desproteger hojas de cálculo o el libro La protección existente permanece activa. Crear, cambiar o ver escenarios Nada Agrupar o esquematizar datos Seguir utilizando los esquemas existentes. Insertar subtotales automáticos Ver los subtotales existentes. Crear tablas de datos (tabla de datos: rango de celdas Ver las tablas de datos existentes. que muestra los resultados de sustituir diferentes valores en una o más fórmulas. Existen dos tipos de tablas de datos: tablas de una entrada y tablas de dos entradas.) Crear o cambiar informes de tabla dinámica Ver los informes existentes. Escribir, grabar, cambiar, ver o asignar macros Ejecutar las macros existentes que no tengan acceso a funciones no disponibles. Registrar las operaciones del libro compartido en una macro almacenada en otro libro no compartido. Agregar o cambiar hojas de diálogo de Microsoft Excel 4 Nada Cambiar o eliminar fórmulas de matriz Las fórmulas de matriz existentes siguen realizando los cálculos correctamente. Usar un formulario de datos para agregar nuevos datos Puede utilizar un formulario de datos para buscar un registro. Trabajar con datos XML, que incluye: Nada Importar, actualizar y exportar datos XML Agregar, cambiar el nombre o eliminar asignaciones XML Asignar celdas a elementos XML Usar el panel de tareas Origen XML, la barra de tareas XML o los comandos XML del menú Datos

Las herramientas y botones de comando para compartir un libro se encuentran en la ficha Revisar.

Para compartir un libro: 1. Crear un nuevo libro o abrir un libro existente 2. Realizar los cambios o acciones no permitidas antes de compartirlo.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

35

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 3. En el grupo Cambios de la ficha Revisar Revisar, clicar ar sobre el comando en Compartir libro. 4. En la ficha Edición,, activar la casilla de verificación Permitir la modificación por varios u usuarios a la vez.. Esto también permite combinar libros.

Avanzado,, seleccionar las opciones a utilizar para 5. En la ficha Uso Avanzado realizar un seguimiento de los cambios y actualizarlos; a continuación, hacer clic en Aceptar. 6. Al compartir un libro siempre aparecerá el mensaje comunicando que el archivo se guardará. Seleccionar la ubicación de la red a la que pueden tener acceso todos los usuarios. 7. Si el libro contiene vínculos a otros libros o documentos , comprobarlos y actualizar los que estén de desactivados 8. Guardar el libro.

Comprobar omprobar y actualizar los vínculos 1. En el grupo Conexiones de la ficha Datos, haga clic en Editar vínculos.

Recordatorio:: El comando Editar vínculos no está disponible si su archivo no contiene información vinculada. 2. Hacer clic en Comprobar estado para revisar el estado de todos los vínculos de la lista. Esta operación puede tardar un poco, si

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

36

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 hay muchos vínculos o si el libro de origen de los vínculos está en una ubicación de red y la red es lenta. 3. Comprobar el estado en la columna Estado,, hacer clic en el vínculo y, a continuación, llevar a cabo la acción necesaria. En la tabla siguiente se encuentran los diferentes estados en la izquierda y la acción a realizar en la derecha Si el estado es Correcto Desconocido

Realizar la siguiente acción No es necesario realizar ninguna acción. El vínculo funciona y está actualizado. Clicar en Comprobar estado para actualizar el estado de todos los vínculos de la lista. N/A El vínculo utiliza vinculación e incrustación de objetos (OLE) o intercambio dinámico de datos (DDE) (DDE). Excel no puede comprobar el estado de estos tipos de vínculos. Error: no se ha encontrado Hacer clic en Cambiar origen y seleccionar el libro correcto. el origen Error: no se ha encontrado Hacer clic en Cambiar origen y seleccionar otra hoja de cálculo. Es posible que la hoja de cálculo se haya movido el origen o que se le haya cambiado el nombre de algún libro. valores. El vínculo no se actualizó izó cuando se abrió el libro. Advertencia: valores no Clicar en Actualizar valores actualizados Advertencia: haga clic en El libro puede estar configurado para cálculo manual. Para establecer el libro en Abrir origen para calcular cálculo automático, clicar en el botón de Microsoft Office y, a ccontinuación, ontinuación, en Opciones de Excel Excel. En la categoría Fórmulas,, bajo Opciones de cálculo, clicar el libro presionando F9 en Automáticamente Automáticamente. Advertencia: no se pueden Hacer clic en Abrir origen, vuelva al libro de destino y clicar en Comprobar resolver algunos nombres estado.. Si este procedimiento no resuelve el problema, asegurarse de que el hasta que se abra el libro nombre esté incluido y de que no esté mal escrito. Pasar al libro de origen y, en la ficha Fórmula Fórmulas, en el grupo Celdas con nombre, clicar en Administrador de de origen nombres.. Buscar el nombre. Advertencia: haga clic en El vínculo no se puede actualizar hasta que se abra el origen. Abrir origen El origen está abierto No se puede comprobar el estado de un vín vínculo. Valores actualizados No es necesario realizar ninguna acción. Se han actualizado los valores. desde nombre de archivo Advertencia: Microsoft Es posible que el origen no contenga hojas de cálculo o que se guardara en un Excel no puede determinar formato de archivo no admitido. Clicar en Actualizar valores. el estado del vínculo

Todos los usuarios con acceso al recurso compartido de red tienen acceso total al libro compartido a menos que el propietario bloquee celdas y proteja la hoja de cálculo para restringir el acceso.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

37

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

Modificar el nombre de usuario en un libro compartido Después de abrir un libro compartido, se puede especificar y cambiar los datos del mismo modo que en un libro normal, aunque es posible que se desee modificar su nombre de us usuario, uario, para identificar el trabajo realizado. 1. Abrir el libro compartido. 2. Desde el botón escoger el menú Opciones de Excel. 3. En la categoría General General, bajo la zona Personalizar la copia de Office,, en el cuadro Nombre de usuario,, escribir el nombre de usuario uario que desea utilizar para identificar su trabajo en el libro compartido

Utilizar filtros y configuración de impresión originales Puede crear la configuración de filtro e impresora que desee para su uso personal. La configuración de cada usuario se g guarda uarda por separado de forma predeterminada. También puede usar los filtros o la configuración de impresión preparados por el propietario del libro cuando lo abra. 1. En el grupo Cambios de la ficha Revisar, hacer clic en Compartir libro. 2. En la ficha Uso Avanzado Avanzado, en la zona Incluir vista personal personal, desactivar la casilla de verificación Configuración de impresora o Configuración de filtro y clicar en Aceptar Aceptar. 3. Guardar uardar los cambios realizados en el libro y ver los cambios guardados por otros usuarios desde la última vez que guardó el libro 4. Si aparece el cuadro de diálogo Resolución de conflictos conflictos, resolver los conflictos. (se verá más adelante)

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

38

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

Quitar un usuario de un libro compartido Si es necesario, es posible desconectar usuarios de un libro compartido, pero antes de desconectar usuarios, es preciso asegurarse de que han completado su trabajo en el libro. Si quita un usuario activo, se perderá todo el trabajo que no se haya guardado.

Lista de usuarios que tienen abierto este libro

1. En el grupo Cambios de la ficha Revisar,, clicar en Compartir libro. 2. En la ficha Edición, Edición dentro de la lista Los siguientes usuarios tienen abierto este libro, comprobar los nombres de los usuarios. 3. Seleccionar el nombre del usuario al que se desea desconectar y clicar en Quitar usuario.

Aunque esta acción desconecta al usuario del libro compartido, no impide a dicho usuario modificar de nuevo el libro compartido. Para eliminar la configuración de vista personal del usuario que se ha quitado, hacer lo siguiente: uiente: 1. En la ficha Vista,, en el grupo Vistas de libro,, hacer clic en Vistas personalizadas. 2. En la lista Vistas,, seleccionar la vista de otro usuario y clicar en Eliminar.

Resolver conflictos de cambios en un libro compartido Cuando dos usuarios modifican el mismo libro compartido y tratan de guardar cambios que afectan a la misma celda se produce un conflicto. Excel sólo puede conservar uno de los cambios realizados en

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

39

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 la celda. Cuando el segundo usuario guarda el libro, Excel muestra el cuadro de diálogo Resolución de conflictos conflictos. conflictos,, leer la información En el cuadro de diálogo Resolución de conflictos acerca de cada cambio y los cambios conflictivos realizados por el otro usuario. • Para mantener el cambio que usted ha realizado o el de la otra persona y continuar con el siguiente cambio conflictivo, hacer clic en Aceptar los míos o en Aceptar otros. • Para conservar todos los cambios restantes que ha realizado o todos los del otro usua usuario, clicar Aceptar todos los míos o en Aceptar todos los otros otros. • Para que sus cambios anulen todos los demás cambios sin mostrar de nuevo el cuadro de diálogo Resolución de conflictos, hacer lo siguiente: a. En el grupo Cambios de la ficha Revisar,, escoger Compartir libro. b. En la ficha Uso avanzado avanzado, bajo En caso de cambios conflictivos entre usuarios, clicar en Prevalecen los cambios guardados y, a continuación, en Aceptar Aceptar.

Control de cambios en un libro compartido En los libros compartidos, a través del control de cambios, puede controlar, mantener y mostrar información acerca de los cambios realizados. Los detalles acerca de los cambios realizados en un libro cada vez que se guarda se registran en el historial de cambios.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

40

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 El historial de cambios es un libro compartido, y contiene la información sobre los cambios realizados en sesiones pasadas. Esta información incluye el nombre de la persona que realiza cada cambio, cuándo se realizó el cambio y los datos que han cambiado. El historial de cambio io ayuda a identificar los cambios realizados en los datos de un libro compartirlo y poder aceptarlos o rechazarlos. El control de cambios es especialmente útil cuando varios usuarios editan un libro o cuando se envía un libro a los revisores para que se comenten y luego se necesitan combinar los comentarios que se recibió con la copia de ese libro, incorporando los cambios y comentarios a conservar.

Cómo funciona el control de cambios El control de cambios sólo se encuentra activo y disponible en los libros l compartidos. Cuando se realizan cambios en el libro compartido, puede ver el historial de cambios directamente en la hoja de cálculo a modo de comentario o en una hoja de cálculo de historial independiente. En ambos casos, se pueden revisar de form forma a instantánea los detalles de cada cambio. Al utilizar el control de cambios se deben tener en cuenta los puntos siguientes: •

El control de cambios es diferente de las acciones de deshacer y copia de seguridad.. No es posible utilizar el historial de cam cambios bios para deshacer los cambios con la opción de deshacer o restablecer a una versión anterior del libro. Sin embargo, la hoja de cálculo de historial incluye un registro de todos los datos eliminados para que se puedan copiar los datos nuevamente en las ce celdas ldas originales del libro compartido.

•

Algunos tipos de cambios no se controlan controlan:: Se controlan los cambios que se realizan en el contenido de las celdas, pero otros cambios, como los cambios os de formato, no se controlan o algunas de las funciones no disponibles en libros compartidos.

•

El historial de cambios se conserva sólo durante un intervalo específico:: Al activar el control de cambios, el historial de cambios se conserva durante 30 días de forma predeterminada, aunque esta opción puede personalizarse.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

41

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 •

El historial de cambios se elimina periódicamente periódicamente:: Cada vez que se cierra el libro, Excel elimina la porción del historial de cambios que supera la cantidad de días desde la última vez que se guardó el libro. Por ejemplo, si conserva 30 días el historial de cambios y abre un libro por primera vez en dos meses, podrá ver el historial de cambios de hace dos meses. Sin embargo, cuando cierre este libro, se eliminará el historial de los 30 días anteriores (días 31 a 60).

Cuando se desactiva el control de cambios o se deja de compartir un libro, se eliminará de forma permanente todo el historial de cambios. Excel proporciona diferentes formas para tener acceso y usar el historial de cambios almacenado. Resaltado en pantalla: Excel resaltará las zonas cambiadas con un color diferente para cada usuario y mostrar mostrará los detalles básicos como un comentario cuando se sitúe el puntero sobre la celda modificada. Esta opción es útil cuando un libro no ttiene iene muchos cambios o cuando se necesita visualizar rápidamente el cambio. Control de historial:: Excel creará una hoja de cálculo de historial independiente con una lista imprimible de los detalles de los cambios que se necesita filtrar y así buscar los cambios que más interesen. La hoja de cálculo de historial es útil cuando un libro tiene muchos cambios o cuando es necesario investigar qué sucedió. Revisión de los cambios:: Excel puede guiar por la secuencia de cambios para que se pueda decidir si se necesita aceptar o rechazar cada cambio. Este método resulta útil cuando se evalúan los comentarios de otros usuarios.

Activar el control de cambios para un libro 1. En la ficha Revisar,, en el grupo Cambios, clicar en Compartir libro. 2. En la ficha Modificación Modificación, activar la casilla Permitir la modificación por varios usuarios a la vez. 3. Hacer clic en la ficha Uso avanzado avanzado. 4. En Control de cambios, clicar en Guardar historial de cambios durante 5. En el cuadro días,, escribir la cantidad de días q que ue se necesita conservar el historial de cambios. cambios.. 6. Clicar Aceptar y, si se le pregunta si desea guardar el libro, en Aceptar.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

42

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

Resaltar cambios Cuando se resaltan los cambios a medida que se trabaja, Excel marca las modificaciones como los cambios, las inserciones y las eliminaciones, eliminaciones con un color de resaltado. 1. En la ficha Revisar, en el grupo Cambios, Cambios abrir el desplegable Control de cambios y escoger Resaltar cambios. 2. Se abrirá el cuadro de diálogo Resaltar cambios. 3. Habilitar la casilla Efectuar control de cambios al modificar. Esta casilla comparte el libro y resalta los cambios que se realizan. 4. En la zona Resaltar cambios, activar la casilla Cuándo y, a continuación, en el desplegable, escoger la opción deseada. 5. Para especificar para qué usuario usuarioss desea resaltar cambios, activar la casilla Quién y, a continuación, en el desplegable,, escoger la opción deseada 6. Para especificar el área de la hoja de cálculo donde se desean resaltar los cambios, activar la casilla Dónde y, a continuación, en el cuadro Dónde, escribir directamente la referencia de celda o el rango. En caso de no saber las referencias de las celdas, puede clicar en el botón Contraer diálogo en el extremo derecho del cuadro Dónde y luego seleccionar con el ratón, el intervalo que interviene, viene, directamente en la hoja de cálculo. Cuando haya terminado, clicar de nuevo en el botón para mostrar de nuevo todo el cuadro de diálogo. 7. Escoger Resaltar cambios en pantalla o Mostrar cambios en una hoja nueva según interese. • Para imprimir la hoja de cálculo de historial, haga clic en el menú Archivo y escoger Imprimir • Puede copiar el historial mediante Copiar/Pegar. 8. Clicar en Aceptar y si se solicita, guardar el libro.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

43

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

Detener el resaltado de cambios Cuando ya no es necesario o resaltar los cambios, es posible desactivar la opción del resaltado de cambios. Si se desactiva el control de cambios, se eliminará el historial de cambios. Para conservar una copia de esta información se recomienda imprimir la hoja de cálculo de histo historial o copiarla en otro libro antes de desactivar la herramienta resaltar cambios. • •

En el grupo Cambios de la pestaña Revisar,, haga clic en Control de cambios y, a continuación, en Resaltar cambios. En el cuadro de diálogo Resaltar cambios,, desactive la casilla Efectuar control de cambios al modificar modificar.

Aceptar y rechazar cambios 1. En la ficha Revisar,, en el grupo Cambios, hacer clic en Control de cambios y luego en Aceptar o rechazar cambios cambios. 2. Guardar el libro. 3. En el cuadro de diálogo Seleccionar cionar cambios para aceptar o rechazar,, hacer lo siguiente: a. Para aceptar o rechazar los cambios que se realizan después de una fecha determinada, activar la casilla Cuándo,, y clicar en Desde la fecha en el desplegable, desplegable a continuación escribir la primera fe fecha cha para la que se desean ver los cambios. b. Para aceptar o rechazar los cambios realizados por otro usuario, activar la casilla Quién y seleccionar la opción deseada en el desplegable. c. Para aceptar o rechazar los cambios realizados por todos los usuarios, desactivar esactivar la casilla Quién. d. Para aceptar y rechazar los cambios realizados en un área específica, activar la casilla Dónde y escribir la referencia de celda del intervalo de la hoja de cálculo. También puede clicar en el cuadro de diálogo Contraer diálogo y seleccionar las celdas directamente en la Hoja de datos. e. Para aceptar o rechazar los cambios en todo el libro, desactivar la casilla Dónde. 4. Hacer clic en Aceptar y revisar la información sobre los cambios en el cuadro de diálogo Aceptar o rechazar cambios. 5. Para aceptar o rechazar cada cambio, escoger Aceptar o Rechazar. 6. Si se le pide que seleccione un valor para una celda, haga clic en el valor que desea y luego en Aceptar.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

44

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

La hoja de cálculo de historial registra un rechazo con "Deshacer" o "Resultado de la acción rechazada" en la columna Tipo de acción.

NOTAS: ž Es preciso aceptar o rechazar un cambio antes de avanzar al siguiente. ž Es posible aceptar o rechazar todos los cambios restantes simultáneamente al hacer clic en Aceptar todos o Rechazar Rech todos.

Ver la hoja de cálculo de historial 1. En la ficha Revisar,, en el grupo Cambios, clicar en Control de cambios y luego en Resaltar cambios.. Recordar que si la casilla Efectuar control de cambios al modificar no está activada, Excel no guardó el historial de cambios para el libro. 2. En Resaltar cambios, activar la casilla Cuándo y, a continuación, en la lista Cuándo, haga clic en Todos. 3. Desactivar las casillas Quién y Dónde. 4. Activar la casilla Mostrar cambios en una hoja nueva nueva. 5. Aceptar. 6. Se creará una hoja nueva llamada Historial, donde para cada columna aparecerá una flecha de filtro para buscar y escoger la información deseada. 7. Al guardar el libro se ocultará la hoja de cálculo de historial. Para ver la hoja de cálculo de historial desp después de guardar, activar la casilla Mostrar cambios en una hoja nueva en el cuadro de diálogo Resaltar cambios cambios.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

45

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

Cambios que Excel no controla ni resalta Excel no realiza un seguimiento de los siguientes tipos de cambios:

Cambios que no se controlan Nombres de hoja cambiados

Alternativas

Los cambios de nombres de hoja no se resaltan en las fichas de hoja, pero se controlan en la hoja de cálculo de historial Hojas de cálculo insertadas o Se controlan los cambios que se realizaron en una hoja de cálculo después eliminadas de insertarla, pero la inserción se controla sólo en la hoja de cálculo de historial. Celdas o datos con formato Ninguna. Ocultar o mostrar filas o Ninguna. columnas Comentarios adicionales o Ninguna. cambiados Celdas que cambian porque Para buscar celdas que cambian debido a la actualización, puede usar las una fórmula calcula un nuevo herramientas de auditoría en la ficha Fórmulas, en el grupo Auditoría de fórmulas. fórmulas.(se verá más adelante) valor Cambios no guardados Puede resaltar los cambios a medida que se realizan, pero los cambios aparecen en la hoja de cálculo de historial sólo después de guardarse.

Debido a que la herramienta Control de cambios sólo se encuentra disponible en los libros compartidos compartidos, las características que no se encuentran disponibles para usar en los libros compartidos no se controlan como cambios.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

46

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

Especificaciones y límites de los libros compartidos CARACTERÍSTICA

LÍMITE MÁXIMO

Usuarios que pueden abrir y compartir simultáneamente un libro compartido (libro compartido: libro configurado para permitir que varios usuarios de una red lo vean y realicen cambios al mismo tiempo. Cada usuario que guarda el libro ve los cambios realizados por los demás usuarios.)

256

Vistas (vista: conjunto de valores de visualización e impresión a los que se puede dar un nombre y se pueden aplicar a un libro. Se pueden crear más de una vista del mismo libro sin guardar copias diferentes del libro.) personales en un libro compartido

En función disponible

Días que se mantendrá el historial de cambios (historial de cambios: en un libro compartido, información que se mantiene sobre los cambios realizados en sesiones de edición pasadas. La información incluye el nombre de la persona que realiza cada cambio, cuándo se realizó el cambio y los datos que han cambiado.)

32.767 (el valor predeterminado es 30 días)

Libros que pueden combinarse a la vez

En función disponible

Celdas que pueden resaltarse en un libro compartido

32.767

Colores utilizados para identificar ntificar los cambios realizados por diferentes usuarios si el resaltado de cambios está activado

32 (cada usuario se identifica mediante un color; los cambios realizados por el usuario actual se resaltarán en color azul marino)

Tablas de Excel en un libro compartido

0 (cero) Nota No se puede compartir un libro que contiene una o más tablas de Excel.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

de

de

la

la

memoria

memoria

47

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

PROTECCIÓN EN MICROSOFT EXCEL Proteger un libro con contraseña Microsoft Excel permite utilizar contraseñas para impedir que otras personas abran o modifiquen los archivos o libros. Es recomendable utilizar contraseñas seguras que combinen letras en mayúsculas y minúsculas, números y símbolos. Las contraseñas deben tener 8 o más caracteres Si se olvida la contraseña, Microsoft no puede recuperarla. Las contraseñas ayudan a proteger los libros de Excel mediante el cifrado, de modo que otras personas no podrán leer ni cambiar su contenido. Su nivel de protección de contraseña se basa en lo bien construida que esté la contraseña. Las contraseñas con mayor complejidad son más difíciles de adivinar y propor proporcionan cionan mayor seguridad. Hay unas directivas de contraseñas que ayudan a configurar las contraseñas más seguras. Para crear una contraseña más segura y compleja, utilice contraseñas que tengan al menos una longitud de siete caracteres, y que incluyan caracteres acteres de al menos cuatro de los grupos enumerados a continuación. • Caracteres en mayúscula (A (A-Z) • Caracteres en minúscula (a (a-z) • Números (0-9) • Caracteres no alfabéticos (por ejemplo: ! $ # o %) • La contraseña no debe incluir su nombre de usuario.

Cifrarr y establecer una contraseña para abrir un libro Realizar los pasos siguientes para cifrar un archivo y establecer una contraseña para abrirlo: 1. Abrir el menú Archivo y escoger la opción Información 2. Hacer clic en el botón Cifrar una contraseña.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

48

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

3. Escribir en el cuadro de diálogo la contraseña. 4. En el cuadro de diálogo Confirmar contraseña,, es preciso escribir de nuevo la contraseña contraseña,, exactamente igual que en el paso anterior.

Establecer una contraseña para modificar un libro 1. Desde el menú Archivo abrir el cuadro de diálogo Guardar como 2. Abrir el desplegable del botón Herramientas, situado en la parte inferior izquierda, y escoger la opción Opciones generales. 3. Se abrirá el cuadro de diálogo

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

49

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 4. En caso de no desear que ningún usuario pueda abrir el libro, definir una contraseña de apertura 5. En caso de no desear que ningún usuario pueda guardar las modificaciones realizadas en el libro original, definir una contraseña de escritura. 6. Para asegurar que e nadie pueda modificar su contenido habilitar la casilla Se recomienda solo lectura. 7. Para indicar que se cree siempre una copia de seguridad, habilitar la casilla Crear siempre una copia de seguridad.

Cambiar una contraseña Seguir uno o ambos procedimie procedimientos: 1. Abrir el archivo con la contraseña de apertura de modo que el archivo se abra en modo de lectura/escritura lectura/escritura. 2. Hacer clic en el botón de Microsoft Office y, a continuación, en Guardar como. 3. Seleccionar Herramientas y, a continuación, en Opciones generales. 4. Seleccionar la contraseña existente y, a continuación, escribir una contraseña nueva. 5. Clicar en Aceptar. 6. Cuando se le indique, volver a escribir la contraseña para confirmarla y, a continuación, haga clic en Aceptar. 7. Seleccionar Guardar.. 8. Si se le pregunta si desea reemplazar el archivo existente, haga clic en Sí.

Quitar una contraseña Seguir uno o ambos procedimientos: 1. Abrir el archivo con la contraseña de apertura de modo que el archivo se abra en modo de lectura/escritura o en modo lectura/escritura. 2. Hacer clic en el botón de Microsoft Office y, a continuación, en Guardar como. 3. Seleccionar Herramientas y, a continuación, en Opciones generales. 4. Seleccionar la contraseña y presionar SUPRIMIR. 5. Seleccionar Aceptar y Guardar. 6. Si se le pregunta si desea reemplazar el archivo existente, haga clic en Sí.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

50

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

PROTEGER ELEMENTOS DE LA HOJA DE CÁLCULO Para impedir que un usuario cambie, mueva o elimine por accidente o premeditadamente datos importantes, es posible proteger determinados nados elementos de la hoja de cálculo o el libro, con o sin una contraseña La protección de elementos del libro u hoja no debe confundirse con la seguridad mediante contraseña de nivel de libro visto en el apartado anterior. La protección de elementos no protege un libro frente a otros usuarios.

De forma predeterminada las celdas de un libro aparecen como bloqueadas. Esta característica se habilita/deshabilita desde el cuadro de diálogo del formato de celdas. Como se verá en los próximos apartados, para bloquear zonas específicas de una hoja de cálculo serán necesarios 2 pasos, desbloquear las zonas donde se permiten cambios y a continuación proteger la hoja de cálculo.

Proteger elementos de la hoja de cálculo 1. Para desbloquear las celdas o rangos de forma que otros usuarios puedan modificarlos: a) Seleccionar cada celda o rango a desbloquear. b) En el grupo Celdas de la ficha Inicio, hacer clic en Formato y luego en Formato de celdas. c) En la ficha Protección,, desactive la casilla de verificación Bloqueada y,, a continuación, haga clic en Aceptar. La casilla Oculta se utiliza para ocultar las fórmulas en la barra de fórmulas. Esta opción es importante cuando en la fórmula existen datos que no se desean que otros usuarios puedan visualizar.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

51

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

Desbloquear objetos gráficos. Para desbloquear objetos gráficos (como imágenes, imágenes prediseñadas, formas o gráficos SmartArt) de forma que otros usuarios puedan modificarlos, haga lo siguiente: 1. Mantener presionada la tecla CTRL para seleccionar todos los objetoss a desbloquear 2. En la ficha Formato de la imagen imagen, en el grupo Tamaño seleccionar el icono Iniciador cuadro de diálogo, situado en la esquina inferior derecha del grupo. . 3. En la ficha Propiedades Propiedades,, desactivar la casilla de verificación Bloqueado y, en caso o necesario, Bloquear texto. 4. En el grupo Cambios de la ficha Revisión, haga clic en Proteger hoja. 5. En la lista Permitir a los usuarios de esta hoja de cálculo,, seleccionar los elementos a modificar por los usuarios. 6. En el cuadro Contraseña para desproteger la hoja hoja, escribir una contraseña para la hoja, hacer clic en Aceptar y, a continuación, volver a escribir la contraseña para confirmarla. La contraseña es opcional. Si no proporciona una contraseña, cualquier usuario puede desproteger la hoja y modificar los elementos protegidos.

Proteger elementos del libro 1. En el grupo Cambios de la ficha Revisión, haga clic en Proteger libro. libro 2. En Proteger en el libro,, seguir uno de los procedimientos siguientes: a) Para proteger la estructura de un libro, activar la casilla de verificación Estructura.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

52

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

b) Para que las ventanas del libro tengan siempre el mismo tamaño y posición cada vez que se abra el libro, activar la casilla de verificación Ventanas. ACTIVAR ESTA CASILLA DE VERIFICACIÓN Estructura

PARA IMPEDIR QUE LOS USUARIOS ž ž ž ž ž ž ž

Ventanas

ž ž ž

Vean hojas de cálculo que haya ocultado. Muevan, eliminen, oculten o cambien los nombres de las hojas de cálculo. Inserten nuevas hojas de cálculo u hojas de gráfico Muevan o copien hojas de cálculo en otro libro. En informes de tabla dinámica, muestren los datos de origen de una celda en el área de datos, o muestren páginas de campos de página en hojas de cálculo independientes. En escenarios, creen un informe de resumen de escenario. En el complemento omplemento Herramientas para análisis, usen las herramientas de análisis que colocan los resultados en una nueva hoja de cálculo. Graben nuevas macros. Cambien el tamaño y la posición de las ventanas del libro cuando éste se abre. Muevan, cambien de tamaño o cierren las ventanas. Los usuarios podrán ocultar y mostrar las ventanas.

c) Para impedir que otros usuarios quiten la protección del libro, en el cuadro Contraseña (opcional), escribir una contraseña.

Desbloquear áreas específicas de una hoja de cálculo Por defecto todas las celdas de una hoja de cálculo se encuentran bloqueadas, pero este bloqueo no tiene efecto efecto, si no se protege la hoja. Al proteger la hoja las celdas no se podrán editar ni modificar. Es posible que sea necesario tener rangos o celdas bloqueadas y rangos o celdas desbloqueadas y permitir poder entrar datos en éstas últimas. Para que se puedan editar algunas celdas mientras otras están bloqueadas, puede desbloquear las celdas y, después, bloquear únicamente celdas o rango rangoss específicos antes de proteger la hoja de cálculo. 1. Si la hoja de cálculo está protegida es necesario desprotegerla. 2. Seleccionar toda la hoja de cálculo haciendo clic en el botón Seleccionar todo.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

53

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

5. 6. 7. 8.

3. En la ficha Inicio, en el grupo Fuente, Fuente clicar en el iniciador del cuadro de diálogo Formato de fuente de celda . 4. En la ficha Protección,, desactivar la casilla de verificación Bloqueada y, a continuación, hacer clic en Aceptar Aceptar.. En cuanto se proteja la hoja, se desbloquearán todas las celdas En la hoja ja de cálculo, seleccionar sólo las celdas a bloquear. En la ficha Protección Protección, activar la casilla de verificación Bloqueada y, a continuación, hacer clic en Aceptar. En el grupo Cambios de la ficha Revisión, seleccionar Proteger hoja. En la lista Permitir a los usuarios de esta hoja de cálculo, cálculo seleccionar los elementos que se permite cambiar a los usuarios.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

54

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

CREAR ESQUEMAS Un esquema es un resumen preciso que refleja los conceptos más importantes o de mayor trascendencia de un documento. Un esquema esque puede ser perfectamente un índice de un libro, donde aparecen los puntos más importantes que se tratan en él y además está estructurado por niveles de profundización que se pueden plegar y desplegar para mostrar su contenido. Microsoft Excel ofrece una herramienta para la creación ón de esquema de hasta ocho niveles. En los esquemas se realizan subtotales automáticos de filas y columnas. Es posible crear un esquema de filas (como se muestra en el ejemplo siguiente), un esquema de columnas o un esquema de filas y columnas. Para mostrar filas para un nivel, haga clic en los símbolos de esquema

adecuados.

El nivel 1 contiene las ventas totales de todas las filas de detalle. El nivel 2 contiene las ventas totales para cada mes en cada región. El nivel 3 contiene las filas de detalle (sólo las filas de detalle 11 a 13 están actualmente visibles). El símbolo de esquema está expandido. Para expandir o contraer datos en el esquema, haga clic en los símbolos de esquema y .

Antes de crear un esquema hay que tener en cuenta algunos aspectos: ž

ž ž

Asegurar que los datos sean apropiados para crear un esquema, deben tener una jerarquía o disponer de una estructura por niveles. En una hoja solo se puede incluir un esquema, para tener más de un esquema sobre los mismos datos, se deben copiar a otra hoja. Para crear esquemas automáticamente se debe preparar lo hoja con un formato (se verá más adelante)

Un esquema se puede crear de forma Manual i/o Automática

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

55

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

Crear un esquema de filas 1. Asegurarse de que cada columna tiene un rótulo de título en la primera fila, contiene datos similares en cada columna y el rango no tiene filas ni columnas en blanco. 2. Seleccionar una celda del rango. 3. Ordenar las columnas que forman los grupos. 4. Insertar lass filas de resumen. Para trazar esquemas de los datos por filas, se deben tener filas de resumen que contengan fórmulas que hagan referencias a las celdas de cada una de las filas de detalle para dicho grupo. Seguir uno de los procedimientos siguientes: a. Insertar nsertar filas de resumen utilizando el comando Subtotal. Subtotal Utilizar el comando Subtotal, que inserta la función SUBTOTAL inmediatamente debajo o encima de cada grupo de filas de detalle y crea automáticamente el esquema para usted. b. Insertar filas de resumen propias: Insertar las filas de resumen propias con las fórmulas inmediatamente anterior o posterior a cada grupo de filas de detalle. 5. Especificar si la ubicación de la fila de resumen está encima o debajo de las filas de detalle. Para ello: a. En el grupo Esquema de la ficha Datos, hacer clic en el icono Iniciador cuadro de diálogo , situado en la esquina inferior derecha b. Para especificar una fila de resumen encima de la fila de detalles, desactivar la casilla de verificación Filas resumen debajo del detalle.. Para especificar una fila de resumen debajo de la fila de detalles, activar la casilla de verificación Filas resumen debajo del detalle

Crear un esquema de filas automáticamente La mejor opción para crear esquemas es que, Microsoft Excel, lo haga hag automáticamente, el tiempo de creación es menor y su configuración es más fiable.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

56

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 Existen unos requisitos previos para que Excel 2007 pueda crear automáticamente el esquema esquema: ž

Las filas sumatorio o de totales deben estar siempre por encima o por debajo de los datos, nunca entremezclados.

ž

Las columnas sumatorio/o de totales deben estar a la derecha o a la izquierda de los datos, nunca entremezclados.

Si la disposición de los datos características, obligatoriamente manualmente.

no se

se corresponde con estas debe cre crear ar el esquema

Para crear el esquema, situar el cursor sobre una celda cualquiera y seleccionar la opción Autoesquema del menú Agrupar que se encuentra en la pestaña Datos.

Crear un esquema de forma manual Cuando se agrupan manualmente niveles de esquema, es mejor tener todos los datos mostrados para evitar agrupar las filas incorrectamente. Según los datos de la tabla de la izquierda, reflejan las ventas de Marzo y Abril de la zona Este de una empresa, donde la fila 6 recoge rec el subtotal de Marzo, la fila 10 recoge el subtotal de Abril y la fila 11 recoge el total de los dos meses.

1. Seleccionar las filas de detalle que subtotalizan los dos meses. En el ejemplo seleccionar y agrupar las filas 2 a 5, y las filas 7 a 9. No incluir en la selección la las filas resumen

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

57

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 2. En el grupo Esquema de la ficha Datos, seleccionar Agrupar. Agrupar Los símbolos del esquema aparecerán en la pantalla al lado del grupo. crea todos 3. Seguir seleccionando y agrupando filas internas hasta crear los niveles del esquema necesarios. Para desagrupar las filas, seleccionarlas de nuevo y en la ficha Datos,, en el grupo Esquema,, clicar en Desagrupar.

Si se desagrupa un esquema mientras los datos de detalle están ocultos, las filas de detalle permanecen ocultas. Para mostrar los datos, arrastrar por los números de fila visibles adyacentes a las filas ocultas. En la ficha Inicio, en el grupo Celdas,, clic clicar en Formato, escoger Ocultar y mostrar y, a continuación, clicar en Mostrar filas. Es posible crear también esquemas de columnas, cuando los datos y totales están configurados en columnas. El funcionamiento es el mismo, que el visto en este apartado. odrá utilizar los símbolos de esquema para mostrar u ocultar los detalles Podrá y totales de la tabla de datos • • •

y

o

Para mostrar los datos de detalle de un grupo, hacer clic en del grupo. Para ocultar los datos de detalle de un grupo, clicar en del grupo grup Utilice los comandos los datos.

para activar el nivel de agrupamiento de

También puede utilizar los comandos Mostrar detalle y Ocultar detalle , situados en el grupo Esquema de la ficha Datos.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

58

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

Mostrar u ocultar datos de esquemas Debido a que muchas opciones de Microsoft Excel pueden personalizarse por cada usuario, en ocasiones pueden no mostrarse los símbolos de esquema. Para personalizar esta opción realizar los pasos siguientes: 1. Desde el menú Archivo , seleccionar el comando Opciones. 2. En la categoría Avanzadas, buscar la zona Mostrar opciones para esta hoja. 3. Habilitar o deshabilitar la casilla Mostrar símbolos de esquema si se aplica un esquema.

Copiar datos esquematizados Para poder copiar en otra ubicación los datos esquematiza esquematizados, dos, es necesario poder seleccionar únicamente las celdas visibles, ya que de lo contrario se copiarían todos los datos. 1. Seleccionar el rango de celdas. 2. En la ficha Inicio, en el grupo Buscar,, seleccionar la opción Ir a Especial. 3. En el cuadro de diálogo activar la opción Solo celdas visibles. 4. Realizar las acciones Copiar/Pegar.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

59

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

FUNCIONES A lo largo de este capítulo, se verán con algunos ejemplos, las funciones más usuales de Microsoft Excel.

¿Qué es una función? Una función es una fórmula compleja prefabricada, en cierta manera como pequeños programas, que se ejecutan en el momento en que las llamamos por su nombre. Además de las funciones ya diseñadas, Excel permite disponer de cualquier fórmula personalizada personalizada, utilizando el lenguaje de VBA (Visual Basic por Aplications). Todas las funciones se componen de: - Nombre de la función:: que pueden ser utilizados en mayúsculas o en minúsculas. - Argumento: Se escriben siempre entre paréntesis y son los valores sobre los que opera la fun función ción para obtener el resultado. Los argumentos se separan siempre por punto y coma (;),, y pueden ser números, otras funciones, textos, rangos, etc. Una función no es de por sí un dato válido en una celda, siempre formará parte de una fórmula. Algunas funciones devuelven un valor, otras por el contrario realizan una determinada acción. Microsoft Excel dispone las funciones ordenadas por categorías. En este apartado se verán las funciones más usuales, aunque el funcionamiento del cuadro de diálogo es si similar en todas ellas.

Funciones matemáticas y trigonométricas: Función

Descripción

ABS

Devuelve el valor absoluto de un número

ALEATORIO

Devuelve un número aleatorio entre 0 y 1

ALEATORIO.ENTRE

Devuelve un número aleatorio entre los números que especifique

COCIENTE

Devuelve la parte entera de una división

ENTERO

Redondea un número hacia abajo hasta el entero más próximo

EXP

Devuelve e elevado a la potencia de un número dado

GRADOS

Convierte radianes en grados

M.C.M

Devuelve el mínimo común múltiplo

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

60

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

Función

Descripción

MOD.

Devuelve el máximo común divisor

MULTIPLO.INFERIOR

Redondea un número hacia abajo, en dirección hacia cero

MULTIPLO.SUPERIOR

Redondea un número al entero más próximo o al múltiplo significativo más cercano

POTENCIA

Devuelve el resultado de elevar un número a una potencia

REDOND.MULT

Devuelve un número redondeado al múltiplo deseado

REDONDEA.IMPAR

Redondea un número hacia arriba hasta el entero impar más próximo

REDONDEA.PAR

Redondea un número hasta el entero par más próximo

REDONDEAR

Redondea un número al número de decimales especificado

SIGNO

Devuelve el signo de un número

SUMA

Suma sus argumentos

SUMA.CUADRADOS

Devuelve la suma de los cuadrados de los argumentos

SUMA.SERIES

Devuelve la suma una serie de potencias en función de la fórmula

SUMAPRODUCTO

Devuelve la suma de los productos de los correspondientes componentes de matriz

SUMAR.SI

Suma las celdas determinados

especificadas

que

cumplen

unos

criterios

SUMAR.SI.CONJUNTO Suma las celdas de un rango que cumplen varios criterios TRUNCAR

Trunca un número a un entero

Función ABS(número) 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) La sintaxis de la función ABS tiene los siguientes argumentos: Número: Obligatorio. El número real cuyo valor absoluto desea obtener.

Función Aleatorio() Devuelve un número real aleatorio mayor o igual a 0 y menor que 1, distribuido uniformemente. Cada vez que se c calcula alcula la hoja de cálculo, se devuelve un número real aleatorio nuevo. Sintaxis ALEATORIO()

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

61

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 La sintaxis de la función ALEATORIO no tiene argumentos: Si desea usar ALEATORIO para generar un número aleatorio pero no desea que los números cambien cada vez q que ue se calcule la celda, puede escribir =ALEATORIO() en la barra de fórmulas y después presionar la tecla F9 para cambiar la fórmula a un número aleatorio.

Función ALEATORIO.ENTRE() Devuelve un número entero aleatorio entre los números que especifique. Devuelve un nuevo número entero aleatorio cada vez que se calcula la hoja de cálculo. Sintaxis ALEATORIO.ENTRE(inferior; superior) La sintaxis de la función ALEATORIO.ENTRE tiene los siguientes argumentos: • Inferior: Obligatorio. El menor número entero que la función ALEATORIO.ENTRE puede devolver. • Superior: Obligatorio. El mayor número entero que la función ALEATORIO.ENTRE puede devolver.

Función COCIENTE() Devuelve la parte entera de una división. Use esta funci función ón cuando desee descartar el residuo de una división. Sintaxis COCIENTE(numerador; denominador) La sintaxis de la función COCIENTE tiene los siguientes argumentos: argumentos • Numerador: Obligatorio. El dividendo. • Denominador:: Obligatorio. El divisor.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

62

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 Observaciones: Si uno de los argumentos no es un valor numérico, COCIENTE devuelve el valor de error #¡VALOR

Función unción Entero (número) Redondea un número hasta el entero inferior más próximo. Sintaxis ENTERO(número) La sintaxis de la función ENTERO tiene los siguientes argumentos: Número: Obligatorio. El número real que se desea redondear al entero inferior más próximo.

Función EXP() Devuelve la constante e elevada a la potencia del argumento número. La constante e es igual a 2,71828182845904, la base del logaritmo neperiano. Sintaxis EXP(número) La sintaxis de la función EXP tiene los siguientes argumentos argumentos: Número: Obligatorio. El exponente aplicado a la base e. Observaciones • Use el operador exponencial ((^)) para calcular potencias en otras bases. • EXP es la inversa de LN, el logaritmo neperiano de número

Función GRADOS() Convierte radianes en grados. Sintaxis GRADOS(ángulo)

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

63

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

La sintaxis de la función GRADOS tiene los siguientes argumentos: Ángulo. Obligatorio. El ángulo en radianes que desea convertir.

Función M.C.M Devuelve el mínimo común múltiplo de números enteros. El mínimo común múltiplo es el menor entero positivo múltiplo de todos los argumentos enteros número1, número2, etcétera. Use M.C.M para sumar fracciones con distintos denomina denominadores. Sintaxis M.C.M(número1; [número2]; ...) La sintaxis de la función M.C.M tiene los siguientes argumentos argumentos: Número1; número2; ... Número1 es obligatorio, los demás números son opcionales. De 1 a 255 valores cuyo mínimo común múltiplo se desea obtener. er. Si un valor no es un entero, se trunca.

• •

Observaciones • Si uno de los argumentos no es numérico, M.C.M devuelve el valor de error #¡VALOR!. Si uno de los argumentos es menor que cero, M.C.M devuelve el valor de error #¡NUM!. Si M.C.M(a;b) >=2^53, M.C.M devuelve el valor de error #¡NUM!.

Función M.C.D Devuelve el máximo común divisor de dos o más números enteros. El máximo común divisor es el mayor número entero por el cual número1 y número2 son divisibles sin dejar residuo. Sintaxis M.C.D(número1; [número2]; ...) La sintaxis de la función M.C.D tiene los siguientes argumentos argumentos: Número1; número2... Número1 es obligatorio, los demás números son opcionales. De 1 a 255 valores. Si un valor no es un número entero, se trunca.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

64

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 Observaciones • Si uno de loss argumentos no es numérico, M.C.D devuelve el valor de error #¡VALOR!. • Si uno de los argumentos es menor que cero, M.C.D devuelve uelve el valor de error #¡NUM!.

Función MULTIPLO.INFERIOR Redondea un número al cifra_significativa, hacia abajo.

próximo

múltiplo

del

argumento

Sintaxis MULTIPLO.INFERIOR(número; cifra_significativa) La sintaxis de la función MULTIPLO.INFERIOR tiene los siguientes argumentos: Número: Obligatorio. El valor numérico que desea redondear. Cifra_significativa: Obligatorio. atorio. El múltiplo al que desea redondear. Observaciones • Si cualquiera de los argumentos es un valor no numérico, MULTIPLO.INFERIOR devuelve el valor de error #¡VALOR! • Si número es positivo y cifra_significativa es negativo, MULTIPLO.INFERIOR devuelve el valor de error #¡NUM!. • Si el signo de número es positivo, un valor siempre se redondea hacia cero. Si el signo de número es negativo, un valor se redondea alejándolo de cero. Si número es un múltiplo exacto del argumento cifra_significativa, no se redondea

Función MULTIPLO.SUPERIOR Redondea un número al próximo múltiplo del argumento cifra_significativa, hacia arriba. Por ejemplo, si desea que los decimales de los precios de un producto sean siempre múltiplo de 5 y el precio del

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

65

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 producto es 4,42€, utilice la fórmula =MULTIPLO.SUPERIOR(4,42;0,05) para redondear los precios al múltiplo de 5 más próximo. Sintaxis MULTIPLO.SUPERIOR(número; cifra_significativa ) La sintaxis de la función MULTIPLO.SUPERIOR tiene los siguientes argumentos: Número: Obligatorio. torio. El valor que se desea redondear. Cifra_significativa: Obligatorio. El múltiplo al que se desea redondear. Observaciones • Si uno de los argumentos es un valor no numérico, MULTIPLO.SUPERIOR devuelve el valor de error #¡VALOR! • Independientemente del signo de número, un valor se redondea hacia arriba. Si el argumento número es un múltiplo exacto del argumento cifra_significativa, no se redondea. • Si tanto número como cifra_significativa son negativos, el valor se redondea hacia abajo, alejándose de cero cero. • Si número es negativo y cifra_significativa es positivo, el valor se redondea hacia arriba, acercándose a cero

Función POTENCIA() Devuelve el resultado de elevar el argumento número a una potencia. Sintaxis POTENCIA(número; potencia) La sintaxis de la función POTENCIA tiene los siguientes argumentos: argumentos Número: Obligatorio. El número base. Puede ser cualquier número real. Potencia: Obligatorio. El exponente al que se desea elevar el número base. Se puede utilizar el operador "^"" en lugar de la función POTENCIA para indicar a qué potencia se eleva el número base, por ejemplo 5^2.

Función REDOND.MULT Redondea un número al múltiplo deseado. Sintaxis REDOND.MULT(número; múltiplo)

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

66

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 La sintaxis de la función REDOND.MULT tiene los siguientes argumentos: argumentos Número: Obligatorio. El valor que se desea redondear. Múltiplo: Obligatorio. El múltiplo al que se desea redondear el número. Observación REDOND.MULT redondea hacia valores positivos, hacia arriba, si el residuo de dividir número entre múltiplo es mayor o igual ig a la mitad del valor de múltiplo

Función REDONDEA.IMPAR Redondea un número hasta el próximo entero impar. Sintaxis REDONDEA.IMPAR(número) La sintaxis de la función REDONDEA.IMPAR tiene los siguientes argumentos: Número: Obligatorio. El valor que se desea redondear. Observaciones • Si número no es un valor numérico, REDONDEA.IMPAR devuelve el valor de error #¡VALOR! • Independiente mente del signo de número, un valor se redondea hacia arriba. Si número es un número entero impar, no se redondea

Función ión REDONDEA.PAR Devuelve un número redondeado hasta el número entero par más próximo. Esta función puede ser útil para procesar artículos que vienen en pares.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

67

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

Sintaxis REDONDEA.PAR(número) La sintaxis de la función REDONDEA.PAR tiene los siguientes arg argumentos umentos: Número: Obligatorio. El valor que se va a redondear. Observaciones • Si el argumento número es un valor no numérico, REDONDEA.PAR devuelve el valor de error #¡VALOR! • Independientemente del signo de número, un valor se redondea hacia arriba. Si el argumento número es un entero par, no se redondea

Función REDONDEAR La función REDONDEAR redondea un número a un número de decimales especificado Sintaxis REDONDEAR(número, número_decimales número_decimales) La sintaxis de la función REDONDEAR tiene los siguientes argumentos: argumentos Número: Obligatorio. Es el número que desea redondear. Número_decimales: Obligatorio. Es el número de decimales al que desea redondear el argumento número. Observaciones • Si núm_decimales es mayor que 0 (cero), el número se redondea al número de decimales especificado. • Si núm_decimales es 0, el número se redondea al número entero más próximo. • Si núm_decimales es menor que 0, el número se redondea hacia la izquierda del separador decimal. • Para ara redondear hacia arriba (lejos de cero), use la función REDONDEAR.MAS. • Para redondear hacia abajo (hacia cero), use la función REDONDEAR.MENOS..

Función SIGNO() Devuelve el signo de un número número.. Devuelve 1 si el argumento número es positivo, 0 si el argumento mento número es 0 y -1 1 si el argumento número es negativo

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

68

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

Sintaxis SIGNO(número) La sintaxis de la función SIGNO tiene los siguientes argumentos argumentos: Número: Obligatorio. Cualquier número real

Función SUMA() La función SUMA suma todos los números especificados como argumentos. Cada argumento puede ser un rango rango, una referencia de celda, celda una matriz, una constante, una fórmula o el resultado de otra función. Por ejemplo, SUMA(A1:A5) A1:A5) suma todos los números que están contenidos en las celdas A1 hasta A5. Otro ejemplo, SUMA(A1, A3, A5) suma los números que están contenidos en las celdas A1, A3 y A5. Sintaxis SUM(number1,[number2],...]) La sintaxis de la función SUMA tiene los si siguientes argumentos: Número1: Obligatorio. Es el primer argumento de número que desea sumar. Número2,.. Opcional. De 2 a 255 argumentos numéricos que desea sumar. Observaciones • Si el argumento es una matriz o una referencia, solo se considerarán los núm números eros contenidos en la matriz o en la referencia. Se omitirán las celdas vacías, los valores lógicos o el texto contenido en la matriz o en la referencia. • Si hay argumentos que son valores de error o texto que no se puede traducir a números, Excel muestra u un error.

Función SUMA.CUADRADOS Devuelve la suma de los cuadrados de los argumentos. Sintaxis SUMA.CUADRADOS(número1; [número2]; ...)

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

69

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 La sintaxis de la función SUMA.CUADRADOS tiene los siguientes argumentos: Número1; número2... Número 1 es obligatorio, los números siguientes son opcionales. De 1 a 255 argumentos de los cuales desea calcular la suma de los cuadrados. También puede usar una matriz única o una referencia matricial en lugar de argumentos separados con punto y coma. Observaciones • Los argumentos ntos pueden ser números, o nombres, matrices o referencias que contengan números. • Se tienen en cuenta los números, valores lógicos y representaciones textuales de números que se escriban directamente en la lista de argumentos. • Si un argumento es una matriz o una referencia, sólo se considerarán 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. • Los argumentos que sean valores de error o texto que no se pueda traducir a números provocan errores

Función SUMAR.SI() La función SUMAR.SI sirve para sumar los valores en un rango que cumplen los criterios o condiciones que se especifican, donde: ž

ž

ž

Rango es obligatorio.. Es el rango de celdas que se desea evaluar según los criterios especificados. Las celdas de cada rango deben ser números, o bien nombres, matrices o referencias que contengan números. Los valores en blanco y los de texto no se tienen en cuenta. Criterios es obligatorio.. Es el criterio en forma de número, expresión o texto, que determina las celdas que se van a sumar. Por ejemplo, los criterios pueden expresarse como 32, ">32", B5, 32, "32", "manzanas" u HOY(). rango_suma es opcional opcional. Son las celdas reales es para agregar, si desea agregar celdas a las ya especificadas en el argumento rango. Si se omite el argumento rango_suma, Excel agrega las celdas que están especificadas en el argumento rango (las mismas celdas a las que se aplica los criterios).

Por ejemplo, emplo, supongamos que en una columna que contiene números, desea sumar sólo los valores que son mayores a 5. Puede usar la siguiente fórmula: =SUMAR.SI(B2:B25,">5").

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

70

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 En este ejemplo, los criterios se aplican a los mismos valores que se están sumando. Si lo o desea, puede aplicar los criterios a un rango y sumar los valores correspondientes en un rango distinto. Por ejemplo, la fórmula =SUMAR.SI(B2:B5, "Juan", C2:C5) sumará únicamente los valores del rango C2:C5, donde las celdas correspondientes al rango B2: B2:B5 son iguales a "Juan." Se pueden usar los caracteres comodín signo de interrogación (?) ( y asterisco (*)) como el argumento en criterios. El signo de interrogación corresponde a cualquier carácter único y el asterisco equivale a cualquier secuencia de car caracteres. acteres. Si desea buscar un signo de interrogación o un asterisco reales, escriba un una a tilde (~) antes del carácter. Recordatorio: Cualquier criterio de texto o cualquier criterio que incluya los símbolos lógicos o matemáticos debe estar entre comillas dobles ("). Si el criterio es numérico, las comillas dobles no son obligatorias. Ejemplo 1: en la siguiente tabla de datos

Ejemplo 2: en la siguiente tabla de datos

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

71

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

Funciones de texto En este capítulo se verán con ejemplos prácticos, las funciones de texto más utilizadas. Función CAR CODIGO

Descripción Devuelve el carácter especificado por el número de código Devuelve un código numérico del primer carácter de una cadena de texto

ENCONTRAR

Concatena varios elementos de texto en uno solo Devuelve los caracteres del lado derecho de un valor de texto Busca un valor de texto dentro de otro (distingue mayúsculas de minúsculas)

HALLAR

Busca un valor de texto dentro de otro (no distingue mayúsculas de minúsculas)

CONCATENAR DERECHA

IGUAL IZQUIERDA LARGO LIMPIAR MAYUSC MINUSC MONEDA RECORTAR REEMPLAZAR REPETIR SUSTITUIR TEXTO VALOR

Comprueba si dos valores de texto son idénticos Devuelve los caracteres del lado izquierdo de un valor de texto Devuelve el número de caracteres de una cadena de texto Quita del texto todos los caracteres no imprimibles Pone el texto en mayúsculas Pone el texto en minúsculas Convierte un número en texto, con el formato de moneda Quita los espacios del texto Reemplaza caracteres de texto Repite el texto un número determinado de veces Sustituye texto nuevo por texto antiguo en una cadena de texto Da formato a un número y lo convierte en texto Convierte un argumento de texto en un número

Función CAR() Devuelve el carácter especificado por un número número. Sintaxis CARÁCTER(número) La sintaxis de la función CARÁCTER tiene los siguientes argumentos: argumentos Número. Obligatorio. Un número entre 1 y 255 que especifica el carácter deseado.

Función CODIGO() Devuelve el número de código del primer carácter del texto.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

72

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 Sintaxis CODIGO(texto) La sintaxis de la función CODIGO tiene los siguientes argumentos argumentos: Texto Obligatorio. El texto del cual se desea obtener el código del primer carácter

Función CONCATENAR () Concatena dos o más cadenas en una sola cadena de texto donde Texto1, texto2... son de 2 a 255 elementos de texto que se unirán en un elemento de texto único. Los elementos de texto pueden ser cadenas de texto, números o referencias a celdas únicas únicas. También puede utilizar el operador de cálculo símbolo de ""&"" en lugar de la función CONCATENAR para unir elementos de texto. Por ejemplo =A1&B1 =CONCATENAR(A1;B1).

devolverá

el

mismo

valor

que

En el ejemplo anterior se intercala en la fórmula un espacio, incluido entre comillas dobles, para que el valor del resultado resulte visiblemente mejor en la celda.

Función DERECHA() DERECHA devuelve el último carácter o caracteres de una cadena de texto, según el número de caracteres que el usuario especi especifica. Sintaxis DERECHA(texto;[núm_de_caracteres])

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

73

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 La función DERECHA tiene los siguientes argumentos argumentos: Texto: Obligatorio. Cadena de texto que contiene los caracteres que se desea extraer. Núm_de_caracteres: Opcional. Especifica el número de caracteres que q desea extraer con DERECHA. Núm_bytes: Opcional. Especifica el número de caracteres que desea se extraer con DERECHAB, basados en bytes. Observaciones • Núm_de_caracteres debe ser mayor o igual a cero. • Si núm_de_caracteres es mayor que la longitud del texto, DERECHA devolverá todo el texto. • Si núm_de_caracteres se omite, se calculará como 1. En el ejemplo de la izquierda, es necesario mostrar en las celdas B7, B8 y B9 las 8 últimos caracteres que corresponden a las fechas.

Función ENCONTRAR Devuelve la posición inicial del texto buscado dentro de otro texto empezando a buscar desde la posición núm_inicial. Sintaxis ENCONTRAR(texto_buscado;dentro_del_texto;núm_inicial) La función ENCONTRAR tiene los argumentos siguientes: Texto_buscado: es el texto que desea encontrar. Dentro_del_texto: es el texto que a su vez contiene el texto a encontrar. Núm_inicial: especifica specifica el carácter a partir del cual se iniciará la búsqueda. El primer carácter de dentro_del_texto es el carácter número 1. Si omite ite núm_inicial, se supone que es 1. Observaciones: • •

La función ENCONTRAR distingue entre mayúsculas y minúsculas y no permite el uso de caracteres comodín. Si texto_buscado es "" (texto vacío), BUSCAR coincide con el primer carácter de la cadena de búsq búsqueda ueda (es decir, el carácter de núm_inicial o 1).

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

74

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 • • •

•

Texto_buscado exto_buscado no puede contener ningún carácter comodín. Si texto_buscado no aparece en dentro_del_texto, ENCONTRAR devuelve el valor de error #¡VALOR!. Si núm_inicial no es mayor que cero, ENCONTRAR devuelve lve el valor de error #¡VALOR!. Si núm_inicial es mayor que la longitud de dentro_del_texto, ENCONTRAR devuelve el valor de error #¡VALOR!.

Función HALLAR() La función HALLAR buscan una cadena de texto dentro de una segunda cadena de texto y devuelve la posición del texto buscado. Sintaxis HALLAR(texto_buscado;dentr HALLAR(texto_buscado;dentro_del_texto;núm_inicial La función HALLAR tiene los siguientes argumentos: texto_buscado: es obligatorio y es el texto que se desea buscar. dentro_del_texto: es obligatorio y corresponde al texto en el que desea encontrar el valor del argumento texto_buscado. núm_inicial: este argumento es opcional y corresponde al número de carácter en el argumento dentro_del_texto donde desea iniciar la búsqueda. Por ejemplo, o, la función =HALLAR("p","impresora") devuelve 3 porque "p" es el tercer carácter en la palabra "impresora." Estas funciones permiten buscar por palabras dentro de otras palabras. Por ejemplo, la función =HALLAR("medio","promedio") devuelve 4, porque la palabra "medio" (1er.argumento) empieza en el cuarto carácter de la palabra "promedio" (2ºargumento). Observaciones • La función HALLAR no distingue mayúsculas de minúsculas. minúsculas Si se desea realizar una búsqueda que distinga mayúsculas de minúsculas, se puede utilizar la función ENCONTRAR (vista en el apartado anterior)

•

Puede usar los caracteres comodín, el signo de interrogación (?) y el asterisco (*), en el argumento texto_buscado. El signo de interrogación corresponde a cualquier carácter único y el

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

75

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

• •

asterisco co equivale a cualquier secuencia de caracteres. Si desea buscar un signo de interrogación o un asterisco reales, escriba una tilde (~) delante del carácter. Si no se puede hallar el argumento texto_buscado, la función devuelve el valor de error #¡VALOR!. Si el argumento núm_inicial se omite, el valor predeterminado es 1. • Si el valor del argumento núm_inicial es mayor que 0 (cero), o si es mayor que la longitud del argumento dentro_del_texto, se devuelve el valor de error #¡VALOR!.

Función IGUAL() Compara dos cadenas de texto y devuelve VERDADERO si son exactamente iguales y FALSO si no lo son, donde Texto1 es la primera cadena de texto y Texto2 es la segunda cadena de texto. La función IGUAL reconoce mayúsculas y minúsculas, pero pasa por alto las diferencias de formato. Sintaxis IGUAL(texto1; texto2) La sintaxis de la función IGUAL tiene los siguientes argumentos: Texto1: Obligatorio. La primera cadena de texto. Texto2: Obligatorio. La segunda cadena de texto.

Función IZQUIERDA() IZQUIERDA devuelve el primer carácter o caracteres de una cadena de texto, según el número de caracteres que especifique el usuario, donde Texto es la cadena de texto que contiene los caracte caracteres res que se desea extraer y Núm_de_caracteres especifica el número de caracteres que se desea extraer con la función IZQUIERDA. Sintaxis IZQUIERDA(texto; [núm_de_caracteres])

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

76

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 El funcionamiento de esta función es igual al de la función Derecha vista anteriormente, ormente, pero empieza a contar desde el principio(izquierda) de la cadena de texto.

Función LARGO() LARGO devuelve el número de caracteres de una cadena de texto donde Texto es el texto cuya longitud se desea conocer. Los espacios se cuentan como caracter caracteres. Sintaxis LARGO(texto) La función LARGO() tiene los argumentos siguientes: Texto: Obligatorio. El texto cuya longitud se desea obtener. Los espacios se cuentan como caracteres.

Función LIMPIAR(texto) Elimina caracteres que no se pueden imprimir. Se utiliza LIMPIAR cuando se importa un texto de otras aplicaciones que contienen caracteres que posiblemente no o se puedan o quieran imprimir. Sintaxis LIMPIAR(texto) La sintaxis de la función LIMPIAR tiene los siguientes argumentos argumentos: Texto: Obligatorio. Cualquier información de una hoja de cálculo de la que se desea eliminar caracteres no imprimibles.

Funciones MAYUSC() Y MINUSC() Convierte a mayúsculas i/o minúsculas una cadena de texto. Sintaxis MAYUSC(texto) MINUSC(texto) La sintaxis de las funciones nes MAYUSC y MINUSC tiene tienen los siguientes argumentos: Texto: Obligatorio. El texto que se desea pasar a

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

77

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 mayúsculas o minúsculas. El argumento texto puede ser una referencia o una cadena de texto.

Función Moneda() Convierte un número a formato de texto y le aplica un símbolo de moneda. El nombre de la función (y el símbolo que aplica) depende de la configuración del idioma. Utiliza un formato de moneda, con el núm_de_decimales redondeado a la posición decimal especificada. El formato empleado es $#.##0,00_);($#.##0,00). Sintaxis MONEDA(número; [núm_de_decimales]) La sintaxis de la función MONEDA tiene los siguientes argumentos argumentos: Número: Obligatorio. Un número, una referencia a una celda que contiene un número o una fórmula que se evalúa como un número. Núm_de_decimales: Opcional. El número de dígitos a la derecha del separador decimal. Si núm_de_decimales es negativo, el argumento número se redondea hacia la izquierda del separador decimal. Si omite el argumento núm_de_deci núm_de_decimales, su valor predeterminado es 2. Observaciones: La principal diferencia entre dar formato a una celda que contiene un número desde los comandos de la Cinta de Opciones y dar formato a un número directamente con la función MONEDA es que MONEDA convierte te el resultado en texto. Un número al que se le da formato con el cuadro de diálogo Aplicar formato a celdas sigue siendo un número. Los números a los que se da formato con la función MONEDA pueden continuar usándose en fórmulas porque, al calcularlos, Microsoft icrosoft Excel convierte los números especificados como valores de texto en números.

Función RECORTAR() Elimina los espacios del texto irregular,, excepto el espacio normal que se deja entre palabras.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

78

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

Sintaxis Recortar(texto) La sintaxis de la función ESPACIOS PACIOS tiene los siguientes argumentos argumentos:: Texto: Obligatorio. El texto del que desea quitar espacios.

Función REEMPLAZAR() Reemplaza parte de una cadena de texto, en función del número de caracteres que se especifique, por una cadena de texto diferente. Los argumentos de esta función son: Texto_original: ess el texto en el que se desea reemplazar algunos caracteres. Núm_inicial: es la posición del carácter dentro de texto_original que se desea reemplazar por texto_nuevo. Núm_de_caracteres: es el número d de e caracteres de texto_original que se desea que REEMPLAZAR reemplace por texto_nuevo. Texto_nuevo: ess el texto que reemplazará los caracteres de texto_original.

Función REPETIR() Repite el texto un número determinado de veces. Puede utilizar esta función para llenar una celda con una cadena de texto repetida un número determinado de veces.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

79

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 Sintaxis REPETIR(texto; núm_de_veces) La sintaxis de la función REPETIR tiene los siguientes argumentos argumentos: Texto: Obligatorio. El texto que se desea repetir. Núm_de_veces: Obligatorio. Un número positivo que especifica el número de veces que debe repetirse el texto. Observaciones • Si núm_de_veces es 0 (cero), REPETIR devuelve "" (texto vacío). • Si el argumento núm_de_veces no es un número entero, se trunca. • El resultado de la función REPETIR no puede contener más de 32.767 caracteres; de lo contrario, la función devolverá el valor de error #¡VALOR!

Función SUSTITUIR() na cadena de texto. Sustituye texto_nuevo por texto_original dentro de una Es muy similar a la función Reemplazar. Utilice SUSTITUIR cuando desee reemplazar texto específico en una cadena de texto, y REEMPLAZAR cuando desee reemplazar cualquier texto que aparezca en una ubicación específica dentro de una cade cadena de caracteres. Sintaxis SUSTITUIR(texto; texto_original; texto_nuevo; [núm_de_instancia]) La sintaxis de la función SUSTITUIR tiene los siguientes argumentos argumentos: Texto: Obligatorio. El texto o la referencia a una celda que contiene el texto en el que se desea sustituir caracteres. Texto_original: Obligatorio. El texto que se desea sustituir. Texto_nuevo: Obligatorio. El texto por el que se desea reemplazar texto_original. Núm_de_instancia: Opcional. Especifica la instancia de texto_original que se desea reemplazar por texto_nuevo. Si se especifica el argumento núm_de_instancia, sólo se remplazará esa instancia de texto_original. De

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

80

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 lo contrario, todas las instancias de texto_original en texto se sustituirán por texto_nuevo.

Funciones de Búsqueda y Referencia En este capítulo se verán algunas de las funciones más utilizadas de la categoría Búsqueda y Referencia.

FUNCIÓN BUSCAR CONSULTAH CONSULTAV COINCIDIR DESREF ELEGIR INDICE TRANSPONER

DESCRIPCIÓN Busca valores de un vector o una matriz Busca en la fila superior de una matriz y devuelve el valor de la celda indicada Busca en la primera columna de una matriz y se mueve en horizontal por la fila para devolver el valor de una celda Busca valores de una referencia o matriz Devuelve un desplazamiento de rreferencia eferencia respecto a una referencia dada Elige un valor de una lista de valores Utiliza un índice para elegir un valor de una referencia o matriz Devuelve la transposición de una matriz

Función BUSCAR La función BUSCAR busca un valor en un rango de una columna o una fila o una matriz.. Se debe indicar el valor a buscar, dónde ha de buscar y de donde se obtendrá el resultado. Definición rango:: dos o más celdas de una hoja. Las celdas de un rango pueden ser er adyacentes o no adyacentes.

Definición matriz:: Es una matriz de valores rectangular o rango de celdas que se combina con otras matrices o rangos a fin de generar varias sumas o productos. Se utiliza para crear fórmulas sencillas que producen varios resultados o que funcionan en un grupo de argumentos que se organizan en filas y columnas. Un rango de matriz comparte una fórmula común; una constante de matriz es un grupo de constantes utilizadas como un argumento.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

81

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

También se puede usar la función BUSCAR como alternativa para la función SI para crear pruebas o pruebas que excedan el límite de funciones anidadas. Para que la función BUSCAR funcione correctamente, los datos en los que se realiza la búsqueda se deben colocar en orden ascendente. Si esto no es posible, es mejor considerar la posibilidad de usar otras funciones como CONSULTAV , CONSULTAH o COINCIDIR (se verán más adelante).

La función BUSCAR tiene dos formas de sintaxis: la forma vectorial y la matricial. 1. Forma vectorial:: Se utiliza para buscar en un rango de una fila o una columna (denominado vector) un valor y devolver un valor desde la misma posición en un segundo rango. Se utiliza cuando se dispone de una amplia lista de valores en los que buscar o cuando es posible que los valores cam cambien con el tiempo. Sintaxis BUSCAR(valor_buscado; vector_de_comparación; vector_resultado) Tendrá los argumentos siguientes: ž

ž

ž

valor_buscado Es un argumento obligatorio. Es el valor que busca la función BUSCAR en el primer vector. Puede ser un número, ttexto, exto, un valor lógico o un nombre de referencia que se refiere a un valor. vector_de_comparación Es un argumento obligatorio. Es el rango que sólo contiene una fila o una columna. Los valores pueden ser texto, números o valores lógicos. vector_resultado Obligatorio. Rango que sólo incluye una fila o una columna. El argumento vector_resultado debe tener el mismo tamaño que vector_de_comparación.

Observaciones • •

Si la función BUSCAR no puede encontrar el valor_buscado, valor_buscado la función muestra el valor más grande en vector_de_comparación. vector_de_comparación Si el valor_buscado es menor que el menor valor del vector_de_comparación, BUSCAR d devuelve evuelve el valor de error #N/A.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

82

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

Forma matricial:: Se utiliza para buscar el valor especificado en la primera fila o columna de una matriz y devolver un valor desde la misma posición en la última fila o columna de la matriz. Se utiliza cuando se dispone de una pequeña lista de valores y estos permanecen siempre constantes. En general, es mejor utilizar la las funciones CONSULTAH o CONSULTAV (se verán más adelante) en lugar de la forma matricial de BUSCAR. Esta forma de BUSCAR se proporciona por cuestiones de compatibilidad con otros programas de hojas de cálculo. Sintaxis BUSCAR(valor_buscado; matriz) rgumentos serán los siguientes: Los argumentos valor_buscado: Obligatorio. Valor que busca la función BUSCAR en una matriz. El argumento valor_buscado puede ser un número, texto, un valor lógico o un nombre de referencia que se refiere a un valor. Si BUSCAR no puede encontrar ontrar el valor de valor_buscado,, usa el mayor valor de la matriz. Si el valor de valor_buscado es menor que el menor valor de la primera fila o columna (según las dimensiones de la matriz), BUSCAR devuelve el valor de error #N/A. Matriz: Obligatorio. Rango de celdas que contiene texto, números o valores lógicos que desea comparar con valor_buscado. La forma matricial de BUSCAR es muy similar a las funciones CONSULTAH y CONSULTAV.. La diferencia es que CONSULTAH busca el valor de valor_buscado do en la primera fila, CONSULTAV busca en la primera

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

83

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 columna y BUSCAR realiza la búsqueda según las dimensiones de la matriz. Observaciones • Si la matriz cubre un área con más columnas que filas, BUSCAR busca el valor de valor_buscado en la primera fila. • Si una matriz es tiene más filas que columnas, BUSCAR busca en la primera columna. • Los valores de la matriz se deben colocar en orden ascendente: ...,-2, -1, 1, 0, 1, 2, ..., A A-Z, Z, FALSO, VERDADERO; de lo contrario, BUSCAR puede devolver un valor incorrecto. E Ell texto en mayúsculas y en minúsculas es equivalente.

Función CONSULTAH() 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 matriz. Se recomienda utilizar CONSULTAH cuando los valores de comparación se encuentran en una fila en la parte superior de una tabla de datos y desee encontrar información que se encuentre dentro de un número especificado de filas. La H de CONSULTAH significa "Hor "Horizontal". Sintaxis CONSULTAH(valor_buscado;matriz_buscar_en;indicador_filas; (valor_buscado;matriz_buscar_en;indicador_filas; ordenado) La función ConsultaH H tiene los argumentos siguientes: Valor_buscado: Obligatorio. Ess el valor que se busca en la primera fila de la tabla. Valor_buscado puede ser un valor, una referencia o una cadena de texto. Matriz_buscar_en: Obligatorio. Ess una tabla de información en la que se buscan los datos. Utilice una referencia 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 ordenado es VERDADERO, los valores de la primera fila de matriz_buscar_en deben colocarse en orden ascendente: ... ...-2, -1, - 0, 1, 2,... , A-Z, Z, FALSO, VERDADERO; de lo contrario, CONSULTAH puede devolver un valor incorrecto. Si ordenado es FALSO, no es necesario ordenar matriz_buscar_en. El texto en mayúsculas y en minúsculas es equivalente.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

84

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 Indicador_filas: Obligatorio. Ess el número de fila en matriz_buscar_en desde el cual debe devolverse el va valor lor coincidente. 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 menor que 1, CONSULTAH devuelve e el valor de error #¡VALOR!; si indicador_filas es mayor que el número de filas en matriz_buscar_en, CONSULTAV devuelve el valor de error #¡REF! Ordenado: Opcional. Ess un valor lógico que especifica si CONSULTAH debe localizar una coincidencia exacta o a aproximada. proximada. Si es VERDADERO o se omite, devolverá una coincidencia aproximada. Es decir, si no encuentra ninguna coincidencia exacta, devolverá el siguiente valor mayor inferior a valor_buscado. Si es FALSO, CONSULTAH encontrará una coincidencia exacta. Si no encuentra ninguna, devolverá el valor de error #N/A. Observaciones • Si CONSULTAH 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, CONSULTAH devuelve el valor de error #N/A.

Función CONSULTAV() Busca un valor específico en la primera columna de una matriz de tabla y devuelve, en la misma fila, un valor de otra columna de dicha matriz de tabla. La V de CONSULTAV significa vertical. Utilice CONSULTAV en lugar de CONSULTAH si los valores de comparación se encuentran en una columna situada a la izquierda de los datos que desea buscar. Sintaxis CONSULTAV(valor_buscado;matriz_buscar_en;indicador_columnas;orde (valor_buscado;matriz_buscar_en;indicador_columnas;orde nado)

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

85

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 La función CONSULTAV tiene los argumentos siguientes: Valor_buscado: Obligatorio: Valor que se va a buscar en la primera columna de la matriz de tabla. Valor_buscado puede ser un valor o una referencia. Si valor_buscado es inferior al menor de los valores de d la primera columna de matriz_buscar_en matriz_buscar_en, CONSULTAV devuelve al valor de error #N/A. Matriz_buscar_en: Obligatorio: Dos o más columnas de datos. Utilizar una referencia a un rango o un nombre de rango. Los valores de la primera columna de matriz_buscar_en son los valores que busca valor_buscado. valor_buscado Estos valores pueden ser texto, números o valores lógicos. Las mayúsculas y minúsculas del texto son equivalentes. Indicador_columnas: Obligatorio Obligatorio: Número de columna de matriz_buscar_en desde la cual debe devolve devolverse rse 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 matriz_buscar_en _buscar_en y así sucesivamente. Si indicador_columnas es: • •

Inferior a 1, CONSULTAV devuelve al valor de error #VALUE! Superior al número de columnas de matriz_buscar_en, CONSULTAV devuelve el valor de error #REF!

Ordenado: Opcional: Valor lógico que especifica si CONSULTAV va a buscar una coincidencia exacta o aproximada: Si se omite o es VERDADERO, se devolverá una coincidencia exacta o aproximada. Si no localiza ninguna coincidencia exacta, devolverá el siguiente valor más alto inferior erior a valor_buscado. Los valores de la primera columna de matriz_buscar_en deben estar clasificados según un criterio de ordenación ascendente; en caso contrario, es posible que CONSULTAV no devuelva el valor correcto. correcto Si es FALSO, CONSULTAV sólo busca buscará rá una coincidencia exacta. En este caso, no es necesario ordenar los valores de la primera columna de matriz_buscar_en.. Si hay dos o más valores en la primera columna de matriz_buscar_en,, se utilizará el primer valor encontrado. Si no se encuentra una coincidencia ncidencia exacta, se devolverá el valor de error #N/A.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

86

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 Observaciones •

Al buscar valores de texto en la primera columna de matriz_buscar_en,, asegurarse de que los datos de ésta no tienen espacios al principio ni al final, de que no hay un uso incoherente de las comillas rectas ( ' o " ) ni tipográficas ( ‘ o “), y de que no haya caracteres no imprimibles. En estos casos, CONSULTAV puede devolver ver un valor inesperado o incorrecto.

•

Al buscar valores de fechas o números, asegurarse de que los datos de la primera columna de matriz_buscar_en no se almacenen como valores de texto, ya que, en ese caso, CONSULTAV puede devolver un valor incorrecto o inesperado. Puede combinar con funciones para Convertir números almacenados como texto en números.

•

Si Ordenado es FALSO y valor_buscado es un valor de texto, se pueden utilizar los caracteres comodín de signo de interrogación (?) y asterisco (*) en el ar argumento valor_buscado.. El signo de interrogación corresponde a un solo carácter cualquiera y el asterisco equivale a cualquier secuencia de caracteres. Si lo que desea buscar es un signo de interrogación o un asterisco, escriba una tilde (~) antes del cará carácter.

Ejemplo 1 En este ejemplo, se busca en la columna A Densidad de una tabla de propiedades atmosféricas los valores correspondientes en las columnas B Viscosidad y C Temperatura Temperatura.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

87

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 Ejemplo 2 En este ejemplo, se buscan valores en la columna Id. de artículo de una tabla de productos para bebés y se les asignan los valores correspondientes de las columnas Costo y Marcas para calcular los precios y las condiciones de prueba.

Función COINCIDIR() La función COINCIDIR busca un elemento específico en un rango de celdas y, a continuación, devuelve la posición relativa de ese elemento en el rango. Por ejemplo, si el rango A1:A3 contiene los valores 5, 25 y 38, la fórmula =COINCIDIR(25;A1:A3;0) devuelve el número 2, porque 25 es el segundo elemento en el rango. Utilizar COINCIDIR en lugar de una de las funciones BUSCAR cuando sea necesario 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]) La sintaxis de e la función COINCIDIR tiene los siguientes argumentos: argumentos valor_buscado: obligatorio.. Valor que desea buscar en matriz_buscada. Por ejemplo, cuando busca un número en la guía telefónica, usa el nombre de la persona como valor de búsqueda, pero el valor que desea es el número de teléfono.El argumento valor_buscado puede ser un valor (número, texto o valor lógico) o una referencia de celda a un número, texto o valor lógico. matriz_buscada: Obligatorio Obligatorio.. Rango de celdas en el que se realiza la búsqueda.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

88

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 tipo_de_coincidencia: Opcional Opcional. Puede ser el número -1, 1, 0 o 1. El argumento tipo_de_coincidencia especifica cómo Excel hace coincidir el valor_buscado con los valores de matriz_buscada. El valor predeterminado de este argumento es 1. En la siguiente tabla se describe cómo la función busca valores según la configuración del argumento tipo_de_coincidencia. TIPO_DE_COINCIDENCIA

COMPORTAMIENTO

1 u omitido

COINCIDIR encuentra el mayor valor que es menor o igual que el valor_buscado valor_buscado. Los valores del argumento matriz_buscada se deben colocar en orden ascendente, por ejemplo: ... ...-2, -1, 1, 0, 1, 2, ..., A A-Z, FALSO, VERDADERO.

0

COINCIDIR encuentra el primer valor que es exactamente igual que el valor_buscado.. Los valores del argumento matriz_buscada pueden estar en cualquier orden.

-1

COINCIDIR encuentra el menor valor que es mayor o igual que el valor_buscado valor_buscado. Los valores del argumento matriz_buscada se deben colocar en orden descendente, por ejemplo: VERDADERO, FALSO, Z Z-A, ...2, 1, 0, -1, -2, ..., etc.

Observaciones • COINCIDIR devuelve la posición del valor coincidente dentro de la matriz_buscada,, no el valor en sí. • COINCIDIR no distingu distingue entre mayúsculas y minúsculas • Si COINCIDIR no puede encontrar una coincidencia, devuelve el valor de error #N/A. • Si el tipo_de_coincidencia es 0 y el valor_buscado es una cadena de texto, puede usar los caracteres comodín: el signo de interrogación (?) y el asterisco (*), en el argumento valor_buscado.. Un signo de interrogación coincide con cualquier carácter individual; un asterisco coincide con cualquier secuencia de caracteres. Si desea buscar un signo de interrogación o un asterisco real, escriba una tilde (~) antes del carácter.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

89

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

Función ELEGIR() Utiliza el argumento núm_índice para d devolver evolver un valor de una lista de argumentos de valores. Utilizar ELEGIR para seleccionar uno de los 254 valores posibles a partir del rango del argumento índice. Por ejemplo, si valor1 a valor7 son los días de la semana, ELEGIR devuelve uno de los días cu cuando ando se utiliza un número entre 1 y 7 como argumento núm_índice. Sintaxis ELEGIR(núm_índice;valor1;valor2;...) Núm_índice: Obligatorio. Especifica specifica el argumento de valor que se selecciona. El argumento núm_índice debe ser un número entre 1 y 254, o bien, una fórmula o referencia a una celda que contenga un número entre 1 y 254. Si núm_índice es 1, ELEGIR devuelve valor1; si es 2, ELEGIR devuelve valor2 y así sucesivamente. Si núm_índice es menor que 1 o mayor que el número del último valor de la lista, ELEGIR GIR devuelve el valor de error #¡VALOR!. Si núm_índice es una fracción, se trunca al entero inferior antes de ser utilizada. Valor1,valor2,...: Valor1 es Obligatorio. Son de 1 a 254 argumentos de valores entre los cuales la función ELEGIR selecciona un valor o acción que se ejecuta basándose en el argumento núm_índice. Los argumentos pueden ser números, referencias a celdas, nombres definidos, fórmulas, funciones o texto. Si núm_índice es una matriz cada valor se evaluará cuando se evalúe ELEGIR. Los argumentos de valor para ELEGIR pueden ser referencias de rango, así como valores individuales. Ejemplo 1:

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

90

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 Ejemplo 2

Función Índice() Devuelve un valor o la referencia a un valor en una tabla o rango. La función INDICE presenta dos formas: matricial y de referencia.. Forma matricial Devuelve el valor de un elemento de una tabla o matriz seleccionado por los índices de número de fila y de columna. Se recomienda utilizar la forma matricial si el primer argumento de INDICE es una constante matricial. Sintaxis INDICE(matriz;núm_fila;núm_columna) Matriz: Obligatorio: es un rango de celdas o una constante de matriz. Si matriz contiene sólo una fila o columna, el argumento n núm_fila úm_fila o núm_columna correspondiente es opcional. 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 devuelve una matriz con toda una fila o columna. Núm_fila: Obligatorio:: 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: Opcional: 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. Observaciones •

Si se utilizan los argumentos núm_fila y núm_columna, INDICE devuelve el valor de la celda donde se produce la intersección de los argumentos.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

91

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 •

Si se define núm_fila o núm_columna como 0 (cero), INDICE devuelve la matriz de valores de toda la columna o fila, respectivamente. Para utilizar valores devueltos como una matriz, escribir la función INDICE como una fórmula de matriz (fórmula encerrada entre llaves { } en un rango horizontal al de celdas para una fila y en un rango vertical de celdas para una columna.

Para especificar una fórmula de matriz, presionar CTRL+MAYÚS+ENTRAR para encerrar la fórmula entre llaves { } •

Los argumentos núm_fila y núm_columna deben indicar una celda incluida uida en matriz; de lo contrario, INDICE devuelve el valor de error #¡REF!

Ejemplo 1

Forma de referencia Devuelve la referencia de la celda ubicada en la intersección de una fila y de una columna. Si el argumento ref. es una selección múltiple, se podrá elegir la selección en la que se buscará la referencia. Sintaxis INDICE(ref;núm_fila;núm_columna;núm_área) Ref: Obligatorio: es una referencia a uno o varios rangos de celdas. Si se especifica un rango no adyacente como argumento ref, se debe escribir 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. Por ejemplo, utilizar INDICE(ref;;núm_columna) para un argumento ref con una sola fila. Núm_fila: Obligatorio: es el número de la fila en el argumento ref desde la que se devolverá una referencia. Núm_columna: es el número de la columna en el argumento ref desde la que se devolverá una referencia.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

92

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 Núm_área: Opcional: 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 especificada se numera con 1, la segunda con 2 y así sucesivamente. Si se omite núm_área, INDICE usa área 1. Por ejemplo, jemplo, 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. Observaciones: •

• •

•

Después de que ref y núm_área hayan seleccionado un rango determinado, núm_fila y núm_columna seleccionan una celda específica: núm_fila 1 es la primera fila del rango, núm_columna 1 es la primera columna y así sucesivamente. La referencia devuelta por INDICE es la intersección entre núm_fila y núm_columna. Si se define núm_fila o núm_columna como 0 (cero), INDICE devuelve la referencia de toda la fila o columna, segú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 argumento 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.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

93

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

Funciones lógicas Función Descripción O Devuelve VERDADERO si cualquier argumento es VERDADERO Y Devuelve VERDADERO si todos sus argumentos son VERDADERO SI Especifica una prueba lógica que realizar SI.ERRORDevuelve Devuelve un valor que se especifica si una fórmula lo evalúa como un error; de lo contrario, dev devuelve uelve el resultado de la fórmula

O (función O) Devolverá VERDADERO si alguno de los argumentos es VERDADERO; devolverá FALSO si todos los argumentos son FALSO. Sintaxis O(valor_lógico1; [valor_lógico2]; ...) La sintaxis de la función O tiene los siguientes argumentos: Valor_lógico1; Valor_lógico2; ... Valor_lógico1 es obligatorio, los valores lógicos siguientes son opcionales. De 1 a 255 condiciones que se desea comprobar y que pueden tener el resultado de VERDADERO o FALSO. Observaciones • Los argumentos rgumentos deben evaluarse como valores lógicos, como VERDADERO O FALSO, o bien en matrices o referencias que contengan valores lógicos. • Si un argumento matricial o de referencia contiene texto o celdas vacías, dichos valores se pasarán por alto. • Si el rango o especificado no contiene valores lógicos, la función O devuelve el valor de error #¡VALOR!. • Puede utilizar la fórmula de matriz O para comprobar si un valor aparece en una matriz.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

94

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

Y (función Y) Devuelve VERDADERO si todos los argumentos se evalúan como VERDADERO; devuelve FALSO si uno o más argumentos se evalúan como FALSO. Un uso común de la función Y es expandir la utilidad de otras funciones que realizan pruebas lógicas. Por ejemplo, la función SI realiza una prueba lógica y, luego, devuelve un valor si la prueba se evalúa como VERDADERO y otro valor si la prueba se evalúa como FALSO. Con la función Y como argumento prueba_lógica de la función SI,, puede probar varias condiciones diferentes en lugar de sólo una. La función SI se verá en el próximo apartado. Sintaxis Y(valor_lógico1; [valor_lógico2]; ...) La sintaxis de la función Y tiene los siguientes argumentos argumentos: valor_lógico1: Obligatorio. La primera condición que desea probar se puede evaluar como VERDADERO o FALSO. valor_lógico2;... Opcional. Las condiciones adicionales que desea probar se pueden evaluar como VERDADERO o FALSO, hasta un máximo de 255 condiciones. Observaciones • Los argumentos deben evaluarse como valores lógicos, como VERDADERO o FALSO, o bien deben ser matrices o referencias eferencias que contengan valores lógicos. • Si un argumento de matriz o de referencia contiene texto o celdas vacías, esos valores se pasarán por alto. • Si el rango especificado no contiene valores lógicos, la función Y devuelve el valor de error #¡VALOR!.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

95

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

SI (función SI) La función SI devuelve un valor si una condición especificada se evalúa como VERDADERO y otro valor si se evalúa como FALSO. Por ejemplo, la fórmula =SI(A1>10,"Más de 10","10 o menos") menos"), devuelve "Más de 10" si A1 es mayor que 10 y "10 o menos" si A1 es menor o igual que 10. Sintaxis SI(prueba_lógica; [valor_si_verdadero]; [valor_si_falso]) La sintaxis de la función SI tiene los siguientes argumentos argumentos: Prueba_lógica: Obligatorio. Cualqu Cualquier ier valor o expresión que pueda evaluarse como VERDADERO o FALSO. Por ejemplo, A10=100 es una expresión lógica; si el valor de la celda A10 es igual a 100, la expresión se evalúa como VERDADERO. De lo contrario, se evaluará como FALSO. Este argumento puede utilizar cualquier operador de comparación. comparación Valor_si_verdadero: Opcional. El valor que desea que se devuelva si el argumento prueba_lógica se evalúa como VERDADERO VERDADERO. Valor_si_falso: Opcional. El valor que desea que se devuelva si el argumento prueba_lógica se evalúa como FALSO. Observaciones • Es posible anidar hasta 64 funciones SI como argumentos valor_si_verdadero y valor_si_falso para crear pruebas más complicadas. • Si cualquiera de e los argumentos de la función SI es una matriz cada elemento de la matriz se evaluará cuando se ejecute la instrucción SI. • Excel proporciona funciones adicionales que se pueden utilizar para analizar los datos en función de una condición. Por ejemplo Contar.Si. ar.Si. Sumar.Si, Promedio.Si.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

96

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 Ejemplo 2: Función Anidada SI

Función SI.ERROR Devuelve un valor que se especifica si una fórmula se evalúa como un error; de lo contrario, devuelve el resultado de la fórmula. Puede utilizar está función unción para interceptar y controlar errores en las fórmulas. Sintaxis SIERROR(valor; valor_si_error) La sintaxis de la función SIERROR tiene los siguientes argumentos argumentos: Valor: Obligatorio. El argumento en el que se busca un error. Valor_si_error: Obligatorio. torio. El valor que se devuelve si la fórmula se valúa como un error. Se evalúan los tipos siguientes de error: #N/A, #¡VALOR!, #¡REF!, #¡DIV/0!, #¡NUM!, #¿NOMBRE? o #¡NULO!. Observaciones • Si valor o valor_si_error están en una celda vacía, SIERROR los trata tr como un valor de cadena vacía (""). • Si valor es una fórmula de matriz, SIERROR devuelve una matriz de resultados para cada celda del rango especificado en el valor.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

97

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

Funciones Fecha y Hora En este apartado se verán algunos ejemplos de las funciones para el tratamiento de valores Fecha y Hora. Función AHORA

Descripción Devuelve el número de serie correspondiente a la fecha y hora actuales

DIAS.LAB

Devuelve el número de serie de la fecha que tiene lugar antes o después de un número determinado de días laborables Devuelve el número de serie de la fecha anterior o posterior a un número especificado de días laborables mediante parámetros para indicar cuáles y cuántos días son días de fin de semana Devuelve el núm número ero de todos los días laborables existentes entre dos fechas

DIAS360

Calcula el número de días entre dos fechas a partir de un año de 360 días

FECHA

Devuelve el número de serie correspondiente a una fecha determinada

NUM.DE.SEMANA

Convierte un número de serie en un número que representa el lugar numérico correspondiente a una semana de un año

DIA.LAB DIA.LAB.INTL

Las fechas son a menudo una parte crítica de análisis de datos datos. Microsoft Excel almacena las fechas como números secuenciales denominados valores de serie. Por ejemplo, en Microsoft Office Excel para Windows, la fecha 1 de enero de 1900 es el número de serie 1, y la fecha 1 de enero de 2008 es el número de serie 39448, porque es 39.448 días después del 1 de enero de 1900. Igualmente Excel almacena las horas como fracciones decimales, ya que la hora se considera una porción del día. El número decimal es un valor entre 0 (cero) y 0,99999999, y se corresponde con los momentos del día entre las 0:00:00 horas (12:00:00 a.m.) y las 23:59:59 horas (11:59:59 p.m.). El valor lor 1 corresponde a 1 día, es decir 24 horas. Por lo tanto, las fechas y las sumarse, restarse e incluirse como un valor de serie y cambiando ndo el formato de la formato General.

horas son valores y, por lo tanto, pueden en otros cálculos. Puede ver una fecha una hora como una fracción decimal, celda que contenga la fecha o la hora a

Recordatorio: Puede aplicar formato fecha y hora o formato general a los valores fechas desde el formato celdas

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

98

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

Función Ahora() Devuelve el número de serie de la fecha y hora actuales del sistema. sistema Si el formato de celda es General antes de especificar la función, Excel cambia el formato de celda al mismo formato de fecha y hora de la configuración regional de fecha y hora especificada en el Panel de control de Windows, aunque puede cambiar e el formato desde los comandos. La función AHORA es útil cuando se necesita mostrar la fecha y hora actuales en una hoja de cálculo o calcular un valor basándose en la fecha y hora actuales, y que ese valor se actualice ca cada da vez que se abra la hoja de cálculo. El valor de actualiza cada vez que se abre el libro. No se actualiza continuamente Sintaxis AHORA() La sintaxis de la función AHORA no tiene argumentos Observaciones • Excel almacena las fechas como números de serie secuenciales para que se puedan usar en cálculos. De manera predeterminada, la fecha 1 de enero de 1900 es el número de serie 1 y la fecha 1 de enero de 2008 es el número de serie 39448, porque es 39.448 días posterior al 1 de enero de 1900. Microsoft Excel el para Macintosh usa un sistema de fechas diferente como sistema predeterminado. • En los números de serie, los dígitos a la derecha del separador decimal representan la hora; los números a la izquierda representan la fecha. Por ejemplo, el número de serie 0,5 representa la hora 12:00 del mediodía. • Los resultados de la función AHORA sólo cambian 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.

Función Dia.Lab() Devuelve lve un número que representa una fecha que es el número de días laborables antes o después d de una fecha (la fecha inicial). Los días laborables excluyen los días de fin de semana y cualquier fecha identificada en el argumento festivos. Use DIA.LAB para exc excluir luir fines de

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

99

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 semana o días festivos cuando calcule fechas de vencimiento de facturas, las fechas de entrega esperadas o el número de días de trabajo realizado. Sintaxis DIA.LAB(fecha_inicial; días_lab; [festivos]) La sintaxis de la función DIA.LAB tiene los siguientes argumentos: Fecha_inicial: Obligatorio. Una fecha q que ue representa la fecha inicial. Días_lab: Obligatorio. El número de días laborables (días que no sean fines de semana ni días festivos) anteriores o posteriores al argumento fecha_inicial.. Un valor positivo para el argumento días_lab produce una fecha futura; un número negativo produce una fecha pasada. Festivos: Opcional. Una lista opcional de una o varias fechas que deben excluirse del calendario laboral, como los días festivos nacionales naciona y locales. La lista puede ser un rango de celdas que contengan las fechas o una constante matricial de los números de serie que representen las fechas. Las fechas deben especificarse mediante la función FECHA o como resultado de otras fórmulas o funciones. Por ejemplo, utilice FECHA(2008;5;23) para el 23 de mayo de 2008. Pueden producirse problemas si las fechas se escriben como texto.

Función Dia.Lab.INTL() Devuelve el número de serie de la fecha que tiene lugar antes o después de un número determinado de días laborables con parámetros de fin de semana personalizados. Los parámetros de fin de semana indican cuáles y cuántos días son días de fin de sem semana. Los días de fin de semana y cualquier día especificado como festivo no se consideran días laborables.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

100

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 Sintaxis DIA.LAB.INTL(fecha_inicial; días_lab; [fin_de_semana]; [festivos]) La sintaxis de la función DIA.LAB.INTL tiene los siguientes argumentos: argumento Fecha_inicial: Obligatorio. La fecha inicial, truncada a entero. Días_lab: Obligatorio. El número de días laborables antes o después de la fecha_inicial. Un valor positivo da como resultado una fecha futura; un valor negativo proporciona una fecha pas pasada; ada; un valor de cero proporciona la fecha_inicial. El desplazamiento de días se trunca a entero. Fin_de_semana: Opcional. Indica los días de la semana que corresponden a días de la semana y no se consideran días laborables. Fin_de_semana es un número de fin de semana o cadena que especifica cuándo ocurren los fines de semana. número de fin de semana 1 u omitido

Días de fin de semana Sábado, domingo

2

Domingo, lunes

3

Lunes, martes

4

Martes, miércoles

5

Miércoles, jueves

6

Jueves, viernes

Los valores numéricos de la tabla de la izquierda indican los siguientes días de fin de semana

Los valores de las cadenas de fin de semana tienen Viernes, sábado 7 siete caracteres de Sólo domingo 11 longitud y cada carácter Sólo lunes 12 de la cadena representa Sólo martes un día de la semana, 13 Sólo miércoles empezando por el lunes. 14 Sólo jueves El valor 1 representa un día 15 no laborable y 0 Sólo viernes 16 representa un día Sólo sábado 17 laborable. Sólo se permiten los caracteres 1 y 0 en la cadena. 1111111 no es una cadena válida. Por ejemplo, 0000011 daría como resultado un fin de semana que es sábado y domingo. Festivos: Opcional. Un conjunto opcional de una o más fechas que se deben excluir del calendario de días laborables. Los días festivos deben estar en el rango de celdas que contienen las fechas o una constante de matriz de los valores seriales que representan esas fechas. El orden de las fechas o los valores seriales de los días festivos puede ser arbitrario.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

101

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 Observaciones • Si el argumento fecha_inicial está fuera del rango para el valor de base de la fecha actual, DIA.LAB.INTL devuelve un valor de error #¡NUM!. • Si cualquier fecha en días festivos está fuera del rango para el valor de base de la fecha a actual, ctual, DIA.LAB.INTL devuelve un valor de error #¡NUM!. • Si el argumento fecha_inicial más el desplazamiento de días da como resultado una fecha no válida, DIA.LAB.INTL devuelve el valor de error #¡NUM!. • Si una cadena de fin de semana tiene una longitud no válida v o contiene caracteres no válidos, DIA.LAB.INTL devuelve el valor de error #¡VALOR!.

Función Dias.Lab() Devuelve el número de días laborables entre fecha_inicial y fecha_final. Los días laborables no incluyen los fines de semana ni otras fechas que qu se identifiquen en el argumento festivos. Sintaxis DIAS.LAB(fecha_inicial; fecha_final; [festivos]) La sintaxis de la función DIAS.LAB tiene los siguientes argumentos argumentos: Fecha_inicial: Obligatorio. Una fecha que representa la fecha inicial. Fecha_final: Obligatorio. Una fecha que representa la fecha final. Festivos: Opcional. Un rango opcional de una o varias fechas que deben excluirse del calendario laboral, como los días festivos nacionales y locales. La lista puede ser un rango de celdas que conten contengan gan las fechas o una constante matricial de los números de serie que representen las fechas. Las fechas deben especificarse mediante la función FECHA o como resultado de otras fórmulas o funciones. Por ejemplo, utilice FECHA(2008;5;23) para el 23 de mayo de 2008. Pueden producirse problemas si las fechas se escriben como texto.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

102

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 Observaciones • Microsoft Excel almacena las fechas como números de serie secuenciales para que se puedan utilizar en cálculos. • Si uno de los argumentos no es una fecha válida DIAS.LAB devuelve el valor de error #¡VALOR!.

Función Dias.360() La función DÍAS360 devuelve la cantidad de días entre dos fechas basándose en un año de 360 días días, es decir 12 meses de 30 días. Sintaxis DÍAS360(fecha_inicial;fecha_final;[método]) a sintaxis de la función DÍAS360 tiene los siguientes argumentos argumentos: La Fecha_inicial, fecha_final:: Obligatorios. Fechas entre las que desea calcular la cantidad de días. Si fecha_inicial se produce después de fecha_final, la función DÍAS360 devuelve un número n negativo. egativo. Las fechas se deben especificar con la función FECHA o se deben derivar de los resultados de otras fórmulas o funciones. Método: Método Opcional. Valor lógico que especifica si se usará el método de cálculo europeo o americano.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

103

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

Función Fecha() La función FECHA devuelve el número de serie secuencial que representa una fecha determinada. Si el formato de la celda era General antes de especificar la función, el resultado tendrá formato de fecha y no de número. La función FECHA es muy útil en situaciones en que el año, el mes y el día se proporcionan mediante fórmulas o referencias de celda. Sintaxis FECHA(año,mes,día) La sintaxis de la función FECHA tiene los siguientes argumentos argumentos: Año: Obligatorio. El valor del argumento año puede incluir de uno a cuatro dígitos. Excel interpreta el argumento año según el sistema de fechas configurado en el e equipo. Mes: Obligatorio. Número entero positivo o negativo que representa el mes del año, de 1 a 12 (de enero a dic diciembre). Día: Obligatorio. Número entero positivo o negativo que representa el día del mes, de 1 a 31.

Función Num.de.Semana() Devuelve el número de la semana correspondiente a una fecha determinada. Por ejemplo, la semana que contiene el 1 de ener enero es la primera semana del año y se numera como semana 1. Existen dos sistemas que se usan para esta función. • Sistema 1: La semana que contiene el 1 de enero es la primera semana del año y se numera como semana 1. • Sistema 2: La semana que contiene el prim primer er jueves del año es la primera semana del año y se numera como semana 1. Sintaxis NUM.DE.SEMANA(núm_de_serie;[tipo_de_devolución])

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

104

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 La sintaxis de argumentos:

la

función

NUM.DE.SEMANA

tiene

los

siguientes

Núm_de_serie: Obligatorio. bligatorio. Una fecha contenida en la semana. Las fechas deben especificarse usando la función FECHA o como resultado de otras fórmulas o funciones. Tipo 1 u omitido 2 11 12 13 14 15 16 17 21

• •

La semana empieza Sistema el Domingo 1 Lunes 1 Lunes 1 Martes 1 Miércoles 1 Jueves 1 Viernes 1 Sábado 1 Domingo 1 Lunes 2

Tipo: Opcional. Un número que determina en qué día comienza la semana. El valor predeterminado es 1. (Ver Ver tabla de la izquierda) Observaciones:

• Microsoft Excel almacena las fechas como números de serie secuenciales para que se puedan usar en cálculos. Si núm_de_serie está fuera del rango para el valor de base de fecha actual, se devuelve un error #¡NUM!. Si tipo está fuera del rango especificado en la tabla anterior, se devuelve un error #¡NUM!.

Funciones anidadas En algunas ocasiones, puede que se necesite utilizar una función como uno de los argumentos de otra función. Por ejemplo, la siguiente fórmula utiliza una función anidada PROMEDIO y compara el resultado con el valor 50.

ž

Resultados válidos: Cuando se utiliza una función anidada como argumento, se deberá devolver el mismo tipo de valor que el que utilice el argumento. Por ejemplo, si el argumento devuelve un valor VERDADERO O o FALSO, la función anidada deberá devolver

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

105

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 VERDADERO o FALSO. Si éste no es el caso, Microsoft Excel mostrará el valor de error #¡VALOR!. ž

Límites del nivel de anidamiento: Una fórmula puede contener como máximo siete niveles de funciones anidadas anidadas. Si la a Función B se utiliza como argumento de la Función A, la Función B es una función de segundo nivel. En el ejemplo anterior,, la función PROMEDIO y la función SUMA son ambas funciones de segundo nivel porque son argumentos de la función SI. Una función anidada ada dentro de la función PROMEDIO será una función de tercer nivel, etc.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

106

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

DIRECTRICES Y EJEMPLOS DE FÓRMULAS MATRIZ Para convertirse en un usuario avanzado de Excel, es recomendable utilizar fórmulas de matriz, que pueden realizar cálculos más avanzados. CSE" A las fórmulas de matriz también se las conoce como "fórmulas CSE". Esto se debe a que para especificarlas en los libros se presiona CTRL+MAYÚS+ENTRAR. Éstas se pueden emplear para realizar tareas complejas como: ž Contar el número de caracteres incluidos en un rango de celdas. ž Sumar únicamente aquellos números que cumplan ciertas condiciones, como los valores más bajos de un rango o los números comprendidos entre un límite superior e inferior. ž Sumar cada valor de un rango de valores. Se puede considerar una matriz como una colección de elementos. En Excel, esos elementos pueden residir en una única fila (lo que se denomina una matriz horizontal unidimensional), una columna (una matriz vertical unidimensional) o varias filas y columnas (una matriz matri bidimensional). Una fórmula de matriz es una fórmula que puede realizar varios cálculos en uno o varios de los elementos de una matriz y pueden devolver varios resultados o un único resultado.

Crear una fórmula de matriz sencilla Según los datos recogidos idos en la siguiente tabla, se necesita calcular los valores de la columna de los totales

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

107

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 En este caso se necesita multiplicar los valores de la matriz, comprendida entre el rango C2 y D11. 1. Seleccionar el rango E2:E11,, donde se insertará la fórmula matricial 2. Escribir la fórmula =C2:C11*D2:D11 =C2:C11*D2:D11en la barra de fórmulas 3. Presionar la combinación del teclado CTRL+MAYÚS+ENTRAR. CTRL+MAYÚS+ENTRAR En este momento la fórmula se mostrará entre llaves ({ }) y coloca una instancia de la misma en cada celda del rango seleccionado. Eso sucede con mucha rapidez, así que lo que verá en la columna E es la cifra de ventas total de cada tipo de vehículo por vendedor. Las llaves no deben entrarse nunca directamente con el teclado

Crear una fórmula de matriz de una celda Siguiendo el mismo smo ejemplo que en el apartado anterior, ž ž

13 del libro, escribir Ventas totales. En la celda C13 En la celda D D13, 13, escribir la siguiente fórmula =SUMA(C2:C11*D2:D11) y, a continuación, presionar CTRL+MAYÚS+ENTRAR CTRL+MAYÚS+ENTRAR:

En este caso, Excel multiplica los val valores ores de la matriz (el rango de celdas entre C2 y D11) y utiliza la función SUMA para agregar los totales. El resultado es un total general de 111.800 € en ventas. Este ejemplo demuestra lo eficaz que puede resultar este tipo de fórmula. Por

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

108

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 ejemplo, en un libro con 15.000 filas de datos se pueden sumar parte de los datos o la totalidad si se crea una fórmula de matriz en una sola celda. Además, debido a que la fórmula de la celda,, en nuestro ejemplo la celda D13, es totalmente independiente de la las fórmulas del rango E2 y E11, ess posible realizar innumerables acciones como modificar las fórmulas de la columna E o eliminar por completo esa columna, sin que ello afecte a la fórmula de una celda. Las fórmulas de matriz también ofrecen las ventajas siguientes: ž Coherencia: Si se clica en cualquiera de las celdas desde E2 hacia abajo, se verá la misma fórmula. Esa coherencia garantiza una mayor precisión. ž Seguridad: No es posible sobrescribir un componente de una fórmula de matriz de varias celdas. Por ejemplo, h haga aga clic en la celda E3 y presione SUPR. Tendrá que seleccionar todo el rango de celdas (de E2 a E11) y modificar la fórmula de la matriz completa o dejar la matriz como está. Como medida de seguridad adicional, tiene que presionar CTRL+MAYÚS+ENTRAR para confirmar onfirmar la modificación de la fórmula. ž Tamaños de archivo menores: Con frecuencia podrá utilizar una fórmula de matriz sencilla en lugar de varias fórmulas intermedias. Por ejemplo, el libro que ha creado para este ejercicio emplea una fórmula de matriz p para ara calcular los resultados de la columna E. Si hubiera utilizado fórmulas estándar (como =C2*D2), habría usado 11 fórmulas distintas para calcular los mismos resultados. En su mayor parte, las fórmulas de matriz usan sintaxis de fórmula estándar. También empiezan con un signo igual y se puede utilizar cualquiera de las funciones incorporadas de Excel. La principal diferencia es que al utilizar una fórmula de matriz es necesario presionar CTRL+MAYÚS+ENTRAR para especificarla. Al hacer esto, Excel incluye la l fórmula de matriz entre llaves; si escribe las llaves manualmente, la fórmula se convertirá en una cadena de texto y no funcionará. Para modificar una fórmula de matriz también debe siempre teclear CTRL+MAYÚS+ENTRAR y esa regla se aplica tanto a las fór fórmulas mulas de una celda como de varias. Siempre que se trabaje con fórmulas de varias celdas, también se tendrán que seguir las siguientes reglas: ž

Se debe seleccionar el rango de celdas en el que va a incluir los resultados antes de especificar la fórmula. Lo hizo en el paso 3 del

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

109

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

ž ž

ž

ejercicio de la fórmula de matriz de varias celdas al seleccionar las celdas comprendidas entre E2 y E11. No puede modificar el contenido de una celda individual de una fórmula de matriz. Puede mover o eliminar una fórmula de matriz completa, pero no parte de la misma. En otras palabras, para reducir una fórmula de matriz, primero debe eliminar la fórmula existente y empezar de nuevo. Para eliminar una fórmula de matriz, debe seleccionar la fórmula completa presionar SUPR y, a continuación, continu CTRL+MAYÚS+ENTRAR CTRL+MAYÚS+ENTRAR. No puede insertar celdas en blanco en una fórmula de matriz de varias celdas ni eliminar celdas de la misma.

Desventajas de utilizar fórmulas de matriz Las fórmulas de matriz presentan algunas desventajas: ž La incomodidad de tener que presionar siempre la combinación de teclas CTRL+MAYÚS+ENTRAR CTRL+MAYÚS+ENTRAR. ž Es posible que otros usuarios no entiendan las fórmulas. Existe relativamente poca documentación sobre las fórmulas de matriz, así que es importante valorar la posibilidad de utilizarlas si otros usuarios han de modificar los libros ž Según la velocidad de procesamiento y la memoria del equipo, las fórmulas de matriz de gran tamaño pueden ralentizar los cálculos.

Constantes de matriz Las constantes de matriz son un componente de las fórmulas de matriz. Es posible crear constantes de matriz si se especifica una lista de elementos y, a continuación, se incluye entre llaves (({ }), ), como: ={1,2,3,4,5} En las secciones anteriores de este artículo se ha recalcado la necesidad ecesidad de presionar CTRL+MAYÚS+ENTRAR al crear fórmulas de matriz. Dado que las constantes son un componente de éstas, se incluyen entre llaves de forma manual al escribir dichas llaves. A continuación se utiliza CTRL+MAYÚS+ENTRAR para especificar la fórmula fór completa.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

110

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 ž ž ž

Si se separan los elementos mediante comas, se crea una matriz horizontal (una fila). Si se delimitan mediante caracteres de punto y coma, se crea una matriz vertical (una columna). Para crear una matriz bidimensional, se delimitan los elem elementos entos de cada fila mediante comas y cada fila se delimita mediante caracteres de punto y coma.

Al igual que ocurre con las fórmulas de matriz, las constantes se pueden utilizar con cualquiera de las funciones incorporadas que proporciona Excel

Crear constantes tantes unidimensionales y bidimensionales El siguiente procedimiento permite conocer cómo crear constantes horizontales, verticales y bidimensionales. Crear una constante horizontal 1. Seleccionar desde la celda A1 hasta E1. 2. En la barra de fórmulas, escribir la siguiente fórmula y presionar CTRL+MAYÚS+ENTRAR: ={1,2,3,4,5}.En este caso se tendrá que escribir las llaves de apertura y cierre (({ }). ). El resultado será el siguiente. Crear una constante vertical 1. Seleccionar una columna de cinco celdas. 2. En la barra de fórmulas, escribir la siguiente fórmula y presionar CTRL+MAYÚS+ENTRAR: ={1;2;3;4;5} 3. El resultado será el de la imagen derecha Crear una constante bidimensional 1. En el libro, seleccionar un bloque de celdas de cuatro cua columnas de ancho por tres filas de alto. 2. En la barra de

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

111

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 fórmulas, escribir la siguiente fórmula ={1,2,3,4;5,6,7,8;9,10,11,12}y presionar CTRL+MAYÚS+ENTRAR Ejemplo 1. Abrir una hoja oja de cálculo en blanco con los siguientes datos. 2. En la celda B6, escribir la siguiente fórmula =SUMA( =SUMA(B1:B51*{1,2,3,4,5}) 1*{1,2,3,4,5}) y presionar CTRL+MAYÚS+ENTRAR. Observar que Excel incluye la constante entre otro par de llaves, dado que la ha especificado como una fórmula de matriz. 3. En la celda B6 aparece aparecerá el valor 85. En la siguiente sección se explica el funcionamiento de la fórmula. La fórmula utilizada en el ejemplo anterior contiene varias partes: Función: SUma Matriz almacenada:: B1:B5 Operador: * (asterisco) Constante de matriz {1,2,3,4,5}

Excel no incluye las constantes stantes de matriz entre llaves, se deben entrar de forma manual y a continuación presionar CTRL+MAYÚS+ENTRAR para especificar la fórmula. Dado que Excel realiza en primer lugar las operaciones de las expresiones incluidas entre paréntesis, los dos siguientes elementos que entran en acción son los valores almacenados en el libro (A1:E1) y el operador. En este punto, la fórmula multiplica los valores de la matriz almacenada por los valores correspondientes de la constante. Es el equivalente uivalente de =SUMA(A1*1,B1*2,C1*3,D1*4,E1*5) Por último, la función SUMA agrega los valores y en la celda A3 aparece la suma 85.. Para evitar el uso de la matriz almacenada y simplemente conservar la operación en su totalidad en memoria, substituir la matriz matr almacenada por otra constante de matriz, =SUMA({3,4,5,6,7}*{1,2,3,4,5}) Para intentarlo, copiar la función, seleccionar una celda en blanco del libro, pegar la fórmula en la barra de fórmulas y, a continuación, presionar CTRL+MAYÚS+ENTRAR. Se verá el m mismo ismo resultado que en el

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

112

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 ejercicio anterior, cuando =SUMA(A1:E1*{1,2,3,4,5}).

empleó

la

fórmula

de

matriz

Elementos que se pueden utilizar en las constantes • • • •

Las constantes de matriz pueden contener números, texto, valores lógicos (como TRUE y FALSE) y valores de error (como #N/A). Permiten utilizar los números en formato entero, decimal y científico. En caso de incluir texto, debe especificarlo entre comillas tipográficas ("). Las constantes de matriz no pueden contener matrices, fórmulas ni funciones adicionales. En otras palabras, sólo pueden incluir texto o números separados por comas o puntos y coma. Cuando se especifica una fórmula como {1,2,A1:D4} o {1,2,SUMA(Q2:Z8)}, Excel muestra un mensaje de advertencia. Además, los valores numéricos pueden in incluir cluir signos de porcentaje, de dólar, comas o paréntesis.

Poner nombre a las constantes de matriz Posiblemente la mejor forma para utilizar las constantes de matriz sea ponerles nombre. Las constantes con nombre pueden resultar mucho más sencillas de utilizar lizar y pueden ocultar parte de la complejidad de las fórmulas de matriz a los principiantes. Para ponerle nombre a una constante de matriz y utilizarla en una fórmula, seguir este procedimiento: 1. En el grupo Nombres definidos de la ficha Fórmulas, abrir el desplegable del comando Definir nombre.. Se abrirá el cuadro de diálogo Definir nombre. nombre

2. Escribir el nombre en el cuadro. 3. Escoger el ámbito al que hará referencia a través del desplegable Ámbito 4. En el cuadro Hace referencia a, escoger el grupo de celdas. Recuerde escribir bir las llaves de forma manual

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

113

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 Cuando se emplee una constante con nombre como fórmula de matriz, es necesario escribir el signo igual. Si no lo hace, Excel interpretará la matriz como una cadena de texto. Por últim último, o, tener en cuenta que puede utilizar combinaciones de texto y números.

Solucionar problemas de las constantes de matriz Si las constantes de matriz no funcionan: •

•

Es posible que algunos elementos no se hayan separado con el carácter adecuado. Si se omit omite e una coma o un punto y coma o coloca uno en la ubicación incorrecta, es posible que la constante de matriz no se cree correctamente o que aparezca un mensaje de advertencia. Es posible que haya seleccionado un rango de celdas que no coincida con el número de elementos de la constante. Por ejemplo, si selecciona una columna de seis celdas para utilizarla con una constante de cinco celdas, aparecerá el valor de error #N/A en la celda vacía. Por el contrario, si selecciona muy pocas celdas, Excel omite los va valores lores que no cuentan con una celda correspondiente.

Ejemplo fórmulas de matriz básicas

1. Utilizar los datos de la tabla superior 2. Asegurarse de que el libro contiene dos hojas de cálculo en blanco. 3. Nombrar a la primera hoja con la palabra Datos y Matrices a la segunda hoja.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

114

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

Crear una matriz a partir de valores existentes 1. En el libro de ejemplo, seleccionar la hoja de cálculo Matrices. 2. Seleccionar el rango de celdas desde C1 a E3. 3. Escribir =Datos!E1:G3 y CTRL+MAYÚS+ENTRAR: La fórmula se vincula ula a los valores almacenados en las celdas E1 a G3 de la hoja de cálculo Datos. La alternativa a esta fórmula de matriz sería introducir una a una la fórmula en cada una de las celdas, como muestra la imagen de la derecha. Si se modifica algunos de los valores de la hoja de cálculo Datos, Datos esas modificaciones aparecen en la hoja de cálculo Matrices.

Contar los caracteres de un rango de celdas En el ejemplo siguiente se muestra cómo contar el número de caracteres, incluidos los espacios, de un rango de celdas. 1. En la hoja de cálculo Datos, en la celda C7, escribir la fórmula =SUMA(LARGO(C1:C5)) y presionar CTRL+MAYÚS+ENTRAR: 2. En la celda C7 aparecerá el valor 59. La función LARGO devuelve la longitud de cada cadena de texto de cada una de las celdas del rango y a continuación, la función SUMA agrega esos valores y muestra el resultado en la celda que contiene la fórmula, C7.

Buscar los n valores más pequeños de un rango En este ejemplo se muestra cómo buscar los tres valores más pequeños de un rango de celdas. 1. En la hoja de cálculo Datos,, seleccionar las celdas comprendidas entre A12 y A14. Este conjunto de celdas contendrá los resultados devueltos por la fórmula de matriz.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

115

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 2. En la barra de fórmulas, escribir =K.ESIMO.MENOR(A1:A10,{1;2 ;3}) y presionar CTRL+MAYÚS+ENTRAR. Los valores 400, 475 y 500 aparecen en las celdas A12 a A14, respectivamente. En esta fórmula se utiliza una constante de matriz para evaluar la función K.ESIMO.MENOR ENOR tres veces y devolver el valor más pequeño (1), segundo más pequeño (2) y tercero más pequeño (3) de la matriz incluida en las celdas A1:A10. Para buscar más valores, agregar más argumentos a la constante y un número equivalente de celdas de resultado resultados al rango A12:A14.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

116

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010

IMPORTAR DATOS A EXCEL Introducción Microsoft Excel permite trabajar con datos procedentes de otras aplicaciones. Puede utilizar algunas de las herramientas disponibles en Excel para copiar datos, como el portapapeles de Windows utilizando las opciones Copiar /Pegar o mediante la opción de me menú nú para la importación de datos, aunque esta puede tener problemas de compatibilidad con los datos, dependiendo del formato del archivo a importar, ya q que ue cada aplicación genera un archivo con un formato propio para identificar mejor el contenido de los datos. Microsoft Excel dispone de herramientas para exportar datos. Al importar datos de una aplicación externa se deben tener en cuenta que pueden surgir los siguientes fallos: ž Algunas de las fórmulas no ajustan correctamente. ž El formato no se ajusta al original. ž Las fórmulas y funciones que no entiende no las copia.

Utilizar el asistente para importar texto La denominación de archivos de texto se refiere a archivos que no tienen formato, llamados archivos de texto plano (ASCII). Los archivos de texto con formato como los de Word (.doc) o los (.rtf) tienen otra forma de importarse a Excel que se verá más adelante Una de las opciones para importar archivos de texto con asistente es: 1. Acceder a la pestaña Datos y seleccionar el botón Desde texto. 2. Seleccionar el archivo a importar en el cuadro de diálogo que aparece. 3. Seguir los pasos de las pantallas del asistente. Paso 1:: Permite indicar si el texto importado tiene los Campos delimitados o no para que Excel sepa donde empieza un campo y

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

117

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 dónde acaba, a partir de qué fila se debe importar por si es necesario descartar títulos y decidir el Origen del archivo. Si todos los datos son correctos pulsamos sobre Siguiente>..

Paso 2: Permite elegir los separadores de los datos. Por defecto viene marcado el separador de Tabulación, los datos comprendidos entre los separadores se importaran en campos diferentes. En la imagen se observa que se incluye en un sólo campo, debido a que no hay signos de tabulación en el texto. Si se utiliza esta opción de separar con espacios en blanco, es conveniente activar Considerar separadores consecutivos como uno solo,, así, si por ejemplo se e encuentran ncuentran dos o más caracteres separadores juntos, los considera como uno sólo. Para continuar con el asistente pulsar sobre Siguiente>. Paso 3:: Permite seleccionar el Formato de los datos. Normalmente Excel determina el tipo de los datos de una columna por los valores contenidos en ella, pero permite cambiar ese formato. ž Clicar en la columna para seleccionarla, aparecerá el fondo negro. ž Seleccionar el formato en la sección Formato de los datos en columnas.

CIEF-ACCIÓ Barcelona Francesc Tárrega, 14, 1º y 2º Tel. 93 351 78 00

CIEF-ACCIÓ Madrid AV, Mediterráneo, 11, 1ºC Tel. 91 502 13 35

118

CET CIEF-ACCIÓ ACCIÓ Reus Sant Joan, 34, 5º 3ª Tel. 977 312 436

EXCEL MEDIO-AVANZADO AVANZADO 2010 Observar que una de las opciones de esta mism misma a sección, ofrece la posibilidad de saltar la importación de la columna seleccionada No importar columna (saltar). ž En el botón Avanzadas es posible completar ciertas características para los números como los separadores de decimales y millares y la posición del signo en los negativos. Una vez completado o comprobado la definición, pulsar sobre el botón Finalizar.

El asistente permite en todo momen momento to la opción de volver pantallas anteriores pulsando el botón