INFONET SRL

MICROSOFT EXCEL 2000 INICIAR EXCEL 2000.- Dar Clic en el botón Inicio, Programas y Microsoft Excel Descripción de las partes de la hoja de cálculo Barra de Fórmulas

Barra de Menús

Hoja de Cálculo Barra De Botones

Desplazamiento por filas Desplazamiento entre hojas

Desplazamiento por columnas

Entrada de datos y fórmulas. • Entrada y edición de Datos. Entrar Datos • Haga un clic con el ratón sobre la casilla donde quiera entrar los datos. • Sitúese con las teclas de desplazamiento sobre la casilla y escriba los datos. • Acepte el valor con la tecla ENTER

Borrar datos • Sitúese sobre la casilla y pulse la tecla Supr • Sitúese sobre la casilla y active las opciones del menú Edición/Borrar/Todo, Edición/Borrar/Contenido.

Melvin Quiñones Aguilar

1

INFONET SRL

Seleccionar un rango. Se define como rango, un grupo de casillas consecutivas de la hoja. 1. Sitúe el cursor sobre la casilla inicial. 2. Una de las dos siguientes opciones: • •

Con el botón principal del ratón pulsado, arrastre hasta la casilla final. Con la tecla Mayúsculas pulsada, desplace el cursor hasta la casilla final con las teclas del cursor.

Modificar datos. 1. Sitúe el cursor sobre la casilla que quiera modificar. 2. Una de las tres siguientes opciones: • • •

Pulse la tecla F2. Haga clic sobre la barra de fórmulas. También puede hacer un doble clic directamente sobre la casilla que quiera modificar.

Copie los datos siguientes:

• Entrada y Edición de Fórmulas. A) Con el teclado. 1. Sitúe el cursor sobre la casilla donde entrará la fórmula. 2. Teclee la fórmula. Recuerde que una fórmula lleva siempre el signo = al principio. 3. Pulse ENTER

Ejemplo . Cálculo de las ventas brutas en Enero. Multiplicaremos las Unidades por P.v.p. 1. Seleccione la casilla B4. 2. Teclee la fórmula. =B2*B3 3. Pulse ENTER.

þ Calcule un descuento del 10% sobre las ventas brutas(B4) en la casilla B5. (La fórmula será =B4*10%)

Melvin Quiñones Aguilar

2

INFONET SRL

B) Con las teclas de desplazamiento. 1. Seleccione la casilla donde se entrará la fórmula. 2. Pulse el signo =. 3. Desplace el cursor hacia la casilla que quiera operar. 4. Pulse el signo ( +, -, *, /). 5. Repita los pasos 3 y 4 para cada operador de la fórmula. 6. Para finalizar la entrada de la fórmula, pulse ENTER.

Ejemplo . Cálculo de las ventas netas en Enero. Restar de las Ventas Brutas el Descuento. 1. Seleccione la casilla B6. 2. Pulse el signo =. 3. Desplace hacia la casilla B4. 4. Ponga el signo -. 5. Desplace hacia la casilla B5. 6. Pulse ENTER.

þ Calcule la comisión del 20% sobre las ventas netas(B6) en la casilla B7. (=B6*20%)

C) Con el ratón. 1. Seleccione la casilla donde va a entrar la fórmula. 2. Pulse el signo =. 3. Haga un clic con el botón principal sobre la casilla que quiera operar. 4. Ponga el signo para operar( +, -, *, /). 5. Repita los pasos 3 y 4 para cada operando de la fórmula. 6. Para finalizar la entrada de la fórmula, pulse ENTER.

Ejemplo . Cálculo del neto en Enero. Restar de las Ventas Netas las Comisiones. 1. Seleccione la casilla B8. 2. Pulse el signo =. 3. Haga un clic sobre la casilla B6. 4. Pulse el signo -. 5. Haga un clic sobre la casilla B7 6. Pulse ENTER.

Melvin Quiñones Aguilar

3

INFONET SRL

Para ver las casillas utilizadas en cualquier fórmula, siga estos pasos. 1.

Sitúese sobre una casilla con fórmula, (B4, por ejemplo).

2.

Haga doble clic o bién pulse la tecla F2. Fíjese como la referéncia de la casilla utilizada en esta fórmula, aparece del mismo color que la casilla.

• Copiar. EXCEL permite copiar datos de una casilla o rango de casillas hacia otra casilla o rango de casillas. Cuando se copia una fórmula hacia otra casilla, automáticamente se cambian las referencias. Por ejemplo, si se copia la fórmula de la casilla B4 (=B2*B3) a la casilla C4, EXCEL camb iará las referencias y pondrá =C2*C3.

Métodos para copiar. A) Con las opciones de la barra de menús. 1. Marque la casilla o rango de casillas a copiar. 2. Active la opción de la barra de menús Edición/ Copiar. 3. Situe el cursor en la casilla de destino. 4. Active la opción de la barra de menús Edición/ Pegar. Las acciones Edición/ Copiar y Edición/ Pegar, se pueden ejecutar, haciendo un clic sobre los botones

de la barra estándard.

Ejemplo . Copie la fórmula de la casilla B4, Ventas Brutas de Enero, hacia a C4, D4 y E4. Calcule las Ventas Brutas de Febrero, Marzo y Abril. 1. Seleccione la casilla B4. 2. Active la opción de la barra de menús Edición/ Copiar, o haga un clic sobre el botón 3. Seleccione el rango C4:E4 . Recuerde Clic en C4 y arrastrar hasta E4. 4. Active la opción de la barra de menús Edición/Pegar, o haga un clic sobre el botón . Verá que la casilla inicial (B4) está rodeada por unas líneas discontinuas intermitentes, para quitar esta selección pulse ENTER o la tecla ESC.

B) Con las opciones del menú de contexto. El menú de contexto, aparece cuando se hace clic con el botón secundario del ratón. 1. Marque la casilla o rango de casillas a copiar. 2. Haga un clic con el botón secundario y active la opción Copiar. 3. Situe el cursor en la casilla de destino. 4. Haga un clic con el botón secundario y active la opción Pegar.

Melvin Quiñones Aguilar

4

INFONET SRL

Ejemplo . Copie la fórmula de la casilla B5, Descuentos en Enero, hacia a C5, D5 y E5. Calcule los Descuentos de Febrero, Marzo y Abril. 1. Seleccione la casilla C5. 2. Haga un clic con el botón secundario y active opción Copiar. 3. Marque el rango C5:E5 . Recuerde clic en C5 y arrastrar hasta E5. 4. Haga un clic con el botón secundario, dentro de la selección, y active opción Pegar.

C) Copias utilizando el recuadro de relleno. Este método sólo se puede utilizar para copiar datos en casillas adyacentes a la original. El recuadro llenar está situado en la parte inferior derecha de la casilla activa.

Recuadro Llenar. 1. Situe el cursor sobre la casilla o rango de casillas a copiar. 2. Situe la cruz del cursor sobre el recuadro llenar, la cruz pasará a color negro. 3. Pulse el botón principal y arrastre hacia la casilla o rango de casillas destino.

Ejemplo . Copie la fórmula de la casilla B6, Ventas Netas hacia las casillas C6, D6 y E6. Calcule las ventas netas de Febrero, Marzo y Abril. 1. Seleccione la casilla B6. 2. Situe la cruz sobre el recuadro llenar. 3. Arrastre, sin dejar el botón principal hasta la casilla E6.

Esta acción, también la puede hacer con el teclado. 1. Seleccione la casilla a copiar. 2. Seleccione las casillas adyacentes. Ha de quedar marcada la casilla con los datos que se copian y las casillas de destino. 3. Active la opción Edición/ Rellenar Abajo (o CTRL+J ), Edición/Rellenar Derecha (o CTRL+D). þ Calcule las comisiones y el neto del resto de meses. þ Calcule las columnas Total y Media (este media la calcularemos en la columna G) utilitzando las funciones =SUMA y = PROMEDIO respectivamente. Ponga título a la columna Media. Para calcular el Total: Seleccione la casilla F2, escriba =SUMA(B2:E2) y pulse ENTER.

Melvin Quiñones Aguilar

5

INFONET SRL

Funciones. =SUMA(casilla inicial :casilla final) Suma los valores entre las casillas inicial y final. El botón sumará las casillas con valor numérico de la izquierda o de encima la casilla donde está el cursor. =PROMEDIO(casilla inicial :casilla final) Calcula la media de los valores comprendidos entre la casilla inicial y la final.

• Mover Excel, permite mover datos de una casilla o rango de casillas hacia otra casilla o rango de casillas.

A) Con las opciones de la barra de menús. 1. Seleccione la casilla o rango de casillas a mover. 2. Active la opción de la barra de menús Edición/Cortar. 3. Situe el cursor en la casilla de destino. 4. Active la opción de la barra de menús Edición/ Pegar. Las acciones Edición/Cortar y Edición/Pegar, se pueden ejecutar, haciendo un clic sobre los botones

de la barra estándard.

Ejemplo . Mueva los rangos Total y Media, dos columnas hacia la derecha. 1. Seleccione los rangos Total y Media. (Ha de incluir el encabezado y los números) 2. Active la opción de la barra de menús Edición /Cortar ,o haga un clic sobre el botón 3. Situese en la casilla destino (H1). 4. Active la opción de la barra de menús Edición/Pegar ,o haga un clic sobre el botón

B) Con las opciones del menú de contexto. 1. Seleccione la casilla o rango de casillas a mover. 2. Haga un clic con el botón secundario y active la opción Cortar. 3. Situe el cursor en la casilla de destino. 4. Haga un clic con el botón secundario y active la opción Pegar.

Melvin Quiñones Aguilar

6

INFONET SRL

C) Mover arrastrando el contenido de la casilla o rango.

1.Seleccione la casilla o rango de casillas a mover. 2.Situe el cursor sobre el marco de las casillas seleccionadas y cuando aparezca un cursor en forma de flecha, arrastre hacia al rango destino.

þ Ponga en las casillas F1 y G1 Mayo y Junio respectivamente. þ En las casillas F2,G2 escriba 320,260. þ En las casillas F3,G3 escriba 60,60. þ Calcule las columnas F y G, utilitzando culaquier método de copiar. þ Modifique las funciones Suma y Promedio, para que se incluyan las columnas Mayo y Junio.

A

1 2 3 4 5 6 7 8

B C D E F G H ENERO FEBRERO MARZO ABRIL MAYO JUNIO TOTAL UNIDADES 300 250 325 375 320 260 1830 PVP 60 60 60 60 60 60 360 VENTAS BRUTAS 18000 15000 19500 22500 19200 15600 109800 DESCUENTOS 1800 1500 1950 2250 1920 1560 10980 VENTAS NETAS 16200 13500 17550 20250 17280 14040 98820 COMISIONES 3240 2700 3510 4050 3456 2808 19764 NETO 12960 10800 14040 16200 13824 11232 79056

Melvin Quiñones Aguilar

7

INFONET SRL

Dar formato a una hoja de Cálculo. Todas las acciones que se verán en este apartado sirven para cambiar el aspecto de presentación de los datos de una hoja.

• Poner Negrita, Cursiva y Subrayado. 1.Seleccione la casilla o rango de casillas a modificar de aspecto. 2.Active la opción del menú Formato/Celdas/Fuente, en esta ficha, seleccione Negrita, Cursiva o un tipo de subrayado. 3.Haga clic sobre Aceptar. También puede utilizar los botones

de la barra estándard.

Ejemplo . Ponga negrita, subrayado y cursiva las casillas de los tres primeros meses 1. Seleccione el rango B1:D1. 2. Active opción Formato/Celdas/ Fuente/Estilo de Fuentes, escoja Negrita, Cursiva 3. En lista Subrayado, seleccione el tipo de subrayado. 4. Pulse Aceptar. Alternativamente, una vez seleccionado el rango, se pueden utilizar los botones .

• Alineación. Ejemplo . Alinear las casillas de los meses. 1.Seleccione las casillas B1:G1 . 2.Active opción del menú Formato /celdas/ Alineación. 3.Seleccione el tipo de alineación horizontal (Izquierda, centrada, derecha). Marque alineación derecha. 4. Pulse sobre Aceptar. También puede utilizar los botones, de alineación de la barra de formatos.

• Alineación de títulos. Esta opción permite inclinar el contenido de una casilla. Siga los pasos siguientes. 1. 2. 3. 4.

Situe el cursor en la casilla. Active opción Formato/Celdas/Alineación. En apartado Alineación del texto, se puede escoger el tipo de alineación. En apartado Orientación, se escogen los grados de inclinació y hacia a que dirección debe ir dicha inclinación.

Melvin Quiñones Aguilar

8

INFONET SRL

• Cambiar tipo de letra y tamaño. A) Cambiar el tamaño. Ejemplo . Modifique el tamaño de letra de la columna A. 1. Seleccione el rango A2:A8. 2. Active Formato/Celdas/Fuente. 3. Escoja el tamaño de la letra en apartado Tamaño. (Déjelo a tamaño 12) 4.Pulse sobre Aceptar. También se puede utilizar la lista desplegable de la barra de formato. .

B) Cambiar tipo de letra (Fuente). 1. Seleccione las casillas a las que canviará el tipo de lletra. 2. Active opción del menú Formato/Celdas/Fuentes. 3. En apartado Fuente, escoja tipo de lletra. 4. Pulse sobre Aceptar. También se puede utilizar la lista desplegable de la barra de formato . þ Ponga el tipo de letra de la primera fila a Font Times New Roman.

• Colores A) Color de fondo. 1.Seleccione la casilla o rango de casillas donde quiera aplicar color. 2.Active Formato/Celdas, y seleccione la ficha Trama. 3.En apartado Color, escoja el color. 4.En apartado Trama, escoja el tipo de trama que quiera y el color para la trama. Pulse sobre Aceptar. También se puede utilizar el botón de la barra de formato colores.

para seleccionar los

þ Ponga el fondo de la primera fila y el de la primera columna en color gris.

B) Color de texto. 1. Seleccione las casillas. 2. Active Formato/Celdas, seleccione la ficha Fuente. 3. En apartado Color, escoja un color para el texto. 4. Pulse sobre Aceptar. También puede utilizar el botón de la barra de formato.

Melvin Quiñones Aguilar

9

INFONET SRL

þ Ponga las letras de la primera fila y la primera columna en color rojo. þ Coloree el fondo del rango de los valores en azul. þ Ponga los números del rango anterior en blanco (Color del texto).

• Formatos Numéricos. Para poner formato numérico, ejecute los pasos siguientes. 1. Seleccione las casillas a formatear. 2. Active opción Formato/celdas/Número. 3. En apartado Categoría seleccione la categoria. 4. Pulse sobre Aceptar. También puede utilizar los botones:

Estos cinco botones representan respectivamente. Modelo Moneda (En pesetas) Euros. Modelo Porcentual. Modelo Millares. Aumentar decimales. Disminuir decimales. þ Aplique formato monetario a los valores de las columnas Total y Media.

