
Première formule Excel sur un devis qui se calcule seul
Le montant de chaque ligne, le total avec SOMME, la remise et la TVA, sur un devis qui se recalcule seul.
Quand chacun tape ce qu'il veut dans une colonne, les comptes deviennent faux. Payé, réglé et Paye sont trois mots différents pour Excel. Une liste déroulante oblige à choisir. Dans ce guide, vous créez la liste simple, puis une liste qui suit chaque ajout et enfin une liste en cascade, sur le suivi des commandes d'une papeterie.


Vidéo hébergée par YouTube : au lancement, YouTube peut déposer des cookies (les cookies de YouTube expliqués).
Léa tient une petite papeterie. Elle suit ses commandes dans un tableau, avec une colonne Statut. Chacun y écrit un peu ce qu'il veut. On trouve Payé, mais aussi réglé. On trouve même Paye sans accent.
Pour Excel, ce sont trois mots différents. La formule =NB.SI(F2:F9;"Payé") trouve trois commandes payées alors qu'il y en a cinq. Avec une liste déroulante, on ne tape plus rien. On choisit dans la liste et tout le monde écrit la même chose, au caractère près.
Sélectionnez les cellules du statut, de F2 à F9. Dans l'onglet Données, cliquez sur Validation des données. La boîte s'ouvre sur l'onglet Options. Dans Autoriser, choisissez Liste. La case Liste déroulante dans la cellule apparaît à côté de Ignorer si vide. Laissez les deux cochées.
Dans Source, tapez les choix séparés par des points-virgules, par exemple Payé;À relancer;En attente. Validez avec OK. Une petite flèche apparaît à droite de chaque cellule de la colonne. Au clavier, Alt et flèche bas ouvre la liste. Si quelqu'un tape un mot absent de la liste, Excel refuse la saisie.

Le jour où vous ajoutez un statut, une source tapée oblige à rouvrir la boîte. Le plus pratique est de ranger les choix dans une feuille à part, appelée Listes. Écrivez les statuts sous un titre, puis mettez-les sous forme de tableau avec Ctrl et T. Excel appelle ce tableau Tableau1.
Attention au piège. Si vous sélectionnez simplement les cellules du tableau dans la source, Excel écrit =Listes!$A$2:$A$4. Ajoutez Annulé sous le tableau. Le tableau s'agrandit bien, mais la liste déroulante reste sur les trois mêmes cellules. Vous ne pouvez pas non plus écrire =Tableau1[Statut] dans la source, Excel le refuse.
La bonne méthode passe par un nom. Dans l'onglet Formules, cliquez sur Définir un nom. Écrivez ListeStatuts, sans espace. Dans Fait référence à, tapez =Tableau1[Statut]. Dans la validation, la source devient =ListeStatuts. Chaque statut ajouté sous le tableau apparaît alors dans la liste.

L'onglet Message de saisie affiche une bulle dès qu'on sélectionne la cellule, par exemple « Choisissez un statut dans la liste ». Quelqu'un qui découvre le fichier sait tout de suite quoi faire.
L'onglet Alerte d'erreur décide de ce qui se passe quand la valeur tapée n'est pas dans la liste. Dans Excel 2021 en français, la liste Style contient trois choix. Stop, avec la croix rouge, refuse la saisie. C'est le style par défaut et celui qu'il faut pour un statut. Avertissement demande si l'on veut continuer. Informations prévient puis accepte la valeur. Ces deux derniers laissent passer les fautes.

Léa vend des carnets, des stylos et du papier cadeau. Elle choisit d'abord la catégorie en colonne C. En colonne D, elle veut voir seulement les articles de cette catégorie. La même fonction sert à résumer plusieurs feuilles identiques, comme le montre le guide de la fonction INDIRECT sur Excel appliquée à douze mois.
Dans la feuille Listes, rangez les articles en colonnes, avec le nom de chaque catégorie en titre. Sélectionnez le bloc, titres compris. Dans l'onglet Formules, cliquez sur Créer à partir de la sélection. Ne gardez que Ligne du haut cochée, puis validez. Excel crée un nom par colonne.
Sélectionnez ensuite D2 à D9 et ouvrez Validation des données. Choisissez Liste et tapez la source =INDIRECT(C2). INDIRECT lit le texte de la cellule C2, par exemple Stylos, puis renvoie la plage qui porte ce nom. Choisissez Stylos en catégorie et la liste des articles ne montre que les stylos.

La première vient des espaces. Un nom Excel ne peut pas en contenir. En créant les noms, Excel transforme Papier cadeau en Papier_cadeau. La source =INDIRECT(C2) cherche alors un nom qui n'existe pas et la liste reste vide. Écrivez plutôt =INDIRECT(SUBSTITUE(C2;" ";"_")), qui remplace l'espace par un tiret bas.
La deuxième vient des cases vides. Si une colonne de la feuille Listes est plus courte que les autres et que le nom couvre tout le bloc, les lignes vides apparaissent en bas de la liste. Créez chaque nom seulement sur les cellules remplies.
La troisième est un copier-coller. Coller une cellule ordinaire sur une cellule à liste efface la liste. Même un collage des valeurs seules laisse passer un mot absent de la liste sans prévenir. Pour remettre la liste, copiez une cellule qui l'a encore, puis choisissez Collage spécial et Validation.

Pour modifier une liste, cliquez sur une de ses cellules et rouvrez Validation des données. Cochez Appliquer ces modifications aux cellules de paramètres identiques et le changement s'applique à toute la colonne. Pour supprimer la liste, cliquez sur Effacer tout puis sur OK. Les valeurs déjà choisies restent dans les cellules.
La formation IniTableur reprend la validation des données dans le Bloc 1, puis les noms et INDIRECT dans le Bloc 2, avec des exercices corrigés. Les listes y servent de base à des tableaux plus complets, comme un suivi de dépenses ou un planning.
Sélectionnez les cellules, puis Données et Validation des données. Dans Autoriser, choisissez Liste. Dans Source, tapez les choix séparés par des points-virgules ou indiquez une plage, puis validez avec OK.
La source pointe sur une plage fixe, par exemple $A$2:$A$4. Définissez un nom sur la colonne d'un tableau, comme =Tableau1[Statut]. Utilisez ensuite ce nom comme source.
Créez un nom par catégorie avec Formules puis Créer à partir de la sélection, puis utilisez la source =INDIRECT(C2), où C2 contient la catégorie choisie.
Le plus souvent, la catégorie contient une espace. Le nom Excel correspondant utilise un tiret bas. Utilisez =INDIRECT(SUBSTITUE(C2;" ";"_")).
Sélectionnez les cellules, ouvrez Validation des données, cliquez sur Effacer tout puis sur OK. Les valeurs déjà saisies restent.
Cet article vous a-t-il aidé ?

Le montant de chaque ligne, le total avec SOMME, la remise et la TVA, sur un devis qui se recalcule seul.

Construisez un tableau de ventes qui calcule les montants et alimente un graphique. Je vous montre aussi comment saisir les décimales, filtrer vos produits et recopier une formule sur 1 000 lignes.