OpenOffice.org Calc (la hoja de cálculo)

Cuestiones específicas de Calc

Cambiar el orden de las hojas en un cuaderno de cálculo

Para cambiar el orden de las hojas en un cuaderno de cálculo, siga estos pasos:

  1. Seleccione la hoja que desea mover.
  2. Posiciónese sobre la pestaña de la hoja y haga clíck con el botón derecho del ratón, seleccione la opción del menú contextual 'Desplazar/Copiar hoja...'
  3. En la ventana de diálogo que aparece, asegúrese de que la opción 'Copiar' no está seleccionada.
  4. seleccione la hoja que estará después de la hoja que esta apunto de mover, si desea mover la hoja a la última posición seleccione la opción 'Desplazar a la última posición'. (Como puede darse cuenta este método también sirve para copiar una hoja a otro documento de una maneras más profesional que un simple Ctrl+C y Ctrl+V).
  5. Haga clíck en 'Aceptar'

Más fácil aún: seleccione y arrastre la pestaña con el nombre de la hoja.

Usted también puede renombrar y borrar una hoja con las opciones de la pestaña.

Emulando una función CONTAR.UNICOS() mediante fórmulas matriciales

En Calc (al igual que en Excel) hay muchas funciones para contar elementos de un rango: CONTAR(), CONTARA(), CONTAR.SI(), etc, pero ¿no habéis encontrado a faltar un CONTAR.UNICOS() que os permita obtener el número de elementos sin repeticiones en un rango dado?

Esto lo podremos emular fácilmente mediante el uso de fórmulas matriciales.  Podéis ir directamente al final del artículo para ver la fórmula que hay que usar, pero como este artículo tiene finalidad didáctica, os cuento con un poco de detalle como se llega a la formulación adecuada.

Fórmulas matriciales... (Hoygan! y ezo ke es?) Risa

Las fórmulas matriciales son cálculos introducidos de un modo especial (con Ctrl+Mayúsc+Intro) que permiten aplicar una sóla fórmula a todo un bloque de celdas.  Podéis identificar que en una hoja se está usando una fórmula matricial cuando en la barra de fórmulas veáis que la fórmula aparece entre llaves: {=la_fórmula_que_sea}. Algunas funciones como la función FRECUENCIA() exigen ser introducidas como fórmula matricial.  Si aplicamos una función matricial a todo un rango de celdas, no se puede modificar la fórmula individualmente en ninguna de las celdas, por lo que, en cierto modo, tiene una función de protección frente a errores.  

Sintetizando un poco las ventajas de uso de las fórmulas matriciales:

  • Permiten hacer operaciones en bloque con grandes rangos de celdas.
  • Ahorran memoria (ocupan más tamaño mil celdas con una fórmula cada una, que una sóla fórmula matricial que opera con las mil celdas)
  • Y permiten resolver de forma elegante problemas que exigirían cálculos intermedios o más complicados sin su ayuda (por ejemplo, un SUMAR.SI() o CONTAR.SI() donde necesitemos más de un criterio).

Este último caso es el que vamos a utilizar para mostrar una aplicación práctica.  

Si deseáis profundizar en el tema, os recomiendo las excelentes guías del maestro Antonio Roldán Martínez, tanto para Calc como para Excel.

Planteando el problema

Hace algún tiempo me vi en la necesidad de conocer cuantos días había dedicado a un proyecto.  Pero los registros de la hoja donde controlaba los proyectos, correspondían a tareas con una hora de inicio y una hora final, por lo cual aparecían varias filas correspondientes al mismo día.  Ahí es donde comencé a buscar infructuosamente si existía una función CONTAR.UNICOS().

Con fines didácticos plantearemos un problema similar más simplificado, y luego veremos como apliqué esta técnica para resolver mi necesidad.  La hoja de cálculo con los ejemplos que se muestran la podéis descargar del adjunto al final del artículo (tanto en ODF como en XLS).  Podéis ver las capturas de pantalla a tamaño real pulsando sobre las miniaturas.

Disponemos de una hoja de cálculo con un rango de datos con diferentes nombres repetidos:

Podemos ver como pese a que hay dieciséis celdas en este rango, tan sólo hay cinco nombres en él:

Usando CONTAR.SI() podemos ver cuantas repeticiones hay para cada uno de los nombres:

