microsoft® excel 2010 intermedio - prof. maría l. … · -ii-microsoft®excel 2010 intermedio...

Microsoft® Excel 2010

Intermedio

Prof. María L. Moctezuma, MA, CPS

mmoctezuma.wordpress.com

http://mmoctezuma.wordpress.com/

TODOS LOS DERECHOS RESERVADOS

El texto de este manual, o parte del mismo, no puede reproducirse o trasmitirse

por método o forma alguna, sea electrónica o mecánica, incluyendo copias

fotostáticas, copias electrónicas, acumulación en un sistema de información con

memoria o de ninguna otra forma sin la autorización por escrito de la autora.

TABLA DE CONTENIDO

INTRODUCCIÓN ............................................................................................................................................. 1

OBJETIVOS GENERALES ................................................................................................................................. 1

ENTRADA Y MANEJO DE DATOS ................................................................................................................... 1

Objetivos específicos ................................................................................................................................. 1

Plantillas (Templates) ............................................................................................................................... 2

Personalizar y grabar plantillas ................................................................................................................ 4

Nombrar rangos y calcular con éstos ....................................................................................................... 5

Name Manager ......................................................................................................................................... 5

Aplicar estilos ............................................................................................................................................ 6

Inmovilizar paneles (Freeze Panes) ........................................................................................................... 6

Dividir la hoja ............................................................................................................................................ 6

Proteger hojas........................................................................................................................................... 7

Proteger celdas ....................................................................................................................................... 10

Copiar valores de las fórmulas ................................................................................................................ 11

TRABAJAR CON MÚLTIPLES HOJAS ............................................................................................................. 11

Objetivos específicos ............................................................................................................................... 11

Ver varios libros al mismo tiempo .......................................................................................................... 11

Realzar más de una hoja de trabajo ....................................................................................................... 12

Personalizar las pestañas de las hojas .................................................................................................... 12

Ocultar hojas de trabajo y libros............................................................................................................. 13

Copiar hojas ............................................................................................................................................ 13

Crear fórmulas 3-D ................................................................................................................................. 14

Insertar hojas de cálculo adicionales ...................................................................................................... 14

Eliminar hojas de cálculo ........................................................................................................................ 14

Vincular valores en las hojas .................................................................................................................. 15

Romper vínculos ...................................................................................................................................... 15

FÓRMULAS Y FUNCIONES ........................................................................................................................... 16

Objetivos específicos ............................................................................................................................... 16

Fórmulas ................................................................................................................................................. 16

Referencia a las celdas ............................................................................................................................ 17

Mensajes de error en las fórmulas ......................................................................................................... 18

Funciones ................................................................................................................................................ 18

Categorías de funciones .......................................................................................................................... 19

-ii- Microsoft®Excel 2010 Intermedio PROFA. MARÍA L. MOCTEZUMA

Sintaxis de las funciones ......................................................................................................................... 20

Asistente de funciones (Function Wizard) .............................................................................................. 21

Función NETWORKDAYS ......................................................................................................................... 22

Función Lógica IF .................................................................................................................................... 23

Entrar más de una condición en IF.......................................................................................................... 26

Función matemática “SUMIF” ................................................................................................................ 27

Función Financiera FV (Inversión) ........................................................................................................... 27

Función Financiera PMT (Payment) ........................................................................................................ 29

ORIGINAR Y MODIFICAR GRÁFICAS ............................................................................................................ 31

Objetivos específicos.............................................................................................................................. 31

Conceptos generales .............................................................................................................................. 31

Partes de la gráfica ................................................................................................................................. 32

Términos ................................................................................................................................................ 32

Crear la Gráfica ....................................................................................................................................... 33

Mover la gráfica a otra hoja ................................................................................................................... 34

Cambiar el tamaño ................................................................................................................................. 34

Borrar la gráfica ...................................................................................................................................... 34

Cambiar el tipo de gráfica ...................................................................................................................... 35

Realzar la gráfica .................................................................................................................................... 35

Añadir etiquetas a diferentes áreas ....................................................................................................... 35

Aplicar formato a los títulos ................................................................................................................... 37

Cambiar la vista de la gráfica ................................................................................................................. 38

Alterar el color a la gráfica ..................................................................................................................... 38

Aplicar formato a las columnas .............................................................................................................. 39

Minigráficos (Sparklines) ........................................................................................................................ 39

Tipos de minigráficos (Sparklines) ......................................................................................................... 40

Insertar los minigráficos (Sparklines) ..................................................................................................... 41

MICROSOFT®EXCEL 2010 INTERMEDIO

INTRODUCCIÓN

Microsoft®Excel es la aplicación de hojas de cálculo electrónicas de Microsoft Office® que integra hojas

de trabajo, gráficas y manejo de datos en un solo programa. Se utiliza para efectuar tareas matemá-

ticas, financieras y organizativas. En los negocios se usa para contabilidad, análisis y administración de

proyectos. También es útil para realizar presupuestos, establecer comparaciones y dar seguimiento a

los gastos personales. En fin, es un programa sumamente útil para desempeñarse con máxima eficiencia

en el ambiente de trabajo competitivo que confrontamos actualmente.

OBJETIVOS GENERALES

Al terminar el adiestramiento los participantes podrán:

1. Utilizar herramientas adicionales que provee el programa para entrar, manejar y proteger datos.

2. Trabajar con varias hojas de cálculo simultáneamente.

3. Redactar y revisar fórmulas complejas e insertar funciones de mediana dificultad a una o varias hojas de cálculo electrónicas.

4. Originar y modificar diferentes tipos de gráficas.

ENTRADA Y MANEJO DE DATOS

Objetivos específicos

Al finalizar la primera lección los participantes:

1. Utilizarán plantillas que provee el programa para generar documentos.

2. Nombrarán rangos y calcularán con éstos.

3. Utilizarán el Administrador de Nombres (Name Manager).

4. Realzarán las hojas con estilos.

5. Inmovilizarán/movilizarán paneles.

6. Dividirán la hoja para copiar/mover más fácilmente datos de un lugar a otro.

7. Protegerán libros de trabajo y celdas.

8. Copiarán únicamente los valores en las fórmulas.

-2- Excel 2010 Intermedio

Profa. María L. Moctezuma

9. Dividirán la pantalla para copiar/mover más fácilmente datos de un libro de trabajo a otro.

Plantillas (Templates)

1. Las plantillas (Templates) son archivos que contienen la estructura y herramientas para formatear el estilo y el diseño de un archivo terminado. Pueden almacenarse en el disco duro e incluirse en una carpeta donde se comparta una colección de archivos.

2. Los documentos que grabe como plantillas aparecerán en la ventana de documentos nuevos (New), sección de plantillas disponibles (Available Templates), mis plantillas (My templates).

Utilizar plantillas

1. Dé clic en Archivo (File) » Nuevo .

2. En la ventana que abre aparecen las categorías de plantillas disponibles Plantillas disponibles (Available Templates) instaladas en el programa o en línea.

3. En el área Plantillas de Office.com (Office.com Templates) podrá localizar las plantillas que Microsoft Office® ofrece en línea.

4. Al seleccionar una categoría, en el centro habrá una vista previa de éstas.

Excel 2010 Intermedio -3-


5. En la opción Plantillas de ejemplo (Sample Templates) estarán las plantillas para preparar: Extractos de facturación (Billing Statement), Detector de presión sanguínea (Blood Pressure Tracker), Informe de gastos (Expense Report), Amortización de préstamo (Loan Amortization), Presupuesto mensual personal (Personal Monthly Budget), Informe de ventas (Sales Report) y Hoja de asistencia (Time Card).

6. Dé clic en cualquiera de las carpetas bajo Office.com Templates. Irá a otra ventana con más carpetas.

7. Dé doble clic en una de las carpetas y, si tiene conexión a la Internet, obtendrá una vista preliminar de las plantillas en línea. Éstas las podrá bajar (download) y personalizar a gusto.



8. Elija un modelo de plantilla y saldrá el documento en el cual sólo añadirá los datos necesarios. Contendrá, además, las fórmulas para determinar los cálculos.

