[Fastoche] Formule comptant nb de cellules contenant texte + numérique

[Rkid75]

Bonjour à tous,

Je bloque un peu avec mon besoin de formule qui semble pourtant très bête. Ce que je veux faire :

Dans une plage de données (Colonne_Nom) je veux compter le nombre de cellules qui contient à la fois du texte et du numérique

Exemple :

Plage de données
1
AB
Papa
Bob
MEE01
SER09
Didier
(jdjd)
334444

Résultat attendu de la formule = 2 (les lignes en rouge)

Je précise que les cellules contenant uniquement des numériques ne m'intéressent pas, je les compte facilement par ailleurs.

Merci d'avance

 

[MJ13]

Re : [Fastoche] Formule comptant nb de cellules contenant texte + numérique

Bonjour Rikid75

En formule, cela me paraît complexe à faire (enfin pour moi ).

Sinon en macro, cela doit être faisable, mais pas si fastoche .

Je précise que les cellules contenant uniquement des numériques ne m'intéressent pas, je les compte facilement par ailleurs.

Et comment fais-tu .

 

[Rkid75]

Re : [Fastoche] Formule comptant nb de cellules contenant texte + numérique

Merci pour ta réponse.

Alors la macro j'y avais pensé mais je souhaite l'éviter au max (derniers recours)

Pour les numériques je fais =NB(Colonne_Nom) tout simplement

 

[Misange]

Re : [Fastoche] Formule comptant nb de cellules contenant texte + numérique

Bonjour

Non ce n'est pas si trivial car à partir du moment ou tu mélanges des chiffres et des lettres dans une cellule, excel considère que c'est du texte.
=NB(SI(A1:A9;A1:A9))
en validation matricielle te donne le nombre de cellules contenant des nombres seulement = 2 dans ton exemple, ce que fait tout aussi bien nb(A1:A9) comme tu l'as signalé,

=SOMMEPROD((ESTTEXTE(A1:A9)*1))
te donne le nombre de cellules contenant du texte soit 7 dans ton exemple mais les deux cellules que tu cherches à décompter sont au milieu de celles-ci.
Il faudrait tester la présence des chiffres et ajouter ce test dans le sommeprod (pas le temps maintenant !)

 

[JBOBO]

Re : [Fastoche] Formule comptant nb de cellules contenant texte + numérique

Bonjour,

Un essai par formule, avec une colonne intermediaire (pouvant etre masqué).

 

[TempusFugit]

Re : [Fastoche] Formule comptant nb de cellules contenant texte + numérique

Bonjour


Une solution avec une colonne en plus et une formule matricielle
en colonne B, saisir

=ET(NB(1*STXT(A1;LIGNE($1:$9);1))>0;ESTTEXTE(A1)*1)

A valider avec CTRL+MAJ+ENTREE et à recopier vers le bas.

en C1: =NB.SI(A1:A9;VRAI) affiche 2

 

[Rkid75]

Re : [Fastoche] Formule comptant nb de cellules contenant texte + numérique

Bonjour,

Un essai par formule, avec une colonne intermediaire (pouvant etre masqué).

Merci ! Je vois bien le principe mais j'ai un problème avec le fichier que tu as fourni.
Dès que je modifie une cellule des données, la cellule valeur affiche #NOM?

Il y a peut etre un paramètre d'excel à modifier...

Bonjour


Une solution avec une colonne en plus et une formule matricielle
en colonne B, saisir

=ET(NB(1*STXT(A1;LIGNE($1:$9);1))>0;ESTTEXTE(A1)*1)

A valider avec CTRL+MAJ+ENTREE et à recopier vers le bas.

en C1: =NB.SI(A1:A9;VRAI) affiche 2

Merci mais bon j'avoue, je comprends pas vraiment. En fait je suis bien plus nul ce que je croyais.
Peux tu aller plus loin dans ton illustration stp ?

 

[TempusFugit]

Re : [Fastoche] Formule comptant nb de cellules contenant texte + numérique

Bonjour


Admettons que tes données ( celles fournies en exemple dans ton premier message) sont dans les cellules A1:A9
Dans la cellule B1, tu saisis la formule : =ET(NB(1*STXT(A1;LIGNE($1:$9);1))>0;ESTTEXTE(A1)*1)
A la fin de la saisie, tu appuies sur les touches CTRL MAJ ENTREE en même temps
Puis tusélectionnes B1 et tu recopies la formule jusqu'en B9
Ensuite en C1, tu saisis cette formule: =NB.SI(A1:A9;VRAI) et tu appuies sur ENTREE.

