excel avanzado 2007c

EXCEL Avanzado Aplicado a tablas con operaciones avanzadas Manual para profundizar en el uso de la hoja electrónica EXCEL, aplicando funciones de búsqueda, estadísticas, financieras, condicionada, tablas dinámicas, macros y otras, así como también en construcción de gráficos. Dirección Académica INC 01/03/2008

CCOOLLOONNIIAALL LLOOSS RROOBBLLEESS:: De Shell Plaza El Sol, 2 c. al Sur, 1 y ½ c. arriba. Teléfonos: 2252-4096, 2252-4401 Telefax: 2252-4097

INSTITUTO NICARAGUENSE DE COMPUTACION

Web: www.inicompweb.net Email: [email protected]

Excel Avanzado Instituto Nicaragüense de Computación-INC 2

CONTENIDO

I. INICIAR Y SALIR DE EXCEL ...................................................................................... 4

II. PRESENTACION DE LA HOJA DE CÁLCULO E INTRODUCCION DE DATOS ............................................................................................................................ 5

III. COMANDOS DEL EXCEL Y FORMATO A UNA HOJA.......................................... 12

IV. MANEJO DEL ARCHIVO DE HOJAS DE CALCULO.............................................. 17

V. CREACION Y USO DE FORMULAS ........................................................................... 19

VI. EDICION DE RANGOS Y LA HOJA DE CALCULO ................................................ 23

VII. IMPRIMIR .................................................................................................................... 26

VIII. CREAR Y DAR FORMATO A UN GRAFICO ......................................................... 28

IX. TABLAS DINAMICAS .................................................................................................. 31

X. EJERCICIOS DE TABLAS Y GRAFICOS ................................................................... 35


INTRODUCCION El Excel es una hoja de cálculo de gran capacidad y facilidad de uso. Los archivos de Excel se llaman libros de trabajo. En ellos puede guardar varias hojas, incluyendo hojas de cálculo, hojas de gráficos y otros módulos. Excel le ofrece un modo coherente para realizar todas las tareas, y cuenta con un eficaz método abreviado para mover y copiar datos; también proporciona métodos abreviados para comandos de uso más frecuente. Excel proporciona la Cinta de Opciones con fichas, grupos y comandos, para que pueda elegir aquellos que necesite de forma fácil y rápida. Excel contiene características nuevas, que le permiten simplificar el trabajo y ahorrar tiempo. Las innovaciones introducidas en los gráficos, la organización de listas, el análisis de datos, el desarrollo de aplicaciones y otras áreas hacen que las tareas más complicadas apenas requieran esfuerzo. En este folleto se estudian los aspectos avanzados de la hoja de Cálculo, dividiéndose en nueve capítulos, con una explicación teórica de cada unidad apoyada en algunos ejemplos. Los primeros tres capítulos tratan todos aquellos aspectos relacionados con la hoja de cálculo, en cuanto a su creación, introducción de datos, y los ajustes que se pueden realizar a la hoja, tales como modificación del formato de columnas o de toda la hoja, control del formato de datos numéricos y rótulos, la "división" de pantalla, la fijación de títulos, etc. El capítulo cuatro brinda la oportunidad de conocer las diferentes operaciones en cuanto al manejo de archivos de hojas de cálculo, tales como Recuperar y Guardar. La unidad cinco trata todo lo relacionado con el uso de las fórmulas matemáticas, estadísticas, financieras, búsqueda y lógicas. También se abordan las técnicas para indicar rangos en fórmulas. El capítulo seis brinda información relacionada con la ampliación de una hoja de cálculo, es decir, insertar renglones, columnas, copiado de datos y fórmulas, etc. La impresión de una hoja de cálculo se explica en el capítulo siete, a través de los numerosos comandos y opciones de formato para hacer este tipo de trabajo. La creación de gráficos y su impresión se abordan en la unidad ocho, pudiéndose seleccionar entre los diferentes tipos y dar formato a cada uno de ellos. El tema de las tablas dinámicas se explica en el capítulo nueve, con el objetivo de aprender a crear informes para consolidar los datos de una hoja de cálculo. En el capítulo diez se hacen ejercicios para elaboración de tablas con fórmulas, creación de gráficos, planillas, funciones estadísticas, funciones financieras, libro de banco, tabla dinámica, etc.


I. INICIAR Y SALIR DE EXCEL

Una vez que se ha introducido al Windows, hacer clic al botón Inicio, elija Todos los Programas, y haga clic al icono de Microsoft Excel para introducirse al programa.

Hecho lo anterior, observará la pantalla de Excel, la cual proporciona, en la parte superior, una serie de botones organizados en fichas o barras de herramientas que facilitan un acceso rápido a los comandos más usados, lo cual es un beneficio ya que no necesita memorizar las teclas. Esta barra de herramientas presenta varios "cuadritos" pequeños, los cuales tienen en su interior un dibujo relacionado con la actividad que ellos realizan. A estos cuadritos se les llama Botones, y si detiene unos segundos el puntero del Mouse sobre uno de ellos, Excel le mostrará una breve descripción del botón. Finalmente para salir de Excel y regresar al WINDOWS, llevar el puntero al comando Archivo y dar un clic, luego colocarlo en el comando Salir y dar otro clic. Si está en Excel 2007 entonces hacer clic al Botón de Office y luego clic al botón Salir de Excel. En ambas versiones puede cerrar el programa haciendo clic al botón X para cerrar la ventana.

Microsoft Excel


II. PRESENTACION DE LA HOJA DE CÁLCULO E INTRODUCCION DE DATOS a) La Hoja Electrónica A continuación se explica brevemente algunos componentes de la pantalla de Excel:

Los tres componentes de la cinta de opciones son las fichas, los grupos y los comandos. Fichas Hay siete en la parte superior y cada una de ellas representa una de las tareas básicas

que se hacen en Excel.

Grupos Cada ficha tiene grupos que muestran elementos relacionados entre sí.

Comandos Un comando es un botón, un cuadro en el que se escribe información o un menú.

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

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

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

1

2

3


Los grupos aglutinan todos los comandos que se necesitan para realizar una tarea concreta, y se mantienen a la vista y disponibles para su uso mientras se trabaja en la tarea, en lugar de permanecer escondidos en los menús. Estos comandos esenciales están a la vista en la parte superior del área de trabajo. He aquí un ejemplo de las ventajas que presenta: si desea mostrar texto en varias líneas de un celda, no es necesario hacer clic en un comando de un menú, en una ficha de un cuadro de diálogo y, por último, en una opción. Sencillamente se hace clic en el botón Ajustar texto del grupo Alineación, de la ficha Inicio. Lo verá en el ejercicio práctico.

- Cuadro de división de las etiquetas:

Pequeño cuadro situado entre las etiquetas de las hojas y la barra de desplazamiento horizontal. Arrastre el cuadro de división para ver más etiquetas de hojas o aumentar el tamaño de la barra de desplazamiento horizontal.

- Barra de estado: Es la barra que se encuentra en la parte inferior de la pantalla y

presenta información acerca de un comando seleccionado o una operación en progreso. - Barra de fórmulas: Es una barra en la parte superior de la ventana que se usa para editar o

introducir valores o fórmulas en celdas. - Barra de desplazamiento: Son las barras sombreadas en los lados derecho e inferior de una

ventana para desplazarse por una hoja. - Borde de una ventana: Es el borde alrededor de una ventana que sirve para cambiar el tamaño

y la forma de la ventana. - Botón minimizar: Minimiza la ventana de Excel a un icono.

Barra de Formulas

Barra de Titulo Seleccionar Todo

Encabezado de

columnas


- Botón Restaurar: Cambia el tamaño de la ventana a su tamaño y ubicación anterior. - Botón Seleccionar todo: Es el rectángulo gris ubicado en la esquina superior izquierda de una

hoja de cálculo, en la intersección de los encabezados de filas y columnas, y sirve para seleccionar todas las celdas de la hoja.

- Botón Maximizar: Ampliar una ventana a su tamaño máximo. - Botones para desplazar etiquetas: Están ubicados en la parte inferior izquierda de la pantalla

representados por una flecha; sirven para desplazarse entre una etiqueta y otra. - Cuadro de división: Pequeño cuadro situado arriba de la barra de desplazamiento vertical y a

la derecha de la barra de desplazamiento horizontal y si se arrastra divide la ventana en dos secciones separadas.

- Cuadro de nombres: Es el cuadro que se encuentra a la izquierda de la barra de fórmulas que

le permite definir rápidamente un nombre para la celda o el rango seleccionado. - Encabezado de columnas:

Es el área gris que se encuentra en la parte superior de cada columna en la cual figura una letra. - Encabezado de filas: Es el área gris numerada a la izquierda de cada fila. - Etiqueta de la hoja: Son las etiquetas ubicadas en la parte inferior de una ventana del libro de

trabajo.

b) Cómo introducir datos en la Hoja de Cálculo En Excel los datos se introducen seleccionando primero la celda o el rango y después escribiendo. A medida que escribe, los datos aparecen en la barra de fórmulas y en la celda. Algo nuevo que ofrece Excel en materia de selección es la vista transparente que, al seleccionar celdas con texto de color, el color permanece igual en vez de aparecer con una combinación de colores inversa. Cuando seleccione una celda de una hoja, ésta se convertirá en la Celda activa. La celda seleccionada recibe el nombre de la letra de la columna y el número de la fila a la cual pertenece. Por ejemplo, la celda A4, se llama así porque está en la columna A y en la fila 4.


Lo que escriba aparecerá en la celda activa y en la barra de fórmulas (arriba de la letra C del encabezado de columnas). En caso de equivocación al introducir un datos, presione la tecla ESC o hacer clic en el cuadro de cancelación (representado por una X arriba del encabezado de columna de la letra B) para anular la entrada. Para aceptar la entrada presione la tecla ENTER o haga clic al cuadro de introducción (representado por símbolo de chequear o letra V a la derecha del cuadro de cancelación). Para corregir el contenido de una celda, haga doble clic sobre la celda a editar, y luego haga los cambios necesarios. Los cuadros de introducción y cancelación desaparecen de la barra de fórmulas una vez introducidos los datos. Para realizar todas las tareas, primero seleccione el elemento con el que desee trabajar. Después elija el comando o el botón que desee aplicar al elemento seleccionado. Cuando vaya a trabajar con más de una celda seleccione un rango de celdas, es decir, un grupo rectangular de celdas. La manera de referirse a un rango es nombrando la primera y última celda, separadas por dos puntos (:). Para hacer esto, coloque el puntero del Mouse en la primera celda, mantenga presionado el botón del Mouse y arrastre hasta la última celda, después suelte el botón. Una vez seleccionada una celda o un rango, elija un comando para realizar una acción específica en la selección. Los comandos están agrupados en menús en la parte superior de la ventana.

- Números: Para introducir un número como un valor constante, seleccione una celda y escriba el número. Los números pueden ser caracteres numéricos (del 0 al 9) y cualquiera de los siguientes caracteres especiales: + - ( ) , / $ . %.

! Puede incluir comas en los números, tal como 1,000,000. ! Un solo punto en una entrada numérica es considerado como un punto decimal. ! Los signos + delante de los números serán ignorados. ! Los números negativos deberán esta precedidos del signo menos (-) o estar entre

paréntesis. ! Al introducir números estos se alinean a la derecha automáticamente.

- Fórmulas:

Una fórmula puede ayudarle a analizar datos en la hoja de cálculo. Con una fórmula puede realizar operaciones con los valores de la hoja de cálculo, tales como una suma, multiplicación, división, etc. Una fórmula puede estar compuesta de cualquiera de los


siguientes elementos: Operadores, referencias de celda, valores, funciones. Para introducir una fórmula en una celda, introduzca una combinación de estos elementos en la barra de fórmulas.

Procedimiento para introducir una fórmula: a) Seleccione la celda en la que desee introducir la fórmula. b) Escriba el signo (=) para activar la barra de fórmulas. Si inicia una fórmula pegando

una función, o un nombre, Excel insertará automáticamente el signo igual. c) Escriba la fórmula o use cualquiera de las siguientes técnicas para introducir una

fórmula en la barra de fórmulas:

Inserte referencias en la fórmula seleccionando las celdas en la hoja de cálculo. Cuando selecciona celdas, Excel inserta referencias relativas en la fórmula del usuario.

Otra forma, Pegar para pegar los caracteres que copió anteriormente de otra fórmula o aplicación.

O bien, elija la función en el cuadro de diálogo Pegar función y haga clic en Aceptar. Inserte las referencias de la fórmula en el cuadro Número o haga clic a Contraer (flecha roja hacia arriba). Pulse Enter o clic a Contraer (flecha roja hacia abajo).

Finalmente, después de haber completado la fórmula, presione la tecla ENTER. - Rótulos:

Un texto está compuesto de letras o cualquier combinación de números y letras. Cuando introduce un texto, los caracteres se alinean a la izquierda dentro de la celda. Para introducir texto, seleccione una celda y escriba el texto. Una celda puede contener hasta 255 caracteres. Puede introducir números como texto, por ejemplo los números del INSS, pero primero hay que aplicar el formato de texto a las celdas en blanco; otra forma es anteponiéndole un apóstrofe. Para alinear el texto a otras posiciones, hay que seguir los siguientes pasos:

! Seleccionar la celda o las celdas a justificar. ! Seleccionar la ficha Inicio y el grupo Alineación


.

! Bajo "horizontal" haga clic a la fechita hacia abajo y seleccione la opción de su interés.

! Elija el botón "Aceptar". - Operadores:

Los operadores indican operaciones en fórmulas aritméticas. Ellos pueden ser de dos tipos: matemáticos y lógicos. Los operadores lógicos no se estudiarán por el momento. Los operadores matemáticos son:

^ Potenciación. +,- Positivo, Negativo. *,/ Multiplicación, División. +,- Adición, Sustracción.

Ejemplo:


1- Haga clic en la A1 para seleccionarla. 2- Introduzca en mayúscula la palabra ARTICULO, luego presione la tecla ENTER. 3- Seleccione la celda D1, escriba CANTIDAD y luego el ENTER. 4- Luego ponga los siguientes datos, según se indica en el ejemplo:


