Application d'une MFC + plage nommée

[arthurho]

Bonjour,

Je cherche à colorier en bleu les cellules de la colonne 1 onglet Feuil1, lorsque la cellule étudiée de cette colonne possède la chaine de caractère présente dans l'onglet Feuil2 colonne 1.

La MFC fonctionne pour des lettres simples, avec le code suivant :

=NB.SI(MesValeurs;A1)>0

Mais en le compliquant un peu ..

=NB.SI(MesValeurs;STXT(A1; CHERCHE(MesValeurs; A1;1); NBCAR(MesValeurs)))>0

Celui ci ne fonctionne plus,

Avez vous une solution ?

Cdt,

Arthur HO.

 

[CISCO]

Re : Application d'une MFC + plage nommée

Bonjour

Ca y est, j'ai trouvé l'origine du problème :

Tu as écrit :

SOMME(ESTNUM(CHERCHE("*"&SUBSTITUE('Big Tables'!A$2:A$200;"*";"")&"*";'G10R0 queries'!A2))*1)

Or, en dessous de la ligne 'Big Tables'!A$61, tu n'as que des cellules vides. Donc la partie SUBSTITUE(....;" ";"") rennvoie "", et CHERCHE("*"&""&"*") renvoie toujours un numéro ("**" représente n'importe quel texte), donc SOMME renvoie toujours un nombre > 0, donc toutes les lignes sont colorées...

Par conséquent, pour ne pas prendre les cellules vides en dessous de A61, il faut écrire :

SOMME(ESTNUM(CHERCHE("*"&SUBSTITUE(Mesvaleurs;" ";"")&"*";'G10R0 queries'!A2))*1)

De plus, la plage Mesvaleurs doit être définie avec des $, pour bien prendre en compte toutes les valeurs de la plage A2:A61, quelle que soit la cellule active en cours.
Donc Mesvaleurs =DECALER('Big Tables'!$A$2;;;NBVAL('Big Tables'!$A$2:$A$200))

Pour comprendre l'utilité de ces 2 $ en plus, supprime les, place toi dans la colonne A, mais pas en A2, et regarde la définition que propose excel de Mesvaleurs.

J'ai supprimé les autres mises en forme conditionnelles pour y gagner en rapidité.

@ plus

 

[arthurho]

Re : Application d'une MFC + plage nommée

Bonjour,

Merci pour tout CISCO, j'étais moi même en train de trouver un moyen pour trouver la dernière ligne non vide , pour faire le substitute. En fait cest donc NBVAL qui permet de trouver le nombre de ligne où les cellules sont non vides.

Une dernière question, histoire de savoir si ce genre de méthode est possible :

est ce que cela aurait été possible de transformer ta formule matricielle :

SOMME(ESTNUM(CHERCHE("*"&SUBSTITUE(Feuil2!A$1:A$60;"*";"")&"*";Feuil1!A2))*1)
=>
SOMME(ESTNUM(CHERCHE("*"&SUBSTITUE(Feuil2!A$1:A$MAX(SI(ESTVIDE(Feuil2!A1:A200);0;LIGNE(Feuil2!A1:A200)));" ";"")&"*";Feuil1!A2))*1)

Pour faire la substitution sur les lignes non vides uniquement,
Ce code provoque une erreur

Cdt,

Arthur HO.

 

[CISCO]

Re : Application d'une MFC + plage nommée

Bonjour

Il faudrait passer par INDIRECT, comme dans par exemple :

SOMME(ESTNUM(CHERCHE("*"&SUBSTITUE(INDIRECT("Big-Tables!A$1:A$"&MAX(SI(ESTVIDE('Big-Tables'!A$1:A$200);0;LIGNE(A$1:A$200))));" ";"")&"*";A2))*1)

, toujours en matriciel.

Toutefois, cela ne fonctionne pas.

Pourquoi ne passes tu pas par Mesvaleurs ? Dans la définition de cette plage nommée, la fonction DECALER permet de ne prendre en compte que les cellules non vides grace à NBVAL. Bien sûr, cela ne couvre la bonne plage que si il ni a pas de cellule vide dans le haut de la liste, dans ton exemple de A2 à A61. DECALER('Big-Tables'!$A$2;;;NBVAL('Big-Tables'!$A$2:$A$200)) donne DECALER('Big-Tables'!$A$2;;;60), ce qui devient 'Big-Tables'!$A$2:$A$61. N'importe comment, il ne faut pas de cellule vide dans le haut de ta liste (à cause du problème signalé dans mon précédent poste avec "*"&""&"*").

