MAQUERA QUISPE HENRY GEORGE
MERCADO RIVAS LENIN OMAR
MERCADO RIVAS RICHARD YURI
ROJAS BUJAICO RAFAEL WILFREDO
Para estudiantes y profesionales
Para estudiantes y profesionales
Maquera Quispe, Henry George
Mercado Rivas, Lenin Omar
Mercado Rivas, Richard Yuri
Rojas
Bujaico,
Rafael
Wilfredo
MICROSOFT EXCEL 2007
Para estudiantes y profesionales
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
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.
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].
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
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.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.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
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.
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.
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
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
9 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.
9 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.
• 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.
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
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.
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:
9 Situar el cursor sobre la celda donde se van a introducir los datos y teclear los datos que desees introducir.
9 Aparecerán en dos lugares: en la celda activa y en la Barra de Fórmulas, como puedes observar en el dibujo siguiente:
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.
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:
9 ##### se produce cuando el ancho de una columna no es suficiente o cuando se utiliza una fecha o una hora negativa.
9 #¡NUM! cuando se ha introducido un tipo de argumento o de operando incorrecto, como puede ser sumar textos.
9 #¡DIV/0! cuando se divide un número por cero.
9 #¿NOMBRE? cuando Excel no reconoce el texto de la fórmula. 9 #N/A cuando un valor no está disponible para una función o fórmula. 9 #¡REF! se produce cuando una referencia de celda no es válida.
9 #¡NUM! cuando se escriben valores numéricos no válidos en una fórmula o función.
9 #¡NULO! cuando se especifica una intersección de dos áreas que no se intersectan.
9 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
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:
9 Haz clic el Botón Office y elige la opción Guardar como...
9 Aparecerá el siguiente cuadro de diálogo: 9 En el recuadro Guardar en haz
clic sobre la flecha de la derecha para seleccionar la unidad dónde vas a grabar tu trabajo.
9 Observa como en el recuadro inferior aparecen las distintas subcarpetas de la unidad seleccionada.
9 Haz doble clic sobre la carpeta donde guardarás el archivo. 9 En el recuadro Nombre de
archivo, escribe el nombre que quieres ponerle a tu archivo.
9 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.
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:
9 Cancelar para no cerrar el documento.
9 No para salir del documento sin almacenar las modificaciones realizada desde la última vez que guardamos.
9 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: 9 Selecciona el Botón Office y elige la opción
Nuevo.
9 O bien utilizar la combinación de teclas CTRL+U.
9 En el cuadro que aparecerá deberás seleccionar Libro en blanco y hacer clic en Crear
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
• 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.
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..
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%
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
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:
9 Si la función va al comienzo de una fórmula debe empezar por el signo =.
9 Los argumentos o valores de entrada van siempre entre paréntesis. No dejes espacios antes o después de cada paréntesis.
9 Los argumentos pueden ser valores constantes (número o texto), fórmulas o funciones.
9 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
• 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.
= ((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.
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.
• 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.
• 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
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.
Función Descripción CARACTER(número) Devuelve el carácter especificado por el número
CODIGO(texto) Devuelve el código ASCII del primer carácter del
texto
CONCATENAR(texto1;texto2;...;textoN) Devuelve una cadena de caracteres con la unión DECIMAL(número;decimales;no_separar_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
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
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
FUNCIÓN SI: Esta función devuelve un valor si la condición especificada es Verdadera y otro valor si dicho argumento es falso
Práctica
Tenemos los siguientes datos:
1. Hallar:
9 ¿Cuántos puntos acumularon en word? 9 Calcular el promedio general de todos 9 Hallar la más alta nota en los promedios 9 Hallar la más baja nota en los promedios
9 Cuantos alumnos aprobaron en el promedio final
9 Cuantos puntos acumulan los alumnos aprobados en el promedio final.
9 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
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:
9 Calcular el descuento a las compras de CONTADO únicamente
9 Calcular el valor del IGV solamente para los comprobantes A, previa resta del descuento si corresponde
9 Calcular el Total de cada compra y el total general.
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.
Función Descripción AREAS(ref) Devuelve el número de rangos de celdas contiguas
BUSCAR(...) Busca valores de un rango de una columna o una fila
BUSCARH(valor_buscado;matriz_b uscar_en;indicador_filas;ordenado)
Busca en la primera fila de la tabla o matriz de valores
BUSCARV(valor_buscado;matriz_b uscar_en;indicador_columnas;orden ado)
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;anc ho)
Devuelve una referencia a un rango
DIRECCION(fila;columna;abs;a1;ho ja)
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íncul o;nombre_descriptivo)
Crea un acceso directo a un documento
IMPORTARDATOSDINAMICOS(c amp_datos;tablas_dinámicas;campo1 ;elemento1;campo2;elemento2...)
Extrae datos almacenados en una tabla dinámica
INDICE(matriz;num_fila;num_colu mna)
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
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_reinve rsió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: 9 Si es inferior a 1, BUSCARV devuelve al valor de error #VALUE!
9 Si es superior al número de columnas de matriz_buscar_en, BUSCARV devuelve el valor de error #REF!
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
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
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)
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
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% PROFUTURO 3 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
Capitulo 3: Gráficos
3.1 Descripción de GráficosEn 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.
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.
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).
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.
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.
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
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.).
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 500 0 5000 10000 15000 20000 25000
Niños Adutos Ancianos
Tí tu lo del ej e
Población
Hyo Chilca Tambo2. 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
