2010/SECTOR PRODU… · Web viewTodos los comandos del grupo se muestran en alta resolución. ......

COORDINACIÓN GENERAL DE VINCULACIÓNDIRECCIÓN GENERAL DE EDUCACIÓN CONTINUA

CURSO TALLER: MICROSOFT EXCEL AVANZADO OBJETIVO GENERAL Al finalizar este evento el participante serán capaz de aplicar los conocimientos teóricos y prácticos necesarios para la realización de operaciones en Excel 2007 así como editar y realizar operaciones que requieran la aplicación de formulas, tablas, gráficos y diversos formatos mediante el uso de la hoja de cálculo, permitiendo mejorar sus actividades laborales.

CONTENIDO TEMÁTICO1 Entorno de una hoja de cálculo2 Empezar a trabajar en Excel 20073 Nuevos formatos de archivo y nuevas opciones al guardar4 Fórmulas y funciones5 Gráficos6 Esquemas y vistas7 Tablas de Excel8 Tablas dinámicas

INSTRUCTOR MIGUEL ANGEL FLORES MALDONADOPasante de Ingeniero en Sistemas Computacionales, cuenta con amplia experiencia en impartición de cursos y ponencias a nivel nacional e internacional y se ha desempeñado como analista de datos, catalogador y fundador y jefe del departamento de control de virus, soporte técnico e investigación de la Universidad de Colima. Instructor certificado de SIABUC 8 y SIABUC 9

DIRIGIDO A:Personal de Embotelladora de Colima, S.A. de C.V.

DURACIÓN:20 horas contacto

FECHAS Y HORARIO:5 sábados: 31 de julio,7,14, 21 de agosto de 2010, en horario 9:00 a 14:00

LUGAR:CIAM, Villa de Álvarez

PRESENTACIÓN 1

La Universidad de Colima dado su liderazgo en la entidad genera los espacios que brindan oportunidades de crecimiento y desarrollo de sus egresados, de los sectores social y productivo, a partir de eventos académicos que tienen como premisa principal “la promoción de una cultura de la educación para toda la vida”; lo cual se articula con la promoción y generación de mejores oportunidades de crecimiento, desarrollo de habilidades y competencias que orienten a detener conocimientos teóricos para su formación profesional; así como también enfocándose al desarrollo humano, de valores, entre otros, que nos permiten ser cada día mejores, buscando por tanto impulsar modalidades de enseñanza – aprendizaje no formales.

Por ello la Coordinación General de Vinculación a través de la Dirección General de Educación Continua (DGEC), para el programa Atención al Sector Productivo implementa el curso taller “Microsoft Excel avanzado” donde el participante, adquiere herramientas que le servirán de apoyo para realizar operaciones en Excel en su trabajo diario en la Institución que labora; además de que estamos contribuyendo a los compromisos que hemos adquirido con la sociedad.

Con este evento académico se contribuye hacia en la promoción de la actualización y formación permanente, así como la renovación de los vínculos que existen entre la educación formal y el mundo del trabajo, con la participación directa del personal de Embotelladora de Colima, S.A. de C.V.

Bienvenidos a la Universidad de Colima

Dra. Susana Aurelia Preciado Jiménez Directora General

INDICE DE CONTENIDO TEMÁTICO

2

TEMA 1 ENTORNO DE UNA HOJA DE CÁLCULO1.1 Introducción...................................................................................................................................... 51.2 Qué ha cambiado y porqué..............................................................................................................51.3 ¿Qué hay en la cinta de opciones?..................................................................................................61.4 Más comandos pero sólo cuando se necesitan...............................................................................71.5 Más opciones si las necesita............................................................................................................81.6 Como se agrega un botón a la barra de herramientas de acceso rápido y como se elimina...........91.7 ¿Dónde están los métodos abreviados de teclado?........................................................................91.8 Una vista nueva............................................................................................................................... 111.9 Con una resolución de pantalla distinta cambia lo que se ve...........................................................12

TEMA 2 EMPEZAR A TRABAJAR EN EXCEL 20072.1 Abrir el archivo................................................................................................................................. 142.2 Insertar una columna....................................................................................................................... 152.3 Modificar y dar formato a datos .......................................................................................................172.4 Escribir una formula......................................................................................................................... 182.5 Agregar encabezados y pies de página...........................................................................................192.6 Imprimir............................................................................................................................................ 202.7 La ventana: nuevo libro.................................................................................................................... 21

TEMA 3 NUEVOS FORMATOS DE ARCHIVO Y NUEVAS OPCIONES AL GUARDAR3.1 Tipos de archivos que puede elegir cuando guarde archivos en Excel 2007...................................223.2 Lo nuevo de Excel 2007................................................................................................................... 233.3 Ponga a prueba sus conocimientos.................................................................................................24

TEMA 4 FORMULAS Y FUNCIONES4.1 Introducir Fórmulas y Funciones......................................................................................................264.2 Operadores más utilizados en las fórmulas o funciones..................................................................274.3 Precedencia de los operadores........................................................................................................284.4 Insertar función con el asistente.......................................................................................................294.5 Utilizar Expresiones como argumentos de las Funciones................................................................314.6 Utilizar Funciones como argumentos de las Funciones...................................................................324.7 Funciones de fecha y hora...............................................................................................................324.8 Practicar el uso de las funciones de fechas y horas........................................................................334.9 Funciones de texto........................................................................................................................... 344.10 Practicar el uso de las funciones de texto......................................................................................354.11 Funciones de búsqueda................................................................................................................. 374.12 Practicar el uso de las funciones que buscan valores....................................................................384.13 Funciones financieras.................................................................................................................... 394.14 Practicar el uso de las Funciones Financieras...............................................................................404.15 Otras funciones.............................................................................................................................. 454.16 Practicar el uso de otras funciones................................................................................................474.17 Manejo de la función SI.................................................................................................................. 484.18 Manejo de la función SI anidada....................................................................................................494.19 Manejo de la función como Y,O.....................................................................................................504.20 Referencias.................................................................................................................................... 524.21 Como cambiar el tipo de referencia...............................................................................................534.22 Referencias a otras hojas o libros..................................................................................................54

TEMA 5 GRÁFICOS5.1 Crear gráficos................................................................................................................................... 55

3

5.2 Añadir una serie de datos................................................................................................................565.3 Modificar las características del gráfico............................................................................................585.4 Modificar el tamaño de un gráfico....................................................................................................605.5 Modificar la posición de un gráfico...................................................................................................605.6 Crear tipos personalizados de gráfico..............................................................................................615.7 Practicar la realización de gráficos a partir de unos datos ya creados en Excel..............................62

TEMA 6 ESQUEMAS Y VISTAS6.1 Creación automática de esquemas..................................................................................................666.2 Practicar cómo crear un esquema automáticamente.......................................................................696.3 Creación manual de esquemas........................................................................................................716.4 Borrar y ocultar un esquema............................................................................................................716.5 Ver una hoja en varias ventanas......................................................................................................726.6 Dividir una hoja en paneles..............................................................................................................736.7 Inmovilizar paneles.......................................................................................................................... 74

TEMA 7 TABLAS DE EXCEL7.1 Crear una tabla................................................................................................................................ 757.2 Modificar los datos de una tabla.......................................................................................................777.3 Modificar la estructura de la tabla....................................................................................................787.4 Estilo de la tabla............................................................................................................................... 797.5 Ordenar una tabla de datos..............................................................................................................79

TEMA 8 TABLAS DINÁMICAS8.1 Creación de una tabla dinámica.......................................................................................................818.2 Eliminar una tabla dinámica.............................................................................................................868.3 Aplicar filtros a una tabla dinámica...................................................................................................868.4 Obtener promedios en una tabla dinámica.......................................................................................868.5 Gráficos con tablas dinámicas.........................................................................................................878.6 Practicar cómo trabajar con tablas dinámicas..................................................................................88

41 ENTORNO DE UNA HOJA DE CÁLCULO

OBJETIVO:

Al finalizar, el alumno registrará el nuevo aspecto de Microsoft Excel 2007, buscará comandos habituales en la cinta de opciones: Cortar, Copiar, Pegar, Insertar filas de hoja, Insertar columnas de hoja y Suma y guardará libros con los nuevos formatos de archivo de Excel.

1.1 Introducción

Se presenta una nueva versión de Excel. Cuando abra Excel verá la hoja de cálculo que le es familiar. También notará que hay nuevos elementos en la parte superior de la ventana.

Los menús y los botones anteriores de Excel han sido reemplazados por esta nueva banda de opciones, con fichas en las que hacer clic para llegar a los comandos. La banda se pensó para facilitar el uso de Excel y la búsqueda de los comandos que más se utilizan.

¿Cómo se trabaja con la banda de opciones? Siga este curso y descúbralo. Vea lo que ha cambiado y por qué los cambios ayudan a crear mejores hojas de cálculo en menos tiempo.

1.2 Qué ha cambiado y porqué

Sí, es mucho lo que ha cambiado, sobre todo en el área superior de la ventana.

5

Pero es un buen cambio, porque lo que se necesita está más visible y más a mano. En lugar de tener unas 30 barras de herramientas ocultas y comandos escondidos entre menús, hay un centro de control, la cinta de opciones, que aglutina los elementos esenciales y los presenta en primer plano.

Cuando pruebe el nuevo diseño, descubrirá que tiene sentido la forma en que se han agrupado los comandos que ya sabe utilizar. Conozca más detalles sobre el nuevo diseño y prepárese para trabajar con la nueva versión de Excel.

1.3 ¿Qué hay en la cinta de opciones?

Son tres los componentes básicos de la cinta de opciones:

Fichas Hay siete en la parte superior y cada una de ellas representa una de las tareas básicas que se hacen en Excel.

Grupos Cada ficha tiene grupos que muestran elementos relacionados entre sí. Comandos Un comando es un botón, un cuadro en el que se escribe información o un

menú.

¿Por dónde se empieza? Se empieza por el principio, por la primera ficha.

Los comandos más importantes de Excel se encuentran en la primera ficha, la ficha Inicio. Estos comandos son los que se han identificado en Microsoft como los más empleados para llevar a cabo tareas básicas en hojas de cálculo.

6

Por ejemplo, los comandos Pegar, Cortar y Copiar aparecen en primer lugar en la ficha Inicio, dentro del grupo Portapapeles. Los comandos para el formato de fuentes aparecen después, dentro del grupo Fuente. Los comandos que centran o alinean texto a la derecha o a la izquierda están en el grupo Alineación y los comandos que insertan y borran celdas, filas, columnas y hojas de cálculo se encuentran en el grupo Celdas.

Los grupos aglutinan todos los comandos que se necesitan para realizar una tarea concreta, y se mantienen a la vista y disponibles para su uso mientras se trabaja en la tarea, en lugar de permanecer escondidos en los menús. Estos comandos esenciales están a la vista en la parte superior del área de trabajo.

He aquí un ejemplo de las ventajas que presenta: si desea mostrar texto en varias líneas de un celda, no es necesario hacer clic en un comando de un menú, en una ficha de un cuadro de diálogo y, por último, en una opción. Sencillamente se hace clic en el botón Ajustar texto

del grupo Alineación, de la ficha Inicio. Lo verá en el ejercicio práctico.

1.4 Más comandos pero sólo cuando se necesitan

Cree un gráfico haciendo clic en un botón de la ficha Insertar del grupo Gráficos. A continuación, aparecerán las fichas de las Herramientas de gráficos: Diseño, Presentación y Formato.

Los comandos de la cinta de opciones son los que más se utilizan. En lugar de mostrar todos los comandos todo el tiempo, Excel 2007 muestra algunos comandos en respuesta a las acciones que se realizan.

7

Por ejemplo, si no hay un gráfico en una hoja de cálculo, los comandos que se usan para trabajar con gráficos no son necesarios.

Pero una vez creado, aparecen las Herramientas de gráficos con tres fichas: Diseño, Presentación y Formato. En dichas fichas están los comandos que se precisan para trabajar con un gráfico. La cinta de opciones responde a la acción del usuario.

Use la ficha Diseño para cambiar el tipo de gráfico o moverlo a otra ubicación; la ficha Presentación para cambiar los títulos y otros elementos del gráfico y la ficha Formato para agregar relleno de color y cambiar el estilo de las líneas. Cuando termine el gráfico, haga clic fuera del área del gráfico. Desaparecerán las Herramientas de gráficos. Para volver a verlas, haga clic dentro del gráfico y se mostrarán.

Así que no se preocupe si no ve todos los comandos todo el tiempo. Dé los primeros pasos y los comandos necesarios estarán a mano.

1.5 Más opciones si las necesita

Haga clic en la flecha en la parte inferior de un grupo para obtener más opciones si las necesita. Haga clic en la flecha en el grupo Fuente.

Se abrirá el cuadro de diálogo Formato de celdas.

Cuando vea esta flecha (denominada iniciador de cuadros de diálogo) en la esquina inferior derecha de un grupo, significa que hay más opciones dentro del grupo. Haga clic en la flecha y se mostrará un cuadro de diálogo o panel de tareas.

8

Por ejemplo, en la ficha Inicio, en el grupo Fuente, se encuentran todos los comandos que más se utilizan para efectuar cambios en las fuentes: cambio de fuente, cambio del tamaño de fuente y formato de negrita, cursiva o subrayado.

Si desea más opciones, por ejemplo, superíndice, haga clic en la flecha de la derecha de Fuente y aparecerá el cuadro de diálogo Formato de celdas, con ésta y otras opciones relativas a las fuentes.

1.6 Cómo se agrega un botón a la barra de herramientas de acceso rápido y como se elimina

Si utiliza con frecuencia comandos que no están tan a mano como le gustaría, es muy sencillo agregarlos a la barra de herramientas de acceso rápido, que se encuentra sobre la cinta de opciones cuando se inicia Excel 2007. En dicha barra, los comandos están siempre visibles y cercanos.

Por ejemplo, si utiliza todos los días el Autofiltro y prefiere evitar hacer clic en la ficha Datos para tener acceso al comando Filtro cada vez, puede agregar Filtro a la barra de herramientas de acceso rápido.

Para ello, haga clic con el botón secundario del mouse (ratón) en Filtro de la ficha Datos y después haga clic en Agregar a la barra de herramientas de acceso rápido.

Para quitar un botón de esa barra, haga clic en ella con el botón secundario del mouse y después haga clic en Eliminar de la barra de herramientas de acceso rápido.

1.7 ¿Dónde están los métodos abreviados de teclado?

9

Puede usar las sugerencias de teclas para, por ejemplo, centrar el texto en Excel.

Presione ALT para que aparezcan las sugerencias de teclas de método abreviado. Después presione H para seleccionar la ficha Inicio. Presione A y después C en el grupo Alineación para centrar el texto seleccionado.

Si trabaja con el teclado más que con el mouse (ratón), le gustará conocer los métodos abreviados de Excel 2007.

El diseño de la cinta de opciones trae consigo nuevos métodos abreviados. ¿Por qué? Porque supone dos grandes ventajas con respecto a versiones anteriores:

Métodos abreviados para cada botón de la cinta de opciones. Métodos abreviados que requieren menor combinación de teclas.