III. COMANDOS DEL EXCEL Y FORMATO A UNA HOJA a) Menús, barras de herramientas y otros elementos: Además de las fichas, los grupos y los comandos, Office Excel 2007 utiliza otros elementos que también proporcionan formas de realizar tareas. Los elementos siguientes son más parecidos a los menús y las barras de herramientas ya conocidos de las versiones anteriores de Excel. El vínculo Libro de asignaciones de la cinta de opciones de Excel le llevará a un libro que contiene todos los elementos asignados.

Botón de Microsoft Office Este botón está ubicado en la esquina superior izquierda de la ventana de Excel y abre el menú que se muestra en la figura.

Barra de herramientas de acceso rápido La barra de herramientas de acceso rápido aparece de forma predeterminada en la parte superior de la ventana de Excel y proporciona acceso rápido a herramientas que se utilizan con frecuencia. Puede personalizarla agregándole comandos.


Iniciadores de cuadros de diálogo Los iniciadores de cuadros de diálogo son pequeños iconos que aparecen en ciertos grupos. Al hacer clic en uno de estos iniciadores se abre un cuadro de diálogo o un panel de tareas relacionado que contiene más opciones relacionadas con dicho grupo.

b) Ajustes del Formato - Control del Ancho de Columnas. Para fijar el ancho de una o varias columnas se sigue el procedimiento siguiente:

1. Seleccione la columna o columnas que desea cambiar. 2. En la ficha Inicio, en el grupo Celdas, haga clic en Formato.


3. En Tamaño de celda, haga clic en Ancho de columna. 4. En el cuadro Ancho de columna, escriba el valor que desee.

Para fijar el ancho de todas las columnas de la hoja se hace:

1. Siga uno de los procedimientos siguientes: ! Para cambiar el ancho predeterminado de columna de una hoja de

cálculo, haga clic en su ficha de hoja. ! Para cambiar el ancho predeterminado de columna de todo el libro,

haga clic con el botón secundario en una ficha de hoja y, a continuación, haga clic en Seleccionar todas las hojas en el menú contextual

2. En la ficha Inicio, en el grupo Celdas, haga clic en Formato.

3. En Tamaño de celda, haga clic en Ancho predeterminado. 4. En el cuadro Ancho de columna predeterminado, escriba una medida nueva.

- Control del Formato de Datos

Utilice el cuadro de diálogo Formato de celdas para aplicar formato al contenido de las celdas seleccionadas.

Para dar formato numérico a un rango de celdas, se hace lo siguiente: 1. Seleccione el rango al cual se dará formato. 2. En la ficha Inicio, abrir la ventana del grupo Número


3. Seleccione el formato que le interese o introduzca un formato personalizado. 4. Haga clic en Aceptar o presione ENTER.

Para dar formato numérico a toda la hoja, proceder a:

1. Haga clic al botón Seleccionar Todo (botón gris que se encuentra en la esquina superior izquierda de una hoja, en la intersección de los encabezados de fila y columna).

2. En la ficha Inicio abrir la ventana del grupo Número. 3. Seleccione el formato de interés o introduzca uno personalizado. 4. Haga clic en Aceptar o presione Enter.

c) Inmovilizar o bloquear filas y columnas Con el objetivo de fijar títulos se inmovilizan filas y columnas, el procedimiento es el siguiente:

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


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

Nota Al inmovilizar paneles, la opción Inmovilizar paneles cambia a Movilizar paneles para que pueda desbloquear filas o columnas inmovilizadas.

d) División de Pantalla Procedimiento para dividir ventanas:

1. En la ficha Vista, grupo Ventana seleccione elija Nueva Ventana en el menú Ventana.

2. Mueva y ajuste el tamaño de las ventanas como desee o elija Organizar todo en el grupo Ventana de la ficha Vista, luego seleccione Horizontal. Finalmente haga clic en Aceptar.


IV. MANEJO DEL ARCHIVO DE HOJAS DE CALCULO a) Abrir Archivo

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

En el menú, haga clic en Abrir para abrir un libro existente.

O bien, haga clic en Opciones de Excel, en la parte inferior del menú para configurar las opciones del programa.

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

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

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

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


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

En Office 2003, muestra el cuadro de diálogo Abrir para que pueda abrir un archivo existente. Puede abrir varios libros de trabajo al mismo tiempo manteniendo presionada la tecla CTRL mientras hace clic

en los nombres. El botón "Abrir" se encuentra al lado izquierdo de la barra de herramientas Estándar. También se puede abrir un archivo seleccionando Abrir del menú Archivo. a) Guardar Archivos Guarda los cambios que se introducen en el libro de trabajo activo. Se puede usar el botón de Office y luego clic a "Guardar". Si el libro de trabajo es primera vez que se archiva se mostrará el siguiente cuadro de diálogo:

En esta situación deberá escribir un nombre de archivo y finalmente hacer clic en el botón

Guardar o presionar la tecla ENTER.

En Office 2003 se puede usar el botón "Guardar" que se encuentra al lado izquierdo de la barra de herramientas Estándar. También se puede guardar un archivo seleccionando Guardar del menú Archivo.


V. CREACION Y USO DE FORMULAS Las fórmulas son ecuaciones que efectúan cálculos con los valores de la hoja de cálculo. Una fórmula comienza por un signo igual (=). Por ejemplo, la siguiente fórmula multiplica 2 por 3 y, a continuación, suma 5 al resultado.

=5+2*3 Una fórmula también puede contener lo siguiente: funciones, referencias, operadores y.

Partes de una fórmula

Funciones: la función PI() devuelve el valor de pi: 3,142... Referencias: A2 devuelve el valor de la celda A2. Constantes: números o valores de texto escritos directamente en una fórmula, por ejemplo, 2. Operadores: el operador ^ (acento circunflejo) eleva un número a una potencia, y el operador *

(asterisco) multiplica. Para copiar fórmulas se siguen los siguientes pasos:

Arrastre la cruz de color negro desde la celda que contiene la fórmula hasta la celda donde desea copiarla y suelte el asa de relleno.

Aparece el botón Opciones de autorrelleno, pero no es necesario hacer nada


A veces es más fácil copiar fórmulas que crearlas. En este ejemplo, verá cómo se copia la fórmula que utilizó para obtener el total de enero y cómo se utiliza para sumar los gastos de febrero.

En primer lugar, seleccione la celda B7, que contiene la fórmula de enero. A continuación, sitúe el puntero en la esquina inferior derecha de la celda hasta que aparezca la cruz negra (+). Después, arrastre el asa de relleno hasta la celda C7. Cuando se suelta el asa de relleno, aparece el total de febrero (126,93) en la celda C7. La fórmula =SUMA(C3:C6) está visible en la barra de fórmulas cerca de la parte superior de la hoja de cálculo cada vez que se hace clic en la celda C7.

Una vez copiada la fórmula, aparece el botón Opciones de autorrelleno para ofrecerle algunas opciones de formato. En este caso, no es necesario hacer nada con las opciones del botón. El botón desaparece cuando se selecciona otra celda. A continuación se presentan por categorías las fórmulas básicas de uso frecuente: a) Funciones Matemáticas y Generales: =ABS(número): Devuelve el valor absoluto de un número. El valor absoluto de un número es

el número sin su signo. Ejemplo: ABS(2) es igual a 2. Si A1 contiene -15, entonces ABS(A1) es igual a 15 =REDONDEAR (número;núm_de_decimales): Redondea un número o el contenido de una

celda con el número de decimales especificados. Ejemplo: REDONDEAR(2.15;1) es igual a 2.2 Si B1 contiene 21.134, entonces REDONDEAR(B1,2) es 21.13 =ENTERO(número): Redondea un número hasta el entero inferior más próximo. Ejemplo: ENTERO (8.9) es igual a 8 Si A2 contiene 6.45, entonces ENTERO(A2) nos da 6 =RAIZ(número): Devuelve la raíz cuadrada de un número o del contenido de una celda. Ejemplo: RAIZ(9)=3 Si A1 contiene 16, entonces RAIZ(A1) es igual a 4 =PI(): Devuelve el número 3.14159...., o la constante matemática Π, con una exactitud de 15

dígitos. Ejemplo: PI()*(A1^2) calcula el área del círculo cuyo radio se encuentra en la celda A1 b) Funciones Estadísticas: Excel incluye un buen número de funciones estadísticas para desarrollar análisis estadísticos

básicos. =SUMA(rango): Devuelve la suma de todos los números en la lista de argumentos.


Por ejemplo: SUMA(A2:E2) obtiene la suma de todos los números desde la celda A2 a E2. =PROMEDIO(rango): Devuelve el promedio (media aritmética) del argumento. Por ejemplo: PROMEDIO(A1..A5), da la media de todos los números desde la celda A1 a la

A5. =CONTAR(rango): Cuenta los números que hay en la lista de argumentos. Use CONTAR

para obtener el numero de entradas en un campo numérico de un rango: Por ejemplo: CONTAR(A1..A5) devuelve un 5 si las celdas de A1 hasta A5 están ocupadas. =DESVEST(rango): Calcula la desviación estándar de una muestra. La desviación estándar es

la medida de la dispersión de los valores respecto a la media (valor promedio). Por ejemplo: DESVEST(A2:E3) calcula la desviación estándar de los valores que están en el

rango de A2 hasta E3. =VAR(RANGO): Calcula la varianza de una muestra. Por ejemplo: VAR(A1:A5) realiza la varianza entre las celdas A1 a la A5. =MIN(RANGO): Devuelve el valor mínimo de una lista de argumentos. Por ejemplo: MIN(B18..B54) calcula el valor más pequeño en la serie desde la celda B18 a la

B54. =MAX(RANGO): Devuelve el valor máximo de una lista de argumentos. Por ejemplo: MAX(B18..B54) calcula el valor más grande desde la celda B18 a la B54. c) Funciones Financieras: Estas son funciones utilizadas en cálculos financieros. Además de las que aquí se explican,

Excel incluye otras funciones, tales como TIR, VPN y muchas más. =VF(tasa,nper,pago,va,tipo): Devuelve el valor futuro de una inversión basándose en pagos

periódicos constantes y en una tasa de interés constante. Por ejemplo: =VF(D27,D28,-D26,,1) calcula el valor futuro de una serie de depósitos anuales en la celda D26, con una tasa anual en la celda D27, un plazo anual en la celda D28 y las anualidades se aplican al inicio.

=VA(tasa,nper,pago,vf,tipo): Devuelve el valor actual de una inversión. El valor actual es el

valor que tiene actualmente la suma de una serie de pagos que se efectuarán en el futuro. Por ejemplo, cuando pide dinero prestado, la cantidad del préstamo es el valor actual para el prestamista. Por ejemplo: =VA(D7,D8,-D6) calcula el valor actual (valor del préstamo) que se pagará con anualidades en la celda D6, con una tasa anual en la celda D7 y un plazo anual en la celda D8.

=NPER(tasa,pago,va,vf,tipo): Devuelve el número de períodos de una inversión basándose

en los pagos periódicos constantes y en la tasa de interés constante. Por ejemplo,


=NPER(D67/12,,-D66,D68) calcula el tiempo para duplicar una inversión cuyo valor actual está en la celda D66 y su valor futuro en la celda D68, con una tasa en la celda D67.

=TASA(nper,pago,va,vf,tipo,estimar): Devuelve la tasa de interés por período de una

anualidad. TASA se calcula por iteración y puede tener cero o más soluciones. Si los resultados sucesivos de TASA no convergen dentro de 0,0000001 después de 20 iteraciones, TASA devuelve el valor de error #¡NUM!. Por ejemplo, =TASA(D87,,-D86,D88) calcula la tasa de una inversión en la celda D86, para obtener un valor futuro en la celda D88, en un período en la celda D87.

=PAGO(tasa,nper,va,vf,tipo): Calcula el pago de un préstamo basándose en pagos

constantes y en una tasa de interés constante. Por ejemplo, =PAGO(D47,D48,-D46) calcula el pago de un préstamo en la celda D46, con una tasa anual en la celda D47 y un plazo anual en la celda D48.

d) Funciones Lógicas: Las funciones lógicas a través de los operadores lógicos ayudan a tomar decisiones en la hoja

de cálculo y, sobre todo, a automatizar sus análisis. Dichos operadores lógicos son: = Igual < Menor que > Mayor que <= Menor o igual a >= Mayor o igual a <> Diferente NO No lógico Y Y lógico O O lógico =SI(ARGUMENTO;VERDADERO;FALSO): Devuelve el valor VERDADERO si el

argumento se cumple, y en caso contrario, o sea el argumento no se cumple, devuelve el valor FALSO. Por ejemplo: SI(B1>O,C3/B1,0). En este argumento, si el valor de la celda B1 es mayor que 0, entonces muestra el cociente de C3 entre B1, si es menor o igual aparece simplemente un 0.

Algunos otros ejemplos de aplicación de funciones condicionales son: =SI(B4<>C4;A1;A2): Si el contenido de la celda B4 no es exactamente igual al contenido de

la celda C4, coloca el contenido de la celda A1; si es igual, el de la celda A2.


VI. EDICION DE RANGOS Y LA HOJA DE CALCULO a) Copia de Celdas Al copiar una celda, se duplica su contenido y se pega en una nueva ubicación. Existen varias

maneras de copiar celdas. A continuación se explican dos procedimientos: - Usando comandos del Menú.

1. Seleccione las celdas que desea mover o copiar. 2. En la ficha Inicio, en el grupo Portapapeles, siga uno de los procedimientos

siguientes:

! Para mover celdas, haga clic en Cortar .

Método abreviado de teclado También puede presionar CTRL+X. ! Para copiar celdas, haga clic en Copiar .

Método abreviado de teclado También puede presionar CTRL+C. 3. Seleccione la celda superior izquierda del área de pegado.

Sugerencia Para mover o copiar una selección a otra hoja de cálculo o libro, haga clic en la ficha de otra hoja o pase a otro libro y, a continuación, seleccione la celda superior izquierda del área de pegado.

4. En la ficha Inicio, en el grupo Portapapeles, haga clic en Pegar . - Usando Menú contextual. 1. Seleccione las celdas que tengan los datos que desea copiar. 2. En el Menú contextual, elija Copiar. 3. Seleccione la celda superior izquierda del área de pegado o seleccione toda el

área de pegado. 4. En el menú contextual, elija Pegar. 5. Si todavía es visible el borde móvil, cancélelo presionando ESC o eligiendo

otro comando. - Arrastrando el Mouse. 1. Seleccione la celda o las celdas que desee copiar. 2. Coloque el puntero del Mouse sobre el borde de la selección. 3. Presione CTRL mientras arrastra la selección hasta donde desee pegarla.