9. Las que personalice y grabe como plantilla, se archivarán en la carpeta My Templates, en donde podrá localizarlas posteriormente.

Personalizar y grabar plantillas

1. En ocasiones hay hojas de trabajo que utilizamos con mucha frecuencia, en las cuales solo varían algunos datos. Para evitar estar copiando y pegando información, podemos grabar una plantilla.

2. Cree la hoja de cálculo que vaya a utilizar como plantilla. Dele el formato adecuado e incluya las fórmulas necesarias.

3. Grabe el documento por Archivo (File) » Grabar como y abrirá el cuadro de diálogo de esa función.

4. En la parte inferior del cuadro está Guardar como tipo (Save as Type).

5. Seleccione cualquiera de las siguientes opciones: Excel Template, Excel Macro-Enabled Template o Excel 97-2003 Template.

6. Dé clic en Guardar (Save) o presione .

7. Los documentos que grabe como plantillas aparecerán en la ventana de documentos nuevos (New), sección de plantillas disponibles (Available Templates), mis plantillas (My templates).

Enter



8. Estas plantillas permanecen inalteradas en la carpeta Mis plantillas (My templates). Al igual que las que provee el programa, al abrirlas se crea un documento independiente que puede grabar con el nombre que desee.

Ejercicio 1 — Utilizar plantilla de factura

Nombrar rangos y calcular con éstos

1. Un rango o grupo de celdas es una serie de dos o más celdas adyacentes en una columna o fila; se denominan, B2:D8.

2. El rango se selecciona igual que un bloque de información.

3. Asignamos nombre a un rango (ranges) seleccionando el grupo de celdas o rango.

4. Dé clic en el indicador de celda activa (Name box) a la izquierda de la barra de fórmula.

5. Escriba el nombre que asignará al rango (range) y pulse [Enter].

6. Cuando se asigna un nombre a un rango, al redactar una fórmula se copia el nombre de éste, en vez de la referencia a las celdas.

Name Manager

1. Esta fue una novedad de 2007 para trabajar con rangos a los cuales hemos asignado nombres. Suministra una lista de los rangos con nombre en un libro, además, provee las referencias a las celdas.

2. Con el Name Manager se pueden borrar, modificar y agregar comentarios a los rangos con nombre.

3. Dé clic en la pestaña Formulas (grupo Defined Names) » .

4. Saldrá el siguiente cuadro de diálogo:



5. En la parte superior están los botones:

a. New – para agregar un nombre nuevo

b. Edit – para editar el nombre existente

c. Delete – para eliminar el nombre del rango; el programa pide confirmación para esta acción

Aplicar estilos

1. En la pestaña Home, grupo Styles, hay una galería con formatos predeterminados para las celdas, columnas y hoja de cálculo.

2. Marque sólo la celda o el rango al cual aplicará el estilo.

3. Seleccione del grupo:

a. Conditional Formatting para destacar ciertas celdas, hacer énfasis en valores inusuales y realzar los datos con barras, escalas de colores e incluir iconos a base de ciertos criterios.

b. Format as Table permite aplicar un estilo predefinido a un rango y convertirlo en tabla.

1) Este formato se utiliza para generar filtros y ordenar datos.

2) También permite insertar una fila de totales (Total Row), a saber, pestaña contextual Design » (grupo Table Style Options) » Total Row.

3) Se puede eliminar el formato de tabla manteniendo el estilo. Selecciona el rango que tenga el formato » clic derecho » Table » Convert to Range.

c. Cell Styles ayuda a formatear y personalizar una o varias celdas con estilos predefinidos.

Inmovilizar paneles (Freeze Panes)

1. Esta función mantiene visibles datos de una hoja a los cuales necesite hacer referencia, como por ejemplo, los títulos de columnas y las entradas en la primera columna.

2. Active la celda que permita ver las áreas a congelar.

3. Seleccione la pestaña View (grupo Window) » .

4. Excel 2010 permite seleccionar dos opciones adicionales:

a. Freeze Top Row – mantiene visible la fila superior cuando navega en la hoja

b. Freeze First Column – mantiene visible la primera columna de la hoja no importa el lugar que se navegue en la hoja

5. Para eliminar la función: pestaña View (grupo Window) » Freeze Panes » Unfreeze Panes.

Dividir la hoja

1. Esta función permite dividir hojas de cálculo extensas en paneles y navegar en diferentes puntos para hacer referencia a otros datos o copiar/mover información de un lugar a otro de la misma hoja.



2. Active la celda que permita ver las áreas a dividir.

3. Seleccione la pestaña View, grupo Window » . La pantalla se dividirá en cuatro secciones.

4. También puede dar doble clic en ; este botón está en la parte superior de la barra de desplazamiento derecha. La pantalla se dividirá en dos secciones.

5. Las líneas divisorias puede moverlas arrastrándolas con el ratón.

6. Las elimina con doble clic encima de la línea divisoria o de la misma forma que la activó.

Proteger hojas

1. Coloque el cursor en cualquier lugar de la hoja.

2. Dé clic en la pestaña Review (grupo Changes) » .

3. También puede dar clic en Home (grupo Cells) » » Protect Sheet.

4. En el cuadro de diálogo agregue una contraseña (password) en Password to unprotect sheet.

5. En el área Allow all users of this worksheet to marque las opciones que permitirá ejecutar en la hoja.

Quite o marque

esta opción Para evitar que los usuarios:

Select locked cells

Muevan el puntero a celdas en la cuales esté marcado Locked en la

pestaña Protection del cuadro de diálogo Format Cells. Está

predeterminado que los usuarios seleccionen las celdas bloqueadas.



Quite o marque


Select unlocked cells

Muevan el puntero a celdas en la cuales no esté marcada la opción

Locked en la pestaña Protection del cuadro de diálogo Format Cells.

Está predeterminado que los usuarios seleccionen las celdas que no

estén bloqueadas y en una hoja de cálculo protegida pueden presionar

para moverse entre las celdas que no están bloqueadas.

Format cells

Cambien cualquiera de las opciones en los cuadros de diálogo Format

Cells o Conditional Formatting. Si se aplican formatos condicionados

antes de proteger la hoja, el formato continuará cambiando cuando un

usuario entre un valor que satisfaga una condición diferente.

Format columns

Usen cualquiera de los comandos de formato para columna, incluyendo

alterar el ancho de la columna o esconderla (pestaña Inicio [Home],

grupo Celdas [Cells], botón Formato [Format]).

Format rows

Utilicen cualquiera de los comandos de formato para filas, incluyendo

alterar el alto de la columna o esconderla (pestaña Inicio [Home], grupo

Celdas [Cells], botón Formato [Format]).

Insert columns Inserten columnas.

Insert rows Inserten filas.

Insert hyperlinks Inserten hipervínculos nuevos, aún en celdas que no estén bloqueadas.

Delete columns

Eliminen columnas.

Nota: Si Delete columns está protegido e Insert columns no está

protegido, el usuario puede insertar columnas que no podrá borrar

luego.

Delete rows

Eliminen filas.

Nota: Si Delete rows está protegido e Insert rows no está protegido, el

usuario puede insertar filas que no podrá borrar posteriormente.

Sort

