 |
Aprender y divertirse con
la Hoja de Cálculo Página personal de Antonio Roldán Martínez |
Inicio Sin decimales Aritmética Divisibilidad Combinatoria Congruencias Juegos Diccionario general Estadística Teoría de Números (R. Parra) |
|
Exploraciones Trucos Estudios Sugerencias Funciones y macros |
||
|
Estás en Inicio > Exploraciones > Pequeños trucos |
||
Pequeños trucos
En este apartado se incluirán trucos o técnicas que estén relacionados de alguna forma con la temática general de la página.
Se irán rellenando las tres secciones según se emprendan exploraciones nuevas
Realiza una interpolación lineal con el controlador de relleno
Volcado de los nombres de hoja en un rango de celdas
Copia en una celda del nombre de su hoja
Crea botones para tus macros en las barras de herramientas
Declara constantes en tu libro
Inserta una lista desplegable en una sola celda o en un combobox
En este documento se han incluido trucos y técnicas para Excel que no funcionan en OpenOffice.org
Realiza una interpolación lineal con el controlador de relleno
Si en unas celdas (por ejemplo en columna) has escrito números que presentan una tendencia lineal más o menos aproximada, los seleccionas y usas el controlador de relleno para extenderlos hacia abajo (o hacia la derecha), las celdas siguientes se rellenarán según la tendencia lineal que presenten los datos seleccionados:
| Por
ejemplo, las celdas de la imagen presentan una cierta tendencia lineal
(falla el incremento de 7 a 12)
|
Al
rellenar hacia abajo con el controlador de relleno se efectúa un ajuste
lineal a los datos dados
|
Volcado de los nombres de hoja en un rango de celdas
Imagina que manejas un libro
de Excel compuesto por muchas hojas, tantas que te es difícil recorrerlas todas
con rapidez.
Supongamos que para redactar un informe necesitas crear un rango de celdas con
la lista ordenada de todos los nombres de las hojas. Una macro en VBA nos
resolvería este problema sin necesidad de consultar las hojas una por una. Aquí
la tienes:
El código se corresponde con el texto en negrita y los comentarios con los añadidos en cursiva.
Sub listahojas()
Dim a, i, n$, colu, fila
a = ActiveWorkbook.Sheets.Count
Cuenta las hojas que tiene el libro
fila = ActiveCell.Row Toma nota de la fila de la celda seleccionada
colu = ActiveCell.Column Hace lo mismo con la columna
For i = 1 To a Recorre todas
las hojas
n$ = ActiveWorkbook.Sheets(i).Name Toma nota de los nombres
ActiveWorkbook.ActiveSheet.Cells(fila, colu).Value = n$ Los vuelca en
la celda elegida
fila = fila + 1 Baja el cursor una fila para seguir rellenando con
nombres
Next i
End Sub
Para implementar el código puedes usar el procedimiento explicado en otra parte de hojamat.es
Selecciona una celda, pide ejecutar la macro propuesta y obtendrás la lista de los nombres de todas las hojas
Copia en una celda del nombre de su hoja
Si te interesa que en una celda se copie el nombre de su propia hoja deberás escribir como código de una función el siguiente:
Public Function copianombre() As
String
copianombre = ActiveWorkbook.ActiveSheet.Name
End Function
Después, en la celda que desees, escribe =COPIANOMBRE()
y automaticamente se rellenará con el nombre de su hoja.
Para implementar el código puedes usar el procedimiento explicado en otra parte de hojamat.es
Crea un nuevo botón en una Barra de Herramientas
Cuando se usa frecuentemente un mismo libro de OpenOffice que contiene macros, puede ser interesante disponer de un nuevo botón en una Barra de Herramientas, pero que sólo esté activo en ese libro, y no en otros. De esta forma no se llena la barra de botones que sólo son útiles en una hoja de cálculo determinada.
Los pasos a dar para lograrlo son los siguientes:
Se supone que tenemos grabada o programada una macro, por ejemplo borrar, que nos puede servir para borrar todo un rango determinado en un libro al que llamaremos borrarango.ods
Para asignarle un botón nuevo de una barra pide Herramientas - Personalizar y elige la pestaña Barra de Herramientas
 |
