Daniel Billy | MasterFormation

Excel – Mode opératoire – Partie 4

Créer un graphique de bout en bout

Les graphiques vous permettent de visualiser vos données de façon à impressionner votre public. Découvrez comment créer un graphique et ajouter une courbe de tendance.

Créer un graphique

Sélectionnez des données pour le graphique.
Cliquez sur Insertion > Graphiques recommandés.
Sous l’onglet Graphiques recommandés, sélectionnez un graphique pour en afficher l’aperçu.

Remarque : Vous pouvez sélectionner les données souhaitées dans le graphique et appuyer sur ALT + F1 pour créer un graphique immédiatement, mais il se peut qu’il ne s’agit pas du meilleur graphique pour les données. Si aucun graphique ne vous convient, sélectionnez l’onglet tous les graphiques pour afficher tous les types de graphiques.

Sélectionnez un graphique.
Sélectionnez OK.

Ajouter une courbe de tendance

Sélectionnez un graphique.
Sélectionnez Création > Ajouter un élément graphique.
Sélectionnez Courbe de tendance, puis le type de courbe de tendance souhaité : par exemple, Linéaire, Exponentielle, Prévision linéaire ou Moyenne mobile.

Ajouter ou supprimer un axe secondaire dans un graphique à partir d’Excel

Lorsque les nombres d’un graphique varient considérablement de série de données aux séries de données, ou lorsque vous avez mélangé des types de données (Price et volume), tracez une ou plusieurs séries de données sur un axe vertical secondaire (valeurs). L’échelle de cet axe affiche les valeurs des séries de données qui lui sont associées. Un axe secondaire fonctionne bien dans un graphique affichant une combinaison d’histogrammes et de graphiques en courbes. Vous pouvez rapidement afficher un graphique comme celui-ci en convertissant votre graphique en graphique combiné.

Graphique combiné avec un axe secondaire

Sélectionner un graphique pour ouvrir Outils de graphique.
Sélectionnez Création > Modifier le type de graphique.
Sélectionnez Graphique combiné > Histogramme groupé – courbe sur un axe secondaire.
Sélectionnez Axe secondaire pour la série de données à afficher.
Sélectionnez la flèche déroulante, puis choisissez Ligne.
Sélectionnez OK.

Ajouter ou supprimer un axe secondaire dans un graphique dans Office 2010

Quand les valeurs dans un graphique 2D varient considérablement d’une série de données à une autre ou que différents types de données sont utilisés (par exemple, des prix et des volumes), vous pouvez tracer une ou plusieurs séries de données sur un axe vertical (ordonnées) secondaire. L’échelle de cet axe représente les valeurs des séries de données qui lui sont associées.

Combinaison de graphiques mise en forme

Après avoir ajouté un axe vertical secondaire à un graphique 2D, vous pouvez également ajouter un axe horizontal (abscisses) secondaire, qui peut être utile dans un graphique en nuages de points ou un graphique en bulles.

Pour mieux distinguer les séries de données tracées sur l’axe secondaire, vous pouvez modifier leur type de graphique. Par exemple, dans un histogramme, vous pouvez convertir les séries de données de l’axe secondaire en graphique en courbes.

Ajouter un axe vertical secondaire (Office 2010)

Vous pouvez tracer des données sur une axe vertical secondaire une série de données à la fois. Pour tracer plusieurs séries de données sur l’axe vertical secondaire, répétez cette procédure pour chacune.

Dans un graphique, cliquez sur la série de données que vous voulez tracer sur l’axe vertical secondaire ou sélectionnez la série de données voulue dans une liste d’éléments. Pour cela, procédez comme suit :

Cliquez sur le graphique.

Cela permet d’afficher les Outils de graphique et les onglets Création, Disposition et Format.

Sous l’onglet Mise en forme, dans le groupe Sélection active, cliquez sur la flèche située dans la zone Éléments de graphique, puis cliquez sur la série de données que vous voulez tracer sur l’axe vertical secondaire.

Image du ruban

Dans l’onglet Mise en forme, dans le groupe Sélection active, cliquez sur Mise en forme de la sélection.

La boîte de dialogue Mettre en forme des séries de données s’affiche.

Remarque : Si une autre boîte de dialogue s’affiche, répétez l’étape 1 et veillez à sélectionner une série de données sur le graphique.

Sous l’onglet Options des séries, sous Tracer la série avec, cliquez sur Axe secondaire, puis sur Fermer.

Un axe vertical secondaire apparaît sur le graphique.

Pour modifier l’affichage de l’axe vertical secondaire, procédez comme suit :

Dans l’onglet Disposition, dans le groupe Axes, cliquez sur Axes.

Cliquez sur Axe vertical secondaire, puis sur l’option d’affichage de votre choix.

Pour modifier les options de l’axe vertical secondaire, procédez comme suit :

Cliquez avec le bouton droit sur l’axe vertical secondaire, puis sur Format de l’axe.

Sous Options de l’axe, sélectionnez les options souhaitées.

Ajouter un axe horizontal secondaire (Office 2010)

Pour effectuer cette procédure, vous devez disposer d’un graphique qui affiche un axe vertical secondaire.

Cliquez sur un graphique qui comporte un axe vertical secondaire.

Cela permet d’afficher les Outils de graphique et les onglets Création, Disposition et Format.

Image du ruban

Sous l’onglet Disposition, dans le groupe Axes, cliquez sur Axes.

Cliquez sur Axe horizontal secondaire, puis sur l’option d’affichage de votre choix.

Modifier le type de graphique d’une série de données (Office 2010)

Dans un graphique, cliquez sur la série de données que vous voulez modifier.

Cela permet d’afficher les Outils de graphique et les onglets Création, Disposition et Format.