Utilicen cualquier comando para ordenar datos (pestaña Datos (Data),

grupo Ordenar y filtrar (Sort & Filter).

Nota: Los usuarios no podrán ordenar rangos que contengan celdas

bloqueadas en una hoja protegida, no importa que se altere este

formato.

Tab



Quite o marque


Use AutoFilter

Usen flechas desplegables para cambiar los filtros en rangos cuando se

aplican filtros automáticos (AutoFilters).

Nota: Los usuarios no podrán aplicar o remover filtros automáticos

(AutoFilters) en una hoja protegida, no importa que se altere este

formato.

Use PivotTable

reports

Formateen, cambien el diseño, actualicen (refreshing), o modifiquen

tablas dinámicas (PivotTables) o las creen nuevas.

Edit objects

Hace lo siguiente:

1. Cambios a objetos gráficos, incluyendo mapas, gráficas incrustadas, objetos, cuadros de texto y controles que no se desbloquearon antes de proteger la hoja. Por ejemplo, si una hoja tiene un botón que ejecuta una macro, se puede usar el botón, pero no borrarlo.

2. Cambios de formato a gráficas incrustadas. Sin embargo, éstas continuarán actualizándose cuando se cambien los datos.

3. Añadir o editar comentarios.

Edit scenarios

Muestren escenarios escondidos, hagan cambios y borren escenarios a

los cuales se les han limitado los cambios. Los usuarios pueden cambiar

los valores en las celdas cambiantes si no están protegidas y añadir

nuevos escenarios.

Nota: Un escenario es un grupo de valores que Microsoft Office Excel

graba y puede sustituir automáticamente en la hoja de cálculo.

Contents

Hacen cambios a ítems que pertenecen a la gráfica, como series de

datos, ejes y leyenda. La gráfica continuará reflejando los cambios

hechos a la fuente de datos.

Objects

Hacen cambios a objetos gráficos—objetos, cuadros de texto y

controles—a menos que se desbloqueen los objetos antes de proteger la

hoja que contenga la imagen.



6. Dé clic en OK » confirme la contraseña » OK.

Notas: Las contraseñas son opcionales. Si no entra una, cualquier usuario puede desproteger la hoja y cambiar los elementos.

Asegúrese de escoger una contraseña fácil de recordar porque si la olvida no podrá acceder a los elementos protegidos en la hoja.

6. Para desactivar la función siga el mismo procedimiento que utilizó para activarla y seleccione Unprotect Sheet » copie la contraseña » OK.

Proteger celdas

1. Para proteger una celda, la hoja de cálculo debe estar sin protección.

2. Seleccione la hoja completa con clic el botón Select All .

3. Dé clic en la pestaña en Home (grupo Font) y en el iniciador (launcher) para activar el cuadro de diálogo Format Cell Font. También puede presionar [Ctrl]+[1] o [Ctrl]+[Shift]+[F].

4. En la pestaña Protection, limpie la casilla Locked y dé clic en OK.

Nota: Esta acción desprotege todas las celdas de la hoja cuando están protegidas.

5. Seleccione solo las celdas que vaya a proteger.

6. Vuelva a dar clic en la pestaña en Home (grupo Font) y en el iniciador (launcher)

.

7. En la pestaña Protection, marque la casilla Locked y dé clic en OK.

8. Dé clic en la pestaña Review (grupo Changes) » .

9. En la lista bajo Allow all users of this worksheet to, marque los elementos que dejará cambiar por los usuarios, según explicado en el tema anterior.

12. Agréguele una contraseña.

13. Para desactivar la función siga el mismo procedimiento que utilizó para activarla y seleccione Unprotect Sheet » copie la contraseña » OK.



Copiar valores de las fórmulas

1. Seleccione la celda que contenga el valor a copiar.

2. Dé clic en el botón de copiar o clic derecho (right-click) » Copy.

3. Active la celda donde copiará el valor y dé otro clic derecho (right-click) » Paste Special » Values.

4. Dé clic en OK.

Ejercicio 2 — Repaso, nombrar rango, inmovilizar paneles (Freeze Panes), proteger la hoja

Ejercicio 3 — Proteger celdas, copiar valores de fórmulas

TRABAJAR CON MÚLTIPLES HOJAS


Al finalizar la tercera lección los participantes:

1. Trabajarán con varios libros al mismo tiempo.

2. Realzarán más de una hoja de trabajo simultáneamente.

3. Personalizarán las pestañas de las hojas.

4. Ocultarán/mostrarán hojas de cálculo y libros de trabajo.

5. Copiarán/moverán hojas de cálculo.

6. Redactarán fórmulas 3-D.

7. Insertarán/eliminarán hojas de cálculo.

8. Vincularán/desvincularán valores en las hojas.

Ver varios libros al mismo tiempo

1. Abra los libros que desee.

2. Dé clic en la pestaña View (grupo Window) » Arrange All.

3. Se abrirá el cuadro de diálogo de la derecha.

4. Habrá cuatro opciones para ver las hojas:

a. Tiled o en mosaicos – se mostrarán en cuadros pequeños



b. Horizontal – estarán en franjas una debajo de la otra

c. Vertical – se verán en paneles una al lado de la otra

d. Cascade o en cascada – estará una sobrepuesta a la otra en

forma escalonada

5. Seleccione la forma que prefiera y presione OK.

6. Si marca la casilla Windows of active workbook, también se

mostrarán las hojas de cálculo que tenga en cada uno de los libros.

7. Para desactivar la función, maximice una de las hojas.

Ejercicio 4 – Comparar una hoja con otra

Realzar más de una hoja de trabajo

1. Para editar y formatear más de una hoja de cálculo simultáneamente, hay que agruparlas. Los

cambios que se hagan a la hoja activa se reflejarán en todas las hojas del grupo.

2. Para agrupar las hojas pulse [Ctrl] mientras da clic sobre cada pestaña o dé clic en la primera

pestaña en un rango contiguo, mantenga oprimida la tecla [Shift] y dé clic en la última pestaña.

También puede dar clic derecho (right-click) en una de las pestañas y seleccionar Select All Sheets.

3. Sabrá que las hojas están activas porque cambia el color de las pestañas a blanco.

4. Para desagruparlas:

a. Dé clic una de las pestañas agrupadas, que no sea la hoja activa.

b. También puede dar clic derecho (right-click) en una de las pestañas y elegir Ungroup Sheets.

Personalizar las pestañas de las hojas

1. Cambiar el nombre:

Dé doble clic encima de la pestaña y escriba el nuevo nombre.

2. Cambiar el color:

a. Dé clic derecho (right-click) encima de la pestaña, seleccione del menú Tab Color y tendrá una

muestra para elegir diferentes colores. Además, al final está la opción More Colors para buscar

otras alternativas.



b. También puede aplicarlo por Home (grupo Cells) » Format » Tab Color.

Ocultar hojas de trabajo y libros

1. Hojas de cálculo:

a. Seleccione la(s) hoja(s) que vaya a ocultar.

b. Dé clic derecho (right-click) encima de la pestaña, seleccione del menú Hide.

c. También puede dar clic en Home (grupo Cells) » Format » Hide & Unhide » Hide Sheet.

2. Un libro – pestaña View (grupo Window) » Hide (si ha realizado cambios, el programa avisará para

que los guarde cuando salga).

3. Mostrar lo escondido:

a. Las hojas

1) Dé clic derecho (right-click) encima de la pestaña, seleccione del menú Unhide.

2) También puede dar clic en Home (grupo Cells) » Format » Hide & Unhide » Unhide Sheet »

del cuadro de diálogo seleccione la hoja a mostrar » OK.

b. El libro – View (grupo Window) » Unhide » del cuadro de diálogo seleccione el que quiera

mostrar » OK.

Copiar hojas

1. Oprima la tecla [Ctrl] y arrastre el nombre en la pestaña de la hoja a copiar. Suelte el botón del

ratón en el lugar deseado.

2. Dé clic derecho (right-click) encima del nombre de la hoja a copiar » Move or copy » (move to end)

» marque Create a copy » OK.



3. Saldrá la hoja nueva con el nombre de la que copió y un 2 entre paréntesis.

Crear fórmulas 3-D

1. Esta función permite crear una fórmula incluyendo varias hojas de cálculo de un libro.

2. Seleccione la celda donde irá la fórmula en la nueva hoja y copie =.

3. Dé clic en la celda que contenga el valor a calcular en la primera hoja e incluya el operador.

4. Abra la segunda hoja y repita el paso anterior cuantas veces sea necesario.

5. Al ir a la última hoja a incluir en el cálculo, marque la celda que contenga el valor a calcular y

presione [Enter].

6. Copie la fórmula vertical u horizontalmente, según sea necesario.

Insertar hojas de cálculo adicionales

1. Dé clic en la pestaña al calce de la hoja de cálculo. Saldrá la hoja con el nombre Sheet__.

2. También puede colocar el cursor en una de las pestañas con el nombre de las hojas. Dé clic derecho

(right-click) » Insert » Worksheet » OK.

Eliminar hojas de cálculo

1. Coloque el cursor en la pestaña de la hoja a eliminar.

