PDF de programación - Trucos para hojas de cálculo

Trucos para hojas de cálculo

Hojamat.es


Trucos

En este apartado se incluirán estudios más amplios que un simple truco. Podrán versar sobre
funciones, técnicas, controles, macros, etc. Algunos podrán quedar desfasados y se estudiará
su eliminación.

Contenido

Trucos ............................................................................................................................... 1

Pequeños trucos ............................................................................................................ 1

Excel ......................................................................................................................... 1

OpenOffice ............................................................................................................... 2

Trucos para Excel ............................................................................................................. 2

Realiza una interpolación lineal con el controlador de relleno ................................ 2

Volcado de los nombres de hoja en un rango de celdas ........................................... 2

Copia en una celda del nombre de su hoja ............................................................... 3

Trucos para OpenOffice ................................................................................................... 3

Crea un nuevo botón en una Barra de Herramientas ................................................ 3

Trucos para Excel y OpenOffice.org ................................................................................ 5

Declara constantes en tu libro ................................................................................... 5

Uso de listas desplegables ........................................................................................ 6

Rellenado de una celda con una lista completa. ....................................................... 7

Uso del control cuadro combinado ........................................................................... 7

Columnas a texto .................................................................................................... 10



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

Excel

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



1

Hojamat.es


OpenOffice

Trucos para hojas de cálculo

Crea botones para tus macros en las barras de herramientas



Para ambos programas

Declara constantes en tu libro

Inserta una lista desplegable en una sola celda o en un combobox

Columnas a texto



Trucos para Excel

En este documento se han incluido trucos y técnicas para Excel que no funcionan en
OpenOffice

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:



2

Trucos para hojas de cálculo

Hojamat.es


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 automáticamente se rellenará
con el nombre de su hoja. Para implementar el código puedes usar el procedimiento explicado
en otra parte de hojamat.es

Trucos para OpenOffice

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



3

Hojamat.es


Trucos para hojas de cálculo

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



4

Hojamat.es


Trucos para hojas de cálculo



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.



5

Hojamat.es



Trucos para hojas de cálculo

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.

Uso de listas desplegables

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á esc