Formation Excel les graphiques et tableaux croises dynamiques

Tous les utilisateurs d’Excel savent qu’il est bien plus qu’une « super-calculatrice ». C’est certes un outil de calcul très puissant, mais c’est aussi un outil de présentation des données, que ce soit sous forme de tableaux ou de graphiques, un outil de simulation et d’analyse, etc

Excel 2007 en quelques mots

La version 12 d’Excel, commercialisée sous le nom d’Excel 2007, marque une rupture avec les versions précédentes en ce qui concerne l’ergonomie. Un nombre croissant d’utilisateurs estimait en effet que les menus devenaient parfois inextricables, rendant complexe l’accès à certaines fonctions. Les concepteurs d’Excel 2007 ont donc répondu à cette préoccupation en changeant radicalement l’accès aux diverses fonctions. L’interface, qui reposait jusqu’à présent sur des menus et des barres d’outils, a été remplacée par un Ruban constitué d’onglets organisés en fonction de l’action à accomplir. Ainsi, Excel dispose par exemple d’onglets tels que Mise en page, Insertion ou Formules, qui regroupent les commandes selon les tâches que vous souhaitez accomplir.
Ces onglets sont associés aux galeries qui vous proposent de choisir parmi un ensemble de résultats potentiels, plutôt que de spécifier des paramètres dans des boîtes de dialogue (voir Figure 1.2).Une fonction d’aperçu instantané permet de visualiser directement, sur le document, l’effet d’une modification ou d’une mise en forme lorsque vous déplacez le pointeur de votre souris sur les résultats proposés dans une galerie.

Travailler avec des données en grande quantité

Supposons que vous disposiez d’une feuille de calcul contenant les ventes pour différentes catégories de produits et différentes régions pour les années 2006 et 2007. Vous devez réaliser un tableau de synthèse présentant le total des ventes par région pour la seule année 2007.

Découvrir la notion de tableau croisé dynamique

Pour les utilisateurs qui sont déjà quelque peu familiarisés avec les tableaux croisés dynamiques, il peut paraître saugrenu d’envisager d’utiliser d’autres fonctionnalités pour mener à bien cette tâche ! Ce détour a toutefois le mérite de bien montrer la puissance de cet outil en termes de souplesse et de productivité.

Les filtres et les sous-totaux

Nous allons à présent construire le tableau présentant le total des ventes par région pour la seule année 2007. 1 Créez tout d’abord une nouvelle feuille dans le classeur. 2 Dans la feuille contenant les données, activez le filtre automatique en cliquant sur le bouton Filtrer du groupe Trier et filtrer de l’onglet Données. 3 Cliquez sur le bouton fléché de la colonne Exercice et choisissez l’année 2007. Validez par OK. Sélectionnez l’ensemble des données visibles à l’aide de la combinaison de touches [Ctrl]+[A].5 Copiez ensuite la sélection ([Ctrl]+[C]) et collez-la sur la feuille vierge préalablement créée. Le reste du travail sera effectué sur cette feuille. 6 Il s’agit tout d’abord de s’assurer que les données sont bien triées par région. Pour cela, sélectionnez la cellule A2 et cliquez sur le bouton Trier et filtrer du groupe Edition de l’onglet Accueil, puis choisissez Trier de A à Z. 7 Vous allez à présent définir des sous-totaux dans la base de données. Il s’agira de totaliser, pour chaque région, le contenu de la colonne CA. Pour cela, cliquez sur le bouton Sous-total du groupe Plan de l’onglet Données. 8 Dans la boîte de dialogue Sous-total, sélectionnez le champ Région dans la zone À chaque changement de, puis Somme dans la zone Utiliser la fonction et enfin cochez CA dans la liste Ajouter un sous-total à. Une fois ces choix effectués, cliquez sur OK. Nous pourrions éventuellement en rester là, mais n’oublions pas que le but est un tableau de synthèse avec seulement les totaux par région. Il faut donc poursuivre notre labeur. Dans un premier temps, nous allons figer les valeurs obtenues en sélectionnant tout le tableau ([Ctrl]+[A]), en le copiant ([Ctrl]+[C]) et enfin en le collant sur lui-même sous forme de valeurs (cliquez sur le bouton Coller du groupe Presse-papiers de l’onglet Accueil, puis choisissez Coller des valeurs).
10 Il faut maintenant supprimer le plan créé suite à la mise en place des sous-totaux. Pour cela, cliquez sur le bouton Dissocier du groupe Plan de l’onglet Données et sélectionnez Effacer le plan.
11 Il faut maintenant regrouper les lignes de synthèse en début de tableau. Pour cela, cliquez sur le bouton Trier et filtrer du groupe Edition de l’onglet Accueil, puis choisissez Trier de Z à A.

Les formules complexes

Complexifions un tout petit peu le travail demandé en ajoutant une dimension à la synthèse. Il s’agit désormais de présenter sur un tableau à double entrée le total des ventes par région pour les années 2006 et 2007.Nous allons utiliser une technique, peut-être plus proche de « l’esprit » des tableaux croisés dynamiques. Dans un premier temps, nous allons construire l’ »ossature » du tableau, puis le remplir avec les valeurs correspondantes. 1 Créez tout d’abord une nouvelle feuille dans le classeur. 2 Pour construire la structure du tableau de synthèse, il faut tout d’abord identifier la liste des valeurs uniques de la colonne Région. Pour cela, nous allons utiliser le filtre dit « avancé ». Cliquez sur le bouton Avancé du groupe Trier et filtrer de l’onglet Données. 3 Dans la boîte de dialogue Filtre avancé, sélectionnez Copier vers un autre emplacement. Cliquez ensuite dans la zone Plage et sélectionnez la colonne A de la base de données. La zone de critère doit demeurer vide. Cliquez dans la zone Copier dans et sélectionnez la cellule K1 de la feuille active. Pour terminer, cochez Extraction sans doublon pour obtenir la liste des valeurs uniques de la colonne Région. Validez par OK.