• Bordes. Con estas opciones podrá emmarcar, separar por líneas o por cuadros, rangos de una hoja de cálculo. 1. Seleccione el rango al cual quiera aplicar una línea. 2. Active opción Formato/Celdas seleccione ficha Bordes. 3. En apartado Bordes, seleccione la posición para la línea. También puede hacerlo seleccionando los botones que rodean el modelo de cuadro. 4. En apartado Estilo, escoja el estilo de línea para el borde. 5. Pulse sobre Aceptar.

También puede utilizar la paleta de bordes situada en la barra de formato.

þ Seleccione toda la tabla y póngale contorno. þ Seleccione la primera fila y aplique una doble línea inferior. þ Seleccione tota la tabla excepto la última columna, ponga líneas por la derecha y líneas centrales verticales.

Melvin Quiñones Aguilar 10

INFONET SRL

• Cambiar el tamaño de las Columnas. 1. Situe el cursor en la columna que quiera cambiar de tamaño. 2. Active opción Formato/Columna, se desplegará un submenú con las siguientes opciones:

Ancho: Modifica el ancho de la columna. Autoajustar a la Selección: Ajusta el ancho de la columna al texto más extenso del rango seleccionado. Para utilizar esta opción, debe seleccionar la columna primero. Ocultar: Oculta la columna seleccionada. Mostrar: Muestra la columna seleccionada, si esta está oculta. Ancho Estándar: Aplica el ancho que tienen las columnas por defecto. También se puede cambiar el ancho de una columna con el ratón: 1. Situe el puntero de ratón sobre la línea de separación de las columnas.

2.El cursor se convertirà en una cruz con doble flecha, pulse el botón principal del ratón y arrastre. Puede hacer doble clic sobre esta línea de separación para que la columna se haga lo suficientemente ancha para mostrar los datos de la casilla donde el texto es más extenso.

þ Amplie la primera columna de manera que se vean todos las datos.

• Cambiar tamaño de Filas. 1. Seleccione la fila o filas a las cuales quiera cambiar el tamaño. 2. Active opción Formato/Fila. 3. Siga los mismos pasos que en apartado ampliar columnas.

Si lo quiere hacer con el ratón. 1. Situe el puntero de ratón sobre la Línea de separación de filas.

2.El cursor es convertirà en una cruz con doble flecha, pulse el botón izquierdo del ratón y arrastre.

Melvin Quiñones Aguilar 11

INFONET SRL

• Eliminar Formatos. 1.Seleccione el rango de los formatos que quiera eliminar. 2.Active opción Edición/Borrar, se desplegará un submenú: Todo: borra fórmulas, formatos y datos. Formatos: borra sólo los formatos. Contenido: borra datos y fórmulas. Comentarios: borra las notas. Si quiere insertar una nota, active opción Insertar/Comentario. 3. Elija la opción Formatos.

• Utilizar formatos predefinidos. Excel tiene formatos predefinidos que se pueden aplicar directamente sobre un rango, para darle formato de manera rápida. Para aplicar uno de estos formatos. 1. Seleccione el rango sobre cual quiera aplicar formato. 2. Active opción del menú Formato/Autoformato. 3. Escoja cualquier formato. • •

El botón opciones, le permitirá escoger el tipo de formato que quiera aplicar sobre el rango. Por ejemplo, aplicar sólo fuente y el color de fondo de un formato determinado. Es posible definir un formato propio con las opciones Formato/Estilo.

• Crear un Modelo de Formato. Para crear un formato nuevo y poderlo utilizar como uno de predefinido, ejecute lo pasos siguientes:

Ejemplo . 1. Recupere el primer ejercicio si no lo tiene en pantalla. 2. Seleccione toda la tabla. 3. Active opción Formato/Estilo. 4. En apartado Nombre, ponga el nombre Personal. Pulse sobre botón Agregar. 5. Pulse sobre botón Modificar, 5.1. En ficha Número, apartado Categoría: Moneda. 5.2 En ficha Alineación, apartado Horizontal: Centrar, en apartado Vertical: Inferior. 5.3 En Fuentes: Arial, Estilo Fuente: Normal,Tamaño: 10, Color: Azul. 5.4. Pulse Aceptar. 6. Verá que vuelve a estar en el primer cuadro de diálogo, donde aparecen las selecciones hechas. Pulse Aceptar. * Para seleccionar el modelo de formato acabado de crear, active opciones Formato/Estilo, en apartado Nombre pulse el botón de la lista desplegable y seleccione el modelo que ha creado. Recuerde tener seleccionada la tabla. þ Aplique sobre el primer ejercicio el formato acabado de crear.

Melvin Quiñones Aguilar 12

INFONET SRL

Observe que no se ve ningún número. Esto se debe a que el color de fondo és el mismo que el color del texto. Cambie el color de fondo de los número a color turquesa. •

Insertar comentarios. 1.

Sitúese sobre la casilla donde quiera un comentario.

2.

Active opción Insertar/Comentario. Aparece un cuadro de texto donde podrá escribir.

Observación: El texto del comentario puede mostrarse siempre o sólo cuando el cursor se sitúa sobre la casilla. Para configurar la presentación de los comentarios: •

Ficha Ver del menú Herramientas/Opciones.

En apartado Comentarios. Ninguno: no muestra ni el punto rojo, ni el texto del comentario. Sólo indicador del comentario : Muestra un punto rojo en las casillas donde hay comentarios. Si sitúa el puntero del ratón sobre la casilla se despliega la nota. Indicador y comentario : Muestra el indicador rojo y la nota.

Visualización de un hoja. Las opciones Ver de la barra de menús, permiten diferentes maneras de visualizar un hoja y sus partes. •

Ver/Barra de Fórmulas: Muestra o oculta la barra de fórmulas.

•

Ver/Barra de Estado: Muestra o oculta la barra de estado (barra informativa en la parte inferior de la pantalla).

•

Ver/Barra de Herramientas: Muestra las diferents barras que se pueden tener activadas. También las puede activar o desactivar, situando el puntero de ratón sobre cualquier barra y hacer clic con el botón secundario del ratón.

•

Ver/Pantalla Completa: Muestra la hoja de cálculo ocupando tosa la pantalla. Para volver a pantalla normal se puede hacer desde Ver/Pantalla Completa, o hacer un clic sobre este botón.

Melvin Quiñones Aguilar 13

INFONET SRL •

Ver/Zoom..: Permite escoger una ampliació para la visualización de la hoja.

Ajustar a la selección: Muestra aproximadamente el rango seleccionado. Personalizada: Muestra la hoja con el zoom que se le indica. •

Ver/Vistas personalizadas. Con esta opción puede poner nomb re a diferents rangos para su visualización, de hecho no se pone nombre a los rangos sino a diferentes partes de la hoja que esta viendo en el momento de aplicar la orden. Se utiliza en hojas extensas para ir rápidamente de una vista a otra.

•

Para a crear una vista. Es posible que esta opción no aparezca. Vistas es un componente que si no se ha incluido cuando se ha instalado Excel, no aparecerá en las opciones de Ver. 1.Ver/ Vistas personalizadas/Agregar. Aparece un cuadro de diálogo donde tendrá que poner nombre a la vista (será el rango que se vé actualmente). 2. Pulse sobre Aceptar. • Cada vez que quiera ver un vista determinada, tendrá que activar opción Ver/Vistas personalizadas/Mostrar.

Ejemplo . 1. Situe el cursor sobre la casilla A1. 2. Active opción.Ver/ Vistas personalizadas /Agregar. Ponga Vista1 en el cuadro de edición Nombre. 3. Situe el cursor sobre la casilla D1 de manera que se vean las columnas de la D a la J. Ponga el nombre Vista2. 4. Active opción Ver/Vistas personalizadas, escoja una de las vistas y pulse sobre Mostrar. En Vista1, verá las columnas de la A a la G, en Vista2, verá las columnas de la D a la J.

Melvin Quiñones Aguilar 14

INFONET SRL

Operaciones con archivos. En esta sección, aprenderá las operaciones básicas para trabajar con archivos. Un archivo, es el lugar donde un sistema informático guarda la información., Un libro de trabajo, por ejemplo, se guarda en un archivo.

• Guardar un archivo. Utilice la opción Archivo/Guardar, o el botón de la barra estàndard que guarda el libro de trabajo, se le pedirá un nombre para el archivo.

. Si es la primera vez

Si el archivo tiene un nombre (ya se guardó anteriormente), Excel lo guardará con este nombre. Si

quiere guardar el archivo con otro nombre, utilice la opción de menú Archivo/Guardar como. Guardar en:

Permite escoger una carpeta donde guardar el archivo .

Nombre de archivo: Nombre que se le da al archivo. Guardar como:

Con que tipo de archivo se quieri guardar. Po ejemplo guardarlo en formato de otra versión de Excel. abierto en modo solo lectura no se puede modificar, de hecho sí se puede, lo que no permite es guardar los cambios.

þ Guarde el archivo con el nombre Exer.

• Cerrar un Libro de trabajo. Esta opción retira el libro de trabajo activo. Es la opción, Archivo/ Cerrar. (Cierre el libro de trabajo)

• Recuperar un libro de trabajo. Para recuperar un libro de trabajo, active opción del menú Archivo/Abrir, o pulse sobre el botón Aparece un cuadro de diálogo similar al de las opciones de guardar. Para ver la utilidad del botón Buscar, mire la siguiente sección Buscar un libro de trabajo. þ Recupere el libro de trabajo Exer.

Melvin Quiñones Aguilar 15

INFONET SRL

• Buscar un libro de trabajo. Para buscar un libro de trabajo o un archivo, active opción Archivo/Abrir. Aparece el siguiente cuadro de diálogo.

•

Nombre de archivo:

Ponga el nombre del archivo. Puede utilizar los caracteres comodín * y?

Tipo de archivo:

Seleccione el tipo de archivo.

Cerrar la aplicación Excel.

Solo tiene que activar la opción de la barra de menús Archivo/Salir. En caso que tenga un libro de trabajo en el cual se hayan hecho modificaciones, Excel preguntará si lo quiere guardar antes de salir.

Melvin Quiñones Aguilar 16

INFONET SRL • Ejercicio Nº 01. Ingrese los siguientes datos:

1 2 3 4 5 6 7 8

A B C D E F Producto Precio Compra Incremento Precio Venta Cantidad Total Ventas Pantalón 3000 15 Camisa 2500 15 Vestido 4500 10 Falda 2300 20 Camiseta 2000 25 Jersey 3200 25 Short 2500 15

Calcule las casillas en blanco.

Cálculos. Incremento Precio Venta Total Ventas

10% Del precio Compra. Precio Compra + Incremento Precio Venta * Cantidad.

þ Calcule la primera fila (Pantalón) y utilice cualquiera de las opciones de copiar para el resto de las celdas. þ Inserte la columna Total compras entre las de Cantidad y Total ventas; calcúlela, Total Compras = Precio Compra * Cantidad. þ Ponga la columna Total Pesetas (Total Ventas - Total Compras), después de la columna de Total Ventas. þ Utilice la función SUMA para calcular los Totales de les columnas Total Compras, Total Ventas y Total Soles. þ Formato de la tabla. ú

Primera fila y columna en negrita, color de fondo azul y color del text o blanco.

ú Casillas de los números, excepto las sumas, fondo azul cielo. ú Casillas de Sumas, fondo gris 25% ú Bordes, Contorno de la tabla, excepto los totales, y otro contorno para los totales. En la Primera fila borde inferior ponga doble línea. Línea derecha a todas las columnas, excepto la última. ú Columnas de Totales, formato numérico, número con separador. ú Fila de Sumas, formato numérico monetario. •

Si después de poner el formato, en las casillas aparecen los símbolos siguientes #####, significa que la columna no tiene suficiente ancho para el número, cambie la anchura de manera que se vea. Siempre que aparezcan estos símbolos o el número en notación científica, significa que la columna no tiene suficiente anchura.

Melvin Quiñones Aguilar 17

INFONET SRL

Solución: Precio Precio Total Compr Increment Venta Cantidad Compras a o

Producto

Pantalón Camisa Vestido Falda Camiseta Jersey Short

3000 2500 4500 2300 2000 3200 2500

300 250 450 230 200 320 250

3300 2750 4950 2530 2200 3520 2750

15 15 10 20 25 25 15 Sumas

Total Ventas

Total Soles

45.000 37.500 45.000 46.000 50.000 80.000 37.500

49.500 41.250 49.500 50.600 55.000 88.000 41.250

4.500 3.750 4.500 4.600 5.000 8.000 3.750

341.000

375.100

34.100

• Ejercicio Nº 02. Ingrese los siguientes datos: A 1 2 3 4 5 6 7

B C D E F G Juan Ana José Antonio María Total 300000 350000 250000 300000 400000

Ventas % Comisiones Total Comisiones Fijo 125000 125000 Anticipos Ganancias

90000 100000 105000

Calcule las casillas en blanco.

Cálculos % Comisiones Total Comisiones Anticipos Ganancias Total

15% Ventas * 15% 3% del Fijo Fijo + Total Comisiones - Anticipos Suma de cada Fila

þ Calcule las casillas vacías. þ Formato de la tabla. ú ú ú ú ú ú ú ú

Títulos de la primera fila, alineados por la derecha y en negrita. Fuente de la primera columna, Times New Roman tamaño 12. Borde inferior a la primera y última fila. Borde derecho en todas las columnas excepto en la última. Formato numérico porcentual en la fila de Comisiones. Formato numérico con separador de miles en fila de Ganancias. Color de fondo para toda la tabla, gris. En la casilla G3, cambiar la función suma por promedio.

Melvin Quiñones Aguilar 18

INFONET SRL Solución:

Ventas % Comisiones Total Comisiones Fijo Anticipos Ganancias

Juan

Ana

José

Antonio

María

Total

300000

350000

250000

300000

15%

15%

15%

15%

15%

15%

45000

52500

37500

45000

60000

240000

125000

125000

90000

100000

105000

545000

3750

3750

2700

3000

3150

16350

166.250

173.750

124.800

142.000

161.850

768.650

400000 1600000

• Ejercicio Nº 03. Ingrese el siguiente texto: A 1 2 3 4 5 6 7 8 9

Precio paquete Precio Unidad Nº Cigarrillos Consumidos Total Nicotina Alquitrán Total Nicotina Total Alquitrán

B C D E F Marlboro Ducados Fortuna Winston Camel 330 165 230 330 300 12

18

20

15

7

1 14

1.1 15

1 14

1 14

1 14

Cálculos. Precio Unidad Total Total Nicotina Total Alquitrán

Precio Paquete / 20 Precio Unidad * Nº Cigarrillos Consumidos Nº Cigarrillos consumidos * Nicotina Nº Cigarrillos Consumidos * Alquitrán

