Préparez votre certification
Microsoft Office Specialist

Gestion des modifications apportées au classeur

Recevez ce cours au format PDF en envoyant un message ici

Pour mettre en œuvre la manipulation de partage et du suivi des modifications, nous allons distribuer aux clients la fiche client pour la remplir puis collecter leurs informations. Je vous invite à télécharger la fiche de travail en cliquant ici .

Préparer le classeur pour le partage

Les moyens de partage d'un classeur Excel sont nombreux et de plus en plus sophistiqués, à commencer par les supports physiques tels que les clés USB et à finir par les serveurs réseaux locaux et les serveurs en ligne (Cloud) dédiés au partage comme SharePoint, One Drive, Google Drive, Dropbox et la liste est longue.

Avant de partager un classeur, quelques questions doivent se poser :

• Mon classeur ne contient-il pas des informations cachées que je ne voudrais pas partager ?
• Y a-t-il des cellules à protéger contre la modification ?
• Dans mon classeur, est ce qu'il n'y a pas des informations à masquer ?

En répondant à ces questions vous prenez des précautions pour protéger l'intégrité du classeur lors de sa relecture.

Supprimer les métadonnées

Les métadonnées sont des informations qui décrivent un fichier (un classeur), Titre, auteur, taille, mots clés, informations concernant votre entreprise, et d'autres données beaucoup plus sensibles. Ces métadonnées sont à ne pas sous-estimer et il est judicieux de les supprimer avant de partager un classeur (on n'est jamais trop prudent).

Pour supprimer les métadonnées il faut lancer l'inspection du document en allant dans le menu Fichier (Backstage) et dans la page Informations, ouvrir le bouton Vérifier l'absence de problèmes puis cliquer sur la ligne Inspecter le document. Dans la boite de dialogue Inspecteur de document, cliquer sur le bouton Inspecter pour lancer l'inspection.

Une fois l'inspecteur de document lancé, il va marquer toutes les lignes contenant des métadonnées avec un point d'exclamation rouge avec un bouton Supprimer en face. Pour supprimer ces informations il suffit de cliquer sur ce bouton Supprimer. Faites défiler la liste pour voir d'autres lignes à supprimer. Il est possible de relancer l'inspection avec le bouton Réinspecter pour s'assurer qu'il n'y a plus rien à supprimer.

Commenter le classeur

Pour que tout soit clair entre vous comme émetteur du classeur et les collaborateurs, n'hésitez pas à mettre des commentaires là où vous jugez que des éclaircissements sont nécessaires.

Ajouter un commentaire

Pour ajouter un commentaire à une cellule on suit les étapes suivantes :

1. Sélectionner la cellule à commenter
2. Dans le groupe Commentaires de l'onglet RÉVISION cliquer sur le bouton Nouveau commentaire
3. Dans la zone de texte jaune qui apparait taper le texte du commentaire

Copier un commentaire

Si un même commentaire concerne plusieurs cellules on peut le copier puis le coller dans les autres cellules (en effectuant un collage spécial).

1. Activer la cellule qui contient déjà le commentaire puis Copier
2. Sélectionner la(es) cellule(s) cible(s) puis dans le groupe Presse-papiers de l'onglet ACCUEIL, ouvrir le bouton Coller et choisir la ligne Collage spécial
3. Dans la boite de dialogue qui s'ouvre, choisir l'option Commentaires puis cliquer sur OK

Les cellules portant des commentaires sont signalées par des marques rouges dans leurs coins supérieurs droits.

Pour supprimer un commentaire il suffit de sélectionner la cellule à laquelle il est attaché puis de cliquer sur le bouton Supprimer dans le groupe Commentaires de l'onglet RÉVISION.

Protéger les feuilles

Généralement, dans une feuille Excel partagée, seules quelques cellules sont affectées pour subir des modifications par les relecteurs, les autres, contenant des titres, des étiquettes, des en-têtes, des formules de calcul sont à ne pas toucher.

Dans notre exemple, les cellules coloriées en vert clair B2:E6 sont réservées à la modification, et tout le reste de la feuille doit être protégé.

Par défaut, lorsqu'on protège une feuille, la totalité des cellules de la feuille sont verrouillées, ce qui empêche les collaborateurs de porter des modifications au classeur une fois partagé. Pour cette raison, la protection d'une feuille doit se dérouler en deux temps :

1. Déverrouiller les cellules à réviser
2. Protéger la feuille

