UNIVERSIDAD TÉCNICA PARTICULAR DE LOJA

La Universidad Católica de Loja

EDUCACIÓN CONTINUA

Material Educativo

EXCEL

INTERMEDIO

1. DATOS INFORMATIVOS

ELABORADO : Ing. Jorge Cordero Zambrano

TELÉFONO : (07) 2 570275 Ext. 2652

E-MAIL : jmcordero@utpl.edu.ec

2014

1. DATOS INFORMATIVOS

EXCEL INTERMEDIO Material Educativo Elaborado por: Jorge Marcos Cordero Zambrano

Teléfono: 07 2570275 ext 2652 E-mail: jmcorderoz@gmail.com Blog: http://jmcordero.blogspot.com

UNIVERSIDAD TÉCNICA PARTICULAR DE LOJA CC Ecuador 3.0 By NC ND

Call Center: 593 - 7 - 2570275 C. P.: 11- 01- 608 www.utpl.edu.ec San Cayetano Alto s/n Loja - Ecuador Primera edición

Esta versión digital, ha sido licenciada bajo las licencias Creative Commons Ecuador 3.0 de Reconocimiento - No comercial - Sin Obras Derivadas; la cual permite copiar, distribuir y comunicar públicamente la obra, mientras se reconozca la autoría original, no se utilice con fines comerciales ni se realicen obras derivadas.

http://www.creativecommons.org/licences/by-nc-nd/3.0/ec/

Febrero, 2014

2. ÍNDICE

1. DATOS INFORMATIVOS .................................................................................................................. 1

2. ÍNDICE ...................................................................................................................................................... 3

3. INTRODUCCIÓN .................................................................................................................................. 8

4. OBJETIVOS GENERALES ............................................................................................................... 9

5. BIBLIOGRAFÍA .................................................................................................................................... 10

6. DESARROLLO DEL APRENDIZAJE ......................................................................................... 12

UNIDAD 1. Fórmulas y Funciones .................................................................................................... 12

1.1 Trabajar con fórmulas .................................................................................................................... 12

1.2 Componentes de una fórmula ................................................................................................... 13

1.2.1 Constantes ...................................................................................................................................... 13

1.2.2 Referencias a celdas .................................................................................................................. 13

1.2.3 Tipos de Operadores .................................................................................................................. 14

1.2.3.1 Operadores aritméticos:......................................................................................................... 14

1.2.3.2 Operadores de comparación: .............................................................................................. 15

1.2.3.3 Operador de concatenación de texto: .............................................................................. 15

1.2.3.4 Operadores de referencia: .................................................................................................... 16

1.2.4 Fórmulas compuestas en Excel ............................................................................................ 16

1.2.4.1 Precedencia de los operadores ......................................................................................... 16

1.2.4.2 Uso de paréntesis ................................................................................................................... 17

1.3 Referencias relativas y absolutas .............................................................................................. 18

1.3.1 Referencia relativa.- .................................................................................................................... 18

1.3.2 Referencia absoluta.- .................................................................................................................. 20

1.4 Sintaxis de una función .................................................................................................................. 21

2. ÍNDICE

1.5 Cómo escribir la función con el asistente ............................................................................... 22

1.6 Errores en los datos ........................................................................................................................ 26

1.7 Tipos de Funciones ......................................................................................................................... 27

UNIDAD 2. Funciones de FECHA, HORA y de TEXTO .......................................................... 28

2.1 Función AHORA ............................................................................................................................... 28

2.2 Función HOY ...................................................................................................................................... 28

2.3 Función DIAS.LAB ........................................................................................................................... 30

2.4 Función CONCATENAR ............................................................................................................... 31

2.5 Función LARGO ............................................................................................................................... 31

2.6 Función ENCONTRAR .................................................................................................................. 33

2.7 Función MED (EXTRAE en Excel 2007) ............................................................................... 33

2.8 Función MINUSC ............................................................................................................................. 34

2.9 Función MAYUSC ............................................................................................................................ 35

2.10 Función DECIMAL ......................................................................................................................... 35

UNIDAD 3. Funciones de Búsqueda, Referencia y Lógicas .................................................. 37

3.1 Función CONSULTAV (BUSCARV en Excel 2007) ........................................................... 37

3.2 Función SI ........................................................................................................................................... 40

3.3 Función SIERROR (SI.ERROR en Excel 2007) .................................................................. 41

3.4 Función CONTAR.SI....................................................................................................................... 42

3.5 Función Y ............................................................................................................................................ 43

3.6 Función O ............................................................................................................................................ 44

UNIDAD 4. Edición Avanzada ............................................................................................................ 45

4.1 Relación entre hojas y libros (referencias externas – vínculos) .................................... 45

4.1.1 Dónde pueden utilizarse eficazmente las referencias externas ................................ 45

4.1.2 Aspecto de una referencia externa a otro libro ................................................................ 46

4.1.3 Crear una referencia externa entre celdas de libros distintos .................................... 46

4.1.4 Controlar los vínculos a otros libros ...................................................................................... 47

4.1.5 Actualizar de forma manual todos los vínculos o no actualizar ningún vínculo .. 47

4.2 Formato de celdas ........................................................................................................................... 48

4.2.1 Número ............................................................................................................................................. 48

4.2.2 Alineación ........................................................................................................................................ 50

4.2.3 Fuente ............................................................................................................................................... 51

4.2.4 Borde ................................................................................................................................................. 52

4.2.5 Relleno .............................................................................................................................................. 52

4.2.6 Protección ........................................................................................................................................ 53

4.3 Proteger un libro de Excel con contraseña ............................................................................ 54

4.4 El formato condicional .................................................................................................................... 56

4.4.1 Agregar, cambiar o borrar formatos condicionales ......................................................... 56

4.4.2 Aplicar formato a celdas que contienen valores de texto, número, o fecha u hora ......................................................................................................................................................................... 57

4.4.3 Aplicar formato utilizando barras de datos ......................................................................... 57

4.4.4 Aplicar formato empleando una escala de dos o tres colores ................................... 58

4.4.5 Aplicar formato empleando un conjunto de iconos ......................................................... 58

4.4.6 Aplicar formato a los valores de rango inferior o superior y por encima o por debajo del promedio ............................................................................................................................... 58

4.4.7 Utilizar una fórmula que determine las celdas para aplicar formato ....................... 59

4.4.8 Borrar formatos condicionales ................................................................................................ 61

4.5 La validación de datos ................................................................................................................... 62

4.5.1 Crear una lista de entradas validas ...................................................................................... 65

4.6 Ordenar y Filtrar ................................................................................................................................ 67

4.6.1 Ordenar información ................................................................................................................... 67

4.6.2 Filtrar información ......................................................................................................................... 68

4.7 Combinar correspondencia (MS Word y MS Excel) .......................................................... 72

4.7.1 Configurar el documento principal ......................................................................................... 72

4.7.2 Conectar el documento a un origen de datos................................................................... 73

4.7.3 Guardar el documento principal ............................................................................................. 81

UNIDAD 5. Gráficos ................................................................................................................................ 82

5.1 Tipos de gráficos .............................................................................................................................. 82

5.1.1 Gráficos de columnas ................................................................................................................. 83

5.1.2 Gráficos de líneas ........................................................................................................................ 83

5.1.3 Gráficos circulares ....................................................................................................................... 83

5.1.4 Gráficos de barras ........................................................................................................................ 84

5.1.5 Gráficos de tipo XY (Dispersión) ............................................................................................ 84

5.2 Crear un gráfico ................................................................................................................................ 85

5.3 Dar formato a los gráficos ............................................................................................................. 88

5.3.1 Seleccionar un diseño de gráfico predefinido ................................................................... 88

5.3.2 Seleccionar un estilo de gráfico predefinido ...................................................................... 89

5.3.3 Seleccionar elementos de gráfico ......................................................................................... 89

5.3.4 Cambiar manualmente el diseño de elementos de gráfico ......................................... 90

5.3.5 Cambiar manualmente el estilo de elementos de gráfico ............................................ 90

5.3.6 Usar títulos en un gráfico .......................................................................................................... 90

5.3.7 Guardar un gráfico como una plantilla de gráfico ........................................................... 91

5.3.8 Cómo aplicar una plantilla de gráfico ................................................................................... 92

5.3.9 Cambiar la presentación de los ejes de gráficos ............................................................. 92

5.3.10 Mostrar u ocultar los ejes........................................................................................................ 93

5.3.11 Cambiar el tipo de gráfico de un gráfico existente ....................................................... 93

5.3.12 Trazar serie de datos de columnas o filas de hoja de cálculo ................................. 94

5.3.13 Modificar el tamaño de un gráfico ....................................................................................... 95

5.3.14 Modificar la ubicación de un gráfico ................................................................................... 95

UNIDAD 6. Tablas Dinámicas ............................................................................................................ 96

6.1 Crear un informe de tabla dinámica ......................................................................................... 96

6.2 Crear un informe de gráfico dinámico .................................................................................... 100

6.3 Aplicar filtros a una tabla dinámica ......................................................................................... 102

6.4 Eliminar una tabla dinámica. ..................................................................................................... 103

UNIDAD 7. Uso de Macros para simplificar tareas .................................................................. 106

7.1 Proceso de grabación .................................................................................................................. 106

7.2 Ejecutar una macro ....................................................................................................................... 108

7.3 Crear una macro manualmente (Uso de Visual Basic)................................................... 109

7.4 Guardar archivos con Macros ................................................................................................... 111

7.5 Ejecutar una macro haciendo clic en un área de un objeto gráfico ........................... 112

7.6 Creación de un botón para ejecutar una Macro ................................................................ 113

Excel Intermedio

8

3. INTRODUCCIÓN

Distinguido estudiante, sea bienvenido, al curso de Excel Intermedio. Este curso corresponde a una de las ofertas académicas que la unidad de Educación Continua de la UTPL brinda a quienes deseen adquirir nuevos conocimientos y alcanzar un futuro mejor. MS Excel es la hoja de cálculo más utilizada en la actualidad, permite manipular datos numéricos y alfanuméricos dispuestos en forma de tablas. Habitualmente es posible realizar cálculos complejos utilizando variedad de fórmulas y funciones, además facilita la creación de distintos tipos de gráficos de forma rápida y fácil, optimizando el trabajo. Este material educativo consta de siete unidades, empezaremos estudiando lo referente a fórmulas y funciones, aplicación de funciones de fecha y hora, aplicación de funciones de texto donde se ahonda en elementos como: sintaxis de una función, errores en los datos, tipos de funciones y ejemplos prácticos. Se profundizará en el manejo de funciones ya definidas en Excel 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 desconocemos las funciones existentes. Continuaremos trabajando con funciones de búsqueda, referencia, y lógicas donde se ilustra a través de ejemplos prácticos la ventaja de trabajar con CONSULTAV( ), además aplicar la función condicional SI( ). En las unidades finales, se trata sobre la edición avanzada, creación de gráficos, tablas dinámicas y uso de macros para simplificar tareas. A través de gráficos se 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. Utilizando filtros y tablas dinámicas se puede resumir, analizar, explorar y presentar datos resumen. Mediante los informes de gráfico dinámico podrá ver los datos de resumen contenidos en un informe de tabla dinámica para realizar comparaciones y tendencias. Con las macros podrá automatizar varias tareas repetitivas y fusionarlas en una sola, añadiendo por ejemplo un botón en nuestro libro que al pulsar sobre él realice todas esas tareas.

“Nunca consideres el estudio como una obligación, sino como una oportunidad para penetrar en el bello y maravilloso mundo del saber.”

Albert Einstein

3. INTRODUCCIÓN

Excel Intermedio

9

4. OBJETIVOS GENERALES

Utilizar Excel para diseñar hojas de trabajo que incluyan cálculos, fórmulas, funciones y gráficos estadísticos, apoyados en el desarrollo de una gran variedad de ejercicios prácticos.

Aplicar las herramientas que ofrece la hoja electrónica Excel.

Utilizar referencias absolutas y relativas.

Aplicar funciones en la resolución de problemas.

Aprender a utilizar y aplicar tablas dinámicas para obtener los más variados reportes.

Automatizar tareas con el uso y aplicación de macros.

4. OBJETIVOS GENERALES

Excel Intermedio

10

5. BIBLIOGRAFÍA

Aulaclic (2011). Curso de Excel 2010 [en línea], disponible en: http://www.aulaclic.es/excel2010/ [consulta 23-08-2011] Curso gratis y de calidad, con ejercicios, video tutoriales y evaluaciones, para aprender todo lo necesario sobre Excel 2010

Aulafacil (2011). Cursos de Excel Hojas de cálculo [en línea], disponible en: http://www.aulafacil.com/excel-2007/curso/Temario.htm [consulta 26-08-2011]. Curso gratis online, con ejercicios, para aprender Excel a través de varias lecciones.

Ayuda de MS Excel (F1) Encuentre fácilmente ayuda sobre lo nuevo, conceptos básicos, referencia de funciones, trabajar con gráficos, tablas dinámicas y macros, etc.

Cordero J. (2011) Blog personal con video tutoriales de Excel [en línea], disponible en: http://jmcordero.blogspot.com/ [consulta 27-08-2011]. Blog con varias entradas y video tutoriales para trabajar con Excel. Aquí puede reforzar el trabajo utilizando referencias relativas y absolutas a celdas, además utilizar funciones de búsqueda y referencia.

Excel Intermedio (2011) Blog Macros, Funciones de Excel, Trucos [en línea], disponible en: http://trucosexcel.blogspot.com/ [consulta 27-08-2011]. Es un Blog con diversas entradas que contienen información sobre Excel. Dispone de múltiples ejercicios especialmente para trabajar con macros.

