La date du prochain lundi

Un lecteur m’a demandé récemment comment on pouvait trouver le premier lundi suivant le jour présent. Ce n’est pas trop compliqué, à condition de connaître la fonction mode() et le mode de calcul des jours de la semaine.

Le jour de la semaine

La fonction joursem(date) renvoie le jour de la semaine pour la date donnée, le jour allant de 1 (dimanche) à 7 (samedi). La Bible n’est pas respectée par Excel puisque dans ce cas, Dieu se reposerait avant même d’avoir créé quoi que ce soit. La sieste avant le repas, en quelque sorte...

La fonction mod()

La fonction mod(nombre; diviseur) renvoie le reste de la division de nombre par diviseur.

Donc mod(A;B) est équivalent à la formule : A-B*ent(A/B).

La formule à utiliser

Un fois tout cela compris, la formule à utiliser n’est pas trop compliquée. Voici celle que nous avons entrée en B2 puis recopiée vers le bas : =A2+7-mod(joursem(A2)-2;7)

Enfin, pour le formatage des cellules, nous avons créé le format personnalisé :
jjj jj/mm/aaaa

Plus sur le Name Manager...

Name Manager, créé par mon ami Jan Karel Pieterse, et que je vous ai présenté il y a quatre jours, est un add-in d’Excel si performant que je l’ai traduit pour vous :

Il possède une particularité rare, que j’aimerais tant voir sur d’autres add-ins ou même – on peut rêver ! – sur Excel lui-même : il permet de choisir la langue avec un choix particulièrement riche : Anglais, Danois, Allemand et Néerlandais.

On sélectionne aisément (cf. écran ci-dessous) tous les noms ayant telle ou telle particularité. Il est par exemple utile d’identifier ainsi rapidement tous les noms avec des références externes, c'est-à-dire ayant un lien avec un autre classeur.

Dans l’écran ci-dessous, nous avons sélectionné tous les noms correspondant à des zones multiples à deux dimensions :

On peut aussi sélectionner tous les noms aboutissant à une erreur, ce qui est bien pratique pour nettoyer un modèle en cours d’audit.

Vous pouvez télécharger gratuitement la dernière version de Name Manager à l’adresse suivante : http://www.jkp-ads.com/OfficeMarketPlacenm-en.asp

En conclusion, Name Manager est – sur mes ordinateurs – un add-in toujours coché dans le dialogue de la commande Outils – Macros complémentaires.

Un add-in vital : Name Manager

Un domaine où Excel demeure balbutiant est celui de la gestion des noms. Il y a encore des progrès à faire en ce domaine, et je ne suis pas convaincu de l’intérêt pratique des évolutions des ces dernières années, en particulier l’insertion automatique du nom correspondant à une cellule pointée en remplacement des coordonnées de la cellule.

La fenêtre de la commande Insertion – Nom – Définir est très pauvre ; si la définition du nom est longue, on n’en voit qu’une petite partie, et cette définition est très difficile à éditer proprement, comme on le voit clairement dans la copie d’écran ci-dessous :

Il est très difficile de scroller dans la zone " Fait référence à " et d’y modifier quoi que ce soit. Avec Name Manager, vous n’avez pas ce problème. Cet add-in gratuit d’Excel est justement calibré pour vous permettre de résoudre ce problème, et d’aller bien plus loin aussi. Voici la fenêtre qu’il vous présente quand on a sélectionné le nom Bloc_7 :

Là enfin, il est aisé de consulter la définition complète du nom, de la modifier, ou de faire plein d’autres choses encore...

Cet outil – dont je me sers régulièrement lors de mes développements professionnels – sera l’objet d’un article complémentaire dans la prochaine rubrique.

Utilisation de la fonction Equiv()

Un exemple intéressant de l’utilisation de la fonction Equiv() est, dans le tableau ci-dessous, d’afficher en B13 le prénom et le nom de la personne dont le salaire est le plus élevé.

