Categorizar cadenas en función de algunas palabras fue una de mis tareas básicas en el análisis de datos. Por ejemplo, en una encuesta, si pregunta a las personas qué les gusta de un teléfono inteligente en particular, las mismas respuestas tendrán una variedad de palabras. Para cámara, pueden usar palabras como fotos, videos, selfies, etc. Todas implican cámara. Por eso es muy importante categorizar las oraciones antes para obtener información significativa.
En este artículo, aprenderemos cómo categorizar en Excel usando palabras clave.
Tomemos el ejemplo de la encuesta de la que hablamos.
Ejemplo: categorizar datos recopilados de una encuesta en Excel
Entonces, hemos realizado una encuesta sobre nuestro nuevo teléfono inteligente xyz. Hemos preguntado a nuestros clientes qué les gusta del teléfono xyz y capturamos su respuesta en Excel. Ahora necesitamos saber a quién le gustó nuestra pantalla LED, altavoz y cámara.
Para ello, hemos preparado una lista de palabras clave que pueden hacer referencia a una categoría, como puede ver en la imagen de abajo. Para entender, lo he mantenido pequeño.
Los comentarios están en el rango A2: A9, las palabras clave están en E2: E10 y la categoría está en F2: F10.
La fórmula genérica para crear categorías será:
{= ÍNDICE (Categoría, COINCIDIR (VERDADERO, ESNÚMERO (BÚSQUEDA (palabras, oración)), 0))}
Tenga en cuenta las llaves, es una fórmula de matriz. Debe ingresarse usando CTRL + MAYÚS + ENTRAR.
Categoría: Es el rango que contiene la lista de categorías. Las frases o comentarios se clasificarán según estos valores. En nuestro caso es F2: F10.
Palabras: es el rango que contiene la lista de palabras clave o frases. Estos se buscarán en el frases. Aquí está E2: E10.
Frase: es la oración la que se categorizará. Es una sola celda relativa.
Dado que ahora conocemos cada variable y función utilizada para la categorización en Excel, implementémosla en nuestro ejemplo.
En la celda B2 escriba esta fórmula y presione CTRL + MAYÚS + ENTRAR.
{= INDICE ($ F $ 2: $ F $ 10, COINCIDIR (TRUE, ISNUMBER (SEARCH ($ E $ 2: $ E $ 10, A2)), 0))}
Copie la fórmula para ver la categoría de cada comentario.
Necesitamos una lista de palabras y categorías fijas, no deberían cambiar a medida que copiamos la fórmula, por lo tanto, he dado una referencia absoluta de palabras clave y categorías. Si bien queremos que las oraciones cambien a medida que copiamos la fórmula, es por eso que he usado la referencia relativa de como A2. Puede leer y comprender acerca de las referencias en Excel aquí.
Ahora puede preparar un informe de cuántos usuarios les gusta la pantalla LED, el altavoz y la cámara.
¿Cómo funciona?
El núcleo de la fórmula es ISNUMBER (SEARCH($ E $ 2: $ E $ 10, A2)): Lo he explicado en detalle aquí. La función de BÚSQUEDA busca cada valor de palabras clave ($ E $ 2: $ E $ 10) en la oración de A2. Devuelve una matriz de ubicación encontrada de palabra o #VALOR (por la palabra no encontrada). Finalmente tendremos una matriz de 9 elementos para este ejemplo. {# ¡VALOR!; 5; # ¡VALOR!; # ¡VALOR!; # ¡VALOR!; # ¡VALOR!; # ¡VALOR!; # ¡VALOR!; # ¡VALOR!}. A continuación, usamos la función ISNUMBER para convertir esta matriz en datos útiles. Lo convierte en una matriz de VERDADERO y FALSO. {FALSO; VERDADERO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO}.
Ahora en adelante, todo es simple coincidencia de índice. COINCIDIR (TRUE, ISNUMBER (SEARCH ($ E $ 2: $ E $ 10, A2)), 0): la función COINCIDIR busca VERDADERO, en la matriz resultante y devuelve el índice del primer VERDADERO encontrado. que es 2 para este caso.
ÍNDICE($ F $ 2: $ F $ 10, COINCIDIR (VERDADERO, ESNÚMERO (BÚSQUEDA ($ E $ 2: $ E $ 10, A2)), 0)): A continuación, la función ÍNDICE busca en la segunda posición en la categoría ($ F $ 2: $ F $ 10) que es la pantalla LED. Finalmente, esta fórmula categoriza este texto o comentarios como pantalla LED.
Haciéndolo sensible a mayúsculas y minúsculas:
Para que esta función distinga entre mayúsculas y minúsculas, utilice la función ENCONTRAR en lugar de la función BÚSQUEDA. La función BUSCAR distingue entre mayúsculas y minúsculas de forma predeterminada.
{= ÍNDICE (Categoría, COINCIDIR (VERDADERO, ESNÚMERO (ENCONTRAR (palabras,frase)),0))}
Los puntos débiles:
1.Si dos de las palabras clave se encuentran en la misma oración, la oración se categorizará de acuerdo con la primera palabra clave de la lista.
Capturando el texto dentro de otra palabra. Suponga que estamos buscando LAD en un rango. Entonces se contarán las palabras que contengan LAD. Por ejemplo, las escaleras se contarán para LAD ya que contiene LAD. Así que ten cuidado con eso. La mejor práctica es normalizar sus datos tanto como sea posible.
Así que este fue un tutorial rápido sobre cómo categorizar datos en Excel. Traté de explicarlo lo más simple que pude. Por favor, avíseme si tiene alguna duda sobre este artículo o cualquier artículo relacionado con Excel.
Descargar archivo:
Categorizar cadenas según palabras claveCómo verificar si la celda contiene texto específico en Excel
Cómo verificar una lista de textos en cadena en Excel
Obtenga la coincidencia de dos criterios de COUNTIFS en Excel
Obtenga los COUNTIFS con OR para varios criterios en Excel
Articulos populares :
50 atajos de Excel para aumentar su productividad : Acelere su tarea. Estos 50 accesos directos le permitirán trabajar aún más rápido en Excel.
Cómo usar tLa función BUSCARV en Excel : Esta es una de las funciones más utilizadas y populares de Excel que se utiliza para buscar valores de diferentes rangos y hojas.
Cómo usar la función CONTAR.SI en Excel : Cuente valores con condiciones usando esta asombrosa función. No necesita filtrar sus datos para contar valores específicos. La función Countif es esencial para preparar su tablero.
Cómo utilizar la función SUMIF en Excel : Esta es otra función esencial del tablero. Esto le ayuda a resumir valores en condiciones específicas.