Jelen Bill y Syrstad Tracy. (2008) Excel Macros y VBA, Madrid-España, Ediciones ANAYA Multimedia. Es un texto sencillo de fácil lectura, fue seleccionado porque contiene conceptos y ejemplos para facilitar al estudiante comprender las macros.

5. BIBLIOGRAFÍA

Excel Intermedio

11

Microsoft (2011). Ayuda y procedimientos de Excel 2010 [en línea], disponible en:

http://office.microsoft.com/es-hn/excel-help/CL010253675.aspx [consulta 25-08-2011]. Sitio oficial de soporte técnico de MS Office que presenta varios enlaces a artículos relacionados con la Validación de datos en Excel 2010.

Trucos de Excel (2011). Todo lo que siempre quisiste saber de Excel [en línea], disponible en: http://trucosdeexcel.com/ [consulta 27-09-2011]. Sitio web con ejemplos, trucos, funciones y tutoriales para profundizar sobre todo lo que siempre quiso saber de Excel.

Excel Intermedio

12

6. DESARROLLO DEL APRENDIZAJE

UNIDAD 1. Fórmulas y Funciones

“Piensa en grande y tus hechos crecerán, piensa en pequeño y quedaras atrás, piensa que puedes y podrás; todo está en el estado mental.”

Napoleón Hill Estimado Estudiante, esta primera unidad es fundamental para el desarrollo del curso, pues su comprensión y manejo es la base para una buena utilización de MS Excel. MS Excel es capaz de insertar fórmulas y funciones, realizando las operaciones que se le describan con los valores contenidos en las celdas especificadas. Vamos a profundizar en el manejo de funciones ya definidas por Excel 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 desconocemos las funciones existentes.

1.1 Trabajar con fórmulas

Las fórmulas en una hoja de cálculo son muy importantes para trabajar de manera rápida y eficaz. Para introducir una fórmula en una celda, se debe ingresar como primer carácter el signo

igual (El signo igual = le indica a MS Excel que los caracteres que le siguen

constituyen una fórmula) Por ejemplo, la fórmula: =A2*B2 Expresa que se multiplique el valor que contiene la celda A2 por el valor que contiene la celda B2. Si la celda A2 contiene el valor numérico 5, la celda B2 contiene el valor numérico 4, entonces, esta fórmula producirá 20 como resultado final, el cual será asignado a la celda en la que se ingresó la fórmula.

6. DESARROLLO DEL APRENDIZAJE

Excel Intermedio

13

1.2 Componentes de una fórmula

Una fórmula en Excel puede contener cualquiera de los siguientes elementos: constantes, referencias a celdas, operadores y funciones. Por ejemplo, la fórmula que permite calcular el área de una circunferencia, A = π R2, se puede escribir como:

=PI()*B1^2

En esta fórmula podemos identificar los elementos nombrados anteriormente: Constantes: el valor 2. Números o valores escritos directamente en una fórmula. Referencias a celdas: B1. Referencia al valor que contiene la celda B1 (Radio). Operadores: ^ y * El operador ^ (acento circunflejo) eleva un número a una potencia, y el operador * (asterisco) multiplica. Funciones: PI() Esta función al ser invocada devuelve el valor de pi: 3,141592...

1.2.1 Constantes

Una constante es un valor que no se calcula ya que el mismo representa su valor. Por ejemplo, la fecha 9-10-2011, el número 2 y el texto "Ganancias" son constantes. Si se utilizan sólo constantes en una fórmula en vez de referencias a celdas (por ejemplo, =30+70+110), el resultado cambia sólo si modifica la fórmula, por lo que no tiene sentido utilizar este tipo de fórmulas.

1.2.2 Referencias a celdas

Una referencia a celda o celdas, identifica una celda o un rango de celdas en una hoja de cálculo e indica a Excel en qué celdas debe buscar los valores o los datos que se requieren en una fórmula. De forma predeterminada, Excel utiliza el estilo de referencia A1, que se refiere a las columnas identificadas mediante letras y a las filas identificadas mediante números. Para hacer referencia a una celda, se debe escribir la letra de la columna seguida del número de fila. Por ejemplo, B2 hace referencia a la celda que se encuentra ubicada en la intersección de la columna B y la fila 2.

Excel Intermedio

14

Hace referencia a:

A8 La celda que se encuentra en la columna A y la fila 8

A5:A10 El rango de celdas de la columna A y de las filas de la 5 a la 10.

B10:E10 El rango de celdas de la fila 10 y de las columnas B a E.

A10:E20 El rango de celdas de las columnas A a E y de las filas 10 a 20.

6:6 Todas las celdas de la fila 6

5:10 Todas las celdas de las filas 5 a 10.

D:D Todas las celdas de la columna D

B:F Todas las celdas desde la columna B hasta la columna F

1.2.3 Tipos de Operadores

Los operadores especifican el tipo de operación que se desea realizar con los elementos de una fórmula. MS Excel incluye cuatro tipos diferentes de operadores: aritméticos, de comparación, texto y de referencia.

1.2.3.1 Operadores aritméticos:

Se utilizan para realizar operaciones matemáticas básicas. Se pueden utilizar los siguientes:

Operador aritmético Significado Ejemplo

+ (signo más) Suma A3+3

- (signo menos) Resta Cambio de signo

B3-B1 -A1

* (asterisco) Multiplicación B3*C3

/ (barra diagonal o slash) División D3/3

% (signo de porcentaje) Porcentaje 20%

^ (acento circunflejo) Exponenciación C3^2

Excel Intermedio

15

1.2.3.2 Operadores de comparación:

Se utilizan cuando se requiere comparar entre si dos valores y proporcionar un valor lógico (verdadero o falso) como resultado de la comparación.

Operador de comparación Significado Ejemplo

= ( igual ) Igual a A1=B1

> ( mayor ) Mayor que A1>B1

< ( menor ) Menor que A1<B1

>= ( mayor o igual ) Mayor o igual que A1>=B1

<= ( menor o igual) Menor o igual que A1<=B1

<> (distinto) Distinto de A1<>B1

1.2.3.3 Operador de concatenación de texto:

Se utiliza el signo ( & “ampersand” ) para unir o concatenar una o varias cadenas de texto con el fin de generar un solo elemento de texto.

Operador de texto Significado Ejemplo

&

("y"comercial)

Concatena o une dos valores para generar un nuevo valor de texto continuo.

A3&B3 Crea un nuevo valor de texto formado por el valor que contiene la celda A3 concatenado o unido con el valor que contiene la celda B3

Excel Intermedio

16

1.2.3.4 Operadores de referencia:

Permiten utilizar rangos de celdas, produciendo referencias a una lista de celdas, para ser utilizadas por otras operaciones en los cálculos; por ejemplo, como argumentos en las funciones.

Operador de referencia Significado Ejemplo

: (dos puntos)

Operador de rango que genera una referencia a todas las celdas

B2:B10 Referencia a todas las celdas que se encuentran en el rango B2 hasta B10

; (punto y coma)

, (coma)

Dependiendo de la configuración regional.

Operador de unión que combina varias referencias en una sola.

Separar parámetros.

B5:B15;D5:D15 Referencia a las celdas que se encuentran en el rango B5 hasta B15 más las celdas en el rango D5 hasta D15

1.2.4 Fórmulas compuestas en Excel

Una fórmula simple en Excel (tiene un solo operador), se puede representar de la forma siguiente: = A1+B1 Es frecuente necesitar fórmulas en donde se requieren dos o más operadores, lo cual implica cierta dificultad tanto para expresar correctamente la fórmula, así como también, para la evaluación de los diferentes operadores de manera que se obtenga el resultado correcto. Por ejemplo, =PI()*B1^2 Es una fórmula compuesta ya que tiene 2 operadores aritméticos ( * y ^ ).

1.2.4.1 Precedencia de los operadores

Cuando hay varias operaciones en una misma expresión, cada parte de la misma se evalúa y se resuelve en un orden determinado. Ese orden se conoce como prioridad de los operadores. Si se combinan varios operadores en una única fórmula, Excel ejecutará las operaciones en el orden correspondiente a cada operador según la tabla que se muestra a continuación.

Excel Intermedio

17

Orden Operador Descripción

1º : (dos puntos) (un solo espacio) ; (punto y coma)

Operadores de referencia

2º - Cambio de signo (como en -A10)

3º % Porcentaje

4º ^ Exponenciación

5º * y / Multiplicación y división

6º + y - Suma y resta

7º & Une dos cadenas de texto

(concatenación)

8º = < > <= >= <> Comparación

Si una fórmula contiene operadores con la misma precedencia (por ejemplo, si una fórmula contiene un operador de multiplicación y otro de división), Excel realizará primero la operación que esté más a la izquierda.

1.2.4.2 Uso de paréntesis

Para cambiar el orden de evaluación de los operadores, se debe escribir entre paréntesis la parte de la fórmula a la que se requiere cambiar el orden preestablecido, de tal forma que ésta se procese antes que las demás. Los paréntesis se deben colocar por pares, es decir, un paréntesis de apertura y otro paréntesis de cierre. Por ejemplo, la siguiente fórmula: =5+2*3 Produce como resultado 11 porque Excel calcula la multiplicación antes que la suma. La fórmula multiplica 2 por 3 y luego suma 5 al resultado. Por el contrario, si se utilizan paréntesis para cambiar el orden de evaluación, la fórmula se puede escribir como: =(5+2)*3 En este caso Excel sumará 5 más 2 y luego multiplica el resultado por 3, con lo que se obtiene 21.

Excel Intermedio

18

1.3 Referencias relativas y absolutas

En Excel cuando escribimos fórmulas y funciones, los argumentos hacen referencia a celdas o a rangos de celdas. Las referencias son enlaces al contenido de una celda, es decir, cuando en una fórmula escribimos =B1+B2 nos estamos refiriendo a que sume el contenido de B1 y el contenido de B2. Excel puede utilizar dos tipos de referencias: relativas y absolutas.

1.3.1 Referencia relativa.-

Las referencias a filas y columnas cambian si se copia la fórmula en otra celda. Esta particularidad la tienen todas las direcciones de celda. Por ejemplo: si deseamos obtener en C2 la suma de los valores de las celdas A2 y B2 se utiliza la fórmula =A2+B2

Además si usted desea seguir sumando los valores restantes de las columnas A y B, tiene que copiar o arrastrar la fórmula de la celda C2 a las celdas de la parte inferior, así:

Preste atención ha cambiado la referencia a la celda, al copiarla una fila hacia abajo en vez de fila 2 pondrá 3, resultado =A3+B3

Excel Intermedio

19

Además si copiará una columna hacia la derecha se incrementará el nombre de la columna en uno, es decir, en vez de A pondrá B, resultado =B2+C2

Excel Intermedio

20

1.3.2 Referencia absoluta.-

Las referencias de filas y columnas no cambian si se copia la fórmula a otra celda, las referencias a las celdas de la fórmula son fijas. Se aplica cuando deseamos hacer referencia a un valor constante. Cuando el cálculo de varias celdas incluye a una en particular se necesita las referencias absolutas. Por ejemplo: si deseamos asignar un porcentaje % de comisión a un conjunto de vendedores en función de lo facturado. Se debe multiplicar siempre por B1.

No se puede “arrastrar”. Si lo hacemos el número de la fila B1 aumentará, por ende no obtenemos el resultado esperado.

El uso del símbolo $ en la dirección de la celda $B$1 le indica a Excel que al “Arrastrar” esa dirección no debe cambiar.

Excel Intermedio

21

El resultado es correcto, porque no se ha cambiado la referencia a la celda donde se especifica el % de comisión. Para colocar una referencia absoluta se utiliza el signo $ al momento de escribir la fórmula, también presionado F4 cuando se tiene el cursor de inserción al lado derecho de la celda requerida.

1.4 Sintaxis de una función

Las funciones son fórmulas predefinidas en Excel que ejecutan cálculos utilizando valores específicos, denominados argumentos, y devuelven un resultado. Las funciones pueden utilizarse para ejecutar operaciones simples o complejas. La sintaxis de cualquier función es: nombre_función(argumento1;argumento2;...;argumentoN) El siguiente ejemplo ilustra la sintaxis de la función REDONDEAR.

Estructura. La estructura de una función comienza por el signo igual =, seguido por el

nombre de la función, un paréntesis de apertura, los argumentos de la función separados por signos de punto y coma y un paréntesis de cierre.

Nombre de función.

Argumentos. Los argumentos pueden ser números, texto, valores lógicos como VERDADERO o FALSO, matrices, valores de error como #N/A o referencias de celda. Los argumentos pueden ser también constantes, fórmulas u otras funciones.

Excel Intermedio

22

Información sobre herramientas de argumentos. Cuando se escribe la función, aparece una información sobre herramientas con su sintaxis y sus argumentos. Así, escriba =SUMA y aparecerá la información.

Ejemplo: =SUMA(A1:C8) 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.

1.5 Cómo escribir la función con el asistente

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

1. Situarse en la celda donde queremos introducir la función. 2. Hacer clic en la pestaña Fórmulas. Elegir la opción Insertar función.

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

Excel Intermedio

23

Para obtener una lista de funciones disponibles, haga clic en una celda y presione MAYÚS+F3.

Aparece el asistente de funciones, mostrando el cuadro de diálogo Insertar función.

Excel 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 ó seleccionar una categoría, esto hará que en el 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 Seleccionar una función: hay que elegir la función que deseamos haciendo clic sobre ésta. Se 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. Aparecerá el cuadro de diálogo Argumentos de función, donde se debe introducir los argumentos de la función: Este cuadro variará según la función que haya elegido, en nuestro caso se eligió la función SI ().

Excel Intermedio

24

Excel Intermedio

25

