Logo GNT
Actualités Tests & Guides Bons Plans
GTA 6 iPhone 17 Copilot Switch 2 Temu ChatGPT Tesla
OVH Cloud OVH Cloud
Entraide Applications Microsoft Office Excel Validation formule trop long

Validation formule trop long

6 réponses
Avatar
denis P
Bonjour à tous le monde,
une ch'tit questions pour bien finire l'année
J'ai une formule matricielle qui me fait une liste sans doublon depuis une
liste avec doublon...

=SI(ESTERREUR(PETITE.VALEUR(SI(EQUIV(Liste_Produit;Liste_Produit;0)=LIGNE(IN
DIRECT("1:"&LIGNES(Liste_Produit)));EQUIV(Liste_Produit;Liste_Produit;0);"")
;LIGNE(INDIRECT("1:"&LIGNES(Liste_Produit)))));"";INDEX(Liste_Produit;PETITE
.VALEUR(SI(EQUIV(Liste_Produit;Liste_Produit;0)=LIGNE(INDIRECT("1:"&LIGNES(L
iste_Produit)));EQUIV(Liste_Produit;Liste_Produit;0);"");LIGNE(INDIRECT("1:"
&LIGNES(Liste_Produit))))))

Cette fonction roule impeccablement dans une cellule, par contre elle est
bien trop longue pour définir nom de plage qui me servirait de liste de
validation.
Y a il un moyen de contourner ce pb

merci
ET BON REVELLION
denis p.
Signaler un problème avec ce contenu

6 réponses

Supprimer
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire

Veuillez sélectionner un problème

Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
Avatar
FxM
Bonjour à tous le monde,
une ch'tit questions pour bien finire l'année
J'ai une formule matricielle qui me fait une liste sans doublon depuis une
liste avec doublon...

=SI(ESTERREUR(PETITE.VALEUR(SI(EQUIV(Liste_Produit;Liste_Produit;0)=LIGNE(IN
DIRECT("1:"&LIGNES(Liste_Produit)));EQUIV(Liste_Produit;Liste_Produit;0);"")
;LIGNE(INDIRECT("1:"&LIGNES(Liste_Produit)))));"";INDEX(Liste_Produit;PETITE
..VALEUR(SI(EQUIV(Liste_Produit;Liste_Produit;0)=LIGNE(INDIRECT("1:"&LIGNES(L
iste_Produit)));EQUIV(Liste_Produit;Liste_Produit;0);"");LIGNE(INDIRECT("1:"
&LIGNES(Liste_Produit))))))

Cette fonction roule impeccablement dans une cellule, par contre elle est
bien trop longue pour définir nom de plage qui me servirait de liste de
validation.
Y a il un moyen de contourner ce pb

merci
ET BON REVELLION
denis p.




Bonsoir,

Si tu n'est pas allergique aux macros complémentaires, va voir chez le
Chef à plumes http://longre.free.fr pour une mc nommée morefun.xll

De là, tu pourras utiliser =valeurs.uniques(...) qui dédoublonnera et
triera les valeurs.

@+
FxM

Avatar
Daniel.M
Bonjour denis,

========================================== 1.
Mettons que ta liste unique commence en G6, tu te définis un NOM excel qui prend
la plage des cellules non-vides de cette liste.

Donc, tu te définis un NOM (Ctrl-F3 ou Menu Insère/Nom/Définir)
ListeValide
avec la formule suivante (G6 et subséquente):
ÞCALER($G$6;0;0;LIGNES($G$6:$G$800)-NB.VIDE($G$6:$G$800);1)

Ensuite, pour ta cellule de validation, tu mets
=ListeValide
dans la Liste autorisée.


========================================== 2.
Ceci étant dit, ta formule pour trouver les valeurs uniques est plutôt longue.
Essaie en G6:
=INDEX(Liste_Produit;1)

Puis en G7, la matricielle suivante:
=SI(ESTNA(EQUIV(0;NB.SI($G$6:G6;Liste_Produit);0));"";
INDEX(Liste_Produit;EQUIV(0;NB.SI($G$6:G6;Liste_Produit);0)))

Tu copies G7 jusqu'en G800 (ou jusqu'à la cellule que tu veux pour contenir le
maximum de valeurs uniques).
==========================================
Salutations,

Daniel M.

"denis P" wrote in message
news:
Bonjour à tous le monde,
une ch'tit questions pour bien finire l'année
J'ai une formule matricielle qui me fait une liste sans doublon depuis une
liste avec doublon...

