Partager un fichier Excel en ligne

Vous avez deux méthodes à suivre pour partager votre fichier Excel avec vos collaborateurs : soit vous le partagez en le joignant à un email, soit vous le placez sur le web et vous invitez vos amis ou membres de votre équipe à travailler ensemble sur ce fichier. Cette dernière s’avère la plus importante surtout avec l’émergence ces dernières années du télétravail.

Partager un fichier excel en ligne


Suivez avec moi l’explication de ces deux méthodes de partage à travers les lignes qui suivent, et profitez de plus d'un cadeau à télécharger gratuitement tout en bas de cet article :

1. La méthode simple de partage

a. Envoyez votre classeur par courrier électronique

- Connectez-vous à votre boite e-mail, puis créez un nouveau message, et cliquez ensuite sur Joindre un fichier.

- Dans la boite de dialogue qui s’ouvre devant vous, parcourez les dossiers de votre pc jusqu’à ce que vous localisiez votre fichier à partager.

- Sélectionnez-le puis cliquez sur Ouvrir ou bien double-cliquez sur son nom.

- Attendez que le chargement du fichier soit terminé.

- Cliquez enfin sur Envoyer.

Joindre un fichier email


b. Partagez le lien de stockage de votre fichier Excel

Si vous avez enregistré votre fichier dans un espace de stockage en ligne tel que : OneDrive, Google Drive, Dropbox ou Mega…., ces genres de services vous fournissent un lien (adresse web URL) de partage, copiez ce lien puis collez-le dans le corps de votre message puis cliquez sur Envoyer.

Lorsque le destinataire reçoit ce lien et clique dessus, il téléchargera le classeur partagé.

2. La co-édition

Utilisez l’outil de partage de l’application Excel

Excel vous permet de partager le classeur sur lequel vous travaillez en cours et ceci en vous fournissant un bouton de partage qui se trouve dans le coin supérieur droit.

Bouton Partager Excel


Mais pour pouvoir effectuer cette opération, vous devez être abonné à office 365.

Commencez le partage :

Connectez-vous à votre compte Office 365 en cliquant sur Connexion, puis entrez vos données nécessaires.

Cliquez ensuite sur Partager.

  • Si votre classeur est sauvegardé sur votre pc, vous serez appelé à l’enregistrer dans OneDrive, Excel vous affichera un message vous demandant de le faire premièrement avant de passer à l’utilisation des options de partage qui vont suivre.

Message de charger le fichier sur OneDrive


  • Si vous avez ouvert le classeur à partir de OneDrive vous serez dirigé directement aux étapes de partage.

Note : je vous suggère de commencer au préalable par enregistrer une copie de votre fichier Excel sur OneDrive avant de procéder au partage.

Utilisez les options de partage sous Excel

Dans la fenêtre Paramètres du lien, Excel affiche par défaut la première option : Toute personne disposant du lien peut modifier le contenu.

OPtions de partage Excel


Dans la zone Entrez un nom ou une adresse email, spécifiez avec qui vous voulez partager votre classeur en saisissant son nom ou son adresse email.

Ajouter un message : cette option est facultative, mais vous pouvez l’utiliser pour lier un petit mot descriptif à votre lien de partage.

Cliquez sur Envoyer lorsque vous aurez terminé.

Le destinataire recevra ensuite un message dans sa boite e-mail l’invitant à ouvrir le fichier partagé.

Remarque :

Après avoir partagé le lien du fichier, le destinataire pourra y accéder et modifier son contenu, et vous serez automatiquement informé par email de la part du service OneDrive. Vous pourriez aussi l'utiliser vous deux au même moment.

Cette possibilité de modifier le fichier Excel partagé est un paramètre de partage défini par défaut, mais vous pourriez le modifier pour limiter l’utilisation de votre fichier Excel à la consultation uniquement si vous le désiriez.

Dans la fenêtre Paramètres du lien, cliquez sur Toute personne disposant du lien peut modifier le contenu.

La fenêtre s’étend verticalement pour afficher d’autres options.

Sous Autres paramètres, vous avez :

Modifier les paramètres de partage Excel

  • Peut modifier : sélectionné par défaut, il indique que les utilisateurs qui recevront votre classeur pourront le modifier.
  • Consultation possible : ce paramètre vous permet de rendre votre classeur Excel disponible en lecture seulement. En le choisissant, Excel vous fournit quelques options d'autorisations d'accès  à définir pour ce choix :