Conseil : Vous pouvez également cliquer avec le bouton droit sur la série de données, cliquer sur Modifier le type de graphique de la série, puis passer à l’étape 3.
Sous l’onglet Création, dans le groupe Type, cliquez sur Modifier le type de graphique.
Dans la boîte de dialogue Modifier le type de graphique, cliquez sur le type de graphique à utiliser.
La première zone affiche une liste de catégories de type graphique, et la deuxième les types de graphiques disponibles pour chaque catégorie de type graphique. Pour plus d’informations sur les types de graphiques que vous pouvez utiliser,

Supprimer un axe secondaire (Office 2010)

Cliquez sur le graphique comportant l’axe secondaire que vous voulez supprimer.

Cela permet d’afficher les Outils de graphique et les onglets Création, Disposition et Mise en forme.

Sous l’onglet Disposition, dans le groupe Axes, cliquez sur Axes, sur Axe vertical secondaire ou Axe horizontal secondaire, puis sur l’option Aucun.

Image du ruban

Conseils :

Vous pouvez également cliquer sur l’axe secondaire que vous voulez supprimer et appuyer sur Suppr, ou cliquer avec le bouton droit sur l’axe secondaire, puis sélectionner Supprimer.
Pour supprimer des axes secondaires immédiatement après les avoir ajoutés, cliquez sur Annuler dans la barre d’outils accès rapide, ou appuyez sur Ctrl + Z.

Graphique Colonne

Les données organisées en colonnes ou en lignes dans une feuille de calcul peuvent être représentées dans un histogramme. Un histogramme affiche généralement les catégories le long de l’axe horizontal (abscisses) et les valeurs le long de l’axe vertical (ordonnées), comme illustré dans ce graphique :

Histogramme groupé

Types d’histogrammes

Histogramme groupé et histogramme groupé en 3D

Un histogramme groupé affiche les valeurs sous forme colonnes en 2D. Un histogramme groupé en 3D affiche les colonnes au format 3D, mais il n’utilise pas de troisième axe des valeurs (axe de profondeur). Utilisez ce graphique lorsque vous avez des catégories représentant :
- des plages de valeurs (par exemple, des totaux d’éléments) ;
- des ajustements d’échelle spécifiques (par exemple, une échelle Likert avec des entrées telles que Totalement d’accord, D’accord, Neutre, Pas d’accord, Totalement pas d’accord).
- des noms qui ne sont pas dans un ordre spécifique (par exemple, des noms d’articles, des noms géographiques ou des noms de personnes).
Histogramme empilé et histogramme empilé en 3D Un histogramme empilé affiche les valeurs sous forme de colonnes empilées en 2D. Un histogramme empilé 3D affiche les colonnes empilées au format 3D, mais n’utilise pas d’axe de profondeur. Utilisez ce graphique lorsque vous avez plusieurs série de données et que vous voulez mettre en évidence le total.
Histogramme empilé 100 % et histogramme empilé 100 % en 3D Un histogramme empilé 100 % affiche les valeurs dans des colonnes en 2D qui sont empilées pour représenter 100 %. Un histogramme empilé 3D 100 % affiche les colonnes au format 3D, mais n’utilise pas d’axe de profondeur. Utilisez ce graphique lorsque vous disposez d’au moins deux séries de données dont vous voulez faire ressortir les contributions au total, notamment si le total est le même dans chaque catégorie.
Histogrammes en 3D Les graphiques en histogrammes 3D utilisent trois axes que vous pouvez modifier (un axe horizontal, un axe vertical et un axe de profondeur), et ils comparent des points de données le long des axes horizontal et de profondeur. Utilisez ce graphique lorsque vous voulez comparer des données entre catégories et séries de données.

Graphique courbe

Les données qui sont agencées dans des colonnes ou des lignes sur une feuille de calcul peuvent être représentées dans un graphique en courbes. Dans un graphique en courbes, les données de catégorie sont distribuées de manière régulière le long de l’axe horizontal et toutes les données de valeurs sont distribuées de manière régulière le long de l’axe vertical. Les graphiques en courbes peuvent afficher des données continues sur une période donnée sur un axe mis à l’échelle de façon homogène et s’avèrent idéaux pour afficher les tendances des données à intervalles réguliers, comme les mois, les trimestres ou les années fiscales.

Graphique en courbes avec marques de données

Types de graphiques en courbes

Courbes et courbes avec marques de données Qu’ils soient affichés avec ou sans marques pour indiquer des valeurs de données individuelles, les graphiques en courbes peuvent afficher les tendances au fil du temps ou des catégories espacées de façon uniforme, en particulier si vous avez de nombreux points de données et que l’ordre de leur affichage est important. S’il y a de nombreuses catégories ou que les valeurs sont approximatives, utilisez un graphique en courbes sans marques.
Courbes empilées et courbes empilées avec marques de données Qu’ils soient affichés avec ou sans marques pour indiquer des valeurs de données individuelles, les graphiques en courbes empilées permettent d’afficher la tendance de la contribution de chaque valeur sur une période donnée ou pour des catégories espacées de façon régulière.
Courbes empilées 100 % et courbes empilées 100 % avec des marques de données Qu’ils soient affichés avec ou sans marques pour indiquer des valeurs de données individuelles, les graphiques en courbes empilées 100 % permettent d’afficher la tendance du pourcentage de contribution pour chaque valeur sur une période donnée ou pour des catégories espacées de façon régulière. S’il y a de nombreuses catégories ou que les valeurs sont approximatives, utilisez un graphique en courbes 100 % sans marques.
Courbes 3D Les graphiques en courbes 3D affichent chaque ligne ou colonne de données sous forme d’un ruban en 3D. Un graphique en courbes 3D présente des axes horizontal, vertical et de profondeur que vous pouvez modifier.
Remarques :
- Les graphiques en courbes fonctionnent de façon optimale lorsque vous avez plusieurs séries de données dans votre graphique ; si vous avez une seule série de données, envisagez d’utiliser un graphique en nuages de points à la place.
- Les graphiques en courbes empilés additionnent les données, ce qui ne correspond peut-être pas au résultat attendu. Étant donné qu’il n’est peut-être pas facile de voir que les lignes sont empilées, envisagez d’utiliser à la place un autre type de graphique en courbes ou en aires empilées.