Mientras arrastra, aparece un borde que indica el tamaño y la posición de la selección.


4. Para copiar los datos a celdas existentes, coloque el puntero del Mouse en la esquina inferior derecha (aparece una cruz fina) y desplace el Mouse de manera que rodee el área de pegado y después suelte el botón del Mouse. Se reemplazará cualquier dato que exista en el área de pegado.

b) Edición de una Celda. Puede editar una celda de la hoja de cálculo escribiendo una nueva entrada sobre una ya

existente, o editando parte de la información dentro de la celda o en la barra de fórmulas. Para editar el contenido de una celda: 1. Haga doble clic en la celda que contenga los datos que desea editar. Para hacer una edición en la barra de fórmulas, haga clic en ella. 2. Edite el contenido de la celda. 3. Para introducir los cambios en la celda, haga clic en el cuadro de introducción o

presione ENTER. Para cancelar las ediciones, haga clic en el cuadro de cancelación o presione ESC. Para eliminar caracteres, use la tecla backspace (retroceso) y borra el carácter de la izquierda,

o use la tecla delete (suprimir) y borra el carácter a la derecha. c) Modificación de Columnas y Filas Puede insertar o eliminar fácilmente filas o columnas. Al insertar las celdas aledañas se

desplazan para dar cabida a las nuevas. Al eliminar, las celdas aledañas se desplazan para llenar el espacio que haya quedado.

- Insertar Filas y Columnas El procedimiento es el siguiente: 1. Seleccione una fila o columna. Puede insertar más de una fila o columna,

ampliando la selección para que incluya el número de filas o columnas que desea insertar.

2. En la ficha Inicio, grupo Celda, se presenta el menú con la opción Insertar, elija Insertar filas de hoja o Insertar columnas de hoja.

También puede elegir Insertar en el menú contextual. Método abreviado: CTRL + SIGNO MAS.


- Eliminar Filas y Columnas El procedimiento es el siguiente: 1. Seleccione una fila o columna. Para eliminar más extienda la selección para

que incluya todas las filas o columnas que desee eliminar. 2. En la ficha Inicio, grupo Celda, se presenta el menú con la opción Eliminar,

elija Eliminar filas de hoja o Eliminar columnas de hoja.

En el menú contextual, elija Eliminar. Método abreviado: CTRL + SIGNO MENOS d) Borrado en la Hoja de Cálculo - Borrar Celdas Al borrar una celda se borra su contenido (fórmulas y datos), los formatos aplicados,

las notas o todo a la vez. Si borra una celda, su valor será cero y cualquier fórmula que haga referencia a la misma recibirá un valor de cero. El procedimiento es el siguiente:

1. Seleccione la celda o el rango de celdas a borrar. 2. En el menú contextual elija Borrar Contenido Método abreviado: DELETE (borra el contenido). Otra forma de borrar es arrastrando el cuadro de llenado (cuadro negro pequeño en la

esquina de la selección) hasta que la selección quede gris y después suelte el botón del Mouse.

- Borrar la pantalla Para borrar el trabajo y comenzar un documento nuevo, haga lo siguiente:

1. Haga clic al botón de Office, elija Cerrar para cerrar los documentos activos y sus ventanas correspondientes.

2. Haga clic al botón de Office, clic al botón Nuevo, luego Crear para iniciar un nuevo documento.


VII. IMPRIMIR

Cuando la información esté lista para imprimir, podrá ver una presentación preliminar del documento y ajustar la configuración de las páginas para obtener la impresión deseada. Esto le permitirá determinar el orden en que Excel crea las páginas de la hoja de cálculo, controlar los saltos de página y los números de páginas; reducir y ampliar el documento para que quepa en un determinado número de páginas y ajustar el tamaño de las hojas de gráficos. A continuación, se incluyen los botones y otras opciones del cuadro de diálogo Imprimir:

Nombre En la lista, haga clic en una impresora que haya instalado y desee usar.

Estado Para la impresora seleccionada, se indica el estado, como inactiva, no disponible, o el número de archivos antes de su trabajo de impresión.

Tipo Para la impresora seleccionada, se indica el tipo (por ejemplo, láser).

Donde Para la impresora seleccionada, se indica la ubicación o el puerto al que está conectada.

Comentario Para la impresora seleccionada, se ofrece información adicional que pudiera ser útil.


Propiedades Haga clic en este botón si desea cambiar las propiedades (como el tipo de papel) para la impresora que utiliza.

Buscar impresora Haga clic en este botón para buscar una impresora a la que tenga acceso.

Imprimir a un archivo Active esta casilla de verificación para crear un archivo a partir del documento en lugar de enviarlo directamente a una impresora. El archivo se guarda con formato de impresión, como selección de fuente y especificación de color en un archivo .prn que se puede imprimir en otra impresora.

Número de copias En esta lista, haga clic en el número de copias que desee imprimir.

Intercalar Active esta casilla de verificación si desea que su trabajo de impresión se guarde con el orden que haya creado en el documento al imprimirlo. Esta casilla de verificación está disponible cuando se imprimen varias copias.

Selección Haga clic en esta opción para imprimir sólo lo que haya seleccionado.

Hojas activas Haga clic en esta opción para imprimir sólo las hojas de cálculo activas.

Todo el libro Haga clic en esta opción para imprimir todo el libro, con todas las hojas de cálculo que contenga.

Tabla Haga clic en esta opción para imprimir sólo la tabla de la hoja de cálculo.

Omitir áreas de impresión Active esta casilla de verificación para omitir las áreas de impresión que pueda haber especificado.

Todo Haga clic en esta opción si desea imprimir todas las páginas del archivo.

Páginas Haga clic en esta opción y agregue los números de página o los intervalos de páginas en el cuadro.

Vista previa Haga clic en este botón para abrir una vista previa de la hoja de cálculo antes de imprimirla y seleccionar otras opciones de impresión, como la configuración de página.

Aceptar Haga clic en este botón para enviar el documento a la impresora.

Cancelar Haga clic en este botón para regresar al documento sin imprimirlo.


VIII. CREAR Y DAR FORMATO A UN GRAFICO

a) Introducción Un gráfico es una representación gráfica de datos de la hoja de cálculo. Los gráficos pueden hacer que los datos se vean más interesantes y atractivos, y que sean más fáciles de leer y de evaluar. También pueden servir de ayuda en el análisis y la comparación de datos. Cuando Usted crea un gráfico basándose en una selección de datos de la hoja de cálculo, Excel utiliza esos valores y los muestra en el gráfico como puntos de datos. Los valores se representan como barras, líneas, columnas, porciones, puntos y otras formas, llamadas marcadores de datos. En el Excel, si los valores del gráfico se componen de números grandes, puede reducir el texto del eje y hacerlo más legible cambiando la unidad de presentación del eje. Por ejemplo, si los valores del gráfico van de 1,000,000 a 50,000,000, puede mostrar los números como 1 a 50 en el eje y mostrar una etiqueta que indica que las unidades representan millones. Los grupos de puntos de datos, o marcadores de datos, que tienen su origen en las filas o las columnas de una sola hoja de cálculo se agrupan en series de datos. Cada serie de datos se puede distinguir por un color o un diseño peculiar, o ambos. Después de crear un gráfico, puede mejorarlo y hacer hincapié en cierta información, agregando elementos para gráficos, como rótulos de datos, una leyenda, títulos, texto, líneas de tendencias, barras de error y líneas de división. Asimismo, podrá desplazar y cambiar el tamaño de la mayoría de los elementos de los gráficos. También les puede dar formato utilizando diseños, colores, alineación, tipos de letras y otras características para dar formato. Si un gráfico está previsto para servir de suplemento a los datos de una hoja de cálculo y para aparecer en la misma, cree un gráfico incrustado en la hoja de cálculo. Para mostrar un gráfico en otra hoja del libro de trabajo, cree una hoja de gráficos. b) Crear el gráfico

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


Los comandos de la cinta de opciones son los que más se utilizan. En lugar de mostrar todos los comandos todo el tiempo, Excel 2007 muestra algunos comandos en respuesta a las acciones que se realizan. Por ejemplo, si no hay un gráfico en una hoja de cálculo, los comandos que se usan para trabajar con gráficos no son necesarios. Pero una vez creado, aparecen las Herramientas de gráficos con tres fichas: Diseño, Presentación y Formato. En dichas fichas están los comandos que se precisan para trabajar con un gráfico. La cinta de opciones responde a la acción del usuario. Use la ficha Diseño para cambiar el tipo de gráfico o moverlo a otra ubicación; la ficha Presentación para cambiar los títulos y otros elementos del gráfico y la ficha Formato para agregar relleno de color y cambiar el estilo de las líneas. Cuando termine el gráfico, haga clic fuera del área del gráfico. Desaparecerán las Herramientas de gráficos. Para volver a verlas, haga clic dentro del gráfico y se mostrarán. c) Notas para modificar la presentación del gráfico

El gráfico se coloca en la hoja de cálculo como un gráfico incrustado. Si desea colocar el

gráfico en una hoja de gráfico independiente, puede cambiar su ubicación. Cómo cambiar la ubicación de un gráfico

1. Haga clic en el gráfico incrustado o en la hoja de gráfico para seleccionarlos y mostrar las herramientas de gráfico.

2. En la ficha Diseño, en el grupo Ubicación, haga clic en Mover gráfico.

3. En Seleccione dónde desea colocar el gráfico, siga uno de los procedimientos

siguientes: • Para mostrar el gráfico en una hoja de gráfico, haga clic en Hoja

nueva. Sugerencia Si desea reemplazar el nombre sugerido para el gráfico, puede escribir un nombre nuevo en el cuadro Hoja nueva.

• Para mostrar el gráfico como un gráfico incrustado en una hoja de cálculo, haga clic en Objeto en y, a continuación, haga clic en una hoja de cálculo en el cuadro Objeto en.

Para crear rápidamente un gráfico basado en el tipo de gráfico predeterminado, seleccione los datos que desea utilizar para el gráfico y, a continuación, presione ALT+F1 o F11. Al presionar ALT+F1, el gráfico se muestra como un gráfico incrustado. Al presionar F11, el gráfico aparece en una hoja de gráfico independiente.


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

Al crear un gráfico, las herramientas de gráfico aparecen disponibles y se muestran las fichas Diseño, Presentación y Formato. Puede utilizar los comandos de estas fichas para modificar el gráfico con el fin de que presente los datos de la forma que desea. Por ejemplo, utilice la ficha Diseño para mostrar las series de datos por filas o por columnas, realizar cambios en el origen de datos del gráfico, cambiar la ubicación del mismo, cambiar el tipo de gráfico, guardarlo como una plantilla o seleccionar opciones de diseño y formato predefinidas. Utilice la ficha Presentación para cambiar la disposición de los elementos del gráfico, como los títulos del gráfico o los s de datos, utilizar herramientas de dibujo o agregar cuadros de texto e imágenes al gráfico. Utilice la ficha Formato para agregar colores de relleno, cambiar estilos de línea o aplicar efectos especiales.


IX. TABLAS DINAMICAS

Utilice un informe de tabla dinámica para resumir, analizar, explorar y presentar datos de resumen. Utilice un informe de gráfico dinámico para ver esos datos de resumen contenidos en un informe de tabla dinámica y ver fácilmente comparaciones, patrones y tendencias. Ambos informes le permiten tomar decisiones informadas sobre datos críticos de su empresa. Las secciones siguientes ofrecen información general sobre los informes de tabla dinámica y de gráfico dinámico. a) Procedimiento para crear una tabla dinámica

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

1. Seleccione una celda de un rango de celdas 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.

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

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

! Para crear un informe de tabla dinámica o gráfico dinámico, en la ficha Insertar, en el grupo Tablas, haga clic en Tabla dinámica y, a continuación, en Gráfico dinámico. Aparecerá el cuadro de diálogo Crear tabla dinámica con el gráfico dinámico.

3. Seleccione un origen de datos. Seleccione la tabla que desea analizar 1. Haga clic en Seleccione una tabla o rango. 2. Escriba el rango de celdas o la referencia del nombre de tabla, por ejemplo =BeneficiosTrimestrales, en el cuadro Tabla o rango. Si seleccionó una celda de un rango de celdas o si el punto de inserción estaba en una tabla antes de iniciar el asistente, el rango de celdas o la referencia del nombre de tabla se muestra en el cuadro Tabla o rango.


Opcionalmente, para seleccionar un rango de celdas o una tabla, haga clic en Contraer cuadro de diálogo para ocultar temporalmente el cuadro de diálogo, seleccione el rango en la hoja de cálculo y, a continuación, presione Expandir diálogo . 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).

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

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

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

! O bien, haga clic en Contraer cuadro de diálogo para ocultar temporalmente el cuadro de diálogo, seleccione la primera celda de la hoja de cálculo y, a continuación, presione Expandir diálogo .

5. Haga clic en Aceptar. Un informe de tabla dinámica vacío se agregará a la ubicación que especificó en la Lista de campos de tabla dinámica que se muestra de modo que puede comenzar a agregar campos, crear un diseño y personalizar el informe de tabla dinámica. b) Agregar los campos a la tabla dinámica Después de crear un informe de tabla dinámica o gráfico dinámico, utilice la lista de campos de tabla dinámica para agregar campos. Si desea cambiar un informe de tabla dinámica o gráfico dinámico, utilice la lista de campos para ordenar y quitar campos. De manera predeterminada, la lista de campos de tabla dinámica contiene dos secciones: una sección de campos en la parte superior para agregar o quitar campos, y una sección de diseño en la parte inferior para volver a organizar campos y ajustar su posición. Puede acoplar la lista de campos de tabla dinámica en algún lado de la ventana y cambiarle el tamaño horizontalmente. También puede desacoplarla, en cuyo caso podrá cambiarle el tamaño tanto vertical como horizontalmente. 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 o gráfico dinámico.


Un origen de datos externo contiene datos estructurados organizados como uno o varios campos (denominados también columnas) que se muestran en la lista de campos.

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.

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.

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.

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. A continuación, se incluyen directrices para mover los campos Valor, Nombre y Dimensión de la sección de campos a las cuatro áreas de la sección de diseño. Campos de valor

Si sólo activa una casilla de verificación para los campos numéricos, el área predeterminada se mueve a las etiquetas de columna.

Campos de fila y columna

