Overblog
Suivre ce blog
Editer l'article Administration Créer mon blog
Le blog d'education et de formation

Cours excel 2003 /trier des donnees

11 Mai 2009 , Rédigé par mazagan Publié dans #TICEتكنولوجيا المعلومات

trier des donnees

Excel n'est pas un SGBD (Système de Gestion de Bases de Données) comme Access. Il dispose cependant de fonctionnalités respectables et très utiles en la matière et il n'est pas rare d'utiliser Excel pour effectuer des traitements sur d'importantes bases de données (on parle de listes dans la terminologie Excel). Nous présenterons ici quelques fonctionnalités particulièrement utiles en la matière : Le tri étant la première sur la liste.

Trier une base de donnees

Quel que soit le cas de figure, des données triées sont toujours plus faciles à exploiter et à interpréter, d'où l'importance de la fonction de tri proposée par Excel.

Avant de commencer, vous pouvez télecharger la base données excel utilisée dans la suite du cours ici

Après avoir sélectionné une cellule de la plage contenant la liste des données (A1 par exemple), allez dans le menu "Données" et choisissez "Trier". Si Excel ne parvient pas à localiser la liste de données un message d'erreur s'affichera, sinon la boite de dialogue suivante vous sera proposée :

Elle vous offre la possibilité de choisir jusqu'à trois critères de tri ainsi que l'ordre de tri : croissant (du plus petit au plus grand) ou decroissant (du plus grand au plus petit).

Voici par exemple le résultat d'un tri simple par mois croissant :

Année Mois Ventes Vendeur Région
2002 Aoû 1647 jean Est
2003 Aoû 1441 Laurent Est
2003 Aoû 4873 jean Nord
2003 Aoû 8447 jean Est
2002 Avr 450 Laurent Est
2003 Avr 9136 jean Est
2002 Déc 2733 Laurent Ouest
2002 Déc 9614 Laurent Est
2002 Déc 2211 Laurent Ouest
2002 Déc 7047 dacosta Ouest
2003 Déc 7686 dacosta Nord
2003 Déc 479 jean Ouest
2002 Fév 4923 Laurent Sud
2002 Fév 8076 Laurent Sud

On remarque que les données relatives à un mois sont regroupées jusqu'à expiration de ce dernier, on passe ensuite au mois suivant.

Et voici par exemple le résultat d'un tri double par année puis par vendeur :

Année Mois Ventes Vendeur Région
2003 Aoû 1441 Laurent Est
2003 Jan 2686 Laurent Ouest
2003 Jan 9662 Laurent Nord
2003 Jun 5575 Laurent Est
2003 Jun 1361 Laurent Sud
2003 Mai 9566 Laurent Sud
2003 Nov 5720 Laurent Est
2003 Oct 7191 Laurent Nord
2003 Sep 3947 Laurent Sud
2003 Sep 66 Laurent Nord
2003 Sep 8859 Laurent Ouest
2003 Aoû 4873 jean Nord
2003 Aoû 8447 jean Est
2003 Avr 9136 jean Est
2003 Déc 479 jean Ouest
2003 Fév 5010 jean Nord
2003 Fév 2666 jean Ouest
2003 Jan 5594 jean Nord
2003 Jan 29 jean Est
2003 Jul 797 jean Nord
2003 Jul 7612 jean Sud
2003 Mar 7113 jean Ouest
2003 Oct 8165 jean Sud
2003 Oct 6028 jean Est
2003 Déc 7686 dacosta Nord
2003 Fév 3106 dacosta Sud
2003 Jul 9082 dacosta Sud
2003 Oct 3338 dacosta Ouest
2002 Avr 450 Laurent Est
2002 Déc 2733 Laurent Ouest

On notera ici que l'ordre des critères de tri (quand il y en a plus d'un) est important car le premier est le critère principal et le second n'est qu'une organisation des données au sein d'une occurence du premier critère (le même vendeur apparait en 2003 puis en 2002) alors que les années apparaissent en totalité avant de passer à l'année suivante. En inversant l'ordre des critères, on obtiendra la liste suivante :