Dans un premier temps, on va d'abord exclure les cellules B2:E6 de la protection. Pour cela, après les avoir sélectionnées, on va dans Format de cellule

Dans l'onglet Protection de la boite de dialogue Format de cellule qui s'ouvre, on décoche la case Verrouillée puis on valide. De cette façon, les cellules sélectionnées seront exclues de la protection de la feuille.

La deuxième étape de l'opération est de protéger la feuille. Pour ce faire, on doit exécuter les tâches suivantes :

1. Dans l'onglet RÉVISION, groupe Modifications, on clique sur le bouton Protéger la feuille
2. Dans la boite de dialogue Protéger la feuille, on coche la case Protéger la feuille et le contenu des cellules verrouillées
3. Saisir un mot de passe (Vous aurez à le saisir deux fois pour le confirmer)
4. Dans la section Autoriser tous les utilisateurs de cette feuille à :, ne laisser cochée que la case Sélectionner les cellules déverrouillées
5. On valide en cliquant sur OK

En validant cette opération, on protège la feuille de toute saisie hors de la zone autorisée et quiconque n'ayant pas le mot de passe ne pourra pas désactiver cette protection.

Cette manière de faire la protection est bonne dans le cas où nous voudrions que tous les relecteurs aient la possibilité de modifier la zone autorisée, ce qui n'est pas le cas présent. Ce que nous cherchons à faire ici c'est que chacun des correspondants (Behja Livesoft, Adel learning, ServiseNord, Ali Hard et Grabel online) ait accès à sa ligne et uniquement à sa ligne pour la modifier.

Dans ce but on va procéder autrement, avant de faire on doit d'abord annuler cette protection en cliquant sur le bouton Ôter la protection de la feuille du groupe Modifications de l'onglet RÉVISION.

1. Activer l'onglet RÉVISION
2. Dans le groupe Modifications, cliquer sur le bouton Ôter la protection de la feuille
3. Dans la boite de dialogue qui apparait, saisir le mot de passe qui a été introduit lors de l'activation de la protection
4. Valider en cliquant sur OK

Permettre la modification des plages

Cette fonctionnalité permet d'attribuer à chaque relecteur une plage spécifique de cellules à modifier avec la possibilité de définir un mot de passe de protection pour empêcher les autres collaborateurs de modifier les zones qui ne leur sont pas assignées. Elle est accessible via le groupe Modifications de l'onglet RÉVISION.

1. Dans le groupe Modifications de l'onglet RÉVISION, cliquer sur le bouton Permettre la modification des plages
2. Dans la boite de dialogue qui s'ouvre, on clique sur le bouton Nouvelle
3. Une seconde boite de dialogue apparait. Dans le champ Titre, saisir un nom pour la plage de cellules
4. Dans le champ Fait référence aux cellules, définir la plage correspondante
5. Dans le champ Mot de passe de la plage, saisir un mot de passe. Vous aurez à ressaisir ce même mot de passe pour le confirmer. (Ce mot de passe sera envoyé au relecteur à qui sera octroyée cette plage)
6. Valider avec OK

Refaire les étapes 2 à 6 pour chacun des collaborateurs.

Après avoir assigné chaque plage à son relecteur, cliquer sur le bouton Appliquer puis sur OK

L'animation suivante montre le déroulement de l'opération pour chacun des cinq relecteurs.

Protéger la structure du classeur

Une autre précaution à prendre avant de partager un classeur pour la révision est de protéger la structure du classeur par un mot de passe. Cette précaution a pour but d'empêcher les autres utilisateurs de supprimer des feuilles, de les renommer, de les déplacer, d'en ajouter ou encore d'afficher des feuilles masquées.

Pour protéger la structure d'un classeur ouvert, on clique dans le groupe Modifications, sur le bouton Protéger le classeur. Dans la boite de dialogue qui apparait, on renseigne un mot de passe tout en laissant la case Structure cochée, puis on valide par OK. On retape le mot de passe une deuxième fois pour la confirmation et on valide encore.

Cette même boite de dialogue est obtenue en passant par le menu Fichier (backstage), page Informations, bouton Protéger le classeur puis Protéger la structure du classeur.

Le fait de combiner la protection d'une feuille et la protection de la structure du classeur a pour effet de griser toutes les commandes d'ajout, de suppression, d'affichage et de masquage des colonnes, des lignes et des feuilles.

