Prérequis

Vous devez avoir suivi les cours 1 à 8 ou en maîtriser le contenu

La formatrice

Aurélie ARROT

Certifiée Microsoft specialist Excel
Habilitée B2i Adultes

EXCEL

Cours suivants :

COURS 2 : Les graphiques simples

COURS 3 : Mise en relation des données

COURS 4 : Fonction conditionnelle simple

COURS 5 : Automatiser une recherche

COURS 6 : Graphiques combinés

COURS 7 : Fonction conditionnelle imbriquée

COURS 8 : Gérer les dates, le temps et le texte

COURS 9 : Manipuler des listes de données

COURS 10 : Les tableaux croisés dynamiques (TCD)

COURS 11 : Les cases à cocher

COURS 9 : Manipulation des listes de données


 

 

LES OBJECTIFS :
Consolider des données, utiliser le mode plan, créer une base de données cohérente et complète, ajouter, supprimer, modifier des fiches afin d’avoir une base d’informations à jour, effectuer des opérations de recherche, de tri et d’extraction sur cette base de données, appliquer les fonctions propres aux bases de données afin d’effectuer des statistiques

PUBLIC VISE : Tout utilisateur d’Excel étant à l'aise avec Excel

 

 

PROGRAMME

 

Excel permet de faire des récapitulatifs, de cumuler des données provenant de feuilles de calcul distinctes dans une feuille de synthèse.

Dans l'exercice proposé, nous disposons de trois tableaux qui indiquent les productions des motos réalisés par SUZUKI, HONDA et YAMAHA en 2003 et 2004.

Nous allons consolider ces trois tableaux dans une quatrième feuille qui indiquera la production de moto pour SUZUKI, HONDA, YAMAHA.
Pour cela, nous allons créer une liaison de consolidation entre les cellules des différents fichiers afin que les modifications effectuées sur une feuille de calcul source soient automatiquement répercutées sur la feuille destination.

Creation des feuilles de calculs

Dans une 1ère feuille nommé SUZUKI saisissez le tableau ci-dessous :



Dans une 2ème feuille nommé YAMAHA saisissez le tableau ci-dessous :



Dans une 3ème feuille nommé HONDA saisissez le tableau ci-dessous :



Créer une dernière feuille que vous nommerez RECAPITULATIF_MOTOS
Enregistrer le classeur sous le nom : PRODUCTIONS MOTOS 03-04

Programmer les formules de consolidation

Activer la feuille de destination « RECAPITULATIF_MOTOS »
Sélectionner les cellules de B3 jusqu’à C4 et cliquez sur l’onglet Données>Consolider La fenêtre suivante apparaît :

Consolider des données dans Excel

Cliquer dans la fenêtre Référence : afin de déterminer les cellules qui seront additionnées



Ouvrer la feuille SUZUKI et sélectionnez les cellules B3:C4 et répéter la même opération
La fenêtre Consolider réapparaît avec la référence SUZUKI!$B$3:$C$4
Cliquer sur Ajouter, afin de placer les cellules dans la fenêtre Référence Source
Refaire la même opération pour les feuilles YAMAHA et HONDA

Voici ce que vous devez obtenir dans la fenêtre Consolider



Cliquez sur l’option : Lier aux données source puis sur OK
Voici ce que vous obtenez :



Calculez le total.

Cliquer sur les symboles + , le détail apparaît.

Si vous disposez d’une liste de données que vous souhaitez regrouper et synthétiser, vous pouvez créer un plan comportant jusqu’à huit niveaux, un pour chaque groupe.

Un plan vous permet d’afficher rapidement les lignes ou les colonnes de synthèse ou de révéler les détails de chaque groupe. Vous pouvez créer un plan de lignes (comme dans l’exemple ci-après), un plan de colonnes ou un plan de lignes et de colonnes.

Explication du mode plan dans Excel

Une base de données est une collection de données. Par exemple, un fichier d’adresses constitue une base de données dans laquelle sont regroupées un certain nombre d’informations telles que :

  • Le nom de la personne ou de l’organisme.
  • Le prénom si c’est une personne.
  • L’adresse de la personne ou de l’organisme.
  • Le code postal.
  • La ville.


Création d'une base de données

Une base de données est saisie dans la feuille de calcul.
Les différents champs de la base de données occupent chacun une colonne de la feuille de calcul.
Un enregistrement ou une fiche doit tenir sur une ligne de la feuille de calcul.
Eviter d’avoir plus d’une liste (liste=base de données) dans une feuille de calcul, car certaines fonctions telles que le filtrage des données ne peuvent porter que sur une liste à la fois dans une feuille de calcul.