Année Mois Ventes Vendeur Région
2003 Aoû 1441 Laurent Est
2003 Jan 2686 Laurent Ouest
2003 Jan 9662 Laurent Nord
2003 Jun 5575 Laurent Est
2003 Jun 1361 Laurent Sud
2003 Mai 9566 Laurent Sud
2003 Nov 5720 Laurent Est
2003 Oct 7191 Laurent Nord
2003 Sep 3947 Laurent Sud
2003 Sep 66 Laurent Nord
2003 Sep 8859 Laurent Ouest
2002 Avr 450 Laurent Est
2002 Déc 2733 Laurent Ouest
2003 Aoû 4873 jean Nord
2003 Aoû 8447 jean Est
2003 Avr 9136 jean Est
2003 Déc 479 jean Ouest
2003 Fév 5010 jean Nord
2003 Fév 2666 jean Ouest

Vous remarquerez que l'année varie alors que le vedeur "Laurent" apparait en totalité.

N'hésitez pas à faire plusieurs simulations pour mieux comprendre ce mécanisme.

Une fois que les données sont triées, nous pouvons effectuer des traitements complémentaires.


Filtrer des données >> Accueil

Contrairement au tri que nous avons présenté à la page précédente, l'application d'un filtre à une base de données (une liste) Excel ne nous retourne qu'une partie de celle-ci et non la totalité de la liste (réorganisée) comme cela est le cas pour les tris.

Les filtres ont donc pour rôle de vous permettre d'étudier une partie (ou sous ensemble) des données.

Supposons par exemple qu'on vous demande de remettre un rapport détaillé sur la région du Nord, il n'y a aucun intérêt à s'encombrer des données concernant les autres régions. En appliquant un filtre sur la région, vouz pouvez limiter les données affichées à celles relatives à la région du Nord qui vous intéresse. Voici comment vous pouvez procéder :

Après avoir sélectionné une cellule de la plage contenant la liste des données (A1 par exemple), allez dans le menu "Données" et choisissez "Filtrer..." puis "Filtre Automatique". Si Excel ne parvient pas à localiser la liste de données un message d'erreur s'affichera, sinon vous verrez apparaitre tout en haut de la liste et pour chaque colonne des petits rectangles avec une flèche au centre comme sur le graphique suivant :

En cliquant sur ce petit rectangle, vous ferez dérouler la liste des options applicables à cette colonne :

  1. Tri croissant / decroissant : équivaut à la technique des tris étudiée dans la page précédente
  2. (tous) : pour afficher toutes les données sans aucun tri, cette option est souvent utilisée pour revenir à la liste initiale complète après avoir effectué des filtrages
  3. (10 permiers) : pour ne faire apparaitre que les 10 premieres valeurs de la colonne (option rarement utile)
  4. (personnalisé...) : vous donne accès à des options de tri plus avancées et personnalisables
  5. Enfin, et c'est le plus important, il est possible de selectionner une des valeurs de la colonne, ici "Nord", "Sud", "Est" et "Ouest". En selectionnant "Nord" par exemple, vous limiterez l'affichage des données aux lignes relatives à la region "Nord".

Il est aussi possible de faire des filtres croisés en appliquant simultanement deux filtres à une même liste : Filtrer la region sur "Sud" et filtrer le vendeur sur "Jean", vous obtiendrez alors la liste des ventes effectuées par le vendeur Jean dans la région du Sud.

Pour tout annuler et revenir à la liste initiale, il vous suffit de séléctionner (tous) dans toutes les colonnes ayant subi un filtrage.


Les sous totaux >> Accueil

Après avoir appliqué des tris et/ou des filtres sur une base de données (voir pages précédentes), il n'est pas rare de vouloir degager des données chiffrées sur celle-ci (total, moyenne, variance, nombre...). Pour ce faire, Excel offre plusieurs solutions dont :

  • l'utilisation des formules et fonctions (pour les tableaux simples)
  • l'utilisation des sous totaux (qui est privilégiée dans le cas des bases de données)

Reprenons notre base de données sur les ventes et supposons que l'on veuille faire un bilan annuel des ventes (combien avons-nous vendu pour chacune des années ?).