Paramètres de consultation possible Excel

  • Définir la date d’expiration du lien qui va mettre fin à l’accès autorisé à votre fichier.
  • Définir le mot de passe pour protéger votre fichier et ne donner la permission d’y accéder qu’à ceux qui disposent de ce mot de passe.
  • Bloquer le téléchargement : cette option reste désactivée et le téléchargement demeure permis tant que la modification est autorisée. Pour activer cette option et bloquer le téléchargement choisissez l’option : Consultation possible.

Une fois vous avez terminé de paramétrer les autorisations d’accès à votre fichier Excel, cliquez sur Appliquer.

Après avoir donc choisi d'interdire la modification, vous verrez que l’option Toute personne disposant du lien peut modifier le contenu est remplacé par Toute personne disposant du lien peut consulter le contenu.

Envoyer fichier excel pour consultation uniquement


Spécifiez les personnes avec qui partager votre classeur

Cliquez sur Toute personne disposant du lien.

Choisissez dans la liste, les personnes spécifiques avec qui vous voulez partager votre lien :

  • Les personnes au sein de votre organisation : votre lien sera accédé uniquement par les membres de votre organisation qui possèdent un compte office 365. Ce lien ne fonctionnera pas pour les externes.

  • Les personnes ayant déjà l’accès : si vous avez déjà partagé votre fichier Excel avec des personnes, il sera très facile de repartager avec eux votre lien.
  • Des personnes spécifiques : qui pourraient être des personnes externes à votre organisation (amis, proches ou autres).

Lorsque vous avez sélectionné le type des personnes souhaité et après avoir défini les autorisations voulues, cliquez sur Appliquer.

Saisissez ensuite les e-mails des personnes avec qui vous voulez partager le fichier Excel puis cliquez sur Envoyer.

Attention !

Si vous avez envoyé le lien à des personnes externes, il se peut que votre organisation interdise l’accès au fichier pour le modifier ou le télécharger, ils auront uniquement le droit de le consulter.

Utilisez Outlook

Dans mon cas j’ai un compte professionnel office 365, je peux choisir de passer par Outlook pour partager le lien du ficher Excel.

Si vous êtes dans le même cas ou si Excel vous affiche un bouton portant le nom d’un autre service de messagerie installé sur votre pc que vous utilisiez, cliquez sur lui.

Votre logiciel de messagerie se lance et un nouveau message est créé automatiquement contenant le lien du classeur partagé.

Complétez les données de votre message, en saisissant l’adresse ou les adresses email de vos destinataires.

Cliquez également sur envoyer une copie soit sous forme d’Excel ou de PDF

Partager fichier Excel sous forme de PDF


Pareillement à l’utilisation du lien de partage, un message électronique sera créé automatiquement contenant cette fois une copie de votre classeur.

Complétez les données nécessaires puis cliquez sur Envoyer.

Téléchargez un extrait de mon livre inédit :

J'ai écrit un livre qui explique une méthode professionnelle qui permet de maitriser ce travail collaboratif sur un fichier Excel dont vous pouvez télécharger un extrait gratuitement via ce lien :

Comment créer une alerte d'anniversaire ?

Voyons dans ce cours comment faire pour créer une alerte d’anniversaire ou un rappel d’anniversaire. Ça peut fonctionner également pour une date d’échéance.

Comment créer une alerte d'anniversaire

Je vais commencer premièrement par vous montrer comment faire pour afficher un message de « Joyeux anniversaire » par exemple en utilisant une formule puis comment mettre en évidence la ligne correspondant à cet anniversaire. Dans un second lieu, vous allez voir comment créer une alerte 7 jours avant l’arrivée de la date d’anniversaire.

Créer une alerte le jour de l’anniversaire

Créer un rappel d’anniversaire à l’aide d’une formule

À partir de ce tableau, je veux afficher un message tel que « Joyeux anniversaire » devant le prénom de la personne dont l’anniversaire est arrivé.


Pour avoir ce résultat, deux conditions doivent être respectées : le jour et le mois de la date de naissance doivent être égaux au jour et au mois de la date d’aujourd’hui.

Et pour traduire ceci en Excel, je vais utiliser les deux fonctions SI et ET de cette façon :

- Si jour(date de naissance) = jour(date d’aujourd’hui) ET mois(date de naissance) = mois(date d’aujourd’hui), Afficher « Joyeux anniversaire », sinon n’afficher rien.

  • Je sélectionne alors la première cellule C2 puis je saisis la formule suivante :