2. Dé clic derecho (right-click) » Delete. Si la hoja contiene datos, deberá confirmar la acción.



Vincular valores en las hojas

1. Puede vincular una fórmula en una hoja a una celda en otra en el mismo libro o en diferentes libros.

De esta manera, al editar los valores en la hoja original se actualizarán automáticamente en la hoja

que contenga el vínculo.

2. Abra la hoja y active la celda que contiene el valor a enlazar y dé el comando para copiar .

También puede utilizar [Ctrl] + [C].

3. Seleccione la celda en donde irá el vínculo, ya sea en la hoja actual o en otra del mismo libro.

4. Dé clic en la pestaña Home (grupo Clipboard) » Paste » Paste Link.

5. También puede dar el comando para copiar, dar clic derecho (right-click) en la celda en donde

establecerá el vínculo y seleccionar .

6. Si el vínculo se hace a la misma hoja, la celda contendrá una referencia absoluta a la celda que

contenga los datos de origen. Si el vínculo se hace a otra hoja, la fórmula resultante contendrá en

primer término el nombre de la hoja de donde provienen los datos, un signo de admiración para

separar el nombre de la hoja de la referencia de la celda y la dirección de la celda utilizando una

referencia absoluta .

Romper vínculos

1. Elimine los datos que contenga la celda con el vínculo.

2. Dé clic en la pestaña Data (grupo Connections) » Edit Links. (Este comando no estará disponible si

el archivo en pantalla no contiene vínculos.)

3. En la lista Source, dé clic en el vínculo a romper. Si va a seleccionar varios vínculos, oprima [Ctrl] y

dé clic en cada vínculo. Si los va a elegir todos, presione [Ctrl] + [A].

4. Dé clic en Break Link.

5. Si el vínculo estaba definido con un nombre, éste permanecerá en la lista. Para borrar el nombre:

Dé clic en la pestaña Formulas (grupo Defined Names) » Name Manager » seleccione el nombre »

Delete » OK.

Ejercicio 5 – Realzar todas las hojas de un libro

Ejercicio 6 – Resumir datos con fórmula 3-D

Ejercicio 7 – Crear/eliminar vínculos



FÓRMULAS Y FUNCIONES


Al finalizar la segunda lección los participantes:

1. Conocerán la estructura de las fórmulas complejas y el orden de evaluación de éstas.

2. Identificarán las referencias relativas, absolutas y mixtas y conocerán la importancia de éstas enlas fórmulas.

3. Identificarán los mensajes de error en las fórmulas.

4. Redactarán fórmulas complejas.

5. Conocerán las categorías de funciones.

6. Conocerán la sintaxis de las funciones.

7. Utilizarán el asistente de funciones para redactar las fórmulas.

8. Utilizarán las funciones: TODAY, NOW, DATE, DAYS360, NETWORKDAYS, IF, SUMIF, COUNTIF, FV y PMT.

Fórmulas

1. Las fórmulas son expresiones matemáticas que consisten de operadores, valores, variables y símbolos; son ecuaciones que se entran en ciertas celdas para calcular los valores de otras. Se basan en constantes, referencia a celdas o ambas.

2. Los valores/variables son el número o referencia de celda utilizado en las fórmulas.

3. Los operadores matemáticos son símbolos o palabras que indican que se ejecutará una operación; le señala al programa qué hacer con los valores/variables.

4. Hay también operadores comparativos:

a. = es igual

b. <> no es igual a, distinto de

c. < menor que

d. > mayor que

e. <= menor que o igual

f. >= mayor que o igual

g. OR y AND – permiten añadir criterios adicionales en el mismo o en campos diferentes

5. Los tipos de fórmulas son simples o complejas; a continuación un ejemplo de fórmula compleja demostrando el orden de evaluación:



120/(8-5)*4-2

Se convierte en:

120/3*4-2

Se convierte en:

40*4-2

Se convierte en:

160-2

Ejercicio

A base de lo presentado el resultado de la fórmula 2*(6-4) tendrá un resultado de: ¿8 ó 4?

Referencia a las celdas

1. La ubicación de una celda en la hoja de cálculo se conoce como referencia a la celda.

2. Relativa – se entra una fórmula y al copiarla a otra celda adopta los parámetros del nuevo lugar (Ej. Si copiamos la fórmula =D4*D5 a la derecha, cambiará a =E4*E5.)

3. Absoluta – contiene los números de columnas y filas precedidos de un signo de dólares; hará referencia siempre a la misma dirección (Ej. Si entramos la fórmula =D4*$B$2, multiplicará el valor

Primero la operación entre

paréntesis: 8 – 5 = 3

Sigue la división: 120/3 = 40

Sigue la multiplicación:

40*4 = 160

Por último, la resta:

160-2 = 158

http://spreadsheets.about.com/od/excelformulas/ss/formula_begin_5.htm



en la celda D4 por el que está en la celda B2 y al copiarla siempre multiplicará por el valor en la celda B2.)

4. Mixta – la que contiene referencia relativa y absoluta

a. Se puede hacer referencia absoluta a la columna y relativa a la fila (Ej. = D4*$B2), lo cual hará que la fórmula siempre mire a los valores en la columna B.

b. Cuando se hace referencia a una fila (Ej. = D4*B$2), provoca que la fórmula siempre busque lo valores en la fila 2.

Mensajes de error en las fórmulas

1. ##### – indica que las cifras no caben en el ancho de la celda o que una celda contiene una fecha u hora con valores negativos; por ejemplo, =06/15/2010–07/01/2010 es una fórmula que resta una fecha del pasado de una fecha en el futuro y resultará en un valor negativo

2. #NAME? – significa que hay algún error en el enunciado de la fórmula (espacio o letra incorrecta); por ejemplo: referirse a un rango con un nombre que no existe, utilizar una función que no existe o que está escrita incorrectamente, utilizar etiquetas cuando no está permitido, entrar texto sin que esté entre comillas, usar una referencia a un rango inválido, referirse a otra hoja de cálculo que no existe

3. #VALUE! – significa que el formato de algún valor es incorrecto o que se ha incluido texto donde debe ir un valor numérico

4. #REF! – indica un error en la referencia a una celda que no contiene datos o se borró; usar un enlace DDE (Dynamic Data Exchange) que no esté disponible

5. #DIV/0! – significa que se dividió entre 0 o se hizo referencia a una celda en blanco

6. #NUM! – surge cuando en una fórmula o función hay valores numéricos inválidos (muy pequeños o grandes); incluir argumentos incorrectos en una función

7. #N/A – indica que un valor no está disponible para una función o fórmula; cuando los parámetros no parean en las funciones VLOOKUP, HLOOKUP o MATCH

8. #NULL! – cuando se utiliza un separador incorrecto en un rango; cuando busca la intersección en dos rangos de una fórmula y no la hay, por ejemplo, =SUM(A1:A2 C3:C5)

Ejercicio 8 – Fórmulas complejas con referencias absolutas, formato a números

Funciones

1. Las funciones son fórmulas predefinidas para ayudar a trabajar con mayor rapidez y de forma sen-cilla cálculos complejos. Se utilizan para simplificar y reducir la longitud de las fórmulas usadas en las hojas de cálculo. Sólo requieren los valores o argumentos (valores específicos) para realizar el cálculo. Excel ofrece decenas de funciones para ejecutar cálculos simples y complejos.



Nota: Al entrar ci-

fras en los argumen-tos de las funciones, no se copia el signo de dólares ($) ni las comas.

