Préparez votre certification
Microsoft Office Specialist

Classement et regroupement de cellules

Recevez ce cours au format PDF en envoyant un message ici

Le fichier de travail est à télécharger ici. Il s'agit d'un classeur contenant plusieurs feuilles de calcul sur lesquelles on va travailler, notamment les feuilles : EvolutionVentes2019, ExtraitVentes2019 et Ventes2019.

Transposer les colonnes et les lignes

Transposer les colonnes et les lignes d'un tableau c'est les intervertir, autrement dit, mettre les lignes à la place des colonnes et les colonnes à la place des lignes.

Dans ce tableau de la feuille EvolutionVentes2019, les mois sont en horizontal formant ainsi les têtes des colonnes et les noms des articles forment les têtes des lignes. Ce que nous voulons faire c'est d'inverser les lignes et les colonnes : Transposer le tableau de façon à mettre les mois en tête de lignes et les articles en tête des colonnes.

La transposition des lignes et des colonnes se fait en passant par un collage spécial du tableau, l'opération se fait en plusieurs étapes :

1. Sélectionner l'intégralité du tableau
2. le copier en effectuant un Ctrl+C ou bien en passant par le groupe Presse-papiers/Copier
3. Cliquer à l'endroit où l'on effectuer le collage (hors du tableau)
4. Dans le groupe Presse-papiers, ouvrir le bouton Coller et choisir Transposer indiqué en rouge ou bien suivre les étapes indiquées en bleu dans l'illustration ci-après.
5. Supprimer le premier tableau (étape à faire si nécessaire)

Résultat de l'opération : les mois en tête des lignes et les articles vendus en tête des colonnes.

Insertion de graphiques Sparkline

Dans un cours précédent, on avait dit que dans une cellule on pouvait mettre des textes, des nombres (sous différents formats) et des formules de calcul. Ce qui n'a pas été dit, c'est qu'on peut aussi y insérer des graphiques dits Sparkline. Ce sont des mini-graphiques qui montrent les tendances d'une série de valeurs et qui se comportent comme une image d'arrière-plan dans une cellule.

Insérer un Sparkline

L'insertion d'un Sparkline se fait en quelques petites étapes :

1. Cliquer sur la cellule d'insertion
2. Sous l'onglet Insertion, groupe Graphiques Sparkline, cliquer sur le graphique voulu (Courbe, Histogramme, Conclusions et pertes)

3. Dans la boite de dialogue qui s'ouvre, renseigner la plage de cellules à représenter puis valider

Le résultat est visualisé dans la figure ci-dessous, dans la cellule B17, un Histogramme qui affiche la tendance des données contenues dans la plage B5:B16

Personnaliser un Sparkline

Une fois le Sparkline inséré, un onglet supplémentaire Outils de Sparkline apparait dans le ruban avec une panoplie d'outils pour modifier l'apparence du graphique, notamment le type, le style, les couleurs, etc.

Supprimer un Sparkline

La touche Suppr du clavier n'a aucun effet sur le Sparkline. La suppression de ce type de graphiques se fait en passant par l'onglet Création, groupe Groupe, ouvrir le bouton Supprimer et choisir l'option Supprimer les graphiques Sparkline sélectionnés

Création de plages nommées

Toute cellule dans une feuille Excel est référencée par une adresse formée de la(es) Lettre(s) de sa colonne suivie(s) du Numéro de sa ligne, exemple : l'adresse C2 identifie la cellule se trouvant à l'intersection de la colonne C et de la ligne 2. Toutefois, pour des raisons de clarté, on peut donner des noms significatifs à une cellule voire à une plage de cellules, qui expriment clairement la nature du contenu de ces cellules.

Définir un Nom

Pour nommer une cellule ou bien une plage de cellules, il faut d'abord les sélectionner ; après quoi on peut utiliser l'une des méthodes suivantes :

Méthode 1

Taper directement le nom dans la Zone Nom puis appuyer sur la touche Entrée du clavier pour valider. Exemple dans la capture ci-dessous, nous avons sélectionné la plage de cellules B5:O5 puis nous avons tapé le nom Janvier dans la Zone Nom, ainsi cette plage est identifiée par ce nom qui pourra par la suite, être utilisé dans les fonctions et les formules de calcul en place et lieu de B5:O5.

Méthode 2

Pour la deuxième méthode, il faut passer par le groupe Noms Définis de l'onglet Formules et cliquer sur le bouton Définir un nom. Dans la boite de dialogue qui apparait, renseigner le champ Nom puis valider.

Utiliser les entêtes de lignes ou de colonnes comme noms

