PDF de programación - Estudios sobre hojas de cálculo

Estudios sobre hojas de cálculo

Hojamat.es


Estudios

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 pueden perder actualidad o eficacia y serán eliminados.

Contenido

Estudios ............................................................................................................................ 1

Construye un temporizador .......................................................................................... 1

Cómo insertar una animación flash en Excel ............................................................... 3

Conjuntos numéricos idénticos ..................................................................................... 5

Recogida de datos en tablas de marcado de casillas. .................................................... 6



Construye un temporizador

Una hoja de cálculo permite pequeñas animaciones, que, aunque no pueden competir como
tales con las logradas con otras herramientas, sí pueden ayudar, mediante pequeños cambios
y movimientos, una comprensión mejor de algunos objetos numéricos.

Concepto de temporizador

Llamaremos temporizador a cualquier técnica que permita que unos números o gráficos
cambien de forma rítmica según el transcurso del tiempo en una hoja de cálculo. Por ejemplo,
podemos lograr que en una celda vayan apareciendo los números primos, de forma
consecutiva, cada segundo de tiempo.

Puedes observar
este ejemplo en un
libro que hemos
preparado de
OpenOffice o de
Excel.



Podemos usar dos métodos distintos para construir un temporizador, dependiendo del
intervalo de tiempo deseado.

1) Temporizador para segundos

Podemos construir un temporizador con el siguiente código en Basic



1

Estudios sobre hojas de cálculo

Hojamat.es



Sub retardo()
Dim t0, t1

t0 = Timer
t1 = t0
While t1 < t0 + pausa
t1 = Timer
Wend
End Sub

Se comienza definiendo dos variables t0 y t1, que leerán la variable Timer del Basic. En
OpenOffice esta variable lleva la cuenta de los segundos transcurridos. Por esta razón el
temporizador definido sólo vale para segundos. En Excel se pueden definir pausas menores
que 1.

La variable t0 almacena el primer valor del Timer, y la t1 vuelve a leer ese valor en el bucle
While_Wend de forma continua hasta que se sobrepasa la Pausa, con lo que sale del bucle,
produciendo un retardo.

Estudia en los ejemplos la forma de integrar este código, tanto en Excel como en OpenOffice.
Cada vez que incluyas la orden Call Retardo, se producirá la pausa en segundos deseada.

Ejemplo: Además del ya incluido de la serie de números primos, puedes consultar otro similar
sobre divisores comunes en Excel u OpenOffice

2) Temporizador aproximado

El anterior temporizador es bastante exacto al contar segundos. Si se renuncia a esa exactitud,
se puede construir un temporizador mucho más rápido, que permita una cierta animación en
los objetos.

El código propuesto se basa en una idea muy sencilla: la de mantener el procesador ocupado
en una tarea inútil. Hemos elegido una muy fácil de programar en Basic, y es la de un bucle
FOR_NEXT vacio, por ejemplo:

For n=1 to 30000
Next i

que repite 30000 veces una tarea inútil, la de llevar la cuenta de la variable i



2

Estudios sobre hojas de cálculo

Hojamat.es



Cómo insertar una animación flash en Excel

Ir al menú Ver > Barra de herramientas > Cuadro de controles y elegir el último botón de

esta barra (botón que parece un martillo)



Seleccionar "Shockwave Flash Object" de la lista.



El puntero del ratón se convierte en una cruz, pulsar el botón del ratón y arrastrar para abrir
un cuadro.



Hacer clic con el botón derecho sobre ese cuadro.



3

Hojamat.es


Estudios sobre hojas de cálculo



Aparecerá un menú y en él hay seleccionar la opción de Propiedades.

En la ventana que aparece hay que hacer clic en la palabra “Personalizado”, y seleccionar el
icono de “build” (los tres puntitos al final de la línea).



Teclar el URL o la dirección donde se encuentra el archivo

y activar la opción de Incrustar película

Presionar el botón de “Aplicar” y luego el de “Aceptar” .

Cerrar el archivo de Excel.

Al volverlo a abrir debería verse la animación flash activa.
Si no se ve nada, pulsar con el botón derecho y pedir Reproducir



4

Hojamat.es


Estudios sobre hojas de cálculo

Conjuntos numéricos idénticos

En algunas cuestiones resulta útil decidir de forma automática si dos conjuntos numéricos son
idénticos o no. Por ejemplo, en las tablas de multiplicar de los cuerpos finitos, como Z/Z7, es
interesante descubrir si

(a) No existen elementos repetidos en ninguna fila o columna
(b) Los elementos de las distintas filas son los mismos.

Si escribimos los dos conjuntos en una hoja de cálculo, en filas paralelas, deberemos
comprobar cuatro hechos para decidir si los conjuntos son idénticos o no:

(1) No existen elementos repetidos en el primer conjunto
(2) Tampoco se repiten los del segundo
(3) Todo elemento del primero ha de pertenecer al segundo
(4) Todo elemento del segundo ha de pertenecer al primero.



