En el análisis de datos cualitativos, como las encuestas de clima laboral, a menudo nos enfrentamos al reto de clasificar y entender grandes volúmenes de comentarios. Esta tarea puede ser tediosa y subjetiva. Microsoft Excel, con sus funciones de matriz dinámica, ofrece una solución potente y automatizada para filtrar y previsualizar rápidamente los comentarios que no contienen palabras clave predefinidas, permitiéndonos centrar la atención en las opiniones más matizadas o ambiguas.
La fórmula que exploraremos combina varias funciones para crear una herramienta de filtrado inteligente. Su objetivo es extraer de una lista de comentarios aquellos que son «neutros», es decir, que no incluyen términos explícitamente positivos o negativos, y presentar un extracto de los mismos. Esto es especialmente útil para identificar sugerencias, preguntas o críticas constructivas que podrían pasar desapercibidas en un análisis superficial.
Sintaxis
=SI.ERROR(CONCATENAR(IZQUIERDA(FILTRAR(rango; (ESNUMERO(ENCONTRAR("positivo"; rango)) + ESNUMERO(ENCONTRAR("negativo"; rango))) = 0); 20); "..."); "No hay comentarios relevantes")Esta fórmula anidada puede parecer compleja, pero cada función cumple un propósito específico. A continuación, la desglosamos para entender su funcionamiento:
- rango Es el conjunto de celdas que contienen los comentarios a analizar. Por ejemplo,
A2:A50. - ENCONTRAR(«texto»; rango) La función ENCONTRAR busca un texto específico dentro de otro. Si lo encuentra, devuelve la posición inicial del texto (un número). Si no, devuelve un error
#¡VALOR!. Es sensible a mayúsculas y minúsculas. - ESNUMERO(…) La función ESNUMERO comprueba si un valor es un número. En nuestra fórmula, la usamos para envolver a
ENCONTRAR. SiENCONTRARtiene éxito (devuelve un número),ESNUMEROdevolveráVERDADERO(que Excel interpreta como 1). SiENCONTRARfalla (devuelve#¡VALOR!),ESNUMEROdevolveráFALSO(interpretado como 0). - (ESNUMERO(…) + ESNUMERO(…)) = 0 Este es el corazón de nuestra lógica de filtrado. Sumamos los resultados de dos comprobaciones
ESNUMERO: una para la palabra «positivo» y otra para «negativo». La suma solo será 0 si ambas comprobaciones devuelvenFALSO, lo que significa que ninguna de las dos palabras fue encontrada en el comentario. - FILTRAR(rango; …) La función FILTRAR devuelve un subconjunto del
rangooriginal que cumple con la condición lógica que hemos definido. En este caso, nos devolverá todos los comentarios que no contienen ni «positivo» ni «negativo». - IZQUIERDA(…; 20) Una vez filtrados los comentarios, IZQUIERDA extrae los primeros 20 caracteres de cada uno. Esto nos da una vista previa rápida sin ocupar demasiado espacio en la hoja de cálculo.
- CONCATENAR(…; «…») CONCATENAR (o su equivalente más moderno, CONCAT) toma los 20 caracteres extraídos por
IZQUIERDAy les añade una elipsis («…») al final, indicando que el texto ha sido acortado. - SI.ERROR(…; «No hay…») Finalmente, SI.ERROR envuelve toda la fórmula. Si la función
FILTRARno encuentra ningún comentario que cumpla la condición, devuelve un error#¡CALC!.SI.ERRORcaptura este error y muestra el mensaje «No hay comentarios relevantes», haciendo nuestra herramienta más robusta y fácil de interpretar.
Ejemplos
Para ilustrar el uso de esta fórmula, imaginemos que hemos realizado una encuesta de clima laboral y tenemos los siguientes comentarios en la columna B.
| A | B | |
|---|---|---|
| 1 | Empleado | Comentario |
| 2 | Empleado 1 | El ambiente de trabajo es muy positivo y colaborativo. |
| 3 | Empleado 2 | Sugiero implementar reuniones semanales de seguimiento. |
| 4 | Empleado 3 | El sistema de fichaje a veces no funciona correctamente. |
| 5 | Empleado 4 | He tenido una experiencia negativa con la gestión de vacaciones. |
| 6 | Empleado 5 | ¿Se podría instalar otra máquina de café en la segunda planta? |
Ejemplo 1: Extraer comentarios neutros
Aplicamos la fórmula para filtrar los comentarios de la tabla anterior que no contienen las palabras «positivo» o «negativo». El rango de comentarios es B2:B6.
=SI.ERROR(CONCATENAR(IZQUIERDA(FILTRAR(B2:B6; (ESNUMERO(ENCONTRAR("positivo"; B2:B6)) + ESNUMERO(ENCONTRAR("negativo"; B2:B6))) = 0); 20); "..."); "No hay comentarios relevantes")Resultado esperado: La fórmula devolverá una matriz dinámica que se desbordará en las celdas contiguas hacia abajo, mostrando un extracto de cada comentario neutro encontrado:
- Sugiero implementa…
- El sistema de fich…
- ¿Se podría instal…
Ejemplo 2: Resumir todos los comentarios neutros en una sola celda
Si preferimos tener un resumen consolidado en una única celda en lugar de una lista, podemos sustituir CONCATENAR por UNIRCADENAS. Esta función nos permite unir los elementos de una matriz con un delimitador personalizado, como una coma y un espacio.
=SI.ERROR(UNIRCADENAS(", "; VERDADERO; IZQUIERDA(FILTRAR(B2:B6; (ESNUMERO(ENCONTRAR("positivo"; B2:B6)) + ESNUMERO(ENCONTRAR("negativo"; B2:B6))) = 0); 20)); "Sin comentarios")Resultado esperado: Una única celda con el texto:
Sugiero implementa, El sistema de fich, ¿Se podría instal
Aplicaciones Prácticas
- 1Análisis de Encuestas de Satisfacción: Identificar rápidamente sugerencias de mejora, preguntas o problemas técnicos en encuestas de empleados o clientes, separándolos de los comentarios puramente positivos o negativos.
- 2Gestión de Feedback de Productos: Filtrar reseñas de usuarios para encontrar preguntas sobre funcionalidades o informes de errores que no usan un lenguaje de valoración explícito.
- 3Clasificación de Tareas: En una lista de tareas o actualizaciones de proyecto, permite aislar aquellas que no están marcadas explícitamente como «completadas» o «con problemas» para una revisión detallada.
Observaciones
Es importante tener en cuenta varios aspectos para utilizar esta fórmula de manera efectiva:
- Versión de Excel: Esta fórmula depende de las funciones de matriz dinámica (como
FILTRAR), que están disponibles en Microsoft 365 y Excel 2021 o versiones posteriores. En versiones más antiguas, no funcionará. - Sensibilidad a Mayúsculas: La función
ENCONTRARdistingue entre mayúsculas y minúsculas. Si desea que la búsqueda no lo haga (por ejemplo, para que «Negativo» y «negativo» se traten igual), debe reemplazarENCONTRARpor la función HALLAR, que cumple el mismo propósito pero sin esta distinción. - Palabras Clave Flexibles: Las palabras «positivo» y «negativo» están escritas directamente en la fórmula. Para mayor flexibilidad, es una buena práctica escribirlas en celdas separadas (por ejemplo, D1 y E1) y hacer referencia a esas celdas en la fórmula:
...ENCONTRAR($D$1; rango).... Esto permite cambiar las palabras clave sin tener que editar la fórmula.
Errores comunes
- #¡NOMBRE! Ocurre si su versión de Excel no es compatible con las funciones de matriz dinámica como
FILTRAR. - #¡CALC! Este error es devuelto por
FILTRARcuando no se encuentra ningún resultado que coincida con los criterios. Nuestra fórmula lo gestiona correctamente gracias a la funciónSI.ERROR, que lo reemplaza por un mensaje amigable. - #DESBORDAMIENTO! Aparece si la fórmula intenta devolver múltiples resultados (desbordamiento) pero no hay suficientes celdas vacías debajo de la celda de la fórmula para mostrarlos todos. Asegúrese de que el área de desbordamiento esté libre.
Alternativas
Para usuarios de versiones de Excel sin matrices dinámicas, lograr un resultado similar requiere fórmulas de matriz más complejas que deben introducirse con Ctrl + Mayús + Intro.
-
{=SI.ERROR(INDICE($B$2:$B$6; K.ESIMO.MENOR(SI((ESERROR(HALLAR("positivo"; $B$2:$B$6))) * (ESERROR(HALLAR("negativo"; $B$2:$B$6))); FILA($B$2:$B$6)-FILA($B$2)+1); FILAS($A$1:A1))); "")}Esta fórmula de matriz tradicional realiza el mismo filtrado. Debe escribirse en una celda y luego arrastrarse hacia abajo para extraer todos los resultados coincidentes uno por uno.