Monsieur Excel
Pour tout savoir faire sur Excel !

28 février 2014

Rafraîchir la mémoire d'un TCD

Nous avons une petite base de données en A1:B11, dans laquelle la cellule A11 contient une erreur de frappe : elle devrait afficher « Lebleu ». A droite, le TCD affiche le crédit par personne.

Corrigeons A11, puis actualisons le TCD. Nous obtenons alors le résultat de la seconde copie d’écran ci-dessous.


Tout a l’air OK mais, si nous déroulons le menu en D1, nous constatons en bas de la liste déroulante que « Leblue » est la dernière option proposée, alors que ce nom n’existe plus dans la base.

Comment peut-on nettoyer le menu déroulant en éliminant cette dernière option ?

Une solution consiste à effectuer les quatre opérations suivantes :
  • cliquer dans le TCD pour l’activer ;
  • ôter l’étiquette de ligne « Nom », ce qui réduit le TCD au seul total du crédit ;
  • actualiser le TCD ;
  • rétablir l’étiquette de ligne « Nom », ce qui ramène le TCD à sa présentation antérieure.
Et voilà ! Comme vous pouvez le vérifier en déroulant le menu en D1, le nom « Leblue » a enfin disparu de la liste des options proposées.

21 février 2014

Description de l’environnement

Par le VBA, vous avez un accès direct à 41 renseignements relatifs à l’environnement au sens large, donc pas seulement celui d’Excel.

Dans la copie d’écran ci-dessous, nous avons listé tous ces renseignements ainsi que la macro qui nous a servi à les sortir.

Nous avons simplement, après l’exécution de la macro, coupé les 21 derniers renseignements pour les coller en B1 afin que vous puissiez avoir une vue d’ensemble du résultat.


Dans un domaine connexe, vous pouvez aussi lire les articles « Création d’une application multilingue… » du 12 septembre 2008  et « La commande Application.International(Index) » du 17 septembre 2008. 

Vous trouverez là un nombre plus grand encore de renseignements, dont de nombreux renseignements relatifs au paramétrage d’Excel.


16 février 2014

Un equiv() sur une matrice (d)

Remarque 1 – Cet article peut être lu indépendamment des quatre articles précédents !

Il conclut en effet une série d’articles commencée le 27 janvier 2014 avec l’article « Un élément dans une matrice », dans le cadre duquel nous avons analysé plusieurs méthodes – en utilisant des formules tantôt directes tantôt matricielles – pour localiser un élément précis dans une matrice.

Cela vous permet de dépasser la limite du equiv(valeur;vecteur;0) dans lequel le second argument ne peut être qu’un vecteur, même si le libellé proposé par l’aide d’Excel, =equiv(valeur_cherchée;tableau_recherche;type), est complètement trompeur à cet égard…

Nous rappelons donc le problème posé au départ. Nous disposons en C4:F6 d’une matrice contenant des données. Nous cherchons à localiser dans cette matrice le contenu identifié en C8. Le résultat de cette recherche doit être affiché soit sous la forme en L4:L8 (100 fois le n° de ligne + le n° de colonne), soit sous la forme en M4:M8 (identification de la ligne et de la colonne).


La solution proposée par Peter Bartholomew affiche toutes les occurrences rencontrées, en affichant une liste exhaustive des références concernées.

Cette formule est quand même matricielle, donc validée avec [Ctrl]-[Maj]-[Entrée], mais elle présente l’avantage d’être très compacte…

Peter aime beaucoup utiliser des formules dans la définition des noms, ce qui permet en effet d’atteindre un grand niveau de compacité dans les formules mais en revanche – rien n’est parfait dans ce bas monde ! – rend le modèle moins facilement auditable.

Remarque 2 – Un format conditionnel masque les lignes de bloc I4:M8 qui contiennent une erreur en colonne L.


11 février 2014

Un equiv() sur une matrice (c)

Nous continuons ici sur la lancée des articles précédents : quelle est la meilleure formule pour localiser un élément dans une matrice, alors que la localisation par equiv() ne fonctionne que sur un vecteur.

Dans le choix d’une formule par rapport à une autre, plusieurs critères interviennent :
  • la longueur de la formule
  • la lisibilité (ou auditabilité) de la formule
  • le fait que la formule soit matricielle ou pas
  • le temps de calcul de la formule
  • la flexibilité de la formule
Nous nous intéressons aujourd’hui au problème de la flexibilité de la formule. La question qui me semble la plus importante pour le problème que nous cherchons à résoudre est la suivante : « Qu’arrive-t-il s’il existe deux occurrences ou plus de ce que l’on recherche ? »

Nous voyons ci-dessous le résultat obtenu avec les solutions présentées dans les articles précédents :


Nous constatons que seule la formule originale de Roberto trouve la première occurrence quand il y en a deux (ici, C3 et B4)..