Un moyen rapide pour nommer plusieurs plages en même temps est l'utilisation du bouton Depuis sélection du groupe Noms définis.

1. Sélectionner toutes les plages avec leurs entêtes
2. Activer l'onglet Formules
3. Dans le groupe Noms définis, cliquer sur le bouton Depuis sélection
4. Dans la boite de dialogue qui s'ouvre, cocher la case adéquate
5. Valider

La Zone Nom répertorie toutes les cellules et plages de cellules nommées et permet un accès direct à ces plages.

Le résultat de cette opération est que chaque ligne est nommée du nom de son entête (Janvier, Fevrier, etc.)

Gérer les Noms

Créer un nouveau nom, Renommer une plage de cellules, Modifier la plage référencée par un nom, Supprimer un nom, toutes ces opérations sont possibles en ouvrant le bouton Gestionnaires de noms du groupe Noms définis de l'onglet Formules

Créer nouveau Nom

Pour créer un nouveau Nom, cliquer sur le bouton Nouveau puis renseigner les champs de la boite de dialogue Nouveau Nom qui apparait.

Dans la capture ci-dessus nous créons une zone nommée Chiffre_Affaire qui s'étend de la cellule P5 jusqu'à la cellule P16 de la feuille EvolutionVentes2019. Cette plage nommée sera reconnue dans tout le classeur. Il ne reste plus que cliquer sur le bouton OK pour valider la création.

Renommer une plage de cellules

Pour modifier le nom d'une cellule ou plage de cellules, on sélectionne son nom dans la fenêtre Gestionnaire de noms puis on clique sur le bouton Modifier.... Dans la boite de dialogue Modifier le nom qui s'ouvre, on tape le nouveau nom dans le champ Nom puis on valide.

Modifier une zone de référence

On peut aussi modifier la plage de cellules à laquelle le nom fait référence et ce en modifiant le contenu du champ Fait référence à. Vous pouvez le modifier à la main si vous savez quoi faire sinon, effacez le contenu et sélectionnez la nouvelle plage.

Supprimer un nom

Toujours dans la boite de dialogue Gestionnaire de noms, la suppression d'un nom se fait en trois clics :

1. Sélectionner le nom à supprimer
2. Cliquer sur le bouton Supprimer...
3. Valider en cliquant sur OK

En cliquant sur OK, on supprime uniquement le nom ChiffreDAffaire, pas la plage et pas les données.

Formatage conditionnel des cellules

La mise en forme conditionnelle permet de mettre en évidence les cellules dont le contenu répond à un certain critère pour faciliter la lecture et l'analyse des données par exemple. De nombreuses conditions sont déjà intégrées dans Excel et en plus vous avez la possibilité de créer vos propres conditions. En ouvrant le bouton Mise en forme conditionnelle du groupe Style sous l'onglet Accueil, vous disposez de l'ensemble des mises en forme proposées par Excel.

Règles de mise en surbrillance

Cette première catégorie de règles permet de mettre en évidence uniquement les cellules dont le contenu répond à un critère, parmi les cellules préalablement sélectionnées. En ouvrant la liste Règles de mise en surbrillance des cellules, choisissez la règle qui vous convient, puis définissez la valeur critère et la mise en forme voulue.

Dans la capture ci-dessus, sont mises en évidence uniquement les cellules dont les valeurs sont strictement supérieures à 330 000 et pour la mise en forme, on a choisi un Remplissage vert avec texte vert foncé.

Sachez qu'on peut appliquer plusieurs règles de mise en forme en même temps à une cellule (plage de cellules).

Règles des valeurs de plage

Cette option de mise en forme sert à mettre en relief les meilleures valeurs mais aussi les valeurs les plus basses.

Après validation du paramétrage ci-dessus on va mettre en évidence les 10 valeurs les moins élevées du tableau en appliquant un Remplissage rouge clair avec texte rouge foncé

Barres de données

Ce type de mise en forme conditionnelle visualise les valeurs contenues dans les cellules à l'aide des Barres horizontales dont la longueur est fonction de la valeur représentée.

Une barre de données orange, remplissage uni est utilisée pour représenter les valeurs. On aurait pu utiliser une autre couleur avec un remplissage dégradé, c'est une question de goût et d'harmonie selon votre charte de couleur.

Nuances de couleurs

Cette catégorie de mise en forme conditionnelle utilise des nuances de trois couleurs pour la mise en relief des données, une couleur pour les plus élevées et une autre pour les plus basses avec des nuances pour les valeurs intermédiaires.

