Monsieur Excel
Pour tout savoir faire sur Excel !

30 mars 2012

Mise en forme des sparklines

Quand on a créé un sparkline et que l’on clique dedans, un titre « Création » apparaît sous une rubrique « Outils sparkline » qui apparaît alors en haut du ruban. Il faut cliquer dans ce titre pour voir apparaître le ruban en question, qui a été reproduit en deux morceaux dans le bas de la copie d’écran ci-dessous.

Remarque – On ne voit pas bien l’intérêt de devoir cliquer dans ce titre pour faire apparaître le ruban : j’aurais pour ma part préféré que le ruban apparaisse dès l’activation d’un sparkline. Je suppose que le souci des développeurs d’Excel était ici de rester compatible avec les graphes courants qui – ayant pour leur part trois rubans associés (Création, Disposition, Mise en forme) – ne sauraient pas lequel des trois rubans activer…

Le nom « Edition » serait plus approprié que « Création » car ce ruban est plus destiné à l’édition de sparklines existants qu’à leur création, qui s’effectue – comme nous l’avons vu dans l’article précédent – plus naturellement via le ruban « Insertion ».

Ce ruban permet de modifier les données, de choisir parmi l’un des trois types possibles, de marquer divers types de points, de modifier la couleur du tracé, de modifier les couleurs du sparkline ou du marqueur, de jouer sur les axes et enfin de travailler sur les groupes.

En faisant des essais, on constate les particularités de ces fonctionnalités. Ainsi, quand on demande l’affichage de l’axe avec les données présentées dans l’article précédent – toutes comprises entre 0 et 999 – on constate que cet axe n’apparaît que si la plus petite valeur de la ligne de données est inférieure à 10… Mais ce n’est pas bien grave car l’intérêt d’un tel axe est faible tant qu’il n’y a aucune valeur négative dans les données.

Pour ma part, je ne vois pas bien l'intérêt de divers gadgets de mise en forme tels que la mise en relief du premier point, du maximum, ... Comme de toute façon le graphe est de petite taille, tout cela n'est pas très visible quand même...

26 mars 2012

Création d’un sparkline

Nous vous avons déjà parlé des graphiques en sparkline, ces mini-graphes qui se logent chacun naturellement dans une cellule de votre tableur favori. Jusqu’à la version 2007 d’Excel, on ne pouvait avoir accès à ces graphes qu’en passant par des compléments (add-ins), ou alors en les construisant soi-même…

Une des grandes nouveautés d’Excel 2010, c’est leur intégration dans le tableur en tant que fonctionnalité à part entière.

Si vous voulez vous replonger dans les articles où je les ai déjà présentés, vous pouvez lire la séquence d’articles publiés il y a un peu plus d’un an :

« Découverte des graphiques en sparkline » : 17 janvier 2011

« Utilisation des graphiques en sparkline » : 21 janvier 2011

« Des sparklines avec Excel 2003 ou 2007 » : 25 janvier 2011

« Une macro pour le sparkline » : 29 janvier 2011

Dans la copie d’écran ci-dessous, nous avons en B2:G6 une série de valeurs. Pour obtenir les graphes en sparkline représentés dans la colonne H, voici les étapes à suivre :

sélectionner l’ensemble des valeurs, ici le bloc B2:G6 ;

ruban Insertion, groupe « Graphiques sparkline » ;

sélectionner le sparkline – cf. copie d’écran en A8:C11 ;

identifier la plage de destination, ici H2:H6 – cf. copie d’écran en D8:H15.

Et voilà, vous obtenez le résultat visible dans les cellules H2 à H6.

Ici, nous avons défini d’un bloc l’ensemble des sparklines. Nous aurions aussi pu définir un premier sparkline, en sélectionnant B2:G2 et en logeant le résultat en H2. Puis tirer H2 vers le bas jusqu'en H6... Le résultat aurait été le même et les cinq graphes auraient été – dans ce cas-là aussi – groupés : dès que l’on clique sur l’un d’entre eux, on active tout le groupe…

Remarque – On peut sélectionner n’importe lequel des sparklines et – via un clic droit et la commande « Graphiques sparkline » – le dissocier du groupe : dans ce cas, le sparkine actif est dissocié du reste du groupe. Je n’ai pas vu pour ma part quel était le véritable impact du fait qu’ils soient groupés ou non. Si un lecteur a une explication, je suis preneur…

22 mars 2012

Les formats téléphoniques…

