J'espère que vous pourrez m'aider.
J'ai un ensemble de données qui comprend un champ de commentaires en texte libre; dans ce champ, il y a des numéros de bon de travail saisis que je veux compter - les numéros de WO sont TOUJOURS 8 chiffres et je pensais que je l'avais craqué avec ceci:
= ROUNDDOWN (SUMPRODUCT (LEN (M7) -LEN (SUBSTITUTE (M7, {0,1,2,3,4,5,6,7,8,9}, ""))) / 8,0 ) qui identifie correctement les numéros de WO dans un commentaire comme celui-ci:
NWMS - 67431710 - Mattress eta pending from contractor NWMS - 67431797 - Double base eta pending from contractor
et renvoie un compte de 2 (c'est une cellule unique avec des commentaires sur plusieurs lignes).
Aussi; à cause de la formule ROUNDDOWN ; un commentaire comme celui-ci:
CMS - 67630485 odeur De sortie de déchets ou de blocage eta 05.02 ne renvoie qu'un décompte de 1 WO (ce qui est correct)
Cependant; le nouveau problème auquel je suis confronté est lorsqu'une date ETA est ajoutée qui comporte également 8 chiffres - par exemple 07/02/2020 - cela ajoute incorrectement 1 au nombre de numéros WO.
Je suppose que je suis après une façon de compter des blocs de 8 nombres contigus dans une chaîne de texte plus grande. Cela exclura tous les autres chiffres, quelle que soit la manière dont ils sont entrés.
Je travaille dans Google Sheets mais je comprends mieux Excel, donc une formule pour l'un ou l'autre m'aidera car je devrais pouvoir le convertir dans les deux sens.
4 Réponses :
Vous pouvez essayer d'utiliser des expressions régulières (dans Google Sheets) pour faire correspondre un nombre à huit chiffres.
Par exemple, ce qui suit remplacera tous les groupes de nombres à huit chiffres par @ .
=len(REGEXREPLACE(M7,"\d{8}","@"))-len(SUBSTITUTE(REGEXREPLACE(M7,"\d{8}","@"),"@",""))
Et puis vous pouvez utiliser la méthode len (M7) - len (substitute (... pour compter le nombre d'occurrences de huit chiffres nombres dans la chaîne.
La formule finale ressemblerait à quelque chose comme
=REGEXREPLACE(M7,"\d{8}","@")
Cela a l'air génial; Je suppose que la principale différence entre REGEXREPLACE et SUBSTITUTE est que vous pouvez rechercher des blocs ({8}) plutôt qu'un seul chiffre, ce qui est exactement ce dont j'avais besoin. Par intérêt, existe-t-il un équivalent Excel?
Je ne pense pas. Je n'utilise plus vraiment Excel. Donc, si une nouvelle formule était récemment introduite, je ne le saurais pas. :-)
@AlanEnglefield Vous devez écrire un UDF en VBA pour pouvoir utiliser des expressions Regex sur la feuille de calcul.
@RonRosenfeld Est-ce quelque chose que vous pouvez conseiller? Également; irait-il avec le fichier si je l'envoyais par e-mail à quelqu'un d'autre? Merci
@AlanEnglefield Regardez la réponse acceptée pour Comment utiliser les expressions régulières (Regex) dans Microsoft Excel à la fois dans la cellule et dans les boucles . En ce qui concerne la distribution, vous voudrez probablement changer la recommandation de la liaison anticipée à la liaison tardive, afin que vos utilisateurs n'aient pas à cocher la référence.
Dans MS Excel, vous pouvez utiliser la fonction FILTERXML pour obtenir le résultat nécessaire:
=SUMPRODUCT(ISNUMBER(FILTERXML("<data><a>" & SUBSTITUTE(SUBSTITUTE(A5,":","_")," ","</a><a>") & "</a></data>","//a"))*(LEN(FILTERXML("<data><a>#" & SUBSTITUTE(A5," ","</a><a>#") & "</a></data>","//a"))=9))
Seuls les espaces doivent être sans duplication.
Modification n ° 1:
Solution pour les zéros non significatifs et les espaces en double:
=SUMPRODUCT(ISNUMBER(FILTERXML("<data><a>" & SUBSTITUTE(A1," ","</a><a>") & "</a></data>","//a"))*(LEN(FILTERXML("<data><a>#" & SUBSTITUTE(A1," ","</a><a>#") & "</a></data>","//a"))=9))
Modification n ° 2:
Solution pour exclure également le temps:
=SUMPRODUCT(ISNUMBER(FILTERXML("<data><a>" & SUBSTITUTE(A1," ","</a><a>") & "</a></data>","//a"))*(LEN(FILTERXML("<data><a>" & SUBSTITUTE(A1," ","</a><a>") & "</a></data>","//a"))=8))
En raison de la manière dont FILTERXML gère les données numériques, cette méthode retournera des résultats peu fiables si les nombres WO ont des zéros non significatifs.
@Ron Rosenfield Oui, mais cela peut être facilement résolu. Édité.
@basic C'est génial - merci cependant; J'ai utilisé votre exemple édité sur ce qui suit et j'obtiens un compte de 2 ?? 'mde - 67832405 Nouveau canon temporaire requis - serrure saisie, ne peut pas accéder à la salle - 09-févr.-2020 08:55:43' est-ce à cause de l'élément temps?
Oui, c'est à cause de l'élément temps. Eh bien, nous pouvons ajouter un autre remplacement - remplacer ":" par "_" dans le test ISNUMBER. Édité.
Merci @basic mais malheureusement; vos formules ne contiennent aucun numéro de bon de travail auquel sont attachés des tirets. Pour autant que je comprends vos formules, ils recherchent un bloc de 8 chiffres entouré d'espaces mais, comme celui-ci est alimenté par un champ de texte libre, ce n'est pas toujours le cas: o (
Alternative plus courte à Google Sheets:
=INDEX(COUNTIF(SPLIT(REGEXREPLACE(A1, "\d{8}", "♦♥"), "♦"), "*♥*"))
Puisque vous avez posé des questions sur l'utilisation d'expressions régulières dans Excel, voici une simple UDF qui renverra un Count de Pattern dans String .
Il doit être portable vers d'autres versions de Windows Excel. Cela ne fonctionnera pas sur Mac Excel car il ne dispose pas de la dll requise
=REGEXCount(cell_ref or string,"\b\d{8}\b")
Vous pouvez l'utiliser sur votre feuille de calcul comme:
Option Explicit
Function REGEXCount(S As String, sPattern As String) As Long
Dim RE As Object, MC As Object
Set RE = CreateObject("vbscript.regexp")
With RE
.Global = True
.MultiLine = True
.Pattern = sPattern
Set MC = .Execute(S)
End With
REGEXCount = MC.Count
End Function
merci @Ron qui fonctionne un régal - le premier modèle que vous avez cité compte également les numéros WO précédés ou suivis d'un tiret aussi. Je ne sais pas si je peux distribuer un fichier Excel compatible avec les macros au sein de notre organisation, mais votre réponse fonctionne !! Merci encore
@AlanEnglefield Oui, les deux le feront car un tiret ou un trait d'union est un caractère non-mot. Est-ce que c'est ce que tu veux? ou non? Sinon, veuillez fournir des exemples de données et vos résultats attendus.
c'est exactement ce que je voulais. Fondamentalement, je dois extraire le nombre de bons de travail cachés parmi tous les commentaires et ETA, quelle que soit la façon dont ils apparaissent (et étant donné qu'il s'agit d'un champ de texte libre; ils sont généralement entourés d'une charge de guff qui Je n'ai pas besoin haha) merci encore