2. Al igual que las fórmulas regulares, se comienzan con el signo de es igual (=), excepto las que se activan por la flecha del botón AutoSum. Luego se elige la función y se añaden los valores o argumentos (Ej. =SUM(A1:A8). Cada argumento se separa por coma y se coloca entre paréntesis.

3. A continuación incluimos algunas de las funciones matemáticas, financieras y estadísticas que más se utilizan:

Función Operación

SUM (suma) Muestra la suma del argumento del rango indicado.

AVERAGE (promedio) Determina el promedio del rango indicado entre paréntesis. Por ejemplo, =AVERAGE(E4:E9) da el promedio de las cifras contenidas en el bloque de celdas mencionado.

IF (si)

Prueba el valor de una celda y ejecuta una acción si éste es positivo (true) y otra si es negativo (false). Requiere tres argumentos: la prueba, la acción a realizar si la prueba es cierta y la acción a realizar si es falsa.

HYPERLINK (hipervínculo) Hace un enlace con otro archivo, dentro del disco duro o en la Red.

COUNT (contar) Establece el número de celdas llenas en el rango identificado. Por ejemplo, =COUNT(D6:D21), da a 16, si todas las celdas contienen valores. Las celdas en blanco o con texto son ignoradas.

COUNTA Cuenta todas las celdas ocupadas, sin importar el contenido.

MAX (máximo) Muestra el número más alto incluido en el rango identificado en el argumento.

MIN (mínimo) Despliega el número más bajo contenido en el rango (range) identificado en el argumento.

ROUND (redondeo) Ajusta el valor a un número de dígitos específico.

SIN Función trigonométrica que calcula el seno de un ángulo.

SUMIF Suma la celda especificada a base de un criterio definido.

PMT Función financiera que calcula el pago periódico de una deuda.

STDEV (desviación estándar) Muestra la desviación estándar de los números contenidos en el rango identificado en el argumento, a base de una muestra.

VAR (varianza) Estima la varianza de una muestra.

Categorías de funciones

1. Funciones financieras – ejecutan cálculos comunes de negocios, como por ejemplo FV, que determina el valor futuro de una inversión, o PMT, que calcula el pago periódico de una deuda



2. Funciones lógicas – indican si una condición es cierta o falsa, como por ejemplo IF, que prueba condicionalmente fórmulas y valores

3. Funciones textuales – permite manipular texto de maneras especiales, como por ejemplo CONCATENATE, que une dos piezas de texto separadas

4. Funciones de fecha y tiempo – permiten analizar y trabajar con fechas y tiempo, como por ejemplo NOW, que trae la fecha y hora actual

5. Funciones de búsqueda y referencia – permite encontrar referencias o valo-res específicos, como por ejemplo HLOOKUP, que encuentra un valor en una fila

6. Funciones matemáticas y trigonométricas – ejecuta funciones matemáticas, como por ejemplo ABS, que determina el valor absoluto de un número

7. Otras funciones (estadísticas, de ingeniería, cúbicas e informativas)

8. Al abrir el cuadro de diálogo para insertar las funciones (Insert Function) aparecen todas las categorías de las funciones que se pueden generar en Excel. Además de las mencionadas, están:

a. Funciones estadísticas – proveen análisis estadísticos, como por ejemplo FREQUENCY, que determina la frecuencia con que ocurre un valor, o VAR, que estima la varianza de una muestra, etc.

b. Funciones de bases de datos – permiten trabajar con bases de datos o listas de ítems, como por ejemplo DCOUNT, que cuenta la cantidad de celdas que contiene un criterio definido

c. Funciones informativas – indican qué tipo de datos contiene una celda, como por ejemplo COUNTBLANK, que determina la cantidad de celdas en blanco en un rango

d. Funciones de ingeniería – proveen funciones utilizadas comúnmente en la solución de problemas de ingeniería, como BIN2DEC, que convierte un número binario en decimal

e. Funciones cúbicas – estas funciones se usan para extraer datos OLAP (Online Analitical Processing – series y valores) de Analysis Services y mostrarlos en una celda. Las formulas OLAP se pueden generar cuando conviertes fórmulas de tablas dinámicas (PivotTable) a fórmulas en celdas regulares o cuando se utiliza AutoComplete al entrar los argumentos de funciones al cubo.

Sintaxis de las funciones

Las funciones tienen un orden específico, al cual se le llama sintaxis, y debe seguirse estrictamente para que funcionen correctamente y obtener los resultados deseados:

a. comienzan con el signo de es igual (=)

b. luego el nombre de la función (SUM, AVERAGE, PMT, etc.)

c. uno o más argumentos (números, texto o referencias a celdas entre paréntesis); si hay más de un argumento se separa por coma, como por ejemplo:



Asistente de funciones (Function Wizard)

1. Escriba el signo de es igual (=). Observe que en el Name box a la extrema izquierda de la barra de fórmula aparece la última función realizada en el equipo y, si abre la flecha, verá la lista de las últi-mas diez funciones activadas.

2. Dé clic en el nombre de la función y se abrirá el cuadro de diálogo para establecer los argumentos. De no estar en la lista la función a realizar, al final está la opción More Functions, la cual abrirá el cuadro de diálogo Insert Function.

3. También puede insertarlas por Sum , pestaña Home, grupo Editing; por AutoSum, ubicado en la pestaña Formulas, grupo Function Library, o directamente por el botón que representa la función a realizar en ese mismo grupo.

4. Otra forma de insertar funciones es presionando [Shift] + [F3], con clic en el botón

en la barra de fórmula o por la pestaña Formulas , grupo Function Library.

5. Con las opciones mencionadas en el ítem 4, saldrá el cuadro e diálogo que sigue:

=AVERAGE(B3:B10, C3:C10)

equal sign function name arguments



Nota: Verifique que las fechas tengan el formato adecuado y que las entre en el orden correcto a base de la configuración del lenguaje en el equipo.

6. En éste se seleccione la categoría (Or select a category) y la función a realizar (Select a function). Inmediatamente verá el cuadro de diálogo para insertar los argumentos de la función (Function Arguments). Los renglones que destacan en negrilla (bold) hay que incluirlos para efectuar el cálculo.

7. El botón de colapsar en los cuadros de diálogos permite minimizarlos para seleccionar más fácilmente las celdas a incluir en la operación.

8. Después de completados los argumentos, dé clic en OK.

Función NETWORKDAYS

1. Se utiliza para determinar la cantidad de días laborables entre dos fechas. Remueve automáticamente los fines de semana del total. También elimina algunos días feriados federales.

2. La sintaxis de esta función es:

argumento (el botón de colapsar el cuadro

de diálogo de la derecha permite minimizarlo

para seleccionar más fácilmente las celdas

en la hoja de cálculo)

resultado

descripción

de la función

enlace de ayuda



Nota: Esta función no se

puede aplicar a un rango.

=NETWORKDAYS (Start_date, End_date, Holidays)

a. Start_date – fecha inicial del periodo

b. End_date – fecha final del periodo

c. Holidays – se utiliza para excluir una o más fechas del total de días laborables; aunque esta parte del argumento es opcional, en Puerto Rico hay que incluir los días feriados locales

3. Verifique que las celdas donde copie las fechas tengan el formato adecuado.

4. Dé clic en la celda donde va la fórmula y otro en la pestaña Formulas (grupo Function Library) »

» NETWORKDAYS.

5. Dé clic en Start_date y otro en la celda donde aparezca esta fecha.

6. Dé otro clic en End_date y otro en la celda donde esté la fecha.

7. Dé clic en en Holidays y otro en la(s) celda(s) donde esté(n) la(s) fecha(s) y en OK. Aquí puede seleccionar el rango, de haber un detalle de más de un día feriado.

Ejercicio 9 – Función NETWORKDAYS

Función Lógica IF

1. Esta función se utiliza para establecer condiciones dentro de una hoja de trabajo e implantar decisiones. Los resultados son determinados por la vera-cidad o la falsedad de la condición. Si la condición es cierta, se obtendrá un

resultado, y si es falsa, se producirá un resultado diferente.

2. Una peculiaridad de este tipo de función es que no se puede aplicar a un rango.

3. Para indicar las condiciones se utilizan los operadores de comparación que están en la página 16.

4. La función contiene tres argumentos o parámetros:



=IF(condición, valor si cierta, valor si falsa)

Valor que refleja el resultado si la condición es falsa

Valor que refleja el resultado si la condición es cierta

Condición cierta o falsa

http://z.about.com/d/spreadsheets/1/0/3/1/-/-/if_funct_4.gif



http://spreadsheets.about.com/od/excelfunctions/ss/if_function_sbs_7.htm



14. Puede utilizar los botones para insertar la función. Al dar clic en el botón saldrá el cuadro de diálogo Insert Function.

15. En Or select a Category elija la categoría de la función a realizar, si es que no está en la lista de Select a function, marque la función en Select a function y dé clic en OK.

16. Saldrá el cuadro de diálogo para establecer los argumentos, los cuales son los siguientes:

a. Logical_test – cualquier valor o expresión que pueda evaluarse como cierta o falsa

b. Value_if_true – valor que surge cuando el enunciado es cierto

c. Value_if_false – valor que resulta cuando el enunciado es falso

Ejemplo: Si el empleado vendió más de $50,000, recibirá una comisión de 1.5%; si no, no recibirá comisión.

Fórmula: =IF(B2>50000,B2*1.5%,0)

Observe que en la fórmula no se incluyen en la cantidad ni el $ ni la coma.

http://z.about.com/d/spreadsheets/1/0/-/1/-/-/if_funct_7.gif



Ejercicio 10 – Función IF

Entrar más de una condición en IF

1. Al utilizar las función lógica IF puede entrar más de una condición.

2. Active la función y entre los argumentos de la primera condición, excepto el Value_if_false.

3. En la barra de fórmula, coloque el cursor antes del paréntesis final de la fórmula que se creó y copie una coma.

4. Vuelva a activar la función IF y entre los argumentos de la segunda condición, repitiendo el segundo paso hasta completar todos los argumentos.

5. Al entrar el último argumento o condición es que copiará el de Value_if_false.

6. Dé clic en OK.



Ejercicio 11 – IF con más de una condición

Función matemática “SUMIF”

1. Esta función sumará los valores de las celdas en un rango si llenan ciertos criterios.

2. La sintaxis para la función es: =SUMIF( Range, Criteria, Sum Range)

a. Rango (Range) – grupo de celdas que contiene o no el criterio y la que Excel buscará para hacer el cálculo

b. Criterio (Criteria) – condición o criterio, que puede tener formato de número, expresión o texto, que define qué celdas se sumarán

c. Rango de suma (Sum Range) – El rango que se suma si el primer rango del argumento llena ciertos criterios

3. Para comenzar la función dé clic en la pestaña Formulas (grupo Function Library) » » SUMIF.

4. Saldrá el cuadro de diálogo para entrar los argumentos de la función.

a. Range – seleccione el rango del cálculo que contendrá o no el criterio

b. Criteria – copie el criterio o seleccione la celda que lo contenga; éste puede ser un número, texto o expresión matemática

c. Sum Range – seleccione el rango que Excel sumará a base del criterio expuesto

5. En el ejemplo anterior el resultado es 50 porque el criterio (165) solo lo contenían dos celdas, B2 y B6. Por ello solo se suman las cantidades de las celdas C2 y C6 (20 + 30 = 50).

Función Financiera FV (Inversión)

1. Esta función es para obtener el valor futuro de una inversión, a base de un pago periódico y un tipo de interés constante.

2. La sintaxis de la función es la siguiente: =FV(Rate, Nper, PMT).



3. A pesar de que el cuadro de diálogo tiene cinco argumentos, la misma requiere solo los tres primeros argumentos o parámetros que siguen:

a. tasa de interés por el periodo (Rate = interés anual dividido entre los doce meses)

b. número de periodos (Nper = si se establece en años, hay que multiplicarlo por los doce meses)

c. pago (Pmt = cantidad de dinero que se paga en cada periodo, el cual no debe cambiar; hay que entrarla como valor negativo)

4. Dé clic en la pestaña Formulas (grupo Function Library) » » FV.


a. Rate – seleccione la celda con el tipo de interés que pagarán por la inversión y divídalo entre los doce meses del año

b. Nper – elija la celda que contenga el periodo de tiempo por el cual estará pagando por la cifra total que invertirá; si está establecido en años, multiplíquelo por 12

c. Pmt – marque la celda que tenga la cantidad que pagará periódicamente para completar el total de la inversión; hay que entrarlo como valor negativo

d. Dé clic en OK.

Ejemplo: Va a invertir $15,000 e interesa saber cuánto sería el rendimiento (return) aproximado si paga a razón de $1,000 mensuales. Le ofrecieron una tasa de interés anual de 6.5%. La fórmula sería:

=FV(6.5%/12,15,-1000)

Fórmula: =FV(C4/12,C5,-C6)

Cantidad a pagar (negativa)

Número de pagos (15 meses)

Tasa de interés anual (% dividido entre 12 meses)



Ejercicio 12 – FV

Función Financiera PMT (Payment)

1. Esta función se utiliza para calcular el pago periódico de una deuda (préstamo hipotecario o personal) o para determinar la cantidad que debes ahorrar para llegar a un tope.

2. La sintaxis de la función es la siguiente: =PMT(Rate, Nper, Pv).

3. A pesar de que el cuadro de diálogo tiene cinco argumentos, la misma requiere solo los tres primeros argumentos o parámetros que siguen:

a. tasa de interés (Rate = interés anual dividido entre los doce meses)

b. número de periodos (Nper = si se establece en años, hay que multiplicarlo por los doce meses)

c. principal (Pv = cantidad total del préstamo o ahorro; hay que entrarla como valor negativo)

d. valor futuro (Fv = balance en efectivo que desea obtener después del último pago, aparece difuso porque se presume en 0, o sea que el valor futuro de un préstamo es 0)

e. tipo (Type = es el valor que representa el número del pago 0 ó 1 que indica cuando hay pagos vencidos; el 0 o nada se refiere al final del periodo, 1 es el principio del periodo)

4. Dé clic en la pestaña Formulas (grupo Function Library) » » PMT.


e. Rate – seleccione la celda con el tipo de interés que pagarán por la inversión y divídalo entre los doce meses del año

f. Nper – elija la celda que contenga el periodo de tiempo por el cual estará pagando por la cifra total que invertirá; si está establecido en años, multiplíquelo por 12

g. Pv – marque la celda que contenga la cantidad total de la deuda o ahorro planificado; hay que entrarlo como valor negativo



h. Dé clic en OK.

6. Para poder copiarla en diferentes direcciones, es necesario usar referencias absolutas y mixtas, dependiendo de la dirección.

Ejemplo: Va a comprar un carro de $15,000 e interesa saber cuánto sería el pago mensual apro-ximado si el préstamo es por 6 años a una tasa de interés anual de 10.5%. La fórmula sería:

=PMT(10.5%/12,72,-15000)

Fórmula: : =PMT($B2/12,$C2*12,-$A$2) = $281.68

Analice esta fórmula aplicada a la hoja de cálculo provista abajo para determinar si se ajusta correctamente al copiarla hacia abajo en la Columna D y hacia la derecha a la Columna H. ¿Puede utilizar una sola fórmula? ¿Cómo hay que modificarla?

Cantidad del préstamo (negativa)

Número de pagos (6 años X 12 meses)

Tasa de interés anual (% dividido entre 12 meses)



Resultados correctos:

Ejercicio 13 – PMT

ORIGINAR Y MODIFICAR GRÁFICAS


Al finalizar la cuarta lección los participantes:

1. Conocerán conceptos y términos relacionados a la preparación de gráficas.

2. Identificarán las partes de la gráfica.

3. Crearán/editarán diferentes tipos de gráficas.

4. Realzarán las gráficas aplicando estilos, cambiando el color representativo de las series de datos, etc.

5. Alterarán el modo de presentar los datos.

6. Crearán minigráficos (Sparklines).

Conceptos generales

1. Las gráficas se utilizan para dar un significado nuevo a los datos, para añadir un toque profesional a las hojas de cálculo, para que el trabajo hecho sea más comprensible al lector e, inclusive, para rom-per la monotonía en informes que contengan muchas cifras. Muestran comparaciones, patrones y tendencias.

2. En Excel se pueden generar una gran variedad de gráficas, a saber: de columnas (Column), de líneas (Line), circulares (Pie), de barras (Bar), de área (Area), de dispersión (Scatter), acciones (Stock), de superficie (Surface), de dona (Doughnut), de burbujas (de burbujas) y Radar.



Partes de la gráfica

Área de la gráfica

Área circundada por los ejes (plot area) de la gráfica

Valores individuales o argumentos (data points) de la serie de datos (data series) que se incluyen en la gráfica

Ejes horizontal (X - categoría) y vertical (Y - valores) a través de los cuales se muestran los datos de la gráfica

Leyenda de la gráfica

Título principal y en el eje X que pueden utilizarse en la gráfica

Etiqueta (data label) que se usa para identificar los detalles de los valores individuales de la serie de datos

Términos

1. área de la gráfica – sirve de trasfondo a la gráfica completa



2. serie de datos (data series) – grupo de valores relacionados que se incluyen en la gráfica; cada serie de datos se representa por el mismo color o patrón

3. eje horizontal o de categoría (X) – muestra las categorías que se toman de las filas o columnas de la hoja de cálculo

4. eje vertical o de valor (Y) – muestra el valor incremental en la hoja de cálculo, como por ejemplo, la cantidad de ventas

5. leyenda (legend) – identifica los patrones o colores asignados a la serie de datos o categorías mostradas en la gráfica; información relativa al contenido de la gráfica que distingue cada uno de los datos

6. etiqueta de valores (data label) – se usa para identificar los detalles de los valores individuales de la serie de datos

7. rango (data range) – serie o grupo de celdas contiguas que se formatean simultáneamente o se utilizan para crear fórmulas y gráficas

Crear la Gráfica

1. Marque la serie de datos o rango que irá en la gráfica. Incluya los títulos de columnas, ya que es la información que contendrá la leyenda.

2. En Excel 2010 la gráfica se produce como un objeto en la hoja. Pulse F11 después de marcar los datos para que la gráfica salga en una hoja separada.



3. Presione [Alt] + [F1] o dé clic en la pestaña Insert » (grupo Charts).

4. Escoja el tipo y subtipo de gráfica a base de los datos a presentar.

3. Al abrir la flecha en cada tipo puede elegir el subtipo de gráfica o diseño de gráfica.

5. Inmediatamente se producirá la gráfica como un objeto en la hoja activa, lo cual está preestablecido en la versión 2010.

6. Surgirá la cinta contextual Chart Tools con las herramientas para trabajar y editar la gráfica. Contiene, a su vez, tres pestañas: Design, Layout y Format. Al dar clic fuera de la gráfica, se oculta esta cinta. Volverá a activarse al seleccionar la gráfica nuevamente.

Mover la gráfica a otra hoja

Por configuración Excel crea la gráfica incrustada en la hoja de cálculo. Si desea colocarla separadamente, presione F11 al iniciar el proceso o haga lo siguiente:

a. Seleccione la gráfica.

b. Dé clic en la pestaña contextual Design (grupo Location) » » New sheet (bajo Choose where you want the chart to be placed).

c. Excel le asigna el nombre Chart1 a la gráfica, de ser la primera en el libro de trabajo.

d. Se le cambia el nombre como a cualquier hoja de cálculo.

Cambiar el tamaño

1. Seleccione la gráfica, coloque el cursor en cualquiera de las esquinas . el puntero cambiará a

una flecha con dos puntas .

2. Arrastre con el ratón hasta que tenga el tamaño deseado.

3. Si desea cambiar el ancho de la gráfica, coloque el puntero en cualquiera de los puntos laterales . Cuando cambie la forma del puntero , arrastre con el ratón.

Borrar la gráfica

Seleccione la gráfica y presione [Delete].



Cambiar el tipo de gráfica

1. Seleccione la gráfica y dé clic en la pestaña contextual Design (grupo Type) » .

2. Elija el tipo de gráfica que prefiera y dé clic en OK.

3. Puede experimentar con diferentes estilos hasta que quede complacido/a con el que elija.

Realzar la gráfica

1. Seleccione la gráfica y dé clic derecho (right-click) en una de las áreas en blanco.

2. Saldrá un menú del cual escogerá Format Chart Area. Si no aparece esta opción dé clic derecho (right-click) en otro lugar diferente hasta que aparezca esta opción en el menú.

3. Verá el cuadro de diálogo de esa función y podrá cambiar el color del trasfondo (Fill), cambiar el color y el estilo del borde (Border Color/Border Styles), añadir sombra (Shadow) y aplicar otros formatos 3-D (3-D Format).

4. Cuando termine de hacer los arreglos dé clic en .

Añadir etiquetas a diferentes áreas

1. Se recomienda incluir títulos descriptivos a las gráficas de modo que los usuarios no tengan que adivinar de qué trata.

2. Puede dar un título a la gráfica y también a los ejes, que miden y describen los datos que contiene. Usualmente tienen dos ejes. A la izquierda se encuentra el eje vertical (también denominado eje de valores o eje Y). Este eje es la escala de números que permite interpretar el alto de las columnas. El eje horizontal o eje X muestra las categorías que se toman de las filas o columnas de la hoja de cálculo.

3. Seleccione la gráfica y la pestaña contextual Layout. En el grupo Labels podrá modificar la gráfica de la siguiente forma:

a. Añadir título a la gráfica:

1) Con el botón tendrá tres opciones:

