Réorganisation du TCD

Dans le dernier article, je vous ai laissé avec une question. Que fallait-il faire au TCD pour que le tableau de résultat (âge moyen et salaire moyen en fonction du sexe) ressemble à celui obtenu dans l’article du 17 décembre à l’aide de la fonction bdmoyenne() ?

En fait, je vous propose deux solutions, que vous retrouverez dans la copie d’écran en bas de cet article.

La solution la plus efficace – mais certes pas la plus naturelle ! – revient à déplacer le S Valeurs qui se trouve au départ dans le bloc Colonnes vers le bloc Lignes. Ce n’est pas intuitif, mais cela fonctionne parfaitement et atteint le but que nous nous étions fixé au départ.

La seconde solution est plus naturelle : elle consiste à tout simplement tirer le bloc Sexe – soit depuis la liste du haut, soit en le retirant du bloc Colonnes – pour le loger dans le bloc Lignes. Cette solution est plus naturelle, mais la présentation n’est plus la même que dans notre tableau Excel initial…

Remarque – Dans un cas comme dans l’autre, nous sommes à présent dans un TCD. Si les données de la base de départ sont modifiées, notre tableau de synthèse ne sera actualisé que si nous n’oublions pas d’actualiser le TCD.   

Un premier TCD sur la base

Reprenons la base sur laquelle nous travaillons depuis le 8 novembre. Cette fois-ci, nous plaçons le curseur n’importe où dans la base (je vous rappelle qu’Excel fait alors un [Ctrl]-* pour sélectionner tout ce qu’il y a autour). Puis nous utilisons la commande Insertion – Tableau croisé dynamique et validons aussitôt, ce qui va créer un TCD dans une nouvelle feuille insérée juste avant la feuille de la base.

Nous tirons alors le champ Sexe dans le bloc Colonnes et les champs Salaire et Age dans le champ S valeurs.

Puis nous sélectionnons chacun des deux champs « Somme de… » pour effectuer, via la commande Paramètres des champs de valeurs, deux opérations : remplacer la somme par la moyenne puis, à l’aide du bouton Format de nombre, affecter à ce champ un format approprié.

Pour obtenir la présentation ci-dessous, j’ai ensuite ôté le quadrillage, ajouté un encadrement, modifié les formats de cellules en centré vertical et horizontal, en enfin redimensionné les colonnes pour obtenir un tableau agréable à lire. 

Voici le résultat :

Vous remarquez bien entendu que la présentation n’est pas aussi jolie que celle que nous avions obtenue dans l’article précédent. Il faudrait idéalement avoir une ligne pour le salaire et une autre pour l’âge.

Essayez donc de résoudre ce problème. En cadeau de Noël, je vous donnerai la solution !

Salaire et Age moyens par Sexe

Supposez à présent que nous souhaitions créer une table avec le salaire moyen et l’âge moyen en fonction du sexe.

Nous créons donc une table à cet effet de C43 à F47.

Vous connaissez ma propension à n’utiliser qu’une formule pour un tableau entier chaque fois que c’est possible. En effet, avec une seule formule au lieu de trois (selon le sexe), nous allons à la fois trois fois plus vite et trois fois plus « safe » : une formule représente un risque d’erreur, trois formules trois risques…

La formule magique, entrée en D46 et validée (avec la touche [Ctrl] enfoncée) dans le bloc D46:F47 est : =bdmoyenne($A$5:$F$30;$C46;D$44:D$45).

Remarque 1 – Notez bien qu’il ne faut pas se tromper avec la touche : la base est entièrement figée, le nom du champ est bloqué en colonne et celui de la zone de critères bloqué en ligne.

Remarque 2 – Il y a 12 fonctions BDxxx(). Cette famille de fonctions possède une particularité originale : c’est la seule dont toutes les fonctions ont exactement les mêmes arguments. Quand vous en maîtrisez une, vous les maîtrisez toutes !

Impact des fonctions BDxxx()

Par rapport aux extractions dont nous avons parlé dans les articles précédents, les fonctions BDxxx() ont une grande différence : ce sont des fonctions, ce qui signifie qu’elles se recalculent en permanence. Si par exemple vous entrez m en F45, ce sont les valeurs pour les hommes qui apparaîtront dans les deux lignes en dessous !

Un problème à résoudre…

Une question pour les lecteurs qui connaissent bien les tableaux croisés dynamiques : « Comment faut-il procéder pour créer un TCD qui nous affiche le même tableau qu’en C44:F47 ?

Vous aurez la réponse dans notre prochain article, ce qui vous donne le temps de vous y entraîner…

Extraction depuis la base

