Partager et consolider les données d'un classeur Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Partage de classeur Excel et collaboration

L'objectif de cette formation est d'apprendre à partager un fichier Excel. Le but du partage est de permettre à plusieurs utilisateurs d'intervenir sur les feuilles du classeur en même temps. On parle de travail collaboratif. La fonction de partage d'Excel permet de consolider l'ensemble des modifications à l'issue. L'utilisateur final récupère le classeur ainsi mis à jour depuis plusieurs sources.

Le classeur

• Télécharger le classeur partage-collaboratif-classeur-excel.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel,

Il s'agit d'une facturation clients automatisée et dynamique. Elle est automatisée dans le sens où il suffit de sélectionner une référence depuis la liste déroulante des codes articles pour que toute la facture se mette à jour. Seule la quantité achetée reste à indiquer. Ces références sont issues du catalogue de la deuxième feuille de ce classeur, la feuille articles. Elle est dynamique car si le catalogue venait à évoluer avec des insertions de nouveaux articles, la liste déroulante et les formules de la facturation les intègreraient automatiquement.

Objectif du travail collaboratif
Le but est donc de partager ce classeur avec plusieurs utilisateurs de manière à ce que le catalogue et ses stocks puissent être mis à jour en fonction des différents fournisseurs. La collaboration doit intervenir sur la feuille articles uniquement. En effet seul le catalogue est concerné. Donc la feuille facturation ne devra pas être proposée aux différents intervenants de façon à la protéger de toute modification malencontreuse. De même, sur la feuille articles, nous souhaitons autoriser l'insertion de nouvelles références et la modification de celles existantes. En revanche, la ligne Titre du tableau ne doit pas changer. Avant le partage, nous devons la protéger.

Protection des données avant partage
Par défaut, toutes les cellules d'une feuille sont verrouillées. Mais il s'agit d'un état qui prend effet seulement lorsque la feuille elle-même est protégée. Donc si la feuille est protégée et qu'aucune cellule n'a préalablement été déverrouillée, plus aucune saisie n'est possible. Nous souhaitons autoriser la saisie et la modification partout sauf sur la ligne de titre du tableau, soit la plage C1:E1. Pour ce faire, nous allons dans un premier temps déverrouiller toutes les cellules puis re-verrouiller uniquement les cellules de la plage C1:E1.

• Activer la feuille articles en cliquant sur son onglet,
• Sélectionner une cellule vide de la feuille, par exemple A1,
• Réaliser le raccourci clavier CTRL + A pour sélectionner toutes les cellules de la feuille,

Si vous aviez sélectionné préalablement l'une des cellules du tableau, le raccourci CTRL + A aurait permis de sélectionner l'ensemble des cellules du tableau, sans déborder. Cette astuce et bien d'autres combinaisons au clavier sont enseignées dans le support de formation sur les raccourcis clavier Excel.

• Cliquer avec le bouton droit de la souris n'importe où sur la sélection,
• Dans le menu contextuel, choisir Format de cellule,
• Dans la boîte de dialogue qui suit, activer l'onglet Protection,
• Décocher la case verrouillée et valider par Ok.

A ce stade, toutes les cellules étant déverrouillées, toutes les modifications sont permises, même si la feuille est protégée. Avant de protéger la feuille, nous devons donc verrouiller les cellules de titre du tableau.

• Sélectionner la plage de cellules C1:E1,
• Cliquer avec le bouton droit de la souris sur cette sélection,
• Dans le menu contextuel, choisir Format de cellule,
• Dans la boîte de dialogue, cocher la case Verrouillée et valider par Ok,
• Activer maintenant le ruban Révision,
• Dans le groupe Modifications, cliquer sur le bouton Protéger la feuille,

Dans la boîte de dialogue qui suit, vous pouvez saisir un mot de passe afin d'être le seul à pouvoir ôter la protection sur les cellules verrouillées. De nombreuses cases vous permettent de définir des autorisations sur des modifications de lignes, de colonnes ou encore de formatage.

• Cliquer sur Ok pour valider la protection de la feuille,

Désormais, si vous saisissez n'importe où dans le tableau, sauf la ligne de titre, vous y êtes autorisé. En revanche, si vous tentez de saisir sur l'une des cellules de la plage C1:E1, un message Excel intervient vous interdisant toute modification. Pour permettre de nouveau la modification totale de la feuille, il faut cliquer sur le bouton Ôter la protection de la feuille du ruban Révision. C'est pourquoi un mot de passe peut s'avérer utile afin d'augmenter la sécurité. Puisque les collaborateurs ont pour mission d'alimenter et de mettre à jour le catalogue, ils n'ont pas à intervenir sur la feuille facturation. Nous pourrions choisir de protéger toutes les cellules de cette feuille, mais il est encore plus simple de la masquer et de n'autoriser personne d'autre que vous de l'afficher sur demande.