Si un campo ya está en un área de etiquetas de fila, etiquetas de columna o filtros de informe y se vuelve a agregar a alguna de estas áreas, se cambia su posición cuando se mueve a la misma área, pero se cambia su orientación cuando se mueve a un área diferente.

Para agregar campos al informe, siga uno o varios de estos procedimientos:

Active la casilla de verificación situada junto a cada nombre de campo en la sección de campos. El campo se coloca en el área predeterminada de la sección de diseño, pero puede organizar los campos si lo desea.


De forma predeterminada, los campos no numéricos se agregan al área de etiquetas de fila, los campos numéricos se agregan al área de valores y las jerarquías OLAP de fecha y hora se agregan al área de etiquetas de columna.

Haga clic con el botón secundario del mouse (ratón) y seleccione el comando correspondiente, Agregar a filtro de informe, Agregar a etiqueta de columna, Agregar a etiqueta de fila y Agregar a valores, para colocar el campo en un área específica de la sección de diseño.

Sugerencia También puede hacer clic y mantener presionado el botón del mouse en un nombre de campo y después arrastrar el campo entre la sección de campos y un área de la sección de diseño. Para agregar un campo varias veces, repita la operación.


X. EJERCICIOS DE TABLAS Y GRAFICOS

Ejercicio Número 1 USO DE LA FUNCIÓN BUSCARV

Aplicado a una Planilla En este ejercicio usted aplicará fórmulas básicas y función de búsqueda de Excel en la elaboración de una planilla. a.- Introducir datos en la hoja de cálculo. 1.- A partir de la celda A1 introduzca los datos como está indicado en la tabla siguiente:

b.- Guardar el libro de trabajos.

1.- Haga clic sobre el icono “Guardar”. 2.- Escriba el nombre del archivo que le indicará el profesor(a) y luego clic a Guardar Nota: A medida que avance el trabajo, guarde periódicamente su archivo. c.- Fórmula de porcentaje para el Incentivo y Total Devengado. 1.- Fórmula para calcular el Incentivo: Incentivo = % del Sueldo.

" Haga clic en la celda D6. " Introduzca la fórmula: =C6*20% " Presione la tecla “Enter”.


2.- Fórmula para calcular el Devengado: Devengado = Sueldo + Incentivo.

" Haga clic en la celda E6. " Introduzca la siguiente fórmula: =C6+D6

d.- Fundamentos de la función Buscarv Busca un valor específico en la columna más a la izquierda de una matriz y devuelve el valor en la misma fila de una columna especificada en la tabla. La V de BuscarV significa �Vertical�.

1.- Sintaxis

BUSCARV(valor_buscado; matriz_buscar_en; indicador_columnas; ordenado)

• Valor_buscado: es el valor que se busca en la primera columna de la matriz. Valor_buscado puede ser un valor, una referencia a celda o una cadena de texto.

• Matriz_buscar_en: es la tabla de información donde se buscan los datos. Utilice una referencia a un rango

• Indicador_columnas: es el 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 menor que 1, BUSCARV devuelve el valor de error #¡VALOR!; si indicador_columnas es mayor que el número de columnas de matriz_buscar_en, BUSCARV devuelve el valor de error #¡REF!.

• Ordenado: Es un valor lógico que indica si desea que la función BUSCARV busque un valor igual o aproximado al valor especificado. Si el argumento ordenado es VERDADERO o se omite, la función devuelve un valor aproximado, es decir, si no encuentra un valor exacto, devolverá el valor inmediatamente menor que valor_buscado. Si ordenado es FALSO, BUSCARV devuelve el valor buscado. Si no encuentra ningún valor, devuelve el valor de error #N/A.

2.- Observaciones

• Si BUSCARV no puede encontrar valor_buscado y ordenado es VERDADERO, utiliza el valor más grande que sea menor o igual a valor_buscado.

• Si valor_buscado es menor que el menor valor de la primera columna de matriz_buscar_en, BUSCARV devuelve el valor de error #N/A.

• Si BUSCARV no puede encontrar valor_buscado y ordenado es FALSO, devuelve el valor de error #N/A.

e.- Introducción de fórmula de búsqueda para deducciones. 1.- Fórmula para calcular la Deducción del INSS


En la actualidad la deducción del INSS se realiza aplicando un % parejo para todos los

empleados, pero durante muchos años se realizó con una tabla en base al rango salarial. Con el objetivo de aplicar la fórmula de Búsqueda, este ejercicio se hará usando dicha tabla, donde aparecen rangos de sueldos y para cada uno la deducción semanal que le corresponde.

" Con las instrucciones del Profesor, introduzca la tabla de INSS que aparece a continuación:

RANGO-INF DED-SEM 0.00 0.70 130.01 1.55 208.01 2.30 286.01 3.00 364.01 3.85 468.01 5.05 624.01 6.85 858.01 9.35 1,170.01 12.60 1,560.01 16.30 1,976.01 20.30 2,418.01 24.70 2,938.01 30.00 3,562.01 38.05 4,680.01 48.00 5,720.01 58.70 6,994.01 71.75 8,554.01 87.70 10,452.01 110.80 13,554.01 134.35 15,556.01 152.80 17,550.01 171.20 19,543.01 189.60 21,537.01 208.05

Ahora, introduzca el número de semanas siguiendo las indicaciones de su profesor(a)

" Con las instrucciones de su Profesor(a) introduzca la siguiente fórmula de búsqueda para calcular el INSS que corresponde al primer empleado.

= (BUSCARV(E6;Hoja2!$A$2:$B$25;2)) * Hoja2!$B$27

(Aparecerá esta barrita indicando que argumento de la función esta siendo introducido en negritas)


" Presione la tecla “Enter”. 2.- Fórmula para calcular la Deducción del IR Para hacer el cálculo del IR se hace uso de una tabla donde se colocan rangos de salarios

y para cada rango una cantidad base a pagar, un porcentaje de IR que se aplica a la diferencia del sueldo del empleado con el correspondiente valor de la columna Exceso de.

Ejemplo: Si un empleado gana 15,408.08, usando la tabla indicada abajo, a este empleado le corresponde pagar el siguiente IR:

IR = (SUELDO – EXCESO DE) * %IR + BASE

IR = (15,408.08 �8,333.33)*0.15 + 416.67 = 1,477.88 Donde: Los valores Exceso de, %IR y Base se obtienen de la tabla. Para obtener estos valores

se usa la función BUSCARV que se explica a continuación. A continuación se presenta el procedimiento para calcular la DED-IR

" Con las instrucciones de su Profesor, introduzca la tabla de IR que aparece a continuación:

LIM-INF BASE %-IR EXCESO DE

0.08 0.00 0.00 0.00 4,166.75 0.00 0.10 4,166.67 8,333.42 416.67 0.15 8,333.33

16,666.75 1,666.67 0.20 16,666.67 25,000.08 3,333.33 0.25 25,000.00

" Con las instrucciones de su Profesor, introduzca la siguiente fórmula de búsqueda

para calcular el IR que corresponde al primer empleado.

=(E6-BUSCARV(E6;Hoja2!$A$31:$D$35;4)) * BUSCARV(E6;Hoja2!$A$31:$D$35;3) + BUSCARV(E6;Hoja2!$A$31:$D$35;2)

" Presione la tecla “Enter”.

Sueldo – Exceso de %IR

BASE


f.- Fórmulas de totales para Deducción y Neto a pagar 1.- Fórmula para calcular la Deducción Total. DED_TOT = DED_INSS+ DED_IR

" Haga clic en la celda H6. " Introduzca la siguiente fórmula: = F6+G6 " Presione la tecla “Enter”.

2.- Fórmula para calcular el Total A Pagar. A_PAGAR = DEVENGADO - DED_TOT

" Haga clic en la celda I6. " Introduzca la fórmula: = E6-H6 " Presione la tecla “Enter”.

g.- Copiado de fórmulas. 1.- Seleccione el rango de celdas D6:I6. 2.- Coloque el puntero del mouse en la esquina inferior derecha de la selección donde hay un

cuadrito negro.(El mouse cambia de forma a una cruz delgadita negra: +). 3.- Haga un clic sostenido y arrastre el mouse hacia abajo hasta la línea donde se

encuentran los datos del último empleado (fila 17). Luego suelte el botón. h.- Calculando Grandes Totales. 1.- Haga clic en la celda A18. 2.- Escriba el texto: GRANDES TOTALES 3.- Seleccione el rango de celdas: C6:C17. 4.- Haga clic en el icono “Autosuma” para sumar los números seleccionados y colocar el

resultado en la celda C18. 5.- Haga clic en la celda C18. 6.- Señale el cuadrito que está en la esquina inferior derecha de la celda. 7.- Haga un clic sostenido y arrastre hacia la derecha hasta la celda I18 para copiar la función

suma en esas celdas y así totalizar el resto de datos. 8.- Haga clic en el icono “Guardar”. i.- Dar formato a la hoja de cálculo. Con los procedimientos aprendidos en los ejercicios anteriores, mejore la presentación de la tabla haciendo lo siguiente:


1.- Cambie la orientación de la página a horizontal 2.- Seleccione toda la hoja de cálculo y cambie la fuente a Times New Roman, cambie

también el tamaño a 12. En caso de que algunos títulos no alcancen aumente el ancho de las columnas.

3.- Seleccione las celdas A1 y A2. Luego haga clic sobre el icono “Negrita”. 4.- Seleccione la celda A1 y cambie el tamaño de la fuente a 16. 5.- Seleccione las celdas desde A1 hasta I1 y haga clic sobre el icono “Combinar y

centrar” . Seleccione las celdas desde A2 hasta I2 y haga clic sobre el icono

“Combinar y centrar” .

6.- Seleccione el rango de celdas A5:I5 y ponga los atributos siguientes: Negrita Centrar Color de relleno gris al 25 % 7.- Seleccione el rango de celdas A5:I18 y ponga bordes: Todos los bordes y Borde de

cuadro grueso. 8.- Seleccione las celdas A18:I18 y ponga Negrita y Color de relleno gris al 25 %. 9.- Seleccione el rango de celdas de la C6 al I18 y ponga a los números formato de Número,

con separador de miles y dos decimales. 10.- Ordenar los registros de empleados en base al nombre. 11.- Prepare la hoja de cálculo para imprimir, centrando la página horizontal y verticalmente. 13.- Haga clic sobre el icono “Presentación preliminar” para ver su trabajo. 14.- Haga clic sobre el icono “Guardar”.


Ejercicio Número 2

ACTUALIZACION DE UNA HOJA DE CALCULO Aplicado a una Planilla

Se usará la planilla del mes de Enero para hacer modificaciones y obtener la planilla del mes de Febrero. Es conveniente que antes de realizar los cambios guarde el trabajo con el nombre de Planilla Febrero. a) Modificar datos existentes.

1. Haga clic en la celda A2. 2. Haga clic en el área de la Barra de fórmulas donde se muestra el contenido de la celda. 3. Introduzca el nuevo nombre para que ahora diga Febrero en lugar de Enero. 4. Presione la tecla “Enter”.

b) Suprimir e insertar registros. El empleado Amílcar Castro López fue despedido a fines de Enero, para ocupar su puesto fue

contratado el señor Carlos Alberto Martínez Ruíz; estos cambios deben reflejarse en la planilla de Febrero.

1. Con el puntero sobre el número de la fila a eliminar, en este caso el 6, hacer clic al botón

derecho del mouse 2. Haga clic sobre la opción “Eliminar”. Los datos del Empleado Amílcar Castro

desaparecieron. 3. Ahora seleccione la fila 8. 4. Con el puntero sobre el número de la fila donde se desea insertar una nueva, en este caso

el 8, hacer clic al botón derecho del mouse 5. Haga clic sobre la opción “Insertar”. Para insertar los datos del nuevo empleado. 6. Haga clic en la celda A8 y escriba a partir de ella los siguientes datos:

Carlos Alberto Martínez Ruiz 22746 1000

7. Seleccione el rango de celdas D7:G7. 8. Coloque el puntero del Mouse en la esquina inferior derecha de la selección y arrastre

hacia abajo hasta la fila 8. Esto para copiar las fórmulas en la nueva fila insertada. c) Eliminar columnas. Para este mes la empresa ha decidido suspender el Incentivo porque hubo muy poco trabajo;

en la planilla deben eliminarse las columnas INCENTIVO Y DEVENGADO y hacer los cambios necesarios en el resto de columnas.


A continuación se presentan los pasos a seguir para eliminar las dos columnas y hacer los cambios necesarios en el resto de columnas:

1. Seleccione las columnas D y E luego eliminarlas.

Observe que el resto de columnas se rellenan con el texto #!REF! que corresponde a un error de referencia, es decir, se está haciendo referencia a la celda equivocada. Recuerde que para calcular la Deducción del INSS y del IR, se usaba la celda E6 donde estaba el Devengado. Pero ese dato ya no existe ahora en la celda E6, hay otro dato. Se deben cambiar las fórmulas para hacer referencia al Sueldo que está en la celda C6.

2. Haga clic en la celda D6 y en la barra de fórmulas observará la fórmula usada para calcular

la DED-INSS, borre el #¡REF! y en su lugar escriba C6. 3. Haga clic en la celda E6 y observe que la fórmula DED-IR presenta varios #¡REF!

teniendo que borrar cada uno de ellos y en cada caso escribir C6. 4. Observe que con esto se arreglan dos fórmulas DED-IR y DED-TOTAL. 5. Haga clic en la celda G6. y sustituya #¡REF! por C6. 6. Seleccione el rango de celdas D6:G6. 7. Coloque el puntero del Mouse en la esquina inferior derecha de la selección y arrastre

hacia abajo hasta la fila 17, para copiar las fórmulas correctas para el resto de empleados.


Ejercicio Número 3

FUNCIONES ESTADISICAS Aplicado a una Planilla

Para el mes de Febrero la empresa decide hacer estadísticas para poder saber cuál es el salario promedio, el salario mínimo y el máximo que devengan sus empleados. Todos estos datos deben reflejarse en la nómina de ese mes. a) Procedimiento para introducir fórmulas estadísticas.

1. Haga clic en la celda A21 y a partir de ella escriba los datos siguientes:

Sueldo Promedio: Sueldo Máximo: Sueldo Mínimo: Sueldo Total: Desviación Estándar Varianza: Total de Empleados:

2. Haga clic en la celda B21 e introduzca la siguiente función:

=PROMEDIO (Rango)

