Monsieur Excel
Pour tout savoir faire sur Excel !

30 septembre 2009

La mise sous forme de tableau

Remarque – Je prie mes lectrices et lecteurs fidèles de m'en excuser : j’ai pris quelques jours de vacances, ce qui fait que mon dernier message remonte à 7 jours, contre les 4 jours d’écart habituels entre deux messages !

Aujourd’hui, nous allons présenter la commande « Mettre sous forme de tableau » de la famille « Style » de l’onglet « Accueil » d’Excel 2007. Le principe de fonctionnement est simple ; on place le curseur sur une des cellules d’un tableau (B2, dans notre exemple) et l’on appelle la commande : apparaît alors une liste de 60 possibilités de décoration :

Sélectionnons l’une quelconque de ces 60 possibilités, et nous aboutissons à un dialogue qui montre que, de façon très surprenante, Excel a incorporé les totaux à droite et pas ceux de la dernière ligne :

Si nous validons immédiatement par « OK », nous constatons que le résultat n’est pas franchement sympathique :
● la ligne des totaux est totalement ignorée par le formatage !
● Excel a placé un filtre en haut de chaque colonne, ce que nous n’avons jamais demandé !
● et il y a un problème – plus grave encore – dont nous parlerons dans le prochain message !

Remarque 2 – Le premier problème disparaît évidemment si l’on remplace la sélection par défaut A1:F4 par celle du bloc A1:F5, mais pourquoi donc imposer ce travail supplémentaire à l’utilisateur ?

23 septembre 2009

SommeProd et formule matricielle

Ce n’est pas la première fois que nous illustrons la résolution d’un problème un peu complexe à l’aide de la fonction SommeProd et/ou d’une formule matricielle.

Dans l’exemple ci-dessous, nous souhaitons calculer en D15 la somme des montants du service « Comptabilité » en juin qui correspondent à plus de 50% d’engagement :

Comme cela a souvent été le cas, nous vous proposons plusieurs solutions :

En D15 :
=sommeprod(--(A2:A13="Comptabilité");--(mois(B2:B13)=6);--(C2:C13>50%);D2:D13)

En D16 :
=sommeprod(1*(A2:A13="Comptabilité");1*(mois(B2:B13)=6);1*(C2:C13>50%);D2:
D13)

En D17 :
=somme((A2:A13="Comptabilité")*(MOIS(B2:B13)=6)*(C2:C13>50%)*D2:D13)

Les deux premières solutions sont des variantes l’une de l’autre, l’objectif de "--" ou de "1*" étant de remplacer un vecteur de VRAI et de FAUX par un vecteur de 1 et de 0.

La troisième solution doit être saisie comme une formule matricielle, donc validée avec la combinaison [Ctrl]-[Maj]-[Entrée]

C'est le prix à payer pour sa compacité supérieure !

19 septembre 2009

Formations pour la fin d'année

Vous trouverez ci-dessous la liste des formations que je propose durant les mois de novembre et décembre. L'effectif de chaque formation est limité à huit participants, avec un ordinateur par personne. Vous trouverez la description complète de ces formations en cliquant sur leurs intitulés.

Modélisation avec Excel (2 jours) : lundi 23 et mardi 24 novembre 2009.

Initiation au Visual Basic (2 jours) : mardi 17 et jeudi 19 novembre 2009.

La modélisation du risque (Crystal Ball) : mardi 15 décembre 2009.

Utilisation de Crystal Ball Pro : jeudi 17 décembre 2009.

Ces formations sont organisées en association avec la société EuroDécision (cf. bandeau droit). Dans le fichier des formations, vous trouverez aussi la description des autres formations non proposées ce trimestre, mais toujours disponibles en intra.

Formations en intra

Chacune de ces formations peut être animée en intra, c’est-à-dire dans votre entreprise, et peut donc aussi être personnalisée. Ainsi, on peut utiliser comme exemple les modèles de l’entreprise, présents ou à venir.

Le but de la formation « Initiation au Visual Basic » n'est pas d'apprendre tout Visual Basic en deux jours, ce qui serait illusoire. Il s'agit plutôt de donner aux participants un niveau leur permettant de se lancer en Visual Basic et de savoir par la suite où trouver les informations qui leur manquent encore. Le but est donc de les rendre autonomes en Visual Basic.

Notez en particulier que, dans la formation « Modélisation avec Excel », qui connaît un taux de satisfaction proche de 100%, je garantis de transformer en deux journées n'importe quel utilisateur « lambda » d’Excel en un utilisateur appartenant au top 10%.