þ Calcule las casillas vacías. þ Formato de la tabla. ú Aplique formato clásico 2. Recuerde que los formatos predefinidos se activan con la opción Formato/ Autoformato y que previamente tiene que seleccionar el rango al cual se lo quiere aplicar.

Solución:

Precio paquete Precio Unidad Nº Cigarrillos Consumidos Total Nicotina Alquitrán Total Nicotina Total Alquitrán

Marlboro Ducados Fortuna 330 165 230 16,5 8,25 11,5 12 18 20 198 1 14 12 168

148,5 1,1 15 19,8 270

230 1 14 20 280

Winston 330 16,5 15

Camel 300 15 7

247,5 1 14 15 210

105 1 14 7 98

Melvin Quiñones Aguilar 19

INFONET SRL

Melvin Quiñones Aguilar 20

INFONET SRL • Referencias Absolutas Una referencia absoluta, significa indicar a EXCEL que cuando tenga que hacer una copia de una fórmula compuesta por referencias, la referencia de una o más casillas no ha de cambiar, siempre será la misma. Se indica referencia absoluta poniendo delante de la fila i/o columna el símbolo $.

Ejemplo. En el cuadro mostrado en la parte inferior: Total comisiones (B3)= Total Ventas (B2) * %Comercial ($B8). Escriba el $ delante de B8, porqué cuando se copie esta fórmula en las casillas adyacentes, no queremos que se incremente la referencia a C8, D8, etc. Total Impuestos (B4)= Total Ventas (B2) * % Impuestos ($B9) Gastos Fijos (B5)= Gastos Fijos ($B10) Total Neto= Total Ventas-(Total Comisiones + Total Impuestos + Gastos Fijos). Total= Suma de todos los meses.

Solución : Total Ventas Total Comisiones Total Impuestos Gastos Fijos Total Neto

% Comercial % Impuestos Gastos Fijos

Enero Febrero 340000 380000 17000 19000 51000 57000 45000 45000 227000 259000

Marzo 420000 21000 63000 45000 291000

Abril 470000 23500 70500 45000 331000

Mayo Total 500000 2110000 25000 105500 75000 316500 45000 225000 355000 1463000

5% 15% 45000

Melvin Quiñones Aguilar 21

INFONET SRL

• Ejercicio. En el cuadro: Tabla de Resultados,

Cálculos. VolumenVentas Ingresos Ventas Costo Variable Costo Fijo Beneficios

Volumen Ventas mes anterior * Incremento de Ventas + Volumen Ventas mes Anterior. Volumen Ventas * Precio 57% de Ingresos Ventas. Utilizando la casilla Coste incr. Ventas Casilla de Costo Fijo. Ingresos Ventas - (Costo Variable + Costo Fijo).

En este Ejercicio, como las copias se hacen por filas, tendrá que poner el $ delante de la fila. Por ejemplo, para calcular el volumen de ventas del Febrero y copiarla al resto de meses, la fórmula será, =B13 + B13 * B$2 þ Calcule las casillas vacías. þ Crear una vista que muestre el cuadro Tabla de previsiones, y otra que muestre toda la tabla de resultados. þ Formatos. ú ú ú ú

Elimine todos los decimales de la tabla de resultados. Aplique Autoformato Clásico 3 a la tabla de previsiones. Aplique Autoformato Clásico 1 a la tabla de Resultados. Aplique formato numérico porcentual a casillas Incremento Ventas y Costo Incr. Ventas.

Solución. Tabla de Previsiones Incremento de Ventas Precio Costo incr. Ventas Costo Fijo

25% 20 57% 1750

Tabla de Resultados

Enero Febrero Marzo Abril Mayo Junio Julio Agosto Setiembre Octubre Noviembre

Volumen Ingresos Costo Costo Beneficios Ventas Ventas Variable Fijo 300 6000 3420 1750 830 375 7500 4275 1750 1475 469 9375 5344 1750 2281 586 11719 6680 1750 3289 732 14648 8350 1750 4549 916 18311 10437 1750 6124 1144 22888 13046 1750 8092 1431 28610 16308 1750 10552 1788 35763 20385 1750 13628 2235 44703 25481 1750 17472 2794 55879 31851 1750 22278

Melvin Quiñones Aguilar 22

INFONET SRL Diciembre

3492

69849

39814

1750

28285

Ejercicio. En el cuadros mostrados en la parte inferior:

Cálculos. Volumen Ventas.

Volumen Ventas mes anterior * Incremento Ventas + Volumen Ventas mes Anterior. Total Ventas. Volumen Ventas * Precio Venta. Desc. Ventas Total Ventas * % Descuento Ventas. Neto Ventas Total Ventas - Desc. Ventas. Volumen Compras. Volumen Compras mes Anterior * Incremento Compras + Volumen Compras mes Anterior. Total Compras. Volumen Compras * Precio Compra. Desc. Compras. Total Compras * % Descuento Compras. Neto Compras. Total Compras - Desc. Compras. Salario (fila 24) Casilla del valor del salario ($E6) Total Salario. Salarios * Trabajadores. Alquileres. Alquileres de la Tabla Gastos Fijos. Publicidad. Publicidad de la Tabla Gastos Fijos. Total Fijos. Alquileres + Publicidad. Ganancias. Neto Ventas. Perdidas. Neto Compras + Total Salarios + Total Fijos. Beneficio. Ganancias - Pérdidas. % Beneficio. (Beneficio * 100) / Ganancias.

þ Calcule las casillas en blanco, incluída la columna de los totales. þ Cree las vistas siguientes. ú Vista1, ha de incluir las tablas, Tabla Variables Ventas, Tabla Variables Compras y Tabla Gastos Fijos y Salario. ú Gastos, ha de incluir las tablas Compras, Salarios y Gastos Fijos. ú Resúmenes, ha de incluir la tabla de Resúmenes. þ Formato de la tabla. ú Aplique separadamente a las diferentes tablas el autoformato Lista 3. ú Elimine la visualización de decimales. ú Formato porcentual en las casillas B2, B4, E2 y E4.

Melvin Quiñones Aguilar 23

INFONET SRL Tabla de Variables Ventas % Inc. Ventas Precio Venta % Desc. Ventas

8% 3500 5%

Tabla Variables Compra

Tabla Gastos Fijos

% Inc. Compras Precio Compra % Desc. Compras

Alquileres Publicidad

Salario Enero Ventas Volumen Ventas Total Ventas Desc. Ventas Neto Ventas

Febrero

500 1750000 87500 1662500

540 1890000 94500 1795500

7% 2200 7%

95000 140000

103000

Marzo

Abril

Mayo

583 630 2041200 2204496 102060 110225 1939140 2094271

Junio

Julio

680 2380856 119043 2261813

735 2571324 128566 2442758

Agosto

793 857 2777030 2999192 138852 149960 2638179 2849233

Septiembre Octubre

Noviembre Diciembre Total

925 3239128 161956 3077171

1000 3498258 174913 3323345

1079 3778119 188906 3589213

1166 9489 4080368 33209971 204018 1660499 3876350 31549473

562

601

643

689

1155562 1236452 80889 86552 1074673 1149900

1323003 92610 1230393

1415614 99093 1316521

1514707 106029 1408677

1620736 13774107 113452 964188 1507284 12809920

Compras Volumen Compras Total Compras Desc. Compras Neto Compras

350

375

401

429

459

491

770000 53900 716100

823900 57673 766227

881573 61710 819863

943283 66030 877253

1009313 70652 938661

1079965 75598 1004367

Salarios Trabajadores Salario Total Salarios

5 103000 515000

5 103000 515000

5 103000 515000

5 103000 515000

5 103000 515000

5 103000 515000

5 103000 515000

5 103000 515000

5 103000 515000

5 103000 515000

5 103000 515000

5 5 103000 1236000 515000 6180000

Gastos Fijos Alquileres Publicidad Total Fijos

95000 140000 235000

95000 140000 235000

95000 140000 235000

95000 140000 235000

95000 140000 235000

95000 140000 235000

95000 140000 235000

95000 140000 235000

95000 140000 235000

95000 140000 235000

95000 140000 235000

95000 1140000 140000 1680000 235000 2820000

Resúmenes Ganancias Pérdidas Beneficios % Beneficios

1662500 1466100 196400 12

1795500 1516227 279273 16

1939140 2094271 1569863 1627253 369277 467018 19 22

2261813 1688661 573152 25

2442758 1754367 688391 28

2638179 2849233 1824673 1899900 813506 949333 31 33

3077171 1980393 1096778 36

3323345 2066521 1256825 38

3589213 2158677 1430536 40

3876350 31549473 2257284 21809920 1619065 9739553 42 28

525

Melvin Quiñones Aguilar

737

6261

25

INFONET SRL

Presentación de una hoja. Los cambios de presentación están incluidos dentro de la opción Ventana de la barra de Menús. Todos los ejemplos de esta sección se harán con el ejemplo anterior.

• Abrir una ventana nueva. Sirve para tener dos copias del mismo libro de trabajo, de esta manera, se pueden tener visibles dos partes del libro sin necesidad de desplazarse con el cursor. Siga los pasos siguientes 1. Sitúe el cursor sobre la casilla A9 (Ventas). 2. Active la opción Ventana/Nueva Ventana. Ahora tiene dos ventanas que presentan diferentes partes de la misma hoja. Puede verlo activando la opción Ventana, observe que la s últimas dos opciones son. Ejercicio.XLS:1 (Ventana 1, la que ya tenía) Ejercicio.XLS:2 (Ventana 2, la que ha creado).

...Y esto sirve para. Cuando estaba solucionando el Ejercicio anterior, era incómoda la introducción de las fórmulas en las casillas situadas por debajo de la tabla de compras, tenía que ir a buscar les referencias a tablas, cuyas casillas estaban visibles. Ahora, repetirá la operación de calcular los descuentos de la tabla compras utilizando las ventanas. Introducirá las fórmulas con el método de les teclas del cursor.

Ejemplo. 1. Active la ventana ejercicio:1 desde opción Ventana y sitúe el cursor sobre la casilla B18 (Descuentos Enero), borre el contenido. 2. Active ventana ejercicio:2 desde opción Ventana y sitúe el cursor sobre la casilla E1. 3. Vuelva a ventana ejercicio:1 y, ú Teclee = , ú Suba a la casilla B17 ú Teclee * ú Active opción Ventana y cambie a ejercicio:2 ú Vaya a la casilla E4 ú Pulse ENTER.

• Organizar ventanas. Puede organizar la disposición de las ventanas en pantalla con la opción de la Barra de Menús Ventana/Organizar... Aparece el siguiente cuadro de diálogo.

Melvin Quiñones Aguilar asd 26

INFONET SRL En Mosaico : Horizontal : Vertical : Cascada :

Distribuye el área de trabajo para mostrar todas las hojas. Distribuye el área de trabajo en ventanas horizontales. Distribuye el área de trabajo en ventanas verticales. Presenta las ventanas solapadas.

Ejemplo. 1. Active la opción Ventanas/Organizar Seleccione Vertical y pulse sobre Aceptar. 2. En la ventana de la izquierda, sitúese sobre la casilla B34 (Pérdidas Enero) y borre el contenido. 3. Cambie a la ventana derecha y sitúese sobre la casilla B16 (Volumen compras Enero), desplace la hoja hasta hacer que esta casilla sea la primera que se visualice en la parte superior. 4. Vuelva a ventana ejercicio:1 (Tiene que estar sobre la casilla B34), ú Pulse = ú Seleccione con el ratón la casilla B19 (Neto Compras) de ventana E8:2 , tiene que hacer dos clics, uno para activar ventana E8:2 y otro para que coja la referencia. ú Pulse + ú Seleccione la casilla B25 (T Salarios) de ventana ejercicio:2 . ú Pulse + ú Seleccione la casilla B30. ú Pulse ENTER. • Ventana/Ocultar. Oculta la ventana Activa. • Ventana/Mostrar Muestra una lista de las ventanas ocultas para que puedan volverse a visualizarse. þ Cierre Ventana2 activando opción cerrar del cuadro de menú de control.

• Dividir una hoja. También se utiliza para ver diferentes partes de una hoja. La hoja quedará dividida en dos o cuatro partes dependiendo de donde esté situada la casilla activa, si es a la primera fila, dividirá la ventana en dos secciones verticales, si es en la primera columna dividirá la ventana en dos secciones horizontales, en cualquier otra parte, dividirá la ventana en cuatro partes. La división de una hoja se activa con la opción de la Barra de Menús Ventana/ Dividir, o haciendo un clic y arrastrando los cuadros de división. Clic y desplace para división Horizontal. Situado al principio de la barra de desplazamiento vertical .

Clic y desplace para división Vertical. Situado al final de la barra de desplazamiento horizontal.

Para quitar la división de la

hoja, haga clic en el menú Ventana/Quitar división.

Melvin Quiñones Aguilar asd 27

INFONET SRL • Inmovilizar secciones de una hoja. Esta opción se utiliza para dejar fijas las filas que están por encima y las columnas que están a la izquierda de la casilla donde se encuentra el cursor (casilla activa). Se utiliza para ver las referencias de los títulos de filas y columnas.

Ejemplo. 1.Situe el cursor en la casilla B9 (Ventas Enero). 2. Active opción de la Barra de Menús Ventana/ Inmovilizar Secciones. O haga clic sobre el botón . Compruebe como quedan fijos los títulos de los menús y los conceptos, desplácese por la hoja de cálculo para comprobar este efecto. Para movilizar las secciones Ventana/Movilizar secciones .

Validación. Esta opción sirve para validar datos cuando se van entrando.

Ejemplo. Pondrá un mensaje de validación a las casillas de Gastos Fijos. 1. 2.

3. 4. 5. 6.

7.

Seleccione el rango de casillas de la H2 a H3. Active opción Datos/Validación, en la ficha Configuración, en apartado Permitir, escoja Número entero. En lista desplegable Datos, seleccione Entre, ponga en Mínimo 50.000 y en Máximo 500.000. Active la ficha Mensaje entrante, active la casilla de Mostrar mensaje al seleccionar la celda. En título, ponga Gastos, y en Mensaje de entrada, Gastos fijos. Seleccione la ficha Mensaje de error, active la casilla de Mostrar mensaje... En Estilo, escoja Advertencia, en Título, ponga Gastos, en Mensaje de error, ponga “No se puede entrar un valor menor de 50.000 ni mayor de 500.000”. Pulse Aceptar.

þ Pruebe de entrar un valor menor de 50.000 y otro mayor de 500.000 en H2 o H3.

þ Ponga mensaje de error en las casillas de Volumen ventas, “Mínimo de 500 y Máximo de 1500”. þ Ponga mensaje de error en las casillas de Volumen compras, “Mínimo de 250 y Máximo de 1.000 ”.

Melvin Quiñones Aguilar asd 28

INFONET SRL