Dans notre exemple, histoire de vérifier en temps réel que la formule donne le résultat escompté à tout moment, nous avons entré en C2, puis recopié vers le bas, la formule =ent(1000+2000*alea()).

La formule de B13 est la suivante :

=index(A2:A10;equiv(max(C2:C10);C2:C10;0))&" "&index(B2:B10;equiv(max (C2:C10);C2:C10;0))

Remarque - Notez que la concaténation de chaîne est bien plus simple avec « & » qu’avec la fonction Concatener().

Une utilisation intéressante d’Equiv() en lien avec Index() consiste à retrouver une valeur particulière, dans un tableau rectangulaire dont les libellés de ligne et de colonne ne sont pas triés alphabétiquement. Il suffit pour cela d’entrer une formule du type :

=index(matrice;equiv(réf_lig;liste_lig;0);equiv(réf_col;liste_col;0))

Le troisième argument d’Equiv()

Dans notre article du 9 mai, nous n’avons pas commenté le troisième argument de la fonction equiv. En fait, ce troisième argument, facultatif, peut avoir trois valeurs possibles :
- +1 : position de la dernière valeur inférieure ou égale à la valeur cherchée ;
- 0 : position de la première valeur identique à la valeur cherchée ;
- –1 position de la dernière valeur supérieure ou égale à la valeur cherchée.

L'argument +1, quand vous n'utilisez que deux arguments, est la valeur par défaut.

Sur le plan algorithmique, equiv(...;...;1) fonctionne à la manière de recherchev(...;...;...;vrai) et equiv(...;...;0) à la manière de recherchev(...;...;...;faux).

Microsoft affirme dans l’aide d’Excel que le vecteur de recherche doit être en ordre croissant avec l’argument +1 et en ordre décroissant avec –1. Là encore, nos chers amis se trompent : même si l’usage normal d’equiv() avec les argument +1 et –1 suppose en général un vecteur trié, il est tout à fait possible d’utiliser un equiv(...;...;1) ou un equiv(...;...;-1) avec un vecteur non trié et cela est même tout à fait utile pour résoudre certains problèmes de façon élégante.

Attention ! Si vous oubliez le troisième argument alors que vous cherchez la position de la première valeur identique à la valeur cherchée, vous serez surpris par le résultat : en effet, Excel supposera alors que l’argument est +1.

Une application originale : le remplacement de la fonction Recherche()

Si vous avez bien compris l’utilisation de la fonction equiv(), vous avez certainement remarqué qu’elle permet de se passer de la fonction recherche().

Reprenons l’exemple du 16 mars, reproduit ci-contre, où nous avions montré que la formule naturelle à placer en C13 était : =recherche(C12;B3:C10).

Cette formule est parfaitement remplaçable par =index(C3:C10;equiv(C12,B3:B10)) ou par =index(C3:C10;equiv(C12,B3:B10;1)), qui donneront toujours le même résultat !

Intéressant, n'est-ce pas ?

Introduction à la fonction Equiv()

Nous avons découvert la fonction Recherche() dans les articles du 16 et du 18 mars, et la fonction Index() dans ceux du 12 et du 15 avril. La troisième fonction fondamentale de recherche est la fonction Equiv() : comme je l’ai déjà dit, je n’ai pas un seul de mes modèles professionnels qui n’utilise au moins l’une de ces trois fonctions.

Entrons tout d'abord dans les 15 premières lignes de la colonne A la formule =ent(1+10*alea()) qui nous donne des valeurs aléatoires de 1 à 10. Puis, en D1, entrons la valeur « 7 ». Pour trouver la position du premier 7 dans la colonne A, enfin, il faut entrer la formule =equiv(D1;A:A;0) :

Comme d’habitude, nous avons copié dans la colonne E les formules de la colonne D. Notons la formule de D3, qui récupère la valeur dont la position est recherchée. En résumé, equiv(valeur;vecteur;0) trouve la position de la valeur dans le vecteur, alors que index(valeur;position) trouve la valeur située à une certaine position dans le vecteur : ces deux fonctions sont tout à fait complémentaires.