Mettre en forme les données :

Avant de pouvoir utiliser les sous-totaux, Excel nécessite une mise en forme des données.

Etant donné que l'année est le critère retenu pour faire le bilan (et non le vendeur ou la région), il est indispensable de commencer par trier les données par année (croissant ou decroissant).

Réaliser des statistiques :

Maintenant que les données sont triées, il ne reste plus qu'à ajouter des sous totaux. Pour cela, voici la procédure à suivre :

Sélectionnez une cellule de la plage de données

Dans le menu "Données", choisissez "Sous totaux " pour faire apparaitre la fenêtre suivante :

Comme vous pouvez le constater, il y a 3 options importantes à renseigner :

  • "Ajouter un sous-total à" : ici il s'agit de séléctionner la ou les colonnes contenant les données objet du traitement statistique (dans notre exemple ce sont les ventes)
  • "Utiliser la fonction" : c'est la fonction (traitement) à appliquer aux données de la colonne séléctionnée (somme, moyenne, variance...)
  • "A chaque changement de" : est le critère qui détermine l'application de la fonction sur les données. Dans notre exemple, Excel doit faire la somme des ventes à chaque fois que la valeur de l'année change pour obtenir des totaux annuels. Vous comprenez aussi pourquoi il est indispensable de trier les données avant d'appliquer les sous totaux car sans cela, Excel ferait des sous totaux plusieurs fois pour chaque année (faites un essai pour voir le résultat).

En cliquant sur OK, Excel ajoute des sous totaux à la fin de l'année 2002 et 2003 et un total global. Vous pouvez aussi remarquer qu'à gauche de la feuille de calcul, Excel a ajouté des petits "-". Cliquez sur ces petits boutons et vous verrez disparaitre le détail des données pour ne garder que les totaux. On obtient le résultat synthètique suivant :

Pour retrouver la totalité des données, il suffira d'utiliser les petits boutons "+".

Les sous totaux sont donc une fonction puissante offerte par Excel, toutefois leur maitrise est delicate et nécessite une certaine pratique surtout que dans la réalité les traitements et les critères utilisés sont bien plus complexes que dans cet exemple pédagogique.

De plus, je tiens à mettre l'accent sur le fait que l'appellation "sous totaux" peut prêtrer à confusion car cet outil ne se limite pas aux totaux mais offre aussi des traitements statistiques plus avancés comme nous avons pu le voir.

Pour vous exercer, vous pouvez appliquer des sous totaux par vendeur, région ou mois. Vous pouvez aussi faire des moyennes par vendeur, année, mois et région.


Tableaux croisés dynamiques >> Accueil

tableaux croises dynamiques

Les tableaux croisés dynamiques représentent l'une des fonctionalités les plus puissantes mais aussi les plus délicates à maitriser d'Excel. Une fois maitrisés, ils vous permettront de réaliser des gains considérables aussi bien en productivité qu'en qualité de travail.

Un tableau est fait de lignes et de colonnes, il permet donc typiquement de représenter de manière synthétiques un maximum de trois variables : V1 en lignes, V2 en colonnes et V3 dans les cases. Cette limitation est contournée artificiellement en ajoutant des lignes ou des colonnes supplémentaires car en pratique, il n'est pas rare d'avoir plus de 3 variables à analyser.

Les tableaux croisés dynamiques, sans réellement résoudre le problème car ils sont aussi des tableaux, permettent de rendre cette procédure de contournement plus simple et plus puissante à utiliser.

Creation d'un tableau croise dynamique

Pour illustrer notre propos, nous allons reprendre notre base de données exemple qui a servi à la présentation des autres fonctions de traitement des données (tris, filtres et sous totaux) et l'exploiter en utilisant un tableau croisé dynamique.

Ouvrez ce fichier et selectionnez aléatoirement une cellule de la liste de données. Dans le menu "Données", choisissez "Rapport de tableau croisé dynamique". L'assistant de création des tableaux croisés dynamiques apparaitre alors à l'écran pour vous aider.