Una vez abierta la
pestaña, has de cuidar la concreción de dos detalles: Elige en qué barra figurará el nuevo botón. En la imagen se ha elegido la Estándar Concreta también si se guardará en OpenOffice.org Calc, con lo que sería válido el botón para todos los libros, o bien en un libro determinado, que en este ejemplo verás que se ha elegido borrarango.ods. Este detalle es muy importante si deseas que sólo funcione en ese libro determinado. |
Pulsa sobre el botón Agregar... para proceder a la creación del nuevo botón. En la ventana que se abre busca, en el explorador de la izquierda, el módulo en el que está contenida la macro borrar. En la imagen es el module1 de borrarango.ods.
Cuando localices la macro, selecciónala y pulsa en Agregar y en Cerrar.
Volverás a ver la ventana anterior, y en la lista de comandos figurará la macro borrar.
Si ahora cierras todo y observas la barra de herramientas estándar, en ella aparecerá la palabra borrar, pero como deseamos que tenga aspecto de botón, vuelve de nuevo a Herramientas - Personalizar y pestaña Barra de Herramientas. Selecciona borrar y pulsa sobre el botón Modificar...Dentro del menú que se te ofrece elige Cambiar icono...
Elige uno de los iconos que figuran en el catálogo y pulsa Aceptar las veces que sean necesarias.
En la imagen puedes observar que hemos elegido el aspa verde para representar el comando borrar en la barra de herramientas.
Como ejemplo de esta técnica, abre el libro borrarango.ods, escribe algo en el rango A1:H21 y después pulsa sobre el botón del aspa verde. Se borrará todo lo que hayas escrito.
Abre cualquier otro libro, y el botón de borrar habrá desaparecido de la barra estándar.
Trucos para Excel y OpenOffice.org
Declara constantes en tu libro
Con Insertar - Nombre - Definir, no sólo podemos asignar un nombre a una celda o rango de celdas, sino que también podemos nombrar al resultado de una fórmula. Por ejemplo, podemos desear llamar ORO al número áureo 1,61803399... , mediante su expresión (1+RAIZ(5))/2, de forma que se pueda integrar en cualquier tipo de fórmula: =2*ORO/PI().
El procedimiento es muy simple: Comenzamos en ambos programas con la secuencia Insertar - Nombre - Definir..., definimos como nombre ORO, pero, en lugar de asignarle un rango, lo referimos a una fórmula. Observa cómo se escribe según el programa que uses.
|
En Excel |
En OpenOffice.org |
|
|
 |
Observa que en OpenOffice se escribe RAÍZ con tilde.
Para probar tu declaración de la variable ORO, escribe en cualquier celda la fórmula =(1+ORO)/ORO-ORO, y comenta el resultado.
Nota importante: Estos nombres sólo son válidos en el libro en el que los definas, lo cual, en realidad, es una ventaja, para no llenar en exceso el programa con nuevos nombres.
Imagina que una hoja de cálculo contiene una lista extensa de datos, normalmente repetidos, y que deseamos poder elegir entre una lista de posibles componentes de la misma para que la hoja nos devuelva el número de veces que se repite el dato elegido: ¿Cuántas veces se repite la ciudad de Lima, o el apellido Ortega?
Para este tipo de operaciones puede ser de gran utilidad disponer de una celda que contenga, no un solo dato, sino una lista completa para poder elegir. Para ello debe ser posible que la lista se despliegue para poder elegir mejor. Si no se desea que sea una celda, también se puede usar un control, como veremos más adelante.
 |