Mon dernier post a provoqué un certain nombre de mails de la part de lectrices et de lecteurs au sujet des formats dans Excel. Je vais donc développer l’article précédent en parlant de formats personnalisés utilisés pour représenter des numéros de téléphone.

Supposons pour simplifier que la clientèle de votre entreprise – ou vos contacts, si vous n’êtes qu’un pauvre particulier – soient pour leur plus grande part des français, américains ou allemands.

Vous souhaitez que – pour chacun de ces pays – un format spécifique soit utilisé, comme nous le voyons dans la copie d’écran ci-dessous en E2:E4, à partir des valeurs entrées en A2:A4.

Nous avons reproduit en D2:D4 les formats personnalisés que nous avons entrés en E2:E4 pour obtenir les résultats désirés.

Le principe en est simple… Dans un format personnalisé, tout ce qui est placé entre parenthèses – uniquement des espaces dans notre cas – est représenté tel quel. Chaque « 0 » représente un chiffre obligatoire, c’est-à-dire qui sera affiché quoi qu’il arrive, qu’il soit représentatif ou pas.

Nous utilisons donc en E2:E4 les formats représentés en D2:D4, atteignant en cela notre objectif, mais avec un format différent pour chaque pays.

La « big » question est : « Peut-on utiliser le même format unique dans les trois cellules du bloc E2:E4 ?

La réponse est « Oui ! », à condition d’avoir recours à la seconde formalisation des formats personnalisés d’Excel. La première syntaxe de ces formats est
« Positif;Négatif;Nul;Texte ». Cela signifie que l’on peut entrer jusqu’à quatre formats consécutifs dépendant ainsi de la valeur de la cellule.

Mais il existe une seconde syntaxe pour les formats personnalisés : « [condition 1]format 1;[condition 2]format 2;format 3 ». C’est donc le format reproduit en A7 que nous avons utilisé dans le bloc E9:E11 !

Remarque 1 – Pour obtenir ces résultats, nous avons utilisé en E2 – et reproduit vers le bas en E2:E4 puis en E9:E11 – la formule « =1*A2 ».

Remarque 2 – Et s'il y a un quatrième pays ? « Too bad, my dear... » Le format personnalisé avec des conditions ne permet pas d'avoir plus de deux conditions, donc plus de trois formats...

18 mars 2012

Conseilleurs, mais pas payeurs…

Il m’arrive souvent de consulter des sites d’échange entre utilisateurs Excel.

J’ai d’ailleurs – à plusieurs reprises – trouvé dans ces sites des suggestions tout à fait intéressantes, que j’ai aussitôt partagées avec vous en rédigeant des articles relatifs à ces thèmes.

Il faut cependant faire attention, car il y a là à boire et à manger. J’ai vu aussi dans ces sites des réponses qui, au mieux ne résolvaient pas vraiment le problème et – au pire – engendraient des résultats erronés.

Voici par exemple la traduction de la requête d’un utilisateur d’Excel et les deux réponses qui lui ont rapidement été proposées…

Question – Dans un tableau, j’ai de grands nombres comme par exemple 56312594 et je souhaite les représenter sous la forme 00563 12594, c’est-à-dire en deux groupes de cinq chiffres avec des « 0 » à gauche. J’ai bien mis un « 0 » à gauche de la valeur, mais il n’apparaît pas à l’affichage dans la cellule.

Réponse 1 – Utilisez le format « "00"# ».

Réponse 2 – Soit vous entrez « '00563 12594 », soit vous donnez le format
« Texte » à cette cellule puis vous entrez « 00563 12594 ».

La première réponse ne résout pas la question du groupage en blocs de cinq chiffres. En outre, elle ajoutera toujours deux « 0 » à gauche, même si le nombre proposé comporte neuf chiffres – auquel cas un seul suffirait – ou s’il a dix chiffres, auquel cas il ne faudrait rien ajouter !

La seconde réponse fonctionne, mais elle a l’inconvénient que le nombre, étant considéré comme un texte, sera affiché à gauche de la cellule. Certes, on pourra le forcer à droite, mais c’est un peu du bricolage. Mais il y a pire, c’est que ce nombre étant considéré comme un texte, même s’il pourra être traité comme une valeur quand il sera en référence directe dans une formule (cf. C5 dans l’écran ci-dessus), il sera compté pour « 0 » s’il fait partie d’un bloc dont on fait la somme (cf. A7 ci-dessus).

La bonne réponse aurait été d’utiliser le format « 00000" "00000 », comme nous l’avons fait ci-dessus dans la cellule A6. Comme je le disais dans le titre, les conseilleurs ne sont pas les payeurs, et les conseils ne sont pas toujours bons à suivre...