=SI(ESTERREUR(PETITE.VALEUR(SI(EQUIV(Liste_Produit;Liste_Produit;0)=LIGNE(IN
DIRECT("1:"&LIGNES(Liste_Produit)));EQUIV(Liste_Produit;Liste_Produit;0);"")
;LIGNE(INDIRECT("1:"&LIGNES(Liste_Produit)))));"";INDEX(Liste_Produit;PETITE
.VALEUR(SI(EQUIV(Liste_Produit;Liste_Produit;0)=LIGNE(INDIRECT("1:"&LIGNES(L
iste_Produit)));EQUIV(Liste_Produit;Liste_Produit;0);"");LIGNE(INDIRECT("1:"
&LIGNES(Liste_Produit))))))

Cette fonction roule impeccablement dans une cellule, par contre elle est
bien trop longue pour définir nom de plage qui me servirait de liste de
validation.
Y a il un moyen de contourner ce pb

merci
ET BON REVELLION
denis p.




Avatar
denis P
Bonjour et merci pour ta réponse
Je connais la macro complémentaire du Grand Chef à plume morefun.xll qui
fait ce travaille de façon impec.
Mais mon applique est distribuée sure plusieurs serveur en particulier sur
serveurs TSE, les utilisateur n'ont pas les droits pour écrire dans
marcolib. Donc j'aurai bien aimer un classeur complètement autonome et sans
macro (niveau de sécurité...).
Mais bon, si vraiment pas d'autre solution je vais vais faire avec...
merci encore et
BONNE ANNEE
----------------------------------------------------------------------------
-
Pasquier Denis

Service d'entomologie

Agroscope RAC Changins

Case postale 254

CH-1260 Nyon 1 - SUISSE

Tél. +41 22 363 43 78 Fax ++41 22 363 43 94

E-Mail : denis point

----------------------------------------------------------------------------
-



"FxM" a écrit dans le message de
news:%
Bonjour à tous le monde,
une ch'tit questions pour bien finire l'année
J'ai une formule matricielle qui me fait une liste sans doublon depuis
une


liste avec doublon...


=SI(ESTERREUR(PETITE.VALEUR(SI(EQUIV(Liste_Produit;Liste_Produit;0)=LIGNE(IN



DIRECT("1:"&LIGNES(Liste_Produit)));EQUIV(Liste_Produit;Liste_Produit;0);"")



;LIGNE(INDIRECT("1:"&LIGNES(Liste_Produit)))));"";INDEX(Liste_Produit;PETITE



..VALEUR(SI(EQUIV(Liste_Produit;Liste_Produit;0)=LIGNE(INDIRECT("1:"&LIGNES(


L

iste_Produit)));EQUIV(Liste_Produit;Liste_Produit;0);"");LIGNE(INDIRECT("1:"


&LIGNES(Liste_Produit))))))

Cette fonction roule impeccablement dans une cellule, par contre elle
est


bien trop longue pour définir nom de plage qui me servirait de liste de
validation.
Y a il un moyen de contourner ce pb

merci
ET BON REVELLION
denis p.




Bonsoir,

Si tu n'est pas allergique aux macros complémentaires, va voir chez le
Chef à plumes http://longre.free.fr pour une mc nommée morefun.xll

De là, tu pourras utiliser =valeurs.uniques(...) qui dédoublonnera et
triera les valeurs.

@+
FxM



Avatar
denis P
Salut et merci
les solutions que tu m'a donnée s'approche du bute.

1er solution.
Pb: je ne voudrai pas avoir besoin de placer ma liste unique sur une
feuille, mais seulement la définir
par Menu Insère/Nom/Définir. De plus si je prévoie 5000 ligne pour place
pour ma liste unique et que ma Liste_Produit n'a que 50 lignes cela donne
des #NA (de ln 51 à ln 5000) qui sont pris comme des lignes blanches dans la
liste de validation

2e solution.
Pb: comme pour la 1er solution je ne voudrai pas avoir besoin de placer ma
liste unique sur une feuille, mais seulement la définir.
Cela fonctionne bien mais attention pour les longue liste cela bouf beaucoup
de ressources car chaque cellules est une matrice que se réfère à la
précédente.

merci encore et
BONNE ANNEE
-------------------------------------------------------------------
Pasquier Denis
Service d'entomologie
Agroscope RAC Changins
Case postale 254
CH-1260 Nyon 1 - SUISSE
Tél. +41 22 363 43 78 Fax ++41 22 363 43 94
E-Mail : denis point
----------------------------------------------------------------------------