Auditoría Auditoria, sirve para buscar las casillas que intervienen en las fórmulas de una hoja de cálculo. Siga los pasos siguientes, Active opción Herramientas/Auditoría, se desplegará el siguiente menú:

Rastrear precedentes: Muestra las casillas que intervienen en la fórmula de la casilla seleccionada.

Ejemplo. 1. Seleccione la casilla B19 (Continuamos con el ejercicio BASICO8.XLS) 2. Active opción Herramientas/Auditoría/rastrear precedentes . 3. Repita el paso nº 2 dos veces, verá el origen de la fórmula de la casilla B19. 4. Para quitar las flechas indicativas Herramientas/ Auditoría/Quitar todas las flechas. Rastrear dependientes :

Muestra las casillas que dependen de la que hay seleccionada.

Ejemplo. 1.Seleccione la casilla B10. 2.Active opción Herramientas/Auditoría/Rastrear Dependientes. 3. Repita el paso 2 tres veces, verá las casillas en las cuales interviene la casilla B10 en una fórmula. 4.Para quitar las flechas indicativas, Herramientas/ Auditoría/Quitar todas las flechas. Rastrear error:

Si se produce un error en la fórmula de la casilla activa (o otra cualquiera), muestra el origen de este error.

Ejemplo. 1. Seleccione la casilla C13. 2. Cambie la fórmula C11-C12 por C11-C8. 3. Seleccione la casilla C35, Herramientas/ Auditoría/Rastrear error. La casilla donde se unen una flecha azul y otra roja es donde se ha producido el error por vez primera. 4. Corrija la fórmula de la casilla C13. 5. Quite las flechas.

Quitar todas las flechas:

Elimina todas las flechas.

Melvin Quiñones Aguilar asd 29

INFONET SRL

Mostrar barra de auditoría: Muestra la siguiente barra de botones: La descripción de los botones, de izquierda a derecha, és la siguiente: Rastrea Nivel Precedente. Elimina Nivel precedente. Rastrea Nivel dependiente. Elimina Nivel dependiente. Quitar las flechas. Rastrea error. Muestra el comentario de la casilla (si hay). Envuelve con un círculo los datos no válidos de una validación. Borra los círculos de una validación.

Preparar Impresión. Preparará el ejercicio anterior para imprimirlo. 1.Active opción Archivo/Presentación preliminar , o pulse el botón Excel mostrará tal como quedaría impresa la hoja.

de la barra estándar.

2. Haga clic sobre el botón Configurar. También puede ejecutar esta acción con la opción Archivo/Configurar página de la barra de menús. Aparecerá un cuadro de diálogo con diferentes fichas. 2.1. Seleccione apartado Página. 2.2. Orientación /Presentación Horizontal. 2.3. Escala/Ajustar a:, 1 página de ancho, por 1 de alto. 3. Apartado Márgenes . Este apartado sirve para ajustar los márgenes, no los ajuste desde esta opción, lo h ará cuando vuelva a presentación preliminar. 4.Encabezado y pie de página. 4.1. En apartado Personalizar Encabezado, en sección izquierda, ponga la fecha, y en sección derecha la hora. Utilice estos botones . 4.2. En apartado Personalizar pie de página, en sección izquierda ponga el nombre del archivo. Utilice este botón

.

5. Pulse sobre el botón Aceptar, para volver a presentación preliminar. 6. Ajuste los márgenes. Pulse sobre el botón Márgenes . Aparecen unas líneas horizontales y verticales que indican la posición de los márgenes.

Melvin Quiñones Aguilar asd 30

INFONET SRL 6.1Situe el cursor sobre estas líneas, cuando el cursor se transforme en una cruz de doble flecha, pulse el botón izquierdo del ratón y arrastre hasta ajustar los márgenes. 6.2 La línea horizontal superior es el margen para la cabecera, la siguiente, el margen superior de la hoja de cálculo. La última línea es el margen para el pié de página, la anterior el margen inferior de la hoja. 7. El apartado Zoom, sirve para aumentar o disminuir la vista de la hoja. 8. Los botones Anterior y Siguiente, se activan cuando los datos que se quiera imprimir no quepan en una sola hoja de papel. 9. Pulse Cerrar para volver a la hoja de cálculo.

• Imprimir. Para imprimir una hoja, active opción de la barra de menús Archivo/Imprimir,. Se desplegará un cuadro de diálogo como este:

.Imprimir: En esta sección, puede escoger lo que desee imprimir, el rango seleccionado, las hojas seleccionadas o bien todo el libro. .Copias: Sirve para especificar cuantas copias quiere de cada hoja. .Intervalo de páginas: Imprimir todas las páginas, o solo las indicadas en cajas de texto Desde, Hasta. .Impresora: Si dispone de varias impresoras esta opción le permitirá seleccionar la que quiera utilizar. þ Imprima el Ejercicio . Si quiere que se imprima en una sola hoja, active opción Archivo/Configurar página, seleccione la ficha Página y active la casilla de verificación Encajar en 1 Páginas de ancho por 1 de Alto .

Melvin Quiñones Aguilar asd 31

INFONET SRL

Edición de datos en diferentes hojas. Un archivo de Excel (libro de trabajo) puede tener más de una hoja de datos, y permite hacer operaciones cogiendo datos de diferentes hojas.

• Cambiar de hoja. • •

Haga un clic sobre pestaña de la hoja situada en la parte inferior. Pulse las combinaciones de teclas Ctrl+Av Pág o Ctrl+Re Pág.

• Trabajar en modo grupo. Este modo lo utilizará cuando quiera introducir los mismos datos o aplicar los mismos formatos dentro de la misma área pero en hojas diferentes. Para agrupar hojas. 1. Con la tecla de mayúsculas pulsada, haga clic sobre las pestañas de las hojas que quiera agrupar. 2. Entre los datos en las casillas. •

Observe, cambiando de hoja, como se han introducido los datos en todas las hojas agrupadas.

Para desagrupar las hojas. 1. Sitúese sobre cualquier pestaña de una hoja agrupada, 2. Haga clic con el botón derecho del ratón. 3. Del menú desplegable que aparece, seleccione opción Desagrupar Hojas.

Ejercicio 1. Abrir un nuevo archivo en Excel 2000. En la celda C1 de la Hoja1 escriba Ganancias, C1 de la Hoja 2 Gastos y en C1 de la Hoja 3 Beneficios. þ Copie los datos siguientes correspondientes a los títulos de los trimestres y las tiendas utilizando el modo grupo (agrupar tres hojas), estos son iguales para las tres hojas. þ Desagrupe las hojas y copie los datos pertenecientes a los cuatro trimestres para Hoja1 y Hoja 2, y calcule los totales por trimestre para las dos primeras hojas.

Ganancias

Tienda 1 Tienda 2 Tienda 3

1 Trimestre 2 Trimestre 3 trimestre 4 Trimestre 150000 175000 300000 390000 200000 210000 280000 300000 180000 220000 280000 185000

Total 1015000 990000 865000

Melvin Quiñones Aguilar 30

INFONET SRL

þ Cambie a segunda hoja y copie los datos siguientes: Gastos

Tienda 1 Tienda 2 Tienda 3

1 Trimestre 2 Trimestre 3 trimestre 4 Trimestre 60000 90000 150000 100000 180000 100000 100000 75000 160000 100000 100000 100000

Total 400000 455000 460000

• Entrar fórmulas. Cuando tenga que entrar una fórmula compuesta por referencias a casillas de diferentes hojas, lo puede hacer de la manera siguiente: 1. Seleccione la casilla donde quiera entrar la fórmula, ponga el signo =. 2. Cambie de hoja, y seleccione la casilla sobre la que quiera operar. 3. Ponga el signo de la operación. 4. Repita los pasos 2 y 3 para a cada casilla que entre en la fórmula. 5. Finalmente pulse ENTER. Si quiere entrar las fórmulas con el teclado, ha de especificar en la fórmula la hoja donde se encuentra la casilla, poner el signo de admiración y finalmente la referencia: (=Hoja2!B4 - Hoja3!B4). Beneficios 1 Trimestre

2 Trimestre

3 trimestre

4 Trimestre

Total

Tienda 1 Tienda 2 Tienda 3

Ejemplo . Calcule los beneficios en Hoja3. 1. Sitúe el cursor en la casilla donde va la fórmula. (Tienda 1 de 1 Trimestre). 3. Teclee = 4. Haga clic sobre la pestaña de Hoja 1 o pulse dos veces la combinación CRTL+Re Pág. 5. Seleccione la casilla que quiera operar (1 Trimestre de Tienda 1 de Hoja1). 6. Pulse el signo 7.Haga clic en la pestaña Hoja2, o pulse la combinación CRTL+Re Pág. 8. Seleccione la casilla que quiera operar (1 Trimestre de Tienda 2 de Hoja 2). 9. Pulse ENTER.

• Insertar una hoja nueva. Active opción de la barra de menús Insertar/Hoja de cálculo.

• Borrar una hoja. Melvin Quiñones Aguilar 31

INFONET SRL Sitúese sobre la pestaña de la hoja que quiera borrar (o seleccione las hojas a borrar), haga clic con el botón derecho del ratón, del menú que se despliega, seleccione opción Eliminar.

• Cambiar el nombre de una hoja. 1. Haga clic con el botón secundario del ratón sobre la pestaña de la hoja a la que quiera cambiarle el nombre, del menú que se despliega, seleccione opción Cambiar Nombre. 2. Ponga el nombre y pulse Aceptar.

þ Ponga nombre a las tres hojas del ejercicio: Ventas, Compras, Beneficios.

Solución de la hoja de Beneficios. Beneficios

Tienda 1 Tienda 2 Tienda 3

1 Trimestre 2 Trimestre 3 trimestre 4 Trimestre 90000 85000 150000 290000 20000 110000 180000 225000 20000 120000 180000 85000

Total 615000 535000 405000

Ejercicio 2. Abrir un nuevo archivo en Excel 2000. En la celda C1 de la Hoja1 escriba Curso1, C1 de la Hoja 2 Curso2 y en C1 de la Hoja 3 Media. þ Ponga nombre a cada hoja; Hoja 1 Curso 1, Hoja2 Curso 2 y Hoja 3 Media. þ Copie las notas de las dos primeras hojas. þ Ponga en modo grupo las tres primeras hojas y copie los nombres y los títulos de la primera fila y primera columna. þ Desactive el modo grupo y calcule la media en la tercera hoja. Se ha de utilizar la función =PROMEDIO, pero con la notación

=PROMEDIO(Hoja1!Casilla; Hoja2! Casilla) þ Formatos. Ponga las hojas en modo grupo. ú Ponga los títulos de filas y columnas en Negrita tamaño 12. ú Ponga color de fondo Gris en el rango de casillas numéricas. ú Ponga borde inferior a la primera fila. ú Ponga borde derecho a la primera columna y Ajuste el ancho de las columnas. 1ª Hoja.

Ana Francesc Cristina Alberto Sonia Jesús

1 Evaluación 2 Evaluación 3 Evaluación 7,3 6,5 8 5,4 6 4,8 6,5 7 8,5 5,8 7,3 6 4,5 4 6 8,5 7,3 5,8

2ª Hoja.

Melvin Quiñones Aguilar 32

INFONET SRL

Ana Francesc Cristina Alberto Sonia Jesús 3ª Hoja. Ana Francesc Cristina Alberto Sonia Jesús

1 Evaluación 2 Evaluación 3 Evaluación 5,7 6,5 7 4,6 6 5,5 3,7 6,2 8 7,8 7 6,9 6,5 6 5,5 4,5 5,6 5 1 Evaluación 2 Evaluación 3 Evaluación 6,5 6,5 7,5 5 6 5,15 5,1 6,6 8,25 6,8 7,15 6,45 5,5 5 5,75 6,5 6,45 5,4

Ejercicio 3. Abrir un nuevo archivo en Excel 2000. Copiar los datos que se muestran en la parte inferior. þ Ponga el nombre Precio Compra a la primera Hoja. þ Ponga el nombre Incrementos a la segunda Hoja. þ Ponga el nombre Precio Venta a la tercera Hoja. Calcule el precio venta, incrementando el precio de compra de la primera hoja con los incrementos de la segunda hoja. þ Ponga el nombre Cantidad Ventas a la cuarta hoja. þ Ponga el nombre Beneficios a la quinta hoja. Calcule los Beneficios que resultaran de multiplicar la cantidad de Hoja Cantidad Ventas por Precio de Hoja Precio Venta, menos cantidad de Hoja Cantidad Ventas por precio de Hoja Precio Compra. þ Calcule los totales de Precio Venta y Beneficios.

1ª. Hoja, Precio Compra.

COCA COLA TÒNICA NARANJADA LIMONADA VERMUT ZUMOS

ENERO FEBRERO 20 22 15 15 18 18 18 18 40 40 20 20

MARZO 23 15 18 18 40 25

ABRIL

MAYO 23 18 20 20 50 30

30 20 25 25 60 30

2ª. Hoja, Incrementos.

COCA COLA TÒNICA NARANJADA LIMONADA VERMUT ZUMOS

ENERO FEBRERO MARZO ABRIL MAYO 15% 15% 15% 20% 20% 10% 10% 10% 10% 15% 10% 10% 10% 10% 20% 10% 10% 10% 10% 20% 15% 15% 15% 20% 25% 10% 10% 10% 20% 20%

Melvin Quiñones Aguilar 33

INFONET SRL 3ª. Hoja, Precio Venta.

COCA COLA TÒNICA NARANJADA LIMONADA VERMUT ZUMOS

ENERO FEBRERO 23 25 17 17 20 20 20 20 46 46 22 22

MARZO 26 17 20 20 46 28

ABRIL

ENERO FEBRERO 60 55 30 30 40 40 40 40 30 30 60 60

MARZO 55 30 40 40 34 60

ABRIL

MAYO 28 20 22 22 60 36

36 23 30 30 75 36

TOTAL 138 92 111 111 273 144

4ª. Hoja, cantidad Venta.

COCA COLA TÒNICA NARANJADA LIMONADA VERMUT ZUMOS

MAYO 65 35 45 45 50 60

70 40 55 55 55 60

TOTAL 305 165 220 220 199 300

5ª. Hoja, Total Beneficios. ENERO

COCA COLA TÒNICA NARANJADA LIMONADA VERMUT ZUMOS

180 45 72 72 180 120

FEBRERO

181,5 45 72 72 180 120

MARZO

189,75 45 72 72 204 150

BENEFICIOS

ABRIL

299 63 90 90 500 360

MAYO

TOTAL

420 1270,25 120 318 275 581 275 581 825 1889 360 1110 5749,25

Ejercicio 4. Abrir un nuevo archivo en Excel 2000 y copiar el contenido de los cuadros mostrados en la parte inferior. þ Ponga Nombre a las Hojas. Hoja1 Ventas, Hoja2 Artículos y Hoja4 Totales.