=SI(ET(JOUR(B2)=JOUR(AUJOURDHUI());MOIS(B2)=MOIS(AUJOURDHUI()));"Joyeux anniversaire";"")

  • Je copie la formule vers le bas, et voici le résultat obtenu en tenant compte que la date d’aujourd’hui est le 05/10/2022

Alerte anniversaire le jour même en utilisant une formule


Colorier la ligne d’anniversaire à l’aide de la mise en forme conditionnelle

  • Je choisis la dernière règle dans la liste des types de règle puis j’insère cette formule :

=ET(JOUR($B2)=JOUR(AUJOURDHUI());MOIS($B2)=MOIS(AUJOURDHUI()))

  • Ensuite je choisis la couleur de remplissage en cliquant sur le bouton Format

  • Je valide enfin.
Alerte anniversaire le jour même en utilisant une mise en forme conditionnelle


Et voilà !

Résultat Alerte anniversaire le jour même en utilisant une mise en forme conditionnelle


Créer une alerte d’anniversaire en fonction d’un délai défini

Supposons que je souhaite colorier la ligne de l’anniversaire que sa date arrivera dans 7 jours qui vont venir.

Pour cela je vais me servir cette fois de la fonction DATEDIF.

Note : j’ai un cours sur la fonction DATEDIF que vous pourriez suivre en cliquant sur ce lien : La fonction DATEDIF

- Je commence tout d’abord par tester ma formule que je vais utiliser :

  • Dans la cellule E2 je saisis donc ma formule : =DATEDIF(B2-$F$1;AUJOURDHUI();"Yd")
        - $F$1 contient le nombre de jours restant à l’anniversaire, dans mon cas, c'est 7 jours. Cette cellule est figée car je vais copier la formule vers le bas et je veux garder cette valeur dans toutes les formules copiées.

        - Le paramètre « yd » permet de calculer la différence en jours en ignorant les années.

  • Je copie la formule vers le bas.

Si ma formule renvoie 0, une alerte devra être déclenchée.

Créer Alerte avant le jour anniversaire d'un délai défini en utilisant la formule DATEDIF


Par exemple la cellule E9 affiche 0, alors je vais passer à l’utilisation de la mise en forme conditionnelle pour mettre en évidence la ligne 9 de mon tableau comme signe d’alerte d’anniversaire.

  • Je fais afficher la boite Nouvelle règle de mise en forme après avoir sélectionné mes données comme vous l’avez suivi précédemment, puis je sélectionne le dernier type de règle.

  • J’insère cette formule :

=DATEDIF($B2-$F$1;AUJOURDHUI();"Yd")=0

  • Ensuite je sélectionne ma couleur de remplissage et je valide.
Alerte anniversaire le jour même en utilisant une mise en forme conditionnelle


Voilà ce que j’ai obtenu (toujours on tient compte qu’aujourd’hui est le 05/10/2022 !):

Résultat Créer Alerte avant le jour anniversaire d'un délai défini en utilisant une mise en forme conditionnelle


  • Je modifie les dates de naissance pour tester ce travail :
Tester sous excel Alerte avant le jour anniversaire d'un délai défini en utilisant une mise en forme conditionnelle


Enfin, si vous souhaitez modifier le délai, vous n’avez qu’à saisir le nombre de jours dans la cellule F1 sans toucher à la formule.

Voilà donc c’est la fin de ce cours, n’hésitez à m’écrire si vous avez des remarques ou des questions dans la zone de commentaire ci-dessous.

Comment utiliser la fonction MAKEARRAY ?

 MAKEARRAY est une nouvelle fonction d’Excel 365 qui permet de générer facilement un tableau de valeurs d’une taille de ligne et de colonne spécifiée, en appliquant une fonction LAMBDA.

Comment utiliser la fonction MAKEARRAY ?


La première remarque qu’on peut faire avant de commencer ce cours c’est qu’Excel garde, à l’instant où j’écris cet article, la nomination anglaise de cette fonction, contrairement à ce qui était habituel lors de l’utilisation de ses fonctions. Est-ce que la traduction de ce nom apparaitra ou non dans les mises à jour qui vont venir ? je n'ai aucune information pour l'instant à ce propos ! 

Voilà, c’est juste une petite remarque à laquelle je voulais attirer votre attention avant de passer à l’explication de l’utilisation de la fonction MAKEARRAY.