"Daniel.M" a écrit dans le message de
news:
Bonjour denis,

========================================== > 1.
Mettons que ta liste unique commence en G6, tu te définis un NOM excel qui
prend

la plage des cellules non-vides de cette liste.

Donc, tu te définis un NOM (Ctrl-F3 ou Menu Insère/Nom/Définir)
ListeValide
avec la formule suivante (G6 et subséquente):
ÞCALER($G$6;0;0;LIGNES($G$6:$G$800)-NB.VIDE($G$6:$G$800);1)

Ensuite, pour ta cellule de validation, tu mets
=ListeValide
dans la Liste autorisée.


========================================== > 2.
Ceci étant dit, ta formule pour trouver les valeurs uniques est plutôt
longue.

Essaie en G6:
=INDEX(Liste_Produit;1)

Puis en G7, la matricielle suivante:
=SI(ESTNA(EQUIV(0;NB.SI($G$6:G6;Liste_Produit);0));"";
INDEX(Liste_Produit;EQUIV(0;NB.SI($G$6:G6;Liste_Produit);0)))

Tu copies G7 jusqu'en G800 (ou jusqu'à la cellule que tu veux pour
contenir le

maximum de valeurs uniques).
========================================== >
Salutations,

Daniel M.

"denis P" wrote in message
news:
Bonjour à tous le monde,
une ch'tit questions pour bien finire l'année
J'ai une formule matricielle qui me fait une liste sans doublon depuis
une


liste avec doublon...


=SI(ESTERREUR(PETITE.VALEUR(SI(EQUIV(Liste_Produit;Liste_Produit;0)=LIGNE(IN



DIRECT("1:"&LIGNES(Liste_Produit)));EQUIV(Liste_Produit;Liste_Produit;0);"")



;LIGNE(INDIRECT("1:"&LIGNES(Liste_Produit)))));"";INDEX(Liste_Produit;PETITE



.VALEUR(SI(EQUIV(Liste_Produit;Liste_Produit;0)=LIGNE(INDIRECT("1:"&LIGNES(L



iste_Produit)));EQUIV(Liste_Produit;Liste_Produit;0);"");LIGNE(INDIRECT("1:"


&LIGNES(Liste_Produit))))))

Cette fonction roule impeccablement dans une cellule, par contre elle
est


bien trop longue pour définir nom de plage qui me servirait de liste de
validation.
Y a il un moyen de contourner ce pb

merci
ET BON REVELLION
denis p.








Avatar
Daniel.M
Salut Denis,

Salut et merci


Y'a pas de quoi.

Pb: je ne voudrai pas avoir besoin de placer ma liste unique sur une
feuille, mais seulement la définir
par Menu Insère/Nom/Définir.


Ok. Mais je suis quand même curieux : pourquoi (surtout si c'est une feuille
cachée)?

<snip> ...

Cela fonctionne bien mais attention pour les longue liste cela bouf beaucoup
de ressources car chaque cellules est une matrice que se réfère à la
précédente.


Tu es en face de la quadrature du cercle. Une définition seule (sans l'appui
d'une plage sur une feuille) requérera une formule assez complexe, qui, en
conséquence, te bouffera inévitablement des ressources. "There's no such thing
as a free lunch" :-)


De plus si je prévoie 5000 ligne pour place
pour ma liste unique et que ma Liste_Produit n'a que 50 lignes cela donne
des #NA (de ln 51 à ln 5000) qui sont pris comme des lignes blanches dans la
liste de validation


Chez moi, la formule qui génère ma liste unique (de G6 à G5005) produira des
quasi-vides sur 4990 lignes (+10 valeurs uniques au début, mettons).
De plus, la formule pour ListeVal n'aura pas de lignes blanches.

Salutations,

Daniel M.

Avatar
Monique Brunel
Bonjour,

Bonjour et merci pour ta réponse
Je connais la macro complémentaire du Grand Chef à plume morefun.xll qui
fait ce travaille de façon impec.


Les nouveaux se posent sans doute des questions...
mais cela fait chaud au coeur de voir ainsi évoqué celui qui a marqué à
jamais mpfe :-)

--
Amicalement,
Monique
en campagne pour des sites conformes aux standards et accessibles...
http://www.webmaster-hub.com/ et http://www.opquast.org/
avec des navigateurs évolutifs http://www.mozilla-europe.org/
Pour les amateurs d'athlétisme : http://users.skynet.be/osga/