Microsoft Excel 2007 - wiki246649.wikispaces.comwiki246649.wikispaces.com/file/view/LibroExcel2007.pdf · esta nueva versión de Excel 2007 y además ... funciones y las ... En este

MAQUERA QUISPE HENRY GEORGE MERCADO RIVAS LENIN OMAR

MERCADO RIVAS RICHARD YURI ROJAS BUJAICO RAFAEL WILFREDO

Para estudiantes y profesionales

Microsoft Excel 2007


Maquera Quispe, Henry George Mercado Rivas, Lenin Omar

Mercado Rivas, Richard Yuri Rojas Bujaico, Rafael Wilfredo

Microsoft Excel 2007

MICROSOFT EXCEL 2007


Autores: Henry George Maquera Quispe Lenin Omar Mercado Rivas Richard Yuri Mercado Rivas Rafael Wilfredo Rojas Bujaico

Editor Richard Yuri Mercado Rivas Pje. José Carlos Mariategui 129 Junín – Huancayo 1a edición, marzo 2009 ISBN Nº 978-9972-33-967-7

Hecho el Depósito Legal en la Biblioteca Nacional del Perú Nº 2009-03415

El presente libro es un documento electrónico y está disponible para descargas en la dirección web: http://www.sistemasuncp.com/campus

iii

PRÓLOGO

Bienvenidos a Microsoft Excel 2007, un libro que muestra las nuevas características de

este potente software y a través de una serie de ejercicios y casos presentamos las

funciones básicas y avanzadas.

La gran utilización que se tiene de Excel en las organizaciones nos permite realizar un

estudio concreto de esta nueva versión y sus cambios incluidos en los diferentes temas

que se muestran en el desarrollo del siguiente libro, además la constante actualización

que debemos tener en estos tiempos en los cuáles la tecnología a acelerado su evolución

nos exige a que constantemente también nosotros debemos actualizar los libros

relacionados a la parte informática.

Dentro de las labores diarias que se presentan en las diversas oficinas o de manera

personal es necesario el manejo de aplicaciones software que hoy en día ya es una

necesidad que se presenta por lo tanto con el presente libro pretendemos dar a conocer

esta nueva versión de Excel 2007 y además tocamos puntos a nivel básico y avanzado

para que los diversos usuarios puedan de esta manera tener a la mano un libro que los

pueda ayudar de acuerdo a la necesidad que se presente.

iv

PRESENTACIÓN

Microsoft Excel 2007, es una poderosa herramienta software que nos permite acceder,

procesar, analizar, compartir y mostrar los datos y de está manera hacer un buen manejo

de la información.

Microsoft Excel 2007 nos permite acceder a servidores de datos, transferir información

en formato XML, publicar datos en sitios Web y realizar trabajos compartidos.

Haciendo uso de las herramientas, funciones y las nuevas características de Microsoft

Excel 2007, nos permitirá realizar una serie de trabajos que a diario se presentan a nivel

personal u organizacional y resolverlos de una manera fácil y obteniendo una

presentación profesional de está manera se convierte en una herramienta muy

importante y poderosa.

En este apartado contemplaremos también el uso de Macros y la programación con

Visual Basic el cuál nos permitirá ver que Microsoft Excel es un programa flexible,

personalizado donde el usuario puede crear sus propias aplicaciones y de esta manera

mejorar su productividad.

Cada tema tratado presenta al final una práctica con una serie de ejercicios los cuales

facilitarán el aprendizaje de esta herramienta. Si tiene alguna consulta o sugerencia al

respecto puede hacerlo escribiendo al email [email protected].

Los Autores

v

CONTENIDO PRÓLOGO iii

PRESENTACIÓN iv

AGRADECIMIENTOS ix

SOBRE LOS AUTORES x

Capítulo I: Introducción a Microsoft Excel 2007 1

1.1 Introducción 1

1.2 Elementos de Excel 1

1.3 Empezando a trabajar con Excel 5

1.4 Ingreso de datos 7

1.5 Errores 8

1.6 Manejo de archives 9

1.7 Formatos 12

1.8 Referencias 13

Práctica

Capítulo II: Formulas y Funciones 17

2.1 Introducir Fórmulas y Funciones 17

2.1.1 Operadores más utilizados en las Fórmulas y Funciones 18

2.2 Funciones 19

2.3 Insertar funciones con el Asistente 21

2.4 Funciones de fecha 22

2.5 Funciones de texto 23

2.6 Funciones Aritméticas y estadísticas 25

2.7 Funciones Lógicas 25

Práctica

2.8 Funciones de Búsqueda 29

2.9 Funciones Financieras 29

2.10 Función BUSCARV 30

2.11 Función BUSCARH 32

Práctica

vi

Capitulo 3: Gráficos 37

3.1 Descripción de Gráficos 37

3.2 Tipos de Gráficos 37

3.3 Características para utilizar tipos de gráficos 38

3.4 Creación de Gráficos 39

3.5 Modificar Gráficos 41

3.6 Utilizar herramientas predefinidas 42

Práctica

Capitulo 4: Base de Datos 47

4.1 Base de datos 47

4.2 Operaciones con base de datos 48

4.3 Autofiltro 51

4.4 Filtros avanzados 55

4.5 Subtotales 57

Capitulo 5: Tablas Dinámicas 61

5.1 Introducción 61

5.2 Creación de Tablas Dinámicas 63

5.3 Aplicar filtros 66

5.4 Modificar el diseño 67

5.5 Subtablas 68

5.6 Tablas Dinámicas de Tres Dimensiones 68

5.7 Graficar tablas Dinámicas 69

Práctica

Capitulo 6: Formularios 73

6.1 Validación de datos 73

6.2 Cuadros de Control 73

6.3 Control de Número 75

6.4 Cuadro de lista 75

6.5 Botón de comando 77

6.6 Botones de opción 78

vii

6.7 Casillas de verificación 79

Capitulo 7: Paneles e Impresiones 81

7.1 Paneles 81

7.2 Inmovilizar paneles 81

7.3 Impresión 84

7.4 Impresión de encabezados en todas las páginas 85

7.5 Trabajar con libros compartidos 86

Capitulo 8: Macros 91

8.1 Definición de Macro 92

8.2 Grabadora de macros 92

8.3 Ejecución de Macros 94

8.4 Creación de una Macro desde VBA 94

8.5 Modificación de Macros 95

8.6 Añadir Botón de Ejecución 97

8.7 Macro Paso a paso 97

8.8 Firmar digitalmente un Proyecto de Macro 99

Práctica

Capitulo 9: Visual Basic Aplications VBA 105

9.1 Introducción 105

9.2 Objetos 106

9.3 Editor de Visual Basic 109

9.4 Creación de un procedimiento 111

9.5 Fundamentos de programación 112

9.6 Subrutinas y Argumentos 113

9.7 Funciones 115

9.8 Complementos de Excel 117

Práctica

Capitulo 10: Variables y Sentencias Condicionales en VBA 121

10.1 Variable 121

10.2 Tipos de datos 121

10.3 Ámbito de las variables y constantes 122

viii

10.4 Función Inputbox 125

10.5 Sentencias Condicionales 127

10.6 Sentencias If 127

10.7 Sentencias Case 128

Práctica

Capitulo 11: Sentencias Repetitivas en VBA 131

11.1 Sentencias Repetitivas 131

11.2 Sentencias Do .. Loop 131

11.3 Sentencias For .. Next 132

11.4 Funciones de Comprobación 133

11.5 Matrices y Variables de Objeto 134

11.6 Tipos definidos por el usuario 135

11.7 Funciones integradas 135

11.8 Estructuras End With y For Each 136

Práctica

BIBLIOGRAFIA

ix

AGRADECIMIENTOS

En primer lugar a Dios por permitirnos estar en este mundo y darnos la dicha de la vida.

En segundo lugar a los amigos y compañeros que día a día son motivo de apoyo para

proseguir en nuestra carrera profesional, y a todos aquellos que de una manera u otra

permiten compartir con nosotros diversas experiencias en la vida.

Los Autores

x

SOBRE LOS AUTORES

HENRY GEORGE MAQUERA QUISPE, Ingeniero de Computación y Sistemas con

estudios concluidos de Maestría en Ingeniería de Sistemas, Actualmente es Docente

Universitario de la Facultad de Ingeniería de Sistemas de la Universidad Nacional del

Centro del Perú y realiza diversos trabajos relacionados a la parte informática así como

ponencias en diferentes certámenes académicos..

LENIN OMAR MERCADO RIVAS, Licenciado en matemáticas, docente de diversas

instituciones estatales y particulares a nivel de colegio y academias preuniversitarias, en

la que hace uso de herramientas a nivel de ofimática cuenta con Estudios de Maestría en

Pedagogía.

RICHARD YURI MERCADO RIVAS, Ingeniero de Sistemas, cuenta con estudios

concluidos de Maestría en Ingeniería de Sistemas, Actualmente es Docente de la

Facultad de Ingeniería de Sistemas de la Universidad Nacional del Centro del Perú,

además es capacitador en temas de informática

RAFAEL ROJAS BUJAICO, Ingeniero de Sistemas, con estudios concluidos en

maestría, Docente en la Universidad Nacional del Centro del Perú y capacitador en

temas de informática y aulas virtuales.

Los Autores

1

Capítulo I: Introducción a Microsoft Excel 2007

1.1. Introducción Excel es un programa del tipo Hoja de Cálculo que permite realizar operaciones con números organizados en una cuadrícula. Es útil para realizar desde simples sumas hasta cálculos de préstamos hipotecarios, pago de planillas y otros.

1.2. Elementos de Excel Vamos a ver cuáles son los elementos básicos de Excel 2007, la pantalla, las barras, etc., para saber diferenciar entre cada uno de ellos. Aprenderás cómo se llaman, dónde están y para qué sirven. También cómo obtener ayuda, por si en algún momento no sabes cómo seguir trabajando. Cuando conozcas todo esto estarás en disposición de empezar a crear hojas de cálculo. Vamos a ver las dos formas básicas de iniciar Excel 2007.

• Desde el botón Inicio : Inicio > Todos los programas > Microsoft Office > Microsoft Office Excel 2007

• Desde el icono de Excel del escritorio

2

Para cerrar Excel 2007, puedes utilizar cualquiera de las siguientes operaciones:

• Hacer clic en el botón cerrar , este botón se encuentra situado en la parte superior derecha de la ventana de Excel.

• También puedes pulsar la combinación de teclas ALT+F4, con esta combinación de teclas cerrarás la ventana que tengas activa en ese momento.

• Hacer clic sobre el menú Botón Office y elegir la opción Salir.

La pantalla inicial

Al iniciar Excel aparece una pantalla inicial como ésta, vamos a ver sus componentes fundamentales, así conoceremos los nombres de los diferentes elementos y será más fácil entender el resto del libro.

Las Barras

La barra de titulo

Contiene el nombre del documento sobre el que se está trabajando en ese momento. Cuando creamos un libro nuevo se le asigna el nombre provisional Libro1, hasta que lo guardemos y le demos el nombre que queramos. En el extremo de la derecha están los botones para minimizar, restaurar y cerrar.

3

La barra de acceso rápido

La barra de acceso rápido contiene las operaciones más habituales de Excel como Guardar, Deshacer o Rehacer.

Esta barra puede personalizarse para añadir todos los botones que quieras. Para ello haz clic en la flecha desplegable de la derecha y selecciona la opción de acceso rápido que desees.

La Banda de Opciones

• La Banda de opciones contiene todas las opciones del programa agrupadas en pestañas. Al hacer clic en Insertar, por ejemplo, veremos las operaciones relacionadas con la inserción de los diferentes elementos que se pueden crear en Excel.

• Todas las operaciones se pueden hacer a partir de estos menús. Pero las más habituales podríamos añadirlas a la barra de acceso rápido como hemos visto en el punto anterior.

• En algunos momentos algunas opciones no estarán disponibles, las reconocerás porque tienen un color atenuado.

• Las pestañas que forman la banda pueden ir cambiando según el momento en que te encuentres cuando trabajes con Excel. Está diseñada para mostrar solamente aquellas opciones que te serán útiles en cada pantalla.

4

• Pulsando la tecla ALT entraremos en el modo de acceso por teclado. De esta forma aparecerán pequeños recuadros junto a las pestañas y opciones indicando la tecla (o conjunto de teclas) que deberás pulsar para acceder a esa opción sin la necesidad del ratón.

• Para salir del modo de acceso por teclado vuelve a pulsar la tecla ALT. • Si haces doble clic sobre cualquiera de las pestañas, la barra se minimizará para

ocupar menos espacio. • De esta forma sólo muestra el nombre de las pestañas y las opciones quedarán

ocultas. • Las opciones volverán a mostrarse en el momento en el que vuelvas a hacer clic

en cualquier pestaña

El Botón office • Haciendo clic en el botón de Office que se encuentra en la parte superior

izquierda de la pantalla podrás desplegar un menú desde donde podrás ver las acciones que puedes realizar sobre el documento, incluyendo Guardar, Imprimir o crear uno Nuevo.

• A este menú también puedes acceder desde el modo de acceso por teclado tal y como vimos para la Banda de opciones.

Este menú contiene tres tipos básicos de elementos:

• Comandos inmediatos. Se ejecutan de forma inmediata al hacer clic sobre ellos. Se reconocen porque a la derecha del nombre del comando no aparece nada.

• Opción con otro menú desplegable. Se reconocen porque tienen un triángulo a la derecha. Colocando el ratón en ese triángulo puedes acceder a otro listado de opciones. Por ejemplo, la opción Imprimir para acceder a las opciones de impresión.

La barra de fórmulas Nos muestra el contenido de la celda activa, es decir, la casilla donde estamos situados. Cuando vayamos a modificar el contenido de la celda, dicha barra variará ligeramente, pero esto lo estudiaremos más adelante.

5

La barra de etiquetas Permite movernos por las distintas hojas del libro de trabajo.

Las barras de desplazamiento Permiten movernos a lo largo y ancho de la hoja de forma rápida y sencilla, simplemente hay que desplazar la barra arrastrándola con el ratón, o hacer clic en los triángulos.

La Ayuda Tenemos varios métodos para obtener Ayuda con Excel.

• Un método consiste en utilizar la Banda de opciones, haciendo clic en el interrogante:

• Otro método consiste en utilizar la tecla F1 del teclado. Aparecerá la ventana de ayuda desde la cual tendremos que buscar la ayuda necesaria.

1.3. Empezando a trabajar con Excel Veremos cómo introducir y modificar los diferentes tipos de datos disponibles en Excel, así como manejar las distintas técnicas de movimiento dentro de un libro de trabajo para la creación de hojas de cálculo.

Conceptos Básicos

Libro de trabajo es el archivo que creamos con Excel, es decir, todo lo que hacemos en este programa se almacenará formando el libro de trabajo. Los libros de

6

trabajo de Excel tienen la extensión .XLS para que el ordenador los reconozca como tal.

Hoja de cálculo es uno de los distintos tipos de hojas que puede contener un libro de trabajo. Es como una gran hoja cuadriculada formada por 16384 columnas y 1.048.576 filas. Las hojas de cálculo están formadas por columnas y filas.

Columna es el conjunto de celdas seleccionadas verticalmente. Cada columna se nombra por letras, por ejemplo A, B, C,.......AA, AB,........IV.

Fila se numera desde 1 hasta 1.048.576 y es la selección horizontal de un conjunto de celdas de una hoja de datos.

Celda La intersección de una columna y una fila se denomina Celda y se nombra con el nombre de la columna a la que pertenece y a continuación el número de su fila. Cuando el cursor está posicionado en alguna celda preparado para trabajar con ésta, dicha celda se denomina Celda activa y se identifica porque aparece más remarcada que las demás. De igual forma tenemos la fila activa y columna activa, que son de la celda activa.

Rango es un bloque rectangular de una o más celdas que Excel trata como una unidad. Los rangos son vitales en la Hoja de Cálculo, ya que todo tipo de operaciones se realizan a base de rangos.

7

Movimiento rápido en la hoja Tan solo una pequeña parte de la hoja es visible en la ventana de documento. Nuestra hoja, la mayoría de las veces, ocupará mayor número de celdas que las visibles en el área de la pantalla y es necesario moverse por el documento rápidamente. Cuando no está abierto ningún menú, las teclas activas para poder desplazarse a través de la hoja son:

MOVIMIENTO TECLADO

Celda Abajo FLECHA ABAJO

Celda Arriba FLECHA ARRIBA

Celda Derecha FLECHA DERECHA

Celda Izquierda FLECHA IZQUIERDA

Pantalla Abajo AVPAG

Pantalla Arriba REPAG

Celda A1 CTRL+INICIO

Primera celda de la columna activa FIN FLECHA ARRIBA

Última celda de la columna activa FIN FLECHA ABAJO

Primera celda de la fila activa FIN FLECHA IZQUIERDA o INICIO