Post scriptum; si tu n'y arrives pas, je te mets ci-dessous une macro pour te créer un exemple
en partant de tes explications de ton premier message.
Pour créer cet exemple avec la macro, voila comment faire:
Dans un nouveau classeur, aller dans Outils/Macros/Microsoft Visual Basic Editor
Puis faire Insertion/Module et copier dans le fenetre de droite la macro ci-dessous
Revenir dans Excel puis faire Outils/Macros/ Sélectionner exemple puis cliquer sur Exécuter
Cela te crééera un exemple avec les formules et tu obtiendras 2 comme résultat en C1

Sub exemple() 
Range("A1:A5") = Application.Transpose(Array(1, "AB", "MEE01", "SER09", "Didier"))
Range("B1").FormulaArray = _
        "=AND(COUNT(1*MID(RC[-1],ROW(R1:R9),1))>0,ISTEXT(RC[-1])*1)"
Range("B1:B5").FillDown
Range("C1").FormulaR1C1 = "=COUNTIF(RC[-1]:R[4]C[-1],TRUE)"
End Sub

 

[Rkid75]

Re : [Fastoche] Formule comptant nb de cellules contenant texte + numérique

Ok on avance !! Je comprends tout mais j'ai juste un soucis sur la formule colonne B
Elle fonctionne uniquement lorsque la chaine de caractère démarre par un numérique.

ça donne :

1MEE -> VRAI (OK)
MEE1 -> FAUX (KO)

 

[Misange]

Re : [Fastoche] Formule comptant nb de cellules contenant texte + numérique

Impossible d'éviter une colonne supplémentaire dans la mesure ou dans la colonne B c'est une formule matricielle qu'on entre dans chaque cellule et qu'ensuite on évalue les résultats de cette colonne avec sommeprod qui est en fait une formule matricielle.
Mais ceci fonctionne :
en colonne B
=EQUIV(VRAI;ESTNUM(1*STXT(A1;LIGNE($1:$20);1));0) à valider en matriciel
et ou tu veux dans ta feuille
=SOMMEPROD(ESTNUM(C1:C9)*1)-NB(A1:A9)

Si tu as plus de 20 caractères dans tes codes il faut que tu ajustes ligne($1:$20)

 

[Rkid75]

Re : [Fastoche] Formule comptant nb de cellules contenant texte + numérique

OK, qu'entends tu par "valider en matriciel" ?

 

[Misange]

Re : [Fastoche] Formule comptant nb de cellules contenant texte + numérique

Tu appuies simultanément sur les touches Maj ctrl et entrée.

pour t'affranchir du problème du nombre de caractères contenu dans ta cellule tu peux utiliser plutôt cette formule dans la colonne B
=EQUIV(VRAI;ESTNUM(1*STXT(A1;LIGNE(INDIRECT("1:"& NBCAR(A1)+1));1));0)

(toujours à valider en matriciel !)

EDIT : le + 1 est une erreur :
=EQUIV(VRAI;ESTNUM(1*STXT(A1;LIGNE(INDIRECT("1:"& NBCAR(A1)));1));0)

 

[JBOBO]

Re : [Fastoche] Formule comptant nb de cellules contenant texte + numérique

Bonjour,

Un essai par formule indigeste à cause des gestions d'erreurs : remplace la formule en C2 de mon précedent post par :