• Cliquer avec le bouton droit de la souris sur l'onglet de la feuille facturation,
• Dans le menu contextuel, choisir Masquer,

La feuille disparaît. Bien sûr, un clic droit sur n'importe quel onglet de feuille, permet de récupérer la feuille en cliquant sur la commande Afficher du menu contextuel. Pour empêcher les collaborateurs d'accéder à cette feuille, il faut protéger le classeur désormais.

• Dans le groupe Modifications du ruban Révision, cliquer sur le bouton Protéger le classeur,

Une boîte de dialogue apparaît. La case Structure est cochée signifiant notamment que les feuilles masquées ne pourront plus être affichées. Comme précédemment, pour améliorer la sécurité, une zone de saisie permet d'associer un mot de passe à cette protection.

• Valider la boîte de dialogue en cliquant sur le bouton Ok.

Désormais la commande Afficher n'est plus disponible via le clic droit sur les onglets des feuilles du classeur. Donc la feuille facturation est protégée pour le mode collaboratif. Sur la feuille articles, les saisies restent autorisées partout sauf sur la ligne de titre. Jusque-là tout est parfait.

Partage collaboratif du classeur
Pour qu'un classeur Excel puisse être modifié en même temps par plusieurs utilisateurs et que toutes les données soient, à l'issue, synchronisées et consolidées, il ne suffit pas de l'enregistrer à un endroit du réseau accessible par tous. Car, sans paramétrage préalable en conséquence, le dernier qui enregistre le classeur, écrase les modifications des autres. Il ne peut y avoir dans ce cas de travail collaboratif en temps réel. Pour permettre la collaboration sur ce classeur :

• Dans le groupe Modifications du ruban Révision, cliquer sur le bouton Partager le classeur,
• Dans la boîte de dialogue qui suit, cocher la case Permettre une modification multi-utilisateur,

Si vous cliquez sur l'onglet Avancé, vous constatez qu'il est possible de paramétrer le suivi des modifications et leurs mises à jour.

• Valider ces réglages par Ok et confirmer la demande d'enregistrement.

Partage dans le Cloud - OneDrive
Il est temps maintenant de partager physiquement ce classeur. L'objectif est de fournir un emplacement accessible aux différents collaborateurs. Soit vous travaillez sur un réseau d'entreprise et vous l'enregistrez sur un lecteur partagé, soit vous faites intervenir des collaborateurs géographiquement éloignés. Dans ce cas, il faut partager ce classeur dans le Cloud pour permettre la collaboration. Pour ce faire, vous devez disposer d'un compte Outlook. Un compte Outlook se crée sur Outlook.com. Lorsqu'il est créé, un emplacement OneDrive est créé et accessible directement depuis votre explorateur Windows.

• Cliquer sur l'onglet Fichier puis choisir Partager dans la liste,
• Cliquer ensuite sur le bouton Enregistrer dans le Cloud,
• Puis, cliquer sur le lien Autres options,
• Dans la boîte de dialogue, choisir l'emplacement OneDrive du dossier Favoris,
• Valider par le bouton Enregistrer.

Le temps de l'Upload, vous êtes de retour sur le classeur.

• Se connecter ensuite sur Outlook.com,
• Cliquer sur le bouton de menu en haut à gauche et choisir OneDrive,

Le classeur Excel partagé apparaît dans la liste des fichiers de OneDrive. Mais pour que plusieurs utilisateurs puissent intervenir dessus, il faut communiquer l'Url qui permet d'y accéder. Et pour cela, il faut partager le classeur depuis OneDrive.

• Cocher la case à gauche du fichier pour le sélectionner,
• Cliquer sur le bouton de menu Partager,
• Dans la petite fenêtre qui apparaît, cliquer sur Obtenir un lien,
• Copier et coller ce lien dans un fichier à archiver,

Il s'agit de l'url publique que vous devez communiquer aux différents collaborateurs afin qu'ils puissent accéder au fichier Excel où qu'ils soient.

• Fermer cette petite fenêtre,
• Cliquer sur le lien du Fichier pour l'ouvrir.

Certaines configurations avancées, comme la protection de feuille nous empêche de l'ouvrir directement dans Excel OnLine.

• Choisir de l'ouvrir avec la version Excel de bureau.

Il faut imaginer qu'il s'agit d'une tierce personne en train de travailler sur le fichier Excel, chargé depuis OneDrive.

• Réaliser des modifications dans le catalogue,
• Enregistrer et fermer,
• Le rouvrir par Fichier puis Parcourir en sélectionnant le dossier OneDrive.