Última celda de la fila activa FIN FLECHA DERECHA

1.4. Ingreso de datos En cada una de las celdas de la hoja, es posible introducir textos, números o fórmulas. En todos los casos, los pasos a seguir serán los siguientes:

Situar el cursor sobre la celda donde se van a introducir los datos y teclear los datos que desees introducir.

Aparecerán en dos lugares: en la celda activa y en la Barra de Fórmulas, como puedes observar en el dibujo siguiente:

8

Para introducir el valor en la celda puedes utilizar cualquiera de los tres métodos que te explicamos a continuación: INTRO: Se valida el valor introducido en la celda y además la celda activa pasa a ser la que se encuentra justo por debajo. TECLAS DE MOVIMIENTO: Se valida el valor introducido en la celda y además la celda activa cambiará dependiendo de la flecha pulsada, es decir, si pulsamos FLECHA DERECHA será la celda contigua hacia la derecha.

CUADRO DE ACEPTACIÓN: Es el botón de la barra de fórmulas, al hacer clic sobre él se valida el valor para introducirlo en la celda pero la celda activa seguirá siendo la misma.

• Si antes de introducir la información cambias de opinión y deseas restaurar el contenido de la celda a su valor inicial, sólo hay que pulsar la tecla Esc del teclado o hacer clic sobre el botón Cancelar de la barra de fórmulas. Así no se introducen los datos y la celda seguirá con el valor que tenía.

• Si hemos introducido mal una fórmula posiblemente nos aparezca un recuadro dándonos información sobre el posible error cometido, leerlo detenidamente para comprender lo que nos dice y aceptar la corrección o no. Otras veces la fórmula no es correcta y no nos avisa, pero aparecerá algo raro en la celda, comprobar la fórmula en la barra de fórmulas para encontrar el error.

Tipos de Datos En una Hoja de Cálculo, los distintos TIPOS DE DATOS que podemos introducir son: VALORES CONSTANTES, es decir, un dato que se introduce directamente en una celda. Puede ser un número, una fecha u hora, o un texto. FÓRMULAS, es decir, una secuencia formada por: valores constantes, referencias a otras celdas, nombres, funciones, u operadores. Es una técnica básica para el análisis de datos. Se pueden realizar diversas operaciones con los datos de las hojas de cálculo como +, -, x, /, Sen, Cos, etc... En una fórmula se pueden mezclar constantes, nombres, referencias a otras celdas, operadores y funciones. La fórmula se escribe en la barra de fórmulas y debe empezar siempre por el signo =. Para escribir dos líneas dentro de una celda Seleccione la celda, escriba el texto de la primera línea, presione las teclas Alt + Enter

1.5. Errores Cuando introducimos una fórmula en una celda puede ocurrir que se produzca un error. Dependiendo del tipo de error puede que Excel nos avise o no.

9

Cuando nos avisa del error, el cuadro de diálogo que aparece tendrá el aspecto que se muestra:

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 como que nos permitirá saber más sobre el error.

Puede que al introducir la fórmula nos aparezca como contenido de la celda #TEXTO , siendo TEXTO un valor que puede cambiar dependiendo del tipo de error. Por ejemplo:

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

#¡NUM! cuando se ha introducido un tipo de argumento o de operando incorrecto, como puede ser sumar textos.

#¡DIV/0! cuando se divide un número por cero. #¿NOMBRE? cuando Excel no reconoce el texto de la fórmula. #N/A cuando un valor no está disponible para una función o fórmula. #¡REF! se produce cuando una referencia de celda no es válida. #¡NUM! cuando se escriben valores numéricos no válidos en una fórmula o

función. #¡NULO! cuando se especifica una intersección de dos áreas que no se

intersectan. 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: . Este símbolo se utilizará como hemos visto antes.

1.6. Manejo de archivos Vamos a ver las operaciones referentes a archivos como abrir, nuevo, guardar, guardar como y cerrar para poder manejarlas sin problemas a la hora de trabajar con libros de trabajo de Excel. Guardar un libro de Trabajo Cuando empezamos a crear un libro de trabajo y queremos poder recuperarlo en otra ocasión para modificarlo, imprimirlo, en fin, realizar cualquier operación posterior sobre éste, tendremos que almacenarlo, esta operación se denomina

10

Guardar. También cuando tengamos un libro ya guardado y lo modifiquemos, para que los cambios permanezcan deberemos guardar el libro antes de cerrar. Para almacenar un libro de trabajo, podemos utilizar varios métodos.

Un método consiste en almacenar el archivo asignándole un nombre: Haz clic el Botón Office y elige la opción Guardar como...

Aparecerá el siguiente cuadro de diálogo:

En el recuadro Guardar en haz clic sobre la flecha de la derecha para seleccionar la unidad dónde vas a grabar tu trabajo.

Observa como en el recuadro inferior aparecen las distintas subcarpetas de la unidad seleccionada.

Haz doble clic sobre la carpeta donde guardarás el archivo.

En el recuadro Nombre de archivo, escribe el nombre que quieres ponerle a tu archivo.

y por último haz clic sobre el botón Guardar Otro método consiste en almacenar el archivo con el mismo nombre que tenía antes de la modificación. Para ello:

• Selecciona la opción Guardar del Botón Office.

• O bien, haz clic sobre el botón Guardar de la Barra de Acceso Rápido, se guardará con el mismo nombre que tenía. También puedes utilizar la combinación de teclas Ctrl + G.

11

Si el archivo era nuevo, aparecerá el cuadro de diálogo Guardar como... que nos permitirá darle nombre y elegir la ruta donde lo vamos a guardar. Cerrar un libro de Trabajo

Una vez que hayamos terminado de trabajar con un archivo, convendrá salir de él. La operación de salir de un documento recibe el nombre de Cierre del documento. Se puede cerrar un documento de varias formas. Una de ellas consiste en utilizar el Botón Office

• Selecciona el Botón Office y elige la opción Cerrar. En caso de detectar un archivo al cual se le ha realizado una modificación no almacenada, Excel nos avisará de ello mostrándonos el siguiente cuadro de diálogo:

Haz clic sobre el botón:

Cancelar para no cerrar el documento. No para salir del documento sin almacenar las

modificaciones realizada desde la última vez que guardamos.

Sí para almacenar el documento antes de salir de él. En este último caso, si el archivo no tuviese aún ningún nombre, aparecerá el cuadro de diálogo Guardar como para poder asignarle un nombre, en caso contrario, se almacenará con el nombre que tenía. Otra forma consiste en utilizar el botón Cerrar de la barra de menú, cuidado no el de la barra de título que cerraría el programa Excel.

Empezar un nuevo libro de trabajo Cuando entramos en Excel automáticamente se inicia un libro de trabajo vacío, pero supongamos que queremos crear otro libro nuevo, la operación se denomina Nuevo. Para empezar a crear un nuevo libro de trabajo, seguir los siguientes pasos:

Selecciona el Botón Office y elige la opción Nuevo.

O bien utilizar la combinación de teclas CTRL+U.

En el cuadro que aparecerá deberás seleccionar

Libro en blanco y hacer clic en Crear

12

Abrir un libro de trabajo existente Si queremos recuperar algún libro de trabajo ya guardado, la operación se denomina Abrir. Para abrir un archivo ya existente selecciona la opción

Abrir del Botón Office. Aparecerá el cuadro de diálogo siguiente:

Debemos buscar la ubicación donde se encuentra el archivo Luego Haz clic sobre el archivo deseado y después sobre el botón Abrir.

1.7. Formatos Seleccionar Celdas, Filas, Columnas Para Seleccionar se puede usar:

• Con el Mouse hacer clic en la celda inicial manteniendo presionado hasta donde deseemos seleccionar.

• Con el teclado manteniendo presionada la tecla Shift y con ayuda de las teclas de dirección selecciones las celdas.

• Para seleccionar celdas no consecutivas seleccione arrastrando el mouse con la tecla control.

Formato a las columnas y filas Ancho de la columna

13

• Por defecto el ancho de la columna es de 10.71. Para poder modificar el ancho debe realizar cualquiera de los siguientes pasos: Haga clic en el borde de la columna y luego Arrastre el ratón

• Seleccionando la celda o columna hacemos clic en el menú Formato y elegir la

opción ancho de columna • Luego elegir ancho

• En la ventana mostrada cambie el tamaño Formato de celdas En Microsoft Excel, puede utilizar los formatos de número para cambiar el aspecto de los números, incluyendo las fechas y las horas, sin cambiar el número que aparece detrás. El formato de número que aplique, no afectará al valor real de la celda - mostrado en la barra de fórmulas - que utiliza Excel para realizar los cálculos. Seleccionamos la celda o rango y luego realizamos un anticlic y elegimos formato de celdas y obtenemos la siguiente ventana:

Seleccionamos la opción que deseamos para nuestra celda.

1.8. Referencias Una referencia identifica una celda o un rango de celdas en una hoja de cálculo e indica a Excel en que celdas debe buscar los valores o los datos que desea utilizar en una fórmula. Referencias relativas Cuándo se crea una formula, normalmente las referencias de celda o de rango en su posición relativa respecto a la celda que contiene la formula.

14

En el siguiente ejemplo, La celda B2 contiene la formula=A1*2, Excel buscara el valor de la celda A1 y luego multiplicará por 2. Este método se denomina referencias relativas. La característica adicional que se tiene en las referencias relativas es que cuando realizamos una copia de la fórmula para otras celdas esta se incrementa automáticamente en el número de celda, ya sea a nivel de fila o columna. Referencias absolutas Las referencias absolutas identifican las celdas por su posición en la hoja de cálculo. Para realizar una referencia absoluta realice los siguientes pasos: Al realizar la formula ponga el símbolo dólar de la siguiente manera: Si deseamos la C4 como absoluta debe escribirse así: $C$4 La característica adicional que se tiene en las referencias absolutas es que cuando realizamos una copia de la fórmula para otras celdas esta no varía ya sea a nivel de fila o columna. Referencias mixtas Son las referencias donde se combinan las referencias relativas y absolutas. =$A1 La fila A es absoluta, la columna 1 es relativa =A$1 La fila A es relativa, la columna 1 es absoluta Si presiona la tecla F4, entonces la referencia a la celda se hará absoluta, si vuelve a presionar F4 se vuelve mixta relativa a la columna y absoluta la fila, si vuelve a presionar F4 se vuelve mixta relativa a la fila y absoluta la columna. La característica que se tiene en las referencias mixtas es que cuando realizamos una copia de la fórmula para otras celdas la absoluta no varía y la relativa si..

15

Práctica

1. Realizar lo siguiente:

MUEBLERÍA LAS DOS TORRES

De: Armando Torres

Pje. Los Pepinos 245 – Huancayo

Nº CODIGO CLIENTE TELEF PAGO

1 a100 Carlos 252426 100

2 a101 Carmen 234256 250,3

3 a102 Pedro 223456 24,36

4 a103 Javier 213456 245,25

5 a104 Paul 245636 246,12

6 a105 Rosa 234563 564,32

2. Utilizando formato de celdas realizar

A B D E F

1 1548.4563 12.30 S/. 100.00 121 15%

2 231.0215 78.64 S/.14983.56 10 20%

3 45678.1246 472.56 S/.14589.26 485 25%

4 146.2356 10.00 S/.48573.25 145 36.5%

5 4687530.2344 100.00 S/.1458.36 12647 14%

6 3542.4892 451.45 S/.136.59 12 18.45%

7 35453.1254 17.01 S/.789.15 144 13%

8 1.0231 0.02 S/.145.50 46 14.5%

9 18.0154 45.78 S/.154.05 10 98.9%

16

3. Considerar que la comisión es el 25% de las ventas y que este varía mensualmente por lo que debe considerar la opción de modificar una sola vez y actualizarse.

EMPRESA ADUANERA DEL MANTARO CODIGO NOMBRE Y APELLIDO VENTAS COMISION

Z-1A LUIS GUILLEN $300.00 $75.00

Z-2A MARIA ARAUJO $200.00 $50.00

Z-3A CARMEN HINOJOSA $500.00 $125.00

Z-4A FELIX GUEVARA $100.00 $25.00

Z-5A ELMO FLORES $200.00 $50.00

Z-6A NAYVET CASTRO $300.00 $75.00

Z-7A CAROLINA BERNALES $59.00 14,75

Z-8A EMMA MACHACA $1,000.00 $250.00

Z-9A LUIS FONSECA $248.00 $62.00

Z-10A CLAUDIA CEVALLOS $100.00 $25.00

17

Capítulo II: Formulas y Funciones

Este capítulo es uno de los más importantes del curso, pues en su comprensión y manejo

está la base de Excel. Qué es una hoja de cálculo sino una base de datos que utilizamos

con una serie de fórmulas para evitar tener que recalcular por cada cambio que

hacemos. Por eso este tema es fundamental para el desarrollo del libro y la buena

utilización de Excel.

Vamos a profundizar en el manejo de funciones ya definidas por Excel 2007 para

agilizar la creación de hojas de cálculo, estudiando la sintaxis de éstas así como el uso

del asistente para funciones, herramienta muy útil cuando no conocemos muy bien las

funciones existentes o la sintaxis de éstas.

2.1 Introducir Fórmulas y Funciones

Una función es una fórmula predefinida por Excel 2007 (o por el usuario) que opera

con uno o más valores y devuelve un resultado que aparecerá directamente en la

celda o será utilizado para calcular la fórmula que la contiene.

La sintaxis de cualquier función es:

nombre_función (argumento1; argumento2;...;argumento N)

Siguen las siguientes reglas:

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

Los argumentos o valores de entrada van siempre entre paréntesis. No dejes

espacios antes o después de cada paréntesis.

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

funciones.

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

Ejemplo: =SUMA(A1:C5)

Tenemos la función SUMA() que devuelve como resultado la suma de sus

argumentos. El operador ":" nos identifica un rango de celdas, así A1:C5 indica

todas las celdas incluidas entre la celda A1 y la C5, así la función anterior sería

equivalente a: =A1+A2+A3+A4+A5+B1+B2+B3+B4+B5+C1+C2+C3+C4+C5

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

18

• Las fórmulas pueden contener más de una función, y pueden aparecer

funciones anidadas dentro de la fórmula.

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

• Existen muchos tipos de funciones dependiendo del tipo de operación o cálculo

que realizan. Así hay funciones matemáticas y trigonométricas, estadísticas,

financieras, de texto, de fecha y hora, lógicas, de base de datos, de búsqueda y

referencia y de información.

• Para introducir una fórmula debe escribirse en una celda cualquiera tal cual

introducimos cualquier texto, precedida siempre del signo =.

2.1.1 Operadores más utilizados en las Fórmulas y Funciones

Excel permite que en una función tengamos varios operadores para tratar los datos. Los

operadores son símbolos que identifica Excel con operaciones aritméticas y es el enlace

entre 2 argumentos.

Operadores más utilizados.

SIMBOLO DEL OPERADOR OPERACIÓN QUE REALIZA

+ SUMA

- RESTA

* MULTIPLICA

/ DIVIDE

^ EXPONENCIACIÓN

& UNIÓN / CONCATENAR

= Comparación IGUAL QUE

> Comparación MAYOR QUE

< Comparación MENOR QUE

>= Comparación MAYOR IGUAL QUE

<= Comparación MENOR IGUAL QUE

<> Comparación DISTINTO

En una fórmula o función pueden utilizarse tanto operadores como sea necesario

teniendo en cuenta siempre que los operadores hacen siempre referencia a dos

argumentos.

Pueden crearse fórmulas verdaderamente complejas. Veamos un ejemplo.

19

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

= (F8*SUMA(G1:G5))

Precedencia de los Operadores

Hemos visto que una fórmula puede ser muy compleja, utilizando multitud de

operadores. Excel como cualquier operador matemático tiene unas ciertas reglas para

saber que operaciones debe realizar primero para que el resultado obtenido sea el

correcto

SIMBOLO DEL

OPERADOR

OPERACIÓN QUE REALIZA PRECEDENCIA

^ EXPONENCIACIÓN 1

* MULTIPLICA 2

/ DIVIDE 2

+ SUMA 3

- RESTA 3

& UNIÓN / CONCATENAR 4

= Comparación IGUAL QUE 5

> Comparación MAYOR QUE 5

< Comparación MENOR QUE 5

>= Comparación MAYOR IGUAL QUE 5

<= Comparación MENOR IGUAL QUE 5

<> Comparación DISTINTO 5

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

2.2 Funciones

Insertar una función con el asistente

Una función como cualquier dato se puede escribir directamente en la celda si

conocemos su sintaxis, pero Excel 2007 dispone de una ayuda o asistente para

utilizarlas, así nos resultará más fácil trabajar con ellas. Si queremos introducir una

función en una celda:

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

• Hacer clic en la pestaña Fórmulas.

• Elegir la opción Insertar función

20

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

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

• Excel 2007 nos permite buscar la función que necesitamos escribiendo una