Uso de nuevos métodos abreviados

Los nuevos métodos abreviados también tienen nombre: Sugerencias de teclas, que aparecen al presionar ALT.

Todas las fichas de la cinta de opciones, los comandos de las fichas, la barra de herramientas de acceso rápido y el botón de Microsoft Office tienen sugerencias de teclas.

Presione la tecla de la ficha que desea mostrar. Aparecerán todos los identificadores de sugerencias de teclas para los botones de la ficha. Utilice la tecla correspondiente al botón que desea.

¿Dónde están los antiguos métodos abreviados?

Los antiguos métodos de teclado que comienzan con CTRL se mantienen intactos y los puede seguir utilizando como antes. Por ejemplo, la combinación CTRL+C aún copia algo en el portapapeles y la combinación CTRL+V pega algo del portapapeles.

10

1.8 Una vista nueva

La nueva vista Diseño de página de Excel. Encabezados de columna.

Encabezados de fila. Reglas de margen.

La cinta de opciones no es la única novedad de Excel 2007. La vista Diseño de página también es nueva. Si ha trabajado en la vista Diseño de página de Microsoft Office Word, se alegrará de disfrutar de las mismas ventajas en Excel.

Para llegar a la nueva vista, haga clic en Vista Diseño de página, en la barra de herramientas Ver en la parte inferior derecha de la pantalla. O haga clic en la ficha Vista de la cinta de opciones y después en Vista Diseño de página, en el grupo Vistas de libro.

En la vista Diseño de página hay márgenes de página en la parte superior, lateral e inferior de la hoja de cálculo, además de un espacio azul entre las hojas. La regla lateral y superior ayudan a ajustar los márgenes. Es posible mostrar y ocultar las reglas según convenga (haga clic en Regla, en el grupo Mostrar u ocultar de la ficha Vista).

Con esta nueva vista, no hace falta la vista preliminar para hacer ajustes en la hoja de cálculo antes de imprimirla. Obtendrá más información sobre ello en la siguiente lección.

Es fácil agregar encabezados y pies de página en la vista Diseño de página. A medida que escribe en la nueva área de encabezados y pies de página en la parte superior o inferior de la página, se abre la ficha Diseño con todos los comandos que necesita para crear los

11

encabezados y pies de página. Comprobará exactamente cómo funciona en la siguiente lección.

Es posible ver cada hoja de un libro en la vista que mejor se ajusta a las peculiaridades de la hoja. Simplemente seleccione una vista en la barra de herramientas Ver o en el grupo Vistas de libro en la ficha Vista de cada hoja de cálculo. La vista Normal y la vista previa de salto de página se encuentran ahí.

Sugerencia Si prefiere la vista Diseño de página, pero con un poco menos de espacio blanco en los márgenes, oculte parte de ese espacio blanco. Le mostraremos cómo hacerlo en el ejercicio práctico al final de la lección.

1.9 Con una resolución de pantalla distinta, cambia lo que se ve

El grupo Mostrar u ocultar en la ficha Vista. Todos los comandos del grupo se muestran en alta resolución. En resolución baja tendrá que hacer clic en la flecha del botón del grupo para mostrar los comandos.

Todo lo descrito hasta ahora es de aplicación cuando la pantalla tiene la máxima resolución y la ventana de Excel está maximizada. Si no es así, la apariencia cambia. ¿Cómo? Del modo siguiente:

Resolución baja Si la pantalla tiene una resolución de 800 por 600 píxeles, por ejemplo, algunos grupos de la cinta de opciones sólo mostrarán el nombre del grupo, no de los comandos, y es necesario hacer clic en la flecha del botón para verlos.

Por ejemplo, en la ficha Vista, el grupo Mostrar u ocultar tiene diversos comandos para mostrar u ocultar elementos. Con una resolución mayor, verá todos los comandos del grupo

12

Mostrar u ocultar. Con una resolución de 800 por 600, se ve el botón Mostrar u ocultar, pero no los comandos del grupo.

En ese caso, haga clic en la flecha del botón del grupo Mostrar u ocultar para mostrarlos.

Los grupos que, con una resolución menor, muestran sólo el nombre del grupo son los que tienen los comandos menos utilizados.

Minimizada Con cualquier resolución, si reduce el tamaño de la ventana de Excel, hay un tamaño en el que algunos grupos sólo muestran el nombre y es preciso hacer clic en la flecha del botón del grupo para mostrar los comandos.

Tablet PC Si trabaja en un Tablet PC con una pantalla pequeña, comprobará que la cinta de opciones se ajusta y muestra versiones más reducidas de las fichas y los grupos. Si tiene un monitor mayor, la cinta se ajusta y muestra versiones más grandes.

13

Conclusión:

Conocer el entorno de la hoja de cálculo y la ubicación de los comandos es esencial para poder trabajar con Excel 2007

Empezar a trabajar en Excel

Ha llegado la hora de poner Excel 2007 a trabajar.

OBJETIVO:El participante revisará rá algunas opciones básicas que necesita saber en Excel 2007

2.1 Abrir el archivo

Haga clic en el botón de Microsoft Office para abrir este menú.

14

2 EMPEZAR A TRABAJAR EN EXCEL 2007

En el menú, haga clic en Abrir para abrir un libro existente. O bien, haga clic en Opciones de Excel, en la parte inferior del menú para configurar las

opciones del programa.

Está abriendo un libro creado en una versión anterior de Excel. Haga clic en el botón de

Microsoft Office en la esquina superior izquierda de la ventana. Ahí se encuentran algunos comandos que ha usado para abrir y guardar libros.

Antes de entrar en materia, observe que este menú presenta más opciones, por ejemplo, encontrará la configuración que controla aspectos como activar o desactivar el estilo de referencia F1C1, o mostrar la barra de fórmulas en la ventana del programa. Haga clic en Opciones de Excel, en la parte inferior del menú para tener acceso a ellas.

En versiones anteriores de Excel se establecían dichas opciones en el cuadro de diálogo Opciones, que se abría en el menú Herramientas. En esta versión, muchas de esas opciones están aquí, más visibles y más cerca para trabajar con archivos viejos o nuevos.

Ahora, volviendo al libro, haga clic en Abrir, seleccione el libro que desee y después haga clic en Abrir. Eso es todo lo que hay que hacer para abrir un archivo creado en una versión anterior. Ya puede empezar a trabajar.

2.2 Insertar una columna

La hoja de cálculo contiene filas de productos que se han pedido a varios proveedores.Tiene que agregar una columna de categorías para identificar los diferentes productos como lácteos, cereales, frutas, etc. Esa columna irá entre dos de las columnas de datos que hay en la hoja.

Desea agregar la columna a la derecha de la columna Cantidad, así que hace clic en la columna Proveedor. Después, en la ficha Inicio, en el grupo Celdas, haga clic en la flecha en Insertar. En el menú que se muestra, haga clic en Insertar columnas de hoja. Se insertará una nueva columna en blanco donde podrá escribir los datos nuevos. Una vez agregada la columna y nuevos datos, si necesita ajustar el ancho de columna para encajar los datos, en el grupo Celdas, haga clic en Formato y en la lista que se muestra haga clic en Autoajustar ancho de columna.

En la lista Formato se encuentran todos los comandos necesarios para ajustar el alto de columna y el ancho de fila, así como para mostrar u ocultar filas, columnas y hojas.

15

2.3 Modificar y dar formato a datos

Modificar y dar formato a datos mediante los comandos de los grupos de la ficha Inicio.

Los títulos de columna resaltarán más si están en negrita. Seleccione la fila de los títulos y, a continuación, en la ficha Inicio, en el grupo Fuente, haga clic en Negrita.

Con los títulos aún seleccionados, decide cambiarles de color y de tamaño, para que resalten aún más.

En el grupo Fuente, haga clic en la flecha de Color de fuente y aparecerán muchos más colores entre los que elegir. Podrá ver qué aspecto tendrán los títulos con distintos colores si señala cualquier color y espera un momento. Con esta visualización previa, ya no es necesario hacer una selección para ver el color y después deshacerla si no es lo que se buscaba. Cuando vea un color que le guste, haga clic en él.

Para cambiar el tamaño de fuente, puede hacer clic en el botón Aumentar tamaño de fuente o puede hacer clic en la flecha situada junto al cuadro Tamaño de fuente para ver los tamaños (este método también ofrece la presentación previa).

Teniendo los títulos seleccionados, decide centrarlos en las celdas. En el grupo Alineación,

hace clic en el botón Centrar y listo.

Por último, descubre que necesita escribir un nuevo pedido de la Salsa de pimiento picante de Luisiana. Así que selecciona el nombre del producto y en el grupo Portapapeles, hace

clic en el botón Copiar . Después hace clic en la fila inferior y en el grupo Portapapeles de nuevo hace clic en el botón Pegar .

17

En el ejercicio práctico verá cómo se utilizan los Estilos de celda del grupo Estilo para agregar otro formato rápido.

2.4 Escribir una formula

Para escribir una fórmula sencilla, en la ficha Inicio, del grupo Modificar, haga clic en el botón Suma.

Botón Suma de la ficha Inicio. Resultado de la fórmula.

Antes de entregar el informe, desea sumar los números de la columna Cantidad. Esto se

consigue fácilmente usando el botón Suma .

Se encuentra en el grupo Modificar de la ficha Inicio. Coloque el cursor en la última celda de la columna Cantidad y haga clic en el botón Suma. A continuación, presione ENTRAR y Excel calculará la suma de los números mediante la función SUMA.

Si desea realizar algo más que sumar, haga clic en la flecha del botón Suma . Después haga clic en alguna de las funciones de la lista que se muestra: Promedio, Contar, Máx o Mín. Si hace clic en Más funciones, se abrirá el cuadro de diálogo Insertar función, en el que puede elegir entre todas las funciones de Excel. O bien, haga clic en la ficha Fórmulas y eche un vistazo a los grupos Biblioteca de funciones y Cálculo.

18

2.5 Agregar encabezados y pies de página

Para agregar un encabezado, en la vista Diseño de página, haga clic en el área "Haga clic para agregar encabezado". Aparecerán las Herramientas de encabezado y pie de página y la ficha Diseño en la parte superior de la cinta de opciones.

Como toque final, suponga que decide agregar encabezados y pies de página a la hoja de cálculo para que se vea claro de qué tratan los datos.

Primero, cambie a la vista Diseño de página. Haga clic en la ficha Vista y después en Vista Diseño de página en el grupo Vistas de libro. (O haga clic en el botón central de la barra de herramientas Ver , en la parte inferior de la ventana).

Es muy fácil agregar encabezados y pies de página en la vista Diseño de página. En lugar de abrir un cuadro de diálogo para agregar un encabezado, sólo tiene que colocar el punto de inserción en el área situada en la parte superior de la página que dice Haga clic para agregar encabezado.

En cuanto hace clic, las fichas Herramientas para encabezado y pie de página y Diseño aparecen en la cinta de opciones. Contienen todos los comandos necesarios para trabajar con los encabezados y pies de página. También existe un comando nuevo en Excel 2007 para agregar encabezados y pies de página diferentes en las páginas pares e impares.

Como encabezado de este informe, escriba Informe de ventas de Junio y ya está. En cuanto haga clic en la hoja de cálculo, desaparecerán las Herramientas para encabezado y pie de página y Diseño, con sus comandos, hasta que vuelva a necesitarlos. Para volver a mostrarlos, haga clic en el área del encabezado o del pie.

19

En el ejercicio práctico verá cómo se trabaja con encabezados y pies de página.

2.6 Imprimir

Hacer ajustes en la hoja de cálculo en la ficha Diseño de página antes de imprimir.

Ha llegado el momento de imprimir el informe para llevar las copias que necesita a la reunión.

En versiones anteriores de Excel, seguramente tenía que pasar varias veces de la vista Diseño de página a la vista Normal para ajustar la hoja de cálculo antes de imprimir. O quizá hacía varios intentos hasta conseguir que las hojas impresas tuviesen el aspecto final esperado.

En la vista Diseño de página, puede realizar ajustes y ver los cambios en la pantalla antes de imprimir. Haga clic en la ficha Diseño de página para definir las opciones de impresión.

En esta ficha, en el grupo Configurar página, haga clic en Orientación y seleccione Horizontal o Vertical. En la página Diseño de página, verá cómo cambia la orientación y la apariencia de los datos.

Estando en el grupo Configurar página, haga clic en Tamaño para elegir el tamaño del papel. Observará el resultado de las opciones elegidas según las va aplicando. Lo que ve en pantalla es lo que se imprimirá.

20

En el ejercicio práctico verá más cosas relacionadas con los comandos de la ficha Diseño de página

2.7 La ventana Nuevo Libro

La ventana Nuevo libro incluye todas las opciones, desde un libro en blanco hasta plantillas en línea de Excel.

Cuando hace clic en el botón de Microsoft Office y después en Nuevo, se abre la ventana Nuevo libro. En la parte superior de la ventana, puede seleccionar un nuevo libro en blanco o una plantilla.

A la izquierda encontrará diferentes categorías de plantillas instaladas con Excel 2007. Haga clic en Destacado bajo Microsoft Office Online a la izquierda para obtener vínculos a demostraciones de vídeo y plantillas en línea para presupuestos, calendarios, informes de gastos, etc.

21

Conclusión:

Aprender la utilidad del botón de Microsoft Office y explorar los nuevos pasos para la creación de un nuevo libro y darle diseño son opciones básicas que se deben de identificar en Excel 2007

OBJETIVO:El participante identificará los diversos tipos de archivos que se pueden almacenar con Excel 2007

3.1 Tipos de archivos que puede elegir cuando guarde archivos en Excel 2007

Puede elegir entre varios tipos de archivos para guardar en Excel 2007.

Libro de Excel (*.xlsx) Guarde libros que no contengan macros ni código de Microsoft Visual Basic para Aplicaciones (VBA) como libros de Excel. Si intenta guardar como Libro de Excel un libro que contiene comandos de macros o proyectos de VBA, Excel le notificará que se eliminará la macro o el código VBA.

Libro de Excel habilitado para macros (*.xlsm) Guarde un libro con este formato de archivo cuando contenga macros o código VBA. Si intenta guardar un libro que contiene macros o VBA como Libro de Excel, Excel le advertirá para que no elija esta opción.

Plantilla de Excel (*.xltx) Guarde el libro como este tipo cuando necesite una plantilla.

Plantilla de Excel habilitada para macros (*.xltm) Guarde un libro como este tipo de archivo cuando necesite una plantilla y el libro contenga macros o código de VBA.

Libro binario de Excel (*.xlsb) Guarde el libro como este tipo de archivo cuando tenga un libro especialmente grande; este tipo de archivo se abrirá antes que un libro de Excel grande. Este formato mantiene las nuevas características de Excel salvo XML.

22

Libro de Excel 97-2003 (*.xls) Utilice este formato cuando tenga que compartir el archivo con alguien que trabaja con versiones anteriores del programa y no cuenta con el Paquete de compatibilidad de Microsoft para Office 2007.

