miércoles, 5 de marzo de 2014

Recogida de datos en tablas de marcado de casillas.


Ya hacía tiempo que no dedicábamos una entrada al manejo de las hojas de cálculo sin relacionarlas con el estudio de los números. Lo hacemos hoy con un problema que se presenta al recoger valoraciones cumplimentadas mediante el 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.

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.



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 deseado, y nos devuelve el valor correspondiente en la misma columna pero situado unas filas más abajo.

Primer problema: La fila de búsqueda es siempre la primera de la matriz y la de devolución de valores es otra, pero aquí lo que deseamos devolver, 1, 2, 3, 4 o 5 está precisamente situado en la primera fila. Una solución es copiar esa fila al final de la tabla y tomar nota de donde está situada. En la imagen la hemos copiado en la fila 11



Después el truco consiste en que al dar la fila de búsqueda damos la actual (por ejemplo, para el concepto “Acabado” sería la fila 7 y para la fila de devolución escribimos 12-FILA() y así la hoja cuenta las filas que van desde la nuestra hasta la final situada en el 11 incluida.

Segundo problema: La dimensión de la matriz cambia al rellenar hacia abajo, pero eso no nos va a afectar porque no importa si sobran filas, ya que sabemos que la que nos interesa está siempre en el 11 y el cálculo 12-FILA()  nos garantiza que llegamos a ella.

En resumen, usaríamos una fórmula como esta:

=BUSCARH("*";D8:H14;12-FILA();0)

que es la correspondiente a la fila 8.
Resulta algo artificioso el procedimiento. Se ve que es mejor el que usa la función COINCIDIR. No importa, porque nuestro objetivo es descubrir posibilidades.

¿Qué haría alguien de Matemáticas?

Esto va un poco en broma.

Cambiaría los asteriscos por un 1. Esto se puede conseguir con la orden Reemplazar.



Los huecos se pueden reemplazar por ceros, pero no es necesario. Una vez que nuestra matriz es numérica, para traducir la posición del asterisco a un número basta usar SUMAPRODUCTO, que multiplique la fila actual por la primera, y así sólo aparecerá la puntuación situada en la misma fila que el 1.



Sería una fórmula similar a esta:

=SUMAPRODUCTO(D6:H6;D$4:H$4)

Observa que la primera fila se usa con referencia absoluta, para que al rellenar hacia abajo se conserven siempre las puntuaciones 1, 2,…5.

El problema está en que la persona que haya diseñado la encuesta nos proteste por manipularla. Por eso decíamos que esto se trataba un poco como broma.