Realice la siguiente práctica para ejercitar sobre las referencias absolutas y relativas.

1. Ingrese a Excel.

2. Partir de la información que se muestra en la ilustración.

3. El campo Comisión se debe obtener multiplicando el % de comisión por el valor de Ventas.

4. El Total es la suma de las Ventas más la Comisión.

Actividad recomendada.

Excel Intermedio

26

1.6 Errores en los datos

Cuando introducimos una función en una celda puede ocurrir que se produzca un error. Dependiendo del tipo de error puede que Excel nos avise o no.

Cuando nos avisa del error, nos da una posible propuesta que podemos aceptar haciendo clic sobre el botón Sí o rechazar utilizando el botón No. Podemos detectar un error sin que nos avise cuando aparece la celda con un símbolo en

la esquina superior izquierda tal como esto: Al hacer clic sobre el símbolo aparecerá un cuadro que nos permitirá saber más sobre el error.

Si no sabemos qué hacer, disponemos de la opción Ayuda sobre este error. Si lo que queremos es comprobar la fórmula para saber si hay que modificarla o no podríamos utilizar la opción Modificar en la barra de fórmulas. Si la fórmula es correcta, se utilizará la opción Omitir error para que desaparezca el símbolo de la esquina de la celda. Puede que al introducir la función nos aparezca como contenido de la celda #TEXTO , siendo TEXTO un valor que puede cambiar dependiendo del tipo de error. Por ejemplo:

Excel Intermedio

27

ERROR DESCRIPCIÓN

##### Este error se produce cuando el ancho de una columna no es suficiente o cuando se utiliza una fecha o una hora negativa.

#¡DIV/0! Este error se produce cuando se divide un número por cero (0).

#¡VALOR! Este error se produce cuando se utiliza un tipo de argumento o de operando incorrecto.

#¡REF! Este error se produce cuando una referencia de celda no es válida.

#¿NOMBRE? Este error se produce cuando MS Excel no reconoce el texto de la fórmula.

También en estos casos, la celda, como en el caso anterior, contendrá además un

símbolo en la esquina superior izquierda tal como:

1.7 Tipos de Funciones

Existen muchos tipos de funciones dependiendo del tipo de operación o cálculo que realizan. Así:

Funciones por categorías

1. Funciones financieras.

2. Funciones de fecha y hora.

3. Funciones matemáticas y trigonométricas.

4. Funciones estadísticas.

5. Funciones de búsqueda y referencia.

6. Funciones de base de datos.

7. Funciones de texto.

8. Funciones lógicas.

9. Funciones de información.

10. Funciones definidas por el usuario. 11. Funciones de ingeniería.

12. Funciones de cubo.

Excel Intermedio

28

UNIDAD 2. Funciones de FECHA, HORA y de TEXTO

“No hay secretos para el éxito. Este se alcanza preparándose,

trabajando arduamente y aprendiendo del fracaso.” Colin Powell

Estimado Estudiante, continuamos con el estudio de las segunda unidad donde se estudiará el manejo de funciones de fecha, hora y de texto. La fecha y hora son datos fundamentales al momento de establecer referencias, determinando así el periodo al que pertenecen los datos. Las funciones que están disponibles en Excel tienen la finalidad de hacer más fácil su trabajo.

2.1 Función AHORA

Devuelve la fecha y hora actuales. Si el formato de celda era General antes de escribir la función, el resultado tendrá formato de fecha. Sintaxis: AHORA( ) Ejemplo

2.2 Función HOY

Devuelve la fecha actual. Si el formato de celda era General antes de escribir la función, el resultado tendrá formato de fecha. Sintaxis: HOY( ) Ejemplo

Excel Intermedio

29

Excel Intermedio

30

Las funciones AHORA() y HOY() sólo cambian cuando se actualiza la hoja de cálculo, o cuando se ejecuta una macro que contiene esas funciones. No se actualizan continuamente. La fecha y hora utilizadas se toman del reloj del sistema del equipo.

2.3 Función DIAS.LAB

Calcula el número total de días laborables entre dos fechas. Los días laborables no incluyen los fines de semana ni otras fechas que se identifiquen en el argumento festivos. Use esta función para facilitar el cálculo de pagos a empleados basándose en el número de días trabajados durante un período específico. Sintaxis DIAS.LAB(fecha_inicial;fecha_final;festivos) fecha_inicial, fecha_final son las dos fechas entre las que se desea calcular el número de días laborados. festivos, un rango opcional de una o varias fechas que deben excluirse del calendario laboral, como los días festivos nacionales y locales. Ejemplo

Excel Intermedio

31

2.4 Función CONCATENAR

Concatena (une) dos o más cadenas en una cadena de texto. Sintaxis CONCATENAR (texto1;texto2; ...) Texto1, texto2...son de 2 a 255 elementos de texto que se unirán en un elemento de texto único. Los elementos de texto pueden ser cadenas de texto, números o referencias a celdas únicas. Observaciones

También puede utilizar el símbolo (&) en lugar de la función CONCATENAR para unir elementos de texto. Por ejemplo =A1&B1 devuelve el mismo valor que =CONCATENAR(A1;B1).

Ejemplo

2.5 Función LARGO

La función LARGO devuelve el número de caracteres de una cadena de texto. Sintaxis LARGO(texto) Texto es el texto cuya longitud se desea obtener. Los espacios se cuentan como caracteres. Ejemplo

Excel Intermedio

32

Excel Intermedio

33

2.6 Función ENCONTRAR

La función ENCONTRAR busca una cadena de texto dentro de una segunda cadena y devuelven el número de la posición inicial de la primera cadena de texto a partir del primer carácter de la segunda cadena de texto. Sintaxis ENCONTRAR(texto_buscado;dentro_del_texto;núm_inicial) Texto_buscado es el texto que desea encontrar. Dentro_del_texto es el texto que a su vez contiene el texto que desea encontrar. Núm_inicial especifica el carácter a partir del cual comenzará la búsqueda. El primer carácter de dentro_del_texto es el carácter número 1. Si omite núm_inicial, se supone que es 1. Observaciones

La función ENCONTRAR distingue entre mayúsculas y minúsculas y no permite el uso de caracteres comodín.

Si texto_buscado es "" (texto vacío), coincide con el primer carácter de la cadena de búsqueda (es decir, el carácter de núm_inicial o 1).

Texto_buscado no puede contener ningún carácter comodín. Si texto_buscado no aparece en dentro_del_texto, devuelve un error #¡VALOR! Si núm_inicial no es mayor que cero, o es mayor que la longitud del texto

devuelve un error #¡VALOR!. Utilice núm_inicial para omitir un número específico de caracteres.

Ejemplo

2.7 Función MED (EXTRAE en Excel 2007)

Devuelve un número específico de caracteres de una cadena de texto, comenzando en la posición que especifique y en función del número de caracteres que especifique. Sintaxis MED(texto;posición_inicial;núm_de_caracteres) Texto Cadena de texto que contiene los caracteres que se desea extraer.

Excel Intermedio

34

Posición_inicial Posición del primer carácter que se desea extraer del texto. La posición_inicial del primer carácter de texto es 1, y así sucesivamente. Núm_de_caracteres Especifica el número de caracteres que se desea que MED devuelva del argumento texto. Observaciones

Si posición_inicial es mayor que la longitud de texto, MED devuelve "" (texto vacío).

Si posición_inicial es menor que la longitud de texto, pero posición_inicial más núm_de_caracteres excede la longitud de texto, MED devuelve los caracteres hasta el final de texto.

Si posición_inicial es menor que 1, MED devuelve el valor de error #¡VALOR!. Ejemplo

2.8 Función MINUSC

Convierte todas las mayúsculas de una cadena de texto en minúsculas. Sintaxis MINUSC(texto) Texto es el texto que se desea convertir en minúsculas. MINUSC no cambia los caracteres de texto que no sean letras. Ejemplo

Excel Intermedio

35

2.9 Función MAYUSC

Pone el texto en mayúsculas. Sintaxis MAYUSC(texto) Texto es el texto que se desea pasar a mayúsculas. El argumento texto puede ser una referencia o una cadena de texto. Ejemplo

2.10 Función DECIMAL

Redondea un número al número de decimales especificado, da formato al número con el formato decimal usando comas y puntos, y devuelve el resultado como texto. Sintaxis DECIMAL(número;decimales;no_separar_millares) Número es el número que desea redondear y convertir en texto. Decimales es el número de dígitos a la derecha del separador decimal. No_separar_millares es un valor lógico que, si es VERDADERO, impide que DECIMAL incluya un separador de millares en el texto devuelto. Ejemplo

Excel Intermedio

36

Realice la siguiente práctica para ejercitar sobre la función CONCATENAR

1. Ingrese a Excel.

2. Partir de la información que se muestra en la ilustración.

3. En el campo Apellido y Nombre obtener en base a la información de las columnas B y A

Actividad recomendada.

Excel Intermedio

37

UNIDAD 3. Funciones de Búsqueda, Referencia y Lógicas

“Solo hay dos cosas que podemos perder: el tiempo y la vida la segunda es inevitable, la primera imperdonable.”

José María Franco Distinguido Estudiante, Excel cuenta con un gran potencial para trabajar con funciones de búsqueda y referencia, debido a la facultad de éste para manipular nombres y rangos. Excel permite utilizar funciones lógicas que ayudan a evaluar una condición y en base al resultado realizar determinada acción.

3.1 Función CONSULTAV (BUSCARV en Excel 2007)

Busca un valor específico en la primera columna de una matriz de tabla y devuelve, en la misma fila, un valor de otra columna de dicha matriz de tabla. La V de CONSULTAV significa vertical. Sintaxis CONSULTAV(valor_buscado;matriz_buscar_en;indicador_columnas;ordenado) Valor_buscado Valor que se va a buscar en la primera columna de la matriz de tabla. Valor_buscado puede ser un valor o una referencia. Si valor_buscado es inferior al menor de los valores de la primera columna de matriz_buscar_en, CONSULTAV devuelve al valor de error #N/A. Matriz_buscar_en Dos o más columnas de datos. Use una referencia a un rango o un nombre de rango. Los valores de la primera columna de matriz_buscar_en son los valores que busca valor_buscado. Indicador_columnas Número de columna de matriz_buscar_en desde la cual debe devolverse el valor coincidente. Si el argumento indicador_columnas es igual a 1, la función devuelve el valor de la primera columna del argumento matriz_buscar_en; si el argumento indicador_columnas es igual a 2, devuelve el valor de la segunda columna de matriz_buscar_en y así sucesivamente. Si indicador_columnas es:

Si es inferior a 1, devuelve un error #VALUE! Si es superior al número de columnas de matriz_buscar_en, devuelve error #REF!

Ordenado Valor lógico que especifica si CONSULTAV va a buscar una coincidencia exacta o aproximada:

Excel Intermedio

38

Si se omite o es VERDADERO, se devolverá una coincidencia exacta o aproximada. Si no localiza ninguna coincidencia exacta, devolverá el siguiente valor más alto inferior a valor_buscado.

Si es FALSO, CONSULTAV sólo buscará una coincidencia exacta. Si no se encuentra una coincidencia exacta, se devolverá un error #N/A.

Ejemplo

Realice una práctica para ejercitar sobre las funciones de búsqueda y referencia.

1. Ingrese a Excel 2. Partir de la información que se muestra en la ilustración.

3. En el campo Ingrese código, debe ingresar un valor entre (100 y 102)

Actividad recomendada.

Excel Intermedio

39

4. En base al código ingresado obtener la Descripción y el Precio. Utilice la función correspondiente

Excel Intermedio

40

3.2 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 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. Por ejemplo, A10=100 es una expresión lógica; si el valor de la celda A10 es igual a 100, la expresión se evalúa como VERDADERO. De lo contrario, se evaluará como FALSO. Este argumento puede utilizar cualquier operador de comparación (=, <>, <, <=, >, >=).

Operadores de Comparación

Igual a =

Mayor que >

Menos que <

Mayor o Igual que >=

Menor o Igual que <=

Distinto <>

Valor_si_verdadero es el valor que se devuelve si el argumento prueba_lógica es VERDADERO. Por ejemplo, si este argumento es la cadena de texto "Dentro de presupuesto" y el argumento prueba_lógica se evalúa como VERDADERO, la función SI muestra el texto "Dentro de presupuesto". Si el argumento prueba_lógica es VERDADERO y el argumento valor_si_verdadero está en blanco, este argumento devuelve 0 (cero). Valor_si_verdadero puede ser otra fórmula. Valor_si_falso es el valor que se devuelve si el argumento prueba_lógica es FALSO. Por ejemplo, si este argumento es la cadena de texto "Presupuesto excedido" y el argumento prueba_lógica se evalúa como FALSO, la función SI muestra el texto "Presupuesto excedido". Valor_si_falso puede ser otra fórmula. Observaciones

Es posible anidar hasta 64 funciones SI como argumentos.

MS Excel proporciona funciones adicionales que se pueden utilizar para analizar los datos en función de una condición. Por ejemplo, para contar el número de veces que una cadena de texto o un número aparecen dentro de un rango de celdas, utilice las funciones de hoja de cálculo CONTAR.SI. Para calcular una suma basándose en una cadena de texto o un número de un rango, utilice las funciones SUMAR.SI.

Excel Intermedio

41

Ejemplo 1

Ejemplo 2

3.3 Función SIERROR (SI.ERROR en Excel 2007)

Devuelve un valor que se especifica si una fórmula se evalúa como un error; de lo contrario, devuelve el resultado de la fórmula. Utilice la función SIERROR para interceptar y controlar errores en una fórmula.

Sintaxis SIERROR(valor; valor_si_error)

Valor el argumento en el que se busca un error.