Compras, Hoja3

þ En Hoja Artículos, calcule el precio venta, utilizando el % de la casilla inc. Venta (Casilla B22). þ En Hoja Compras, calcule el total de cada fila en la columna totales. þ Haga lo mismo en Hoja Ventas. þ En última hoja, la de Totales, tiene que hacer los siguientes cálculos: ú

ú

La columna Cantidad Compra, es la columna totales de hoja de compras. La columna Cantidad Venta, es la columna totales de hoja de ventas.

Melvin Quiñones Aguilar 34

INFONET SRL ú

Pesetas Compra = Cantidad Compra * Precio Compra de Hoja Artículos.

ú

Pesetas Venta = Cantidad Venta * Precio Venta de Hoja Artículos.

ú

En la columna Descuento, multiplique Pesetas Venta * Desc venta de hoja Artículos.

ú

Venta - Descuento, reste Pesetas venta - Desc.

ú

En la columna de Totales, reste (Venta-Desc.) -Pesetas Compra.

Ventas.

SEMANAS Fútbol Baloncesto Handbol Waterpolo Rugbi Fútbol Americano Tenis(3) Tenis(6) Ping-Pong(6) Ping-Pong(12) Squash Voleibol Fútbol Sala Hockey Hockey Patines

1-jun-96 15-jun-96 29-jun-96 7-jun-96 21-jun-96 5-jul-96 6 2 0 6 0 0 5 4 4 12 3 5 1 0 0

5 4 1 2 1 0 4 4 2 8 4 2 0 0 0

4 0 2 7 1 1 1 5 6 3 3 4 0 0 1

13-jul-96 19-jul-96 2 7 8 8 2 0 4 6 9 11 3 2 2 2 0

27-jul-96 2-ago-96 8 7 1 5 3 3 7 9 11 5 0 3 6 0 2

Totales 25 20 12 28 7 4 21 28 32 39 13 16 9 2 3

Compras.

SEMANAS Fútbol Baloncesto Handbol Waterpolo Rugbi Fútbol Americano Tenis(3) Tenis(6) Ping-Pong(6) Ping-Pong(12) Squash Voleibol Fútbol Sala Hockey Hockey Patines

1-jun-96 15-jun-96 29-jun-96 7-jun-96 21-jun-96 5-jul-96 15 10 10 10 10 10 10 10 10 10 10 10 10 10 10

0 0 0 10 0 0 0 5 10 10 0 0 0 0 0

0 10 10 10 0 0 5 5 10 10 5 0 0 0 0

13-jul-96 19-jul-96 10 0 0 0 0 0 10 10 5 10 5 5 0 0 0

27-jul-96 2-ago-96 5 0 0 0 0 0 0 5 5 0 0 5 0 0 0

Totales 30 20 20 30 10 10 25 35 40 40 20 20 10 10 10

Melvin Quiñones Aguilar 35

INFONET SRL Artículos.

Artículo Fútbol Baloncesto Handbol Waterpolo Rugbi Fútbol Americano Tenis(3) Tenis(6) Ping-Pong(6) Ping-Pong(12) Squash Voleibol Fútbol Sala Hockey Hockey Patines

Precio Compra Precio Venta Desc. Venta 4100 6355 5% 3900 6045 2900 4495 1700 2635 3000 4650 3300 5115 7% 4200 6510 8000 12400 6% 2100 3255 4000 6200 1800 2790 1400 2170 5% 3200 4960 1100 1705 1300 2015 3%

Incremento venta

55%

Totales.

Artículo

Cantidad Cantidad Pesetas

Pesetas

Compra

Venta

Venta

Compra

Desc.

Venta-%desc Totales

Fútbol

30

25

150931,25

27931,25

Baloncesto

20

20

123000 158875 7943,75 78000 120900

0

120900

42900

Handbol

20

12

58000

53940

0

53940

-4060

Waterpolo

30

28

51000

73780

0

73780

22780

Rugbi

10

7

30000

32550

0

32550

2550

Fútbol Americano

10

4

33000

20460

1432,2

19027,8

-13972,2

Tenis(3)

25

21

105000 136710

0

136710

31710

Tenis(6)

35

28

280000 347200

20832

326368

46368

Ping-Pong(6)

40

32

84000 104160

0

104160

20160

Ping-Pong(12)

40

39

160000 241800

0

241800

81800

Squash

20

13

36000

36270

0

36270

270

Voleibol

20

16

28000

34720

1736

32984

4984

Fútbol Sala

10

9

32000

44640

0

44640

12640

Hockey

10

2

11000

3410

0

3410

-7590

Hockey Patines

10

3

13000

6045

181,35

5863,65

-7136,35

Melvin Quiñones Aguilar 36

INFONET SRL

Funciones condicionales. Este tipo de funciones sirven para realizar una acción u otra según el resultado de evaluar una condición. Constan siempre de un argumento condición y dependiendo, de si esta condición se cumple o no, se ejecutará una acción u otra.

Funciones. Funciones Condicionales. =SI(Condición; valor o expresión1; valor o expresión2). Esta función evalúa condición. Sí condición es cierta, se ejecuta valor o expresión1, sino (la condición es falsa), se ejecuta valor o expresión2. =CONTAR.SI(Rango;”Condición”) Esta función cuenta cuantas casillas de rango cumplen la condición. =SUMAR.SI(Rango a evaluar;”Condición”;Rango a sumar) Suma las casillas del Rango a Sumar que cumplen la Condición en su correspondiente Rango a evaluar. Nota: Tener en cuenta que tanto las funciones CONTAR.SI como SUMAR.SI, la condición se ha de escribir entre comillas o bien utilizar una casilla como tal.

Ejemplo de función =Si. A la casilla de descuento (B2), se aplica un 5% sobre el total (B1), si este supera los 100.00 soles, sino, no se aplica descuento.

þ Copie el ejercicio, ponga un total menor que 100.00 en B1,verá que en B2 el descuento que aparece es de un 0%, ya que no se cumple la condición B1 > 100.00 þ Cambie el total por una cantidad superior a 100.00. Verá que el descuento de B2 es 5%.

Ejemplo de función =CONTAR.SI En la casilla B10 se ha utilizado la función =CONTAR.SI para saber el número de alumnos aprobados. Cuenta cuantas casillas del rango B2:B9 cumplen la condición de ser >=5.

Melvin Quiñones Aguilar 37

INFONET SRL Ejemplo de función SUMAR.SI En la casilla B13 se ha utilizado la función SUMAR.SI para calcular el total de ventas de un vendedor (Juan en el ejemplo). Observe que el rango a evaluar es B2:B12 (Columna de Vendedores). La condición es B14 (Casilla donde se pone el nombre del vendedor) y el rango que se suma es C2:C14 (Columna de ventas). La función SUMAR.SI, sumará en este caso las ventas de Juan, para sumar las ventas de los otros vendedores, solo hará falta poner su nombre en la casilla B14. Se Podría interpretar la función de B13 de la forma, SUMA las casillas del Rango C2:C12 cuyas correspondientes en el rango B2:B12 sean iguales al valor de B14.

Esta función, también se puede escribir desde el asistente, para hacer lo mismo que en el ejemplo Sumar.Si. 1.

Active opción Herramientas/Asistente/Suma Condicional.

2.

Se despliega una ventana, pidiendo el rango de casillas donde están los valores a utilizar, ponga $A$3:$C$12. Pulse Siguiente.

3.

Esta ventana pide el nombre de la casilla a sumar, es decir el título, seleccione Valor Ventas. Más abajo, en apartado Columna, ha de escoger el título de la columna donde quiera poner la condición, escoja Vendedor. En apartado Es:, seleccione el signo =, y a Este Valor, el nombre del vendedor sobre el que quiera contar el total de ventas, en este caso, Juan. Haga clic sobre el botón Agregar condición y pulse Siguiente.

4.

En la tercera ventana, ha de escoger entre copiar sólo la fórmula o bien la fórmula y los valores, seleccione esta última y Siguiente.

5.

En la ventana siguiente, ha de poner una casilla donde aparecerá el título, en este caso Juan, ponga B14.

6.

Y en la última ventana, ponga la casilla A15, que será la casilla donde habrá los valores sumados.

Compruebe que el resultado es el mismo, pero se ahorra teclear los datos.

Melvin Quiñones Aguilar 38

INFONET SRL •

Resaltar datos condicionados. Esta opción permite escoger el formato y el color de los datos según una condición. 1. 2.

Seleccione la columna Valor Ventas. Active opción Formato/Formato condicional. En la primera casilla de selección, escoja Valor de la celda, en la segunda, seleccione Mayor que, y en la tercera, ponga 20000. 3. Pulse el botón Formato, seleccione el color rojo y estilo de la fuente Cursiva, pulse Aceptar. 4. Si quiere poner otra condición, pulse el botón Agregar >>, y siga los mismos pasos. Pulse Aceptar. Compruebe como todas las casillas que superan el valor 20000, están en color rojo y en cursiva.

Funciones. Algunas funciones de búsqueda. =BUSCARV(Valor;Rango o nombre de Rango;n Columna); Busca en la primera columna del Rango o nombre del rango el Valor, y muestra el contenido de su correspondiente n Columna hacia a la derecha. =ELEGIR(Índice; Elemento1, Elemento2,...,Elemento n) Muestra el valor del Elemento que ocupa la posición Índice en la lista. =INDICE(Rango o Nombre del Rango;Fila;Columna) Muestra el elemento del Rango o Nombre del Rango situado en la posición fila, columna de la tabla.

Ejemplo de función =BUSCARV En la casilla B9 se ha utilizado la función BUSCARV para encontrar el nombre del producto que tiene código 2. BUSCA el valor de casilla B8 en la primera columna del Rango A2:C5 y muestra el valor correspondiente de la segunda columna de este Rango. Cambie el valor del código en la casilla B8 para ver como automáticamente cambia el nombre del producto y el precio.

Melvin Quiñones Aguilar 39

INFONET SRL

Nombre de Rango. A veces, para clarificar o simplificar operaciones en una hoja de cálculo, es necesario, dar nombre a una casilla o rango de casillas. La función BUSCARV, por ejemplo, tiene como argumento un rango de casillas que contiene la tabla donde se han de buscar los valores. Para no tener que recordar las referencias de esta tabla, se le puede poner un nombre al rango que ocupa dentro de la hoja, de esta manera, en lugar de utilizar las referencias de rango, se podrá utilizar su nombre. Para poner nombre a un rango. 1. Seleccione el rango. 2. Active opción de la barra de menús Insertar/ Nombre/ Definir. Aparece el siguiente cuadro de diálogo.

3. En cuadro de edición Nombre en el libro, teclee el nombre para el rango. 4. Pulse sobre el botón Aceptar.

• Como ejemplo ponga Nombre al rango que comprende la tabla del ejemplo, el que se utiliza en la función BUSCARV. 1. Seleccione el rango A2:C5. 2. Active opción Insertar/ Nombre / Definir. 3. En cuadro de edición Nombres en el libro, teclee Frutas.

Ejemplo de función =BUSCARV Observe como quedan ahora las funciones BUSCARV de B9 y B11.

Melvin Quiñones Aguilar 40

INFONET SRL • Los Nombres de rango también permiten desplazarse rápidamente hacia a ellos. Siga los pasos del ejemplo siguiente. 1. Sitúe el cursor en la casilla A30 (per ejemplo). 2. Despliegue la lista de la parte izquierda de la barra de fórmulas, la que indica la casilla donde tiene situado el cursor. Si no tiene visible la barra de fórmulas actívela con la opción Ver/ Barra de fórmulas. Se desplegará una lista parecida a esta.

3. Haga un clic con el ratón sobre elemento Frutas y verá como seguidamente queda seleccionado este rango. • Otra forma rápida de desplazarse por la hoja es pulsar la tecla F5. Pruébelo.