Libro de Microsoft Excel 5.0/95 (*.xls) Utilice este formato cuando tenga que compartir el archivo con alguien que utiliza Microsoft Excel 5.0. La mayor parte de las características de Excel 2007 no estarán disponibles.

3.2 Lo nuevo de Excel 2007

El nuevo formato de archivo incluye mejoras en Excel.

El nuevo formato de archivo trae consigo mejoras en Excel. Las ventajas principales son:

Nuevas características Además de las características vistas en las lecciones anteriores, el número de filas ha crecido de 65.536 a 1.048.576 y el número de columnas ha aumentado de 256 a 16.384. Puede escribir fórmulas más largas en la nueva barra de fórmulas de tamaño variable. Y si hace clic en grandes fragmentos de texto en una celda, la barra de fórmulas ya no ocupa parte de la cuadrícula de la hoja de cálculo.

Archivos más seguros Los libros que contienen macros o código no deseado se identifican y bloquean más fácilmente.

Menor riesgo de que los archivos se dañen Excel ha mejorado la capacidad de abrir archivos dañados y recuperar una parte del trabajo que, de otro modo, se perdería.

23

Menor tamaño de archivo Los libros están comprimidos; el tamaño de archivo es entre 50 y 75 por ciento más pequeño que en versiones anteriores de Excel. Los archivos comprimidos se abren y se guardan del mismo modo que abría y guardaba cualquier archivo de Excel.

Datos más útiles Se puede hacer más con los datos porque la base de XML facilita la tarea de integrarlos con conjuntos de datos de otros programas y equipos. Por ejemplo, almacenar los datos presupuestarios en un programa especializado, seleccionar la información que necesita e importarla a un documento de Word, a una hoja de Excel y a una base de datos de Access.

3.3 Ponga a prueba sus conocimientos

Realice la siguiente prueba para asegurarse de que ha entendido todo el material.

Si guarda un archivo creado en una versión anterior de Excel como archivo de Excel 2007,

dicho archivo usará todas las nuevas características de Excel.Verdadero.

Falso.

El formato de archivo de Excel 2007 cambia a XML porque:Permite agregar nuevas características a Excel.

Los archivos son más seguros.

Un riesgo menor de que los archivos se dañen.

Un tamaño de archivo menor.

Todo lo anterior.

Con el Paquete de compatibilidad de Microsoft instalado, los archivos de Excel 2007 pueden

abrirse en las siguientes versiones de Excel:Excel 95- Excel 2003.

Excel 97- Excel 2003.

Excel 2000- Excel 2003.

Alguien le envía un archivo de Excel 2003 y usted lo abre en Excel 2007. Cuando termina de

trabajar con el archivo en la versión 2007, el archivo se guardará automáticamente como

Excel 2007, a menos que cambie la opción.

24

Sí, hay nuevas ca

Sí, cuesta menos

Sí, Excel ha mejor

Sí, los libros está

No; vuelva a inten

No, inténtelo de n

Verdadero.

Falso.

25

Conclusión:

Identificar los diferentes tipos de archivo que podemos crear con Excel 2007 y conocer las nuevas mejoras nos amplía la visión para conocer la potencialidad de la hoja de cálculo.

No, no es correct

OBJETIVO:Que el participante adquiera los conocimientos teóricos y prácticos que le permitan realizar formulas y diversos tipos de funciones.

Qué es una hoja de cálculo sino una base de datos que utilizamos con una serie de fórmulas para evitar tener que re-calcular por cada cambio que hacemos. Por eso esta unidad es fundamental para el desarrollo del curso y la buena utilización de Excel.

Vamos a profundizar en el manejo de funciones ya definidas por Excel 2007 para agilizar la creación de hojas de cálculo, estudiando la sintaxis de éstas así como el uso del asistente para funciones, herramienta muy útil cuando no conocemos muy bien las funciones existentes o la sintaxis de éstas.

4.1 Introducir Fórmulas y Funciones

Una función es una fórmula predefinida por Excel 2007 (o por el usuario) que opera con uno o más valores y devuelve un resultado que aparecerá directamente en la celda o será utilizado para calcular la fórmula que la contiene.

La sintaxis de cualquier función es:

nombre_función(argumento1;argumento2;...;argumentoN)

Siguen las siguientes reglas:

- Si la función va al comienzo de una fórmula debe empezar por el signo =.

- Los argumentos o valores de entrada van siempre entre paréntesis. No dejes espacios antes o después de cada paréntesis.

- Los argumentos pueden ser valores constantes (número o texto), fórmulas o funciones.

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

Ejemplo: =SUMA(A1:C8)

26

4 FÓRMULAS Y FUNCIONES

Tenemos la función SUMA() que devuelve como resultado la suma de sus argumentos. El operador ":" nos identifica un rango de celdas, así A1:C8 indica todas las celdas incluidas entre la celda A1 y la C8, así la función anterior sería equivalente a:

=A1+A2+A3+A4+A5+A6+A7+A8+B1+B2+B3+B4+B5+B6+B7+B8+C1+C2+C3+C4+C5+C6+C7+C8

En este ejemplo se puede apreciar la ventaja de utilizar la función.

Las fórmulas pueden contener más de una función, y pueden aparecer funciones anidadas dentro de la fórmula.

Ejemplo: =SUMA(A1:B4)/SUMA(C1:D4)

Existen muchos tipos de funciones dependiendo del tipo de operación o cálculo que realizan. Así hay funciones matemáticas y trigonométricas, estadísticas, financieras, de texto, de fecha y hora, lógicas, de base de datos, de búsqueda y referencia y de información. Para introducir una fórmula debe escribirse en una celda cualquiera tal cual introducimos cualquier texto, precedida siempre del signo =.

4.2 Operadores más utilizados en las fórmulas o funciones

Excel permite que en una función tengamos varios operadores para tratar los datos. Los operadores son símbolos que identifica Excel con operaciones aritméticas y es el enlace entre 2 argumentos.

En la tabla podemos ver los operadores más utilizadosSIMBOLO DEL

OPERADOR OPERACIÓN QUE

REALIZA + SUMA - RESTA * MULTIPLICA / DIVIDE ^ EXPONENCIACIÓN

& UNIÓN / CONCATENAR

= Comparación IGUAL QUE

> Comparación MAYOR QUE

< Comparación MENOR QUE

>= Comparación

27

MAYOR IGUAL QUE

<= Comparación MENOR IGUAL QUE

<> Comparación DISTINTO

En una fórmula o función pueden utilizarse tanto operadores como sea necesario teniendo en cuenta siempre que los operadores hacen siempre referencia a dos argumentos. Pueden crearse fórmulas verdaderamente complejas. Veamos un ejemplo.

= ((SUMA(A1:A7)*SUMA(B1:B7)) / (SUMA(C1:C7)*SUMA(D1:D7)))=(F8*SUMA(G1:G5))

4.3 Precedencia de los operadores

Hemos visto que una fórmula puede ser muy compleja, utilizando multitud de operadores. Excel como cualquier operador matemático tiene unas ciertas reglas para saber que operaciones debe realizar primero para que el resultado obtenido sea el correcto.

En la siguiente tabla mostramos las precedencias establecidas por ExcelSIMBOLO DEL

OPERADOR OPERACIÓN QUE

REALIZA PRECEDENCIA

^ EXPONENCIACIÓN 1 * MULTIPLICA 2 / DIVIDE 2 + SUMA 3 - RESTA 3

& UNIÓN / CONCATENAR 4

= Comparación IGUAL QUE 5

> Comparación MAYOR QUE 5

< Comparación MENOR QUE 5

>=Comparación MAYOR IGUAL QUE

5

<=Comparación MENOR IGUAL QUE

5

<> Comparación DISTINTO 5

28

Además de esta tabla de precedencias la precedencia máxima, es decir la operación que antes se evalúa, es aquella que va entre paréntesis.

29

4.4 Insertar función con el asistente

Una función como cualquier dato se puede escribir directamente en la celda si conocemos su sintaxis, pero Excel 2007 dispone de una ayuda o asistente para utilizarlas, así nos resultará más fácil trabajar con ellas.

Si queremos introducir una función en una celda:

Situarse en la celda donde queremos introducir la función.

Hacer clic en la ficha Fórmulas.

Elegir la opción Insertar función.

O bien, hacer clic sobre el botón de la barra de fórmulas.

Aparecerá el siguiente cuadro de diálogo Insertar función:

Excel 2007 nos permite buscar la función que necesitamos escribiendo una breve descripción de la función necesitada en el recuadro Buscar una función: y a continuación hacer clic sobre el botón , de esta forma no es necesario conocer cada una de las funciones que incorpora Excel ya que el nos mostrará en el cuadro de lista Seleccionar una función: las funciones que tienen que ver con la descripción escrita.

Para que la lista de funciones no sea tan extensa podemos seleccionar previamente una categoría del cuadro combinado O seleccionar una categoría:, esto hará que en el

30

cuadro de lista sólo aparezcan las funciones de la categoría elegida y reduzca por lo tanto la lista. Si no estamos muy seguros de la categoría podemos elegir Todas.

En el cuadro de lista Seleccionar una función: hay que elegir la función que deseamos haciendo clic sobre ésta.

Observa como conforme seleccionamos una función, en la parte inferior nos aparecen los distintos argumentos y una breve descripción de ésta. También disponemos de un enlace Ayuda sobre esta función para obtener una descripción más completa de dicha función.

A final, hacer clic sobre el botón Aceptar.

Justo por debajo de la barra de fórmulas aparecerá el cuadro de diálogo Argumentos de función, donde nos pide introducir los argumentos de la función: Este cuadro variará según la función que hayamos elegido, en nuestro caso se eligió la función SUMA ().

En el recuadro Número1 hay que indicar el primer argumento que generalmente será una celda o rango de celdas tipo A1:B4 . Para ello, hacer clic sobre le botón para que el cuadro se haga más pequeño y podamos ver toda la hoja de cálculo, a continuación seleccionar el rango de celdas o la celda deseadas como primer argumento (para seleccionar un rango de celdas haz clic con el botón izquierdo del ratón sobre la primera celda del rango y sin soltar el botón arrástralo hasta la última celda del rango) y pulsar la tecla ENTER para volver al cuadro de diálogo.

En el recuadro Número2 habrá que indicar cuál será el segundo argumento. Sólo en caso de que existiera.

31

Si introducimos segundo argumento, aparecerá otro recuadro para el tercero, y así sucesivamente.

Cuando tengamos introducidos todos los argumentos, hacer clic sobre el botón Aceptar.

Si por algún motivo insertáramos una fila en medio del rango de una función, Excel expande automáticamente el rango incluyendo así el valor de la celda en el rango. Por ejemplo: Si tenemos en la celda A5 la función =SUMA(A1:A4) e insertamos un fila en la posición 3 la fórmula se expandirá automáticamente cambiando a =SUMA(A1:A5).

En la ficha Inicio o en la de Fórmulas encontrarás el botón Autosuma que nos permite realizar la función SUMA de forma más rápida.

Con este botón tenemos acceso también a otras funciones utilizando la flecha de la derecha del botón.

Al hacer clic sobre ésta aparecerá la lista desplegable de la derecha:

Y podremos utilizar otra función que no sea la Suma, como puede ser Promedio (calcula la media aritmética), Cuenta (cuenta valores), Máx (obtiene el valor máximo) o Mín (obtiene el valor mínimo). Ademas de poder accesar al diálogo de funciones a través de Más Funciones...

4.5 Utilizar Expresiones como argumentos de las Funciones

Excel permite que en una función tengamos como argumentos expresiones, por ejemplo la suma de dos celdas (A1+A3). El orden de ejecución de la función será primero resolver las expresiones y después ejecutar la función sobre el resultado de las expresiones.

Por ejemplo, si tenemos la siguiente función =Suma((A1+A3);(A2-A4)) donde:A1 vale 1A2 vale 5A3 vale 2A4 vale 3

Excel resolverá primero las expresiones (A1+A3) y (A2-A4) por lo que obtendremos los valores 3 y 2 respectivamente, después realizará la suma obteniendo así 5 como resultado.

4.6 Utilizar Funciones como argumentos de las Funciones

32

Excel también permite que una función se convierta en argumento de otra función, de esta forma podemos realizar operaciones realmente complejas en una simple celda. Por ejemplo =MAX(SUMA(A1:A4);B3) , esta fórmula consta de la combinación de dos funciones, la suma y el valor máximo. Excel realizará primero la suma SUMA(A1:A4) y después calculará el valor máximo entre el resultado de la suma y la celda B3.

4.7

33

Funciones de fecha y hora

De entre todo el conjunto de funciones, en este apartado estudiaremos las funciones dedicadas al tratamiento de fechas y horas.

Y estas son todas las posibles funciones ofrecidas por Excel.

En varias funciones veremos que el argumento que se le pasa o el valor que nos devuelve es un "número de serie". Pues bien, Excel llama número de serie al número de días transcurridos desde el 0 de enero de 1900 hasta la fecha introducida, es decir coge la fecha inicial del sistema como el día 0/1/1900 y a partir de ahí empieza a contar, en las funciones que tengan núm_de_serie como argumento, podremos poner un número o bien la referencia de una celda que contenga una fecha.

Función DescripciónAHORA() Devuelve la fecha y la hora actual AÑO(núm_de_serie) Devuelve el año en formato año DIA(núm_de_serie) Devuelve el día del mes

DIAS360(fecha_inicial;fecha_final;método) Calcula el número de días entre las dos fechas

DIASEM(núm_de_serie;tipo) Devuelve un número del 1 al 7FECHA(año;mes;día) Devuelve la fecha en formato fecha FECHANUMERO(texto_de_fecha) Devuelve la fecha en formato de fecha

HORA(núm_de_serie) Devuelve la hora como un número del 0 al 23

HORANUMERO(texto_de_fecha) Convierte una hora de texto en un número

HOY() Devuelve la fecha actual

MES(núm_de_serie) Devuelve el número del mes en el rango del 1 (enero) al 12 (diciembre)

MINUTO(núm_de_serie) Devuelve el minuto en el rango de 0 a 59

NSHORA(hora;minuto;segundo) Convierte horas, minutos y segundos dados como números

SEGUNDO(núm_de_serie) Devuelve el segundo en el rango de 0 a 59

4.8 Practicar el uso de las funciones de fechas y horas

Ejercicio paso a paso.

1 Si no tienes abierto Excel2007, ábrelo para realizar el ejercicio.

34

2 Sitúate en la celda A1 y pulsa sobre , selecciona la categoría de fecha y hora y elige la función AHORA().

3 Pulsa el botón Aceptar.

Aparece un cuadro de diálogo indicando que la función no tiene argumentos.

4 Pulsa de nuevo sobre Aceptar.

5 Sitúate en la celda B2 y escribe el día de hoy en número. Ej. 7

6 Sitúate en la celda B3 y escribe el mes actual en número. Ej. 8

7 Sitúate en la celda B4 y escribe el año actual en número. Ej. 2010

8 Sitúate en la celda C5 y pulsa sobre , escoge la función FECHA() y pulsa el botón Aceptar.