Graphique en secteur et en anneau

Les données qui sont agencées en une colonne ou une ligne dans une feuille de calcul peuvent être représentées dans un graphique en secteurs. Les graphiques en secteurs représentent la taille des éléments d’une série de données par rapport à leur somme. Les points de données dans un graphique en secteurs sont représentés sous forme de pourcentage du graphique entier.

Graphique en secteurs

Il est conseillé d’utiliser un graphique en secteurs quand :

vous avez une seule série de données ;
aucune des valeurs dans vos données n’est négative ;
presqu’aucune des valeurs dans vos données n’est nulle ;
vous n’avez pas plus de sept catégories, qui représentent toutes des parties du graphique entier.

Types de graphiques en secteurs

Secteurs et secteurs en 3D Les graphiques en secteurs affichent la contribution de chaque valeur par rapport à un total au format 2D ou 3D. Vous pouvez extraire manuellement des secteurs d’un graphique en secteurs afin de les mettre en valeur.
Secteurs de secteur et barre de secteurs Les graphiques de type secteurs de secteur ou barres de secteur affichent des graphiques en secteurs avec des valeurs plus petites extraites dans un graphique secondaire en secteurs ou barres empilées, ce qui permet de les différencier plus facilement.

Graphiques en anneaux

Les données qui sont agencées uniquement dans des colonnes ou des lignes dans une feuille de calcul peuvent être représentées dans un graphique en anneaux. De même qu’un graphique en secteurs, un graphique en anneaux représente la relation des différentes parties par rapport au total, mais il peut contenir plusieurs série de données.

Graphique en anneau

Types de graphiques en anneaux

Anneau Les graphiques en anneaux affichent les données en anneaux, où chaque anneau représente une série de données. Si les pourcentages sont affichés dans des étiquettes de données, chaque anneau totalise 100 %.

Remarque : Les graphiques en anneaux ne sont pas très lisibles. Il est possible que vous souhaitiez utiliser un histogramme empilé ou un graphique à barres empilées.

Graphique à barre

Les données qui sont agencées dans des colonnes ou des lignes dans une feuille de calcul peuvent être représentées dans un graphique à barres. Ce type de graphique permet de représenter les comparaisons entre différents éléments. Dans un graphique à barres, les catégories sont généralement disposées le long de l’axe vertical, et les valeurs le long de l’axe horizontal.

Graphique à barres

Il est conseillé d’utiliser un graphique à barres lorsque :

les étiquettes des axes sont longues;
les valeurs affichées sont des durées.

Types de graphiques à barres

Barres groupées et barres groupées en 3D Un graphique à barres groupées affiche les barres au format 2D. Un graphique à barres groupées 3D affiche les barres au format 3D ; il n’utilise pas d’axe de profondeur.
Barres empilées et barres empilées en 3D Les graphiques à barres empilées affichent la relation qui existe entre un tout et ses éléments individuels dans des barres en 2D. Un graphique à barres empilées 3D affiche les barres au format 3D ; il n’utilise pas d’axe de profondeur.
Barres empilées 100 % et barres empilées 100 % en 3D Une barre empilée 100 % affiche des barres en 2D qui comparent le pourcentage de contribution de chaque valeur par rapport à un total entre catégories. Un graphique à barres empilées 3D affiche les barres au format 3D ; il n’utilise pas d’axe de profondeur.

Graphiques en aires

Les données organisées en colonnes ou en lignes dans une feuille de calcul peuvent être tracées dans un graphique en aires. Les graphiques en aires peuvent être utilisés pour tracer les modifications sur une période donnée et attirer l’attention sur la valeur totale pour une tendance. Ce type de graphique, qui affiche la somme des valeurs tracées, indique également la relation qui existe entre un tout et ses différentes parties.

Graphique en aires

Types de graphiques en aires

Aires et aires en 3D Qu’ils soient représentés au format 2D ou 3D, les graphiques en aires affichent la tendance de valeurs sur une période donnée ou d’autres données de catégories. Les graphiques en aires 3D utilisent trois axes (horizontal, vertical et profondeur) que vous pouvez modifier. En règle générale, envisagez d’utiliser un graphique en courbes au lieu d’un graphique en aires non empilées, car les données d’une série peuvent être masquées par les données d’une autre série.
Aires empilées et aires empilées en 3D Les graphiques en aires empilées affichent la tendance de la contribution de chaque valeur sur une période donnée ou d’autres données de catégorie au format 2D. Un graphique en aires empilées 3D fait de même, mais il affiche les aires au format 3D sans utiliser d’axe de profondeur.
Aires empilées 100 % et aires empilées 100 % en 3D Les graphiques en aires empilées 100 % affichent la tendance de contribution en pourcentage pour chaque valeur sur une période donnée ou d’autres données de catégorie. Un graphique en aires empilées 100 % 3D fait de même, mais il affiche les aires au format 3D sans utiliser d’axe de profondeur.

Graphique en nuages de points (XY) et en bulles

Les données qui sont agencées dans des colonnes et des lignes dans une feuille de calcul peuvent être représentées dans un graphique en nuages de points (XY). Placez les valeurs x dans une ligne ou colonne, puis entrez les valeurs y correspondantes dans les lignes ou colonnes adjacentes.

Un graphique en nuages de points comporte deux axes de valeurs : un axe horizontal (x) et un axe vertical (y). Il associe les valeurs x et y en points de données uniques et les affiche à des intervalles irréguliers ou des groupes. Les graphiques en nuages de points sont généralement utilisés pour afficher et comparer des valeurs numériques, telles que des données scientifiques, statistiques et d’ingénierie.

Graphique en nuages

Il est conseillé d’utiliser un graphique en nuages de points lorsque :