Activer le suivi des modifications

Une dernière opération à effectuer avant le partage est d'activer le suivi des modifications. Cette fonctionnalité a pour but, comme son nom l'indique, de voir les modifications faites par les relecteurs ainsi que leurs auteurs et par la suite pouvoir les accepter ou les refuser.

Pour ce faire, sous l'onglet RÉVISION, dans le groupe Modifications, on ouvre le bouton Suivi des modifications et on clique sur la ligne Afficher les modifications

Suite au clic sur la ligne Afficher les modifications, la boite de dialogue correspondante apparait et vous invite à activer et paramétrer le suivi.

Cochez alors la case Suivre les modifications au fur et à mesure, puis activez et paramétrez la case Le : à Tous qui a pour effet de suivre les modifications faites par les relecteurs depuis le partage du classeur jusqu'à l'arrêt du partage, et faites de même pour la case Par : pour suivre les modifications effectuées par tous les relecteurs. Pour terminer, validez en cliquant sur OK, après quoi une dernière boite de dialogue s'affiche pour vous indiquer que le classeur sera enregistré avec le paramétrage du suivi. Vous n'avez qu'à cliquer sur le bouton OK pour valider l'enregistrement.

À partir de ce moment, votre classeur est prêt à la distribution, comme c'est signalé entre crochets dans la barre de titre, et il ne vous reste plus que l'envoyer à vos collaborateurs.

Configurer le suivi des modifications

Encore une dernière opération que vous pouvez faire avant la distribution du classeur, c'est de paramétrer le suivi et ce en allant dans les options avancées du partage. Toujours sous l'onglet RÉVISION, groupe Modifications, cliquer sur le bouton Partager le classeur puis dans l'onglet Avancé de la boite de dialogue Options de partage du fichier vous pouvez les options selon ce que vous souhaitez :

• Suivi des modifications : choisissez la période au cours de laquelle les modifications seront conservées, cette période est de 30 jours par défaut
• Mise à jour des modifications : dans cette section, définissez le mode de mise à jour des modifications, soit automatiquement au bout d'un certain nombre de minutes, ou bien lorsque le relecteur enregistre les modifications faites
• En cas de modifications contradictoires : ici vous pouvez gérer les conflits des modifications, c'est à dire lorsque des relecteurs proposent des modifications différentes pour une même information, que faire ? Deux cas de figure sont possibles soit Excel vous demande laquelle des propositions garder (c'est vous qui décidez), soit vous ordonnez à Excel de garder la dernière proposition enregistrée.

Distribuer le classeur partagé aux collaborateurs

Une fois toutes les étapes préparatoires effectuées, vous pouvez distribuer une copie à chacun des relecteurs et ce en utilisant l'une des méthodes suivantes en fonction de vos possibilités :

1. Effectuer la distribution via un support physique, sur une clé USB par exemple, cette méthode "traditionnelle" est de moins en moins utilisée vue ses limites.
2. Envoyer à chacun des collaborateurs une copie par Email, dans ce cas, il faut attendre les relecteurs qu'ils ouvrent leurs courriers et relire le classeur puis le renvoyer de la même manière.
3. Mettre le classeur à réviser dans un dossier partagé, sur le réseau local ou bien sur le Cloud, dossier auquel tous les collaborateurs ont un accès direct. C'est la meilleure façon de faire.

Modifier un classeur partagé

Une fois le classeur partagé ouvert par un relecteur, celui-ci peut le modifier conformément aux droits qui lui ont été octroyés par le propriétaire du fichier.

Par exemple, la cellule B2 est modifiée et par conséquent, elle porte une encoche bleue dans son coin supérieur gauche témoignant ceci. La cellule B4 porte elle aussi la même marque, ce qui signifie que son contenu a été modifié, et en plus dans son coin supérieur droit on remarque une encoche rouge indiquant qu'un commentaire lui a été assigné.

Pour ajouter un commentaire à une cellule il suffit de cliquer sur la cellule concernée puis cliquer dans le groupe Commentaires de l'onglet RÉVISION, sur le bouton Nouveau commentaire et dans l'infobulle rattachée à la cellule, taper le texte du commentaire.

Après avoir apporté leurs modifications, les relecteurs doivent enregistrer le classeur et éventuellement le renvoyer au propriétaire.

Récupérer les classeurs revus

