Cadena en función Clasificar en Palabras clave en Excel
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 le 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á:
{=INDEX(Category,MATCH(TRUE,ISNUMBER(SEARCH(words,sentence)),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 oraciones 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 las oraciones. Aquí está E2: E10.
Sentencia: es la sentencia 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.
{=INDEX($F$2:$F$10,MATCH(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 comprender acerca de link: / excel-generals -rative-and-absolute-reference-in-excel [haciendo referencia 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 vínculo: / fórmulas-lógicas-la-función-número-en-excel [ESNUMERO] (vínculo: / función-de-búsqueda-texto-excel [BÚSQUEDA] ($ E $ 2: $ E $ 10, A2)) *:
Lo he explicado en detalle enlace: / contando-contados-con-o-para-criterios-múltiples [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 de palabra encontrada o #VALOR (para 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, todo es simple link: / lookup-formulas-use-index-and-match-to-lookup-value [index match].
link: / lookup-formulas-excel-match-function [MATCH] (TRUE, link: / logic-formulas-the-isnumber-function-in-excel [ISNUMBER] (`link: / text-excel- función-búsqueda [BÚSQUEDA] `($ 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.
INDICE ($ F $ 2: $ F $ 10, MATCH (TRUE, ISNUMBER (SEARCH ($ E $ 2: $ E $ 10, A2)), 0)): A continuación, la función INDEX busca en la segunda posición en la categoría ($ F $ 2: $ F $ 10) que es una pantalla LED. Finalmente, esta fórmula categoriza este texto o comentarios como pantalla LED.
Haciendo que sea sensible a mayúsculas y minúsculas:
Para que esta función distinga entre mayúsculas y minúsculas, utilice la función BUSCAR en lugar de la función BUSCAR. La función BUSCAR distingue entre mayúsculas y minúsculas de forma predeterminada.
{=INDEX(Category,MATCH(TRUE,ISNUMBER(FIND(words,sentence)),0))}
Los puntos débiles:
1.Si dos de las palabras clave se encuentran en la misma oración, la oración se categorizará según 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 KOP ya que contienen KOP. 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:
: Esta es otra función esencial del tablero. Esto le ayuda a resumir valores en condiciones específicas.