En la
imagen puedes ver una celda que contiene una lista de ciudades españolas,
de la cual se ha elegido Bilbao. Tanto Excel como OpenOffice.org Calc permiten rellenar una celda con una lista completa de datos. Aquí veremos el caso en el que la lista ya está escrita en la misma hoja.
En el ejemplo que puedes ir consultado mientras lees esto, (desplegar.ods para OpenOffice y desplegar.xls para Excel), la lista está "escondida" en el rango A7:A12. No se ve porque se ha escrito con color blanco. |
Así que en lo que sigue supondremos que la lista ha sido escrita en un rango de la hoja.
Desarrollaremos a continuación, tanto para Excel como para OpenOffice, las posibles soluciones para disponer de una lista desplegable en la que elegir un dato.
Rellenado de una celda con una lista completa.
En ambos programas se puede rellenar una celda a través del menú Datos, y el submenú de Validación.
Procedimiento en Excel
Selecciona la celda en la que deseas introducir una lista que está escrita en un rango (deberá ser en una sola columna o en una fila. En el ejemplo desplegar.xls se encuentra en el rango E7:E11
En el menú Datos, elige Validación y después la pestaña Configuración.
En el cuadro Permitir, elige Lista.
Como Origen escribe el rango en el que está situada la lista. En el ejemplo sería a7:A12, precedido por el signo =, o captúralo directamente con el ratón.
Mantén activada la opción de Celda con lista desplegable.
Si lo deseas, especifica también, en las otras pestañas, el mensaje de entrada y el de error, pero no es necesario.
Si has seguido bien todos los pasos, al seleccionar la celda la verás acompañada por una pequeña flecha y al pulsarla se desplegará la lista.
Procedimiento en OpenOffice.org Calc
Es similar al anterior, con distintas palabras:
Selecciona la celda en la que deseas introducir una lista que está escrita en un rango (deberá ser en una sola columna o en una fila. En el ejemplo desplegar.ods se encuentra en el rango A7:A11
En el menú Datos, elige Validez... y después la pestaña Criterios.
En el cuadro Permitir, elige Intervalo de celdas.
Como Origen escribe el rango en el que está situada la lista. En el ejemplo sería A7:A12, escrito tal cual, sin el signo =.
Mantén activada las demás opciones.
Si lo deseas, especifica también, en las otras pestañas, el mensaje de entrada y el de error, pero no es necesario.
Uso del control cuadro combinado
Para esta operación hay quien prefiere usar el control "cuadro combinado", porque le permite añadir más prestaciones con el Basic, o incluirlo en un formulario.
Si usas un control, te encontrarás con una novedad, y es que deberás indicar en qué celda se copiará el dato que has elegido en la lista.
Para insertar el control de cuadro combinado, tanto en Excel como en Calc, hay que hacer visible la barra de herramientas de Controles.
Pide Ver - Barra de herramientas y elige Cuadro de controles, si es en Excel, o Campos de control de formulario si es en OpenOffice Calc.
En ambos casos obtendrás una barra similar a estas:
Procedimiento en Excel
Busca el control Cuadro Combinado, que en la imagen primera es el noveno botón desde la izquierda, y pulsa el ratón sobre él. Sin realizar otra operación, con el mismo ratón dibuja el cuadro en la celda que desees. Si te resulta con dimensiones distintas a las que querías, corrígelas actuando sobre su marco.
En primer lugar, en la barra de controles pulsa el primer botón de la izquierda y con él elige Modo Diseño.
Señala con el botón derecho el cuadro combinado que has construido en las celdas y elige Propiedades. Verás un conjunto de muchas propiedades. Ahora te interesan sólo dos.
Busca la propiedad ListFillRange, que contiene el rango del sacará la lista el cuadro combinado. En el ejemplo sería A7:A12. Se escribe a la derecha de la propiedad.
Junto a ella verás la propiedad LinkedCell, que es la celda en la se copiará el dato que elijas. En el ejemplo es la G15
Pulsa de nuevo el botón de la Barra de Controles con el que entraste al Modo Diseño, y al pulsarlo deberá salir de ese modo y tendrás el control listo para funcionar. Todo lo que elijas en él se copiará en la celda G15.
Estúdialo en el ejemplo desplegar.xls.
Procedimiento en OpenOffice.org Calc
En OpenOffice el cuadro combinado está rotulado en su botón como Combo Box.
En primer lugar, en la barra de controles pulsa el primer botón de la izquierda y con él elige Modo Diseño.
Busca el ComboBox, que es que que está a la izquierda de la palabra REC y pulsa el ratón sobre él. Sin realizar otra operación, con el mismo ratón dibuja el cuadro en la celda que desees. Si te resulta con dimensiones distintas a las que deseabas, corrígelas actuando sobre su marco.
Señala ese cuadro combinado con el botón derecho y elige Cuadro de control. En la ventana que se abre elige la pestaña Datos.
Como Celda vinculada, que es la celda en la se copiará el dato que elijas escribe la referencia a celda que desees. En el ejemplo desplegar.ods es la G15
Como Rango de celdas de origen escribe el rango en el que está la lista. En el ejemplo, A7:A12.
Pulsa de nuevo el botón de la Barra de Controles con el que entraste al Modo Diseño, y al pulsarlo deberá salir de ese modo y tendrás el control listo para funcionar. Todo lo que elijas en él se copiará en la celda G15.
Estúdialo en el ejemplo desplegar.ods.
Si has usado frecuentemente la operación de pasar de un texto a columnas de una hoja de cálculo, sabrás que es un procedimiento muy útil para capturar listas de números de un documento de texto, separados por comas, espacios en blanco u otro tipo de delimitador y copiarlos en distintas celdas de la hoja.
Es aún más sencilla la operación contraria. Si tienes unos números en una columna de una hoja (o una fila)
|
2 |
|
5 |
|
10 |
|
17 |
|
26 |
|
37 |
|
50 |
|
65 |
|
82 |
|
101 |
|
122 |
|
145 |
|
170 |
y deseas convertirlos en una lista de texto, basta usar el comando de combinar celdas y copiar el contenido nuevo desde la línea de entrada con Ctrl-C
Pasa después al documento de texto y pégalo con Ctrl-V
Obtendrás la lista con números separados por espacios:
2 5 10 17 26 37 50 65 82 101 122 145 170
