Comment éviter le week-end...

Nous avons en colonne A des dates de départ, et en colonne B un nombre de jours de délai. Nous voulons obtenir en colonne C la date obtenue en ajoutant le délai, à un détail près : si cette date tombe durant le week-end, nous voulons alors obtenir le vendredi précédant ce week-end.

La formule de C2 est la suivante : =A2+B2-max(joursem(A2+B2-1)-5;0)

Afin que vous puissiez vérifier le bon fonctionnement de cette formule, nous avons mis en colonne D, avec la formule =A2+B2 en D2, le résultat obtenu quand on n’évite pas le week-end.

Texte et valeur dans une cellule

Pour des raisons techniques qui ne tiennent qu’à lui, un lecteur du blog m’a demandé si l’on pouvait, comme on le voit dans la colonne A ci-dessous, afficher dans chaque cellule un texte et une valeur, les valeurs étant ajustées à droite.


L’astuce principale utilisée dans les formats personnalisés que j’ai créés – et reproduits en colonne C – consiste à utiliser le code « * » qui, apparemment – n’est pas documenté dans l’aide d’Excel. Ce code met à droite de la cellule la valeur concernée.

Notez l’utilisation du code « _ » qui, lui, est documenté : il provoque la présence d’un espace ayant la même taille que le caractère qui suit. C’est important car, par exemple, les places prises par un « i » et par un « m » ne sont pas du tout les mêmes, sauf si l’on utiliser une police « à chasse constante » telle que la police Courier.

Remarque – Pour ma part, je ne conseille pas de mêler ainsi textes et valeurs, en cachant les textes dans les formats. La modification des textes est alors bien moins pratique… Sans compter qu’il y a aussi une limite quant au nombre de formats personnalisés.

Tendance géométrique douteuse…

Nous avions montré dans notre article du 5 avril 2007 qu’il pouvait y avoir un problème avec la tendance géométrique, quand on allait « un peu trop loin »… Ce problème – lié à la précision de calcul dans Excel – persiste avec Excel 2007.

Dans la colonne A, nous avons entré la formule =10*A1 en A2, recopiée ensuite vers le bas. Tous les résultats sont exacts.

Dans la colonne B, nous avons sélectionné B1:B2, puis tiré le curseur de recopie incrémentée vers le bas, en maintenant le bouton droit de la souris enfoncé. Nous l’avons enfin lâché en B18, en demandant une tendance
« géométrique ».

Dans la colonne C, nous avons sélectionné C1:C3, puis procédé ensuite exactement comme pour la colonne B.

On constate, à partir de la ligne 15, des erreurs de calcul dans les colonnes B et C…

Calcul de la tendance géométrique

Nous avons vu dans le dernier article comment se comportait la « tendance linéaire ». Aujourd’hui, nous allons voir comment est calculée la « tendance géométrique ». En fait, Excel appelle cela « tendance géométrique » mais, quand on veut ajouter la courbe de tendance à la série, il faut demander une courbe « exponentielle ».

Pour mettre en œuvre la tendance géométrique, prenons les valeurs en A1:A4 ci-dessous, dont nous traçons le graphe et la courbe de tendance exponentielle. Voici le résultat :


La courbe identifiée par Excel à cet effet correspond à la formule y = 77,46*e^0,2303x.

Comme aucun axe des abscisses n’avait été défini, Excel a encore supposé qu’il fallait relier les quatre valeurs de y aux abscisses { 1 ; 2 ; 3 ; 4 }, ce qui explique la courbe de régression obtenue.

C’est exactement ce qu’Excel fait quand on sélectionne A1:A4 puis que l’on complète la série vers le bas en tirant le curseur de recopie incrémentée avec le bouton droit de la souris enfoncé : les nouvelles valeurs – en A5:A9 – correspondent bien aux valeurs de y (selon la courbe de régression) associées aux valeurs suivantes de x : 5, 6, 7…

Principe de la tendance linéaire

Le lecteur des deux derniers articles se demande peut-être comment fonctionne la tendance linéaire à partir d’une série de valeurs (deux valeurs ou plus…).

Pour cela, prenons les valeurs en A1:A4 ci-dessous, dont nous traçons le graphe et la courbe de tendance linéaire. Si vous ne savez pas comment ajouter une courbe de tendance, lisez donc l’article « Ajouter une droite de régression à un graphe » du 22 décembre 2006. Voici le résultat :

La courbe identifiée par Excel à cet effet est la droite y = 7,6 x + 3,5.

Comme aucun axe des abscisses n’avait été défini, Excel a supposé qu’il fallait relier les quatre valeurs de y aux abscisses { 1 ; 2 ; 3 ; 4 } , ce qui explique la droite de régression obtenue.

C’est exactement ce qu’Excel fait quand on sélectionne A1:A4 puis que l’on complète la série vers le bas en tirant le curseur de recopie incrémentée avec le bouton droit de la souris enfoncé : les nouvelles valeurs { 41,5 ; 49,1 ; 56,7 ; 64,3 : 71,9 ; 79,5 } correspondent bien aux valeurs de y (selon la droite de régression) associées aux valeurs suivantes de x : 5, 6, 7…

Série en tendance géométrique

Nous avons vu il y a quatre jours, en faisant de la recopie incrémentée avec le bouton droit de la souris enfoncé à partir d’une date, comment l’on pouvait engendrer une série de dates, en passant éventuellement par la commande
« Série » du menu contextuel.

Si maintenant l’on sélectionne deux cellules (ici, A1 et A2) avant de réaliser cette recopie incrémentée, le comportement d’Excel dépend de la nature des deux cellules initiales.

Si elles représentent des dates, seule la commande « Série » est active dans le dernier bloc. Si en revanche, comme dans le cas ci-dessous, il s’agit de simples nombres, les trois commandes du dernier bloc sont alors actives.

Quand nous tirons avec le bouton droit de la souris jusqu’en A7, la valeur 55 – qui correspond à la tendance linéaire – est affichée (cf. au-dessus du menu contextuel) même si, comme nous l’avons fait ci-dessus, nous activons la commande « Tendance géométrique ».

Si nous lâchons à présent le bouton droit de la souris, les valeurs 100, 1.000, 10.000, 100.000 et 1.000.000 remplissent les cellules A3 à A7.

Création d’une série de dates

Nous voyons, dans la première des trois images ci-dessous, ce qui se passe quand on sélectionne la cellule A1, avec le curseur de recopie incrémentée, et que l’on tire vers le bas avec le bouton droit de la souris enfoncé.

Quand on lâche le bouton de la souris, le dialogue contextuel de la seconde image apparaît. Si l’on sélectionne alors une des quatre commandes du second bloc, cela crée immédiatement la série demandée.

Si en revanche on sélectionne la dernière commande, « Série », le dialogue reproduit en troisième position apparaît alors, ce qui permet par exemple de créer une série de dates espacées de deux jours, soit en jours quelconques, soit en jours ouvrés.

Le dialogue « Série de données » est le même dans les versions 2003 et 2007.

Si vous voulez sauter aussi les jours fériés, il ne vous reste plus qu’à utiliser la jolie macro que nous vous avions proposée à cet effet le 9 août 2007.