Pour créer une base de données :

  • Saisir sur la première ligne de votre feuille de calcul les noms des champs.
  • Saisir éventuellement les fiches, ne pas laisser de ligne blanche entre la ligne contenant les noms des champs et la première fiche.


  • Disposition d'une liste base de données sur Excel

    Excel identifiera toujours la première ligne de la liste comme ligne des noms de champs. Il suffira qu’une cellule de votre base de données soit sélectionnée. Pour une utilisation plus pratique, il est préférable de nommer votre base de données.

    Tier et filter les données Vous pouvez trier les données sélectionnées par ordre croissant ou décroissant, ou appliquer un filtre temporaire à des valeurs spécifiques.
    Appliquer un filtre sur des données équivaut à masquer temporairement les données qui ne répondent pas aux critères du filtre.
    Sous l’onglet ACCUEIL
    Groupe Edition
    Cliquez sur le bouton Trier et filtrer

    Option de tri et filtre de données sur Excel

    Permet la mise en place des filtres automatiques des cellules sélectionnées. CTRL + MAJ + L
    Lorsque le filtrage est activé, cliquez sur la flèche dans l’en-tête de la colonne concernée pour sélectionner le filtre à appliquer à cette colonne.

    Activer les filtres de données sur Excel



    Vous avez la possibilité de trier croissant (de A à Z) ou décroissant (de Z à A) mais aussi par couleur.
    Pour filtrer sur une valeur, pensez à désélectionner tout (décocher l’option Sélectionner tout) puis sélectionnez-la ou les valeurs à afficher dans la liste.

    Les choix de filtres et de tris varient en fonction du contenu de la colonne.

    Les données de type texte



    Nous retrouvons donc des filtres textuels :



    Les données de type numérique



    Nous retrouvons donc des filtres numériques :



    Les données de type date



    Nous retrouvons donc des filtres chronologiques :



    Les filtres personnalisés :

    Permet de définir un filtre avec un deuxième critère.
    Pour un filtre ET, les données doivent répondre aux deux critères.
    Pour un filtre OU, les données doivent répondre à l’un ou l’autre des deux critères ou bien aux deux.



    Les filtres élaborés :

    Lorsque les options du filtre automatique ou du filtre personnalisé ne suffisent pas à définir vos besoins, vous pouvez utiliser le filtre avancé.



    Vous devez définir une zone de critères contenant les en-têtes de vos colonnes et une zone d’extraction.



    Des critères inscrits sur une même ligne équivalent à du ET.
    Des critères inscrits sur des lignes différentes équivalent à du OU.
    Pour un double critère sur une valeur numérique il faut écrire le filtre dans deux colonnes ayant le même en-tête et se trouvant sur une même ligne.

    Pour nommer une base de données :

  • Sélectionner entièrement les informations + une ligne vide, puis activer l’onglet Formule/Définir un nom.
  • Donner un nom et cliquez sur OK.
  • Une base de données peut contenir des champs calculés, c’est à dire des champs dont la valeur est le résultat d’une formule de calcul impliquant un des champs de la base.



  • Pour visualiser les fiches de la base de données :

    Utiliser les bandes de défilement de la feuille de calcul

    Pour ajouter une fiche dans la base de données, il faut :

    Se positionner dans la feuille de calcul là où vous voulez insérer la fiche
    Clic droit de la souris « Insertion/Ligne»
    Remplir les différentes cellules de la fiche

    Statistiques

    Le fait d’utiliser une base de données permet d’accéder à des fonctions statistiques. On peut ainsi calculer des moyennes, des variances, des écarts types, etc...

    Ces fonctions sont :

  • BDMOYENNE (moyenne des nombres)
  • BDNB (compte les valeurs)
  • BDNBVAL (compte les cellules non vides)
  • BDMAX (recherche la valeur maximum)
  • BDMIN (recherche la valeur minimum)
  • BDPRODUIT (multiplie les valeurs)
  • BDECARTYPE (calcule les écarts types)
  • BDECARTYPEP (calcule les écarts types de la population)
  • BDSOMME (ajoute les valeurs)
  • BDVAR (calcule les variances)
  • BDVARP (calcule les variances de la population)
  • BDLIRE (extrait un enregistrement)


  • La syntaxe pour utiliser ces fonctions dans des formules de calcul est :

    =nom_fonction(Base_de_données;champ;critères)

    Contact

    SARL AAT's au capital de 5000 €

    N° SIREN : 822580056 - N° TVA intracommunautaire : FR40822580056