Monsieur Excel
Pour tout savoir faire sur Excel !

29 janvier 2010

Temps par service et par date

Dans un rapport, on dispose de durées d’utilisation d’un outil par service et par date. On souhaite pourvoir consulter à tout moment la durée cumulée utilisée par un service donné à une date donnée. On entre donc les paramètres en F1:F4, le résultat étant affiché en F5.

La formule de F5 est : =sommeprod((A2:A21=F4)*(B2:B21=date(F1;F2;F3))*C2:C21)

Remarque 1 – Notez que cette formule, grâce à la fonction sommeprod(), n’a pas besoin d’être matricielle. Certes, elle marche encore si on l’entre comme formule matricielle, mais pourquoi faire compliqué quand on peut faire simple...

Remarque 2 – N’oubliez pas d’attribuer le format « [hh]:mm:ss » à la cellule F5, sinon vous aurez des surprises si le temps cumulé dépasse les 24 heures.

25 janvier 2010

Le problème des graphes de TCD

Le drame avec un TCD, c’est qu’il est très difficile – depuis l’année néfaste où Microsoft a inventé les « Tableau croisé dynamique avec graphe » – d’obtenir le graphe que l’on veut avec un TCD.

Prenons un exemple simple. Nous avons sélectionné, en maintenant la touche [Ctrl] enfoncée, à la fois A5:A23 et J5:J23. Nous souhaitons tout simplement tracer un histogramme du salaire moyen par pays, tous âges confondus. Voici la sélection :
Naïvement, nous insérons un histogramme de la nature la plus simple qui soit, et nous obtenons un résultat ne correspondant pas du tout à notre attente, le résultat (a) ci-dessous.

Pour obtenir le résultat souhaité, il faut en fait copier le bloc A4:J23, le coller à côté – pour éliminer son statut de TCD –, sélectionner la première et la dernière colonne, et recréer le graphe qui, enfin, comme on le voit avec le résultat (b), correspond enfin à notre attente.

Remarque 1 – Pour une fois, c’est encore pire avec Excel 2003 ou XP car, dans ce cas, Excel ajoute deux problèmes supplémentaires : il crée le graphe sur un onglet séparé (je trouve les graphes créés sur des onglets séparés totalement inefficaces), et en outre il ajoute des menus déroulants non voulus de tous côtés dans ce graphe.

Remarque 2 – Certains pourront objecter que l’on peut toujours, dans le TCD, remettre l’âge en « Filtre du rapport » pour obtenir le même résultat. Certes, mais on ne peut donc alors plus voir à la fois le graphe et le salaire moyen par pays et par âge.

21 janvier 2010

Les réglages des options de TCD

Le 17 janvier, nous avons présenté une des options de TCD, accessible par le menu contextuel « Options du tableau croisé dynamique » qui apparaît dès que l’on fait un clic droit dans le TCD. Aujourd’hui, nous allons étudier quelques-unes des autres options possibles.

Dans l’onglet « Disposition et mise en forme », je vous conseille vivement de ne jamais cocher la première commande.

Si vous voulez en savoir plus sur les problèmes liés à la fusion de cellules, lisez mon article « Ne fusionnez plus jamais vos cellules ! » du 17 novembre 2005.

Les autres commandes de l’onglet « Disposition et mise en forme » ne posent pas de problème particulier.

L’onglet « Totaux et filtres » autorise plusieurs réglages : on ne voit pas bien l’utilité de masquer les totaux (mais au moins on peut le faire) ; en revanche, la possibilité d’afficher ce que l’on veut dans les cellules vides – et surtout dans les cellules contenant des erreurs – est bien utile. En effet, quand on affiche la moyenne d’un champ, on obtient souvent des « #DIV/0 » qu’il est plus aisé de masquer ainsi qu’avec un format conditionnel.

L’onglet « Affichage » contient un certain nombre de commandes dont la plus utile est celle de la disposition classique présentée il y a quatre jours.