Creando nuestra fórmula matricial

En el ejemplo, ¿qué ocurriría si asignamos un "peso" a cada elemento consistente en la unidad dividido por el número de repeticiones?

Es decir, a cada elemento "José", le damos un peso de 1/6; a "Juan", 1/2; a "María", 1/3 y así sucesivamente. De esta manera si multiplicamos el número de repeticiones de cada uno por su "peso", obtendremos la unidad.  Como todas las celdas de un elemento pesarán 1, la suma final nos mostrará el número de elementos con que estamos trabajando.

Y esto es lo que hemos aplicado en esta primera aproximación a la solución:

{=SUMA(1/CONTAR.SI(datos;datos))}

Recordad, en la fórmula no hay que escribir las llaves {...}.  Éstas aparecerán al introducir la fórmula con Ctrl+Mayúsc+Intro.

Dentro de la fórmula, CONTAR.SI(datos,datos) está contando para cada celda las repeticiones del mismo elemento que hay en todo el rango.  Ese es el denominador que por el cual se divide la unidad.  

Esta expresión en una fórmula no matricial provocaría un error de #VALOR! pues CONTAR.SI() espera que su segundo argumento sea un simple criterio, no un rango de criterios.  

Pero al introducirla como matricial, lo que hace CONTAR.SI() es devolver una matriz del mismo tamaño que el rango de datos con el número de repeticiones correspondiente a cada elemento.  

Éste es el valor por el cual vamos a dividir la unidad, obteniendo una matriz con el "peso" de cada elemento tal como habíamos visto antes.  

Finalmente esta matriz es la que pasamos a la función SUMA(), con lo cual obtenemos el resultado deseado.

¡Socorro! Las celdas vacías me provocan un siniestro #DIV/0! (error de división por cero)

Hemos de prever que en nuestro rango de datos, no necesariamente todas las celdas tendrán contenido y de darse este caso, se provoca el típico error de división por cero.

No hay problema, podemos mejorar nuestra fórmula contemplando esta posibilidad:

{=SUMA(SI(ESBLANCO(datos);0;1/CONTAR.SI(datos;datos)))}

Una vez más, os recuerdo la necesidad de introducir la fórmula con Ctrl+Mayúsc+Intro para que sea correctamente tratada como una fórmula matricial.

Una aplicación práctica

Ahora, que ya hemos visto como usar una fórmula matricial para emular el comportamiento de una hipotética función CONTAR.UNICOS(), podemos aplicarla en el control de seguimiento de proyectos que exponía en el planteamiento.

Para su estudio, podéis descargar las hojas de cálculo en formato ODF y XLS del pie de este artículo.

Espero que hayáis encontrado útil esta técnica y la apliquéis a vuestros proyectos.

 

AdjuntoTamaño
CONTAR_UNICOS.ods21.28 KB
CONTAR_UNICOS.xls30.5 KB

Filtro especial en Calc

Parcialmente basado en Usar Calc como una base de datos simple, de Andrew Pitonyak, publicado en http://oooauthors.org (original en inglés)

En ocasiones, para manejar nuestra lista de datos, encontraremos limitadas las posibilidades que nos ofrecen los filtros automáticos (autofiltro) o el filtro predeterminado. Es entonces cuando podemos hacer uso de los filtros especiales, que superan algunas de esas limitaciones. Su uso no es del todo trivial, pues exige establecer los criterios de filtro en un rango de la propia hoja de cálculo, pero tienen unas opciones que los pueden hacer muy interesantes para ciertas necesidades.

Para seguir esta guía, puedes copiar y pegar los datos siguientes en una hoja de Calc. También puedes descargar el documento de ejemplo desde el enlace al pie de este artículo.

Nombre

Población

Sexo

Peso Kg

Altura

Estefania

Zaragoza

Mujer

73

1,62

Queralt

Barcelona

Mujer

98

1,55

Joan

Zaragoza

Hombre

61

1,60

Joan

Zaragoza

Hombre

70

1,80

Marc

Tarragona

Hombre

91

1,81

Josep

Valencia

Hombre

53

1,57

Esther

Girona

Mujer

60

1,57

Laura

Tarragona

Mujer

70

1,72

Raquel

Barcelona

Mujer

65

