Consolider des tableaux

La consolidation permet de synthétiser des données à partir de données figurant sur des feuilles de calcul distinctes situées soit dans le même classeur ou soit dans des classeurs différents. 

Par exemple, si vous disposez d’un tableau des dépenses pour chaque commune de votre département, vous pouvez vous servir de la consolidation pour rassembler ces chiffres dans une feuille de calcul de dépenses départementale.

Vous disposez des ventes mensuelles des 3 agences de votre entreprise. La consolidation va vous permettre de créer une feuille mensuelle globale.

Il existe deux méthodes pour consolider les données :

• Consolider par position : A n’utiliser que si les données de plusieurs zones source sont organisées dans le même ordre et utilisent les mêmes étiquettes de lignes. La disposition des données doit être rigoureusement la même dans chacun de vos tableaux sources
• Consolider par catégorie : Lorsque les données de plusieurs zones source sont organisées différemment, mais possèdent les mêmes étiquettes de lignes et de colonnes 

Les manipulations sont quasiment identiques pour les deux méthodes.

1. Commencez par ouvrir tous les classeurs à cumuler.
2. Positionner le curseur dans la feuille récapitulative, c’est-à-dire là où vous souhaitez faire apparaître le cumul.
3. Dans la feuille de calcul récapitulative, cliquez sur la cellule à partir de laquelle vous souhaitez faire apparaître les données consolidées.
4. Cliquer sur l'onglet Données et choisissez Consolider dans le groupe "Outils de données". Une boîte de dialogue s'affiche.
5. Choisir le type de calcul à effectuer, par défaut la somme

puis pour chaque zone de feuille de calcul à consolider 

Consolidation par position

• Sélectionner les données à cumuler dans la feuille de calcul source. 
• Dans la boîte de dialogue Consolider, cliquez sur Ajouter
• L’adresse de la plage de cellules s’inscrit dans la zone « toutes les références »
• Recommencer jusqu'à que toutes vos zones à cumuler s'affichent dans la zone « toutes les références »

Une fois la sélection de l’ensemble des plages sources à cumuler effectuée, valider par « Entrée »

Consolidation par étiquette

Procéder comme la consolidation par position

Mais lors de la sélection de chaque plage de données source, il faudra impérativement sélectionner les étiquettes de titre des colonnes et/ou des lignes.

 Dans la boîte de dialogue de consolidation, activer les cases à cocher situées sous Étiquettes dans, qui indiquent l’emplacement des étiquettes dans les plages source Ligne du haut, Colonne de gauche ou les deux.

Toutes les cellules qui possèdent les mêmes étiquettes seront cumulées ensemble.

Une seule ligne ou colonne par étiquette distincte s’affichera dans le résultat cumulé.

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

L'actualisation

Cocher la case "Lier aux données source" si vous désirez établir un lien entre les tableaux source et le tableau consolidé.

Toute modification effectuée à l’intérieur d’un tableau source sera automatiquement reportée dans le tableau consolidé.

Utiliser des noms

Un nom défini représente une cellule ou une plage de cellules.

Attribuer un nom à une plage de cellules permet de se servir de ce nom, plutôt que des adresses de cellules, dans une formule de calcul afin de rendre celle ci plus compréhensible.
Ainsi, la formule =somme(B2:B45) est moins explicite que
=somme(Frais_deplacement_mars) 

Dans celle ci, la zone B2:B45 est représentée par le nom Frais_deplacement_mars

Ce nom peut aussi servir pour atteindre directement une zone particulière d'une feuille

Pour attribuer un nom à une cellule ou une plage de cellule

1) Sélectionner la cellule ou la plage de cellules à nommer.
2) Cliquer sur la zone "nom" située à l'extrémité gauche de la barre de formules
3) Taper le nom (255 caractères max, pas d'espace)
4) Ne pas oublier de valider en appuyant sur la touche "Entrée"

Par défaut, ce nom sera reconnu dans l’ensemble des feuilles de calcul du classeur

Autre solution : Utiliser la rubrique Nom située dans l’onglet formule

1)   Sélectionner la cellule ou la plage de cellules à nommer.

2)   Cliquer sur l’onglet Formules dans le ruban puis Définir un nom (Define Name)

3)   Dans Le dialogue qui s’ouvre, taper le nom que vous souhaitez attribuer à la cellule.

4)   Indiquer si le nom est valable pour tout le classeur ou juste pour la feuille active

5)  Valider par "Entrée

Règles de nommage

Un nom ne peut excéder 255 caractères, ni contenir d'espace. Seuls, les lettres, chiffres, points et le soulignement _ sont acceptés.
Un nom de cellule ne peut non plus être identique à une adresse de cellules.

De plus, le premier caractère d'un nom ne peut être qu'une lettre ou le caractère _ 

Définir des noms à partir d’une sélection de plages de cellules