14 mars 2012

Le filtrage avec Excel 2010

En matière de filtrage, Excel 2010 apporte une nouveauté, le bloc « Rechercher » qui permet de trouver rapidement tous les enregistrements contenant une chaîne de caractères donnée.

Pour mettre en relief les possibilités offertes par cette nouvelle fonctionnalité, j’ai créé une colonne avec 1250 produits consécutifs en colonne A.

Nous constatons d’abord, en déroulant le filtre en A1, que l’on trouve bien tous les produits. N’oublions pas que cela a changé assez récemment : avant Excel 2007, seules les 1.000 premières modalités d’une colonne étaient accessibles par le filtre.

L’image du coin supérieur gauche, dans la copie d’écran ci-dessous, montre que le filtre textuel, avec l’option « Contient », permet d’utiliser les jokers, « ? » pour remplacer un caractère et « * » pour remplacer une chaîne de caractères quelconque. Cella a fonctionné avec toutes les versions d’Excel.

La grande nouveauté d’Excel 2010, en matière de filtre, est l’apparition du bloc « Rechercher », qui permet de gagner beaucoup de temps et d’efficacité dans le filtrage. Nous voyons, avec l’image du coin supérieur droit, ce que cela donne quand on filtre ainsi avec « 029 » :on trouve alors toutes les modalités dans lesquelles la chaîne « 029 » est présente.

Remarque 1 – Attention ! Les jokers ne fonctionnent absolument pas dans le bloc « Rechercher » !

Comme nous puvons le constater avec l’image inférieure gauche, la recherche de la chaîne de caractères « 1?1 » ne donne rien : « Aucun élément en correspond à votre recherche » est en effet la conclusion en bas de cette image. Pourtant, tous les produits de Prod0101 à Prod0109, et de Prod1010 à Prod1019, auraient été retenus si les jokers avaient été reconnus…

Remarque 2 – Si vous voulez mieux voir la copie d'écran ci-dessus et cela marche avec toutes les images du blog faites un double clic dessus !

10 mars 2012

Mettre sous forme de tableau

La commande « Mettre sous forme de tableau » du ruban Accueil d’Excel 2007 et 2010 représente un énorme changement par rapport à la commande
« Format automatique » du menu Edition d’Excel 2003.

Pour en voir une présentation en détail, vous pouvez lire les articles « Mettre sous forme de tableau (a) » du 27 octobre 2008 et « Mettre sous forme de tableau (b) » du 31 octobre 2008.

En résumé, nous avons à présent un plus grand nombre de formats mais avec – à mon jugement – plusieurs inconvénients notables :

• Excel ajoute automatiquement un filtre en haut de chaque colonne, ce dont je ne vois pas l’intérêt en tant qu'option par défaut ;

• aucun des nombreux formats ne permet de simplement mettre en relief de façon automatique les totaux par ligne ou par colonne ;

• plus grave que tout : ces formats créent une sorte de fusion : si deux tableaux partagent des colonnes, on ne peut plus insérer ni détruire de colonne à l’intérieur de la partie commune ;

• idem, si deux tableaux partagent des lignes, on ne peut plus insérer ni détruire de ligne à l’intérieur de la partie commune.

Ce dernier inconvénient est présenté en bas du second article cité plus haut, ainsi que la solution apportée par la commande – bien cachée et mal nommée… – « Convertir en plage ».

Vous pourrez d’ailleurs lire à ce sujet avec intérêt l’article « "Convertir en plage", en VBA 2007 » du 26 octobre 2009. J’y montre comment construire une macro effectuant la conversion en plage.

En conclusion, pour ma part, je n’utilise donc pas la commande « Mettre sous forme de tableau ». Je déplore que la commande « Format automatique » ait complètement disparu et qu’il faille aujourd’hui avoir recours à des compléments Excel tels qu’UpSlide – déjà présenté dans ce blog – pour récupérer cette fonctionnalité. Avec UpSlide, en effet, vous pouvez décorer d'un clic tout un tableau etmieux encorefaire cela en respectant la charte graphique de votre entreprise.

En attendant, faut de mieux, vous pouvez toujours copier un tableau créé sous Excel 2003 et en coller les formats (par collage spécial) sur un tableau 2007 ou 2010. Mais cela ne marchera bien que pour des tableaux ayant la même dimension…

A moins qu’un lecteur ou une lectrice ne propose une meilleure solution…