vous souhaitez changer l’échelle de l’axe horizontal ;
vous souhaitez que l’axe horizontal soit une échelle logarithmique ;
les valeurs de l’axe horizontal ne sont pas régulièrement espacées ;
l’axe horizontal comporte de nombreux points ;
vous souhaitez ajuster les échelles d’axes indépendants d’un graphique en nuage de points pour fournir plus d’informations sur les données incluant des paires ou des ensembles groupés de valeurs ;
vous souhaitez afficher les similitudes entre les grands ensembles de données au lieu des différences entre les points de données ;
vous souhaitez comparer plusieurs points de données quelle que soit la période ; plus vous incluez de données dans un graphique en nuages de points, plus les comparaisons sont satisfaisantes.

Types de graphiques en nuages

Nuage de points Ce graphique affiche les points de données sans connecter les lignes pour comparer des paires de valeurs.
Nuage de points avec courbes lissées et marques de données, et nuage de points avec courbes lissées Ce graphique affiche une courbe lissée qui connecte les points de données. Les courbes lissées peuvent être affichées avec ou sans marqueurs. Utilisez une courbe lissée sans marqueurs s’il y a de nombreux points de données.
Nuage de points avec lignes droites et marqueurs, et nuage de points avec des lignes droites Ce graphique montre les lignes de connexion entre les points de données. Les lignes droites peuvent être affichées avec ou sans marqueurs.

Graphique en bulles

Tout comme un graphique en nuages, un graphique en bulles ajoute une troisième colonne pour spécifier la taille des bulles utilisées pour représenter les points de données dans les séries de données.

Graphique en bulles

Type de graphiques en bulles

Bulles ou bulles avec effet 3D Ces deux types de graphiques en bulles comparent des ensembles de trois valeurs au lieu de deux et affichent les bulles au format 2D ou 3D (sans utiliser d’axe de profondeur). La troisième valeur spécifie la taille du marqueur de bulle.

Graphique boursier

Les données qui sont agencées dans des colonnes ou des lignes dans un ordre précis dans une feuille de calcul peuvent être représentées dans un graphique boursier. Comme leur nom l’indique, les graphiques boursiers permettent d’afficher les fluctuations des cours boursiers. Cependant, ils peuvent aussi être utilisés pour afficher des fluctuations d’autres données, comme les précipitations quotidiennes ou les températures annuelles. Veillez à disposer vos données dans l’ordre correct pour créer un graphique boursier.

Par exemple, pour créer un graphique boursier simple comportant Max-Min-Clôture, agencez vos données dans des colonnes intitulées Max., Min. et Clôture, dans cet ordre.

Graphique boursier

Types de graphiques boursiers

Max-Min-Clôture Ce graphique boursier utilise trois séries de valeurs dans l’ordre suivant : Maximum, Minimum et Clôture.
Ouverture-Max-Min-Clôture Ce graphique boursier utilise quatre séries de valeurs dans l’ordre suivant : Ouverture, Maximum, Minimum, puis Clôture.
Volume-Max-Min-Clôture Ce graphique boursier utilise quatre séries de valeurs dans l’ordre suivant : Volume, Maximum, Minimum, puis Clôture. Il mesure le volume à l’aide de deux axes des ordonnées : un pour les colonnes qui mesurent le volume et l’autre pour les cours des valeurs.
Volume-Ouverture-Max-Min-Clôture Ce graphique boursier utilise cinq séries de valeurs dans l’ordre suivant : Volume, Ouverture, Maximum, Minimum, puis Clôture.

Graphique en surface

Les données qui sont agencées dans des colonnes ou des lignes dans une feuille de calcul peuvent être représentées dans un graphique en surface. Ce graphique permet de rechercher les meilleures combinaisons entre deux ensembles de données. Comme sur une carte topographique, les couleurs et les formes indiquent les zones qui sont dans la même plage de valeurs. Vous pouvez créer un graphique en surface lorsque les catégories et les séries de données sont des valeurs numériques.

Graphique en surface

Types de graphiques en surface

Surface en 3D Ce graphique présente une vue 3D des données, que vous pouvez imaginer comme une feuille élastique étendue sur un histogramme 3D. Il est généralement utilisé pour montrer les relations entre de grands volumes de données, qu’il serait difficile de voir autrement. Les bandes de couleurs d’un graphique en surface ne représentent pas la série de données : elles indiquent la différence entre les valeurs.
Surface 3D avec structure apparente Le graphique en surface en 3D est affiché sans couleur sur la surface; il s’agit d’un graphique en surface 3D avec structure apparente. Ce graphique affiche uniquement les lignes. Un graphique en surface en 3D avec structure apparente n’est pas facile à lire, mais il peut tracer des jeux de données volumineux plus rapidement qu’un graphique en surface en 3D.
Contour Les graphiques avec contour sont des graphiques en surface vus du dessus, comme des cartes topographiques en 2D. Dans un graphique avec contour, les bandes de couleurs représentent des plages de valeurs spécifiques. Les traits dans un graphique en surface avec contour connectent des points interpolés de valeur égale.
Avec contour filaire Les graphiques avec contour filaire sont également des graphiques en surface vus du dessus. Dépourvu de bandes de couleur sur la surface, un graphique avec structure apparente n’affiche que les lignes. Les graphiques avec contour filaire ne sont pas faciles à lire. Vous souhaiterez peut-être utiliser plutôt un graphique en surface en 3D.

Graphique en radar

Les données organisées en colonnes ou en lignes dans une feuille de calcul peuvent être représentées dans un graphique en radar. Les graphiques en radar comparent les valeurs agrégées de plusieurs série de données.

Graphique en radar

Types de graphiques en radar

Radar et radar avec marquage des données Avec ou sans marquage de chaque point de données, les graphiques en radar représentent les variations des valeurs par rapport à un point central.
Radar plein Dans un graphique en radar plein, la zone couverte par une série de données est colorée.

Excel – Mode opératoire – Partie 3

Des outils à manier avec savoir faire

Application d’une validation des données aux cellules

La validation des données permet de limiter le type de données ou les valeurs que les utilisateurs peuvent entrer dans une cellule. Une des utilisations les plus courantes de la validation des données consiste à créer une liste déroulante.

