Los formularios de Google son de muchísima ayuda para recolectar datos en una encuesta o cuestionario, sin embargo, cuando elegimos ciertos tipos de pregunta y terminamos con muchas respuestas, puede ser un poco difícil analizarlas por la forma en que se presentan en la hoja de cálculo. El tipo de pregunta de opción múltiple y casilla de verificación, nos dan como resultado valores separados por comas en cada fila, pero, ¿y esto cómo lo sumarizamos y separamos de nuevo? Con un poco de ingenio y algunas fórmulas, podemos analizar este tipo de pregunta fácilmente.
Preparando el formulario de Google
Lo primero que haremos será preparar nuestro formulario para poder recolectar la información que nos interesa correctamente. En el ejemplo que revisaremos, estamos haciendo una encuesta para saber los géneros de lectura más populares, entonces, en el tipo de pregunta elegimos Casillas de verificación y ponemos nuestras respuestas posibles.
Conectar formulario con una Hoja de Cálculo de Google
Lo siguiente que haremos será conectar nuestro formulario a una hoja de cálculo de Google para poder tener un control más preciso sobre los datos. Esta interfaz nos provee un resumen muy básico y aunque es casi igual a lo que queremos lograr, hacerlo en Google Sheets nos permitirá mucha más flexibilidad a futuro.
Entonces, vamos a la pestaña de Respuestas y damos click en el botón para vincular el formulario con una hoja de cálculo, que puede ser nueva o una que ya tengas preparada.
Analizando las respuestas
Después de enviar el formulario y recibir muchas respuestas, nos encontramos con que en la columna de la respuesta que nos interesa, están todas las respuestas correctamente recolectadas, pero están formateadas de forma que si se eligió más de una opción, las opciones se separan por espacios y comas. Esto es lo que necesitamos separar para poder analizar correctamente y hacer resúmenes.
Entonces, en una hoja nueva dentro del documento, comenzamos por planear lo que haremos. Primero necesitamos la lista de opciones disponibles en una columna, y en la siguiente contaremos cuántas veces aparece cada opción.
Limpiando los datos separados por coma
Primero, vamos a emparejar los datos para que todas las filas terminen con una coma, porque después juntaremos todas las filas en una sola cadena de caracteres, y esto hará más sencillo separarlos de nuevo para contarlos. Esto lo hacemos con el caracter de & (ampersand) y ArrayFormula para poder hacer referencia a todo el intervalo de una sola vez. Esto lo hacemos en la celda A2:
=ARRAYFORMULA(Respuestas!B2:B & ",")
NOTA: Toma en cuenta que habrá casos especiales que deberás contemplar en tus fórmulas, como por ejemplo, si tus opciones contienen el caracter de coma. Mi recomendación es que no uses ese caracter en la presentación de las opciones de tu formulario, así te evitarás dolores de cabeza teniendo que usar más fórmulas.
Ahora tenemos que juntar todas las filas en una sola cadena de caracteres con la fórmula CONCATENATE, que nos permite juntar una cadena de texto con otra. En este caso, como le estamos proporcionando un intervalo completo, basta con encapsular lo que tenemos en la misma.
=ARRAYFORMULA(CONCATENATE(Respuestas!B2:B & ","))
Lo siguiente será utilizar la fórmula SPLIT usando el caracter de coma como delimitador para ver divididas todas las respuestas en datos separados.
=ARRAYFORMULA(SPLIT(CONCATENATE(Respuestas!B2:B & ","),","))
No te preocupes si ves un error sobre que son necesarias más columnas para expandir el resultado, porque nosotros queremos el resultado expandido hacia abajo y no hacia la derecha. Para esto vamos a usar la fórmula TRANSPOSE para transponer los datos y que todo se muestre de arriba a abajo en vez de de izquierda a derecha:
=ARRAYFORMULA(TRANSPOSE(SPLIT(CONCATENATE(Respuestas!B2:B & ","),",")))
Aquí nos encontramos con un pequeño problema, y es que muchos de los datos parece que empiezan con un espacio. Esto hará que en el conteo, Fantasía y (espacio) Fantasía sean considerados como datos diferentes, entonces vamos a arreglarlo de una vez con la fórmula TRIM, que quita los espacios al principio, al final y todos los espacios repetidos:
=ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(CONCATENATE(Respuestas!B2:B & ","),","))))
Y finalmente para esta columna, aplicamos la fórmula UNIQUE, que nos devuelve las filas únicas en un intervalo
=ARRAYFORMULA(UNIQUE(TRANSPOSE(TRIM(SPLIT(CONCATENATE(Respuestas!B2:B&","),",")))))
Y ya tenemos una columna en la que dinámicamente se iran presentando las opciones, aunque las cambiemos en el formulario original y agreguemos más, porque despenden de las respuestas que recibimos.
Contar las opciones
Ahora solo queda hacer el conteo de cada tipo de opción. Siguiendo un procedimiento similar al anterior, primero separamos las opciones con ArrayFormula, TRIM y SPLIT en B2:
=ArrayFormula(TRIM(SPLIT(Respuestas!$B$2:B,",")))
Y ahora simplemente usamos COUNTIF para hacer un conteo acotado solamente a las opciones que ya tenemos en la columna de Género (osea la columna A)
=ArrayFormula(COUNTIF(TRIM(SPLIT(Respuestas!$B$2:B,",")), A2))
Arrastramos la fórmula hacia abajo tanto como sea necesario y tendremos ya nuestro conteo final:
Lo último que nos queda es hacer una pequeña gráfica y estamos listos para ver y mostrar los resultados mientras se recogen en tiempo real
¡Y listo! Ya tenemos una mejor forma de controlar nuestra información proveniente de formularios de Google. Con algo de creatividad podrás hacer análisis mucho más completos sobre las información que recolectes en tus encuestas y cuestionarios.
Guía en video
Si tienes dudas puedes ver esta misma guía explicada en video:
¡Espero que te haya sido útil esta guía! No olvides seguirnos en YouTube para más tips, trucos y guías de productividad.
Alekz es un apasionado de la tecnología y los videojuegos. Además de ser locutor comercial, disfruta de la expresión artística más sastisfactoria y técnica que existe: programar (y pintar algo de pixel art). Fundó Tesel para ayudar a empoderar a las empresas a desarrollar su máximo potencial mediante la tecnología. Es un absoluto nerd de Star Trek.
Alekz es un apasionado de la tecnología y los videojuegos. Además de ser locutor comercial, disfruta de la expresión artística más sastisfactoria y técnica que existe: programar (y pintar algo de pixel art). Fundó Tesel para ayudar a empoderar a las empresas a desarrollar su máximo potencial mediante la tecnología. Es un absoluto nerd de Star Trek.