9 Selecciona como argumentos las celdas B4 --> para año, B3 --> para mes y B2 --> para día, pulsa Aceptar.

Vamos a calcular nuestra edad.

10 Sitúate en la celda D1 y escribe tu fecha de nacimiento en formato (dÍa/mes/año)

11 En la celda E1 escribe =HOY()

12 En la celda E2 selecciona la función DIAS360, como fecha inicial la celda D1 (fecha nacimiento), como fecha final E1 (el día de hoy) y en método escribe Verdadero.

Como resultado nos aparece los días transcurridos desde la fecha D1 y la fecha E1.

13 Ahora en la celda F3 escribe =E2/360 para obtener los años.

El resultado aparece con decimales, para que nos salga solo la parte entera podemos utilizar la función =ENTERO(E2/360).

Hemos utilizado cuatro de las funciones más utilizadas y que ofrecen muchas posibilidades.

14 Guarda el libro de trabajo en la carpeta Mis documentos del disco duro con el nombre de Funciones con fechas.

15 Cierra el libro de trabajo.

4.9 Funciones de texto

Una hoja de cálculo está pensada para manejarse dentro del mundo de los números, pero Excel también tiene un conjunto de funciones específicas para la manipulación de texto.

35

Estas son todas las funciones de texto ofrecidas por Excel.

Función Descripción

CARACTER(número) Devuelve el carácter especificado por el número

CODIGO(texto) Devuelve el código ASCII del primer carácter del texto

CONCATENAR(texto1;texto2;...;textoN) Devuelve una cadena de caracteres con la unión

DECIMAL(número;decimales;no_separar_millares) Redondea un número pasado como parámetro

DERECHA(texto;núm_de_caracteres) Devuelve el número de caracteres especificados

ENCONTRAR(texto_buscado;dentro_del_texto;núm_inicial)

Devuelve la posición inicial del texto buscado

ESPACIOS(texto) Devuelve el mismo texto pero sin espacios

EXTRAE(texto;posicion_inicial;núm_caracteres) Devuelve los caracteres indicados de una cadena

HALLAR(texto_buscado;dentro_del_texto;núm_inicial) Encuentra una cadena dentro de un texto

IGUAL(texto1;texto2) Devuelve un valor lógico (verdadero/falso)

IZQUIERDA(texto;núm_de_caracteres) Devuelve el número de caracteres especificados

LARGO(texto) Devuelve la longitud del texto

LIMPIAR(texto) Limpia el texto de caracteres no imprimibles

MAYUSC(texto) Convierte a mayúsculasMINUSC(texto) Convierte a minúsculasMONEDA(número;núm_de_decimales) Convierte a moneda

NOMPROPIO(texto) Convierte a mayúscula la primera letra del texto

REEMPLAZAR(texto_original;num_inicial;núm_de_caracteres;texto_nuevo)

Reemplaza parte de una cadena de texto por otra

REPETIR(texto;núm_de_veces) Repite el textoSUSTITUIR(texto;texto_original;texto_nuevo;núm_de_ocurrencia)

Reemplaza el texto con texto nuevo

T(valor) Comprueba que el valor es texto

TEXTO(valor;formato) Convierte un valor a texto

TEXTOBAHT(número) Convierte un número a texto tailandés (Baht)

VALOR(texto) Convierte un texto a número

36

4.10 Practicar el uso de las funciones de texto



2 Sitúate en la celda A1 y pulsa sobre , selecciona la Categoría Texto y elige la función CODIGO(), pulsa sobre Aceptar.

3 Escribe como argumento de esa función la letra ”a” y pulsa Intro.

Vemos que obtenemos el código 97, este es el número asignado a la letra a, ahora vamos a comprobar que es cierto con la función CARACTER().

4 Sitúate en la celda B1 y pulsa sobre , selecciona la Categoría Texto y selecciona la función CARACTER().

5 Como argumento escribe 97. Comprobamos que efectivamente nos devuelve la letra "a".

6 Sitúate en la celda B2 y escribe "Hoy es ". Fíjate en dejar los espacios en blanco.

7 Sitúate en la celda B3 y escribe " Agosto ". Fíjate en dejar los espacios en blanco.

8 En la celda B4 escribe 7 y en la celda B5 escribe 2010.

Ahora vamos a crear una frase concatenando varias celdas.

9 Sitúate en la celda C5 y pulsa sobre , selecciona la Categoría Texto

10 Selecciona la función CONCATENAR() y como argumentos escribe en el primer argumento B2, en el segundo B4, como tercer argumento escribe " de", como cuarto argumento escribe B3, como quinto argumento escribe "de " y como último argumento escribe B5.

11 Para que nos indique la fecha en una sola celda la función debe quedar así =CONCATENAR(B2;B4;" de";B3;"de ";B5).

Vamos a utilizar ahora la Función NOMPROPIO() que también es de gran utilidad para formatear una cadena de texto.

37

12 Sitúate en la celda A6 y escribe "pepe" en la celda B6 escribe "gutierrez" y en la celda C6 escribe "ruiz". No escribas mayúsculas.

13 Ahora en la celda D6 pulsa sobre , selecciona la categoria de Texto, selecciona la Función CONCATENAR() pulsa Aceptar y escoge como parámetros A6, B6 y C6. Debemos añadir los espacios para separar el nombre completo. La fórmula debe quedar así =CONCATENAR(A6;" ";B6;" ";C6).

Ya tenemos el nombre completo en una celda, ahora sí podemos hacer uso de la Función NOMPROPIO().

14 Sitúate en la celda G6 y selecciona la Función NOMPROPIO() y pásale como parámetro la celda D6.

Debemos obtener en la celda G6 Pepe Gutierrez Ruiz. Nos ha puesto las iniciales en mayúsculas.

15 Guarda el libro de trabajo en la carpeta Mis documentos del disco duro con el nombre Funciones con texto.


38

4.11 Funciones de búsqueda

En una hoja de Excel es muy importante coger los datos correctos para trabajar con las fórmulas diseñadas. Por eso existe una agrupación de funciones específicas para realizar búsquedas de datos.

Comprendamos qué es en sí una búsqueda, cuando queremos encontrar alguna información de algo no buscamos directamente por lo que buscamos pues lo desconocemos, realizamos una búsqueda de una propiedad o algo similar que conocemos que puede tener lo que buscamos. Por ejemplo, si buscamos a una persona, describimos su aspecto físico, si buscamos el nº de teléfono de un restaurante, buscamos en la guía de teléfonos por el nombre del restaurante. Normalmente el dato que queremos encontrar no lo conocemos por eso buscamos por otros datos que sí conocemos.

Estas son las funciones disponibles por Excel para realizar búsquedas:

Función Descripción

AREAS(ref) Devuelve el número de rangos de celdas contiguas

BUSCAR(...) Busca valores de un rango de una columna o una fila

BUSCARH(valor_buscado;matriz_buscar_en;indicador_filas;ordenado)

Busca en la primera fila de la tabla o matriz de valores

BUSCARV(valor_buscado;matriz_buscar_en;indicador_columnas;ordenado)

Busca un valor en la primera columna de la izquierda

COINCIDIR(valor_buscado;matriz_buscar_en;tipo_de_coincidencia)

Devuelve la posición relativa de un elemento

COLUMNA(ref) Devuelve el número de columna de una referencia

COLUMNAS(matriz) Devuelve el número de columnas que componen la matriz

DESREF(ref;filas;columnas;alto;ancho) Devuelve una referencia a un rango

DIRECCION(fila;columna;abs;a1;hoja) Crea una referencia de celda en forma de texto

ELEGIR(num_indice;valor1;valor2;...) Elige un valor o una accion de una lista de valores

FILA(ref) Devuelve el número de filaFILAS(matriz) Devuelve el número de filasHIPERvínculo(ubicación_del_vínculo;nombre_descriptivo) Crea un acceso directo a un documento

IMPORTARDATOSDINAMICOS(camp_datos;tablas_dinámicas;campo1;elemento1;campo2;elemento2...)

Extrae datos almacenados en una tabla dinámica

INDICE(matriz;num_fila;num_columna) Devuelve el valor de una celda en la intersección de una fila y una columna

39

INDIRECTO(ref;a1) Devuelve una referencia especificada

TRANSPONER(matriz) Intercambia las filas por las columnas en una matriz

4.12 Practicar el uso de las funciones que buscan valores



2 Escribe en la Columna B unos 10 nombres de personas conocidas e imaginarias, por ejemplo José, Juan, Javi, María... Cada uno en una celda, pero ten en cuenta de ponerlos siempre en la columna B.

3 Ahora en la Columna C escribe números de teléfono al lado de cada nombre que has introducido antes.

Vamos a hacer uso de la función BUSCAR(), utilizaremos la celda D5 para introducir el nombre a buscar y la celda D11 para albergar el teléfono de la persona buscada.

4 Así pues, sitúate en la celda D11 y pulsa sobre , selecciona la categoría de búsqueda y referencia y elige la función BUSCAR().

5 Pulsa Aceptar.

6 En el parámetro valor_buscado introduce o selecciona la celda D5, en vector_de_comparación escribe "B:B" o selecciona la Columna B y en el parámetro vector_resultado escribe "C:C" o selecciona la Columna C.

7 Introduce un nombre en la celda D5, un valor que se encuentre en la columna B y mira que ocurre. En la celda D11 debe aparecer el teléfono de la persona.

Ahora vamos a utilizar la función TRANSPONER(), que es una poco más compleja.

Recordemos que transponer significa cambiar filas por columnas y viceversa. Es decir si tenemos los datos en fila, al transponer obtendremos los datos en columna.

8 Sitúate en la celda A15 y pulsa sobre , selecciona la categoría de búsqueda y referencia y elige la función TRANSPONER(). Pulsa Aceptar.

En el único parámetro que nos pide "Matriz" debemos seleccionar la matriz de filas y columnas que vamos a transponer.

9 Seleccionar la matriz de B1:C10.

La formula ya está hecha, pero vemos que nos da un error de valor, esto se debe a que la fórmula hace referencia a una matriz y no a una única celda. Para que la transposición se

40

realice correctamente debemos repetir la fórmula para todas las celdas a transponer siguiendo los siguientes pasos.

10 A partir de la celda de la fórmula (A15) está incluida, selecciona las celdas necesarias para cubrir la matriz transpuesta, es decir una matriz de 2 filas por 10 columnas que se corresponderán con la matriz A15:J16, pulsar F2 y seguidamente Ctrl+Mayus+Enter. De este modo la formula se copia al resto de celdas y se ejecuta correctamente.

11 Guarda el libro de trabajo en la carpeta Mis documentos del disco duro con el nombre de Funciones de búsqueda.


4.13 Funciones financieras

Excel es una de las herramientas más potentes para trabajar con información y cálculos financieros, ofrece una amplia gama de funciones prediseñadas para crearte tu propia "caja de ahorros en casa".

Todas estas funciones están agrupadas en la categoría de Financieras.

Vamos a estudiar la amplia gama de funciones financieras que nos ofrece Excel:Función Descripción y Ejemplo

DB(costo;valor_residual;vida;periodo;mes)Devuelve la depreciación de un bien para

un período especificado, método de depreciación de saldo fijo

DDB(costo;valor_residual;vida;periodo;factor)Devuelve la depreciación de un bien para

un período especificado, mediante el método de depreciación por doble

disminución de saldo

DVS(costo;valor_residual;vida;periodo_inicial;periodo_final;factor;sin_cambios)

Devuelve la depreciación de un bien para un período especificado, incluyendo

periodos parciales

INT.PAGO.DIR(tasa;periodo;nper;va) Calcula el interés pagado durante un período específico de una inversión

NPER(tasa;pago;va;vf;tipo) Devuelve el número de pagos de una inversión

PAGO(tasa;nper;va;vf;tipo) Devuelve el pago de un préstamo basado en pagos y tasas de interés constantes

PAGOINT(tasa;periodo;nper;va;vf;tipo) Devuelve el interés pagado por una inversión durante periodo determinado

PAGOPRIN(tasa;periodo;nper;va;vf;tipo) Devuelve el pago de un capital de una inversión determinada

SLN(costo;valor_residual;vida) Devuelve la depreciación por método directo de un bien durante un período

41

dado

SYD(costo;valor_residual;vida;periodo)Devuelve la depreciación por método de

anualidades de un bien durante un período específico

TASA(nper;pago;va;vf;tipo;estimar) Devuelve la tasa de interés por periodo de un préstamo o una inversión

TIR(valores;estimar)Devuelve la tasa interna de retorno de una

inversión para una serie de valores en efectivo

TIRM(valores;tasa_financiamiento;tasa_reinversión)

Devuelve la tasa interna de retorno modificada

VA(tasa;nper;pago;vf;tipo) Devuelve el valor actual de una inversión

VF(tasa;nper;pago;vf;tipo)Devuelve el valor futuro de una inversión basada en pagos periódicos y constantes

más una tasa de interés constante

VNA(tasa;valor1;valor2;...)Devuelve el valor neto actual de una

inversión a partir de una tasa de descuentos y una serie de pagos futuros

4.14 Practicar el uso de las Funciones Financieras


Vamos a desarrollar un ejemplo práctico para calcular el pago de un préstamo basándonos en pagos constantes y una tasa de interés constante.


Para realizar este ejercicio haremos uso de la función PAGO.

2 Haz clic sobre el botón de la barra de fórmulas.

Aparecerá el cuadro de diálogo Insertar función.

3 Selecciona del recuadro Selecciona una categoría la opción Financieras.

4 Elige del recuadro Selecciona una función, PAGO.

La sintaxis de la función PAGO es PAGO(tasa,nper,va,vf,tipo), vamos a describir cada uno de sus parámetros:

tasa = es el tipo de interés del préstamo.

42

nper = es el número total de pagos del préstamo.

va = es el valor actual del total de pagos

vf = es el valor futuro o un saldo en efectivo que se desea lograr tras el último pago. Si vf se omite, se asume que vale 0, es decir, tras el último pago no queda ningún saldo pendiente lo que ocurre cuando se trata de un préstamo.

tipo = indica el vencimiento de pagos.

(tipo = 0) --> al final del período

(tipo = 1) --> al inicio del período

Ahora que ya conocemos los parámetros que necesita la función, podemos crear el ejemplo:

Vamos a pedir un préstamo de $ 100,000.00, en un período de 30 años, es decir 30*12=360 nº de pagos mensuales, a un interés del 12%.

Así pues, ya tenemos un ejemplo de un préstamo.

5 Sitúate en la celda A1 y escribe Préstamo

6 Sitúate en la celda A2 y escribe Tasa

7 Sitúate en la celda A3 y escribe Nº Pagos

8 Sitúate en la celda A4 y escribe Tipo

9 Sitúate en la celda A5 y escribe Cuota Mensual

10 Sitúate en la celda B1 y escribe 100,000

11 Sitúate en la celda B2 y escribe 12 %

12 Sitúate en la celda B3 y escribe 360


14 Sitúate en la celda B5 y escribe =PAGO(B2/12;B3;B1;0;B4)

Con esta función indicamos que el vencimiento del pago se realiza al final del período y que no existen cuotas al finalizar los pagos.