• Siga los pasos siguientes para ver un ejemplo de como utilizar el ratón para entrar un rango con nombre en funciones que así lo requieran. þ Previamente borre el contenido de la casilla B9. 1. Seleccione la casilla donde ha de ir la función. Seleccione B9. 2. Teclee el nombre de la función. =BUSCARV( 3. Haga clic sobre la casilla donde hay el valor. Haga clic sobre casilla B8. 4. Teclee punto y coma para separar los argumentos. 5. Haga clic en lista desplegable de la barra de fórmulas y seleccione elemento Frutas. 6. Teclee punto y coma para separar los argumentos. 7. Teclee el desplazamiento. Teclee 2. 8. Cierre el paréntesis y pulse ENTER. •

Observación. Tenga en cuenta que si los valores de la primera columna de una tabla no están ordenados en la función BUSCARV tendrá que poner un cuarto parámetro con valor 0 para indicarlo. =BUSCARV(valor;tabla;Columna;0)

Ejemplo de función =ELEGIR. En la casilla B7 se mostrará el valor Manzanas. Escoger el elemento que ocupa la posición indicada en B6 de la lista B1,B2,B3,B4.

Melvin Quiñones Aguilar 41

INFONET SRL

Ejemplo de función =INDICE La función INDICE de la casilla B10, localiza el precio a aplicar según los valores de B8(fila) y B9(Columna). Previamente al rango B3:D5 se le ha dado el nombre Precios. El precio localizado será 52. Localiza en la tabla Precios(B3:D5) el elemento que está en la fila indicada por el valor de B8 y columna indicada por el valor de B9.

Ejercicio. En el cuadro mostrado. Calcule lo que se le indica:

Cálculos. Soles Descuento Total Base Imponible Iva 16% Importe Tipo Cliente Descuento Cliente Total Factura Productos Desc. 10%

Precio * Cantidad Si Soles > 250.000 aplicar 10% sino aplicar 5% Soles-(Soles * Descuento) Suma de la columna Total. Aplicar 16% sobre Base Imponible. Base Imponible + Iva Escriba 1, 2 o 3 Seleccionar los descuentos según sea Cliente Normal, Cliente Habitual o Pariente Importe - (Importe * Descuento). Contar los productos con descuento del 10%.

þ Utilice la función =SI en la columna de Descuento. þ Utilice la función =ELEGIR en la casilla Desc. Cliente, el índice es el valor de la casilla Tipo de Cliente y los valores son los de la tabla Cliente Habitual, Cliente Normal, Pariente. þ Utilice la función =SUMA con el rango en la columna Totales para calcular la Base Imponible. þ Utilice la función =CONTAR.SI en la casilla Desc. 10% þ Formatos. ú ú ú ú

Aplique a la factura y a la tabla clientes autoformato clásico2. Ponga las columnas Precio, Cantidad y Pesetas en formato numérico con separador. La columna Descuento y la casilla Desc. Cliente tiene formato %. La columna Total formato Monetario.

Melvin Quiñones Aguilar 42

INFONET SRL Precio

Cantidad

Soles

Descuento

Total

Televisor

Artículos

120.00

7

840.00

10%

756.00

Lavadora

80.00

4

320.00

10%

288.00

Microondas

33.00

8

264.00

10%

237.60

Tostadora

12.00

12

144.00

5%

136.80

Nevera

140.00

9

1.260.00

10%

1.134.00

Batidora

7.50

12

90.00

5%

85.50

Secador

4.50

23

103.50

5%

98.32

Base I.V.A. Importe Tipo Cliente Imponible 16% 2.736.22 437.79 3.174.02

Cliente Normal

0%

Cliente Habitual

5%

Parientes

Desc Cliente. 2

Producto Desc. 10%

Total Factura 5% 3.015.32

4

10%

Ejercicio. En el cuadro mostrado. Calcule lo que se le indica:

Cálculos. Soles Descuento. Total. Base Imponible Total I.V.A. Importe Tipo Desc. Desc. Factura Total Factura

Cantidad * Precio U. (Si Soles > 20.000 aplicar Descuento de casilla %Descuento sino 0) * Soles Soles - Descuento Suma de los valores de la columna Total. Base Imponible * Iva 16% Base Imponible + Iva Teclee 1 o 2 Aplicar descuento según sea Normal (1) o Especial(2). Importe - (Importe * Desc. Factura).

þ En la columna Descuento aplique la función =SI, el % de descuento está en la casilla descuento y se tiene que poner una referencia hacia esta casilla. þ En la casilla Desc. Factura utilice la función =ELEGIR, el índice es el valor de la casilla Tipo Desc. y los valores son los de las casillas Desc. Normal y Desc. Especial. þ Utilice la función =SUMA con los valores de la columna Total, para calcular la Base Imponible þ Formatos. Como quiera.

Melvin Quiñones Aguilar 43

INFONET SRL

%Descuento I.V.A.

7% 16%

7Desc. Normal 5% Desc. Especial 15% Cantidad Descripción Precio U. Soles Descuento Total 13 Novelas 795 10335 0 10335 15 Fascículos 395 5925 0 5925 4 Enciclopedias 75000 300000 21000 279000 24 Revistas 215 5160 0 5160 7 Diccionarios 3500 24500 1715 22785 Base Imponible Total I.V.A Importe Tipo Desc. Desc.Factura Total Factura 323205 51712,8 374917,8 1 0,05 356171,91

• Función =Si(; ;), con varias condiciones. A veces nos vemos en la necesidad de utilizar más de una condición dentro de una misma fórmula, para ello utilizaremos la función SI, con mas de una condición.

Ejemplo. Suponga que quiere aplicar un descuento en los productos de un albarán a un cliente. Si és cliente habitual: Si compra una cantidad superior o igual a 1000 unidades, aplicar un 6% de descuento. Si compra una cantidad inferor a 1000 unidades, aplicar un 5% de descuento. Si no és cliente habitual: Si compra una cantidad superior o igual a 1000 unidades, aplicar un 4% de descuento. Si compra una cantidad inferor a 1000 unidades, aplicar un 3% de descuento. Recordemos el formato de la función SI: =SI(Condición;Cierto;Falso) Condición: La condición a controlar. Cierto: Si la condición és verdadera ,ejecuta esta acción. Falso: Si la condición no se cumple, ejecuta esta acción. Para calcular el descuento de la casilla D5 B$2=1 (Cliente habitual) B$21 (Cliente no habitual) B5>=1000 (Cantidad es mayor o igual a 1000 unidades) B5=1000;6%;5%);SI(B5>=1000;4%;3%)) Segunda posibilidad: =SI(Y(B$2=1;B5>=1000);6%;SI(Y(B$2=1;B5=1000);4%;3%))) La función Y(condició1;condició2;..;Condición N), significa que se han de cumplir todas las condiciones que cierran los paréntesis. Observe como se pondrían las condiciones:

Cálculos. Descuento Total.

Una de las dos posibilidades de la función SI (cantidad*precio)-(cantidad*precio*descuento)

Melvin Quiñones Aguilar 44

INFONET SRL Casilla B2

Escriba 1 para cliente habitual y cuanquier otra cosa para cliente no habitual. Una posible solución a este ejemplo:

þ Cambie las cantidades y la casilla de tipo de cliente y verá como el % de descuento va variando. •

Ejercicio. En el cuadro mostrado. Calcule lo que se le indica:

Cálculos. Soles % Descuento.

Total. Base Imponible. Iva 16%. Importe. Tipo Cliente. Desc. Cliente. Total Factura

Precio * Cantidad. Si Soles < 100.00 Descuento del 3% Si Soles > 100.00 y Soles 300.00 y Soles 1.000.00 Descuento del 10%. Soles - (Soles * % Descuento). Suma de los valores de la columna Total. 16% de Base Imponible. Base Imponible + Iva 16% Escoja 1, 2 o 3. Aplicar Descuento según valor de la casilla Tipo cliente Importe - (Importe * Desc. Cliente).

þ En la columna de %Descuento utilice las funciones =Si =Y para calcular el descuento correspondiente según los valores de la tabla descuentos. þ En la casilla Desc. Cliente utilice la función =ELEGIR, como índice el valor de la casilla Tipo cliente y como lista las casillas Cliente Normal, Cliente Habitual y Parientes separadas por punto y coma. þ En la casilla Base Imponible, utilice la función =SUMA con el rango correspondiente de la columna Total. Artículos

Precio

Cantidad

Soles

%Descuento

Total

Televisor

120000

7

840000

0,07

781200

Lavadora

80000

4

320000

0,07

297600

Microondas

33000

8

264000

0,05

250800

Tostadora

12000

12

144000

0,05

136800

140000

9

1260000

0,1

1134000

Batidora

7500

12

90000

0,03

87300

Secador

4500

23

103500

0,05

98325

Nevera

Base Imponible 2786025

I.V.A. 16% Importe 445764

Cliente Normal

0%

Cliente Habitual

5%

3231789

Tipo Cliente

Desc. Cliente 2

0,05

Total Factura 3070199,55

Melvin Quiñones Aguilar 45

INFONET SRL Parientes

10%

Asistente para funciones. El asistente para a funciones se activa pulsando sobre el botón de la barra estándar o bien activando la opción de la barra de menús Insertar/Función. En el primer ejercicio de Condiciones, siga ejemplos siguientes. Antes, calcule de nuevo todas las casillas, excepto la columna correspondiente al descuento y la casilla E12.

Ejemplo . 1. Seleccione la primera casilla E3 correspondiente a columna Descuento y haga un clic sobre el botón de asistente para funciones. 2. Seleccione la categoría Lógicas, y la función Si. Pulse el botón Siguiente. 3. En cuadro de edición Prueba-Lógica ponga la casilla que ha de cumplir la condición, D3>250000. 4. Si Verdadero, ponga 10%, y Si Falso, 5%. Pulse sobre Terminar.

Cálculo de la columna del descuento según el cliente.

Ejemplo . 1. Seleccione la casilla E12 y pulse el botón de asistente para funciones. 2. Seleccione la categoría Búsqueda y referencia, y la función Elegir. Pulse sobre Siguiente. 3. En cuadro de edición Indice: ponga la casilla D12, que es la casilla donde hará la selección del tipo de cliente. 4. En cuadro de texto Valor1: ponga la casilla donde hay el primer tipo de cliente, la B15, en Valor2: B16, y en Valor3: B17, pulse el botón Terminar.

Melvin Quiñones Aguilar 46

INFONET SRL

Insertar Gráficos. En el cuadro mostrado. Calcule lo que se le indica: 1. Seleccione el rango de la tabla que quiera representar gráficamente, toda la tabla (incluyendo la fila de meses y la columna de frutas) excepto la columna de totales. 2. Active opción Insertar/Gráfico. También puede hacer clic sobre el botón . 3. Aparece una ventana con diferentes tipos de gráficos, en Tipo de gráfico, escoja Columnas, en Subtipo de gráfico, el primero. Pulse Siguiente. 4. Aparece otra ventana, seleccione la pestaña Rango de datos, el apartado con este nombre, sirve para poner el rango de la tabla (normalmente ya aparece si ha ejecutado el paso 1), debajo, en Series en:, seleccione Filas. Ahora seleccione la pestaña Serie, el apartado con este nombre, es para si quiere añadir más productos (series), también aparecen los rangos de la tabla donde hay los datos del gráfico, déjelo tal como está y pulse Siguiente. 5. La ventana siguiente, lleva el nombre Opciones de gráfico. -En la pestaña Títulos, ponga el título del gráfico, Ventas Mensuales. Eje de categorías (X), ponga Meses, Eje de categorías (Y), ponga Cantidades. -En la pestaña Leyenda, active la casilla de verificación Mostrar leyenda. Ubicación, derecha. Pulse Siguiente. 6. La última ventana es para seleccionar donde quiere el gráfico, seleccione En una hoja nueva. Pulse Terminar. Tabla de valores.

Peras Patatas Cerezas Manzanas Melocotones Melón

Enero Febrero Marzo 2503 3941 4527 2263 4714 3370 2606 1795 1860 3912 4272 1997 4944 3277 1982 2583 3030 3854

Abril 2481 2722 3437 4917 4751 4310

Mayo Total 4146 17598 3229 16298 2312 12010 1898 16996 3421 18375 3482 17259

Gráfico 1.

Ventas Mensuales 5000 Cantidades

Pera 4000

Patata

3000

Cereza

2000

Manzana Melocoton

1000

Melon es

0 Enero

Febrero

Marzo

Abril

Mayo

Meses

Melvin Quiñones Aguilar 47

INFONET SRL

Dar formato a un gráfico. þ Cambie el color de fondo del título Ventas Mensuales, 1.

Con el puntero del ratón situado sobre el título, pulse el botón de la derecha, seleccione la opción Formato del título del Gráfico. O bien en la barra de gráfico, en la casilla de selección, escoja Título del gráfico. Y después pulse este botón.

2.

En la ventana que aparece, seleccione la pestaña Tramas, y en apartado Área, escoja el color. Pulse Aceptar.

þ Repita los mismos pasos para la leyenda. þ Inserte líneas de división vertical. 1. Haga clic con el botón secundario del ratón sobre cualquier área en blanco del gráfico para desplegar el menú de contexto. 2. Seleccione opción, Opciones de gráfico. 3. En la pestaña líneas de división, en apartado Eje de categorías(X), escoja Líneas de división principales. Pulse sobre Aceptar. þ Cambiar el tipo de gráfico. 1. Pulse el botón de la barra de gráfico,

y seleccione Gráfico de columnas 3D. (O bien en la opción de menú Gráfico/ Tipo de gráfico). 2. Haga clic sobre el gráfico, del menú de contexto, seleccione opción Vista en 3D. 3. En la ventana que se despliega, en apartado Elevación, ponga 45, en apartado Giro, ponga 330 y en Perspectiva, ponga 70, pulse Aceptar.

Ventas Mensuales

Peras Mel one Melocot ones Manz anas Cerez as Patat Peras

Patata ss Cerez as manz

Qu 5000 ans tit 0 at

anas Melocot ones Mel

Abril

one

Enero Meses

þ Vuelva a dejar el gráfico en 2 Dimensiones, Tipo Columnas.

Melvin Quiñones Aguilar 48

INFONET SRL

þ Cambie el orden de las series y superponga las columnas. 1. Haga clic con el botón secundario del ratón, sobre cualquier barra del gráfico. 2. Del menú de contexto seleccione opción Formato de series de datos. 3. De la ventana que es despliega, seleccione la pestaña Orden de las series, en el apartado con este nombre, escoja la serie Melocotón y haciendo clics en el botón situado al lado Mover hacia arriba, póngalo en la 1ra. posición. 4. Seleccione pestaña Opciones, en apartado Superposición ponga 30, y a Ancho de rango, 60. Pulse Aceptar. þ Cambie las marcas de graduación y los intervalos del eje Y. 1.

Haga clic con el botón secundario sobre los números que representan las Cantidades.

2.

Del menú de contexto, seleccione opción Formato de ejes.

3.

En la pestaña Tramas, en apartado Marca de graduación secundaria, escoja Cruzada.

4.

En la pestaña Escala, en Unidad mayor, ponga 1000 y en Unidad menor, 500. Pulse Aceptar.

þ Cambiar el diseño de una serie. 1.

Seleccione la barra que representa las Peras y haga clic con el botón secundario del ratón.

2.

En el menú de contexto seleccione la opción Formato de serie de datos.

3.

En la ventana que se despliega, seleccione la pestaña Tramas, en apartado Área, pulse el botón Efectos de relleno.

4.

Aparece una nueva ventana, seleccione la pestaña Trama y escoja un modelo de trama. En Primer plano, seleccione el color amarillo, en Segundo plano el color rojo. Pulse Aceptar.

5.

Vuelve a estar en la ventana Formato de serie de datos, pulse Aceptar.

Melvin Quiñones Aguilar 49

INFONET SRL

Ventas Mensuales 5000

Cantidades

4000 Melocoton 3000

Peras Patatas

2000

Ciruelas Manzana s Melon

1000

0 Enero

Febrero

Marzo

Abril

Mayo

Meses

Gráficos con dos escalas. Es aconsejable poner un segundo eje de ordenadas, cuando una serie del gráfico contiene valores muy por encima o muy por debajo del resto de las series. Asignando otro eje de ordenadas a esta serie, conseguirá que no se represente mucho más (o menos) destacada que el resto.

Ejemplo. En este ejemplo, hará un gráfico normal y añadirá después una segunda escala en la parte derecha del gráfico. 1.

Seleccione toda la tabla, incluida la columna de totales.

2.

Active opción Insertar /gráfico, seleccione el tipo Columnas, y en Subtipos el primer modelo. Ponga las series en columnas.

3.

Título del gráfico, Gráfico 2., Siguiente y cree el gráfico en una hoja nueva.

Compruebe que la barra que representa los totales, es mucho más grande que el resto. 4.

Seleccione la barra de los totales, active el menú de contexto o seleccione la opción Tipo de gráfico, escoja el tipo Líneas y el subtipo primero, compruebe que la opción Aplicar a la selección del apartado Opciones, esté activada. Pulse Aceptar.

5.

En gráfico, haga clic con el botón secundario del ratón y seleccione opción Opciones de gráfico. Active la pestaña Eje, en apartado Eje secundario, active la casilla de Eje de valores (Y). Pulse Aceptar.

Melvin Quiñones Aguilar 50

INFONET SRL

Cantidades

Gráfico 2

5000

20000

4500

18000

4000

16000

3500

14000

3000

12000

2500

10000

2000

8000

1500

6000

1000

4000

500

2000

0

Enero Febrero Marzo Abril Mayo Total

0 Peras

Patatas

Cerezas

Manzana

Melocoton

Melon

Productos

Ejemplo. En este otro ejemplo, el gráfico que se ha escogido ya lleva dos escalas. 1.

Seleccione toda la tabla, incluida la columna de totales.