Les tableaux comportent déjà des en-têtes de colonne explicites. Pourquoi ne pas s’en servir pour nommer les plages du tableau ?

1) Sélectionner la plage à laquelle attribuer un nom, y compris les étiquettes de colonnes et/ou de lignes.

2) Dans le ruban, sélectionner l'onglet Formules puis "Depuis la sélection" (Create from selection)

3) Dans la boîte de dialogue, indiquer si Excel doit utiliser les libellés de la  première ligne (Produit, production,...chiffre d'affaire TTC) et/ou de la première colonne (Rosiers, oliviers...Bonzai) pour nommer les zones de cellules.

4) Dans notre exemple, nous allons cocher uniquement ligne du haut car nommer les lignes ne va pas nous servir.

Désormais, chaque colonne de notre tableau porte un nom. Par exemple, vente se réfère aux cellules C5:C9. Les noms font uniquement référence aux cellules contenant des valeurs et excluent les étiquettes  existantes.

Cela va rendre les formules de calcul plus claires.

La formule =Production-Vente est plus rapide à comprendre que =B5-C5

Utiliser un nom dans un calcul

Pour utiliser un nom lors de la saisie d’une formule de calcul, il suffit de taper le nom ou de le sélectionner à partir d'une liste.

• Taper la première lettre du nom et sélectionner le nom dans la liste qui s'affiche.
• Ou, dans l'onglet Formules, cliquer sur Utiliser dans la formule (Use in formula) et sélectionner le nom que vous souhaitez utiliser.
• Ou lors de la saisie de la formule, appuyer sur la touche de fonction F3 qui permet d’appeler la liste des noms lors de la saisie d’une formule.

Si le nom est mal orthographié ou a été supprimé, le message d'erreur #NOM s'affiche.
Le gestionnaire de noms

 
Le « Gestionnaire de noms » (Name manager) situé dans le groupe « noms définis » de l’onglet « Formules » permet de modifier ou de supprimer un nom défini.

La protection des données

Excel vous permet de protéger vos documents afin d'empêcher que des données importantes soient accidentellement ou intentionnellement modifiées, déplacées ou supprimées d’une feuille de calcul ou d’un classeur. 

Excel offre deux sortes de protection. La protection des données d'une feuille ou la protection de la structure d'un classeur.

---

Protéger une feuille

Par défaut, lorsque vous protégez une feuille de calcul, toutes les cellules de cette feuille sont verrouillées et il est impossible de les modifier ou d'en changer la présentation.

Pour protéger la totalité des cellules d'une feuille, Cliquez sur l'onglet "Révision" et choisissez "Protéger la feuille" dans le groupe "Modifications"

Une boîte de dialogue s'affiche pour affiner la protection

Profitez en pour mettre un mot de passe afin d’empêcher toute tentative de déprotection de votre feuille.

Si vous ne souhaitez pas bloquer la totalité des cellules de la feuille mais permettre la modification de certaines cellules, commencez par déverrouiller les cellules concernées puis dans un second temps, activez la protection de la feuille

Pour déverrouiller les cellules ou les plages de cellules que d’autres utilisateurs pourront modifier

1. Sélectionnez chaque cellule ou plage que vous souhaitez déverrouiller
2. Cliquez sur l'onglet Accueil, choisissez "Format" dans le groupe Cellules
   
3. Sous l’onglet Protection, désactivez la case à cocher Verrouillé , puis cliquez sur OK.

---

Protéger le classeur

La protection du classeur permet de : 

•  Protéger les fenêtres d'un classeur pour qu'elles ne puissent pas être déplacées ou redimensionnées.

•  Verrouiller la structure d'un classeur ce qui empêche les utilisateurs d’ajouter,de déplacer, de supprimer des feuilles de calcul ou d’afficher des feuilles de calcul masquées. 

Pour protéger la structure et les fenêtres d'un classeur, Cliquez sur l'onglet "Révision" et choisissez "Protéger le classeur" dans le groupe "Modifications"

• Pour protéger la structure d’un classeur, cocher Structure.
• Pour que les fenêtres du classeur conservent la même taille et la même position à chaque ouverture du classeur, cocher fenêtres 

Le mot de passe est facultatif. Mais il évite que tous les utilisateurs puissent déprotéger le classeur et modifier les éléments protégés.

---

Attention : La protection de feuille ou la protection du classeur n'empêche pas un utilisateur d'accéder à votre classeur.

Pour interdire à un utilisateur d’entrer dans un classeur, il faut utiliser l’option d’enregistrement avec mot de passe.

Dans le menu Fichier, choisissez « Informations » puis   cliquez sur le bouton « Protéger le classeur » situé dans l'onglet Autorisations

Sélectionnez alors « Chiffrer avec mot de passe » et tapez deux fois votre mot de passe.