a) None – para no incluir título

b) Centered Overlay Title – para añadir un título sin alterar el tamaño de la gráfica



c) Above Chart – para desplegar el título en la parte superior de la gráfica, pero cambiándole el tamaño

2) Elija una de las opciones y saldrá el cuadro de texto para incluir el título. Seleccione el texto dentro del cuadro y escríbalo.

b. Agregar título a los ejes:

1) Con el botón tendrá las siguientes opciones:

a) Primary Horizontal Axis Title – para colocar el título en el eje horizontal (Title Below Axis) o ninguno

b) Primary Vertical Axis Title – para el eje vertical tendrá las siguientes opciones:

(1) None – cuando no tiene títulos

(2) Rotated Title – rota el título y altera el tamaño de la gráfica

(3) Vertical Title – muestra el texto en forma vertical y cambia el tamaño de la gráfica

(4) Horizontal Title – despliega el texto en forma horizontal y altera el tamaño de la gráfica

2) Elija una de las opciones y saldrá el cuadro de texto para que incluya el título. Seleccione el texto dentro del cuadro y cópielo.

c. Editar la leyenda:

Con el botón tendrá las siguientes opciones:

a) None – para eliminar la leyenda

b) Show Legend at Right – para colocarla a la derecha

c) Show Legend at Top – para ponerla en la parte superior