Un carton rouge à Microsoft qui définit dans l'aide d'Excel la syntaxe comme equiv(valeur_cherchée;tableau_recherche;type) et enfonce le clou en déclarant : « tableau_recherche est une plage de cellules adjacentes contenant les valeurs d'équivalence possibles. L'argument tableau_ recherche peut être une matrice ou une référence matricielle. »

Eh bien c’est faux ! La fonction equiv() ne fonctionne que sur un vecteur...

Les limites d’un tableau croisé

Un tableau croisé dynamique est soumis à certaines contraintes qui, si elles ne sont pas respectées, aboutiront le plus souvent au message fatidique : Mémoire insuffisante pour afficher la totalité du tableau croisé dynamique.

Champs de colonne

Le produit du nombre des valeurs de tous les champs de colonne ne doit pas dépasser 32.768.

Supposons que vous ayez quatre champs de colonne avec respectivement 20, 50, 4 et 5 valeurs, ce qui fait un produit de 20.000 valeurs. Si vous ajoutez un cinquième champ avec 3 valeurs, cela vous porte à 60.000 et vous obtenez le message d’erreur...

Champs de ligne

Selon le même principe, le produit du nombre des valeurs de tous les champs de ligne ne doit pas dépasser 2^31, soit un peu plus de 2 milliards de valeurs. Là, vous avez quand même de quoi respirer.

Nombre d’éléments par champ (ligne, colonne ou page)

Vous n’avez pas droit à plus de 8.000 valeurs différentes dans n’importe quel champ de ligne, de colonne ou de page. Sinon vous avez droit à un autre message d’erreur.

Là, je vous laisse apprécier la poésie de la traduction dont – il faut l’avouer – nos amis de Microsoft nous préviennent bien qu’elle est produite par un logiciel :

Pour en savoir plus, consultez la base de connaissance de Microsoft sur les limites des tableaux croisés dynamiques :
http://support.microsoft.com/kb/q211517/

Un conseil d’ami… Pensez à cocher “Anglais (US)” dans le menu déroulant en haut et à droite, dans le bloc « Traductions disponibles ».

Heureusement pour vous, vous obtenez dans ce cas l’original en anglo-américain, plutôt que la retraduction en anglais de la traduction française, ce qui ne manquerait pas d’être poétique...

Calculer le nombre d’itérations

Pour en terminer avec mes messages du 27 avril et du 30 avril, nous allons voir aujourd’hui comment calculer le nombre d’itérations requis dans notre exemple de calcul de marge pour obtenir le résultat final au centime près. Pour cela, nous devons utiliser les réglages suivants :

Notez que nous avons demandé un calcul sur ordre avec 100 itérations au maximum (ce sera plus que nécessaire pour obtenir la précision demandée) et un écart de 0,01 correspondant au centime requis comme précision.

Il y a deux problèmes à résoudre :

- comment faire pour réinitialiser les valeurs après un calcul de marge ?
- comment obtenir une précision de 0,01 si le compteur d’itérations est incrémenté de 1 à chaque itération ?

Réinitialisation de la simulation

Pour réinitialiser les valeurs après un calcul itératif, la solution la plus efficace consiste à créer un drapeau que l’on met à 0 pour réinitialiser et à 1 quand on souhaite que le calcul itératif s’effectue :


Gestion du compteur d’itérations

Pour que le compteur d’itérations ne casse pas la convergence en ajoutant 1 à chaque itération – auquel cas l’écart maximal de 0,01 ne serait jamais obtenu – l’astuce consiste tout simplement à compter les itérations en millièmes, donc inférieurs à 0,01 !

Convergence vers la solution finale

Il ne nous reste plus qu’à saisir 1 dans le drapeau en E2 et à lancer le calcul par [F9]. Le résultat apparaît instantanément, avec 142,85 € en B4 et 0,008 en B6. Il a donc suffi de 8 itérations pour converger vers la solution !