1,81

Joan

Tarragona

Hombre

99

1,62

Maria Isabel

Lleida

Mujer

87

1,56

Adrià

Valencia

Hombre

92

1,60

Pulsando sobre las capturas de pantalla, podrás ver las imágenes a tamaño real.

A diferencia del filtro predeterminado, que tan sólo admite tres condiciones (obsoleto, desde la versión 3, ya se disponen de ocho criterios), un filtro especial soporta hasta ocho condiciones de filtro. Los criterios para un filtro especial se almacenan en un rango de celdas de una hoja. El primer paso para crear un filtro especial será introducir los criterios de filtro en la hoja de cálculo.

  1. Escoge un rango de celdas vacío en el documento de Calc. Este rango puede estar en cualquier hoja del documento (a diferencia de Excel, cuyos criterios para el filtro avanzado, deben estar en la propia hoja de los datos).
  2. Duplica los encabezados de columna del área que va a ser filtrada en el área que contendrá los criterios de filtro. Ten muy presente que un espacio de más en los nombres de los encabezados puede hacer que no funcione correctamente el filtro, así que, aunque puedes escribirlos manualmente, lo más seguro es que lo hagas mediante Copiar y Pegar. No es imprescindible escribir todos los encabezados, basta con los que debas utilizar como criterio.
  3. Ahora, introduce los criterios de filtro debajo de los encabezados de columna. El criterio en cada celda de una fila se conecta con Y lógico (AND). Los criterios en filas diferentes están conectados con un O lógico (OR).

Como ejemplo, para la tabla con la que estamos trabajando, el criterio podría estar definido como sigue:

Población

Sexo

Altura

Zaragoza

Mujer

Mujer

>1,80

Este criterio nos filtrará la tabla original de forma que tan sólo nos muestre las mujeres de Zaragoza, o las mujeres de altura mayor de 1,80 m (aunque no sean zaragozanas). El vínculo "O" entre las dos condiciones viene dado por haberlas ubicado en filas diferentes. En cambio los criterios que están en la misma fila vienen conectados por un vínculo "Y" (es decir, deben cumplirse ambos criterios simultáneamente). En el ejemplo: ser de Zaragoza Y ser mujer, O bien, ser mujer Y de altura superior a 1,80.

Aquí puedes observar la apariencia de la hoja de cálculo con los datos y criterios de ejemplo.

 

 

Después de haber creado los criterios, puedes aplicar el filtro especial de la siguiente manera:

  1. Selecciona la tabla de datos a filtrar. Si el área de datos está delimitada por filas y columnas vacías (como debería ser) basta con que estemos situados en una celda dentro del área, pues Calc sabrá reconocer el área completa.
  2. Usa Datos > Filtros > Filtros especiales para abrir el cuadro de diálogo Filtro especial

  3. Selecciona el rango que contiene los criterios de filtro. Aquí Calc debería permitir leer los criterios del filtro desde un rango nombrado, pero algún error de esta versión del programa no lo permite, así que habrá que seleccionar manualmente el rango.
  4. Haz clic en Aceptar.

Tu lista ya está correctamente filtrada, como puedes ver en la siguiente captura.

 

 

Para restablecer la lista de datos, bastará con ir a Datos > Filtros > Eliminar filtro

También se pueden establecer criterios más sofisticados y flexibles; por ejemplo, usando expresiones regulares. Para ello, en el cuadro de diálogo Filtro especial, debemos pulsar el botón Opciones, que nos ampliará el diálogo.

 

 

Las expresiones regulares son muy potentes (mucho más que los comodines '*' y '?' usuales en Excel), pero son algo más complejas. Como referencia básica usa esta tabla

En Calc

En Excel

Interpretación

. (un punto)

?

Cualquier carácter.

.* (un punto seguido de un asterisco)

*

Cualquier carácter, cualquier número de veces.

Si estás interesado en profundizar en las posibilidades del uso de expresiones regulares, acude a la ayuda de Calc o a la Wikipedia (entre otros sitios).

Siguiendo con nuestro ejemplo, si deseamos obtener el listado de todos los hombres de nuestra lista, usando expresiones regulares, el criterio será:

Sexo

H.*