Notas: Excel le mostrará una etiqueta con los argumento de la función promedio

que usted necesita ingresar.

Las celdas que constituyen el Rango son las del Sueldo, C6:C17 tanto para esta fórmula, como para las siguientes.

3.- Haga clic en la celda B22 e introduzca la función siguiente: =MAX(Rango) 4.- En la celda B23 introduzca la siguiente función: =MIN(Rango)

5.- En la celda B24 debe insertar la función siguiente: =SUMA(Rango)

6.- En la celda B25 introduzca la fórmula para calcular la desviación estándar: =DESVESTP(Rango) 7.- En la celda B26 debe introducir la función para calcular la varianza:


=VARP(Rango)

8.- Finalmente en la celda B27 introduzca la función para contar los empleados: =CONTARA(Rango)

9.- Seleccione el rango de celdas A21:B27 y ponga borde “Todos los bordes” y “Borde de cuadro grueso”.

10.-Seleccione el rango de celdas A21:A27 y ponga a ese rango Negrita, Cursiva y color de

relleno Gris al 25 %. 11.- Seleccione el rango de celdas B21:B26 y ponga Formato a los números.

12.- Haga clic sobre el botón “Guardar” .


Ejercicio Número 4

FUNCIONES FINANCERIAS a) Cálculo del valor presente

1.- Fundamentos de la función VA Devuelve el valor actual de una inversión. El valor actual es el valor que tiene

actualmente la suma de una serie de pagos que se efectuarán en el futuro. Por ejemplo, cuando pide dinero prestado, la cantidad del préstamo es el valor actual para el prestamista.

Sintaxis VA(tasa; nper; pago; vf; tipo)

# Tasa: Es la tasa de interés por período. # Nper: Es el número total de períodos de pago en una inversión. # Pago: Es el pago que se efectúa en cada período y que no cambia durante

la vida de la inversión. # Vf: Es el valor futuro o el saldo que desea lograr después de efectuar el

último pago. Si el argumento vf se omite, se asume que el valor es 0 (por ejemplo, el valor futuro de un préstamo es 0). Si desea ahorrar $50.000 para pagar un proyecto especial en 18 años, $50.000 sería el valor futuro. De esta forma, es posible hacer una estimación conservadora a cierta tasa de interés y determinar la cantidad que deberá ahorrar cada mes.

# Tipo: Es el número 0 ó 1 e indica el vencimiento de los pagos. * 0 u omitido : Pagos al final del período * 1 : Pagos al inicio del período

Observaciones

$ Mantenga uniformidad en el uso de las unidades con las que especifica los argumentos tasa y nper. Si realiza pagos mensuales sobre un préstamo de 4 años con un interés anual del 12%, use 12%/12 para el argumento tasa y 4*12 para el argumento nper. Si realiza pagos anuales sobre el mismo préstamo, use 12% para el argumento tasa y 4 para el argumento nper.

$ En las funciones financieras el efectivo que paga, por ejemplo, depósitos

en cuentas de ahorros, se representa con números negativos; el efectivo que recibe, por ejemplo, cheques de dividendos, se representa con números positivos. Por ejemplo, un depósito de $1000 en el banco, se representaría con el argumento -1000 si usted es el depositario y con el argumento 1000 si usted es el banco.


2.- Ejemplo para el cálculo del valor presente Enunciado del Problema: Usted tiene que pagar 50,000.00 córdobas cada año, durante 20 años a un prestamista

que cobra el 8% de interés. ¿Cuánto es el valor del préstamo (valor actual)? Procedimiento para la solución del problema:

• Entre a Excel. • Introduzca los datos indicados en la tabla abajo.

CELDA DATO A INTRODUCIR

C1 CALCULO DEL VALOR PRESENTE B6 ANUALIDAD B7 TASA DE INTERES B8 PLAZO D6 50,000.00 D7 0.08 D8 20 E6 CORDOBAS E7 (8% Anual) E8 AÑOS

B15 VALOR ACTUAL.

• Introduzca la fórmula del valor actual siguiendo las indicaciones de su profesor(a). Los valores entre corchetes son opcionales.

• Presione la tecla “Enter” para completar la introducción de la función. • Presione la tecla “Page Down” para avanzar una página.

b) Cálculo del valor futuro. 1.- Fundamentos de la función VF Devuelve el valor futuro de una inversión basándose en pagos periódicos constantes y

en una tasa de interés constante. Sintaxis VF(tasa; nper; pago; va; tipo)

# Tasa: Es la tasa de interés por período. # Nper: Es el número total de pagos de una inversión. # Pago: Es el pago que se efectúa cada período y que no puede cambiar

durante la vigencia de la inversión.


# Va: Es el valor actual de la cantidad total de una serie de pagos futuros. Si el argumento va se omite, se considerará 0 y se considerara el argumento pago.

# Tipo: Indica cuándo vencen los pagos; es el número 0 si los pagos vencen al final del período ó 1 si los pagos vencen al inicio del período. Si el argumento tipo se omite, se considerará 0.

2.- Ejemplo para el cálculo del Valor Futuro Enunciado del problema: Si usted ahorra anualmente la cantidad de 200,000.00 en un banco que paga 10% de

interés anual y desea saber cuánto dinero tendrá acumulado dentro de 20 años, usted puede usar la función de excel VF que a continuación se explica.

Procedimiento para la solución del problema:

• Introduzca los datos indicados en la tabla.

CELDA DATO A INTRODUCIR C21 CALCULO DEL VALOR FUTURO B26 ANUALIDAD B27 TASA DE INTERES B28 PLAZ0 D26 200,000.00 D27 0.10 D28 20 E26 CORDOBAS E27 (10% ANUAL) E28 AÑOS B35 VALOR FUTURO

• Introduzca la fórmula del valor futuro siguiendo las indicaciones del profesor.

• Para finalizar la introducción de la fórmula presione la tecla “Enter”. • Presione la tecla “Page Down” para avanzar una página.

c) Cálculo de una cuota mensual. 1.- Fundamentos de la Función Cuota Mensual - Pago Devuelve el pago periódico de una anualidad basándose en pagos constantes y en una

tasa de interés constante. Sintaxis PAGO(tasa; nper; va; vf; tipo)


# Tasa: Es la tasa de interés por período. # Nper: Es el número total de períodos de pago. # Va: Es el valor actual o la cantidad total de una serie de pagos futuros. # Vf: Es el valor futuro o saldo en efectivo que desea lograr después de

efectuar el último pago. Si el argumento vf se omite, se asume que el valor es 0 (por ejemplo, el valor futuro de un préstamo es 0).

# Tipo: Es el número 0 u omitido si los pagos se vencen al final del período ó 1 si los pagos se vencen al inicio del período.

Sugerencia: Para encontrar la cantidad total que se pagó durante la vida de la anualidad,

multiplique el valor devuelto por PAGO por el argumento nper. 2.- Ejemplo para el cálculo del Pago o Cuota Mensual Enunciado del problema: Usted prestó 800,000.00 córdobas a un interés de 14% anual para pagarse en 4 años; los

pagos deberán hacerse mensualmente, ¿Cuál es la cuota mensual que deberán pagarle? Procedimiento para la solución del problema:


CELDA DATO A INTRODUCIR C41 CALCULO DE CUOTA MENSUAL B46 VALOR ACTUAL B47 TASA DE INTERES B48 PLAZO D46 800000 D47 0.14 D48 4 E46 CORDOBAS E47 (14% Anual) E48 AÑOS B55 CUOTA MENSUAL

• Introduzca la fórmula para calcular la cuota mensual, siguiendo las indicaciones

del profesor. • Presione la tecla “Enter”. • Presione la tecla “Page Down” para avanzar una página.

d) Cálculo del tiempo para acumular un valor futuro. 1.- Fundamentos de la función NPER


Devuelve el número de períodos de una inversión basándose en los pagos periódicos

constantes y en la tasa de interés constante. Sintaxis NPER(tasa; pago; va; vf; tipo)

# Tasa: es la tasa de interés por período. # Pago: es el pago efectuado en cada período; debe permanecer constante

durante la vida de la inversión. # Va: es el valor actual o la suma total de una serie de futuros pagos. # Vf: es el valor futuro o saldo en efectivo que se desea lograr después del

último pago. Si vf se omite, el valor predeterminado es 0 (por ejemplo, el valor futuro de un préstamo es 0).

# Tipo: es el número 0 u omitido si los pagos se vencen al final del período ó 1 si los pagos se vencen al principio del período.

2.- Ejemplo para calcular el tiempo NPER en acumular un capital en base a anualidades. Enunciado del problema: Usted está ahorrando 10,000.00 córdobas cada año en un banco que paga el 9% de

interés anual; quiere saber en cuántos años podrá acumular 100,000.00. Procedimiento para la solución del problema:

• Haga click en la celda C61 e introduzca los datos detallados en la tabla.

CELDA DATO A INTRODUCIR C61 CALCULO DEL TIEMPO PARA ACUMULAR UN VALOR

FUTURO B66 ANUALIDAD B67 TASA DE INTERES B68 VALOR FUTURO D66 10,000.00 D67 0.09 D68 100,000.00 E66 CORDOBAS E67 (9% ANUAL) E68 CORDOBAS B75 TIEMPO

• Siguiendo las indicaciones del profesor, introduzca la fórmula que calculará el número de períodos para obtener la cantidad deseada.

• Presione la tecla “Enter”para concluir. • Presione la tecla “Page Down” para avanzar una página.


3.- Cálculo del período de tiempo para acumular un capital. Enunciado del problema: Si deposita la cantidad de 1,000,000.00 en un banco que paga 10 % de interés anual

capitalizable mensualmente y desea saber en cuántos años podrá doblar el capital sin hacer un solo depósito más (solo con los intereses cobrados ), puede usar la función NPER explicada anteriormente usando el argumento Va ( que en el ejemplo anterior no se usó; el argumento Pago no se usará porque no hay anualidades).


• Introduzca los datos indicados en la tabla siguiente:

CELDA DATO A INTRODUCIR C81 CALCULO DEL TIEMPO PARA ACUMULAR UN

CAPITAL B86 VALOR ACTUAL B87 TASA DE INTERES B88 VALOR FUTURO D86 1,000,000.00 D87 0.10 D88 2,000,000.00 E86 CORDOBAS E87 (10% ANUAL) E88 CORDOBAS B95 TIEMPO PARA B96 DOBLAR LA INVERSION

• Siguiendo las indicaciones del profesor, introduzca la fórmula para calcular el

número de años que debe esperar para acumular el doble de lo que depositó.

• Presione la tecla “Enter”. • Presione la tecla “Page Down” para avanzar una página.

e) Cálculo del interés periódico mensual 1.- Fundamentos de la función Tasa Devuelve la tasa de interés por período de una inversión. Sintaxis TASA (nper; pago; va; vf; tipo; estimar)

# Nper: Es el número total de períodos de pago en una inversión.


# Pago: Es el pago que se efectúa en cada período y que no puede cambiar durante la vida de la inversión.

# Va: Es el valor actual de la cantidad total de una serie de pagos futuros. # Vf: Es el valor futuro o un saldo en efectivo que desea lograr después

de efectuar el último pago. Si el argumento vf se omite, se asume que el valor es 0 (por ejemplo, el valor futuro de un préstamo es 0).

# Tipo: Es el número 0 u omitido si los pagos vencen al final del período ó 1 si los pagos vencen al inicio del período.

# Estimar: Es la estimación de la tasa de interés. Observaciones

$ Si el argumento estimar se omite, se supone que es 10%. $ Si TASA no converge, use diferentes valores para el argumento estimar.

TASA generalmente converge si Estimar se encuentra entre 0 y 1. 2.- Ejemplo para calcular el interés periódico mensual Enunciado del problema: Si le ofrecen participar en un proyecto a completarse en 60 meses en donde usted debe

invertir 100,000.00 córdobas y le dicen que al final de la inversión usted tendrá un valor futuro de 180,000.00; obtener la tasa de interés con la función Tasa.



CELDA DATO A INTRODUCIR C101 INTERES PERIODICO MENSUAL B106 INVERSION B107 PLAZO B108 VALOR FUTURO D106 100,000.00 D107 60 D108 180,000.00 E106 CORDOBAS E107 MESES E108 CORDOBAS B115 TASA DE INTERES

• Siguiendo las indicaciones del profesor, introduzca la función para calcular la

tasa de interés. • Presiona la tecla “Enter”.


Ejercicio Número 5

MANEJO DE DATOS Y CONSTRUCCION DE GRAFICOS a) Introducción de datos Introduzca los siguientes datos correspondientes al inventario de papelería, materiales y

equipo de la compañía de seguros �San Jorge� Guarde el trabajo con el nombre de Inventario b) Cálculo de la columna Total

- Haga clic en la celda F6 - Introduzca la fórmula del total que es: Precio por unidad * Existencia - Copia la fórmula anterior a los demás artículos (hasta la celda F19)

c) Ordenar los datos en forma ascendente según la columna Clasificación y Artículos.

- Haga clic en la celda A5 - En la ficha “Datos”, grupo �Ordenar y Filtrar�, haga clic al botón “Ordenar”


- En la casilla “Ordenar por” seleccione la columna que le indique su profesor(a) y observe que la casilla �Criterio de ordenación” tenga “A a Z”.

- Haga clic al botón “Agregar nivel”. - En la casilla “Luego por” seleccione la columna “ARTICULOS” y observe que la

casilla �Criterio de ordenación” tenga “A a Z”. - Finalmente haga clic a Aceptar.

Observe que toda la tabla se ha ordenado en forma alfabética y ascendentemente, tomando como primer criterio los datos de la columna Clasificación y en segundo lugar los datos de la columna Artículos.

d) Aplicar Filtros para ver sólo los datos que corresponden a una clasificación

- Estando ubicado en alguna celda de la fila 5 (donde se encuentra el encabezado de la tabla), hacer clic a la ficha “Datos”, en el grupo “Ordenar y filtrar”, hacer clic al botón “Filtros”. Observe que ahora cada celda donde se encuentran los encabezados de columna, aparece como cuadros de lista, es decir, al lado derecho hay un indicador (triangulito hacia abajo) que permite seleccionar el criterio con respecto al cual se desea aplicar un filtro a esa tabla.

- Si desea ver sólo los artículos correspondientes a la clasificación Mobiliario, entonces

siga las instrucciones que le oriente su profesor(a).