43

Como resultado debemos obtener -$1,028.61 que será la cuota mensual. El número sale negativo porque el efectivo que se paga, por ejemplo depósitos en cuentas de ahorros, cuotas de un préstamo, se representa con números negativos; el efectivo que se recibe, se representa con números positivos.

Con la función PAGO también podemos calcular qué cuota mensual debemos ingresar para ahorrar una cantidad de dinero en X años.

Vamos a calcular cómo podemos ahorrar $ 30,000 en 5 años, con un interés del 6%.

15 Sitúate en la celda C1 y escribe Ahorro

16 Sitúate en la celda C2 y escribe Tasa Anual

17 Sitúate en la celda C3 y escribe Años

18 Sitúate en la celda C4 y escribe Ingresos Mensuales

19 Sitúate en la celda D1 y escribe $ 30,000

20 Sitúate en la celda D2 y escribe 6%

21 Sitúate en la celda D3 y escribe 5

22 Sitúate en la celda D4 y escribe PAGO(D2/12,D3*12,0,D1) Como resultado debemos obtener en la celda D4 la cantidad de -$429.98

Vamos a calcular ahora los intereses pagados en un período de tiempo por un préstamo, por ejemplo los intereses del primer ejemplo.

Para realizar este ejercicio utilizaremos la función PAGOINT

Esta función tiene la siguiente sintaxis PAGOINT(tasa;periodo;nper;va;vf;tipo)


período = es el período para el que se desea calcular el interés y debe estar entre 1 y el parámetro nper



vf = es el valor futuro o un saldo en efectivo que se desea lograr trás el último pago. Si vf se omite, se asume que vale 0, es decir, trás el último pago no queda ningún saldo pendiente.

44




23 Sitúate en la celda E1 y escribe Préstamo

24 Sitúate en la celda E2 y escribe Tasa Anual

25 Sitúate en la celda E3 y escribe Interés en la Cuota Nº

26 Sitúate en la celda E4 y escribe Cantidad de Cuotas

27 Sitúate en la celda E5 y escribe Interés

28 Sitúate en la celda F1 y escribe $ 100.000

29 Sitúate en la celda F2 y escribe 6%

30 Sitúate en la celda F3 y escribe 1

31 Sitúate en la celda F4 y escribe 360

32 Sitúate en la celda F5 y escribe PAGO(F2/12,F3,F4)Esta función nos debe devolver -$361.80 que es el interés pagado en la primera cuota del préstamo. Cambiando el valor en F3 podrás ver el interés pagado en cada caso.

Vamos a calcular ahora las cuotas amortizadas para un préstamo, seguimos basándonos en el primer ejercicio.

Para realizar este ejercicio utilizaremos la función PAGOPRIN

Esta función tiene la siguiente sintaxis PAGOPRIN(tasa;periodo;nper;va;vf;tipo)


período = es el período para el que se desea calcular la amortización y debe estar entre 1 y el parámetro nper



vf = es el valor futuro o un saldo en efectivo que se desea lograr trás el último pago. Si vf se omite, se asume que vale 0, es decir, trás el último pago no queda ningún saldo pendiente.



45


33 Sitúate en la celda A8 y escribe Préstamo

34 Sitúate en la celda A9 y escribe Tasa Anual

35 Sitúate en la celda A10 y escribe Cálculo amortización en cuota nº

36 Sitúate en la celda A11 y escribe Cuotas Totales

37 Sitúate en la celda A12 y escribe Amortizado

38 Sitúate en la celda B8 y escribe $ 100,000

39 Sitúate en la celda B9 y escribe 6 %



42 Sitúate en la celda B12 y escribe PAGOPRIN(B9/12,B10,B11,B8) Como resultado debemos obtener en la celda B12 la cantidad de -$99.55 que si nos fijamos es el resultado de la diferencia de quitar los intereses a la cuota total del préstamo. Como vemos en la primera cuota de la amortización los intereses son más del 80% y la amortización propia del préstamo no llega al 20%.

43 Guarda el libro de trabajo en la carpeta Mis documentos del disco duro con el nombre de Funciones Financieras.


46

4.15 Otras funciones

Además de las funciones anteriormente mencionadas, existe un gran abanico de funciones de diferentes categorías que nos pueden ser de gran utilidad.

En este capítulo veremos algunas de ellas clasificándolas por categorías.Función Descripción Funciones matemáticas y trigonométricas

ABS(número) Devuelve el valor absoluto de un número

ALEATORIO() Devuelve un número entre 0 y 1

COMBINAT(número;tamaño)Devuelve el número de

combinaciones para un número determinado de elementos

COS(número) Devuelve el coseno de un ángulo

ENTERO(número) Redondea un número hasta el entero inferior más próximo

EXP(número) Realiza el cálculo de elevar "e"

a la potencia de un número determinado

FACT(número) Devuelve el factorial de un número

NUMERO.ROMANO(número,forma) Devuelve el número pasado en

formato decimal a número Romano

PI() Devuelve el valor de la constante pi

POTENCIA(número;potencia) Realiza el cálculo de elevar un número a la potencia indicada

PRODUCTO(número1;número2;...)Devuelve el resultado de

realizar el producto de todos los números pasados como

argumentos

RAIZ(número) Devuelve la raíz cuadrada del número indicado

RESIDUO(número;núm_divisor) Devuelve el resto de la divisiónFunciones estadísticas

MEDIA.ARMO(número1;número2;...)Devuelve la media armónica de

un conjunto de números positivos

MAX(número1;número2;...) Devuelve el valor máximo de la lista de valores

MIN(número1;número2;...) Devuelve el valor mínimo de la lista de valores

47

MEDIANA(número1;número2;...) Devuelve la mediana de la lista de valores

MODA(número1;número2;...) Devuelve el valor que más se repite en la lista de valores

PROMEDIO(número1;número2;...) Devuelve la media aritmética de la lista de valores

VAR(número1;número2;...) Devuelve la varianza de una lista de valores

K.ESIMO.MAYOR(matriz;k) Devuelve el valor k-ésimo mayor de un conjunto de datos

K.ESIMO.MENOR(matriz;k) Devuelve el valor k-ésimo menor de un conjunto de datos

Funciones lógicasFALSO() Devuelve el valor lógico Falso

VERDADERO Devuelve el valor lógico Verdadero

SI(prueba_logica;valor_si_verdadero;valor_si_falso)

Devuelve un valor u otro, según se cumpla o no una condición

NO(valor_lógico) Invierte el valor lógico proporcionado

Y(valor_logico1;valor_logico2;...) Comprueba si todos los valores son verdaderos

O(valor_logico1;valor_logico2;...)Comprueba si algún valor lógico

es verdadero y devuelve VERDADERO

Funciones de información

ESBLANCO(valor) Comprueba si se refiere a una celda vacía

ESERR(valor) Comprueba si un valor es un error

ESLOGICO(valor) Comprueba si un valor es lógico

ESNOTEXTO(valor) Comprueba si un valor no es de tipo texto

ESTEXTO(valor) Comprueba si un valor es de tipo texto

ESNUMERO(valor) Comprueba si un valor es de tipo numérico

TIPO(valor) Devuelve un número que

representa el tipo de datos del valor

4.16 Practicar el uso de otras funciones


1 Si no tienes abierto Excel2007, ábrelo para realizar el ejercicio. 48

2 Escribe en la Columna B unos 5 números enteros (mejor para el ejercicio si son de un solo dígito). Cada uno en una celda, pero ten en cuenta de ponerlos siempre en la columna B.

Vamos a multiplicar todos los valores de la columna B,

3 Sitúate en la celda C6 y pulsa sobre , selecciona la categoría de Matemáticas y trigonometría y elige la función PRODUCTO(). Pulsa Aceptar.

4 Selecciona la columna B como parámetro. Pulsa Aceptar.

Ya tenemos el resultado del producto en la celda C6.

Ahora vamos a pasar el resultado a números romanos. (Por esto mejor números de un dígito).

5 Sitúate en la celda D6 y pulsa sobre , selecciona la categoría de Matemáticas y trigonometría, selecciona la función NUMERO.ROMANO.

6 Selecciona como parámetro la celda donde tenemos el resultado del producto, la celda C6 y pulsa Aceptar. Debe de aparecer el resultado en números romanos.

Trabajemos un poco de estadística.

7 Añade 5 números más en la columna B y modifica los que existen por números más grandes, de más de un dígito.

8 Sitúate en la celda B12 y pulsa sobre , selecciona la función PROMEDIO de la categoría Estadísticas.

9 Selecciona como parámetro las 10 celdas de la columna B. Y pulsa sobre Aceptar.

Ahora vamos a ver la MEDIANA.

10 Sitúate en la celda B13 y pulsa sobre , selecciona la función MEDIANA de la categoría Estadísticas.


Y vamos a ver la MODA.

12 Sitúate en la celda B11 y pulsa sobre , selecciona la función MODA de la categoría Estadísticas.


14 Guarda el libro de trabajo en la carpeta Mis documentos del disco duro con el nombre de Otras Funciones de Excel.

15 Cierra el libro de trabajo.49

4.17 Manejo de la función SI

Devuelve un valor si la condición especificada es VERDADERO y otro valor si dicho

argumento es FALSO.

Utilice SI para realizar pruebas condicionales en valores y fórmulas.

Sintaxis Para hojas de cálculo y de macros

SI(prueba_lógica;valor_si_verdadero;valor_si_falso)

Prueba_lógica es cualquier valor o expresión que pueda evaluarse como VERDADERO o

FALSO.

Valor_si_verdadero es el valor que se devolverá si prueba_lógica es VERDADERO. Si prueba_lógica es VERDADERO y se omite el argumento valor_si_verdadero, la función devuelve VERDADERO. Valor_si_verdadero puede ser otra fórmula.

Valor_si_falso es el valor que se devolverá si prueba_lógica es FALSO. Si prueba_lógica es FALSO y se omite el argumento valor_si_falso, la función devuelve FALSO. Valor_si_falso puede ser otra fórmula.

Es posible anidar hasta siete funciones SI como argumentos valor_si_verdadero y valor_si_falso para construir pruebas más elaboradas.

Cuando los argumentos valor_si_verdadero y valor_si_falso se evalúan, la función SI 50

devuelve el valor devuelto por la ejecución de las instrucciones.

Si uno de los argumentos de la función SI es una matriz, cada elemento de la matriz se evaluará cuando se ejecute la instrucción SI. Cuando alguno de los argumentos valor_si_verdadero o valor_si_falso es una función que ejecuta una acción, se ejecutan todas las acciones.

Ejemplos

Si el valor en la celda A:10 es 100, prueba_lógica será VERDADERO y se calculará el valor total del rango B5:B15. De lo contrario, prueba_lógica será FALSO y se devolverá una cadena de texto vacía ("") que borrará el contenido de la celda que contenga la función SI.

SI(A10=100;SUMA(B5:B15);"")

En la gráfica se muestra un ejemplo clásico de uso del SI , en el se compara si la cantidad sobrepasa o es igual a 5 se cobra a 250 la unidad en caso contrario la unidad vale 300.

4.18 Manejo de la función SI anidada

Es posible anidar hasta siete funciones SI como argumentos valor_si_verdadero y valor_si_falso para construir pruebas más elaboradas.

Cuando los argumentos valor_si_verdadero y valor_si_falso se evalúan, la función SI devuelve el valor devuelto por la ejecución de las instrucciones.

Si uno de los argumentos de la función SI es una matriz, cada elemento de la matriz se evaluará cuando se ejecute la instrucción SI.

Supongamos que desea calificar con letras los números de referencia con el nombre 51

Promedio.

Si Promedio es La función devuelve:

Mayor que 89 = ADe 80 a 89 = BDe 70 a 79 = CDe 60 a 69 = DMenor que 60 = F

Se podría utilizar la siguiente función anidada SI:

=SI(A1>89,"A",SI(A1>79,"B",SI(A1>69,"C",SI(A1>59,"D","F"))))

En el ejemplo anterior, la segunda instrucción SI representa también el argumento valor_si_falso de la primera instrucción SI. De manera similar, la tercera instrucción SI es el argumento valor_si_falso de la segunda instrucción SI. Por ejemplo, si el primer argumento prueba_lógica (A1>89) es VERDADERO, se devuelve el valor "A". Si el primer argumento prueba_lógica es FALSO, se evalúa la segunda instrucción SI y así sucesivamente.

4.19 Manejo de la función como Y,O

Y Devuelve VERDADERO si todos los argumentos son VERDADERO; devuelve FALSO si uno o más argumentos son FALSO.

Sintaxis Y(valor_lógico1;valor_lógico2; ...)

Valor_lógico1;valor_lógico2; ... son de 1 a 30 condiciones que desea comprobar y que pueden ser VERDADERO o FALSO.

Los argumentos deben evaluarse como valores lógicos (VERDADERO O FALSO), o los argumentos deben ser matrices o referencias que contengan valores lógicos.

Si un argumento matricial o de referencia contiene texto o celdas vacías, esos valores se pasan por alto.

Si el rango especificado no contiene valores lógicos, la función Y devuelve el valor de error #¡VALOR!

52

Ejemplos

Y(VERDADERO; VERDADERO) es igual a VERDADERO

Y(VERDADERO; FALSO) es igual a FALSO

Y(2+2=4; 2+3=5) es igual a VERDADERO

Si B1:B3 contiene los valores VERDADERO, FALSO y VERDADERO, entonces:

Y(B1:B3) es igual a FALSO

Si B4 contiene un número entre 1 y 100, entonces:

Y(1<B4; B4<100) es igual a VERDADERO

Supongamos que desea mostrar la celda B4 sólo si contiene un número entre 1 y 100, y que desea mostrar un mensaje si no lo contiene. Si B4 contiene 104, entonces:

SI(Y(1<B4; B4<100); B4; "El valor queda fuera del rango") es igual a "El valor queda fuera del rango."

Si B4 contiene 50, entonces:

SI(Y(1<B4; B4<100); B4; "El valor queda fuera del rango") es igual a 50

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

Valor_lógico1; valor_lógico2; ... son entre 1 y 30 condiciones que se desean comprobar y que pueden ser VERDADERO o FALSO.

Los argumentos deben ser valores lógicos como VERDADERO O FALSO, o matrices o referencias que contengan valores lógicos. Si un argumento matricial o de referencia contiene texto o celdas vacías, dichos valores se pasarán por alto. Si el rango especificado no contiene valores lógicos, O devolverá el valor de error #¡VALOR!

O(VERDADERO) es igual a VERDADERO

O(1+1=1;2+2=5) es igual a FALSO

Si el rango A1:A3 contiene los valores VERDADERO, FALSO y VERDADERO, entonces:

O(A1:A3) es igual a VERDADERO

53

4.20 Referencias

Cuando trabajamos en Excel y más concretamente cuando hacemos usos de fórmulas y funciones casi es seguro que pongamos referencias a celdas o conjunto de celdas que no son propiamente la misma celda donde tenemos la formula.

Las referencias son enlaces a un lugar, es decir, cuando en una formula escribimos =SUMA(A1;B1) nos estamos refiriendo a que sume el contenido de A1 y el contenido de B1.