Comment utiliser la fonction MAKEARRAY ?

La syntaxe de cette fonction est :

MAKEARRAY (nombre de lignes ;nombre de colonnes ;calcul)

On demande à Excel alors de créer un tableau se composant du nombre de lignes et de colonnes que l’on définit dans les deux premiers arguments, puis de remplir ce tableau par les données qui seront renvoyés par le calcul qui va être effectué (le troisième paramètre) à l’aide de la fonction LAMBDA.

Note :LAMBDA est une nouvelle fonction qui vient d’apparaitre récemment. Vous trouvez plus d’explication sur sa façon d’utiliser en suivant ce lien : Comment utiliser la fonction LAMBDA ?

Exemple simple d’utilisation de MAKEARRAY

Voici comment utiliser MAKEARRAY pour créer par exemple une table de multiplication très rapidement :

Sélectionnez une cellule, par exemple A1, puis tapez cette formule :

=MAKEARRAY(10;10;LAMBDA(x;y;x*y))

Lorsque vous validez, Excel va générer une table de multiplication de dimensions 10×10.

Table de multiplication avec MAKEARRAY

  • Nombre de lignes : 10
  • Nombre de colonnes : 10
  • Calcul : LAMBDA(x;y;x*y), x représente ici l’index de ligne (de 1 à 10) et y l’index de colonne (de 1 à 10 également) qui seront utilisés dans le calcul de LAMBDA (x*y).

Utilisation complexe de MAKEARRAY

  1. Intégrer INDEX et ALEA.ENTRE.BORNES dans MAKEARRAY

Supposons par exemple que j’ai un tableau qui affiche les noms de quelques fruits, et que je veux qu’à partir de ce tableau créer un autre tableau de dimensions spécifiées qui sera rempli aléatoirement par ces noms de fruits.

Voici donc mon tableau de départ composé de 3 lignes et de 4 colonnes:

Tableau des fruits

  • Primo, je veux générer aléatoirement les noms des fruits dans un tableau d’une colonne et de 5 lignes :

Pour cela je vais me servir de la fonction INDEX et de ALEA.ENTRE.BORNES qui vont être insérées dans la fonction LAMBDA de cette façon :

=MAKEARRAY(5;1;LAMBDA(x;y;INDEX($A$2:$D$4;ALEA.ENTRE.BORNES(1;3);ALEA.ENTRE.BORNES(1;4))))

Voici le résultat obtenu :

Utilisr MAKEARRAY avec INDEX et ALEA.ENTRE.BORNES

J’explique le fonctionnement de cette formule :

Tout d’abord nous savons que la fonction INDEX nous permet de renvoyer une valeur qui se trouve dans une plage de cellules à l’intersection d’une ligne et d’une colonne que nous lui spécifions.

Plus de détails sur l’utilisation de la fonction INDEX ici : Comment utiliser la fonction INDEX?

Nous savons également que la fonction ALEA.ENTRE.BORNES renvoie un nombre aléatoire entre les nombres que l’on spécifie.

Dans notre cas, INDEX va chercher dans le tableau des noms des fruits (A2:D4) le nom du fruit se trouvant à l’intersection de la ligne que son numéro sera renvoyé par ALEA.ENTRE.BORNES(1;3) et de la colonne dont le numéro sera renvoyé par ALEA.ENTRE.BORNES(1;4).

Et puisque notre tableau à créer sera d’une dimension de 5x1, la fonction LAMBDA sera exécutée 5 fois pour remplir les 5 cellules de notre nouveau tableau.

Appuyez sur F9 pour actualiser la formule :

L’image suivante essaie de vous rapprocher ce qui était effectué pour générer le tableau créé :

Comment fonctionne MAKEARRAY


  • Secundo, je vais créer un tableau avec avec plus de lignes ou de colonnes :

Cette fois je vais procéder à une petite modification dans l’écriture de ma formule MAKEARRAY, en effet, au lieu de saisir à chaque fois les nombres de lignes et de colonnes souhaités, je vais insérer les références des cellules C8 et C9 qui les contiennent, et de cette façon il me sera très facile la tâche de modifier ces deux nombres.

=MAKEARRAY(C8;C9;LAMBDA(x;y;INDEX($A$2:$D$4;ALEA.ENTRE.BORNES(1;3);ALEA.ENTRE.BORNES(1;4))))

Rendre MAKEARRAY dynamique