Los demás artículos de papelería y equipo se ocultan y sólo se presentan en pantalla los de la clasificación tipo mobiliario.

- Haga lo mismo con los artículos de papelería y finalmente con los de equipo.

- Si desea que aparezcan nuevamente todos los artículos, haga clic en el indicador del

cuadro de lista y luego haga clic a la opción (Todas).


- Si desea desactivar los filtros en la ficha “Datos”, grupo �Ordenar y Filtrar�, haga clic al

botón “Filtros”. e) Construir los Subtotales por clasificación y el gran total.

- Estando ubicado en la celda A5, en la ficha “Dato”, grupo �Esquema�, haga clic a “Subtotales”.

- Observe que la casilla �Para cada cambio en:� tenga señalada la opción CLASIFICACION; en caso contrario, cambiarlo a esta forma.

- Observe que la casilla �Usar función:� tenga la opción Suma, por ser esta la función que nos interesa en el ejemplo que se está desarrollando.

- En la casilla �Agregar subtotal a:�, dejar marcada sólo la que nos interesa, en este caso la columna Total; si aparece otra columna marcada que no nos interesa, por ejemplo Unidades, entonces quitarle la marca haciendo clic en la casilla de verificación que aparece a la izquierda.

- Finalmente dar clic a Aceptar. - Observe que después de cada bloque de clasificación (Equipo, mobiliario, papelería)

apareció una nueva fila que presenta el total para cada clasificación. Además, al final de la hoja se observa una nueva fila con el total general.

Al lado izquierdo de la tabla se pueden apreciar unas rayas que señalizan cada bloque según el tipo de clasificación; a la izquierda de la fila de cada subtotal se aprecian unos pequeños cuadros con un signo menos lo que permite poder observar todas las filas correspondientes a ese subtotal. En caso de querer observar sólo los subtotales, hacer clic a cada botón del signo menos y éste se cambia a signo más .


f) Construir un gráfico que presente el inventario por tipo de artículo (equipo, mobiliario, papelería).

- Haga clic a los botones de signos menos que corresponden a los subtotales, con el

objetivo de sólo observar los subtotales. - Oculte las columnas B, C, D y E haciendo lo siguiente:

" Seleccione las columnas B, C, D y E. " Con el puntero en el área seleccionada, haga clic al botón derecho del mouse y

luego clic a �Ocultar�. - Antes de iniciar el gráfico, mejore la presentación de la tabla haciendo lo siguiente:

" Seleccione la tabla A5:F23

" En la ficha �Inicio�, grupo �Fuente�, haga clic al botón Bordes y luego clic a la opción Todos los bordes.

" Seleccione los encabezados de la fila 5 (A5:F5) y póngale Negrita y Centrado en la celda.

" Modifique el ancho de la columna A a 25 (180 píxeles) y la F a 20 (145 píxeles). " Seleccione las celdas A1 y F1 y luego en la ficha �Inicio�, grupo �Alinear�, haga

clic al botón Combinar y Centrar. Active la Negrita y cambie la letra a 12. " Seleccione las A2 y F2 y luego haga clic al botón Combinar y Centrar. Active la

Negrita. - Seleccione los datos que se necesitan para el gráfico, desde la celda A10:A22 y luego con

la tecla CTRL presionada el rango desde F10:F22. - En la ficha �Insertar�, grupo �Gráfico�, haga clic al tipo de gráfico que se le indicará. - En la ficha �Presentación�, grupo �Etiqueta�, hacer clic al botón �Título del gráfico� y

seleccionar la opción �Encima del gráfico�. Haga clic donde dice Título del gráfico, borre este texto e introduzca el siguiente: �Variación porcentual por tipo de artículo�. A este texto poner un tamaño de 16.

- En la ficha �Presentación�, grupo �Etiqueta�, hacer clic al botón �Leyenda� y seleccionar la opción �Mostrar leyenda en la parte inferior�.

- En la ficha �Presentación�, grupo �Etiqueta�, hacer clic al botón �Etiquetas de datos� y seleccionar la opción �Más opciones de etiquetas de datos…�. Quitar la marca a Valores y ponerla en �Porcentaje� y marcar el botón �Extremo externo�. Finalmente �Cerrar�.

- Insertar una columna en A, y luego mover el gráfico para colocarlo en A28. Si es necesario modificar el ancho del gráfico para que se acomode entre las columnas A y H.

g) Configurar la página para terminar de mejorar su presentación

- En la ficha �Diseño de Página�, grupo �Configurar página”, haga clic botón �Márgenes� y luego clic a �Márgenes personalizados�, haga clic a los botones para centrar la página Horizontalmente y Verticalmente, luego �Aceptar�.

- Finalmente, haga clic al botón Vista Preliminar para observar como queda el gráfico.


Ejercicio Número 6. CALCULO CON DATOS TIPO FECHA Y FORMULA DE BÚSQUEDA

Modificar la estructura de la planilla �plan enero�, haciendo que la columna incentivo funcione según la antigüedad. a) Insertar columna con los datos de la antigüedad

- Recupere la hoja de cálculo PlanEnero - Guarde la hoja con el nombre que le indicarán. - Seleccione la columna D - Con el puntero en el área seleccionada, clic al botón derecho del mouse, luego clic a

Insertar. - En la celda D5 poner el encabezado FECHA/INICIO y ampliar el ancho de la columna

hasta que el rótulo se vea bien. - Seleccionar el rango de celdas desde la D6 hasta la D17 y darle un formato de fecha así:

En la ficha �Inicio�, grupo �Número�, hacer clic al botón �Formato de Número� y seleccionar �Fecha corta�.

- Introducir los siguientes datos:

- Seleccione el rango de celdas D6:D17 y haga clic al botón Centrar para que los datos de las fechas queden centrados en sus respectivas celdas.

b) Insertar una columna que calcule el número de años

- Seleccione la columna E - Con el puntero en el área seleccionada, haga clic al botón derecho del mouse y luego clic

a Insertar.


- En la celda E6 introduzca el encabezado # AÑOS - Seleccionar el rango de celdas E6:E17 y poner el formato de Número. - Nos interesa introducir en la celda E6 una fórmula que reste la fecha en que se emite la

planilla con la fecha de inicio al trabajo de cada empleado. Para la fecha de emisión de la planilla se utiliza la función AHORA(), que es la que tiene la memoria de la microcomputadora, o bien, si lo desean pueden utilizar una fecha fija. Esta diferencia nos da el número de días, por lo que hay que dividirla entre 365 para obtener la diferencia en años. Finalmente la fórmula se la indicará su profesor(a).

c) Introducir la tabla de incentivos por antigüedad

- Hacer clic Hoja2 - Hacer clic en la celda A38 e introducir los siguientes datos:

Esta tabla presenta en la columna A el límite inferior del rango en años de trabajo y en la

columna B el porcentaje que se le debe aplicar al Sueldo para obtener el incentivo. Ejemplo: Caso de una persona que gana C$ 5,000.00 y tiene 5 años de laborar en la

empresa. ¿Cuál sería el incentivo que le correspondería?

• En la tabla anterior se observa que los 5 años de trabajo se ubican en la fila A41, es decir, a partir de 3 que es el límite inferior; por lo tanto, el porcentaje que le corresponde aparece al lado derecho en la columna B con un valor de 8%.

• Ahora se aplica el 8% al sueldo de C$ 5,000.00 obteniendo lo siguiente:

5000*8/100 = 400

d) Introducir la fórmula de búsqueda A continuación se introducirá la fórmula de búsqueda, la cual tiene como función calcular

el incentivo multiplicando el Sueldo con el porcentaje que aparece en la tabla anterior, según el número de años de laborar en la empresa.

- Haga clic en la Hoja1 - Haga clic en la celda F6 y borre la fórmula que actualmente está en dicha celda - Introducir la nueva fórmula, que le indique su profesor(a).


Ejercicio Número 7.

USO DE LA FORMULA CONDICIONADA

Modificar la estructura de la planilla planenero, introduciendo una nueva columna en concepto de otra deducción, aplicando un porcentaje según varíe el sueldo a) Insertar una nueva columna, donde la condición sea, “Si el sueldo es menor a C$

2,500.00 aplicar el 2%, en caso contrario aplicar el 5%”

La fórmula condicionada es una función que se utiliza cuando se desea presentar un resultado a partir del cumplimiento o no de cierta (s) condición (es). Devuelve un valor si la condición especificada es VERDADERA y otro valor si dicho argumento es FALSO. Utilice SI para realizar pruebas condicionales en valores y fórmulas. Sintaxis:

SI(prueba_lógica;valor_si_verdadero;valor_si_falso)

Por ejemplo: El caso de un empleado cuyo sueldo es C$ 1,000.00. ¿Cuál sería la deducción sobre la base de la condición?

En este caso, el empleado gana 1000, cantidad que es menor a 2500, por lo tanto se le debe

aplicar el 2% del Sueldo y el cálculo sería: 1000 * 2/100 = 20 Generalizando lo anterior Sueldo * 2% Pero que pasa si hay un empleado que gana 5000, entonces sobre la base de la condición se le

debería aplicar el 5% y el cálculo es: 5000 * 5/100 = 250 Generalizando lo anterior Sueldo * 5% Puede concluirse que son dos cálculos los que hay que aplicar según el cumplimiento de la

condición y por lo tanto la fórmula final debería tener el siguiente razonamiento: Si Sueldo < 2500 , entonces OtraDed=Sueldo*2%, sino OtraDed=Sueldo*5% Finalmente, para introducir la fórmula hacer lo siguiente:


- En la hoja1 seleccionar la columna J - Con el puntero en el área seleccionada, clic al botón derecho del mouse y luego clic a

Insertar. - Hacer clic en la celda J5 e introducir el encabezado OTRA-DED - Hacer clic en la celda J6 e introducir la fórmula que le indique su profesor(a) - Ahora, copiar la fórmula hacia abajo hasta la celda J17 - Introducir la fórmula de Sumatoria correspondiente al gran total de esta nueva columna,

para lo cual hacer lo siguiente: - Clic en la celda J18 - Clic dos veces al botón de Sumatoria

b) Modificar la fórmula de la deducción total (DED-TOT)

En vista que ahora hay una nueva deducción por otro concepto, se hace necesario modificar la fórmula de la deducción total, para lo cual hacer lo siguiente:

- Clic en la celda K6 - Observe que en la casilla de barra de fórmula (arriba de las letras de las columnas)

aparece la fórmula anterior =H6+I6, la cual no incluye la columna de Otra-Ded, por lo tanto, hacer clic en el extremo derecho de dicha fórmula y agregar +J6, quedando finalmente la fórmula =H6+I6+J6

- Copie la nueva fórmula hasta la celda K17

Ejercicio Número 8. FORMULA CONDICIONADA CON MAS DE DOS CRITERIOS

Realice un ejercicio similar al anterior, pero suponiendo ahora que la fórmula para la otra deducción se aplicará con los siguientes criterios: �Si el sueldo es menor o igual a C$ 2,000.00 aplicar el 2%; sino, y el sueldo es menor o igual a C$ 5,000.00 aplicar el 5%; sino aplicar el 10%� Para este caso se aplicará una fórmula condicionada dentro de otra, haciéndose necesario brindar una explicación bastante detallada como se hizo en el ejercicio anterior, con algunos ejemplos numéricos. El razonamiento de la fórmula es el siguiente:

Si Sueldo<=2000, entonces OtraDed=Sueldo*2%, sino Si Sueldo<=5000, entonces OtraDed=Sueldo*5%, sino

OtraDed=Sueldo*10%

Finalmente, para introducir la fórmula hacer lo siguiente:

- Clic en la celda J6 - Borrar la fórmula actual e introducir la nueva, que le indicara su profesor(a). - Copie la nueva fórmula hasta la celda J17



APLICACIÓN A UN LIBRO DE BANCO

Elaborar un libro de banco para llevar el control diario de los movimientos en una cuenta bancaria. a) Elaborar el formato

- Introduzca los siguientes datos a partir de la celda A1:

- En la celda C50 introduzca:

- Haga más anchas las columnas B y C a los siguientes valores: - Columna B : 19.57 (142 píxeles) - Columna C : 17.71 (129 píxeles) - Las demás columnas que queden con su ancho original de 10.71 (80 píxeles)

- Formar un recuadro que cubra desde la celda A6 hasta la celda F50:

- Seleccione el rango de celdas A6:F50 - En la ficha �Inicio�, grupo �Fuentes�, haga clic al botón bordes y luego clic a la

opción bordes externos

- Cambiar los atributos al encabezado de la tabla - Seleccione las celdas A6:F6 - En la ficha �Inicio�, grupo �Fuentes�, haga clic al botón Negrita, al botón

Bordes y clic a la opción bordes externos. - En la ficha �Inicio�, grupo �Alineación�, haga clic al botón Centrado.

- Cambiar los atributos de las líneas donde se encuentra el texto de los Saldos (fila 7 y fila

50) - Seleccione las celdas C7:F7 - En la ficha �Inicio�, grupo �Fuentes�, haga clic al botón Bordes y clic a la opción

borde inferior


- Seleccione la celda C7 y haga clic al botón Negrita - Seleccione las celdas C50:F50 - En la ficha �Inicio�, grupo �Fuentes�, haga clic al botón Bordes y clic a la opción

borde superior - Seleccione la celda C50 y haga clic al botón Negrita

- Modifique los atributos del encabezado de la hoja

- Seleccione las celdas A1:F1 (Nombre de la empresa) - En la ficha �Inicio�, grupo �Alineación�, haga clic al botón Combinar y Centrar

- En el grupo �Fuentes�, haga clic al botón Negrita, al botón Subrayado y cambie

el tamaño del texto a 16. - Seleccione las celdas A3 y E3, luego active Negrita

- Configurar la página

- En la ficha �Diseño de página�, grupo �Configurar página�, haga clic al botón Márgenes y seleccione �Márgenes personalizados…�

- Haga clic (marcar) a las casillas Horizontalmente y Verticalmente para centrar la página.

- Haga clic a Aceptar

- Haga clic en el botón de Vista preliminar para ver como quedó el formato

b) Introducir los datos

- Haga clic en la celda B3 para introducir el número de la cuenta bancaria; para este caso poner: 1001394 Bancentro.

- Haga clic en la celda F3 para introducir la fecha; primero, en la ficha �Inicio�, grupo �Número�, en botón �Formato de número�, seleccionar el formato �Fecha corta�. Ahora introduzca el dato:26-07-08