Existen 3 tipos de referencias:

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

Supongamos el ejemplo:

A B 1 15 202 =A1+2 303

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

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


A B 1 15 202 =$A$1+2 303

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

54

Referencia Mixta: Podemos hacer una combinación de ambas referencias, podemos hacer que las filas sean relativas y las columnas absolutas o viceversa.


A B 1 15 20 2 =$A1+2 303

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


A B 1 15 20 2 =A$1+2 303

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

4.21 Como cambiar el tipo de referencia

Una opción para cambiar el tipo de referencia una vez sabemos distinguir entre los diferentes tipos de referencias que existen y la que más nos interesa en cada momento es hacerlo a mano.

Las referencias relativas se escriben tal cual vemos la intersección de la celda con la columna y la fila (A2, B3, D1...).

Para que la referencia sea absoluta, es decir que sea fija, debemos anteponer a la columna y a la fila el signo $ ($A$2, $B$3, $D$1...).

Para las referencias mixtas como hemos dicho puede ser una mezcla entre relativa y absoluta por tanto pueden ser de este tipo ($A2, B$3, $D1...).

Otra opción, en lugar de escribirlo a mano es hacerlo cuando estemos editando la formula, en el momento en el que se incluyan las celdas referenciadas podemos pulsar sobre la

55

tecla F4 y vemos que va cambiando a los posibles tipos de referencias que podemos hacer con la celda.

4.22 Referencias a otras hojas o libros

Otra funcionalidad muy interesante de las referencias es la posibilidad de escribir referencias a celdas que se encuentran en otras hojas o incluso en otros libros.

Referencia a otras hojas Para hacer referencia a celdas de otras hojas debemos indicar el nombre de la hoja seguido del signo de exclamación y el nombre de la celda.

Por ejemplo: Hoja2!A2 esta referencia está diciendo que coja la celda A2 de la hoja Hoja2. Si la hoja tuviera un nombre personalizado con espacios incluidos, la referencia sería de este modo 'Nombre de la hoja externa'!A2, habría que encerrar el nombre de la hoja entre comillas simples ' '.

Referencia a otros libros Para hacer referencia a celdas de otros libros debemos indicar el nombre del libro entre corchetes y el resto como acabamos de ver.

Por ejemplo: '[presupuesto 2007]Hoja1'!B2 esta referencia indica que la celda se encuentra en el libro "Presupuesto 2007", en la Hoja1 y en la celda B2.

Muy importante: Fíjense bien que al escribir una cadena de caracteres que incluya espacios debemos ponerlo siempre entre comillas simples ' '.

56

Conclusión:

Ahora que has aprendido el uso de las formulas y funciones en Excel se ampliará tu panorama para poder resolver cualquier reto que se te presente en la hoja de cálculo.

OBJETIVO:El participante será capaz de elaborar gráficas utilizando el asistente e identificará los diversos tipos que existen.

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

Cuando se crea un gráfico en Excel, podemos optar por crearlo:

- Como gráfico incrustado: Insertar el gráfico en una hoja normal como cualquier otro objeto.

- Como hoja de gráfico: Crear el gráfico en una hoja exclusiva para el gráfico, en las hojas de gráfico no existen celdas ni ningún otro tipo de objeto.

Veamos cómo crear de un gráfico.

5.1 Crear gráficos

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

Es recomendable que tengas seleccionado el rango de celdas que quieres que participen en el gráfico, de esta forma, Excel podrá generarlo automáticamente. En caso contrario, el gráfico se mostrará en blanco o no se creará debido a un tipo de error en los datos que solicita.

57

5 GRÁFICOS

Como puedes ver existen diversos tipos de gráficos a nuestra disposición. Podemos seleccionar un gráfico a insertar haciendo clic en el tipo que nos interese para que se despliegue el listado de los que se encuentran disponibles.

En cada uno de los tipos generales de gráficos podrás encontrar un enlace en la parte inferior del listado que muestra Todos los tipos de gráfico...

Hacer clic en esa opción equivaldría a desplegar el cuadro de diálogo de Insertar gráfico que se muestra al hacer clic en la flecha de la parte inferior derecha de la sección Gráficos.

Aquí puedes ver listados todos los gráficos disponibles, selecciona uno y pulsa Aceptar para empezar a crearlo.

Si seleccionaste un rango de celdas verás tu nuevo gráfico inmediatamente y lo insertará en la hoja de cálculo con las características predeterminadas del gráfico escogido. Si has decidido probar suerte y no tenías celdas seleccionadas, deberás seguir leyendo los siguientes apartados.

5.2 Añadir una serie de datos

Este paso es el más importante de todos ya que en él definiremos qué datos queremos que aparezcan en el gráfico. Una vez tengamos un gráfico sobre la hoja de cálculo, aparecerán nuevas fichas para mostrarnos nuevas opciones.

58

Si observamos la ficha Diseño encontraremos dos opciones muy útiles:

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

Observa detenidamente el contenido de esta ventana. Como ya hemos dicho es la más importante porque se encargará de generar el gráfico.

Así pues tenemos un campo llamado Rango de datos del gráfico donde podremos seleccionar el rango de celdas que se tomarán en cuenta para crearlo. En el caso de la imagen, las celdas que se tomaron eran 5 y tenían los valores 445, 453, 545, 453 y 345.

59

Pulsa el botón y selecciona las celdas, automáticamente se rellenará el campo de texto con el rango correcto.

Una vez hayamos acotado los datos que utilizaremos, Excel asociará unos al eje horizontal (categorías) y otros al eje vertical (series).

Ten en cuenta que hay gráficos que necesitan más de dos series para poder crearse (por ejemplo los gráficos de superficie), y otros en cambio, (como el que ves en la imagen) se bastan con uno solo.

Utiliza el botón Editar de las series para modificar el literal que se muestra en la leyenda del gráfico.

Del mismo modo también podrás modificar el rango de celdas que se incluirán tanto en las series como en las categorías.

Haciendo clic en el botón Cambiar fila/columna podremos permutar los datos de las series y pasarlas a las categorías y viceversa. Este botón actúa del mismo modo que el que podemos encontrar en la banda de opciones Cambiar entre filas y columnas (ficha Diseño).

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

5.3 Modificar las características del gráfico

En la ficha Presentación podrás encontrar todas las opciones relativas al aspecto del gráfico.

Por ejemplo, podrás decidir que ejes mostrar o si quieres incluir una cuadrícula de fondo para poder leer mejor los resultados. Todo esto lo encontraras en la sección Ejes:

60

Utiliza las opciones del grupo Etiquetas para establecer qué literales de texto se mostrarán en el gráfico:

De todas formas, recuerda que puedes seleccionar las etiquetas dentro del gráfico y arrastrarlas para colocarlas en la posición deseada.

Desde esta sección también podrás configurar la Leyenda del gráfico.

Finalmente destacaremos las opciones de la sección Fondo que te permitirán modificar el modo en el que se integrará el gráfico en el cuadro de cálculo.

La primera opción Área de trazado, sólo estará disponible para los gráficos bidimensionales (como el de la imagen de ejemplo anterior).

Cuadro Gráfico, Plano interior del gráfico y Giro 3D modifican el aspecto de los gráficos tridimensionales disponibles:

Excel 2007 ha sido diseñado para que todas sus opciones sean sencillas e intuitivas, así que después de un par de pruebas con cada una de estas opciones entenderás perfectamente sus comportamientos y resultados.

Para terminar de configurar tu gráfico puedes ir a la ficha Formato, donde encontrarás el grupo Estilos de forma (que utilizaremos también más adelante para enriquecer la visualización de los objetos que insertemos).

61

Estas opciones te permitirán aplicar diversos estilos sobre tus gráficos.

Para ello, simplemente selecciona el área completa del gráfico o de uno de sus componentes (áreas, barras, leyenda...) y luego haz clic en el estilo que más se ajuste a lo que buscas.

Si no quieres utilizar uno de los preestablecidos puedes utilizar las listas Relleno de forma, Contorno de forma y Efectos de forma para personalizar aún más el estilo del gráfico.

5.4 Modificar el tamaño de un gráfico

También puedes seleccionar un elemento del gráfico para modificarlo.

Cuando tienes un elemento seleccionado aparecen diferentes tipos de controles que explicaremos a continuación:

Los controles cuadrados establecen el ancho y largo del objeto, haz clic sobre ellos y arrástralos para modificar sus dimensiones.

Haciendo clic y arrastrando los controles circulares podrás modificar su tamaño manteniendo el alto y ancho que hayas establecido, de esta forma podrás escalar el objeto y hacerlo más grande o pequeño.

Coloca el cursor sobre cualquier objeto seleccionado, cuando tome esta forma podrás hacer clic y arrastrarlo a la posición deseada.

5.5 Modificar la posición de un gráfico

Excel te permite decidir la posición del gráfico en el documento.

Además de poder establecer su tamaño y moverlo por la hoja de cálculo también podrás establecer su ubicación.

62

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

Se abrirá el siguiente cuadro de diálogo:

La primera opción Hoja nueva te permite establecer el gráfico como una hoja nueva. Tendrás la ventaja de que no molestará en la hoja de cálculo, pero no podrás contrastar los datos numéricos si la mantienes en una hoja aparte. Depende del formato que quieras utilizar.

Escribe un nombre en la caja de texto y pulsa Aceptar.

Utilizando la segunda opción, Objeto en, podremos mover el gráfico a una hoja ya existente. Si utilizas este método, el gráfico quedará flotante en la hoja y podrás situarlo en la posición y con el tamaño que tú elijas.

5.6 Crear tipos personalizados de gráfico

Excel nos ofrece distintos tipos de gráficos, con unas propiedades predefinidas y que nos permiten crear un gráfico en cuestión de segundos, seleccionando los datos a incluir en el gráfico y seleccionando el tipo de gráfico, sin necesidad de definir todas sus propiedades.

Excel nos ofrece por defecto sus propios tipos de gráfico pero nosotros podemos crear un gráfico con las propiedades que nos convengan y luego asignar sus propiedades a un tipo de gráfico personalizado y así poder utilizarlo como tipo de gráfico para crear nuevos gráficos con esas mismas características creando una plantilla.

63

Una vez hemos terminado de crear el gráfico, para guardar el estilo personalizado de nuestro gráfico debemos seguir los siguientes pasos:

Seleccionar la ficha Diseño en la banda de opciones.

Hacer clic en la opción Guardar como plantilla.

Se abrirá un cuadro de diálogo desde donde podrás escribir un nombre para tu tipo de gráfico, pulsa el botón Guardar y se almacenará.

Más tarde cuando quieras crear un gráfico utilizando esta plantilla, sólo tendrás que seleccionar la categoría Plantillas del listado de todos los gráficos disponibles:

5.7 Practicar la realización de gráficos a partir de unos datos


1 Si no tienes abierto Excel, ábrelo para realizar el ejercicio.

2 Crea un libro de trabajo con el nombre Robos.

64

Vamos a realizar un gráfico de líneas para representar la evolución del número de robos en las municipios de Colima y Villa de Álvarez.

3. Ingresa la siguiente tabla de datos:

3 Selecciona los datos a representar, es decir A2:C5.

4 Ve a la ficha Insertar.

5 Selecciona uno de los gráficos incluidos en el menú Línea.

6 Haz clic sobre el tipo de gráfico a realizar, en nuestro caso el de líneas.

Observa como aparece cómo aparece nuestro gráfico.

2007 2008 20090

100

200

300

400

500

600

ColimaVilla de Alvarez

7 Vamos a cambiar de gráfico, haz clic en el botón Cambiar tipo de gráfico de la ficha Diseño.

8 Se abrirá un cuadro de diálogo, selecciona la categoría Columna y escoge el diseño de barras sencillo.

9 Haz clic sobre el botón Aceptar para ejecutar los cambios.

65

Vamos a cambiar los datos que queremos que se muestren, añadiremos también los robos en Manzanillo con los datos 2007, 2008 y 2009 con 370, 393 y 412 respectivamente.

10 Haz clic en el botón Seleccionar datos de la ficha Diseño.

11 Se abrirá un cuadro de diálogo, no escribiremos nada, simplemente seleccionaremos el rango A2:D5 en la hoja de cálculo.

Si el gráfico tapa los datos, deberás moverlo primero.

12 Pulsamos el botón Aceptar para ver los cambios.

Para cambiar nombre a la serie de datos:

Escribe en las celdas B1 Tecomán, C1 Comala y D1 Coquimatlán

13 Vuelve a hacer clic en el botón Seleccionar datos.

14 En el cuadro de diálogo selecciona la primer serie a cambiar (Colima) y pulsa el botón Editar.

15 Coloca el cursor en el cuadro de texto Nombre de la serie y haz clic en la celda B1 de la hoja de cálculo para decirle a Excel que el nombre de esta serie se encuentra en esa celda.

16 Pulsa el botón Aceptar.

17 Repite los pasos 14 a 16 para las dos otras series con las celdas C1 y D1.

18 Pulsa el botón Aceptar y observa los cambios.

Hemos puesto los nombres de los pueblos en la leyenda para saber a cual corresponde cada barra. Vamos a hacer lo mismo para los literales del eje horizontal.

19 Volvemos a hacer clic en el botón Seleccionar datos.

20 Haz clic en el botón Editar de la columna Etiquetas del eje horizontal (categoría).

21 En este caso, como no se trata de series no tendremos que indicarle una a una cual es su nomenclatura, bastará con darle el rango de los literales para los datos que hemos seleccionado.

Selecciona el rango de celdas A3:A5 y pulsa Aceptar.

66

22 Pulsa el botón Aceptar y observa los cambios.

Ahora, desde las fichas Diseño, Presentación y Formato podrás mejorar su visualización cambiando los estilos del gráfico.

Deberá quedarte algo así:

2007 2008 20090

100

200

300

400

500

600

TecománComalaCoquimatlán

67

Conclusión:

Esta valiosa herramienta mejorada le da un aspecto más profesional a nuestros datos para interpretarlos de una mejor manera

OBJETIVO:El participante aplicará los conocimientos para crear esquemas e identificar la estructura y jerarquía de los datos.

Un esquema podríamos definirlo como un resumen preciso que refleja los conceptos más importantes o de mayor trascendencia del documento esquematizado.

Así pues, un esquema puede ser perfectamente un índice de un libro, donde vemos todos los puntos tratados en el libro, también podemos ver como ejemplo de esquema el índice de este curso, el cual contiene los puntos más importantes que se tratan en él y además está estructurado por niveles de profundización sobre un tema en concreto, vamos desplegando el esquema de los puntos contenidos en el tema.

Antes de ponernos a crear un esquema debemos tener en cuenta algunos aspectos.

- Debemos asegurarnos de que los datos sean apropiados para crear un esquema. Los datos apropiados para crear un esquema debe tener una jerarquía o disponer de una estructura por niveles, por ejemplo si tenemos datos sobre las precipitaciones ocurridas a lo largo del año en toda España con las precipitaciones mensuales de todas las provincias, estos datos son buenos candidatos a formar un esquema. Pero si únicamente tenemos datos sobre los gastos efectuados en una compra, con una estructura compuesta por Producto---Precio, no disponemos de niveles suficientes para hacer un esquema.

