Catégoriser le texte dans Google Sheets en fonction du texte partiel correspondant dans l'index des catégories
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
- La feuille de dépenses contient les données telles que nous les recevons, dans les colonnes A à C.
- 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),"")
- 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***ke5
etSEND E-TFR US***xja
. En tant que tel, nous aimerions faire correspondre uniquement le texteSEND 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
Dans ce cas, VLOOKUP
il 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 FUNDS
doivent ê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:A3
sont les cellules contenant les clés qui doivent faire une correspondance complète etCategories!B1:B3
sont les catégories correspondantesCategories!A4:A8
sont les cellules contenant les clés qui devraient faire une correspondance partielle etCategories!B4:B8
sont les catégories correspondantes
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!