La première étape, consiste à choisir la source des données et le type de rapport. Pour la source des données, nous allons choisir la première car nous disposons d'une liste de données Excel. Pour la seconde option, on optera pour un tableau et non le graphique :

Cliquez sur suivant pour faire apparaitre le second ecran.

Ce dernier vous offre la possibilité de définir la plage de données à utiliser. En général, ayant cliqué sur une des cellules de la liste avant de lancer l'assistant, Excel sera en mesure de détecter les données automatiquement. Assurez vous donc que tout est bon et cliquez sur suivant pour passer à la dernière étapge.

Ici vous avez le choix entre la création d'une nouvelle feuille dédiée au tableau croisé dynamique et l'ajout dans la feuille actuelle. Nous vous recommandons de choisir la première option comme sur l'image et de cliquer sur Terminer.

Apparaitre alors une nouvelle feuille avec un tableau vierge, une barre d'outils et une fenetre qui vous permettront d'utiliser le tableau croisé dynamique comme vous le souhaitez.

ASTUCE : Pour gagner encore plus de temps, vous n'avez pas besoin de passer par les trois étapes précédentes car nous utilisons les options par défaut, vous pouvez donc cliquez directement sur Terminer dans la toute première fenetre et obtenir le même résultat.

utilisation d'un teableau croisé dynamique

Pour utiliser un tableau croisé dynamique, il suffit d'ajouter des champs de page, de colonne, de ligne et des champs de données à analyser.

Pour ce faire, il suffit de faire un "glisser - déplacer" depuis la fenetre "Liste de champs de tableau croisé dynamique" vers chacune des zones du tableau comme cela est présenté dans l'animation suivante :

Nous disposons maintenant d'un tableau représentant les ventes de chaque vendeur par mois pour l'ensemble des années, ce qui n'est pas très informatif. Il faut donc réaliser quelques traitements supplémentaires à savoir :

  • choisir l'année souhaitée.
  • choisir la fonction statistique à appliquer aux données.

En effet, on peut souhaiter ne voir que les données de l'année 2002 et se limiter au vendeurs Laurent et Jean, pour cela il suffit de faire comme dans l'animation suivante :

Maintenant essayons d'aller un peu plus loin en essayant de definir pour chacune des années, les moyennes des ventes mensuelles de l'ensemble des vendeurs !

Pour ce faire, il faut modifier l'ensemble du tableau et faire quelques manipulations supplémentaires comme dans l'animation suivante :

Comme vous pouvez le remarquer, l'option "Paramètres de champs..." dans le menu contextuel permet d'appliquer une fonction aux données étudiées (les ventes). On peut donc faire la somme, compter le nombre, faire la moyenne, obtenir la valeur Max ou Min... et ainsi de suite.

Voilà, l'essentiel a été dit concernant les tableaux croisés dynamiques. Toutefois, je vous recommande vivement de vous exercer plusieurs fois car cet outil extraordinairement puissant et utile est loin d'etre simple à manipuler et à comprendre.

 


Les macros Excel >> Accueil

Aussi flexible soit il, Microsoft Excel reste un logiciel destiné à une certaine utilisation; sa personnalisation est donc, comme pour n'importe quel autre logiciel, limitée. Afin, de donner plus de liberté aux utilisateurs, Microsoft a doté l'ensemble de sa suite bureautique Office du langage de programmation VBA (Visual Basic for Application) seul moyen d'augmeneter considèrablement la flexibilité de ces outils. Malheureusement les langages de programmation sont compliqués à comprendre et à utiliser et sont donc limités à une "élite technologique".

Pour remédier à cette situation, Microsoft a introduit une fonction appelée "Macro". Une Macro est un simple programme informatique ecrit en VBA qui a la particularité de s'ecrire automatiquement. En effet, Excel rédige à votre place le code VBA. Vous n'avez donc plus besoin d'apprendre le langage VBA pour accéder aux fonctionnalités offertes par la programmation.

 

Creer une Macro

