mosprepaPréparez votre certification
Microsoft Office Specialist

MS Excel 2013/2016

Classement et regroupement de cellules

Regrouper des lignes ou des colonnes est une des opérations de synthèse de données. Le regroupement permet d'afficher les lignes ou les colonnes de synthèse de données ou bien si vous le souhaitez, d'afficher les détails de chaque groupe. À la fin de ce cours, vous serez capable d'effectuer les manipulations suivantes :

  1. Transposition de colonnes et de lignes
  2. Insertion de graphiques Sparkline
  3. Création de plages nommées
  4. Formatage conditionnel des cellules
  5. Création des contours : grouper les lignes et les colonnes
  6. Réduction des groupes de données
  7. Insertion des sous totaux

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.

Regrouper les lignes et les colonnes

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.

Regrouper les lignes et les 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)

Regrouper les lignes et les colonnes

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

Regrouper les lignes et les 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. Regrouper les lignes et les colonnes
  4. Dans la boite de dialogue qui s'ouvre, renseigner la plage de cellules à représenter puis valider
  5. Regrouper les lignes et les colonnes

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

Regrouper les lignes et les colonnes

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.

Regrouper les lignes et les colonnes

On peut recopier un Sparkline vers les autres cellules en utilisant la poignée de recopie située en bas à droite de la cellule, pour représenter les séries des autres plages de cellules.

Regrouper les lignes et les colonnes

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

Regrouper les lignes et les colonnes

Travail à faire

Dans les cellules B17:O17, insérez des Sparkline type Courbe représentant les données correspondantes.

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.

L'adresse ou le nom de la cellule active s'affiche dans la Zone Nom du ruban.

Regrouper les lignes et les colonnes

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.

Regrouper les lignes et les colonnes

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.

Regrouper les lignes et les colonnes

Règles de nomenclature :

  • Pas d'espace
  • Ne doit pas commencer par un chiffre
  • Ne doit pas être une référence de cellule, exemples : A1, x2
  • Ne pas utiliser de caractères spéciaux, excepté le underscore (_)
  • Éviter, de préférence les caractères accentués

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

Regrouper les lignes et les colonnes

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.)

Regrouper les lignes et les colonnes

Travail à faire.

Nommez les plages de cellules comme suit :

  • B6:O6, Fevrier
  • B7:O7, Mars
  • - - - - -
  • B16;O16, Decembre

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

Regrouper les lignes et les colonnes

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.

Regrouper les lignes et les colonnes

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.

Regrouper les lignes et les colonnes

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.

Regrouper les lignes et les colonnes

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

Regrouper les lignes et les colonnes

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

Travail à faire

Sur la feuille EvolutionVentes2019 du classeur téléchargé, effectuez les tâches suivantes :

  1. Créez le nom CA qui fait référence à la plage P5:P10
  2. Renommez cette plage en ChiffreDAffaire
  3. Modifiez la zone référencée par ChiffreDAffaire pour la plage P5:P16

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.

Regrouper les lignes et les colonnes

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.

Regrouper les lignes et les colonnes

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.

Regrouper les lignes et les colonnes

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.

Regrouper les lignes et les colonnes

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.

Regrouper les lignes et les colonnes

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.

Regrouper les lignes et les colonnes

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.

Regrouper les lignes et les colonnes

Travail à faire

Sur la feuille EvolutionVentes2019 effectuez les tâches suivantes :

  1. Effacez la mise en forme conditionnelle appliquée aux cellules B5:O16.
  2. Dans la colonne CA, insérez les sommes des mois correspondants (On n'a pas encore fait ça mais vous devriez être capables)
  3. Dans cette colonne (CA), appliquez la mise en forme conditionnelle : Remplissage rouge clair avec texte rouge foncé pour les valeurs inférieures strictement à la moyenne

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).

Regrouper les lignes et les colonnes

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.

Regrouper les lignes et les colonnes

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

Regrouper les lignes et les colonnes

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.

Regrouper les lignes et les colonnes

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.

Regrouper les lignes et les colonnes

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.).

Regrouper les lignes et les colonnes

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.

Regrouper les lignes et les colonnes

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

Regrouper les lignes et les colonnes

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.

Regrouper les lignes et les colonnes

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.

Important :

Avant d'utiliser la fonction Sous-total, vous devez impérativement classer le tableau par ordre croissant (ou décroissant) selon la colonne pour laquelle vous voulez calculer les sous-totaux.

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

Regrouper les lignes et les colonnes

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

Regrouper les lignes et les colonnes

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

Regrouper les lignes et les colonnes

Résultat de l'opération :

Regrouper les lignes et les colonnes

À remarquer :

  • Les éléments du tableau sont organisés dans un plan de trois niveaux
  • Des sous-totaux indiquant le nombre d'articles vendus pour chaque désignation pour le mois Janvier
  • En bas du tableau le total général de tous les articles vendus au cours du mois Janvier
  • Seulement une partie du tableau est affichée dans la capture

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.

Regrouper les lignes et les colonnes

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.

Regrouper les lignes et les colonnes

Travail à faire :

Sur la feuille Ventes2019, insérez les Sous-totaux des PrixTotal par Désignation pour afficher le chiffre d'affaire de chaque article.

Téléchargement

Télécharger ce document au format PDF

MOS Prepa