2.

Active opción Insertar/Gráfico, de la ventana que se despliega, seleccione la pestaña Tipos personalizados.

3.

Active opción Integrado de apartado Seleccionar desde. En Tipo de gráfico, escoja Líneas y columnas 2. Pulse Siguiente. En rango de datos, elija, series en columnas y Siguiente.

4.

En la siguiente ventana, en la pestaña Título, ponga el título Gráfico 2 para el gráfico, Productos en Eje de categorías(X), y Cantidades en Eje de valores(Y). Pulse Siguiente.

5.

En última ventana ha de escoger la opción que hace que el gráfico se genere en una hoja nueva. Pulse Terminar.

Observe que la línea de totales, la muestra en la parte superior. 6.

Seleccione la línea de totales, haga clic con el botón secundario del ratón y del menú de contexto seleccione opción Tipo de gráfico.

7.

En la pestaña Tipos estándar, en Tipo de gráfico, seleccione Áreas, y en Subtipo el primer modelo. Compruebe que la opción Aplicar a la selección de apartado Opciones esté activada, si no es así, modificará todo el gráfico. Pulse Aceptar.

Melvin Quiñones Aguilar 51

INFONET SRL

Gráficos de sectores (circulares). Un gráfico de sectores, sirve para indicar porcentajes sobre un rango de valores. Con este tipo de gráficos, puede representar por ejemplo qué % de cada fruta sobre el total se ha vendido en Enero, o qué % del total de melocotones le corresponde a cada mes. Hará un gráfico de sectores que muestre qué % de cada fruta se ha vendido sobre el total. Para hacerlo, tendrá que seleccionar los rangos de los nomb res de las frutas (primera columna) y los totales vendidos (columna de totales). Per seleccionar rangos separados, siga los pasos siguientes: 1. Seleccione el rango de los títulos.(Nombres de las frutas). 2. Con la tecla de Control pulsada, seleccione el rango de los totales. Una vez marcados los rangos, siga los siguientes pasos, para hacer el gráfico. Tener en cuenta que tenemos que seleccionar el mismo número de casillas en cada rango para que el gráfico nos dé la información adecuada. 1.

Active opción Insertar/Gráfico, en Tipo de gráfico, seleccione Circular y en Subtipo el segundo modelo Circular con efecto 3D. Pulse Siguiente.

2. 3.

Esta ventana, en Rango de datos presenta el rango de datos con el cual hará el gráfico, en Series en, ponga Columnas. Pulse Siguiente. Ponga el título Porcentajes, en ficha Rótulos de datos, active las opciones Mostrar rótulo y porcentajes y Mostrar clave de leyenda junto al rótulo. No muestre la leyenda, pulse Siguiente.

4.

El gráfico ha de aparecer en una hoja nueva. Pulse Terminar.

Para destacar un sector del gráfico. Haga dos clics sobre el sector de Manzanas, y arrastre. Los dos clics no han de ser seguidos, el primer clic, selecciona el gráfico, y el segundo, el sector.

Melvin Quiñones Aguilar 52

INFONET SRL

Ejercicio . Con los valores de la tabla, Haga los gráficos siguientes. þ Primer gráfico, sin incluir la columna de totales. Tipo de gráfico, columnas, subtipo, columna 100% apilada. series en filas. þ Segundo gráfico, seleccione toda la tabla. Tipo de gráfico personalizados, Conos. Series en columnas. Título del gráfico, Turismo, título del eje X, Años y título de eje Y, Países. Color de fondo del gráfico amarillo, color de los títulos azul. * Para poner el título Países vertical; 1. 2.

Haga clic sobre el título con el botón secundario del ratón, escoja opción Formato del título del eje. En la pestaña Alineación, en apartado Orientación, seleccione el modelo donde pone Texto vertical, pulse Aceptar.

þ El tercero ha de ser un gráfico de porcentajes. Sobre la fila de países y la fila de 1995, no incluya la columna de totales. Tipo de gráfico, Anillos, subtipo el primero, series en filas.

España

Francia

Inglaterra

Alemania

Portugal

Total

1.990

7154151

1074063

1271878

9016046

5482563

23.998.701

1.991

9360633

3228135

6416386

7228462

8624220

34.857.836

1.992

5677212

9379538

2344678

5983030

8234124

31.618.582

1.993

1799978

2001732

3913061

1074206

2682260

11.471.237

1.994

2485965

6839122

9919728

6549779

3277171

29.071.765

1.995

7564013

1290994

7749636

6074951

1990117

24.669.711

Gráfico 1.

Melvin Quiñones Aguilar 53

INFONET SRL

Gráfico 2.

Gráfico 3.

Melvin Quiñones Aguilar 54

INFONET SRL

Tablas de una variable. Una tabla de una variable, consta de una columna o fila de valores y una o más fórmulas para aplicar a estos valores. Las tablas permiten actualizaciones rápidas de los resultados que se obtienen a partir de los valores, simplemente cambiando la fórmula que se les tiene que aplicar, la obtención de resultados es inmediata. Antes de realizar una tabla, aprenderá como se puede llenar un rango rápidamente con series de valores.

• Llenar rangos con series de valores. 1. En la primera casilla de la serie de valores, ponga el valor inicial, seleccione el rango de columna o fila que quiera llenar (incluida la casilla del valor inicial). 2. Active opción Edición/ rellenar/series. Se despliega el siguiente cuadro de diálogo.

Series En :

Escoger llenar filas o columnas.

Tipo :

El tipo de progresión.

Incremento :

El intervalo entre dos valores seguidos de la serie.

Unidad de tiempo : Forma de incrementar valores cronológicos. Límite :

Valor máximo de la progresión.

Ejemplo. 1. Sitúe el cursor en la casilla A1, entre un 0. 2. Seleccione el rango A1:A10 3. Active opción del menú Edición/ Rellenar/series. 4. En incremento, ponga 5. 5. Pulse sobre Aceptar.

Esta acción también se puede hacer con el ratón.

Ejemplo. 1. Sitúe el cursor en la casilla A1, entre un 0. 2. Sitúe el cursor en la casilla A2, entre un 5. 3. Seleccione las casillas A1 y A2. 4. Sitúe el puntero sobre el recuadro llenar y arrastre hasta A10.

Melvin Quiñones Aguilar 55

INFONET SRL * No cierre el documento, servirá para el próximo ejemplo.

• Construir una tabla de una variable. 1. Seleccione la casilla de la derecha de la tabla de valores, si estos están en una columna. La casilla de la fila inferior de la tabla de valores si están en una fila. 2. Teclee la fórmula; la fórmula ha de tener una referencia a la casilla donde hay el primer valor de la tabla. 3. Seleccione el rango que incluya, la columna o fila de valores, y la columna o fila con las fórmulas. 4. Active opción de la barra de menús Datos/ Tabla. 5. Se despliega un cuadro de diálogo. Introduzca la referencia de la casilla de la columna o fila de valores que ha utilizado en cuadro de edición Celda variable fila (si los valores están en una fila) o columna (si los valores están en una columna). 6. Pulse sobre Aceptar.

Ejemplo. 1. Sitúe el cursor sobre la casilla B1, teclee =A1*5. 2. Sitúe el cursor sobre la casilla C1, teclee =A1*13. 3. Seleccione el rango de la Tabla (A1:C10) 4. Active opción Datos/ Tabla. 5. En cuadro de edición Celda Variable Columna ponga A1. 6. Pulse sobre Aceptar. Cambie la fórmula de la casilla B1, ponga =A1*23. Observe como automáticamente se generan nuevos resultados a partir de esta fórmula.

Melvin Quiñones Aguilar 56

INFONET SRL BASE DE DATOS Una base de datos es una serie de registros de datos, organizados en filas y columnas. De manera que a una fila le corresponde un registro y a una columna le corresponde un campo de este registro.

Ejemplo de una base de datos .

Operaciones con una base de datos. •

Ordenar registros. Puede ordenar una base de datos, por uno o más campos (columnas).

Ejemplo. Ordene la base de datos por el campo Nombre. 1. Sitúe el cursor en la primera fila, primera columna de la tabla. 2. Active opción Datos/Ordenar. Se seleccionará toda la tabla. 3. Pulse sobre el botón Opciones, en apartado Orientación, seleccione Ordenar de arriba abajo. Pulse Aceptar. 4. En la ventana Ordenar, en primer apartado Ordenar por, escoja Nombre, active el botón de radio Ascendente. Pulse Aceptar. Si quiere ordenar por más de un campo a la vez, en apartado Luego por, escoja el segundo campo a ordenar. Una ordenación por dos campos significa que cada grupo de valores repetidos en el primer criterio, se tiene que ordenar por el segundo criterio, y así sucesivamente.

* Cuando pulse el botón Opciones, en apartado Orientación, hay otra opción, Ordenar de izquierda a derecha, esta opción ordenará las columnas, por la fila que se el indique en apartado Ordenar por de la ventana Ordenar. Ejemplo: þ Ordene la base de datos por los siguientes campos: . Por Edad. . Por Sexo y Edad. . Por Afición y Cuota. . Por Afición y fecha ingreso. . Por Fecha Ingreso y Cuota. . Per Sexo, afición y Cuota.

Melvin Quiñones Aguilar 57

INFONET SRL

•

Seleccionar registros. Una selección de registros, filtra los registros de la base de datos de manera que sólo se visualicen los que cumplan una determinada condición. Para hacer un selección o filtrado de registros, siga los pasos siguientes:

Ejemplo. Realice un filtrado que mu estre los registros que tienen una M en la columna sexo. 1. Sitúe el cursor en la primera fila, primera columna de la tabla. 2. Active opción Datos/ Filtro/ AutoFiltro. En cada columna aparecerá un botón con una flecha. 3. Haga clic sobre el botón de la columna Sexo, y seleccione la M. Verá que la tabla, sólo muestra las mujeres. Para quitar las condiciones de filtrado, vaya a opción Datos/Filtro/Mostrar todo, o bien en la lista que se despliega de cada fila, seleccione Todas. Para quitar el modo de filtrado automático,active Datos/ Filtro/ AutoFiltro. Per hacer un filtrado personalizado.

Ejemplo. Seleccione los registros que tengan en el campo cuota, valores comprendidos entre 3000 y 5000. 1. Active el filtrado automático. 2. Despliegue la lista de la columna Cuota, seleccione Personalizadas. 3. En la ventana que se activa, en el cuadro donde pone Nombre, seleccione de la lista es mayor que, y en el cuadro de al lado, ponga 3000. 4. Active el botón de radio de Y, y en el cuadro de debajo, seleccione es menor que, al lado, ponga 5000. Pulse Aceptar. Observe que sólo se muestran los registros que tienen una cuota entre 3000 y 5000. þ Haga las siguientes selecciones: . Aficionados al Baloncesto. . Hombres . Personas que paguen más de 4000 pesetas de Cuota. . Personas que tengan entre 20 y 35 años. . Mujeres aficionadas al baloncesto. . Hombres aficionados a la natación o al fútbol. . Hombres mayores de 30 años. . Hombres con una cuota entre 2000 y 5000. . Aficionados al Baloncesto que paguen una cuota superior a 4000. . Hombres aficionados al fútbol que paguen una cuota entre 5000 y 7000.

Utilización del filtro avanzado. Melvin Quiñones Aguilar 58

INFONET SRL

Ejemplo. Filtrará todos el que tengan 52 años. 1. 2. 3. 4. 5. 6. 7.

En la casilla A24, fuera de la tabla, ponga Edad, y en la casilla de debajo, A25, ponga 52. Active opción Datos/Filtro/Filtro avanzado. En apartado Acción, seleccione el botón de radio Copiar a otro lugar. Sitúe el cursor en cuadro de Rango de la lista, y con el ratón, seleccione todo el rango de la tabla, normalmente ya aparece seleccionado. Sitúe el cursor en cuadro de Rango de criterios, y con el ratón, seleccione la casilla de la A24 a A25. Sitúe el cursor en Copiar a y seleccione la casilla A26. Pulse Aceptar. Fíjese como aparece una tabla con todas las personas de la lista que tienen 52 años.

Ejemplo. Filtre los hombres con afición al fútbol. 1. En la casilla A24, ponga Sexo, debajo, H, en la casilla B24, ponga Afición y en la de debajo Fútbol. 2. Siga los mismos pasos , y en paso 5, ponga el rango de casillas de la A24 a la B25. Fíjese como mostrará una tabla con todos los que cumplen estas dos condiciones.

•

Cálculo de subtotales. Esta opción permite hacer cálculos con registros que tengan valores iguales en un campo determinado. Por ejemplo, saber cual es la media de edad de cada afición. Es muy importante que la tabla esté ordenada por la columna por la cual se quiere subtotalizar, de esta manera los valores repetidos de la columna estarán agrupados. Ejemplo. Encuentre la media de edad de cada afición. Antes, ordene la tabla por esta columna. 1. Sitúe el cursor en la primera fila, primera columna. 2. Active opción Datos/Subtotales. Aparece un cuadro de diálogo: 3. En apartado Para cada cambio en:, ponga Afición. 4. En apartado Usar función:, escoja la función Promedio. 5. En apartado Agregar subtotal a:, seleccione Edad. 6. Pulse sobre Aceptar. þ Calcule el total de cuotas por sexo. þ Calcule la media de edad per sexo. þ Calcule cuantos registros hay de cada afición. Utilice la función Cuenta sobre este campo. þ Calcule el total de cuota por afición. þ Calcule la media de cuota por afición. * Para quitar los subtotales, active opción Datos/Subtotales/Quitar Todos.

•

Gestión de una base de datos con la opción Datos/Formulario.

Melvin Quiñones Aguilar 59

INFONET SRL Esta opción permite una gestión rápida y sencilla de una base de datos. Para activar esta opción. 1. Seleccione la primera fila, primera columna de la base de datos. 2. Active opción de la barra de menú Datos/ Formulario. Aparece el cuadro siguiente,

Nuevo: Eliminar: Restaurar: Buscar anterior: Buscar siguiente: Criterios:

Inserta un nuevo registro a la base de datos. Elimina el registro activo. Cancela la última modificación. Busca el anterior registro que cumpla el criterio. Busca el siguiente registro que cumpla el criterio. Establece criterios de filtrado o selección de registros.

þ Pulse sobre el botón Nuevo y añada los registros siguientes. Indiana Jones Luke SkyWalker Eleanor Rigby

43 Natación H 32 Baloncesto H 55 Volei M

4500 3200 4300

5/05/96 6/07/95 7/07/95

þ Sitúese sobre el registro Luke Skywalker y bórrelo. þ Active opción Criterios y en campo edad ponga > 25. Pulse alternativamente sobre botones Buscar siguiente y Buscar anterior para comprobar que sólo se muestran los registros que tienen en el campo edad un valor superior a 25. þ Repita el ejercicio anterior con los criterios siguientes. . Aficionados al Baloncesto. . Hombres. . Personas que paguen más de 4000 pesetas de Cuota. •