Pour créer une Macro, allez dans le menu "outils" -> "Macro" -> "Nouvelle Macro...". Dans la fenêtre qui s'affiche, donnez un nom à la macro, choisissez une touche de racourcis pour y accéder facilement (exemple : M) et faites en une petite description.

Une fois ces informations saisies, il ne vous reste plus qu'à appuyer sur la touche "OK" pour lancer l'enregistrement de la Macro. Apparaitra alors la petite fenetre suivante :

barre d'outils macro

Faites ce que vous voulez dans Excel comme ecrire des mots, insérer des formules, formater des cellules... et appuyez sur le boutton "Arrêter l'enregistrement" (petit carré bleu) pour mettre fin à l'enregistrement de la Macro.

 

Executer une Macro

Effacez tout le contenu de la feuille en cours allez dans "Outils" -> "Macro" -> "Macros..." (ou faites Alt + F8) et vous verrez la liste de toutes les Macros enregistrées dans le classeur. Choisissez celle que vous voulez exécuter et appuyer sur le bouton "Exécuter". Observez ce qui se passe.

Vous pouvez aussi lancer la Macro en appuyant simultanemment sur les touches "Ctrl" et la touche que vous avez définie lors de la création de la Macro (exemple : Ctrl + M )

Comme vous le constater, la Macro va recréer la suite des opérations que vous avez faites pendant son enregistrement.

fonctionnement des macros

Pour comprendre le fonctionnement des Macros, il suffit d'aller dans "Outils" -> "Macro" -> "Visual Basic Editor...". Ouvrez alors le module "Module1" et vous verrez apparaitre la procédure VBA correspondant à votre Macro comme dans l'image suivante :

 module VBA macro

Ces quelques lignes disent à Excel ce qui suit:

- Selectionner la cellule A1

- Ecrire Bonjour dans la cellue active (càd A1)

- Selectionner la cellule A2

- Ecrire au revoir dans la cellue active (càd A2)

- Selectionner la cellule A3

- Ecrire merci dans la cellue active (càd A3)

 

Utilisation des Macros

Vous vous demandez sans doute quel pourrait être l'intérêt d'utiliser des Macros dans Excel. Mon avis personnel est que Excel est suffisamment puissant pour ne nécessiter l'utilisation des Macros que dans des cas très spécifiques.

Toutefois, vous remarquez que les Macros sont le moyen le plus simple pour faire de la programmation sur Excel mais aussi et surtout apprendre le VBA facilement.


Les macros complémentaires >> Accueil

MACROS COMPLEMENTAIRES

En plus des classiques Macros traitées dans les pages précédentes, Excel dispose d'une fonctionnalité aussi puissante qu'utle appelée Macros complémentaires.

En plus des dizaines de fonctions dites natives contenues en standard dans Excel et accessibles depuis le menu Insertion > Fonctions...), Microsoft Excel dispose de fonctions supplémentaires appelées macros complémentaires. Une macro complémentaire peut être définie comme étant un groupe de fonctions (ou de procédures) visant à apporter une palette de fonctionnalités additionnelles généralement cohérentes entre elles.

A titre d'exemple, Excel propose un groupe de macros complémentaires appelé "Utilitaire d'analyse" dédié à l'analyse statistique des données qui s'avère être particulièrement utile et puissant lorsque l'on a des données à analyser en profondeur et que les fonctiosn standards ne suffisent donc plus.

Ces macros n'étant pas standards, elles ne sont pas nativement disponibles et doivent donc d'abord être installées avant de pouvoir être utilisées.

Pour accèder au menu des Macros Complémentaires, il suffit d'utiliser Outils > Macros complémentaires... Dans la fenêtre qui s'affiche, il faut cocher le groupe de macros à installer et cliquer sur OK. Excel vous avertira que la macro n'est pas installée et vous offrira la possibilité de l'ajouter, pour cela vous aurez généralement besoin du CD d'installation.

Une fois installées, ces macros seront disponibles à l'utilisation.

creer une macro complementaire (ou fonction personnalisee)

Pour aller plus loin dans la comprehension et l'utilisation de ces macros complémentaires, je vous propose de créer notre propre fonction personnalisée qui puisse répondre à nos attentes spécifiques. Pour cela, on devra passer par la création d'une macro complémentaire.