Sélectionnez les cellules pour lesquelles vous voulez créer une règle.
Sélectionnez validation de données >Data.
Sous l’onglet Paramètres, sous Autoriser, sélectionnez une option :
- Nombre entier : pour que la cellule accepte uniquement des nombres entiers.
- Décimal : pour que la cellule accepte uniquement des nombres décimaux.
- Liste : pour choisir des données dans la liste déroulante.
- Date : pour que la cellule accepte uniquement des dates.
- Durée : pour que la cellule accepte uniquement des heures.
- Longueur du texte : pour limiter la longueur du texte.
- Personnalisé : pour une formule personnalisée.
Sous Données, sélectionnez une condition :
- entre
- non comprise entre
- égal à
- différent de
- supérieur à
- inférieur à
- supérieur ou égal à
- inférieur ou égal à
Sous l’onglet Paramètres, sous Autoriser, sélectionnez une option :
Définissez les autres valeurs requises, en fonction de ce que vous avez choisi pour Autoriser et Données. Par exemple, si vous sélectionnez entre, sélectionnez les valeurs minimum: et maximum: pour la ou les cellules.
Si vous voulez ignorer les espaces vides, activez la case à cocher Ignorer si vide.
Si vous voulez ajouter un titre et un message à votre règle, sélectionnez l’onglet message de saisie , puis tapez un titre et un message.
Activez la case à cocher Quand la cellule est sélectionnée pour que le message s’affiche lorsque l’utilisateur sélectionne les cellules sélectionnées ou pointe sur celles-ci.
Sélectionnez OK.
À présent, si l’utilisateur essaie d’entrer une valeur qui n’est pas valide, une fenêtre contextuelle affiche un message similaire à « Cette valeur ne respecte pas les restrictions de validation des données de cette cellule ».

Créer une liste déroulante

Sélectionnez les cellules pour lesquelles vous voulez créer une règle.

Sélectionnez validation de données >Data.
Sous l’onglet Paramètres, sous Autoriser, sélectionnez une option :
- Nombre entier : pour que la cellule accepte uniquement des nombres entiers.
- Décimal : pour que la cellule accepte uniquement des nombres décimaux.
- Liste : pour choisir des données dans la liste déroulante.
- Date : pour que la cellule accepte uniquement des dates.
- Durée : pour que la cellule accepte uniquement des heures.
- Longueur du texte : pour limiter la longueur du texte.
- Personnalisé : pour une formule personnalisée.
Sous Données, sélectionnez une condition :
- entre
- non comprise entre
- égal à
- différent de
- supérieur à
- inférieur à
- supérieur ou égal à
- inférieur ou égal à
Sous l’onglet Paramètres, sous Autoriser, sélectionnez une option :
Définissez les autres valeurs requises, en fonction de ce que vous avez choisi pour Autoriser et Données. Par exemple, si vous sélectionnez entre, sélectionnez les valeurs minimum: et maximum: pour la ou les cellules.
Si vous voulez ignorer les espaces vides, activez la case à cocher Ignorer si vide.
Si vous voulez ajouter un titre et un message à votre règle, sélectionnez l’onglet message de saisie , puis tapez un titre et un message.
Activez la case à cocher Quand la cellule est sélectionnée pour que le message s’affiche lorsque l’utilisateur sélectionne les cellules sélectionnées ou pointe sur celles-ci.
Sélectionnez OK.
À présent, si l’utilisateur essaie d’entrer une valeur qui n’est pas valide, une fenêtre contextuelle affiche un message similaire à « Cette valeur ne respecte pas les restrictions de validation des données de cette cellule ».

Créer une liste déroulante

Sélectionnez les cellules pour lesquelles vous voulez créer une règle.

Sélectionnez validation de données >Data.
Sous l’onglet Paramètres, sous Autoriser, sélectionnez une option :
- Nombre entier : pour que la cellule accepte uniquement des nombres entiers.
- Décimal : pour que la cellule accepte uniquement des nombres décimaux.
- Liste : pour choisir des données dans la liste déroulante.
- Date : pour que la cellule accepte uniquement des dates.
- Durée : pour que la cellule accepte uniquement des heures.
- Longueur du texte : pour limiter la longueur du texte.
- Personnalisé : pour une formule personnalisée.
Sous Données, sélectionnez une condition :
- entre
- non comprise entre
- égal à
- différent de
- supérieur à
- inférieur à
- supérieur ou égal à
- inférieur ou égal à
Sous l’onglet Paramètres, sous Autoriser, sélectionnez une option :
Définissez les autres valeurs requises, en fonction de ce que vous avez choisi pour Autoriser et Données. Par exemple, si vous sélectionnez entre, sélectionnez les valeurs minimum: et maximum: pour la ou les cellules.
Si vous voulez ignorer les espaces vides, activez la case à cocher Ignorer si vide.
Si vous voulez ajouter un titre et un message à votre règle, sélectionnez l’onglet message de saisie , puis tapez un titre et un message.
Activez la case à cocher Quand la cellule est sélectionnée pour que le message s’affiche lorsque l’utilisateur sélectionne les cellules sélectionnées ou pointe sur celles-ci.
Sélectionnez OK.
À présent, si l’utilisateur essaie d’entrer une valeur qui n’est pas valide, une fenêtre contextuelle affiche un message similaire à « Cette valeur ne respecte pas les restrictions de validation des données de cette cellule ».

Excel – Mode opératoire – Partie 2

Des outils à manier avec savoir faire

Présentation des tableaux Excel

Pour faciliter la gestion et l’analyse d’un groupe de données connexes, vous pouvez transformer un plage de cellules en tableau Excel (anciennement liste Excel).

Exemple de données mises en forme en tant que tableau Excel

Remarques :

Il ne faut pas confondre les tableaux Excel avec les tables de données qui font partie d’une série de commandes d’analyse de simulation. Pour plus d’informations sur les tables de données.
Vous ne pouvez pas créer ou insérer des tableaux dans un classeur partagé.