- Al rango de celdas que presentará los depósitos, salidas y saldos, conviene dar un formato numérico con coma de miles y dos decimales, para lo cual hacer lo siguiente:

- Seleccionar el rango de celdas D7:F50 - En la ficha �Inicio�, grupo �Número�, haga clic al botón �Formato de número�

y luego clic al botón �Estilo de millares�

- Haga clic en la celda F7 e introduzca el saldo anterior con el cual comienza el día, que en este caso es 1,353.21.

- Haga clic en la celda A8 e introduzca los siguientes datos que corresponden a los movimientos registrados en la cuenta bancaria


c) Introducir la fórmula del Saldo por movimiento

Alternativa # 1: Saldo anterior más el Depósito o menos la Salida del movimiento (No se recomienda por el inconveniente que se explica a continuación)

- Hacer clic en la celda F8 e introducir la fórmula:

= F7 + D8 � E8

- Copiar la fórmula hasta la celda F50 El inconveniente de esta fórmula es que el saldo correspondiente al último movimiento se repite en todas las demás líneas en blanco donde no hay movimientos; a continuación se puede apreciar este problema:

Esto se podría evitar usando una fórmula condicionada para que no escriba el saldo cuando no hay entrada o salida, pero tendría el inconveniente de brindar un resultado malo al movimiento que aparece después de un cheque anulado. Para ver mejor esta situación, hacer lo siguiente:

Saldo anterior

Entrada Salida

Problema de repetición del saldo


- Clic en la celda F8 - Introducir la fórmula condicionada siguiendo las instrucciones de su profesor: - Copiar la fórmula hacia abajo hasta la celda F49 Observe que en la fila 13 hay un cheque anulado, por lo tanto en la celda del Saldo (F13) correspondiente a dicha fila ha quedado un espacio en blanco; esto provoca que al calcular el saldo del nuevo movimiento (fila 14), se produzca un error #¡VALOR!, debido a que se hace un cálculo con un espacio en blanco, lo cual es imposible de hacer. A continuación se puede apreciar el error:

Este problema se puede solucionar cambiando la forma de hacer el cálculo del Saldo, tal como se explica en la siguiente alternativa. Alternativa # 2: Saldo de inicio del día (primer saldo) más la sumatoria de todos los depósitos, menos la sumatoria de todas las Salidas

La fórmula sería similar a la anterior, ya que siempre se utilizaría la fórmula condicionada; pero lo que cambia es lo que se hace (el cálculo) si la condición se cumple:

=SI(O(D8>0;E8>0);$F$7+suma($D$8:D8)-suma($E$8:E8);� �)

Condición (O Entrada >0 O Salida >0)

Lo que se hace si la condición se cumple (fórmula)

Lo que se hace si la condición no se cumple (dejar espacio en blanco) Función

Saldo de inicio del día, con el símbolo $ para que se conserve constante

Sumatoria del rango de celdas donde se realizan los depósitos desde la primera (D8 que es constante) hasta la celda donde se realiza el último movimiento (que es variable desde D8 hasta D49)

Sumatoria del rango de celdas donde se realizan las salidas desde la primera (E8 que es constante) hasta la celda donde se realiza el último movimiento (que es variable desde E8 hasta E49)

Error


Para introducir la fórmula haga lo siguiente:

- Haga clic en la celda F8 - Introduzca la fórmula que le indicara su profesor(a): - Copie la fórmula hasta la celda F49

Esta fórmula tiene la ventaja que no depende del saldo del movimiento anterior, sino del saldo inicial que es constante ($F$8) y la sumatoria de todos los depósitos y todas las salidas. Con la nueva fórmula los resultados se observan así:

d) Introducir la fórmula del Saldo Actual (Final) La fórmula del Saldo Actual tomando en cuenta todos los movimientos del día se obtiene

así: El Saldo anterior al inicio del día (F7) más la sumatoria de todas las celdas disponibles para ubicar los depósitos (D8:D49), menos la sumatoria de todas celdas disponibles para ubicar las salidas (E8:E49).

El procedimiento es el siguiente:

- Haga clic en la celda F50

- Introduzca la fórmula que le indicara su profesor(a)

Observe que el Saldo Actual coincide con el saldo correspondiente al último movimiento, lo cual es lo correcto. - Finalmente guardar el archivo.

e) Preparar el formato para cualquier cuenta bancaria


- Guarde el archivo con el nombre de Formato de Libro de banco - Borrar todos los datos:

- Clic en la celda B3 y borrar el número de la cuenta - Clic en la celda F3 y borrar la fecha actual - Clic en la celda F7 y borrar el saldo anterior al inicio del día - Seleccionar las celdas A8:E14 y borrar todos datos de los movimientos

- Observe que al borrar todos los datos el Saldo Actual de la celda F50 queda en cero.

- Guarde nuevamente, para que se actualice el formato de libro de banco.

Ejercicio Número 10. USO DEL FORMATO DE LIBRO DE BANCO

Con el formato de libro de banco llevar el control diario de los movimientos en una cuenta bancaria.

- Recupere el archivo Formato de Libro de Banco

- Guárdelo con otro nombre; por ejemplo CuentaNo255032

- Introduzca los nuevos datos :

- En la celda B3 el número de la nueva cuenta - En la celda F3 la nueva fecha actual - En la celda F7 el nuevo saldo anterior con el cual comienza el día dicha cuenta - A partir de la celda A8 introduzca los nuevos movimientos y observe que, gracias

a las fórmulas, aparece automáticamente el saldo al final de la línea del movimiento y el saldo actual al final de la hoja, coincidiendo dichos valores.



CREAR UNA TABLA DINAMICA PARA OBTENER UN CONSOLIDADO DE HORAS CLASE

Las tablas dinámicas son herramientas relacionadas con las listas o bases de datos creadas en hojas de cálculo. Sirven para organizar de manera sencilla y rápida grandes cantidades de datos procedentes de dichas listas. Permiten agrupar y filtrar la información por cualquiera de los campos de la lista, creando una tabla-resumen. La tabla dinámica comprende una serie de Datos presentados en forma de resumen que agrupan aspectos concretos de una información global. Es decir con una Tabla Dinámica se pueden hacer resúmenes de una Base de Datos, utilizándose para, promediar, o totalizar datos. Debe ser muy importante la cantidad de información a manejar para que el uso de la tabla dinámica se justifique. Para su utilización, se debe recurrir al menú Datos > Informe de Tablas y gráficos dinámicos Partiendo de una Planilla confeccionada, se trata de aplicar sobre la misma un principio de ordenamiento a través de una herramienta de Excel, Tablas Dinámicas. a) Introducir los datos

Disponemos de una hoja de cálculo con las horas trabajadas durante una semana por los profesores de un instituto en unos determinados cursos, tal como aparece en la siguiente tabla. Introduzca los datos a partir de la celda A1.

Profesor Curso Fecha Horas

Cisneros Arana César AutoCAD 10/09/2007 9.5 Astacio Rodríguez Alejandro AutoCAD 10/09/2007 3.6 Lanzas Aragón Pedro MS-Project 11/09/2007 5.5 Astacio Rodríguez Alejandro Land Desktop 12/09/2007 8 Cisneros Arana César MS-Project 11/09/2007 4 Cisneros Arana César Land Desktop 14/09/2007 10 Lanzas Aragón Pedro AutoCAD 13/09/2007 4 Astacio Rodríguez Alejandro Land Desktop 14/09/2007 7 Lanzas Aragón Pedro AutoCAD 13/09/2007 6.5

Queremos crear una tabla dinámica que muestre, para cada profesor del instituto, en páginas individuales, el total de horas trabajadas en cada uno de los cursos durante la última semana.


b) Crear la tabla dinámica

Crear la tabla Dinámica con el siguiente procedimiento:

1º. En la hoja donde introdujo la tabla, sitúate en una de las celdas que contienen los datos (Para este ejemplo A1), ve a la ficha Datos (menú principal) y hacer clic al botón Tabla Dinámica.

2º. En el cuadro de diálogo Crear Tabla Dinámica, se solicita seleccionar los datos que se desea analizar. En este caso, dejamos la opción preseleccionada Tabla#, que corresponde al rango correcto. En la parte inferior del cuadro de diálogo, se debe elegir donde se desea colocar el informe de Tabla Dinámica, hacer clic al botón Hoja de cálculo existente, e indica la posición de destino de la tabla dinámica tecleando (o seleccionando con el ratón) la dirección de la celda superior izquierda a partir de la cual se colocará la tabla (Hágalo en la Hoja2, celda A5). Finalmente, clic a Aceptar.

3º. Para realizar el diseño, hacer la distribución de los campos en la tabla a crear:

En la parte derecha superior, se muestra la lista de campos y en la parte derecha inferior aparece el área en donde se diseñará la tabla, que está dividida en cuatro secciones (Filtro de informe, Rótulos de Fila, Rótulos de Columnas y Sumatoria Valores), en las que se pueden colocar los distintos campos, pulsando sobre el nombre del campo y arrastrándolo a una sección.

A la hora de organizar los datos en nuestro ejemplo deberá tenerse en cuenta lo siguiente:

⇒ El campo que se coloque en la sección Filtro de informe aparecerá en forma de una

lista desplegable desde la que se podrá seleccionar aquel elemento del que se desee mostrar el resumen.


⇒ El campo que se coloque en la sección Rótulos de Fila, mostrará sus elementos como encabezados o títulos de las filas en la tabla

⇒ El campo que se coloque en la sección Rótulos de Columnas, mostrará sus elementos como encabezados de las columnas de la tabla

⇒ En cuanto al campo que se coloque en la sección Sumatoria de Valores, sus datos se someterán a una determinada operación de cálculo: Suma (es la que se ofrece por defecto cuando los datos de este campo son todos numéricos), Contar (la que se ofrece por defecto en los demás casos), Promedio, Mínimo, Máximo, Producto, etc.

En nuestro ejemplo, por tanto, colocaremos los campos del siguiente modo: % El campo Profesor en la sección Filtro % El campo Curso en la sección Rótulo de Fila % El campo Fecha en la sección Rótulo de Columnas % El campo Horas (que contiene los valores que queremos sumar) en la sección

Sumatoria de Valores, aceptando la función SUMA que Excel propone por defecto. % La tabla Dinámica se presenta a continuación:

c) Modificar la tabla dinámica

Se desea modificar la tabla dinámica para presentar las horas y los costos de los cursos impartidos en un período determinado. A continuación el procedimiento:

1º. En la hoja donde introdujo la tabla, crear una nueva tabla con los siguientes datos:


2º. Modificar la tabla de los datos agregando en E1 el encabezado Costo/hora e introduciendo

en E2 la siguiente fórmula: =SI(B2<>"",BUSCARV(B2,$H$2:$I$4,2,FALSO),0) Copiar la fórmula en la columna

3º. Modificar la tabla de los datos agregando en F1 el encabezado Costo Total e introduciendo en F2 la siguiente fórmula: =D2*E2 Copiar la fórmula en la columna

4º. Ir a la hoja de la tabla dinámica y realizar los siguientes cambios: ⇒ Hacer clic en el interior de la tabla dinámica ⇒ Clic a la ficha Opciones y luego clic al botón Cambiar origen de datos ⇒ Seleccionar el nuevo rango de datos: A1:F10 y Aceptar ⇒ Mover el campo Profesor a Rótulos de Fila, arriba de Curso ⇒ Arrastrar el campo Fecha a Filtro de informe ⇒ Arrastrar el campo Costo/hora para Rótulos de fila ⇒ Arrastrar el campo Costo Total para Sumatoria de Valores

5º. Mejorar la presentación de la tabla dinámica

⇒ Cambiar el formato de Esquema a Tabular, haciendo clic a la ficha Diseño, luego clic

al botón Diseño de informe y finalmente clic a Mostrar en formato tabular. ⇒ Quitar el total al campo Curso:

" Puntero en el encabezado del campo CURSO y clic al botón derecho del mouse

" Clic a Configuración de campo " Ninguno " Aceptar

⇒ Cambiar el formato a las columnas de los Valores " Con puntero en el encabezado del campo Suma de horas, clic al botón

derecho " Clic a Configuración de campo " En la casilla nombre personalizado introducir el texto: Total Horas " Clic al botón formato de número " Clic a la categoría Número " Marcar el botón Usar separadores de miles " Aceptar " De forma similar hacer con la columna Suma de Costo Total


⇒ Ocultar la fila 5 ⇒ Insertar una fila que separe a cada profesor

" Con puntero en la columna Profesor clic al botón derecho del mouse " Clic a Configuración de campo " Clic a la ficha Diseño e Impresión " Clic al botón Insertar línea en blanco después de cada etiqueta " Aceptar

Finalmente la tabla dinámica se presenta así:


Ejercicio Número 12. CREAR UNA TABLA DINAMICA PARA OBTENER

CONSOLIDADO DE UNA ESTACION DE PEAJE Partiendo de una Planilla confeccionada, tomando como ejemplo la circulación de vehículos a través de una estación de peaje, se trata de aplicar sobre la misma un principio de ordenamiento a través de una herramienta de Excel, Tablas Dinámicas.

a) Introducir los datos

A continuación, introducir los siguientes datos:

Las preguntas a responder con la tabla dinámica son: a) Qué cantidad de tipo de vehículos pasaron por la estación de peaje? b) Qué cantidad de vehículos pasaron cada semana?

b) Crear la Tabla


1º. En la hoja donde introdujo la tabla, sitúate en una de las celdas que contienen los datos (Para este ejemplo A1), ve a la ficha Datos (menú principal) y hacer clic al botón Tabla Dinámica.

2º. En el cuadro de diálogo Crear Tabla Dinámica, se solicita seleccionar los datos que se desea analizar. En este caso, dejamos la opción preseleccionada Tabla#, que corresponde al rango


correcto. En la parte inferior del cuadro de diálogo, se debe elegir donde se desea colocar el informe de Tabla Dinámica, hacer clic al botón Hoja de cálculo existente, e indica la posición de destino de la tabla dinámica tecleando (o seleccionando con el ratón) la dirección de la celda superior izquierda a partir de la cual se colocará la tabla (Hágalo en la Hoja2, celda A5). Finalmente, clic a Aceptar.










En nuestro ejemplo, por tanto, colocaremos los campos del siguiente modo: % El campo Vehículo en la sección Rótulo de Fila % El campo Semana en la sección Rótulo de Columnas % El campo Cantidad (que contiene los valores que queremos sumar) en la sección