Valor_si_error el valor que se devuelve si la fórmula se evalúa como un error. Se evalúan los tipos siguientes de error: #N/A, #¡VALOR!, #¡REF!, #¡DIV/0!, #¡NUM!, #¿NOMBRE? o #¡NULO!..

Observaciones

Si valor o valor_si_error están en una celda vacía, SIERROR los trata como un valor de cadena vacía ("").

Ejemplo:

Excel Intermedio

42

3.4 Función CONTAR.SI

Esta función cuenta el número de celdas dentro de un rango que cumplen con un solo criterio especificado por el usuario. La función CONTAR.SI se encuentra disponible, dentro de la categoría de fórmulas Estadísticas. Sintaxis CONTAR.SI(rango; criterios)

Rango Una o más celdas que se van a contar, incluidos números o nombres, matrices o referencias que contengan números. Los valores en blanco y los de texto no se tienen en cuenta.

Criterios Número, expresión, referencia de celda o cadena de texto que determina las celdas que se van a contar.

Observaciones

En los criterios se puede utilizar los caracteres comodín — signo de interrogación (?) y asterisco (*) —. El signo de interrogación corresponde a un solo carácter cualquiera y el asterisco equivale a cualquier secuencia de caracteres. Si desea buscar un signo de interrogación o un asterisco reales, escriba una tilde (~) delante del carácter que desea buscar.

Los criterios no distinguen entre mayúsculas y minúsculas.

Ejemplo 1: Sin empleo de operadores de comparación.

Contar todas los estudiantes que están APROBADOS, además contar todos los estudiantes que están REPROBADOS

Excel Intermedio

43

Ejemplo 2: Con empleo de operadores de comparación.

Contar según rango notas FINAL y en base a CONDICIÓN

3.5 Función 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 255 condiciones que se desea comprobar y que pueden tener el resultado de VERDADERO o FALSO. Observaciones

Los argumentos deben evaluarse como valores lógicos, como VERDADERO o FALSO, o referencias que contengan valores lógicos.

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

Ejemplo

Excel Intermedio

44

3.6 Función O

Devolverá VERDADERO si alguno de los argumentos es VERDADERO; devolverá FALSO si todos los argumentos son FALSO. Sintaxis O(valor_lógico1;valor_lógico2; ...) Valor_lógico1, Valor_lógico2, ... son de 1 a 255 condiciones que se desea comprobar y que pueden tener el resultado de VERDADERO o FALSO. Observaciones

Los argumentos deben evaluarse como valores lógicos, como VERDADERO O FALSO, o referencias que contengan valores lógicos.

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

Ejemplo

Excel Intermedio

45

UNIDAD 4. Edición Avanzada

“Las personas que corren riesgos cambian el mundo. Pocas personas se vuelven ricas sin asumir riesgos.”

Robert T. Kiyosaki En esta unidad estudiaremos algunas características avanzadas de Excel que nos pueden ser de utilidad cuando queramos realizar algún trabajo un poco más profesional. Veremos como el formato condicional ayuda a facilitar el proceso de resaltar celdas o rangos de celdas interesantes, de destacar valores inusuales y de ver datos empleando barras de datos, escalas de colores y conjunto de iconos. Un formato condicional cambia el aspecto de un rango de celdas en función de una condición.

4.1 Relación entre hojas y libros (referencias externas – vínculos)

Una referencia externa (también denominada vínculo) es una referencia al contenido de una celda o a un rango de celdas de otra hoja de cálculo o de otro libro de Excel. Puede crear referencias externas entre los libros para traer información de un libro de origen (proporciona los datos) a un libro de destino (creo la referencia).

4.1.1 Dónde pueden utilizarse eficazmente las referencias externas

Las referencias externas son especialmente útiles cuando no resulta práctico mantener juntos en el mismo libro los modelos de hoja de cálculo grandes.

Combinar datos de varios libros Puede vincular libros de varios usuarios o departamentos e integrar los datos pertinentes en un libro de resumen.

Simplificar modelos grandes y complejos Al dividir un modelo complicado en una serie de libros interdependientes, podrá trabajar en el modelo sin necesidad de abrir todas las hojas relacionadas con él. Los libros de menor tamaño son más fáciles de modificar, no necesitan mucha memoria y se pueden abrir, guardar y calcular con más rapidez.

Excel Intermedio

46

4.1.2 Aspecto de una referencia externa a otro libro

Las fórmulas con referencias externas a otros libros se muestran de dos maneras, según el libro de origen, se encuentre abierto o cerrado. Cuando el libro de origen está abierto, la referencia externa incluye el nombre del libro entre corchetes, seguido del nombre de la hoja de cálculo, un signo de exclamación (!) y las celdas de las que depende la fórmula. Por ejemplo, la siguiente fórmula suma las celdas C10:C25 del libro Presupuesto.xls.

Referencia externa

=SUMA([Presupuesto.xlsx]Anual!C10:C25)

Si el libro de origen está cerrado, la referencia externa incluye toda la ruta de acceso.

Referencia externa

=SUMA('C:\Informes\[Presupuesto.xlsx]Anual'!C10:C25)

4.1.3 Crear una referencia externa entre celdas de libros distintos

1. Abra el libro que contendrá la referencia externa (el libro de destino) y el libro que contiene los datos con los que desea establecer el vínculo (el libro de origen).

2. En el libro de origen, haga clic en Guardar en la barra de herramientas de acceso rápido.

3. Seleccione en el libro destino la celda en las que desea crear la referencia externa.

4. Escriba = (signo de igual). Si desea ejecutar cálculos o funciones en el valor de la referencia externa, escriba el operador o la función que debe preceder a la referencia externa.

5. Cambie al libro de origen y, a continuación, haga clic en la hoja de cálculo que contiene las celdas con las que desea establecer el vínculo.

6. Seleccione la celda o las celdas con las que desee establecer el vínculo.

7. Vuelva al libro de destino y observe que Excel agrega la referencia al libro de origen y a las celdas que seleccionó en el paso anterior.

8. También puede modificar o cambiar la fórmula en la hoja de cálculo de destino.

Excel Intermedio

47

4.1.4 Controlar los vínculos a otros libros

Cuando el libro de origen y el libro de destino están abiertos en el mismo equipo, los vínculos se actualizan de forma automática. Si el libro de origen no está abierto en el momento de abrir el libro de destino, la barra de confianza puede preguntarle si desea actualizar los vínculos.

4.1.5 Actualizar de forma manual todos los vínculos o no actualizar ningún vínculo

1. Cierre todos los libros. Si un libro de origen se queda abierto y los demás se cierran, las actualizaciones no serán uniformes.

2. Abra el libro que contiene los vínculos.

3. Para actualizar los vínculos, en la barra de confianza, haga clic en Opciones y, a continuación, en Habilitar este contenido.

Excel Intermedio

48

4.2 Formato de celdas

Excel no solo permite realizar cálculos sino que también permite darle una buena presentación a las hojas de cálculo resaltando la información más interesante, de esta forma con un solo vistazo podremos percibir la información más importante y así sacar conclusiones de forma rápida y eficiente. Por ejemplo podemos llevar la cuenta de todos nuestros gastos y nuestras ganancias del año y resaltar en color rojo las pérdidas y en color verde las ganancias, de esta forma sabremos rápidamente si el año ha ido bien o mal. Veremos las diferentes opciones disponibles en Excel respecto al cambio de aspecto de las celdas de una hoja de cálculo y cómo manejarlas para modificar el tipo y aspecto de la letra, la alineación, bordes, sombreados y forma de visualizar números en la celda. Utilice el cuadro de diálogo Formato de celdas para aplicar formato al contenido de las celdas seleccionadas.

4.2.1 Número

Excel permite modificar la visualización de los números en la celda. En la ficha Inicio, en el grupo Número, disponemos de una serie de opciones que nos permitirá modificar el formato de los números en una forma más rápida. Otra opción es utilizar el cuadro de diálogo Formato de celdas

Las categorías más utilizadas son: General: Visualiza en la celda exactamente el valor introducido. Es el formato que utiliza Excel por defecto. Número: Contiene una serie de opciones que permiten especificar el número de decimales, también permite especificar el separador de millares y la forma de visualizar los números negativos. Moneda: Es parecido a la categoría Número, permite especificar el número de decimales, se puede escoger el símbolo monetario como

Excel Intermedio

49

podría ser € y la forma de visualizar los números negativos. Contabilidad: Difiere del formato moneda en que alinea los símbolos de moneda y las comas decimales en una columna. Fecha: Contiene números que representan fechas y horas como valores de fecha. Puede escogerse entre diferentes formatos de fecha. Hora: Contiene números que representan valores de horas. Puede escogerse entre diferentes formatos de hora. Porcentaje: Visualiza los números como porcentajes. Se multiplica el valor de la celda por 100 y se le asigna el símbolo %, por ejemplo, un formato de porcentaje sin decimales muestra 0,1528 como 15%, y con 2 decimales lo mostraría como 15,28%. Fracción: Permite escoger entre nueve formatos de fracción. Científica: Muestra el valor de la celda en formato de coma flotante. Podemos escoger el número de decimales. Texto: Las celdas con formato de texto son tratadas como texto incluso si en el texto se encuentre algún número en la celda. Especial: Contiene algunos formatos especiales, como puedan ser el código postal, el número de teléfono, etc. Personalizada: Aquí podemos crear un nuevo formato, indicando el código de formato.

Excel Intermedio

50

4.2.2 Alineación

Se puede asignar formato a las entradas de las celdas a fin de que los datos queden alineados u orientados de una forma determinada.

En la ficha Inicio, en el grupo Alineación, disponemos de una serie de opciones que nos permitirá alinear los datos de una hoja de cálculo en una forma más rápida. Otra opción es utilizar el cuadro de diálogo Formato de celdas

Alineación del texto Horizontal.- permite cambiar la alineación horizontal del contenido de la celda. De forma predeterminada, MS Excel alinea el texto a la izquierda, los números a la derecha y se centran los valores de errores y lógicos. La alineación horizontal predeterminada es General. El cambio de la alineación de datos no cambia el tipo de datos. Vertical.- permite cambiar la alineación vertical del contenido de la celda. De forma predeterminada, Excel alinea el texto verticalmente en la parte inferior de una celda. Sangría.- aplica sangría al contenido

de una celda desde cualquier borde de la misma, en función de si elige Horizontal y Vertical. Cada incremento del cuadro Sangría es equivalente al ancho de un carácter. Orientación.- permite cambiar la orientación del texto en las celdas seleccionadas. Es posible que las opciones de giro no estén disponibles si se seleccionan otras opciones de alineación. Grados.- establece la cantidad de giro del texto en la celda seleccionada. Utilice un número positivo para girar el texto seleccionado desde la esquina inferior izquierda a la esquina superior derecha de la celda. Utilice grados negativos para girar el texto desde la esquina superior izquierda a la esquina inferior derecha en la celda seleccionada. Control del texto Ajustar texto.- ajusta el texto en varias líneas en una celda. El número de líneas ajustadas dependerá del ancho de la columna y de la longitud del contenido de la celda.

Excel Intermedio

51

Reducir hasta ajustar.- reduce el tamaño aparente de los caracteres de fuente de forma que se ajusten todos los datos de una celda seleccionada a la columna. El tamaño de carácter se ajusta automáticamente al cambiar el ancho de la columna. No cambia el tamaño de la fuente aplicada. Combinar celdas.- combina dos o más celdas seleccionadas en una sola celda. La referencia de celda para una celda combinada es la celda de la esquina superior izquierda del rango original seleccionado. De derecha a izquierda Dirección del texto.- seleccione una opción en el cuadro Texto para especificar el orden de lectura y la alineación. La configuración predeterminada es Contexto pero puede cambiarla a De izquierda a derecha o De derecha a izquierda.

4.2.3 Fuente

Excel nos permite cambiar la apariencia de los datos de una hoja de cálculo cambiando la fuente, el tamaño, estilo y color de los datos de una celda.

En la ficha Inicio, en el grupo Fuente, disponemos de una serie de opciones que nos permitirá modificar la apariencia de los datos de una hoja en una forma más rápida. Otra opción es utilizar el cuadro de diálogo Formato de celdas

Fuente.- Seleccione el tipo de fuente para el texto de las celdas seleccionadas. Estilo de fuente.- Seleccione el estilo de fuente para el texto en las celdas seleccionadas. Tamaño.- Seleccione el tamaño de fuente para el texto de las celdas seleccionadas. Puede escribir cualquier número entre 1 y 1638. Subrayado.- Seleccione el tipo de subrayado que desea utilizar para el texto de las celdas seleccionadas. Color.- Seleccione el color que desea

utilizar para texto o celdas seleccionadas.

Excel Intermedio

52

Fuente normal.- Active la casilla de verificación Fuente normal para restablecer la fuente, el estilo de fuente, el tamaño y los efectos al estilo Normal (predeterminado). Efectos.- Le permite seleccionar uno de los siguientes efectos de formato.

Tachado.- Active esta casilla de verificación para que aparezca el texto en las celdas seleccionadas como tachado.

Superíndice.- Active esta casilla de verificación para que aparezca el texto en las celdas seleccionadas como superíndice.

Subíndice.- Active esta casilla de verificación para que aparezca el texto en las celdas seleccionadas como subíndice.

Vista previa.- Vea un ejemplo de texto que aparece con las opciones de formato que selecciona.

4.2.4 Borde