Para que funcione el criterio, deberás mostrar las Opciones y activar la casilla Expresiones regulares. Tras aceptar el diálogo tendrás el listado de hombres de nuestra tabla de datos.

 

 

Otras posibles opciones son:

  • Diferenciar entre mayúsculas y minúsculas
  • Obtener los resultados del filtro en otra parte del documento (manteniendo intacta la lista original)
  • Mostrar los datos sin repeticiones

Por su especial interés, veamos un ejemplo de esto último. Queremos obtener un listado de poblaciones de nuestra tabla de datos, pero queremos poblaciones únicas, sin repeticiones. Procederemos de la siguiente manera:

Estableceremos el rango de criterios en una columna de dos celdas, con la primera celda con el título Población y la celda inferior vacía.

  1. Seleccionaremos tan sólo la columna de poblaciones de nuestra tabla (aquí sí que necesitamos seleccionar específicamente los datos deseados, pues Calc no sería capaz de adivinarlo).
  2. Abriremos el diálogo de Filtro especial (Datos > Filtro > Filtro especial).
  3. Activaremos la casilla Sin duplicados.
  4. Activaremos la casilla Escribir resultados en y seleccionaremos la celda donde queremos obtener el listado (aquí si que permite seleccionar el destino de los datos mediante un rango nombrado).

 

 

Tras aceptar, obtendremos, sin repeticiones, el listado de las poblaciones que contiene nuestra tabla de datos.

 

 

¡Y hasta aquí hemos llegado! Espero que este tutorial resulte de utilidad para vuestros proyectos.

Nos vemos en la próxima receta de nuestro Recetario de OpenOffice.org .

AdjuntoTamaño
filtro-especial-ejemplo.ods18.17 KB

Funciones matriciales: obtener histogramas usando FRECUENCIA()

Fredy Cifuentes, de Chile, nos envía este ejemplo de uso de funciones matriciales

Una de las acciones más comunes al hacer un análisis estadístico es obtener la distribución de frecuencia de un grupo de datos.

Por ejemplo, supongamos que tenemos las calificaciones del examen semestral de dos grupos de 20 alumnos de preparatoria, la calificación máxima del examen es 20 y queremos conocer la distribución de las calificaciones de ambos grupos agrupadas en rangos de 4. Es decir, cuántos alumnos sacaron 4 o menos, cuantos entre 4 y 8, etcétera.

Para ello utilizaremos la función matricial FRECUENCIA.

Las fórmulas matriciales, son unos tipos de fórmulas que nos permiten efectuar cálculos con bloques completos de celdas. Habitualmente, las fórmulas matriciales se introducen con todas las celdas donde debe aparecer la fórmula seleccionadas, y en vez de introducirla con el habitual [Intro], lo haremos con [Mayus + Ctrl + Intro]. Cuando usamos funciones matriciales mediante el asistente para funciones, éste mismo se encarga de introducir la fórmula debidamente, así que no debemos preocuparnos de la selección previa, ni de la combinación de teclas para introducir la fórmula.

Colocamos los valores del Grupo 1 en el rango C6:C16 y los del Grupo 2 en el rango D6:D16.

En el rango F6:F10, relacionaremos los límites de valores para los cuales deseamos obtener la distribución: 4, 8, 12, 16 y 20.

Nos situamos en la celda G6, activamos el asistente para funciones, seleccionamos la categoría "Matriz", y la función FRECUENCIA. Tras pulsar el botón "Siguiente", el asistente nos pedirá las opciones "Datos" y "Grupos".

En "Datos" seleccionaremos el rango C6:C16 (los datos correspondientes al primer grupo de calificaciones)

En "Grupos" seleccionaremos el rango F6:F10 (los intervalos de calificaciones cuyas frecuencias deseamos obtener)

Al aceptar el diálogo, Calc introducirá las frecuencias deseadas en el rango G1:G11. Observemos como se ha escrito esta fórmula: {=FRECUENCIA(C6:C16;F6:F10)}. Las llaves {...} nos indican que la fórmula introducida es de tipo matricial.

NOTA: Tal como hemos comentado antes, si en vez de usar el asistente hubiésemos introducido la fórmula directamente, hubiésemos debido seleccionar el rango G1:G11, escribir la fórmula (sin las llaves {...}) e introducirla pulsando [Mayus + Ctrl + Intro]. El asistente para funciones es el que permite introducirla con más facilidad.

