Tableur EXCEL/Données

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Import de données

Importer des fichiers texte

Il est possible d’importer des données d’un fichier texte de deux manières : vous pouvez soit ouvrir le document dans Excel, ou bien utiliser l’assistant d’importation de texte pour créer une plage de données externes.
Deux formats de fichier texte peuvent être utilisés :

• Le fichier texte délimité dans lequel des tabulations séparent les différents champs de texte
• Le fichier csv (comma separated values – valeurs séparées par une virgule) dans lequel des virgules (ou des points virgules) séparent les différents champs de texte

Il est également possible de modifier le caractère utilisé pour séparer les données d’un fichier texte délimité ou d’un fichier texte csv

Importer un fichier texte en s’y connectant

Excel offre la possibilité d’importer les données d’un fichier texte externe en créant une plage de données externe dans la feuille de calcul

• Cliquez sur la cellule où vous souhaitez importer les données du fichier texte.
• Dans l’onglet Data > Cliquez sur À partir du texte
• Dans la nouvelle fenêtre, localisez le fichier que vous désirez importer et cliquez sur Importer

L’assistant d’importation de texte s’affiche, si vous souhaitez importer vos données de façon brute, vous pouvez cliquer directement sur Terminer. Si vous souhaitez configurer l’importation de vos données, il est nécessaire de parcourir toutes les étapes de l’assistant d’importation.

• Dans la nouvelle fenêtre importation de données, vous pouvez cliquer sur Propriétés pour paramétrer certaines options d’importation, ou bien spécifier l’emplacement ou vous souhaitez importer vos données sous «Insérer les données dans»
• Cliquez sur OK pour terminer l’importation des données

Excel importe les données externes dans une plage de données à l’emplacement que vous lui aviez indiqué.

Assistant d’Importation de texte

L’assistant d’importation de texte se décline en 3 étapes et permet de spécifier les différents paramètres liés à l’importation des données externes.

Etape 1 sur 3

L’assistant d’importation vous indique le type de données que vous souhaitez importer. Un aperçu est disponible en bas de la fenêtre afin de pré visualiser les données qui sont disponibles dans le fichier et la manière dont elles seront formatées après l’importation.

• Type de données d’origine : Si le document contient des données qui sont séparées par des caractères (tabulations, virgules, points-virgules, etc…) alors choisissez Délimité. Si les différentes colonnes de données sont toutes de largeurs identiques (des colonnes de 5 caractères par exemple), choisissez Largeur Fixe
• Commencer l’importation à la ligne : Il peut arriver parfois que des documents textes contiennent plusieurs lignes d’en-tête spécifiant le contenu du document avant d’avoir les colonnes de données. Ce paramètre permet d’indiquer la première ligne qui devra être prise en compte dans l’importation.
• Origine du fichier : Ce paramètre permet de spécifier le jeu de caractères qui a été utilisé pour créer le fichier texte. Dans la plupart des situations, ce paramètre peut rester sur sa valeur de défaut.
• Aperçu du fichier : Cette zone permet d’avoir un aperçu de l’aspect du formatage des données une fois que celles-ci auront été importées dans la feuille de calcul

Etape 2 sur 3 (Données délimitées)

• Séparateurs : Choisissez le caractère qui permet de délimiter vos champs de texte dans votre fichier texte. Vous avez la possibilité d’activer la case à cocher Autre et de spécifier le caractère de délimitation si celui-ci n’est pas disponible dans la liste
• Interpréter des séparateurs identiques consécutifs comme uniques : Permet d’indiquer si l’importation des données doit prendre en compte la répétition ou non des séparateurs identiques. Par exemple, si le point-virgule est le caractère de délimitation, a;;;b donnera 4 colonnes alors qu’avec l’option activée, il n’y en aurait plus que 2).
• Identificateur de texte : Permet de spécifier à l’assistant d’importation le caractère d’identification des chaînes de caractères. Par exemple, si le point-virgule est le caractère de délimitation et le guillemet est le caractère d’identification de texte, alors h ; « i ; j » ; k donnera 3 colonnes au lieu de 4.
• Aperçu de données : Cette zone permet de s’assurer que les données du fichier sont bien séparées dans les colonnes que vous voulez avoir.

Etape 2 sur 3 (Données de largeur fixe)

• Aperçu de données : Cette zone de prévisualisation permet de définir la largeur des champs que l’on souhaite utiliser pour délimiter les données. Une ligne verticale représente un saut de colonne et peut être supprimée par un double-clic. Il est également possible de créer des sauts de colonne et de les déplacer.

Etape 3 sur 3

• Le bouton avancé permet de configurer :
  • Le séparateur de décimal ou de millier utilisé dans le document texte
  • Qu’une ou plusieurs valeurs numériques peuvent être négatives et se terminer par un signe moins
• Format des données en colonne : Permet de spécifier le type de données d’une colonne. Pour une colonne de type Date, vous pouvez spécifier le format d’affichage de la date (JJ/MM/AA, MM/JJ/AA, etc…). Choisissez Colonne non distribuée si vous ne désirez pas l’importer dans votre feuille de calcul.
• Cliquez sur Terminer pour finir l’importation des données