Excel nos permite crear líneas en los bordes o lados de las celdas. Línea.- Seleccione una opción en Estilo para especificar el estilo y el tamaño de la línea para un borde. Si desea cambiar un estilo de línea en un borde que ya existe, seleccione la opción de estilo de línea que desea y, a continuación, haga clic en el área del borde en el modelo Borde donde desea que aparezca el nuevo estilo de línea. Preestablecidas.- Seleccione una opción de borde predefinida para aplicar bordes o quitarlos de las celdas seleccionadas. Color.- Seleccione un color en la lista para cambiar el color de las celdas seleccionadas.

Borde.- Haga clic en un estilo de línea en el cuadro Estilo y después haga clic en los botones bajo Preestablecidas o Borde para aplicar bordes a las celdas seleccionadas. Para quitar todos los bordes, haga clic en el botón Ninguno. También puede hacer clic en las áreas del cuadro de texto para agregar o quitar bordes.

4.2.5 Relleno

Excel Intermedio

53

Excel nos permite también sombrear las celdas de una hoja de cálculo para remarcarlas de las demás.

Color de fondo.- Seleccione un color de fondo para las celdas seleccionadas utilizando la paleta de colores. Efectos de relleno.- Seleccione este botón para aplicar degradado, textura y rellenos de imagen en las celdas seleccionadas. Más colores.- Seleccione este botón para agregar colores que no están disponibles en la paleta de colores. Color de Trama.- Seleccione un color de primer plano para crear una trama que utilice dos colores. Estilo de Trama.- Seleccione una

trama para aplicar formato a las celdas seleccionadas con una trama que utiliza los colores seleccionados en los cuadros Color de fondo y Color de Trama. Muestra.- Vea una muestra de las opciones de trama, color y efectos de relleno que selecciona.

4.2.6 Protección

Bloqueado.- Evita que las celdas seleccionadas se cambien, muevan, cambien de tamaño o se eliminen. El bloqueo de celdas no tiene ningún efecto a menos que la hoja esté protegida. Oculta.- Oculta una fórmula de una celda para que no aparezca en la barra de fórmulas cuando la celda está seleccionada. Si selecciona esta opción, no tiene efecto a menos que la hoja esté protegida.

Excel Intermedio

54

¿Cómo proteger una hoja?

En la ficha Revisar, en el grupo Cambios, haga clic en Proteger hoja. Asegúrese de que la casilla de verificación Proteger hoja y contenido de celdas bloqueadas está activada. En el cuadro Permitir a los usuarios de esta hoja de cálculo, seleccione las actividades que permite que los usuarios ejecuten en una hoja protegida que contiene celdas bloqueadas. Si queremos asignar una contraseña para que solamente pueda desproteger la hoja la persona que sepa la contraseña, escribirla en el recuadro Contraseña.

4.3 Proteger un libro de Excel con contraseña

En ocasiones, por diversos motivos, se requiere de cierto nivel de seguridad en los archivos que escribimos y usamos.

En este caso vamos a describir la forma en que podemos bloquear el acceso o la edición sobre archivos de Excel.

1. En el menú Archivo, haga clic en Guardar como.

2. En el menú Herramientas, haga clic en Opciones generales.

Excel Intermedio

55

3. Siga uno de estos procedimientos o ambos:

Si desea que los usuarios escriban una contraseña antes de poder ver el libro, escriba una contraseña en el cuadro Contraseña de apertura y haga clic en Aceptar.

Si desea que los usuarios escriban una

contraseña antes de poder guardar los cambios en el libro, inserte una contraseña en el cuadro Contraseña

de escritura y haga clic en Aceptar.

4. Cuando se solicite, vuelva a escribir las contraseñas para confirmarlas.

5. Haga clic en Guardar. 6. Si se le pide, haga clic en Sí para reemplazar el libro existente.

Excel Intermedio

56

Si no se especifica la contraseña adecuada no se podrá abrir el libro y por lo tanto no se podrá ver el contenido del archivo.

Cuando se desee abrir el documento se mostrará una ventana en la que se debe

introducir la contraseña correcta.

Para remover este tipo de protección debemos seguir los mismos pasos anteriores y en esta ocasión será suficiente con remover la contraseña para quitar la protección del libro.

4.4 El formato condicional

4.4.1 Agregar, cambiar o borrar formatos condicionales

El formato condicional sirve para que dependiendo del valor de la celda, Excel aplique un formato especial o no sobre esa celda. El formato condicional suele utilizarse para explorar y analizar datos visualmente, resaltar errores y para valores que cumplan una determinada condición, para resaltar las celdas según el valor contenido en ella. Cuando se analizan datos, es frecuente que surjan preguntas como:

¿Quién ha vendido más de 50.000 dólares este mes? ¿Cuál es la distribución de antigüedad general de los empleados? ¿De qué productos han aumentado los ingresos más del 10% de año en año? ¿Cuáles son los estudiantes con mejores resultados y cuáles los de peores

resultados? El formato condicional ayuda a responder estas preguntas porque facilita el proceso de resaltar celdas o rangos de celdas, de destacar valores inusuales y de ver datos empleando barras de datos, escalas de colores y conjuntos de iconos. Un formato condicional cambia el aspecto de un rango de celdas en función de una condición (o criterio). Si la condición es verdadera, el rango de celdas basa el formato en

Excel Intermedio

57

dicha condición; si la condición es falsa, el rango de celdas no tiene formato basado en dicha condición.

4.4.2 Aplicar formato a celdas que contienen valores de texto, número, o fecha u hora

Para encontrar más fácilmente celdas específicas dentro de un rango de celdas, puede aplicar formato a dichas celdas específicas basándose en un operador de comparación. Por ejemplo, en una hoja de cálculo de inventario clasificada por categorías, puede resaltar los productos con menos de 10 artículos disponibles en amarillo.

1. Seleccione un rango de celdas o asegúrese de que la celda activa está en una tabla o en un informe de tabla dinámica. 2. En la ficha Inicio, en el grupo Estilo, haga clic en la flecha junto a Formato condicional y, a continuación, haga clic en Resaltar reglas de celdas. 3. Seleccione el comando que desea, como Entre, Igual a texto que contiene o Una fecha. 4. Escriba los valores que desee utilizar y, a continuación, seleccione un formato.

4.4.3 Aplicar formato utilizando barras de datos

Una barra de datos le ayuda a ver el valor de una celda con relación a las demás. La longitud de la barra de datos representa el valor de la celda. Una barra más grande representa un valor más alto y una barra más corta representa un valor más bajo.

1. Seleccione un rango de celdas. 2. En la ficha Inicio, en el grupo Estilo, haga clic en la flecha junto a Formato condicional, haga clic en Barras de datos y, a continuación, seleccione un icono de la barra de datos.

Excel Intermedio

58

4.4.4 Aplicar formato empleando una escala de dos o tres colores

Las escalas de colores son guías visuales que ayudan a comprender la variación y la distribución de datos. Una escala de dos o tres colores permite comparar un rango de celdas utilizando una gradación de dos o tres colores. El tono de color representa los valores superiores o inferiores.

1. Seleccione un rango de celdas o asegúrese de que la celda activa está en una tabla o en un informe de tabla dinámica. 2. En la ficha Inicio, en el grupo Estilos, haga clic en la flecha situada junto a Formato

condicional y, después, en Escalas de color. 3. Seleccione una escala de dos colores.

4.4.5 Aplicar formato empleando un conjunto de iconos

Utilice un conjunto de iconos para comentar y clasificar datos de tres a cinco categorías. Cada icono representa un rango de valores. Por ejemplo, en el conjunto de iconos de 3 flechas, la flecha roja hacia arriba representa valores más altos, la flecha hacia el lado amarilla representa valores medios y la flecha hacia abajo verde representa valores más bajos.

1. Seleccione un rango de celdas o asegúrese de que la celda activa está en una tabla o en un informe de tabla dinámica. 2. En la ficha Inicio, en el grupo Estilo, haga clic en la flecha junto a Formato condicional, haga clic en Conjunto de iconos y, a continuación, seleccione un conjunto de iconos.

4.4.6 Aplicar formato a los valores de rango inferior o superior y por encima o por debajo del promedio

Puede buscar los valores más altos y más bajos en un rango de celdas según un valor de corte que especifique. Puede buscar valores por encima o por debajo del promedio o desviación estándar en un rango de celdas.

Excel Intermedio

59

Por ejemplo, puede buscar los 5 productos más vendidos en un informe regional, el 15% de los productos del final de una encuesta al cliente o los 15 mejores salarios de un análisis de personal de departamento.

1. Seleccione un rango de celdas. 2. En la ficha Inicio, en el grupo Estilo, haga clic en la flecha junto a Formato condicional y, a continuación, haga clic en Reglas superiores e inferiores. 3. Seleccione el comando que desee, como 10 elementos superiores o 10% inferiores. 4. Escriba los valores que desee utilizar y, a continuación, seleccione un formato.

4.4.7 Utilizar una fórmula que determine las celdas para aplicar formato

Si el formato condicional tiene que ser más complejo, puede utilizar una fórmula lógica para especificar los criterios de formato. Por ejemplo, puede que desee comparar valores con un resultado devuelto por una función o evaluar datos de celdas que se encuentran fuera del rango seleccionado. 1. Seleccione un rango de celdas. 2. En la ficha Inicio, en el grupo Estilos, haga clic en la flecha situada junto a Formato

condicional y, después, en Administrar reglas. Se mostrará el cuadro de diálogo Administrador de reglas de formato condicionales.

Excel Intermedio

60

3. Para agregar un formato condicional, haga clic en Nueva regla. Aparecerá el cuadro de diálogo Nueva regla de formato.

4. En Seleccionar un tipo de regla, haga clic en Utilice una fórmula que determine las celdas para aplicar formato.

5. En Editar una descripción de regla, en el cuadro de lista Dar formato a los valores donde esta fórmula sea verdadera, escriba las fórmula.

6. Haga clic en Formato para mostrar el cuadro de diálogo Formato de celdas.

7. Seleccione el número, fuente, borde o formato de relleno que desea aplicar cuando el valor de la celda cumple la condición y, a continuación, haga clic en Aceptar. Puede elegir más de un formato. Los formatos que selecciona aparecen en el cuadro Vista previa.

Excel Intermedio

61

4.4.8 Borrar formatos condicionales

Siga uno de los procedimientos siguientes:

1. En la ficha Inicio, en el grupo Estilos, haga clic en la flecha situada junto a Formato condicional y, después, en Borrar reglas. 2. Haga clic en Borrar reglas de toda la hoja.

Excel Intermedio

62

4.5 La validación de datos

La validación de datos es muy similar al formato condicional, salvo que esta característica tiene una función muy concreta y es validar el contenido de una celda; pudiéndose incluso mostrar un mensaje de error o aviso si llegara el caso. En muchas hojas de cálculo, los usuarios escribirán datos para obtener los resultados y cálculos que desee. Asegurar la entrada válida de datos es una tarea importante. Puede que desee restringir la entrada de datos a un rango determinado de fechas, limitar las opciones empleando una lista o asegurarse de que sólo se entran números enteros positivos. También es esencial proporcionar una ayuda inmediata para dar instrucciones a los usuarios y mensajes claros cuando se escriban datos no válidos para hacer que el proceso de entrada de datos se lleve a cabo sin problemas. Para aplicar una validación:

1. Seleccione una o más celdas para validar.

2. En la ficha Datos, en el grupo Herramientas de datos, haga clic en Validación de datos.

3. Se muestra el cuadro de diálogo Validación de datos.

4. Haga clic en la ficha Configuración.

Excel Intermedio

63

5. En la sección Criterio de validación indicamos la condición para que los datos

sean correctos.

Dentro de Permitir podemos encontrar Cualquier valor, Número entero, Decimal, Lista, Fecha, Hora, Longitud del texto y Personalizada. Por ejemplo si elegimos Número entero, Excel sólo permitirá números enteros en esa celda, si el usuario intenta escribir un número decimal, se producirá un error. Podemos restringir más los valores permitidos en la celda con la opción Datos, donde, por ejemplo, podemos indicar que los

valores estén entre 2 y 8. Si en la opción Permitir: elegimos Lista, podremos escribir una lista de valores para que el usuario pueda escoger un valor de los disponibles en la lista.

6. Otra opción es mostrar un Mensaje de entrada cuando se haga clic en la celda.

Este mensaje sirve para informar de qué tipos de datos son considerados válidos para esa celda.

Excel Intermedio

64

7. En la pestaña Mensaje de error podemos escribir el mensaje de error que queremos se le muestre al usuario cuando introduzca en la celda datos no válidos.

Seleccione una de las siguientes opciones en el cuadro Estilo: Información.- Para mostrar un mensaje informativo que no evite la especificación de datos no válidos.

Advertencia.- Para mostrar un mensaje de advertencia que no evite la especificación de datos no válidos.

Grave.- Para evitar la especificación de datos no válidos.

Excel Intermedio

65

4.5.1 Crear una lista de entradas validas

Para facilitar la entrada de datos, o para limitar las entradas a determinados elementos que define el usuario, puede crear una lista desplegable de entradas válidas. Esta herramienta permite que Excel supervise el ingreso de información en una hoja de

cálculo sobre la base de un conjunto de criterios previamente establecidos.

Ejemplo: Validar lista de entradas para seleccionar la Provincia donde vive.

1. Para crear una lista de entradas válidas para la lista desplegable, escriba las entradas en una fila o columna única sin celdas en blanco. Por ejemplo:

2. Seleccione la celda donde desee crear la lista desplegable.

3. En la ficha Datos, en el grupo Herramientas de datos, haga clic en Validación de datos.

4. Se muestra el cuadro de diálogo Validación de datos.

Excel Intermedio

66

5. Haga clic en la ficha Configuración.

6. En el cuadro Permitir, haga clic en Lista.

7. Especifique la ubicación de la lista de entradas válidas.

8. Asegúrese de que esté activada la casilla de verificación Celda con lista desplegable.

