I.E.S. María de Molina. Apuntes y ejercicios de OpenOffice Calc Avanzado.
A Appuunntteess yy eejjeerrcciicciiooss ssoobbrree C Caallcc A Avvaannzzaaddoo
Ordenación En Calc, puede utilizarse fácilmente una lista como una base de datos y se pueden ejecutan tareas en la base de datos, como búsquedas, clasificaciones o datos subtotales. Una base de datos es un conjunto de datos que pertenecen a una misma entidad, objeto o individuo. Cada fila es un registro y cada columna un campo. Los datos de la misma fila, que son un registro, pertenecen a la misma persona. Cada columna es un campo, contiene todos los datos necesarios para formar un registro y permite clasificarlos y ordenarlos. En el ejemplo tenemos la hoja de cálculo “listas y ordenación.odt”
Para ordenar datos nos colocamos en la celda A1 y utilizamos del menú Datos -> Ordenar. En las caja de Ordenar según y Después según se selecciona de la lista, el nombre del campo con el que se quiere ordenar según la prioridad. Calc permite manejar hasta tres prioridades. Cada criterio se puede ordenar Ascendente o Descendente. En la pestaña Opciones podemos definir un orden específico y opciones adicionales para el orden. Se puede determinar que sea sensible a las letras mayúsculas en mayúsculas / minúsculas e incluso personalizar el orden.
Página 1 de 10
I.E.S. María de Molina. Apuntes y ejercicios de OpenOffice Calc Avanzado.
El resultado de ordenar los datos del ejemplo anterior, si le indicamos al Calc que los ordene por el campo Producto sería el siguiente:
1.- a) Ordena la hoja “listas y ordenación.ods” por productos y después por ventas. Guarda el resultado en una hoja nueva que se llame producto-ventas.
b) Ordena la hoja “listas y ordenación.ods” por vendedor, después por producto y después por meses de manera descendente. Guarda el resultado en una hoja nueva que se llame vendedorproducto-mes. Guárdalo como ejercicio1.
Filtros Una de las operaciones más comunes que se llevan a cabo con las bases de datos es la consulta. Cuando se hace una consulta, normalmente se están buscando registros que coincidan con algún valor o criterio. Existen varias formas de buscar esa información, los Filtros son una de ellas. Para aplicar un filtro seleccionamos los datos de la hoja y vamos al menú Datos -> Filtro -> Filtro automático... El resultado será similar al siguiente:
El comando Filtro automático aplica flechas para abrir menús desplegables en el encabezado de la base de datos. Con estos menús, se pueden elegir las opciones de consulta. Según el campo, aparecen las posibles combinaciones de los registros que se pueden buscar. Al seleccionar Filtro ~predeterminado aparece la siguiente caja de diálogo:
Página 2 de 10
I.E.S. María de Molina. Apuntes y ejercicios de OpenOffice Calc Avanzado.
En Condición se pueden determinar que registros exactamente se están buscando. Se pueden utilizar operadores de comparación que se encuentran en la lista. Los botones de los campos donde se establecieron filtros y los encabezados de las filas cambian de color y se ponen azules. En cada uno de los campos se pueden establecer filtros y en este caso se suman los criterios, es decir que se debe de cumplir con cada uno de los criterios que se marquen en los filtros de los campos. Para deshabilitar todos los filtros, seleccione en el menú Datos -> Filtros -> Ocultar Autofiltro.
2.- En el archivo Editorial Juventud.ods filtra los libros para obtener: a) Los libros de precio menor de 13,60 € y escritos por Helen Cooper. Guarda el resultado en una hoja nueva que se llame menos de 13,60-Helen Cooper. b) Los libros que tienen en el apartado Título y autor la palabra “viaje”. Guarda el resultado en una hoja nueva que se llame viaje.
Subtotales Calc puede resumir datos automáticamente, calculando valores de subtotales y de totales en una lista. Para usar los subtotales automáticos, la lista debe contener columnas rotuladas y debe estar ordenada por las columnas para las que desee calcular los subtotales. Se accede en Datos -> Subtotales…
Cuando se insertan subtotales automáticos, Calc esquematiza la lista agrupando las filas con detalles con la fila de subtotal asociada, y agrupando las filas de subtotales con la fila del total general. Se pueden hacer grupos sucesivo en Grupo 2, Grupo 3… Puede elegir la función de Calc que desee para calcular los totales. En este ejemplo se utiliza la función Suma para calcular los subtotales por Vendedor y el total general de toda la lista.
Página 3 de 10
I.E.S. María de Molina. Apuntes y ejercicios de OpenOffice Calc Avanzado.
3.- Abre el ejercicio 3.ods Apartado 1: La hoja2 contiene una base de datos de facturas de un comercio de electrodomésticos. Cada registro corresponde a un electrodoméstico comprado por un cliente, un determinado día. Como un cliente puede comprar varios electrodomésticos ese día, a cada factura le pueden corresponder varios registros. Los campos serán los siguientes: �Número (de factura): año seguido de un guión y de un entero. �Fecha. �Cliente: nombre y apellidos del cliente. �Artículo. �Precio. �Unidades. �Subtotal, IVA (18%) y Total (campos a calcular). La resolución de los ejercicios de cada uno de los siguientes apartados se debe copiar en una hoja nueva de nombre igual a la letra del apartado. Apartado 2 (Ordenación): A la base de datos de la (HOJA2), ordenarla de las siguientes formas: a) Por cliente y para cada cliente por artículo (en ambos casos alfabéticamente A a Z). b) Por artículo, por cliente y por fecha (de más reciente a más antiguo). c) Por número (de mayor a menor), por cliente y por artículo. d) Por total, por artículo y por fecha. Apartado 3 (Filtros): Para la base de datos de la tienda de electrodomésticos crea los filtros para obtener la siguiente información: e) Facturas en las que se vendió una "Nevera c/frizer Whirlpool 360 ltr.". f) Las diez ventas de mayor precio. g) Las ventas de mayo de artículos de la marca Liliana. h) Las ventas de abril de artículos que no sean de BGH. i) Las ventas de la primer quincena de mayo de artículos de la marca BGH. j) Las ventas en mayo de microondas o calefactor de más de 300 euros. Apartado 4 (Subtotales): Los subtotales deben ser para los campos Subtotal, IVA y Total. Para la base de datos de la tienda de electrodomésticos crea los siguientes listados: (Cada filtrado se debe copiar en una hoja nueva de nombre según la letra del apartado) k) Ventas por fecha y por cliente para cada fecha, con subtotales por fecha y por cliente. l) Ventas por cliente y por fecha para cada cliente, con subtotales por cliente y por fecha. m) Ventas de cada artículo (orden alfabético) y para cada artículo de cada fecha, con subtotales por artículo y por fecha n) Ventas de cada artículo (orden alfabético) y para cada artículo de cada cliente, con subtotales por artículo y por cliente. Página 4 de 10
I.E.S. María de Molina. Apuntes y ejercicios de OpenOffice Calc Avanzado.
Búsqueda del valor destino En el caso de que se conozca el resultado deseado de una fórmula sencilla, pero no la variable que determina el resultado, se puede utilizar la función Herramientas -> Búsqueda del valor destino… Al realizar una búsqueda de destino, Calc varía el valor de la celda específica hasta que una fórmula dependiente de dicha celda, devuelve el resultado deseado. Por ejemplo tenemos la siguiente hoja para el cálculo de las cuotas de pago de un coche:
=B1 - B2
Si lo que queremos saber es el anticipo que debemos pagar para que nos quede una cuota de 2.500 €; tendríamos que seleccionar las celdas B1:B5 y Herramientas -> Búsqueda del valor destino… en la pantalla que obtenemos ya nos sale la Celda de fórmula
=B3 / B4
rellena. Indicaríamos el valor de destino que deseamos (2.500 €) y la celda que debe variar (en nuestro caso B2). Finalmente aceptamos en la pantalla que sale:
El resultado que obtendríamos sería:
4.- El proceso de corte y manipulación de las telas en una fábrica de vestidos ocasiona una perdida del 15% de la tela. Se quiere calcular cuantos metros cuadrados de tela harán falta para obtener al final 15.200 m2 de tela útil.
Solver Calc ofrece una herramienta para la toma de decisiones denominada "Solver". Esta herramienta se basa en un cálculo matricial (programación lineal) en el que multiplica valores para posteriormente sumarlos. Se aplicará esta herramienta a un ejemplo sencillo. El ejemplo será la empresa Muebles García que fabrica muebles. El beneficio neto de un lote de sillones Inca (I) es de 670 € y de sillones Plasencia (P) 1123 €. El proceso de montaje de ambos modelos pasa por dos áreas del Dpto. de Producción: Área "A" responsable de limpieza, clasificación y corte de la madera reciclada. Área "B" encargada del armado de estructura, esterilización de la madera y tapizado. El Área "A" dispone de 150 horas mensuales. Cada lote de "I" utiliza 10 horas de esta área, y cada lote de "P" 15 horas.
Página 5 de 10
I.E.S. María de Molina. Apuntes y ejercicios de OpenOffice Calc Avanzado.
El Área "B" tiene una disponibilidad de 160 horas mensuales. Cada lote del modelo "I" precisa de 20 horas y cada lote del modelo "P" precisa de 10 horas para el armado final. Para la producción de "I" y "P", se deberán utilizar al menos 135 horas destinadas al control de calidad en el próximo mes; el modelo "I" necesita de 30 horas y "P" 10 horas. El Dpto. de Producción ha decidido que será necesario producir un modelo "P" por cada tres de "I". Para maximizar los beneficios de la empresa, los Administradores de Muebles García tienen que usar de manera óptima los recursos disponibles para producir cada uno de los modelos. La función objetivo es maximizar el beneficio: Max 670 I + 1123 P. El beneficio depende de: Horas del Área "A": 10 I + 15 P ≤ 150 Horas del Área "B": 20 I + 10 P ≤ 160 Horas de control de calidad: 30 I + 10 P ≥ 135 Un modelo de "P" por cada tres de "I": I - 3 P = 0 No se pueden producir cantidades negativas: I ≥ 0; P ≥ 0 A continuación sobre la hoja de cálculo introducimos los datos indicados arriba. La hoja deberá quedar como se muestra a continuación:
En la celda A2 ingresar en la barra de formulas =B4*B5+C4*C5 En la celda D7 introducir la siguiente formula en la barra de formulas: =SUMA.PRODUCTO(B$5:C$5;B7:C7) Entre "B" y 5 se coloca el signo peso ($) para que Solver tome como referencia fija siempre la celda B5. Proceder de igual manera en C5. Arrastrar la celda D7 hacia el rango D8:D10 para copiar la formula. A continuación seleccionar todo y vamos a la barra de menú: Herramientas -> Solver… Rellenamos la pantalla que sale con las restricciones que hemos querido poner y damos a Solucionar. Así nos sale una pantalla con el resultado. Si damos Mantener resultados nos cambiaría la hoja de Cálculo con los valores que ha obtenido. Página 6 de 10
I.E.S. María de Molina. Apuntes y ejercicios de OpenOffice Calc Avanzado.
La solución que maximizaría los beneficios sería (redondeando los valores si es necesario) 7 lotes del modelo Inca y 2 del Plasencia.
5.- Una fábrica de coches fabrica dos modelos: Diablo (D) y Furia (F) Los beneficios por cada coche Diablo vendido son 1.800 € y por cada Furia 1500 €. El modelo D necesita 4 horas de fabricación y el F 2 horas. Se dispone en la fábrica de 1200 horas de fabricación El modelo D se tarda 3 horas en montar y el F 4 horas. Disponen de 1500 horas de montaje En Pintura y remates tardan: el D 4 horas y el F 2 horas. Se dispone de 1800 horas. Como mínimo hemos de fabricar 3 coches del tipo F por cada 1 de D porque se vende más. Calcula en Calc y con la orden Solver la solución óptima del número de coches de cada clase que se deben fabricar para sacar el máximo beneficio posible. Repite (en la misma hoja de cálculo) los cálculos pero sin tener en cuenta la última restricción. Es decir que se pueden construir los coches que se quiera de D y F.
Piloto de datos El piloto de datos es un asistente de Calc que genera una tabla resumen compacta a partir de grandes listados de datos. Una hoja de cálculo creada a través del Piloto de datos será una hoja de cálculo interactiva ya que permitirá que los datos se puedan organizar y agrupar conforme distintos criterios. Partamos de la siguiente tabla de datos de ejemplo: Producto
Marca
Almacén
Stock
Servilletas
Colhogar
1
40
Pañuelos Papel
Colhogar
1
50
Servilletas
Colhogar
2
150
Pañuelos Papel
Colhogar
2
120
Ambientador hogar
Brise
1
40
Ambientador coche
Brise
1
30
Ambientador hogar
Brise
2
90
Ambientador coche
Brise
2
50
Página 7 de 10
I.E.S. María de Molina. Apuntes y ejercicios de OpenOffice Calc Avanzado.
El piloto de datos es realmente útil cuando tenemos datos repetidos susceptibles de servir de criterio de agrupación. Volviendo a nuestro ejemplo tenemos un fragmento de la tabla que contiene los productos que tenemos en stock, la marca a la que pertenecen, su ubicación y la cantidad de unidades. Vamos a crear el piloto de datos paso a paso: 1. Comenzaremos por seleccionar toda el área de datos. 2. En el menú seguiremos Datos -> Piloto de datos -> Inicio… 3. Se nos abre una ventana de diálogo en la que encontramos los encabezados de las columnas y 4 áreas: a) Campos de Página. b) Campos de Filas. Criterio de agrupación que se representará en filas. c) Campos de Columnas. Criterio de agrupación que se representará en columnas. d) Campos de Datos. Datos que serán representados, es en esta zona donde se realizan cálculos. 4. Arrastramos los encabezados de columna a cada una de las zonas en función de nuestros criterios de agrupación. Si hacemos doble clic sobre uno de los encabezados arrastrados se nos mostrarán los cálculos que se pueden realizar. Como criterio de agrupación para las filas utilizaremos “Producto”, por lo que arrastraremos este encabezado a la zona “Campos de filas”. Como criterio de agrupación para las columnas utilizaremos “Almacén”, por tanto, arrastramos “Almacén” dentro de la zona “Campos de Columnas”. “Stock” será arrastrado a la zona “Campos de Datos”. Por último, Marca será arrastrada a la zona de “Campos de Página” Si hacemos doble clic sobre “Stock” se abrirá una ventana en la que podemos comprobar que la función que está seleccionada es “Suma”. Podríamos estar seleccionando otra función o bien, manteniendo pulsada la tecla Ctrl seleccionar más de una. El objeto de este piloto de datos es el de darnos los totales de productos que tenemos en stock por almacén y en el podemos decidir si nos dan los datos de las marcas en total o marca por marca.
Página 8 de 10
I.E.S. María de Molina. Apuntes y ejercicios de OpenOffice Calc Avanzado.
Además, obtenemos los totales por almacén y el total de productos en stock en el año. 6.- Abre el archivo “Piloto de datos.ods” y utiliza el piloto de datos para obtener los totales de gasto como se ve en la imagen siguiente:
Guárdalo como ejercicio 6.ods
7.- Repaso nombres, funciones y piloto de datos Abre el ejercicio 7.ods y realiza lo que pide en la hoja enunciado.
Macros Del mismo modo que utilizamos el formateado automático para aplicar una estética determinada a un área de datos sin necesidad de tener que dar los pasos uno a uno, Calc nos permite guardar una serie de pasos que se ejecutarán de una sola vez con el consiguiente ahorro de tiempo para nosotros. La ejecución automática de una secuencia de acciones recibe el nombre de Macro. Supongamos que continuamente creamos diagramas que recogen datos para los 7 días de la semana, pues bien, esto supone que cada vez que llevamos a cabo esta tarea debemos escribir los días de la semana, aplicarle un formato y por último insertar el gráfico. Vamos a grabar una macro que reunirá estos 3 pasos en 1 solo. 1. En primer lugar, en el menú principal elegimos Herramientas -> Macros -> Grabar macros. Página 9 de 10
I.E.S. María de Molina. Apuntes y ejercicios de OpenOffice Calc Avanzado.
2. Una vez hecho esto, el único cambio que apreciaremos es que en nuestra hoja de cálculo ha aparecido una pequeña ventana flotante que contiene un botón “Finalizar grabación”. 3. Ahora llevaremos a cabo los pasos siguientes a modo de ejemplo: escribir los días de la semana, dar formato e insertar diagrama. Cuando hayamos completado los 3 pasos pulsaremos el botón “Finalizar grabación”. 4. Ahora deberemos guardar nuestra nueva Macro en la biblioteca de macros de Calc. Para ello, solo tenemos que darle un nombre identificativo y pulsar el botón “Guardar”. Ahora nos queda ver como ejecutar la macro que acabamos de grabar: 1. En el menú principal damos los siguientes pasos: Herramientas -> Macro -> Ejecutar Macro… 2. Se nos volverá a abrir la ventana de “Macro”. En ella elegiremos la macro que vayamos a utilizar y pulsamos el botón “Ejecutar”. 3. Se ejecutarán (de una vez) todas las acciones que grabamos anteriormente. También podemos condicionar la ejecución de una macro a un evento (acontecimiento) dentro de Calc, con lo que ni siquiera tendremos que ejecutar nosotros la macro ya que lo hará automáticamente. Por último veremos cómo asignar una macro al teclado, creando un atajo y evitando así tener que recorrer los pasos del menú. 1. Vamos al menú Herramientas -> Personalizar… 2. Elegimos la pestaña teclado 3. En primer lugar nos encontramos con los accesos directos desde teclado ya asignados. 4. Buscaremos uno que se encuentre libre si no queremos modificar ninguno de los ya existentes, por ejemplo Ctrl+Mayúsculas+Insert, y lo seleccionamos. 5. Dentro de área buscamos el módulo dentro de la librería en la que se encuentre nuestra macro. 6. En función seleccionamos la macro que vaya a ser asignada. 7. Para finalizar pulsamos el botón “Modificar” y la macro ya queda asignada a esa combinación de teclas. 8.- Macros Abre el ejercicio 8.ods y realiza lo que pide en la hoja enunciado.
Página 10 de 10