Dans notre cas, la couleur bleue est choisie pour les valeurs les plus grandes puis le bleu se dégrade vers le blanc pour les valeurs moyennes et le blanc à son tour se dégrade vers le rouge pour les valeurs les plus basses.

Jeux d'icônes

Les icônes sont des petits dessins que l'on peut insérer dans une cellule pour exprimer la valeur de son contenu. On peut utiliser des formes et des couleurs différentes pour indiquer si une valeur est grande, moyenne ou bien petite par rapport au reste des valeurs sélectionnées.

Dans notre exemple, les plus grandes valeurs sont indiquées par un rond vert, les valeurs moyennes par un rond orange et les plus faibles par un rond rouge.

Effacer une règle de mise en forme

On peut à tout moment effacer les règles de mise en forme conditionnelle appliquées aux cellules soit pour toute la feuille ou bien uniquement pour les cellules sélectionnées.

Création des contours

Le mode Plan est l'un des outils de synthèse de données, il permet de grouper les lignes (ou bien les colonnes) pour pouvoir afficher une vue globale ne montrant que les données synthétiques (Somme, Total, Moyenne, etc.), ou bien une vue détaillée qui montre toutes les lignes du tableau.

Dans la feuille VentesJanvier on trouve toutes les ventes qui ont été effectuées pendant le mois Janvier. On voudrait créer un plan qui regroupe les ventes de chaque semaine (du lundi au dimanche).

Grouper les lignes et les colonnes

Avant de grouper les lignes de chaque semaine, on doit d'abord insérer une ligne séparatrice de manière à délimiter chaque semaine.

Après l'installation de ces lignes séparatrices, on doit exécuter les deux étapes suivantes pour chacune des semaines :

1. Sélectionner toutes les lignes d'une même semaine
2. Dans le groupe Plan de l'onglet Données, on ouvre le bouton Grouper puis on clique sur Grouper

Le résultat de cette opération est l'apparition d'une ligne verticale avec un symbole (-) sur la marge gauche de la feuille indiquant que les lignes sont bien groupées. On fait de même pour les autres semaines pour grouper leurs lignes.

En groupant les différentes lignes, nous créons un Plan à plusieurs niveaux, dans le cas présent deux niveaux : 1 et 2, le niveau 1 pour la vue globale et le niveau 2 pour la vue détaillée.

Réduction des groupes de données

En cliquant sur le bouton 1, tous les groupes se rétrecissent pour n'afficher que les lignes récapitulatives, ces lignes qui sont pour l'instant vides comme vous pouvez le voir dans la capture ci-dessous, il serait judicieux d'y insérer une formule calculant une donnée de synthèse pour chaque semaine (Par exemple : Somme, Moyenne, etc.).

Deux autres moyens de réduire un groupe particulier est de cliquer sur le symbole (-) qui se trouve en bas de la ligne verticale, ou bien d'aller chercher la commande Masquer le détail dans le groupe Plan.

Le déploiement d'un groupe se fait en cliquant sur le petit plus (+) sur la marge gauche ou bien en passant par le groupe Plan, commande Afficher le détail

Pour dissocier un groupe, il faut sélectionner toutes les lignes (les colonnes) qui le composent puis choisir la commande Dissocier dans le groupe Plan.

Insertion des sous totaux

La fonction Sous-total est une fonction Excel qui crée automatiquement un plan pour organiser les données d'un tableau en groupes et comme son nom l'indique, insère des sous-totaux (Somme, Moyenne, Nombre, Maximum, Minimum, etc.) pour chaque groupe.

On se propose d'afficher le total des articles vendus (somme de Quantité) pour chaque Désignation dans le tableau de la feuille VentesJanvier.

La première opération à faire est donc de Trier le tableau en ordre croissant (ou décroissant) sur la colonne Désignation.

Après le tri sur la colonne désirée, on suit les étapes :

1. Ouvrir le groupe Plan si nécessaire
2. Cliquer sur le bouton Sous-total
3. Dans la boite de dialogue Sous-total, ouvrir la liste À chaque changement de : et choisir Désignation
4. Dans la liste Utiliser la fonction : choisir Somme
5. Dans la zone Ajouter un sous-total à : cocher Quantité
6. Valider avec OK

Résultat de l'opération :

Dans la capture ci-dessous, seul le groupe Souris HP filaire de voyage - USB (G1K28AA) est déployé ; tous les autres groupes sont réduits.

En cliquant sur le bouton 1, on réduit tous les niveaux du plan sauf le niveau supérieur qui affiche le Total général c'est à dire le nombre de tous les articles vendus au cours du mois Janvier.

Recevez ce cours au format PDF en envoyant un message ici