d) Show Legend at Left – para ubicarla a la izquierda

e) Show Legend at Bottom – para colocarla en la parte inferior

f) Overlay Legend at Right – para ponerla a la derecha sin alterar el tamaño de la gráfica

g) Overlay Legend at Left – para ubicarla a la izquierda sin alterar el tamaño de la gráfica

d. Añadir data labels:


a) None – para desactivar las etiquetas



b) Show – para mostrar los detalles de los valores individuales de la serie de datos en la selección

e. Incluir la tabla de datos:


a) None – para no mostrarla

b) Show Data Table – para colocar la tabla de datos debajo de la gráfica sin leyenda

c) Show Data Table with Legend Keys – para colocarla debajo de la gráfica con las claves de la leyenda

4. Otra forma de añadir títulos a la gráfica rápidamente:

a. Seleccione la gráfica y la pestaña Design

b. En el grupo de Chart Layout, dé clic en el botón para ver diferentes diseños.

c. Cada opción muestra diferentes alternativas que cambian la distribución de los elementos de la gráfica. Observe que cada ilustración agrega marcadores de posición o cuadros de texto para el título de la gráfica y de los ejes.

d. Los títulos se escriben directamente en los marcadores de posición de la gráfica después de colocar el formato.

Aplicar formato a los títulos

1. En la pestaña contextual Format, grupo WordArt Styles, hay muchas formas de trabajar con los títulos.

2. Para usar un relleno de texto:

a. Seleccione el área de título a editar.

b. Dé clic en la flecha de Text Fill , grupo WordArt Styles.

c. Sitúe el puntero encima de cualquier color para ver los cambios hasta dar con el que guste.

d. Esta opción también incluye alternativas para aplicar un color degradado o textura a un título.

3. Otras de las opciones de este grupo son Text Outline y Text Effect , los cuales incluyen sombra (Shadow), reflejo (Reflection) y resplandor (Glow).