=SI(ESTERREUR(CNUM(SI(ESTNUM(B2);"";SI(ESTERREUR(TROUVE(CAR(48);B2));"";TROUVE(CAR(48);B2))&SI(ESTERREUR(TROUVE(CAR(49);B2));"";TROUVE(CAR(49);B2))&SI(ESTERREUR(TROUVE(CAR(50);B2));"";TROUVE(CAR(50);B2))&SI(ESTERREUR(TROUVE(CAR(51);B2));"";TROUVE(CAR(51);B2))&SI(ESTERREUR(TROUVE(CAR(52);B2));"";TROUVE(CAR(52);B2))&SI(ESTERREUR(TROUVE(CAR(53);B2));"";TROUVE(CAR(53);B2))&SI(ESTERREUR(TROUVE(CAR(54);B2));"";TROUVE(CAR(54);B2))&SI(ESTERREUR(TROUVE(CAR(55);B2));"";TROUVE(CAR(55);B2))&SI(ESTERREUR(TROUVE(CAR(56);B2));"";TROUVE(CAR(56);B2))&SI(ESTERREUR(TROUVE(CAR(57);B2));"";TROUVE(CAR(57);B2)))));"";CNUM(SI(ESTNUM(B2);"";SI(ESTERREUR(TROUVE(CAR(48);B2));"";TROUVE(CAR(48);B2))&SI(ESTERREUR(TROUVE(CAR(49);B2));"";TROUVE(CAR(49);B2))&SI(ESTERREUR(TROUVE(CAR(50);B2));"";TROUVE(CAR(50);B2))&SI(ESTERREUR(TROUVE(CAR(51);B2));"";TROUVE(CAR(51);B2))&SI(ESTERREUR(TROUVE(CAR(52);B2));"";TROUVE(CAR(52);B2))&SI(ESTERREUR(TROUVE(CAR(53);B2));"";TROUVE(CAR(53);B2))&SI(ESTERREUR(TROUVE(CAR(54);B2));"";TROUVE(CAR(54);B2))&SI(ESTERREUR(TROUVE(CAR(55);B2));"";TROUVE(CAR(55);B2))&SI(ESTERREUR(TROUVE(CAR(56);B2));"";TROUVE(CAR(56);B2))&SI(ESTERREUR(TROUVE(CAR(57);B2));"";TROUVE(CAR(57);B2)))))

 

[TempusFugit]

Re : [Fastoche] Formule comptant nb de cellules contenant texte + numérique

Bonjour de nouveau

ça donne :

1MEE -> VRAI (OK)
MEE1 -> FAUX (KO)

Avec une validation matricielle (CTRL+MAJ+ENTREE comme expliqué dans mon précédent message) , VRAi est renvoyé dans les deux cas

Voir ci-dessous

Sub testOK()
[A1] = "1MEE"
[A2] = "MEE1"
Range("B1").FormulaArray = _
        "=AND(COUNT(1*MID(RC[-1],ROW(R1:R9),1))>0,ISTEXT(RC[-1])*1)"
Range("B1:B2").FillDown
End Sub

 

[Misange]

Re : [Fastoche] Formule comptant nb de cellules contenant texte + numérique

En complément, explications de ma proposition qui me parait plus simple que la tienne JBOBO

si en A1 tu as un texte de 9 caractères,
LIGNE(INDIRECT("1:"& NBCAR(A1)));1)
renvoie ligne(1:9)
stxt(A1;ligne(1:9);1) validé en matriciel renvoie un tableau (virtuel) qui répartit chacun des caractères dans une cellule (toujours virtuelle).
Donc à partir de la cellule A1 tu génères un tableau virtuel contenant 9 valeurs
On multiplie chacune de ces cellules virtuelles par 1. une lettre ou autre caractère non numérique1 donne une erreur alors qu'un chiffre multiplié par 1 donne un chiffre.
Estnum(... transforme alors le résultat de chacune de ces cellules virtuelles en vrai ou faux suivant que le résultat est numérique ou pas.
=equiv(vrai; ... recherche alors dans notre tableau virtuel la position du premier vrai

si ta cellule A1 ne contient que des lettres, le tableau virtuel ne contient que des faux
si elle contient des chiffres, cela renvoie la position du premier chiffre.

La formule finale sommeprod est aussi en fait une formule matricielle mais qui par exception n'a pas besoin d'être validée par ctrl maj entrée.

=SOMMEPROD(ESTNUM(C1:C9)*1)-NB(A1:A9)
Comme l'indique son nom sommeprod fait la somme des produits. Pour chaque cellule de C1à C9, on teste si on a une valeur numérique (estnum). Ca renvoie vrai ou faux, résultat qu'on multiplie par 1 ce qui transforme le résultat en 0 (faux) ou 1. Et ensuite on fait la somme de ces résultats intermédiaires. Ce qui dans notre cas donne 4 : les 2 cellules ne contenant que des nombres et les 2 cellules qu'on recherche qui contiennent un mélange de lettres et de nombres. Il faut donc ensuite soustraire du résultat le nombre de cellules ne contenant que des nombres ce qu'on obtient par nb(A1:A9).