breve descripción de la función necesitada en el recuadro Buscar una función: y

a continuación hacer clic sobre el botón, de esta forma no es necesario conocer

cada una de las funciones que incorpora Excel ya que el nos mostrará en el

cuadro de lista Seleccionar una función: las funciones que tienen que ver con la

descripción escrita.

• Para que la lista de funciones no sea tan extensa podemos seleccionar

previamente una categoría del cuadro combinado O seleccionar una categoría,

esto hará que en el cuadro de lista sólo aparezcan las funciones de la categoría

elegida y reduzca por lo tanto la lista. Si no estamos muy seguros de la categoría

podemos elegir Todas.

• En el cuadro de lista Seleccionar una función: hay que elegir la función que

deseamos haciendo clic sobre ésta.

21

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

2.3 Insertar funciones con el Asistente

Una vez hecho el paso anterior, aparecerá el cuadro de diálogo Argumentos de

función, donde nos pide introducir los argumentos de la función: Este cuadro variará

según la función que hayamos elegido, en nuestro caso se eligió la función SUMA ().

• En el recuadro Número1 hay que indicar el primer argumento que generalmente

será una celda o rango de celdas tipo A1:B4 . Para ello, hacer clic sobre el botón

para que el cuadro se haga más pequeño y podamos ver toda la hoja de cálculo, a

continuación seleccionar el rango de celdas o la celda deseadas como primer

argumento (para seleccionar un rango de celdas haz clic con el botón izquierdo

del ratón sobre la primera celda del rango y sin soltar el botón arrástralo hasta la

última celda del rango) y pulsar la tecla INTRO para volver al cuadro de

diálogo.

22

• En el recuadro Número2 habrá que indicar cuál será el segundo argumento. Sólo

en caso de que existiera. Si introducimos segundo argumento, aparecerá otro

recuadro para el tercero, y así sucesivamente.

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

Utilizar Funciones como argumentos de las Funciones

Excel también permite que una función se convierta en argumento de otra función,

de esta forma podemos realizar operaciones realmente complejas en una simple

celda.

Por ejemplo =MAX(SUMA(A1:A4);B3) , esta fórmula consta de la combinación de

dos funciones, la suma y el valor máximo. Excel realizará primero la suma

SUMA(A1:A4) y después calculará el valor máximo entre el resultado de la suma y

la celda B3.

2.4 Funciones de fecha

De entre todo el conjunto de funciones, en este apartado estudiaremos las funciones

dedicadas al tratamiento de fechas y horas.

Y estas son las funciones más utilizadas y ofrecidas por Excel

Función Descripción

AHORA() Devuelve la fecha y la hora actual

AÑO(núm_de_serie) Devuelve el año en formato año

DIA(núm_de_serie) Devuelve el día del mes

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

DIASEM(núm_de_serie;tipo) Devuelve un número del 1 al 7

FECHA(año;mes;día) Devuelve la fecha en formato fecha

FECHANUMERO(texto_de_fecha) Devuelve la fecha en formato de fecha

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

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

HOY() Devuelve la fecha actual

MES(núm_de_serie) Devuelve el número del mes en el rango del 1

(enero) al 12 (diciembre)

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

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

números

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

23

2.5 Funciones de texto

Una hoja de cálculo está pensada para manejarse dentro del mundo de los números,

pero Excel también tiene un conjunto de funciones específicas para la manipulación

de texto.

Estas son todas las funciones de texto ofrecidas por Excel.


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

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

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

DECIMAL(número;decimales;no_separar_millar

es)

Redondea un número pasado como parámetro

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

ENCONTRAR(texto_buscado;dentro_del_texto;n

úm_inicial)

Devuelve la posición inicial del texto buscado

ESPACIOS(texto) Devuelve el mismo texto pero sin espacios

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

HALLAR(texto_buscado;dentro_del_texto;núm_i

nicial)

Encuentra una cadena dentro de un texto

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

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

LARGO(texto) Devuelve la longitud del texto

LIMPIAR(texto) Limpia el texto de caracteres no imprimibles

MAYUSC(texto) Convierte a mayúsculas

MINUSC(texto) Convierte a minúsculas

MONEDA(número;núm_de_decimales) Convierte a moneda

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

REEMPLAZAR(texto_original;num_inicial;núm

_de_caracteres;texto_nuevo)

Reemplaza parte de una cadena de texto por otra

REPETIR(texto;núm_de_veces) Repite el texto

SUSTITUIR(texto;texto_original;texto_nuevo;nú

m_de_ocurrencia)

Reemplaza el texto con texto nuevo

T(valor) Comprueba que el valor es texto

TEXTO(valor;formato) Convierte un valor a texto

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

VALOR(texto) Convierte un texto a número

Otras Funciones

24

Además de las funciones anteriormente mencionadas, existe un gran abanico de

funciones de diferentes categorías que nos pueden ser de gran utilidad.

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

ABS(número) Devuelve el valor absoluto de un número ALEATORIO() Devuelve un número entre 0 y 1 COMBINAT(número;tamaño) Devuelve el número de combinaciones para un

número determinado de elementos COS(número) Devuelve el coseno de un ángulo ENTERO(número) Redondea un número hasta el entero inferior más

próximo EXP(número) Realiza el cálculo de elevar "e" a la potencia de un

número determinado FACT(número) Devuelve el factorial de un número NUMERO.ROMANO(número,forma) Devuelve el número pasado en formato decimal a

número Romano PI() Devuelve el valor de la constante pi POTENCIA(número;potencia) Realiza el cálculo de elevar un número a la potencia

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

todos los números pasados como argumentos RAIZ(número) Devuelve la raiz cuadrada del número indicado RESIDUO(número;núm_divisor) Devuelve el resto de la división Funciones estadísticas MEDIA.ARMO(número1;número2;...) Devuelve la media armónica de un conjunto de

números positivos MAX(número1;número2;...) Devuelve el valor máximo de la lista de valores MIN(número1;número2;...) Devuelve el valor mínimo de la lista de valores MEDIANA(número1;número2;...) Devuelve la mediana de la lista de valores MODA(número1;número2;...) Devuelve el valor que más se repite en la lista de

valores PROMEDIO(número1;número2;...) Devuelve la media aritmética de la lista de valores VAR(número1;número2;...) Devuelve la varianza de una lista de valores K.ESIMO.MAYOR(matriz;k) Devuelve el valor k-ésimo mayor de un conjunto de

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

datos Funciones lógicas

FALSO() Devuelve el valor lógico Falso VERDADERO Devuelve el valor lógico Verdadero SI(prueba_logica;valor_si_verdadero;valor_si_falso) Devuelve un valor u otro, según se cumpla o no una

condición NO(valor_lógico) Invierte el valor lógico proporcionado Y(valor_logico1;valor_logico2;...) Comprueba si todos los valores son verdaderos O(valor_logico1;valor_logico2;...) Comprueba si algún valor lógico es verdadero y

devuelve VERDADERO Funciones de información

ESBLANCO(valor) Comprueba si se refiere a una celda vacía ESERR(valor) Comprueba si un valor es un error ESLOGICO(valor) Comprueba si un valor es lógico ESNOTEXTO(valor) Comprueba si un valor no es de tipo texto ESTEXTO(valor) Comprueba si un valor es de tipo texto ESNUMERO(valor) Comprueba si un valor es de tipo numérico TIPO(valor) Devuelve un número que representa el tipo de datos

del valor

25

2.6 Funciones Aritméticas y estadísticas

Son fórmulas predeterminas que ejecutan cálculos utilizando valores específicos,

denominados argumentos, en un orden determinado o estructura.

Funciones Elementales

Suma : Suma todo los números de un rango

Sintaxis: SUMA(num1;num2;...)

Promedio: Devuelve el promedio (media aritmética) de los argumentos

Sintaxis: PROMEDIO(num1;num2;..)

Max: Devuelve el valor máximo de un conjunto de valores

Sintaxis: MAX(num1;num2;...)

Min: Devuelve el valor mínimo de un conjunto de valores

Sintaxis: MIN(num1;num2;...)

Contar.Si: Cuenta las celdas dentro del rango, que no están en blanco y que

cumplen con el criterio especificado

Sintaxis: CONTAR.SI(rango;criterio)

Sumar.Si: Suma las celdas en el rango que coincide con el argumento criterio

Sintaxis: SUMAR.SI(rango;criterio;rango suma)

Contar: Cuenta el número de celdas que contiene números y los números en la

lista de argumentos.

Sintaxis : CONTAR(ref1;ref2;....)

2.7 Funciones Lógicas

Las funciones lógicas son importantes porque a través de una expresión se puede

evaluar datos o valores que permiten responder a muchas interrogantes.

FUNCIÓN O: Devolverá Verdadero si alguno de los argumentos es Verdadero;

devolverá Falso si todos los argumentos son Falsos

Sintaxis: O(valor lógico1; valor lógico2;...)

FUNCIÓN Y: Devolverá Verdadero si alguno de los argumentos es Verdadero;

devolverá Falso si uno o más argumentos es Falso

Sintaxis: Y(valor lógico1; valor lógico2;...)

FUNCIÓN NO: Invierte el valor lógico del argumento. Use NO cuando desee

asegurarse de que un valor no es igual a otro valor específico

Sintaxis: NO(valor lógico1)

26

FUNCIÓN SI: Esta función devuelve un valor si la condición especificada es

Verdadera y otro valor si dicho argumento es falso

Sintaxis: SI(Prueba_lógica;valor_si_verdadero; valor_si_falso)

27

Práctica Tenemos los siguientes datos: 1. Hallar:

¿Cuántos puntos acumularon en word? Calcular el promedio general de todos Hallar la más alta nota en los promedios Hallar la más baja nota en los promedios Cuantos alumnos aprobaron en el promedio final Cuantos puntos acumulan los alumnos aprobados en el promedio final.

En otra parte de la hoja colocar el nombre y apellido juntos y deben estar en mayúsculas, luego colocar su promedio y agregar un campo donde coloquemos la fecha actual

2. Hallar:

• Cuanto aprobaron Word o Excel • Cuantos aprobaron Word y Excel

Considerar verdadero como aprobado y falso como desaprobado 3. Elaborar el siguiente trabajo

Se debe evaluar dos números que existen en 2 celdas independientes y colocar los signos correspondientes; para ello se recomienda utilizar la Función Si.

IMPORTANTE

COMPARACION DE NUMEROS 14 < 15

15 < 17

20 > 15

40 > 32

65 > 12

2 < 17

87 = 87

CÓDIGO NOMBRES APELLIDOS NOTAS

WINDOWS WORD EXCEL PROM

100 César Cardenas 12 14 13

200 Marco Quispe 13 15 14

300 Martha Torres 10 14 15

400 Karina Conde 15 14 11

500 Jaime Ponce 8 13 11

600 Andrea Quispe 10 10 12

700 Jose Rios 7 12 10

800 Miguel Ponce 14 12 18

900 Maria Villa 11 17 16

1000 Ronald Fuentes 13 14 13

1100 Carmen Porta 10 11 10

1200 Jose Linares 10 15 14

28

4. Se tienen los siguientes datos. En la celda PROM se debe hallar el promedio de las cuatro notas (N1,N2,N3,N4)y debe estar redondeado. En la celda Observaciones se debe colocar un mensaje dependiendo del promedio

COD NOMBRE N1 N2 N3 N4 PROM OBSERVACION

AB01 CAROLINA 12 15 16 20 16 BIEN

AB02 ELMO 11 5 16 0 8 GRAVE

AB03 CARMEN 20 20 20 19 20 MUY BIEN

AB04 FELIX 14 17 15 12 15 REGULAR

AB05 ELIANA 17 18 19 18 18 BIEN

AB06 NAYVET 8 5 6 5 6 GRAVE

AB07 EMMA 12 13 12 14 13 REGULAR

AB08 CLAUDIA 11 13 12 11 12 REGULAR

AB09 MILAGRITO 16 15 18 14 16 BIEN

AB10 MYRIAM 18 19 18 17 18 BIEN

AB11 JESSICA 20 20 18 19 19 MUY BIEN

AB12 MARIA 5 14 15 12 12 REGULAR

Para ello se debe de tomar en cuenta: • Para las condiciones se debe considerar

Muy Bien (20 a 18) Bien (17 a 15) Regular (11 a 14) Grave (0 a 10)

5. Se tiene los siguientes datos:

Se pide: Calcular el descuento a las compras de CONTADO únicamente Calcular el valor del IGV solamente para los comprobantes A, previa resta del descuento si corresponde

Calcular el Total de cada compra y el total general. El resultado debe ser:

29

2.8 Funciones de Búsqueda

En una hoja de Excel es muy importante coger los datos correctos para trabajar con

las fórmulas diseñadas. Por eso existe una agrupación de funciones específicas para

realizar búsquedas de datos.


AREAS(ref) Devuelve el número de rangos de celdas contiguas BUSCAR(...) Busca valores de un rango de una columna o una fila BUSCARH(valor_buscado;matriz_buscar_en;indicador_filas;ordenado)

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

BUSCARV(valor_buscado;matriz_buscar_en;indicador_columnas;ordenado)

Busca un valor en la primera columna de la izquierda

COINCIDIR(valor_buscado;matriz_buscar_en;tipo_de_coincidencia)

Devuelve la posición relativa de un elemento

COLUMNA(ref) Devuelve el número de columna de una referencia COLUMNAS(matriz) Devuelve el número de columnas que componen la

matriz DESREF(ref;filas;columnas;alto;ancho)

Devuelve una referencia a un rango

DIRECCION(fila;columna;abs;a1;hoja)

Crea una referencia de celda en forma de texto

ELEGIR(num_indice;valor1;valor2;...)

Elige un valor o una accion de una lista de valores

FILA(ref) Devuelve el número de fila FILAS(matriz) Devuelve el número de filas HIPERvínculo(ubicación_del_vínculo;nombre_descriptivo)

Crea un acceso directo a un documento

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

Extrae datos almacenados en una tabla dinámica

INDICE(matriz;num_fila;num_columna)

Devuelve el valor de una celda en la intersección de una fila y una columna

INDIRECTO(ref;a1) Devuelve una referencia especificada TRANSPONER(matriz) Intercambia las filas por las columnas en una matriz

2.9 Funciones Financieras

Excel es una de las herramientas más potentes para trabajar con información y

cálculos financieros, ofrece una amplia gama de funciones prediseñadas para

crearte tu propia "caja de ahorros en casa".

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

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

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

DDB(costo;valor_residual;vida;periodo;factor) Devuelve la depreciación de un bien para un período especificado, mediante el método de depreciación por doble disminución de saldo

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

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

30

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

NPER(tasa;pago;va;vf;tipo) Devuelve el número de pagos de una inversión PAGO(tasa;nper;va;vf;tipo) Devuelve el pago de un préstamo basado en

pagos y tasas de interés constantes PAGOINT(tasa;periodo;nper;va;vf;tipo) Devuelve el interés pagado por una inversión

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

determinada SLN(costo;valor_residual;vida) Devuelve la depreciación por método directo de

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

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

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

TIR(valores;estimar) Devuelve la tasa interna de retorno de una inversión para una serie de valores en efectivo

TIRM(valores;tasa_financiamiento;tasa_reinversión)

Devuelve la tasa interna de retorno modificada

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

basada en pagos periódicos y constantes más una tasa de interés constante

VNA(tasa;valor1;valor2;...) Devuelve el valor neto actual de una inversión a partir de una tasa de descuentos y una serie de pagos futuros

2.10 Función BUSCARV

Sintaxis:

BUSCARV(valor_buscado;matriz_buscar_en;indicador_columnas;ordenado)

Valor_buscado es el valor que se busca, en la mayoría de situaciones es una referencia a una celda. Matriz_buscar_en es una tabla de información en la que se buscan los datos, en la mayoría de situaciones es un rango de datos. Se recomienda una referencia a un rango o el nombre de un rango. 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, BUSCARV devuelve al valor de error #VALUE!

Si es superior al número de columnas de matriz_buscar_en, BUSCARV

devuelve el valor de error #REF!

31

Ordenado Valor lógico que especifica si BUSCARV va a buscar una coincidencia

exacta o aproximada.

Ejemplo 1: Se busca en la columna Densidad de una tabla de propiedades

atmosféricas los valores correspondientes de las columnas Viscosidad y Temperatura

(los valores se refieren a aire a 0 grados Celsius al nivel del mar, o 1 atmósfera).

En la parte inferior de la Hoja realicemos la búsqueda de la Viscosidad y Temperatura

de 1.09 y luego debemos mostrarlo en otras celdas.

Ejemplo 2: En este ejemplo, se buscan valores en la columna Id. de artículo de una

tabla de productos para bebés y se les asignan los valores correspondientes de las

columnas Costo y Marcas para calcular los precios y las condiciones de prueba

32

Vamos a calcular el precio minorista de un producto sobrecargándole la marca de costo