A continuación, siguiendo el mismo método, introduciremos en el rango F1:F11 las frecuencias correspondientes a las calificaciones del segundo grupo (rango D6:D16).

Ahora, ya podemos obtener el histograma de la distribución de frecuencias:

Funciones para redondeo de importes (REDONDEAR).

La función REDONDEAR nos permite hacer lo que dice su nombre: redondear cantidades. Vamos a mostrar un poco la forma de usarla (lo que se verá a continuación funciona tanto en OpenOffice.org Calc como en MS-Excel)

Sintaxis de uso:
=REDONDEAR(IMPORTE, DECIMALES)
donde:
IMPORTE puede ser un valor o una referencia a una celda que contenga un valor.
DECIMALES, un número entero (o una referencia a celda que contenga un número entero) que indica a cuántos decimales se desea redondear.

Un truco poco utilizado en esta función es que si DECIMALES es un ENTERO de signo negativo, el redondeo se produce a la izquierda del separador decimal, obteniendo decenas, centenas, miles, etc.

Lo veremos más facilmente con un ejemplo:

Conteniendo A1 el valor 3158,325

REDONDEAR(A1;2) = 3158,33
REDONDEAR(A1;1) = 3158,3
REDONDEAR(A1;0) = 3158
REDONDEAR(A1;-1) = 3160
REDONDEAR(A1;-2) = 3200

Muy útil para calcular precios "políticos", aunque en ese caso es mejor utilizar REDONDEAR.MAS, que siempre redondea por arriba. ;-)

Si PRECIO es 23423:
=REDONDEAR.MAS(PRECIO;-3)- 5 dará como resultado: 23995

De forma análoga a REDONDEAR.MAS, tenemos REDONDEAR.MENOS, REDONDEA.PAR o REDONDEA.IMPAR (no estoy seguro de que estas dos últimas estén disponibles para Excel)

¡Cuidado con el separador de listas! Yo he puesto el punto y coma (;) por que es el que corresponde en la localización española, con otras configuraciones regionales se puede utilizar diferente puntuación para ello.

Importar ficheros de texto en Calc

En ocasiones, tendremos un fichero TXT con información estructurada que deseamos importar a una hoja de cálculo, pero cuando lo abrimos desde Calc, lo reconocerá como un archivo de texto y, consecuentemente, lo abre con Writer.

Para que sea reconocido por Calc, debemos hacer Archivo => Abrir, y en Tipo de Archivo, seleccionar "Texto CSV (*.csv;*.txt)", seleccionar el archivo y ya aparecerá el diálogo de importación de texto, que nos muestra la estructura y permite definir algunos parámetros de la importación.

Si, previamente, cambiáis la extensión TXT por CSV, el reconocimiento de este tipo de archivo será automático.

La extensión CSV (Comma Separed Values) es un formato estándar de intercambio de información tabulada.

Truco adicional

A veces tendréis que importar ficheros de texto con valores numéricos donde el separador decimal es el punto. En esas ocasiones os encontraréis que determinados valores os los transforma en fechas; por ejemplo, 15.1 lo cambia en 15/01/04. Además el resto de los números los trata como texto, con lo cual tenéis que hacer las operaciones de conversión oportunas.

La solución: En el diálogo de importación de texto, seleccionad los campos numéricos (admite selecciones múltiples) y seleccionad Tipo de Campo: Inglés (US). Reconocerá todos los valores numéricos perfectamente.

Impresionantes problemas de impresión en Calc

Actualización 01/09/10: Se ha eliminado la mención del problema que ocurría cuando imprimía todas las hojas en vez de sólo la activa; hace bastantes versiones que se ha solventado correctamente y ya no se da ese comportamiento.

Calc sólo me imprime parte de las hojas de un documento Excel importado

 

Motivo:

Hay definida al menos una área de impresión, que es tratada de forma diferente por los dos programas:

  • Excel:
    imprime las hojas que tengan definida área de impresión de acuerdo con las especificaciones de la misma, y además imprime de forma normal las hojas que no tienen definida área de impresión.
  • OpenOffice.org Calc
    imprime exclusivamente las áreas de impresión definidas en el documento.