Références de formation intra

Voici quelques références de sociétés auprès desquelles j’ai animé ces formations en intra : Aéroports de Paris, Aérospatiale, Arianespace, Bouygues, Caisse des dépôts, CASE-Poclain, CCIP, Cegelec, CNES, CNET, EADS, EdF, Elf, Ernst & Young, Euroconsult, Finacor, France Telecom, Gaz de France, GIAT, IFP, Isochem, Lafarge, Lilly France, Marsh, Sanofi, SIRIS, Texas Instruments, Tir Groupé, Total, Wabco, Walt Disney.

14 septembre 2009

La protection de la protection…

Nous avons vu il y a quatre jours comment – à l’aide d’une macro – protéger ou déprotéger d’un seul coup toute une série de feuilles.

Certes, notre macro ne suffit pas telle quelle. En effet, n’importe quel utilisateur du modèle pourrait ainsi la lancer et modifier la protection à loisir, pour peu qu’il sache rendre visible (afin de la paramétrer) la feuille
« Protection », ce qui est à la portée du premier programmeur venu…

Deux étapes supplémentaires sont donc indispensables :
protéger l’exécution de la macro par un mot de passe ;
protéger le projet VBA par un autre mot de passe.

La protection de l’exécution de la macro peut aisément être assurée par les deux nouvelles instructions placées au début du code qui devient alors le suivant :


La protection du projet est assurée grâce à la séquence suivante en VBA : menu « Outils », commande « Propriétés de VBAProject », et enfin onglet
« Protection ».

Remarque – Certes, ces protections ne résisteront ni à un programmeur compétent ni à – tout simplement – une recherche sur Internet sur les diverses façons de déprotéger un classeur Excel…
Mais ces deux nouvelles lignes assureront tout de même une protection de premier niveau, ce qui est à peu près tout ce que l’on peut espérer avec Excel.

10 septembre 2009

Protection d'une série de feuilles

Quelquefois, on aimerait bien pouvoir protéger ou déprotéger toute une série de feuilles d’un seul coup. Pour atteindre cet objectif, une macro est bien évidemment nécessaire.

Nous avons à cet effet une feuille appelée « Protection », à laquelle nous aurons logiquement attribué le statut « Very hidden », dans laquelle apparaît la liste des feuilles du classeur avec en colonne B le statut souhaité et, en colonne C, le mot de passe.

La liste des feuilles du classeur a pu être obtenue grâce à la solution proposée le 20 juin 2009 dans ce blog.

La macro reproduite ci-dessous et activée par le bouton « Protection » fait le travail !

Remarque – L’article du 24 juin vous permettra de comprendre pourquoi nous affichons le message « Faites un recalcul complet ! » quand le nom de la feuille active n’est pas correct, donc quand la liste créée en colonne A de la feuille « Protection » n’est pas actualisée.

04 septembre 2009

Les limites des divers blocages

Dans les articles du 17 au 31 août, nous avons vu comment désactiver – et bien sûr comment réactiver – telle ou telle commande de tel ou tel menu.

Nous ne l’avions pas encore spécifié mais ces blocages sont bien entendu limités aux versions d’Excel antérieures à Excel 2007.

Ceci dit, même avec les versions antérieures, il y a des limites à ce que l’on peut faire pour se protéger en bloquant ainsi l’accès à certaines commandes, comme nous allons le voir ci-dessous…

La restauration des menus

Si l’on utilise la commande « Personnaliser » du menu « Outils », onglet
« Barre d’outils », il suffit de sélectionner comme dans la copie d’écran ci-après la « Barre de menus Feuille de calcul » et de cliquer ensuite dans le bouton « Réinitialiser » pour restaurer la barre de menus habituelle, avec tous ses menus et toutes ses commandes !

Si l’on veut bloquer l’accès à certaines commandes, il faut donc penser aussi à désactiver cette commande « Personnaliser » !

La présence de raccourcis

Quand une commande possède un raccourci, celui-ci fonctionne même quand la commande est désactivée !

Vous le constaterez aisément en désactivant la commande « Copier » du menu « Edition » : vous verrez que le raccourci [Ctrl]-c fonctionnera encore !

En revanche, l’accès sans souris aux commandes, avec la combinaison [Alt]-e pour appeler le menu « Edition » puis [Alt]-p pour la commande « Copier » est pour sa part effectivement désactivé…