que se le aplica al producto en este caso a los pañales.

Vamos a calcular el precio minorista de un producto sobrecargándole la marca de costo

que se le aplica al producto en este caso a los pañales

2.11 Función BUSCARH

Busca un valor en la fila superior de una tabla o una matriz de valores y, a continuación, devuelve un valor en la misma columna de una fila especificada en la tabla o matriz. Use BUSCARH cuando los valores de comparación se encuentren en una fila en la parte superior de una tabla de datos y desee encontrar información que se encuentre dentro de un número especificado de filas. Use BUSCARV cuando los valores de comparación se encuentren en una columna a la izquierda de los datos que desee encontrar. La H de BUSCARH significa "Horizontal". Sintaxis: BUSCARH(valor_buscado;matriz_buscar_en;indicador_filas; ordenado) Valor_buscado es el valor que se busca en la primera fila de la tabla. Matriz_buscar_en es una tabla de información en la que se buscan los datos. Utilice una referencia a un rango o el nombre de un rango. Indicador_filas es el número de fila en matriz_buscar_en desde el cual debe devolverse el valor coincidente. Si indicador_filas es 1, devuelve el valor de la primera fila en matriz_buscar_en; si indicador_filas es 2, devuelve el valor de la

33

segunda fila en matriz_buscar_en y así sucesivamente. Si indicador_filas es menor que 1, BUSCARH devuelve el valor de error #¡VALOR!; si indicador_filas es mayor que el número de filas en matriz_buscar_en, BUSCARH devuelve el valor de error #¡REF!

Ordenado es un valor lógico que especifica si BUSCARH debe localizar una coincidencia exacta o aproximada.

Ejemplo: Vamos a buscar en la primera fila algún nombre y mostraremos el dato de la fila que indiquemos.

Busca Pernos en la fila 1 y devuelve el valor de la fila 3 que está en la misma columna (10)

34

Practica

La Empresa “RH” dedicada al Rubro de Importación y Exportación de Productos en general, desea automatizar los procesos que realiza. La Empresa cuenta actualmente con 50 trabajadores y dentro de los primeros procesos que desea automatizar es el de un Sistema para el Control de Pagos, para lo cual ha contratado los Servicios de un Profesional de Computación el cual determinó las siguientes especificaciones: Consideraciones El Programa que se determinó utilizar será MICROSOFT EXCEL, ya que se maneja poca información y dado el conocimiento de su personal de la Suite Office. El Sistema de Planillas será almacenado en el archivo PLANILLA.XLSX y el análisis ha determinado las siguientes consideraciones:

CALCULO DE LOS INGRESOS SUELDO BASICO Se determinará según su Categoría

Si su CATEGORIA es "A" su SUELDO BASICO será S/. 1,500.00

Si su CATEGORIA es "B" su SUELDO BASICO será S/. 1,200.00

Si su CATEGORIA es "C" su SUELDO BASICO será S/. 800.00

Si su CATEGORIA es "D" su SUELDO BASICO será S/. 500.00