9. Para especificar si la celda se puede dejar en blanco, active o desactive la casilla de verificación Omitir blancos.

10. Haga clic en el botón Aceptar.

11. Para finalizar se muestra una flecha en la celda seleccionada. Haga clic y se desplegará la lista de las provincias.

Excel Intermedio

67

4.6 Ordenar y Filtrar

4.6.1 Ordenar información

Al ordenar los datos contenidos en una hoja de Excel podemos llegar a comprender mejor la información. Los datos se pueden ordenar de manera ascendente y descendente.

Ordenar rápidamente

Para ordenar los datos de manera rápida seleccione una celda que pertenezca a la columna por la que se realizará el ordenamiento, en la ficha Inicio, en el grupo Modificar, haga clic en el botón Ordenar y filtrar para finalmente seleccionar la opción Ordenar de A a Z (ascendente) o la opción Ordenar de Z a A (descendente).

Estos mismos comandos los puede encontrar en la ficha Datos como parte del grupo Ordenar y filtrar.

Ordenar por más de una columna

Para ordenar los datos por más de una columna se puede utilizar la opción Orden personalizado que también se encuentra en el menú desplegable del botón Ordenar y filtrar en la ficha Inicio. Este comando mostrará el cuadro de diálogo Ordenar el cual nos permitirá especificar todas las columnas por las que deseamos ordenar los datos.

Excel Intermedio

68

De la lista Ordenar por selecciona la columna por la que se ordenará y de la lista Criterio de ordenación elija el tipo de ordenamiento ya sea ascendente o descendente. Para agregar una columna adicional debe oprimir el botón Agregar nivel y de nuevo especificar los valores para cada una de las listas. Al pulsar el botón Aceptar se aplicará el orden especificado para cada una de las columnas.

4.6.2 Filtrar información

Para agregar un filtro a una tabla debe seleccionar cualquier celda que forme parte de los datos y entonces hacer clic en la opción Filtro que se encuentra dentro del botón Ordenar y filtrar en la ficha Inicio.

Excel Intermedio

69

El mismo comando Filtro lo puede encontrar en la ficha Datos dentro del grupo Ordenar y filtrar.

Una vez aplicado el filtro aparecerán flechas al lado derecho de cada columnas las cuales serán menús desplegables donde aparecerán las opciones de filtrado para la columna.

Filtrar una columna

Con los filtros agregados a nuestra tabla podemos comenzar a filtrar información. Podemos filtrar los datos de una columna al hacer clic sobre la flecha desplegable del filtro de columna y podremos observar la lista de los valores únicos de la columna.

Excel Intermedio

70

Debemos seleccionar aquellos valores que deseamos observar en pantalla y entonces seleccionar el botón Aceptar. Excel ocultará aquellas filas que no cumplan con el criterio especificado.

El primer elemento de la lista es la opción (Seleccionar todo) la cual nos ayuda a marcar o desmarcar todas las cajas de selección con solo un clic.

Filtrar por dos o más columnas

Una vez que ha filtrado los datos por una columna puedes refinar aún más los resultados al aplicar un filtro adicional a una segunda o tercer columna. Solamente vuelve a elegir la flecha desplegable de filtro de otra columna y selecciona las opciones de filtrado. Ejemplo: Filtro por Carrera (Arquitectura) y por Estado (Aprobado)

Excel Intermedio

71

Borrar un filtro específico

Para borrar el filtro de alguna columna en específico vuelve a pulsar la flecha desplegable de filtro y selecciona la opción Eliminar filtro.

Borrar todos los filtros

Si en lugar de borrar un solo filtro desea eliminar todos los filtros de los datos, entonces debe ir a la ficha Datos y dentro del grupo Ordenar y filtrar hacer clic en el botón Borrar.

Excel Intermedio

72

4.7 Combinar correspondencia (MS Word y MS Excel)

Puede utilizar combinar correspondencia de Word con datos de Excel para crear un conjunto de documentos, como un oficio modelo que se debe enviar a muchos destinatarios. Cada oficio contiene el mismo tipo de información, pero se puede personalizar con el nombre, dirección, (u otros datos) de la persona al que va dirigido.

El proceso de combinación de correspondencia implica los siguientes pasos generales:

1. Configurar el documento principal. El documento principal contiene el texto y los gráficos que son iguales para cada versión del documento combinado. Por ejemplo, en el caso de un oficio modelo, el remite o el saludo.

2. Conectar el documento a un origen de datos. Un origen de datos es un archivo

que contiene la información que se debe combinar en un documento. Por ejemplo, el título, los nombres, el cargo y direcciones de los destinatarios de un oficio.

4.7.1 Configurar el documento principal

Inicie Word, crear el documento principal que contiene el texto y los gráficos que son iguales para cada versión del documento combinado.

Por ejemplo, en el caso de un oficio modelo.

Excel Intermedio

73

4.7.2 Conectar el documento a un origen de datos.

Para combinar información en el documento principal, debe conectarlo a un origen de datos, también llamado archivo de datos. Iniciar Excel, crear el libro que contiene la información que se debe combinar. Por ejemplo, el grado académico, nombres y apellidos, departamento, etc.

Guardar el archivo, asignarle un nombre y recordar la ubicación donde se almacenó. Por ejemplo: Nombre: Correspondencia Guardar en: Mis documentos Para combinar correspondencia, se debe trabajar con el asistente para combinar correspondencia, de la ficha Correspondencia.

El asistente nos proporciona 6 sencillos pasos para combinar correspondencia:

1. Seleccione el tipo de documento. 2. Seleccione el documento inicial. 3. Seleccione los destinatarios. 4. Escriba la carta. 5. Vista previa de las cartas. 6. Complete la combinación

Excel Intermedio

74

Paso 1. Seleccione el tipo de documento

En primer lugar tenemos que elegir el tipo de documento que queremos generar (cartas, mensajes de correo electrónico, sobres, etc.)

Seleccionar Cartas y hacer en Siguiente: Inicie el documento, aparece el segundo paso del asistente.

Paso 2: Seleccione el documento inicial

Definir el documento inicial, el documento que contiene la parte fija a partir de la cual se creara el documento combinado. Debajo de las tres opciones tenemos una explicación de la opción seleccionada en este momento.

Dejar seleccionada la primera opción y hacer clic en Siguiente para continuar con el asistente.

Excel Intermedio

75

Paso 3. Seleccione los destinatarios

Seleccionar el origen de datos, utilizar una lista existente (hoja de Excel) Si seleccionamos la opción Utilizar una lista existente aparece la opción Examinar..., hacer clic para buscar el archivo que contiene el origen de datos. Si seleccionamos la opción Seleccionar de los contactos de Outlook, en lugar de Examinar... aparece la opción Elegir la carpeta de contactos, hacer clic en ella y elegir la carpeta. Si seleccionamos la opción Escribir una lista nueva en lugar de Examinar... aparece la opción Crear..., hacer clic en ella para introducir los valores en la lista.

Ahora explicaremos con más detalle la opción Examinar... Al hacer clic en la opción Examinar... se abre el cuadro de diálogo Seleccionar archivos de origen de datos en el que indicaremos de dónde extraerá los datos:

En la parte superior de la pantalla en el campo Buscar en: indicamos la carpeta donde se encuentra el origen de datos. Al seleccionar la carpeta aparecerán todos los archivos

Excel Intermedio

76

del tipo seleccionado que se encuentren en la carpeta, sólo nos quedará hacer doble clic sobre el archivo que contiene nuestro origen de datos o seleccionarlo para que aparezca su nombre en el campo Nombre de archivo: y hacer clic en el botón Abrir. Seleccionar la hoja donde están los datos (Hoja1), y hacer clic en el botón Aceptar, La primera fila de datos contiene encabezados de columna, pues precisamente la primera fila de la hoja de Excel, es el nombre de cada uno de los campos: el grado académico, nombres y apellidos, departamento, etc.

Una vez indicado el origen de datos se abre el cuadro de diálogo Destinatarios de combinar correspondencia, en él vemos los datos que se combinarán y podemos añadir opciones.

Hacer clic en Aceptar para pasar al paso 4 del asistente...

Excel Intermedio

77

Excel Intermedio

78

Paso 4. Escriba la carta

Redactar en el documento abierto el texto fijo de nuestro oficio (si no estaba escrito ya) y añadir los campos de combinación. Para ello debemos ubicar el cursor en la posición donde queremos que aparezca el campo de combinación y a continuación hacemos clic en la opción Más elementos… en la nueva ventana Insertar campo de combinación seleccionar el elemento que queremos insertar y finalmente clic en el botón Insertar.

Repetir el proceso para poder elegir de la lista los campos a insertar.

Nota.- Cuando se inserta un campo de combinación de correspondencia en el documento principal, el nombre del campo siempre se indica entre comillas de combinación (« »).

Excel Intermedio

79

Estas comillas no aparecen en los documentos combinados. Simplemente ayudan a distinguir el texto normal de los campos en el documento principal. Qué ocurre al realizar la combinación Al combinar, la información contenida en la primera fila del archivo de datos reemplaza los campos del documento principal para crear el primer documento combinado. La información de la segunda fila del archivo de datos reemplaza los campos para crear un segundo sobre, y así sucesivamente.

Cuando hayamos completado el documento hacemos clic en Siguiente para pasar al paso 5 del asistente.

Paso 5. Vista previa de las cartas

En este paso examinamos las cartas tal como se escribirán con los valores concretos del origen de datos.

Excel Intermedio

80

Podemos utilizar los botones << y >> para pasar al destinatario anterior y siguiente respectivamente, podemos Buscar un destinatario... concreto, Excluir al destinatario (en el que nos encontramos) o Editar lista de destinatarios... para corregir algún error detectado.

Para terminar hacer clic en Siguiente.

Paso 6. Complete la combinación

Ultimo paso, usted está listo para generar las cartas. Para enviar las cartas directamente a la impresora hacer clic en la opción Imprimir... Si nos queremos guardar las cartas en un nuevo documento por ejemplo para rectificar el texto fijo en algunas de ellas, o enviar luego el documento a otro usuario hacemos clic en Editar cartas individuales...

En este caso nos permite elegir combinar todos los registros, el registro actual o un grupo de registros. El documento creado será un documento normal sin combinación

Excel Intermedio

81

4.7.3 Guardar el documento principal

Recuerde que los documentos combinados que guarde son independientes del documento principal. Es conveniente guardar el propio documento principal si desea utilizarlo para otra combinación de correspondencia. Cuando guarde el documento principal, también puede guardar su conexión con el archivo de datos. La siguiente vez que abra el documento principal, se le pedirá que indique si desea volver a combinar la información del archivo de datos en el documento principal.

Si hace clic en Sí, se abre el documento con la información del primer registro ya combinada.

Si hace clic en No, se rompe la conexión entre el documento principal y el archivo

de datos. El documento principal se convierte en un documento normal de Word y los campos se reemplazan con la información del primer registro.

Excel Intermedio

82

UNIDAD 5. Gráficos

Para el logro del triunfo siempre ha sido indispensable pasar por la senda de los sacrificios.

Simón Bolívar MS Excel, permite fácilmente crear varios tipos de gráficos de aspecto profesional. Un gráfico es la representación gráfica de los datos de una hoja de cálculo y facilita su interpretación. Para crear una gráfica es indispensable contar con los datos. La información puede ser no definitiva, por lo que la gráfica se actualiza conforme se modifican sus datos de origen.

5.1 Tipos de gráficos

MS Excel ofrece varios tipos de gráficos para ayudarle a mostrar datos de forma comprensible ante una audiencia. Cuando cree un gráfico o modifique uno existente, puede elegir entre una amplia gama de subtipos de gráficos disponibles para cada uno de los tipos de gráficos siguientes:

Gráficos de columnas.

Gráficos de líneas.

Gráficos circulares.

Gráficos de barras.

Gráficos de área.

Gráficos de tipo XY (Dispersión).

Gráficos de cotizaciones.

Gráficos de superficie.

Gráficos de anillos.

Gráficos de burbujas.

Gráficos radiales.

Excel Intermedio

83

5.1.1 Gráficos de columnas

Este tipo de gráfico es útil para mostrar cambios de datos en un período de tiempo o para ilustrar comparaciones entre elementos. En los gráficos de columnas, las categorías normalmente se organizan en el eje horizontal y los valores en el eje vertical.

5.1.2 Gráficos de líneas

Los gráficos de línea pueden mostrar datos continuos en el tiempo, establecidos frente a una escala común y, por tanto, son ideales para mostrar tendencias en datos a intervalos iguales. En un gráfico de líneas, los datos de categoría se distribuyen uniformemente en el eje horizontal y todos los datos de valor se distribuyen uniformemente en el eje vertical.

Debería utilizar un gráfico de líneas si las etiquetas de categoría son texto, y representan valores de espacio uniforme como meses, trimestres o ejercicios fiscales.

5.1.3 Gráficos circulares

Los gráficos circulares muestran el tamaño de los elementos de una serie de datos, en proporción a la suma de los elementos. Los puntos de datos de un gráfico circular se muestran como porcentajes del total del gráfico circular.

Excel Intermedio

84

Piense en utilizar un gráfico circular cuando: Sólo tenga una serie de datos que desee trazar. Ninguno de los valores que desea trazar son negativos. Casi ninguno de los valores que desea trazar son valores cero. No tiene más de siete categorías.

5.1.4 Gráficos de barras

Los gráficos de barras muestran comparaciones entre elementos individuales. Piense en utilizar un gráfico de barras cuando:

Las etiquetas de eje son largas. Los valores que se muestran son

duraciones.

5.1.5 Gráficos de tipo XY (Dispersión)