Supposons que vous souaitez connaitre ce que votre banque vous facturera pour chaque transaction en utilisant une fonction personnalisée appellée "FraisBanque()". Supposons aussi que votre banque ait la politique tarifaire suivante : 3% de frais sur le montant de la transaction si le montant est inférieur à 100 Euros et 2% sinon avec une commission fixe de 2 Euros quel que soit le montant.

Il est évident qu'une simple fonction SI peut faire l'affaire mais ici l'objectif est de comprendre le fonctionnement des macros complémentaires et non de résoudre le problème posé !

Nous allons procéder en quatre étapes :

Réaliser le programme VBA

La première étape consiste à "écrire" le programme VBA associé à cette nouvelle fonction (macro).

Pour cela il faut ouvrir un nouveau classeur Excel et se rendre dans l'éditeur Visual Basic via Outils > Macro > Visual Basic Editor (vous pouvez aussi utiliser le raccourci clavier Alt + F11) puis ajouter un module en utilisant Insertion > Module. Dans la page de module qui apparait, saisissez le code suivant correspondant à la politique tarifaire de la banque :

----------------------------------------------------------

Function FraisBanque(Montant As Double)

If Montant < 100 Then
FraisBanque = (Montant * 0.03) + 2
Else
FraisBanque = (Montant * 0.02) + 2
End If

End Function

---------------------------------------------------------

Nous ne nous attarderons pas sur le code en lui même car ce n'est pas un cours de programmation, ce qu'il faut toutefois noter c'est le nom de la fonction "FraisBanque" et que l'on utilisera plus tard pour faire appel à cette fonction, il faut donc utiliser un nom descriptif et différent de ceux déjà utilisés en standard dans Excel.

Créer la macro complémentaire

En deuxième lieu, il est important d'enregistrer le fichier au format XLA qui désigne les fichiers macros complémentaires. Pour cela il suffit d'aller dans le menu Fichier > Enregistrer Classeur1... dans l'éditeur VBA et de bien choisir, dans la fenêtre qui s'affiche, l'option "Macro Complémentaire Microsoft Office Excel (*.xla)" dans le menu déroulant des "Type de fichier". Choisissez ensuite un nom descriptif et enregistrez le fichier. Attention, il est préférable de ne pas changer l'emplacement du fichier créé.

Activer la macro complémentaire

La dernière étape avant de pouvoir utiliser votre nouvelle macro consiste à la rendre disponible (l'activer). Pour cela, il faut quitter l'éditeur VBA pour revenir à votre classeur Excel et aller dans le menu Outils > Macro complémentaire... Dans la fenetre qui s'affiche, cliquez sur le bouton "Parcourir..." pour rechercher le fichier XLA. Si vous n'avez pas modifié l'emplacement de l'enregistrement pas défaut, le fichier devrait apparaitre dans cette fenêtre et il ne vous restera plus qu'à le sélectionner pour l'ajouter aux macros complémentaires. Votre macro figure désormai dans la liste des macros complémentaires et il ne reste plus qu'à la cocher et cliquer sur OK pour la rendre active.

Utiliser la macro

Maintenant que notre macro est correctement configurée dans Excel et donc disponible depuis n'import quel classeur, il est possible de l'utiliser de manière très simple comme pour les fonctions classiques.

En tapant '=FraisBanque(110)' dans la barre de formule correspondant à une cellule de votre feuille Excel, vous obtiendrez le résultat '4.2' qui correspond donc à 2% du montant + 2 !

Comme pour n'importe quelle fomule, vous pouvez mettre en argument de la fonction des cellules plutot qu'une valeur numérique.

Pour vous aider à faire tout cela rapidement, je vous propose une petite vidéo de la procédure à suivre :

Enfin, il est important de signaler que cette fonction personnalisée ne sera disponible que sur l'ordinateur où la fonction complémentaire à été créée et que pour la rendre disponible sur d'autres machines, il faut à chaque fois suivre la démarche présentée ci-dessus.


Accueil

Partager cet article

Commenter cet article