Aujourd’hui, nous nous attaquons à l’extraction depuis la base. On peut extraire seulement certains champs, et pas nécessairement dans le même ordre que celui des noms de champs dans la première ligne de la base. Nous allons extraire l’âge et le nom des garçons de 27 ans et des filles de 26 ans.

Fractionnement horizontal

Pour commencer, sélectionnons la ligne 8 puis utilisons la commande Fractionner de l’onglet Affichage pour pouvoir voir à la fois dans l’écran le haut de la base et la zone d’extraction.

Remarque – Parfois, on a besoin de pouvoir voir simultanément trois blocs horizontaux à l’écran. Mais on ne peut fractionner qu’une seule fois ! Comment faire ? La solution est toute simple : il suffit de sélectionner un bloc de lignes temporairement inutile entre le second et le troisième bloc, puis de masquer ces lignes !

Extraction demandée

Nous mettons les critères à partir de B34 pour les avoir à la hauteur de la future extraction. Nous entrons les noms des champs à extraire en E34:F34. A partir de la ligne 44, nous avons une série de « Important » pour montrer que ce qui est en dessous est important.

Nous cliquons n’importe où dans la base (pour qu’elle soit reconnue par Excel) puis nous utilisons la commande Avancé du bloc « Trier et filtrer » du menu Données et nous indiquons que nous voulons extraire à partir de E34:F34 en fonction des critères en B34:C35.

Et voilà le résultat :

Un bon point : les 6 personnes attendues apparaissent aussitôt à partir de la ligne 35.

Un mauvais point : tout le tableau Excel en dessous, dans les colonnes E et F, a été détruit. Vous vous dites probablement « Ce n’est pas grave, je vais faire un [Ctrl]-z pour annuler ». Eh bien non : il est impossible d’annuler une extraction !

Imaginez que vous ayez réglé Excel pour faire une sauvegarde automatique toutes les 10 minutes et qu’elle survienne à ce moment-là ! Vous venez de détruire une partir important de votre modèle et la sauvegarde a pérennisé cette catastrophe…

Une excellente raison pour ne jamais régler Excel pour faire des sauvegardes automatiques. Soyez mature et pensez à faire vos sauvegardes à intervalles réguliers…

Un critère sur les salaires

Créons un nouveau critère pour en finir avec les critères calculés. En A1, nous entrons le nom Paye (je vous rappelle que cela ne doit pas être le nom d’un champ existant) et, en A2, la condition =E6>E7.

Question : selon vous, avec ce critère calculé, à quoi correspondront les « gagnants », c’est-à-dire les participants qui resteront visibles ?

Réfléchissez bien avant de lire la suite… Je propose souvent cet exercice à mes étudiants et à mes stagiaires, et je peux vous dire que rares sont ceux qui trouvent la réponse exacte.

En fait comme la formule nous dit que George gagne plus qu’Alper, cela veut dire que l’on gardera tous ceux qui gagnent plus que le suivant dans la liste. Comment le vérifier facilement ?

Vérification des lignes sélectionnées

La façon d’effectuer cette vérification est toute simple. Une fois la base filtrée à partir de cette nouvelle zone de critères en A1:A2, sélectionnons toutes les lignes qui restent visibles et colorons ces lignes en rouge. Il ne reste alors plus qu’à effacer le filtre pour vérifier comment celui-ci a fonctionné. On voit bien que, pour chaque ligne en rouge, le salaire est supérieur au salaire de la personne suivante ?

L’écran ci-dessous montre le haut du résultat. Il suffirait de repeindre la base en bleu pour rétablir la situation antérieure.

Si la condition avait été =E6>$E$7, nous n’aurions gardé que les personnes gagnant plus de 2.200 € !

Quelques conseils

Profitons-en pour donner deux conseils utiles…

1. Annulation du filtre

Pour annuler un filtre, il y a dans le ruban un petit bouton « Effacer ». Pourquoi donc vous fatiguer à viser ce bouton ? Il est bien plus rapide et plus facile d’utiliser le grand bouton « Filtrer » qui, quand il y a déjà un filtre actif, joue exactement le même rôle !

2. Nom du filtre calculé

Le nom du champ d’un filtre doit être différent de celui des noms des champs de la base. Certes mais, dans notre exemple, nous aurions bien aimé utiliser « Salaire » comme nom de champ. Que peut-on faire ?

Il y a une énorme astuce pour cela, entrez donc " Salaire " sans les guillemets, c’est-à-dire en ajoutant un espace avant et un espace après. Du coup, ce nom est différent des noms des champs de la base !

Mieux encore, si vous avez l’esprit mutin, montrez cela à une personne sans lui expliquer le coup des espaces, et défiez-la de parvenir à obtenir le même résultat que vous…