Tablas dinámicas. Esta opción permite resumir y analizar rápidamente valores contenidos en una tabla o base de datos. Excel activa un asistente cuando se ejecuta esta opción.

Ejemplo. Melvin Quiñones Aguilar 60

INFONET SRL Construirá una tabla dinámica que muestre la edad media por sexo y afición. 1. Sitúe el cursor en la primera fila, primera columna de la base de datos. 2. Active opción del menú Datos/ Informe de tablas y gráficos dinámicos. Se despliega la primera ventana del asistente donde se pregunta sobre el origen de la base de datos, deje activada la opción Lista o Base de datos de Microsoft Excel , en la opción de ¿qué tipo de informe desea crear? Pulse, Tabla dinámica y pulse sobre Siguiente. 3. Ahora, el asistente pide que seleccione el rango que contiene los datos que se quieren utilizar. Como el rango ya aparece seleccionado, pulse sobre Siguiente. 4. Se le pide donde desea situar la tabla dinámica, lo mejor es situarla en una hoja de cálculo nueva. Para situar los elementos en la tabla dinámica, pulse el botón Diseño... Al pulsar el botón, parece una ventana con los nombres de los campos y la descripción de los elementos de la tabla. 5. Seleccione el campo Afición y arrástrelo hasta posición Fila de la tabla. 6. Seleccione campo Sexo y arrástrelo hasta posición Columna de la tabla. 7. Seleccione campo Edad y arrástrelo hasta posición Datos de la tabla. 8. Seguramente en Datos aparecerá Suma de Edad, para cambiar por Promedio de Edad, haga doble clic sobre Suma de Edad, se desplegará un cuadro con una lista de funciones, seleccione Promedio y pulse Aceptar para volver al asistente, pulse sobre Aceptar. 9. Es interesante, hecharle un vistazo al contenido del botón Opciones... ya que aquí podemos decirle, entre otras cosas, el contenido de las celdas vacías y celdas con resultado erróneo. 10. Ahora, después de mirar este botón, ya puede finalizar el asistente para tablas dinámicas. Excel ha generado la siguiente tabla que muestra la media de edad por sexo y afición. Promedio de Edad Afición Baloncesto Bolos Fútbol Natación Volei TOTAL

Sexo H

D TOTAL 52,5 35,33333333 42,2 55 14 34,5 37 37 32,33333333 42 36,2 35,66666667 42 37,25 39,21428571 35,14285714 37,85714286

Operaciones con una tabla dinámica. Cuando se genera una tabla dinámica, Excel muestra una barra de herramientas para poder realizar diferentes operaciones con los datos obtenidos de la tabla. þ Construya una tabla dinámica que muestre la suma de cuotas por afición y sexo. þ Haga una tabla dinámica que muestre la media de edad por Cuota y Sexo.

Melvin Quiñones Aguilar 61

INFONET SRL

Algunas Funciones Estadísticas. =CONTAR(Rango de casillas). Cuenta la cantidad de casillas numéricas que hay en el rango. =CONTARA(Rango de casillas). Cuenta la cantidad de casillas alfanuméricas del rango indicado. =MODA(Rango de casillas). Devuelve el valor más común del rango. =PROMEDIO(Rango de casillas). Devuelve la media de los valores del rango de casillas indicado.

Algunas Funciones de bases de datos. =BDCONTAR(Rango o nombre de la tabla;Número de columna;Criterio). Rango o Nombre de la tabla= Rango de casillas o nombre de la tabla. Número columna= Columna donde se encuentran los datos sobre los que se realiza la operación. Criterio= Rango donde se encuentra el criterio,(previamente indicado en forma de tabla).

Cuenta el número de casillas de la columna Número de columna de tabla que cumplen la condición de criterio. =BDSUMAR(Rango o nombre de tabla;Número de columna;Criterio).

Suma las casillas numéricas de la columna Número de Columna de tabla, que cumplan la condición del criterio. =BDPROMEDIO(Rango o nombre de tabla;Número Columna;Criterio).

Calcula la media de los valores de la columna Número columna de tabla, que cumplan el criterio. =BDMAX(Rango o nombre de tabla;Número Columna;Criterio).

Devuelve el valor máximo de la columna Número columna indicada de tabla, que cumpla la condición del criterio. =BDMIN(Rango o nombre de tabla;Número Columna;Criterio).

Devuelve el valor mínimo de la columna Número columna de tabla, que cumpla la condición del criterio. • Criterio, son siempre como mínimo dos casillas, una que es el nombre de la columna y otra que es la condición que se ha de cumplir. • Número Columna es la posición que ocupa una determinada columna dentro de la tabla. Por ejemplo en ejercicio que se verá posteriormente, Lenguaje ocupa la columna cuatro.

Melvin Quiñones Aguilar 62

INFONET SRL

• Ejercicio. En la base inferior, haga las operaciones siguientes: þ Ponga nombre a la tabla, no hace falta incluir la columna Media. þ Contar el nº de exámenes de todas las asignaturas, función =CONTAR. þ Contar nº de alumnos, función =CONTARA. þ Mostrar las notas más comunes de cada asignatura, función =MODA. þ Calcular la media de cada asignatura, función =PROMEDIO.

Nombre Txetxu Itziar Prudencia Leocadia Estrellita Romualdo Godofredo Sigfrido Gumersindo Leovigildo Heladio Urko Higinia Teodófilo Macarena Quintiliano Romualdo Ataulfo Demetrio Abunciana Hug Idoia

Apellidos Arazabaleta Bengoetea Gara Garcia Gutiérrez Gutiérrez Martineti Martínez Martínez Mastuerzo Parralo Pérez Pérez Peritarpei Pitu Sánchez Santemesmases Segarra Sinforoso Torrelles Urdiales Zumalacarregi

Media General

Clase A B C B C B A C C B A A A A B C B A C A B C

Lenguaje

Lenguaje 6,1

Nº Exámenes Nº Alumnos Notas más Comunes

Matemáticas Historia Media 5 7 9 7,00 7 5 5 5,67 3 8 5 5,33 6 5 8 6,33 6 6 7 6,33 8 4 6 6,00 7 5 6 6,00 5 7 8 6,67 7 6 5 6,00 8 9 8 8,33 6 3 9 6,00 7 4 5 5,33 3 4 7 4,67 6 3 9 6,00 6 5 6 5,67 3 8 5 5,33 9 3 7 6,33 5 5 6 5,33 7 7 3 5,67 8 7 3 6,00 8 6 7 7,00 4 6 8 6,00

Matemáticas Historia 5,6 6,5

66 22 Lenguaje

Matemáticas 7

Historia 5

5

Melvin Quiñones Aguilar 63

Resolución de Problemas. Buscar Soluciones. Buscar objetivo. Con esta herramienta Excel ajusta automáticamente el valor de una casilla para obtener un resultado determinado en otra. Evidentemente, la casilla donde quiera obtener el resultado ha de depender directamente o indirectamente de la casilla a la cual se le ajusta el valor. La casilla que cambia ha de contener obligatoriamente un número, no se puede utilizar una casilla con fórmula.

Ejemplo. Copie la tabla siguiente. Total Compra, Total Venta y Beneficios se han de calcular.

Calcular que cantidad se necesitaría para obtener unos beneficios de 55.000 pesetas. 1. Active opción Herramientas/ Buscar Objetivo. 2. En el cuadro de diálogo que aparece, en caja de texto Definir la celda: ponga la casilla en la que quiera un determinado resultado, en este caso la $B$6, Beneficios. En este apartado, sólo se pueden utilizar casillas que contengan fórmula. 3. En la siguiente caja de texto, Con el valor: ponga el valor que quiera obtener, 55.000. 4. Cambiando celda: esta caja de texto sirve para definir que casilla se puede ajustar para conseguir el resultado en la casilla indicada en Definir la celda. Recuerde que aquí sólo puede indicar casillas con valores numéricos y que intervengan directa o indirectamente en el resultado final. Ponga la casilla donde hay la cantidad D2. 5. Pulse sobre Aceptar. Excel ajusta el valor de la casilla D2 para que en la casilla B6 se obtenga 55.000.

þ Calcule que precio de venta, tendría que haber para conseguir un beneficio de 120.000 pts. þ Calcule a que precio se tendría que comprar, para obtener unos beneficios de 90.000 pts.

• Para hojas con gran cantidad de fórmulas, puede ser de gran ayuda utilizar las herramientas de Auditoría. De esta manera verá rápidamente de que casillas depende la casilla en la que quiere obtener el resultado.

Resolución de Problemas. Buscar Soluciones. 64

Resolución de Problemas. Buscar Soluciones. • Ejercicio. Recupere el archivo OBJETIVO1.XLS

Cálculos. Total Ventas. Total Compras. Total Salarios. Beneficios.

Ventas * Precio Venta. Compras * Precio Compra. Trabajadores * Salario. Total Ventas - (Total Compras + Total Salarios).

þ Utilice la opción Buscar Objetivos para calcular unos beneficios de 450.000, cambiando: . Salario . Cantidad de Venta . Precio Venta Recuerde volver a poner el valor original en la casilla variable antes de hacer el siguiente cálculo. Por ejemplo, antes de definir como variable la casilla Cantidad de venta, vuelva a poner el valor 130.000 en la casilla Salario. Ventas 25000 Total Ventas Total Compras Total Salario Beneficios

Compras 7900 5500000 1185000 3900000

Precio Venta

Precio Compra Trabajadores 220

150

30

Salario 130000

415000

Resolución de Problemas. Buscar Soluciones. 65

Resolución de Problemas. Buscar Soluciones. Solver Solver, es parecido a Buscar Objetivo, se utiliza también para obtener un determinado resultado en una casilla. Esta herramienta, permite establecer más de una casilla ajustable. También permite establecer restricciones, con esto, se indica a Solver que cuando haga los ajustes en las casillas variables, se ha de limitar a las condiciones establecidas en cada restricción. De la misma manera que en Buscar Objetivo, las casillas variables han de contener valores numéricos y han de intervenir directa o indirectamente en la fórmula de la casilla donde se quiera obtener el resultado final. Para utilizar este método, siga los pasos siguientes: 1. Active opción Herramientas/Solver, se desplegará este cuadro:

Celda objetivo: Casilla donde se quiere el resultado. Máximo: Que obtenga en Celda Objetivo el valor máximo posible. Mínimo: Que obtenga en Celda Objetivo el valor mínimo posible. Igual a: Valor que se desea obtener. Cambiando las celdas: Casillas que pueden variar su contenido para obtener el valor buscado en Celda Objetivo, si hay más de una casilla, se separan con un punto y coma. También se puede definir un rango con la notación Casilla Inicial:Casilla final. Estimar: Si se activa esta opción, Excel utilizará todas las casillas sin fórmula que impliquen el resultado de Celda Objetivo. Sujetas a las restricciones: En este apartado, se agregan las condiciones que ha de respetar Solver a la hora de hacer los ajustes en las casillas variables. 2. Después de llenar los apartados anteriores, pulse el botón Resolver. 3. Aparecerá otro cuadro que muestra los resultados calculados por Solver, pulse sobre Aceptar si quiere conservar este resultado, Cancelar para que vuelvan los valores originales.

Resolución de Problemas. Buscar Soluciones. 66

Resolución de Problemas. Buscar Soluciones. • No siempre será posible encontrar una solución.

Ejemplo: Copie o recupere el ejercicio OBJETIVO1.XLS. Se tiene que conseguir unos beneficios de 550.000 pts., variando las siguientes casillas: . Precio Comp ra . Trabajadores . Salario Y poniendo las restricciones siguientes: Precio Compra, >=120 y =28 y =120.000 y =2 Pizzas, Hamburguesas, >=2 Total Semanas , >=45 y 8 se pagará a 825 sino se pagará a 800. Si Número Personas > 20 cada una recoge 20 Kg. Sino recoge 30 Kg. Personal * Horas Diarias * Kg. Recogidos Persona/ Hora.

Días Recogida. 1ª Pasada

Kg. Previstos 1ª Pasada/ Total Kg. Día.

2ª Pasada

Kg. Previstos 2ª Pasada/ Total Kg. Día

Total Kg. Perdidos Día. 1ª Pasada

1ª Pasada + 2ª Pasada Sí Días Recogida > 7 Pérdida del 0,8% de los Kg. Recogidos por día. Sino un 0,5% de los Kg. Recogidos por Día.

2ª Pasada Si Días Recogida > 4 Pérdida del 3% de los Kg. Recogidos día Sino 5% de los Kg. Recogidos Día. Total Kg. Perdidos. 1ª Pasada.

Kg. Perdidos día 1ª Pasada * Días Recogida 1ª Pasada.

2ª Pasada.

Kg. Perdidos día 2ª Pasada * Días Recogida 2ª Pasada.

Resolución de Problemas. Buscar Soluciones. 70

Resolución de Problemas. Buscar Soluciones. Ingresos 1ª Pasada

(Kg. Recogidos 1ª Pasada - Total Kg. Perdidos 1ª Pasada) * Precio Kg. 1ª Pasada.

2ª Pasada

(Kg. Recogidos 2ª Pasada - Total Kg. Perdidos 2ª Pasada) * Precio Kg. 2ª Pasada.

Total Total Horas Total Gastos Beneficios

Ingresos 1ª Pasada + Ingresos 2ª Pasada. Personal * Total Días Recogida * Horas diarias Total Horas * Precio Hora Total Ingresos - Total Gastos.

þ Utilice la función =SI en precio hora para determinar si se paga a 800 o a 825. þ Utilice la función =SI en Kg. Recogidos Persona/Hora. þ Utilice la función =SI en Kg. Perdidos día. þ Utilice el solver para calcular el valor máximo en beneficios. Las restricciones son las siguientes. . Personal contratado tiene que estar entre 15 y 23 persones. . El total de días a recoger tiene que estar entre 15 y 20. . Las horas diarias pueden ser 8 o 9. Las casillas variables son. . Personal contratado. . Horas diarias.

Campaña Frutera Kg. Previstos 1ª Pasada 2ª Pasada

Personal

Precio Hora 800

30

Kg. Recogidos Día 4080

Días de recogida 1ª Pasada 13,4803922 2ª Pasada 5,6372549 Total Días 19,1176471

3546400 780850 4327250

65 35

Horas Diarias

17 Kg. Recogidos Persona/Hora

Ingresos 1ª Pasada 2ª Pasada Total

Precio Kg. 1ª Pasada 2ª Pasada

55000 23000

8

Kg. Perdidos Día Total Kg. Perdidos 32,64 440 122,4 690

Total Horas Total Gastos 2600 2080000

Resolución de Problemas. Buscar Soluciones. 71

Resolución de Problemas. Buscar Soluciones. Beneficios

2247250

Resolución de Problemas. Buscar Soluciones. 72