Monsieur Excel
Pour tout savoir faire sur Excel !

29 octobre 2013

Ajouter une droite de régression

Prenons une série de valeurs obtenues pour les onze derniers mois. Nous les représentons graphiquement et essayons d’obtenir la droite de régression permettant d’illustrer la tendance de ces valeurs.
 
Pour cela, nous traçons la courbe des valeurs et faisons un clic droit sur la courbe, puis sélectionnons la commande « Ajouter une courbe de tendance ». Excel nous propose par défaut une régression linéaire, ce qui nous convient parfaitement ici.

Activons l’onglet « Options » et cochons « Ajouter l’équation sur le graphique » et enfin validons par « OK ».

Après quelques retouches de formatage pour mettre la droite de régression en rouge, formater et déplacer l’équation sur le graphique, nous obtenons le résultat suivant :


En colonne C, nous avons entré les valeurs « prévues » par la droite de régression, en appliquant la formule =0,1963*$A2-7974,6 en C2.


Si vous disposez d’une version d’Excel antérieure à 2010, vous aurez peut-être l’occasion de constater le phénomène décrit dans l’article « Une bien étrange droite de régression... » du 26 décembre 2006. La solution au problème soulevé dans cet article a été publiée quatre jours plus tard dans l'article qui explique le bug de la droite de régression



:

24 octobre 2013

Une fonction perso « illégale »

Dans l’article précédent, nous avons montré qu’un simple survol d’une cellule par la souris pouvait provoquer la fermeture – sans sauvegarde ! – d’Excel.

Cela fonctionnait grâce au fait que – en combinaison avec la fonction lien. hypertexte() – on pouvait utiliser une fonction personnalisée pour modifier le contenu d’une autre cellule, ce qui est en théorie illégal pour Excel.

Rick Rothstein en a fait une illustration que nous avons exploitée dans l’exemple ci-dessous.

On entre une valeur dans la cellule B3. La cellule C3 contient la formule =docool(B3) et la cellule C4 est vide.


Quand on saisit une valeur en B3, le message en C3 est modifié en conséquence (là, c’est normal car il y a une formule) mais aussi – en même temps ! – le message correspondant à notre exemple ci-dessus apparaît dans la cellule C4.

Ce message est le résultat de la fonction evaluate situé en bas de la fonction personnalisée.

Voilà donc comment, via une fonction personnalisée et à l'aide de la fonction lien.hypertexte(), on parvient à modifier le contenu d’une cellule quelconque. En toute illégalité, selon les principes d’Excel !


19 octobre 2013

Un survol de souris bluffant !

Le 23 juillet 2012, il y a donc maintenant plus d’un an, j’ai publié un article intitulé « Une macro sensible au passage de la souris ». Cela montrait comment, à l’aide de la fonction lien.hypertexte() et d’une fonction personnalisée, lancer une action telle que par exemple entrer une valeur dans une autre cellule.

Ce concept génial a été découvert par Jordan Goldmeier et – depuis – des applications pratiques de ce processus ont été développées et publiées par divers auteurs.

Ce concept est très original car – en théorie ! – une fonction personnalisée ne peut modifier quoi que ce soit d’autre que la cellule active dans une feuille de calcul.

Un expert d’Excel bien connu des spécialistes, Bob Umlas, en a fait une application surprenante :

La formule de la cellule B3 est la suivante : =si(esterreur(lien_hypertexte( tordu()));"";"Si tu me passes dessus, je m'en vais !")

Si vous avez le malheur de passer la souris sur la cellule B3, vous quittez Excel sans pouvoir faire la sauvegarde du classeur actif.

Pour voir le contenu de la cellule B3 sans fermer Excel, il faut atteindre cette cellule en utilisant le clavier, sans passer par la souris.

Avouez que c’est impressionnant de provoquer la fermeture brutale d’Excel par un simple survol de souris !

Pour voir une application tout à fait intéressante – et plus utile ! – du survol de souris, lisez donc l’article « Un splendide usage du survol de souris ! » du 28 juillet 2012.



14 octobre 2013

Mes formations en novembre...

Vous trouverez ci-dessous la liste des formations que je propose pour le mois de novembre. Chacune de ces formations est limitée à huit participants, avec un ordinateur par personne. Vous pourrez télécharger la description de toutes ces formations en cliquant sur le lien suivant :