À la réception des classeurs revus par les relecteurs, les cellules qui ont subi des modifications sont marquées d'une encoche bleue dans leur coin supérieur gauche. Par exemple la cellule B2 a été modifiée. En pointant cette cellule une infobulle apparait et affiche des informations détaillées à propos de la modification : L'auteur est Bahja LiveSoft, la date et l'heure, le contenu avant la modification (la cellule était vide) et le nouveau contenu.

ServiceNord a effectué lui aussi une modification dans la cellule B4 qui était vide auparavant et en plus il a laissé un commentaire, comme le montre l'infobulle.

Remarquez que Excel utilise des couleurs différentes pour indiquer les changements faits par chaque relecteur.

Affichage de toutes les modifications

Au lieu de voir les modifications une à une dans des infobulles, vous pouvez les lister toutes en même temps sous forme de tableau dans une autre feuille. Pour ce faire, suivez les étapes ci-après :

1. Ouvrir le bouton Suivi des modifications
2. Cliquer sur la ligne Afficher les modifications
3. Dans la boite de dialogue qui s'ouvre, cocher la case Lister les modifications dans une autre feuille
4. Valider en cliquant sur OK

Cette opération a pour effet de créer une nouvelle feuille appelée Historique avec un tableau listant les détails de toutes les modifications qui ont été faites par les différents collaborateurs.

Gestion des commentaires

Dans le groupe Commentaires de l'onglet RÉVISION, vous trouvez différents outils pour manipuler les commentaires des relecteurs : Supprimer, se déplacer vers le Suivant ou bien vers le Précédent, Afficher ou masquer un commentaire ou bien Afficher tous les commentaires.

Résolution des problèmes de suivi

Il arrive parfois que deux ou plusieurs relecteurs proposent des corrections différentes (contradictoires) pour une même information alors laquelle prendre ? Ce cas de figure vous l'auriez configuré lors du paramétrage du partage dans la boite de dialogue Options de partage du fichier sous l'onglet Avancé.

Dans la section En cas de modifications contradictoires, vous pouvez paramétrer Excel, à chaque fois qu'il y a des propositions contradictoires, de vous demander une confirmation, ou bien vous lui ordonner de garder sans confirmation, la dernière proposition qui a été enregistrée.

Conservation de toutes les modifications

Après avoir reçu le classeur révisé par les relecteurs, vous pouvez utiliser la boite de dialogue Accepter ou refuser les modifications via le bouton Suivi des modifications, pour voir leurs suggestions une à une puis prendre la décision pour chacune de l'accepter ou bien de la refuser en utilisant le bouton adéquat, sinon, cliquez sur le bouton Accepter tout pour conserver toutes les modifications, ou bien sur le bouton Refuser tout pour tout rejeter.

Fusionner deux classeurs

Pour faire un seul fichier à partir de tous les classeurs qui ont été revus par les collaborateurs, il faudra les fusionner dans le classeur origine que vous avez gardé chez vous. La fusion se fait à l'aide du bouton Comparaison et fusion des classeurs

Ajouter le bouton de fusion à la barre d'outils accès rapide

Le bouton de comparaison et de fusion n'est pas disponible sur le ruban, il faut donc l'ajouter à la barre d'accès rapide. Pour ce faire suivez les étapes ci-dessous :

1. Clic droit sur la barre d'outils accès rapide, puis cliquer sur la ligne Personnaliser la barre d'outils accès rapide
2. Dans la boite de dialogue Options Excel, ouvrez la liste Choisir les commandes dans les catégories suivantes : choisissez Toutes les commandes
3. Dans la liste, chercher la commande Comparaison et fusion de classeurs et cliquer sur le bouton Ajouter pour la faire passer dans la zone de droite
4. Valider en cliquant sur OK

Fusionner les classeurs

Pour fusionner les classeurs modifiés avec le classeur origine, ouvrez d'abord ce dernier puis cliquez sur le bouton Comparaison et fusion des classeurs qui se situe désormais sur la barre d'outils accès rapide, pour aller chercher le classeur modifié par un collaborateur puis valider. Cette opération va effectuer une sorte d'importation des toutes les modifications apportées par le relecteur. Répétez cette opération avec tous les autres classeurs que vous avez reçus après leurs modifications.

Identification des erreurs

Les erreurs de calcul dans Excel, on peut les classer dans plusieurs catégories : les erreurs de syntaxe, les erreurs de non cohérence et les erreurs de logique ou de raisonnement.