Los gráficos de dispersión tipo XY muestran la relación entre los valores numéricos de varias series de datos o trazan dos grupos de números como una serie de coordenadas XY. Un gráfico de dispersión tiene dos ejes de valores, mostrando un conjunto de datos numéricos en el eje horizontal (eje x) y otro en el eje vertical (eje y).

Combina estos valores en puntos de datos únicos y los muestra en intervalos uniformes o agrupaciones. Los gráficos de dispersión se utilizan normalmente para mostrar y comparar valores numéricos, como datos científicos, estadísticos y de ingeniería. Para organizar los datos de una hoja de cálculo para un gráfico de dispersión, debería colocar los valores de X en una fila o columna y, a continuación, escribir los valores Y correspondientes en las filas o columnas adyacentes.

Excel Intermedio

85

5.2 Crear un gráfico

Crear un gráfico en MS Excel es rápido y sencillo. Excel proporciona una variedad de tipos de gráficos entre los que puede elegir. En la mayoría de los gráficos, como los gráficos de columnas y los gráficos de barras, puede representar gráficamente datos que están organizados en filas o columnas en una hoja de cálculo. Sin embargo, algunos tipos de gráfico, como el gráfico circular o el gráfico de burbujas, requieren una disposición de datos específica.

1. En la hoja de cálculo, organice los datos que desea trazar en un gráfico.

2. Seleccione las celdas que contienen los datos que desea utilizar en el gráfico.

Si selecciona únicamente una celda, Excel traza automáticamente todas las celdas que contienen datos que rodean directamente esa celda en un gráfico.

3. En la ficha Insertar, en el grupo Gráficos, siga uno de los procedimientos siguientes:

Haga clic en el tipo de gráfico y, a continuación, haga clic en el subtipo de gráfico que desea utilizar.

Para ver todos los tipos de gráficos disponibles, haga clic en un tipo de gráfico y, a continuación, haga clic en Todos los tipos de gráfico para mostrar el cuadro de diálogo Insertar gráfico; por último, haga clic en las flechas para desplazarse por todos los tipos y subtipos de gráficos disponibles y haga clic en los que desea utilizar.

El gráfico se coloca en la hoja de cálculo como un gráfico incrustado. Si desea colocar el gráfico en una hoja de gráfico independiente, puede cambiar su ubicación. En base a los datos a continuación se ilustra un gráfico tipo columna.

Excel Intermedio

86

Al crear un gráfico, las herramientas de gráfico aparecen disponibles y se muestran las fichas Diseño, Presentación y Formato.

Puede utilizar los comandos de estas fichas para modificar el gráfico con el fin de que presente los datos de la forma que desea. Por ejemplo, utilice la

ficha Diseño para mostrar las series de datos por filas o por columnas, realizar cambios en el origen de datos del gráfico, cambiar la ubicación del mismo, cambiar el tipo de gráfico, o seleccionar opciones de diseño y formato predefinidas. Utilice la ficha Presentación para cambiar la disposición de los elementos del gráfico, como los títulos del gráfico, utilizar herramientas de dibujo o agregar cuadros de texto e imágenes al gráfico. Utilice la ficha Formato para agregar colores de relleno, cambiar estilos de línea o aplicar efectos especiales.

Excel Intermedio

87

Realice una práctica para ejercitar sobre creación de gráficos.

1. Ingrese a Excel. 2. En base a la información que se muestra en cada ilustración, generar el gráfico

correspondiente.

3. Datos para gráfico de barra.

4. Datos para gráfico de línea.

Actividad recomendada.

Excel Intermedio

88

5. Datos para gráfico circular.

5.3 Dar formato a los gráficos

Después de crear un gráfico, puede cambiar su aspecto de forma instantánea. En lugar de agregar o cambiar manualmente los elementos o el formato del gráfico, puede aplicarle rápidamente un diseño y un estilo predefinido. Aunque MS Excel proporciona diversos diseños y estilos predefinidos muy útiles, si fuera necesario, puede personalizarlos aún más cambiando manualmente el diseño y el estilo de cada uno de los elementos del gráfico. No puede guardar un diseño o un formato personalizados, pero si desea volver a utilizarlos, puede guardar el gráfico como una plantilla de gráfico.

5.3.1 Seleccionar un diseño de gráfico predefinido

En la ficha Diseño, en el grupo Diseños de gráfico, haga clic en el diseño que desee utilizar.

Excel Intermedio

89

Para ver todos los diseños disponibles, haga clic en más .

5.3.2 Seleccionar un estilo de gráfico predefinido

En la ficha Diseño, en el grupo Estilos de diseño, haga clic en el estilo que desee utilizar.

Para ver todos los estilos y diseños de gráficos disponibles, haga clic en más .

5.3.3 Seleccionar elementos de gráfico

Puede seleccionar rápidamente elementos de gráfico utilizando el mouse (haga clic en el elemento de gráfico que desea seleccionar). No obstante, si no está seguro del lugar que ocupa un elemento específico en el gráfico, lo puede seleccionar de una lista de elementos de gráfico así:

Haga clic en un gráfico. En la ficha Formato, en el grupo Selección actual, haga clic en la flecha situada junto al cuadro Elementos de gráfico y, a continuación, haga clic en el elemento de gráfico que desea seleccionar.

Excel Intermedio

90

5.3.4 Cambiar manualmente el diseño de elementos de gráfico

1 Haga clic en el gráfico o seleccione el elemento de gráfico al que desea cambiar el

diseño.

2 En la ficha Presentación, siga uno o varios de estos procedimientos: En el grupo Etiquetas, haga clic en la opción de diseño de etiqueta que desee. En el grupo Ejes, haga clic en la opción de eje o de línea de división que desee. En el grupo Fondo, haga clic en la opción de diseño que desee.

Las opciones de diseño que seleccione se aplicarán al elemento que haya seleccionado. Por ejemplo, si tiene seleccionado todo el gráfico, las etiquetas de datos se aplicarán a todas las series de datos. Si tiene seleccionado un único punto de datos, las etiquetas de datos se aplicarán solamente a la serie de datos o al punto de datos seleccionado.

5.3.5 Cambiar manualmente el estilo de elementos de gráfico

También puede aplicar un estilo rápido a cada uno de los elementos, o hacer clic en Relleno de forma, Contorno de forma y Efectos de formas en el grupo Estilos rápidos de forma de la ficha Formato y, a continuación, seleccionar las opciones de formato que desee.

5.3.6 Usar títulos en un gráfico

Para facilitar la interpretación de un gráfico, puede agregar títulos, como un título de gráfico y títulos de eje, a cualquier tipo de gráfico. Puede modificar fácilmente el texto de los títulos, y si ya no desea mostrarlos, puede quitarlos del gráfico. Aplicar un diseño de gráfico que contenga títulos

1 Haga clic en el gráfico al que desea aplicar un diseño de gráfico. 2 En la ficha Diseño, en el grupo Diseño del gráfico, haga clic en un diseño que contenga títulos.

Excel Intermedio

91

Agregar un título de gráfico manualmente

1 Haga clic en el gráfico al que desea agregar un título. 2 En la ficha Presentación, en el grupo Etiquetas, haga clic en Título del gráfico.

3 Haga clic en Título superpuesto centrado o Encima del gráfico. 4 En el cuadro de texto Título del gráfico que aparece en el gráfico, escriba el texto que desee.

5.3.7 Guardar un gráfico como una plantilla de gráfico

1 Haga clic en el gráfico que desea guardar como plantilla.

2 En la ficha Diseño, en el grupo Tipo, haga clic en Guardar como plantilla.

3 En el cuadro Guardar en, asegúrese de que la carpeta Gráficos (Charts) está

seleccionada.

4 En el cuadro Nombre de archivo, escriba un nombre adecuado para la plantilla de

gráfico.

Cuando cree un gráfico nuevo o desee cambiar el tipo de gráfico de un gráfico existente, puede aplicar la nueva plantilla de gráfico.

Excel Intermedio

92

5.3.8 Cómo aplicar una plantilla de gráfico

1. Para crear un gráfico nuevo basado en la plantilla, en la ficha Insertar, en el grupo Gráficos, haga clic en un tipo de gráfico y, a continuación, en Todos los tipos de gráfico. También puede hacer clic en el iniciador del cuadro de diálogo situado junto a Gráficos en el grupo Gráficos de la ficha Insertar.

2. Haga clic en Plantillas en el primer cuadro y, a continuación, haga clic en la plantilla

que desee utilizar en el segundo cuadro.

5.3.9 Cambiar la presentación de los ejes de gráficos

Los gráficos tienen normalmente dos ejes que se utilizan para medir y clasificar los datos: un eje vertical (denominado también eje de valores o eje y) y un eje horizontal (conocido también como eje de categorías o eje x). Los gráficos 3D tienen un tercer eje, el eje de profundidad (denominado también eje de series o eje z), que permite representar los datos a lo largo de la profundidad de un gráfico. Los gráficos radiales no tienen ejes

horizontales (categorías), y los gráficos circulares y de anillos no tienen ningún eje. 1 Eje vertical (valores). 2 Eje horizontal (categorías). 3 Eje de profundidad (series).

Excel Intermedio

93

5.3.10 Mostrar u ocultar los ejes

1 Haga clic en el gráfico en el que desea mostrar u ocultar los ejes. 2 En la ficha Presentación, en el grupo Ejes, haga clic en Ejes.

3 Siga uno de los procedimientos siguientes: Para mostrar un eje, haga clic en el tipo de eje que desea mostrar y, a

continuación, haga clic en una de las opciones de presentación del eje. Para ocultar un eje, haga clic en el tipo de eje que desee ocultar y, a continuación,

haga clic en Ninguno.

5.3.11 Cambiar el tipo de gráfico de un gráfico existente

En la mayoría de los gráficos 2D, puede cambiar el tipo de gráfico para aportarle un aspecto totalmente diferente, o puede seleccionar un nuevo tipo de gráfico para cualquier serie de datos. 1. Para cambiar el tipo de gráfico, haga clic en el área del gráfico para mostrar las herramientas de gráfico. 2. En la ficha Diseño, en el grupo Tipo, haga clic en Cambiar tipo de gráfico.

Excel Intermedio

94

3. En el cuadro de diálogo Cambiar tipo de gráfico, haga clic en un tipo de gráfico en el primer cuadro y, a continuación, haga clic en el subtipo de gráfico que desee utilizar en el segundo cuadro. Si guardó algún tipo de gráfico como plantilla, haga clic en Plantillas y, a continuación, haga clic en la plantilla de gráfico que desee utilizar en el segundo cuadro.

Si con frecuencia utiliza un tipo de gráfico específico cuando crea un gráfico, es una buena idea establecer dicho tipo de gráfico como el predeterminado. Después de seleccionar el tipo y el subtipo de gráfico en el cuadro de diálogo Cambiar tipo de gráfico, haga clic en Establecer como predeterminado.

5.3.12 Trazar serie de datos de columnas o filas de hoja de cálculo

Después de crear un gráfico, puede cambiar fácilmente la manera en la que se trazan las columnas y las filas de la hoja de cálculo en el gráfico. 1 Haga clic en el gráfico que contiene los datos que desea trazar de forma diferente. 2 En la ficha Diseño, en el grupo Datos, haga clic en Cambiar entre filas y columnas.

Al hacer clic en este botón se cambia rápidamente entre trazar la serie de datos en el gráfico desde las filas de la hoja de cálculo o desde las columnas.

Excel Intermedio

95

5.3.13 Modificar el tamaño de un gráfico

Después de crear un gráfico, puede cambiar el tamaño. Cuando tiene un elemento seleccionado aparecen diferentes tipos de controles que explicaremos a continuación:

Los controles cuadrados establecen el ancho y largo del objeto, haga clic sobre ellos y arrástralos para modificar sus dimensiones. Haciendo clic y arrastrando los controles circulares podrá modificar su tamaño manteniendo el alto y ancho que haya establecido, de esta forma podrá escalar el objeto y hacerlo más grande o pequeño.

5.3.14 Modificar la ubicación de un gráfico

Excel permite decidir la posición del gráfico en el documento. 1. Haga clic en el gráfico. 2. En la ficha Diseño, en el grupo Ubicación, haga clic en Mover gráfico.

La primera opción Hoja nueva permite establecer el gráfico como una hoja nueva. Utilizando la segunda opción, Objeto en, podremos mover el gráfico a una hoja ya existente. El gráfico quedará flotante en la hoja y podrá situarlo en la posición y con el tamaño que usted elija.

Excel Intermedio

96

UNIDAD 6. Tablas Dinámicas

“El que no vive para servir, no sirve para vivir.”

Franklin Delano Roosevelt 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... Un informe de tabla dinámica es una forma interactiva de resumir rápidamente grandes volúmenes de datos. Utilice un informe de tabla dinámica para analizar datos numéricos en profundidad y para responder preguntas no anticipadas sobre los datos. Un informe de tabla dinámica está especialmente diseñado para:

Consultar grandes cantidades de datos de muchas maneras diferentes y cómodas para el usuario.

Calcular el subtotal y agregar datos numéricos, resumir datos por categorías y subcategorías, y crear cálculos y fórmulas personalizados.

Expandir y contraer niveles de datos para destacar los resultados y desplazarse hacia abajo para ver los detalles de los datos de resumen de las áreas de interés.

Desplazar filas a columnas y columnas a filas para ver resúmenes diferentes de los datos de origen.

Filtrar, ordenar, agrupar y dar formato condicional a los subconjuntos de datos más útiles e interesantes para poder centrarse en la información que le interesa.

6.1 Crear un informe de tabla dinámica

Para crear un informe de tabla dinámico, necesita conectar con un origen de datos y especificar la ubicación del informe.

