Catégoriser le texte dans Google Sheets en fonction du texte partiel correspondant dans l'index des catégories

Nov 25 2020

Je cherche à faire correspondre, avec RECHERCHEV, une partie des mots d'une colonne de la feuille principale à un index de mots-clés / catégories où des mots partiels sont stockés pour plus de facilité dans une autre feuille.

Voici un exemple de feuille de calcul: https://docs.google.com/spreadsheets/d/13QrS1nDjTN6oxIoXhvdyoiqAa4lf8x3eSY3566_eTJ8/edit?usp=sharing

  1. La feuille de dépenses contient les données telles que nous les recevons, dans les colonnes A à C.
  2. Dans la colonne D, nous aimerions attribuer automatiquement une catégorie à ces dépenses. Si nous faisons cela avec du texte exact dans la colonne B, par exemple BANK VISA PREAUTH PYMT, alors c'est facile. La formule RECHERCHEV dans la colonne D fonctionne en obtenant cette référence précise à partir de l'onglet «Catégories» (la cellule A1 est formulée exactement de cette façon). Cette formule fonctionne:=IFERROR(VLOOKUP(B2,Categories!A:B,2,FALSE),"")
  3. Le défi est que nos dépenses d'un type similaire ne suivront pas toujours le texte exact. Par exemple: SEND E-TFR US***ke5et SEND E-TFR US***xja. En tant que tel, nous aimerions faire correspondre uniquement le texte SEND E-TFR.

Cela signifie que la RECHERCHEV doit correspondre aux partiels dans la première colonne de l'onglet «Catégories». Comment ajuste la formule RECHERCHEV pour y parvenir?

PS. Ma question est assez similaire à celle-ci avec la distinction vitale de vouloir faire correspondre les sous-chaînes.
Catégoriser les cellules dans les feuilles de calcul Google

EDIT: Répondu.

Réponses

2 Rubén Nov 25 2020 at 11:31

Dans ce cas, VLOOKUPil ne peut pas être utilisé car certaines des catégories kyes sont contenues dans les données à mettre en correspondance, par contre, le cas est plus complexe que la description incluse dans le corps de la question car certaines catégories sont une correspondance partielle entre eux (c'est-à BANK VISA PREAUTH PYMT- dire et Bank Visa) et parce que les matchs doivent être faits sans tenir compte de la casse ( BANK MUTUAL FUNDSdoivent être classés comme Mutual Funds)

Voici la formule proposée

=ArrayFormula(ARRAY_CONSTRAIN(
SPLIT(TRIM(TRANSPOSE(QUERY(TRANSPOSE({
IF(
TRIM(B2:B9)=TRIM(TRANSPOSE(Categories!A1:A3)),Categories!B1:B3&"|",),
TRANSPOSE(IF(IFERROR(SEARCH(TRIM(Categories!A4:A8),TRANSPOSE(TRIM(B2:B9))),),Categories!B4:B8&"|",))
}),,1000000))),"|"),
COUNTA(B2:B9),1))
  • B2:B9 sont les cellules dont les valeurs doivent être comparées aux clés de catégories.

    REMARQUE: il est supposé qu'il n'y aura pas de cellules vides dans cette plage.

  • Categories!A1:A3sont les cellules contenant les clés qui doivent faire une correspondance complète et Categories!B1:B3sont les catégories correspondantes

  • Categories!A4:A8sont les cellules contenant les clés qui devraient faire une correspondance partielle et Categories!B4:B8sont les catégories correspondantes

2 PKHunter Nov 26 2020 at 01:35

Merci à @Ruben pour la réponse. Finalement, nous avons décidé d'adopter une approche plus simple. Juste pour vérifier l'expression régulière de la chaîne de recherche dans RECHERCHEV, et avoir ces sous-chaînes dans les index clés de l'onglet Catégories.

Donc, si une dépense a ce texte dans la cellule B1:

SEND E-TFR US***ke5
SEND E-TFR US***abc
SEND E-TFR US***xyz
...

Ce qui causait des problèmes avec les correspondances exactes, nous tronquons simplement la chaîne à la sous-chaîne qui se produit avant le deuxième espace:

REGEXEXTRACT(B1, "[^\ ]*\ [^\ ]*")

Pour nous donner juste ceci:

SEND E-TFR

Et notre fonction VLOOKUP renouvelée:

=IFERROR(VLOOKUP(TRIM(REGEXEXTRACT(B1, "[^\ ]*\ [^\ ]*")),Categories!A:B,2,FALSE), "")

L'index de clé de catégories fait alors référence à ce texte tronqué pour la recherche:

Ce qui correspond donc et renseigne la bonne catégorie:

Un fichier de test pour voir ce fonctionnement est: https://docs.google.com/spreadsheets/d/13QrS1nDjTN6oxIoXhvdyoiqAa4lf8x3eSY3566_eTJ8/edit?usp=sharing

Merci!