On peut aussi faire avec

=DECALER('Big-Tables'!$A$2;;;EQUIV("zz";'Big-Tables'!$A$2:$A$200;1))

@ plus

 

[arthurho]

Re : Application d'une MFC + plage nommée

Bonjour CISCO;

Oui je vais passer par mes valeurs, je me demandais juste si une telle syntaxe pouvait marcher.

En ce qui concerne le nommage de la plage MesValeurs à savoir :

=DECALER('Big Tables'!$A$2;;;NBVAL('Big Tables'!$A$2:$A$200))

Je pensais, pour optimiser la recherche des tables, à mémoriser les valeurs trouvées pour effectuer la recherche sur la plage diminuée des valeurs trouvées. La recherche serait alors beaucoup plus rapide.
Est ce que MesValeurs peut être considéré comme un tableau, pour sauvegarder les éléments trouvés (MesValeurs) dans une autre plage que j'utiliserais avant d'effectuer une recherche ?

Cest la seule optimisation qui me vient à l'esprit
Qu'en penses tu ?
Cdt,
Arthur HO.

 

[CISCO]

Re : Application d'une MFC + plage nommée

Bonjour

Le meilleur moyen pour accélérer ces calculs, ce serait de faire tout cela avec une macro.

Pour ce qui est de la solution que tu proposes, je ne sais si cela fera gagner beaucoup de temps. J'essaye de mon coté, et je reviens sur le forum si je trouve quelque chose.

@ plus

 

[CISCO]

Re : Application d'une MFC + plage nommée

Bonjour

As tu l'impression que cela va plus vite avec le fichier ci-joint ?

@ plus

 

[arthurho]

Re : Application d'une MFC + plage nommée

Bonjour CISCO,

Jai pu copier toutes les requêtes SQL (au nombre de 110) dans le fichier, prises en compte par les formules matricielles, sans que le fichier plante.
Après de l'attente lors de l'ajout d'une table, la modification est bien effectuée.
Le fichier est consultable sans aucun problème si on continue a utiliser le calcul intermédiaire.

Bref, cest génial
Qu'as tu fait dans l'onglet 2 ? Je tenvoie le fichier complet.

Merci encore pour le temps que tu prends

Arthur HO.

 

[CISCO]

Re : Application d'une MFC + plage nommée

Bonsoir

Sur le second onglet, je met en évidence, avec un 1, les textes de la colonne A contenus dans la colonne A de l'onglet 1.
En colonne C, je regroupe les textes de la colonne A ayant un 1 sur la ligne.

Autrement dit, je regroupe en colonne C les textes de la colonne A de l'onglet 2 cités dans la colonne A de l'onglet 1.

La MFC est faite avec mesvaleurs2 qui ne contient que les textes de la colonne C.

Les calculs sont qu'en même longs, mais bon, par rapport aux ordi à cartes des années 70 ...

Tu peux regrouper les deux MFC rouge dans une seule ligne en écrivant en dessous de "s'applique à" =$A$2:$A$200;$I$2:$I$200

@ plus

 

[arthurho]

Re : Application d'une MFC + plage nommée

Bonjour CISCO,

Puisque le test est fait sur MesValeurs2, je vais pouvoir supprimer la colonne de l'onglet 1 pour alléger le fichier.

Au niveau des formules :

=SI(C$1<LIGNES(C$1:C2);"";INDEX(A$1:A$200;PETITE.VALEUR(SI(B$2:B$200=1;LIGNE(B$2:B$200));LIGNES(C$1:C2))))

Ceci permet-il de trouver la référence de la table repérée par LIGNES(C$1:C2) et de l'écrire dans la colonne C, DANS LE CAS OU C$1<LIGNES(C$1:C2) est faux, c'est à dire si le nombre de lignes total est supérieur au numéro de la ligne en question.

J'enverrai le fichier remasterisé (suppression des calculs inutiles) tout à l'heure,

A plus tard,

Arthur HO.

 

[arthurho]

Re : Application d'une MFC + plage nommée

Rebonjour,

Après avoir regardé un peu plus en détail, il y a du pour et du contre parce que :
- LA MFC est faite sur une colonne plus petite donc bien
- mais Il faut utiliser une formule avec CHERCHE une nouvelle fois sur la colonne B de l'onglet 2, ce qu'on avait fait initialement dans l'onglet 1.
Il y a peut êtreu n moyen de factoriser les deux colonnes.