1. Seleccione una celda de un rango de celdas o coloque el punto de inserción dentro de una tabla de MS Excel.

Excel Intermedio

97

2. Asegúrese de que el rango de celdas tiene encabezados de columna.

3. Para crear un informe de tabla dinámica, en la ficha Insertar, en el grupo Tablas, haga clic en Tabla dinámica y, a continuación, en Tabla dinámica.

4. Aparecerá el cuadro de diálogo Crear tabla dinámica.

Excel Intermedio

98

5. Opcionalmente, para seleccionar un rango de celdas o una tabla, haga clic en

Contraer cuadro de diálogo para ocultar temporalmente el cuadro de diálogo, seleccione el rango en la hoja de cálculo y, a continuación, presione Expandir

diálogo . 6. Escriba una ubicación. Siga uno de los procedimientos siguientes:

Para poner el informe de tabla dinámica en una hoja de cálculo nueva que empiece por la celda A1, haga clic en Nueva hoja de cálculo.

Para poner el informe de tabla dinámica en una hoja de cálculo existente, seleccione Hoja de cálculo existente y, a continuación, escriba la primera celda del rango de celdas donde desea situar el informe de tabla dinámica.

7. Haga clic en Aceptar. Un informe de tabla dinámica vacío se agregará a la ubicación que especificó en la Lista de campos de tabla dinámica que se muestra de modo que puede comenzar a agregar campos, crear un diseño y personalizar el informe de tabla dinámica.

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

Excel Intermedio

99

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 Precio para cada Referencia en

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

Excel Intermedio

100

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 pestaña 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. Si arrastrásemos a la zona de datos los campos cantidad, obtendríamos la tabla, más compleja pero con más información:

6.2 Crear un informe de gráfico dinámico

Para crear un informe de gráfico dinámico, necesita conectar con un origen de datos y especificar la ubicación del informe.

1. Seleccione una celda de un rango de celdas. 2. Asegúrese de que el rango de celdas tiene encabezados de columna. 3. Para crear un informe de gráfico dinámico, en la ficha Insertar, en el grupo

Tablas, haga clic en Tabla dinámica y, a continuación, en Gráfico dinámico.

Excel Intermedio

101

4. Aparecerá el cuadro de diálogo Crear tabla dinámica.

5. Opcionalmente, para seleccionar un rango de celdas o una tabla, haga clic en

Contraer cuadro de diálogo para ocultar temporalmente el cuadro de diálogo, seleccione el rango en la hoja de cálculo y, a continuación, presione Expandir

diálogo . 6. Escriba una ubicación. Siga uno de los procedimientos siguientes: 7. Para poner el informe de tabla dinámica en una hoja de cálculo nueva que

empiece por la celda A1, haga clic en Nueva hoja de cálculo. 8. Para poner el informe de tabla dinámica en una hoja de cálculo existente,

seleccione Hoja de cálculo existente y, a continuación, escriba la primera celda del rango de celdas donde desea situar el informe de tabla dinámica.

9. Haga clic en Aceptar. Un informe de tabla dinámica vacío se agregará a la ubicación que especificó en la Lista de campos de tabla dinámica que se muestra de modo que puede comenzar a agregar campos, crear un diseño y personalizar el informe de tabla dinámica.

Excel Intermedio

102

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

Excel Intermedio

103

Si dejamos marcados la sucursal Loja, la otra sucursal desaparecerá de la tabla, pero no se pierde, en cualquier momento podemos visualizarla volviendo a desplegar la lista y marcando la casilla (Seleccionar todo).

Aplicando el filtro a varios campos podemos formar condiciones de filtrado más complejas.

6.4 Eliminar una tabla dinámica.

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

1. Haga clic en el informe de tabla o gráfico dinámico.

2. En la ficha Opciones, en el grupo Acciones, haga clic en Seleccionar y, a continuación, en Toda la tabla dinámica.

3. Presione SUPR Otra opción es eliminar la hoja donde se generó la tabla dinámica.

Excel Intermedio

104

Realice la siguiente práctica para ejercitar sobre creación de tablas dinámicas.

1. Ingrese a Excel. 2. En base a la información que se muestra en la ilustración, generar el informe de

tabla dinámica.

3. Se tiene una muestra de estudiantes matriculados en un determinado periodo académico, y se necesita generar un informe resumen de matriculados por carrera y centro.

4. El resultado se muestra en la siguiente ilustración.

Actividad recomendada.

Excel Intermedio

105

Excel Intermedio

106

UNIDAD 7. Uso de Macros para simplificar tareas

“Pensar que se domina algo, es pensar no poder superarse.”

José María Franco En esta última unidad estudiaremos Macros, en qué nos pueden ayudar y cómo crear macros automáticamente. Las macros permiten automatizar varias tareas repetitivas y fusionarlas en una sola, añadiendo por ejemplo un botón en nuestro libro que al pulsar sobre él realice todas esas tareas. Una vez creada una macro, puede asignarla a un objeto (como un botón de barra de herramientas, un gráfico o un control) para que pueda ejecutarla haciendo clic en ese objeto. Si ya no es necesario utilizar una macro, puede eliminarla.

7.1 Proceso de grabación

La forma más fácil e intuitiva de crear macros es crearlas mediante el grabador de macros del que dispone MS Excel. Cuando grabe una macro, la grabadora de macros graba todos los pasos necesarios para completar las acciones que desea grabar. En los pasos grabados no se incluye el desplazamiento por la cinta de opciones. Estos pasos se traduce a instrucciones (código) de MS Visual Basic para Aplicaciones (VBA), las cuales se pueden modificar posteriormente si se tienen conocimientos de programación.

1 Si la ficha Programador no está disponible, haga lo siguiente para mostrarla:

Haga clic en el Botón Office y, a continuación, haga clic en Opciones de Excel. En la categoría Más frecuentes, bajo Opciones principales para trabajar con Excel, active la casilla de verificación Mostrar ficha Programador en la cinta de opciones y, a continuación, haga clic en Aceptar.

Excel Intermedio

107

2 Para grabar una macro debemos acceder de cualquiera de las siguientes formas:

1. En la ficha Vista y en el grupo Macros, haga clic Grabar macro... 2. En la ficha Programador, en el grupo Código, haga clic en Grabar macro.

3 En el cuadro Nombre de la macro, escriba un nombre para la macro.

El primer carácter del nombre de la macro debe ser una letra. Los caracteres siguientes pueden ser letras, números o caracteres de subrayado. No se permiten espacios en un nombre de macro. Puede asignar una combinación de tecla de método abreviado mediante la combinación de teclas con CTRL para ejecutar la macro, en el cuadro Tecla de método abreviado, escriba cualquier letra en mayúsculas o minúsculas que desee utilizar.

La tecla de método abreviado suplantará a cualquier tecla de método abreviado predeterminada equivalente en Excel mientras esté abierto el libro que contiene la macro. Es importante encontrar una combinación que no utilice ya Excel.

4 En la lista Guardar macro en, seleccione el libro en el que desea almacenar la macro.

Si desea que la macro esté disponible siempre que utilice Excel, seleccione Libro de macros personal. Cuando se selecciona Libro de macros personal.

5 Para incluir una descripción de la macro, escriba el texto que desee en el cuadro

Descripción.

6 Haga clic en Aceptar para iniciar la grabación.

7 Realice las acciones que desee grabar.

8 En la ficha Programador, en el grupo Código, haga clic en Detener grabación .

También puede hacer clic en Detener grabación en el lado izquierdo de la barra de estado.

Excel Intermedio

108

7.2 Ejecutar una macro

Existen varias maneras de ejecutar una macro. Siempre puede ejecutar una macro utilizando el comando de menú. Dependiendo de cómo esté asignada la ejecución a la macro, puede que también pueda ejecutarla presionando una combinación de teclas de método abreviado con CTRL o haciendo clic en un botón de barra de herramientas o en un área de un objeto, un gráfico o un control. Asimismo, puede ejecutar una macro automáticamente cuando se abre el libro.

1 Para establecer el nivel de seguridad de manera que estén habilitadas temporalmente

todas las macros, haga lo siguiente:

En la ficha Programador, en el grupo Código, haga clic en Seguridad de macros.

En la categoría Configuración de macros, bajo Configuración de macros, haga clic en Habilitar todas las macros (no recomendado; puede ejecutarse código posiblemente peligroso) y, a continuación, haga clic en Aceptar.

Para ayudar a evitar que se ejecute código potencialmente peligroso, recomendamos que vuelva a cualquiera de las configuraciones que deshabilitan todas las macros cuando termine de trabajar con las macros.

2 Abra el libro que contiene la macro.

3 En la ficha Programador, en el grupo Código, haga clic en Macros.

4 En el cuadro Nombre de la macro, haga clic en la macro que desea ejecutar.

5 Para ejecutar una macro en un libro de Excel, haga clic en Ejecutar.

En cuanto al resto de botones: Paso a paso - Ejecuta la macro instrucción por instrucción abriendo el editor de programación de Visual Basic. Modificar - Abre el editor de programación de Visual Basic para modificar el código de la macro. Eliminar - Borra la macro. Opciones - Abre otro cuadro de diálogo donde podemos modificar la tecla de método abreviado y la descripción de la macro.

Excel Intermedio

109

7.3 Crear una macro manualmente (Uso de Visual Basic)

Para crear una macro de forma manual es necesario tener conocimientos de programación en general y de Visual Basic en particular, ya que es el lenguaje de programación en el que se basa el VBA de Excel. En la ficha Programador, en el grupo Código, haga clic en Visual Basic.

A continuación se presenta la pantalla general del editor de Visual Basic.

1 En la parte izquierda, el recuadro superior es el del proyecto. Aquí se encuentran los

módulos, las hojas utilizadas en los procedimientos/funciones, etc.

2 En la parte izquierda, el recuadro inferior son las propiedades del objeto seleccionado.

En esta imagen podemos ver las propiedades del objeto Hoja1 y que es una hoja de cálculo (Worksheet).

Excel Intermedio

110

3 En la parte derecha tenemos el espacio dedicado a redactar el código de los

procedimientos/funciones, y en la parte superior existen dos cuadros combinados donde podemos seleccionar los objetos y los métodos de ese objeto respectivamente. Una vez abierto el editor de Visual Basic debemos insertar un módulo de trabajo que es donde se almacena el código de las funciones o procedimientos de las macros. Para insertar un módulo accedemos al menú Insertar→ MÓDULO. A continuación debemos plantearnos si lo que vamos a crear es una función (en el caso que devuelva algún valor), o si por el contrario es un procedimiento (si no devuelve ningún valor). Una vez concretado que es lo que vamos a crear, accedemos al menú Insertar→ Procedimiento...

1 Procedemos a dar el Nombre: sin insertar espacios en su nombre. Ejm: Curso

2 También escogemos de qué Tipo es, si es un Procedimiento, Función o es una

Propiedad.

3 Además podemos seleccionar el Ámbito de ejecución. Si lo ponemos como Público

podremos utilizar el procedimiento/función desde cualquier otro módulo, pero si lo creamos como Privado solo podremos utilizarlo dentro de ese módulo. Una vez seleccionado el tipo de procedimiento y el ámbito presionamos sobre Aceptar y se abre el editor de Visual Basic donde escribimos las instrucciones necesarias para definir la macro. Ejemplo:

Excel Intermedio

111

Para ejecutar una macro haga clic en la barra de botones en o presione F5

7.4 Guardar archivos con Macros

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

Excel Intermedio

112

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

Esto ocurre porque Excel no conoce la procedencia de las Macros. Como están compuestas por código podrían realizar acciones que fuesen perjudiciales para nuestro equipo. Si confía en las posibles Macros que contuviese el archivo o la ha creado Ud. haga clic en el botón Opciones para activarlas.

Selecciona la opción Habilitar este contenido y haga clic en Aceptar.

7.5 Ejecutar una macro haciendo clic en un área de un objeto gráfico

1. En la hoja de cálculo, seleccione un objeto gráfico existente, como una imagen, una imagen prediseñada, una forma o un gráfico SmartArt.

2. Para crear una zona activa en el objeto existente, en la ficha Insertar, en el grupo Ilustraciones, haga clic en Formas, seleccione la forma que desea utilizar y, a continuación, dibuje dicha forma en el objeto existente.

3. Haga clic con el botón secundario del mouse en la zona activa que ha creado y, a continuación, elija Asignar Macro en el menú contextual.

Excel Intermedio

113

4. Para asignar una macro al botón u objeto gráfico, haga doble clic en la macro o escriba el nombre de la misma en el cuadro Nombre de la macro.

5. Haga clic en Aceptar.

7.6 Creación de un botón para ejecutar una Macro

1. En la hoja de cálculo, en la ficha Programador, en el grupo Controles, haga clic en Insertar, seleccione Botón (control de formulario).

2. Haga clic con el botón secundario del mouse en la zona activa que ha creado y, a continuación, elija Asignar Macro en el menú contextual.

3. Para asignar una macro al botón, haga doble clic en la macro o escriba el nombre de la misma en el cuadro Nombre de la macro.

4. Haga clic en Aceptar.

Excel Intermedio

114

EXCEL INTERMEDIO Material Educativo Elaborada por: Jorge Marcos Cordero Zambrano

E-mail: jmcorderoz@gmail.com Blog: http://jmcordero.blogspot.com

Esta versión digital, ha sido licenciada bajo las licencias Creative Commons Ecuador 3.0 de Reconocimiento - No comercial - Sin Obras Derivadas; la cual permite copiar, distribuir y comunicar públicamente la obra, mientras se reconozca la autoría original, no se utilice con fines comerciales ni se realicen obras derivadas.

http://www.creativecommons.org/licences/by-nc-nd/3.0/ec/

Febrero, 2014