Se recomienda usar la función lógica =BUSCARV(...

BONIFICACION x CATEGORIA Se determinará según su Categoría Si su CATEGORIA es "A" su BONIFICACION x CATEGORIA será 20% del SUELDO BASICO

Si su CATEGORIA es "B" su BONIFICACION x CATEGORIA será 15% del SUELDO BASICO

Si su CATEGORIA es "C" su BONIFICACION x CATEGORIA será 12% del SUELDO BASICO

Si su CATEGORIA es "D" su BONIFICACION x CATEGORIA será 8% del SUELDO BASICO

Se recomienda usar la función lógica =BUSCARV(... REFRIGERIO Por política de la empresa se le asignará a todos los

trabajadores S/. 50.00 por REFRIGERIO

MOVILIDAD Por política de la empresa se le asignará a todos los trabajadores

5% de MOVILIDAD del SUELDO BASICO

MOVILIDAD = SUELDO BASICO * 5%

BONIFICACION x COSTO DE VIDA

Por política de la empresa se le asignará a todos los trabajadores 10% del SUELDO BASICO

BONIF. x COSTO VIDA = SUELDO BASICO * 10%

TOTAL DE INGRESOS =

BASICO + BONI CATEGORIA + REFRIGERIO + MOVILIDAD + BONI COSTO VIDA

35

CALCULO DE LAS APORTACIONES DEL EMPLEADO APORTACION DEL EMPLEADO Cuando un trabajador es contratado o estable se le descuenta por Ley un monto

para su jubilación, dependiendo dónde este afiliado: SNP o AFP SNP

Sólo se le descontará para las personas que NO estén Afiliadas a una AFP. Según Ley será el 11% del TOTAL DE INGRESOS

Se recomienda usar la función lógica =SI(... AFILIADOS A AFP

Sólo se le descontará para las personas que están Afiliadas a una AFP. Se determinará según el CODIGO de AFP a la cual están afiliadas.

Si su CODIGO AFP es 1 (INTEGRA) se le descontará el 20% del TOTAL DE INGRESOS

Si su CODIGO AFP es 2 (PROFUTURO) se le descontará el 18% del TOTAL DE INGRESOS

Si su CODIGO AFP es 3 (HORIZONTE) se le descontará el 19% del TOTAL DE INGRESOS

Se recomienda usar la función lógica =BUSCARV(...

CALCULO DEL SUELDO A PAGAR SUELDO A PAGAR = TOTAL INGRESOS - TOTAL APORTACIONES EMPLEADO

CALCULO DE LAS APORTACIONES DEL EMPLEADOR (Referencial para la Planilla)

Cuando un trabajador es contratado o estable, la Empresa va a aportar por este trabajador por: ESSALUD Prestaciones de salud 9% del Total de

Ingresos.

ESSALUD = TOTAL INGRESOS * 9%

FONAVI FONAVI (Fondo Nacional de Vivienda) 9% del Total de Ingreso

FONAVI = TOTAL INGRESOS * 9%

TOTAL APORT. EMPLEADOR = ESSALUD + FONAVI

Tabla II

CODIGO AFP % 0 0% 1 20% INTEGRA 2 18% PROFUTURO3 19% HORIZONTE

1 2

5ta. CATEGORIA Si el TOTAL DE INGRESOS es mas de S/. 1,400.00 , se le descontará el 15% sobre la cantidad excedida al TOTAL DE INGRESOS => (TOTAL INGRESOS - 1400) * 15%

Se recomienda usar la función lógica =SI(... TOTAL APORT. EMPLEADO

= SNP + AFILIADO AFP + 5ta. CATEGORIA

5ta. CATEGORIA Si el TOTAL DE INGRESOS es mas de S/. 1,400.00 , se le descontará el 15% sobre la cantidad excedida al TOTAL DE INGRESOS => (TOTAL INGRESOS - 1400) * 15%

Se recomienda usar la función lógica =SI(... TOTAL APORT. EMPLEADO

= SNP + AFILIADO AFP + 5ta. CATEGORIA

Tabla I CATEGORIA BASICO

BONI 1 x CATEGORIA

A 1,500.00 20% B 1,200.00 15% C 800.00 12% D 500.00 8%

1 2 3

36

37

Capitulo 3: Gráficos 3.1 Descripción de Gráficos

En Microsoft Office Excel 2007, es fácil crear gráficos de aspecto profesional. Sólo

con seleccionar un tipo, un diseño y estilo de gráfico (opciones de fácil acceso en la

nueva cinta de opciones de Office Excel 2007), obtendrá resultados profesionales

inmediatos cada vez que cree un gráfico. Para hacerlo aún más sencillo, puede

guardar sus gráficos favoritos como una plantilla de gráfico que podrá aplicar

rápidamente cuando cree un nuevo gráfico.

Microsoft Excel presenta los datos en un gráfico. Los gráficos se vinculan a los

datos a partir de los que se crean y se actualizan cuando se crean estos, puede crear

gráficos a partir de celdas o rangos no continuos.

3.2 Tipos de Gráficos

Microsoft Office Excel 2007 admite varios tipos de gráficos para ayudarle a

mostrar datos de forma comprensible para su audiencia. Cuando crea un gráfico o

cambia uno existente, puede elegir entre una amplia gama de subtipos de gráficos

disponibles para cada uno de los tipos de gráficos siguientes.

38

3.3 Características para utilizar algún tipo de gráfico

Cuando usar gráficos en columnas (simple y 3d)

Un gráfico en columnas muestra los cambios que han sufrido los datos en el

transcurso de un tiempo.

Cuando usar gráficos líneas

Muestra la tendencia intervalos equivalentes, se emplea para evaluar proyecciones de

ventas u otros.

39

Cuando usar gráficos circulares

Muestra en tamaño proporcional de los elementos que conforma una serie de datos

en función de la suma de los elementos.

3.4 Creación de Gráficos

Para crear un gráfico básico en Excel que pueda modificar y dar formato más

adelante, debe especificar, en primer lugar, los datos del gráfico en una hoja de

cálculo. A continuación, sólo tiene que seleccionarlos y elegir el tipo de gráfico que

desea utilizar en la cinta de opciones (ficha Insertar, grupo Gráficos).

40

Pasos:

1. Datos de la hoja de cálculo

2. Gráfico creado a partir de datos de la hoja de cálculo

Si estamos dentro de las celdas de los datos que queremos generar el gráfico, Excel lo

seleccionar automáticamente

Cuando ya hemos generado un gráfico se puede apreciar que se presenta una nueva

Pestaña en la que se presenta una serie de opciones como Diseño, Tipo de Tabla Estilos,

etc.

41

3.5 Modificar Gráficos

Después de crear un gráfico, puede modificarlo. Por ejemplo, tal vez desee cambiar

la forma en que se muestran los ejes (eje: línea que rodea el área de trazado de un

gráfico utilizada como marco de referencia de medida. El eje y suele ser el eje

vertical y contener datos. El eje x suele ser el eje horizontal y contener categorías.),

agregar un título, mover u ocultar la leyenda o mostrar otros elementos del gráfico.

Para modificar un gráfico, puede:

Cambiar la presentación de los ejes del gráfico Puede especificar la escala de los

ejes y ajustar el intervalo entre los valores o categorías que se muestran. Para facilitar

la lectura del gráfico, puede agregar también marcas de graduación (marcas de

graduación y rótulos de marcas de graduación: las marcas de graduación son líneas

pequeñas de medida, similares a las divisiones de una regla, que intersecan un eje.

Los rótulos de marcas de graduación identifican las categorías, los valores o las

series del gráfico.) a un eje y especificar el intervalo en el que van a aparecer

Agregar títulos y rótulos de datos a un gráfico Para que la información que

aparece en el gráfico sea más clara, puede agregar un título de gráfico, títulos de eje

y rótulos de datos.

Agregar una leyenda o una tabla de datos Puede mostrar u ocultar una leyenda o

cambiar su ubicación. En algunos gráficos, puede mostrar también una tabla de

datos.

42

Aplicar opciones especiales en cada tipo de gráfico Existen líneas especiales

(líneas de máximos y mínimos y líneas de tendencia (línea de tendencia:

representación gráfica de tendencias en series de datos, como una línea inclinada

ascendente para representar el aumento de ventas a lo largo de un período de meses.

Las líneas de tendencia se utilizan para el estudio de problemas de predicción, lo

que se denomina también análisis de regresión.)), y otras opciones para los

diferentes tipos de gráficos.

3.6 Utilizar herramientas predefinidas

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. Excel proporciona

43

varios diseños y estilos predefinidos de gran utilidad, pero puede adaptar un diseño o

estilo si es preciso realizando cambios manuales en el diseño o formato de los

distintos elementos del gráfico, como el área del gráfico (área de gráfico: todo el

gráfico junto con todos sus elementos.), el área de trazado (área de trazado: en un

gráfico 2D, área delimitada por los ejes, incluidas todas las series de datos.

En un gráfico 3D, área delimitada por los ejes, incluidas las series de datos, los

nombres de categoría, los rótulos de marcas de graduación y los títulos de eje.), la

serie de datos (serie de datos: puntos de datos relacionados que se trazan en un

gráfico. Cada serie de datos de un gráfico tiene una trama o color exclusivo y se

representa en la leyenda del gráfico. Puede trazar una o más series de datos en un

gráfico. Los gráficos circulares sólo tienen una serie de datos.) o la leyenda (leyenda:

cuadro que identifica los diseños o colores asignados a las series de datos o

categorías de un gráfico.).

44

Práctica 1. Con los siguientes datos presente de diversas maneras

los datos en forma de gráficos para que estos sean fácilmente entendibles.

Población

Niños Adutos Ancianos Total

Hyo 10000 20000 800 30800

Chilca 8000 15000 700 23700

Tambo 9000 17000 500 26500

10000

20000

800

8000

15000

700

9000

17000

5000

5000

10000

15000

20000

25000

Niños Adutos Ancianos

Título del eje

Población

Hyo

Chilca

Tambo

2. Varié la presentación invirtiendo las columnas y filas

3. Muestre la proporción de la población.

30800

23700

26500

Población Total

Hyo

Chilca

Tambo

45

4. Se tiene los siguientes datos se pide que realice en gráficos 3D.

Ventas

Enero Febrero Marzo

Libros 400 100 300

Revista 200 200 400

Folleto 500 300 200

Ventas Enero

Ventas Febrero

Ventas Marzo

LibrosRevista

Folleto

0

100

200

300

400

500

ITEMS

CANTIDAD

Título del gráfico

Ventas Enero

Ventas Febrero

Ventas Marzo

46

5. Muestre la tabla de datos asociada y una línea de tendencia para las ventas de febrero

Libros Revista Folleto

Ventas Enero 400 200 500

Ventas Febrero 100 200 300

Ventas Marzo 300 400 200

0

100

200

300

400

500

600

ITEMS

CANTIDAD

Ventas

Ventas Enero

Ventas Febrero

Ventas Marzo

Exponencial (Ventas Febrero)

47

Capitulo 4: Base de Datos 4.1 Base de datos

Una base de datos es una serie de registros de datos, organizados en filas y columnas.

Es una serie de filas rotuladas que contienen datos relacionados.

En esencia son los conceptos básicos de base de datos para poder trabajar en Excel

Por ejemplo: los nombres, direcciones y números de teléfono de los clientes de una

empresa, la relación de alumnos, relación de trabajadores, etc.

Campo

Cada columna de una Base de Datos es un campo individual y cada una de las celdas

de una columna es un campo. Los elementos de un campo pueden ser texto, números,

fechas, fórmulas o funciones.

Registro

Un registro es una fila individual de una base de datos. Todos los registros de una

base de datos contienen las mismas categorías de datos

Se llama registro a todo el conjunto de campos (fila), y a todo el conjunto de

registros se le llama Base de datos, y algunos lo llaman tabla.

Una tabla se puede usar para:

Buscar datos específicos

Ordenar datos alfabéticamente y numericamente

Extraer subconjuntos de los datos en base a criterios definidos.

48

Efectuar cálculos estadísticos con los datos para realizar análisis y tomar

decisiones.

Imprimir los datos de acuerdo a propósitos específicos.

Entre otros fines.

Sugerencias para crear una tabla

Una tabla puede ocupar toda una hoja de cálculo.

Una hoja de cálculo no debe tener más de una tabla.

Deje en blanco, como mínimo una columna y una fila entre la tabla y los demás

datos.

Evite la colocación de filas y columnas en blanco para que Excel pueda detectar

y seleccionar con mayor facilidad la tabla.

Ubicación y longitud de los nombres de campo

La primera fila de la lista deberá tener los nombres de los campos.

Los nombres de los campos pueden contener 255 caracteres como máximo.

Registros

Cada registro debe tener los mismos campos, pero no es necesario insertar datos

en cada campo.

No ingrese espacios adicionales al inicio de una celda ya que afectarán a las

clasificaciones y búsquedas.

No utilice una fila en blanco para separar la fila que contiene los nombres de los

campos de la primera fila de datos.

Uso de mayúsculas

Puede utilizar mayúsculas o minúsculas en una lista. Excel no lo considera

cuando realiza una búsqueda u ordena la lista.

4.2 Operaciones con base de datos

Ordenar datos

Excel permite reorganizar las filas o las columnas de una lista basándose en los

valores de la lista. Excel ordena en orden ascendente o descendente (1 al 9, Z a la A).

O bien basándose en el contenido de una o más columnas.

49

Ordenar registros de manera ascendente o descendente

Hacemos clic en la pestaña de datos y luego en ordenar

Excel selecciona los datos y muestra el cuadro de diálogo ordenar

Tenemos la opción de elegir el campo y el orden

Ordenar registros de manera ascendente o descendente

Finalmente vemos que los datos están ordenados de acuerdo al campo nombre y de

manera ascendente.

Tambien podemos hacer uso de los botones Orden ascendente y orden descendente

de la barra de herramientas para abreviar el procedimiento

Ordenar en función a dos columnas

Muchas veces, ordenar una lista por un campo no es de gran ayuda. Con frecuencia

se presenta la necesidad de ordenar una lista por dos campos.

Por ejemplo ordenemos por el campo tipo de salida y fecha:

Seleccionamos cualquier celda de la lista y realizamos lo mismo que el paso

anterior.

50

Luego hacemos un clic en el botón agregar nivel y escogemos las opciones y

finalmente aceptar

NOTA: ¿Como ordenar por más de cuatro campos?

El proceso de ordenar debe realizarse dando pasos sucesivos. En primer lugar debe

ordenar la lista por los criterios más concretos y, una vez así, volver a ordenar por los

criterios más generales. Es decir de adentro hacia fuera.

Ordenar columnas (horizontal)

En algunas situaciones se puede presentar la necesidad de ordenar una lista en

función de las columnas y no de las filas.

Seleccione la parte de la lista que desee ordenar

Haga clic en datos luego ordenar

En el cuadro de dialogo ordenar hacer clic en el botón opciones y selecciones

ordenar de izquierda a derecha.

51

Luego selecciona la fila que corresponde al criterio de ordenación.

4.3 Autofiltro

El comando Autofiltro nos permite filtrar Tablas de manera automática. Al filtrar una

tabla, Excel oculta de la vista todos aquellos registros de la lista que no cumplen con

la condición establecida.

Deseamos filtrar la lista de manera que solo muestre aquellos datos del campo Tipo

de salida:

Haga clic en una celda de la lista

Luego en la pestaña de datos seleccione Filtro dependiendo de la selección de

datos aparecerá un botón de lista desplegable.

52

Haga clic sobre el botón de flecha Autofiltro del campo requerido.

Escogemos la opción venta y como resultado obtenemos:

Quitar el filtro de una columna

Para quitar el filtro de la columna haga clic en la flecha de filtro del campo y

seleccione Todas.

Al seleccionar (Todas) el filtro aplicado en la columna queda eliminado, pero

aún seguimos en el modo filtrar por lo que sigue siendo posible aplicar otros

filtros.

Los filtros se pueden combinar: Por ejemplo seleccione Venta y luego en el

campo fecha 05 de Julio

Realizar un filtro personalizado

Supongamos que queremos averiguar los precios mayores de 25.

Realizamos los mismos pasos

que el caso anterior y al

seleccionar el botón flecha de

autofiltro del campo precio

seleccionamos Filtro de

número y podemos utilizarlos o

hacer clic en filtro personalizado

53

Luego el el cuadro de filtro personalizado colocamos la opción que deseamos

y aceptar

Salir del modo filtrar

Para salir del modo filtrar, vuelva a hacer clic sobre la opción filtro.

Las flechas de filtro desaparecen de la tabla.

Filtros múltiples

Condición Y

Al aplicar sucesivamente dos filtros a una lista, sólo quedan en pantalla los registros

que cumplen a la vez las dos condiciones planteadas. Pues a esto se le llama una

condición Y.

Por ejemplo queremos saber las ventas entre los días 02 al 05 de Julio, el filtro sería

asi:

54

Y la tabla tendría el siguiente aspecto:

La condición o

Las condiciones O se distinguen de las Y, en que basta con que se cumpla una de las

condiciones establecidas para que se muestre la lista en la pantalla cuando se aplica

el filtro.

Por ejemplo, queremos saber que libros se han dado como muestra o donación:

El resultado es:

Los caracteres comodín

Qué debemos hacer si queremos filtrar la lista de manera que sólo muestre los datos

de los clientes que empiezan con la letra “L”. Cuando se le presente esta situación

utilice los caracteres comodines.

El comodín asterisco (*)

Representa a cualquier número de caracteres. Por ejemplo, el criterio L* encontraría

Luis, Lorena, leandro, etc.

55

El comodín interrogación (?)

Representa a un sólo carácter. Por ejemplo, el criterio p?sta encontraría pasta, pista,

posta.

Los caracteres comodín pueden utilizarse en cualquier lugar del criterio, es

decir, puede usar p*, *cion, ca*mar, p?z, venta?, etc.

Filtros con los caracteres comodín

Para filtrar siga los siguientes pasos:

Seleccione cualquier celda de la lista

Seleccione el comando Filtro y luego

escoja las opciones respectivas.

Finalmente el resultado es:

4.4 Filtros avanzados

A diferencia del comando Autofiltro, el comando filtro avanzado requiere que los

criterios de filtrado sean especificados dentro de un rango separado de la lista. Un

rango de criterios debe constar al menos de dos filas. En la primera fila se introduce

uno o más encabezamientos de columnas y en las filas restantes los criterios de

filtrado.

Utilizando el filtrado avanzado:

Copie los encabezados en otra celda

Copie el tipo de salida DONACION y pequelo.

Copie los encabezados en otra celda.

Se debe tener el siguiente aspecto:

56

Seleccione la opción Filtro avanzado. Microsoft excel selecciona la lista y muestra el

cuadro de dialogo Filtro Avanzado.

Elija las opciones que se muestran:

El resultado es:

57

Condiciones O

Solamente necesitamos colocar el nuevo criterio debajo del anterior, por ejemplo:

Repetimos el proceso anterior, con la diferencia que debemos cambiar la zona de los

criterios.

Condiciones Y

Se deben añadir columnas. Por ejemplo queremos agregar que las fechas sean el 03

de Julio.

4.5 Subtotales

Este comando permite obtener información de resumen del contenido de una tabla

filtrada o sin filtrar.

Es muy importante que la tabla este ordenada por la columna por la cual se quiere

subtotalizar, de esta manera los valores repetidos de la columna estarán agrupados.

58

Deseamos saber qué cantidad de dinero se ha obtenido en cada día de venta.

Necesitamos adicionar una columna donde totalice la venta del día para cada libro.

Seleccione el comando Subtotales, y muestra el cuadro de dialogo

En Para cada cambio en, seleccione la columna por la que esta ordenada la lista,

fecha.

En usar función, elija SUMA

En agregar subtotal a, seleccione las columnas de las que quiere ver los

subtotales, en este caso Total

Habilite la opción resumen debajo de los datos.

59

En la parte izquierda de la hoja se observa una zona con botones que sirven para

examinar los subtotales de la lista.

60

Quitar subtotales

El cuadro de dialogo Subtotales presenta un botón llamado Quitar Todos. Haga clic

en ese botón y se eliminará todos los subtotales de la lista actual.

61

Capitulo 5: Tablas Dinámicas 5.1 Introducción

Las Tablas Dinámicas comprenden una serie de datos agrupados en forma de

resumen, es decir, agrupan aspectos concretos de una información global. Con una

Tabla dinámica se pueden hacer resúmenes de una base de datos, utilizándose para

promediar o totalizar datos

¿Qué ha ocurrido con el comando Informe de tablas y gráficos dinámicos?

En las versiones anteriores de Microsoft Office Excel, el comando Informe de

tablas y gráficos dinámicos se encontraba en el menú Datos e iniciaba el Asistente

para tablas y gráficos dinámicos. En Microsoft Office Excel 2007, el comando

Informe de tablas y gráficos dinámicos se ha separado en los dos siguientes

comandos:

El comando Tabla dinámica, que muestra el cuadro de diálogo Crear tabla

dinámica.

El comando Gráfico dinámico, que muestra el cuadro de diálogo Crear

gráfico dinámico.

Los dos cuadros de diálogo tienen interfaces de usuario similares y son mucho más

sencillos de utilizar que el asistente de la versión anterior. La diferencia radica en que

el cuadro de diálogo Crear tabla dinámica sólo crea un informe de tabla dinámica y

el cuadro de diálogo Crear gráfico dinámico crea un informe de gráfico dinámico

con un informe de tabla dinámica asociado

Informes de tabla dinámica

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:

62

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.

Presentar informes electrónicos o impresos concisos, atractivos y con comentarios.

Para ir comprendiendo las Tablas Dinámicas iremos desarrollando un pequeño

ejemplo que se basará en el registro de circulación de vehículos.

Mes Semana Vehículo Cantidad

Enero 1 Auto 105000

Enero 1 Camión 1050

Enero 1 Ómnibus 1575

Enero 1 Camioneta 2100

Enero 1 Moto 583

Enero 2 Auto 120750




Enero 2 Moto 485

Enero 3 Auto 122350




Enero 3 Moto 630

Enero 4 Auto 99000

Enero 4 Camión 990



Enero 4 Moto 544

63

5.2 Creación de Tablas Dinámicas

Para crear un informe de tabla, necesita conectar con un origen de datos y

especificar la ubicación del informe.

Seleccione una celda de un rango de celdas o coloque el punto de inserción dentro

de una tabla de Microsoft Office Excel. Asegúrese de que el rango de celdas tiene

encabezados de columna.

Siga uno de los procedimientos siguientes:

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

Siga uno de los procedimientos siguientes:

Seleccione los datos que desea analizar.

Puede elegir la opción de datos externos si tendría el caso.

Nota: Si el rango se encuentra en otra hoja de cálculo del mismo libro o de otro

libro, escriba el nombre del libro y de la hoja de cálculo utilizando la siguiente

sintaxis: ([nombredellibro]nombredelahoja!rango).

Escriba una ubicación. Siga uno de los procedimientos siguientes:

64

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

− Luego haga clic en Aceptar

Tendremos finalmente el siguiente resultado

Es importante comprender el modo en que funciona la lista de campos de tabla

dinámica y el modo en que puede organizar diferentes tipos de campos para obtener

los resultados deseados al diseñar un informe de tabla dinámica.

65

Cómo funciona la lista de campos de tabla dinámica

1. Un origen de datos contiene datos estructurados organizados como uno o varios

campos (denominados también columnas) que se muestran en la lista de campos. 2. Mueva un campo al área de filtro de informe en la lista de campos, lo que a su

vez moverá el campo al área de filtro de informe en el informe de tabla dinámica.

3. Mueva un campo al área de etiqueta de columna en la lista de campos, lo que a su vez moverá el campo al área de etiqueta de columna en el informe de tabla dinámica.

4. Mueva un campo al área de etiqueta de fila en la lista de campos, lo que a su vez moverá el campo al área de fila de columna en el informe de tabla dinámica.

5. Mueva un campo al área de valores en la lista de campos, lo que a su vez moverá el campo al área de valores en el informe de tabla dinámica.

Diseñando la Tabla Dinámica

Colocar Semana en columna

Colocar Vehículo a Fila

Colocar cantidad en datos

Podemos apreciar que en la parte superior aparece Suma de cantidad que es la

función que se está aplicando a los datos de la tabla.

Además la tabla dinámica aparece acompañada por una barra especial para filtrar

datos por despliegue de las mismas

66

5.3 Aplicar filtros

Con la barra especial podemos filtrar datos a nivel de filas y columnas.

Por ejemplo podemos ver los datos sólo de la 1ra y 4ta semana y solo de los

vehículos Auto y Omnibús

67

Tendríamos el siguiente resultado:

Podemos ver que las demás vistas también marcan lo realizado

5.4 Modificar el diseño

Siguiendo los procedimientos iniciales podemos realizar una presentación diferente.

También podemos replantear los datos de una manera distinta.

Por ejemplo:

Colocar Semana en fila

Colocar Vehículo a columna

Colocar cantidad en datos

Esto lo podemos realizar en la ventana de rótulos de fila

y columna realizando un clic y seleccionando la opción

Mover a rótulos de fila/ columna.

Podremos apreciar que se cambia automáticamente la

posición y se actualizan los datos.

68

Obtendremos el siguiente resultado:

5.5 Subtablas

Realizando un doble clic sobre una celda, en este caso por ejemplo la tercera de

Camión donde figura la cantidad 1124. Excel produce automáticamente un detalle

en hoja aparte:

5.6 Tablas Dinámicas de Tres Dimensiones

Para realizar una tabla de tres Dimensiones se debe realizar el mismo proceso pero se

presenta la opción de Campos de página y se ubica en el filtro de Informe.

69

Para nuestro caso si tuviéramos mas meses registrados semana sería nuestra tercera

Dimensión y se tendría la siguiente presentación.

5.7 Graficar tablas Dinámicas

Para realizar un gráfico dinámico elegimos la opción Gráfico Dinámico de Tabla

Dinámica.

Seleccionamos el Rango de datos y la Ubicación

Aceptamos

70

Obtenemos el siguiente resultado:

Colocamos los campos del mismo modo que realizamos para la tabla Dinámica.

Tendremos una nueva pestaña de Herramientas de diseño de Gráfico Dinámico

donde tendremos opciones de:

Cambiar el tipo de gráfico

Diseños de gráfico

Estilos de diseño.

Etc.

71

Podemos apreciar que se pueden realizar una serie de filtros y cambiar la

presentación del gráfico.

72

Práctica 1. Se tienen los siguientes datos:

Se pide realizar lo siguiente:

a) Crear la tabla dinámica para lo cuál considere que Mes debe ir en columna y Auto hasta Total beneficios a datos, aplique la función suma a todos.

b) Realice la obtención de subtablas. c) Obtenga en la tabla sin filtrar el promedio de recaudación por mes de cada

vehículo. d) Realice algunos cambios al diseño de la tabla en este caso pase mes a Fila. e) Aplique Filtros para presentar los datos.

2. Cree una tabla de tres dimensiones Con los mismos datos y se recomienda colocar

Semana a página. 3. Se pide que obtenga la máxima recaudación de cada vehículo. 4. Realice un gráfico dinámico para la presentación de los datos y realice filtro y

varié el tipo de gráfico.

73

Capitulo 6: Formularios

Los formularios son hojas de cálculo en las cuales el usuario puede ingresar datos en

una forma más amigable que la que puede obtenerse con la simple escritura en

celdas en forma de una tabla.

6.1 Validación de datos

Celdas que solo permiten usar valores de una lista

Por ejemplo si usted desea almacenar los códigos de sus clientes en una determinada

celda (A1 de una hoja de cálculo) y desea ingresar solamente los siguientes códigos:

a100, a101, a102, a103; puede realizarlo con los siguientes pasos:

Escriba las palabras permitidas en algún rango C1:C4

Seleccione la celda A1

Clic en la pestaña Datos; opción Validación

En el cuadro Permitir seleccione Lista

En el cuadro Origen De Datos escriba C1:C4

Haga clic en el botón Aceptar.

En la celda A1 solo se podrá almacenar los datos que muestra la lista

6.2 Cuadros de Control

Activando el uso de cuadros de control en Excel 2007

Lo primero que debemos hacer es seleccionar el Botón Office y hacer un clic en

Opciones de Excel.

74

Luego seleccionar Mostrar Ficha de Programador

Se mostrará la pestaña programador

En la opción Insertar podemos apreciar los controles de formulario

75

6.3 Control de Número

Son utilizados para ayudar al usuario a introducir a una celda valores numéricos los

cuales se encuentran dentro de una rango con extremos mínimos y máximos.

Seleccionamos el control de número de, el cuadro de control.

Lo colocamos en la Hoja de cálculo

Luego seleccionamos propiedades y podemos configurar el control de acuerdo a

las características que se presentan.

Las propiedades más importantes son la asignación a una celda y variación de

incremento ya que el rango está definido entre 0 y 30000.

Una vez que hayamos definido aceptamos y podemos apreciar que la celda que

esta asignada varia al hacer clic en los botones.

Las propiedades varían según el control pero por lo general tienen varias cosas

similares

6.4 Cuadro de lista

Son utilizados para mostrar un grupo de posibles valores de los cuales puede elegir

uno o varios.

76

El procedimiento para poder trabajar con un cuadro de lista y un cuadro combinado

es el mismo por lo que a ambos se aplica el mismo procedimiento.

Insertemos un cuadro de lista

Escriba los siguientes datos en la Hoja2

Televisor Refrigerador Licuadora Monitor Teclado

Debemos darle un nombre a este rango de datos le colocamos electrodomésticos.

En el cuadro combinado seleccionamos propiedades y luego en Rango de datos

escribimos electrodomésticos vinculamos con la celda B10 y las líneas de unión

verticales indican la cantidad de celdas que aparecen cuando se hace clic sobre el

control.

Podemos ver que el cuadro combinado nos permite seleccionar cualquier elemento

77

Podemos ver que al seleccionar se muestra el número ordinal de la lista. Este

concepto de ordinal es muy importante, ya que con este número puedo extraer más

datos de una tabla en otra hoja de trabajo, esto está siempre asociado a la función

=Buscarv, con estas bondades de Excel se pueden hacer trabajos más automáticas y

precisas.

6.5 Botón de comando

Los botones de cuadro de control nos permiten realizar alguna acción cuando

hacemos un clic sobre ellas.

Ejemplo: Veremos un pequeño ejemplo para entenderlo.

Insertemos en una hoja de cálculo dos botones de comando y cambie los títulos por

compras y ventas.

Luego seleccione el primer botón y haga clic en ver código. En esta parte se abrirá el

Editor de Visual Basic y escriba lo siguiente:

78

Realice lo mismo con el otro botón.

Luego pruebe el funcionamiento de los Botones

6.6 Botones de opción

Los botones de opción son utilizados cuando el usuario debe escoger una opción

dentro de un grupo de posibles valores.

Los pasos a seguir son:

• En la pestaña Programador

• Haga clic en botón de opción.

• Arrastre el botón del puntero en la posición deseada.

• Haga clic derecho sobre el título y luego proceda a cambiar el título.

• Para aceptar el nuevo título haga clic en cualquier parte de la hoja.

• Haga clic en el Botón de opción y arrastre dentro del cuadro de grupo.

• Puede cambiar la etiqueta del mismo modo que el anterior.

Como puede apreciar al vincular a alguna celda este indicará el número de la opción

marcada.

Además sólo podrá seleccionar una de las opciones que se presenten.

79

6.7 Casillas de verificación

En ocasiones la respuesta que espera el usuario a una determinada pregunta es si o

no. Si Ud. Usara para este caso botones de opción necesitaría dos de ellos uno para la

respuesta afirmativa y otro para la negativa; las casillas de verificación son más

eficientes pues con sólo usar una de ellas el usuario puede elegir o no esa opción.

Además en caso que hubiera varias opciones para seleccionar también conviene

utilizar las casillas

80

Práctica 1. La Empresa “XXYYZZ” realiza el servicio de giros para ello

considera los siguientes parámetros. • El recargo por flete es el 10% del monto de la venta si es

que se presentará. • El descuento es el 15 % si el tipo de pago es por

adelantado y 8% si es contra entrega, no otorga descuento si el pago es al crédito.

• La fecha de entrega puede ser máximo 30 días posterior a la fecha de pedido.

La empresa requiere agilizar este servicio para lo cual solicita que se le desarrolle un pequeño programa de control considerando los parámetros expuestos anteriormente. Los resultados que se muestran deben presentarse directamente al seleccionar las diversas opciones.

2. Del caso de la planilla de trabajadores desarrollada

anteriormente ahora desarrolle la Boleta de pagos, considerando que en un cuadro de lista se coloque los nombres de los trabajadores y seleccionando se actualicen sus datos.

81

Capitulo 7: Paneles e Impresiones 7.1 Paneles

Muchas veces se hace necesario mantener inmóvil ciertas partes de una hoja de

cálculo como en el caso de tener muchos datos y estos tiene asignado un campo para

ello podemos utilizar los paneles de Excel”

Panel: parte de la ventana de documento unida y a la vez separada de otras partes

por barras verticales u horizontales.

Para ver dos áreas de una hoja de cálculo y bloquear filas y columnas de un área se

puede usar la inmovilización o la división de paneles. Para inmovilizar paneles, se

seleccionan filas o columnas específicas que permanecen visibles al desplazarse por

la hoja de cálculo.

7.2 Inmovilizar paneles

Al dividir paneles, se crean áreas de hoja de cálculo independientes dentro de las

cuales es posible desplazarse, mientras que las filas o columnas del área no

desplazable permanecen visibles.

Inmovilizar paneles para bloquear filas o columnas específicas

En la hoja de cálculo, siga uno de los procedimientos siguientes:

Para bloquear filas, seleccione la fila situada debajo del punto en el que

desea que aparezca la división.

Para bloquear columnas, seleccione la columna situada a la derecha del

punto en el que desea que aparezca la división.

Para bloquear filas y columnas, haga clic en la celda situada por debajo y

a la derecha del punto en el que desea que aparezca la división.

En la ficha Vista, en el grupo Ventana, haga clic en Inmovilizar paneles y, a

continuación, haga clic en la opción que desea.

82

Veamos el siguiente ejemplo:

Podemos apreciar que cuando nos desplazamos por los datos hacia abajo las

cabeceras no son visibles y se pierden.

Aplicamos la inmovilización de Paneles

Podemos apreciar que nos hemos desplazado hacia abajo y la cabecera se mantiene y

de esta manera podemos apreciar los títulos de los campos además se coloca una

línea que indica la zona que no se va a mover.

83

Dividir paneles para bloquear filas o columnas en áreas de hoja de cálculo

independientes

• Para dividir paneles, sitúe el puntero sobre el cuadro de división que aparece en

la parte superior de la barra de desplazamiento vertical o en el extremo derecho

de la barra horizontal.

• Cuando el puntero adopte la forma de un puntero de división, arrastre el cuadro

de división hacia abajo o hacia la izquierda, hasta la posición deseada.

84

Para quitar la división, haga doble clic en cualquier punto de la barra de división que

divide los paneles.

7.3 Impresión

Imprimir una hoja de cálculo o libro de forma completa o parcial

Para imprimir parte de la hoja, haga clic en la hoja de cálculo y, a

continuación, seleccione el rango de datos que desea imprimir.

Para imprimir toda la hoja, haga clic en ella para activarla.

Para imprimir un libro, haga clic en cualquiera de sus hojas de cálculo.

Haga clic en el Botón office y luego imprimir

85

7.4 Impresión de encabezados en todas las páginas

Excel nos permite imprimir encabezados de filas o columnas en todas las páginas

Seleccione la hoja u hojas de cálculo que desee imprimir.

En la ficha Diseño de página, en el grupo Configurar página, haga clic

en Imprimir títulos

En la ficha Hoja, en el cuadro de diálogo Configurar página, siga uno o

varios de estos procedimientos:

En el cuadro Repetir filas en extremo superior, indique la referencia de

las filas que incluyan encabezados de columna.

En el cuadro Repetir columnas a la izquierda, escriba la referencia de

las columnas que incluyan los rótulos de fila

86

Si hacemos un clic en la vista preliminar podremos apreciar que la segunda página

tiene la cabecera de títulos también.

7.5 Trabajar con libros compartidos

Libro compartido: libro configurado para permitir que varios usuarios de una red lo

vean y realicen cambios al mismo tiempo. Cada usuario que guarda el libro ve los

cambios realizados por los demás usuarios.

Por ejemplo, si las personas de su grupo de trabajo administran varios proyectos cada

una y desea conocer el estado de los proyectos de sus compañeros, el grupo puede

utilizar un libro compartido para realizar un seguimiento del estado de los proyectos.

Todas las personas implicadas podrán especificar la información sobre sus proyectos

en el mismo libro.

87

Como propietario del libro compartido, puede administrarlo quitando usuarios del

mismo y resolviendo los cambios que estén en conflicto con otros. Cuando se hayan

incluido todos los cambios, puede dejar de compartir el libro.

Compartir un libro

1. Cree un nuevo libro e escriba los datos que desea proporcionar o abra un libro

existente que desea que esté disponible para su modificación por varios usuarios

2. En la ficha Revisar, en el grupo Cambios, haga clic en Compartir libro

3. En la ficha Edición, active la casilla de verificación Permitir la modificación

por varios usuarios a la vez. Esto también permite combinar libros.

4. En la ficha Avanzadas, seleccione las opciones que desea utilizar para realizar un

seguimiento de los cambios y actualizarlos; a continuación, haga clic en Aceptar

88

5. Debemos seleccione una ubicación de red a la que puedan tener acceso los

usuarios previstos y Guardar o colocar el archivo en ese lugar.

Quitar usuarios de un libro compartido

En la ficha Revisar, en el grupo Cambios, haga clic en Compartir libro

En la ficha Modificación, dentro de la lista Los siguientes usuarios tienen abierto

este libro, revise los nombres de los usuarios.

89

Seleccione el nombre del usuario al que desea desconectar y haga clic en Quitar

usuario.

Eliminar la vista personalizada del usuario

En la ficha Vista, en el grupo Vistas de libro, haga clic en Vistas

personalizadas.

En la lista Vistas, seleccione la vista de otro usuario y haga clic en

Eliminar.

90

Dejar de compartir el libro

• En el libro compartido, en la ficha Revisar, en el grupo Cambios, haga clic en

Compartir libro.

• En la ficha Modificación, asegúrese de que es el único usuario que aparece en la

lista Los siguientes usuarios tienen abierto este libro.

• Desactive la casilla de verificación Permitir la modificación por varios

usuarios a la vez. Esto también permite combinar libros.

91

Capitulo 8: Macros 8.1 Definición de Macro

El propósito de una macro es automatizar tareas de uso frecuente. Aunque algunas

macros son simplemente la grabación de las pulsaciones de tecla o los clic del mouse

(ratón), los programadores escriben macros de VBA (Visual Basic para Aplicaciones

VBA que se utiliza para programar aplicaciones basadas en Microsoft Windows y

que se incluye en varios programas de Microsoft.) más eficaces que utilizan código

que puede ejecutar muchos comandos en el equipo

La palabra Macro es una abreviatura de la palabra macroinstrucción. Una macro en

sí es un pequeño programa en código Visual Basic que se graba con un nombre y que

podemos invocarlo en cualquier momento.

Veamos el siguiente ejemplo:

Supongamos que diariamente tenemos que arreglar una tabla de datos poniéndole

colores, formatos, bordes, etc. Para posteriormente imprimirla.

Tendríamos que hacer los siguientes pasos:

92

1. Seleccionarla

2. Colocar un borde exterior

3. Ponerle color al fondo

4. Ponerlo color al texto

5. Darle formato de moneda

6. Colocarla en cursiva

7. Hacer un clic fuera de la tabla

8.2 Grabadora de macros

Cuando grabe una macro, la grabadora de macros graba todos los pasos necesarios

para completar las acciones que desea grabar.

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

− Haga clic en el Botón Microsoft y, a continuación, haga clic en

Opciones de Excel y luego seleccione la opción de mostrar ficha

programador.

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

93

Sitúese en la hoja donde tenga los datos de la tabla (guarde el original en otra

hoja). Elija la opción Grabar macro

Se presenta el cuadro para Grabar macros en el cual podemos colocar el nombre

de la Macro, colocar alguna abreviación para su ejecución, la ubicación donde

vamos a guardar y una descripción.

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

• Realice las acciones que desee grabar.

• En la ficha Programador, en el grupo Código, haga clic en Detener

grabación

• Finalmente en la opción Macros podemos apreciar que sale la ventana de macro

con una serie de opciones y las macros con las que contamos.

94

8.3 Ejecución de Macros

• Abra el libro que contiene la macro.

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

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

• Siga uno de los procedimientos siguientes:

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

Sugerencia También puede presionar CTRL+F8 para ejecutar la macro.

Puede interrumpir la ejecución de la macro presionando ESC.

− Para ejecutar una macro desde un módulo de Microsoft Visual Basic,

haga clic en el menú Ejecutar, haga clic en Ejecutar Macro o presione

F5.

• Podremos apreciar que la macro se ejecuto y los pasos que hicimos se realizaron

de una manera rápida.

• Obteniéndose el resultado de nuestros datos de acuerdo a lo que realizamos.

8.4 Creación de una Macro desde VBA

En la ficha Programador, en el grupo Código, haga clic en Visual Basic.

95

Si es necesario, en el menú Insertar, haga clic en Módulo.

Nota Se crean módulos automáticamente para todas las hojas del libro.

• En la ventana de código del módulo, escriba o copie el código de macro que

desea utilizar.

• Para ejecutar la macro desde la ventana del módulo, presione F5.

• En el menú Archivo, haga clic en Cerrar y volver a Microsoft Excel cuando

termine de escribir la macro.

8.5 Modificación de Macros

Antes de modificar una macro, deberá familiarizarse con el Editor de Visual

Basic; puede utilizar el Editor de Visual Basic para escribir y modificar macros

adjuntas a los libros de Microsoft Excel.

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

− En el cuadro Nombre de la macro, haga clic en la macro que desea

modificar.

Haga clic en Modificar.

96

• Observe que se muestran todas las acciones que hemos realizado. Excel lo ha

traducido a lenguaje VBA. Desde aquí podemos modificar cualquier línea con lo

que modificaremos el comportamiento de la macro.

• Observe que hay varias órdenes y hay que leerlas de derecha a izquierda.

• La rutina del programa tiene la siguiente estructura:

Sub Macro1 ()

Órdenes y acciones

End Sub

Sub y End Sub marcan el inicio

y el fin de la rutina.

Cierra la ventana. Observe que

se deberá cerrar una ventana

especial (Visual Basic

Aplication VBA) para retornar

a la ventana de Excel.

97

8.6 Añadir Botón de Ejecución

En Excel se puede asignar una Macro a un objeto, gráfico o control. En una hoja de

cálculo, haga clic con el botón secundario del mouse en el objeto, gráfico o control al

que desea asignar una macro existente y, a continuación, haga clic en Asignar macro

en el menú contextual.

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

Puede ejecutar la macro haciendo un clic sobre el objeto asignado

8.7 Macro Paso a paso

La ejecución de las macros es muy rápida. A veces nos puede interesar ver paso a

paso lo que hace una macro, sobre todo cuando hay algún fallo para localizarlo y

corregirlo.

Haga clic en la opción Macros y seleccione la Macro a ejecutar y haga clic en paso a

paso.

98

Puedes cambiar el tamaño de las dos ventanas para poder verlas al mismo tiempo

En la ventana de VBA pulse la tecla F8 y observa en la ventana de Excel como se va

ejecutando la Macro.

99

8.8 Firmar digitalmente un Proyecto de Macro

Firma digital: sello electrónico seguro con cifrado que certifica la autenticidad de

una macro o documento. Esta firma confirma que la macro o documento emitido por

el equipo o la persona que lo firma no ha sido alterado.

Un proyecto de macro mediante un certificado (certificado: medio digital de

confirmar la identidad y la autenticidad. Los certificados son emitidos por entidades

emisoras de certificados y, al igual que ocurre con los permisos de conducción,

pueden caducar o ser revocados.). Si aún no tiene ningún certificado digital, debe

puede obtener uno. Para probar los proyectos de macro en su propio equipo, puede

crear su propio certificado de firma con la herramienta Selfcert.exe.

Crear un certificado digital propio para firmar

En Microsoft Windows Vista, haga clic en el botón Inicio, elija Todos los

programas, haga clic en Microsoft Office, haga clic en Herramientas de

Microsoft Office y, a continuación, haga clic en Certificado digital para proyectos

de VBA. En el cuadro Nombre del certificado, escriba un nombre descriptivo para

el certificado.

100

En Microsoft Windows XP, haga clic en el botón Inicio, elija Todos los programas,

Microsoft Office, Herramientas de Microsoft Office y, a continuación, haga clic

en Certificado digital para proyectos de VBA. En el cuadro Nombre del

certificado, escriba un nombre descriptivo para el certificado.

Una vez colocado el nombre y pulsado en Aceptar nos sale un mensaje de creación

correcta

Para ver el certificado en el almacén de certificados personales, siga este

procedimiento:

• Abra Windows Internet Explorer.

• En el menú Herramientas, haga clic en Opciones de Internet y, a

continuación, haga clic en la ficha Contenido.

101

• Haga clic en Certificados y, a continuación, haga clic en la ficha Personal.

• Haga clic en la ficha Contenido.

• Haga clic en Certificados y, a continuación, haga clic en la ficha Personal.

• Podrá apreciar el certificado creado

Firmar digitalmente el proyecto

Abra el archivo que contiene el proyecto de macro que desee firmar.

Siga uno de estos procedimientos en los programas de 2007 Microsoft Office system

que se indican: Word, Excel o PowerPoint

− En la ficha Programador del grupo Código, haga clic en Visual Basic

102

− En el Explorador de proyecto de Visual Basic, seleccione el proyecto que desee

firmar.

En el menú Herramientas, haga clic en Firma digital.

• Siga uno de estos procedimientos:

− Si no ha seleccionado previamente un certificado digital o desea usar

otro, haga clic en Elegir, seleccione el certificado y haga clic en Aceptar

dos veces.

− Para usar el certificado actual, haga clic en Aceptar.

103

Dado que el certificado digital que ha creado no lo ha emitido ninguna entidad

emisora de certificados oficial, los proyectos de macro firmados con tales

certificados se conocen como proyectos con firma propia. Microsoft Office sólo

confía en los certificados con firma propia de los equipos que tengan ese certificado

en el almacén de certificados personales.

Se recomienda Importar y Exportar para trabajar con estos certificados

104

Práctica 1. La empresa ABC dedicada a repartir libros a 4 provincias de

Junín, los cuales están clasificados por módulos (minorista y mayorista) y en cada modulo cuenta con categorías (Infantil, artes, etc.) y dentro de cada categoría posee distintos niveles de precios. Tal como se muestran.

Pasos:

Lo que se pide es rellenar las celdas vacías con la etiqueta de la celda anterior.

Pulsa Ctrl + Inicio para situarte en la celda A1 Pulsa Ctrl + * para seleccionar todo el rango Presiona F5, especial y activa Celdas en Blanco Escribe =c2 y pulsa Ctrl + Intro Pulsa Ctrl + Inicio para situarte en la celda A1 y vuelve a

pulsar Ctrl + * Copia y luego a pegado especial selecciona valores y acepta.

Realice una macro para que pueda hacer lo mismo y pruébelo haciendo una copia de la hoja.

Asígnele a un botón de control la ejecución de la Macro y ejecútelo. Analice el código de la Macro que se genero Realice la firma digital al proyecto y haga prueba de funcionamiento en

otro equipo.

105

Capitulo 9: Visual Basic Aplications VBA 9.1 Introducción

Los objetos del office son los bloques fundamentales de construcción de las

aplicaciones Office, casi todo lo que haga en Visual Basic tiene que ver con la

manipulación de objetos. Cada unidad de contenido y funcionalidad en office, cada

libro de trabajo, hoja de trabajo, documento, rango, etc. Es un objeto que usted puede

controlar con programas de Visual Basic.

Visual Basic para Aplicaciones

• Esencialmente la programación de Excel se reduce a la manipulación de objetos

(mediante la escritura de instrucciones en un lenguaje que Excel puede

entender), mediante el lenguaje VBA.

• VBA está pensado como un lenguaje de secuencia de comandos de aplicación de

Microsoft común y ahora está incluido en las aplicaciones de Office (e incluso

en aplicaciones de otros fabricantes). Por consiguiente, al dominar VBA usando

Excel se podrá entrar directamente en la escritura de macros para otros

productos de Microsoft. Mejor aún, se pueden generar soluciones completas que

usan opciones a través de varias aplicaciones.

• El secreto de usar VBA con otras aplicaciones reside en entender el modelo de

objetos para cada aplicación.

• VBA simplemente manipula objetos, y cada producto (Excel, Word, Access,

PowerPoint y demás) posee un modelo de objeto único propio.

• Por ejemplo Excel expone varios objetos de análisis de datos muy potentes

como las hojas de cálculo, gráficos, tablas dinámicas, escenarios y numerosas

funciones matemáticas, financieras y temas generales. Con VBA se puede

trabajar con estos objetos y diseñar procedimientos automatizados.

EL NÚCLEO DE VBA

Las acciones de VBA se realizan mediante la ejecución del código VBA. El código

VBA se escribe (o se graba) y se guarda en un módulo VBA.

Los módulos se guardan en un libro de trabajo de Excel pero se editan o visualizan

en el editor de Visual Basic.

106

Un módulo VBA se compone de procedimientos. Un procedimiento es básicamente

un código de ordenador que realiza alguna acción sobre los objetos o con ellos.

Ejemplo:

Sub Prueba()

Sum= 1+1

MSGBox “La respuesta es” & Sum

End Sub

9.2 Objetos

¿Qué es un objeto?

En Microsoft Excel son objetos: un libro, una hoja, una celda, etc. Cada objeto

contiene métodos y propiedades.

Propiedades de un objeto

Las propiedades de un objeto definen sus características. Por ejemplo, un auto tiene

un color azul, dirección hidráulica, vidrios verdes, 4 puertas, ruedas de hierro, etc.

107

Una moto puede tener algunas propiedades similares, por ejemplo, color roja, 2

ruedas de aluminio, etc. A pesar de las propiedades similares, cada objeto contiene

las suyas y éstas le son propias.

Métodos de un objeto

Métodos de un objeto son las acciones referentes al mismo. Algunos objetos pueden

tener métodos similares; por ejemplo: un auto y una moto tienen los métodos

acelerar, frenar, recargar, cambiar de cambio, etc.

Excel proporciona más de 100 clases de objetos para manipular. Ejemplos de

objetos son: un libro de trabajo, una hoja de cálculo, un rango de una hoja de cálculo,

un gráfico y un rectángulo dibujado. Existen muchos más objetos a nuestra

disposición y se pueden manipular mediante el uso de código VBA.

Las clases de objetos están ordenados jerárquicamente. Los objetos pueden

actuar como contenedores de otros objetos. Por ejemplo, Excel es un objeto llamado

Application y contiene otros objetos como WorkBook y CommandBar. El objeto

WoorkBook puede contener otros objetos como WorkSheet y Chart. Un objeto

108

WoorkSheet puede contener objetos como Range, PivotTable y demás. Nos

referimos al orden de estos objetos como modelo de objeto de Excel.

Objetos similares forman una colección. Por ejemplo, la colección WorkSheets

está compuesta por todas las hojas de cálculo de un libro concreto. La colección

CommandBars está compuesta por todos los objetos CommandBar. Las colecciones

son objetos en sí mismas.

Cuando nos referimos a un objeto contenido o miembro, estamos especificando su

posición en la jerarquía del objeto, usando un punto como separador entre el

contenedor y el miembro.

Application.WorkBooks(“Libro1.xlsx”)

Esto se refiere al libro de trabajo Libro1.xlsx en la colección WorkBooks.

Ejemplo de referencia:

Nos podemos referir a la Hoja1 del Libro1 como

Application.WorkBooks(“Libro1.xls”).WorkSheets(“Hoja1”)

Si se quiere omitir una referencia específica a un objeto, Excel usa los objetos

activos. Si el libro1 es el libro de trabajo activo, la referencia anterior se puede

simplificar a

WorkSheets(“Hoja1”).Range(“A1”)

Si se sabe que la Hoja1 es la hoja activa, se puede incluso simplificar más

Range(“A1”)

Los objetos tienen propiedades. Se puede pensar en una propiedad como en una

configuración para un objeto.

Por ejemplo, un objetoRange tiene propiedades como Value y Name. Un objeto

Chart tiene propiedades como HasTitle y Type. Se puede usar VBA para determinar

las propiedades del objeto y también para cambiarlas.

109

Nos podemos referir a propiedades para combinar el objeto con su propiedad,

separados por punto.

Por ejemplo nos podemos referir al valor de la celda A1 de la Hoja1 como

WorkSheets(“Hoja1”).Range(“A1”).Value

Los objetos disponen de métodos. Un método es una acción que se realiza con el

objeto. Por ejemplo, uno de los métodos para el objeto Range es ClearContents. Este

método borra el contenido del objeto.

Se pueden especificar métodos mediante la combinación del objeto con el método,

separados por punto.

Range (“A1”).ClearContents

VBA también incluye todas las estructuras de lenguajes de programación modernos,

como matrices, bucles y de más.

9.3 Editor de Visual Basic

Para activar el editor de Visual Basic puede usar alguna de las siguientes técnicas:

• Ingresar a Excel

• Presionar ALT+F11

o

• Ir a la pestaña

Programador

• clic sobre el botón

de Visual Basic

Barra de herramientas

Posee una serie de botones que permiten acceder a tareas generales de edición como:

copiar, cortar y pegar; ejecutar procedimientos, etc.

110

Mediante el menú Ver, también podemos acceder a las ventanas del editor de Visual

Basic

La ventana de propiedades

Una propiedad es una característica de un objeto tal como el coloro el titulo del

objeto. Se configura la propiedad para especificar las características o

comportamiento de un objeto.

Podemos acceder a la ventana de propiedades de la siguiente manera.

Mediante la barra de herramientas

Mediante el menú ver

Presionando la tecla F2

111

El explorador de proyectos

Todos los códigos asociados con un libro de trabajo, documento, plantilla son

almacenados en un project que es automáticamente guardado con un libro de trabajo.

En el explorador de proyectos podrá ver, modificar y navegar los proyectos.

Elementos de un proyecto Ver código, Ver Objeto y Alternar carpeta

9.4 Creación de un procedimiento

Escribir un procedimiento nuevo

Un procedimiento es una unidad de código encerrado entre sub y end sub o entre las

sentencias function y end function.

Si desea escribir un código que no se asocie con un evento u objeto especifico, puede

crear un procedimiento en un módulo estándar.

Crear un módulo estándar

ir al menú insertar módulo

Agregar procedimiento

Abra el módulo menú insertar elija

procedimiento

Seleccione las opciones y clic en ok

Observe que el sistema le añade las expresiones Public Sub y al final End Sub.

Escriba la sentencia

112

MsgBox (“Bienvenidos a VBA”) y luego ejecútelo

Podemos apreciar el resultado.

9.5 Fundamentos de programación

Procedimiento General.

Los procedimientos generales pueden ser almacenados en un módulo de formulario o

en un módulo estándar. Los módulos estándar pueden almacenar procedimientos

generales, pero no se permiten procedimientos de eventos.

Procedimiento Público y Privado.

Los procedimientos públicos pueden ser llamados desde cualquier procedimiento en

cualquier módulo. Los procedimientos privados pueden ser llamados solo desde ese

procedimiento en el mismo módulo.

113

Creación de un Procedimiento General.

Los procedimientos generales pueden ser

de tipo Subrutina, Función o propiedad.

Al momento de insertar un procedimiento

se debe escribir su nombre luego elegir el

tipo y seleccionar el alcance del

procedimiento.

9.6 Subrutinas y Argumentos

Escribiendo código.

Dependiendo del tipo de procedimiento Sub, Function o Property podemos colocar el

código de acuerdo a lo que se requiera. Visual Basic finaliza el procedimiento con la

instrucción End Sub, End Function o End Property.

Llamando al procedimiento.

Cuando se tiene procedimientos generales se debe llamarlos explícitamente desde

algún procedimiento de su aplicación o el código nunca se ejecutará.

Asignando el procedimiento a un objeto.

Haciendo un anticlic a cualquier objeto seleccionamos asignar macro

Llamando al procedimiento desde otro lugar

114

También podemos escribir el siguiente código para llamar a un procedimiento y se

ejecute.

Sub Llamando()

clases 'Llamando al procedimiento

MsgBox ("se ejecuto clases")

End Sub

Podemos apreciar el resultado

Y luego de Aceptar

Tomando argumentos

Los valores con los que trabajan los procedimientos no deben ser necesariamente

fijos, sino pueden tomar distintos valores:

Por ejemplo:

Al ejecutar el procedimiento Prueba1, se hace un llamado al procedimiento ejemplo1

pasando el argumento nombre

No devuelven un valor

115

Una subrutina no devuelve un valor a la sentencia que la llama. Una subrutina puede,

sin embargo, devolver información al código que lo llama modificando los

argumentos, o mediante la modificación o cambio de los valores de las variables a

nivel de formulario

9.7 Funciones

La diferencia entre procedimientos de función y subrutina es que los

procedimientos de función devuelven un valor mientras que las subrutinas no lo

hacen. Además para ejecutar una función, está debe ser llamada desde un

procedimiento general o de evento.

El valor por defecto devuelto de un procedimiento de función es del tipo de datos

Variant. Debe especificar un tipo de dato para los valores devueltos usando la

palabra clave as

Sintaxis:

Function NombreFuncion() as TipoDato

Bloque de sentencias

NombreFuncion= Valor devuelto

End Function

Ejemplo:

116

Podemos apreciar que la subrutina alerta utiliza el valor devuelto de la función

valida.

Creando una función personalizada:

En la hoja de cálculo, escriba el nombre de la función y el argumento a pasar

La función se añade automáticamente a su asistente de Funciones

117

9.8 Complementos de Excel

Guardar un libro de Excel como complemento

Al guardar un libro de excel como complemento, asegura que las funciones que

desarrollo estén disponibles en otros libros de Excel.

1. Coloque la opción guardar

2. Escriba un nombre y luego en tipo de archivo elija Complemento de Microsoft

Excel

Activar el Complemento

• Haga clic en el Botón Microsoft Office y, a continuación, haga clic en

Opciones de Excel.

• Haga clic en la categoría Complementos.

• En el cuadro Administrar, haga clic en Complementos de Excel y, a

continuación, en Ir.

• Para cargar un complemento de Excel, haga lo siguiente:

118

− En el cuadro Complementos disponibles, active la casilla de

verificación situada junto al complemento que desea cargar y, a

continuación, haga clic en Aceptar.

119

Práctica 1. Excel presenta una serie de eventos sobre los objetos.

Dentro del Editor de Visual Basic Ingrese al Objeto This Workbook. Este objeto presenta una serie de eventos. Ingrese los siguientes códigos en los eventos respectivos:

Guarde el libro y vuelva a abrirlo. Observe los mensajes que se generan. Luego Observe los eventos de este Objeto.

2. Inserte un procedimiento que muestre en una celda un mensaje de saludo además el texto este en negrita para lo cual debe utilizar las siguientes propiedades, objetos y eventos ActiveSheet Range(“celdas”) Value Font Bold

3. Crear una función que acepte como argumento el monto de préstamo y el sueldo. La función debe devolver “Aprobado” si el préstamo no excede en 4 veces al sueldo. En caso contrario devolver “No aprobado”.

120

Guárdelo como complemento y en otro libro use la función.

121

Tipo de dato Bytes usados Rango de valoresByte 1 0 a 255Boolean 2 Verdadero o FalsoInteger 2 -32.768 hasta 32.767

Long 4 -2.147.483.648 hasta 2.147.483.647

Single 4

-3.402823E38 a -1.401298E-4545 (para valores negativos) y 1.4012298E-45 a 3.42823E38 (para valores positivos)

Double 8Currency 8

Date 8Del 1 Enero, 0100 hasta 31 de diciembre, 9999

Object 4String (de longitud variable)String (de longitud fija)

Capitulo 10: Variables y Sentencias Condicionales en VBA 10.1 Variable

Una variable es simplemente un trozo de memoria que la función o procedimiento se

reserva para guardar datos.

La forma de declarar una variable es:

dim variable as tipo

Ejemplo:

dim texto as string

Estamos indicando que se reserve un trozo de memoria, que se llama texto y que el

tipo de datos que se guardarán serán caracteres.

El propósito principal de VBA es manipular datos. Algunos residen en objetos tales

como rangos de hojas de cálculo. Otros se guardan en las variables que se crean.

Una variable es una localización de almacenamiento con nombre, dentro de la

memoria del ordenador.

VBA tiene algunas reglas relacionadas con los nombres de las variables:

– Se pueden usar caracteres alfabéticos, números y algún carácter de

puntuación, pero el primero de los caracteres debe ser alfabético

– VBA no distingue entre mayúsculas y minúsculas

– No se pueden usar espacios ni puntos

– No se pueden incrustar en el nombre de una variable los siguientes

símbolos: #, $, %,!

Los nombres de las variables pueden tener hasta 254 caracteres

10.2 Tipos de datos

122

Ámbito Cómo se declara una variable en este ámbito

Un procedimiento Incluye instrucciones Dim, Static o Private dentro del procedimiento.

Al nivel de módulo Incluye una instrucción Dim antes del primer procedimiento de un módulo.

Todos los módulos Incluye una instrucción Public antes del primer procedimiento de un módulo.

10.3 Ambito de las variables y constantes

El ámbito de una variable determina el módulo y el procedimiento en el que se puede

usar una variable.

Variables locales

Una variable local es una variable declarada dentro de un procedimiento. Estas

variables se pueden usar sólo en el procedimiento en que se declararon. Cuando el

procedimiento finaliza, la variable deja de existir y Excel libera su memoria.

La manera más común de declarar una variable local es situarla dentro de una

instrucción DIM.

Sub MiSub()

DIM x As Integer

DIM First As Long

DIM InerestRate As Single

DIM TodaysDate As Date

DIM UserName As String * 20

´Aquí se sitúa el código del procedimiento

End Sub

Esto también es válido:

DIM x As Integer, y As Integer, z As Integer

Esto no es válido:

DIM x, y, z As Integer

Variables a nivel de módulo

123

Algunas veces se deseará que una variable esté disponible para todos los

procedimientos de un módulo. Para ello, se declara la variable antes del primer

procedimiento del módulo (fuera de cualquier procedimiento o función).

DIM fecha As Date

Sub Dias()

Aquí va el código

End Sub

Sub Meses()

Aquí va el código

End Sub

Variables Public

Para que una variable esté disponible para todos los procedimientos de un proyecto

de VBA, se declara la variable a nivel de módulo con el uso de la palabra public.

Public InterésAnual as Long

Variables Static

Las variables estáticas son un caso especial. Se declaran a nivel de procedimiento y

retienen su valor después de que el procedimiento finaliza.

Sub Ejemplo()

Static Counter As Integer

´Aquí va el código

End Sub

Constantes

Variables que mantiene un valor constante en el tiempo de ejecución de un

procedimiento o función

Declarar constantes

Las constantes se declaran usando Const.

Const NumTrim As Integer = 4

Const Interés = 0.05, Periodo = 12

Const Nombre Mod as String = “Macros Presupuestos”

Public Const NombreApp As String = “Aplicación Presupuestos”

124

Al igual que las variables, las constantes también poseen un ámbito.

• Para que una constante esté disponible sólo dentro de un determinado

procedimiento, hay que declararla después de Sub o Function para convertirla en

una constante local.

• Para que una constante esté disponible para todos los procedimientos de un

módulo, se tiene que declarar antes del primer procedimiento de un módulo.

• Para que una constante esté disponible para todos los módulos de un libro de

trabajo, hay que usar la palabra Public, y declarar la constante antes del primer

procedimiento de un módulo.

Excel y VBA contienen muchas constantes predeterminadas, que se pueden usar sin

necesidad de declararlas; de hecho no es necesario conocer el valor de estas

constantes para usarlas.

Sub CalcManual()

Application.Calculation = xlManual

End Sub

Trabajar con cadenas

Existen dos tipos de cadenas en VBA:

• Cadenas de longitud fija, que se declaran con un número específico de

caracteres. La máxima longitud es de 65.536 caracteres.

• Cadenas de longitud variable, que teóricamente pueden tener hasta 2.000

millones de caracteres.

• Dim MiCadena As String * 50

• Dim SuCadena As String

Trabajar con fechas

Dim Hoy As Date

Dim HoraInicio As Date

Const PrimerDía As Date = #1/1/2008#

Const MedioDía As Date = #12:00:00#

Expresiones de asignación

125

Una expresión de asignación es una instrucción de VBA que realiza evaluaciones

matemáticas y asigna el resultado a una variable o aun objeto.

VBA usa el singno igual “=“ como operador de asignación.

• x = 1

• x = x + 1

• x = (y * 2) / (z * 2)

• FileOpen = true

• Range(“Año”). Value = 2009

Operadores aritméticos

+ Suma, - Resta, * Multiplicación, / División, \ División entera, Mod Resto, ^

exponencial, & Concatenación

Operadores comparativos

= Igual, < Menor, > Mayor, <= Menor o igual, >= Mayor o igual, <> Distinto

Operadores lógicos

Not (negación lógica, And (conjunción lógica), Or (disyunción lógica), XoR

(exclusión lógica), Eqv (equivalencia en dos expresiones), Imp (implicación lógica)

10.4 Función Inputbox

La sintaxis para la función InputBox de VBA es:

InputBox(Mensaje, Titulo, Valor por defecto, Posicion Horizontal, Posicion

Vertica, Archivo ayuda, Nro contexto para ayuda)

Mensaje Requerido. El texto desplegado en el cuadro de entrada

Titulo Opcional. El título de la ventana del cuadro de entrada

Valor por defecto Opcional. El valor predeterminado que se va a desplegar en el

cuadro de diálogo

Posición Horiz y Verti. Opcional. Coordenadas de la pantalla de la esquina superior

izquierda de la ventana

Archivo ayuda y Contexto Opcional. Archivo de ayuda y tema de ayuda

126

La función InputBox solicita información al usuario. La función siempre devuelve

una cadena, por lo que puede ser necesario convertir los resultados a un valor.

Ejemplo:

Sub EntrarValor()

Dim Texto as string

‘chr(13) sirve para que el mensaje se muestre en dos lineas

Texto= Inputbox(“Introducir un texto” & Chr (13) & “Para la casilla B3”,

“Entrada de datos”)

ActiveSheet.Range(“B3”).value=texto

ActiveSheet.Range(“B3”).Font.Bold=true

ActiveSheet.Range(“B3”).Font.Color=RGB(255,0,0)

End Sub

Conversión de tipos de datos

La función Inputbox devuelve siempre datos tipo String. Para lo cuál en algunas

situaciones se deben utilizar funciones de conversión de tipo. Estas funciones

convierten datos de un tipo a otro, de String a Integer, de Integer a String, de date a

String, ….

127

Sub Sumando()

Dim numero1 As Integer

Dim numero2 As Integer

numero1 = Val(InputBox("Entrar el primer valor", "Entrada de datos"))

numero2 = Val(InputBox("Entrar el segundo", "Entrada de datos"))

ActiveSheet.Range("A1").Value = numero1 + numero2

End Sub

Ejecute y haga la prueba sin colocar la función Val

Val (Cadena) Convierte la cadena a un valor numérico.

Str(Número) Convierte el número a una expresión cadena

10.5 Sentencias Condicionales

Las sentencias condicionales pueden probar condiciones y luego, dependiendo de los

resultados de esa prueba realizar diferentes operaciones.

Sentencias If ... Then … Else

Sentencias Select Case

10.6 Sentencias If

ESTRUCTURAS IF...THEN

Se usa para ejecutar una o más instrucciones de forma condicional. La sintaxis

general es:

If condición Then inst_verdaderas [Else inst_falsas]

Sub Positivos()

a = InputBox("Ingrese un número")

If a > 0 Then

MsgBox "Número Positivo"

End If

End Sub

128

Sub Positivos_Negativos()


If a > 0 Then MsgBox "Número Positivo" Else

MsgBox "Número negativo“

End if

End Sub

Sub Positivos_Negativos_Cero()


If a > 0 Then

MsgBox "Número Positivo"

ElseIf a < 0 Then

MsgBox "Número negativo "

ElseIf a = 0 Then

MsgBox " Cero "

End If

End Sub

10.7 Sentencias Case

La estructura Select Case es útil para elegir entre tres o más opciones

Sub Positivos_Negativos_Cero()


Select Case a

Case Is > 0

Msg = "Número Positivo"

Case Is < 0

Msg = "Número negativo"

Case Else

Msg = "Cero“

End Select

MsgBox Msg

End Sub

129

Sub Descuento1()

Cantidad = InputBox("Introducir cantidad: ")

Select Case Cantidad

Case "": Exit Sub

Case 0 To 24: Descuento = 0.1



Case Is >= 75: Descuento = 0.25

End Select

MsgBox "Descuento: " & Descuento

End Sub

También se pueden anidar estructuras Select Case

El siguiente procedimiento, verifica el estado de la ventana de Excel (maximizada,

minimizada o normal) y después presenta un mensaje describiendo dicho estado. Si

el estado de la ventana de Excel es normal, el procedimiento verifica el estado de la

ventana activa y después presenta otro mensaje.

Sub AppWindow()

Select Case Application.WindowState

Case xlMaximized: MsgBox "App Maximizada"

Case xlMinimized: MsgBox "App Minimizada”"

Case xlNormal: MsgBox "App Normal"

Select Case ActiveWindow.WindowState

Case xlMaximized: MsgBox "Libro Maximizado"

Case xlMinimized: MsgBox "Libro Minimizado"

Case xlNormal: MsgBox "Libro Normal"

End Select

End Select

End Sub

130

Práctica 1. Realizar una función personalizada para obtener la

bonificación que depende de la categoría de cada personal y se calcula en base al sueldo básico.

Se recomienda utilizar la función Ucase Ucase (Texto) Convierte a mayúsculas el text

2. Ingresar una cantidad que represente el precio de algo por el teclado con la instrucción Inputbox y guardalo en la celda A1 de la hoja activa. Si el valor entrado desde el teclado es superior a 200, pedir el porcentaje de descuento con otro Inputbox y guárdalo en la casilla A2 de la hoja activa, luego en A3 calcular el descuento y por último calcular en A4, el precio que sería A1 menos el descuento A3

131

Capitulo 11: Sentencias Repetitivas en VBA 11.1 Sentencias Repetitivas

Las sentencias repetitivas ejecutan un grupo de sentencias más de una vez

dependiendo de las condiciones particulares especificadas.

Los dos tipos de Bucles son

Sentencias Do ... Loop

Sentencias For .. Next

11.2 Sentencias Do .. Loop

BUCLES DO...WHILE

Do While se ejecuta mientras se verifica una condición especificada. Do While

puede tener cualquiera de estas dos sintaxis.

Mientras la celda activa este vacía se desplaza hacia abajo asignándole a cada

celda el valor 0

BUCLES DO...UNTIL

El bucle se ejecuta hasta que la condición llegue a ser verdadera. Do Until puede

tener cualquiera de estas dos sintaxis.

132

Sub DoUntilDemo()

Do

ActiveCell.Value = 10

ActiveCell.Offset(1, 0).Select

Loop Until Not IsEmpty(ActiveCell)

End Sub

11.3 Sentencias For .. Next

BUCLES FOR...NEXT

Esta sentencia de iteración se ejecuta un número determinado de veces

Su sintaxis es:

For contador = empezar To finalizar [Step valorincremento]

[Instrucciones]

[Exit For]

[instrucciones]

Next [contador]

Sub SumaNúmeros

Sum = 0

For Count = 0 To 10

Sum = Sum + Count

Next Count

MsgBox Sum

End Sub

Sub SumaNúmerosPares

Sum = 0

For Count = 0 To 10 Step 2

Sum = Sum + Count

Next Count

MsgBox Sum

End Sub

133

11.4 Funciones de Comprobación

Lista de funciones de comprobación:

IsNumeric (Expresión) Comprueba si expresión tiene un valor que se puede

interpretar como numérico

IsDate (Expresión) Comprueba si expresión tiene un valor que se puede interpretar

como tipo fecha

IsEmpty (Expresión) Comprueba que expresión tenga algún valor, que se haya

inicializado.

IsError (Expresión) Comprueba si expresión devuelve un algún valor de error

IsArray (Expresión) Comprueba si expresión (una variable) es un array o no.

IsObject (Expresión) Comprueba si expresión (una variable) representa una variable

tipo objeto.

134

IsNull (Expresión) Comprueba si expresión contiene un valor nulo debido a datos

no válidos.

Nothing: Sirve para comprobar si una variable objeto está asociada a un objeto antes

de hacer cualquier operación con ella.

11.5 Matrices y Variables de Objeto

Una matriz es un grupo de elementos del mismo tipo que tienen un nombre común;

para referirse aun elemento específico de una matriz se usa el nombre de la matriz y

un número de índice.

Se puede declarar una matriz con DIM o Public como cualquier variable.

MATRICES DE UNA DIMENSIÓN

DIM MiMatriz(1 To 100) As Integer

DIM MiMatriz (100)

MiMatriz(1) = 20

MATRICES MULTIDIMESIONALES

DIM MiMatriz(1 To 100, 1 to 10) As Integer

MiMatriz(1,2) = 20

VARIABLES DE OBJETO

Una variable de objeto es una variable que representa un objeto entero, como puede

ser un rango o una hoja de cálculo.

Las variables de objeto son importante por dos razones:

• Pueden simplificar el código

• Pueden hacer que el código se ejecute más de prisa.

Se declaran con DIM o Public.

Por ejemplo, la expresión siguiente declara AreaEntradaDatos como un objeto

Range. Para ver como simplifican el código, veamos un ejemplo sin usar variables de

objeto:

Sub VarSinObj();

Worksheets(“Hoja1”).Range(“A1”).Value = 124

Worksheets(“Hoja1”).Range(“A1”).Font.Bold = True

Worksheets(“Hoja1”).Range(“A1”).Font. Italic = True

End Sub

135

Esta rutina introduce un valor en la celda A1 de la HOJA1, del libro de trabajo

activo, y después aplica Negrita y cursiva a su contenido. Para reducir el código se

puede condensar la rutina con una variable de objeto.

Sub VarObj();

Dim MiCelda As Range

Set Micelda = Worksheets(“Hoja1”).Range(“A1”)

MiCelda.Value = 124

MiCelda.Font.Bold = True

MiCelda.Font. Italic = True

End Sub

11.6 Tipos definidos por el usuario

VBA permite crear tipos de datos personalizados definidos por el usuario (un

concepto más parecido a los registros de Pascal o las estructuras de C).

Ejemplo:

Type InfoClientes

Empresa As String * 25

Ventas As Long

End Type

DIM Clientes(1 To 100) As InfoClientes

Se puede hacer referencia a una componente particular, de la siguiente manera:

• Clientes(1).Empresa “Diseño Gráfico”

• Clientes(1).Ventas= 187000

Para copiar la información de Clientes(1) en Clientes(2) se puede hacer lo siguiente:

• Clientes(2) = Clientes(1)

11.7 Funciones integradas

Las funciones integradas de VBA no son las mismas que las de Excel. La función

UCASE de VBA, que convierte una cadena a mayúsculas es equivalente a la función

MAYUSC de Excel.

136

Sub MostrarRaiz()

MiValor = 25

RaizCuadrada = Sqr(MiValor)

MsgBox RaizCuadrada

End Sub

El objeto WorsheetFunction, que está contenido en el objeto Application, contiene

todas las funciones de hoja de cálculo que se pueden llamar desde los procedimientos

VBA. Veamos un ejemplo para convertir un número decimal en número romano.

Sub MostrarRomano

ValorDecimal = 1999

ValorRomano = Application.WorsheetFunction.Roman(ValorDecimal)

MsgBox ValorRomano

End Sub

Es importante saber que no se puede usar una función de hoja de cálculo que tenga

una función de VBA equivalente.

Por ejemplo no se puede usar la función de hoja de cálculo RAIZ, porque VBA tiene

la función SQR. La siguiente sentencia da error:

Application.WorksheetFunction.RAIZ(144)

11.8 Estructuras End With y For Each

MANIPULAR OBJETOS Y COLECCIONES

VBA ofrece dos importantes estructuras que pueden simplificar el trabajo con

objetos y colecciones:

Estructuras With ...End With

Estructuras For Each...next

137

ESTRUCTURAS WITH...END WITH

Permite realizar múltiples operaciones en un solo objeto.

Sub CambiarFuente()

With Selection.Font

.Name = “Times New Roman”

.FontStyle = “Bold Italic”

.Size = 12

.Underline = xlSingle

.ColorIndex = 5

End With

End Sub

ESTRUCTURAS FOR EACH...NEXT

Recordemos que una colección es un grupo de objetos relacionado. Por ejemplo, la

colección WorkBooks es una colección de todos los objetos WorkBook abiertos.

No es necesario saber la cantidad de elementos que existen en una colección para

usar la estructura For Each...Next.

Sub ContarHojas()

Dim Item As Worksheet

For Each Item In ActiveWorkbook.Sheets

MsgBox Item.Name

Next Item

End Sub

Sub VentanasAbiertas()

Suma = 0

For Each Item In Windows

Suma = Suma + 1

Next Item

MsgBox “Total de ventanas abiertas”, & Suma

End Sub

138

Sub CerrarActivo()

For Each Book In Workbooks

If Book.Name <> ActiveWorkbook.Name Then

Book.Close

Next Book

End Sub

Sub ConvertirMayus()

For Each Cell In Selection

Cell.Value = UCASE(Cell.Value)

Next Cell

End Sub

139

Práctica 1. Cree un procedimiento en el cual declare una variable de tipo

String y luego asígnele un valor y muéstrelo a través de un Msgbox

2. En un procedimiento declare una variable numérica y asígnele un valor, luego declare una variable de tipo objeto y asígnele una celda o rango de la hoja 2 luego al objeto definido asígnele el valor de la variable numérica

3. Defina una variable definida por el usuario llamada datapersonal y que tenga las propiedades Nombre y Edad. Luego utilícelo dentro de un Procedimiento y declare una variable del tipo definido asígnele valores y muéstrelo a través de un MsgBox

4. Dentro de un procedimiento llenar el rango de casillas A1 hasta A5 con valores introducidos por el usuario y en A6 colocar la suma de los mismos.

Propiedad Cells Sirve para referenciar una celda o un rango de celdas según coordenadas de fila y columna. Propiedades Row y Column Devuelven la fila y la columna de un objeto range.

140

Propiedades Offset Significa desplazamiento , es una propiedad que se utiliza para referenciar una casilla situada a n Filas y m columnas de una casilla dada.

5. Realizar lo indicado.

a) Del caso de la planilla de trabajadores desarrollada en los

capítulos anteriores ahora desarrolle la Boleta de pagos, considerando que en un cuadro de lista se coloque los nombres de los trabajadores y seleccionando se actualicen sus datos.

b) Utilice la función para cambiar números a letras y colóquelo dentro de la boleta para que se muestre en texto el monto a pagar.

