Excel permet d’accélérer considérablement la saisie et même d’automatiser certaines tâches répétitives. Nous allons voir comment créer une mini-application de saisie et d’envoi de devis.

Objectifs :

  • Créer une « application » avec Excel.
  • Lister et exploiter des données.
  • Envoyer un mail automatiquement.

Durée : 2 heures

Apprentissage :

  • La fonction « recherchev »
  • Les bases des macros

Contexte

Vous travaillez en job d’été dans une boulangerie. Chaque jour les professionnels de la ville (restaurants, pizzerias, campings, …) passent des commandes pour le lendemain. Vous êtes chargés de prendre les commandes au téléphone, de faire un devis à la main, puis le faxer (oui, ça existe encore).

La procédure est très fastidieuse : les calculs complexes, les erreurs humaines fréquentes, envoyer un fax est une opération lente…

Vous proposez donc au boulanger de moderniser la procédure en créant un fichier excel qui permettra de saisir un devis au cours de l’appel téléphonique et de l’envoyer directement par mail.

Structure du fichier

Le fichier sera composé de 3 feuilles :

  1. Une feuille « Produits » qui listera tous les produits proposés par la boulangerie.
  2. Une feuille « Clients » qui listera les coordonnées des clients.
  3. Une feuille « Devis » qui permettra de saisir facilement la commande du client et de lui envoyer le devis.

1) La feuille « Produits »

Sur cette feuille vous listerez les produits de la boulangerie :

  • Le nom du produit (« Baguette », « Pain », « Malabar’, « Mars », …).
  • Le prix HT du produit.
  • Le taux de TVA à appliquer (liste déroulante). En France, tous les produits ne sont pas soumis au même taux de TVA :
    • Les produits de premières nécessité (Baguette, Pain, …) sont taxé à 5%.
    • Les autres produits sont taxés à 20%.
  • Le Prix TTC du produit (calculé automatiquement).

2) La feuille page « Client »

Sur cette feuille vous listerez les clients :

  • Le nom du client (« Pizza Del Arte », « Chez Joe », « Camping des flots bleus », …)
  • L’adresse
  • Le code postal
  • La ville
  • L’adresse e-mail

3) La feuille « Devis »

Cette feuille sera composée de trois parties :

  1. Les coordonnées du client.
  2. Un tableau des produits commandés.
  3. Un bouton « Envoyer le devis »

devis

 3.1) Les coordonnées du client

Une liste déroulante vous permettra de sélectionner le nom d’un client. Cela affichera automatiquement :

  • l’adresse
  • le code postal et la ville (séparés par une virgule)
  • l’adresse e-mail

3.2) Les produits commandés

Chaque ligne du tableau représente un produit, les colonnes sont :

  • Nom du produit (liste déroulante)
  • Le prix unitaire Hors Taxe (automatique)
  • La quantité commandée (nombre entier uniquement)
  • Le montant Hors Taxe (automatique)
  • Le montant de la TVA (automatique)
  • Le montant Toute Taxe Comprise (automatique)

Le tableau affichera automatiquement en bas :

  • Le nombre de produits commandés
  • Le prix total HT
  • Le montant total de la TVA
  • Le prix total TTC

3.3) Le bouton « Envoyer e-mail »

Ajouter sous le tableau un bouton « Envoyer le devis » qui envoie automatiquement un mail avec :

  • les informations sur le client.
  • les produits commandés et leur prix
  • les totaux.

La fonction « recherchev »

Pour trouver automatiquement les informations contenues dans la feuilles « Produits » et « Clients », vous devrez utiliser la fonction « recherchev ».

Cette fonction est très puissante : elle permet de trouver une valeur dans un tableau. Par exemple, si j’ai ce tableau :

recherchev

La fonction « recherchev » est capable de déterminer que le code postal de « Pizza Del Arte » est « 80100 ».

Pour cela, elle a besoin de 3 informations :

Le nom du client dont on cherche une donnée. Dans l’exemple, c’est « Pizza Del Arte » :

recherchev1

Le tableau dans lequel se trouve le nom et la donnée recherchée. Dans l’exemple, c’est [B2:E5]

recherchev2

Le numéro de colonne dans lequel se trouve la donnée recherchée. Dans l’exemple, c’est la 3 :

recherchev3Pour trouver le code postal de « Pizza del Arte », on doit donc utiliser la formule suivante :

=recherchev(« Pizza Del Arte »; [B2:E5]; 3)

IMPORTANT SINON CA NE FONCTIONNERA PAS :

La colonne qui sert à rechercher l’item doit être triée par ordre croissant

Autrement dit, si « Chez Joe » est après « Pizza Del Arte », vous aurez des erreurs.

Le bouton « Envoyer le devis »

  • Dans l’onglet « Développeur », cliquez sur « Insérer » et ajoutez un bouton.
  • Excel vous propose alors d’affecter une macro au bouton, cliquez sur « Nouvelle »
  • Copiez-collez ce bout de code entre les lignes « Sub… » et « End Sub » :

   Dim AdresseMail
   AdresseMail = Range(« B5 »).Value ‘ la cellule avec l’adresse mail du client
   
    ActiveSheet.Range(« A1:F20 »).Select ‘ la plage de cellules à envoyer
    ActiveWorkbook.EnvelopeVisible = True
     
    With ActiveSheet.MailEnvelope
        .Introduction = « Bonjour , ci-dessous votre devis : »
        .Item.To = AdresseMail
        .Item.Subject = « Devis de la boulangerie »
        .Item.Send
    End With

Ce bout de code permet d’envoyer automatiquement un e-mail.

Adaptez ce code à votre fichier Excel pour automatiser l’envoi du mail.