Je vais regarder,

 

[CISCO]

Re : Application d'une MFC + plage nommée

Bonjour

Effectivement, je n'ai pas l'impression que cela aille plus vite. Dommage.

@ plus

 

[CISCO]

Re : Application d'une MFC + plage nommée

Bonjour

Est-ce bon et plus rapide avec la formule suivante dans la mise en forme conditionnelle ?

SOMMEPROD((NBCAR($A2)>NBCAR(SUBSTITUE($A2;SUBSTITUE(MesValeurs;" ";"");"")))*1)

ou encore avec

=SOMMEPROD(1*NON(EXACT($A2;SUBSTITUE($A2;SUBSTITUE(MesValeurs;" ";"");""))))

@ plus

P.S1 : Ne pas oublier que les formules proposées dans ce fil ne donnent le résultat désiré que si les mots recherchés, provenant du second onglet, n'ont pas d'espace vide entre les mots. Dans le fichier ci-joint, c'est OK puisqu'il y a des _ entre les mots de la colonne A du second onglet. Si tu penses avoir parfois des mots séparés par des espaces, dis le moi, je chercherai une autre formule prenant en compte cette possibilité.

P.S2 : Ne pas oublier que dans la partie SUBSTITUE(MesValeurs;" ";""), l'espace vide " " n'est pas obtenu avec la barre d'espace, mais en faisant un copier-coller pris à la fin d'un mot de la colonne A du second onglet. D'ailleurs, si je colle cet espace sur ce message, cela donne une étoile à l'écran ! Ex :

=SOMMEPROD((NBCAR($A2)>NBCAR(SUBSTITUE($A2;SUBSTITUE(MesValeurs;"*";"");"")))*1)

 

[arthurho]

Re : Application d'une MFC + plage nommée

Bonjour CISCO et joyeux noel;

J'ai testé ton fichier en faisant des verifications fonctionnelles (ajout suppression de nouvelles requêtes, test unitaires sur la MFC) et le fichier excel est totalement fluide avec les 110 requêtes.

Tu as réussi à ne pas utiliser la fonction CHERCHE qui ralentissait le fichier. Le test est uniquement sur le nombre de caractères de la requêtes, en remplacant successivement le nom de la table par les tables présentes dans la liste du deuxieme onglet.

Je ne comprends pas encore bien les deux substitue imbriqués, mais je continue à tester.

Merci pour ce super cadeau de noel

A bientôt

 

[arthurho]

Re : Application d'une MFC + plage nommée

Rebonjour,

En fait, les SUBSTITUE effectuent une recherche PREALABLE avant de remplacer quoi que ce soit, cest ce que tu utilises. Tu enlèves le nom de la table dans la requête si c'est possible (recherche effectuée) et tu comptes le nombre de caractères.

SOMMEPROD permet de remplacer le resultat de la condition logique par 0 ou 1.

Est ce que tu peux confirmer ca ?

Voila le fichier final : prise en compte des minuscules dans les tables, toute la ligne coloriée en rouge

Cdt,

 

[CISCO]

Re : Application d'une MFC + plage nommée

Bonsoir

Oui, la formule fonctionne comme tu le dis, soit :

Un SUBSTITUE pour effacer les espaces à la fin des mots de la colonne A du second onglet, ce qui donne par ex pour une cellule PROD_IND. Le test est fait sur toute la colonne grace à SOMMEPROD.
Un autre SUBSTITUE pour effacer ce qui est obtenu, PROD_IND, dans la colonne A du premier onglet.
Après, un test sur le nombre de caractères, pour savoir si le second SUBSTITUE a fonctionné, qui renvoie VRAI ou FAUX.
Ensuite, un *1 pour transformer les VRAI en 1 et les FAUX en 0.
Le SOMMEPROD permet de faire la SOMME de tous ces chiffres (et aussi de faire du matriciel camouflé), et ici, de travailler beaucoup plus vite.

Tu peux simplifier l'écriture de la plage d'action de la MFC en écrivant $A$2:$H$200.

@ plus

P.S : Le premier MAJUSCULE, dans la MFC, dans NBCAR(MAJUSCULE($A2))>, ne sert à rien (si ce n'est à perdre un peu de temps), puisqu'un texte, en majuscule ou en minuscule, compte le même nombre de caractère.