4. Para realizar cambios en la letra (aumentar, reducir el tamaño o cambiar el estilo), dé clic en la pestaña Home y en el grupo fuente (Font). Alternativamente, pueden realizarse los mismos cambios de formato mediante la mini barra de herramientas (Mini Toolbar). La cual aparece atenuada después de seleccionar el texto del título, pero si se señala se volverá sólida y se podrá elegir una opción de formato.



Cambiar la vista de la gráfica

1. Al crear la gráfica puede hacer que los datos se muestren de forma diferente con un clic en el botón para cambiar de vista.

2. Dé clic en el botón , grupo Data, de la pestaña Design.

3. Para recuperar la vista original, vuelva a dar clic en el botón Switch Row/Column.

Nota Para mantener ambas vistas de los datos, seleccione la segunda vista de la gráfica, cópiela y péguela en la otra hoja de cálculo. Luego cambie a la vista original de la gráfica con clic en la gráfica original y otro en el botón Switch Row/Column.

Alterar el color a la gráfica

1. Seleccione una de las barras o serie de la gráfica.

2. En la pestaña Home (grupo Font) » .

3. Abrirá una muestra de diferentes colores y al final tendrá la opción More Colors para elegir colores adicionales a los mostrados.

4. Otra forma de hacerlo es seleccionando una de las barras de la gráfica y dando clic derecho (right-click) encima de ésta.

a. Marque Format Data Series en el menú que salga.

b. Verá el cuadro de diálogo de esa función y podrá cambiar el color de la serie seleccionada (Fill), cambiarle el color y el estilo del borde (Border Color/Border Styles), añadirle sombra (Shadow) y aplicarle formatos 3-D (3-D Format).

c. Cuando termine de hacer los arreglos dé clic en el botón .

5. También puede cambiar el color por medio de un estilo.

a. Dé clic en la gráfica y en la pestaña contextual Design (grupo Chart Styles) » .

b. Verá todas las opciones; dé clic en el estilo que desee.



c. Algunos estilos sólo cambian el color de las columnas, otros cambian el color y agregan un borde alrededor de las columnas, otros agregan color al área de trazado (el área limitada por los ejes de la gráfica) y otros agregan color a toda la gráfica.

6. Si en Chart Styles no hay diseños que le agraden, puede obtener otras opciones de color seleccionando un tema diferente.

a. Dé clic en la pestaña Page Layout (grupo Themes) » Colors.

b. Al colocar el puntero encima de un color, éste se muestra en la gráfica en una vista previa tem-poral, lo cual es diferente a lo que sucede cuando se observa un estilo de gráfica. Esto evita tener que deshacer la acción.

c. Dé clic en el color que desee para aplicarlo a la gráfica.

Importante Diferente a los estilos, los colores de un tema se aplican a otros elementos agregados a la hoja de cálculo. Por ejemplo; una tabla, estilo de celda o un encabezado, adoptará los colores del tema aplicado a la gráfica.

Aplicar formato a las columnas

1. Se pueden hacer más cambios en el formato de las columnas de la gráfica.

2. Dé clic en una de las columnas. De esta forma se seleccionarán todas las columnas de la serie de datos.

3. En la pestaña Format (grupo Shape Styles) » Shape Effects » Shadow.

4. Sitúe el puntero encima de los distintos estilos de sombra de la lista. A medida que coloque el puntero encima de cada estilo, obtendrá una vista previa de las sombras.

5. Repita el proceso las veces que sea necesario.

6. En Shape Styles hay más opciones para elegir. Por ejemplo, dé clic en Shape Fill para agregar un tono degradado o textura a las columnas. Dé clic en Shape Outline para agregarles contorno. Shape Effects ofrece opciones adicionales a las sombras. Por ejemplo, puede aplicar efectos de biselado y bordes redondeados a las columnas, e incluso hacer que resplandezcan.

Minigráficos (Sparklines)

1. Esta es una novedad de Excel 2010. Las Sparklines son gráficas pequeñitas que caben en una celda de una hoja de cálculo.

2. La intención de éstas es tener una muestra de tendencias o cambios que no se pueden detectar fácilmente en los datos.

3. Aunque se puede situar en cualquier celda, surten mejor efecto cuando están próximas a los datos que representan.



Tipos de minigráficos (Sparklines)

1. Minigráficos (Sparklines) de línea

a. Como cualquier gráfica de línea, ayudan a destacar cambios o tendencias en los datos por un un periodo de tiempo.

b. Son útiles para dar seguimiento o destacar datos como aumentos/disminuciones en ventas por cierto tiempo, variaciones en temperatura o precipitación por temporadas.

c. En el ejemplo que sigue se facilita comparar la fluctuación mes por mes en las ganancias en ventas de varios tipos de galletas.

2. Minigráficos (Sparklines) de columnas

a. Como cualquier gráfica de columna, se utilizan para mostrar la cantidad de veces que ocurre un valor. Un ejemplo podría ser la cantidad de ventas para diferentes regiones o los niveles de inventario para uno o más artículos.

b. Es importante hacer notar que por ser éstas tan pequeñas hasta la diferencia mínima puede lucir desproporcionada en este tipo de gráfica.

c. En el ejemplo que sigue se facilita destacar el trimestre que obtuvo las mejores ventas para cada tipo de galletas.

3. Minigráficos (Sparklines) de ganancias/pérdidas

a. Muestran si los datos son positivos, los cuales denotan ganancia, o si son negativos, lo cual refleja pérdida.

http://0.tqn.com/d/spreadsheets/1/0/W/B/-/-/sparklines1.gif

http://0.tqn.com/d/spreadsheets/1/0/R/B/-/-/sparklines2.gif



b. Ésta expone las diferencias colocando los datos positivos sobre el eje X de la gráfica (línea cero) y la negativa debajo. El eje no se muestra, pero está situado en el centro de la celda.

c. Si hay un valor de cero en los datos, es como si hubiese una celda en blanco y no habrá barra para dicha celda. Sin embargo, se notará porque existirá un espacio pequeño en blanco.

d. El ejemplo refleja aumento positivo para las acciones en 2007, 2008 y 2010. La pérdida en el 2009 se ve con la barra roja bajo el eje X.

Insertar los minigráficos (Sparklines)

1. Active la celda donde vaya la gráfica.

2. Dé clic en la pestaña Insert (grupo Sparklines) » seleccione Line, Column o Win/Loss.

3. Marque el rango que cubrirá la gráfica.

Ejercicio 14 – Gráfica de barra

Ejercicio 15 – Gráfica circular

Ejercicio 16 – Minigráficas

NOTA: Para propósitos de reforzar los conocimientos, la facilitadora podrá ajustar la cantidad de

ejercicios de práctica.

http://0.tqn.com/d/spreadsheets/1/0/P/B/-/-/sparklines3.gif

BIBLIOGRAFÍA

Aulaclic.com. Curso en Excel 2010. Disponible en: http://www.aulaclic.es/excel2010/index.htm.

Microsoft Office Online. Excel 2010 Help and How-To. Disponible en:

http://office.microsoft.com/en-us/excel-help/CL010253675.aspx?CTT=97.

Microsoft Office Online. Cursos de aprendizaje. Disponible en: http://office.microsoft.com/es-

es/excel-help/CH010369467.aspx.

In About.com: Excel 2010. Disponible en:

http://www.about.com/compute/hubsearch.htm?q=excel+2010&SUName=compute

Moctezuma, M. L. (2010). Excel 2007 al alcance de tod@s. Puerto Rico: Editorial John Louis von

Neumann Inc.

Torres Batista, N. (2010). Aplicaciones Office. Puerto Rico: Editorial John Louis von Neumann Inc.

ISBN100974829714

http://www.aulaclic.es/excel2010/index.htm

http://office.microsoft.com/en-us/excel-help/CL010253675.aspx?CTT=97

http://office.microsoft.com/es-es/excel-help/CH010369467.aspx

http://office.microsoft.com/es-es/excel-help/CH010369467.aspx

http://www.about.com/compute/hubsearch.htm?q=excel+2010&SUName=compute

microsoft® excel 2010 intermedio - prof. maría l. … · -ii-microsoft®excel 2010 intermedio...

Documents