- En una hoja solo podemos incluir un esquema, para tener más de un esquema sobre los mismos datos, debemos copiar los datos a otra hoja.

- Para crear esquemas automáticamente debemos preparar lo hoja con un formato adecuado como veremos más adelante.

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

6.1 Creación automática de esquemas

La mejor opción para crear esquemas es que lo haga Excel automáticamente, puesto que tarda mucho menos tiempo que haciéndolo manualmente.

Existen unos requisitos previos para que Excel 2007 pueda crear automáticamente el esquema:

- Las filas sumario deben estar por encima o por debajo de los datos, nunca entremezclados.

68

6 ESQUEMAS Y VISTAS

- Las columnas sumario deben estar a la derecha o a la izquierda de los datos, nunca entremezclados.

Si la disposición de los datos no se corresponde con estas características nos veremos obligados a definir el esquema manualmente.

En la imagen podemos ver el ejemplo de datos bien estructurados:

Podemos ver que existen subtotales en las celdas B6, C6, D6, B11, C11, D11, B20, C20, D20 y la columna E está llena de subtotales correspondientes a las filas donde se encuentran. En las celdas B21, C21, D21 y E21 son los totales de los subtotales.

En este ejemplo podemos hacer un esquema tanto de filas como de columnas, puesto que se ha organizado los subtotales de cada comunidad autónoma (filas) y se ha calculado los subtotales de cada trimestre (columnas).

Por tanto, como ya tenemos los datos vamos a realizar el esquema.

Para ello nos situamos sobre una celda cualquiera y seleccionamos la opción Autoesquema del menú Agrupar que se encuentra en la ficha Datos.

69

Automáticamente, Excel nos genera los niveles del esquema como podemos ver a continuación:

En la imagen anterior podemos ver que ahora aparecen unas líneas en la zona izquierda de las celdas y otra línea sobre las columnas de la tabla.

Además, vemos en la esquina superior izquierda unos números que nos indican cuántos niveles tiene el esquema.

Por columnas podemos ver que existen dos niveles:

70

- La tabla desplegada por completo

- y la tabla con los datos trimestrales.

Por filas tenemos tres niveles:

- La tabla desplegada completamente

- Por autonomías

- y solo por España.

Para comprimir y expander el esquema sólo tenemos que hacer clic en los símbolos - y + de cada nivel.

Por ejemplo, en la tabla del ejemplo, si hacemos clic sobre el - encima del primer trimestre, comprimiremos ese trimestre, si hacemos lo mismo sobre los niveles de cada autonomía, el esquema se nos quedaría como podemos ver en la imagen.

6.2 Practicar cómo crear un esquema automáticamente



2 Empieza un nuevo libro de trabajo.

Desarrolla una hoja de trabajo lo más parecida a la mostrada en la imagen, el formato no es necesario, lo que nos interesa es el contenido de los datos. Incluso si lo deseas puedes ampliar los datos.

71

Los valores resaltados en negrita son fórmulas de sumas, por ejemplo la celda B6 es =SUMA(B3:B5), así sucesivamente con el resto de las celdas sumario.

Las celdas sumarios de la columna E son sumatorias de las columnas B,C y D de su correspondiente fila, por ejemplo la celda E3 es =SUMA(B3:D3).

Las únicas que cambian un poco son las de totales de España-Mes, por ejemplo la celda B21 que se corresponde con las precipitaciones en España en el mes de enero es =Suma(B6;B11;B20) que son los subtotales correspondientes a las comunidades autónomas que disponemos.

3 Selecciona la ficha Datos.

4 Elige la opción Agrupar.

Se abrirá otro submenú.

5 Elige la opción Autoesquema.

6 Pliega y despliega los niveles para ver cómo funcionan.

72

7 Por último cierra el libro de trabajo y guarda el libro en la carpeta Mis documentos del disco duro, con el nombre de Ejemplo_esquemas.

6.3 Creación manual de esquemas

La segunda opción es la de crear el esquema manualmente.

Para crear un esquema manualmente debemos crear grupos de filas o de columnas dependiendo del tipo de esquema que queramos hacer.

Para crear un grupo debemos seleccionar las filas (seleccionando los números de las filas) o columnas (seleccionando las letras de las columnas) de las cuales vayamos a crear un grupo, pero no debemos incluir las filas o columnas que contengan fórmulas de sumario.

Una vez seleccionadas las filas o columnas vamos al menú Agrupar de la ficha Datos y seleccionamos Agrupar.

Para trabajar un poco más rápido podemos utilizar las teclas para agrupar y desagrupar.

Para agrupar, una vez tengamos el grupo seleccionado presionamos Alt+Shift+Flecha derecha.

Para desagrupar, una vez tengamos el grupo seleccionado presionamos Alt+Shift+Flecha izquierda.

6.4 Borrar y ocultar un esquema

Borrar un esquema Para borrar un esquema debemos acceder a la opción Desagrupar y seleccionar la opción Borrar esquema.

Antes de eliminar un esquema, debemos estar seguros de que es lo que realmente deseamos, pues una vez dada la orden, Excel no nos ofrece la posibilidad de deshacer. Así pues si no estamos seguros de querer eliminar el esquema es mejor ocultarlo.

Ocultar un esquema Al ocultarlo lo que hacemos es ocultar las marcas que nos ayudan a expandir/contraer un esquema, pero el esquema sigue estando activo, para ocultar un esquema basta con presionar las teclas Ctrl+8 y automáticamente desaparecen las marcas del esquema, para volverlas a mostrar volvemos a pulsar la combinación de teclas Ctrl+8.

73

6.5 Ver una hoja en varias ventanas

Suele suceder de forma muy común que al estar realizando hojas de cálculo vayamos ampliando cada vez más el campo de visión de la pantalla, llegando a ocupar más de una página por hoja, y cuando se trata de estar cotejando datos resulta bastante incómodo tener que desplazarse cada vez de arriba hacia abajo o de un lado al otro.

Vamos a ver las distintas opciones que Excel 2007 nos ofrece para minimizar el problema y así trabajar de una manera más cómoda con los datos introducidos.

Podemos utilizar la opción de ver la misma hoja en varias ventanas, o utilizar la opción de ver la misma hoja en varios paneles.

Para ver la misma hoja en varias ventanas, debemos acceder a la ficha Vista y pulsar el botón Nueva ventana, si la ventana que teníamos estaba maximizada no nos daremos cuenta de que haya ocurrido algún cambio en la ventana, pero si nos fijamos en la barra de título podemos ver que ahora el nombre del documento además tiene añadido al final ":2" indicando que es la segunda ventana del mismo documento.

Para ver las dos ventanas al mismo tiempo hacemos clic en Organizar todo y seleccionamos Vertical, Horizontal, Mosaico o Cascada, dependiendo de cómo estén situados los datos en la hoja.

Ahora podemos desplazarnos sobre una ventana independientemente de la otra.

74

6.6 Dividir una hoja en paneles

Podemos utilizar también la opción de dividir la hoja por paneles.

Si no nos gusta lo de tener varias ventanas abiertas en la pantalla, podemos utilizar la opción de dividir la hoja en 2 ó 4 paneles.

Para dividir la hoja en paneles podemos hacer clic en el botón Dividir en la ficha Vista y automáticamente nos aparecen dos barras, una vertical y otra horizontal las cuales podemos desplazar para ajustar el tamaño de las porciones de ventana a nuestro gusto.

Otra opción para definir los paneles y dividir la pantalla a nuestro gusto es utilizando las barras de división:

Tenemos dos disponibles:

- La barra de división horizontal que aparece en la barra de desplazamiento

vertical arriba del todo. Al situar el puntero del ratón sobre la línea gris que está encima de la flecha de desplazamiente el puntero del ratón toma el aspecto , indicando el desplazamiento hacia arriba y hacia abajo.

75

- La barra de división vertical que aparece en la barra de desplazamiento

horizontal a la derecha del todo. Al situar el puntero del ratón sobre la línea gris vertical que está a la derecha de la flecha de desplazamiento, el puntero del ratón adopta la forma , indicando el desplazamiento de la barra hacia la derecha y hacia la izquierda.

6.7 Inmovilizar paneles

Podemos utilizar la opción de inmovilizar los paneles.

Si lo que nos interesa es dejar inmóviles las cabeceras de los datos y así desplazarnos únicamente sobre los datos teniendo siempre disponible la vista de las cabeceras, podemos utilizar la opción de inmovilizar los paneles.

Para realizar esto, simplemente despliega el menú Inmovilizar paneles que se encuentra en la ficha Vista. Si te interesa mantener la primera fila (como cabecera) o la primera columna (para que ejerza la misma función) selecciona la opción correspondiente

En el caso de que lo que quisieses inmovilizar no se encontrase en esas posiciones selecciona el rango de celdas y pulsa la opción Inmovilizar paneles. La zona seleccionada podrá desplazarse, el resto permanecerá inmovilizado.

Para desactivar la inmovilización de los paneles vuelve a seleccionar esta opción y haz clic sobre Movilizar paneles.

76

Conclusión:

Otra herramienta sencilla pero de gran uso es el conocimiento de los esquemas y el uso de los paneles para aplicarse en actividades laborales

OBJETIVO:Que el participante será capaz de manejar herramientas para almacenar información utilizando creando tablas en Excel

Una tabla en Excel es un conjunto de datos organizados en filas o registros, en la que la primera fila contiene las cabeceras de las columnas (los nombres de los campos), y las demás filas contienen los datos almacenados. Es como una tabla de base de datos, de hecho también se denominan listas de base de datos. Cada fila es un registro de entrada, por tanto podremos componer como máximo una lista con 255 campos y 65535 registros.

Las tablas son muy útiles porque además de almacenar información, incluyen una serie de operaciones que permiten analizar y administrar esos datos de forma muy cómoda.

Entre las operaciones más interesantes que podemos realizar con las listas tenemos:

- Ordenar la los registros.

- Filtrar el contenido de la tabla por algún criterio.

- Utilizar fórmulas para la lista añadiendo algún tipo de filtrado.

- Crear un resumen de los datos.

- Aplicar formatos a todos los datos.

Las tablas de datos, ya se usaban en versiones anteriores de Excel, pero bajo el término Listas de datos. Incluso encontrarás, que en algunos cuadros de diálogo, se refiere a las tablas como listas.

A partir de ahora nos referiremos a este tipo de tablas.

7.1 Crear una tabla

Para crear una lista tenemos que seguir los siguientes pasos:

- Seleccionar el rango de celdas (con datos o vacías) que queremos incluir en la lista.

- Seleccionar del Tabla en la ficha Insertar.

77

7 TABLAS DE EXCEL

Aparecerá a continuación el cuadro de diálogo Crear tabla .

Si nos hemos saltado el paso de seleccionar previamente las celdas, lo podemos hacer ahora.

- Si en el rango seleccionado hemos incluido la fila de cabeceras (recomendado), activaremos la casilla de verificación La lista tiene encabezados.

- Al final hacer clic en Aceptar.

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

78

7.2 Modificar los datos de una tabla

Para modificar o introducir nuevos datos en la tabla podemos teclear directamente los nuevos valores sobre la ella, o bien podemos utilizar un formulario de datos. Esta segunda opción viene muy bien sobre todo si la lista es muy grande.

Veamos un ejemplo, tenemos la siguiente lista con información de nuestros amig@s.

Un formulario de datos es un cuadro de diálogo que permite al usuario escribir o mostrar con facilidad una fila entera de datos (un registro).

Para abrir el formulario de datos, tenemos que posicionarnos en la lista para que esté activa, y pulsar en el icono Formulario .

Como esta opción no está directamente disponible en la Cinta de opciones, podemos añadirla a la Barra de acceso rápido, de la forma que ya vimos. Pulsando el Botón Office > Opciones de Excel > Personalizar, y Agregar el icono Formulario..., en la sección de Comandos que no están en la cinta de opciones.

Al crear el formulario, disponemos de siguientes botones:

Nuevo: Sirve para introducir un nuevo registro.

Eliminar: Eliminar el registro que está activo.

Restaurar: Deshace los cambios efectuados.

Buscar anterior: Se desplaza al registro anterior.

Buscar siguiente: Se desplaza al siguiente registro.

Criterios: Sirve para aplicar un filtro de búsqueda.

Cerrar: Cierra el formulario.

80

Para cambiar los datos de un registro, primero nos posicionamos sobre el registro, luego rectificamos los datos que queramos (para desplazarnos por los campos podemos utilizar las teclas de tabulación), si nos hemos equivocado y no queremos guardar los cambios hacemos clic en el botón Restaurar, si queremos guardar los cambios pulsamos la tecla Intro.

Para crear un nuevo registro, hacemos clic en el botón Nuevo, Excel se posicionará en un registro vacío, sólo nos quedará rellenarlo y pulsar Enter o Restaurar para aceptar o cancelar respectivamente.

Después de aceptar Excel se posiciona en un nuevo registro en blanco por si queremos insertar varios registros, una vez agregados los registros, hacer clic en Cerrar.

Para buscar un registro y posicionarnos en él podemos utilizar los botones Buscar anterior y Buscar siguiente o ir directamente a un registro concreto introduciendo un criterio de búsqueda. Pulsamos en el botón Criterios con lo cual pasamos al formulario para introducir el criterio de búsqueda, es similar al formulario de datos pero encima de la columna de botones aparece la palabra Criterios.

Por ejemplo, si buscamos un registro con el valor Ana en el campo Nombre, escribimos Ana en Nombre y pulsamos el botón Buscar Siguiente, Excel vuelve al formulario de datos y nos posiciona en el registro de nombre Ana.

7.3 Modificar la estructura de la tabla

Pulsando en el icono Cambiar tamaño de la tabla, podemos seleccionar un nuevo rango de datos. Pero si la tabla contiene encabezados, estos deben permanecer en la misma posición, así que sólo podremos aumentar y disminuir filas.

Podemos modificar directamente el rango de filas y columnas, estirando o encogiendo la

tabla desde su esquina inferior derecha.

Cuando necesitemos añadir una fila al final de la tabla para continuar introduciendo datos, sólo tendremos que pulsar la tecla TAB desde la última celda y aparecerá una fila nueva.

Si necesitamos insertar filas y columnas entre las filas existentes de la tabla, nos posicionaremos en la primera celda que queremos desplazar y elegiremos la opción correspondiente desde el menú Insertar, en la ficha de Inicio o en el menú contextual de la tabla.

Para eliminar filas o columnas, deberemos posicionarnos sobre

81

una celda, y elegiremos Filas o Columnas de la tabla en el menú Eliminar, disponible en la ficha de Inicio y en el menú contextual de la tabla.

Seleccionando una celda, fila o columna, y pulsando la tecla SUPR, eliminamos los datos seleccionados, pero no la estructura de la tabla.

Para eliminar la tabla completa, seleccionamos toda la tabla y pulsamos SUPR. Si deseamos eliminar la estructura de la tabla, pero conservar los datos en la hoja, entonces pulsamos en la ficha de Diseño de la tabla.