c) Genere una macro que nos permita imprimir la boleta de los

trabajadores.

141

d) Realice una copia de la macro y cambie de nombre luego modifique la macro para que imprima todas las boletas de los trabajadores al ejecutar la macro.

BIBLIOGRAFÍA 1) Chuzón Diaz José, Programación Excel con Visual Basic, Lima-Perú: Editorial

Macro, 2002.

2) Denise Etheridge, Excel 2007: Top 100 Simplified Tips & Tricks, United States

of America: Wiley Publishing, Inc, 2007.

3) Elaine Marmel, Master Visually Excel 2007, United States of America: Wiley

Publishing, Inc. 2008

4) Helen Dixon, Excel 2007: Beyond the manual, United States of America: Apress

2007.

5) John Walkenbach, Microsoft Office Excel 2007, United States of America:

Wiley Publishing, Inc. 2007

6) Oceda Samaniego Cesar, Análisis estadístico, matemático y financiero con

herramientas avanzadas en Microsoft Excel, Lima-Perú: Empresa Editora Macro

EIRL, 2005.

Documents

Microsoft Excel 2007 - wiki246649.wikispaces.comwiki246649.wikispaces.com/file/view/LibroExcel2007.pdf · esta nueva versión de Excel 2007 y además ... funciones y las ... En este