Les modifications s'enregistrent entre temps, dans le Cloud sur l'emplacement du fichier. Si bien que lorsque vous le rouvrez, vous récupérez toutes les données consolidées et synchronisées issues des travaux des différents intervenants.


Maintenant que le fichier est livré sur un emplacement public, Excel vous propose de le partager avec des personnes pour collaborer. Si vous cliquez sur le bouton Partager en haut à droite de la fenêtre Excel, le volet Partager s'affiche sur la droite du classeur. Vous pouvez obtenir un lien de partage, si ce n'est déjà fait, en cliquant sur la rubrique correspondante en bas du volet. Il faut ensuite cliquer sur le bouton de votre choix. Soit vous créez un lien de partage qui permet la lecture sans modifications, soit vous autorisez les collaborateurs à intervenir sur le fichier.


Ensuite, vous pouvez inviter des personnes à collaborer en cliquant sur le bouton du carnet d'adresses. Sachez que ces personnes doivent disposer d'un compte Microsoft en ligne, un compte Outlook. Ceci s'explique par le fait que tout collaborateur doit être authentifié pour la sécurité, le suivi des modifications et l'accès aux espaces de stockage OneDrive.

Consolidation et suivi des modifications
Une fois le classeur partagé, Excel permet au créateur propriétaire de suivre l'ensemble des évolutions apportées au fichier par les personnes collaborant.

• Cliquer sur le bouton Suivi des modifications du ruban Révision,
• Dans la liste, choisir Accepter ou refuser les modifications,
• Cliquer sur Ok pour les visualiser toutes,

La boîte de dialogue qui suit résume les modifications apportées. Si vous cliquez sur le bouton Refuser, vous retrouvez l'état d'origine pour la cellule concernée. Donc, en plus du suivi, Excel conserve la trace des modifications et vous permet de garder la main sur le classeur. Si vous cliquez sur Accepter, la modification est entérinée et n'apparaîtra plus dans le suivi des modifications.

• Fermer cette boîte de dialogue en cliquant sur Accepter tout par exemple,
• Cliquer de nouveau sur le bouton Suivi des modifications du ruban,
• Dans la liste, choisir cette fois Afficher les modifications,

Une boîte de dialogue apparaît. La case Suivre les modifications au fur et à mesure doit être cochée. A l'aide des listes déroulantes, vous pouvez personnaliser le suivi, depuis quand et par qui.

• Cliquer sur Ok pour valider ces choix.

Des annotations sous forme de coches apparaissent sur le coin supérieur gauche des cellules modifiées. Lorsque vous pointez dessus à la souris, une infobulle s'affiche indiquant la modification réalisée et la personne concernée. Cette option vous permet donc de suivre toutes les modifications apportées en temps réel.

Déconnecter un utilisateur du partage
Tout d'abord, il est possible de personnaliser le nom sous lequel vous intervenez. Pour ce faire :

• Cliquer sur l'onglet Fichier,
• Dans la liste, choisir Options tout en bas,
• Choisir la catégorie Général et modifier le nom d'utilisateur dans la zone Nom d'utilisateur de la section Personnaliser votre suite de programmes Microsoft Office,

C'est ce nom qui apparaîtra désormais dans le suivi des modifications.

• Cliquer maintenant sur le bouton Partager le classeur du ruban Révision,

L'onglet Modification de la boîte de dialogue qui apparaît, liste les utilisateurs actifs sur ce classeur.

• Sélectionner l'utilisateur à déconnecter et cliquer sur le bouton Supprimer,

L'utilisateur est ainsi déconnecté mais rien ne l'empêche de revenir plus tard et reprendre les modifications.

Gérer les modifications contradictoires
Ce type de conflit se produit lorsque des personnes collaborant à la modification du classeur partagé, travaillent sur les mêmes cellules. Excel ne peut conserver qu'une seule modification. Pour que vos modifications prennent le pas sur celles des autres en cas de contradiction :

• Cliquer sur le bouton Partager le classeur du ruban Révision,
• Activer l'onglet Avancé,
• Cocher la case Conserver celles déjà enregistrées dans la section En cas de modifications contradictoires,
• Valider la boîte de dialogue par Ok,

Stopper la collaboration - Annuler le partage du classeur Excel
Pour réaliser une telle opération, vous devez bien sûr être le propriétaire.

• Dans le ruban Révision, cliquer sur le bouton Partager le classeur,
• Dans l'onglet Modification de la boîte de dialogue, décocher la case Permettre une modification multi-utilisateur,
• Valider par Ok,
• Confirmer par Oui l'alerte Excel vous informant que le classeur va être retiré du partage.

Il est bien sûr aussi possible de supprimer le fichier du dossier de partage OneDrive de votre ordinateur. Ainsi il n'est plus accessible par les collaborateurs.