Tri des données

Le tri sur Excel 2007 permet de trier des données sur une plage ou une feuille sélectionnée, en fonction d’un ou plusieurs critères.

Il est possible de trier les données en fonction de textes (tri croissant : de A à Z ; ou tri décroissant : de Z à A), de nombres (tri croissant : du plus petit au plus grand ; ou décroissant : du plus grand au plus petit) ou encore de dates ou d’heures (tri des plus anciennes aux plus récentes ou des plus récentes aux plus anciennes). Excel propose également de trier les données en fonction d’une liste personnalisée ou en fonction de la mise en forme (couleur de la cellule, couleur de la police, icône de la cellule).

Le tri peut s’effectuer sur une ou plusieurs colonnes, mais également sur une ou plusieurs lignes.

Trier une unique colonne

1. Vérifiez que toutes les données de la colonne à trier sont au même format pour éviter les problèmes de tri. Si toutes les données ne sont pas au même format, mettre en forme(type et format d'affichage) toutes les données de la colonne.
2. Sélectionnez la plage de cellule de la colonne à trier (constituée d’une seule colonne) .
3. Dans l’onglet « Données » du ruban, sélectionnez « Trier » (tri personnalisé) ou cliquez directement sur le tri croissant ou décroissant si cela correspond au tri souhaité. Vous pouvez également faire un clic droit sur la plage de cellules sélectionnées et choisir trier (croissant : de A à Z ; décroissant : de Z à A ; ou tri personnalisé).
4. Si vous avez choisi le tri personnalisé, une boite de dialogue s’ouvre. Vous pouvez alors choisir le type de tri que vous souhaitez effectuer (Valeurs, couleur de la cellule, couleur du texte, icone). Sélectionnez le type de tri puis l’ordre souhaité.

Trier plusieurs colonnes

Il y a deux possibilités pour le tri de plusieurs colonnes :

• Tri de plusieurs colonnes avec un unique critère : Vous modifiez l’ordre d’une colonne et cela modifie l’ordre des lignes entières.

Exemple : Vous avez la liste d’une classe d’élèves qui ont chacun un nom de famille différent. Vous voulez trier les élèves selon leur nom de famille, dans l’ordre croissant. Vous souhaitez donc effectuer un tri en gardant les données des lignes correspondantes (Prénom, date de naissance, adresse…). Vous effectuez donc un tri sur plusieurs colonnes avec un unique critère.

• Tri de plusieurs colonnes avec plusieurs critères : Vous modifiez l’ordre de plusieurs colonnes grâce à un ordre de priorité.

Exemple : Vous souhaitez trier la liste des commandes clients de votre entreprise selon leur nom et la date de leur dernière commande. La priorité est le tri selon le nom, puis si le client a passé plusieurs commandes, celles-ci s’afficheront de la plus récente à la plus ancienne, et non pas dans un ordre aléatoire. C’est le tri sur plusieurs critères.

Tri avec un seul critère

1. Vérifiez que toutes les données de la colonne à trier sont au même format pour éviter les problèmes de tri.
2. Sélectionnez la plage de cellule à trier (constituée de plusieurs colonnes dans ce cas).
3. Dans l’onglet « Données » du ruban, sélectionner « Trier » (tri personnalisé) ou cliquez directement sur le tri croissant ou décroissant si cela correspond au tri souhaité. Vous pouvez également faire un clic droit sur la plage de cellules sélectionnées et choisir trier (croissant : de A à Z ; décroissant : de Z à A ; ou tri personnalisé).
4. Si vous avez choisi le tri personnalisé, une boite de dialogue s’ouvre. Vous pouvez alors choisir le type de tri que vous souhaitez effectuer (Valeurs, couleur de la cellule, couleur du texte, icone). Sélectionnez le type de tri puis l’ordre souhaité.
5. Validez en cliquant sur OK : Le tri s’effectue, et les données de chaque ligne suivent.

Tri avec plusieurs critères

1. Vérifiez que toutes les données de chaque colonne à trier sont au même format pour éviter les problèmes de tri.
2. Sélectionnez la plage de cellule à trier (constituée de plusieurs colonnes dans ce cas).
3. Dans l’onglet « Données » du ruban, sélectionner « Trier » (tri personnalisé). Vous pouvez également faire un clic droit sur la plage de cellules sélectionnées et choisir trier  tri personnalisé.
4. Une boite de dialogue s’ouvre. Vous pouvez alors choisir le type de tri que vous souhaitez effectuer pour chacune des colonnes (Valeurs, couleur de la cellule, couleur du texte, icone). Pour cela, ajoutez autant de niveau que nécessaire (si vous souhaitez trier en fonction de 2 critères, ajoutez 1 niveau à celui déjà présent. Pour trier en fonction de 3 critères, ajoutez 2 niveau, etc…)
5. Choisissez les colonnes à trier par ordre d’importance, le premier tri à faire par Excel se trouvant sur la 1ère ligne de la boite de dialogue.
6. Sélectionnez le type de tri puis l’ordre souhaité pour chacune des colonnes choisies.
7. Validez en cliquant sur OK.

Trier d’après une liste personnalisée

Deux listes personnalisées sont intégrées dans Excel :

• Jours de la semaine
• Mois de l’année

Pour créer une autre liste personnalisée :

1. Cliquez sur le bouton Microsoft Office, puis sur Options
2. Allez dans la catégorie Standard, puis sous Meilleures options pour travailler sous Excel, choississez Modifier les listes.
3. Dans la partie Listes personnalisées, cliquez sur Nouvelle liste.
4. Entrez les valeurs dans la zone Entrées de la liste. ATTENTION : Vous pouvez uniquement créer une liste personnalisée basée sur une valeur (texte, nombre, date ou heure). Vous ne pouvez pas créer de liste personnalisée basée sur une mise en forme (couleur de cellule, couleur de police, icône).
5. Cliquez sur Entrée à chaque nouvelle saisie de ligne.
6. Lorsque votre liste est terminée, cliquez sur Ajouter, puis deux fois sur OK.

Trier des lignes

1. Sélectionnez une plage de cellules.
2. Dans l’onglet Données, cliquez sur Trier
3. Cliquez sur Options. Une boite de dialogue « Options de tri » s’ouvre.
4. Choisissez « de la gauche vers la droite » pour l’orientation. Cliquez sur OK.
5. Effectuez le tri souhaité en choisissant la ligne, le type de tri et l’ordre de tri.
6. Cliquez sur OK pour valider.

Remarques

Remarques	Aide	Image
Boîte de dialogue "Attention"	Si cette boite de dialogue apparaît, cela signifie que d’autres colonnes sont en lien avec la colonne sélectionnée. Vous avez peut-être oublié de sélectionner certaines colonnes. Deux possibilités : - Vous souhaitez trier la ou les colonnes sélectionnées dans prendre en compte les colonnes adjacentes. Cochez « Continuer avec la sélection en cours » et cliquez sur OK. - Vous avez oublié de sélectionner dans votre plage de cellule une ou plusieurs colonnes que vous souhaitez faire apparaître dans votre tri. Cochez « Étendre la sélection » et cliquez sur OK.
Trier en respectant la casse	Dans l’onglet données, cliquez sur Trier. Cliquez sur options. Dans la nouvelle boite de dialogue, cochez « Respecter la casse ». Cliquez sur OK (à deux reprises)
Colonnes sans en-têtes	Si vous n’avez pas d’en-têtes sur vos colonnes, le tri ne prendra pas en compte la première ligne de votre sélection. Pour que toutes les lignes soient prises en compte : Cliquez sur Trier dans l’onglet « Données ». En haut à droite, décochez « Mes données ont des en-têtes ». Cliquez sur OK pour valider.	Mes données ont des en-têtes

Filtre automatique de données

Le filtre automatique est un outil permettant de faciliter la recherche d'informations au sein d'une plage continue de données. Cette recherche se réalise au travers de l’application de filtres sur une ou plusieurs colonnes de la plage de données, permettant donc à l'utilisateur de choisir les informations qu’il souhaite afficher ou masquer. L'utilisateur à la possibilité de filtrer des données numériques, textuelles, ou bien en fonction de la mise en forme des cellules (couleur du texte et de l'arrière-plan)

Exemple d’application du filtre automatique

Logistic data filter example 1

• Sélectionnez n’importe quelle cellule de la plage de données que l’on veut filtrer, pour des résultats plus explicites il est préférable que ces données comportent des en-têtes.
• Cliquez sur Données > Filtrer (Excel va automatiquement afficher des flèches sur les en-têtes des colonnes de votre plage de données)

Logistic data filter example 2

• Cliquez sur la flèche située à côté de l’en-tête de la colonne que vous souhaitez filtrer, et ensuite un menu contextuel s’affiche avec différentes options.
• Cliquez sur Filtre Textuel ou Filtre Numérique.
• Cliquez sur l’un des opérateurs de comparaison. Par exemple, pour afficher uniquement les nombres inférieurs à une valeur, choisissez Inférieur à...

Logistic data filter example 3

• Dans la boîte Filtre Automatique Personnalisé, indiquez ou sélectionnez le critère de tri de vos données. Par exemple pour afficher tous les nombres strictement inférieurs à 1000, dans le champ de saisie est inférieur à, tapez 1000.

Logistic data filter example 4

Utiliser le filtre automatique pour filtrer les données

Le filtre automatique permet de trouver des valeurs, d’afficher ou de masquer des valeurs, sur une ou plusieurs colonnes de données. Vous pouvez filtrer vos données à partir de listes de sélection ou bien en cherchant les valeurs que vous souhaitez afficher. Lorsque vous filtrez vos données, des lignes complètes sont masquées si les valeurs d’une ou plusieurs colonnes ne correspondent pas aux critères des filtres.

• Sélectionnez les données que vous voulez filtrer automatiquement
• Cliquez sur Données > Filtrer
• Cliquez sur la flèche située à côté de l’en-tête de la colonne que vous souhaitez filtrer et faites l’une des actions suivantes :
  • Choisissez une valeur spécifique : Cliquez sur (Sélectionner Tout) pour décocher toutes les cases à cocher, et sélectionnez ensuite les valeurs que vous souhaitez afficher pour la colonne concernée.
  • Recherchez une valeur : Dans le champ de Recherche, indiquez le texte ou le nombre que vous souhaitez afficher pour la colonne concernée.
• Cliquez sur OK pour appliquer le filtre

Retirer un filtre

Si vous ne trouvez pas les données que vous recherchez dans une feuille de calcul, celle-ci peuvent avoir été masquées par un filtre. Par exemple, si vous avez une colonne contenant uniquement des dates, il se peut que celle-ci soit filtrée pour n’afficher que certains mois. Vous pouvez retirer un filtre d’une colonne spécifique ou retirer complètement tous les filtres de la feuille de calcul.

Retirer un filtre d’une colonne de données

• Cliquez sur le bouton Filtrer situé à côté de l’en-tête de colonne
• Dans le menu contextuel cliquez sur "Effacer le filtre de l’en-tête de colonne".

Retirer tous les filtres de la feuille de calcul

• Cliquez sur la bascule Données > Filtrer, ceci désélectionnera le filtre entier

Ruban Filtre

Comment savoir si des données ont été filtrées ?

Si l’outil filtre automatique est actif, vous trouverez l’un des boutons suivant affiché à côté des en-têtes de colonnes :

• Ce bouton indique qu’un filtre est disponible et qu’il n’a pas été utilisé pour filtrer les données dans la colonne
• Ce bouton indique qu’un filtre a été appliqué pour filtrer ou trier des données dans la colonne

Dans la feuille de calcul suivante :

• Un filtre est disponible pour la colonne Stock mais n’a pas été utilisé.
• Le filtre dans la colonne Category a été utilisé pour ne pas afficher la category Engine, de ce fait les lignes 8 et 9 sont masquées.

Conversion de données

La fonction convertir permet de diviser une colonne de donnée texte en plusieurs colonnes. Par exemple vous pouvez à partir d’une colonne nom complet, la diviser en une colonne prénom et une colonne nom de famille.

1ère étape : sélectionnez la colonne de texte que vous voulez fractionner puis cliquez sur convertir.

2^e étape : la fenêtre « assistant de conversion » s’ouvre alors. Cliquez sur délimiter puis suivant. Cochez ensuite le mode de séparation que vous avez utilisé, par exemple espace. (Vous pouvez également cocher plusieurs modes de séparation si vous avez par exemple utilisé une virgule puis un espace entre vos données à fractionner). Un aperçu de vos données s’affiche en bas de la fenêtre. Cliquez sur suivant.

3^e étape : Vous pouvez choisir le format de vos nouvelles colonnes, par exemple texte. Ou bien vous pouvez laisser le format prédéfini par Excel. Un aperçu de vos nouvelles données s’affiche en bas de la fenêtre.

4^e étape : Réduisez la fenêtre en cliquant sur le bouton à droite de la ligne destination (situé au milieu de la fenêtre). Sélectionnez les cellules dans lesquelles vous souhaitez coller vos nouvelles données fractionnées.

5^e étape : Cliquez sur le bouton à droite de la ligne de destination pour agrandir la fenêtre. Cliquez sur terminer.

Suppression des doublons

Cette fonctionnalité permet de supprimer les doublons dans une feuille de calculs.

1. Pour supprimer des doublons, cliquez sur une cellule comprenant des données
2. Cliquez sur le bouton « SUPPRIMER LES DOUBLONS »
3. La fenêtre suivante apparaît
   

1. Cliquez sur le bouton « SELECTIONNER TOUT » pour sélectionner toutes les colonnes. Ou sur le bouton « DESELECTIONNER TOUT » pour désélectionner les colonnes.
   Vous pouvez choisir manuellement les colonnes dans lesquelles il y a des doublons à supprimer en cochant les colonnes (exemple : Année)
   La coche « Mes données ont des en-têtes » fait apparaître le nom des colonnes. Si vous décochez, les colonnes seront nommés « Colonne 1 » pour « Année », « colonne 2 » pour « Modèle » et « colonne 3 » pour « Prix ».
   

    Attention: une donnée dans une même colonne peut avoir été attribuée plusieurs fois, alors que les colonnes adjacentes ne sont pas identiques. Si vous cochez une seule colonne, par exemple "ANNEE", la fonction "SUPPRIMER LES DOUBLONS" effacera toutes les lignes qui ont la même année.

1. Cliquez sur le bouton « OK »
2. S’il n’y a pas de données en doublon, le message "Aucune valeur en double trouvée" apparaît.
3. S’il y a un doublon dans votre feuille de données, comme dans l’exemple ci-dessous :
   
   

Alors les données seront supprimées et le message " x valeurs en double trouvées et supprimées. Il reste y valeurs uniques." apparaît.
Le doublon est alors supprimé.

Validation de données

La validation des données permet de restreindre la saisie de certaines valeurs ou types de données par les utilisateurs du classeur excel. Ainsi les utilisateurs ne pourront pas saisir des données qui ne sont pas valides. Ou bien vous pouvez simplement avertir l’utilisateur que ces données ne sont pas valides. Vous pouvez aussi afficher un message d’instruction sur le type de donnée valide afin d’aider l’utilisateur à saisir les données attendues.

Utiliser la validation des données

• Limiter les données à des éléments prédéfinis dans une liste. Par exemple, vous pouvez limiter la saisie d’une zone géographique à certains pays (France, Allemagne, Belgique)
• Limiter les numéros en dehors d’une plage prédéfinie. Par exemple, vous pouvez limiter la valeur à saisir entre un minimum et en maximum que vous avez prédéfini.
• Limiter le nombre de caractères textuels Par exemple, vous pouvez limiter le texte autorisé dans une cellule à un certain nombre de caractère.
• Restreindre les dates en dehors d’une certaine plage de dates. Par exemple, vous pouvez spécifier une plage de dates comprises 1 mois avant la date du jour à 1 mois après la date du jour.
• Restreindre les heures en dehors d’une certaine plage horaire. Par exemple, vous pouvez spécifier une plage horaire correspondant à l’ouverture d’un magasin.

Vous pouvez utiliser des formules pour la validation des données. Par exemple pour restreindre une plage entre 1 mois avant la date du jour jusqu'à 1 mois après, entrez la formule =AUJOURDHUI()-30 comme date de début et =AUJOURDHUI()+30 comme date de fin de plage.

Vous pouvez utiliser vos formules dans autoriser : personnalisé. Par exemple pour autorisez la mention félicitation uniquement si la moyenne en cellule E2 est supérieure à 16. Dans personnalisé saisissez la formule SI(E2>16;"félicitation").

Vous pouvez utiliser le contenu d'une autre cellule pour autoriser les données de votre cellule. Par exemple pour que le contenu de la cellule résultat soit comprise entre le montant négatif des charges en cellule A2 et le montant des produit en cellule B2. Alors dans minimum saisissez =-A2 et en maximum =B2.

Vous pouvez entourer les données non valides en cliquant sur le bouton "entourer les données non valides". Ainsi toutes les données erronées sont entourées par des cercles rouges. Ces cercles disparaissent automatiquement lorsque les données sont corrigées, sinon il suffit de cliquer sur le bouton "effacer les cercles de validation".

Pour supprimer la validation de données pour une cellule, sélectionnez-la, cliquez sur Validation des données puis effacer tout (dans l'onglet paramètre).

Messages de validation des données

• Message de saisie, il s’affiche lorsque l’utilisateur place son curseur sur la cellule. Il permet de conseiller l’utilisateur sur le type de donnée attendu.

• Message d’erreur, il s’affiche lorsque l’utilisateur entre des données non valides. Il existe plusieurs types d’alerte.

Le type arrêt propose à l’utilisateur de choisir entre recommencer ou annuler ce qui efface la donnée erronée saisie dans la cellule.Ce type de message ne laisse pas la possibilité à l'utilisateur de confirmer qu’il a délibérément entré une donnée non valide.

Message d'avertissement

Le type avertissement laisse trois possibilités à l’utilisateur, "annuler", "non" pour modifier les données saisies et "oui" pour accepter les données non valides.

Message d'avertissement

Le type information, l’utilisateur peut choisir "ok" il accepte les données ou annuler.

Message d'avertissement

Il est possible de personnaliser le message d’erreur, sinon c’est un message par défaut qui s’affiche.

Attention, ces messages ne fonctionnent pas pour 3 cas : lorsque la cellule est remplie par copie ou remplissage, lorsque c’est une formule qui calcul les données de la cellule et lorsqu’une macro entre les données dans la cellule.

Remarque

lorsque vous définissez des paramètres de validation des données alors que les cellules contiennent déjà des données non valides alors rien ne se passe automatiquement. Il faut mettre en évidence les données non valides en les entourant.

Exemple d'application de la validation des données

1ère étape : Sélectionnez une ou plusieurs cellules à valider. Sous l’onglet Données, dans le groupe Outils de données, cliquez sur Validation des données.

2^e étape : Dans « critère de validation » vous pouvez choisir quelles données autoriser, par exemple liste. Puis cliquez sur la zone source et tapez la liste des valeurs séparées par un point-virgule. Par exemple : France ; Belgique ; Allemagne. Vous pouvez également créer cette liste de valeurs par référence à une plage de cellules situées ailleurs dans le classeur. Vérifiez que la case liste déroulante dans la cellule est cochée. Sinon, vous ne pourrez pas voir la flèche de la liste déroulant de la cellule.

validation des données 1

3^e étape : Testez la validation des données pour vérifier qu’elle fonctionne correctement. Essayez d’entrer dans les cellules des données valides et non valides pour vérifier que vos paramètres fonctionnent et que les messages s’affichent de la façon escomptée.

Consolidation de données

Pour synthétiser, résumer, présenter et afficher des résultats de différentes feuilles de calcul, il est possible de consolider les données de chaque feuille dans une feuille unique. Ces nouvelles feuilles uniques peuvent être créées dans le même classeur en tant que « feuille maître » ou dans des classeurs différents.

Pour consolider des données, utilisez la commande Consolider du groupe Outils de données, sous l’onglet Données.

Les différentes consolidations

Plusieurs procédures existent en fonction de ce que vous souhaitez faire :

• Consolidez par position si vous souhaitez organiser les données dans toutes les feuilles de calcul dans un ordre et à un endroit identique ;
• Consolidez par catégorie si vous souhaitez organiser les données différemment dans chaque feuille de calcul, mais utiliser les mêmes étiquettes de ligne et de colonne de sorte que la feuille de calcul maître puisse correspondre aux données;
• Consolidez par formule si vous souhaitez utiliser des formules contenant des références de cellules ou des référence 3D à d’autres feuilles de calcul que vous associez parce que vous ne disposez pas d’une position ou d’une catégorie fiable ;
• Utilisez un rapport de tableau croisé dynamique pour consolider les données à la place d'une consolidation.

Pour que la consolidation fonctionne :

• Assurez-vous que chaque première ligne de chaque colonne contient une étiquette, chaque colonne contient des formats identiques, et la liste ne contient aucune ligne ou colonne vide.
• Placez chaque plage dans une feuille de calcul différente. Ne placez aucune des plages dans la feuille de calcul sur laquelle vous allez placer la consolidation.
• Nommez chaque plage d’un nom différent.
  

Remarque

Attention ! La commande consolider remplit la zone de données nécessaire. Prenez soin de laisser suffisamment de cellules à droite et en dessous de cette cellule sélectionnée pour les données consolidées.

Consolidation par position

1. Choisissez les données à consolider dans chaque feuille de calcul.
2. Cliquez sur la cellule située dans l’angle supérieur gauche de la zone où vous souhaitez faire apparaître les données consolidées dans la feuille de calcul maître.
3. Sous l'onglet Données, dans le groupe Outils de données, cliquez sur Consolider.
4. Dans la zone Fonction, cliquez sur la fonction que vous souhaitez utiliser pour consolider les données.
5. Si la feuille de calcul est dans un autre classeur, cliquez sur Parcourir, à droite de la zone Référence pour choisir le fichier à consolider.
6. Tapez le nom que vous avez attribué à la plage, puis cliquez sur Ajouter. Répétez cette étape pour chaque plage à consolider.
7. Cliquez sur OK
   

Remarque

Attention ! Assurez-vous que toutes les plages ont la même disposition dans les feuilles

Consolidation par catégorie

1. Choisissez les données à consolider dans chaque feuille de calcul.
2. Cliquez sur la cellule située dans l’angle supérieur gauche de la zone où vous souhaitez faire apparaître les données consolidées dans la feuille de calcul maître.
3. Sous l'onglet Données, dans le groupe Outils de données, cliquez sur Consolider.
4. Dans la zone Fonction, cliquez sur la fonction que vous souhaitez utiliser pour consolider les données.
5. Si la feuille de calcul est dans un autre classeur, cliquez sur Parcourir, à droite de la zone Référence pour choisir le fichier à consolider.
6. Tapez le nom que vous avez attribué à la plage, puis cliquez sur Ajouter. Répétez cette étape pour chaque plage à consolider.
7. Cliquez sur OK
   

Remarque

Attention ! Assurez-vous que les étiquettes des colonnes ou des lignes que vous voulez associer ont la même orthographe et la même casse

• Exemple : Les étiquettes "CA annuel" et "Chiffre d'affaires annuel" sont différentes et ne seront pas consolidées.
• Toutes les étiquettes qui ne correspondent pas à celles situées dans les autres zones source génèrent des lignes ou des colonnes distinctes dans la consolidation.
• Vérifiez si toutes les catégories que vous ne souhaitez pas consolider sont dotées d’étiquettes uniques qui n’apparaissent que dans une seule plage source.

Consolidation par formule

1. Dans la feuille de calcul maître, insérez les étiquettes de ligne ou de colonne souhaitées pour les données consolidées.
2. Cliquez sur la cellule située dans l’angle supérieur gauche de la zone où vous souhaitez faire apparaître les données consolidées dans la feuille de calcul maître.
3. Tapez une formule permettant la consolidation :

Si les données à consolider figurent dans des cellules différentes de plusieurs feuilles de calcul	Si les données à consolider figurent dans les mêmes cellules de différentes feuilles de calcul
Entrez une formule avec des références de cellules à d’autres feuilles de calcul, une pour chaque feuille. Par exemple, =SOMME(feuille1!A1;feuille2!B6;feuille3!D9)	Entrez une formule contenant une référence 3D utilisant une référence à une plage de noms de feuille de calcul. Par exemple, =SOMME(feuille1:feuille3!A1)
ASTUCE : "Pour entrer une référence comme feuille1!D8 dans une formule sans la taper directement, cliquez sur l’onglet de la feuille de calcul où figure cette référence, puis sur la cellule concernée."	ASTUCE : "Si le classeur est configuré pour calculer automatiquement les formules, la consolidation par formule est toujours mise à jour automatiquement lorsque les données de feuilles de calcul distinctes changent."

Rapport de tableau croisé dynamique

Il est également possible de créer un tableau croisé dynamique à partir de plusieurs plages de consolidation. Cette méthode équivaut à la consolidation par catégorie, mais offre davantage de possibilités pour la réorganisation des catégories.

Analyse de scénarios

Gestionnaire de scénarios

À la différence de la valeur cible qui part du résultat pour trouver des données, le Gestionnaire de scénarios montre comment des données variables agissent sur les résultats.

Créer un scénario

1. Cliquez sur l’onglet « DONNEES », puis sur le bouton « Analyse de scénarios », puis Gestionnaire de scénarios.

2. Cliquez sur « AJOUTER »

3. Dans la fenêtre « Ajouter un scénario »

• Nommez le scénario
• Dans « cellules variables », indiquez la ou les cellule(s) qui seront modifiées par le gestionnaire de scénario
• Cochez « Changements interdits » afin de protéger les cellules variables
• Cochez « Masquer » pour que les cellules modifiées n’apparaissent pas

4. Cliquez sur OK. Dans la fenêtre « Valeurs de scénarios », saisir les différentes valeurs variables pour chaque cellule variable.

5. Cliquez sur OK. Il est possible de créer plusieurs scénarios.

Analyser les scénarios

Avant de lancer l’analyse, il est préférable de renommer les cellules afin de donner meilleure visibilité et compréhension des résultats.

1. Retournez dans le Gestionnaire de scénarios. Sélectionnez un scénario puis cliquez sur « Synthèse »

« Fusionner » permet de fusionner plusieurs scénarios de feuilles de calculs différentes.

En face de « Cellules variables » apparaît TAUX_DE_BAISSE. C’est le nom de la cellule variable renommée.

2. Choisir le type de rapport. • Synthèse de scénario • Scénario du rapport de tableau croisé dynamique (pas traité dans ce chapitre) Définir la plage de cellules à analyser. Cliquez sur OK

3. Analyse de scénarios Le scénario apparaît sous cette forme, dans une autre feuille de calcul.

La cellule à modifier était la F18 « TAUX_DE_BAISSE ». Dans cet exemple, deux scénarios avaient été créés: l’un avec une baisse de 2%, l’autre avec une baisse de 0,50%. Les cellules résultantes telle que « $B$21 » n’ont pas été renommées, contrairement à la cellule « CA_moy_trim1 ». Le résultat est ainsi plus compréhensible.

L’analyse de scénarios peut être modifiée (mise en forme, suppression d’information, etc.)

Valeur cible

« VALEUR CIBLE » permet de parvenir à un résultat souhaité qui est la « cible », par un ajustement de certaines valeurs. Les ajustements de données variables sont effectués par Excel une fois que le résultat souhaité, dit la valeur cible, a été indiqué

Pour atteindre la valeur cible :

1. Cliquez sur une cellule de la plage du tableau
2. Cliquez sur l’onglet Données puis sur le bouton Analyse scénarios. Enfin, cliquer sur Valeur cible.
3. Dans le champ Cellule à définir, saisir la cellule à modifier. La formule qui doit être saisie dans la cellule à modifier doit utiliser la cellule à modifier.
4. Dans le champ Valeur à atteindre, saisir le résultat souhaité, c'est-à-dire la valeur cible.
5. Dans le champ Cellule à modifier, saisir la ou les cellule(s) que le tableur Excel ajustera pour atteindre la valeur cible.
   
   

Dans cet exemple, nous voulons qu’Excel ajuste la cellule B5 « Nombre de mois » afin de calculer le montant des mensualités. Nous voulons rembourser l’emprunt avec des mensualités de 200 euros. Mais nous souhaitons garder comme taux d’intérêt 3.60%. Pour cela, Excel va modifier la valeur de la cellule B5 « Nombre de mois ».

1. Après avoir cliqué sur OK, l’ « État de la recherche » fait apparaître le résultat suivant.
   

Le taux d'intérêt est inchangé. Les mensualités sont passées à 200 euros comme demandé et le nombre de mensualités passé de 108 mois à 54 mois.

Table de données

La table de données permet d’effectuer des calculs sur un ensemble de données. La table de données présente les différents résultats selon des données variables et offre une comparaison rapide des différents résultats.

Pour créer une table de données :

1. Dans une cellule, entrez la formule qui utilise différentes données du tableau.
2. Saisir différentes variables. Par exemple, différents taux d’intérêt.
3. Sélectionnez la cellule comprenant la formule ainsi que les différents variables. Les résultats apparaissent à côté des données variables. Pour cela, sélectionnez également les cases vides à droite des cellules contenant les données variables.
   
4. Cliquez sur l’onglet Données puis sur le bouton Analyse scénarios. Enfin, cliquez sur Table de données.
5. Indiquer la valeur d’entrée, c’est-à-dire la valeur de base qui permettra d’établir différents résultats
   
6. Dans la fenêtre « Table de données », deux choix sont possibles :
   * Cellule d’entrée en ligne : si les variables sont en ligne, alors saisir la référence de la cellule d’entrée en ligne.
   * Cellule d’entrée en colonne : si les variables sont en colonne (comme dans l’exemple ci-dessus), alors saisir la référence de la cellule d’entrée en colonne.
   Dans cet exemple, la cellule d’entrée est la cellule J4 « Taux d’intérêt ». En effet, Excel va remplacer ce taux par les différentes variables (4,00%, 4,50%, etc.) afin d’avoir les différentes mensualités.
7. Cliquez sur OK. Les différents résultats apparaissent dans les cellules vides à droite des variables.
   

Groupement de données

Grouper des données

La fonctionnalité « Grouper » permet d’analyser des données en un sous-ensemble. Cette fonction permet aussi de voir rapidement les sous-totaux. La fonctionnalité « Grouper » regroupe les données par ligne ou par colonne.

Créer un groupe de données

1. Cliquez sur l’onglet « DONNEES »
   
2. Sélectionnez les colonnes ou les lignes à regrouper
   

Attention : les colonnes et les lignes ne peuvent pas être regroupées ensemble
Dans l’exemple, nous sélectionnons les colonnes B à D.

1. Cliques sur le bouton « Grouper » puis sur l’objet "Grouper"
   
2. La fenêtre suivante apparaît :
   

Il est possible de regrouper les données par :
* ligne (exemple : ligne 1 à ligne 5)
* colonne (exemple : colonne A à colonne E)
Cliquer sur le bouton OK
Cela créera un premier niveau de regroupement des données.

1. Les données sont regroupées par niveau :
   * niveau 1 : premier sous-ensemble. Dans l’exemple, le niveau 1 correspond aux colonnes B à C
   * niveau 2 : deuxième sous-ensemble. Il permet par exemple de regrouper des données dans le premier groupe existant (même méthodologie que pour créer un groupe de niveau 1)
   

Une ligne suivie du signe « - » apparaît. En cliquant sur le signe « - », les colonnes ou lignes se regroupent. En cliquant sur le signe « + », les données se dégroupent et les colonnes apparaissent.

Dissocier un groupe de données

La fonctionnalité « DISSOCIER » permet de supprimer les groupes de données (voir chapitre « GROUPER») Pour dissocier, il y a deux méthodes :

• sélectionnez les colonnes ou lignes groupées et cliquez sur « DISSOCIER »
• Ou cliquez sur le signe « + » puis sur « DISSOCIER »

Insérer des sous-totaux

Grâce à Excel, il est possible de calculer automatiquement les sous-totaux et les totaux d’une colonne à l’aide de l’outil « Sous-Total » de l’onglet « Données » (Plan).
Il vous est possible d’effectuer deux actions différentes :

• Insérer un seul niveau de sous-totaux
• Insérer des sous-totaux imbriqués

Attention, pour que la commande « Sous-Total » fonctionne correctement, il faut que :

• Chaque colonne comporte une étiquette dans la première ligne.
• Chaque ligne contienne des informations similaires.
• Aucune ligne ou colonne de la plage de cellule ne soit vide.

Insérer un seul niveau de sous-totaux.

1. Sélectionnez une cellule dans la plage de cellule à laquelle vous voulez ajouter des sous-totaux.
2. Triez la colonne qui forme le groupe pour lequel vous souhaitez faire le sous-total. Vous pouvez vous reporter au chapitre tri pour de l’aide.
3. Dans l’onglet « Données », cliquez sur « Sous-Total ». La boîte de dialogue « Sous Total » va alors s’afficher.
4. Dans la zone « A chaque changement de », sélectionnez la colonne dont vous voulez calculer le sous-total.
5. Dans « Utilisez la fonction », choisissez la fonction que vous voulez utiliser pour calculer les sous-totaux (somme, moyenne…etc)
6. Cliquez sur « OK »

Insérer des sous-totaux imbriqués.

1. Suivre la procédure pour insérer un unique niveau de sous-totaux
2. Effectuez de nouveau cette procédure, en prenant soin de décocher la case « Remplacer les sous-totaux existants » afin de ne pas écraser les anciens sous-totaux.
3. Répétez l’étape (jusqu’à sept fois) afin d’obtenir le nombre de sous-totaux souhaité.

Remarques

• Pour définir un saut de page automatique après chaque sous-total, cochez la case « Saut de page entre les groupes »
• Pour spécifier la ligne de synthèse des sous-totaux au-dessus de la ligne de détails, décochez la case « Synthèse sous les données ». En laissant cette case cochée, la ligne de synthèse apparaîtra en dessous de la ligne de détails.