7.4 Estilo de la tabla

Una forma fácil de dar una combinación de colores a la tabla que resulte elegante, es escogiendo uno de los estilos predefinidos, disponibles en la ficha Diseño de la tabla.

En Opciones de estilo de la tabla, podemos marcar o desmarcar otros aspectos, como que las columnas o filas aparezcan remarcadas con bandas, o se muestre un resaltado especial en la primera o última columna. Las bandas y resaltados dependerán del estilo de

la tabla.

Por lo demás, a cada celda se le podrán aplicar los colores de fuente y fondo, fondo condicional, etc. que a cualquier celda de la hoja de cálculo.

En esta tabla, se ha cambiado el estilo, y se han marcado las opciones Primera y Última columna.

7.5 Ordenar una tabla de datos

A la hora de ordenar una tabla, Excel puede hacerlo de forma simple, es decir, ordenar por un único campo u ordenar la lista por diferentes campos a la vez. Para hacer una ordenación simple, por ejemplo ordenar la lista anterior por el primer apellido, debemos posicionarnos en la columna del primer apellido, después podemos acceder a la ficha Datos y pulsar sobre Ordenar... y escoger el criterio de ordenación o bien pulsar sobre uno

82

de los botones de la sección Ordenar y filtrar para que la ordenación sea ascendente o descendente respectivamente. Estos botones también están disponibles al desplegar la lista que aparece pulsando la ficha junto al encabezado de la columna.

Para ordenar la lista por más de un criterio de ordenación, por ejemplo ordenar la lista por el primer apellido más la fecha de nacimiento, en la ficha Datos, pulsamos sobre Ordenar... nos aparece el cuadro de diálogo Ordenar donde podemos seleccionar los campos por los que queremos ordenar (pulsando Agregar Nivel para añadir un campo), si ordenamos según el valor de la celda, o

por su color o icono (en Ordenar Según), y el Criterio de ordenación, donde elegimos si el orden es alfabético (A a Z o Z a A) o sigue el orden de una Lista personalizada. Por ejemplo, si en la columna de la tabla se guardan los nombres de días de la semana o meses, la ordenación alfabética no sería correcta, y podemos escoger una lista donde se guarden los valores posibles, ordenados de la forma que creamos conveniente, y así el criterio de ordenación seguirá el mismo patrón.

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

83

Conclusión:

El uso y manejo de tablas en Excel nos ayudan a organizar las series de datos a fin de poder obtener diferentes resultados utilizando los filtros.

OBJETIVO:Que el participante demuestre el uso de una tabla dinámica así como su diseño y aplicando diversos tipos de filtros.

Una tabla dinámica consiste en el resumen de un conjunto de datos, atendiendo a varios criterios de agrupación, representado como una tabla de doble entrada que nos facilita la interpretación de dichos datos. Es dinámica porque nos permite ir obteniendo diferentes totales, filtrando datos, cambiando la presentación de los datos, visualizando o no los datos origen, etc..

En Access es lo más parecido a una consulta de referencias cruzadas, pero con más interactividad. Veamos cómo podemos crear una tabla dinámica a partir de unos datos que ya tenemos.

8.1 Creación de una tabla dinámica

Para crear una tabla dinámica, Excel nos proporciona las tablas y gráficos dinámicos.

Supongamos que tenemos una colección de datos de los artículos del almacén con el número de referencia y el mes de compra, además sabemos la cantidad comprada y el importe del mismo.

Vamos a crear una tabla dinámica a partir de estos datos para poder examinar mejor las ventas de cada artículo en cada mes.

Para ello vamos a la ficha Insertar y hacemos clic en Tabla dinámica (también podemos desplegar el menú haciendo clic en la flecha al pie del botón para crear un gráfico dinámico).

84

8 TABLAS DINÁMICAS

Aparece el cuadro de diálogo de creación de tablas dinámicas. Desde aquí podremos indicar el lugar donde se encuentran los datos que queremos analizar y el lugar donde queremos ubicarla.

En nuestro caso indicamos que vamos a seleccionar los datos de un rango de celdas y que queremos ubicarla en una hoja de cálculo nueva.

Podríamos crear una conexión con otra aplicación para obtener los datos desde otra fuente diferente a Excel.

En el caso de seleccionar la opción Selecciona una tabla o rango debemos seleccionar todas las celdas que vayan a participar, incluyendo las cabeceras.

Pulsamos Aceptar para seguir.

85

Se abrirá un nuevo panel en la derecha de la pantalla:

Desde este panel podemos personalizar la forma en que van a verse los datos en la tabla dinámica.

Con esta herramienta podríamos construir una tabla dinámica con la siguiente estructura:

- Una fila para cada una de las Referencias de la tabla.

- Una columna para cada uno de los Meses de la tabla.

- En el resto de la tabla incluiremos el total del Importe para cada Referencia en cada Mes.

86

Para ello simplemente tendremos que arrastrar los elementos que vemos listados a su lugar correspondiente al pie del panel.

En este ejemplo deberíamos arrastrar el campo REF a , el campo MES a y

finalmente el campo IMPORTE a la sección .

Tras realizar la tabla dinámica este sería el resultado obtenido.

Podemos ver que la estructura es la que hemos definido anteriormente, en el campo fila tenemos las referencias, en el campo columnas tenemos los meses y en el centro de la tabla las sumas de los importes.

Con esta estructura es mucho más fácil analizar los resultados.

Una vez creada la tabla dinámica nos aparece la ficha Opciones:

El panel lateral seguirá pudiéndose utilizar, así que en cualquier momento podremos quitar un campo de un zona arrastrándolo fuera.

Con esto vemos que en un segundo podemos variar la estructura de la tabla y obtener otros resultados sin casi esfuerzos.

87

Si arrastrásemos a la zona de datos los campos cantidad y total, obtendríamos la siguiente tabla, más compleja pero con más información:

Puede que no visualices la tabla de la misma forma, al añadir varios campos en la sección Valores el rótulo aparecerá en una las secciones de rótulos, si te aparece en Rótulos de columna despliega la lista asociada a él y selecciona la opción Mover a rótulos de fila.

8.2 Eliminar una tabla dinámica

88

Para eliminar una tabla dinámica simplemente debemos seleccionar la tabla en su totalidad y presionar la tecla Supr.

8.3 Aplicar filtros a una tabla dinámica

Otra característica útil de las tablas dinámicas es permitir filtrar los resultados y así visualizar únicamente los que nos interesen en un momento determinado. Esto se emplea sobre todo cuando el volumen de datos es importante.

Los campos principales en el panel y los rótulos en la tabla están acompañados, en su parte derecha, de una flecha indicando una lista desplegable.

Por ejemplo, si pulsamos sobre la flecha del rótulo Rótulos de columna nos aparece una lista como vemos en la imagen con los distintos meses disponibles en la tabla con una casilla de verificación en cada uno de ellos para indicar si los queremos ver o no, más una opción para marcar todas las opciones en este caso todos los meses.

Si dejamos marcados los meses Enero y Febrero, los otros meses desaparecerán de la tabla, pero no se pierden, en cualquier momento podemos visualizarlos volviendo a desplegar la lista y marcando la casilla (Seleccionar todo).

Para cerrar este cuadro debemos pulsar en Aceptar o sobre Cancelar para cerrar y dejarlo como estaba.

Aplicando el filtro a varios campos podemos formar condiciones de filtrado más complejas, por ejemplo podemos seleccionar ver los artículos con referencia 1236 de Abril.

8.4 Obtener promedios en una tabla dinámica

Por defecto, al crear una tabla dinámica, Excel nos genera unos totales con sumatorio, puede interesarnos modificar esas fórmulas por otras como pueden ser sacar el máximo o el mínimo, el promedio, etc.

89

Para hacer esto debemos situarnos en cualquier celda de la zona que queremos rectificar y hacer clic con el botón derecho del ratón, nos aparece un menú emergente con diferentes opciones, debemos escoger la opción Configuración de campo de valor... y nos aparece un cuadro de diálogo como el que vemos en la imagen.

En este cuadro de diálogo podemos escoger cómo queremos hacer el resumen, mediante Suma, Cuenta, Promedio, etc.

También podemos abrir el cuadro de diálogo con el botón de la ficha Opciones.

8.5 Gráficos con tablas dinámicas

Para crear una gráfica de nuestra tabla dinámica deberemos hacer clic en el botón Gráfico dinámico de la ficha Opciones.

Para cambiar el formato del gráfico a otro tipo de gráfico que nos agrade más o nos convenga más según los datos que tenemos. Al pulsar este botón se abrirá el cuadro de diálogo de Insertar gráfico, allí deberemos escoger el gráfico que más nos convenga.

8.6 Practicar cómo trabajar con tablas dinámicas

90


1 Si no tienes abierto Excel2007, ábrelo para realizar el ejercicio. 2 Crea una Tabla_dinamica con los siguientes datos.

3 Guarda el libro en tu carpeta Mis documentos.

Vamos a crear una tabla dinámica para interpretar mejor estos datos.

4 Selecciona el rango A1:G15.

5 Haz clic en el botón Tabla dinámica de la ficha Insertar.

6 La opción Seleccione una tabla o rango debería estar marcada y con el rango A1:G15 en la caja de texto.

7 Marca Nueva hoja de cálculo.

8 Pulsa en Aceptar.

9 Se abrirá el panel lateral de tablas dinámicas.

10 Arrastra el campo SEMANA a .

11 Arrastra el campo CLIENTE a .

91

12 Arrastra el campo TOTAL a .

13 Arrastra el campo ARTICULO a .

El campo ARTICULO se añadirá a los rótulos de columna, vamos a cambiar esto porque o queremos en los rótulos de fila.

14 En el área de Rótulos de columna despliega el campo Valores y selecciona la opción Mover a rótulos de fila.

15 Haz clic en el botón Encabezados de campo de la ficha Opciones para quietar los encabezados de la tabla dinámica.

Deberá quedarte algo así:

En estos momentos tenemos una tabla en la que se nos muestra por cada semana qué ha comprado cada cliente.

La fila Suma de TOTAL nos da lo que se ha gastado cada cliente en cada semana.

92

Sin embargo la fila Suma de ARTICULO nos muestra la suma del número de artículo para cada semana, esto no es lo que queremos. Nuestro objetivo es que esta última fila muestre el número de artículos que se han comprado, así podríamos saber para cada semana cuánto se ha gastado cada cliente y cuántos artículos ha comprado.

16 Haz clic derecho sobre cualquier celda de la fila Suma de ARTICULO y en el menú emergente selecciona Configuración de campo de valor.

17 En el cuadro de diálogo selecciona la función Cuenta y pulsa Aceptar.

Ya tenemos lo que queríamos. Ahora desde la ficha Diseño modifica el aspecto de la tabla, deberá quedarte algo como esto:

Nosotros simplemente hemos seleccionado la opción Columnas con bandas.

Ahora vamos a filtrar los resultados.

Queremos ver solamente los datos del cliente 14. 18 Haz clic sobre una celda de la tabla para que aparezca el panel lateral. 19 Haz clic sobre la flecha a la derecha del campo CLIENTE en la lista de campos. 20 Deselecciona todos los clientes menos el 14. 21 Pulsa Aceptar. 22 Cierra el libro de trabajo guardando los cambios realizados.

93

Conclusión:

Las tablas dinámicas son fundamentales por su facilidad de recalcular e interpretar rápidamente datos numéricos para llevar un control adecuado de la empresa.

CONCLUSIÓN GENERAL DEL EVENTO

Durante todo este curso has ejercitado numerosas técnicas básicas y avanzadas para trabajar con Microsoft Excel 2007. Al diseñar este curso, he hecho un esfuerzo para facilitarte el trabajo, proporcionándote herramientas de trabajo y la mayoría de las soluciones, además de distintas visiones de la integración de las Tecnologías de la Información y de la Comunicación para que te vayas poniendo al día.

Has aprendido Excel 2007, que era el objetivo principal de este curso, si es cierto que la creatividad es el resultado de encontrar relaciones entre experiencias que antes no las tenían, lo que te pido es que conjugues lo aprendido en este curso y tu conocimiento y experiencia de la materia en la que te desarrollas para facilitarte tus tareas cotidianas

Mide la utilidad en provecho propio: vas a dedicar unas horas a este trabajo, no las tires por la borda y piensa en qué situación de tus tareas cotidianas puedes usar la hoja de cálculo y diseña la práctica, la unidad didáctica, la evaluación, el cuestionario.

94

BIBLIOGRAFIA

FAMILIARIZARSE CON EXCEL 2007 - Microsoft Excel 2007

NELSON, S. (1993). EXCEL 4 PARA WINDOWS A SU ALCANCE. MADRID: MCGRAW-HILL. [QA76.76 .G72 N4418]

MATTEHEWS, M. (1993). EXCEL 3 PARA WINDOWS A SU ALCANCE. MADRID: MCGRAW-HILL. [HF5548.4 M37]

PIERDANT RODRIGUEZ, A. (1997). ESTADISTICA DESCRIPTIVA USANDO EXCEL. MEXICO: UAM-XOCHIMILCO. [HA29 P53]

PIERDANT RODRIGUEZ, A. (1997). MODELOS ADMINISTRATIVOS USANDO EXCEL. MEXICO: UAM-XOCHIMILCO. [HD30.25 P53]

DODGE, M. (1996). GUIA COMPLETA DE MICROSOFT EXCEL PARA WINDOWS 95. BUENOS AIRES, ARGENTINA: MCGRAW-HILL. [QA76.76 .G72 D6218]

HARVEY, G. (1995). EXCEL FOR WINDOWS 95 FOR DUMMIES. USA: IDG BOOKS WORLDWIDE. [HF5548.4 .M523 H37 1995]

WALKENBANCH, J. (1995). EXCEL FOR WINDOWS 95 FOR DUMMIES: QUICK REFERENCE. FOSTER, CITY: IDG BOOKS WORLDWIDE. [HF5548.4 .M523 W34]

BOTT, E. (1999). DESCUBRE MICROSOFT OFFICE 2000. MEXICO: PRENTICE HALL. [HF5548.4 .M525 B6718]

BOTT, E. & MORENO, I. (1999). DESCUBRE MICROSOFT OFFICE 2000. MADRID, ESPAÑA: PRENTICE HALL. [HF5548.4 M525 B6718]

Ferreyra Cortés, G. (2000). Office 2000 paso a paso. México: Alfaomega. [HF5548.4 .M525 F47]

Todos los derechos reservadosNo se permite reproducir este manual

Ni total ni parcialmenteSin previa autorización por escrito

de la Universidad de Colima

Impreso en la Universidad de ColimaEn los talleres de la Dirección General de Educación Continua

Elaborado por: Miguel Ángel Flores MaldonadoPara fines de enseñanza y uso exclusivo de la propia DGEC

Primera Impresión, Agosto 2010

95

2010/SECTOR PRODU… · Web viewTodos los comandos del grupo se muestran en alta resolución. ......

Documents

Transcript of 2010/SECTOR PRODU… · Web viewTodos los comandos del grupo se muestran en alta resolución. ......