1 - Ce que ne sait pas faire un tablau croisé dynamique
Dans OpenOffice Calc la création d'un tableau croisé dynamique se fait via le menu Données/Pilote de données. Comme dans Excel l'assistant est très pratique et permet d'obtenir des analyses rapides dès lors que les requêtes sont simples (compter ou additionner les valeurs d'une colonne par exemple). Les choses se compliquent par contre lorsqu'on désire anlyser des données sur des critères multiples contenus dans des champs différents ou en fonction de certaines conditions.
Prenons la base de données suivante en exemple (elle est volontairement simpliste, l'essentiel étant de comprendre le fonctionnement des fonctions d'analyses de Base de données) :
Si vous avez suivi le tutorial sur les tableaux croisés dynamiques la création d'une synthèse des ventes en nombre et montant par vendeur ne devrait pas poser problème : ces opérations ne travaillent que sur un seul champ à la fois et ne comportent pas de conditions spécifiques.
Si l'on veut faire une synthèse par trimestre par contre, celà signifie qu'une analyse de la date de vente doit être faite avant le comptage. A ce stade il est préférable d'abandonner l'assistant Tableau croisé dynamique au profit d'un tableau personnalisé créé à l'aide de fonctions d'analyse de Bases de données.
2 - Premier aperçu
Pour accéder aux fonctions cliquez sur le bouton Assistant fonctions :
Dans l'onglet Fontions, rubrique Catégorie, sélectionnez Base de données :
Pour les utilisateurs d'Excel : sélectionnez Base de données dans la liste déroulante Sélectionnez une catégorie.
Dans la liste choisissez BDNBVAL :
Sur la partie droite une courte définition de la fonction vous renseigne sur son rôle.
Celles que vous utiliserez le plus souvent sont :
BDMAX : rechercher le maximum
BDMIN : rechercher le minimum
BDMOYENNE : faire une moyenne
BDPRODUIT : pour multiplier
BDSOMME : pour additionner
BDNB et BDNBVAL : pour compter
Cliquez sur Suivant (pour les utilisateurs d'Excel il faut cliquer sur OK). L'assistant affiche alors les paramètres à renseigner dans la fonction :
Ils sont au nombre de 3 dans notre cas : Base de données, Champ et Critères de recherche. En plaçant le curseur dans le champ vide de chaque paramètre une explication nous donne des indications sur le paramètre en question.
Base de données : il s'agit bien évidemment des données figurant dans notre feuille Base.
Champ : c'est la colonne de notre base sur laquelle il faudra faire l'analyse (compter, additionner...).
Critères de recherche : voilà certainement le paramètre qui risque de poser le plus de problème mais qui donne toute la puissance aux fonctions de Base de données. Grâce à ce paramètre nous allons pouvoir spécifier des conditions s'aplliquant à certains champs de la base. C'est sur ce point que nous allons nous atarder.
Cliquez sur Annuler pour quitter l'assistant car en l'état notre classeur n'est pas prêt pour l'utilisation des fonctions Base de données.
3 - Péparation du classeur
Commencez par créer une base comme celle montrée en exemple puis renommez la feuille contenant la base en faisant un clic droit sur l'onglet de la feuille pour choisir l'option Renommer la feuille :
Pour les utilisateurs d'Excel : vous pouvez aussi double cliquer sur l'onglet pour obtenir le même résultat.
Dans la boîte de dialogue nommez la feuille Base :
Renommez de la même manière la feuille 2 en Analyse :
Lors du premier aperçu de l'assistant fonctions Base de données nous avons vu que des critères étaient nécessaires. La prochaine étape consiste donc à créer ceux-ci dans la feuille 3. Renommez celle-ci en Critères.
Dernière étape préalable toutefois : nous allons commencer à créer notre tableau d'anlyse. Ceci permettra de mieux comprendre la création des critères.
Sélectionnez la feuille Analyse et créez une première version sommaire du tableau de synthèse désiré :
Pour l'instant nous ne nous préoccupons pas de la mise en forme. A l'aide de ce tableau nous désirons synthétiser le montant des ventes par trimestre pour chaque vendeur. Pour le premier trimestre par exemple nous avons donc besoin d'une fonction qui additionne les montants des ventes si leur date se situe bien dans le premier trimestre. C'est à ce stade que les critères déjà évoqués vont nous aider.
Pour la cellule B3 par exemple nos critères de sélection devraient être : additionner les somme du champ Prix de vente si le champ Vendeur est égal à Eric et si le champ Date vente contient une date comprise entre le 1er janvier 2006 et le 31 mars 2006 inclus.
Nous allons maintenant examiner comment créer un tel critère de recherche. Sélectionnez la feuille Base et copiez les en-têtes de colonne :
Sélectionnez la feuille Critères et collez-y ces en-têtes en A2 (j'expliquerai plus tard pourquoi ce choix) :
Comme nous voulons compter le montant des ventes d'Eric nous allons spécifier Eric comme critère du champ Vendeur en G3 :
Pour le champ Date vente, nous allons spécifier que nous voulons les dates comprises entre le 1er janvier et le 31 mars 2006.
A ce stade il est nécessaire d'expliquer comment les différents critères sont reliès entre eux :
- si les critères sont sur la même ligne ils sont reliès par ET
- s'ils sont sur des lignes différentes il sont reliés par OU.
Dans l'exemple ci-dessous la recherche portera sur les ventes d'Eric auprès de la Société Office (Client = Société Office ET Vendeur = Eric) :
Dans cet exemple la recherche portera sur les ventes d' Eric ou de Philippe (Vendeur = Eric OU Vendeur = Philippe) :
Si on veut appliquer cette règle à notre travail nous devons donc spécifier : Date Vente >= 01/01/2006 ET Date Vente <= 31/03/2006. Or nous n'avons qu'un seul champ Date Vente.
Il va donc falloir dupliquer ce champ pour entrer nos critères. Dans la feuille Critères cliquez sur l'en-tête de colonne C et choisissez Insérer des colonnes (pour les utilisateurs Excel : vous pouvez également sélectionner l'en-tête C et utiliser la combinaison de touches CTRL+) :
En cellule C2 tapez Date vente et complétez les critères comme ci-dessous :
Nous sommes maintenant prêts pour créer notre première formule.
Aucun commentaire:
Enregistrer un commentaire