En savoir plus sur les éléments d’un tableau Excel

Un tableau peut inclure les éléments suivants :

Ligne d’en-tête Par défaut, une table comporte une ligne d’en-tête. Le filtrage de chaque colonne de table est activé dans la ligne d’en-tête afin de pouvoir filtrer ou trier rapidement les données de votre tableau.

Trier ou appliquer un filtre au tableau

Vous pouvez désactiver la ligne d’en-tête dans un tableau.

Lignes à bandes Une trame de fond alternative ou des bandes en lignes permettent de mieux distinguer les données.

Tableau Excel avec données d’en-tête, mais non sélectionné avec l’option Mon tableau comporte des en-têtes et donc, Excel a ajouté des noms d’en-tête par défaut tels que Colonne1, Colonne2.

Colonnes calculées En entrant une formule dans une cellule d’une colonne de tableau, vous pouvez créer une colonne calculée dans laquelle cette formule est instantanément appliquée à toutes les autres cellules de cette colonne.

Ajouter une seule formule dans une cellule de tableau qui s'exécute de manière automatique pour créer une colonne calculée

Ligne Total Une fois que vous avez ajouté une ligne total à une table, Excel vous propose une liste déroulante somme automatique pour effectuer une sélection parmi les fonctions telles que somme, moyenne, etc. Lorsque vous sélectionnez l’une de ces options, le tableau les convertit automatiquement en sous-total, ce qui a pour effet d’ignorer les lignes masquées par défaut dans un filtre. Si vous souhaitez inclure des lignes masquées dans vos calculs, vous pouvez modifier les arguments de la fonction sous.

Exemple de sélection d’une formule de ligne des totaux à partir de la liste déroulante des formules de ligne des totaux

Poignée de redimensionnement Une poignée de redimensionnement située dans le coin inférieur droit vous permet de redimensionner le tableau à la taille voulue

. Faire glisser la poignée de redimensionnement pour redimensionner le tableau

Créer des tables dans une base de données

Vous pouvez créer autant de tables que vous le souhaitez dans une feuille de calcul.

Pour créer rapidement un tableau dans Excel, procédez comme suit:

Sélectionnez la cellule ou la plage dans les données.
Sélectionnez Accueil > Mettre sous forme de tableau.
Sélectionnez un style de tableau.
Dans la boîte de dialogue mettre sous forme de tableau, activez la case à cocher en regard de mon tableau si vous voulez que la première ligne de la plage soit la ligne d’en-tête, puis cliquez sur OK.

Travailler efficacement avec les données de votre tableau

Excel propose des fonctionnalités qui vous permettent de travailler efficacement avec les données de votre tableau:

Utilisation de références structurées Au lieu d’utiliser des références de cellule, comme A1 et L1C1, vous pouvez utiliser des références structurées qui font référence à des noms de tableaux dans une formule.

Garantir l’intégrité des données Vous pouvez utiliser la fonctionnalité de validation des données intégrée dans Excel. Par exemple, vous pouvez choisir de n’autoriser que des nombres ou des dates dans une colonne d’une table.

Totaliser les données d’un tableau Excel

Vous pouvez rapidement additionner des données dans un tableau Excel en activant l’option de ligne Total , puis utilisez l’une des différentes fonctions fournies dans une liste déroulante pour chaque colonne de tableau. Les sélections de ligne total par défaut utilisent la fonction totale qui vous permet d’inclure ou d’ignorer des lignes de tableau masquées, mais vous pouvez également utiliser d’autres fonctions.

Cliquez n’importe où dans le tableau.
Accédez à Outils de tableau > Création, puis activez la case à cocher pour Ligne des totaux.
La ligne des totaux est insérée dans la partie inférieure de votre tableau.

Remarque : Si vous appliquez des formules à une ligne des totaux, puis désactivez et activez cette ligne des totaux, Excel mémorise vos formules. Dans l’exemple précédent, nous avions déjà appliqué la fonction SOMME à la ligne des totaux. Lorsque vous appliquez une ligne des totaux pour la première fois, les cellules sont vides.
Sélectionnez la colonne dont vous souhaitez calculer les totaux, puis sélectionnez une option dans la liste déroulante. Dans ce cas, nous avons appliqué la fonction SOMME à chaque colonne :

Vous noterez qu’Excel a créé la formule suivante : =SOUS.TOTAL(109,[Centre]). Il s’agit d’une fonction SOUS.TOTAL pour SOMME, et d’une formule de référence structurée réservée aux tableaux Excel.

Vous pouvez également appliquer une fonction différente à la valeur totale en sélectionnant l’option Plus de fonctions ou en créant votre propre fonction.

Remarque : Si vous souhaitez copier une formule de ligne des totaux dans une cellule adjacente à la ligne des totaux, faites glisser la formule à l’aide de la poignée de recopie. Les références de colonnes sont alors mises à jour en conséquence et affichent la valeur qui convient. Si vous copiez et collez une formule dans la ligne des totaux, elle ne met pas à jour les références de colonnes et génère des valeurs incorrectes.

Utiliser des colonnes calculées dans un tableau Excel

Colonnes calculées dans les tableaux Excel sont un outil incroyables pour la saisie de formules efficacement. Ils permettent de saisir une seule formule dans une cellule et puis cette formule se développe automatiquement pour le reste de la colonne en lui-même. Il est inutile d’utiliser les commandes remplir ou copier. Cela peut être très gagner du temps, surtout si vous avez un grand nombre de lignes. Et la même chose qui se passe lorsque vous modifiez une formule ; la modification complet s’affiche également dans le reste de la colonne calculée.

Créer une colonne calculée

Créer une table. Si vous n’êtes pas familiarisé avec des tableaux Excel, vous pouvez en savoir plus sur : Présentation des tableaux Excel.

Insérer une nouvelle colonne dans la table. Vous pouvez le faire en tapant dans la colonne située immédiatement à droite de la table, puis Excel étend automatiquement le tableau pour vous. Dans cet exemple, nous avons créé une nouvelle colonne en tapant « Total général » dans la cellule D1.