Voici alors un exemple de résultat que j’ai obtenu :

Tableau généré par MAKEARRAY

En appuyant sur F9 encore, le calcul sera actualisé.

Comme vous pouvez le voir, avec seulement une formule très réduite, nous sommes arrivés à créer très facilement un tableau avec des données dynamiques et qui changent chaque fois que nous actualisons le calcul, en appuyant sur la touche F9.

    2. Générer un tableau de lettres aléatoires de A à Z

Vous savez que chaque caractère Windows (lettre, nombre, symbole, signe…) a un code spécial. Si nous prenons l’exemple des lettres majuscules de A à Z, leurs codes vont de 65 à 90. (Lisez sur Jeu de caractères ANSI et Unicode en cherchant sur google pour plus d’informations).

Si vous tapez par exemple : =CAR(65) dans une cellule, Excel vous affiche A

Choisissez un autre nombre de 65 à 90 pour afficher la lettre qui lui correspond.

J’ai fait cette petite introduction parce que mon but est de vous préparer à trouver la bonne formule utilisant MAKEARRAY et CAR pour créer un tableau de 2 lignes et de 6 colonnes qui va afficher aléatoirement des lettres majuscules de A à Z, et qui sera actualisé à chaque fois que l’on tape F9.

Voici un tableau pareil à ce que vous pourriez obtenir :

Générer un tableau de lettres aléatoires de A à Z


A vous donc de trouver cette formule et de la partager avec nous dans la zone commentaire ci-dessous.

Je serai à l’attente de votre réponse.

Bonne chance !

Comment utiliser la fonction LAMBDA ?

La fonction LAMBDA est une des fonctions qui ont fait leurs apparitions lors de la dernière mise à jour d’Excel 365. Son rôle est de vous permettre de créer vos propres fonctions en leur donnant des noms de votre choix et de les appeler par ces noms lorsque vous les utiliser.

La fonction LAMBDA

Comment utiliser la fonction LAMBDA ?

Avec l’arrivée de la fonction LAMBDA, Excel vous facilite la tâche de créer vos propres fonctions sans passer par le code VBA.

Par exemple, je souhaite créer une fonction qui effectuera le calcul suivant : 2x+y, en utilisant les deux variables x et y.

Si je donne par exemple à la variable "x" la valeur 5 et à "y" la valeur 3, Excel va m’afficher 13.

Comment LAMBDA va m’aider donc à créer cette fonction ?

Syntaxe de la fonction LAMBDA :

La syntaxe de la fonction LAMBDA est très simple :

=LAMBDA(Paramètre1; Paramètre2;...; ParamètreN; Calcul ou formule à effectuer en utilisant les paramètres)

Dans mon cas j’ai deux paramètres x et y et pour la formule utilisant ces paramètres c’est : 2x+y

Alors LAMBDA s’écrira comme suit :

« =LAMBDA(x ;y ;2x+y) »

Attendez ! l’utilisation de cette fonction de cette façon ne fonctionnera pas ! Pourquoi ?

Premièrement l’écriture de la formule 2x+y n’est pas reconnue par Excel car le signe de multiplication est omis.

Vous pouvez essayer de taper dans une cellule : =2x+y et lorsque vous la validez Excel vous affiche un message d’erreur.

Message d'utilisation incorrecte de la formule

L’écriture donc correcte est 2*x+y

Essayez maintenant !

Qu’est-ce que vous remarquez ?!

Erreur de type Nom

L’écriture est acceptable mais Excel ne peut pas effectuer le calcul sur des données (x et y) qui ne sont pas des nombres. On peut mettre par exemple à la place de x et de y des références des cellules contenant consécutivement les deux valeurs de x et de y comme dans l’exemple suivant : =2*A2+B2

Création d'une formule d'équation


Ce qui importe jusqu’ici, c’est que la formule fonctionne correctement, ce qui me permet alors de l’utiliser dans les arguments de LAMBDA.

Je reviens donc à LAMBDA et j’écris ma formule : =LAMBDA(x ;y ;2*x+y)

Je la teste (faites aussi la même chose) :

Une autre erreur s’affiche #CALC!, n’est-ce pas ?!

Erreur CALC de LAMBDA

Cette erreur nous informe que la fonction LAMBDA, même si elle est bien codée, n’effectuera pas de calcul et n’affichera aucun résultat que lorsqu’elle sera appelée ou que lorsqu’on lui ajoutera un appel !!!!