Sumatoria de Valores, aceptando la función SUMA que Excel propone por defecto. % La tabla Dinámica se presenta a continuación:

c) Modificar la Tabla

Aplicar Filtro a la Tabla

Si se quiere conocer los totales de vehículos para la 3ra. y 4ta. semana de los autos y ómnibus, hacer lo siguiente:

En primer lugar se despliega la lista en Rótulos de columnas y se desactiva la 1° y 2°


En segundo lugar se despliega la lista en Rótulos de fila y se desactivan las opciones Camión, Camioneta, Moto.

Resultado de la tabla al aplicarle los filtros correspondientes:

Para regresar a todas las opciones desplegar la lista y marcar Mostar Todo.

Con el objetivo que la tabla sea más explícita, se deberá cambiar el encabezado Rótulos de columnas por Semana y el encabezado Rótulos de fila por Vehículo. Finalmente la tabla se presenta así:


Cambiar el diseño de la tabla

El siguiente ejemplo es para colocar los vehículos en las columnas y las semanas en las filas.

Siguiendo el mismo procedimiento se puede cambiar el diseño de la tabla, por ejemplo a) En Vehículo y Semana, mostrar la lista y marcar la opción Seleccionar todo. b) En el cuadro de la derecha Lista de Campos, arrastre el campos Semana a Rótulos de

Filas y el campo Vehículos a Rótulo de Columnas c) Cambiar los rótulos nuevamente, donde dice Vehículo introducir Semana y viceversa.

Obteniéndose el siguiente resultado.

Aplicar Filtros a la Tabla Ahora se quiere obtener las cantidades de vehículos del tipo Autos y Camiones, correspondiente a la 1ra. y 3ra. semana. Siguiendo con el mismo procedimiento, en Vehículos se dejan activados Auto y Camión. En Semana se deja activada solamente la 1° y 3° Semana. De lo ejecutado surge el siguiente resultado.

Obtención de Subtablas

Si se quiere un detalle en hoja aparte sobre una celda, por ejemplo, la segunda semana de autos, se hace lo siguiente:

Hacer doble clic sobre una celda, en este caso por ejemplo la segunda de Auto, donde figura la cantidad 122350, excel automáticamente produce un detalle en hoja aparte según lo siguiente:


Ejercicio Número 13 TABLA DINAMICA

Aplicado a un Libro de Banco a) Preparar hoja Cargar e introducir datos y fórmulas

• Crear una tabla como se muestra en la siguiente imagen, llevarla hasta la fila 1000:

• Introducir los encabezados de la hoja y de la tabla así:

⇒ En la celda F2, alineado a la derecha y color de texto azul oscuro: Fecha de hoy:

⇒ En la celda G2 y alineada a la derecha, introducir la función =HOY() para presentar la fecha que tiene la computadora, que se supone es la de hoy. Cambiar color de relleno en azul oscuro y color de texto en blanco.

⇒ En la celda F4, alineado a la derecha y color de texto azul oscuro: Saldo Inicial:

⇒ En la celda G4, el valor de: 1000. Dar el formato de moneda ⇒ En la celda B6: Fecha ⇒ En la celda C6: Concepto ⇒ En la celda D6: Número de Cheque u Operación ⇒ En la celda E6: Créditos ⇒ En la celda F6: Débitos ⇒ En la celda G6: Saldo ⇒ En la celda H6: Mes

• Modificar el ancho de columnas para que se parezca al modelo presentado anteriormente.

A la fila 6 dar un formato de celda, para alinear el texto centrado verticalmente y ajustar el texto.

• Seleccionar todo el encabezado (b6:h6) y poner centrado, negrita, relleno azul y color de texto blanco y bordes blancos.

• A partir de la fila 7 hasta la fila 1000, colocar bordes.


• En la celda G4, introducir un comentario, para lo cual colocar el puntero sobre la celda y dar clic al botón derecho del mouse, luego clic a Insertar Comentario y luego introducir el siguiente texto: Si es deudor introducirlo con signo negativo.

• Configurar el papel a la orientación horizontal. • Seleccionar la columna Fecha y dar un formato de fecha corta. • Seleccionar la columna Número de Cheque� , dar formato de texto y Centrar. • Seleccionar las columnas Crédito, Débito y Saldo, y dar un formato de moneda. • En la celda G6 introducir la siguiente fórmula:

=SI(B7="","",G4+E7-F7)

• En la celda G7 introducir la siguiente fórmula: =SI(B8="","",G7+E8-F8), luego copiarla hasta la fila 1000.

• En la celda H7 introducir la siguiente fórmula: =SI(B7="","",CONCATENAR(MES(B7),"/",AÑO(B7)))

• Introducir los siguientes datos:

b) Preparar hoja Resumen y crear la tabla dinámica



• Crear la tabla dinámica que presente, para cada mes, en páginas individuales, el total de Créditos y Débitos por Fecha, Concepto y Número de Cheque. Para lograr esto hacer lo siguiente:

1º. En la hoja Cargar donde introdujo la tabla, sitúate en una de las celdas que contienen

los datos (Para este ejemplo B6), ve a la ficha Datos (menú principal) y hacer clic al botón Tabla Dinámica.

2º. En el cuadro de diálogo Crear Tabla Dinámica, se solicita seleccionar los datos que se desea analizar. En este caso, dejamos la opción preseleccionada Tabla#, que corresponde al rango correcto. En la parte inferior del cuadro de diálogo, se debe elegir donde se desea colocar el informe de Tabla Dinámica, hacer clic al botón Hoja de cálculo existente, e indica la posición de destino de la tabla dinámica tecleando (o seleccionando con el ratón) la dirección de la celda superior izquierda a partir de la cual se colocará la tabla (Hágalo en la Resumen, celda A5). Finalmente, clic a Aceptar.










En nuestro ejemplo, por tanto, colocaremos los campos del siguiente modo: % El campo Mes en la sección Filtro de informe % Los campos Fecha, Concepto, Número de Cheque en la sección Rótulo de Fila % El campo Crédito y Débito (que contiene los valores que queremos sumar) en la

sección Sumatoria de Valores. Excel propone por defecto la función de CUENTA, sin embargo esto lo vamos a cambiar por la función SUMA, haciendo clic sobre el campo, seleccionando Configuración de campo y luego Suma.

4º. Mejorar la presentación de la tabla dinámica

⇒ Cambiar el formato de Esquema a Tabular, haciendo clic a la ficha Diseño, luego clic

al botón Diseño de informe y finalmente clic a Mostrar en formato tabular. ⇒ Quitar el total a todos los campos:

" Clic a la ficha Diseño " Clic al botón Subtotales " No Mostrar Subtotales

⇒ Cambiar el formato a las columnas de los Valores " Con puntero en el encabezado del campo Suma de Crédito, clic al botón

derecho " Clic a Configuración de campo " En la casilla nombre personalizado introducir el texto: Créditos " Clic al botón formato de número " Clic a la categoría Número " Marcar el botón Usar separadores de miles " Aceptar " De forma similar hacer con la columna Suma de Débitos

⇒ Ocultar la fila 5 ⇒ Insertar una fila que separe a cada fecha

" Con puntero en la columna Fecha clic al botón derecho del mouse " Clic a Configuración de campo " Clic a la ficha Diseño e Impresión " Clic al botón Insertar línea en blanco después de cada etiqueta " Aceptar

Finalmente la tabla dinámica se presenta así:


CONSOLIDADO DE CREDITOS Y DEBITOS Período comprendido entre

Mes (Todas)

Fecha Concepto Número de cheque u operación

Créditos Débitos

05/06/2006 Cheque 00000021 100.00 00000022 50.00

Depósito (en blanco) 100.00

06/06/2006 Cheque 00000023 100.00

09/06/2006 Cheque 00000024 100.00

19/06/2006 Cheque 00000025 50.00 00000026 205.00

21/06/2006 Cheque 00000027 100.00

Depósito (en blanco) 1,000.00

22/06/2006 Depósito (en blanco) 2,500.00

23/06/2006 Cheque 00000028 500.00 Depósito (en blanco) 105.00

24/06/2006 Cheque 00000029 100.00

25/06/2006 Cheque 00000030 100.00

26/06/2006 Cheque 00000031 100.00

00000032 200.00

03/07/2006 Cheque 00000033 500.00 00000034 500.00

Total general 3,705.00 2,705.00


Ejercicio Número 14. DAR FORMATOS A CELDAS USANDO MACROS

a) Introducción

En esta unidad estudiaremos qué son las Macros, en qué nos pueden ayudar y cómo crear macros automáticamente. Esta unidad tratará de manera muy sencilla el tema de macros sin entrar en profundidad con el lenguaje de programación utilizado por MS Excel, el Visual Basic Application (VBA), ya que esto ocuparía un curso entero y se sale del objetivo del curso.

Cuando trabajamos con un libro personalizado, es decir, que nos hemos definido con una serie de características específicas como puedan ser el tipo de letra, el color de ciertas celdas, los formatos de los cálculos y características similares, perdemos mucho tiempo en formatear todo el libro si disponemos de muchas hojas.

Con las macros lo que se pretende es automatizar varias tareas y fusionarlas en una sola, añadiendo por ejemplo un botón en nuestro libro que al pulsar sobre él realice todas esas tareas.

b) Crear una macro automáticamente

La forma más fácil e intuitiva de crear macros es crearlas mediante el grabador de macros del que dispone Excel.

Este grabador de macros te permite grabar las acciones deseadas que posteriormente las traduce a instrucciones en VBA, las cuales podemos modificar posteriormente si tenemos conocimientos de programación.

Para grabar una macro debemos acceder a la pestaña Vista y despliega el submenú Macros y dentro de este submenú seleccionar la opción Grabar macro...

Además de esta opción en el menú podemos encontrar las siguientes opciones:

Ver Macros... - Donde accedemos a un listado de las macros creadas en ese libro.

Usar referencias relativas - Con esta opción utilizaremos referencias relativas para que las macros se graben con acciones relativas a la celda inicial seleccionada.

Al seleccionar la opción Grabar macro..., lo primero que vemos es el cuadro de diálogo Grabar macro donde podemos dar un nombre a la macro (no está permitido insertar espacios en blanco en el nombre de la macro).


Podemos asignarle un Método abreviado: mediante la combinación de las tecla CTRL + "una tecla del teclado". El problema está en encontrar una combinación que no utilice ya Excel.

En Guardar macro en: podemos seleccionar guardar la macro en el libro activo, en el libro de macros personal o en otro libro.

En Descripción: podemos describir cuál es el cometido de la macro o cualquier otro dato que creamos conveniente.

Para comenzar la grabación de la macro pulsamos el botón Aceptar y a continuación, si nos

fijamos en la barra de estado, encontraremos este botón en la barra de estado donde tenemos la opción de detener la grabación.

A partir de entonces debemos realizar las acciones que queramos grabar, es conveniente no seleccionar ninguna celda a partir de la grabación, ya que si seleccionamos alguna celda posteriormente, cuando ejecutemos la macro, la selección nos puede ocasionar problemas de celdas fuera de rango.

Una vez concluidas las acciones que queremos grabar, presionamos sobre el botón Detener de la barra de estado, o accediendo al menú de Macros y haciendo clic en c) Ejecutar una macro

Una vez creada una macro, la podremos ejecutar las veces que queramos.

Antes de dar la orden de ejecución de la macro, dependiendo del tipo de macro que sea, será necesario seleccionar o no las celdas que queramos queden afectadas por las acciones de la macro.

Por ejemplo si hemos creado una macro que automáticamente da formato a las celdas seleccionadas, tendremos que seleccionar las celdas previamente antes de ejecutar la macro.


Para ejecutar la macro debemos acceder al menú Ver Macros..., que se encuentra en el menú Macros de la pestaña Vista, y nos aparece el cuadro de diálogo Macro como el que vemos en la imagen donde tenemos una lista con las macros creadas.

Debemos seleccionar la macro deseada y pulsar sobre el botón Ejecutar. Se cerrará el cuadro y se ejecutará la macro.

En cuanto al resto de botones:

Cancelar - Cierra el cuadro de diálogo sin realizar ninguna acción.

Paso a paso - Ejecuta la macro instrucción por instrucción abriendo el editor de programación de Visual Basic.

Modificar - Abre el editor de programación de Visual Basic para modificar el código de la macro. Estos dos últimos botones son para los que sapan programar.

Eliminar - Borra la macro.

Opciones - Abre otro cuadro de diálogo donde podemos modificar la tecla de método abreviado (combinación de teclas que provoca la ejecución de la macro sin necesidad de utilizar el menú) y la descripción de la macro.

d) Ejercicio

& Crear la Macro

Aprender como crear una macro de forma automática utilizando el grabador de macros. Crearemos una macro que automatice el formato de la cabecera de nuestra hoja de trabajo.


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

2. Al entrar estaremos situados en la celda A1.

3. Ve a la pestaña Vista.

4. Abre el submenú Macro.

5. Y elije la opción Grabar nueva macro...

Aparece el cuadro de diálogo Grabar macro.

6. Escribe el nombre de la macro, EjemploMacro1. Y pulsa Aceptar.

7. Ahora estamos grabando, vamos a realizar las acciones necesarias para dar formato a nuestra hoja de trabajo.

8. Presiona sobre el botón Negrita de la sección Fuente, en la pestaña Inicio.

9. Presiona sobre el botón Cursiva de la sección Fuente.

10. Escoge el tipo de fuente Cambria.

11. Escoge el tamaño de la fuente en 14 puntos.

Ya hemos concluido con el formato de una cabecera de datos, por tanto concluimos la grabación de la macro.

12. Presiona sobre el botón detener de la barra de herramientas, o accede al menú Macro y selecciona Detener grabación.

& Ahora vamos a comprobar que funciona correctamente la macro creada.

1. Escribe en la celda D1 Cabecera.

2. Escribe en la celda E1 de.

3. Escribe en la celda F1 prueba.

4. Selecciona las celdas anteriores D1, E1 y F1.

5. Ve a la pestaña Vista.

6. Abre el submenú Macro.

7. Elige Ver Macros.

8. Selecciona la macro creada EjemploMacro1 y pulsa sobre Ejecutar.

Observa como las celdas seleccionadas adoptan el formato automáticamente.

excel avanzado 2007c

Documents