Posibles soluciones:

  • Definir todas las áreas de impresión que necesitemos
    Se puede hacer fácilmente con Ver => Previsualización del salto de página; seleccionando el área deseada, pulsar el botón secundario del ratón y en el menú contextual, Definir (o Añadir) área de impresión. Si además, necesitamos definir filas de repetición en cada página del área, ir a Formato => Áreas de impresión => Editar => Filas a repetir.
  • Eliminar las definiciones de áreas de impresión.
    Bien fácil con la Previsualización del salto de página. En cada hoja, donde haya establecida un área de impresión, pulsar el botón secundario del ratón y en el menú contextual, Restablecer el área de impresión.
  • Seleccionar el área que deseamos imprimir y hacer Archivo => Imprimir => Selección

Dependerá de la estructura del documento y de nuestras necesidades, que nos resulte más conveniente una u otra)

Sugerencia:  Si hemos de usarlas con frecuencia, resultará más cómodo el uso de áreas de impresión si añadimos unos iconos con estas funciones de añadir o eliminar área de impresión a la barra de herramientas de formato.


Listas de clasificación personalizadas

Una de las características más útiles de Calc es el autorellenado de celdas. Por ejemplo, si usted inserta en las celdas B1 y B2 los valores 100, 200 respectivamente y a continuación selecciona estas dos celdas, con sólo arrastrar el puntero de celda hasta la B6, Calc rellenará las celdas con los valores de incremento tomando como referencia las dos primera celdas, es decir, los valores 300, 400, 500 y 600 aparecerán en B3, B4, B5 y B6. Esta función de autorellenado también funciona con fechas. Sin embargo en ocasiones Calc no puede inferir la lógica de la primera secuencia para rellenar el resto por lo cual sólo repetirá la secuencia sin incrementar los valores.

Para hacer que Calc reconozca secuencias que nos interesan se pueden crear listas de clasificación personales seleccionando en la barra de menú Herramientas => Opciones => Hoja de calculo => Listas de clasificación. Por ejemplo, supongamos que tenemos un catálogo de software y con frecuencia perdemos tiempo reescribiéndolo a mano una o abriendo otro documento para cortar y pegar. Para tener siempre nuestro catálogo a mano creamos una lista de clasificación:

Listas de clasificación

Ahora basta escribir ?Gimp? y luego arrastrar el puntero de celda hacia cualquier posición para que las celdas se autorellenen:

Arrastrando puntero

Recálculo manual o automático

En ocasiones, si tenéis alguna hoja de cálculo muy grande, con largas listas de datos, montones de fórmulas y muchísimas hojas, podéis notar cierta demora cada vez que introducís algún dato.

En esos casos puede resultar interesante desactivar el cálculo automático, para que no recalcule todo cada vez que introduzcáis algún contenido.

Para ello, desactivar la opción Herrramientas => Contenido de las celdas => Cálculo automático.

Entonces, podréis introducir datos en la hoja a toda velocidad y cuando vosotros queráis actualizar los cálculos no tenéis más que pulsar <F9>.

Naturalmente, eso puede ser algo excepcional, y si llegáis a verlo necesario, posiblemente es que el diseño de la hoja se ha complicado tanto que sería conveniente rediseñarla, utilizar una base de datos o alguna otra solución, según vuestra necesidad.

Vincular celdas desde otro archivo de Calc (función DDE)

Una forma de hacer esto es abrir el archivo fuente, seleccionar y copiar (CTRL + C) las celdas deseadas, luego en una segunda hoja de cálculo seleccionar una celda y elegir la opción de menú Editar => Pegado Especial y en la subsección Opciones elegir la opción Vincular.

Una manera más directa de hacer esto es escribir la ruta, la hoja y la celda del valor que nos interesa directamente en la barra de fórmulas siguiendo el siguiente formato:


=DDE(soffice; RutaAlarchivo; NombreDeLaHoja.UbicaciónDeLaCelda)

Por ejemplo:


=DDE("soffice";"/home/manuel/Tesis/Datos.sxc";"Promedios.C23").


De la Ayuda de OpenOffice.org:
DDE significa "Dynamic Data Exchange" (intercambio dinámico de datos), un precursor de OLE, "Object Linking and Embedding" (Vinculación e incrustación de objetos). DDE vincula los objetos a través de referencias de archivo, no los incrusta.