Pas de remarque particulière à faire quant à l’onglet « Impression ».

Dans le dernier onglet, « Données », notez la présence de la commande
« Actualiser les données lors de l’ouverture du fichier », inactive par défaut, particulièrement utile quand le TCD utilise des données externes au classeur actif.

Remarque – Notez aussi le menu déroulant « Nombre d’éléments à retenir par champ » : vous avez intérêt à choisir dans ce menu l’option « Aucun » si vous voulez éviter que votre TCD alourdisse inutilement la taille du classeur.

17 janvier 2010

La "disposition classique" du TCD

Nous avons terminé l’article précédent en indiquant pourquoi nous n’étions pas très heureux de la façon don t Excel 2007 présentait le TCD avec deux champs de valeurs affichés. En fait, il y a une parade…

Faites un clic droit n’importe où dans le TCD pour afficher son menu contextuel, puis sélectionnez la commande « Options du tableau croisé dynamique », l’onglet « Affichage », et enfin cochez la commande « Disposition classique du tableau croisé dynamique (glisser de champs dans la grille) ».

Vous obtenez immédiatement une présentation bien plus claire, comme on le voit ci-dessous. En bas de l’image, nous avons reproduit la disposition antérieure du TCD.


Remarque – J’ai trouvé un bug étrange. Si à présent vous décochez la commande, vous ne revenez pas pour autant à la présentation antérieure. Pour cela, il faut annuler avec [Ctrl]-z.

Vous allez penser que je suis encore méchant avec Excel 2007 mais, pour la réalisation de ce modèle, Excel 2003 ou XP présentait tout de même trois avantages :

● Il n’était pas besoin, avec Excel 2003/XP, de faire l’opération – non intuitive ! – de déplacement de « S Valeurs » vers « Etiquettes de lignes » pour obtenir la présentation avec le salaire et l’âge en deux lignes consécutives.

●La présentation de 2003/XP était plus compacte car les pays apparaissaient alors dans une colonne à gauche et deux lignes suffisaient donc par pays.

●Les encadrements par pays étaient parfaitement clairs, par défaut, avec Excel 2003/XP.

13 janvier 2010

Un second champ dans le TCD

Nous avons amélioré le TCD créé il y a quatre jours en formatant les salaires moyens et en ajoutant un second champ, l’âge moyen par pays et par âge.

Puis nous avons permuté l’âge et le sexe, mettant le premier dans le « Filtre du rapport » et le second en « Etiquettes de colonnes ». Et nous avons renommé les titres en B5 et C5 pour les raccourcir. Voici le résultat :

Manifestement, ce n’est pas terrible comme présentation. Nous aimerions, pour chaque pays, afficher le salaire moyen et l’âge moyen (chacun sur une ligne), par sexe. Comment faire ?

La solution n’est pas vraiment intuitive, mais elle est simple : il suffit en effet
de tirer le champ « S Valeurs » qui est apparu dans le bloc « Etiquettes de colonnes » dès que l’on a ajouté le champ « Age », de ce bloc vers le bloc
« Etiquettes de lignes ». Voici le résultat :

Remarque – Je ne suis pas emballé par la présentation d’Excel 2007, où les lignes de séparation apparaissent en-dessous des lignes 5, 8, 11,… Cela donne un peu l’impression que l’Autriche correspond aux lignes 9-11 plutôt qu’aux lignes 11-13.

09 janvier 2010

Création d’un premier TCD en 2007

Avec la version 2007, il y a eu de grands changements dans la façon dont on crée ou modifie un tableau croisé dynamique (TCD).

Pour commencer, mettons le curseur dans la base – n’importe où – puis activons la commande « Tableau croisé dynamique » du menu « Insertion ». Nous pouvons mettre le TCD sur la même feuille ou non, la liste des champs apparaît (à droite ci-dessous) :