En revanche, ma formule en B9 est intéressante car elle récupère les adresses des deux occurrences.

En fait, elle ne prendra pas toutes les occurrences, mais seulement la première de chaque ligne… Enfin, c’est déjà mieux que le résultat auquel les autres formules arrivent.

Remarque - Notons au passage que la formule originale de Roberto en D8 supporte bien la seconde occurrence du "h" alors que la formule plus courte en E8 ne la supporte pas...

Pour les autres formules à part la formule originale de Roberto, on pourrait utiliser :
=si(nb.si(B6;B2:D4)>1;nb.si(B6; B2:D4)&" sol.";formule) à la place de =formule.

Rappelons pour mémoire la formule de Roberto, en D8 (formule matricielle) :
=petite.valeur(si(B2:D4=B6;(ligne(B2:D4)-min(ligne(B2:D4))+1)*100+(colonne(B2:D4)-min(colonne(B2:D4))+1);"");1)


06 février 2014

Un equiv() sur une matrice (b)

Pour compléter les deux articles précédents, nous vous proposons dans cet article une solution non matricielle pour remplacer celle de Roberto, solution qui a été envoyée par Eric van Rooijen.

En outre, nous vous proposons d’autres solutions pour l’affichage de « Lig 3 – Col 1 ».

Une solution non matricielle pour l’affichage du « 301 »

Voici la formule de la cellule D10, qui a été proposée par Eric van Rooijen.
=(max(index((B2:D4=B6)*ligne(B2:B4););1)-1)*100+(max(index((B2:D4=B6)*colonne(B2:D2););1)-1)

Cette formule n’est pas matricielle et elle est en outre plus compacte encore que celle de Roberto.

Pour l’esthétique, j’ai mis les cellules D9:E9 et D10:E10 dans le format d’alignement « Centré sur plusieurs colonnes » qui présente l’avantage d’afficher les résultat comme des cellules fusionnées, mais en évitant les effets catastrophiques de la fusion de cellules, la pire fonctionnalité jamais inventée dans Excel.


Une solution non matricielle pour l’affichage du « Lig 3 – Col 1 »

Dans son commentaire à l’article du 27 janvier, Philippe nous propose une solution non matricielle pour l’affichage de « Lig 3 – Col 1 ». C’est la formule en D13 :
="Lig "&sommeprod((B2:D4=B6)*ligne($A$1:$A$3))&" - Col "&sommeprod((B2:D4=B6)*colonne($A$1:$C$1))

L’avantage est que cette formule est non matricielle. L’inconvénient tient à ce que $A$1:$A$3 suppose que la matrice contient trois lignes, et à ce que $A$1:$C$1 suppose que la matrice contient trois colonnes, ce qui la rend moins générique.

C’est pourquoi je vous propose en D14 une formule reprenant des éléments de la formule de Roberto et ne présentant donc plus cette limitation :
="Lig "&sommeprod((B2:D4=B6)*(ligne(B2:D4)-min(ligne(B2:D4))+1))&" - Col "&sommeprod((B2:D4=B6)*(colonne(B2:D4)-min(colonne(B2:D4))+1))

Remarque – Cette formule est plus longue, mais elle reste non matricielle et – surtout ! – elle devient totalement générique car elle ne fait référence qu’à un bloc matriciel, B2:D4.


01 février 2014

Un equiv() sur une matrice (a)

L’article précédent nous a servi à combler une immense lacune de la fonction equiv() :  elle permet en effet de localiser un texte ou une valeur dans un vecteur, mais pas dans une matrice.

Comme l’a remarqué avec justesse Benji dans le premier commentaire de l'article précédent, je comptais bien entendu vous apporter l’équivalent d’un equiv() pour la localisation d’un élément dans une matrice.

La première solution que je vous propose est – ce n’est pas un miracle pour les habitués de ce blog ! – encore due à Roberto Mensa. Voici sa formule – matricielle, donc validée avec [Ctrl]-[Maj]-[Entrée] – en D8 :
=petite.valeur(si(B2:D4=B6;(ligne(B2:D4)-min(ligne(B2:D4))+1)*100+(colonne(B2:D4)-min(colonne(B2:D4))+1);"");1)

B2:D4 est notre matrice complète, qui pourrait avoir une taille quelconque…


B2:D4=B6 s’évalue en {FAUX.FAUX.FAUX;FAUX.FAUX.FAUX;VRAI.FAUX.FAUX}. C’est donc la matrice des résultats.

Le si() sert à mettre un espace chaque fois que l’on obtient « FAUX».

On peut alléger encore un peu la formule de Roberto en remplaçant petite.valeur(… ;1) par somme(…). C'est ce que j'ai fait en E8.

Vous avez donc à présent l’équivalent d’un equiv() qui trouve la localisation d’un texte ou d’une valeur dans une matrice !

Nous verrons dans le prochain article une autre solution à ce problème.