La catégorisation des chaînes en fonction de certains mots était l’une de mes tâches de base dans l’analyse des données. Par exemple, dans une enquête, si vous demandez aux gens ce qu’ils aiment d’un téléphone intelligent en particulier, les mêmes réponses contiendront une variété de mots. Pour la caméra, ils peuvent utiliser des mots tels que photos, vidéos, selfies, etc. Ils impliquent tous une caméra. Il est donc très important de classer les phrases avant, pour obtenir des informations significatives.

0013

Dans cet article, nous allons apprendre à classer dans Excel à l’aide de mots-clés.

Prenons l’exemple de l’enquête dont nous avons parlé.

Exemple: catégoriser les données collectées à partir d’une enquête dans Excel

Nous avons donc réalisé une enquête sur notre nouveau smartphone xyz. Nous avons demandé à nos clients ce qu’ils aiment du téléphone xyz et capturé leur réponse dans Excel. Nous devons maintenant savoir qui a aimé notre écran LED, notre haut-parleur et notre caméra.

0014

Pour cela, nous avons préparé une liste de mots-clés pouvant faire référence à une catégorie, comme vous pouvez le voir dans l’image ci-dessous. Pour comprendre, je l’ai gardé petit.

0015

Les retours sont dans la plage A2: A9, les mots-clés sont dans E2: E10 et la catégorie est dans F2: F10.

La formule générique pour créer des catégories sera:

{=INDEX(Category,MATCH(TRUE,ISNUMBER(SEARCH(words,sentence)),0))}

Notez les accolades, c’est une formule matricielle. Doit être entré en utilisant CTRL + MAJ + ENTRÉE.

Catégorie: C’est la plage qui contient la liste des catégories. Les phrases ou les commentaires seront classés par ces valeurs. Dans notre cas, c’est F2: F10.

Mots: c’est la plage qui contient la liste des mots-clés ou des phrases.

Ceux-ci seront recherchés dans les phrases. Ici, c’est E2: E10.

Phrase: c’est la phrase qui sera catégorisée. C’est une seule cellule relative.

Puisque maintenant nous connaissons chaque variable et fonction utilisée pour la catégorisation dans Excel, implémentons-la dans notre exemple.

Dans la cellule B2, écrivez cette formule et appuyez sur CTRL + MAJ + ENTRÉE.

{=INDEX($F$2:$F$10,MATCH(TRUE,ISNUMBER(SEARCH($E$2:$E$10,A2)),0))}

Copiez la formule pour voir la catégorie de chaque commentaire.

0016

Nous avons besoin d’une liste de mots et de catégories fixes, ils ne devraient pas changer lorsque nous copions la formule, c’est pourquoi j’ai donné une référence absolue aux mots-clés et aux catégories. Alors que nous voulons que les phrases changent à mesure que nous copions la formule, c’est pourquoi j’ai utilisé une référence relative comme A2.

Vous pouvez lire comprendre sur link: / excel-generals-relative-and-absolute-reference-in-excel [référencement dans Excel ici.]

Vous pouvez maintenant préparer un rapport sur le nombre d’utilisateurs qui aiment l’écran LED, le haut-parleur et la caméra.

Comment ça marche?

Le noyau de la formule est lien: / formules-logiques-la-fonction-numéro-is-dans-excel [ISNUMBER] (lien: / text-excel-recherche-fonction [RECHERCHE] ($ E $ 2: $ E 10 $, A2)) *:

Je l’ai expliqué en détail link: / counting-countifs-with-or-for-multiple-criteria [here].

La fonction RECHERCHE recherche chaque valeur de mots-clés ($ E $ 2: $ E $ 10) dans la phrase A2. Il renvoie un tableau de l’emplacement trouvé du mot ou #VALUE (pour le mot introuvable). Enfin, nous aurons un tableau de 9 éléments pour cet exemple.

\ {# VALEUR!; 5; # VALEUR!; # VALEUR!; # VALEUR!; # VALEUR!; # VALEUR!; # VALEUR!; # VALEUR!}.

Ensuite, nous utilisons la fonction ISNUMBER pour convertir ce tableau en données utiles. Il le convertit en tableau de TRUE et FALSE.

\ {FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.

Maintenant, tout est simple lien: / lookup-formulas-use-index-and-match-to-lookup-value [index match].

link: / lookup-formulas-excel-match-function [MATCH] (TRUE, link: / logical-formulas-the-isnumber-function-in-excel [ISNUMBER] (`link: / text-excel- fonction de recherche [RECHERCHE] `($ E $ 2: $ E $ 10, A2)), 0):

la fonction MATCH recherche TRUE, dans le tableau résultant et renvoie l’index du premier TRUE trouvé. qui est de 2 pour ce cas.

INDEX ($ F $ 2: $ F $ 10, MATCH (TRUE, ISNUMBER (SEARCH ($ E $ 2: $ E $ 10, A2)), 0)): Ensuite, la fonction INDEX regarde la 2ème position dans la catégorie ($ F $ 2: $ F $ 10) qui est un écran LED. Enfin, cette formule classe ce texte ou ce commentaire en tant qu’écran LED.

Rendre sensible à la casse:

Pour rendre cette fonction sensible à la casse, utilisez la fonction FIND au lieu de la fonction SEARCH. La fonction FIND est sensible à la casse par défaut.

{=INDEX(Category,MATCH(TRUE,ISNUMBER(FIND(words,sentence)),0))}

Les points faibles:

1.Si deux mots-clés se trouvent dans la même phrase, la phrase sera classée en fonction du premier mot-clé de la liste.

0017

Capturer le texte dans un autre mot. Supposons que nous recherchions LAD dans une plage. Ensuite, les mots contenant CONT seront comptés. Par exemple, les échelles seront comptées pour CONT car elles contiennent CONT. Soyez donc prudent. La meilleure pratique consiste à normaliser vos données autant que possible.

C’était donc un tutoriel rapide sur la façon de catégoriser les données dans Excel. J’ai essayé de l’expliquer aussi simplement que possible. S’il vous plaît laissez-moi savoir si vous avez des doutes sur cet article ou sur des articles liés à Excel.

Télécharger le fichier:

: Ceci est une autre fonction essentielle du tableau de bord. Cela vous aide à résumer les valeurs sur des conditions spécifiques.