Monsieur Excel
Pour tout savoir faire sur Excel !

27 juillet 2009

Une bombe à retardement…

Il arrive que vous souhaitiez limiter l’accès à un document dans le temps, par exemple réduire ses fonctionnalités, ou bloquer son ouverture, ou même – plus fort encore ! – le détruire, au-delà d’une date limite.

Je vous conseille alors la lecture de l’excellent article « Timebombing A Workbook » de Pearson Software Consulting :
http://www.cpearson.com/excel/WorkbookTimeBomb.aspx

Nous vous proposons ici une brève présentation des diverses procédures pour lesquelles le code VBA est fourni dans cet article tout à fait intéressant.

« TimeBomb With A Defined Name »

Cette procédure définit un nom où est stockée la date de péremption du modèle. Si l’on tente d’ouvrir le modèle après la date limite, il est refermé automatiquement.

« TimeBomb With Suicide »

Avec cette procédure plus radicale, toute tentative d’ouverture du modèle au-delà de la date limite provoque sa destruction physique.

Certes, cette solution est brutale, mais elle évite au moins une réouverture ultérieure du modèle après modification de la date de la machine par l’utilisateur : )

« TimeBomb To Read-Only »

Avec cette troisième procédure, au lieu de fermer le modèle automatiquement ou de le détruire, on se contente de le mettre en « lecture seule » s’il est ouvert au-delà de la date limite.

« TimeBomb With Registry »

Avec cette procédure, la date limite n’est plus inscrite dans un champ nommé du modèle, mais dans le registre système lui-même. Cette procédure est intéressante car on y voit comment modifier le registre système depuis Excel.

Remarque – Bien entendu, pour que ces procédures fonctionnent, il est impératif que l’utilisateur ait un niveau de sécurité acceptant les macros et qu’il les ait autorisées à l’ouverture du modèle.

22 juillet 2009

Récupération de la première ligne

Vous avez récupéré d’un gros fichier informatique une série de noms et adresses, chacun occupant une cellule (cf. colonne A). Vous souhaitez en sortir uniquement la première ligne (cf. colonne B).

La solution est assez simple quand on sait que le saut de ligne est le caractère de code 10...

Voici la formule de B1 : =gauche(A1;cherche(car(10);A1)-1)

Le nombre de caractères à récupérer est tout simplement la position du premier saut de ligne, moins un caractère.

Récupération des deux premières lignes (lignes 5 et 6)

Si l’on veut récupérer précisément les deux premières lignes, la formule devient nettement plus complexe :
=gauche(A5;cherche(car(10);A5)+cherche (car(10);droite(A5;nbcar(A5)-cherche (car((10);A5)))-1)

Et un problème apparaît : le saut de ligne apparaît sous la forme d’un rectangle (cf. B5:B6).

Si l’on copie les cellules de la colonne B et que l’on y colle les valeurs, le problème demeure sauf si, une à une, on active l’édition de la cellule pour valider immédiatement avec [Entrée].

Quelqu’un aurait-il la solution pour obtenir de vrais sauts de ligne directement avec une formule, sans passer par une macro ?

17 juillet 2009

Fonction tri.paiements() : un bug !

Nous vous avons présenté le 13, le 17 et le 21 juin 2008 l'ensemble des fonctions financières d'Excel, dont celles présentes dans l’utilitaire d’analyse.

La fonction tri.paiements() – dont le nom anglais est XIRR – doit être utilisée avec des pincettes…

Voici l'adresse de la description de la fonction par Microsoft :
http://office.microsoft.com/fr-fr/excel/HP052093411036.aspx

Ce qui est délicat avec cette fonction c’est tout d’abord qu’elle renvoie des résultats très différents selon la valeur de démarrage (facultative).

Mais surtout – et cela est bien plus grave ! – elle comporte un bug : elle ne renvoie pas le même résultat selon la version d’Excel ou l’environnement (Mac ou Windows) utilisé !

Pour en savoir plus, vous pouvez consulter un article particulièrement intéressant à la référence suivante :
http://www.financialwebring.org/gummystuff/XIRR-bug.htm

Vous trouverez plusieurs autres références utiles en tapant « XIRR bug » dans Google !

Remarque – Je vous ai indiqué le nom de la fonction en anglais car vous pouvez aussi l’utiliser avec un Excel français, et cela présente l’avantage que votre modèle sera alors « international ».

13 juillet 2009

Compacité/Lisibilité d’une formule

Quand on cherche à construire une formule un tant soit peu complexe avec Excel, on a fréquemment le choix entre une formule plus compacte – mais moins facile à comprendre – et une formule plus facilement compréhensible – mais plus lourde.

Dans l’exemple que nous étudions aujourd’hui, nous devons calculer la rémunération de personnes dont le tarif horaire est indiqué en colonne A et le nombre d’heures de travail dans la journée dans la colonne B.

La rémunération de toute heure au-delà de 8 heures est majorée de 50%, et celle de toute heure au-delà de 12 heures est doublée.


La formule en C2 est :
=si(B2>12;(8*A2)+(4*1,5*A2)+((B2-12)*A2*2);si(B2>8;(8*A2)+((B2-8)*A2*1,5);A2*B2))

La formule en D2 est :
=A2*(B2+0,5*(max(0;B2-8)+max(0;B2-12)))

La formule en D2 est certes deux fois plus compacte que la formule en C2 mais – pour beaucoup de gens – elle sera nettement moins compréhensible que la formule en C2.

Remarque – Bien entendu, nous déconseillons formellement l’utilisation – comme c’est le cas dans cet exemple – de constantes cachées (les valeurs 1 ; 1,5 ; 2 ; 8 ; 12). Nous ne l’avons fait ici que pour simplifier la présentation du cas et les formules. Normalement, nous aurions mis tous ces paramètres dans ces cellules de la feuille : ainsi, les formules resteraient « bonnes » même si l’on devait un jour modifier ces valeurs.

08 juillet 2009

Nombre de caractères d’un bloc

Votre objectif est aujourd'hui de calculer le nombre de caractères dans un bloc de cellules – le bloc A2:B3, dans notre exemple – en comptant ou pas, selon le cas, d’éventuels espaces intermédiaires.

La copie d’écran ci-dessus représente notre solution avec, en bas de l’image, la description des formules utilisée en C5 et C6.

Une fois de plus, nous constatons la richesse de la fonction SommeProd() qui permet souvent d’atteindre – mais de façon plus légère – le genre de résultat pour lequel on pense en général devoir utiliser une formule matricielle.

La fonction Substitue() – nous l'avons déjà vu dans des articles antérieurs – est pour sa part assez peu connue et se révèle pourtant fréquemment utile.

04 juillet 2009

Masquage des lignes à quantité 0

Vous souhaitez – par voie de macro – masquer automatiquement toutes les lignes pour lesquelles une valeur de 0 a été saisie pour la quantité. Mais en revanche, vous ne souhaitez pas le faire pour les lignes dont la valeur dans la colonne « Quantité » n’a pas encore été saisie. Voici un exemple avec la feuille avant et après l’exécution de la macro :

Pour que cette macro fonctionne, il faut que vous ayez préalablement sélectionné le bloc B2:B17 des valeurs à contrôler. Voici la macro :

Sub Cache_lignes()
For Each Cell In Selection
If Cell = 0 And Not (IsEmpty(Cell)) Then Cell.EntireRow.Hidden = True
Next Cell
End Sub


Remarque –
On pourrait, bien évidemment, ajouter au début de la macro une commande demandant à l’utilisateur de sélectionner le bloc des valeurs à contrôler.