Ajouter une nouvelle colonne du tableau en tapant dans la colonne vide immédiatement à droite d’une table existante

Conseils :

Vous pouvez également ajouter une colonne de tableau à partir de l’onglet accueil . Cliquez sur la flèche pour Insérer > Insérer des colonnes de tableau à gauche.

Tapez la formule que vous souhaitez utiliser, puis appuyez sur entrée.

Dans ce cas, nous avons entré = Somme (, puis choisi les colonnes Trim 1 et 2 Trim . Par conséquent, Excel intégré la formule : = Somme (Table1 [@[Trim 1] : [Trim 2]]). Il s’agit d’une formule de référence structurée , qui est unique aux tables Excel. Le format de référence structurée est ce qui permet la table à utiliser la même formule pour chaque ligne. Une formule Excel normale pendant ce serait =Somme(B2:C2)dont vous devez alors copie ou remplissage vers le bas pour le reste des cellules dans la colonne
Lorsque vous appuyez sur entrée, la formule est automatiquement appliquée aux cellules de la colonne — au-dessus et au-dessous de la cellule où vous avez entré la formule. La formule est identique pour chaque ligne, mais comme il s’agit d’une référence structurée, Excel sait en interne quelle ligne est qui.

Remarques :

Il est également possible de créer une colonne calculée en remplissant ou en copiant une formule dans toutes les cellules d’une colonne de tableau vide.
Si vous tapez ou déplacez une formule dans une colonne de table qui contient déjà des données, une colonne calculée n’est pas automatiquement créée. Toutefois, le bouton Options de correction automatique s’affiche pour vous permettre de remplacer les données afin que vous pouvez créer une colonne calculée.

Si vous entrez une nouvelle formule est la différence entre les formules existantes dans une colonne calculée, la colonne met automatiquement à jour avec la nouvelle formule. Vous pouvez choisir Annuler la mise à jour, puis conserver uniquement la nouvelle formule unique à partir du bouton Options de correction automatique. Ceci est généralement pas recommandé, car cela peut empêcher la mise à jour automatique à l’avenir, car il ne sait pas quelle est la formule pour étendre lors de l’ajout de nouvelles lignes de la colonne.

Option pour annuler une colonne calculée après l’avoir une formule a été entrée

Si vous avez tapé ou copié une formule dans une cellule d’une colonne vide et ne souhaitez pas conserver la nouvelle colonne calculée, cliquez sur Annuler à deux reprises. Vous pouvez également appuyer sur Ctrl + Z à deux reprises

Excel – Mode opératoire – partie 1

Des outils à manier avec savoir faire

Recopier automatiquement des données dans les cellules d’une feuille de calcul

Utilisez la fonctionnalité Remplissage automatique pour remplir des cellules avec des données qui suivent un motif ou sont basées sur des données d’autres cellules.

1 Sélectionnez une ou plusieurs cellules à utiliser comme base pour remplir d’autres cellules.

2 Pour une série telle que 1, 2, 3, 4, 5…, tapez 1 et 2 dans les deux premières cellules. Pour la série 2, 4, 6, 8…, tapez 2 et 4.

3 Pour la série 2, 2, 2, 2…, tapez 2 uniquement dans la première cellule.

Faire glisser la poignée de recopie .

Le cas échéant, cliquez sur Options de recopie incrémentée Image du bouton , puis sélectionnez l’option de votre choix.

Recopier une formule dans des cellules adjacentes

Vous pouvez rapidement copier des formules dans des cellules adjacentes en faisant glisser la poignée de recopie . Lorsque vous remplissez les formules vers le bas, les références relatives sont placées en place pour garantir que les formules s’ajustent pour chaque ligne, sauf si vous incluez des références absolues ou mixtes avant de remplir la formule.

Suivez les étapes ci-dessous pour remplir une formule et sélectionnez les options à appliquer:

Sélectionnez la cellule contenant la formule à recopier dans des cellules adjacentes.
Faites glisser la poignée de recopie sur les cellules à remplir.Si vous ne voyez pas la poignée de recopie, il est possible qu’elle soit masquée. Pour le réafficher:
1. Cliquez sur fichier > options
2. Cliquez sur Options avancées.
3. Sous options d’édition, cochez la case Activer la poignée de recopie et le glisser-déplacer de la cellule.
Pour modifier le mode de recopie, cliquez sur la petite icône options de recopie incrément qui s’affiche une fois que vous avez terminé le glissement, puis choisissez l’option souhaitée.

Recopier les formules dans des cellules adjacentes

Vous pouvez utiliser la commande remplissage pour recopier une formule dans une plage de cellules adjacente. Procédez simplement comme suit:

Sélectionnez la cellule contenant la formule ainsi que les cellules adjacentes à remplir.
Cliquez sur > d’ Accueil , puis sélectionnez vers le bas, à droite, en hautou à gauche.Raccourci clavier: Vous pouvez également appuyer sur Ctrl + D pour recopier la formule vers le bas dans une colonne ou Ctrl + R pour recopier la formule vers la droite dans une ligne.

Activer le calcul du classeur

Les formules ne seront pas recalculées lorsque vous remplirez des cellules si le calcul automatique de classeur n’est pas activé.

Voici comment l’activer:

Cliquez sur Fichier > Options.
Cliquez sur formules.
Sous calculdu classeur, sélectionnez automatique.

Fractionner du texte en plusieurs colonnes à l’aide de l’Assistant Conversion

Sélectionnez la cellule ou la colonne contenant le texte à fractionner

Sélectionnez Données > Convertir.

Dans l’Assistant Conversion, sélectionnez Délimité > Suivant.

Sélectionnez les séparateurs de vos données. Par exemple, la virgule et l’ espace. Vous pouvez afficher un aperçu de vos données dans la fenêtre Aperçu des données.

Sélectionnez Suivant.

Sélectionnez l’option Format des données en colonne ou utilisez ce qu’Excel a choisi pour vous.

Sélectionnez la Destination (emplacement où vous souhaitez que les données fractionnées apparaissent dans votre feuille de calcul).

Sélectionnez Terminer.

Transposer (faire pivoter) des données de lignes vers des colonnes, ou inversement

Si vous avez une feuille de calcul contenant des données dans des colonnes que vous devez faire pivoter pour la réorganiser dans des lignes, utilisez la fonctionnalité transposer. Grâce à cette fonction, vous pouvez rapidement basculer les données de colonnes en lignes, ou vice-versa.

Par exemple, si vos données ressemblent à ce qui suit, avec les régions commerciales dans les en-têtes de colonnes et les trimestres sur le côté gauche:

Données régionales en colonnes

La fonctionnalité transposer réorganiser le tableau de telle sorte que les trimestres s’affichent dans les en-têtes de colonne et que les régions de vente apparaissent à gauche, comme suit:

Données régionales en lignes

Remarque : Si vos données se trouvent dans un tableau Excel, la fonctionnalité Transposer n’est pas disponible. Vous pouvez commencer par convertir le tableau en plage, ou utiliser la fonction TRANSPOSE pour faire pivoter les lignes et les colonnes.

Voici comment procéder :

Sélectionnez la plage de données que vous voulez réorganiser, y compris les étiquettes de ligne ou colonne, puis appuyez sur Ctrl+C.

Remarque : Vérifiez que vous copiez les données dans le cas contraire, car l’utilisation de la commande couper ou Ctrl + X ne fonctionnera pas.
Dans la feuille de calcul, sélectionnez un nouvel emplacement dans lequel vous voulez coller la table transposée, afin de vous assurer qu’il reste suffisamment d’espace pour coller vos données. Le nouveau tableau que vous collez contient entièrement les données/mises en forme qui y sont déjà.Cliquez avec le bouton droit sur la cellule en haut à gauche de l’emplacement où vous voulez coller la table transposée, puissélectionnez transposer

. Menu Options de collage

Après avoir fait pivoter les données, vous pouvez supprimer la table d’origine, et les données de la nouvelle table resteront intactes.

Conseils pour la transposition de vos données

Si vos données incluent des formules, Excel les met automatiquement à jour conformément à leur nouvelle position. Vérifiez que ces formules utilisent des références absolues (si ce n’est pas le cas, vous pouvez basculer entre les références relatives, absolues et mixtes avant de faire pivoter les données).
Si vous voulez faire pivoter vos données fréquemment afin de les afficher selon différentes perspectives, vous pouvez créer un tableau croisé dynamique afin de faire pivoter rapidement vos données en faisant glisser les champs de la zone lignes vers la zone colonnes (ou inversement) dans la liste de champs de tableau croisé dynamique.

TRANSPOSE, fonction

Vous devez parfois basculer ou faire pivoter des cellules. Vous pouvez effectuer ceci par copier-coller ou à l’aide de l’option TRANSPOSE. L’utilisation de celle-ci crée toutefois des données en double. Pour éviter cela, vous pouvez taper une formule plutôt que d’utiliser la fonction TRANSPOSE. Par exemple, dans l’image suivante, la formule =TRANSPOSE(A1:B4) utilise les cellules A1 à B4 et les réorganise horizontalement.

Cellules d’origine au-dessus, cellules avec la fonction TRANSPOSE en dessous

Étape 1 : sélectionner les cellules vides

Commencez par sélectionner des cellules vides. Veillez à sélectionner le même nombre de cellules que dans l’ensemble de cellules d’origine, mais dans le sens inverse. Par exemple, 8 cellules sont organisées verticalement ici :

Cellules dans A1:B4

Par conséquent, nous devons sélectionner huit cellules horizontales, comme suit :

Cellules A6:D7 sélectionnées

Les nouvelles cellules transposées se retrouveront à cet emplacement.

Étape 2 : taper =TRANSPOSE(

Avec ces cellules vides sélectionnées, tapez : =TRANSPOSE(

Excel se présente comme suit :

=TRANSPOSE(

Notez que les huit cellules sont toujours sélectionnées même si nous avons commencé à taper une formule.

Étape 3 : taper la plage des cellules d’origine

Tapez la plage de cellules que vous voulez transposer. Dans cet exemple, nous voulons transposer les cellules de a1 à B4. Par conséquent, la formule de cet exemple serait: = transpose (a1: B4) — mais n’appuyez pas sur entrée pour le moment. Arrêtez de taper, puis passez à l’étape suivante.

Excel se présente comme suit :

=TRANSPOSE(A1:B4)

Étape 4 : appuyer sur Ctrl+Maj+Entrée pour finir

Appuyez à présent sur Ctrl+Maj+Entrée. Pourquoi ? Parce que la fonction TRANSPOSE est utilisée uniquement dans les formules de tableaux et que cela permet de valider ce type de formule. En bref, une formule de tableau est une formule qui est appliquée à plusieurs cellules. Comme vous avez sélectionné plusieurs cellules à l’étape 1, la formule sera appliquée à plusieurs cellules. Voici le résultat obtenu une fois que vous avez appuyé sur Ctrl+Maj+Entrée :

Résultat de la formule avec les cellules A1:B4 transposées dans les cellules A6:D7

Conseils

Vous n’êtes pas obligé de taper la plage manuellement. Après avoir tapé =TRANSPOSE(, vous pouvez utiliser la souris pour sélectionner la plage. Cliquez et faites glisser du début de la plage jusqu’à la fin. Veillez à n’appuyer sur Ctrl+Maj+Entrée que lorsque vous avez terminé (et pas seulement sur Entrée).
Vous devez transposer du texte et le format de cellules également ? Essayez de copier coller et d’utiliser l’option TRANSPOSE . N’oubliez pas que cette opération crée des doublons. Par conséquent, si vos cellules d’origine sont modifiées, les copies ne seront pas mises à jour.

Entrées suivantes »