Las cuatro cuestiones las resuelve la función CONTAR.SI. Recorremos todo el primer conjunto y
mediante esta función contamos las veces que figuran en el segundo. Si esos valores son
mayores que 1, es que existen repetidos en el segundo conjunto, y si es 0, es que falta alguno.
Lo deseable, pues, es que todos los contadores presenten el valor 1.

Procedemos de la misma forma, contando las veces que los elementos del segundo conjunto
figuran en el primero, y también han de valer 1. Para evitar problemas en las siguientes
operaciones que explicaremos, a las celdas vacías también se le debe asignar un 1.

¿Cómo resumimos la situación? Multiplicamos todos los contadores del primer conjunto, y nos
ha de resultar la unidad. Ocurrirá lo mismo con el producto de los del segundo, por lo que si
multiplicamos ambos productos, obtendremos un criterio para decidir si los dos conjuntos son
idénticos: el que el producto final tenga el valor de 1.

Puedes estudiar este proceso en las hojas

identicos.xls



identicos.ods



5

Hojamat.es



Estudios sobre hojas de cálculo

Recogida de datos en tablas de marcado de casillas.

Cuando se plantea una encuesta de valoración es fácil adivinar la orientación cultural de quien
la ha confeccionado. Si es alguien con mentalidad numérica, la crea pensando ya en la recogida
de datos y aplicación de medidas estadísticas. Utiliza escalas numéricas o ceros y unos. Por el
contrario, personas más cercanas a una cultura de tipo humanístico prefieren esquemas
sencillos, visuales y que transmitan bien la idea que se desea valorar.

Una estructura muy usada es la de una tabla de doble entrada en la que se marcan algunas
celdas según la valoración deseada. En la imagen presentamos una muy popular, y es la de
elegir del 1 al 5, como escala ordinal y subjetiva en las columnas, para la valoración de los
aspectos que figuran en las filas.



En una tabla pequeña como esta, los totales por filas y columnas son muy sencillos de obtener,
pero imaginemos que se manejan cientos de filas o que se han agrupado muchas encuestas en
una, ¿cómo automatizar la traducción del símbolo “*” a datos numéricos? Deseamos dos
cosas:



(a) Crear unas frecuencias en la parte baja de la tabla con los distintos resultados
(b) Traducir cada asterisco a la valoración numérica entre 1 y 5 correspondiente.


En la imagen recogemos nuestras pretensiones:

Las celdas coloreadas son las que deseamos obtener de forma automática.



6

Escala del 1 al 5, 1 muy mal, 5 muy bien12345Seguridad *Prestaciones*Acabado*Precio*Servicio técnico*Escala del 1 al 5, 1 muy mal, 5 muy bien12345Seguridad *5Prestaciones*2Acabado*2Precio*4Servicio técnico*302111 Hojamat.es


Estudios sobre hojas de cálculo

Frecuencias por columnas

Con estas no hay problema, pues la función CONTAR.SI nos lo resuelve. En cada columna
escribimos algo así como =CONTAR.SI(E5:E921;"*"), donde el primer argumento recorre toda
la columna de la tabla y el segundo contiene el símbolo usado.

Como vemos, el problema se resuelve sin dificultad y no le prestamos más atención. Pasamos
al otro.

Conversión de un símbolo en una valoración numérica

Este otro problema es más difícil de resolver y con el objeto de repasar técnicas de las hojas de
cálculo lo abordaremos de varias formas.

Función COINCIDIR

Es la solución más sencilla, pues esta función nos devuelve la posición del asterisco dentro de
la fila, si la organizamos de esta forma:



 Valor buscado: el símbolo “*”
 Matriz: La fila en la que estamos trabajando
 Tipo de coincidencia: Usamos el 0 para que sea de tipo exacto: o es un asterisco o no

lo es.

En la celda se escribiría una fórmula similar a esta: =COINCIDIR("*";D5:H5;0)

Este procedimiento tiene la ventaja de poder arrastrar la fórmula hacia abajo, porque sólo
maneja referencias relativas.

El inconveniente es que si las puntuaciones no son del 1 al 5, sino otras, como 2,4,5,20, o
A,B,C,D…esta técnica nos devolvería el número de orden y no el valor. Esto se puede arreglar
con la función INDICE. Buscamos el asterisco con COINCIDIR y después lo volcamos en la
primera fila con INDICE para localizar la puntuación.



7

ABCDESeguridad*EPrestaciones*BAcabado*BPrecio*DServicio técnico*C12345 Hojamat.es


Estudios sobre hojas de cálculo

Para obtener el resultado de la imagen hemos usado este tipo de fórmula:

=INDICE(D$4:H$4;COINCIDIR("*";D6:H6))

Función BUSCARH

Esta función es muy útil en estos casos, pero aquí tiene dos problemas, como veremos.
BUSCARH actúa sobre una matriz recorriendo la primera fila para buscar el valor dese