Pour obtenir le résultat ci-dessus, nous avons donc tiré le champ « Sexe » dans le bloc « Filtre du rapport », le champ « Age » dans le bloc « Etiquettes de colonnes », le champ « Pays » dans le bloc « Etiquettes de lignes » et enfin le champ « Salaire » dans le bloc « S Valeurs ».

Remarque 1 – Hélas, Excel a mis par défaut en A4 et B3 les titres totalement idiots « Etiquettes de lignes » et « Etiquettes de colonnes » qu’il m’a fallu remplacer à la main par « Pays » et « Age ». Pourquoi ne pas mettre directement les noms des champs, comme cela se faisait intelligemment avec Excel 2003 ou XP ?

Remarque 2 – Comme dans Excel 2003 ou XP, c’est par défaut la somme qui est utilisée comme opérateur pour le salaire. Pour remplacer cette somme par la moyenne, ce qui aurait beaucoup plus de sens sur ce type de base, on utilise la commande « Paramètres des champs de valeurs », qui est accessible soit en faisant un clic droit sur une cellule quelconque à l’intérieur du TCD, soit en déroulant « Somme de Salaire » dans le bloc « S Valeurs ».

Remarque 3 – La commande « Format de nombre » qui apparaît dans le dialogue de la commande « Paramètres des champs de valeurs », permet de modifier le format numérique, par exemple pour afficher des € avec deux décimales.

05 janvier 2010

Liste dynamique des modalités

La liste des pays extraite dans l’article du 30 décembre – à l’instar de toute extraction – n’est évidemment pas dynamique : si l’on modifie le contenu de la base, elle ne se met pas à jour.

Est-il possible de construire une liste de modalités dynamique ?

La réponse est « Oui », au prix cependant d’une formule que seuls 1% (et je pense que je suis ici tout à fait optimiste) des utilisateurs d’Excel sauraient imaginer…
La formule de J6 est tout simplement =F6. Ce n'est bien entendu pas celle-ci qui est difficile :)

La formule de J7, reproduite ensuite jusqu’en J30, est la formule matricielle suivante :
=index(F:F;min(si(estna(equiv(F7:F$31;$H$6:H6;0));ligne(F7:F$31);100)))

Je vous laisse découvrir par vous-même pourquoi cela fonctionne...

Remarque 1 – Restent les inesthétiques « 0 » des lignes 25 à 30. Une première solution pour les masquer est d’utiliser soit un format personnalisé qui masque les 0, par exemple « ;; » (deux points-virgules), soit un format conditionnel attribuant une police blanche aux valeurs égales à 0.

Remarque 2 – Une solution plus simple encore, et là je vous laisse découvrir aussi pourquoi elle fonctionne, revient à entrer un simple espace comme contenu de la cellule F31...

Pour une fois, vous avez deux exercices stimulants pour démarrer la nouvelle année : comprendre la formule de J7 et répondre à la question posée dans la remarque n°2 …

01 janvier 2010

Une joyeuse année 2010 !

Pour vous souhaiter une excellente année 2010, Excel oblige, j’ai cherché à rédiger un texte dont la somme des valeurs des caractères ferait exactement 2010. Et j’ai trouvé le résultat affiché en A1 :

Nous avons reproduit en commentaire les formules de B1 – reproduite ensuite vers le bas – et de E4. La formule de E1 est la somme de la colonne B et celle de E3 est simplement =nbcar(A1).

Notez que la formule matricielle entrée en E4 permet d’obtenir directement le total 2010 des valeurs des caractères du texte en A1, sans avoir besoin de la colonne B. Encore un bel exemple de formule matricielle.

Remarque 1 – Je n’ai pas réussi hélas à me passer de la cellule E3 en remplaçant dans la formule matricielle le « 22 » par « nbcar(A1) ». Cela ne passe pas au niveau de la syntaxe…

Remarque 2 - La beauté de la chose, c'est que le texte de vœux ci-dessus peut être utilisé pendant 10 ans, en adaptant l'année de 2010 à 2019 : chaque fois, la somme des valeurs des caractères sera égale à la valeur de l'année !