J’explique mieux :

Num1 : je vais ajouter un appel à LAMBDA :

Cela signifie que je dois écrire à la fin de la formule les valeurs de x et de y à utiliser dans mon calcul .

Et comme nous l’avons vu, ces valeurs se trouvent dans A2 et B2.

De ce fait, ma formule s’écrira comme suit :

=LAMBDA(x;y;2*x+y)(A2;B2)

Lorsque je valide, Excel affiche cette fois le bon résultat.

Ajout d'appel pour la fonction LAMBDA

Num2 : je vais appeler LAMBDA créée :

Ici on arrive à une étape très importante puisqu’on va créer notre propre fonction qui va nous faciliter tout le travail précédent :

- Je clique donc sur Définir un nom qui se trouve dans le groupe Noms définis sous l’onglet Formules.

- Dans la petite fenêtre qui apparaît :

>> Je saisis un nom pour ma propre fonction, par exemple : MonEquation

>> Je donne une petite description de cette fonction dans la zone Commentaire

>> Enfin, j’insère ma formule utilisant la fonction LAMBDA dans la zone Fait référence à : regardez l’illustration suivante :

Créer un nom de fonction utilisant LAMBDA

Voilà donc ma fonction est créée, je reviens à la cellule qui va afficher le résultat de mon calcul et j’insère ma fonction puis je sélectionne les deux cellules contenant les valeurs correspondant aux variables x et y, puis je valide: 

Exemple d'utilisation d'une fonction personnalisée LAMBDA


C’est très intéressant non ?!

J’applique ma fonction pour d’autres valeurs :

Voilà le résultat obtenu:

Copier une fonction utilisant LAMBDA


Récapitulons :

Pour créer votre propre fonction en utilisant LAMBDA, vous devez suivre ces étapes :

- Créez votre formule à insérer dans LAMBDA et testez-la premièrement.

- Créez votre formule en utilisant LAMBDA et copiez-la

- Utilisez l’outil Définir un nom pour créer votre propre fonction en remplissant les paramètres demandés (Nom, Commentaire (facultatif), Fait référence à (coller votre formule LAMBDA))

- Utilisez enfin votre nouvelle fonction créée.

Note : La fonction personnalisée que nous avons créée est utilisable dans tout le classeur.

Exemple de cas pratique d’utilisation de la fonction LAMBDA

Dans l’exemple suivant nous allons voir comment créer notre propre fonction en utilisant LAMBDA pour calculer la moyenne de vente par jour.

 

Tableau de vente

La formule que nous allons utiliser est donc : Total/(Date de fin – Date de début + 1)

Alors nous aurons besoin de trois variables pour créer notre formule LAMBDA : Total, DateDébut et DateFin

Après avoir testé avec succès la formule précédente, je l’emploie dans LAMBDA de cette façon:

