[Résolu]Compréhension d'une formule de saisie semi-automatique

[mamyfik]

Bonjour à tous!

J'ai une formule de saisie semi-automatique que j'ai trouvé je ne sais plus où sur internet. Elle marche parfaitement bien, cependant, j'ai un peu de mal à la comprendre totalement. Voici cette formule :

=SI(F7<>"";DECALER(Bd_Nom;EQUIV(F7&"*";Bd_Nom;0)-1;;SOMMEPROD((STXT(Bd_Nom;1;NBCAR(F7))=TEXTE(F7;"0"))*1));Bd_Nom)

A savoir qu'elle se trouve dans "Source" de "Validation des données" d'une liste. Bd_Nom fait référence à une colonne d'un tableau ou se trouve les noms de clients. Ce que j'ai du mal à comprendre c'est SOMMEPROD((STXT(Bd_Nom;1;NBCAR(F7))=TEXTE(F7;"0"))*1) autrement dit, presque toute la formule .

Si quelqu'un voulait bien éclairer ma lanterne, je lui en serais reconnaissant!

++

EDIT : Petite précision : pour ceux qui serait intéressé par cette formule et qui voudrais l'utiliser, sachez que pour son bon fonctionnement, la liste doit être préalablement classée par ordre alphabétique.

 

[Dranreb]

Re : Compréhension d'une formule de saisie semi-automatique

Bonjour.
C'est la raison pour laquelle je n'utilise guère de formules un peu longues rivalisant d'ingéniosité, je préfère m'écrire des fonctions personnalisées plus simple à relire. Voyons voir.
SOMMEPROD((STXT(Bd_Nom;1;NBCAR(F7))=TEXTE(F7;"0")) *1)
Soit n le nombre de caractères du contenu de F7, compare les n 1ers caractères de chaque Bd_Nom avec une forme normalisée de ce contenu de F7, le transforme les résultats booléens en valeur 0 ou 1 pour en faire la somme afin de les compter.
À+

 

[mamyfik]

Re : Compréhension d'une formule de saisie semi-automatique

Bonjour Dranreb.

Merci beaucoup pour ta réponse, elle avance grandement ma compréhension.

Je suis d'accord avec toi sur le fait d'utiliser des fonctions que l'on fait soi-même, je préfère aussi. Cependant, j'ai trouvé celle-ci un peu par hasard, je l'ai testé et trouvé très pratique!

Toutefois, il y a encore quelque chose que je ne comprends pas : la référence de STXT() ne doit pas être une seule cellule? Là on lui passe une plage de cellule, et je ne comprends pas comment il se débrouille en fait. Si tu pouvais m'expliquer cela aussi, ce serait cool

 

[Tibo]

Re : Compréhension d'une formule de saisie semi-automatique

Bonjour le fil,

=SI(F7<>"";DECALER(Bd_Nom;EQUIV(F7&"*";Bd_Nom;0)-1;;SOMMEPROD((STXT(Bd_Nom;1;NBCAR(F7))=TEXTE(F7;"0"))*1));Bd_Nom)

Ici, la fonction DECALER permet d'extraire de ta liste Bd_Nom une sous-liste des données commençant par le contenu de F7.

EQUIV(F7&"*";Bd_Nom;0)-1 :

permet de repérer le rang de la 1ère donnée qui commence par le contenu de F7

SOMMEPROD((STXT(Bd_Nom;1;NBCAR(F7))=TEXTE(F7;"0"))*1)) :

permet de compter le nombre de données qui commencent par le contenu de F7

Si F7 ne contient rien, la liste déroulante prend en compte Bd_Nom au complet.

En intégrant le tout dans un DECALER, ça permet d'avoir une liste déroulante des données commençant par le contenu de F7


On peut légèrement adapter la formule :

STXT(Bd_Nom;1;NBCAR(F7))
est équivalent à :
GAUCHE(Bd_Nom;NBCAR(F7))


TEXTE(F7;"0")
est équivalent à :
F7&""

Ce qui permet de modifier la formule ainsi :

=SI(F7<>"";DECALER(Bd_Nom;EQUIV(F7&"*";Bd_Nom;0)-1;;SOMMEPROD((GAUCHE(Bd_Nom;NBCAR(F7))=F7&"")*1));Bd_Nom)

@+

 

[Monique]

Re : Compréhension d'une formule de saisie semi-automatique

Bonjour,

On peut faire plus court, avec
NB.SI(Bd_Nom;F7&"*")))
"*" remplaçant n'importe quel(s) caractère(s)

ça donne :
=SI(F7<>"";DECALER($A$1;EQUIV(F7&"*";Bd_Nom;0);;NB.SI(Bd_Nom;F7&"*")))

Pas besoin de Decaler() toute la plage, Decaler(A1; etc) suffit

Mais cette formule ne propose aucun choix si la cellule est vide
Avec celle-ci, on a tous les noms :
=SI(F7<>"";DECALER($A$1;EQUIV(F7&"*";Bd_Nom;0);;NB.SI(Bd_Nom;F7&"*"));Bd_Nom)

 

[Dranreb]

Re : Compréhension d'une formule de saisie semi-automatique

la référence de STXT() ne doit pas être une seule cellule?

Si. Mais la fonction SOMMEPROD à cette propriété extraordinaire de prétraiter ses paramètres comme une formule matricielle:
si une expression demandant une valeur unique se voit confiée une liste de valeurs, il fabrique... une liste d'autant de valeurs uniques traitées chacune par l'expression !
À+

 

[mamyfik]

Re : Compréhension d'une formule de saisie semi-automatique

Si. Mais la fonction SOMMEPROD à cette propriété extraordinaire de prétraiter ses paramètres comme une formule matricielle:
si une expression demandant une valeur unique se voit confiée une liste de valeurs, il fabrique... une liste d'autant de valeurs uniques traitées chacune par l'expression !
À+

!! Mais genre !! Impressionnant !! Je ne savais pas. Merci de me l'apprendre

Bonjour à Tibo et Monique et merci beaucoup pour vos explications
Je ne m'attendais pas à ce que quelqu'un me fasse carrément un fichier excel démo! Merci! Et c'est vrai qu'avec le NB.SI c'est plus simple, je n'y avais pas pensé!

En tout cas merci à tous, grâce à vous j'ai tout pigé