Modélisation avec Excel (2 jours) : les mercredi 20 et 27 novembre 2013.
Découverte de Visual Basic (2 jours) : le 28 novembre et le 2 décembre 2013.

Les autres séminaires ne sont pas proposés en inter lors de cette session. Toutes les formations proposées sont organisées en association avec la société EuroDécision.

Avec la formation « Modélisation avec Excel », je garantis de transformer en deux jours tout utilisateur moyen d'Excel en « power user », c'est-à-dire à l'amener au niveau des 5% des meilleurs utilisateurs d'Excel.

Avec la formation  « Découverte de Visual Basic », vous ne saurez pas tout sur le VBA, ce qui n'est pas possible en si peu de temps. Mais vous en saurez assez pour développer vos propres macros et savoir où trouver ce que vous ne savez pas encore.

Les cinq formations peuvent être animées en intra dans votre entreprise et – le cas échéant – personnalisées grâce à l’analyse et à l’amélioration des modèles propres à votre entreprise.

Ne loupez pas cette occasion de découvrir tout cela de la bouche même de l’auteur de ce blog, qui partagera avec vous l’expérience qu'il acquise en développant plus de 1.000 modèles dans plus de 100 entreprises en 10 pays.

Quelques-unes de mes références de formation 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, La Poste, Lilly France, Marsh, RTE, Sanofi, SIRIS, Texas Instruments, Tir Groupé, Total, Wabco, Walt Disney.

09 octobre 2013

Formatage numérique du mailing

Comme nous le constatons dans la copie d’écran ci-dessous, les valeurs importées ne respectent pas le format en € utilisé dans la base Excel.

Pour cela, une solution possible consiste à ajouter dans la base une colonne G avec en G2 la formule =texte(F2;"0,00 €"). Si c’est cette nouvelle colonne que l’on importe, il n’y aura plus de problème de formatage.

Si les portions de centimes ne sont pas utiles pour les calculs dans le modèle Excel et que l’on ne souhaite pas alourdir le modèle avec une colonne supplémentaire, une seconde solution consiste à remplacer la formule de F2 par =texte(formule;"0,00 €"). Si d’autres cellules doivent utiliser F2 dans leurs calculs, ce ne sera pas un problème car un texte représentant une valeur est traité comme une valeur dans une formule. Ainsi, quand on écrit =2*F2 alors que F2 contient le texte "168,33 €", on obtient bien la valeur 336,66.

Tout cela a très bien fonctionné quand j’ai créé un mailing à partir de 0, en respectant les explications ci-dessus.

Un problème de mise à jour…

En revanche, quand j’ai ajouté la colonne avec les montants en texte dans la base initiale pour modifier le mailing précédent, je n’ai pas trouvé la façon d’actualiser le lien avec la base autre que de repasser par  « Utiliser la liste existante » puis de nouveau aller chercher le fichier Excel contenant la base de données.

Il devrait y avoir une commande « Mise à jour du lien avec la base »… Elle existe peut-être, mais je ne l’ai pas trouvée.

04 octobre 2013

Mailing : Utilisation des règles

Dans un mailing, on peut utiliser un certain nombre de règles accessibles par la commande « Règles ». Quand on insère un champ, il est affiché entre guillemets. On peut le mettre en relief en activant le bouton « Champs de fusion en surbrillance », comme nous l’avons fait dans la première copie d’écran ci-dessous.

En revanche, quand nous insérons une règle telle que « Si .. Alors .. Sinon », comme nous l’avons fait pour la seconde phrase, on ne voit que ce que cela donne sur la première fiche de la base. Certes, on peut faire défiler les noms de la base en utilisant les flèches de déplacement dans le bloc « Aperçu des résultats ».

Si l’on veut vraiment voir comment le mailing est constitué, il faut basculer dans le mode « Affichage des codes de champ ». Cela peut certes se faire grâce aux options Word, en allant chercher dans les options avancées. Il y a cependant beaucoup plus simple : le raccourci [Alt]-[F9]  donne un accès direct à cette bascule et vous permet d’obtenir le résultat affiché dans notre troisième copie d’écran.