=LAMBDA(Total ;DateDébut ;DateFin ; Total/(DateFin – DateDébut  + 1)

Cette formule je vais l’appeler dans une fonction que je vais nommer : MoyenneVenteJour.

Dans la cellule E2, j’insère ma propre fonction MoyenneVenteJour 

Je sélectionne D2 pour le paramètre Total, B2 et C2 pour les paramètres DateDébut et DateFin.

Fonction moyenne de vente par jour

Je valide et voilà ça fonctionne bien, je copie maintenant ma formule vers le bas :

Calcul de moyenne de vente par jour

Tout marche très bien !


Bon voilà, j’ai essayé dans ce cours de vous montrer comment utiliser la fonction LAMBDA pour créer vos propres fonctions et faciliter votre travail sous Excel. Si vous avez trouvé des difficultés dans l’utilisation de LAMBDA ou si vous avez quoi à ajouter, écrivez-moi dans la zone Commentaire ci-dessous.

Je vous laisse en plus ce petit exercice à faire pour calculer le Prix TTC, Essayez d’appliquer ce que vous avez vu dans ce cours pour créer votre propre fonction correspondante et partagez-la avec nous dans la même zone Commentaire.

Bonne chance !

Exercice calculer le prix TTC



Comment créer un tableau de comparaison des offres ?

Dans cet exercice je vais vous montrer comment établir un tableau de comparaison des offres, en se basant sur les réponses des fournisseurs à un appel d'offres lancé par un hôtel qui désire renouveler les chaises de ses 150 chambres.

Ce tableau de comparaison des offres va permettre à notre hôtel de sélectionner le fournisseur qui propose le prix le moins cher. Ce fournisseur doit aussi respecter le critère de livrer la commande dans un délai qui ne dépasse pas les 30 jours.

Comment créer un tableau de comparaison des offres


Tout d’abord, j’ai préparé ce fichier (que vous pouvez télécharger ici) dans lequel vous pouvez voir, en plus de mon tableau de comparaison des offres, une petite partie intitulée Besoins et critères concernant ce que demande l’hôtel.


parties composant le tableau de comparaison des offres


Et dans le tableau qui se trouve en dessous et qui porte le nom de Réponses des fournisseurs, j'ai saisi les données collectées des réponses des fournisseurs reçues par l’hôtel. Ces données vont me servir à remplir mon tableau de comparaison des offres et d’en faire une étude comparative afin de sélectionner le fournisseur qui présente la meilleure offre pour mon hôtel.

Entamons le remplissage de notre tableau de comparaison des offres :

Pour le prix unitaire, vous pouvez soit copier les prix à partir du tableau Réponses des fournisseurs soit créer une petite formule qui va introduire ces valeurs d’une façon automatique.

Sélectionnez donc les trois cellules Prix unitaire puis tapez =G9, validez ensuite par Ctrl+Entrée.

Calculer le prix unitaire


Calculer le montant total HT :

Le montant total = le prix unitaire x le nombre de chaises

De la même manière et pour insérer cette formule dans les trois cellules du Montant Total HT, sélectionnez ces dernières puis saisissez =B6*$G$3, validez enfin par Crtl+Entrée.

Calculer le montant total HT


Remarque : N’oubliez pas de figer la cellule G3 qui contient le nombre de chaises.

Insérer le taux de remise

Vous remarquez sans doute que dans le tableau des réponses des fournisseurs, le fournisseur F2 ne fournit aucune remise, alors que les deux autres proposent leurs remises mais suivant quelques conditions :

  • Pour le fournisseur F1 :

La remise de 6%  est applicable à partir de 100 chaises commandées, ce que nous allons traduire sous forme d’une formule utilisant la fonction SI :

- Sélectionnez donc la cellule B8 puis tapez cette formule : =SI(G3>=G11;G10;"")

- G3 contient le nombre de chaises commandées et qui est supérieur à 100 déjà saisi dans G11.

- Dans ce cas, Excel va afficher alors 6% se trouvant dans la cellule G10.

insérer taux de remise


  • Pour le fournisseur F2 :

Il propose une remise qui varie en fonction du nombre de chaises commandées :

5% à partir de 80 chaises et 10% si la quantité passe à 100 chaises ou plus.

Voici la formule à insérer dans la cellule D8 :

=SI(G3>=I13;I12;SI(G3>=I11;I10;""))

J’explique :

Si le nombre de chaises contenu dans G3 est supérieur ou égal à 100 se trouvant dans I13, le taux 10% sera affiché, sinon, on demande à Excel de vérifier si cette quantité commandée est supérieure ou égale à 80 que contient I11 et d’afficher donc 5% se trouvant dans I10.

Si aucune condition n’est respectée, alors Excel n’affichera rien.

Le nombre de chaises dépasse 100, alors le taux 10% est affiché.

Insérer taux de remise selon conditions


Calculer le montant de remise :

Le montant de remise = le montant total x le taux de remise

Sélectionnez les trois cellules B9, C9 et D9 puis tapez  =B7*B8 et validez par Ctrl+Entrée.

Calculer le montant de remise


Note : appliquez le format monétaire ou comptabilité pour mettre en forme les montants calculés.

Calculer le Net commercial

C’est le montant total HT le montant de remise

Sélectionnez la plage de cellules B10:D10 puis tapez ceci =B7-B9 et validez ensuite par Ctrl+Entrée.

Calculer le Net commercial


Insérer le taux d’escompte

Copiez tout simplement les valeurs à partir du tableau des réponses des fournisseurs ou sélectionnez les trois cellules de B11 à D11 puis tapez =G14 et validez par Ctrl+Entrée.

Calculer le montant d’escompte

Il égale à : Net commercial x taux d’escompte

Pour faire rapidement comme d’habitudes, sélectionnez la plage de cellules B12:D12 puis saisissez la formule suivante : =B10*B11, validez ensuite par Ctrl+Entrée


Calculer le montant d’escompte


Calculer le Net financier

le Net financier= Net commercial - montant d’escompte

Sélectionnez les cellules du Net financier puis insérez cette formule =B10-B12, validez ensuite par Ctrl+Entrée.

Calculer le Net financier


Saisir le frais de transport :

Le fournisseur F2 propose de livrer la marchandise gratuitement contrairement aux deux autres.

Copiez donc le frais correspondant au fournisseur F1, quant au fournisseur F3 vous avez besoin de calculer ce frais en fonction du pourcentage qu’il précise (1% du Net commercial).

Pour cela, sélectionnez la cellule D14 et saisissez la formule suivante : =D10*1%

Calculer le Net HT

le Net HT = le Net financier + frais de livraison

Sélectionnez donc les trois cellules qui vont afficher le Net HT puis saisissez la formule =SOMME(B13:B14), validez ensuite par Ctrl+Entrée.


Calculer le Net HT

Insérer les données du délai de livraison et de la garantie

Sélectionnez la plage de cellules B16:D17 et insérez cette petite formule =G16 puis validez par Ctrl+Entrée.

Si vous souhaitez afficher les nombres des jours suivis du mot "jours", procédez comme suit :

  • Sélectionnez les cellules contenant ces nombres puis cliquez sur la flèche située dans le coin inférieur droit du groupe Nombre.
  • Dans la fenêtre qui apparaît cliquez sur Personnalisée
  • Dans la zone Type tapez 0" jours" et cliquez enfin sur le bouton OK.
Formater un nombre de jours


Suivez la même démarche si vous souhaitez également afficher le mot « mois » devant le nombre de mois de la garantie.

Choisir le fournisseur approprié

Pour ce faire, nous allons analyser ces deux données essentielles : Net HT et Délai de livraison.

Pour notre hôtel, ce qui est favorable pour lui c’est :

  • Le Net HT le moins élevé,
  • Un délai de livraison le plus bref qui ne dépasse pas les 30 jours prédéfinis.

Comme vous pouvez le remarquez, bien entendu, et d’après les résultats obtenus, le fournisseur F1 la ramène parce que son offre est la moins chère (30 629,70 Euros) , en plus, il est le seul qui propose de livrer la commande dans un délai plus rapide  (15 jours ) par rapport aux autres fournisseurs.

Prix moins cher et délai bref de livraison


Pour traduire ce travail sous Excel, vous pouvez procéder ainsi :

  • Afficher le nom du fournisseur proposant le prix le plus petit :

Sélectionnez la cellule B19, puis tapez la formule suivante :

=SI(B15=MIN($B$15:$D$15);B5;"")

Premièrement Excel va vérifier si le montant Net HT du Fournisseur F1 égale au montant le plus petit de ceux obtenus, si c’est le cas ; il affiche le nom de ce fournisseur contenu dans la cellule B5, sinon Excel n’affichera rien.

Trouver le fournisseur le moins cher


Puis on copie la formule dans les deux cellules C19 et D19.

  • Afficher le nom du fournisseur qui va livrer la commande rapidement :

Sélectionnez la cellule B20, et tapez la formule suivante :

=SI(ET(B16<=$G$4;B16=MIN($B$16:$D$16));B5;"")

Dans cette formule, nous avons deux conditions à vérifier :

Le délai de livraison ne doit pas dépasser les 30 jours (B16<=$G$4) et s'il est le plus petit parmi les trois proposés par nos fournisseurs (B16=MIN($B$16:$D$16)).

J’ai utilisé dans ma formule la fonction ET pour tester ces deux conditions.

Si les deux conditions sont respectées, Excel affichera le nom du fournisseur F1 qui se trouve dans la cellule B5, sinon il n’affichera rien.

Afficher le nom du fournisseur qui va livrer la commande rapidement


Copiez la formule dans les deux autres cellules C20 et D20.

D’après les résultats obtenus, j’arrive à décider que le fournisseur F1 est celui que l’hôtel va choisir pour lui passer la commande.

Résultat choix du fournisseur


Voilà donc, vous avez suivi comment réaliser un tableau de comparaisons des offres sous Excel, en vous basant sur un exemple d’un hôtel qui a voulu commander 150 chaises pour ses 150 chambres selon des critères précis, et vous avez vu comment faire pour arriver à choisir parmi trois fournisseurs celui le plus approprié. 

Bien évidemment, il existe d'autres exemples de choix de fournisseurs tel que le choix effectué à l'aide de critères pondérés.