Les erreurs de syntaxe

Ce type d'erreurs est le plus facile à identifier car il est signalé par Excel par des codes indiquant sa nature, ainsi ces erreurs sont relativement faciles à corriger. Le tableau suivant montre les erreurs les plus fréquentes de cette catégorie.

Les erreurs de non cohérence

Excel attire l'attention de l'utilisateur vers une cellule qui contient une formule qui ne ressemble pas aux formules contenues dans les cellules voisines. Cette incohérence est signalée par un losange jaune avec un point d'exclamation. En ouvrant la petite flèche à droite du losange, on obtient une liste dont la première ligne indique la nature de l'incohérence et les lignes suivantes listent les différentes opérations que l'on peut faire.

Dans le cas ci-dessus, la cellule concernée par l'erreur contient une formule qui n'est pas identique à celles des cellules voisines comme le montre la colonne de droite. L'utilisateur doit donc voir cette anomalie et décider de la garder comme elle est, en cliquant sur la ligne Ignorer l'erreur ou bien de la corriger en cliquant sur la ligne Copier la formule de dessus ou bien la ligne Modifier dans la barre de formule

Un autre exemple de données incohérentes, est montré dans la capture qui suit ; la cellule signalée contient la valeur 10 mais qui est considérée comme du texte parce qu'elle est précédée d'une apostrophe, chose qui fausse le résultat de l'expression =SOMME(H3:H7) contenue dans la cellule verte. Cette expression est censée calculer la somme des valeurs des cinq cellules au-dessus et le résultat attendu est 50. La source de l'erreur est le contenu de la première cellule, tout en haut, qui n'est pas pris en compte par la formule SOMME car il est considéré comme du texte.

Les erreurs de logique

Ces erreurs se manifestent par des résultats erronés. Elles sont plus difficiles à détecter car elles ne sont pas signalées par Excel et l'utilisateur doit être vigilant envers elles. Pour trouver la cause, dans le cas d'un affichage douteux, sachez qu'il y a plusieurs manières d'auditer une formule pour savoir toutes les données qui ont contribué à la formule ainsi que les résultats intermédiaires qui ont donné l'affichage final.

Le code couleur utilisé par Excel

Une première méthode pour auditer une formule est de double-cliquer sur la cellule contenant la formule et grâce aux codes couleurs utilisés par Excel, on peut voir que chaque membre de la formule est coloré de la même couleur que la cellule à laquelle il est attaché. Da cette façon, vous avez une idée sur l'origine de l'erreur.

Afficher les antécédents et les dépendants

Les outils de l'audite sont disponibles dans le groupe Vérification des formules de l'onglet FORMULES. Parmi ces outils figurent Repérer les antécédents qui indique avec des flèches les données source de la formule, et le bouton Repérer les dépendants qui indique avec des flèches les données issues de la formule.

Évaluer la formule

Dans le cas d'une formule un peu complexe, on utilise l'outil Évaluer la formule qui permet de suivre l'exécution de l'expression pas à pas pour évaluer toutes ses étapes et ainsi localiser la source qui a donné le résultat douteux.

Exemple dans la cellule H3 on introduit l'expression suivante =SI(ET(F3>10;F4<15);F5;F6) pour afficher la valeur de la cellule F5 si la valeur de F3>10 et la valeur de F4<15, sinon on affiche la valeur de F6. Cette expression étant de complexité supérieure à celle de la première formule, on peut demander à Excel d'aller pas à pas et afficher les résultats intermédiaires jusqu'à la fin de l'opération.

Utiliser la fenêtre Espion

La fenêtre Espion est utilisée pour surveiller la valeur d'une ou plusieurs cellules en fonction du changement du contenu des cellules antécédentes. Cette fenêtre est surtout utilisée dans le cas où les cellules à espionner ne sont pas visibles sur l'écran (dans une autre feuille par exemple). En ouvrant cette fenêtre Espion, on dispose de deux boutons : un pour Ajouter un espion et l'autre pour Supprimer un espion.

Dans notre cas, on veut garder sous l'œil le changement des valeurs des cellules de la colonne N par l'intermédiaire de l'Espion, lorsqu'on modifie les valeurs des cellules de la colonne F.

La fenêtre de l'Espion montre les changements des cellules espionnées sans faire des allers-retours entre les cellules sources et les cellules cibles.

Recevez ce cours au format PDF en envoyant un message ici